BPA Master List

Sometime users wants to have the master BPA (Blanket Purchase Agreement) list irrespective of ay release is being created or not. Below query will help to get the same.


/* Formatted on 11/6/2016 12:55:53 PM (QP5 v5.126.903.23003) */
SELECT   a.SEGMENT1 BPA_NO,
         COMMENTS BPA_DESC,
         CURRENCY_CODE,
         APPROVED_DATE,
         (SELECT   APPROVED_DATE
            FROM   PO_HEADERS_ARCHIVE_ALL
           WHERE       REVISION_NUM = 0
                   AND APPROVED_FLAG = 'Y'
                   AND po_header_id = a.po_header_id)
            PO_APPROVED_DATE_ORG,
         REVISION_NUM,
         START_DATE,
         END_DATE,
         AMOUNT_LIMIT Updated_ACV_Amount,
         (CASE
             WHEN a.ATTRIBUTE_CATEGORY = 'ACV Details' THEN a.ATTRIBUTE1
          END)
            Initial_ACV_Amount,
         a.RATE,
         (SELECT   DESCRIPTION
            FROM   fnd_lookup_values
           WHERE   a.FOB_LOOKUP_CODE = LOOKUP_CODE AND lookup_type = 'FOB')
            SHIPMENT_TERMS,
         c.SEGMENT1 SUPPLIER_NUM,
         c.VENDOR_NAME SUPPLIER_NAME,
         b.address_line1,
         b.country,
         SYSDATE
  FROM   PO_HEADERS_ALL a, ap_supplier_sites_all b, ap_suppliers c
 WHERE       a.vendor_id = b.vendor_SITE_id(+)
         AND b.vendor_id = c.vendor_id(+)
         AND TYPE_LOOKUP_CODE = 'BLANKET'


Enjoy and Stay Well !!