Search Results legal_res_cntry_code

Overview

IGS.IGS_SV_BIO_INFO is a transaction and staging table within the Oracle E-Business Suite Student Systems (IGS) product family, purpose-built for the collection, validation, and transmission of student biographical data required by the Student and Exchange Visitor Information System (SEVIS). The table stores both new and updated biographical records for foreign students and exchange visitors, capturing the identity, demographic, citizenship, and immigration attributes that U.S. federal reporting regulations mandate. Records are scoped to a SEVIS processing run through the BATCH_ID column, allowing multiple incremental or corrective submissions to coexist without overwriting prior data.

From a data modeling perspective, the mined foreign key structure suggests a satellite-leaning classification. The table references IGS_SV_PERSONS via BATCH_ID, indicating that its attributes are descriptive, time-variant, and subordinate to a governing SEVIS person entity, which is characteristic satellite behavior in a Data Vault model. This classification should be treated as a heuristic suggestion rather than a documented architectural decision.

The object resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, reflecting a transactional data workload with moderate update activity.

Key Information Stored

The table's composite surrogate primary key is IGS_SV_BIO_INFO_PK, defined on BATCH_ID and PERSON_ID. A unique index, IGS_SV_BIO_INFO_U1, mirrors the same two columns and serves as the business-key candidate enforcing one biographical record per person per batch. A non-unique index, IGS_SV_BIO_INFO_N1, supports lookups on PERSON_ID alone, which is important because most reporting queries retrieve history for a single individual rather than a batch.

• BATCH_ID — Identifier for the SEVIS processing run that produced the record.
• PERSON_ID — The person to whom the biographical record belongs.
• LEGAL_RES_CNTRY_CODE — The country code representing the student's country of legal residence, a mandatory SEVIS field for address and jurisdiction reporting.
• BIRTH_CNTRY_CODE — Country of birth.
• CITIZEN_CNTRY_CODE — Country of citizenship.
• BIRTH_DATE — Date of birth, stored as character data to match SEVIS submission format.
• FIRST_NAME, MIDDLE_NAME, LAST_NAME, SUFFIX — The name components transmitted to SEVIS.
• GENDER — Person's gender.
• VISA_TYPE — SEVIS code for the visa classification associated with the student.
• POSITION_CODE — SEVIS position code for the exchange visitor or student.
• COMMUTER — Flag indicating commuter status.
• PRINT_FORM — User-controlled flag governing whether SEVIS prints the form.
• REMARKS — Free-text notes, up to 500 characters.

Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) provide audit traceability.

Common Use Cases and Queries

The most frequent access pattern retrieves the current biographical snapshot for a student before a SEVIS batch is assembled. Because LEGAL_RES_CNTRY_CODE is often a focus of compliance audits, a typical query filters on that column:

• SELECT person_id, legal_res_cntry_code, citizen_cntry_code, visa_type FROM igs.igs_sv_bio_info WHERE batch_id = :p_batch AND person_id = :p_person;
• SELECT person_id, legal_res_cntry_code FROM igs.igs_sv_bio_info WHERE person_id = :p_person ORDER BY last_update_date DESC;
• Reporting against IGS_SV_BIO_INFO_N1 to pull the full historical biographical trail for one person across batches.
• Data quality extraction identifying rows where LEGAL_RES_CNTRY_CODE, CITIZEN_CNTRY_CODE, or BIRTH_CNTRY_CODE are null ahead of a SEVIS submission.

Related Objects

The principal relationship is the foreign key from IGS_SV_BIO_INFO.BATCH_ID to IGS_SV_PERSONS, which anchors biographical detail to the SEVIS person record. Practically, joins to IGS_SV_PERSONS on PERSON_ID are common to reconcile identity data with biographical attributes. SEVIS batch control and error tables referencing BATCH_ID are also relevant, as they determine which biographical rows were successfully transmitted. Country code columns resolve against FND territory or IGS lookup views. Concurrency programs that populate this table typically run under the IGS SEVIS processing framework and insert rows keyed by a newly generated BATCH_ID.