Search Results igs_ps_usec_ref_cd_uk

Overview

The IGS_PS_USEC_REF_CD table is a core data entity within the Oracle E-Business Suite Student System (IGS), specifically designed for release versions 12.1.1 and 12.2.2. Its primary function is to establish and manage the relationships between Unit Section References and standardized Reference Codes. This table acts as a junction entity, enabling the association of specific academic unit sections with various types of coded classifications or attributes defined elsewhere in the system. It is critical for maintaining referential integrity and supporting data-driven rules or reporting based on unit section characteristics. Notably, the ETRM metadata explicitly classifies the IGS product as "Obsolete," indicating this table is part of a legacy module that may be deprecated in future releases, though it remains operational in the specified versions.

Key Information Stored

The table stores the unique linkage between a unit section reference and a reference code pair. The primary identifier is the UNIT_SECTION_REFERENCE_CD_ID, a system-generated primary key. The core relational data consists of the UNIT_SECTION_REFERENCE_ID, which points to a specific unit section record, and the combination of REFERENCE_CODE_TYPE and REFERENCE_CODE. This code-type and code pair links to a defined value in the system's reference code master tables. The structure enforces uniqueness on the combination of UNIT_SECTION_REFERENCE_ID, REFERENCE_CODE_TYPE, and REFERENCE_CODE through its unique key constraint (IGS_PS_USEC_REF_CD_UK), preventing duplicate associations for the same section.

Common Use Cases and Queries

This table is primarily used in scenarios requiring the filtering, reporting, or application of business logic based on coded attributes attached to unit sections. For instance, it could be used to identify all unit sections tagged with a specific "Delivery Mode" reference code (e.g., "ONLINE") or a particular "Faculty" code. A common reporting query would join this table to the unit section reference and reference code description tables. A typical SQL pattern involves selecting unit section details filtered by a reference code:

• SELECT usec_ref.unit_section_reference_id, ref_cd.reference_code, ref_cd.meaning FROM igs_ps_usec_ref_cd usec_ref_cd JOIN igs_ge_ref_cd ref_cd ON usec_ref_cd.reference_code_type = ref_cd.reference_code_type AND usec_ref_cd.reference_code = ref_cd.reference_code WHERE usec_ref_cd.unit_section_reference_id = :1;

Data integrity checks, such as identifying unit section references with missing reference code definitions, are another critical use case, leveraging the foreign key relationships.

Related Objects

The IGS_PS_USEC_REF_CD table is centrally connected to three key master tables via foreign key constraints, as documented in the ETRM metadata:

• IGS_PS_USEC_REF: The parent table for Unit Section References. The foreign key on UNIT_SECTION_REFERENCE_ID ensures every record in IGS_PS_USEC_REF_CD corresponds to a valid unit section.
• IGS_GE_REF_CD_TYPE_ALL: The master table for Reference Code Types. The foreign key on REFERENCE_CODE_TYPE validates that the code type used is defined in the system.
• IGS_GE_REF_CD: The master table for Reference Codes. The composite foreign key on (REFERENCE_CODE_TYPE, REFERENCE_CODE) ensures the specific code value is valid for the given code type.

These relationships form a critical data model where IGS_PS_USEC_REF_CD is the child of IGS_PS_USEC_REF and the child of IGS_GE_REF_CD, with IGS_GE_REF_CD_TYPE_ALL providing the overarching type definition.