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;