Search Results emp_cat_code

Overview

IGS_PS_EMP_CATS_WL_OSS_V is a PL/SQL view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. It presents employment-category workload information for use in student-system reporting and integration, and is specifically intended for deployments operating without Oracle HRMS installed. The view derives its employment-category reference data from Oracle lookups rather than from the HR organization structures, which is why the object name carries the "OSS" (Oracle Student System) qualifier. The view is documented as VALID and is exposed read-only; it is a reporting and inquiry surface, not a maintenance entity. Consumers who search on "cal_cat_code" arrive at this view because CAL_CAT_CODE is one of the principal selective keys exposed in its projection.

Underlying Base Objects

The view text joins two objects: the base table IGS_PS_EMP_CATS_WL (aliased ECW) and the lookup view IGS_LOOKUP_VALUES (aliased LKUP). The base table holds the stored employment-category workload rows, while IGS_LOOKUP_VALUES supplies the descriptive meaning and enabled status for each category. The join condition is ECW.EMP_CAT_CODE = LKUP.LOOKUP_CODE, restricted by LOOKUP_TYPE = 'PE_EMP_CATEGORIES' and ENABLED_FLAG = 'Y'. The view additionally applies a date-validity predicate using SYSDATE BETWEEN NVL(LKUP.START_DATE_ACTIVE, SYSDATE) AND NVL(LKUP.END_DATE_ACTIVE, SYSDATE), so only lookups that are currently active are returned. The documented 12.2.2 metadata records no referenced base objects beyond this pair; the relationship is effectively a one-to-one lookup enrichment of the workload table.

Key Columns

The view projects eleven columns. ROW_ID exposes the ROWID of the underlying IGS_PS_EMP_CATS_WL record and provides a stable row identifier. CAL_CAT_CODE is the calendar category code, the primary selective attribute in this view and the term most commonly used to filter or join workload data to calendar or calendar-category definitions. EMP_CAT_CODE is the employment category code sourced from the lookup code. EMP_CAT_DESC carries the lookup MEANING, giving a human-readable employment category description. EXPECTED_WL_NUM is the headline measure: the expected workload number for the employment category. The remaining columns are standard EBS audit attributes — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — which support audit trail reporting and row-level change tracking.

Common Use Cases and Queries

Typical uses include validating that workload figures exist for every active employment category, producing extract files for downstream reporting, and confirming lookup configuration in environments where HR is not installed. Because the view filters on both ENABLED_FLAG and lookup active dates, it is well suited to authoritative "currently effective" extracts, while the base table should be queried directly when historical or disabled categories are required.

• Retrieve all workload rows for a calendar category:
  SELECT CAL_CAT_CODE, EMP_CAT_CODE, EMP_CAT_DESC, EXPECTED_WL_NUM FROM IGS_PS_EMP_CATS_WL_OSS_V WHERE CAL_CAT_CODE = :cal_cat_code;
• List active employment categories and their workloads:
  SELECT EMP_CAT_CODE, EMP_CAT_DESC, EXPECTED_WL_NUM FROM IGS_PS_EMP_CATS_WL_OSS_V ORDER BY EMP_CAT_CODE;
• Audit recent changes:
  SELECT ROW_ID, CAL_CAT_CODE, EMP_CAT_CODE, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM IGS_PS_EMP_CATS_WL_OSS_V WHERE LAST_UPDATE_DATE >= TRUNC(SYSDATE) - 7;

Queries should filter on CAL_CAT_CODE or EMP_CAT_CODE to exploit the selective predicates, and consumers comparing results across 12.1.1 and 12.2.2 should confirm that the underlying lookup configuration for PE_EMP_CATEGORIES is consistent between environments.