Results for “jtf_changed_terr_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• TERR_ID — foreign key to JTF_TERR_ALL; identifies the territory whose change is being logged.
• ACTION — the nature of the change performed against the territory record.
• TRIGGER_MODE — indicates how the change was initiated (for example, programmatic versus user-driven).
• OLD_* / NEW_* column pairs — the prior and resulting values for the same attribute, including OLD_START_DATE_ACTIVE / NEW_START_DATE_ACTIVE, OLD_END_DATE_ACTIVE / NEW_END_DATE_ACTIVE, OLD_RANK / NEW_RANK, OLD_UPDATE_FLAG / NEW_UPDATE_FLAG, and the audit fields OLD_LAST_UPDATED_BY, OLD_LAST_UPDATE_DATE, NEW_LAST_UPDATED_BY, NEW_LAST_UPDATE_DATE.
• OLD_TERR_QUAL_ID / NEW_TERR_QUAL_ID — linkage to territory qualification definitions that were modified.
• OLD_TERRITORY_ID / NEW_TERRITORY_ID via PARENT_TERRITORY_ID, OLD_PARENT_TERRITORY_ID and NEW_PARENT_TERRITORY_ID — captures hierarchy reparenting events.
• TRANSFER_ONLY_FLAG — distinguishes transfer-only updates from other change categories.
• REQUEST_ID — associates the change with a concurrent request, enabling traceability to the job that wrote it.
• ORG_ID and SECURITY_GROUP_ID — multi-org and security group context for the change; SECURITY_GROUP_ID references FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — optimistic locking / versioning column used by the EBS framework.

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.