Search Results ps_fos_type_cd

Overview

IGS_PS_FLD_STDY_HIST is an Oracle EBS view owned by the APPS schema and defined in the Student Systems (IGS) product family, specifically within the Postsecondary/Student field-of-study configuration area. It exposes historical versions of field-of-study records together with a decoded, user-facing description of the field-of-study type. The view is a reporting and integration convenience object: rather than joining the history base table to the lookup table manually, consumers receive a single row per historical field-of-study entry with the lookup meaning already resolved through the fos_type_code_desc column.

The view is defined over IGS_PS_FLD_STDY_HIST_ALL as the driving table and joins IGS_LOOKUP_VALUES on the field-of-study type code. The lookup predicate anchors on US.LOOKUP_TYPE = 'PS_FOS_TYPE_CD' — the exact lookup type referenced by the user's search term, ps_fos_type_cd. Because it is built on an _ALL table, it carries the ORG_ID column, making the view multi-org aware and suitable for operating-unit-scoped reporting. Note that although the view is documented as a companion to the transactional field-of-study entity, the ETRM record for this object lists no separately documented base objects, so the view text itself remains the authoritative definition.

Underlying Base Objects

Two objects are referenced in the view definition:

• IGS_PS_FLD_STDY_HIST_ALL — the driving base table holding the historical field-of-study rows. Columns such as FIELD_OF_STUDY, HIST_START_DT, HIST_END_DT, HIST_WHO, GOVT_FIELD_OF_STUDY, CLOSED_IND, and the standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) originate here, along with ORG_ID and the table's ROWID.
• IGS_LOOKUP_VALUES — the lookup table supplying the MEANING value exposed as FOS_TYPE_CODE_DESC. The join is an inner equality on lookup_code = fos_type_code filtered to lookup_type = 'PS_FOS_TYPE_CD'. This is why records whose FOS_TYPE_CODE has no active row in that lookup type will not surface through the view.

Key Columns

• ROW_ID — the ROWID of the underlying history row, preserving unique row identity for downstream processing.
• FOS_TYPE_CODE — the coded field-of-study type, the column matched to the lookup code.
• FOS_TYPE_CODE_DESC — the decoded meaning from IGS_LOOKUP_VALUES, providing a readable type description.
• FIELD_OF_STUDY — the field-of-study value or code being versioned.
• HIST_START_DT / HIST_END_DT — the effective dating window for the historical record; HIST_END_DT typically being null for the current version.
• HIST_WHO — the actor or source associated with the historical change.
• GOVT_FIELD_OF_STUDY — the government-defined classification, typically used for regulatory reporting.
• CLOSED_IND — indicates whether the historical field-of-study record is closed.
• ORG_ID — the operating unit, enabling multi-org filtering.

Common Use Cases and Queries

The view is most often used for point-in-time reporting of field-of-study history, auditing changes, and extracting records for regulatory or interface feeds. A typical query confirming the decoded type and restricting to a single operating unit is:

• SELECT fos_type_code, fos_type_code_desc, field_of_study, hist_start_dt, hist_end_dt, closed_ind FROM apps.igs_ps_fld_stdy_hist WHERE org_id = :p_org_id AND TRUNC(hist_start_dt) >= :p_from_date ORDER BY field_of_study, hist_start_dt;
• To list distinct field-of-study types with descriptions: SELECT DISTINCT fos_type_code, fos_type_code_desc FROM apps.igs_ps_fld_stdy_hist ORDER BY 1;
• To retrieve the current historical version: filter on hist_end_dt IS NULL together with the desired field_of_study and fos_type_code.

Because the lookup join is an inner join on the PS_FOS_TYPE_CD lookup type, validation queries that return no rows frequently indicate either a missing or end-dated lookup entry rather than absent history data — a point to verify before concluding that a field-of-study record does not exist.