Search Results fac_ovr_wl_id




Overview
Explore in AI Chat

Overview

IGS.IGS_PS_FAC_OVR_WL is a transaction table in the Oracle E-Business Suite Higher Education / Student Systems (IGS) schema that stores overridden expected workload assignments for faculty members. Within Oracle EBS 12.1.1 and 12.2.2, the table supports the Faculty Workload functionality, where the standard expected teaching or administrative workload defined at the faculty level may need to be adjusted for a specific period. Each row captures one such override, recording the parent workload assignment, the effective date range, the substituted workload value, and the reason for the change.

From a Data Vault modeling perspective, the mined foreign-key structure suggests classifying this table as a link entity. It connects a parent faculty workload record (IGS_PS_FAC_WL) to an override reason reference (IGS_PS_WL_OVER_RESN), while carrying its own descriptive attributes such as the date range and new workload value. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, consistent with OLTP transaction storage in Oracle EBS.

Key Information Stored

The table contains eleven documented columns. The most significant are:

  • FAC_OVR_WL_ID — NUMBER(15). Sequence-generated surrogate primary key and the column most commonly searched by users. It is the single column of the unique index IGS_PS_FAC_OVR_WL_U1 and of the primary key constraint IGS_PS_FAC_OVR_WL_PK.
  • FAC_WL_ID — NUMBER(15). Foreign key to the parent table IGS_PS_FAC_WL. This is the principal business link identifying which faculty workload assignment is being overridden.
  • START_DATE and END_DATE — DATE. Define the period over which the overridden expected workload applies. These dates bound the validity window of the override.
  • NEW_EXP_WL — NUMBER. The replacement expected workload value in force for the period delimited by START_DATE and END_DATE.
  • OVERRIDE_REASON — VARCHAR2(30). Foreign key to IGS_PS_WL_OVER_RESN, classifying why the workload was overridden.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns recording row provenance and change history.

Only FAC_OVR_WL_ID is documented as a unique business-key candidate; the remaining identifiers are foreign keys or descriptive attributes.

Common Use Cases and Queries

Typical scenarios include reporting the effective expected workload for a faculty member over an academic period, auditing who altered workload values and why, and reconciling overridden workloads against the parent assignment. A common query retrieves all overrides for a given parent workload:

SELECT FAC_OVR_WL_ID, FAC_WL_ID, START_DATE, END_DATE,
       NEW_EXP_WL, OVERRIDE_REASON
FROM   IGS.IGS_PS_FAC_OVR_WL
WHERE  FAC_WL_ID = :p_fac_wl_id
ORDER  BY START_DATE;

To find overrides active on a specific date, filter where START_DATE <= :as_of AND (END_DATE IS NULL OR END_DATE >= :as_of). Resolving the override reason label requires a join to IGS_PS_WL_OVER_RESN on OVERRIDE_REASON. Trend reporting by reason can be achieved with a GROUP BY on OVERRIDE_REASON, while audit extracts typically select the WHO columns alongside the surrogate key.

Related Objects

The most significant related objects are:

  • IGS.IGS_PS_FAC_WL — parent workload table; joined on FAC_WL_ID = IGS_PS_FAC_WL.FAC_WL_ID.
  • IGS.IGS_PS_WL_OVER_RESN — override reason reference table; joined on OVERRIDE_REASON.
  • APPS.IGS_PS_FAC_OVR_WL — the APPS synonym, the object normally queried from application code and reports.
  • IGS_PS_FAC_OVR_WL_PK — primary key constraint on FAC_OVR_WL_ID.
  • IGS_PS_FAC_OVR_WL_U1 — unique index on FAC_OVR_WL_ID, the documented business-key candidate.

Application-level dependent objects may include faculty workload concurrent programs, OAF pages, and enrollment or teaching-load reports that consume the overridden values.