Contract Purchase Agreement Stats

If your organisation uses Contract Purchase Agreements, you can use this SQL to report on how many POs have been raised against each CPA.



WITH header_lines_summary AS
     (SELECT   pla.contract_id
             , COUNT(DISTINCT pha2.po_header_id) AS po_count
             , MAX(pha2.creation_date) AS latest_cpa_po
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
      GROUP BY pla.contract_id)
SELECT pha.segment1 cpa_number
     , pha.comments cpa_description
     , pha.creation_date
     , fu.description created_by
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , hls.po_count po_count_raised_via_cpa
     , hls.latest_cpa_po
  FROM po.po_headers_all pha
     , po.po_vendors pv                -- use ap.ap_suppliers for r12
     , po.po_vendor_sites_all pvsa        -- use ap.ap_supplier_sites_all for r12
     , header_lines_summary hls
     , applsys.fnd_user fu
 WHERE pha.vendor_id = pv.vendor_id
   AND pha.vendor_site_id = pvsa.vendor_site_id
   AND pha.created_by = fu.user_id
   AND pha.po_header_id = hls.contract_id(+)
   AND pha.type_lookup_code = 'CONTRACT';