Search Results sv_opt_empl

Overview

IGS_SV_OPT_EMPL_DUM_V is a reporting and integration view in the Oracle E-Business Suite Student Systems (also referenced in the Oracle Student System / SEVIS context). The name follows the IGS_SV naming convention, where "SV" denotes SEVIS (Student and Exchange Visitor Information System) processing, and the suffix "_DUM_V" identifies it as a staging or "dump" view used to extract a flat, denormalized result set for downstream consumption. In Oracle EBS 12.1.1 and 12.2.2, this view belongs to the APPS schema and is exposed as APPS.IGS_SV_OPT_EMPL_DUM_V.

The view consolidates person-level SEVIS data with batch-level summary information for the Optional Practical Training (OPT) employment reporting category, identified by the tag code SV_OPT_EMPL and the administrative action code SEND. It presents rows suitable for extraction by a concurrent program, interface file, or external reporting layer. Because it joins completed person records against batch summary records awaiting a send action, the view functions as a functional driver for communications transmitted on behalf of OPT employment events.

Underlying Base Objects

The view is defined over two documented base tables:

• IGS_SV_PERSONS — the source of person-level SEVIS records, including batch identifier, person identifier, SEVIS identifiers, record number, reprint information, and record status.
• IGS_SV_BTCH_SUMMARY — the batch summary table supplying the tag code and administrative action code used to filter candidate records.

The join is performed on both PERSON_ID and BATCH_ID, ensuring person records are matched to the correct batch summary row. The WHERE clause restricts output to person records with RECORD_STATUS = 'C' (completed) and batch summary rows with TAG_CODE = 'SV_OPT_EMPL' and ADM_ACTION_CODE = 'SEND'. No referenced base objects were separately documented in the ETRM metadata beyond these two tables.

Key Columns

• BATCH_ID — identifies the SEVIS batch to which the record belongs; a key join column.
• PDSO_SEVIS_ID — the designated school official's SEVIS identifier associated with the record.
• PERSON_ID — the internal person identifier used across Student Systems.
• SEVIS_USER_ID — the SEVIS user identifier linked to the person.
• PERSON_NUMBER — derived via SUBSTR and TO_CHAR/LTRIM formatting of PERSON_ID, producing a zero-padded numeric string for the first ten characters.
• RECORD_NUMBER — the SEVIS record number used for tracking the transmitted record.
• PERSON_ID_LONG — a fourteen-character formatted derivative of PERSON_ID, providing an extended person reference.
• REPRINT_REASON — the reprint reason code, with code '11' suppressed to NULL through a DECODE expression, meaning reason 11 is not surfaced as an explicit value.
• REPRINT_REMARKS — free-text remarks associated with a reprint request.

Common Use Cases and Queries

Typical usage centers on extracting OPT employment records for SEVIS transmission or reconciliation. A common pattern selects all columns for a given batch:

• Listing pending OPT employment records: SELECT * FROM apps.igs_sv_opt_empl_dum_v WHERE batch_id = :batch_id;
• Retrieving person identifiers and SEVIS IDs for validation: SELECT person_id, person_number, sevis_user_id FROM apps.igs_sv_opt_empl_dum_v;
• Identifying reprint cases: SELECT person_id, reprint_reason, reprint_remarks FROM apps.igs_sv_opt_empl_dum_v WHERE reprint_remarks IS NOT NULL;

Because the view already enforces the RECORD_STATUS, TAG_CODE, and ADM_ACTION_CODE filters, consumers need not reapply them, making it suitable as a read-only interface source for concurrent programs and extracts.