Results for “jtf_rs_res_emp_vl”

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

Overview

JTF_RS_RES_EMP_VL is an APPS-owned database view within the JTF – CRM Foundation product family. It presents a small, denormalized slice of attributes for resources whose category is Employee, joining the CRM resource repository to Oracle HRMS person and assignment data. In Oracle EBS 12.1.1 and 12.2.2 the view retains the same definition and remains VALID, providing a stable, read-only interface for reporting and integration against employee-type resources. Its primary role is to resolve a resource identifier into human-readable identity details: employee name components, employee number, work telephone and email address.

Because it exposes only a subset of columns, the view functions as a convenient lookup rather than a comprehensive resource master. It is frequently consumed by CRM and service modules, concurrent programs, and custom reports that need to display or validate the person behind a resource.

Underlying Base Objects

The view is defined over the CRM resource extension table together with Oracle HRMS person and assignment views. The documented referenced base objects are JTF_RS_RESOURCE_EXTNS (accessed via synonym), PER_PEOPLE_F (view), PER_ASSIGNMENTS_F (view), and PER_PERIODS_OF_SERVICE (synonym). Security and name formatting are supplied through the packages HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY.

• JTF_RS_RESOURCE_EXTNS — the CRM resource row, filtered to CATEGORY = 'EMPLOYEE'; its SOURCE_ID maps to the HR person.
• PER_PEOPLE_F — supplies FIRST_NAME, LAST_NAME, MIDDLE_NAMES, FULL_NAME, EMPLOYEE_NUMBER, WORK_TELEPHONE and EMAIL_ADDRESS, restricted to the currently effective date-tracked record.
• PER_ASSIGNMENTS_F — restricts to the primary employee assignment (PRIMARY_FLAG = 'Y', ASSIGNMENT_TYPE = 'E').
• PER_PERIODS_OF_SERVICE — used to align the assignment row to the person's period of service and effective termination timing.

The joins are date-effective aware: TRUNC(SYSDATE) must fall within the person's effective range, and assignment dates are compared against the maximum of actual termination date or the current date. This ensures only currently valid employee resources are returned.

Key Columns

• RESOURCE_ID — the CRM resource identifier, the primary key used across CRM modules.
• USER_ID — the associated application user account, if any, linking the resource to FND_USER.
• EMPLOYEE_ID — the HR person identifier (PERSON_ID) behind the resource.
• FIRST_NAME, LAST_NAME, MIDDLE_NAME, FULL_NAME — name components and the formatted full name.
• EMPLOYEE_NUMBER — the HR employee number; the view requires this to be non-null.
• PHONE — the HR work telephone number.
• EMAIL — the HR email address.

Common Use Cases and Queries

Typical uses include resolving a resource to a display name in CRM reports, validating that an employee resource is currently active, and populating notification or assignment lists. Because HR_SECURITY participates, row visibility respects the HR security profile of the querying user.

A representative query retrieves display attributes for a set of resources:

• SELECT resource_id, employee_id, full_name, employee_number, phone, email FROM jtf_rs_res_emp_vl WHERE resource_id = :p_resource_id;
• SELECT full_name, email FROM jtf_rs_res_emp_vl WHERE employee_number = :p_emp_num;
• SELECT r.resource_id, r.full_name FROM jtf_rs_res_emp_vl r WHERE UPPER(r.last_name) LIKE UPPER(:p_name || '%');

All access is read-only; the view should never be used as a DML target.