1 PACKAGE BODY IGS_GR_VAL_GAC AS
2 /* $Header: IGSGR08B.pls 115.6 2002/11/29 00:40:56 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function GRDP_VAL_ACUSG_CLOSE removed
7 --smadathi 27-AUG-2001 Bug No. 1956374 .The function grdp_val_awc_closed removed
8 -------------------------------------------------------------------------------------------
9 -- Validate graduand award ceremony insert.
10 FUNCTION grdp_val_gac_insert(
11 p_person_id IGS_GR_AWD_CRMN.person_id%TYPE ,
12 p_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 BEGIN -- grdp_val_gac_insert
16 -- Description: This routine validates inserting a graduand_award_ceremony
17 -- record based on the graduand details.
18 DECLARE
19 v_gr_rec IGS_GR_GRADUAND.s_graduand_type%TYPE;
20 cst_unknown CONSTANT VARCHAR2(10) := 'UNKNOWN';
21 cst_attending CONSTANT VARCHAR2(10) := 'ATTENDING';
22 cst_inabsentia CONSTANT VARCHAR2(10) := 'INABSENTIA';
23 CURSOR c_gr IS
24 SELECT 'X'
25 FROM IGS_GR_GRADUAND gr
26 WHERE gr.person_id = p_person_id AND
27 gr.create_dt = p_create_dt AND
28 gr.s_graduand_type NOT IN (cst_unknown,
29 cst_attending,
30 cst_inabsentia);
31 BEGIN
32 p_message_name := NULL;
33 IF p_person_id IS NULL OR
34 p_create_dt IS NULL THEN
35 RETURN TRUE;
36 END IF;
37 OPEN c_gr;
38 FETCH c_gr INTO v_gr_rec;
39 IF (c_gr%FOUND) THEN
40 CLOSE c_gr;
41 p_message_name := 'IGS_GR_TYPE_ATT_INABS_UNKNOWN';
42 RETURN FALSE;
43 END IF;
44 CLOSE c_gr;
45 RETURN TRUE;
46 END;
47 EXCEPTION
48 WHEN OTHERS THEN
49 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
50 IGS_GE_MSG_STACK.ADD;
51 App_Exception.Raise_Exception;
52 END grdp_val_gac_insert;
53 --
54 -- Validate inserting or updating a graduand award ceremony.
55 FUNCTION grdp_val_gac_iu(
56 p_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE ,
57 p_grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE ,
58 p_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE ,
59 p_message_name OUT NOCOPY VARCHAR2 )
60 RETURN BOOLEAN AS
61 BEGIN -- grdp_val_gac_iu
62 -- Validate that the insert or update of a graduand_award_ceremony record
63 -- does not fall outside the graduation_ceremony update window.
64 DECLARE
65 CURSOR c_crd IS
66 SELECT crd.start_dt_alias,
67 crd.start_dai_sequence_number,
68 crd.end_dt_alias,
69 crd.end_dai_sequence_number
70 FROM IGS_GR_CRMN_ROUND crd
71 WHERE crd.grd_cal_type = p_grd_cal_type AND
72 crd.grd_ci_sequence_number = p_grd_ci_sequence_number;
73 v_crd_rec c_crd%ROWTYPE;
74 CURSOR c_gc IS
75 SELECT gc.ceremony_dt_alias,
76 gc.ceremony_dai_sequence_number,
77 gc.closing_dt_alias,
78 gc.closing_dai_sequence_number
79 FROM IGS_GR_CRMN gc
80 WHERE gc.grd_cal_type = p_grd_cal_type AND
81 gc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
82 gc.ceremony_number = p_ceremony_number;
83 v_gc_rec c_gc%ROWTYPE;
84 v_start_dt DATE DEFAULT NULL;
85 v_end_dt DATE DEFAULT NULL;
86 v_ceremony_dt DATE DEFAULT NULL;
87 v_closing_dt DATE DEFAULT NULL;
88 BEGIN
89 -- Set the default message number
90 p_message_name := NULL;
91 IF p_grd_cal_type IS NULL OR
92 p_grd_ci_sequence_number IS NULL OR
93 p_ceremony_number IS NULL THEN
94 RETURN TRUE;
95 END IF;
96 OPEN c_crd;
97 FETCH c_crd INTO v_crd_rec;
98 IF c_crd%FOUND THEN
99 CLOSE c_crd;
100 v_start_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
101 v_crd_rec.start_dt_alias,
102 v_crd_rec.start_dai_sequence_number,
103 p_grd_cal_type,
104 p_grd_ci_sequence_number);
105 v_end_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
106 v_crd_rec.end_dt_alias,
107 v_crd_rec.end_dai_sequence_number,
108 p_grd_cal_type,
109 p_grd_ci_sequence_number);
110 IF v_start_dt IS NULL OR
111 v_end_dt IS NULL THEN
112 p_message_name := NULL;
113 RETURN TRUE;
114 END IF;
115 IF TRUNC(SYSDATE) < TRUNC(v_start_dt) OR
116 TRUNC(SYSDATE) > TRUNC(v_end_dt) THEN
117 p_message_name := 'IGS_GR_INVALID_PROC_PERIOD';
118 RETURN FALSE;
119 END IF;
120 ELSE
121 CLOSE c_crd;
122 END IF;
123 OPEN c_gc;
124 FETCH c_gc INTO v_gc_rec;
125 IF c_gc %FOUND THEN
126 CLOSE c_gc;
127 v_ceremony_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
128 v_gc_rec.ceremony_dt_alias,
129 v_gc_rec.ceremony_dai_sequence_number,
130 p_grd_cal_type,
131 p_grd_ci_sequence_number);
132 v_closing_dt := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
133 v_gc_rec.closing_dt_alias,
134 v_gc_rec.closing_dai_sequence_number,
135 p_grd_cal_type,
136 p_grd_ci_sequence_number);
137 IF v_ceremony_dt IS NULL OR
138 v_closing_dt IS NULL THEN
139 p_message_name := NULL;
140 RETURN TRUE;
141 END IF;
142 IF TRUNC(SYSDATE) > TRUNC(v_ceremony_dt) THEN
143 p_message_name := 'IGS_GR_INV_DT_GRAD_CERM';
144 RETURN TRUE;
145 END IF;
146 IF TRUNC(SYSDATE) > TRUNC(v_closing_dt) THEN
147 p_message_name := 'IGS_GR_CLOSING_DT_REACHED';
148 RETURN TRUE;
149 END IF;
150 ELSE
151 CLOSE c_gc;
152 END IF;
153 -- Return the default value
154 RETURN TRUE;
155 EXCEPTION
156 WHEN OTHERS THEN
157 IF c_crd %ISOPEN THEN
158 CLOSE c_crd;
159 END IF;
160 IF c_gc %ISOPEN THEN
161 CLOSE c_gc;
162 END IF;
163 RAISE;
164 END;
165 EXCEPTION
166 WHEN OTHERS THEN
167 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END grdp_val_gac_iu;
171 --
172 -- Validate Graduand Award Ceremony required details have been specified.
173 FUNCTION grdp_val_gac_rqrd(
174 p_award_course_cd IGS_GR_AWD_CRMN.award_course_cd%TYPE ,
175 p_award_crs_version_number IGS_GR_AWD_CRMN.award_crs_version_number%TYPE ,
176 p_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE ,
177 p_us_group_number IGS_GR_AWD_CRMN.us_group_number%TYPE ,
178 p_academic_dress_rqrd_ind VARCHAR2 DEFAULT 'N',
179 p_academic_gown_size VARCHAR2 ,
180 p_academic_hat_size VARCHAR2 ,
181 p_message_name OUT NOCOPY VARCHAR2 )
182 RETURN BOOLEAN AS
183 BEGIN -- grdp_val_gac_rqrd
184 -- Validate that the graduand_award_ceremony record required details;
185 -- us_group_number can only be specified when a course award
186 -- is being conferred.
187 DECLARE
188 BEGIN
189 -- Set the default message number
190 p_message_name := NULL;
191 IF p_award_cd IS NULL OR
192 p_academic_dress_rqrd_ind IS NULL then
193 RETURN TRUE;
194 END IF;
195 IF p_us_group_number IS NOT NULL THEN
196 IF p_award_course_cd IS NULL AND
197 p_award_crs_version_number IS NULL THEN
198 p_message_name := 'IGS_GR_UNT_GRP_CANNNOT_BE_SET';
199 RETURN FALSE;
200 END IF;
201 END IF;
202 IF p_academic_dress_rqrd_ind = 'N' THEN
203 IF p_academic_gown_size IS NOT NULL OR
204 p_academic_hat_size IS NOT NULL THEN
205 p_message_name := 'IGS_GR_SET_DRESS_INDICATOR';
206 RETURN FALSE;
207 END IF;
208 END IF;
209 -- Return the default value
210 RETURN TRUE;
211 EXCEPTION
212 WHEN OTHERS THEN
213 RAISE;
214 END;
215 EXCEPTION
216 WHEN OTHERS THEN
217 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
218 IGS_GE_MSG_STACK.ADD;
219 App_Exception.Raise_Exception;
220 END grdp_val_gac_rqrd;
221 --
222 -- Validate graduand seat number is unique for the person.
223 FUNCTION grdp_val_gac_seat(
224 p_person_id IGS_GR_AWD_CRMN.person_id%TYPE ,
225 p_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE ,
226 p_grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE ,
227 p_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE ,
228 p_graduand_seat_number IGS_GR_AWD_CRMN.graduand_seat_number%TYPE ,
229 p_message_name OUT NOCOPY VARCHAR2 )
230 RETURN BOOLEAN AS
231 BEGIN -- grdp_val_gac_seat
232 -- This routine validates the allocation of seats to graduands. It checks
233 -- that the same seat isn't allocated to more than one graduand at the
234 -- same graduation ceromony.
235 DECLARE
236 v_gac_found VARCHAR2(1);
237 CURSOR c_gac IS
238 SELECT 'x'
239 FROM IGS_GR_AWD_CRMN gac
240 WHERE gac.grd_cal_type = p_grd_cal_type AND
241 gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
242 gac.ceremony_number = p_ceremony_number AND
243 gac.person_id <> p_person_id AND
244 gac.graduand_seat_number = p_graduand_seat_number;
245 BEGIN
246 -- Initialise p_message_name.
247 p_message_name := NULL;
248 -- Check parameters.
249 IF p_person_id IS NULL OR
250 p_grd_cal_type IS NULL OR
251 p_grd_ci_sequence_number IS NULL OR
252 p_ceremony_number IS NULL OR
253 p_graduand_seat_number IS NULL THEN
254 RETURN TRUE;
255 END IF;
256 -- Check seat number is not being used by another graduand.
257 OPEN c_gac;
258 FETCH c_gac INTO v_gac_found;
259 IF c_gac%FOUND THEN
260 CLOSE c_gac;
261 p_message_name := 'IGS_GR_SEAT_ALREADY_ALLOCATED';
262 RETURN FALSE;
263 END IF;
264 CLOSE c_gac;
265 -- Return no error.
266 RETURN TRUE;
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF c_gac%ISOPEN THEN
270 CLOSE c_gac;
271 END IF;
272 RAISE;
273 END;
274 EXCEPTION
275 WHEN OTHERS THEN
276 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END grdp_val_gac_seat;
280 --
281 -- Validate Graduand Student Unit Set Attempts.
282 FUNCTION grdp_val_gac_susa(
283 p_person_id IGS_GR_AWD_CRMN.person_id%TYPE ,
284 p_create_dt IGS_GR_AWD_CRMN.create_dt%TYPE ,
285 p_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE ,
286 p_grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE ,
287 p_course_cd IGS_PS_COURSE.course_cd%TYPE ,
288 p_graduand_status VARCHAR2 ,
289 p_ceremony_number IGS_GR_AWD_CRMN.ceremony_number%TYPE ,
290 p_award_course_cd IGS_GR_AWD_CRMN.award_course_cd%TYPE ,
291 p_award_crs_version_number IGS_GR_AWD_CRMN.award_crs_version_number%TYPE ,
292 p_award_cd IGS_GR_AWD_CRMN.award_cd%TYPE ,
293 p_us_group_number IGS_GR_AWD_CRMN.us_group_number%TYPE ,
294 p_message_name OUT NOCOPY VARCHAR2 )
295 RETURN BOOLEAN AS
296 BEGIN -- grdp_val_gac_susa
297 -- This routine validates the award ceremony unit sets belonging to a unit
298 -- set group match with student unit set attempts belonging to the graduand
299 -- under the course of the award being conferred.
300 DECLARE
301 cst_eligible CONSTANT VARCHAR2(10) := 'ELIGIBLE';
302 cst_graduated CONSTANT VARCHAR2(10) := 'GRADUATED';
303 cst_surrender CONSTANT VARCHAR2(10) := 'SURRENDER';
304 v_incomplete_unit_sets BOOLEAN DEFAULT FALSE;
305 v_rqrmnts_complete_ind IGS_AS_SU_SETATMPT.rqrmnts_complete_ind%TYPE;
306 v_course_cd IGS_GR_GRADUAND.course_cd%TYPE;
307 v_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE;
308 v_s_graduand_status IGS_GR_STAT.s_graduand_status%TYPE;
309 CURSOR c_gr IS
310 SELECT gr.course_cd,
311 gr.graduand_status
312 FROM IGS_GR_GRADUAND gr
313 WHERE gr.person_id = p_person_id AND
314 gr.create_dt = p_create_dt;
315 CURSOR c_acus IS
316 SELECT acus.unit_set_cd,
317 acus.us_version_number
318 FROM IGS_GR_AWD_CRM_UT_ST acus
319 WHERE acus.grd_cal_type = p_grd_cal_type AND
320 acus.grd_ci_sequence_number = p_grd_ci_sequence_number AND
321 acus.ceremony_number = p_ceremony_number AND
322 acus.award_course_cd = p_award_course_cd AND
323 acus.award_crs_version_number = p_award_crs_version_number AND
324 acus.award_cd = p_award_cd AND
325 acus.us_group_number = p_us_group_number;
326 CURSOR c_susa (
327 cp_course_cd IGS_GR_GRADUAND.course_cd%TYPE,
328 cp_unit_set_cd IGS_GR_AWD_CRM_UT_ST.unit_set_cd%TYPE,
329 cp_us_version_number IGS_GR_AWD_CRM_UT_ST.us_version_number%TYPE)
330 IS
331 SELECT susa.rqrmnts_complete_ind
332 FROM IGS_AS_SU_SETATMPT susa
333 WHERE susa.person_id = p_person_id AND
334 susa.course_cd = cp_course_cd AND
335 susa.unit_set_cd = cp_unit_set_cd AND
336 susa.us_version_number = cp_us_version_number AND
337 susa.student_confirmed_ind = 'Y' AND
338 susa.primary_set_ind = 'Y' AND
339 susa.end_dt IS NULL;
340 CURSOR c_gst(
341 cp_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE)
342 IS
343 SELECT gst.s_graduand_status
344 FROM IGS_GR_STAT gst
345 WHERE gst.graduand_status = cp_graduand_status;
346 BEGIN
347 -- 1. Check parameters :
348 IF p_person_id IS NULL OR
349 p_grd_cal_type IS NULL OR
350 p_grd_ci_sequence_number IS NULL OR
351 p_ceremony_number IS NULL OR
352 p_award_course_cd IS NULL OR
353 p_award_crs_version_number IS NULL OR
354 p_award_cd IS NULL OR
355 p_us_group_number IS NULL THEN
356 p_message_name := NULL;
357 RETURN TRUE;
358 END IF;
359 -- 2. Get the required graduand details
360 IF p_course_cd IS NULL OR p_graduand_status IS NULL THEN
361 OPEN c_gr;
362 FETCH c_gr INTO v_course_cd,
363 v_graduand_status;
364 CLOSE c_gr;
365 ELSE
366 v_course_cd := p_course_cd;
367 v_graduand_status := p_graduand_status;
368 END IF;
369 -- 3. Match award ceremony unit sets with student unit set attempts
370 FOR v_acus IN c_acus LOOP
371 OPEN c_susa(
372 v_course_cd,
373 v_acus.unit_set_cd,
374 v_acus.us_version_number);
375 FETCH c_susa INTO v_rqrmnts_complete_ind;
376 IF c_susa%NOTFOUND THEN
377 CLOSE c_susa;
378 p_message_name := 'IGS_GR_NOT_ATTEMPTED_ALL_UNIT';
379 RETURN FALSE;
380 END IF;
381 CLOSE c_susa;
382 IF v_rqrmnts_complete_ind = 'N' THEN
383 v_incomplete_unit_sets := TRUE;
384 END IF;
385 END LOOP;
386 IF v_incomplete_unit_sets = TRUE THEN
387 OPEN c_gst(
388 v_graduand_status);
389 FETCH c_gst INTO v_s_graduand_status;
390 CLOSE c_gst;
391 IF v_s_graduand_status = cst_eligible OR
392 v_s_graduand_status = cst_graduated OR
393 v_s_graduand_status = cst_surrender THEN
394 p_message_name := 'IGS_GR_REQUIR_NOT_COMPLETED';
395 RETURN FALSE;
396 ELSE
397 p_message_name := 'IGS_GR_INCOMPLETE_REQUIRMENTS';
398 RETURN TRUE;
399 END IF;
400 END IF;
401 -- 4. Return no error:
402 p_message_name := NULL;
403 RETURN TRUE;
404 EXCEPTION
405 WHEN OTHERS THEN
406 IF (c_gr%ISOPEN) THEN
407 CLOSE c_gr;
408 END IF;
409 IF (c_acus%ISOPEN) THEN
410 CLOSE c_acus;
411 END IF;
412 IF (c_susa%ISOPEN) THEN
413 CLOSE c_susa;
414 END IF;
415 IF (c_gst%ISOPEN) THEN
416 CLOSE c_gst;
417 END IF;
418 RAISE;
419 END;
420 EXCEPTION
421 WHEN OTHERS THEN
422 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
423 IGS_GE_MSG_STACK.ADD;
424 App_Exception.Raise_Exception;
425 END grdp_val_gac_susa;
426 --
427 -- Validate Graduand Award Ceremony graduation calendar instance.
428 FUNCTION grdp_val_gac_grd_ci(
429 p_grd_cal_type IGS_GR_AWD_CRMN.grd_cal_type%TYPE ,
430 p_grd_ci_sequence_number IGS_GR_AWD_CRMN.grd_ci_sequence_number%TYPE ,
431 p_message_name OUT NOCOPY VARCHAR2 )
432 RETURN BOOLEAN AS
433 BEGIN -- grdp_val_gac_grd_ci
434 -- Validate that the graduand_award_ceremony is linked
435 -- to a ceremony_round that has an ACTIVE calendar instance.
436 DECLARE
437 CURSOR c_ci_cs IS
438 SELECT 'x'
439 FROM IGS_CA_INST ci,
440 IGS_CA_STAT cs
441 WHERE ci.cal_type = p_grd_cal_type AND
442 ci.sequence_number = p_grd_ci_sequence_number AND
443 cs.cal_status = ci.cal_status AND
444 cs.s_cal_status = 'ACTIVE';
445 v_ci_cs_exists VARCHAR2(1);
446 BEGIN
447 -- Set the default message number
448 p_message_name := NULL;
449 IF p_grd_cal_type IS NULL OR
450 p_grd_ci_sequence_number IS NULL THEN
451 RETURN TRUE;
452 END IF;
453 OPEN c_ci_cs;
454 FETCH c_ci_cs INTO v_ci_cs_exists;
455 IF c_ci_cs %NOTFOUND THEN
456 CLOSE c_ci_cs;
457 p_message_name :='IGS_GR_CERM_CAL_ACTIVE';
458 RETURN FALSE;
459 END IF;
460 CLOSE c_ci_cs;
461 -- Return the default value
462 RETURN TRUE;
463 EXCEPTION
464 WHEN OTHERS THEN
465 IF c_ci_cs %ISOPEN THEN
466 CLOSE c_ci_cs;
467 END IF;
468 RAISE;
469 END;
470 EXCEPTION
471 WHEN OTHERS THEN
472 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
473 IGS_GE_MSG_STACK.ADD;
474 App_Exception.Raise_Exception;
475 END grdp_val_gac_grd_ci;
476 --
477 -- Validate graduand award ceremony order in presentation is unique.
478 FUNCTION grdp_val_gac_order(
479 p_person_id IN NUMBER ,
480 p_grd_cal_type IN VARCHAR2 ,
481 p_grd_ci_sequence_number IN NUMBER ,
482 p_ceremony_number IN NUMBER ,
483 p_order_in_presentation IN NUMBER ,
484 p_message_name OUT NOCOPY VARCHAR2 )
485 RETURN BOOLEAN AS
486 BEGIN -- grdp_val_gac_order
487 -- Description: This routine validates the graduand award ceremony order
488 -- in presentation is unique.
489 DECLARE
490 v_dummy VARCHAR2(1);
491 CURSOR c_gac IS
492 SELECT 'X'
493 FROM IGS_GR_AWD_CRMN
494 WHERE grd_cal_type = p_grd_cal_type AND
495 grd_ci_sequence_number = p_grd_ci_sequence_number AND
496 ceremony_number = p_ceremony_number AND
497 person_id <> p_person_id AND
498 order_in_presentation = p_order_in_presentation;
499 BEGIN
500 p_message_name := NULL;
501 IF p_person_id IS NULL OR
502 p_grd_cal_type IS NULL OR
503 p_grd_ci_sequence_number IS NULL OR
504 p_ceremony_number IS NULL OR
505 p_order_in_presentation IS NULL THEN
506 RETURN TRUE;
507 END IF;
508 OPEN c_gac;
509 FETCH c_gac INTO v_dummy;
510 IF (c_gac%FOUND) THEN
511 CLOSE c_gac;
512 p_message_name := 'IGS_GR_PRES_ORDER_NOT_UNIQUE';
513 RETURN FALSE;
514 END IF;
515 CLOSE c_gac;
516 RETURN TRUE;
517 EXCEPTION
518 WHEN OTHERS THEN
519 IF (c_gac%ISOPEN) THEN
520 CLOSE c_gac;
521 END IF;
522 RAISE;
523 END;
524 EXCEPTION
525 WHEN OTHERS THEN
526 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
527 IGS_GE_MSG_STACK.ADD;
528 App_Exception.Raise_Exception;
529 END grdp_val_gac_order;
530
531 -- Validate if the award ceremony unit set group is closed
532 FUNCTION grdp_val_acusg_close(
533 p_grd_cal_type IGS_GR_AWD_CRM_US_GP.grd_cal_type%TYPE ,
534 p_grd_ci_sequence_number IGS_GR_AWD_CRM_US_GP.grd_ci_sequence_number%TYPE ,
535 p_ceremony_number IGS_GR_AWD_CRM_US_GP.ceremony_number%TYPE ,
536 p_award_course_cd IGS_GR_AWD_CRM_US_GP.award_course_cd%TYPE ,
537 p_award_crs_version_number IGS_GR_AWD_CRM_US_GP.award_crs_version_number%TYPE ,
538 p_award_cd IGS_GR_AWD_CRM_US_GP.award_cd%TYPE ,
539 p_us_group_number IGS_GR_AWD_CRM_US_GP.us_group_number%TYPE ,
540 p_message_name OUT NOCOPY VARCHAR2 )
541 RETURN BOOLEAN AS
542 BEGIN -- grdp_val_acusg_close
543 -- Description: Validate if the award ceremony unit set group is closed
544 DECLARE
545 v_acusg_rec IGS_GR_AWD_CRM_US_GP.closed_ind%TYPE;
546 CURSOR c_acusg IS
547 SELECT acusg.closed_ind
548 FROM IGS_GR_AWD_CRM_US_GP acusg
549 WHERE acusg.grd_cal_type = p_grd_cal_type and
550 acusg.grd_ci_sequence_number = p_grd_ci_sequence_number and
551 acusg.ceremony_number = p_ceremony_number and
552 acusg.award_course_cd = p_award_course_cd and
553 acusg.award_crs_version_number =p_award_crs_version_number and
554 acusg.award_cd = p_award_cd and
555 acusg.us_group_number = p_us_group_number and
556 acusg.closed_ind ='Y';
557 BEGIN
558 p_message_name := NULL;
559 IF p_grd_cal_type IS NULL OR
560 p_grd_ci_sequence_number IS NULL OR
561 p_ceremony_number IS NULL OR
562 p_award_course_cd IS NULL OR
563 p_award_crs_version_number IS NULL OR
564 p_award_cd IS NULL OR
565 p_us_group_number iS NULL THEN
566 RETURN TRUE;
567 END IF;
568 OPEN c_acusg;
569 FETCH c_acusg INTO v_acusg_rec;
570 IF (c_acusg%FOUND) THEN
571 CLOSE c_acusg;
572 p_message_name := 'IGS_GR_AWD_CERM_GRP_CLOSED';
573 RETURN FALSE;
574 END IF;
575 CLOSE c_acusg;
576 RETURN TRUE;
577 EXCEPTION
578 WHEN OTHERS THEN
579 IF (c_acusg%ISOPEN) THEN
580 CLOSE c_acusg;
581 END IF;
582 RAISE;
583 END;
584 EXCEPTION
585 WHEN OTHERS THEN
586 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
587 IGS_GE_MSG_STACK.ADD;
588 App_Exception.Raise_Exception;
589 END grdp_val_acusg_close;
590 --
591 -- Validate a measurement code is not closed.
592 FUNCTION GRDP_VAL_MSR_CLOSED(
593 p_measurement_cd IGS_GE_MEASUREMENT.measurement_cd%TYPE ,
594 p_message_name OUT NOCOPY VARCHAR2 )
595 RETURN BOOLEAN AS
596 BEGIN -- GRDP_VAL_MSR_CLOSED
597 -- Validate if the measurement is closed.
598 DECLARE
599 CURSOR c_MSR IS
600 SELECT 'X'
601 FROM IGS_GE_MEASUREMENT msr
602 WHERE msr.measurement_cd = p_measurement_cd AND
603 msr.closed_ind = 'Y';
604 v_msr_exists VARCHAR2(1);
605 BEGIN
606 -- Set the default message number
607 p_message_name := NULL;
608 OPEN c_msr ;
609 FETCH c_msr INTO v_msr_exists ;
610 IF c_msr %FOUND THEN
611 CLOSE c_msr ;
612 p_message_name := 'IGS_GR_MEASURMENT_CLOSED';
613 RETURN FALSE;
614 END IF;
615 CLOSE c_msr ;
616 -- Return the default value
617 RETURN TRUE;
618 EXCEPTION
619 WHEN OTHERS THEN
620 IF c_msr %ISOPEN THEN
621 CLOSE c_msr;
622 END IF;
623 RAISE;
624 END;
625 EXCEPTION
626 WHEN OTHERS THEN
627 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
628 IGS_GE_MSG_STACK.ADD;
629 App_Exception.Raise_Exception;
630 END GRDP_VAL_MSR_CLOSED;
631 END IGS_GR_VAL_GAC;
[Home] [Help]