CE Bank Statement Import Diagnostics

Description
Report to help diagnose the entries shown or missing from the CE Bank Statement Import Execution Report

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
Bank Name
x.bank_name=:p_bank_name
LOV
Bank Branch Name
x.bank_branch_name=:p_bank_branch_name
LOV
Bank Account Name
x.bank_account_name=:p_bank_account_name
LOV
Bank Account Number
x.bank_account_number=:p_bank_account_num
LOV
Statement Number From
x.statement_number>=:p_stat_number_from
LOV
Statement Number To
x.statement_number<=:p_stat_number_to
LOV
Statement Date From
x.statement_date>=trunc(:p_stat_date_from)
Date
Statement Date To
x.statement_date<trunc(:p_stat_date_to)+1
Date