Results for “igs_ps_type_hist_all”

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

Overview

IGS_PS_TYPE_HIST_ALL is a historical audit table within the Oracle E-Business Suite Student System (IGS) product family. It records the chronological history of changes made to program type definitions, capturing each prior state of a program type record as it is updated, retired, or reclassified. As its name implies, this is a date-effective ("_HIST_ALL") table, meaning it preserves every versioned row across all change events rather than only the current record. In the IGS schema, program types drive enrollment, award, and financial aid logic, so retaining their change trail is essential for audit, regulatory reporting, and reconciliation.

From a Data Vault modeling perspective, the ETRM metadata classifies this object heuristically as standalone. In practice, the table behaves as a satellite that tracks the descriptive attributes of a program type over time, keyed by a business effective-date column rather than an isolated surrogate. Because the classification is derived from FK structure and no incoming or outgoing foreign keys were mined, it is best treated as a self-contained historical satellite rather than a hub or link. Under Oracle EBS 12.1.1 and 12.2.2, this object is documented but not implemented in the base database — it is an obsolete, legacy IGS entity retained primarily for reference and migration analysis.

Key Information Stored

The table contains 19 documented columns. The composite primary key is IGS_PS_TYPE_HIST_PK, composed of COURSE_TYPE and HIST_START_DT. The unique index IGS_PS_TYPE_HIST_ALL_U1 covers the same two columns, confirming that the business key is the program type identifier combined with the effective start date of that historical version. There is no separate numeric surrogate key; the business key is the primary key.

The most significant columns are:

• COURSE_TYPE — the program type identifier (business key component).
• HIST_START_DT — effective start date of the versioned row (business key component).
• HIST_END_DT — end date of the version; null or a high date indicates the current/active version.
• HIST_WHO — the user or process that triggered the historical change.
• DESCRIPTION — narrative description of the program type at that point in time.
• GOVT_COURSE_TYPE — government-mapped course type code.
• AWARD_COURSE_IND — flag indicating whether the program type leads to an award.
• COURSE_TYPE_GROUP_CD — grouping classification for the program type.
• TAC_COURSE_LEVEL — level code used in teaching/assessment contexts.
• RESEARCH_TYPE_IND — indicates a research-oriented program type.
• CLOSED_IND — whether the program type is closed to new activity.
• FIN_AID_PROGRAM_TYPE — financial aid program type association.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.
• ORG_ID — multi-org operating unit context.

Common Use Cases and Queries

Typical uses include point-in-time reconstruction of a program type definition, audit trails for regulatory compliance, and change-diff reporting. A common pattern selects the version active on a given date:

SELECT * FROM igs_ps_type_hist_all WHERE course_type = :type AND hist_start_dt <= :as_of AND (hist_end_dt IS NULL OR hist_end_dt > :as_of);

To compare consecutive versions, analysts use the LAG/LEAD window functions partitioned by COURSE_TYPE ordered by HIST_START_DT. Reporting extracts often join this history to current program type dimensions to flag records whose attributes (e.g., AWARD_COURSE_IND, CLOSED_IND) have changed since a reference date.

Related Objects

Because the metadata classifies this object as standalone with no mined FK relationships, dependencies are inferred from the IGS program type domain rather than documented constraints. Significant related objects include:

• IGS_PS_TYPE_ALL — the current program type master; related via COURSE_TYPE.
• IGS_PS_TYPE_V — the current-version view counterpart, joined on COURSE_TYPE.
• IGS course/award catalog tables keyed by COURSE_TYPE and COURSE_TYPE_GROUP_CD.
• Financial aid program tables referencing FIN_AID_PROGRAM_TYPE.
• IGS enrollment/registration objects that resolve program type through COURSE_TYPE.

Consult the definitive IGS data model for authoritative relationship confirmation, as this entity is obsolete in 12.1.1 and 12.2.2.