Search Results ozf_terr_levels_all




Overview
Explore in AI Chat

Overview

OZF_TERR_LEVELS_ALL is a foundational table within the Oracle Trade Management (OZF) module of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. The table stores the hierarchical levels that correspond to territory types defined in the JTF (CRM Foundation) schema. In practical terms, it provides the structural backbone for territory hierarchies used by Trade Management, Channel Management, and related go-to-market functions, allowing each territory type — such as geography, product, or customer-based classifications — to be decomposed into nested levels that roll up from a leaf territory to a root node.

The ETRM metadata classifies this object heuristically as a standalone Data Vault artifact. Because it carries its own descriptive level attributes and references external parents (TERR_TYPE_ID, SECURITY_GROUP_ID) without acting purely as an association table between two hubs, it is most naturally modeled as a satellite or reference hub rather than a link. Treat this classification as a modeling suggestion when mapping EBS data into a warehouse layer.

Key Information Stored

The table contains 39 documented columns. The most operationally significant are:

Common Use Cases and Queries

Typical scenarios include reconstructing territory trees for CRM assignment, validating hierarchy completeness for partner and quota planning, and reporting on enabled levels per hierarchy. A common query joins levels to their territory type:

  • SELECT l.TERR_LEVEL_ID, l.LEVEL_DEPTH, l.HIERARCHY_NAME, t.TERR_TYPE_NAME FROM OZF_TERR_LEVELS_ALL l, JTF_TERR_TYPES_ALL t WHERE l.TERR_TYPE_ID = t.TERR_TYPE_ID AND l.ENABLED_FLAG = 'Y';
  • Traversing a tree: self-join on l.TERRITORY_ID = c.PARENT_TERRITORY_ID to walk from a parent to its children.
  • Filtering by ORG_ID and SECURITY_GROUP_ID for multi-org, multi-tenant reporting.
  • Effective-date filtering using END_DATE_ACTIVE to reproduce historical hierarchy snapshots.

Related Objects

The most significant related objects, based on documented foreign keys and PK structure, are:

  • JTF_TERR_TYPES_ALL — joined on TERR_TYPE_ID; defines the territory type each level belongs to.
  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; enforces security partitioning.
  • OZF_TERR_LEVELS_ALL itself, via the self-referencing TERRITORY_ID / PARENT_TERRITORY_ID pair, for hierarchy traversal.
  • AMS_TERR_LEVELS_ALL — shares the AMS_TERR_LEVELS_ALL_PK primary key on TERR_LEVEL_ID, indicating a closely coupled AMS (Marketing) counterpart.
  • OZF_TERRITORIES_ALL and related territory assignment tables, which consume the level structure to place accounts and resources.