I have a custom event that I want to trigger an email when a shop order is set to “Completely Reserved”. Seems simple enough. I have this event with the following called out as well as a custom attribute called “SHOP_STATUS” = SHOP_ORD_API.Get_Mtrl_State(&NEW:ORDER_NO,&NEW:RELEASE_NO,&NEW:SEQUENCE_NO)
The problem I’m getting is that the custom attribute for Shop Status is the OLD status before the event occurs. I would think if I had Fire After selected the object is changed that I would get the NEW status. Any words of advice?
If I could find the material state as an actual standard attribute in a tab, obviously that would be best, but I cannot find it anywhere in any standard LU.
I’m thinking the reason the material status is acting this way is because I do not believe this state is stored but rather a real time query from IEE and since you’ve intercepted the event before committing the data to the database the call to SHOP_ORD_API.Get_Mtrl_State(&NEW:ORDER_NO,&NEW:RELEASE_NO,&NEW:SEQUENCE_NO) is returning what’s currently in the database.
Looking at the package SHOP_ORD_API if you trace through Get_Mtrl_State you’ll eventually end up in the API called Get_Mtrl_State_Db__ which has this cursor in it to check for completely reserved. Basically the API Get_Mtrl_State_Db__ gets the total number of material lines on the shop order set to ‘Consumed’ and if all of them have qty_issued = 0 and qty_assigned >= qty_required it will return a 5 indicating “Completely Reserved’.
SELECT NVL(COUNT(*),0)
FROM SHOP_MATERIAL_ALLOC_TAB
WHERE qty_issued = 0AND qty_assigned >= qty_required
AND sequence_no = sequence_no_
AND release_no = release_no_
AND order_no = order_no_
AND consumption_item = 'Consumed';
I’m thinking the reason the material status is acting this way is because I do not believe this state is stored but rather a real time query from IEE and since you’ve intercepted the event before committing the data to the database the call to SHOP_ORD_API.Get_Mtrl_State(&NEW:ORDER_NO,&NEW:RELEASE_NO,&NEW:SEQUENCE_NO) is returning what’s currently in the database.
Looking at the package SHOP_ORD_API if you trace through Get_Mtrl_State you’ll eventually end up in the API called Get_Mtrl_State_Db__ which has this cursor in it to check for completely reserved. Basically the API Get_Mtrl_State_Db__ gets the total number of material lines on the shop order set to ‘Consumed’ and if all of them have qty_issued = 0 and qty_assigned >= qty_required it will return a 5 indicating “Completely Reserved’.
SELECT NVL(COUNT(*),0)
FROM SHOP_MATERIAL_ALLOC_TAB
WHERE qty_issued = 0AND qty_assigned >= qty_required
AND sequence_no = sequence_no_
AND release_no = release_no_
AND order_no = order_no_
AND consumption_item = 'Consumed';
Gosh that makes so much sense. Thank you so much for writing out this response, it really means a lot to me to get help like this. And I did find the API as well, but didn’t know to use it correctly.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
