CE Cash Leveling Execution - draft

select xba1.bank_account_num sub_account,
           xba1.currency_code C_CURRENCY_CODE,
           level_mesg.transfer_amount Transfer_Amount,
           xba2.bank_account_num Conc_Account,
           level_mesg.deal_type deal_type,
           level_mesg.deal_no deal_number,
           level_mesg.trx_number  trx_number,
           level_mesg.offset_deal_no  offset_deal_number,
           level_mesg.offset_trx_number  offset_trx_number,
          null 	subtype_name
From
           ce_leveling_messages level_mesg,
           ce_bank_accts_gt_v xba1,
           ce_bank_accounts  xba2
Where
           level_mesg.sub_account_id = xba1.bank_account_id
and    level_mesg.conc_account_id = xba2.bank_account_id
and   :CP_PROFILE = 'XTR'
and    level_mesg.success_flag = 'Y'
and    level_mesg.request_id = :p_request_id
union
select xba1.bank_account_num sub_account,
           cpo.currency_code C_CURRENCY_CODE,
           level_mesg.transfer_amount Transfer_Amount,
           xba2.bank_account_num Conc_Account,
           level_mesg.deal_type deal_type,
           level_mesg.deal_no deal_number,
           level_mesg.trx_number  trx_number,
           level_mesg.offset_deal_no  offset_deal_number,
           level_mesg.offset_trx_number  offset_trx_number,
          cty.transaction_sub_type_name subtype_name
From
           ce_leveling_messages level_mesg,
           ce_bank_accts_gt_v xba1,
           ce_bank_accounts  xba2,
           ce_cashpools cpo,
           ce_trxns_subtype_codes cty
Where
           level_mesg.sub_account_id = xba1.bank_account_id
and    level_mesg.conc_account_id = xba2.bank_account_id
and    :CP_PROFILE = 'CE'
and    level_mesg.success_flag = 'Y'
and    level_mesg.message_text is null
and    cpo.cashpool_id = level_mesg.cashpool_id
and    cpo.trxn_subtype_code_id = cty.trxn_subtype_code_id(+)
and    level_mesg.request_id = :p_request_id