Search Results asg_assignment_id




Overview
Explore in AI Chat

Overview

APPS.HR_EDW_WRK_CMPSTN_FCV is a reporting view within the Oracle E-Business Suite Human Resources warehouse architecture. The name follows the HR Enterprise Data Warehouse (EDW) naming convention: the WRK segment indicates a work or staging layer, CMPSTN denotes workforce composition, and the FCV suffix identifies it as a flat, denormalized collection view intended for direct consumption by downstream reporting and analytics tools. Its principal role is to expose a consolidated snapshot of workforce composition metrics — headcount, full-time equivalent (FTE), grade salary ranges, and annualized salary values — joined against the descriptive foreign key columns that allow slicing by age, service, job, grade, geography, and organization.

Because the object is a view rather than a table, it presents no independent storage. It serves as a stable, query-friendly interface over the warehouse-building views beneath it, insulating report authors and extract processes from changes to the underlying transformation logic. This makes it suitable for Oracle Business Intelligence (OBIEE) subject areas, Discoverer workbooks, and custom PL/SQL or SQL*Plus extracts that populate HR metrics dashboards.

Underlying Base Objects

The ETRM metadata documents no referenced base tables and lists the owner as unspecified. However, the view definition is definitively documented: it is a simple projection over a single object, HR_EDWBV_WRK_CMPSTN_FCV, an HR EDW base view. Every column in HR_EDW_WRK_CMPSTN_FCV is passed straight through from that source object, with no filtering, joins, or expressions applied at this layer. The view is therefore a thin wrapper — its purpose is to present the flat composition data set under a standardized EDW name while the actual derivations (foreign key lookups, banding, currency conversion) occur in HR_EDWBV_WRK_CMPSTN_FCV and its own parents. Reporting code should treat this view as the public contract and avoid querying the base view directly, so that any future substitution of the underlying source does not break dependent reports.

Key Columns

The column list divides into three functional groups:

Common Use Cases and Queries

Typical usage aggregates composition measures across a chosen band or dimension, usually restricted to a snapshot date. A representative query analyzing headcount and salary by age band is:

  • SELECT age_band_fk, SUM(composition_headcount) hc, SUM(composition_fte) fte, AVG(crnt_annlzed_slrY_bc) avg_sal
  • FROM apps.hr_edw_wrk_cmpstn_fcv
  • WHERE snapshot_date = :p_snapshot_date
  • GROUP BY age_band_fk
  • ORDER BY age_band_fk;

Other common scenarios include workforce composition reporting by organization or job, compensation analysis comparing annualized salary against grade salary ranges (LWST_GRD_SLRY and HGHST_GRD_SLRY), and FTE utilization trending across time via TIME_FK. Because the view is a pass-through with no row-level security applied at this layer, queries should be scoped by the appropriate business group or organization identifiers and, where the EDW is partitioned, filtered by SNAPSHOT_DATE to limit scanned data. The *_FK columns are intended for joins to the corresponding EDW dimension views rather than to transactional base tables.