CE Bank Statement and Reconciliation
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Cash Management
Description: Bank Statements - Bank Statement Details and Reconciliation
Provides equivalent functionality to the following standard Oracle Forms/Reports
- Bank Statements and Reconciliation Form
Applicable Templates:
Pivot: Bank Statement by Transaction Type
Bank Statement Detail
Bank Statement Detail with Reconciled Trxs
- Bank Sta ... more
Description: Bank Statements - Bank Statement Details and Reconciliation
Provides equivalent functionality to the following standard Oracle Forms/Reports
- Bank Statements and Reconciliation Form
Applicable Templates:
Pivot: Bank Statement by Transaction Type
Bank Statement Detail
Bank Statement Detail with Reconciled Trxs
- Bank Sta ... more
Run
CE Bank Statement and Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
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, cbagv.masked_account_num bank_account_num, cbagv.bank_account_name bank_account_name, nvl(xxen_util.meaning(cbagv.bank_account_type,'BANK_ACCOUNT_TYPE',260),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, xxen_util.meaning(csh.auto_loaded_flag,'YES_NO',0) auto_loaded_flag, xxen_util.meaning(csh.statement_complete_flag,'YES_NO',0) statement_complete_flag, xxen_util.meaning(cbagv.multi_currency_allowed_flag,'YES_NO',0) multi_currency_flag, -- -- Statement Line Details csl.line_number line_num, xxen_util.meaning(csl.trx_type,'BANK_TRX_TYPE',260) trx_type, csl.trx_code code, csl.bank_trx_number trx_number, trunc(csl.trx_date) trx_date, trunc(csl.effective_date) value_date, xxen_util.meaning(csl.status,'STATEMENT_LINE_STATUS',260) 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, xxen_util.user_name(csl.last_updated_by) line_last_updated_by, xxen_util.client_time(csl.last_update_date) line_last_updated_date, -- -- 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 from ce_bank_accts_gt_v cbagv, ce_statement_headers csh, ce_statement_lines csl, hz_parties hpb, hz_parties hpbb, xle_entity_profiles xep, gl_code_combinations gcc, ( 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 1=1 ) &lp_recon_trx_qry -- -- Main Query Starts Here select &lp_recon_trx_hint cebs.legal_entity, 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, cebs.line_last_updated_by, cebs.line_last_updated_date, -- -- Reconcilation Trx Details &lp_recon_trx_col -- -- 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, |