Results for “igs_sv_edt_pers_blk_v”

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

Overview

IGS_SV_EDT_PERS_BLK_V is a seeded Oracle E-Business Suite view owned by the APPS schema and delivered with the IGS (Student System) product. Its documented purpose is to supply the data required to build the XML block that describes student personal information changes, specifically within the SEVIS reporting flow handled by the Student Visits/SEVIS functionality of Oracle Student System. In release 12.1.1 and 12.2.2 the object is reported with a status of VALID, confirming that it is a supported, active dictionary object rather than a deprecated or invalidated artifact.

The view functions as a reporting and integration layer rather than a transactional entity. It flattens data originating from the student personal-information batch tables into a canonical shape suitable for downstream XML generation and government-facing electronic reporting. Because it exposes a fixed, alias-controlled set of columns—including several literal NULL placeholders—it acts as a positional contract: the consuming XML template or concurrent program expects a stable column list, and the view enforces that stability even where the underlying business data is not yet available.

Underlying Base Objects

The ETRM metadata documents no referenced base objects for this view, but the embedded view text identifies them explicitly. IGS_SV_EDT_PERS_BLK_V is defined over IGS_SV_PERSONS (aliased PERS) joined to an inline subquery aliased BIO_INFO. That inline subquery itself joins three objects: IGS_SV_BIO_INFO (aliased BIO), IGS_SV_BTCH_SUMMARY (aliased BIOSUMM), and a second reference to IGS_SV_PERSONS (aliased PRS).

The driving filter on the outer query requires PERS.RECORD_STATUS = 'C', meaning only completed person records qualify. The existence of a matching batch summary row is also enforced through an EXISTS clause against IGS_SV_BTCH_SUMMARY keyed on both PERSON_ID and BATCH_ID. Within the BIO_INFO subquery, additional predicates restrict rows to PRS.RECORD_STATUS = 'C', require batch and person identifiers to align across all three tables, and constrain BIOSUMM.TAG_CODE = 'SV_BIO' with BIOSUMM.ADM_ACTION_CODE = 'SEND'. Collectively, these conditions select only those personal-information change records that are both complete and flagged for transmission.

Key Columns

The view exposes batch and identity keys first: BATCH_ID, PDSO_SEVIS_ID, PERSON_ID, SEVIS_USER_ID, and RECORD_NUMBER. Two derived numbering columns are produced by reformatting PERSON_ID through SUBSTR and TO_CHAR with zero padding—PERSON_NUMBER (first 10 characters) and PERSON_ID_LONG (first 14 characters), each passed through LTRIM to strip leading blanks.

The reprint attributes deserve attention because of their conditional logic. REPRINT_REASON returns REPRINT_RSN_CODE directly. REPRINT_REMARKS is returned only when the reason code is not '05', while OTHER_REMARKS returns the remark text only when the code equals '05'. This split allows the XML template to route a free-text explanation into the correct SEVIS element depending on the reason selected.

Biographic attributes are sourced from the BIO_INFO subquery: FIRST_NAME, LAST_NAME, MIDDLE_NAME, BIRTH_DATE, GENDER, BIRTH_CNTRY_CODE, CTZNSHIP_CNTRY_CODE, COMMUTER, and SUFFIX. A substantial group of columns—PRINT_FORM, both foreign and U.S. address blocks, DRIVERS_LICENSE, SSN, TAX_ID, and ADMISSION_NUMBER—is returned as literal NULL. These are structural placeholders that preserve the expected column count and ordering for the XML block without carrying data in this view.

Common Use Cases and Queries

The primary use case is generating the personal-information XML block for a given student batch. A typical query selects the full column list filtered by batch and person, for example:

• SELECT person_id, person_number, 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;
• SELECT batch_id, COUNT(*) FROM apps.igs_sv_edt_pers_blk_v GROUP BY batch_id; to confirm how many complete personal records are eligible for transmission in each batch.
• SELECT person_number, reprint_reason, other_remarks FROM apps.igs_sv_edt_pers_blk_v WHERE reprint_reason = '05'; to isolate records whose reprint reason requires free-text handling.

Because the view filters on completed records and the 'SV_BIO'/'SEND' summary combination, it should be treated as an extract-ready source rather than a general-purpose person query. Troubleshooting typically involves verifying that IGS_SV_PERSONS.RECORD_STATUS is 'C' and that a corresponding IGS_SV_BTCH_SUMMARY row carries TAG_CODE 'SV_BIO' and ADM_ACTION_CODE 'SEND'.