5 Replies Latest reply on May 7, 2018 10:03 AM by Dhanya-Oracle

    Query for BLANKET PURCHASE AGREEMENT

    AB115

      Hi,

       

      We are using below query for BPA (Blanket Purchase Agreement) but it fetches duplicate lines.

       

      Query:

      SELECT   pha.segment1 bpa_number

             , pla.line_num

             , pla.item_description

             , pla.unit_price

          FROM apps.po_headers_all pha

             , apps.po_lines_all pla

             , apps.ap_suppliers pv

             , apps.ap_supplier_sites_all pvsa

             , apps.mtl_system_items_b msi

             , apps.po_line_locations_all  plla

         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 msi.inventory_item_id = pla.item_id

           AND msi.organization_id = plla.ship_to_organization_id

           AND pla.po_line_id = plla.po_line_id

           AND pha.type_lookup_code = 'BLANKET'

      ORDER BY pha.segment1;

       

      could you please let us know what changes are required to fetch unique lines?

       

      Thanks.

        • 1. Re: Query for BLANKET PURCHASE AGREEMENT
          Dhanya-Oracle

          Hi,

           

          Looks like you missed one join ( pla.po_line_id= plla.po_line_id  )

           

          Try to use the below query. Suggest to hard code value of header id to one number for testing purpose and count the number of record ( Eg : pha.po_header_id       = 61)

           

          SELECT pha.segment1 bpa_number ,

            pla.line_num ,

            pla.item_description ,

            pla.unit_price

          FROM apps.po_headers_all pha,

            apps.po_lines_all pla,

            apps.po_line_locations_all plla,

            apps.mtl_system_items_b msi,

            apps.ap_suppliers pv,

            apps.ap_supplier_sites_all pvsa

          WHERE pha.type_lookup_code = 'BLANKET'

          --AND pha.po_header_id       = 61  -- Added this just to check the number of records returned for this specific po_header id.

          AND pha.po_header_id       = pla.po_header_id

          AND pla.po_line_id         = plla.po_line_id 

          AND pla.po_header_id       = plla.po_header_id

          AND msi.INVENTORY_ITEM_ID  = PLA.ITEM_ID

          AND msi.ORGANIZATION_ID    = PLLA.SHIP_TO_ORGANIZATION_ID

          AND pha.vendor_id          = pv.vendor_id

          AND pha.vendor_site_id     = pvsa.vendor_site_id

          AND pv.vendor_id           = pvsa.vendor_id;

           

          NOTE: If you consider your question/issue as helpful or answered, please, rate this answer as Correct or Helpful .

           

          Regards,

          Dhanya

          • 2. Re: Query for BLANKET PURCHASE AGREEMENT
            AB115

            we have added AND pla.po_line_id = plla.po_line_id condition in initial query.

             

            and when we are hard coding pha.segment1 value, it's giving duplicate rows.

            • 3. Re: Query for BLANKET PURCHASE AGREEMENT
              Dhanya-Oracle

              Add the column  plla.quantity in your select query, and you will come to know that it is not duplicate records.

               

              Same PO number can have multiple lines so it is expected behaviour that segment1 will remain same for multiple records.

              • 4. Re: Query for BLANKET PURCHASE AGREEMENT
                AB115

                thanks due to quantity and other fields, it's displaying multiple rows.

                • 5. Re: Query for BLANKET PURCHASE AGREEMENT
                  Dhanya-Oracle

                  Hi,

                   

                  Glad I could help.

                   

                  NOTE: If you consider your question/issue as helpful or answered, please, rate this answer as Correct or Helpful .

                             This will help to the rest of customers to find correct answers easily.

                   

                  Regards,

                  Dhanya