Results for “igs_ps_ref_cd”

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

Overview

IGS_PS_REF_CD is a reference code assignment table within the Oracle E-Business Suite Student System (IGS) product family. It stores arbitrary reference codes that are associated with a specific program version. The documented example is an IVRS code, but the table is generically designed so that any externally defined coding scheme can be attached to a program. The table is owned by the IGS schema, is marked VALID in the ETRM 12.1.1 physical schema, and contains 10 documented columns.

Mined from its foreign key structure, the heuristic Data Vault classification for this object is link. This is a modeling suggestion rather than a physical implementation: the table resolves many-to-many style relationships between a program version and a reference code type/value pair, behaving as an associative (link) entity rather than as a descriptive satellite of a single parent.

Key Information Stored

The four columns that constitute the primary key IGS_PS_REF_CD_PK carry the substantive business content of the row. COURSE_CD identifies the program (course) to which the reference code applies, and VERSION_NUMBER pins the assignment to a specific version of that program. Together these two columns anchor the record to a program version. REFERENCE_CD_TYPE names the category or scheme of the reference code, and REFERENCE_CD holds the actual code value within that scheme. The combination of these four columns is also exposed as the unique index IGS_PS_REF_CD_U1, making the full key the documented business-key candidate and preventing duplicate code assignments for the same program version and type.

DESCRIPTION provides a free-text explanation of the reference code. The remaining columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — are the standard Oracle EBS WHO audit columns recording insert and update attribution and timestamps. No separate surrogate identifier is documented; identity is carried entirely by the composite business key.

Common Use Cases and Queries

The most frequent access pattern is retrieving all reference codes for a given program version, typically surfaced through a program details or prospectus inquiry form. A representative query joins the table to its parents:

• List all reference codes for a program version: SELECT r.reference_cd_type, r.reference_cd, r.description FROM igs.igs_ps_ref_cd r WHERE r.course_cd = :course_cd AND r.version_number = :version_number;
• Resolve a specific type, for example an IVRS code: SELECT reference_cd FROM igs.igs_ps_ref_cd WHERE course_cd = :course_cd AND version_number = :version_number AND reference_cd_type = :ref_type;
• Validate that a code type is defined before use by joining to IGS_GE_REF_CD_TYPE_ALL on REFERENCE_CD_TYPE.
• Audit recently changed assignments using LAST_UPDATE_DATE and LAST_UPDATED_BY.
• Reporting extracts that feed downstream code lookups by cross-referencing IGS_PS_VER_ALL for program descriptive attributes.

Because the key is fully composite, all update and delete operations should be scoped by all four key columns to avoid unintended multi-row changes.

Related Objects

The documented foreign keys establish the table's principal dependencies:

• IGS_PS_VER_ALL — parent of COURSE_CD and VERSION_NUMBER; supplies the program version context.
• IGS_GE_REF_CD_TYPE_ALL — parent of REFERENCE_CD_TYPE; defines the valid reference code schemes.
• IGS_PS_REF_CD_PK — the primary key constraint enforcing uniqueness of the four key columns.
• IGS_PS_REF_CD_U1 — the unique index supporting the business key, also on COURSE_CD, VERSION_NUMBER, REFERENCE_CD_TYPE, REFERENCE_CD.

Any IGS program configuration inquiry, admission lookup, or external interface that resolves program-level reference codes will traverse this table, generally in conjunction with the two parent tables above.