Results for “igs_ps_usec_ref_cd_pk”

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

Overview

IGS_PS_USEC_REF_CD is a table within the Oracle E-Business Suite Student System (IGS) product family. The ETRM documentation classifies this product as obsolete, and the table is explicitly noted as "Not implemented in this database" in the reference environment. Its stated purpose is to capture the relationship between Unit Sections Reference records and Reference Codes, functioning as a cross-reference or classification mechanism within the academic structure.

Within the IGS academic model, unit section references (the scheduling and offering entities derived from the course catalog) frequently need to be tagged with controlled reference codes — for example, classifying offerings by delivery mode, campus, funding category, or attendance pattern. IGS_PS_USEC_REF_CD holds those assignments. It is a junction, or link, entity: it resolves a many-to-many relationship between unit section references and the reference code catalogue. The ETRM metadata's Data Vault classification (mined heuristically from the foreign key structure) identifies this object as a link. This classification is a modelling suggestion rather than a functional property of the table: the physical table exhibits link semantics because it carries foreign keys to two or more independent entities and a composite uniqueness constraint over them.

Key Information Stored

The table is documented with 11 columns in the ETRM 12.1.1 physical schema. The most significant are described below.

• UNIT_SECTION_REFERENCE_CD_ID — The surrogate primary key, enforced through the IGS_PS_USEC_REF_CD_PK constraint and the unique index IGS_PS_USEC_REF_CD_U1. This is a system-generated identifier with no business meaning.
• UNIT_SECTION_REFERENCE_ID — Foreign key to the parent unit section reference record in IGS_PS_USEC_REF. This is the driving column in the business key.
• REFERENCE_CODE_TYPE — Identifies the category or type of reference code being assigned. This column participates in foreign keys to both IGS_GE_REF_CD_TYPE_ALL and IGS_GE_REF_CD.
• REFERENCE_CODE — The actual code value assigned to the unit section reference. Paired with REFERENCE_CODE_TYPE, it forms the composite foreign key to IGS_GE_REF_CD.
• REFERENCE_CODE_ID — Surrogate identifier for the reference code row, also pointing to IGS_GE_REF_CD. An additional foreign key relationship is documented on this column.
• REFERENCE_CODE_DESC — A denormalised, descriptive text value for the assigned code, useful for reporting without joining back to the reference-code lookup.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle Applications WHO columns capturing audit and concurrency information for each row.

The composite unique index IGS_PS_USEC_REF_CD_U2 covers UNIT_SECTION_REFERENCE_ID, REFERENCE_CODE_TYPE, and REFERENCE_CODE, preventing duplicate assignment of the same reference code type and value to a given unit section reference. This constraint is the principal business-key candidate for the table.

Common Use Cases and Queries

Because the table is obsolete and unimplemented, its use cases are largely historical or migration-oriented. Typical patterns include extracting the complete set of reference codes attached to a unit section offering, or validating that each reference code type is populated exactly once per reference.

• Listing codes for a specific unit section reference: SELECT reference_code_type, reference_code, reference_code_desc FROM igs_ps_usec_ref_cd WHERE unit_section_reference_id = :id;
• Reporting code usage by type: SELECT reference_code_type, reference_code, COUNT(*) FROM igs_ps_usec_ref_cd GROUP BY reference_code_type, reference_code;
• Validating referential integrity against the reference-code catalogue via a join on REFERENCE_CODE_TYPE and REFERENCE_CODE to IGS_GE_REF_CD.
• Migration or archival extraction joining to IGS_PS_USEC_REF to recover the full academic context of each assignment.

Related Objects

The following relationships are documented in the ETRM metadata and represent the principal dependencies on this table.

• IGS_PS_USEC_REF — Parent unit section reference entity, joined via UNIT_SECTION_REFERENCE_ID.
• IGS_GE_REF_CD_TYPE_ALL — Reference code type lookup, joined via REFERENCE_CODE_TYPE.
• IGS_GE_REF_CD — Reference code catalogue, joined via REFERENCE_CODE_TYPE and REFERENCE_CODE, and additionally via REFERENCE_CODE_ID.
• IGS_PS_USEC_REF_CD_PK and IGS_PS_USEC_REF_CD_U1 / U2 — Primary key and unique indexes enforcing identity and business-key integrity.