Search Results sv_prgms




Overview
Explore in AI Chat

Overview

IGS_SV_PRGM_DUM_V is an Oracle E-Business Suite view owned by the APPS schema, part of the Student Systems (IGS) product family that supports the Student and Exchange Visitor Information System (SEVIS) integration. The view presents a consolidated, report-ready projection of program-related SEVIS records that have been marked complete and are queued for transmission to the SEVIS system. It joins person-level SEVIS data with batch-level control data so that downstream extracts, concurrent programs, and reports can retrieve the mailing-relevant attributes for a single batch/person combination without re-implementing the filtering logic.

The view is effectively a "dumb" (data-only, non-maintained) view that exposes a fixed set of derived columns. Its principal role in EBS reporting and integration is to feed SEVIS batch generation processes and diagnostic reports, particularly for events tagged as program actions. Because it filters on RECORD_STATUS = 'C' and ADM_ACTION_CODE = 'SEND', it exposes only records that are ready for outgoing submission and have been validated to a complete state.

Underlying Base Objects

The view is defined over exactly two base tables, both in the IGS schema:

  • IGS_SV_PERSONS PERS — the person-level SEVIS table holding identifiers, SEVIS user references, record numbers, and reprint information.
  • IGS_SV_BTCH_SUMMARY PRGMSUMM — the batch summary table holding the person's batch membership, tag code, and admission action code.

ETRM documentation for this view lists referenced base objects as "none documented," meaning the object is not formally registered as a dependency in the ETRM metadata repository. Nonetheless, the shipped view text unambiguously references the two tables above. The join is on both PERSON_ID and BATCH_ID, so a row is produced only when a person appears in the batch summary with a matching batch.

Key Columns

  • BATCH_ID — the SEVIS batch identifier; used to group records and to correlate with batch-level processing.
  • PDSO_SEVIS_ID — the SEVIS identifier of the principal designated school official associated with the record.
  • PERSON_ID — the internal person identifier from IGS_SV_PERSONS.
  • SEVIS_USER_ID — the SEVIS user identifier for the record.
  • PERSON_NUMBER — a ten-character zero-padded derivation of the first ten digits of PERSON_ID, produced with LTRIM/TO_CHAR/SUBSTR for fixed-width formatting.
  • RECORD_NUMBER — the SEVIS record number for the person/batch combination.
  • PERSON_ID_LONG — a fourteen-character zero-padded derivation of PERSON_ID, used where a longer formatted identifier is required.
  • REPRINT_REASON — the reprint reason code, with code '11' explicitly mapped to NULL via DECODE, suppressing that value in output.
  • REPRINT_REMARKS — free-text remarks associated with a reprint.

Common Use Cases and Queries

Typical usage is batch extraction and reprint reporting. Example query returning all records for a batch:

SELECT person_number, record_number, pdso_sevis_id, reprint_reason, reprint_remarks FROM apps.igs_sv_prgm_dum_v WHERE batch_id = :p_batch_id ORDER BY person_number;

A reprint-focused query that returns only rows carrying a reprint reason:

SELECT batch_id, person_id, sevis_user_id, reprint_reason FROM apps.igs_sv_prgm_dum_v WHERE reprint_reason IS NOT NULL;

Because the view already restricts to complete records with a SEND admission action and the SV_PRGMS tag, callers should not add redundant filters except for batch or person scoping. Performance is governed by the join between IGS_SV_PERSONS and IGS_SV_BTCH_SUMMARY; indexing on (PERSON_ID, BATCH_ID) and on (TAG_CODE, ADM_ACTION_CODE) is recommended for large batches.