Results for “related_modifier_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_RELATED_DEAL_LINES is an Oracle Marketing (AMS) schema table that supports Trade Deal Offers. A trade deal offer line can carry two modifiers within a single deal line, and this table captures the relationship between those two modifier lines. The columns MODIFIER_ID and RELATED_MODIFIER_ID are foreign keys to LIST_LINE_ID in QP_LIST_LINES, establishing the association that links a primary modifier to its related modifier within the pricing and trade deal infrastructure.

From a Data Vault modeling perspective, this object is best classified heuristically as a link table. It does not describe an entity in its own right; rather it resolves a relationship between two records in the same parent table (QP_LIST_LINES), functioning as a self-referencing association link. The table also carries descriptive and estimative attributes (estimated quantity and amount), so a hybrid hub-link-satellite treatment may be appropriate when modeling this structure for downstream analytics.

Key Information Stored

The documented physical schema contains 16 columns. The most significant are:

• RELATED_DEAL_LINES_ID — Surrogate primary key, enforced by AMS_RELATED_DEAL_LINES_PK and mirrored by the unique index AMS_RELATED_DEAL_LINES_U1.
• MODIFIER_ID — Foreign key to QP_LIST_LINES.LIST_LINE_ID; identifies the primary modifier line in the deal.
• RELATED_MODIFIER_ID — Foreign key to QP_LIST_LINES.LIST_LINE_ID; identifies the modifier line related to MODIFIER_ID.
• QP_LIST_HEADER_ID — Reference to the owning price list header, anchoring the modifiers to their parent trade deal.
• ESTIMATED_QTY and ESTIMATE_QTY_UOM — Estimated quantity and its unit of measure for the related deal line.
• ESTIMATED_QTY_IS_MAX and ESTIMATED_AMOUNT_IS_MAX — Flags indicating whether the estimated quantity or amount represents a maximum threshold.
• ESTIMATED_AMOUNT — Estimated monetary value associated with the related line.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, providing multi-org/security group isolation.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications Framework.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN capture standard who-column auditing.

The only documented business-key candidate is RELATED_DEAL_LINES_ID, which appears in both the primary key constraint and the unique index; no other unique business key is defined in the metadata.

Common Use Cases and Queries

Typical reporting scenarios involve resolving the two sides of a trade deal association to present offer pairings, estimated volumes, and estimated values. A common query pattern joins the table twice to QP_LIST_LINES to retrieve descriptive attributes for both modifiers:

• Pairing report — join MODIFIER_ID to QP_LIST_LINES.LIST_LINE_ID aliased as the primary modifier and RELATED_MODIFIER_ID to the same column with a second alias for the related modifier.
• Header rollup — join QP_LIST_HEADER_ID to QP_LIST_HEADERS to report related line pairings per trade deal or price list.
• Estimation analysis — aggregate ESTIMATED_QTY and ESTIMATED_AMOUNT by QP_LIST_HEADER_ID, filtering on ESTIMATED_QTY_IS_MAX or ESTIMATED_AMOUNT_IS_MAX.
• Security-scoped extraction — filter by SECURITY_GROUP_ID when extracting data for a specific operating unit or security context.
• Change tracking — order by LAST_UPDATE_DATE to identify recently modified deal line relationships for incremental ETL.

Related Objects

The most significant objects in the dependency chain are:

• QP_LIST_LINES — Referenced twice through MODIFIER_ID and RELATED_MODIFIER_ID, both to LIST_LINE_ID. This is the central parent table for the modifier relationship.
• QP_LIST_HEADERS — Parent of the price list that owns the modifier lines; reachable through QP_LIST_HEADER_ID.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID for access control partitioning.
• AMS_RELATED_DEAL_LINES_PK / AMS_RELATED_DEAL_LINES_U1 — Primary key constraint and unique index on RELATED_DEAL_LINES_ID.
• AMS trade deal offer objects — Related AMS marketing tables such as the deal offer header and offer line structures that provide the business context in which these modifier pairings are created and maintained.

Oracle Proprietary, Confidential Information — Legal Notices apply.