Results for “ece_advo_headers”

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

Overview

ECE_ADVO_HEADERS is the inbound transaction status header table within the Oracle E-Business Suite e-Commerce Gateway (EC) module. It stores one row per inbound advice document processed through the gateway, capturing the identifying, trading partner, document classification, and processing-status attributes associated with that transaction. The table serves transactions in three EDI message categories: Invoice (810/INVOIC), Ship Notice/Manifest (856/DESADV), and Shipment and Billing Notice (857). In Oracle EBS 12.1.1 and 12.2.2, the object resides in the EC schema and is reported as VALID, with 49 documented columns in the ETRM 12.2.2 physical schema.

The table functions as the parent header for inbound advice data. Each header row anchors one or more line-level rows in the detail and interface tables. Under the heuristic Data Vault classification mined from its foreign key structure, ECE_ADVO_HEADERS is described as hub-leaning: the surrogate ADVICE_HEADER_ID behaves as a stable business hub key, while descriptive and audit attributes behave as satellite-style dependent data. This classification is a modeling suggestion derived from the FK topology, not a mandated design for the EBS schema.

Key Information Stored

The primary key is defined by the constraint ECE_ADVO_HEADERS_PK on the column ADVICE_HEADER_ID. A separate unique index, ECE_ADVO_HEADERS_U1, is also defined on ADVICE_HEADER_ID, making it the documented business-key candidate in addition to the surrogate identifier. The most significant columns fall into the following groups.

• Identity and linking: ADVICE_HEADER_ID (surrogate primary key), TP_HEADER_ID (trading partner header reference), RELATED_DOCUMENT_ID, and SECURITY_GROUP_ID, which references FND_SECURITY_GROUPS and enforces multi-org security scoping.
• Document classification: DOCUMENT_TYPE, DOCUMENT_CODE, and DOCUMENT_STANDARD, which identify the EDI transaction set and standard invoked by the inbound interface.
• Transaction control: TRANSACTION_DATE, TRANSACTION_CONTROL1, TRANSACTION_CONTROL2, and TRANSACTION_CONTROL3, which carry control numbers and dates used for reconciliation and duplicate detection.
• Processing status: EDI_PROCESSED_FLAG and EDI_PROCESS_DATE, which record whether the advice has been processed and when that processing occurred.
• Trading partner detail: TP_NAME, TP_CODE, TP_ENTITY_TYPE, TP_LOCATION_CODE_EXT, TP_ADDRESS1 through TP_ADDRESS4, TP_CITY, TP_STATE, TP_PROVINCE, TP_COUNTY, TP_POSTAL_CODE, and TP_COUNTRY, which preserve the partner address and location as received.
• Cross-references: EXTERNAL_REFERENCE1 through EXTERNAL_REFERENCE6 and INTERNAL_REFERENCE1 through INTERNAL_REFERENCE6, which map inbound identifiers to internal values.
• Audit and concurrency: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical use cases include monitoring inbound 810, 856, and 857 traffic, auditing unprocessed or failed advice headers, reconciling control numbers against transmitted documents, and joining header data to line detail for reporting.

Listing unprocessed headers for a document type:

• SELECT ADVICE_HEADER_ID, DOCUMENT_TYPE, TRANSACTION_DATE, TP_CODE FROM EC.ECE_ADVO_HEADERS WHERE EDI_PROCESSED_FLAG = 'N';

Joining headers to detail rows across the documented foreign key:

• SELECT h.ADVICE_HEADER_ID, h.DOCUMENT_TYPE, d.ADVICE_HEADER_ID FROM EC.ECE_ADVO_HEADERS h JOIN EC.ECE_ADVO_DETAILS d ON d.ADVICE_HEADER_ID = h.ADVICE_HEADER_ID;

Isolating headers within a security group:

• SELECT h.ADVICE_HEADER_ID, h.TRANSACTION_CONTROL1 FROM EC.ECE_ADVO_HEADERS h WHERE h.SECURITY_GROUP_ID = :security_group_id;

Related Objects

• ECE_ADVO_DETAILS — child table; joined on ECE_ADVO_DETAILS.ADVICE_HEADER_ID = ECE_ADVO_HEADERS.ADVICE_HEADER_ID.
• ECE_ADVO_DETAILS_INTERFACE — interface child table; joined on ECE_ADVO_DETAILS_INTERFACE.ADVICE_HEADER_ID = ECE_ADVO_HEADERS.ADVICE_HEADER_ID.
• FND_SECURITY_GROUPS — referenced by ECE_ADVO_HEADERS.SECURITY_GROUP_ID for multi-org security.
• ECE_ADVO_HEADERS_PK / ECE_ADVO_HEADERS_U1 — primary key constraint and unique index on ADVICE_HEADER_ID.