Results for “jtf_tty_geo_terr”

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

Overview

JTF.JTF_TTY_GEO_TERR is a CRM Foundation table within the Oracle E-Business Suite that stores Geography Territory data. It is owned by the JTF schema, holds VALID status in both the 12.1.1 and 12.2.2 releases, and belongs to the JTF - CRM Foundation product group. The table functions as the central definition repository for geography-based territories, providing the hierarchical and organizational backbone used by Oracle Territory Manager and downstream CRM modules such as Sales, TeleSales, Service, and Incentive Compensation.

From a Data Vault modeling perspective, the FK topology mined from the ETRM metadata classifies this object as hub-leaning. It acts primarily as a hub whose business key is GEO_TERRITORY_ID, while also carrying embedded descriptive attributes and self-referential hierarchy edges. Analytic consumers may wish to model it as a hub with an adjacent satellite for the descriptive columns and a recursive link for the PARENT_GEO_TERR_ID relationship.

Key Information Stored

The documented physical schema exposes 15 columns. The most significant are:

• GEO_TERRITORY_ID — the surrogate primary key, defined by constraint JTF_TTY_GEO_TERR_PK. A unique index JTF_TTY_GEO_TERR_U1 exists on the same column, confirming it as the business-key candidate.
• GEO_TERR_NAME — the human-readable description of the geography territory.
• TERR_GROUP_ID — foreign key to JTF_TTY_TERR_GROUPS, placing the territory in its owning territory group.
• PARENT_GEO_TERR_ID — self-referencing foreign key used to build hierarchical territory trees.
• CHILD_NODE_FLAG — flags whether the node has children in the hierarchy.
• OWNER_RESOURCE_ID, OWNER_RSC_GROUP_ID, OWNER_RSC_ROLE_CODE, and OWNER_RESOURCE_TYPE — identify the resource, resource group, role, and type that own the territory.
• OBJECT_VERSION_NUMBER — optimistic-locking column introduced by the OAF/ADF framework.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — audit columns populated by standard Oracle EBS WHO columns.

Common Use Cases and Queries

Typical usage includes retrieving a territory tree, identifying owners for a given geography, and joining geography territories to resource assignments for reporting. A representative recursive query retrieves the hierarchy:

SELECT LEVEL, GEO_TERRITORY_ID, GEO_TERR_NAME, PARENT_GEO_TERR_ID
FROM   JTF.JTF_TTY_GEO_TERR
START WITH PARENT_GEO_TERR_ID IS NULL
CONNECT BY PRIOR GEO_TERRITORY_ID = PARENT_GEO_TERR_ID;

To find all territories owned by a specific resource:

SELECT g.GEO_TERRITORY_ID, g.GEO_TERR_NAME, g.OWNER_RESOURCE_ID
FROM   JTF.JTF_TTY_GEO_TERR g
WHERE  g.OWNER_RESOURCE_ID = :resource_id;

Reporting scenarios include territory coverage roll-ups, quota-to-territory alignment, and geography-based security. Because GEO_TERRITORY_ID is referenced by several dependent tables, joins to resource and value tables are common in territory qualification logic.

Related Objects

The ETRM metadata documents the following significant related objects:

• JTF_TTY_TERR_GROUPS — parent of TERR_GROUP_ID; groups geography territories into logical sets.
• JTF_TTY_GEO_TERR (self) — via PARENT_GEO_TERR_ID, enabling recursive hierarchies.
• JTF_TTY_GEO_TERR_RSC — child table joined on GEO_TERRITORY_ID; stores resource assignments per geography territory.
• JTF_TTY_GEO_TERR_VALUES — child table joined on GEO_TERRITORY_ID; holds the geographic values (e.g., countries, states, postal codes) that define the territory scope.
• JTF_TERR_ALL — the consolidated territory view/master reference, joined via GEO_TERRITORY_ID.

Together these objects form the territory definition subsystem that Oracle Territory Manager and downstream CRM applications query at runtime.