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.
IFSAPP9 by the way.
Best answer by william.klotz
Hi @matthunter ,
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 = 0 AND 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 = 0 AND 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.
