Results for “as_territories”

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

Overview

AS_TERRITORIES is a multi-organization reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the AS (Sales Foundation) product family and exposes territory definition data — the hierarchical sales structures used by Oracle Territory Manager and downstream modules such as Sales, Service, and TeleSales. Oracle's own metadata classifies the object as Obsolete, meaning it is retained only for backward compatibility and should not be used for new development. Its documented status is VALID, so existing customizations and reports that still reference it will continue to resolve against the compiled definition.

The view's functional role is to present the single-organization columns of territories alongside a filtered, organization-scoped subset, allowing a session to see only the territory rows relevant to its current operating unit context. Because the filter is derived from the client environment rather than a runtime bind, the row set returned depends on how the session's client information (ORG_ID context) is initialized.

Underlying Base Objects

ETRM documents exactly one referenced base object for this view: the synonym AS_TERRITORIES_ALL. The view text confirms this, selecting every column from AS_TERRITORIES_ALL and applying an ORG_ID predicate. In the multi-org model, _ALL tables store rows for every operating unit, keyed by ORG_ID. AS_TERRITORIES therefore functions as the organization-scoped layer over that table.

The filter compares NVL(ORG_ID, a decoded session value) to that same decoded session value, where the session value is parsed from USERENV('CLIENT_INFO') — taking the first ten characters and treating a leading blank as NULL. Two consequences follow. First, rows in AS_TERRITORIES_ALL where ORG_ID is NULL (or resolves to the sentinel -99) are returned regardless of operating unit, because both sides of the comparison collapse to the same value. Second, the view is not secured by MOAC (Multi-Org Access Control) row-level security in the modern sense; it is a legacy client-info pattern superseded by policy-based multi-org views. This is consistent with the object's Obsolete designation.

Key Columns

• TERRITORY_ID — primary key of the territory definition, the join key to related AS tables.
• NAME, DESCRIPTION — user-facing identity of the territory.
• ORG_ID — owning operating unit; the column driving the view's filter.
• ENABLED_FLAG, ACTIVATED_FLAG, DEACTIVATED_FLAG — lifecycle and availability indicators.
• ACCESS_TYPE, ROLLUP_FLAG, USE_TO_ASSIGN_FLAG, NEED_TO_EXPAND_FLAG — control how the territory participates in the territory hierarchy and the assignment engine.
• SALESFORCE_ID, TEAM_LEADER_FLAG, RANK — sales organization association, leadership designation, and ordering within a group.
• START_DATE_ACTIVE, END_DATE_ACTIVE — effective dating for the territory.
• EXPANDED_FROM_TERR_ID, EXPAND_DATE — the columns that most directly answer the search term "expanded_from_terr_id." EXPANDED_FROM_TERR_ID records the parent territory from which the current row was expanded, and EXPAND_DATE records when that expansion occurred. Together they trace territory rollup/expansion lineage, which is why queries on expanded_from_terr_id typically reconstruct a territory's ancestry.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns for customer-defined territory attributes.
• Who-columns and concurrency columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) plus REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE for concurrent program auditing.

Common Use Cases and Queries

The most frequent legacy use is joining the view to transaction or assignment tables to resolve territory names and enabled status. A typical lookup by territory:

SELECT territory_id, name, enabled_flag, org_id
  FROM apps.as_territories
 WHERE territory_id = :p_territory_id;

To trace expansion lineage — the scenario most relevant to the "expanded_from_terr_id" search — join the view to itself on the expansion parent:

SELECT c.territory_id, c.name child_territory,
       p.territory_id parent_territory, p.name parent_name,
       c.expand_date
  FROM apps.as_territories c, apps.as_territories p
 WHERE c.expanded_from_terr_id = p.territory_id (+)
   AND c.enabled_flag = 'Y';

Because the object is Obsolete, new work should target the supported multi-org territory views and tables rather than AS_TERRITORIES. Where it must still be used, restrict joins on ORG_ID and territory_id to keep plans efficient, and be aware that the CLIENT_INFO-based filter can silently return NULL-ORG rows alongside operating-unit rows.