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