Hello,
I’m looking to update the “PRIORITY” field of an Internal Customer Order from either the External Customer Order or better yet, the customer priority field found under Application Base Setup > Enterprise > Customer > Customer, then Order > Misc Customer Info tab.
The problem with the Internal Customer Order not picking up the customer priority, is that it is from a different site (contract) than the External Customer Order. We have all of our External Customer Orders set up for Site 0. The Internal Customer Orders are set up for whatever site the order is being placed out of (Sites 13,14,16,18,19)
As you may know, there is a delay when “Releasing” the External Customer Order, which the Purchasing Order is created, then the Internal Customer Order. If you watch the Supply Chain Order Analysis screen after releasing an External Customer Order and constantly refresh it, you will eventually see the Internal Customer Order pop up.
I am trying to write a custom event to get the priority and populate within the Internal Customer Order, similar to how that happens in the External Customer Order when it is created. I’ve set up my custom event to trigger based on order release and made the custom event act as a background job, but it still fires before the Internal Customer Order is created, causing “ORA-20115: CustomerOrder.FND_REMOVED The "Customer Order" has already been removed by another user.” error. The custom event is below, any other ideas?
DECLARE
attr_ VARCHAR2(32000);
sql_msg_ VARCHAR2(32000);
stmt_ VARCHAR2(32000);
BEGIN
stmt_ := 'DECLARE
info_ VARCHAR2(32000) := NULL;
objid_ VARCHAR2(100) ;
objversion_ VARCHAR2(100);
attr_cf_ VARCHAR2(32000);
attr_ VARCHAR2(32000) := '''';
action_ VARCHAR2(32000) := ''DO'';
order_no_ varchar2(35) := ''OLD:&ORDER_NO'';
customer_no_ varchar2(35) := ''OLD:&CUSTOMER_NO'';
priority_ varchar2(10);
cursor get_object IS
SELECT distinct INT.objid AS objid, INT.objversion AS objversion, INT.priority AS priority
FROM (
SELECT soa.tree_id, col.order_no, col.objid, col.objversion, col.priority, col.customer_no
FROM SUPPLY_ORDER_ANALYSIS soa
INNER JOIN CUSTOMER_ORDER col ON soa.order_ref1 = col.order_no
WHERE soa.CONTRACT <> 0
) INT,
(SELECT soa.tree_id, col.order_no, col.objid, col.objversion, col.priority, col.customer_no
FROM SUPPLY_ORDER_ANALYSIS soa
INNER JOIN CUSTOMER_ORDER col ON soa.order_ref1 = col.order_no
WHERE soa.CONTRACT = 0
) EXT
WHERE INT.TREE_ID = EXT.TREE_ID
AND EXT.order_no = order_no_;
BEGIN
open get_object;
fetch get_object into objid_, objversion_, priority_;
close get_object;
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr(''PRIORITY'', CUST_ORD_CUSTOMER_API.Get_Priority(customer_no_ => customer_no_), attr_);
Customer_Order_API.Modify__(info_, objid_, objversion_, attr_, action_);
END;';
sql_msg_ := Message_SYS.Construct('RELEASE');
Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
Client_SYS.Add_To_Attr('MSG_', '', attr_);
Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql',
'PARAMETER',
attr_,
'Populate ICO priority with ECO priority.');
END;
Thank you,
Mike
