Results for “root_flag”

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

Overview

The EDW_BIM_CMPGNS_DENORM table is a denormalized campaign dimension object owned by the BIM schema in Oracle E-Business Suite. It belongs to the BIM – Marketing Intelligence product, which is flagged as obsolete in Oracle ETRM documentation for releases 12.1.1 and 12.2.2. The table materializes campaign hierarchy information in a flattened, query-friendly form, eliminating the need for recursive self-joins that would otherwise be required to traverse parent-child campaign relationships. As such, it functions primarily as a reporting and analytics acceleration structure rather than an operational transaction table.

The columns expose lineage paths through both PARENT_CAMPAIGN_ID and ROOT traversal chains, alongside pre-computed level indicators. This design supports campaign performance roll-ups, hierarchical drill-downs, and BI extracts common in marketing intelligence workloads. The ETRM status is documented as VALID, with a physical column count of 19.

Based on the heuristic Data Vault classification mined from the foreign key structure, the table is characterized as standalone. In Data Vault terms, this suggests a satellite-style role rather than a hub or link: it enriches campaign identity with descriptive hierarchy attributes but does not participate in material outbound referential relationships of its own. The single documented foreign key points outward to FND_SECURITY_GROUPS, reinforcing its role as a consumer of reference data rather than a driver of transactional joins.

Key Information Stored

The most significant columns fall into three groups:

• Surrogate identifiers: CAMPAIGN_ID functions as the surrogate primary key, uniquely identifying each campaign row. PARENT_CAMPAIGN_ID is the self-referential business key linking a child campaign to its immediate hierarchical parent.
• Hierarchy descriptors: CAMPAIGN_NAME and PARENT_CAMPAIGN_NAME provide human-readable labels for the current and parent nodes. IMMEDIATE_PARENT_FLAG, ROOT_FLAG, LEVEL_FROM_PARENT, LEVEL_FROM_ROOT, and PARENT_LEVEL_FROM_ROOT together encode the depth and position of each campaign within the tree, enabling flat queries to reconstruct hierarchy without recursion.
• Audit and concurrent-program columns: Standard EBS audit fields — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — capture row history. The concurrent manager trio REQUEST_ID, PROGRAM_APPLICATION_ID, and PROGRAM_ID, plus PROGRAM_UPDATE_DATE, identify the batch process responsible for the most recent write, which is typical for denormalized structures populated by scheduled concurrent programs.

SECURITY_GROUP_ID restricts row visibility according to the security group assigned to the operating unit or responsibility, and is the only documented foreign key in the schema.

Common Use Cases and Queries

Typical usage centers on hierarchical campaign reporting. A common pattern retrieves all root campaigns and their immediate children:

• Filter by ROOT_FLAG = 'Y' to isolate top-level campaigns.
• Use LEVEL_FROM_ROOT to restrict depth, for example LEVEL_FROM_ROOT <= 2 for a two-tier view.
• Join back to transaction or response tables on CAMPAIGN_ID to attribute revenue or responses to the correct hierarchy node without recursive SQL.
• Group by SECURITY_GROUP_ID to honor multi-org access rules in shared reporting environments.

A representative pattern joins the denormalized table to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to resolve the security group description, while restricting rows by PROGRAM_ID or REQUEST_ID to audit which concurrent request last refreshed the data.

Related Objects

• FND_SECURITY_GROUPS — joined on EDW_BIM_CMPGNS_DENORM.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; the only documented foreign key relationship.
• EDW_BIM_CMPGNS_DENORM itself acts as a self-referencing structure via CAMPAIGN_ID and PARENT_CAMPAIGN_ID.
• BIM base campaign tables — the source objects from which this denormalized structure is derived; the documented source is the operational campaign table populated by the Marketing Intelligence concurrent processes.
• FND_CONCURRENT_REQUESTS — referenced implicitly through REQUEST_ID for batch execution auditing.
• FND_APPLICATION / FND_PROGRAM — referenced through PROGRAM_APPLICATION_ID and PROGRAM_ID to identify the populating program.

Because the BIM – Marketing Intelligence product is marked obsolete in the documented releases, downstream dependencies should be verified against the current EBS instance before reliance in new development.