Results for “termination_effect_date”
9 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
The IGS_SV_DEPDNT_INFO table is a core data object within the Oracle E-Business Suite IGS — Student System product module. It stores dependent information for foreign students and exchange visitors who are tracked under the Student and Exchange Visitor Information System (SEVIS) reporting framework mandated by U.S. immigration authorities. In Oracle EBS 12.1.1 and 12.2.2, the table resides in the IGS schema and is classified as VALID in the ETRM repository.
From a dimensional modeling perspective, the mined foreign-key structure suggests this object behaves as a link table in Data Vault terms. It resolves relationships between a SEVIS person record (via BATCH_ID and PERSON_ID) and the corresponding dependent identity (via DEPDNT_ID), while simultaneously carrying descriptive attributes. This dual role — relational linkage plus descriptive payload — makes it a hybrid object that supports both integration and reporting workloads.
Key Information Stored
The table contains 33 documented columns. Its primary key, IGS_SV_DEPDNT_INFO_PK, is a composite surrogate spanning BATCH_ID, PERSON_ID, and DEPDNT_ID. A unique index, IGS_SV_DEPDNT_INFO_U1, mirrors these same three columns, confirming them as the business-key candidate set.
- BATCH_ID — Identifier for the SEVIS submission batch that owns this dependent record.
- PERSON_ID — Foreign key to the primary SEVIS person (student or exchange visitor); references
HZ_PARTIES. - DEPDNT_ID — Identifier of the dependent tied to the primary person.
- PERSON_NUMBER — Business-facing person number for the record.
- DEPDNT_SEVIS_ID — SEVIS-assigned identifier for the dependent.
- DEPDNT_ACTION_TYPE and VISA_TYPE — Classify the SEVIS action and dependent visa category.
- LAST_NAME, FIRST_NAME, MIDDLE_NAME, SUFFIX — Dependent name components.
- BIRTH_DATE, GENDER, BIRTH_CITY, BIRTH_CNTRY_CODE, CITIZEN_CNTRY_CODE — Demographic attributes required for SEVIS filings.
- RELATIONSHIP and RELATIONSHIP_REMARKS — Nature of the tie to the primary person.
- TERMINATION_REASON, TERMINATION_EFFECT_DATE, OTHER_REASON_REMARKS — Dependent status termination detail.
- PRINT_FORM, REPRINT_RSN_CODE, REPRINT_REMARKS — Control whether and why an I-20 or DS-2019 form is printed or reprinted.
Standard EBS audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) are also present.
Common Use Cases and Queries
Typical usage centers on SEVIS batch processing, dependent reporting, and audit reconciliation. A common reporting pattern joins dependents to their primary party record:
SELECT d.PERSON_ID, d.DEPDNT_ID, d.LAST_NAME,
d.FIRST_NAME, d.RELATIONSHIP, d.VISA_TYPE
FROM IGS.IGS_SV_DEPDNT_INFO d
WHERE d.BATCH_ID = :batch_id
ORDER BY d.PERSON_ID, d.DEPDNT_ID;
Batch verification queries filter by DEPDNT_ACTION_TYPE to isolate additions, changes, or terminations. Reprint auditing leverages REPRINT_RSN_CODE and PRINT_FORM. Terminated-dependency reporting filters on TERMINATION_EFFECT_DATE and TERMINATION_REASON.
Related Objects
- IGS_SV_PERSONS — Primary SEVIS person table; joined via
BATCH_IDandPERSON_ID. - HZ_PARTIES — TCA party master; joined via
PERSON_IDto reconcile identity across applications. - IGS_SV_BATCH — Batch control data governing SEVIS submissions.
- IGS_SV_EVENTS — SEVIS event records often correlated with dependent actions.
- HZ_PERSON_PROFILES — Extended party demographic detail.
- SEVIS extract processes and concurrent programs that populate or consume
IGS_SV_DEPDNT_INFOduring I-20/DS-2019 generation.
-
Table to hold the information on the dependent information for the Foreign students and Exchange Visitors for SEVIS.
-
Lookup Type: SV_DEPDNT_INFO 12.1.1
Dependent Information
-
Lookup Type: SV_DEPDNT_INFO 12.2.2
Dependent Information
-
eTRM - IGS Tables and Views 12.1.1
Holds applicant whose records are wrongly available . It is recommended that such applicant records are deleted from the system . It synchronizes with UCAS view 'ivStarW'.