Search Results igs_ps_usec_sp_fees_u1

Overview

The IGS_PS_USEC_SP_FEES table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically designed for the Institutional Global Systems (IGS) module, which typically supports higher education functionalities. Its primary role is to store and manage special or ancillary fees associated with specific unit sections (UOO_ID). A unit section represents an offering or instance of a course. This table enables institutions to define and track non-standard fee amounts that apply to particular sections, beyond any standard tuition or program fees, facilitating precise financial tracking and billing within the academic operational model.

Key Information Stored

The table's columns are structured to capture the special fee definition, its financial value, status, and audit trail. The most critical fields include:

• USEC_SP_FEES_ID: A mandatory, unique numeric identifier serving as the primary key for each record.
• UOO_ID: The identifier for the associated unit section, forming part of a unique constraint with FEE_TYPE to prevent duplicate fee types per section.
• FEE_TYPE: A code (up to 10 characters) categorizing the nature of the special fee (e.g., lab, material, technology).
• SP_FEE_AMT: The monetary amount of the special fee.
• CLOSED_FLAG: A flag indicating whether the fee record is active or closed for further transactions.
• Standard WHO Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): Audit columns capturing the user and timestamp for record creation and modification, adhering to Oracle EBS conventions.

The table is stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, optimized for transactional data.

Common Use Cases and Queries

This table is central to processes involving the assessment and reporting of section-specific charges. Common operational scenarios include generating detailed fee invoices for students, calculating total cost of enrollment for a given term, and managing fee overrides or exceptions. For reporting and data extraction, the following SQL patterns are fundamental:

• Retrieve all active special fees for a specific unit section:
  SELECT * FROM IGS.IGS_PS_USEC_SP_FEES WHERE UOO_ID = <section_id> AND NVL(CLOSED_FLAG, 'N') = 'N';
• List all sections with a particular type of special fee (e.g., 'LAB'):
  SELECT UOO_ID, SP_FEE_AMT FROM IGS.IGS_PS_USEC_SP_FEES WHERE FEE_TYPE = 'LAB';
• Aggregate total special fees by fee type for reporting:
  SELECT FEE_TYPE, SUM(SP_FEE_AMT) AS TOTAL_AMOUNT FROM IGS.IGS_PS_USEC_SP_FEES WHERE CLOSED_FLAG = 'N' GROUP BY FEE_TYPE;

These queries are typically joined with related unit section and student enrollment tables to produce comprehensive financial statements.

Related Objects

While the provided dependency information states the table does not reference other objects, it is referenced by objects within the APPS schema. This indicates it is a foundational source table. Its integrity is enforced by two unique indexes: IGS_PS_USEC_SP_FEES_PK (on USEC_SP_FEES_ID) and IGS_PS_USEC_SP_FEES_U1 (on UOO_ID and FEE_TYPE). In a typical EBS IGS implementation, this table would logically relate to core academic structures, such as the table storing unit section definitions (likely keyed by UOO_ID), and is referenced by transactional and interface tables in the APPS schema for fee assessment, invoicing, and general ledger posting processes.