Results for “igs_sv_prgm_dum_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_SV_PRGM_DUM_V is a seeded Oracle E-Business Suite database view owned by the APPS schema and belonging to the IGS (Student System) product family. Its documented purpose is to fetch program information for a student, and in practice it functions as a staging or extraction view for SEVIS-related batch processing. The name segment "DUM" implies a dummy or intermediate construct, and the view text confirms this interpretation: rather than exposing a wide program catalog, it joins the SEVIS person record set to a batch summary table filtered to a single tag and action, effectively producing the roster of student records queued for transmission under the SV_PRGMS tag.

The view is a read-only, single-statement definition with no aggregation and no PL/SQL components. Because it presents a flat, denormalized result set from two base tables, it is well suited to concurrent program source queries, XML/SEVIS file generation, and ad hoc operational reporting. Status is documented as VALID in the ETRM repository for 12.2.2, and the object is shipped in the standard APPS schema rather than in any custom schema. It carries no documented materialized view, synonym, or index of its own; performance therefore depends entirely on the access paths available on the two underlying tables.

In the broader Student System architecture, SEVIS reporting is batch-driven. Government-transmission records are accumulated, tagged, and released in batches, and views such as this one provide the batch-scoped slice of person data needed by downstream processes. IGS_SV_PRGM_DUM_V should be regarded as a supporting internal object, not an end-user-facing inquiry view.

Underlying Base Objects

The ETRM structure metadata records no documented referenced base objects, but the view text itself is authoritative and names exactly two sources, both in the APPS schema:

• IGS_SV_PERSONS — aliased PERS; the driving table, supplying the SEVIS person attributes, batch identifier, record status, record number, and reprint information.
• IGS_SV_BTCH_SUMMARY — aliased PRGMSUMM; the batch summary table, supplying the batch-to-person linkage and the control attributes (TAG_CODE and ADM_ACTION_CODE) that qualify a record for inclusion.

The join is an equi-join on two columns simultaneously: PERSON_ID and BATCH_ID must both match between the two tables. This composite key enforces that a person appears in the view only for the specific batch in which that person is represented. Three additional predicates restrict the result set: PERS.RECORD_STATUS must equal 'C' (completed or confirmed, in the SEVIS record-status domain), PRGMSUMM.TAG_CODE must equal 'SV_PRGMS', and PRGMSUMM.ADM_ACTION_CODE must equal 'SEND'. The net effect is an inner join producing only those person rows that belong to a program-tagged batch whose administrative action is transmission. Any person row orphaned from its batch summary, or any batch row whose tag or action differs, is silently excluded.

Key Columns

The view exposes nine columns. Several are passed through unchanged, while two apply transformations that are significant for downstream consumers.

• BATCH_ID — the SEVIS batch identifier; the primary grouping key for output files.
• PERSON_ID — the internal person identifier, passed through from IGS_SV_PERSONS.
• PDSO_SEVIS_ID — the designated principal designated school official's SEVIS identifier, carried on the person record.
• SEVIS_USER_ID — the SEVIS user identifier associated with the person record.
• PERSON_NUMBER — derived, not stored. It applies LTRIM(TO_CHAR(SUBSTR(PERSON_ID,1,10),'0000000000')) to format the first ten characters of the person identifier as a ten-digit zero-padded string, then strips the leading blanks introduced by the TO_CHAR numeric format. This is the SEVIS-facing person reference.
• RECORD_NUMBER — the sequence or record number within the batch.
• PERSON_ID_LONG — derived identically to PERSON_NUMBER but over fourteen characters with a fourteen-digit format mask, yielding a wider zero-padded person identifier.
• REPRINT_REASON — a DECODE that returns NULL when REPRINT_RSN_CODE equals '11' and otherwise returns the code verbatim. Reason code 11 is therefore suppressed in this view's output.
• REPRINT_REMARKS — free-text remarks associated with a reprint request, passed through unchanged.

Common Use Cases and Queries

Typical consumption patterns are batch diagnostics, pre-transmission validation, and volume reconciliation against the batch summary. Because the view already constrains the tag and action, callers only need to further filter by batch or person.

• Listing all program records in a batch: SELECT * FROM apps.igs_sv_prgm_dum_v WHERE batch_id = :p_batch_id ORDER BY record_number;
• Retrieving a single student's program record: SELECT * FROM apps.igs_sv_prgm_dum_v WHERE person_id = :p_person_id;
• Counting rows per batch for reconciliation: SELECT batch_id, COUNT(*) FROM apps.igs_sv_prgm_dum_v GROUP BY batch_id;
• Isolating reprint activity: SELECT person_id, person_number, reprint_reason FROM apps.igs_sv_prgm_dum_v WHERE reprint_reason IS NOT NULL;

Because REPRINT_REASON is nulled for code 11, queries that must distinguish suppressed reason codes have to read IGS_SV_PERSONS directly rather than this view.