Results for “record_number”

50+ results




Overview
Explore in AI Chat

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

Overview

The IGS_SV_OFF_EMPL_DUM_V view resides in the APPS schema of the Oracle E-Business Suite Student System (IGS) product family and carries a VALID status in both release 12.1.1 and 12.2.2. Its documented purpose is to fetch off-campus employment information for students. It is a SEVIS-related reporting object: the naming convention and the column set (PDSO_SEVIS_ID, SEVIS_USER_ID) indicate that it is used to produce output destined for the Student and Exchange Visitor Information System (SEVIS) batch process for students engaged in off-campus employment.

Functionally the view is a specialized projection over the SEVIS person staging area, joined to batch summary data and filtered to a single extract tag. Because it exposes the effective person identifier, batch identifier, reprint reason, and remarks, it serves as a reporting and troubleshooting surface for the SV_OFF_EMPL batch rather than as a transactional interface. The "_DUM_V" suffix and the use of DECODE to null out a reprint reason code suggest a "dummy" or fixed-layout extract view whose output shape is aligned to a downstream file or interface format.

Underlying Base Objects

The view text documents a two-table join. The primary source is IGS_SV_PERSONS (aliased PERS), the staging table that holds SEVIS person records and their identifiers. The secondary source is IGS_SV_BTCH_SUMMARY (aliased OFFSUMM), the batch summary table that records the administrative action applied to each person within a batch.

The join is defined on both PERSON_ID and BATCH_ID, ensuring that a person record is matched only to its own batch action row, and the result set is restricted by three predicates:

  • PERS.RECORD_STATUS = 'C' — only completed person records are returned.
  • OFFSUMM.TAG_CODE = 'SV_OFF_EMPL' — only the off-campus employment extract tag qualifies.
  • OFFSUMM.ADM_ACTION_CODE = 'SEND' — only rows whose administrative action is a send are included.

The ETRM metadata notes no separately documented base objects for this view in 12.2.2, so the view text itself is the authoritative reference for its two source tables. No owner is separately recorded beyond APPS.

Key Columns

  • BATCH_ID — Identifier of the SEVIS batch to which the person record belongs; carried from IGS_SV_PERSONS.
  • PDSO_SEVIS_ID — The Designated School Official SEVIS identifier associated with the person record. This is the column most commonly searched for and is exposed directly from IGS_SV_PERSONS.
  • PERSON_ID — The internal person identifier.
  • SEVIS_USER_ID — The SEVIS user identifier for the person.
  • PERSON_NUMBER — A zero-padded, left-trimmed character rendering of the first ten characters of PERSON_ID, produced via LTRIM(TO_CHAR(SUBSTR(...),'0000000000')).
  • RECORD_NUMBER — The person record sequence number.
  • PERSON_ID_LONG — A longer zero-padded rendering derived from the first fourteen characters of PERSON_ID.
  • REPRINT_REASON — The reprint reason code, with a DECODE that substitutes NULL when the code equals '11'.
  • REPRINT_REMARKS — Free-text remarks associated with a reprint.

Common Use Cases and Queries

Typical usage includes auditing which completed person records were queued for the off-campus employment extract, validating SEVIS person identifiers prior to submission, and diagnosing reprint handling. A representative query is:

SELECT batch_id, pdso_sevis_id, person_id, person_number, record_number, reprint_reason FROM apps.igs_sv_off_empl_dum_v WHERE pdso_sevis_id = :sevis_id;

Analysts frequently filter on BATCH_ID to inspect a specific run, or list REPRINT_REASON to identify records flagged for reissue. Because the view already enforces RECORD_STATUS = 'C' and ADM_ACTION_CODE = 'SEND', callers need not repeat those predicates, though adding them explicitly is harmless and aids readability. Oracle recommends read-only access through the APPS schema, as the view underlies SEVIS interface processing and should not be treated as an updatable object.