Search Results igs_pe_res_dtls_h

Overview

The IGS_PE_RES_DTLS_H table is a historical tracking table within the Oracle E-Business Suite (EBS) Student System (IGS). Its primary role is to maintain a historical record of changes made to a person's residency details. This type of table is a standard architectural pattern in EBS for auditing and preserving the state of transactional data at specific points in time, allowing for point-in-time reporting and compliance. The provided ETRM metadata explicitly states this table is "No longer used," indicating it is likely a legacy object retained for historical data reference but not actively written to by current application logic in versions 12.1.1 and 12.2.2. Its existence is critical for understanding the complete data lifecycle of student residency information.

Key Information Stored

The table's structure is designed to capture snapshots of residency data linked to a person. The primary key uniquely identifies each historical record and consists of three columns: PERSON_ID, which links to the individual; RESIDENCY_CLASS_CD, which specifies the residency classification code; and HISTORY_START_DATE, which marks the effective date when that particular historical snapshot became valid. While the full column list is not detailed in the excerpt, a historical table typically includes all columns from its corresponding transactional table (IGS_PE_RES_DTLS_ALL) plus audit columns such as HISTORY_END_DATE, CREATION_DATE, and CREATED_BY. The data stored represents past states of a person's residency classification, which may have been updated due to administrative corrections, changes in legal status, or data migration events.

Common Use Cases and Queries

Given its status as a historical table, direct transactional use is deprecated. Its primary use case is analytical and forensic: reconstructing a person's residency status history for auditing purposes or generating historical reports. A common query pattern involves joining the historical table with the main transactional table to get a complete timeline. For example, to see all historical records for a specific person, one might use:

• SELECT * FROM igs.igs_pe_res_dtls_h WHERE person_id = <ID> ORDER BY history_start_date;

Another critical pattern is a point-in-time query to determine what the residency data was on a specific past date:

• SELECT * FROM igs.igs_pe_res_dtls_h WHERE person_id = <ID> AND <target_date> BETWEEN history_start_date AND NVL(history_end_date, SYSDATE);

Development and support staff reference this table when troubleshooting data discrepancies or validating the impact of historical data fixes.

Related Objects

The table maintains a direct foreign key relationship with the primary transactional table, IGS_PE_RES_DTLS_ALL. This relationship enforces data integrity, ensuring that every historical record corresponds to a valid current record. The foreign key is defined on two columns:

• IGS_PE_RES_DTLS_H.PERSON_ID references IGS_PE_RES_DTLS_ALL.PERSON_ID
• IGS_PE_RES_DTLS_H.RESIDENCY_CLASS_CD references IGS_PE_RES_DTLS_ALL.RESIDENCY_CLASS_CD

This indicates that the combination of a person and a residency class code forms the core link between the current and historical data sets. The table IGS_PE_RES_DTLS_H itself has a primary key constraint named IGS_PE_RES_DTLS_H_PK. Other objects, such as views or APIs within the Student System module, may historically have referenced this table for comprehensive data retrieval, though its "no longer used" status suggests newer logic may bypass it.