Search Results igs_ps_exp_wl




Overview
Explore in AI Chat

Overview

IGS_PS_EXP_WL is a setup table within the Oracle E-Business Suite Student System (IGS) product family. It stores the configuration data that defines the expected workload associated with a given calendar category. In institutional terms, the table establishes a normative value — an anticipated or standard workload — for categories such as a term, semester, academic year, or teaching period, allowing the Student System to compare planned or actual student workload against an institution-defined expectation. The table is owned by the IGS schema and is marked VALID in the ETRM 12.1.1 documentation set; it is present in both the 12.1.1 and 12.2.2 releases with the same documented structure (eight columns).

From a dimensional modeling perspective, the heuristic Data Vault classification supplied in the metadata is hub-leaning. This reflects the fact that the table's unique business key, CALENDAR_CAT, is a stable, non-transactional identifier that other tables reference, and that the table carries descriptive attributes rather than acting purely as a relationship or as a high-volume event record. A hub-style treatment of CALENDAR_CAT, potentially paired with a satellite holding EXPECTED_WL and the audit columns, would be a reasonable modeling suggestion if the source were to be folded into a Data Vault warehouse. It should not be treated as a transactional fact.

Key Information Stored

The documented physical schema for IGS_PS_EXP_WL contains eight columns. The most significant are:

  • CALENDAR_CAT — the calendar category code. This is the business key of the table and is carried by the unique index IGS_PS_EXP_WL_UK as well as by the primary key constraint IGS_PS_EXP_WL_PK. It identifies the category (for example, a term type or academic period type) for which an expected workload is being defined.
  • EXPECTED_WL — the expected (normative) workload value configured for that calendar category. This is the principal payload attribute and the value most often consumed by downstream load or comparison logic.
  • CONTROL_NUM — a control or sequence number associated with the setup row, used for internal ordering and identification within the Student System setup framework.
  • CREATED_BY, CREATION_DATE — standard Oracle EBS audit columns recording the user and timestamp of row insertion.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns recording the most recent modification, its timestamp, and the login session of the updating user.

Note that the primary key and the unique business key are documented on the same column, CALENDAR_CAT; in this table the surrogate and business key coincide rather than being separated by an independent sequence-generated ID. The table therefore functions as a small, low-cardinality reference/setup table whose grain is one row per calendar category.

Common Use Cases and Queries

The primary use case is setup validation and workload definition reporting. Typical queries retrieve the expected workload for one or all calendar categories, or validate that every category in use has a corresponding setup row.

  • Single-category lookup: SELECT EXPECTED_WL FROM IGS_PS_EXP_WL WHERE CALENDAR_CAT = :p_cat;
  • Full setup listing ordered for review: SELECT CALENDAR_CAT, EXPECTED_WL, LAST_UPDATE_DATE FROM IGS_PS_EXP_WL ORDER BY CALENDAR_CAT;
  • Coverage/exception report identifying categories referenced elsewhere but lacking expected-workload setup, generally implemented as an outer join against the consuming table (see Related Objects) with a null check on EXPECTED_WL.
  • Audit analysis of setup changes: filtering on LAST_UPDATE_DATE and LAST_UPDATED_BY to review who altered expected workload values during a given period.

The table is not a high-volume transactional object; reporting is usually driven from user exits, concurrent programs, or extract scripts rather than from end-user inquiry screens.

Related Objects

The FK metadata documents a single dependent relationship, but that relationship is significant because it constrains the values that may appear in CALENDAR_CAT:

  • IGS_PS_EMP_CATS_WL — references IGS_PS_EXP_WL through the column IGS_PS_EMP_CATS_WL.CAL_CAT_CODE. This table links calendar categories to employment categories (or equivalent categorizations) and their workload values, and is the principal consumer of the expected-workload setup defined here. Any insert or update into IGS_PS_EMP_CATS_WL.CAL_CAT_CODE must resolve to an existing CALENDAR_CAT value in IGS_PS_EXP_WL.
  • IGS_PS_EXP_WL_PK and IGS_PS_EXP_WL_UK — the primary key and unique index on CALENDAR_CAT, both enforcing uniqueness at the category level and serving as the access path for lookups.

Because the metadata lists only one foreign-key relationship, broader integration with other IGS calendar, term, and workload tables should be confirmed against the institution's specific configuration and the Student System data model before being relied upon in custom queries or extracts.