CE Bank Statement Summary - draft

SELECT    abb.branch_party_id					C_BANK_ID,
	abb.bank_branch_name				C_BANK_BRANCH_NAME,
	abb.bank_name					C_BANK_NAME,
	aba.bank_account_id				C_BANK_ACCOUNT_ID,
	aba.bank_account_name				C_BANK_ACCOUNT,
	aba.currency_code					C_BANK_ACC_CURRENCY,
	aba.bank_account_num				C_BANK_ACC_NUM,
	sh.statement_header_id				C_STAT_HEADER_ID,
	sh.statement_number				C_STAT_NUMBER,
	sh.statement_date					C_STAT_DATE,
	sh.control_begin_balance				C_STAT_BEGIN_BAL,
	sh.doc_sequence_value				C_DOC_SEQUENCE	,
	NVL(sl.amount,0)					C_STAT_LINE_AMOUNT,
	sl.statement_line_id					C_STATEMENT_LINE_ID,
	sl.status						C_STAT_LINE_STATUS,
	DECODE(sl.status,'EXTERNAL',0,'UNRECONCILED',1,'RECONCILED',0,'ERROR',1,1)
							C_UNREC_INDICATOR,
                  DECODE(sl.trx_type,'DEBIT','PAYMENT',
		                  'MISC_DEBIT','PAYMENT',
			'STOP','RECEIPT',
		 	'CREDIT','RECEIPT',
	   		'MISC_CREDIT','RECEIPT',
	            		'NSF','PAYMENT',
			'REJECTED','PAYMENT',
			'SWEEP_IN','RECEIPT',
			'SWEEP_OUT','PAYMENT')		C_STAT_LINE_TYPE,
	sl.trx_type					C_STAT_LINE_TYPE_REAL,
                  DECODE(sl.trx_type,'DEBIT',NVL(sl.amount,0),
		                 'MISC_DEBIT',NVL(sl.amount,0),
		                 'NSF',NVL(sl.amount,0),
			'REJECTED',NVL(sl.amount,0),
			 'SWEEP_OUT',NVL(sl.amount,0),0)
				C_STAT_LINE_DEBIT_AMOUNT,
                  DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),
		                  'MISC_CREDIT',NVL(sl.amount,0),
		 	'STOP',NVL(sl.amount,0),
			 'SWEEP_IN',NVL(sl.amount,0),0)
				C_STAT_LINE_CREDIT_AMOUNT,
	CE_CEXSTMSR_XMLP_PKG.c_stat_end_balformula(sh.control_begin_balance, :C_STAT_DEBIT_AMOUNT, :C_STAT_CREDIT_AMOUNT) C_STAT_END_BAL,
	CE_CEXSTMSR_XMLP_PKG.c_net_movementformula(:C_STAT_END_BAL, sh.control_begin_balance) C_NET_MOVEMENT,
	CE_CEXSTMSR_XMLP_PKG.c_lines_unrec_amountformula(sl.status, DECODE ( sl.trx_type , 'DEBIT' , 'PAYMENT' , 'MISC_DEBIT' , 'PAYMENT' , 'STOP' , 'RECEIPT' , 'CREDIT' , 'RECEIPT' , 'MISC_CREDIT' , 'RECEIPT' , 'NSF' , 'PAYMENT' , 'REJECTED' , 'PAYMENT' , 'SWEEP_IN' , 'RECEIPT' , 'SWEEP_OUT' , 'PAYMENT' ), NVL ( sl.amount , 0 ), :C_LINES_SUM_CLEARED) C_LINES_UNREC_AMOUNT
FROM 	ce_bank_accts_gt_v					aba,
	ce_bank_branches_v					abb,
	ce_statement_lines 					sl,
	ce_statement_headers 				sh
WHERE     abb.branch_party_id 				= aba.bank_branch_id
AND	aba.bank_account_id 				= sh.bank_account_id
AND	sl.statement_header_id(+) 				= sh.statement_header_id
AND	sh.bank_account_id 				= NVL(:P_BANK_ACCOUNT_ID, sh.bank_account_id)
AND	DECODE(:P_BALANCES_BY, 'VALUE', NVL(sl.effective_date, NVL(:P_AS_OF_DATE, SYSDATE)),
	                		            'TRX', NVL(sl.trx_date, NVL(:P_AS_OF_DATE, SYSDATE)))	<=
	NVL(:P_AS_OF_DATE, DECODE(:P_BALANCES_BY, 'VALUE', NVL(sl.effective_date, SYSDATE),
					              'TRX', NVL(sl.trx_date, SYSDATE)))
AND 	&C_STAT_DATE_LEX
AND	&C_STAT_NUMBER_LEX
ORDER BY 2, 5, 10, 9

SELECT abb.branch_party_id C_BANK_ID, abb.bank_branch_name C_BANK_BRANCH_NAME, abb.bank_name C_BANK_NAME, aba.bank_account_id C_BANK_ACCOUNT_ID, aba.bank_account_name C_BANK_ACCOUNT, aba.currency_code C_BANK_ACC_CURRENCY, aba.bank_account_num C_BANK_ACC_NUM, sh.statement_header_id C_STAT_HEADER_ID, sh.statement_number C_STAT_NUMBER, sh.statement_date C_STAT_DATE, sh.control_begin_balance C_STAT_BEGIN_BAL, sh.doc_sequence_value C_DOC_SEQUENCE , NVL(sl.amount,0) C_STAT_LINE_AMOUNT, sl.statement_line_id C_STATEMENT_LINE_ID, sl.status C_STAT_LINE_STATUS, DECODE(sl.status,'EXTERNAL',0,'UNRECONCILED',1,'RECONCILED',0,'ERROR',1,1) C_UNREC_INDICATOR, DECODE(sl.trx_type,'DEBIT','PAYMENT', 'MISC_DEBIT','PAYMENT', 'STOP','RECEIPT', 'CREDIT','RECEIPT', 'MISC_CREDIT','RECEIPT', 'NSF','PAYMENT', 'REJECTED','PAYMENT', 'SWEEP_IN','RECEIPT', 'SWEEP_OUT','PAYMENT') C_STAT_LINE_TYPE, sl.trx_type C_STAT_LINE_TYPE_REAL, DECODE(sl.trx_type,'DEBIT',NVL(sl.amount,0), 'MISC_DEBIT',NVL(sl.amount,0), 'NSF',NVL(sl.amount,0), 'REJECTED',NVL(sl.amount,0), 'SWEEP_OUT',NVL(sl.amount,0),0) C_STAT_LINE_DEBIT_AMOUNT, DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0), 'MISC_CREDIT',NVL(sl.amount,0), 'STOP',NVL(sl.amount,0), 'SWEEP_IN',NVL(sl.amount,0),0) C_STAT_LINE_CREDIT_AMOUNT, CE_CEXSTMSR_XMLP_PKG.c_stat_end_balformula(sh.control_begin_balance, :C_STAT_DEBIT_AMOUNT, :C_STAT_CREDIT_AMOUNT) C_STAT_END_BAL, CE_CEXSTMSR_XMLP_PKG.c_net_movementformula(:C_STAT_END_BAL, sh.control_begin_balance) C_NET_MOVEMENT, CE_CEXSTMSR_XMLP_PKG.c_lines_unrec_amountformula(sl.status, DECODE ( sl.trx_type , 'DEBIT' , 'PAYMENT' , 'MISC_DEBIT' , 'PAYMENT' , 'STOP' , 'RECEIPT' , 'CREDIT' , 'RECEIPT' , 'MISC_CREDIT' , 'RECEIPT' , 'NSF' , 'PAYMENT' , 'REJECTED' , 'PAYMENT' , 'SWEEP_IN' , 'RECEIPT' , 'SWEEP_OUT' , 'PAYMENT' ), NVL ( sl.amount , 0 ), :C_LINES_SUM_CLEARED) C_LINES_UNREC_AMOUNT FROM ce_bank_accts_gt_v aba, ce_bank_branches_v abb, ce_statement_lines sl, ce_statement_headers sh WHERE abb.branch_party_id = aba.bank_branch_id AND aba.bank_account_id = sh.bank_account_id AND sl.statement_header_id(+) = sh.statement_header_id AND sh.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, sh.bank_account_id) AND DECODE(:P_BALANCES_BY, 'VALUE', NVL(sl.effective_date, NVL(:P_AS_OF_DATE, SYSDATE)), 'TRX', NVL(sl.trx_date, NVL(:P_AS_OF_DATE, SYSDATE))) <= NVL(:P_AS_OF_DATE, DECODE(:P_BALANCES_BY, 'VALUE', NVL(sl.effective_date, SYSDATE), 'TRX', NVL(sl.trx_date, SYSDATE))) AND &C_STAT_DATE_LEX AND &C_STAT_NUMBER_LEX ORDER BY 2, 5, 10, 9