Cash Management Statements

-- ##############################################################################
-- #      CASH MANAGEMENT STATEMENTS - DETAILS
-- ############################################################################*/

    SELECT csh.statement_header_id
	     , csh.statement_number
	     , TO_CHAR(csh.statement_date, 'DD-MON-YYYY') statement_date
         , csl.status
         , csl.BANK_TRX_NUMBER
         , csl.statement_line_id
	     , csl.statement_header_id
	     , csl.line_number
	     , csl.trx_date
	     , csl.trx_type
         , csl.TRX_CODE
	     , csl.amount
         , csh.bank_account_id
         , csra.statement_line_id reconciled
         , cba.BANK_ACCOUNT_NAME
         , cba.BANK_ACCOUNT_NUM
      FROM ce.CE_STATEMENT_HEADERS csh
      JOIN ce.CE_STATEMENT_LINES csl ON csh.statement_header_id = csl.statement_header_id
 LEFT JOIN CE.CE_STATEMENT_RECONCILS_ALL csra ON csra.statement_line_id = csl.statement_line_id
 LEFT JOIN ce_bank_accounts cba on cba.BANK_ACCOUNT_ID = csh.BANK_ACCOUNT_ID
     WHERE 1 = 1
       AND csl.creation_date > ''
       AND csl.BANK_TRX_NUMBER LIKE 'TRANSFER%'
       AND csl.STATEMENT_LINE_ID = 3335000
       AND csl.status = 'UNRECONCILED'
       AND csl.trx_type = 'CREDIT'
       AND csh.statement_number = '12345'
       AND csl.TRX_CODE = 'TRFD'
       AND csl.TRX_DATE = '24-SEP-2017'
       AND csl.AMOUNT = 3.73
       AND csl.STATEMENT_LINE_ID = 3335000
       AND csl.STATEMENT_LINE_ID IN (3317187,3317003,3317008)
       AND csh.bank_account_id = 1234
  ORDER BY csh.creation_date DESC
         , csl.line_number;
         
-- ##############################################################################
-- #      CASH MANAGEMENT STATEMENTS - SUMMARY
-- ############################################################################*/
         
     select csh.statement_header_id
          , csh.statement_number
          , csh.statement_date
          , csh.creation_date 
          , cba.BANK_ACCOUNT_NAME
          , cba.BANK_ACCOUNT_NUM
          , csl.status
          , count(*) lines
          , sum(csl.amount) amt
       from ce.CE_STATEMENT_HEADERS csh
       JOIN ce.CE_STATEMENT_LINES csl ON csh.statement_header_id = csl.statement_header_id
  LEFT JOIN CE.CE_STATEMENT_RECONCILS_ALL csra ON csra.statement_line_id = csl.statement_line_id
  LEFT JOIN ce_bank_accounts cba on cba.BANK_ACCOUNT_ID = csh.BANK_ACCOUNT_ID
       where 1 = 1
        and csl.status = 'UNRECONCILED'
        and csh.creation_date > '16-JAN-2018'
        and cba.BANK_ACCOUNT_NAME like 'Test%Bank%'
   GROUP BY csh.statement_header_id
          , csh.statement_number
          , csh.statement_date
          , cba.BANK_ACCOUNT_NAME
          , cba.BANK_ACCOUNT_NUM
          , csl.status
          , csh.creation_date 
   ORDER BY csh.statement_date DESC;

-- ##############################################################################
-- #      CASH MANAGEMENT INTERFACE
-- ############################################################################*/

select * from CE_STATEMENT_HEADERS_INT where creation_date > '16-JAN-2018' order by creation_date desc;
select * from CE_STATEMENT_LINES_INTERFACE where creation_date > '16-JAN-2018' order by creation_date desc;

-- ##############################################################################
-- #      CASH MANAGEMENT INTERFACE ERRORS
-- ############################################################################*/

select * from CE_HEADER_INTERFACE_ERRORS where creation_date > '16-JAN-2018' order by creation_date desc;

select * from CE_LINE_INTERFACE_ERRORS where creation_date > '16-JAN-2018' order by creation_date desc;

          select to_char(creation_date, 'YYYY-MM-DD')
               , count(*) ct 
            from CE_HEADER_INTERFACE_ERRORS 
           where creation_date > '01-JAN-2018' 
        group by to_char(creation_date, 'YYYY-MM-DD')
        order by to_char(creation_date, 'YYYY-MM-DD') desc;

-- ##############################################################################
-- #      RECONCILIATIONS
-- ############################################################################*/

select * from CE_RECONCILIATION_ERRORS;
select * from ce.CE_STATEMENT_RECONCILS_ALL where STATEMENT_LINE_ID = 2984204;

-- ##############################################################################
-- #      STATEMENT MAPPING
-- ############################################################################*/

-- Stores the definitions of the mapping templates
select * from CE_BANK_STMT_INT_MAP;

-- Maps the columns the Bank Statement Headers Interface table (CE_STATEMENT_HEADERS_INT_ALL) to the columns in the intermediate table (CE_STMT_INT_TMP)
select * from CE_BANK_STMT_MAP_HDR;

-- Maps the columns in the Bank Statement Lines Interface table (CE_STATEMENT_LINES_INTERFACE) to the columns in the intermediate table (CE_STMT_INT_TMP)
select * from CE_BANK_STMT_MAP_LINE;

-- This table stores pre-determined codes between you and your bank to identify the types of transactions for matching statement lines
select * from CE_TRANSACTION_CODES;

-- Intermediate table, which stores the information loaded from a bank statement file. This table is populated by the SQL*Loader script
select * from CE_STMT_INT_TMP;

-- Records the errors encountered by the Bank Statement Loader program when loading data from the bank statement file into the intermediate table
select * from CE_SQLLDR_ERRORS;

Cash Management Internal Bank Accounts

Sections:

  1. INTERNAL BANK ACCOUNTS
  2. INTERNAL BANK ACCOUNTS – BASIC
  3. BANKS AND BRANCHES

INTERNAL BANK ACCOUNTS

-- ##############################################################################
--      INTERNAL BANK ACCOUNTS
-- ############################################################################*/

            select hou.name "operating unit"      
                 , hp.party_name "legal entity"
                 , cba.bank_account_num "account_number"
                 , cba.bank_account_name
                 , cba.iban_number
                 , cba.bank_account_name_alt
                 , cba.short_account_name
                 , cba.ap_use_allowed_flag use_ap
                 , cba.ar_use_allowed_flag use_ar
                 , cba.currency_code
                 , cba.multi_currency_allowed_flag
                 , cbbv.bank_name bank
                 , cbbv.bank_branch_name branch
                 , cba.creation_date
                 , fu_cr.user_name || ' (' || fu_cr.description || ')' created_by
                 , cba.last_update_date
                 , fu_up.user_name || ' (' || fu_up.description || ')' updated_by
                 , gcck1.concatenated_segments "cash account"
                 , gcck2.concatenated_segments "bank_charges_account"
                 , gcck3.concatenated_segments "foreign_exchange_charges"
              from ce_bank_accounts cba
              join ce_bank_acct_uses_all cbau on cba.bank_account_id = cbau.bank_account_id
              join ce_banks_v cbv on cba.bank_id = cbv.bank_party_id
              join ce_gl_accounts_ccid cgac on  cgac.bank_acct_use_id = cbau.bank_acct_use_id
              join ce_bank_branches_v cbbv on cba.bank_id = cbbv.bank_party_id and cbbv.branch_party_id = cba.bank_branch_id
              join hr_operating_units hou on cbau.org_id = hou.organization_id
              join hz_parties hp on hp.party_id = cba.account_owner_party_id
              join fnd_user fu_cr on cba.created_by = fu_cr.user_id
              join fnd_user fu_up on cba.last_updated_by = fu_up.user_id
         left join gl_code_combinations_kfv gcck1 on gcck1.code_combination_id = cgac.ap_asset_ccid
         left join gl_code_combinations_kfv gcck2 on gcck2.code_combination_id = cgac.bank_charges_ccid
         left join gl_code_combinations_kfv gcck3 on gcck3.code_combination_id = cba.fx_charge_ccid
         left join gl_code_combinations_kfv gcck4 on gcck4.code_combination_id = cgac.cash_clearing_ccid
         left join gl_code_combinations_kfv gcck5 on gcck5.code_combination_id = cgac.bank_errors_ccid
         left join gl_code_combinations_kfv gcck6 on gcck6.code_combination_id = cgac.future_dated_payment_ccid
         left join gl_code_combinations_kfv gcck7 on gcck7.code_combination_id = cgac.on_account_ccid
         left join gl_code_combinations_kfv gcck8 on gcck8.code_combination_id = cgac.unapplied_ccid
         left join gl_code_combinations_kfv gcck9 on gcck9.code_combination_id = cgac.unidentified_ccid
         left join gl_code_combinations_kfv gcck10 on gcck10.code_combination_id = cgac.asset_code_combination_id
         left join gl_code_combinations_kfv gcck11 on gcck11.code_combination_id = cgac.remittance_ccid
             where 1 = 1
               and cba.bank_account_num in ('00000000')
--               and cba.bank_account_id in (10020,15029)
               and 1 = 1;

INTERNAL BANK ACCOUNTS – BASIC

-- ##############################################################################
--      INTERNAL BANK ACCOUNTS - BASIC
-- ##############################################################################

          SELECT cba.bank_account_name
               , cba.bank_account_num
               , cbv.bank_name
               , cbbv.branch_number
               , cbbv.bank_branch_name
            FROM ce_bank_accounts cba
            join ce_banks_v cbv on cba.bank_id = cbv.bank_party_id
            join ce_bank_branches_v cbbv on cbbv.branch_party_id = cba.bank_branch_id
           WHERE 1 = 1
        ORDER BY 3 DESC;

-- https://community.oracle.com/message/12359189#12359189

     select cba.bank_account_id
          , cba.bank_account_name
          , cbau.bank_acct_use_id
          , cbau.org_id 
       from ce_bank_accounts cba
          , ce_bank_acct_uses_all cbau
      where cba.bank_account_id = cbau.bank_account_id
--        and cbau.bank_acct_use_id = < ce_bank_acct_use_id of the ap_checks_all >
        and 1 = 1
        and cba.bank_account_id IN (10020,15029)
   order by 1 ;

BANKS AND BRANCHES

-- ##############################################################################
--      BANKS AND BRANCHES
-- ##############################################################################

          SELECT cbv.bank_name
               , cbbv.branch_number
               , cbbv.bank_branch_name
            FROM ce_banks_v cbv
            join ce_bank_branches_v cbbv on cbv.bank_party_id = cbbv.bank_party_id
           WHERE 1 = 1
        ORDER BY 2;