Search Results usxgm_chld

Overview

APPS.IGS_PS_USEC_X_USEC_V is a reporting and integration view within the Oracle E-Business Suite Student System (Oracle Student System / OSS) product family, built on the IGS_PS (Programs and Student Units) schema. It exposes the parent–child relationships that exist between units of study when a cross-unit-sec linkage has been defined. Specifically, the view flattens the self-referencing structure held in the unit-section cross-reference group membership table, returning the parent unit offering option alongside each of its child unit offering options.

The name encodes the business concept: a "USEC" (unit section) crossing another "USEC." When an institution lists a set of units together — for example a co-requisite bundle or a parent unit that spans several child units — the membership rows capture that grouping. This view filters the group membership to the two roles that matter for reporting: the row flagged as the parent (parent = 'Y') and the rows flagged as children (parent = 'N'), joined on their shared group identifier. Because it resolves unit codes, titles, calendar types, locations, and lifecycle audit columns in a single pass, it is well suited to operational reporting, data extracts, and interface feeds that must move parent/child unit structures into downstream systems.

Underlying Base Objects

The view is defined over seven documented base objects, all in the IGS schema:

• IGS_PS_USEC_X_GRPMEM (USXGM) — the parent alias, filtered to parent = 'Y'.
• IGS_PS_USEC_X_GRPMEM (USXGM_CHLD) — the child alias, filtered to parent = 'N', and the source of the ROWID and surrogate key.
• IGS_PS_UNIT_OFR_OPT (UOO) — the unit offering option, supplying unit code, version, calendar type, sequence number, unit class, and location.
• IGS_PS_UNIT_VER (UV) — the unit version, joined on unit code and version number to supply the descriptive title.
• IGS_CA_INST (CI) — the calendar instance, joined on calendar type and sequence number for alternate code and start/end dates.
• IGS_AS_UNIT_CLASS (UC) — the unit class, supplying the unit mode.
• IGS_AD_LOCATION (LOC) — the location, supplying the location description.

The parent and child aliases are joined on usec_x_listed_group_id, and the relationship to the offering option is driven through uoo_id, which is the primary linking key throughout.

Key Columns

• ROW_ID — rowid of the child group member; a unique handle for the extracted row.
• UNIT_SEC_CROSS_UNIT_SEC_ID — the child grouping record identifier (USEC_X_LISTED_GROUP_MEM_ID).
• PARENT_UOO_ID / CHILD_UOO_ID — the offering option identifiers for the parent and child units, the core traversal keys.
• CHILD_UNIT_CD, CHILD_VERSION_NUMBER, CHILD_TITLE — the child unit's identity and descriptive title.
• CHILD_CAL_TYPE, CHILD_CI_SEQUENCE_NUMBER, CHILD_ALTERNATE_CODE, START_DT, END_DT — the calendar context and validity window of the child unit's offering.
• CHILD_UNIT_CLASS, CHILD_UNIT_MODE — classification and delivery mode attributes of the child unit.
• CHILD_LOCATION_CD, CHILD_LOCATION_DESCRIPTION — where the child unit is offered.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns carried from the child membership record.

Common Use Cases and Queries

Typical usage includes reconstructing parent/child unit hierarchies for catalogs and advising reports, validating co-requisite or bundled offerings, and feeding external scheduling or enrollment systems. Because the parent offering is a single value and children repeat, the view is naturally one-to-many on the parent.

A representative query retrieves all children of a given parent unit offering:

• SELECT child_unit_cd, child_version_number, child_title, child_cal_type, child_alternate_code, child_location_description FROM apps.igs_ps_usec_x_usec_v WHERE parent_uoo_id = :p_uoo_id;

To locate a parent by child unit, invert the predicate:

• SELECT parent_uoo_id, child_unit_cd, child_title FROM apps.igs_ps_usec_x_usec_v WHERE child_unit_cd = :p_unit_cd AND child_cal_type = :p_cal_type;

For change-detection extracts, filter on the audit column, e.g. WHERE last_update_date >= :p_since, exposing only memberships modified since the last interface run. Restricting by child_unit_class or child_unit_mode further narrows the result for mode-specific reporting.