Search Results dpp_transaction_lines_all_pk

Overview

DPP_TRANSACTION_LINES_ALL is the core line-level transaction table for Oracle Price Protection (DPP), a module within Oracle E-Business Suite designed to automate supplier price-change negotiations and customer remittance claims. In EBS 12.1.1 and 12.2.2, the table holds the detail rows for each Price Protection transaction, recording the prior and new supplier prices, inventory quantities, claim amounts, and the downstream update flags that determine which purchasing, costing, and price-list documents are refreshed or notified when a transaction is processed. The table is owned by the DPP schema and is documented as VALID in ETRM 12.2.2 with 72 physical columns.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification identifies DPP_TRANSACTION_LINES_ALL as standalone. This suggests treating it as an independent satellite-like structure keyed on its own surrogate identifier rather than as a hub or link with explicit parent relationships enforceable at the database level. Where strict Data Vault modeling is pursued, the transaction line should be decomposed with its foreign keys promoted into separate hub and link constructs.

Key Information Stored

The primary key is a single-column surrogate, TRANSACTION_LINE_ID, backed by the unique index DPP_TRANSACTION_LINES_ALL_PK. A second unique index, DPP_TRANSACTION_LINES_ALL_U1, is defined on the same column, reinforcing column uniqueness but offering no distinct business-key candidate; the true business key is contextual, combining TRANSACTION_HEADER_ID with LINE_NUMBER.

The most operationally significant columns include:

• TRANSACTION_HEADER_ID — foreign reference back to the parent transaction header.
• LINE_NUMBER — sequence of the line within the header.
• INVENTORY_ITEM_ID / SUPPLIER_PART_NUM — identifies the affected item.
• PRIOR_PRICE / SUPPLIER_NEW_PRICE / PRICE_CHANGE — price delta basis.
• CHANGE_TYPE / CHANGE_VALUE — categorical or absolute change parameters.
• ONHAND_INVENTORY / COVERED_INVENTORY / APPROVED_INVENTORY — quantity tiers used in claim calculation.
• CLAIM_AMOUNT — computed monetary recovery.
• UOM, LAST_CALCULATED_DATE, LAST_CALCULATED_BY — calculation audit trail.
• ORG_ID — multi-org operating unit context.
• SUPP_DIST_CLAIM_ID / SUPP_DIST_CLAIM_STATUS — linkage to supplier/distributor claim workflows.
• APPROVED_INVENTORY, APPROVED_INVENTORY, SUPPLIER_APPROVAL_DATE, SUPPLIER_APPROVED_BY — approval evidence.
• Flags such as UPDATE_PURCHASING_DOCS, NOTIFY_PURCHASING_DOCS, UPDATE_INVENTORY_COSTING, UPDATE_ITEM_LIST_PRICE govern downstream propagation.
• ATTRIBUTE1–ATTRIBUTE30 — flexfield extensibility.
• Standard WHO/audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical reporting includes claim reconciliation (summing CLAIM_AMOUNT by ORG_ID and period), price-change impact analysis (comparing PRIOR_PRICE against SUPPLIER_NEW_PRICE), and pending-approval tracking (filtering on SUPPLIER_APPROVAL_DATE IS NULL). A representative query joins the header to lines and restricts to a specific operating unit:

SELECT l.line_number, l.inventory_item_id, l.prior_price,
       l.supplier_new_price, l.claim_amount, l.supp_dist_claim_status
  FROM dpp_transaction_lines_all l
 WHERE l.transaction_header_id = :p_header_id
   AND l.org_id = :p_org_id
 ORDER BY l.line_number;

Another pattern aggregates downstream-impact flags to identify lines awaiting propagation:

SELECT org_id, COUNT(*), SUM(claim_amount)
  FROM dpp_transaction_lines_all
 WHERE update_inventory_costing = 'Y'
    OR update_item_list_price = 'Y'
 GROUP BY org_id;

Related Objects

The following objects are most relevant to DPP_TRANSACTION_LINES_ALL. Join on TRANSACTION_HEADER_ID for header context, on INVENTORY_ITEM_ID to master item views, on ORG_ID to operating units, and on the claim identifier columns for claim workflow tracking.

• DPP_TRANSACTION_HEADERS_ALL — parent header; join on TRANSACTION_HEADER_ID.
• MTL_SYSTEM_ITEMS_B — item master; join on INVENTORY_ITEM_ID.
• HR_ALL_ORGANIZATION_UNITS / ORG_ORGANIZATION_DEFINITIONS — operating unit context via ORG_ID.
• PO_HEADERS_ALL / PO_LINES_ALL — purchasing documents potentially updated by the propagation flags.
• MTL_MATERIAL_TRANSACTIONS — inventory activity supporting on-hand and covered quantities.
• CST_ITEM_COSTS — cost records affected by UPDATE_INVENTORY_COSTING.
• QP_LIST_HEADERS_B / QP_LIST_LINES — price lists affected by UPDATE_ITEM_LIST_PRICE and the pricelist notification flags.
• DPP_SUPP_DIST_CLAIMS (or equivalent claim entity) — referenced through SUPP_DIST_CLAIM_ID.

Because the object is classified as standalone under the heuristic Data Vault view, no declarative foreign keys are documented, so joins should be validated against the DPP transaction header and the standard inventory, purchasing, and pricing reference tables listed above.