Results for “group_advisor_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_AZ_ADVISORS_V is a read-only database view owned by the APPS schema within the Oracle E-Business Suite Student System (IGS) product family. It presents advisor information used by the advising functionality, consolidating advisor-to-group assignments, advisor personal details, and advisor group definitions into a single denormalized result set. The view carries a VALID status in the ETRM metadata for both 12.1.1 and 12.2.2.

Because advisor records span several normalized tables — assignment details, group definitions, party records, and person identifiers — the view abstracts the required join logic so that forms, concurrent programs, and reporting tools can retrieve a complete advisor profile without reconstructing relationships manually. It is typically consumed for operational lookups (for example, determining which advisors are assigned to a group) and for integration extracts where a flat advisor projection is required.

Underlying Base Objects

The view text joins four documented base objects:

• IGS_AZ_ADVISORS AZA — the driving table, holding advisor assignments: advisor person identifier, associated group name, assignment start and end dates, maximum student load, notification date, and add/delete acceptance flags.
• IGS_AZ_GROUPS AG — advisor group definitions, providing GROUP_DESC, ADVISOR_GROUP_ID, and DELIVERY_METHOD_CODE. Joined to IGS_AZ_ADVISORS on GROUP_NAME.
• HZ_PARTIES HZ — the trading community party record supplying PARTY_NUMBER and the person name components. Joined on ADVISOR_PERSON_ID = PARTY_ID.
• IGS_PE_PERSID_GROUP_ALL PG — person identifier group definitions, supplying GROUP_CD and DESCRIPTION. This is an outer join (PG.GROUP_ID(+) = AG.ADVISOR_GROUP_ID), so advisor groups without a matching identifier group still return rows.

The view also exposes AZA.ROWID as ROW_ID, enabling row-level addressing of the underlying advisor assignment for update-capable consumers.

Key Columns

• ADVISOR_PERSON_ID — the party identifier of the advisor. This is the column most commonly searched, and it is the primary link between the advising tables and HZ_PARTIES.
• ADVISOR_PERSON_NUMBER — sourced from HZ.PARTY_NUMBER; the external party number for the advisor, useful when the internal party ID is not known.
• ADVISOR_FULL_NAME — a concatenation of last name, first name, and middle name (with a space inserted before a non-null middle name) producing a display-ready advisor name.
• GROUP_ADVISOR_ID / GROUP_NAME / GROUP_DESC — the advisor assignment identity and its associated group.
• ADVISOR_GROUP_ID / ADVISOR_GROUP_CD / ADVISOR_GROUP_DESC — the group definition and person identifier group code and description.
• START_DATE / END_DATE — the effective period of the advisor assignment.
• MAX_STUDENTS_NUM / NOTIFIED_DATE / ACCEPT_ADD_FLAG / ACCEPT_DELETE_FLAG — advising workload and workflow attributes.
• DELIVERY_METHOD_CODE — the delivery method associated with the advisor group.
• ROW_ID and the audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) follow standard EBS conventions.

Common Use Cases and Queries

Typical uses include advisor lookup by party, reporting on active advisor assignments, and validating group membership during integration loads.

• Resolve an advisor's group and display name from a known party identifier:

SELECT advisor_person_id,
       advisor_person_number,
       advisor_full_name,
       group_name,
       group_desc,
       start_date,
       end_date
FROM   apps.igs_az_advisors_v
WHERE  advisor_person_id = :p_advisor_person_id;

• List all advisors for a given group, ordered by name:

SELECT advisor_person_id, advisor_full_name, max_students_num
FROM   apps.igs_az_advisors_v
WHERE  group_name = :p_group_name
ORDER  BY advisor_full_name;

• Return advisors with open-ended (current) assignments:

SELECT advisor_person_id, advisor_full_name, advisor_group_cd
FROM   apps.igs_az_advisors_v
WHERE  (end_date IS NULL OR end_date >= TRUNC(SYSDATE))
  AND  start_date <= TRUNC(SYSDATE);

Because advisor personal details derive from HZ_PARTIES, queries should treat the name and number columns as party-sourced attributes subject to party data changes. The outer join to IGS_PE_PERSID_GROUP_ALL means ADVISOR_GROUP_CD and ADVISOR_GROUP_DESC may be null for groups lacking a person identifier group definition, and predicates on those columns should account for that behavior.