Question

Transfer WO Reference Number to Customer Order

  • 28 July 2021
  • 1 reply
  • 144 views

Userlevel 4
Badge +7

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?


This topic has been closed for comments

1 reply

Userlevel 7
Badge +21

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