Search Results igs_or_org_alternate_ids_pk

Overview

The IGS_OR_ORG_ALT_IDS table is a data entity within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically belonging to the now-obsolete IGS (Student System) product family. Its primary function is to store alternate identifiers for institutions or organizational units within the system. This allows a single organizational entity to be referenced by multiple external or internal identification codes, such as government reporting IDs, legacy system codes, or partner institution identifiers. The table's designation as "Not implemented in this database" in the provided metadata indicates it may not be present in a standard installation, suggesting it was part of a specific, potentially unused, customization or a legacy component of the IGS module.

Key Information Stored

The table's structure is designed to uniquely define an alternate ID for an organizational unit within a specific timeframe. The primary key is a composite of five columns, ensuring uniqueness. Key columns include ORG_STRUCTURE_ID, which links to the specific institution or unit; ORG_STRUCTURE_TYPE, which likely defines the category of the organizational entity; and ORG_ALTERNATE_ID_TYPE, a foreign key referencing a lookup of valid ID types. The ORG_ALTERNATE_ID column holds the actual alternate code or number. The START_DATE column is part of the primary key, enabling the maintenance of a historical record of valid alternate IDs over time, though a corresponding END_DATE column is not listed in the provided metadata.

Common Use Cases and Queries

This table supports operational and reporting scenarios where organizational data must be cross-referenced with external systems. A common use case would be extracting a mapping of internal organization IDs to their corresponding government or accreditation body identifiers for regulatory submissions. A typical query would join this table to the organizational master table to retrieve all active alternate IDs for reporting purposes. For example, to find all alternate IDs for a specific location, one might use a SQL pattern such as: SELECT alt.org_alternate_id_type, alt.org_alternate_id FROM igs_or_org_alt_ids alt, igs_ad_location_all loc WHERE alt.org_structure_id = loc.location_id AND loc.location_code = '&LOC_CODE';. Another critical use case is data validation during interfaces, ensuring incoming records with an external organization code can be correctly matched to the internal EBS entity.

Related Objects

Based on the documented foreign key relationships, IGS_OR_ORG_ALT_IDS is integrally linked to two primary tables:

• IGS_AD_LOCATION_ALL: This is the master table for organizational locations. The relationship is established via the ORG_STRUCTURE_ID column in IGS_OR_ORG_ALT_IDS, which references a column of the same name (presumably LOCATION_ID) in IGS_AD_LOCATION_ALL. This join retrieves the descriptive details of the organization unit associated with the alternate ID.
• IGS_OR_ORG_ALT_IDTYP: This is a lookup table defining the valid types of alternate identifiers (e.g., 'STATE_CODE', 'FICE_CODE'). The ORG_ALTERNATE_ID_TYPE column in IGS_OR_ORG_ALT_IDS is a foreign key to this table, ensuring data integrity and providing a description for the ID type.

These relationships are essential for any query or report that needs to display the alternate ID alongside the organization's name or the meaningful description of the ID type.