ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 22F9280696B38737E06362FB0905B27A 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 Y US ECC Cash Management, Bank Statements 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 Enterprise Command Center 1=1 2=2 3=3