CE Bank Statement Summary - draft
Description
Categories: BI Publisher
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
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 | |
---|---|---|---|
Bank Account Number |
|
LOV Oracle | |
Statement Number From |
|
LOV Oracle | |
Statement Number To |
|
LOV Oracle | |
Statement Date From |
|
Date | |
Statement Date To |
|
Date | |
Balances by |
|
LOV Oracle | |
As of Date |
|
Date |