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)

How To Link AR Receipt With Bank Statement

SELECT CASH_RECEIPT_ID FROM FUSION.AR_CASH_RECEIPTS_ALL WHERE RECEIPT_NUMBER = '&RECEIPT_NUMBER' AND RECON_FLAG = Y; SELECT RECON_HISTORY_ID FROM FUSION.CE_RECON_HISTORY_ITEMS WHERE RECON_SOURCE = 'AR' AND SOURCE_ID = '&CASH_RECEIPT_ID'; SELECT STATEMENT_HEADER_ID FROM FUSION.CE_STATEMENT_LINES WHERE RECON_HISTORY_ID = '&RECON_HISTORY_ID'; SELECT STATEMENT_NUMBER FROM FUSION.CE_STATEMENT_HEADERS WHERE STATEMENT_HEADER_ID = '&STATEMENT_HEADER_ID';  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)

How to get Active bank accounts for a customer, Supplier

-----
Case: You have account number

hz_cust_accounts  (pass account_number = '<1234>') (get CUST_ACCOUNT_ID, PARTY_ID)
HZ_CUST_ACCT_SITES_ALL  (cust_account_id=hz_cust_accounts.cust_account_id) (get cust_account_site_id)
HZ_CUST_SITE_USES_ALL   (pass CUST_ACCT_SITE_ID) (get SITE_USE_ID -> which are active)

IBY_EXTERNAL_PAYERS_ALL (pass party_id= hz_cust_accounts.party_id, ACCT_SITE_USE_ID-> join with HZ_CUST_SITE_USES_ALL.SITE_USE_ID) (get EXT_PAYER_ID)
IBY_PMT_INSTR_USES_ALL  (pass EXT_PMT_PARTY_ID  = IBY_EXTERNAL_PAYERS_ALL.EXT_PAYER_ID)
IBY_EXT_BANK_ACCOUNTS   (get EXT_BANK_ACCOUNT_ID)
---
HZ_ACCOUNT_OWNERS (pass account_owner_party_id= hz_cust_accounts.party_id) (get EXT_BANK_ACCOUNT_ID)


SELECT ca.account_number      "Customer Account Number"
      , null                  "Site address"
      , piu.start_date        "Bank Account Use Start Date"
      , piu.end_date          "Bank Account Use End Date"
      , eba.bank_account_num  "Bank Account Number"
      , eba.bank_account_name "Bank Account Name"
      , eba.start_date        "Bank Account Start Date"
      , eba.end_date          "Bank Account End Date"
  FROM hz_cust_accounts        ca
     , iby_external_payers_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE ca.cust_account_id      = epa.cust_account_id
   AND piu.ext_pmt_party_id    = epa.ext_payer_id
   AND piu.payment_function    = 'CUSTOMER_PAYMENT'
   AND eba.ext_bank_account_id = piu.instrument_id
   AND ca.account_number       = 'SKM-PERSON-01'
   AND epa.acct_site_use_id    is null



-----------------

(
                SELECT DISTINCT
                    bank.party_name bank_name,
                    branch.party_name branch_name,
                    account.bank_account_num bank_account_num,
                    account.iban iban,
                    account.eft_swift_code swift_code,
                    acc_owner.account_owner_party_id account_owner_party_id,
                    account.ext_bank_account_id
                FROM
                    hz_parties bank,
                    hz_relationships rel,
                    hz_parties branch,
                    iby_ext_bank_accounts account,
                    iby_account_owners acc_owner
                WHERE
                    1 = 1
                    AND bank.party_id = rel.object_id
                    AND bank.party_type = rel.object_type
                    AND rel.object_table_name = 'HZ_PARTIES'
                    AND rel.relationship_code = 'BRANCH_OF'
                    AND rel.subject_id = branch.party_id
                    AND rel.subject_type = branch.party_type
                    AND rel.subject_table_name = 'HZ_PARTIES'
                    AND bank.party_id = account.bank_id
                    AND branch.party_id = account.branch_id (+)
                    AND account.ext_bank_account_id = acc_owner.ext_bank_account_id (+)
                    AND SYSDATE BETWEEN nvl(rel.start_date,SYSDATE - 1) AND nvl(rel.end_date,SYSDATE + 1)
                    AND bank.status = 'A'
                    AND branch.status = 'A'
            ) supplier_bank