Results for “as_sales_groups_denorm”

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

Overview

AS_SALES_GROUPS_DENORM is a denormalized table owned by the OSM schema within the AS - Sales Foundation product of Oracle E-Business Suite, documented as VALID in both release 12.1.1 and 12.2.2. As its name and description indicate, the table is derived from the base AS_SALES_GROUPS entity and stores a flattened, pre-joined representation of the sales group hierarchy. Sales groups are the organizational units used throughout Oracle's sales and order management stack to segment customers, territories, and resources for reporting, assignment, and security purposes.

The denormalized form exists primarily to accelerate hierarchical queries, such as traversing parent-child relationships among sales groups, without repeatedly joining the base table to itself. The primary key AS_SALES_GROUPS_DENORM_PK is a composite of SALES_GROUP_ID and PARENT_SALES_GROUP_ID, meaning each row represents one ancestor-descendant pairing rather than a single sales group. A unique index AS_SALES_GROUPS_DENORM_U1 on the same two columns serves as the business-key candidate.

From a Data Vault modeling perspective, the metadata's heuristic classification places this object as standalone, with no foreign key relationships mined from the physical structure. This is consistent with a denormalized reporting or performance construct rather than a normalized transactional entity. Modelers treating AS_SALES_GROUPS_DENORM analytically may choose to treat the (SALES_GROUP_ID, PARENT_SALES_GROUP_ID) pair as a link-like association between sales group nodes, while the descriptive and audit columns behave as satellite attributes.

Key Information Stored

The table contains 24 documented columns. The most significant are:

• SALES_GROUP_ID — Identifies the sales group node being described; part of the composite primary key and unique index.
• PARENT_SALES_GROUP_ID — Identifies the corresponding ancestor sales group; the second component of the primary key and unique index.
• IMMEDIATE_PARENT_FLAG — Indicates whether the parent relationship is the immediate parent rather than a higher-level ancestor, allowing direct-versus-transitive hierarchy distinctions.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield (DFF) columns, providing extensibility for customer-specific hierarchy metadata.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns tracking who created and last modified each row, and under which login session.

The surrogate primary key AS_SALES_GROUPS_DENORM_PK and the business-key candidate AS_SALES_GROUPS_DENORM_U1 both span SALES_GROUP_ID and PARENT_SALES_GROUP_ID, indicating that in this table the business identity and the technical identity coincide. There is no separate single-column surrogate.

Common Use Cases and Queries

Typical use cases include hierarchical reporting, security rule evaluation, and territory analytics where the full ancestor chain of a sales group is required. A representative query returns all ancestors of a given sales group:

• SELECT parent_sales_group_id FROM as_sales_groups_denorm WHERE sales_group_id = :p_group_id;

To retrieve only the direct parent, filter on the flag:

• SELECT parent_sales_group_id FROM as_sales_groups_denorm WHERE sales_group_id = :p_group_id AND immediate_parent_flag = 'Y';

Reporting use cases frequently join this table to customer or resource assignment tables to produce roll-up summaries by top-level sales group. Because the table is denormalized, these queries avoid recursive CONNECT BY logic against AS_SALES_GROUPS, which is important for performance on large hierarchies. DFF attributes (ATTRIBUTE1 through ATTRIBUTE15) are commonly surfaced in operational reports where descriptive flexfield context matters.

Related Objects

The metadata identifies no foreign keys, consistent with its standalone classification. The most significant related objects are:

• AS_SALES_GROUPS — The base table from which this denormalized structure is derived; the source of truth for sales group definitions.
• AS_SALES_GROUPS_DENORM_PK and AS_SALES_GROUPS_DENORM_U1 — The primary key constraint and unique index enforcing uniqueness on SALES_GROUP_ID and PARENT_SALES_GROUP_ID.
• AS_SALES_GROUP_USAGES and related Sales Foundation assignment entities — Reference sales groups for segmentation and access control.
• AS_ACCESSES and associated security objects — Consume sales group hierarchies when evaluating user access to customers and territories.

Because denormalized data is typically maintained by concurrent programs or triggers rather than direct DML, administrators should verify refresh jobs and consistency checks against AS_SALES_GROUPS when troubleshooting discrepancies.