1 PACKAGE BODY IGS_PS_VAL_CRV AS
2 /* $Header: IGSPS34B.pls 120.2 2006/07/25 15:10:23 sommukhe noship $ */
3
4
5 /* WHO WHEN WHAT
6 sommukhe 19-Jul-2006 Bug#5343926,CIP Design Cganges forward prted from 115.
7 sommukhe 16-FEB-2006 Bug#3094371, replaced IGS_OR_UNIT by igs_or_inst_org_base_v for cursor c_get_s_org_status in function crsp_val_ou_sys_sts
8 sarakshi 23-Feb-2003 Enh#2797116,Modified the cursor c_course_offfering_option in crsp_val_crv_quality
9 procedure
10 */
11 -- Validate IGS_PS_COURSE version government special IGS_PS_COURSE type.
12 FUNCTION crsp_val_crv_gsct(
13 p_govt_special_course_type IN VARCHAR2 ,
14 p_message_name OUT NOCOPY VARCHAR2 )
15 RETURN BOOLEAN AS
16 v_closed_ind IGS_PS_GOVT_SPL_TYPE.closed_ind%TYPE;
17 CURSOR c_govt_special_course_type IS
18 SELECT closed_ind
19 FROM IGS_PS_GOVT_SPL_TYPE
20 WHERE govt_special_course_type = p_govt_special_course_type;
21 BEGIN
22 OPEN c_govt_special_course_type;
23 FETCH c_govt_special_course_type INTO v_closed_ind;
24 IF c_govt_special_course_type%NOTFOUND THEN
25 p_message_name := NULL;
26 CLOSE c_govt_special_course_type;
27 RETURN TRUE;
28 ELSIF (v_closed_ind = 'N') THEN
29 p_message_name := NULL;
30 CLOSE c_govt_special_course_type;
31 RETURN TRUE;
32 ELSE
33 p_message_name := 'IGS_PS_GOVT_SPLPRGTYPE_CLOSED';
34 CLOSE c_govt_special_course_type;
35 RETURN FALSE;
36 END IF;
37 EXCEPTION
38 WHEN OTHERS THEN
39 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
40 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_gsct');
41 IGS_GE_MSG_STACK.ADD;
42 APP_EXCEPTION.RAISE_EXCEPTION;
43 END crsp_val_crv_gsct;
44 --
45 -- Validate IGS_PS_COURSE version IGS_PS_COURSE type.
46 FUNCTION crsp_val_crv_type(
47 p_course_cd IN VARCHAR2 ,
48 p_version_number IN NUMBER ,
49 p_course_type IN VARCHAR2 ,
50 p_message_name OUT NOCOPY VARCHAR2 )
51 RETURN BOOLEAN AS
52 /***************************************************************
53 Created By :
54 Date Created By :
55 Purpose :
56 Known Limitations,Enhancements or Remarks:
57 Change History :
58 Who When What
59 smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_course_award_rec to select open program awards only.
60 ***************************************************************/
61
62 v_closed_ind IGS_PS_TYPE.closed_ind%TYPE;
63 v_award_course_ind IGS_PS_TYPE.award_course_ind%TYPE;
64 v_course_award_rec IGS_PS_AWARD%ROWTYPE;
65 CURSOR c_course_type IS
66 SELECT closed_ind,
67 award_course_ind
68 FROM IGS_PS_TYPE
69 WHERE course_type = p_course_type;
70 CURSOR c_course_award_rec IS
71 SELECT *
72 FROM IGS_PS_AWARD
73 WHERE course_cd = p_course_cd AND
74 version_number = p_version_number AND
75 CLOSED_IND = 'N';
76 BEGIN
77 -- validating the IGS_PS_VER.IGS_PS_TYPE
78 OPEN c_course_type;
79 FETCH c_course_type INTO v_closed_ind, v_award_course_ind;
80 IF c_course_type%NOTFOUND THEN
81 p_message_name := NULL;
82 CLOSE c_course_type;
83 RETURN TRUE;
84 END IF;
85 IF (v_closed_ind = 'Y') THEN
86 p_message_name := 'IGS_PS_PRGTYPE_CLOSED';
87 CLOSE c_course_type;
88 RETURN FALSE;
89 END IF;
90 CLOSE c_course_type;
91 -- validating the IGS_PS_COURSE IGS_PS_AWDs
92 OPEN c_course_award_rec;
93 FETCH c_course_award_rec INTO v_course_award_rec;
94 IF (c_course_award_rec%NOTFOUND) THEN
95 p_message_name := NULL;
96 CLOSE c_course_award_rec;
97 RETURN TRUE;
98 END IF;
99 IF (c_course_award_rec%FOUND) THEN
100 IF (v_award_course_ind = 'Y') THEN
101 p_message_name := NULL;
102 CLOSE c_course_award_rec;
103 RETURN TRUE;
104 ELSE
105 p_message_name := 'IGS_PS_PRGAWARD_EXISTS_FORPRG';
106 CLOSE c_course_award_rec;
107 RETURN FALSE;
108 END IF;
109 END IF;
110 EXCEPTION
111 WHEN OTHERS THEN
112 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
113 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_type');
114 IGS_GE_MSG_STACK.ADD;
115 APP_EXCEPTION.RAISE_EXCEPTION;
116 END crsp_val_crv_type;
117
118
119 -- Validate organisational IGS_PS_UNIT system status is ACTIVE
120 FUNCTION crsp_val_ou_sys_sts(
121 p_org_unit_cd IN VARCHAR2 ,
122 p_start_dt IN DATE ,
123 p_message_name OUT NOCOPY VARCHAR2 )
124 RETURN BOOLEAN AS
125 v_s_org_status IGS_OR_STATUS.s_org_status%TYPE;
126 CURSOR c_get_s_org_status IS
127 SELECT s_org_status
128 FROM igs_or_inst_org_base_v,
129 IGS_OR_STATUS
130 WHERE party_number = p_org_unit_cd AND
131 start_dt = p_start_dt AND
132 igs_or_inst_org_base_v.org_status = IGS_OR_STATUS.org_status;
133 BEGIN
134 -- Validate organisational IGS_PS_UNIT system status.
135 p_message_name := NULL;
136 OPEN c_get_s_org_status;
137 FETCH c_get_s_org_status INTO v_s_org_status;
138 IF c_get_s_org_status%NOTFOUND THEN
139 CLOSE c_get_s_org_status;
140 RETURN TRUE;
141 END IF;
142 CLOSE c_get_s_org_status;
143 IF (v_s_org_status <> 'INACTIVE') THEN
144 RETURN TRUE;
145 ELSE
146 p_message_name := 'IGS_PS_ORGUNIT_STATUS_INACTIV';
147 RETURN FALSE;
148 END IF;
149 EXCEPTION
150 WHEN OTHERS THEN
151 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
152 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_ou_sys_sts');
153 IGS_GE_MSG_STACK.ADD;
154 APP_EXCEPTION.RAISE_EXCEPTION;
155 END crsp_val_ou_sys_sts;
156 --
157 -- Validate the IGS_PS_COURSE version end date and status.
158 FUNCTION crsp_val_crv_end_sts(
159 p_end_dt IN DATE ,
160 p_course_status IN VARCHAR2 ,
161 p_message_name OUT NOCOPY VARCHAR2 )
162 RETURN BOOLEAN AS
163 v_s_course_status IGS_PS_STAT.s_course_status%TYPE;
164 CURSOR c_get_s_course_status IS
165 SELECT s_course_status
166 FROM IGS_PS_STAT
167 WHERE course_status = p_course_status;
168 BEGIN
169 p_message_name := NULL;
170 OPEN c_get_s_course_status;
171 FETCH c_get_s_course_status INTO v_s_course_status;
172 IF c_get_s_course_status%NOTFOUND THEN
173 CLOSE c_get_s_course_status;
174 RETURN TRUE;
175 END IF;
176 CLOSE c_get_s_course_status;
177 -- Validate end date and IGS_PS_COURSE status.
178 IF (p_end_dt IS NOT NULL) THEN
179 IF (v_s_course_status = 'INACTIVE') THEN
180 RETURN TRUE;
181 ELSE
182 p_message_name := 'IGS_PS_STSET_INACTIVE_ENDDT';
183 RETURN FALSE;
184 END IF;
185 ELSE -- p_end_dt is null
186 IF (v_s_course_status <> 'INACTIVE') THEN
187 RETURN TRUE;
188 ELSE
189 p_message_name := 'IGS_PS_STNOTSET_INACTIVE_OPDT';
190 RETURN FALSE;
191 END IF;
192 END IF;
193 EXCEPTION
194 WHEN OTHERS THEN
195 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
196 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_end_sts');
197 IGS_GE_MSG_STACK.ADD;
198 APP_EXCEPTION.RAISE_EXCEPTION;
199 END crsp_val_crv_end_sts;
200 --
201 -- Validate IGS_PS_COURSE version expiry date and status
202 FUNCTION crsp_val_crv_exp_sts(
203 p_course_cd IN VARCHAR2 ,
204 p_version_number IN NUMBER ,
205 p_expiry_dt IN DATE ,
206 p_course_status IN VARCHAR2 ,
207 p_message_name OUT NOCOPY VARCHAR2 )
208 RETURN BOOLEAN AS
209 v_s_course_status IGS_PS_STAT.s_course_status%TYPE;
210 v_check CHAR;
211 CURSOR c_get_s_course_status IS
212 SELECT s_course_status
213 FROM IGS_PS_STAT
214 WHERE course_status = p_course_status;
215 CURSOR c_check_cv_cs IS
216 SELECT 'x'
217 FROM IGS_PS_VER cv,
218 IGS_PS_STAT cs
219 WHERE
220 course_cd = p_course_cd AND
221 version_number <> p_version_number AND
222 expiry_dt IS NULL AND
223 cv.course_status = cs.course_status AND
224 cs.s_course_status = 'ACTIVE';
225 BEGIN
226 p_message_name := NULL;
227 OPEN c_get_s_course_status;
228 FETCH c_get_s_course_status INTO v_s_course_status;
229 IF c_get_s_course_status%NOTFOUND THEN
230 CLOSE c_get_s_course_status;
231 RETURN TRUE;
232 END IF;
233 CLOSE c_get_s_course_status;
234 -- Validate expiry date and IGS_PS_COURSE status.
235 IF (v_s_course_status = 'ACTIVE') AND (p_expiry_dt IS NULL) THEN
236 OPEN c_check_cv_cs;
237 FETCH c_check_cv_cs INTO v_check;
238 IF c_check_cv_cs%FOUND THEN
239 CLOSE c_check_cv_cs;
240 p_message_name := 'IGS_PS_ANOTHER_VERSION_EXISTS';
241 RETURN FALSE;
242 END IF;
243 CLOSE c_check_cv_cs;
244 END IF;
245 RETURN TRUE;
246 EXCEPTION
247 WHEN OTHERS THEN
248 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
249 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_exp_sts');
250 IGS_GE_MSG_STACK.ADD;
251 APP_EXCEPTION.RAISE_EXCEPTION;
252 END crsp_val_crv_exp_sts;
253 --
254 -- Validate the IGS_PS_COURSE version status.
255 FUNCTION crsp_val_crv_status(
256 p_new_course_status IN VARCHAR2 ,
257 p_old_course_status IN VARCHAR2 ,
258 p_message_name OUT NOCOPY VARCHAR2 )
259 RETURN BOOLEAN AS
260 v_s_course_status_new IGS_PS_STAT.s_course_status%TYPE;
261 v_s_course_status_old IGS_PS_STAT.s_course_status%TYPE;
262 v_closed_ind IGS_PS_STAT.closed_ind%TYPE;
263 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
264 CURSOR c_course_status (cp_course_status IGS_PS_STAT.course_status%TYPE) IS
265 SELECT closed_ind,
266 s_course_status
267 FROM IGS_PS_STAT
268 WHERE course_status = cp_course_status;
269 BEGIN
270 -- Validating the closed indicator
271 OPEN c_course_status(p_new_course_status);
272 FETCH c_course_status INTO v_closed_ind, v_s_course_status_new;
273 IF (v_closed_ind = 'Y') THEN
274 p_message_name := 'IGS_PS_PRGSTATUS_CLOSED';
275 CLOSE c_course_status;
276 RETURN FALSE;
277 END IF;
278 CLOSE c_course_status;
279 -- Validating whether IGS_PS_VER.IGS_PS_STAT isn't being changed
280 -- from 'ACTIVE' or 'INACTIVE' to 'PLANNED'. This is only checked when
281 -- IGS_PS_VER.IGS_PS_STAT
282 -- is being updated.
283 IF (p_old_course_status IS NOT NULL) AND
284 (p_new_course_status <> p_old_course_status) THEN
285 OPEN c_course_status(p_old_course_status);
286 FETCH c_course_status INTO v_closed_ind, v_s_course_status_old;
287 IF (v_s_course_status_new <> v_s_course_status_old) THEN
288 IF (v_s_course_status_new = cst_planned) THEN
289 p_message_name := 'IGS_PS_PRGSTATUS_NOT_ALTERED';
290 CLOSE c_course_status;
291 RETURN FALSE;
292 END IF;
293 END IF;
294 CLOSE c_course_status;
295 END IF;
296 -- Validating whether p_new_course_status is 'PLANNED' when
297 -- p_old_course_status system IGS_PS_COURSE status isn't set.
298 IF (p_old_course_status IS NULL) AND
299 (v_s_course_status_new <> cst_planned) THEN
300 p_message_name := 'IGS_PS_NEWVER_STATUS_PLANNED';
301 RETURN FALSE;
302 END IF;
303 p_message_name := NULL;
304 RETURN TRUE;
305 EXCEPTION
306 WHEN OTHERS THEN
307 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
308 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_status');
309 IGS_GE_MSG_STACK.ADD;
310 APP_EXCEPTION.RAISE_EXCEPTION;
311 END crsp_val_crv_status;
312 --
313 -- Perform quality validation checks on a IGS_PS_COURSE version and its details.
314 FUNCTION CRSP_VAL_CRV_QUALITY(
315 p_course_cd IN VARCHAR2 ,
316 p_version_number IN NUMBER ,
317 p_old_course_status IN VARCHAR2 ,
318 p_message_name OUT NOCOPY VARCHAR2 )
319 RETURN BOOLEAN AS
320 /***************************************************************
321 Created By :
322 Date Created By :
323 Purpose :
324 Known Limitations,Enhancements or Remarks:
325 Change History :
326 Who When What
327 smvk 03-Jun-2003 Bug # 2858436. Modified the cursor c_get_award_cd to select open program awards only.
328 skpandey 10-Jul-2006 Bug#5343912,removed the validation of 100% Field of study as this was used as check for updating status to active.
329 ***************************************************************/
330
331 v_cv_rec IGS_PS_VER%ROWTYPE;
332 v_award_cd IGS_PS_AWARD.award_cd%TYPE;
333 v_funding_source IGS_FI_FND_SRC_RSTN.funding_source%TYPE;
334 v_award_course_ind IGS_PS_TYPE.award_course_ind%TYPE;
335 v_field_of_study IGS_PS_FIELD_STUDY.field_of_study%TYPE;
336 v_course_cat IGS_PS_CATEGORISE.course_cat%TYPE;
337 v_s_course_status IGS_PS_STAT.s_course_status%TYPE;
338 v_terminate BOOLEAN := FALSE;
339 v_coo_exist BOOLEAN := FALSE;
340 CURSOR c_course_version IS
341 SELECT *
342 FROM IGS_PS_VER
343 WHERE course_cd = p_course_cd AND
344 version_number = p_version_number;
345 CURSOR c_get_award_course_ind (
346 cp_course_type IGS_PS_VER.course_type%TYPE) IS
347 SELECT award_course_ind
348 FROM IGS_PS_TYPE
349 WHERE course_type = cp_course_type;
350 CURSOR c_get_award_cd IS
351 SELECT award_cd
352 FROM IGS_PS_AWARD
353 WHERE course_cd = p_course_cd AND
354 version_number = p_version_number AND
355 CLOSED_IND = 'N';
356 CURSOR c_get_funding_source IS
357 SELECT funding_source
358 FROM IGS_FI_FND_SRC_RSTN
359 WHERE course_cd = p_course_cd AND
360 version_number = p_version_number;
361 CURSOR c_get_field_of_study IS
362 SELECT field_of_study
363 FROM IGS_PS_FIELD_STUDY
364 WHERE course_cd = p_course_cd AND
365 version_number = p_version_number;
366 CURSOR c_get_course_cat IS
367 SELECT course_cat
368 FROM IGS_PS_CATEGORISE
369 WHERE course_cd = p_course_cd AND
370 version_number = p_version_number;
371 CURSOR c_get_reference_cd_type IS
372 SELECT reference_cd_type
373 FROM IGS_PS_REF_CD
374 WHERE course_cd = p_course_cd AND
375 version_number = p_version_number;
376 CURSOR c_get_course_group_cd IS
377 SELECT course_group_cd
378 FROM IGS_PS_GRP_MBR
379 WHERE course_cd = p_course_cd AND
380 version_number = p_version_number;
381 CURSOR c_course_annual_load_unit_link IS
382 SELECT unit_cd,
383 uv_version_number
384 FROM IGS_PS_ANL_LOAD_U_LN
385 WHERE course_cd = p_course_cd AND
386 crv_version_number =p_version_number;
387 CURSOR c_get_cal_type IS
388 SELECT cal_type
389 FROM IGS_PS_OFR
390 WHERE course_cd = p_course_cd AND
391 version_number = p_version_number;
392 CURSOR c_course_offering_option IS
393 SELECT location_cd,
394 attendance_type,
395 attendance_mode
396 FROM IGS_PS_OFR_OPT
397 WHERE course_cd = p_course_cd AND
398 version_number = p_version_number AND
399 delete_flag = 'N';
400 CURSOR c_get_dist_reference_cd_type IS
401 SELECT DISTINCT reference_cd_type
402 FROM IGS_PS_ENT_PT_REF_CD
403 WHERE course_cd = p_course_cd AND
404 version_number = p_version_number;
405 CURSOR c_get_s_course_status IS
406 SELECT s_course_status
407 FROM IGS_PS_STAT
408 WHERE course_status = p_old_course_status;
409 CURSOR c_course_offering_instance IS
410 SELECT cal_type, ci_sequence_number
411 FROM IGS_PS_OFR_INST
412 WHERE course_cd = p_course_cd AND
413 version_number = p_version_number;
414 BEGIN
415 OPEN c_course_version;
416 FETCH c_course_version INTO v_cv_rec;
417 -- no IGS_PS_VER found
418 IF (c_course_version%NOTFOUND) THEN
419 CLOSE c_course_version;
420 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
421 RETURN FALSE;
422 END IF;
423 CLOSE c_course_version;
424 -- Validate that there is only one funding_source_resstriction table
425 -- set to default for a IGS_PS_COURSE version.
426 IF (IGS_PS_VAL_FSr.crsp_val_fsr_default (
427 p_course_cd,
428 p_version_number,
429 p_message_name) = FALSE) THEN
430 RETURN FALSE;
431 END IF;
432 -- validate that all records have there restriction indicator set.
433 IF (IGS_PS_VAL_FSr.crsp_val_fsr_rstrct (
434 p_course_cd,
435 p_version_number,
436 p_message_name) = FALSE) THEN
437 RETURN FALSE;
438 END IF;
439 -- Validate that IGS_PS_GOVT_SPL_TYPE is not closed
440 IF (IGS_PS_VAL_CRV.crsp_val_crv_gsct(
441 v_cv_rec.govt_special_course_type,
442 p_message_name) = FALSE) THEN
443 RETURN FALSE;
444 END IF;
445 -- Validate the IGS_PS_TYPE is not closed
446 IF (IGS_PS_VAL_CRV.crsp_val_crv_type(
447 p_course_cd,
448 p_version_number,
449 v_cv_rec.course_type,
450 p_message_name) = FALSE) THEN
451 RETURN FALSE;
452 END IF;
453 -- Validate that the responsible_org_unit_cd is active
454 IF (IGS_PS_VAL_CRV.crsp_val_ou_sys_sts(
455 v_cv_rec.responsible_org_unit_cd,
456 v_cv_rec.responsible_ou_start_dt,
457 p_message_name) = FALSE) THEN
458 RETURN FALSE;
459 END IF;
460 -- Validate the IGS_PS_FIELD_STUDY record percentage of total 100%. Removed Code
461
462 -- Validate the IGS_PS_OWN record percentage total 100%
463 IF (IGS_PS_VAL_COw.crsp_val_cow_perc(
464 p_course_cd,
465 p_version_number,
466 p_message_name) = FALSE) THEN
467 RETURN FALSE;
468 END IF;
469 OPEN c_get_award_course_ind(v_cv_rec.course_type);
470 FETCH c_get_award_course_ind INTO v_award_course_ind;
471 CLOSE c_get_award_course_ind;
472 -- If IGS_PS_VER is an IGS_PS_AWD IGS_PS_COURSE, check that the IGS_PS_AWD is open
473 -- and that the IGS_PS_AWD_OWN percentages total 100% for
474 -- a IGS_PS_VER IGS_PS_AWD
475 IF (v_award_course_ind = 'Y') THEN
476 OPEN c_get_award_cd;
477 LOOP
478 FETCH c_get_award_cd INTO v_award_cd;
479 EXIT WHEN c_get_award_cd%NOTFOUND;
480 IF (IGS_PS_VAL_CAW.crsp_val_caw_award(
481 v_award_cd,
482 p_message_name) = FALSE) THEN
483 v_terminate := TRUE;
484 EXIT;
485 END IF;
486 IF (IGS_PS_VAL_CAO.crsp_val_cao_perc(
487 p_course_cd,
488 p_version_number,
489 v_award_cd,
490 p_message_name) = FALSE) THEN
491 v_terminate := TRUE;
492 EXIT;
493 END IF;
494 END LOOP;
495 IF (v_terminate = TRUE) THEN
496 CLOSE c_get_award_cd;
497 RETURN FALSE;
498 END IF;
499 -- IGS_PS_VER is an IGS_PS_AWD IGS_PS_COURSE and no record exist in the
500 -- IGS_PS_AWARD table for p_course_cd and p_version_number
501 IF (c_get_award_cd%ROWCOUNT = 0) THEN
502 CLOSE c_get_award_cd;
503 p_message_name := 'IGS_PS_PRGVER_AWARDPRG';
504 RETURN FALSE;
505 END IF;
506 CLOSE c_get_award_cd;
507 END IF; -- course_award_ind is 'Y'
508 -- Validate the IGS_FI_FND_SRC_RSTN table and that the
509 -- IGS_FI_FUND_SRC is not closed.
510 FOR fs_rec IN c_get_funding_source LOOP
511 IF (IGS_PS_VAL_FSr.crsp_val_fsr_fnd_src(
512 fs_rec.funding_source,
513 p_message_name) = FALSE) THEN
514 v_terminate := TRUE;
515 EXIT; -- premature exit loop
516 END IF;
517 END LOOP;
518 IF (v_terminate = TRUE) THEN
519 RETURN FALSE;
520 END IF;
521 -- validate the IGS_PS_FIELD_STUDY table and that the
522 -- IGS_PS_FLD_OF_STUDY is not closed
523 FOR fos_rec IN c_get_field_of_study LOOP
524 IF (IGS_PS_VAL_CFOS.crsp_val_cfos_fos(
525 fos_rec.field_of_study,
526 p_message_name) = FALSE) THEN
527 v_terminate := TRUE;
528 EXIT; -- premature exit loop
529 END IF;
530 END LOOP;
531 IF (v_terminate = TRUE) THEN
532 RETURN FALSE;
533 END IF;
534 -- Validate the IGS_PS_CATEGORISE table and
535 -- that IGS_PS_CAT is not closed
536 FOR cc_rec IN c_get_course_cat LOOP
537 IF (IGS_PS_VAL_CRC.crsp_val_crc_crs_cat(
538 cc_rec.course_cat,
539 p_message_name) = FALSE) THEN
540 v_terminate := TRUE;
541 EXIT; -- premature exit loop
542 END IF;
543 END LOOP;
544 IF (v_terminate = TRUE) THEN
545 RETURN FALSE;
546 END IF;
547 -- Validate the IGS_PS_REF_CD table and that
548 -- IGS_GE_REF_CD_TYPE is not closed
549 FOR crc_rec IN c_get_reference_cd_type LOOP
550 IF (IGS_PS_VAL_CRFC.crsp_val_ref_cd_type(
551 crc_rec.reference_cd_type,
552 p_message_name) = FALSE) THEN
553 v_terminate := TRUE;
554 EXIT; -- premature exit loop
555 END IF;
556 END LOOP;
557 IF (v_terminate = TRUE) THEN
558 RETURN FALSE;
559 END IF;
560 -- Validate the IGS_PS_GRP_MBR table and that
561 -- course_group_cd is not closed.
562 FOR cgm_rec IN c_get_course_group_cd LOOP
563 IF (IGS_PS_VAL_CGM.crsp_val_cgm_crs_grp(
564 cgm_rec.course_group_cd,
565 p_message_name) = FALSE) THEN
566 v_terminate := TRUE;
567 EXIT; -- premature exit loop
568 END IF;
569 END LOOP;
570 IF (v_terminate = TRUE) THEN
571 RETURN FALSE;
572 END IF;
573 -- Validate that if IGS_PS_ANL_LOAD_U_LN records exist,
574 -- check that the associated IGS_PS_UNIT version(s) is not inactive
575 FOR calul_rec IN c_course_annual_load_unit_link LOOP
576 IF (IGS_PS_VAL_CALul.crsp_val_uv_sys_sts(
577 calul_rec.unit_cd,
578 calul_rec.uv_version_number,
579 p_message_name) = FALSE) THEN
580 v_terminate := TRUE;
581 EXIT; -- premature exit loop
582 END IF;
583 END LOOP;
584 IF (v_terminate = TRUE) THEN
585 RETURN FALSE;
586 END IF;
587 OPEN c_get_s_course_status;
588 FETCH c_get_s_course_status INTO v_s_course_status;
589 -- no IGS_PS_STAT found
590 IF (c_get_s_course_status%NOTFOUND) THEN
591 CLOSE c_get_s_course_status;
592 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
593 RETURN FALSE;
594 END IF;
595 CLOSE c_get_s_course_status;
596 IF (v_s_course_status = 'PLANNED') THEN
597 -- Validate that if IGS_PS_OFR records exist,
598 -- check that the IGS_CA_TYPE is not closed
599 FOR co_rec IN c_get_cal_type LOOP
600 IF (IGS_PS_VAL_CO.crsp_val_co_cal_type(
601 co_rec.cal_type,
602 p_message_name) = FALSE) THEN
603 v_terminate := TRUE;
604 EXIT; -- premature exit loop
605 END IF;
606 END LOOP;
607 IF (v_terminate = TRUE) THEN
608 RETURN FALSE;
609 END IF;
610 -- Validate IGS_PS_OFR_OPT record(s) if it exists and
611 -- the closed indicators associated with the fields of the record
612 FOR coo_rec IN c_course_offering_option LOOP
613 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_COO.crsp_val_loc_cd
614 IF (IGS_PS_VAL_UOO.crsp_val_loc_cd(
615 coo_rec.location_cd,
616 p_message_name) = FALSE) THEN
617 v_terminate := TRUE;
618 EXIT; -- premature exit loop
619 END IF;
620 IF (IGS_PS_VAL_COo.crsp_val_coo_am(
621 coo_rec.attendance_mode,
622 p_message_name) = FALSE) THEN
623 v_terminate := TRUE;
624 EXIT; -- premature exit loop
625 END IF;
626 IF (IGS_PS_VAL_COo.crsp_val_coo_att(
627 coo_rec.attendance_type, p_message_name) = FALSE) THEN
628 v_terminate := TRUE;
629 EXIT; -- premature exit loop
630 END IF;
631 v_coo_exist := TRUE;
632 END LOOP;
633 IF (v_terminate = TRUE) THEN
634 RETURN FALSE;
635 END IF;
636 -- Validate IGS_PS_ENT_PT_REF_CD record(s) if
637 -- it exists and that IGS_GE_REF_CD_TYPE is not closed.
638 -- Validate IGS_PS_OFR_INST record(s) if
639 -- it exists and the IGS_CA_INST.IGS_CA_STAT is ACTIVE.
640 -- Only perform these if course_offering_records exist.
641 IF (v_coo_exist = TRUE) THEN
642 FOR ceprc_rec IN c_get_dist_reference_cd_type LOOP
643 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_CEPRC.crsp_val_ref_cd_type
644 IF (IGS_PS_VAL_CRFC.crsp_val_ref_cd_type(
645 ceprc_rec.reference_cd_type,
646 p_message_name) = FALSE) THEN
647 v_terminate := TRUE;
648 EXIT; -- premature exit loop
649 END IF;
650 END LOOP;
651 IF (v_terminate = TRUE) THEN
652 RETURN FALSE;
653 END IF;
654 FOR coi_rec IN c_course_offering_instance LOOP
655 IF (IGS_as_VAL_uai.crsp_val_crs_ci(
656 coi_rec.cal_type,
657 coi_rec.ci_sequence_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 END IF; -- v_coo_exist = TRUE
667 END IF; -- v_s_course_status = 'PLANNED'
668 -- Validation successfull
669 p_message_name := NULL;
670 RETURN TRUE;
671 EXCEPTION
672 WHEN OTHERS THEN
673 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
674 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_quality');
675 IGS_GE_MSG_STACK.ADD;
676 APP_EXCEPTION.RAISE_EXCEPTION;
677 END crsp_val_crv_quality;
678 --
679 -- Validate that a IGS_PS_COURSE version can end, looking at sca status
680 FUNCTION crsp_val_crv_end(
681 p_course_cd IN VARCHAR2 ,
682 p_version_number IN NUMBER ,
683 p_return_type OUT NOCOPY VARCHAR2 ,
684 p_message_name OUT NOCOPY VARCHAR2 )
685 RETURN BOOLEAN AS
686 BEGIN -- crsp_val_crv_end
687 -- Perform checks required prior to the 'ending' of a IGS_PS_COURSE version, being:
688 -- - no IGS_PS_COURSE attempts can be linked to the version with a status in
689 -- 'Enrolled', 'Inactive', 'Intermitted',
690 -- A warning is produced if a IGS_PS_COURSE attempt exists with a status of
691 -- 'Lapsed' or 'Unconfirmed'.
692 DECLARE
693 cst_enrolled CONSTANT
694 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
695 cst_inactive CONSTANT
696 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INACTIVE';
697 cst_intermit CONSTANT
698 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INTERMIT';
699 cst_lapsed CONSTANT
700 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'LAPSED';
701 cst_unconfirm CONSTANT
702 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'UNCONFIRM';
703 cst_error CONSTANT VARCHAR2(1) := 'E';
704 cst_warning CONSTANT VARCHAR2(1) := 'W';
705 v_dummy VARCHAR2(1);
706 CURSOR c_sca1 IS
707 SELECT 'X'
708 FROM IGS_EN_STDNT_PS_ATT sca
709 WHERE sca.course_cd = p_course_cd AND
710 sca.version_number = p_version_number AND
711 sca.course_attempt_status IN (
712 cst_enrolled,
713 cst_inactive,
714 cst_intermit);
715 CURSOR c_sca2 IS
716 SELECT 'X'
717 FROM IGS_EN_STDNT_PS_ATT sca
718 WHERE sca.course_cd = p_course_cd AND
719 sca.version_number = p_version_number AND
720 sca.course_attempt_status IN (
721 cst_lapsed,
722 cst_unconfirm);
723 BEGIN
724 OPEN c_sca1;
725 FETCH c_sca1 INTO v_dummy;
726 IF (c_sca1%FOUND) THEN
727 CLOSE c_sca1;
728 p_return_type := cst_error;
729 p_message_name := 'IGS_PS_ENDPRG_ENROLLED_INACTV';
730 RETURN FALSE;
731 END IF;
732 CLOSE c_sca1;
733 OPEN c_sca2;
734 FETCH c_sca2 INTO v_dummy;
735 IF (c_sca2%FOUND) THEN
736 CLOSE c_sca2;
737 p_return_type := cst_warning;
738 p_message_name := 'IGS_PS_LAPSED_UNCONFIRMED_LIN';
739 RETURN FALSE;
740 END IF;
741 CLOSE c_sca2;
742 p_return_type := NULL;
743 p_message_name := NULL;
744 RETURN TRUE;
745 EXCEPTION
746 WHEN OTHERS THEN
747 IF (c_sca1%ISOPEN) THEN
748 CLOSE c_sca1;
749 END IF;
750 IF (c_sca2%ISOPEN) THEN
751 CLOSE c_sca2;
752 END IF;
753 APP_EXCEPTION.RAISE_EXCEPTION;
754 END;
755 EXCEPTION
756 WHEN OTHERS THEN
757 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
758 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_CRV.crsp_val_crv_end');
759 IGS_GE_MSG_STACK.ADD;
760 APP_EXCEPTION.RAISE_EXCEPTION;
761 END crsp_val_crv_end;
762 END IGS_PS_VAL_CRV;
[Home] [Help]