Search Results dpp_transaction_headers_log_pk

Overview

DPP_TRANSACTION_HEADERS_LOG is an audit and history table in the Oracle Price Protection (DPP) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. Its stated purpose is to store history information for changes made to the Price Protection Transaction Header, which is the parent record governing a price protection claim or agreement between a buying organization and a supplier. When transaction header attributes are created, updated, or otherwise modified, the corresponding pre-change or post-change image is written to this table, providing a chronological audit trail that the transactional table itself does not retain.

The table resides in the DPP schema and contains 52 documented columns. Its primary key is DPP_TRANSACTION_HEADERS_LOG_PK, defined on the single column LOG_ID, and a unique index DPP_TRANSACTION_HEADERS_LOG_U1 also exists on LOG_ID. Under the heuristic Data Vault classification mined from its foreign-key structure, the table is treated as standalone — it carries no enforced foreign keys to parent or child objects. From a modeling perspective this should be read as a suggestion rather than a rule: the table functionally behaves as a satellite or history/audit structure attached to the DPP transaction header, keyed by its own surrogate LOG_ID and correlated to the operational record through TRANSACTION_HEADER_ID. Because the history rows are deliberately decoupled from the referential integrity of the operational schema, historical records survive even if the live header is purged or archived.

Key Information Stored

The most significant columns fall into three groups: identity, transaction business data, and audit/descriptive attributes.

• LOG_ID — Surrogate primary key of the log row. This is the key generated for each historical entry and is the only column comprising both the PK (DPP_TRANSACTION_HEADERS_LOG_PK) and the unique index DPP_TRANSACTION_HEADERS_LOG_U1. It is not a business identifier.
• LOG_MODE — Indicates the nature of the change captured (for example, insert, update, or delete), enabling the audit trail to be interpreted by event type.
• TRANSACTION_HEADER_ID — The business-key candidate that links the history row back to the live Price Protection transaction header. This is the principal join column for reporting on a header's full change history.
• REF_DOCUMENT_NUMBER — The reference document number associated with the transaction at the time of the logged change, useful for tracing source documents.
• VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID — The supplier, supplier site, and supplier contact identifiers captured in the header image.
• CONTACT_EMAIL_ADDRESS and CONTACT_PHONE — Contact details recorded on the header at the point of the change.
• EFFECTIVE_START_DATE and DAYS_COVERED — Commercial terms of the price protection arrangement: when it takes effect and the coverage period.
• TRX_CURRENCY — Currency of the transaction header image.
• TRANSACTION_STATUS — Status of the header when the log entry was written, essential for reconstructing workflow progression.
• ORG_ID — Operating unit that owns the transaction, supporting multi-org reporting and security.
• LAST_REFRESHED_BY and LAST_REFRESHED_DATE — Track the last refresh performed against the underlying record.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — The standard EBS who-columns auditing the log row itself, distinct from the business change being recorded.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30 — The descriptive flexfield segments captured with the header image, preserved in the history row.

Common Use Cases and Queries

The primary use case is audit and change-history reporting: answering who changed a price protection transaction, when, and what the prior values were. A typical query reconstructs the history for a given header, most recent first:

• SELECT log_id, log_mode, transaction_status, effective_start_date, days_covered, trx_currency, last_updated_by, last_update_date FROM dpp.dpp_transaction_headers_log WHERE transaction_header_id = :p_header_id ORDER BY last_update_date DESC;
• Status-transition analysis, grouping by status to see how transactions progress: SELECT transaction_status, log_mode, COUNT(*) FROM dpp.dpp_transaction_headers_log WHERE org_id = :p_org GROUP BY transaction_status, log_mode;
• Supplier-centric reporting by filtering on VENDOR_ID or VENDOR_SITE_ID to trace all header changes affecting a supplier.
• Extract-change-data-capture style reconciliation by comparing the latest log image against the live header for a TRANSACTION_HEADER_ID.
• Flexfield audit by inspecting ATTRIBUTE_CATEGORY and ATTRIBUTE1–30 to determine when a descriptive flexfield value changed.

Because LOG_ID is unique and standalone, queries should always scope by TRANSACTION_HEADER_ID and ORG_ID rather than relying on implicit parent-child joins.

Related Objects

The relationship metadata classifies this table as standalone, so no enforced foreign keys exist; the significant relationships are functional rather than declarative.

• DPP_TRANSACTION_HEADERS — The live Price Protection transaction header; joined on TRANSACTION_HEADER_ID, which is the business-key candidate linking each log row to its operational parent.
• PO_VENDORS / AP_SUPPLIERS — Supplier master referenced by VENDOR_ID.
• PO_VENDOR_SITES_ALL / AP_SUPPLIER_SITES_ALL — Supplier site master referenced by VENDOR_SITE_ID.
• PO_VENDOR_CONTACTS / AP_SUPPLIER_CONTACTS — Contact master referenced by VENDOR_CONTACT_ID.
• FND_CURRENCIES — Currency validation for TRX_CURRENCY.
• HR_OPERATING_UNITS / ORG_ORGANIZATION_DEFINITIONS — Operating unit context for ORG_ID.
• FND_USER — Resolves CREATED_BY, LAST_UPDATED_BY, and LAST_REFRESHED_BY to application users.

No PL/SQL APIs or views specific to this log table are documented in the ETRM metadata; access is expected to be read-only reporting against the DPP schema.