Results for “hr_edw_wrk_actvty_f”

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

Overview

The HR_EDW_WRK_ACTVTY_F table is a Human Resources Intelligence (HRI) fact-style warehouse object owned by the HRI schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores work activity and assignment change history extracted from the Oracle HRMS operational model, denormalized for analytical consumption through the HR Intelligence/ETRM reporting layer. The table holds 127 documented columns and is populated by the HR Intelligence ETL programs that consolidate assignment lifecycle events into a single wide structure.

Heuristically, the metadata classifies this object as standalone based on its foreign key topology. In Data Vault modeling terms it is best treated as a satellite (a descriptive, event-grained structure keyed by business attributes such as ASSIGNMENT_CHANGE_PK), rather than a hub or link, since it carries measures, flags, and descriptive attributes tied to assignment change events rather than acting as a pure key registry. This is a modeling suggestion only; the physical ETRM implementation is a flattened analytical table, not a normalized Data Vault structure.

Key Information Stored

The documented physical model exposes one unique index, HR_EDW_WRK_ACTVTY_F_U1, defined on ASSIGNMENT_CHANGE_PK. That column is therefore the business-key candidate for the assignment change event. A distinct surrogate identity is implied by INSTANCE_FK_KEY and the ASSIGNMENT_CHANGE_FK_KEY/ASSIGNMENT_FK_KEY surrogate set carried alongside the business key.

• ASSIGNMENT_CHANGE_PK — business-key candidate (unique index U1); identifies the assignment change occurrence.
• ASSIGNMENT_ID and PERSON_ID — the assignment and person whose activity is being recorded.
• ASSIGNMENT_SEQUENCE — ordering of the assignment version within the person's history.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the dated range for the change record.
• ASSIGNMENT_STATUS_TYPE_ID — FK to PER_ASSIGNMENT_STATUS_TYPES, the status at the time of change.
• CHANGE_REASON and MOVEMENT_TYPE_FK_KEY — cause and movement classification of the change.
• JOB_ID, POSITION_ID, GRADE_ID, ORGANIZATION_ID, LOCATION_ID — the core assignment attributes subject to change.
• SUPERVISOR_ID and MANAGER_FLAG — supervisory relationship and manager indicator.
• ASG_CHANGE_FTE and ASG_CHANGE_HEADCOUNT — the primary measures of the fact structure.
• JOB_CHANGE_FLAG, ORG_CHANGE_FLAG, POS_CHANGE_FLAG, GRD_CHANGE_FLAG, GEOG_CHANGE_FLAG, OTHER_CHANGE_FLAG — analytic flags identifying the dimension changed.
• DAYS_SINCE_LAST_* measures — tenure in the prior job, position, grade, organization, and geography.
• USER_ATTRIBUTE1–15 and USER_MEASURE1–5 — extensibility columns for customer-defined analytics.

Common Use Cases and Queries

The table supports workforce movement analytics, assignment change trending, and HR Intelligence dashboards. Typical patterns join surrogate keys back to their conformed dimensions and filter by effective date.

• Counting assignment changes by reason and movement type over a period.
• Measuring FTE and headcount deltas attributable to each change.
• Tracking average days since last promotion, transfer, or reorganization.

A representative query pattern is:

SELECT w.assignment_id,
       w.effective_start_date,
       w.change_reason,
       w.asg_change_fte,
       w.asg_change_headcount
FROM   hri.hr_edw_wrk_actvty_f w
WHERE  w.organization_id = :org_id
AND    w.effective_start_date BETWEEN :from_date AND :to_date
ORDER BY w.assignment_id, w.effective_start_date;

Because the table is pre-aggregated at the change level, it is also frequently used as a source for period-over-period comparison and attrition-adjacent reporting in HR Intelligence.

Related Objects

The foreign keys documented in the metadata define the principal conformed dimensions joined to this fact:

• PER_ASSIGNMENT_STATUS_TYPES — via ASSIGNMENT_STATUS_TYPE_ID.
• PER_PERIODS_OF_SERVICE — via PERIOD_OF_SERVICE_ID.
• PER_ESTABLISHMENTS — via ESTABLISHMENT_ID.
• PER_CAGR_GRADES_DEF — via CAGR_GRADE_DEF_ID.
• PER_COLLECTIVE_AGREEMENTS — via COLLECTIVE_AGREEMENT_ID.
• PER_PAY_BASES — via PAY_BASIS_ID.
• PER_ALL_VACANCIES — via VACANCY_ID.
• PER_RECRUITMENT_ACTIVITIES — via RECRUITMENT_ACTIVITY_ID.
• PAY_PEOPLE_GROUPS — via PEOPLE_GROUP_ID.
• HR_SOFT_CODING_KEYFLEX — via SOFT_CODING_KEYFLEX_ID.

The surrogate FK keys (ASSIGNMENT_FK_KEY, JOB_FROM_FK_KEY/ JOB_TO_FK_KEY, POSITION_FROM_FK_KEY/POSITION_TO_FK_KEY, ORGANIZATION_FROM_FK_KEY/ORGANIZATION_TO_FK_KEY, GRADE_FROM_FK_KEY/GRADE_TO_FK_KEY, GEOGRAPHY_FROM_FK_KEY/GEOGRAPHY_TO_FK_KEY, MOVEMENT_TYPE_FK_KEY, AGE_BAND_FK_KEY, TIME_FROM_FK_KEY/TIME_TO_FK_KEY, SERVICE_BAND_FK_KEY) resolve to the shared HRI EDW dimension tables, providing the from/to comparison structure typical of HR Intelligence activity facts.