- OracleApps Epicenter - http://www.oracleappshub.com -

R12 Supplier Bank – Techno Functional Guide

Three banks you can manage in EBS

You can enter intermediary bank accounts on Suppliers->Entry->Banking Details->Bank Account Details

This is important when paying a foreign supplier from a domestic disbursement account, there may be an intermediary bank used, and it would be set up on the supplier bank account. Although the intermediary bank UI is owned by Payments, the implementation is as embeddable UI components in pages owned by i-supplier Portal (suppliers) and AR/Collections (customers).

Some information

  1. The supplier bank account information is in the table: IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table HZ_PARTIES.
  2. Creating a supplier in AP now creates a record in HZ_PARTIES. In the create Supplier screen, you will notice that that Registry_id is the party_number in HZ_Parties.
  3. The table hz_party_usg_assignments table stores the party_usage_code SUPPLIER, and also contains the given party_id for that supplier. Running this query will return if customer was a SUPPLIER or CUSTOMER
  4. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds
  5. IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.
  6. The master record that replaces PO_VENDORS is now AP_SUPPLIERS. PO_VENDORS is a view that joins AP_SUPPLIERS and HZ_PARTIES.
  7. The table that hold mappings between AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID is PO_SUPPLIER_MAPPINGS. Query by party_id.
  8. The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created.

ER Diagram(Bank Model)

Oracle Table Involved

 


Link between Supplier And Banks and TCA table

 

Driving Bank account associated with a Supplier Site in R12

In R12 a Supplier Site is stored, in TCA, as a Party_Site. The Party Site has the Party ID of the Party that represents the Supplier record.

QUERY1..try this

SELECT BANK_ACCOUNT_NAME "Account Name",
BANK_ACCOUNT_NUM "Account Number"
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 = 'site code'
)
)

QUERY2..try this

SELECT aba.bank_account_name "BANK_ACCOUNT_NAME",
aba.bank_account_num "BANK_ACCOUNT_NUMBER",
abau.order_of_preference "PRIMARY_FLAG",
aba.currency_code "CURRENCY",
abau.start_date "START DATE",
abau.end_date "END DATE",
pvs.vendor_site_id "VENDOR_SITE"
from iby_payee_assigned_bankacct_v abau ,
ap_supplier_sites pvs ,
iby_payee_all_bankacct_v aba
WHERE abau.ext_bank_account_id = aba.ext_bank_account_id
AND abau.supplier_site_id = pvs.vendor_site_id
AND abau.party_site_id = pvs.party_site_id ;

QUERY3..try this

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER"
FROM HZ_PARTIES HZP
, AP_SUPPLIERS APS
, HZ_PARTY_SITES SITE_SUPP
, AP_SUPPLIER_SITES_ALL ASS
, IBY_EXTERNAL_PAYEES_ALL IEP
, IBY_PMT_INSTR_USES_ALL IPI
, IBY_EXT_BANK_ACCOUNTS IEB
, HZ_PARTIES HZPBANK
, HZ_PARTIES HZPBRANCH
, HZ_ORGANIZATION_PROFILES HOPBANK
, HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3

 Releated Post

9 Comments (Open | Close)

9 Comments To "R12 Supplier Bank – Techno Functional Guide"

#1 Pingback By Customer Interface Vs TCA API | OracleApps Epicenter On October 28, 2008 @ 12:21 am

[…] EBSTrading Community Architecture (TCA) 101Dealing with “Addressee” on Customer MasterR12 Supplier Bank – Techno Functional GuideA note on sub ledger reconciliation reports during period closureSub Ledger Accounting – Technical […]

#2 Pingback By Quick note for Banking Details for Supplier in Release 12 | OracleApps Epicenter On October 29, 2008 @ 3:27 am

[…] EBSTrading Community Architecture (TCA) 101Dealing with “Addressee” on Customer MasterR12 Supplier Bank – Techno Functional GuideA note on sub ledger reconciliation reports during period closureSub Ledger Accounting – Technical […]

#3 Comment By mahipal On December 28, 2008 @ 2:25 am

Hi Anand,
Thanks for your valuable information.
I am working on Customer banks conversion.
My requirement is to load the banks details and assign the banks to customer site using API

I used IBY_EXT_BANKACCT_PUB to load the bank details .Data is loading into Bank tables.
HZ_PARTIES,iby_ext_bank_accounts IBY_ACCOUNT_OWNERS

But Banks are not assigned to the customer.
We used the API iby_disbursement_setup_pub.set_payee_instr_assignment to assign bank accounts to the suppliers.
Can we use the same API to assign the bank accounts to customer?

Thanks in advance.

Regards,
Mahipal

#4 Comment By Tomislav Kusanic On January 26, 2009 @ 8:07 am

Hi Mahipal,
on my current project I have to load bank details for suppliers but can’t seem to find any documentation on this. On Metalink some Bank Account tables are mentioned but I can’t seem to find them.
Can you please explain the process of importing suppliers bank accounts combined with importing of suppliers.
Do we first import Suppliers and then bank accounts or what?
Regards,
Tomislav

#5 Comment By Gareth Roberts On March 1, 2009 @ 9:18 pm

Hi,
Query3 has bugs:

The first:
, HOPBRANCH.BANK_OR_BRANCH_NUMBER “BANK NUMBER”
Should be:
, HOPBANK.BANK_OR_BRANCH_NUMBER “BANK NUMBER”

And

AND IEB.BANK_ID = HZPBRANCH.PARTY_ID

Should be:

AND IEB.BRANCH_ID = HZPBRANCH.PARTY_ID

Regards,
Gareth

#6 Comment By Mastan On March 19, 2009 @ 1:01 am

Hello,

i wanted to used query 3. But it is fetching duplicate values. Please help

Mastan

#7 Comment By Sanjit On March 7, 2010 @ 9:15 pm

Hi,
Please send me the procedure to upload customer in oracle APPS R12 from Legacy systems.
Thanks,
Sanjit de
[5]

#8 Comment By Dember On November 26, 2010 @ 2:53 pm

Hi!

Nice code,
I have the Query2 and it works fine for me.

nice work!

#9 Comment By neha On August 30, 2011 @ 5:05 am

Thanks…Just wht i wnted…:)