Search Results derived_completion_yr




Overview
Explore in AI Chat

Overview

IGS_AS_SCAH_EFFECTIVE_H_V is a reporting view in the Oracle E-Business Suite Student Systems (IGS) product family, owned by the APPS schema. It presents the effective, or most current, historic student course attempt records for students enrolled in courses tracked by the Student Course Attempts History model. The view abstracts a complex set of underlying history tables and exposes a flattened, denormalised projection suitable for reporting, integration, and downstream analytical queries.

The suffix _V indicates a view, and _H_ denotes that it is built on historic (versioned) data. The view is therefore intended to return the effective historical row per student/course combination rather than every versioned change. The name SCAH refers to Student Course Attempt History, and EFFECTIVE indicates that the view applies effective-dating logic so consumers see only the currently applicable historical record.

Because Oracle EBS 12.1.1 and 12.2.2 share the same IGS schema lineage, this view exists in both releases. The view is chiefly consumed by institutional reporting, student progression analysis, and external integrations that require a denormalised snapshot of course attempt status and derived attributes.

Underlying Base Objects

The metadata documents no explicit referenced base objects, which is expected for a view of this complexity. The SELECT text shows that the view derives its rows through a set of PL/SQL functions packaged in IGS_EN_GET_SCAEH_DTL. Rather than joining directly to history tables, the view calls functions such as enrp_get_scaeh_eff_st, enrp_get_scaeh_eff_end, and enrp_get_scaeh_col to retrieve effective start and end dates, and column values respectively, for a given person, course, history start date, and course attempt status combination.

Implicitly, these functions operate against the underlying Student Course Attempt History entity and its associated history detail tables within the IGS schema. The UNIQUE keyword in the SELECT ensures that duplicate rows arising from the underlying history structure are collapsed into a single effective record per person/course combination.

Key Columns

The user search term derived_completion_yr is not among the columns explicitly exposed in the documented SELECT text. Derived completion year is typically obtained through related IGS derived-attribute functions rather than directly from this view. Where a completion year is required, it is usually resolved via an additional lookup against the derived attribute columns supplied by the IGS_EN_GET_SCAEH_DTL family. The view exposes other derived_ prefixed columns such as DERIVED_ATT_TYPE and DERIVED_ATT_MODE, which confirms the convention of prefixing computed attributes with DERIVED_.

Common Use Cases and Queries

Typical uses include producing student course attempt extracts, feeding data warehouses, and validating effective history for a student. A representative query listing effective attempts follows:

SELECT person_id, course_cd, course_attempt_status, commencement_dt
FROM apps.igs_as_scah_effective_h_v
WHERE person_id = :p_person_id;

To filter by attendance attributes:

SELECT person_id, course_cd, derived_att_mode, derived_att_type
FROM apps.igs_as_scah_effective_h_v
WHERE course_cd = :p_course_cd;

Because the view invokes PL/SQL functions per row, filtering by person_id or course_cd is strongly recommended to limit the volume of function evaluations and improve response time. Where derived completion year is required, join the returned rows to the appropriate IGS derived-attribute view or call the corresponding IGS_EN_GET_SCAEH_DTL function directly.