CE Israel - Bank Statement Reconciliation
Description
Categories: BI Publisher
Application: Cash Management
Source: Israel - Bank Statement Reconciliation
Short Name: CEBNKSTMTRECON
DB package: CE_BNK_STMT_RECON_RPT_PKG
Source: Israel - Bank Statement Reconciliation
Short Name: CEBNKSTMTRECON
DB package: CE_BNK_STMT_RECON_RPT_PKG
Run
CE Israel - Bank Statement Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
-- This Query populates Detail Information of Unreconciled Bank and Book Transactions. -- First 3 sqls populate bank amount details while, the other 3 sqls populate book amount details -- Following SQL fetches bank amounts for unreconciled lines --> SELECT &gc_origin_bank_stmt ORIGIN , DECODE(csl.trx_type, 'CREDIT', &gc_trx_type_receipt, 'DEBIT', &gc_trx_type_payment, csl.trx_type) TRX_TYPE , csl.bank_trx_number DOCNUMBER , NVL(csl.effective_date,csl.trx_date) TRX_DATE , 0 BOOKS , NVL(SUM(DECODE(csl.trx_type, 'DEBIT', -1 * csl.amount, csl.amount)),0) BANK FROM ce_statement_headers csh , ce_statement_lines csl WHERE csl.statement_header_id= csh.statement_header_id AND csl.status = 'UNRECONCILED' AND csh.bank_account_id = :P_BANK_ACCOUNT_ID AND TRUNC(csl.trx_date) BETWEEN :gc_from_date AND :gc_to_date GROUP BY csl.trx_type , csl.bank_trx_number , csl.effective_date , csl.trx_date UNION ALL -- This SQL fetches bank amounts for lines that at the 'Period To' parameter time were -- unreconciled, but reconciled at report submission time SELECT &gc_origin_bank_stmt ORIGIN , DECODE(csl.trx_type, 'CREDIT', &gc_trx_type_receipt, 'DEBIT', &gc_trx_type_payment, csl.trx_type) TRX_TYPE , csl.bank_trx_number DOCNUMBER , NVL(csl.effective_date,csl.trx_date) TRX_DATE , 0 BOOKS , NVL(SUM(DECODE(csl.trx_type, 'DEBIT', -1 * csl.amount, csl.amount)),0) BANK FROM ce_statement_headers csh , ce_statement_reconciliations csr , ce_statement_lines csl WHERE csl.statement_header_id = csh.statement_header_id AND csl.status = 'RECONCILED' AND csh.bank_account_id= :P_BANK_ACCOUNT_ID AND TRUNC(csl.trx_date) BETWEEN :gc_from_date AND :gc_to_date AND csl.statement_line_id = csr.statement_line_id AND csr.current_record_flag = 'Y' AND csr.status_flag = 'M' --Changed the column created_date to effective_date AND TRUNC(csl.effective_date) > :gc_to_date GROUP BY csl.trx_type , csl.bank_trx_number , csl.effective_date , csl.trx_date UNION ALL -- If a bank statement line is partly reconciled, the line status is 'reconciled'. Still, this line -- should appear in this part (the unreconciled bank amount). This SQL fetches amounts for those lines. SELECT &gc_origin_bank_stmt ORIGIN , DECODE(csl.trx_type, 'CREDIT', &gc_trx_type_receipt, 'DEBIT', &gc_trx_type_payment, csl.trx_type) TRX_TYPE , csl.bank_trx_number DOCNUMBER , NVL(csl.effective_date,csl.trx_date) TRX_DATE , 0 BOOKS , NVL(SUM(DECODE(csl.trx_type, 'DEBIT', -1 * (csl.amount - csr.amount), (csl.amount - csr.amount))),0) BANK FROM ce_statement_headers csh , ce_statement_reconciliations csr , ce_statement_lines csl WHERE csl.statement_header_id = csh.statement_header_id AND csl.status ='RECONCILED' AND csh.bank_account_id = :P_BANK_ACCOUNT_ID AND TRUNC(csl.trx_date) BETWEEN :gc_from_date AND :gc_to_date AND csl.statement_line_id = csr.statement_line_id AND csl.amount > csr.amount --Changed the column created_date to effective_date AND TRUNC(csl.effective_date) BETWEEN :gc_from_date AND :gc_to_date AND csr.current_record_flag = 'Y' AND csr.status_flag = 'M' GROUP BY csl.trx_type , csl.bank_trx_number , csl.effective_date , csl.trx_date UNION ALL -- This SQL fetches book amounts for unreconciled lines SELECT &gc_origin_accounted ORIGIN , catv.type_meaning TRX_TYPE , catv.trx_number DOCNUMBER , catv.trx_date TRX_DATE , NVL(SUM(DECODE(catv.type_meaning, 'Debit', catv.bank_account_amount, 'Receipt', catv.bank_account_amount, -1 * catv.bank_account_amount)),0) BOOKS , 0 BANK FROM ce_available_transactions_v catv WHERE application_id <> 260 AND catv.bank_account_id = :P_BANK_ACCOUNT_ID AND catv.trx_date BETWEEN :gc_from_date AND :gc_to_date --Added the Currency Condition AND catv.currency_code = :gc_currency_code GROUP BY catv.type_meaning ,catv.trx_number ,catv.trx_date UNION ALL -- This SQL fetches book amounts for lines that at the 'Period To' parameter time were -- unreconciled, but reconciled at report submission time --> SELECT &gc_origin_accounted ORIGIN , crtv.type_meaning TRX_TYPE , crtv.trx_number DOCNUMBER , crtv.trx_date TRX_DATE , NVL(SUM(DECODE(crtv.type_meaning, 'Debit', crtv.bank_account_amount, 'Receipt', crtv.bank_account_amount, -1 * crtv.bank_account_amount)),0) BOOKS , 0 BANK FROM ce_reconciled_transactions_v crtv WHERE crtv.bank_account_id = :P_BANK_ACCOUNT_ID AND crtv.trx_date BETWEEN :gc_from_date AND :gc_to_date AND TRUNC(crtv.cleared_date) > :gc_to_date --Added the Currency Condition AND crtv.currency_code = :gc_currency_code --Commented the Status condition is false --AND crtv.status = 'RECONCILED' GROUP BY crtv.type_meaning ,crtv.trx_number ,crtv.trx_date UNION ALL -- If a line is partly reconciled, the line status is 'reconciled'. Still, this line should appear -- in this part (the unreconciled amount). This SQL fetches book amounts for those lines. SELECT &gc_origin_accounted ORIGIN , crtv.type_meaning TRX_TYPE , crtv.trx_number DOCNUMBER , crtv.trx_date TRX_DATE , NVL(SUM(DECODE(crtv.type_meaning, 'Debit', (crtv.bank_account_amount - crtv.actual_amount_cleared), 'Receipt', (crtv.bank_account_amount - crtv.actual_amount_cleared), -1 * (crtv.bank_account_amount - crtv.actual_amount_cleared))),0) BOOKS , 0 BANK FROM ce_reconciled_transactions_v crtv WHERE crtv.bank_account_id = :P_BANK_ACCOUNT_ID AND crtv.trx_date BETWEEN :gc_from_date AND :gc_to_date AND crtv.bank_account_amount > crtv.actual_amount_cleared --Added the Currency Condition AND crtv.currency_code = :gc_currency_code --Commented the Status condition is false --AND crtv.status = 'RECONCILED' GROUP BY crtv.type_meaning ,crtv.trx_number ,crtv.trx_date ORDER BY 3, 4 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Bank Account Name |
|
LOV Oracle | |
From Accounting Period |
|
LOV Oracle | |
To Accounting Period |
|
LOV Oracle | |
Closing Balance |
|
Number |