CE Bank Statement Detail - draft

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: Bank Statement Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXSTMRR_XML
DB package: CE_CEXSTMRR_XMLP_PKG
SELECT DECODE(sl.status,'EXTERNAL','RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ) C_STAT_TYPE,
DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_char(sl.line_number),sh.statement_number||'/'||sl.line_number) C_SORT,
sh.statement_header_id C_STAT_HEADER_ID,
sl.statement_line_id C_STAT_LINE_ID,
sh.doc_sequence_value C_STAT_DOC_SEQ,
aba.bank_account_id C_BANK_ACCOUNT_ID,
l1.meaning C_SL_TYPE_DSP,
sl.trx_type C_SL_TYPE,
aba.bank_account_name C_BANK_ACCOUNT_NAME,
aba.bank_account_num C_BANK_ACCOUNT_NUM,
aba.currency_code C_BANK_ACC_CURRENCY,
abb.bank_branch_name C_BANK_BRANCH_NAME,
abb.bank_name C_BANK_NAME,
DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_number(to_char(sh.statement_date,'J')),aba.bank_account_id)
C_ORDERING,
DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',sl.line_number,to_number(to_char(sh.statement_date,'J')))
C_ORDER_BY,
DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',sh.statement_number,aba.bank_account_num) C_STAT_NUMBER,
sl.line_number C_SL_NUMBER,
DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_char(sl.trx_date,:C_DATEFORMAT),sh.statement_number) C_SL_DATE,
NVL(sl.amount,0) C_SL_AMOUNT,
DECODE(sl.trx_type,'DEBIT',NVL(-sl.amount,0),'CREDIT',NVL(sl.amount,0),'MISC_DEBIT',NVL(-sl.amount,0),'MISC_CREDIT',NVL(sl.amount,0),'NSF',NVL(-sl.amount,0),'STOP',NVL(sl.amount,0),'REJECTED',NVL(-sl.amount,0),'SWEEP_IN',NVL(sl.amount,0),'SWEEP_OUT',NVL(-sl.amount,0),0) C_SL_SIGNED_AMOUNT,
DECODE(sl.trx_type,'DEBIT',NVL(sl.amount,0),0) C_SL_DR,
DECODE(sl.trx_type,'MISC_DEBIT',NVL(sl.amount,0),0) C_SL_MISC_DR,
DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),0) C_SL_CR,
DECODE(sl.trx_type,'MISC_CREDIT',NVL(sl.amount,0),0) C_SL_MISC_CR,
DECODE(sl.trx_type,'NSF',NVL(sl.amount,0),0) C_SL_NSF,
DECODE(sl.trx_type,'STOP',NVL(sl.amount,0),0) C_SL_STOP,
DECODE(sl.trx_type,'REJECTED',NVL(sl.amount,0),0) C_SL_REJECTED,
DECODE(sl.trx_type,'SWEEP_IN',NVL(sl.amount,0),0) C_SL_SWP_IN,
DECODE(sl.trx_type,'SWEEP_OUT',NVL(sl.amount,0),0) C_SL_SWP_OUT,
sl.status C_STAT_LINE_STATUS,
l2.meaning C_STAT_LINE_STATUS_DSP,
DECODE(sl.status,'ERROR',
DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),
'MISC_CREDIT',NVL(sl.amount,0),
'SWEEP_IN',NVL(sl.amount,0),0),0) C_SL_CR_ERRORS,
DECODE(sl.status,'ERROR',
DECODE(sl.trx_type,'DEBIT',sl.amount,
'MISC_DEBIT',NVL(sl.amount,0),
'SWEEP_OUT',NVL(sl.amount,0),0),0) C_SL_DR_ERRORS,
sl.bank_trx_number C_SL_TRX_NUMBER,
sl.charges_amount C_SL_BANK_CHARGES,
sh.control_end_balance C_STAT_CONTROL_END,
sh.statement_date C_STAT_STATEMENT_DATE,
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) C_SL_REC_AMOUNT,
(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ) C_SL_UNREC_AMOUNT,
CE_CEXSTMRR_XMLP_PKG.c_stat_sum_rec_lineformula(:C_STAT_SUM_LINE_AMOUNT,:C_STAT_UNREC_SUM_LINE) C_STAT_SUM_REC_LINE,
CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_signed_amountformul(DECODE(sl.trx_type,'DEBIT',NVL(-sl.amount,0),'CREDIT',NVL(sl.amount,0),'MISC_DEBIT',NVL(-sl.amount,0),'MISC_CREDIT',NVL(sl.amount,0),'NSF',NVL(-sl.amount,0),'STOP',NVL(sl.amount,0),'REJECTED',NVL(-sl.amount,0),'SWEEP_IN',NVL(sl.amount,0),'SWEEP_OUT',NVL(-sl.amount,0),0),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_UNREC_SIGNED_AMOUNT,
CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_pos_amountformula(sl.status,NVL(sl.amount,0),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_POS_UNREC_AMOUNT,
CE_CEXSTMRR_XMLP_PKG.c_sl_swp_in2formula((DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),DECODE(sl.trx_type,'SWEEP_IN',NVL(sl.amount,0),0)) C_SL_SWP_IN2,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_drformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_DR,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_dr_miscformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_DR_MISC,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_crformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_CR,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_cr_miscformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_CR_MISC,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_nsfformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_NSF,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_rejectedformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_REJECTED,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_swp_outformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_SWP_OUT,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_swp_informula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_SWP_IN,
CE_CEXSTMRR_XMLP_PKG.c_sl_rec_stopformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) ),sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0))) C_SL_REC_STOP,
CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_drformula(sl.trx_type,DECODE(sl.status,'EXTERNAL','RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) )) C_SL_UNREC_DR,
CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_dr_miscformula(sl.trx_type,DECODE(sl.status,'EXTERNAL','RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) )) C_SL_UNREC_DR_MISC,
CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_crformula(sl.trx_type,DECODE(sl.status,'EXTERNAL','RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl(decode (sl.trx_type,'MISC_CREDIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),
'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0)),
'RECEIPT' ,DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(- sr.amount,0),nvl(sr.amount,0 ) ) ,
'CREDIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'PAYMENT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'MISC_DEBIT',DECODE(decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT',nvl(-sr.amount,0),nvl(sr.amount,0 ) ) ,
'PAYMENT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount ,0 ) ) ,
'DEBIT' ,DECODE (decode(sr.reference_type ,'DM REVERSAL','RECEIPT',sr.reference_type),'RECEIPT' ,nvl(-sr.amount,0),nvl(sr.amount,0) ) ,
nvl (sr.amount ,0 ) ),0)) )) C_SL_UNREC_CR,
CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_swp_informula(sl.trx_type,DECODE(sl.status,'EXTERNAL','RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status ,'EXTERNAL',0,NVL(sl.amount ,0)) -
sum(nvl