SQL – Purchase Orders – Value, Ordered, Receipted, Billed

Sometimes while testing, it’s useful to find POs in specific states to test against. For example, you might want to test matching an AP invoice to a PO which has no receipts against it, to test the Holds process.

It can be quicker to find these sometimes, than create the test data from scratch. I don’t think it’s 100% perfect, but it’s a bit of a rough and ready way to find test data.

This SQL can be changed to meet your requirements.

    SELECT pha.segment1 po
         , pha.creation_date
         , pha.po_header_id
         , PHA.CLOSED_CODE
         , PHA.CANCEL_FLAG
         , pv.vendor_name supplier
         , pvsa.vendor_site_code site
         , SUM(pla.unit_price * pla.quantity) total_value
         , SUM(pla.quantity) total_ordered
         , SUM(plla.quantity_received) total_receipted
         , SUM(plla.quantity_billed) total_billed
         , COUNT(DISTINCT pla.po_line_id) line_count
--         , COUNT(DISTINCT pla.closed_code) line_closed_ct
      FROM po.po_headers_all pha
      JOIN ap.ap_suppliers pv on pha.vendor_id = pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa ON pha.vendor_site_id = PVSA.VENDOR_SITE_ID AND pvsa.vendor_id = pv.vendor_id
      JOIN po.po_lines_all pla ON pha.po_header_id = pla.po_header_id
      JOIN po.po_line_locations_all plla ON plla.po_line_id = pla.po_line_id
      JOIN po.po_distributions_all pda ON PDA.PO_LINE_ID = PLA.PO_LINE_ID
     WHERE pha.authorization_status = 'APPROVED'
       AND pha.creation_date BETWEEN '01-OCT-2016' AND '01-NOV-2016'
       AND pla.closed_code = 'OPEN' -- line not closed or cancelled
       AND PDA.PROJECT_ID IS NULL -- not matched to project
       AND pvsa.pay_on_code IS NULL -- supplier is not set to pay on receipt
    HAVING SUM(pla.unit_price * pla.quantity) > 1000 
       AND COUNT(DISTINCT pla.closed_code) = 1 -- all lines open
       AND COUNT(DISTINCT pla.po_line_id) = 1 -- single line
       AND SUM(plla.quantity_received) = 0 -- not receipted
       AND SUM(plla.quantity_billed) = 0
  GROUP BY pha.po_header_id
         , pha.creation_date
         , pha.segment1
         , PHA.CLOSED_CODE
         , PHA.CANCEL_FLAG
         , pv.vendor_name
         , pvsa.vendor_site_code
  ORDER BY pha.creation_date ASC;