Search Results as_territory_rollups




Overview
Explore in AI Chat

Overview

The AS_TERRITORY_ROLLUPS table is a core data object within the Oracle E-Business Suite Sales Foundation (AS) module. It is owned by the OSM schema and is responsible for defining hierarchical relationships between territories, specifically for the purpose of creating rollup territories. In the context of Oracle EBS sales and territory management, a rollup territory is a parent territory that aggregates the data and performance of its child territories. This structure is fundamental for enabling management reporting, quota assignment, and compensation calculations at various organizational levels, from individual sales representatives up through regional and national management.

Key Information Stored

The table's primary function is to store the parent-child links between territories. Its structure is defined by a composite primary key consisting of two critical columns, both of which are foreign keys to the AS_TERRITORIES_ALL table. The TERRITORY_ID column identifies the child or member territory. The ROLLUP_TERRITORY_ID column identifies the parent or rollup territory to which the child belongs. A single child territory (TERRITORY_ID) can be associated with multiple parent rollup territories, allowing for flexible and complex territory hierarchies that support different reporting dimensions.

Common Use Cases and Queries

The primary use case is querying territory hierarchies for reporting and data aggregation. A common requirement is to generate a list of all territories that roll up to a specific management territory for a sales report. Another typical scenario involves validating territory assignments during data loads or integration processes. A fundamental query pattern retrieves the rollup path for a given territory, often using hierarchical SQL (CONNECT BY or recursive CTEs in later database versions). For example, to find all territories under a specific rollup, one might use: SELECT territory_id FROM as_territory_rollups START WITH rollup_territory_id = &parent_terr_id CONNECT BY PRIOR territory_id = rollup_territory_id;. Conversely, to find the management chain for a leaf territory, the START WITH and PRIOR clauses would be reversed.

Related Objects

AS_TERRITORY_ROLLUPS has a direct and essential relationship with the AS_TERRITORIES_ALL table, which holds the master definition of all territories, including names, effective dates, and other attributes. Two foreign key constraints enforce referential integrity from AS_TERRITORY_ROLLUPS.TERRITORY_ID and AS_TERRITORY_ROLLUPS.ROLLUP_TERRITORY_ID to AS_TERRITORIES_ALL. This table is central to the territory management engine and is heavily referenced by APIs and programs within the Sales Foundation module for calculating territory membership, validating assignments, and aggregating transactional data like opportunities and sales credits for rollup reporting.