Search Results igs_ps_fld_stdy_hist




Overview
Explore in AI Chat

Overview

The view IGS_PS_FLD_STDY_HIST belongs to the Oracle E-Business Suite product group IGS — Student System, which is classified in the ETRM reference as obsolete. The view exposes historical records of Field of Study definitions, one of the core reference data structures used by the Student System to classify academic programs and enrollment records. In the Oracle EBS 12.1.1 and 12.2.2 release levels, this object is documented as not implemented in the current database, meaning the view exists in the metadata repository as a definition only and carries no runtime data in a standard installation. Its role, where deployed historically, was to provide a denormalized, user-friendly projection of the underlying history table so that reporting and integration components could resolve a field-of-study code to its descriptive meaning without joining to the lookup infrastructure directly.

Underlying Base Objects

The documented ETRM metadata for this view lists no referenced base objects; however, the view text itself shows that it is defined over exactly two sources:

  • IGS_PS_FLD_STDY_HIST_ALL — the base history table holding the actual field-of-study records, aliased as TAB. This is a partitioned, org-enabled _ALL table, as evidenced by the ORG_ID column and the ROWID-based ROW_ID alias.
  • IGS_LOOKUP_VALUES — the standard Oracle lookup values table, aliased as US, used to translate the FOS_TYPE_CODE into a human-readable description.

The join is an inner join on TAB.FOS_TYPE_CODE = US.LOOKUP_CODE restricted to US.LOOKUP_TYPE = 'PS_FOS_TYPE_CD'. Because the join is not outer, any history row whose type code has no matching lookup value is silently excluded from the result set.

Key Columns

The view projects the following columns:

  • ROW_ID — the ROWID of the underlying history table row, unique per physical record.
  • FOS_TYPE_CODE / FOS_TYPE_CODE_DESC — the field-of-study type code and its decoded meaning from the lookup table.
  • FIELD_OF_STUDY — the primary field-of-study identifier or description.
  • HIST_START_DT / HIST_END_DT — the effective date range for the historical record, defining when the field-of-study definition was active.
  • HIST_WHO — the user or process responsible for the historical change.
  • DESCRIPTION / GOVT_FIELD_OF_STUDY / CLOSED_IND — descriptive text, the government reporting classification, and a closure flag indicating whether the field of study is inactive.
  • Standard audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and ORG_ID for multi-org security and audit lineage.

Common Use Cases and Queries

Typical usage in historical IGS implementations was to reconstruct the state of a field of study as of a given date, or to audit which changes were made and by whom. Because the view is documented as not implemented, any query should first confirm existence in the target instance.

  • Point-in-time reconstruction: filter on HIST_START_DT <= :as_of_date and HIST_END_DT >= :as_of_date.
  • Audit reporting: select HIST_WHO along with the audit columns to trace modifications.
  • Government reporting extracts: use GOVT_FIELD_OF_STUDY together with FOS_TYPE_CODE_DESC for statutory files.
  • Active-record filtering: apply CLOSED_IND = 'N' to exclude retired definitions.

Sample query:

SELECT fos_type_code_desc, field_of_study, hist_start_dt, hist_end_dt, closed_ind
FROM igs_ps_fld_stdy_hist
WHERE org_id = :org_id
AND hist_start_dt <= SYSDATE
AND NVL(hist_end_dt, SYSDATE) >= SYSDATE
ORDER BY field_of_study;

Given the obsolete classification of the IGS product line, this view should be treated as reference-only for upgrade and migration analysis rather than as a supported object in current 12.2.2 deployments.