Search Results relation_start_date

Overview

The IGS_AZ_ADVISING_RELS_V view belongs to the IGS (Student System) product family, an obsolete module within the Oracle E-Business Suite 12.1.1 and 12.2.2 footprints. It is a denormalized reporting view that presents advisor-to-student relationship records together with the display names and party numbers of both the student and the advisor. Its stated purpose is to support the advising functionality by exposing a single, queryable structure that combines the advising relationship itself with the identifying attributes of the two parties involved.

Because the view flattens data that would otherwise require four-way joins across the advising relationship, two registry tables, and the Trading Community Architecture (TCA) party table, it is intended primarily for reporting, inquiry screens, and lightweight integrations. Consumers do not need to resolve the party identifiers themselves, since the concatenated person names are returned directly. Note that the ETRM metadata records this object as "Not implemented in this database," and no owner is documented, indicating the view may not exist in every environment and should be validated at runtime before being referenced in custom code.

Underlying Base Objects

The documented view text defines the view over four base objects joined through composite keys:

• IGS_AZ_ADVISING_RELS (alias AZREL) — the driving relationship table holding GROUP_NAME, START_DATE, END_DATE, GROUP_ADVISING_REL_ID, GROUP_ADVISOR_ID, GROUP_STUDENT_ID, and the standard WHO audit columns.
• IGS_AZ_STUDENTS (alias AZSTU) — resolves the student side of the relationship and supplies STUDENT_PERSON_ID.
• IGS_AZ_ADVISORS (alias AZADV) — resolves the advisor side of the relationship and supplies ADVISOR_PERSON_ID.
• HZ_PARTIES (aliases HP1 and HP2) — referenced twice to derive the student name/number and the advisor name/number from the TCA party model.

The joins are keyed on GROUP_STUDENT_ID plus GROUP_NAME and GROUP_ADVISOR_ID plus GROUP_NAME, so the GROUP_NAME column acts as a partitioning attribute that must match across the relationship, student, and advisor records. The two HZ_PARTIES instances are then joined on STUDENT_PERSON_ID = HP1.PARTY_ID and ADVISOR_PERSON_ID = HP2.PARTY_ID respectively. The view exposes AZREL.ROWID as its first column, which is a characteristic pattern for Oracle Forms-based modules.

Key Columns

• ROW_ID — the row identifier of the underlying IGS_AZ_ADVISING_RELS record.
• STUDENT_NAME / ADVISOR_NAME — concatenations of PERSON_LAST_NAME, PERSON_FIRST_NAME, and PERSON_MIDDLE_NAME. The presence of PERSON_MIDDLE_NAME in these expressions is directly relevant to searches involving person_middle_name; the middle name is embedded in the display value rather than exposed as a separate column.
• STUDENT_NUMBER / ADVISOR_NUMBER — the HZ_PARTIES.PARTY_NUMBER values for each party.
• RELATION_START_DATE / RELATION_END_DATE — the effective period of the advising relationship, useful for active/inactive filtering.
• ADVISING_REL_ID, GROUP_ADVISOR_ID, GROUP_STUDENT_ID — the surrogate keys linking back to the base relationship records.
• STUDENT_PERSON_ID / ADVISOR_PERSON_ID — the TCA party identifiers, allowing joins to other HZ_PARTIES-based views.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns inherited from the base table.

Common Use Cases and Queries

Typical usage includes advising rosters, caseload reports, and historical relationship audits. Because middle names are concatenated, users searching on person_middle_name will only find matches embedded within the STUDENT_NAME or ADVISOR_NAME strings, and should use pattern matching rather than equality:

• Current advisor caseload by group:

SELECT GROUP_NAME, ADVISOR_NAME, STUDENT_NAME, RELATION_START_DATE, RELATION_END_DATE FROM IGS_AZ_ADVISING_RELS_V WHERE SYSDATE BETWEEN RELATION_START_DATE AND NVL(RELATION_END_DATE, SYSDATE);

• Locating a relationship by a middle name fragment:

SELECT STUDENT_NAME, ADVISOR_NAME, RELATION_START_DATE FROM IGS_AZ_ADVISING_RELS_V WHERE UPPER(ADVISOR_NAME) LIKE '%'||UPPER(:middle_name)||'%' OR UPPER(STUDENT_NAME) LIKE '%'||UPPER(:middle_name)||'%';

Given the "Obsolete" classification and the "Not implemented" note, any dependency on this view should be confirmed against the target instance before deployment.