Search Results jtf_terr_types




Overview
Explore in AI Chat

Overview

JTF_TERR_TYPES is a CRM Foundation (JTF) reporting and integration view that exposes the definition of territory types within Oracle E-Business Suite 12.1.1 and 12.2.2. Territory types represent the classification layer of the Enterprise Territory Management (ETRM) model; each type groups territories that share a common business purpose, such as sales, service, or collections. The view presents one row per territory type definition, including its application context, lifecycle dates, enablement status, multi-organization identifier, and the standard fifteen descriptive flexfield attribute columns.

Because territory type definitions are owned by a specific operating unit, the view is org-striped: it exposes only the rows visible to the current organization context established by MOAC (Multi-Org Access Control). This makes it suitable for concurrent programs, OBIEE/BI Publisher data models, and integration extracts that must respect the responsibility's organization security profile. The view is identical in structure across 12.1.1 and 12.2.2; no column or filter differences are documented between the two releases.

Underlying Base Objects

JTF_TERR_TYPES is defined over a single base object, JTF_TERR_TYPES_ALL. The view text is a straightforward projection: it selects all listed columns from the _ALL table with no join, union, or aggregation. The org-striping is therefore inherited from the _ALL table's own multi-organization policy, typically implemented through the MO (Multi-Org) view replacement mechanism, which appends a predicate such as ORG_ID = NVL(SYS_CONTEXT(''USERENV'',''ORG_ID''), -99) or an equivalent ORG_ID security filter at runtime.

JTF_TERR_TYPES_ALL is the multi-organization base table that stores both the seeded and user-defined territory types. The corresponding non-org-striped object, JTF_TERR_TYPES (the view documented here), carries no additional business logic; it is a compatibility and reporting layer. No other base tables, lookup views, or foreign key relationships are documented in the ETRM metadata. Consequently, any enrichment — for example joining to JTF_TERRITORIES to count territories per type, or to FND_APPLICATION to resolve APPLICATION_SHORT_NAME — must be performed explicitly by the consuming query.

Key Columns

  • TERR_TYPE_ID — Primary key and surrogate identifier for the territory type. This value is the foreign key referenced by JTF_TERRITORIES.TERR_TYPE_ID and should be used in all joins.
  • NAME — User-visible name of the territory type as displayed in the Territory Manager UI.
  • APPLICATION_SHORT_NAME — Application short name (for example, JTF or ASO) that owns the territory type, enabling product-specific partitioning of territory definitions.
  • ENABLED_FLAG — Y/N indicator of whether the type is active. Disabled types remain queryable but should be filtered out of operational reports.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — Effective date range of the territory type; END_DATE_ACTIVE may be null for open-ended definitions.
  • ORG_ID — Operating unit identifier. This column drives the MOAC security filter and must be included in any extract that isolates a single organization.
  • DESCRIPTION — Free-text description of the type's business purpose.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard WHO-column auditability.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — Descriptive flexfield segments used to store customer-specific extensions on the territory type.

Common Use Cases and Queries

Typical uses include listing available territory types for setup validation, validating that a territory references an enabled type, and feeding territory type metadata into CRM analytics. The query below returns all enabled territory types visible in the current organization context.

SELECT terr_type_id,
       name,
       application_short_name,
       org_id,
       start_date_active,
       end_date_active
FROM   jtf_terr_types
WHERE  enabled_flag = 'Y'
AND    (end_date_active IS NULL OR end_date_active > SYSDATE)
ORDER BY name;

A second pattern counts territories per type, joining to JTF_TERRITORIES to support territory footprint analysis.

SELECT t.name,
       t.terr_type_id,
       COUNT(r.territory_id) AS territory_count
FROM   jtf_terr_types t,
       jtf_territories r
WHERE  r.terr_type_id = t.terr_type_id
AND    t.enabled_flag = 'Y'
GROUP BY t.name, t.terr_type_id
ORDER BY territory_count DESC;

Because the object is a view over a multi-org table, queries should never hard-code ORG_ID unless intentionally extracting a specific operating unit; relying on the MOAC context preserves security compliance. Note that ETRM metadata records this view as "not implemented in this database" for the documented instance, meaning it may be absent from databases where CRM Foundation territory functionality was never provisioned. Existence should therefore be verified against ALL_VIEWS before deployment into a target environment.