/*Query to get All bank accounts in Oracle EBS R12 */
SELECT A10.HOME_COUNTRY "Country",
a10.bank_name "Bank Name",
CBA.BANK_ACCOUNT_NAME "Account Name",
A10.BANK_PARTY_ID "Bank Code",
BB.BANK_BRANCH_NAME "Branch Name",
BB.BANK_BRANCH_ID "Bank Brach Code",
BB.BANK_NUMBER "BANK BRANCH NUMBER",
NULL "BIC Code",
CBA.BANK_ACCOUNT_NAME "Bank Account Name",
CBA.BANK_ACCOUNT_NUM "Bank Account Number",
CBA.CURRENCY_CODE "Currency",
OU.NAME "Primary Ledger",
CBA.BANK_ACCOUNT_TYPE "Account Type",
CBA.IBAN_NUMBER "IBAN",
CBA.AP_USE_ALLOWED_FLAG "Payable Use",
CBA.AR_USE_ALLOWED_FLAG "Receivable Use",
(SELECT GCF.SEGMENT1
|| '.'
|| GCF.SEGMENT2
|| '.'
|| GCF.SEGMENT3
|| '.'
|| GCF.SEGMENT4
|| '.'
|| GCF.SEGMENT5
FROM APPS.GL_CODE_COMBINATIONS_KFV GCF
WHERE GCF.CODE_COMBINATION_ID = CBA.BANK_CHARGES_CCID) "Cash Clearing Account",
(SELECT GCF.SEGMENT1
|| '.'
|| GCF.SEGMENT2
|| '.'
|| GCF.SEGMENT3
|| '.'
|| GCF.SEGMENT4
|| '.'
|| GCF.SEGMENT5
FROM APPS.GL_CODE_COMBINATIONS_KFV GCF
WHERE GCF.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID) "Cash Clearing Account"
FROM APPS.CE_BANK_ACCOUNTS CBA, APPS.CE_BANK_ACCT_USES_ALL BAU, APPS.CEFV_BANK_BRANCHES BB,
APPS.HR_OPERATING_UNITS OU, APPS.GL_CODE_COMBINATIONS_KFV GCF,
CE_BANKS_V A10WHERE 1 = 1 1 AND A10.BANK_NAME = BB.BANK_NAME
AND CBA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID = BB.BANK_BRANCH_ID
AND OU.ORGANIZATION_ID = BAU.ORG_ID
AND CBA.ASSET_CODE_COMBINATION_ID = GCF.CODE_COMBINATION_ID
AND OU.ORGANIZATION_ID = :P1 -- Add bussniess unit id
AND (cba.end_date IS NULLOR cba.end_date > TRUNC(SYSDATE))Use this query to retrieve all bank accounts in oracle EBS R12 while building new report,
In case you are in a process of migrating ERP system from Oracle E-business to Oracle Fusion cloud,
It’s essential to extract all bank accounts data and integrate the output into an FBDI file for uploading.
