Oracle Supplier Extract Queries

------------------------------------------------------------

          /* Vendors Extract Query */

------------------------------------------------------------

--> Oracle Vendors

SELECT pv.vendor_id,

       pv.vendor_name,

       pv.vendor_name_alt,

       pv.segment1,

       pv.employee_id,

       NULL employee_number,

       pv.vendor_type_lookup_code,

       pv.customer_num,

       pv.one_time_flag,

       pv.terms_id,

       t.NAME terms_name,

       pv.set_of_books_id,

       pv.pay_date_basis_lookup_code,

       pv.pay_group_lookup_code,

       pv.payment_priority,

       pv.invoice_currency_code,

       pv.payment_currency_code,

       pv.hold_all_payments_flag,

       pv.hold_reason,

       pv.num_1099,

       pv.type_1099,

       pv.withholding_status_lookup_code,

       pv.withholding_start_date,

       pv.organization_type_lookup_code,

       pv.start_date_active,

       pv.end_date_active,

       pv.payment_method_lookup_code,

       pv.terms_date_basis,

       pv.receipt_required_flag,

       pv.qty_rcv_tolerance,

       pv.qty_rcv_exception_code,

       pv.enforce_ship_to_location_code,

       pv.days_early_receipt_allowed,

       pv.days_late_receipt_allowed,

       pv.receipt_days_exception_code,

       pv.exclusive_payment_flag,

       pv.auto_tax_calc_flag,

       pv.auto_tax_calc_override,

       pv.amount_includes_tax_flag,

       pv.tax_verification_date,

       pv.state_reportable_flag,

       pv.federal_reportable_flag,

       pv.attribute_category,

       pv.attribute1,

       pv.attribute2,

       pv.attribute3,

       pv.attribute4,

       pv.attribute5,

       pv.attribute6,

       pv.attribute7,

       pv.attribute8,

       pv.attribute9,

       pv.attribute10,

       pv.attribute11,

       pv.attribute12,

       pv.attribute13,

       pv.attribute14,

       pv.attribute15,

       pv.vat_registration_num,

       pv.exclude_freight_from_discount,

       pv.tax_reporting_name,

       pv.allow_awt_flag,

       pv.awt_group_id,

       awg.NAME awg_group_name,

       pv.match_option,

       pv.create_debit_memo_flag,

       pv.match_status_flag,

       pv.individual_1099,

       NULL business_group_id,

       NULL business_group_name

  FROM po_vendors pv, ap_terms_tl t, ap_awt_groups awg

 WHERE 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 t.term_id(+) = pv.terms_id

       AND NVL (pv.vendor_type_lookup_code, -1) <> 'EMPLOYEE'

       AND pv.awt_group_id = awg.GROUP_ID(+)

       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

--> Oracle Employees

SELECT pv.vendor_id,

       pv.vendor_name,

       pv.vendor_name_alt,

       pv.segment1,

       pv.employee_id,

       ppf.employee_number,

       pv.vendor_type_lookup_code,

       pv.customer_num,

       pv.one_time_flag,

       pv.terms_id,

       t.NAME terms_name,

       pv.set_of_books_id,

       pv.pay_date_basis_lookup_code,

       pv.pay_group_lookup_code,

       pv.payment_priority,

       pv.invoice_currency_code,

       pv.payment_currency_code,

       pv.hold_all_payments_flag,

       pv.hold_reason,

       pv.num_1099,

       pv.type_1099,

       pv.withholding_status_lookup_code,

       pv.withholding_start_date,

       pv.organization_type_lookup_code,

       pv.start_date_active,

       pv.end_date_active,

       pv.payment_method_lookup_code,

       pv.terms_date_basis,

       pv.receipt_required_flag,

       pv.qty_rcv_tolerance,

       pv.qty_rcv_exception_code,

       pv.enforce_ship_to_location_code,

       pv.days_early_receipt_allowed,

       pv.days_late_receipt_allowed,

       pv.receipt_days_exception_code,

       pv.exclusive_payment_flag,

       pv.auto_tax_calc_flag,

       pv.auto_tax_calc_override,

       pv.amount_includes_tax_flag,

       pv.tax_verification_date,

       pv.state_reportable_flag,

       pv.federal_reportable_flag,

       pv.attribute_category,

       pv.attribute1,

       pv.attribute2,

       pv.attribute3,

       pv.attribute4,

       pv.attribute5,

       pv.attribute6,

       pv.attribute7,

       pv.attribute8,

       pv.attribute9,

       pv.attribute10,

       pv.attribute11,

       pv.attribute12,

       pv.attribute13,

       pv.attribute14,

       pv.attribute15,

       pv.vat_registration_num,

       pv.exclude_freight_from_discount,

       pv.tax_reporting_name,

       pv.allow_awt_flag,

       pv.awt_group_id,

       awg.NAME awg_group_name,

       pv.match_option,

       pv.create_debit_memo_flag,

       pv.match_status_flag,

       pv.individual_1099,

       NULL business_group_id,

       bg.NAME business_group_name

  FROM po_vendors pv,

       ap_terms_tl t,

       apps.per_people_x ppf,

       per_person_types ppt,

       apps.per_assignments_x ppa,

       per_business_groups bg,

       ap_awt_groups awg

 WHERE pv.enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (pv.start_date_active, SYSDATE) AND NVL (pv.end_date_active, SYSDATE)

       AND t.term_id(+) = pv.terms_id

       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 ppf.BUSINESS_GROUP_ID = bg.BUSINESS_GROUP_ID

       AND pv.awt_group_id = awg.GROUP_ID(+);