Search Results cal_cat_code




Overview
Explore in AI Chat

Overview

The IGS_PS_EMP_CATS_WL table is a core data entity within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Student System (OSS). It serves as a master repository for defining the expected workload associated with different employment categories at an institutional level. Its primary role is to establish a configurable relationship between calendar categories—such as Terms or Academic periods—and employment classifications, enabling the system to manage and calculate workload expectations for personnel based on their employment type within a given academic timeframe. The table is owned by the IGS schema and is integral to workload planning and reporting functionalities.

Key Information Stored

The table's structure centers on three key data columns that define the workload rule, supplemented by standard WHO audit columns. The primary data elements are:

  • CAL_CAT_CODE (VARCHAR2(30), Mandatory): This column stores the code for the calendar category, such as 'TERM' or a specific academic calendar. It defines the institutional time period for which the workload rule is applicable. The metadata specifies permissible values are Term (Load) and Academic calendar categories.
  • EMP_CAT_CODE (VARCHAR2(30), Mandatory): This column holds the code for the employment category (e.g., Full-Time, Part-Time, Adjunct). The values are dynamically sourced from lookup tables in the HR module if it is installed; otherwise, they are sourced from the Student System lookups.
  • EXPECTED_WL_NUM (NUMBER): This numeric column stores the expected workload figure for the combination of the specified calendar category and employment category. This is the quantitative target or standard used by the system.

Together, the CAL_CAT_CODE and EMP_CAT_CODE form the table's primary key (IGS_PS_EMP_CATS_WL_PK), ensuring uniqueness for each workload definition rule.

Common Use Cases and Queries

This table is primarily used in institutional setup and reporting for academic workload management. A common use case is during the configuration of a new academic term or calendar, where administrators must define the standard expected workload (e.g., teaching hours, course load) for each type of employment contract. It is also critical for generating reports that compare actual assigned workloads against these institutional expectations for analysis and compliance.

A fundamental query to retrieve all configured workload rules follows the pattern provided in the metadata:

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;

To find the expected workload for a specific calendar and employment category, a targeted query is used:

SELECT EXPECTED_WL_NUM
FROM IGS.IGS_PS_EMP_CATS_WL
WHERE CAL_CAT_CODE = :p_cal_cat_code
  AND EMP_CAT_CODE = :p_emp_cat_code;

Related Objects

Based on the provided relationship data, the table has defined referential integrity with other entities in the Student System. The documented relationships are:

  • Primary Key: IGS_PS_EMP_CATS_WL_PK on (CAL_CAT_CODE, EMP_CAT_CODE).
  • Foreign Key Reference: The column CAL_CAT_CODE in this table references another table, IGS_PS_EXP_WL. This indicates that the calendar category code stored here must be a valid, pre-existing category in that related master table, which likely holds broader definitions for expected workload calendars.

The dependency information notes that the table is referenced by objects within the APPS schema, confirming its integration into the broader EBS application layer for data processing and validation.