Search Results igs_pe_res_dtls_u1

Overview

The IGS_PE_RES_DTLS_ALL table is a core data repository within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (OSM) product, owned by the IGS schema. Its primary function is to store historical and current residency details for students (persons). This table acts as a system of record for residency evaluations, tracking a student's residency classification, the status of that classification, the date of evaluation, and the evaluator. It is essential for processes related to tuition fee calculations, enrollment eligibility, and regulatory reporting, where residency status is a critical determinant.

Key Information Stored

The table's structure includes standard system columns and specific business attributes. The key business columns are PERSON_ID, which links to the person/student, and EVALUATION_DATE, which records when the residency was assessed. Notably, the RESIDENCY_CLASS_ID and RESIDENCY_STATUS_ID columns are documented as obsolete in the provided metadata, indicating a likely migration to a code-based lookup model. The current residency classification is instead captured by the RESIDENCY_CLASS_CD, which participates in the unique key constraint IGS_PE_RES_DTLS_U2 alongside PERSON_ID, CAL_TYPE, and SEQUENCE_NUMBER. Other significant columns include EVALUATOR (the user who performed the assessment), COMMENTS, and a suite of Descriptive Flexfield (DFF) columns (ATTRIBUTE_CATEGORY through ATTRIBUTE10) for institution-specific extensions. The START_DT and END_DT columns are also marked as no longer used.

Common Use Cases and Queries

This table is central to reporting and operational queries concerning student residency. Common use cases include generating lists of students with a specific residency classification for billing purposes, auditing changes in residency status over time, and verifying the evaluator and date of the last residency determination. A typical query to find a student's current residency details would join on PERSON_ID and order by EVALUATION_DATE descending. For example:

• SELECT person_id, residency_class_cd, evaluation_date, evaluator FROM igs.igs_pe_res_dtls_all WHERE person_id = :person_id ORDER BY evaluation_date DESC;

Given the obsolete nature of the ID columns, integrations and custom reports should prioritize using RESIDENCY_CLASS_CD. The unique constraint on PERSON_ID, RESIDENCY_CLASS_CD, CAL_TYPE, and SEQUENCE_NUMBER is critical for understanding how multiple residency records for a single student are managed and sequenced.

Related Objects

Based on the provided metadata, the table's relationships are primarily defined through its constraints and key columns. The RESIDENT_DETAILS_ID column is the primary key, enforced by the unique index IGS_PE_RES_DTLS_U1. The PERSON_ID column is a foreign key referencing the person master table (likely IGS_PE_PERSON or a similar base table) to link residency details to a specific student. The RESIDENCY_CLASS_CD, part of the composite unique key, would reference a lookup table for valid residency classification codes (such as IGS_LOOKUPS_VALUES with a specific lookup type). The table's dependencies are not fully listed, but it is a central entity likely referenced by various views, forms, and APIs within the Student Management module that process residency information.