Results for “jtf_rs_resource_extns_vl”

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

Overview

JTF_RS_RESOURCE_EXTNS_VL is the primary translatable (VL) view in the CRM Foundation (JTF) product that consolidates all resource master data within Oracle E-Business Suite 12.1.1 and 12.2.2. Owned by the APPS schema and carrying a VALID status, the view is described in the ETRM metadata as the "Main resource view that stores all resource data." In this context, a "resource" is any entity that can be assigned to, or perform work for, a service request, task, or interaction — typically employees, partners, suppliers, and other parties modeled through Resource Manager. The _VL suffix indicates that the view joins a base table with its translation table and filters the language via USERENV('LANG'), returning the resource name in the session's current language. Because it is the principal consolidated source for resource attributes, the view is widely used as the authoritative reporting and integration surface for downstream modules such as TeleService, Field Service, Service Contracts, and CRM resource scheduling.

Underlying Base Objects

The ETRM documentation lists the referenced base objects as JTF_RS_RESOURCE_EXTNS and JTF_RS_RESOURCE_EXTNS_TL, both accessed via synonyms. The view text confirms an inner join between the two on RESOURCE_ID and CATEGORY:

• JTF_RS_RESOURCE_EXTNS (B) — the non-translatable base table holding the working attributes of each resource, including operational, costing, compensation, and descriptive flexfield columns.
• JTF_RS_RESOURCE_EXTNS_TL (T) — the translation table supplying the language-specific RESOURCE_NAME; the join condition T.LANGUAGE = USERENV('LANG') ensures only the current session language is returned.

Only the RESOURCE_NAME column is drawn from the translation table; every other exposed column originates from the B alias.

Key Columns

The view exposes the full column set of the base table, prefixed as B.*, plus T.RESOURCE_NAME. Notable columns include:

• RESOURCE_ID / CATEGORY — the composite identifier of a resource; the join keys to the translation table.
• ROW_ID / OBJECT_VERSION_NUMBER — ROWID plus optimistic locking column used by the Resource Manager API and forms.
• RESOURCE_NUMBER, SOURCE_ID, ADDRESS_ID, CONTACT_ID, MANAGING_EMPLOYEE_ID — links to the source party, address, contact record, and the managing employee.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the active date range of the resource.
• COST_PER_HR, COST_CENTER, CHARGE_TO_COST_CENTER, COMPENSATION_CURRENCY_CODE, COMMISSIONABLE_FLAG — costing and compensation attributes used by field service and billing flows.
• HOLD_REASON_CODE, HOLD_PAYMENT — indicators controlling whether the resource is on hold or excluded from payment.
• PERSON_PARTY_ID, USER_ID, IES_AGENT_LOGIN — identity links into the trading community (HZ) and HCM/User model.
• SOURCE_* columns — a denormalized set of source party attributes (NAME, EMAIL, PHONE, ORG_ID, ADDRESS1..4, CITY, STATE, POSTAL_CODE, COUNTRY, MGR_NAME, BUSINESS_GRP_NAME, FIRST/MIDDLE/LAST_NAME, CATEGORY, STATUS, JOB_ID, MOBILE_PHONE, PAGER).
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield segments.
• WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — audit fields.

Common Use Cases and Queries

Typical reporting scenarios include listing active resources for scheduling, joining resource names to service request assignments, and exporting costing data. Because the view already resolves the language-specific name, queries do not need to reference the translation table explicitly. Example:

SELECT resource_id
     , resource_number
     , resource_name
     , category
     , start_date_active
     , end_date_active
     , user_name
  FROM apps.jtf_rs_resource_extns_vl
 WHERE category = 'EMPLOYEE'
   AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
                   AND NVL(end_date_active, SYSDATE + 1);

A further example joining resources to their managing employee and business group:

SELECT v.resource_name
     , v.source_email
     , v.source_phone
     , v.source_org_name
  FROM apps.jtf_rs_resource_extns_vl v
 WHERE v.source_business_grp_id = :p_org_id
   AND v.commissionable_flag = 'Y';

The view is suitable for read-only reporting and integration; updates must go through the supported Resource Manager APIs, not the view itself.