select 'B' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
sl.line_number line_no,
sh.statement_Date statement_date,
sl.trx_date trx_date,
ba.bank_account_num bank_acc,
ba.bank_Account_name bank_Account_name,
ba.currency_code acc_currency,
null message_name,
nvl(sl.amount,0) amount,
l2.meaning trx_type,
nvl(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
sl.bank_Account_text bank_Account_text,
e.deal_type deal_type,
e.deal_num deal_num,
e.transaction_num transaction_num,
offset_deal_num offset_deal_num,
1 count,
nvl(sl.currency_code,ba.currency_code) C_CURRENCY_CODE,
offset_transaction_num offset_transaction_num,
null counter_name,
null subtype_name,
null status,
null cashflows_create,
CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
from
ce_lookups l2,
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_Statement_headers sh,
ce_statement_lines sl,
ce_zba_Deal_messages e
where
l2.lookup_type = 'BANK_TRX_TYPE'
AND l2.lookup_code = sl.trx_type
AND :c_profile = 'XTR'
AND bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND ba.account_classification = 'INTERNAL'
AND sh.statement_header_id = sl.statement_header_id
AND sh.statement_header_id = e.statement_header_id
AND sl.statement_line_id = e.statement_line_id
AND e.statement_line_id is not null
AND sl.status != 'EXTERNAL'
AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID,NULL,ba.bank_account_id,:P_BANK_ACCOUNT_ID)
AND trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND trunc(sh.statement_date) <= nvl(:LP_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 e.deal_status_flag = 'Y'
union all
select 'B' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
sl.line_number line_no,
sh.statement_Date statement_date,
sl.trx_date trx_date,
ba.bank_account_num bank_acc,
ba.bank_Account_name bank_Account_name,
ba.currency_code acc_currency,
null message_name,
nvl(sl.amount,0) amount,
l2.meaning trx_type,
nvl(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
sl.bank_Account_text bank_Account_text,
e.deal_type deal_type,
e.deal_num deal_num,
e.transaction_num transaction_num,
offset_deal_num offset_deal_num,
1 count,
cpo.currency_code C_CURRENCY_CODE,
offset_transaction_num offset_transaction_num,
xle.name counter_name,
trxn.transaction_sub_type_name subtype_name,
l3.meaning status,
l4.meaning cashflows_create,
CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
from
ce_lookups l2,
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_Statement_headers sh,
ce_statement_lines sl,
ce_zba_Deal_messages e,
ce_cashpools cpo,
ce_payment_transactions bat,
ce_trxns_subtype_codes trxn,
xle_firstparty_information_v xle,
ce_lookups l3,
ce_lookups l4
where
l2.lookup_type = 'BANK_TRX_TYPE'
AND l2.lookup_code = sl.trx_type
AND :c_profile = 'CE'
AND bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND ba.account_classification = 'INTERNAL'
AND sh.statement_header_id = sl.statement_header_id
AND sh.statement_header_id = e.statement_header_id
AND sl.statement_line_id = e.statement_line_id
AND e.statement_line_id is not null
AND sl.status != 'EXTERNAL'
AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID,NULL,ba.bank_account_id,:P_BANK_ACCOUNT_ID)
AND trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND trunc(sh.statement_date) <= nvl(:LP_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 e.deal_status_flag = 'Y'
AND e.cashpool_id = cpo.cashpool_id
AND e.transaction_num = bat.trxn_reference_number
AND cpo.trxn_subtype_code_id = trxn.trxn_subtype_code_id(+)
AND bat.destination_legal_entity_id = xle.legal_entity_id
AND l3.lookup_type = 'CE_BAT_STATUS'
AND l3.lookup_code = bat.trxn_status_code
AND l4.lookup_type = 'YES/NO'
AND l4.lookup_code = e.cashflows_created_flag
union all
select 'C' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
sl.line_number line_no,
sh.statement_date statement_date,
sl.trx_date trx_date,
ba.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
nvl(sl.amount,0) amount,
l2.meaning trx_type,
nvl(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
sl.bank_account_text bank_account_text,
null deal_type,
null deal_num,
null transaction_num,
null offset_deal_num,
1 count,
decode(:c_profile,'CE',nvl(cpo.currency_code,ba.currency_code),'XTR',nvl(sl.currency_code,ba.currency_code)) C_CURRENCY_CODE,
null offset_transaction_num,
null counter_name,
null subtype_name,
null status,
null cashflows_create,
CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
FROM
ce_lookups l2,
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_statement_headers sh,
ce_statement_lines sl,
ce_zba_deal_messages e,
ce_cashpools cpo
WHERE
l2.lookup_type = 'BANK_TRX_TYPE'
AND l2.lookup_code = sl.trx_type
AND bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND ba.account_classification='INTERNAL'
AND sh.statement_header_id = sl.statement_header_id
AND sh.statement_header_id = e.statement_header_id
AND sl.statement_line_id = e.statement_line_id
AND e.statement_line_id is not null
AND sl.status != 'EXTERNAL'
AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID,NULL,ba.bank_account_id,:P_BANK_ACCOUNT_ID)
AND trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND trunc(sh.statement_date) <= nvl(:LP_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 e.deal_status_flag = 'N'
AND e.cashpool_id = cpo.cashpool_id(+)
UNION ALL
SELECT 'A' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
0 line_no,
sh.statement_date statement_date,
to_date(NULL) trx_date,
ba.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
to_number(NULL) amount,
NULL trx_type,
NVL(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
NULL bank_Account_text,
null deal_type,
null deal_num,
null transaction_num,
null offset_deal_num,
1 count,
null C_CURRENCY_CODE,
null offset_transaction_num,
null counter_name,
null subtype_name,
null status,
null cashflows_create,
CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
FROM
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_statement_headers sh,
ce_zba_deal_messages e
WHERE
bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND ba.account_classification = 'INTERNAL'
AND sh.statement_header_id = e.statement_header_id
AND e.statement_line_id is null
AND ba.bank_account_id=decode(:P_BANK_ACCOUNT_ID,null,ba.bank_account_id,:P_BANK_ACCOUNT_ID)
AND trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND trunc(sh.statement_date) <= nvl(:LP_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 e.deal_status_flag = 'N'
order by break_group, line_no |
