Search Results dys_frm_strt_to_prcss

Overview

HR_EDW_WRK_SPRTN_FCV is a reporting view owned by the APPS schema within the Oracle E-Business Suite Human Resources Intelligence (HRI) product. It is a denormalized workforce separation fact view intended for the Oracle HR Analytics / HR Intelligence data warehouse layer rather than for transactional use. The view presents employee separation (termination) events as a star-schema style fact collection, coupling surrogate foreign keys to dimension tables with additive and semi-additive measures such as full-time-equivalent counts, headcounts, and duration metrics.

Its principal role is to feed separation analytics: turnover reporting, reason analysis, projected versus actual termination tracking, and time-to-processing metrics. Because the columns are almost entirely surrogate key references and pre-aggregated numeric measures, the view is designed for consumption by BI tools, HR dashboards, and downstream ETL that join to shared HR dimensions (person, assignment, organization, job, grade, geography, time).

Underlying Base Objects

The documented definition shows the view is defined as a single-table SELECT over HR_EDWBV_WRK_SPRTN_FCV, an HRI base view (the "BV" in the name denotes a base view in the HRI naming convention, typically a materialized or staged layer built from HR_EDW tables). The ETRM metadata records no additional referenced base tables for HR_EDW_WRK_SPRTN_FCV itself; the object is documented as VALID and owned by APPS.

Notably, the SELECT list ends with a run of NULL placeholder expressions following LEAVING_REASON. These are structural fillers aligned positionally with the underlying base view, and they resolve to the columns that are intentionally blank or reserved in this reporting layer. Practically, this means the effective dimension and measure content is delivered by HR_EDWBV_WRK_SPRTN_FCV, and HR_EDW_WRK_SPRTN_FCV acts as the published, stable interface over it.

Key Columns

The view exposes three broad column families. Surrogate key columns bind the fact to dimensions: SEPARATION_PK is the primary key of the fact; ASSIGNMENT_FK, PERSON_FK, PERSON_TYPE_FK, POSITION_FK, JOB_FK, GRADE_FK, ORGANIZATION_FK, GEOGRAPHY_FK, AGE_BAND_FK, SERVICE_BAND_FK, and INSTANCE_FK identify the surrounding dimension members. Time dimension keys include TIME_TRM_NTFD_FK, TIME_EMP_STRT_FK, TIME_TRM_ACCPTD_FK, TIME_TRM_PRJCTD_FK, TIME_TRM_PRCSS_FK, and TIME_TRM_OCCRD_FK.

Among these, REASON_FK — the column associated with the user query "reason_fk" — links each separation fact to the reason dimension, enabling grouping of terminations by cause. It is complemented by MOVEMENT_TYPE_FK, which classifies the movement type, and by LEAVING_REASON, the descriptive reason text. Together these support reason code analysis.

Measure columns carry the quantitative content: EMP_START_FTE/HDCNT, SPRTN_NTFD_FTE/HDCNT, SPRTN_ACCPTD_FTE/HDCNT, SPRTN_PRJCTD_FTE/HDCNT, EMP_SPRTN_FTE/HDCNT, FINAL_PRCSS_FTE/HDCNT, and SEPARATION_FTE/HEADCOUNT. Value measures include SPRTN_NTFD_VL, SPRTN_ACCPTD_VL, SPRTN_PLND_VL, SPRTN_CNCLD_VL, SPRTN_OCCRRD_VL, and SPRTN_FNL_PRCSSNG_VL. A rich set of elapsed-day measures spans notification through final processing (DYS_FRM_STRT_TO_*, DYS_FRM_NTFD_TO_*, etc.), alongside key dates NTFD_TRMNTN_DT, ACCPTD_TRMNTN_DT, PRJCTD_TRMNTN_DT, ACTUAL_TRMNTN_DT, and FINAL_PROCESS_DT. Audit columns LAST_UPDATE_DATE and CREATION_DATE are included, and ASG_* attributes provide denormalized assignment keys.

Common Use Cases and Queries

Typical uses include turnover and reason analysis, separation funnel tracking (notified, accepted, planned, occurred, processed), and duration/aging analysis. A representative reason-based aggregate:

• Grouping separations by reason: SELECT REASON_FK, SUM(SEPARATION_HEADCOUNT), SUM(SEPARATION_FTE) FROM HR_EDW_WRK_SPRTN_FCV GROUP BY REASON_FK;
• Reason versus leaving description: joining REASON_FK and MOVEMENT_TYPE_FK to their dimensions while retaining LEAVING_REASON for labeling.
• Time-to-process analysis: comparing DYS_FRM_NTFD_TO_ACPTD and DYS_FRM_STRT_TO_PRCSS across REASON_FK, PERSON_TYPE_FK, or ORGANIZATION_FK.
• Funnel metrics: reporting SEPARATION_HEADCOUNT against SPRTN_NTFD_HDCNT and SPRTN_ACCPTD_HDCNT by time dimension key (TIME_TRM_NTFD_FK, TIME_TRM_OCCRD_FK).

Because no base tables are documented beyond HR_EDWBV_WRK_SPRTN_FCV, queries should target HR_EDW_WRK_SPRTN_FCV directly and join out to the HR dimension views on the *_FK columns, enabling consistent reason-, person-, and organization-level separation reporting within the HR Intelligence model.