Results for “igs_az_advisors”

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

Overview

The IGS_AZ_ADVISORS table belongs to the IGS schema (Student System product) and stores the details of all advisors associated with the various advising groups maintained in Oracle E-Business Suite. In Oracle EBS 12.1.1 and 12.2.2, this table functions as the authoritative registry of advisor-to-group assignments, capturing which person serves as an advisor for a given advising group, the effective period of that assignment, capacity constraints, and notification state. Its documented status is VALID, and it is owned by the IGS schema.

From a Data Vault modeling perspective, the mined FK structure classifies this object heuristically as a standalone entity. This suggests it may be modeled as a hub-like or reference-style table centered on its surrogate key, with no upward foreign-key dependencies on other IGS tables within the documented schema. The table is instead referenced by child tables, which is consistent with a source-of-truth registry for advisor/group associations.

Key Information Stored

The physical schema documents 14 columns in ETRM 12.1.1. The most significant are:

• GROUP_ADVISOR_ID — the surrogate primary key (IGS_AZ_ADVISORS_PK) uniquely identifying each advisor/group assignment record.
• ADVISOR_PERSON_ID — the person identifier of the advisor, linking to the person/party model.
• GROUP_NAME — the advising group to which the advisor is attached.
• START_DATE and END_DATE — the effective dating window for the advisor's membership in the group.
• MAX_STUDENTS_NUM — the maximum number of students the advisor may serve within the group.
• NOTIFIED_DATE — the date the advisor was notified of the assignment.
• ACCEPT_ADD_FLAG and ACCEPT_DELETE_FLAG — flags controlling whether the advisor accepts additions or deletions of students.
• Audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.

The business-key candidate is defined by the unique index IGS_AZ_ADVISORS_U1 over (ADVISOR_PERSON_ID, GROUP_NAME, START_DATE), which enforces that a given advisor is associated with a group only once per start date. This distinguishes the surrogate key from the business key.

Common Use Cases and Queries

Typical reporting needs include identifying active advisors for a group as of a given date, listing advisors approaching capacity, and auditing unaccepted assignments.

• Active advisors for a group:

  SELECT advisor_person_id, group_name, start_date, end_date
  FROM   igs.igs_az_advisors
  WHERE  group_name = :group_name
  AND    TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, TRUNC(SYSDATE));

• Capacity check — advisors at or over their student limit:

  SELECT group_advisor_id, advisor_person_id, max_students_num
  FROM   igs.igs_az_advisors
  WHERE  max_students_num IS NOT NULL;

• Pending notifications or unaccepted additions (where ACCEPT_ADD_FLAG or NOTIFIED_DATE indicate outstanding action).
• Joining to the child relationship table to view full advisor-to-student/group relationships.

Related Objects

The documented FK relationship identifies the primary dependent object: IGS_AZ_ADVISING_RELS, which references IGS_AZ_ADVISORS via GROUP_ADVISOR_ID. Queries joining these two objects by GROUP_ADVISOR_ID return the advisor registry record alongside its advising relationship rows. Advisor person identifiers align with the person/party model used broadly across the IGS Student System. Because the object is classified as standalone, no parent-table foreign keys are documented within the mined FK structure; dependencies flow outward to child tables such as IGS_AZ_ADVISING_RELS, making IGS_AZ_ADVISORS a foundational reference point for advising-group reporting and validation in Oracle EBS 12.1.1 and 12.2.2.