Search Results sv_bio




Overview
Explore in AI Chat

Overview

IGS_SV_EDT_PERS_BLK_V is an Oracle E-Business Suite 12.1.1 / 12.2.2 view owned by the APPS schema and defined in the Student System (IGS) product family. It presents the person-level data required to build the SEVIS batch edit/reprint extract associated with the SV_BIO (biographical) tag. In practice, the view flattens the outer join between the driver table IGS_SV_PERSONS and a derived biographical block, exposing only records that have been marked complete (record_status = 'C') and that carry an approved admission action ('SEND') for a set of related tags.

The name and the embedded TAG_CODE filter indicate the view is one of the building blocks used by SEVIS batch processing to generate the "person block" submission. It is intended for integration and reporting rather than online data entry; consumers are typically concurrent programs or custom reports that assemble flat-file XML extracts for SEVIS.

Underlying Base Objects

The documented view text references the following objects:

  • IGS_SV_PERSONS (alias pers, prs) — the primary SEVIS person header table, supplying batch_id, person_id, sevis_user_id, record_number, pdso_sevis_id, REPRINT_RSN_CODE and REPRINT_REMARKS.
  • IGS_SV_BIO_INFO (alias bio) — biographical detail: first, middle and last name, suffix, birth_date, gender, birth country, citizenship country and commuter flag.
  • IGS_SV_BTCH_SUMMARY (alias bioSumm) — the batch/record status summary that drives the filter logic, providing TAG_CODE and ADM_ACTION_CODE.

The biographical block is supplied by an inline (derived) query that inner-joins IGS_SV_BIO_INFO to IGS_SV_BTCH_SUMMARY and IGS_SV_PERSONS, restricting to TAG_CODE = 'SV_BIO' and ADM_ACTION_CODE = 'SEND'. This block is outer-joined to IGS_SV_PERSONS (pers.person_id = bio_info.person_id (+)). The outer WHERE clause requires record_status = 'C' and the EXISTS predicate admits rows whose batch summary carries one of the tags SV_BIO, SV_US_ADDR, SV_F_ADDR, SV_LEGAL or SV_OTHER with ADM_ACTION_CODE = 'SEND'. Note both joins key on person_id together with batch_id.

The ETRM metadata does not enumerate separate base objects for the view, but the SELECT text documents the three tables above as the effective sources.

Key Columns

  • batch_id, person_id — the composite key linking a person to the SEVIS batch.
  • pdso_sevis_id, sevis_user_id, record_number — SEVIS identifiers carried from IGS_SV_PERSONS.
  • person_number, person_id_long — zero-padded derivations produced by ltrim(to_char(substr(...))) on person_id.
  • REPRINT_REASON, reprint_remarks, other_remarks — reprint handling. When REPRINT_RSN_CODE = '05', reprint_remarks is NULL and the text is redirected to other_remarks via DECODE; otherwise the reverse applies.
  • first_name, last_name, middle_name, suffix, birth_date, gender, birth_cntry_code, ctznship_cntry_code, commuter — biographical attributes from IGS_SV_BIO_INFO.
  • fn_address_line1/2, fn_city, fn_cntry_code, fn_province, fn_postal_code, us_address_line1/2, us_city, us_state, us_postal_code, us_postal_routing_code, drivers_license, drivers_license_state, ssn, tax_id, admission_number, print_form — all exposed as hard-coded NULL placeholders in this view. These columns define the physical layout expected by the downstream extract; populate them from the corresponding address and identity tags (SV_US_ADDR, SV_F_ADDR, SV_LEGAL).

Common Use Cases and Queries

Typical usage is to extract a candidate person block for a specific batch before the SEVIS flat file is assembled. A representative query is:

SELECT person_id, person_number, sevis_user_id,
       first_name, last_name, birth_date, gender,
       REPRINT_REASON, reprint_remarks, other_remarks
  FROM apps.igs_sv_edt_pers_blk_v
 WHERE batch_id = :p_batch_id
   AND person_id = :p_person_id;

Because the address and legal columns are NULL in this view, report designers commonly join it to IGS_SV_BIO_INFO, the address tables and the SV_LEGAL extract to complete the submission record. The view is also useful for verifying which persons will be picked up in a batch — the EXISTS predicate effectively defines the population, so it is a quick way to reconcile counts against IGS_SV_BTCH_SUMMARY. A COUNT grouped by batch_id yields the expected block size for a SEVIS run.