Results for “as_promotion_relationships_v”

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

Overview

The view AS_PROMOTION_RELATIONSHIPS_V belongs to the AS - Sales Foundation product family in Oracle E-Business Suite (documented for 12.1.1 and 12.2.2). It presents the hierarchy of relationships that exist between promotions, exposing the parent-child associations recorded in the promotion relationships table alongside descriptive attributes for the child promotion. In the Oracle Advanced Pricing and Sales Foundation data model, promotions may be qualified, linked, or subordinated to other promotions; this view provides a denormalized, query-friendly representation of those links.

The view is documented as a reporting and integration construct rather than an operational entity. Its purpose is to allow reporting tools, concurrent programs, and custom extensions to retrieve promotion hierarchy information without having to re-implement the joins between the relationship table, the promotion master table, and the lookup table that resolves promotion type meanings. Because it exposes standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) together with fifteen descriptive flexfield attributes, it supports audit-oriented and attribute-driven reporting as well as simple hierarchy navigation.

Underlying Base Objects

The view is defined over three documented base objects:

• AS_PROMOTION_RELATIONSHIPS (aliased PROMREL) — the primary table holding the promotion relationship records, including the parent and child promotion identifiers and the DFF attributes.
• AS_PROMOTIONS (aliased CHILDPROM) — the promotion master table, joined on CHILD_PROMOTION_ID = CHILDPROM.PROMOTION_ID to supply the child promotion's type, code, and name.
• AS_LOOKUPS (aliased ASLKP1) — the lookup table, joined with outer-join syntax on CHILDPROM.TYPE = ASLKP1.LOOKUP_CODE and ASLKP1.LOOKUP_TYPE = 'PROMOTION_TYPE', to resolve the promotion type code to its display meaning.

An important implementation note in the ETRM metadata is that the view is not implemented in this database, meaning the definition may exist in the repository or documentation but the object is not necessarily deployed in every environment. The join to AS_LOOKUPS is an outer join, so rows are returned even when no matching PROMOTION_TYPE lookup exists.

Key Columns

• PROMOTION_RELATIONSHIP_ID — primary identifier of the relationship record.
• PARENT_PROMOTION_ID and CHILD_PROMOTION_ID — the two promotions that participate in the relationship; the parent drives the hierarchy and the child is the dependent promotion.
• CHILD_CODE, CHILD_NAME, CHILD_TYPE — the child promotion's code, name, and raw type code (aliases of CHILDPROM.CODE, CHILDPROM.NAME, CHILDPROM.TYPE).
• CHILD_TYPE_CODE — the meaning resolved from AS_LOOKUPS (ASLKP1.MEANING) for the promotion type.
• ROW_ID — the row identifier from the underlying relationship table.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield columns carried through for extended attribute reporting.
• Standard WHO Columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include reporting on promotion hierarchies, identifying all children of a given parent promotion, and validating that dependent promotions are correctly linked.

Retrieve all children of a specific parent:

SELECT promotion_relationship_id, child_promotion_id, child_code, child_name, child_type_code
FROM   as_promotion_relationships_v
WHERE  parent_promotion_id = :parent_id
ORDER BY child_name;

Search by child promotion name (the term "child_name" that prompted this lookup):

SELECT parent_promotion_id, child_promotion_id, child_name, child_type_code
FROM   as_promotion_relationships_v
WHERE  UPPER(child_name) LIKE UPPER('%' || :search || '%');

List relationship counts by child promotion type:

SELECT child_type_code, COUNT(*)
FROM   as_promotion_relationships_v
GROUP BY child_type_code;

Because the view is not guaranteed to be deployed, developers should confirm its existence in the target instance and be prepared to replicate the join logic directly against AS_PROMOTION_RELATIONSHIPS, AS_PROMOTIONS, and AS_LOOKUPS when the view is unavailable.