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