In this post we will discuss on query to get Cash Management (CE) information and related tables in Oracle Cloud Fusion Application, this query will fetch bank statement and reconciliation details in Oracle Fusion.
Important table of Cash Management (CE) in Oracle Cloud Fusion Application
CE_BANK_ACCOUNTS: This table contains Legal Entity Level bank account information or identifier of the payment profile selected from Oracle Fusion Payments’ table.
CE_BANK_ACCT_USES_ALL: This table contains Operating Unit level bank account use identifier of the payment profile selected from Oracle Fusion Payments’ table.
CE_STMT_BALANCES: This table contains information about statement balances.
CE_PAYMENT_DOCUMENTS: This table contains information related to payment documents.
CE_STATEMENT_HEADERS: This table contains the statement header information.
CE_STATEMENT_LINES: This table contains information about statement lines.
CE_ALL_BANK_BRANCHES_V: This is view is build with multiple bank tables, this view contains bank and bank branch related information.
Query to get Cash Management (CE) details in Oracle Cloud Fusion Application
select b.BANK_NAME
from CE_BANK_ACCOUNTS A ,
CE_BANKS_V B
WHERE a.bank_account_id IN (SELECT b.bank_account_id
FROM CE_BANK_ACCT_USES_ALL b
WHERE 1=1
AND (NVL(AP_USE_ENABLE_FLAG,'N' ) = 'Y' OR NVL(AR_USE_ENABLE_FLAG,'N' ) = 'Y' ))
and B.BANK_PARTY_ID =A.BANK_ID
ORDER BY a.bank_account_name;