Search Results instance_code

Overview

HR_EDWBV_WRK_ACTVTY_FCV is a view owned by the APPS schema within the Oracle E-Business Suite release 12.1.1 / 12.2.2 environment. It belongs to the HRI (Human Resources Intelligence) product family, which supplies the Oracle HRMS Intelligence and Enterprise Data Warehouse (EDW) reporting layer. The object name follows the standard EDW Business View (EDWBV) naming convention and, more specifically, the "_FCV" suffix that identifies a Foreign Key Conversion View. These views are the mechanisms by which the EDW staging and fact-loading processes translate native Oracle HRMS surrogate keys into the composite, global surrogate keys — formed by concatenating the source identifier, an instance code, and (where appropriate) an object-type discriminator — that are required by the HR Intelligence dimensional model.

The view therefore does not simply expose transactional data; it projects a set of resolved foreign key columns, each corresponding to a dimension (Assignment, Grade, Geography, Job, Age Band, Instance, and so on). Its role is to present a single denormalized row per assignment change event, in which every key has been correctly qualified with the INSTANCE_CODE of the source system, ready for loading into the workforce activity fact tables of the HR Intelligence warehouse.

Underlying Base Objects

The ETRM metadata for this view does not document referenced base objects. The view text preserved in the source header, however, shows that it is defined over at least two joined HRMS entities: an assignment alias (ASG, drawn from PER_ALL_ASSIGNMENTS_F) and a change alias (CHG, drawn from the assignment-change tracking structure, such as PER_ASSIGNMENT_STATUS_TYPES or the corresponding change-flag table). The view also invokes the packaged function EDW_HR_AGE_BAND_PKG.AGE_BAND_FK, a helper in the HR Intelligence PL/SQL package layer that derives the Age Band dimension key from a supplied age in months. The INSTANCE_CODE column, referenced throughout the SELECT list, originates from the EDW instance registration configuration and is carried into every composite key.

Key Columns

• ASSIGNMENT_CHANGE_PK — Primary key for the assignment change record, composed as ASSIGNMENT_ID, EFFECTIVE_START_DATE, and INSTANCE_CODE joined by hyphens.
• ASSIGNMENT_CHANGE_FK — Composite foreign key describing the nature of the change (organization, job, position, grade, geography, or employment start), concatenated with the instance code.
• ASSIGNMENT_FK — Assignment dimension key, constructed manually from ASSIGNMENT_ID and INSTANCE_CODE.
• AGE_BAND_FK — Derived via the Age Band API using MONTHS_BETWEEN of the effective start date and the worker's date of birth.
• GRADE_FROM_FK / GRADE_TO_FK — Prior and current grade keys, populated with NA_EDW where no grade is present.
• GEOGRAPHY_FROM_FK / GEOGRAPHY_TO_FK — Prior and current location keys, qualified with the 'HR_LOC' object-type discriminator and defaulted to NA_EDW when absent.
• INSTANCE_FK — The standard EDW instance foreign key, identifying the originating source system.
• APPLICATION_FK — Exposed as NULL in this view, reserved for application-level dimensional attribution.

Common Use Cases and Queries

The view is most frequently consumed by HR Intelligence ETL flows and by reporting queries that require fully resolved warehouse keys when reconciling assignment activity. A typical diagnostic query selects the composite keys alongside the source identifiers to validate that dimensional resolution has succeeded:

• SELECT assignment_change_pk, assignment_fk, grade_from_fk, grade_to_fk, instance_fk FROM apps.hr_edwbv_wrk_actvty_fcv;
• Filtering on change type to isolate promotions: SELECT assignment_change_pk, grade_from_fk, grade_to_fk FROM apps.hr_edwbv_wrk_actvty_fcv WHERE assignment_change_fk LIKE '%Y-Y%';
• Identifying unresolved dimension members prior to load: SELECT assignment_change_pk FROM apps.hr_edwbv_wrk_actvty_fcv WHERE grade_to_fk = 'NA_EDW' OR geography_to_fk = 'NA_EDW';
• Joining to the assignment dimension to report movements by organization for a given instance.

Because the view performs key transformation rather than data extraction, it is used exclusively within the APPS schema and should not be modified or queried outside the supported HR Intelligence reporting context.