-- 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 |
