Results for “igs_ps_prg_unit_rel_pk”

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

Overview

IGS_PS_PRG_UNIT_REL is an Oracle E-Business Suite Student System (IGS) configuration table that stores the relationship between program type levels and unit type levels. It resides in the IGS schema and holds a VALID status in both ETRM 12.1.1 and 12.2.2. The table functions as a validation and control matrix: it defines which unit types are permitted, credit-bearing, or reportable against a given student career level within the academic structure. Because it constrains the combinations of program-level and unit-level attributes that downstream enrollment, registration, and academic record processing may accept, it is a foundational setup object rather than a transactional one. Under the heuristic Data Vault classification supplied in the metadata, the object is best modeled as a link — a relationship table connecting two reference entities (student career level and unit type level), with associated descriptive attributes. This classification is a modeling suggestion derived from the foreign-key structure, not a physical implementation statement.

Key Information Stored

The table is documented with ten columns. The surrogate primary key is PS_PRUN_REL_ID, enforced by the constraint IGS_PS_PRG_UNIT_REL_PK. Two unique indexes expose business-key candidates: IGS_PS_PRG_UNIT_REL_U1 on PS_PRUN_REL_ID and IGS_PS_PRG_UNIT_REL_U2, a composite unique key on (STUDENT_CAREER_LEVEL, UNIT_TYPE_ID). The composite key is the significant one for data integrity, since it prevents duplicate program-to-unit mappings at the same career level.

• PS_PRUN_REL_ID — system-generated surrogate identifier; primary key of the relationship row.
• STUDENT_CAREER_LEVEL — the program type level; foreign key to IGS_PS_TYPE_ALL. Defines the career dimension of the mapping.
• UNIT_TYPE_ID — the unit type level; foreign key to IGS_PS_UNIT_TYPE_LVL. Defines the unit dimension of the mapping.
• STUDENT_CAREER_TRANSCRIPT — flag or indicator controlling whether units of this relationship appear on the student transcript.
• STUDENT_CAREER_STATISTICS — indicator controlling whether units of this relationship are included in student career statistical reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS WHO columns providing audit lineage for each row.

Common Use Cases and Queries

Typical usage centers on setup validation, enrollment eligibility checking, and reporting extracts. A common query resolves the descriptive names of both reference entities:

• Resolve a complete mapping: join IGS_PS_PRG_UNIT_REL to IGS_PS_TYPE_ALL on STUDENT_CAREER_LEVEL and to IGS_PS_UNIT_TYPE_LVL on UNIT_TYPE_ID to render human-readable program-type and unit-type descriptions.
• Validate a registration combination: select PS_PRUN_REL_ID where STUDENT_CAREER_LEVEL = :career and UNIT_TYPE_ID = :unit_type to confirm that a proposed enrollment is permitted before committing student unit attempt records.
• Transcript and statistics configuration audit: filter on STUDENT_CAREER_TRANSCRIPT and STUDENT_CAREER_STATISTICS to identify mappings whose reporting behavior diverges from institutional policy.
• Duplicate detection: group by (STUDENT_CAREER_LEVEL, UNIT_TYPE_ID) having count(*) > 1 as a data-quality check, though the U2 unique index should prevent this in a healthy instance.
• Migration and comparison reporting: extract PS_PRUN_REL_ID, STUDENT_CAREER_LEVEL, and UNIT_TYPE_ID with the WHO columns to compare configuration between environments or releases.

Related Objects

The relationship graph is defined by two foreign keys and one primary key. The most significant related objects are:

• IGS_PS_TYPE_ALL — referenced via STUDENT_CAREER_LEVEL; the program type reference table supplying the career dimension.
• IGS_PS_UNIT_TYPE_LVL — referenced via UNIT_TYPE_ID; the unit type level reference table supplying the unit dimension.
• IGS_PS_PRG_UNIT_REL_PK — primary key constraint/parent index on PS_PRUN_REL_ID.
• IGS_PS_PRG_UNIT_REL_U1 / U2 — unique indexes on PS_PRUN_REL_ID and (STUDENT_CAREER_LEVEL, UNIT_TYPE_ID) respectively; the U2 index is the effective business key.
• Student enrollment, unit attempt, and academic record processing components in the IGS product that consult this table as a validation source when creating or updating student unit attempts.

Because the table is configuration-oriented, referential integrity and correct setup in IGS_PS_TYPE_ALL and IGS_PS_UNIT_TYPE_LVL must be established before rows are loaded here; otherwise downstream registration and transcript processes may reject valid enrollments or omit units from reporting.