1 PACKAGE BODY igs_re_workflow AS
2 /* $Header: IGSRE21B.pls 120.1 2006/01/17 04:20:11 ckasu noship $ */
3
4 /******************************************************************
5 Created By :Deepankar Dey
6 Date Created By :18-Jul-2001
7 Purpose :This package implements procedure concerned to workflow
8 remarks :
9 Change History
10 Who When What
11 DDEY 8-Sep-2003 The changes are done as per the
12 Enrollments Notifications TD Bug # 3052429
13 stutta 13-Apr-2005 Replaced all references to FND_USER.customer_id to
14 FND_USER.person_party_id. Bug #4293911
15 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL)
16 in enrp_assign_timeslot procedure as a part of bug#4958173.
17 ******************************************************************/
18
19 CURSOR c_research( p_person_id igs_pe_person.person_id%TYPE,
20 p_seq_num igs_re_candidature.sequence_number%TYPE) IS
21 SELECT sca_course_cd, research_topic
22 FROM igs_re_candidature
23 WHERE person_id = p_person_id
24 AND sequence_number = p_seq_num;
25
26 CURSOR c_thesis ( p_person_id igs_pe_person.person_id%TYPE,
27 p_ca_seq_num igs_re_thesis.ca_sequence_number%TYPE,
28 p_seq_num igs_re_thesis.sequence_number%TYPE) IS
29 SELECT title, thesis_topic
30 FROM igs_re_thesis
31 WHERE person_id = p_person_id
32 AND ca_sequence_number=p_ca_seq_num
33 AND sequence_number=p_seq_num;
34
35
36 PROCEDURE get_supervisor(
37 p_personid IN NUMBER,
38 p_ca_sequence_number IN NUMBER ,
39 p_supervisor OUT NOCOPY VARCHAR2
40 ) AS
41 ------------------------------------------------------------------------------------------------
42 -- Created by : Deepankar Dey, Oracle India (in)
43 -- Date created: 05-Sept-2003
44 --
45 -- Purpose: The changes are done as per the Enrollments Notifications TD Bug # 3052429
46 -- The procedure is used to get the supervisors.
47 --
48 --
49 -- Known limitations/enhancements and/or remarks:
50 --
51 -- Change History:
52 -- Who When What
53 --
54 -------------------------------------------------------------------------------------------------
55
56 CURSOR cur_get_supervisor( p_ca_person_id igs_re_sprvsr.ca_person_id%TYPE,
57 p_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE) IS
58 SELECT person_id
59 FROM igs_re_sprvsr
60 WHERE ca_person_id = p_ca_person_id
61 AND ca_sequence_number = p_ca_sequence_number ;
62
63
64 BEGIN
65
66 FOR l_cur_get_supervisor IN cur_get_supervisor(p_personid,p_ca_sequence_number) LOOP
67
68 IF (p_supervisor IS NOT NULL) THEN
69
70 p_supervisor := p_supervisor || ',' || l_cur_get_supervisor.person_id ;
71
72 ELSE
73
74 p_supervisor := l_cur_get_supervisor.person_id ;
75
76 END IF;
77
78 END LOOP;
79
80
81 END get_supervisor;
82
83
84 PROCEDURE get_panel_member(
85 p_personid IN NUMBER,
86 p_ca_sequence_number IN NUMBER ,
87 p_the_sequence_number IN NUMBER ,
88 p_creation_dt IN DATE ,
89 p_panel_member OUT NOCOPY VARCHAR2
90 ) AS
91 ------------------------------------------------------------------------------------------------
92 -- Created by : Deepankar Dey, Oracle India (in)
93 -- Date created: 05-Sept-2003
94 --
95 -- Purpose: This procedure would return the panel members for a thesis in a concatenated format
96 -- with ',' as the delimiter.
97 --
98 --
99 -- Known limitations/enhancements and/or remarks:
100 --
101 -- Change History:
102 -- Who When What
103 --
104 -------------------------------------------------------------------------------------------------
105
106 CURSOR cur_get_panelmem( p_ca_person_id igs_re_ths_pnl_mbr.ca_person_id%TYPE,
107 p_ca_sequence_number igs_re_ths_pnl_mbr.ca_sequence_number%TYPE,
108 p_the_sequence_number igs_re_ths_pnl_mbr.the_sequence_number%TYPE,
109 p_creation_dt igs_re_ths_pnl_mbr.creation_dt%TYPE) IS
110 SELECT person_id
111 FROM igs_re_ths_pnl_mbr
112 WHERE ca_person_id = p_personid
113 AND ca_sequence_number= p_ca_sequence_number
114 AND the_sequence_number= p_the_sequence_number
115 AND creation_dt= p_creation_dt ;
116
117 BEGIN
118
119 FOR l_cur_get_panelmem IN cur_get_panelmem(p_personid,p_ca_sequence_number,p_the_sequence_number,p_creation_dt) LOOP
120
121 IF (p_panel_member IS NOT NULL) THEN
122
123 p_panel_member := p_panel_member || ',' || l_cur_get_panelmem.person_id ;
124
125 ELSE
126
127 p_panel_member := l_cur_get_panelmem.person_id ;
128
129 END IF;
130
131 END LOOP;
132
133
134 END get_panel_member;
135
136
137 PROCEDURE retopic_event (
138 p_personid IN NUMBER ,
139 p_programcd IN VARCHAR2 ,
140 p_restopic IN VARCHAR2
141 ) IS
142 ------------------------------------------------------------------------------------------------
143 -- Created by : Deepankar Dey, Oracle India (in)
144 -- Date created: 30-04-2003
145 --
146 -- Purpose:Bug # 2829275 . UK Correspondence.The TBH needs to be modified to invoke the thesis topic change business event when thesis topic
147 -- is changed.
148 --
149 --
150 --
151 -- Known limitations/enhancements and/or remarks:
152 --
153 -- Change History:
154 -- Who When What
155 --
156 -------------------------------------------------------------------------------------------------
157
158 l_event_t wf_event_t;
159 l_parameter_list_t wf_parameter_list_t;
160 l_itemKey varchar2(100);
161 ln_seq_val NUMBER;
162
163 -- Gets a unique sequence number
164
165 CURSOR c_seq_num IS
166 SELECT igs_re_retopc_s.NEXTVAL
167 FROM dual;
168
169
170 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
171
172 CURSOR cur_prof_value IS
173 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
174 FROM dual;
175
176 -- Getting the logged in User
177
178 CURSOR cur_user_id IS
179 SELECT FND_GLOBAL.USER_ID user_id
180 FROM dual;
181
182 l_cur_prof_value cur_prof_value%ROWTYPE;
183 l_cur_user_id cur_user_id%ROWTYPE;
184
185
186 BEGIN
187
188 -- Checking if the Workflow is installed at the environment or not.
189
190 OPEN cur_prof_value;
191 FETCH cur_prof_value INTO l_cur_prof_value;
192 CLOSE cur_prof_value;
193
194 IF (l_cur_prof_value.value = 'Y') THEN
195
196 -- Get the sequence value
197
198 OPEN c_seq_num;
199 FETCH c_seq_num INTO ln_seq_val ;
200 CLOSE c_seq_num ;
201
202 -- Getting the Logged on User
203
204 OPEN cur_user_id ;
205 FETCH cur_user_id INTO l_cur_user_id ;
206 CLOSE cur_user_id ;
207
208 --
209 -- initialize the wf_event_t object
210 --
211
212 wf_event_t.Initialize(l_event_t);
213
214
215 --
216 -- Adding the parameters to the parameter list
217 --
218
219 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
220 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => p_programcd, p_ParameterList => l_parameter_list_t);
221 wf_event.AddParameterToList (p_name => 'P_RESTOPIC', p_Value => p_restopic, p_ParameterList => l_parameter_list_t);
222 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
223
224
225 -- Raise the Event
226
227 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.restop.update',
228 p_event_key => 'RESTOPUPDATE'||ln_seq_val,
229 p_parameters => l_parameter_list_t);
230
231 --
232 -- Deleting the Parameter list after the event is raised
233 --
234
235 l_parameter_list_t.delete;
236
237 END IF;
238
239 END retopic_event;
240
241 PROCEDURE rethesis_event (
242 p_personid IN NUMBER ,
243 p_ca_seq_num IN NUMBER ,
244 p_thesistopic IN VARCHAR2 ,
245 p_thesistitle IN VARCHAR2 ,
246 p_approved IN VARCHAR2 ,
247 p_deleted IN VARCHAR2
248 ) IS
249 ------------------------------------------------------------------------------------------------
250 -- Created by : Deepankar Dey, Oracle India (in)
251 -- Date created: 30-04-2003
252 --
253 -- Purpose:Bug # 2829275 . UK Correspondence.The TBH needs to be modified to invoke the thesis event is raised
254 -- when there is a change in thesis attributes.
255 --
256 --
257 --
258 -- Known limitations/enhancements and/or remarks:
259 --
260 -- Change History:
261 -- Who When What
262 --
263 -------------------------------------------------------------------------------------------------
264
265 l_event_t wf_event_t;
266 l_parameter_list_t wf_parameter_list_t;
267 l_itemKey varchar2(100);
268 ln_seq_val NUMBER;
269
270 -- Gets a unique sequence number
271
272 CURSOR c_seq_num IS
273 SELECT igs_re_thesis_s.NEXTVAL
274 FROM dual;
275
276
277 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
278
279 CURSOR cur_prof_value IS
280 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
281 FROM dual;
282
283 -- Getting the logged in User
284
285 CURSOR cur_user_id IS
286 SELECT FND_GLOBAL.USER_ID user_id
287 FROM dual;
288
289 -- Getting the course Code for the research canditure
290
291 CURSOR cur_course_cd IS
292 SELECT sca_course_cd
293 FROM igs_re_candidature
294 WHERE person_id = p_personid
295 AND sequence_number = p_ca_seq_num;
296
297 l_cur_prof_value cur_prof_value%ROWTYPE;
298 l_cur_user_id cur_user_id%ROWTYPE;
299 l_cur_course_cd cur_course_cd%ROWTYPE;
300
301
302 BEGIN
303
304 -- Checking if the Workflow is installed at the environment or not.
305
306 OPEN cur_prof_value;
307 FETCH cur_prof_value INTO l_cur_prof_value;
308 CLOSE cur_prof_value;
309
310 IF (l_cur_prof_value.value = 'Y') THEN
311
312 -- Get the sequence value
313
314 OPEN c_seq_num;
315 FETCH c_seq_num INTO ln_seq_val ;
316 CLOSE c_seq_num ;
317
318 -- Getting the Logged on User
319
320 OPEN cur_user_id ;
321 FETCH cur_user_id INTO l_cur_user_id ;
322 CLOSE cur_user_id ;
323
324 -- Getting the course Code for the research canditure
325
326 OPEN cur_course_cd;
327 FETCH cur_course_cd INTO l_cur_course_cd;
328 CLOSE cur_course_cd;
329
330 --
331 -- initialize the wf_event_t object
332 --
333
334 wf_event_t.Initialize(l_event_t);
335
336
337
338 --
339 -- Adding the parameters to the parameter list
340 --
341
342 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
343 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => l_cur_course_cd.sca_course_cd, p_ParameterList => l_parameter_list_t);
344 wf_event.AddParameterToList (p_name => 'P_THESISTOPIC', p_Value => p_thesistopic, p_ParameterList => l_parameter_list_t);
345 wf_event.AddParameterToList (p_name => 'P_THESISTITLE', p_Value => p_thesistitle, p_ParameterList => l_parameter_list_t);
346 wf_event.AddParameterToList (p_name => 'P_APPROVED', p_Value => p_approved, p_ParameterList => l_parameter_list_t);
347 wf_event.AddParameterToList (p_name => 'P_DELETED', p_Value => p_deleted, p_ParameterList => l_parameter_list_t);
348 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
349
350 -- Raise the Event
351
352 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.thesis.update',
353 p_event_key => 'THESISUPDATE'||ln_seq_val,
354 p_parameters => l_parameter_list_t);
355
356 --
357 -- Deleting the Parameter list after the event is raised
358 --
359
360 l_parameter_list_t.delete;
361
362 END IF;
363
364 END rethesis_event;
365
366 PROCEDURE supervision_event (
367 p_personid IN NUMBER ,
368 p_ca_seq_num IN NUMBER ,
369 p_supervisorid IN NUMBER ,
370 p_startdt IN DATE ,
371 p_enddt IN DATE ,
372 p_spr_percent IN NUMBER ,
373 p_spr_type IN VARCHAR2,
374 p_fund_percent IN NUMBER ,
375 p_org_unit_cd IN VARCHAR2 ,
376 p_rep_person_id IN VARCHAR2 ,
377 p_rep_seq_num IN NUMBER
378 ) IS
379
380 ------------------------------------------------------------------------------------------------
381 -- Created by : Deepankar Dey, Oracle India (in)
382 -- Date created: 30-04-2003
383 --
384 -- Purpose:Bug # 2829275 . UK Correspondence.Raises the supervision event when supervisor attributes of a student changes.
385 --
386 --
387 -- Known limitations/enhancements and/or remarks:
388 --
389 -- Change History:
390 -- Who When What
391 --
392 -------------------------------------------------------------------------------------------------
393
394 l_event_t wf_event_t;
395 l_parameter_list_t wf_parameter_list_t;
396 l_itemKey varchar2(100);
397 ln_seq_val NUMBER;
398
399 -- Gets a unique sequence number
400
401 CURSOR c_seq_num IS
402 SELECT igs_re_sprvsn_s.NEXTVAL
403 FROM dual;
404
405
406 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
407
408 CURSOR cur_prof_value IS
409 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
410 FROM dual;
411
412 -- Getting the logged in User
413
414 CURSOR cur_user_id IS
415 SELECT FND_GLOBAL.USER_ID user_id
416 FROM dual;
417
418 -- Getting the course Code for the research canditure
419
420 CURSOR cur_course_cd IS
421 SELECT sca_course_cd
422 FROM igs_re_candidature
423 WHERE person_id = p_personid
424 AND sequence_number = p_ca_seq_num;
425
426 -- Getting the Start Date for the research Supervisor.
427
428 CURSOR cur_res_start_dt IS
429 SELECT
430 start_dt
431 FROM igs_re_sprvsr
432 WHERE
433 ca_person_id = p_personid
434 AND ca_sequence_number = p_ca_seq_num
435 AND person_id = p_rep_person_id
436 AND sequence_number = p_rep_seq_num ;
437
438 l_cur_prof_value cur_prof_value%ROWTYPE;
439 l_cur_user_id cur_user_id%ROWTYPE;
440 l_cur_course_cd cur_course_cd%ROWTYPE;
441 l_cur_res_start_dt cur_res_start_dt%ROWTYPE;
442 l_staff_ind VARCHAR2(1);
443
444 BEGIN
445
446
447 -- Checking if the Workflow is installed at the environment or not.
448
449 OPEN cur_prof_value;
450 FETCH cur_prof_value INTO l_cur_prof_value;
451 CLOSE cur_prof_value;
452
453 IF (l_cur_prof_value.value = 'Y') THEN
454
455 -- Get the sequence value
456
457 OPEN c_seq_num;
458 FETCH c_seq_num INTO ln_seq_val ;
459 CLOSE c_seq_num ;
460
461 -- Getting the Logged on User
462
463 OPEN cur_user_id ;
464 FETCH cur_user_id INTO l_cur_user_id ;
465 CLOSE cur_user_id ;
466
467 -- Getting the course Code for the research canditure
468
469 OPEN cur_course_cd;
470 FETCH cur_course_cd INTO l_cur_course_cd;
471 CLOSE cur_course_cd;
472
473 -- Getting the Start Date for the research Supervisor.
474
475 OPEN cur_res_start_dt ;
476 FETCH cur_res_start_dt INTO l_cur_res_start_dt;
477 CLOSE cur_res_start_dt;
478 --
479 -- initialize the wf_event_t object
480 --
481
482 wf_event_t.Initialize(l_event_t);
483
484
485 l_staff_ind := IGS_EN_GEN_003.GET_STAFF_IND(p_supervisorid);
486
487 --
488 -- Adding the parameters to the parameter list
489 --
490
491 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
492 wf_event.AddParameterToList (p_name => 'P_PROG_CD', p_Value => l_cur_course_cd.sca_course_cd, p_ParameterList => l_parameter_list_t);
493 wf_event.AddParameterToList (p_name => 'P_SUPERVISORID', p_Value => p_supervisorid, p_ParameterList => l_parameter_list_t);
494 wf_event.AddParameterToList (p_name => 'P_STAFF_IND', p_Value => l_staff_ind, p_ParameterList => l_parameter_list_t);
495 wf_event.AddParameterToList (p_name => 'P_STARTDT', p_Value => p_startdt, p_ParameterList => l_parameter_list_t);
496 wf_event.AddParameterToList (p_name => 'P_ENDDT', p_Value => p_enddt, p_ParameterList => l_parameter_list_t);
497 wf_event.AddParameterToList (p_name => 'P_SPR_PERCENT', p_Value => p_spr_percent, p_ParameterList => l_parameter_list_t);
498 wf_event.AddParameterToList (p_name => 'P_SPR_TYPE', p_Value => p_spr_type, p_ParameterList => l_parameter_list_t);
499 wf_event.AddParameterToList (p_name => 'P_FUND_PERCENT', p_Value => p_fund_percent, p_ParameterList => l_parameter_list_t);
500 wf_event.AddParameterToList (p_name => 'P_ORG_UNIT_CD', p_Value => p_org_unit_cd, p_ParameterList => l_parameter_list_t);
501 wf_event.AddParameterToList (p_name => 'P_REP_PERSON_ID', p_Value => p_rep_person_id, p_ParameterList => l_parameter_list_t);
502 wf_event.AddParameterToList (p_name => 'P_REP_STARTDT', p_Value => l_cur_res_start_dt.start_dt, p_ParameterList => l_parameter_list_t);
503 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
504
505 -- Raise the Event
506
507 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.sprvsn.update',
508 p_event_key => 'SPRVSNUPDATE'||ln_seq_val,
509 p_parameters => l_parameter_list_t);
510
511 --
512 -- Deleting the Parameter list after the event is raised
513 --
514
515 l_parameter_list_t.delete;
516
517 END IF;
518
519 END supervision_event;
520
521 PROCEDURE overduesub_event (
522 p_personid IN NUMBER ,
523 p_programcd IN VARCHAR2,
524 p_thesistitle IN VARCHAR2,
525 p_maxsubdt IN DATE,
526 p_suprvsr IN VARCHAR2
527 ) IS
528
529 ------------------------------------------------------------------------------------------------
530 -- Created by : Deepankar Dey, Oracle India (in)
531 -- Date created: 30-04-2003
532 --
533 -- Purpose:Bug # 2829275 . This procedure would trigger the overdue submission business event.
534 -- It is triggered from a concurrent process that identified whether any submissions are overdue.
535 --
536 --
537 -- Known limitations/enhancements and/or remarks:
538 --
539 -- Change History:
540 -- Who When What
541 --
542 -------------------------------------------------------------------------------------------------
543
544 l_event_t wf_event_t;
545 l_parameter_list_t wf_parameter_list_t;
546 l_itemKey varchar2(100);
547 ln_seq_val NUMBER;
548
549 -- Gets a unique sequence number
550
551 CURSOR c_seq_num IS
552 SELECT igs_en_ovrsub_s.NEXTVAL
553 FROM dual;
554
555
556 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
557
558 CURSOR cur_prof_value IS
559 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
560 FROM dual;
561
562 -- Getting the logged in User
563
564 CURSOR cur_user_id IS
565 SELECT FND_GLOBAL.USER_ID user_id
566 FROM dual;
567
568 l_cur_prof_value cur_prof_value%ROWTYPE;
569 l_cur_user_id cur_user_id%ROWTYPE;
570
571
572 BEGIN
573
574 -- Checking if the Workflow is installed at the environment or not.
575
576 OPEN cur_prof_value;
577 FETCH cur_prof_value INTO l_cur_prof_value;
578 CLOSE cur_prof_value;
579
580 IF (l_cur_prof_value.value = 'Y') THEN
581
582
583 -- Get the sequence value
584
585 OPEN c_seq_num;
586 FETCH c_seq_num INTO ln_seq_val ;
587 CLOSE c_seq_num ;
588
589 -- Getting the Logged on User
590
591 OPEN cur_user_id ;
592 FETCH cur_user_id INTO l_cur_user_id ;
593 CLOSE cur_user_id ;
594
595 --
596 -- initialize the wf_event_t object
597 --
598
599 wf_event_t.Initialize(l_event_t);
600
601
602 --
603 -- Adding the parameters to the parameter list
604 --
605
606 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
607 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => p_programcd, p_ParameterList => l_parameter_list_t);
608 wf_event.AddParameterToList (p_name => 'P_THESISTITLE', p_Value => p_thesistitle, p_ParameterList => l_parameter_list_t);
609 wf_event.AddParameterToList (p_name => 'P_MAXSUBDT', p_Value => p_maxsubdt, p_ParameterList => l_parameter_list_t);
610 wf_event.AddParameterToList (p_name => 'P_SUPRVSR',p_value=>p_suprvsr,p_parameterlist=>l_parameter_list_t);
611 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
612
613
614 -- Raise the Event
615
616
617 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.subm.overdue',
618 p_event_key => 'SUBMOVERDUE'||ln_seq_val,
619 p_parameters => l_parameter_list_t);
620
621
622 --
623 -- Deleting the Parameter list after the event is raised
624 --
625
626 l_parameter_list_t.delete;
627
628 END IF;
629
630
631 END overduesub_event ;
632
633 PROCEDURE retopic_not (
634 itemtype IN VARCHAR2 ,
635 itemkey IN VARCHAR2 ,
636 actid IN NUMBER ,
637 funcmode IN VARCHAR2 ,
638 resultout OUT NOCOPY VARCHAR2 ) AS
639
640 ------------------------------------------------------------------------------------------------
641 -- Created by : Deepankar Dey, Oracle India (in)
642 -- Date created: 30-04-2003
643 --
644 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
645 --
646 --
647 --
648 -- Known limitations/enhancements and/or remarks:
649 --
650 -- Change History:
651 -- Who When What
652 --
653 -------------------------------------------------------------------------------------------------
654 l_date_prod VARCHAR2(30);
655 l_doc_type VARCHAR2(30);
656 l_role_name VARCHAR2(320);
657 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSRE001';
658 l_person_id_stu VARCHAR2(4000);
659 l_person_id_app VARCHAR2(4000);
660 l_person_id VARCHAR2(30);
661
662 -- cursor to get the user_name corresponding to the person_id
663
664 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
665 SELECT user_name
666 FROM fnd_user
667 WHERE person_party_id = cp_person_id
668 AND ( end_date IS NULL OR end_date > SYSDATE );
669
670 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
671 SELECT user_name
672 FROM fnd_user
673 WHERE user_id = cp_person_id
674 AND ( end_date IS NULL OR end_date > SYSDATE );
675
676 l_user_name fnd_user.user_name%TYPE;
677 l_user_name_admin fnd_user.user_name%TYPE;
678
679 CURSOR c_dup_user (cp_user_name VARCHAR2,
680 cp_role_name VARCHAR2) IS
681 SELECT count(1)
682 FROM WF_LOCAL_USER_ROLES
683 WHERE USER_NAME = cp_user_name
684 AND ROLE_NAME = cp_role_name
685 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
686 AND ROLE_ORIG_SYSTEM_ID = 0;
687
688 l_dup_user NUMBER :=0;
689
690 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
691 SELECT full_name
692 FROM igs_pe_person_base_v
693 WHERE person_id = cp_person_id;
694
695 l_c_full_name c_full_name%ROWTYPE;
696
697 BEGIN
698
699
700
701 IF (funcmode = 'RUN') THEN
702 -- create the adhoc role
703 l_role_name := 'IGS'|| itemkey ;
704
705 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
706 role_display_name => l_role_display_name
707 );
708
709
710 --
711 -- fetch student for whom the record has been procesed and add the user name to the
712 -- adhoc role
713 --
714 --
715
716
717 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
718 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
719
720
721 -- Getting the Full Name of the Student
722
723 OPEN c_full_name(l_person_id_stu);
724 FETCH c_full_name INTO l_c_full_name;
725 CLOSE c_full_name;
726
727 -- Setting this full name of the student
728
729 Wf_Engine.SetItemAttrText( ItemType => itemtype,
730 ItemKey => itemkey,
731 aname => 'IA_PERSONNAME',
732 avalue => l_c_full_name.full_name
733 );
734
735 -- Checking for User Name for the corresponding person ID of a student
736
737
738
739 OPEN c_user_name (l_person_id_stu);
740 FETCH c_user_name INTO l_user_name;
741 CLOSE c_user_name;
742
743
744 -- add this user name to the adhoc role if it is not null and unique
745 OPEN c_dup_user(l_user_name,l_role_name);
746 FETCH c_dup_user INTO l_dup_user;
747 CLOSE c_dup_user;
748
749 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
750
751 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
752 role_users => l_user_name);
753 END IF;
754
755
756 -- Checking for User Name for the corresponding person ID of a approver
757 OPEN c_user_name_admin (l_person_id_app);
758 FETCH c_user_name_admin INTO l_user_name_admin;
759 CLOSE c_user_name_admin;
760
761
762 -- add this user name to the adhoc role if it is not null and unique
763 OPEN c_dup_user(l_user_name_admin,l_role_name);
764 FETCH c_dup_user INTO l_dup_user;
765 CLOSE c_dup_user;
766
767 IF l_user_name_admin IS NOT NULL AND l_dup_user = 0 THEN
768 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
769 role_users => l_user_name_admin);
770 END IF;
771
772 -- now set this role to the workflow
773
774 Wf_Engine.SetItemAttrText( ItemType => itemtype,
775 ItemKey => itemkey,
776 aname => 'IA_ROLE',
777 avalue => l_role_name
778 );
779
780 Resultout:= 'COMPLETE:';
781 RETURN;
782 END IF;
783
784
785 END retopic_not;
786
787 PROCEDURE thesis_not (
788 itemtype IN VARCHAR2 ,
789 itemkey IN VARCHAR2 ,
790 actid IN NUMBER ,
791 funcmode IN VARCHAR2 ,
792 resultout OUT NOCOPY VARCHAR2 ) AS
793
794 ------------------------------------------------------------------------------------------------
795 -- Created by : Deepankar Dey, Oracle India (in)
796 -- Date created: 30-04-2003
797 --
798 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
799 --
800 --
801 --
802 -- Known limitations/enhancements and/or remarks:
803 --
804 -- Change History:
805 -- Who When What
806 --
807 -------------------------------------------------------------------------------------------------
808
809 l_date_prod VARCHAR2(30);
810 l_doc_type VARCHAR2(30);
811 l_role_name VARCHAR2(320);
812 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSRE002';
813 l_person_id_stu VARCHAR2(50);
814 l_person_id_app VARCHAR2(50);
815 l_person_id VARCHAR2(30);
816 l_delete VARCHAR2(10);
817 l_approved VARCHAR2(10);
818 -- cursor to get the user_name corresponding to the person_id
819
820 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
821 SELECT user_name
822 FROM fnd_user
823 WHERE person_party_id = cp_person_id
824 AND ( end_date IS NULL OR end_date > SYSDATE );
825
826
827 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
828 SELECT user_name
829 FROM fnd_user
830 WHERE user_id = cp_person_id
831 AND ( end_date IS NULL OR end_date > SYSDATE );
832
833
834 l_user_name fnd_user.user_name%TYPE;
835 l_user_name_admin fnd_user.user_name%TYPE;
836
837 CURSOR c_dup_user (cp_user_name VARCHAR2,
838 cp_role_name VARCHAR2) IS
839 SELECT count(1)
840 FROM WF_LOCAL_USER_ROLES
841 WHERE USER_NAME = cp_user_name
842 AND ROLE_NAME = cp_role_name
843 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
844 AND ROLE_ORIG_SYSTEM_ID = 0;
845
846 l_dup_user NUMBER :=0;
847
848 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
849 SELECT full_name
850 FROM igs_pe_person_base_v
851 WHERE person_id = cp_person_id;
852
853 l_c_full_name c_full_name%ROWTYPE;
854
855 CURSOR c_mesg_text (cp_message_name fnd_new_messages.message_name%TYPE) IS
856 SELECT message_text
857 FROM fnd_new_messages
858 WHERE message_name = cp_message_name;
859
860 l_c_mesg_text c_mesg_text%ROWTYPE;
861
862 BEGIN
863
864
865
866 IF (funcmode = 'RUN') THEN
867 -- create the adhoc role
868 l_role_name := 'IGS'||substr(itemkey,6);
869
870 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
871 role_display_name => l_role_display_name
872 );
873
874
875 --
876 -- fetch student for whom the record has been procesed and add the user name to the
877 -- adhoc role
878 --
879 --
880
881 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
882 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
883 l_delete := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_DELETED');
884 l_approved := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_APPROVED');
885
886
887
888 -- Setting the value of the attribute IA_APPROVED_MSG based on the P_APPROVED value.
889
890 IF l_approved = 'N' THEN
891
892 OPEN c_mesg_text('IGS_EN_NOT');
893 FETCH c_mesg_text INTO l_c_mesg_text;
894 CLOSE c_mesg_text ;
895
896 -- Setting this the attribute IA_APPROVED_MSG
897
898
899 Wf_Engine.SetItemAttrText( ItemType => itemtype,
900 ItemKey => itemkey,
901 aname => 'IA_APPROVED_MSG',
902 avalue => l_c_mesg_text.message_text
903 );
904
905 END IF;
906
907
908 -- Getting the Full Name of the Student
909
910 OPEN c_full_name(l_person_id_stu);
911 FETCH c_full_name INTO l_c_full_name;
912 CLOSE c_full_name;
913
914 -- Setting this full name of the student
915
916 Wf_Engine.SetItemAttrText( ItemType => itemtype,
917 ItemKey => itemkey,
918 aname => 'IA_PERSONNAME',
919 avalue => l_c_full_name.full_name
920 );
921
922 -- Checking for User Name for the corresponding person ID of a student
923
924 OPEN c_user_name (l_person_id_stu);
925 FETCH c_user_name INTO l_user_name;
926 CLOSE c_user_name;
927
928
929 -- add this user name to the adhoc role if it is not null and unique
930 OPEN c_dup_user(l_user_name,l_role_name);
931 FETCH c_dup_user INTO l_dup_user;
932 CLOSE c_dup_user;
933
934 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
935 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
936 role_users => l_user_name);
937 END IF;
938
939
940 -- Checking for User Name for the corresponding person ID of a Admin
941 OPEN c_user_name_admin (l_person_id_app);
942 FETCH c_user_name_admin INTO l_user_name_admin;
943 CLOSE c_user_name_admin;
944
945 -- add this user name to the adhoc role if it is not null and unique
946 OPEN c_dup_user(l_user_name_admin,l_role_name);
947 FETCH c_dup_user INTO l_dup_user;
948 CLOSE c_dup_user;
949
950
951 IF l_user_name_admin IS NOT NULL AND l_dup_user = 0 THEN
952 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
953 role_users => l_user_name_admin);
954 END IF;
955
956 -- now set this role to the workflow
957 Wf_Engine.SetItemAttrText( ItemType => itemtype,
958 ItemKey => itemkey,
959 aname => 'IA_ROLE',
960 avalue => l_role_name
961 );
962
963
964 IF (l_delete = 'Y') THEN
965
966 resultout := 'COMPLETE:Y' ;
967 RETURN;
968
969 ELSIF (l_delete = 'N' or l_delete IS NULL ) THEN
970
971 resultout := 'COMPLETE:N' ;
972 RETURN;
973
974 END IF;
975
976 Resultout:= 'COMPLETE:';
977 RETURN;
978 END IF;
979
980
981 END thesis_not;
982
983 PROCEDURE supervision_not (
984 itemtype IN VARCHAR2 ,
985 itemkey IN VARCHAR2 ,
986 actid IN NUMBER ,
987 funcmode IN VARCHAR2 ,
988 resultout OUT NOCOPY VARCHAR2 ) AS
989 ------------------------------------------------------------------------------------------------
990 -- Created by : Deepankar Dey, Oracle India (in)
991 -- Date created: 30-04-2003
992 --
993 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
994 --
995 --
996 --
997 -- Known limitations/enhancements and/or remarks:
998 --
999 -- Change History:
1000 -- Who When What
1001 --
1002 -------------------------------------------------------------------------------------------------
1003
1004 l_date_prod VARCHAR2(30);
1005 l_doc_type VARCHAR2(30);
1006 l_role_name VARCHAR2(320);
1007 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSRE003';
1008 l_person_id_stu VARCHAR2(50);
1009 l_person_id_suvisor VARCHAR2(50);
1010 l_person_id_app VARCHAR2(50);
1011 l_person_id VARCHAR2(30);
1012 l_staff_ind VARCHAR2(10);
1013 -- cursor to get the user_name corresponding to the person_id
1014
1015 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
1016 SELECT user_name
1017 FROM fnd_user
1018 WHERE person_party_id = cp_person_id
1019 AND ( end_date IS NULL OR end_date > SYSDATE );
1020
1021
1022 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
1023 SELECT user_name
1024 FROM fnd_user
1025 WHERE user_id = cp_person_id
1026 AND ( end_date IS NULL OR end_date > SYSDATE );
1027
1028
1029 l_user_name_stu fnd_user.user_name%TYPE;
1030 l_user_name_adm fnd_user.user_name%TYPE;
1031 l_user_name_sup fnd_user.user_name%TYPE;
1032
1033 CURSOR c_dup_user (cp_user_name VARCHAR2,
1034 cp_role_name VARCHAR2) IS
1035 SELECT count(1)
1036 FROM WF_LOCAL_USER_ROLES
1037 WHERE USER_NAME = cp_user_name
1038 AND ROLE_NAME = cp_role_name
1039 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
1040 AND ROLE_ORIG_SYSTEM_ID = 0;
1041
1042 l_dup_user NUMBER :=0;
1043
1044 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1045 SELECT full_name
1046 FROM igs_pe_person_base_v
1047 WHERE person_id = cp_person_id;
1048
1049 l_c_full_name c_full_name%ROWTYPE;
1050 l_c_full_name_sup c_full_name%ROWTYPE;
1051
1052 CURSOR c_mesg_text (cp_message_name fnd_new_messages.message_name%TYPE) IS
1053 SELECT message_text
1054 FROM fnd_new_messages
1055 WHERE message_name = cp_message_name;
1056
1057 l_c_mesg_text c_mesg_text%ROWTYPE;
1058
1059 BEGIN
1060
1061
1062
1063 IF (funcmode = 'RUN') THEN
1064 -- create the adhoc role
1065 l_role_name := 'IGS'||substr(itemkey,6);
1066
1067 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
1068 role_display_name => l_role_display_name
1069 );
1070
1071
1072 --
1073 -- fetch student for whom the record has been procesed and add the user name to the
1074 -- adhoc role
1075 --
1076 --
1077
1078 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
1079 l_person_id_suvisor := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_SUPERVISORID');
1080 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
1081 l_staff_ind := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_STAFF_IND');
1082
1083
1084
1085 -- Setting the value of the attribute IA_APPROVED_MSG based on the P_APPROVED value.
1086
1087 IF l_staff_ind = 'N' THEN
1088
1089 OPEN c_mesg_text('IGS_EN_NOT');
1090 FETCH c_mesg_text INTO l_c_mesg_text;
1091 CLOSE c_mesg_text ;
1092
1093 -- Setting this the attribute IA_APPROVED_MSG
1094
1095 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1096 ItemKey => itemkey,
1097 aname => 'IA_STAFF_IND',
1098 avalue => l_c_mesg_text.message_text
1099 );
1100
1101 END IF;
1102
1103
1104 -- Getting the Full Name of the Student
1105
1106 OPEN c_full_name(l_person_id_stu);
1107 FETCH c_full_name INTO l_c_full_name;
1108 CLOSE c_full_name;
1109
1110
1111 -- Setting this full name of the student
1112
1113 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1114 ItemKey => itemkey,
1115 aname => 'IA_PERSONNAME',
1116 avalue => l_c_full_name.full_name
1117 );
1118
1119
1120 -- Getting the Full Name of the Supervisor
1121
1122 OPEN c_full_name(l_person_id_suvisor);
1123 FETCH c_full_name INTO l_c_full_name_sup;
1124 CLOSE c_full_name;
1125
1126 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1127 ItemKey => itemkey,
1128 aname => 'P_SUPERVISORNAME',
1129 avalue => l_c_full_name_sup.full_name
1130 );
1131
1132
1133
1134 -- Checking for User Name for the corresponding person ID of a student
1135
1136 OPEN c_user_name (l_person_id_stu);
1137 FETCH c_user_name INTO l_user_name_stu;
1138 CLOSE c_user_name;
1139
1140 -- add this user name to the adhoc role if it is not null and unique
1141
1142 IF ( l_user_name_stu IS NOT NULL ) THEN
1143
1144 OPEN c_dup_user(l_user_name_stu,l_role_name);
1145 FETCH c_dup_user INTO l_dup_user;
1146 CLOSE c_dup_user;
1147
1148 IF l_user_name_stu IS NOT NULL AND l_dup_user = 0 THEN
1149 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1150 role_users => l_user_name_stu);
1151 END IF;
1152 END IF;
1153
1154
1155 -- Checking for User Name for the corresponding person ID of a ADMIN
1156
1157 OPEN c_user_name_admin (l_person_id_app);
1158 FETCH c_user_name_admin INTO l_user_name_adm;
1159 CLOSE c_user_name_admin;
1160
1161 /***
1162 -- add this user name to the adhoc role if it is not null and unique
1163
1164 IF ( l_user_name_adm IS NOT NULL ) THEN
1165
1166 OPEN c_dup_user(l_user_name_adm,l_role_name);
1167 FETCH c_dup_user INTO l_dup_user;
1168 CLOSE c_dup_user;
1169
1170 IF l_user_name_adm IS NOT NULL AND l_dup_user = 0 THEN
1171 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1172 role_users => l_user_name_adm);
1173 END IF;
1174
1175 END IF;
1176 ***/
1177 -- Checking for User Name for the corresponding Supervisor
1178
1179 OPEN c_user_name (l_person_id_suvisor);
1180 FETCH c_user_name INTO l_user_name_sup;
1181 CLOSE c_user_name;
1182
1183 -- add this user name to the adhoc role if it is not null and unique
1184
1185 IF ( l_user_name_sup IS NOT NULL ) THEN
1186
1187 OPEN c_dup_user(l_user_name_sup,l_role_name);
1188 FETCH c_dup_user INTO l_dup_user;
1189 CLOSE c_dup_user;
1190
1191 IF l_user_name_sup IS NOT NULL AND l_dup_user = 0 THEN
1192 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1193 role_users => l_user_name_sup);
1194 END IF;
1195
1196 END IF;
1197
1198 -- now set this role to the workflow
1199 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1200 ItemKey => itemkey,
1201 aname => 'IA_ROLE',
1202 avalue => l_role_name
1203 );
1204
1205
1206
1207 IF (l_user_name_stu IS NULL AND l_user_name_sup IS NULL ) THEN
1208
1209 -- Setting this full name of the student
1210
1211 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1212 ItemKey => itemkey,
1213 aname => 'IA_PERSON1',
1214 avalue => l_c_full_name.full_name
1215 );
1216
1217
1218
1219 -- Setting this full name of the Supervisor
1220
1221 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1222 ItemKey => itemkey,
1223 aname => 'IA_PERSON2',
1224 avalue => l_c_full_name_sup.full_name
1225 );
1226
1227 -- Add the admin to the role
1228 IF ( l_user_name_adm IS NOT NULL ) THEN
1229
1230 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1231 ItemKey => itemkey,
1232 aname => 'IA_ADMIN_USER',
1233 avalue => l_user_name_adm
1234 );
1235 END IF;
1236
1237 resultout := 'COMPLETE:ADMIN' ;
1238 RETURN;
1239
1240 ELSIF (l_user_name_stu IS NOT NULL AND l_user_name_sup IS NULL ) THEN
1241
1242 -- Setting this full name of the Supervisor
1243
1244 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1245 ItemKey => itemkey,
1246 aname => 'IA_PERSON1',
1247 avalue => l_c_full_name_sup.full_name
1248 );
1249
1250 -- Add the admin to the role
1251 IF ( l_user_name_adm IS NOT NULL ) THEN
1252
1253 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1254 ItemKey => itemkey,
1255 aname => 'IA_ADMIN_USER',
1256 avalue => l_user_name_adm
1257 );
1258
1259 END IF;
1260
1261
1262 resultout := 'COMPLETE:BOTH' ;
1263 RETURN;
1264
1265 ELSIF (l_user_name_stu IS NULL AND l_user_name_sup IS NOT NULL ) THEN
1266
1267 -- Setting this full name of the student
1268
1269 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1270 ItemKey => itemkey,
1271 aname => 'IA_PERSON1',
1272 avalue => l_c_full_name.full_name
1273 );
1274
1275 -- Add the admin to the role
1276 IF ( l_user_name_adm IS NOT NULL ) THEN
1277
1278 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1279 ItemKey => itemkey,
1280 aname => 'IA_ADMIN_USER',
1281 avalue => l_user_name_adm
1282 );
1283 END IF;
1284
1285 resultout := 'COMPLETE:BOTH' ;
1286 RETURN;
1287
1288
1289 ELSIF (l_user_name_stu IS NOT NULL AND l_user_name_sup IS NOT NULL ) THEN
1290
1291 -- Add the admin to the role
1292 IF ( l_user_name_adm IS NOT NULL ) THEN
1293
1294 OPEN c_dup_user(l_user_name_adm,l_role_name);
1295 FETCH c_dup_user INTO l_dup_user;
1296 CLOSE c_dup_user;
1297
1298 IF l_user_name_adm IS NOT NULL AND l_dup_user = 0 THEN
1299 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1300 role_users => l_user_name_adm);
1301 END IF;
1302
1303 END IF;
1304
1305 resultout := 'COMPLETE:ALL' ;
1306 RETURN;
1307
1308 END IF;
1309
1310
1311 Resultout:= 'COMPLETE:';
1312 RETURN;
1313
1314 END IF;
1315
1316 END supervision_not;
1317
1318 PROCEDURE overduesub_not (
1319 itemtype IN VARCHAR2 ,
1320 itemkey IN VARCHAR2 ,
1321 actid IN NUMBER ,
1322 funcmode IN VARCHAR2 ,
1323 resultout OUT NOCOPY VARCHAR2 ) AS
1324
1325 ------------------------------------------------------------------------------------------------
1326 -- Created by : Deepankar Dey, Oracle India (in)
1327 -- Date created: 30-04-2003
1328 --
1329 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
1330 --
1331 --
1332 --
1333 -- Known limitations/enhancements and/or remarks:
1334 --
1335 -- Change History:
1336 -- Who When What
1337 --
1338 -------------------------------------------------------------------------------------------------
1339
1340 l_date_prod VARCHAR2(30);
1341 l_doc_type VARCHAR2(30);
1342 l_role_name VARCHAR2(320);
1343 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSRE004';
1344 l_person_id_stu VARCHAR2(50);
1345 l_person_id_suvisor VARCHAR2(50);
1346 l_person_id_app VARCHAR2(50);
1347 l_person_id VARCHAR2(30);
1348
1349 -- cursor to get the user_name corresponding to the person_id
1350
1351 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
1352 SELECT user_name
1353 FROM fnd_user
1354 WHERE person_party_id = cp_person_id
1355 AND ( end_date IS NULL OR end_date > SYSDATE );
1356
1357
1358 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
1359 SELECT user_name
1360 FROM fnd_user
1361 WHERE user_id = cp_person_id
1362 AND ( end_date IS NULL OR end_date > SYSDATE );
1363
1364 l_user_name_stu fnd_user.user_name%TYPE;
1365 l_user_name_adm fnd_user.user_name%TYPE;
1366 l_user_name fnd_user.user_name%TYPE;
1367 l_sup_name VARCHAR2(32000);
1368
1369 CURSOR c_dup_user (cp_user_name VARCHAR2,
1370 cp_role_name VARCHAR2) IS
1371 SELECT count(1)
1372 FROM WF_LOCAL_USER_ROLES
1373 WHERE USER_NAME = cp_user_name
1374 AND ROLE_NAME = cp_role_name
1375 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
1376 AND ROLE_ORIG_SYSTEM_ID = 0;
1377
1378 l_dup_user NUMBER :=0;
1379
1380 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1381 SELECT full_name
1382 FROM igs_pe_person_base_v
1383 WHERE person_id = cp_person_id;
1384
1385 l_c_full_name c_full_name%ROWTYPE;
1386
1387 CURSOR c_mesg_text (cp_message_name fnd_new_messages.message_name%TYPE) IS
1388 SELECT message_text
1389 FROM fnd_new_messages
1390 WHERE message_name = cp_message_name;
1391
1392 l_c_mesg_text c_mesg_text%ROWTYPE;
1393
1394 l_supervisor_flag VARCHAR2(5) := 'TRUE';
1395 BEGIN
1396
1397
1398
1399 IF (funcmode = 'RUN') THEN
1400 -- create the adhoc role
1401 l_role_name := 'IGS'||substr(itemkey,6);
1402
1403 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
1404 role_display_name => l_role_display_name
1405 );
1406
1407
1408 --
1409 -- fetch student for whom the record has been procesed and add the user name to the
1410 -- adhoc role
1411 --
1412 --
1413
1414 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
1415 l_person_id_suvisor := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_SUPRVSR');
1416 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
1417
1418 -- Getting the Full Name of the Student
1419
1420 OPEN c_full_name(l_person_id_stu);
1421 FETCH c_full_name INTO l_c_full_name;
1422 CLOSE c_full_name;
1423
1424 -- Setting this full name of the student
1425
1426 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1427 ItemKey => itemkey,
1428 aname => 'IA_PERSONNAME',
1429 avalue => l_c_full_name.full_name
1430 );
1431
1432 -- Checking for User Name for the corresponding person ID of a student
1433
1434 OPEN c_user_name (l_person_id_stu);
1435 FETCH c_user_name INTO l_user_name_stu;
1436 CLOSE c_user_name;
1437
1438 -- add this user name to the adhoc role if it is not null and unique
1439
1440
1441 IF ( l_user_name_stu IS NOT NULL ) THEN
1442
1443 OPEN c_dup_user(l_user_name_stu,l_role_name);
1444 FETCH c_dup_user INTO l_dup_user;
1445 CLOSE c_dup_user;
1446
1447 IF l_user_name_stu IS NOT NULL AND l_dup_user = 0 THEN
1448 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1449 role_users => l_user_name_stu);
1450 END IF;
1451 END IF;
1452
1453
1454 -- Checking for User Name for the corresponding person ID of a ADMIN
1455
1456 OPEN c_user_name_admin (l_person_id_app);
1457 FETCH c_user_name_admin INTO l_user_name_adm;
1458 CLOSE c_user_name_admin;
1459
1460 -- Checking for User Name for the corresponding Supervisor
1461
1462 WHILE (LENGTH (l_person_id_suvisor) > 0)
1463 LOOP
1464
1465 -- getting the person ID for supervisor from the concatenated string
1466 IF (INSTR (l_person_id_suvisor, ',') > 0) THEN
1467 l_person_id := SUBSTR (l_person_id_suvisor, 1, INSTR (l_person_id_suvisor, ',') - 1);
1468 l_person_id_suvisor := SUBSTR (l_person_id_suvisor, INSTR (l_person_id_suvisor, ',') + 1);
1469
1470 ELSE
1471
1472 l_person_id := l_person_id_suvisor;
1473 l_person_id_suvisor := NULL;
1474
1475 END IF;
1476 l_user_name := NULL;
1477 OPEN c_user_name (l_person_id);
1478 FETCH c_user_name INTO l_user_name;
1479 CLOSE c_user_name;
1480
1481 -- IF the l_user_name is null
1482 IF l_user_name IS NULL THEN
1483 -- Getting the full name of the Supervisor
1484 OPEN c_full_name(l_person_id);
1485 FETCH c_full_name INTO l_c_full_name;
1486 CLOSE c_full_name;
1487
1488 IF l_supervisor_flag = 'FALSE' THEN
1489 l_sup_name := l_sup_name || ', ';
1490 ELSE
1491 l_supervisor_flag := 'FALSE';
1492 END IF;
1493
1494 l_sup_name := l_sup_name || l_c_full_name.full_name;
1495
1496 ELSE
1497 -- add this user name to the adhoc role if it is not null and unique
1498 OPEN c_dup_user(l_user_name,l_role_name);
1499 FETCH c_dup_user INTO l_dup_user;
1500 CLOSE c_dup_user;
1501
1502 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
1503 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1504 role_users => l_user_name);
1505 END IF;
1506 END IF;
1507
1508 END LOOP;
1509
1510
1511 -- Setting the Adhoc Role Attribute
1512
1513 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1514 ItemKey => itemkey,
1515 aname => 'IA_ROLE',
1516 avalue => l_role_name);
1517
1518
1519 IF (l_user_name_stu IS NULL AND l_supervisor_flag = 'FALSE' ) THEN
1520
1521 -- Setting this full name of the student
1522 OPEN c_full_name(l_person_id_stu);
1523 FETCH c_full_name INTO l_c_full_name;
1524 CLOSE c_full_name;
1525
1526 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1527 ItemKey => itemkey,
1528 aname => 'IA_PERSON1',
1529 avalue => l_c_full_name.full_name
1530 );
1531
1532
1533
1534 -- Setting this full name of the Supervisor
1535
1536 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1537 ItemKey => itemkey,
1538 aname => 'IA_PERSON2',
1539 avalue => l_sup_name
1540 );
1541
1542 -- setting admin to IA for performer of admin notification
1543 IF ( l_user_name_adm IS NOT NULL ) THEN
1544
1545 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1546 ItemKey => itemkey,
1547 aname => 'IA_ADMIN_USER',
1548 avalue => l_user_name_adm
1549 );
1550
1551 END IF;
1552
1553 resultout := 'COMPLETE:ADMIN' ;
1554 RETURN;
1555
1556 ELSIF (l_user_name_stu IS NOT NULL AND l_supervisor_flag = 'FALSE' ) THEN
1557
1558 -- Setting this full name of the Supervisor
1559
1560 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1561 ItemKey => itemkey,
1562 aname => 'IA_PERSON1',
1563 avalue => l_sup_name
1564 );
1565
1566 -- setting admin to IA for performer of admin notification
1567 IF ( l_user_name_adm IS NOT NULL ) THEN
1568
1569 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1570 ItemKey => itemkey,
1571 aname => 'IA_ADMIN_USER',
1572 avalue => l_user_name_adm
1573 );
1574
1575 END IF;
1576
1577 resultout := 'COMPLETE:BOTH' ;
1578 RETURN;
1579
1580 ELSIF (l_user_name_stu IS NULL AND l_supervisor_flag = 'TRUE' ) THEN
1581
1582 -- Setting this full name of the student
1583
1584 OPEN c_full_name(l_person_id_stu);
1585 FETCH c_full_name INTO l_c_full_name;
1586 CLOSE c_full_name;
1587
1588 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1589 ItemKey => itemkey,
1590 aname => 'IA_PERSON1',
1591 avalue => l_c_full_name.full_name
1592 );
1593
1594 -- setting admin to IA for performer of admin notification
1595 IF ( l_user_name_adm IS NOT NULL ) THEN
1596
1597 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1598 ItemKey => itemkey,
1599 aname => 'IA_ADMIN_USER',
1600 avalue => l_user_name_adm
1601 );
1602 END IF;
1603
1604 resultout := 'COMPLETE:BOTH' ;
1605 RETURN;
1606
1607 ELSIF (l_user_name_stu IS NOT NULL AND l_supervisor_flag = 'TRUE') THEN
1608
1609 -- adding admin to the role
1610 IF ( l_user_name_adm IS NOT NULL ) THEN
1611
1612 OPEN c_dup_user(l_user_name_adm,l_role_name);
1613 FETCH c_dup_user INTO l_dup_user;
1614 CLOSE c_dup_user;
1615
1616 IF l_user_name_adm IS NOT NULL AND l_dup_user = 0 THEN
1617 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1618 role_users => l_user_name_adm);
1619 END IF;
1620
1621 END IF;
1622
1623 resultout := 'COMPLETE:ALL' ;
1624 RETURN;
1625
1626 END IF;
1627
1628 Resultout:= 'COMPLETE:';
1629 RETURN;
1630
1631 END IF;
1632
1633 END overduesub_not;
1634
1635 PROCEDURE milestone_event(
1636 p_personid IN NUMBER ,
1637 p_ca_seq_num IN NUMBER ,
1638 p_milestn_typ IN VARCHAR2 ,
1639 p_milestn_stat IN VARCHAR2 ,
1640 p_due_dt IN DATE ,
1641 p_dt_reached IN DATE ,
1642 p_deleted IN VARCHAR2
1643 ) AS
1644 ------------------------------------------------------------------------------------------------
1645 -- Created by : Deepankar Dey, Oracle India (in)
1646 -- Date created: 05-Sept-2003
1647 --
1648 -- Purpose: The changes are done as per the Enrollments Notifications TD Bug # 3052429
1649 -- The procedure raises the milestone event.
1650 --
1651 --
1652 -- Known limitations/enhancements and/or remarks:
1653 --
1654 -- Change History:
1655 -- Who When What
1656 --
1657 -------------------------------------------------------------------------------------------------
1658 l_event_t wf_event_t;
1659 l_parameter_list_t wf_parameter_list_t;
1660 l_itemKey varchar2(100);
1661 ln_seq_val NUMBER;
1662 l_programcd igs_re_candidature.sca_course_cd%TYPE;
1663 l_res_topic igs_re_candidature.research_topic%TYPE;
1664
1665 -- Gets a unique sequence number
1666
1667 CURSOR c_seq_num IS
1668 SELECT igs_re_milstn_s.NEXTVAL
1669 FROM dual;
1670
1671
1672 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
1673
1674 CURSOR cur_prof_value IS
1675 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
1676 FROM dual;
1677
1678 -- Getting the logged in User
1679
1680 CURSOR cur_user_id IS
1681 SELECT FND_GLOBAL.USER_ID user_id
1682 FROM dual;
1683
1684 --
1685 CURSOR cur_prog_ver ( p_person_id igs_pe_person.person_id%TYPE,
1686 p_course_cd igs_ps_ver.course_cd%TYPE)IS
1687 SELECT course_cd , version_number, attendance_type
1688 FROM igs_en_stdnt_ps_att
1689 WHERE person_id = p_person_id
1690 AND course_cd = p_course_cd ;
1691
1692 CURSOR cur_def_milestone ( p_course_cd igs_re_dflt_ms_set.course_cd%TYPE,
1693 p_version_number igs_re_dflt_ms_set.version_number%TYPE,
1694 p_attendance_type igs_re_dflt_ms_set.attendance_type%TYPE
1695 ) IS
1696 SELECT 1
1697 FROM igs_re_dflt_ms_set
1698 WHERE
1699 course_cd = p_course_cd AND
1700 version_number = p_version_number AND
1701 attendance_type = p_attendance_type ;
1702
1703
1704 l_cur_prof_value cur_prof_value%ROWTYPE;
1705 l_cur_user_id cur_user_id%ROWTYPE;
1706 l_cur_prog_ver cur_prog_ver%ROWTYPE;
1707 ln_def_milestone NUMBER;
1708 l_source VARCHAR2(100);
1709 l_supervisor VARCHAR2(32000);
1710
1711
1712 BEGIN
1713
1714 -- Checking if the Workflow is installed at the environment or not.
1715
1716
1717 OPEN cur_prof_value;
1718 FETCH cur_prof_value INTO l_cur_prof_value;
1719 CLOSE cur_prof_value;
1720
1721 IF (l_cur_prof_value.value = 'Y') THEN
1722
1723
1724 -- Get the sequence value
1725
1726 OPEN c_seq_num;
1727 FETCH c_seq_num INTO ln_seq_val ;
1728 CLOSE c_seq_num ;
1729
1730 -- Getting the Logged on User
1731
1732 OPEN cur_user_id ;
1733 FETCH cur_user_id INTO l_cur_user_id ;
1734 CLOSE cur_user_id ;
1735
1736 --
1737 -- initialize the wf_event_t object
1738 --
1739
1740 wf_event_t.Initialize(l_event_t);
1741
1742
1743
1744
1745 OPEN c_research(p_personid,p_ca_seq_num);
1746 FETCH c_research INTO l_programcd,l_res_topic;
1747 CLOSE c_research;
1748
1749 OPEN cur_prog_ver(p_personid,l_programcd);
1750 FETCH cur_prog_ver INTO l_cur_prog_ver;
1751 CLOSE cur_prog_ver;
1752
1753 OPEN cur_def_milestone(l_programcd,l_cur_prog_ver.version_number,l_cur_prog_ver.attendance_type);
1754 FETCH cur_def_milestone INTO ln_def_milestone ;
1755 CLOSE cur_def_milestone ;
1756
1757
1758 IF (ln_def_milestone = 1) THEN
1759 l_source := 'DEFAULT';
1760 ELSE
1761 l_source := 'MANUAL';
1762 END IF;
1763
1764
1765 igs_re_workflow.get_supervisor(
1766 p_personid => p_personid ,
1767 p_ca_sequence_number => p_ca_seq_num,
1768 p_supervisor => l_supervisor
1769 );
1770
1771
1772 --
1773 -- Adding the parameters to the parameter list
1774 --
1775
1776 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
1777 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => l_programcd, p_ParameterList => l_parameter_list_t);
1778 wf_event.AddParameterToList (p_name => 'P_RESTOPIC', p_Value => l_res_topic, p_ParameterList => l_parameter_list_t);
1779 wf_event.AddParameterToList (p_name => 'P_MILESTN_TYP', p_Value => p_milestn_typ , p_ParameterList => l_parameter_list_t);
1780 wf_event.AddParameterToList (p_name => 'P_DUEDT', p_Value => p_due_dt , p_ParameterList => l_parameter_list_t);
1781 wf_event.AddParameterToList (p_name => 'P_MILESTN_STAT', p_Value => p_milestn_stat, p_ParameterList => l_parameter_list_t);
1782 wf_event.AddParameterToList (p_name => 'P_DT_REACHED', p_Value => p_dt_reached, p_ParameterList => l_parameter_list_t);
1783 wf_event.AddParameterToList (p_name => 'P_MILESTN_SRC', p_Value => l_source, p_ParameterList => l_parameter_list_t);
1784 wf_event.AddParameterToList (p_name => 'P_NOMI_ATT_TYP', p_Value => l_cur_prog_ver.attendance_type, p_ParameterList => l_parameter_list_t);
1785 wf_event.AddParameterToList (p_name => 'P_DELETED', p_Value => p_deleted, p_ParameterList => l_parameter_list_t);
1786 wf_event.AddParameterToList (p_name => 'P_SUPERVISOR', p_Value => l_supervisor, p_ParameterList => l_parameter_list_t);
1787 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
1788
1789
1790
1791 -- Raise the Event
1792
1793 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.milestone.change',
1794 p_event_key => 'MILESTONEEVENT'||ln_seq_val,
1795 p_parameters => l_parameter_list_t);
1796
1797
1798 --
1799 -- Deleting the Parameter list after the event is raised
1800 --
1801
1802
1803 l_parameter_list_t.delete;
1804
1805 END IF;
1806
1807 END milestone_event;
1808
1809
1810
1811
1812 PROCEDURE thesis_exam_event(
1813 p_personid IN NUMBER ,
1814 p_ca_sequence_number IN NUMBER ,
1815 p_the_sequence_number IN NUMBER ,
1816 p_creation_dt IN DATE ,
1817 p_submission_dt IN DATE ,
1818 p_thesis_exam_type IN VARCHAR2
1819 ) AS
1820 ------------------------------------------------------------------------------------------------
1821 -- Created by : Deepankar Dey, Oracle India (in)
1822 -- Date created: 05-Sept-2003
1823 --
1824 -- Purpose: The changes are done as per the Enrollments Notifications TD Bug # 3052429
1825 -- The procedure raises the milestone event.
1826 --
1827 --
1828 -- Known limitations/enhancements and/or remarks:
1829 --
1830 -- Change History:
1831 -- Who When What
1832 --
1833 -------------------------------------------------------------------------------------------------
1834
1835 l_event_t wf_event_t;
1836 l_parameter_list_t wf_parameter_list_t;
1837 l_itemKey varchar2(100);
1838 ln_seq_val NUMBER;
1839 l_programcd igs_re_candidature.sca_course_cd%TYPE;
1840 l_res_topic igs_re_candidature.research_topic%TYPE;
1841
1842 -- Gets a unique sequence number
1843
1844 CURSOR c_seq_num IS
1845 SELECT igs_re_exmsub_s.NEXTVAL
1846 FROM dual;
1847
1848
1849 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
1850
1851 CURSOR cur_prof_value IS
1852 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
1853 FROM dual;
1854
1855 -- Getting the logged in User
1856
1857 CURSOR cur_user_id IS
1858 SELECT FND_GLOBAL.USER_ID user_id
1859 FROM dual;
1860
1861 --
1862 CURSOR cur_prog_stat ( p_person_id igs_pe_person.person_id%TYPE,
1863 p_course_cd igs_ps_ver.course_cd%TYPE)IS
1864 SELECT course_attempt_status
1865 FROM igs_en_stdnt_ps_att
1866 WHERE person_id = p_person_id
1867 AND course_cd = p_course_cd ;
1868
1869
1870 l_cur_prof_value cur_prof_value%ROWTYPE;
1871 l_cur_user_id cur_user_id%ROWTYPE;
1872 l_cur_prog_stat cur_prog_stat%ROWTYPE;
1873 l_panel_member VARCHAR2(32000);
1874 l_the_topic igs_re_thesis.thesis_topic%TYPE;
1875 l_title igs_re_thesis.title%TYPE;
1876 l_supervisor VARCHAR2(32000);
1877
1878
1879 BEGIN
1880
1881 -- Checking if the Workflow is installed at the environment or not.
1882
1883 OPEN cur_prof_value;
1884 FETCH cur_prof_value INTO l_cur_prof_value;
1885 CLOSE cur_prof_value;
1886
1887 IF (l_cur_prof_value.value = 'Y') THEN
1888
1889 -- Get the sequence value
1890
1891 OPEN c_seq_num;
1892 FETCH c_seq_num INTO ln_seq_val ;
1893 CLOSE c_seq_num ;
1894
1895 -- Getting the Logged on User
1896
1897 OPEN cur_user_id ;
1898 FETCH cur_user_id INTO l_cur_user_id ;
1899 CLOSE cur_user_id ;
1900
1901 --
1902 -- initialize the wf_event_t object
1903 --
1904
1905 wf_event_t.Initialize(l_event_t);
1906
1907
1908 OPEN c_research(p_personid,p_ca_sequence_number);
1909 FETCH c_research INTO l_programcd,l_res_topic;
1910 CLOSE c_research;
1911
1912 OPEN c_thesis ( p_personid,p_ca_sequence_number,p_the_sequence_number);
1913 FETCH c_thesis INTO l_the_topic , l_title;
1914 CLOSE c_thesis ;
1915
1916 OPEN cur_prog_stat(p_personid,l_programcd);
1917 FETCH cur_prog_stat INTO l_cur_prog_stat;
1918 CLOSE cur_prog_stat;
1919
1920
1921 igs_re_workflow.get_panel_member(
1922 p_personid => p_personid,
1923 p_ca_sequence_number => p_ca_sequence_number,
1924 p_the_sequence_number => p_the_sequence_number,
1925 p_creation_dt => p_creation_dt,
1926 p_panel_member => l_panel_member
1927 ) ;
1928
1929
1930 igs_re_workflow.get_supervisor(
1931 p_personid => p_personid ,
1932 p_ca_sequence_number => p_ca_sequence_number,
1933 p_supervisor => l_supervisor
1934 );
1935
1936 --
1937 -- Adding the parameters to the parameter list
1938 --
1939
1940 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
1941 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => l_programcd, p_ParameterList => l_parameter_list_t);
1942 wf_event.AddParameterToList (p_name => 'P_RESTOPIC', p_Value => l_res_topic, p_ParameterList => l_parameter_list_t);
1943 wf_event.AddParameterToList (p_name => 'P_THESIS_TPC', p_Value => l_the_topic , p_ParameterList => l_parameter_list_t);
1944 wf_event.AddParameterToList (p_name => 'P_THESIS_TITLE', p_Value => l_title , p_ParameterList => l_parameter_list_t);
1945 wf_event.AddParameterToList (p_name => 'P_SUB_ON', p_Value => p_submission_dt, p_ParameterList => l_parameter_list_t);
1946 wf_event.AddParameterToList (p_name => 'P_SPA_STAT', p_Value => l_cur_prog_stat.course_attempt_status, p_ParameterList => l_parameter_list_t);
1947 wf_event.AddParameterToList (p_name => 'P_THESIS_EXM_TYP', p_Value => p_thesis_exam_type, p_ParameterList => l_parameter_list_t);
1948 wf_event.AddParameterToList (p_name => 'P_THESIS_EXM_PNL', p_Value => l_panel_member, p_ParameterList => l_parameter_list_t);
1949 wf_event.AddParameterToList (p_name => 'P_SUPERVISOR', p_Value => l_supervisor, p_ParameterList => l_parameter_list_t);
1950 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
1951
1952
1953
1954 -- Raise the Event
1955
1956 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.thesis.examsub',
1957 p_event_key => 'THESISEXAMEVENT'||ln_seq_val,
1958 p_parameters => l_parameter_list_t);
1959
1960 --
1961 -- Deleting the Parameter list after the event is raised
1962 --
1963
1964 l_parameter_list_t.delete;
1965
1966 END IF;
1967
1968 END thesis_exam_event;
1969
1970
1971 PROCEDURE thesis_result_event(
1972 p_personid IN NUMBER ,
1973 p_ca_sequence_number IN NUMBER,
1974 p_the_sequence_number IN NUMBER ,
1975 p_creation_dt IN DATE ,
1976 p_submission_dt IN DATE ,
1977 p_thesis_exam_type IN VARCHAR2 ,
1978 p_thesis_result_cd IN VARCHAR2 ) AS
1979 ------------------------------------------------------------------------------------------------
1980 -- Created by : Deepankar Dey, Oracle India (in)
1981 -- Date created: 05-Sept-2003
1982 --
1983 -- Purpose: The changes are done as per the Enrollments Notifications TD Bug # 3052429
1984 -- The procedure raises the milestone event.
1985 --
1986 --
1987 -- Known limitations/enhancements and/or remarks:
1988 --
1989 -- Change History:
1990 -- Who When What
1991 --
1992 -------------------------------------------------------------------------------------------------
1993
1994 l_event_t wf_event_t;
1995 l_parameter_list_t wf_parameter_list_t;
1996 l_itemKey varchar2(100);
1997 ln_seq_val NUMBER;
1998 l_programcd igs_re_candidature.sca_course_cd%TYPE;
1999 l_res_topic igs_re_candidature.research_topic%TYPE;
2000
2001 -- Gets a unique sequence number
2002
2003 CURSOR c_seq_num IS
2004 SELECT igs_re_resupd_s.NEXTVAL
2005 FROM dual;
2006
2007
2008 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
2009
2010 CURSOR cur_prof_value IS
2011 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
2012 FROM dual;
2013
2014 -- Getting the logged in User
2015
2016 CURSOR cur_user_id IS
2017 SELECT FND_GLOBAL.USER_ID user_id
2018 FROM dual;
2019
2020
2021
2022 l_cur_prof_value cur_prof_value%ROWTYPE;
2023 l_cur_user_id cur_user_id%ROWTYPE;
2024 l_panel_member VARCHAR2(32000);
2025 l_the_topic igs_re_thesis.thesis_topic%TYPE;
2026 l_title igs_re_thesis.title%TYPE;
2027 l_supervisor VARCHAR2(32000);
2028
2029
2030 BEGIN
2031
2032 -- Checking if the Workflow is installed at the environment or not.
2033
2034 OPEN cur_prof_value;
2035 FETCH cur_prof_value INTO l_cur_prof_value;
2036 CLOSE cur_prof_value;
2037
2038 IF (l_cur_prof_value.value = 'Y') THEN
2039
2040 -- Get the sequence value
2041
2042 OPEN c_seq_num;
2043 FETCH c_seq_num INTO ln_seq_val ;
2044 CLOSE c_seq_num ;
2045
2046 -- Getting the Logged on User
2047
2048 OPEN cur_user_id ;
2049 FETCH cur_user_id INTO l_cur_user_id ;
2050 CLOSE cur_user_id ;
2051
2052 --
2053 -- initialize the wf_event_t object
2054 --
2055
2056 wf_event_t.Initialize(l_event_t);
2057
2058
2059 OPEN c_research(p_personid,p_ca_sequence_number);
2060 FETCH c_research INTO l_programcd,l_res_topic;
2061 CLOSE c_research;
2062
2063 OPEN c_thesis ( p_personid,p_ca_sequence_number,p_the_sequence_number);
2064 FETCH c_thesis INTO l_the_topic , l_title;
2065 CLOSE c_thesis ;
2066
2067
2068 igs_re_workflow.get_panel_member(
2069 p_personid => p_personid,
2070 p_ca_sequence_number => p_ca_sequence_number,
2071 p_the_sequence_number => p_the_sequence_number,
2072 p_creation_dt => p_creation_dt,
2073 p_panel_member => l_panel_member
2074 ) ;
2075
2076
2077 igs_re_workflow.get_supervisor(
2078 p_personid => p_personid ,
2079 p_ca_sequence_number => p_ca_sequence_number,
2080 p_supervisor => l_supervisor
2081 );
2082
2083
2084 --
2085 -- Adding the parameters to the parameter list
2086 --
2087
2088 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
2089 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => l_programcd, p_ParameterList => l_parameter_list_t);
2090 wf_event.AddParameterToList (p_name => 'P_RESTOPIC', p_Value => l_res_topic, p_ParameterList => l_parameter_list_t);
2091 wf_event.AddParameterToList (p_name => 'P_THESIS_TPC', p_Value => l_the_topic , p_ParameterList => l_parameter_list_t);
2092 wf_event.AddParameterToList (p_name => 'P_THESIS_TITLE', p_Value => l_title , p_ParameterList => l_parameter_list_t);
2093 wf_event.AddParameterToList (p_name => 'P_SUB_ON', p_Value => p_submission_dt, p_ParameterList => l_parameter_list_t);
2094 wf_event.AddParameterToList (p_name => 'P_THESIS_RSLT', p_Value => p_thesis_result_cd, p_ParameterList => l_parameter_list_t);
2095 wf_event.AddParameterToList (p_name => 'P_THESIS_EXM_TYP', p_Value => p_thesis_exam_type, p_ParameterList => l_parameter_list_t);
2096 wf_event.AddParameterToList (p_name => 'P_THESIS_EXM_PNL', p_Value => l_panel_member, p_ParameterList => l_parameter_list_t);
2097 wf_event.AddParameterToList (p_name => 'P_SUPERVISOR', p_Value => l_supervisor, p_ParameterList => l_parameter_list_t);
2098 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
2099
2100
2101 -- Raise the Event
2102
2103
2104 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.thesis.resultupd',
2105 p_event_key => 'THESISRESULTEVENT'||ln_seq_val,
2106 p_parameters => l_parameter_list_t);
2107
2108 --
2109 -- Deleting the Parameter list after the event is raised
2110 --
2111
2112 l_parameter_list_t.delete;
2113
2114 END IF;
2115
2116 END thesis_result_event;
2117
2118 PROCEDURE confirm_reg_event (
2119 p_personid IN NUMBER ,
2120 p_programcd IN VARCHAR2,
2121 p_spa_start_dt IN DATE ,
2122 p_prog_attempt_stat IN VARCHAR2
2123
2124 ) AS
2125 ------------------------------------------------------------------------------------------------
2126 -- Created by : Deepankar Dey, Oracle India (in)
2127 -- Date created: 05-Sept-2003
2128 --
2129 -- Purpose: The changes are done as per the Enrollments Notifications TD Bug # 3052429
2130 -- The procedure raises the milestone event.
2131 --
2132 --
2133 -- Known limitations/enhancements and/or remarks:
2134 --
2135 -- Change History:
2136 -- Who When What
2137 --
2138 -------------------------------------------------------------------------------------------------
2139
2140 l_event_t wf_event_t;
2141 l_parameter_list_t wf_parameter_list_t;
2142 l_itemKey varchar2(100);
2143 ln_seq_val NUMBER;
2144 l_programcd igs_re_candidature.sca_course_cd%TYPE;
2145 l_res_topic igs_re_candidature.research_topic%TYPE;
2146
2147 -- Gets a unique sequence number
2148
2149 CURSOR c_seq_num IS
2150 SELECT igs_re_regcfm_s.NEXTVAL
2151 FROM dual;
2152
2153
2154 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
2155
2156 CURSOR cur_prof_value IS
2157 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
2158 FROM dual;
2159
2160 -- Getting the logged in User
2161
2162 CURSOR cur_user_id IS
2163 SELECT FND_GLOBAL.USER_ID user_id
2164 FROM dual;
2165
2166
2167 CURSOR cur_res_top (p_personid igs_pe_person_base_v.person_id%TYPE,
2168 p_programcd igs_ps_ver.course_cd%TYPE ) IS
2169 SELECT research_topic,sequence_number
2170 FROM igs_re_candidature
2171 WHERE person_id = p_personid
2172 AND sca_course_cd = p_programcd ;
2173
2174 CURSOR cur_course_type (p_programcd igs_ps_ver.course_cd%TYPE ) IS
2175 SELECT course_type
2176 FROM igs_ps_ver
2177 WHERE course_cd=p_programcd ;
2178
2179 l_cur_res_top cur_res_top%ROWTYPE;
2180 l_cur_course_type cur_course_type%ROWTYPE;
2181 l_supervisor VARCHAR2(32000);
2182 l_cur_prof_value cur_prof_value%ROWTYPE;
2183 l_cur_user_id cur_user_id%ROWTYPE;
2184
2185 BEGIN
2186
2187 -- Checking if the Workflow is installed at the environment or not.
2188
2189 OPEN cur_prof_value;
2190 FETCH cur_prof_value INTO l_cur_prof_value;
2191 CLOSE cur_prof_value;
2192
2193 IF (l_cur_prof_value.value = 'Y') THEN
2194
2195 -- Get the sequence value
2196
2197 OPEN c_seq_num;
2198 FETCH c_seq_num INTO ln_seq_val ;
2199 CLOSE c_seq_num ;
2200
2201 -- Getting the Logged on User
2202
2203 OPEN cur_user_id ;
2204 FETCH cur_user_id INTO l_cur_user_id ;
2205 CLOSE cur_user_id ;
2206
2207 --
2208 -- initialize the wf_event_t object
2209 --
2210
2211 wf_event_t.Initialize(l_event_t);
2212
2213
2214
2215 OPEN cur_res_top(p_personid,p_programcd);
2216 FETCH cur_res_top INTO l_cur_res_top;
2217 CLOSE cur_res_top;
2218
2219 OPEN cur_course_type(p_programcd);
2220 FETCH cur_course_type INTO l_cur_course_type;
2221 CLOSE cur_course_type;
2222
2223 igs_re_workflow.get_supervisor(
2224 p_personid => p_personid ,
2225 p_ca_sequence_number => l_cur_res_top.sequence_number,
2226 p_supervisor => l_supervisor
2227 );
2228
2229 --
2230 -- Adding the parameters to the parameter list
2231 --
2232
2233 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
2234 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => p_programcd, p_ParameterList => l_parameter_list_t);
2235 wf_event.AddParameterToList (p_name => 'P_RESTOPIC', p_Value => l_cur_res_top.research_topic, p_ParameterList => l_parameter_list_t);
2236 wf_event.AddParameterToList (p_name => 'P_SPA_START_DT', p_Value => p_spa_start_dt , p_ParameterList => l_parameter_list_t);
2237 wf_event.AddParameterToList (p_name => 'P_PROGRAM_TYP', p_Value => l_cur_course_type.course_type, p_ParameterList => l_parameter_list_t);
2238 wf_event.AddParameterToList (p_name => 'P_PROGRAMSTAT', p_Value => p_prog_attempt_stat, p_ParameterList => l_parameter_list_t);
2239 wf_event.AddParameterToList (p_name => 'P_SUPERVISOR', p_Value => l_supervisor, p_ParameterList => l_parameter_list_t);
2240 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
2241
2242
2243 -- Raise the Event
2244
2245 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.registration.confirm',
2246 p_event_key => 'CONFIRMREGEVENT'||ln_seq_val,
2247 p_parameters => l_parameter_list_t);
2248
2249 --
2250 -- Deleting the Parameter list after the event is raised
2251 --
2252
2253 l_parameter_list_t.delete;
2254
2255 END IF;
2256
2257 END confirm_reg_event;
2258
2259 PROCEDURE milstn_notify_prcs ( ERRBUF OUT NOCOPY VARCHAR2 ,
2260 RETCODE OUT NOCOPY NUMBER ) AS
2261 ------------------------------------------------------------------------------------------------
2262 -- Created by : Deepankar Dey, Oracle India (in)
2263 -- Date created: 05-Sept-2003
2264 --
2265 -- Purpose: The changes are done as per the Enrollments Notifications TD Bug # 3052429
2266 -- The procedure raises the milestone event.
2267 --
2268 --
2269 -- Known limitations/enhancements and/or remarks:
2270 --
2271 -- Change History:
2272 -- Who When What
2273 -- ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
2274 -------------------------------------------------------------------------------------------------
2275
2276 -- Gets a unique sequence number
2277
2278 CURSOR c_seq_num IS
2279 SELECT igs_re_milnot_s.NEXTVAL
2280 FROM dual;
2281
2282
2283 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
2284
2285 CURSOR cur_prof_value IS
2286 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
2287 FROM dual;
2288
2289 -- Getting the logged in User
2290
2291 CURSOR cur_user_id IS
2292 SELECT FND_GLOBAL.USER_ID user_id
2293 FROM dual;
2294
2295 -- Identifying all the student program attempts which are not in the program attempt status of 'DISCONTIN' or 'LAPSED'
2296 -- and have a research record.
2297
2298 CURSOR cur_prog_atmpt IS
2299 SELECT spa.person_id, spa.course_cd, re.sequence_number, spa.attendance_type, re.research_topic
2300 FROM igs_en_stdnt_ps_att spa, igs_re_candidature re
2301 WHERE spa.person_id = re.person_id
2302 AND spa.course_cd = re.sca_course_cd
2303 AND spa.course_attempt_status NOT IN ('LAPSED','DISCONTIN');
2304
2305 -- For each person program attempt identified above select the milestone records, which do not have the
2306 -- status of 'ACHIEVED' and the due date is less than SYSDATE.
2307
2308 CURSOR cur_achived_milestone ( p_person_id igs_pe_person_base_v.person_id%TYPE,
2309 p_seq_num igs_pr_milestone.ca_sequence_number%TYPE
2310 )IS
2311 SELECT a.due_dt, NVL(ovrd_ntfctn_imminent_days, b.NTFCTN_IMMINENT_DAYS) ovrd_ntfctn_imminent_days,
2312 nvl(ovrd_ntfctn_reminder_days, b.NTFCTN_REMINDER_DAYS) ovrd_ntfctn_reminder_days,
2313 nvl(ovrd_ntfctn_re_reminder_days, b.NTFCTN_RE_REMINDER_DAYS) ovrd_ntfctn_re_reminder_days, a.milestone_status, a.milestone_type
2314 FROM igs_pr_milestone a,
2315 igs_pr_milestone_typ b
2316 WHERE a.person_id = p_person_id
2317 AND a.ca_sequence_number = p_seq_num
2318 AND a.milestone_status <> 'ACHIEVED'
2319 AND TRUNC(a.due_dt) >= TRUNC(SYSDATE)
2320 AND a.milestone_type = b.milestone_type ;
2321
2322
2323 l_cur_achived_milestone cur_achived_milestone%ROWTYPE;
2324 l_cur_prog_atmpt cur_prog_atmpt%ROWTYPE;
2325 l_notifydays NUMBER;
2326 l_notifytype VARCHAR2(30);
2327 l_supervisor VARCHAR2(32000);
2328 l_cur_prof_value cur_prof_value%ROWTYPE;
2329 l_cur_user_id cur_user_id%ROWTYPE;
2330 ln_seq_val NUMBER;
2331 l_event_t wf_event_t;
2332 l_parameter_list_t wf_parameter_list_t;
2333 l_itemKey varchar2(100);
2334 l_flag NUMBER := 0;
2335
2336 BEGIN
2337
2338 igs_ge_gen_003.set_org_id(NULL);
2339 retcode := 0;
2340 SAVEPOINT sp_milstn_notify_prcs;
2341
2342 -- Checking if the Workflow is installed at the environment or not.
2343
2344 OPEN cur_prof_value;
2345 FETCH cur_prof_value INTO l_cur_prof_value;
2346 CLOSE cur_prof_value;
2347
2348
2349 IF (l_cur_prof_value.value = 'Y') THEN
2350
2351
2352 -- Getting the Logged on User
2353
2354 OPEN cur_user_id ;
2355 FETCH cur_user_id INTO l_cur_user_id ;
2356 CLOSE cur_user_id ;
2357
2358
2359
2360 FOR l_cur_prog_atmpt IN cur_prog_atmpt LOOP
2361
2362
2363 igs_re_workflow.get_supervisor(
2364 p_personid => l_cur_prog_atmpt.person_id ,
2365 p_ca_sequence_number => l_cur_prog_atmpt.sequence_number ,
2366 p_supervisor => l_supervisor
2367 );
2368
2369
2370 FOR l_cur_achived_milestone IN cur_achived_milestone(l_cur_prog_atmpt.person_id,l_cur_prog_atmpt.sequence_number) LOOP
2371
2372
2373 --
2374 -- initialize the wf_event_t object
2375 --
2376
2377
2378 wf_event_t.Initialize(l_event_t);
2379
2380 -- Get the sequence value
2381
2382 OPEN c_seq_num;
2383 FETCH c_seq_num INTO ln_seq_val ;
2384 CLOSE c_seq_num ;
2385
2386 l_flag := 0;
2387
2388 IF ( TRUNC(l_cur_achived_milestone.due_dt) = TRUNC(SYSDATE) ) THEN
2389
2390 l_notifydays:=0 ;
2391 l_notifytype:='DUE' ;
2392 l_flag := 1;
2393
2394 ELSIF ( (TRUNC(l_cur_achived_milestone.due_dt) - NVL(l_cur_achived_milestone.ovrd_ntfctn_re_reminder_days,0) ) = TRUNC(SYSDATE) ) THEN
2395
2396 l_notifydays := NVL(l_cur_achived_milestone.ovrd_ntfctn_re_reminder_days,0) ;
2397 l_notifytype := 'REREMIND';
2398 l_flag := 1;
2399
2400 ELSIF ((TRUNC(l_cur_achived_milestone.due_dt) - NVL(l_cur_achived_milestone.ovrd_ntfctn_reminder_days,0)) = TRUNC(SYSDATE)) THEN
2401
2402 l_notifydays := NVL(l_cur_achived_milestone.ovrd_ntfctn_reminder_days,0) ;
2403 l_notifytype := 'REMIND';
2404 l_flag := 1;
2405
2406 ELSIF ((TRUNC(l_cur_achived_milestone.due_dt) - NVL(l_cur_achived_milestone.ovrd_ntfctn_imminent_days,0)) = TRUNC(SYSDATE) ) THEN
2407
2408 l_notifydays := NVL(l_cur_achived_milestone.ovrd_ntfctn_imminent_days,0) ;
2409 l_notifytype := 'IMMI';
2410 l_flag := 1;
2411
2412 END IF;
2413
2414 IF ( l_flag = 1 ) THEN
2415
2416 --
2417 -- Adding the parameters to the parameter list
2418 --
2419
2420 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>l_cur_prog_atmpt.person_id,p_parameterlist=>l_parameter_list_t);
2421 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => l_cur_prog_atmpt.course_cd, p_ParameterList => l_parameter_list_t);
2422 wf_event.AddParameterToList (p_name => 'P_RESTOPIC', p_Value => l_cur_prog_atmpt.research_topic, p_ParameterList => l_parameter_list_t);
2423 wf_event.AddParameterToList (p_name => 'P_MILESTN_TYP', p_Value => l_cur_achived_milestone.milestone_type , p_ParameterList => l_parameter_list_t);
2424 wf_event.AddParameterToList (p_name => 'P_DUEDT', p_Value => l_cur_achived_milestone.due_dt , p_ParameterList => l_parameter_list_t);
2425 wf_event.AddParameterToList (p_name => 'P_MILESTN_STAT', p_Value => l_cur_achived_milestone.milestone_status, p_ParameterList => l_parameter_list_t);
2426 wf_event.AddParameterToList (p_name => 'P_NOTIFY_DAYS', p_Value => l_notifydays, p_ParameterList => l_parameter_list_t);
2427 wf_event.AddParameterToList (p_name => 'P_NOTIFYTYPE', p_Value => l_notifytype, p_ParameterList => l_parameter_list_t);
2428 wf_event.AddParameterToList (p_name => 'P_NOMI_ATT_TYP', p_Value => l_cur_prog_atmpt.attendance_type, p_ParameterList => l_parameter_list_t);
2429 wf_event.AddParameterToList (p_name => 'P_SUPERVISOR', p_Value => l_supervisor, p_ParameterList => l_parameter_list_t);
2430 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
2431
2432
2433
2434 -- Raise the Event
2435
2436 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.re.milestone.notify' ,
2437 p_event_key => 'MILSTNNOTIFYPRCS'||ln_seq_val,
2438 p_parameters => l_parameter_list_t);
2439
2440 --
2441 -- Deleting the Parameter list after the event is raised
2442 --
2443
2444 l_parameter_list_t.delete;
2445
2446 END IF;
2447
2448 END LOOP;
2449
2450 END LOOP;
2451
2452 END IF;
2453
2454
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 ROLLBACK TO sp_milstn_notify_prcs;
2458 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2459 retcode := 2;
2460 FND_MESSAGE.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2461 FND_MESSAGE.SET_TOKEN ('NAME', 'igs_re_workflow.milstn_notify_prcs(): '
2462 || SUBSTR (SQLERRM,1,80));
2463 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.Get);
2464 IGS_GE_MSG_STACK.ADD;
2465 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2466
2467
2468 END milstn_notify_prcs;
2469
2470
2471 PROCEDURE create_adhoc_role (
2472 itemtype IN VARCHAR2 ,
2473 itemkey IN VARCHAR2 ,
2474 actid IN NUMBER ,
2475 funcmode IN VARCHAR2 ,
2476 resultout OUT NOCOPY VARCHAR2 ) AS
2477
2478 ------------------------------------------------------------------------------------------------
2479 -- Created by : Deepankar Dey, Oracle India (in)
2480 -- Date created: 16-Sept-2003
2481 --
2482 -- Purpose:
2483 --
2484 --
2485 --
2486 -- Known limitations/enhancements and/or remarks:
2487 --
2488 -- Change History:
2489 -- Who When What
2490 --
2491 -------------------------------------------------------------------------------------------------
2492
2493 l_date_prod VARCHAR2(30);
2494 l_doc_type VARCHAR2(30);
2495 l_role_name VARCHAR2(320);
2496 l_role_display_name VARCHAR2(320) := 'Adhoc Role';
2497 l_person_id_stu VARCHAR2(50);
2498 l_person_id_suvisor VARCHAR2(50);
2499 l_person_id_app VARCHAR2(50);
2500 l_person_id VARCHAR2(30);
2501
2502 -- cursor to get the user_name corresponding to the person_id
2503
2504 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
2505 SELECT user_name
2506 FROM fnd_user
2507 WHERE person_party_id = cp_person_id
2508 AND ( end_date IS NULL OR end_date > SYSDATE );
2509
2510
2511 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
2512 SELECT user_name
2513 FROM fnd_user
2514 WHERE user_id = cp_person_id
2515 AND ( end_date IS NULL OR end_date > SYSDATE );
2516
2517 l_user_name_stu fnd_user.user_name%TYPE;
2518 l_user_name_adm fnd_user.user_name%TYPE;
2519 l_user_name fnd_user.user_name%TYPE;
2520 l_sup_name VARCHAR2(32000);
2521
2522 CURSOR c_dup_user (cp_user_name VARCHAR2,
2523 cp_role_name VARCHAR2) IS
2524 SELECT count(1)
2525 FROM WF_LOCAL_USER_ROLES
2526 WHERE USER_NAME = cp_user_name
2527 AND ROLE_NAME = cp_role_name
2528 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
2529 AND ROLE_ORIG_SYSTEM_ID = 0;
2530
2531 l_dup_user NUMBER :=0;
2532
2533 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
2534 SELECT full_name
2535 FROM igs_pe_person_base_v
2536 WHERE person_id = cp_person_id;
2537
2538 l_c_full_name c_full_name%ROWTYPE;
2539
2540 CURSOR c_mesg_text (cp_message_name fnd_new_messages.message_name%TYPE) IS
2541 SELECT message_text
2542 FROM fnd_new_messages
2543 WHERE message_name = cp_message_name;
2544
2545 l_c_mesg_text c_mesg_text%ROWTYPE;
2546
2547 l_supervisor_flag VARCHAR2(5) := 'TRUE';
2548 BEGIN
2549
2550
2551
2552 IF (funcmode = 'RUN') THEN
2553
2554
2555 -- create the adhoc role
2556 l_role_name := 'IGS'||substr(itemkey,6);
2557
2558 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
2559 role_display_name => l_role_display_name
2560 );
2561
2562
2563 --
2564 -- fetch student for whom the record has been procesed and add the user name to the
2565 -- adhoc role
2566 --
2567
2568
2569 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
2570 l_person_id_suvisor := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_SUPERVISOR');
2571 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
2572
2573
2574 -- Checking for User Name for the corresponding person ID of a student
2575
2576 OPEN c_user_name (l_person_id_stu);
2577 FETCH c_user_name INTO l_user_name_stu;
2578 CLOSE c_user_name;
2579
2580 -- add this user name to the adhoc role if it is not null and unique
2581
2582 IF ( l_user_name_stu IS NOT NULL ) THEN
2583
2584 OPEN c_dup_user(l_user_name_stu,l_role_name);
2585 FETCH c_dup_user INTO l_dup_user;
2586 CLOSE c_dup_user;
2587
2588 IF l_user_name_stu IS NOT NULL AND l_dup_user = 0 THEN
2589 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
2590 role_users => l_user_name_stu);
2591 END IF;
2592
2593 END IF;
2594
2595
2596 -- Checking for User Name for the corresponding person ID of a ADMIN
2597
2598 OPEN c_user_name_admin (l_person_id_app);
2599 FETCH c_user_name_admin INTO l_user_name_adm;
2600 CLOSE c_user_name_admin;
2601
2602
2603 -- adding admin to the role
2604
2605 IF ( l_user_name_adm IS NOT NULL ) THEN
2606
2607 OPEN c_dup_user(l_user_name_adm,l_role_name);
2608 FETCH c_dup_user INTO l_dup_user;
2609 CLOSE c_dup_user;
2610
2611 IF l_user_name_adm IS NOT NULL AND l_dup_user = 0 THEN
2612 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
2613 role_users => l_user_name_adm);
2614 END IF;
2615
2616 END IF;
2617
2618
2619 -- Checking for User Name for the corresponding Supervisor
2620
2621 WHILE (LENGTH (l_person_id_suvisor) > 0)
2622 LOOP
2623
2624 -- getting the person ID for supervisor from the concatenated string
2625 IF (INSTR (l_person_id_suvisor, ',') > 0) THEN
2626 l_person_id := SUBSTR (l_person_id_suvisor, 1, INSTR (l_person_id_suvisor, ',') - 1);
2627 l_person_id_suvisor := SUBSTR (l_person_id_suvisor, INSTR (l_person_id_suvisor, ',') + 1);
2628
2629 ELSE
2630
2631 l_person_id := l_person_id_suvisor;
2632 l_person_id_suvisor := NULL;
2633
2634 END IF;
2635
2636 l_user_name := NULL;
2637 OPEN c_user_name (l_person_id);
2638 FETCH c_user_name INTO l_user_name;
2639 CLOSE c_user_name;
2640
2641 -- IF the l_user_name is null
2642 IF l_user_name IS NULL THEN
2643 -- Getting the full name of the Supervisor
2644 OPEN c_full_name(l_person_id);
2645 FETCH c_full_name INTO l_c_full_name;
2646 CLOSE c_full_name;
2647
2648 IF l_supervisor_flag = 'FALSE' THEN
2649 l_sup_name := l_sup_name || ', ';
2650 ELSE
2651 l_supervisor_flag := 'FALSE';
2652 END IF;
2653
2654 l_sup_name := l_sup_name || l_c_full_name.full_name;
2655
2656 ELSE
2657 -- add this user name to the adhoc role if it is not null and unique
2658 OPEN c_dup_user(l_user_name,l_role_name);
2659 FETCH c_dup_user INTO l_dup_user;
2660 CLOSE c_dup_user;
2661
2662 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
2663 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
2664 role_users => l_user_name);
2665 END IF;
2666 END IF;
2667
2668 END LOOP;
2669
2670 -- Setting the Adhoc Role Attribute
2671
2672 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2673 ItemKey => itemkey,
2674 aname => 'P_ROLE',
2675 avalue => l_role_name);
2676
2677 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2678 ItemKey => itemkey,
2679 aname => 'P_FROM_ROLE',
2680 avalue => l_user_name_adm);
2681
2682 Resultout:= 'COMPLETE:';
2683 RETURN;
2684
2685 END IF;
2686
2687 END create_adhoc_role;
2688
2689 PROCEDURE confirm_reg_not(
2690 itemtype IN VARCHAR2 ,
2691 itemkey IN VARCHAR2 ,
2692 actid IN NUMBER ,
2693 funcmode IN VARCHAR2 ,
2694 resultout OUT NOCOPY VARCHAR2
2695 ) AS
2696
2697 l_person_id VARCHAR2(50);
2698
2699 CURSOR c_person_info (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
2700 SELECT full_name , person_number
2701 FROM igs_pe_person_base_v
2702 WHERE person_id = cp_person_id;
2703
2704 CURSOR c_status (l_lookup_code fnd_lookups.lookup_code%TYPE)IS
2705 SELECT meaning
2706 FROM igs_lookups_view
2707 WHERE lookup_type = 'VS_EN_COURSE_ATMPT_STATUS'
2708 AND lookup_code = l_lookup_code ;
2709
2710 l_c_person_info c_person_info%ROWTYPE;
2711 l_c_status c_status%ROWTYPE;
2712 l_lookup fnd_lookups.lookup_code%TYPE;
2713
2714 BEGIN
2715
2716 igs_re_workflow.create_adhoc_role( itemtype => itemtype ,
2717 itemkey => itemkey ,
2718 actid => actid ,
2719 funcmode => funcmode ,
2720 resultout => resultout
2721 );
2722
2723 l_person_id := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
2724 l_lookup := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PROGRAMSTAT');
2725
2726 OPEN c_person_info(l_person_id);
2727 FETCH c_person_info INTO l_c_person_info;
2728 CLOSE c_person_info;
2729
2730 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2731 ItemKey => itemkey,
2732 aname => 'IA_PERSON_NUMBER',
2733 avalue => l_c_person_info.person_number);
2734
2735
2736 OPEN c_status(l_lookup);
2737 FETCH c_status INTO l_c_status;
2738 CLOSE c_status;
2739
2740 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2741 ItemKey => itemkey,
2742 aname => 'IA_PROG_STATUS',
2743 avalue => l_c_status.meaning);
2744
2745
2746 Resultout:= 'COMPLETE:';
2747 RETURN;
2748
2749
2750 END confirm_reg_not;
2751
2752
2753 PROCEDURE milstn_notify_not(
2754 itemtype IN VARCHAR2 ,
2755 itemkey IN VARCHAR2 ,
2756 actid IN NUMBER ,
2757 funcmode IN VARCHAR2 ,
2758 resultout OUT NOCOPY VARCHAR2
2759 ) AS
2760
2761 l_person_id VARCHAR2(50);
2762
2763 CURSOR c_person_info (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
2764 SELECT full_name , person_number
2765 FROM igs_pe_person_base_v
2766 WHERE person_id = cp_person_id;
2767
2768
2769 l_c_person_info c_person_info%ROWTYPE;
2770
2771 BEGIN
2772
2773 igs_re_workflow.create_adhoc_role( itemtype => itemtype ,
2774 itemkey => itemkey ,
2775 actid => actid ,
2776 funcmode => funcmode ,
2777 resultout => resultout
2778 );
2779
2780 l_person_id := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
2781
2782 OPEN c_person_info(l_person_id);
2783 FETCH c_person_info INTO l_c_person_info;
2784 CLOSE c_person_info;
2785
2786 Wf_Engine.SetItemAttrText( ItemType => itemtype,
2787 ItemKey => itemkey,
2788 aname => 'IA_PERSON_NUMBER',
2789 avalue => l_c_person_info.person_number);
2790
2791
2792
2793 Resultout:= 'COMPLETE:';
2794 RETURN;
2795
2796
2797 END milstn_notify_not;
2798
2799 END igs_re_workflow;
[Home] [Help]