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.
- Create the data model in BI Publisher with parameter param_sales_order and a new data set.
- 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
Tagged:
4