ECC Cash Management, Bank Statements

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Bank stantement and statement lines
Dataset Key: ce-bank-statement
Query Procedure: ce_ecc_util_pvt.get_ecc_data_load_info
Security Procedure: CE_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
select
x.*
from
(
WITH ce_bank_statements AS (
    SELECT /*+ ordered index(hpb hz_parties_u1) index(hpbb hz_parties_u1) index(xep xle_entity_profiles_u1) index(gcc gl_code_combinations_u1) push_pred(nsf_recon) push_pred(oth_recon) */
 /* Statement Details*/
        xep.name                                                              legal_entity,
        xep.legal_entity_id                                                    legal_entity_id,
        cbagv.bank_account_num                                                bank_account_num,
        cbagv.bank_account_name                                               bank_account_name,
        cbagv.bank_account_type                                               bank_account_type,
        cbagv.currency_code                                                   bank_account_currency,
        hpb.party_name                                                        bank_name,
        hpbb.party_name                                                       branch_name,
        csh.statement_number                                                  statement_num,
        csh.doc_sequence_value                                                document_number,
        trunc(csh.statement_date)                                             statement_date,
        trunc(csh.gl_date)                                                    gl_date,
        csh.check_digits,
        csh.control_begin_balance                                             control_opening_balance,
        nvl(csh.control_total_cr, 0) - nvl(csh.control_total_dr, 0)           control_net_movement,
        csh.control_end_balance                                               control_closing_balance,
        csh.control_total_cr                                                  control_receipts,
        csh.control_total_dr                                                  control_payments,
        nvl(csh.control_cr_line_count, 0) + nvl(csh.control_dr_line_count, 0) control_lines_count,
        csh.control_cr_line_count                                             control_receipts_count,
        csh.control_dr_line_count                                             control_payments_count,
        csh.cashflow_balance,
        csh.int_calc_balance                                                  value_dated_balance,
        csh.one_day_float,
        csh.two_day_float,
        csh.auto_loaded_flag                   auto_loaded_flag,
        csh.statement_complete_flag           statement_complete_flag,
        csh.org_id org_id,
        cbagv.multi_currency_allowed_flag     multi_currency_flag,
 /* Statement Line Details*/
        csl.line_number                                                       line_num,
        csl.trx_type                                                          trx_type,
        csl.trx_code                                                          code,
        csl.bank_trx_number                                                   trx_number,
        trunc(csl.trx_date)                                                   trx_date,
        trunc(csl.effective_date)                                             value_date,
        csl.status                                                            status,
        csl.status                                                            status_code,
 /* credit/debit amounts*/
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_CREDIT', 'CREDIT', 'STOP', 'SWEEP_IN') THEN
                       csl.amount
                   ELSE
                       NULL
               END,
               NULL)                                                          receipt_amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_DEBIT', 'DEBIT', 'REJECTED', 'NSF', 'SWEEP_OUT') THEN
                       csl.amount
                   ELSE
                       NULL
               END,
               NULL)                                                          payment_amount,
 /* net amounts*/
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_DEBIT', 'DEBIT', 'REJECTED', 'NSF', 'SWEEP_OUT') THEN
                       - 1
                   ELSE
                       1
               END
               * csl.amount,
               NULL)                                                          net_amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_DEBIT', 'DEBIT', 'REJECTED', 'NSF', 'SWEEP_OUT') THEN
                       - 1
                   ELSE
                       1
               END
               * decode(csl.status,
                        'EXTERNAL',
                        csl.amount,
                        decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched)),
               NULL)                                                          net_reconciled_amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_DEBIT', 'DEBIT', 'REJECTED', 'NSF', 'SWEEP_OUT') THEN
                       - 1
                   ELSE
                       1
               END
               * decode(csl.status,
                        'EXTERNAL',
                        TO_NUMBER(NULL),
                        decode(sign(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                     0)),
                               1,
                               csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                0),
                               NULL)),
               NULL)                                                          net_unreconciled_amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_DEBIT', 'DEBIT', 'REJECTED', 'NSF', 'SWEEP_OUT') THEN
                       - 1
                   ELSE
                       1
               END
               * decode(csl.status,
                        'EXTERNAL',
                        TO_NUMBER(NULL),
                        decode(sign(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                     0)),
                               - 1,
                               -(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                  0)),
                               NULL)),
               NULL)                                                          net_overreconciled_amount,
 /* amounts*/
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               csl.amount,
               NULL)                                                          amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               decode(csl.status,
                      'EXTERNAL',
                      csl.amount,
                      decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched)),
               NULL)                                                          reconciled_amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               decode(csl.status,
                      'EXTERNAL',
                      TO_NUMBER(NULL),
                      decode(sign(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                   0)),
                             1,
                             csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                              0),
                             NULL)),
               NULL)                                                          unreconciled_amount,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               decode(csl.status,
                      'EXTERNAL',
                      TO_NUMBER(NULL),
                      decode(sign(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                   0)),
                             - 1,
                             -(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                                0)),
                             NULL)),
               NULL)                                                          overreconciled_amount,
 /* counts*/
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               1,
               NULL)                                                          line_count,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_CREDIT', 'CREDIT', 'STOP', 'SWEEP_IN') THEN
                       1
                   ELSE
                       0
               END,
               NULL)                                                          receipt_line_count,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               CASE
                   WHEN csl.trx_type IN('MISC_DEBIT', 'DEBIT', 'REJECTED', 'NSF', 'SWEEP_OUT') THEN
                       1
                   ELSE
                       0
               END,
               NULL)                                                          payment_line_count,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               decode(csl.status, 'UNRECONCILED', 1, 'ERROR', 1,
                      0),
               NULL)                                                          unreconciled_line_count,
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               decode(csl.trx_type, 'NSF', nsf_recon.count_matched, oth_recon.count_matched),
               NULL)                                                          reconciled_trx_count,
 /* charges*/
        decode(COUNT(csl.statement_line_id)
               OVER(PARTITION BY csl.statement_line_id
                    ORDER BY
                        csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ),
               1,
               decode(csl.charges_amount,
                      0,
                      TO_NUMBER(NULL),
                      csl.charges_amount),
               NULL)                                                          charges_amount,
 
        csl.currency_code                                                     trx_currency,
        trunc(csl.exchange_rate_date)                                         exchange_date,
        (
            SELECT
                gdct.user_conversion_type
            FROM
                gl_daily_conversion_types gdct
            WHERE
                gdct.conversion_type = csl.exchange_rate_type
        )                                                                     exchange_rate_type,
        csl.exchange_rate,
        csl.original_amount                                                   trx_original_amount,
 
        nvl(csl.customer_text,(
            SELECT /*+ push_pred */
                crtv.agent_name
            FROM
                ce_reconciled_transactions_v crtv
            WHERE
                    crtv.statement_line_id = csl.statement_line_id
                AND ROWNUM <= 1
        ))                                                                    agent,
        csl.bank_account_text                                                 agent_bank_account,
        csl.invoice_text                                                      invoice,
        csl.trx_text                                                          description,
        (
            SELECT /*+ push_pred */ DISTINCT
                LISTAGG(crev.error_message, ', ') WITHIN GROUP(
                ORDER BY
                    crev.error_message
                )
                OVER()
            FROM
                (
                    SELECT DISTINCT
                        crev.statement_header_id,
                        crev.statement_line_id,
                        fnd_message.get_string(crev.application_short_name, crev.message_name) error_message
                    FROM
                        ce_reconciliation_errors_v crev
                ) crev
            WHERE
                    crev.statement_header_id = csl.statement_header_id
                AND crev.statement_line_id = csl.statement_line_id
        )                                                                     error_messages,
 
 /* GL Cash Account Details*/
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE'),
             NULL)                                                            gl_company_code,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION'),
             NULL)                                                            gl_company_desc,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE'),
             NULL)                                                            gl_account_code,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION'),
             NULL)                                                            gl_account_desc,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE'),
             NULL)                                                            gl_cost_center_code,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION'),
             NULL)                                                            gl_cost_center_desc,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'ALL', 'Y', 'VALUE'),
             NULL)                                                            gl_cash_account,
        nvl2(gcc.code_combination_id,
             fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL
             ,
                                                                   gcc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION'),
             NULL)                                                            gl_cash_account_desc,
 
        csh.statement_header_id,
        csl.statement_line_id,
        COUNT(csl.statement_line_id)
        OVER(PARTITION BY csl.statement_line_id
             ORDER BY
                 csl.statement_line_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )                                                                     recon_sort_num,
        decode(csl.status,
               'EXTERNAL',
               'N',
               decode(sign(csl.amount - nvl(decode(csl.trx_type, 'NSF', nsf_recon.amount_matched, oth_recon.amount_matched),
                                            0)),
                      1,
                      'Y',
                      'N'))                                                   unrecon_amount_exists_flag,
       flv1.meaning  trx_type_meaning ,
       flv2.meaning status_meaning
       ,flv1.language language
    FROM
        CE_BANK_ACCOUNTS   cbagv,
        ce_statement_headers csh,
        ce_statement_lines   csl,
        hz_parties           hpb,
        hz_parties           hpbb,
        xle_entity_profiles  xep,
        gl_code_combinations gcc,
        fnd_lookup_values flv1,
        fnd_lookup_values flv2,
        (
            SELECT
                csra.statement_line_id,
                SUM(decode(csra.reference_type,
                           'RECEIPT',
                           decode(acra.type,
                                  'MISC',
                                  decode(acrha.status,
                                         'CLEARED',
                                         nvl(- csra.amount, 0),
                                         nvl(csra.amount, 0)),
                                  nvl(csra.amount, 0)),
                           nvl(csra.amount, 0))) amount_matched,
                COUNT(*)              count_matched
            FROM
                ce_statement_reconcils_all  csra,
                ar_cash_receipt_history_all acrha,
                ar_cash_receipts_all        acra
            WHERE
                    csra.status_flag = 'M'
                AND csra.current_record_flag = 'Y'
                AND csra.reference_id = acrha.cash_receipt_history_id (+)
                AND acrha.cash_receipt_id = acra.cash_receipt_id (+)
            GROUP BY
                csra.statement_line_id
        )                    nsf_recon,
        (
            SELECT
                csl.statement_line_id,
                SUM(decode(decode(csl.trx_type, 'MISC_CREDIT', 'RECEIPT', 'CREDIT', 'RECEIPT',
                                  'MISC_DEBIT', 'PAYMENT', 'SWEEP_IN', 'RECEIPT', 'SWEEP_OUT',
                                  'PAYMENT', 'DEBIT', 'PAYMENT', 'OTHERS'),
                           'RECEIPT',
                           decode(csra.reference_type,
                                  'PAYMENT',
                                  nvl(- csra.amount, 0),
                                  nvl(csra.amount, 0)),
                           'PAYMENT',
                           decode(csra.reference_type,
                                  'RECEIPT',
                                  nvl(- csra.amount, 0),
                                  nvl(csra.amount, 0)),
                           nvl(csra.amount, 0))) amount_matched,
                COUNT(*)              count_matched
            FROM
                ce_statement_reconcils_all csra,
                ce_statement_lines         csl
            WHERE
                    csra.statement_line_id = csl.statement_line_id
                AND csra.status_flag = 'M'
                AND csra.current_record_flag = 'Y'
            GROUP BY
                csl.statement_line_id
        )                    oth_recon
    WHERE
            csh.bank_account_id = cbagv.bank_account_id
        AND csl.statement_header_id = csh.statement_header_id
        AND hpb.party_id = cbagv.bank_id
        AND hpbb.party_id = cbagv.bank_branch_id
        AND xep.legal_entity_id = cbagv.account_owner_org_id
        AND gcc.code_combination_id (+) = cbagv.asset_code_combination_id
        AND nsf_recon.statement_line_id (+) = decode(csl.trx_type, 'NSF', csl.statement_line_id, NULL)
        AND oth_recon.statement_line_id (+) = decode(csl.trx_type, 'NSF', NULL, csl.statement_line_id)
        AND (csh.statement_complete_flag is null OR csh.statement_complete_flag = 'N')
        AND flv1.lookup_code=csl.trx_type
        AND flv2.lookup_code=csl.status
        AND flv1.lookup_type='BANK_TRX_TYPE'
        AND flv2.lookup_type='STATEMENT_LINE_STATUS'
        AND flv1.language=flv2.language
        AND flv1.language in ('US') 
 
        AND 1 = 1
)
/* Main Query Starts Here*/
select * from (
select
 cebs.bank_account_num||'-'||cebs.statement_header_id||'-'||cebs.statement_line_id    ECC_SPEC_ID,
 cebs.legal_entity,
 cebs.legal_entity_id,
 cebs.org_id ,
 cebs.bank_account_num,
 cebs.bank_account_name,
 cebs.bank_account_type,
 cebs.bank_account_currency,
 cebs.bank_name,
 cebs.branch_name,
 cebs.statement_num,
 cebs.document_number,
 cebs.statement_date,
 cebs.gl_date,
 cebs.check_digits,
 cebs.control_opening_balance,
 cebs.control_net_movement,
 cebs.control_closing_balance,
 cebs.control_receipts,
 cebs.control_payments,
 cebs.control_lines_count,
 cebs.control_receipts_count,
 cebs.control_payments_count,
 
 case
 when nvl(cebs.control_receipts,0) = nvl(sum(cebs.receipt_amount) over (partition by cebs.statement_header_id),0) and
      nvl(cebs.control_payments,0) = nvl(sum(cebs.payment_amount) over (partition by cebs.statement_header_id),0)
 then null
 else 'Yes'
 end control_amount_error,
 case
 when nvl(cebs.control_receipts_count,0) = nvl(sum(cebs.receipt_line_count) over (partition by cebs.statement_header_id),0) and
      nvl(cebs.control_payments_count,0) = nvl(sum(cebs.payment_line_count) over (partition by cebs.statement_header_id),0)
 then null
 else 'Yes'
 end control_count_error,
 
 cebs.cashflow_balance,
 cebs.value_dated_balance,
 cebs.one_day_float,
 cebs.two_day_float,
 cebs.auto_loaded_flag,
 cebs.statement_complete_flag,
 cebs.multi_currency_flag,
 
 /*Statement Line Details*/
 cebs.line_num,
 cebs.trx_type,
 cebs.code,
 cebs.trx_number,
 cebs.trx_date,
 cebs.value_date,
 cebs.status,
 cebs.receipt_amount,
 cebs.payment_amount,
 cebs.net_amount,
 cebs.net_reconciled_amount,
 cebs.net_unreconciled_amount,
 cebs.net_overreconciled_amount,
 cebs.amount,
 cebs.reconciled_amount,
 cebs.unreconciled_amount,
 cebs.overreconciled_amount,
 cebs.line_count,
 cebs.receipt_line_count,
 cebs.payment_line_count,
 cebs.unreconciled_line_count,
 cebs.reconciled_trx_count,
 cebs.charges_amount,
 cebs.trx_currency,
 cebs.exchange_date,
 cebs.exchange_rate_type,
 cebs.exchange_rate,
 cebs.trx_original_amount,
 cebs.agent,
 cebs.agent_bank_account,
 cebs.invoice,
 cebs.description,
 cebs.error_messages,
/* Reconcilation Trx Details*/
/*GL Cash Account Details*/
 cebs.gl_company_code,
 cebs.gl_company_desc,
 cebs.gl_account_code,
 cebs.gl_account_desc,
 cebs.gl_cost_center_code,
 cebs.gl_cost_center_desc,
 cebs.gl_cash_account,
 cebs.gl_cash_account_desc,
 
 cebs.statement_header_id,
 cebs.statement_line_id,
 cebs.recon_sort_num,
 /*Labels*/
 cebs.gl_company_code || ' - ' || cebs.gl_company_desc gl_company_label,
 cebs.bank_name || ' - ' || cebs.bank_account_num || ' - ' || cebs.bank_account_name || ' (' ||  cebs.bank_account_currency || ')' bank_account_label,
 to_char(cebs.statement_date,'YYYY-MM-DD') || ' - ' || cebs.statement_num  statement_num_label
,cebs.trx_type_meaning trx_type_meaning,
cebs.status_meaning status_meaning,
cebs.bank_name ||'|'|| cebs.bank_account_num bank_account_details
,cebs.language language
from
 ce_bank_statements        cebs
where
 3=3)
 PIVOT (max(trx_type_meaning) as trx_type_meaning,
            max(status_meaning) as status_meaning
            for LANGUAGE in ('US' "US"))
) x
where
2=2
Download
Blitz Report™