$Applaud$

Supplier Site Level Bank Account Details in Oracle Apps R12 - Orapoint

  • Home
  • Supplier Site Level Bank Account Details in Oracle Apps R12

This query will fetch supplier site level bank account details with certain columns like supplier name, bank name, bank account number, bank branch name, bank branch city and many more in Oracle Apps R12

Supplier Bank Account Table:

AP_SUPPLIERS

IBY_EXTERNAL_PAYEES_ALL

IBY_PMT_INSTR_USES_ALL

IBY_EXT_BANK_ACCOUNTS

ce_banks_v

ce_bank_BRANCHES_V

AP_SUPPLIER_SITES_ALL

 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(+);