Search Results jtf_terr_resources_v




Overview
Explore in AI Chat

Overview

The APPS.JTF_TERR_RESOURCES_V view is a CRM Foundation (JTF) reporting and integration object in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the assignment of resources to territories maintained by Oracle Territory Manager (part of the Foundation/ETRM stack). A territory is a named collection of customers, prospects, or other entities grouped by geography, product, or business rule; the resources assigned to that territory are the sales representatives, groups, suppliers, or other parties responsible for working it. This view consolidates those resource assignments into a single denormalized result set, resolving the raw TERR_RSC rows into business-meaningful names.

The view does not store data of its own; it is a read-only projection. Its principal value in reporting and integration is that it returns decoded descriptive text — resource type names, group names, resource names, and role names — alongside the numeric foreign keys. This allows BI Publisher reports, OBIEE/OBIA extracts, custom concurrent programs, and interface programs to consume territory-resource assignments without performing their own joins to the CRM lookup views or calling the territory PL/SQL API directly. Because the object is a view under APPS, standard read privileges and the usual EBS security model (org-level access via ORG_ID) apply.

Underlying Base Objects

The view text is defined over four documented base objects:

  • JTF_TERR_RSC (SYNONYM) — the driving table, aliased JTRA, holding the physical territory-resource assignment rows, including the primary key TERR_RSC_ID and the audit columns. The synonym resolves to the underlying JTF_TERR_RSC table in the JTF schema.
  • JTF_OBJECTS_VL (VIEW) — aliased JO, supplying the resource type name. The join is on OBJECT_CODE, with a DECODE mapping the supplier resource type to its contact equivalent.
  • JTF_RS_ROLES_VL (VIEW) — aliased JRRV, supplying the role name for the assignment. This is an outer join, so assignments with an unrecognized role still appear.
  • JTF_TERRITORY_RESOURCE_PVT (PACKAGE) — the territory resource private API package, invoked for the derived columns GROUP_NAME and RESOURCE_NAME via GET_GROUP_NAME and GET_RESOURCE_NAME. Because these are PL/SQL function calls embedded in the SELECT list, the view is not a simple join and its performance is sensitive to row counts and function context.

Key Columns

  • TERR_RSC_ID — primary key of the territory-resource assignment; joins back to JTF_TERR_RSC.
  • TERR_ID — the territory to which the resource is assigned; join to the territory definition tables.
  • RESOURCE_ID / RESOURCE_TYPE — the assigned resource and its type code; RESOURCE_TYPE_NAME provides the decoded label from JTF_OBJECTS_VL.
  • RESOURCE_NAME — resolved resource name returned by JTF_TERRITORY_RESOURCE_PVT.GET_RESOURCE_NAME, with supplier types mapped to the contact equivalent.
  • GROUP_ID / GROUP_NAME — the resource group and its resolved name.
  • ROLE / ROLE_NAME — the role code on the assignment and its decoded name from JTF_RS_ROLES_VL.
  • PRIMARY_CONTACT_FLAG — indicates whether the resource is the primary contact for the territory.
  • FULL_ACCESS_FLAG — indicates whether the resource has full access to the territory.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range of the assignment; used to filter currently active resources.
  • PERSON_ID — person identifier for person-type resources.
  • ORG_ID — operating unit / organization context, the basis for multi-org (VPD) filtering.
  • Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and ROW_ID — standard WHO columns and the physical row identifier.

Common Use Cases and Queries

Typical scenarios include territory resource roster reports, validation of sales-team coverage, and inbound/outbound interfaces that must resolve which resource owns a territory. Note that start and end dates should normally be applied in the caller's predicate, since the view itself does not restrict to active assignments.

Current active resources for a given territory:

  • SELECT terr_rsc_id, terr_id, resource_id, resource_name, resource_type_name, role_name, group_name, primary_contact_flag FROM jtf_terr_resources_v WHERE terr_id = :p_terr_id AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);

Primary contacts by territory, restricted to an operating unit:

  • SELECT terr_id, resource_name, role_name FROM jtf_terr_resources_v WHERE primary_contact_flag = 'Y' AND org_id = :p_org_id ORDER BY terr_id, resource_name;

Because RESOURCE_NAME and GROUP_NAME are computed through JTF_TERRITORY_RESOURCE_PVT, restrict the result set by TERR_ID, ORG_ID, or date predicates before selecting these columns on large volumes.