Search Results next_session_start_date




Overview
Explore in AI Chat

Overview

The view IGS_SV_STDNT_REG_BLK_V belongs to the product family IGS – Student System, which is documented in Oracle E-Business Suite (12.1.1 and 12.2.2) as Obsolete. It was originally designed to return a single row per student registration record, structured specifically to feed an XML block describing student registration information for the SEVIS (Student and Exchange Visitor Information System) reporting process. The view supplies the registration data required by the XML block consumed downstream by SEVIS-related integration logic.

The view surfaces columns used in US and foreign address blocks, passport data, visa data, entry/admission data, and person identification attributes. Notably, the majority of those columns are declared with hard-coded NULL values in the view text — for example NULL US_ADDRESS_LINE1, NULL PSPT_EXPIRY_DATE, NULL VISA_EXPIRY_DATE, and NULL PORT_OF_ENTRY. This confirms that the view was implemented as a structural shell: its purpose was to guarantee that the XML block has a consistent set of element names and data types, even though many elements were not yet or no longer populated by this object.

Within Oracle EBS, such views operate as an interface layer between the base student registration tables and the document-generation / XML-publishing tier. They are also commonly queried directly in Oracle Reports, BI Publisher data templates, or ad-hoc SQL used by institutional reporting teams extracting registration data.

Underlying Base Objects

According to the documented metadata, no referenced base objects are formally documented for this view. However, the view definition is derived from two underlying base tables of the IGS schema:

  • IGS_SV_PERSONS — aliased PRS. Provides batch, person, session, print, SEVIS user, record number, reprint reason, and record status data.
  • IGS_SV_BIO_INFO — aliased BIO. Provides biographical attributes such as COMMUTER.

Supporting objects referenced in the filter predicates include IGS_SV_BATCHES and IGS_SV_BTCH_SUMMARY. The join is performed on PERSON_ID between IGS_SV_PERSONS and IGS_SV_BIO_INFO, with the biography row restricted to the latest batch (maximum BATCH_ID) whose batch status is not 'E'. Only records with PRS.RECORD_STATUS = 'C' are returned.

Key Columns

  • BATCH_ID — Identifies the SEVIS processing batch to which the row belongs.
  • PERSON_ID — Key identifier for the student/person record; also the join key to IGS_SV_BIO_INFO.
  • PERSON_NUMBER / PERSON_ID_LONG — Formatted string representations derived from PERSON_ID using LTRIM(TO_CHAR(...)).
  • PDSO_SEVIS_ID — The SEVIS identifier of the Principal Designated School Official on the record.
  • CURRENT_SESSION_END_DATE and NEXT_SESSION_START_DATE — Session boundary dates used in SEVIS reporting predicates.
  • COMMUTER — Biographical indicator of whether the student commutes.
  • PSPT_EXPIRY_DATE — Passport expiry date element. In this view it is defined as NULL; the surrounding passport columns (PSPT_NUMBER, PSPT_ISSUING_CNTR) are likewise NULL placeholders.
  • VISA_NUMBER, VISA_ISSUING_CNTR, VISA_EXPIRY_DATE — Visa-related XML elements, also declared NULL.
  • US_ADDRESS_LINE1/2, US_CITY, US_STATE, US_POSTAL_CODE and the FN_ foreign address equivalents — Address block elements, all NULL.
  • REPRINT_REASON — Copies PRS.REPRINT_RSN_CODE, except when the code is '11', in which case NULL is returned.

Common Use Cases and Queries

The primary use case was generating the SEVIS registration XML block. A representative query to retrieve registration block data for a person is:

  • SELECT person_id, pdso_sevis_id, current_session_end_date, next_session_start_date, commuter FROM igs_sv_stdnt_reg_blk_v WHERE person_id = :p_person_id;
  • SELECT * FROM igs_sv_stdnt_reg_blk_v WHERE batch_id = :p_batch_id AND record_status IS NULL;
  • Filtering on session dates: ... WHERE current_session_end_date IS NOT NULL AND next_session_start_date IS NOT NULL

Because the view is marked obsolete and is not implemented in the reference database, DBAs should confirm availability before use. Where present, queries against it should be treated as read-only and limited to XML extract or migration reporting; the populated columns are the safest basis for logic, while the NULL-declared columns (including PSPT_EXPIRY_DATE) should not be relied upon for data retrieval.