Query for Bank Account information for Release R12

Query that retrieves bank account details at the Supplier Level for Release R12

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;
Advertisements
Comments
One Response to “Query for Bank Account information for Release R12”
  1. Durgarao G says:

    TCA related content is so informative and it’s very helpful for the beginners to understand the complexed TCA in R12. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: