Creating a R12 Supplier Bank accounts 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
