Search Results residency_status_desc




Overview
Explore in AI Chat

Overview

IGS_PE_RES_DTLS_V is a view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. It presents person-level residency determination data — the record of how a learner's residency classification and residency status were evaluated for a given academic calendar. In the ETRM 12.2.2 documentation this object carries a VALID status and is defined over the residency details entity IGS_PE_RES_DTLS plus supporting lookup, person, and calendar-instance sources.

The view exists to spare reporting and integration developers from hand-coding the lookup joins that translate the stored code values RESIDENCY_CLASS_CD and RESIDENCY_STATUS_CD into their descriptive meanings. It also denormalizes the person number from IGS_PE_PERSON_BASE_V and the calendar window (start/end dates and description) from IGS_CA_INST_ALL. The result is a single, human-readable row per residency detail record — useful for regulatory residency reporting, admissions review, and downstream integrations that need the decoded status rather than the raw code.

Underlying Base Objects

The view is defined over five sources, joined in its defining SQL:

Because the lookup and calendar joins are inner joins, a residency detail row will only appear when matching lookup values and a matching calendar instance exist.

Key Columns

  • PERSON_ID / PERSON_NUMBER — the learner identity and human-facing person number.
  • RESIDENCY_CLASS / RESIDENCY_CLASS_DESC — the class code from PE_RES_CLASS and its decoded meaning.
  • RESIDENCY_STATUS / RESIDENCY_STATUS_DESC — the status code from PE_RES_STATUS and its decoded meaning; this is the pair most relevant to a residency_status search.
  • EVALUATION_DATE and EVALUATOR — when and by whom the determination was made.
  • CAL_TYPE, SEQUENCE_NUMBER, START_DT, END_DT, CALENDAR_DESC — the academic calendar context for the determination.
  • COMMENTS, ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE20 — free-text notes and DFF segments.
  • Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical uses include producing residency status rosters for a person, auditing evaluator activity, and extracting decoded status values into a data warehouse or third-party system without re-implementing lookup logic.

SELECT person_number,
       residency_class_desc,
       residency_status_desc,
       evaluation_date,
       calendar_desc
  FROM igs_pe_res_dtls_v
 WHERE person_id = :p_person_id
 ORDER BY evaluation_date DESC;

SELECT residency_status_desc,
       COUNT(*) AS num_persons
  FROM igs_pe_res_dtls_v
 WHERE start_dt >= :p_from_date
   AND end_dt   <= :p_to_date
 GROUP BY residency_status_desc;

Note that searches on "residency_status" may match either the stored code column RESIDENCY_STATUS or the decoded column RESIDENCY_STATUS_DESC; filtering on the code is more efficient, while the description is more readable in output.