Oracle Applications: Oracle Supplier Site Extract Queries

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

/* Vendor Sites Extract Query */

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

-- Oracle Vendors

SELECT pvs.vendor_site_id,

pvs.vendor_id,

pvs.vendor_site_code,

pvs.vendor_site_code_alt,

pvs.purchasing_site_flag,

pvs.rfq_only_site_flag,

pvs.pay_site_flag,

pvs.address_line1,

REPLACE (pvs.address_lines_alt, CHR (10), ' ') address_lines_alt,

pvs.address_line2,

pvs.address_line3,

pvs.city,

pvs.state,

pvs.zip,

pvs.province,

pvs.country,

pvs.area_code,

pvs.phone,

pvs.inactive_date,

pvs.fax,

pvs.fax_area_code,

pvs.telex,

pvs.payment_method_lookup_code,

pvs.terms_date_basis,

pvs.pay_group_lookup_code,

pvs.payment_priority,

pvs.terms_id,

t.NAME terms_name,

pvs.pay_date_basis_lookup_code,

pvs.invoice_currency_code,

pvs.payment_currency_code,

pvs.hold_all_payments_flag,

pvs.hold_reason,

pvs.hold_unmatched_invoices_flag,

pvs.auto_tax_calc_flag,

pvs.auto_tax_calc_override,

pvs.amount_includes_tax_flag,

pvs.exclusive_payment_flag,

pvs.tax_reporting_site_flag,

pvs.attribute_category,

pvs.attribute1,

pvs.attribute2,

pvs.attribute3,

pvs.attribute4,

pvs.attribute5,

pvs.attribute6,

pvs.attribute7,

pvs.attribute8,

pvs.attribute9,

pvs.attribute10,

pvs.attribute11,

pvs.attribute12,

pvs.attribute13,

pvs.attribute14,

pvs.attribute15,

pvs.exclude_freight_from_discount,

pvs.vat_registration_num,

pvs.offset_vat_code,

pvs.org_id,

(SELECT hou.NAME

FROM hr_operating_units hou

WHERE hou.organization_id = pvs.org_id)

operating_unit,

pvs.address_line4,

pvs.county,

pvs.address_style,

pvs.LANGUAGE,

pvs.allow_awt_flag,

pvs.awt_group_id,

pvs.match_option,

pvs.country_of_origin_code,

pvs.create_debit_memo_flag,

pvs.supplier_notif_method,

pvs.email_address,

pvs.remittance_email,

pvs.primary_pay_site_flag,

pvs.tolerance_id,

pvs.services_tolerance_id

FROM po_vendors pv, po_vendor_sites_all pvs, ap_terms_tl t

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

-- Employees