Search Results igs_or_rel_ps_type_pk

Overview

IGS_OR_REL_PS_TYPE is a table in the Oracle E-Business Suite Student System (IGS) product family. The ETRM metadata classifies the owning module as IGS - Student System (Obsolete), and the object itself is marked Not implemented in this database in the supplied 12.1.1 baseline. Its documented purpose is to store the course type restrictions applied to an organization unit relationship, specifically for the committee structures used within the Progression functionality, with the stated intent that it be reused by other areas in future releases. In practical terms it acts as a qualification or filtering layer over the base organization unit relationship: a given parent/child organization unit pairing is valid only for a defined set of course types held in this table.

The table is owned by the IGS schema and, per the documented physical schema for 12.1.1, contains 11 columns. The structure is a composite-key intersection table that resolves a many-to-many style relationship. Where the Object Relationship metadata supplies a Data Vault classification (mined heuristically from the foreign key structure), the suggestion is link. This is a modeling suggestion rather than a physical characteristic: IGS_OR_REL_PS_TYPE does not carry descriptive attributes of its own beyond the association keys, audit columns, and the course type discriminator, which is consistent with a link entity joining an organization unit relationship to a course type definition. Consumers should treat the table as a restriction/allow-list rather than as a transactional or historical record.

Key Information Stored

The most important columns are the composite business key components and the course type discriminator:

• PARENT_ORG_UNIT_CD and PARENT_START_DT — the parent organization unit code and the effective start date of that parent unit relationship.
• CHILD_ORG_UNIT_CD and CHILD_START_DT — the child organization unit code and its effective start date, completing the organization unit relationship pair.
• OUR_CREATE_DT — the create date of the underlying organization unit relationship record, included so that the specific relationship version is uniquely identified.
• COURSE_TYPE — the course type that is permitted for the identified organization unit relationship; this is the restriction being recorded.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS "Who" audit columns.

The documented primary key is IGS_OR_REL_PS_TYPE_PK, and the unique index IGS_OR_REL_PS_TYPE_U1 covers exactly the same column list: PARENT_ORG_UNIT_CD, PARENT_START_DT, CHILD_ORG_UNIT_CD, CHILD_START_DT, OUR_CREATE_DT, and COURSE_TYPE. There is therefore no separate surrogate primary key column; the business key components themselves form the primary key, and the U1 unique index is a business-key candidate that enforces the same constraint. Notably, none of the audit columns participate in the key.

Common Use Cases and Queries

Because the table restricts which course types may be attached to a committee structure, the principal query pattern is a membership test: given an organization unit relationship and a course type, is the combination permitted? A representative pattern follows.

• Restriction lookup: select COURSE_TYPE from IGS_OR_REL_PS_TYPE where PARENT_ORG_UNIT_CD = :parent and PARENT_START_DT = :parent_start and CHILD_ORG_UNIT_CD = :child and CHILD_START_DT = :child_start.
• All permitted course types for a relationship: the same predicate set without a COURSE_TYPE filter, typically joined to IGS_PS_TYPE_ALL to resolve the course type description for reporting.
• Validation / troubleshooting: identifying relationships with no restriction rows, or duplicate-free verification of the U1 key, supports diagnosis of committee structure setup errors in Progression.
• Reporting: joining to IGS_OR_UNIT_REL supplies the organization unit names behind the code and date pairs, yielding a readable committee structure with its allowed course types.

Reporting should be scoped to an effective date range using PARENT_START_DT, CHILD_START_DT, and OUR_CREATE_DT, since the same codes can recur across relationship versions.

Related Objects

The foreign key metadata identifies the tables that this object depends on:

• IGS_OR_UNIT_REL — joined on PARENT_ORG_UNIT_CD, PARENT_START_DT, CHILD_ORG_UNIT_CD, CHILD_START_DT, and OUR_CREATE_DT. This is the parent organization unit relationship that the course type restriction qualifies.
• IGS_PS_TYPE_ALL — joined on COURSE_TYPE. Supplies the course type definition referenced by the restriction row.
• IGS_OR_REL_PS_TYPE — self-referencing in the FK listing, reflecting the composite key columns that resolve back to the parent relationship entity through IGS_OR_UNIT_REL.

No views or public APIs are documented for this object in the supplied metadata. Given the "Obsolete" product classification and the "Not implemented in this database" status, validation against a live 12.2.2 instance is advisable before relying on the table for new development.