1 PACKAGE BODY IGS_PS_VAL_US AS
2 /* $Header: IGSPS68B.pls 115.6 2002/11/29 03:09:51 nsidana ship $ */
3 /*change history:
4 who when what
5 sarakshi 14-nov-2002 bug#2649028,modified function crsp_val_ver_dt,added parameter p_lgcy_validator
6 and corresponding validations
7 vvutukur 08-apr-2002 modifications done in crsp_val_us_category for bug#2121770.*/
8 --
9 -- Validate the IGS_PS_UNIT set status closed indicator.
10 FUNCTION crsp_val_uss_closed(
11 p_unit_set_status IN VARCHAR2 ,
12 p_message_name OUT NOCOPY VARCHAR2 )
13 RETURN BOOLEAN AS
14 BEGIN -- crsp_val_uss_closed
15 -- Validate the IGS_PS_UNIT set status closed indicator
16 DECLARE
17 v_closed_ind IGS_EN_UNIT_SET_STAT.closed_ind%TYPE;
18 CURSOR c_uss IS
19 SELECT uss.closed_ind
20 FROM IGS_EN_UNIT_SET_STAT uss
21 WHERE uss.unit_set_status = p_unit_set_status;
22 BEGIN
23 OPEN c_uss;
24 FETCH c_uss INTO v_closed_ind;
25 IF (c_uss%NOTFOUND) THEN
26 CLOSE c_uss;
27 p_message_name := NULL;
28 RETURN TRUE;
29 END IF;
30 CLOSE c_uss;
31 IF (v_closed_ind = 'Y') THEN
32 p_message_name := 'IGS_PS_UNIT_SET_STATUS_CLOSED';
33 RETURN FALSE;
34 END IF;
35 p_message_name := NULL;
36 RETURN TRUE;
37 EXCEPTION
38 WHEN OTHERS THEN
39 IF (c_uss%ISOPEN) THEN
40 CLOSE c_uss;
41 END IF;
42 App_Exception.Raise_Exception;
43 END;
44 EXCEPTION
45 WHEN OTHERS THEN
46 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
47 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_uss_closed');
48 IGS_GE_MSG_STACK.ADD;
49 App_Exception.Raise_Exception;
50 END crsp_val_uss_closed;
51 --
52 -- Validate the IGS_PS_UNIT set category closed indicator.
53 FUNCTION crsp_val_usc_closed(
54 p_unit_set_cat IN VARCHAR2 ,
55 p_message_name OUT NOCOPY VARCHAR2 )
56 RETURN BOOLEAN AS
57 BEGIN --crsp_val_usc_closed
58 -- Validate the IGS_PS_UNIT set closed closed indicator
59 DECLARE
60 v_closed_ind IGS_EN_UNIT_SET_CAT.closed_ind%TYPE;
61 CURSOR c_usc IS
62 SELECT usc.closed_ind
63 FROM IGS_EN_UNIT_SET_CAT usc
64 WHERE usc.unit_set_cat = p_unit_set_cat;
65 BEGIN
66 --set default message number
67 p_message_name := NULL;
68 OPEN c_usc;
69 FETCH c_usc INTO v_closed_ind;
70 IF (c_usc%NOTFOUND) THEN
71 CLOSE c_usc;
72 RETURN TRUE;
73 END IF;
74 CLOSE c_usc;
75 IF (v_closed_ind = 'Y') THEN
76 p_message_name := 'IGS_PS_UNIT_SET_CAT_CLOSED';
77 RETURN FALSE;
78 END IF;
79 RETURN TRUE ;
80 EXCEPTION
81 WHEN OTHERS THEN
82 IF (c_usc%ISOPEN) THEN
83 CLOSE c_usc;
84 END IF;
85 App_Exception.Raise_Exception;
86 END;
87 EXCEPTION
88 WHEN OTHERS THEN
89 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
90 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_usc_closed');
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END crsp_val_usc_closed;
94 --
95 -- Validate version dates for IGS_PS_COURSE and IGS_PS_UNIT versions.
96 FUNCTION crsp_val_ver_dt(
97 p_start_dt IN DATE ,
98 p_end_dt IN DATE ,
99 p_expiry_dt IN DATE ,
100 p_message_name OUT NOCOPY VARCHAR2,
101 p_lgcy_validator IN BOOLEAN)
102 RETURN BOOLEAN AS
103 l_ret_status BOOLEAN :=TRUE;
104 BEGIN
105 IF (p_end_dt IS NOT NULL) AND (p_start_dt IS NOT NULL) THEN
106 IF (p_end_dt < p_start_dt) THEN
107 IF p_lgcy_validator THEN
108 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_VERENDDT_GE_VERSTARTDT',NULL,NULL,FALSE);
109 l_ret_status:=FALSE;
110 ELSE
111 p_message_name := 'IGS_PS_VERENDDT_GE_VERSTARTDT';
112 RETURN FALSE;
113 END IF;
114 END IF;
115 END IF;
116 IF (p_end_dt IS NOT NULL) AND (p_expiry_dt IS NOT NULL) THEN
117 IF (p_end_dt < p_expiry_dt) THEN
118 IF p_lgcy_validator THEN
119 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_VER_ENDDT_GE_VER_EXPDT',NULL,NULL,FALSE);
120 l_ret_status:=FALSE;
121 ELSE
122 p_message_name := 'IGS_PS_VER_ENDDT_GE_VER_EXPDT';
123 RETURN FALSE;
124 END IF;
125 END IF;
126 END IF;
127 IF (p_start_dt IS NOT NULL) AND (p_expiry_dt IS NOT NULL) THEN
128 IF (p_expiry_dt < p_start_dt) THEN
129 IF p_lgcy_validator THEN
130 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_VER_EXPDT_GE_VER_STDT',NULL,NULL,FALSE);
131 l_ret_status:=FALSE;
132 ELSE
133 p_message_name := 'IGS_PS_VER_EXPDT_GE_VER_STDT';
134 RETURN FALSE;
135 END IF;
136 END IF;
137 END IF;
138
139 IF p_lgcy_validator THEN
140 p_message_name := NULL;
141 RETURN l_ret_status;
142 ELSE
143 p_message_name := NULL;
144 RETURN TRUE;
145 END IF;
146 EXCEPTION
147 WHEN OTHERS THEN
148 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
149 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_ver_dt');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception;
152 END crsp_val_ver_dt;
153 --
154 -- Validate IGS_PS_UNIT set end date and IGS_PS_UNIT set status
155 FUNCTION crsp_val_us_end_sts(
156 p_end_dt IN DATE ,
157 p_unit_set_status IN VARCHAR2 ,
158 p_message_name OUT NOCOPY VARCHAR2 )
159 RETURN BOOLEAN AS
160 BEGIN -- crsp_val_us_end_sts
161 -- This module performs cross-field validation on the IGS_PS_UNIT set
162 -- version end date and the IGS_PS_UNIT version status.
163 -- - End date can only be set if the IGS_PS_UNIT set system status is INACTIVE
164 DECLARE
165 cst_inactive CONSTANT IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE := 'INACTIVE';
166 v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
167 CURSOR c_uss IS
168 SELECT uss.s_unit_set_status
169 FROM IGS_EN_UNIT_SET_STAT uss
170 WHERE uss.unit_set_status = p_unit_set_status;
171 BEGIN
172 -- 1. Select the IGS_EN_UNIT_SET_STAT.s_unit_set_status for
173 -- the given p_unit_set_status.
174 OPEN c_uss;
175 FETCH c_uss INTO v_s_unit_set_status;
176 CLOSE c_uss;
177 -- 2. Perform validation when the p_end_dt is set
178 IF (p_end_dt IS NOT NULL) THEN
179 IF (v_s_unit_set_status = cst_inactive) THEN
180 p_message_name := NULL;
181 RETURN TRUE;
182 ELSE
183 p_message_name := 'IGS_PS_STATUS_SET_INACTIVE';
184 RETURN FALSE;
185 END IF;
186 ELSE
187 -- 3. Perform validation when the p_end_dt is not set
188 IF (v_s_unit_set_status <> cst_inactive) THEN
189 p_message_name := NULL;
190 RETURN TRUE;
191 ELSE
192 p_message_name := 'IGS_PS_STATUS_NOTSET_INACTIVE';
193 RETURN FALSE;
194 END IF;
195 END IF;
196 EXCEPTION
197 WHEN OTHERS THEN
198 IF (c_uss%ISOPEN) THEN
199 CLOSE c_uss;
200 END IF;
201 App_Exception.Raise_Exception;
202 END;
203 EXCEPTION
204 WHEN OTHERS THEN
205 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
206 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_us_end_sts');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END crsp_val_us_end_sts;
210 --
211 -- Validate IGS_PS_UNIT set end date and status when active students exist
212 FUNCTION crsp_val_us_enr(
213 p_unit_set_cd IN VARCHAR2 ,
214 p_version_number IN NUMBER ,
215 p_message_name OUT NOCOPY VARCHAR2 )
216 RETURN BOOLEAN AS
217 BEGIN -- crsp_val_us_enr
218 -- This module validates end date/IGS_PS_UNIT set status of INACTIVE cannot be set
219 -- when there are active students within an offering of the IGS_PS_UNIT set.
220 DECLARE
221 cst_enrolled CONSTANT
222 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'ENROLLED';
223 cst_intermit CONSTANT
224 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INTERMIT';
225 cst_inactive CONSTANT
226 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INACTIVE';
227 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
228 CURSOR c_sca_susa IS
229 SELECT sca.course_attempt_status
230 FROM IGS_EN_STDNT_PS_ATT sca,
231 IGS_AS_SU_SETATMPT susa
232 WHERE sca.person_id = susa.person_id AND
233 sca.course_cd = susa.course_cd AND
234 sca.course_attempt_status IN (
235 cst_enrolled,
236 cst_intermit,
237 cst_inactive) AND
238 susa.unit_set_cd = p_unit_set_cd AND
239 susa.us_version_number = p_version_number AND
240 susa.student_confirmed_ind = 'Y';
241 BEGIN
242 OPEN c_sca_susa;
243 FETCH c_sca_susa INTO v_course_attempt_status;
244 IF (c_sca_susa%FOUND) THEN
245 CLOSE c_sca_susa;
246 p_message_name := 'IGS_PS_ENDDT_CANNOT_BESET';
247 RETURN FALSE;
248 END IF;
249 CLOSE c_sca_susa;
250 p_message_name := NULL;
251 RETURN TRUE;
252 EXCEPTION
253 WHEN OTHERS THEN
254 IF (c_sca_susa%ISOPEN) THEN
255 CLOSE c_sca_susa;
256 END IF;
257 App_Exception.Raise_Exception;
258 END;
259 EXCEPTION
260 WHEN OTHERS THEN
261 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
262 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_us_enr');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END crsp_val_us_enr;
266 --
267 -- Validate IGS_PS_UNIT set status changes
268 FUNCTION crsp_val_us_status(
269 p_old_unit_set_status IN VARCHAR2 ,
270 p_new_unit_set_status IN VARCHAR2 ,
271 p_message_name OUT NOCOPY VARCHAR2 )
272 RETURN BOOLEAN AS
273 BEGIN -- crsp_val_us_status
274 -- This module validates the IGS_EN_UNIT_SET.IGS_EN_UNIT_SET_STAT. It is fired at
275 -- item level. The checks are:
276 -- IGS_PS_UNIT_STAT cannot be set back to a system status of 'PLANNED' once
277 -- it is 'ACTIVE' or 'INACTIVE'.
278 DECLARE
279 cst_planned CONSTANT IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE := 'PLANNED';
280 v_new_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
281 v_old_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
282 CURSOR c_uss (
283 cp_unit_set_status IGS_EN_UNIT_SET_STAT.unit_set_status%TYPE) IS
284 SELECT uss.s_unit_set_status
285 FROM IGS_EN_UNIT_SET_STAT uss
286 WHERE uss.unit_set_status = cp_unit_set_status;
287 BEGIN
288 -- Validate the system status is not being altered to PLANNED from
289 -- ACTIVE or INACTIVE.
290 IF (p_old_unit_set_status IS NOT NULL AND
291 p_new_unit_set_status <> p_old_unit_set_status) THEN
292 -- Fetch new system status
293 OPEN c_uss(
294 p_new_unit_set_status);
295 FETCH c_uss INTO v_new_unit_set_status;
296 CLOSE c_uss;
297 -- Fetch old system status
298 OPEN c_uss(
299 p_old_unit_set_status);
300 FETCH c_uss INTO v_old_unit_set_status;
301 CLOSE c_uss;
302 IF (v_new_unit_set_status <> v_old_unit_set_status AND
303 v_new_unit_set_status = cst_planned) THEN
304 p_message_name := 'IGS_PS_UNIT_SET_STATUS_NOTALT';
305 RETURN FALSE;
306 END IF;
307 END IF;
308 p_message_name := NULL;
309 RETURN TRUE;
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF (c_uss%ISOPEN) THEN
313 CLOSE c_uss;
314 END IF;
315 App_Exception.Raise_Exception;
316 END;
317 EXCEPTION
318 WHEN OTHERS THEN
319 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
320 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_us_status');
321 IGS_GE_MSG_STACK.ADD;
322 App_Exception.Raise_Exception;
323 END crsp_val_us_status;
324 --
325 -- Validate IGS_PS_UNIT set expiry date and IGS_PS_UNIT set status
326 FUNCTION crsp_val_us_exp_sts(
327 p_unit_set_cd IN VARCHAR2 ,
328 p_version_number IN NUMBER ,
329 p_unit_set_status IN VARCHAR2 ,
330 p_expiry_dt IN DATE ,
331 p_message_name OUT NOCOPY VARCHAR2 )
332 RETURN BOOLEAN AS
333 BEGIN -- crsp_val_us_exp_sts
334 -- This module validates the cross-record validation dependent on the
335 -- IGS_EN_UNIT_SET.expiry_dt and IGS_EN_UNIT_SET.IGS_PS_UNIT_STAT columns.
336 -- . There can only be one version of a IGS_PS_UNIT set which has a system status
337 -- of 'ACTIVE' and the expiry date not set.
338 DECLARE
339 cst_active CONSTANT VARCHAR2(10) :='ACTIVE';
340 v_dummy VARCHAR2(1);
341 CURSOR c_uss IS
342 SELECT 'x'
343 FROM IGS_EN_UNIT_SET_STAT uss
344 WHERE uss.unit_set_status = unit_set_status AND
345 uss.s_unit_set_status = cst_active;
346 CURSOR c_us_uss IS
347 SELECT 'X'
348 FROM IGS_EN_UNIT_SET us,
349 IGS_EN_UNIT_SET_STAT uss
350 WHERE us.unit_set_cd = p_unit_set_cd AND
351 us.version_number <> p_version_number AND
352 us.expiry_dt IS NULL AND
353 us.unit_set_status = uss.unit_set_status AND
354 uss.s_unit_set_status = cst_active;
355 BEGIN
356 --Set the default message number
357 p_message_name := NULL;
361 OPEN c_uss;
358 -- Check parameters passed in. If the IGS_PS_UNIT set system status (fetch
359 -- s_unit_set_status from IGS_EN_UNIT_SET_STAT table) is ACTIVE and the expiry
360 -- date not set.
362 FETCH c_uss INTO v_dummy;
363 IF c_uss%FOUND THEN
364 CLOSE c_uss;
365 IF p_expiry_dt IS NULL THEN
366 -- Check that no other versions of the IGS_PS_UNIT set exist that have a system
367 -- status of ACTIVE and p_expiry_dt not set
368 OPEN c_us_uss;
369 FETCH c_us_uss INTO v_dummy;
370 IF c_us_uss%FOUND THEN
371 CLOSE c_us_uss;
372 p_message_name := 'IGS_PS_ANOTHERVER_UNITSET_EXI';
373 RETURN FALSE;
374 END IF;
375 CLOSE c_us_uss;
376 END IF;
377 ELSE
378 CLOSE c_uss;
379 END IF;
380 RETURN TRUE;
381 EXCEPTION
382 WHEN OTHERS THEN
383 IF c_uss%ISOPEN THEN
384 CLOSE c_uss;
385 END IF;
386 IF c_us_uss%ISOPEN THEN
387 CLOSE c_us_uss;
388 END IF;
389 App_Exception.Raise_Exception;
390 END;
391 EXCEPTION
392 WHEN OTHERS THEN
393 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
394 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_us_exp_sts');
395 IGS_GE_MSG_STACK.ADD;
396 App_Exception.Raise_Exception;
397 END crsp_val_us_exp_sts;
398 --
399 -- Validate IGS_PS_UNIT set status for ins/upd/del of IGS_PS_UNIT set details
400 FUNCTION crsp_val_iud_us_dtl2(
401 p_old_unit_set_status IN VARCHAR2 ,
402 p_new_unit_set_status IN VARCHAR2 ,
403 p_message_name OUT NOCOPY VARCHAR2 )
404 RETURN BOOLEAN AS
405 BEGIN -- crsp_val_iud_us_dtl2
406 -- This module validates whether or not inserts and updates can be made to
407 -- IGS_EN_UNIT_SET details
408 -- on the IGS_PS_UNIT set record. It is fired at record level (hence could not be
409 -- incorporated
410 -- in the CRSP_VAL_US_STATUS validation).
411 DECLARE
412 v_new_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
413 v_old_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
414 -- Fetch new system status
415 CURSOR c_new_uss IS
416 SELECT uss.s_unit_set_status
417 FROM IGS_EN_UNIT_SET_STAT uss
418 WHERE uss.unit_set_status = p_new_unit_set_status;
419 -- Fetch old system status
420 CURSOR c_old_uss IS
421 SELECT uss.s_unit_set_status
422 FROM IGS_EN_UNIT_SET_STAT uss
423 WHERE uss.unit_set_status = p_old_unit_set_status;
424 BEGIN
425 -- Set the default message number
426 p_message_name := NULL;
427 OPEN c_new_uss;
428 FETCH c_new_uss INTO v_new_unit_set_status;
429 OPEN c_old_uss;
430 FETCH c_old_uss INTO v_old_unit_set_status;
431 -- Validate the system status is not being altered when INACTIVE
432 -- unless system status is also being changed (to ACTIVE):
433 IF (c_new_uss%FOUND AND c_old_uss%FOUND) THEN
434 IF v_old_unit_set_status = 'INACTIVE' THEN
435 IF v_new_unit_set_status <> 'ACTIVE' THEN
436 CLOSE c_new_uss;
437 CLOSE c_old_uss;
438 p_message_name := 'IGS_PS_UNIT_SET_INACTIVE';
439 RETURN FALSE;
440 END IF;
441 END IF;
442 END IF;
443 CLOSE c_new_uss;
444 CLOSE c_old_uss;
445 RETURN TRUE;
446 EXCEPTION
447 WHEN OTHERS THEN
448 IF (c_new_uss%NOTFOUND) THEN
449 CLOSE c_new_uss;
450 END IF;
451 IF (c_old_uss%NOTFOUND) THEN
452 CLOSE c_old_uss;
453 END IF;
454 App_Exception.Raise_Exception;
455 END;
456 EXCEPTION
457 WHEN OTHERS THEN
458 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
459 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_iud_us_dtl2');
460 IGS_GE_MSG_STACK.ADD;
461 App_Exception.Raise_Exception;
462 END crsp_val_iud_us_dtl2;
463 --
464 --Validate IGS_PS_UNIT set category changes
465 FUNCTION crsp_val_us_category(
466 p_unit_set_status IN VARCHAR2 ,
467 p_old_unit_set_cat IN VARCHAR2 ,
468 p_new_unit_set_cat IN VARCHAR2 ,
469 p_message_name OUT NOCOPY VARCHAR2 )
470 RETURN BOOLEAN AS
471 /* change history:
472 who when what*/
473 BEGIN -- crsp_val_us_category
474 -- This module provides a warning if the IGS_PS_UNIT set is active
475 -- when the IGS_PS_UNIT set category is being changed.
476 DECLARE
477 v_s_unit_set_status IGS_EN_UNIT_SET_STAT.s_unit_set_status%TYPE;
478 CURSOR c_uss IS
479 SELECT uss.s_unit_set_status
480 FROM IGS_EN_UNIT_SET_STAT uss
481 WHERE uss.unit_set_status = p_unit_set_status;
482 BEGIN
483 -- Set the default message number
484 p_message_name := NULL;
485 IF p_old_unit_set_cat <> p_new_unit_set_cat THEN
486 -- check whether IGS_EN_UNIT_SET_STAT is ACTIVE
487 OPEN c_uss;
488 FETCH c_uss INTO v_s_unit_set_status;
489 --if the unit set status is not planned and unit set category is getting changed, throw error message.bug#2121770.
490 IF (c_uss%FOUND) THEN
491 IF v_s_unit_set_status <> 'PLANNED' THEN
492 CLOSE c_uss;
493 p_message_name := 'IGS_PS_UNIT_SET_ACTIVE';
494 RETURN FALSE;
495 END IF;
496 END IF;
497 CLOSE c_uss;
498 END IF;
499 RETURN TRUE;
500 EXCEPTION
501 WHEN OTHERS THEN
502 IF (c_uss%ISOPEN) THEN
503 CLOSE c_uss;
504 END IF;
505 App_Exception.Raise_Exception;
506 END;
507 EXCEPTION
508 WHEN OTHERS THEN
509 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
510 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_US.crsp_val_us_category');
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception;
513 END crsp_val_us_category ;
514 --
515 END IGS_PS_VAL_US;
[Home] [Help]