Results for “igs_ps_tch_resp_hist_all”

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

Overview

IGS_PS_TCH_RESP_HIST_ALL is a history (audit) table within the Oracle E-Business Suite Student System (IGS) product family. It records the chronological evolution of teaching responsibility assignments associated with a specific unit version. In the Oracle EBS 12.1.1 and 12.2.2 data models, the IGS schema supports higher-education institutions in managing academic structures such as programs, units, versions, and the organizational units responsible for delivering them. This table captures the "before and after" states of teaching responsibility allocations, including which organizational unit held responsibility, for what percentage of the unit, and over what effective date range.

The ETRM metadata explicitly notes that this entity is Not implemented in this database for the documented release, and the product is flagged as Obsolete. As a result, IGS_PS_TCH_RESP_HIST_ALL is generally encountered only in legacy reference material or custom-history implementations. The heuristic Data Vault classification mined from the foreign key structure is satellite-leaning. This suggests that, from a dimensional or Data Vault modeling perspective, the table functions as a satellite capturing descriptive, time-variant attributes around a hub or link defined by unit version and organizational unit. It is not itself a hub (no single natural business key) nor a pure link, but rather a historized attribute store.

Key Information Stored

The table is documented with 14 columns. The most significant are summarized below, distinguishing the surrogate/business key from descriptive attributes.

• UNIT_CD – Identifies the academic unit to which the teaching responsibility applies. Part of the primary key and the business-key unique index.
• VERSION_NUMBER – The specific version of the unit; together with UNIT_CD it anchors the history to a versioned unit record.
• ORG_UNIT_CD – The organizational unit code that bears teaching responsibility for the unit version.
• OU_START_DT – The effective start date of the organizational unit's responsibility.
• HIST_START_DT – The date from which this history record is effective; the discriminating key for the historized version.
• HIST_END_DT – The date on which this history record ceases to be current; supports temporal (as-of) querying.
• HIST_WHO – Identifies the actor or process that caused the historical change.
• PERCENTAGE – The percentage share of teaching responsibility assigned to the organizational unit.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – Standard Oracle EBS who-columns providing row-level auditability.
• ORG_ID – The multi-org operating unit identifier used for data partitioning and security.

The surrogate primary key is IGS_PS_TCH_RESP_HIST_PK, defined over UNIT_CD, VERSION_NUMBER, ORG_UNIT_CD, OU_START_DT, and HIST_START_DT. The unique index IGS_PS_TCH_RESP_HIST_ALL_U1 shares the identical column set, confirming these five columns as the business-key candidate that guarantees uniqueness of a historized responsibility record.

Common Use Cases and Queries

Because the table is a temporal satellite, its primary reporting purpose is reconstructing the state of teaching responsibility as of any point in time, and auditing who changed assignments and when. Typical scenarios include:

• As-of reporting: Reconstructing which organizational unit was responsible for a unit version on a given date, using HIST_START_DT and HIST_END_DT ranges.
• Change audit trails: Identifying all changes to a version's teaching responsibility, including percentage reallocations.
• Comparative analysis: Tracking responsibility shifts across academic periods for workload or resource planning.

A representative query pattern retrieves the currently effective responsibility for a unit version, filtering on the history window:

SELECT unit_cd, version_number, org_unit_cd, percentage
FROM igs_ps_tch_resp_hist_all
WHERE unit_cd = :unit_cd
AND version_number = :version
AND TRUNC(SYSDATE) BETWEEN hist_start_dt AND NVL(hist_end_dt, TRUNC(SYSDATE));

A change-history query orders rows by HIST_START_DT to expose the sequence of assignments for audit or reconciliation purposes. Because ORG_ID participates in multi-org security, queries should generally be constrained by the appropriate operating unit context.

Related Objects

The documented foreign key relationship anchors this table to the unit version master. Significant related objects include:

• IGS_PS_UNIT_VER_ALL – Referenced via UNIT_CD and VERSION_NUMBER; the parent unit-version entity that the teaching responsibility history describes.
• IGS_PS_TCH_RESP_ALL – The current-state teaching responsibility table (implied counterpart) from which history is typically derived.
• IGS_PS_TCH_RESP_HIST_PK – The primary key constraint enforcing uniqueness of the historized record.
• IGS_PS_TCH_RESP_HIST_ALL_U1 – The unique business-key index supporting efficient lookups by unit version, org unit, and dates.

Given the obsolete and non-implemented status documented in the ETRM metadata, administrators should verify object existence before relying on this table and confirm whether equivalent history functionality is provided by current Student System structures in their specific 12.1.1 or 12.2.2 installation.