select
/* Q1. successfuly imported statements with no errors */
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,
'Processed' import_status,
null error_type,
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,
null message_name,
nvl(csl.amount,0) amount,
nvl(csl.currency_code,
cbagv.currency_code
) trx_currency,
cl2.meaning trx_type,
csl.bank_trx_number trx_no,
'CE' application
from
ce_lookups cl2,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
ce_statement_headers csh,
ce_statement_lines csl
where
1=1
and nvl(:p_show_errors_only,'No') != 'Yes'
and 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
and not exists
(select
null
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 not exists
(select
null
from
ce_reconciliation_errors clie
where
csl.statement_line_id = clie.statement_line_id
and clie.statement_line_id is not null
and csl.status != 'EXTERNAL'
)
--
union all
--
select
/* Q2. miscellaneous receipts created */
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,
'Processed' import_status,
cl1.meaning error_type,
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,
null message_name,
nvl(csl.amount,0) amount,
acra.currency_code trx_currency,
cl2.meaning trx_type,
acra.receipt_number trx_no,
'CE' application
from
ce_lookups cl1,
ce_lookups cl2,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
ce_statement_headers csh,
ce_statement_lines csl,
ar_cash_receipts_all acra,
ar_cash_receipt_history_all acrha,
ce_statement_reconcils_all csra
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and cbagv.bank_account_id = csh.bank_account_id
and csh.statement_header_id = csl.statement_header_id
and 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 cl1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
and cl1.lookup_code = 'MISC_RECEIPT_CREATED'
and cl2.lookup_type (+) = 'BANK_TRX_TYPE'
and cl2.lookup_code (+) = csl.trx_type
--
union all
--
select /*+ push_pred(clie) */
/*Q3. reconciliation errors - line level */
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,
'Processed' import_status,
cl1.description error_type,
csh.statement_number statement_no,
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_no,
csl.trx_date trx_date,
xxen_util.meaning(csl.status,'STATEMENT_LINE_STATUS',260) line_status,
clie.message_name,
nvl(csl.amount,0) amount,
nvl(csl.currency_code,cbagv.currency_code) c_currency_code,
cl2.meaning trx_type,
csl.bank_trx_number trx_no,
nvl(clie.application_short_name,'CE') application_short_name
from
ce_lookups cl1,
ce_lookups cl2,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
ce_statement_headers csh,
ce_statement_lines csl,
(select distinct
cre.statement_header_id,
cre.statement_line_id,
cre.application_short_name,
listagg (ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(cre.message_name),', ') within group (order by cre.message_name) message_name
from
ce_reconciliation_errors cre
group by
cre.statement_header_id,
cre.statement_line_id,
cre.application_short_name
) clie
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and cbagv.bank_account_id = csh.bank_account_id
and csh.statement_header_id = csl.statement_header_id
and csh.statement_header_id = clie.statement_header_id
and csl.statement_line_id = clie.statement_line_id
and clie.statement_line_id is not null
and csl.status != 'EXTERNAL'
and cl1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
and cl1.lookup_code = 'RECONCILIATION'
and cl2.lookup_type (+) = 'BANK_TRX_TYPE'
and cl2.lookup_code (+) = csl.trx_type
--
union all
--
select /*+ push_pred(clie) */
/* Q4. reconciliation errors - header level */
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,
'Processed' import_status,
cl1.description error_type,
csh.statement_number statement_no,
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,
0 line_no,
to_date(null) trx_date,
null line_status,
clie.message_name,
to_number(null) amount,
cbagv.currency_code c_currency_code,
null trx_type,
null trx_no,
nvl(clie.application_short_name,'CE') application_short_name
from
ce_lookups cl1,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
ce_statement_headers csh,
(select distinct
cre.statement_header_id,
cre.statement_line_id,
cre.application_short_name,
listagg (ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(cre.message_name),', ') within group (order by cre.message_name) message_name
from
ce_reconciliation_errors cre
group by
cre.statement_header_id,
cre.statement_line_id,
cre.application_short_name
) clie
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and cbagv.bank_account_id = csh.bank_account_id
and clie.statement_line_id is null
and csh.statement_header_id = clie.statement_header_id
and cl1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
and cl1.lookup_code = 'RECONCILIATION'
--
union all
--
(
select /*+ push_pred(clie) */
/*Q5. header interface errors */
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,
'Error' import_status,
cl1.description error_type,
clie.statement_number statement_no,
trunc(csh.statement_date) statement_date,
csh.creation_date statement_interface_date,
to_number(null) doc_sequence_value,
0 line_no,
csh.statement_date trx_date,
null line_status,
clie.message_name,
0 amount,
' ' c_currency_code,
' ' trx_type,
' ' trx_no,
nvl(clie.application_short_name,'CE') application_short_name
from
ce_lookups cl1,
ce_statement_headers_int csh,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
(select distinct
chie.bank_account_num,
chie.statement_number,
chie.currency_code,
chie.application_short_name,
listagg (ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(chie.message_name),', ') within group (order by chie.message_name) message_name
from
ce_header_interface_errors chie
group by
chie.bank_account_num,
chie.statement_number,
chie.currency_code,
chie.application_short_name
) clie
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and clie.bank_account_num = cbagv.bank_account_num
and cbagv.currency_code = csh.currency_code
and csh.bank_account_num = clie.bank_account_num
and csh.statement_number = clie.statement_number
and csh.currency_code = clie.currency_code
and cl1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
and cl1.lookup_code = 'STATEMENT'
and nvl(csh.record_status_flag,'N') != 'T' -- not transferred
--
union
--
select /*+ push_pred(clie) */
/*Q6. header interface errors, in case the stmt has been transfered to ce_statement_headers and there're warnings during transfer */
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,
'Processed' import_status,
cl1.description error_type,
clie.statement_number statement_no,
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,
to_number(null) doc_sequence_value,
0 line_no,
csh.statement_date trx_date,
null line_status,
clie.message_name,
0 amount,
' ' c_currency_code,
' ' trx_type,
' ' trx_no,
nvl(clie.application_short_name,'CE') application_short_name
from
ce_lookups cl1,
ce_statement_headers csh,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
(select distinct
chie.bank_account_num,
chie.statement_number,
chie.currency_code,
chie.application_short_name,
listagg (ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(chie.message_name),', ') within group (order by chie.message_name) message_name
from
ce_header_interface_errors chie
group by
chie.bank_account_num,
chie.statement_number,
chie.currency_code,
chie.application_short_name
) clie
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and csh.bank_account_id = cbagv.bank_account_id
and clie.bank_account_num = cbagv.bank_account_num
and clie.statement_number = csh.statement_number
and clie.currency_code = nvl(csh.currency_code,cbagv.currency_code)
and cl1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
and cl1.lookup_code = 'STATEMENT'
)
--
union all
--
select /*+ push_pred(clie) */
/*Q7. line interface errors */
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,
'Error' import_status,
cl1.description error_type,
clie.statement_number statement_no,
trunc(csh.statement_date) statement_date,
csh.creation_date statement_interface_date,
to_number(null) doc_sequence_value,
clie.line_number line_no,
csl.trx_date trx_date,
'Error' line_status,
clie.message_name,
nvl(csl.amount,0) amount,
nvl(curr.currency_code, cbagv.currency_code) c_currency_code,
' ' trx_type,
' ' trx_no,
nvl(clie.application_short_name,'CE') application_short_name
from
fnd_currencies curr,
ce_lookups cl1,
ce_statement_headers_int csh,
ce_statement_lines_interface csl,
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
(select distinct
clie.bank_account_num,
clie.statement_number,
clie.line_number,
clie.bank_acct_currency_code,
clie.application_short_name,
listagg (ce_cexinerr_xmlp_pkg.c_prt_message_nameformula(clie.message_name),', ') within group (order by clie.message_name) message_name
from
ce_line_interface_errors clie
group by
clie.bank_account_num,
clie.statement_number,
clie.line_number,
clie.bank_acct_currency_code,
clie.application_short_name
) clie
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and cbagv.bank_account_num = csh.bank_account_num
and cbagv.currency_code = csh.currency_code
and csh.bank_account_num = csl.bank_account_num
and csh.statement_number = csl.statement_number
and csh.currency_code = csl.bank_acct_currency_code
and clie.bank_account_num = cbagv.bank_account_num
and csl.line_number = clie.line_number
and csl.statement_number = clie.statement_number
and csl.bank_account_num = clie.bank_account_num
and csl.bank_acct_currency_code = clie.bank_acct_currency_code
and curr.currency_code(+) = csl.currency_code
and cl1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
and cl1.lookup_code = 'LINE'
union all
select
/* Q8. statement headers with no lines */
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,
'Processed' import_status,
'No Statement Lines' error_type,
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,
to_number(null) line_number,
to_date(null) trx_date,
null line_status,
'No Statement Lines' message_name,
to_number(null) amount,
null trx_currency,
null trx_type,
null trx_no,
'CE' application
from
ce_bank_accts_gt_v cbagv,
ce_bank_branches_v cbbv,
ce_statement_headers csh
where
1=1
and cbbv.branch_party_id = cbagv.bank_branch_id
and cbagv.bank_account_id = csh.bank_account_id
and not exists -- no statement lines
(select
null
from
ce_statement_lines csl
where
csl.statement_header_id = csh.statement_header_id
)
and not exists -- will be picked up in Q4 header recon error
(select
null
from
ce_reconciliation_errors cre
where
cre.statement_line_id is null
and cre.statement_header_id = csh.statement_header_id
)
and not exists -- will be picked up in Q6 header interface errors
(select
null
from
ce_header_interface_errors chie
where
chie.bank_account_num = cbagv.bank_account_num
and chie.statement_number = csh.statement_number
and chie.currency_code = nvl(csh.currency_code,cbagv.currency_code)
)
--
order by
1,2,3,4,9,12 |
