Search Results igs_en_svs_auth_n1

Overview

IGS.IGS_EN_SVS_AUTH is a transaction table in the Oracle E-Business Suite Student System (IGS) schema that stores SEVIS Authorization Records. SEVIS — the Student and Exchange Visitor Information System — is the U.S. Department of Homeland Security system through which institutions report and certify international students and exchange visitors on F, M, and J visas. In Oracle EBS 12.1.1 and 12.2.2, this table captures the authorization credentials and validity windows issued by SEVIS to an individual, allowing the institution to track which authorizations exist, when they are effective, and whether they have been cancelled.

The table is classified in the FND design data repository as IGS.IGS_EN_SVS_AUTH and resides in the APPS_TS_TX_DATA tablespace, with indexes in APPS_TS_TX_IDX. Its heuristic Data Vault classification is satellite-leaning. This is a modeling suggestion rather than a physical implementation: the table behaves like a satellite attached to a hub or link keyed by the person, because it holds descriptive, time-bounded attributes (authorization code, start and end dates, cancellation state) about a business entity rather than acting as a standalone hub.

Key Information Stored

The surrogate primary key is SEVIS_AUTH_ID, a sequence-generated number enforced by the unique index IGS_EN_SVS_AUTH_PK. The documented unique/business-key candidate is this same primary key; no other single-column unique index is defined, so SEVIS_AUTH_ID is the sole documented uniqueness anchor.

• SEVIS_AUTH_ID — Sequence-generated surrogate identifier and primary key.
• PERSON_ID — Person identifier, validated against HZ_PARTIES.
• SEVIS_AUTHORIZATION_CODE — The SEVIS authorization code (VARCHAR2(30)); the column the user searched for.
• SEVIS_AUTHORIZATION_NO — System-generated number, unique per person.
• START_DT and END_DT — Validity window for the authorization.
• CANCEL_FLAG — Cancel indicator.
• COMMENTS — Free-text notes.
• Standard Who / Request columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE for audit and concurrent-program traceability.

The non-unique index IGS_EN_SVS_AUTH_N1 covers PERSON_ID, SEVIS_AUTHORIZATION_CODE, and START_DT, which reflects the typical access pattern of locating a person’s authorization by code and effective date.

Common Use Cases and Queries

Typical usage centers on verifying an individual’s SEVIS authorization status, reporting active authorizations, and identifying lapsed or cancelled records. The standard extraction pattern is a direct select on the documented column list:

• Active authorizations by person: filter on PERSON_ID where CANCEL_FLAG is null and SYSDATE falls between START_DT and END_DT.
• Authorization lookup by code: query on SEVIS_AUTHORIZATION_CODE, supported by IGS_EN_SVS_AUTH_N1.
• Recency reporting: order by START_DT DESC to surface the latest authorization per person.
• Audit and reconciliation: use REQUEST_ID and the standard Who columns to trace which concurrent program created or last modified a record.

The documented query text supports these scenarios directly across both 12.1.1 and 12.2.2, as the table structure is stable in the ETRM reference.

Related Objects

IGS.IGS_EN_SVS_AUTH has no outgoing foreign key apart from PERSON_ID, which references HZ_PARTIES (the Trading Community Architecture party registry), making the person the principal external anchor. Two child tables reference it:

• IGS_EN_SVS_AUTH_CAL — joined on SEVIS_AUTH_ID; holds authorization calendar/validity details.
• IGS_SV_PRGMS_INFO — joined on SEVIS_AUTH_ID; holds SEVIS program information.

In the APPS layer, the object is exposed through the synonym/view IGS_EN_SVS_AUTH. No additional database objects are documented as referenced by this table beyond HZ_PARTIES, so the person table and the two dependent child tables constitute the primary relationship set.