R12 Banks, Branches and Bank Accounts

The following query yields the bank, branch and and account numbers in Oracle eBusiness R12:

SELECT hp.party_name BANK_NAME
, hop.organization_name BANK_BRANCH_NAME
, hop.bank_or_branch_number BRANCH_SORT_CODE
, ieba.masked_bank_account_num BANK_ACCT_MASKED
, ieba.bank_account_num BANK_ACCT
FROM hz_parties hp
, hz_organization_profiles hop
, iby_ext_bank_accounts ieba
WHERE hp.party_type(+) = 'ORGANIZATION'
AND hop.party_id(+) = ieba.branch_id
AND hp.party_id(+) = ieba.bank_id
AND NVL(hp.status(+),'X') = 'A'
AND NVL(hop.effective_end_date(+),SYSDATE + 1) > SYSDATE
AND ieba.bank_account_num = '20286184'

And account owners…

FROM iby_account_owners
WHERE ext_bank_account_id = 48586

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 )

Connecting to %s