Search Results dpp_transaction_claims_all




Overview
Explore in AI Chat

Overview

DPP_TRANSACTION_CLAIMS_ALL is a transactional table in the Oracle Price Protection (DPP) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It serves as the association table that links Trade Management claims to Price Protection transactions, enabling the Price Protection process to reconcile supplier claim activity against the underlying transaction recorded by the pricing engine. The documentation states plainly: "This table will link the Trade Management claims to Price Protection Transaction." The table is owned by the DPP schema and resides in the Oracle Price Protection product family.

From a data-modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the table has no outbound foreign keys to other DPP entities. In practice, the composite primary key (TRANSACTION_HEADER_ID, CLAIM_ID) is characteristic of a link (association) table, joining two business entities: a Price Protection transaction header and a Trade Management claim. Project teams should therefore treat it as a link candidate, even though the FK metadata classifies it as standalone.

Key Information Stored

The documented physical schema in ETRM 12.2.2 contains 11 columns. The two most important columns are the composite primary key members:

  • TRANSACTION_HEADER_ID — Identifies the Price Protection transaction header to which the claim is attached. This is the principal link to the DPP transaction hierarchy.
  • CLAIM_ID — Identifies the Trade Management claim. Together with TRANSACTION_HEADER_ID, it forms the primary key DPP_TRANSACTION_CLAIMS_ALL_PK and the unique index DPP_TRANSACTION_CLAIMS_ALL_U1 (TRANSACTION_HEADER_ID, CLAIM_ID), which is the business-key candidate for this table.
  • CLAIM_TYPE — Classifies the nature of the linked claim, allowing queries to distinguish claim variants associated with a transaction.
  • APPROVED_BY_SUPPLIER — A status flag indicating whether the supplier has approved the claim; essential for approval-driven reporting and reconciliation.
  • ORG_ID — The operating unit that owns the record, providing multi-org (MOAC) partitioning for secure reporting.
  • OBJECT_VERSION_NUMBER — The optimistic-locking column used by the Oracle Application Object Library for concurrent updates.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO (audit) columns capturing who created and last modified each row and when.

Note there is no separate surrogate sequence key; the business key itself is the primary key, which simplifies joins to both parent entities.

Common Use Cases and Queries

Typical use cases include reconciliation of price protection claims against transactions, supplier-approval status tracking, and multi-org claim reporting. A basic query to retrieve claims for a transaction might be:

  • Claim lookup by transaction — SELECT claim_id, claim_type, approved_by_supplier FROM dpp_transaction_claims_all WHERE transaction_header_id = :p_header_id;
  • Approval status reporting — Filter approved_by_supplier to list claims pending or completed supplier approval, grouped by org_id for MOAC-safe reporting.
  • Concurrent-safe updates — Any DML should include WHERE object_version_number = :p_ovn to honor optimistic locking.
  • Audit trail analysis — Use the WHO columns to identify records created or modified by specific users during a period.

Because both key columns are indexed via the unique index, lookups by transaction or by claim are efficient; queries driven solely by CLAIM_ID may benefit from an additional index depending on volume.

Related Objects

Although the mined relationship data classifies this table as standalone, the composite key columns imply logical relationships to the following objects:

Applications should join through TRANSACTION_HEADER_ID and CLAIM_ID rather than relying on enforced foreign keys, since the documented schema does not list them.