1 PACKAGE BODY IGS_AD_VAL_APCOOD AS
2 /* $Header: IGSAD41B.pls 115.6 2003/01/08 14:33:59 rghosh ship $ */
3 -- Validate admission period calendar instance
4
5 --
6 -- Validate the adm period course off option date details
7 FUNCTION admp_val_apcood_opt(
8 p_course_cd IN VARCHAR2 ,
9 p_version_number IN NUMBER ,
10 p_acad_cal_type IN VARCHAR2 ,
11 p_location_cd IN VARCHAR2 ,
12 p_attendance_mode IN VARCHAR2 ,
13 p_attendance_type IN VARCHAR2 ,
14 p_adm_cal_type IN VARCHAR2 ,
15 p_adm_ci_sequence_number IN NUMBER ,
16 p_admission_cat IN VARCHAR2 ,
17 p_s_admission_process_type IN VARCHAR2 ,
18 p_message_name OUT NOCOPY VARCHAR2 )
19 RETURN BOOLEAN IS
20 gv_other_detail VARCHAR2(255);
21 BEGIN -- admp_val_apcood_opt
22 -- Validate the admission period course offering option date details
23 DECLARE
24 v_s_admission_process_type IGS_AD_PRCS_CAT.s_admission_process_type%TYPE;
25 v_message_name varchar2(30);
26 v_valid_optn BOOLEAN DEFAULT NULL;
27 v_apapc_found BOOLEAN DEFAULT FALSE;
28 CURSOR c_apapc IS
29 SELECT s_admission_process_type
30 FROM IGS_AD_PRD_AD_PRC_CA
31 WHERE adm_cal_type = p_adm_cal_type AND
32 adm_ci_sequence_number = p_adm_ci_sequence_number AND
33 admission_cat = p_admission_cat;
34 ----------------------------------------- SUBFUNCTION -------------------------
35 ------------------------------
36 FUNCTION admpl_val_option (v_s_admission_process_type
37 IGS_AD_PRCS_CAT.s_admission_process_type%TYPE)
38 RETURN BOOLEAN
39 IS
40 BEGIN -- admpl_val_option
41 -- validate options
42 DECLARE
43 v_return_val BOOLEAN DEFAULT FALSE;
44 v_location_cd IGS_PS_OFR_PAT.location_cd%TYPE;
45 v_attendance_mode IGS_PS_OFR_PAT.attendance_mode%TYPE;
46 v_attendance_type IGS_PS_OFR_PAT.attendance_type%TYPE;
47 -- Validate option
48 CURSOR c_acov (
49 cp_s_admission_process_type
50 IGS_AD_PRCS_CAT.s_admission_process_type%TYPE) IS
51 SELECT acov.location_cd,
52 acov.attendance_mode,
53 acov.attendance_type
54 FROM IGS_PS_OFR_PAT_APCOOD_V acov
55 WHERE acov.adm_cal_type = p_adm_cal_type AND
56 acov.adm_ci_sequence_number = p_adm_ci_sequence_number AND
57 acov.admission_cat = p_admission_cat AND
58 acov.s_admission_process_type = cp_s_admission_process_type AND
59 (p_course_cd IS NULL OR
60 (acov.course_cd = p_course_cd AND
61 acov.version_number = p_version_number AND
62 acov.acad_cal_type = p_acad_cal_type)) AND
63 (p_location_cd IS NULL OR
64 acov.location_cd = p_location_cd) AND
65 (p_attendance_mode IS NULL OR
66 acov.attendance_mode = p_attendance_mode) AND
67 (p_attendance_type IS NULL OR
68 acov.attendance_type = p_attendance_type);
69 BEGIN
70 OPEN c_acov(v_s_admission_process_type);
71 FETCH c_acov INTO v_location_cd,
72 v_attendance_mode,
73 v_attendance_type;
74 IF (c_acov%FOUND) THEN
75 -- Option is valid
76 v_return_val := TRUE;
77 ELSE
78 -- Option is invalid
79 v_return_val := FALSE;
80 END IF;
81 CLOSE c_acov;
82 RETURN v_return_val;
83 END;
84 EXCEPTION
85 WHEN OTHERS THEN
86 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
87 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_option');
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END admpl_val_option;
91 ---------------------------- MAIN ---------------------------------
92 BEGIN
93 p_message_name := Null;
94 IF p_s_admission_process_type IS NULL AND
95 p_course_cd IS NULL AND
96 p_location_cd IS NULL AND
97 p_attendance_mode IS NULL AND
98 p_attendance_type IS NULL THEN
99 -- At least one of the components must be specified for overrides
100 p_message_name := 'IGS_AD_ONE_COMPONENT_SPECIFY';
101 Return FALSE;
102 END IF;
103 IF p_location_cd IS NOT NULL OR
104 p_attendance_mode IS NOT NULL OR
105 p_attendance_type IS NOT NULL THEN
106 -- Check if the offering option is valid
107 IF p_s_admission_process_type IS NOT NULL THEN
108 v_s_admission_process_type := p_s_admission_process_type;
109 -- Validate option
110 IF admpl_val_option(v_s_admission_process_type) = FALSE THEN
111 v_valid_optn := FALSE;
112 ELSE
113 v_valid_optn := TRUE;
114 END IF;
115 ELSE
116 -- Select s_admission_process_type from dbase
117 FOR v_apapc_rec IN c_apapc LOOP
118 v_apapc_found := TRUE;
119 v_s_admission_process_type := v_apapc_rec.s_admission_process_type;
120 -- Validate option
121 IF NOT admpl_val_option(v_s_admission_process_type) THEN
122 v_valid_optn := FALSE;
123 ELSE
124 v_valid_optn := TRUE;
125 EXIT;
126 END IF;
127 END LOOP;
128 IF v_apapc_found = FALSE THEN
129 RETURN TRUE;
130 END IF;
131 END IF;
132 IF v_valid_optn = FALSE THEN
133 p_message_name := 'IGS_AD_INVALID_POO_DT_OVERRID';
134 RETURN FALSE;
135 END IF;
136 END IF;
137 RETURN TRUE;
138 END;
139 EXCEPTION
140 WHEN OTHERS THEN
141 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
142 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_apcood_opt');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END admp_val_apcood_opt;
146 --
147 -- Validate the adm period course off option date course offering
148 FUNCTION admp_val_apcood_co(
149 p_course_cd IN VARCHAR2 ,
150 p_version_number IN NUMBER ,
151 p_acad_cal_type IN VARCHAR2 ,
152 p_admission_cat IN VARCHAR2 ,
153 p_s_admission_process_type IN VARCHAR2 ,
154 p_adm_cal_type IN VARCHAR2 ,
155 p_adm_ci_sequence_number IN NUMBER ,
156 p_message_name OUT NOCOPY VARCHAR2 )
157 RETURN BOOLEAN IS
158 gv_other_detail VARCHAR2(255);
159 BEGIN -- admp_val_apcood_co
160 -- Routine to verify that the course offering is valid for the admission
161 -- period course offering option date override.
162 DECLARE
163 v_s_adm_proc_type IGS_AD_PRD_PS_OF_OPT.s_admission_process_type%TYPE;
164 v_cop_cir_rec_found BOOLEAN DEFAULT FALSE;
165 v_apapc_rec_found BOOLEAN DEFAULT FALSE;
166 v_crv_valid BOOLEAN DEFAULT FALSE;
167 v_adm_perd_valid BOOLEAN DEFAULT FALSE;
168 v_adm_cat_match BOOLEAN DEFAULT FALSE;
169 v_crv_valid_cnt NUMBER DEFAULT 0;
170 v_message_name VARCHAR2(30);
171 CURSOR c_cop_cir (
172 cp_adm_cal_type IGS_CA_INST.cal_type%TYPE,
173 cp_adm_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
174 cp_acad_cal_type IGS_CA_INST.cal_type%TYPE,
175 cp_course_cd IGS_PS_OFR.course_cd%TYPE,
176 cp_version_number IGS_PS_OFR.version_number%TYPE) IS
177 SELECT cop.course_cd,
178 cop.version_number,
179 cop.cal_type,
180 cop.location_cd,
181 cop.attendance_mode,
182 cop.attendance_type
183 FROM IGS_PS_OFR_PAT cop,
184 IGS_CA_INST_REL cir
185 WHERE cir.sup_cal_type = cp_acad_cal_type AND
186 cir.sub_cal_type = cp_adm_cal_type AND
187 cir.sub_ci_sequence_number = cp_adm_ci_sequence_number AND
188 cop.course_cd = cp_course_cd AND
189 cop.version_number = cp_version_number AND
190 cop.offered_ind = 'Y' AND
191 cop.entry_point_ind = 'Y' AND
192 cop.cal_type = cir.sup_cal_type AND
193 cop.ci_sequence_number = cir.sup_ci_sequence_number;
194 CURSOR c_apapc (
195 cp_adm_cal_type IGS_CA_INST.cal_type%TYPE,
196 cp_adm_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
197 cp_admission_cat IGS_AD_PRD_PS_OF_OPT.admission_cat%TYPE) IS
198 SELECT apapc.s_admission_process_type
199 FROM IGS_AD_PRD_AD_PRC_CA apapc
200 WHERE apapc.adm_cal_type = cp_adm_cal_type AND
201 apapc.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
202 apapc.admission_cat = cp_admission_cat AND
203 apapc.closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
204 BEGIN
205 p_message_name := Null;
206 -- Check if at least one course offering pattern exists for the admission
207 -- period course offering
208 FOR v_cop_cir_rec IN c_cop_cir(
209 p_adm_cal_type,
210 p_adm_ci_sequence_number,
211 p_acad_cal_type,
212 p_course_cd,
213 p_version_number) LOOP
214 v_cop_cir_rec_found := TRUE;
215 -- Determine if course offering is valid for the admission category
216 IF(IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_adm_cat(
217 v_cop_cir_rec.course_cd,
218 v_cop_cir_rec.version_number,
219 v_cop_cir_rec.cal_type,
220 v_cop_cir_rec.location_cd,
221 v_cop_cir_rec.attendance_mode,
222 v_cop_cir_rec.attendance_type,
223 p_admission_cat,
224 v_message_name) = TRUE) THEN
225 v_adm_cat_match := TRUE;
226 IF(p_s_admission_process_type IS NULL) THEN
227 FOR v_apapc_rec IN c_apapc(
228 p_adm_cal_type,
229 p_adm_ci_sequence_number,
230 p_admission_cat) LOOP
231 v_apapc_rec_found := TRUE;
232 v_s_adm_proc_type := v_apapc_rec.s_admission_process_type;
233 -- Validate course offering
234 -- Determine if course version is valid
235 IF(IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_crv(
236 v_cop_cir_rec.course_cd,
237 v_cop_cir_rec.version_number,
238 v_s_adm_proc_type,
239 'N', -- this is not offer processing
240 v_message_name) = TRUE) THEN
241 -- Valid course version is found
242 v_crv_valid := TRUE;
243 -- Determine if course offering is valid for the admission period
244 -- course offering option restriction
245 IF(IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_admperd(
246 p_adm_cal_type,
247 p_adm_ci_sequence_number,
248 p_admission_cat,
249 v_s_adm_proc_type,
250 v_cop_cir_rec.course_cd,
251 v_cop_cir_rec.version_number,
252 v_cop_cir_rec.cal_type,
253 v_cop_cir_rec.location_cd,
254 v_cop_cir_rec.attendance_mode,
255 v_cop_cir_rec.attendance_type,
256 v_message_name) = TRUE) THEN
257 -- at least one admission period course offering option
258 -- restriction
259 v_adm_perd_valid := TRUE;
260 END IF;
261 END IF;
262 IF(v_crv_valid = TRUE AND
263 v_adm_perd_valid = TRUE) THEN
264 RETURN TRUE;
265 ELSIF(v_crv_valid = TRUE AND v_adm_perd_valid = FALSE) THEN
266 v_crv_valid_cnt := v_crv_valid_cnt + 1;
267 v_crv_valid := FALSE;
268 v_adm_perd_valid := FALSE;
269 END IF;
270 END LOOP;
271 IF(v_apapc_rec_found = FALSE) THEN
272 -- This is an error that will be handled outside this Module
273 -- ADMP_VAL_APCOOD_INS will be called before this module so
274 -- this condition should if happens should be trapped before
275 RETURN TRUE;
276 END IF;
277 ELSE
278 v_s_adm_proc_type := p_s_admission_process_type;
279 -- Validate course offering
280 -- Determine if course version is valid
281 IF(IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_crv(
282 v_cop_cir_rec.course_cd,
283 v_cop_cir_rec.version_number,
284 v_s_adm_proc_type,
285 'N', -- this is not offer processing
286 v_message_name) = TRUE) THEN
287 -- Valid course version is found
288 v_crv_valid := TRUE;
289 -- Determine if course offering is valid for the admission period
290 -- course offering option restriction
291 IF(IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_admperd(
292 p_adm_cal_type,
293 p_adm_ci_sequence_number,
294 p_admission_cat,
298 v_cop_cir_rec.cal_type,
295 v_s_adm_proc_type,
296 v_cop_cir_rec.course_cd,
297 v_cop_cir_rec.version_number,
299 v_cop_cir_rec.location_cd,
300 v_cop_cir_rec.attendance_mode,
301 v_cop_cir_rec.attendance_type,
302 v_message_name) = TRUE) THEN
303 -- at least one admission period course offering option
304 -- restriction
305 v_adm_perd_valid := TRUE;
306 END IF;
307 END IF;
308 IF(v_crv_valid = TRUE AND
309 v_adm_perd_valid = TRUE) THEN
310 RETURN TRUE;
311 ELSIF(v_crv_valid = TRUE AND v_adm_perd_valid = FALSE) THEN
312 v_crv_valid_cnt := v_crv_valid_cnt + 1;
313 v_crv_valid := FALSE;
314 v_adm_perd_valid := FALSE;
315 END IF;
316 END IF;
317 END IF;
318 END LOOP;
319 -- No IGS_PS_COURSE offering records
320 IF(v_cop_cir_rec_found = FALSE) THEN
321 p_message_name := 'IGS_AD_NO_PRGOFOP_ENTRYPOINT';
322 RETURN FALSE;
323 END IF;
324 -- No admission category matches
325 IF(v_adm_cat_match = FALSE) THEN
326 p_message_name := 'IGS_AD_NONE_PRGOFOP_COMPERIOD';
327 RETURN FALSE;
328 END IF;
329 -- No IGS_PS_COURSE version is valid
330 IF(v_crv_valid_cnt = 0) THEN
331 p_message_name := 'IGS_AD_PRGVER_OFR_COMPRD';
332 RETURN FALSE;
333 ELSE
334 p_message_name := 'IGS_AD_NOPOP_COMMENCE_PRD';
335 RETURN FALSE;
336 END IF;
337 END;
338 EXCEPTION
339 WHEN OTHERS THEN
340 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
341 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_apcood_co');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END admp_val_apcood_co;
345 --
346 -- Validate the adm period course off option date optional components.
347 FUNCTION admp_val_apcood_link(
348 p_adm_cal_type IN VARCHAR2 ,
349 p_adm_ci_sequence_number IN NUMBER ,
350 p_admission_cat IN VARCHAR2 ,
351 p_dt_alias IN VARCHAR2 ,
352 p_dai_sequence_number IN NUMBER ,
353 p_apcood_sequence_number IN NUMBER ,
354 p_s_admission_process_type IN VARCHAR2 ,
355 p_course_cd IN VARCHAR2 ,
356 p_version_number IN NUMBER ,
357 p_acad_cal_type IN VARCHAR2 ,
358 p_location_cd IN VARCHAR2 ,
359 p_attendance_mode IN VARCHAR2 ,
360 p_attendance_type IN VARCHAR2 ,
361 p_message_name OUT NOCOPY VARCHAR2 )
362 RETURN BOOLEAN IS
363 gv_other_detail VARCHAR2(255);
364 BEGIN -- admp_val_apcood_link
365 -- This module will validate that admission period date links do not clash
366 -- with existing admission period date links.
367 -- This module must ensure that this table will result in one and only one
368 -- date match for a course offering pattern in an admission period for a
369 -- specified admission category and system admission process type.
370 DECLARE
371 CURSOR c_apcood IS
372 SELECT 'x'
373 FROM IGS_AD_PECRS_OFOP_DT
374 WHERE adm_cal_type = p_adm_cal_type AND
375 adm_ci_sequence_number = p_adm_ci_sequence_number AND
376 admission_cat = p_admission_cat AND
377 dt_alias = p_dt_alias AND
378 (dai_sequence_number <> p_dai_sequence_number OR
379 sequence_number <> p_apcood_sequence_number);
380 CURSOR c_apcood2 IS
381 SELECT 'x'
382 FROM IGS_AD_PECRS_OFOP_DT
383 WHERE adm_cal_type = p_adm_cal_type AND
387 (sequence_number <> p_apcood_sequence_number) AND
384 adm_ci_sequence_number = p_adm_ci_sequence_number AND
385 admission_cat = p_admission_cat AND
386 dt_alias = p_dt_alias AND
388 NVL(s_admission_process_type, 'NULL')
389 = NVL(p_s_admission_process_type, 'NULL') AND
390 NVL(course_cd, 'NULL') = NVL(p_course_cd, 'NULL') AND
391 NVL(version_number, 0) = NVL(p_version_number, 0) AND
392 NVL(acad_cal_type, 'NULL') = NVL(p_acad_cal_type, 'NULL') AND
393 NVL(location_cd, 'NULL') = NVL(p_location_cd, 'NULL') AND
394 NVL(attendance_mode, 'NULL') = NVL(p_attendance_mode, 'NULL') AND
395 NVL(attendance_type, 'NULL') = NVL(p_attendance_type, 'NULL');
396 CURSOR c_apcood_rec (
397 cp_field_name VARCHAR2)IS
398 SELECT s_admission_process_type,
399 course_cd,
400 version_number,
401 acad_cal_type,
402 location_cd,
403 attendance_mode,
404 attendance_type
405 FROM IGS_AD_PECRS_OFOP_DT
406 WHERE adm_cal_type = p_adm_cal_type AND
407 adm_ci_sequence_number = p_adm_ci_sequence_number AND
408 admission_cat = p_admission_cat AND
409 dt_alias = p_dt_alias AND
410 (dai_sequence_number <> p_dai_sequence_number OR
411 sequence_number <> p_apcood_sequence_number) AND
412 DECODE(cp_field_name,
413 's_admission_process_type', S_ADMISSION_PROCESS_TYPE,
414 'course_cd', COURSE_CD,
415 'location_cd', LOCATION_CD,
416 'attendance_type', attendance_type,
417 'IGS_EN_ATD_MODE', attendance_mode, NULL) IS NULL;
418 v_apcood_exists VARCHAR2(1);
419 v_field_name VARCHAR2(20) := NULL;
420 v_null_ind BOOLEAN := FALSE;
421 v_message_name varchar2(30);
422 FUNCTION admpl_val_check_conflicts (
423 p_new_s_admission_process_type
424 IGS_AD_PECRS_OFOP_DT.s_admission_process_type%TYPE,
425 p_new_course_cd IGS_AD_PECRS_OFOP_DT.course_cd%TYPE,
426 p_new_acad_cal_type IGS_AD_PECRS_OFOP_DT.acad_cal_type%TYPE,
427 p_new_location_cd IGS_AD_PECRS_OFOP_DT.location_cd%TYPE,
428 p_new_attendance_mode IGS_AD_PECRS_OFOP_DT.attendance_mode%TYPE,
429 p_new_attendance_type IGS_AD_PECRS_OFOP_DT.attendance_type%TYPE,
430 p_new_version_number IGS_AD_PECRS_OFOP_DT.version_number%TYPE)
431 RETURN BOOLEAN
432 IS
433 BEGIN
434 -- IF any of the components do not match, THEN everything is OK,
435 -- continue processing
436 -- * First level conflict
437 -- A first level conflict is when a record already exists with one
438 -- of the optional components having a specific value, and the same
439 -- component in the record being validated is null
440 -- (which equates to everything).
441 -- Allowing this would result in two dates being matched.
442 IF (p_s_admission_process_type IS NOT NULL AND
443 p_new_s_admission_process_type IS NOT NULL AND
444 (p_s_admission_process_type <>
445 p_new_s_admission_process_type)) OR
446 (p_course_cd IS NOT NULL AND
447 p_new_course_cd IS NOT NULL AND
448 (p_course_cd <> p_new_course_cd OR
449 p_version_number <> p_new_version_number OR
450 p_acad_cal_type <> p_new_acad_cal_type)) OR
451 (p_location_cd IS NOT NULL AND
452 p_new_location_cd IS NOT NULL AND
453 (p_location_cd <> p_new_location_cd)) OR
454 (p_attendance_mode IS NOT NULL AND
455 p_new_attendance_mode IS NOT NULL AND
456 (p_attendance_mode <> p_new_attendance_mode)) OR
457 (p_attendance_type IS NOT NULL AND
458 p_new_attendance_type IS NOT NULL AND
459 (p_attendance_type <> p_new_attendance_type)) THEN
460 -- There is no conflict, continue with next record
461 NULL;
462 ELSE
463 IF (p_s_admission_process_type IS NULL AND
464 p_new_s_admission_process_type IS NOT NULL) OR
465 (p_course_cd IS NULL AND
466 p_new_course_cd IS NOT NULL) OR
467 (p_location_cd IS NULL AND
468 p_new_location_cd IS NOT NULL) OR
469 (p_attendance_mode IS NULL AND
470 p_new_attendance_mode IS NOT NULL) OR
471 (p_attendance_type IS NULL AND
472 p_new_attendance_type IS NOT NULL) THEN
473 -- * Second level conflict
474 IF NOT IGS_AD_VAL_APCOOD.admp_val_apcood_lnk2(
475 p_adm_cal_type,
476 p_adm_ci_sequence_number,
477 p_acad_cal_type,
478 p_admission_cat,
479 p_dt_alias,
480 p_dai_sequence_number,
481 p_apcood_sequence_number,
482 p_s_admission_process_type,
483 p_course_cd,
484 p_version_number,
485 p_location_cd,
486 p_attendance_mode,
487 p_attendance_type,
488 p_new_s_admission_process_type,
489 p_new_course_cd,
490 p_new_version_number,
491 p_new_location_cd,
492 p_new_attendance_mode,
496 END IF;
493 p_new_attendance_type,
494 v_message_name) THEN
495 RETURN FALSE;
497 END IF;
498 END IF;
499 RETURN TRUE;
500 EXCEPTION
501 WHEN OTHERS THEN
502 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
503 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_check_conflicts');
504 IGS_GE_MSG_STACK.ADD;
505 App_Exception.Raise_Exception;
506 END admpl_val_check_conflicts;
507 BEGIN
508 -- Set the default message number
509 p_message_name := Null;
510 -- Check if no records already exist for the particular date alias
511 OPEN c_apcood;
512 FETCH c_apcood INTO v_apcood_exists;
513 IF c_apcood%NOTFOUND THEN
514 -- Record can be inserted, it is the first
515 CLOSE c_apcood;
516 RETURN TRUE;
517 END IF;
518 CLOSE c_apcood;
519 -- Check if this record already exists
520 OPEN c_apcood2;
521 FETCH c_apcood2 INTO v_apcood_exists;
522 IF c_apcood2%FOUND THEN
523 -- This record already exists, do not create a duplicate
524 CLOSE c_apcood2;
525 p_message_name := 'IGS_AD_ADMPRD_DTALIAS_EXISTS';
526 RETURN FALSE;
527 END IF;
528 CLOSE c_apcood2;
529 IF p_s_admission_process_type IS NOT NULL THEN
530 FOR v_apcood_rec IN c_apcood_rec(
531 's_admission_process_type') LOOP
532 IF NOT admpl_val_check_conflicts (
533 v_apcood_rec.s_admission_process_type,
534 v_apcood_rec.course_cd,
535 v_apcood_rec.acad_cal_type,
536 v_apcood_rec.location_cd,
537 v_apcood_rec.attendance_mode,
538 v_apcood_rec.attendance_type,
539 v_apcood_rec.version_number) THEN
540 v_message_name := 'IGS_AD_ADMPRDDT_CONFLICT_SYS';
541 EXIT;
542 END IF;
543 END LOOP;
544 END IF;
545 IF v_message_name <> Null THEN
546 p_message_name := v_message_name;
547 RETURN FALSE;
548 END IF;
549 -- Check course code component
550 IF p_course_cd IS NOT NULL THEN
551 FOR v_apcood_rec IN c_apcood_rec(
552 'course_cd') LOOP
553 IF NOT admpl_val_check_conflicts (
554 v_apcood_rec.s_admission_process_type,
555 v_apcood_rec.course_cd,
556 v_apcood_rec.acad_cal_type,
557 v_apcood_rec.location_cd,
558 v_apcood_rec.attendance_mode,
559 v_apcood_rec.attendance_type,
560 v_apcood_rec.version_number) THEN
561 v_message_name := 'IGS_AD_ADMPRDDT_CONFLICT_PRGC';
562 EXIT;
563 END IF;
564 END LOOP;
565 END IF;
566 IF v_message_name <> Null THEN
567 p_message_name := v_message_name;
568 RETURN FALSE;
569 END IF;
570 -- Check location code component
571 IF p_location_cd IS NOT NULL THEN
572 FOR v_apcood_rec IN c_apcood_rec(
573 'location_cd') LOOP
574 IF NOT admpl_val_check_conflicts (
575 v_apcood_rec.s_admission_process_type,
576 v_apcood_rec.course_cd,
577 v_apcood_rec.acad_cal_type,
578 v_apcood_rec.location_cd,
579 v_apcood_rec.attendance_mode,
580 v_apcood_rec.attendance_type,
581 v_apcood_rec.version_number) THEN
582 v_message_name := 'IGS_AD_ADMPRDDT_CONFLICT_LOCD';
583 EXIT;
584 END IF;
585 END LOOP;
586 END IF;
587 IF v_message_name <> Null THEN
588 p_message_name := v_message_name;
589 RETURN FALSE;
590 END IF;
591 -- Check attendance mode component
592 IF p_attendance_mode IS NOT NULL THEN
593 FOR v_apcood_rec IN c_apcood_rec(
594 'attendance_mode') LOOP
595 IF NOT admpl_val_check_conflicts (
596 v_apcood_rec.s_admission_process_type,
597 v_apcood_rec.course_cd,
598 v_apcood_rec.acad_cal_type,
599 v_apcood_rec.location_cd,
600 v_apcood_rec.attendance_mode,
601 v_apcood_rec.attendance_type,
602 v_apcood_rec.version_number) THEN
603 v_message_name := 'IGS_AD_ADMPRDDT_CONFLICT_ATMO';
604 EXIT;
605 END IF;
606 END LOOP;
607 END IF;
608 IF v_message_name <> Null THEN
609 p_message_name := v_message_name;
613 IF p_attendance_type IS NOT NULL THEN
610 RETURN FALSE;
611 END IF;
612 -- Check attendance type component
614 FOR v_apcood_rec IN c_apcood_rec(
615 'attendance_type') LOOP
616 IF NOT admpl_val_check_conflicts (
617 v_apcood_rec.s_admission_process_type,
618 v_apcood_rec.course_cd,
619 v_apcood_rec.acad_cal_type,
620 v_apcood_rec.location_cd,
621 v_apcood_rec.attendance_mode,
622 v_apcood_rec.attendance_type,
623 v_apcood_rec.version_number) THEN
624 v_message_name := 'IGS_AD_ADMPRDDT_CONFLICT_ATTY';
625 EXIT;
626 END IF;
627 END LOOP;
628 END IF;
629 IF v_message_name <> Null THEN
630 p_message_name := v_message_name;
631 RETURN FALSE;
632 END IF;
633 RETURN TRUE;
634 END;
635 EXCEPTION
636 WHEN OTHERS THEN
637 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
638 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_apcood_link');
639 IGS_GE_MSG_STACK.ADD;
640 App_Exception.Raise_Exception;
641 END admp_val_apcood_link;
642 --
643 -- Validate the adm period course off option date optional components.
644 FUNCTION admp_val_apcood_lnk2(
645 p_adm_cal_type IN VARCHAR2 ,
646 p_adm_ci_sequence_number IN NUMBER ,
647 p_acad_cal_type IN VARCHAR2 ,
648 p_admission_cat IN VARCHAR2 ,
649 p_dt_alias IN VARCHAR2 ,
650 p_dai_sequence_number IN NUMBER ,
651 p_apcood_sequence_number IN NUMBER ,
652 p_s_admission_process_type IN VARCHAR2 ,
653 p_course_cd IN VARCHAR2 ,
654 p_version_number IN NUMBER ,
655 p_location_cd IN VARCHAR2 ,
656 p_attendance_mode IN VARCHAR2 ,
657 p_attendance_type IN VARCHAR2 ,
658 p_db_s_admission_process_type IN VARCHAR2 ,
659 p_db_course_cd IN VARCHAR2 ,
660 p_db_version_number IN NUMBER ,
661 p_db_location_cd IN VARCHAR2 ,
662 p_db_attendance_mode IN VARCHAR2 ,
663 p_db_attendance_type IN VARCHAR2 ,
664 p_message_name OUT NOCOPY VARCHAR2 )
665 RETURN BOOLEAN IS
666 gv_other_detail VARCHAR2(255);
667 BEGIN -- admp_val_apcood_lnk2
668 -- This module is called by ADMP_VAL_APCOOD_LINK which validates that
669 -- admission period date link does NOT clash with existing admission
670 -- period date links. This module validates a ?second level conflict?.
671 -- It checks for the existence of a record with the combination of
672 -- components of the record being validated, AND an existing record
673 -- that has been identified as having a potential conflict. If the
674 -- combination record exists, then there is no conflict AND the record
675 -- can be inserted, updated or deleted.
676 -- Second level conflict
677 -- A second level conflict is when a first level conflict is encountered
678 -- AND a record does NOT already exist to compensate for the first level
679 -- conflict. This is determined by combining the specified components of
680 -- the record being validated AND the record found with a first level
681 -- conflict, AND checking for the existence of a record with this combination.
682 DECLARE
683 v_s_admission_process_type
684 IGS_AD_PECRS_OFOP_DT.s_admission_process_type%TYPE;
685 v_course_cd IGS_AD_PECRS_OFOP_DT.course_cd%TYPE;
686 v_version_number IGS_AD_PECRS_OFOP_DT.version_number%TYPE;
687 v_location_cd IGS_AD_PECRS_OFOP_DT.location_cd%TYPE;
688 v_attendance_mode IGS_AD_PECRS_OFOP_DT.attendance_mode %TYPE;
689 v_attendance_type IGS_AD_PECRS_OFOP_DT.attendance_type%TYPE;
690 CURSOR c_apcood IS
691 SELECT 'x'
692 FROM IGS_AD_PECRS_OFOP_DT
693 WHERE adm_cal_type = p_adm_cal_type AND
694 adm_ci_sequence_number = p_adm_ci_sequence_number AND
695 admission_cat = p_admission_cat AND
696 dt_alias = p_dt_alias AND
697 (dai_sequence_number <> p_dai_sequence_number OR
698 sequence_number <> p_apcood_sequence_number) AND
699 acad_cal_type = p_acad_cal_type AND
700 (v_s_admission_process_type IS NULL OR
701 s_admission_process_type = v_s_admission_process_type) AND
702 (v_course_cd IS NULL OR
703 course_cd = v_course_cd) AND
704 (v_version_number IS NULL OR
705 version_number = v_version_number) AND
706 (v_location_cd IS NULL OR
707 location_cd = v_location_cd) AND
708 (v_attendance_mode IS NULL OR
709 attendance_mode = v_attendance_mode) AND
710 (v_attendance_type IS NULL OR
711 attendance_type = v_attendance_type);
712 v_exit BOOLEAN := FALSE;
713 v_apcood_exists VARCHAR2(1);
714 BEGIN
715 -- Set the default message number
716 p_message_name := Null;
717 -- Validate parameters
718 IF p_adm_cal_type IS NULL OR
719 p_adm_ci_sequence_number IS NULL OR
720 p_admission_cat IS NULL OR
721 p_dt_alias IS NULL THEN
722 p_message_name := 'IGS_AD_ADMPRD_POO_INVALID';
723 RETURN FALSE;
724 END IF;
725 -- Initialise local variables
726 v_s_admission_process_type :=
727 NVL(p_db_s_admission_process_type, p_s_admission_process_type);
728 v_course_cd := NVL(p_db_course_cd, p_course_cd);
729 v_version_number := NVL(p_db_version_number, p_version_number);
730 v_location_cd := NVL(p_db_location_cd, p_location_cd);
731 v_attendance_mode := NVL(p_db_attendance_mode, p_attendance_mode);
735 IF c_apcood%NOTFOUND THEN
732 v_attendance_type := NVL(p_db_attendance_type, p_attendance_type);
733 OPEN c_apcood;
734 FETCH c_apcood INTO v_apcood_exists;
736 CLOSE c_apcood;
737 p_message_name := 'IGS_AD_ADMPRD_POO_CONFLICT';
738 RETURN FALSE;
739 END IF;
740 CLOSE c_apcood;
741 RETURN TRUE;
742 END;
743 EXCEPTION
744 WHEN OTHERS THEN
745 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
746 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_apcood_lnk2');
747 IGS_GE_MSG_STACK.ADD;
748 App_Exception.Raise_Exception;
749 END admp_val_apcood_lnk2;
750 --
751 -- Validate insert of adm period course off option date
752 FUNCTION admp_val_apcood_ins(
753 p_adm_cal_type IN VARCHAR2 ,
754 p_adm_ci_sequence_number IN NUMBER ,
755 p_admission_cat IN VARCHAR2 ,
756 p_dt_alias IN VARCHAR2 ,
757 p_dai_sequence_number IN NUMBER ,
758 p_apcood_sequence_number IN NUMBER ,
759 p_message_name OUT NOCOPY VARCHAR2 )
760 RETURN BOOLEAN IS
761 gv_other_detail VARCHAR2(255);
762 BEGIN --admp_val_apcood_ins
763 --This module validates the insert of admission
764 --period course offering option date.
765 DECLARE
766 v_apapc_exists VARCHAR2(1);
767 v_apcood_exists VARCHAR2(1);
768 v_adm_perd_dt_exists BOOLEAN DEFAULT FALSE;
769 CURSOR c_apapc IS
770 SELECT 'X'
771 FROM IGS_AD_PRD_AD_PRC_CA
772 WHERE adm_cal_type = p_adm_cal_type AND
773 adm_ci_sequence_number = p_adm_ci_sequence_number AND
774 admission_cat = p_admission_cat AND
775 closed_ind = 'N'; --added the closed indicator for bug# 2380108 (rghosh)
776 CURSOR c_dai IS
777 SELECT sequence_number
778 FROM IGS_CA_DA_INST
779 WHERE cal_type = p_adm_cal_type AND
780 ci_sequence_number = p_adm_ci_sequence_number AND
781 dt_alias = p_dt_alias;
782 CURSOR c_apcood (
783 cp_sequence_number IGS_AD_PECRS_OFOP_DT.dai_sequence_number%TYPE) IS
784 SELECT 'x'
785 FROM IGS_AD_PECRS_OFOP_DT
786 WHERE adm_cal_type = p_adm_cal_type AND
787 adm_ci_sequence_number = p_adm_ci_sequence_number AND
788 dt_alias = p_dt_alias AND
789 dai_sequence_number = cp_sequence_number AND
790 (sequence_number <> p_apcood_sequence_number);
791 BEGIN
792 --Set the default message number
793 p_message_name := Null;
794 --Admission period course offering option date overrides
795 --cannot be inserted if no IGS_AD_PRD_AD_PRC_CA exists
796 OPEN c_apapc;
797 FETCH c_apapc INTO v_apapc_exists;
798 IF (c_apapc%NOTFOUND) THEN
799 CLOSE c_apapc;
800 p_message_name :='IGS_AD_ADMPRD_POODT_CANINS';
801 RETURN FALSE;
802 END IF;
803 CLOSE c_apapc;
804 -- Admission Period course offering option date override cannot
805 -- be inserted if this will NOT leave a date alias instance for
806 -- the date alias with no overrides attached.
807 FOR v_dai_rec IN c_dai LOOP
808 IF v_dai_rec.sequence_number <> NVL(p_dai_sequence_number, 0) THEN
809 OPEN c_apcood(
810 v_dai_rec.sequence_number);
811 FETCH c_apcood INTO v_apcood_exists;
812 IF c_apcood%NOTFOUND THEN
813 CLOSE c_apcood;
814 v_adm_perd_dt_exists := TRUE;
815 EXIT;
816 END IF;
817 CLOSE c_apcood;
818 END IF;
819 END LOOP;
820 IF NOT v_adm_perd_dt_exists THEN
821 p_message_name := 'IGS_AD_INVALID_OVERRIDE_DATE';
822 RETURN FALSE;
823 END IF;
824 RETURN TRUE;
828 CLOSE c_apapc;
825 EXCEPTION
826 WHEN OTHERS THEN
827 IF c_apapc%ISOPEN THEN
829 END IF;
830 IF c_dai%ISOPEN THEN
831 CLOSE c_dai;
832 END IF;
833 IF c_apcood%ISOPEN THEN
834 CLOSE c_apcood;
835 END IF;
836 RAISE;
837 END;
838 EXCEPTION
839 WHEN OTHERS THEN
840 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
841 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOOD.admp_val_apcood_ins');
842 IGS_GE_MSG_STACK.ADD;
843 App_Exception.Raise_Exception;
844 END admp_val_apcood_ins;
845 --
846 -- Validate the adm period course off option date date alias
847 FUNCTION admp_val_apcood_da(
848 p_dt_alias IN VARCHAR2 ,
849 p_message_name OUT NOCOPY VARCHAR2 )
850 RETURN BOOLEAN IS
851 gv_other_detail VARCHAR2(255);
852 BEGIN -- admp_val_apcood_da
853 -- This module validates the date alias is allowed for the admission
854 -- period date restriction.
855 DECLARE
856 CURSOR c_sacc IS
857 SELECT adm_appl_offer_resp_dt_alias,
858 adm_appl_due_dt_alias,
859 adm_appl_final_dt_alias
860 FROM IGS_AD_CAL_CONF
861 WHERE s_control_num = 1;
862 v_sacc_rec c_sacc%ROWTYPE;
863 BEGIN
864 -- Set the default message number
865 p_message_name := Null;
866 -- The admission period date restrictions table only applies to selected dates
867 -- as defined in the system admission calendar configuration table.
868 OPEN c_sacc;
869 FETCH c_sacc INTO v_sacc_rec;
870 IF c_sacc%NOTFOUND THEN
871 CLOSE c_sacc;
872 p_message_name :='IGS_AD_SYSCAL_CONFIG_NOT_DTMN';
873 RETURN FALSE;
874 ELSIF (v_sacc_rec.adm_appl_offer_resp_dt_alias <> p_dt_alias AND
875 v_sacc_rec.adm_appl_due_dt_alias <> p_dt_alias AND
876 v_sacc_rec.adm_appl_final_dt_alias <> p_dt_alias) THEN
877 CLOSE c_sacc;
878 p_message_name := 'IGS_AD_DTALIAS_ADMPRD_POO';
879 RETURN FALSE;
880 END IF;
881 CLOSE c_sacc;
882 RETURN TRUE;
883 END;
884
885 END admp_val_apcood_da;
886
887
888 END IGS_AD_VAL_APCOOD;
[Home] [Help]