Blanket Purchase Agreements in Oracle Apps

Blanket Purchase Agreement with Line Count

SELECT   pv.vendor_name

       , pvsa.vendor_site_code site

       , pha.segment1 bpa_num

       , pha.creation_date

       , COUNT(*) line_count

    FROM po.po_headers_all pha

       , po.po_lines_all pla

       , apps.po_vendors pv -- use for use for 11i

       , apps.po_vendor_site_all pvsa -- use for 11i

   WHERE pha.po_header_id = pla.po_header_id

     AND pha.vendor_id = pv.vendor_id

     AND pha.vendor_site_id = pvsa.vendor_site_id

     AND pv.vendor_id = pvsa.vendor_id

     AND pha.type_lookup_code = 'BLANKET'

     AND pha.closed_code IS NULL

GROUP BY pv.vendor_name

       , pvsa.vendor_site_code

       , pha.segment1

       , pha.creation_date;

 

Linked to details

ELECT pv.vendor_name

     , pvsa.vendor_site_code site

     , pavt.description

     , pavt.manufacturer

     , pavt.comments

     , pavt.long_description

     , pav.picture

  FROM po.po_headers_all pha

     , po.po_lines_all pla

     , apps.po_vendors pv -- use for use for 11i

     , apps.po_vendor_site_all pvsa -- use for 11i

     , po.po_attribute_values pav

     , po.po_attribute_values_tlp pavt

 WHERE pha.po_header_id = pla.po_header_id

   AND pha.vendor_id = pv.vendor_id

   AND pha.vendor_site_id = pvsa.vendor_site_id

   AND pv.vendor_id = pvsa.vendor_id

   AND pla.po_line_id = pav.po_line_id

   AND pla.po_line_id = pavt.po_line_id

   AND pha.type_lookup_code = 'BLANKET';

 

Another details view

SELECT   pv.vendor_name supplier

       , pvsa.vendor_site_code site

       , pha.segment1 bpa_number

       , pla.line_num

       , pla.unit_price

       , pla.base_unit_price

       , pla.item_description

       , pla.unit_meas_lookup_code uom

       , pla.vendor_product_num item_code

       , mcb.segment1 || '.' || mcb.segment2 purchase_category      

       , pla.catalog_name

       , pla.creation_date

       , fu.description created_by

    FROM po.po_headers_all pha

       , po.po_lines_all pla

       , apps.po_vendors pv -- use for use for 11i

       , apps.po_vendor_site_all pvsa -- use for 11i

       , inv.mtl_categories_b mcb

       , applsys.fnd_user fu

   WHERE pha.po_header_id = pla.po_header_id

     AND pha.vendor_id = pv.vendor_id

     AND pha.vendor_site_id = pvsa.vendor_site_id

     AND pv.vendor_id = pvsa.vendor_id

     AND pla.category_id = mcb.category_id

     AND pla.CREATED_BY = fu.USER_ID

     AND pha.type_lookup_code = 'BLANKET'

     AND pha.AUTHORIZATION_STATUS = 'APPROVED'

     AND (pla.cancel_flag IS NOT NULL OR pla.cancel_flag = 'N')

     AND (pha.closed_code IS NULL OR pha.closed_code <> 'CLOSED')

ORDER BY pv.vendor_name

       , pvsa.vendor_site_code

       , pla.vendor_product_num;

 

Item Count Per Supplier

SELECT   pv.vendor_name supplier

       , pvsa.vendor_site_code site

       , count(*) item_count

    FROM po.po_headers_all pha

       , po.po_lines_all pla

       , apps.po_vendors pv -- use for use for 11i

       , apps.po_vendor_site_all pvsa -- use for 11i

       , applsys.fnd_user fu

   WHERE pha.po_header_id = pla.po_header_id

     AND pha.vendor_id = pv.vendor_id

     AND pha.vendor_site_id = pvsa.vendor_site_id

     AND pv.vendor_id = pvsa.vendor_id

     AND pla.CREATED_BY = fu.USER_ID

     AND pha.type_lookup_code = 'BLANKET'

     AND pha.AUTHORIZATION_STATUS = 'APPROVED'

     AND (pla.cancel_flag IS NULL OR pla.cancel_flag = 'N')

     AND (pha.closed_code IS NULL OR pha.closed_code <> 'CLOSED')

GROUP BY pv.vendor_name

       , pvsa.vendor_site_code

ORDER BY pv.vendor_name

       , pvsa.vendor_site_code;