Results for “org_alternate_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_OR_ORG_ALT_IDS is a table within the IGS (Student System) product family of Oracle E-Business Suite, documented against releases 12.1.1 and 12.2.2. It stores alternate identifiers assigned to institutions or organizational units, allowing the same organization record to be recognized under multiple external or legacy identifiers. In the ETRM metadata the object is explicitly flagged as obsolete and, in the sampled environment, is not implemented in the database. Its presence in the data dictionary therefore serves reference and migration-planning purposes rather than active application support.

Functionally, the table acts as a cross-reference between an internal organizational structure record and one or more alternate identification schemes. This pattern is common where an institution must reconcile its internal organizational hierarchy with external reporting bodies, accreditation registries, or legacy systems that use different keys. From a heuristic Data Vault perspective, the mined foreign-key structure classifies this entity as a link, connecting organization structure references to alternate identifier type definitions.

Key Information Stored

The documented physical schema for 12.1.1 records the owner as IGS with 11 columns. The most significant columns are:

• ORG_STRUCTURE_ID — identifier of the organizational structure or institution record being aliased; participates in both the primary key and the foreign key to IGS_AD_LOCATION_ALL.
• ORG_STRUCTURE_TYPE — discriminator indicating the category of organizational structure referenced.
• ORG_ALTERNATE_ID_TYPE — classification of the alternate identifier, validated against IGS_OR_ORG_ALT_IDTYP.
• ORG_ALTERNATE_ID — the actual alternate identifier value held for the organization.
• START_DATE and END_DATE — effective dating that bounds the validity of the alternate identifier.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — standard Oracle EBS audit columns capturing who created or modified the row and when.

The primary key constraint is IGS_OR_ORG_ALTERNATE_IDS_PK, spanning ORG_STRUCTURE_ID, ORG_STRUCTURE_TYPE, ORG_ALTERNATE_ID_TYPE, ORG_ALTERNATE_ID, and START_DATE. A unique index, IGS_OR_ORG_ALT_IDS_U1, carries the identical column list, making it the business-key candidate that enforces uniqueness of an alternate identifier per structure, type, and effective start date. Because the composite key and unique index share the same columns, no separate surrogate key column is documented.

Common Use Cases and Queries

Where the table is populated, the most frequent requirement is resolving an alternate identifier back to its organization, typically during interface or conversion work. A representative pattern joins the base table to the identifier type lookup:

• Resolve an external identifier: SELECT ORG_STRUCTURE_ID FROM IGS_OR_ORG_ALT_IDS WHERE ORG_ALTERNATE_ID = :id AND ORG_ALTERNATE_ID_TYPE = :type AND TRUNC(SYSDATE) BETWEEN START_DATE AND NVL(END_DATE, TRUNC(SYSDATE));
• List all aliases for an organization: filter on ORG_STRUCTURE_ID and ORG_STRUCTURE_TYPE, joining IGS_OR_ORG_ALT_IDTYP for a descriptive type name.
• Effective-dating audits: identify overlapping or expired identifiers using START_DATE and END_DATE, which supports reconciliation and data-cleansing reports.

Since the object is obsolete and not implemented in the sampled database, these queries should be treated as migration reference patterns rather than production reporting statements.

Related Objects

The documented foreign keys establish the primary relationships for this entity:

• IGS_AD_LOCATION_ALL — referenced on ORG_STRUCTURE_ID, supplying the organization or institution record associated with each alternate identifier.
• IGS_OR_ORG_ALT_IDTYP — referenced on ORG_ALTERNATE_ID_TYPE, providing the valid alternate identifier types and their descriptions.

These two relationships define the link structure of the table and are the joins most likely required in any query against it. Broader IGS organization and student-system objects that model the parent organizational hierarchy are the natural consumers of these mappings, though they fall outside the FK set recorded here.