1 PACKAGE BODY igs_ps_validate_generic_pkg AS
2 /* $Header: IGSPS92B.pls 120.2 2006/02/19 08:19:03 sommukhe noship $ */
3
4 /***********************************************************************************************
5 Created By : Sanjeeb Rakshit, Somnath Mukherjee
6 Date Created By: 17-Jun-2005
7 Purpose : This package has the some validation function which will be called from sub processes,
8 in igs_ps_create_generic_pkg package.
9
10 Known limitations,enhancements,remarks:
11 Change History (in reverse chronological order)
12 Who When What
13 ********************************************************************************************** */
14
15 g_n_user_id igs_ps_unit_ver_all.created_by%TYPE := NVL(fnd_global.user_id,-1);
16 g_n_login_id igs_ps_unit_ver_all.last_update_login%TYPE := NVL(fnd_global.login_id,-1);
17
18
19 PROCEDURE validate_usec_notes(p_usec_notes_rec IN OUT NOCOPY igs_ps_generic_pub.usec_notes_rec_type,
20 p_n_uoo_id IN NUMBER)
21 AS
22 /***********************************************************************************************
23 Created By : Sommukhe
24 Date Created By:
25 Purpose : This function will do validations after inserting records of Unit Section NOTES.
26 .
27
28 Known limitations,enhancements,remarks:
29 Change History (in reverse chronological order)
30 Who When What
31 ********************************************************************************************** */
32 v_message_name VARCHAR2(30);
33 BEGIN
34 IF NOT igs_ps_val_unit.crsp_val_iud_uv_dtl(p_usec_notes_rec.unit_cd, p_usec_notes_rec.version_number,v_message_name ) THEN
35 fnd_message.set_name ( 'IGS', 'IGS_PS_NOCHG_UNITVER_DETAILS');
36 fnd_msg_pub.add;
37 p_usec_notes_rec.status := 'E';
38 END IF;
39
40 --Check if the unit section is NOT_OFFERED
41 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
42 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
43 fnd_msg_pub.add;
44 p_usec_notes_rec.status := 'E';
45 END IF;
46
47 END validate_usec_notes;
48
49
50 PROCEDURE validate_usec_assmnt ( p_usec_assmnt_rec IN OUT NOCOPY igs_ps_generic_pub.usec_assmnt_rec_type,
51 p_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
52 p_d_exam_start_time igs_ps_usec_as.exam_start_time%TYPE,
53 p_d_exam_end_time igs_ps_usec_as.exam_end_time%TYPE,
54 p_n_building_id NUMBER,
55 p_n_room_id NUMBER,
56 p_insert_update VARCHAR2)
57 AS
58 /***********************************************************************************************
59 Created By : Sommukhe
60 Date Created By:
61 Purpose : This function will do validations before inserting records of Unit Section Assesments.
62
63 Known limitations,enhancements,remarks:
64 Change History (in reverse chronological order)
65 Who When What
66 ********************************************************************************************** */
67 CURSOR c_check_host(cp_uoo_id IN igs_ps_us_exam_meet.exam_meet_group_id%TYPE) IS
68 SELECT host
69 FROM igs_ps_us_exam_meet
70 WHERE uoo_id = cp_uoo_id;
71
72 l_host igs_ps_us_exam_meet.host%TYPE;
73
74 CURSOR c_building(cp_building_id igs_ad_building_all.building_id%TYPE,
75 cp_location_cd igs_ad_building_all.location_cd%TYPE ) IS
76 SELECT 'X'
77 FROM igs_ad_building_all
78 WHERE building_id = cp_building_id
79 AND location_cd = cp_location_cd
80 AND closed_ind = 'N';
81
82 CURSOR c_room(cp_room_id igs_ad_room_all.room_id%TYPE,cp_building_id igs_ad_building_all.building_id%TYPE) IS
83 SELECT 'X'
84 FROM igs_ad_room_all
85 WHERE room_id = cp_room_id
86 AND building_id =cp_building_id
87 AND closed_ind = 'N';
88 l_c_var VARCHAR2(1);
89
90 BEGIN
91
92 --Check if the unit section is NOT_OFFERED
93 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
94 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
95 fnd_msg_pub.add;
96 p_usec_assmnt_rec.status := 'E';
97 END IF;
98
99 IF p_usec_assmnt_rec.final_exam_date IS NOT NULL
100 AND (p_d_exam_start_time IS NULL OR p_d_exam_end_time IS NULL ) THEN
101 fnd_message.set_name ('IGS','IGS_PS_XM_ST_END_TIME_ENTR' );
102 fnd_msg_pub.add;
103 p_usec_assmnt_rec.status := 'E';
104 END IF;
105
106 IF p_d_exam_end_time IS NOT NULL AND p_d_exam_start_time IS NOT NULL
107 AND (TO_NUMBER(TO_CHAR(p_d_exam_end_time,'HH24MI')) <
108 TO_NUMBER(TO_CHAR(p_d_exam_start_time,'HH24MI'))) THEN
109 fnd_message.set_name ( 'IGS','IGS_PS_XM_END_GR_ST_TM');
110 fnd_msg_pub.add;
111 p_usec_assmnt_rec.status := 'E';
112 END IF;
113
114 IF (p_d_exam_start_time IS NOT NULL OR p_d_exam_end_time IS NOT NULL) AND (p_usec_assmnt_rec.final_exam_date IS NULL ) THEN
115 fnd_message.set_name ('IGS','IGS_PS_FIN_XM_DT_ST_ET');
116 fnd_msg_pub.add;
117 p_usec_assmnt_rec.status := 'E';
118 END IF;
119
120 IF p_n_building_id IS NOT NULL THEN
121 OPEN c_building(p_n_building_id,p_usec_assmnt_rec.exam_location_cd);
122 FETCH c_building INTO l_c_var;
123 IF c_building%NOTFOUND THEN
124 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'BUILDING_CODE', 'LEGACY_TOKENS', FALSE);
125 p_usec_assmnt_rec.status := 'E';
126 END IF;
127 CLOSE c_building;
128 END IF;
129
130 IF p_n_building_id IS NOT NULL AND p_n_room_id IS NOT NULL THEN
131 OPEN c_room(p_n_room_id,p_n_building_id);
132 FETCH c_room INTO l_c_var;
133 IF c_room%NOTFOUND THEN
134 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'ROOM_CODE', 'LEGACY_TOKENS', FALSE);
135 p_usec_assmnt_rec.status := 'E';
136 END IF;
137 CLOSE c_room;
138 END IF;
139
140
141 IF p_insert_update = 'U' THEN
142 OPEN c_check_host(p_n_uoo_id );
143 FETCH c_check_host INTO l_host;
144 IF c_check_host%FOUND THEN
145 IF l_host = 'Y' THEN
146 fnd_message.set_name('IGS','IGS_PS_CHANGE_US_HOST_REM');
147 fnd_msg_pub.add;
148 p_usec_assmnt_rec.status := 'E';
149 END IF;
150 END IF;
151 CLOSE c_check_host;
152 END IF;
153
154 END validate_usec_assmnt;
155
156
157 PROCEDURE validate_tch_rsp_ovrd ( p_tch_rsp_ovrd_rec IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_ovrd_rec_type,
158 p_n_uoo_id IN NUMBER)
159 AS
160 /***********************************************************************************************
161 Created By : sommukhe
162 Date Created By:
163 Purpose :
164 Known limitations,enhancements,remarks:
165 Change History (in reverse chronological order)
166 Who When What
167 ********************************************************************************************** */
168 l_c_message VARCHAR2(30);
169 BEGIN
170 -- Check if unit status is inactive.
171 IF NOT igs_ps_val_unit.crsp_val_iud_uv_dtl(p_tch_rsp_ovrd_rec.unit_cd,p_tch_rsp_ovrd_rec.version_number,l_c_message) THEN
172 fnd_message.set_name ( 'IGS', 'IGS_PS_NOCHG_UNITVER_DETAILS' );
173 fnd_msg_pub.add;
174 p_tch_rsp_ovrd_rec.status := 'E';
175 END IF;
176
177 --Check if the unit section is NOT_OFFERED
178 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
179 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
180 fnd_msg_pub.add;
181 p_tch_rsp_ovrd_rec.status := 'E';
182 END IF;
183
184 -- Check if orgunit is inactive.
185 IF NOT igs_ps_val_crv.crsp_val_ou_sys_sts(p_tch_rsp_ovrd_rec.org_unit_cd,p_tch_rsp_ovrd_rec.ou_start_dt,l_c_message) THEN
186 fnd_message.set_name ( 'IGS', 'IGS_PS_ORGUNIT_STATUS_INACTIV' );
187 fnd_msg_pub.add;
188 p_tch_rsp_ovrd_rec.status := 'E';
189 END IF;
190 END validate_tch_rsp_ovrd;
191
192 ---validations before inserting/updating Unit Section Assessment item group records
193 PROCEDURE validate_as_us_ai_group (p_as_us_ai_group_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type,
194 p_n_uoo_id NUMBER)
195 AS
196 /***********************************************************************************************
197 Created By : sommukhe
198 Date Created By:
199 Purpose :
200 Known limitations,enhancements,remarks:
201 Change History (in reverse chronological order)
202 Who When What
203 ********************************************************************************************** */
204 CURSOR cur_usec (cp_uoo_id NUMBER) IS
205 SELECT 'X'
206 FROM igs_ps_unit_ofr_opt_all
207 WHERE uoo_id = cp_uoo_id
208 AND NVL(enrollment_actual,0) > 0;
209 l_c_var VARCHAR2(1);
210 l_c_message VARCHAR2(30);
211
212 BEGIN
213 IF p_as_us_ai_group_rec.final_formula_code IN ('ATLEAST_N', 'BEST_N') THEN
214 IF p_as_us_ai_group_rec.final_formula_qty IS NULL THEN
215 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'FINAL_FORMULA_QTY', 'LEGACY_TOKENS', FALSE);
216 p_as_us_ai_group_rec.status := 'E';
217 END IF;
218 END IF;
219
220 IF p_as_us_ai_group_rec.final_formula_code IN ('ATLEAST_N', 'BEST_N','WEIGHTED_AVERAGE') THEN
221 IF p_as_us_ai_group_rec.final_weight_qty IS NULL THEN
222 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'FINAL_WEIGHT_QTY', 'LEGACY_TOKENS', FALSE);
223 p_as_us_ai_group_rec.status := 'E';
224 END IF;
225 END IF;
226
227 IF (p_as_us_ai_group_rec.midterm_formula_code IS NOT NULL) AND (p_as_us_ai_group_rec.midterm_formula_code IN ('ATLEAST_N', 'BEST_N')) THEN
228 IF p_as_us_ai_group_rec.midterm_formula_qty IS NULL THEN
229 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'MIDTERM_FORMULA_QTY', 'LEGACY_TOKENS', FALSE);
230 p_as_us_ai_group_rec.status := 'E';
231 END IF;
232 END IF;
233
234 IF (p_as_us_ai_group_rec.midterm_formula_code IS NOT NULL) AND (p_as_us_ai_group_rec.midterm_formula_code IN ('ATLEAST_N', 'BEST_N','WEIGHTED_AVERAGE')) THEN
235 IF p_as_us_ai_group_rec.midterm_weight_qty IS NULL THEN
236 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'MIDTERM_WEIGHT_QTY', 'LEGACY_TOKENS', FALSE);
237 p_as_us_ai_group_rec.status := 'E';
238 END IF;
239 END IF;
240
241
242 --Final formula qty cannot have value when final formula code is null
243 IF p_as_us_ai_group_rec.final_formula_qty IS NOT NULL AND p_as_us_ai_group_rec.final_formula_code IS NULL THEN
244 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'FINAL_FORMULA_QTY', 'LEGACY_TOKENS', FALSE);
245 p_as_us_ai_group_rec.status := 'E';
246 END IF;
247
248 --Final weight qty cannot have value when final formula code is null
249 IF p_as_us_ai_group_rec.final_weight_qty IS NOT NULL AND p_as_us_ai_group_rec.final_formula_code IS NULL THEN
250 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'FINAL_WEIGHT_QTY', 'LEGACY_TOKENS', FALSE);
251 p_as_us_ai_group_rec.status := 'E';
252 END IF;
253
254 --Midterm weight qty cannot have value when Midterm formula code is null
255 IF p_as_us_ai_group_rec.midterm_weight_qty IS NOT NULL AND p_as_us_ai_group_rec.midterm_formula_code IS NULL THEN
256 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'MIDTERM_WEIGHT_QTY', 'LEGACY_TOKENS', FALSE);
257 p_as_us_ai_group_rec.status := 'E';
258 END IF;
259
260 --Midterm formula qty cannot have value when Midterm formula code is null
261 IF p_as_us_ai_group_rec.midterm_formula_qty IS NOT NULL AND p_as_us_ai_group_rec.midterm_formula_code IS NULL THEN
262 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'MIDTERM_FORMULA_QTY', 'LEGACY_TOKENS', FALSE);
263 p_as_us_ai_group_rec.status := 'E';
264 END IF;
265
266
267
268 IF (p_as_us_ai_group_rec.final_formula_code IS NOT NULL) AND (p_as_us_ai_group_rec.final_formula_code NOT IN ('ATLEAST_N', 'BEST_N','WEIGHTED_AVERAGE')) THEN
269 p_as_us_ai_group_rec.status := 'E';
270 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'FINAL_FORMULA_CODE', 'LEGACY_TOKENS', FALSE);
271 END IF;
272
273 IF (p_as_us_ai_group_rec.midterm_formula_code IS NOT NULL) AND (p_as_us_ai_group_rec.midterm_formula_code NOT IN ('ATLEAST_N', 'BEST_N','WEIGHTED_AVERAGE')) THEN
274 p_as_us_ai_group_rec.status := 'E';
275 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'MIDTERM_FORMULA_CODE', 'LEGACY_TOKENS', FALSE);
276 END IF;
277
278 --Check if enrollment exists against a unit section then import is not allowed
279 OPEN cur_usec(p_n_uoo_id) ;
280 FETCH cur_usec INTO l_c_var;
281 IF cur_usec%FOUND THEN
282 fnd_message.set_name ( 'IGS', 'IGS_PS_ENR_EXISTS_NO_IMPORT' );
283 fnd_msg_pub.add;
284 p_as_us_ai_group_rec.status := 'E';
285 END IF;
286 CLOSE cur_usec;
287
288 --check if location code is closed.
289 IF NOT igs_ps_val_uoo.crsp_val_loc_cd(p_as_us_ai_group_rec.location_cd,l_c_message) THEN
290 fnd_message.set_name ( 'IGS', l_c_message );
291 fnd_msg_pub.add;
292 p_as_us_ai_group_rec.status := 'E';
293 END IF;
294
295 -- Check if unit status is inactive.
296 IF NOT igs_ps_val_unit.crsp_val_iud_uv_dtl(p_as_us_ai_group_rec.unit_cd,p_as_us_ai_group_rec.version_number,l_c_message) THEN
297 fnd_message.set_name ( 'IGS', 'IGS_PS_NOCHG_UNITVER_DETAILS' );
298 fnd_msg_pub.add;
299 p_as_us_ai_group_rec.status := 'E';
300 END IF;
301
302 --Check if the unit section is NOT_OFFERED
303 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
304 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
305 fnd_msg_pub.add;
306 p_as_us_ai_group_rec.status := 'E';
307 END IF;
308
309 END validate_as_us_ai_group;
310
311 -- validations before inserting/updating Unit Section Assessment item records
312 PROCEDURE validate_unitass_item ( p_unitass_item_rec IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_rec_type,
313 p_cal_type IN VARCHAR2,
314 p_ci_sequence_number NUMBER,
315 p_n_uoo_id NUMBER,
316 p_insert VARCHAR2)
317 AS
318 /***********************************************************************************************
319 Created By : sommukhe
320 Date Created By:
321 Purpose :
322 Known limitations,enhancements,remarks:
323 Change History (in reverse chronological order)
324 Who When What
325 ********************************************************************************************** */
326
327 CURSOR cp_st_en_dt(cp_cal_type VARCHAR ,cp_seq_number NUMBER) IS
328 SELECT start_dt, end_dt
329 FROM igs_ca_inst
330 WHERE cal_type = cp_cal_type
331 AND sequence_number = cp_seq_number;
332
333 cp_st_en_dt_rec cp_st_en_dt%ROWTYPE;
334
335 CURSOR c_reference(cp_n_uoo_id NUMBER,cp_reference VARCHAR2) IS
336 SELECT 'X'
337 FROM igs_ps_unitass_item
338 WHERE uoo_id = cp_n_uoo_id
339 AND reference = cp_reference
340 AND logical_delete_dt IS NULL;
341
342 c_reference_rec c_reference%ROWTYPE;
343
344 CURSOR c_usec(cp_n_uoo_id NUMBER) IS
345 SELECT *
346 FROM igs_ps_unit_ofr_opt_all
347 WHERE uoo_id = cp_n_uoo_id;
348
349 c_usec_rec c_usec%ROWTYPE;
350
351 CURSOR c_action_dt(cp_assessment_id igs_as_assessmnt_itm.ass_id%TYPE,cp_uoo_id NUMBER,cp_sequence_number NUMBER) IS
352 SELECT *
353 FROM igs_ps_unitass_item
354 WHERE ass_id =cp_assessment_id
355 AND uoo_id =cp_uoo_id
356 AND sequence_number =cp_sequence_number ;
357
358 c_action_dt_rec c_action_dt%ROWTYPE;
359 l_c_message VARCHAR2(30);
360
361
362 CURSOR cur_assessment_id(cp_assessment_id igs_as_assessmnt_itm.ass_id%TYPE ) IS
363 SELECT assessment_type
364 FROM igs_as_assessmnt_itm
365 WHERE ass_id = cp_assessment_id;
366 l_cur_assessment_id cur_assessment_id%ROWTYPE;
367
368 l_grading_schema_cd igs_ps_unitass_item.grading_schema_cd%TYPE;
369 l_gs_version_number igs_ps_unitass_item.gs_version_number%TYPE;
370 l_description igs_as_grd_schema.description%TYPE;
371 l_approved VARCHAR2 (1);
372
373 CURSOR cur_grading_approved(cp_unit_cd VARCHAR2,cp_version_number NUMBER,cp_assessment_type VARCHAR2,cp_grading_schema_cd VARCHAR2,cp_gs_version_number NUMBER) IS
374 SELECT 'X'
375 FROM igs_as_appr_grd_sch agrs,
376 igs_as_grd_schema grd
377 WHERE grd.grading_schema_cd = agrs.grading_schema_cd
378 AND grd.version_number = agrs.gs_version_number
379 AND agrs.unit_cd = cp_unit_cd
380 AND agrs.version_number = cp_version_number
381 AND agrs.assessment_type = cp_assessment_type
382 AND agrs.closed_ind = 'N'
383 AND agrs.grading_schema_cd = cp_grading_schema_cd
384 AND agrs.gs_version_number = cp_gs_version_number;
385
386 CURSOR cur_grading_assess(cp_grading_schema_cd VARCHAR2,cp_gs_version_number NUMBER) IS
387 SELECT 'X'
388 FROM igs_as_grd_schema
389 WHERE grading_schema_cd = cp_grading_schema_cd
390 AND version_number = cp_gs_version_number
391 AND NVL(end_dt,SYSDATE) >= SYSDATE
392 AND grading_schema_type = 'ASSESSMENT_ITEM';
393
394 l_c_var VARCHAR2(1);
395 l_c_token_val fnd_new_messages.message_text%TYPE;
396
397 BEGIN
398
399 IF (p_unitass_item_rec.midterm_mandatory_type_code IS NOT NULL) AND (p_unitass_item_rec.midterm_mandatory_type_code NOT IN ('MANDATORY', 'MANDATORY_PASS','OPTIONAL')) THEN
400 p_unitass_item_rec.status := 'E';
401 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'MIDTERM_MANDATORY_TYPE_CD', 'LEGACY_TOKENS', FALSE);
402 END IF;
403
404 IF (p_unitass_item_rec.final_mandatory_type_code IS NOT NULL) AND (p_unitass_item_rec.final_mandatory_type_code NOT IN ('MANDATORY', 'MANDATORY_PASS','OPTIONAL')) THEN
405 p_unitass_item_rec.status := 'E';
406 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'FINAL_MANDATORY_TYPE_CD', 'LEGACY_TOKENS', FALSE);
407 END IF;
408
409 --If midterm_mandatory_type_code is not null then MIDTERM_WEIGHT_QTY is mandatory.
410 IF p_unitass_item_rec.midterm_mandatory_type_code IS NOT NULL THEN
411 IF p_unitass_item_rec.midterm_weight_qty_item IS NULL THEN
412 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'MIDTERM_WEIGHT_QTY', 'LEGACY_TOKENS', FALSE);
413 p_unitass_item_rec.status := 'E';
414 END IF;
415 END IF;
416
417 --If final_mandatory_type_code is not null then FINAL_WEIGHT_QTY is mandatory.
418 IF p_unitass_item_rec.final_mandatory_type_code IS NOT NULL THEN
419 IF p_unitass_item_rec.final_weight_qty_item IS NULL THEN
420 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'FINAL_WEIGHT_QTY', 'LEGACY_TOKENS', FALSE);
421 p_unitass_item_rec.status := 'E';
422 END IF;
423 END IF;
424
425
426 --Midterm weight qty cannot have value when Midterm mandatory Type code is null
427 IF p_unitass_item_rec.midterm_weight_qty_item IS NOT NULL AND p_unitass_item_rec.midterm_mandatory_type_code IS NULL THEN
428 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'MIDTERM_WEIGHT_QTY_ITEM', 'LEGACY_TOKENS', FALSE);
429 p_unitass_item_rec.status := 'E';
430 END IF;
431
432 --Final weight qty cannot have value when Final mandatory Type code is null
433 IF p_unitass_item_rec.final_weight_qty_item IS NOT NULL AND p_unitass_item_rec.final_mandatory_type_code IS NULL THEN
434 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'FINAL_WEIGHT_QTY_ITEM', 'LEGACY_TOKENS', FALSE);
435 p_unitass_item_rec.status := 'E';
436 END IF;
437
438
439
440 --Validate grading schema code
441 OPEN cur_assessment_id(p_unitass_item_rec.assessment_id);
442 FETCH cur_assessment_id INTO l_cur_assessment_id;
443 CLOSE cur_assessment_id;
444 igs_as_gen_003.get_default_grds (
445 x_unit_cd => p_unitass_item_rec.unit_cd,
446 x_version_number => p_unitass_item_rec.version_number,
447 x_assessment_type => l_cur_assessment_id.assessment_type,
448 x_grading_schema_cd => l_grading_schema_cd,
449 x_gs_version_number => l_gs_version_number,
450 x_description => l_description,
451 x_approved => l_approved
452 );
453 IF NVL (l_approved, 'N') = 'Y' THEN
454 OPEN cur_grading_approved(p_unitass_item_rec.unit_cd,p_unitass_item_rec.version_number,l_cur_assessment_id.assessment_type,p_unitass_item_rec.grading_schema_cd,p_unitass_item_rec.gs_version_number);
455 FETCH cur_grading_approved INTO l_c_var;
456 IF cur_grading_approved%NOTFOUND THEN
457 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'GRADINGS_SCHEMA_CD', 'LEGACY_TOKENS', FALSE);
458 p_unitass_item_rec.status := 'E';
459 END IF;
460 CLOSE cur_grading_approved;
461 ELSE
462 OPEN cur_grading_assess(p_unitass_item_rec.grading_schema_cd,p_unitass_item_rec.gs_version_number);
463 FETCH cur_grading_assess INTO l_c_var;
464 IF cur_grading_assess%NOTFOUND THEN
465 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'GRADINGS_SCHEMA_CD', 'LEGACY_TOKENS', FALSE);
466 p_unitass_item_rec.status := 'E';
467 END IF;
468 CLOSE cur_grading_assess;
469 END IF;
470
471 --Release date should be greater than or equal to due date
472 IF p_unitass_item_rec.release_date IS NOT NULL AND p_unitass_item_rec.due_dt IS NOT NULL THEN
473 IF p_unitass_item_rec.release_date < p_unitass_item_rec.due_dt THEN
474 fnd_message.set_name ( 'IGS', 'IGS_AS_DUE_DT_LESS_RELEASE_DT' );
475 fnd_msg_pub.add;
476 p_unitass_item_rec.status := 'E';
477 END IF;
478 END IF;
479
480
481 IF p_insert = 'I' THEN
482 OPEN c_reference(p_n_uoo_id,p_unitass_item_rec.reference);
483 FETCH c_reference INTO c_reference_rec;
484 IF c_reference%FOUND THEN
485 fnd_message.set_name ( 'IGS', 'IGS_PS_REF_USAI_UNIQUE_US' );
486 fnd_msg_pub.add;
487 p_unitass_item_rec.status := 'E';
488 END IF;
489 CLOSE c_reference;
490 ELSE
491 OPEN c_action_dt(p_unitass_item_rec.assessment_id,p_n_uoo_id,p_unitass_item_rec.sequence_number);
492 FETCH c_action_dt INTO c_action_dt_rec;
493 IF c_action_dt_rec.reference <>p_unitass_item_rec.reference THEN
494 OPEN c_reference(p_n_uoo_id,p_unitass_item_rec.reference);
495 FETCH c_reference INTO c_reference_rec;
496 IF c_reference%FOUND THEN
497 fnd_message.set_name ( 'IGS', 'IGS_PS_REF_USAI_UNIQUE_US' );
498 fnd_msg_pub.add;
499 p_unitass_item_rec.status := 'E';
500 END IF;
501 CLOSE c_reference;
502 END IF;
503 CLOSE c_action_dt;
504 END IF;
505
506
507
508 --If item is examinable then validate that reference is set
509 IF NVL (p_unitass_item_rec.reference, 'NULL666') = 'NULL666'
510 AND (igs_as_val_aiem.assp_val_ai_exmnbl (p_unitass_item_rec.assessment_id, l_c_message) OR igs_as_gen_002.assp_get_ai_s_type (p_unitass_item_rec.assessment_id) = 'ASSIGNMENT') THEN
511 fnd_message.set_name ( 'IGS', 'IGS_AS_REF_ASSITEM_EXAM' );
512 fnd_msg_pub.add;
513 p_unitass_item_rec.status := 'E';
514 END IF;
515
516 --If the assessment item is examinable,Validate that the reference number id unique within a UOP.Else if record has not been deleted, again validate the reference number.
517 IF igs_as_val_aiem.assp_val_ai_exmnbl (p_unitass_item_rec.assessment_id, l_c_message) = TRUE THEN
518 IF igs_as_val_uai.assp_val_uai_uniqref (p_unitass_item_rec.unit_cd,
519 p_unitass_item_rec.version_number,
520 p_cal_type, p_ci_sequence_number,
521 p_unitass_item_rec.sequence_number,
522 p_unitass_item_rec.reference,
523 p_unitass_item_rec.assessment_id,
524 l_c_message) = FALSE THEN
525 fnd_message.set_name ( 'IGS', l_c_message );
526 fnd_msg_pub.add;
527 p_unitass_item_rec.status := 'E';
528 ELSIF NVL(p_unitass_item_rec.logical_delete_dt, igs_ge_date.igsdate ('1900/01/01')) = igs_ge_date.igsdate ('1900/01/01') THEN
529 IF igs_ps_val_uai.assp_val_uai_opt_ref (
530 p_unitass_item_rec.unit_cd,
531 p_unitass_item_rec.version_number,
532 p_cal_type, p_ci_sequence_number,
533 p_unitass_item_rec.sequence_number,
534 p_unitass_item_rec.reference,
535 p_unitass_item_rec.assessment_id,
536 igs_as_gen_001.assp_get_ai_a_type (p_unitass_item_rec.assessment_id),
537 l_c_message) = FALSE THEN
538
539 fnd_message.set_name ( 'IGS', l_c_message );
540 fnd_msg_pub.add;
541 p_unitass_item_rec.status := 'E';
542 END IF;
543 END IF;
544 END IF;
545
546 --validations on Due date
547 OPEN c_usec(p_n_uoo_id);
548 FETCH c_usec INTO c_usec_rec;
549 OPEN cp_st_en_dt(p_cal_type, p_ci_sequence_number);
550 FETCH cp_st_en_dt INTO cp_st_en_dt_rec;
551 IF p_unitass_item_rec.due_dt IS NOT NULL THEN
552 IF c_usec_rec.unit_section_start_date IS NOT NULL
553 OR c_usec_rec.unit_section_end_date IS NOT NULL THEN
554 IF c_usec_rec.unit_section_start_date IS NULL THEN
555 IF p_unitass_item_rec.due_dt < TRUNC (cp_st_en_dt_rec.start_dt)
556 OR p_unitass_item_rec.due_dt > TRUNC (c_usec_rec.unit_section_end_date) THEN
557 fnd_message.set_name ('IGS', 'IGS_PS_USEC_EFFET_DATES');
558 l_c_token_val:= fnd_message.get;
559
560 fnd_message.set_name ('IGS', 'IGS_PS_UA_DUDT_IN_TP');
561 fnd_message.set_token ('PERIOD', l_c_token_val);
562 fnd_msg_pub.add;
563 p_unitass_item_rec.status := 'E';
564
565 END IF;
566 ELSIF c_usec_rec.unit_section_end_date IS NULL THEN
567 IF p_unitass_item_rec.due_dt < TRUNC (c_usec_rec.unit_section_start_date)
568 OR p_unitass_item_rec.due_dt > TRUNC (cp_st_en_dt_rec.end_dt) THEN
569 fnd_message.set_name ('IGS', 'IGS_PS_USEC_EFFET_DATES');
570 l_c_token_val:= fnd_message.get;
571
572 fnd_message.set_name ('IGS', 'IGS_PS_UA_DUDT_IN_TP');
573 fnd_message.set_token ('PERIOD', l_c_token_val);
574 fnd_msg_pub.add;
575 p_unitass_item_rec.status := 'E';
576
577 END IF;
578 ELSE
579 IF p_unitass_item_rec.due_dt < TRUNC (c_usec_rec.unit_section_start_date)
580 OR p_unitass_item_rec.due_dt > TRUNC (c_usec_rec.unit_section_end_date) THEN
581 fnd_message.set_name ('IGS', 'IGS_PS_USEC_EFFET_DATES');
582 l_c_token_val:= fnd_message.get;
583
584 fnd_message.set_name ('IGS', 'IGS_PS_UA_DUDT_IN_TP');
585 fnd_message.set_token ('PERIOD', l_c_token_val);
586 fnd_msg_pub.add;
587 p_unitass_item_rec.status := 'E';
588
589 END IF;
590 END IF;
591 ELSE
592 IF p_unitass_item_rec.due_dt < TRUNC (cp_st_en_dt_rec.start_dt)
593 OR p_unitass_item_rec.due_dt > TRUNC (cp_st_en_dt_rec.end_dt) THEN
594 fnd_message.set_name ('IGS', 'IGS_PS_TP_DATES');
595 l_c_token_val:= fnd_message.get;
596
597 fnd_message.set_name ('IGS', 'IGS_PS_UA_DUDT_IN_TP');
598 fnd_message.set_token ('PERIOD', l_c_token_val);
599 fnd_msg_pub.add;
600 p_unitass_item_rec.status := 'E';
601 END IF;
602 END IF;
603 END IF;
604 CLOSE cp_st_en_dt;
605 CLOSE c_usec;
606
607 END validate_unitass_item;
608
609
610 PROCEDURE validate_uso_clas_meet ( p_uso_clas_meet_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type,
611 p_n_uoo_id NUMBER,
612 p_n_class_meet_group_id NUMBER,
613 p_c_cal_type VARCHAR2,
614 p_n_seq_num NUMBER)
615 AS
616 /***********************************************************************************************
617 Created By : sommukhe
618 Date Created By:
619 Purpose :
620
621 Known limitations,enhancements,remarks:
622 Change History (in reverse chronological order)
623 Who When What
624 ********************************************************************************************** */
625
626 CURSOR cur_usec IS
627 SELECT 'X'
628 FROM igs_ps_unit_ofr_opt_all uoo
629 WHERE uoo.unit_section_status IN ('OPEN','PLANNED','FULLWAITOK','CLOSED')
630 AND uoo.relation_type='NONE'
631 AND uoo.cal_type=p_c_cal_type
632 AND uoo.ci_sequence_number =p_n_seq_num
633 AND uoo.uoo_id=p_n_uoo_id;
634
635 cur_usec_rec cur_usec%ROWTYPE;
636
637 CURSOR cur_std_attempt(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
638 SELECT *
639 FROM igs_en_su_attempt
640 WHERE uoo_id=cp_uoo_id
641 AND unit_attempt_status='WAITLISTED';
642
643 CURSOR c_x_grpmem(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
644 SELECT 'X'
645 FROM igs_ps_usec_x_grpmem
646 WHERE uoo_id=cp_uoo_id;
647
648 c_x_grpmem_rec c_x_grpmem%ROWTYPE;
649
650 CURSOR cur_waitlist(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
651 SELECT waitlist_actual
652 FROM igs_ps_unit_ofr_opt
653 WHERE uoo_id=cp_uoo_id;
654 l_cur_waitlist cur_waitlist%ROWTYPE;
655
656 l_sysdate DATE := trunc(SYSDATE);
657
658
659
660 BEGIN
661
662 --Check if the unit section is NOT_OFFERED
663 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
664 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
665 fnd_msg_pub.add;
666 p_uso_clas_meet_rec.status := 'E';
667 END IF;
668
669 --Validation on selection of
670 OPEN cur_usec;
671 FETCH cur_usec INTO cur_usec_rec;
672 IF cur_usec%NOTFOUND THEN
673 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
674 p_uso_clas_meet_rec.status := 'E';
675 END IF;
676 CLOSE cur_usec;
677
678 --This unit section should not be present in any cross listed Unit section group
679 OPEN c_x_grpmem(p_n_uoo_id);
680 FETCH c_x_grpmem INTO c_x_grpmem_rec;
681 IF c_x_grpmem%FOUND THEN
682 fnd_message.set_name('IGS','IGS_PS_UNT_SEC_DEFINED_GRP');
683 fnd_msg_pub.add;
684 p_uso_clas_meet_rec.status := 'E';
685 END IF;
686 CLOSE c_x_grpmem;
687
688 OPEN cur_waitlist(p_n_uoo_id);
689 FETCH cur_waitlist INTO l_cur_waitlist;
690 IF l_cur_waitlist.waitlist_actual >= 1 THEN
691 FOR l_c_fetch_record_cur IN cur_std_attempt(p_n_uoo_id) LOOP
692
693 DECLARE
694 FUNCTION get_msg_from_stack(l_n_msg_count NUMBER) RETURN VARCHAR2 AS
695 l_c_msg VARCHAR2(3000);
696 l_c_msg_name fnd_new_messages.message_name%TYPE;
697 l_appl_name VARCHAR2(30);
698 BEGIN
699 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
700 FND_MESSAGE.SET_ENCODED (l_c_msg);
701 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_appl_name, l_c_msg_name);
702 RETURN l_c_msg_name;
703 END get_msg_from_stack;
704
705 BEGIN
706
707 IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT(
708 X_ROWID => l_c_fetch_record_cur.row_id,
709 x_waitlist_manual_ind => l_c_fetch_record_cur.waitlist_manual_ind,
710 X_PERSON_ID => l_c_fetch_record_cur.person_id,
711 X_COURSE_CD => l_c_fetch_record_cur.course_cd,
712 X_UNIT_CD => l_c_fetch_record_cur.unit_cd,
713 X_CAL_TYPE => l_c_fetch_record_cur.cal_type,
714 X_CI_SEQUENCE_NUMBER => l_c_fetch_record_cur.ci_sequence_number,
715 X_VERSION_NUMBER => l_c_fetch_record_cur.version_number,
716 X_LOCATION_CD => l_c_fetch_record_cur.location_cd,
717 X_UNIT_CLASS => l_c_fetch_record_cur.unit_class,
718 X_CI_START_DT => l_c_fetch_record_cur.ci_start_dt,
719 X_CI_END_DT => l_c_fetch_record_cur.ci_end_dt,
720 X_UOO_ID => l_c_fetch_record_cur.uoo_id,
721 X_ENROLLED_DT => l_c_fetch_record_cur.enrolled_dt,
722 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
723 X_ADMINISTRATIVE_UNIT_STATUS => l_c_fetch_record_cur.administrative_unit_status,
724 X_DISCONTINUED_DT => nvl(l_c_fetch_record_cur.discontinued_dt, l_sysdate),
725 X_RULE_WAIVED_DT => l_c_fetch_record_cur.rule_waived_dt,
726 X_RULE_WAIVED_PERSON_ID => l_c_fetch_record_cur.rule_waived_person_id,
727 X_NO_ASSESSMENT_IND => l_c_fetch_record_cur.no_assessment_ind,
728 X_SUP_UNIT_CD => l_c_fetch_record_cur.sup_unit_cd,
729 X_SUP_VERSION_NUMBER => l_c_fetch_record_cur.sup_version_number,
730 X_EXAM_LOCATION_CD => l_c_fetch_record_cur.exam_location_cd,
731 X_ALTERNATIVE_TITLE => l_c_fetch_record_cur.alternative_title,
732 X_OVERRIDE_ENROLLED_CP => l_c_fetch_record_cur.override_enrolled_cp,
733 X_OVERRIDE_EFTSU => l_c_fetch_record_cur.override_eftsu,
734 X_OVERRIDE_ACHIEVABLE_CP => l_c_fetch_record_cur.override_achievable_cp,
735 X_OVERRIDE_OUTCOME_DUE_DT => l_c_fetch_record_cur.override_outcome_due_dt,
736 X_OVERRIDE_CREDIT_REASON => l_c_fetch_record_cur.override_credit_reason,
737 X_ADMINISTRATIVE_PRIORITY => l_c_fetch_record_cur.administrative_priority,
738 X_WAITLIST_DT => l_c_fetch_record_cur.waitlist_dt,
739 X_DCNT_REASON_CD => l_c_fetch_record_cur.dcnt_reason_cd,
740 X_MODE => 'R',
741 X_GS_VERSION_NUMBER => l_c_fetch_record_cur.gs_version_number,
742 X_ENR_METHOD_TYPE => l_c_fetch_record_cur.enr_method_type,
743 X_FAILED_UNIT_RULE => l_c_fetch_record_cur.failed_unit_rule,
744 X_CART => l_c_fetch_record_cur.cart,
745 X_RSV_SEAT_EXT_ID => l_c_fetch_record_cur.rsv_seat_ext_id ,
746 X_ORG_UNIT_CD => l_c_fetch_record_cur.org_unit_cd,
747 X_SESSION_ID => l_c_fetch_record_cur.session_id,
748 X_GRADING_SCHEMA_CODE => l_c_fetch_record_cur.grading_schema_code,
749 X_DEG_AUD_DETAIL_ID => l_c_fetch_record_cur.deg_aud_detail_id,
750 X_SUBTITLE => l_c_fetch_record_cur.subtitle,
751 X_STUDENT_CAREER_TRANSCRIPT => l_c_fetch_record_cur.student_career_transcript,
752 X_STUDENT_CAREER_STATISTICS => l_c_fetch_record_cur.student_career_statistics,
753 X_ATTRIBUTE_CATEGORY => l_c_fetch_record_cur.attribute_category,
754 X_ATTRIBUTE1 => l_c_fetch_record_cur.attribute1,
755 X_ATTRIBUTE2 => l_c_fetch_record_cur.attribute2,
756 X_ATTRIBUTE3 => l_c_fetch_record_cur.attribute3,
757 X_ATTRIBUTE4 => l_c_fetch_record_cur.attribute4,
758 X_ATTRIBUTE5 => l_c_fetch_record_cur.attribute5,
759 X_ATTRIBUTE6 => l_c_fetch_record_cur.attribute6,
760 X_ATTRIBUTE7 => l_c_fetch_record_cur.attribute7,
761 X_ATTRIBUTE8 => l_c_fetch_record_cur.attribute8,
762 X_ATTRIBUTE9 => l_c_fetch_record_cur.attribute9,
763 X_ATTRIBUTE10 => l_c_fetch_record_cur.attribute10,
764 X_ATTRIBUTE11 => l_c_fetch_record_cur.attribute11,
765 X_ATTRIBUTE12 => l_c_fetch_record_cur.attribute12,
766 X_ATTRIBUTE13 => l_c_fetch_record_cur.attribute13,
767 X_ATTRIBUTE14 => l_c_fetch_record_cur.attribute14,
768 X_ATTRIBUTE15 => l_c_fetch_record_cur.attribute15,
769 X_ATTRIBUTE16 => l_c_fetch_record_cur.attribute16,
770 X_ATTRIBUTE17 => l_c_fetch_record_cur.attribute17,
771 X_ATTRIBUTE18 => l_c_fetch_record_cur.attribute18,
772 X_ATTRIBUTE19 => l_c_fetch_record_cur.attribute19,
773 X_ATTRIBUTE20 => l_c_fetch_record_cur.attribute20,
774 X_WLST_PRIORITY_WEIGHT_NUM => l_c_fetch_record_cur.wlst_priority_weight_num,
775 X_WLST_PREFERENCE_WEIGHT_NUM => l_c_fetch_record_cur.wlst_preference_weight_num,
776 X_CORE_INDICATOR_CODE => l_c_fetch_record_cur.core_indicator_code
777 ) ;
778 EXCEPTION
779 WHEN OTHERS THEN
780 fnd_message.set_name('IGS', get_msg_from_stack(1));
781 fnd_msg_pub.add;
782 p_uso_clas_meet_rec.status := 'E';
783 END;
784
785 END LOOP;
786 END IF;
787 CLOSE cur_waitlist;
788
789 END validate_uso_clas_meet;
790
791 PROCEDURE update_usec_status(p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE,
792 p_unit_section_status IN igs_ps_unit_ofr_opt.unit_section_status%TYPE) IS
793 CURSOR cur_usec(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
794 SELECT *
795 FROM igs_ps_unit_ofr_opt
796 WHERE uoo_id=cp_uoo_id;
797 l_cur_usec cur_usec%ROWTYPE;
798
799 BEGIN
800 OPEN cur_usec(p_uoo_id);
801 FETCH cur_usec INTO l_cur_usec;
802 CLOSE cur_usec;
803
804
805 igs_ps_unit_ofr_opt_pkg.update_row( x_rowid =>l_cur_usec.row_id,
806 x_unit_cd =>l_cur_usec.unit_cd,
807 x_version_number =>l_cur_usec.version_number,
808 x_cal_type =>l_cur_usec.cal_type,
809 x_ci_sequence_number =>l_cur_usec.ci_sequence_number,
810 x_location_cd =>l_cur_usec.location_cd,
811 x_unit_class =>l_cur_usec.unit_class,
812 x_uoo_id =>l_cur_usec.uoo_id,
813 x_ivrs_available_ind =>l_cur_usec.ivrs_available_ind,
814 x_call_number =>l_cur_usec.call_number,
815 x_unit_section_status =>p_unit_section_status,
816 x_unit_section_start_date =>l_cur_usec.unit_section_start_date,
817 x_unit_section_end_date =>l_cur_usec.unit_section_end_date,
818 x_enrollment_actual =>l_cur_usec.enrollment_actual,
819 x_waitlist_actual =>l_cur_usec.waitlist_actual,
820 x_offered_ind =>l_cur_usec.offered_ind,
821 x_state_financial_aid =>l_cur_usec.state_financial_aid,
822 x_grading_schema_prcdnce_ind =>l_cur_usec.grading_schema_prcdnce_ind,
823 x_federal_financial_aid =>l_cur_usec.federal_financial_aid,
824 x_unit_quota =>l_cur_usec.unit_quota,
825 x_unit_quota_reserved_places =>l_cur_usec.unit_quota_reserved_places,
826 x_institutional_financial_aid =>l_cur_usec.institutional_financial_aid,
827 x_grading_schema_cd =>l_cur_usec.grading_schema_cd,
828 x_gs_version_number =>l_cur_usec.gs_version_number,
829 x_unit_contact =>l_cur_usec.unit_contact,
830 x_mode =>'R',
831 x_ss_enrol_ind =>l_cur_usec.ss_enrol_ind,
832 x_owner_org_unit_cd => l_cur_usec.owner_org_unit_cd,
833 x_attendance_required_ind => l_cur_usec.attendance_required_ind,
834 x_reserved_seating_allowed => l_cur_usec.reserved_seating_allowed,
835 x_ss_display_ind => l_cur_usec.ss_display_ind,
836 x_special_permission_ind => l_cur_usec.special_permission_ind,
837 x_rev_account_cd => l_cur_usec.rev_account_cd ,
838 x_anon_unit_grading_ind => l_cur_usec.anon_unit_grading_ind,
839 x_anon_assess_grading_ind => l_cur_usec.anon_assess_grading_ind ,
840 x_non_std_usec_ind => l_cur_usec.non_std_usec_ind,
841 x_auditable_ind => l_cur_usec.auditable_ind,
842 x_audit_permission_ind => l_cur_usec.audit_permission_ind,
843 x_not_multiple_section_flag => l_cur_usec.not_multiple_section_flag,
844 x_sup_uoo_id => l_cur_usec.sup_uoo_id,
845 x_relation_type => l_cur_usec.relation_type,
846 x_default_enroll_flag => l_cur_usec.default_enroll_flag,
847 x_abort_flag => l_cur_usec.abort_flag
848 );
849 END update_usec_status;
850
851 FUNCTION post_usec_meet_with(p_tab_usec_meet_with IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_tbl_type,
852 p_class_meet_tab IN igs_ps_create_generic_pkg.class_meet_rec_tbl_type) RETURN BOOLEAN
853 AS
854 /***********************************************************************************************
855 Created By : sommukhe
856 Date Created By:
857 Purpose :
858 Known limitations,enhancements,remarks:
859 Change History (in reverse chronological order)
860 Who When What
861 ********************************************************************************************** */
862 CURSOR c_host(cp_class_meet_group_id NUMBER) IS
863 SELECT count (ROWID) cnt
864 FROM igs_ps_uso_clas_meet
865 WHERE host = 'Y'
866 AND class_meet_group_id =cp_class_meet_group_id;
867
868 c_host_rec c_host%ROWTYPE;
869
870 CURSOR c_cm_grp(cp_class_meet_group_id igs_ps_uso_clas_meet_v.class_meet_group_id%TYPE) IS
871 SELECT *
872 FROM igs_ps_uso_cm_grp
873 WHERE class_meet_group_id = cp_class_meet_group_id;
874
875 c_cm_grp_rec c_cm_grp%ROWTYPE;
876
877
878 CURSOR c_sum_enract(cp_class_meet_group_id igs_ps_uso_clas_meet_v.class_meet_group_id%TYPE) IS
879 SELECT SUM(enrollment_actual) sum_enrollment_actual
880 FROM igs_ps_uso_clas_meet_v
881 WHERE class_meet_group_id = cp_class_meet_group_id ;
882
883 c_sum_enract_rec c_sum_enract%ROWTYPE;
884
885 CURSOR c_usec_enr(cp_class_meet_group_id igs_ps_uso_clas_meet_v.class_meet_group_id%TYPE) IS
886 SELECT *
887 FROM igs_ps_uso_clas_meet_v
888 WHERE class_meet_group_id = cp_class_meet_group_id ;
889
890 c_usec_enr_rec c_usec_enr%ROWTYPE;
891
892 CURSOR cur_usec (cp_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE,
893 cp_usec_status igs_ps_unit_ofr_opt.unit_section_status%TYPE ) IS
894 SELECT uoo.uoo_id
895 FROM igs_ps_uso_clas_meet usm,
896 igs_ps_unit_ofr_opt uoo
897 WHERE usm.class_meet_group_id = cp_class_meet_group_id
898 AND usm.uoo_id=uoo.uoo_id
899 AND uoo.unit_section_status NOT IN (cp_usec_status,'PLANNED','CANCELLED','NOT_OFFERED');
900
901 CURSOR cur_group_id (cp_group_name VARCHAR2, cp_alternate_cd VARCHAR2) IS
902 SELECT a.class_meet_group_id
903 FROM igs_ps_uso_cm_grp a, igs_ca_inst_all b
904 WHERE a.class_meet_group_name = cp_group_name
905 AND a.cal_type = b.cal_type
906 AND a.ci_sequence_number =b.sequence_number
907 AND b.alternate_code=cp_alternate_cd;
908 l_cur_group_id NUMBER;
909
910
911 l_new_sum NUMBER;
912 l_execute_next_logic BOOLEAN;
913 l_n_count_msg NUMBER;
914 max_enr_group_temp igs_ps_uso_cm_grp.max_enr_group%TYPE;
915 max_ovr_group_temp igs_ps_uso_cm_grp.max_ovr_group%TYPE;
916 l_b_status BOOLEAN;
917 l_b_error BOOLEAN;
918 BEGIN
919
920 l_b_status := TRUE;
921 FOR i IN 1 ..p_class_meet_tab.LAST LOOP
922 l_b_error := FALSE;
923 OPEN c_host (p_class_meet_tab(i).class_meet_group_id);
924 FETCH c_host INTO c_host_rec;
925 CLOSE c_host;
926 IF NVL(c_host_rec.cnt,0) = 0 THEN
927 l_b_status := FALSE;
928 l_b_error := TRUE;
929 fnd_message.set_name ( 'IGS', 'IGS_PS_CM_NO_HOST' );
930 fnd_message.set_token('GROUP_NAME',p_class_meet_tab(i).class_meet_group_name);
931 fnd_msg_pub.add;
932 l_n_count_msg := fnd_msg_pub.count_msg;
933 ELSIF NVL(c_host_rec.cnt,0) > 1 THEN
934 l_b_status := FALSE;
935 l_b_error := TRUE;
936 fnd_message.set_name ( 'IGS', 'IGS_PS_CM_ONLY_ONE_HOST' );
937 fnd_msg_pub.add;
938 l_n_count_msg := fnd_msg_pub.count_msg;
939 END IF;
940 IF l_b_error THEN
941 FOR j in 1..p_tab_usec_meet_with.LAST LOOP
942 IF p_tab_usec_meet_with.EXISTS(j) THEN
943 OPEN cur_group_id(p_tab_usec_meet_with(j).class_meet_group_name,p_tab_usec_meet_with(j).teach_cal_alternate_code);
944 FETCH cur_group_id INTO l_cur_group_id;
945 CLOSE cur_group_id;
946 IF p_tab_usec_meet_with(j).status = 'S' AND l_cur_group_id = p_class_meet_tab(i).class_meet_group_id THEN
947 p_tab_usec_meet_with(j).status := 'E';
948 p_tab_usec_meet_with(j).msg_from := l_n_count_msg;
949 p_tab_usec_meet_with(j).msg_to := l_n_count_msg;
950 END IF;
951 END IF;
952 END LOOP;
953 END IF;
954
955 -- Group Override Maximum should always be greater than or equal to
956 -- Actual Enrollment for the group
957 OPEN c_sum_enract(p_class_meet_tab(i).class_meet_group_id);
958 FETCH c_sum_enract INTO c_sum_enract_rec;
959 CLOSE c_sum_enract;
960
961 OPEN c_cm_grp(p_class_meet_tab(i).class_meet_group_id);
962 FETCH c_cm_grp INTO c_cm_grp_rec;
963 CLOSE c_cm_grp;
964
965 IF ((c_cm_grp_rec.max_ovr_group IS NOT NULL) AND (NVL(c_cm_grp_rec.max_ovr_group,0) < NVL(c_sum_enract_rec.sum_enrollment_actual,0))) THEN
966 l_b_status := FALSE;
967 fnd_message.set_name('IGS','IGS_PS_MAX_OVR_MAX');
968 fnd_msg_pub.add;
969 l_n_count_msg := fnd_msg_pub.count_msg;
970 FOR j in 1..p_tab_usec_meet_with.LAST LOOP
971 IF p_tab_usec_meet_with.EXISTS(j) THEN
972 OPEN cur_group_id(p_tab_usec_meet_with(j).class_meet_group_name,p_tab_usec_meet_with(j).teach_cal_alternate_code);
973 FETCH cur_group_id INTO l_cur_group_id;
974 CLOSE cur_group_id;
975 IF p_tab_usec_meet_with(j).status = 'S' AND l_cur_group_id = p_class_meet_tab(i).class_meet_group_id THEN
976 p_tab_usec_meet_with(j).status := 'E';
977 p_tab_usec_meet_with(j).msg_from := l_n_count_msg;
978 p_tab_usec_meet_with(j).msg_to := l_n_count_msg;
979 END IF;
980 END IF;
981 END LOOP;
982 END IF;
983
984 IF l_b_status THEN
985 OPEN c_cm_grp(p_class_meet_tab(i).class_meet_group_id);
986 FETCH c_cm_grp INTO c_cm_grp_rec;
987 CLOSE c_cm_grp;
988
989 OPEN c_usec_enr(p_class_meet_tab(i).class_meet_group_id);
990 FETCH c_usec_enr INTO c_usec_enr_rec;
991 CLOSE c_usec_enr;
992
993 OPEN c_sum_enract(p_class_meet_tab(i).class_meet_group_id);
994 FETCH c_sum_enract INTO l_new_sum;
995 CLOSE c_sum_enract;
996
997 l_execute_next_logic:= TRUE;
998
999 IF c_cm_grp_rec.max_ovr_group IS NOT NULL OR c_cm_grp_rec.max_enr_group IS NOT NULL THEN--1o
1000 IF NVL(c_usec_enr_rec.enrollment_actual,0) >= 1 THEN--2o
1001 l_execute_next_logic:= FALSE;
1002 IF NVL(c_cm_grp_rec.max_enr_group,0) < NVL(l_new_sum,0) THEN--3o
1003 max_enr_group_temp:=l_new_sum;
1004 max_ovr_group_temp:=c_cm_grp_rec.max_ovr_group;
1005
1006 IF NVL(c_cm_grp_rec.max_ovr_group,0) < NVL(l_new_sum,0) THEN
1007 max_ovr_group_temp:=l_new_sum;
1008 END IF;
1009
1010 --Update the maximum enrollment/ovrride group
1011 UPDATE igs_ps_uso_cm_grp
1012 SET max_enr_group=max_enr_group_temp,
1013 max_ovr_group=max_ovr_group_temp,
1014 last_updated_by = g_n_user_id,
1015 last_update_date = SYSDATE,
1016 last_update_login = g_n_login_id
1017 WHERE class_meet_group_id =p_class_meet_tab(i).class_meet_group_id;
1018
1019 --Make all the unit section status to closed if open in that group
1020 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'CLOSED') LOOP
1021 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1022 END LOOP;
1023
1024 ELSIF NVL(c_cm_grp_rec.max_enr_group,0) > NVL(l_new_sum,0) THEN
1025 --Make this unit section status open if closed
1026 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'OPEN') LOOP
1027 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1028 END LOOP;
1029
1030 ELSE
1031 --Make all the unit section status to closed if open in that group
1032 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'CLOSED') LOOP
1033 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1034 END LOOP;
1035
1036 END IF;--3c
1037
1038 ELSE
1039 --If the inserted/modified unit section is not an enrolled one then also change the status
1040 --accordingly, bug#2702252
1041 IF NVL(c_cm_grp_rec.max_enr_group,0) < NVL(l_new_sum,0) THEN--4o
1042 --Make all the unit section status to closed if open in that group
1043 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'CLOSED') LOOP
1044 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1045 END LOOP;
1046
1047 ELSIF NVL(c_cm_grp_rec.max_enr_group,0) > NVL(l_new_sum,0) THEN
1048 --Make this unit section status open if closed
1049 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'OPEN') LOOP
1050 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1051 END LOOP;
1052
1053 ELSIF c_cm_grp_rec.max_enr_group IS NULL AND l_new_sum IS NULL THEN
1054 --Make all the unit section status to open if closed in that group
1055 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'OPEN') LOOP
1056 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1057 END LOOP;
1058
1059 ELSE
1060 --Make all the unit section status to closed if open in that group
1061 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'CLOSED') LOOP
1062 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1063 END LOOP;
1064
1065 END IF;--4c
1066 END IF; --2c
1067 END IF;--1c
1068
1069 IF l_execute_next_logic = TRUE THEN
1070 IF NVL(p_class_meet_tab(i).old_max_enr_group ,0) <> NVL(c_cm_grp_rec.max_enr_group,0) THEN
1071 IF p_class_meet_tab(i).old_max_enr_group = l_new_sum THEN
1072 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'OPEN') LOOP
1073 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1074 END LOOP;
1075
1076 ELSIF l_new_sum = c_cm_grp_rec.max_enr_group THEN
1077 FOR l_cur_usec IN cur_usec(p_class_meet_tab(i).class_meet_group_id,'CLOSED') LOOP
1078 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1079 END LOOP;
1080 END IF;
1081 END IF;
1082 END IF;
1083
1084 END IF; --l_b_status
1085
1086 END LOOP;
1087
1088 RETURN l_b_status;
1089
1090 END post_usec_meet_with;
1091
1092
1093 PROCEDURE validate_uso_cm_grp ( p_uso_cm_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_meet_with_rec_type,
1094 p_c_cal_type VARCHAR2,
1095 p_n_seq_num NUMBER,
1096 p_insert_update VARCHAR2,
1097 p_class_meet_rec IN OUT NOCOPY igs_ps_create_generic_pkg.class_meet_rec_type )
1098 AS
1099 /***********************************************************************************************
1100 Created By : sommukhe
1101 Date Created By: 18-Jun-2005
1102 Purpose :
1103
1104 Known limitations,enhancements,remarks:
1105 Change History (in reverse chronological order)
1106 Who When What
1107 ********************************************************************************************** */
1108 CURSOR c_uso_cm_grp(cp_cmg_name VARCHAR2) IS
1109 SELECT 'X' FROM igs_ps_uso_cm_grp
1110 WHERE class_meet_group_name =cp_cmg_name;
1111
1112 c_uso_cm_grp_rec c_uso_cm_grp%ROWTYPE;
1113
1114 CURSOR c_val_enr(cp_cmg_name VARCHAR2,cp_cal_type VARCHAR2,cp_sequence_number NUMBER) IS
1115 SELECT *
1116 FROM igs_ps_uso_cm_grp
1117 WHERE class_meet_group_name =cp_cmg_name
1118 AND Cal_type=cp_cal_type
1119 AND ci_sequence_number=cp_sequence_number;
1120
1121 c_val_enr_rec c_val_enr%ROWTYPE;
1122
1123 CURSOR c_old_cmgrp(cp_cmg_name VARCHAR2,cp_cal_type VARCHAR2,cp_seq_no NUMBER) IS
1124 SELECT * FROM igs_ps_uso_cm_grp
1125 WHERE class_meet_group_name =cp_cmg_name
1126 AND cal_type=cp_cal_type
1127 AND ci_sequence_number=cp_seq_no;
1128
1129 c_old_cmgrp_rec c_old_cmgrp%ROWTYPE;
1130
1131 CURSOR cur_usec (cp_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE,
1132 cp_usec_status igs_ps_unit_ofr_opt.unit_section_status%TYPE ) IS
1133 SELECT uoo.uoo_id
1134 FROM igs_ps_uso_clas_meet usm,
1135 igs_ps_unit_ofr_opt uoo
1136 WHERE usm.class_meet_group_id = cp_class_meet_group_id
1137 AND usm.uoo_id=uoo.uoo_id
1138 AND uoo.unit_section_status NOT IN (cp_usec_status,'PLANNED','CANCELLED','NOT_OFFERED');
1139
1140 CURSOR cur_usecs_in_group(cp_class_meet_group_id igs_ps_uso_clas_meet.class_meet_group_id%TYPE) IS
1141 SELECT uoo_id
1142 FROM igs_ps_uso_clas_meet
1143 WHERE class_meet_group_id = cp_class_meet_group_id;
1144
1145 l_message_name fnd_new_messages.message_name%TYPE;
1146 l_request_id igs_ps_sch_hdr_int.request_id%TYPE;
1147
1148 BEGIN
1149
1150 --Cannot enter a value for Override Maximum when Enrollment Maximum is null
1151 IF ((p_uso_cm_grp_rec.max_ovr_group IS NOT NULL) AND (p_uso_cm_grp_rec.max_enr_group IS NULL)) THEN
1152 fnd_message.set_name ( 'IGS', 'IGS_PS_ENR_NULL_OVR_NOT' );
1153 fnd_msg_pub.add;
1154 p_uso_cm_grp_rec.status := 'E';
1155 END IF;
1156
1157 --Group Override Maximum should always be greater than or equal toMaximum Enrollment for the group
1158 IF ((p_uso_cm_grp_rec.max_ovr_group IS NOT NULL) AND (NVL(p_uso_cm_grp_rec.max_ovr_group,0) < NVL(p_uso_cm_grp_rec.max_enr_group,0))) THEN
1159 fnd_message.set_name ( 'IGS', 'IGS_PS_OVERIDE_MIN_MAX_CP' );
1160 fnd_msg_pub.add;
1161 p_uso_cm_grp_rec.status := 'E';
1162 END IF;
1163
1164
1165 p_class_meet_rec.class_meet_group_name:=p_uso_cm_grp_rec.class_meet_group_name;
1166
1167 OPEN c_old_cmgrp(p_uso_cm_grp_rec.class_meet_group_name,p_c_cal_type,p_n_seq_num);
1168 FETCH c_old_cmgrp INTO c_old_cmgrp_rec;
1169 IF c_old_cmgrp%FOUND THEN
1170 p_class_meet_rec.class_meet_group_id:=c_old_cmgrp_rec.class_meet_group_id;
1171 p_class_meet_rec.old_max_enr_group:=c_old_cmgrp_rec.max_enr_group;
1172 ELSE
1173 p_class_meet_rec.class_meet_group_id:=NULL;
1174 p_class_meet_rec.old_max_enr_group:=NULL;
1175 END IF;
1176 CLOSE c_old_cmgrp;
1177
1178
1179 -- IF the group enrollment is getting modified
1180 OPEN c_val_enr(p_uso_cm_grp_rec.class_meet_group_name,p_c_cal_type,p_n_seq_num);
1181 FETCH c_val_enr INTO c_val_enr_rec;
1182
1183 IF c_val_enr%FOUND THEN
1184 IF NVL( p_uso_cm_grp_rec.max_enr_group,0) <> c_val_enr_rec.max_enr_group OR
1185 NVL(p_uso_cm_grp_rec.max_ovr_group,0) <> c_val_enr_rec.max_ovr_group THEN
1186
1187 --for loop which loops thru all unit sections within a group.
1188 FOR rec_cur_usecs_in_group IN cur_usecs_in_group(c_val_enr_rec.class_meet_group_id) LOOP
1189 --Before updating check for validity of schedule status...
1190 IF igs_ps_usec_schedule.prgp_get_schd_status(p_uoo_id => rec_cur_usecs_in_group.uoo_id,
1191 p_usec_id => NULL,
1192 p_message_name => l_message_name ) = TRUE THEN
1193 IF l_message_name IS NULL THEN
1194 l_message_name := 'IGS_PS_SCST_PROC';
1195 END IF;
1196
1197 fnd_message.set_name('IGS',l_message_name);
1198 fnd_msg_pub.add;
1199 p_uso_cm_grp_rec.status := 'E';
1200 --update the schedule status to 'Rescheduling Requested', if unit section occurrence is a scheduled one.
1201 ELSIF igs_ps_usec_schedule.prgp_upd_usec_dtls
1202 (
1203 p_uoo_id => rec_cur_usecs_in_group.uoo_id,
1204 p_location_cd => NULL,
1205 p_usec_status => NULL,
1206 p_max_enrollments => NVL(p_uso_cm_grp_rec.max_enr_group,-999),
1207 p_override_enrollment_max => NVL(p_uso_cm_grp_rec.max_ovr_group,-999),
1208 p_enrollment_expected => NULL,
1209 p_request_id => l_request_id,
1210 p_message_name => l_message_name
1211 ) = FALSE THEN
1212 fnd_message.set_name('IGS',l_message_name);
1213 fnd_msg_pub.add;
1214 p_uso_cm_grp_rec.status := 'E';
1215 END IF;
1216 END LOOP;
1217 END IF;
1218 END IF;
1219 CLOSE c_val_enr;
1220
1221 END validate_uso_cm_grp;
1222
1223
1224 --Validations of crosslisted unit sections
1225 PROCEDURE validate_usec_x_grpmem ( p_usec_x_grpmem IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type,
1226 p_n_uoo_id NUMBER,
1227 p_n_usec_x_listed_group_id NUMBER,
1228 p_c_cal_type VARCHAR2,
1229 p_n_seq_num NUMBER)
1230 AS
1231 /***********************************************************************************************
1232 Created By : sommukhe
1233 Date Created By:
1234 Purpose :
1235
1236 Known limitations,enhancements,remarks:
1237 Change History (in reverse chronological order)
1238 Who When What
1239 ********************************************************************************************** */
1240
1241 CURSOR cur_usec IS
1242 SELECT 'X'
1243 FROM igs_ps_unit_ofr_opt_all uoo
1244 WHERE uoo.unit_section_status IN ('OPEN','PLANNED','FULLWAITOK','CLOSED')
1245 AND uoo.relation_type='NONE'
1246 AND uoo.cal_type=p_c_cal_type
1247 AND uoo.ci_sequence_number =p_n_seq_num
1248 AND uoo.uoo_id=p_n_uoo_id;
1249
1250 cur_usec_rec cur_usec%ROWTYPE;
1251
1252 CURSOR c_uso_cm(cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
1253 SELECT 'X'
1254 FROM igs_ps_uso_clas_meet
1255 WHERE uoo_id=cp_uoo_id;
1256
1257 c_uso_cm_rec c_uso_cm%ROWTYPE;
1258
1259 CURSOR cur_std_attempt(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
1260 SELECT *
1261 FROM igs_en_su_attempt
1262 WHERE uoo_id=cp_uoo_id
1263 AND unit_attempt_status='WAITLISTED';
1264
1265 CURSOR cur_waitlist(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1266 SELECT waitlist_actual
1267 FROM igs_ps_unit_ofr_opt
1268 WHERE uoo_id=cp_uoo_id;
1269 l_cur_waitlist cur_waitlist%ROWTYPE;
1270
1271 l_sysdate DATE := trunc(SYSDATE);
1272
1273 BEGIN
1274
1275 --Check if the unit section is NOT_OFFERED
1276 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
1277 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
1278 fnd_msg_pub.add;
1279 p_usec_x_grpmem.status := 'E';
1280 END IF;
1281
1282
1283 --Validation on selection of
1284 OPEN cur_usec;
1285 FETCH cur_usec INTO cur_usec_rec;
1286 IF cur_usec%NOTFOUND THEN
1287 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'UNIT_SECTION', 'LEGACY_TOKENS', FALSE);
1288 p_usec_x_grpmem.status := 'E';
1289 END IF;
1290 CLOSE cur_usec;
1291
1292 --This unit section should not be present in any Unit section meet with class group
1293 OPEN c_uso_cm(p_n_uoo_id);
1294 FETCH c_uso_cm INTO c_uso_cm_rec;
1295 IF c_uso_cm%FOUND THEN
1296 fnd_message.set_name('IGS','IGS_PS_UNT_SEC_DEFINED_GRP');
1297 fnd_msg_pub.add;
1298 p_usec_x_grpmem.status := 'E';
1299 END IF;
1300 CLOSE c_uso_cm;
1301
1302 OPEN cur_waitlist(p_n_uoo_id);
1303 FETCH cur_waitlist INTO l_cur_waitlist;
1304 IF l_cur_waitlist.waitlist_actual >= 1 THEN
1305 FOR l_c_fetch_record_cur IN cur_std_attempt(p_n_uoo_id) LOOP
1306
1307 DECLARE
1308 FUNCTION get_msg_from_stack(l_n_msg_count NUMBER) RETURN VARCHAR2 AS
1309 l_c_msg VARCHAR2(3000);
1310 l_c_msg_name fnd_new_messages.message_name%TYPE;
1311 l_appl_name VARCHAR2(30);
1312 BEGIN
1313 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
1314 FND_MESSAGE.SET_ENCODED (l_c_msg);
1315 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_appl_name, l_c_msg_name);
1316 RETURN l_c_msg_name;
1317 END get_msg_from_stack;
1318 BEGIN
1319
1320 IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT(
1321 X_ROWID => l_c_fetch_record_cur.row_id,
1322 x_waitlist_manual_ind => l_c_fetch_record_cur.waitlist_manual_ind,
1323 X_PERSON_ID => l_c_fetch_record_cur.person_id,
1324 X_COURSE_CD => l_c_fetch_record_cur.course_cd,
1325 X_UNIT_CD => l_c_fetch_record_cur.unit_cd,
1326 X_CAL_TYPE => l_c_fetch_record_cur.cal_type,
1327 X_CI_SEQUENCE_NUMBER => l_c_fetch_record_cur.ci_sequence_number,
1328 X_VERSION_NUMBER => l_c_fetch_record_cur.version_number,
1329 X_LOCATION_CD => l_c_fetch_record_cur.location_cd,
1330 X_UNIT_CLASS => l_c_fetch_record_cur.unit_class,
1331 X_CI_START_DT => l_c_fetch_record_cur.ci_start_dt,
1332 X_CI_END_DT => l_c_fetch_record_cur.ci_end_dt,
1333 X_UOO_ID => l_c_fetch_record_cur.uoo_id,
1334 X_ENROLLED_DT => l_c_fetch_record_cur.enrolled_dt,
1335 X_UNIT_ATTEMPT_STATUS => 'DROPPED',
1336 X_ADMINISTRATIVE_UNIT_STATUS => l_c_fetch_record_cur.administrative_unit_status,
1337 X_DISCONTINUED_DT => nvl(l_c_fetch_record_cur.discontinued_dt, l_sysdate),
1338 X_RULE_WAIVED_DT => l_c_fetch_record_cur.rule_waived_dt,
1339 X_RULE_WAIVED_PERSON_ID => l_c_fetch_record_cur.rule_waived_person_id,
1340 X_NO_ASSESSMENT_IND => l_c_fetch_record_cur.no_assessment_ind,
1341 X_SUP_UNIT_CD => l_c_fetch_record_cur.sup_unit_cd,
1342 X_SUP_VERSION_NUMBER => l_c_fetch_record_cur.sup_version_number,
1343 X_EXAM_LOCATION_CD => l_c_fetch_record_cur.exam_location_cd,
1344 X_ALTERNATIVE_TITLE => l_c_fetch_record_cur.alternative_title,
1345 X_OVERRIDE_ENROLLED_CP => l_c_fetch_record_cur.override_enrolled_cp,
1346 X_OVERRIDE_EFTSU => l_c_fetch_record_cur.override_eftsu,
1347 X_OVERRIDE_ACHIEVABLE_CP => l_c_fetch_record_cur.override_achievable_cp,
1348 X_OVERRIDE_OUTCOME_DUE_DT => l_c_fetch_record_cur.override_outcome_due_dt,
1349 X_OVERRIDE_CREDIT_REASON => l_c_fetch_record_cur.override_credit_reason,
1350 X_ADMINISTRATIVE_PRIORITY => l_c_fetch_record_cur.administrative_priority,
1351 X_WAITLIST_DT => l_c_fetch_record_cur.waitlist_dt,
1352 X_DCNT_REASON_CD => l_c_fetch_record_cur.dcnt_reason_cd,
1353 X_MODE => 'R',
1354 X_GS_VERSION_NUMBER => l_c_fetch_record_cur.gs_version_number,
1355 X_ENR_METHOD_TYPE => l_c_fetch_record_cur.enr_method_type,
1356 X_FAILED_UNIT_RULE => l_c_fetch_record_cur.failed_unit_rule,
1357 X_CART => l_c_fetch_record_cur.cart,
1358 X_RSV_SEAT_EXT_ID => l_c_fetch_record_cur.rsv_seat_ext_id ,
1359 X_ORG_UNIT_CD => l_c_fetch_record_cur.org_unit_cd,
1360 X_SESSION_ID => l_c_fetch_record_cur.session_id,
1361 X_GRADING_SCHEMA_CODE => l_c_fetch_record_cur.grading_schema_code,
1362 X_DEG_AUD_DETAIL_ID => l_c_fetch_record_cur.deg_aud_detail_id,
1363 X_SUBTITLE => l_c_fetch_record_cur.subtitle,
1364 X_STUDENT_CAREER_TRANSCRIPT => l_c_fetch_record_cur.student_career_transcript,
1365 X_STUDENT_CAREER_STATISTICS => l_c_fetch_record_cur.student_career_statistics,
1366 X_ATTRIBUTE_CATEGORY => l_c_fetch_record_cur.attribute_category,
1367 X_ATTRIBUTE1 => l_c_fetch_record_cur.attribute1,
1368 X_ATTRIBUTE2 => l_c_fetch_record_cur.attribute2,
1369 X_ATTRIBUTE3 => l_c_fetch_record_cur.attribute3,
1370 X_ATTRIBUTE4 => l_c_fetch_record_cur.attribute4,
1371 X_ATTRIBUTE5 => l_c_fetch_record_cur.attribute5,
1372 X_ATTRIBUTE6 => l_c_fetch_record_cur.attribute6,
1373 X_ATTRIBUTE7 => l_c_fetch_record_cur.attribute7,
1374 X_ATTRIBUTE8 => l_c_fetch_record_cur.attribute8,
1375 X_ATTRIBUTE9 => l_c_fetch_record_cur.attribute9,
1376 X_ATTRIBUTE10 => l_c_fetch_record_cur.attribute10,
1377 X_ATTRIBUTE11 => l_c_fetch_record_cur.attribute11,
1378 X_ATTRIBUTE12 => l_c_fetch_record_cur.attribute12,
1379 X_ATTRIBUTE13 => l_c_fetch_record_cur.attribute13,
1380 X_ATTRIBUTE14 => l_c_fetch_record_cur.attribute14,
1381 X_ATTRIBUTE15 => l_c_fetch_record_cur.attribute15,
1382 X_ATTRIBUTE16 => l_c_fetch_record_cur.attribute16,
1383 X_ATTRIBUTE17 => l_c_fetch_record_cur.attribute17,
1384 X_ATTRIBUTE18 => l_c_fetch_record_cur.attribute18,
1385 X_ATTRIBUTE19 => l_c_fetch_record_cur.attribute19,
1386 X_ATTRIBUTE20 => l_c_fetch_record_cur.attribute20,
1387 X_WLST_PRIORITY_WEIGHT_NUM => l_c_fetch_record_cur.wlst_priority_weight_num,
1388 X_WLST_PREFERENCE_WEIGHT_NUM => l_c_fetch_record_cur.wlst_preference_weight_num,
1389 X_CORE_INDICATOR_CODE => l_c_fetch_record_cur.core_indicator_code
1390 ) ;
1391 EXCEPTION
1392 WHEN OTHERS THEN
1393 fnd_message.set_name('IGS', get_msg_from_stack(1));
1394 fnd_msg_pub.add;
1395 p_usec_x_grpmem.status := 'E';
1396 END;
1397
1398 END LOOP;
1399
1400 END IF;
1401 CLOSE cur_waitlist;
1402
1403 END validate_usec_x_grpmem;
1404
1405
1406 FUNCTION post_usec_cross_group(p_tab_usec_cross_group IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_tbl_type,
1407 p_cross_group_tab IN igs_ps_create_generic_pkg.cross_group_rec_tbl_type) RETURN BOOLEAN
1408 AS
1409 /***********************************************************************************************
1410 Created By : sommukhe
1411 Date Created By:
1412 Purpose :
1413 Known limitations,enhancements,remarks:
1414 Change History (in reverse chronological order)
1415 Who When What
1416 ********************************************************************************************** */
1417 CURSOR c_parent(cp_usec_x_listed_group_id NUMBER) IS
1418 SELECT count (ROWID) cnt
1419 FROM igs_ps_usec_x_grpmem
1420 WHERE parent = 'Y'
1421 AND usec_x_listed_group_id =cp_usec_x_listed_group_id;
1422
1423 c_parent_rec c_parent%ROWTYPE;
1424
1425 CURSOR c_x_grp(cp_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
1426 SELECT *
1427 FROM igs_ps_usec_x_grp
1428 WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id;
1429
1430 c_x_grp_rec c_x_grp%ROWTYPE;
1431
1432
1433 CURSOR c_sum_enract(cp_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
1434 SELECT SUM(enrollment_actual) sum_enrollment_actual
1435 FROM igs_ps_usec_x_grpmem_v
1436 WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id;
1437
1438
1439 c_sum_enract_rec c_sum_enract%ROWTYPE;
1440
1441 CURSOR c_usec_enr(cp_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
1442 SELECT *
1443 FROM igs_ps_usec_x_grpmem_v
1444 WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id ;
1445
1446 c_usec_enr_rec c_usec_enr%ROWTYPE;
1447
1448 CURSOR cur_usec (cp_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE,
1449 cp_usec_status igs_ps_unit_ofr_opt.unit_section_status%TYPE
1450 ) IS
1451 SELECT uoo.uoo_id
1452 FROM igs_ps_usec_x_grpmem usm,
1453 igs_ps_unit_ofr_opt uoo
1454 WHERE usm.usec_x_listed_group_id = cp_usec_x_listed_group_id
1455 AND usm.uoo_id=uoo.uoo_id
1456 AND uoo.unit_section_status NOT IN (cp_usec_status,'PLANNED','CANCELLED','NOT_OFFERED');
1457
1458 CURSOR cur_group_id (cp_group_name VARCHAR2, cp_alternate_cd VARCHAR2) IS
1459 SELECT a.usec_x_listed_group_id
1460 FROM igs_ps_usec_x_grp a, igs_ca_inst_all b
1461 WHERE a.usec_x_listed_group_name = cp_group_name
1462 AND a.cal_type = b.cal_type
1463 AND a.ci_sequence_number =b.sequence_number
1464 AND b.alternate_code=cp_alternate_cd;
1465 l_cur_group_id NUMBER;
1466
1467 l_new_sum NUMBER;
1468 l_execute_next_logic BOOLEAN;
1469 l_n_count_msg NUMBER;
1470 max_enr_group_temp NUMBER;
1471 max_ovr_group_temp NUMBER;
1472 l_b_status BOOLEAN;
1473 l_b_error BOOLEAN;
1474 BEGIN
1475 l_b_status := TRUE;
1476 FOR i IN 1 ..p_cross_group_tab.LAST LOOP
1477 l_b_error := FALSE;
1478 OPEN c_parent (p_cross_group_tab(i).usec_x_listed_group_id);
1479 FETCH c_parent INTO c_parent_rec;
1480 CLOSE c_parent;
1481 IF NVL(c_parent_rec.cnt,0) = 0 THEN
1482 l_b_status := FALSE;
1483 l_b_error := TRUE;
1484 fnd_message.set_name ( 'IGS', 'IGS_PS_USXL_NO_PARENT' );
1485 fnd_message.set_token ( 'GROUP_NAME',p_cross_group_tab(i).usec_x_listed_group_name );
1486 fnd_msg_pub.add;
1487 l_n_count_msg := fnd_msg_pub.count_msg;
1488 ELSIF NVL(c_parent_rec.cnt,0) > 1 THEN
1489 l_b_status := FALSE;
1490 l_b_error := TRUE;
1491 fnd_message.set_name ( 'IGS', 'IGS_PS_UXL_ONLY_ONE_PARENT' );
1492 fnd_msg_pub.add;
1493 l_n_count_msg := fnd_msg_pub.count_msg;
1494 END IF;
1495 IF l_b_error THEN
1496 FOR j in 1..p_tab_usec_cross_group.LAST LOOP
1497 IF p_tab_usec_cross_group.EXISTS(j) THEN
1498 OPEN cur_group_id(p_tab_usec_cross_group(j).usec_x_listed_group_name,p_tab_usec_cross_group(j).teach_cal_alternate_code);
1499 FETCH cur_group_id INTO l_cur_group_id;
1500 CLOSE cur_group_id;
1501 IF p_tab_usec_cross_group(j).status = 'S' AND l_cur_group_id = p_cross_group_tab(i).usec_x_listed_group_id THEN
1502 p_tab_usec_cross_group(j).status := 'E';
1503 p_tab_usec_cross_group(j).msg_from := l_n_count_msg;
1504 p_tab_usec_cross_group(j).msg_to := l_n_count_msg;
1505 END IF;
1506 END IF;
1507 END LOOP;
1508 END IF;
1509
1510
1511 -- Group Override Maximum should always be greater than or equal to
1512 -- Actual Enrollment for the group
1513 OPEN c_sum_enract(p_cross_group_tab(i).usec_x_listed_group_id);
1514 FETCH c_sum_enract INTO c_sum_enract_rec;
1515 CLOSE c_sum_enract;
1516
1517 OPEN c_x_grp(p_cross_group_tab(i).usec_x_listed_group_id);
1518 FETCH c_x_grp INTO c_x_grp_rec;
1519 CLOSE c_x_grp;
1520
1521 IF ((c_x_grp_rec.max_ovr_group IS NOT NULL) AND (NVL(c_x_grp_rec.max_ovr_group,0) < NVL(c_sum_enract_rec.sum_enrollment_actual,0))) THEN
1522 l_b_status := FALSE;
1523 fnd_message.set_name('IGS','IGS_PS_MAX_OVR_MAX');
1524 fnd_msg_pub.add;
1525 l_n_count_msg := fnd_msg_pub.count_msg;
1526 FOR j in 1..p_tab_usec_cross_group.LAST LOOP
1527 IF p_tab_usec_cross_group.EXISTS(j) THEN
1528 OPEN cur_group_id(p_tab_usec_cross_group(j).usec_x_listed_group_name,p_tab_usec_cross_group(j).teach_cal_alternate_code);
1529 FETCH cur_group_id INTO l_cur_group_id;
1530 CLOSE cur_group_id;
1531 IF p_tab_usec_cross_group(j).status = 'S' AND l_cur_group_id =p_cross_group_tab(i).usec_x_listed_group_id THEN
1532 p_tab_usec_cross_group(j).status := 'E';
1533 p_tab_usec_cross_group(j).msg_from := l_n_count_msg;
1534 p_tab_usec_cross_group(j).msg_to := l_n_count_msg;
1535 END IF;
1536 END IF;
1537 END LOOP;
1538 END IF;
1539
1540 IF l_b_status THEN
1541
1542 OPEN c_x_grp(p_cross_group_tab(i).usec_x_listed_group_id);
1543 FETCH c_x_grp INTO c_x_grp_rec;
1544 CLOSE c_x_grp;
1545
1546 OPEN c_usec_enr(p_cross_group_tab(i).usec_x_listed_group_id);
1547 FETCH c_usec_enr INTO c_usec_enr_rec;
1548 CLOSE c_usec_enr;
1549
1550 OPEN c_sum_enract(p_cross_group_tab(i).usec_x_listed_group_id);
1551 FETCH c_sum_enract INTO l_new_sum;
1552 CLOSE c_sum_enract;
1553
1554 l_execute_next_logic:= TRUE;
1555
1556 IF c_x_grp_rec.max_ovr_group IS NOT NULL OR c_x_grp_rec.max_enr_group IS NOT NULL THEN--1o
1557
1558 IF NVL(c_usec_enr_rec.enrollment_actual,0) >= 1 THEN--2o
1559 l_execute_next_logic:= FALSE;
1560
1561 IF NVL(c_x_grp_rec.max_enr_group,0) < NVL(l_new_sum,0) THEN--3o
1562 max_enr_group_temp:=l_new_sum;
1563 max_ovr_group_temp:=c_x_grp_rec.max_ovr_group;
1564
1565 IF NVL(c_x_grp_rec.max_ovr_group,0) < NVL(l_new_sum,0) THEN
1566 max_ovr_group_temp:=l_new_sum;
1567 END IF;
1568
1569 --Update the maximum enrollment/ovrride group
1570 UPDATE igs_ps_usec_x_grp
1571 SET max_enr_group=max_enr_group_temp,
1572 max_ovr_group=max_ovr_group_temp,
1573 last_updated_by = g_n_user_id,
1574 last_update_date = SYSDATE,
1575 last_update_login = g_n_login_id
1576 WHERE usec_x_listed_group_id =p_cross_group_tab(i).usec_x_listed_group_id;
1577
1578 --Make all the unit section status to closed if open in that group
1579 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'CLOSED') LOOP
1580 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1581 END LOOP;
1582
1583 ELSIF NVL(c_x_grp_rec.max_enr_group,0) > NVL(l_new_sum,0) THEN
1584 --Make this unit section status open if closed
1585
1586 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'OPEN') LOOP
1587 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1588 END LOOP;
1589
1590 ELSE
1591
1592 --Make all the unit section status to closed if open in that group
1593 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'CLOSED') LOOP
1594 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1595 END LOOP;
1596
1597 END IF;--3c
1598
1599 ELSE
1600 --If the inserted/modified unit section is not an enrolled one then also change the status
1601 --accordingly, bug#2702252
1602 IF NVL(c_x_grp_rec.max_enr_group,0) < NVL(l_new_sum,0) THEN--4o
1603
1604 --Make all the unit section status to closed if open in that group
1605 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'CLOSED') LOOP
1606 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1607 END LOOP;
1608
1609 ELSIF NVL(c_x_grp_rec.max_enr_group,0) > NVL(l_new_sum,0) THEN
1610 --Make this unit section status open if closed
1611
1612 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'OPEN') LOOP
1613 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1614 END LOOP;
1615
1616 ELSIF c_x_grp_rec.max_enr_group IS NULL AND l_new_sum IS NULL THEN
1617 --Make all the unit section status to open if closed in that group
1618
1619 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'OPEN') LOOP
1620 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1621 END LOOP;
1622
1623 ELSE
1624
1625 --Make all the unit section status to closed if open in that group
1626 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'CLOSED') LOOP
1627 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1628 END LOOP;
1629
1630 END IF;--4c
1631 END IF; --2c
1632 END IF;--1c
1633
1634 IF l_execute_next_logic = TRUE THEN
1635
1636 IF NVL(p_cross_group_tab(i).old_max_enr_group ,0) <> NVL(c_x_grp_rec.max_enr_group,0) THEN
1637 IF p_cross_group_tab(i).old_max_enr_group = l_new_sum THEN
1638
1639 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'OPEN') LOOP
1640 update_usec_status(l_cur_usec.uoo_id,'OPEN');
1641 END LOOP;
1642
1643 ELSIF l_new_sum = c_x_grp_rec.max_enr_group THEN
1644 FOR l_cur_usec IN cur_usec(p_cross_group_tab(i).usec_x_listed_group_id,'CLOSED') LOOP
1645 update_usec_status(l_cur_usec.uoo_id,'CLOSED');
1646 END LOOP;
1647 END IF;
1648 END IF;
1649 END IF;
1650
1651 END IF; --l_b_status
1652
1653 END LOOP;
1654
1655 RETURN l_b_status;
1656
1657 END post_usec_cross_group;
1658
1659
1660
1661 PROCEDURE validate_usec_x_grp ( p_usec_x_grp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_cross_group_rec_type,
1662 p_c_cal_type VARCHAR2,
1663 p_n_seq_num NUMBER,
1664 p_insert_update VARCHAR2,
1665 p_cross_group_rec IN OUT NOCOPY igs_ps_create_generic_pkg.cross_group_rec_type )
1666 AS
1667 /***********************************************************************************************
1668 Created By : sommukhe
1669 Date Created By: 10-Jun-2005
1670 Purpose :
1671
1672 Known limitations,enhancements,remarks:
1673 Change History (in reverse chronological order)
1674 Who When What
1675 ********************************************************************************************** */
1676 CURSOR c_usec_x_grp(cp_xgrp_name VARCHAR2) IS
1677 SELECT 'X'
1678 FROM igs_ps_usec_x_grp
1679 WHERE usec_x_listed_group_name =cp_xgrp_name;
1680
1681 c_usec_x_grp_rec c_usec_x_grp%ROWTYPE;
1682
1683
1684 CURSOR c_val_enr(cp_x_name VARCHAR2,cp_cal_type VARCHAR2,cp_sequence_number NUMBER) IS
1685 SELECT *
1686 FROM igs_ps_usec_x_grp
1687 WHERE usec_x_listed_group_name =cp_x_name
1688 AND Cal_type=cp_cal_type
1689 AND ci_sequence_number=cp_sequence_number;
1690
1691 c_val_enr_rec c_val_enr%ROWTYPE;
1692
1693 CURSOR c_old_cmgrp(cp_x_name VARCHAR2,cp_cal_type VARCHAR2,cp_seq_no NUMBER) IS
1694 SELECT * FROM igs_ps_usec_x_grp
1695 WHERE usec_x_listed_group_name =cp_x_name
1696 AND cal_type=cp_cal_type
1697 AND ci_sequence_number=cp_seq_no;
1698
1699 c_old_cmgrp_rec c_old_cmgrp%ROWTYPE;
1700
1701 CURSOR cur_usecs_in_group(cp_usec_x_listed_group_id igs_ps_usec_x_grpmem.usec_x_listed_group_id%TYPE) IS
1702 SELECT uoo_id
1703 FROM igs_ps_usec_x_grpmem
1704 WHERE usec_x_listed_group_id = cp_usec_x_listed_group_id;
1705
1706 l_message_name fnd_new_messages.message_name%TYPE;
1707 l_request_id igs_ps_sch_hdr_int.request_id%TYPE;
1708
1709 BEGIN
1710
1711 --Cannot enter a value for Override Maximum when Enrollment Maximum is null
1712 IF ((p_usec_x_grp_rec.max_ovr_group IS NOT NULL) AND (p_usec_x_grp_rec.max_enr_group IS NULL)) THEN
1713 fnd_message.set_name ( 'IGS', 'IGS_PS_ENR_NULL_OVR_NOT' );
1714 fnd_msg_pub.add;
1715 p_usec_x_grp_rec.status := 'E';
1716 END IF;
1717
1718 --Group Override Maximum should always be greater than or equal toMaximum Enrollment for the group
1719 IF ((p_usec_x_grp_rec.max_ovr_group IS NOT NULL) AND (NVL(p_usec_x_grp_rec.max_ovr_group,0) < NVL(p_usec_x_grp_rec.max_enr_group,0))) THEN
1720 fnd_message.set_name ( 'IGS', 'IGS_PS_OVERIDE_MIN_MAX_CP' );
1721 fnd_msg_pub.add;
1722 p_usec_x_grp_rec.status := 'E';
1723 END IF;
1724
1725
1726 p_cross_group_rec.usec_x_listed_group_name:=p_usec_x_grp_rec.usec_x_listed_group_name;
1727
1728 OPEN c_old_cmgrp(p_usec_x_grp_rec.usec_x_listed_group_name,p_c_cal_type,p_n_seq_num);
1729 FETCH c_old_cmgrp INTO c_old_cmgrp_rec;
1730 IF c_old_cmgrp%FOUND THEN
1731 p_cross_group_rec.usec_x_listed_group_id:=c_old_cmgrp_rec.usec_x_listed_group_id;
1732 p_cross_group_rec.old_max_enr_group:=c_old_cmgrp_rec.max_enr_group;
1733 ELSE
1734 p_cross_group_rec.usec_x_listed_group_id:=NULL;
1735 p_cross_group_rec.old_max_enr_group:=NULL;
1736 END IF;
1737 CLOSE c_old_cmgrp;
1738
1739 --validation related to location_inheritance
1740 IF p_insert_update = 'U' THEN
1741 IF p_usec_x_grp_rec.location_inheritance = 'N' AND c_old_cmgrp_rec.location_inheritance ='Y' THEN
1742 Fnd_Message.Set_Name('IGS', 'IGS_PS_LOC_INHR_CANNOT_UPD');
1743 fnd_msg_pub.add;
1744 p_usec_x_grp_rec.status := 'E';
1745 END IF;
1746 END IF;
1747
1748 -- IF the group enrollment is getting modified
1749 OPEN c_val_enr(p_usec_x_grp_rec.usec_x_listed_group_name,p_c_cal_type,p_n_seq_num);
1750 FETCH c_val_enr INTO c_val_enr_rec;
1751 IF c_val_enr%FOUND THEN
1752 IF NVL( p_usec_x_grp_rec.max_enr_group,0) <> c_val_enr_rec.max_enr_group OR
1753 NVL(p_usec_x_grp_rec.max_ovr_group,0) <> c_val_enr_rec.max_ovr_group THEN
1754
1755 --for loop which loops thru all unit sections within a group.
1756 FOR rec_cur_usecs_in_group IN cur_usecs_in_group(c_val_enr_rec.usec_x_listed_group_id) LOOP
1757 --Before updating check for validity of schedule status...
1758 IF igs_ps_usec_schedule.prgp_get_schd_status( p_uoo_id => rec_cur_usecs_in_group.uoo_id,
1759 p_usec_id => NULL,
1760 p_message_name => l_message_name ) = TRUE THEN
1761 IF l_message_name IS NULL THEN
1762 l_message_name := 'IGS_PS_SCST_PROC';
1763 END IF;
1764
1765 fnd_message.set_name('IGS',l_message_name);
1766 fnd_msg_pub.add;
1767 p_usec_x_grp_rec.status := 'E';
1768 --update the schedule status to 'Rescheduling Requested', if unit section occurrence is a scheduled one.
1769 ELSIF igs_ps_usec_schedule.prgp_upd_usec_dtls
1770 (
1771 p_uoo_id => rec_cur_usecs_in_group.uoo_id,
1772 p_location_cd => NULL,
1773 p_usec_status => NULL,
1774 p_max_enrollments => NVL(p_usec_x_grp_rec.max_enr_group,-999),
1775 p_override_enrollment_max => NVL(p_usec_x_grp_rec.max_ovr_group,-999),
1776 p_enrollment_expected => NULL,
1777 p_request_id => l_request_id,
1778 p_message_name => l_message_name
1779 ) = FALSE THEN
1780 fnd_message.set_name('IGS',l_message_name);
1781 fnd_msg_pub.add;
1782 p_usec_x_grp_rec.status := 'E';
1783 END IF;
1784 END LOOP;
1785 END IF;
1786 END IF;
1787 CLOSE c_val_enr;
1788
1789
1790 END validate_usec_x_grp;
1791
1792
1793 FUNCTION post_as_us_ai ( p_tab_as_us_ai IN OUT NOCOPY igs_ps_generic_pub.usec_ass_item_grp_tbl_type,
1794 p_tab_uoo IN igs_ps_create_generic_pkg.uoo_tbl_type) RETURN BOOLEAN
1795 AS
1796 /***********************************************************************************************
1797 Created By : sommukhe
1798 Date Created By:
1799 Purpose :
1800 Known limitations,enhancements,remarks:
1801 Change History (in reverse chronological order)
1802 Who When What
1803 ********************************************************************************************** */
1804 CURSOR cur_aig (cp_uoo_id IN NUMBER) IS
1805 SELECT us_ass_item_group_id,
1806 group_name,
1807 NVL (final_weight_qty, 0) group_final_weight_qty,
1808 NVL (midterm_weight_qty, 0) group_midterm_weight_qty,
1809 NVL (final_formula_qty, 0) group_final_formula_qty,
1810 NVL (midterm_formula_qty, 0) group_midterm_formula_qty
1811 FROM igs_as_us_ai_group
1812 WHERE uoo_id = cp_uoo_id;
1813
1814 CURSOR cur_ai (cp_us_ass_item_group_id IN NUMBER ) IS
1815 SELECT NVL (SUM (final_weight_qty), 0) sum_final_weight_qty,
1816 NVL (SUM (midterm_weight_qty), 0) sum_midterm_weight_qty,
1817 COUNT (us_ass_item_group_id) number_of_ai,
1818 COUNT (DECODE (dflt_item_ind, 'Y', 1, NULL)) number_of_default_ai
1819 FROM igs_ps_unitass_item
1820 WHERE us_ass_item_group_id = cp_us_ass_item_group_id
1821 AND logical_delete_dt IS NULL;
1822
1823 CURSOR cur_group (cp_rec_as_us_ai igs_ps_generic_pub.usec_ass_item_grp_rec_type) IS
1824 SELECT a.us_ass_item_group_id
1825 FROM igs_as_us_ai_group a, igs_ps_unit_ofr_opt_all b, igs_ca_inst_all c
1826 WHERE a.group_name = cp_rec_as_us_ai.group_name
1827 AND a.uoo_id =b.uoo_id
1828 AND b.unit_cd=cp_rec_as_us_ai.unit_cd
1829 AND b.version_number=cp_rec_as_us_ai.version_number
1830 AND b.cal_type=c.cal_type
1831 AND b.ci_sequence_number=c.sequence_number
1832 AND c.alternate_code=cp_rec_as_us_ai.teach_cal_alternate_code
1833 AND b.unit_class=cp_rec_as_us_ai.unit_class
1834 AND b.location_cd=cp_rec_as_us_ai.location_cd;
1835 l_cur_group cur_group%ROWTYPE;
1836
1837 rec_ai cur_ai%ROWTYPE;
1838 l_n_count_msg NUMBER(6);
1839 l_b_status BOOLEAN;
1840 BEGIN
1841 l_b_status := TRUE;
1842
1843 FOR i IN 1 ..p_tab_uoo.LAST LOOP
1844 FOR rec_aig IN cur_aig (p_tab_uoo(i)) LOOP
1845 --
1846 -- Validate that the at least one Assessment Item has to have a weighting
1847 -- of more than zero, if the corresponding Assessment Item Group's Grading
1848 -- Period has a weighting of more than zero.
1849 --
1850 OPEN cur_ai (rec_aig.us_ass_item_group_id);
1851 FETCH cur_ai INTO rec_ai;
1852 CLOSE cur_ai;
1853 --
1854 -- Midterm Grading Period
1855 --
1856 IF (rec_aig.group_midterm_weight_qty > 0) THEN
1857 IF (rec_ai.number_of_ai >= 0) THEN
1858 IF (rec_ai.sum_midterm_weight_qty = 0) THEN
1859 fnd_message.set_name ('IGS', 'IGS_AS_GRP_WEIT_GT_SUM_AI_WEIT');
1860 fnd_msg_pub.add;
1861 l_n_count_msg := fnd_msg_pub.count_msg;
1862 FOR j in 1..p_tab_as_us_ai.LAST LOOP
1863 IF p_tab_as_us_ai.EXISTS(j) THEN
1864 OPEN cur_group( p_tab_as_us_ai(j));
1865 FETCH cur_group INTO l_cur_group;
1866 CLOSE cur_group;
1867 IF p_tab_as_us_ai(j).status = 'S' AND l_cur_group.us_ass_item_group_id = rec_aig.us_ass_item_group_id THEN
1868 p_tab_as_us_ai(j).status := 'E';
1869 p_tab_as_us_ai(j).msg_from := l_n_count_msg;
1870 p_tab_as_us_ai(j).msg_to := l_n_count_msg;
1871 END IF;
1872 END IF;
1873 END LOOP;
1874 l_b_status :=FALSE;
1875 END IF;
1876 END IF;
1877 END IF;
1878
1879 --
1880 -- Final Grading Period
1881 --
1882 IF (rec_aig.group_final_weight_qty > 0) THEN
1883 IF (rec_ai.number_of_ai >= 0) THEN
1884 IF (rec_ai.sum_final_weight_qty = 0) THEN
1885 fnd_message.set_name ('IGS', 'IGS_AS_GRP_WEIT_GT_SUM_AI_WEIT');
1886 fnd_msg_pub.add;
1887 l_n_count_msg := fnd_msg_pub.count_msg;
1888 FOR j in 1..p_tab_as_us_ai.LAST LOOP
1889 IF p_tab_as_us_ai.EXISTS(j) THEN
1890 OPEN cur_group( p_tab_as_us_ai(j));
1891 FETCH cur_group INTO l_cur_group;
1892 CLOSE cur_group;
1893 IF p_tab_as_us_ai(j).status = 'S' AND l_cur_group.us_ass_item_group_id = rec_aig.us_ass_item_group_id THEN
1894 p_tab_as_us_ai(j).status := 'E';
1895 p_tab_as_us_ai(j).msg_from := l_n_count_msg;
1896 p_tab_as_us_ai(j).msg_to := l_n_count_msg;
1897 END IF;
1898 END IF;
1899 END LOOP;
1900 l_b_status :=FALSE;
1901 END IF;
1902 END IF;
1903 END IF;
1904 --
1905 -- Check that there are enough Default Assessment Items to satisfy the
1906 -- Assessment Item Group's Final/Midterm Grading Period formula number.
1907 --
1908 IF ((rec_ai.number_of_default_ai < rec_aig.group_final_formula_qty) OR
1909 (rec_ai.number_of_default_ai < rec_aig.group_midterm_formula_qty)) THEN
1910 fnd_message.set_name ('IGS', 'IGS_AS_NOT_ENOUGH_DFLT_AI');
1911 fnd_msg_pub.add;
1912 l_n_count_msg := fnd_msg_pub.count_msg;
1913 FOR j in 1..p_tab_as_us_ai.LAST LOOP
1914 IF p_tab_as_us_ai.EXISTS(j) THEN
1915 OPEN cur_group( p_tab_as_us_ai(j));
1916 FETCH cur_group INTO l_cur_group;
1917 CLOSE cur_group;
1918 IF p_tab_as_us_ai(j).status = 'S' AND l_cur_group.us_ass_item_group_id = rec_aig.us_ass_item_group_id THEN
1919 p_tab_as_us_ai(j).status := 'E';
1920 p_tab_as_us_ai(j).msg_from := l_n_count_msg;
1921 p_tab_as_us_ai(j).msg_to := l_n_count_msg;
1922 END IF;
1923 END IF;
1924 END LOOP;
1925 l_b_status :=FALSE;
1926 END IF;
1927 END LOOP;
1928 END LOOP;
1929
1930 RETURN l_b_status;
1931
1932 END post_as_us_ai;
1933
1934
1935 FUNCTION post_tch_rsp_ovrd ( p_tab_tch_rsp_ovrd IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_ovrd_tbl_type,
1936 p_tab_uoo IN igs_ps_create_generic_pkg.uoo_tbl_type) RETURN BOOLEAN
1937 AS
1938 /***********************************************************************************************
1939 Created By : sommukhe
1940 Date Created By: 18-Jun-2005
1941 Purpose : This function will do validations after importing records of Unit Section Teaching Responsibility Override.
1942 This will returns TRUE if all the validations pass and returns FALSE, if fails.
1943
1944 Known limitations,enhancements,remarks:
1945 Change History (in reverse chronological order)
1946 Who When What
1947 ********************************************************************************************** */
1948 CURSOR c_tro_perc_sum( cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
1949 SELECT SUM(percentage) percentage
1950 FROM igs_ps_tch_resp_ovrd_all
1951 WHERE uoo_id=cp_uoo_id;
1952 c_tro_perc_sum_rec c_tro_perc_sum%ROWTYPE;
1953
1954 CURSOR c_uoo_id (cp_usec_tch_resp_rec IN OUT NOCOPY igs_ps_generic_pub.usec_teach_resp_ovrd_rec_type) IS
1955 SELECT uoo_id
1956 FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
1957 WHERE a.unit_cd = cp_usec_tch_resp_rec.unit_cd
1958 AND a.version_number = cp_usec_tch_resp_rec.version_number
1959 AND a.cal_type = b.cal_type
1960 AND a.ci_sequence_number = b.sequence_number
1961 AND b.alternate_code=cp_usec_tch_resp_rec.teach_cal_alternate_code
1962 AND a.location_cd =cp_usec_tch_resp_rec.location_cd
1963 AND a.unit_class = cp_usec_tch_resp_rec.unit_class;
1964 c_uoo_id_rec c_uoo_id%ROWTYPE;
1965
1966 l_n_count_msg NUMBER(6);
1967 l_b_status BOOLEAN;
1968
1969 BEGIN
1970 l_b_status:= TRUE;
1971
1972 FOR i IN 1 ..p_tab_uoo.LAST LOOP
1973 OPEN c_tro_perc_sum (p_tab_uoo(i));
1974 FETCH c_tro_perc_sum INTO c_tro_perc_sum_rec;
1975 CLOSE c_tro_perc_sum;
1976 IF (c_tro_perc_sum_rec.percentage) <> 100 THEN
1977 l_b_status:= FALSE;
1978 fnd_message.set_name ( 'IGS', 'IGS_PS_PRCALLOC_TEACH_RESP' );
1979 fnd_msg_pub.add;
1980 l_n_count_msg := fnd_msg_pub.count_msg;
1981 FOR j in 1..p_tab_tch_rsp_ovrd.LAST LOOP
1982 OPEN c_uoo_id (p_tab_tch_rsp_ovrd(j));
1983 FETCH c_uoo_id INTO c_uoo_id_rec;
1984 CLOSE c_uoo_id;
1985 IF p_tab_tch_rsp_ovrd.EXISTS(j) THEN
1986 IF p_tab_tch_rsp_ovrd(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id THEN
1987 p_tab_tch_rsp_ovrd(j).status := 'E';
1988 p_tab_tch_rsp_ovrd(j).msg_from := l_n_count_msg;
1989 p_tab_tch_rsp_ovrd(j).msg_to := l_n_count_msg;
1990 END IF;
1991 END IF;
1992 END LOOP;
1993 END IF;
1994 END LOOP;
1995
1996 RETURN l_b_status;
1997
1998 END post_tch_rsp_ovrd;
1999
2000
2001 FUNCTION post_usec_rsv ( p_tab_usec_rsv IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_tbl_type,
2002 p_tab_uoo IN igs_ps_create_generic_pkg.uoo_tbl_type) RETURN BOOLEAN
2003 AS
2004 /***********************************************************************************************
2005 Created By : sommukhe
2006 Date Created By: 18-Jul-2005
2007 Purpose : Check child existence for a priority
2008 Priority order should be in series
2009 Preference order should be in series
2010
2011 Known limitations,enhancements,remarks:
2012 Change History (in reverse chronological order)
2013 Who When What
2014 ********************************************************************************************** */
2015 l_n_count_msg NUMBER(6);
2016
2017 CURSOR c_rsv_perc_sum( cp_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
2018 SELECT nvl(SUM(percentage_reserved),0) percentage_reserved
2019 FROM igs_ps_rsv_usec_pri usprv,igs_ps_rsv_usec_prf uspfv
2020 WHERE usprv.rsv_usec_pri_id = uspfv.rsv_usec_pri_id
2021 AND usprv.uoo_id =cp_uoo_id;
2022
2023 c_rsv_perc_sum_rec c_rsv_perc_sum%ROWTYPE;
2024
2025 CURSOR c_usec_rsvpric_ser(cp_uoo_id NUMBER) IS
2026 SELECT priority_order
2027 FROM igs_ps_rsv_usec_pri
2028 WHERE uoo_id=cp_uoo_id
2029 ORDER BY priority_order;
2030
2031 CURSOR cur_priority_id(cp_uoo_id NUMBER) IS
2032 SELECT rsv_usec_pri_id,priority_value
2033 FROM igs_ps_rsv_usec_pri
2034 WHERE uoo_id=cp_uoo_id;
2035
2036 CURSOR c_usec_rsvprfc_ser(cp_rsv_usec_pri_id NUMBER) IS
2037 SELECT preference_order
2038 FROM igs_ps_rsv_usec_prf
2039 WHERE rsv_usec_pri_id = cp_rsv_usec_pri_id
2040 ORDER BY preference_order;
2041 l_c_usec_rsvprfc_ser c_usec_rsvprfc_ser%ROWTYPE;
2042
2043 CURSOR c_uoo_id (cp_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type) IS
2044 SELECT uoo_id
2045 FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
2046 WHERE a.unit_cd = cp_usec_rsv_rec.unit_cd
2047 AND a.version_number = cp_usec_rsv_rec.version_number
2048 AND a.cal_type = b.cal_type
2049 AND a.ci_sequence_number = b.sequence_number
2050 AND b.alternate_code=cp_usec_rsv_rec.teach_cal_alternate_code
2051 AND a.location_cd =cp_usec_rsv_rec.location_cd
2052 AND a.unit_class = cp_usec_rsv_rec.unit_class;
2053
2054 c_uoo_id_rec c_uoo_id%ROWTYPE;
2055
2056 l_b_status BOOLEAN;
2057 l_n_counter NUMBER;
2058
2059 BEGIN
2060 l_b_status:=TRUE;
2061 FOR i IN 1 ..p_tab_uoo.LAST LOOP
2062 --Check child existence for a priority
2063 FOR rec_priority_id IN cur_priority_id(p_tab_uoo(i)) LOOP
2064
2065 OPEN c_usec_rsvprfc_ser(rec_priority_id.rsv_usec_pri_id);
2066 FETCH c_usec_rsvprfc_ser INTO l_c_usec_rsvprfc_ser;
2067 IF c_usec_rsvprfc_ser%NOTFOUND THEN
2068 l_b_status:= FALSE;
2069 fnd_message.set_name ( 'IGS', 'IGS_EN_PREF_REQ_PRIOR');
2070 fnd_message.set_token('PRIORITY',rec_priority_id.priority_value);
2071 fnd_msg_pub.add;
2072 l_n_count_msg := fnd_msg_pub.count_msg;
2073 FOR j in 1..p_tab_usec_rsv.LAST LOOP
2074 IF p_tab_usec_rsv.EXISTS(j) THEN
2075 OPEN c_uoo_id(p_tab_usec_rsv(j));
2076 FETCH c_uoo_id INTO c_uoo_id_rec;
2077 CLOSE c_uoo_id;
2078 IF p_tab_usec_rsv(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id AND p_tab_usec_rsv(j).priority_value = rec_priority_id.priority_value THEN
2079 p_tab_usec_rsv(j).status := 'E';
2080 p_tab_usec_rsv(j).msg_from := l_n_count_msg;
2081 p_tab_usec_rsv(j).msg_to := l_n_count_msg;
2082 END IF;
2083 END IF;
2084 END LOOP;
2085
2086 END IF;
2087 CLOSE c_usec_rsvprfc_ser;
2088
2089 END LOOP;
2090
2091
2092 --Sum of the percentage of preferences for a unit section should not be greater than 100.
2093 OPEN c_rsv_perc_sum (p_tab_uoo(i));
2094 FETCH c_rsv_perc_sum INTO c_rsv_perc_sum_rec;
2095 IF (c_rsv_perc_sum_rec.percentage_reserved) > 100 THEN
2096 l_b_status:= FALSE;
2097 fnd_message.set_name ( 'IGS', 'IGS_PS_PREF_SUM_BET_0_100' );
2098 fnd_msg_pub.add;
2099 l_n_count_msg := fnd_msg_pub.count_msg;
2100 FOR j in 1..p_tab_usec_rsv.LAST LOOP
2101 IF p_tab_usec_rsv.EXISTS(j) THEN
2102 OPEN c_uoo_id(p_tab_usec_rsv(j));
2103 FETCH c_uoo_id INTO c_uoo_id_rec;
2104 CLOSE c_uoo_id;
2105 IF p_tab_usec_rsv(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id THEN
2106 p_tab_usec_rsv(j).status := 'E';
2107 p_tab_usec_rsv(j).msg_from := l_n_count_msg;
2108 p_tab_usec_rsv(j).msg_to := l_n_count_msg;
2109 END IF;
2110 END IF;
2111 END LOOP;
2112 END IF;
2113 CLOSE c_rsv_perc_sum;
2114
2115
2116 --Priority order should be in series
2117 l_n_counter :=1;
2118 FOR c_usec_rsvpric_ser_rec IN c_usec_rsvpric_ser(p_tab_uoo(i)) LOOP
2119 IF l_n_counter <> c_usec_rsvpric_ser_rec.priority_order THEN
2120 l_b_status:= FALSE;
2121 fnd_message.set_name ( 'IGS', 'IGS_PS_RSV_PRI_NOT_IN_SERIES' );
2122 fnd_msg_pub.add;
2123 l_n_count_msg := fnd_msg_pub.count_msg;
2124 FOR j in 1..p_tab_usec_rsv.LAST LOOP
2125 IF p_tab_usec_rsv.EXISTS(j) THEN
2126 OPEN c_uoo_id(p_tab_usec_rsv(j));
2127 FETCH c_uoo_id INTO c_uoo_id_rec;
2128 CLOSE c_uoo_id;
2129 IF p_tab_usec_rsv(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id THEN
2130 p_tab_usec_rsv(j).status := 'E';
2131 p_tab_usec_rsv(j).msg_from := l_n_count_msg;
2132 p_tab_usec_rsv(j).msg_to := l_n_count_msg;
2133 END IF;
2134 END IF;
2135 END LOOP;
2136
2137 IF l_b_status = FALSE THEN
2138 EXIT;
2139 END IF;
2140
2141 END IF;
2142 l_n_counter:= l_n_counter+1;
2143 END LOOP;
2144
2145 --Preference order should be in series
2146 FOR rec_priority_id IN cur_priority_id(p_tab_uoo(i)) LOOP
2147 l_n_counter:=1;
2148 FOR c_usec_rsvprfc_ser_rec IN c_usec_rsvprfc_ser(rec_priority_id.rsv_usec_pri_id) LOOP
2149
2150 IF l_n_counter <> c_usec_rsvprfc_ser_rec.preference_order THEN
2151 l_b_status:= FALSE;
2152 fnd_message.set_name ( 'IGS', 'IGS_PS_RSV_PRF_NOT_IN_SERIES' );
2153 fnd_msg_pub.add;
2154 l_n_count_msg := fnd_msg_pub.count_msg;
2155 FOR j in 1..p_tab_usec_rsv.LAST LOOP
2156 IF p_tab_usec_rsv.EXISTS(j) THEN
2157 OPEN c_uoo_id(p_tab_usec_rsv(j));
2158 FETCH c_uoo_id INTO c_uoo_id_rec;
2159 CLOSE c_uoo_id;
2160 IF p_tab_usec_rsv(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id AND p_tab_usec_rsv(j).priority_value = rec_priority_id.priority_value THEN
2161 p_tab_usec_rsv(j).status := 'E';
2162 p_tab_usec_rsv(j).msg_from := l_n_count_msg;
2163 p_tab_usec_rsv(j).msg_to := l_n_count_msg;
2164 END IF;
2165 END IF;
2166 END LOOP;
2167
2168 END IF;
2169 l_n_counter:= l_n_counter+1;
2170
2171 END LOOP;
2172 END LOOP; --distinct priority_id loop
2173
2174 END LOOP; --Main distinct uoo_id loop
2175
2176 RETURN l_b_status;
2177
2178 END post_usec_rsv;
2179
2180
2181
2182 --post insert/update validations for Unit section Waitlist
2183 FUNCTION post_usec_wlst ( p_tab_usec_wlst IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_tbl_type,
2184 p_tab_uoo IN igs_ps_create_generic_pkg.uoo_tbl_type) RETURN BOOLEAN
2185 AS
2186 /***********************************************************************************************
2187 Created By : sommukhe
2188 Date Created By: 18-Jul-2005
2189 Purpose : Check child existence for a priority
2190 Priority order should be in series
2191 Preference order should be in series
2192
2193 Known limitations,enhancements,remarks:
2194 Change History (in reverse chronological order)
2195 Who When What
2196 ********************************************************************************************** */
2197 l_n_count_msg NUMBER(6);
2198
2199 CURSOR c_usec_wlstpric_ser(cp_uoo_id NUMBER) IS
2200 SELECT priority_number
2201 FROM igs_ps_usec_wlst_pri
2202 WHERE uoo_id=cp_uoo_id
2203 ORDER BY priority_number;
2204
2205 CURSOR cur_priority_id(cp_uoo_id NUMBER) IS
2206 SELECT unit_sec_waitlist_priority_id,priority_value
2207 FROM igs_ps_usec_wlst_pri
2208 WHERE uoo_id=cp_uoo_id;
2209
2210 CURSOR c_usec_wlstprfc_ser(cp_waitlist_priority_id NUMBER) IS
2211 SELECT preference_order
2212 FROM igs_ps_usec_wlst_prf
2213 WHERE unit_sec_waitlist_priority_id = cp_waitlist_priority_id
2214 ORDER BY preference_order;
2215 l_c_usec_wlstprfc_ser c_usec_wlstprfc_ser%ROWTYPE;
2216
2217 CURSOR c_uoo_id (cp_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type) IS
2218 SELECT uoo_id
2219 FROM igs_ps_unit_ofr_opt_all a,igs_ca_inst_all b
2220 WHERE a.unit_cd = cp_usec_wlst_rec.unit_cd
2221 AND a.version_number = cp_usec_wlst_rec.version_number
2222 AND a.cal_type = b.cal_type
2223 AND a.ci_sequence_number = b.sequence_number
2224 AND b.alternate_code=cp_usec_wlst_rec.teach_cal_alternate_code
2225 AND a.location_cd =cp_usec_wlst_rec.location_cd
2226 AND a.unit_class = cp_usec_wlst_rec.unit_class;
2227
2228 c_uoo_id_rec c_uoo_id%ROWTYPE;
2229
2230 l_b_status BOOLEAN;
2231 l_n_counter NUMBER;
2232
2233 BEGIN
2234 l_b_status:=TRUE;
2235 FOR i IN 1 ..p_tab_uoo.LAST LOOP
2236 --Check child existence for a priority
2237 FOR rec_priority_id IN cur_priority_id(p_tab_uoo(i)) LOOP
2238 OPEN c_usec_wlstprfc_ser(rec_priority_id.unit_sec_waitlist_priority_id);
2239 FETCH c_usec_wlstprfc_ser INTO l_c_usec_wlstprfc_ser;
2240 IF c_usec_wlstprfc_ser%NOTFOUND THEN
2241 l_b_status:= FALSE;
2242 fnd_message.set_name ( 'IGS', 'IGS_EN_PREF_REQ_PRIOR');
2243 fnd_message.set_token('PRIORITY',rec_priority_id.priority_value);
2244 fnd_msg_pub.add;
2245 l_n_count_msg := fnd_msg_pub.count_msg;
2246 FOR j in 1..p_tab_usec_wlst.LAST LOOP
2247 IF p_tab_usec_wlst.EXISTS(j) THEN
2248 OPEN c_uoo_id(p_tab_usec_wlst(j));
2249 FETCH c_uoo_id INTO c_uoo_id_rec;
2250 CLOSE c_uoo_id;
2251 IF p_tab_usec_wlst(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id AND p_tab_usec_wlst(j).priority_value = rec_priority_id.priority_value THEN
2252 p_tab_usec_wlst(j).status := 'E';
2253 p_tab_usec_wlst(j).msg_from := l_n_count_msg;
2254 p_tab_usec_wlst(j).msg_to := l_n_count_msg;
2255 END IF;
2256 END IF;
2257 END LOOP;
2258 END IF;
2259 CLOSE c_usec_wlstprfc_ser;
2260 END LOOP;
2261
2262
2263 --Priority order should be in series
2264 l_n_counter :=1;
2265 FOR c_usec_wlstpric_ser_rec IN c_usec_wlstpric_ser(p_tab_uoo(i)) LOOP
2266 IF l_n_counter <> c_usec_wlstpric_ser_rec.priority_number THEN
2267 l_b_status:= FALSE;
2268 fnd_message.set_name ( 'IGS', 'IGS_PS_WLST_PRI_NOT_IN_SERIES' );
2269 fnd_msg_pub.add;
2270 l_n_count_msg := fnd_msg_pub.count_msg;
2271 FOR j in 1..p_tab_usec_wlst.LAST LOOP
2272 IF p_tab_usec_wlst.EXISTS(j) THEN
2273 OPEN c_uoo_id(p_tab_usec_wlst(j));
2274 FETCH c_uoo_id INTO c_uoo_id_rec;
2275 CLOSE c_uoo_id;
2276 IF p_tab_usec_wlst(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id THEN
2277 p_tab_usec_wlst(j).status := 'E';
2278 p_tab_usec_wlst(j).msg_from := l_n_count_msg;
2279 p_tab_usec_wlst(j).msg_to := l_n_count_msg;
2280 END IF;
2281 END IF;
2282 END LOOP;
2283
2284 IF l_b_status = FALSE THEN
2285 EXIT;
2286 END IF;
2287
2288 END IF;
2289 l_n_counter:= l_n_counter+1;
2290 END LOOP;
2291
2292 --Preference order should be in series
2293 FOR rec_priority_id IN cur_priority_id(p_tab_uoo(i)) LOOP
2294 l_n_counter:=1;
2295 FOR c_usec_wlstprfc_ser_rec IN c_usec_wlstprfc_ser(rec_priority_id.unit_sec_waitlist_priority_id) LOOP
2296
2297 IF l_n_counter <> c_usec_wlstprfc_ser_rec.preference_order THEN
2298 l_b_status:= FALSE;
2299 fnd_message.set_name ( 'IGS', 'IGS_PS_WLST_PRF_NOT_IN_SERIES' );
2300 fnd_msg_pub.add;
2301 l_n_count_msg := fnd_msg_pub.count_msg;
2302 FOR j in 1..p_tab_usec_wlst.LAST LOOP
2303 IF p_tab_usec_wlst.EXISTS(j) THEN
2304 OPEN c_uoo_id(p_tab_usec_wlst(j));
2305 FETCH c_uoo_id INTO c_uoo_id_rec;
2306 CLOSE c_uoo_id;
2307 IF p_tab_usec_wlst(j).status = 'S' AND p_tab_uoo(i)= c_uoo_id_rec.uoo_id AND p_tab_usec_wlst(j).priority_value = rec_priority_id.priority_value THEN
2308 p_tab_usec_wlst(j).status := 'E';
2309 p_tab_usec_wlst(j).msg_from := l_n_count_msg;
2310 p_tab_usec_wlst(j).msg_to := l_n_count_msg;
2311 END IF;
2312 END IF;
2313 END LOOP;
2314
2315 END IF;
2316 l_n_counter:= l_n_counter+1;
2317
2318 END LOOP;
2319 END LOOP; --distinct priority_id loop
2320
2321 END LOOP; --Main distinct uoo_id loop
2322
2323 RETURN l_b_status;
2324
2325 END post_usec_wlst;
2326
2327
2328
2329
2330 -- Validate Unit Section Occurence Facility Records before inserting them
2331
2332 PROCEDURE validate_facility (p_uso_fclt_rec IN OUT NOCOPY igs_ps_generic_pub.usec_occurs_facility_rec_type,
2333 p_n_uoo_id IN NUMBER,
2334 p_uso_id IN NUMBER,
2335 p_calling_context IN VARCHAR2)
2336 AS
2337 /***********************************************************************************************
2338 Created By : SOMMUKHE
2339 Date Created By: 21-NOV-2002
2340 Purpose :
2341
2342 Known limitations,enhancements,remarks:
2343 Change History (in reverse chronological order)
2344 Who When What
2345 ********************************************************************************************** */
2346
2347 CURSOR cur_facility_closed (cp_facility_code igs_ps_media_equip_all.media_code%TYPE) IS
2348 SELECT 'X'
2349 FROM igs_ps_media_equip_all
2350 WHERE media_code = cp_facility_code
2351 AND closed_ind = 'Y';
2352
2353 CURSOR c_facility (cp_uso_id igs_ps_uso_facility.unit_section_occurrence_id%TYPE) IS
2354 SELECT 'X'
2355 FROM igs_ps_usec_occurs_all
2356 WHERE unit_section_occurrence_id = cp_uso_id
2357 AND schedule_status = 'PROCESSING';
2358
2359 l_c_var VARCHAR2(1);
2360
2361 CURSOR c_occurs(cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
2362 SELECT uso.unit_section_occurrence_id
2363 FROM igs_ps_usec_occurs_all uso
2364 WHERE (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
2365 AND uso.no_set_day_ind ='N'
2366 AND uso.unit_section_occurrence_id=cp_unit_section_occurrence_id;
2367
2368 BEGIN
2369
2370 --Check if the unit section is NOT_OFFERED
2371 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
2372 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
2373 fnd_msg_pub.add;
2374 p_uso_fclt_rec.status := 'E';
2375 END IF;
2376
2377 --Facility code cannot be closed
2378 OPEN cur_facility_closed( p_uso_fclt_rec.facility_code);
2379 FETCH cur_facility_closed INTO l_c_var;
2380 IF cur_facility_closed%FOUND THEN
2381 fnd_message.set_name ( 'IGS', 'IGS_PS_FACILITY_CLOSED' );
2382 fnd_msg_pub.add;
2383 p_uso_fclt_rec.status := 'E';
2384 END IF;
2385 CLOSE cur_facility_closed;
2386
2387 --Cannot import facilities when the occurrence is in progress except calling context is scheduling
2388 IF p_calling_context <> 'S' THEN
2389 OPEN c_facility (p_uso_id);
2390 FETCH c_facility INTO l_c_var;
2391 IF c_facility%FOUND THEN
2392 fnd_message.set_name ( 'IGS', 'IGS_PS_SCHEDULING_IN_PROGRESS' );
2393 fnd_msg_pub.add;
2394 p_uso_fclt_rec.status := 'E';
2395 END IF;
2396 CLOSE c_facility;
2397
2398 --Update the schedule status of the occurrence to USER_UPDATE if inserting/updating a record
2399 IF p_uso_fclt_rec.status = 'S' THEN
2400 FOR l_occurs_rec IN c_occurs(p_uso_id) LOOP
2401 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
2402 END LOOP;
2403 END IF;
2404
2405 END IF;
2406
2407
2408 END validate_facility;
2409
2410 PROCEDURE validate_category (p_usec_cat_rec IN OUT NOCOPY igs_ps_generic_pub.usec_cat_rec_type,
2411 p_n_uoo_id IN NUMBER)
2412 AS
2413 /***********************************************************************************************
2414 Created By : SOMMUKHE
2415 Date Created By: 21-NOV-2002
2416 Purpose : Check for the closed indicator for the unit category
2417
2418 Known limitations,enhancements,remarks:
2419 Change History (in reverse chronological order)
2420 Who When What
2421 ********************************************************************************************** */
2422 CURSOR c_category(cp_unit_cat igs_ps_usec_category.unit_cat%type) IS
2423 SELECT 'X'
2424 FROM igs_ps_unit_cat
2425 WHERE unit_cat = cp_unit_cat
2426 AND closed_ind = 'Y';
2427
2428 c_category_rec c_category%ROWTYPE;
2429 BEGIN
2430
2431 --Check if the unit section is NOT_OFFERED
2432 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
2433 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
2434 fnd_msg_pub.add;
2435 p_usec_cat_rec.status := 'E';
2436 END IF;
2437
2438 OPEN c_category(p_usec_cat_rec.unit_cat);
2439 FETCH c_category INTO c_category_rec;
2440 IF c_category%FOUND THEN
2441 fnd_message.set_name ( 'IGS', 'IGS_PS_CATEGORY_CLOSED' );
2442 fnd_msg_pub.add;
2443 p_usec_cat_rec.status := 'E';
2444 END IF;
2445 CLOSE c_category;
2446
2447 END validate_category;
2448
2449 PROCEDURE validate_usec_rsvpri(p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,
2450 p_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
2451 p_insert_update IN VARCHAR2)
2452 AS
2453 /***********************************************************************************************
2454 Created By : Sommukhe
2455 Date Created By:
2456 Purpose : Check if reserved seating is allowed and if priority value is 'PERSON_GRP'
2457
2458 Known limitations,enhancements,remarks:
2459 Change History (in reverse chronological order)
2460 Who When What
2461 ********************************************************************************************** */
2462 CURSOR c_rsv_allow (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
2463 SELECT reserved_seating_allowed
2464 FROM igs_ps_unit_ofr_opt_all
2465 WHERE uoo_id =cp_n_uoo_id;
2466 c_rsvpri_rec c_rsv_allow%ROWTYPE;
2467
2468 l_c_message VARCHAR2(30);
2469 BEGIN
2470
2471 -- Check if unit status is inactive.
2472 IF NOT igs_ps_val_unit.crsp_val_iud_uv_dtl(p_usec_rsv_rec.unit_cd,p_usec_rsv_rec.version_number,l_c_message) THEN
2473 fnd_message.set_name ( 'IGS', 'IGS_PS_NOCHG_UNITVER_DETAILS' );
2474 fnd_msg_pub.add;
2475 p_usec_rsv_rec.status := 'E';
2476 END IF;
2477
2478 --Check if the unit section is NOT_OFFERED
2479 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
2480 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
2481 fnd_msg_pub.add;
2482 p_usec_rsv_rec.status := 'E';
2483 END IF;
2484
2485
2486 OPEN c_rsv_allow (p_n_uoo_id);
2487 FETCH c_rsv_allow INTO c_rsvpri_rec;
2488 IF c_rsvpri_rec.reserved_seating_allowed = 'N' THEN
2489 fnd_message.set_name ( 'IGS', 'IGS_PS_RSV_SEAT_NOT_ALLOWED' );
2490 fnd_msg_pub.add;
2491 p_usec_rsv_rec.status := 'E';
2492 END IF;
2493 CLOSE c_rsv_allow;
2494
2495 IF p_insert_update = 'I' THEN
2496 IF p_usec_rsv_rec.priority_order IS NULL THEN
2497 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'PRIORITY_ORDER', 'LEGACY_TOKENS', FALSE);
2498 p_usec_rsv_rec.status := 'E';
2499 END IF;
2500 END IF;
2501
2502 END validate_usec_rsvpri;
2503
2504 PROCEDURE validate_usec_rsvprf(p_usec_rsv_rec IN OUT NOCOPY igs_ps_generic_pub.usec_res_seat_rec_type,
2505 p_insert_update IN VARCHAR2)
2506 AS
2507 /***********************************************************************************************
2508 Created By : Sommukhe
2509 Date Created By:
2510 Purpose : Check if reserved seating is allowed and if priority value is 'PERSON_GRP'
2511
2512 Known limitations,enhancements,remarks:
2513 Change History (in reverse chronological order)
2514 Who When What
2515 sommukhe 16-FEB-2006 Bug#3094371, replaced IGS_OR_UNIT by igs_or_inst_org_base_v for cursor c_org
2516 ********************************************************************************************** */
2517
2518 CURSOR c_clstd(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2519 SELECT 'X'
2520 FROM igs_pr_class_std
2521 WHERE closed_ind = 'N'
2522 AND class_standing = cp_preference_code;
2523
2524 c_clstd_rec c_clstd%ROWTYPE;
2525
2526 CURSOR c_pstage(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2527 SELECT 'X'
2528 FROM igs_ps_stage_type
2529 WHERE closed_ind = 'N'
2530 AND course_stage_type = cp_preference_code;
2531
2532 c_pstage_rec c_pstage%ROWTYPE;
2533
2534 CURSOR c_prog(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE,
2535 cp_preference_version igs_ps_rsv_usec_prf.preference_version%TYPE) IS
2536 SELECT 'X'
2537 FROM igs_ps_ver pv, igs_ps_stat pvst
2538 WHERE pvst.course_status = pv.course_status
2539 AND pvst.s_course_status <> 'INACTIVE'
2540 AND pv.course_cd=cp_preference_code
2541 AND pv.version_number=cp_preference_version;
2542
2543 c_prog_rec c_prog%ROWTYPE;
2544
2545 CURSOR c_unit_set(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE,
2546 cp_preference_version igs_ps_rsv_usec_prf.preference_version%TYPE) IS
2547 SELECT 'X'
2548 FROM igs_en_unit_set us, igs_en_unit_set_stat uss
2549 WHERE us.unit_set_status = uss.unit_set_status
2550 AND uss.s_unit_set_status <> 'INACTIVE'
2551 AND us.unit_set_cd = cp_preference_code
2552 AND us.version_number = cp_preference_version;
2553
2554 c_unit_set_rec c_unit_set%ROWTYPE;
2555
2556 CURSOR c_pgrp(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2557 SELECT 'X'
2558 FROM igs_pe_persid_group_all
2559 WHERE closed_ind = 'N'
2560 AND group_cd = cp_preference_code
2561 AND file_name IS NULL
2562 AND NVL(org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
2563 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
2564 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
2565 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
2566
2567 c_pgrp_rec c_pgrp%ROWTYPE;
2568
2569 CURSOR c_org(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2570 SELECT 'X'
2571 FROM igs_or_inst_org_base_v a, igs_or_status b
2572 WHERE a.party_number = cp_preference_code
2573 AND a.org_status = b.org_status
2574 AND b.s_org_status <> 'INACTIVE';
2575 c_org_rec c_org%ROWTYPE;
2576
2577 BEGIN
2578
2579 IF p_insert_update = 'I' THEN
2580
2581 --validation when priority value as Person Group
2582 IF p_usec_rsv_rec.priority_value = 'PERSON_GRP' THEN
2583 OPEN c_pgrp (p_usec_rsv_rec.preference_code);
2584 FETCH c_pgrp INTO c_pgrp_rec;
2585 IF c_pgrp%NOTFOUND THEN
2586 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2587 p_usec_rsv_rec.status := 'E';
2588 END IF;
2589 CLOSE c_pgrp;
2590 END IF;
2591
2592 --validation when priority value as Org unit
2593 IF p_usec_rsv_rec.priority_value = 'ORG_UNIT' THEN
2594 OPEN c_org (p_usec_rsv_rec.preference_code);
2595 FETCH c_org INTO c_org_rec;
2596 IF c_org%NOTFOUND THEN
2597 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2598 p_usec_rsv_rec.status := 'E';
2599 END IF;
2600 CLOSE c_org;
2601 END IF;
2602
2603
2604 --validation when priority value as Class standing
2605 IF p_usec_rsv_rec.priority_value = 'CLASS_STD' THEN
2606 OPEN c_clstd (p_usec_rsv_rec.preference_code);
2607 FETCH c_clstd INTO c_clstd_rec;
2608 IF c_clstd%NOTFOUND THEN
2609 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2610 p_usec_rsv_rec.status := 'E';
2611 END IF;
2612 CLOSE c_clstd;
2613 END IF;
2614
2615 --Validation when priority value as Program stage.
2616 IF p_usec_rsv_rec.priority_value = 'PROGRAM_STAGE' THEN
2617 OPEN c_pstage (p_usec_rsv_rec.preference_code);
2618 FETCH c_pstage INTO c_pstage_rec;
2619 IF c_pstage%NOTFOUND THEN
2620 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2621 p_usec_rsv_rec.status := 'E';
2622 END IF;
2623 CLOSE c_pstage;
2624 END IF;
2625
2626 --Validation when priority value as Program .
2627 IF p_usec_rsv_rec.priority_value = 'PROGRAM' THEN
2628 OPEN c_prog (p_usec_rsv_rec.preference_code,p_usec_rsv_rec.preference_version);
2629 FETCH c_prog INTO c_prog_rec;
2630 IF c_prog%NOTFOUND THEN
2631 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2632 p_usec_rsv_rec.status := 'E';
2633 END IF;
2634 CLOSE c_prog;
2635 END IF;
2636
2637 --Validation when priority value as UNIT_SET .
2638 IF p_usec_rsv_rec.priority_value = 'UNIT_SET' THEN
2639 OPEN c_unit_set (p_usec_rsv_rec.preference_code,p_usec_rsv_rec.preference_version);
2640 FETCH c_unit_set INTO c_unit_set_rec;
2641 IF c_unit_set%NOTFOUND THEN
2642 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2643 p_usec_rsv_rec.status := 'E';
2644 END IF;
2645 CLOSE c_unit_set;
2646 END IF;
2647
2648 END IF;
2649
2650 END validate_usec_rsvprf;
2651
2652
2653 PROCEDURE validate_usec_wlstprf(p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,
2654 p_insert_update IN VARCHAR2) AS
2655
2656 /***********************************************************************************************
2657 Created By : Sommukhe
2658 Date Created By:
2659 Purpose : Check if waitlist preference related validation are passed.
2660
2661 Known limitations,enhancements,remarks:
2662 Change History (in reverse chronological order)
2663 Who When What
2664 sommukhe 16-FEB-2006 Bug#3094371, replaced IGS_OR_UNIT by igs_or_inst_org_base_v for cursor c_org
2665 ********************************************************************************************** */
2666
2667 CURSOR c_clstd(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2668 SELECT 'X'
2669 FROM igs_pr_class_std
2670 WHERE closed_ind = 'N'
2671 AND class_standing = cp_preference_code;
2672
2673 c_clstd_rec c_clstd%ROWTYPE;
2674
2675 CURSOR c_pstage(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2676 SELECT 'X'
2677 FROM igs_ps_stage_type
2678 WHERE closed_ind = 'N'
2679 AND course_stage_type = cp_preference_code;
2680
2681 c_pstage_rec c_pstage%ROWTYPE;
2682
2683 CURSOR c_prog(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE,
2684 cp_preference_version igs_ps_rsv_usec_prf.preference_version%TYPE) IS
2685 SELECT 'X'
2686 FROM igs_ps_ver pv, igs_ps_stat pvst
2687 WHERE pvst.course_status = pv.course_status
2688 AND pvst.s_course_status <> 'INACTIVE'
2689 AND pv.course_cd=cp_preference_code
2690 AND pv.version_number=cp_preference_version;
2691
2692 c_prog_rec c_prog%ROWTYPE;
2693
2694 CURSOR c_unit_set(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE,
2695 cp_preference_version igs_ps_rsv_usec_prf.preference_version%TYPE) IS
2696 SELECT 'X'
2697 FROM igs_en_unit_set us, igs_en_unit_set_stat uss
2698 WHERE us.unit_set_status = uss.unit_set_status
2699 AND uss.s_unit_set_status <> 'INACTIVE'
2700 AND us.unit_set_cd = cp_preference_code
2701 AND us.version_number = cp_preference_version;
2702
2703 c_unit_set_rec c_unit_set%ROWTYPE;
2704
2705 CURSOR c_org(cp_preference_code igs_ps_rsv_usec_prf.preference_code%TYPE) IS
2706 SELECT 'X'
2707 FROM igs_or_inst_org_base_v a, igs_or_status b
2708 WHERE a.party_number = cp_preference_code
2709 AND a.org_status = b.org_status
2710 AND b.s_org_status <> 'INACTIVE';
2711 c_org_rec c_org%ROWTYPE;
2712
2713 BEGIN
2714
2715 IF p_insert_update = 'I' THEN
2716
2717 --validation when priority value as Org unit
2718 IF p_usec_wlst_rec.priority_value = 'ORG_UNIT' THEN
2719 OPEN c_org (p_usec_wlst_rec.preference_code);
2720 FETCH c_org INTO c_org_rec;
2721 IF c_org%NOTFOUND THEN
2722 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2723 p_usec_wlst_rec.status := 'E';
2724 END IF;
2725 CLOSE c_org;
2726 END IF;
2727
2728
2729 --validation when priority value as Class standing
2730 IF p_usec_wlst_rec.priority_value = 'CLASS_STD' THEN
2731 OPEN c_clstd (p_usec_wlst_rec.preference_code);
2732 FETCH c_clstd INTO c_clstd_rec;
2733 IF c_clstd%NOTFOUND THEN
2734 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2735 p_usec_wlst_rec.status := 'E';
2736 END IF;
2737 CLOSE c_clstd;
2738 END IF;
2739
2740 --Validation when priority value as Program stage.
2741 IF p_usec_wlst_rec.priority_value = 'PROGRAM_STAGE' THEN
2742 OPEN c_pstage (p_usec_wlst_rec.preference_code);
2743 FETCH c_pstage INTO c_pstage_rec;
2744 IF c_pstage%NOTFOUND THEN
2745 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2746 p_usec_wlst_rec.status := 'E';
2747 END IF;
2748 CLOSE c_pstage;
2749 END IF;
2750
2751 --Validation when priority value as Program .
2752 IF p_usec_wlst_rec.priority_value = 'PROGRAM' THEN
2753 OPEN c_prog (p_usec_wlst_rec.preference_code,p_usec_wlst_rec.preference_version);
2754 FETCH c_prog INTO c_prog_rec;
2755 IF c_prog%NOTFOUND THEN
2756 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2757 p_usec_wlst_rec.status := 'E';
2758 END IF;
2759 CLOSE c_prog;
2760 END IF;
2761
2762 --Validation when priority value as UNIT_SET .
2763 IF p_usec_wlst_rec.priority_value = 'UNIT_SET' THEN
2764 OPEN c_unit_set (p_usec_wlst_rec.preference_code,p_usec_wlst_rec.preference_version);
2765 FETCH c_unit_set INTO c_unit_set_rec;
2766 IF c_unit_set%NOTFOUND THEN
2767 igs_ps_validate_lgcy_pkg.set_msg('IGS_EN_INV', 'PREFERENCE_CODE', 'LEGACY_TOKENS', FALSE);
2768 p_usec_wlst_rec.status := 'E';
2769 END IF;
2770 CLOSE c_unit_set;
2771 END IF;
2772
2773 END IF;
2774
2775
2776 END validate_usec_wlstprf;
2777
2778
2779 --Validations for Unit section waitlist
2780 PROCEDURE validate_usec_wlstpri(p_usec_wlst_rec IN OUT NOCOPY igs_ps_generic_pub.usec_waitlist_rec_type,
2781 p_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE,
2782 p_insert_update VARCHAR2)
2783 AS
2784 /***********************************************************************************************
2785 Created By : Sommukhe
2786 Date Created By:
2787 Purpose : Check if Waitlist is allowed and if priority value is 'PERSON_GRP'
2788
2789 Known limitations,enhancements,remarks:
2790 Change History (in reverse chronological order)
2791 Who When What
2792 ********************************************************************************************** */
2793 CURSOR c_wlst_allow (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
2794 SELECT waitlist_allowed
2795 FROM igs_ps_usec_lim_wlst
2796 WHERE uoo_id = cp_n_uoo_id;
2797
2798 c_wlst_allow_rec c_wlst_allow%ROWTYPE;
2799
2800 CURSOR c_wlst_allow_pat (cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
2801 SELECT 'X'
2802 FROM igs_ps_unit_ofr_pat_all a, igs_ps_unit_ofr_opt_all b
2803 WHERE b.uoo_id = cp_n_uoo_id
2804 AND a.unit_cd=b.unit_cd
2805 AND a.version_number=b.version_number
2806 AND a.cal_type=b.cal_type
2807 AND a.ci_sequence_number=b.ci_sequence_number
2808 AND waitlist_allowed='Y';
2809 l_c_var VARCHAR2(1);
2810 l_c_message VARCHAR2(30);
2811
2812 BEGIN
2813
2814 -- Check if unit status is inactive.
2815 IF NOT igs_ps_val_unit.crsp_val_iud_uv_dtl(p_usec_wlst_rec.unit_cd,p_usec_wlst_rec.version_number,l_c_message) THEN
2816 fnd_message.set_name ( 'IGS', 'IGS_PS_NOCHG_UNITVER_DETAILS' );
2817 fnd_msg_pub.add;
2818 p_usec_wlst_rec.status := 'E';
2819 END IF;
2820
2821 --Check if the unit section is NOT_OFFERED
2822 IF NOT igs_ps_validate_lgcy_pkg.check_not_offered_usec_status(p_n_uoo_id) THEN
2823 fnd_message.set_name ( 'IGS', 'IGS_PS_IMP_NOT_ALD_NOT_OFFERED' );
2824 fnd_msg_pub.add;
2825 p_usec_wlst_rec.status := 'E';
2826 END IF;
2827
2828 --Waitlist should be allowed at section level if record exists else at pattern level such that priorities and preferences
2829 --can be imported
2830 OPEN c_wlst_allow (p_n_uoo_id);
2831 FETCH c_wlst_allow INTO c_wlst_allow_rec;
2832 IF c_wlst_allow%FOUND THEN
2833 IF c_wlst_allow_rec.waitlist_allowed = 'N' THEN
2834 fnd_message.set_name ( 'IGS', 'IGS_PS_WAITLIST_NOT_ALLOWED' );
2835 fnd_msg_pub.add;
2836 p_usec_wlst_rec.status := 'E';
2837 END IF;
2838 ELSE
2839 OPEN c_wlst_allow_pat(p_n_uoo_id);
2840 FETCH c_wlst_allow_pat INTO l_c_var;
2841 IF c_wlst_allow_pat%NOTFOUND THEN
2842 fnd_message.set_name ( 'IGS', 'IGS_PS_WAITLIST_NOT_ALLOWED' );
2843 fnd_msg_pub.add;
2844 p_usec_wlst_rec.status := 'E';
2845 END IF;
2846 CLOSE c_wlst_allow_pat;
2847 END IF;
2848 CLOSE c_wlst_allow;
2849
2850 IF p_insert_update = 'I' THEN
2851 IF p_usec_wlst_rec.priority_number IS NULL THEN
2852 igs_ps_validate_lgcy_pkg.set_msg('IGS_PS_LGCY_MANDATORY', 'PRIORITY_NUMBER', 'LEGACY_TOKENS', FALSE);
2853 p_usec_wlst_rec.status := 'E';
2854 END IF;
2855 END IF;
2856
2857 END validate_usec_wlstpri;
2858
2859
2860 END igs_ps_validate_generic_pkg;
[Home] [Help]