1 PACKAGE BODY IGS_PS_GEN_003 AS
2 /* $Header: IGSPS03B.pls 120.6 2006/02/17 02:48:40 sarakshi ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --sarakshi 21-oct-2003 Enh#3052452,added function enrollment_for_uoo_check
7 --sarakshi 25-Feb-2003 Bug#2797116,modified curosr gc_cd_and_vn_passed and gc_coo_id_passed
8 -- in crsp_get_coo_key procedure,added delete_flag check in the where clause
9 --shtatiko 05-FEB-2003 Bug# 2550392, Modified crsp_ins_ci_cop procedure and added
10 -- log_parameters procedures.
11 --smadathi 28-AUG-2001 Bug No. 1956374 .The call to igs_ps_val_cop.genp_val_staff_prsn
12 -- is changed to igs_ad_val_acai.genp_val_staff_prsn
13 -------------------------------------------------------------------------------------------
14 -- Bug # 1956374 Procedure assp_val_gs_cur_fut reference is changed
15
16 PROCEDURE log_parameters ( p_c_param_name VARCHAR2 ,
17 p_c_param_value VARCHAR2
18 ) IS
19 /***********************************************************************************************
20
21 Created By : SHTATIKO
22 Date Created By: 05-FEB-2003
23
24 Purpose : To log the parameters. This has been added as part of Bug Fix 2550392.
25
26 Known limitations,enhancements,remarks:
27 Change History
28 Who When What
29
30 ********************************************************************************************** */
31 BEGIN
32 fnd_message.set_name('IGS','IGS_PS_DEL_PRIORITY_LOG');
33 fnd_message.set_token('PARAMETER_NAME', p_c_param_name );
34 fnd_message.set_token('PARAMETER_VAL' , p_c_param_value ) ;
35 fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET);
36 END log_parameters ;
37
38
39 PROCEDURE crsp_get_coo_key(
40 p_coo_id IN OUT NOCOPY NUMBER ,
41 p_course_cd IN OUT NOCOPY VARCHAR2 ,
42 p_version_number IN OUT NOCOPY NUMBER ,
43 p_cal_type IN OUT NOCOPY VARCHAR2 ,
44 p_location_cd IN OUT NOCOPY VARCHAR2 ,
45 p_attendance_mode IN OUT NOCOPY VARCHAR2 ,
46 p_attendance_type IN OUT NOCOPY VARCHAR2 )
47 AS
48 lv_param_values VARCHAR2(1080);
49 BEGIN
50 DECLARE
51
52 gv_cd_and_vn_passed IGS_PS_OFR_OPT.coo_id%TYPE;
53 gv_coo_id_passed IGS_PS_OFR_OPT%ROWTYPE;
54 -- this is used when the course_cd and version_number
55 -- are passed
56 CURSOR gc_cd_and_vn_passed IS
57 SELECT coo_id
58 FROM IGS_PS_OFR_OPT
59 WHERE course_cd = p_course_cd AND
60 version_number = p_version_number AND
61 cal_type = p_cal_type AND
62 location_cd = p_location_cd AND
63 attendance_mode = p_attendance_mode AND
64 attendance_type = p_attendance_type AND
65 delete_flag = 'N';
66 -- this is used when the coo_id is passed
67 CURSOR gc_coo_id_passed IS
68 SELECT *
69 FROM IGS_PS_OFR_OPT
70 WHERE coo_id = p_coo_id
71 AND delete_flag = 'N';
72 BEGIN
73 -- if only the course_cd was enetered
74 IF(NVL(p_course_cd, 'NO_VALUE') <> 'NO_VALUE') THEN
75 OPEN gc_cd_and_vn_passed;
76 FETCH gc_cd_and_vn_passed INTO gv_cd_and_vn_passed;
77 -- return the coo_id
78 IF(gc_cd_and_vn_passed%ROWCOUNT <> 0) THEN
79 CLOSE gc_cd_and_vn_passed;
80 p_coo_id := gv_cd_and_vn_passed;
81 END IF;
82 ELSE
83 -- if only the coo_id is entered
84 IF p_coo_id IS NOT NULL THEN
85 OPEN gc_coo_id_passed;
86 FETCH gc_coo_id_passed INTO gv_coo_id_passed;
87 -- return the coo_id, course_cd, version_number,
88 -- IGS_CA_TYPE, location_cd, attendance_mode, attendance_type
89 IF(gc_coo_id_passed%ROWCOUNT <> 0) THEN
90 CLOSE gc_coo_id_passed;
91 p_course_cd := gv_coo_id_passed.course_cd;
92 p_version_number := gv_coo_id_passed.version_number;
93 p_cal_type := gv_coo_id_passed.cal_type;
94 p_location_cd := gv_coo_id_passed.location_cd;
95 p_attendance_mode := gv_coo_id_passed.attendance_mode;
96 p_attendance_type := gv_coo_id_passed.attendance_type;
97 END IF;
98 ELSE
99 -- Do nothing.
100 NULL;
101 END IF;
102 END IF;
103 EXCEPTION
104 WHEN OTHERS THEN
105 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
106 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_003.crsp_get_coo_key');
107 IGS_GE_MSG_STACK.ADD;
108 lv_param_values := to_char(p_coo_id)||','||p_course_cd||','||to_char(p_version_number)||','||p_cal_type||','
109 ||p_location_cd||','||p_attendance_mode||','||p_attendance_type;
110 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
111 Fnd_Message.Set_Token('VALUE',lv_param_values);
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception;
114 END;
115 END crsp_get_coo_key;
116
117
118 PROCEDURE crsp_get_cop_key(
119 p_cop_id IN OUT NOCOPY NUMBER ,
120 p_course_cd IN OUT NOCOPY VARCHAR2 ,
121 p_version_number IN OUT NOCOPY NUMBER ,
122 p_cal_type IN OUT NOCOPY VARCHAR2 ,
123 p_ci_sequence_number IN OUT NOCOPY NUMBER ,
124 p_location_cd IN OUT NOCOPY VARCHAR2 ,
125 p_attendance_mode IN OUT NOCOPY VARCHAR2 ,
126 p_attendance_type IN OUT NOCOPY VARCHAR2 )
127 AS
128 lv_param_values VARCHAR2(1080);
129 BEGIN
130 DECLARE
131
132 gv_cd_and_vn_passed IGS_PS_OFR_PAT.coo_id%TYPE;
133 gv_cop_id_passed IGS_PS_OFR_PAT%ROWTYPE;
134 -- this is used when the course_cd and version_number
135 -- are passed
136 CURSOR gc_cd_and_vn_passed IS
137 SELECT cop_id
138 FROM IGS_PS_OFR_PAT
139 WHERE course_cd = p_course_cd AND
140 version_number = p_version_number AND
141 cal_type = p_cal_type AND
142 ci_sequence_number = p_ci_sequence_number AND
143 location_cd = p_location_cd AND
144 attendance_mode = p_attendance_mode AND
145 attendance_type = p_attendance_type;
146 -- this is used when the coo_id is passed
147 CURSOR gc_cop_id_passed IS
148 SELECT *
149 FROM IGS_PS_OFR_PAT
150 WHERE cop_id = p_cop_id;
151 BEGIN
152 -- if only the course_cd was enetered
153 IF(NVL(p_course_cd, 'NO_VALUE') <> 'NO_VALUE') THEN
154 OPEN gc_cd_and_vn_passed;
155 FETCH gc_cd_and_vn_passed INTO gv_cd_and_vn_passed;
156 -- return the cop_id
157 IF(gc_cd_and_vn_passed%ROWCOUNT <> 0) THEN
158 CLOSE gc_cd_and_vn_passed;
159 p_cop_id := gv_cd_and_vn_passed;
160 END IF;
161 ELSE
162 -- if only the cop_id is entered
163 IF p_cop_id IS NOT NULL THEN
164 OPEN gc_cop_id_passed;
165 FETCH gc_cop_id_passed INTO gv_cop_id_passed;
166 -- return the cop_id, course_cd, version_number,
167 -- IGS_CA_TYPE, ci_sequence_numberlocation_cd,
168 -- attendance_mode, attendance_type
169 IF(gc_cop_id_passed%ROWCOUNT <> 0) THEN
170 CLOSE gc_cop_id_passed;
171 p_course_cd := gv_cop_id_passed.course_cd;
172 p_version_number := gv_cop_id_passed.version_number;
173 p_cal_type := gv_cop_id_passed.cal_type;
174 p_ci_sequence_number := gv_cop_id_passed.ci_sequence_number;
175 p_location_cd := gv_cop_id_passed.location_cd;
176 p_attendance_mode := gv_cop_id_passed.attendance_mode;
177 p_attendance_type := gv_cop_id_passed.attendance_type;
178 END IF;
179 ELSE
180 -- Do nothing.
181 NULL;
182 END IF;
183 END IF;
184 EXCEPTION
185
186 WHEN OTHERS THEN
187 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
188 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_003.crsp_get_cop_key');
189 IGS_GE_MSG_STACK.ADD;
190 lv_param_values := to_char(p_cop_id)||','||p_course_cd||','||to_char(p_version_number)||','||p_cal_type||','
191 ||to_char(p_ci_sequence_number)||','||p_location_cd||','||p_attendance_mode||','||p_attendance_type;
192 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
193 Fnd_Message.Set_Token('VALUE',lv_param_values);
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END;
197 END crsp_get_cop_key;
198
199 FUNCTION crsp_get_cous_ind(
200 p_course_cd IN VARCHAR2 ,
201 p_crv_version_number IN NUMBER ,
202 p_cal_type IN VARCHAR2 ,
203 p_unit_set_cd IN VARCHAR2 )
204 RETURN VARCHAR2 AS
205 lv_param_values VARCHAR2(1080);
206 BEGIN -- crsp_get_cous_ind
207 -- This module returns Y if the IGS_PS_COURSE offering and IGS_PS_UNIT set exists
208 -- in the IGS_PS_OFR_UNIT_SET table.
209 DECLARE
210
211 v_dummy VARCHAR2(1);
212 CURSOR c_cous IS
213 SELECT 'X'
214 FROM IGS_PS_OFR_UNIT_SET cous
215 WHERE cous.course_cd = p_course_cd AND
216 cous.crv_version_number = p_crv_version_number AND
217 cous.cal_type = p_cal_type AND
218 cous.unit_set_cd = p_unit_set_cd;
219 BEGIN
220 OPEN c_cous;
221 FETCH c_cous INTO v_dummy;
222 IF c_cous%FOUND THEN
223 CLOSE c_cous;
224 RETURN 'Y';
225 ELSE
226 CLOSE c_cous;
227 RETURN 'N';
228 END IF;
229 EXCEPTION
230 WHEN OTHERS THEN
231 IF c_cous%ISOPEN THEN
232 CLOSE c_cous;
233 END IF;
234 App_Exception.Raise_Exception;
235 END;
236 EXCEPTION
237 WHEN OTHERS THEN
238 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
239 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_003.crsp_get_cous_ind');
240 IGS_GE_MSG_STACK.ADD;
241 lv_param_values := p_course_cd||','||to_char(p_crv_version_number)||','||p_cal_type||','||p_unit_set_cd;
242
243 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
244 Fnd_Message.Set_Token('VALUE',lv_param_values);
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception;
247 END crsp_get_cous_ind;
248
249 FUNCTION crsp_get_cous_subind(
250 p_course_cd IN VARCHAR2 ,
251 p_crv_version_number IN NUMBER ,
252 p_cal_type IN VARCHAR2 ,
253 p_unit_set_cd IN VARCHAR2 ,
254 p_us_version_number IN NUMBER )
255 RETURN VARCHAR2 AS
256 lv_param_values VARCHAR2(1080);
257 BEGIN -- crsp_get_cous_subind
258 -- This module fetches the value for the only_as_sub_ind for a IGS_PS_COURSE offering
259 -- IGS_PS_UNIT set from the IGS_PS_OFR_UNIT_SET table.
260 DECLARE
261
262 v_only_as_sub_ind IGS_PS_OFR_UNIT_SET.only_as_sub_ind%TYPE;
263 CURSOR c_cous IS
264 SELECT cous.only_as_sub_ind
265 FROM IGS_PS_OFR_UNIT_SET cous
266 WHERE cous.course_cd = p_course_cd AND
267 cous.crv_version_number = p_crv_version_number AND
268 cous.cal_type = p_cal_type AND
269 cous.unit_set_cd = p_unit_set_cd AND
270 cous.us_version_number = p_us_version_number;
271 BEGIN
272 OPEN c_cous;
273 FETCH c_cous INTO v_only_as_sub_ind;
274 IF c_cous%FOUND THEN
275 CLOSE c_cous;
276 RETURN v_only_as_sub_ind;
277 ELSE
278 CLOSE c_cous;
279 RETURN NULL;
280 END IF;
281 EXCEPTION
282 WHEN OTHERS THEN
283 IF c_cous%ISOPEN THEN
284 CLOSE c_cous;
285 END IF;
286 App_Exception.Raise_Exception;
287 END;
288 EXCEPTION
289 WHEN OTHERS THEN
290
291 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
292 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_003.crsp_get_cous_subind');
293 IGS_GE_MSG_STACK.ADD;
294 lv_param_values := p_course_cd||','||to_char(p_crv_version_number)||','||p_cal_type||','||p_unit_set_cd||
295 ','||to_char(p_us_version_number);
296
297 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
298 Fnd_Message.Set_Token('VALUE',lv_param_values);
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 END crsp_get_cous_subind;
302
303 FUNCTION crsp_ins_coi_cop(
304 p_course_cd IN VARCHAR2 ,
305 p_version_number IN NUMBER ,
306 p_source_cal_type IN VARCHAR2 ,
307 p_source_sequence_number IN NUMBER ,
308 p_dest_cal_type IN VARCHAR2 ,
309 p_dest_sequence_number IN NUMBER ,
310 p_message_name OUT NOCOPY VARCHAR2 )
311 RETURN boolean AS
312 lv_param_values VARCHAR2(1080);
313 BEGIN -- crsp_ins_coi_cop
314 DECLARE
315
316 v_inserted_cnt NUMBER(4);
317 v_check CHAR;
318 v_message_name varchar2(30);
319 v_gs_version_number IGS_AS_GRD_SCHEMA.version_number%TYPE;
320 CURSOR c_course_offering_pattern IS
321 SELECT location_cd,
322 attendance_mode,
323 attendance_type,
324 cop_id,
325 coo_id,
326 offered_ind,
327 entry_point_ind,
328 pre_enrol_units_ind,
329 enrollable_ind,
330 ivrs_available_ind,
331 min_entry_ass_score,
332 guaranteed_entry_ass_scr,
333 max_cross_faculty_cp,
334 max_cross_location_cp,
335 max_cross_mode_cp,
336 max_hist_cross_faculty_cp,
337 adm_ass_officer_person_id,
338 adm_contact_person_id,
339 grading_schema_cd,
340 gs_version_number
341 FROM IGS_PS_OFR_PAT
342 WHERE course_cd = p_course_cd AND
343 version_number = p_version_number AND
344 cal_type = p_source_cal_type AND
345 ci_sequence_number = p_source_sequence_number;
346 v_cop_rec c_course_offering_pattern%ROWTYPE;
347 CURSOR c_check_cop_exist IS
348 SELECT 'x'
349 FROM IGS_PS_OFR_PAT
350 WHERE course_cd = p_course_cd AND
351 version_number = p_version_number AND
352 cal_type = p_dest_cal_type AND
353 ci_sequence_number = p_dest_sequence_number AND
354 location_cd = v_cop_rec.location_cd AND
355 attendance_type = v_cop_rec.attendance_type AND
356 attendance_mode = v_cop_rec.attendance_mode;
357 CURSOR c_cop_sequence_number IS
358 SELECT IGS_PS_OFR_PAT_COP_ID_S.NEXTVAL
359 FROM DUAL;
360 CURSOR c_latest_gs_version (
361 cp_gs_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE) IS
362 SELECT MAX(gs.version_number)
363 FROM IGS_AS_GRD_SCHEMA gs
364 WHERE gs.grading_schema_cd = cp_gs_cd;
365
366 x_rowid VARCHAR2(25);
367 BEGIN
368 p_message_name := NULL;
369 v_inserted_cnt := 0; -- number of records inserted
370 -- Rollover IGS_PS_COURSE offering patterns from
371 -- source IGS_PS_COURSE offering instance
372 -- (p_course_cd,p_version_number,
373 -- p_source_cal_type,p_source_sequence_number) to
374 -- destination IGS_PS_COURSE offering instance
375 -- (p_course_cd,p_version_number,
376 -- p_dest_cal_type,p_dest_sequence_number)
377 OPEN c_course_offering_pattern;
378 LOOP
379 FETCH c_course_offering_pattern INTO v_cop_rec;
380 EXIT WHEN c_course_offering_pattern%NOTFOUND;
381 -- Check for closed IGS_AD_LOCATION code,
382 -- closed attendance mode,
383 -- and closed attendance type
384
385 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_COO.crsp_val_loc_cd
386 IF (IGS_PS_VAL_UOO.crsp_val_loc_cd (
387 v_cop_rec.location_cd,
388 p_message_name) = TRUE) AND
389 (IGS_PS_VAL_COo.crsp_val_coo_am (
390 v_cop_rec.attendance_mode,
391 p_message_name) = TRUE) AND
392 (IGS_PS_VAL_COo.crsp_val_coo_att (
393 v_cop_rec.attendance_type,
394 p_message_name) = TRUE) THEN
395 OPEN c_check_cop_exist;
396 FETCH c_check_cop_exist INTO v_check;
397 -- Only rollover if the record to be inserted not exists
398 IF (c_check_cop_exist%NOTFOUND) THEN
399 CLOSE c_check_cop_exist;
400 -- get the next sequence number
401 OPEN c_cop_sequence_number;
402 FETCH c_cop_sequence_number INTO v_cop_rec.cop_id;
403 CLOSE c_cop_sequence_number;
404 -- get the latest grading schema version number
405 OPEN c_latest_gs_version (
406 v_cop_rec.grading_schema_cd);
407 FETCH c_latest_gs_version INTO v_gs_version_number;
408 CLOSE c_latest_gs_version;
409 IF IGS_AS_VAL_GSG.assp_val_gs_cur_fut (
410 v_cop_rec.grading_schema_cd,
411 v_gs_version_number,
412 v_message_name) = FALSE THEN
413 -- The latest grading schema fails the current or vuture valildation
414 v_cop_rec.grading_schema_cd := NULL;
415 v_cop_rec.gs_version_number := NULL;
416 ELSE
417 v_cop_rec.gs_version_number := v_gs_version_number;
418 END IF;
419 -- check if a IGS_PE_PERSON fails the staff IGS_PE_PERSON validation
420 IF igs_ad_val_acai.genp_val_staff_prsn (
421 v_cop_rec.adm_ass_officer_person_id,
422 v_message_name) = FALSE THEN
423 v_cop_rec.adm_ass_officer_person_id := NULL;
424 END IF;
425 IF igs_ad_val_acai.genp_val_staff_prsn (
426 v_cop_rec.adm_contact_person_id,
427 v_message_name) = FALSE THEN
428 v_cop_rec.adm_contact_person_id := NULL;
429 END IF;
430
431
432 IGS_PS_OFR_PAT_PKG.Insert_Row(
433 X_ROWID => x_rowid,
434 X_COURSE_CD => p_course_cd,
435 X_CI_SEQUENCE_NUMBER => p_dest_sequence_number,
436 X_CAL_TYPE => p_dest_cal_type,
437 X_VERSION_NUMBER => p_version_number,
438 X_LOCATION_CD => v_cop_rec.location_cd,
439 X_ATTENDANCE_TYPE => v_cop_rec.attendance_type,
440 X_ATTENDANCE_MODE => v_cop_rec.attendance_mode,
441 X_COP_ID => v_cop_rec.cop_id,
442 X_COO_ID => v_cop_rec.coo_id,
443 X_OFFERED_IND => v_cop_rec.offered_ind,
444 X_CONFIRMED_OFFERING_IND => NULL,
445 X_ENTRY_POINT_IND => v_cop_rec.entry_point_ind,
446 X_PRE_ENROL_UNITS_IND => v_cop_rec.pre_enrol_units_ind,
447 X_ENROLLABLE_IND => v_cop_rec.enrollable_ind,
448 X_IVRS_AVAILABLE_IND => v_cop_rec.ivrs_available_ind,
449 X_MIN_ENTRY_ASS_SCORE => v_cop_rec.min_entry_ass_score,
450 X_GUARANTEED_ENTRY_ASS_SCR => v_cop_rec.guaranteed_entry_ass_scr,
451 X_MAX_CROSS_FACULTY_CP => v_cop_rec.max_cross_faculty_cp,
452 X_MAX_CROSS_LOCATION_CP => v_cop_rec.max_cross_location_cp,
453 X_MAX_CROSS_MODE_CP => v_cop_rec.max_cross_mode_cp,
454 X_MAX_HIST_CROSS_FACULTY_CP => v_cop_rec.max_hist_cross_faculty_cp,
455 X_ADM_ASS_OFFICER_PERSON_ID => v_cop_rec.adm_ass_officer_person_id,
456 X_ADM_CONTACT_PERSON_ID => v_cop_rec.adm_contact_person_id,
457 X_GRADING_SCHEMA_CD => v_cop_rec.grading_schema_cd,
458 X_GS_VERSION_NUMBER => v_cop_rec.gs_version_number,
459 X_MODE => 'R');
460
461
462 v_inserted_cnt := v_inserted_cnt + 1;
463 ELSE
464 CLOSE c_check_cop_exist;
465 END IF; -- dest record does not exist
466 END IF; -- check closed IGS_AD_LOCATION code
467 END LOOP;
468 -- No record is selected
469 IF (c_course_offering_pattern%ROWCOUNT = 0) THEN
470 p_message_name := 'IGS_PS_NO_POP_TOBE_ENROLLED';
471 -- No record is inserted
472 ELSIF (v_inserted_cnt = 0) THEN
473 p_message_name := 'IGS_PS_PRGOFFR_ROLLED_EXIST';
474 -- Some records are inserted
475 ELSIF (c_course_offering_pattern%ROWCOUNT <> v_inserted_cnt) THEN
476 p_message_name := 'IGS_PS_PARTIALCREATION_OFFPAT';
477 -- All records are inserted
478 ELSIF (c_course_offering_pattern%ROWCOUNT = v_inserted_cnt) THEN
479 p_message_name := 'IGS_PS_SUCCESS_CREATION_POP';
480 END IF;
481 CLOSE c_course_offering_pattern;
482 RETURN TRUE;
483 EXCEPTION
484 WHEN OTHERS THEN
485 IF c_course_offering_pattern%ISOPEN THEN
486 CLOSE c_course_offering_pattern;
487 END IF;
488 IF c_cop_sequence_number%ISOPEN THEN
489 CLOSE c_cop_sequence_number;
490 END IF;
491 IF c_check_cop_exist%ISOPEN THEN
492 CLOSE c_check_cop_exist;
493 END IF;
494 IF c_latest_gs_version%ISOPEN THEN
495 CLOSE c_latest_gs_version;
496 END IF;
497 App_Exception.Raise_Exception;
498 END;
499 EXCEPTION
500 WHEN OTHERS THEN
501
502 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
503 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_003.crsp_ins_coi_cop');
504 IGS_GE_MSG_STACK.ADD;
505 lv_param_values := p_course_cd||','||to_char(p_version_number)||','||p_source_cal_type||','
506 ||to_char(p_source_sequence_number)||','||p_dest_cal_type||','||
507 to_char(p_dest_sequence_number)||','||p_message_name;
508
509 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
510 Fnd_Message.Set_Token('VALUE',lv_param_values);
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception;
513 END crsp_ins_coi_cop;
514
515 PROCEDURE CRSP_INS_CI_COP (
516 errbuf out NOCOPY varchar2,
517 retcode out NOCOPY number,
518 p_source_cal IN VARCHAR2 ,
519 p_dest_cal IN VARCHAR2 ,
520 p_org_unit IN VARCHAR2,
521 p_org_id IN NUMBER) AS
522 /*
523 WHO WHEN WHAT
524 shtatiko 05-FEB-2003 Bug# 2550392, Added code to log information wherever needed to make
525 log file more informative.
526 */
527
528 p_source_cal_type igs_ca_inst.cal_type%type ;
529 p_source_sequence_number igs_ca_inst.sequence_number%type;
530 p_dest_cal_type igs_ca_inst.cal_type%type;
531 p_dest_sequence_number igs_ca_inst.sequence_number%type;
532 p_org_unit_cd igs_or_unit.org_unit_cd%type;
533 v_check CHAR;
534 v_coi_rec IGS_PS_OFR_INST%ROWTYPE;
535 v_message VARCHAR2(30);
536 gv_ci_start_dt IGS_PS_OFR_INST.ci_start_dt%TYPE;
537 gv_ci_end_dt IGS_PS_OFR_INST.ci_end_dt%TYPE;
538 v_rec_inserted_cnt NUMBER(4) := 0;
539 cst_none_cop_rec_inserted BOOLEAN := TRUE;
540 cst_partial_cop_rec_inserted BOOLEAN := TRUE;
541 cst_all_cop_rec_inserted BOOLEAN := TRUE;
542 cst_none_coi_rec_inserted BOOLEAN := FALSE;
543 cst_partial_coi_rec_inserted BOOLEAN := FALSE;
544 cst_all_coi_rec_inserted BOOLEAN := FALSE;
545
546 CURSOR c_check_cal_type_exist IS
547 SELECT 'x'
548 FROM IGS_CA_TYPE
549 WHERE cal_type = p_source_cal_type;
550 CURSOR c_check_cal_instance_exist(
551 cp_cal_type IGS_PS_OFR_INST.cal_type%TYPE,
552 cp_sequence_number IGS_PS_OFR_INST.version_number%TYPE) IS
553 SELECT 'x'
554 FROM IGS_CA_INST
555 WHERE cal_type = cp_cal_type AND
556 sequence_number = cp_sequence_number;
557 CURSOR c_get_cal_instance_dates(
558 cp_cal_type IGS_PS_OFR_INST.cal_type%TYPE,
559 cp_sequence_number IGS_PS_OFR_INST.version_number%TYPE) IS
560 SELECT start_dt, end_dt
561 FROM IGS_CA_INST
562 WHERE cal_type = cp_cal_type AND
563 sequence_number = cp_sequence_number;
564 CURSOR c_course_offering_instance IS
565 SELECT coi.course_cd,
566 coi.version_number,
567 coi.cal_type,
568 coi.ci_sequence_number,
569 coi.ci_start_dt,
570 coi.ci_end_dt,
571 coi.min_entry_ass_score,
572 coi.guaranteed_entry_ass_scr,
573 coi.created_by,
574 coi.creation_date,
575 coi.last_updated_by,
576 coi.last_update_date,
577 coi.last_update_login,
578 coi.request_id,
579 coi.PROGRAM_APPLICATION_ID,
580 coi.PROGRAM_ID,
581 coi.PROGRAM_UPDATE_DATE
582 FROM IGS_PS_OFR_INST coi,
583 IGS_PS_VER cv
584 WHERE coi.cal_type=p_source_cal_type AND
585 coi.ci_sequence_number= p_source_sequence_number AND
586 cv.course_cd = coi.course_cd AND
587 cv.version_number= coi.version_number AND
588 cv.expiry_dt IS NULL AND
589 cv.responsible_org_unit_cd LIKE p_org_unit_cd;
590 CURSOR c_check_coi_exist (
591 cp_course_cd IGS_PS_OFR_INST.course_cd%TYPE,
592 cp_version_number IGS_PS_OFR_INST.version_number%TYPE) IS
593 SELECT 'x'
594 FROM IGS_PS_OFR_INST
595 WHERE course_cd = cp_course_cd AND
596 version_number = cp_version_number AND
597 cal_type = p_dest_cal_type AND
598 ci_sequence_number = p_dest_sequence_number;
599 x_rowid VARCHAR2(25);
600 INVALID EXCEPTION;
601 VALID EXCEPTION;
602
603 l_start_dt igs_ca_inst.start_dt%TYPE;
604 l_end_dt igs_ca_inst.end_dt%TYPE;
605
606 BEGIN
607 -- Parameter Validation
608 -- Adding the default org id parameter as part of MULTI-ORG changes
609 IGS_GE_GEN_003.SET_ORG_ID(p_org_id);
610
611 -- Extract source calendar
612 p_source_cal_type := RTRIM(SUBSTR(p_source_cal, 102, 10));
613 p_source_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_source_cal, 113, 8)));
614
615 -- Extract destination calendar
616 p_dest_cal_type := RTRIM(SUBSTR(p_dest_cal, 102, 10));
617 p_dest_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_dest_cal, 113, 8)));
618
619 -- Extract org_unit_cd
620 p_org_unit_cd := NVL(SUBSTR(p_org_unit, 1, 10),'%');
621
622 -- Log all the parameters passed. This has been added as part of Bug# 2550392 by shtatiko
623
624 l_start_dt := TO_DATE ( RTRIM(SUBSTR(p_source_cal, 12, 10)), 'DD/MM/YYYY' ) ;
625 l_end_dt := TO_DATE ( RTRIM(SUBSTR(p_source_cal, 23, 10)), 'DD/MM/YYYY' );
626
627 fnd_file.put_line ( fnd_file.LOG, ' ' );
628 log_parameters ( p_c_param_name => igs_ge_gen_004.GENP_GET_LOOKUP ( 'IGS_PS_LOG_PARAMETERS', 'SOURCE_CAL' ),
629 p_c_param_value => TO_CHAR ( l_start_dt, 'DD-MON-YYYY' ) || ' - ' ||
630 TO_CHAR ( l_end_dt, 'DD-MON-YYYY' ) || ' - ' ||
631 p_source_cal_type );
632
633 l_start_dt := TO_DATE ( RTRIM(SUBSTR(p_dest_cal, 12, 10)), 'DD/MM/YYYY' ) ;
634 l_end_dt := TO_DATE ( RTRIM(SUBSTR(p_dest_cal, 23, 10)), 'DD/MM/YYYY' );
635
636 log_parameters ( p_c_param_name => igs_ge_gen_004.GENP_GET_LOOKUP ( 'IGS_PS_LOG_PARAMETERS', 'DEST_CAL' ),
637 p_c_param_value => TO_CHAR ( l_start_dt, 'DD-MON-YYYY' ) || ' - ' ||
638 TO_CHAR ( l_end_dt, 'DD-MON-YYYY' ) || ' - ' ||
639 p_dest_cal_type );
640 log_parameters ( p_c_param_name => igs_ge_gen_004.GENP_GET_LOOKUP ( 'LEGACY_TOKENS', 'ORG_UNIT_CD' ),
641 p_c_param_value => p_org_unit_cd );
642 fnd_file.put_line ( fnd_file.LOG, ' ' );
643 fnd_file.put_line ( fnd_file.LOG, ' ' );
644
645 v_message := NULL;
646
647 -- Can only transfer within the same IGS_CA_TYPE
648 IF (p_source_cal_type <> p_dest_cal_type) THEN
649 v_message :='IGS_PS_ROLLOVER_CALINSTANCES';
650 RAISE invalid;
651 END IF;
652
653
654 -- Calendar type must exist
655 OPEN c_check_cal_type_exist;
656 FETCH c_check_cal_type_exist INTO v_check;
657 IF (c_check_cal_type_exist%NOTFOUND) THEN
658 CLOSE c_check_cal_type_exist;
659 v_message := 'IGS_GE_VAL_DOES_NOT_XS';
660 RAISE invalid;
661
662 END IF;
663 CLOSE c_check_cal_type_exist;
664 -- validate the calendar type of IGS_PS_COURSE offering
665 IF (IGS_PS_VAL_CO.crsp_val_co_cal_type (
666 p_source_cal_type,
667 v_message) = FALSE) THEN
668 RAISE invalid;
669 END IF;
670 -- "Source" calendar instance must exist
671 OPEN c_check_cal_instance_exist(
672 p_source_cal_type,
673 p_source_sequence_number);
674 FETCH c_check_cal_instance_exist INTO v_check;
675 IF (c_check_cal_instance_exist%NOTFOUND) THEN
676 CLOSE c_check_cal_instance_exist;
677 v_message := 'IGS_PS_SRC_CALINST_NOT_EXIST';
678 RAISE invalid;
679 END IF;
680 CLOSE c_check_cal_instance_exist;
681 -- "Destination" calendar instance must exist and fetch start_dt and end_dt
682 OPEN c_get_cal_instance_dates(
683 p_dest_cal_type,
684 p_dest_sequence_number);
685 FETCH c_get_cal_instance_dates INTO gv_ci_start_dt, gv_ci_end_dt;
686 IF (c_get_cal_instance_dates%NOTFOUND) THEN
687 CLOSE c_get_cal_instance_dates;
688 v_message := 'IGS_PS_DEST_CAL_INST_NOT_EXIS';
689 RAISE invalid;
690 END IF;
691 CLOSE c_get_cal_instance_dates;
692 -- "Destination" calendar must be active
693 IF (IGS_as_VAL_uai.crsp_val_crs_ci (
694 p_dest_cal_type,
695 p_dest_sequence_number,
696 v_message) = FALSE) THEN
697 RAISE invalid;
698 END IF;
699 OPEN c_course_offering_instance;
700 LOOP
701 FETCH c_course_offering_instance INTO v_coi_rec;
702 EXIT WHEN c_course_offering_instance%NOTFOUND;
703
704 -- This logging Unit information has been added as part of Bug# 2550392 by shtatiko
705 fnd_file.put_line ( fnd_file.LOG, ' ');
706 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'PROGRAM_CD' )
707 || ' : ' || v_coi_rec.course_cd );
708 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'VERSION_NUMBER' )
709 || ' : ' || TO_CHAR (v_coi_rec.version_number) );
710 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'CAL_TYPE' )
711 || ' : ' || p_dest_cal_type );
712 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_FI_LOCKBOX', 'START_DT' )
713 || ' : ' || fnd_date.date_to_displaydate (gv_ci_start_dt) );
714 fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_FI_LOCKBOX', 'END_DT' )
715 || ' : ' || fnd_date.date_to_displaydate (gv_ci_end_dt) );
716 fnd_file.put_line ( fnd_file.LOG, ' ');
717
718 -- Check that IGS_PS_COURSE version is still active and can be updated
719 IF (IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl(
720 v_coi_rec.course_cd,
721 v_coi_rec.version_number,
722 v_message) = TRUE) THEN
723 OPEN c_check_coi_exist (
724 v_coi_rec.course_cd,
725 v_coi_rec.version_number);
726 FETCH c_check_coi_exist INTO v_check;
727 IF (c_check_coi_exist%NOTFOUND) THEN
728 IGS_PS_OFR_INST_PKG.Insert_Row(
729 X_ROWID => x_rowid,
730 X_COURSE_CD => v_coi_rec.course_cd,
731 X_VERSION_NUMBER => v_coi_rec.version_number,
732 X_CAL_TYPE => p_dest_cal_type,
733 X_CI_SEQUENCE_NUMBER => p_dest_sequence_number,
734 X_CI_START_DT => gv_ci_start_dt,
735 X_CI_END_DT => gv_ci_end_dt,
736 X_MIN_ENTRY_ASS_SCORE => v_coi_rec.min_entry_ass_score,
737 X_GUARANTEED_ENTRY_ASS_SCR => v_coi_rec.guaranteed_entry_ass_scr,
738 X_MODE => 'R');
739 v_rec_inserted_cnt := v_rec_inserted_cnt + 1;
740 -- This message has been added as part of Bug# 2550392 by shtatiko
741 fnd_message.set_name ( 'IGS', 'IGS_PS_ROLL_POI_SUCCESS' );
742 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get );
743 ELSE
744 -- This message has been added as part of Bug# 2550392 by shtatiko
745 fnd_message.set_name ( 'IGS', 'IGS_PS_ROLL_POI_EXISTS' );
746 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get );
747 END IF;
748 CLOSE c_check_coi_exist;
749 -- Rollover all course_offering_patterns
750 -- within IGS_PS_OFR_INST.
751 IF (IGS_PS_GEN_003.crsp_ins_coi_cop(
752 v_coi_rec.course_cd,
753 v_coi_rec.version_number,
754 p_source_cal_type,
755 p_source_sequence_number,
756 p_dest_cal_type,
757 p_dest_sequence_number,
758 v_message) = FALSE) THEN
759 -- This function never return FALSE at the moment
760 RAISE invalid;
761 END IF;
762
763 fnd_message.set_name ( 'IGS', v_message );
764 fnd_file.put_line ( fnd_file.LOG, ' ' || fnd_message.get );
765 -- no course_offering_patterns rolled over
766 IF (v_message = 'IGS_PS_PRGOFFR_ROLLED_EXIST' OR
767 v_message = 'IGS_PS_NO_POP_TOBE_ENROLLED') THEN
768 cst_all_cop_rec_inserted := FALSE;
769 -- partial course_offering_patterns rolled over
770 ELSIF (v_message = 'IGS_PS_PARTIALCREATION_OFFPAT') THEN
771
772 cst_all_cop_rec_inserted := FALSE;
773 cst_none_cop_rec_inserted := FALSE;
774 -- all course_offering_pattenrs rolled over
775 ELSIF (v_message = 'IGS_PS_SUCCESS_CREATION_POP') THEN
776 cst_none_cop_rec_inserted := FALSE;
777 END IF;
778 END IF; -- course_version is active
779 END LOOP;
780 IF (cst_none_cop_rec_inserted AND NOT cst_all_cop_rec_inserted) OR
781 (cst_all_cop_rec_inserted AND NOT cst_none_cop_rec_inserted) THEN
782 cst_partial_cop_rec_inserted := FALSE;
783 END IF;
784 -- none course_offering_instance is inserted
785 IF (v_rec_inserted_cnt = 0) THEN
786 cst_none_coi_rec_inserted := TRUE;
787 -- all course_offering_instance are inserted
788 ELSIF (v_rec_inserted_cnt = c_course_offering_instance%ROWCOUNT) THEN
789 cst_all_coi_rec_inserted := TRUE;
790 -- partial course_offering_instance are inserted
791 ELSE
792 cst_partial_coi_rec_inserted := TRUE;
793 END IF;
794 -- no course_offering_instances AND no course_offering_patterns are inserted
795 IF (cst_none_coi_rec_inserted AND cst_none_cop_rec_inserted) THEN
796 v_message := 'IGS_PS_NO_PRGOFFR_INST_FOUND';
797 RAISE VALID;
798 END IF;
799 -- (no coi AND all cop are inserted) OR (partial coi OR partial cop are
800 -- inserted)
801 IF (cst_none_coi_rec_inserted AND cst_all_cop_rec_inserted) OR
802 (cst_partial_coi_rec_inserted OR cst_partial_cop_rec_inserted) THEN
803 v_message := 'IGS_PS_PARTIALCREATION_OFFINS';
804 RAISE VALID;
805 END IF;
806 -- otherwise: (all coi and all cop are inserted) OR
807 -- (all coi and no cop are inserted)
808 v_message := 'IGS_PS_SUCCESS_CREAT_POP';
809 RAISE VALID;
810
811 EXCEPTION
812 WHEN VALID THEN
813 COMMIT;
814 RETCODE:=0;
815 fnd_file.put_line ( fnd_file.LOG, ' ');
816 fnd_file.put_line ( fnd_file.LOG, fnd_message.get_string('IGS',v_message) );
817 WHEN INVALID THEN
818 RETCODE:=2;
819 fnd_file.put_line ( fnd_file.LOG, ' ');
820 fnd_file.put_line ( fnd_file.LOG, fnd_message.get_string('IGS',v_message) );
821 WHEN OTHERS THEN
822 RETCODE:=2;
823 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
824 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
825 ROLLBACK;
826 END crsp_ins_ci_cop;
827
828
829 PROCEDURE crsp_ins_cous(
830 p_course_cd IN VARCHAR2 ,
831 p_crv_version_number IN NUMBER ,
832 p_cal_type IN VARCHAR2 ,
833 p_unit_set_cd IN VARCHAR2 ,
834 p_us_version_number IN NUMBER ,
835 p_override_title IN VARCHAR2 ,
836 p_only_as_sub_ind IN VARCHAR2 DEFAULT 'N')
837 AS
838 lv_param_values VARCHAR2(1080);
839 BEGIN -- crsp_ins_cous
840 -- This module inserts a record into the IGS_PS_OFR_UNIT_SET table.
841 -- The routine is used by the defaulting mechanism for a IGS_EN_UNIT_SET. This is
842 -- invoked from the 'Apply IGS_PS_UNIT Set to IGS_PS_COURSE Offerings' form (ie: CRSF4210)
843 -- and applies the unit_set_in context to all IGS_PS_COURSE offerings selected via
844 -- the screen.
845 -- The routine is not used by the IGS_EN_UNIT_SET rollover process.
846 DECLARE
847
848 v_administrative_ind IGS_EN_UNIT_SET.administrative_ind%TYPE;
849 v_show_on_official_ntfctn_ind VARCHAR2(1);
850 v_dummy VARCHAR2(1);
851 CURSOR c_cous IS
852 SELECT 'X'
853 FROM IGS_PS_OFR_UNIT_SET cous
854 WHERE cous.course_cd = p_course_cd AND
855 cous.crv_version_number = p_crv_version_number AND
856 cous.cal_type = p_cal_type AND
857 cous.unit_set_cd = p_unit_set_cd AND
858 cous.us_version_number = p_us_version_number;
859
860 x_rowid VARCHAR2(25);
861 BEGIN
862 -- Fetch the administrative indicator from the IGS_EN_UNIT_SET table
863 v_administrative_ind := IGS_PS_GEN_006.crsp_get_us_admin(
864 p_unit_set_cd,
865 p_us_version_number);
866 IF (v_administrative_ind = 'Y') THEN
867 v_show_on_official_ntfctn_ind := 'N';
868 ELSE
869 v_show_on_official_ntfctn_ind := 'Y';
870 END IF;
871 OPEN c_cous;
872 FETCH c_cous INTO v_dummy;
873 IF (c_cous%NOTFOUND) THEN
874 CLOSE c_cous;
875
876 IGS_PS_OFR_UNIT_SET_Pkg.Insert_Row(
877 X_ROWID => x_rowid,
878 X_COURSE_CD => p_course_cd,
879 X_CRV_VERSION_NUMBER => p_crv_version_number,
880 X_CAL_TYPE => p_cal_type,
881 X_UNIT_SET_CD => p_unit_set_cd,
882 X_US_VERSION_NUMBER => p_us_version_number,
883 X_OVERRIDE_TITLE => p_override_title,
884 X_ONLY_AS_SUB_IND => p_only_as_sub_ind,
885 X_SHOW_ON_OFFICIAL_NTFCTN_IND => v_show_on_official_ntfctn_ind,
886 X_MODE => 'R');
887
888 COMMIT;
889 ELSE
890 CLOSE c_cous;
891 END IF;
892 EXCEPTION
893 WHEN OTHERS THEN
894 IF (c_cous%ISOPEN) THEN
895 CLOSE c_cous;
896 END IF;
897 App_Exception.Raise_Exception;
898 END;
899 EXCEPTION
900 WHEN OTHERS THEN
901
902 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
903 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_003.crsp_ins_cous');
904 IGS_GE_MSG_STACK.ADD;
905 lv_param_values := p_course_cd||','||to_char(p_crv_version_number)||','||p_cal_type||','
906 ||p_unit_set_cd||','||to_char(p_us_version_number)||','||p_override_title||','||p_only_as_sub_ind;
907 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
908 Fnd_Message.Set_Token('VALUE',lv_param_values);
909 IGS_GE_MSG_STACK.ADD;
910 App_Exception.Raise_Exception;
911 END crsp_ins_cous;
912
913 FUNCTION enrollment_for_uoo_check ( p_n_uoo_id NUMBER) RETURN BOOLEAN AS
914 /***********************************************************************************************
915
916 Created By : sarakshi
917 Date Created By: 21-oct-2003
918
919 Purpose : To check if enrollment exists for a unit section, i.e to check if a record exists in IGS_EN_SU_ATTEMPT
920 for the input unit section
921
922 Known limitations,enhancements,remarks:
923 Change History
924 Who When What
925
926 ********************************************************************************************** */
927 CURSOR c_enroll (cp_n_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
928 SELECT 'X'
929 FROM igs_en_su_attempt
930 WHERE uoo_id=cp_n_uoo_id
931 AND ROWNUM=1;
932 l_c_var VARCHAR2(1) ;
933
934 BEGIN
935 --If enrollment exists for a unit section (uoo_id) then return TRUE else FALSE
936 OPEN c_enroll(p_n_uoo_id);
937 FETCH c_enroll INTO l_c_var;
938 IF c_enroll%FOUND THEN
939 CLOSE c_enroll;
940 RETURN TRUE;
941 END IF;
942 CLOSE c_enroll;
943 RETURN FALSE;
944
945 END enrollment_for_uoo_check;
946
947 FUNCTION CheckValid(
948 p_n_uoo_id NUMBER,
949 p_n_usec_occurs_id NUMBER,
950 p_c_building_cd VARCHAR2,
951 p_c_room_cd VARCHAR2,
952 p_d_start_date DATE,
953 p_d_end_date DATE,
954 p_d_start_time DATE,
955 p_d_end_time DATE,
956 p_c_monday VARCHAR2,
957 p_c_tuesday VARCHAR2,
958 p_c_wednesday VARCHAR2,
959 p_c_thrusday VARCHAR2,
960 p_c_friday VARCHAR2,
961 p_c_saturday VARCHAR2,
962 p_c_sunday VARCHAR2,
963 p_called_from VARCHAR2,
964 p_c_clash_section OUT NOCOPY VARCHAR2,
965 p_c_clash_occurrence OUT NOCOPY VARCHAR2
966 )
967 RETURN BOOLEAN AS
968 /***********************************************************************************************
969
970 Created By : sarakshi
971 Date Created By: 17-May-2005
972
973 Purpose : To check if an occurrence is having a building and Room conflict with other occurrences which
974 are not in same crosslistd/meetwith group.
975
976 Known limitations,enhancements,remarks:
977 Change History
978 Who When What
979 --sarakshi 29-Sep-2005 Bug#4589117, changed the signature of the function CheckValid and related validation.
980 --sarakshi 26-Sep-2005 Bug#4589301, modified the signature of function CheckValid and related processing
981 ********************************************************************************************** */
982
983
984 CURSOR chk IS
985 SELECT uso.unit_section_occurrence_id, uso.uoo_id, crs.usec_x_listed_group_id, mwg.class_meet_group_id
986 FROM igs_ps_usec_occurs uso, igs_ps_usec_x_grpmem crs, igs_ps_uso_clas_meet mwg
987 WHERE uso.building_code = p_c_building_cd AND
988 uso.room_code = p_c_room_cd AND
989 (
990 TRUNC( uso.start_date ) BETWEEN TRUNC(p_d_start_date) AND TRUNC(p_d_end_date) OR
991 TRUNC(uso.end_date) BETWEEN TRUNC(p_d_start_date) AND TRUNC(p_d_end_date) OR
992 TRUNC(p_d_start_date) BETWEEN TRUNC(uso.start_date) AND TRUNC(uso.end_date) OR
993 TRUNC(p_d_end_date) BETWEEN TRUNC(uso.start_date) AND TRUNC(uso.end_date)
994 ) AND
995
996 (
997 (((TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(p_d_end_time,'HH24:MI'),'HH24:MI')) OR
998 (TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(p_d_end_time,'HH24:MI'),'HH24:MI'))) AND
999 (
1000 -- considering boundary conditions as no conflict
1001 (TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(p_d_end_time,'HH24:MI'),'HH24:MI')) AND
1002 (TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI'))))
1003
1004 OR
1005
1006 (((TO_DATE(TO_CHAR(p_d_start_time,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI')) OR
1007 (TO_DATE(TO_CHAR(p_d_end_time ,'HH24:MI'),'HH24:MI') BETWEEN TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI') AND TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI'))) AND
1008 (
1009 -- considering boundary conditions as no conflict
1010 (TO_DATE(TO_CHAR(p_d_start_time ,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(uso.end_time,'HH24:MI'),'HH24:MI')) AND
1011 (TO_DATE(TO_CHAR(p_d_end_time ,'HH24:MI'),'HH24:MI') <> TO_DATE(TO_CHAR(uso.start_time,'HH24:MI'),'HH24:MI'))))
1012
1013 ) AND
1014
1015
1016 (uso.monday = DECODE (p_c_monday,'Y','Y','-') OR
1017 uso.tuesday = DECODE (p_c_tuesday,'Y','Y','-') OR
1018 uso.wednesday = DECODE (p_c_wednesday,'Y','Y','-') OR
1019 uso.thursday = DECODE (p_c_thrusday,'Y','Y','-') OR
1020 uso.friday = DECODE (p_c_friday,'Y','Y','-') OR
1021 uso.saturday = DECODE (p_c_saturday,'Y','Y','-') OR
1022 uso.sunday = DECODE (p_c_sunday,'Y','Y','-')) AND
1023 uso.uoo_id=crs.uoo_id(+) AND
1024 uso.uoo_id=mwg.uoo_id(+) AND
1025 uso.unit_section_occurrence_id <> p_n_usec_occurs_id;
1026
1027 --check if member of cross listed group
1028 CURSOR chk_crs_list_grp (cp_usec_x_listed_group_id NUMBER) IS
1029 SELECT 'X'
1030 FROM igs_ps_usec_x_grpmem
1031 WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id
1032 AND uoo_id = p_n_uoo_id;
1033
1034 --check if member of meet with group
1035 CURSOR chk_meet_with_grp (cp_class_meet_group_id NUMBER) IS
1036 SELECT 'X'
1037 FROM igs_ps_uso_clas_meet
1038 WHERE class_meet_group_id = cp_class_meet_group_id
1039 AND uoo_id = p_n_uoo_id;
1040
1041 CURSOR c_occur(cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
1042 SELECT b.occurrence_identifier,a.unit_cd,a.version_number,a.location_cd,a.unit_class,c.alternate_code
1043 FROM igs_ps_unit_ofr_opt_all a, igs_ps_usec_occurs_all b, igs_ca_inst_all c
1044 WHERE a.uoo_id=b.uoo_id
1045 AND b.unit_section_occurrence_id=cp_unit_section_occurrence_id
1046 AND a.cal_type=c.cal_type
1047 AND a.ci_sequence_number=c.sequence_number;
1048
1049 l_c_occur c_occur%ROWTYPE;
1050
1051 l_c_valid BOOLEAN := TRUE;
1052 l_c_var VARCHAR2(1);
1053 BEGIN
1054 FOR chk_rec IN chk LOOP
1055 l_c_valid := FALSE;
1056
1057 IF p_called_from='OCCURRENCE' THEN
1058 IF chk_rec.usec_x_listed_group_id IS NOT NULL THEN
1059 OPEN chk_crs_list_grp (chk_rec.usec_x_listed_group_id);
1060 FETCH chk_crs_list_grp INTO l_c_var;
1061 IF chk_crs_list_grp%FOUND THEN
1062 l_c_valid := TRUE;
1063 END IF;
1064 CLOSE chk_crs_list_grp;
1065 END IF;
1066
1067 IF chk_rec.class_meet_group_id IS NOT NULL THEN
1068 OPEN chk_meet_with_grp (chk_rec.class_meet_group_id);
1069 FETCH chk_meet_with_grp INTO l_c_var;
1070 IF chk_meet_with_grp%FOUND THEN
1071 l_c_valid := TRUE;
1072 END IF;
1073 CLOSE chk_meet_with_grp;
1074 END IF;
1075 END IF;
1076
1077 IF NOT l_c_valid THEN
1078 OPEN c_occur(chk_rec.unit_section_occurrence_id);
1079 FETCH c_occur INTO l_c_occur;
1080 CLOSE c_occur;
1081 p_c_clash_section:='"'||l_c_occur.unit_cd||'-'||l_c_occur.version_number||'-'||l_c_occur.alternate_code||'-'||l_c_occur.location_cd||'-'||l_c_occur.unit_class||'"';
1082 p_c_clash_occurrence:='"'||l_c_occur.occurrence_identifier||'"';
1083 EXIT;
1084 END IF;
1085
1086 END LOOP;
1087
1088 IF l_c_valid THEN
1089 RETURN TRUE;
1090 ELSE
1091 RETURN FALSE;
1092 END IF;
1093
1094 END CheckValid;
1095
1096
1097 END IGS_PS_GEN_003;
[Home] [Help]