Search Results ams_terr_v




Overview
Explore in AI Chat

Overview

AMS_TERR_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, belonging to the AMS (Marketing) product family. It presents a flattened, denormalized representation of the territory hierarchy used by Oracle Marketing and Oracle Territory Manager. The view consolidates territory definition data into a single queryable structure, exposing each territory node alongside its parent, its hierarchy identifier, its territory type (hierarchy level), and its depth within the tree. Because territory hierarchies in AMS are stored across several normalized tables, AMS_TERR_V serves as the primary reporting and integration surface for consumers that need to traverse or present the hierarchy without writing the underlying joins themselves.

The view is documented as VALID in both EBS 12.1.1 and 12.2.2, and its definition is stable across those releases. Its ordered output — the view text includes an explicit ORDER BY LEVEL_DEPTH — makes it particularly convenient for reports that need territories emitted in top-down sequence, and for recursive or level-based processing in PL/SQL, BI Publisher, and Oracle Discoverer reports.

Underlying Base Objects

The ETRM metadata records AMS_TERR_V as defined over three base objects, exposed in APPS through synonyms:

The view is built with SELECT DISTINCT and joins the three tables on territory ID, parent territory ID, and territory type ID respectively. Because the AMS_TERR_LEVELS_ALL structure is denormalized across parent/child rows, the DISTINCT clause guards against duplicate node emissions when a territory participates in more than one parent relationship or level definition.

Key Columns

  • HIERARCHY_ID — Identifier of the territory hierarchy to which the node belongs; groups nodes into a single tree.
  • HIERARCHY_LEVEL — The name of the territory type at this level (for example, the label associated with the hierarchy tier such as country, region, or sales territory).
  • HIERARCHY_LEVEL_ID — The TERR_TYPE_ID for the level, enabling joins back to JTF_TERR_TYPES_ALL.
  • LEVEL_DEPTH — The zero- or one-based depth of the node within the hierarchy; the view is explicitly ordered by this column, so it is the primary driver of top-down traversal and the column most commonly referenced in filtering predicates such as WHERE LEVEL_DEPTH <= n.
  • PARENT_ID — The territory ID of the node's parent; NULL or a root marker for top-level nodes.
  • NODE_ID — The territory ID of the node itself.
  • NODE_VALUE — The display name of the territory node, as held in JTF_TERR_ALL.NAME.

Common Use Cases and Queries

Typical uses include hierarchy reports, territory assignment validation, integration extracts into CRM or data warehouses, and ad hoc analysis of territory depth and coverage. A representative query lists all levels of a given hierarchy in depth order:

SELECT hierarchy_id, level_depth, hierarchy_level, node_id, node_value, parent_id FROM apps.ams_terr_v WHERE hierarchy_id = :p_hierarchy_id ORDER BY level_depth, node_value;

To retrieve only the leaf territories for a hierarchy, filter on the maximum depth:

SELECT node_id, node_value FROM apps.ams_terr_v WHERE hierarchy_id = :p_hierarchy_id AND level_depth = (SELECT MAX(level_depth) FROM apps.ams_terr_v WHERE hierarchy_id = :p_hierarchy_id);

Because the view already exposes both PARENT_ID and NODE_ID, it can be used directly in CONNECT BY PRIOR node_id = parent_id queries to expand the tree, though the built-in LEVEL_DEPTH column usually makes explicit recursion unnecessary. Standard EBS access rules apply: grant SELECT on the view to reporting schemas, and query it through the APPS synonym rather than referencing base tables directly, so that the DISTINCT and join semantics remain encapsulated.