1 PACKAGE BODY IGS_AD_VAL_APCOO AS
2 /* $Header: IGSAD40B.pls 115.7 2002/11/28 21:31:44 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --avenkatr 29-AUG-2001 Bug Id : 1956374. Added Pragma to function "crsp_val_att_closed"
7 -------------------------------------------------------------------------------------------
8
9 -- Validate admission period course offering option course offering.
10
11 FUNCTION admp_val_apcoo_co(
12 p_course_cd IN VARCHAR2 ,
13 p_crv_version_number IN NUMBER ,
14 p_acad_cal_type IN VARCHAR2 ,
15 p_admission_cat IN VARCHAR2 ,
16 p_s_admission_process_type IN VARCHAR2 ,
17 p_adm_cal_type IN VARCHAR2 ,
18 p_adm_ci_sequence_number IN NUMBER ,
19 p_message_name OUT NOCOPY VARCHAR2 )
20 RETURN BOOLEAN IS
21 gv_other_detail VARCHAR2(255);
22 BEGIN
23 --admp_val_apcoo_co
24 -- Routine to verify that the course offering is
25 -- valid for the admission period.
26 DECLARE
27 v_record_found BOOLEAN DEFAULT FALSE;
28 v_adm_cat_valid BOOLEAN DEFAULT FALSE;
29 v_crv_valid BOOLEAN DEFAULT FALSE;
30 v_message_name VARCHAR2(30);
31 CURSOR c_cop IS
32 SELECT cop.course_cd,
33 cop.version_number,
34 cop.cal_type,
35 cop.location_cd,
36 cop.attendance_mode,
37 cop.attendance_type
38 FROM IGS_CA_INST_REL cir,
39 IGS_PS_OFR_PAT cop
40 WHERE cir.sub_cal_type = p_adm_cal_type AND
41 cir.sub_ci_sequence_number = p_adm_ci_sequence_number AND
42 cir.sup_cal_type = p_acad_cal_type AND
43 cop.course_cd = p_course_cd AND
44 cop.version_number = p_crv_version_number AND
45 cop.offered_ind = 'Y' AND
46 cop.entry_point_ind = 'Y' AND
47 cop.cal_type = cir.sup_cal_type AND
48 cop.ci_sequence_number = cir.sup_ci_sequence_number;
49 r_cop_rec c_cop%ROWTYPE;
50 BEGIN
51 --- Set the default message number
52 p_message_name := Null;
53 -- Check if at least one course offering pattern exists
54 -- for the admission period.
55 FOR v_cop_rec IN c_cop LOOP
56 v_record_found := TRUE;
57 -- For the first record determine if the course version is valid.
58 IF (c_cop%ROWCOUNT = 1) THEN
59 IF (IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_crv(
60 v_cop_rec.course_cd,
61 v_cop_rec.version_number,
62 p_s_admission_process_type,
63 'N',
64 v_message_name) = TRUE) THEN
65 v_crv_valid := TRUE;
66 ELSE
67 EXIT;
68 END IF;
69 END IF;
70 -- For each record determine if course offering is valid for
71 -- the admission category
72 IF (IGS_AD_VAL_CRS_ADMPERD.admp_val_coo_adm_cat(
73 v_cop_rec.course_cd,
74 v_cop_rec.version_number,
75 v_cop_rec.cal_type,
76 v_cop_rec.location_cd,
77 v_cop_rec.attendance_mode,
78 v_cop_rec.attendance_type,
79 p_admission_cat,
80 v_message_name) = TRUE) THEN
81 v_adm_cat_valid := TRUE;
82 EXIT;
83 END IF;
84 END LOOP;
85 IF (v_record_found = FALSE) THEN
86 -- There are no course offering patterns being offered for
87 -- this IGS_PS_COURSE version in this commencement period.
88 p_message_name := 'IGS_AD_NO_PRGOFOP_ENTRYPOINT';
89 Return FALSE;
90 END IF;
91 IF (v_crv_valid = FALSE) THEN
92 -- This course version is being offered in the commencement period but
93 -- is either inactive, or is not valid for the Admission Period Admission
94 -- Process Type.
95 p_message_name := 'IGS_AD_PRGVER_OFR_COMPRD';
96 RETURN FALSE;
97 END IF;
98 IF (v_adm_cat_valid = FALSE) THEN
99 -- None of the course offering patterns being offered for this course
100 -- version in this commencement period have mappings to the Admission
101 -- Period Admission Category.
102 p_message_name := 'IGS_AD_NONE_PRGOFOP_COMPERIOD';
103 RETURN FALSE;
104 END IF;
105 -- Return the no error
106 RETURN TRUE;
107 END;
108 EXCEPTION
109 WHEN OTHERS THEN
110 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
111 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.admp_val_apcoo_co');
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception;
114 END admp_val_apcoo_co;
115
116 -- Validate the attendance type closed indicator.
117 FUNCTION crsp_val_att_closed(
118 p_attendance_type IN VARCHAR2 ,
119 p_message_name OUT NOCOPY VARCHAR2 )
120 RETURN BOOLEAN IS
121 BEGIN
122 DECLARE
123 v_other_detail VARCHAR2(255);
124 v_closed_ind IGS_EN_ATD_TYPE.closed_ind%TYPE;
125 CURSOR c_ci IS
126 SELECT closed_ind
127 FROM IGS_EN_ATD_TYPE
128 WHERE attendance_type = p_attendance_type;
129 BEGIN
130 -- Validates attendance type closed indicator.
131 p_message_name := Null;
132 OPEN c_ci;
133 FETCH c_ci into v_closed_ind;
134 IF (c_ci%NOTFOUND) THEN
135 CLOSE c_ci;
136 p_message_name := Null;
137 RETURN TRUE;
138 END IF;
139 IF (v_closed_ind = 'Y') THEN
140 CLOSE c_ci;
141 p_message_name := 'IGS_PS_ATTEND_TYPE_CLOSED';
142 RETURN FALSE;
143 END IF;
144 CLOSE c_ci;
145 --- Return the default value
146 p_message_name := Null;
147 RETURN TRUE;
148 EXCEPTION
149 WHEN OTHERS THEN
150 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
151 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.crsp_val_att_closed');
152 -- IGS_GE_MSG_STACK.ADD;
153 -- App_Exception.Raise_Exception;
154 END;
155 END CRSP_VAL_ATT_CLOSED;
156 --
157
158 --
159 -- Validate admission period IGS_PS_COURSE offering option optional links.
160 FUNCTION admp_val_apcoo_links(
161 p_adm_cal_type IN VARCHAR2 ,
162 p_adm_ci_sequence_number IN NUMBER ,
163 p_admission_cat IN VARCHAR2 ,
164 p_s_admission_process_type IN VARCHAR2 ,
165 p_course_cd IN VARCHAR2 ,
166 p_version_number IN NUMBER ,
167 p_acad_cal_type IN VARCHAR2 ,
168 p_sequence_number IN NUMBER ,
169 p_location_cd IN VARCHAR2 ,
170 p_attendance_mode IN VARCHAR2 ,
171 p_attendance_type IN VARCHAR2 ,
172 p_message_name OUT NOCOPY VARCHAR2 )
173 RETURN BOOLEAN IS
174 gv_other_detail VARCHAR2(255);
175 BEGIN -- admp_val_apcoo_links
176 -- Validates that the links to the location, attendance_mode and
177 -- IGS_EN_ATD_TYPE tables when inserting or updating an admission period
178 -- IGS_PS_COURSE offering option record to avoid
179 -- conflicting or duplicate course offering option components.
180 DECLARE
181 v_message_name VARCHAR2(30);
182 CURSOR c_apcoo (
183 cp_adm_cal_type IGS_AD_PRD_PS_OF_OPT.adm_cal_type%TYPE,
184 cp_adm_ci_sequence_number
185 IGS_AD_PRD_PS_OF_OPT.adm_ci_sequence_number%TYPE,
186 cp_admission_cat IGS_AD_PRD_PS_OF_OPT.admission_cat%TYPE,
187 cp_s_admission_process_type
188 IGS_AD_PRD_PS_OF_OPT.s_admission_process_type%TYPE,
189 cp_course_cd IGS_AD_PRD_PS_OF_OPT.course_cd%TYPE,
190 cp_version_number IGS_AD_PRD_PS_OF_OPT.version_number%TYPE,
191 cp_acad_cal_type IGS_AD_PRD_PS_OF_OPT.acad_cal_type%TYPE,
192 cp_sequence_number IGS_AD_PRD_PS_OF_OPT.sequence_number%TYPE) IS
193 SELECT apcoo.location_cd,
194 apcoo.attendance_mode,
195 apcoo.attendance_type
196 FROM IGS_AD_PRD_PS_OF_OPT apcoo
197 WHERE apcoo.adm_cal_type = cp_adm_cal_type AND
198 apcoo.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
199 apcoo.admission_cat = cp_admission_cat AND
200 apcoo.s_admission_process_type = cp_s_admission_process_type AND
201 apcoo.course_cd = cp_course_cd AND
202 apcoo.version_number = cp_version_number AND
203 apcoo.acad_cal_type = cp_acad_cal_type AND
204 apcoo.sequence_number <> cp_sequence_number;
205 BEGIN
206 p_message_name := Null;
207 FOR v_apcoo_rec IN c_apcoo(
208 p_adm_cal_type,
209 p_adm_ci_sequence_number,
210 p_admission_cat,
211 p_s_admission_process_type,
212 p_course_cd,
213 p_version_number,
214 p_acad_cal_type,
215 p_sequence_number) LOOP
216 -- Validate parameter linkages against the selected record's linkages
217 IF(IGS_AD_VAL_APCOO.genp_val_optnl_coo(
218 p_location_cd,
219 p_attendance_mode,
220 p_attendance_type,
221 v_apcoo_rec.location_cd,
222 v_apcoo_rec.attendance_mode,
223 v_apcoo_rec.attendance_type,
224 v_message_name) = FALSE) THEN
225 IF(v_message_name = 'IGS_AS_SPECIFIED_LINK_CONFLIC') THEN
226 p_message_name := 'IGS_AD_LINKCONFLICT_ADMPRD';
227 ELSIF(v_message_name = 'IGS_GE_RECORD_ALREADY_EXISTS') THEN
228 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
229 ELSIF(v_message_name = 'IGS_GE_RECORD_ALREADY_EXISTS') THEN
230 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
231 ELSIF(v_message_name = 'IGS_AS_REC_INS_UPD_MORE_LINKS') THEN
232 p_message_name := 'IGS_AD_ADMPRD_PRGOFOP_INSUPD';
233 END IF;
234 RETURN FALSE;
235 END IF;
236 END LOOP;
237 RETURN TRUE;
238 END;
239 EXCEPTION
240 WHEN OTHERS THEN
241 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
242 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.admp_val_apcoo_links');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END admp_val_apcoo_links;
246 --
247 -- Insert admission period course offering options
248 FUNCTION admp_ins_dflt_apcoo(
249 p_adm_cal_type IN VARCHAR2 ,
250 p_adm_ci_sequence_number IN NUMBER ,
251 p_admission_cat IN VARCHAR2 ,
252 p_s_admission_process_type IN VARCHAR2 ,
253 p_message_name OUT NOCOPY VARCHAR2 )
254 RETURN BOOLEAN IS
255 gv_other_detail VARCHAR2(255);
256 BEGIN -- ADMP_INS_DFLT_APCOO
257 -- Routine to insert admission period course offering options.
258 -- This will be fired from the form when defining an admission
259 -- period for an admission category and process type.
260 DECLARE
261 CURSOR c_apcoo(
262 cp_adm_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,
263 cp_adm_ci_sequence_number IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE,
264 cp_admission_cat IGS_AD_PRD_AD_PRC_CA.admission_cat%TYPE,
265 cp_s_admission_process_type IGS_AD_PRD_AD_PRC_CA.s_admission_process_type%TYPE) IS
266 SELECT 'x'
267 FROM IGS_AD_PRD_PS_OF_OPT
268 WHERE adm_cal_type = cp_adm_cal_type AND
269 adm_ci_sequence_number = cp_adm_ci_sequence_number AND
270 admission_cat = cp_admission_cat AND
271 s_admission_process_type = cp_s_admission_process_type;
272 v_apc_exists c_apcoo%ROWTYPE;
273 CURSOR c_acov(
274 cp_adm_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,
275 cp_adm_ci_sequence_number IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE) IS
276 SELECT DISTINCT acov.course_cd,
277 acov.version_number,
278 acov.acad_cal_type
279 FROM IGS_PS_OFR_PAT_APCOO_V acov
280 WHERE acov.adm_cal_type = cp_adm_cal_type AND
281 acov.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
282 acov.admission_cat = p_admission_cat AND
283 acov.s_admission_process_type = p_s_admission_process_type AND
284 ( IGS_AD_GEN_013.ADMS_GET_COO_CRV(
285 acov.course_cd,
286 acov.version_number,
287 p_s_admission_process_type,
288 'N') = 'Y' );
289 v_acov_rec c_acov%ROWTYPE;
290 v_course_cd IGS_PS_OFR_PAT.course_cd%TYPE DEFAULT NULL;
291 v_version_number IGS_PS_OFR_PAT.version_number%TYPE DEFAULT NULL;
292 v_cal_type IGS_PS_OFR_PAT.cal_type%TYPE DEFAULT NULL;
293 v_rowid VARCHAR2(25);
294 v_sequence_number NUMBER(6);
295 BEGIN
296 -- Set the default message number
297 p_message_name := Null;
298 -- Check that an adm_perd-course_off_option does not already exist
299 OPEN c_apcoo(
300 p_adm_cal_type,
301 p_adm_ci_sequence_number,
302 p_admission_cat,
303 p_s_admission_process_type);
304 FETCH c_apcoo INTO v_apc_exists;
305 IF c_apcoo%FOUND THEN
306 CLOSE c_apcoo;
307 p_message_name := 'IGS_AD_CAN_ONLY_DFLT_PRG_OFOP';
308 RETURN FALSE;
309 END IF;
310 CLOSE c_apcoo;
311 FOR v_acov_rec IN c_acov(
312 p_adm_cal_type,
313 p_adm_ci_sequence_number) LOOP
314
315 select IGS_AD_PRD_PS_OF_OPT_SEQ_NUM_S.NEXTVAL into
316 v_sequence_number
317 from dual;
318
319 IGS_AD_PRD_PS_OF_OPT_PKG.INSERT_ROW(
320 X_ROWID => v_rowid,
321 X_ADM_CAL_TYPE => p_adm_cal_type,
322 X_ADM_CI_SEQUENCE_NUMBER => p_adm_ci_sequence_number,
323 X_ADMISSION_CAT => p_admission_cat,
324 X_S_ADMISSION_PROCESS_TYPE => p_s_admission_process_type,
325 X_COURSE_CD => v_acov_rec.course_cd,
326 X_VERSION_NUMBER => v_acov_rec.version_number,
327 X_ACAD_CAL_TYPE => v_acov_rec.acad_cal_type,
328 X_SEQUENCE_NUMBER => v_sequence_number,
329 X_LOCATION_CD => NULL,
330 X_ATTENDANCE_MODE => NULL,
331 X_ATTENDANCE_TYPE => NULL,
332 X_ROLLOVER_INCLUSION_IND => 'Y',
333 X_MODE => 'R');
334
335 END LOOP;
336 -- Return the default value
337 RETURN TRUE;
338 END;
339 EXCEPTION
340 WHEN OTHERS THEN
341 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
342 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.admp_ins_dflt_apcoo');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END ADMP_INS_DFLT_APCOO;
346 --
347 -- Validate course offering option optional links.
348 FUNCTION genp_val_optnl_coo(
349 p_new_location_cd IN VARCHAR2 ,
350 p_new_attendance_mode IN VARCHAR2 ,
351 p_new_attendance_type IN VARCHAR2 ,
352 p_db_location_cd IN VARCHAR2 ,
353 p_db_attendance_mode IN VARCHAR2 ,
354 p_db_attendance_type IN VARCHAR2 ,
355 p_message_name OUT NOCOPY VARCHAR2 )
356 RETURN BOOLEAN IS
357 gv_other_detail VARCHAR2(255);
358 BEGIN -- genp_val_optnl_coo
359 -- Common routine to validate the optional links to the location,
360 -- attendance mode and attendance type in tables where the
361 -- IGS_PS_OFR_OPT components are
362 -- made optional to cater for data volumes and the course code being the key
363 -- component. This routine detects conflicting or duplicate records.
364 DECLARE
365 v_message_name VARCHAR2(30) := Null;
366 BEGIN
367 -- Set the default message number
368 p_message_name := Null;
369 -- Validating 'N N N' parameters
370 IF (p_new_location_cd IS NULL) AND
371 (p_new_attendance_mode IS NULL) AND
372 (p_new_attendance_type IS NULL) THEN
373 IF (p_db_location_cd IS NULL AND
374 p_db_attendance_mode IS NULL AND
375 p_db_attendance_type IS NULL) THEN
376 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
377 ELSE
378 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
379 END IF;
380 END IF;
381 -- Validating 'N N Y' parameters
382 IF ((p_new_location_cd IS NULL)AND
383 (p_new_attendance_mode IS NULL) AND
384 (p_new_attendance_type IS NOT NULL)) THEN
385 IF (p_db_attendance_type IS NULL) THEN
386 IF (p_db_attendance_mode IS NULL) THEN
387 IF (p_db_location_cd IS NULL) THEN
388 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
389 ELSE
390 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
391 END IF;
392 ELSE
393 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
394 END IF;
395 ELSE
396 IF p_db_attendance_type = p_new_attendance_type THEN
397 IF (p_db_location_cd IS NULL AND
398 p_db_attendance_mode IS NULL) THEN
399 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
400 ELSE
401 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
402 END IF;
403 END IF;
404 END IF;
405 END IF;
406 -- Validating 'N Y N' parameters
407 IF (p_new_location_cd IS NULL)AND
408 (p_new_attendance_mode IS NOT NULL) AND
409 (p_new_attendance_type IS NULL) THEN
410 IF (p_db_attendance_mode IS NULL) THEN
411 IF (p_db_attendance_type IS NULL) THEN
412 IF (p_db_location_cd IS NULL) THEN
413 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
414 ELSE
415 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
416 END IF;
417 ELSE
418 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
419 END IF;
420 ELSE
421 IF (p_db_attendance_mode = p_new_attendance_mode) THEN
422 IF (p_db_location_cd IS NULL AND
423 p_db_attendance_type IS NULL) THEN
424 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
425 ELSE
426 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
427 END IF;
428 END IF;
429 END IF;
430 END IF;
431 -- Validating 'N Y Y' parameters
432 IF (p_new_location_cd IS NULL)AND
433 (p_new_attendance_mode IS NOT NULL) AND
434 (p_new_attendance_type IS NOT NULL) THEN
435 IF (p_db_attendance_mode IS NULL) THEN
436 IF (p_db_attendance_type IS NULL) THEN
437 IF (p_db_location_cd IS NULL) THEN
438 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
439 ELSE
440 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
441 END IF;
442 ELSE
443 IF (p_db_attendance_type = p_new_attendance_type) THEN
444 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
445 END IF;
446 END IF;
447 ELSE
448 IF (p_db_attendance_mode = p_new_attendance_mode) THEN
449 IF (p_db_attendance_type IS NULL) THEN
450 IF (p_db_location_cd IS NULL) THEN
451 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
452 ELSE
453 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
454 END IF;
455 ELSE
456 IF p_db_attendance_type = p_new_attendance_type THEN
457 IF p_db_location_cd IS NULL THEN
458 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
459 ELSE
460 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
461 END IF;
462 END IF;
463 END IF;
464 END IF;
465 END IF;
466 END IF;
467 -- Validating 'Y N N' parameters
468 IF (p_new_location_cd IS NOT NULL)AND
469 (p_new_attendance_mode IS NULL) AND
470 (p_new_attendance_type IS NULL) THEN
471 IF (p_db_location_cd IS NULL) THEN
472 IF (p_db_attendance_mode IS NULL AND
473 p_db_attendance_type IS NULL) THEN
474 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
475 ELSE
476 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
477 END IF;
478 ELSE
479 IF (p_db_location_cd = p_new_location_cd) THEN
480 IF (p_db_attendance_mode IS NULL AND
481 p_db_attendance_type IS NULL) THEN
482 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
483 ELSE
484 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
485 END IF;
486 END IF;
487 END IF;
488 END IF;
489 -- Validating ?Y N Y? parameters
490 IF (p_new_location_cd IS NOT NULL)AND
491 (p_new_attendance_mode IS NULL) AND
492 (p_new_attendance_type IS NOT NULL) THEN
493 IF p_db_attendance_mode IS NULL THEN
494 IF p_db_location_cd IS NULL AND
495 p_db_attendance_type IS NULL THEN
496 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
497 END IF;
498 IF (p_db_location_cd = p_new_location_cd AND
499 p_db_attendance_type IS NULL) OR
500 (p_db_attendance_type = p_new_attendance_type AND
501 p_db_location_cd IS NULL) THEN
502 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
503 END IF;
504 IF (p_db_location_cd = p_new_location_cd AND
505 p_db_attendance_type = p_new_attendance_type) THEN
506 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
507 END IF;
508 ELSE
509 IF (p_db_location_cd IS NULL AND
510 p_db_attendance_type IS NULL) THEN
511 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
512 END IF;
513 IF (p_db_location_cd = p_new_location_cd AND
514 p_db_attendance_type IS NULL) OR
515 (p_db_attendance_type = p_new_attendance_type AND
516 p_db_location_cd IS NULL) THEN
517 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
518 END IF;
519 IF (p_db_location_cd = p_new_location_cd AND
520 p_db_attendance_type = p_new_attendance_type) THEN
521 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
522 END IF;
523 END IF;
524 END IF;
525 -- Validating ?Y Y N? parameters (ie; att parameter is null)
526 IF (p_new_location_cd IS NOT NULL)AND
527 (p_new_attendance_mode IS NOT NULL) AND
528 (p_new_attendance_type IS NULL) THEN
529 IF (p_db_attendance_type IS NULL) THEN
530 IF (p_db_location_cd IS NULL AND
531 p_db_attendance_mode IS NULL)THEN
532 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
533 END IF;
534 IF (p_db_location_cd = p_new_location_cd AND
535 p_db_attendance_mode IS NULL) OR
536 (p_db_attendance_mode = p_new_attendance_mode AND
537 p_db_location_cd IS NULL) THEN
538 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
539 END IF;
540 IF (p_db_location_cd = p_new_location_cd AND
541 p_db_attendance_mode = p_new_attendance_mode) THEN
542 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
543 END IF;
544 ELSE
545 IF (p_db_location_cd IS NULL AND
546 p_db_attendance_mode IS NULL) OR
547 (p_db_location_cd = p_new_location_cd AND
548 p_db_attendance_mode is null) OR
549 (p_db_attendance_mode = p_new_attendance_mode AND
550 p_db_location_cd IS NULL) THEN
551 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
552 END IF;
553 IF (p_db_location_cd = p_new_location_cd AND
554 p_db_attendance_mode = p_new_attendance_mode) THEN
555 v_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS';
556 END IF;
557 END IF;
558 END IF;
559 -- Validating ?Y Y Y? parameters (ie; no parameters are null)
560 IF (p_new_location_cd IS NOT NULL)AND
561 (p_new_attendance_mode IS NOT NULL) AND
562 (p_new_attendance_type IS NOT NULL) THEN
563 IF (p_db_attendance_type IS NULL) THEN
564 IF (p_db_location_cd IS NULL AND
565 p_db_attendance_mode IS NULL) OR
566 (p_db_location_cd = p_new_location_cd AND
567 p_db_attendance_mode IS NULL) OR
568 (p_db_attendance_mode = p_new_attendance_mode AND
569 p_db_location_cd IS NULL) THEN
570 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
571 END IF;
572 IF (p_db_location_cd = p_new_location_cd AND
573 p_db_attendance_mode = p_new_attendance_mode) THEN
574 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
575 END IF;
576 ELSE
577 IF (p_db_attendance_type = p_new_attendance_type) THEN
578 IF (p_db_location_cd IS NULL AND
579 p_db_attendance_mode IS NULL) OR
580 (p_db_location_cd = p_new_location_cd AND
581 p_db_attendance_mode IS NULL) OR
582 (p_db_attendance_mode = p_new_attendance_mode AND
583 p_db_location_cd IS NULL) THEN
584 v_message_name := 'IGS_AS_REC_INS_UPD_MORE_LINKS';
585 END IF;
586 IF (p_db_location_cd = p_new_location_cd AND
587 p_db_attendance_mode = p_new_attendance_mode) THEN
588 v_message_name := 'IGS_AS_SPECIFIED_LINK_CONFLIC';
589 END IF;
590 END IF;
591 END IF;
592 END IF;
593 IF (v_message_name <> Null) THEN
594 p_message_name := v_message_name;
595 RETURN FALSE;
596 END IF;
597 -- Return the default value
598 RETURN TRUE;
599 END;
600 EXCEPTION
601 WHEN OTHERS THEN
602 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
603 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.genp_val_optnl_coo');
604 IGS_GE_MSG_STACK.ADD;
605 App_Exception.Raise_Exception;
606 END genp_val_optnl_coo;
607 --
608
609 -- Validate the attendance mode closed indicator.
610 FUNCTION crsp_val_am_closed(
611 p_attendance_mode IN VARCHAR2 ,
612 p_message_name OUT NOCOPY VARCHAR2 )
613 RETURN BOOLEAN IS
614 gv_other_detail VARCHAR2(255);
615 BEGIN -- crsp_val_am_closed
616 -- Validate if IGS_EN_ATD_MODE.attendance_mod is closed.
617 DECLARE
618 v_closed_ind IGS_EN_ATD_MODE.closed_ind%TYPE;
619 v_ret_val BOOLEAN DEFAULT TRUE;
620 CURSOR c_am IS
621 SELECT closed_ind
622 FROM IGS_EN_ATD_MODE
623 WHERE attendance_mode = p_attendance_mode;
624 BEGIN
625 p_message_name := Null;
626 OPEN c_am;
627 FETCH c_am INTO v_closed_ind;
628 IF (c_am%FOUND) THEN
629 IF (v_closed_ind = 'Y') THEN
630 p_message_name := 'IGS_PS_ATTEND_MODE_CLOSED';
631 v_ret_val := FALSE;
632 END IF;
633 END IF;
634 CLOSE c_am;
635 RETURN v_ret_val;
636 END;
637 EXCEPTION
638 WHEN OTHERS THEN
639 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
640 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.crsp_val_am_closed');
641 IGS_GE_MSG_STACK.ADD;
642 App_Exception.Raise_Exception;
643 END crsp_val_am_closed;
644 --
645 -- Validate the admission period course offering option details.
646 FUNCTION admp_val_apcoo_opt(
647 p_course_cd IN VARCHAR2 ,
648 p_version_number IN NUMBER ,
649 p_acad_cal_type IN VARCHAR2 ,
650 p_location_cd IN VARCHAR2 ,
651 p_attendance_mode IN VARCHAR2 ,
652 p_attendance_type IN VARCHAR2 ,
653 p_adm_cal_type IN VARCHAR2 ,
654 p_adm_ci_sequence_number IN NUMBER ,
655 p_admission_cat IN VARCHAR2 ,
656 p_s_admission_process_type IN VARCHAR2 ,
657 p_message_name OUT NOCOPY VARCHAR2 )
658 RETURN BOOLEAN IS
659 gv_other_detail VARCHAR2(255);
660 BEGIN -- admp_val_apcoo_opt
661 -- Validate the admission period course offering option details
662 DECLARE
663 v_location_cd IGS_PS_OFR_PAT.location_cd%TYPE;
664 v_attendance_mode IGS_PS_OFR_PAT.attendance_mode%TYPE;
665 v_attendance_type IGS_PS_OFR_PAT.attendance_type%TYPE;
666 v_message_name varchar2(30);
667 CURSOR c_acov IS
668 SELECT location_cd,
669 attendance_mode,
670 attendance_type
671 FROM IGS_PS_OFR_PAT_APCOO_V
672 WHERE course_cd = p_course_cd AND
673 version_number = p_version_number AND
674 acad_cal_type = p_acad_cal_type AND
675 adm_cal_type = p_adm_cal_type AND
676 adm_ci_sequence_number = p_adm_ci_sequence_number AND
677 admission_cat = p_admission_cat AND
678 s_admission_process_type = p_s_admission_process_type AND
679 (IGS_AD_GEN_013.ADMS_GET_COO_CRV(
680 course_cd,
681 version_number,
682 s_admission_process_type,
683 'N') = 'Y') AND
684 (IGS_AD_GEN_013.ADMS_GET_COO_ADM_CAT (
685 course_cd,
686 version_number,
687 acad_cal_type,
688 location_cd,
689 attendance_mode,
690 attendance_type,
691 admission_cat) = 'Y') AND
692 (p_location_cd IS NULL OR
693 location_cd = p_location_cd) AND
694 (p_attendance_mode IS NULL OR
695 attendance_mode = p_attendance_mode) AND
696 (p_attendance_type IS NULL OR
697 attendance_type = p_attendance_type);
698 BEGIN
699 p_message_name := Null;
700 IF (p_location_cd IS NOT NULL OR
701 p_attendance_mode IS NOT NULL OR
702 p_attendance_type IS NOT NULL) THEN
703 OPEN c_acov;
704 FETCH c_acov INTO v_location_cd,
705 v_attendance_mode,
706 v_attendance_type;
707 IF (c_acov%NOTFOUND) THEN
708 CLOSE c_acov;
709 p_message_name := 'IGS_AD_NOPOO_NOTSPECFY_PRG';
710 RETURN FALSE;
711 END IF;
712 CLOSE c_acov;
713 END IF;
714 RETURN TRUE;
715 END;
716 EXCEPTION
717 WHEN OTHERS THEN
718 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
719 Fnd_Message.Set_Token('NAME','IGS_AD_VAL_APCOO.admp_val_apcoo_opt');
720 IGS_GE_MSG_STACK.ADD;
721 App_Exception.Raise_Exception;
722 END admp_val_apcoo_opt;
723
724 END IGS_AD_VAL_APCOO;
[Home] [Help]