Search Results as_prom_relations_uk

Overview

The AS_PROMOTION_RELATIONSHIPS table is a core data object within the Oracle E-Business Suite (EBS) Sales Foundation module (AS). Its primary function is to define and manage hierarchical relationships between different sales promotions. This table enables the creation of complex, multi-level promotional structures, such as a parent promotion that contains several child promotions or a promotion that is a component of a larger campaign. By storing these relationships, the system can enforce business rules, cascade attributes, and track the lineage and dependencies between promotional activities. This is essential for managing sophisticated go-to-market strategies and ensuring data integrity across the promotional hierarchy in releases 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to capture the essential identifiers for establishing a relationship between two promotions. The primary unique identifier for each relationship record is the PROMOTION_RELATIONSHIP_ID column, which is the primary key for the AS_PROM_RELATIONS_PK constraint. The core data stored consists of two critical foreign key columns that link to the master promotions table. The PARENT_PROMOTION_ID column stores the identifier for the superior or containing promotion in the relationship. Conversely, the CHILD_PROMOTION_ID column stores the identifier for the subordinate or component promotion. Together, these two columns form a unique key (AS_PROM_RELATIONS_UK), preventing duplicate relationships from being defined between the same pair of promotions.

Common Use Cases and Queries

A primary use case is analyzing the structure of a promotional campaign. For instance, a business user may need to list all child promotions under a specific parent campaign for review or reporting. Developers and system integrators often query this table to understand promotion dependencies before making updates or running batch processes. Common SQL patterns include finding all children of a promotion or identifying all top-level promotions (those that do not appear as a CHILD_PROMOTION_ID in any record). A typical query to retrieve a promotion hierarchy would join AS_PROMOTION_RELATIONSHIPS to AS_PROMOTIONS_ALL twice—once for the parent details and once for the child details.

SELECT parent.promotion_name AS parent_promotion,
       child.promotion_name AS child_promotion
FROM   osm.as_promotion_relationships rel,
       osm.as_promotions_all parent,
       osm.as_promotions_all child
WHERE  rel.parent_promotion_id = parent.promotion_id
AND    rel.child_promotion_id = child.promotion_id
AND    parent.promotion_id = :p_parent_id;

Related Objects

The AS_PROMOTION_RELATIONSHIPS table has a direct and exclusive dependency on the master promotions table, AS_PROMOTIONS_ALL. It establishes two distinct foreign key relationships to this table, which are fundamental to its operation and data integrity.

• AS_PROMOTIONS_ALL (via PARENT_PROMOTION_ID): This foreign key constraint ensures that every parent promotion ID stored in a relationship record corresponds to a valid, existing promotion in the system.
• AS_PROMOTIONS_ALL (via CHILD_PROMOTION_ID): Similarly, this foreign key constraint ensures that every child promotion ID references a valid promotion, maintaining referential integrity for the subordinate side of the relationship.

These relationships mean that AS_PROMOTION_RELATIONSHIPS is a child table to AS_PROMOTIONS_ALL. Any reporting or data extraction involving promotion hierarchies will necessarily involve joining these three tables together.