Search Results instance_code




Overview
Explore in AI Chat

Overview

HR_EDWBV_WRK_ACTVTY_FCV is a Business Intelligence (BI) extraction view owned by the APPS schema in Oracle E-Business Suite. The suffix "BV" identifies it as a Business View, "EDW" indicates it belongs to the Enterprise Data Warehouse integration layer, and "FCV" denotes a flattened currency/value construct used by the HR analytics and workforce activity subject areas. The view exposes assignment-level workforce activity changes, packaged as a set of surrogate foreign keys that conform to the EDW dimensional model. Rather than reporting transactional assignment rows, it presents a denormalized projection of the Assignment Changes fact, in which natural key values are concatenated with the instance_code to produce globally unique, cross-instance identifiers. This design allows the Oracle HR Analytics warehouse, Discoverer workbooks, and custom BI Publisher extracts to reconcile workforce activity across multiple EBS instances that share a single reporting environment. Because the view is a thin projection over the HR assignment and assignment-change entities, it is read-only and is intended for query, extraction, and ETL consumption rather than transactional update.

Underlying Base Objects

The ETRM metadata for this object does not document any referenced base objects, which is typical for EDW conformance views whose lineage is maintained in the HR Analytics data model rather than in the EBS data dictionary. From the embedded view text, the projection is clearly derived from the HR assignment entity (aliased asg) and its change-tracking counterpart (aliased chg), with the prior-assignment image accessed through the pasg_* columns. The filter columns org_change_flag, job_change_flag, pos_change_flag, grd_change_flag, geog_change_flag, and emplymnt_start_flag indicate the view is built on the assignment-change staging logic that captures before-and-after images of an assignment. Reference to EDW_HR_AGE_BAND_PKG.age_band_fk confirms a dependency on the HR EDW age-band PL/SQL package for banding derived from date of birth.

Key Columns

  • assignment_change_pk — Primary surrogate key built as assignment_id || '-' || effective_start_date || '-' || instance_code.
  • instance_fk — Standard EDW instance foreign key populated from instance_code; critical for multi-instance consolidation.
  • assignment_fk — Constructed as assignment_id || '-' || instance_code.
  • assignment_change_fk — Composite of the six Y/N change flags, scoring whether any change occurred, suffixed by instance_code.
  • age_band_fk — Derived via the age-band API using months between effective start date and date of birth.
  • geography_from_fk / geography_to_fk — Location-based keys, defaulted to NA_EDW when no location exists.
  • grade_from_fk / grade_to_fk, job_from_fk, and related *_to_fk columns — Prior and current dimension keys, all concatenated with instance_code.
  • application_fk — Exposed as NULL in this view.

Common Use Cases and Queries

Typical use of this view is to feed the Assignment Changes fact table in the HR EDW and to support workforce activity analysis such as promotion, transfer, and grade-change reporting. Because instance_code is embedded in every key, the most common query predicate filters by instance. A representative query is:

SELECT assignment_change_pk, assignment_fk, assignment_change_fk, age_band_fk, instance_fk FROM apps.hr_edwbv_wrk_actvty_fcv WHERE instance_fk = :p_instance_code;

Analysts also join the *_from_fk and *_to_fk columns to the corresponding job, grade, and geography dimensions to compare prior and current assignment states within a single extraction.