Search Results igs_or_rel_ps_type_n2

Overview

IGS.IGS_OR_REL_PS_TYPE is a seed data table within the Oracle E-Business Suite Student Systems (IGS) product family. It stores course type restrictions applied to organization unit relationships, primarily to support committee structures used within academic progression processes. The table allows an institution to nominate only particular types of courses within a given organization unit relationship; for example, a progression committee may be responsible for higher degree students from a faculty while a separate committee handles the remainder. The table resides in the APPS_TS_SEED tablespace and carries a status of VALID in the ETRM 12.1.1 / 12.2.2 documented schema.

From a heuristic Data Vault modeling perspective, the relationship metadata classifies this object as a link. The composite nature of the primary key — combining two organization units, their start dates, a creation date, and a course type — supports this interpretation: the row records an association between entities (parent organization unit, child organization unit, and course type) rather than describing a single business entity in isolation.

Key Information Stored

The table contains eleven documented columns. The most significant are described below.

• PARENT_ORG_UNIT_CD (VARCHAR2, 30) — The parent organizational unit code of the relationship.
• PARENT_START_DT (DATE) — The start date associated with the parent organization unit.
• CHILD_ORG_UNIT_CD (VARCHAR2, 30) — The child organizational unit code of the relationship.
• CHILD_START_DT (DATE) — The start date associated with the child organization unit.
• OUR_CREATE_DT (DATE) — The creation date of the organizational unit relationship. This is the column the user searched for; despite its name, it is a business attribute and part of the primary key, not one of the standard Who columns.
• COURSE_TYPE (VARCHAR2, 10) — The program type that qualifies the organizational unit relationship.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Oracle Who columns recording audit lineage.

The surrogate primary key is documented as IGS_OR_REL_PS_TYPE_PK, spanning PARENT_ORG_UNIT_CD, PARENT_START_DT, CHILD_ORG_UNIT_CD, CHILD_START_DT, OUR_CREATE_DT, and COURSE_TYPE. A unique index, IGS_OR_REL_PS_TYPE_U1, mirrors this same column set and serves as the business-key candidate. Two nonunique indexes support alternate access paths: IGS_OR_REL_PS_TYPE_N1 on COURSE_TYPE, and IGS_OR_REL_PS_TYPE_N2 on CHILD_ORG_UNIT_CD and CHILD_START_DT.

Common Use Cases and Queries

Typical usage centers on resolving which course types a progression committee is entitled to govern. A common reporting pattern joins the table to IGS_PS_TYPE_ALL to resolve the course type description, and to the organization unit relationship structures to enrich parent/child context.

A representative query retrieves all restrictions for a given child organization unit:

• SELECT parent_org_unit_cd, parent_start_dt, child_org_unit_cd, child_start_dt, our_create_dt, course_type FROM igs_or_rel_ps_type WHERE child_org_unit_cd = :unit AND child_start_dt = :dt;

Another pattern lists all relationships associated with a specific course type, leveraging the N1 index:

• SELECT child_org_unit_cd, child_start_dt, our_create_dt FROM igs_or_rel_ps_type WHERE course_type = :type;

Because OUR_CREATE_DT participates in the primary key, queries filtering on it can exploit IGS_OR_REL_PS_TYPE_U1 directly. Reporting extracts frequently include this table when producing committee responsibility matrices, course ownership by organizational unit, and progression rule validation reports.

Related Objects

• IGS.IGS_PS_TYPE_ALL — Referenced via the COURSE_TYPE foreign key; supplies the program type definition.
• IGS.IGS_OR_UNIT_REL — Referenced via PARENT_ORG_UNIT_CD; provides the parent organization unit relationship context.
• IGS_OR_REL_PS_TYPE_PK — The primary key constraint enforcing uniqueness across the six key columns.
• IGS_OR_REL_PS_TYPE_U1 — Unique business-key index on the full key column set.
• IGS_OR_REL_PS_TYPE_N1 — Nonunique index on COURSE_TYPE.
• IGS_OR_REL_PS_TYPE_N2 — Nonunique index on CHILD_ORG_UNIT_CD and CHILD_START_DT.

These relationships position the table as a junction between organization unit relationships and course type definitions, reinforcing its classification as a link object in a dimensional or Data Vault model.