Supplier and supplier site Query

Therefore, if the statement below does not work for you, you will need to replace references to the release 12 supplier tables with the 11i table names.


  SELECT DISTINCT bus_gp.name org
                , pv.segment1
                , pv.vendor_name
                , pvsa.vendor_site_code site
                , pvsa.org_id
                , pvsa.supplier_notif_method notif
                , pvsa.email_address email
                , fu.description last_udated_by
                , pv.end_date_active header_end_date
                , pvsa.inactive_date site_end_date
    FROM ap.ap_suppliers pv                        -- use po.po_vendors for 11i
       , ap.ap_supplier_sites_all pvsa                    -- use po.po_vendor_sites_all for 11i
       , hr.hr_all_organization_units_tl bus_gp
       , applsys.fnd_user fu
   WHERE pv.vendor_id = pvsa.vendor_id
     AND pvsa.org_id = bus_gp.organization_id
     AND pvsa.last_updated_by = fu.user_id
     AND pv.segment1 = 123456
     AND pvsa.inactive_date IS NULL
     AND pv.end_date_active IS NULL
     AND pvsa.purchasing_site_flag = 'Y'
ORDER BY bus_gp.name
       , pv.vendor_name
       , pvsa.vendor_site_code;

Include Bank Account and Site Currency

       SELECT pv.segment1 supplier_num
     , pv.vendor_id
     , pv.vendor_name
     , pvsa.vendor_site_code site
     , pvsa.invoice_currency_code
     , pvsa.payment_currency_code
     , pv.end_date_active header_end_date
     , pvsa.inactive_date site_end_date
  FROM ap.ap_suppliers pv
     , ap.ap_supplier_sites_all pvsa
 WHERE pv.vendor_id = pvsa.vendor_id
   AND pv.vendor_name ='BIG CHEEZEZ R US'

Sites which have had no POS raised against them in the last 300 days

SELECT   pv.vendor_name

       , pvsa.vendor_site_code

       , (SELECT COUNT(*)

            FROM po.po_headers_all pha

           WHERE pha.vendor_id = pv.vendor_id

             AND pha.vendor_site_id = pvsa.vendor_site_id) po_ct

    FROM po.po_vendor_sites_all pvsa                    -- use ap.ap_suppliers for r12

       , po.po_vendors pv                        -- use ap.ap_supplier_sites_all for r12

   WHERE NOT EXISTS(

            SELECT 'z'

              FROM po.po_headers_all pha

             WHERE pha.vendor_site_id = pvsa.vendor_site_id

               AND creation_date > SYSDATE - 300)

     AND pv.vendor_id = pvsa.vendor_id

     AND pv.end_date_active IS NULL

     AND pvsa.inactive_date IS NULL

     AND pvsa.purchasing_site_flag = 'Y'

ORDER BY 1

       , 2;

 

A basic supplier check. Note that in Release 12 that the basic supplier tables have changed:

Table Description	11i	R12
Supplier Headers	po.po_vendors	ap.ap_suppliers
Supplier Sites	po.po_vendor_sites_all	ap.ap_supplier_sites_all