AP Supplier List Report
SELECT
l.ou_number,
l.supplier_name,
l.supplier_number,
l.alternate_supplier_name,
l.supplier_creation_date,
l.site_code,
l.alternate_supplier_site,
l.supplier_type,
l.site_pay_method,
l.pay_group,
l.supplier_end_date,
l.supplier_site_end_date,
l.site_last_updated_by,
l.organization_type,
l.name_control,
l.pay_instrument_end_date,
l.vat_registration_number,
l.bank_type,
l.phone_number,
l.fax_number,
l.fed_tax_id,
l.vat_code,
l.address_country,
l.bank_country_code,
l.bank_account_name,
l.alt_bank_account_name,
l.alt_bank_name,
l.alt_branch_name,
l.branch_number,
l.branch_name,
l.bank_charge,
l.bank_name,
l.bank_number,
l.invoice_currency,
l.payment_currency,
l.pay_method,
l.payment_priority,
l.site_payment_term,
l.bank_account_currency,
l.bank_acct_num_last_4,
l.remit_email,
l.po_email_address,
l.hold_all_payments,
l.hdr_hold_all_payments,
l.site_hold_future_pmts,
l.hdr_on_hold_flag,
l.hdr_hold_future_pmts,
l.hdr_hold_reason,
l.mission_statement,
l.address_state,
l.site_last_update_date,
l.supplier_last_update_date,
l.allow_witholding_tax,
l.withholding_tax_name,
l.address_line1,
l.address_line2,
l.address_line3,
l.address_line4,
l.address_city,
l.address_province,
l.zip_postal_code,
l.bank_account_end_date,
l.payment_allowed,
l.alternate_pay_site,
l.vendor_site_id,
l.purchasing_allowed,
l.pay_on,
l.match_option,
l.vendor_id
FROM
(
SELECT /*+ Parallel (8) */
hou.short_code ou_number,
vh.segment1 supplier_number,
vh.vendor_name supplier_name,
vh.VENDOR_NAME_ALT alternate_supplier_name,
vh.vendor_type_lookup_code supplier_type,
vs.attribute3 mfg_area,
vs.attribute14 legacy_supplier_number,
vs.vendor_site_code site_code,
vs.vendor_site_code_alt alternate_supplier_site,
--vs.vat_registration_num vat_registration_number,
null vat_registration_number,
apt.name site_payment_term,
vs.purchasing_site_flag purchasing_allowed,
vs.pay_site_flag payment_allowed,
vh.small_business_flag small_business,
vs.pay_on_code pay_on,
DECODE(vs.match_option,'R','Receipt','Purchase Order') match_option,
alt.vendor_site_code alternate_pay_site,
vs.address_lines_alt alternate_address,
vs.address_line1 address_line1,
vs.address_line2 address_line2,
vs.address_line3 address_line3,
vs.address_line4 address_line4,
vs.city address_city,
vs.state address_state,
vs.province address_province,
vs.zip zip_postal_code,
vs.country address_country,
vs.phone phone_number,
vs.fax fax_number,
vs.email_address po_email_address,
vs.terms_date_basis terms_date_base,
--vs.vat_code vat_code,
null vat_code,
gl1.concatenated_segments ap_liability_account,
gl2.concatenated_segments prepayment_account,
gl3.concatenated_segments future_payment_account,
vs.invoice_currency_code invoice_currency,
vs.payment_currency_code payment_currency,
vh.allow_awt_flag allow_witholding_tax,
vs.pay_group_lookup_code pay_group,
iep.iep_default_payment_method pay_method,
iep.ipm_payment_method_code site_pay_method,
iep.iep_payment_reason_code pay_reason,
DECODE(vs.bank_charge_bearer,'I','ON pay','Supplier Pay') bank_charge,
--vs.exclusive_payment_flag pay_alone,
null pay_alone,
bac.acc_bank_account_name bank_account_name,
bac.acc_bank_account_name_alt alt_bank_account_name,
bac.acc_bank_number bank_number,
bac.acc_bank_name bank_name,
bac.acc_bank_name_alt alt_bank_name,
bac.acc_branch_number branch_number,
bac.acc_branch_name branch_name,
bac.acc_bank_branch_name_alt alt_branch_name,
bac.acc_branch_province branch_province,
bac.acc_branch_city branch_city,
bac.acc_country_code bank_country_code,
bac.acc_currency_code bank_account_currency,
substr(bac.acc_bank_account_num, (length(bac.acc_bank_account_num) - 3),4) bank_acct_num_last_4,
bac.iao_primary_flag primary_flag,
iep.ipi_order_of_preference preference,
bac.acc_bank_account_type bank_type,
trunc(bac.acc_start_date) bank_account_start_date,
trunc(bac.acc_end_date) bank_account_end_date,
iep.iep_remit_delivery_method remit_deliver_method,
iep.iep_remit_advice_email remit_email,
vh.end_date_active supplier_end_date,
CASE
WHEN nvl(vh.end_date_active,SYSDATE + 1) > SYSDATE THEN 'ACTIVE'
ELSE 'INACTIVE'
END
supplier_status,
vs.inactive_date supplier_site_end_date,
CASE
WHEN nvl(vs.inactive_date,SYSDATE + 1) > SYSDATE THEN 'ACTIVE'
ELSE 'INACTIVE'
END
supplier_site_status,
iep.ipi_end_date pay_instrument_end_date,
vh.creation_date supplier_creation_date,
--fhc.description supplier_created_by,
fhc.username supplier_created_by,
vh.last_update_date supplier_last_update_date,
--fhu.description supplier_last_updated_by,
fhu.username supplier_last_updated_by,
vs.creation_date site_creation_date,
--fsc.description site_created_by,
fsc.username site_created_by,
vs.last_update_date site_last_update_date,
--fsu.description site_last_updated_by,
fsu.username site_last_updated_by,
bac.acc_creation_date bank_account_creation_date,
bac.acc_created_by bank_account_created_by,
bac.acc_last_update_date bank_account_update_date,
bac.acc_last_updated_by bank_account_updated_by,
vs.PRC_BU_ID org_id,
vh.vendor_id vendor_id,
vh.party_id party_id,
vs.vendor_site_id vendor_site_id,
vs.party_site_id party_site_id,
iep.iep_ext_payee_id ext_payee_id,
iep.ipi_instrument_payment_use_id instr_pmt_use_id,
bac.iao_account_owner_id acct_owner_id,
bac.ext_bank_account_id ext_bank_acct_id,
bac.acc_bank_id acc_bank_id,
bac.acc_branch_id acc_branch_id,
vh.TAX_REPORTING_NAME withholding_tax_name,
vh.TAX_REPORTING_NAME withholding_tax_description,
vh.organization_type_lookup_code organization_type,
CASE
WHEN bac.org_id = 3236 THEN bac.temp_column
ELSE NULL
END
temp_column,
vs.purchasing_hold_reason purchasing_hold_reason,
vs.create_debit_memo_flag supp_create_debit_memo,
vs.qty_rcv_tolerance qty_rcv_tolerance,
vs.terms_date_basis supp_terms_date_basis,
vh.TYPE_1099 fed_tax_id,
vh.women_owned_flag women_owned,
vs.hold_all_payments_flag hdr_hold_all_payments,
vs.hold_date hdr_hold_date,
vs.hold_flag hdr_on_hold_flag,
vs.hold_future_payments_flag hdr_hold_future_pmts,
vs.hold_reason hdr_hold_reason,
hdt.name hdr_payment_term,
hzp.mission_statement mission_statement,
vs.create_debit_memo_flag site_create_debit_memo,
vs.pay_on_receipt_summary_code invoice_summary_level,
vs.supplier_notif_method supplier_notif_method,
vs.purchasing_site_flag purchasing_site_flag,
vs.pay_site_flag pay_site_flag,
vs.hold_all_payments_flag hold_all_payments,
vs.hold_future_payments_flag site_hold_future_pmts,
vs.hold_unmatched_invoices_flag hold_unmatched_invoices,
vs.fob_lookup_code fob_code,
vs.freight_terms_lookup_code freight_terms_code,
vs.ship_via_lookup_code ship_via_code,
DECODE(vs.receipt_required_flag,'N',DECODE(vs.inspection_required_flag,'N','2-Way','2-Way'),'Y',DECODE(vs.inspection_required_flag
,'N','3-Way','Y','4-Way','3-Way') ) match_approval_level,
lcs.location_code dflt_ship_to_loc_cd,
lcb.location_code dflt_bill_to_loc_cd,
vh.name_control name_control,
vs.payment_priority payment_priority,
vs.attribute8 supplier_additional_email
FROM
poz_suppliers_v vh,
poz_supplier_sites_v vs,
poz_supplier_sites_v alt,
poz_site_assignments_all_x vss,
ap_awt_groups awt,
(SELECT
iep.org_id org_id,
iep.payee_party_id party_id,
iep.supplier_site_id vendor_site_id,
iep.party_site_id party_site_id,
iep.ext_payee_id iep_ext_payee_id,
iep.default_payment_method_code iep_default_payment_method,
ipm.payment_method_code ipm_payment_method_code,
iep.remit_advice_delivery_method iep_remit_delivery_method,
iep.remit_advice_email iep_remit_advice_email,
iep.creation_date iep_creation_date,
iep.created_by iep_created_by,
iep.last_update_date iep_last_update_date,
iep.last_updated_by iep_last_updated_by,
pis.ext_pmt_party_id ipi_ext_pmt_party_id,
pis.instrument_payment_use_id ipi_instrument_payment_use_id,
pis.instrument_id ipi_instrument_id,
pis.order_of_preference ipi_order_of_preference,
pis.end_date ipi_end_date,
pis.creation_date ipi_creation_date,
pis.created_by ipi_created_by,
pis.last_update_date ipi_last_update_date,
pis.last_updated_by ipi_last_updated_by,
ieb.bank_account_num ieb_bank_account_num,
ieb.iban ieb_iban,
iep.payment_reason_code iep_payment_reason_code
FROM
iby_external_payees_all iep,
iby_pmt_instr_uses_all pis,
iby_ext_bank_accounts ieb,
iby_ext_party_pmt_mthds ipm
WHERE
iep.org_id IS NOT NULL
AND ipm.ext_pmt_party_id (+) = iep.ext_payee_id
AND ipm.primary_flag (+) = 'Y'
AND nvl(pis.end_date(+),SYSDATE + 9999) > SYSDATE
AND iep.ext_payee_id = pis.ext_pmt_party_id (+)
AND ieb.ext_bank_account_id (+) = pis.instrument_id
) iep, /*External Payee*/
(
SELECT
vs.PRC_BU_ID org_id,
vh.vendor_id vendor_id,
vh.party_id party_id,
vs.vendor_site_id vendor_site_id,
vs.party_site_id party_site_id,
iao.ext_bank_account_id ext_bank_account_id,
iao.account_owner_party_id account_owner_party_id,
iao.account_owner_id iao_account_owner_id,
iao.end_date iao_end_date,
iao.primary_flag iao_primary_flag,
iao.creation_date iao_creation_date,
iao.created_by iao_created_by_id,
--foc.description iao_created_by,
foc.username iao_created_by,
iao.last_update_date iao_last_update_date,
iao.last_updated_by iao_last_updated_by_id,
fou.username iao_last_updated_by,
acc.country_code acc_country_code,
acc.branch_id acc_branch_id,
acc.bank_id acc_bank_id,
acc.bank_account_name acc_bank_account_name,
acc.bank_account_name_alt acc_bank_account_name_alt,
acc.bank_account_num acc_bank_account_num,
acc.currency_code acc_currency_code,
acc.bank_account_type acc_bank_account_type,
acc.start_date acc_start_date,
acc.end_date acc_end_date,
acc.bank_name acc_bank_name,
acc.bank_number acc_bank_number,
acc.branch_name acc_branch_name,
acc.branch_number acc_branch_number,
hp.province acc_branch_province,
hp.city acc_branch_city,
acc.bank_branch_name_alt acc_bank_branch_name_alt,
acc.bank_name_alt acc_bank_name_alt,
acc.creation_date acc_creation_date,
acc.created_by acc_created_by_id,
fac.username acc_created_by,
acc.last_update_date acc_last_update_date,
acc.last_updated_by acc_last_updated_by_id,
fau.username acc_last_updated_by,
acc.bank_account_num temp_column
FROM
iby_account_owners iao,
iby_payee_all_bankacct_v acc,
poz_suppliers_v vh,
poz_supplier_sites_v vs,
per_users foc,
per_users fou,
per_users fac,
per_users fau,
hz_parties hp
WHERE
iao.ext_bank_account_id = acc.ext_bank_account_id
AND iao.account_owner_party_id = acc.party_id
AND iao.created_by = foc.username
AND iao.last_updated_by = fou.username
AND iao.account_owner_party_id = vh.party_id
AND vh.vendor_id = vs.vendor_id
AND nvl(iao.end_date,SYSDATE + 9999) > SYSDATE
AND acc.created_by = fac.username
AND acc.last_updated_by = fau.username
AND nvl(acc.end_date,SYSDATE + 9999) > SYSDATE
AND acc.branch_id = hp.party_id (+)
AND 'ORGANIZATION' = hp.party_type (+)
) bac,
hr_operating_units hou,
ap_terms apt,
ap_terms hdt,
gl_code_combinations gl1,
gl_code_combinations gl2,
gl_code_combinations gl3,
per_users fhc,
per_users fhu,
per_users fsc,
per_users fsu,
hz_parties hzp,
hr_locations_all lcs,
hr_locations_all lcb
WHERE
vh.created_by = fhc.username
AND vh.last_updated_by = fhu.username
AND vh.vendor_id = vs.vendor_id
AND awt.group_id (+) = vh.awt_group_id
AND nvl(vh.vendor_type_lookup_code,'X') != 'EMPLOYEE'
AND vs.prc_bu_id = hou.organization_id
AND vs.terms_id = apt.term_id (+)
AND vs.terms_id = hdt.term_id (+)
AND vs.vendor_site_id = vss.vendor_site_id
AND vss.accts_pay_code_combination_id = gl1.code_combination_id (+)
AND vss.prepay_code_combination_id = gl2.code_combination_id (+)
AND vss.future_dated_payment_ccid = gl3.code_combination_id (+)
AND vs.created_by = fsc.username
AND vs.last_updated_by = fsu.username
AND vs.default_pay_site_id = alt.vendor_site_id (+)
AND vs.prc_bu_id = iep.org_id (+)
AND vs.vendor_site_id = iep.vendor_site_id (+)
AND iep.org_id = bac.org_id (+)
AND iep.vendor_site_id = bac.vendor_site_id (+)
AND iep.ipi_instrument_id = bac.ext_bank_account_id (+)
AND hzp.party_id (+) = vh.party_id
AND lcs.location_id (+) = vs.location_id
AND lcb.location_id (+) = vs.location_id
UNION
SELECT
hou.short_code ou_number,
vh.segment1 supplier_number,
vh.vendor_name supplier_name,
vh.VENDOR_NAME_ALT alternate_supplier_name,
vh.vendor_type_lookup_code supplier_type,
vs.attribute3 mfg_area,
vs.attribute14 legacy_supplier_number,
vs.vendor_site_code site_code,
vs.vendor_site_code_alt alternate_supplier_site,
--vs.vat_registration_num vat_registration_number,
null vat_registration_number,
apt.name site_payment_term,
vs.purchasing_site_flag purchasing_allowed,
vs.pay_site_flag payment_allowed,
vh.small_business_flag small_business,
vs.pay_on_code pay_on,
DECODE(vs.match_option,'R','Receipt','Purchase Order') match_option,
alt.vendor_site_code alternate_pay_site,
vs.address_lines_alt alternate_address,
vs.address_line1 address_line1,
vs.address_line2 address_line2,
vs.address_line3 address_line3,
vs.address_line4 address_line4,
vs.city address_city,
vs.state address_state,
vs.province address_province,
vs.zip zip_postal_code,
vs.country address_country,
vs.phone phone_number,
vs.fax fax_number,
vs.email_address po_email,
vs.terms_date_basis terms_date_base,
--vs.vat_code vat_code,
null vat_code,
gl1.concatenated_segments ap_liability_account,
gl2.concatenated_segments prepayment_account,
gl3.concatenated_segments future_payment_account,
vs.invoice_currency_code invoice_currency,
vs.payment_currency_code payment_currency,
vh.allow_awt_flag allow_witholding_tax,
vs.pay_group_lookup_code pay_group,
iep.ipm_payment_method_code pay_method,
iep.ipm_payment_method_code site_pay_method,
iep.iep_payment_reason_code pay_reason,
DECODE(vs.bank_charge_bearer,'I','ON pay','Supplier Pay') bank_charge,
--vs.exclusive_payment_flag pay_alone,
null pay_alone,
bac.acc_bank_account_name bank_account_name,
bac.acc_bank_account_name_alt alt_bank_account_name,
bac.acc_bank_number bank_number,
bac.acc_bank_name bank_name,
bac.acc_bank_name_alt alt_bank_name,
bac.acc_branch_number branch_number,
bac.acc_branch_name branch_name,
bac.acc_bank_branch_name_alt alt_branch_name,
bac.acc_branch_province branch_province,
bac.acc_branch_city branch_city,
bac.acc_country_code bank_country_code,
bac.acc_currency_code bank_account_currency,
substr(bac.acc_bank_account_num, (length(bac.acc_bank_account_num) - 3),4) bank_acct_num_last_4,
bac.iao_primary_flag primary_flag,
iep.ipi_order_of_preference preference,
bac.acc_bank_account_type bank_account_type,
trunc(bac.acc_start_date) bank_account_start_date,
trunc(bac.acc_end_date) bank_account_end_date,
iep.iep_remit_delivery_method remit_deliver_method,
iep.iep_remit_advice_email remit_email,
vh.end_date_active supplier_end_date,
CASE
WHEN nvl(vh.end_date_active,SYSDATE + 1) > SYSDATE THEN 'ACTIVE'
ELSE 'INACTIVE'
END
supplier_status,
vs.inactive_date supplier_site_end_date,
CASE
WHEN nvl(vs.inactive_date,SYSDATE + 1) > SYSDATE THEN 'ACTIVE'
ELSE 'INACTIVE'
END
supplier_site_status,
bac.instr_end_date pay_instrument_end_date,
vh.creation_date supplier_creation_date,
fhc.username supplier_created_by,
vh.last_update_date supplier_last_update_date,
fhu.username supplier_last_updated_by,
vs.creation_date site_creation_date,
fsc.username site_created_by,
vs.last_update_date site_last_update_date,
fsu.username site_last_updated_by,
bac.acc_creation_date bank_account_creation_date,
bac.acc_created_by bank_account_created_by,
bac.acc_last_update_date bank_account_update_date,
bac.acc_last_updated_by bank_account_updated_by,
vs.prc_bu_id org_id,
vh.vendor_id vendor_id,
vh.party_id party_id,
vs.vendor_site_id vendor_site_id,
vs.party_site_id party_site_id,
iep.iep_ext_payee_id ext_payee_id,
iep.ipi_instrument_payment_use_id instr_pmt_use_id,
bac.iao_account_owner_id acct_owner_id,
bac.ext_bank_account_id ext_bank_acct_id,
bac.acc_bank_id acc_bank_id,
bac.acc_branch_id acc_branch_id,
vh.TAX_REPORTING_NAME withholding_tax_name,
vh.TAX_REPORTING_NAME withholding_tax_description,
vh.organization_type_lookup_code organization_type,
bac.temp_column temp_column,
vs.purchasing_hold_reason purchasing_hold_reason,
vs.create_debit_memo_flag supp_create_debit_memo,
vs.qty_rcv_tolerance qty_rcv_tolerance,
vs.terms_date_basis supp_terms_date_basis,
vh.TYPE_1099 fed_tax_id,
vh.women_owned_flag women_owned,
vs.hold_all_payments_flag hdr_hold_all_payments,
vs.hold_date hdr_hold_date,
vs.hold_flag hdr_on_hold_flag,
vs.hold_future_payments_flag hdr_hold_future_pmts,
vs.hold_reason hdr_hold_reason,
hdt.name hdr_payment_term,
hzp.mission_statement mission_statement,
vs.create_debit_memo_flag site_create_debit_memo,
vs.pay_on_receipt_summary_code invoice_summary_level,
vs.supplier_notif_method supplier_notif_method,
vs.purchasing_site_flag purchasing_site_flag,
vs.pay_site_flag pay_site_flag,
vs.hold_all_payments_flag hold_all_payments,
vs.hold_future_payments_flag site_hold_future_pmts,
vs.hold_unmatched_invoices_flag hold_unmatched_invoices,
vs.fob_lookup_code fob_code,
vs.freight_terms_lookup_code freight_terms_code,
vs.ship_via_lookup_code ship_via_code,
DECODE(vs.receipt_required_flag,'N',DECODE(vs.inspection_required_flag,'N','2-Way','2-Way'),'Y',DECODE(vs.inspection_required_flag
,'N','3-Way','Y','4-Way','3-Way') ) match_approval_level,
lcs.location_code dflt_ship_to_loc_cd,
lcb.location_code dflt_bill_to_loc_cd,
vh.name_control name_control,
vs.payment_priority payment_priority,
vs.attribute8 supplier_additional_email
FROM
poz_suppliers_v vh,
poz_supplier_sites_v vs,
poz_supplier_sites_v alt,
ap_awt_groups awt,
(SELECT
iep.org_id org_id,
iep.payee_party_id party_id,
iep.supplier_site_id vendor_site_id,
iep.party_site_id party_site_id,
iep.ext_payee_id iep_ext_payee_id,
iep.default_payment_method_code iep_default_payment_method,
ipm.payment_method_code ipm_payment_method_code,
iep.remit_advice_delivery_method iep_remit_delivery_method,
iep.remit_advice_email iep_remit_advice_email,
iep.creation_date iep_creation_date,
iep.created_by iep_created_by,
iep.last_update_date iep_last_update_date,
iep.last_updated_by iep_last_updated_by,
pis.ext_pmt_party_id ipi_ext_pmt_party_id,
pis.instrument_payment_use_id ipi_instrument_payment_use_id,
pis.instrument_id ipi_instrument_id,
pis.order_of_preference ipi_order_of_preference,
pis.end_date ipi_end_date,
pis.creation_date ipi_creation_date,
pis.created_by ipi_created_by,
pis.last_update_date ipi_last_update_date,
pis.last_updated_by ipi_last_updated_by,
ieb.bank_account_num ieb_bank_account_num,
ieb.iban ieb_iban,
iep.payment_reason_code iep_payment_reason_code
FROM
iby_external_payees_all iep,
iby_pmt_instr_uses_all pis,
iby_ext_bank_accounts ieb,
iby_ext_party_pmt_mthds ipm
WHERE
iep.org_id IS NOT NULL
AND ipm.ext_pmt_party_id (+) = iep.ext_payee_id
AND ipm.primary_flag (+) = 'Y'
AND nvl(pis.end_date(+),SYSDATE + 9999) > SYSDATE
AND iep.ext_payee_id = pis.ext_pmt_party_id (+)
AND ieb.ext_bank_account_id (+) = pis.instrument_id
) iep, /*External Payee */
(
SELECT
vs.prc_bu_id org_id,
vh.vendor_id vendor_id,
vh.party_id party_id,
vs.vendor_site_id vendor_site_id,
vs.party_site_id party_site_id,
iao.ext_bank_account_id ext_bank_account_id,
iao.account_owner_party_id account_owner_party_id,
iao.account_owner_id iao_account_owner_id,
iao.end_date iao_end_date,
iao.primary_flag iao_primary_flag,
iao.creation_date iao_creation_date,
iao.created_by iao_created_by_id,
foc.username iao_created_by,
iao.last_update_date iao_last_update_date,
iao.last_updated_by iao_last_updated_by_id,
fou.username iao_last_updated_by,
acc.country_code acc_country_code,
acc.branch_id acc_branch_id,
acc.bank_id acc_bank_id,
acc.bank_account_name acc_bank_account_name,
acc.bank_account_name_alt acc_bank_account_name_alt,
'xxxxx' acc_bank_account_num,
acc.currency_code acc_currency_code,
acc.bank_account_type acc_bank_account_type,
acc.start_date acc_start_date,
acc.end_date acc_end_date,
acc.bank_name acc_bank_name,
acc.bank_number acc_bank_number,
acc.branch_name acc_branch_name,
acc.branch_number acc_branch_number,
hp.province acc_branch_province,
hp.city acc_branch_city,
acc.bank_branch_name_alt acc_bank_branch_name_alt,
acc.bank_nam
