Results for “ece_advo_details_pk”

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

Overview

ECE_ADVO_DETAILS is a transactional detail table owned by the EC schema in Oracle E-Business Suite, belonging to the e-Commerce Gateway product. It stores the line-level status detail for inbound transactions processed through the gateway, specifically covering Invoice (810/INVOIC), Ship Notice/Manifest (856/DESADV), and Shipment and Billing Notice (857) message types. In EBS 12.1.1 and 12.2.2, this table records the disposition of each incoming advice record, indicating whether processing succeeded or failed and capturing any diagnostic messages generated during translation and import.

The table carries a foreign key to ECE_ADVO_HEADERS through ADVICE_HEADER_ID, and a foreign key to FND_SECURITY_GROUPS through SECURITY_GROUP_ID. Based on the mined foreign-key structure, the object leans toward a satellite classification in Data Vault modeling terms: it is descriptive, event-dated content hanging off the parent advice header hub. Analysts designing a warehouse layer should treat ECE_ADVO_DETAILS as a dependent satellite of its header, keyed by the parent advice identifier.

Key Information Stored

The table contains 30 documented columns. The following are the most operationally significant:

• ADVICE_DETAIL_ID — surrogate primary key of the row; also the sole documented unique index, ECE_ADVO_DETAILS_U1, making it the candidate business key at the detail grain.
• ADVICE_HEADER_ID — foreign key to ECE_ADVO_HEADERS, linking each detail line to its parent inbound advice.
• ADVO_STATUS_CODE — the processing status of the detail record, the central control column for error triage.
• ADVO_DATE_TIME — timestamp at which the advice detail status was recorded.
• ADVO_MESSAGE_CODE and ADVO_MESSAGE_DESC — the diagnostic code and human-readable description associated with the status.
• ADVO_DATA_BAD and ADVO_DATA_GOOD — flags capturing the quality classification of the imported data.
• EXTERNAL_REFERENCE1 through EXTERNAL_REFERENCE6 — the six external reference columns that carry trading-partner-supplied identifiers, typically document numbers and line identifiers from the source document.
• INTERNAL_REFERENCE1 through INTERNAL_REFERENCE6 — the matching internal reference columns, used to store EBS-side identifiers and correlation values.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE — concurrent request metadata identifying the program that generated or last updated the row.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, used for multi-organization security enforcement.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — standard EBS Who columns for auditability.

Common Use Cases and Queries

Typical usage centers on reconciliation, error investigation, and gateway throughput reporting. A frequent pattern joins the detail to its header to obtain the transaction type and trading partner context, then filters on status:

• Error triage: select rows where ADVO_STATUS_CODE indicates a failure, joining to ECE_ADVO_HEADERS to retrieve sender and document context, and displaying ADVO_MESSAGE_CODE and ADVO_MESSAGE_DESC.
• Throughput reporting: aggregate counts by status and by date using ADVICE_DETAIL_ID as the counting grain, grouped against ADVO_DATE_TIME.
• Data quality monitoring: count rows flagged by ADVO_DATA_BAD versus ADVO_DATA_GOOD to assess inbound data reliability by partner.
• Concurrent program auditing: group by REQUEST_ID and PROGRAM_ID to attribute processing outcomes to a specific e-Commerce Gateway run.
• Reference correlation: match EXTERNAL_REFERENCE against INTERNAL_REFERENCE to trace a partner-supplied line identifier back to the corresponding EBS document.

Related Objects

• ECE_ADVO_HEADERS — parent table; joined on ECE_ADVO_DETAILS.ADVICE_HEADER_ID = ECE_ADVO_HEADERS.ADVICE_HEADER_ID.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID for security group enforcement.
• ECE_ADVO_DETAILS_PK — primary key constraint on ADVICE_DETAIL_ID.
• ECE_ADVO_DETAILS_U1 — unique index on ADVICE_DETAIL_ID, the documented business-key candidate.
• The EC e-Commerce Gateway concurrent programs and inbound processing APIs that read invoice and shipment advice status detail during translation and import.