Search Results per_locations

Overview

JTF.JTF_RS_RESOURCE_EXTNS is the central repository of resource master data within Oracle E-Business Suite Release 12.1.1 and 12.2.2. The table stores descriptive, contact, organizational, and personal attributes for every resource known to the applications, regardless of whether that resource originates from Oracle HRMS, Oracle Trading Community Architecture (HZ), or a third-party or legacy source. Each row represents a single resource, identified by the surrogate primary key RESOURCE_ID. In ETRM metadata the table carries FND Design Data reference JTF.JTF_RS_RESOURCE_EXTNS, resides in the APPS_TS_TX_DATA tablespace (PCTFREE 10), and is documented with 86 columns and status VALID. Functionally, it acts as the single point of convergence for resources used across Sales, Service, Marketing, Collections, Contracts, and Territory Management, which is why it is referenced by an unusually large number of downstream tables.

From a Data Vault modeling perspective, the heuristic classification of this object is hub. The metadata identifies JTF_RS_RESOURCE_EXTNS as a hub, meaning it is best understood as a business-key-bearing entity whose primary key (RESOURCE_ID) is referenced by many surrounding link and satellite structures. This classification is a modeling suggestion derived from the foreign-key topology, not a physical constraint imposed by the EBS schema.

Key Information Stored

The table contains both the surrogate identity of a resource and the business keys that resolve it to external systems. The most operationally significant columns include:

• RESOURCE_ID — the surrogate primary key (index JTF_RS_RESOURCE_EXTNS_U1). Every foreign key in the surrounding schema resolves to this column.
• RESOURCE_NUMBER — a unique business identifier for the resource (index JTF_RS_RESOURCE_EXTNS_U2).
• USER_ID — the associated FND user, uniquely constrained by index JTF_RS_RESOURCE_EXTNS_U3. It links the resource to an application login.
• CATEGORY — classifies the resource type; this column participates in foreign key JTF_RS_RESOURCE_EXTNS_NU3 toward JTF_OBJECTS_B.
• CONTACT_ID — reference to an HZ_ORG_CONTACTS record (index JTF_RS_RESOURCE_EXTNS_NU10).
• SUPPORT_SITE_ID — reference to an HZ_PARTY_SITES record (index JTF_RS_RESOURCE_EXTNS_NU12).
• MANAGING_EMPLOYEE_ID — the resource's manager (index JTF_RS_RESOURCE_EXTNS_NU2).
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, used for multi-organization data partitioning.
• ASSIGNED_TO_GROUP_ID — identifies the resource group to which the resource belongs (index JTF_RS_RESOURCE_EXTNS_NU11).
• SOURCE_* columns — a denormalized set of source attributes including SOURCE_NAME, SOURCE_NUMBER, SOURCE_EMAIL, SOURCE_PHONE, SOURCE_CITY, SOURCE_STATE, SOURCE_COUNTRY, SOURCE_JOB_TITLE, and SOURCE_MGR_NAME. Several are indexed via function-based indexes (FN1 through FN6) using UPPER() for case-insensitive search.
• PERSON_PARTY_ID — links the resource to the TCA person party model.
• START_DATE_ACTIVE / END_DATE_ACTIVE — define the effective lifecycle window of the resource.
• ATTRIBUTE1–ATTRIBUTE15 and ATTRIBUTE_CATEGORY — the standard DFF extension columns for client-specific descriptive flexfields.
• OBJECT_VERSION_NUMBER — supports optimistic locking during concurrent updates.

The presence of SOURCE_* columns reflects the table's role as an integration point: HR, HZ, and external systems populate these fields so that Oracle EBS applications can consume resource data uniformly without querying each source system directly.

Common Use Cases and Queries

Because resources drive ownership, assignment, and access throughout EBS, JTF_RS_RESOURCE_EXTNS is heavily queried in reporting and integration scenarios. Typical patterns include:

• Resolving resource identity for a user session. Applications frequently join USER_ID to FND_USER to display the resource's display name and email: SELECT r.resource_number, r.source_name, r.source_email FROM jtf_rs_resource_extns r, fnd_user u WHERE r.user_id = u.user_id AND u.user_name = :user_name;
• Manager reporting hierarchies. MANAGING_EMPLOYEE_ID (index NU2) supports recursive or self-referencing queries to build reporting lines.
• Territory and assignment lookups. The resource_id is the join column through JTF_TERR_RSC_ALL, JTF_TTY_ALIGNMENTS, and related territory tables.
• Sales and collections alignment. JTF_RS_SALESREPS, AR_COLLECTORS, and AS_SALES_CREDITS all resolve resource attributes here for dashboards and credit assignment.
• Party and contact integration. The CONTACT_ID, SUPPORT_SITE_ID, and PERSON_PARTY_ID columns link resources back to TCA, supporting 360-degree party views.
• Case-insensitive name search. Function-based indexes FN1–FN6 allow efficient queries such as WHERE UPPER(SOURCE_LAST_NAME) = UPPER(:name).

For 12.2.2, the additional 86-column physical schema (including FS_SETUP_COMPLETE and SUPPORT_SITE) provides a superset of the 12.1.1 definition, so queries should always reference columns documented as present in the target release.

Related Objects

The table participates in an extensive web of dependencies. The most significant related objects include:

• JTF_OBJECTS_B — referenced via CATEGORY, defining the resource category taxonomy.
• HZ_ORG_CONTACTS — referenced via CONTACT_ID for the resource's contact identity.
• HZ_PARTY_SITES — referenced via SUPPORT_SITE_ID for the resource's site.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for security partitioning.
• JTF_RS_SALESREPS — the sales-representative extension, joined on RESOURCE_ID; a key table for quoting and territory assignment.
• JTF_RS_GROUP_MEMBERS and JTF_RS_TEAM_MEMBERS — group and team membership, joined on RESOURCE_ID and TEAM_RESOURCE_ID.
• AS_SALES_CREDITS — revenue credit allocation for resources, joined via SALESFORCE_ID.
• CS_INCIDENTS_ALL_B — service incidents owned by a resource, joined via INCIDENT_OWNER_ID.
• CS_CONTACTS — relates contacts to resources via CONTACT_ID.
• AMS_ACT_RESOURCES — marketing activity resource assignments, joined on RESOURCE_ID; and OKC_K_ACCESSES — contract access granted to resources, joined on RESOURCE_ID.

These dependencies confirm that JTF_RS_RESOURCE_EXTNS behaves as a foundational hub: changes to a resource record cascade operationally across sales, service, marketing, collections, contracts, and territory modules throughout Oracle EBS 12.1.1 and 12.2.2.