Results for “igs_as_sca_h_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_AS_SCA_H_V is a read-only database view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. The object carries a VALID status on both 12.1.1 and 12.2.2 application tiers and is registered in the E-Business Suite Technical Reference Manual (ETRM) as a reporting and integration artifact rather than a transactional entity.

The view presents a merged, time-aware picture of student course attempt data. It combines historical student course attempt records stored in the history table with the corresponding current student course attempt record, so that every column value is exposed for any point along the attempt's lifecycle up to and including the present day. Each row in the result set represents a complete set of column values for a defined time period, bounded by HIST_START_DT and HIST_END_DT. This design allows downstream reports, extracts, and interfaces to query a single object instead of manually joining the historical and current tables and reconciling nulls across time slices.

Underlying Base Objects

The documented view text references the following objects:

• SCAH1 — the aliased student course attempt history source supplying historical column values, version numbers, and the HIST_START_DT, HIST_END_DT and HIST_WHO audit columns.
• SCA1 — the aliased current student course attempt source, used as the fallback when no historical value exists.
• IGS_AU_GEN_003 — an IGS audit/generic package whose function AUDP_GET_SCAH_COL retrieves a named column value for a given person, course and effective date, using the IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COLUMN_NAME', PERSON_ID, COURSE_CD, HIST_END_DT) pattern.
• IGS_GE_NUMBER.TO_NUM — a generic conversion utility applied to VERSION_NUMBER.
• IGS_GE_DATE.IGSDATE — a date conversion utility applied to COMMENCEMENT_DT and OVERRIDE_CMPL_DT.

Although the ETRM metadata does not list explicit base tables, the naming and aliasing convention implies IGS_AS_SCA_H (history) and IGS_AS_SCA (current) as the physical sources. The view therefore depends on the IGS_AU_GEN_003 package for the historical lookup logic, which explains why a search for that package returns this view as a documented dependency.

Key Columns

• PERSON_ID, COURSE_CD — the composite identifier for a student course attempt.
• HIST_START_DT, HIST_END_DT — the effective date boundaries of each historical time slice.
• HIST_WHO — the user or process that created the historical record.
• VERSION_NUMBER — resolved from history, then the audit package, then the current record.
• CAL_TYPE, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE — truncated to ten characters and resolved through the same fallback chain.
• STUDENT_CONFIRMED_IND, MANUAL_OVR_CMPL_DT_IND — single-character indicator columns.
• COMMENCEMENT_DT, OVERRIDE_CMPL_DT — date columns converted via IGS_GE_DATE.IGSDATE.

Common Use Cases and Queries

Typical consumers include point-in-time student record extracts, admissions and progression reporting, and integration feeds that require a frozen view of attempt attributes as they stood on a given date.

SELECT person_id, course_cd, hist_start_dt, hist_end_dt,
       version_number, cal_type, location_cd
FROM   apps.igs_as_sca_h_v
WHERE  person_id = :p_person_id
AND    course_cd = :p_course_cd
ORDER BY hist_end_dt;

SELECT person_id, course_cd, hist_end_dt, attendance_mode
FROM   apps.igs_as_sca_h_v
WHERE  hist_end_dt BETWEEN :p_from_date AND :p_to_date;

Because the view invokes IGS_AU_GEN_003 and IGS_GE_DATE PL/SQL functions per row, performance-sensitive queries should be filtered by PERSON_ID or date range before joining to other IGS objects.