CE Bank Statement Cashflow Creation Execution - draft

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