Search Results audp_get_fosh_col

Overview

IGS_PS_FLD_STDY_HIST_V is a public Oracle E-Business Suite view owned by the APPS schema and delivered as part of the Student Systems (IGS) product family. Its name decomposes into "Field of Study History," indicating that it exposes change-history records maintained against an institution's defined fields of study. The view is a union of two complementary result sets: a historical slice drawn from the field-of-study history table joined to the master field-of-study table, and a synthesized "current" row derived from the master table alone. This design lets report authors and integration developers retrieve a single, complete picture of how a field of study evolved over time without writing the union manually.

The view is closely associated with the FOS_TYPE_CODE column, which classifies each field of study by type. Because the view exposes both this code and its descriptive counterpart, it is commonly used as a lookup source when reporting on field-of-study classifications and their historical attributes.

Underlying Base Objects

The ETRM metadata documents no explicit base-object list for this view, but the view text itself is unambiguous. It is defined over two tables:

• IGS_PS_FLD_OF_STUDY (aliased fos1/fos2) — the master table of field-of-study definitions, holding FOS_TYPE_CODE, FOS_TYPE_CODE_DESC, FIELD_OF_STUDY, DESCRIPTION, GOVT_FIELD_OF_STUDY, CLOSED_IND, and WHO-column audit fields.
• IGS_PS_FLD_STDY_HIST (aliased fosh1/fosh2) — the history table capturing dated attribute snapshots, keyed by the same FIELD_OF_STUDY value, with HIST_START_DT, HIST_END_DT, and HIST_WHO.

The first branch performs an inner join on FIELD_OF_STUDY, returning each archived version. The second branch performs an outer join (fos2.FIELD_OF_STUDY = fosh2.FIELD_OF_STUDY (+)) and aggregates with MAX(hist_end_dt) grouped by the master attributes, producing one row per field of study that always reflects the latest state. The UNION ALL combines both.

The view also invokes a helper function, IGS_AU_GEN_002.audp_get_fosh_col, to fall back on history-derived values when the primary history columns are null. The SUBSTR wrappers (60, 10, and 1 characters for description, government code, and closed flag respectively) enforce the width of the corresponding history columns.

Key Columns

• FOS_TYPE_CODE / FOS_TYPE_CODE_DESC — the field-of-study type and its description; the primary filter key for classification reporting.
• FIELD_OF_STUDY — unique identifier of the field of study.
• HIST_START_DT / HIST_END_DT — validity window of each historical snapshot. A null HIST_END_DT in the second branch denotes the currently effective row.
• HIST_WHO — user or process responsible for the historical change.
• DESCRIPTION, GOVT_FIELD_OF_STUDY, CLOSED_IND — effective attribute values resolved through the layered NVL logic.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard DML audit columns.

Common Use Cases and Queries

Typical uses include auditing changes to field-of-study definitions, extracting effective-dated classifications for downstream reporting, and resolving current attributes when a master row has been superseded.

SELECT fos_type_code, field_of_study, description,
       hist_start_dt, hist_end_dt
FROM   apps.igs_ps_fld_stdy_hist_v
WHERE  fos_type_code = :p_fos_type_code
ORDER  BY field_of_study, hist_start_dt;

To isolate current records only:

SELECT fos_type_code, field_of_study, description
FROM   apps.igs_ps_fld_stdy_hist_v
WHERE  hist_end_dt IS NULL
AND    closed_ind = 'N';

Because the view is not a base table and performs set operations on each access, queries should be constrained on fos_type_code or field_of_study to limit the rows materialized prior to the union.