1 PACKAGE BODY IGS_PS_VAL_UV AS
2 /* $Header: IGSPS72B.pls 120.0 2005/06/01 19:29:58 appldev noship $ */
3 /*-------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 sarakshi 12-Jul-2004 Bug#3729462, Added the DELETE_FLAG predicate in the cursor c_unit_offering_pattern of procedure crsp_val_uv_quality.
7 ijeddy 03-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment FD.
8 sarakshi 02-sep-2003 Enh#3052452,removed functions crsp_val_uv_sub_ind and crsp_val_uv_sup_ind.Also removed
9 local procedures crsp_val_non_inactive_subs,crsp_val_non_active_sups and crsp_val_non_active_subs and their calls also
10 smvk 10-Dec-2002 Bug # 2699913, Modified function crsp_val_uv_unit_sts not to do the
11 validations associated with following error messages for legacy data.
12 IGS_PS_UNIT_STATUS_CLOSED, IGS_PS_UNITSTATUS_NOT_ALTERED and IGS_PS_NEWUNITVER_ST_PLANNED
13 sarakshi 14-nov-2002 bug#2649028,modified function crsp_val_uv_pnt_ovrd,
14 crsp_val_uv_unit_sts
15 jbegum 21 Mar 02 As part of big fix of bug #2192616
16 Removed the exception handling part of the
17 function enrp_get_sua_incur.This was done in order
18 to allow the user defined exception NO_AUSL_RECORD_FOUND
19 coming from IGS_EN_GEN_007.ENRP_GET_SUA_INCUR which in turn gets it
20 from IGS_EN_PRC_LOAD.ENRP_GET_LOAD_INCUR and
21 to propagate to the form IGSPS047 and be handled accordingly
22 instead of coming as an unhandled exception.
23
24 --jbegum 12 Mar 02 As part of bug fix of bug #2192616
25 -- Modified the procedure crsp_val_uv_pnt_ovrd
26 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
27 -- smvk 16-Dec-2002 Function Call IGS_PS_VAL_TR.crsp_val_tr_perc,IGS_PS_VAL_UD.crsp_val_ud_perc are modified with
28 -- additional parameter value 'FALSE'. for Bug # 2696207
29 --bdeviset 21-JUL-2004 Added a new procedure GET_CP_VALUES for Bug # 3782329
30 -------------------------------------------------------------------------------------------*/
31 --
32 -- Validate the IGS_PS_UNIT level
33 FUNCTION crsp_val_unit_lvl(
34 p_unit_level IN CHAR ,
35 p_message_name OUT NOCOPY VARCHAR2 )
36 RETURN BOOLEAN AS
37 v_closed_ind IGS_PS_UNIT_LEVEL.closed_ind%TYPE;
38 CURSOR c_unit_lvl_closed_ind IS
39 SELECT closed_ind
40 FROM IGS_PS_UNIT_LEVEL
41 WHERE unit_level = p_unit_level AND
42 closed_ind = 'Y';
43 BEGIN
44 OPEN c_unit_lvl_closed_ind;
45 FETCH c_unit_lvl_closed_ind INTO v_closed_ind;
46 --- If a record was not found, then return TRUE, else FALSE
47 IF c_unit_lvl_closed_ind%NOTFOUND THEN
48 p_message_name := NULL;
49 CLOSE c_unit_lvl_closed_ind;
50 RETURN TRUE;
51 ELSE
52 p_message_name := 'IGS_PS_UNITLVL_CLOSED';
53 CLOSE c_unit_lvl_closed_ind;
54 RETURN FALSE;
55 END IF;
56 EXCEPTION
57 WHEN OTHERS THEN
58 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
59 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_unit_lvl');
60 IGS_GE_MSG_STACK.ADD;
61 App_Exception.Raise_Exception;
62 END crsp_val_unit_lvl;
63 --
64 -- Validate the credit point descritor for IGS_PS_UNIT version.
65 FUNCTION crsp_val_uv_cp_desc(
66 P_CREDIT_POINT_DESCRIPTOR IN VARCHAR2,
67 P_MESSAGE_NAME OUT NOCOPY VARCHAR2 )
68 RETURN BOOLEAN AS
69 v_closed_ind IGS_LOOKUPS_VIEW.closed_ind%TYPE;
70 CURSOR c_cp_desc_closed_ind IS
71 SELECT closed_ind
72 FROM IGS_LOOKUPS_VIEW
73 WHERE lookup_code = p_credit_point_descriptor AND
74 closed_ind = 'Y' AND
75 lookup_type = 'CREDIT_POINT_DSCR';
76 BEGIN
77 OPEN c_cp_desc_closed_ind;
78 FETCH c_cp_desc_closed_ind INTO v_closed_ind;
79 IF c_cp_desc_closed_ind%NOTFOUND THEN
80 p_message_name := NULL;
81 CLOSE c_cp_desc_closed_ind;
82 RETURN TRUE;
83 ELSE
84 p_message_name := 'IGS_PS_CRDPNT_DESCRIPTOR_CLS';
85 CLOSE c_cp_desc_closed_ind;
86 RETURN FALSE;
87 END IF;
88 EXCEPTION
89 WHEN OTHERS THEN
90 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
91 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_cp_desc');
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END crsp_val_uv_cp_desc;
95 --
96 -- Validate the IGS_PS_UNIT internal IGS_PS_COURSE level for IGS_PS_UNIT version.
97 FUNCTION crsp_val_uv_uicl(
98 p_unit_int_course_level_cd IN VARCHAR2 ,
99 p_message_name OUT NOCOPY VARCHAR2 )
100 RETURN BOOLEAN AS
101 v_closed_ind IGS_PS_UNIT_INT_LVL.closed_ind%TYPE;
102 CURSOR c_uv_uicl_closed_ind IS
103 SELECT closed_ind
104 FROM IGS_PS_UNIT_INT_LVL
105 WHERE unit_int_course_level_cd = p_unit_int_course_level_cd AND
106 closed_ind = 'Y';
107 BEGIN
108 OPEN c_uv_uicl_closed_ind;
109 FETCH c_uv_uicl_closed_ind INTO v_closed_ind;
110 IF c_uv_uicl_closed_ind%NOTFOUND THEN
111 p_message_name := NULL;
112 CLOSE c_uv_uicl_closed_ind;
113 RETURN TRUE;
114 ELSE
115 p_message_name := 'IGS_PS_UNIT_INTPRG_LVL_CLOSED';
116 CLOSE c_uv_uicl_closed_ind;
117 RETURN FALSE;
118 END IF;
119 EXCEPTION
120 WHEN OTHERS THEN
121 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
122 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_uicl');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END crsp_val_uv_uicl;
126
127 --
128 -- Validate IGS_PS_UNIT version end date and IGS_PS_UNIT version status
129 FUNCTION crsp_val_uv_end_sts(
130 p_end_dt IN DATE ,
131 p_unit_status IN VARCHAR2 ,
132 p_message_name OUT NOCOPY VARCHAR2)
133 RETURN BOOLEAN AS
134 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
135 CURSOR c_get_s_unit_status IS
136 SELECT s_unit_status
137 FROM IGS_PS_UNIT_STAT
138 WHERE unit_status = p_unit_status;
139 BEGIN
140 p_message_name := NULL;
141 OPEN c_get_s_unit_status;
142 FETCH c_get_s_unit_status INTO v_s_unit_status;
143 IF (c_get_s_unit_status%NOTFOUND) THEN
144 CLOSE c_get_s_unit_status;
145 RETURN TRUE;
146 END IF;
147 CLOSE c_get_s_unit_status;
148 -- end date can only be set if the
149 -- IGS_PS_UNIT system status is INACTIVE
150 IF (p_end_dt IS NOT NULL) THEN
151 IF (v_s_unit_status = 'INACTIVE') THEN
152 RETURN TRUE;
153 ELSE
154 p_message_name := 'IGS_PS_STSET_INACTIVE_UNITVER';
155 RETURN FALSE;
156 END IF;
157 ELSE
158 IF (v_s_unit_status <> 'INACTIVE') THEN
159 RETURN TRUE;
160 ELSE
161 p_message_name := 'IGS_PS_STNOTSET_INACTIVE_UNIT';
162 RETURN FALSE;
163 END IF;
164 END IF;
165 EXCEPTION
166 WHEN OTHERS THEN
167 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
168 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_end_sts');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END crsp_val_uv_end_sts;
172 --
173 -- Validate IGS_PS_UNIT version expiry date and IGS_PS_UNIT version status.
174 FUNCTION crsp_val_uv_exp_sts(
175 p_unit_cd IN VARCHAR2 ,
176 p_version_number IN NUMBER ,
177 p_expiry_dt IN DATE ,
178 p_unit_status IN VARCHAR2 ,
179 p_message_name OUT NOCOPY VARCHAR2 )
180 RETURN BOOLEAN AS
181 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
182 v_check CHAR;
183 CURSOR c_get_s_unit_status IS
184 SELECT s_unit_status
185 FROM IGS_PS_UNIT_STAT
186 WHERE unit_status = p_unit_status;
187 CURSOR c_check_uv_us IS
188 SELECT 'x'
189 FROM IGS_PS_UNIT_VER uv,
190 IGS_PS_UNIT_STAT us
191 WHERE unit_cd = p_unit_cd AND
192 version_number <> p_version_number AND
193 expiry_dt IS NULL AND
194 uv.unit_status = us.unit_status AND
195 us.s_unit_status = 'ACTIVE';
196 BEGIN
197 p_message_name := NULL;
198 OPEN c_get_s_unit_status;
199 FETCH c_get_s_unit_status INTO v_s_unit_status;
200 IF (c_get_s_unit_status%NOTFOUND) THEN
201 CLOSE c_get_s_unit_status;
202 RETURN TRUE;
203 END IF;
204 CLOSE c_get_s_unit_status;
205 -- Check that no other versions of the IGS_PS_UNIT exist that
206 -- have a system status of ACTIVE and expiry date not set.
207 IF (v_s_unit_status = 'ACTIVE') AND (p_expiry_dt IS NULL) THEN
208 OPEN c_check_uv_us;
209 FETCH c_check_uv_us INTO v_check;
210 IF (c_check_uv_us%FOUND) THEN
211 CLOSE c_check_uv_us;
212 p_message_name := 'IGS_PS_ANOTHERVER_EXISTS';
213 RETURN FALSE;
214 END IF;
215 CLOSE c_check_uv_us;
216 END IF;
217 RETURN TRUE;
218 EXCEPTION
219 WHEN OTHERS THEN
220 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
221 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_exp_sts');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END crsp_val_uv_exp_sts;
225 --
226 -- Validate points increment, min and max fields against points override.
227 FUNCTION crsp_val_uv_pnt_ovrd(
228 p_points_override_ind IN VARCHAR2 ,
229 p_points_increment IN NUMBER ,
230 p_points_min IN NUMBER ,
231 p_points_max IN NUMBER ,
232 p_enrolled_credit_points IN NUMBER ,
233 p_achievable_credit_points IN NUMBER ,
234 p_message_name OUT NOCOPY VARCHAR2 ,
235 p_lgcy_validator IN BOOLEAN )
236 RETURN BOOLEAN AS
237 /***********************************************************************************************
238 Created By :
239 Date Created :
240 Purpose :
241 Known limitations,enhancements,remarks:
242 Change History :
243 Who When What
244 jbegum 12 Mar 02 As part of bug fix of bug #2192616
245 The If conditions of all the validations have been modified
246 to check for NOT NULL.Earlier the IF conditions were using
247 NVL clause ie. IF (NVL(parameter,0)) <> 0
248 This caused the validation's to fail when the value of the parameter
249 was 0.
250 *************************************************************************************************/
251 l_ret_status BOOLEAN :=TRUE;
252 BEGIN
253 -- This module performs cross-field validation for points
254 -- increment points minimum and points maximum fields in
255 -- IGS_PS_UNIT version table
256 p_message_name := NULL;
257 IF(p_points_override_ind = 'Y') THEN
258 -- validate that p_points_min <= p_points_max
259 IF p_points_min IS NOT NULL AND
260 p_points_max IS NOT NULL AND
261 p_points_min > p_points_max THEN
262 IF p_lgcy_validator THEN
263 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_CP_MAX_GE_CP_MIN',NULL,NULL,FALSE);
264 l_ret_status :=FALSE;
265 ELSE
266 p_message_name := 'IGS_PS_CP_MAX_GE_CP_MIN';
267 RETURN FALSE;
268 END IF;
269 END IF;
270 -- validate that p_points_min >= p_enrolled_credit_points
271 IF p_points_min IS NOT NULL AND
272 p_points_min > p_enrolled_credit_points THEN
273 IF p_lgcy_validator THEN
274 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_ENRCP_ME_CPMIN',NULL,NULL,FALSE);
275 l_ret_status :=FALSE;
276 ELSE
277 p_message_name := 'IGS_PS_ENRCP_ME_CPMIN';
278 RETURN FALSE;
279 END IF;
280 END IF;
281 -- validate that p_points_max >= p_enrolled_credit_points
282 IF p_points_max IS NOT NULL AND
283 p_points_max < p_enrolled_credit_points THEN
284 IF p_lgcy_validator THEN
285 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_CP_MAX_GE_ENR_CP',NULL,NULL,FALSE);
286 l_ret_status :=FALSE;
287 ELSE
288 p_message_name := 'IGS_PS_CP_MAX_GE_ENR_CP';
289 RETURN FALSE;
290 END IF;
291 END IF;
292 -- validate that p_points_min <= p_achievable_credit_points
293 IF p_points_min IS NOT NULL AND
294 p_achievable_credit_points IS NOT NULL AND
295 p_points_min > p_achievable_credit_points THEN
296 IF p_lgcy_validator THEN
297 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_ACHCP_GE_CP_MIN',NULL,NULL,FALSE);
298 l_ret_status :=FALSE;
299 ELSE
300 p_message_name := 'IGS_PS_ACHCP_GE_CP_MIN';
301 RETURN FALSE;
302 END IF;
303 END IF;
304 -- validate that p_points_max >= p_achievable_credit_points
305 IF p_points_max IS NOT NULL AND
306 p_achievable_credit_points IS NOT NULL AND
307 p_points_max < p_achievable_credit_points THEN
308 IF p_lgcy_validator THEN
309 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_CP_MAX_GE_ACH_CP',NULL,NULL,FALSE);
310 l_ret_status :=FALSE;
311 ELSE
312 p_message_name := 'IGS_PS_CP_MAX_GE_ACH_CP';
313 RETURN FALSE;
314 END IF;
315 END IF;
316 -- validate that p_points_min and p_points_max values are in accordance with
317 -- p_enrolled_credit_points and p_points_increment values
318 IF p_points_min IS NOT NULL AND
319 p_points_increment IS NOT NULL THEN
320 IF(MOD(ABS(p_points_min - p_enrolled_credit_points), p_points_increment)
321 <> 0) THEN
322 IF p_lgcy_validator THEN
323 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_CP_MAX_DECR_ENR_CP',NULL,NULL,FALSE);
324 l_ret_status :=FALSE;
325 ELSE
326 p_message_name := 'IGS_PS_CP_MAX_DECR_ENR_CP';
327 RETURN FALSE;
328 END IF;
329 END IF;
330 END IF;
331 IF p_points_max IS NOT NULL AND
332 p_points_increment IS NOT NULL THEN
333 IF(MOD(ABS(p_points_max - p_enrolled_credit_points), p_points_increment)
334 <> 0) THEN
335 IF p_lgcy_validator THEN
336 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_CP_MAX_INCR_ENR_CP',NULL,NULL,FALSE);
337 l_ret_status :=FALSE;
338 ELSE
339 p_message_name := 'IGS_PS_CP_MAX_INCR_ENR_CP';
340 RETURN FALSE;
341 END IF;
342 END IF;
343 END IF;
344 ELSE
345 IF p_points_increment IS NOT NULL OR
346 p_points_min IS NOT NULL OR
347 p_points_max IS NOT NULL THEN
348 IF p_lgcy_validator THEN
349 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_CRDPOINT_INCR_MAX_MIN',NULL,NULL,FALSE);
350 l_ret_status :=FALSE;
351 ELSE
352 p_message_name := 'IGS_PS_CRDPOINT_INCR_MAX_MIN';
353 RETURN FALSE;
354 END IF;
355 END IF;
356 END IF;
357
358 IF p_lgcy_validator THEN
359 RETURN l_ret_status;
360 ELSE
361 RETURN TRUE;
362 END IF;
363 EXCEPTION
364 WHEN OTHERS THEN
365 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
366 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_pnt_ovrd');
367 IGS_GE_MSG_STACK.ADD;
368 App_Exception.Raise_Exception;
369 END crsp_val_uv_pnt_ovrd;
370 --
371 -- Validate the IGS_PS_UNIT status for IGS_PS_UNIT version
372 FUNCTION crsp_val_uv_unit_sts(
373 p_unit_cd IN VARCHAR2 ,
374 p_version_number IN NUMBER ,
375 p_new_unit_status IN VARCHAR2 ,
376 p_old_unit_status IN VARCHAR2 ,
377 p_message_name OUT NOCOPY VARCHAR2 ,
378 p_lgcy_validator IN BOOLEAN )
379 RETURN BOOLEAN AS
380 cst_planned_status VARCHAR2(7);
381 cst_inactive_status VARCHAR2(8);
382 cst_active_status VARCHAR2(6);
383 gv_closed_ind IGS_PS_UNIT_STAT.closed_ind%TYPE;
384 gv_new_sys_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
385 gv_old_sys_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
386
387 l_ret_status BOOLEAN :=TRUE;
388
389 CURSOR c_unit_sts_closed_ind IS
390 SELECT closed_ind
391 FROM IGS_PS_UNIT_STAT
392 WHERE unit_status = p_new_unit_status;
393 CURSOR c_new_sys_status IS
394 SELECT s_unit_status
395 FROM IGS_PS_UNIT_STAT
396 WHERE unit_status = p_new_unit_status;
397 CURSOR c_old_sys_status IS
398 SELECT s_unit_status
399 FROM IGS_PS_UNIT_STAT
400 WHERE unit_status = p_old_unit_status;
401 BEGIN
402 --For gscc warning shifted down
403 cst_planned_status := 'PLANNED';
404 cst_inactive_status := 'INACTIVE';
405 cst_active_status := 'ACTIVE';
406
407 --- Set default message
408 p_message_name := NULL;
409 --- Check the closed indicator for the new IGS_PS_UNIT
410 OPEN c_unit_sts_closed_ind;
411 FETCH c_unit_sts_closed_ind INTO gv_closed_ind;
412 --- If a record was not found, then return FALSE, else check the
413 --- closed indicator.
414 IF c_unit_sts_closed_ind%NOTFOUND THEN
415 IF NOT p_lgcy_validator THEN
416 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
417 CLOSE c_unit_sts_closed_ind;
418 RETURN FALSE;
419 END IF;
420 END IF;
421 CLOSE c_unit_sts_closed_ind;
422 IF gv_closed_ind <> 'N' THEN
423 IF NOT p_lgcy_validator THEN
424 p_message_name := 'IGS_PS_UNIT_STATUS_CLOSED';
425 CLOSE c_unit_sts_closed_ind;
426 RETURN FALSE;
427 END IF;
428 END IF;
429 --- Validate the system status is not being altered to PLANNED from ACTIVE
430 --- or INACTIVE.
431 --- Retrieve the system status for the new and old IGS_PS_UNIT statuses.
432 OPEN c_new_sys_status;
433 FETCH c_new_sys_status INTO gv_new_sys_unit_status;
434 IF c_new_sys_status%NOTFOUND THEN
435 IF NOT p_lgcy_validator THEN
436 CLOSE c_new_sys_status;
437 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
438 RETURN FALSE;
439 END IF;
440 END IF;
441 CLOSE c_new_sys_status;
442 OPEN c_old_sys_status;
443 FETCH c_old_sys_status INTO gv_old_sys_unit_status;
444 CLOSE c_old_sys_status;
445 IF p_old_unit_status IS NOT NULL AND
446 p_new_unit_status <> p_old_unit_status THEN
447 IF gv_new_sys_unit_status <> gv_old_sys_unit_status THEN
448 IF gv_new_sys_unit_status = cst_planned_status THEN
449 IF NOT p_lgcy_validator THEN
450 p_message_name := 'IGS_PS_UNITSTATUS_NOT_ALTERED';
451 RETURN FALSE;
452 END IF;
453 END IF;
454 END IF;
455 END IF;
456 --- Check that the new system status is not Planned when the old IGS_PS_UNIT
457 --- status is NULL
458 IF p_old_unit_status IS NULL THEN
459 IF gv_new_sys_unit_status <> cst_planned_status THEN
460 IF NOT p_lgcy_validator THEN
461 p_message_name := 'IGS_PS_NEWUNITVER_ST_PLANNED';
462 RETURN FALSE;
463 END IF;
464 END IF;
465 END IF;
466
467 --- Additional check must be done to see if students enrolled in this IGS_PS_UNIT are
468 --- ACTIVE - waiting on Enrolment sub-system.
469
470 --- If all validation is successful, then return TRUE and message number 0
471 IF p_lgcy_validator THEN
472 RETURN l_ret_status;
473 ELSE
474 RETURN TRUE;
475 END IF;
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
480 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_unit_sts');
481 IGS_GE_MSG_STACK.ADD;
482 App_Exception.Raise_Exception;
483 END crsp_val_uv_unit_sts;
484 --
485 -- Perform quality validation checks on a IGS_PS_UNIT version and its details.
486 FUNCTION crsp_val_uv_quality(
487 p_unit_cd IN VARCHAR2 ,
488 p_version_number IN NUMBER ,
489 p_old_unit_status IN VARCHAR2 ,
490 p_message_name OUT NOCOPY VARCHAR2 )
491 RETURN BOOLEAN AS
492 BEGIN -- crsp_val_uv_quality
493 -- Perform a quality validation check on insert.
494 -- * Validate that all reference data is open and available for use
495 -- for IGS_PS_UNIT_VER records (e.g IGS_PS_UNIT_LEVEL is not closed) and also
496 -- for existing IGS_PS_UNIT_VER detail records such as:
497 -- IGS_PS_UNIT_DSCP,
498 -- IGS_PS_UNIT_CATEGORY,
499 -- IGS_PS_UNIT_LVL,
500 -- IGS_PS_UNIT_REF_CD.
501 -- If IGS_PS_UNIT version is altered from a system status of planned to
502 -- active then check:
503 -- IGS_PS_UNIT_OFR,
504 -- IGS_PS_UNIT_OFR_PAT,
505 -- IGS_PS_UNIT_OFR_OPT.
506 -- * Validate that where tables contains fields that hold percentages, that
507 -- the records total 100% for the given IGS_PS_UNIT version. The relevant tables
508 -- are:
509 -- IGS_PS_TCH_RESP,
510 -- IGS_PS_TCH_RESP_OVRD,
511 -- IGS_PS_UNIT_DSCP.
512 -- * Validate that all referenced organisational units are active.
513 DECLARE
514 v_terminate BOOLEAN := FALSE;
515 v_uv_rec IGS_PS_UNIT_VER%ROWTYPE;
516 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
517 v_message_name VARCHAR2(30);
518 v_ret BOOLEAN;
519 CURSOR c_unit_version IS
520 SELECT *
521 FROM IGS_PS_UNIT_VER
522 WHERE unit_cd = p_unit_cd AND
523 version_number = p_version_number;
524 CURSOR c_unit_discipline IS
525 SELECT discipline_group_cd
526 FROM IGS_PS_UNIT_DSCP
527 WHERE unit_cd = p_unit_cd AND
528 version_number = p_version_number;
529 CURSOR c_course_unit_level IS
530 SELECT course_cd, course_version_number
531 FROM igs_ps_unit_lvl
532 WHERE unit_cd = p_unit_cd AND
533 version_number = p_version_number;
534 CURSOR c_unit_categorisation IS
535 SELECT unit_cat
536 FROM IGS_PS_UNIT_CATEGORY
537 WHERE unit_cd = p_unit_cd AND
538 version_number = p_version_number;
539
540 -- ijeddy 03-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment FD.
541 CURSOR c_unit_reference_cd IS
542 SELECT reference_cd_type
543 FROM IGS_PS_UNIT_REF_CD
544 WHERE unit_cd = p_unit_cd AND
545 version_number = p_version_number;
546 CURSOR c_get_s_unit_status IS
547 SELECT s_unit_status
548 FROM IGS_PS_UNIT_STAT
549 WHERE unit_status = p_old_unit_status;
550 CURSOR c_unit_offering IS
551 SELECT cal_type
552 FROM IGS_PS_UNIT_OFR
553 WHERE unit_cd = p_unit_cd AND
554 version_number = p_version_number;
555 CURSOR c_unit_offering_pattern IS
556 SELECT cal_type,
557 ci_sequence_number
558 FROM IGS_PS_UNIT_OFR_PAT
559 WHERE unit_cd = p_unit_cd AND
560 version_number = p_version_number
561 AND delete_flag = 'N';
562 CURSOR c_unit_offering_option IS
563 SELECT location_cd,
564 unit_class,
565 unit_contact
566 FROM IGS_PS_UNIT_OFR_OPT
567 WHERE unit_cd = p_unit_cd AND
568 version_number = p_version_number;
569 CURSOR c_teach_res_ovrd IS
570 SELECT cal_type,
571 ci_sequence_number,
572 location_cd,
573 unit_class
574 FROM IGS_PS_UNIT_OFR_OPT
575 WHERE unit_cd = p_unit_cd AND
576 version_number = p_version_number;
577 BEGIN
578 p_message_name := NULL;
579 OPEN c_unit_version;
580 FETCH c_unit_version INTO v_uv_rec;
581 IF (c_unit_version%NOTFOUND) THEN
582 CLOSE c_unit_version;
583 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
584 RETURN FALSE;
585 END IF;
586 CLOSE c_unit_version;
587 -- Validate that the IGS_PS_UNIT_LEVEL is not closed
588 IF (IGS_PS_VAL_UV.crsp_val_unit_lvl(
589 v_uv_rec.unit_level,
590 p_message_name) = FALSE) THEN
591 RETURN FALSE;
592 END IF;
593 -- Validate the IGS_PS_CR_PT_DSCR is not closed
594 IF (IGS_PS_VAL_UV.crsp_val_uv_cp_desc(
595 v_uv_rec.credit_point_descriptor,
596 p_message_name) = FALSE) THEN
597 RETURN FALSE;
598 END IF;
599 -- Validate that the owning_org_unit_cd is ACTIVE
600
601 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UV.crsp_val_ou_sys_sts
602 IF (IGS_PS_VAL_CRV.crsp_val_ou_sys_sts(
603 v_uv_rec.owner_org_unit_cd,
604 v_uv_rec.owner_ou_start_dt,
605 p_message_name) = FALSE) THEN
606 RETURN FALSE;
607 END IF;
608 -- Validate the records consisting of percentages total 100%
609 IF (IGS_PS_VAL_TR.crsp_val_tr_perc(
610 p_unit_cd,
611 p_version_number,
612 p_message_name,FALSE) = FALSE) THEN
613 RETURN FALSE;
614 END IF;
615 -- Loop for all offereing options for the IGS_PS_UNIT version.
616 FOR v_teach_res_ovrd_rec IN c_teach_res_ovrd LOOP
617 IF IGS_PS_VAL_TRo.crsp_val_tro_perc (
618 p_unit_cd,
619 p_version_number,
620 v_teach_res_ovrd_rec.cal_type,
621 v_teach_res_ovrd_rec.ci_sequence_number,
622 v_teach_res_ovrd_rec.location_cd,
623 v_teach_res_ovrd_rec.unit_class,
624 v_message_name) = FALSE THEN
625 v_ret := FALSE;
626 EXIT;
627 END IF;
628 END LOOP;
629 IF v_ret = FALSE THEN
630 p_message_name := 'IGS_PS_PRCALLOC_TEACHRESP_100';
631 RETURN FALSE;
632 END IF;
633 -- Validate the IGS_PS_UNIT_DSCP record percentage total 100%
634 IF (IGS_PS_VAL_UD.crsp_val_ud_perc(
635 p_unit_cd,
636 p_version_number,
637 p_message_name,FALSE) = FALSE) THEN
638 RETURN FALSE;
639 END IF;
640 -- Validate the IGS_PS_UNIT_DSCP table and that the disclipline_group_cd
641 -- is not closed.
642 FOR ud_rec IN c_unit_discipline LOOP
643 IF (IGS_PS_VAL_UD.crsp_val_ud_dg_cd(
644 ud_rec.discipline_group_cd,
645 p_message_name) = FALSE) THEN
646 v_terminate := TRUE;
647 EXIT;
648 END IF;
649 END LOOP;
650 IF (v_terminate = TRUE) THEN
651 RETURN FALSE;
652 END IF;
653 -- Validate the IGS_PS_UNIT_LVL table and that IGS_PS_COURSE is not closed
654 FOR cul_rec IN c_course_unit_level LOOP
655 IF (IGS_PS_VAL_CUL.crsp_val_crs_type(
656 cul_rec.course_cd,
657 cul_rec.course_version_number,
658 p_message_name) = FALSE) THEN
659 v_terminate := TRUE;
660 EXIT;
661 END IF;
662 END LOOP;
663 IF (v_terminate = TRUE) THEN
664 RETURN FALSE;
665 END IF;
666 -- Validate the IGS_PS_UNIT_CATEGORY table and that IGS_PS_UNIT_CAT is not closed
667 FOR uc_rec IN c_unit_categorisation LOOP
668 IF (IGS_PS_VAL_UC.crsp_val_uc_unit_cat(
669 uc_rec.unit_cat,
670 p_message_name) = FALSE) THEN
671 v_terminate := TRUE;
672 EXIT;
673 END IF;
674 END LOOP;
675 IF (v_terminate = TRUE) THEN
676 RETURN FALSE;
677 END IF;
678 -- Validate the IGS_PS_UNIT_REF_CD table and that IGS_GE_REF_CD_TYPE is not
679 -- closed
680 FOR urc_rec IN c_unit_reference_cd LOOP
681 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_URC.crsp_val_ref_cd_type
682 IF (IGS_PS_VAL_CRFC.crsp_val_ref_cd_type(
683 urc_rec.reference_cd_type,
684 p_message_name) = FALSE) THEN
685 v_terminate := TRUE;
686 EXIT;
687 END IF;
688 END LOOP;
689 IF (v_terminate = TRUE) THEN
690 RETURN FALSE;
691 END IF;
692 OPEN c_get_s_unit_status;
693 FETCH c_get_s_unit_status INTO v_s_unit_status;
694 -- No IGS_PS_UNIT_STAT found
695 IF (c_get_s_unit_status%NOTFOUND) THEN
696 CLOSE c_get_s_unit_status;
697 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
698 RETURN FALSE;
699 END IF;
700 CLOSE c_get_s_unit_status;
701 IF (v_s_unit_status = 'PLANNED') THEN
702 -- Validate if IGS_PS_UNIT_OFR records exist, then the IGS_CA_TYPE is not closed
703 FOR uo_rec IN c_unit_offering LOOP
704 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UO.crsp_val_uo_cal_type
705 IF (IGS_AS_VAL_UAI.crsp_val_uo_cal_type(
706 uo_rec.cal_type,
707 p_message_name) = FALSE) THEN
708 v_terminate := TRUE;
709 EXIT;
710 END IF;
711 END LOOP;
712 IF (v_terminate = TRUE) THEN
713 RETURN FALSE;
714 END IF;
715 -- Validate if unit_ofering_pattern records exist, then the
716 -- IGS_CA_INST.IGS_CA_STAT = 'ACTIVE'
717 FOR uop_rec IN c_unit_offering_pattern LOOP
718 IF (IGS_as_VAL_uai.crsp_val_crs_ci(
719 uop_rec.cal_type,
720 uop_rec.ci_sequence_number,
721 p_message_name) = FALSE) THEN
722 v_terminate := TRUE;
723 EXIT;
724 END IF;
725 END LOOP;
726 IF (v_terminate = TRUE) THEN
727 RETURN FALSE;
728 END IF;
729 -- Validate that if IGS_PS_UNIT_OFR_OPT records exist, then
730 -- check that location_cd and IGS_AS_UNIT_CLASS is not closed.
731 FOR uoo_rec IN c_unit_offering_option LOOP
732 IF (IGS_PS_VAL_UOo.crsp_val_loc_cd(
733 uoo_rec.location_cd,
734 p_message_name) = FALSE) THEN
735 v_terminate := TRUE;
736 EXIT;
737 END IF;
738 IF (IGS_PS_VAL_UOo.crsp_val_uoo_uc(
739 uoo_rec.unit_class,
740 p_message_name) = FALSE) THEN
741 v_terminate := TRUE;
742 EXIT;
743 END IF;
744 IF NVL(uoo_rec.unit_contact, 9999999999) <> 9999999999 THEN
745 -- Validate that the IGS_PS_UNIT contact is a staff member
746 IF (IGS_PS_VAL_UOo.crsp_val_uoo_contact(
747 uoo_rec.unit_contact,
748 p_message_name) = FALSE) THEN
749 v_terminate := TRUE;
750 EXIT;
751 END IF;
752 END IF;
753 END LOOP;
754 IF (v_terminate = TRUE) THEN
755 RETURN FALSE;
756 END IF;
757 END IF; -- (v_s_unit_status = 'PLANNED')
758 -- All validation successful
759 RETURN TRUE;
760 EXCEPTION
761 WHEN OTHERS THEN
762 IF (c_unit_version%ISOPEN) THEN
763 CLOSE c_unit_version;
764 END IF;
765 IF (c_unit_discipline%ISOPEN) THEN
766 CLOSE c_unit_discipline;
767 END IF;
768 IF (c_course_unit_level%ISOPEN) THEN
769 CLOSE c_course_unit_level;
770 END IF;
771 IF (c_unit_categorisation%ISOPEN) THEN
772 CLOSE c_unit_categorisation;
773 END IF;
774 IF (c_unit_reference_cd%ISOPEN) THEN
775 CLOSE c_unit_reference_cd;
776 END IF;
777 IF (c_get_s_unit_status%ISOPEN) THEN
778 CLOSE c_get_s_unit_status;
779 END IF;
780 IF (c_unit_offering%ISOPEN) THEN
781 CLOSE c_unit_offering;
782 END IF;
783 IF (c_unit_offering_pattern%ISOPEN) THEN
784 CLOSE c_unit_offering_pattern;
785 END IF;
786 IF (c_unit_offering_option%ISOPEN) THEN
787 CLOSE c_unit_offering_option;
788 END IF;
789 IF (c_teach_res_ovrd%ISOPEN) THEN
790 CLOSE c_teach_res_ovrd;
791 END IF;
792 App_Exception.Raise_Exception;
793 END;
794 EXCEPTION
795 WHEN OTHERS THEN
796 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
797 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_quality');
798 IGS_GE_MSG_STACK.ADD;
799 App_Exception.Raise_Exception;
800 END crsp_val_uv_quality;
801 --
802 -- Validate supplementary exam indicator against the assessable indicator
803 FUNCTION CRSP_VAL_UV_SUP_EXAM(
804 p_supp_exam_permitted_ind IN VARCHAR2 ,
805 p_assessable_ind IN VARCHAR2 ,
806 p_message_name OUT NOCOPY VARCHAR2 )
807 RETURN BOOLEAN AS
808 BEGIN
809 p_message_name := NULL;
810 -- Validate the system status of the IGS_PS_COURSE version
811 IF p_supp_exam_permitted_ind = 'Y' AND
812 p_assessable_ind = 'N' THEN
813 p_message_name:= 'IGS_PS_UNITVER_ASSESSABLE';
814 RETURN FALSE;
815 END IF;
816 RETURN TRUE;
817 EXCEPTION
818 WHEN OTHERS THEN
819 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
820 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_sup_exam');
821 IGS_GE_MSG_STACK.ADD;
822 App_Exception.Raise_Exception;
823 END crsp_val_uv_sup_exam;
824 --
825
826 -- Validate students fall within new override limits set
827 FUNCTION crsp_val_uv_cp_ovrd(
828 p_unit_cd IN VARCHAR2 ,
829 p_version_number IN NUMBER ,
830 p_points_override_ind IN VARCHAR2 ,
831 p_points_min IN NUMBER ,
832 p_points_max IN NUMBER ,
833 p_points_increment IN NUMBER ,
834 p_message_name OUT NOCOPY VARCHAR2 )
835 RETURN BOOLEAN AS
836 /* Who When What
837 knaraset 09-May-2003 Modified call to IGS_EN_GEN_007.ENRP_GET_SUA_INCUR to add parameter uoo_id,as part of MUS build bug 2829262
838 jbegum 21 Mar 2002 As part of big fix of bug #2192616
839 Removed the exception handling part of the
840 function crsp_val_uv_cp_ovrd.This was done in order
841 to allow the user defined exception NO_AUSL_RECORD_FOUND
842 coming from IGS_EN_GEN_007.ENRP_GET_SUA_INCUR which in turn gets it
843 from IGS_EN_PRC_LOAD.ENRP_GET_LOAD_INCUR and
844 to propagate to the form IGSPS047 and be handled accordingly
845 instead of coming as an unhandled exception.
846 */
847
848 BEGIN -- crsp_val_uv_cp_ovrd
849 -- Validate that all enrolled students are in accordance with the
850 -- new override credit points fields being specified.
851 -- This routine only returns warnings, and so will only
852 -- return the TRUE return.
853 DECLARE
854 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
855 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
856 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
857 v_uas1_enrolled BOOLEAN := FALSE;
858 v_uas1_completed BOOLEAN := FALSE;
859 v_uas1_discontin BOOLEAN := FALSE;
860 v_uas2_enrolled BOOLEAN := FALSE;
861 v_uas2_completed BOOLEAN := FALSE;
862 v_uas2_discontin BOOLEAN := FALSE;
863 NO_AUSL_RECORD_FOUND EXCEPTION;
864 CURSOR c_sua1 IS
865 SELECT DISTINCT sua.unit_attempt_status
866 FROM IGS_EN_SU_ATTEMPT sua
867 WHERE sua.unit_cd = p_unit_cd AND
868 sua.version_number = p_version_number AND
869 (sua.unit_attempt_status IN (cst_enrolled,cst_completed) OR
870 (sua.unit_attempt_status = cst_discontin AND
871 IGS_EN_GEN_007.ENRP_GET_SUA_INCUR (
872 sua.person_id,
873 sua.course_cd,
874 sua.unit_cd,
875 sua.version_number,
876 sua.cal_type,
877 sua.ci_sequence_number,
878 sua.unit_attempt_status,
879 sua.discontinued_dt,
880 sua.administrative_unit_status,
881 sua.uoo_id) = 'Y')) AND
882 (sua.override_enrolled_cp IS NOT NULL OR
883 sua.override_achievable_cp IS NOT NULL);
884 CURSOR c_sua2 IS
885 SELECT sua.unit_attempt_status
886 FROM IGS_EN_SU_ATTEMPT sua
887 WHERE sua.unit_cd = p_unit_cd AND
888 sua.version_number = p_version_number AND
889 (sua.unit_attempt_status IN (cst_enrolled,cst_completed) OR
890 (sua.unit_attempt_status = cst_discontin AND
891 IGS_EN_GEN_007.ENRP_GET_SUA_INCUR (
892 sua.person_id,
893 sua.course_cd,
894 sua.unit_cd,
895 sua.version_number,
896 sua.cal_type,
897 sua.ci_sequence_number,
898 sua.unit_attempt_status,
899 sua.discontinued_dt,
900 sua.administrative_unit_status,
901 sua.uoo_id) = 'Y')) AND
902 (p_points_min IS NULL OR
903 NVL(sua.override_enrolled_cp,999999) < p_points_min OR
904 NVL(sua.override_achievable_cp,999999) < p_points_min) AND
905 (p_points_max IS NULL OR
906 NVL(sua.override_enrolled_cp,0) > p_points_max OR
907 NVL(sua.override_achievable_cp,0) > p_points_max) AND
908 (p_points_increment IS NULL OR
909 MOD(NVL(sua.override_enrolled_cp,p_points_increment),
910 p_points_increment) <> 0.0 OR
911 MOD(NVL(sua.override_achievable_cp,p_points_increment),
912 p_points_increment) <> 0.0);
913 BEGIN
914 -- Set the default message number
915 p_message_name := NULL;
916 -- If the new points override indicator is set to N then check whether
917 -- the are any students with overridden credit points
918 IF p_points_override_ind = 'N' THEN
919 FOR v_sua1_rec IN c_sua1 LOOP
920 IF v_sua1_rec.unit_attempt_status = cst_enrolled THEN
921 v_uas1_enrolled := TRUE;
922 ELSIF v_sua1_rec.unit_attempt_status = cst_completed THEN
923 v_uas1_completed := TRUE;
924 ELSIF v_sua1_rec.unit_attempt_status = cst_discontin THEN
925 v_uas1_discontin := TRUE;
926 END IF;
927 END LOOP;
928 -- If all records had a status of 'ENROLLED'
929 IF (v_uas1_enrolled = TRUE AND
930 v_uas1_completed = FALSE AND
931 v_uas1_discontin = FALSE) THEN
932 p_message_name := 'IGS_PS_ENR_UNIT_ATTEMPTS_EXIS';
933 --all records had a status of 'COMPLETED'
934 ElSIF (v_uas1_enrolled = FALSE AND
935 v_uas1_completed = TRUE AND
936 v_uas1_discontin = FALSE) THEN
937 p_message_name := 'IGS_PS_COMPL_UNIT_ATTEMPTS';
938 -- all records contained statuses of 'ENROLLED' & 'COMPLETED'
939 ELSIF (v_uas1_enrolled = TRUE AND
940 v_uas1_completed = TRUE AND
941 v_uas1_discontin = FALSE) THEN
942 p_message_name := 'IGS_PS_ENR_COMPL_UNIT_ATTEMPT';
943 -- all records had a status of 'DISCONTIN'
944 ELSIF ( v_uas1_enrolled = FALSE AND
945 v_uas1_completed = FALSE AND
946 v_uas1_discontin = TRUE) THEN
947 p_message_name := 'IGS_PS_DIS_UA_EXISTS';
948 -- all records contained statuses of 'ENROLLED' & 'DISCONTIN'
949 ELSIF ( v_uas1_enrolled = TRUE AND
950 v_uas1_completed = FALSE AND
951 v_uas1_discontin = TRUE) THEN
952 p_message_name := 'IGS_PS_DIS_ENR_UA_EXISTS';
953 -- all records contained statuses of 'COMPLETED' & 'DISCONTIN'
954 ELSIF ( v_uas1_enrolled = FALSE AND
955 v_uas1_completed = TRUE AND
956 v_uas1_discontin = TRUE) THEN
957 p_message_name := 'IGS_PS_DIS_COMPL_UA_EXISTS';
958 -- all records contained statuses of 'COMPLETED' & 'DISCONTIN' & 'ENROLLED'
959 -- or no records where found
960 ELSIF (v_uas1_enrolled = TRUE AND
961 v_uas1_completed = TRUE AND
962 v_uas1_discontin = TRUE) THEN
963 p_message_name := 'IGS_PS_DIS_ENR_COMP_UA_EXISTS';
964 END IF;
965 ELSE -- (later checks don't apply if override is not permitted)
966 -- * Check that all student IGS_PS_UNIT attempts which exist are in accordance
967 -- with the new values for the credit point limits.
968 FOR v_sua2_rec IN c_sua2 LOOP
969 IF v_sua2_rec.unit_attempt_status = cst_enrolled THEN
970 v_uas2_enrolled := TRUE;
971 ELSIF v_sua2_rec.unit_attempt_status = cst_completed THEN
972 v_uas2_completed := TRUE;
973 ELSIF v_sua2_rec.unit_attempt_status = cst_discontin THEN
974 v_uas2_discontin := TRUE;
975 END IF;
976 END LOOP;
977 -- If all records had a status of 'ENROLLED'
978 IF (v_uas2_enrolled = TRUE AND
979 v_uas2_completed = FALSE AND
980 v_uas2_discontin = FALSE) THEN
981 p_message_name := 'IGS_PS_ENR_UNIT_ATTEMPTS';
982 --all records had a status of 'COMPLETED'
983 ELSIF (v_uas2_enrolled = FALSE AND
984 v_uas2_completed = TRUE AND
985 v_uas2_discontin = FALSE) THEN
986 p_message_name := 'IGS_PS_COMPL_UNITATT_EXISTS';
987 -- all records contained statuses of 'ENROLLED' & 'COMPLETED'
988 ELSIF ( v_uas2_enrolled = TRUE AND
989 v_uas2_completed = TRUE AND
990 v_uas2_discontin = FALSE ) THEN
991 p_message_name := 'IGS_PS_ENR_COMPL_UNITATT_EXIS';
992 -- all records had a status of 'DISCONTIN'
993 ELSIF (v_uas2_enrolled = FALSE AND
994 v_uas2_completed = FALSE AND
995 v_uas2_discontin = TRUE) THEN
996 p_message_name := 'IGS_PS_DIS_UA_EXISTS_OVERRIDE';
997 -- all records contained statuses of 'ENROLLED' & 'DISCONTIN'
998 ELSIF (v_uas2_enrolled = TRUE AND
999 v_uas2_completed = FALSE AND
1000 v_uas2_discontin = TRUE ) THEN
1001 p_message_name := 'IGS_PS_DIS_ENR_UA_EXIST';
1002 -- all records contained statuses of 'COMPLETED' & 'DISCONTIN'
1003 ELSIF (v_uas2_enrolled = FALSE AND
1004 v_uas2_completed = TRUE AND
1005 v_uas2_discontin = TRUE) THEN
1006 p_message_name := 'IGS_PS_DIS_COMP_UA_EXISTS';
1007 -- all records contained statuses of 'COMPLETED' & 'DISCONTIN' & 'ENROLLED'
1008 -- or no records where found
1009 ELSIF (v_uas2_enrolled = TRUE AND
1010 v_uas2_completed = TRUE AND
1011 v_uas2_discontin = TRUE) THEN
1012 p_message_name := 'IGS_PS_ENR_COMP_UA_EXISTS';
1013 END IF;
1014 END IF;
1015 RETURN TRUE;
1016 END;
1017
1018 END crsp_val_uv_cp_ovrd;
1019 --
1020 -- Validate discont sua with pass grade within new uv overrides.
1021 FUNCTION crsp_val_uv_dsc_ovrd(
1022 p_unit_cd IN VARCHAR2 ,
1023 p_version_number IN NUMBER ,
1024 p_points_min IN NUMBER ,
1025 p_points_max IN NUMBER ,
1026 p_points_increment IN NUMBER ,
1027 p_message_name OUT NOCOPY VARCHAR2 )
1028 RETURN BOOLEAN AS
1029 /*
1030 | Who When What
1031 | knaraset 09-May-03 Modified WHERE clause of cursor c_uv_dsc_ovrd to include uoo_id, as part of MUS build bug 2829262
1032 |
1033 |*/
1034 BEGIN -- crsp_val_uv_dsc_ovrd
1035 -- This module validates student IGS_PS_UNIT attempt credit point override
1036 -- values against values in the IGS_PS_UNIT version for students that have
1037 -- a unit_attempt status of discontinued but a result type of pass.
1038 DECLARE
1039 cst_discontin CONSTANT VARCHAR2(9) := 'DISCONTIN';
1040 cst_pass CONSTANT VARCHAR2(4) := 'PASS';
1041 v_x VARCHAR2(1);
1042 CURSOR c_uv_dsc_ovrd IS
1043 SELECT 'x'
1044 FROM IGS_EN_SU_ATTEMPT sua,
1045 IGS_AS_SU_STMPTOUT suao,
1046 IGS_AS_GRD_SCH_GRADE gsg
1047 WHERE sua.unit_cd = p_unit_cd AND
1048 sua.version_number = p_version_number AND
1049 sua.person_id = suao.person_id AND
1050 sua.course_cd = suao.course_cd AND
1051 sua.uoo_id = suao.uoo_id AND
1052 sua.unit_attempt_status = cst_discontin AND
1053 suao.grading_schema_cd = gsg.grading_schema_cd AND
1054 suao.grade = gsg.grade AND
1055 suao.version_number = gsg.version_number AND
1056 gsg.s_result_type = cst_pass AND
1057 (p_points_min IS NULL OR
1058 NVL(sua.override_enrolled_cp,999999) < p_points_min OR
1059 NVL(sua.override_achievable_cp,999999) < p_points_min) AND
1060 (p_points_max IS NULL OR
1061 NVL(sua.override_enrolled_cp,0) > p_points_max OR
1062 NVL(sua.override_achievable_cp,0) > p_points_max) AND
1063 (p_points_increment IS NULL OR
1064 MOD(NVL(sua.override_enrolled_cp,p_points_increment),
1065 p_points_increment) <> 0.0 OR
1066 MOD(NVL(sua.override_achievable_cp,p_points_increment),
1067 p_points_increment) <> 0.0) ;
1068 BEGIN
1069 -- Set the default message number
1070 p_message_name := NULL;
1071 -- Validate IGS_PS_UNIT version
1072 OPEN c_uv_dsc_ovrd;
1073 FETCH c_uv_dsc_ovrd INTO v_x;
1074 IF c_uv_dsc_ovrd%FOUND THEN
1075 CLOSE c_uv_dsc_ovrd;
1076 p_message_name := 'IGS_PS_DISCONT_RESULT_TYPE';
1077 ELSE
1078 CLOSE c_uv_dsc_ovrd;
1079 END IF;
1080 RETURN TRUE;
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 IF c_uv_dsc_ovrd%ISOPEN THEN
1084 CLOSE c_uv_dsc_ovrd;
1085 END IF;
1086 App_Exception.Raise_Exception;
1087 END;
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1091 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_dsc_ovrd');
1092 IGS_GE_MSG_STACK.ADD;
1093 App_Exception.Raise_Exception;
1094 END crsp_val_uv_dsc_ovrd;
1095 --
1096 -- Validate IGS_PS_UNIT attempts when ending IGS_PS_UNIT version.
1097 FUNCTION crsp_val_uv_end(
1098 p_unit_cd IN VARCHAR2 ,
1099 p_version_number IN NUMBER ,
1100 p_return_type OUT NOCOPY VARCHAR2 ,
1101 p_message_name OUT NOCOPY VARCHAR2 )
1102 RETURN BOOLEAN AS
1103 BEGIN -- crsp_val_uv_end
1104 -- Perform checks required prior to the 'ending' of a IGS_PS_UNIT version, being
1105 -- - no 'enrolled' attempts can be linked to the version.
1106 DECLARE
1107 cst_enrolled CONSTANT
1108 IGS_LOOKUPS_VIEW.Lookup_Code%TYPE := 'ENROLLED';
1109 cst_unconfirm CONSTANT
1110 IGS_LOOKUPS_VIEW.Lookup_Code%TYPE := 'UNCONFIRM';
1111 cst_invalid CONSTANT
1112 IGS_LOOKUPS_VIEW.Lookup_Code%TYPE := 'INVALID';
1113 cst_error CONSTANT VARCHAR2(1) := 'E';
1114 cst_warning CONSTANT VARCHAR2(1) := 'W';
1115 v_sua_enrolled BOOLEAN := FALSE;
1116 v_sua_inv_unc BOOLEAN := FALSE;
1117 CURSOR c_sua IS
1118 SELECT DISTINCT sua.unit_attempt_status
1119 FROM IGS_EN_SU_ATTEMPT sua
1120 WHERE sua.unit_cd = p_unit_cd AND
1121 sua.version_number = p_version_number AND
1122 sua.unit_attempt_status in (cst_enrolled, cst_unconfirm, cst_invalid);
1123 BEGIN
1124 FOR v_sua_rec IN c_sua LOOP
1125 IF v_sua_rec.unit_attempt_status = cst_enrolled THEN
1126 v_sua_enrolled := TRUE;
1127 ELSE
1128 v_sua_inv_unc := TRUE;
1129 END IF;
1130 END LOOP;
1131 IF v_sua_enrolled = TRUE THEN
1132 p_message_name := 'IGS_PS_ENDPRG_ENROLLED';
1133 p_return_type := cst_error;
1134 RETURN FALSE;
1135 END IF;
1136 IF v_sua_inv_unc = TRUE THEN
1137 p_message_name := 'IGS_PS_UNCONFIRMED_INVALID';
1138 p_return_type := cst_warning;
1139 RETURN FALSE;
1140 END IF;
1141 p_message_name := NULL;
1142 p_return_type := NULL;
1143 RETURN TRUE;
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 IF (c_sua%ISOPEN) THEN
1147 CLOSE c_sua;
1148 END IF;
1149 App_Exception.Raise_Exception;
1150 END;
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1154 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_end');
1155 IGS_GE_MSG_STACK.ADD;
1156 App_Exception.Raise_Exception;
1157 END crsp_val_uv_end;
1158 --
1159 -- Validate if students have IGS_EN_SU_ATTEMPT IGS_PE_TITLE override set
1160 FUNCTION crsp_val_uv_ttl_ovrd(
1161 p_unit_cd IN VARCHAR2 ,
1162 p_version_number IN NUMBER ,
1163 p_title_override_ind IN VARCHAR2 ,
1164 p_message_name OUT NOCOPY VARCHAR2 )
1165 RETURN BOOLEAN AS
1166 BEGIN -- crsp_val_uv_ttl_ovrd
1167 -- validate the IGS_PE_TITLE indicator against student IGS_PS_UNIT attempt records.
1168 DECLARE
1169 cst_enrolled CONSTANT VARCHAR(10) := 'ENROLLED';
1170 cst_completed CONSTANT VARCHAR(10) := 'COMPLETED';
1171 cst_discontin CONSTANT VARCHAR(10) := 'DISCONTIN';
1172 v_unit_attempt_status
1173 IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := NULL;
1174 CURSOR c_sua IS
1175 SELECT sua.unit_attempt_status
1176 FROM IGS_EN_SU_ATTEMPT sua
1177 WHERE sua.unit_cd = p_unit_cd AND
1178 sua.version_number = p_version_number AND
1179 sua.unit_attempt_status IN (
1180 cst_enrolled,
1181 cst_completed,
1182 cst_discontin) AND
1183 sua.alternative_title IS NOT NULL;
1184 BEGIN
1185 p_message_name := NULL;
1186 IF p_title_override_ind = 'N' THEN
1187 FOR v_sua_rec IN c_sua LOOP
1188 IF c_sua%ROWCOUNT = 1 THEN
1189 v_unit_attempt_status := v_sua_rec.unit_attempt_status;
1190 IF v_unit_attempt_status = cst_enrolled THEN
1191 p_message_name := 'IGS_PS_ENR_UNITATT_EXIST_OVER';
1192 ELSIF v_unit_attempt_status = cst_completed THEN
1193 p_message_name := 'IGS_PS_COMPL_UNITATT_EXIST_AL';
1194 ELSE
1195 p_message_name := 'IGS_PS_DISCONT_UNIT_ATTEMPT';
1196 END IF;
1197 ELSE
1198 IF v_sua_rec.unit_attempt_status <> v_unit_attempt_status THEN
1199 p_message_name := 'IGS_PS_ENR_COMPL_DISCONT_UA';
1200 EXIT;
1201 END IF;
1202 END IF;
1203 END LOOP;
1204 END IF;
1205 RETURN TRUE;
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 IF (c_sua%ISOPEN) THEN
1209 CLOSE c_sua;
1210 END IF;
1211 App_Exception.Raise_Exception;
1212 END;
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1216 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UV.crsp_val_uv_ttl_ovrd');
1217 IGS_GE_MSG_STACK.ADD;
1218 App_Exception.Raise_Exception;
1219
1220 END crsp_val_uv_ttl_ovrd;
1221
1222
1223 PROCEDURE get_cp_values(
1224 p_uoo_id IN IGS_PS_UNIT_OFR_OPT_ALL.uoo_id%TYPE,
1225 p_enrolled_cp OUT NOCOPY IGS_PS_USEC_CPS.enrolled_credit_points%TYPE,
1226 p_billable_cp OUT NOCOPY IGS_PS_USEC_CPS.billing_hrs%TYPE,
1227 p_audit_cp OUT NOCOPY IGS_PS_USEC_CPS.billing_credit_points%TYPE) AS
1228 /***********************************************************************************************
1229 Created By : bdeviset
1230 Date Created : 21-JUL-2004
1231 Purpose : gets Enrolled, Audit and Billable credit point values for the passed unit section.
1232 Known limitations,enhancements,remarks:
1233 Change History :
1234 Who When What
1235 *************************************************************************************************/
1236
1237 l_uv_enrolled_cp IGS_PS_UNIT_VER.enrolled_credit_points%TYPE;
1238 l_uv_billing_cp IGS_PS_UNIT_VER.billing_hrs%TYPE;
1239 l_uv_audit_cp IGS_PS_UNIT_VER.billing_credit_points%TYPE;
1240 l_uoo_enrolled_cp IGS_PS_USEC_CPS.enrolled_credit_points%TYPE;
1241 l_uoo_billing_cp IGS_PS_USEC_CPS.billing_hrs%TYPE;
1242 l_uoo_audit_cp IGS_PS_USEC_CPS.billing_credit_points%TYPE;
1243
1244 -- fetches Enrolled, Audit and Billable credit point values from unit version table for given uoo_id
1245 CURSOR c_uv(cp_uoo_id IN IGS_PS_UNIT_OFR_OPT_ALL.uoo_id%TYPE) IS
1246 SELECT
1247 uv.enrolled_credit_points,
1248 uv.billing_hrs,
1249 uv.billing_credit_points
1250 FROM
1251 IGS_PS_UNIT_VER uv,
1252 IGS_PS_UNIT_OFR_OPT uoo
1253 WHERE
1254 uoo.uoo_id = cp_uoo_id AND
1255 uoo.unit_cd = uv.unit_cd AND
1256 uoo.version_number = uv.version_number;
1257
1258 -- fetches Enrolled, Audit and Billable credit point values from unit section table for given uoo_id
1259 CURSOR c_uoo(cp_uoo_id IN IGS_PS_UNIT_OFR_OPT_ALL.uoo_id%TYPE) IS
1260 SELECT
1261 us.enrolled_credit_points,
1262 us.billing_hrs,
1263 us.billing_credit_points
1264 FROM
1265 IGS_PS_USEC_CPS us
1266 WHERE
1267 us.uoo_id = cp_uoo_id;
1268
1269 BEGIN
1270
1271 -- fetches Enrolled, Audit and Billable credit point values into local variables
1272 -- for unit version level
1273 OPEN c_uv(p_uoo_id);
1274 FETCH c_uv INTO l_uv_enrolled_cp,l_uv_billing_cp,l_uv_audit_cp;
1275 CLOSE c_uv;
1276
1277 -- fetches Enrolled, Audit and Billable credit point values into local variables
1278 -- for unit section level
1279 OPEN c_uoo(p_uoo_id);
1280 FETCH c_uoo INTO l_uoo_enrolled_cp,l_uoo_billing_cp,l_uoo_audit_cp;
1281 CLOSE c_uoo;
1282
1283 -- gets the Enrolled and Audit credit point values of unit section
1284 -- if null takes the value of unit version in out parameter
1285 p_enrolled_cp := NVL(l_uoo_enrolled_cp, l_uv_enrolled_cp);
1286 p_audit_cp := NVL(l_uoo_audit_cp, l_uv_audit_cp);
1287
1288 --If billable cp is defined at unit section level then the same is set in the out parameter.
1289 --If billable cp is not defined at unit section level but enrolled cp is then out parameter is set as null.
1290 --If billable cp and enrolled cp is not defined at unit section level and if billable cp at unit version level
1291 --is defined then billable cp at unit version is set in the out parameter else it is kept null.
1292 p_billable_cp := l_uoo_billing_cp;
1293 IF p_billable_cp IS NULL AND l_uoo_enrolled_cp IS NULL THEN
1294 p_billable_cp := l_uv_billing_cp;
1295 END IF;
1296
1297 END get_cp_values;
1298 END IGS_PS_VAL_UV;
[Home] [Help]