Search Results igs_sv_addresses




Overview
Explore in AI Chat

Overview

IGS.IGS_SV_ADDRESSES is a transactional table within the Oracle E-Business Suite Student System (IGS) product family. It stores address information for foreign students and exchange visitors, including United States addresses, foreign addresses, and Site of Activity addresses associated with SEVIS (Student and Exchange Visitor Information System) reporting. The table supports regulatory tracking of non-immigrant students and scholars, capturing the multiple address contexts required by US immigration authorities. Record identity is established by the composite primary key IGS_SV_ADDRESSES_PK, defined on the columns BATCH_ID, PERSON_ID, and PARTY_SITE_ID.

From a heuristic Data Vault modeling perspective, the mined foreign-key structure classifies this object as standalone. This suggests it may appropriately be modeled as a satellite, capturing descriptive address attributes that change over time, keyed to a batch identifier, party, and site combination, rather than as a hub or link.

Key Information Stored

The table contains 23 documented columns in the 12.1.1 physical schema. The most significant columns are:

  • BATCH_ID, PERSON_ID, PARTY_SITE_ID — the composite primary key. BATCH_ID typically ties records to a processing run or reporting batch, PERSON_ID identifies the individual, and PARTY_SITE_ID references the specific address party site.
  • ADDRESS_TYPE — distinguishes among US, foreign, and Site of Activity address categories.
  • ADDRESS_LINE1, ADDRESS_LINE2 — street-level address detail.
  • CITY, STATE, POSTAL_CODE, POSTAL_ROUTING_CODE, COUNTRY_CODE, PROVINCE — the geographic components of the address, supporting both domestic and international formats.
  • PRIMARY_FLAG — indicates whether this address is the primary/preferred record for the person.
  • STDNT_VALID_FLAG — a validation indicator, typically reflecting student address verification.
  • ACTIVITY_SITE_CD — the Site of Activity code, relevant to SEVIS reporting requirements.
  • ACTION_TYPE and PRINT_FORM — track the processing action and whether the address populates a printed form.
  • Audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — standard Oracle EBS who-columns.
  • REMARKS — free-text notes.

The unique index IGS_SV_ADDRESSES_U1 duplicates the primary key columns (BATCH_ID, PERSON_ID, PARTY_SITE_ID), confirming that combination as the business-key candidate. No separate surrogate sequence column is documented, so the natural composite key serves as the identifier.

Common Use Cases and Queries

This table is typically queried for SEVIS compliance reporting, address verification, and student/exchange visitor record maintenance. A common pattern retrieves all addresses for a given person:

  • SELECT * FROM igs.igs_sv_addresses WHERE person_id = :person_id;
  • Filtering by address category: SELECT * FROM igs.igs_sv_addresses WHERE person_id = :person_id AND address_type = 'US';
  • Identifying primary addresses: SELECT * FROM igs.igs_sv_addresses WHERE person_id = :person_id AND primary_flag = 'Y';
  • Isolating a reporting batch: SELECT * FROM igs.igs_sv_addresses WHERE batch_id = :batch_id;

Reporting use cases include extracting Site of Activity addresses (via ACTIVITY_SITE_CD) for a batch, auditing records with STDNT_VALID_FLAG set to 'N' for data-quality review, and joining to person and party-site tables to produce mailing labels or regulatory submissions.

Related Objects

Although the mined Data Vault classification marks this object as standalone, its key columns imply relationships with core IGS and HR objects:

  • IGS_SV_ADDRESSES_U1 / IGS_SV_ADDRESSES_PK — the unique index and primary key enforcing uniqueness on BATCH_ID, PERSON_ID, PARTY_SITE_ID.
  • PER_ALL_PEOPLE_F — joined on PERSON_ID to resolve the individual's name, number, and attributes.
  • HZ_PARTY_SITES / HZ_PARTY_SITE_USES — joined on PARTY_SITE_ID to obtain registered party site information.
  • IGS_SV_PERSON / IGS_SV_STUDENT — related SEVIS person and student records keyed by PERSON_ID.
  • IGS_SV_BATCH or equivalent batch control tables — joined on BATCH_ID to resolve processing-run context.
  • IGS_SV_ADDRESSES audit tables or interface staging tables — used during batch validation and load.

Because the authoritative foreign keys are not fully enumerated in the metadata, join columns should be validated against the deployed IGS schema before use in production queries.