R12 Supplier Bank – Techno Functional Guide

Three banks you can manage in EBS

• House Bank or internal bank
• External bank for supplier and Customer
• Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.
• Intermediary bank for SEPA payment : An intermediary bank is a financial institution that as a relationship with the destination bank (in this case the supplier bank account you are setting up) which is not a direct correspondent of the source bank (the disbursement bank in AP/Payments), which facilities the funds transfer to the destination bank.

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

• 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 .This information is stored in the following iPayment (IBY) tables:

 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

 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

API AP_VENDOR_PUB_PKG.Update_Vendor_Site to update the Payment Method at Supplier Site

 am using api AP_VENDOR_PUB_PKG.Update_Vendor_Site to update the Payment Method at Supplier Site level. The below code executes with no error but dont update the payment method. The requirement is to update the payment method to SEPA

Code :

DECLARE

v_error_reason VARCHAR2 (2000) := NULL;
v_msg_data VARCHAR2 (1000) := NULL;
v_msg_count NUMBER := NULL;
v_return_status VARCHAR2 (100) := NULL;
v_vensite_rec_type      AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
ext_payee_rec           IBY_DISBURSEMENT_SETUP_PUB.EXTERNAL_PAYEE_REC_TYPE;

BEGIN

DBMS_OUTPUT.put_line ('BEFORE apps initialization');

--Fnd_Global.apps_initialize(-1,20639,200);

fnd_global.apps_initialize(730908,141200,401);

--DBMS_OUTPUT.put_line ('AFTER apps initialization'); 

v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;

--ext_payee_rec.default_pmt_method := 'SEPA';
v_vensite_rec_type.org_id := 2796;
v_vensite_rec_type.vendor_site_code := '0653-01NEUSS';
v_vensite_rec_type.ext_payee_rec.default_pmt_method := 'SEPA';
--v_vensite_rec_type.ext_payee_rec.default_pmt_method:= 'CHECK';

DBMS_OUTPUT.put_line ('BEFORE remittance API');

AP_VENDOR_PUB_PKG.Update_Vendor_Site
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status =>v_return_status ,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_vendor_site_rec =>v_vensite_rec_type,
p_vendor_site_id => '393130'

);

commit;

DBMS_OUTPUT.put_line ('AFTER pymt mtd API');
DBMS_OUTPUT.put_line (v_return_status);
DBMS_OUTPUT.put_line (v_msg_count);
DBMS_OUTPUT.put_line (v_msg_data);

IF v_return_status = fnd_api.g_ret_sts_success THEN
     IF v_msg_count >= 1 THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL THEN
v_error_reason := SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);
ELSE
v_error_reason := v_error_reason|| ' ,'|| SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);
END IF;
DBMS_OUTPUT.put_line ('VENDOR_remittance email UPDATE API ERROR-' || v_error_reason);
END LOOP;
END IF;
ROLLBACK;
ELSE

DBMS_OUTPUT.put_line ('The updateion is sucessful');

COMMIT;

END IF;
EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.put_line (SQLERRM || '-' || SQLCODE);
END;

Query to find attachments of a contract 

SELECT adoc.creation_date Creation_date
,docs.last_update_date
,docs.document_id
,adoc.attached_document_id
,docs.category_id
,DECODE (docs.datatype_id,1,docs_tl.media_id,NULL) text_Id
,docs.datatype_id
,adoc.pk1_value Contract_Number
,docs_tl.doc_attribute2 file_size
,adoc.automatically_added_flag
,adoc.created_by
,DECODE (docs.datatype_id,5,docs_tl.file_name,NULL) link_Url
,docs_tl.LANGUAGE
,DECODE (docs.datatype_id,6,docs_tl.file_name,NULL) File_Name
,docs_tl.description
,doc_s_text.short_text
,DECODE (docs.datatype_id,6,docs_tl.media_id,NULL) fileId
,docs.publish_flag
,docs_tl.file_name
FROM fnd_attached_documents adoc
,fnd_documents docs
,fnd_documents_tl docs_tl
,fnd_documents_short_text doc_s_text
WHERE 1 = 1
AND adoc.document_id = docs.document_id
AND docs.document_id = docs_tl.document_id
AND docs_tl.LANGUAGE = USERENV (‘LANG’)
AND docs_tl.media_id = doc_s_text.media_id(+)
AND adoc.entity_name = 'OKC_K_HEADERS_V'
AND pk1_value = '1081665' -- Enter your contract number here.

Very large files are stored in the fnd_lobs table

To check the attached file in fnd_lobs table

1. Note Document ID from above query.
2. Take media ID for this document ID from fnd_documents_tl table.
3. run following query:
select * from fnd_lobs where file_id = (media id from 2nd query)