CE Bank Statement Import Execution

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 csh.statement_header_id = csl.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