Results for “jtf_rs_resource_extns”

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

Overview

JTF_RS_RESOURCE_EXTNS is the central resource master table within the JTF - CRM Foundation product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definitive attribute set for every "resource" recognized by the CRM application stack — individuals and groups sourced from Oracle HRMS (employees and applicants), Oracle Trading Community Architecture (HZ parties and contacts), and vendor or external sources. The table is owned by the JTF schema and is documented as VALID in ETRM for both release levels, containing 86 columns. Its stated purpose is to consolidate the information needed to identify, qualify, schedule, cost, and secure a resource across CRM modules such as Sales, Marketing (AMS/AMV), Service (CS/CSD/CSS), TeleSales (JTF_TTY), Partner Management (PV), and Contracts (OKC).

From a dimensional modeling perspective, the documented FK topology — a single surrogate primary key (RESOURCE_ID) with a very large number of inbound foreign keys from transaction and fact-like tables, plus a small number of outbound lookups to JTF_OBJECTS_B, HZ_PARTY_SITES, HZ_ORG_CONTACTS, and FND_SECURITY_GROUPS — suggests classifying this object heuristically as a hub in Data Vault terms. It anchors a business key (the resource) and is referenced by numerous satellites, denormalized helper tables, and dependent transactional entities.

Key Information Stored

The table's surrogate primary key is RESOURCE_ID, enforced by the constraint JTF_RS_RESOURCE_EXTNS_PK. Two additional unique indexes, JTF_RS_RESOURCE_EXTNS_U2 on RESOURCE_NUMBER and JTF_RS_RESOURCE_EXTNS_U3 on USER_ID, identify RESOURCE_NUMBER and USER_ID as business-key candidates — the externally meaningful identifiers used to reference a resource. The most operationally significant columns include:

• RESOURCE_ID — surrogate primary key and the join key consumed by nearly every dependent CRM table.
• RESOURCE_NUMBER and USER_ID — unique business identifiers; USER_ID links the resource to an FND_USER login.
• CATEGORY — foreign key to JTF_OBJECTS_B, defining the resource type (for example, employee, partner, group).
• SOURCE_ID, SOURCE_NAME, SOURCE_NUMBER — back-reference to the originating HR or HZ record.
• CONTACT_ID (FK to HZ_ORG_CONTACTS) and PERSON_PARTY_ID — link to the TCA person/contact model.
• SUPPORT_SITE_ID (FK to HZ_PARTY_SITES) and SUPPORT_SITE — service-site association.
• MANAGING_EMPLOYEE_ID and SOURCE_MGR_ID — supervisory hierarchy references.
• START_DATE_ACTIVE and END_DATE_ACTIVE — effective-dating window controlling resource availability.
• COST_PER_HR, COMPENSATION_CURRENCY_CODE, COST_CENTER, CHARGE_TO_COST_CENTER — costing and charging attributes.
• TIME_ZONE, PRIMARY_LANGUAGE, SECONDARY_LANGUAGE — scheduling and localization attributes.
• SECURITY_GROUP_ID (FK to FND_SECURITY_GROUPS) — multi-org style security partitioning.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS DFF/descriptive flexfield columns.
• The SOURCE_* family (SOURCE_EMAIL, SOURCE_PHONE, SOURCE_CITY, SOURCE_COUNTRY, SOURCE_JOB_TITLE, and related) — a denormalized snapshot of the source HR/HZ record for fast display.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus OBJECT_VERSION_NUMBER for optimistic locking.

Translatable descriptive text is held separately in JTF_RS_RESOURCE_EXTNS_TL, keyed by RESOURCE_ID.

Common Use Cases and Queries

The table is queried whenever an application needs to resolve a resource identifier to a display name, validate active status for a given date, or join a CRM transaction to its owner or assignee. Typical patterns include:

• Resolving a resource owner on sales or marketing records: join AMS_CAMPAIGNS_ALL_B.OWNER_USER_ID, AS_LEADS_ALL.OWNER_SALESFORCE_ID, or OZF_OFFERS.OWNER_ID back to JTF_RS_RESOURCE_EXTNS.USER_ID or RESOURCE_ID.
• Date-effective resource lookups, filtering on TRUNC(SYSDATE) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE).
• Sales credit and territory reporting via AS_SALES_CREDITS.SALESFORCE_ID, AS_SALES_CREDITS_DENORM.SALESFORCE_ID, and JTF_TERR_RSC_ALL.RESOURCE_ID.
• Service ownership and escalation through CS_INCIDENTS_ALL_B.INCIDENT_OWNER_ID and CSD_REPAIRS.RESOURCE_ID.
• Costing and charge-back analysis using COST_PER_HR, COST_CENTER, and CHARGE_TO_COST_CENTER.
• Security-scoped reporting, restricting results by SECURITY_GROUP_ID.

A representative query retrieves all active resources with their party linkage and manager:

SELECT r.resource_id, r.resource_number, r.user_id, t.resource_name, r.category, r.managing_employee_id FROM jtf_rs_resource_extns r, jtf_rs_resource_extns_tl t WHERE r.resource_id = t.resource_id AND t.language = USERENV('LANG') AND TRUNC(SYSDATE) BETWEEN r.start_date_active AND NVL(r.end_date_active, SYSDATE);

Related Objects

• JTF_RS_RESOURCE_EXTNS_TL — translation table joined on RESOURCE_ID, supplying language-specific resource names and descriptions.
• JTF_RS_RESOURCE_DENORM — denormalized resource snapshot referencing RESOURCE_ID and PARENT_RESOURCE_ID, used for reporting hierarchies.
• JTF_RS_GROUP_MEMBERS and JTF_RS_TEAM_MEMBERS — group and team membership joined via RESOURCE_ID and TEAM_RESOURCE_ID.
• JTF_RS_ROLE_RELATIONS — role assignment via ROLE_RESOURCE_ID.
• JTF_RS_SALESREPS — the sales-representative extension keyed by RESOURCE_ID.
• AMS_CAMPAIGNS_ALL_B, AMS_EVENT_HEADERS_ALL_B, AMS_EVENT_OFFERS_ALL_B — marketing entities referencing OWNER_USER_ID, COORDINATOR_ID, and related resource owners.
• AS_SALES_CREDITS / AS_SALES_CREDITS_DENORM — sales-credit facts joined on SALESFORCE_ID.
• CS_INCIDENTS_ALL_B and CSD_REPAIRS — service transactions referencing resource owners and repair resources.
• HZ_PARTY_SITES and HZ_ORG_CONTACTS — outbound lookups through SUPPORT_SITE_ID and CONTACT_ID.
• FND_SECURITY_GROUPS and FND_USER — security and login provisioning through SECURITY_GROUP_ID and USER_ID.

The breadth of inbound foreign keys makes JTF_RS_RESOURCE_EXTNS one of the most heavily joined reference tables in the CRM Foundation layer, and its RESOURCE_ID value is the de facto global handle for any actor — person, representative, or group — participating in CRM transactions.