Auto Create Purchase orders

This small bit of SQL is useful if you want to work out how many POs per day are created via Auto Create, and how many are created automatically by being linked to a Contract Purchase Order, and are therefore created via the CREATEPO (PO Create Documents) workflow.

SELECT SUM(autocreate_ct) count_autocreate

     , SUM(contract_ct) count_contract

  FROM (SELECT (SELECT COUNT(DISTINCT pha.po_header_id)

                  FROM po.po_lines_all pla

                 WHERE pha.po_header_id = pla.po_header_id

                   AND pha.document_creation_method = 'AUTOCREATE')

                                                                autocreate_ct

             , (SELECT COUNT(DISTINCT pla.po_header_id)

                  FROM po.po_lines_all pla

                 WHERE pha.po_header_id = pla.po_header_id

                   AND pla.contract_id IS NOT NULL

                   AND (pha.document_creation_method <> 'AUTOCREATE'))

                                                                  contract_ct

          FROM po.po_headers_all pha

         WHERE pha.creation_date >= TRUNC(SYSDATE) - 30 -- last 30 days

           AND pha.type_lookup_code = 'STANDARD';