Results for “line_trx_type”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CE_RECONCILED_TRANSACTIONS_V is a public Oracle E-Business Suite view owned by the APPS schema in the Cash Management (CE) module. As its name and ETRM description indicate, it presents reconciled cash transactions—bank statement lines that have been matched to their corresponding system transactions within the bank reconciliation process. The view consolidates transaction, statement line, statement header, and bank account attributes into a single denormalized result set, allowing reconciliation activity to be reported on without joining the underlying operational tables directly.

In Oracle EBS 12.1.1 and 12.2.2, this object serves as a reporting and integration layer over the treasury reconciliation engine. Because Cash Management reconciliation data spans multiple base tables and is subject to version-specific reconciliation logic (the CE_101, CE_200, CE_222, CE_260, CE_801, and CE_999 family of views), CE_RECONCILED_TRANSACTIONS_V provides a stable, published interface that abstracts those internals from custom reports, extracts, and third-party integrations. It is commonly consumed by reconciliation dashboards, bank reconciliation inquiries, and downstream data warehouse loads.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the view as defined over a set of reconciliation views and packages:

• CE_101_RECONCILED_V, CE_200_RECONCILED_V, CE_222_RECONCILED_V, CE_260_RECONCILED_V, CE_260_CF_RECONCILED_V, CE_801_RECONCILED_V, CE_801_EFT_RECONCILED_V, and CE_999_RECONCILED_V — a family of per-format reconciled transaction views, each handling a specific bank statement or payment format (101, 200, 222, 260, 801, 999).
• CE_AUTO_BANK_CLEAR and CE_AUTO_BANK_MATCH — packages providing automatic clearing and matching logic, including the GET_INVERSE_RATE function referenced in the view text.
• FND_GLOBAL — the standard Oracle Application Object Library package supplying session context such as ORG_ID, USER_ID, and responsibility information.
• IBY_FD_EXTRACT_GEN_PVT — the Payments funds-disbursement extract generation package.
• PAY_CE_RECONCILIATION_PKG — the Payables reconciliation package that supports matching of payment transactions to bank statements.

The view text itself performs the selection of the columns listed below with inline comments numbering each expression, and applies DECODE logic against CE_AUTO_BANK_MATCH.GET_INVERSE_RATE to conditionally invert the exchange rate.

Key Columns

The view exposes over sixty columns. The most significant include:

• ROW_ID, MULTI_SELECT, STATEMENT_LINE_ID, STATEMENT_HEADER_ID — identifiers supporting the reconciliation user interface and multi-row selection.
• BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, BANK_BRANCH_ID, BANK_CURRENCY_CODE — bank account and branch identification.
• TRX_ID, TRX_TYPE, TRX_NUMBER, TRX_DATE, TRX_CURRENCY_TYPE, TYPE_MEANING — the matched system transaction.
• CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, ACTUAL_AMOUNT_CLEARED, COUNT_CLEARED — monetary and clearing amounts.
• GL_DATE, CLEARED_DATE, MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE, EXCHANGE_RATE — accounting dates and currency conversion.
• BANK_CHARGES, BANK_ERRORS, STATUS, STATUS_DSP, LINE_TYPE_MEANING, LINE_TRX_TYPE, CLEARING_TRX_TYPE — reconciliation status and adjustments.
• BATCH_NAME, BATCH_ID, AGENT_NAME, CUSTOMER_NAME, VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, REFERENCE_TYPE, REFERENCE_ID, REMITTANCE_NUMBER, CASH_RECEIPT_ID, RECEIPT_DATE — trading partner and source document details.
• STATEMENT_HEADER_NUMBER, STATEMENT_LINE_NUMBER, STATEMENT_LINE_DATE, STATEMENT_LINE_AMOUNT, STATEMENT_DOC_SEQUENCE_VALUE, CONTROL_END_BALANCE — statement-level context.
• CE_BANK_ACCT_USE_ID, ORG_ID, LEGAL_ENTITY_ID, APPLICATION_ID — multi-org and legal entity keys essential for row-level security.

Common Use Cases and Queries

Typical uses include reconciling reported statement activity to system transactions, generating reconciliation aging and exception reports, and feeding downstream treasury and general ledger extracts. Because the view is multi-org enabled, queries should constrain by ORG_ID where reporting across operating units is required.

Example — reconciled transactions for one bank account within a date range:

• SELECT TRX_NUMBER, TRX_DATE, TYPE_MEANING, AMOUNT, AMOUNT_CLEARED, STATUS_DSP, CLEARED_DATE, BANK_ACCOUNT_NAME
• FROM CE_RECONCILED_TRANSACTIONS_V
• WHERE BANK_ACCOUNT_ID = :p_bank_account_id
•   AND CLEARED_DATE BETWEEN :p_start AND :p_end
• ORDER BY CLEARED_DATE;

Example — unreconciled or partially cleared exception report (filtering on STATUS or AMOUNT_CLEARED <> AMOUNT) supports the identification of amounts requiring manual investigation. Example — a currency analysis joining EXCHANGE_RATE and EXCHANGE_RATE_TYPE supports revaluation and audit. Because the view draws on package functions, performance in large-volume reconciliation environments benefits from indexing on BANK_ACCOUNT_ID, STATEMENT_HEADER_ID, and TRX_ID, and from restricting the result set to a single operating unit.