Saturday, July 15, 2017

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
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,
       apps.per_people_x ppf,
       per_person_types ppt,
       apps.per_assignments_x ppa
 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 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';


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect