AP Supplier Upload

➜ Back to Library
Description

Overview of the AP Supplier Upload

1. Purpose

The AP Supplier Upload enables automatic creation of a bank and/or bank branch for a supplier account if they do not already exist within Oracle EBS. This upload ensures that supplier bank account details are accurately recorded and linked to the supplier, provided the necessary fields are completed.

2. Required Fields for Bank Account Creation or Update

To create or update bank account details, the following fields must be populated in the AP Supplier Upload:

  • Bank Name and/or Bank Number
  • Bank Branch Name and/or Branch Number
  • Bank Account Name and/or Bank Account Number

If these fields are populated, the AP Supplier Upload will invoke standard Oracle APIs to validate the information. Upon successful validation, the Oracle APIs will proceed to create or update the bank, branch, and bank account details.

3. Country-Specific Validations

Oracle EBS applies country-specific validations that dictate the required information and its format. These validations ensure compliance with local banking regulations, such as format checks for routing transit numbers in the U.S. and Canada.

Note: The AP Supplier Upload will only process bank account details if both the Bank Account Name and/or Bank Account Number are provided. This functionality cannot be used solely to create banks or bank branches without associated supplier bank account details. The upload’s purpose is to facilitate creating and linking supplier bank account details to the supplier or updating existing linked bank account details.

4. Common Validation Errors and Solutions

For U.S. and Canadian bank accounts, validation failures may include errors like:

The modulus check validation for Routing Transit Number has failed. Please enter a correct value for the Routing Transit Number.

This error message indicates that the provided bank branch number does not meet country-specific validation requirements. To resolve this, consult the following My Oracle Support documents for the required format and validations:

  • Doc ID 1587838.1: “R12: AP: When Entering Bank Account Number Receive The Following Error: The Check Digit Validation For Routing Transit Number Has Failed.”
  • Doc ID 1287647.1: “R12/CE: Bank Branch Validation USA, CANADA : Profile Option ‘CE: Disable Bank Validations’, Bank Branch Information: Error: Check Digit Validation for Routing Transit Number Has Failed.”

5. Bypassing Bank Validations

If needed, you can bypass bank validations by setting the Oracle profile option CE: Disable Bank Validations to bypass these validations during the upload process.

Important: These validations are enforced by the standard Oracle APIs. The upload will be rejected if the information does not pass the required validation checks.
select /*+ push_pred(ipiua2) */ distinct
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
:p_upload_mode upload_mode_,
:p_disable_tca_events disable_tca_events,
to_char(null) vend_row_id,
to_char(null) site_row_id,
to_char(null) site_contact_row_id,
to_char(null) vend_bank_intsr_pay_use_row_id,
to_char(null) site_bank_intsr_pay_use_row_id,
-- ########
-- Vendor
-- ########
(select pex.full_name from per_employees_x pex where pex.employee_id = aps.employee_id) employee_name,
(select pex.employee_num from per_employees_x pex where pex.employee_id = aps.employee_id) employee_number,
(select pbgp.name from per_employees_x pex,per_business_groups_perf pbgp where pex.business_group_id = pbgp.business_group_id and pex.employee_id = aps.employee_id) business_group,
aps.vendor_name supplier_name,
hp.party_number,
hp.organization_name_phonetic alt_supplier_name,
hp.known_as alias,
aps.segment1 supplier_number,
aps.end_date_active inactive_date,
-- ## Vendor Organization
xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) vendor_type,
xxen_util.meaning(aps.organization_type_lookup_code,'ORGANIZATION TYPE',201) organization_type,
decode(aps.organization_type_lookup_code,'INDIVIDUAL',aps.individual_1099,aps.num_1099) taxpayer_id,
zptp0.rep_registration_number tax_registration_number,
zptp0.registration_type_code tax_registration_type,
(select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp0.country_code) tax_country_name,
hp.duns_number_c duns_number,
aps.standard_industry_class sic,
aps.ni_number,
nvl2(aps1.segment1,aps1.vendor_name || ' (' || aps1.segment1 || ')',null) parent_supplier,
aps.customer_num,
xxen_util.meaning(aps.one_time_flag,'YES_NO',0) one_time_flag,
xxen_util.meaning(aps.small_business_flag,'YES_NO',0) small_business_flag,
hp.url,
-- # Party dff
xxen_util.display_flexfield_context(222,'HZ_PARTIES',hp.attribute_category) party_attribute_category,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE1',hp.rowid,hp.attribute1) hz_party_attribute1,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE2',hp.rowid,hp.attribute2) hz_party_attribute2,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE3',hp.rowid,hp.attribute3) hz_party_attribute3,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE4',hp.rowid,hp.attribute4) hz_party_attribute4,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE5',hp.rowid,hp.attribute5) hz_party_attribute5,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE6',hp.rowid,hp.attribute6) hz_party_attribute6,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE7',hp.rowid,hp.attribute7) hz_party_attribute7,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE8',hp.rowid,hp.attribute8) hz_party_attribute8,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE9',hp.rowid,hp.attribute9) hz_party_attribute9,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE10',hp.rowid,hp.attribute10) hz_party_attribute10,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE11',hp.rowid,hp.attribute11) hz_party_attribute11,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE12',hp.rowid,hp.attribute12) hz_party_attribute12,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE13',hp.rowid,hp.attribute13) hz_party_attribute13,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE14',hp.rowid,hp.attribute14) hz_party_attribute14,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE15',hp.rowid,hp.attribute15) hz_party_attribute15,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE16',hp.rowid,hp.attribute16) hz_party_attribute16,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE17',hp.rowid,hp.attribute17) hz_party_attribute17,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE18',hp.rowid,hp.attribute18) hz_party_attribute18,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE19',hp.rowid,hp.attribute19) hz_party_attribute19,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE20',hp.rowid,hp.attribute20) hz_party_attribute20,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE21',hp.rowid,hp.attribute21) hz_party_attribute21,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE22',hp.rowid,hp.attribute22) hz_party_attribute22,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE23',hp.rowid,hp.attribute23) hz_party_attribute23,
xxen_util.display_flexfield_value(222,'HZ_PARTIES',hp.attribute_category,'ATTRIBUTE24',hp.rowid,hp.attribute24) hz_party_attribute24,
-- # Vendor DFF
xxen_util.display_flexfield_context(201,'PO_VENDORS',aps.attribute_category) vendor_attribute_category,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE1',aps.rowid,aps.attribute1) vendor_attribute1,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE2',aps.rowid,aps.attribute2) vendor_attribute2,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE3',aps.rowid,aps.attribute3) vendor_attribute3,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE4',aps.rowid,aps.attribute4) vendor_attribute4,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE5',aps.rowid,aps.attribute5) vendor_attribute5,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE6',aps.rowid,aps.attribute6) vendor_attribute6,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE7',aps.rowid,aps.attribute7) vendor_attribute7,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE8',aps.rowid,aps.attribute8) vendor_attribute8,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE9',aps.rowid,aps.attribute9) vendor_attribute9,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE10',aps.rowid,aps.attribute10) vendor_attribute10,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE11',aps.rowid,aps.attribute11) vendor_attribute11,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE12',aps.rowid,aps.attribute12) vendor_attribute12,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE13',aps.rowid,aps.attribute13) vendor_attribute13,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE14',aps.rowid,aps.attribute14) vendor_attribute14,
xxen_util.display_flexfield_value(201,'PO_VENDORS',aps.attribute_category,'ATTRIBUTE15',aps.rowid,aps.attribute15) vendor_attribute15,
-- ## Vendor Purchasing Options
aps.min_order_amount min_order_amount,
xxen_util.meaning(aps.hold_flag,'YES_NO',0) purchasing_hold_flag,
aps.purchasing_hold_reason,
xxen_util.meaning(aps.create_debit_memo_flag,'YES_NO',0) create_dm_for_rts,
--(select he.full_name from hr_employees he where he.employee_id = aps.hold_by) held_by,
--aps.hold_date,
-- ## Vendor Receiving Options
xxen_util.meaning(aps.enforce_ship_to_location_code,'RECEIVING CONTROL LEVEL',201) enforce_ship_to_location,
(select rrh.routing_name from rcv_routing_headers rrh where rrh.routing_header_id = aps.receiving_routing_id) receipt_routing,
case aps.inspection_required_flag || aps.receipt_required_flag
when 'NN' then '2-Way'
when 'NY' then '3-Way'
when 'YY' then '4-Way'
else null
end match_approval_level,
xxen_util.meaning(aps.inspection_required_flag,'YES_NO',0) inspection_required,
xxen_util.meaning(aps.receipt_required_flag,'YES_NO',0) receipt_required,
aps.qty_rcv_tolerance qty_received_tolerance,
xxen_util.meaning(aps.qty_rcv_exception_code,'RECEIVING CONTROL LEVEL',201) qty_received_exception,
aps.days_early_receipt_allowed,
aps.days_late_receipt_allowed,
xxen_util.meaning(aps.allow_substitute_receipts_flag,'YES_NO',0) allow_substitute_receipts,
xxen_util.meaning(aps.allow_unordered_receipts_flag,'YES_NO',0) allow_unordered_receipts,
xxen_util.meaning(aps.receipt_days_exception_code,'RECEIVING CONTROL LEVEL',201) receipt_days_exception,
-- ## Vendor Invoicing Options
aps.invoice_currency_code,
aps.payment_currency_code,
aps.invoice_amount_limit,
xxen_util.meaning(aps.match_option,'POS_INVOICE_MATCH_OPTION',0) invoice_match_option,
aps.payment_priority,
(select atv.name from ap_terms_vl atv where atv.term_id = aps.terms_id) payment_terms,
xxen_util.meaning(aps.terms_date_basis,'TERMS DATE BASIS',200) terms_date_basis,
xxen_util.meaning(aps.pay_date_basis_lookup_code,'PAY DATE BASIS',200) pay_date_basis,
xxen_util.meaning(aps.pay_group_lookup_code,'PAY GROUP',201) pay_group,
xxen_util.meaning(aps.always_take_disc_flag,'YES_NO',0) always_take_discount,
xxen_util.meaning(aps.exclude_freight_from_discount,'YES_NO',0) exclude_freight_from_discount,
xxen_util.meaning(aps.auto_calculate_interest_flag,'YES_NO',0) create_interest_invoices,
-- ## Vendor Invoicing - Payment Holds
xxen_util.meaning(aps.hold_all_payments_flag,'YES_NO',0) hold_all_invoices,
xxen_util.meaning(aps.hold_unmatched_invoices_flag,'YES_NO',0) hold_unmatched_invoices,
xxen_util.meaning(aps.hold_future_payments_flag,'YES_NO',0) hold_unvalidated_invoices,
aps.hold_reason payments_hold_reason,
-- ## Vendor Tax details - Income Tax
xxen_util.meaning(aps.federal_reportable_flag,'YES_NO',0) federal_reportable,
--select income_tax_type, description from ap_income_tax_types where (inactive_date is null or inactive_date > sysdate)
aps.type_1099 income_tax_type,
xxen_util.meaning(aps.state_reportable_flag,'YES_NO',0) state_reportable,
xxen_util.meaning(aps.allow_awt_flag,'YES_NO',0) allow_withholding_tax,
(select aag.name from ap_awt_groups aag where aag.group_id = aps.awt_group_id) inv_withholding_tax_group,
(select aag.name from ap_awt_groups aag where aag.group_id = aps.pay_awt_group_id) pay_withholding_tax_group,
-- ## Vendor Tax Details - Transaction Tax
xxen_util.meaning(zptp0.rounding_level_code,'ZX_ROUNDING_LEVEL',0) tax_rounding_level,
nvl(xxen_util.meaning(zptp0.rounding_rule_code,'ZX_ROUNDING_RULE',0),xxen_util.meaning(:p_default_tax_rounding_rule,'AP_TAX_ROUNDING_RULE',200)) tax_rounding_rule,
xxen_util.meaning(nvl(zptp0.inclusive_tax_flag,:p_default_inclusive_tax),'YES_NO',0) inclusive_tax,
xxen_util.meaning(nvl(zptp0.process_for_applicability_flag,'Y'),'YES_NO',0) allow_tax_applicability,
xxen_util.meaning(nvl(zptp0.self_assess_flag,'N'),'YES_NO',0) self_assessment,
xxen_util.meaning(nvl(zptp0.allow_offset_tax_flag,:p_default_allow_offset_taxes),'YES_NO',0) allow_offset_taxes,
zptp0.tax_classification_code tax_classification,
aps.offset_vat_code,
-- ## Vendor Tax Details - Tax Reporting
aps.tax_reporting_name,
aps.name_control tax_reporting_name_control,
aps.tax_verification_date tax_reporting_verif_date,
-- # Vendor Payment Details
(select
 ipmv.payment_method_name
 from
 iby_ext_party_pmt_mthds ieppm,
 iby_payment_methods_vl ipmv
 where
 ieppm.payment_method_code = ipmv.payment_method_code and
 ieppm.primary_flag = 'Y' and
 ieppm.payment_function='PAYABLES_DISB' and
 ieppm.payment_flow = 'DISBURSEMENTS' and
 nvl(ieppm.inactive_date,trunc(sysdate+1)) > trunc(sysdate) and
 nvl(ipmv.inactive_date,trunc(sysdate+1)) > trunc(sysdate) and
 ieppm.ext_pmt_party_id = iepa0.ext_payee_id and
 rownum <= 1
) default_payment_method,
xxen_util.meaning(nvl(iepa0.exclusive_payment_flag,:p_default_pay_document_alone),'YES_NO',0) pay_each_document_alone,
xxen_util.meaning(iepa0.bank_charge_bearer,'IBY_BANK_CHARGE_BEARER',0) bank_charge_bearer,
(select iprv.meaning from iby_payment_reasons_vl iprv where iprv.payment_reason_code = iepa0.payment_reason_code) payment_reason,
iepa0.payment_reason_comments payment_reason_comments,
(select ifv.format_name from iby_formats_vl ifv where ifv.format_code = iepa0.payment_format_code) payee_payment_format,
--xxen_util.meaning(iepa0.service_level_code,'IBY_SERVICE_LEVEL',0) payment_service_level,
(select idcv.meaning from iby_delivery_channels_vl idcv where idcv.delivery_channel_code = iepa0.delivery_channel_code) payment_delivery_channel,
(select ibiv.meaning from iby_bank_instructions_vl ibiv where ibiv.bank_instruction_code = iepa0.bank_instruction1_code) bank_instruction1,
(select ibiv.meaning from iby_bank_instructions_vl ibiv where ibiv.bank_instruction_code = iepa0.bank_instruction2_code) bank_instruction2,
iepa0.bank_instruction_details bank_instruction_details,
iepa0.payment_text_message1 payment_text_message1,
iepa0.payment_text_message2 payment_text_message2,
iepa0.payment_text_message3 payment_text_message3,
xxen_util.meaning(iepa0.remit_advice_delivery_method,'IBY_DELIVERY_METHODS',0) remit_advice_del_method,
iepa0.remit_advice_email remit_advice_email,
iepa0.remit_advice_fax remit_advice_fax,
iepa0.ece_tp_location_code ece_tp_location_code,
iepa0.settlement_priority settlement_priority,
-- ########
-- Vendor Bank Accounts
-- ########
cbbv0.bank_name vendor_bank_name,
cbbv0.bank_number vendor_bank_number,
(select ftv.territory_short_name
 from   fnd_territories_vl ftv
 where  ftv.territory_code = cbbv0.bank_home_country
) vendor_bank_country,
cbbv0.bank_branch_name vendor_bank_branch_name,
cbbv0.branch_number vendor_bank_branch_number,
cbbv0.bank_branch_type vendor_bank_branch_type,
cbbv0.eft_swift_code vendor_bank_branch_bic,
ieba0.bank_account_name vendor_bank_acct_name,
ieba0.bank_account_num vendor_bank_acct_num,
ieba0.check_digits vendor_bank_acct_check_digits,
ieba0.currency_code vendor_bank_acct_currency,
xxen_util.meaning(ieba0.foreign_payment_use_flag,'YES_NO',0) vendor_bank_acct_allow_foreign,
ieba0.iban vendor_bank_acct_iban,
ieba0.bank_account_name_alt vendor_bank_acct_name_alt,
ieba0.account_suffix vendor_bank_acct_suffix,
xxen_util.meaning(ieba0.bank_account_type,'BANK_ACCOUNT_TYPE',260) vendor_bank_acct_type,
ieba0.secondary_account_reference vendor_bank_acct_sec_reference,
ieba0.description vendor_bank_acct_description,
ieba0.contact_name vendor_bank_acct_contact,
ieba0.contact_phone vendor_bank_acct_contact_phone,
ieba0.contact_fax vendor_bank_acct_contact_fax,
ieba0.contact_email vendor_bank_acct_contact_email,
ipiua0.start_date vendor_bank_acct_assign_start,
ipiua0.end_date vendor_bank_acct_assign_end,
-- ########
-- Party Site/Address
-- ########
hps.party_site_number,
hps.party_site_name address_name,
(select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = hl.country) country,
hl.address1 address_line1,
hl.address2 address_line2,
hl.address3 address_line3,
hl.address4 address_line4,
hl.city,
hl.county,
hl.state,
hl.postal_code zip,
hl.province,
xxen_util.meaning(hl.address_style,'ADDRESS_STYLE',0) address_style,
initcap(fl.nls_language) address_language,
xxen_util.meaning(hps.status,'REGISTRY_STATUS',222) address_status,
--
hcp1.phone_area_code address_phone_area_code,
hcp1.phone_number address_phone_number,
hcp2.phone_area_code address_fax_area_code,
hcp2.phone_number address_fax_number,
hcp3.email_address address_email_address,
(select xxen_util.meaning('Y','YES_NO',0) from hz_party_site_uses hpsu where hpsu.party_site_id = assa.party_site_id and hpsu.site_use_type = 'PURCHASING' and hpsu.status = 'A') purchasing_address,
(select xxen_util.meaning('Y','YES_NO',0) from hz_party_site_uses hpsu where hpsu.party_site_id = assa.party_site_id and hpsu.site_use_type = 'PAY' and hpsu.status = 'A') pay_address,
(select xxen_util.meaning('Y','YES_NO',0) from hz_party_site_uses hpsu where hpsu.party_site_id = assa.party_site_id and hpsu.site_use_type = 'RFQ' and hpsu.status = 'A') rfq_only_address,
-- ########
-- Vendor Site
-- ########
assa.vendor_site_code site_name,
assa.vendor_site_code_alt alt_site_name,
hou.name operating_unit,
assa.inactive_date site_inactive_date,
xxen_util.meaning(assa.supplier_notif_method,'DOCUMENT_COMMUNICATION_METHOD',201) site_notification_method,
assa.area_code site_phone_area_code,
assa.phone site_phone_number,
assa.fax_area_code site_fax_area_code,
assa.fax site_fax_number,
assa.email_address site_email_address,
decode(assa.purchasing_site_flag,'Y',xxen_util.meaning('Y','YES_NO',0),null) purchasing_site,
decode(assa.pay_site_flag,'Y',xxen_util.meaning('Y','YES_NO',0),null) pay_site,
decode(assa.primary_pay_site_flag,'Y',xxen_util.meaning('Y','YES_NO',0),null) primary_pay_site,
decode(assa.rfq_only_site_flag,'Y',xxen_util.meaning('Y','YES_NO',0),null) rfq_only_site,
decode(assa.tax_reporting_site_flag,'Y',xxen_util.meaning('Y','YES_NO',0),null) tax_reporting_site,
nvl(assa.vat_registration_num,zptp1.rep_registration_number) site_tax_reg_number,
zptp1.registration_type_code site_tax_reg_type,
(select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp1.country_code) site_tax_reg_country,
assa.customer_num site_customer_num,
-- # Party Site dff
xxen_util.display_flexfield_context(222,'HZ_PARTY_SITES',hps.attribute_category) party_site_attribute_category,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE1',hps.rowid,hps.attribute1) hz_party_site_attribute1,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE2',hps.rowid,hps.attribute2) hz_party_site_attribute2,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE3',hps.rowid,hps.attribute3) hz_party_site_attribute3,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE4',hps.rowid,hps.attribute4) hz_party_site_attribute4,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE5',hps.rowid,hps.attribute5) hz_party_site_attribute5,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE6',hps.rowid,hps.attribute6) hz_party_site_attribute6,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE7',hps.rowid,hps.attribute7) hz_party_site_attribute7,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE8',hps.rowid,hps.attribute8) hz_party_site_attribute8,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE9',hps.rowid,hps.attribute9) hz_party_site_attribute9,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE10',hps.rowid,hps.attribute10) hz_party_site_attribute10,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE11',hps.rowid,hps.attribute11) hz_party_site_attribute11,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE12',hps.rowid,hps.attribute12) hz_party_site_attribute12,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE13',hps.rowid,hps.attribute13) hz_party_site_attribute13,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE14',hps.rowid,hps.attribute14) hz_party_site_attribute14,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE15',hps.rowid,hps.attribute15) hz_party_site_attribute15,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE16',hps.rowid,hps.attribute16) hz_party_site_attribute16,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE17',hps.rowid,hps.attribute17) hz_party_site_attribute17,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE18',hps.rowid,hps.attribute18) hz_party_site_attribute18,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE19',hps.rowid,hps.attribute19) hz_party_site_attribute19,
xxen_util.display_flexfield_value(222,'HZ_PARTY_SITES',hps.attribute_category,'ATTRIBUTE20',hps.rowid,hps.attribute20) hz_party_site_attribute20,
-- # Vendor Site DFF
xxen_util.display_flexfield_context(201,'PO_VENDOR_SITES',assa.attribute_category) site_attribute_category,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE1',assa.rowid,assa.attribute1) site_attribute1,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE2',assa.rowid,assa.attribute2) site_attribute2,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE3',assa.rowid,assa.attribute3) site_attribute3,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE4',assa.rowid,assa.attribute4) site_attribute4,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE5',assa.rowid,assa.attribute5) site_attribute5,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE6',assa.rowid,assa.attribute6) site_attribute6,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE7',assa.rowid,assa.attribute7) site_attribute7,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE8',assa.rowid,assa.attribute8) site_attribute8,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE9',assa.rowid,assa.attribute9) site_attribute9,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE10',assa.rowid,assa.attribute10) site_attribute10,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE11',assa.rowid,assa.attribute11) site_attribute11,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE12',assa.rowid,assa.attribute12) site_attribute12,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE13',assa.rowid,assa.attribute13) site_attribute13,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE14',assa.rowid,assa.attribute14) site_attribute14,
xxen_util.display_flexfield_value(201,'PO_VENDOR_SITES',assa.attribute_category,'ATTRIBUTE15',assa.rowid,assa.attribute15) site_attribute15,
-- ## Vendor Site Purchasing Options
(select hl.location_code from hr_locations hl where hl.location_id = assa.ship_to_location_id) site_ship_to_location,
(select hl.location_code from hr_locations hl where hl.location_id = assa.bill_to_location_id) site_bill_to_location,
xxen_util.meaning(assa.ship_via_lookup_code,'SHIP_METHOD',3) site_ship_via,
xxen_util.meaning(assa.pay_on_code,'ERS PAY_ON_CODE',201) site_pay_on,
(select assa2.vendor_site_code from ap_supplier_sites_all assa2 where assa2.vendor_site_id = assa.default_pay_site_id) site_alt_pay_site,
xxen_util.meaning(assa.pay_on_receipt_summary_code,'ERS INVOICE_SUMMARY',201)  site_invoice_summary_level,
xxen_util.meaning(assa.create_debit_memo_flag,'YES_NO',0) site_create_dm_for_rts,
xxen_util.meaning(assa.gapless_inv_num_flag,'YES_NO',0) site_gapless_inv_numbering,
assa.selling_company_identifier site_selling_company_identif,
xxen_util.meaning(assa.fob_lookup_code,'FOB',201) site_fob,
xxen_util.meaning(assa.freight_terms_lookup_code,'FREIGHT TERMS',201) site_freight_terms,
xxen_util.meaning(assa.shipping_control,'SHIPPING CONTROL',201) site_transportation_arranged,
(select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = assa.country_of_origin_code) site_country_of_origin,
--assa.ack_lead_time site_ack_days_lead_time,
-- # Vendor Site Invoicing Options
assa.invoice_currency_code site_invoice_currency_code,
assa.payment_currency_code site_payment_currency_code,
assa.invoice_amount_limit site_invoice_amount_limit,
xxen_util.meaning(assa.match_option,'POS_INVOICE_MATCH_OPTION',0) site_invoice_match_option,
assa.payment_priority site_payment_priority,
(select atv.name from ap_terms_vl atv where atv.term_id = assa.terms_id) site_payment_terms,
xxen_util.meaning(assa.terms_date_basis,'TERMS DATE BASIS',200) site_terms_date_basis,
xxen_util.meaning(assa.pay_date_basis_lookup_code,'PAY DATE BASIS',200) site_pay_date_basis,
xxen_util.meaning(assa.pay_group_lookup_code,'PAY GROUP',201) site_pay_group,
xxen_util.meaning(assa.always_take_disc_flag,'YES_NO',0) site_always_take_discount,
xxen_util.meaning(assa.exclude_freight_from_discount,'YES_NO',0) site_excl_freight_from_disc,
(select att.tolerance_name from ap_tolerance_templates att where att.tolerance_id = assa.tolerance_id and tolerance_type = 'GOODS') site_invoice_tolerance,
(select att.tolerance_name from ap_tolerance_templates att where att.tolerance_id = assa.services_tolerance_id and tolerance_type = 'SERVICES') site_service_tolerance,
assa.retainage_rate site_retainage_rate_pct,
-- ## Vendor Site Invoicing - Payment Holds
xxen_util.meaning(assa.hold_all_payments_flag,'YES_NO',0) site_hold_all_invoices,
xxen_util.meaning(assa.hold_unmatched_invoices_flag,'YES_NO',0) site_hold_unmatched_invoices,
xxen_util.meaning(assa.hold_future_payments_flag,'YES_NO',0) site_hold_unvalidated_invoices,
assa.hold_reason site_payments_hold_reason,
-- # Vendor Site Accounting
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where assa.accts_pay_code_combination_id=gcck.code_combination_id) liability_account,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where assa.prepay_code_combination_id=gcck.code_combination_id) prepayment_account,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where assa.future_dated_payment_ccid=gcck.code_combination_id) bills_payable_account,
(select adsa.distribution_set_name from ap_distribution_sets_all adsa where adsa.distribution_set_id = assa.distribution_set_id) distribution_set,
-- # Vendor Site Tax Details
xxen_util.meaning(assa.allow_awt_flag,'YES_NO',0) site_allow_withholding_tax,
(select aag.name from ap_awt_groups aag where aag.group_id = assa.awt_group_id) site_inv_withholding_tax_group,
(select aag.name from ap_awt_groups aag where aag.group_id = assa.pay_awt_group_id) site_pay_withholding_tax_group,
xxen_util.meaning(nvl(zptp1.rounding_level_code,zptp0.rounding_level_code),'ZX_ROUNDING_LEVEL',0) site_tax_rounding_level,
xxen_util.meaning(coalesce(decode(assa.ap_tax_rounding_rule,'N','NEAREST','U','UP','D','DOWN'),zptp1.rounding_rule_code,zptp0.rounding_rule_code,decode(:p_default_tax_rounding_rule,'N','NEAREST','U','UP','D','DOWN')),'ZX_ROUNDING_RULE',0) site_tax_rounding_rule,
xxen_util.meaning(coalesce(assa.amount_includes_tax_flag,zptp1.inclusive_tax_flag,zptp0.inclusive_tax_flag,:p_default_inclusive_tax),'YES_NO',0) site_inclusive_tax,
xxen_util.meaning(coalesce(assa.auto_tax_calc_flag,zptp1.process_for_applicability_flag,zptp0.process_for_applicability_flag,'Y'),'YES_NO',0) site_calculate_tax,
xxen_util.meaning(coalesce(assa.offset_tax_flag,zptp1.allow_offset_tax_flag,zptp0.allow_offset_tax_flag,:p_default_allow_offset_taxes),'YES_NO',0) site_allow_offset_taxes,
nvl(assa.vat_code,zptp1.tax_classification_code) site_tax_classification,
-- # Vendor Site Payment Details
(select
 ipmv.payment_method_name
 from
 iby_ext_party_pmt_mthds ieppm,
 iby_payment_methods_vl ipmv
 where
 ieppm.payment_method_code = ipmv.payment_method_code and
 ieppm.primary_flag = 'Y' and
 ieppm.payment_function='PAYABLES_DISB' and
 ieppm.payment_flow = 'DISBURSEMENTS' and
 nvl(ieppm.inactive_date,trunc(sysdate+1)) > trunc(sysdate) and
 nvl(ipmv.inactive_date,trunc(sysdate+1)) > trunc(sysdate) and
 ieppm.ext_pmt_party_id = iepa1.ext_payee_id and
 rownum <= 1
) site_default_payment_method,
xxen_util.meaning(nvl(iepa1.exclusive_payment_flag,nvl(iepa0.exclusive_payment_flag,:p_default_pay_document_alone)),'YES_NO',0) site_pay_each_document_alone,
xxen_util.meaning(assa.bank_charge_bearer,'BANK CHARGE BEARER',200) site_deduct_charge_from_bank,
xxen_util.meaning(iepa1.bank_charge_bearer,'IBY_BANK_CHARGE_BEARER',0) site_bank_charge_bearer,
(select iprv.meaning from iby_payment_reasons_vl iprv where iprv.payment_reason_code = iepa1.payment_reason_code) site_payment_reason,
iepa1.payment_reason_comments site_payment_reason_comments,
(select ifv.format_name from iby_formats_vl ifv where ifv.format_code = iepa1.payment_format_code) site_payee_payment_format,
--xxen_util.meaning(iepa1.service_level_code,'IBY_SERVICE_LEVEL',0) site_payment_service_level,
(select idcv.meaning from iby_delivery_channels_vl idcv where idcv.delivery_channel_code = iepa1.delivery_channel_code) site_payment_delivery_channel,
(select ibiv.meaning from iby_bank_instructions_vl ibiv where ibiv.bank_instruction_code = iepa1.bank_instruction1_code) site_bank_instruction1,
(select ibiv.meaning from iby_bank_instructions_vl ibiv where ibiv.bank_instruction_code = iepa1.bank_instruction2_code) site_bank_instruction2,
iepa1.bank_instruction_details site_bank_instruction_details,
iepa1.payment_text_message1 site_payment_text_message1,
iepa1.payment_text_message2 site_payment_text_message2,
iepa1.payment_text_message3 site_payment_text_message3,
xxen_util.meaning(iepa1.remit_advice_delivery_method,'IBY_DELIVERY_METHODS',0) site_remit_advice_del_method,
iepa1.remit_advice_email site_remit_advice_email,
iepa1.remit_advice_fax site_remit_advice_fax,
iepa1.ece_tp_location_code site_ece_tp_location_code,
iepa1.settlement_priority site_settlement_priority,
-- ########
-- Vendor Site Bank Accounts
-- ########
ipiua2.bank_assignment_level site_bank_assignment_level,
ipiua2.bank_name site_bank_name,
ipiua2.bank_number site_bank_number,
ipiua2.territory_short_name site_bank_country,
ipiua2.bank_branch_name site_bank_branch_name,
ipiua2.branch_number site_bank_branch_number,
ipiua2.bank_branch_type site_bank_branch_type,
ipiua2.eft_swift_code site_bank_branch_bic,
ipiua2.bank_account_name site_bank_acct_name,
ipiua2.bank_account_num site_bank_acct_num,
ipiua2.check_digits site_bank_acct_check_digits,
ipiua2.currency_code site_bank_acct_currency,
xxen_util.meaning(ipiua2.foreign_payment_use_flag,'yes_no',0) site_bank_acct_allow_foreign,
ipiua2.iban site_bank_acct_iban,
ipiua2.bank_account_name_alt site_bank_acct_name_alt,
ipiua2.account_suffix site_bank_acct_suffix,
xxen_util.meaning(ipiua2.bank_account_type,'bank_account_type',260) site_bank_acct_type,
ipiua2.secondary_account_reference site_bank_acct_sec_reference,
ipiua2.description site_bank_acct_description,
ipiua2.contact_name site_bank_acct_contact,
ipiua2.contact_phone site_bank_acct_contact_phone,
ipiua2.contact_fax site_bank_acct_contact_fax,
ipiua2.contact_email site_bank_acct_contact_email,
ipiua2.start_date site_bank_acct_assign_start,
ipiua2.end_date site_bank_acct_assign_end,
-- ########
-- Vendor Site Contacts
-- ########
xxen_util.meaning(nvl(hpc.person_pre_name_adjunct,hpc.salutation),'CONTACT_TITLE',222) contact_title,
hpc.person_first_name contact_first_name,
hpc.person_middle_name contact_middle_name,
hpc.person_last_name contact_last_name,
hpc.known_as contact_alternate_name,
hpc.person_title contact_job_title,
(select hoc.department
 from hz_org_contacts hoc
 where hoc.org_contact_id = asco.org_contact_id
) contact_department,
nvl(
 (select hcp.email_address
  from   hz_contact_points hcp
  where  hcp.contact_point_id =
  (select
   distinct first_value(hcp2.contact_point_id) over (order by hcp2.last_update_date desc, hcp2.contact_point_id desc rows between unbounded preceding and unbounded following)
   from
   hz_contact_points hcp2
   where
   hcp2.owner_table_name = 'HZ_PARTIES' and
   hcp2.owner_table_id = asco.rel_party_id and
   hcp2.contact_point_type = 'EMAIL' and
   hcp2.status = 'A'
  )
 ),
 hpr.email_address
) contact_email_address,
hpr.url contact_url,
case when hpr.primary_phone_line_type='GEN' then hpr.primary_phone_area_code else null end contact_phone_area_code,
case when hpr.primary_phone_line_type='GEN' then hpr.primary_phone_number else null end contact_phone_number,
case when hpr.primary_phone_line_type='GEN' then hpr.primary_phone_extension else null end contact_phone_ext,
(select hcp.phone_area_code
 from   hz_contact_points hcp
 where  hcp.contact_point_id =
 (select
  distinct first_value(hcp2.contact_point_id) over (order by hcp2.last_update_date desc, hcp2.contact_point_id desc rows between unbounded preceding and unbounded following)
  from
  hz_contact_points hcp2
  where
  hcp2.owner_table_name = 'HZ_PARTIES' and
  hcp2.owner_table_id = asco.rel_party_id and
  hcp2.contact_point_type = 'PHONE' and
  hcp2.phone_line_type = 'GEN' and
  hcp2.primary_flag = 'N' and
  hcp2.status = 'A'
 )
) contact_alt_phone_area_code,
(select hcp.phone_number
 from   hz_contact_points hcp
 where  hcp.contact_point_id =
 (select
  distinct first_value(hcp2.contact_point_id) over (order by hcp2.last_update_date desc, hcp2.contact_point_id desc rows between unbounded preceding and unbounded following)
  from
  hz_contact_points hcp2
  where
  hcp2.owner_table_name = 'HZ_PARTIES' and
  hcp2.owner_table_id = asco.rel_party_id and
  hcp2.contact_point_type = 'PHONE' and
  hcp2.phone_line_type = 'GEN' and
  hcp2.primary_flag = 'N' and
  hcp2.status = 'A'
 )
) contact_alt_phone_number,
(select hcp.phone_area_code
 from   hz_contact_points hcp
 where  hcp.contact_point_id =
 (select
  distinct first_value(hcp2.contact_point_id) over (order by hcp2.last_update_date desc, hcp2.contact_point_id desc rows between unbounded preceding and unbounded following)
  from
  hz_contact_points hcp2
  where
  hcp2.owner_table_name = 'HZ_PARTIES' and
  hcp2.owner_table_id = asco.rel_party_id and
  hcp2.contact_point_type = 'PHONE' and
  hcp2.phone_line_type = 'FAX' and
  hcp2.status = 'A'
 )
) contact_fax_area_code,
(select hcp.phone_number
 from   hz_contact_points hcp
 where  hcp.contact_point_id =
 (select
  distinct first_value(hcp2.contact_point_id) over (order by hcp2.last_update_date desc, hcp2.contact_point_id desc rows between unbounded preceding and unbounded following)
  from
  hz_contact_points hcp2
  where
  hcp2.owner_table_name = 'HZ_PARTIES' and
  hcp2.owner_table_id = asco.rel_party_id and
  hcp2.contact_point_type = 'PHONE' and
  hcp2.phone_line_type = 'FAX' and
  hcp2.status = 'A'
 )
) contact_fax_number,
(select hr.end_date from hz_relationships hr where hr.relationship_id = asco.relationship_id and hr.directional_flag = 'F' and trunc(hr.end_date) != to_date('31/12/4712','DD/MM/YYYY')) contact_inactive_date,
-- # Contacts DFF
xxen_util.display_flexfield_context(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category) contact_attribute_category,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE1',asco.rowid,asco.attribute1) contact_attribute1,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE2',asco.rowid,asco.attribute2) contact_attribute2,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE3',asco.rowid,asco.attribute3) contact_attribute3,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE4',asco.rowid,asco.attribute4) contact_attribute4,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE5',asco.rowid,asco.attribute5) contact_attribute5,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE6',asco.rowid,asco.attribute6) contact_attribute6,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE7',asco.rowid,asco.attribute7) contact_attribute7,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE8',asco.rowid,asco.attribute8) contact_attribute8,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE9',asco.rowid,asco.attribute9) contact_attribute9,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE10',asco.rowid,asco.attribute10) contact_attribute10,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE11',asco.rowid,asco.attribute11) contact_attribute11,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE12',asco.rowid,asco.attribute12) contact_attribute12,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE13',asco.rowid,asco.attribute13) contact_attribute13,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE14',asco.rowid,asco.attribute14) contact_attribute14,
xxen_util.display_flexfield_value(200,'AP_SUPPLIER_CONTACTS',asco.attribute_category,'ATTRIBUTE15',asco.rowid,asco.attribute15) contact_attribute15,
-- ########
-- IDs
-- ########
aps.vendor_id,
aps.employee_id,
hp.party_id,
assa.vendor_site_id,
assa.party_site_id,
assa.org_id,
asco.vendor_contact_id,
ipiua0.instrument_payment_use_id supplier_bank_intsr_pay_use_id,
ipiua2.instrument_payment_use_id site_bank_instr_pay_use_id,
to_number(null) upload_row,
-- defaults
:p_default_tax_rounding_rule default_tax_rounding_rule,
:p_default_inclusive_tax default_inclusive_tax_flag,
:p_default_allow_offset_taxes default_allow_offset_tax_flag,
:p_default_pay_document_alone default_pay_alone_flag,
:p_site_bank_assign_level default_site_bank_assign_lvl
from
ap_suppliers aps,
ap_supplier_sites_all assa,
hr_all_organization_units_vl hou,
ap_system_parameters_all aspa,
gl_ledgers gl,
hz_parties hp,
hz_party_sites hps,
hz_locations hl,
fnd_languages fl,
--
ap_suppliers aps1,
zx_party_tax_profile zptp0,
iby_external_payees_all iepa0,  -- to get the supplier level payment distribution
zx_party_tax_profile zptp1,
iby_external_payees_all iepa1,  -- to get the site level payment distribution
--
hz_contact_points hcp1,
hz_contact_points hcp2,
hz_contact_points hcp3,
-- vendor bank tables
iby_pmt_instr_uses_all ipiua0,
iby_ext_bank_accounts ieba0,
ce_bank_branches_v cbbv0,
-- vendor site bank tables
(select
 iepa2.payee_party_id,
 iepa2.party_site_id,
 iepa2.org_id,
 iepa2.supplier_site_id,
 case when ipiua2.instrument_payment_use_id is not null and iepa2.supplier_site_id is not null
 then xxen_util.meaning('SITE','POS_SBD_BYR_USAGE_LIST',0)
 when ipiua2.instrument_payment_use_id is not null and iepa2.org_id is not null
 then xxen_util.meaning('ADDRESS_OU','POS_SBD_BYR_USAGE_LIST',0)
 when ipiua2.instrument_payment_use_id is not null and iepa2.party_site_id is not null
 then xxen_util.meaning('ADDRESS','POS_SBD_BYR_USAGE_LIST',0)
 else null
 end bank_assignment_level,
 cbbv2.bank_name,
 cbbv2.bank_number,
 ftv.territory_short_name,
 cbbv2.bank_branch_name,
 cbbv2.branch_number,
 cbbv2.bank_branch_type,
 cbbv2.eft_swift_code,
 ieba2.bank_account_name,
 ieba2.bank_account_num,
 ieba2.check_digits,
 ieba2.currency_code,
 ieba2.foreign_payment_use_flag,
 ieba2.iban,
 ieba2.bank_account_name_alt,
 ieba2.account_suffix,
 ieba2.bank_account_type,
 ieba2.secondary_account_reference,
 ieba2.description,
 ieba2.contact_name,
 ieba2.contact_phone,
 ieba2.contact_fax,
 ieba2.contact_email,
 ipiua2.start_date,
 ipiua2.end_date,
 ipiua2.instrument_payment_use_id
 from
 iby_external_payees_all iepa2,
 iby_pmt_instr_uses_all ipiua2,
 iby_ext_bank_accounts ieba2,
 ce_bank_branches_v cbbv2,
 fnd_territories_vl ftv
 where
 :p_show_site_banks = 'Y' and
 coalesce(iepa2.party_site_id,iepa2.org_id,iepa2.supplier_site_id) is not null and
 iepa2.payment_function = 'PAYABLES_DISB' and
 iepa2.ext_payee_id = ipiua2.ext_pmt_party_id and
 ipiua2.payment_flow = 'DISBURSEMENTS' and
 ipiua2.payment_function = 'PAYABLES_DISB' and
 ipiua2.instrument_type = 'BANKACCOUNT' and
 ipiua2.instrument_id = ieba2.ext_bank_account_id and
 ieba2.branch_id = cbbv2.branch_party_id and
 cbbv2.bank_home_country = ftv.territory_code (+)
) ipiua2,
--
ap_supplier_contacts asco,
hz_parties hpc,
hz_parties hpr
--
where
:p_upload_mode like '%' || xxen_upload.action_update and
1=1 and
decode(:p_show_vendor_sites,'Y',aps.vendor_id,-1) = assa.vendor_id (+) and
(assa.org_id is null or mo_global.check_access(assa.org_id)='Y') and
assa.org_id = hou.organization_id (+) and
assa.org_id = aspa.org_id (+) and
aspa.set_of_books_id = gl.ledger_id (+) and
aps.party_id = hp.party_id and
assa.party_site_id = hps.party_site_id (+) and
hps.location_id = hl.location_id (+) and
hl.language = fl.language_code (+) and
--
aps.parent_vendor_id = aps1.vendor_id(+) and
--
aps.party_id = zptp0.party_id (+) and
'THIRD_PARTY' = zptp0.party_type_code (+) and
assa.party_site_id = zptp1.party_id (+) and
'THIRD_PARTY_SITE' = zptp1.party_type_code (+) and
--
aps.party_id = iepa0.payee_party_id (+) and
nvl(iepa0.party_site_id (+),0) = 0 and
nvl(iepa0.supplier_site_id (+),0) = 0 and
nvl(iepa0.org_id (+),0) = 0 and
iepa0.payment_function (+) = 'PAYABLES_DISB' and
aps.party_id = iepa1.payee_party_id (+) and
assa.vendor_site_id = iepa1.supplier_site_id (+) and
assa.party_site_id = iepa1.party_site_id (+) and
iepa1.payment_function (+) = 'PAYABLES_DISB' and
--
hps.party_site_id = hcp1.owner_table_id (+) and
hcp1.owner_table_name (+) = 'HZ_PARTY_SITES' and
hcp1.contact_point_type (+) = 'PHONE' and
hcp1.phone_line_type (+) = 'GEN' and
hcp1.primary_flag (+) = 'Y' and
hcp1.status (+) = 'A' and
hps.party_site_id = hcp2.owner_table_id (+) and
hcp2.owner_table_name (+) = 'HZ_PARTY_SITES' and
hcp2.contact_point_type (+) = 'PHONE' and
hcp2.phone_line_type (+) = 'FAX' and
hcp2.status (+) = 'A' and
hps.party_site_id = hcp3.owner_table_id (+) and
hcp3.owner_table_name (+) = 'HZ_PARTY_SITES' and
hcp3.contact_point_type (+) = 'EMAIL' and
hcp3.primary_flag (+) = 'Y' and
hcp3.status (+) = 'A' and
--
decode(:p_show_vendor_banks,'Y',iepa0.ext_payee_id) = ipiua0.ext_pmt_party_id (+) and
ipiua0.payment_flow (+) = 'DISBURSEMENTS' and
ipiua0.payment_function (+) = 'PAYABLES_DISB' and
ipiua0.instrument_type (+) = 'BANKACCOUNT' and
ipiua0.instrument_id = ieba0.ext_bank_account_id (+) and
ieba0.branch_id = cbbv0.branch_party_id (+) and
--
aps.party_id = ipiua2.payee_party_id (+) and
nvl(assa.party_site_id,-1) = nvl(ipiua2.party_site_id (+),nvl(assa.party_site_id,-1)) and
assa.org_id = nvl(ipiua2.org_id (+),assa.org_id) and
assa.vendor_site_id = nvl(ipiua2.supplier_site_id (+),assa.vendor_site_id) and
--
decode(:p_show_site_contacts,'Y',assa.party_site_id,-99) = asco.org_party_site_id (+) and
(asco.vendor_contact_id is null or
 asco.vendor_contact_id = xxen_ap_upload.get_vendor_contact_id(assa.vendor_site_id,aps.party_id,assa.party_site_id,asco.relationship_id,asco.rel_party_id)
) and
asco.per_party_id = hpc.party_id (+) and
asco.rel_party_id = hpr.party_id (+)
Parameter NameSQL textValidation
Upload Mode
 
LOV
Supplier Type
hou.name=:p_operating_unit
LOV
Exclude Employee Suppliers
aps.employee_id is null
LOV
Supplier Name
aps.vendor_name=:p_supplier_name
LOV
Supplier Name From
aps.vendor_name>=:p_supplier_name_fr
LOV
Supplier Name To
aps.vendor_name<=:p_supplier_name_to
LOV
Supplier Number
aps.segment1=:p_supplier_num
LOV
Supplier Number From
aps.segment1>=:p_supplier_num_from
LOV
Supplier Number To
aps.segment1<=:p_supplier_num_to
LOV
Payment Method
aps.party_id in (
select
iepa.payee_party_id
from
iby_ext_party_pmt_mthds ieppm,
iby_external_payees_all iepa
where
ieppm.payment_method_code=xxen_util.lookup_code(:payment_method,'PAYMENT METHOD',200) and
ieppm.ext_pmt_party_id=iepa.ext_payee_id and
iepa.payment_function='PAYABLES_DISB' and
iepa.party_site_id is null and
iepa.supplier_site_id is null
)
LOV
Tax Registration Number
aps.vat_registration_num=:tax_registration_number
Char
Supplier Site
assa.vendor_site_code=:p_supplier_site
LOV
Supplier/Site Status
(trunc(sysdate) <= nvl(aps.end_date_active,trunc(sysdate)) and trunc(sysdate) <= nvl(assa.inactive_date,trunc(sysdate))) and nvl(hps.status,'A') = 'A'
LOV
Bank Account Status
sysdate between nvl(ipiua0.start_date (+),sysdate) and nvl(ipiua0.end_date (+),sysdate) and
sysdate between nvl(ipiua2.start_date (+),sysdate) and nvl(ipiua2.end_date (+),sysdate)
LOV
Contact Status
trunc(sysdate) <= nvl((select hr.end_date from hz_relationships hr where hr.relationship_id = asco.relationship_id and hr.directional_flag = 'F') ,to_date('31/12/4712','DD/MM/YYYY'))
LOV
Update Supplier Bank Accounts
 
LOV Oracle
Update Supplier Sites
 
LOV Oracle
Update Site Bank Accounts
 
LOV
Update Site Contacts
 
LOV
Default Site Bank Acct Assign Lvl
 
LOV
Default Tax Rounding Rule
 
LOV
Default Inclusive Tax
 
LOV Oracle
Default Allow Offset Taxes
 
LOV Oracle
Default Pay Each Document Alone
 
LOV Oracle
Download
Blitz Report™