1 PACKAGE BODY igs_az_gen_001 AS
2 /* $Header: IGSAZ01B.pls 120.9 2006/06/06 13:47:55 swaghmar ship $ */
3
4 /***********************************************************************************************
5 Created By : Girish Jha
6 Date Created By : 14 May 2003
7 Purpose : This package is the generaic package for advising functionality. This contains the routines
8 for Maintaining the advising group, apply advising holds on the students of the group and
9 sending the notifications to students and advisors.
10 This is modular approach to make the routines which can be called from 1. Concurrent program
11 2. Self service pages 3. Any pl/sql block separately.
12 Remarks : None
13 Change History
14 Who When What
15 -----------------------------------------------------------
16 Girish Jha 12-May-2003 New Package created.
17 anilk 03-Jul-2003 Fixed Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
18 smanglm 05-Aug-2003 Bug 3084766: Make use of Dynamic Person ID Group if the igs_pe_persid_group_all.FILE_NAME
19 is not null else make use of static query. Changes made in the
20 following cursors of maintain_groups:
21 cur_std_to_add
22 cur_adv_to_add
23 cur_std_to_del
24 cur_adv_to_del
25 kdande 03-Sep-2003 Bug# 3034714
26 Changed the log format for the Maintain Advising Group job as per the format
27 mentioned in the FD.
28 | nmankodi 11-Apr-2005 fnd_user.customer_id column has been changed to
29 | fnd_user.person_party_id as an ebizsuite wide TCA mandate.
30 |swaghmar 16-Jan-2006 Bug# 4951054 Added check for disabling UI's
31 | sepalani 27-Mar-2006 added validation check for empty string values on student group and
32 | advisor group person ids .
33 | sepalani 20-Apr-2006 Bug # 5188499: ISSUE WITH SUGGESTED MATCHES IN ADVISING GROUPS
34 ***********************************************************************************************/
35
36 PROCEDURE maintain_groups (
37 errbuf OUT NOCOPY VARCHAR2,
38 retcode OUT NOCOPY VARCHAR2,
39 p_group_name IN VARCHAR2 DEFAULT NULL,
40 p_apply_hold IN VARCHAR2 DEFAULT 'N',
41 p_notify IN VARCHAR2 DEFAULT 'Y'
42 ) IS
43 --
44 -- declare the ref cursor
45 --
46 TYPE ref_cur IS REF CURSOR;
47 --
48 -- Now declare the variables for the above ref curosr
49 --
50 cur_std_to_add ref_cur;
51 cur_adv_to_add ref_cur;
52 cur_std_to_del ref_cur;
53 cur_adv_to_del ref_cur;
54 --
55 -- Declare the out param for the funtion IGS_PE_DYNAMIC_PERSID_GROUP.IGS_GET_DYNAMIC_SQL
56 --
57 l_status VARCHAR2 (2000);
58 --
59 -- Cursor to check whether dynamic person_id_group has to be used or not based on the value
60 -- of igs_pe_persid_group_all.file_name for the given group_id
61 --
62 CURSOR c_is_filename_null (cp_group_id igs_pe_persid_group_all.GROUP_ID%TYPE) IS
63 SELECT 'Y'
64 FROM igs_pe_persid_group_all
65 WHERE GROUP_ID = cp_group_id AND file_name IS NULL;
66 --
67 l_adv_is_filename_null VARCHAR2 (1) := 'N';
68 l_std_is_filename_null VARCHAR2 (1) := 'N';
69 --
70 -- Cursor to select the advising groups to be processed .. If user passes Null as group name then
71 -- Select all the groups having AUTO_REFRESH_FLAG = 'Y' Also this should not be run for delivary method = 'Self advised'
72 --
73 CURSOR cur_grp_to_be_processed IS
74 SELECT azg.ROWID row_id,
75 azg.*
76 FROM igs_az_groups azg
77 WHERE (azg.group_name = p_group_name
78 AND azg.delivery_method_code <> 'SELF')
79 OR (p_group_name IS NULL
80 AND azg.auto_refresh_flag = 'Y');
81
82 CURSOR cur_stdt_to_be_updated(cp_group_name VARCHAR2,cp_student_person_id NUMBER) IS
83 SELECT azs.ROWID row_id,
84 azs.*
85 FROM igs_az_students azs
86 where GROUP_NAME = cp_group_name
87 AND STUDENT_PERSON_ID = cp_student_person_id;
88
89 CURSOR cur_advr_to_be_updated(cp_group_name VARCHAR2,cp_advisor_person_id NUMBER) IS
90 SELECT azs.ROWID row_id,
91 azs.*
92 FROM igs_az_advisors azs
93 where GROUP_NAME = cp_group_name
94 AND ADVISOR_PERSON_ID = cp_advisor_person_id;
95 --
96 -- Variable to store select the Students in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table
97 -- for the group for static group id
98 --
99 l_stc_std_to_add VARCHAR2 (2000)
100 := ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
101 || ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
102 || ' MINUS '
103 || ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :2 ';
104 --
105 -- Variable to store select the Students in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table
106 -- for the group for dynamic group id
107 --
108 l_dyn_std_to_add VARCHAR2 (2000)
109 := ' MINUS SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 ';
110 --
111 -- Variable to store to select the ADVISORS in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table
112 -- for the group for static group id
113 --
114 l_stc_adv_to_add VARCHAR2 (2000)
115 := ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
116 || ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
117 || ' MINUS '
118 || ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :2 ';
119 --
120 -- variable to store to select the ADVISORS in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table
121 -- for the group for dynamic group id
122 --
123 l_dyn_adv_to_add VARCHAR2 (2000)
124 := ' MINUS SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 ';
125 --
126 -- Variable to store to select the Students existing inthe IGS_AZ_STUDENTS table MINUS
127 -- those in the student person ID group for static group id
128 --
129 l_stc_std_to_del VARCHAR2 (2000)
130 := ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 '
131 || ' AND NVL(ACCEPT_DELETE_FLAG, ''N'') = ''N'' '
132 || ' MINUS '
133 || ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :2 '
134 || ' AND NVL(END_DATE, SYSDATE) >= SYSDATE AND trunc(START_DATE) <= trunc(SYSDATE) ';
135 --
136 -- To do see the effective date in person ID group
137 --
138 --
139 -- Variable to store to select the Students existing inthe IGS_AZ_STUDENTS table MINUS
140 -- those in the student person ID group for dynamic group id
141 --
142 l_dyn_std_to_del VARCHAR2 (2000)
143 := ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 '
144 || ' AND NVL(ACCEPT_DELETE_FLAG, ''N'') = ''N'' MINUS ';
145 --
146 -- Variable to store to select the advisors existing inthe IGS_AZ_STUDENTS table MINUS
147 -- those in the advisor person ID group for static group id
148 --
149 l_stc_adv_to_del VARCHAR2 (2000)
150 := ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 '
151 || ' MINUS '
152 || ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :2 '
153 || ' AND NVL(END_DATE, SYSDATE) >= SYSDATE AND trunc(START_DATE) <= trunc(SYSDATE) ';
154 --
155 -- Variable to store to select the advisors existing inthe IGS_AZ_STUDENTS table MINUS
156 -- those in the advisor person ID group for dynamic group id
157 --
158 l_dyn_adv_to_del VARCHAR2 (2000)
159 := ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 MINUS ';
160
161 -- sepalani For Bug # 5188499
162 --
163 -- Variable to store select the Students in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table who
164 -- has accept flag set to none
165 -- for the group for static group id
166 --
167 l_stc_std_to_upd VARCHAR2 (2000)
168 := ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
169 || ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
170 || ' MINUS '
171 || ' SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :2 '
172 || ' AND ACCEPT_ADD_FLAG IS NOT NULL';
173 --
174 -- Variable to store select the Students in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table who
175 -- has accept flag set to none
176 -- for the group for dynamic group id
177 --
178 l_dyn_std_to_upd VARCHAR2 (2000)
179 := ' MINUS SELECT STUDENT_PERSON_ID PERSON_ID FROM IGS_AZ_STUDENTS WHERE GROUP_NAME = :1 '
180 || ' AND ACCEPT_ADD_FLAG IS NOT NULL';
181 --
182 -- Variable to store to select the ADVISORS in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table who
183 -- has accept flag set to none
184 -- for the group for static group id
185 --
186 l_stc_adv_to_upd VARCHAR2 (2000)
187 := ' SELECT PERSON_ID FROM IGS_PE_PRSID_GRP_MEM_ALL WHERE GROUP_ID = :1 '
188 || ' AND trunc(START_DATE) <= trunc(SYSDATE) AND NVL(END_DATE, SYSDATE) >= SYSDATE '
189 || ' MINUS '
190 || ' SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :2 '
191 || ' AND ACCEPT_ADD_FLAG IS NOT NULL';
192 --
193 -- variable to store to select the ADVISORS in the existing Student PID grop minus that one for the IGS_AZ_STUDENTS table who
194 -- has accept flag set to none
195 -- for the group for dynamic group id
196 --
197 l_dyn_adv_to_upd VARCHAR2 (2000)
198 := ' MINUS SELECT ADVISOR_PERSON_ID PERSON_ID FROM IGS_AZ_ADVISORS WHERE GROUP_NAME = :1 '
199 || ' AND ACCEPT_ADD_FLAG IS NOT NULL';
200
201 --
202 -- Declare a variable to store the person_id that would be obtained from the above ref cursors
203 --
204 l_person_id igs_pe_person.person_id%TYPE;
205 --
206 -- Declare Local variables
207 --
208 lvAutoStdAddInd VARCHAR2 (1) := 'N';
209 lvAutoStdDelInd VARCHAR2 (1) := 'N';
210 lvAutoAdvAddInd VARCHAR2 (1) := 'N';
211 lvAutoAdvDelInd VARCHAR2 (1) := 'N';
212 lvAutoMatchInd VARCHAR2 (1) := 'N';
213 ldAddStdStartDate DATE := NULL;
214 ldDelStdStartDate DATE := NULL;
215 ldAddAdvStartDate DATE := NULL;
216 ldDelAdvStartDate DATE := NULL;
217 lnAddedStudents NUMBER;
218 lnAddedAdvisors NUMBER;
219 lnDelStudents NUMBER;
220 lnDelAdvisors NUMBER;
221 lnGrpCount NUMBER := 0;
222 lnSuggestedMatches NUMBER := 0;
223 lvcNotifErbuf VARCHAR2 (1000); -- Err buf code for making a call to send notification (its out parameter)
224 lvNotifRtCode VARCHAR2 (100);
225 lvcApplHldErbuf VARCHAR2 (1000); -- Err buf code for making a call to apply hold (its out parameter)
226 lvApplHldrtcode VARCHAR2 (100);
227 lvStdRowID VARCHAR2 (25); -- ROWID to be passed as parameter to IGS_AZ_STUDENTS_PKG.INSERT_ROW(An out parameter)
228 lnGrpStdID igs_az_students.group_student_id%TYPE; -- Group Student ID to be passed as parameter to IGS_AZ_STUDENTS_PKG.INSERT_ROW(An out parameter)
229 lvAdvRowID VARCHAR2 (25); -- ROWID to be passed as parameter to IGS_AZ_ADVISORS_PKG.INSERT_ROW(An out parameter)
230 lnGrpadvID igs_az_students.group_student_id%TYPE; -- Group Student ID to be passed as parameter to IGS_AZ_ADVISORS_PKG.INSERT_ROW(An out parameter)
231 lvReturnStatus VARCHAR2 (1); -- Parameter to be passed to the procedures which have RETURN_STATUS as an out paramere
232 lvMsgData VARCHAR2 (1000); -- Parameter to be passed to the procedures which have MSG_DATA as an out paramere
233 lnMsgCount NUMBER; -- Parameter to be passed to the procedures which have MSG_COUNT as an out paramere
234 -- sepalani
235 p_student_rec cur_stdt_to_be_updated%ROWTYPE;
236 p_advisor_rec cur_advr_to_be_updated%ROWTYPE;
237
238 --
239 BEGIN
240 --
241 -- Initialize the OUT params
242 --
243
244 retcode := 0;
245 errbuf := NULL;
246 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
247 SAVEPOINT s_maintain_groups;
248 --
249 -- Write the passed parameters to the Log File
250 --
251 fnd_message.set_name ('FND', 'CONC-ARGUMENTS');
252 fnd_file.put_line (fnd_file.log, fnd_message.get);
253 fnd_file.put_line (fnd_file.log, '+---------------------------------------------------------------------------+');
254 fnd_file.put_line (fnd_file.log, 'P_GROUP_NAME=''' || p_group_name || '''');
255 fnd_file.put_line (fnd_file.log, 'P_APPLY_HOLD=''' || p_apply_hold || '''');
256 fnd_file.put_line (fnd_file.log, 'P_NOTIFY=''' || p_notify || '''');
257 fnd_file.put_line (fnd_file.log, '+---------------------------------------------------------------------------+');
258 fnd_file.put_line (fnd_file.log, '');
259 --
260 FOR grp_rec IN cur_grp_to_be_processed LOOP
261 --
262 -- check whether the dynamic persid group has to be used or not for advisor
263 --
264 OPEN c_is_filename_null (grp_rec.advisor_group_id);
265 FETCH c_is_filename_null INTO l_adv_is_filename_null;
266 CLOSE c_is_filename_null;
267 --
268 -- check whether the dynamic persid group has to be used or not for student
269 --
270 OPEN c_is_filename_null (grp_rec.student_group_id);
271 FETCH c_is_filename_null INTO l_std_is_filename_null;
272 CLOSE c_is_filename_null;
273 --
274 -- Update the count of the group to be printed in log file
275 --
276 lnGrpCount := lnGrpCount + 1;
277 --
278 -- Initialize the counts to be logged in the log file
279 --
280 lnAddedStudents := 0;
281 lnAddedAdvisors := 0;
282 lnDelStudents := 0;
283 lnDelAdvisors := 0;
284 --
285 -- See if the value of AUTO_STDNT_ADD_FLAG is 'Y' if yes then START_DATE should be 'Y' else it should be null;
286 --
287 IF grp_rec.auto_stdnt_add_flag = 'Y' THEN
288 ldAddStdStartDate := SYSDATE;
289 lvAutoStdAddInd := 'Y';
290 ELSE
291 ldAddStdStartDate := NULL;
292 lvAutoStdAddInd := NULL;
293 END IF;
294 --
295 -- See if the value of AUTO_STDNT_REMOVE_FLAG is 'Y' if yes then START_DATE should be 'Y' else it should be null;
296 --
297 IF grp_rec.auto_stdnt_add_flag = 'Y' THEN
298 ldDelStdStartDate := SYSDATE;
299 lvAutoStdDelInd := 'Y';
300 ELSE
301 ldDelStdStartDate := NULL;
302 lvAutoStdDelInd := NULL;
303 END IF;
304 --
305 -- See if the value of AUTO_ADVISOR_ADD_FLAG is 'Y' if yes then START_DATE should be 'Y' else it should be null;
306 --
307 IF grp_rec.auto_advisor_add_flag = 'Y' THEN
308 ldAddAdvStartDate := SYSDATE;
309 lvAutoAdvAddInd := 'Y';
310 ELSE
311 ldAddAdvStartDate := NULL;
312 lvAutoAdvAddInd := NULL;
313 END IF;
314 --
315 -- See if the value of AUTO_ADVISOR_REMOVE_FLAG is 'Y' if yes then START_DATE should be 'Y' else it should be null;
316 --
317 IF grp_rec.auto_advisor_remove_flag = 'Y' THEN
318 ldDelAdvStartDate := SYSDATE;
319 lvAutoAdvDelInd := 'Y';
320 ELSE
321 ldDelAdvStartDate := NULL;
322 lvAutoAdvDelInd := NULL;
323 END IF;
324 --
325 -- See if the auto match indiactor is Yes
326 --
327 IF grp_rec.auto_match_flag = 'Y' AND grp_rec.delivery_method_code = '1_ON_1' THEN
328 lvAutoMatchInd := 'Y';
329 ELSE
330 lvAutoMatchInd := 'N';
331 END IF;
332
333 --
334 -- Check if the Student group id is null or not
335 -- if it is not null the proceed with the adding to the Advising Group
336 --
337 IF (grp_rec.student_group_id IS NOT NULL) THEN
338 --
339 -- Loop through all the students who are suggested to be added to the group..
340 --
341 IF l_std_is_filename_null = 'N' THEN
342 l_dyn_std_to_add :=
343 igs_pe_dynamic_persid_group.igs_get_dynamic_sql (
344 grp_rec.student_group_id,
345 l_status
346 )
347 || l_dyn_std_to_add;
348 --
349 IF l_status <> fnd_api.g_ret_sts_success THEN
350 fnd_message.set_name ('IGS', 'IGS_AZ_DYN_PERS_ID_GRP_ERR');
351 fnd_msg_pub.ADD;
352 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
353 RAISE fnd_api.g_exc_error;
354 END IF;
355 --
356 OPEN cur_std_to_add FOR l_dyn_std_to_add USING grp_rec.group_name;
357 ELSE
358 OPEN cur_std_to_add FOR l_stc_std_to_add
359 USING grp_rec.student_group_id, grp_rec.group_name;
360 END IF;
361 --
362 LOOP
363 FETCH cur_std_to_add INTO l_person_id;
364 EXIT WHEN cur_std_to_add%NOTFOUND;
365 --
366 -- Update the count to be printed in log file
367 --
368 lnAddedStudents := lnAddedStudents + 1;
369 --
370 -- Make a call to the procedure to add the student with values for
371 -- Get the nextvalue from the sequence ..
372 --
373 -- Now call insert row
374 --
375 igs_az_students_pkg.insert_row (
376 x_rowid => lvStdRowID,
377 x_group_student_id => lnGrpStdID,
378 x_group_name => grp_rec.group_name,
379 x_student_person_id => l_person_id,
380 x_start_date => ldAddStdStartDate,
381 x_end_date => NULL,
382 x_advising_hold_type => NULL,
383 x_hold_start_date => NULL,
384 x_notified_date => NULL,
385 x_accept_add_flag => lvAutoStdAddInd,
386 x_accept_delete_flag => NULL,
387 x_return_status => lvReturnStatus,
388 x_msg_data => lvMsgData,
389 x_msg_count => lnMsgCount
390 );
391 --
392 -- To do error handling...
393 --
394 IF (lvReturnStatus <> fnd_api.g_ret_sts_success) THEN
395 retcode := 2;
396 IF (lnMsgCount = 1) THEN
397 errbuf := lvMsgData;
398 ELSE
399 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
400 END IF;
401 RAISE fnd_api.g_exc_error;
402 END IF;
403 END LOOP; -- for cur_std_to_add
404 CLOSE cur_std_to_add;
405
406 -- sepalani bug # 5188499
407
408 IF lnAddedStudents = 0 and lvAutoStdAddInd = 'Y' THEN
409
410
411
412 IF l_std_is_filename_null = 'N' THEN
413 l_dyn_std_to_upd :=
414 igs_pe_dynamic_persid_group.igs_get_dynamic_sql (
415 grp_rec.student_group_id,
416 l_status
417 )
418 || l_dyn_std_to_upd;
419 --
420 IF l_status <> fnd_api.g_ret_sts_success THEN
421 fnd_message.set_name ('IGS', 'IGS_AZ_DYN_PERS_ID_GRP_ERR');
422 fnd_msg_pub.ADD;
423 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
424 RAISE fnd_api.g_exc_error;
425 END IF;
426 OPEN cur_std_to_add FOR l_dyn_std_to_upd USING grp_rec.group_name;
427 ELSE
428 OPEN cur_std_to_add FOR l_stc_std_to_upd
429 USING grp_rec.student_group_id, grp_rec.group_name;
430 END IF;
431
432 LOOP
433
434 FETCH cur_std_to_add INTO l_person_id;
435 EXIT WHEN cur_std_to_add%NOTFOUND;
436
437 OPEN cur_stdt_to_be_updated (p_group_name, l_person_id);
438 FETCH cur_stdt_to_be_updated into p_student_rec;
439
440 IF lvAutoStdAddInd IS NULL THEN
441 lvAutoStdAddInd := p_student_rec.accept_add_flag;
442 END IF;
443
444 igs_az_students_pkg.update_row (
445 x_rowid => p_student_rec.row_id,
446 x_group_student_id => p_student_rec.group_student_id,
447 x_group_name => p_student_rec.group_name,
448 x_student_person_id => p_student_rec.student_person_id,
449 x_start_date => ldAddStdStartDate,
450 x_end_date => p_student_rec.end_date,
451 x_advising_hold_type => p_student_rec.advising_hold_type,
452 x_hold_start_date => p_student_rec.hold_start_date,
453 x_notified_date => p_student_rec.notified_date,
454 x_accept_add_flag => lvAutoStdAddInd,
455 x_accept_delete_flag => p_student_rec.accept_delete_flag,
456 x_return_status => lvReturnStatus,
457 x_msg_data => lvMsgData,
458 x_msg_count => lnMsgCount
459 );
460 CLOSE cur_stdt_to_be_updated;
461 --
462 -- To do error handling...
463 --
464 IF (lvReturnStatus <> fnd_api.g_ret_sts_success) THEN
465 retcode := 2;
466 IF (lnMsgCount = 1) THEN
467 errbuf := lvMsgData;
468 ELSE
469 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
470 END IF;
471 RAISE fnd_api.g_exc_error;
472 END IF;
473
474 END LOOP;
475 CLOSE cur_std_to_add;
476 END IF;
477
478 END IF ; -- IF (grp_rec.student_group_id IS NOT NULL) THEN
479
480 --
481 -- Check if the Advisor group id is null or not
482 -- if it is not null then proceed with the adding Advisors
483 --
484
485 IF (grp_rec.advisor_group_id IS NOT NULL) THEN
486
487 --
488 -- Loop through all the Advisors who are suggested to be added
489 --
490 IF l_adv_is_filename_null = 'N' THEN
491 l_dyn_adv_to_add :=
492 igs_pe_dynamic_persid_group.igs_get_dynamic_sql (
493 grp_rec.advisor_group_id,
494 l_status
495 )
496 || l_dyn_adv_to_add;
497 IF l_status <> fnd_api.g_ret_sts_success THEN
498 fnd_message.set_name ('IGS', 'IGS_AZ_DYN_PERS_ID_GRP_ERR');
499 fnd_msg_pub.ADD;
500 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
501 RAISE fnd_api.g_exc_error;
502 END IF;
503 OPEN cur_adv_to_add FOR l_dyn_adv_to_add USING grp_rec.group_name;
504 ELSE
505 OPEN cur_adv_to_add FOR l_stc_adv_to_add
506 USING grp_rec.advisor_group_id, grp_rec.group_name;
507 END IF;
508 LOOP
509 FETCH cur_adv_to_add INTO l_person_id;
510 EXIT WHEN cur_adv_to_add%NOTFOUND;
511 --
512 -- Update the count to be printed in log file
513 --
514 lnAddedAdvisors := lnAddedAdvisors + 1;
515 --
516 -- Make a call to the procedure to add the advisors with values for
517 -- auto accept and start dates properly
518 --
519 -- Make a call to insert row for the group advisor
520 --
521 igs_az_advisors_pkg.insert_row (
522 x_rowid => lvAdvRowID,
523 x_group_advisor_id => lnGrpadvID,
524 x_group_name => grp_rec.group_name,
525 x_advisor_person_id => l_person_id,
526 x_start_date => ldAddAdvStartDate,
527 x_end_date => NULL,
528 x_max_students_num => grp_rec.default_advisor_load_num, -- The maximum load initialized to the default load of the group
529 x_notified_date => NULL,
530 x_accept_add_flag => lvAutoAdvAddInd,
531 x_accept_delete_flag => NULL, --Todo uncomment following three parameters once the are added to TBH
532 x_return_status => lvReturnStatus,
533 x_msg_data => lvMsgData,
534 x_msg_count => lnMsgCount
535 );
536 --
537 -- To do Error handling
538 --
539 IF lvReturnStatus <> fnd_api.g_ret_sts_success THEN
540 retcode := 2;
541 IF lnMsgCount = 1 THEN
542 errbuf := lvMsgData;
543 ELSE
544 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
545 END IF;
546 RAISE fnd_api.g_exc_error;
547 END IF;
548 END LOOP; --cur_adv_to_add
549
550 CLOSE cur_adv_to_add;
551
552 -- sepalani bug # 5188499
553
554 IF lnAddedAdvisors = 0 and lvAutoAdvAddInd = 'Y' THEN
555
556 IF l_adv_is_filename_null = 'N' THEN
557 l_dyn_adv_to_upd :=
558 igs_pe_dynamic_persid_group.igs_get_dynamic_sql (
559 grp_rec.advisor_group_id,
560 l_status
561 )
562 || l_dyn_adv_to_upd;
563 IF l_status <> fnd_api.g_ret_sts_success THEN
564 fnd_message.set_name ('IGS', 'IGS_AZ_DYN_PERS_ID_GRP_ERR');
565 fnd_msg_pub.ADD;
566 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
567 RAISE fnd_api.g_exc_error;
568 END IF;
569 OPEN cur_adv_to_add FOR l_dyn_adv_to_upd USING grp_rec.group_name;
570 ELSE
571 OPEN cur_adv_to_add FOR l_stc_adv_to_upd
572 USING grp_rec.advisor_group_id, grp_rec.group_name;
573 END IF;
574
575 LOOP
576
577 FETCH cur_adv_to_add INTO l_person_id;
578 EXIT WHEN cur_adv_to_add%NOTFOUND;
579
580 OPEN cur_advr_to_be_updated (p_group_name, l_person_id);
581 FETCH cur_advr_to_be_updated into p_advisor_rec;
582
583 IF lvAutoAdvAddInd IS NULL THEN
584 lvAutoAdvAddInd := p_advisor_rec.accept_add_flag;
585 END IF;
586
587
588 igs_az_advisors_pkg.update_row(
589 x_rowid => p_advisor_rec.row_id,
590 x_group_advisor_id => p_advisor_rec.group_advisor_id,
591 x_group_name => p_advisor_rec.group_name,
592 x_advisor_person_id => p_advisor_rec.advisor_person_id,
593 x_start_date => ldAddAdvStartDate,
594 x_end_date => p_advisor_rec.end_date,
595 x_max_students_num => p_advisor_rec.max_students_num,
596 x_notified_date => SYSDATE, -- This is the only change
597 x_accept_add_flag => lvAutoAdvAddInd,
598 x_accept_delete_flag => p_advisor_rec.accept_delete_flag, ---To do Follwing three parameters need to be added in the TBH and then uncomment
599 x_return_status => lvReturnStatus,
600 x_msg_data => lvMsgData,
601 x_msg_count => lnMsgCount
602 );
603
604 CLOSE cur_advr_to_be_updated;
605 --
606 -- To do error handling...
607 --
608 IF (lvReturnStatus <> fnd_api.g_ret_sts_success) THEN
609 retcode := 2;
610 IF (lnMsgCount = 1) THEN
611 errbuf := lvMsgData;
612 ELSE
613 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
614 END IF;
615 RAISE fnd_api.g_exc_error;
616 END IF;
617
618 END LOOP;
619 CLOSE cur_adv_to_add;
620 END IF;
621
622
623 END IF; -- IF (grp_rec.advisor_group_id IS NOT NULL) THEN
624
625 --
626 -- Check if the Student group id is null or not
627 -- if it is not null the proceed with removing the suggested Students
628 --
629
630 IF (grp_rec.student_group_id IS NOT NULL) THEN
631 --
632 -- Loop through all the students who are suggested to be removed
633 --
634 IF l_std_is_filename_null = 'N' THEN
635 l_dyn_std_to_del :=
636 l_dyn_std_to_del
637 || igs_pe_dynamic_persid_group.igs_get_dynamic_sql (
638 grp_rec.student_group_id,
639 l_status
640 );
641 IF l_status <> fnd_api.g_ret_sts_success THEN
642 fnd_message.set_name ('IGS', 'IGS_AZ_DYN_PERS_ID_GRP_ERR');
643 fnd_msg_pub.ADD;
644 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
645 RAISE fnd_api.g_exc_error;
646 END IF;
647 OPEN cur_std_to_del FOR l_dyn_std_to_del USING grp_rec.group_name;
648 ELSE
649 OPEN cur_std_to_del FOR l_stc_std_to_del
650 USING grp_rec.group_name, grp_rec.student_group_id;
651 END IF;
652 LOOP
653 FETCH cur_std_to_del INTO l_person_id;
654 EXIT WHEN cur_std_to_del%NOTFOUND;
655 --
656 -- Update the count to be printed in log file
657 --
658 lnDelStudents := lnDelStudents + 1;
659 --
660 -- Make a call to the procedure to update the students with values for auto accept and end datesproperly
661 --
662 end_date_student (grp_rec.group_name, l_person_id, SYSDATE, 'C');
663 END LOOP; --cur_std_to_del
664 CLOSE cur_std_to_del;
665 END IF; -- IF (grp_rec.student_group_id IS NOT NULL) THEN
666
667 --
668 -- Check if the advisor group id is null or not
669 -- if it is not null the proceed with removing the suggested advisors
670 --
671
672 IF (grp_rec.advisor_group_id IS NOT NULL) THEN
673
674 --
675 -- Loop through all the advisors who are suggested to be removed
676 --
677 IF l_adv_is_filename_null = 'N' THEN
678 l_dyn_adv_to_del :=
679 l_dyn_adv_to_del
680 || igs_pe_dynamic_persid_group.igs_get_dynamic_sql (
681 grp_rec.advisor_group_id,
682 l_status
683 );
684 IF l_status <> fnd_api.g_ret_sts_success THEN
685 fnd_message.set_name ('IGS', 'IGS_AZ_DYN_PERS_ID_GRP_ERR');
686 fnd_msg_pub.ADD;
687 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
688 RAISE fnd_api.g_exc_error;
689 END IF;
690 OPEN cur_adv_to_del FOR l_dyn_adv_to_del USING grp_rec.group_name;
691 ELSE
692 OPEN cur_adv_to_del FOR l_stc_adv_to_del
693 USING grp_rec.group_name, grp_rec.advisor_group_id;
694 END IF;
695 LOOP
696 FETCH cur_adv_to_del INTO l_person_id;
697 EXIT WHEN cur_adv_to_del%NOTFOUND;
698 --
699 -- Update the count to be printed in log file
700 --
701 lnDelAdvisors := lnDelAdvisors + 1;
702 --
703 -- Make a call to the procedure to update the Advisors with values for auto accept and end datesproperly
704 --
705 end_date_advisor (grp_rec.group_name, l_person_id, SYSDATE, 'C');
706 END LOOP; --cur_adv_to_del
707 CLOSE cur_adv_to_del;
708
709 END IF ;-- IF (grp_rec.advisor_group_id IS NOT NULL) THEN
710
711 --
712 -- See if the match is to be provided for the Students and the advisors.
713 --
714 IF lvAutoMatchInd = 'Y' THEN
715 assign_students_to_advisors (
716 grp_rec.group_name,
717 lnSuggestedMatches,
718 SYSDATE
719 );
720 END IF;
721 --
722 -- Print the Statistics in the log file:
723 --
724 fnd_file.put_line (fnd_file.log, lnGrpCount || '. ' || grp_rec.group_name || ' - ' || grp_rec.group_desc);
725 --
726 fnd_message.set_name ('IGS', 'IGS_AZ_SUG_STU_ADD');
727 fnd_message.set_token ('ADDSTU', lnAddedStudents);
728 fnd_message.set_token ('AUTOSTD', lvAutoStdAddInd);
729 fnd_file.put_line (fnd_file.log, ' o ' || fnd_message.get);
730 --
731 fnd_message.set_name ('IGS', 'IGS_AZ_SUG_STU_REM');
732 fnd_message.set_token ('DELSTU', lnDelStudents);
733 fnd_message.set_token ('AUTOST', lvAutoStdDelInd);
734 fnd_file.put_line (fnd_file.log, ' o ' || fnd_message.get);
735 --
736 fnd_message.set_name ('IGS', 'IGS_AZ_SUG_ADV_ADD');
737 fnd_message.set_token ('ADDADV', lnAddedAdvisors);
738 fnd_message.set_token ('AUTOADV', lvAutoAdvAddInd);
739 fnd_file.put_line (fnd_file.log, ' o ' || fnd_message.get);
740 --
741 fnd_message.set_name ('IGS', 'IGS_AZ_SUG_ADV_REM');
742 fnd_message.set_token ('DELADV', lnDelAdvisors);
743 fnd_message.set_token ('AUTOADV', lvAutoAdvDelInd);
744 fnd_file.put_line (fnd_file.log, ' o ' || fnd_message.get);
745 --
746 fnd_message.set_name ('IGS', 'IGS_AZ_SUG_MATCH');
747 fnd_message.set_token ('SUGGMAT', lnSuggestedMatches);
748 fnd_message.set_token ('AUTOMAT', lvAutoMatchInd);
749 fnd_file.put_line (fnd_file.log, ' o ' || fnd_message.get);
750 fnd_file.put_line (fnd_file.log, '');
751 --
752 -- See if the auto notification parameter is passed as Yes
753 --
754 IF (p_notify = 'Y') THEN
755 --
756 -- Make a call to send notification as group name as parameter.
757 --
758 send_notification (
759 errbuf => lvcNotifErbuf,
760 retcode => lvNotifrtcode,
761 p_group_name => grp_rec.group_name
762 );
763 --
764 -- to do error handling and loggin
765 --
766 END IF; -- End Notify
767 --
768 -- See if the auto apply hold is passed as Yes
769 -- If Y then call the procedure to Apply the hold also see if the
770 -- advising group has a default advising hold defined.
771 --
772 IF ((p_apply_hold = 'Y' OR
773 grp_rec.auto_apply_hold_flag = 'Y') AND
774 grp_rec.advising_hold_type IS NOT NULL) THEN
775 apply_hold (
776 errbuf => lvcApplHldErbuf,
777 retcode => lvApplHldrtcode,
778 p_group_name => grp_rec.group_name,
779 p_notify => p_notify
780 );
781 --
782 -- to do error handling and loggin
783 --
784 END IF; -- End Apply Hold
785 --
786 -- Now update the IGS_AZ_GROUPS table for last_auto_refres_dt with SYSDATE.
787 --
788 igs_az_groups_pkg.update_row (
789 x_rowid => grp_rec.row_id,
790 x_group_name => grp_rec.group_name,
791 x_group_desc => grp_rec.group_desc,
792 x_advising_code => grp_rec.advising_code,
793 x_resp_org_unit_cd => grp_rec.resp_org_unit_cd,
794 x_resp_person_id => grp_rec.resp_person_id,
795 x_location_cd => grp_rec.location_cd,
796 x_delivery_method_code => grp_rec.delivery_method_code,
797 x_advisor_group_id => grp_rec.advisor_group_id,
798 x_student_group_id => grp_rec.student_group_id,
799 x_default_advisor_load_num => grp_rec.default_advisor_load_num,
800 x_mandatory_flag => grp_rec.mandatory_flag,
801 x_advising_sessions_num => grp_rec.advising_sessions_num,
802 x_advising_hold_type => grp_rec.advising_hold_type,
803 x_closed_flag => grp_rec.closed_flag,
804 x_comments_txt => grp_rec.comments_txt,
805 x_auto_refresh_flag => grp_rec.auto_refresh_flag,
806 x_last_auto_refresh_date => SYSDATE, -- only change.
807 x_auto_stdnt_add_flag => grp_rec.auto_stdnt_add_flag,
808 x_auto_stdnt_remove_flag => grp_rec.auto_stdnt_remove_flag,
809 x_auto_advisor_add_flag => grp_rec.auto_advisor_add_flag,
810 x_auto_advisor_remove_flag => grp_rec.auto_advisor_remove_flag,
811 x_auto_match_flag => grp_rec.auto_match_flag,
812 x_auto_apply_hold_flag => grp_rec.auto_apply_hold_flag,
813 x_attribute_category => grp_rec.attribute_category,
814 x_attribute1 => grp_rec.attribute1,
815 x_attribute2 => grp_rec.attribute2,
816 x_attribute3 => grp_rec.attribute3,
817 x_attribute4 => grp_rec.attribute4,
818 x_attribute5 => grp_rec.attribute5,
819 x_attribute6 => grp_rec.attribute6,
820 x_attribute7 => grp_rec.attribute7,
821 x_attribute8 => grp_rec.attribute8,
822 x_attribute9 => grp_rec.attribute9,
823 x_attribute10 => grp_rec.attribute10,
824 x_attribute11 => grp_rec.attribute11,
825 x_attribute12 => grp_rec.attribute12,
826 x_attribute13 => grp_rec.attribute13,
827 x_attribute14 => grp_rec.attribute14,
828 x_attribute15 => grp_rec.attribute15,
829 x_attribute16 => grp_rec.attribute16,
830 x_attribute17 => grp_rec.attribute17,
831 x_attribute18 => grp_rec.attribute18,
832 x_attribute19 => grp_rec.attribute19,
833 x_attribute20 => grp_rec.attribute20, --To Do --see if the following three parameters are required.
834 x_return_status => lvReturnStatus,
835 x_msg_data => lvMsgData,
836 x_msg_count => lnMsgCount
837 );
838 END LOOP; -- main
839 fnd_message.set_name ('IGS', 'IGS_AD_TOT_REC_PRC');
840 fnd_message.set_token ('RCOUNT', lnGrpCount);
841 fnd_file.put_line (fnd_file.log, fnd_message.get);
842 fnd_file.put_line (fnd_file.log, '');
843 --
844 EXCEPTION
845 WHEN fnd_api.g_exc_error THEN
846 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
847 retcode := 2;
848 ROLLBACK TO s_maintain_groups;
849 --
850 WHEN OTHERS THEN
851 retcode := 2;
852 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
853 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
854 fnd_message.set_token ('NAME', 'IGS_AZ_GEN_001.Maintain_group : ' || SUBSTR (SQLERRM, 80));
855 fnd_msg_pub.add;
856 fnd_msg_pub.count_and_get(
857 p_encoded => fnd_api.g_false,
858 p_count => lnMsgCount,
859 p_data => lvMsgData
860 );
861 ROLLBACK TO s_maintain_groups ;
862 --
863 END maintain_groups;
864 --
865 --
866 --
867 PROCEDURE assign_students_to_advisors (
868 p_group_name IN VARCHAR2,
869 p_n_processed OUT NOCOPY NUMBER,
870 p_start_date IN DATE DEFAULT NULL
871 ) AS
872 --
873 lnProcessed NUMBER := 0;
874 --
875 -- Select all the students who need to be assigned to an advisor
876 --
877 -- Cursor to detremine whether Match is to start dated automatically:
878 --
879 CURSOR auto_match_cur IS
880 SELECT auto_match_flag
881 FROM igs_az_groups
882 WHERE group_name = p_group_name;
883 --
884 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
885 --
886 CURSOR cur_students_to_match IS
887 SELECT stu.group_student_id,
888 stu.end_date
889 FROM igs_az_students stu
890 WHERE TRUNC (stu.start_date) <= TRUNC (SYSDATE)
891 AND NVL (stu.end_date, SYSDATE + 1) > SYSDATE
892 AND stu.group_name = p_group_name
893 AND NOT EXISTS
894 (SELECT 1
895 FROM igs_az_advising_rels rel
896 WHERE rel.group_name = p_group_name
897 AND rel.group_student_id = stu.group_student_id);
898 --
899 -- Select the advisor details for allocating the student
900 --
901 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
902 --
903 CURSOR cur_advisors_to_load IS
904 SELECT adv.group_advisor_id,
905 adv.end_date,
906 adv.max_students_num maximum_load,
907 NVL (rel.actual_load, 0) actual_load,
908 NVL (rel.actual_load, 0) / NVL (adv.max_students_num, 1) percent_load
909 FROM igs_az_advisors adv, (SELECT group_advisor_id,
910 COUNT(*) actual_load
911 FROM igs_az_advising_rels
912 WHERE group_name = p_group_name
913 GROUP BY group_advisor_id) rel
914 WHERE adv.start_date IS NOT NULL
915 AND TRUNC (adv.start_date) <= TRUNC (SYSDATE)
916 AND NVL (adv.end_date, SYSDATE + 1) > SYSDATE
917 AND adv.group_name = p_group_name
918 AND rel.group_advisor_id (+) = adv.group_advisor_id
919 ORDER BY percent_load;
920 --
921 -- Cursor to find the count the number of students
922 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
923 --
924 CURSOR cur_find_count IS
925 SELECT COUNT (group_student_id)
926 FROM igs_az_students st
927 WHERE TRUNC (start_date) <= SYSDATE
928 AND NVL (end_date, SYSDATE + 1) > SYSDATE
929 AND group_name = p_group_name
930 AND NOT EXISTS
931 (SELECT 1
932 FROM igs_az_advising_rels rel
933 WHERE group_name = p_group_name
934 AND rel.group_student_id = st.group_student_id);
935 --
936 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
937 --
938 CURSOR cur_max_stu_num IS
939 SELECT SUM (max_students_num)
940 FROM igs_az_advisors
941 WHERE TRUNC (start_date) <= TRUNC (SYSDATE)
942 AND NVL (end_date, SYSDATE + 1) > SYSDATE
943 AND group_name = p_group_name;
944 --
945 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
946 --
947 CURSOR cur_tot_act_load IS
948 SELECT COUNT (*)
949 FROM igs_az_advising_rels rel
950 WHERE group_name = p_group_name
951 AND EXISTS
952 (SELECT 1
953 FROM igs_az_advisors adv
954 WHERE rel.group_advisor_id = adv.group_advisor_id
955 AND TRUNC (adv.start_date) <= TRUNC (SYSDATE)
956 AND NVL (adv.end_date, SYSDATE + 1) > SYSDATE
957 AND adv.group_name = p_group_name);
958 --
959 -- Declare local variables
960 --
961 rec_advisors_to_load cur_advisors_to_load%ROWTYPE;
962 numberOfStudentsToProcess NUMBER;
963 totalActualLoad NUMBER;
964 totalMaximumLoad NUMBER;
965 desiredLoadPercentage NUMBER;
966 lv_rowid VARCHAR2(25);
967 l_group_advising_rel_id igs_az_advising_rels.group_advising_rel_id%TYPE;
968 ldRelEndDate DATE;
969 ldRelStartDate DATE;
970 lvcReturnStatus VARCHAR2(10);
971 lvcMsgData VARCHAR2(2000);
972 lnMsgCount NUMBER;
973 lvCAutoMatch Varchar2(1) := 'N';
974 --
975 BEGIN
976 --
977 fnd_msg_pub.initialize;
978 --
979 -- Get Auto match indicator
980 --
981 OPEN auto_match_cur;
982 FETCH auto_match_cur INTO lvCAutoMatch;
983 CLOSE auto_match_cur;
984 --
985 IF (lvCAutoMatch = 'Y' AND
986 p_start_date IS NULL) THEN
987 ldRelStartDate := SYSDATE;
988 ELSE
989 ldRelStartDate := p_start_date;
990 END IF;
991 --
992 -- Get the Total Number of Students who need to be assigned to the Advisor
993 --
994 OPEN cur_find_count;
995 FETCH cur_find_count INTO numberOfStudentsToProcess;
996 CLOSE cur_find_count;
997 --
998 -- Get the Sum of Maximum Load of the Advisors in the group
999 --
1000 OPEN cur_max_stu_num;
1001 FETCH cur_max_stu_num INTO totalMaximumLoad;
1002 CLOSE cur_max_stu_num;
1003 --
1004 -- Get the Sum of Actual Loads of the Advisors in the group
1005 --
1006 OPEN cur_tot_act_load;
1007 FETCH cur_tot_act_load INTO totalActualLoad;
1008 CLOSE cur_tot_act_load;
1009 --
1010 -- Calculate the Desired Load Percentage
1011 --
1012 desiredLoadPercentage := (numberOfStudentsToProcess +
1013 NVL (totalActualLoad, 0)) / NVL (totalMaximumLoad, 1);
1014 --
1015 -- Assign Advisors to the Students
1016 --
1017 lnProcessed := 0;
1018 FOR rec_students_to_match IN cur_students_to_match LOOP
1019 --
1020 OPEN cur_advisors_to_load;
1021 FETCH cur_advisors_to_load INTO rec_advisors_to_load;
1022 IF (cur_advisors_to_load%FOUND) THEN
1023 IF ((NVL (rec_advisors_to_load.actual_load, 0) /
1024 NVL (rec_advisors_to_load.maximum_load, 1)) < desiredLoadPercentage) AND
1025 (rec_advisors_to_load.maximum_load > rec_advisors_to_load.actual_load) THEN
1026 --
1027 -- Determine the end date of relationship.
1028 -- If student or advisor is end dated then the end date will be the earilest of the two
1029 -- else the end date of relationship will be null;
1030 --
1031 IF (rec_students_to_match.end_date IS NULL AND
1032 rec_advisors_to_load.end_date IS NULL) THEN
1033 ldRelEndDate := NULL;
1034 ELSE
1035 IF (rec_students_to_match.end_date >= rec_advisors_to_load.end_date) THEN
1036 ldRelEndDate := rec_advisors_to_load.end_date;
1037 ELSE
1038 ldRelEndDate := rec_students_to_match.end_date;
1039 END IF;
1040 END IF;
1041 --
1042 -- increment the count of statistics by 1
1043 --
1044 lnProcessed := lnProcessed +1;
1045 --
1046 -- Add Student to the Relationship table with the current Advisor
1047 --
1048 igs_az_advising_rels_pkg.insert_row (
1049 x_rowid => lv_rowid,
1050 x_group_advising_rel_id => l_group_advising_rel_id,
1051 x_group_name => p_group_name,
1052 x_group_advisor_id => rec_advisors_to_load.group_advisor_id,
1053 x_group_student_id => rec_students_to_match.group_student_id,
1054 x_start_date => ldRelStartDate,
1055 x_end_date => ldRelEndDate,
1056 x_return_status => lvcReturnStatus,
1057 x_msg_data => lvcMsgData,
1058 x_msg_count => lnMsgCount
1059 );
1060 --
1061 -- To do See if we have to add ret status, msg_count ad msg_data to th eparameter list
1062 --
1063 END IF;
1064 END IF;
1065 CLOSE cur_advisors_to_load;
1066 END LOOP;
1067 --
1068 p_n_processed := lnProcessed;
1069 --
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1073 fnd_message.set_token ('NAME', 'ASSIGN_STUDENTS_TO_ADVISORS : ' || SUBSTR (SQLERRM, 80));
1074 fnd_msg_pub.add;
1075 RETURN;
1076 END assign_students_to_advisors;
1077 --
1078 --
1079 -- swaghmar 06-Jun-2006 Bug# 5283309, Added new message IGS_AZ_STU_LIST_HOLD_APPLIED
1080 -- instead of IGS_AZ_STU_LIST_ADD
1081 --
1082 PROCEDURE apply_hold (
1083 errbuf OUT NOCOPY VARCHAR2,
1084 retcode OUT NOCOPY VARCHAR2,
1085 p_group_name IN VARCHAR2 DEFAULT NULL,
1086 p_notify IN VARCHAR2 DEFAULT 'Y'
1087 ) IS
1088 --
1089 -- Cursor to get the hold Type defined for the group
1090 --
1091 CURSOR cur_hold_Type IS
1092 SELECT advising_hold_type
1093 FROM igs_az_groups
1094 WHERE group_name = p_group_name;
1095 --
1096 -- Cursor to get all the default hold effect associated with the Hold Type
1097 --
1098 CURSOR cur_hold_effect (cp_hold_type VARCHAR2) IS
1099 SELECT s_encmb_effect_type
1100 FROM igs_fi_enc_dflt_eft
1101 WHERE encumbrance_type = cp_hold_type;
1102 --
1103 -- Cursor to select all the students who are to applyed the hold. This will contain all the students who are in the group and have
1104 -- not been applied with any hold as part of this group
1105 --
1106 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
1107 --
1108 CURSOR cur_appl_hld_std (cp_group_name VARCHAR2, cp_hold_type VARCHAR2) IS
1109 SELECT azs.ROWID row_id,
1110 azs.*,
1111 p.party_number,
1112 p.party_name
1113 FROM igs_az_students azs ,
1114 hz_parties p
1115 WHERE azs.group_name = cp_group_name
1116 AND azs.advising_hold_type IS NULL
1117 AND azs.hold_start_date IS NULL
1118 AND azs.start_date IS NOT NULL
1119 AND TRUNC (azs.start_date) <= TRUNC (SYSDATE)
1120 AND NVL (azs.end_date, TRUNC (SYSDATE+1)) > TRUNC (SYSDATE)
1121 AND p.party_id = azs.student_person_id;
1122 --
1123 -- Cursor to generate new sequence number
1124 --
1125 CURSOR cur_seq_num IS
1126 SELECT igs_pe_persenc_effct_seq_num_s.NEXTVAL
1127 FROM dual;
1128 --
1129 -- Cursor to check if the Hold already existes for the Student
1130 --
1131 CURSOR cur_stu_encumb (
1132 cp_encumbrance_type igs_pe_pers_encumb.encumbrance_type%TYPE,
1133 cp_person_id hz_parties.party_id%TYPE) IS
1134 SELECT encumbrance_type,
1135 start_dt
1136 FROM igs_pe_pers_encumb
1137 WHERE encumbrance_type = cp_encumbrance_type
1138 AND person_id = cp_person_id
1139 AND TRUNC (start_dt) <= TRUNC (SYSDATE)
1140 AND NVL (expiry_dt, SYSDATE) >= SYSDATE;
1141 --
1142 --
1143 --
1144 CURSOR cur_az_holds_upd (
1145 cp_student_person_id igs_az_students.student_person_id%TYPE,
1146 cp_group_name igs_az_students.group_name%TYPE) IS
1147 SELECT start_date
1148 FROM igs_az_students
1149 WHERE student_person_id = cp_student_person_id
1150 AND group_name = cp_group_name;
1151 --
1152 -- Local Variables here
1153 --
1154 lvcHoldType VARCHAR2(30);
1155 lvHoldRowID VARCHAR2(25);
1156 lvHoldEfctRowID VARCHAR2(25);
1157 lvEncefctRowID VARCHAR2(25);
1158 ldHldStrtDt DATE := TRUNC (SYSDATE);
1159 ldHldEfctStrtDt DATE := TRUNC (SYSDATE);
1160 lnpeeseqnum NUMBER;
1161 lvReturnStatus VARCHAR2(1);
1162 lvMsgData VARCHAR2(100);
1163 lnMsgCount NUMBER;
1164 lvcHoldPersonIds VARCHAR2(32767); -- Variable to hold the comma separated person IDs of the students who are going to be applied advising hold. Will be used in sending the notification.
1165 lvcHoldMsgSubject fnd_new_messages.message_text%TYPE;
1166 lvcHoldMsgText fnd_new_messages.message_text%TYPE;
1167 lvnHoldExixts NUMBER;
1168 lvEncumbranceType igs_pe_pers_encumb.encumbrance_type%TYPE;
1169 lvEncmbTypeStartDt igs_pe_pers_encumb.start_dt%TYPE;
1170 l_cur_az_holds_upd cur_az_holds_upd%ROWTYPE;
1171 --
1172 BEGIN
1173 --
1174 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
1175
1176 fnd_msg_pub.initialize;
1177 --
1178 OPEN cur_hold_Type;
1179 FETCH cur_hold_Type INTO lvcHoldType;
1180 CLOSE cur_hold_Type;
1181 --
1182 -- If Hold Type is not defined do nothing and return success
1183 --
1184 IF (lvcHoldType IS NULL) THEN
1185 errbuf := NULL;
1186 retcode := 0;
1187 RETURN;
1188 END IF;
1189 --
1190 -- If there is some hold type associated with the group then
1191 --
1192 -- Loop through all the students who need to be applied Hold
1193 -- initialize the personIds to null;
1194 --
1195 lvcHoldPersonIds := NULL;
1196 --
1197 -- Set the token and get the message which will be used as subject for the hold notification.
1198 --
1199 fnd_message.set_name ('IGS', 'IGS_AZ_HOLD_NOTIF_SUBJECT'); -- Bug# 5283309
1200 fnd_message.set_token ('GROUP_NAME', p_group_name);
1201 lvcHoldMsgSubject := fnd_message.get;
1202 --
1203 -- Clear the message buffer now
1204 --
1205 fnd_msg_pub.initialize;
1206 --
1207 FOR std_rec IN cur_appl_hld_std (p_group_name, lvcHoldType) LOOP
1208 --
1209 IF (cur_appl_hld_std%ROWCOUNT = 1) THEN
1210 -- Put the entry in the log file for the students who are being put on advising hold...
1211 fnd_message.set_name ('IGS', 'IGS_AZ_STU_LIST_HOLD_APPLIED');
1212 fnd_file.put_line (fnd_file.log, fnd_message.get);
1213 END IF;
1214 --
1215 -- 1. Create a record in table IGS_PE_PERS_ENCUMB
1216 --
1217 -- Concatenate the person Ids for sending the notification:
1218 --
1219 lvcHoldPersonIds := lvcHoldPersonIds || ',' || std_rec.student_person_id; -- To do See here I am using student_person_id inplace of group_student_ID(seq gen PK) as mentioned in FD.
1220 ldHldStrtDt := TRUNC(SYSDATE);
1221 --
1222 lvEncumbranceType := NULL;
1223 lvEncmbTypeStartDt := NULL;
1224 OPEN cur_stu_encumb (lvcHoldType, std_rec.student_person_id);
1225 FETCH cur_stu_encumb INTO lvEncumbranceType, lvEncmbTypeStartDt;
1226 CLOSE cur_stu_encumb;
1227 --
1228 IF (lvEncumbranceType IS NULL) THEN
1229 --
1230 igs_pe_pers_encumb_pkg.insert_row (
1231 x_rowid => lvHoldRowID,
1232 x_person_id => std_rec.student_person_id,
1233 x_encumbrance_type => lvcHoldType,
1234 x_start_dt => ldHldStrtDt,
1235 x_expiry_dt => NULL,
1236 x_authorising_person_id => NULL, -- To do .. Look how this can be populated
1237 x_comments => NULL, -- See if we can use some message here.
1238 x_spo_course_cd => NULL,
1239 x_spo_sequence_number => NULL,
1240 x_cal_type => NULL,
1241 x_sequence_number => NULL,
1242 x_auth_resp_id => NULL,
1243 x_external_reference => NULL
1244 );
1245 --
1246 -- 2. Loop through all the Default hold effects for the hold type and Create a record in Table IGS_PE_PERSENC_EFFCT
1247 --
1248 FOR HldEfct_rec IN cur_hold_effect (lvcHoldType) LOOP
1249 --
1250 -- Get the sequnce number from the sequence
1251 --
1252 OPEN cur_seq_num;
1253 FETCH cur_seq_num INTO lnpeeseqnum;
1254 CLOSE cur_seq_num;
1255 --
1256 igs_pe_persenc_effct_pkg.insert_row (
1257 x_rowid => lvHoldEfctRowID,
1258 x_person_id => std_rec.student_person_id,
1259 x_encumbrance_type => lvcHoldType,
1260 x_pen_start_dt => ldHldStrtDt,
1261 x_s_encmb_effect_type => HldEfct_rec.s_encmb_effect_type,
1262 x_pee_start_dt => ldHldEfctStrtDt,
1263 x_sequence_number => lnpeeseqnum,
1264 x_expiry_dt => NULL,
1265 x_course_cd => NULL,
1266 x_restricted_enrolment_cp => NULL,
1267 x_restricted_attendance_type => NULL
1268 );
1269 --
1270 END LOOP; --HldEfct_rec
1271 ELSE
1272 --
1273 OPEN cur_az_holds_upd(std_rec.student_person_id,std_rec.group_name);
1274 FETCH cur_az_holds_upd INTO l_cur_az_holds_upd;
1275 CLOSE cur_az_holds_upd;
1276 --
1277 ldHldStrtDt := lvEncmbTypeStartDt;
1278 --
1279 END IF;
1280 --
1281 -- Now update the advising student table with the HOLD_APPLIED and start Date.
1282 --
1283 igs_az_students_pkg.update_row (
1284 x_rowid => std_rec.row_id,
1285 x_group_student_id => std_rec.group_student_id,
1286 x_group_name => std_rec.group_name,
1287 x_student_person_id => std_rec.student_person_id,
1288 x_start_date => std_rec.start_date,
1289 x_end_date => std_rec.end_date,
1290 x_advising_hold_type => lvcHoldType,
1291 x_hold_start_date => ldHldStrtDt,
1292 x_notified_date => std_rec.notified_date,
1293 x_accept_add_flag => std_rec.accept_add_flag,
1294 x_accept_delete_flag => std_rec.accept_delete_flag ,
1295 x_return_status => lvReturnStatus,
1296 x_msg_data => lvMsgData,
1297 x_msg_count => lnMsgCount
1298 );
1299 --
1300 -- Put in the log the info about student being put on hold..
1301 --
1302 fnd_file.put_line (fnd_file.log, ' ' || std_rec.party_number || ' - ' || std_rec.party_name) ;
1303 --
1304 END LOOP; --std_rec
1305 --
1306 -- Get the first comma from the comma separated list of person IDs.
1307 --
1308 lvcHoldPersonIds := SUBSTR (lvcHoldPersonIds, INSTR (lvcHoldPersonIds, ',' ) + 1);
1309 --
1310 -- Now send the notification to all the students who have been applied with advising hold..
1311 --
1312 IF (NVL (LENGTH (lvcHoldPersonIds), 0) > 0) THEN
1313 --
1314 -- Get the message text to be sent to the student for being added to the group.
1315 --
1316 fnd_message.set_name ('IGS', 'IGS_AZ_HOLD_NOTIF_TEXT');
1317 fnd_message.set_token ('GROUP_NAME', p_group_name);
1318 fnd_message.set_token ('START_DATE', TO_CHAR (SYSDATE, 'DD-MON-RRRR'));
1319 --
1320 lvcHoldMsgText := fnd_message.get;
1321 --
1322 -- Once u get the message text in a local variable initialize the message stack
1323 --
1324 fnd_msg_pub.initialize;
1325
1326
1327 IF (p_notify = 'Y') THEN
1328
1329 notify_person (
1330 p_busevent => 'oracle.apps.igs.az.ntfyhold', -- to do --Verify this with final case and seed.
1331 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1332 p_param_value1 => lvcHoldPersonIds,
1333 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1334 p_param_value2 => lvcHoldMsgSubject,
1335 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1336 p_param_value3 => lvcHoldMsgText
1337 );
1338
1339 END IF;
1340
1341 END IF;
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 retcode := 2;
1345 errbuf := fnd_msg_pub.get (fnd_msg_pub.g_last, fnd_api.g_false);
1346 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1347 fnd_message.set_token ('NAME', 'IGS_AZ_GEN_001.Apply_hold : ' || SUBSTR (SQLERRM, 80));
1348 fnd_msg_pub.add;
1349 fnd_msg_pub.count_and_get (
1350 p_encoded => fnd_api.g_false,
1351 p_count => lnMsgCount,
1352 p_data => lvMsgData
1353 );
1354 RETURN;
1355 END apply_hold;
1356 --
1357 --
1358 --
1359 PROCEDURE send_notification(
1360 errbuf OUT NOCOPY VARCHAR2,
1361 retcode OUT NOCOPY VARCHAR2,
1362 p_group_name IN VARCHAR2 DEFAULT NULL) IS
1363 --
1364 -- 1. For students
1365 -- a. Notify the student who have been added to the group and not yet sent the notification
1366 -- b. Notify the students who have been removed from the group and not yet sent the notification.
1367 -- a.-- ---------
1368 --
1369 -- Cursor to get all the newly added students.
1370 --
1371 CURSOR cur_std_add IS
1372 SELECT azs.ROWID AS row_id,
1373 azs.*,
1374 p.party_number,
1375 p.party_name
1376 FROM igs_az_students azs, hz_parties p
1377 WHERE azs.group_name = p_group_name
1378 AND azs.accept_add_flag = 'Y'
1379 AND azs.start_date IS NOT NULL
1380 AND azs.end_date IS NULL
1381 AND azs.notified_date IS NULL
1382 AND p.party_id = azs.student_person_id;
1383 --
1384 -- Cursor to get all the students who are removed from the group.
1385 --
1386 CURSOR cur_std_del IS
1387 SELECT azs.ROWID AS row_id,
1388 azs.*,
1389 p.party_number,
1390 p.party_name
1391 FROM igs_az_students azs, hz_parties p
1392 WHERE azs.group_name = p_group_name
1393 AND azs.accept_add_flag = 'Y'
1394 AND azs.notified_date IS NULL
1395 AND azs.start_date IS NOT NULL
1396 AND azs.end_date IS NOT NULL
1397 AND p.party_id = azs.student_person_id;
1398 --
1399 -- Cursor to get all the newly added advisors.
1400 --
1401 CURSOR cur_adv_add IS
1402 SELECT aza.ROWID AS row_id,
1403 aza.*,
1404 p.party_number,
1405 p.party_name
1406 FROM igs_az_advisors aza, igs_az_groups azg, hz_parties p
1407 WHERE aza.group_name = p_group_name
1408 AND aza.accept_add_flag = 'Y'
1409 AND aza.start_date IS NOT NULL
1410 AND aza.end_date IS NULL
1411 AND aza.notified_date IS NULL
1412 AND azg.group_name = aza.group_name
1413 AND azg.delivery_method_code <>
1414 'SELF' -- To do look for exact lookup code
1415 AND p.party_id = aza.advisor_person_id;
1416 --
1417 -- Cursor to get all the advisors who are removed from the group.
1418 --
1419 CURSOR cur_adv_del IS
1420 SELECT aza.ROWID AS row_id,
1421 aza.*,
1422 p.party_number,
1423 p.party_name
1424 FROM igs_az_advisors aza, igs_az_groups azg, hz_parties p
1425 WHERE aza.group_name = p_group_name
1426 AND aza.accept_add_flag = 'Y'
1427 AND aza.start_date IS NOT NULL
1428 AND aza.notified_date IS NULL
1429 AND aza.end_date IS NOT NULL
1430 AND azg.group_name = aza.group_name
1431 AND azg.delivery_method_code <>
1432 'SELF' -- To do look for exact lookup code
1433 AND p.party_id = aza.advisor_person_id;
1434 --
1435 -- Declare Local varaibles to be used ..
1436 --
1437 lvcmsgsubject VARCHAR2(2000);
1438 lvcmsgtext VARCHAR2(20000);
1439 lvcpersonids VARCHAR2(32767); -- Variable to store the comma separated Person IDs. Which will be passed for notificataion.
1440 --
1441 -- The workflow in turn will call the procedure igs_as_notify_student.wf_set_role to add these students to
1442 -- the role to which the notification will be sent.
1443 --
1444 lvReturnStatus VARCHAR2(10);
1445 lvMsgData VARCHAR2(2000);
1446 lnMsgCount NUMBER;
1447 lncount NUMBER := 0;
1448 --
1449 BEGIN
1450 --
1451 -- Get the Default message subject. This can be got from FND_NEW_MESSAGES table. PLacing here before any loop because subject remains same for
1452 -- all kinds of advising notification. (Message IGS_AZ_NOTIF_SUBJECT Token Group name ).
1453 --
1454 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
1455
1456 fnd_message.set_name('IGS', 'IGS_AZ_NOTIF_SUBJECT');
1457 fnd_message.set_token('GROUP_NAME', p_group_name);
1458 lvcmsgsubject := fnd_message.get;
1459 --
1460 -- Once u get the message text in a local variable initialize the message stack
1461 --
1462 fnd_msg_pub.initialize;
1463 --
1464 -- Now u start .. 1. For newly added students:
1465 -- Get the message text to be sent to the student for being added to the group.
1466 --
1467 fnd_message.set_name('IGS', 'IGS_AZ_NOTIF_TEXT');
1468 fnd_message.set_token('GROUP_NAME', p_group_name);
1469 fnd_message.set_token('ADDED_REMOVED ', ' assigned to');
1470 fnd_message.set_token('NOTIF_DATE', TO_CHAR(SYSDATE, 'DD-MON-YY'));
1471 lvcmsgtext := fnd_message.get;
1472 --
1473 -- Once u get the message text in a local variable initialize the message stack
1474 --
1475 fnd_msg_pub.initialize;
1476 --
1477 FOR add_std_rec IN cur_std_add LOOP
1478 IF (cur_std_add%ROWCOUNT = 1) THEN
1479 --
1480 -- Log that u are going to start the notification for the students being added
1481 --
1482 fnd_message.set_name('IGS', 'IGS_AZ_STU_LIST_ADD');
1483 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
1484 END IF;
1485 --
1486 -- Now since the student is slated to be notified, updated the IGS_AZ_STUDENTS table with NOTIFIED_DATE = sysdate.
1487 --
1488 igs_az_students_pkg.update_row(
1489 x_rowid => add_std_rec.row_id,
1490 x_group_student_id => add_std_rec.group_student_id,
1491 x_group_name => add_std_rec.group_name,
1492 x_student_person_id => add_std_rec.student_person_id,
1493 x_start_date => add_std_rec.start_date,
1494 x_end_date => add_std_rec.end_date,
1495 x_advising_hold_type => add_std_rec.advising_hold_type,
1496 x_hold_start_date => add_std_rec.hold_start_date,
1497 x_notified_date => SYSDATE,
1498 x_accept_add_flag => add_std_rec.accept_add_flag,
1499 x_accept_delete_flag => add_std_rec.accept_delete_flag,
1500 x_return_status => lvReturnStatus,
1501 x_msg_data => lvMsgData,
1502 x_msg_count => lnMsgCount
1503 );
1504 --
1505 -- Log that the selected student is notified.
1506 --
1507 fnd_file.put_line(
1508 fnd_file.LOG,
1509 ' - ' || add_std_rec.party_number || ' - ' || add_std_rec.party_name);
1510 lvcpersonids := lvcpersonids || ',' || add_std_rec.student_person_id;
1511 lncount := lncount + 1;
1512 --
1513 -- The event oracle.apps.igs.az.ntfystud would be raised for every hundred students
1514 -- This is to avoid buffer overflow.
1515 -- Please don't change the code.
1516 --
1517 IF (MOD(lncount, 100) = 0) THEN
1518 lvcpersonids := SUBSTR(lvcpersonids, INSTR(lvcpersonids, ',') + 1);
1519 notify_person(
1520 p_busevent => 'oracle.apps.igs.az.ntfystud', -- to do --Verify this with final case and seed.
1521 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1522 p_param_value1 => lvcpersonids,
1523 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1524 p_param_value2 => lvcmsgsubject,
1525 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1526 p_param_value3 => lvcmsgtext
1527 );
1528 lvcpersonids := NULL;
1529 END IF;
1530 END LOOP;
1531 --
1532 -- Now we have all the student Ids.. concatenated. Lets strip the first comma and then send the notification.
1533 --
1534 lvcpersonids := SUBSTR(lvcpersonids, INSTR(lvcpersonids, ',') + 1);
1535 --
1536 -- The event oracle.apps.igs.az.ntfystud would be raised for every hundred students
1537 -- This is to avoid buffer overflow.
1538 -- In the code given above the event would be raised for every 100 person, if the number of students are like 231, 202
1539 -- then the notification would not go to 31 , 2 students resp. So the code below is requied to send the notifications all the students.
1540 --
1541 IF NVL(LENGTH(lvcpersonids), 0) > 0 THEN
1542 notify_person(
1543 p_busevent => 'oracle.apps.igs.az.ntfystud', -- to do --Verify this with final case and seed.
1544 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1545 p_param_value1 => lvcpersonids,
1546 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1547 p_param_value2 => lvcmsgsubject,
1548 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1549 p_param_value3 => lvcmsgtext
1550 );
1551 END IF; -- Some student selected for group add notification.
1552 --
1553 -- Agian initialize the personIdlist before trying to send next notification.
1554 --
1555 lvcpersonids := NULL;
1556 lncount := 0;
1557 lvReturnStatus := NULL;
1558 lvMsgData := NULL;
1559 lnMsgCount := NULL;
1560 --
1561 -- Send notification to the student who are removed from the group.
1562 -- Log that u are going to start the notification for the students being rmoved from the group
1563 --
1564 fnd_message.set_name('IGS', 'IGS_AZ_STU_LIST_DEL');
1565 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
1566 --
1567 -- Get the message text to be sent to the student for being added to the group.
1568 --
1569 fnd_message.set_name('IGS', 'IGS_AZ_NOTIF_TEXT');
1570 fnd_message.set_token('GROUP_NAME', p_group_name);
1571 fnd_message.set_token('ADDED_REMOVED ', ' removed from');
1572 fnd_message.set_token('NOTIF_DATE', TO_CHAR(SYSDATE, 'DD-MON-YY'));
1573 lvcmsgtext := fnd_message.get;
1574 --
1575 -- Once u get the message text in a local variable initialize the message stack
1576 --
1577 fnd_msg_pub.initialize;
1578 --
1579 FOR del_std_rec IN cur_std_del LOOP
1580 --
1581 -- Now since the student is slated to be notified. updated the IGS_AZ_STUDENTS table with NOTIFIED_DATE = sysdate.
1582 --
1583 igs_az_students_pkg.update_row(
1584 x_rowid => del_std_rec.row_id,
1585 x_group_student_id => del_std_rec.group_student_id,
1586 x_group_name => del_std_rec.group_name,
1587 x_student_person_id => del_std_rec.student_person_id,
1588 x_start_date => del_std_rec.start_date,
1589 x_end_date => del_std_rec.end_date,
1590 x_advising_hold_type => del_std_rec.advising_hold_type,
1591 x_hold_start_date => del_std_rec.hold_start_date,
1592 x_notified_date => SYSDATE,
1593 x_accept_add_flag => del_std_rec.accept_add_flag,
1594 x_accept_delete_flag => del_std_rec.accept_delete_flag,
1595 x_return_status => lvReturnStatus,
1596 x_msg_data => lvMsgData,
1597 x_msg_count => lnMsgCount
1598 );
1599 --
1600 -- Log that the selected student is removed from the group.
1601 --
1602 fnd_file.put_line(
1603 fnd_file.LOG,
1604 ' - ' || del_std_rec.party_number || ' - ' || del_std_rec.party_name);
1605 --
1606 lncount := lncount + 1;
1607 lvcpersonids := lvcpersonids || ',' || del_std_rec.student_person_id;
1608 --
1609 -- The event oracle.apps.igs.az.ntfystud would be raised for every hundred students
1610 -- This is to avoid buffer overflow.
1611 -- Please don't change the code.
1612 --
1613 IF (MOD(lncount, 100) = 0) THEN
1614 lvcpersonids := SUBSTR(lvcpersonids, INSTR(lvcpersonids, ',') + 1);
1615 notify_person(
1616 p_busevent => 'oracle.apps.igs.az.ntfystud', -- to do --Verify this with final case and seed.
1617 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1618 p_param_value1 => lvcpersonids,
1619 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1620 p_param_value2 => lvcmsgsubject,
1621 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1622 p_param_value3 => lvcmsgtext
1623 );
1624 lvcpersonids := NULL;
1625 END IF;
1626 END LOOP;
1627 --
1628 -- Now we have all the student Ids.. concatenated. Lets strip the first comma and then send the notification.
1629 --
1630 lvcpersonids := SUBSTR(lvcpersonids, INSTR(lvcpersonids, ',') + 1);
1631 --
1632 -- The event oracle.apps.igs.az.ntfystud would be raised for every hundred students
1633 -- This is to avoid buffer overflow.
1634 -- In the code given above the event would be raised for every 100 person, if the number of students are like 231, 202
1635 -- then the notification would not go to 31 , 2 students resp. So the code below is requied to send the notifications all the students.
1636 --
1637 IF NVL(LENGTH(lvcpersonids), 0) > 0 THEN
1638 --
1639 notify_person(
1640 p_busevent => 'oracle.apps.igs.az.ntfystud', -- to do --Verify this with final case and seed.
1641 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1642 p_param_value1 => lvcpersonids,
1643 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1644 p_param_value2 => lvcmsgsubject,
1645 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1646 p_param_value3 => lvcmsgtext
1647 );
1648 --
1649 END IF; -- Some student selected for group add notification.
1650 --
1651 -- Agian initialize the personIdlist before trying to send next notification.
1652 --
1653 lvcpersonids := NULL;
1654 --
1655 -- Notify the advisors now:
1656 --
1657 -- Log that u are going to start the notification for the advisors being assigned the group
1658 --
1659 fnd_message.set_name('IGS', 'IGS_AZ_STU_LIST_NOTIFY_ADD');
1660 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
1661 --
1662 FOR add_adv_rec IN cur_adv_add LOOP
1663 --
1664 lvcpersonids := lvcpersonids || ',' || add_adv_rec.advisor_person_id; -- To do See here I am using student_person_id inplace of Froup_student_ID(seq gen PK) as mentioned in FD.
1665 --
1666 -- Now since the advisor is slated to be notified for being added to the group , updated the IGS_AZ_STUDENTS table with NOTIFIED_DATE = sysdate.
1667 --
1668 igs_az_advisors_pkg.update_row(
1669 x_rowid => add_adv_rec.row_id,
1670 x_group_advisor_id => add_adv_rec.group_advisor_id,
1671 x_group_name => add_adv_rec.group_name,
1672 x_advisor_person_id => add_adv_rec.advisor_person_id,
1673 x_start_date => add_adv_rec.start_date,
1674 x_end_date => add_adv_rec.end_date,
1675 x_max_students_num => add_adv_rec.max_students_num,
1676 x_notified_date => SYSDATE, -- This is the only change
1677 x_accept_add_flag => add_adv_rec.accept_add_flag,
1678 x_accept_delete_flag => add_adv_rec.accept_delete_flag, ---To do Follwing three parameters need to be added in the TBH and then uncomment
1679 x_return_status => lvReturnStatus,
1680 x_msg_data => lvMsgData,
1681 x_msg_count => lnMsgCount
1682 );
1683 --
1684 -- Log that the selected advisor is assigned to the group.
1685 --
1686 fnd_file.put_line(
1687 fnd_file.LOG,
1688 ' ' || add_adv_rec.party_number || ' - ' || add_adv_rec.party_name);
1689 END LOOP;
1690 --
1691 -- Now we have all the student Ids.. concatenated. Lets strip the first comma and then send the notification.
1692 --
1693 lvcpersonids := SUBSTR(lvcpersonids, INSTR(lvcpersonids, ',') + 1);
1694 --
1695 -- See if there were any advisor selected in this category.
1696 --
1697 IF NVL(LENGTH(lvcpersonids), 0) > 0 THEN
1698 --
1699 -- Get the message text to be sent to the student for being added to the group.
1700 --
1701 fnd_message.set_name('IGS', 'IGS_AZ_NOTIF_TEXT');
1702 fnd_message.set_token('GROUP_NAME', p_group_name);
1703 fnd_message.set_token('ADDED_REMOVED ', ' assigned to');
1704 fnd_message.set_token('NOTIF_DATE', TO_CHAR(SYSDATE, 'DD-MON-YY'));
1705 lvcmsgtext := fnd_message.get;
1706 --
1707 -- Once u get the message text in a local variable initialize the message stack
1708 --
1709 fnd_msg_pub.initialize;
1710 --
1711 notify_person(
1712 p_busevent => 'oracle.apps.igs.az.ntfyadvr', -- to do --Verify this with final case and seed.
1713 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1714 p_param_value1 => lvcpersonids,
1715 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1716 p_param_value2 => lvcmsgsubject,
1717 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1718 p_param_value3 => lvcmsgtext
1719 );
1720 END IF; -- Some advisor selected for group add notification.
1721 --
1722 -- Agian initialize the personIdlist before trying to send next notification.
1723 --
1724 lvcpersonids := NULL;
1725 --
1726 -- Send notification to the advisor who are removed from the group.
1727 -- Log that u are going to start the notification for the advisors being rmoved from the group
1728 --
1729 fnd_message.set_name('IGS', 'IGS_AZ_STU_LIST_NOTIFY_AD_REM');
1730 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
1731 --
1732 FOR del_adv_rec IN cur_adv_del LOOP
1733 --
1734 -- set the staus, message count etc. back to null so that it is used in the next call
1735 --
1736 lvReturnStatus := NULL;
1737 lvMsgData := NULL;
1738 lnMsgCount := NULL;
1739 lvcpersonids := lvcpersonids || ',' || del_adv_rec.advisor_person_id; -- To do See here I am using advisor_person_id inplace of Group_advisor_ID(seq gen PK) as mentioned in FD.
1740 --
1741 -- Now since the advisor is slated to be notified about removal from group, updated the IGS_AZ_STUDENTS table with NOTIFIED_DATE = sysdate.
1742 --
1743 igs_az_advisors_pkg.update_row(
1744 x_rowid => del_adv_rec.row_id,
1745 x_group_advisor_id => del_adv_rec.group_advisor_id,
1746 x_group_name => del_adv_rec.group_name,
1747 x_advisor_person_id => del_adv_rec.advisor_person_id,
1748 x_start_date => del_adv_rec.start_date,
1749 x_end_date => del_adv_rec.end_date,
1750 x_max_students_num => del_adv_rec.max_students_num,
1751 x_notified_date => SYSDATE, -- This is the only change
1752 x_accept_add_flag => del_adv_rec.accept_add_flag,
1753 x_accept_delete_flag => del_adv_rec.accept_delete_flag, ---To do Follwing three parameters need to be added in the TBH and then uncomment
1754 x_return_status => lvReturnStatus,
1755 x_msg_data => lvMsgData,
1756 x_msg_count => lnMsgCount
1757 );
1758 --
1759 -- Log that the selected advisor is removed from the group.
1760 --
1761 fnd_file.put_line(
1762 fnd_file.LOG,
1763 ' ' || del_adv_rec.party_number || ' - ' || del_adv_rec.party_name);
1764 END LOOP;
1765 --
1766 -- Now we have all the student Ids.. concatenated. Lets strip the first comma and then send the notification.
1767 --
1768 lvcpersonids := SUBSTR(lvcpersonids, INSTR(lvcpersonids, ',') + 1);
1769 --
1770 -- See if there were any student selected in this category.
1771 --
1772 IF NVL(LENGTH(lvcpersonids), 0) > 0 THEN
1773 --
1774 -- Get the message text to be sent to the student for being added to the group.
1775 --
1776 fnd_message.set_name('IGS', 'IGS_AZ_NOTIF_TEXT');
1777 fnd_message.set_token('GROUP_NAME', p_group_name);
1778 fnd_message.set_token('ADDED_REMOVED ', ' removed from');
1779 fnd_message.set_token('NOTIF_DATE', TO_CHAR(SYSDATE, 'DD-MON-YY'));
1780 lvcmsgtext := fnd_message.get;
1781 --
1782 -- Once u get the message text in a local variable initialize the message stack
1783 --
1784 fnd_msg_pub.initialize;
1785 --
1786 notify_person(
1787 p_busevent => 'oracle.apps.igs.az.ntfyadvr', -- to do --Verify this with final case and seed.
1788 p_param_name1 => 'IA_USERS', ---this must be defined in workflow as the parameter to this event
1789 p_param_value1 => lvcpersonids,
1790 p_param_name2 => 'IA_SUBJECT', ---this must be defined in workflow as the parameter to this event
1791 p_param_value2 => lvcmsgsubject,
1792 p_param_name3 => 'IA_MESSAGE', -- ---this must be defined in workflow as the parameter to this event
1793 p_param_value3 => lvcmsgtext
1794 );
1795 END IF; -- Some student selected for group add notification.
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 retcode := 2;
1799 errbuf := fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false);
1800 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1801 fnd_message.set_token(
1802 'NAME',
1803 'IGS_AZ_GEN_001.send_notification : ' || SUBSTR(SQLERRM, 80));
1804 fnd_msg_pub.ADD;
1805 fnd_msg_pub.count_and_get(
1806 p_encoded => fnd_api.g_false,
1807 p_count => lnMsgCount,
1808 p_data => lvMsgData);
1809 RAISE;
1810 END send_notification;
1811 --
1812 --
1813 --
1814 PROCEDURE notify_person(
1815 p_busevent IN VARCHAR2,
1816 p_param_name1 IN VARCHAR2 DEFAULT NULL,
1817 p_param_value1 IN VARCHAR2 DEFAULT NULL,
1818 p_param_name2 IN VARCHAR2 DEFAULT NULL,
1819 p_param_value2 IN VARCHAR2 DEFAULT NULL,
1820 p_param_name3 IN VARCHAR2 DEFAULT NULL,
1821 p_param_value3 IN VARCHAR2 DEFAULT NULL,
1822 p_param_name4 IN VARCHAR2 DEFAULT NULL,
1823 p_param_value4 IN VARCHAR2 DEFAULT NULL,
1824 p_param_name5 IN VARCHAR2 DEFAULT NULL,
1825 p_param_value5 IN VARCHAR2 DEFAULT NULL
1826 ) AS
1827 /******************************************************************
1828 Created By : Girish Jha
1829 Date Created By : 17-May-2003
1830 Purpose : This procedure will be used for raising business event. This procedure is made very generic.
1831 This will acceept business event name and five pair of name value pair of w/f parameters.
1832 The name of the parameters must be registered with the w/f.
1833 Change History
1834 Who When What
1835 ******************************************************************/
1836 l_event_t wf_event_t;
1837 l_parameter_list_t wf_parameter_list_t;
1838 l_itemkey VARCHAR2(100);
1839 ln_seq_val NUMBER;
1840 lvMsgData VARCHAR2(100);
1841 lnMsgCount NUMBER;
1842 --
1843 -- Gets a unique sequence number
1844 --
1845 CURSOR c_seq_num IS
1846 SELECT igs_as_wf_beas006_s.NEXTVAL
1847 FROM DUAL;
1848 --
1849 BEGIN
1850 --
1851 -- Get the sequence value
1852 --
1853 OPEN c_seq_num;
1854 FETCH c_seq_num INTO ln_seq_val;
1855 CLOSE c_seq_num;
1856 --
1857 -- initialize the wf_event_t object
1858 --
1859 wf_event_t.initialize(l_event_t);
1860 --
1861 -- Adding the parameters to the parameter list, only when param is not null
1862 --
1863 IF p_param_name1 IS NOT NULL THEN
1864 wf_event.addparametertolist(
1865 p_name => p_param_name1,
1866 p_value => p_param_value1,
1867 p_parameterlist => l_parameter_list_t
1868 );
1869 END IF;
1870 --
1871 IF p_param_name2 IS NOT NULL THEN
1872 wf_event.addparametertolist(
1873 p_name => p_param_name2,
1874 p_value => p_param_value2,
1875 p_parameterlist => l_parameter_list_t
1876 );
1877 END IF;
1878 --
1879 IF p_param_name3 IS NOT NULL THEN
1880 wf_event.addparametertolist(
1881 p_name => p_param_name3,
1882 p_value => p_param_value3,
1883 p_parameterlist => l_parameter_list_t
1884 );
1885 END IF;
1886 --
1887 IF p_param_name4 IS NOT NULL THEN
1888 wf_event.addparametertolist(
1889 p_name => p_param_name4,
1890 p_value => p_param_value4,
1891 p_parameterlist => l_parameter_list_t
1892 );
1893 END IF;
1894 --
1895 IF p_param_name5 IS NOT NULL THEN
1896 wf_event.addparametertolist(
1897 p_name => p_param_name5,
1898 p_value => p_param_value5,
1899 p_parameterlist => l_parameter_list_t
1900 );
1901 END IF;
1902 --
1903 -- Now the parameters are set, Raise the Event
1904 --
1905 wf_event.RAISE(
1906 p_event_name => p_busevent,
1907 p_event_key => 'IGSAZ001' || ln_seq_val,
1908 p_parameters => l_parameter_list_t
1909 );
1910 --
1911 -- Delete the Parameter list after the event is raised
1912 --
1913 l_parameter_list_t.DELETE;
1914 --
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1918 fnd_message.set_token ('NAME', 'IGS_AZ_GEN_001.notify_person : ' || SUBSTR(SQLERRM, 80));
1919 fnd_msg_pub.ADD;
1920 fnd_msg_pub.count_and_get (
1921 p_encoded => fnd_api.g_false,
1922 p_count => lnMsgCount,
1923 p_data => lvMsgData
1924 );
1925 RAISE;
1926 END notify_person;
1927 --
1928 --
1929 --
1930 PROCEDURE end_date_advisor (
1931 p_group_name VARCHAR2,
1932 p_advisor_person_id NUMBER,
1933 p_end_date DATE,
1934 p_calling_mod VARCHAR2,
1935 p_enforce VARCHAR2 DEFAULT NULL
1936 ) IS
1937 /******************************************************************
1938 Created By : Girish Jha
1939 Date Created By : 17-May-2003
1940 Purpose : The requirement for ending an advisor is not limited to ending
1941 the advisor but also the relationships that advisor has with the students in table IGS_AZ_ADVISING_RELS.
1942 Also the end dating an advisor can happen either from SS page fro a concurrent program. Its better to write a
1943 separet procedure to handle this.
1944
1945 Change History
1946 Who When What
1947 ******************************************************************/
1948 --
1949 --
1950 --
1951 CURSOR cur_adv_upd IS
1952 SELECT aza.ROWID row_id,
1953 aza.*
1954 FROM igs_az_advisors aza
1955 WHERE aza.group_name = p_group_name
1956 AND aza.advisor_person_id = p_advisor_person_id
1957 AND NVL (aza.accept_add_flag, 'Y') = 'Y';
1958 --
1959 -- Business requirement 1.5 of FD. Technical approach section Last but one bullet says that if the advisor/student
1960 -- has not bben accepted and no longer is part of the PIG then phycally delete the record.. Get that record corresponding to the
1961 -- Advisor person_id Passed.. To do .. Can this happen from SS Screen? Can I have a record with ACCEPT_ADD_FLAG = 'Y' and again make that to 'N'?
1962 --
1963 CURSOR cur_adv_del IS
1964 SELECT aza.ROWID row_id
1965 FROM igs_az_advisors aza
1966 WHERE aza.group_name = p_group_name
1967 AND aza.advisor_person_id = p_advisor_person_id
1968 AND aza.accept_add_flag = 'N';
1969 --
1970 -- Cursor to get all the active relationship of the advisor.
1971 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
1972 --
1973 CURSOR cur_reln IS
1974 SELECT azr.ROWID row_id,
1975 azr.*
1976 FROM igs_az_advising_rels azr, igs_az_advisors aza
1977 WHERE azr.group_name = p_group_name
1978 AND azr.group_name = aza.group_name
1979 AND azr.group_advisor_id = aza.group_advisor_id
1980 AND aza.advisor_person_id = p_advisor_person_id
1981 AND azr.start_date IS NOT NULL
1982 AND TRUNC (NVL (azr.end_date, SYSDATE + 1)) > TRUNC (SYSDATE);
1983 --
1984 --
1985 --
1986 CURSOR cur_reln_del IS
1987 SELECT azr.ROWID row_id
1988 FROM igs_az_advising_rels azr, igs_az_advisors aza
1989 WHERE azr.group_name = p_group_name
1990 AND azr.group_name = aza.group_name
1991 AND azr.group_advisor_id = aza.group_advisor_id
1992 AND aza.advisor_person_id = p_advisor_person_id
1993 AND azr.start_date IS NULL;
1994 --
1995 --
1996 --
1997 CURSOR cur_grp IS
1998 SELECT auto_advisor_remove_flag
1999 FROM igs_az_groups azg
2000 WHERE azg.group_name = p_group_name;
2001 --
2002 -- Local variables:
2003 --
2004 lddeladvdate DATE;
2005 lvautoadvremind VARCHAR2 (1);
2006 lvadvdelind VARCHAR2 (1);
2007 lvReturnStatus VARCHAR2 (10); -- To do look for the dat lengths
2008 lvMsgData VARCHAR2 (1000);
2009 lnMsgCount NUMBER;
2010 --
2011 BEGIN
2012 --
2013 IF p_calling_mod = 'C' THEN
2014 OPEN cur_grp;
2015 FETCH cur_grp INTO lvautoadvremind;
2016 CLOSE cur_grp;
2017 --
2018 IF lvautoadvremind = 'Y' THEN
2019 lddeladvdate := SYSDATE;
2020 lvadvdelind := 'Y';
2021 ELSE
2022 IF p_enforce = 'Y' THEN
2023 lddeladvdate := SYSDATE;
2024 lvadvdelind := 'Y';
2025 ELSE
2026 lddeladvdate := NULL;
2027 lvadvdelind := 'Y';
2028 END IF;
2029 END IF;
2030 ELSE
2031 lddeladvdate := p_end_date;
2032 lvadvdelind := 'Y';
2033 END IF;
2034 --
2035 -- End date the relationship..
2036 --
2037 FOR reln_rec IN cur_reln LOOP
2038 igs_az_advising_rels_pkg.update_row (
2039 x_rowid => reln_rec.row_id,
2040 x_group_advising_rel_id => reln_rec.group_advising_rel_id,
2041 x_group_name => reln_rec.group_name,
2042 x_group_advisor_id => reln_rec.group_advisor_id,
2043 x_group_student_id => reln_rec.group_student_id,
2044 x_start_date => reln_rec.start_date,
2045 x_end_date => lddeladvdate,
2046 x_return_status => lvReturnStatus,
2047 x_msg_data => lvMsgData,
2048 x_msg_count => lnMsgCount
2049 ); -- To do see if msg count etc. is require , if yes add and Error Handling
2050 END LOOP;
2051 --
2052 FOR del_reln IN cur_reln_del LOOP
2053 igs_az_advising_rels_pkg.delete_row (
2054 x_rowid => del_reln.row_id,
2055 x_return_status => lvReturnStatus,
2056 x_msg_data => lvMsgData,
2057 x_msg_count => lnMsgCount
2058 );
2059 END LOOP; -- DEL_RELN
2060 --
2061 -- Update the advising record
2062 --
2063 FOR adv_upd_rec IN cur_adv_upd LOOP
2064 IF p_calling_mod = 'C' THEN
2065 igs_az_advisors_pkg.update_row (
2066 x_rowid => adv_upd_rec.row_id,
2067 x_group_advisor_id => adv_upd_rec.group_advisor_id,
2068 x_group_name => adv_upd_rec.group_name,
2069 x_advisor_person_id => adv_upd_rec.advisor_person_id,
2070 x_start_date => adv_upd_rec.start_date,
2071 x_end_date => lddeladvdate, -- This is only changed..
2072 x_max_students_num => adv_upd_rec.max_students_num,
2073 x_notified_date => adv_upd_rec.notified_date, -- This is the only change
2074 x_accept_add_flag => adv_upd_rec.accept_add_flag,
2075 x_accept_delete_flag => lvadvdelind, ---To do Follwing three parameters need to be added in the TBH and then uncomment
2076 x_return_status => lvReturnStatus,
2077 x_msg_data => lvMsgData,
2078 x_msg_count => lnMsgCount
2079 ); -- To Do error handling..
2080 END IF;
2081 END LOOP;
2082 --
2083 -- See if the record has to be deleted
2084 --
2085 FOR adv_del_rec IN cur_adv_del LOOP
2086 igs_az_advisors_pkg.delete_row (
2087 adv_del_rec.row_id,
2088 lvReturnStatus,
2089 lvMsgData,
2090 lnMsgCount
2091 );
2092 END LOOP;
2093 EXCEPTION
2094 WHEN OTHERS THEN
2095 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2096 fnd_message.set_token ('NAME', 'IGS_AZ_GEN_001.Mainatin_group : ' || SUBSTR (SQLERRM, 80));
2097 fnd_msg_pub.ADD;
2098 fnd_msg_pub.count_and_get (
2099 p_encoded => fnd_api.g_false,
2100 p_count => lnMsgCount,
2101 p_data => lvMsgData
2102 );
2103 RETURN;
2104 END end_date_advisor;
2105 --
2106 --
2107 --
2108 PROCEDURE end_date_student (
2109 p_group_name VARCHAR2,
2110 p_student_person_id NUMBER,
2111 p_end_date DATE,
2112 p_calling_mod VARCHAR2 DEFAULT 'C',
2113 p_enforce VARCHAR2 DEFAULT NULL
2114 ) IS
2115 /******************************************************************
2116 Created By : Girish Jha
2117 Date Created By : 17-May-2003
2118 Purpose : The requirement for ending an student is not limited to end dating
2119 the student but also the relationships that student has in table IGS_AZ_ADVISING_RELS.
2120 Also the end dating an student can happen either from SS page fro a concurrent program. Its better to write a
2121 separet procedure to handle this.
2122
2123
2124 Change History
2125 Who When What
2126 ******************************************************************/
2127 --
2128 -- Decalre
2129 --
2130 CURSOR cur_std_upd IS
2131 SELECT azs.ROWID row_id,
2132 azs.*
2133 FROM igs_az_students azs
2134 WHERE azs.group_name = p_group_name
2135 AND azs.student_person_id = p_student_person_id
2136 AND NVL (azs.accept_add_flag, 'Y') = 'Y';
2137 --
2138 -- Business requirement 1.5 of FD. Technical approach section Last but one bullet says that if the advisor/student
2139 -- has not bben accepted and no longer is part of the PIG then phycally delete the record.. Get that record corresponding to the
2140 -- Advisor person_id Passed.. To do .. Can this happen from SS Screen? Can I have a record with ACCEPT_ADD_FLAG = 'Y' and again make that to 'N'?
2141 --
2142 CURSOR cur_std_del IS
2143 SELECT azs.ROWID row_id
2144 FROM igs_az_students azs
2145 WHERE azs.group_name = p_group_name
2146 AND azs.student_person_id = p_student_person_id
2147 AND azs.accept_add_flag = 'N';
2148 --
2149 -- Cursor to get all the active relationship of the advisor.
2150 -- anilk, Bug# 3032626, STUDENT/ADVISOR STILL ACTIVE ON THE END DATE
2151 --
2152 CURSOR cur_reln IS
2153 SELECT azr.ROWID row_id,
2154 azr.*
2155 FROM igs_az_advising_rels azr, igs_az_students azs
2156 WHERE azr.group_name = p_group_name
2157 AND azr.group_name = azs.group_name
2158 AND azr.group_student_id = azs.group_student_id
2159 AND azs.student_person_id = p_student_person_id
2160 AND azr.start_date IS NOT NULL
2161 AND TRUNC (NVL (azr.end_date, SYSDATE + 1)) > TRUNC (SYSDATE);
2162 --
2163 --
2164 --
2165 CURSOR cur_grp IS
2166 SELECT auto_stdnt_remove_flag
2167 FROM igs_az_groups azg
2168 WHERE azg.group_name = p_group_name;
2169 --
2170 --
2171 --
2172 CURSOR cur_reln_del IS
2173 SELECT azr.ROWID row_id
2174 FROM igs_az_advising_rels azr, igs_az_students azs
2175 WHERE azr.group_name = p_group_name
2176 AND azr.group_name = azs.group_name
2177 AND azr.group_student_id = azs.group_student_id
2178 AND azs.student_person_id = p_student_person_id
2179 AND azr.start_date IS NULL;
2180 --
2181 -- Local variables:
2182 --
2183 lvReturnStatus VARCHAR2 (10); -- To do look for the dat lengths
2184 lvMsgData VARCHAR2 (1000);
2185 lnMsgCount NUMBER;
2186 lddelstddate DATE;
2187 lvautostdremind VARCHAR2 (1);
2188 lvstddelind VARCHAR2 (1);
2189 --
2190 BEGIN
2191 --
2192 IF p_calling_mod = 'C' THEN
2193 OPEN cur_grp;
2194 FETCH cur_grp INTO lvautostdremind;
2195 CLOSE cur_grp;
2196 --
2197 IF lvautostdremind = 'Y' THEN
2198 lddelstddate := SYSDATE;
2199 lvstddelind := 'Y';
2200 ELSE
2201 IF p_enforce = 'Y' THEN
2202 lddelstddate := SYSDATE;
2203 lvstddelind := 'Y';
2204 ELSE
2205 lddelstddate := NULL;
2206 lvstddelind := 'Y';
2207 END IF;
2208 END IF;
2209 ELSE --- Because if called from SS pages .. the auto_accept ind will be 'Y' and end will be waht is passed as parameter.
2210 lddelstddate := p_end_date;
2211 lvstddelind := 'Y';
2212 END IF;
2213 --
2214 -- End date the relationship..
2215 --
2216 FOR reln_rec IN cur_reln LOOP
2217 igs_az_advising_rels_pkg.update_row (
2218 x_rowid => reln_rec.row_id,
2219 x_group_advising_rel_id => reln_rec.group_advising_rel_id,
2220 x_group_name => reln_rec.group_name,
2221 x_group_advisor_id => reln_rec.group_advisor_id,
2222 x_group_student_id => reln_rec.group_student_id,
2223 x_start_date => reln_rec.start_date,
2224 x_end_date => lddelstddate,
2225 x_return_status => lvReturnStatus,
2226 x_msg_data => lvMsgData,
2227 x_msg_count => lnMsgCount
2228 ); -- To do see if msg count etc. is require , if yes add and Error Handling also to do See how we can verify that the end date is earliest of either the student or the advisor end date if any.
2229 END LOOP; --reln_rec
2230 --
2231 FOR del_reln IN cur_reln_del LOOP
2232 igs_az_advising_rels_pkg.delete_row (
2233 x_rowid => del_reln.row_id,
2234 x_return_status => lvReturnStatus,
2235 x_msg_data => lvMsgData,
2236 x_msg_count => lnMsgCount
2237 );
2238 END LOOP; --Del_reln
2239 --
2240 -- Update the advising record
2241 --
2242 FOR std_upd_rec IN cur_std_upd LOOP
2243 IF p_calling_mod = 'C' THEN -- This is because from the ss pages, there will already be call to update row for this table.
2244 igs_az_students_pkg.update_row (
2245 x_rowid => std_upd_rec.row_id,
2246 x_group_student_id => std_upd_rec.group_student_id,
2247 x_group_name => std_upd_rec.group_name,
2248 x_student_person_id => std_upd_rec.student_person_id,
2249 x_start_date => std_upd_rec.start_date,
2250 x_end_date => lddelstddate, -- this is the only change..
2251 x_advising_hold_type => std_upd_rec.advising_hold_type,
2252 x_hold_start_date => std_upd_rec.hold_start_date,
2253 x_notified_date => std_upd_rec.notified_date,
2254 x_accept_add_flag => std_upd_rec.accept_add_flag,
2255 x_accept_delete_flag => lvstddelind,
2256 x_return_status => lvReturnStatus,
2257 x_msg_data => lvMsgData,
2258 x_msg_count => lnMsgCount
2259 ); -- To Do error handling..
2260 END IF;
2261 --
2262 -- End date the holds and hold effects for the student which was created as part of being ion this group.
2263 --
2264 end_std_advsng_hold (
2265 std_upd_rec.group_name,
2266 std_upd_rec.student_person_id,
2267 p_end_date
2268 ); -- Should the hold be end dated with the end date passed as parameter.
2269 END LOOP;
2270 --
2271 -- See if the record has to be deleted
2272 --
2273 FOR std_del_rec IN cur_std_del LOOP
2274 igs_az_students_pkg.delete_row (
2275 std_del_rec.row_id,
2276 lvReturnStatus,
2277 lvMsgData,
2278 lnMsgCount
2279 );
2280 END LOOP;
2281 --
2282 END end_date_student;
2283 --
2284 --
2285 --
2286 PROCEDURE end_std_advsng_hold (
2287 p_group_name VARCHAR2,
2288 p_person_id NUMBER,
2289 p_hld_end_dt DATE DEFAULT SYSDATE
2290 ) IS
2291 --
2292 -- Select the student record for whom the hold is to be end dated.
2293 --
2294 CURSOR cur_std_hold IS
2295 SELECT azs.ROWID row_id,
2296 azs.*
2297 FROM igs_az_students azs
2298 WHERE azs.group_name = p_group_name AND azs.student_person_id = p_person_id;
2299 --
2300 -- Cursor to get all the advising holds of the students that were applied as part of this group and which are not yet end dated.
2301 --
2302 CURSOR cur_std_grp_hld (cp_hold_type VARCHAR2, cp_start_date DATE) IS
2303 SELECT hld.ROWID row_id,
2304 hld.*
2305 FROM igs_pe_pers_encumb hld
2306 WHERE hld.person_id = p_person_id
2307 AND hld.encumbrance_type = cp_hold_type
2308 AND hld.start_dt = cp_start_date; --AND hld.EXPIRY_DT IS NULL ;
2309 --
2310 -- Cursor to get the hold effects that are to be end dated....
2311 --
2312 CURSOR cur_hld_efct (cp_hold_type VARCHAR2, cp_start_date DATE) IS
2313 SELECT efc.ROWID row_id,
2314 efc.*
2315 FROM igs_pe_persenc_effct efc
2316 WHERE efc.person_id = p_person_id
2317 AND efc.encumbrance_type = cp_hold_type
2318 AND efc.pen_start_dt = cp_start_date; --to do see if there needs to be a check on expiry date also..
2319 --
2320 BEGIN
2321 --
2322 -- Start the loop for Student:
2323 --
2324 FOR std_rec IN cur_std_hold LOOP
2325 --
2326 -- Start the Loop for the holds
2327 --
2328 FOR hold_rec IN cur_std_grp_hld (
2329 std_rec.advising_hold_type,
2330 std_rec.hold_start_date
2331 ) LOOP
2332 --
2333 -- Start the loop for hold effect..
2334 --
2335 FOR effect_rec IN cur_hld_efct (
2336 hold_rec.encumbrance_type,
2337 hold_rec.start_dt
2338 ) LOOP
2339 igs_pe_persenc_effct_pkg.update_row (
2340 x_rowid => effect_rec.row_id,
2341 x_person_id => effect_rec.person_id,
2342 x_encumbrance_type => effect_rec.encumbrance_type,
2343 x_pen_start_dt => effect_rec.pen_start_dt,
2344 x_s_encmb_effect_type => effect_rec.s_encmb_effect_type,
2345 x_pee_start_dt => effect_rec.pee_start_dt,
2346 x_sequence_number => effect_rec.sequence_number,
2347 x_expiry_dt => p_hld_end_dt, -- Only Change
2348 x_course_cd => effect_rec.course_cd,
2349 x_restricted_enrolment_cp => effect_rec.restricted_enrolment_cp,
2350 x_restricted_attendance_type => effect_rec.restricted_attendance_type
2351 );
2352 END LOOP; --effect_rec
2353 --
2354 -- Once all the effects are end dated end date the hold itself.
2355 --
2356 igs_pe_pers_encumb_pkg.update_row (
2357 x_rowid => hold_rec.row_id,
2358 x_person_id => hold_rec.person_id,
2359 x_encumbrance_type => hold_rec.encumbrance_type,
2360 x_start_dt => hold_rec.start_dt,
2361 x_expiry_dt => p_hld_end_dt,
2362 x_authorising_person_id => hold_rec.authorising_person_id,
2363 x_comments => hold_rec.comments,
2364 x_spo_course_cd => hold_rec.spo_course_cd,
2365 x_spo_sequence_number => hold_rec.spo_sequence_number,
2366 x_cal_type => hold_rec.cal_type,
2367 x_sequence_number => hold_rec.sequence_number,
2368 x_auth_resp_id => hold_rec.auth_resp_id,
2369 x_external_reference => hold_rec.external_reference
2370 );
2371 END LOOP; --hold_rec
2372 END LOOP; --std_rec
2373 --
2374 END end_std_advsng_hold;
2375 --
2376 --
2377 --
2378 PROCEDURE submit_maintain_group_job (
2379 p_group_name IN igs_az_groups.group_name%TYPE,
2380 p_return_status OUT NOCOPY VARCHAR2,
2381 p_message_data OUT NOCOPY VARCHAR2,
2382 p_message_count OUT NOCOPY NUMBER,
2383 p_request_id OUT NOCOPY NUMBER
2384 ) AS
2385 --
2386 l_message VARCHAR2 (2000);
2387 l_req_id NUMBER := 100;
2388 --
2389 BEGIN
2390 --
2391 -- This report now needs to take the order number as parameter
2392 --
2393 l_req_id := fnd_request.submit_request (
2394 application => 'IGS',
2395 program => 'IGSAZJ01',
2396 description => NULL,
2397 start_time => SYSDATE,
2398 sub_request => FALSE,
2399 argument1 => p_group_name
2400 );
2401 --
2402 IF l_req_id = 0 THEN
2403 p_message_data := fnd_message.get;
2404 END IF;
2405 --
2406 p_request_id := l_req_id;
2407 --
2408 -- Commit issued as the job will not be saved till commit is done
2409 --
2410 COMMIT;
2411 END submit_maintain_group_job;
2412 --
2413 --
2414 --
2415 PROCEDURE wf_set_role (
2416 itemtype IN VARCHAR2,
2417 itemkey IN VARCHAR2,
2418 actid IN NUMBER,
2419 funcmode IN VARCHAR2,
2420 resultout OUT NOCOPY VARCHAR2
2421 ) AS
2422 /******************************************************************
2423 Created By : anilk
2424 Date Created By : 10-Jun-2003
2425 Purpose : This procedure is called from workflow IGSAZ001
2426 Change History
2427 Who When What
2428 ******************************************************************/
2429 --
2430 l_date_prod VARCHAR2 (30);
2431 l_doc_type VARCHAR2 (30);
2432 l_role_name VARCHAR2 (320);
2433 l_role_display_name VARCHAR2 (320) := 'Adhoc Role for IGSAZ001';
2434 l_person_id_sep VARCHAR2 (4000);
2435 l_person_id VARCHAR2 (30);
2436 --
2437 -- cursor to get the user_name corresponding to the person_id
2438 --
2439 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
2440 SELECT user_name
2441 FROM fnd_user
2442 WHERE person_party_id = cp_person_id;
2443 --
2444 l_user_name fnd_user.user_name%TYPE;
2445 --
2446 --
2447 --
2448 CURSOR c_dup_user (cp_user_name VARCHAR2, cp_role_name VARCHAR2) IS
2449 SELECT COUNT (1)
2450 FROM wf_local_user_roles
2451 WHERE user_name = cp_user_name
2452 AND role_name = cp_role_name
2453 AND role_orig_system = 'WF_LOCAL_ROLES'
2454 AND role_orig_system_id = 0;
2455 --
2456 l_dup_user NUMBER := 0;
2457 --
2458 BEGIN
2459 --
2460 IF (funcmode = 'RUN') THEN
2461 -- create the adhoc role
2462 l_role_name := 'IGS' || SUBSTR (itemkey, 6);
2463 wf_directory.createadhocrole (
2464 role_name => l_role_name,
2465 role_display_name => l_role_display_name
2466 );
2467 --
2468 -- fetch student for whom the record has been procesed and add the user name to the
2469 -- adhoc role
2470 --
2471 l_person_id_sep := wf_engine.getitemattrtext (itemtype, itemkey, 'IA_USERS');
2472 --
2473 WHILE (LENGTH (l_person_id_sep) > 0) LOOP
2474 IF (INSTR (l_person_id_sep, ',') > 0) THEN
2475 l_person_id := SUBSTR (l_person_id_sep, 1, INSTR (l_person_id_sep, ',') - 1);
2476 l_person_id_sep := SUBSTR (l_person_id_sep, INSTR (l_person_id_sep, ',') + 1);
2477 OPEN c_user_name (l_person_id);
2478 FETCH c_user_name INTO l_user_name;
2479 CLOSE c_user_name;
2480 --
2481 -- add this user name to the adhoc role if it is not null and unique
2482 --
2483 OPEN c_dup_user (l_user_name, l_role_name);
2484 FETCH c_dup_user INTO l_dup_user;
2485 CLOSE c_dup_user;
2486 --
2487 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
2488 wf_directory.adduserstoadhocrole (
2489 role_name => l_role_name,
2490 role_users => l_user_name
2491 );
2492 END IF;
2493 ELSE
2494 OPEN c_user_name (l_person_id_sep);
2495 FETCH c_user_name INTO l_user_name;
2496 CLOSE c_user_name;
2497 --
2498 -- add this user name to the adhoc role if it is not null and unique
2499 --
2500 OPEN c_dup_user (l_user_name, l_role_name);
2501 FETCH c_dup_user INTO l_dup_user;
2502 CLOSE c_dup_user;
2503 --
2504 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
2505 wf_directory.adduserstoadhocrole (
2506 role_name => l_role_name,
2507 role_users => l_user_name
2508 );
2509 END IF;
2510 --
2511 l_person_id := l_person_id_sep;
2512 l_person_id_sep := NULL;
2513 --
2514 END IF;
2515 END LOOP;
2516 --
2517 -- now set this role to the workflow
2518 --
2519 wf_engine.setitemattrtext (
2520 itemtype => itemtype,
2521 itemkey => itemkey,
2522 aname => 'IA_ADHOCROLE',
2523 avalue => l_role_name
2524 );
2525 resultout := 'COMPLETE:';
2526 RETURN;
2527 END IF;
2528 --
2529 END wf_set_role;
2530 --
2531 --
2532 --
2533 PROCEDURE deactivate_group (
2534 p_group_name IN VARCHAR2,
2535 x_return_status OUT NOCOPY VARCHAR2,
2536 x_msg_count OUT NOCOPY NUMBER,
2537 x_msg_data OUT NOCOPY VARCHAR2
2538 ) IS
2539 --
2540 -- More info of the group
2541 --
2542 CURSOR cur_grp IS
2543 SELECT azg.ROWID row_id,
2544 azg.*
2545 FROM igs_az_groups azg
2546 WHERE azg.group_name = p_group_name;
2547 --
2548 -- Advisors in the group
2549 --
2550 CURSOR cur_adv IS
2551 SELECT advisor_person_id
2552 FROM igs_az_advisors aza
2553 WHERE aza.group_name = p_group_name;
2554 --
2555 -- Students in the group
2556 --
2557 CURSOR cur_stud IS
2558 SELECT student_person_id
2559 FROM igs_az_students azs
2560 WHERE azs.group_name = p_group_name;
2561 --
2562 grp_rec cur_grp%ROWTYPE;
2563 l_return_status VARCHAR2 (1);
2564 l_msg_count NUMBER;
2565 l_msg_data VARCHAR2 (2000);
2566 --
2567 BEGIN
2568 --
2569 OPEN cur_grp;
2570 FETCH cur_grp INTO grp_rec;
2571 CLOSE cur_grp;
2572 --
2573 igs_az_groups_pkg.update_row (
2574 x_rowid => grp_rec.row_id,
2575 x_group_name => grp_rec.group_name,
2576 x_group_desc => grp_rec.group_desc,
2577 x_advising_code => grp_rec.advising_code,
2578 x_resp_org_unit_cd => grp_rec.resp_org_unit_cd,
2579 x_resp_person_id => grp_rec.resp_person_id,
2580 x_location_cd => grp_rec.location_cd,
2581 x_delivery_method_code => grp_rec.delivery_method_code,
2582 x_advisor_group_id => grp_rec.advisor_group_id,
2583 x_student_group_id => grp_rec.student_group_id,
2584 x_default_advisor_load_num => grp_rec.default_advisor_load_num,
2585 x_mandatory_flag => grp_rec.mandatory_flag,
2586 x_advising_sessions_num => grp_rec.advising_sessions_num,
2587 x_advising_hold_type => grp_rec.advising_hold_type,
2588 x_closed_flag => 'Y',
2589 x_comments_txt => grp_rec.comments_txt,
2590 x_auto_refresh_flag => grp_rec.auto_refresh_flag,
2591 x_last_auto_refresh_date => grp_rec.last_auto_refresh_date,
2592 x_auto_stdnt_add_flag => grp_rec.auto_stdnt_add_flag,
2593 x_auto_stdnt_remove_flag => grp_rec.auto_stdnt_remove_flag,
2594 x_auto_advisor_add_flag => grp_rec.auto_advisor_add_flag,
2595 x_auto_advisor_remove_flag => grp_rec.auto_advisor_remove_flag,
2596 x_auto_match_flag => grp_rec.auto_match_flag,
2597 x_auto_apply_hold_flag => grp_rec.auto_apply_hold_flag,
2598 x_attribute_category => grp_rec.attribute_category,
2599 x_attribute1 => grp_rec.attribute1,
2600 x_attribute2 => grp_rec.attribute2,
2601 x_attribute3 => grp_rec.attribute3,
2602 x_attribute4 => grp_rec.attribute4,
2603 x_attribute5 => grp_rec.attribute5,
2604 x_attribute6 => grp_rec.attribute6,
2605 x_attribute7 => grp_rec.attribute7,
2606 x_attribute8 => grp_rec.attribute8,
2607 x_attribute9 => grp_rec.attribute9,
2608 x_attribute10 => grp_rec.attribute10,
2609 x_attribute11 => grp_rec.attribute11,
2610 x_attribute12 => grp_rec.attribute12,
2611 x_attribute13 => grp_rec.attribute13,
2612 x_attribute14 => grp_rec.attribute14,
2613 x_attribute15 => grp_rec.attribute15,
2614 x_attribute16 => grp_rec.attribute16,
2615 x_attribute17 => grp_rec.attribute17,
2616 x_attribute18 => grp_rec.attribute18,
2617 x_attribute19 => grp_rec.attribute19,
2618 x_attribute20 => grp_rec.attribute20,
2619 x_return_status => l_return_status,
2620 x_msg_data => l_msg_data,
2621 x_msg_count => l_msg_count
2622 );
2623 --
2624 -- End date the advisors, this will end date relations also
2625 --
2626 FOR adv_rec IN cur_adv LOOP
2627 end_date_advisor (
2628 p_group_name => p_group_name,
2629 p_advisor_person_id => adv_rec.advisor_person_id,
2630 p_end_date => TRUNC (SYSDATE),
2631 p_calling_mod => 'C',
2632 p_enforce => 'Y'
2633 );
2634 END LOOP;
2635 --
2636 -- End date the students, this will end date relations also
2637 --
2638 FOR stud_rec IN cur_stud LOOP
2639 end_date_student (
2640 p_group_name => p_group_name,
2641 p_student_person_id => stud_rec.student_person_id,
2642 p_end_date => TRUNC (SYSDATE),
2643 p_calling_mod => 'C',
2644 p_enforce => 'Y'
2645 );
2646 END LOOP;
2647 --
2648 x_return_status := 'S';
2649 --
2650 END deactivate_group;
2651 --
2652 --
2653 --
2654 PROCEDURE reactivate_group (
2655 p_group_name VARCHAR2,
2656 x_return_status OUT NOCOPY VARCHAR2,
2657 x_msg_count OUT NOCOPY NUMBER,
2658 x_msg_data OUT NOCOPY VARCHAR2
2659 ) IS
2660 --
2661 -- More info of the group
2662 --
2663 CURSOR cur_grp IS
2664 SELECT azg.ROWID row_id,
2665 azg.*
2666 FROM igs_az_groups azg
2667 WHERE azg.group_name = p_group_name;
2668 --
2669 grp_rec cur_grp%ROWTYPE;
2670 l_return_status VARCHAR2 (1);
2671 l_msg_count NUMBER;
2672 l_msg_data VARCHAR2 (2000);
2673 --
2674 BEGIN
2675 --
2676 OPEN cur_grp;
2677 FETCH cur_grp INTO grp_rec;
2678 CLOSE cur_grp;
2679 --
2680 igs_az_groups_pkg.update_row (
2681 x_rowid => grp_rec.row_id,
2682 x_group_name => grp_rec.group_name,
2683 x_group_desc => grp_rec.group_desc,
2684 x_advising_code => grp_rec.advising_code,
2685 x_resp_org_unit_cd => grp_rec.resp_org_unit_cd,
2686 x_resp_person_id => grp_rec.resp_person_id,
2687 x_location_cd => grp_rec.location_cd,
2688 x_delivery_method_code => grp_rec.delivery_method_code,
2689 x_advisor_group_id => grp_rec.advisor_group_id,
2690 x_student_group_id => grp_rec.student_group_id,
2691 x_default_advisor_load_num => grp_rec.default_advisor_load_num,
2692 x_mandatory_flag => grp_rec.mandatory_flag,
2693 x_advising_sessions_num => grp_rec.advising_sessions_num,
2694 x_advising_hold_type => grp_rec.advising_hold_type,
2695 x_closed_flag => 'N',
2696 x_comments_txt => grp_rec.comments_txt,
2697 x_auto_refresh_flag => grp_rec.auto_refresh_flag,
2698 x_last_auto_refresh_date => grp_rec.last_auto_refresh_date,
2699 x_auto_stdnt_add_flag => grp_rec.auto_stdnt_add_flag,
2700 x_auto_stdnt_remove_flag => grp_rec.auto_stdnt_remove_flag,
2701 x_auto_advisor_add_flag => grp_rec.auto_advisor_add_flag,
2702 x_auto_advisor_remove_flag => grp_rec.auto_advisor_remove_flag,
2703 x_auto_match_flag => grp_rec.auto_match_flag,
2704 x_auto_apply_hold_flag => grp_rec.auto_apply_hold_flag,
2705 x_attribute_category => grp_rec.attribute_category,
2706 x_attribute1 => grp_rec.attribute1,
2707 x_attribute2 => grp_rec.attribute2,
2708 x_attribute3 => grp_rec.attribute3,
2709 x_attribute4 => grp_rec.attribute4,
2710 x_attribute5 => grp_rec.attribute5,
2711 x_attribute6 => grp_rec.attribute6,
2712 x_attribute7 => grp_rec.attribute7,
2713 x_attribute8 => grp_rec.attribute8,
2714 x_attribute9 => grp_rec.attribute9,
2715 x_attribute10 => grp_rec.attribute10,
2716 x_attribute11 => grp_rec.attribute11,
2717 x_attribute12 => grp_rec.attribute12,
2718 x_attribute13 => grp_rec.attribute13,
2719 x_attribute14 => grp_rec.attribute14,
2720 x_attribute15 => grp_rec.attribute15,
2721 x_attribute16 => grp_rec.attribute16,
2722 x_attribute17 => grp_rec.attribute17,
2723 x_attribute18 => grp_rec.attribute18,
2724 x_attribute19 => grp_rec.attribute19,
2725 x_attribute20 => grp_rec.attribute20,
2726 x_return_status => l_return_status,
2727 x_msg_data => l_msg_data,
2728 x_msg_count => l_msg_count
2729 );
2730 x_return_status := 'S';
2731 --
2732 END reactivate_group;
2733 --
2734 END igs_az_gen_001;
[Home] [Help]