Search Results perm_resident_cd




Overview
Explore in AI Chat

Overview

The IGS_PE_PERM_RES_CD table is a core reference data table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Institutional Global Systems (IGS) module. It functions as a lookup table that standardizes and defines permanent resident status codes for individuals within the system. Its primary role is to maintain a controlled list of institution-specific permanent residence codes, each of which must map to an official government-defined code. This mapping ensures data integrity and compliance with external reporting requirements, such as the DETYA (Department of Education, Training and Youth Affairs) Higher Education Student Data Collection in the Australian context. The table is fundamental for accurately capturing and reporting on a person's residency status.

Key Information Stored

The table stores the definition and metadata for each permanent residence code. The most critical columns include:

  • PERM_RESIDENT_CD (PK): A unique, institution-defined 10-character code representing a specific permanent resident status.
  • GOVT_PERM_RESIDENT_CD: A mandatory numeric code that maps the institution's code to an official government standard (DETYA element 390). This enforces compliance.
  • DESCRIPTION: A 60-character text field providing a meaningful description of the institution's code.
  • CLOSED_IND: A flag indicating whether the code is active ('N') or closed ('Y'). Closed codes cannot be used in new or modified person records.
  • NOTES: A large (2000-character) field for administrative comments or clarifications regarding the code.
  • Standard WHO Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): For auditing and tracking data changes.

Common Use Cases and Queries

This table is primarily used in two contexts: data maintenance and person-related reporting. Administrators use forms in the EBS application to maintain the code list, ensuring the GOVT_PERM_RESIDENT_CD mapping is correct. For reporting and data validation, common SQL queries include fetching all active codes for use in a list of values (LOV) or validating person statistics. A typical query to retrieve all active, mapped codes is:

SELECT perm_resident_cd, description, govt_perm_resident_cd FROM igs.igs_pe_perm_res_cd WHERE closed_ind = 'N' ORDER BY 1;

Another critical use case is joining to person statistics to generate compliance or demographic reports, linking via the PERM_RESIDENT_CD foreign key in the IGS_PE_STATISTICS table.

Related Objects

The IGS_PE_PERM_RES_CD table has defined relationships with several other key objects in the IGS schema, forming a critical part of the person data model.

  • Primary Key: The table's primary key constraint (IGS_PE_PERM_RES_CD_PK) is defined on the PERM_RESIDENT_CD column.
  • Referenced Foreign Key: The GOVT_PERM_RESIDENT_CD column references a government code lookup table, likely named IGS_PE_GOV_PER_RESCD, to enforce valid government code mappings.
  • Referencing Foreign Key: The table is referenced by the IGS_PE_STATISTICS table via its PERM_RESIDENT_CD column. This is the primary usage link, ensuring that residency codes recorded against a person are valid and defined in this reference table.