Trigger custom event after internal order is created?

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

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;