SELECT ieba.ext_bank_account_id
, ieba.country_code
, ieba.branch_id
, ieba.bank_id
, ieba.bank_account_num
, ieba.masked_bank_account_num
, ieba.ba_mask_setting
, ieba.ba_unmask_length
, ieba.currency_code
, ieba.iban
, ieba.iban_hash1
, ieba.iban_hash2
, ieba.masked_iban
, ieba.check_digits
, ieba.bank_account_type
, ieba.account_classification
, ieba.account_suffix
, ieba.agency_location_code
, ieba.payment_factor_flag
, ieba.foreign_payment_use_flag
, ieba.exchange_rate_agreement_num
, ieba.exchange_rate_agreement_type
, ieba.exchange_rate
, ieba.hedging_contract_reference
, ieba.secondary_account_reference
, ieba.ba_num_sec_segment_id
, ieba.encrypted
, ieba.iban_sec_segment_id
, ieba.attribute_category
, ieba.attribute1
, ieba.attribute2
, ieba.attribute3
, ieba.attribute4
, ieba.attribute5
, ieba.attribute6
, ieba.attribute7
, ieba.attribute8
, ieba.attribute9
, ieba.attribute10
, ieba.attribute11
, ieba.attribute12
, ieba.attribute13
, ieba.attribute14
, ieba.attribute15
, ieba.request_id
, ieba.program_application_id
, ieba.program_id
, ieba.program_update_date
, ieba.start_date
, ieba.end_date
, ieba.created_by
, ieba.creation_date
, ieba.last_updated_by
, ieba.last_update_date
, ieba.last_update_login
, ieba.object_version_number
, ieba.bank_account_name
, ieba.bank_account_name_alt
, ieba.short_acct_name
, ieba.description
, ieba.bank_account_num_electronic
, ieba.ba_num_elec_sec_segment_id
, ieba.salt_version
, ieba.contact_name
, ieba.contact_phone
, ieba.contact_fax
, ieba.contact_email
, aps.vendor_id
, hopbank.bank_or_branch_number "BANK_NUMBER"
, hopbranch.bank_or_branch_number "BRANCH_NUMBER"
, hzpbank.party_name "BANK_NAME"
, hzpbranch.party_name "BRANCH_NAME"
, hopbank.home_country
, ipi.order_of_preference
, ipi.start_date assign_date
, ipi.end_date assign_end_date
, ipi.last_update_date last_date
, aps.segment1
, aps.vendor_name
FROM hz_parties hzp
, ap_suppliers aps
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieba
, hz_parties hzpbank
, hz_parties hzpbranch
, hz_organization_profiles hopbranch
, hz_organization_profiles hopbank
, iby_account_owners iao
WHERE hzp.party_id = aps.party_id
AND iep.payee_party_id = hzp.party_id
AND hzp.party_id = iao.account_owner_party_id
AND ieba.ext_bank_account_id = iao.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieba.ext_bank_account_id
AND ieba.bank_id = hzpbank.party_id
AND ieba.branch_id = hzpbranch.party_id
AND hzpbranch.party_id = hopbranch.party_id
AND hzpbank.party_id = hopbank.party_id
-- AND aps.segment1 = " <Supplier Number>"
AND NVL (TO_CHAR (aps.end_date_active, 'DD-MON-YYYY'), SYSDATE + 1) > SYSDATE
AND iep.inactive_date IS NULL
AND iep.org_type IS NULL
AND iep.supplier_site_id IS NULL;
, ieba.country_code
, ieba.branch_id
, ieba.bank_id
, ieba.bank_account_num
, ieba.masked_bank_account_num
, ieba.ba_mask_setting
, ieba.ba_unmask_length
, ieba.currency_code
, ieba.iban
, ieba.iban_hash1
, ieba.iban_hash2
, ieba.masked_iban
, ieba.check_digits
, ieba.bank_account_type
, ieba.account_classification
, ieba.account_suffix
, ieba.agency_location_code
, ieba.payment_factor_flag
, ieba.foreign_payment_use_flag
, ieba.exchange_rate_agreement_num
, ieba.exchange_rate_agreement_type
, ieba.exchange_rate
, ieba.hedging_contract_reference
, ieba.secondary_account_reference
, ieba.ba_num_sec_segment_id
, ieba.encrypted
, ieba.iban_sec_segment_id
, ieba.attribute_category
, ieba.attribute1
, ieba.attribute2
, ieba.attribute3
, ieba.attribute4
, ieba.attribute5
, ieba.attribute6
, ieba.attribute7
, ieba.attribute8
, ieba.attribute9
, ieba.attribute10
, ieba.attribute11
, ieba.attribute12
, ieba.attribute13
, ieba.attribute14
, ieba.attribute15
, ieba.request_id
, ieba.program_application_id
, ieba.program_id
, ieba.program_update_date
, ieba.start_date
, ieba.end_date
, ieba.created_by
, ieba.creation_date
, ieba.last_updated_by
, ieba.last_update_date
, ieba.last_update_login
, ieba.object_version_number
, ieba.bank_account_name
, ieba.bank_account_name_alt
, ieba.short_acct_name
, ieba.description
, ieba.bank_account_num_electronic
, ieba.ba_num_elec_sec_segment_id
, ieba.salt_version
, ieba.contact_name
, ieba.contact_phone
, ieba.contact_fax
, ieba.contact_email
, aps.vendor_id
, hopbank.bank_or_branch_number "BANK_NUMBER"
, hopbranch.bank_or_branch_number "BRANCH_NUMBER"
, hzpbank.party_name "BANK_NAME"
, hzpbranch.party_name "BRANCH_NAME"
, hopbank.home_country
, ipi.order_of_preference
, ipi.start_date assign_date
, ipi.end_date assign_end_date
, ipi.last_update_date last_date
, aps.segment1
, aps.vendor_name
FROM hz_parties hzp
, ap_suppliers aps
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieba
, hz_parties hzpbank
, hz_parties hzpbranch
, hz_organization_profiles hopbranch
, hz_organization_profiles hopbank
, iby_account_owners iao
WHERE hzp.party_id = aps.party_id
AND iep.payee_party_id = hzp.party_id
AND hzp.party_id = iao.account_owner_party_id
AND ieba.ext_bank_account_id = iao.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieba.ext_bank_account_id
AND ieba.bank_id = hzpbank.party_id
AND ieba.branch_id = hzpbranch.party_id
AND hzpbranch.party_id = hopbranch.party_id
AND hzpbank.party_id = hopbank.party_id
-- AND aps.segment1 = " <Supplier Number>"
AND NVL (TO_CHAR (aps.end_date_active, 'DD-MON-YYYY'), SYSDATE + 1) > SYSDATE
AND iep.inactive_date IS NULL
AND iep.org_type IS NULL
AND iep.supplier_site_id IS NULL;
No comments:
Post a Comment