Search Results jtf_rs_all_resources_vl




Overview
Explore in AI Chat

Overview

JTF_RS_ALL_RESOURCES_VL is an APPS-owned database view within the JTF – CRM Foundation product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to present a unified, consolidated listing of every resource tracked by the Resource Manager subsystem, regardless of whether that resource is an individual person, a group, or a team. The view is a "VL" (validated, translatable) view, meaning it participates in the multilingual (MLS) validation pattern used throughout CRM Foundation, and it is commonly consumed by Oracle's own forms, concurrent programs, and by custom reporting and integration code that needs a single focal point for resource lookups.

Because it merges three separate resource entities into one homogenous result set, the view is foundational for any reporting layer that must enumerate resources without first knowing their category. It abstracts away the differences between the base resource, group, and team structures, exposing a common set of columns such as RESOURCE_ID, RESOURCE_NUMBER, RESOURCE_TYPE, and RESOURCE_NAME.

Underlying Base Objects

The view is defined as a UNION ALL of three component views, documented in ETRM 12.2.2 as follows:

The view also references the HR_GENERAL and HR_SECURITY packages, which indicates that it is subject to Human Resources security rules and organization-based access. This means row visibility can be restricted by the operating user's HR security profile, a consideration that is critical when introducing the view into custom reports. The individual resource branch uses a DECODE over RSC.CATEGORY to derive RESOURCE_TYPE from the underlying category value.

Key Columns

  • RESOURCE_ID — Primary identifier. Carries RESOURCE_ID for individuals, GROUP_ID for groups, and TEAM_ID for teams, so uniqueness is guaranteed per RESOURCE_TYPE.
  • RESOURCE_NUMBER — System-assigned resource number (GROUP_NUMBER or TEAM_NUMBER for those entity types).
  • RESOURCE_TYPE — Derived discriminator. Values include RS_EMPLOYEE, RS_PARTNER, RS_SUPPLIER_CONTACT, RS_PARTY, RS_OTHER, RS_TBH (to be hired), RS_GROUP, and RS_TEAM.
  • RESOURCE_NAME — Display name (resource, group, or team name).
  • EMAIL — Email address; sourced from EMAIL_ADDRESS on groups and teams.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — Effective dating for the resource record.
  • PHONE — Populated only for individual resources.
  • PERSON_PARTY_ID — Party identifier for person resources; NULL elsewhere.
  • ORG_ID / ORG_NAME — Owning operating unit context; NULL for groups and teams.

Common Use Cases and Queries

Typical usage includes LOV-style queries, resource assignment reports, and CRM integration extracts. A representative query enumerating only active employees is:

SELECT resource_id, resource_number, resource_name, email
FROM   jtf_rs_all_resources_vl
WHERE  resource_type = 'RS_EMPLOYEE'
AND    TRUNC(SYSDATE) BETWEEN start_date_active
       AND NVL(end_date_active, TRUNC(SYSDATE));

To aggregate counts by category:

SELECT resource_type, COUNT(*)
FROM   jtf_rs_all_resources_vl
GROUP  BY resource_type;

Because the view applies HR security, report developers should validate that the assigning responsibility has sufficient HR access, and should join to per-entity tables (JTF_RS_RESOURCE_EXTNS, JTF_RS_GROUPS_B, JTF_RS_TEAMS_B) when attributes outside the standard column list are required.