1 PACKAGE BODY IGS_CA_VAL_CI AS
2 /* $Header: IGSCA05B.pls 115.7 2002/11/28 22:57:15 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --npalanis 12-NOV-2002 Bug No. 2563531 .The alternate code is made required for
7 -- LOAD calendar instances.
8 --smadathi 27-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
9 -------------------------------------------------------------------------------------------
10 -- Validate if calendar status is closed.
11 FUNCTION calp_val_cs_closed(
12 p_cal_status IN VARCHAR2 ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 gv_other_detail VARCHAR2(255);
16 BEGIN --calp_val_cs_closed
17 --This module Validates if IGS_CA_STAT.cal_status is closed
18 DECLARE
19 v_cs_exists VARCHAR2(1);
20 CURSOR c_cs IS
21 SELECT 'X'
22 FROM IGS_CA_STAT cs
23 WHERE cs.cal_status = p_cal_status AND
24 cs.closed_ind = 'Y';
25 BEGIN
26 --Set the default message number
27 p_message_name :=NULL;
28 --If record exists then closed_ind = 'Y' therefore set p_message_name
29 OPEN c_cs;
30 FETCH c_cs INTO v_cs_exists;
31 IF (c_cs%FOUND) THEN
32 p_message_name := 'IGS_CA_CAL_STATUS_CLOSED';
33 CLOSE c_cs;
34 RETURN FALSE;
35 END IF;
36 CLOSE c_cs;
37 RETURN TRUE;
38 END;
39
40 END calp_val_cs_closed;
41 --
42 -- To validate calendar instance alternate code
43 FUNCTION calp_val_ci_alt_cd(
44 p_cal_type IN VARCHAR2 ,
45 p_alternate_code IN VARCHAR2 ,
46 p_message_name OUT NOCOPY VARCHAR2 )
47 RETURN boolean AS
48 gv_other_detail VARCHAR2(255);
49 BEGIN
50 DECLARE
51 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
52 cst_teaching CONSTANT VARCHAR2(10) := 'TEACHING';
53 cst_admission CONSTANT VARCHAR2(10) := 'ADMISSION';
54 cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
55 cst_award CONSTANT VARCHAR2(10) := 'AWARD';
56 cst_load CONSTANT VARCHAR2(10) := 'LOAD';
57 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
58 CURSOR c_cal_type (
59 cp_cal_type IGS_CA_TYPE.cal_type%TYPE) IS
60 SELECT CAT.s_cal_cat
61 FROM IGS_CA_TYPE CAT
62 WHERE CAT.cal_type = cp_cal_type;
63 BEGIN
64 -- Module to validate that alternate code is given for calendar instances whose
65 -- calendar type has system calendar categories 'ACADEMIC', 'TEACHING',
66 -- 'ADMISSION','AWARD' and 'PROGRESS'.
67 p_message_name := NULL;
68 OPEN c_cal_type(
69 p_cal_type);
70 FETCH c_cal_type INTO v_s_cal_cat;
71 IF(c_cal_type%FOUND) THEN
72 IF(p_alternate_code IS NULL) THEN
73 IF(v_s_cal_cat = cst_academic) THEN
74 CLOSE c_cal_type;
75 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
76 RETURN FALSE;
77 END IF;
78 IF(v_s_cal_cat = cst_teaching) THEN
79 CLOSE c_cal_type;
80 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
81 RETURN FALSE;
82 END IF;
83 IF(v_s_cal_cat = cst_admission) THEN
84 CLOSE c_cal_type;
85 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
86 RETURN FALSE;
87 END IF;
88 -- added for bug 1620686
89 IF(v_s_cal_cat = cst_award) THEN
90 CLOSE c_cal_type;
91 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
92 RETURN FALSE;
93 END IF;
94 -- added for bug 1620686
95 IF(v_s_cal_cat = cst_progress) THEN
96 CLOSE c_cal_type;
97 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
98 RETURN FALSE;
99 END IF;
100 -- added for bug 2563531 ( to check that the load caledar is required)
101 IF(v_s_cal_cat = cst_load) THEN
102 CLOSE c_cal_type;
103 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
104 RETURN FALSE;
105 END IF;
106 END IF;
107 END IF;
108 CLOSE c_cal_type;
109 RETURN TRUE;
110 END;
111 END calp_val_ci_alt_cd;
112 --
113 --
114 -- To validate a change of calendar instance
115 FUNCTION calp_val_ci_status(
116 p_cal_type IN VARCHAR2 ,
117 p_sequence_number IN NUMBER ,
118 p_old_cal_status IN VARCHAR2 ,
119 p_new_cal_status IN VARCHAR2 ,
120 p_message_name OUT NOCOPY VARCHAR2 )
121 RETURN boolean AS
122 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
123 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
124 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
125 v_other_detail VARCHAR2(255);
126 v_cal_instance_rec IGS_CA_INST%ROWTYPE;
127 v_cal_instance_rltshp_rec IGS_CA_INST_REL%ROWTYPE;
128 v_dt_alias_instance_rec IGS_CA_DA_INST_V%ROWTYPE;
129 v_new_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
130 v_old_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
131 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
132 -- define cursors
133 CURSOR c_cal_status(cp_cal_status IGS_CA_STAT.cal_status%TYPE)
134 IS
135 SELECT *
136 FROM IGS_CA_STAT
137 WHERE cal_status = cp_cal_status;
138 CURSOR c_cir_subord_calendars
139 IS
140 SELECT *
141 FROM IGS_CA_INST_REL
142 WHERE sup_cal_type = p_cal_type AND
143 sup_ci_sequence_number = p_sequence_number;
144 CURSOR c_cir_superior_calendars
145 IS
146 SELECT *
147 FROM IGS_CA_INST_REL
148 WHERE sub_cal_type = p_cal_type AND
149 sub_ci_sequence_number = p_sequence_number;
150 CURSOR c_dt_alias_instance
151 IS
152 SELECT *
153 FROM IGS_CA_DA_INST_V
154 WHERE cal_type = p_cal_type AND
155 ci_sequence_number = p_sequence_number;
156 CURSOR c_cal_instance(cp_cal_type IGS_CA_INST.cal_type%TYPE,
157 cp_sequence_number IGS_CA_INST.sequence_number%TYPE)
158 IS
159 SELECT *
160 FROM IGS_CA_INST
161 WHERE cal_type = cp_cal_type AND
162 sequence_number = cp_sequence_number;
163 -- define sub-routines
164 FUNCTION check_status_change
165 RETURN boolean AS
166 BEGIN
167 FOR v_cal_status_rec IN c_cal_status(p_old_cal_status) LOOP
168 v_old_s_cal_status := v_cal_status_rec.s_cal_status;
169 END LOOP;
170 IF (v_new_s_cal_status = cst_inactive)
171 THEN
172 -- check calendar status is not being changed from PLANNED to INACTIVE
173 IF (v_old_s_cal_status = cst_planned)
174 THEN
175 p_message_name := 'IGS_CA_CALST_NOTCHG_PLAN_ACT';
176 RETURN FALSE;
177 END IF;
178 ELSIF (v_new_s_cal_status = cst_planned)
179 THEN
180 -- Check status is not being changed from INACTIVE to PLANNED
181 IF (v_old_s_cal_status = cst_inactive) THEN
182 p_message_name := 'IGS_CA_INACTIVE_NOTCHG_PLANN';
183 RETURN FALSE;
184 END IF;
185 END IF;
186 RETURN TRUE;
187 END check_status_change;
188 FUNCTION check_related_calendars
189 RETURN boolean AS
190 BEGIN
191 IF (v_new_s_cal_status = cst_inactive) OR
192 (v_new_s_cal_status = cst_planned)
193 THEN
194 -- Check subordinate calendars
195 OPEN c_cir_subord_calendars;
196 LOOP
197 FETCH c_cir_subord_calendars INTO v_cal_instance_rltshp_rec;
198 EXIT WHEN
199 c_cir_subord_calendars%NOTFOUND;
200 FOR v_cal_instance_rec IN
201 c_cal_instance(v_cal_instance_rltshp_rec.sub_cal_type,
202 v_cal_instance_rltshp_rec.sub_ci_sequence_number) LOOP
203 EXIT WHEN
204 c_cal_instance%NOTFOUND;
205 FOR v_cal_status_rec IN
206 c_cal_status(v_cal_instance_rec.cal_status) LOOP
207 v_s_cal_status := v_cal_status_rec.s_cal_status;
208 END LOOP;
209 IF (v_new_s_cal_status = cst_inactive)
210 THEN
211 -- if new status is INACTIVE, check ACTIVE or PLANNED sub-ordinate
212 -- calendars do not exist
213 IF (v_s_cal_status = cst_active) OR
214 (v_s_cal_status = cst_planned)
215 THEN
216 CLOSE c_cir_subord_calendars;
217 p_message_name := 'IGS_CA_ACTIVE_PLAN_SUBORD';
218 RETURN FALSE;
219 END IF;
220 ELSIF (v_new_s_cal_status = cst_planned)
221 THEN
222 -- if new status is PLANNED, check ACTIVE or INACTIVE sub-ordinate
223 -- calendars do not exist
224 IF (v_s_cal_status = cst_active) OR
225 (v_s_cal_status = cst_inactive)
226 THEN
227 CLOSE c_cir_subord_calendars;
228 p_message_name := 'IGS_CA_ACTIVE_INACTIVE_SUBORD';
229 RETURN FALSE;
230 END IF;
231 END IF;
232 END LOOP;
233 END LOOP;
234 ELSIF (v_new_s_cal_status = cst_active) THEN
235 -- Check subordinate calendars
236 OPEN c_cir_superior_calendars;
237 LOOP
238 FETCH c_cir_superior_calendars
239 INTO v_cal_instance_rltshp_rec;
240 EXIT WHEN
241 c_cir_superior_calendars%NOTFOUND;
242 FOR v_cal_instance_rec IN
243 c_cal_instance(v_cal_instance_rltshp_rec.sup_cal_type,
244 v_cal_instance_rltshp_rec.sup_ci_sequence_number) LOOP
245 EXIT WHEN
246 c_cal_instance%NOTFOUND;
247 FOR v_cal_status_rec IN
248 c_cal_status(v_cal_instance_rec.cal_status) LOOP
249 v_s_cal_status := v_cal_status_rec.s_cal_status;
250 END LOOP;
251 -- new status is ACTIVE, check superior calendars are not
252 -- INACTIVE or PLANNED
253 IF(v_s_cal_status = cst_inactive) OR
254 (v_s_cal_status = cst_planned) THEN
255 CLOSE c_cir_superior_calendars;
256 p_message_name :='IGS_CA_SUPCAL_INACTIVE_PLAN';
257 RETURN FALSE;
258 END IF;
259 END LOOP;
260 END LOOP;
261 END IF;
262 IF(c_cir_subord_calendars%ISOPEN) THEN
263 CLOSE c_cir_subord_calendars;
264 END IF;
265 IF(c_cir_superior_calendars%ISOPEN) THEN
266 CLOSE c_cir_superior_calendars;
267 END IF;
268 RETURN TRUE;
269 END check_related_calendars;
270 FUNCTION check_dt_alias_instances
271 RETURN boolean AS
272 BEGIN
273 -- check date alias's have an alias value
274 IF (v_new_s_cal_status = cst_active)
275 THEN
276 OPEN c_dt_alias_instance;
277 LOOP
278 FETCH c_dt_alias_instance
279 INTO v_dt_alias_instance_rec;
280 EXIT WHEN
281 c_dt_alias_instance%NOTFOUND;
282 IF (v_dt_alias_instance_rec.alias_val IS NULL) THEN
283 CLOSE c_dt_alias_instance;
284 p_message_name :='IGS_CA_STATUS_NOTCHG_ACTIVE';
285 RETURN FALSE;
286 END IF;
287 END LOOP;
288 IF (c_dt_alias_instance%ISOPEN) THEN
289 CLOSE c_dt_alias_instance;
290 END IF;
291 END IF;
292 RETURN TRUE;
293 END check_dt_alias_instances;
294 BEGIN
295 -- check if the calendar status has changed
296 IF (p_new_cal_status = p_old_cal_status)
297 THEN
298 p_message_name :=NULL;
299 RETURN TRUE;
300 END IF;
301 FOR v_cal_status_rec IN c_cal_status(p_new_cal_status) LOOP
302 -- check calendar status is not closed
303 IF (v_cal_status_rec.closed_ind = 'Y') THEN
304 p_message_name := 'IGS_CA_CAL_STATUS_CLOSED';
305 RETURN FALSE;
306 END IF;
307 v_new_s_cal_status := v_cal_status_rec.s_cal_status;
308 END LOOP;
309 -- p_old_cal_status may not have been passed
310 IF (NVL(p_old_cal_status,' ') <> ' ')
311 THEN
312 IF check_status_change = FALSE
313 THEN
314 RETURN FALSE;
315 END IF;
316 END IF;
317 -- p_cal_type and p_sequence_number may not have been passed
318 IF (NVL(p_cal_type,' ') <> ' ') AND
319 (NVL(to_char(p_sequence_number),' ') <> ' ')
320 THEN
321 IF check_related_calendars = FALSE
322 THEN
323 RETURN FALSE;
324 END IF;
325 IF check_dt_alias_instances = FALSE
326 THEN
327 RETURN FALSE;
328 END IF;
329 END IF;
330 p_message_name := NULL;
331 RETURN TRUE;
332 END calp_val_ci_status;
333 --
334 -- To validate columns on insert or update of calendar instance.
335 FUNCTION calp_val_ci_upd(
336 p_cal_type IN VARCHAR2 ,
337 p_sequence_number IN NUMBER ,
338 p_alternate_code IN VARCHAR2 ,
339 p_message_name OUT NOCOPY VARCHAR2 )
340 RETURN boolean AS
341 cst_teaching_period CONSTANT VARCHAR2(15) := 'TEACHING';
342 cst_academic_period CONSTANT VARCHAR2(15) := 'ACADEMIC';
343 CURSOR c_cal_instance (
344 cp_cal_type IGS_CA_INST.cal_type%TYPE,
345 cp_cal_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
346 SELECT *
347 FROM IGS_CA_INST
348 WHERE cal_type = cp_cal_type
349 AND sequence_number = cp_cal_sequence_number ;
350 CURSOR c_cal_inst_rltshp_sup(
351 cp_sub_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
352 cp_sub_ci_sequence_number
353 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE) IS
354 SELECT *
355 FROM IGS_CA_INST_REL
356 WHERE sub_cal_type = cp_sub_cal_type
357 AND sub_ci_sequence_number = cp_sub_ci_sequence_number;
358 CURSOR c_cal_inst_rltshp_sub(
359 cp_sup_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
360 cp_sup_ci_sequence_number
361 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE) IS
362 SELECT *
363 FROM IGS_CA_INST_REL
364 WHERE sup_cal_type = cp_sup_cal_type
365 AND sup_ci_sequence_number = cp_sup_ci_sequence_number;
366 CURSOR c_cal_type(
367 cp_cal_type IGS_CA_TYPE.cal_type%TYPE)
368 IS
369 SELECT *
370 FROM IGS_CA_TYPE
371 WHERE cal_type = cp_cal_type
372 AND s_cal_cat = cst_academic_period;
373 CURSOR c_cal_typ(
374 cp_cal_type IGS_CA_TYPE.cal_type%TYPE)
375 IS
376 SELECT *
377 FROM IGS_CA_TYPE
378 WHERE cal_type = cp_cal_type
379 AND s_cal_cat = cst_teaching_period;
380 v_other_detail VARCHAR2(255);
381 BEGIN
382 FOR c_cal_inst_rltshp_sup_rec IN c_cal_inst_rltshp_sup(
383 p_cal_type,
384 p_sequence_number)
385 LOOP
386 FOR c_cal_type_rec IN c_cal_type(c_cal_inst_rltshp_sup_rec.sup_cal_type)
387 LOOP
388 FOR c_cal_inst_rltshp_sub_rec IN c_cal_inst_rltshp_sub(
389 c_cal_inst_rltshp_sup_rec.sup_cal_type,
390 c_cal_inst_rltshp_sup_rec.sup_ci_sequence_number)
391 LOOP
392 FOR c_cal_typ_rec IN c_cal_typ(
393 c_cal_inst_rltshp_sub_rec.sub_cal_type)
394 LOOP
395 FOR c_cal_instance_rec IN c_cal_instance(
396 c_cal_inst_rltshp_sub_rec.sub_cal_type,
397 c_cal_inst_rltshp_sub_rec.sub_ci_sequence_number)
398 LOOP
399 IF ((c_cal_instance_rec.alternate_code = p_alternate_code) AND
400 (NOT(c_cal_instance_rec.cal_type = p_cal_type AND
401 c_cal_instance_rec.sequence_number = p_sequence_number))) THEN
402 p_message_name := 'IGS_CA_ALTCD_EXISTS_TEACHING';
403 RETURN TRUE;
404 END IF;
405 END LOOP; --c_cal_instance
406 END LOOP; --c_cal_typ
407 END LOOP;-- c_cal_inst_rltshp_sub
408 END LOOP; --c_cal_type
409 END LOOP; --c_cal_inst_rltshp_sup
410 p_message_name := NULL;
411 RETURN TRUE;
412 EXCEPTION
413 WHEN OTHERS THEN
414
415 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
416 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_CI.calp_val_ci_upd');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception;
419 END calp_val_ci_upd;
420 END IGS_CA_VAL_CI;
[Home] [Help]