Results for “igs_ps_unit_ref_cd”

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

Overview

IGS_PS_UNIT_REF_CD is a reference-code table within the Oracle E-Business Suite Student System (IGS) product family. In the ETRM 12.1.1 and 12.2.2 documentation it is classified as Obsolete, and the implementation note states "Not implemented in this database." The object represents the association between a specific version of an academic unit of study and one or more externally meaningful reference codes, such as an IVRS (interactive voice response) code. Reference codes of this type allow the institution to address a unit through alternate identifiers used by telephony, legacy, or third-party systems without altering the unit's own primary identifiers.

From a dimensional and Data Vault perspective, the heuristic classification supplied in the metadata is link. That classification is a modeling suggestion only: the table's all-key primary key and its role as a bridge between unit versions and the shared reference-code lookup make it behave as an associative link rather than as a hub or a descriptive satellite. Its business data is effectively a set of code assignments attached to a unit version.

Key Information Stored

The documented physical schema contains ten columns. The structurally significant columns are the four that make up the primary key and unique index:

• UNIT_CD — the code identifying the academic unit of study to which the reference code applies.
• VERSION_NUMBER — the unit version, allowing reference codes to differ across versions of the same unit.
• REFERENCE_CD_TYPE — the category or type of the reference code, for example an IVRS code class. This column participates in foreign key relationships.
• REFERENCE_CD — the actual reference code value assigned to that unit version and code type.

The primary key is named IGS_PS_UNIT_REF_CD_PK and comprises all four columns above. The unique index IGS_PS_UNIT_REF_CD_U1 covers the same four columns, so the table has no separate surrogate identifier; the composite business key is the identifier. This means a given unit version can hold at most one value per reference code type. A DESCRIPTION column provides free-text explanation of the assignment.

The remaining columns are standard EBS audit and concurrency attributes: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN. These support WHO-column auditing and multi-user concurrency control and should be retained in any extraction that requires change tracking.

Common Use Cases and Queries

Because the table is documented as obsolete and not implemented in the reference database, queries against it in a live 12.1.1 or 12.2.2 instance will only succeed where a legacy or customized Student System schema persists. The most common pattern is resolving a unit version to its alternate identifier:

• Joining IGS_PS_UNIT_REF_CD to IGS_GE_REF_CD on REFERENCE_CD_TYPE and REFERENCE_CD to obtain the human-readable code description.
• Joining to IGS_PS_UNIT_VER_ALL on UNIT_CD and VERSION_NUMBER to enrich a unit-version extract with its reference codes.
• Filtering by REFERENCE_CD_TYPE to isolate a single code class, such as the IVRS codes cited in the documentation.

A representative pattern is:
SELECT u.UNIT_CD, u.VERSION_NUMBER, r.REFERENCE_CD_TYPE, r.REFERENCE_CD, r.DESCRIPTION
FROM IGS_PS_UNIT_REF_CD r
JOIN IGS_PS_UNIT_VER_ALL u
ON r.UNIT_CD = u.UNIT_CD AND r.VERSION_NUMBER = u.VERSION_NUMBER
WHERE r.REFERENCE_CD_TYPE = :p_type;

Reporting use cases include validating that every active unit version carries a required code type, reconciling IVRS or legacy identifiers after a data migration, and auditing pending unit-version changes that alter an existing code assignment.

Related Objects

The documented foreign keys identify the following dependencies:

• IGS_PS_UNIT_VER_ALL — parent of the unit version; joined on UNIT_CD and VERSION_NUMBER.
• IGS_GE_REF_CD — supplies the reference code; joined on REFERENCE_CD_TYPE and REFERENCE_CD.
• IGS_GE_REF_CD_TYPE_ALL — supplies the code type definition; joined on REFERENCE_CD_TYPE.
• IGS_PS_UNIT_REF_CD_PK and IGS_PS_UNIT_REF_CD_U1 — the primary key constraint and its backing unique index, both on the four business-key columns.

Together these objects let a report resolve a unit version to its typed reference codes while remaining consistent with the generic reference-code framework shared by other IGS entities.