Purchasing

Get Involved. Join the Conversation.

Topic

    Boyuan Zhu
    How to Create Change Orders with a Script
    Topic posted November 1, 2018 by Boyuan ZhuGold Medal: 3,500+ Points, tagged Orders, Web Services 
    235 Views
    Title:
    How to Create Change Orders with a Script
    Content:

    You can call the Purchase Order Service V2 to create change orders to keep your orders up to date. For example, you can use change orders to correct attribute values that have been defaulted to an undesired value or migrated from a legacy system with the wrong value. If you need to modify only a few orders, you can use the sample payloads in our previous post and a client like SoapUI. If you want to create many change orders, it is easier to do so using a script. In the following use case, orders have been created with the invoice match option set to Receipt, but now the business would like to set the option to Order for each schedule. 

    1. To use the script, you need to feed it data on the orders/lines/schedules/distributions you want to change. You can generate this data by running a data model on BI Publisher. The example code below will output an XML of all the schedules you need to update by selecting the schedules with invoice match option as Receipt and no receiving or invoicing has taken place. The XML also includes the desired new invoice match option (Order) for each schedule and the change order description for the header. You can save the output as data.xml in the script directory.

    SELECT pll.po_header_id AS HEADERID,
    pll.po_line_id AS LINEID, 
    pll.line_location_id AS SCHEDULEID,
    'Change match option' AS CHANGEORDERDESC,
    'Order' AS MATCHOPTION
    FROM po_line_locations_all pll, 
     po_distributions_all pod 
    WHERE
    pll.SCHEDULE_STATUS = 'OPEN' 
    AND pll.match_option = 'R' 
    AND pod.line_location_id = pll.line_location_id 
    AND NOT EXISTS 
     (SELECT 'txnexists' 
     FROM rcv_transactions rt 
     WHERE rt.po_line_location_id = pll.line_location_id) 
    AND NOT EXISTS 
     (SELECT 'invoiceexists' 
     FROM AP_INVOICE_DISTRIBUTIONS_ALL ap 
     WHERE ap.PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID)
    

    2. To run the script (createChangeOrder.py attached below), you need the zeep SOAP library. You can install it using pip, or change the script to use your preferred SOAP library. 

    3. When you run the script, it reads the data.xml file and make a web service call for each order changed. For each order, the invoice match option on the selected schedules is updated to the desired invoice match option. You can change or add new attributes to the payload by changing BI Publisher SQL, and then parsing and referencing the output data in the script. Supported attributes can be found in the service definition

    Document: