Search Results hri_gain_types




Overview
Explore in AI Chat

Overview

APPS.HRI_CL_WAC_GAINTYP_X_V is a read-only Oracle EBS view that exposes a filtered, denormalized list of "gain type" lookup values maintained in the HR_LOOKUPS table. It is part of the Oracle HR Intelligence / ETRM (Enterprise Talent and Rewards Management) data model, where the HRI_CL_WAC prefix denotes the "Write-Access Code" or code-lookup virtualization layer used by compensation and gainsharing analytics. In Oracle EBS 12.1.1 and 12.2.2, this view functions as the canonical enumeration source for gain types referenced by compensation workbench and total compensation reporting components.

The view presents lookup codes as an ID column, lookup meanings as VALUE, effective date bounds as DATE_FROM and DATE_TO, and the enabled flag as VALID_CODE. Its role is to normalize lookup storage into a key/value/effective-dating structure that downstream reporting and integration consumers can query uniformly, without needing to know the underlying HR_LOOKUPS schema or lookup_type naming conventions.

Underlying Base Objects

The documented base objects referenced by this view are HR_API (PACKAGE), HR_GENERAL (PACKAGE), HR_LOOKUPS (VIEW) and DUAL (SYNONYM). The primary data source is HR_LOOKUPS, filtered by LOOKUP_TYPE = 'HRI_GAIN_TYPES'. The HR_GENERAL package supplies the START_OF_TIME and END_OF_TIME constants used to default missing effective date boundaries, so that open-ended lookups are returned with full-range dates rather than NULLs. DUAL supports the UNION ALL branch that injects the synthetic 'NA_EDW' row. HR_API is referenced as an expected dependency in the documented metadata, typical of HRI_CL_WAC views that participate in the HR write-access validation framework.

Key Columns

  • ID — The lookup_code value, typically codes prefixed with 'GAIN' (for example GAINSHARE or GAINBONUS). This is the join key used by consuming reports.
  • VALUE — The lookup meaning, i.e., the user-facing label associated with the gain type.
  • DATE_FROM — The lookup's start_date_active, defaulted to HR_GENERAL.START_OF_TIME when not set.
  • DATE_TO — The lookup's end_date_active, defaulted to HR_GENERAL.END_OF_TIME when not set.
  • VALID_CODE — The enabled_flag value; only 'Y' rows are returned by the primary branch.

Rows are excluded when the lookup code equals 'GAINS', and when the current truncated system date falls outside the effective range. A synthetic row with ID = 'NA_EDW', an empty VALUE, full date range and VALID_CODE = 'Y' is always appended via UNION ALL, providing a placeholder for warehouse/unknown-dimension handling.

Common Use Cases and Queries

The view is typically used to drive list-of-values elements, lookup joins in gainsharing and compensation extracts, and EDW dimension population where a consistent "not applicable" member is required.

SELECT id, value, date_from, date_to, valid_code
FROM   apps.hri_cl_wac_gaintyp_x_v
ORDER BY id;

Joining to a transaction or award table by lookup code:

SELECT g.id, g.value, t.award_amount
FROM   apps.hri_cl_wac_gaintyp_x_v g,
       apps.some_gain_transaction t
WHERE  t.gain_type = g.id;

Because the view is defined WITH READ ONLY and derives effective dating from SYSDATE, it must be re-queried for current validity rather than cached. It is not a base table and provides no DML path.