[Home] [Help]
891: AND NVL (stu.end_date, SYSDATE + 1) > SYSDATE
892: AND stu.group_name = p_group_name
893: AND NOT EXISTS
894: (SELECT 1
895: FROM igs_az_advising_rels rel
896: WHERE rel.group_name = p_group_name
897: AND rel.group_student_id = stu.group_student_id);
898: --
899: -- Select the advisor details for allocating the student
907: NVL (rel.actual_load, 0) actual_load,
908: NVL (rel.actual_load, 0) / NVL (adv.max_students_num, 1) percent_load
909: FROM igs_az_advisors adv, (SELECT group_advisor_id,
910: COUNT(*) actual_load
911: FROM igs_az_advising_rels
912: WHERE group_name = p_group_name
913: GROUP BY group_advisor_id) rel
914: WHERE adv.start_date IS NOT NULL
915: AND TRUNC (adv.start_date) <= TRUNC (SYSDATE)
928: AND NVL (end_date, SYSDATE + 1) > SYSDATE
929: AND group_name = p_group_name
930: AND NOT EXISTS
931: (SELECT 1
932: FROM igs_az_advising_rels rel
933: WHERE group_name = p_group_name
934: AND rel.group_student_id = st.group_student_id);
935: --
936: -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
945: -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
946: --
947: CURSOR cur_tot_act_load IS
948: SELECT COUNT (*)
949: FROM igs_az_advising_rels rel
950: WHERE group_name = p_group_name
951: AND EXISTS
952: (SELECT 1
953: FROM igs_az_advisors adv
963: totalActualLoad NUMBER;
964: totalMaximumLoad NUMBER;
965: desiredLoadPercentage NUMBER;
966: lv_rowid VARCHAR2(25);
967: l_group_advising_rel_id igs_az_advising_rels.group_advising_rel_id%TYPE;
968: ldRelEndDate DATE;
969: ldRelStartDate DATE;
970: lvcReturnStatus VARCHAR2(10);
971: lvcMsgData VARCHAR2(2000);
1044: lnProcessed := lnProcessed +1;
1045: --
1046: -- Add Student to the Relationship table with the current Advisor
1047: --
1048: igs_az_advising_rels_pkg.insert_row (
1049: x_rowid => lv_rowid,
1050: x_group_advising_rel_id => l_group_advising_rel_id,
1051: x_group_name => p_group_name,
1052: x_group_advisor_id => rec_advisors_to_load.group_advisor_id,
1937: /******************************************************************
1938: Created By : Girish Jha
1939: Date Created By : 17-May-2003
1940: Purpose : The requirement for ending an advisor is not limited to ending
1941: the advisor but also the relationships that advisor has with the students in table IGS_AZ_ADVISING_RELS.
1942: Also the end dating an advisor can happen either from SS page fro a concurrent program. Its better to write a
1943: separet procedure to handle this.
1944:
1945: Change History
1972: --
1973: CURSOR cur_reln IS
1974: SELECT azr.ROWID row_id,
1975: azr.*
1976: FROM igs_az_advising_rels azr, igs_az_advisors aza
1977: WHERE azr.group_name = p_group_name
1978: AND azr.group_name = aza.group_name
1979: AND azr.group_advisor_id = aza.group_advisor_id
1980: AND aza.advisor_person_id = p_advisor_person_id
1984: --
1985: --
1986: CURSOR cur_reln_del IS
1987: SELECT azr.ROWID row_id
1988: FROM igs_az_advising_rels azr, igs_az_advisors aza
1989: WHERE azr.group_name = p_group_name
1990: AND azr.group_name = aza.group_name
1991: AND azr.group_advisor_id = aza.group_advisor_id
1992: AND aza.advisor_person_id = p_advisor_person_id
2034: --
2035: -- End date the relationship..
2036: --
2037: FOR reln_rec IN cur_reln LOOP
2038: igs_az_advising_rels_pkg.update_row (
2039: x_rowid => reln_rec.row_id,
2040: x_group_advising_rel_id => reln_rec.group_advising_rel_id,
2041: x_group_name => reln_rec.group_name,
2042: x_group_advisor_id => reln_rec.group_advisor_id,
2049: ); -- To do see if msg count etc. is require , if yes add and Error Handling
2050: END LOOP;
2051: --
2052: FOR del_reln IN cur_reln_del LOOP
2053: igs_az_advising_rels_pkg.delete_row (
2054: x_rowid => del_reln.row_id,
2055: x_return_status => lvReturnStatus,
2056: x_msg_data => lvMsgData,
2057: x_msg_count => lnMsgCount
2115: /******************************************************************
2116: Created By : Girish Jha
2117: Date Created By : 17-May-2003
2118: Purpose : The requirement for ending an student is not limited to end dating
2119: the student but also the relationships that student has in table IGS_AZ_ADVISING_RELS.
2120: Also the end dating an student can happen either from SS page fro a concurrent program. Its better to write a
2121: separet procedure to handle this.
2122:
2123:
2151: --
2152: CURSOR cur_reln IS
2153: SELECT azr.ROWID row_id,
2154: azr.*
2155: FROM igs_az_advising_rels azr, igs_az_students azs
2156: WHERE azr.group_name = p_group_name
2157: AND azr.group_name = azs.group_name
2158: AND azr.group_student_id = azs.group_student_id
2159: AND azs.student_person_id = p_student_person_id
2170: --
2171: --
2172: CURSOR cur_reln_del IS
2173: SELECT azr.ROWID row_id
2174: FROM igs_az_advising_rels azr, igs_az_students azs
2175: WHERE azr.group_name = p_group_name
2176: AND azr.group_name = azs.group_name
2177: AND azr.group_student_id = azs.group_student_id
2178: AND azs.student_person_id = p_student_person_id
2213: --
2214: -- End date the relationship..
2215: --
2216: FOR reln_rec IN cur_reln LOOP
2217: igs_az_advising_rels_pkg.update_row (
2218: x_rowid => reln_rec.row_id,
2219: x_group_advising_rel_id => reln_rec.group_advising_rel_id,
2220: x_group_name => reln_rec.group_name,
2221: x_group_advisor_id => reln_rec.group_advisor_id,
2228: ); -- To do see if msg count etc. is require , if yes add and Error Handling also to do See how we can verify that the end date is earliest of either the student or the advisor end date if any.
2229: END LOOP; --reln_rec
2230: --
2231: FOR del_reln IN cur_reln_del LOOP
2232: igs_az_advising_rels_pkg.delete_row (
2233: x_rowid => del_reln.row_id,
2234: x_return_status => lvReturnStatus,
2235: x_msg_data => lvMsgData,
2236: x_msg_count => lnMsgCount