Search Results igs_ps_emp_cats_wl




Overview
Explore in AI Chat

Overview

IGS_PS_EMP_CATS_WL is a table within the IGS — Student System product module of Oracle E-Business Suite, owned by the IGS schema. Its documented purpose is to describe the workload associated with employment categories. The reference values for these categories are sourced from Human Resources lookups when Oracle HR is installed in the instance; in the absence of an HR installation, the values are instead drawn from lookups within the Student System itself. This conditional sourcing makes the table a bridge between the Student System's academic workload configuration and the enterprise HR reference data model.

The table is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2 releases and carries a physical schema of eight columns. From the heuristic Data Vault classification mined from its foreign key structure, the table is characterized as satellite-leaning. In modeling terms, this suggests it should be treated as a descriptive satellite that captures attributes and measures (specifically workload values) attached to a parent business entity, rather than as an independent hub or a pure relationship link. The parent entity is the expected workload definition, referenced through the CAL_CAT_CODE foreign key.

Key Information Stored

The most significant columns of this table are the composite primary key members, the workload measure, and the standard EBS audit columns:

  • CAL_CAT_CODE — Calendar category code; part of the composite primary key and the sole documented foreign key column, referencing IGS_PS_EXP_WL.
  • EMP_CAT_CODE — Employment category code; the second member of the composite primary key, identifying the employment category whose workload is being described.
  • EXPECTED_WL_NUM — Expected workload number; the core business measure storing the workload value associated with the employment category.
  • CREATED_BY, CREATION_DATE — Standard audit columns recording the creating user and creation timestamp.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns capturing the last modifying user, the update timestamp, and the login session that performed the update.

The documented unique index IGS_PS_EMP_CATS_WL_PK covers the column pair (CAL_CAT_CODE, EMP_CAT_CODE). Because this unique index defines the business-key candidate — the combination that uniquely identifies a workload record — there is no separate surrogate key column documented for this table. The physical primary key therefore coincides with the business key, meaning the workload is uniquely determined by the calendar category and employment category pairing.

Common Use Cases and Queries

Typical uses revolve around retrieving or reporting the expected workload for a given employment category within a calendar category context. A common lookup pattern joins the table to its parent expected-workload definition:

SELECT w.CAL_CAT_CODE, w.EMP_CAT_CODE, w.EXPECTED_WL_NUM
FROM   IGS_PS_EMP_CATS_WL w
WHERE  w.CAL_CAT_CODE = :cal_cat_code;

Reporting scenarios frequently aggregate workload by employment category to support staffing and teaching-load analysis, or join outward to the HR/Student System lookup views that supply the EMP_CAT_CODE descriptions. Because the source of employment category values depends on whether HR is installed, reconciliation queries that compare these category codes against the appropriate lookup source are a recurring administrative task. Audit-oriented queries use the LAST_UPDATE_DATE and LAST_UPDATED_BY columns to track configuration changes to workload values over time.

Related Objects

The following objects are most significant to IGS_PS_EMP_CATS_WL:

  • IGS_PS_EXP_WL — Referenced through the foreign key IGS_PS_EMP_CATS_WL.CAL_CAT_CODE → IGS_PS_EXP_WL. This is the parent expected-workload table and the primary join path, confirming the satellite relationship inferred from the FK structure.
  • IGS_PS_EMP_CATS_WL_PK — The unique index enforcing the composite business key (CAL_CAT_CODE, EMP_CAT_CODE).
  • HR lookup views and tables — The conditional source of employment category values when Oracle HR is installed.
  • Student System lookup views and tables — The fallback source of employment category values when Oracle HR is not installed.

Collectively, these objects position IGS_PS_EMP_CATS_WL as a descriptive workload satellite anchored to the expected-workload parent entity, with its category reference data flowing from either HR or Student System lookups depending on the instance configuration.