Search Results ex_emp




Overview
Explore in AI Chat

Overview

APPS.HR_H2PI_EMPLOYEES_V is a reporting and integration view in Oracle E-Business Suite (12.1.1 and 12.2.2) that exposes a flattened, denormalized projection of Oracle Human Resources (HRMS) person data. The name incorporates the "H2PI" token, indicating it belongs to the HR-to-Third-Party-Integration (H2PI) family of objects used for extracting employee and applicant records for downstream payroll, benefit, or third-party interfaces. The view joins the effective-dated person table to the person type lookup and normalizes the system person type value, mapping the internal codes EMP_APL and EX_EMP_APL to EMP and EX_EMP respectively. This normalization collapses the distinction between "employee" and "employee applicant" (and their ex-employee counterparts) so that consumers see a simplified, consistent classification. A user searching for emp_apl reaches this object because the view's filter explicitly includes the EMP_APL and EX_EMP_APL system person types.

Underlying Base Objects

Per the documented metadata, the view is owned by APPS and is defined over two base objects, both resolved through synonyms:

  • PER_ALL_PEOPLE_F — the effective-dated, date-tracked core table storing person records. Each person may have multiple rows representing historical and future-dated versions of the record.
  • PER_PERSON_TYPES — the person type definition table, joined on PERSON_TYPE_ID to supply the SYSTEM_PERSON_TYPE value used in the filter and the DECODE transformation.

The view is therefore a direct consumer of the primary HR person entity and its type classification, with no aggregation or summarization applied. Because it selects from a _F (date-tracked) table, output rows remain effective-dated via EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.

Key Columns

The view exposes a broad set of person attributes suitable for flat-file or interface export:

  • person_id, business_group_id, employee_number — primary identifiers and the multi-tenant business group key.
  • effective_start_date, effective_end_date — date-track boundaries; consumers must filter on the desired effective date to avoid returning multiple versions per person.
  • Person name and demographics — last_name, first_name, middle_names, suffix, pre_name_adjunct, date_of_birth, sex, marital_status, nationality, national_identifier, date_of_death, email_address.
  • Employment attributes — start_date, title, rehire_reason, office_number, expense_check_send_to_address, registered_disabled_flag, correspondence_language.
  • Descriptive flexfield columns — PER_INFORMATION1 through PER_INFORMATION30, plus ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30.
  • Normalized person type — the DECODE expression returns EMP, EX_EMP, or a passthrough value for other system person types. Note that the select list references PPT.SYSTEM_PERSON_TYPE but the resulting column is unnamed in the text, so downstream queries typically alias it.

Common Use Cases and Queries

Typical uses include bulk extraction of current employee and applicant data for third-party interfaces, reconciliation of person records against external systems, and reporting on the EMP_APL population.

Typical current-row query, restricted to the employee/applicant population and a single effective date:

  • SELECT person_id, employee_number, last_name, first_name, email_address, effective_start_date, effective_end_date FROM apps.hr_h2pi_employees_v WHERE business_group_id = :bg_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Applicant-focused extract mirroring the emp_apl search intent, filtering on the raw person type via a join or on the normalized type as exposed:

  • SELECT v.employee_number, v.last_name, v.first_name FROM apps.hr_h2pi_employees_v v, apps.per_person_types t WHERE v.person_id IS NOT NULL AND t.system_person_type IN ('EMP_APL','EX_EMP_APL');

Because the view performs no date filtering internally, all queries should constrain EFFECTIVE_START_DATE/EFFECTIVE_END_DATE to avoid duplicate historic rows per person.