Search Results unit_set_cd_display_lvl

Overview

IGS_AS_SUSA_V is a hierarchical (tree-walking) view in the Oracle E-Business Suite student systems schema (APPS), belonging to the IGS (Student Systems) product family. The name suggests "Student Unit Set Attempt – Structure/Summary Alternate View," and its function is to flatten a self-referencing parent/child structure of unit set attempts into a single, indented, level-ordered result set. The view is defined over the base table IGS_AS_SU_SETATMPT, which stores unit set attempt records for a student's course attempt, including an optional pointer (parent_unit_set_cd) that links each child unit set attempt to its parent. Because the base table is a recursive structure, straightforward SQL queries against it cannot easily present the natural hierarchy in a report-friendly layout. IGS_AS_SUSA_V solves that problem by applying the Oracle CONNECT BY hierarchical query mechanism, producing one row per unit set attempt ordered by level within the tree.

In reporting and integration terms, this view behaves as a denormalized, read-only projection: consumers do not need to write their own CONNECT BY against IGS_AS_SU_SETATMPT; they can select from the view and receive the same ordered hierarchy. This is particularly valuable for extracts, BI Publisher data templates, and downstream integration payloads that must preserve the parent/child relationships of unit set attempts.

Underlying Base Objects

ETRM metadata documents no referenced base objects beyond the view definition itself. However, the view text clearly identifies IGS_AS_SU_SETATMPT as the sole base table. The recursive relationship is defined as follows: the root rows are those where parent_unit_set_cd IS NULL (top-level unit set attempts). The CONNECT BY clause then joins each child to its parent using the combined key:

• PRIOR person_id = person_id
• PRIOR course_cd = course_cd
• PRIOR unit_set_cd = parent_unit_set_cd
• PRIOR sequence_number = parent_sequence_number

This means the hierarchy is scoped to a student (person_id) and course (course_cd), and within that scope children are matched to their parent's unit_set_cd plus sequence_number. The root filter excludes rows that have a parent, ensuring the tree is seeded only at true top-level nodes. The metadata lists no additional joins, so the view is a pure single-table hierarchy transformation with no lookup or descriptive joins.

Key Columns

• person_id — Identifies the student (person) whose unit set attempts are being traversed. Forms part of the hierarchy join key.
• course_cd — The course attempt code; scopes the tree to a specific course. Also part of the join key.
• unit_set_cd — The unit set code for each row. This is the column matched against the parent's parent_unit_set_cd in the CONNECT BY.
• parent_unit_set_cd — The parent pointer; not exposed as a projected column but drives the START WITH seed and the CONNECT BY condition.
• sequence_number — Ordering/sequence for the attempt; part of the parent/child matching via parent_sequence_number.
• us_version_number — Version identifier of the unit set attempt record.
• SUBSTR(LPAD(' ',2*(LEVEL-1)) || unit_set_cd,1,40) — An indentation column: LEVEL (the pseudo-column supplied by CONNECT BY) multiplied by two spaces, prefixed to unit_set_cd, truncated to 40 characters. This visually renders the hierarchy depth for reports.
• end_dt — End date of the attempt record, used to determine whether the attempt is still active.
• created_by, creation_date, last_updated_by, last_update_date, last_update_login — Standard EBS audit (WHO) columns carried through from the base table.

The presence of the LEVEL pseudo-column means consumers can also sort or filter by depth even though the formatted indentation column masks it.

Common Use Cases and Queries

The view is typically used when a report or interface must present unit set attempts in their natural parent/child order, for example on a student course attempt summary, a unit set structure listing, or a qualification planning extract. A representative query is:

SELECT person_id,
       course_cd,
       unit_set_cd,
       sequence_number,
       us_version_number,
       end_dt
FROM   apps.igs_as_susa_v
WHERE  person_id  = :p_person_id
AND    course_cd  = :p_course_cd
ORDER BY person_id, course_cd, unit_set_cd;

Because the view already includes a CONNECT BY traversal, the rows arrive in hierarchical order (depth-first), and the formatted indentation column can be selected directly to reproduce that structure in a fixed-width report. Callers should supply the PERSON_ID and COURSE_CD predicates to constrain the hierarchy to a single student/course tree, both to avoid pulling the entire table and to keep the CONNECT BY evaluation efficient. Filtering on end_dt is common to restrict output to current attempts. The view does not expose parent_unit_set_cd itself, so if a query needs the raw parent pointer it must be obtained from IGS_AS_SU_SETATMPT or by pairing the view with the base table. As with all IGS views, APPS grants and the standard EBS security of the calling responsibility govern access. No additional documented base objects are involved, so the view introduces no hidden join overhead beyond the recursive scan of IGS_AS_SU_SETATMPT.