CE Bank Statement Summary - draft

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: Bank Statement Summary Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXSTMSR_XML
DB package: CE_CEXSTMSR_XMLP_PKG
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
Parameter Name SQL text Validation
As of Date
 
Date
Balances by
 
LOV Oracle
Statement Date To
 
Date
Statement Date From
 
Date
Statement Number To
 
LOV Oracle
Statement Number From
 
LOV Oracle
Bank Account Number
 
LOV Oracle