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
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
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.
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
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
36 -- in case of exception and process the Subordinate unit attempts before Superior
37 -- unit attempts. Modified procedure enrpl_invalid_to_enrolled to rollback per unit
38 -- attempt in case of exception and process the Superior unit attempts before
39 -- Subordinate unit attempts. Removed the exception handling block of enrp_val_sca_urule
40 -- Bug# 3451409.
41 --svanukur 04-MAY-2004 Added the check to filter the programs based on the academic calendar type passed as
42 -- parameter .
43 -- smaddali 15-oct-04 Modified for bug#3954071 - load calendar being passed to rules engine instead of teaching calendar
44 --ckasu 02-May-2006 Modified as a part of bug#5191592
45
46 PROCEDURE ENRP_VAL_SCA_RULTODO(
47 errbuf OUT NOCOPY VARCHAR2 ,
48 retcode OUT NOCOPY NUMBER ,
49 p_acad_calander IN VARCHAR2,
50 p_crs_cd IN VARCHAR2 ,
51 p_org_id IN NUMBER,
52 -- added new parameters for enrollment processes build bug#1832130
53 p_load_teach_calendar IN VARCHAR2 ,
54 p_org_unit_cd IN VARCHAR2 ,
55 p_rule_to_be_validated IN VARCHAR2 )
56 AS
57 p_course_cd igs_ps_course.course_cd%type;
58 p_acad_cal_type igs_ca_inst.cal_type%TYPE ;
59 p_acad_sequence_number igs_ca_inst.sequence_number%TYPE;
60 --added for enrollment processes dld bug#1832130
61 p_load_cal_type igs_ca_inst.cal_type%TYPE ;
62 p_load_sequence_number igs_ca_inst.sequence_number%TYPE;
63 BEGIN
64 retcode:=0;
65
66 IGS_GE_GEN_003.SET_ORG_ID(p_org_id);
67
68 p_course_cd := nvl(p_crs_cd,'%');
69 BEGIN
70 IF p_acad_calander IS NOT NULL THEN
71 p_acad_cal_type := RTRIM(SUBSTR(p_acad_calander,1,10));
72 p_acad_sequence_number := (SUBSTR(p_acad_calander,75,10));
73 ELSE
74 p_acad_cal_type := '%';
75 END IF;
76
77 -- added for enrollment processes dld bug#1832130
78 p_load_cal_type := RTRIM(SUBSTR(p_load_teach_calendar,101,10));
79 p_load_sequence_number := SUBSTR(p_load_teach_calendar,111,10);
80 END ;
81 DECLARE
82 CURSOR c_st IS
83 SELECT rowid,cst.*
84 FROM IGS_PE_STD_TODO cst
85 WHERE s_student_todo_type = 'UNIT-RULES' AND
86 logical_delete_dt IS NULL AND
87 NVL(todo_dt,TO_DATE('01/01/1980','DD/MM/YYYY')) <= SYSDATE
88 ORDER BY person_id asc
89 FOR UPDATE NOWAIT;
90 CURSOR c_sca (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
91 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
92 SELECT course_cd
93 FROM IGS_EN_STDNT_PS_ATT
94 WHERE person_id = cp_person_id AND
95 (course_cd = cp_course_cd OR
96 course_cd like cp_course_cd) AND
97 (cal_type = p_acad_cal_type OR
98 cal_type like p_acad_cal_type) AND
99 course_attempt_status IN ('ENROLLED','INACTIVE','INTERMIT');
100 v_creation_dt DATE;
101 v_last_person_id IGS_PE_PERSON.person_id%TYPE;
102
103 BEGIN
104 -- Call routine to create a new IGS_GE_S_LOG entry under which the log
105 -- entries will be placed.
106 IGS_GE_GEN_003.GENP_INS_LOG(p_rule_to_be_validated,
107 p_acad_cal_type||','||TO_CHAR(p_acad_sequence_number)||','||p_course_cd,
108 v_creation_dt);
109
110 -- Logging parameters in the Log File.
111 FND_FILE.PUT_LINE(FND_FILE.LOG,'================================================================================');
112 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
113 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_EN_ACAD_CAL') || ': ' || RTRIM(SUBSTR(p_acad_calander,1,74)));
114 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRS_CD') || ': ' || p_crs_cd);
115 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_EN_TERM_TEACH_CAL') || ': ' || RTRIM(SUBSTR(p_load_teach_calendar,1,100)));
116 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_PR_ORG_UNIT') || ': ' || p_org_unit_cd);
117 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_EN_RULE_TO_VAL') || ': ' || p_rule_to_be_validated);
118 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
119 FND_FILE.PUT_LINE(FND_FILE.LOG,'================================================================================');
120
121 v_last_person_id := 0;
122 BEGIN
123 FOR v_st_rec IN c_st
124 LOOP
125
126 -- Only process each PERSON once (NOTE : a distinct could
127 -- not be used in the query due to the FOR UPDATE clause.
128 IF v_st_rec.person_id <> v_last_person_id THEN
129 v_last_person_id := v_st_rec.person_id;
130
131 FOR v_sca_rec IN c_sca(v_st_rec.person_id,p_course_cd)
132 LOOP
133 -- Call routine to process individual student Course
134 -- attempt.
135 --added 4 new parameters to this call for enrollment processes dld
136
137 IGS_EN_VAL_BULKRULE.enrp_val_sca_urule(
138 p_acad_cal_type,
139 p_acad_sequence_number,
140 v_st_rec.person_id,
141 v_sca_rec.course_cd,
142 p_rule_to_be_validated,
143 v_creation_dt,
144 p_load_cal_type,
145 p_load_sequence_number,
146 p_org_unit_cd,
147 p_rule_to_be_validated);
148 END LOOP;
149 -- Logically remove the todo entries as they have been processed.
150 /* For the coloumn to be updated,modify the record variable value fetched */
151 v_st_rec.logical_delete_dt := SYSDATE;
152 /* Call server side TBH package procedure */
153 IGS_PE_STD_TODO_pkg.update_row(
154 X_ROWID => v_st_rec.ROWID,
155 X_PERSON_ID => v_st_rec.PERSON_ID,
156 X_S_STUDENT_TODO_TYPE => v_st_rec.S_STUDENT_TODO_TYPE,
157 X_SEQUENCE_NUMBER => v_st_rec.SEQUENCE_NUMBER,
158 X_TODO_DT => v_st_rec.TODO_DT,
159 X_LOGICAL_DELETE_DT => v_st_rec.LOGICAL_DELETE_DT,
160 X_MODE => 'R');
161 END IF;
162 END LOOP;
163 END;
164 COMMIT;
165 END;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 retcode:=2;
170 Fnd_File.PUT_LINE(Fnd_File.LOG,SQLERRM);
171 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
172 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_en_val_bulkrule.enrp_val_sca_rultodo.UNH_EXP','Unhandled Exception raised with code '||SQLCODE||' and error '||SQLERRM);
173 END IF;
174 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
175 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
176
177 END enrp_val_sca_rultodo;
178
179 --
180 -- Validate the unit rules for a student Course attempt (in bulk)
181 /* smaddali modified this procedure to add 3 new parameters and their use in code */
182 PROCEDURE ENRP_VAL_SCA_URULE(
183 p_acad_cal_type IN VARCHAR2 ,
184 p_acad_sequence_number IN NUMBER ,
185 p_person_id IN NUMBER ,
186 p_course_cd IN VARCHAR2 ,
187 p_s_log_type IN VARCHAR2 ,
188 p_creation_dt IN DATE ,
189 -- added new parameters for bug#1832130
190 p_cal_type IN VARCHAR2 ,
191 p_ci_sequence_number IN NUMBER ,
192 p_org_unit_cd IN VARCHAR2 ,
193 p_rule_to_be_validated IN VARCHAR2 )
194 -------------------------------------------------------------------------------------------
195 --Change History:
196 --Who When What
197 --kkillams 28-04-2003 Modified the r_unit_attempt_typ record type definition in the
198 -- enrpl_enrolled_to_invalid and enrpl_invalid_to_enrolled procedures
199 -- w.r.t. bug number 2829262
200 -- svenkata 3-Jun-2003 The function ENRP_VAL_COO_CROSS has been removed. All references to this API is removed. Bug# 2829272
201 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
202 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
203 --ptandon 25-Feb-2004 Modified procedure enrpl_enrolled_to_invalid to rollback per unit attempt
204 -- in case of exception and process the Subordinate unit attempts before Superior
205 -- unit attempts. Modified procedure enrpl_invalid_to_enrolled to rollback per unit
206 -- attempt in case of exception and process the Superior unit attempts before
207 -- Subordinate unit attempts. Removed the exception handling block of
208 -- enrp_val_sca_urule. Bug# 3451409.
209 --svanukur 04-MAY-2004 Modified procedures enrpl_enrolled_to_invalid and enrpl_invalid_to_enrolled to pass
210 -- the Term calendar as parameter to the rule checking procedures in IGS_RU_VAL_UNIT_RULE
211 -- to fix bug 3606629
212 -- smaddali 15-oct-04 Modified for bug#3954071, reverted earlier fix bug 3606629 of passing load calendar to rules engine
213 -------------------------------------------------------------------------------------------
214 AS
215 BEGIN
216 DECLARE
217 cst_s_control_num CONSTANT IGS_EN_CAL_CONF.s_control_num%TYPE := 1;
218 e_no_records_found EXCEPTION;
219 v_enrolled_rule_cutoff_dt IGS_EN_CAL_CONF.enrolled_rule_cutoff_dt_alias%TYPE;
220 v_invalid_rule_cutoff_dt IGS_EN_CAL_CONF.invalid_rule_cutoff_dt_alias%TYPE;
221 v_message_name varchar2(30);
222 CURSOR c_secc IS
223 SELECT secc.enrolled_rule_cutoff_dt_alias,
224 secc.invalid_rule_cutoff_dt_alias
225 FROM IGS_EN_CAL_CONF secc
226 WHERE secc.s_control_num = cst_s_control_num;
227
228 /* smaddali modified this procedure to add 4 new parameters and their use in code
229 --stutta 08-SEP-2004 Modified c_sua as part of performance tuning bug #3869677*/
230 -------------------------------------
231 PROCEDURE enrpl_enrolled_to_invalid (
232 p_acad_cal_type IN IGS_CA_INST.cal_type%TYPE,
233 p_acad_sequence_number IN IGS_CA_INST.sequence_number%TYPE,
234 p_person_id IN IGS_EN_STDNT_PS_ATT.person_id%TYPE,
235 p_course_cd IN IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
236 p_s_log_type IN IGS_GE_S_LOG.s_log_type%TYPE,
237 p_creation_dt IN IGS_GE_S_LOG.creation_dt%TYPE,
238 p_invalid_rule_cutoff_dt IN IGS_EN_CAL_CONF.invalid_rule_cutoff_dt_alias%TYPE,
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';
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
252 v_do_cursor_again_ind BOOLEAN;
253 v_last_date_to_invalid IGS_CA_DA_INST_V.alias_val%TYPE;
254 v_message_text VARCHAR2(2000);
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;
264
265 t_unit_att_failure t_unit_attempt_typ;
266 r_unit_att_failure_rec r_unit_attempt_typ;
267 l_counter_failure NUMBER;
268 l_log_msg BOOLEAN;
269
270 -- modified for enrollment processes build bug#1832130 by smaddali
271 -- to consider the new parameters added.this cursor selects all the
272 -- student unit attempts for the passed program attempt.
273 --The two selects are mutually exclusive ie at any time the calendar passed is
274 --either 'LOAD' or 'TEACHING' so this cursor selects the unit attempts
275 -- for that particular calendar passed as parameter
276 CURSOR c_sua(cp_cal_cat igs_ca_type.s_cal_cat%TYPE) IS
277 -- selects the unit attempt when a teaching calendar is passed
278 SELECT
279 sua.unit_cd,
280 sua.cal_type,
281 sua.ci_sequence_number,
282 sua.version_number,
283 sua.location_cd,
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
293 AND sua.course_cd = p_course_cd
294 AND sua.unit_attempt_status = 'ENROLLED'
295 AND sua.rule_waived_dt IS NULL
296 AND ci.cal_type = sua.cal_type
297 AND ci.sequence_number = sua.ci_sequence_number
298 AND uoo.uoo_id = sua.uoo_id
299 AND uoo.owner_org_unit_cd LIKE NVL(p_org_unit_cd,'%')
300 AND (
301 ( cp_cal_cat = 'TEACHING'
302 AND sua.cal_type LIKE NVL(p_cal_type,'%')
303 AND sua.ci_sequence_number = nvl(p_ci_sequence_number,sua.ci_sequence_number)
304 ) OR
305 ( cp_cal_cat = 'LOAD' AND
306 (sua.cal_type,sua.ci_sequence_number) IN
307 ( SELECT teach_cal_type,teach_ci_sequence_number
308 FROM IGS_CA_LOAD_TO_TEACH_V
309 WHERE load_cal_type LIKE NVL(p_cal_type,'%')
310 AND load_ci_sequence_number = nvl(p_ci_sequence_number,load_ci_sequence_number)
311 )
312 )
313 )
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
323 daiv.ci_sequence_number = cp_sua_sequence_number AND
324 daiv.dt_alias = p_invalid_rule_cutoff_dt;
325
326 -- Add the following five lines of code. From Callista 2.0 18-May-2000
327 --gets the course varsion number for the student program attempt
328 CURSOR c_sca IS
329 SELECT sca.version_number
330 FROM IGS_EN_STDNT_PS_ATT sca
331 WHERE sca.person_id = p_person_id 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
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
344 sua.rule_waived_dt IS NULL AND
345 ci.cal_type = sua.cal_type AND
346 ci.sequence_number = sua.ci_sequence_number AND
347 sua.unit_cd = cp_unit_cd AND
348 --added for enrollment processes dld by smaddali
349 sua.uoo_id = cp_uoo_id;
350
351 /* smaddali bug#1832130 enrollment processes build nov 2001 release
352 added declarations for workflow events */
353 CURSOR cur_cal_cat IS
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
363 BEGIN
364 -- 3. Check all ENROLLED UNIT attempt IGS_PS_UNIT rules. If a RULE is failed the
365 -- status is set to INVALID and the select is repeated. This process
366 -- repeats until no further units fail rules.
367 --------------------------------------------------------
368
369 v_counter := 0;
370 l_counter_failure :=0;
371 -- Add the following nine lines of code. From Callista 2.0 18-May-2000
372 -- Determine the version number of the course to be used in the call to
373 -- rulp_val_enrol_unit.
374
375 OPEN c_sca;
376 FETCH c_sca INTO v_sca_version_number;
377 IF c_sca%NOTFOUND THEN
378 CLOSE c_sca;
379 RAISE NO_DATA_FOUND;
380 END IF;
381 CLOSE c_sca;
382
383 IF p_cal_type IS NULL THEN
384 l_cal_cat := 'TEACHING' ;
385 ELSE
386 OPEN cur_cal_cat ;
387 FETCH cur_cal_cat INTO l_cal_cat ;
388 CLOSE cur_cal_cat ;
389 END IF;
390
391 LOOP
392 v_do_cursor_again_ind := FALSE;
393 -- get the calendar category of the calendar passed
394
395 FOR v_sua_rec IN c_sua(l_cal_cat)
396 LOOP
397
398 DECLARE
399 l_message_text VARCHAR2(1000);
400 BEGIN
401 SAVEPOINT sp_sua;
402 v_next_rec := FALSE;
403
404 -- unit attempts which can't be set to invalid shouldn't be
405 -- processed again - would result in a never ending recursive loop
406 FOR i IN 1..v_counter
407 LOOP
408 r_unit_attempt_rec := t_unit_attempt_tab(i);
409 IF (r_unit_attempt_rec.rv_uoo_id = v_sua_rec.uoo_id) THEN
410 v_next_rec := TRUE;
411 EXIT;
412 END IF;
413 END LOOP;
414
415 IF (v_next_rec = FALSE) THEN
416
417 -- sua record is not in the PL/SQL table
418 -- Select the last date to switch to ENROLLED from the teaching period.
419 IF (p_invalid_rule_cutoff_dt IS NOT NULL) THEN
420 OPEN c_daiv(
421 v_sua_rec.cal_type,
422 v_sua_rec.ci_sequence_number);
423 FETCH c_daiv INTO v_last_date_to_invalid;
424 IF (c_daiv%NOTFOUND) THEN
425 v_last_date_to_invalid := NULL;
426 END IF;
427 CLOSE c_daiv;
428 ELSE
429 v_last_date_to_invalid := NULL;
430 END IF;
431
432 -- Check whether the unit attempt fails the unit rule passed as parameter
433 -- If so, it should be set to INVALID.
434 IF (p_rule_to_be_validated = 'PRE-REQ') THEN
435
436 --if rule to validate is prerequisite then call the corresponding
437 l_retval := IGS_RU_VAL_UNIT_RULE.rulp_val_prereq(
438 p_person_id,
439 p_course_cd,
440 v_sua_rec.unit_cd,
441 v_sua_rec.cal_type,
442 v_sua_rec.ci_sequence_number,
443 v_message_text ,
444 v_sca_version_number,
445 v_sua_rec.version_number,
446 v_sua_rec.uoo_id,
447 l_failed_rule );
448
449 ELSIF (p_rule_to_be_validated = 'CO-REQ') THEN
450
451 l_retval := IGS_RU_VAL_UNIT_RULE.rulp_val_coreq(
452 p_person_id,
453 p_course_cd,
454 v_sua_rec.unit_cd,
455 v_sua_rec.cal_type,
456 v_sua_rec.ci_sequence_number,
457 v_message_text ,
458 v_sca_version_number,
459 v_sua_rec.version_number,
460 v_sua_rec.uoo_id,
461 l_failed_rule );
462 ELSIF (p_rule_to_be_validated = 'INCOMP') THEN
463
464 l_retval := IGS_RU_VAL_UNIT_RULE.rulp_val_incomp(
465 p_person_id,
466 p_course_cd,
467 v_sua_rec.unit_cd,
468 v_sua_rec.cal_type,
469 v_sua_rec.ci_sequence_number,
470 v_message_text ,
471 v_sca_version_number,
472 v_sua_rec.version_number,
473 v_sua_rec.uoo_id,
474 l_failed_rule );
475
476 ELSIF (p_rule_to_be_validated = 'ALL') THEN
477
478 l_retval := IGS_RU_VAL_UNIT_RULE.rulp_val_enrol_unit(
479 p_person_id,
480 p_course_cd,
481 -- Add the following one lines of code.From Callista 2.0 18-May-2000
482 v_sca_version_number,
483 v_sua_rec.unit_cd,
484 v_sua_rec.version_number,
485 v_sua_rec.cal_type,
486 v_sua_rec.ci_sequence_number,
487 v_message_text ,
488 v_sua_rec.uoo_id,
489 l_failed_rule );
490
491 END IF ;
492 IF NOT l_retval THEN
493
494 -- if the rule has failed then log an entry and try to change to invalid
495 IF (v_last_date_to_invalid IS NOT NULL AND
496 SYSDATE > v_last_date_to_invalid) THEN
497 --the invalid cutoff date has passed so cannot make invalid
498 -- If logging is required, then log the change to the passed log
499
500 IF (p_s_log_type IS NOT NULL AND
501 p_creation_dt IS NOT NULL) THEN
502 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
503 p_s_log_type,
504 p_creation_dt,
505 'WARNING,' || p_person_id || ',' ||
506 p_course_cd ||',' ||
507 v_sua_rec.unit_cd || ',' ||
508 v_sua_rec.version_number || ',' ||
509 'ENROLLED,' || v_sua_rec.alternate_code || ',' ||
510 v_sua_rec.location_cd || ',' ||
511 v_sua_rec.unit_class,
512 'IGS_EN_UA_FAILS_ST_INVALID',
513 NULL);
514 END IF;
515 -- Store unit_cd, cal_type and ci_sequence_number in PL/SQL table
516 r_unit_attempt_rec.rv_uoo_id := v_sua_rec.uoo_id;
517 v_counter := v_counter + 1;
518 t_unit_attempt_tab(v_counter) := r_unit_attempt_rec;
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' ;
528 -- Call the API to update the student unit attempt. This API is a
529 -- wrapper to the update row of the TBH.
530 igs_en_sua_api.update_unit_attempt(
531 X_ROWID => sua_rec.ROW_ID,
532 X_PERSON_ID => sua_rec.PERSON_ID,
533 X_COURSE_CD => sua_rec.COURSE_CD,
534 X_UNIT_CD => sua_rec.UNIT_CD,
535 X_CAL_TYPE => sua_rec.CAL_TYPE,
536 X_CI_SEQUENCE_NUMBER => sua_rec.CI_SEQUENCE_NUMBER,
537 X_VERSION_NUMBER => sua_rec.VERSION_NUMBER,
538 X_LOCATION_CD => sua_rec.LOCATION_CD,
539 X_UNIT_CLASS => sua_rec.UNIT_CLASS,
540 X_CI_START_DT => sua_rec.CI_START_DT,
541 X_CI_END_DT => sua_rec.CI_END_DT,
542 X_UOO_ID => sua_rec.UOO_ID,
543 X_ENROLLED_DT => sua_rec.ENROLLED_DT,
544 X_UNIT_ATTEMPT_STATUS => sua_rec.UNIT_ATTEMPT_STATUS,
545 X_ADMINISTRATIVE_UNIT_STATUS => sua_rec.ADMINISTRATIVE_UNIT_STATUS,
546 X_ADMINISTRATIVE_PRIORITY => sua_rec.ADMINISTRATIVE_PRIORITY,
547 X_DISCONTINUED_DT => sua_rec.DISCONTINUED_DT,
548 X_DCNT_REASON_CD => sua_rec.DCNT_REASON_CD ,
549 X_RULE_WAIVED_DT => sua_rec.RULE_WAIVED_DT,
550 X_RULE_WAIVED_PERSON_ID => sua_rec.RULE_WAIVED_PERSON_ID,
551 X_NO_ASSESSMENT_IND => sua_rec.NO_ASSESSMENT_IND,
552 X_SUP_UNIT_CD => sua_rec.SUP_UNIT_CD,
553 X_SUP_VERSION_NUMBER => sua_rec.SUP_VERSION_NUMBER,
554 X_EXAM_LOCATION_CD => sua_rec.EXAM_LOCATION_CD,
555 X_ALTERNATIVE_TITLE => sua_rec.ALTERNATIVE_TITLE,
556 X_OVERRIDE_ENROLLED_CP => sua_rec.OVERRIDE_ENROLLED_CP,
557 X_OVERRIDE_EFTSU => sua_rec.OVERRIDE_EFTSU,
558 X_OVERRIDE_ACHIEVABLE_CP => sua_rec.OVERRIDE_ACHIEVABLE_CP,
559 X_OVERRIDE_OUTCOME_DUE_DT => sua_rec.OVERRIDE_OUTCOME_DUE_DT,
560 X_OVERRIDE_CREDIT_REASON => sua_rec.OVERRIDE_CREDIT_REASON,
561 X_WAITLIST_DT => sua_rec.WAITLIST_DT,
562 X_MODE => 'R' ,
563 -- added for enrollment processes dld nov2001 by smaddali
564 X_GS_VERSION_NUMBER => sua_rec.gs_version_number,
565 X_ENR_METHOD_TYPE => sua_rec.enr_method_type,
566 X_FAILED_UNIT_RULE => l_failed_rule,
567 X_CART => sua_rec.CART,
568 X_RSV_SEAT_EXT_ID => sua_rec.RSV_SEAT_EXT_ID,
569 X_ORG_UNIT_CD => sua_rec.ORG_UNIT_CD ,
570 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
571 X_SESSION_ID => sua_rec.session_id,
572 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
573 X_GRADING_SCHEMA_CODE => sua_rec.GRADING_SCHEMA_CODE,
574 -- Added the column deg_aud_detail_id as part of Degree Audit Interface build (Bug# 2033208)
575 -- by pradhakr
576 X_DEG_AUD_DETAIL_ID => sua_rec.DEG_AUD_DETAIL_ID,
577 X_SUBTITLE => sua_rec.subtitle,
578 X_STUDENT_CAREER_TRANSCRIPT => sua_rec.student_career_transcript,
579 X_STUDENT_CAREER_STATISTICS => sua_rec.student_career_statistics,
580 X_ATTRIBUTE_CATEGORY => sua_rec.attribute_category,
581 X_ATTRIBUTE1 => sua_rec.attribute1,
582 X_ATTRIBUTE2 => sua_rec.attribute2,
583 X_ATTRIBUTE3 => sua_rec.attribute3,
584 X_ATTRIBUTE4 => sua_rec.attribute4,
585 X_ATTRIBUTE5 => sua_rec.attribute5,
586 X_ATTRIBUTE6 => sua_rec.attribute6,
587 X_ATTRIBUTE7 => sua_rec.attribute7,
588 X_ATTRIBUTE8 => sua_rec.attribute8,
589 X_ATTRIBUTE9 => sua_rec.attribute9,
590 X_ATTRIBUTE10 => sua_rec.attribute10,
591 X_ATTRIBUTE11 => sua_rec.attribute11,
592 X_ATTRIBUTE12 => sua_rec.attribute12,
593 X_ATTRIBUTE13 => sua_rec.attribute13,
594 X_ATTRIBUTE14 => sua_rec.attribute14,
595 X_ATTRIBUTE15 => sua_rec.attribute15,
596 X_ATTRIBUTE16 => sua_rec.attribute16,
597 X_ATTRIBUTE17 => sua_rec.attribute17,
598 X_ATTRIBUTE18 => sua_rec.attribute18,
599 X_ATTRIBUTE19 => sua_rec.attribute19,
600 X_ATTRIBUTE20 => sua_rec.attribute20,
601 X_WAITLIST_MANUAL_IND => sua_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
602 X_WLST_PRIORITY_WEIGHT_NUM => sua_rec.wlst_priority_weight_num,
603 X_WLST_PREFERENCE_WEIGHT_NUM=> sua_rec.wlst_preference_weight_num,
604 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
605 X_CORE_INDICATOR_CODE => sua_rec.core_indicator_code
606 );
607
608 -- added for bug#1832130 enrollment processes dld
609 -- raise the workflow event to send a mail to the student when the
610 --student unit attempt status has changed from enrolled to invalid
611 IGS_EN_WORKFLOW.SUA_STATUS_CHANGE_MAIL(sua_rec.UNIT_ATTEMPT_STATUS,
612 sua_rec.PERSON_ID,sua_rec.UOO_ID);
613
614 -- If logging is required, then log the change to the passed log
615 IF (p_s_log_type IS NOT NULL AND
616 p_creation_dt IS NOT NULL) THEN
617 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
618 p_s_log_type,
619 p_creation_dt,
620 'INFO,' || p_person_id || ',' ||
621 p_course_cd ||',' ||
622 v_sua_rec.unit_cd || ',' ||
623 v_sua_rec.version_number || ',' ||
624 'INVALID,' || v_sua_rec.alternate_code || ',' ||
625 v_sua_rec.location_cd || ',' ||
626 v_sua_rec.unit_class,
627 'IGS_EN_UA_FAILES_STATUS_INVAL',
628 NULL);
629 END IF;
630 END IF;
631 -- Exit from loop and repeat entire select
632 v_do_cursor_again_ind := TRUE;
633 EXIT;
634 END IF;
635 END IF;
636 EXCEPTION
637 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
638
639 ROLLBACK TO sp_sua;
640 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
641 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_en_val_bulkrule.enrp_val_sca_urule.enrpl_enrolled_to_invalid.APP_EXP','Application Exception raised with code '||SQLCODE||' and error '||SQLERRM);
642 END IF;
643 l_message_text := FND_MESSAGE.GET;
644
645 IF l_message_text IS NULL THEN
646 l_message_text := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNEXPECTED_ERR');
647 END IF;
648 IF (p_s_log_type IS NOT NULL AND
649 p_creation_dt IS NOT NULL) THEN
650
651 l_log_msg := TRUE;
652
653 FOR i IN 1..l_counter_failure
654 LOOP
655 r_unit_att_failure_rec := t_unit_att_failure(i);
656 IF (r_unit_att_failure_rec.rv_uoo_id = v_sua_rec.uoo_id) THEN
657 l_log_msg := FALSE;
658 EXIT;
659 END IF;
660 END LOOP;
661
662 IF l_log_msg = TRUE THEN
663
664 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
665 p_s_log_type,
666 p_creation_dt,
667 'ERROR,' || p_person_id || ',' ||
668 p_course_cd ||',' ||
669 v_sua_rec.unit_cd || ',' ||
670 v_sua_rec.version_number || ',' ||
671 'ENROLLED,' || v_sua_rec.alternate_code || ',' ||
672 v_sua_rec.location_cd || ',' ||
673 v_sua_rec.unit_class,
674 NULL,
675 l_message_text);
676
677 r_unit_att_failure_rec.rv_uoo_id := v_sua_rec.uoo_id;
678 l_counter_failure := l_counter_failure + 1;
679 t_unit_att_failure(l_counter_failure) := r_unit_att_failure_rec;
680
681 END IF;
682 END IF;
683
684 WHEN OTHERS THEN
685
686 ROLLBACK TO sp_sua;
687 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
688 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_en_val_bulkrule.enrp_val_sca_urule.enrpl_enrolled_to_invalid.UNH_EXP','Unhandled Exception raised with code '||SQLCODE||' and error '||SQLERRM);
689 END IF;
690 l_message_text := FND_MESSAGE.GET;
691 IF l_message_text IS NULL THEN
692 l_message_text := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNEXPECTED_ERR');
693 END IF;
694 IF (p_s_log_type IS NOT NULL AND
695 p_creation_dt IS NOT NULL) THEN
696
697 l_log_msg := TRUE;
698
699 FOR i IN 1..l_counter_failure
700 LOOP
701 r_unit_att_failure_rec := t_unit_att_failure(i);
702 IF (r_unit_att_failure_rec.rv_uoo_id = v_sua_rec.uoo_id) THEN
703 l_log_msg := FALSE;
704 EXIT;
705 END IF;
706 END LOOP;
707
708 IF l_log_msg = TRUE THEN
709
710 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
711 p_s_log_type,
712 p_creation_dt,
713 'ERROR,' || p_person_id || ',' ||
714 p_course_cd ||',' ||
715 v_sua_rec.unit_cd || ',' ||
716 v_sua_rec.version_number || ',' ||
717 'ENROLLED,' || v_sua_rec.alternate_code || ',' ||
718 v_sua_rec.location_cd || ',' ||
719 v_sua_rec.unit_class,
720 NULL,
721 l_message_text);
722
723 r_unit_att_failure_rec.rv_uoo_id := v_sua_rec.uoo_id;
724 l_counter_failure := l_counter_failure + 1;
725 t_unit_att_failure(l_counter_failure) := r_unit_att_failure_rec;
726
727 END IF;
728 END IF;
729
730 END;
731 END LOOP;
732 IF (v_do_cursor_again_ind = FALSE) THEN
733 EXIT;
734 END IF;
735 END LOOP;
736
737 END;
738 END enrpl_enrolled_to_invalid;
739
740 /* --smaddali modified this procedure to add 3 new parameters and their use in code
741 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
742 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
743 --stutta 08-SEP-2004 Modified c_sua_sca as part of performance tuning bug #3869677
744 --stutta 21-FEB-2006 Modified c_sua_sca and added pl/sql logic as part of bug #5051047
745 --ckasu 25-APR-2006 Modfied as a part of bug#5191592.
746 */
747 -----------------------------------
748 PROCEDURE enrpl_invalid_to_enrolled (
749 p_acad_cal_type IN IGS_CA_INST.cal_type%TYPE,
750 p_acad_sequence_number IN IGS_CA_INST.sequence_number%TYPE,
751 p_person_id IN IGS_EN_STDNT_PS_ATT.person_id%TYPE,
752 p_course_cd IN IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
753 p_s_log_type IN IGS_GE_S_LOG.s_log_type%TYPE,
754 p_creation_dt IN IGS_GE_S_LOG.creation_dt%TYPE,
755 p_enrolled_rule_cutoff_dt IN IGS_EN_CAL_CONF.enrolled_rule_cutoff_dt_alias%TYPE,
756 --added for enrollment processes dld by smaddali bug#1832130
757 p_cal_type IN IGS_CA_INST.cal_type%TYPE ,
758 p_ci_sequence_number IN IGS_CA_INST.sequence_number%TYPE ,
759 p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE )
760 IS
761 BEGIN
762 DECLARE
763 v_counter NUMBER; -- keeps record of the number of records in the PL/SQL table
764 v_next_rec BOOLEAN; -- used to determine if the next record should be found
765 v_do_cursor_again_ind BOOLEAN;
766 v_last_date_to_enrolled IGS_CA_DA_INST_V.alias_val%TYPE;
767 v_validation_error BOOLEAN;
768 v_message_text VARCHAR2(2000);
769 v_validation_message_name VARCHAR2(30);
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;
779
780 t_unit_att_failure t_unit_attempt_typ;
781 r_unit_att_failure_rec r_unit_attempt_typ;
782 l_counter_failure NUMBER;
783 l_log_msg BOOLEAN;
784
785 -- exception declaration - used when updating sua.unit_attempt_status
786 e_resource_busy_exception EXCEPTION;
787 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
788
789 -- modified for enrollment processes build bug#1832130 by smaddali
790 -- to consider the new parameters added .
791 --The two selects are mutually exclusive ie at any time the calendar passed is
792 --either 'LOAD' or 'TEACHING' so this cursor selects the unit attempts
793 -- for that particular calendar passed as parameter
794
795 CURSOR c_sua_sca(cp_cal_cat igs_ca_type.s_cal_cat%TYPE, cp_cal_type igs_ca_inst.cal_type%TYPE, cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
796 --selects unit attempts when a teaching calendar is passed
797 SELECT sua.unit_cd,
798 sua.version_number sua_version_number,
799 sua.cal_type,
800 sua.ci_sequence_number,
801 sua.unit_class,
802 sua.location_cd,
803 sca.version_number sca_version_number,
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
813 AND sua.course_cd = p_course_cd
814 AND sua.unit_attempt_status = 'INVALID'
815 AND sca.person_id = sua.person_id
816 AND sca.course_cd = sua.course_cd
817 AND ci.sequence_number = sua.ci_sequence_number
818 AND ci.cal_type = sua.cal_type
819 AND uoo.uoo_id = sua.uoo_id
820 AND uoo.owner_org_unit_cd LIKE NVL(p_org_unit_cd,'%')
821 AND sua.cal_type LIKE NVL(cp_cal_type ,'%')
822 AND sua.ci_sequence_number = nvl( cp_ci_sequence_number ,sua.ci_sequence_number)
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,
832 IGS_CA_INST ci
833 WHERE sua.person_id = p_person_id AND
834 sua.course_cd = p_course_cd AND
835 sua.unit_attempt_status = 'INVALID' AND
836 sca.person_id = sua.person_id AND
837 sca.course_cd = sua.course_cd AND
838 ci.cal_type = sua.cal_type AND
839 ci.sequence_number = sua.ci_sequence_number AND
840 sua.unit_cd = cp_unit_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
850 daiv.ci_sequence_number = cp_sua_sequence_number AND
851 daiv.dt_alias = p_enrolled_rule_cutoff_dt;
852
853 /* smaddali bug#1832130 enrollment processes build nov 2001 release
854 added declarations for workflow events and new parameters added to this procedure */
855 CURSOR cur_cal_cat IS
856 SELECT s_cal_cat
857 FROM IGS_CA_TYPE
858 WHERE cal_type = p_cal_type ;
859
860 CURSOR c_load_to_teach IS
861 SELECT teach_cal_type,teach_ci_sequence_number
862 FROM IGS_CA_LOAD_TO_TEACH_V
863 WHERE load_cal_type = p_cal_type
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
873 -- 2. Re-check all INVALID UNIT attempts and switch to ENROLLED if the
874 -- rules are now passed. If an invalid unit passes all rules the status is
875 -- set to ENROLLED and the select is repeated. This repeats until no more
876 -- invalid unit attempts pass the unit rules.
877 --------------------------------------------------------------------------
878 v_counter := 0;
879 l_counter_failure := 0;
880
881 IF p_cal_type IS NULL THEN
882 l_cal_cat := 'TEACHING' ;
883 ELSE
884 OPEN cur_cal_cat ;
885 FETCH cur_cal_cat INTO l_cal_cat ;
886 CLOSE cur_cal_cat ;
887 END IF;
888
889 l_loop_ended := FALSE;
890
891 IF l_cal_cat = 'TEACHING' THEN
892 --
893 rec_teach.teach_cal_type := p_cal_type;
894 rec_teach.teach_ci_sequence_number := p_ci_sequence_number;
895 ELSIF l_cal_cat = 'LOAD' THEN
896 OPEN c_load_to_teach;
897 END IF;
898 LOOP -- loop to find each teach calendar associated with the load.
899
900 IF l_cal_cat = 'LOAD' THEN
901 --
902
903 FETCH c_load_to_teach INTO rec_teach;
904 IF c_load_to_teach%NOTFOUND THEN
905 --
906 l_loop_ended :=TRUE;
907 END IF;
908
909 END IF;
910 EXIT WHEN l_loop_ended = TRUE;
911 --
912
913 LOOP
914 v_do_cursor_again_ind := FALSE;
915 -- for eachof the unit attempts of the student program attempt
916 --validate the rule
917 -- get the calendar category of the calendar passed
918
919 FOR v_sua_sca_rec IN c_sua_sca(l_cal_cat, rec_teach.teach_cal_type, rec_teach.teach_ci_sequence_number)
920 LOOP
921 DECLARE
922 l_message_text VARCHAR2(1000);
923 BEGIN
924 SAVEPOINT sp_sua;
925 v_next_rec := FALSE;
926 -- unit attempts which can't be set to enrolled shouldn't be processed
927 -- again - would result in a never ending recursive loop.
928 FOR i IN 1..v_counter
929 LOOP
930 r_unit_attempt_rec := t_unit_attempt_tab(i);
931 IF (r_unit_attempt_rec.rv_uoo_id = v_sua_sca_rec.uoo_id) THEN
932 v_next_rec := TRUE;
933 EXIT;
934 END IF;
935 END LOOP;
936 --if unit attempt hasn't been processed before then only validate for rules
937 IF (v_next_rec = FALSE) THEN
938 -- sua record is not in the PL/SQL table
939 -- Select the last date to switch to ENROLLED from the teaching period
940 IF (p_enrolled_rule_cutoff_dt IS NOT NULL) THEN
941 OPEN c_daiv(v_sua_sca_rec.cal_type,v_sua_sca_rec.ci_sequence_number);
942 FETCH c_daiv INTO v_last_date_to_enrolled;
943 IF (c_daiv%NOTFOUND) THEN
944 v_last_date_to_enrolled := NULL;
945 END IF;
946 CLOSE c_daiv;
947 ELSE
948 v_last_date_to_enrolled := NULL;
949 END IF;
950
951 -- Check that there is still a unit Rule being failed for the UNIT
952 -- attempt. If not, it should be switched back to ENROLLED.
953 -- smaddali added two new parameters to this function bug#1832130
954
955 IF IGS_RU_VAL_UNIT_RULE.rulp_val_enrol_unit(
956 p_person_id,
957 p_course_cd,
958 -- Add the following one line of code. From Callista 2.0 18-May-2000
959 v_sua_sca_rec.sca_version_number,
960 v_sua_sca_rec.unit_cd,
961 v_sua_sca_rec.sua_version_number,
962 v_sua_sca_rec.cal_type,
963 v_sua_sca_rec.ci_sequence_number,
964 v_message_text ,
965 --added for enrollment processes dld by smaddali
966 v_sua_sca_rec.uoo_id,
967 l_failed_rule) = TRUE THEN
968 v_validation_error := FALSE;
969 IF (v_last_date_to_enrolled IS NOT NULL AND
970 SYSDATE > v_last_date_to_enrolled) THEN
971 -- if the enrollment cutoff date has passed then cannot
972 --change the unit to enrolled , so mark as validation error
973 v_validation_error := TRUE;
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
983 sua_rec.unit_attempt_status := 'ENROLLED' ;
984 -- Call the API to update the student unit attempt. This API is a
985 -- wrapper to the update row of the TBH.
986 igs_en_sua_api.update_unit_attempt(
987 X_ROWID => sua_rec.ROW_ID,
988 X_PERSON_ID => sua_rec.PERSON_ID,
989 X_COURSE_CD => sua_rec.COURSE_CD,
990 X_UNIT_CD => sua_rec.UNIT_CD,
991 X_CAL_TYPE => sua_rec.CAL_TYPE,
992 X_CI_SEQUENCE_NUMBER => sua_rec.CI_SEQUENCE_NUMBER,
993 X_VERSION_NUMBER => sua_rec.VERSION_NUMBER,
994 X_LOCATION_CD => sua_rec.LOCATION_CD,
995 X_UNIT_CLASS => sua_rec.UNIT_CLASS,
996 X_CI_START_DT => sua_rec.CI_START_DT,
997 X_CI_END_DT => sua_rec.CI_END_DT,
998 X_UOO_ID => sua_rec.UOO_ID,
999 X_ENROLLED_DT => sua_rec.ENROLLED_DT,
1000 X_UNIT_ATTEMPT_STATUS => sua_rec.UNIT_ATTEMPT_STATUS,
1001 X_ADMINISTRATIVE_UNIT_STATUS => sua_rec.ADMINISTRATIVE_UNIT_STATUS,
1002 X_ADMINISTRATIVE_PRIORITY => sua_rec.ADMINISTRATIVE_PRIORITY,
1003 X_DISCONTINUED_DT => sua_rec.DISCONTINUED_DT,
1004 X_DCNT_REASON_CD => sua_rec.DCNT_REASON_CD ,
1005 X_RULE_WAIVED_DT => sua_rec.RULE_WAIVED_DT,
1006 X_RULE_WAIVED_PERSON_ID => sua_rec.RULE_WAIVED_PERSON_ID,
1007 X_NO_ASSESSMENT_IND => sua_rec.NO_ASSESSMENT_IND,
1008 X_SUP_UNIT_CD => sua_rec.SUP_UNIT_CD,
1009 X_SUP_VERSION_NUMBER => sua_rec.SUP_VERSION_NUMBER,
1010 X_EXAM_LOCATION_CD => sua_rec.EXAM_LOCATION_CD,
1011 X_ALTERNATIVE_TITLE => sua_rec.ALTERNATIVE_TITLE,
1012 X_OVERRIDE_ENROLLED_CP => sua_rec.OVERRIDE_ENROLLED_CP,
1013 X_OVERRIDE_EFTSU => sua_rec.OVERRIDE_EFTSU,
1014 X_OVERRIDE_ACHIEVABLE_CP => sua_rec.OVERRIDE_ACHIEVABLE_CP,
1015 X_OVERRIDE_OUTCOME_DUE_DT => sua_rec.OVERRIDE_OUTCOME_DUE_DT,
1016 X_OVERRIDE_CREDIT_REASON => sua_rec.OVERRIDE_CREDIT_REASON,
1017 X_WAITLIST_DT => sua_rec.waitlist_dt,
1018 X_MODE => 'R' ,
1019 X_GS_VERSION_NUMBER => sua_rec.gs_version_number,
1020 X_ENR_METHOD_TYPE => sua_rec.enr_method_type,
1021 X_FAILED_UNIT_RULE => l_failed_rule ,
1022 X_CART => sua_rec.CART,
1023 X_RSV_SEAT_EXT_ID => sua_rec.RSV_SEAT_EXT_ID,
1024 X_ORG_UNIT_CD => sua_rec.ORG_UNIT_CD,
1025 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1026 X_SESSION_ID => sua_rec.session_id,
1027 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
1028 X_GRADING_SCHEMA_CODE => sua_rec.GRADING_SCHEMA_CODE,
1029 -- Added the column deg_aud_detail_id as part of Degree Audit Interface build (Bug# 2033208)
1030 -- by pradhakr
1031 X_DEG_AUD_DETAIL_ID => sua_rec.DEG_AUD_DETAIL_ID,
1032 X_SUBTITLE => sua_rec.subtitle,
1033 X_STUDENT_CAREER_TRANSCRIPT => sua_rec.student_career_transcript,
1034 X_STUDENT_CAREER_STATISTICS => sua_rec.student_career_statistics,
1035 X_ATTRIBUTE_CATEGORY => sua_rec.attribute_category,
1036 X_ATTRIBUTE1 => sua_rec.attribute1,
1037 X_ATTRIBUTE2 => sua_rec.attribute2,
1038 X_ATTRIBUTE3 => sua_rec.attribute3,
1039 X_ATTRIBUTE4 => sua_rec.attribute4,
1040 X_ATTRIBUTE5 => sua_rec.attribute5,
1041 X_ATTRIBUTE6 => sua_rec.attribute6,
1042 X_ATTRIBUTE7 => sua_rec.attribute7,
1043 X_ATTRIBUTE8 => sua_rec.attribute8,
1044 X_ATTRIBUTE9 => sua_rec.attribute9,
1045 X_ATTRIBUTE10 => sua_rec.attribute10,
1046 X_ATTRIBUTE11 => sua_rec.attribute11,
1047 X_ATTRIBUTE12 => sua_rec.attribute12,
1048 X_ATTRIBUTE13 => sua_rec.attribute13,
1049 X_ATTRIBUTE14 => sua_rec.attribute14,
1050 X_ATTRIBUTE15 => sua_rec.attribute15,
1051 X_ATTRIBUTE16 => sua_rec.attribute16,
1052 X_ATTRIBUTE17 => sua_rec.attribute17,
1053 X_ATTRIBUTE18 => sua_rec.attribute18,
1054 X_ATTRIBUTE19 => sua_rec.attribute19,
1055 X_ATTRIBUTE20 => sua_rec.attribute20,
1056 X_WAITLIST_MANUAL_IND => sua_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
1057 X_WLST_PRIORITY_WEIGHT_NUM => sua_rec.wlst_priority_weight_num,
1058 X_WLST_PREFERENCE_WEIGHT_NUM=> sua_rec.wlst_preference_weight_num,
1059 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
1060 X_CORE_INDICATOR_CODE => sua_rec.core_indicator_code
1061 );
1062
1063 END IF;
1064 IF (v_validation_error = FALSE) THEN
1065
1066 -- ie unt attempt has passed rules so changed to enrolled
1067 -- so check for other validations
1068 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_unit(
1069 p_person_id,p_course_cd,v_sua_sca_rec.unit_cd,
1070 SYSDATE,v_message_name) = FALSE) THEN
1071 v_validation_error := TRUE;
1072 v_validation_message_name := 'IGS_EN_UA_NOFAIL_STUD_EXCL';
1073
1074 ELSIF (IGS_EN_VAL_SUA.enrp_val_sua_intrmt(
1075 p_person_id,p_course_cd,v_sua_sca_rec.cal_type,
1076 v_sua_sca_rec.ci_sequence_number,v_message_name) = FALSE) THEN
1077 v_validation_error := TRUE;
1078 v_validation_message_name := 'IGS_EN_UA_NOFAIL_UNIT_ATT';
1079
1080 ELSIF (IGS_EN_VAL_SUA.enrp_val_sua_advstnd( p_person_id,p_course_cd,
1081 v_sua_sca_rec.sca_version_number, v_sua_sca_rec.unit_cd,
1082 v_sua_sca_rec.sua_version_number,
1083 v_message_name , 'N') = FALSE AND v_message_name <> 'IGS_EN_STUD_APPROVED_ADVSTD') THEN
1084 v_validation_error := TRUE;
1085 v_validation_message_name := 'IGS_EN_UA_NOFAIL_UNIT_EXISTS';
1086
1087 ELSIF (IGS_EN_VAL_SUA.enrp_val_coo_mode( v_sua_sca_rec.coo_id,
1088 v_sua_sca_rec.unit_class,v_message_name) = FALSE OR
1089 IGS_EN_VAL_SUA.enrp_val_coo_loc( v_sua_sca_rec.coo_id,
1090 v_sua_sca_rec.location_cd,v_message_name) = FALSE OR
1091 IGS_EN_VAL_SCA.enrp_val_coo_att(
1092 p_person_id,v_sua_sca_rec.coo_id,v_sua_sca_rec.cal_type,
1093 v_sua_sca_rec.ci_sequence_number,v_message_name,
1094 v_attendance_types,
1095 nvl(p_cal_type,v_sua_sca_rec.cal_type),
1096 nvl(p_ci_sequence_number,v_sua_sca_rec.ci_sequence_number)) = FALSE) THEN
1097 v_validation_error := TRUE;
1098 v_validation_message_name := 'IGS_EN_UA_NOFAIL_BREACHES_FND';
1099
1100 END IF;
1101 END IF;
1102
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 */
1112 sua_rec.unit_attempt_status := 'INVALID';
1113 -- Call the API to update the student unit attempt. This API is a
1114 -- wrapper to the update row of the TBH.
1115 igs_en_sua_api.update_unit_attempt(
1116 X_ROWID => sua_rec.ROW_ID,
1117 X_PERSON_ID => sua_rec.PERSON_ID,
1118 X_COURSE_CD => sua_rec.COURSE_CD,
1119 X_UNIT_CD => sua_rec.UNIT_CD,
1120 X_CAL_TYPE => sua_rec.CAL_TYPE,
1121 X_CI_SEQUENCE_NUMBER => sua_rec.CI_SEQUENCE_NUMBER,
1122 X_VERSION_NUMBER => sua_rec.VERSION_NUMBER,
1123 X_LOCATION_CD => sua_rec.LOCATION_CD,
1124 X_UNIT_CLASS => sua_rec.UNIT_CLASS,
1125 X_CI_START_DT => sua_rec.CI_START_DT,
1126 X_CI_END_DT => sua_rec.CI_END_DT,
1127 X_UOO_ID => sua_rec.UOO_ID,
1128 X_ENROLLED_DT => sua_rec.ENROLLED_DT,
1129 X_UNIT_ATTEMPT_STATUS => sua_rec.UNIT_ATTEMPT_STATUS,
1130 X_ADMINISTRATIVE_UNIT_STATUS => sua_rec.ADMINISTRATIVE_UNIT_STATUS,
1131 X_ADMINISTRATIVE_PRIORITY => sua_rec.ADMINISTRATIVE_PRIORITY,
1132 X_DISCONTINUED_DT => sua_rec.DISCONTINUED_DT,
1133 X_DCNT_REASON_CD => sua_rec.DCNT_REASON_CD ,
1134 X_RULE_WAIVED_DT => sua_rec.RULE_WAIVED_DT,
1135 X_RULE_WAIVED_PERSON_ID => sua_rec.RULE_WAIVED_PERSON_ID,
1136 X_NO_ASSESSMENT_IND => sua_rec.NO_ASSESSMENT_IND,
1137 X_SUP_UNIT_CD => sua_rec.SUP_UNIT_CD,
1138 X_SUP_VERSION_NUMBER => sua_rec.SUP_VERSION_NUMBER,
1139 X_EXAM_LOCATION_CD => sua_rec.EXAM_LOCATION_CD,
1140 X_ALTERNATIVE_TITLE => sua_rec.ALTERNATIVE_TITLE,
1141 X_OVERRIDE_ENROLLED_CP => sua_rec.OVERRIDE_ENROLLED_CP,
1142 X_OVERRIDE_EFTSU => sua_rec.OVERRIDE_EFTSU,
1143 X_OVERRIDE_ACHIEVABLE_CP => sua_rec.OVERRIDE_ACHIEVABLE_CP,
1144 X_OVERRIDE_OUTCOME_DUE_DT => sua_rec.OVERRIDE_OUTCOME_DUE_DT,
1145 X_OVERRIDE_CREDIT_REASON => sua_rec.OVERRIDE_CREDIT_REASON,
1146 X_WAITLIST_DT => sua_rec.waitlist_dt,
1147 X_MODE => 'R' ,
1148 --added the new fields for enrollment processes dld
1149 X_GS_VERSION_NUMBER => sua_rec.gs_version_number,
1150 X_ENR_METHOD_TYPE => sua_rec.enr_method_type,
1151 X_FAILED_UNIT_RULE => sua_rec.failed_unit_rule,
1152 X_CART => sua_rec.CART,
1153 X_RSV_SEAT_EXT_ID => sua_rec.RSV_SEAT_EXT_ID ,
1154 X_ORG_UNIT_CD => sua_rec.ORG_UNIT_CD,
1155 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1156 X_SESSION_ID => sua_rec.session_id,
1157 -- Added the column grading schema as a part pf the bug 2037897. - aiyer
1158 X_GRADING_SCHEMA_CODE => sua_rec.GRADING_SCHEMA_CODE,
1159 -- Added the column deg_aud_detail_id as part of Degree Audit Interface build (Bug# 2033208)
1160 -- by pradhakr
1161 X_DEG_AUD_DETAIL_ID => sua_rec.DEG_AUD_DETAIL_ID,
1162 X_SUBTITLE => sua_rec.subtitle,
1163 X_STUDENT_CAREER_TRANSCRIPT => sua_rec.student_career_transcript,
1164 X_STUDENT_CAREER_STATISTICS => sua_rec.student_career_statistics,
1165 X_ATTRIBUTE_CATEGORY => sua_rec.attribute_category,
1166 X_ATTRIBUTE1 => sua_rec.attribute1,
1167 X_ATTRIBUTE2 => sua_rec.attribute2,
1168 X_ATTRIBUTE3 => sua_rec.attribute3,
1169 X_ATTRIBUTE4 => sua_rec.attribute4,
1170 X_ATTRIBUTE5 => sua_rec.attribute5,
1171 X_ATTRIBUTE6 => sua_rec.attribute6,
1172 X_ATTRIBUTE7 => sua_rec.attribute7,
1173 X_ATTRIBUTE8 => sua_rec.attribute8,
1174 X_ATTRIBUTE9 => sua_rec.attribute9,
1175 X_ATTRIBUTE10 => sua_rec.attribute10,
1176 X_ATTRIBUTE11 => sua_rec.attribute11,
1177 X_ATTRIBUTE12 => sua_rec.attribute12,
1178 X_ATTRIBUTE13 => sua_rec.attribute13,
1179 X_ATTRIBUTE14 => sua_rec.attribute14,
1180 X_ATTRIBUTE15 => sua_rec.attribute15,
1181 X_ATTRIBUTE16 => sua_rec.attribute16,
1182 X_ATTRIBUTE17 => sua_rec.attribute17,
1183 X_ATTRIBUTE18 => sua_rec.attribute18,
1184 X_ATTRIBUTE19 => sua_rec.attribute19,
1185 X_ATTRIBUTE20 => sua_rec.attribute20,
1186 X_WAITLIST_MANUAL_IND => sua_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
1187 X_WLST_PRIORITY_WEIGHT_NUM => sua_rec.wlst_priority_weight_num,
1188 X_WLST_PREFERENCE_WEIGHT_NUM=> sua_rec.wlst_preference_weight_num,
1189 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
1190 X_CORE_INDICATOR_CODE => sua_rec.core_indicator_code
1191 ) ;
1192 -- If logging is required, then log the change to the passed log
1193 IF (p_s_log_type IS NOT NULL AND
1194 p_creation_dt IS NOT NULL) THEN
1195 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1196 p_s_log_type,
1197 p_creation_dt,
1198 'WARNING,' || p_person_id || ',' ||
1199 p_course_cd ||',' ||
1200 v_sua_sca_rec.unit_cd || ',' ||
1201 v_sua_sca_rec.sua_version_number || ',' ||
1202 'INVALID,' || v_sua_sca_rec.alternate_code || ',' ||
1203 v_sua_sca_rec.location_cd || ',' ||
1204 v_sua_sca_rec.unit_class,
1205 v_validation_message_name,
1206 NULL);
1207
1208 END IF;
1209 -- the unit attempt has been validated to invalid
1210 -- Store unit_cd, cal_type, ci_sequence_number in PL/SQL table
1211 r_unit_attempt_rec.rv_uoo_id := v_sua_sca_rec.uoo_id;
1212 v_counter := v_counter + 1;
1213 t_unit_attempt_tab(v_counter) := r_unit_attempt_rec;
1214 ELSE
1215
1216 --ie the rules have been passed and no other validation errors
1217 --then log an entry that the unit attempt has successfully changed to enrolled
1218 IF (p_s_log_type IS NOT NULL AND
1219 p_creation_dt IS NOT NULL) THEN
1220
1221 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1222 p_s_log_type,
1223 p_creation_dt,
1224 'INFO,' || p_person_id || ',' ||
1225 p_course_cd ||',' ||
1226 v_sua_sca_rec.unit_cd || ',' ||
1227 v_sua_sca_rec.sua_version_number || ',' ||
1228 'ENROLLED,' || v_sua_sca_rec.alternate_code || ',' ||
1229 v_sua_sca_rec.location_cd || ',' ||
1230 v_sua_sca_rec.unit_class,
1231 'IGS_EN_UA_NOFAIL_CHG_ENROLL',
1232 NULL);
1233
1234 END IF;
1235 -- added for bug#1832130 enrollment processes dld
1236 --since the student unit attempt status has changed from invalid to enrolled
1237 --we have to raise a workflow event to send a mail to the student about the status
1238 IGS_EN_WORKFLOW.SUA_STATUS_CHANGE_MAIL(sua_rec.UNIT_ATTEMPT_STATUS,
1239 sua_rec.PERSON_ID,sua_rec.UOO_ID);
1240 END IF;
1241 -- Exit from loop and repeat entire select
1242 v_do_cursor_again_ind := TRUE;
1243 EXIT;
1244 END IF;
1245 END IF;
1246
1247 EXCEPTION
1248 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1249 ROLLBACK TO sp_sua;
1250 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1251 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_en_val_bulkrule.enrp_val_sca_urule.enrpl_invalid_to_enrolled.APP_EXP','Application Exception raised with code '||SQLCODE||' and error '||SQLERRM);
1252 END IF;
1253 l_message_text := FND_MESSAGE.GET;
1254
1255 IF l_message_text IS NULL THEN
1256 l_message_text := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNEXPECTED_ERR');
1257 END IF;
1258 IF (p_s_log_type IS NOT NULL AND
1259 p_creation_dt IS NOT NULL) THEN
1260
1261 l_log_msg := TRUE;
1262
1263 FOR i IN 1..l_counter_failure
1264 LOOP
1265 r_unit_att_failure_rec := t_unit_att_failure(i);
1266 IF (r_unit_att_failure_rec.rv_uoo_id = v_sua_sca_rec.uoo_id) THEN
1267 l_log_msg := FALSE;
1268 EXIT;
1269 END IF;
1270 END LOOP;
1271
1272 IF l_log_msg = TRUE THEN
1273
1274 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1275 p_s_log_type,
1276 p_creation_dt,
1277 'ERROR,' || p_person_id || ',' ||
1278 p_course_cd ||',' ||
1279 v_sua_sca_rec.unit_cd || ',' ||
1280 v_sua_sca_rec.sua_version_number || ',' ||
1281 'INVALID,' || v_sua_sca_rec.alternate_code || ',' ||
1282 v_sua_sca_rec.location_cd || ',' ||
1283 v_sua_sca_rec.unit_class,
1284 NULL,
1285 l_message_text);
1286
1287 r_unit_att_failure_rec.rv_uoo_id := v_sua_sca_rec.uoo_id;
1288 l_counter_failure := l_counter_failure + 1;
1289 t_unit_att_failure(l_counter_failure) := r_unit_att_failure_rec;
1290
1291 END IF;
1292 END IF;
1293
1294 WHEN OTHERS THEN
1295 ROLLBACK TO sp_sua;
1296 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1297 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,'igs.plsql.igs_en_val_bulkrule.enrp_val_sca_urule.enrpl_invalid_to_enrolled.UNH_EXP','Unhandled Exception raised with code '||SQLCODE||' and error '||SQLERRM);
1298 END IF;
1299 l_message_text := FND_MESSAGE.GET;
1300 IF l_message_text IS NULL THEN
1301 l_message_text := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNEXPECTED_ERR');
1302 END IF;
1303 IF (p_s_log_type IS NOT NULL AND
1304 p_creation_dt IS NOT NULL) THEN
1305
1306 l_log_msg := TRUE;
1307
1308 FOR i IN 1..l_counter_failure
1309 LOOP
1310 r_unit_att_failure_rec := t_unit_att_failure(i);
1311 IF (r_unit_att_failure_rec.rv_uoo_id = v_sua_sca_rec.uoo_id) THEN
1312 l_log_msg := FALSE;
1313 EXIT;
1314 END IF;
1315 END LOOP;
1316
1317 IF l_log_msg = TRUE THEN
1318
1319 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1320 p_s_log_type,
1321 p_creation_dt,
1322 'ERROR,' || p_person_id || ',' ||
1323 p_course_cd ||',' ||
1324 v_sua_sca_rec.unit_cd || ',' ||
1325 v_sua_sca_rec.sua_version_number || ',' ||
1326 'INVALID,' || v_sua_sca_rec.alternate_code || ',' ||
1327 v_sua_sca_rec.location_cd || ',' ||
1328 v_sua_sca_rec.unit_class,
1329 NULL,
1330 l_message_text);
1331
1332 r_unit_att_failure_rec.rv_uoo_id := v_sua_sca_rec.uoo_id;
1333 l_counter_failure := l_counter_failure + 1;
1334 t_unit_att_failure(l_counter_failure) := r_unit_att_failure_rec;
1335 END IF;
1336
1337 END IF;
1338 END;
1339
1340 END LOOP;
1341 IF (v_do_cursor_again_ind = FALSE) THEN
1342 EXIT;
1343 END IF;
1344 END LOOP;
1345
1346 IF l_cal_cat = 'TEACHING' THEN
1347 l_loop_ended := TRUE;
1348 END IF;
1349 END LOOP; -- loop for each teach cal of a load.
1350 IF c_load_to_teach%ISOPEN THEN
1351 CLOSE c_load_to_teach;
1352 END IF;
1353
1354 END;
1355 END enrpl_invalid_to_enrolled;
1356 ------------------------------
1357 BEGIN
1358 -------------------------------------------------------------------------------
1359 -- This procedure validates the unit rules for all unit attempts within a
1360 -- nominated student Course attempt for a nominated academic calendar instance.
1361 -- It has been designed to accept S_LOG type and creation_dt to enable any
1362 -- output to be inserted into these. If null, nothing will be logged.
1363 -------------------------------------------------------------------------------
1364 -- 1. Select the date fields representing the date after which units cannot be
1365 -- set to ENROLLED, and the date after which units can not be set to INVALID.
1366 ------------------------------------------------------------------------------
1367
1368 OPEN c_secc;
1369 FETCH c_secc INTO v_enrolled_rule_cutoff_dt,
1370 v_invalid_rule_cutoff_dt;
1371 IF (c_secc%NOTFOUND) THEN
1372 CLOSE c_secc;
1373 RAISE e_no_records_found;
1374 END IF;
1375 CLOSE c_secc;
1376 -- added this if condition for enrollment processes dld bug#1832130
1377 --can enroll an invalid unit attempt only when the user passes all rules
1378
1379 IF (p_rule_to_be_validated= 'ALL' ) THEN
1380 enrpl_invalid_to_enrolled(
1381 p_acad_cal_type,
1382 p_acad_sequence_number,
1383 p_person_id,
1384 p_course_cd,
1385 p_s_log_type,
1386 p_creation_dt,
1387 v_enrolled_rule_cutoff_dt,
1388 --added 3 parameters for enrollment processes dld bug#1832130
1389 p_cal_type,
1390 p_ci_sequence_number,
1391 p_org_unit_cd );
1392 END IF;
1393 --validate the passed rule for all enrolled unit attempts and make them
1394 --invalid if the rule fails
1395 enrpl_enrolled_to_invalid(
1396 p_acad_cal_type,
1397 p_acad_sequence_number,
1398 p_person_id,
1399 p_course_cd,
1400 p_s_log_type,
1401 p_creation_dt,
1402 v_invalid_rule_cutoff_dt,
1403 --added 4 parameters for enrollment processes dld bug#1832130
1404 p_cal_type ,
1405 p_ci_sequence_number,
1406 p_org_unit_cd,
1407 p_rule_to_be_validated);
1408 EXCEPTION
1409 WHEN e_no_records_found THEN
1410 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
1411 IGS_GE_MSG_STACK.ADD;
1412 App_Exception.Raise_Exception;
1413 END;
1414 END enrp_val_sca_urule;
1415 END IGS_EN_VAL_BULKRULE;
[Home] [Help]