Search Results cal_cat_code




Overview
Explore in AI Chat

Overview

IGS.IGS_PS_EMP_CATS_WL is a transactional configuration table in the Oracle E-Business Suite student system schema (IGS), documented as VALID across EBS 12.1.1 and 12.2.2. The entity describes the expected workload associated with an employment category within a given calendar category. It functions as a rules or setup table: for each combination of calendar category and employment category, it stores the institutional workload expectation. Records are scoped at the institutional level, with permission values restricted to Term (Load) and Academic calendar categories.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free of 10, consistent with a small-to-medium setup table subject to infrequent maintenance rather than high-volume transaction processing. Because the primary key is a composite of two code columns plus a measure, the metadata's heuristic Data Vault classification is satellite-leaning. In modeling terms, this suggests treating CAL_CAT_CODE as a parent hub or link reference, with EMP_CAT_CODE and EXPECTED_WL_NUM carried as descriptive attributes of a satellite rather than as independent business entities.

Key Information Stored

The physical schema comprises eight columns, of which three are business-meaningful and five are standard WHO audit columns.

  • CAL_CAT_CODE (VARCHAR2(30), mandatory) — The calendar category at which the expected workload is stored at institutional level. Permitted values are Term (Load) and Academic calendar categories. This column participates in the unique primary key and is the documented foreign key target toward IGS_PS_EXP_WL.
  • EMP_CAT_CODE (VARCHAR2(30), mandatory) — The employment category assigned to a calendar category. Values are sourced from HR lookups when HR is installed, or from Oracle Student System lookups when HR is not installed.
  • EXPECTED_WL_NUM (NUMBER, mandatory) — The numeric expected workload for the given calendar category and employment category combination.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns recording insert and update identity and timestamps.

The surrogate primary key is IGS_PS_EMP_CATS_WL_PK, a UNIQUE NORMAL index on (CAL_CAT_CODE, EMP_CAT_CODE) in the APPS_TS_TX_IDX tablespace. Because this index is both the primary key and the sole unique index, the composite of the two code columns is also the business-key candidate; no separate surrogate identifier column is documented.

Common Use Cases and Queries

The table is typically queried to validate or report workload expectations per employment category, or to resolve setup gaps before downstream load calculations. A direct extraction follows the documented query text:

  • Listing all workload rules: SELECT cal_cat_code, emp_cat_code, expected_wl_num FROM igs.igs_ps_emp_cats_wl ORDER BY cal_cat_code, emp_cat_code;
  • Filtering by calendar category using the indexed leading column: SELECT emp_cat_code, expected_wl_num FROM igs.igs_ps_emp_cats_wl WHERE cal_cat_code = :p_cal_cat_code;
  • Retrieving a single rule by full primary key: SELECT expected_wl_num FROM igs.igs_ps_emp_cats_wl WHERE cal_cat_code = :p_cal AND emp_cat_code = :p_emp;
  • Auditing recent setup changes: SELECT cal_cat_code, emp_cat_code, last_updated_by, last_update_date FROM igs.igs_ps_emp_cats_wl WHERE last_update_date > SYSDATE - 30;

Because only the leading column of the composite key is independently selective, queries that filter on EMP_CAT_CODE alone cannot use the index efficiently and should be reviewed for full scans in high-concurrency reporting.

Related Objects

  • IGS.IGS_PS_EXP_WL — Referenced by the documented foreign key from CAL_CAT_CODE, anchoring the calendar category definition used by this table.
  • IGS.IGS_PS_EMP_CATS_WL_PK — The unique index enforcing the composite business key on (CAL_CAT_CODE, EMP_CAT_CODE).
  • APPS.IGS_PS_EMP_CATS_WL — The APPS-synonym view layer through which the table is accessed in EBS; the metadata notes this table is referenced by APPS.
  • HR Lookups (FND_LOOKUP_VALUES) — The source of EMP_CAT_CODE values when Oracle HR is installed; OSS lookups supply them otherwise.
  • IGS_PS_EMP_CATS_WL (FND Design Data) — The registered design data object used by the application for setup and concurrent processing.

Dependency metadata confirms the table does not reference any database object other than the documented foreign key, and it is referenced by the APPS layer via its synonym.