Search Results tp_postal_code




Overview
Explore in AI Chat

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.

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.