Search Results citizen_cntry_code

Overview

IGS_SV_EV_EDT_BIO_V is a read-only database view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to fetch modified biographic information for Exchange Visitor students, meaning foreign nationals whose SEVIS records are maintained by an institution and who are processed through the Student and Exchange Visitor Information System (SEVIS). In EBS 12.1.1 and 12.2.2, the view serves as a filtered presentation layer over staging biographic data, exposing only those rows that have been queued for outbound transmission. It is typically consumed by concurrent programs and interfaces that assemble SEVIS batch files, such as the Exchange Visitor biographic update process. Because it joins staging detail to batch summary control data, the view encapsulates the selection logic that determines which biographical records are "ready to send," shielding downstream reports and integration code from the underlying table structure.

Underlying Base Objects

The view text is defined over two base tables in the APPS schema: IGS_SV_BIO_INFO and IGS_SV_BTCH_SUMMARY. IGS_SV_BIO_INFO (aliased BIO) holds the biographic detail records—name components, date of birth, gender, place and country of birth, citizenship, permanent residence country, position code, and remarks. IGS_SV_BTCH_SUMMARY (aliased BIOSUMM) holds batch-level control and status information, including the tag code and administrative action code that drive processing state. The join is a correlated two-key equijoin on PERSON_ID and BATCH_ID, ensuring that a biographic row is returned only when a matching summary row exists at the same batch. Two literal filters constrain the result: TAG_CODE must equal 'SV_BIO' and ADM_ACTION_CODE must equal 'SEND', so the view yields only biographic rows whose associated batch summary is flagged for the SV_BIO tag and marked with the SEND administrative action. No other referenced base objects are documented in the ETRM metadata.

Key Columns

The view exposes sixteen columns. The BATCH_ID and PERSON_ID columns identify the batch and the person and together form the join key to the summary table. Personal identifiers include LAST_NAME, FIRST_NAME, MIDDLE_NAME, and SUFFIX. Demographic and origin data are carried by BIRTH_DATE, GENDER, and BIRTH_CITY. Three country fields are surfaced: BIRTH_CNTRY_CODE, PERM_RES_CNTRY_CODE (mapped from BIO.LEGAL_RES_CNTRY_CODE via a column alias), and CITIZEN_CNTRY_CODE, the column most frequently referenced when users search for "citizen_cntry_code." Additional attributes are POSITION_CODE, REMARKS, and BIRTH_CNTRY_RESN_CODE, which supplies the reason code associated with the birth-country value. PRINT_FORM flags whether the form should be printed. Note that PERM_RES_CNTRY_CODE is an alias: the physical column on IGS_SV_BIO_INFO is LEGAL_RES_CNTRY_CODE, so queries must reference the view alias when selecting from this view.

Common Use Cases and Queries

Typical uses include troubleshooting a person's pending SEVIS biographic update, listing all records prepared for a named batch, and auditing citizenship or residence data prior to transmission. A simple lookup by person is:

• SELECT person_id, last_name, first_name, citizen_cntry_code, perm_res_cntry_code FROM igs_sv_ev_edt_bio_v WHERE person_id = :person_id;
• SELECT batch_id, COUNT(*) FROM igs_sv_ev_edt_bio_v GROUP BY batch_id ORDER BY batch_id;
• SELECT person_id, citizen_cntry_code FROM igs_sv_ev_edt_bio_v WHERE UPPER(citizen_cntry_code) = 'US';
• SELECT * FROM igs_sv_ev_edt_bio_v WHERE print_form = 'Y';

Because the view already enforces TAG_CODE = 'SV_BIO' and ADM_ACTION_CODE = 'SEND', callers cannot retrieve rows for other tags or non-SEND actions through this object; queries against the base tables IGS_SV_BIO_INFO and IGS_SV_BTCH_SUMMARY are required for those cases. As a view with no documented base-object dependencies beyond the two tables, it should be treated as a reporting and integration convenience rather than a data-maintenance target.