Search Results igs_pe_alt_pers_id

Overview

The IGS_PE_ALT_PERS_ID table is a core data entity within the Oracle E-Business Suite's now-obsolete Student System (IGS) module. Its primary function is to store a historical and alternative identifier registry for persons within the system. It allows for the association of multiple external or legacy identifiers with a single individual, such as IDs from previous student information systems, government or educational agency identifiers (e.g., OLAA, VTAC), or other institutional codes. This capability is critical for maintaining data integrity and continuity when integrating records from disparate sources or during system migrations. The table's design supports tracking the validity of these identifiers through a start date, enabling a temporal view of the identifiers a person has held.

Key Information Stored

The table's structure is designed to link an alternative identifier to a core person record and classify its type. The primary key is a composite of four columns, ensuring uniqueness for a person's identifier of a specific type from a specific date. Key columns include:

• PE_PERSON_ID: The foreign key linking to the core person record in the HZ_PARTIES table, representing the individual to whom the alternate ID belongs.
• API_PERSON_ID: The actual value of the alternative person identifier being stored.
• PERSON_ID_TYPE: A code classifying the nature of the alternative ID (e.g., 'VTAC', 'OLD_SYS'), referenced via a foreign key to the IGS_PE_PERSON_ID_TYP table.
• START_DT: The date from which this alternative identifier became valid or was associated with the person.

Common Use Cases and Queries

This table is essential for reporting, data reconciliation, and legacy system interface processes. A common use case is identifying a person when only a legacy or external ID is known, such as during an application import from a tertiary admissions center. For reporting, it enables the correlation of historical data from retired systems with current records. A typical query would retrieve the current EBS person details using a known external ID:

SELECT hp.party_name, alt.api_person_id, alt.person_id_type, alt.start_dt
FROM igs_pe_alt_pers_id alt, hz_parties hp
WHERE alt.pe_person_id = hp.party_id
AND alt.api_person_id = '<KNOWN_LEGACY_ID>';

Another pattern involves listing all alternative identifiers for a specific person for an audit trail:

SELECT alt.api_person_id, typ.description, alt.start_dt
FROM igs_pe_alt_pers_id alt, igs_pe_person_id_typ typ
WHERE alt.person_id_type = typ.person_id_type
AND alt.pe_person_id = <PE_PERSON_ID>
ORDER BY alt.start_dt DESC;

Related Objects

The IGS_PE_ALT_PERS_ID table maintains defined foreign key relationships with fundamental person and code tables in the EBS architecture. These relationships are critical for maintaining referential integrity.

• HZ_PARTIES: The master table for all persons, organizations, and groups in Oracle Trading Community Architecture. The join is made on IGS_PE_ALT_PERS_ID.PE_PERSON_ID = HZ_PARTIES.PARTY_ID. This links every alternative ID to a valid, master party record.
• IGS_PE_PERSON_ID_TYP: A code lookup table that defines and describes the valid types of person identifiers. The join is made on IGS_PE_ALT_PERS_ID.PERSON_ID_TYPE = IGS_PE_PERSON_ID_TYP.PERSON_ID_TYPE. This ensures data quality by restricting the identifier type to a controlled list.