Search Results ece_ar_trx_lines_u2

Overview

EC.ECE_AR_TRX_LINES is an E-Business Suite (EBS) interface table that stores customer invoice line data used to support the outbound Invoice (810/INVOIC) EDI transaction. The table is owned by the EC (E-Commerce Gateway) schema and is created in the APPS_TS_INTERFACE tablespace with a PCT FREE of 10. Its rows are populated primarily from Oracle Receivables data held in RA_CUSTOMER_TRX_LINES, and the table carries invoice detail enriched with the 20-segment item number sourced from MTL_ITEM_FLEXFIELDS.

ECE_AR_TRX_LINES functions as the line-level staging area for outbound invoice processing. Each row corresponds to a single invoice line and is associated with exactly one row in ECE_AR_TRX_HEADERS or ECE_AR_TRX_HEADER_1, establishing a one-to-many relationship between headers and lines. Rows persist only long enough for the outbound data file to be written, after which they are purged. Columns suffixed "Int" (or labelled Internal) hold Oracle Applications internal values, while columns suffixed "Ext" (External) hold the equivalent external values obtained from ECE_XREF_DATA when code conversion is enabled for that column.

Based on the documented foreign-key and index structure, the object is best modeled as a standalone entity rather than a classic hub, link, or satellite. Where a Data Vault treatment is required, the narrow unique key TRANSACTION_RECORD_ID would serve as the natural business key around which a satellite could be anchored, but the ETRM metadata's heuristic classification is explicitly standalone.

Key Information Stored

The table is physically wide, with 245 documented columns in the 12.2.2 schema. The most operationally significant columns are:

• TRANSACTION_RECORD_ID — the unique key that links each row to its extension table, ECE_AR_TRX_LINES_X. It is the single column of unique index ECE_AR_TRX_LINES_U2, making it the principal documented business-key candidate.
• LINE_NUMBER — the column named in the documented primary key ECE_AR_TRX_LINES_PK; it identifies the invoice line within its header.
• TRANSACTION_ID, TRANSACTION_TYPE and COMMUNICATION_METHOD — identify the originating transaction and the delivery channel used by the EDI process.
• CREDITED_LINE_NUMBER, CREDIT_MEMO_REASON and INVOICE_LINE_TYPE — carry credit and adjustment context for the line.
• LINE_ITEM_NUMBER, CUSTOMER_ITEM_NUMBER, CUSTOMER_ITEM_DESC, ITEM_ID, ITEM_DESCRIPTION — the item identification and description attributes, including the supplier part number and customer part number.
• ORDERED_QUANTITY, QUANTITY, SHIPPED_QUANTITY, UOM_CODE and UOM_CODE_EXT1 — quantity and unit-of-measure values in internal and external form.
• UNIT_STANDARD_PRICE, UNIT_SELLING_PRICE and LINE_AMOUNT — pricing and extended amount for the line.
• SALES_ORDER_NUMBER, SALES_ORDER_LINE_NUMBER, SALES_ORDER_DATE and ORIGINAL_SYSTEM_LINE_REFERENCE — order references tying the invoice line back to the source order.
• RUN_ID, REQUEST_ID, PROGRAM_ID and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — identify the concurrent request and audit trail of the extraction.

Beyond these, the table is dominated by attribute groups: LINE_ITEM_ATTRIBUTE1–15, INTERFACE_LINE_ATTRIBUTE1–15, LINE_ATTRIBUTE1–15, HEADER_TP_ATTRIBUTE1–15, LINE_TP_ATTRIBUTE1–15, and INDUSTRY_ATTRIBUTE1–15, which provide trade-partner and industry-specific extension slots for the outbound mapping.

Common Use Cases and Queries

The primary use case is troubleshooting and auditing outbound Invoice (810/INVOIC) extraction. Because rows are purged after the data file is written, queries are typically run immediately after an extraction request to validate what was staged.

• Joining lines to headers to reconstruct a full invoice document before file generation.
• Detecting EDI mapping problems by comparing internal columns (for example, UOM_CODE) with their external counterparts (UOM_CODE_EXT1) to confirm code conversion via ECE_XREF_DATA.
• Confirming which concurrent request produced a given set of lines, using RUN_ID, REQUEST_ID and PROGRAM_ID.
• Reconciling staged line amounts and quantities against the Receivables source for audit purposes.

A representative query pattern joins the line table to its header table on the transaction identifier and filters by run:

• SELECT l.transaction_id, l.line_number, l.item_description, l.quantity, l.uom_code, l.uom_code_ext1, l.line_amount FROM ec.ece_ar_trx_lines l WHERE l.run_id = :run_id ORDER BY l.transaction_id, l.line_number;

For extension data, the query joins to ECE_AR_TRX_LINES_X on TRANSACTION_RECORD_ID, the documented linkage key and the column behind unique index ECE_AR_TRX_LINES_U2.

Related Objects

• ECE_AR_TRX_HEADERS / ECE_AR_TRX_HEADER_1 — the parent invoice header records; the one-to-many relationship is the defining association for this table.
• ECE_AR_TRX_LINES_X — the extension table, joined via TRANSACTION_RECORD_ID.
• ECE_XREF_DATA — supplies the external values that populate every "Ext" column when code conversion is enabled.
• RA_CUSTOMER_TRX_LINES — the principal source table in Oracle Receivables from which ECE_AR_TRX_LINES data is derived.
• MTL_ITEM_FLEXFIELDS — source of the 20-segment item flexfield value used in the line detail.
• ECE_AR_TRX_LINES_PK and ECE_AR_TRX_LINES_U2 — the primary-key constraint (LINE_NUMBER) and unique index (TRANSACTION_RECORD_ID) that govern access paths in the APPS_TS_INTERFACE tablespace.