select /*+ push_pred(x) */
x.*
from
(
select
/* Q1. Imported Statements */
(select hou.name from hr_operating_units hou where hou.organization_id = csh.org_id) ou,
cbbv.bank_name bank_name,
cbbv.bank_branch_name bank_branch_name,
cbagv.bank_account_name bank_account_name,
cbagv.bank_account_num bank_account_number,
cbagv.currency_code account_currency,
csh.statement_number statement_number,
csh.statement_date statement_date,
(select
cshi.creation_date
from
ce_statement_headers_int cshi
where
cshi.statement_number = csh.statement_number
and cshi.currency_code = nvl(csh.currency_code,cbagv.currency_code)
and cshi.bank_account_num = cbagv.bank_account_num
and nvl(cshi.org_id,-99) = nvl(csh.org_id,-99)
) statement_interface_date,
csh.doc_sequence_value doc_sequence_value,
csl.line_number line_number,
csl.trx_date trx_date,
xxen_util.meaning(csl.status,'STATEMENT_LINE_STATUS',260) line_status,
csl.currency_code trx_currency,
cl2.meaning trx_type,
csl.bank_trx_number trx_no,
'CE' application,
--
'Imported' import_status,
null interf_header_status_flag,
csl.status line_status_code,
nvl2(csl.statement_line_id,null,'Y') no_statement_line_flag, -- Q8
(select
'Y'
from
ar_cash_receipts_all acra,
ar_cash_receipt_history_all acrha,
ce_statement_reconcils_all csra
where
csra.statement_line_id = csl.statement_line_id
and csra.reference_type in ('RECEIPT','DM REVERSAL')
and csra.current_record_flag = 'Y'
and csra.status_flag = 'M'
and acrha.cash_receipt_history_id = csra.reference_id
and acra.cash_receipt_id = acrha.cash_receipt_id
and acra.comments = 'Created by Auto Bank Rec'
and rownum <= 1
) auto_created_misc_rec_flag, -- Q2
(select distinct listagg(nvl(ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(cre.message_name),'Y'),', ') within group (order by cre.message_name) from ce_reconciliation_errors cre where cre.statement_line_id = csl.statement_line_id) line_recon_error, -- Q3
(select distinct listagg(nvl(ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(cre.message_name),'Y'),', ') within group (order by cre.message_name) from ce_reconciliation_errors cre where cre.statement_header_id = csh.statement_header_id and cre.statement_line_id is null) header_recon_error, -- Q4
(select listagg (nvl(ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(chie.message_name),', '),'Y') within group (order by chie.message_name) from ce_header_interface_errors chie where chie.bank_account_num = cbagv.bank_account_num and chie.statement_number = csh.statement_number) header_intf_error, -- Q6
(select distinct listagg (chie.currency_code,', ') within group (order by chie.currency_code) from ce_header_interface_errors chie where chie.bank_account_num = cbagv.bank_account_num and chie.statement_number = csh.statement_number) header_intf_error_cur, -- Q6
(select listagg (nvl(ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(clie.message_name),'Y'),', ') within group (order by clie.message_name) from ce_line_interface_errors clie where clie.bank_account_num = cbagv.bank_account_num and clie.statement_number = csh.statement_number and clie.line_number = csl.line_number) line_intf_error, -- Q7
(select listagg (clie.bank_acct_currency_code,', ') within group (order by clie.bank_acct_currency_code) from ce_line_interface_errors clie where clie.bank_account_num = cbagv.bank_account_num and clie.statement_number = csh.statement_number and clie.line_number = csl.line_number) line_intf_error_cur, -- Q7
(select count(*) from ce_statement_reconcils_all csra where csra.statement_line_id = csl.statement_line_id and csra.current_record_flag = 'Y' and csra.status_flag = 'M') recon_count,
(select listagg (csra.reference_type,', ') within group (order by csra.reference_type) from ce_statement_reconcils_all csra where csra.statement_line_id = csl.statement_line_id and csra.current_record_flag = 'Y' and csra.status_flag = 'M') recon_types,
--
csh.statement_header_id,
csl.statement_line_id
--
from
ce_lookups cl2,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
ce_statement_headers csh,
ce_statement_lines csl
where
cbbv.branch_party_id = cbagv.bank_branch_id
and cbagv.bank_account_id = csh.bank_account_id
and csl.statement_header_id (+) = csh.statement_header_id
and cl2.lookup_type (+) = 'BANK_TRX_TYPE'
and cl2.lookup_code (+) = csl.trx_type
--
union all
select
/* Q2. Interface Statements */
(select hou.name from hr_operating_units hou where hou.organization_id = csh.org_id) ou,
csh.bank_name bank_name,
csh.bank_branch_name bank_branch_name,
cbagv.bank_account_name bank_account_name,
csh.bank_account_num bank_account_number,
csh.currency_code account_currency,
csh.statement_number statement_number,
csh.statement_date statement_date,
csh.creation_date statement_interface_date,
to_number(null) doc_sequence_value,
csl.line_number line_number,
csl.trx_date trx_date,
'Interface' line_status,
csl.currency_code trx_currency,
csl.trx_code trx_type,
csl.bank_trx_number trx_no,
'CE' application,
--
'Interface' import_status,
csh.record_status_flag interf_header_status_flag,
'INTERFACE' line_status_code,
nvl2(csl.bank_account_num,null,'Y') no_statement_line_flag, -- Q8
null auto_created_misc_rec_flag, -- Q2
null line_recon_error_flag, -- Q3
null header_recon_error_flag, -- Q4
(select listagg (nvl(ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(chie.message_name),', '),'Y') within group (order by chie.message_name) from ce_header_interface_errors chie where chie.bank_account_num = csh.bank_account_num and chie.statement_number = csh.statement_number) header_intf_error, -- Q5
(select listagg (chie.currency_code,', ') within group (order by chie.currency_code) from ce_header_interface_errors chie where chie.bank_account_num = csh.bank_account_num and chie.statement_number = csh.statement_number) header_intf_error_cur, -- Q5
(select listagg (nvl(ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(clie.message_name),'Y'),', ') within group (order by clie.message_name) from ce_line_interface_errors clie where clie.bank_account_num = csl.bank_account_num and clie.statement_number = csl.statement_number and clie.line_number = csl.line_number) line_intf_error, -- Q7
(select listagg (clie.bank_acct_currency_code,', ') within group (order by clie.bank_acct_currency_code) from ce_line_interface_errors clie where clie.bank_account_num = csl.bank_account_num and clie.statement_number = csl.statement_number and clie.line_number = csl.line_number and rownum <= 1) line_intf_error_cur, -- Q7
to_number(null) recon_count,
null recon_types,
--
to_number(null) statement_header_id,
to_number(null) statement_line_id
--
from
ce_statement_headers_int csh,
ce_statement_lines_interface csl,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv
where
cbagv.bank_account_num (+) = csh.bank_account_num
and cbagv.currency_code (+) = csh.currency_code
and cbbv.branch_party_id (+) = cbagv.bank_branch_id
) x
where
1=1
order by
x.bank_name,
x.bank_branch_name,
x.bank_account_name,
x.bank_account_number,
x.statement_date,
x.statement_number,
x.line_number |
