Supplier bank details query in oracle apps r12

Supplier bank details query in oracle apps r12

Here We are going to Discuss about Supplier Bank Information's sql query in Oracle apps. We do maintain the Supplier Banking Information's in Oracle System , When we are doing the payment of supplier electronically. When we are running the Payment Batch in oracle , System automatically fetch these Supplier Banking Information's and later on Sent to Bank for the Complete payment. We do store's supplier bank details in Supplier Master. We store the two levels of supplier Banking information's in Oracle and I will share both these two level Supplier bank details query in oracle apps r12.

 

 

Two levels of Supplier Bank Detail in Oracle Apps

1. Header Level

2. Supplier Site Level

 

When we store the Banking information of Supplier in Supplier Header level only , Then by default all the sites will use this same banking information.

 

When we store the Banking Information's of Supplier according to the Site Level. When each supplier site have their own banking Information.

Header Level Supplier bank details query in oracle apps r12

 select 'Suppplier Header' "Banking_Info" ,VENDOR_NAME,IEBA.BANK_ACCOUNT_NUM,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
where 1=1
AND APS.VENDOR_ID IN (select VENDOR_ID from apps.AP_SUPPLIER_SITES_ALL ass
where ORG_ID=:P_ORG_ID)
AND IEPA.PAYEE_PARTY_ID=APS.PARTY_ID
AND PARTY_SITE_ID IS NULL
AND 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(+)

Supplier Site Level bank details query in oracle apps 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(+)