Search Results cst_xla_hr_employee_ref_v




Overview
Explore in AI Chat

Overview

CST_XLA_HR_EMPLOYEE_REF_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. Within the ETRM metadata it is catalogued under the BOM (Bills of Material) product family, reflecting its role as a supporting reference object for subledger accounting (XLA) and cost management processing that requires employee information. The view presents a denormalized, reporting-friendly projection of employee records, exposing a small, stable set of columns drawn from the HR_EMPLOYEES source while renaming several of them with an EMP_ prefix to avoid ambiguity when the view is joined to other XLA or costing objects.

The object carries a status of VALID and exists to shield downstream accounting and reporting logic from the full width and complexity of the HR employee entity. Because it surfaces EMPLOYEE_NUM — the search term associated with this lookup — it is frequently used as a lightweight lookup source when an employee number must be resolved to an internal EMPLOYEE_ID, or when a name must be displayed alongside a transaction. Typical consumers include subledger accounting extracts, cost management reports, and custom inquiry pages that need to identify the employee responsible for a transaction without querying HR tables directly.

Underlying Base Objects

The most significant fact about this view is that it does not simply select from HR_EMPLOYEES. While the view text in the catalogue shows a direct projection of HR_EMPLOYEES columns, the documented ETRM dependency list records a richer set of referenced objects: HR_EMPLOYEES (view), HR_GENERAL (package), HR_PERSON_NAME (package), HR_SECURITY (package), and FND_PROFILE (package). The presence of HR_SECURITY and FND_PROFILE indicates that the effective definition incorporates Oracle HRMS security, so rows visible to a session are typically filtered by the security profile and business group of the logged-in user. HR_GENERAL and HR_PERSON_NAME supply the name-formatting and lookup logic that produces the FULL_NAME, FIRST_NAME, LAST_NAME, and MIDDLE_NAME values.

Consequently, querying this view returns only those employees the current responsibility is permitted to see. A query executed under a restricted HR security profile may return fewer rows than a query executed by a user with unrestricted access, even though the underlying HR_EMPLOYEES view contains the full population.

Key Columns

  • EMPLOYEE_ID — The internal surrogate key for the employee, used to join to PER_ALL_PEOPLE_F and other HR entities.
  • EMPLOYEE_NUM — The user-visible employee number, the primary human-readable identifier and the column most often searched.
  • EMP_FULL_NAME — The concatenated formatted name assembled through HR_PERSON_NAME.
  • EMP_FIRST_NAME, EMP_LAST_NAME, EMP_MIDDLE_NAME — Individual name components for localized or custom formatting.
  • EMP_ATTRIBUTE_CATEGORY and EMP_ATTRIBUTE1 through EMP_ATTRIBUTE5 — Descriptive flexfield values carried from the employee record, available for reporting extensions.

Common Use Cases and Queries

The view is most often used as a name-and-number lookup joined to transactional data. A representative query resolves an employee number to an ID:

  • SELECT employee_id, emp_full_name FROM cst_xla_hr_employee_ref_v WHERE employee_num = :p_employee_num;
  • SELECT t.transaction_id, e.emp_full_name FROM xla_transactions t, cst_xla_hr_employee_ref_v e WHERE t.employee_id = e.employee_id;
  • SELECT employee_num, emp_full_name, emp_attribute1 FROM cst_xla_hr_employee_ref_v WHERE emp_last_name LIKE :p_name || '%';

Because security filtering is applied at runtime, these queries should be executed within a responsibility whose HR security profile grants access to the relevant employees. Applications requiring complete employee populations should query HR_EMPLOYEES through supported HR APIs instead.