Saturday, July 15, 2017

Oracle Supplier, Supplier Site, Supplier Contact and Supplier Bank Account 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(+);


------------------------------------------------------------
        /* 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';


------------------------------------------------------------
    /* 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;


------------------------------------------------------------
       /* Bank Branches Extract Query */
------------------------------------------------------------
-- Bank branches            
SELECT *
  FROM (SELECT abb.bank_branch_id,
               mdrx_get_bank_branch_id (abb.bank_branch_id)
                  bank_branch_id_new,
               abb.bank_name,
               abb.bank_branch_name,
               REPLACE (abb.description, CHR (10), ' ') description,
               abb.address_line1,
               abb.address_line2,
               abb.address_line3,
               abb.city,
               abb.state,
               abb.zip,
               abb.province,
               abb.country,
               abb.area_code,
               abb.phone,
               abb.contact_first_name,
               abb.contact_middle_name,
               abb.contact_last_name,
               abb.contact_prefix,
               abb.contact_title,
               abb.bank_num,
               abb.institution_type,
               abb.address_style,
               abb.bank_number,
               abb.address_line4,
               abb.county,
               abb.eft_user_number,
               abb.eft_swift_code,
               abb.end_date,
               abb.edi_id_number,
               abb.bank_branch_type,
               abb.bank_name_alt,
               abb.bank_branch_name_alt,
               abb.address_lines_alt,
               abb.active_date,
               abb.tp_header_id,
               abb.ece_tp_location_code,
               abb.rfc_identifier,
               abb.bank_admin_email
          FROM ap_bank_branches abb                                     --1723
         WHERE NVL (abb.end_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
               AND EXISTS
                      (SELECT *
                         FROM ap_bank_accounts_all aba
                        WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
                              AND aba.bank_branch_id = abb.bank_branch_id)) a
 WHERE a.bank_branch_id = a.bank_branch_id_new;


------------------------------------------------------------
       /* Bank Account Extract Query */
------------------------------------------------------------
  SELECT aba.bank_account_id,
         aba.bank_account_name,
         aba.bank_account_num,
         --aba.bank_branch_id,
         bb.bank_num,
         mdrx_get_bank_branch_id (aba.bank_branch_id) bank_branch_id,
         (SELECT hou.NAME
            FROM hr_operating_units hou
           WHERE hou.organization_id = aba.org_id)
            operating_unit_name,
         aba.set_of_books_id,
         aba.currency_code,
         aba.description,
         aba.contact_first_name,
         aba.contact_middle_name,
         aba.contact_last_name,
         aba.contact_prefix,
         aba.contact_title,
         aba.contact_area_code,
         aba.contact_phone,
         aba.bank_account_type,
         aba.attribute_category,
         aba.attribute1,
         aba.check_digits,
         aba.org_id,
         aba.bank_account_name_alt,
         aba.account_holder_name,
         aba.account_holder_name_alt,
         aba.allow_multi_assignments_flag,
         aba.iban_number,
         abau.vendor_id,
         abau.vendor_site_id,
         abau.primary_flag,
         abau.start_date,
         abau.end_date,
         pv.segment1,
         pvs.vendor_site_code
    FROM ap_bank_accounts_all aba,
         ap_bank_account_uses_all abau,
         po_vendors pv,
         po_vendor_sites_all pvs,
         ap_bank_branches bb
   WHERE NVL (aba.inactive_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE)
         AND aba.bank_account_id = abau.external_bank_account_id
         AND TRUNC (SYSDATE) BETWEEN NVL (abau.start_date, TRUNC (SYSDATE)) AND NVL (abau.end_date, TRUNC (SYSDATE))
         AND abau.vendor_id = pv.vendor_id
         AND abau.vendor_site_id = pvs.vendor_site_id(+)
         AND aba.bank_branch_id = bb.bank_branch_id
--AND bb.bank_num = '5623532'

ORDER BY bank_branch_id;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect