Thursday, July 21, 2016

Query to Find POs which not used Blanket Purchase Agreement

Blanket Purchase Agreement not used

SELECT *
  FROM apps.po_headers_all pha
 WHERE NOT EXISTS (
          SELECT po_header_id
            FROM apps.po_releases_all pra
           WHERE pra.po_header_id = pha.po_header_id
             AND TRUNC (creation_date) BETWEEN :p_str_date AND :end_date)
   AND type_lookup_code = 'BLANKET'
   AND NVL (closed_code, 'OPEN') LIKE 'OPEN'
   AND NVL (cancel_flag, 'N') LIKE 'N'
   AND closed_date IS NULL
   AND org_id = 687

-------------------------------------------------------------------------------
Select Trunc (Creation_Date) Creation_Date,
       Trunc (Last_Update_Date) Last_Update_Date, Segment1 Po_Number,
       Type_Lookup_Code Type1,
   (Select LAST_NAME
   From Apps.Per_All_People_F Papf
   Where Papf.Person_Id=pha.Agent_Id
   and rownum=1) Buyer_name,
          (Select Vendor_Name
          From Apps.Po_Vendors Pv
         Where Pha.Vendor_Id = Pv.Vendor_Id) Vendor_Name,
       (Select Vendor_Site_Code
          From Apps.Po_Vendor_Sites_All Pvsa
         Where Pha.Vendor_Site_Id = Pvsa.Vendor_Site_Id) Vendor_Site,
       Authorization_Status, Comments
  From Apps.Po_Headers_All Pha
 Where Type_Lookup_Code = 'BLANKET'
   And Nvl (Closed_Code, 'OPEN') Like 'OPEN'
   And Nvl (Cancel_Flag, 'N') Like 'N'
   And Closed_Date Is Null
   And Org_Id = 687
-----------------------------------------------------------------------------
SELECT TRUNC (creation_date) creation_date,
       TRUNC (last_update_date) last_update_date, segment1 po_number,
       type_lookup_code type1,
       (SELECT last_name
          FROM apps.per_all_people_f papf
         WHERE papf.person_id = pha.agent_id AND ROWNUM = 1) buyer_name,
       (SELECT vendor_name
          FROM apps.po_vendors pv
         WHERE pha.vendor_id = pv.vendor_id) vendor_name,
       (SELECT vendor_site_code
          FROM apps.po_vendor_sites_all pvsa
         WHERE pha.vendor_site_id = pvsa.vendor_site_id) vendor_site,
       authorization_status, comments,
       (SELECT COUNT (*)
          FROM po.po_releases_all pra
         WHERE NVL (pra.closed_code, 'OPEN') = 'OPEN'
           AND NVL (pra.cancel_flag, 'N') = 'N'
           AND pra.po_header_id = pha.po_header_id) rel_count
  FROM apps.po_headers_all pha
 WHERE pha.type_lookup_code = 'BLANKET'
   AND NVL (pha.closed_code, 'OPEN') LIKE 'OPEN'
   AND NVL (pha.cancel_flag, 'N') LIKE 'N'
   AND pha.closed_date IS NULL
   AND pha.org_id = 687
   

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...