Search Results related_deal_lines_id

Overview

The AMS_RELATED_DEAL_LINES table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the AMS (Marketing) module. Its primary function is to support the configuration of Trade Deal Offers. In this context, a single deal line can be associated with two distinct pricing modifiers. This table exists to define and store the relationship between these two related modifiers, effectively linking them within the same promotional deal. It serves as a junction table that enforces the business logic allowing for complex, multi-part promotional pricing structures within trade agreements.

Key Information Stored

The table's structure is designed to capture the essential link between two pricing modifiers. The most critical columns are the foreign keys that establish this relationship. The MODIFIER_ID column holds the identifier for the primary modifier in the deal line. The RELATED_MODIFIER_ID column holds the identifier for the secondary, related modifier. Both of these columns are foreign keys that reference the LIST_LINE_ID in the QP_LIST_LINES table, which is part of the Advanced Pricing (QP) module. This linkage is the table's raison d'être. The table is uniquely identified by its primary key column, RELATED_DEAL_LINES_ID, which provides a unique identifier for each relationship record.

Common Use Cases and Queries

The primary use case is reporting and validating the structure of Trade Deal Offers that utilize dual modifiers. A common query would retrieve all deal lines where two specific modifiers are used in conjunction, or list all related modifier pairs for a given deal. For instance, to audit all established relationships, one might execute:

• SELECT modifier_id, related_modifier_id FROM ams_related_deal_lines;

To find the details of the related pricing modifiers from the QP module, a join is essential:

• SELECT ardl.*, qll1.list_line_code AS modifier_code, qll2.list_line_code AS related_modifier_code FROM ams_related_deal_lines ardl JOIN qp_list_lines qll1 ON ardl.modifier_id = qll1.list_line_id JOIN qp_list_lines qll2 ON ardl.related_modifier_id = qll2.list_line_id;

This data is critical for understanding the complete promotional mechanics applied to a product or agreement and is often used in deal analysis and financial accrual reports.

Related Objects

The AMS_RELATED_DEAL_LINES table has a direct and exclusive dependency on the Advanced Pricing module's modifier definitions. As documented in the provided metadata, it references the QP_LIST_LINES table twice through two distinct foreign key constraints:

• AMS_RELATED_DEAL_LINES.MODIFIER_ID references QP_LIST_LINES.LIST_LINE_ID
• AMS_RELATED_DEAL_LINES.RELATED_MODIFIER_ID references QP_LIST_LINES.LIST_LINE_ID

This means the validity of the data in AMS_RELATED_DEAL_LINES is contingent on the existence of corresponding modifier records in QP_LIST_LINES. The table itself is referenced by its primary key constraint, AMS_RELATED_DEAL_LINES_PK, on the column RELATED_DEAL_LINES_ID.