Cash Management SQL

Cash Management SQLs:

CE_BANK_ACCOUNTS
CE_BANK_BRANCHES_V
CE_STATEMENT_HEADERS
CE_STATEMENT_LINES
CE_STML_BALANCES
========================

Buliding SQL for Cash Management Report

Bank statements sent are uploaded to ce_statement_headers

1.  ce_statement_headers
-> ce_statement_headers.bank_account_id     = ce_bank_accounts.bank_account_id

2. ce_bank_accounts
-> joins with account_owner_org_id:
[CE_BANK_ACCOUNTS || XLE_ENTITY_PROFILES ->ACCOUNT_OWNER_ORG_ID]

3. XLE_ENTITY_PROFILES  ->
-> XLE_ENTITY_PROFILES.legal_entity_id = CE_BANK_ACCOUNTS.ACCOUNT_OWNER_ORG_ID

4. SELECT CSH.*
   FROM
   CE_STATEMENT_HEADERS CSH,
   CE_BANK_ACCOUNTS CBA,
   XLE_ENTITY_PROFILES XEP
   WHERE CSH.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
     AND XEP.LEGAL_ENTITY_ID = CBA.ACCOUNT_OWNER_ORG_ID 


5. CE_LOOKUPS

6. CE_STML_BALANCES

This Table contains statement open booked and close booked balances based on bank_account_id and balance date.
    BANK_ACCOUNT_ID
    STATEMENT_HEADER_ID
    BALANCE_CODE
    BALANCE_AMOUNT
    BALANCE_DATE

===================================
7. How to get Bank Balance using GL

1. CE_STATEMENT_HEADERS -> Get Bank Account ID
2. CE_BANK_ACCOUNTS -> GET ASSET_CODE_COMBINTAION_ID
3. GL_BALANCES -> CODE_COMBINATION_ID
4. BASED ON PERIOD -> GET BALANCES
sum(glb.begin_balance_dr - glb.begin_balance_cr),0) OPENGLBAL

====================================

8. How to get Open and Close Balance for a Statement
1. CE_STATEMENT_HEADERS -> GET STATEMENT_HEADER_ID
2. CE_STML_BALANCES

===================================
9. Mapping of Transactions from Cash Management to AP/AR

Lets Say we have a record in 

1. query CE_STATEMENT_HEADERS -> 
2. query CE_STATEMENT_LINES -> get recon_history_id
3. query CE_RECON_HISTORY_ITEMS -> provide recon_history_id, and get source id -> sourec_id = check_id (ap), and sourec_id = cash_receipt_id (AR)