Search Results ce_arch_interface_lines

Overview

CE_ARCH_INTERFACE_LINES is an archived data table in the Oracle E-Business Suite Cash Management (CE) module, owned by the CE schema. It stores historical copies of records originally held in CE_STATEMENT_LINES_INTERFACE, the staging table that receives bank statement line data before it is validated, processed, and reconciled in Cash Management. Once interface records are successfully consumed by the statement import process, or when they are purged from the active interface, rows can be moved into this archive table to preserve an audit trail of what was received from the bank without continuing to burden the operational interface table.

From a Data Vault modeling perspective, the heuristic mined from the foreign-key structure classifies this object as a link. This classification is a modeling suggestion rather than a mandatory design: the table records transactional relationship events (statement lines) that associate a header-level statement and bank account with a detail line, and it carries descriptive context around currency and transaction amounts. The archive table is multi-tenant neutral (schema CE, no operating unit column), and its status is VALID in both 12.1.1 and 12.2.2.

Key Information Stored

The table contains 38 documented columns in the 12.2.2 physical schema. The most operationally significant columns are outlined below.

• STATEMENT_NUMBER – business identifier of the bank statement to which the archived line belongs; part of the primary key.
• BANK_ACCOUNT_NUM – the bank account for the statement; part of the primary key.
• LINE_NUMBER – the sequence number of the line within the statement; part of the primary key.
• TRX_DATE – the bank transaction date, used for reconciliation and reporting timelines.
• TRX_CODE – the bank transaction code that categorizes the line (for example, debit versus credit).
• EFFECTIVE_DATE – the date the transaction takes effect for accounting purposes.
• AMOUNT – the statement line amount in the statement currency.
• CURRENCY_CODE – currency of the line; foreign key to FND_CURRENCIES.
• EXCHANGE_RATE, EXCHANGE_RATE_DATE, USER_EXCHANGE_RATE_TYPE – conversion factors and rate metadata for multi-currency statements.
• ORIGINAL_AMOUNT and CHARGES_AMOUNT – original line value and any bank charges applied.
• BANK_TRX_NUMBER – the bank's own transaction reference, useful for traceability back to bank feeds.
• TRX_TEXT and INVOICE_TEXT – free-form narrative and invoice/memo text supplied by the bank.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY – flexible descriptive fields used for extensibility and downstream mapping.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE – standard audit columns recording who inserted or last modified the archived row and when.

The surrogate primary key is CE_ARCHIVE_INTERFACE_LINES_PK1, defined as the composite of STATEMENT_NUMBER, BANK_ACCOUNT_NUM, and LINE_NUMBER. This composite effectively serves as the business-key candidate as well, since it uniquely identifies each statement line within a bank account.

Common Use Cases and Queries

Typical uses include audit and reconciliation reporting, troubleshooting of historical statement imports, and controlled purge programs that move aged rows out of the live interface. A representative query retrieves all archived lines for a given statement:

• SELECT line_number, trx_date, trx_code, amount, currency_code FROM ce_arch_interface_lines WHERE statement_number = :stmt AND bank_account_num = :acct ORDER BY line_number;
• Currency-aware reporting joins ce_arch_interface_lines.currency_code = fnd_currencies.currency_code.
• Header-level rollups join ce_arch_interface_lines.statement_number = ce_arch_interface_headers_all.statement_number AND ce_arch_interface_lines.bank_account_num = ce_arch_interface_headers_all.bank_account_num.

Related Objects

• CE_ARCH_INTERFACE_HEADERS_ALL – parent header archive; joined on STATEMENT_NUMBER and BANK_ACCOUNT_NUM.
• FND_CURRENCIES – reference table for CURRENCY_CODE validation and lookup.
• CE_STATEMENT_LINES_INTERFACE – the source operational table whose rows are archived here.
• CE_STATEMENT_HEADERS_INTERFACE – header-level counterpart of the source interface.
• CE_STATEMENT_LINES and CE_STATEMENT_HEADERS_ALL – reconciled statement tables that hold lines after import.