Results for “student_person_id”

44 results




Overview
Explore in AI Chat

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

Overview

The IGS_SC_ADVISOR_PERSON_SV view is a Student System (IGS) security and relationship view owned by the APPS schema in Oracle E-Business Suite. Its purpose is to resolve the association between advisors and the students they advise, returning a simple two-column result set of ADVISOR_PERSON_ID and STUDENT_PERSON_ID pairs. The view is a snapshot-style (_SV) security view, meaning it is typically consumed by Oracle EBS row-level security (RLS/VPD) constructs or by application logic that must determine, for a given logged-in user, which student records that advisor is entitled to see or act upon.

In the wider IGS architecture, advising relationships can be established in more than one way — through pairwise advisor-to-student relationships, or through group-based delivery methods. The view consolidates these disparate establishment paths into a single canonical list of advisor/student pairs, which makes it valuable for reporting, for personalization, and for feeding downstream security predicates rather than requiring each consumer to re-implement the same multi-table join logic.

Underlying Base Objects

Although the documented base object list is not enumerated in the ETRM extract, the view text itself reveals the four tables on which it depends:

The view is a UNION ALL of two branches. The first branch resolves 1-on-1 advising, joining all four tables and requiring AVG.DELIVERY_METHOD_CODE = '1_ON_1'. The second branch resolves centre-based advising, joining IGS_AZ_STUDENTS, IGS_AZ_ADVISORS, and IGS_AZ_GROUPS and requiring AVG.DELIVERY_METHOD_CODE = 'CENTER'. Both branches apply SYSDATE-based effective dating so that only currently valid relationships are returned; end dates are defaulted with NVL to 31-12-4712, the standard EBS open-ended sentinel.

Key Columns

  • ADVISOR_PERSON_ID — the party/person identifier of the advisor. This is derived from AVA.ADVISOR_PERSON_ID and is the primary access-control key for advisor-facing inquiries.
  • STUDENT_PERSON_ID — the party/person identifier of the advised student, derived from AVS.STUDENT_PERSON_ID. This is the value typically compared against a student record to determine visibility.

Because both columns are person identifiers, the view can be joined directly to HZ_PARTIES or to other IGS person-identifier columns without additional resolution. The view exposes no dates, group codes, or delivery-method columns, so consumers needing that detail must query the base tables.

Common Use Cases and Queries

The principal use case is personalization and security: determining which students an advisor may view. A typical query retrieves all students for a known advisor:

  • SELECT student_person_id FROM igs_sc_advisor_person_sv WHERE advisor_person_id = :p_advisor_person_id;

A reverse lookup — identifying the advisor(s) for a given student — is equally common:

  • SELECT advisor_person_id FROM igs_sc_advisor_person_sv WHERE student_person_id = :p_student_person_id;

Because the view already enforces effective dating and merges the 1-on-1 and CENTER delivery methods, it is well suited as a driving set in report queries that must not double-count or miss relationships established through either mechanism. Advisors of record are also frequently joined to HZ_PARTIES to obtain names for display, and to IGS enrolment or progression tables to build caseload reports. Developers should note that the view returns DISTINCT-equivalent pairs only implicitly; where duplicates can arise from overlapping relationship records, an outer DISTINCT or a GROUP BY on the two columns is prudent.