1 PACKAGE BODY igs_en_wlst_gen_proc as
2 /* $Header: IGSEN76B.pls 120.2 2005/10/10 04:58:33 appldev ship $ */
3 ------------------------------------------------------------------
4 --Created by : Sanil Madathil, Oracle IDC
5 --Date created: 18-JUL-2001
6 --
7 --Purpose: Package specification contains definition of procedures
8 -- getPersonDetail and getUooDetail
9 -- and procedure to raise event for sending mail to student
10 -- and administrator
11 -- and function to get message text
12 --
13 --
14 --Known limitations/enhancements and/or remarks:
15 --
16 --Change History:
17 --Who When What
18 -- rnirwani 01-Dec-2003 Bug# 2829263. Term records build
19 -- Parameters to procedure wf_inform_stud have been modified.
20 -- The parameters passed to the business events too have changed.
21 --kkillams 11-03-2003 Initialized the workflow parameter list variable
22 -- while declaring wiht wf_parameter_list_t();
23 -- w.r.t. but no:2840162
24 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
25 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
26 -------------------------------------------------------------------
27
28 FUNCTION getmessagetext (p_message_name IN VARCHAR2
29 )RETURN VARCHAR2
30 ------------------------------------------------------------------
31 --Created by : smanglm, Oracle IDC
32 --Date created: 24-JUL-2001
33 --
34 --Purpose: This function returns the message text
35 --
36 --
37 --
38 --Known limitations/enhancements and/or remarks:
39 --
40 --Change History:
41 --Who When What
42 -------------------------------------------------------------------
43 IS
44 l_message_text VARCHAR2(2000);
45 BEGIN
46 --
47 -- set the message name passed in
48 --
49 FND_MESSAGE.SET_NAME('IGS',p_message_name);
50 --
51 -- get the message string
52 --
53 l_message_text := FND_MESSAGE.GET;
54 RETURN l_message_text;
55
56 END getmessagetext;
57
58 PROCEDURE getpersondetail ( p_person_id IN igs_pe_person.person_id%TYPE ,
59 p_person_number OUT NOCOPY igs_pe_person.person_number%TYPE ,
60 p_full_name OUT NOCOPY igs_pe_person.full_name%TYPE ,
61 p_email_addr OUT NOCOPY igs_pe_person.email_addr%TYPE ,
62 p_message OUT NOCOPY VARCHAR2
63 ) IS
64 ------------------------------------------------------------------
65 --Created by : Sanil Madathil, Oracle IDC
66 --Date created: 18-JUL-2001
67 --
68 --Purpose: This procedure return all the details of person id when person id
69 -- is passed as parameter .
70 --
71 --
72 --
73 --Known limitations/enhancements and/or remarks:
74 --
75 --Change History:
76 --Who When What
77 -------------------------------------------------------------------
78 CURSOR c_igs_pe_person(cp_person_id igs_pe_person.person_id%TYPE) IS
79 SELECT person_number , full_name , email_addr
80 FROM igs_pe_person
81 WHERE person_id = cp_person_id ;
82
83 l_c_igs_pe_person c_igs_pe_person%ROWTYPE ;
84 l_message VARCHAR2(1000) DEFAULT NULL ;
85 BEGIN
86 OPEN c_igs_pe_person(cp_person_id => p_person_id );
87 FETCH c_igs_pe_person INTO l_c_igs_pe_person ;
88 IF c_igs_pe_person%NOTFOUND THEN
89 CLOSE c_igs_pe_person ;
90 l_message := 'IGS_PE_PERS_NOT_EXIST' ;
91 p_person_number := NULL ;
92 p_full_name := NULL ;
93 p_email_addr := NULL ;
94 p_message := l_message ;
95 RETURN ;
96 END IF;
97 p_person_number := l_c_igs_pe_person.person_number ;
98 p_full_name := l_c_igs_pe_person.full_name ;
99 p_email_addr := l_c_igs_pe_person.email_addr ;
100 p_message := NULL ;
101 CLOSE c_igs_pe_person ;
102
103 END getpersondetail ;
104
105 PROCEDURE getuoodetail ( p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE ,
106 p_unit_cd OUT NOCOPY igs_ps_unit_ver.unit_cd%TYPE ,
107 p_unit_title OUT NOCOPY igs_ps_unit_ver.title%TYPE ,
108 p_cal_type OUT NOCOPY igs_ps_unit_ofr_opt.cal_type%TYPE ,
109 p_alternate_code OUT NOCOPY igs_ca_inst.alternate_code%TYPE ,
110 p_location_desc OUT NOCOPY igs_ad_location.description%TYPE ,
111 p_unit_class OUT NOCOPY igs_ps_unit_ofr_opt.unit_class%TYPE ,
112 p_message OUT NOCOPY VARCHAR2
113 ) IS
114 ------------------------------------------------------------------
115 --Created by : Sanil Madathil, Oracle IDC
116 --Date created: 18-JUL-2001
117 --
118 --Purpose: This procedure return all the details of unit offer option when uoo Id
119 -- is passed as parameter .
120 --
121 --
122 --
123 --Known limitations/enhancements and/or remarks:
124 --
125 --Change History:
126 --Who When What
127 -------------------------------------------------------------------
128
129 CURSOR c_uoo_details(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
130 SELECT uoo.unit_cd , uv.title , uoo.cal_type ,
131 ci.alternate_code , al.description location_desc ,
132 uoo.unit_class
133 FROM igs_ps_unit_ofr_opt uoo ,
134 igs_ad_location al ,
135 igs_ps_unit_ver uv ,
136 igs_ca_inst ci
137 WHERE uoo.unit_cd = uv.unit_cd
138 AND uoo.version_number = uv.version_number
139 AND uoo.cal_type = ci.cal_type
140 AND uoo.ci_sequence_number = ci.sequence_number
141 AND uoo.location_cd = al.location_cd
142 AND uoo.uoo_id = cp_uoo_id ;
143
144 l_c_uoo_details c_uoo_details%ROWTYPE ;
145 l_message VARCHAR2(1000) DEFAULT NULL ;
146 BEGIN
147 OPEN c_uoo_details(cp_uoo_id => p_uoo_id);
148 FETCH c_uoo_details INTO l_c_uoo_details ;
149 IF c_uoo_details%NOTFOUND THEN
150 CLOSE c_uoo_details ;
151 l_message := 'IGS_EN_UOO_NOT_EXIST' ;
152 p_unit_cd := NULL ;
153 p_unit_title := NULL ;
154 p_cal_type := NULL ;
155 p_alternate_code := NULL ;
156 p_location_desc := NULL ;
157 p_unit_class := NULL ;
158 p_message := l_message ;
159 RETURN ;
160 END IF;
161 p_unit_cd := l_c_uoo_details.unit_cd ;
162 p_unit_title := l_c_uoo_details.title ;
163 p_cal_type := l_c_uoo_details.cal_type ;
164 p_alternate_code := l_c_uoo_details.alternate_code ;
165 p_location_desc := l_c_uoo_details.location_desc ;
166 p_unit_class := l_c_uoo_details.unit_class ;
167 p_message := NULL ;
168 CLOSE c_uoo_details ;
169
170 END getuoodetail ;
171
172 PROCEDURE wf_inform_stud ( p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE ,
173 p_program_cd IN igs_en_stdnt_ps_att.course_cd%TYPE,
174 P_version_number IN igs_en_stdnt_ps_att.version_number%TYPE,
175 P_program_attempt_status IN igs_en_stdnt_ps_att.course_attempt_status%TYPE,
176 p_org_id IN NUMBER,
177 p_old_key_program IN igs_en_stdnt_ps_att.course_cd%TYPE,
178 p_old_prim_program IN igs_en_stdnt_ps_att.course_cd%TYPE,
179 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
180 p_load_ci_seq_num IN igs_ca_inst.sequence_number%TYPE
181 )
182 ------------------------------------------------------------------
183 --Created by : svenkata, Oracle IDC
184 --Date created: 17-Jan-2002
185 --
186 --Purpose: This procedure raises the business event for informing
187 -- to student.
188 --
189 --
190 --
191 --Known limitations/enhancements and/or remarks:
192 --
193 --Change History:
194 --Who When What
195 -------------------------------------------------------------------
196 IS
197 l_wf_parameter_list_t WF_PARAMETER_LIST_T :=wf_parameter_list_t();
198 l_key NUMBER;
199 l_wf_installed fnd_lookups.lookup_code%TYPE;
200 BEGIN
201 -- get the profile value that is set for checking if workflow is installed
202 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
203
204 -- if workflow is installed then carry on with the raising an event
205 IF (RTRIM(l_wf_installed) ='Y') THEN
206 --
207 -- set the event key but before the select a number from sequenec
208 --
209 SELECT igs_en_inform_stud_s.NEXTVAL INTO l_key FROM dual;
210 --
211 -- now add the parameters to the parameter list
212 --
213 wf_event.AddParameterToList( p_Name => 'P_ORG_ID', p_Value => p_org_id, p_parameterlist =>l_wf_parameter_list_t);
214 wf_event.AddParameterToList( p_Name => 'P_PERSON_ID', p_Value => p_person_id, p_parameterlist =>l_wf_parameter_list_t);
215 wf_event.AddParameterToList( p_Name => 'P_KEY_PROGRAM', p_Value => p_program_cd, p_parameterlist =>l_wf_parameter_list_t);
216 wf_event.AddParameterToList( p_Name => 'P_VERSION_NUMBER', p_Value => p_version_number, p_parameterlist =>l_wf_parameter_list_t);
217 wf_event.AddParameterToList( p_Name => 'P_PROGRAM_ATTEMPT_STATUS', p_Value => p_program_attempt_status, p_parameterlist =>l_wf_parameter_list_t);
218 wf_event.AddParameterToList( p_Name => 'P_OLD_KEY_PROGRAM', p_Value => p_old_key_program, p_parameterlist =>l_wf_parameter_list_t);
219 wf_event.AddParameterToList( p_Name => 'P_OLD_PRIM_PROGRAM', p_Value => p_old_prim_program, p_parameterlist =>l_wf_parameter_list_t);
220 wf_event.AddParameterToList( p_Name => 'P_LOAD_CAL_TYPE', p_Value => p_load_cal_type, p_parameterlist =>l_wf_parameter_list_t);
221 wf_event.AddParameterToList( p_Name => 'P_LOAD_CA_SEQ_NUM', p_Value => p_load_ci_seq_num, p_parameterlist =>l_wf_parameter_list_t);
222 --
223 -- raise the event
224 --
225 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.prog.keyprim',
226 p_event_key => 'keyprim'||l_key,
227 p_event_data => NULL,
228 p_parameters => l_wf_parameter_list_t);
229 END IF;
230 END wf_inform_stud ;
231
232
233
234 PROCEDURE wf_send_mail_stud ( p_person_id IN igs_pe_person.person_id%TYPE ,
235 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE ,
236 p_org_id IN NUMBER
237 )
238 ------------------------------------------------------------------
239 --Created by : smanglm, Oracle IDC
240 --Date created: 24-JUL-2001
241 --
242 --Purpose: This procedure raises the business event for sending mail
243 -- to student.
244 --
245 --
246 --
247 --Known limitations/enhancements and/or remarks:
248 --
249 --Change History:
250 --Who When What
251 -------------------------------------------------------------------
252 IS
253 l_key NUMBER;
254 l_param_list wf_parameter_list_t:=wf_parameter_list_t();
255 l_wf_installed fnd_lookups.lookup_code%TYPE;
256 BEGIN
257 -- get the profile value that is set for checking if workflow is installed
258 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
259
260 -- if workflow is installed then carry on with the raising an event
261 IF (RTRIM(l_wf_installed) ='Y') THEN
262 SELECT IGS_EN_WF_MAILSTUD_S.NEXTVAL INTO l_key FROM dual;
263 --
264 -- now add the parameters to the parameter list
265 --
266 wf_event.AddParameterToList(p_name => 'ORG_ID', p_value => p_org_id, p_parameterlist => l_param_list);
267 wf_event.AddParameterToList(p_name => 'USER_ID', p_value => FND_PROFILE.VALUE('USER_ID'), p_parameterlist => l_param_list);
268 wf_event.AddParameterToList(p_name => 'RESP_ID', p_value => FND_PROFILE.VALUE('RESP_ID'), p_parameterlist => l_param_list);
269 wf_event.AddParameterToList(p_name => 'RESP_APPL_ID',p_value => FND_PROFILE.VALUE('RESP_APPL_ID'),p_parameterlist => l_param_list);
270 wf_event.AddParameterToList(p_name => 'UOO_ID', p_value => p_uoo_id, p_parameterlist => l_param_list);
271 wf_event.AddParameterToList(p_name => 'PERSON_ID', p_value => p_person_id, p_parameterlist => l_param_list);
272 --
273 -- raise the event
274 --
275 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.wlst.mailstud',
276 p_event_key => 'mailstud'||l_key,
277 p_parameters => l_param_list);
278 END IF;
279
280 END wf_send_mail_stud;
281
282 PROCEDURE wf_send_mail_adm ( p_person_id_list IN VARCHAR2 ,
283 p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE ,
284 p_org_id IN NUMBER
285 )
286 ------------------------------------------------------------------
287 --Created by : smanglm, Oracle IDC
288 --Date created: 24-JUL-2001
289 --
290 --Purpose: This procedure raises the business event for sending mail
291 -- to administrator.
292 --
293 --
294 --
295 --Known limitations/enhancements and/or remarks:
296 --
297 --Change History:
298 --Who When What
299 -- ctyagi 07-OCT-2005 Modified for bug #4314601: tosend workflow notification
300 -- to lead instructor
301 -------------------------------------------------------------------
302 IS
303
304 CURSOR c_userid IS
305 SELECT fnd.USER_ID from fnd_user fnd ,
306 igs_ps_usec_tch_resp ustr
307 WHERE
308 ustr.uoo_id = p_uoo_id AND
309 ustr.LEAD_INSTRUCTOR_FLAG='Y' AND
310 ustr.instructor_id = fnd.person_party_id ;
311
312
313 CURSOR c_resp (cp_user_id fnd_user.user_id%type) IS
314 SELECT rg.responsibility_id,
315 rg.responsibility_application_id
316 FROM fnd_user_resp_groups rg, fnd_responsibility r
317 WHERE rg.user_id = cp_user_id
318 AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date,(SYSDATE+1))
319 AND r.responsibility_id = rg.responsibility_id
320 AND r.responsibility_key = 'IGS_SS_FACULTY';
321
322
323 l_key NUMBER;
324 l_param_list wf_parameter_list_t:=wf_parameter_list_t();
325 l_wf_installed fnd_lookups.lookup_code%TYPE;
326 v_USER_ID c_userid%ROWTYPE ;
327 l_USER_ID fnd_user.USER_ID%TYPE ;
328 l_RESP_ID fnd_user_resp_groups.responsibility_id%TYPE ;
329 l_RESP_APPL_ID fnd_user_resp_groups.responsibility_application_id%TYPE ;
330
331 BEGIN
332 -- get the profile value that is set for checking if workflow is installed
333 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
334
335 -- if workflow is installed then carry on with the raising an event
336 IF (RTRIM(l_wf_installed) ='Y') THEN
337
338 FOR v_USER_ID IN c_userid LOOP
339 l_USER_ID := v_USER_ID.USER_ID;
340 IF l_USER_ID IS NOT NULL THEN
341 OPEN c_resp(l_USER_ID);
342 FETCH c_resp INTO l_RESP_ID,l_RESP_APPL_ID;
343 CLOSE c_resp;
344 END IF;
345 IF l_USER_ID IS NOT NULL AND l_RESP_ID IS NOT NULL AND l_RESP_APPL_ID IS NOT NULL THEN
346 EXIT ;
347 END IF;
348 END LOOP ;
349
350
351 IF l_USER_ID IS NULL OR l_RESP_ID IS NULL OR l_RESP_APPL_ID IS NULL THEN
352 l_USER_ID := FND_PROFILE.VALUE('USER_ID');
353 l_RESP_ID := FND_PROFILE.VALUE('RESP_ID');
354 l_RESP_APPL_ID := FND_PROFILE.VALUE('RESP_APPL_ID');
355 END IF;
356
357
358 --
359 -- now add the parameters to the parameter list
360 --
361 wf_event.AddParameterToList(p_name => 'ORG_ID', p_value => p_org_id, p_parameterlist => l_param_list);
362 wf_event.AddParameterToList(p_name => 'USER_ID', p_value => l_USER_ID, p_parameterlist => l_param_list);
363 wf_event.AddParameterToList(p_name => 'RESP_ID', p_value => l_RESP_ID, p_parameterlist => l_param_list);
364 wf_event.AddParameterToList(p_name => 'RESP_APPL_ID', p_value => l_RESP_APPL_ID, p_parameterlist => l_param_list);
365 wf_event.AddParameterToList(p_name => 'UOO_ID', p_value => p_uoo_id, p_parameterlist => l_param_list);
366 wf_event.AddParameterToList(p_name => 'PERSON_ID_LIST', p_value => p_person_id_list, p_parameterlist => l_param_list);
367 --
368 -- raise the event
369 --
370 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.wlst.mailadm',
371 p_event_key => 'mailadm'||l_key,
372 p_parameters => l_param_list);
373 END IF;
374 END wf_send_mail_adm;
375
376 FUNCTION Enrp_Resequence_Wlst ( p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE ,
377 p_modified_pos_tab IN t_modified_pos_tab
378 ) RETURN BOOLEAN
379
380 ------------------------------------------------------------------
381 --Created by : prraj, Oracle IDC
382 --Date created: 9-SEP-2002
383 --
384 --Purpose: This procedure resequences the waitlist and raises the
385 -- business event for sending mail to the students whose
386 -- wailist positions have been affected.
387 --
388 --
389 --
390 --Known limitations/enhancements and/or remarks:
391 --
392 --Change History:
393 --Who When What
394 --pradhakr 15-Dec-2002 Changed the call to the update_row of igs_en_su_attempt
395 -- table to igs_en_sua_api.update_unit_attempt.
396 -- Changes wrt ENCR031 build. Bug# 2643207
397 --ptandon 25-Aug-2003 Modified the signature to add a new parameter of
398 -- type t_modified_pos_tab and added logic to retain the
399 -- same priority/preference weight for a given position
400 -- as part of Waitlist Enhancements Build (Bug# 3052426)
401 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
402 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
403 -------------------------------------------------------------------
404
405 IS
406
407 -- Cursor to select all the students for the unit section whose
408 -- waitlist postion has been currently modified by the Admin.
409 CURSOR c_unit_sec_stud_mod(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
410 SELECT
411 person_id,
412 course_cd,
413 administrative_priority,
414 waitlist_manual_ind,
415 wlst_priority_weight_num,
416 wlst_preference_weight_num
417 FROM
418 igs_en_su_attempt_all sua
419 WHERE
420 sua.uoo_id = cp_uoo_id
421 AND sua.waitlist_manual_ind = 'M'
422 AND sua.unit_attempt_status = 'WAITLISTED'
423 FOR UPDATE NOWAIT;
424 unit_sec_stud_mod_rec c_unit_sec_stud_mod%ROWTYPE;
425
426
427 -- Cursor to select all the students for the unit section whose
428 -- waitlist postion has NOT been currently modified by the Admin.
429 CURSOR c_unit_sec_stud(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
430 SELECT
431 person_id,
432 course_cd,
433 administrative_priority,
434 waitlist_manual_ind,
435 wlst_priority_weight_num,
436 wlst_preference_weight_num
437 FROM
438 igs_en_su_attempt_all sua
439 WHERE
440 sua.uoo_id = cp_uoo_id
441 AND sua.waitlist_manual_ind <> 'M'
442 AND sua.unit_attempt_status = 'WAITLISTED'
443 ORDER BY sua.administrative_priority
444 FOR UPDATE NOWAIT;
445 unit_sec_stud_rec c_unit_sec_stud%ROWTYPE;
446
447
448 -- Cursor to select a unit section for a particular student
449 CURSOR c_stud_unit_attempt(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
450 cp_person_id igs_en_su_attempt_all.person_id%TYPE,
451 cp_course_cd igs_en_su_attempt_all.course_cd%TYPE
452 ) IS
453 SELECT * FROM
454 igs_en_su_attempt sua
455 WHERE
456 sua.uoo_id = cp_uoo_id
457 AND sua.person_id = cp_person_id
458 AND sua.course_cd = cp_course_cd
459 FOR UPDATE NOWAIT;
460 cur_sua_rec c_stud_unit_attempt%ROWTYPE;
461
462
463 -- Cursor to select person number based on the person_id
464 CURSOR c_person(cp_person_id hz_parties.party_id%TYPE) IS
465 SELECT party_number
466 FROM hz_parties
467 WHERE party_id = cp_person_id;
468
469 -- Cursor to select user name of the student based on the person_id
470 CURSOR c_student(cp_person_id hz_parties.party_id%TYPE) IS
471 SELECT user_name
472 FROM fnd_user
473 WHERE person_party_id = cp_person_id;
474
475 -- Record type for PL/SQL table
476 TYPE t_stud_wait_rec IS RECORD
477 (
478 person_id igs_en_su_attempt_all.person_id%TYPE,
479 course_cd igs_en_su_attempt_all.course_cd%TYPE,
480 admin_priority igs_en_su_attempt_all.administrative_priority%TYPE,
481 wlst_manual_ind igs_en_su_attempt_all.waitlist_manual_ind%TYPE,
482 wlst_priority_weight_num igs_en_su_attempt_all.wlst_priority_weight_num%TYPE,
483 wlst_preference_weight_num igs_en_su_attempt_all.wlst_preference_weight_num%TYPE
484 );
485
486 TYPE t_stud_waitlist IS TABLE OF t_stud_wait_rec INDEX BY BINARY_INTEGER;
487
488 stud_waitlist_table t_stud_waitlist;
489
490 l_person_number_wlst hz_parties.party_number%TYPE;
491 l_person_number_sua hz_parties.party_number%TYPE;
492 l_student_user_name fnd_user.user_name%TYPE;
493
494 waitlist_pos NUMBER;
495 counter NUMBER;
496
497 -- Workflow variables
498 l_wf_event_t WF_EVENT_T;
499 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
500 l_key NUMBER;
501 l_tot_stud NUMBER;
502 l_param_list wf_parameter_list_t:=wf_parameter_list_t();
503 l_param_value VARCHAR2(200);
504 l_param_name VARCHAR2(50);
505 l_pri_pref_def BOOLEAN;
506
507 BEGIN
508
509 l_pri_pref_def := FALSE;
510 IF p_modified_pos_tab.COUNT <> 0 THEN
511 l_pri_pref_def := TRUE;
512 END IF;
513 FOR unit_sec_stud_mod_rec IN c_unit_sec_stud_mod (cp_uoo_id => p_uoo_id)
514 LOOP
515 waitlist_pos := unit_sec_stud_mod_rec.administrative_priority;
516
517 IF stud_waitlist_table.EXISTS(waitlist_pos) AND stud_waitlist_table(waitlist_pos).person_id IS NOT NULL THEN
518 -- Duplicate value exists at the same position
519 -- Select the person numbers involved
520
521 OPEN c_person(stud_waitlist_table(waitlist_pos).person_id);
522 FETCH c_person INTO l_person_number_wlst;
523 CLOSE c_person;
524
525 OPEN c_person(unit_sec_stud_mod_rec.person_id);
526 FETCH c_person INTO l_person_number_sua;
527 CLOSE c_person;
528
529 -- concatenate both person numbers and raise exception
530 FND_MESSAGE.SET_NAME('IGS','IGS_EN_DUP_WLST_POSITION');
531 FND_MESSAGE.SET_TOKEN('STUDENTS',l_person_number_wlst || ', ' || l_person_number_sua);
532 IGS_GE_MSG_STACK.ADD;
533 APP_EXCEPTION.RAISE_EXCEPTION;
534 ELSE
535 IF l_pri_pref_def THEN
536 -- assign the priority/preference weights of the student at correct position
537 FOR pos_tab_cnt IN 1 .. p_modified_pos_tab.COUNT LOOP
538
539 IF p_modified_pos_tab(pos_tab_cnt).current_position = waitlist_pos THEN
540 stud_waitlist_table(p_modified_pos_tab(pos_tab_cnt).previous_position).wlst_priority_weight_num :=
541 unit_sec_stud_mod_rec.wlst_priority_weight_num;
542 stud_waitlist_table(p_modified_pos_tab(pos_tab_cnt).previous_position).wlst_preference_weight_num :=
543 unit_sec_stud_mod_rec.wlst_preference_weight_num;
544 EXIT;
545 END IF;
546 END LOOP;
547 END IF;
548
549 -- record does not exist
550 -- Assigning the record
551 stud_waitlist_table(waitlist_pos).person_id := unit_sec_stud_mod_rec.person_id;
552 stud_waitlist_table(waitlist_pos).course_cd := unit_sec_stud_mod_rec.course_cd;
553 stud_waitlist_table(waitlist_pos).admin_priority := unit_sec_stud_mod_rec.administrative_priority;
554 stud_waitlist_table(waitlist_pos).wlst_manual_ind := unit_sec_stud_mod_rec.waitlist_manual_ind;
555 END IF;
556 END LOOP;
557
558
559 -- Assign the unmodified records at the vacant positions in the PL/SQL table
560 counter := 1;
561 FOR unit_sec_stud_rec IN c_unit_sec_stud (cp_uoo_id => p_uoo_id)
562 LOOP
563
564 IF stud_waitlist_table.EXISTS(counter) AND stud_waitlist_table(counter).person_id IS NOT NULL THEN
565
566 IF l_pri_pref_def THEN
567 -- assign the priority/preference weights of the student at correct position
568 stud_waitlist_table(unit_sec_stud_rec.administrative_priority).wlst_priority_weight_num :=
569 unit_sec_stud_rec.wlst_priority_weight_num;
570 stud_waitlist_table(unit_sec_stud_rec.administrative_priority).wlst_preference_weight_num :=
571 unit_sec_stud_rec.wlst_preference_weight_num;
572 END IF;
573
574 -- loop until a vacant place is found in the pl/sql table
575 -- and set the record there
576 counter := counter + 1;
577 LOOP
578 IF (NOT stud_waitlist_table.EXISTS(counter)) OR
579 (stud_waitlist_table.EXISTS(counter) AND stud_waitlist_table(counter).person_id IS NULL)
580 THEN
581 stud_waitlist_table(counter).person_id := unit_sec_stud_rec.person_id;
582 stud_waitlist_table(counter).course_cd := unit_sec_stud_rec.course_cd;
583 stud_waitlist_table(counter).admin_priority := unit_sec_stud_rec.administrative_priority;
584 stud_waitlist_table(counter).wlst_manual_ind := unit_sec_stud_rec.waitlist_manual_ind;
585 EXIT;
586 END IF;
587 counter := counter + 1;
588 END LOOP;
589 ELSE
590
591 IF l_pri_pref_def THEN
592 -- assign the priority/preference weights of the student at correct position
593 stud_waitlist_table(unit_sec_stud_rec.administrative_priority).wlst_priority_weight_num :=
594 unit_sec_stud_rec.wlst_priority_weight_num;
595 stud_waitlist_table(unit_sec_stud_rec.administrative_priority).wlst_preference_weight_num :=
596 unit_sec_stud_rec.wlst_preference_weight_num;
597 END IF;
598
599 -- No records exist at this index
600 -- so directly assign the record
601 stud_waitlist_table(counter).person_id := unit_sec_stud_rec.person_id;
602 stud_waitlist_table(counter).course_cd := unit_sec_stud_rec.course_cd;
603 stud_waitlist_table(counter).admin_priority := unit_sec_stud_rec.administrative_priority;
604 stud_waitlist_table(counter).wlst_manual_ind := unit_sec_stud_rec.waitlist_manual_ind;
605 END IF;
606 counter := counter + 1;
607 END LOOP;
608
609 counter := 1;
610 l_tot_stud := 0;
611 WHILE counter <= stud_waitlist_table.COUNT
612 LOOP
613 IF ( (counter <> stud_waitlist_table(counter).admin_priority) OR (stud_waitlist_table(counter).wlst_manual_ind IN ('M','Y')) ) THEN
614 OPEN c_stud_unit_attempt(p_uoo_id,
615 stud_waitlist_table(counter).person_id,
616 stud_waitlist_table(counter).course_cd
617 );
618 FETCH c_stud_unit_attempt INTO cur_sua_rec;
619
620 IF(stud_waitlist_table(counter).wlst_manual_ind = 'M') THEN
621 cur_sua_rec.WAITLIST_MANUAL_IND := 'Y';
622 ELSE
623 cur_sua_rec.WAITLIST_MANUAL_IND := 'N';
624 END IF;
625
626 -- assign the table index value as waitlist position
627 cur_sua_rec.ADMINISTRATIVE_PRIORITY := counter;
628 cur_sua_rec.WLST_PRIORITY_WEIGHT_NUM := stud_waitlist_table(counter).wlst_priority_weight_num;
629 cur_sua_rec.WLST_PREFERENCE_WEIGHT_NUM := stud_waitlist_table(counter).wlst_preference_weight_num;
630
631 -- Call the API to update the student unit attempt. This API is a
632 -- wrapper to the update row of the TBH.
633
634 -- Added two more parameters to the call X_WLST_PRIORITY_WEIGHT_NUM and X_WLST_PREFERENCE_WEIGHT_NUM
635 -- as part of Waitlist Enhancements Build - Bug# 3052426 (ptandon)
636 igs_en_sua_api.update_unit_attempt (
637 X_ROWID => cur_sua_rec.ROW_ID,
638 X_PERSON_ID => cur_sua_rec.PERSON_ID,
639 X_COURSE_CD => cur_sua_rec.COURSE_CD,
640 X_UNIT_CD => cur_sua_rec.UNIT_CD,
641 X_CAL_TYPE => cur_sua_rec.CAL_TYPE,
642 X_CI_SEQUENCE_NUMBER => cur_sua_rec.CI_SEQUENCE_NUMBER,
643 X_VERSION_NUMBER => cur_sua_rec.VERSION_NUMBER,
644 X_LOCATION_CD => cur_sua_rec.LOCATION_CD,
645 X_UNIT_CLASS => cur_sua_rec.UNIT_CLASS,
646 X_CI_START_DT => cur_sua_rec.CI_START_DT,
647 X_CI_END_DT => cur_sua_rec.CI_END_DT,
648 X_UOO_ID => cur_sua_rec.UOO_ID,
649 X_ENROLLED_DT => cur_sua_rec.ENROLLED_DT,
650 X_UNIT_ATTEMPT_STATUS => cur_sua_rec.UNIT_ATTEMPT_STATUS,
651 X_ADMINISTRATIVE_UNIT_STATUS => cur_sua_rec.ADMINISTRATIVE_UNIT_STATUS,
652 X_DISCONTINUED_DT => cur_sua_rec.DISCONTINUED_DT,
653 X_RULE_WAIVED_DT => cur_sua_rec.RULE_WAIVED_DT,
654 X_RULE_WAIVED_PERSON_ID => cur_sua_rec.RULE_WAIVED_PERSON_ID,
655 X_NO_ASSESSMENT_IND => cur_sua_rec.NO_ASSESSMENT_IND,
656 X_SUP_UNIT_CD => cur_sua_rec.SUP_UNIT_CD,
657 X_SUP_VERSION_NUMBER => cur_sua_rec.SUP_VERSION_NUMBER,
658 X_EXAM_LOCATION_CD => cur_sua_rec.EXAM_LOCATION_CD,
659 X_ALTERNATIVE_TITLE => cur_sua_rec.ALTERNATIVE_TITLE,
660 X_OVERRIDE_ENROLLED_CP => cur_sua_rec.OVERRIDE_ENROLLED_CP,
661 X_OVERRIDE_EFTSU => cur_sua_rec.OVERRIDE_EFTSU,
662 X_OVERRIDE_ACHIEVABLE_CP => cur_sua_rec.OVERRIDE_ACHIEVABLE_CP,
663 X_OVERRIDE_OUTCOME_DUE_DT => cur_sua_rec.OVERRIDE_OUTCOME_DUE_DT,
664 X_OVERRIDE_CREDIT_REASON => cur_sua_rec.OVERRIDE_CREDIT_REASON,
665 X_ADMINISTRATIVE_PRIORITY => cur_sua_rec.ADMINISTRATIVE_PRIORITY,
666 X_WAITLIST_DT => cur_sua_rec.WAITLIST_DT,
667 X_DCNT_REASON_CD => cur_sua_rec.DCNT_REASON_CD,
668 X_MODE => 'R',
669 X_GS_VERSION_NUMBER => cur_sua_rec.GS_VERSION_NUMBER,
670 X_ENR_METHOD_TYPE => cur_sua_rec.ENR_METHOD_TYPE,
671 X_FAILED_UNIT_RULE => cur_sua_rec.FAILED_UNIT_RULE,
672 X_CART => cur_sua_rec.CART,
673 X_RSV_SEAT_EXT_ID => cur_sua_rec.RSV_SEAT_EXT_ID,
674 X_ORG_UNIT_CD => cur_sua_rec.ORG_UNIT_CD,
675 X_GRADING_SCHEMA_CODE => cur_sua_rec.GRADING_SCHEMA_CODE,
676 X_SUBTITLE => cur_sua_rec.SUBTITLE,
677 X_SESSION_ID => cur_sua_rec.SESSION_ID,
678 X_DEG_AUD_DETAIL_ID => cur_sua_rec.DEG_AUD_DETAIL_ID,
679 X_STUDENT_CAREER_TRANSCRIPT => cur_sua_rec.STUDENT_CAREER_TRANSCRIPT,
680 X_STUDENT_CAREER_STATISTICS => cur_sua_rec.STUDENT_CAREER_STATISTICS,
681 X_ATTRIBUTE_CATEGORY => cur_sua_rec.ATTRIBUTE_CATEGORY,
682 X_ATTRIBUTE1 => cur_sua_rec.ATTRIBUTE1,
683 X_ATTRIBUTE2 => cur_sua_rec.ATTRIBUTE2,
684 X_ATTRIBUTE3 => cur_sua_rec.ATTRIBUTE3,
685 X_ATTRIBUTE4 => cur_sua_rec.ATTRIBUTE4,
686 X_ATTRIBUTE5 => cur_sua_rec.ATTRIBUTE5,
687 X_ATTRIBUTE6 => cur_sua_rec.ATTRIBUTE6,
688 X_ATTRIBUTE7 => cur_sua_rec.ATTRIBUTE7,
689 X_ATTRIBUTE8 => cur_sua_rec.ATTRIBUTE8,
690 X_ATTRIBUTE9 => cur_sua_rec.ATTRIBUTE9,
691 X_ATTRIBUTE10 => cur_sua_rec.ATTRIBUTE10,
692 X_ATTRIBUTE11 => cur_sua_rec.ATTRIBUTE11,
693 X_ATTRIBUTE12 => cur_sua_rec.ATTRIBUTE12,
694 X_ATTRIBUTE13 => cur_sua_rec.ATTRIBUTE13,
695 X_ATTRIBUTE14 => cur_sua_rec.ATTRIBUTE14,
696 X_ATTRIBUTE15 => cur_sua_rec.ATTRIBUTE15,
697 X_ATTRIBUTE16 => cur_sua_rec.ATTRIBUTE16,
698 X_ATTRIBUTE17 => cur_sua_rec.ATTRIBUTE17,
699 X_ATTRIBUTE18 => cur_sua_rec.ATTRIBUTE18,
700 X_ATTRIBUTE19 => cur_sua_rec.ATTRIBUTE19,
701 X_ATTRIBUTE20 => cur_sua_rec.ATTRIBUTE20,
702 X_WAITLIST_MANUAL_IND => cur_sua_rec.WAITLIST_MANUAL_IND,
703 X_WLST_PRIORITY_WEIGHT_NUM => cur_sua_rec.WLST_PRIORITY_WEIGHT_NUM,
704 X_WLST_PREFERENCE_WEIGHT_NUM => cur_sua_rec.WLST_PREFERENCE_WEIGHT_NUM,
705 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
706 X_CORE_INDICATOR_CODE => cur_sua_rec.CORE_INDICATOR_CODE
707 );
708
709 -- Set Workflow parameters, setting the student details for whom the notification has to be sent
710 IF ( (stud_waitlist_table(counter).admin_priority <> cur_sua_rec.ADMINISTRATIVE_PRIORITY) OR
711 (stud_waitlist_table(counter).wlst_manual_ind = 'M') ) THEN
712
713 OPEN c_student(stud_waitlist_table(counter).person_id);
714 FETCH c_student INTO l_student_user_name;
715 CLOSE c_student;
716 l_tot_stud := l_tot_stud + 1;
717 l_param_name := 'PARAM_'||l_tot_stud;
718
719 IF l_student_user_name IS NULL THEN
720 l_student_user_name := ' ';
721 END IF;
722
723 l_param_value := RPAD(l_student_user_name,100,' ')||cur_sua_rec.WAITLIST_MANUAL_IND||LPAD(cur_sua_rec.ADMINISTRATIVE_PRIORITY,4,'0');
724
725 wf_event.AddParameterToList(p_name => l_param_name, p_value => l_param_value,p_parameterlist => l_param_list);
726
727 END IF; -- end of Workflow notification
728
729 CLOSE c_stud_unit_attempt;
730 END IF;
731 counter := counter + 1;
732 END LOOP;
733
734
735 -- Workflow notification ------------------------------
736
737 SELECT igs_en_wf_be_en001_s.NEXTVAL INTO l_key FROM dual;
738
739 WF_EVENT.AddParameterToList(p_name => 'TOTAL_STDNT_COUNT',p_value =>l_tot_stud,p_parameterlist => l_param_list);
740 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.be_en001',
741 p_event_key => 'wlst_pos_change'|| l_key,
742 p_parameters => l_param_list);
743
744
745 -- end of Workflow notification
746
747 RETURN TRUE;
748
749 END Enrp_Resequence_Wlst;
750
751
752 PROCEDURE check_stud_count( itemtype IN VARCHAR2,
753 itemkey IN VARCHAR2,
754 actid IN NUMBER,
755 funcmode IN VARCHAR2,
756 resultout OUT NOCOPY VARCHAR2)
757
758 ------------------------------------------------------------------
759 --Created by : prraj, Oracle IDC
760 --Date created: 12-SEP-2002
761 --
762 --Purpose: This procedure checks the number of students for whom the
763 -- notification has to be raised
764 --
765 --Known limitations/enhancements and/or remarks:
766 --
767 --Change History:
768 --Who When What
769 -------------------------------------------------------------------
770
771 IS
772
773 l_total_std NUMBER;
774 l_event_message WF_EVENT_T;
775 l_par_st_count NUMBER;
776 l_curr_student NUMBER;
777 l_user VARCHAR2(30);
778 l_param_name VARCHAR2(50);
779 l_param_value VARCHAR2(1500);
780 l_chk_Val NUMBER;
781
782 BEGIN
783
784 IF funcmode='RUN' THEN
785
786 IF wf_engine.getitemattrnumber(itemtype,itemkey,'IA_STD_COUNT') IS NULL THEN
787
788 l_event_message := wf_engine.getitemattrevent(itemtype,itemkey,'IA_EVE_MSG');
789 l_par_st_count := wf_event.getvalueforparameter('TOTAL_STDNT_COUNT',l_event_message.parameter_list);
790
791 wf_engine.setitemattrnumber(itemtype,itemkey,'IA_STD_COUNT',l_par_st_count);
792 l_chk_val := wf_engine.getitemattrnumber(itemtype,itemkey,'IA_STD_COUNT');
793
794 wf_engine.setitemattrnumber(itemtype,itemkey,'IA_CURR_STD',0);
795 END IF;
796
797 l_curr_student := wf_engine.getitemattrnumber(itemtype,itemkey,'IA_CURR_STD');
798 l_total_std := wf_engine.getitemattrnumber(itemtype,itemkey,'IA_STD_COUNT');
799
800 IF (l_total_std - l_curr_student) > 0 THEN
801 resultout := 'COMPLETE:Y';
802 ELSE
803 resultout := 'COMPLETE:N';
804 END IF;
805
806 END IF;
807
808 END check_stud_count;
809
810
811 PROCEDURE check_manual_ind(itemtype IN VARCHAR2,
812 itemkey IN VARCHAR2,
813 actid IN NUMBER,
814 funcmode IN VARCHAR2,
815 resultout OUT NOCOPY VARCHAR2)
816
817 ------------------------------------------------------------------
818 --Created by : prraj, Oracle IDC
819 --Date created: 12-SEP-2002
820 --
821 --Purpose: This procedure checks the manual indicator status
822 --of students for whom the notification has to be raised
823 --
824 --Known limitations/enhancements and/or remarks:
825 --
826 --Change History:
827 --Who When What
828 -------------------------------------------------------------------
829
830 IS
831
832 l_event_name VARCHAR2(200);
833 l_event_key VARCHAR2(200);
834 l_event_message WF_EVENT_T;
835 l_curr_student NUMBER;
836 l_param_name VARCHAR2(100);
837 l_param_Value VARCHAR2(2000);
838 l_std_name VARCHAR2(30);
839 l_std_pos NUMBER;
840 l_send_wf VARCHAR2(1);
841
842 BEGIN
843
844 IF funcmode='RUN' THEN
845
846 l_event_name := wf_engine.getitemattrtext(itemtype,itemkey,'IA_EVENT');
847 l_event_key := wf_engine.getitemattrtext(itemtype,itemkey,'IA_EVE_KEY');
848 l_event_message := wf_engine.getitemattrevent(itemtype,itemkey,'IA_EVE_MSG');
849 l_curr_student := wf_engine.getitemattrnumber(itemtype,itemkey,'IA_CURR_STD');
850 l_curr_student := l_curr_student + 1;
851
852 l_param_name := 'PARAM_'||l_Curr_student;
853 l_param_value := wf_event.getvalueforparameter(l_param_name,l_event_message.parameter_list);
854 l_send_wf := SUBSTR(l_param_Value,101,1);
855
856 IF l_send_wf = 'Y' THEN
857 l_std_name := LTRIM(RTRIM(SUBSTR(l_param_Value,1,100)));
858 l_std_pos := TO_NUMBER(SUBSTR(l_param_value,102));
859
860 wf_engine.setitemattrtext(itemtype,itemkey,'IA_UNAME',l_std_name);
861 wf_engine.setitemattrtext(itemtype,itemkey,'IA_WLPOS',l_std_pos);
862 wf_engine.setitemattrnumber(itemtype,itemkey,'IA_CURR_STD',l_curr_student);
863 resultout := 'COMPLETE:Y';
864 ELSE
865 wf_engine.setitemattrtext(itemtype,itemkey,'IA_UNAME',l_std_name);
866 wf_engine.setitemattrtext(itemtype,itemkey,'IA_WLPOS',l_std_pos);
867 wf_engine.setitemattrnumber(itemtype,itemkey,'IA_CURR_STD',l_curr_student);
868 resultout := 'COMPLETE:N';
869 END IF;
870 END IF;
871
872 END check_manual_ind;
873
874 PROCEDURE enrp_wlst_assign_pos ( p_person_id IN NUMBER ,
875 p_program_cd IN VARCHAR2 ,
876 p_uoo_id IN NUMBER
877 )
878 ------------------------------------------------------------------
879 --Created by : ptandon, Oracle IDC
880 --Date created: 25-AUG-2003
881 --
882 --Purpose: This procedure re-sequences the students after calculating
883 -- the priority/preference weights for the student in context.
884 --
885 --Known limitations/enhancements and/or remarks:
886 --
887 --Change History:
888 --Who When What
889 -------------------------------------------------------------------
890
891 IS
892 -- Cursor to lock parent unit section record.
893 CURSOR c_unit_sec_lock IS
894 SELECT uoo_id
895 FROM igs_ps_unit_ofr_opt
896 WHERE uoo_id = p_uoo_id
897 FOR UPDATE;
898
899 -- Cursor to get all the waitlisted students in the given unit section.
900 CURSOR c_sua_pri_pref (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
901 SELECT *
902 FROM igs_en_su_attempt
903 WHERE uoo_id = cp_uoo_id AND unit_attempt_status = 'WAITLISTED'
904 ORDER BY wlst_priority_weight_num DESC, wlst_preference_weight_num DESC, administrative_priority
905 FOR UPDATE;
906
907 l_cnt NUMBER;
908 BEGIN
909
910 -- Lock the parent unit section record
911 OPEN c_unit_sec_lock;
912 l_cnt := 1;
913 FOR l_sua_details IN c_sua_pri_pref(p_uoo_id) LOOP
914 IF l_cnt <> NVL(l_sua_details.administrative_priority,0) THEN
915 UPDATE igs_en_su_attempt
916 SET administrative_priority = l_cnt ,
917 waitlist_manual_ind = 'N'
918 WHERE CURRENT OF c_sua_pri_pref;
919 END IF;
920 l_cnt := l_cnt + 1;
921 END LOOP;
922 CLOSE c_unit_sec_lock;
923
924 END enrp_wlst_assign_pos;
925
926 PROCEDURE enrp_wlst_dt_reseq ( p_person_id IN NUMBER ,
927 p_program_cd IN VARCHAR2 ,
928 p_uoo_id IN NUMBER ,
929 p_cur_position IN NUMBER
930 )
931 ------------------------------------------------------------------
932 --Created by : ptandon, Oracle IDC
933 --Date created: 25-AUG-2003
934 --
935 --Purpose: This procedure re-sequences the remaining students after
936 -- placing the student in context at appropriate position.
937 --
938 --Known limitations/enhancements and/or remarks:
939 --
940 --Change History:
941 --Who When What
942 -------------------------------------------------------------------
943
944 IS
945 -- Cursor to lock parent unit section record.
946 CURSOR c_unit_sec_lock IS
947 SELECT uoo_id
948 FROM igs_ps_unit_ofr_opt
949 WHERE uoo_id = p_uoo_id
950 FOR UPDATE;
951
952 -- Cursor to get all the waitlisted students in the given unit section
953 -- whose waitlist date is later than the student in context.
954 CURSOR c_sua_wl_dt (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE ,
955 cp_person_id igs_en_su_attempt.person_id%TYPE ,
956 cp_program_cd igs_en_su_attempt.course_cd%TYPE ,
957 cp_cur_position NUMBER)
958 IS
959 SELECT *
960 FROM igs_en_su_attempt
961 WHERE uoo_id = cp_uoo_id AND
962 (person_id <> cp_person_id OR
963 course_cd <> cp_program_cd) AND
964 unit_attempt_status = 'WAITLISTED' AND
965 administrative_priority >= cp_cur_position
966 ORDER BY administrative_priority
967 FOR UPDATE;
968
969 l_cnt NUMBER;
970 BEGIN
971
972 -- Lock the parent unit section record
973 OPEN c_unit_sec_lock;
974 l_cnt := p_cur_position + 1;
975 FOR l_sua_details IN c_sua_wl_dt(p_uoo_id,p_person_id,p_program_cd,p_cur_position) LOOP
976 IF l_cnt <> NVL(l_sua_details.administrative_priority,0) THEN
977 UPDATE igs_en_su_attempt
978 SET administrative_priority = l_cnt ,
979 waitlist_manual_ind = 'N'
980 WHERE CURRENT OF c_sua_wl_dt;
981 END IF;
982 l_cnt := l_cnt + 1;
983 END LOOP;
984 CLOSE c_unit_sec_lock;
985
986 END enrp_wlst_dt_reseq;
987
988 PROCEDURE enrp_wlst_pri_pref_calc ( p_person_id IN NUMBER ,
989 p_program_cd IN VARCHAR2 ,
990 p_uoo_id IN NUMBER ,
991 p_priority_weight OUT NOCOPY NUMBER ,
992 p_preference_weight OUT NOCOPY NUMBER
993 )
994 ------------------------------------------------------------------
995 --Created by : ptandon, Oracle IDC
996 --Date created: 26-AUG-2003
997 --
998 --Purpose: This procedure calculates the waitlist priority / preference
999 -- weights for the given student.
1000 --
1001 --Known limitations/enhancements and/or remarks:
1002 --
1003 --Change History:
1004 --Who When What
1005 -------------------------------------------------------------------
1006 IS
1007 -- Cursor to select priorities at unit section level
1008 CURSOR cur_wlst_uoo_pri ( cp_uoo_id igs_ps_usec_wlst_pri.uoo_id%TYPE)
1009 IS
1010 SELECT unit_sec_waitlist_priority_id, priority_value, priority_number
1011 FROM igs_ps_usec_wlst_pri
1012 WHERE uoo_id = cp_uoo_id
1013 ORDER BY priority_number;
1014
1015 -- Cursor to select preferences for a given priority Id at unit section level
1016 CURSOR cur_wlst_uoo_prf ( cp_priority_id igs_ps_usec_wlst_prf.unit_sec_waitlist_priority_id%TYPE)
1017 IS
1018 SELECT preference_code,preference_version
1019 FROM igs_ps_usec_wlst_prf
1020 WHERE unit_sec_waitlist_priority_id = cp_priority_id
1021 ORDER BY preference_order;
1022
1023 -- Cursor to select maximum number of priorities at unit section level
1024 CURSOR cur_max_uoo_pri ( cp_uoo_id igs_ps_usec_wlst_pri.uoo_id%TYPE)
1025 IS
1026 SELECT count(unit_sec_waitlist_priority_id)
1027 FROM igs_ps_usec_wlst_pri
1028 WHERE uoo_id = cp_uoo_id;
1029 l_max_uoo_pri NUMBER;
1030
1031 -- Cursor to select maximum number of preferences among all the priorities at unit section level
1032 CURSOR cur_max_uoo_prf ( cp_uoo_id igs_ps_usec_wlst_pri.uoo_id%TYPE)
1033 IS
1034 SELECT count(unit_sec_waitlist_pref_id)
1035 FROM igs_ps_usec_wlst_prf
1036 WHERE unit_sec_waitlist_priority_id IN(SELECT unit_sec_waitlist_priority_id
1037 FROM igs_ps_usec_wlst_pri
1038 WHERE uoo_id = cp_uoo_id);
1039 l_max_uoo_pref NUMBER;
1040
1041 -- Cursor to get the unit code, version number and teaching calender associated with the unit section
1042 CURSOR cur_get_teach_inst ( cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE)
1043 IS
1044 SELECT unit_cd, version_number, cal_type , ci_sequence_number
1045 FROM igs_ps_unit_ofr_opt
1046 WHERE uoo_id = cp_uoo_id;
1047 cur_get_teach_inst_rec cur_get_teach_inst%ROWTYPE;
1048
1049 -- Cursor to select priorities at unit offering pattern level
1050 CURSOR cur_wlst_uop_pri ( cp_unit_cd igs_ps_uofr_wlst_pri.unit_cd%TYPE ,
1051 cp_version_number igs_ps_uofr_wlst_pri.version_number%TYPE ,
1052 cp_cal_type igs_ps_uofr_wlst_pri.calender_type%TYPE ,
1053 cp_seq_no igs_ps_uofr_wlst_pri.ci_sequence_number%TYPE)
1054 IS
1055 SELECT unit_ofr_waitlist_priority_id, priority_value, priority_number
1056 FROM igs_ps_uofr_wlst_pri
1057 WHERE unit_cd = cp_unit_cd
1058 AND version_number = cp_version_number
1059 AND calender_type = cp_cal_type
1060 AND ci_sequence_number = cp_seq_no
1061 ORDER BY priority_number;
1062
1063 -- Cursor to select preferences for a given priority Id at unit offering pattern level
1064 CURSOR cur_wlst_uop_prf ( cp_priority_id igs_ps_uofr_wlst_prf.unit_ofr_waitlist_priority_id%TYPE)
1065 IS
1066 SELECT preference_code,preference_version
1067 FROM igs_ps_uofr_wlst_prf
1068 WHERE unit_ofr_waitlist_priority_id = cp_priority_id
1069 ORDER BY preference_order;
1070
1071 -- Cursor to select maximum number of priorities at unit offering pattern level
1072 CURSOR cur_max_uop_pri ( cp_unit_cd igs_ps_uofr_wlst_pri.unit_cd%TYPE ,
1073 cp_version_number igs_ps_uofr_wlst_pri.version_number%TYPE ,
1074 cp_cal_type igs_ps_uofr_wlst_pri.calender_type%TYPE ,
1075 cp_seq_no igs_ps_uofr_wlst_pri.ci_sequence_number%TYPE)
1076 IS
1077 SELECT count(unit_ofr_waitlist_priority_id)
1078 FROM igs_ps_uofr_wlst_pri
1079 WHERE unit_cd = cp_unit_cd
1080 AND version_number = cp_version_number
1081 AND calender_type = cp_cal_type
1082 AND ci_sequence_number = cp_seq_no;
1083 l_max_uop_pri NUMBER;
1084
1085 -- Cursor to select maximum number of preferences among all the priorities at unit offering pattern level
1086 CURSOR cur_max_uop_prf ( cp_unit_cd igs_ps_uofr_wlst_pri.unit_cd%TYPE ,
1087 cp_version_number igs_ps_uofr_wlst_pri.version_number%TYPE ,
1088 cp_cal_type igs_ps_uofr_wlst_pri.calender_type%TYPE ,
1089 cp_seq_no igs_ps_uofr_wlst_pri.ci_sequence_number%TYPE)
1090 IS
1091 SELECT count(unit_ofr_waitlist_pref_id)
1092 FROM igs_ps_uofr_wlst_prf
1093 WHERE unit_ofr_waitlist_priority_id IN(SELECT unit_ofr_waitlist_priority_id
1094 FROM igs_ps_uofr_wlst_pri
1095 WHERE unit_cd = cp_unit_cd
1096 AND version_number = cp_version_number
1097 AND calender_type = cp_cal_type
1098 AND ci_sequence_number = cp_seq_no);
1099 l_max_uop_pref NUMBER;
1100
1101 -- Cursor to select organization unit code for a given unit section
1102 CURSOR cur_org_unit_cd ( cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE)
1103 IS
1104 SELECT NVL(uoo.owner_org_unit_cd,uv.owner_org_unit_cd)
1105 FROM igs_ps_unit_ofr_opt uoo,
1106 igs_ps_unit_ver uv
1107 WHERE uoo.uoo_id = cp_uoo_id
1108 AND uv.unit_cd= uoo.unit_cd
1109 AND uv.version_number= uoo.version_number;
1110
1111 l_org_unit_cd igs_ps_unit_ofr_opt.owner_org_unit_cd%TYPE;
1112
1113 -- Cursor to get the load calender type associated with a teaching calender instance
1114 CURSOR cur_teach_to_load ( cp_teach_cal_type igs_ca_inst.cal_type%TYPE ,
1115 cp_teach_seq_no igs_ca_inst.sequence_number%TYPE)
1116 IS
1117 SELECT load_cal_type
1118 FROM igs_ca_teach_to_load_v
1119 WHERE teach_cal_type = cp_teach_cal_type
1120 AND teach_ci_sequence_number = cp_teach_seq_no
1121 ORDER BY load_start_dt;
1122
1123 l_load_cal_type igs_ca_inst.cal_type%TYPE;
1124
1125 -- Cursor to select priorities at organizational unit level
1126 CURSOR cur_wlst_org_pri ( cp_org_unit_cd igs_en_or_unit_wlst.org_unit_cd%TYPE ,
1127 cp_cal_type igs_en_or_unit_wlst.cal_type%TYPE )
1128 IS
1129 SELECT owp.org_unit_wlst_pri_id, owp.priority_value, owp.priority_number
1130 FROM igs_en_orun_wlst_pri owp, igs_en_or_unit_wlst ouw
1131 WHERE ouw.org_unit_cd = cp_org_unit_cd
1132 AND ouw.cal_type = cp_cal_type
1133 AND owp.org_unit_wlst_id = ouw.org_unit_wlst_id
1134 AND ouw.closed_flag = 'N'
1135 ORDER BY owp.priority_number;
1136
1137 -- Cursor to select preferences for a given priority Id at organizational unit level
1138 CURSOR cur_wlst_org_prf ( cp_priority_id igs_en_orun_wlst_pri.org_unit_wlst_pri_id%TYPE)
1139 IS
1140 SELECT preference_code,preference_version
1141 FROM igs_en_orun_wlst_prf
1142 WHERE org_unit_wlst_pri_id = cp_priority_id
1143 ORDER BY preference_order;
1144
1145 -- Cursor to select maximum number of priorities at organizational unit level
1146 CURSOR cur_max_org_pri ( cp_org_unit_cd igs_en_or_unit_wlst.org_unit_cd%TYPE ,
1147 cp_cal_type igs_en_or_unit_wlst.cal_type%TYPE )
1148 IS
1149 SELECT count(org_unit_wlst_pri_id)
1150 FROM igs_en_orun_wlst_pri owp, igs_en_or_unit_wlst ouw
1151 WHERE ouw.org_unit_cd = cp_org_unit_cd
1152 AND ouw.cal_type = cp_cal_type
1153 AND owp.org_unit_wlst_id = ouw.org_unit_wlst_id
1154 AND ouw.closed_flag = 'N';
1155 l_max_org_pri NUMBER;
1156
1157 -- Cursor to select maximum number of preferences among all the priorities at organizational unit level
1158 CURSOR cur_max_org_prf ( cp_org_unit_cd igs_en_or_unit_wlst.org_unit_cd%TYPE ,
1159 cp_cal_type igs_en_or_unit_wlst.cal_type%TYPE )
1160 IS
1161 SELECT count(org_unit_wlst_prf_id)
1162 FROM igs_en_orun_wlst_prf
1163 WHERE org_unit_wlst_pri_id IN(SELECT owp.org_unit_wlst_pri_id
1164 FROM igs_en_orun_wlst_pri owp, igs_en_or_unit_wlst ouw
1165 WHERE ouw.org_unit_cd = cp_org_unit_cd
1166 AND ouw.cal_type = cp_cal_type
1167 AND owp.org_unit_wlst_id = ouw.org_unit_wlst_id
1168 AND ouw.closed_flag = 'N');
1169 l_max_org_pref NUMBER;
1170
1171 -- Cursor to get program version for a student program attempt
1172 CURSOR cur_get_prog_ver (cp_person_id NUMBER ,
1173 cp_course_cd VARCHAR2)
1174 IS
1175 SELECT version_number
1176 FROM igs_en_stdnt_ps_att
1177 WHERE person_id = cp_person_id
1178 AND course_cd = cp_course_cd;
1179
1180 -- Cursors to check whether given student is satisfying waitlist priority / preferences
1181 CURSOR cur_program ( cp_person_id NUMBER ,
1182 cp_course_cd VARCHAR2 ,
1183 cp_version_number NUMBER)
1184 IS
1185 SELECT 'X'
1186 FROM igs_en_stdnt_ps_att
1187 WHERE person_id = cp_person_id
1188 AND course_cd = cp_course_cd
1189 AND version_number = cp_version_number;
1190
1191 CURSOR cur_org ( cp_person_id NUMBER ,
1192 cp_org_unit_cd VARCHAR2)
1193 IS
1194 SELECT 'X'
1195 FROM igs_en_stdnt_ps_att sca,
1196 igs_ps_ver pv
1197 WHERE sca.person_id = cp_person_id
1198 AND sca.course_cd = pv.course_cd
1199 AND sca.version_number = pv.version_number
1200 AND pv.responsible_org_unit_cd = cp_org_unit_cd;
1201
1202 CURSOR cur_unit_set ( cp_person_id NUMBER ,
1203 cp_unit_set_cd VARCHAR2 ,
1204 cp_us_version_number NUMBER)
1205 IS
1206 SELECT 'X'
1207 FROM igs_en_stdnt_ps_att spa,
1208 igs_as_su_setatmpt sus
1209 WHERE spa.person_id = cp_person_id
1210 AND sus.person_id = spa.person_id
1211 AND spa.course_cd = sus.course_cd
1212 AND sus.unit_set_cd = cp_unit_set_cd
1213 AND sus.us_version_number = cp_us_version_number;
1214
1215 l_pref_order NUMBER;
1216 l_pri_weight NUMBER;
1217 l_pref_weight NUMBER;
1218 l_wlst_level VARCHAR2(10);
1219 l_pref_satisfied BOOLEAN;
1220 l_pri_satisfied VARCHAR2(1);
1221 l_teach_cal_type igs_ca_inst.cal_type%TYPE;
1222 l_teach_seq_no igs_ca_inst.sequence_number%TYPE;
1223 l_program_version igs_en_stdnt_ps_att.version_number%TYPE;
1224
1225 BEGIN
1226 l_pref_order := 0;
1227 l_pri_weight := 0;
1228 l_pref_weight := 0;
1229 l_wlst_level := NULL;
1230
1231 OPEN cur_get_prog_ver(p_person_id,p_program_cd);
1232 FETCH cur_get_prog_ver INTO l_program_version;
1233 CLOSE cur_get_prog_ver;
1234
1235 -- Get the maximum number of priorities at unit section level
1236 OPEN cur_max_uoo_pri(p_uoo_id);
1237 FETCH cur_max_uoo_pri INTO l_max_uoo_pri;
1238 CLOSE cur_max_uoo_pri;
1239
1240 -- Get the maximum number of preferences among all the priorities at unit section level
1241 OPEN cur_max_uoo_prf(p_uoo_id);
1242 FETCH cur_max_uoo_prf INTO l_max_uoo_pref;
1243 CLOSE cur_max_uoo_prf;
1244
1245 -- Loop for all priorities at unit section level
1246 FOR cur_wlst_uoo_pri_rec IN cur_wlst_uoo_pri(p_uoo_id)
1247 LOOP
1248 l_wlst_level := 'UNIT_SEC';
1249 IF cur_wlst_uoo_pri_rec.priority_value = 'PROGRAM' THEN
1250 l_pref_satisfied := FALSE;
1251 FOR cur_wlst_uoo_prf_rec IN cur_wlst_uoo_prf(cur_wlst_uoo_pri_rec.unit_sec_waitlist_priority_id)
1252 LOOP
1253 l_pref_order := l_pref_order + 1;
1254 OPEN cur_program (p_person_id ,
1255 cur_wlst_uoo_prf_rec.preference_code ,
1256 cur_wlst_uoo_prf_rec.preference_version);
1257 FETCH cur_program INTO l_pri_satisfied;
1258 IF cur_program%FOUND THEN
1259 l_pref_satisfied := TRUE;
1260 l_pref_weight := l_pref_weight + power(2,(l_max_uoo_pref-l_pref_order));
1261 END IF;
1262 CLOSE cur_program;
1263 END LOOP; -- End of cur_wlst_uoo_prf
1264 IF l_pref_satisfied THEN
1265 l_pri_weight := l_pri_weight + power(2,(l_max_uoo_pri-cur_wlst_uoo_pri_rec.priority_number));
1266 END IF;
1267
1268 ELSIF cur_wlst_uoo_pri_rec.priority_value = 'ORG_UNIT' THEN
1269 l_pref_satisfied := FALSE;
1270 FOR cur_wlst_uoo_prf_rec IN cur_wlst_uoo_prf(cur_wlst_uoo_pri_rec.unit_sec_waitlist_priority_id)
1271 LOOP
1272 l_pref_order := l_pref_order + 1;
1273 OPEN cur_org (p_person_id ,
1274 cur_wlst_uoo_prf_rec.preference_code);
1275 FETCH cur_org INTO l_pri_satisfied;
1276 IF cur_org%FOUND THEN
1277 l_pref_satisfied := TRUE;
1278 l_pref_weight := l_pref_weight + power(2,(l_max_uoo_pref-l_pref_order));
1279 END IF;
1280 CLOSE cur_org;
1281 END LOOP; -- End of cur_wlst_uoo_prf
1282 IF l_pref_satisfied THEN
1283 l_pri_weight := l_pri_weight + power(2,(l_max_uoo_pri-cur_wlst_uoo_pri_rec.priority_number));
1284 END IF;
1285
1286 ELSIF cur_wlst_uoo_pri_rec.priority_value = 'UNIT_SET' THEN
1287 l_pref_satisfied := FALSE;
1288 FOR cur_wlst_uoo_prf_rec IN cur_wlst_uoo_prf(cur_wlst_uoo_pri_rec.unit_sec_waitlist_priority_id)
1289 LOOP
1290 l_pref_order := l_pref_order + 1;
1291 OPEN cur_unit_set (p_person_id ,
1292 cur_wlst_uoo_prf_rec.preference_code ,
1293 cur_wlst_uoo_prf_rec.preference_version);
1294 FETCH cur_unit_set INTO l_pri_satisfied;
1295 IF cur_unit_set%FOUND THEN
1296 l_pref_satisfied := TRUE;
1297 l_pref_weight := l_pref_weight + power(2,(l_max_uoo_pref-l_pref_order));
1298 END IF;
1299 CLOSE cur_unit_set;
1300 END LOOP; -- End of cur_wlst_uoo_prf
1301 IF l_pref_satisfied THEN
1302 l_pri_weight := l_pri_weight + power(2,(l_max_uoo_pri-cur_wlst_uoo_pri_rec.priority_number));
1303 END IF;
1304
1305 ELSIF cur_wlst_uoo_pri_rec.priority_value = 'PROGRAM_STAGE' THEN
1306 l_pref_satisfied := FALSE;
1307 FOR cur_wlst_uoo_prf_rec IN cur_wlst_uoo_prf(cur_wlst_uoo_pri_rec.unit_sec_waitlist_priority_id)
1308 LOOP
1309 l_pref_order := l_pref_order + 1;
1310
1311 -- Call the function to determine whether the student completed the given program stage
1312 IF igs_en_gen_015.enrp_val_ps_stage(p_person_id, p_program_cd, l_program_version,
1313 cur_wlst_uoo_prf_rec.preference_code)
1314 THEN
1315 l_pref_satisfied := TRUE;
1316 l_pref_weight := l_pref_weight + power(2,(l_max_uoo_pref-l_pref_order));
1317 END IF;
1318 END LOOP; -- End of cur_wlst_uoo_prf
1319 IF l_pref_satisfied THEN
1320 l_pri_weight := l_pri_weight + power(2,(l_max_uoo_pri-cur_wlst_uoo_pri_rec.priority_number));
1321 END IF;
1322
1323 ELSIF cur_wlst_uoo_pri_rec.priority_value = 'CLASS_STD' THEN
1324 l_pref_satisfied := FALSE;
1325 FOR cur_wlst_uoo_prf_rec IN cur_wlst_uoo_prf(cur_wlst_uoo_pri_rec.unit_sec_waitlist_priority_id)
1326 LOOP
1327 l_pref_order := l_pref_order + 1;
1328
1329 -- Call the function to determine the class standing of the given student
1330 IF igs_pr_get_class_std.get_class_standing(p_person_id, p_program_cd, 'Y', SYSDATE, NULL, NULL)
1331 = cur_wlst_uoo_prf_rec.preference_code
1332 THEN
1333 l_pref_satisfied := TRUE;
1334 l_pref_weight := l_pref_weight + power(2,(l_max_uoo_pref-l_pref_order));
1335 END IF;
1336 END LOOP; -- End of cur_wlst_uoo_prf
1337 IF l_pref_satisfied THEN
1338 l_pri_weight := l_pri_weight + power(2,(l_max_uoo_pri-cur_wlst_uoo_pri_rec.priority_number));
1339 END IF;
1340 END IF; -- End of cur_wlst_uoo_pri_rec.priority_value
1341 END LOOP; -- End of cur_wlst_uoo_pri
1342
1343 IF l_wlst_level IS NULL THEN
1344
1345 -- Waitlist setup is not defined at unit section level,
1346 -- Check whether the setup is defined at unit offering level.
1347
1348 -- Get the teach calender associated with the unit section
1349 OPEN cur_get_teach_inst(p_uoo_id);
1350 FETCH cur_get_teach_inst INTO cur_get_teach_inst_rec;
1351 CLOSE cur_get_teach_inst;
1352
1353 l_teach_cal_type := cur_get_teach_inst_rec.cal_type;
1354 l_teach_seq_no := cur_get_teach_inst_rec.ci_sequence_number;
1355
1356 -- Get the maximum number of priorities at unit offering pattern level
1357 OPEN cur_max_uop_pri(cur_get_teach_inst_rec.unit_cd ,
1358 cur_get_teach_inst_rec.version_number ,
1359 cur_get_teach_inst_rec.cal_type ,
1360 cur_get_teach_inst_rec.ci_sequence_number);
1361 FETCH cur_max_uop_pri INTO l_max_uop_pri;
1362 CLOSE cur_max_uop_pri;
1363
1364 -- Get the maximum number of preferences among all the priorities at unit offering pattern level
1365 OPEN cur_max_uop_prf(cur_get_teach_inst_rec.unit_cd ,
1366 cur_get_teach_inst_rec.version_number ,
1367 cur_get_teach_inst_rec.cal_type ,
1368 cur_get_teach_inst_rec.ci_sequence_number);
1369 FETCH cur_max_uop_prf INTO l_max_uop_pref;
1370 CLOSE cur_max_uop_prf;
1371
1372 -- Loop for all priorities at unit offering pattern level
1373 FOR cur_wlst_uop_pri_rec IN cur_wlst_uop_pri(cur_get_teach_inst_rec.unit_cd ,
1374 cur_get_teach_inst_rec.version_number ,
1375 cur_get_teach_inst_rec.cal_type ,
1376 cur_get_teach_inst_rec.ci_sequence_number)
1377 LOOP
1378 l_wlst_level := 'UNIT_PAT';
1379 IF cur_wlst_uop_pri_rec.priority_value = 'PROGRAM' THEN
1380 l_pref_satisfied := FALSE;
1381 FOR cur_wlst_uop_prf_rec IN cur_wlst_uop_prf(cur_wlst_uop_pri_rec.unit_ofr_waitlist_priority_id)
1382 LOOP
1383 l_pref_order := l_pref_order + 1;
1384 OPEN cur_program (p_person_id ,
1385 cur_wlst_uop_prf_rec.preference_code ,
1386 cur_wlst_uop_prf_rec.preference_version);
1387 FETCH cur_program INTO l_pri_satisfied;
1388 IF cur_program%FOUND THEN
1389 l_pref_satisfied := TRUE;
1390 l_pref_weight := l_pref_weight + power(2,(l_max_uop_pref-l_pref_order));
1391 END IF;
1392 CLOSE cur_program;
1393 END LOOP; -- End of cur_wlst_uop_prf
1394 IF l_pref_satisfied THEN
1395 l_pri_weight := l_pri_weight + power(2,(l_max_uop_pri-cur_wlst_uop_pri_rec.priority_number));
1396 END IF;
1397
1398 ELSIF cur_wlst_uop_pri_rec.priority_value = 'ORG_UNIT' THEN
1399 l_pref_satisfied := FALSE;
1400 FOR cur_wlst_uop_prf_rec IN cur_wlst_uop_prf(cur_wlst_uop_pri_rec.unit_ofr_waitlist_priority_id)
1401 LOOP
1402 l_pref_order := l_pref_order + 1;
1403 OPEN cur_org (p_person_id ,
1404 cur_wlst_uop_prf_rec.preference_code);
1405 FETCH cur_org INTO l_pri_satisfied;
1406 IF cur_org%FOUND THEN
1407 l_pref_satisfied := TRUE;
1408 l_pref_weight := l_pref_weight + power(2,(l_max_uop_pref-l_pref_order));
1409 END IF;
1410 CLOSE cur_org;
1411 END LOOP; -- End of cur_wlst_uop_prf
1412 IF l_pref_satisfied THEN
1413 l_pri_weight := l_pri_weight + power(2,(l_max_uop_pri-cur_wlst_uop_pri_rec.priority_number));
1414 END IF;
1415
1416 ELSIF cur_wlst_uop_pri_rec.priority_value = 'UNIT_SET' THEN
1417 l_pref_satisfied := FALSE;
1418 FOR cur_wlst_uop_prf_rec IN cur_wlst_uop_prf(cur_wlst_uop_pri_rec.unit_ofr_waitlist_priority_id)
1419 LOOP
1420 l_pref_order := l_pref_order + 1;
1421 OPEN cur_unit_set (p_person_id ,
1422 cur_wlst_uop_prf_rec.preference_code ,
1423 cur_wlst_uop_prf_rec.preference_version);
1424 FETCH cur_unit_set INTO l_pri_satisfied;
1425 IF cur_unit_set%FOUND THEN
1426 l_pref_satisfied := TRUE;
1427 l_pref_weight := l_pref_weight + power(2,(l_max_uop_pref-l_pref_order));
1428 END IF;
1429 CLOSE cur_unit_set;
1430 END LOOP; -- End of cur_wlst_uop_prf
1431 IF l_pref_satisfied THEN
1432 l_pri_weight := l_pri_weight + power(2,(l_max_uop_pri-cur_wlst_uop_pri_rec.priority_number));
1433 END IF;
1434
1435 ELSIF cur_wlst_uop_pri_rec.priority_value = 'PROGRAM_STAGE' THEN
1436 l_pref_satisfied := FALSE;
1437 FOR cur_wlst_uop_prf_rec IN cur_wlst_uop_prf(cur_wlst_uop_pri_rec.unit_ofr_waitlist_priority_id)
1438 LOOP
1439 l_pref_order := l_pref_order + 1;
1440
1441 -- Call the function to determine whether the student completed the given program stage
1442 IF igs_en_gen_015.enrp_val_ps_stage(p_person_id, p_program_cd, l_program_version,
1443 cur_wlst_uop_prf_rec.preference_code)
1444 THEN
1445 l_pref_satisfied := TRUE;
1446 l_pref_weight := l_pref_weight + power(2,(l_max_uop_pref-l_pref_order));
1447 END IF;
1448 END LOOP; -- End of cur_wlst_uop_prf
1449 IF l_pref_satisfied THEN
1450 l_pri_weight := l_pri_weight + power(2,(l_max_uop_pri-cur_wlst_uop_pri_rec.priority_number));
1451 END IF;
1452
1453 ELSIF cur_wlst_uop_pri_rec.priority_value = 'CLASS_STD' THEN
1454 l_pref_satisfied := FALSE;
1455 FOR cur_wlst_uop_prf_rec IN cur_wlst_uop_prf(cur_wlst_uop_pri_rec.unit_ofr_waitlist_priority_id)
1456 LOOP
1457 l_pref_order := l_pref_order + 1;
1458
1459 -- Call the function to determine the class standing of the given student
1460 IF igs_pr_get_class_std.get_class_standing(p_person_id, p_program_cd, 'Y', SYSDATE, NULL, NULL)
1461 = cur_wlst_uop_prf_rec.preference_code
1462 THEN
1463 l_pref_satisfied := TRUE;
1464 l_pref_weight := l_pref_weight + power(2,(l_max_uop_pref-l_pref_order));
1465 END IF;
1466 END LOOP; -- End of cur_wlst_uop_prf
1467 IF l_pref_satisfied THEN
1468 l_pri_weight := l_pri_weight + power(2,(l_max_uop_pri-cur_wlst_uop_pri_rec.priority_number));
1469 END IF;
1470 END IF; -- End of cur_wlst_uop_pri_rec.priority_value
1471 END LOOP; -- End of cur_wlst_uop_pri
1472 END IF;
1473
1474 IF l_wlst_level IS NULL THEN
1475
1476 -- Waitlist setup is not defined at unit section / unit offering level,
1477 -- Check whether the setup is defined at organization unit level.
1478
1479 -- Get the organizational unit code associated with the unit section
1480 OPEN cur_org_unit_cd(p_uoo_id);
1481 FETCH cur_org_unit_cd INTO l_org_unit_cd;
1482 CLOSE cur_org_unit_cd;
1483
1484 -- Get the Load Calender type associated with the teach calender
1485 OPEN cur_teach_to_load(l_teach_cal_type,l_teach_seq_no);
1486 FETCH cur_teach_to_load INTO l_load_cal_type;
1487 CLOSE cur_teach_to_load;
1488
1489 -- Get the maximum number of priorities at organizational unit level
1490 OPEN cur_max_org_pri(l_org_unit_cd, l_load_cal_type);
1491 FETCH cur_max_org_pri INTO l_max_org_pri;
1492 CLOSE cur_max_org_pri;
1493
1494 -- Get the maximum number of preferences among all the priorities at organizational unit level
1495 OPEN cur_max_org_prf(l_org_unit_cd, l_load_cal_type);
1496 FETCH cur_max_org_prf INTO l_max_org_pref;
1497 CLOSE cur_max_org_prf;
1498
1499 -- Loop for all priorities at organizational unit level
1500 FOR cur_wlst_org_pri_rec IN cur_wlst_org_pri(l_org_unit_cd, l_load_cal_type)
1501 LOOP
1502 l_wlst_level := 'ORG_UNIT';
1503 IF cur_wlst_org_pri_rec.priority_value = 'PROGRAM' THEN
1504 l_pref_satisfied := FALSE;
1505 FOR cur_wlst_org_prf_rec IN cur_wlst_org_prf(cur_wlst_org_pri_rec.org_unit_wlst_pri_id)
1506 LOOP
1507 l_pref_order := l_pref_order + 1;
1508 OPEN cur_program (p_person_id ,
1509 cur_wlst_org_prf_rec.preference_code ,
1510 cur_wlst_org_prf_rec.preference_version);
1511 FETCH cur_program INTO l_pri_satisfied;
1512 IF cur_program%FOUND THEN
1513 l_pref_satisfied := TRUE;
1514 l_pref_weight := l_pref_weight + power(2,(l_max_org_pref-l_pref_order));
1515 END IF;
1516 CLOSE cur_program;
1517 END LOOP; -- End of cur_wlst_org_prf
1518 IF l_pref_satisfied THEN
1519 l_pri_weight := l_pri_weight + power(2,(l_max_org_pri-cur_wlst_org_pri_rec.priority_number));
1520 END IF;
1521
1522 ELSIF cur_wlst_org_pri_rec.priority_value = 'ORG_UNIT' THEN
1523 l_pref_satisfied := FALSE;
1524 FOR cur_wlst_org_prf_rec IN cur_wlst_org_prf(cur_wlst_org_pri_rec.org_unit_wlst_pri_id)
1525 LOOP
1526 l_pref_order := l_pref_order + 1;
1527 OPEN cur_org (p_person_id ,
1528 cur_wlst_org_prf_rec.preference_code);
1529 FETCH cur_org INTO l_pri_satisfied;
1530 IF cur_org%FOUND THEN
1531 l_pref_satisfied := TRUE;
1532 l_pref_weight := l_pref_weight + power(2,(l_max_org_pref-l_pref_order));
1533 END IF;
1534 CLOSE cur_org;
1535 END LOOP; -- End of cur_wlst_org_prf
1536 IF l_pref_satisfied THEN
1537 l_pri_weight := l_pri_weight + power(2,(l_max_org_pri-cur_wlst_org_pri_rec.priority_number));
1538 END IF;
1539
1540 ELSIF cur_wlst_org_pri_rec.priority_value = 'UNIT_SET' THEN
1541 l_pref_satisfied := FALSE;
1542 FOR cur_wlst_org_prf_rec IN cur_wlst_org_prf(cur_wlst_org_pri_rec.org_unit_wlst_pri_id)
1543 LOOP
1544 l_pref_order := l_pref_order + 1;
1545 OPEN cur_unit_set (p_person_id ,
1546 cur_wlst_org_prf_rec.preference_code ,
1547 cur_wlst_org_prf_rec.preference_version);
1548 FETCH cur_unit_set INTO l_pri_satisfied;
1549 IF cur_unit_set%FOUND THEN
1550 l_pref_satisfied := TRUE;
1551 l_pref_weight := l_pref_weight + power(2,(l_max_org_pref-l_pref_order));
1552 END IF;
1553 CLOSE cur_unit_set;
1554 END LOOP; -- End of cur_wlst_org_prf
1555 IF l_pref_satisfied THEN
1556 l_pri_weight := l_pri_weight + power(2,(l_max_org_pri-cur_wlst_org_pri_rec.priority_number));
1557 END IF;
1558
1559 ELSIF cur_wlst_org_pri_rec.priority_value = 'PROGRAM_STAGE' THEN
1560 l_pref_satisfied := FALSE;
1561 FOR cur_wlst_org_prf_rec IN cur_wlst_org_prf(cur_wlst_org_pri_rec.org_unit_wlst_pri_id)
1562 LOOP
1563 l_pref_order := l_pref_order + 1;
1564
1565 -- Call the function to determine whether the student completed the given program stage
1566 IF igs_en_gen_015.enrp_val_ps_stage(p_person_id, p_program_cd, l_program_version,
1567 cur_wlst_org_prf_rec.preference_code)
1568 THEN
1569 l_pref_satisfied := TRUE;
1570 l_pref_weight := l_pref_weight + power(2,(l_max_org_pref-l_pref_order));
1571 END IF;
1572 END LOOP; -- End of cur_wlst_org_prf
1573 IF l_pref_satisfied THEN
1574 l_pri_weight := l_pri_weight + power(2,(l_max_org_pri-cur_wlst_org_pri_rec.priority_number));
1575 END IF;
1576
1577 ELSIF cur_wlst_org_pri_rec.priority_value = 'CLASS_STD' THEN
1578 l_pref_satisfied := FALSE;
1579 FOR cur_wlst_org_prf_rec IN cur_wlst_org_prf(cur_wlst_org_pri_rec.org_unit_wlst_pri_id)
1580 LOOP
1581 l_pref_order := l_pref_order + 1;
1582
1583 -- Call the function to determine the class standing of the given student
1584 IF igs_pr_get_class_std.get_class_standing(p_person_id, p_program_cd, 'Y', SYSDATE, NULL, NULL)
1585 = cur_wlst_org_prf_rec.preference_code
1586 THEN
1587 l_pref_satisfied := TRUE;
1588 l_pref_weight := l_pref_weight + power(2,(l_max_org_pref-l_pref_order));
1589 END IF;
1590 END LOOP; -- End of cur_wlst_org_prf
1591 IF l_pref_satisfied THEN
1592 l_pri_weight := l_pri_weight + power(2,(l_max_org_pri-cur_wlst_org_pri_rec.priority_number));
1593 END IF;
1594 END IF; -- End of cur_wlst_org_pri_rec.priority_value
1595 END LOOP; -- End of cur_wlst_org_pri
1596 END IF;
1597
1598 IF l_wlst_level IS NULL THEN
1599 p_priority_weight := NULL;
1600 p_preference_weight := NULL;
1601 ELSE
1602 p_priority_weight := l_pri_weight;
1603 p_preference_weight := l_pref_weight;
1604 END IF;
1605
1606 END enrp_wlst_pri_pref_calc;
1607
1608 PROCEDURE inform_stud_not(itemtype IN VARCHAR2,
1609 itemkey IN VARCHAR2,
1610 actid IN NUMBER,
1611 funcmode IN VARCHAR2,
1612 resultout OUT NOCOPY VARCHAR2)
1613
1614 ------------------------------------------------------------------
1615 --Created by : rnirwani, Oracle IDC
1616 --Date created: 12-DEC-2003
1617 --
1618 --Purpose: This procedure sets the values for person number
1619 -- and calendar description.
1620 --
1621 --Known limitations/enhancements and/or remarks:
1622 --
1623 --Change History:
1624 --Who When What
1625 -------------------------------------------------------------------
1626
1627 IS
1628
1629
1630 -- cursor to get the Person Number corresponding to the person_id
1631 CURSOR c_person_num (cp_person_id hz_parties.party_id%TYPE) IS
1632 SELECT party_number
1633 FROM hz_parties
1634 WHERE party_id = cp_person_id;
1635
1636 -- cursor to get the calendar description
1637 CURSOR c_cal_desc (pc_cal_type igs_ca_inst.cal_type%TYPE, pc_seq_num igs_ca_inst.sequence_number%TYPE) IS
1638 SELECT description
1639 FROM igs_ca_inst
1640 WHERE cal_type=pc_cal_type
1641 AND sequence_number=pc_seq_num;
1642
1643 l_party_id hz_parties.party_id%TYPE;
1644 l_cal_type igs_ca_inst.cal_type%TYPE;
1645 l_seq_num igs_ca_inst.sequence_number%TYPE;
1646 l_party_number hz_parties.party_number%TYPE;
1647 l_description igs_ca_inst.description%TYPE;
1648
1649 BEGIN
1650
1651
1652
1653 IF (funcmode = 'RUN') THEN
1654
1655 --
1656 -- fetch student for whom the record has been procesed and add the user name to the
1657 -- adhoc role
1658 --
1659 --
1660 l_party_id := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_ID');
1661 l_cal_type := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_LOAD_CAL_TYPE');
1662 l_seq_num := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_LOAD_CA_SEQ_NUM');
1663
1664
1665 -- Getting the Person Number
1666 OPEN c_person_num(l_party_id);
1667 FETCH c_person_num INTO l_party_number;
1668 CLOSE c_person_num;
1669
1670
1671 -- Setting the Load calendar description
1672 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1673 ItemKey => itemkey,
1674 aname => 'P_PERSON_NUM',
1675 avalue => l_party_number
1676 );
1677
1678
1679
1680 -- Getting the Load Calendar Description
1681 OPEN c_cal_desc(l_cal_type,l_seq_num);
1682 FETCH c_cal_desc INTO l_description;
1683 CLOSE c_cal_desc;
1684
1685
1686 -- Setting the Load calendar description
1687 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1688 ItemKey => itemkey,
1689 aname => 'P_LOAD_CAL_DESC',
1690 avalue => l_description
1691 );
1692
1693 Resultout:= 'COMPLETE:';
1694 RETURN;
1695 END IF;
1696
1697 END inform_stud_not;
1698
1699
1700
1701 END igs_en_wlst_gen_proc ;---------
[Home] [Help]