Search Results igs_ps_fac_wl_u2

Overview

IGS.IGS_PS_FAC_WL is a transactional table within the Oracle E-Business Suite Student System (IGS) schema that stores faculty workload records. Each row associates a faculty member, identified by PERSON_ID, with a specific calendar instance, capturing the workload assigned for that person within a defined academic or term calendar. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its two unique indexes are stored in APPS_TS_TX_IDX, consistent with standard Oracle EBS transaction data placement conventions.

From a Data Vault modeling perspective, the relationship metadata suggests a satellite-leaning classification. The table carries a sequence-generated surrogate primary key, FAC_WL_ID, supplemented by descriptive and foreign-key attributes, and is referenced by dependent child tables rather than acting as a central integration point between multiple business hubs. It thus behaves largely as a descriptive satellite attached to the faculty person hub, with additional link characteristics through its calendar references.

Key Information Stored

The table contains 32 documented columns in the 12.1.1 schema. The most significant columns are:

• FAC_WL_ID (NUMBER 15, mandatory) — the sequence-generated surrogate primary key and the column behind unique index IGS_PS_FAC_WL_U1.
• PERSON_ID (NUMBER 15, mandatory) — the person identifier of the faculty member; a foreign key to HZ_PARTIES.
• CAL_TYPE (VARCHAR2 10) — the calendar type for which workload is set; a foreign key to IGS_CA_INST_ALL.
• CI_SEQUENCE_NUMBER (NUMBER) — the sequence number of the calendar instance for which workload is recorded.
• CALENDAR_CAT (VARCHAR2 30) — the calendar category; permissible values include Term (Load) and academic calendar categories.
• STD_EXP_WL (NUMBER) — retained for backward compatibility; documented as no longer used.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 (VARCHAR2) — the descriptive flexfield (DFF) context and attribute columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS who-columns for audit and concurrency tracking.

Unique index IGS_PS_FAC_WL_U2, defined on PERSON_ID, CAL_TYPE, and CI_SEQUENCE_NUMBER, forms the principal business-key candidate. It enforces that a faculty member has at most one workload record per calendar type and calendar instance sequence, making this composite the natural identifier for the assignment.

Common Use Cases and Queries

Typical scenarios center on faculty workload reporting, teaching-load validation, and downstream task or override processing. A common query pattern joins the workload table to calendar instances and person records:

• Retrieve all workload rows for a person: SELECT * FROM IGS.IGS_PS_FAC_WL WHERE PERSON_ID = :person_id;
• Look up a specific business key: SELECT * FROM IGS.IGS_PS_FAC_WL WHERE PERSON_ID = :p AND CAL_TYPE = :t AND CI_SEQUENCE_NUMBER = :s;
• Join to IGS_CA_INST_ALL on CAL_TYPE and the calendar instance sequence to obtain calendar descriptions for reporting.
• Join to HZ_PARTIES on PERSON_ID to resolve faculty names for workload reports.
• Aggregate workload counts by calendar category (Term/Load versus academic) to validate faculty load distribution.

Because the table hosts a DFF, reports frequently filter on ATTRIBUTE_CATEGORY and specific ATTRIBUTEn columns where institutions have configured local context values. Reporting should rely on the unique business key rather than FAC_WL_ID when reconciling source data.

Related Objects

The following objects are most significant in relation to this table:

• IGS_PS_FAC_ASG_TASK — references IGS_PS_FAC_WL via FAC_WL_ID; holds faculty assignment task records tied to a workload.
• IGS_PS_FAC_OVR_WL — references IGS_PS_FAC_WL via FAC_WL_ID; stores override workload entries for a faculty workload record.
• HZ_PARTIES — referenced by PERSON_ID; provides the party/person identity of the faculty member.
• IGS_CA_INST_ALL — referenced by CAL_TYPE; supplies calendar instance definitions used to contextualize the workload.
• IGS_PS_FAC_WL_U1 / IGS_PS_FAC_WL_U2 — the unique indexes enforcing the surrogate and composite business keys respectively.
• FND Design Data: IGS.IGS_PS_FAC_WL — the registered design metadata entry governing the table's definition in the EBS data model.