Search Results child_alternate_code

Overview

IGS_PS_USEC_X_USEC_V is a seeded Oracle E-Business Suite view owned by the APPS schema and delivered with the IGS (Student System) product family. It exposes cross-unit (parent-child) relationships that are configured between scheduled unit offerings in an institution's curriculum structure. Specifically, the view flattens the hierarchical membership stored in the cross-unit listed group tables so that a report or integration can join a parent unit offering directly to each of its child unit offerings in a single row.

The view is read-only by convention and is intended for reporting, data extraction, and interface development rather than for transactional maintenance. Because it is defined in the APPS schema against the base Student System tables, it is accessible to any responsibility that has been granted the standard IGS read privileges. The view carries a CHILD_ALTERNATE_CODE column, which is frequently referenced in reporting requirements that need the institution-defined alternate identifier for the child unit occurrence rather than the internal unit code.

Underlying Base Objects

The view is defined over seven base objects, joined on their natural keys:

• IGS_PS_USEC_X_GRPMEM — referenced twice, once as USXGM for the parent row (PARENT = 'Y') and once as USXGM_CHLD for the child row (PARENT = 'N'). These two aliases are matched on USEC_X_LISTED_GROUP_ID, which forms the core of the parent-child link.
• IGS_PS_UNIT_OFR_OPT (UOO) — the scheduled unit offering option; supplies unit code, version, calendar type, class, location, and CI sequence number.
• IGS_PS_UNIT_VER (UV) — the unit version master, joined on UNIT_CD and VERSION_NUMBER to supply the child title.
• IGS_CA_INST (CI) — the calendar instance, joined on CAL_TYPE and SEQUENCE_NUMBER, supplying ALTERNATE_CODE, START_DT, and END_DT.
• IGS_AS_UNIT_CLASS (UC) — the unit class definition, supplying UNIT_MODE.
• IGS_AD_LOCATION (LOC) — the location master, supplying the location description.

The metadata does not document these objects as separately registered dependencies, but the view text shows them explicitly.

Key Columns

• ROW_ID — the ROWID of the child group member row; useful as a surrogate key.
• UNIT_SEC_CROSS_UNIT_SEC_ID — the group member identifier for the child cross-unit record.
• PARENT_UOO_ID / CHILD_UOO_ID — the offering identifiers for the parent and child scheduled units.
• CHILD_UNIT_CD, CHILD_VERSION_NUMBER, CHILD_TITLE — identify and describe the child unit.
• CHILD_ALTERNATE_CODE — the institution-defined alternate code of the child calendar instance (from IGS_CA_INST.ALTERNATE_CODE). This is the column most often cited in cross-unit reporting that must display a business-friendly identifier.
• CHILD_CAL_TYPE, START_DT, END_DT, CHILD_CI_SEQUENCE_NUMBER — the child offering's teaching period and dates.
• CHILD_UNIT_CLASS, CHILD_UNIT_MODE, CHILD_LOCATION_CD, CHILD_LOCATION_DESCRIPTION — delivery attributes of the child unit.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns from the child group member record.

Common Use Cases and Queries

Typical uses include validating cross-unit configuration before teaching period rollover, reporting which units are delivered together as cross-unit pairs, and feeding downstream scheduling or room-booking extracts.

SELECT child_unit_cd,
       child_alternate_code,
       child_title,
       start_dt,
       end_dt,
       child_location_cd
FROM   apps.igs_ps_usec_x_usec_v
WHERE  child_cal_type = :p_cal_type
AND    child_ci_sequence_number = :p_seq
ORDER  BY child_unit_cd;

A second common pattern lists all children of a known parent offering:

SELECT child_uoo_id,
       child_unit_cd,
       child_alternate_code
FROM   apps.igs_ps_usec_x_usec_v
WHERE  parent_uoo_id = :p_uoo_id;

Because the view performs no filtering beyond the PARENT flag, additional WHERE predicates on calendar type, location, or dates are normally required to constrain result sets in production reporting.