[Home] [Help]
1: PACKAGE BODY IGS_EN_VAL_BULKRULE AS
2: /* $Header: IGSEN28B.pls 120.2 2006/05/02 23:54:29 ckasu noship $ */
3: /* smaddali modified all the procedures , added new parameters during
4: nov 2001 build of enrollment processes dld , bug#1832130 */
5: --Added refernces to column ORG_UNIT_CD incall to IGS_EN_SU_ATTEMPT TBH call as a part of bug 1964697
6: --
7: -- To process bulk unit Rule checks for students with todo entries
8: --added 3 new parameters for enrollment processes dld bug#1832130
9: --Who When What
6: --
7: -- To process bulk unit Rule checks for students with todo entries
8: --added 3 new parameters for enrollment processes dld bug#1832130
9: --Who When What
10: --Aiyer 10-Oct-2001 Added the column grading schema in all Tbh calls of IGS_EN_SU_ATTEMPT_PKG as a part of the bug 2037897.
11: -- pradhakr 10-Dec-2001 Added the column deg_Aud_Detail_Id as part of Degree Audit Interface build.
12: -- Bug# 2033208
13: -- svenkata 20-Dec-2001 Added columns student_career_transcript and Student_career_statistics as part of build Career
14: -- Impact Part2 . Bug #2158626
13: -- svenkata 20-Dec-2001 Added columns student_career_transcript and Student_career_statistics as part of build Career
14: -- Impact Part2 . Bug #2158626
15: -- svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
16: -- to table handler procedure calls as part of CCR - ENCR022.
17: --Nishikant 29-jan-2002 Added the column session_id in the Tbh calls of IGS_EN_SU_ATTEMPT_PKG
18: -- as a part of the bug 2172380.
19: --mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in TBH call of IGS_EN_SU_ATTEMPT
20: -- for Bug 2554109 MINI Waitlist Build for Jan 03 Release
21: --svenkata 20-NOV-2002 Modified the call to the function igs_en_val_sua.enrp_val_sua_advstnd to add value 'N' for the parameter
15: -- svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
16: -- to table handler procedure calls as part of CCR - ENCR022.
17: --Nishikant 29-jan-2002 Added the column session_id in the Tbh calls of IGS_EN_SU_ATTEMPT_PKG
18: -- as a part of the bug 2172380.
19: --mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in TBH call of IGS_EN_SU_ATTEMPT
20: -- for Bug 2554109 MINI Waitlist Build for Jan 03 Release
21: --svenkata 20-NOV-2002 Modified the call to the function igs_en_val_sua.enrp_val_sua_advstnd to add value 'N' for the parameter
22: -- p_legacy. Bug#2661533.
23: -- pradhakr 04-Dec-2002 Changed the parameter sequence in the procedure ENRP_VAL_SCA_RULTODO.
22: -- p_legacy. Bug#2661533.
23: -- pradhakr 04-Dec-2002 Changed the parameter sequence in the procedure ENRP_VAL_SCA_RULTODO.
24: -- As per standard the parameter errbuf and retcode are made as the
25: -- first two paramters. Changes as per bug# 2683629
26: -- pradhakr 15-Dec-2002 Changed the call to the update_row of igs_en_su_attempt
27: -- table to igs_en_sua_api.update_unit_attempt.
28: -- Changes wrt ENCR031 build. Bug# 2643207
29: -- svenkata 3-Jun-2003 The function ENRP_VAL_COO_CROSS has been removed. All references to this API is removed. Bug# 2829272
30: --rvivekan 3-SEP-2003 Waitlist Enhacements build # 3052426. 2 new columns added to
27: -- table to igs_en_sua_api.update_unit_attempt.
28: -- Changes wrt ENCR031 build. Bug# 2643207
29: -- svenkata 3-Jun-2003 The function ENRP_VAL_COO_CROSS has been removed. All references to this API is removed. Bug# 2829272
30: --rvivekan 3-SEP-2003 Waitlist Enhacements build # 3052426. 2 new columns added to
31: -- IGS_EN_SU_ATTEMPT_PKG procedures and consequently to IGS_EN_SUA_API procedures
32: --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
33: -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
34: --ptandon 25-Feb-2004 Modified procedure ENRP_VAL_SCA_RULTODO to log parameters in the log file of the
35: -- job. Modified procedure enrpl_enrolled_to_invalid to rollback per unit attempt
239: --added for enrollment processes dld by smaddali bug#1832130
240: p_cal_type IN IGS_CA_INST.cal_type%TYPE,
241: p_ci_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
242: p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE ,
243: p_rule_to_be_validated IN IGS_EN_SU_ATTEMPT_ALL.failed_unit_rule%TYPE )
244: IS
245: BEGIN
246: DECLARE
247: cst_enrolled CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'ENROLLED';
243: p_rule_to_be_validated IN IGS_EN_SU_ATTEMPT_ALL.failed_unit_rule%TYPE )
244: IS
245: BEGIN
246: DECLARE
247: cst_enrolled CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'ENROLLED';
248: cst_active CONSTANT IGS_CA_INST.cal_status%TYPE := 'ACTIVE';
249:
250: v_counter NUMBER; -- keeps record of the number of records in the PL/SQL table
251: v_next_rec BOOLEAN; -- used to determine if the next record should be found
255: -- Add the following one line of code. From Callista 2.0 18-May-2000
256: v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
257: -- Initialise PL/SQL table to hold UNIT attempt records which
258: -- cannot be set to INVALID.
259: TYPE r_unit_attempt_typ IS RECORD (rv_uoo_id igs_en_su_attempt.uoo_id%TYPE);
260: r_unit_attempt_rec r_unit_attempt_typ;
261: TYPE t_unit_attempt_typ IS TABLE OF r_unit_attempt_rec%TYPE
262: INDEX BY BINARY_INTEGER;
263: t_unit_attempt_tab t_unit_attempt_typ;
284: sua.unit_class,
285: ci.alternate_code,
286: uoo.uoo_id,
287: sua.sup_unit_cd
288: FROM IGS_EN_SU_ATTEMPT sua,
289: IGS_CA_INST ci,
290: IGS_PS_UNIT_OFR_OPT uoo
291: WHERE
292: sua.person_id = p_person_id
314: ORDER BY sup_unit_cd;
315:
316: -- gets the invalid cutoff date alias value
317: CURSOR c_daiv (
318: cp_sua_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
319: cp_sua_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
320: SELECT MAX(daiv.alias_val)
321: FROM IGS_CA_DA_INST_V daiv
322: WHERE daiv.cal_type = cp_sua_cal_type AND
315:
316: -- gets the invalid cutoff date alias value
317: CURSOR c_daiv (
318: cp_sua_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
319: cp_sua_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
320: SELECT MAX(daiv.alias_val)
321: FROM IGS_CA_DA_INST_V daiv
322: WHERE daiv.cal_type = cp_sua_cal_type AND
323: daiv.ci_sequence_number = cp_sua_sequence_number AND
332: sca.course_cd = p_course_cd;
333:
334: --smaddali modified this cursor to select only one unit section attempt
335: --instead of all the unit sections of a given unit cd during enrollment processes dld
336: CURSOR cur_IGS_EN_SU_ATTEMPT(cp_unit_cd VARCHAR2,
337: cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
338: SELECT sua.*
339: FROM IGS_EN_SU_ATTEMPT sua,
340: IGS_CA_INST ci
333:
334: --smaddali modified this cursor to select only one unit section attempt
335: --instead of all the unit sections of a given unit cd during enrollment processes dld
336: CURSOR cur_IGS_EN_SU_ATTEMPT(cp_unit_cd VARCHAR2,
337: cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
338: SELECT sua.*
339: FROM IGS_EN_SU_ATTEMPT sua,
340: IGS_CA_INST ci
341: WHERE sua.person_id = p_person_id AND
335: --instead of all the unit sections of a given unit cd during enrollment processes dld
336: CURSOR cur_IGS_EN_SU_ATTEMPT(cp_unit_cd VARCHAR2,
337: cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
338: SELECT sua.*
339: FROM IGS_EN_SU_ATTEMPT sua,
340: IGS_CA_INST ci
341: WHERE sua.person_id = p_person_id AND
342: sua.course_cd = p_course_cd AND
343: sua.unit_attempt_status = cst_enrolled AND
354: SELECT s_cal_cat
355: FROM IGS_CA_TYPE
356: WHERE cal_type = p_cal_type ;
357: l_cal_cat igs_ca_type.s_cal_cat%TYPE ;
358: l_failed_rule igs_en_su_attempt_all.failed_unit_rule%TYPE ;
359: l_retval BOOLEAN ;
360: sua_rec cur_IGS_EN_SU_ATTEMPT%ROWTYPE ;
361:
362:
356: WHERE cal_type = p_cal_type ;
357: l_cal_cat igs_ca_type.s_cal_cat%TYPE ;
358: l_failed_rule igs_en_su_attempt_all.failed_unit_rule%TYPE ;
359: l_retval BOOLEAN ;
360: sua_rec cur_IGS_EN_SU_ATTEMPT%ROWTYPE ;
361:
362:
363: BEGIN
364: -- 3. Check all ENROLLED UNIT attempt IGS_PS_UNIT rules. If a RULE is failed the
519: ELSE
520: -- Set the student unit attempt to invalid
521: -- as rules are being breached.
522:
523: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_rec.unit_cd,v_sua_rec.uoo_id) ;
524: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
525: CLOSE cur_IGS_EN_SU_ATTEMPT ;
526: /* For the coloumn to be updated,modify the record variable value fetched */
527: sua_rec.unit_attempt_status := 'INVALID' ;
520: -- Set the student unit attempt to invalid
521: -- as rules are being breached.
522:
523: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_rec.unit_cd,v_sua_rec.uoo_id) ;
524: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
525: CLOSE cur_IGS_EN_SU_ATTEMPT ;
526: /* For the coloumn to be updated,modify the record variable value fetched */
527: sua_rec.unit_attempt_status := 'INVALID' ;
528: -- Call the API to update the student unit attempt. This API is a
521: -- as rules are being breached.
522:
523: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_rec.unit_cd,v_sua_rec.uoo_id) ;
524: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
525: CLOSE cur_IGS_EN_SU_ATTEMPT ;
526: /* For the coloumn to be updated,modify the record variable value fetched */
527: sua_rec.unit_attempt_status := 'INVALID' ;
528: -- Call the API to update the student unit attempt. This API is a
529: -- wrapper to the update row of the TBH.
770: v_attendance_types VARCHAR2(100);
771: cst_active CONSTANT IGS_CA_INST.cal_status%TYPE := 'ACTIVE';
772: -- Initialise PL/SQL table to hold UNIT attempt records which
773: -- cannot be set to ENROLLED.
774: TYPE r_unit_attempt_typ IS RECORD (rv_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE);
775: r_unit_attempt_rec r_unit_attempt_typ;
776: TYPE t_unit_attempt_typ IS TABLE OF r_unit_attempt_rec%TYPE
777: INDEX BY BINARY_INTEGER;
778: t_unit_attempt_tab t_unit_attempt_typ;
804: sca.coo_id,
805: ci.alternate_code ,
806: uoo.uoo_id,
807: sua.sup_unit_cd
808: FROM IGS_EN_SU_ATTEMPT sua,
809: IGS_EN_STDNT_PS_ATT sca,
810: IGS_CA_INST ci ,
811: IGS_PS_UNIT_OFR_OPT uoo
812: WHERE sua.person_id = p_person_id
823: ORDER BY sup_unit_cd DESC;
824:
825: --smaddali modified this cursor to select only one unit section attempt
826: --instead of all the unit sections of a given unit cd during enrollment processes dld
827: CURSOR cur_IGS_EN_SU_ATTEMPT(cp_unit_cd varchar2,
828: cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
829: SELECT sua.*
830: FROM IGS_EN_SU_ATTEMPT sua,
831: IGS_EN_STDNT_PS_ATT sca,
824:
825: --smaddali modified this cursor to select only one unit section attempt
826: --instead of all the unit sections of a given unit cd during enrollment processes dld
827: CURSOR cur_IGS_EN_SU_ATTEMPT(cp_unit_cd varchar2,
828: cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
829: SELECT sua.*
830: FROM IGS_EN_SU_ATTEMPT sua,
831: IGS_EN_STDNT_PS_ATT sca,
832: IGS_CA_INST ci
826: --instead of all the unit sections of a given unit cd during enrollment processes dld
827: CURSOR cur_IGS_EN_SU_ATTEMPT(cp_unit_cd varchar2,
828: cp_uoo_id igs_en_su_attempt_all.uoo_id%TYPE) IS
829: SELECT sua.*
830: FROM IGS_EN_SU_ATTEMPT sua,
831: IGS_EN_STDNT_PS_ATT sca,
832: IGS_CA_INST ci
833: WHERE sua.person_id = p_person_id AND
834: sua.course_cd = p_course_cd AND
841: --added for enrollment processes dld by smaddali
842: sua.uoo_id = cp_uoo_id;
843:
844: CURSOR c_daiv (
845: cp_sua_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
846: cp_sua_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
847: SELECT MAX(daiv.alias_val)
848: FROM IGS_CA_DA_INST_V daiv
849: WHERE daiv.cal_type = cp_sua_cal_type AND
842: sua.uoo_id = cp_uoo_id;
843:
844: CURSOR c_daiv (
845: cp_sua_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
846: cp_sua_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE) IS
847: SELECT MAX(daiv.alias_val)
848: FROM IGS_CA_DA_INST_V daiv
849: WHERE daiv.cal_type = cp_sua_cal_type AND
850: daiv.ci_sequence_number = cp_sua_sequence_number AND
864: AND load_ci_sequence_number = nvl( p_ci_sequence_number ,load_ci_sequence_number);
865:
866: rec_teach c_load_to_teach%ROWTYPE;
867: l_cal_cat igs_ca_type.s_cal_cat%TYPE ;
868: l_failed_rule igs_en_su_attempt_all.failed_unit_rule%TYPE;
869: sua_rec cur_IGS_EN_SU_ATTEMPT%ROWTYPE ;
870: l_loop_ended BOOLEAN := FALSE;
871:
872: BEGIN
865:
866: rec_teach c_load_to_teach%ROWTYPE;
867: l_cal_cat igs_ca_type.s_cal_cat%TYPE ;
868: l_failed_rule igs_en_su_attempt_all.failed_unit_rule%TYPE;
869: sua_rec cur_IGS_EN_SU_ATTEMPT%ROWTYPE ;
870: l_loop_ended BOOLEAN := FALSE;
871:
872: BEGIN
873: -- 2. Re-check all INVALID UNIT attempts and switch to ENROLLED if the
974: v_validation_message_name := 'IGS_EN_UA_CHGST_ENROLLED';
975:
976: ELSE
977: --update the unit attempt status to enrolled and set failed unit rule to NULL
978: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_sca_rec.unit_cd, v_sua_sca_rec.uoo_id);
979: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
980: CLOSE cur_IGS_EN_SU_ATTEMPT ;
981: /* For the column to be updated,modify the record variable value fetched */
982:
975:
976: ELSE
977: --update the unit attempt status to enrolled and set failed unit rule to NULL
978: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_sca_rec.unit_cd, v_sua_sca_rec.uoo_id);
979: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
980: CLOSE cur_IGS_EN_SU_ATTEMPT ;
981: /* For the column to be updated,modify the record variable value fetched */
982:
983: sua_rec.unit_attempt_status := 'ENROLLED' ;
976: ELSE
977: --update the unit attempt status to enrolled and set failed unit rule to NULL
978: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_sca_rec.unit_cd, v_sua_sca_rec.uoo_id);
979: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
980: CLOSE cur_IGS_EN_SU_ATTEMPT ;
981: /* For the column to be updated,modify the record variable value fetched */
982:
983: sua_rec.unit_attempt_status := 'ENROLLED' ;
984: -- Call the API to update the student unit attempt. This API is a
1103: IF (v_validation_error) THEN
1104:
1105: -- if any of the above validations failed or the cutoff date has failed
1106: -- then revert to the invalid state of the unit attempt and enter into log
1107: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_sca_rec.unit_cd,v_sua_sca_rec.uoo_id);
1108: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
1109: CLOSE cur_IGS_EN_SU_ATTEMPT ;
1110: -- Set the status back to invalid
1111: /* For the coloumn to be updated,modify the record variable value fetched */
1104:
1105: -- if any of the above validations failed or the cutoff date has failed
1106: -- then revert to the invalid state of the unit attempt and enter into log
1107: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_sca_rec.unit_cd,v_sua_sca_rec.uoo_id);
1108: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
1109: CLOSE cur_IGS_EN_SU_ATTEMPT ;
1110: -- Set the status back to invalid
1111: /* For the coloumn to be updated,modify the record variable value fetched */
1112: sua_rec.unit_attempt_status := 'INVALID';
1105: -- if any of the above validations failed or the cutoff date has failed
1106: -- then revert to the invalid state of the unit attempt and enter into log
1107: OPEN cur_IGS_EN_SU_ATTEMPT(v_sua_sca_rec.unit_cd,v_sua_sca_rec.uoo_id);
1108: FETCH cur_IGS_EN_SU_ATTEMPT INTO sua_rec ;
1109: CLOSE cur_IGS_EN_SU_ATTEMPT ;
1110: -- Set the status back to invalid
1111: /* For the coloumn to be updated,modify the record variable value fetched */
1112: sua_rec.unit_attempt_status := 'INVALID';
1113: -- Call the API to update the student unit attempt. This API is a