Search Results jtf_changed_terr_all




Overview
Explore in AI Chat

Overview

JTF_CHANGED_TERR_ALL is a CRM Foundation (JTF) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores change-history records for the Territory Management (ETRM) model. According to Oracle's documented metadata, the table records modifications made to three core territory objects: JTF_TERR_ALL (the territory definition itself), JTF_TERR_VALUES (territory qualification criteria), and JTF_TERR_RSC (territory resource assignments). Each row captures the state of an affected record before and after a change, allowing the territory engine and downstream processes to identify what moved, what was added, what was removed, and when. In the ETRM schema the table is owned by JTF and is documented as VALID, with 79 columns in the 12.2.2 physical schema.

From a Data Vault modeling perspective, the table is heuristic-classified as satellite-leaning. This is a reasonable suggestion given that it holds descriptive, time-stamped change attributes (old and new values, action codes, audit columns) keyed to parent territory entities rather than representing an independent business relationship.

Key Information Stored

The column set is organized around a before/after pattern. The most operationally significant columns include:

Surrogate and audit identifiers (TERR_VALUE_ID, TERR_RSC_ID, TERR_QTYPE_USG_ID, TERR_RSC_ACCESS_ID) tie each change row to the specific qualification value, resource assignment, usage, or access record affected. The documented schema does not expose a single-column surrogate primary key; the effective row key is a composite of the change context plus the affected entity identifiers.

Common Use Cases and Queries

Typical uses include auditing territory hierarchy changes, troubleshooting why a sales representative lost or gained access to a territory, reconstructing qualification criteria as of a date, and feeding downstream analytics with change volume metrics. A representative query joining the change log to the territory master might be:

  • SELECT c.TERR_ID, t.NAME, c.ACTION, c.OLD_RANK, c.NEW_RANK, c.OLD_LAST_UPDATE_DATE, c.NEW_LAST_UPDATE_DATE FROM JTF_CHANGED_TERR_ALL c JOIN JTF_TERR_ALL t ON t.TERR_ID = c.TERR_ID WHERE c.NEW_LAST_UPDATE_DATE >= :start_date;
  • Filtering by REQUEST_ID to isolate the output of a specific concurrent territory refresh.
  • Aggregating by ACTION and TRIGGER_MODE to report change volumes per period.
  • Comparing OLD_PARENT_TERRITORY_ID and NEW_PARENT_TERRITORY_ID to identify reparenting activity in the territory hierarchy.

Related Objects

  • JTF_TERR_ALL — primary parent via TERR_ID; the territory master record.
  • JTF_TERR_VALUES_ALL — referenced through TERR_VALUE_ID; territory qualification values.
  • JTF_TERR_RSC_ALL — referenced through TERR_RSC_ID; territory resource assignments.
  • JTF_TERR_RSC_ACCESS_ALL — referenced through TERR_RSC_ACCESS_ID; resource access rules.
  • JTF_TERR_QTYPE_USGS_ALL — referenced through TERR_QTYPE_USG_ID; qualification type usage.
  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; security group context.

These relationships confirm that JTF_CHANGED_TERR_ALL functions as a dependent change-capture satellite for the core ETRM territory tables.