Results for “jtf_terr_all”

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

Overview

JTF_TERR_ALL is the master definition table for territories within the Oracle E-Business Suite CRM Foundation (JTF) product family. It stores the complete definition of every territory maintained by the application, including the territory name, its category, hierarchical relationships, effective dates, and control flags that govern how the territory participates in the Territory Manager engine. Three categories of territories are supported: Standard, Escalation, and Template. Standard territories are the operational groupings used to assign and qualify resources, Escalation territories define routing behavior when standard qualification does not produce a winner, and Template territories serve as reusable patterns from which new territories can be generated. The table also records descriptive comments, activation windows, and the type classification of each territory.

From a Data Vault modeling perspective, the foreign key topology of JTF_TERR_ALL — dominated by recursive self-references and a stable surrogate primary key — suggests classification as a hub. The TERR_ID column functions as the enduring business key anchor for territory identity, while surrounding descriptive and flag attributes would typically be modeled as satellites in a Data Vault design.

Key Information Stored

The table is defined in the JTF schema and contains 64 documented columns in release 12.2.2. The primary key, constraint JTF_TERR_ALL_PK, is the surrogate key TERR_ID. A unique index, JTF_TERR_U1, covers (TERR_ID, ORG_ID, ZD_EDITION_NAME), which is the documented business-key candidate and reflects the multi-org and edition-aware nature of the table.

• TERR_ID — surrogate primary key and the join column referenced by virtually every dependent territory table.
• NAME — the user-facing territory name.
• PARENT_TERRITORY_ID — self-referencing foreign key that establishes the territory hierarchy.
• TERRITORY_TYPE_ID — foreign key to JTF_TERR_TYPES_ALL, classifying the territory across the Standard, Escalation, and Template categories.
• TEMPLATE_TERRITORY_ID and TEMPLATE_FLAG — identify the source template and mark template-based territories.
• ESCALATION_TERRITORY_ID and ESCALATION_TERRITORY_FLAG — define escalation routing behavior.
• ENABLED_FLAG, START_DATE_ACTIVE, and END_DATE_ACTIVE — control whether the territory is currently active and its effective window.
• RANK, RELATIVE_RANK, and ABSOLUTE_RANK — ordering and priority attributes used by the qualification engine.
• NUM_WINNERS and NUM_QUAL — define how many winners and qualified candidates a territory run may produce.
• OVERLAP_ALLOWED_FLAG and CATCH_ALL_FLAG — behavioral controls for overlapping qualification and catch-all handling.
• TERR_GROUP_FLAG, TERR_GROUP_ID, and TERR_GROUP_ACCOUNT_ID — group membership links to JTF_TTY_TERR_GROUPS and JTF_TTY_TERR_GRP_ACCTS.
• GEO_TERR_FLAG and GEO_TERRITORY_ID — geographic territory linkage to JTF_TTY_GEO_TERR.
• ORG_ID and SECURITY_GROUP_ID — multi-organization and security group partitioning, the latter referencing FND_SECURITY_GROUPS.
• ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF-enabled descriptive flexfield columns.
• OBJECT_VERSION_NUMBER — optimistic locking support for concurrent updates.

Common Use Cases and Queries

JTF_TERR_ALL is queried most often for territory hierarchy reporting, resource-to-territory assignment analysis, and troubleshooting qualification results in Territory Manager.

Listing active standard territories for an operating unit:

• SELECT t.terr_id, t.name, t.enabled_flag, t.start_date_active, t.end_date_active FROM jtf_terr_all t WHERE t.org_id = :org_id AND t.enabled_flag = 'Y' AND SYSDATE BETWEEN t.start_date_active AND NVL(t.end_date_active, SYSDATE + 1);

Walking the territory hierarchy using the recursive parent-child relationship:

• SELECT LEVEL, t.terr_id, t.name, t.parent_territory_id FROM jtf_terr_all t START WITH t.parent_territory_id IS NULL CONNECT BY PRIOR t.terr_id = t.parent_territory_id;

Joining to the territory type classification and to resources assigned to a territory:

• SELECT t.name, ty.name territory_type, r.resource_id FROM jtf_terr_all t, jtf_terr_types_all ty, jtf_terr_rsc_all r WHERE t.territory_type_id = ty.territory_type_id AND t.terr_id = r.terr_id;

Typical reporting scenarios include territory utilization, escalation path auditing, template usage tracking, and reconciliation of qualification outcomes stored in JTF_TERR_RESULTS against the defining territory metadata.

Related Objects

Because TERR_ID is widely referenced, the table participates in an extensive dependency web. The most significant related objects include:

• JTF_TERR_TYPES_ALL — joined on TERRITORY_TYPE_ID to resolve the territory category.
• JTF_TTY_TERR_GROUPS and JTF_TTY_TERR_GRP_ACCTS — joined on TERR_GROUP_ID and TERR_GROUP_ACCOUNT_ID for group membership.
• JTF_TTY_GEO_TERR — joined on GEO_TERRITORY_ID for geographic definitions.
• JTF_TERR_RSC_ALL — joined on TERR_ID to list resources assigned to a territory.
• JTF_TERR_QUAL_ALL, JTF_TERR_USGS_ALL, and JTF_TERR_QTYPE_USGS_ALL — qualification criteria and usage definitions attached to a territory.
• JTF_TERR_RESULTS and JTF_TAE_RPT_OBJ_WINNERS — joined on TERR_ID to report qualification outcomes.
• JTF_CHANGED_TERR_ALL and JTY_CHANGED_TERRS — track territory change history.
• AS_TERRITORY_ACCESSES and CSF_PLAN_TERRS — consume TERRITORY_ID for access control and planning.
• OKR_IP_RELEASES and AMS_BASE_SALES_VOL_ALL — reference TERR_ID and MRKT_LEVEL_VALUE respectively for release and market-level data.

DBA activities such as purge, security group filtering, and territory engine tuning should account for these recursive and downstream references to preserve referential integrity.