1 PACKAGE BODY IGS_RU_VAL_UNIT_RULE AS
2 /* $Header: IGSRU09B.pls 120.1 2005/12/13 03:58:46 appldev ship $ */
3 /* smaddali :modified all the functions to add four new parameters
4 during enrollment Processes build nov2001 bug#1832130
5 smaddali 15-oct-04 Modified for bug#3954071 -
6 load calendar being passed to rules engine instead of teaching calendar*/
7
8 -- added new for enrollment processes dld bug#1832130
9 -- all thebelow declerations and cursors
10
11 -- selects the rule number defined for a given unit section
12 CURSOR cur_usec_rule (cp_uoo_id igs_ps_usec_ru.uoo_id%TYPE ,
13 cp_rul_call_cd igs_ps_usec_ru.s_rule_call_cd%TYPE ) IS
14 SELECT s_rule_call_cd , rul_sequence_number
15 FROM igs_ps_usec_ru
16 WHERE uoo_id = cp_uoo_id
17 AND s_rule_call_cd = cp_rul_call_cd;
18
19 --selects the rule number defined for the unit version
20 CURSOR cur_uver_rule (cp_unit_cd igs_ps_unit_ver_ru.unit_cd%TYPE,
21 cp_version_number igs_ps_unit_ver_ru.version_number%TYPE,
22 cp_rul_call_cd igs_ps_unit_ver_ru.s_rule_call_cd%TYPE ) IS
23 SELECT s_rule_call_cd , rul_sequence_number
24 FROM igs_ps_unit_ver_ru
25 WHERE unit_cd = cp_unit_cd
26 AND version_number = cp_version_number
27 AND s_rule_call_cd = cp_rul_call_cd;
28
29 --select all the active program attempts for this student
30 CURSOR cur_std_psatt(cp_person_id igs_en_stdnt_ps_att_all.person_id%TYPE) IS
31 SELECT ps.course_cd , ps.version_number
32 FROM igs_en_stdnt_ps_att ps
33 WHERE ps.person_id = cp_person_id
34 AND ps.course_attempt_status IN ('ENROLLED' ,'INACTIVE')
35 AND EXISTS ( SELECT 1 FROM igs_en_su_attempt su
36 WHERE su.person_id=ps.person_id
37 AND su.course_cd =ps.course_cd )
38 ORDER BY course_cd desc;
39
40 cst_active CONSTANT IGS_CA_INST.cal_status%TYPE := 'ACTIVE';
41 CURSOR cur_load(cp_teach_cal_type igs_ca_inst.cal_type%TYPE,
42 cp_teach_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
43 SELECT cv.load_cal_type ,
44 cv.load_ci_sequence_number
45 FROM igs_ca_teach_to_load_v cv,
46 IGS_CA_INST ci,
47 IGS_CA_STAT cs
48 WHERE cv.teach_cal_type = cp_teach_cal_type
49 AND cv.teach_ci_sequence_number = cp_teach_ci_sequence_number
50 AND ci.cal_type = cv.load_cal_type
51 AND ci.sequence_number = cv.load_ci_sequence_number
52 AND ci.cal_status = cs.cal_status
53 AND cs.s_cal_status = cst_active
54 ORDER BY load_start_dt ASC;
55
56 /*
57 Validate co-requisite rules for a student unit attempt
58 smaddali modified the code for enrollment processes dld
59 bug#1832130
60 */
61 /*-------------------------------------------------------------------------------------
62 --who when what
63 smaddali 15-oct-04 Modified the logic for bug#3954071 to pass the load calendar while checking for step override
64 --bdeviset 12-DEC-2005 Passing extra parameter p_param_8 ( in which the uoo_is is passed)
65 -- while calling rules engine for Bug# 4304688
66 ------------------------------------------------------------------------------------- */
67
68 FUNCTION RULP_VAL_COREQ(
69 p_person_id IN NUMBER ,
70 p_course_cd IN VARCHAR2 ,
71 p_unit_cd IN VARCHAR2 ,
72 p_cal_type IN VARCHAR2 ,
73 p_ci_sequence_number IN NUMBER ,
74 p_message_text OUT NOCOPY VARCHAR2 ,
75 -- added new parameters for bug#1832130
76 p_course_version IN NUMBER DEFAULT NULL,
77 p_unit_ver IN NUMBER DEFAULT NULL,
78 p_uoo_id IN NUMBER DEFAULT NULL,
79 p_rule_failed OUT NOCOPY VARCHAR2)
80 RETURN boolean IS
81
82
83 l_term_cal igs_ca_inst.cal_type%TYPE;
84 l_term_seq_number igs_ca_inst.sequence_number%TYPE;
85 v_return_val VARCHAR2(30);
86 l_step_override_limit IGS_EN_ELGB_OVR_UOO.step_override_limit%TYPE ;
87 --l_step_override_limit igs_en_elgb_ovr_step.step_override_limit%TYPE ;
88 l_rule_call_cd igs_ps_usec_ru.s_rule_call_cd%TYPE DEFAULT NULL;
89 l_rul_seq_number igs_ps_usec_ru.rul_sequence_number%TYPE DEFAULT NULL;
90
91 BEGIN
92 --cursor to fetch the term calendar from the unit section teach calendar
93 --this cursor is ordered by the load cal start date to fetch the
94 --first term cal if a teching period spans across multiple terms.
95 l_term_cal := NULL;
96 l_term_seq_number := NULL;
97 OPEN cur_load(p_cal_type, p_ci_sequence_number);
98 FETCH cur_load INTO l_term_cal, l_term_seq_number;
99 CLOSE cur_load;
100
101 --added this code for enrollment processes dld bug#1832130
102 -- if the rule validation is overridden for the student then return true
103 IF( IGS_EN_GEN_015.validation_step_is_overridden(
104 p_eligibility_step_type => 'COREQ',
105 p_load_cal_type => l_term_cal,
106 p_load_cal_seq_number => l_term_seq_number,
107 p_person_id => p_person_id,
108 p_uoo_id => p_uoo_id,
109 p_step_override_limit => l_step_override_limit)) THEN
110 p_rule_failed := NULL;
111 p_message_text := NULL ;
112 RETURN TRUE ;
113 ELSE
114 -- check if this rule is defined for the unit section
115 OPEN cur_usec_rule(p_uoo_id,'USECCOREQ' ) ;
116 FETCH cur_usec_rule INTO l_rule_call_cd , l_rul_seq_number ;
117 IF cur_usec_rule%NOTFOUND THEN
118 -- if this rule is not defined for the unit section then
119 --check if this rule is defined for the unit version
120 OPEN cur_uver_rule(p_unit_cd,p_unit_ver,'COREQ' ) ;
121 FETCH cur_uver_rule INTO l_rule_call_cd , l_rul_seq_number ;
122 CLOSE cur_uver_rule ;
123 END IF;
124 CLOSE cur_usec_rule ;
125 -- if no rule is defined for either the unit section or the unit version
126 -- then return true
127 IF l_rule_call_cd IS NULL THEN
128 p_rule_failed := NULL ;
129 p_message_text := NULL ;
130 RETURN TRUE ;
131 END IF;
132 -- else check if the rule is satisfied for any of
133 -- the active program attempts of the student
134 FOR std_psatt_rec IN cur_std_psatt(p_person_id)
135 LOOP
136 v_return_val := IGS_RU_GEN_001.RULP_VAL_SENNA(
137 p_rule_call_name => l_rule_call_cd,
138 p_person_id => p_person_id,
139 p_course_cd => std_psatt_rec.course_cd,
140 p_course_version => std_psatt_rec.version_number,
141 p_unit_cd => p_unit_cd,
142 p_unit_version => p_unit_ver,
143 p_cal_type => p_cal_type,
144 p_ci_sequence_number => p_ci_sequence_number,
145 p_message => p_message_text ,
146 p_rule_number => l_rul_seq_number,
147 p_param_8 => p_uoo_id);
148 -- if the rule is satisfied for any of the program attemptsthen return true
149 IF v_return_val = 'true' THEN
150 p_rule_failed := NULL;
151 p_message_text := NULL ;
152 RETURN TRUE ;
153 END IF;
154 END LOOP ;
155 -- Rule has been checked against all the program attempts and
156 --is not satisfied for any
157 p_rule_failed := 'CO-REQ' ;
158 RETURN FALSE;
159 END IF;
160 END rulp_val_coreq;
161 /*
162 To validate for incompatible student unit attempts
163 smaddali modified the code for enrollment processes dld
164 bug#1832130
165 */
166 /*-------------------------------------------------------------------------------------
167 --who when what
168 smaddali 15-oct-04 Modified the logic for bug#3954071 to pass the load calendar while checking for step override
169 ------------------------------------------------------------------------------------- */
170
171 FUNCTION RULP_VAL_INCOMP(
172 p_person_id IN NUMBER ,
173 p_course_cd IN VARCHAR2 ,
174 p_unit_cd IN VARCHAR2 ,
175 p_cal_type IN VARCHAR2 ,
176 p_ci_sequence_number IN NUMBER ,
177 p_message_text OUT NOCOPY VARCHAR2 ,
178 -- added new parameters for bug#1832130
179 p_course_version IN NUMBER DEFAULT NULL,
180 p_unit_ver IN NUMBER DEFAULT NULL,
181 p_uoo_id IN NUMBER DEFAULT NULL,
182 p_rule_failed OUT NOCOPY VARCHAR2)
183 RETURN boolean IS
184
185 l_term_cal igs_ca_inst.cal_type%TYPE;
186 l_term_seq_number igs_ca_inst.sequence_number%TYPE;
187 v_return_val VARCHAR2(30);
188 l_step_override_limit IGS_EN_ELGB_OVR_UOO.step_override_limit%TYPE ;
189 --l_step_override_limit igs_en_elgb_ovr_step.step_override_limit%TYPE ;
190 l_rule_call_cd igs_ps_usec_ru.s_rule_call_cd%TYPE DEFAULT NULL;
191 l_rul_seq_number igs_ps_usec_ru.rul_sequence_number%TYPE DEFAULT NULL;
192
193 BEGIN
194
195 --cursor to fetch the term calendar from the unit section teach calendar
196 --this cursor is ordered by the load cal start date to fetch the
197 --first term cal if a teching period spans across multiple terms.
198 l_term_cal := NULL;
199 l_term_seq_number := NULL;
200 OPEN cur_load(p_cal_type, p_ci_sequence_number);
201 FETCH cur_load INTO l_term_cal, l_term_seq_number;
202 CLOSE cur_load;
203
204 -- if the rule validation is overridden for the student then return true
205 IF( IGS_EN_GEN_015.validation_step_is_overridden(p_eligibility_step_type => 'INCMPT_UNT',
206 p_load_cal_type => l_term_cal,
207 p_load_cal_seq_number => l_term_seq_number,
208 p_person_id => p_person_id,
209 p_uoo_id => p_uoo_id,
210 p_step_override_limit => l_step_override_limit)) THEN
211 p_rule_failed := NULL;
212 p_message_text := NULL ;
213 RETURN TRUE ;
214 ELSE
215 --check if this rule is defined for the unit version
216 OPEN cur_uver_rule(p_unit_cd,p_unit_ver,'INCOMP' ) ;
217 FETCH cur_uver_rule INTO l_rule_call_cd , l_rul_seq_number ;
218 CLOSE cur_uver_rule ;
219
220 -- if this rule is not defined for the unit version then return true
221 IF l_rule_call_cd IS NULL THEN
222 p_rule_failed := NULL ;
223 p_message_text := NULL ;
224 RETURN TRUE ;
225 END IF;
226 -- for each of the program attempt of the student check if the
227 --rule is satisfied
228 FOR std_psatt_rec IN cur_std_psatt(p_person_id)
229 LOOP
230 v_return_val := IGS_RU_GEN_001.RULP_VAL_SENNA(p_rule_call_name => l_rule_call_cd,
231 p_person_id => p_person_id,
232 p_course_cd => std_psatt_rec.course_cd,
233 p_course_version => std_psatt_rec.version_number,
234 p_unit_cd => p_unit_cd,
235 p_unit_version => p_unit_ver,
236 p_cal_type => p_cal_type,
237 p_ci_sequence_number => p_ci_sequence_number,
238 p_message => p_message_text ,
239 p_rule_number => l_rul_seq_number );
240 -- if the rule is not satisfied for any of the program attempts then return false
241 IF v_return_val = 'false' THEN
242 p_rule_failed := 'INCOMP' ;
243 RETURN FALSE;
244 END IF;
245 END LOOP ;
246 -- Rule has been checked against all the program attempts and
247 --is satisfied for all
248 p_rule_failed := NULL;
249 p_message_text := NULL ;
250 RETURN TRUE ;
251 END IF;
252 END rulp_val_incomp;
253 /*
254 Validate the pre-requisite rules for a student unit attempt
255 smaddali modified the code for enrollment processes dld
256 bug#1832130
257 */
258 /*-------------------------------------------------------------------------------------
259 --who when what
260 --svanukur 23-May-2003 Redefined l_step_override_limit to refer to igs_en_elgb_ovr_uoo
261 -- as part of Deny/War behaviour build # 2829272
262 --stutta 21-Sep-2004 Passing p_param_5 as 'Y' in call to rulp_val_senna to
263 -- which is used by PREDICTED_ENROLLED rule component to
264 -- include enrolled units. Bug # 3703355
265 -- smaddali 15-oct-04 Modified the logic for bug#3954071 to pass the load calendar while checking for step override
266 -- bdeviset 12-DEC-2005 Passing extra parameter p_param_8 ( in which the uoo_is is passed)
267 -- while calling rules engine for Bug# 4304688
268 ------------------------------------------------------------------------------------- */
269 FUNCTION RULP_VAL_PREREQ(
270 p_person_id IN NUMBER ,
271 p_course_cd IN VARCHAR2 ,
272 p_unit_cd IN VARCHAR2 ,
273 p_cal_type IN VARCHAR2 ,
274 p_ci_sequence_number IN NUMBER ,
275 p_message_text OUT NOCOPY VARCHAR2 ,
276 -- added new parameters for bug#1832130
277 p_course_version IN NUMBER DEFAULT NULL,
278 p_unit_ver IN NUMBER DEFAULT NULL,
279 p_uoo_id IN NUMBER DEFAULT NULL,
280 p_rule_failed OUT NOCOPY VARCHAR2)
281 RETURN boolean IS
282
283 l_term_cal igs_ca_inst.cal_type%TYPE;
284 l_term_seq_number igs_ca_inst.sequence_number%TYPE;
285
286 v_return_val VARCHAR2(30);
287 l_step_override_limit IGS_EN_ELGB_OVR_UOO.step_override_limit%TYPE ;
288 l_rule_call_cd igs_ps_usec_ru.s_rule_call_cd%TYPE DEFAULT NULL;
289 l_rul_seq_number igs_ps_usec_ru.rul_sequence_number%TYPE DEFAULT NULL;
290
291 BEGIN
292
293 --cursor to fetch the term calendar from the unit section teach calendar
294 --this cursor is ordered by the load cal start date to fetch the
295 --first term cal if a teching period spans across multiple terms.
296 l_term_cal := NULL;
297 l_term_seq_number := NULL;
298 OPEN cur_load(p_cal_type, p_ci_sequence_number);
299 FETCH cur_load INTO l_term_cal, l_term_seq_number;
300 CLOSE cur_load;
301
302 -- if the rule validation is overridden for the student then return true
303 IF( IGS_EN_GEN_015.validation_step_is_overridden(p_eligibility_step_type => 'PREREQ',
304 p_load_cal_type => l_term_cal,
305 p_load_cal_seq_number => l_term_seq_number,
306 p_person_id => p_person_id,
307 p_uoo_id => p_uoo_id,
308 p_step_override_limit => l_step_override_limit)) THEN
309 p_rule_failed := NULL;
310 p_message_text := NULL ;
311 RETURN TRUE ;
312 ELSE
313 -- check if this rule is defined for the unit section
314 OPEN cur_usec_rule(p_uoo_id,'USECPREREQ' ) ;
315 FETCH cur_usec_rule INTO l_rule_call_cd , l_rul_seq_number ;
316 IF cur_usec_rule%NOTFOUND THEN
317 -- if no rule is defined for this unit section then
318 --check if this rule is defined for the unit version
319 OPEN cur_uver_rule(p_unit_cd,p_unit_ver,'PREREQ' ) ;
320 FETCH cur_uver_rule INTO l_rule_call_cd , l_rul_seq_number ;
321 CLOSE cur_uver_rule ;
322 END IF;
323 CLOSE cur_usec_rule ;
324 -- if no rule is defined for either the unit section or the unit version
325 -- then return true
326 IF l_rule_call_cd IS NULL THEN
327 p_rule_failed := NULL ;
328 p_message_text := NULL ;
329 RETURN TRUE ;
330 END IF;
331
332 -- for each of the program attempt of the student check if the
333 --rule is satisfied
334
335 FOR std_psatt_rec IN cur_std_psatt(p_person_id)
336 LOOP
337 v_return_val := IGS_RU_GEN_001.RULP_VAL_SENNA(
338 p_rule_call_name => l_rule_call_cd,
339 p_person_id => p_person_id,
340 p_course_cd => std_psatt_rec.course_cd,
341 p_course_version => std_psatt_rec.version_number,
342 p_unit_cd => p_unit_cd,
343 p_unit_version => p_unit_ver,
344 p_cal_type => p_cal_type,
345 p_ci_sequence_number => p_ci_sequence_number,
346 p_message => p_message_text ,
347 p_rule_number => l_rul_seq_number,
348 p_param_5 => 'Y',
349 p_param_8 => p_uoo_id);
350 -- if the rule is satisfied for any of the program attempts then return true
351 IF v_return_val = 'true' THEN
352 p_rule_failed := NULL;
353 p_message_text := NULL ;
354 RETURN TRUE ;
355 END IF;
356 END LOOP ;
357 -- Rule has been checked against all the program attempts and
358 --is not satisfied for any
359 p_rule_failed := 'PRE-REQ' ;
360 RETURN FALSE;
361 END IF;
362 END rulp_val_prereq;
363 /*
364 Validate the enrolment rules for a student unit attempt
365 smaddali modified the code for enrollment processes dld
366 bug#1832130
367 */
368 FUNCTION RULP_VAL_ENROL_UNIT(
369 p_person_id IN NUMBER ,
370 p_course_cd IN VARCHAR2 ,
371 p_course_version NUMBER DEFAULT NULL,
372 p_unit_cd IN VARCHAR2 ,
373 p_unit_version NUMBER ,
374 p_cal_type IN VARCHAR2 ,
375 p_ci_sequence_number IN NUMBER ,
376 p_message_text OUT NOCOPY VARCHAR2 ,
377 -- added new parameters for bug#1832130
378 p_uoo_id IN NUMBER DEFAULT NULL,
379 p_rule_failed OUT NOCOPY VARCHAR2 )
380 RETURN boolean IS
381 v_return_val BOOLEAN;
382 BEGIN
383 -- validate pre requisite rule for this unit
384 IF NOT( RULP_VAL_PREREQ(
385 p_person_id => p_person_id,
386 p_course_cd => p_course_cd,
387 p_unit_cd => p_unit_cd,
388 p_cal_type => p_cal_type,
389 p_ci_sequence_number => p_ci_sequence_number,
390 p_message_text => p_message_text,
391 p_course_version => p_course_version,
392 p_unit_ver => p_unit_version,
393 p_uoo_id => p_uoo_id,
394 p_rule_failed => p_rule_failed )) THEN
395
396 RETURN FALSE;
397 END IF;
398 -- if prerequisite rule is satisfied then validate corequisite rule for this unit
399 IF NOT ( RULP_VAL_COREQ( p_person_id => p_person_id,
400 p_course_cd => p_course_cd,
401 p_unit_cd => p_unit_cd,
402 p_cal_type => p_cal_type,
403 p_ci_sequence_number => p_ci_sequence_number,
404 p_message_text => p_message_text,
405 p_course_version => p_course_version,
406 p_unit_ver => p_unit_version,
407 p_uoo_id => p_uoo_id,
408 p_rule_failed => p_rule_failed )) THEN
409
410 RETURN FALSE;
411 END IF;
412 -- if prerequisite and corequisite rules are satisfied then
413 -- validate incompatibility rule for this unit
414 IF NOT ( RULP_VAL_INCOMP( p_person_id => p_person_id,
415 p_course_cd => p_course_cd,
416 p_unit_cd => p_unit_cd,
417 p_cal_type => p_cal_type,
418 p_ci_sequence_number => p_ci_sequence_number,
419 p_message_text => p_message_text,
420 p_course_version => p_course_version,
421 p_unit_ver => p_unit_version,
422 p_uoo_id => p_uoo_id,
423 p_rule_failed => p_rule_failed )) THEN
424
425 RETURN FALSE;
426 END IF;
427 -- if all the rules are satisfied then return true
428 RETURN TRUE;
429 END rulp_val_enrol_unit;
430
431 END IGS_RU_VAL_UNIT_RULE;
[Home] [Help]