How to Get Supplier Bank Detail In R12

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. After going thru the guides, it seems that the bank information (such as bank account numbers, etc) for suppliers may be in TCA or in Cash Management Bank Model. Please provide the tables names where bank account information and other bank related information is stored?

By Using Following Queries you can get Required Information on Basis on Supplier Site Name

/* For Supplier Bank Account Number Use This One */

SELECT bank_account_name, bank_account_num, branch_id

FROM iby_ext_bank_accounts

WHERE ext_bank_account_id IN (

SELECT ext_bank_account_id

FROM iby_account_owners

WHERE account_owner_party_id IN (

SELECT party_id

FROM hz_party_sites

WHERE party_site_name LIKE

‘%UPC%’))

/* USE THIS QUERY TO GET BANK NAME AND BANK BRANCH NAME OF SUPPLIER BANK*/

SELECT BANKORGPROFILE.HOME_COUNTRY BANK_HOME_COUNTRY,

BANKORGPROFILE.PARTY_ID BANK_PARTY_ID,

BANKORGPROFILE.ORGANIZATION_NAME BANK_NAME,

BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER,

BRANCHPARTY.PARTY_ID BRANCH_PARTY_ID,

BRANCHPARTY.PARTY_NAME BANK_BRANCH_NAME,

BRANCHPARTY.PARTY_ID

FROM HZ_ORGANIZATION_PROFILES BANKORGPROFILE,

HZ_CODE_ASSIGNMENTS BANKCA,

HZ_PARTIES BRANCHPARTY,

HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,

HZ_CODE_ASSIGNMENTS BRANCHCA,

HZ_RELATIONSHIPS BRREL,

HZ_CODE_ASSIGNMENTS BRANCHTYPECA,

HZ_CONTACT_POINTS BRANCHCP,

HZ_CONTACT_POINTS EDICP

WHERE SYSDATE BETWEEN TRUNC (BANKORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BANKORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BANKCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BANKCA.CLASS_CODE IN (‘BANK’, ‘CLEARINGHOUSE’)

AND BANKCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BANKCA.STATUS = ‘A’ OR BANKCA.STATUS IS NULL)

AND BANKCA.OWNER_TABLE_ID = BANKORGPROFILE.PARTY_ID

AND BRANCHPARTY.PARTY_TYPE = ‘ORGANIZATION’

AND BRANCHPARTY.STATUS = ‘A’

AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID

AND SYSDATE BETWEEN TRUNC (BRANCHORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BRANCHORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BRANCHCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BRANCHCA.CLASS_CODE IN (‘BANK_BRANCH’, ‘CLEARINGHOUSE_BRANCH’)

AND BRANCHCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BRANCHCA.STATUS = ‘A’ OR BRANCHCA.STATUS IS NULL)

AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID

AND BANKORGPROFILE.PARTY_ID = BRREL.OBJECT_ID

AND BRREL.RELATIONSHIP_TYPE = ‘BANK_AND_BRANCH’

AND BRREL.RELATIONSHIP_CODE = ‘BRANCH_OF’

AND BRREL.STATUS = ‘A’

AND BRREL.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.SUBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.OBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.OBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.SUBJECT_ID = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.CLASS_CATEGORY(+) = ‘BANK_BRANCH_TYPE’

AND BRANCHTYPECA.PRIMARY_FLAG(+) = ‘Y’

AND BRANCHTYPECA.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHTYPECA.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.STATUS(+) = ‘A’

AND BRANCHCP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHCP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHCP.CONTACT_POINT_TYPE(+) = ‘EFT’

AND BRANCHCP.STATUS(+) = ‘A’

AND EDICP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND EDICP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND EDICP.CONTACT_POINT_TYPE(+) = ‘EDI’

AND EDICP.STATUS(+) = ‘A’

AND BRANCHCA.OWNER_TABLE_ID = :IBY_BRANCH_ID /*USER BRANCH ID FROM ABOVE QUERY*/

If anyone have a better solution then please update

Advertisements

12 thoughts on “How to Get Supplier Bank Detail In R12

  1. Hi,

    Thanks for posting the query. It is very difficult in R12 to get the Supplier/Banking information. Below query will provide all results in one line. Let me know if you find any issues with the query.

    select s.vendor_name, ss.vendor_site_code, eb.bank_name, ebb.bank_branch_name, ebb.branch_number,
    eba.BANK_ACCOUNT_NUM, eba.BANK_ACCOUNT_NAME
    from ap.ap_suppliers s, ap.ap_supplier_sites_all ss, apps.iby_ext_bank_accounts eba,
    apps.iby_account_owners ao, apps.iby_ext_banks_v eb, apps.iby_ext_bank_branches_v ebb
    where s.vendor_id = ss.vendor_id
    and ao.account_owner_party_id = s.party_id
    and eba.ext_bank_account_id = ao.ext_bank_account_id
    and eb.bank_party_id = ebb.bank_party_id
    and eba.branch_id = ebb.branch_party_id
    and eba.bank_id = eb.bank_party_id

  2. Nimal,

    Thanks , I spent 5 hour to get this information, Plz tell me how to get payment methods agianst a supplier,

    Plz help out,

    Zubyr

  3. Excellent Nirmal, I spent more than 48 hours and tried minimum half dozen scripts before finding one by you.

    Thank you very much buddy

  4. Hi Nimal
    Thank you very much, i also spent more then 3 hours to find it, and your script did it 🙂

    Thanks & Regards,
    FAHAD

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