Query to get supplier site bank details in r12

Query to get supplier site bank details in r12

Here I will be share the sql query to get the Supplier site Bank Details. We all know that , we need the Supplier Banking Information's for Payments. When we are doing the payment for supplier we need to send the Supplier Banking information to our internal banks for the Electronics payments. We do store two levels of Supplier Information in Oracle r12 which is Supplier Header level and Supplier Site level. When all the Supplier sites are sharing the same banking information's then we don't need to maintain the Supplier Bank details for all site. We just need to maintain it under Supplier Header but if the supplier sites have different banks and banking information's then its recommended to store supplier site bank details as per site level.

 

 

 

 

Important Table of Supplier Banking Information's in R12

1.IBY_EXTERNAL_PAYEES_ALL

2.IBY_PMT_INSTR_USES_ALL

3.IBY_EXT_BANK_ACCOUNTS
4.CE_BANKS_V

5.CE_BANK_BRANCHES_V

 

SQL Query to get supplier site bank details in r12

select 'Suppplier Site' "Banking_Info" VENDOR_NAME,IEBA.BANK_ACCOUNT_NUM,IBAN,IEBA.BANK_ACCOUNT_NAME,cbv.BANK_NAME,cbv.ADDRESS_LINE1 BANK_ADDRESS_1,CBV.COUNTRY BANK_COUNTRY,
CBV.CITY BANK_CITY,
CBBV.BANK_BRANCH_NAME,CBBV.ADDRESS_LINE1 BRANCH_ADDRESS_1,CBBV.CITY BRANCH_CITY,CBBV.COUNTRY BRANCH_COUNTRY,CBBV.BRANCH_NUMBER,CBBV.EFT_SWIFT_CODE BIC ,FOREIGN_PAYMENT_USE_FLAG
from apps.AP_SUPPLIERS APS,apps.IBY_EXTERNAL_PAYEES_ALL IEPA,apps.IBY_PMT_INSTR_USES_ALL IPIUA,APPS.IBY_EXT_BANK_ACCOUNTS IEBA
,apps.ce_banks_v cbv, apps.ce_bank_BRANCHES_V CBBV, apps.AP_SUPPLIER_SITES_ALL ass
where 1=1
AND APS.VENDOR_ID=ASS.VENDOR_ID
AND ASS.ORG_ID=:P_ORG_ID
AND IEPA.PAYEE_PARTY_ID=APS.PARTY_ID
AND IEPA.PARTY_SITE_ID IS NULL
AND IEPA.SUPPLIER_SITE_ID IS NULL
AND IPIUA.EXT_PMT_PARTY_ID(+)=IEPA.EXT_PAYEE_ID
AND IEBA.EXT_BANK_ACCOUNT_ID(+)=IPIUA.INSTRUMENT_ID
AND IEBA.BANK_ID=cbv.BANK_PARTY_ID(+)
AND IEBA.BRANCH_ID=CBBV.BRANCH_PARTY_ID(+)