Contract Purchase Agreements in Purchase Order

CPA Details

SELECT   hou.NAME ou

       , pha.segment1 cpa_po_number

       , pha.creation_date

       , pv.vendor_name supplier

       , pvsa.vendor_site_code site

       , pvsa.attribute4

       , pvsa.attribute5

       , pvsa.attribute6

       , pvsa.attribute7

    FROM po.po_headers_all pha

       , apps.ap_suppliers pv

       , apps.ap_supplier_sites_all pvsa

       , apps.hr_operating_units hou

   WHERE pha.vendor_id = pv.vendor_id

     AND pha.vendor_site_id = pvsa.vendor_site_id

     AND pha.org_id = pvsa.org_id

     AND pha.org_id = hou.organization_id

     AND pha.type_lookup_code = 'CONTRACT'

     AND pha.authorization_status = 'APPROVED'

ORDER BY 3

 

CPA Count Setup Rollup by Year and Month

SELECT   NVL(TO_CHAR(extract(YEAR FROM pha.creation_date)),'TOTAL') CREATION_YEAR,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),1,1,0)) JAN,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),2,1,0)) FEB,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),3,1,0)) MAR,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),4,1,0)) APR,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),5,1,0)) MAY,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),6,1,0)) JUN,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),7,1,0)) JUL,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),8,1,0)) AUG,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),9,1,0)) SEP,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),10,1,0)) OCT,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),11,1,0)) NOV,

         SUM(DECODE(extract (MONTH FROM pha.creation_date),12,1,0)) DEC,

         SUM(1) total

    FROM po.po_headers_all pha

       , apps.ap_suppliers pv

       , apps.ap_supplier_sites_all pvsa

       , apps.hr_operating_units hou

   WHERE pha.vendor_id = pv.vendor_id

     AND pha.vendor_site_id = pvsa.vendor_site_id

     AND pha.org_id = pvsa.org_id

     AND pha.org_id = hou.organization_id

     AND pha.type_lookup_code = 'CONTRACT'

     AND pha.authorization_status = 'APPROVED'

GROUP BY rollup(extract(YEAR FROM pha.creation_date));