CE Israel - Bank Statement Reconciliation

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