Search Results jtf_tty_geo_terr




Overview
Explore in AI Chat

Overview

The JTF_TTY_GEO_TERR table is a core data repository within the Oracle E-Business Suite CRM Foundation (JTF) module, specifically for versions 12.1.1 and 12.2.2. It serves as the master table for storing the definition and hierarchical structure of geography-based territories. Geography territories are a fundamental component of the territory management system, enabling the logical grouping of geographical locations—such as countries, states, or postal codes—for assignment to sales, service, or marketing resources. The table's primary role is to establish the territory framework upon which resource assignments, compensation plans, and performance reporting are built.

Key Information Stored

The table's central identifier is the GEO_TERRITORY_ID column, which is the primary key (JTF_TTY_GEO_TERR_PK). This unique numeric key is referenced throughout the territory management schema. Other critical columns define the territory's properties and relationships. The TERR_GROUP_ID is a foreign key linking the territory to a parent territory group defined in the JTF_TTY_TERR_GROUPS table. The PARENT_GEO_TERR_ID column is a self-referencing foreign key that establishes a parent-child hierarchy within the geography territory table itself, allowing for the creation of nested territory structures (e.g., a region containing multiple countries). Additional columns, while not explicitly listed in the provided metadata, typically include attributes such as territory name, effective start and end dates, and creation metadata.

Common Use Cases and Queries

This table is central to queries that resolve resource-to-territory assignments and analyze territory hierarchies. A common use case is identifying all resources assigned to a specific geography territory or its children. Another is generating a hierarchical report of all territories within a given group. Sample SQL patterns often involve hierarchical queries using CONNECT BY on the PARENT_GEO_TERR_ID column or joins to related resource assignment tables.

  • Hierarchy Query: SELECT LEVEL, geo_territory_id FROM jtf_tty_geo_terr START WITH parent_geo_terr_id IS NULL CONNECT BY PRIOR geo_territory_id = parent_geo_terr_id;
  • Join to Resources: SELECT rsc.resource_id, geo.geo_territory_id FROM jtf_tty_geo_terr geo, jtf_tty_geo_terr_rsc rsc WHERE geo.geo_territory_id = rsc.geo_territory_id;

Related Objects

The JTF_TTY_GEO_TERR table is a pivotal node in the territory management data model, with several documented foreign key relationships. It references two objects: the JTF_TTY_TERR_GROUPS table via TERR_GROUP_ID, and itself (for the hierarchy) via PARENT_GEO_TERR_ID. More critically, it is referenced by three key objects, using the GEO_TERRITORY_ID as the join column.

  • JTF_TTY_GEO_TERR (Self-Reference): Child territories reference their parent via PARENT_GEO_TERR_ID → GEO_TERRITORY_ID.
  • JTF_TTY_GEO_TERR_RSC: Stores the actual assignment of resources to a specific geography territory (GEO_TERRITORY_ID).
  • JTF_TTY_GEO_TERR_VALUES: Contains the specific geographical values (e.g., state codes, postal codes) that define the boundary of a territory (GEO_TERRITORY_ID).