Hi @WMCMARSEGLIA ,

My thought would be to create a custom event to trigger on the CustomerOrder logical unit and CUSTOMER_ORDER_TAB database table.   I’m not sure if I would trigger on a new or an update.   I would prefer to trigger on the update when the status changes and then in the custom action only have the action active when the status goes to release it possible.   There are two other fields I’d consider adding to my action filter and they are customer number and internal PO No. or internal PO Ref.

If when the internal customer order is created the system puts a value in the internal PO No.  or internal PO Ref. field you’d be able to easily navigate back to the PO that created the internal customer order.  If you’ve get to this part of the action working you should be able to use the internal PO No. or internal PO Ref to navigate back to the internal PO and ultimately from the internal PO back to the external customer order.   You’d be able then get the priority number from the external customer order and update the internal customer order.

You’ll have to update the internal customer order in the background so you do not get an error from Oracle about a mutating trigger.

Regards,

William Klotz

Hey William,

             Thank you so much for replying, I really appreciate it.  I see what you’re saying and I’ve attempted to put all that together.  Unfortunately, I’m running into a road block below.  Would you happen to have any suggestions?

            Unfortunately, I can’t use CUSTOMER_NO, because it’s always the same from that site (site 0).  I’m attempting to get back to the External Customer or External Customer Order by the api below, but I keep running into the requirement of the PO LINE_NO and RELEASE_NO.

CUST_ORD_CUSTOMER_API.Get_Priority(customer_no_ => CUSTOMER_ORDER_API.Get_Customer_No(order_no_ => PURCHASE_ORDER_LINE_PART_API.Get_Demand_Order_No(order_no_ => &OLD:CUSTOMER_PO_NO,line_no_ => '1',release_no_ => '1')))

OR

CUSTOMER_ORDER_API.Get_Priority(order_no_ => PURCHASE_ORDER_LINE_PART_API.Get_Demand_Order_No(order_no_ => &OLD:CUSTOMER_PO_NO,line_no_ => '1',release_no_ => '1'))

         Would you happen to know another way?  Should I be using CUSTOMER_ORDER_LINE for an event LU?

Thank you for any help, very much appreciated,

Mike

OK, I accomplished this by using a Customer_Order_Line LU and the Customer_Order_Line_Tab trigger when the order line on an Internal Customer Order (Contract !=0) rowstate becomes “Released”.  I just use the first line on the purchase order to find the External Customer No, then ultimately the priority from the customer record.  I check to see if that is NULL or not.

Here is the action:

NEW:CONTRACT != 0, OLD_ROWSTATE != Released, NEW_ROWSTATE = Released

DECLARE
   attr_    VARCHAR2(32000);
   sql_msg_ VARCHAR2(32000);
   stmt_    VARCHAR2(32000);
   
BEGIN
  stmt_ := 'DECLARE
  info_       varchar2(2000);
  objid_      varchar2(100);
  objversion_ varchar2(100);
  attr_    VARCHAR2(32000);
  action_     VARCHAR2(32000) := ''DO'';
  order_no_   varchar2(35) := ''&NEW:ORDER_NO'';
  order_po_no_ varchar2(35) := ''&NEW:DEMAND_ORDER_REF1'';
  
  cursor get_object IS
  select objid, objversion
  from customer_order
  where order_no = order_no_
  and state = ''Released'';

BEGIN 
  open get_object; 
  fetch get_object into objid_, objversion_; 
  close get_object; 

if(CUST_ORD_CUSTOMER_API.Get_Priority(customer_no_ => CUSTOMER_ORDER_API.Get_Customer_No(order_no_ => PURCHASE_ORDER_LINE_PART_API.Get_Demand_Order_No(order_no_ => order_po_no_,line_no_ => ''1'',release_no_ => ''1''))) IS NOT NULL) THEN
  Client_SYS.Clear_Attr(attr_); 
  Client_SYS.Add_To_Attr(''PRIORITY'', CUST_ORD_CUSTOMER_API.Get_Priority(customer_no_ => CUSTOMER_ORDER_API.Get_Customer_No(order_no_ => PURCHASE_ORDER_LINE_PART_API.Get_Demand_Order_No(order_no_ => order_po_no_,line_no_ => ''1'',release_no_ => ''1''))), attr_); 
  Customer_Order_API.Modify__(info_, objid_, objversion_, attr_, action_);
end if;
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;