Search Results igs_sv_convictions

Overview

The IGS_SV_CONVICTIONS table is a core data object within the Oracle E-Business Suite (EBS) Student System (IGS) module. Its primary function is to store and manage information regarding criminal convictions for foreign (non-immigrant) students. This data is critical for compliance with the U.S. Student and Exchange Visitor Information System (SEVIS) regulations. The table acts as a repository for conviction details that educational institutions are legally obligated to report to the U.S. Department of Homeland Security, thereby playing a vital role in maintaining institutional compliance and student visa status tracking within the EBS ecosystem.

Key Information Stored

Based on the documented metadata, the table's structure is defined by its primary and foreign keys, which dictate the core data relationships. The primary key is a composite of BATCH_ID and PERSON_ID, indicating that a student's conviction record is uniquely identified within a specific processing batch. While the exact detail columns are not enumerated in the provided excerpt, the table's purpose implies it would store fields such as the conviction date, nature of the offense, court details, and the specific data elements required for SEVIS reporting. The BATCH_ID and PERSON_ID columns are fundamental, linking each conviction record to a specific individual in the IGS_SV_PERSONS table and to a specific batch processing context.

Common Use Cases and Queries

The primary use case for this table is the generation of SEVIS reports for students with criminal convictions. Administrators would query this table to compile mandatory updates for transmission to the government. Common SQL operations include selecting all convictions for a specific student to review their record, or extracting all convictions within a given date range for batch SEVIS reporting. A typical query pattern would join IGS_SV_CONVICTIONS to IGS_SV_PERSONS to retrieve student identifiers (like SEVIS ID) along with the conviction details. For example: SELECT p.sevis_id, c.* FROM igs.igs_sv_convictions c JOIN igs.igs_sv_persons p ON c.batch_id = p.batch_id AND c.person_id = p.person_id WHERE c.conviction_date > SYSDATE - 30; Data maintenance tasks, such as inserting new conviction records upon notification or correcting existing entries, are also standard operational procedures.

Related Objects

The table has documented foreign key relationships that integrate it into the larger Student System schema for foreign student management. The key dependency is:

• IGS_SV_PERSONS: The table is directly linked via a foreign key relationship where the columns IGS_SV_CONVICTIONS.BATCH_ID and IGS_SV_CONVICTIONS.PERSON_ID reference the IGS_SV_PERSONS table. This ensures referential integrity, meaning every conviction record is associated with a valid, existing student person record within a specific batch context.

This relationship is fundamental, as it anchors the conviction data to the master student identity, enabling comprehensive student tracking and reporting.