Search Results org_alternate_id

Overview

The IGS_OR_ORG_ALT_IDS table is a core data repository within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically under the IGS (Oracle's former Student Systems) product family. Its primary function is to manage alternate identifiers for institutions or organizational units. This table enables a single organizational entity, such as a campus or department, to be associated with multiple external or internal identification codes beyond its primary system key. This is critical for integration with external systems, regulatory reporting, and maintaining historical identification records. The table's design includes date-effective tracking, allowing identifiers to be valid for specific periods.

Key Information Stored

The table stores the mapping between an organizational entity and its alternate identifiers. The key columns define this relationship and its validity period. The mandatory columns form a unique composite primary key, ensuring no duplicate identifier mappings for the same entity and type within the same timeframe.

• ORG_STRUCTURE_ID (VARCHAR2(30)): The identifier for the organizational unit or institution.
• ORG_STRUCTURE_TYPE (VARCHAR2(30)): Classifies the type of organizational structure referenced.
• ORG_ALTERNATE_ID_TYPE (VARCHAR2(10)): A code defining the category of the alternate ID (e.g., GOVERNMENT_CODE, LEGACY_SYSTEM_ID). This references the lookup table IGS_OR_ORG_ALT_IDTYP.
• ORG_ALTERNATE_ID (VARCHAR2(20)): The actual alternate identifier value.
• START_DATE (DATE): The date from which the alternate ID becomes effective.
• END_DATE (DATE): The expiration date of the alternate ID; a NULL value typically indicates it is currently active.
• Standard WHO Columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN): Audit columns tracking the record's creation and modification history.

Common Use Cases and Queries

This table is central to processes requiring translation between an EBS internal organization ID and an external agency's identifier. A common scenario is generating regulatory reports for a government body that requires submission using its assigned institutional code rather than the internal Oracle ID. Another use case is data migration or integration, where legacy system identifiers must be preserved and linked to the new EBS organizational records.

A typical query retrieves the current active alternate ID for a specific organization and ID type:

SELECT org_alternate_id
FROM igs.igs_or_org_alt_ids
WHERE org_structure_id = 'UNIV_MAIN_CAMPUS'
AND org_alternate_id_type = 'STATE_REG'
AND start_date <= SYSDATE
AND (end_date IS NULL OR end_date >= SYSDATE);

For reporting or data validation, a join to the location table may be used to list all alternate IDs for active organizations:

SELECT loc.location_name, alt.org_alternate_id_type, alt.org_alternate_id, alt.start_date, alt.end_date
FROM igs.igs_or_org_alt_ids alt,
igs.igs_ad_location_all loc
WHERE alt.org_structure_id = loc.location_code
AND alt.org_structure_type = 'LOCATION'
ORDER BY loc.location_name, alt.org_alternate_id_type;

Related Objects

The IGS_OR_ORG_ALT_IDS table maintains defined relationships with other key tables in the IGS schema, primarily through foreign key constraints. These relationships ensure referential integrity for the organizational entity and the alternate ID type.

• Primary Key: IGS_OR_ORG_ALTERNATE_IDS_PK on (ORG_STRUCTURE_ID, ORG_STRUCTURE_TYPE, ORG_ALTERNATE_ID_TYPE, ORG_ALTERNATE_ID, START_DATE).
• Foreign Key References (This table references):
  • IGS_AD_LOCATION_ALL: The ORG_STRUCTURE_ID column references a location, tying alternate IDs to specific physical or logical organizational units.
  • IGS_OR_ORG_ALT_IDTYP: The ORG_ALTERNATE_ID_TYPE column references this lookup table, which validates and defines the meaning of each alternate ID type code.
• Referenced By: The table is referenced by objects within the APPS schema, indicating its use by the application's public synonym and likely by standard EBS application logic and interfaces.