Search Results dpp_transaction_lines_log

Overview

DPP_TRANSACTION_LINES_LOG is a history (audit) table owned by the DPP schema within Oracle Price Protection, a module that manages supplier price protection claims and related purchasing, inventory, and pricing adjustments. The table stores historical snapshots of Price Protection Transaction Line level detail. Each row captures the state of a transaction line at a point in time, recorded as a log entry rather than as a live transactional record. This design preserves the audit trail of price changes, claim amounts, inventory quantities, and downstream update/notification flags, allowing users to reconstruct how a transaction line evolved.

From a heuristic Data Vault modeling perspective mined from the FK structure, this object is classified as standalone, meaning no foreign key relationships were detected. That classification suggests it functions less like a link or hub within a normalized vault model and more like a self-contained satellite or history store, keyed only by its own surrogate LOG_ID.

Key Information Stored

The table contains 58 documented columns. The most significant are summarized below.

• LOG_ID — Surrogate primary key, enforced by DPP_TRANSACTION_LINES_LOG_PK and also carrying a unique index (DPP_TRANSACTION_LINES_LOG_U1). This is the sole business-key candidate documented and uniquely identifies each historical row.
• LOG_MODE — Indicates the mode or reason for the log entry (for example, insert, update, or deletion capture).
• TRANSACTION_LINE_ID — The transaction line whose history is being recorded; the principal link back to the live transaction line.
• INVENTORY_ITEM_ID / SUPPLIER_PART_NUM — Item identity by internal ID and supplier part number.
• PRIOR_PRICE / CHANGE_TYPE / CHANGE_VALUE — The prior price, the nature of the change, and the magnitude/value of the change.
• ONHAND_INVENTORY, COVERED_INVENTORY, APPROVED_INVENTORY — Quantities relevant to price protection eligibility and settlement.
• CLAIM_AMOUNT / SUPP_DIST_CLAIM_STATUS — Claim value and its supplier/distributor claim status.
• MANUALLY_ADJUSTED — Flag indicating whether the line was manually adjusted.
• UPDATE_PURCHASING_DOCS, NOTIFY_PURCHASING_DOCS, UPDATE_INVENTORY_COSTING, UPDATE_ITEM_LIST_PRICE, NOTIFY_INBOUND_PRICELIST, NOTIFY_OUTBOUND_PRICELIST, NOTIFY_PROMOTIONS_PRICELIST — Control flags governing downstream propagation of the change to purchasing, costing, item pricing, and price-list notification processes.
• ORG_ID — Operating unit identifier, supporting multi-org security.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS who-columns for audit.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–30 — Oracle's standard descriptive flexfield (DFF) columns for extensibility, not core business data.

Common Use Cases and Queries

Typical usage centers on audit and reconciliation reporting: reconstructing the price-change history for a transaction line, comparing claim amounts over time, and verifying which downstream updates or notifications were triggered.

• Retrieve the full history for a transaction line:

SELECT log_id, log_mode, transaction_line_id, prior_price,
       change_type, change_value, claim_amount, supp_dist_claim_status,
       creation_date, created_by
FROM   dpp.dpp_transaction_lines_log
WHERE  transaction_line_id = :line_id
ORDER BY creation_date;

• Report on claim status by operating unit:

SELECT org_id, supp_dist_claim_status,
       SUM(claim_amount) total_claim
FROM   dpp.dpp_transaction_lines_log
WHERE  creation_date BETWEEN :from_date AND :to_date
GROUP BY org_id, supp_dist_claim_status;

• Identify manual adjustments or notification activity by filtering on MANUALLY_ADJUSTED and the NOTIFY_*/UPDATE_* flags.

Related Objects

The metadata documents no foreign keys for this object, so no referential constraints are declared. In practical terms, the most relevant related objects are those referenced by shared identifier columns rather than enforced constraints:

• DPP_TRANSACTION_LINES — Joined via TRANSACTION_LINE_ID; the live counterpart of this history table.
• DPP_TRANSACTION_HEADERS — Parent transaction context, joined through the transaction line's parent header.
• MTL_SYSTEM_ITEMS_B — Joined via INVENTORY_ITEM_ID for item descriptions and attributes.
• EGP_SYSTEM_ITEMS_B / EGP_SYSTEM_ITEMS_VL — Item master views for reporting item details.
• MTL_ONHAND_QUANTITIES_DETAIL — Used to corroborate ONHAND_INVENTORY and related quantity columns.
• AP_SUPPLIERS / PO_VENDORS — Supplier context for claims and supplier part numbers.
• ORG_ORGANIZATION_DEFINITIONS / HR_OPERATING_UNITS — Resolve ORG_ID to an operating unit name.
• FND_USER — Resolves CREATED_BY and LAST_UPDATED_BY to user names for audit reports.

Because there are no declared foreign keys, joins rely on application-managed relationships via TRANSACTION_LINE_ID and ORG_ID. Reports should therefore validate orphan conditions and apply organization security carefully when querying across operating units.