Search Results sevis_auth_id




Overview
Explore in AI Chat

Overview

IGS_SV_UPD_DROP_BLW_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, associated with the SEVIS (Student and Exchange Visitor Information System) compliance functionality delivered through the IGS (International Graduate/Student) product family. The view presents a comparison-oriented result set that surfaces update or drop actions against prior SEVIS program authorizations. Its name, containing "UPD_DROP_BLW," indicates that it exposes authorization records that have been updated or dropped relative to a baseline (previous) authorization record tracked by SEVIS authorization identifier.

The purpose of the view is to allow downstream batch processing, reporting, and SEVIS reporting extracts to identify the changed authorization context for a person, presenting both the newer ("new") authorization values and the older ("old") authorization values side by side. It is not a base transactional table; it is a read-only query object intended for reporting and data selection.

Underlying Base Objects

The view is defined exclusively over a single base table, IGS_SV_PRGMS_INFO, which stores SEVIS program and authorization information. The view references this table twice using self-joins, aliased as prgms_new and prgms_old, reflecting a before/after comparison across batch records.

The join logic is documented as follows:

  • prgms_new.prgm_action_type = 'DB' — restricts the new side to drop/batch action records.
  • prgms_new.auth_action_code = 'U' — restricts to update authorization actions.
  • prgms_new.person_id = prgms_old.person_id — matches the same person.
  • prgms_old.prgm_action_type = 'DB' — the prior record is also a drop/batch action.
  • prgms_old.sevis_auth_id = prgms_new.sevis_auth_id — matches on the SEVIS authorization identifier, which is the key correlation the user searched for.
  • prgms_new.batch_id <> prgms_old.batch_id — ensures the new and old records are different batch instances.
  • A correlated subquery selects the maximum prior batch_id that is less than the current maximum batch for the same person and SEVIS authorization, thereby identifying the immediately preceding authorization record (the baseline).

No additional base objects beyond IGS_SV_PRGMS_INFO are referenced in the documented view text.

Key Columns

The view exposes the following columns:

  • batch_id — batch identifier of the new authorization record.
  • person_id — identifier of the person (student/exchange visitor) associated with the authorization.
  • print_form — indicator of whether a form should be printed.
  • new_auth_reason — authorization reason on the newer record (authorization_reason from prgms_new).
  • new_auth_start_date / new_auth_end_date — start and end dates of the newer authorization.
  • dbf_remarks — remarks captured on the newer record (remarks).
  • auth_reason, auth_start_date, auth_end_date — the corresponding prior authorization values from prgms_old.
  • auth_action_code — the authorization action code from the newer record, used to classify the change.

The sevis_auth_id column, although central to the join predicate, is not projected in the SELECT list; it is used purely as the correlation key linking old and new records.

Common Use Cases and Queries

Typical usage involves retrieving the change history for a person's SEVIS authorization to produce before/after reporting, feed downstream batch extract processes, or support compliance auditing. A representative query follows:

  • SELECT person_id, batch_id, new_auth_reason, new_auth_start_date, new_auth_end_date, auth_reason, auth_start_date, auth_end_date, auth_action_code FROM apps.igs_sv_upd_drop_blw_v WHERE person_id = :person_id;
  • Reports comparing prior and current authorization dates to detect date shifts resulting from update actions.
  • Batch-driven reconciliation of SEVIS authorization changes prior to submission.

Because sevis_auth_id is the correlation key rather than an output column, callers needing that identifier directly must join back to IGS_SV_PRGMS_INFO on person_id and batch_id.