Search Results fos_type_code




Overview
Explore in AI Chat

Overview

The view IGS_PS_FLD_STDY_HIST_V belongs to the IGS (Student System) product family within Oracle E-Business Suite. It is documented in ETRM as part of an obsolete component of the Student System module, and the metadata explicitly notes that it is "Not implemented in this database" in the reference environment. Its purpose, as described in the documentation, is to merge the field of study history records with the current field of study details, so that all attribute values for a given field of study can be accessed across the full span of time up to and including the current day. Each row represents a complete set of column values valid over a defined time period, combining historical snapshots held in the history table with the live record held in the current table.

Because the object is flagged as obsolete within the IGS Student System, it is primarily relevant to legacy implementations that were originally deployed on earlier Oracle Student System releases and later upgraded. It is not intended for new development, but it may still appear in existing customer schemas and in historical reporting logic.

Underlying Base Objects

The documented ETRM metadata lists no referenced base objects under the "Referenced base objects" field. However, the view text embedded in the documentation reveals the two source tables against which the view is defined:

  • IGS_PS_FLD_OF_STUDY (aliased FOS1 and FOS2) — the current field of study definition table.
  • IGS_PS_FLD_STDY_HIST (aliased FOSH1 and FOSH2) — the historical field of study table, storing time-bounded versions of field of study attributes.

The view is constructed as a UNION ALL of two queries. The first joins the two tables on FIELD_OF_STUDY and returns each historical row, substituting current values only where the historical column is null. The second query groups the current table with an outer join to the history table, producing a synthetic "open-ended" row whose HIST_START_DT is the maximum historical end date (or the record's last update date) and whose HIST_END_DT is null, representing the record as it stands today.

Key Columns

  • FOS_TYPE_CODE — the code identifying the field of study type. This is the column referenced in the user's search and is central to classifying field of study records.
  • FOS_TYPE_CODE_DESC — the descriptive text corresponding to the type code.
  • FIELD_OF_STUDY — the primary identifier of the field of study, used as the join key between the current and history tables.
  • HIST_START_DT and HIST_END_DT — the effective start and end of the historical validity window. A null HIST_END_DT indicates the current open record.
  • HIST_WHO — the user associated with the historical change.
  • DESCRIPTION, GOVT_FIELD_OF_STUDY, and CLOSED_IND — attribute columns resolved through nested NVL logic, falling back to an audit helper (IGS_AU_GEN_002.AUDP_GET_FOSH_COL) and then to the current record.
  • Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage centres on point-in-time or as-of reporting of field of study attributes, and on auditing how a field of study type or description changed over time.

To retrieve the full history for a single field of study:

  • SELECT fos_type_code, field_of_study, hist_start_dt, hist_end_dt, description FROM igs_ps_fld_stdy_hist_v WHERE field_of_study = :p_fos_code ORDER BY hist_start_dt;

To list all current (open-ended) records for a type code:

  • SELECT field_of_study, fos_type_code_desc, description FROM igs_ps_fld_stdy_hist_v WHERE fos_type_code = :p_type AND hist_end_dt IS NULL;

To find records valid at a specific date, filter on the historical windows. Because the object is obsolete and not implemented in the reference database, these queries should be validated against the target instance's actual view definition before being used in production reporting.