VIEW: APPS.IGS_SC_ADVISOR_PERSON_SV

Source

View Text - Preformatted

SELECT AVA.ADVISOR_PERSON_ID, AVS.STUDENT_PERSON_ID FROM IGS_AZ_STUDENTS AVS, IGS_AZ_ADVISORS AVA, IGS_AZ_ADVISING_RELS AVR, IGS_AZ_GROUPS AVG WHERE AVA.GROUP_ADVISOR_ID = AVR.GROUP_ADVISOR_ID AND SYSDATE BETWEEN AVR.START_DATE AND NVL(AVR.END_DATE,TO_DATE('31-12-4712','DD-MM-YYYY')) AND AVS.GROUP_STUDENT_ID = AVR.GROUP_STUDENT_ID AND AVS.GROUP_NAME = AVR.GROUP_NAME AND AVS.GROUP_NAME = AVA.GROUP_NAME AND AVS.GROUP_NAME = AVG.GROUP_NAME AND AVG.DELIVERY_METHOD_CODE = '1_ON_1' UNION ALL SELECT AVA.ADVISOR_PERSON_ID, AVS.STUDENT_PERSON_ID FROM IGS_AZ_STUDENTS AVS, IGS_AZ_ADVISORS AVA, IGS_AZ_GROUPS AVG WHERE AVS.GROUP_NAME = AVA.GROUP_NAME AND SYSDATE BETWEEN AVS.START_DATE AND NVL(AVS.END_DATE,TO_DATE('31-12-4712','DD-MM-YYYY')) AND SYSDATE BETWEEN AVA.START_DATE AND NVL(AVS.END_DATE,TO_DATE('31-12-4712','DD-MM-YYYY')) AND AVS.GROUP_NAME = AVG.GROUP_NAME AND AVG.DELIVERY_METHOD_CODE = 'CENTER'

View Text - HTML Formatted

SELECT AVA.ADVISOR_PERSON_ID
, AVS.STUDENT_PERSON_ID
FROM IGS_AZ_STUDENTS AVS
, IGS_AZ_ADVISORS AVA
, IGS_AZ_ADVISING_RELS AVR
, IGS_AZ_GROUPS AVG
WHERE AVA.GROUP_ADVISOR_ID = AVR.GROUP_ADVISOR_ID
AND SYSDATE BETWEEN AVR.START_DATE
AND NVL(AVR.END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND AVS.GROUP_STUDENT_ID = AVR.GROUP_STUDENT_ID
AND AVS.GROUP_NAME = AVR.GROUP_NAME
AND AVS.GROUP_NAME = AVA.GROUP_NAME
AND AVS.GROUP_NAME = AVG.GROUP_NAME
AND AVG.DELIVERY_METHOD_CODE = '1_ON_1' UNION ALL SELECT AVA.ADVISOR_PERSON_ID
, AVS.STUDENT_PERSON_ID
FROM IGS_AZ_STUDENTS AVS
, IGS_AZ_ADVISORS AVA
, IGS_AZ_GROUPS AVG
WHERE AVS.GROUP_NAME = AVA.GROUP_NAME
AND SYSDATE BETWEEN AVS.START_DATE
AND NVL(AVS.END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND SYSDATE BETWEEN AVA.START_DATE
AND NVL(AVS.END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND AVS.GROUP_NAME = AVG.GROUP_NAME
AND AVG.DELIVERY_METHOD_CODE = 'CENTER'