script for Supplier Bank account in R12 using API

After the Supplier or Supplier Site is validated and a row entered in the various AP and HZ tables, a Payee is created in IBY (the new Payments application) for the Supplier or Supplier Site. If the Payee is successfully created, we then check to see if there are any corresponding rows in IBY_TEMP_EXT_BANK_ACCTS. If there are, we call an IBY API to create the Bank Account and associate it with the Payee. So to import supplier bank  accounts during Supplier and Supplier Site Open Interface, you can populate the IBY_TEMP_EXT_BANK_ACCTS table. A row in   IBY_TEMP_EXT_BANK_ACCTS is said to be associated with the Supplier or Supplier Site if the column IBY_TEMP_EXT_BANK_ACCTS. calling_app_unique_ref1 is equal to either AP_SUPPLIERS_INT.vendor_interface_id for Suppliers or  IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2 is equal to AP_SUPPLIER_SITES_INT.vendor_site_interface_id for Supplier Sites. The bank and bank branch referenced in IBY_TEMP_EXT_BANK_ACCTS must already exist in the system. There is no functionality in the Bank Account Import to create the bank and/or bank branch. This functionality associates the new supplier to an existing bank and/or bank branch.

Some information

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.

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.

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

Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds

IBY_EXT_BANK_ACCOUNTS, the bank and bank branches information is in the table: HZ_PARTIES.

The master record that replaces PO_VENDORS is now AP_SUPPLIERS. PO_VENDORS is a view that joins AP_SUPPLIERS and HZ_PARTIES.

The table that hold mappings between AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID is PO_SUPPLIER_MAPPINGS. Query by party_id.

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.

For  Importing internal and external bank account we can use the following API's

1. API to create External Customer Bank IBY_EXT_BANKACCT_PUB.create_ext_bank

2. Bank Branch
IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

3. Customer Bank Account
IBY_EXT_BANKACCT_PUB.create_ext_bank_acct

4. Instrument Assignment
IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment

5. Payer Attributes
IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes

6. For the Branch Address
hz_location_v2pub.create_location
hz_party_site_v2pub.create_party_site

7. Contacts
HZ_PARTY_V2PUB.create_person
hz_party_contact_v2pub.create_org_contact
hz_party_contact_v2pub.create_org_contact_role
HZ_CONTACT_POINT_V2PUB.create_contact_point 

Oracle Table Involved

IBY_EXTERNAL_PAYEES_ALL : This stores supplier information and customer information

IBY_EXT_BANK_ACCOUNTS : This storage for bank accounts

IBY_EXT_PARTY_PMT_MTHDS : This storage for payment method usage rules.

IBY_CREDITCARD : stores the credit card information for a customer

IBY_EXT_BANK_ACCOUNTS :This Stores external bank accounts . These records have bank_account_type = Supplier

IBY_ACCOUNT_OWNERS :stores the joint account owners of a bank account

IBY_PMT_INSTR_USES_ALL : This stores data from AP_BANK_ACCOUNT_USES_ALL for payment instruments assignments .

Link between Supplier And Banks and TCA table

The link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id. The link between PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.

When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS

When a bank Is Created, the banking information will be stored in IBY_EXT_BANK_ACCOUNTS IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id

When the Bank is assigned to Vendors then it will be updated in HZ_CODE_ASSIGNMENTS.

HZ_CODE_ASSIGNMENTS.owner_table_id = IBY_EXT_BANK_ACCOUNTS.branch_id.

The PARTY_SITE_ID column is the link between the tables IBY_EXTERNAL_PAYEES_ALL & PO_VENDOR_SITES_ALL

Example procedure::

CREATE OR REPLACE procedure APPS.xxsup_bank_test_v2
as
x_bank_rec  IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
x_bank_id   NUMBER;
x_return_status  VARCHAR2(10);
x_msg_count   number;
x_msg_data    varchar2(256);
x_response_rec   IBY_FNDCPT_COMMON_PUB.Result_rec_type;
x_bank_branch_rec  IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;

x_branch_id number;
x_acct_id  number;
x_bank_acct_rec  IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type; 
p_ext_payee_tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type ;
x_ext_payee_id_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type ;
x_ext_payee_status_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type ;
l_ext_payee_rec IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type ;

l_ext_payee_id_rec_type IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Rec_Type ;

p_payee IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type ;
p_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type ;
p_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type ;
x_assign_id  number;

begin
dbms_output.put_line('Start of procedure.');
fnd_global.apps_initialize (fnd_global.user_id,
                                  fnd_global.resp_id,
                                  fnd_global.resp_appl_id
                                 );
dbms_output.put_line('Procedure initializad.');
x_bank_rec.bank_name := 'ran_sbi71';
x_bank_rec.bank_number := 23591;
x_bank_rec.institution_type := 'BANK';     -- hz_code_assignments .CLASS_CODE
x_bank_rec.country_code := 'SA' ;
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
dbms_output.put_line('before External bank creation.');

IBY_EXT_BANKACCT_PUB.create_ext_bank
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_rec => x_bank_rec
,x_bank_id => x_bank_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);
dbms_output.put_line('External bank created.');
dbms_output.put_line(' bank id. '||x_bank_id);
dbms_output.put_line('x_return_status: '||x_return_status);
dbms_output.put_line('x_msg_count. '||x_msg_count);
dbms_output.put_line('x_msg_data. '||x_msg_data);

x_bank_branch_rec.bank_party_id  := x_bank_id ;
x_bank_branch_rec.branch_name  := 'xxran_branch71' ;
x_bank_branch_rec.branch_number  := 23592 ;
x_bank_branch_rec.branch_type  := 'OTHER' ;        --defined in lookup as BANK BRANCH TYPE
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_branch_rec => x_bank_branch_rec
,x_branch_id => x_branch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);
dbms_output.put_line('External bank Branch created.');
dbms_output.put_line('x_branch_id: '||x_branch_id);
dbms_output.put_line('x_return_status: '||x_return_status);
dbms_output.put_line('x_msg_count. '||x_msg_count);
dbms_output.put_line('x_msg_data. '||x_msg_data);

x_bank_acct_rec.country_code  := 'SA' ;
x_bank_acct_rec.branch_id  := x_branch_id ;
x_bank_acct_rec.bank_id  := x_bank_id ;
x_bank_acct_rec.acct_owner_party_id  := 325685;        --supplier party id
x_bank_acct_rec.currency  := 'USD' ;
x_bank_acct_rec.bank_account_name  :=  'xxran account6';
x_bank_acct_rec.bank_account_num  := 23593 ;
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_acct_rec => x_bank_acct_rec
,x_acct_id => x_acct_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);  
dbms_output.put_line('External bank account created.');
dbms_output.put_line('x_acct_id'||x_acct_id);
dbms_output.put_line('x_return_status'||x_return_status);
dbms_output.put_line('x_msg_count.'||x_msg_count);
dbms_output.put_line('x_msg_data.'||x_msg_data);

l_ext_payee_rec.Payee_Party_Site_Id := 189630;
l_ext_payee_rec.Payee_Party_Id := 325685;
l_ext_payee_rec.payment_function := 'PAYABLES_DISB';
l_ext_payee_rec.payer_org_id := 204;
l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT';
l_ext_payee_rec.Exclusive_Pay_Flag := 'N';
l_ext_payee_rec.Default_Pmt_method := 'XX_REP_VIREMENT';
l_ext_payee_rec.Supplier_Site_Id := 6930;
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
p_ext_payee_tab(0) := l_ext_payee_rec;

IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee(
p_api_version  => 1.0,
p_init_msg_list  => fnd_api.G_TRUE,
p_ext_payee_tab  => p_ext_payee_tab,
x_return_status  => x_return_status,
x_msg_count  => x_msg_count,
x_msg_data  => x_msg_data,
x_ext_payee_id_tab  =>  x_ext_payee_id_tab,
x_ext_payee_status_tab  => x_ext_payee_status_tab
);

dbms_output.put_line('External Payee created.');
dbms_output.put_line('x_return_status: '||x_return_status);
dbms_output.put_line('x_msg_count. '||x_msg_count);
dbms_output.put_line('x_msg_data. '||x_msg_data);

p_payee.Supplier_Site_id := 6930; 
p_payee.Party_Id := 325685;                                     
p_payee.Party_Site_Id := 189630;                              
p_payee.Payment_Function := 'PAYABLES_DISB';
p_payee.Org_Id := 204;
p_payee.Org_Type := 'OPERATING_UNIT';
l_ext_payee_id_rec_type := x_ext_payee_id_tab(0);
p_instrument.Instrument_Id := x_acct_id;
p_instrument.Instrument_Type := 'BANKACCOUNT';
p_assignment_attribs.priority := 1;
p_assignment_attribs.Instrument := p_instrument;
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
x_response_rec  := null;

IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
p_api_version  => 1.0,
p_init_msg_list  => fnd_api.G_TRUE,
p_commit  => fnd_api.G_TRUE,
x_return_status  => x_return_status,
x_msg_count  => x_msg_count,
x_msg_data  => x_msg_data,
p_payee  => p_payee,
p_assignment_attribs  => p_assignment_attribs,
x_assign_id  => x_assign_id,
x_response  => x_response_rec
);

dbms_output.put_line('Payee_Instr_Assignment.');
dbms_output.put_line('x_assign_id'||x_assign_id);
dbms_output.put_line('x_return_status'||x_return_status);
dbms_output.put_line('x_msg_count.'||x_msg_count);
dbms_output.put_line('x_msg_data.'||x_msg_data);

exception
when others then
dbms_output.put_line('Error occurred during procedure.');
dbms_output.put_line('sqlcode: '||sqlcode||' Sqlerrm: '||substr(sqlerrm,1,255));
end xxsup_bank_test_v2;
/


You can get the related party_id, vendor_site_id and party_site_id   by using the following query.


select
hp.party_id,hp.party_name,apss.vendor_site_id,hps.party_site_id
from hz_parties hp,
hz_party_sites hps,
ap_suppliers aps,
ap_supplier_sites_all apss
where
hp.party_id = aps.party_id
and hp.party_id = hps.party_id
and aps.vendor_id = apss.vendor_id
order by hp.creation_date desc

Query to get bank details with supplier details

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