Search Results jtf_nav_tree_roots_vl




Overview
Explore in AI Chat

Overview

JTF_NAV_TREE_ROOTS_VL is a multilingual (VL) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the JTF – CRM Foundation product family, which supplies shared navigation, taxonomy, and tree infrastructure used across CRM modules such as iSupport, TeleSales, and Trade Management. The view exposes tree root definitions — the top-level entries of navigation trees — while automatically filtering the descriptive label to the language of the current session.

Technically, JTF_NAV_TREE_ROOTS_VL is a language-filtered join between the base table JTF_NAV_TREE_ROOTS_B and the translation table JTF_NAV_TREE_ROOTS_TL. Because EBS maintains multilingual columns in separate TL tables, this view provides a single logical row per tree root with the ROOT_LABEL resolved to the user's language. Applications and concurrent programs query the _VL view rather than joining base and translation tables directly, which enforces consistent language filtering and simplifies integration code.

In reporting and integration contexts, the view is treated as a read-only, reporting-safe object. It is frequently used to resolve the tree root behind a navigation hierarchy, to build menu and category listings, and to surface the runtime-language label in custom concurrent programs, BI Publisher reports, and OA Framework extensions. Its role is descriptive and relational rather than transactional; it holds no business transactions, only tree-root metadata.

Underlying Base Objects

The documented ETRM 12.2.2 metadata records the following referenced base objects, both accessed through APPS synonyms:

  • JTF_NAV_TREE_ROOTS_B (SYNONYM) — the base table holding non-translatable attributes such as TREE_ROOT_ID, ROOT_VALUE, VIEWBY_ID, SEQUENCE_NUMBER, and standard WHO audit columns.
  • JTF_NAV_TREE_ROOTS_TL (SYNONYM) — the translation table holding ROOT_LABEL per LANGUAGE, keyed by TREE_ROOT_ID.

The view definition joins these two tables on TREE_ROOT_ID and filters with the predicate T.LANGUAGE = USERENV('LANG'), so only the row matching the session language is returned. The B table supplies the identifier and structural columns; the TL table supplies the translated label. This is the standard EBS _VL pattern: one base table plus one translation table producing a single language-specific view.

Key Columns

  • TREE_ROOT_ID — Primary identifier for the tree root; the join key between base and translation records.
  • ROOT_VALUE — Internal value identifying the root within the navigation tree structure.
  • VIEWBY_ID — Identifier controlling how the tree is viewed or grouped for the root.
  • ROOT_LABEL — Translatable display label for the tree root, resolved to the current session language.
  • SEQUENCE_NUMBER — Ordering attribute used to sequence the root within its tree.
  • CREATED_BY, CREATION_DATE — Standard WHO columns recording row creation.
  • LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, LAST_UPDATED_BY — Standard WHO columns recording the most recent change.

Common Use Cases and Queries

Typical scenarios include listing all navigation tree roots with their runtime labels, resolving a ROOT_VALUE to its display name, and joining the roots to child tree structures for menu generation. The following query lists all roots in the session language, ordered by sequence:

SELECT tree_root_id,
       root_value,
       viewby_id,
       root_label,
       sequence_number
  FROM   apps.jtf_nav_tree_roots_vl
 ORDER BY sequence_number;

A second common pattern resolves a specific root by its internal value:

SELECT tree_root_id, root_label
  FROM   apps.jtf_nav_tree_roots_vl
 WHERE  root_value = :p_root_value;

Because ROOT_LABEL is already language-filtered, no additional join to JTF_NAV_TREE_ROOTS_TL is needed. When data appears missing, verify that a translation row exists for the session's USERENV('LANG'), since the view returns only rows with a matching language entry. For reporting, the view can be joined to navigation tree node tables using TREE_ROOT_ID to build complete, label-resolved hierarchies.