Search Results employee_num




Overview
Explore in AI Chat

Overview

The view APPS.CST_XLA_HR_EMPLOYEE_REF_V is a denormalized reference view defined in the APPS schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It surfaces a curated subset of employee attributes from the HR_EMPLOYEES data source, renaming core HR columns with an EMP_ prefix so that they can be consumed cleanly by costing and subledger accounting (XLA) logic. The view is a constituent of the Costing (CST) and XLA integration methodology, where employee identifiers are required as account derivation sources, transaction reference attributes, and reporting dimensions. Because Oracle Subledger Accounting maps transaction attributes to accounting rules, a stable, aliased employee reference is necessary to avoid name collisions with other HR or FND-based sources. The view therefore serves primarily as a lookup and join target for employee number, name components, and descriptive flexfield attributes within costing-related reports, extraction programs, and custom integrations.

Underlying Base Objects

The view is defined over the HR_EMPLOYEES view, which is the standard HRMS presentation layer for the PER_ALL_PEOPLE_F and related person records. The ETRM metadata documents the following referenced base objects:

  • HR_EMPLOYEES (VIEW) — the direct FROM source; supplies EMPLOYEE_ID, employee number, name fields, and DFF attributes.
  • HR_GENERAL (PACKAGE) — HRMS utility package invoked by the HR_EMPLOYEES view to resolve business group and date-tracked person data.
  • HR_PERSON_NAME (PACKAGE) — supplies formatted name composition (FULL_NAME, FIRST_NAME, LAST_NAME, MIDDLE_NAME).
  • HR_SECURITY (PACKAGE) — enforces row-level security so that a query returns only employees visible to the accessing responsibility.
  • FND_PROFILE (PACKAGE) — resolves profile option values (for example, business group and date-tracked security context) that govern which employee rows are exposed.

Because the base object is a view rather than a table, no direct DML is permitted against CST_XLA_HR_EMPLOYEE_REF_V; it is strictly a read-only reference.

Key Columns

  • EMPLOYEE_ID — the internal surrogate primary key for the person record; used as the join key to PER_ALL_PEOPLE_F, HR_EMPLOYEES, and XLA reference tables.
  • EMPLOYEE_NUM — the user-visible employee number, the column referenced when users search on "employee_num"; corresponds to the person's business-group-scoped number.
  • EMP_FULL_NAME — formatted full display name as resolved by HR_PERSON_NAME.
  • EMP_FIRST_NAME, EMP_LAST_NAME, EMP_MIDDLE_NAME — parsed name components for sorting, concatenation, or locale-specific formatting.
  • EMP_ATTRIBUTE_CATEGORY — the descriptive flexfield context (structure) for the employee record.
  • EMP_ATTRIBUTE1 through EMP_ATTRIBUTE5 — the first five DFF segments, commonly repurposed for employee cost center, project, or labor distribution attributes used in costing.

Common Use Cases and Queries

The view is typically joined to costing and XLA transaction tables to translate an internal EMPLOYEE_ID into a human-recognizable number or name, or to source DFF attributes into accounting derivations and reports.

  • Resolving the employee number for a given employee ID in a costing extract.
  • Producing labor-distribution reports by employee name and cost center DFF.
  • Populating XLA reference attributes for subledger journal lines tied to labor transactions.
  • Validating employee visibility under HR security before exposing data in a custom concurrent program.

Sample query retrieving employee number and name for a specific person:

SELECT employee_id, employee_num, emp_full_name
FROM apps.cst_xla_hr_employee_ref_v
WHERE employee_num = :p_employee_num;

Sample join to a costing transaction source on EMPLOYEE_ID:

SELECT h.employee_num, h.emp_full_name, h.emp_attribute1
FROM apps.cst_xla_hr_employee_ref_v h,
    apps.cst_transactions t
WHERE h.employee_id = t.employee_id
  AND t.organization_id = :p_org_id;

Because HR_SECURITY and FND_PROFILE govern row visibility, results are automatically restricted to the business group and security profile of the querying responsibility; developers should not hard-code business group predicates that would override this behavior.