Search Results geography_fk




Overview
Explore in AI Chat

Overview

HR_EDWBV_WRK_CMPSTN_FCV is an APPS-owned, VALID view within the HRI (Human Resources Intelligence) product family of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It serves as a foreign key composition view that maps workforce composition fact rows to the dimension keys used by the Oracle HR Intelligence/Enterprise Data Warehouse (EDW) star schema. The view synthesizes surrogate dimension keys — age band, service band, assignment, currency, geography, grade, and instance — from transactional workforce data, so that the fact records in the HR Intelligence datamart can be joined consistently to their conformed dimensions. Because it exposes the primary key COMPOSITION_PK alongside each foreign key, it is typically consumed by ETL processes and BI reporting layers rather than by end-user transactional screens.

Underlying Base Objects

The documented ETRM metadata lists no base objects explicitly, but the embedded view text reveals the aliases used in the definition. The principal driving table is the assignment source aliased ASG, joined conceptually to a snapshot source aliased SNP (providing SNAPSHOT_DATE), a person/period source aliased PPS (providing DATE_START), and a salary detail source aliased DSD (providing SALARY_CURRENCY_CODE). Dimension keys are produced through package APIs, notably EDW_HR_AGE_BAND_PKG.AGE_BAND_FK and EDW_HR_SERVICE_PKG.SERVICE_BAND_FK, which encapsulate the banding logic for age and length of service. The INSTANCE_CODE element is a standard EDW foreign key component that isolates data originating from different EBS instances in a multi-instance warehouse.

Key Columns

  • COMPOSITION_PK — the composite primary key of the fact row, constructed as ASSIGNMENT_ID, SNAPSHOT_DATE, and INSTANCE_CODE concatenated with hyphen separators.
  • AGE_BAND_FK — derived by calling EDW_HR_AGE_BAND_PKG.AGE_BAND_FK with age in months, computed as MONTHS_BETWEEN(ASG.EFFECTIVE_START_DATE, DATE_OF_BIRTH).
  • SERVICE_BAND_FK — derived via EDW_HR_SERVICE_PKG.SERVICE_BAND_FK, using length of service calculated as SNP.SNAPSHOT_DATE minus PPS.DATE_START.
  • ASSIGNMENT_FK — assembled manually as ASSIGNMENT_ID concatenated with INSTANCE_CODE.
  • CURRENCY_FK — taken directly from DSD.SALARY_CURRENCY_CODE.
  • GEOGRAPHY_FK — constructed manually from ASG.LOCATION_ID, INSTANCE_CODE, and the literal 'HR_LOC'. When LOCATION_ID is null, DECODE substitutes the sentinel value 'NA_EDW', indicating an unknown or not-applicable geography. This column is the one most frequently referenced when users search on the term "geography_fk".
  • GRADE_FK — built from ASG.GRADE_ID and INSTANCE_CODE, again falling back to 'NA_EDW' when a grade is not assigned.

The consistent 'NA_EDW' fallback and the INSTANCE_CODE suffix ensure that orphan dimension references are handled gracefully and that keys remain unique across source instances.

Common Use Cases and Queries

The view is primarily used to populate or validate HR Intelligence fact tables and to drive composition analysis by demographic and organizational dimensions. Typical queries select the foreign keys for grouping and join to dimension views.

  • Grouping headcount or salary facts by age band and service band.
  • Adding geography or grade context to workforce composition reports, filtering out the 'NA_EDW' sentinel where location or grade is mandatory.
  • Reconciling fact rows against dimensions by verifying COMPOSITION_PK uniqueness across INSTANCE_CODE values.

A representative query is:

SELECT COMPOSITION_PK, GEOGRAPHY_FK, GRADE_FK, AGE_BAND_FK, SERVICE_BAND_FK FROM APPS.HR_EDWBV_WRK_CMPSTN_FCV WHERE GEOGRAPHY_FK <> 'NA_EDW';

Because the view performs string concatenation and package-based derivations, performance depends on the underlying assignment, snapshot, and salary sources; reporting layers should join to conformed dimension views on the FK columns rather than re-deriving banding logic.