Search Results edr_trans_ackn_u

Overview

The EDR_TRANS_ACKN table is a core E-Records (EDR) transactional object in Oracle E-Business Suite, owned by the EDR schema. Its documented purpose is to store transaction acknowledgement records for e-records — capturing the lifecycle events by which an e-record's transaction is confirmed, rejected, or otherwise dispositioned. In EBS 12.1.1 and 12.2.2, this table underpins the e-records acknowledgement workflow relied upon by regulated industries (pharmaceutical, food, aerospace) that require an electronic audit trail demonstrating that a transaction was received and acted upon.

The ETRM metadata assigns a heuristic Data Vault classification of standalone. In Data Vault modeling terms, this suggests the table behaves as an independent construct rather than a strict hub, link, or satellite dependent on other EDR entities. Its uniquely identifiable rows are anchored by the surrogate ACKN_ID plus a business-key composite — a pattern consistent with an autonomous event or acknowledgement store.

Key Information Stored

The table contains 11 documented columns. The most important are described below.

• ACKN_ID — Surrogate primary key, enforced via the EDR_TRANS_ACKN_P unique index. This is the technical identifier for each acknowledgement row.
• ERECORD_ID — Foreign reference to the e-record whose transaction is being acknowledged; combined with TRANSACTION_STATUS, it forms the business-key candidate EDR_TRANS_ACKN_U.
• TRANSACTION_STATUS — The acknowledged disposition state of the e-record transaction; paired with ERECORD_ID to guarantee one acknowledgement per status per record.
• ACKN_DATE — Timestamp at which the acknowledgement was recorded.
• ACKN_BY — Identity of the user or process that performed the acknowledgement.
• ACKN_NOTE — Free-text rationale, comment, or reference accompanying the acknowledgement.
• CREATED_BY, CREATION_DATE — Standard EBS WHO-column audit trail for row insertion.
• LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard WHO-column audit trail for the most recent row modification.

The presence of dual unique indexes (surrogate ACKN_ID and business composite ERECORD_ID + TRANSACTION_STATUS) is significant: it enforces that a given e-record cannot hold duplicate acknowledgements for the same status, protecting traceability integrity.

Common Use Cases and Queries

Typical usage centers on compliance reporting and workflow status monitoring. A standard query retrieves the acknowledgement history for a specific e-record:

SELECT ackn_id, erecord_id, transaction_status,
       ackn_date, ackn_by, ackn_note
FROM   edr.edr_trans_ackn
WHERE  erecord_id = :p_erecord_id
ORDER BY ackn_date DESC;

Auditors commonly request acknowledgements by status within a date window to prove timely disposition:

SELECT transaction_status, COUNT(*)
FROM   edr.edr_trans_ackn
WHERE  ackn_date BETWEEN :start_date AND :end_date
GROUP BY transaction_status;

Reverse-lookup by ACKN_BY supports segregation-of-duties reviews, while joins on ERECORD_ID feed e-record dashboards and escalation reports.

Related Objects

Because the metadata classifies this object as standalone, its documented external relationships are implicit through column semantics rather than declared foreign keys. The most relevant associated objects include:

• The e-record master table in the EDR schema, joined on ERECORD_ID, providing the transaction context being acknowledged.
• E-Records workflow and OAF pages that surface acknowledgement status to users.
• EBS concurrent programs that reconcile acknowledged versus outstanding transactions.
• Standard EBS audit/BI views that query CREATED_BY and LAST_UPDATE_DATE for change tracking.
• E-Records APIs (PL/SQL packages in the EDR schema) that insert acknowledgements, populating ACKN_ID via sequence.

Given the limited documented FK data, practitioners should validate relationships against the EDR schema's referential constraints in their specific 12.1.1 or 12.2.2 instance before building dependent reports.