Search Results igs_ps_sch_usec_int_all




Overview
Explore in AI Chat

Overview

The IGS_PS_SCH_USEC_INT_ALL table is a core interface table within the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to capture Scheduler Interface Unit Section data, acting as a staging and integration surface through which externally originated or batch-loaded unit section records are validated and propagated into the scheduler subsystem. The table resides in the IGS schema alongside the broader set of IGS_PS_SCH_* interface and base tables that together comprise the scheduling (SCH) component of the Student System.

Architecturally, the ETRM metadata describes the table as belonging to the scheduling interface layer rather than to transactional or enrollment processing. Records flow in from upstream sources identified by transaction and extract-pattern identifiers, and are subsequently consumed by dependent scheduler tables. From a Data Vault modeling perspective, the mined foreign key structure suggests a hub-leaning classification: the table carries the distinct business entity key INT_USEC_ID as its primary key, while retaining descriptive attributes such as unit title, section dates, enrollment counts, and location. Analysts modeling this object for an enterprise data warehouse may therefore treat it as a hub candidate with attached descriptive context, though the interface-table nature and the presence of TRANSACTION_ID and INT_PAT_ID indicate batch grouping semantics as well.

Key Information Stored

The table comprises 33 documented columns. The following are the most significant for functional and integration purposes:

  • INT_USEC_ID — the surrogate primary key, enforced by IGS_PS_SCH_USEC_INT_ALL_PK and additionally by unique index IGS_PS_SCH_USEC_INT_ALL_U1. It uniquely identifies each interface unit section record and is reused as the foreign key by dependent child tables.
  • UOO_ID — the unit offering option identifier; together with TRANSACTION_ID it forms the second unique index, IGS_PS_SCH_USEC_INT_ALL_U2, which serves as a documented business-key candidate.
  • TRANSACTION_ID — groups records into a batch and links to the scheduler header interface table IGS_PS_SCH_HDR_INT_ALL. It is essential for identifying the source run of an interface load.
  • INT_PAT_ID — the interface extract-pattern identifier, which ties records to a specific data extraction pattern or source system definition.
  • UNIT_CD, VERSION_NUMBER, UNIT_TITLE, SUBTITLE — the academic unit identity: the unit code, its version, and the descriptive titles used for reporting and validation.
  • UNIT_SECTION_START_DATE and UNIT_SECTION_END_DATE — the teaching period boundaries for the unit section.
  • UNIT_SECTION_STATUS — the current lifecycle status of the section as supplied by the interface source.
  • ENROLLMENT_MAXIMUM and ENROLLMENT_ACTUAL — capacity and realized enrollment counts, with ENROLLMENT_EXPECTED and OVERRIDE_ENROLLMENT_MAX providing planning values and administrative capacity overrides respectively.
  • LOCATION_CD — the delivery location for the section, a common filter in scheduling reports.
  • IMPORT_DONE_FLAG and ABORT_FLAG — processing control indicators that signal whether the record has been successfully imported and whether the transaction was aborted.
  • ORG_ID and OWNER_ORG_UNIT_CD — the operating unit and owning academic organization, enabling multi-org security filtering.

Standard EBS audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — are also present.

Common Use Cases and Queries

Typical usage centers on monitoring, reconciliation, and diagnostics of scheduler interface loads. A common pattern identifies failed or stalled transactions:

SELECT INT_USEC_ID, UNIT_CD, TRANSACTION_ID, ABORT_FLAG
FROM   IGS_PS_SCH_USEC_INT_ALL
WHERE  NVL(IMPORT_DONE_FLAG,'N') = 'N';

Capacity and enrollment reporting is another frequent scenario, aggregating sections by location and status to support timetable planning. Integration reconciliation joins the interface record to its transaction header using TRANSACTION_ID to confirm that every unit section belongs to a recognized header batch. Diagnostics on multi-org data use ORG_ID predicates to restrict results to the appropriate operating unit, while joins through INT_PAT_ID isolate records originating from a particular extraction pattern. Because the table is an interface object, purge and archiving routines commonly select records where IMPORT_DONE_FLAG is set and IMPORT processing dates are older than a retention threshold.

Related Objects

The FK metadata documents the following significant relationships:

  • IGS_PS_SCH_HDR_INT_ALL — referenced via TRANSACTION_ID; the header-level interface transaction that groups unit section records.
  • IGS_PS_SCH_MWC_ALL — references this table via INT_USEC_ID; the base scheduler unit section record populated from the interface.
  • IGS_PS_SCH_INT_ALL — references this table via INT_USEC_ID; a dependent scheduling interface table.
  • IGS_PS_SCH_X_USEC_INT_ALL — references this table via INT_USEC_ID; an extension interface table carrying additional unit section data.
  • IGS_PS_SCH_USEC_INT_ALL.INT_PAT_ID — the documented self-referencing/pattern linkage column that associates interface records with their extract pattern.

Together these objects form the scheduler interface chain: header records in IGS_PS_SCH_HDR_INT_ALL drive transaction batches, unit section records land in IGS_PS_SCH_USEC_INT_ALL, extension data arrives in IGS_PS_SCH_X_USEC_INT_ALL, and validated results propagate into IGS_PS_SCH_MWC_ALL and IGS_PS_SCH_INT_ALL for downstream scheduling use.