Search Results jtf_terr_denorm_rules_all




Overview
Explore in AI Chat

Overview

JTF_TERR_DENORM_RULES_ALL is a CRM Foundation (JTF) table that stores denormalized territorial assignment rules used by Oracle E-Business Suite's Territory Manager and Resource Manager components. Its documented purpose is performance optimization: the transactional territory assignment engine, driven by the Territory Assignment Program (TAP) and the resource–territory qualification logic, would otherwise resolve multi-level territory hierarchies, qualification types, and parent–child relationships through repeated recursive lookups against JTF_TERR_ALL and its related qualification tables. By persisting the resolved hierarchy attributes and ranking values into a flat, denormalized structure, the table eliminates that runtime recursion during high-volume territory qualification operations.

The object is registered in the JTF schema with VALID status and, per the documented 12.2.2 physical schema, contains 48 columns. The metadata classifies it heuristically as standalone under Data Vault modeling conventions; that is, it is not itself decomposed into hub, link, and satellite constructs. This classification is worth noting because the table carries both a foreign key to JTF_TERR_ALL (suggesting a link or satellite role) and a full set of WHO columns and date ranges (suggesting a satellite). In practice it behaves as a performance satellite whose grain is one row per source/qualification type/territory combination.

Key Information Stored

The table's identifying and qualifying columns are the most operationally significant. SOURCE_ID, QUAL_TYPE_ID, and TERR_ID together form the effective business key of a denormalized rule row: SOURCE_ID identifies the originating source record, QUAL_TYPE_ID references JTF_QUAL_TYPES_ALL to characterize the qualification dimension, and TERR_ID references JTF_TERR_ALL for the territory itself.

The metadata does not document an explicit surrogate primary key; the combination of SOURCE_ID, QUAL_TYPE_ID, and TERR_ID is the most plausible unique business-key candidate, and any index analysis should treat that triplet as the primary lookup pattern.

Common Use Cases and Queries

The dominant use case is performance-tuned territory qualification within Oracle Territory Manager. Applications query the denormalized table to resolve which territories a resource or customer qualifies for without recursing through the hierarchy. A representative query filters by qualification type and traverses the precomputed links:

  • SELECT TERR_ID, PARENT_TERR_ID, TOP_LEVEL_TERR_ID, ABSOLUTE_RANK FROM JTF_TERR_DENORM_RULES_ALL WHERE QUAL_TYPE_ID = :p_qual_type AND ORG_ID = :p_org ORDER BY ABSOLUTE_RANK;
  • Reporting on hierarchy depth: SELECT LEVEL_FROM_ROOT, COUNT(*) FROM JTF_TERR_DENORM_RULES_ALL GROUP BY LEVEL_FROM_ROOT;
  • Identifying leaf territories for a given source: SELECT TERR_ID FROM JTF_TERR_DENORM_RULES_ALL WHERE LEAF_FLAG = 'Y' AND SOURCE_ID = :p_source;
  • Effective-dated lookups constrained by SYSDATE BETWEEN START_DATE AND END_DATE.

Because the table is maintained by concurrent programs (evidenced by REQUEST_ID and PROGRAM_ID), administrators should schedule the denormalization refresh rather than attempting direct DML.

Related Objects

The most significant related objects, based on the documented foreign keys, are:

  • JTF_TERR_ALL — parent territory definition, joined on TERR_ID.
  • JTF_QUAL_TYPES_ALL — qualification type definitions, joined on QUAL_TYPE_ID.
  • FND_SECURITY_GROUPS — security group scoping, joined on SECURITY_GROUP_ID.
  • JTF_TERR_DENORM_RULES_ALL references JTF_TERR_ALL's related territory concept via RELATED_TERR_ID and PARENT_TERR_ID for hierarchy resolution.
  • FND_CONCURRENT_REQUESTS — referenced indirectly through REQUEST_ID for audit of the denormalization job.

Developers and integration specialists should treat JTF_TERR_DENORM_RULES_ALL as a derived, non-authoritative structure: it depends entirely on the primary territory and qualification definitions and should be queried, not modified, by external applications.