Search Results igs_ps_usec_category_u2

Overview

The IGS_PS_USEC_CATEGORY table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (Campus Solutions) product family under the IGS (iGrants) schema. Its primary function is to manage the assignment of institutional unit categories to specific unit sections. A unit section represents a scheduled offering of a unit (or course), and this table allows administrators to classify these offerings based on shared characteristics, such as delivery mode, funding source, or academic program type. This categorization is critical for reporting, compliance, fee calculation, and academic planning within the institution.

Key Information Stored

The table is designed to uniquely map a unit category to a unit section. The key columns are:

• USEC_CAT_ID: A mandatory, system-generated unique identifier (primary key) sourced from the sequence IGS_PS_USEC_CAT_S. This is the primary technical key for the record.
• UOO_ID: A mandatory foreign key referencing IGS_PS_UNIT_OFR_OPT_ALL. This column stores the unique identifier for the specific unit offering option (section) to which the category is being assigned.
• UNIT_CAT: A mandatory, institution-defined VARCHAR2(10) code representing the unit category. This foreign key references IGS_PS_UNIT_CAT and is the business key for the categorization.
• Standard WHO Columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN for audit trail purposes.

The table enforces two unique constraints: one on the primary key (USEC_CAT_ID) and another on the combination of UOO_ID and UNIT_CAT, ensuring a unit section cannot be assigned the same category more than once.

Common Use Cases and Queries

This table is central to processes that depend on section-level categorization. A common use case is generating reports for government funding or accreditation that require counting student enrollments or unit sections by specific category types. Another is driving business rules for fee assessments or enrollment eligibility based on the section's category.

A fundamental query retrieves all category assignments for analysis:

SELECT uc.USEC_CAT_ID,
       uc.UOO_ID,
       uc.UNIT_CAT,
       uoo.UNIT_CD,
       uoo.VERSION_NUMBER
FROM IGS.IGS_PS_USEC_CATEGORY uc,
     IGS.IGS_PS_UNIT_OFR_OPT_ALL uoo
WHERE uc.UOO_ID = uoo.UOO_ID
AND uc.UNIT_CAT = 'RESEARCH'; -- Example category code

To find sections without a specific category assigned, an anti-join pattern with the UNIT_OFR_OPT_ALL table would be used, leveraging the foreign key relationship on UOO_ID.

Related Objects

The IGS_PS_USEC_CATEGORY table sits within a defined relational model, primarily linking to master and transactional tables for units and their offerings.

• Primary Key: IGS_PS_USEC_CATEGORY_PK on the USEC_CAT_ID column.
• Foreign Key (Reference from): The UNIT_CAT column references the IGS_PS_UNIT_CAT table, validating the category code against the institution's defined list.
• Foreign Key (Reference from): The UOO_ID column references the IGS_PS_UNIT_OFR_OPT_ALL table, which holds the detailed definition of the unit section (offering option).
• Sequence: The IGS_PS_USEC_CAT_S sequence supplies values for the USEC_CAT_ID primary key.
• Indexes: IGS_PS_USEC_CATEGORY_U1 (unique on USEC_CAT_ID) and IGS_PS_USEC_CATEGORY_U2 (unique on UOO_ID, UNIT_CAT) enforce data integrity and optimize query performance.