Search Results ece_ar_trx_headers_u1

Overview

EC.ECE_AR_TRX_HEADERS is an E-Business Suite interface table that holds outbound Oracle Receivables customer invoice header data used to support the outbound Invoice (810/INVOIC) EDI transaction in Oracle E-Commerce Gateway. The table is owned by the EC schema and resides in the APPS_TS_INTERFACE tablespace with a PCT Free of 10. It is populated primarily from Oracle Receivables source tables, including RA_CUSTOMER_TRX, RA_CUSTOMERS, RA_ADDRESSES, RA_CONTACTS, RA_TERMS, and RA_TERMS_LINE, and serves as the staging area from which invoice header information is flattened, code-converted, and written to the outbound EDI data file.

The object carries a 274-column physical schema in ETRM 12.2.2 and is explicitly described as transient: each row in ECE_AR_TRX_HEADERS is purged after the data is written to the data file. Column naming follows a consistent internal/external convention — columns suffixed with "Int" or "Internal" hold Oracle Applications internal values, while those suffixed with "Ext" or "External" hold the equivalent external value sourced from ECE_XREF_DATA when code conversion is enabled for the column.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone, with no derived hub, link, or satellite role assigned by the mined foreign-key structure. It is best treated as a transient staging/interface entity rather than a persistent modeled entity, since rows are not retained beyond the outbound extract cycle.

Key Information Stored

The table stores invoice header identity, trading partner routing, party address blocks, amounts, and payment terms. The most significant columns are:

• TRANSACTION_RECORD_ID — unique key that links the header row to its corresponding extension table ECE_AR_TRX_HEADERS_X; enforced by unique index ECE_AR_TRX_HEADERS_U2.
• TRANSACTION_ID — the Receivables internal transaction identifier; paired with COMMUNICATION_METHOD in unique index ECE_AR_TRX_HEADERS_U1.
• COMMUNICATION_METHOD — routing method for the outbound transaction; forms part of the U1 business-key candidate along with TRANSACTION_ID.
• TP_DOCUMENT_ID — trading partner document identifier, described in the relationship data as the primary key reference used by dependent tables; also the PK column label "TC_DOCUMENT_ID (TP_DOCUMENT_ID)".
• DOCUMENT_TYPE / TP_DOCUMENT_CODE / TP_DOCUMENT_PURPOSE_CODE / TP_TRANSLATOR_CODE — document classification and translation metadata for the originating transaction.
• TRANSACTION_NUMBER, CREDITED_INVOICE_NUMBER, REFERENCE_INVOICE_NUMBER, PARENT_INVOICE_NUMBER — invoice identification and cross-reference numbers, including credit memo and parent references.
• BILL_TO_*, SHIP_TO_*, SOLD_TO_*, REMIT_TO_* address and customer blocks — party name, address lines, city, postal code, country/state/province with Ext1 counterparts, contact name, SIC code, and sales channel.
• TOTAL_AMOUNT_DUE, AMOUNT_TAX_DUE, AMOUNT_FREIGHT_DUE, AMOUNT_LINE_ITEMS_DUE, AMOUNT_CHARGES_DUE — invoiced amounts broken out by tax, freight, line items, and charges.
• CURRENCY_CODE, CURRENCY_EXCHANGE_RATE, BASE_CURRENCY_CODE — currency and conversion context for the invoice.
• PAYMENT_TERM_NAME and TERM_DUE_* / DISCOUNT_* columns — payment terms header and installment-level due and discount schedules.
• SHIPMENT_DATE, DUE_DATE, TRANSACTION_DATE, INV_TRANSACTION_DATE — key dates for the outbound invoice document.
• RUN_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — concurrent program and run attribution for the extract.

The surrogate unique identity is TRANSACTION_RECORD_ID (ECE_AR_TRX_HEADERS_U2); the composite business-key candidate is COMMUNICATION_METHOD + TRANSACTION_ID (ECE_AR_TRX_HEADERS_U1).

Common Use Cases and Queries

Typical use is diagnosing why an outbound 810/INVOIC invoice header failed validation or was excluded from the extract.

• Locate a specific invoice header staged for the run:
  SELECT TRANSACTION_RECORD_ID, TRANSACTION_ID, TRANSACTION_NUMBER, CURRENCY_CODE, TOTAL_AMOUNT_DUE FROM EC.ECE_AR_TRX_HEADERS WHERE TRANSACTION_NUMBER = :invoice_num;
• Join header to its extension attributes:
  SELECT h.TRANSACTION_RECORD_ID, h.TRANSACTION_NUMBER, x.* FROM EC.ECE_AR_TRX_HEADERS h, EC.ECE_AR_TRX_HEADERS_X x WHERE h.TRANSACTION_RECORD_ID = x.TRANSACTION_RECORD_ID;
• Verify the unique keys are intact after a rerun:
  SELECT COMMUNICATION_METHOD, TRANSACTION_ID, COUNT(*) FROM EC.ECE_AR_TRX_HEADERS GROUP BY COMMUNICATION_METHOD, TRANSACTION_ID HAVING COUNT(*) > 1;
• Troubleshoot extract scope by run:
  SELECT RUN_ID, COUNT(*) FROM EC.ECE_AR_TRX_HEADERS GROUP BY RUN_ID;

Because each row is purged after the data file is written, the table is generally populated only during an active or recently completed extract. Query results are therefore most meaningful immediately after the outbound Invoice concurrent program runs; persisting this data for historical reporting requires an external snapshot.

Related Objects

The following objects are the most significant dependents and source relationships documented for this table:

• EC.ECE_AR_TRX_HEADERS_X — extension table for the header, joined on TRANSACTION_RECORD_ID.
• EC.ECE_ADVO_HEADERS_INTERFACE — references ECE_AR_TRX_HEADERS via TP_DOCUMENT_ID; used in advance shipment/invoice document flows.
• EC.ECE_MVSTO_HEADERS — references ECE_AR_TRX_HEADERS via TP_DOCUMENT_ID; movement/stock transfer header linkage.
• EC.ECE_XREF_DATA — supplies converted external values for all Ext columns when code conversion is enabled.
• RA_CUSTOMER_TRX / RA_CUSTOMERS / RA_ADDRESSES / RA_CONTACTS / RA_TERMS / RA_TERMS_LINE — the primary Oracle Receivables source tables from which this header staging data is derived.

Tuning and purge behavior are governed by the APPS_TS_INTERFACE tablespace and the standard E-Commerce Gateway outbound extract machinery, which controls insertion, code conversion, file generation, and subsequent row removal.