Search Results igs_ps_usec

Overview

IGS_PS_USEC_OCUR_REF is a reference table in the IGS (Student System) product schema of Oracle E-Business Suite, holding the occurrence reference records associated with a Unit Section. In EBS terms, a "unit section occurrence" represents a scheduled or offered instance of a unit section (for example, a specific class offering during an academic period). This table captures additional code-based categorisation for those occurrences, allowing an institution to tag each occurrence with one or more institution-defined reference codes.

The entity is narrow in scope and largely associative: it links a unit section occurrence to a reference code definition. From a Data Vault modelling perspective, the mined foreign-key structure classifies this object heuristically as a link table — it resolves a many-to-many style relationship between occurrence records and reference code values rather than storing descriptive attributes of a business entity on its own. The presence of standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) confirms it is a fully audited transactional table.

Key Information Stored

The table carries ten documented columns. The most significant are:

• UNIT_SEC_OCCUR_REFERENCE_ID — the surrogate primary key of the row, generated for each occurrence-to-reference assignment. It is backed by the unique index IGS_PS_USEC_OCUR_REF_U1.
• UNIT_SECTION_OCCURRENCE_ID — the foreign key to IGS_PS_USEC_OCCURS_ALL, identifying the unit section occurrence being tagged.
• REFERENCE_CODE_TYPE — the category (reference code type) of the code being applied to the occurrence. Together with REFERENCE_CODE this forms the business key.
• REFERENCE_CODE — the specific institution-defined code value assigned to the occurrence.
• REFERENCE_CODE_DESC — a denormalised description of the reference code, stored alongside the code for convenience in reporting and UI display.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle WHO audit columns recording row creation and last modification metadata.

A second unique index, IGS_PS_USEC_OCUR_REF_U2, enforces uniqueness on the (REFERENCE_CODE_TYPE, REFERENCE_CODE) pair. This is best understood as a business-key candidate: it ensures a given code type/code combination is not duplicated at the row level within the constraint's scope.

Common Use Cases and Queries

Typical reporting scenarios include listing all reference codes applied to a given unit section occurrence, or finding every occurrence tagged with a particular code (for example, an attendance mode, campus identifier, or delivery method). A basic retrieval pattern is:

• Join to IGS_PS_USEC_OCCURS_ALL on UNIT_SECTION_OCCURRENCE_ID to return occurrence details alongside the coded tags.
• Join to IGS_GE_REF_CD on (REFERENCE_CODE_TYPE, REFERENCE_CODE) to validate against the central reference-code definition and retrieve authoritative descriptions rather than relying on the stored REFERENCE_CODE_DESC.
• Filter by REFERENCE_CODE_TYPE to produce per-category reports, such as a census of all occurrences assigned to a specific delivery or location code.

Extracts for data warehouses or integration feeds commonly select UNIT_SEC_OCCUR_REFERENCE_ID, UNIT_SECTION_OCCURRENCE_ID, REFERENCE_CODE_TYPE, and REFERENCE_CODE, using the WHO columns for incremental delta loads based on LAST_UPDATE_DATE.

Related Objects

The table participates in two documented foreign-key relationships and is largely dependent on the following objects:

• IGS_PS_USEC_OCCURS_ALL — parent of the UNIT_SECTION_OCCURRENCE_ID foreign key; holds the unit section occurrence records this table annotates.
• IGS_GE_REF_CD — parent of the REFERENCE_CODE_TYPE / REFERENCE_CODE foreign key; the central reference code definition table that validates the codes used here.

Because the table is a dependent link entity, it is usually queried in conjunction with IGS_PS_USEC_OCCURS_ALL and, where authoritative descriptions are required, IGS_GE_REF_CD. No standalone public APIs are documented for this object; maintenance is generally performed through the standard Student System unit section and occurrence maintenance forms, which insert and update rows in this table as occurrence reference codes are assigned or changed.