1 PACKAGE BODY IGS_CA_VAL_CIR AS
2 /* $Header: IGSCA06B.pls 120.1 2005/08/11 07:54:32 appldev ship $ */
3
4 /******************************************************************
5 Created By :
6 Date Created By :
7 Purpose :
8 Known limitations,
9 enhancements,
10 remarks :
11 Change History
12 Who When What
13 ssawhney 11-apr-2005 Bug 4252347. Modified find_subordinate, to match the complete CI and not just cal type.
14 major perf gain.
15 smadathi 09-Sep-2002 Bug# 2086177. Modified calp_val_ci_rltnshp to
16 establish relation between Admission Calendar
17 instance and Academic Term(Load) Calendar instance.
18 masehgal 29-Aug-2002 # 2442637 SWSD01_Calendar Build
19 Added validations for Award categories
20 Corrected logic for 'NEW' categories
21 And existing validations
22 schodava 18-Apr-2002 Enh # 2279265
23 Modifies the FUNCTION calp_val_ci_rltnshp
24 schodava 21-Jan-2002 Enh # 2187247
25 Modifies the FUNCTION calp_val_ci_rltnshp
26 ******************************************************************/
27
28 -- To validate that the calendar has a IGS_CA_TYPE.s_cal_cat of type 'LOAD'
29 FUNCTION calp_val_cat_load( p_cal_type IN VARCHAR2 ,
30 p_message_name OUT NOCOPY VARCHAR2 )
31 RETURN BOOLEAN AS
32
33 gv_other_detail VARCHAR2(255);
34
35 BEGIN
36 DECLARE
37 cst_load CONSTANT IGS_CA_TYPE.s_cal_cat%TYPE := 'LOAD';
38 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
39
40 CURSOR c_cat IS
41 SELECT s_cal_cat
42 FROM igs_ca_type cat
43 WHERE cat.cal_type = p_cal_type;
44
45 BEGIN
46 OPEN c_cat;
47 FETCH c_cat INTO v_s_cal_cat;
48 CLOSE c_cat;
49
50 IF v_s_cal_cat <> cst_load THEN
51 p_message_name := 'IGS_CA_CALTYPE_LOAD_UPD';
52 RETURN FALSE;
53 END IF;
54
55 p_message_name := NULL;
56 RETURN TRUE;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 IF (c_cat%ISOPEN) THEN
61 CLOSE c_cat;
62 END IF;
63 RAISE;
64 END;
65 END calp_val_cat_load;
66
67
68 --
69 -- To validate calendar instanes in a relationship
70 -- Code changed for Bug 4252347. by ssawhney
71 FUNCTION calp_val_cir_ci( p_sub_cal_type IN VARCHAR2 ,
72 p_sub_ci_sequence_number IN NUMBER ,
73 p_sup_cal_type IN VARCHAR2 ,
74 p_sup_ci_sequence_number IN NUMBER ,
75 p_message_name OUT NOCOPY VARCHAR2 )
76 RETURN BOOLEAN AS
77
78 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
79 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
80 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
81 v_cal_inst_rltshp_sup_rec IGS_CA_INST_REL%ROWTYPE;
82 v_cal_inst_rltshp_sub_rec IGS_CA_INST_REL%ROWTYPE;
83 e_superior_not_found EXCEPTION;
84 e_subordinate_not_found EXCEPTION;
85 v_superior_found BOOLEAN := FALSE;
86 v_subordinate_found BOOLEAN := FALSE;
87 v_other_detail VARCHAR2(255);
88 v_sup_cal_status IGS_CA_STAT.s_cal_status%TYPE;
89 v_sub_cal_status IGS_CA_STAT.s_cal_status%TYPE;
90
91 CURSOR c_cal_inst_rltshp_sup IS
92 SELECT *
93 FROM igs_ca_inst_rel
94 WHERE sub_cal_type = p_sub_cal_type
95 AND sub_ci_sequence_number = p_sub_ci_sequence_number
96 AND (sup_cal_type <> p_sup_cal_type)
97 AND (sup_ci_sequence_number <> p_sup_ci_sequence_number);
98
99 CURSOR c_cal_inst_rltshp_sub IS
100 SELECT *
101 FROM igs_ca_inst_rel
102 WHERE sup_cal_type = p_sup_cal_type
103 AND sup_ci_sequence_number = p_sup_ci_sequence_number
104 AND (sub_cal_type <> p_sub_cal_type)
105 AND (sub_ci_sequence_number <> p_sub_ci_sequence_number);
106
107 CURSOR c_cal_instance ( cp_cal_type igs_ca_inst.cal_type%TYPE,
108 cp_cal_sequence_number igs_ca_inst.sequence_number%TYPE) IS
109 SELECT *
110 FROM igs_ca_inst
111 WHERE cal_type = cp_cal_type
112 AND sequence_number = cp_cal_sequence_number ;
113
114 CURSOR c_cal_status( cp_cal_status igs_ca_stat.cal_status%TYPE) IS
115 SELECT *
116 FROM igs_ca_stat
117 WHERE cal_status = cp_cal_status;
118
119 FUNCTION calp_find_superior( p_sup_cal_type igs_ca_inst_rel.sup_cal_type%TYPE,
120 p_sup_ci_sequence_number igs_ca_inst_rel.sup_ci_sequence_number%TYPE,
121 p_sub_cal_type igs_ca_inst_rel.sub_cal_type%TYPE,
122 p_sub_ci_sequence_number igs_ca_inst_rel.sub_ci_sequence_number%TYPE)
123 RETURN BOOLEAN AS
124
125 v_cal_inst_rltshp_sup_rec igs_ca_inst_rel%ROWTYPE;
126
127 CURSOR c_cal_inst_rltshp_sup( cp_sup_cal_type igs_ca_inst_rel.sup_cal_type%TYPE,
128 cp_sup_ci_sequence_number igs_ca_inst_rel.sup_ci_sequence_number%TYPE) IS
129 SELECT *
130 FROM igs_ca_inst_rel
131 WHERE sub_cal_type = cp_sup_cal_type
132 AND sub_ci_sequence_number = cp_sup_ci_sequence_number;
133
134 BEGIN
135 IF (c_cal_inst_rltshp_sup%ISOPEN = FALSE) THEN
136 OPEN c_cal_inst_rltshp_sup( p_sup_cal_type, p_sup_ci_sequence_number);
137 END IF;
138
139 LOOP
140 FETCH c_cal_inst_rltshp_sup INTO v_cal_inst_rltshp_sup_rec;
141 EXIT WHEN c_cal_inst_rltshp_sup%NOTFOUND;
142
143 IF (v_cal_inst_rltshp_sup_rec.sup_cal_type = p_sub_cal_type AND
144 v_cal_inst_rltshp_sup_rec.sup_ci_sequence_number = p_sub_ci_sequence_number ) THEN
145 CLOSE c_cal_inst_rltshp_sup;
146 RETURN TRUE;
147 ELSE
148 IF (calp_find_superior
149 ( v_cal_inst_rltshp_sup_rec.sup_cal_type,
150 v_cal_inst_rltshp_sup_rec.sup_ci_sequence_number,
151 p_sub_cal_type,
152 p_sub_ci_sequence_number) = TRUE) THEN
153 CLOSE c_cal_inst_rltshp_sup;
154 RETURN TRUE;
155 END IF;
156 END IF;
157 END LOOP;
158
159 IF (c_cal_inst_rltshp_sup%ISOPEN) THEN
160 CLOSE c_cal_inst_rltshp_sup;
161 END IF;
162
163 RETURN FALSE;
164
165 END calp_find_superior;
166
167 FUNCTION calp_find_subordinate( p_sub_cal_type igs_ca_inst_rel.sub_cal_type%TYPE,
168 p_sub_ci_sequence_number igs_ca_inst_rel.sub_ci_sequence_number%TYPE,
169 p_sup_cal_type igs_ca_inst_rel.sup_cal_type%TYPE,
170 p_sup_ci_sequence_number igs_ca_inst_rel.sup_ci_sequence_number%TYPE)
171 --simran perf
172 --)
173 RETURN BOOLEAN AS
174
175 v_cal_inst_rltshp_sub_rec igs_ca_inst_rel%ROWTYPE;
176
177 CURSOR c_cal_inst_rltshp_sub( cp_sub_cal_type igs_ca_inst_rel.sub_cal_type%TYPE,
178 cp_sub_ci_sequence_number igs_ca_inst_rel.sub_ci_sequence_number%TYPE) IS
179 ----simran perf
180 SELECT *
181 FROM igs_ca_inst_rel
182 WHERE sup_cal_type = cp_sub_cal_type
183 AND sup_ci_sequence_number = cp_sub_ci_sequence_number; --simran perf --this was missing.
184 --due to which it was FTS on ca_inst_rel and doing recursive FTS as this func is recursive.
185
186
187 BEGIN
188 IF (c_cal_inst_rltshp_sub%ISOPEN = FALSE) THEN
189 OPEN c_cal_inst_rltshp_sub( p_sub_cal_type, p_sub_ci_sequence_number); --simran perf
190 END IF;
191
192 LOOP
193 FETCH c_cal_inst_rltshp_sub
194 INTO v_cal_inst_rltshp_sub_rec;
195 EXIT WHEN c_cal_inst_rltshp_sub%NOTFOUND;
196 IF ( v_cal_inst_rltshp_sub_rec.sub_cal_type = p_sup_cal_type AND
197 v_cal_inst_rltshp_sub_rec.sub_ci_sequence_number = p_sup_ci_sequence_number ) THEN
198 --simran perf, validate whether sub's cursors, sub cal = sup cal passed.
199 CLOSE c_cal_inst_rltshp_sub;
200 RETURN TRUE;
201 ELSE
202 IF (calp_find_subordinate( v_cal_inst_rltshp_sub_rec.sub_cal_type,
203 v_cal_inst_rltshp_sub_rec.sub_ci_sequence_number,
204 p_sup_cal_type,
205 p_sup_ci_sequence_number)) = TRUE THEN
206 CLOSE c_cal_inst_rltshp_sub;
207 RETURN TRUE;
208 END IF;
209 END IF;
210
211 END LOOP;
212
213 IF (c_cal_inst_rltshp_sub%ISOPEN) THEN
214 CLOSE c_cal_inst_rltshp_sub;
215 END IF;
216
217 RETURN FALSE;
218
219 END calp_find_subordinate;
220
221 BEGIN
222 -- Validate sub-ordinate and superior calendar types cannot be the same
223 IF(p_sup_cal_type = p_sub_cal_type) THEN
224 p_message_name := 'IGS_CA_SUBORD_SUPCAL_NOTSAME';
225 RETURN FALSE;
226 END IF;
227 -- Validate superior calendar instance exists
228 -- Retain system calendar status
229 FOR c_cal_instance_rec IN c_cal_instance( p_sup_cal_type, p_sup_ci_sequence_number)
230 LOOP
231 v_superior_found := TRUE;
232 FOR v_cal_status_rec IN c_cal_status( c_cal_instance_rec.cal_status)
233 LOOP
234 v_sup_cal_status := v_cal_status_rec.s_cal_status;
235 END LOOP;
236 END LOOP;
237
238 IF (v_superior_found = FALSE) THEN
239 RAISE e_superior_not_found;
240 END IF;
241
242 -- Validate sub-ordinate calendar instance exists
243 -- Retain system calendar status
244 FOR c_cal_instance_rec IN c_cal_instance ( p_sub_cal_type, p_sub_ci_sequence_number)
245 LOOP
246 v_subordinate_found := TRUE;
247 FOR v_cal_status_rec IN c_cal_status( c_cal_instance_rec.cal_status)
248 LOOP
249 v_sub_cal_status := v_cal_status_rec.s_cal_status;
250 END LOOP;
251 END LOOP;
252
253 IF (v_subordinate_found = FALSE) THEN
254 RAISE e_subordinate_not_found;
255 END IF;
256
257 -- Validate calendar status between sub-ordinate and superior
258 IF (v_sup_cal_status = cst_planned AND
259 v_sub_cal_status = cst_inactive) THEN
260 p_message_name :='IGS_CA_SUBORD_NOT_INACTIVE_ST';
261 RETURN FALSE;
262 END IF;
263
264 IF (v_sup_cal_status = cst_planned OR
265 v_sup_cal_status = cst_inactive) AND
266 (v_sub_cal_status = cst_active) THEN
267 p_message_name :='IGS_CA_SUBORD_CANNOT_ACTIVEST';
268 RETURN FALSE;
269 END IF;
270 IF (v_sup_cal_status = cst_inactive AND
271 v_sub_cal_status = cst_planned) THEN
272 p_message_name :='IGS_CA_SUBORD_CANNOT_PLANST';
273 RETURN FALSE;
274 END IF;
275
276 -- Validate that sub-ordinate does not already exist in the superior calendar
277 --structure
278 IF (calp_find_superior(p_sup_cal_type,
279 p_sup_ci_sequence_number,
280 p_sub_cal_type,
281 p_sub_ci_sequence_number) = TRUE) THEN
282 p_message_name := 'IGS_CA_SUP_SUBORD_EXISTS';
283 RETURN FALSE;
284 END IF;
285 -- Validate that superior does not already exist in the sub-ordinate calendar
286 --structure
287 IF (calp_find_subordinate(p_sub_cal_type,
288 p_sub_ci_sequence_number,
289 p_sup_cal_type,
290 --p_sup_ci_sequence_number)) THEN --simran perf
291 p_sup_ci_sequence_number)) THEN
292 p_message_name := 'IGS_CA_SUP_SUBORD_EXISTS';
293 RETURN FALSE;
294 END IF;
295
296 p_message_name :=NULL;
297 RETURN TRUE;
298
299 EXCEPTION
300 WHEN e_superior_not_found THEN
301 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
302 RETURN FALSE;
303 WHEN e_subordinate_not_found THEN
304 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
305 RETURN FALSE;
306 END calp_val_cir_ci;
307
308
309 --
310 -- To validate calendar instances categories in a relationship
311 FUNCTION calp_val_ci_rltnshp(
312 p_sub_cal_cat IN VARCHAR2 ,
313 p_sup_cal_cat IN VARCHAR2
314 )
315 RETURN VARCHAR2 AS
316 /******************************************************************
317 Created By :
318 Date Created By :
319 Purpose :
320 Known limitations,
321 enhancements,
322 remarks :
323 Change History
324
325 Who When What
326 masehgal 05-Nov-2002 # 2613546 FA `05_108 Multi Award Years Build
327 Added Teach as a possible subordinate for Award categories
328 smadathi 09-Sep-2002 Bug# 2086177. SWSFD01_calendar Build.
329 Established relation between Admission Calendar
330 instance and Academic Term(Load) Calendar instance.
331 masehgal 29-Aug-2002 # 2442637 SWSD01_Calendar Build
332 Added validations for Award categories
333 Corrected logic for 'NEW' categories
334 And existing validations
335 schodava 21-Jan-2002 Bug # 2279265
336 Prevents a relationship between Load and
337 Teaching Calendars
338 schodava 21-Jan-2002 Enh # 2187247
339 Creates a superior-subordinate relationship
340 between Fee and Load Calendars
341 ******************************************************************/
342
343 gv_other_detail VARCHAR2(255);
344
345 BEGIN
346 -- calp_val_ci_rltnshp
347 -- Validate the relationship between two calendar instances of nominated
348 -- calendar categories.
349 -- This routine has no return message number as it is designed to be called
350 -- from within LOVs
351 -- and View definitions. Any message displaying must be done by the
352 -- calling routine.
353 DECLARE
354 -- SWSFD01_Calendar Build
355 -- Award is a System defined category but not taken care of.
356 -- Including that here.
357 cst_award CONSTANT VARCHAR2(5) := 'AWARD' ;
358 cst_academic CONSTANT VARCHAR2(8) := 'ACADEMIC';
359 cst_admission CONSTANT VARCHAR2(9) := 'ADMISSION';
360 cst_assessment CONSTANT VARCHAR2(10) := 'ASSESSMENT';
361 cst_enrolment CONSTANT VARCHAR2(9) := 'ENROLMENT';
362 cst_exam CONSTANT VARCHAR2(4) := 'EXAM';
363 cst_fee CONSTANT VARCHAR2(3) := 'FEE';
364 cst_finance CONSTANT VARCHAR2(7) := 'FINANCE';
365 cst_load CONSTANT VARCHAR2(4) := 'LOAD';
366 cst_progress CONSTANT VARCHAR2(8) := 'PROGRESS';
367 cst_teaching CONSTANT VARCHAR2(8) := 'TEACHING';
368 cst_holiday CONSTANT VARCHAR2(7) := 'HOLIDAY';
369 cst_graduation CONSTANT VARCHAR2(10) := 'GRADUATION';
370 cst_userdef CONSTANT VARCHAR2(8) := 'USERDEF';
371
372 BEGIN
373
374 -- * If either of the categories are 'user defined' then return 'Y'.
375 -- User defined calendars are not restricted.
376 IF ( p_sup_cal_cat = cst_userdef OR p_sub_cal_cat = cst_userdef ) THEN
377 RETURN 'TRUE';
378 END IF;
379
380 -- # 2442637 SWSFD01_Calendar Build
381 -- Logic for checking 'NEW' category corrected
382 -- Also added 'Award' to the existing categories
383
384 -- If either the categories are 'new' to the system, then don't enforce any relations
385 IF (p_sup_cal_cat NOT IN (cst_award, cst_academic, cst_admission, cst_assessment,
386 cst_enrolment,cst_exam, cst_fee, cst_finance, cst_load,
387 cst_progress, cst_teaching, cst_holiday, cst_graduation)
388 OR
389 p_sub_cal_cat NOT IN (cst_award, cst_academic, cst_admission, cst_assessment,
390 cst_enrolment,cst_exam, cst_fee, cst_finance, cst_load,
391 cst_progress, cst_teaching, cst_holiday, cst_graduation)
392 ) THEN
393 IF ( p_sup_cal_cat <> cst_userdef AND p_sub_cal_cat <> cst_userdef) THEN
394 RETURN 'TRUE';
395 END IF;
396 END IF;
397
398 IF (p_sup_cal_cat IN (cst_award, cst_academic, cst_admission, cst_assessment,
399 cst_enrolment,cst_exam, cst_fee, cst_finance, cst_load,
400 cst_progress, cst_teaching, cst_holiday, cst_graduation)
401 AND (p_sub_cal_cat IN (cst_award, cst_academic, cst_admission, cst_assessment,
402 cst_enrolment,cst_exam, cst_fee, cst_finance, cst_load,
403 cst_progress, cst_teaching, cst_holiday, cst_graduation,NULL)
404 )
405 ) THEN
406
407 -- * Check the p_sup_cal_cat, p_sub_cal_cat values against
408 -- existing superior/subordinate combinations
409
410 -- # 2442637 SWSFD01_Calendar Build
411 -- Added check for Award Category
412 IF ( p_sup_cal_cat = cst_award AND p_sub_cal_cat IN (cst_load, cst_teaching) ) THEN
413 RETURN 'TRUE' ;
414 END IF ;
415
416 IF p_sup_cal_cat = cst_academic THEN
417 IF (p_sub_cal_cat IN (cst_admission, cst_assessment, cst_enrolment,
418 cst_exam, cst_fee, cst_load, cst_progress,
419 cst_teaching, cst_holiday, cst_graduation )
420 ) THEN
421 RETURN 'TRUE';
422 END IF;
423 END IF;
424
425 -- An Academic Term (Load) calendar instance is a subordinate calendar
426 -- relationship to Admission Calendar Instance
427 IF ( p_sup_cal_cat = cst_admission AND (p_sub_cal_cat IN (cst_enrolment,cst_load))) THEN
428 RETURN 'TRUE';
429 END IF;
430
431 IF p_sup_cal_cat = cst_assessment THEN
432 IF (p_sub_cal_cat IN (cst_assessment, cst_teaching)) THEN
433 RETURN 'TRUE';
434 END IF;
435 END IF;
436
437 IF (p_sup_cal_cat = cst_enrolment AND p_sub_cal_cat IS NULL) THEN
438 RETURN 'TRUE';
439 END IF;
440
441 IF p_sup_cal_cat = cst_exam THEN
442 IF (p_sub_cal_cat IN (cst_exam, cst_teaching)) THEN
443 RETURN 'TRUE';
444 END IF;
445 END IF;
446
447 -- Enh # 2187247
448 -- Creates a superior-subordinate relationship
449 -- between Fee and Load Calendars
450 IF ( p_sup_cal_cat = cst_fee AND p_sub_cal_cat = cst_load ) THEN
451 RETURN 'TRUE';
452 END IF;
453
454 IF ( p_sup_cal_cat = cst_finance AND p_sub_cal_cat = cst_fee) THEN
455 RETURN 'TRUE';
456 END IF;
457
458 -- Bug # 2279265
459 -- Removed the relationship between Load and Teaching calendars
460 IF p_sup_cal_cat = cst_progress THEN
461 IF (p_sub_cal_cat IN (cst_progress, cst_teaching, cst_load)) THEN
462 RETURN 'TRUE';
463 END IF;
464 END IF;
465
466 IF ( p_sup_cal_cat = cst_teaching AND p_sub_cal_cat = cst_admission) THEN
467 RETURN 'TRUE';
468 END IF;
469
470 IF ( p_sup_cal_cat = cst_holiday AND p_sub_cal_cat = cst_holiday) THEN
471 RETURN 'TRUE';
472 END IF;
473
474 END IF;
475 RETURN 'FALSE';
476 END;
477 END calp_val_ci_rltnshp;
478 END IGS_CA_VAL_CIR;
[Home] [Help]