Supplier Detail Query

PO and Invoice Count for Active Suppliers

 

SELECT   pv.vendor_name

       , pv.vendor_type_lookup_code

       , pv.segment1 supplier_number

       , pvsa.vendor_site_code

       , pvsa.supplier_notif_method

       , pvsa.email_address email_po_address

       , pv.creation_date header_creation_date

       , pvsa.creation_date site_creation_date

       , pvsa.purchasing_site_flag

       , pvsa.pay_site_flag

       , pvsa.address_line1

       , pvsa.address_line2

       , pvsa.address_line3

       , pvsa.city

       , pvsa.state

       , pvsa.zip PURCHASE ORDERS

       , (SELECT COUNT(*)

            FROM po.po_headers_all pha

           WHERE pha.vendor_id = pv.vendor_id

             AND pha.vendor_site_id = pvsa.vendor_site_id

             AND pha.authorization_status = 'APPROVED'

             AND pha.type_lookup_code = 'STANDARD'

             AND pha.creation_date BETWEEN :dt1 AND :dt2) po_count

       , (SELECT SUM(pla.quantity*pla.unit_price)

            FROM po.po_headers_all pha

               , po.po_lines_all pla

           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 pha.authorization_status = 'APPROVED'

             AND pha.type_lookup_code = 'STANDARD'

             AND pha.creation_date BETWEEN :dt1 AND :dt2) po_value            

       , (SELECT MAX(pha.creation_date)

            FROM po.po_headers_all pha

           WHERE pha.vendor_id = pv.vendor_id

             AND pha.vendor_site_id = pvsa.vendor_site_id

             AND pha.authorization_status = 'APPROVED'

             AND pha.type_lookup_code = 'STANDARD'

             AND pha.creation_date BETWEEN :dt1 AND :dt2) latest_po_date

       , ROUND(

              SYSDATE

            - (SELECT MAX(pha.creation_date)

                 FROM po.po_headers_all pha

                WHERE pha.vendor_id = pv.vendor_id

                  AND pha.vendor_site_id = pvsa.vendor_site_id

                  AND pha.authorization_status = 'APPROVED'

                  AND pha.type_lookup_code = 'STANDARD'

                  AND pha.creation_date BETWEEN :dt1 AND :dt2)

          , 2

         ) days_since_last_po -- NVOICES

       , (SELECT COUNT(*)

            FROM ap.ap_invoices_all aia

           WHERE aia.vendor_id = pv.vendor_id

             AND aia.vendor_site_id = pvsa.vendor_site_id

             AND aia.creation_date BETWEEN :dt1 AND :dt2) inv_count

       , (SELECT SUM(aia.invoice_amount) inv_total

            FROM ap.ap_invoices_all aia

           WHERE aia.vendor_id = pv.vendor_id

             AND aia.vendor_site_id = pvsa.vendor_site_id

             AND aia.creation_date BETWEEN :dt1 AND :dt2) inv_value

       , (SELECT MAX(aia.creation_date)

            FROM ap.ap_invoices_all aia

           WHERE aia.vendor_id = pv.vendor_id

             AND aia.vendor_site_id = pvsa.vendor_site_id

             AND aia.creation_date BETWEEN :dt1 AND :dt2) latest_inv_date

       , ROUND(

              SYSDATE

            - (SELECT MAX(aia.creation_date)

                 FROM ap.ap_invoices_all aia

                WHERE aia.vendor_id = pv.vendor_id

                  AND aia.vendor_site_id = pvsa.vendor_site_id

                  AND aia.creation_date BETWEEN :dt1 AND :dt2)

          , 2

         ) days_since_last_inv

    FROM ap.ap_suppliers pv                -- use ap.ap_suppliers for r12

       , ap.ap_supplier_sites_all pvsa            -- use ap.ap_supplier_sites_all for r12

   WHERE pv.vendor_id = pvsa.vendor_id

     AND NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE

     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE

ORDER BY pv.vendor_name

       , pvsa.vendor_site_code;    

 

Count Sites per Supplier

 SELECT   pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1 supplier_number
       , COUNT(pvsa.vendor_site_id) site_count
    FROM po.po_vendors pv
       , po.po_vendor_sites_all pvsa
   WHERE pv.vendor_id = pvsa.vendor_id
     AND NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
     AND pvsa.org_id IN(7041)
GROUP BY pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1
ORDER BY pv.vendor_name;

 

Standard Supplier Details

SELECT DISTINCT bus_gp.NAME org
              , pv.vendor_name supplier
              , pv.segment1 supno
              , pv.vendor_type_lookup_code
              , pvsa.vendor_site_code site
              , pvsa.email_address email_to_send_pos_to
              , pvsa.purchasing_site_flag
              , pvsa.pay_site_flag
              , pvsa.address_line1
              , pvsa.address_line2
              , pvsa.address_line3
              , pvsa.city
              , pvsa.state
              , pvsa.zip post_code
              , '-------- CONTACT DETAILS -----------'
              , pvc.first_name contact_first_name
              , pvc.last_name contact_last_name
              , pvc.area_code || ' ' || pvc.phone contact_phone
              , pvc.email_address contact_email
              , pvc.fax_area_code || ' ' || pvc.fax contact_fax
           FROM ap.ap_suppliers pv
              , ap.ap_supplier_sites_all pvsa
              , ap.ap_supplier_contacts pvc
              , hr.hr_all_organization_units_tl bus_gp
          WHERE pv.vendor_id = pvsa.vendor_id
            AND pvsa.vendor_site_id = pvc.vendor_site_id(+)
            AND pvsa.org_id = bus_gp.organization_id
            AND pvsa.inactive_date IS NULL
            AND pv.end_date_active IS NULL
            AND pvc.inactive_date IS NULL
ORDER BY        bus_gp.NAME
              , pv.vendor_name
              , pvsa.vendor_site_code;