Results for “ece_advo_headers_u1”

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

Overview

EC.ECE_ADVO_HEADERS is an Oracle E-Business Suite inbound EDI transaction table that stores status and error information reported by Oracle Payables and Oracle Purchasing for Electronic Data Interchange processing. In Release 11i and continuing through Oracle EBS 12.1.1 and 12.2.2, the table captures header-level data for inbound Invoice (810/INVOIC), Ship Notice/Manifest (856/DESADV), and Shipment and Billing Notice (857) transactions. It records the trading partner involved, whether an error occurred during the inbound transaction or transaction type, and the date the EDI transaction was processed. The table was architected to accommodate additional EDI transactions introduced in future releases.

Rows are inserted by the Payables Open Interface Import concurrent program for all inbound Invoice errors and by the Receiving Open Interface in Oracle Purchasing for all inbound Ship Notice/Manifest and Shipment and Billing Notice errors. The object is owned by the EC schema, resides in the APPS_TS_TX_DATA tablespace, and holds 49 documented columns in ETRM 12.2.2. Following Data Vault modeling heuristics mined from its foreign key structure, ECE_ADVO_HEADERS is classified as hub-leaning: ADVICE_HEADER_ID is a system-generated surrogate key unique to each transaction attempt, while reference and trading partner attributes behave as descriptive satellite data. This classification is a modeling suggestion rather than a prescribed design.

Key Information Stored

The table's surrogate primary key is ADVICE_HEADER_ID, enforced by unique index ECE_ADVO_HEADERS_U1 (type NORMAL, uniqueness UNIQUE, tablespace APPS_TS_TX_IDX). All other columns are descriptive or foreign key references rather than declared business keys. The most significant attributes are:

• ADVICE_HEADER_ID — System-generated primary key, invisible to end users.
• TP_HEADER_ID — Trading partner header identifier linking to the EDI trading partner definition.
• DOCUMENT_TYPE / DOCUMENT_CODE / DOCUMENT_STANDARD — Identify the EDI document and standard (e.g., 810, 856, 857) and its governing standard (ANSI X12, EDIFACT).
• TRANSACTION_DATE — Date the inbound transaction was reported.
• EDI_PROCESSED_FLAG / EDI_PROCESS_DATE — Whether the transaction was processed and when.
• EXTERNAL_REFERENCE1–6 / INTERNAL_REFERENCE1–6 — Up to six external and six internal values that uniquely identify the EDI document; internal values are Oracle Applications internal equivalents sourced from ECE_XREF_DATA when code conversion is enabled.
• TP_CODE, TP_NAME, TP_LOCATION_CODE_EXT, TP_ENTITY_TYPE, TP_ADDRESS1–4, TP_CITY, TP_STATE, TP_PROVINCE, TP_POSTAL_CODE, TP_COUNTRY, TP_COUNTY — Trading partner identification and address attributes.
• RELATED_DOCUMENT_ID — Identifier of the related Oracle document (e.g., invoice or receipt).
• COMMUNICATION_METHOD / TRANSACTION_CONTROL1–3 — Transmission method and control reference values.
• AUDIT COLUMNS — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, SECURITY_GROUP_ID (foreign key to FND_SECURITY_GROUPS).

Common Use Cases and Queries

Typical queries surface unprocessed or failed inbound EDI documents for troubleshooting and reconciliation. A frequent pattern filters on the processing flag and date range across a trading partner:

• Identifying all failed invoices by partner: SELECT ADVICE_HEADER_ID, TP_CODE, TP_NAME, DOCUMENT_TYPE, TRANSACTION_DATE FROM EC.ECE_ADVO_HEADERS WHERE EDI_PROCESSED_FLAG = 'N' AND DOCUMENT_TYPE = '810'.
• Locating documents via trading partner references: join on TP_HEADER_ID or filter EXTERNAL_REFERENCE1 for the partner's control number.
• Reconciliation reporting: aggregate counts by DOCUMENT_TYPE, EDI_PROCESSED_FLAG, and TRANSACTION_DATE to monitor inbound volume and error rates.
• Audit and audit trail: combine REQUEST_ID and PROGRAM_ID with concurrent program history to trace which interface populated a given row.
• Detailed error drill-down: join to ECE_ADVO_DETAILS on ADVICE_HEADER_ID for line-level failure messages.

Related Objects

The following objects reference or depend on ECE_ADVO_HEADERS and are most significant for joins and integration:

• EC.ECE_ADVO_DETAILS — References ECE_ADVO_HEADERS via ADVICE_HEADER_ID; holds line-level error detail for each header.
• EC.ECE_ADVO_DETAILS_INTERFACE — Interface-side detail mirroring the same foreign key, populated during open interface processing.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID, enforcing Oracle Applications security group isolation.
• ECE_XREF_DATA — Source of external-to-internal code conversions for the Ext/Int-referenced columns.
• Oracle Payables Open Interface Import — Concurrent program that populates the table for Invoice (810) errors.
• Receiving Open Interface (Oracle Purchasing) — Concurrent program that populates the table for 856/DESADV and 857 errors.
• EC.ECE_TP_HEADERS (trading partner setup) — Joined via TP_HEADER_ID to resolve partner metadata.