Query to get Customer Bank Account Details
oracle apps ebs Customer Bank Account Details
oracle apps ebs sql for Customer Bank
SELECT cust.party_name customer_name,
cust_acct.account_number,
cust_uses.site_use_code,
cust_loc.address1,
cust_loc.address2,
cust_loc.address3,
cust_loc.address4,
cust_loc.city,
cust_loc.postal_code,
bank.party_name bank_name,
bank_prof.home_country,
branch.party_name branch_name,
branch_prof.bank_or_branch_number branch_number,
account.bank_account_num,
account.bank_account_name,
account.IBAN,
ixbv.EFT_SWIFT_CODE,
ixpm.PAYMENT_METHOD_CODE,
hou.name
FROM hz_parties bank,
hz_relationships rel,
hz_parties branch,
hz_organization_profiles bank_prof,
hz_organization_profiles branch_prof,
iby_ext_bank_accounts account,
iby_account_owners acc_owner,
iby_external_payers_all ext_payer,
iby_pmt_instr_uses_all acc_instr,
hz_parties cust,
hz_cust_accounts cust_acct,
hz_cust_acct_sites_all cust_site,
hz_cust_site_uses_all cust_uses,
hz_locations cust_loc,
hz_party_sites hps,
iby_ext_party_pmt_mthds ixpm,
IBY_EXT_BANK_BRANCHES_V ixbv,
hr_operating_units hou
WHERE 1 = 1
AND bank.party_id = rel.object_id
AND hps.party_id = cust.PARTY_ID
AND hps.PARTY_SITE_ID = cust_site.PARTY_SITE_ID
AND hps.location_id = cust_loc.location_id
AND bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust.party_id = cust_acct.party_id
AND SYSDATE BETWEEN (bank_prof.EFFECTIVE_START_DATE)
AND NVL (bank_prof.EFFECTIVE_END_DATE, SYSDATE + 1)
AND SYSDATE BETWEEN (branch_prof.EFFECTIVE_START_DATE)
AND NVL (branch_prof.EFFECTIVE_END_DATE, SYSDATE + 1)
AND ixpm.EXT_PMT_PARTY_ID = ext_payer.EXT_PAYER_ID
AND ixpm.PRIMARY_FLAG = 'Y'
AND ixpm.INACTIVE_DATE IS NULL
AND cust_site.org_id = ORGANIZATION_ID
AND hou.name LIKE '<Operating Unit Name>'
AND ixbv.branch_party_id = branch.party_id
AND ixbv.BANK_PARTY_ID = bank.party_id
AND ixpm.PAYMENT_METHOD_CODE = 'EFT'
Related posts:
Upload your own post and refer it anywhere anytime:
Like this:
Like Loading...