Saturday, July 15, 2017

Oracle Supplier Contact Extract Queries

------------------------------------------------------------
    /* Vendor Site Contacts Extract Query */
------------------------------------------------------------
-- Oracle Vendors
SELECT pvc.vendor_contact_id,
       pvc.vendor_site_id,
       pvc.inactive_date,
       pvc.first_name,
       pvc.middle_name,
       pvc.last_name,
       pvc.prefix,
       pvc.title,
       pvc.area_code,
       pvc.phone,
       pvc.contact_name_alt,
       pvc.first_name_alt,
       pvc.last_name_alt,
       pvc.department,
       pvc.email_address,
       pvc.url,
       pvc.alt_area_code,
       pvc.alt_phone,
       pvc.fax_area_code,
       pvc.fax
  FROM po_vendors pv, po_vendor_sites_all pvs, po_vendor_contacts pvc
 WHERE     NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE) AND NVL (pv.end_date_active, SYSDATE)
       AND pv.attribute14 IS NULL
       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE
       AND EXISTS
              (SELECT 1
                 FROM ap_invoices_all i
                WHERE i.vendor_id = pv.vendor_id
                      AND i.invoice_date >= ADD_MONTHS (TRUNC (SYSDATE), -24))
UNION
-- Employees
SELECT pvc.vendor_contact_id,
       pvc.vendor_site_id,
       pvc.inactive_date,
       pvc.first_name,
       pvc.middle_name,
       pvc.last_name,
       pvc.prefix,
       pvc.title,
       pvc.area_code,
       pvc.phone,
       pvc.contact_name_alt,
       pvc.first_name_alt,
       pvc.last_name_alt,
       pvc.department,
       pvc.email_address,
       pvc.url,
       pvc.alt_area_code,
       pvc.alt_phone,
       pvc.fax_area_code,
       pvc.fax
  FROM po_vendors pv,
       po_vendor_sites_all pvs,
       apps.per_people_x ppf,
       per_person_types ppt,
       apps.per_assignments_x ppa,
       po_vendor_contacts pvc
 WHERE     NVL (pvs.inactive_date, SYSDATE) >= SYSDATE
       AND pv.vendor_id = pvs.vendor_id
       AND pv.enabled_flag = 'Y'
       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE) AND NVL (pv.end_date_active, SYSDATE)
       AND NVL (pv.vendor_type_lookup_code, -1) = 'EMPLOYEE'
       AND pv.employee_id = ppf.person_id
       AND ppf.person_type_id = ppt.person_type_id
       AND ppt.system_person_type IN ('EMP')
       AND ppa.person_id = ppf.person_id
       AND ppa.assignment_type = 'E'
       AND pvs.vendor_site_id = pvc.vendor_site_id
       AND NVL (pvc.inactive_date, SYSDATE) >= SYSDATE;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect