Results for “igs_ps_us_req_ref_cd”

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

Overview

IGS_PS_US_REQ_REF_CD is a reference-code intersection table within the Oracle E-Business Suite Student System (IGS) product family. It stores the association between a unit section reference record and the external reference codes that qualify or categorize that record. The table is documented in ETRM as Obsolete and is not implemented in the reference database, meaning installations on Oracle EBS 12.1.1 and 12.2.2 generally do not contain its physical segment unless a legacy Student System deployment introduced it and retained it through upgrade.

From a Data Vault modeling perspective, the mined heuristic classifies this object as a link table. The classification follows directly from its structure: the primary key is a surrogate identifier, while the substantive business content is entirely composed of foreign-key references to a parent unit section reference row and to a shared reference-code lookup. A link interpretation is a modeling suggestion rather than a documented Oracle classification, but it accurately reflects the table's role as a pure relationship carrier between two otherwise independent entities. No descriptive attributes beyond creator/updater audit columns and a redundant code description are present, which reinforces the link characterization.

Key Information Stored

The table is documented with eleven columns. The most significant are:

• UNIT_SECTION_REQ_REF_CD_ID — the surrogate primary key, enforced by IGS_PS_US_REQ_REF_CD_PK and mirrored by unique index IGS_PS_US_REQ_REF_CD_U1. It carries no business meaning and exists solely to give each row a stable identity.
• UNIT_SECTION_REFERENCE_ID — foreign key to IGS_PS_USEC_REF, identifying the unit section reference to which the code is attached.
• REFERENCE_CD_TYPE — the reference-code category or lookup type, drawn from IGS_GE_REF_CD.
• REFERENCE_CODE — the actual reference value within the type, also validated against IGS_GE_REF_CD.
• REFERENCE_CODE_ID — a secondary reference-code identifier with its own foreign-key path to IGS_GE_REF_CD.
• REFERENCE_CODE_DESC — a denormalized description of the code, useful for reporting without a lookup.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle audit columns recording row provenance and change history.

The business-key candidate is unique index IGS_PS_US_REQ_REF_CD_U2 over (UNIT_SECTION_REFERENCE_ID, REFERENCE_CD_TYPE, REFERENCE_CODE), which prevents duplicate code assignments on the same unit section reference.

Common Use Cases and Queries

Because this is a link table, typical usage resolves codes for a unit section reference or performs reverse lookups by code. Queries join to IGS_GE_REF_CD for code validation and description, and to IGS_PS_USEC_REF for section context:

• List all reference codes attached to a unit section reference, ordered by type.
• Find unit section references carrying a specific reference code for compliance or eligibility reporting.
• Detect orphaned or invalid codes by outer-joining IGS_GE_REF_CD and filtering null code descriptions.
• Reconcile duplicate assignments using the U2 business key.

A representative pattern is: SELECT r.UNIT_SECTION_REFERENCE_ID, r.REFERENCE_CD_TYPE, r.REFERENCE_CODE, r.REFERENCE_CODE_DESC FROM IGS_PS_US_REQ_REF_CD r WHERE r.UNIT_SECTION_REFERENCE_ID = :section_ref_id;

Related Objects

• IGS_GE_REF_CD — the reference-code lookup referenced twice, via REFERENCE_CD_TYPE/REFERENCE_CODE and via REFERENCE_CODE_ID.
• IGS_PS_USEC_REF — parent unit section reference table referenced by UNIT_SECTION_REFERENCE_ID.
• IGS_PS_US_REQ_REF_CD_PK — primary-key constraint over UNIT_SECTION_REQ_REF_CD_ID.
• IGS_PS_US_REQ_REF_CD_U1 / U2 — unique indexes defining the surrogate key and business key.

Given its obsolete status, integration with active IGS APIs is unlikely; the table primarily supports historical reporting and legacy data migration analysis.