Search Results dpp_customer_claims_all_u2

Overview

DPP.DPP_CUSTOMER_CLAIMS_ALL is a transactional table in the Oracle EBS 12.1.1 and 12.2.2 E-Business Suite, owned by the DPP schema (the Channel Revenue Management / Price Protection module). It stores the customer inventory and claim information associated with a Price Protection transaction. When a supplier drops the price of an item, distributors and their end customers may be entitled to credits against on-hand inventory valued at the old price. This table captures, per customer and per inventory line, the reported and calculated inventory quantities, the old and new prices, and the resulting claim amounts owed to the customer by the distributor and to the distributor by the supplier.

The table is classified heuristically as standalone in the Data Vault model mined from its foreign-key structure. It does not function purely as a hub, link, or satellite; it behaves as a multi-source transaction fact, holding business keys (transaction header, inventory line), descriptive attributes (prices, currencies, UOM), and measure-like numeric fields (inventory and claim amounts) in a single relation. A Data Vault model would likely split this into a hub keyed on CUSTOMER_INV_LINE_ID, a link to the price protection transaction header, and one or more satellites for pricing and claim amounts.

Key Information Stored

The table is populated with 58 documented columns, including 30 generic ATTRIBUTE columns and standard WHO/audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER). The columns of greatest functional importance are:

• CUSTOMER_INV_LINE_ID — surrogate primary key (DPP_CUSTOMER_CLAIMS_ALL_PK) and the column underlying unique index DPP_CUSTOMER_CLAIMS_ALL_U1. It identifies the customer inventory detail line.
• TRANSACTION_HEADER_ID and LINE_NUMBER — together form the second business-key candidate, enforced by unique index DPP_CUSTOMER_CLAIMS_ALL_U2. TRANSACTION_HEADER_ID is the unique identifier for the Price Protection transaction; LINE_NUMBER is the customer inventory detail line number.
• CUST_ACCOUNT_ID — the customer account (stored as VARCHAR2(150)), foreign-keyed to HZ_CUST_ACCOUNTS.
• INVENTORY_ITEM_ID — the inventory item (stored as VARCHAR2(150)) to which the price protection applies.
• LAST_PRICE, SUPPLIER_NEW_PRICE, CUSTOMER_NEW_PRICE — the prior price, the supplier's new price, and the distributor's resale price to the customer.
• SUPPLIER_PRICE_DROP — the change in value for the item as provided by the supplier.
• REPORTED_INVENTORY and CALCULATED_INVENTORY — quantity declared by the customer versus quantity computed by the application; the variance is a key reconciliation control.
• CUST_CLAIM_AMT and SUPP_CLAIM_AMT — the claim amount owed to the customer and the supplier claim for that customer.
• TRX_CURRENCY and UOM — transactional currency and unit of measure.
• DEBIT_MEMO_NUMBER, CUSTOMER_CLAIM_ID, SUPP_CUST_CLAIM_ID — identifiers linking the row to debit memo and claim processing.
• CUSTOMER_CLAIM_CREATED and SUPPLIER_CLAIM_CREATED — flags/indicators of claim creation status.
• ORG_ID — operating unit, supporting multi-org security and partitioned reporting.

Common Use Cases and Queries

Typical usage centers on claim reconciliation, price protection reporting, and inventory variance analysis.

• Retrieve all claim lines for a price protection transaction:

  SELECT customer_inv_line_id, line_number, cust_account_id, inventory_item_id,
  reported_inventory, calculated_inventory, cust_claim_amt, supp_claim_amt
FROM dpp.dpp_customer_claims_all
WHERE transaction_header_id = :p_transaction_header_id
ORDER BY line_number;

• Identify lines where the customer's reported inventory exceeds the application's calculated inventory:

  SELECT * FROM dpp.dpp_customer_claims_all
WHERE calculated_inventory > reported_inventory
AND org_id = :p_org_id;

• Aggregate claim exposure by customer and item for a given operating unit:

  SELECT cust_account_id, inventory_item_id, SUM(cust_claim_amt) claim_total
FROM dpp.dpp_customer_claims_all
WHERE org_id = :p_org_id
GROUP BY cust_account_id, inventory_item_id;

• Track the difference between the supplier's claim and the customer's claim to gauge margin impact using CUST_CLAIM_AMT versus SUPP_CLAIM_AMT, or use the leverage of DPP_CUSTOMER_CLAIMS_ALL_N1 (CUST_ACCOUNT_ID) and DPP_CUSTOMER_CLAIMS_ALL_N2 (TRANSACTION_HEADER_ID, INVENTORY_ITEM_ID, CUST_ACCOUNT_ID, ORG_ID) for multi-attribute filtering.

Related Objects

• HZ_CUST_ACCOUNTS — referenced via DPP_CUSTOMER_CLAIMS_ALL.CUST_ACCOUNT_ID; join to obtain customer name and account details.
• DPP_CUSTOMER_CLAIMS_LOG — references this table through CUSTOMER_INV_LINE_ID, capturing audit/history records for claim line changes.
• DPP Price Protection transaction header — linked through TRANSACTION_HEADER_ID; the parent transaction owning each claim line.
• DPP customer inventory detail — the source of CUSTOMER_INV_LINE_ID and LINE_NUMBER.
• Inventory item master (MTL_SYSTEM_ITEMS_B) — join on INVENTORY_ITEM_ID to resolve item descriptions and attributes.
• Claim and debit memo processing entities — surfaced through CUSTOMER_CLAIM_ID, SUPP_CUST_CLAIM_ID, and DEBIT_MEMO_NUMBER.

The unique indexes DPP_CUSTOMER_CLAIMS_ALL_U1 and U2 confirm that each customer inventory line and each transaction/line-number combination is stored once, providing reliable join paths for these related objects.