CE Cleared Transactions – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cash Management · all Cash Management reports
Overview
Application: Cash Management Description: Transactions - Cleared Payment/Receipt Transactions
Provides equivalent functionality to the following standard Oracle Forms/Reports
- Cleared Transactions Report Applicable Templates: Pivot: Cleared Transactions Summary Pivot: Cleared Batches Summary
Source: Cleared Transactions Report (CEXCLEAR) DB package: CE_CEXCLEAR_XMLP_PKG (required to initialize security)
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Bank Name, Bank Branch, Bank Account Name, Bank Account Number, Transaction Type, Treasury Legal Entity, Payroll Business Group, AP/AR Operating Unit, Cleared Date From, Cleared Date To
Oracle EBS Tables Used
ap_checks_all, iby_payment_methods_vl, iby_payments_all, iby_pay_instructions_all, ce_bank_accts_gt_v, hz_parties, ce_bank_acct_uses_all, ce_security_profiles_gt, xle_entity_profiles, hr_all_organization_units, gl_code_combinations, ar_cash_receipts, ar_cash_receipt_history_all, ar_batches_all, ce_lookups, hz_cust_accounts, ar_payment_schedules_all, ar_receipt_methods, ce_801_reconciled_v, pay_pre_payments, pay_assignment_actions, pay_org_payment_methods_f, ce_801_eft_reconciled_v, ce_999_interface_v, ce_bank_branches_v, ce_185_reconciled_v, ce_cashflows, ce_cashflow_acct_h
Report Categories
Related Reports
CE Cash in Transit, CE Bank Statement and Reconciliation, IBY Payment Process Request Details, CE General Ledger Reconciliation, AP Supplier Upload, AR Customer Upload, AP Invoice Payments 11g, AR Miscellaneous Receipts, AP Invoice Payments
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | Example |
| Blitz Report™ XML Import | CE_Cleared_Transactions.xml |
| SQL Source | CE Cleared Transactions.sql |
| Full Report on Enginatics | www.enginatics.com/reports/ce-cleared-transactions/ |
Executive Summary
The CE Cleared Transactions report is a historical record of all payments and receipts that have successfully cleared the bank. It serves as the definitive proof of payment or receipt for audit and vendor inquiry purposes. By filtering on clearance dates, Treasury teams can analyze the actual timing of cash flows compared to their issuance dates, helping to refine float assumptions.
Business Challenge
Once a transaction clears the bank, it moves from “In Transit” to “Cleared.”
- Vendor Inquiries: Suppliers often ask, “Has check #1234 cleared yet?” This report provides the answer.
- Float Analysis: Understanding the average time it takes for a check to clear (e.g., 3 days vs. 7 days) is essential for accurate cash forecasting.
- Audit Evidence: Auditors require a list of cleared transactions to verify the bank reconciliation.
Solution
This report lists all transactions with a status of ‘CLEARED’ within a specified date range.
Key Features:
- Status Confirmation: Only includes transactions that have been matched to a bank statement line.
- Float Calculation: By comparing the Transaction Date to the Cleared Date, users can calculate the actual float days.
- Batch Summary: Option to summarize by batch for high-volume environments.
Architecture
The report queries the subledger tables (AP_CHECKS_ALL, AR_CASH_RECEIPTS) where the status is ‘CLEARED’ or ‘RECONCILED’.
Key Tables:
AP_CHECKS_ALL: Cleared payments.AR_CASH_RECEIPTS: Cleared receipts.CE_BANK_ACCOUNTS: The bank account.CE_STATEMENT_LINES: The bank statement line that cleared the transaction.
Impact
- Customer Service: Enables rapid response to vendor and customer payment status inquiries.
- Forecasting Refinement: Provides the historical data needed to calculate accurate float times.
- Audit Support: Generates the detailed transaction lists required for financial audits.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH