Results for “igs_ps_usec_ocur_ref_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS.IGS_PS_USEC_OCUR_REF is a transactional table in the Oracle E-Business Suite Student Systems (IGS) schema. Its documented purpose is to capture the occurrences of a unit section, meaning it stores reference-code driven attributes that qualify or classify individual unit section occurrences. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its indexes are placed in APPS_TS_TX_IDX, consistent with the standard partitioning of transactional data and index storage in an EBS 12.1.1 / 12.2.2 environment.

Under the heuristic Data Vault classification derived from the foreign-key structure, this object is best modeled as a link table. It associates two independent parent entities — the unit section occurrence (IGS_PS_USEC_OCCURS_ALL) and a reference code definition (IGS_GE_REF_CD) — and carries descriptive attributes of that association (the reference code, its description, and the standard Who columns). The modeling suggestion is therefore that IGS_PS_USEC_OCUR_REF functions as the connective record between an occurrence and the reference codes applied to it, rather than as a standalone hub or a pure satellite of a single parent.

Key Information Stored

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

• UNIT_SEC_OCCUR_REFERENCE_ID (NUMBER(10), mandatory) — the surrogate unique identifier for each occurrence-reference row. This column is enforced by the unique index IGS_PS_USEC_OCUR_REF_U1 and is the column referenced by the user's search term "igs_ps_usec_ocur_ref_u1".
• UNIT_SECTION_OCCURRENCE_ID (NUMBER(10)) — the foreign key to IGS_PS_USEC_OCCURS_ALL, tying the reference to a specific unit section occurrence.
• REFERENCE_CODE_TYPE (VARCHAR2(10)) — the classification of the reference code applied to the occurrence; indexed non-uniquely by IGS_PS_USEC_OCUR_REF_N1 and part of the documented composite primary key candidate.
• REFERENCE_CODE (VARCHAR2(30)) — the actual code value drawn from the reference code set.
• REFERENCE_CODE_DESC (VARCHAR2(60)) — a descriptive expansion of the reference code, stored locally for reporting convenience.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS Who columns that provide auditability and change tracking.

Two business-key candidates are documented: the unique index IGS_PS_USEC_OCUR_REF_U1 on UNIT_SEC_OCCUR_REFERENCE_ID, and a heuristic primary key IGS_PS_USEC_OCUR_REF_U2 on (REFERENCE_CODE_TYPE, REFERENCE_CODE). The latter implies that a given reference code type/code pair should appear at most once within a logical grouping.

Common Use Cases and Queries

Typical usage involves retrieving the reference codes attached to unit section occurrences for reporting, validation, and integration. A standard retrieval pattern follows the documented query skeleton:

• Extract all reference codes for a given occurrence: SELECT * FROM IGS.IGS_PS_USEC_OCUR_REF WHERE UNIT_SECTION_OCCURRENCE_ID = :occurrence_id;
• List occurrences by code type: SELECT UNIT_SECTION_OCCURRENCE_ID, REFERENCE_CODE, REFERENCE_CODE_DESC FROM IGS.IGS_PS_USEC_OCUR_REF WHERE REFERENCE_CODE_TYPE = :type;
• De-duplicate or validate business keys using the composite candidate: group by REFERENCE_CODE_TYPE and REFERENCE_CODE to detect unexpected duplicates.
• Join to the parent occurrence table to enrich reporting with section, term, and unit attributes.

Because the table is referenced by the APPS synonym IGS_PS_USEC_OCUR_REF, reports and concurrent programs in the APPS schema can query it directly without qualifying the IGS schema.

Related Objects

The foreign-key metadata identifies the primary related objects:

• IGS.IGS_PS_USEC_OCCURS_ALL — referenced via UNIT_SECTION_OCCURRENCE_ID; the parent that defines each unit section occurrence.
• IGS.IGS_GE_REF_CD — referenced via REFERENCE_CODE_TYPE; the reference code lookup that supplies valid code types.
• APPS.IGS_PS_USEC_OCUR_REF — the APPS synonym through which the table is exposed to application code.
• Index objects IGS_PS_USEC_OCUR_REF_U1, IGS_PS_USEC_OCUR_REF_N1, and the documented candidate IGS_PS_USEC_OCUR_REF_U2 accompany the table and govern access paths.

No database object is referenced by IGS_PS_USEC_OCUR_REF beyond the two foreign keys noted above; it is a leaf dependency that depends on IGS_PS_USEC_OCCURS_ALL and IGS_GE_REF_CD.