Purchasing

Get Involved. Join the Conversation.

Topic

    Boyuan Zhu
    How to Find Purchase Orders Associated with a Sales Order
    Topic posted December 20, 2018 by Boyuan ZhuGold Medal: 3,500+ Points, last edited December 20, 2018, tagged BI Publisher Reports, How-To, Orders 
    53 Views
    Title:
    How to Find Purchase Orders Associated with a Sales Order
    Content:

    Customers integrating Purchasing with other applications may need to retrieve details for purchase orders associated with a sales order. You can create and call a BI report using PublicReportWSSService to fetch the associated order details. 

    1. Create the data model in BI Publisher with parameter param_sales_order and a new data set.
    2. Use the following query in the dataset to select orders associated with the sales order. Add any additional attributes you need to the query. 
      SELECT ph.SEGMENT1 OrderNumber,
      pl.LINE_NUM LineNumber,
      pd.DISTRIBUTION_NUM DistributionNumber
      FROM po_headers_all ph, po_lines_all pl, po_distributions_all pd, por_req_distributions_all prd, por_requisition_lines_all prl
      WHERE prd.REQUISITION_LINE_ID = prl.REQUISITION_LINE_ID
      AND pd.REQ_DISTRIBUTION_ID = prd.DISTRIBUTION_ID
      AND pd.PO_HEADER_ID = ph.PO_HEADER_ID
      AND pd.PO_LINE_ID = pl.PO_LINE_ID
      AND prl.SALES_ORDER_NUMBER = :param_sales_order
      
    3. Create a report that uses the data model as the source. 
    4. Run the report using the runReport operation in PublicReportWSSService (see 2086077.1 for WSDL information). 
    Document: