Search Results csf_plan_terrs_pk

Overview

CSF_PLAN_TERRS is a table in the CSF (Field Service) product schema of Oracle E-Business Suite, present and valid in both release 12.1.1 and 12.2.2. It is an associative, or relationship, table whose stated purpose is to map territories to resource groups. In practice, each row records that a specific resource group participates in a specific service territory, forming the many-to-many bridge between the territory model maintained by Oracle Territory Manager and the resource group model maintained by Oracle Resource Manager.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this object heuristically as a link. This is a modeling suggestion only: the table carries no descriptive attributes of its own beyond audit and security columns, and it exists purely to resolve the relationship between two independent business entities, which is the canonical signature of a link table rather than a hub or satellite.

Key Information Stored

The documented physical schema contains ten columns. The most significant are:

• PLAN_TERR_ID — the surrogate primary key, enforced by the CSF_PLAN_TERRS_PK constraint. It uniquely identifies each territory-to-group association and is the column downstream tables and APIs should reference.
• TERR_ID — foreign key to JTF_TERR_ALL, identifying the territory side of the association.
• GROUP_ID — foreign key to JTF_RS_GROUPS_B, identifying the resource group side of the association.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant and operating-unit security filtering.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Application Framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created and last changed each association and when.

The business-key candidate is captured by the unique index CSF_PLAN_TERRS_U1 on the pair (TERR_ID, GROUP_ID), confirming that a given group may be linked to a given territory only once. The surrogate PLAN_TERR_ID remains the efficient join target, while the unique index preserves the integrity of the natural pairing.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include listing all groups assigned to a territory, finding every territory in which a group participates, and auditing the completeness of territory setup before service dispatch or scheduling runs.

• Retrieve the groups belonging to a territory: SELECT group_id FROM csf.csf_plan_terrs WHERE terr_id = :terr_id;
• Retrieve the territories for a group: SELECT terr_id FROM csf.csf_plan_terrs WHERE group_id = :group_id;
• Join to the territory definition for names and other descriptive data: SELECT t.terr_id, t.terr_name, p.group_id FROM csf.csf_plan_terrs p, jtf_terr_all t WHERE p.terr_id = t.terr_id;
• Detect duplicate or orphaned mappings using the unique index columns and the foreign keys to JTF_TERR_ALL and JTF_RS_GROUPS_B.

Because the table is compact and indexed on the territory and group pair, these queries perform well and are suitable for embedded reporting, concurrent program extracts, and territory-coverage analysis in the Field Service module.

Related Objects

The FK structure documents the principal dependencies, alongside the objects that reference this table through the primary key:

• JTF_TERR_ALL — joined on CSF_PLAN_TERRS.TERR_ID = JTF_TERR_ALL.TERR_ID; the master territory definition.
• JTF_RS_GROUPS_B — joined on CSF_PLAN_TERRS.GROUP_ID = JTF_RS_GROUPS_B.GROUP_ID; the base resource group table.
• FND_SECURITY_GROUPS — joined on CSF_PLAN_TERRS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; governs row-level access.
• CSF_PLAN_TERRS_PK / CSF_PLAN_TERRS_U1 — primary and unique constraints that other objects may reference via PLAN_TERR_ID or the (TERR_ID, GROUP_ID) pair.