1 PACKAGE BODY IGS_GR_VAL_GC AS
2 /* $Header: IGSGR02B.pls 115.4 2002/11/29 00:39:34 nsidana ship $ */
3 --
4 -- Validate the graduation ceremony date aliases
5 FUNCTION grdp_val_gc_dai(
6 p_grd_cal_type IN VARCHAR2 ,
7 p_grd_ci_sequence_number IN NUMBER ,
8 p_ceremony_dt_alias IN VARCHAR2 ,
9 p_ceremony_dai_sequence_number IN NUMBER ,
10 p_closing_dt_alias IN VARCHAR2 ,
11 p_closing_dai_sequence_number IN NUMBER ,
12 p_message_name OUT NOCOPY VARCHAR2 )
13 RETURN BOOLEAN AS
14
15 BEGIN -- grdp_val_gc_dai
16 -- Validate that closing_dt_alias and closing_dai_sequence_number relate to a
17 -- IGS_CA_DA_INST with an alias_val less than or equal to the alias_val for
18 -- the IGS_CA_DA_INST for the ceremony_dt_alias and
19 -- ceremony_dai_sequence_number.
20 DECLARE
21 v_gc_dai_found VARCHAR2(1);
22 CURSOR c_ceremony_daiv IS
23 SELECT 'X'
24 FROM IGS_CA_DA_INST_V daiv
25 WHERE daiv.dt_alias = p_ceremony_dt_alias AND
26 daiv.sequence_number = p_ceremony_dai_sequence_number AND
27 daiv.cal_type = p_grd_cal_type AND
28 daiv.ci_sequence_number = p_grd_ci_sequence_number AND
29 TRUNC(daiv.alias_val) < TRUNC(SYSDATE);
30 CURSOR c_closing_daiv IS
31 SELECT 'X'
32 FROM IGS_CA_DA_INST_V daiv
33 WHERE daiv.dt_alias = p_closing_dt_alias AND
34 daiv.sequence_number = p_closing_dai_sequence_number AND
35 daiv.cal_type = p_grd_cal_type AND
36 daiv.ci_sequence_number = p_grd_ci_sequence_number AND
37 TRUNC(daiv.alias_val) < TRUNC(SYSDATE);
38 CURSOR c_daiv IS
39 SELECT 'X'
40 FROM IGS_CA_DA_INST_V ceremony_daiv,
41 IGS_CA_DA_INST_V closing_daiv
42 WHERE ceremony_daiv.dt_alias = p_ceremony_dt_alias AND
43 ceremony_daiv.sequence_number = p_ceremony_dai_sequence_number AND
44 ceremony_daiv.cal_type = p_grd_cal_type AND
45 ceremony_daiv.ci_sequence_number = p_grd_ci_sequence_number AND
46 closing_daiv.dt_alias = p_closing_dt_alias AND
47 closing_daiv.sequence_number = p_closing_dai_sequence_number AND
48 closing_daiv.cal_type = p_grd_cal_type AND
49 closing_daiv.ci_sequence_number = p_grd_ci_sequence_number AND
50 closing_daiv.alias_val <= ceremony_daiv.alias_val;
51 BEGIN
52 p_message_name := NULL;
53 IF p_grd_cal_type IS NULL OR
54 p_grd_ci_sequence_number IS NULL THEN
55 RETURN TRUE;
56 END IF;
57 IF p_ceremony_dt_alias IS NOT NULL AND
58 p_ceremony_dai_sequence_number IS NOT NULL THEN
59 OPEN c_ceremony_daiv;
60 FETCH c_ceremony_daiv INTO v_gc_dai_found;
61 IF c_ceremony_daiv%FOUND THEN
62 CLOSE c_ceremony_daiv;
63 p_message_name := 'IGS_GR_GRAD_DT_CANNOT_LT_CUR';
64 RETURN FALSE;
65 END IF;
66 CLOSE c_ceremony_daiv;
67 END IF;
68 IF p_closing_dt_alias IS NOT NULL AND
69 p_closing_dai_sequence_number IS NOT NULL THEN
70 OPEN c_closing_daiv;
71 FETCH c_closing_daiv INTO v_gc_dai_found;
72 IF c_closing_daiv%FOUND THEN
73 CLOSE c_closing_daiv;
74 p_message_name := 'IGS_GR_CERCL_DT_CANNOT_LT_CUR';
75 RETURN FALSE;
76 END IF;
77 CLOSE c_closing_daiv;
78 END IF;
79 IF p_ceremony_dt_alias IS NULL OR
80 p_ceremony_dai_sequence_number IS NULL OR
81 p_closing_dt_alias IS NULL OR
82 p_closing_dai_sequence_number IS NULL THEN
83 RETURN TRUE;
84 END IF;
85 OPEN c_daiv;
86 FETCH c_daiv INTO v_gc_dai_found;
87 IF c_daiv%NOTFOUND THEN
88 CLOSE c_daiv;
89 p_message_name := 'IGS_GR_CER_CL_DT_LE_CERM_DT';
90 RETURN FALSE;
91 END IF;
92 CLOSE c_daiv;
93 RETURN TRUE;
94 EXCEPTION
95 WHEN OTHERS THEN
96 IF c_daiv%ISOPEN THEN
97 CLOSE c_daiv;
98 END IF;
99 RAISE;
100 END;
101 EXCEPTION
102 WHEN OTHERS THEN
103 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106
107 END grdp_val_gc_dai;
108 --
109 -- Validate the graduation ceremony can be updated
110 FUNCTION grdp_val_gc_upd(
111 p_grd_cal_type IN VARCHAR2 ,
112 p_grd_ci_sequence_number IN NUMBER ,
113 p_ceremony_number IN NUMBER ,
114 p_message_name OUT NOCOPY VARCHAR2 )
115 RETURN BOOLEAN AS
116
117 BEGIN -- grdp_val_gc_upd
118 -- Description: Check if any IGS_GR_AWD_CRMN records exist
119 -- for this IGS_GR_CRMN.
120 DECLARE
121 v_gac_exists VARCHAR2(1);
122 CURSOR c_gac IS
123 SELECT 'X'
124 FROM IGS_GR_AWD_CRMN gac
125 WHERE gac.grd_cal_type = p_grd_cal_type AND
126 gac.grd_ci_sequence_number = p_grd_ci_sequence_number AND
127 gac.ceremony_number = p_ceremony_number;
128 BEGIN
129 p_message_name := NULL;
130 IF p_grd_cal_type IS NULL OR
131 p_grd_ci_sequence_number IS NULL OR
132 p_ceremony_number IS NULL THEN
133 RETURN TRUE;
134 END IF;
135 OPEN c_gac;
136 FETCH c_gac INTO v_gac_exists;
137 IF (c_gac%FOUND) THEN
138 CLOSE c_gac;
139 p_message_name := 'IGS_GR_DATES_CANNOT_BE_CHANGE';
140 RETURN FALSE;
141 END IF;
142 CLOSE c_gac;
143 RETURN TRUE;
144 EXCEPTION
145 WHEN OTHERS THEN
146 IF (c_gac%ISOPEN) THEN
147 CLOSE c_gac;
148 END IF;
149 RAISE;
150 END;
151 EXCEPTION
152 WHEN OTHERS THEN
153 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END grdp_val_gc_upd;
157 --
158 -- Validate the ceremony round linked to the graduation ceremony
159 FUNCTION grdp_val_gc_crd(
160 p_grd_cal_type IN VARCHAR2 ,
161 p_grd_ci_sequence_number IN NUMBER ,
162 p_message_name OUT NOCOPY VARCHAR2 )
163 RETURN BOOLEAN AS
164
165 BEGIN -- grdp_val_gc_crd
166 DECLARE
167 v_crdp_exists VARCHAR2(1);
168 CURSOR c_crdp IS
169 SELECT 'x'
170 FROM IGS_GR_CRM_ROUND_PRD crdp
171 WHERE crdp.grd_cal_type = p_grd_cal_type AND
172 crdp.grd_ci_sequence_number = p_grd_ci_sequence_number;
173 BEGIN
174 -- Set the default message number
175 p_message_name := NULL;
176 --1. Check parameters :
177 IF p_grd_cal_type IS NULL OR
178 p_grd_ci_sequence_number IS NULL THEN
179 RETURN TRUE;
180 END IF;
181 --2. Return a warning if no IGS_GR_CRMN_ROUND_PRD records exist for the
182 --specified IGS_GR_CRMN_ROUND.
183 OPEN c_crdp;
184 FETCH c_crdp INTO v_crdp_exists;
185 IF c_crdp%NOTFOUND THEN
186 CLOSE c_crdp;
187 p_message_name := 'IGS_GR_NO_CERM_ROUND_PERIOD';
188 RETURN TRUE; -- Warning only
189 END IF;
190 CLOSE c_crdp;
191 -- Return the default value
192 RETURN TRUE;
193 EXCEPTION
194 WHEN OTHERS THEN
195 IF c_crdp%ISOPEN THEN
196 CLOSE c_crdp;
197 END IF;
198 RAISE;
199 END;
200 EXCEPTION
201 WHEN OTHERS THEN
202 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END grdp_val_gc_crd;
206 --
207 -- Validate the start and end time of the graduation ceremony
208 FUNCTION grdp_val_gc_times(
209 p_grd_cal_type IN VARCHAR2 ,
210 p_grd_ci_sequence_number IN NUMBER ,
211 p_ceremony_number IN NUMBER ,
212 p_venue_cd IN VARCHAR2 ,
213 p_ceremony_dt_alias IN VARCHAR2 ,
214 p_ceremony_dai_sequence_number IN NUMBER ,
215 p_ceremony_start_time IN DATE ,
216 p_ceremony_end_time IN DATE ,
217 p_message_name OUT NOCOPY VARCHAR2 )
218 RETURN BOOLEAN AS
219 BEGIN -- grdp_val_gc_times
220 -- Check if the ceremony_start_time is after the ceremony_end_time.
221 -- Check if the graduation_ceremony ceremony date(ceremony_dt_alias,
222 --ceremony_dai_sequence_number), venue_cd, ceremony_start_time and
223 --ceremony_end_time overlap with another graduation_ceremony.
224 DECLARE
225 v_gc_daiv_exists VARCHAR2(1);
226 CURSOR c_gc_daiv IS
227 SELECT 'x'
228 FROM IGS_GR_CRMN gc,
229 IGS_CA_DA_INST_V daiv1,
230 IGS_CA_DA_INST_V daiv2
231 WHERE gc.grd_cal_type = p_grd_cal_type AND
232 gc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
233 gc.ceremony_number <> p_ceremony_number AND
234 gc.venue_cd = p_venue_cd AND
235 gc.ceremony_dt_alias = daiv1.dt_alias AND
236 gc.ceremony_dai_sequence_number = daiv1.sequence_number AND
237 gc.grd_cal_type = daiv1.cal_type AND
238 gc.grd_ci_sequence_number = daiv1.ci_sequence_number AND
239 daiv2.dt_alias = p_ceremony_dt_alias AND
240 daiv2.sequence_number = p_ceremony_dai_sequence_number AND
241 daiv2.cal_type = p_grd_cal_type AND
242 daiv2.ci_sequence_number = p_grd_ci_sequence_number AND
243 TRUNC(daiv1.alias_val) = TRUNC(daiv2.alias_val) AND
244 ((TO_CHAR(p_ceremony_start_time, 'HH24:MI') >
245 TO_CHAR(gc.ceremony_start_time, 'HH24:MI') AND
246 TO_CHAR(p_ceremony_start_time, 'HH24:MI') <
247 TO_CHAR(gc.ceremony_end_time, 'HH24:MI')) OR
248 (TO_CHAR(p_ceremony_end_time, 'HH24:MI') >
249 TO_CHAR(gc.ceremony_start_time, 'HH24:MI') AND
250 TO_CHAR(p_ceremony_end_time, 'HH24:MI') <=
251 TO_CHAR(gc.ceremony_end_time, 'HH24:MI')) OR
252 (TO_CHAR(p_ceremony_start_time, 'HH24:MI') <=
253 TO_CHAR(gc.ceremony_start_time, 'HH24:MI') AND
254 TO_CHAR(p_ceremony_end_time, 'HH24:MI') >=
255 TO_CHAR(gc.ceremony_end_time, 'HH24:MI')));
256 BEGIN
257 -- Set the default message number
258 p_message_name := NULL;
259 --1. Check parameters :
260 IF p_grd_cal_type IS NULL OR
261 p_grd_ci_sequence_number IS NULL OR
262 p_ceremony_number IS NULL OR
263 p_venue_cd IS NULL OR
264 p_ceremony_dt_alias IS NULL OR
265 p_ceremony_dai_sequence_number IS NULL OR
266 p_ceremony_start_time IS NULL OR
267 p_ceremony_end_time IS NULL THEN
268 RETURN TRUE;
269 END IF;
270 --Check if ceremony_start_time is after the ceremony_end_time..
271 IF TO_CHAR(p_ceremony_start_time, 'HH24:MI') >
272 TO_CHAR(p_ceremony_end_time, 'HH24:MI') THEN
273 p_message_name := 'IGS_GR_CERM_TIME_LT_END_TIME';
274 RETURN FALSE;
275 END IF;
276 --Check if the start and end times overlap with another ceremony on the
277 --same day at the same venue.
278 OPEN c_gc_daiv;
279 FETCH c_gc_daiv INTO v_gc_daiv_exists;
280 IF c_gc_daiv%FOUND THEN
281 CLOSE c_gc_daiv;
282 p_message_name := 'IGS_GR_CERM_DT_TIME_OVERLAPS';
283 RETURN FALSE;
284 END IF;
285 CLOSE c_gc_daiv;
286 -- Return the default value
287 RETURN TRUE;
288 EXCEPTION
289 WHEN OTHERS THEN
290 IF c_gc_daiv%ISOPEN THEN
291 CLOSE c_gc_daiv;
292 END IF;
293 RAISE;
294 END;
295 EXCEPTION
296 WHEN OTHERS THEN
297 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
298 IGS_GE_MSG_STACK.ADD;
299 App_Exception.Raise_Exception;
300 END grdp_val_gc_times;
301 --
302 -- Validate the ins/upd/del to the graduation ceremony
303 FUNCTION grdp_val_gc_iud(
304 p_grd_cal_type IN VARCHAR2 ,
305 p_grd_ci_sequence_number IN NUMBER ,
306 p_ceremony_number IN NUMBER ,
307 p_message_name OUT NOCOPY VARCHAR2 )
308 RETURN BOOLEAN AS
309 BEGIN -- grdp_val_gc_iud
310 DECLARE
311 v_gc_daiv_exists VARCHAR2(1);
312 CURSOR c_gc_daiv IS
313 SELECT 'x'
314 FROM IGS_GR_CRMN gc,
315 IGS_CA_DA_INST_V daiv
316 WHERE gc.grd_cal_type = p_grd_cal_type AND
317 gc.grd_ci_sequence_number = p_grd_ci_sequence_number AND
318 gc.ceremony_number = p_ceremony_number AND
319 gc.ceremony_dt_alias = daiv.dt_alias AND
320 gc.ceremony_dai_sequence_number = daiv.sequence_number AND
321 gc.grd_cal_type = daiv.cal_type AND
322 gc.grd_ci_sequence_number = daiv.ci_sequence_number AND
323 TRUNC(daiv.alias_val) < TRUNC(SYSDATE);
324 BEGIN
325 -- Set the default message number
326 p_message_name := NULL;
327 --1. Check parameters :
328 IF p_grd_cal_type IS NULL OR
329 p_grd_ci_sequence_number IS NULL OR
330 p_ceremony_number IS NULL THEN
331 RETURN TRUE;
332 END IF;
333 --2. Return a warning if ceremony_round start_dt_alias has a value earlier
334 -- than the current date.
335 OPEN c_gc_daiv;
336 FETCH c_gc_daiv INTO v_gc_daiv_exists;
337 IF c_gc_daiv%FOUND THEN
338 CLOSE c_gc_daiv;
339 p_message_name := 'IGS_GR_CERMONY_DT_EXPIRED';
340 RETURN FALSE;
341 END IF;
342 CLOSE c_gc_daiv;
343 -- Return the default value
344 RETURN TRUE;
345 EXCEPTION
346 WHEN OTHERS THEN
347 IF c_gc_daiv%ISOPEN THEN
348 CLOSE c_gc_daiv;
349 END IF;
350 RAISE;
351 END;
352 EXCEPTION
353 WHEN OTHERS THEN
354 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END grdp_val_gc_iud;
358 --
359 -- Validate if the venue has a system location type of CRD_CTR
360 FUNCTION grdp_val_ve_lot(
361 p_venue_cd IN VARCHAR2 ,
362 p_message_name OUT NOCOPY VARCHAR2 )
363 RETURN BOOLEAN AS
364 BEGIN -- grdp_val_ve_lot
365 -- Validate that the venue relates to a location which has a
366 -- location_type.s_location_type of GRD_CTR
367 DECLARE
368 v_ve_lot_found VARCHAR2(1);
369 cst_grd_ctr CONSTANT VARCHAR2(10) := 'GRD_CTR';
370 CURSOR c_ve IS
371 SELECT 'x'
372 FROM IGS_GR_VENUE ve,
373 IGS_AD_LOCATION loc,
374 IGS_AD_LOCATION_TYPE lot
375 WHERE ve.venue_cd = p_venue_cd AND
376 ve.exam_location_cd = loc.location_cd AND
377 loc.location_type = lot.location_type AND
378 lot.s_location_type = cst_grd_ctr;
379 BEGIN
380 p_message_name := NULL;
381 IF p_venue_cd IS NULL THEN
382 RETURN TRUE;
383 END IF;
384 OPEN c_ve;
385 FETCH c_ve INTO v_ve_lot_found;
386 IF c_ve%NOTFOUND THEN
387 CLOSE c_ve;
388 p_message_name := 'IGS_GR_TYPE_MUST_BE_GRD_CTR';
389 RETURN FALSE;
390 END IF;
391 CLOSE c_ve;
392 RETURN TRUE;
393 EXCEPTION
394 WHEN OTHERS THEN
395 IF c_ve%ISOPEN THEN
396 CLOSE c_ve;
397 END IF;
398 RAISE;
399 END;
400 EXCEPTION
401 WHEN OTHERS THEN
402 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
403 IGS_GE_MSG_STACK.ADD;
404 App_Exception.Raise_Exception;
405 END grdp_val_ve_lot;
406 --
407 -- To validate the venue closed indicator
408 FUNCTION ASSP_VAL_VE_CLOSED(
409 p_venue_cd IN VARCHAR2 ,
410 p_message_name OUT NOCOPY VARCHAR2 )
411 RETURN boolean AS
412 BEGIN -- assp_val_ve_closed
413 -- Validate the venue closed indicator
414 DECLARE
415 v_venue_cd IGS_GR_VENUE.venue_cd%TYPE;
416 v_ve_closed_ind IGS_GR_VENUE.closed_ind%TYPE;
417 v_ret_val BOOLEAN DEFAULT TRUE;
418 CURSOR c_ve IS
419 SELECT closed_ind
420 FROM IGS_GR_VENUE
421 WHERE venue_cd = p_venue_cd;
422 BEGIN
423 p_message_name := NULL;
424 OPEN c_ve;
425 FETCH c_ve INTO v_ve_closed_ind;
426 IF (c_ve%FOUND) THEN
427 IF (v_ve_closed_ind = 'Y') THEN
428 p_message_name := 'IGS_AS_VENUE_CLOSED';
429 v_ret_val := FALSE;
430 END IF;
431 END IF;
432 CLOSE c_ve;
433 RETURN v_ret_val;
434 END;
435 EXCEPTION
436 WHEN OTHERS THEN
437 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
438 IGS_GE_MSG_STACK.ADD;
439 App_Exception.Raise_Exception;
440 END assp_val_ve_closed;
441 END IGS_GR_VAL_GC;
[Home] [Help]