CE Bank Statement Cashflow Creation Execution - draft
Description
Categories: BI Publisher
Application: Cash Management
Source: Bank Statement Cashflow Creation Execution Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEJEEXER_XML
DB package: CE_CEJEEXER_XMLP_PKG
Source: Bank Statement Cashflow Creation Execution Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEJEEXER_XML
DB package: CE_CEJEEXER_XMLP_PKG
Run
CE Bank Statement Cashflow Creation Execution - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT 'A' break_group, bb.bank_name bank_name, ba.bank_account_name bank_account_name, ba.currency_code ba_currency_code, sh.statement_number statement_number, to_char(sh.statement_date,'DD-MON-RRRR') statement_date, sl.statement_line_id statement_line_id, sl.line_number line_number, l1.meaning trx_type, sl.trx_date transaction_date, nvl(sh.currency_code,ba.currency_code) currency_code, sl.amount amount, e.message_name error, null trxn_subtype, null reference, null cashflow_number, null cashflow_currency, null cashflow_amount, CE_CEJEEXER_XMLP_PKG.c_message_dspformula(e.message_name) C_MESSAGE_DSP FROM ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_statement_headers sh, ce_statement_lines sl, ce_je_messages e, ce_lookups l1 WHERE e.request_id = :P_REQUEST_ID and sh.bank_account_id = ba.bank_account_id and ba.bank_branch_id = bb.branch_party_id and sh.statement_header_id = sl.statement_header_id and sl.statement_line_id = e.statement_line_id and sl.cashflow_id is null and sh.statement_header_id = e.statement_header_id and l1.lookup_type = 'BANK_TRX_TYPE' and l1.lookup_code = sl.trx_type UNION ALL SELECT 'B' break_group, bb.bank_name bank_name, ba.bank_account_name bank_account_name, ba.currency_code ba_currency_code, sh.statement_number statement_number, to_char(sh.statement_date,'DD-MON-RRRR') statement_date, sl.statement_line_id statement_line_id, sl.line_number line_number, l1.meaning trx_type, sl.trx_date transaction_date, nvl(sh.currency_code,ba.currency_code) currency_code, sl.amount amount, null error, trxn.transaction_sub_type_name trxn_subtype, ca.reference_text reference, ca.cashflow_id cashflow_number, nvl(ca.cashflow_currency_code,ba.currency_code) cashflow_currency, ca.cashflow_amount cashflow_amount, CE_CEJEEXER_XMLP_PKG.c_message_dspformula(null) C_MESSAGE_DSP FROM ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_statement_headers sh, ce_statement_lines sl, ce_lookups l1, ce_cashflows ca, ce_je_messages e, ce_trxns_subtype_codes trxn WHERE bb.branch_party_id = :P_BANK_BRANCH_ID and ba.bank_branch_id = bb.branch_party_id and ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,ba.bank_account_id) and sh.bank_account_id = ba.bank_account_id and ba.account_classification = 'INTERNAL' and trunc(sh.statement_date) >= nvl(:P_STAT_DATE_FROM,trunc(sh.statement_date)) and trunc(sh.statement_date) <= nvl(:P_STAT_DATE_TO,trunc(sh.statement_date)) and sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) and sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) and sl.statement_header_id = sh.statement_header_id and sl.statement_line_id = e.statement_line_id and sh.statement_header_id = e.statement_header_id and sl.cashflow_id = ca.cashflow_id and ca.trxn_reference_number is null and sl.status <> 'EXTERNAL' and sl.statement_line_id = ca.statement_line_id and ca.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+) and l1.lookup_type = 'BANK_TRX_TYPE' and l1.lookup_code = sl.trx_type UNION ALL SELECT 'C' break_group, bb.bank_name bank_name, ba.bank_account_name bank_account_name, ba.currency_code ba_currency_code, sh.statement_number statement_number, to_char(sh.statement_date,'DD-MON-RRRR') statement_date, sl.statement_line_id statement_line_id, sl.line_number line_number, l1.meaning trx_type, sl.trx_date transaction_date, nvl(sh.currency_code,ba.currency_code) currency_code, sl.amount amount, null error, trxn.transaction_sub_type_name trxn_subtype, ca.reference_text reference, ca.cashflow_id cashflow_number, nvl(ca.cashflow_currency_code,ba.currency_code) cashflow_currency, ca.cashflow_amount cashflow_amount, CE_CEJEEXER_XMLP_PKG.c_message_dspformula(null) C_MESSAGE_DSP FROM ce_bank_accts_gt_v ba, ce_bank_branches_v bb, ce_statement_headers sh, ce_statement_lines sl, ce_lookups l1, ce_cashflows ca, ce_trxns_subtype_codes trxn WHERE bb.branch_party_id = :P_BANK_BRANCH_ID and ba.bank_branch_id = bb.branch_party_id and ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,ba.bank_account_id) and sh.bank_account_id = ba.bank_account_id and ba.account_classification = 'INTERNAL' and trunc(sh.statement_date) >= nvl(:P_STAT_DATE_FROM,trunc(sh.statement_date)) and trunc(sh.statement_date) <= nvl(:P_STAT_DATE_TO,trunc(sh.statement_date)) and sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number) and sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) and sl.statement_header_id = sh.statement_header_id and sl.cashflow_id = ca.cashflow_id and ca.trxn_reference_number is null and sl.statement_line_id not in (select statement_line_id from ce_je_messages) and sl.status <> 'EXTERNAL' and sl.statement_line_id = ca.statement_line_id and ca.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+) and l1.lookup_type = 'BANK_TRX_TYPE' and l1.lookup_code = sl.trx_type ORDER BY statement_number, break_group, statement_line_id |