Results for “igs_sv_convictions”

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

Overview

The IGS_SV_CONVICTIONS table belongs to the IGS (Student System) product family, a legacy Oracle E-Business Suite module that supported international student administration and regulatory reporting. Its documented purpose is to hold conviction records for foreign students whose information must be transmitted to SEVIS, the Student and Exchange Visitor Information System operated by the United States government. In Oracle EBS 12.1.1 and 12.2.2, this table is classified as obsolete and is explicitly noted as "Not implemented in this database" in the ETRM metadata, meaning it survives as a schema artifact for backward compatibility and historical reference rather than as an active transactional object.

From a modeling perspective, the heuristic Data Vault classification mined from its foreign key structure is satellite-leaning. This suggests the table is best understood as a descriptive satellite attached to parent entities such as IGS_SV_PERSONS, capturing attributes about a specific student's conviction events rather than acting as an independent hub or a pure relationship link. The classification is heuristic and should be treated as a design suggestion, not a canonical Data Vault mapping.

Key Information Stored

The primary key is defined by IGS_SV_CONVICTIONS_PK, a composite of BATCH_ID and PERSON_ID. A separate unique index, IGS_SV_CONVICTIONS_U1 on BATCH_ID, PERSON_ID, and CONVICTION_ID, identifies the business-key candidate, indicating that a conviction is uniquely discerned by combining the batch, the person, and the conviction identifier. Nine other documented columns complete the eleven-column schema.

• BATCH_ID — Foreign key column referencing IGS_SV_PERSONS; groups records into a SEVIS submission batch.
• PERSON_ID — Foreign key column identifying the foreign student associated with the conviction.
• CONVICTION_ID — Business-key discriminator distinguishing multiple convictions for the same person, and part of the unique index.
• CRIMINAL_CONVICTION — The substantive conviction indicator, the core SEVIS-reportable attribute.
• PRINT_FORM — Flag controlling whether a related form is generated for the record.
• REMARKS — Free-text narrative supplying supplementary detail.
• CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns recording who created and last modified each row and when; LAST_UPDATE_LOGIN preserves the session identifier.

Common Use Cases and Queries

Because the table is obsolete, the principal practical use cases are legacy data migration, archival reporting, and verification of historical SEVIS submissions. A typical pattern joins IGS_SV_CONVICTIONS to IGS_SV_PERSONS on BATCH_ID and PERSON_ID to associate conviction records with the student demographic record:

SELECT c.BATCH_ID, c.PERSON_ID, c.CONVICTION_ID,
       c.CRIMINAL_CONVICTION, c.REMARKS,
       p.*
FROM   IGS_SV_CONVICTIONS c,
       IGS_SV_PERSONS p
WHERE  c.BATCH_ID  = p.BATCH_ID
AND    c.PERSON_ID = p.PERSON_ID
AND    c.CRIMINAL_CONVICTION = 'Y';

Reporting requirements commonly include listing unsubmitted convictions by batch, auditing conviction records where PRINT_FORM is flagged, and reconciling conviction counts per person. Migration projects filter on LAST_UPDATE_DATE to extract only records changed after a baseline, and archival scripts export rows grouped by BATCH_ID for retention compliance. Because the object is unimplemented, any operational query should be guarded by an existence check or run only against retained legacy schemas.

Related Objects

The most significant dependent object is the parent entity referenced through the documented foreign keys.

• IGS_SV_PERSONS — Parent table joined on BATCH_ID and PERSON_ID; supplies the student identity against which convictions are recorded.
• IGS_SV_CONVICTIONS_PK — Composite primary key constraint enforcing uniqueness of BATCH_ID and PERSON_ID pairs.
• IGS_SV_CONVICTIONS_U1 — Unique index expanding the business key to include CONVICTION_ID.
• Other IGS_SV_* tables — Sibling SEVIS satellite tables within the same batch and person context, which share the BATCH_ID and PERSON_ID access paths and are typically queried together for complete SEVIS extracts.
• SEVIS submission batch objects — Batch-level parent records that govern the grouping of conviction rows for transmission.

Given its obsolete status, integration through documented APIs is not expected; consumers should treat IGS_SV_CONVICTIONS strictly as a read-oriented historical source.