Search Results projected_termination_date

Overview

HR_EDWBV_WRK_SPRTN_FCV is a view owned by the APPS schema within the Oracle E-Business Suite Human Resources Intelligence (HRI) product. It belongs to the ETRM (Enterprise Territory and Resource Management) family of HR analytics objects, specifically the "EDW" (Enterprise Data Warehouse) work-separation fact view layer. Its name decomposes to HR_EDW (HR Data Warehouse), BV (Business View), WRK (Work), SPRTN (Separation), and FCV (Fact Coded View). In Oracle EBS 12.1.1 and 12.2.2, this view functions as a semantic bridge between Human Resources operational tables and the dimensional model used for HR workforce analytics, exposing a separation fact record keyed by period of service, person, assignment, and instance code.

The view does not store data; it computes surrogate keys and derived attributes at query time so downstream HR Intelligence ETL processes and Business Intelligence Publisher reports can load or query the separation fact without duplicating key-building logic.

Underlying Base Objects

The documented metadata lists no referenced base objects, but the embedded view text reveals the underlying HR source structures. The alias PPS corresponds to the PER_PERIODS_OF_SERVICE table, which supplies PERIOD_OF_SERVICE_ID, PERSON_ID, DATE_START, and the four termination date columns: ACTUAL_TERMINATION_DATE, PROJECTED_TERMINATION_DATE, ACCEPTED_TERMINATION_DATE, and NOTIFIED_TERMINATION_DATE. The alias PASG refers to PER_ALL_ASSIGNMENTS_F or its equivalent assignment table, supplying ASSIGNMENT_ID, LOCATION_ID, and grade references. PEO represents PER_ALL_PEOPLE_F, providing DATE_OF_BIRTH used for age-band derivation. The alias SNP refers to a snapshot table (PER_SNAPSHOTS-style) supplying SNAPSHOT_DATE for service-length calculation.

The view also invokes two PL/SQL API packages: EDW_HR_AGE_BAND_PKG.AGE_BAND_FK and EDW_HR_SERVICE_PKG.SERVICE_BAND_FK. These packages resolve dimensional foreign keys for age band and service band. The INSTANCE_CODE literal supports multi-instance or global HR deployments by qualifying the composite keys.

Key Columns

• SEPARATION_PK — Primary fact key, concatenating PERIOD_OF_SERVICE_ID, PERSON_ID, ASSIGNMENT_ID, and INSTANCE_CODE.
• ASSIGNMENT_FK — Manually constructed assignment dimension foreign key from ASSIGNMENT_ID and INSTANCE_CODE.
• AGE_BAND_FK — Derived via the age-band API, calculated from months between date of birth and the latest available termination date, defaulting to SYSDATE when no termination date exists.
• SERVICE_BAND_FK — Calculated as the interval between SNAPSHOT_DATE and PPS.DATE_START, passed to the service-band API.
• GEOGRAPHY_FK — Built from ASSIGNMENT LOCATION_ID plus instance code and the literal 'HR_LOC', defaulting to 'NA_EDW' when location is null.
• GRADE_FK — Manually constructed grade dimension key, populated only when an assignment grade exists.

Common Use Cases and Queries

The view supports separation analysis, turnover trending, tenure distribution, and age-based workforce profiling in HR Intelligence dashboards. A typical query aggregates separations by service band and age band:

• SELECT SERVICE_BAND_FK, AGE_BAND_FK, COUNT(*) FROM APPS.HR_EDWBV_WRK_SPRTN_FCV GROUP BY SERVICE_BAND_FK, AGE_BAND_FK;
• Joining GRADEBAND or GEOGRAPHY dimensions to slice voluntary versus involuntary terminations.
• ETL extraction into the HR EDW separation fact table using SEPARATION_PK as the load key.

Because the fallback to SYSDATE ("start_of_time" style default anchor) drives age and service band computation for active employees without termination dates, reports must filter on the presence of actual termination dates to avoid inflating separation counts with the current workforce.