Search Results std_exp_wl

Overview

IGS_PS_FAC_WL_V is an Oracle E-Business Suite view owned by the APPS schema and delivered as part of the IGS (Student System) product family. Its documented purpose is to store and present the workload assigned to faculty members within an academic institution. In Oracle EBS 12.1.1 and 12.2.2, the view functions as a reporting and integration layer over the faculty workload base table IGS_PS_FAC_WL, denormalizing that data with person, calendar instance, and lookup information so that consumers can retrieve faculty workload records without writing multi-table joins themselves.

Because IGS_PS_FAC_WL_V is a view rather than a table, it holds no data of its own and is subject to the same integrity and performance characteristics as its underlying objects. It is typically consumed by reports, concurrent programs, Oracle Forms-based inquiry screens, and external integrations that need a flattened perspective of faculty teaching and workload allocation across calendar categories and teaching periods.

Underlying Base Objects

The view text documents that it is defined over five source objects:

• IGS_PS_FAC_WL (PFW) — the primary base table holding the faculty workload rows. This drives the row cardinality of the view and supplies FAC_WL_ID, PERSON_ID, CALENDAR_CAT, CAL_TYPE, CI_SEQUENCE_NUMBER, and the ATTRIBUTE1–20 descriptive flexfield columns, along with the standard WHO audit columns.
• IGS_PE_PERSON_BASE_V (PE) — a person base view joined on PERSON_ID, supplying PERSON_NUMBER, FULL_NAME, GENDER, and BIRTH_DATE.
• IGS_CA_INST (CI) — the calendar instance, joined on CAL_TYPE and CI_SEQUENCE_NUMBER, supplying ALTERNATE_CODE for the teaching period.
• IGS_LOOKUP_VALUES (LKUP) — joined on LOOKUP_CODE = CALENDAR_CAT and LOOKUP_TYPE = 'CAL_CAT', supplying the meaningful calendar category description.
• FND_LOOKUP_VALUES (LKUP1) — joined as an outer join on lookup type 'HZ_GENDER' with VIEW_APPLICATION_ID 222 and LANGUAGE = USERENV('LANG'), supplying the SEX_MEANING value.

The ETRM metadata lists no other referenced base objects, and the joins are predominantly inner joins except for the gender lookup, which is outer-joined so that workload rows are preserved even where a gender lookup value is unavailable.

Key Columns

• ROW_ID — the ROWID of the underlying IGS_PS_FAC_WL row, useful for direct updates or drill-back.
• FAC_WL_ID / PERSON_ID — primary identifier of the workload record and the faculty member.
• PERSON_NUMBER / FULL_NAME / SEX_MEANING / BIRTH_DT — identifying and demographic attributes of the faculty member.
• CALENDAR_CAT / CALENDAR_CAT_MEANING — the code and decoded description of the calendar category, typically distinguishing teaching from non-teaching workload.
• CAL_TYPE / CI_SEQUENCE_NUMBER / ALTERNATE_CODE — the calendar type, instance sequence, and readable code identifying the period in which the workload falls.
• STD_EXP_WL — the standard expected workload column. This is the column most commonly targeted by users searching for "std_exp_wl" against this view, and it represents the expected workload value used when comparing actual allocated workload against the standard expectation for a faculty member in a given period.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–20 — descriptive flexfield columns carried forward from the base table for site-specific data capture.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN for auditing and reconciliation.

Common Use Cases and Queries

Typical scenarios include faculty workload reporting, standard-versus-actual workload analysis, and reconciliation of workload against the assigned calendar instance.

• Retrieve expected workload per faculty for a teaching period:
  SELECT person_number, full_name, calendar_cat_meaning, alternate_code, std_exp_wl FROM apps.igs_ps_fac_wl_v WHERE cal_type = :p_cal_type AND ci_sequence_number = :p_seq;
• Locate a faculty member's workload records by name:
  SELECT fac_wl_id, person_number, calendar_cat_meaning, std_exp_wl FROM apps.igs_ps_fac_wl_v WHERE full_name LIKE :p_name;
• Aggregate expected workload by calendar category:
  SELECT calendar_cat_meaning, SUM(std_exp_wl) FROM apps.igs_ps_fac_wl_v GROUP BY calendar_cat_meaning;

Because the view joins the person base view and the calendar instance, queries should be filtered on CAL_TYPE and CI_SEQUENCE_NUMBER wherever possible to limit the driving set and avoid unnecessary full scans of the underlying faculty workload table.