Search Results ams_terr_levels_all




Overview
Explore in AI Chat

Overview

AMS_TERR_LEVELS_ALL is a Marketing (AMS) module table within the Oracle E-Business Suite that stores the hierarchical levels corresponding to territory types maintained in the JTF schema. Territories in Oracle EBS are used by Territory Manager, Sales, Marketing, and Incentive Compensation to partition customers, prospects, and resources into logical groupings. A territory type — for example, a geography-based or industry-based classification — can be decomposed into one or more levels that define its internal hierarchy. AMS_TERR_LEVELS_ALL is the repository that binds each level to its owning territory type and describes its depth within that hierarchy.

From a data-modeling perspective, the supplied metadata classifies this object heuristically as a standalone entity. In Data Vault terms this is a modeling suggestion only: the table can be treated as a hub-like reference structure keyed on TERR_LEVEL_ID, with descriptive attributes (level depth, hierarchy identifier, territory identifiers) functioning as satellite attributes and foreign-key references to JTF_TERR_TYPES_ALL and FND_SECURITY_GROUPS acting as links to their respective hubs. Because the classification is heuristic and the object carries no downstream foreign keys in the mined relationship data, implementations should validate any dimensional or Data Vault treatment against the physical model before adopting it.

Key Information Stored

The primary key of the table is TERR_LEVEL_ID, enforced by the unique index AMS_TERR_LEVELS_ALL_U1 and the constraint AMS_TERR_LEVELS_ALL_PK. TERR_LEVEL_ID is the surrogate identifier for a territory level and is the column through which the table is joined from dependent territory structures. Although the metadata documents the unique index on TERR_LEVEL_ID as a business-key candidate, no additional natural key is supplied, meaning uniqueness of a level is defined only by the surrogate.

Among the thirty-five documented columns, the most operationally significant are:

Common Use Cases and Queries

Typical usage centers on resolving the level structure behind a territory type, reconstructing hierarchy paths, and validating level depth values during territory setup or migration. A common query enumerates levels for a given territory type joined to its JTF_TERR_TYPES_ALL definition:

  • SELECT l.TERR_LEVEL_ID, l.TERR_TYPE_ID, l.LEVEL_DEPTH, l.TERRITORY_ID, l.PARENT_TERRITORY_ID FROM AMS.AMS_TERR_LEVELS_ALL l, JTF.JTF_TERR_TYPES_ALL t WHERE l.TERR_TYPE_ID = t.TERR_TYPE_ID;
  • Filtering by ORG_ID and SECURITY_GROUP_ID to honor operating unit and security group boundaries in reporting extracts.
  • Ordering by HEIRARCHY_ID and LEVEL_DEPTH to render the full hierarchy tree for a territory type.
  • Self-joining TERRITORY_ID to PARENT_TERRITORY_ID to derive ancestor/descendant relationships for analytics or Incentive Compensation plan design.

Related Objects

The documented foreign keys anchor this table to two reference objects, and additional AMS/JTF territory objects depend on it indirectly:

  • JTF_TERR_TYPES_ALL — joined via AMS_TERR_LEVELS_ALL.TERR_TYPE_ID = JTF_TERR_TYPES_ALL.TERR_TYPE_ID; defines the territory type each level belongs to.
  • FND_SECURITY_GROUPS — joined via AMS_TERR_LEVELS_ALL.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; enforces row-level security.
  • JTF_TERRITORIES_ALL — the territory master referenced by TERRITORY_ID and PARENT_TERRITORY_ID.
  • AMS_TERRITORIES_ALL / AMS_TERR_DENORM_ALL — denormalized territory structures that consume level definitions.
  • JTF_TERR_RESOURCES / JTF_TERR_ASSIGNMENTS — resource-to-territory mappings built on the defined level structure.
  • FND_TERRITORIES — the seeded geographic territory reference distinct from AMS hierarchies but often cross-referenced in reporting.