Results for “hr_employee_api_v”

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

Overview

HR_EMPLOYEE_API_V is a public APPS-owned view in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, registered under the PER (Human Resources) product family with a VALID status. Functionally, it is a flattened projection of the PER_ALL_PEOPLE_F person record, exposing person attributes as they exist on a single dated row. It is used by the Oracle HRMS public APIs and by external integrations that need to read or feed person data without navigating the underlying date-tracked Person entity directly. The view is commonly referenced as the query-side counterpart to HR_EMPLOYEE_API, which performs the corresponding insert and update operations against the same person record.

The name is significant: it exposes the person row in a form shaped for API interaction, not a denormalized join of assignment, address, and service data. Reports that traverse the view should therefore not assume one row per employee across time; they must resolve the date-tracked row themselves.

Underlying Base Objects

ETRM documents the view as referencing the following synonyms:

• PER_PERSON_TYPES — supplies the person type definition associated with PERSON_TYPE_ID.
• PER_ALL_PEOPLE_F — the primary date-tracked base table supplying the person rows.
• PER_PERIODS_OF_SERVICE — periods of service for employees and applicants.
• PER_ALL_ASSIGNMENTS_F — the date-tracked assignment records.
• PER_ADDRESSES — address rows associated with the person.

Because the documented view text selects from PER (the base table, aliased PER), EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are carried directly from PER_ALL_PEOPLE_F. The presence of the other synonyms reflects dependencies pulled in by the compiled definition, typically through subqueries or outer joins for current person type, current assignment, or current address information.

Key Columns

The view exposes the full person attribute set. The date-tracking columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE define the validity window of each personality row and are the columns most often searched for as "asg_effective_start_date." Although ASG_EFFECTIVE_START_DATE is the assignment-level date column, the person-level equivalent is EFFECTIVE_START_DATE; confusion between the two is common in HRMS queries.

• PERSON_ID — primary key of the person.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked validity of the person row.
• BUSINESS_GROUP_ID — operating unit/legislative grouping for the person.
• PERSON_TYPE_ID — foreign key to PER_PERSON_TYPES.
• EMPLOYEE_NUMBER, APPLICANT_NUMBER — identification numbers.
• CURRENT_EMPLOYEE_FLAG, CURRENT_APPLICANT_FLAG, CURRENT_EMP_OR_APL_FLAG — flags indicating current status.
• START_DATE, PROJECTED_START_DATE — service start indicators.
• LAST_NAME, FIRST_NAME, FULL_NAME, KNOWN_AS, PRE_NAME_ADJUNCT, SUFFIX — name components.
• EMAIL_ADDRESS, WORK_TELEPHONE, OFFICE_NUMBER — contact attributes.
• REQUEST_ID, ATTRIBUTE1..ATTRIBUTE30, ATTRIBUTE_CATEGORY — DFF and API audit columns.

Common Use Cases and Queries

Typical scenarios include point-in-time employee extraction for payroll or benefits interfaces, reconciliation of person records loaded through HR_EMPLOYEE_API, and reporting on current employees filtered by business group.

• Current employee listing by business group.
• Point-in-time reconstruction using EFFECTIVE_START_DATE.
• Correlation of person rows loaded via API using REQUEST_ID.

A representative query that retrieves the current row for a person:

SELECT person_id, employee_number, full_name, effective_start_date, effective_end_date FROM apps.hr_employee_api_v WHERE business_group_id = :bg AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because the view is not secured by MO or HRMS security profiles in the same manner as secured business views, application-level filtering on BUSINESS_GROUP_ID and security profiles is required when the view is queried directly by reports.