CE General Ledger Reconciliation
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Cash Management
Description: Bank Statements - General Ledger Reconciliation
Provides equivalent functionality to the following standard Oracle Forms/Reports
- General Ledger Reconciliation Report
Applicable Templates:
Pivot: Reconciliation Summary
Source: General Ledger Reconciliation Report
Short Name: CEXRECRE
DB package: CE_CEXRECRE_XMLP_PKG
Description: Bank Statements - General Ledger Reconciliation
Provides equivalent functionality to the following standard Oracle Forms/Reports
- General Ledger Reconciliation Report
Applicable Templates:
Pivot: Reconciliation Summary
Source: General Ledger Reconciliation Report
Short Name: CEXRECRE
DB package: CE_CEXRECRE_XMLP_PKG
Run
CE General Ledger Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
with q_gl_end_bal as ( select 'GL END BALANCE' record_type, null operating_unit, null transaction_number, to_date(null) gl_date, to_date(null) effective_date, null agent, null payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, null status, :c_bank_curr_dsp currency, to_number(null) transaction_amount, to_number(null) accounted_amount, to_number (nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0) + nvl(bal.begin_balance_dr,0) - nvl(bal.begin_balance_cr,0)) gl_end_balance, glcc.code_combination_id cash_account_ccid from gl_balances bal, gl_code_combinations glcc where bal.actual_flag(+) = 'A' and bal.ledger_id(+) = :c_set_of_books_id and bal.currency_code(+) = :c_bank_curr_dsp and nvl(bal.translated_flag,'R') = 'R' and bal.period_name(+) = :p_period_name and bal.code_combination_id(+) = glcc.code_combination_id and glcc.chart_of_accounts_id = :c_chart_of_accounts_id and glcc.template_id is null and glcc.code_combination_id in ( select distinct case col.cnum when 1 then cba2.asset_code_combination_id when 2 then cgac2.asset_code_combination_id when 3 then cgac2.ap_asset_ccid when 4 then cgac2.ar_asset_ccid when 5 then cgac2.xtr_asset_ccid end from ce_bank_accounts cba2, ce_bank_acct_uses_all cbaua2, ce_gl_accounts_ccid cgac2, ( select 1 as cnum from dual union all select 2 as cnum from dual union all select 3 as cnum from dual union all select 4 as cnum from dual union all select 5 as cnum from dual ) col where cba2.bank_account_id = :p_bank_account_id and cbaua2.bank_account_id = cba2.bank_account_id and cgac2.bank_acct_use_id = cbaua2.bank_acct_use_id ) ), q_stmt_end_bal as ( select 'STMT END BALANCE' record_type, null operating_unit, null transaction_number, to_date(null) gl_date, to_date(null) effective_date, null agent, null payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, null status, :c_bank_curr_dsp currency, to_number(null) transaction_amount, to_number(:p_closing_balance) accounted_amount, to_number(null) gl_end_balance, to_number(:c_asset_cc_id) cash_account_ccid from dual ), q_ar_receipts as ( /* query 1: fetch latest receipt history event which has hit gl cash account and is not reconciled query 2: fetch unreconciled cleared event for a reversed receipt */ select 'RECEIPT' record_type, fnd_access_control_util.get_org_name(cr.org_id) operating_unit, cr.receipt_number transaction_number, crh.gl_date gl_date, cr.receipt_date effective_date, hz.party_name agent, arm.name payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, crh.status status, cr.currency_code currency, cr.amount transaction_amount, decode(:c_bank_curr_dsp, :c_gl_currency_code, decode(crh.status,'REVERSED',-crh.acctd_amount,crh.acctd_amount), decode(crh.status,'REVERSED',-crh.amount,crh.amount) ) accounted_amount, to_number(null) gl_end_balance, gla.ar_asset_ccid cash_account_ccid from ar_cash_receipts cr, ar_cash_receipt_history crh, hz_cust_accounts cu, hz_parties hz, ar_receipt_methods arm, ce_bank_acct_uses_ou_v bau, ce_bank_accounts ba, ce_gl_accounts_ccid gla, ce_system_parameters sys where cr.cash_receipt_id = crh.cash_receipt_id and cr.remit_bank_acct_use_id = bau.bank_acct_use_id and bau.bank_account_id = :p_bank_account_id and bau.org_id = cr.org_id and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and bau.ar_use_enable_flag = 'Y' and gla.bank_acct_use_id = bau.bank_acct_use_id and crh.account_code_combination_id = gla.ar_asset_ccid and crh.status in('REMITTED', 'CLEARED', 'REVERSED') and crh.gl_date <= :c_as_of_date and crh.gl_date >= sys.cashbook_begin_date and decode(crh.status,'REMITTED',nvl(crh.reversal_created_from,'X'),crh.created_from) <> 'RATE ADJUSTMENT TRIGGER' and not ( crh.status = 'REMITTED' and crh.created_from = 'RATE ADJUSTMENT TRIGGER' and crh.reversal_created_from is null and crh.amount = - 1 * cr.amount ) and not ( crh.status ='CLEARED' and nvl(crh.reversal_created_from,'x') ='RATE ADJUSTMENT TRIGGER' ) and crh.posting_control_id > 0 and not exists ( select 1 from ar_cash_receipt_history_all crh_r where crh_r.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id and crh_r.gl_date <= :c_as_of_date and crh_r.posting_control_id > 0 and crh_r.created_from <> 'RATE ADJUSTMENT TRIGGER' ) and cu.cust_account_id(+) = cr.pay_from_customer and hz.party_id(+) = cu.party_id and arm.receipt_method_id = cr.receipt_method_id and cr.status <> 'REV' and not exists ( select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh where sr.reference_id = crh.cash_receipt_history_id and sr.reference_type = 'RECEIPT' and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date union /* this union is required for receipts with rate adjustment that are reconciled. for such cases, the reference will be of the rate adjustment record */ select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh, ar_cash_receipt_history crh_rc where sr.reference_id = crh_rc.cash_receipt_history_id and sr.reference_type = 'RECEIPT' and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date and crh_rc.created_from = 'RATE ADJUSTMENT TRIGGER' and crh_rc.cash_receipt_id = cr.cash_receipt_id ) and /* not exists to filter out receipts reversed without remittance */ not exists ( select 1 from ar_cash_receipt_history_all crh2 where crh.cash_receipt_history_id = crh2.reversal_cash_receipt_hist_id and crh.status = 'REVERSED' and crh2.status = 'CONFIRMED' ) union all select 'RECEIPT' record_type, fnd_access_control_util.get_org_name(cr.org_id) operating_unit, cr.receipt_number transaction_number, crh.gl_date gl_date, cr.receipt_date effective_date, hz.party_name agent, arm.name payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, crh.status status, cr.currency_code currency, cr.amount transaction_amount, decode(:c_bank_curr_dsp,:c_gl_currency_code,crh.acctd_amount,crh.amount) accounted_amount, to_number(null) gl_end_balance, gla.ar_asset_ccid cash_account_ccid from ar_cash_receipts cr, ar_cash_receipt_history crh2, ar_cash_receipt_history crh, hz_cust_accounts cu, hz_parties hz, ar_receipt_methods arm, ce_bank_acct_uses_ou_v bau, ce_gl_accounts_ccid gla, ce_bank_accounts ba, ce_system_parameters sys where cr.cash_receipt_id = crh.cash_receipt_id and cr.remit_bank_acct_use_id = bau.bank_acct_use_id and bau.bank_account_id = :p_bank_account_id and bau.org_id = cr.org_id and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and bau.ar_use_enable_flag = 'Y' and gla.bank_acct_use_id = bau.bank_acct_use_id and crh.account_code_combination_id = gla.ar_asset_ccid and crh.status in('REMITTED', 'CLEARED') and crh.gl_date <= :c_as_of_date and crh.gl_date >= sys.cashbook_begin_date and crh.gl_posted_date is not null and crh.created_from <> 'RATE ADJUSTMENT TRIGGER' and crh2.cash_receipt_id = crh.cash_receipt_id and crh2.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id and crh2.status = 'REVERSED' and crh2.gl_date <= :c_as_of_date and crh2.gl_date >= sys.cashbook_begin_date and cu.cust_account_id(+) = cr.pay_from_customer and hz.party_id(+) = cu.party_id and arm.receipt_method_id = cr.receipt_method_id and (cr.status <> 'REV' or (cr.status = 'REV' and crh.reversal_gl_date > :c_as_of_date) ) and not exists ( select null from ce_statement_recon_gt_v sr, ce_statement_lines sl, ce_statement_headers sh where sr.reference_id = crh.cash_receipt_history_id and sr.reference_type = 'RECEIPT' and sr.status_flag = 'M' and sr.current_record_flag = 'Y' and sl.statement_line_id = sr.statement_line_id and sl.statement_header_id = sh.statement_header_id and sh.bank_account_id = :p_bank_account_id and sh.statement_date <= :c_as_of_date ) ), q_ap_payments as ( /* query 1: checks have not hit gl cash, but are reconciled. (query 1 of union) query 2: checks have hit gl cash, but are not reconciled. (query 2 of union) */ select 'PAYMENT' record_type, fnd_access_control_util.get_org_name(c.org_id) operating_unit, to_char(c.check_number) transaction_number, xle.event_date gl_date, c.check_date effective_date, c.vendor_name agent, c.payment_method_code payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, c.status_lookup_code status, c.currency_code currency, c.amount transaction_amount, decode(:c_bank_curr_dsp, :c_gl_currency_code,nvl(nvl(c.cleared_base_amount,c.base_amount),c.amount) ,c.amount ) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.ap_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ap_checks_all c, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_security_profiles_gt ou, ce_bank_accounts ba, ce_system_parameters sys, xla_transaction_entities trx, xla_events xle where c.check_date <= trunc(:c_as_of_date) + 1 - 1/24/60/60 and c.ce_bank_acct_use_id = bau.bank_acct_use_id and c.org_id = bau.org_id and bau.bank_account_id = :p_bank_account_id and gla.bank_acct_use_id = bau.bank_acct_use_id and bau.org_id = ou.organization_id and ou.organization_type = 'OPERATING_UNIT' and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and trx.application_id = 200 and trx.ledger_id = sys.set_of_books_id and nvl(trx.source_id_int_1,-99) = c.check_id and trx.entity_code = 'AP_PAYMENTS' and xle.application_id = trx.application_id and xle.entity_id = trx.entity_id and xle.event_type_code not in ('PAYMENT CANCELLED','REFUND CANCELLED') and xle.event_number = ( select max(event_number) from xla_events xe2 where xe2.application_id = xle.application_id and xe2.entity_id = xle.entity_id and xe2.event_date <= :c_as_of_date and xe2.event_date >= sys.cashbook_begin_date and xe2.event_status_code = 'P' and xe2.event_type_code not in ('PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED' ) ) and exists ( select /*+ push_subq no_unnest*/ 1 from ap_payment_history_all h2 where h2.check_id = c.check_id and h2.transaction_type like decode(c.void_date, null, h2.transaction_type,'%CANCEL%') ) and not exists ( select null from xla_ae_headers xeh, xla_ae_lines ael1 where xeh.event_id = xle.event_id and xeh.application_id = xle.application_id and ael1.application_id = xeh.application_id and xeh.entity_id = xle.entity_id and ael1.ae_header_id = xeh.ae_header_id and xeh.event_type_code not in ('PAYMENT UNCLEARED', 'PAYMENT CANCELLED', 'REFUND CANCELLED' ) and ael1.accounting_class_code = 'CASH' ) and /* check that payment is reconciled */ exists ( select null from ce_statement_reconcils_all csr, ce_statement_lines csl, ce_statement_headers csh where csr.reference_id = c.check_id and csr.current_record_flag = 'Y' and csr.reference_type = 'PAYMENT' and csr.status_flag = 'M' and csr.statement_line_id = csl.statement_line_id and csl.statement_header_id = csh.statement_header_id and csh.statement_date <= :c_as_of_date and csh.statement_date >= sys.cashbook_begin_date ) union all select 'PAYMENT' record_type, fnd_access_control_util.get_org_name(c.org_id) operating_unit, to_char(c.check_number) transaction_number, aeh.accounting_date gl_date, c.check_date effective_date, c.vendor_name agent, c.payment_method_code payment_method, null transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, c.status_lookup_code status, c.currency_code currency, c.amount transaction_amount, -1*decode(:c_bank_curr_dsp, :c_gl_currency_code,nvl(nvl(c.cleared_base_amount,c.base_amount),c.amount) ,c.amount ) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.ap_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ap_checks_all c, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_security_profiles_gt ou, ce_bank_accounts ba, ce_system_parameters sys, xla_transaction_entities trx, xla_ae_headers aeh where c.check_date <= trunc(:c_as_of_date) + 1 - 1/24/60/60 and c.ce_bank_acct_use_id = bau.bank_acct_use_id and c.org_id = bau.org_id and bau.bank_account_id = :p_bank_account_id and gla.bank_acct_use_id = bau.bank_acct_use_id and bau.org_id = ou.organization_id and ou.organization_type = 'OPERATING_UNIT' and bau.bank_account_id = ba.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id and nvl(trx.source_id_int_1,-99) = c.check_id and trx.entity_code = 'AP_PAYMENTS' and trx.application_id = 200 and trx.ledger_id = sys.set_of_books_id and aeh.entity_id = trx.entity_id and aeh.application_id = trx.application_id and aeh.application_id = 200 and /* fetch latest accounted event before as_of_date and check that it hits the gl cash account */ aeh.event_type_code in ('PAYMENT CLEARED', 'PAYMENT CREATED', 'REFUND RECORDED', 'PAYMENT MATURED' ) and aeh.ledger_id = sys.set_of_books_id and aeh.event_id = ( select max(event_id) from xla_events xe where xe.application_id = 200 and xe.entity_id = trx.entity_id and xe.event_number = ( select max(event_number) from xla_events xe2 where xe2.application_id = 200 and xe2.entity_id = xe.entity_id and xe2.event_date <= :c_as_of_date and xe2.event_date >= sys.cashbook_begin_date and xe2.event_status_code = 'P' and xe2.event_type_code not in ('PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED' ) ) ) and exists ( select 'x' from xla_ae_lines ael where aeh.ae_header_id = ael.ae_header_id and ael.application_id = 200 and ael.accounting_class_code = 'CASH' ) and exists ( select 1 from ap_payment_history_all h2 where h2.check_id = c.check_id and h2.transaction_type like decode(c.void_date, null, h2.transaction_type, '%CANCEL%') ) and /* check that payment is not reconciled */ not exists ( select /*+ push_subq no_unnest */ null from ce_statement_reconcils_all csr, ce_statement_lines csl, ce_statement_headers csh where csr.reference_id = c.check_id and csr.reference_type = 'PAYMENT' and csr.status_flag = 'M' and csr.current_record_flag = 'Y' and csr.statement_line_id = csl.statement_line_id and csl.statement_header_id = csh.statement_header_id and csh.statement_date <= :c_as_of_date ) ), q_cashflows as ( select 'CASHFLOW' record_type, null operating_unit, to_char(ca.cashflow_id) transaction_number, ch.accounting_date gl_date, ca.cashflow_date effective_date, nvl(xle.name,ca.customer_text) agent, null payment_method, trxn.transaction_sub_type_name transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, l1.meaning status, ca.cashflow_currency_code currency, ca.cashflow_amount transaction_amount, decode(ca.cashflow_direction,'RECEIPT',(-1),'PAYMENT',(1)) * nvl(ch.cleared_amount,ca.cashflow_amount) accounted_amount, to_number(null) gl_end_balance, coalesce(gla.xtr_asset_ccid,gla.asset_code_combination_id,ba.asset_code_combination_id) cash_account_ccid from ce_cashflows ca, ce_cashflow_acct_h ch, ce_bank_accounts ba, ce_bank_acct_uses_all bau, ce_gl_accounts_ccid gla, ce_trxns_subtype_codes trxn, xle_firstparty_information_v xle, ce_lookups l1, ce_system_parameters sys where ca.cashflow_bank_account_id = :p_bank_account_id and ca.cashflow_legal_entity_id = sys.legal_entity_id and ca.cashflow_id = ch.cashflow_id and ba.bank_account_id = ca.cashflow_bank_account_id and bau.bank_account_id = ca.cashflow_bank_account_id and bau.legal_entity_id = ca.cashflow_legal_entity_id and gla.bank_acct_use_id = bau.bank_acct_use_id and ca.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+) and ch.accounting_date >= sys.cashbook_begin_date and ca.counterparty_party_id = xle.party_id(+) and l1.lookup_type = 'CASHFLOW_STATUS_CODE' and l1.lookup_code = ca.cashflow_status_code and ca.cashflow_status_code = 'RECONCILED' and ch.event_id = ( select nvl(max(a.event_id),-1) from ce_cashflow_acct_h a where a.cashflow_id = ch.cashflow_id and trunc(a.accounting_date) <= :c_as_of_date ) and ch.event_type = decode(ca.source_trxn_type,'BAT','CE_BAT_CLEARED','STMT','CE_STMT_RECORDED') and ch.status_code = 'UNACCOUNTED' union select 'CASHFLOW' record_type, null operating_unit, to_char(ca.cashflow_id) transaction_number, ch.accounting_date gl_date, ca.cashflow_date effective_date, nvl(xle.name,ca.customer_text) agent, null payment_method, trxn.transaction_sub_type_name transaction_subtype, null je_name, to_number(null) je_line, null je_line_type, to_date(null) je_posted_date, null statement_number, to_number(null) statement_line, null statement_trx_type, l1.meaning status, ca.cashflow_currency_code currency, ca.cashflow_amount transaction_amount, decode(ca.cashflow_direction,'RECEIPT',(1),'PAYMENT' |