Skip to main content

We create work orders for re-active jobs and enter the customers purchase order number in the ‘Reference No’ field under the CO Information tab.

To invoice, we create an entry in the Postings tab and Transfer it to Customer Order. When we create the customer order from this posting line, we wish the value in the ‘Reference No’ field of the work order, to automatically populate the ‘Customer PO No’ field on the header section of the Customer Order.

I have created a custom event to fire once the customer order is created, however I am unsure if a custom action (Execute Online SQL) could be used to add this value to the field. Can anyone advise if this is possible and point me towards the correct syntax for such an event?

Hi @hwilkie ,

 

As you mention you could create a customer event that fires once the customer order has been created and then call a custom stored procedure to update the customer order in the background with the purchase order number.  Here’s a sample stored procedure which updates a customer order with a PO Number.  

 

You will need to modify the stored procedure below to meet your requirements such as maybe instead of passing the PO_NO_ passing the Work Order information so you can retrieve the Reference No and use the Reference No to populate the customer po no.

 

CREATE OR REPLACE PROCEDURE OUR_WO_TO_CO_PO (
  order_no_     IN VARCHAR2,
  po_no_        IN VARCHAR2
) AS

  customer_order_no_ CUSTOMER_ORDER_TAB.order_no%TYPE;
  customer_po_no_    CUSTOMER_ORDER_TAB.customer_po_no%TYPE;
  info_              VARCHAR2(4000);
  attr_              VARCHAR2(4000);

BEGIN
  customer_order_no_ := order_no_;
  customer_po_no_ := po_no_;

  CLIENT_SYS.Clear_Attr(attr_);
  CLIENT_SYS.Add_To_Attr('CUSTOMER_PO_NO',customer_po_no_, attr_);
  CUSTOMER_ORDER_API.Modify(info_, attr_, customer_order_no_);
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END OUR_WO_TO_CO_PO;
/

 

Regards,

William Klotz

Terms & ConditionsCookie settings