Results for “relationship_remarks”

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.

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_ID and PERSON_ID.
  • HZ_PARTIES — TCA party master; joined via PERSON_ID to 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_INFO during I-20/DS-2019 generation.