Search Results dpp_transaction_lines_log_u1

Overview

DPP.DPP_TRANSACTION_LINES_LOG is a transactional history table in the Oracle E-Business Suite Price Protection (DPP) module. It stores the auditable record of activity at the Price Protection transaction line level, capturing the state of each line as it passes through the price protection workflow. The table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and its indexes are maintained in APPS_TS_TX_IDX. In Oracle EBS 12.1.1 and 12.2.2, DPP supports supplier price protection programs in which distributors and manufacturers reconcile inventory value changes, claims, and purchasing document updates when supplier prices change.

The documented physical schema lists 58 columns, indicating a deliberately wide history structure that preserves both the business values of a transaction line and a set of status flags describing downstream update and notification outcomes. Following Data Vault modeling heuristics mined from the foreign key structure, the object is classified as standalone, meaning it has no documented parent relationships. In Data Vault terms, it behaves most like a satellite, preserving descriptive history and status flags keyed by a surrogate log identifier rather than acting as a hub or link between business entities.

Key Information Stored

The primary key is DPP_TRANSACTION_LINES_LOG_PK, defined on LOG_ID, a NUMBER described as the Lead Log Identifier. A unique index, DPP_TRANSACTION_LINES_LOG_U1, is also defined on LOG_ID in the APPS_TS_TX_IDX tablespace. LOG_ID is therefore both the surrogate primary key and the sole documented business-key candidate; there is no separate natural key unique index on the line identifier, meaning TRANSACTION_LINE_ID carries no uniqueness guarantee within this log table.

Among the most important columns are:

• LOG_ID — surrogate primary key and unique index column; identifies each log entry.
• LOG_MODE — records the mode in which the log entry was written (for example, insert, update, or delete history).
• TRANSACTION_LINE_ID — the unique identifier of the underlying Price Protection transaction line being logged.
• SUPPLIER_PART_NUM and INVENTORY_ITEM_ID — identify the supplier part and inventory item affected; note INVENTORY_ITEM_ID is stored as VARCHAR2(150) rather than NUMBER, reflecting this history/interface context.
• PRIOR_PRICE, CHANGE_TYPE, and CHANGE_VALUE — capture pricing context, where CHANGE_TYPE holds values such as Increase Amount, Increase Percentage, New Price, Decrease Amount, or Decrease Percentage.
• ONHAND_INVENTORY, COVERED_INVENTORY, and APPROVED_INVENTORY — inventory quantities calculated in ERP and approved by the supplier for coverage.
• CLAIM_AMOUNT — the monetary claim amount associated with the line.
• MANUALLY_ADJUSTED — Y/N flag indicating manual adjustment.
• UPDATE_PURCHASING_DOCS, NOTIFY_PURCHASING_DOCS, UPDATE_INVENTORY_COSTING, and UPDATE_ITEM_LIST_PRICE — status flags for downstream update actions.
• NOTIFY_INBOUND_PRICELIST, NOTIFY_OUTBOUND_PRICELIST, and NOTIFY_PROMOTIONS_PRICELIST — notification status flags across price-list channels.
• SUPP_DIST_CLAIM_STATUS — supplier claim for distributor creation status.
• ORG_ID — operating unit, supporting multi-org data access; also stored as VARCHAR2(150).

The remaining documented columns are standard audit fields (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and thirty descriptive flexfield attribute columns (ATTRIBUTE_CATEGORY, ATTRIBUTE1 through ATTRIBUTE30).

Common Use Cases and Queries

This table is primarily used for audit, reconciliation, and dispute resolution. Analysts reconstruct the history of a price protection transaction line to determine whether purchasing documents, inventory costing, item list prices, and price lists were updated or notified successfully.

Typical retrieval by transaction line:

• SELECT log_id, log_mode, change_type, change_value, claim_amount, org_id FROM dpp.dpp_transaction_lines_log WHERE transaction_line_id = :line_id ORDER BY creation_date DESC;
• Status monitoring for unprocessed updates, for example filtering rows where any of the UPDATE_* or NOTIFY_* flags are not set to the expected value.
• Claim reporting aggregating CLAIM_AMOUNT by ORG_ID, supplier part, or item over a date range using CREATION_DATE.
• Detecting manual intervention via WHERE manually_adjusted = 'Y'.

Related Objects

The documented metadata classifies this object as standalone with no foreign key dependencies, so related objects are identified by shared business identifiers rather than enforced constraints:

• DPP_TRANSACTION_LINES — the operational parent line table, joined on TRANSACTION_LINE_ID.
• DPP_TRANSACTIONS / DPP_TRANSACTIONS_ALL — transaction header context for lines referenced here.
• MTL_SYSTEM_ITEMS_B — item validation via INVENTORY_ITEM_ID.
• PO_HEADERS_ALL / PO_LINES_ALL — purchasing documents subject to the UPDATE_ and NOTIFY_PURCHASING_DOCS flags.
• CST_ITEM_COSTS / MTL_CST_ITEM_COSTS — inventory costing impacted by UPDATE_INVENTORY_COSTING.
• QP_LIST_HEADERS_B — price list artifacts tied to the NOTIFY_*_PRICELIST flags.
• HR_OPERATING_UNITS — resolution of ORG_ID.

Because no FK constraints are documented, all joins are conventional business-key joins applied by the application and reporting layers.