Search Results audp_get_susah_col

Overview

IGS_AS_SUSAH_V is a seeded Oracle E-Business Suite database view owned by the APPS schema within the IGS (Student System) product family. Its purpose is to merge student unit set attempt history records with the corresponding current student unit set attempt details, so that a complete picture of a student's unit set attempt data is available continuously over time, up to and including the present day. Each row returned by the view represents the full set of column values for a given unit set attempt as they stood at a defined point in time, reconciled against the live record where a historical value does not exist.

The view is therefore a temporal reconciliation layer rather than a transactional entry point. It exists to support reporting and integration scenarios where consumers need to see historical unit set attempt states without separately joining history and current tables or manually applying fallback logic. This is particularly relevant in EBS 12.1.1 and 12.2.2, where multi-version and history-tracked student records are common in the Student System module.

Underlying Base Objects

ETRM metadata for the 12.2.2 environment does not enumerate referenced base objects for this view. The documented view text, however, shows that IGS_AS_SUSAH_V is defined over at least the following sources:

• SUSAH1 — an alias for the student unit set attempt history table (the IGS_AS_SUSAH family), providing the historical column values and effective-dating columns HIST_START_DT, HIST_END_DT and HIST_WHO.
• SUSA1 — an alias for the current student unit set attempt table (the IGS_AS_SUSA family), supplying the present-day values used as the fallback in each NVL expression.
• IGS_AU_GEN_003.AUDP_GET_SUSAH_COL — a PL/SQL function that retrieves a named column value for a specific person, course, unit set and sequence number at a given history end date. It is used where the history row does not carry the value directly.
• IGS_GE_DATE.IGSDATE — a date conversion function applied to date-typed columns such as SELECTION_DT and END_DT.

The view joins history and current rows on the key combination of PERSON_ID, COURSE_CD, UNIT_SET_CD and SEQUENCE_NUMBER, and applies NVL logic so that history values take precedence, with current-table values used only when history is null. This layered approach means the view does not simply concatenate two tables; it produces one coherent version of each attempt row per historical boundary, with the current row filling the most recent state.

Key Columns

The view exposes identity and descriptive columns carried directly from the history table, plus reconciled attribute columns derived through the NVL patterns described above.

• PERSON_ID, COURSE_CD, UNIT_SET_CD, SEQUENCE_NUMBER — the composite identifiers that uniquely describe a student's unit set attempt and link history to current data.
• HIST_START_DT, HIST_END_DT, HIST_WHO — the effective-dating and audit columns that define the period during which the row's values were in force and who made the change.
• US_VERSION_NUMBER — the unit set version, resolved from history, then the AUDP_GET_SUSAH_COL function, then the current table.
• SELECTION_DT and END_DT — date attributes resolved through the same fallback chain, with IGSDATE applied for conversion.
• STUDENT_CONFIRMED_IND — the student confirmation flag, truncated to a single character when read via the function.
• PARENT_UNIT_SET_CD and PARENT_SEQUENCE_NUMBER — the parent unit set reference, supporting hierarchical unit set structures.
• PRIMARY_SET_IND — indicates whether the unit set is the student's primary set.

Common Use Cases and Queries

The view is typically consumed in point-in-time reporting, audit or reconciliation extracts, and integration interfaces that must reflect historical unit set attempt state. A common pattern is to filter by student and course to review the progression of attempts over time:

• SELECT person_id, course_cd, unit_set_cd, sequence_number, hist_start_dt, hist_end_dt, us_version_number, end_dt FROM igs_as_susah_v WHERE person_id = :p_person_id AND course_cd = :p_course_cd ORDER BY unit_set_cd, sequence_number, hist_start_dt;
• SELECT * FROM igs_as_susah_v WHERE hist_end_dt IS NULL OR hist_end_dt >= TRUNC(SYSDATE) — isolate rows current as of today.
• SELECT unit_set_cd, sequence_number, parent_unit_set_cd, parent_sequence_number, primary_set_ind FROM igs_as_susah_v WHERE person_id = :p_person_id AND primary_set_ind = 'Y' — identify a student's primary unit sets.
• Joining the view to student or course reference tables on PERSON_ID and COURSE_CD for reporting extracts that require descriptive attributes alongside temporal attempt data.

Because the resolved values depend on a PL/SQL function and a date conversion utility, queries against this view are best scoped by PERSON_ID or COURSE_CD to limit row counts and function invocations. Note that the user search term "catalog_seq_num" is not a documented column of this view; catalog sequence references are handled in related IGS catalog structures rather than in IGS_AS_SUSAH_V.