Search Results parent_node_key




Overview
Explore in AI Chat

Overview

The APPS.JTF_TERRITORIES_NAV view is a navigation-oriented database object within the Oracle E-Business Suite Territory Management (also referred to as ETRM) module. It exposes a hierarchical, presentation-ready representation of territory definitions that is consumed by the Territory Manager navigation tree. Rather than storing data itself, the view synthesizes territory records into a flat result set of navigation nodes, each carrying the attributes needed to render a tree node: a display label, node type, node key, icon, parent linkage, display rank, and a LEAF_FLAG.

The view is a UNION ALL construct composed of multiple sub-queries. Each branch maps territory records to either a TERRITORY parent node type or a SOURCE parent node type, with the differentiation driven by the jtall.parent_territory_id value relative to the sentinel value 1. The LEAF_FLAG column is a computed indicator derived from an outer-join count of child territories: territories with no children are flagged 'Y', and those with children are flagged 'N'. The same child-count logic also determines the ICON_NAME, returning 'territor' for leaf nodes and 'aftreecl' for nodes that contain children.

Because the view filters out template territories (template_flag <> 'Y') and escalation territories (escalation_territory_flag <> 'Y'), it presents only the operational, navigable territory hierarchy relevant to users in the Territory Manager interface.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, JTF_TERRITORIES_NAV is owned by APPS and is defined over the following referenced base objects:

  • FND_GLOBAL (PACKAGE) — provides session context such as organization and user identifiers.
  • JTF_NAV_NODE_TYPES_V (VIEW) — supplies node type metadata; the view constrains on root_key = 'JTF_TERR_ROOT' and node_type = 'TERRITORY'.
  • JTF_SOURCES (VIEW) — the territory source definition, linked through JTF_TERR_USGS.source_id.
  • JTF_TERR (SYNONYM) — the base territory entity, aliased as jtall and again as jtall2 for the self-join that counts children.
  • JTF_TERR_USGS (SYNONYM) — the territory usage association bridging territories to sources.

The self-join on JTF_TERR (jtall.terr_id = jtall2.parent_territory_id(+), with matching org_id) is the mechanism underlying both LEAF_FLAG and the icon selection, since the aggregate COUNT(jtall2.parent_territory_id) returns zero precisely for territories that have no children.

Key Columns

  • LABEL — the localized territory name concatenated with its rank in brackets, formatted as name [rank].
  • NODE_TYPE — the navigation node type, here 'TERRITORY'.
  • NODE_KEY — the unique node identifier, constructed by quoting the territory ID.
  • ICON_NAME — 'territor' for leaf territories, 'aftreecl' for nodes having children.
  • LEAF_FLAG — 'Y' when the territory has no child territories, otherwise 'N'.
  • PARENT_NODE_TYPE — 'TERRITORY' or 'SOURCE' depending on the union branch.
  • PARENT_NODE_KEY — the quoted parent territory ID or source ID.
  • DISPLAY_RANK — the sort order value derived from jtall.rank.

Common Use Cases and Queries

The view is primarily queried to enumerate the territory tree and to identify leaf versus non-leaf nodes for reporting or eligibility checks. A typical query locates leaf territories under a given parent:

SELECT label, node_key, display_rank
FROM   apps.jtf_territories_nav
WHERE  leaf_flag = 'Y'
ORDER  BY display_rank;

To retrieve the full hierarchy for a specific parent node:

SELECT label, node_type, node_key, parent_node_key
FROM   apps.jtf_territories_nav
WHERE  parent_node_key = '"1001"'
ORDER  BY display_rank;

Counting leaves by parent supports administrator validation of tree depth, while filtering on parent_node_type = 'SOURCE' isolates the top-level entries that hang directly beneath a territory source. Because LEAF_FLAG is computed rather than stored, it is always current with the underlying JTF_TERR hierarchy.