Search Results sv_status




Overview
Explore in AI Chat

Overview

IGS_SV_STATUS_DUM_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 applications schema (APPS) that supports the Student and Visitor Exchange Information System (SEVIS) integration. Its name and column set identify it as a status extract used by the SEVIS batch processing framework, specifically the "SV_STATUS" tag. The view assembles person-level SEVIS identifiers alongside batch context and reprint information, then filters the population to records that are candidates for a status transmission.

In practice, the view serves as a curated presentation layer over SEVIS person data. Rather than exposing every person record, it restricts output to records that are logically complete (RECORD_STATUS = 'C') and that either qualify for a reprint under reason code 11 or have a matching batch summary entry marked for sending. This makes the view suitable for downstream extracts, concurrent program inputs, and diagnostic queries used by institutions that must report status changes to the SEVIS system.

Underlying Base Objects

The view is defined over two documented base objects, both referenced directly in its text:

  • IGS_SV_PERSONS — the driving table. It supplies the batch, person, SEVIS user, record number, and reprint attributes. The alias PERS is used throughout the SELECT list and WHERE clause.
  • IGS_SV_BTCH_SUMMARY — referenced in a correlated EXISTS subquery. It provides the batch-to-person status summary that determines whether a record is flagged for transmission.

The relationship is established on two join keys: PERSON_ID and BATCH_ID. The subquery further constrains the summary rows to TAG_CODE = 'SV_STATUS' and ADM_ACTION_CODE = 'SEND'. Only when such a summary row exists — or when the reprint reason equals 11 — does the person record survive the filter. No additional documented base objects are associated with this view.

Key Columns

The projection is deliberately narrow and oriented toward SEVIS submission:

  • BATCH_ID — the batch to which the person record belongs; also a join key to the batch summary.
  • PDSO_SEVIS_ID — the principal designated school official SEVIS identifier associated with the record.
  • PERSON_ID — the internal person identifier, used for joins and correlation.
  • SEVIS_USER_ID — the SEVIS user identifier carried on the person record.
  • PERSON_NUMBER — a zero-padded character rendering of the first ten characters of PERSON_ID, produced with LTRIM(TO_CHAR(..., '0000000000')).
  • RECORD_NUMBER — the record sequence number from the person record.
  • PERSON_ID_LONG — a wider zero-padded rendering, built from the first fourteen characters of PERSON_ID.
  • REPRINT_REASON — the reprint reason code, except that code 11 is decoded to NULL.
  • REPRINT_REMARKS — free-text remarks accompanying a reprint.

Common Use Cases and Queries

The typical use is to enumerate SEVIS person records awaiting status transmission for a given batch, or to isolate reprint cases. A batch-scoped extract resembles:

  • SELECT person_id, person_number, pdso_sevis_id, sevis_user_id FROM apps.igs_sv_status_dum_v WHERE batch_id = :batch_id;
  • SELECT person_id, reprint_reason, reprint_remarks FROM apps.igs_sv_status_dum_v WHERE reprint_reason IS NOT NULL;
  • SELECT batch_id, COUNT(*) FROM apps.igs_sv_status_dum_v GROUP BY batch_id;

Because the view already applies the RECORD_STATUS and batch-summary filters, callers do not need to re-implement them. Access should be granted through the APPS schema in accordance with site security conventions, and results should be validated against the underlying IGS_SV_PERSONS and IGS_SV_BTCH_SUMMARY rows where reconciliation is required.