Search Results jty_denorm_dea_rules_all




Overview
Explore in AI Chat

Overview

The JTY_DENORM_DEA_RULES_ALL table is a multi-organization (denoted by the _ALL suffix) table owned by the JTF schema within the CRM Foundation product family. Its documented description states that it "stores denormalized territories for date effectivity assignment." In Oracle EBS 12.1.1 and 12.2.2, Territory Manager relies on this structure to flatten the hierarchical parent/child relationships inherent in territory definitions into pre-resolved rows, enabling significantly faster evaluation of date-effective territory rules during runtime qualification.

Territory data in the transactional CRM schema (primarily JTF_TERR_ALL) is organized hierarchically. Evaluating a rule that must ascend the hierarchy at query time is expensive. By denormalizing the territory relationships — pre-computing attributes such as rank, level from root, top-level territory, and immediate-parent flags — this table allows the Territory Manager engine to resolve winners, compute ranks, and enforce date effectivity without recursive traversal. Data Vault modeling heuristics classify this object as standalone, meaning it does not participate in a classic hub/link/satellite pattern mined from its foreign keys; it functions as a purpose-built denormalized cache rather than a normalized entity.

Key Information Stored

The table contains 25 documented columns in the 12.2.2 physical schema. The most significant include:

The physical row identifier is typically TERR_ID combined with the effectivity range, while TERR_ID alone is the dominant business-key candidate.

Common Use Cases and Queries

This table is primarily consumed by the Territory Manager and trade management evaluation engine. Common practical scenarios include:

  • Retrieving all denormalized territories active as of a given date for a specific operating unit.
  • Identifying top-level and immediate-parent territories to reconstruct hierarchy views.
  • Ranking qualifying territories to determine assignment winners.

A representative query:

SELECT terr_id, rank, absolute_rank, level_from_root, start_date, end_date
FROM jtf.jty_denorm_dea_rules_all
WHERE org_id = :p_org_id
AND TRUNC(SYSDATE) BETWEEN start_date AND end_date
ORDER BY absolute_rank;

Reporting use cases include audit reports on stale denormalized rows, hierarchy-depth analytics, and data-quality checks comparing this table against JTF_TERR_ALL.

Related Objects

Because the denormalization process is periodic, administrators should validate freshness of this table against JTF_TERR_ALL after any territory hierarchy change.