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'