Search Results igsfv_residency_statuses

Overview

IGSFV_RESIDENCY_STATUSES is a read-only view within the Oracle E-Business Suite Student System (product code IGS), a module documented as obsolete in release 12.1.1 and 12.2.2. The view presents residency information for a person (party) as recorded in the institution's student records. Residency classification and status are central to higher-education administration because they frequently drive tuition assessment, fee determination, and statutory reporting obligations. The view consolidates the person's residency evaluation, the residency class and status codes, the associated academic calendar, and standard Who-column audit attributes into a single queryable entity. It exposes a denormalized, reporting-friendly projection of the underlying residency detail records and is intended for use by reporting, integration, and extraction processes rather than for transactional data entry. The view is defined with the WITH READ ONLY clause, which prohibits DML against it. The ETRM metadata notes that the object is not implemented in the target database, so the definition should be treated as a design artifact documenting the intended structure of the entity rather than a guaranteed runtime object in a given environment.

Underlying Base Objects

The view is defined over three base objects joined on person and calendar keys:

• IGS_PE_RES_DTLS_ALL (aliased PRD) — the person residency details table, which supplies evaluation dates, evaluator, comments, residency class and status codes, the calendar type and sequence number, the residency detail identifier, and audit columns.
• HZ_PARTIES (aliased HZP) — the Trading Community Architecture party table, joined on PRD.PERSON_ID = HZP.PARTY_ID to supply the party number exposed as PERSON_NUMBER.
• IGS_CA_INST_ALL (aliased CI) — the calendar instance table, joined on both PRD.CAL_TYPE = CI.CAL_TYPE and PRD.SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER, supplying the calendar start and end dates and the calendar description.

The join between the residency detail and calendar instance uses a two-column composite key (calendar type plus sequence number), which links each residency evaluation to the specific academic calendar instance under which it was assessed. The metadata records no separate documented base objects beyond those appearing in the view text.

Key Columns

• PERSON_NUMBER — the party number of the person, sourced from HZ_PARTIES.
• _LA:RESIDENCY_CLASS — a lookup-meaning column derived from the residency class code via IGS_LOOKUP_VALUES with lookup type PE_RES_CLASS.
• _LA:RESIDENCY_STATUS — a lookup-meaning column derived from the residency status code via IGS_LOOKUP_VALUES with lookup type PE_RES_STATUS.
• CALENDAR_DESCRIPTION — the description of the associated calendar instance, sourced from IGS_CA_INST_ALL.DESCRIPTION. This is the column that corresponds to the user's search term "calendar_description" and provides a human-readable label for the academic period against which residency was evaluated.
• CALENDAR_TYPE and SEQUENCE_NUMBER — the calendar type and instance sequence forming the link to IGS_CA_INST_ALL.
• START_DATE / END_DATE — the effective start and end dates of the calendar instance.
• EVALUATION_DATE, EVALUATOR, COMMENTS — the residency determination details.
• RESIDENT_DETAILS_ID, PERSON_ID — surrogate identifiers for the residency record and the person.
• _DF — a descriptive flexfield reference indicator mapped to IGS_PE_PERS_RESIDENCY_FLEX.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard audit columns.

Common Use Cases and Queries

Typical scenarios include producing a residency roster per academic calendar, auditing evaluations by evaluator or date, and extracting residency data for downstream fee or reporting systems. The calendar description is frequently the reporting label of choice.

Listing residency statuses for a specific calendar description:

SELECT person_number,
       "_LA:RESIDENCY_CLASS"  AS residency_class,
       "_LA:RESIDENCY_STATUS" AS residency_status,
       calendar_description,
       evaluation_date
FROM   igsfv_residency_statuses
WHERE  calendar_description = :calendar_description;

Auditing evaluations within a date range:

SELECT person_number, evaluator, evaluation_date, calendar_description
FROM   igsfv_residency_statuses
WHERE  evaluation_date BETWEEN :from_date AND :to_date
ORDER  BY evaluation_date;

Because the object is obsolete and may not be implemented, alternative sources such as IGS_PE_RES_DTLS_ALL joined directly to HZ_PARTIES and IGS_CA_INST_ALL should be considered where the view is unavailable.