Search Results igs_ps_prefs_sch_int_pk

Overview

IGS_PS_PREFS_SCH_INT_ALL is a transactional table in the Oracle E-Business Suite Student System (IGS) product family, owned by the IGS schema. It describes the preferences attached to unit section occurrences within the scheduling interface. In practical terms, the table records the scheduling preferences applied to individual section occurrences so that the Student System scheduling engine can honour those preferences when placing classes, allocating resources, or resolving timetable conflicts. The object is documented as VALID in ETRM for release 12.1.1, and the same definition is retained in 12.2.2.

Heuristic Data Vault classification mined from the foreign-key structure suggests this table is satellite-leaning. It does not behave as an independent hub of business entities; rather, it carries descriptive and qualifying attributes — the preference records themselves — that depend on a parent scheduling interface row. In a Data Vault model it would most naturally be modelled as a satellite hanging off the scheduling interface hub or link, with a dependent child relationship to IGS_PS_SCH_INT_ALL.

Key Information Stored

The table carries fourteen documented columns spanning identifiers, descriptive attributes, and standard Oracle audit columns.

• INT_PREFS_ID — the surrogate identifier for the preference record; it is protected by the unique index IGS_PS_PREFS_SCH_INT_ALL_U1 and is the most convenient single-column lookup key.
• INT_OCCURS_ID — the scheduling interface occurrence this preference belongs to. It is a foreign key to IGS_PS_SCH_INT_ALL and forms the leading column of the business-key unique index IGS_PS_PREFS_SCH_INT_ALL_U2.
• REFERENCE_CD and REFERENCE_CD_TYPE — the coded value and the type (lookup category) that describe the preference. Together with INT_OCCURS_ID they complete the U2 business key, making the trio a stable natural identifier.
• REFERENCE_CODE_DESCRIPTION and REFERENCE_TYPE_DESCRIPTION — denormalised, human-readable descriptions of the reference code and reference type, useful for reporting without joining the lookup views.
• TRANSACTION_ID and UNIT_SECTION_OCCURRENCE_ID — additional context identifiers that participate, alongside REFERENCE_CD and REFERENCE_CD_TYPE, in the declared primary key IGS_PS_PREFS_SCH_INT_PK.
• ORG_ID — the operating unit, enabling multi-org (MOAC) security and filtering.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle audit trail columns present in almost every EBS transactional table.

Note that the table exposes two distinct uniqueness assertions: the declared primary key IGS_PS_PREFS_SCH_INT_PK (REFERENCE_CD, REFERENCE_CD_TYPE, TRANSACTION_ID, UNIT_SECTION_OCCURRENCE_ID), and two unique indexes — U1 on INT_PREFS_ID and U2 on (INT_OCCURS_ID, REFERENCE_CD, REFERENCE_CD_TYPE). The U1/U2 indexes function as the practical business-key candidates.

Common Use Cases and Queries

Typical usage centres on retrieving the preferences recorded for a given scheduling interface occurrence, or auditing the preference codes applied to a unit section occurrence.

SELECT p.int_prefs_id,
       p.reference_cd,
       p.reference_code_description,
       p.reference_cd_type,
       p.reference_type_description
FROM   igs.igs_ps_prefs_sch_int_all p
WHERE  p.int_occurs_id = :p_occurs_id
AND    p.org_id       = :p_org_id;

A second common pattern resolves the parent scheduling interface row for reporting:

SELECT s.*, p.reference_cd, p.reference_code_description
FROM   igs.igs_ps_sch_int_all s,
       igs.igs_ps_prefs_sch_int_all p
WHERE  p.int_occurs_id = s.int_occurs_id
AND    p.org_id        = :p_org_id;

Reporting use cases include extracting preference mixes by operating unit, identifying section occurrences that have no preferences recorded, and auditing preference changes via the LAST_UPDATED_BY / LAST_UPDATE_DATE audit columns. Lookups normally filter by INT_PREFS_ID (U1) for single-row fetches or by the (INT_OCCURS_ID, REFERENCE_CD, REFERENCE_CD_TYPE) triplet (U2) when resolving from a known occurrence and code.

Related Objects

• IGS_PS_SCH_INT_ALL — the parent scheduling interface table; IGS_PS_PREFS_SCH_INT_ALL.INT_OCCURS_ID joins to it, and this is the sole documented foreign key.
• IGS_PS_PREFS_SCH_INT_ALL_U1 — the unique index on INT_PREFS_ID, supporting the surrogate key lookup path.
• IGS_PS_PREFS_SCH_INT_ALL_U2 — the composite unique index on (INT_OCCURS_ID, REFERENCE_CD, REFERENCE_CD_TYPE), the practical business key.
• IGS_PS_PREFS_SCH_INT_PK — the declared primary key index on (REFERENCE_CD, REFERENCE_CD_TYPE, TRANSACTION_ID, UNIT_SECTION_OCCURRENCE_ID).
• IGS_PS_SECTION_OCCURRENCES — the unit section occurrence entity referenced by UNIT_SECTION_OCCURRENCE_ID.
• IGS_LOOKUPS / FND_LOOKUP_VALUES — the reference-code and reference-type lookups that populate REFERENCE_CD and REFERENCE_CD_TYPE.

The table is written to and read by the scheduling interface processes within the IGS Student System, and its rows are typically maintained alongside their parent scheduling interface rows so that preference data remains consistent across the IGS_PS_SCH_INT_ALL and IGS_PS_PREFS_SCH_INT_ALL pair.