Search Results ece_ar_trx_lines_pk

Overview

ECE_AR_TRX_LINES is an e-Commerce Gateway (EC) staging table in the Oracle E-Business Suite EC schema. It stores the customer invoice line-level data extracted from Oracle Receivables (AR) in preparation for the outbound Invoice transaction (ANSI X12 810 / UN/EDIFACT INVOIC). During outbound processing, the e-Commerce Gateway concurrent programs gather invoice data from Receivables, populate the EC staging tables, translate the rows into the target EDI or XML format, and then transmit them through the configured trading partner communication method. ECE_AR_TRX_LINES carries the detail rows that sit beneath the invoice header information, providing item, quantity, pricing, reference, and shipping attributes required by the outbound invoice document.

The table is documented with 245 columns, indicating a wide denormalized structure intended to hold every data element and trading-partner extension an outbound invoice line may require. From a heuristic Data Vault modeling perspective, the table is classified as standalone, with no foreign key relationships documented to parent vault structures. In practice this reflects its nature as a staging workload table rather than a normalized entity. Analysts designing a Data Vault representation would typically treat the row grain as a link candidate between an invoice transaction hub and an item hub, with the descriptive columns modeled as a satellite.

Key Information Stored

The table is uniquely identified by the primary key ECE_AR_TRX_LINES_PK on LINE_NUMBER, which serves as the surrogate key for each staged invoice line. A separate unique index, ECE_AR_TRX_LINES_U2, is defined on TRANSACTION_RECORD_ID, making it a documented business-key candidate that ties the line back to the transaction processing run.

The most significant columns fall into several groups:

• Transaction identity: TRANSACTION_ID, TRANSACTION_RECORD_ID, TRANSACTION_TYPE, COMMUNICATION_METHOD, and TRANSACTION_REFERENCE_KEY identify the originating invoice and the outbound transaction context.
• Line identity: LINE_NUMBER, LINE_ITEM_NUMBER, and CREDITED_LINE_NUMBER.
• Item data: ITEM_ID, ITEM_DESCRIPTION, CUSTOMER_ITEM_NUMBER, CUSTOMER_ITEM_DESC, and ORDERED_ITEM.
• Quantities and units: ORDERED_QUANTITY, QUANTITY, SHIPPED_QUANTITY, SHIPPING_QUANTITY, and UOM_CODE along with the UOM extension columns.
• Pricing: UNIT_STANDARD_PRICE, UNIT_SELLING_PRICE, and LINE_AMOUNT.
• Order references: SALES_ORDER_NUMBER, SALES_ORDER_LINE_NUMBER, SALES_ORDER_REVISION_NUMBER, SALES_ORDER_DATE, and SALES_CHANNEL.
• Trade partner extensions: LINE_TP_ATTRIBUTE1 through 15, HEADER_TP_ATTRIBUTE1 through 15, and LINE_ATTRIBUTE1 through 15 provide configurable extension fields.
• Audit and run control: RUN_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY.

Common Use Cases and Queries

Typical usage includes diagnosing outbound invoice extracts, verifying that lines were correctly gathered before translation, and reconciling Receivables invoice data against the transmitted EDI document. Because the table is populated per run, the RUN_ID and TRANSACTION_RECORD_ID columns are the primary filters for targeted investigation.

• Retrieving all lines for a given processing run: SELECT line_number, item_id, quantity, unit_selling_price FROM ec.ece_ar_trx_lines WHERE run_id = :run_id;
• Locating a specific invoice's staged lines: SELECT * FROM ec.ece_ar_trx_lines WHERE transaction_id = :invoice_id AND transaction_type = 'INVOICE';
• Reconciling header-to-line amounts and quantities by joining on TRANSACTION_RECORD_ID and LINE_NUMBER to the corresponding header staging table.
• Identifying lines that failed to translate by comparing populated rows against the outbound transmission log.

Related Objects

Although the documented relationship data classifies the table as standalone, the following objects participate in the same outbound invoice flow and join to this table through the shared transaction keys:

• ECE_AR_TRX_HEADERS — the corresponding header staging table, joined on TRANSACTION_ID and TRANSACTION_RECORD_ID.
• ECE_TP_HEADERS — trading partner header definitions, referenced by COMMUNICATION_METHOD and TRANSACTION_TYPE.
• RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL — the Receivables source tables from which invoice line data is gathered.
• ECE_810_OUT and the e-Commerce Gateway outbound output tables, populated during translation.
• ECE_AR_TRX_LINES_PK / ECE_AR_TRX_LINES_U2 — the primary and unique indexes enforcing row identity and deduplication on TRANSACTION_RECORD_ID.