Search Results int_occurs_id




Overview
Explore in AI Chat

Overview

IGS.IGS_PS_PREFS_SCH_INT_ALL is an interface (staging) table in the Oracle E-Business Suite Student Systems / Institutional Grants (IGS) product family. Its display name in the ETRM repository is "Import Scheduling Reference Code". The table stores the reference code types and reference codes defined at the unit section occurrence level within the scheduling interface, and its corresponding production table is IGS_PS_USEC_OCUR_REF. In practice, records are loaded into this interface table by an inbound scheduling import process, validated, and then propagated to the production reference-code table that drives unit section occurrence scheduling in Oracle Student System.

The object is owned by the IGS schema, holds public scope, has an active lifecycle, and is stored in the APPS_TS_INTERFACE tablespace with PCT Free 10. Under the metadata's Data Vault classification heuristic, this object is satellite-leaning: it carries descriptive attribute payload (reference code, reference code type, and their descriptions) attached to parent scheduling-interface entities rather than acting primarily as a hub or a many-to-many link.

Key Information Stored

The table contains fourteen columns. The most significant are:

The documented primary key IGS_PS_PREFS_SCH_INT_PK is composite (REFERENCE_CD, REFERENCE_CD_TYPE, TRANSACTION_ID, UNIT_SECTION_OCCURRENCE_ID). The viable business-key candidates are the unique indexes: IGS_PS_PREFS_SCH_INT_ALL_U1 (INT_PREFS_ID) and IGS_PS_PREFS_SCH_INT_ALL_U2 (INT_OCCURS_ID, REFERENCE_CD, REFERENCE_CD_TYPE). The latter enforces that a given reference code/type combination is unique per occurrence within the interface.

Common Use Cases and Queries

Typical uses include pre-import validation, duplicate detection, and reconciliation between the interface and production tables. A representative query retrieving all reference codes for an occurrence:

  • SELECT int_occurs_id, reference_cd, reference_cd_type, reference_code_description FROM igs.igs_ps_prefs_sch_int_all WHERE int_occurs_id = :p_occurs_id;
  • Join to the parent interface table to resolve occurrence context: SELECT a.int_occurs_id, a.reference_cd, b.* FROM igs.igs_ps_prefs_sch_int_all a, igs.igs_ps_sch_int_all b WHERE a.int_occurs_id = b.int_occurs_id;
  • Detect duplicate business keys prior to load: SELECT int_occurs_id, reference_cd, reference_cd_type, COUNT(*) FROM igs.igs_ps_prefs_sch_int_all GROUP BY int_occurs_id, reference_cd, reference_cd_type HAVING COUNT(*) > 1;
  • Compare staged rows against production: SELECT i.* FROM igs.igs_ps_prefs_sch_int_all i MINUS SELECT p. FROM igs.igs_ps_usec_ocur_ref p;
  • Operational reporting filtered by operating unit: SELECT * FROM igs.igs_ps_prefs_sch_int_all WHERE org_id = :p_org_id;

The interface tablespace and PL/SQL API-driven load convention means these tables are usually populated by submission of a concurrent import program rather than direct DML; query patterns are therefore read-mostly and short-lived.

Related Objects

  • IGS.IGS_PS_SCH_INT_ALL — parent scheduling interface entity; joined via INT_OCCURS_ID.
  • IGS.IGS_PS_USEC_OCUR_REF — the production reference-code table that this interface feeds.
  • IGS.IGS_PS_SCH_INT_ALL — occurrence-level interface header supplying INT_OCCURS_ID context.
  • IGS.IGS_PS_PREFS_SCH_INT_ALL indexes — U1 and U2, enforcing surrogate and business-key uniqueness.
  • Oracle Student System scheduling import concurrent programs and their PL/SQL validation APIs, which read from and purge this table after successful propagation to IGS_PS_USEC_OCUR_REF.