1 PACKAGE BODY igs_en_workflow AS
2 /* $Header: IGSEN85B.pls 120.6 2006/04/13 01:54:27 smaddali ship $ */
3
4 /******************************************************************
5 Created By :Sanjeeb Rakshit
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 30th April,2003 Bug # 2829275 The new procedures are added as a part of UK Correspondence - Part -1 FD
12 vchappid 25-Jul-01 Two new procedures are added
13 --kkillams 10-03-2003 Replaced wf_event_t.AddParameterToList with wf_event.AddParameterToList api
14 -- to show params in the workflow out queue and able to derive the parameters
15 from the outside after event is raised, w.r.t. bug 2840171
16 knaraset 18-Nov-2003 Added procedure student_placement_event, for placement build
17 stutta 14-Apr-2005 Replaced all references to FND_USER.customer_id to
18 FND_USER.person_party_id. Bug #4293911
19
20 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) in ENR_NOTIFICATION
21 procedure as a part of bug#4958173.
22 smaddali 10-apr-06 Added new procedure raise_spi_rcond_event for bug#5091858 BUILD EN324
23 ******************************************************************/
24
25 PROCEDURE sua_status_change_mail(p_unit_status IN VARCHAR2,p_person_id IN NUMBER,p_uoo_id IN NUMBER) IS
26 ------------------------------------------------------------------------------------------------
27 --Created by : sarakshi, Oracle India (in)
28 --Date created: 18-Jul-2001
29 --
30 --Purpose:To implement workflow ,to send mail to student if a particular
31 --unit attempt status has changed.
32 --
33 --
34 --Known limitations/enhancements and/or remarks:
35 --
36 --Change History:
37 --Who When What
38 --vchappid 25-Jul-01 Call to initialize method had to be made before raising an event
39 -------------------------------------------------------------------------------------------------
40
41 CURSOR cur_seq_val
42 IS
43 SELECT igs_en_status_mail_req_s.nextval seq_val
44 FROM DUAL;
45
46 l_cur_seq_val cur_seq_val%ROWTYPE;
47 l_wf_parameter_list_t WF_PARAMETER_LIST_T:= wf_parameter_list_t();
48 l_wf_installed fnd_lookups.lookup_code%TYPE;
49
50 BEGIN
51 -- get the profile value that is set for checking if workflow is installed
52 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
53
54 -- if workflow is installed then carry on with the raising an event
55 IF (RTRIM(l_wf_installed) ='Y') THEN
56 -- get the next value of the sequence
57 OPEN cur_seq_val;
58 FETCH cur_seq_val INTO l_cur_seq_val;
59 CLOSE cur_seq_val;
60
61 -- set the event parameters
62 wf_event.AddParameterToList(p_name=>'STUDENT_ID', p_value=>p_person_id, p_parameterlist=>l_wf_parameter_list_t);
63 wf_event.AddParameterToList(p_name=>'UOO_ID', p_value=>p_uoo_id, p_parameterlist=>l_wf_parameter_list_t);
64 wf_event.AddParameterToList(p_name=>'UNIT_STATUS',p_value=>p_unit_status,p_parameterlist=>l_wf_parameter_list_t);
65
66 -- raise the event
67 WF_EVENT.RAISE(p_event_name=>'oracle.apps.igs.en.enrp.statmail',
68 p_event_key =>'oracle.apps.igs.en.enrp.statmail'||l_cur_seq_val.seq_val,
69 p_parameters=>l_wf_parameter_list_t);
70 END IF;
71
72 END sua_status_change_mail;
73
74 PROCEDURE inform_stdnt_instruct_action( p_student_id IN NUMBER,
75 p_instructor_id IN NUMBER,
76 p_uoo_id IN NUMBER,
77 p_approval_status IN VARCHAR2,
78 p_date_submission IN DATE,
79 p_request_type IN VARCHAR2
80 )
81 IS
82 ------------------------------------------------------------------------------------------------
83 --Created by : vchappid, Oracle India (in)
84 --Date created: 25-Jul-2001
85 --
86 --Purpose: To raise the business event for sending the mail to the student notifying the action
87 -- of the Instructor ( Approve/Deny/Need More Information )
88 --
89 --
90 --Known limitations/enhancements and/or remarks:
91 --
92 --Change History:
93 --Who When What
94 --knaraset 24-oct-02 Added parameter p_request_type, as part of build TD Audit,used to distinguish
95 -- whether the request is for Special permission or for Audit.
96 -------------------------------------------------------------------------------------------------
97
98 CURSOR cur_seq_val
99 IS
100 SELECT igs_en_inst_action_s.nextval seq_val
101 FROM DUAL;
102
103 l_cur_seq_val cur_seq_val%ROWTYPE;
104 l_wf_parameter_list_t WF_PARAMETER_LIST_T:=wf_parameter_list_t();
105 l_wf_installed fnd_lookups.lookup_code%TYPE;
106
107 BEGIN
108
109 -- get the profile value that is set for checking if workflow is installed
110 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
111
112 -- if workflow is installed then carry on with the raising an event
113 IF (RTRIM(l_wf_installed) ='Y') THEN
114 -- get the next value of the sequence
115 OPEN cur_seq_val;
116 FETCH cur_seq_val INTO l_cur_seq_val;
117 CLOSE cur_seq_val;
118
119 -- set the event parameters
120 wf_event.AddParameterToList(p_name=> 'STUDENT_ID', p_value => p_student_id, p_parameterlist=>l_wf_parameter_list_t);
121 wf_event.AddParameterToList(p_name => 'INSTRUCTOR_ID', p_value => p_instructor_id, p_parameterlist=>l_wf_parameter_list_t);
122 wf_event.AddParameterToList(p_name => 'UOO_ID', p_value => p_uoo_id, p_parameterlist=>l_wf_parameter_list_t);
123 wf_event.AddParameterToList(p_name => 'APPROVAL_STATUS', p_value => p_approval_status,p_parameterlist=>l_wf_parameter_list_t);
124 wf_event.AddParameterToList(p_name => 'DATE_SUBMISSION', p_value => p_date_submission,p_parameterlist=>l_wf_parameter_list_t);
125 wf_event.AddParameterToList(p_name => 'REQUEST_TYPE', p_value => p_request_type, p_parameterlist=>l_wf_parameter_list_t);
126
127
128 -- raise the event
129 WF_EVENT.RAISE(p_event_name=>'oracle.apps.igs.en.enrp.instresp',
130 p_event_key =>'oracle.apps.igs.en.enrp.instresp'||l_cur_seq_val.seq_val,
131 p_parameters=>l_wf_parameter_list_t);
132 END IF;
133
134 END inform_stdnt_instruct_action;
135 PROCEDURE inform_instruct_stdnt_petition( p_student_id IN NUMBER,
136 p_instructor_id IN NUMBER,
137 p_uoo_id IN NUMBER,
138 p_date_submission IN DATE,
139 p_transaction_type IN VARCHAR2,
140 p_request_type IN VARCHAR2
141 )
142 IS
143 ------------------------------------------------------------------------------------------------
144 --Created by : vchappid, Oracle India (in)
145 --Date created: 18-Jul-2001
146 --
147 --Purpose:To raise the business event for sending mail to the Instructor when the student submits
148 -- his/her petition for special approval
149 --
150 --
151 --Known limitations/enhancements and/or remarks:
152 --
153 --Change History:
154 --Who When What
155 --knaraset 24-oct-02 Added parameter p_request_type and p_transaction_type, as part of build TD Audit,
156 --
157 -------------------------------------------------------------------------------------------------
158
159 CURSOR cur_seq_val IS SELECT igs_en_stud_splperm_req_s.NEXTVAL seq_val FROM DUAL;
160
161 l_cur_seq_val cur_seq_val%ROWTYPE;
162 l_wf_parameter_list_t WF_PARAMETER_LIST_T:=wf_parameter_list_t();
163 l_wf_installed fnd_lookups.lookup_code%TYPE;
164
165 BEGIN
166
167 -- get the profile value that is set for checking if workflow is installed
168 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
169
170 -- if workflow is installed then carry on with the raising an event
171 IF (RTRIM(l_wf_installed) ='Y') THEN
172 -- get the next value of the sequence
173 OPEN cur_seq_val;
174 FETCH cur_seq_val INTO l_cur_seq_val;
175 CLOSE cur_seq_val;
176
177 wf_event.AddParameterToList ( p_Name => 'STUDENT_ID', p_Value => p_student_id, p_parameterlist=>l_wf_parameter_list_t);
178 wf_event.AddParameterToList ( p_Name => 'INSTRUCTOR_ID', p_Value => p_instructor_id, p_parameterlist=>l_wf_parameter_list_t);
179 wf_event.AddParameterToList ( p_Name => 'UOO_ID', p_Value => p_uoo_id, p_parameterlist=>l_wf_parameter_list_t);
180 wf_event.AddParameterToList ( p_Name => 'DATE_SUBMISSION', p_Value => p_date_submission, p_parameterlist=>l_wf_parameter_list_t);
181 wf_event.AddParameterToList ( p_Name => 'TRANSACTION_TYPE',p_Value => p_transaction_type, p_parameterlist=>l_wf_parameter_list_t);
182 wf_event.AddParameterToList ( p_Name => 'REQUEST_TYPE', p_Value => p_request_type, p_parameterlist=>l_wf_parameter_list_t);
183
184 -- raise the event
185 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.enrp.studreq',
186 p_event_key => 'oracle.apps.igs.en.enrp.studreq'||l_cur_seq_val.seq_val,
187 p_parameters => l_wf_parameter_list_t);
188 END IF;
189 END inform_instruct_stdnt_petition;
190
191
192 PROCEDURE intermission_event(p_personid IN NUMBER ,
193 p_program_cd IN VARCHAR2,
194 p_intmtype IN VARCHAR2,
195 p_startdt IN DATE,
196 p_enddt IN DATE ,
197 p_inst_name IN VARCHAR2,
198 p_max_cp IN NUMBER,
199 p_max_term IN NUMBER,
200 p_anti_cp IN NUMBER,
201 p_approver IN NUMBER
202 )
203
204 IS
205 ------------------------------------------------------------------------------------------------
206 -- Created by : Deepankar Dey, Oracle India (in)
207 -- Date created: 30-04-2003
208 --
209 -- Purpose:Bug # 2829275 . UK Correspondence.The TBH needs to be modified to invoke the intermission business event when an
210 -- intermission record is created or certain attributes updated.
211 --
212 --
213 -- Known limitations/enhancements and/or remarks:
214 --
215 -- Change History:
216 -- Who When What
217 --
218 -------------------------------------------------------------------------------------------------
219 l_event_t wf_event_t;
220 l_parameter_list_t wf_parameter_list_t;
221 l_itemKey varchar2(100);
222 ln_seq_val NUMBER;
223
224 -- Gets a unique sequence number
225
226 CURSOR c_seq_num IS
227 SELECT igs_en_intrmn_s.NEXTVAL
228 FROM dual;
229
230
231 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
232
233 CURSOR cur_prof_value IS
234 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
235 FROM dual;
236
237 -- Cursor for fetching the Program Type based on the program Attempt.
238
239 CURSOR cur_prog_type IS
240 SELECT course_type
241 FROM
242 igs_en_stdnt_ps_att espa,
243 igs_ps_ver psv
244 WHERE
245 psv.course_cd = espa.course_cd
246 AND psv.version_number = espa.version_number
247 AND espa.course_cd = p_program_cd
248 AND espa.person_id = p_personid ;
249
250 -- Cursor to fetch the Intermission Type Description
251
252 CURSOR cur_int_type IS
253 SELECT intermission_type,description
254 FROM igs_en_intm_types
255 WHERE intermission_type = p_intmtype;
256
257
258
259 l_cur_prof_value cur_prof_value%ROWTYPE;
260 l_cur_prog_type cur_prog_type%ROWTYPE;
261 l_cur_int_type cur_int_type%ROWTYPE;
262
263
264 BEGIN
265
266 -- Checking if the Workflow is installed at the environment or not.
267
268 OPEN cur_prof_value;
269 FETCH cur_prof_value INTO l_cur_prof_value;
270 CLOSE cur_prof_value;
271
272 IF (l_cur_prof_value.value = 'Y') THEN
273
274
275 -- Get the sequence value
276
277 OPEN c_seq_num;
278 FETCH c_seq_num INTO ln_seq_val ;
279 CLOSE c_seq_num ;
280
281 -- Getting the Program Type based on the program Attempt
282
283 OPEN cur_prog_type;
284 FETCH cur_prog_type INTO l_cur_prog_type;
285 CLOSE cur_prog_type;
286
287 -- Getting the Intermission Type Description, based on the Intermission Type Passed
288
289 OPEN cur_int_type;
290 FETCH cur_int_type INTO l_cur_int_type;
291 CLOSE cur_int_type;
292
293 --
294 -- initialize the wf_event_t object
295 --
296
297 wf_event_t.Initialize(l_event_t);
298
299
300 --
301 -- Adding the parameters to the parameter list
302 --
303
304 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
305 wf_event.AddParameterToList (p_name => 'P_PROGRAM_CD', p_Value => p_program_cd, p_ParameterList => l_parameter_list_t);
306 wf_event.AddParameterToList (p_name => 'P_PROGRAM_TYPE', p_Value => l_cur_prog_type.course_type, p_ParameterList => l_parameter_list_t);
307 wf_event.AddParameterToList (p_name => 'P_INTMTYPE', p_Value => p_intmtype, p_ParameterList => l_parameter_list_t);
308 wf_event.AddParameterToList (p_name => 'P_INTMDESC',p_value=>l_cur_int_type.description,p_parameterlist=>l_parameter_list_t);
309 wf_event.AddParameterToList (p_name => 'P_STARTDT', p_Value => p_startdt, p_ParameterList => l_parameter_list_t);
310 wf_event.AddParameterToList (p_name => 'P_ENDDT', p_Value => p_enddt, p_ParameterList => l_parameter_list_t);
311 wf_event.AddParameterToList (p_name => 'P_INST_NAME', p_Value => p_inst_name, p_ParameterList => l_parameter_list_t);
312 wf_event.AddParameterToList (p_name => 'P_MAX_CP',p_value=>p_max_cp,p_parameterlist=>l_parameter_list_t);
313 wf_event.AddParameterToList (p_name => 'P_MAX_TERM', p_Value => p_max_term, p_ParameterList => l_parameter_list_t);
314 wf_event.AddParameterToList (p_name => 'P_ANTI_CP',p_value=>p_anti_cp,p_parameterlist=>l_parameter_list_t);
315 wf_event.AddParameterToList (p_name => 'P_APPROVER', p_Value => p_approver, p_ParameterList => l_parameter_list_t);
316
317
318 -- Raise the Event
319
320
321 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.prog.intrmn',
322 p_event_key => 'PROGINTRMN'||ln_seq_val,
323 p_parameters => l_parameter_list_t);
324
325 --
326 -- Deleting the Parameter list after the event is raised
327 --
328
329 l_parameter_list_t.delete;
330
331 END IF;
332
333 END intermission_event;
334
335 PROCEDURE progdiscont_event (
336 p_personid IN NUMBER ,
337 p_programcd IN VARCHAR2,
338 p_discontindt IN DATE ,
339 p_discontincd IN VARCHAR2
340 ) IS
341 ------------------------------------------------------------------------------------------------
342 -- Created by : Deepankar Dey, Oracle India (in)
343 -- Date created: 30-04-2003
344 --
345 -- Purpose:Bug # 2829275 . UK Correspondence.The TBH needs to be modified to invoke the program discontinuation business event when an
346 -- program is discontinued.
347 --
348 --
349 -- Known limitations/enhancements and/or remarks:
350 --
351 -- Change History:
352 -- Who When What
353 --
354 -------------------------------------------------------------------------------------------------
355 l_event_t wf_event_t;
356 l_parameter_list_t wf_parameter_list_t;
357 l_itemKey varchar2(100);
358 ln_seq_val NUMBER;
359
360 -- Gets a unique sequence number
361
362 CURSOR c_seq_num IS
363 SELECT igs_en_prgdsc_s.NEXTVAL
364 FROM dual;
365
366
367 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
368
369 CURSOR cur_prof_value IS
370 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
371 FROM dual;
372
373 -- Getting the logged in User
374
375 CURSOR cur_user_id IS
376 SELECT FND_GLOBAL.USER_ID user_id
377 FROM dual;
378
379 -- Getting the Discontinuation Reason for a Discontinuation Type
380
381 CURSOR cur_discontinue_reason IS
382 SELECT s_discontinuation_reason_type
383 FROM igs_en_dcnt_reasoncd
384 WHERE discontinuation_reason_cd = p_discontincd;
385
386 l_cur_prof_value cur_prof_value%ROWTYPE;
387 l_cur_user_id cur_user_id%ROWTYPE;
388 l_cur_discontinue_reason cur_discontinue_reason%ROWTYPE;
389
390
391 BEGIN
392
393 -- Checking if the Workflow is installed at the environment or not.
394
395 OPEN cur_prof_value;
396 FETCH cur_prof_value INTO l_cur_prof_value;
397 CLOSE cur_prof_value;
398
399 IF (l_cur_prof_value.value = 'Y') THEN
400
401
402 -- Get the sequence value
403
404 OPEN c_seq_num;
405 FETCH c_seq_num INTO ln_seq_val ;
406 CLOSE c_seq_num ;
407
408 -- Getting the Logged on User
409
410 OPEN cur_user_id ;
411 FETCH cur_user_id INTO l_cur_user_id ;
412 CLOSE cur_user_id ;
413
414 -- Getting the Discontinuation Reason for a Discontinuation Type
415
416 OPEN cur_discontinue_reason;
417 FETCH cur_discontinue_reason INTO l_cur_discontinue_reason;
418 CLOSE cur_discontinue_reason;
419
420 --
421 -- initialize the wf_event_t object
422 --
423
424 wf_event_t.Initialize(l_event_t);
425
426
427 --
428 -- Adding the parameters to the parameter list
429 --
430
431 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
432 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => p_programcd, p_ParameterList => l_parameter_list_t);
433 wf_event.AddParameterToList (p_name => 'P_DISCONTINDT', p_Value => p_discontindt, p_ParameterList => l_parameter_list_t);
434 wf_event.AddParameterToList (p_name => 'P_DISCONTINCD', p_Value => p_discontincd, p_ParameterList => l_parameter_list_t);
435 wf_event.AddParameterToList (p_name => 'P_DISCONTINTYPE',p_value=>l_cur_discontinue_reason.s_discontinuation_reason_type,p_parameterlist=>l_parameter_list_t);
436 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
437
438
439 -- Raise the Event
440
441
442 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.prog.discon',
443 p_event_key => 'PROGDISCON'||ln_seq_val,
444 p_parameters => l_parameter_list_t);
445
446
447 --
448 -- Deleting the Parameter list after the event is raised
449 --
450
451 l_parameter_list_t.delete;
452
453 END IF;
454
455 END progdiscont_event;
456
457
458 PROCEDURE progtrans_event (
459 p_personid IN NUMBER ,
460 p_destprogcd IN VARCHAR2,
461 p_progstartdt IN DATE ,
462 p_location IN VARCHAR2,
463 p_atten_type IN VARCHAR2,
464 p_atten_mode IN VARCHAR2,
465 p_prog_status IN VARCHAR2,
466 p_trsnfrdt IN DATE,
467 p_sourceprogcd IN VARCHAR2
468 ) IS
469 ------------------------------------------------------------------------------------------------
470 -- Created by : Deepankar Dey, Oracle India (in)
471 -- Date created: 30-04-2003
472 --
473 -- Purpose:Bug # 2829275 . UK Correspondence.The program transfer business event is reaised form this procedure when
474 -- program is transfered.
475 --
476 --
477 -- Known limitations/enhancements and/or remarks:
478 --
479 -- Change History:
480 -- Who When What
481 --
482 -------------------------------------------------------------------------------------------------
483
484 l_event_t wf_event_t;
485 l_parameter_list_t wf_parameter_list_t;
486 l_itemKey varchar2(100);
487 ln_seq_val NUMBER;
488
489 -- Gets a unique sequence number
490
491 CURSOR c_seq_num IS
492 SELECT igs_en_prgtrn_s.NEXTVAL
493 FROM dual;
494
495
496 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
497
498 CURSOR cur_prof_value IS
499 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
500 FROM dual;
501
502 -- Getting the logged in User
503
504 CURSOR cur_user_id IS
505 SELECT FND_GLOBAL.USER_ID user_id
506 FROM dual;
507
508 l_cur_prof_value cur_prof_value%ROWTYPE;
509 l_cur_user_id cur_user_id%ROWTYPE;
510
511
512 BEGIN
513
514 -- Checking if the Workflow is installed at the environment or not.
515
516 OPEN cur_prof_value;
517 FETCH cur_prof_value INTO l_cur_prof_value;
518 CLOSE cur_prof_value;
519
520 IF (l_cur_prof_value.value = 'Y') THEN
521
522
523 -- Get the sequence value
524
525 OPEN c_seq_num;
526 FETCH c_seq_num INTO ln_seq_val ;
527 CLOSE c_seq_num ;
528
529 -- Getting the Logged on User
530
531 OPEN cur_user_id ;
532 FETCH cur_user_id INTO l_cur_user_id ;
533 CLOSE cur_user_id ;
534
535 --
536 -- initialize the wf_event_t object
537 --
538
539 wf_event_t.Initialize(l_event_t);
540
541
542 --
543 -- Adding the parameters to the parameter list
544 --
545
546 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
547 wf_event.AddParameterToList (p_name => 'P_DESTPROGCD', p_Value => p_destprogcd, p_ParameterList => l_parameter_list_t);
548 wf_event.AddParameterToList (p_name => 'P_PROGSTARTDT', p_Value => p_progstartdt, p_ParameterList => l_parameter_list_t);
549 wf_event.AddParameterToList (p_name => 'P_LOCATION', p_Value => p_location, p_ParameterList => l_parameter_list_t);
550 wf_event.AddParameterToList (p_name => 'P_ATTEN_TYPE',p_value=>p_atten_type,p_parameterlist=>l_parameter_list_t);
551 wf_event.AddParameterToList (p_name => 'P_ATTEN_MODE', p_Value => p_atten_mode, p_ParameterList => l_parameter_list_t);
552 wf_event.AddParameterToList (p_name => 'P_PROG_STATUS', p_Value => p_prog_status, p_ParameterList => l_parameter_list_t);
553 wf_event.AddParameterToList (p_name => 'P_TRSNFRDT', p_Value => p_trsnfrdt, p_ParameterList => l_parameter_list_t);
554 wf_event.AddParameterToList (p_name => 'P_SOURCEPROGCD',p_value=>p_sourceprogcd,p_parameterlist=>l_parameter_list_t);
555 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
556
557
558 -- Raise the Event
559
560
561 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.prog.transfer',
562 p_event_key => 'PROGTRANSFER'||ln_seq_val,
563 p_parameters => l_parameter_list_t);
564
565 --
566 -- Deleting the Parameter list after the event is raised
567 --
568
569 l_parameter_list_t.delete;
570
571 END IF;
572
573 END progtrans_event;
574
575
576 PROCEDURE progofropt_event (
577 p_personid IN NUMBER ,
578 p_programcd IN VARCHAR2 ,
579 p_locationcd IN VARCHAR2 ,
580 p_prev_location_cd IN VARCHAR2 ,
581 p_attndmode IN VARCHAR2 ,
582 p_prev_attndmode IN VARCHAR2 ,
583 p_attndtype IN VARCHAR2 ,
584 p_prev_attndtype IN VARCHAR2
585
586 ) IS
587 ------------------------------------------------------------------------------------------------
588 -- Created by : Deepankar Dey, Oracle India (in)
589 -- Date created: 30-04-2003
590 --
591 -- Purpose:Bug # 2829275 . UK Correspondence.The program option change business event is reaised form this procedure when
592 -- program option is changed.
593 --
594 --
595 -- Known limitations/enhancements and/or remarks:
596 --
597 -- Change History:
598 -- Who When What
599 --
600 -------------------------------------------------------------------------------------------------
601
602 l_event_t wf_event_t;
603 l_parameter_list_t wf_parameter_list_t;
604 l_itemKey varchar2(100);
605 ln_seq_val NUMBER;
606
607 -- Gets a unique sequence number
608
609 CURSOR c_seq_num IS
610 SELECT igs_en_profop_s.NEXTVAL
611 FROM dual;
612
613
614 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
615
616 CURSOR cur_prof_value IS
617 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
618 FROM dual;
619
620 -- Getting the logged in User
621
622 CURSOR cur_user_id IS
623 SELECT FND_GLOBAL.USER_ID user_id
624 FROM dual;
625
626 l_cur_prof_value cur_prof_value%ROWTYPE;
627 l_cur_user_id cur_user_id%ROWTYPE;
628
629
630 BEGIN
631
632 -- Checking if the Workflow is installed at the environment or not.
633
634
635 OPEN cur_prof_value;
636 FETCH cur_prof_value INTO l_cur_prof_value;
637 CLOSE cur_prof_value;
638
639
640 IF (l_cur_prof_value.value = 'Y') THEN
641
642
643 -- Get the sequence value
644
645 OPEN c_seq_num;
646 FETCH c_seq_num INTO ln_seq_val ;
647 CLOSE c_seq_num ;
648
649 -- Getting the Logged on User
650
651 OPEN cur_user_id ;
652 FETCH cur_user_id INTO l_cur_user_id ;
653 CLOSE cur_user_id ;
654
655 --
656 -- initialize the wf_event_t object
657 --
658
659 wf_event_t.Initialize(l_event_t);
660
661
662 --
663 -- Adding the parameters to the parameter list
664 --
665
666 wf_event.AddParameterToList (p_name => 'P_PERSONID',p_value=>p_personid,p_parameterlist=>l_parameter_list_t);
667 wf_event.AddParameterToList (p_name => 'P_PROGRAMCD', p_Value => p_programcd, p_ParameterList => l_parameter_list_t);
668 wf_event.AddParameterToList (p_name => 'P_LOCATIONCD', p_Value => p_locationcd, p_ParameterList => l_parameter_list_t);
669 wf_event.AddParameterToList (p_name => 'P_PREV_LOCATION_CD', p_Value => p_prev_location_cd, p_ParameterList => l_parameter_list_t);
670 wf_event.AddParameterToList (p_name => 'P_ATTNDMODE',p_value=>p_attndmode,p_parameterlist=>l_parameter_list_t);
671 wf_event.AddParameterToList (p_name => 'P_PREV_ATTNDMODE', p_Value => p_prev_attndmode, p_ParameterList => l_parameter_list_t);
672 wf_event.AddParameterToList (p_name => 'P_ATTNDTYPE', p_Value => p_attndtype, p_ParameterList => l_parameter_list_t);
673 wf_event.AddParameterToList (p_name => 'P_PREV_ATTNDTYPE', p_Value => p_prev_attndtype, p_ParameterList => l_parameter_list_t);
674 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => l_cur_user_id.user_id, p_ParameterList => l_parameter_list_t);
675
676
677 -- Raise the Event
678
679
680 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.pgofop.update',
681 p_event_key => 'PROFOPUPDATE'||ln_seq_val,
682 p_parameters => l_parameter_list_t);
683
684
685
686 --
687 -- Deleting the Parameter list after the event is raised
688 --
689
690 l_parameter_list_t.delete;
691
692 END IF;
693
694 END progofropt_event;
695
696 PROCEDURE enr_notification ( ERRBUF OUT NOCOPY VARCHAR2 ,
697 RETCODE OUT NOCOPY NUMBER ,
698 p_acad_cal_type IN VARCHAR2 ,
699 p_sub_offset_day IN NUMBER
700
701 ) IS
702 ------------------------------------------------------------------------------------------------
703 -- Created by : Deepankar Dey, Oracle India (in)
704 -- Date created: 30-04-2003
705 --
706 -- Purpose:Bug # 2829275 . This procedure is the executable for the enrollment notifications concurrent program.
707 -- It will identify the overdue submissions and raise a notification for each one of those.
708 --
709 --
710 -- Known limitations/enhancements and/or remarks:
711 --
712 -- Change History:
713 -- Who When What
714 -- ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
715 -------------------------------------------------------------------------------------------------
716
717 CURSOR cur_acad_cal_type IS
718 SELECT 1
719 FROM igs_ca_type
720 WHERE closed_ind='N'
721 AND s_cal_cat = 'ACADEMIC'
722 AND cal_type = p_acad_cal_type;
723
724 CURSOR cur_offset_days(cp_offset_date igs_re_candidature_all.research_topic%TYPE) IS
725 SELECT rec.person_id,rec.max_submission_dt, esp.course_cd, ret.ca_sequence_number, ret.sequence_number
726 ,ret.logical_delete_dt,ret.thesis_result_cd , ret.title , rec.sequence_number can_sequence_number
727 FROM igs_re_candidature rec,
728 igs_en_stdnt_ps_att esp,
729 igs_re_thesis ret
730 WHERE max_submission_dt IS NOT NULL
731 AND max_submission_dt = (trunc(SYSDATE) - cp_offset_date )
732 AND esp.course_cd = rec.sca_course_cd
733 AND esp.person_id = rec.person_id
734 AND esp.cal_type = p_acad_cal_type
735 AND ret.person_id = rec.person_id
736 AND ret.ca_sequence_number = rec.sequence_number
737 ORDER BY rec.person_id;
738
739 CURSOR cur_person_number (cp_person_id igs_pe_person.person_id%TYPE) IS
740 SELECT person_number,full_name
741 FROM igs_pe_person_base_v
742 WHERE person_id = cp_person_id;
743
744 l_cur_person_number cur_person_number%ROWTYPE;
745
746 CURSOR cur_thesis_supervisor(cp_person_id igs_re_sprvsr.person_id%TYPE,
747 cp_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE) IS
748 SELECT person_id , sequence_number
749 FROM igs_re_sprvsr sup
750 WHERE ca_person_id = cp_person_id
751 AND ca_sequence_number = cp_sequence_number
752 AND (end_dt IS NULL OR end_dt > SYSDATE );
753
754 l_cur_acad_cal_type cur_acad_cal_type%ROWTYPE;
755 l_num NUMBER(1);
756 l_supervisor_gr VARCHAR2(4000);
757 l_person_id_old igs_pe_person_base_v.person_id%TYPE := NULL;
758 l_person_id_new igs_pe_person_base_v.person_id%TYPE := NULL;
759
760 BEGIN
761
762 igs_ge_gen_003.set_org_id(NULL);
763 retcode := 0;
764 SAVEPOINT s_enr_notify;
765
766
767 OPEN cur_acad_cal_type;
768 FETCH cur_acad_cal_type INTO l_num ;
769 CLOSE cur_acad_cal_type ;
770
771 IF (l_num <> 1) THEN
772
773 FND_MESSAGE.Set_Name('IGS','IGS_EN_INVLD_PARAM');
774 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
775 IGS_GE_MSG_STACK.ADD;
776 RAISE FND_API.G_EXC_ERROR;
777
778 END IF;
779
780
781 -- Processing for all the research candidates depending on the Academinc Calander and the Offset days Parametes passed
782 -- If this is a negative integer then the submission date of future .
783 -- If this is a positive integer then the submission date of past
784
785 FOR l_cur_offset_days IN cur_offset_days(p_sub_offset_day) LOOP
786
787 l_supervisor_gr := null;
788 l_person_id_new := l_cur_offset_days.person_id;
789
790 -- Checking for the thesis records which has a status of pending.
791
792 IF ( igs_re_gen_002.resp_get_the_status(l_cur_offset_days.person_id,
793 l_cur_offset_days.ca_sequence_number,
794 l_cur_offset_days.sequence_number,
795 'Y',
796 l_cur_offset_days.logical_delete_dt,
797 l_cur_offset_days.thesis_result_cd ) = 'PENDING' ) THEN
798
799
800 FOR l_cur_thesis_supervisor IN cur_thesis_supervisor(l_cur_offset_days.person_id ,l_cur_offset_days.can_sequence_number) LOOP
801
802 IF (l_supervisor_gr IS NOT NULL) THEN
803 l_supervisor_gr := l_supervisor_gr || ',' || l_cur_thesis_supervisor.person_id ;
804 ELSE
805 l_supervisor_gr := l_cur_thesis_supervisor.person_id ;
806 END IF;
807
808 END LOOP;
809
810 -- Raising the Overdue Submission Event
811
812 igs_re_workflow.overduesub_event(
813 p_personid => l_cur_offset_days.person_id ,
814 p_programcd => l_cur_offset_days.course_cd ,
815 p_thesistitle => l_cur_offset_days.title ,
816 p_maxsubdt => l_cur_offset_days.max_submission_dt ,
817 p_suprvsr => l_supervisor_gr
818
819 );
820
821 -- Displaying message in the log. Message used in concurrent log to indicate raising of overdue submission event
822
823 IF l_person_id_old <> l_person_id_new OR l_person_id_old IS NULL THEN
824
825 OPEN cur_person_number(l_person_id_new);
826 FETCH cur_person_number INTO l_cur_person_number;
827 CLOSE cur_person_number ;
828
829 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PERSON_NUM');
830 FND_MESSAGE.SET_TOKEN('PERSON_NUM',l_cur_person_number.person_number);
831 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
832
833 l_person_id_old := l_person_id_new;
834
835 END IF;
836
837 FND_MESSAGE.SET_NAME('IGS','IGS_EN_OVR_SUB');
838 FND_MESSAGE.SET_TOKEN('THSS_TTL',l_cur_offset_days.title);
839 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get);
840
841
842 END IF;
843
844 END LOOP;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 ROLLBACK TO s_enr_notify;
849 errbuf := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
850 retcode := 2;
851 FND_MESSAGE.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
852 FND_MESSAGE.SET_TOKEN ('NAME', 'igs_en_workflow.enr_notification(): '
853 || SUBSTR (SQLERRM,1,80));
854 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.Get);
855 IGS_GE_MSG_STACK.ADD;
856 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
857
858 END enr_notification;
859
860 PROCEDURE intermission_not(
861 itemtype IN VARCHAR2 ,
862 itemkey IN VARCHAR2 ,
863 actid IN NUMBER ,
864 funcmode IN VARCHAR2 ,
865 resultout OUT NOCOPY VARCHAR2 ) AS
866 ------------------------------------------------------------------------------------------------
867 -- Created by : Deepankar Dey, Oracle India (in)
868 -- Date created: 30-04-2003
869 --
870 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
871 --
872 --
873 --
874 -- Known limitations/enhancements and/or remarks:
875 --
876 -- Change History:
877 -- Who When What
878 --
879 -------------------------------------------------------------------------------------------------
880 l_date_prod VARCHAR2(30);
881 l_doc_type VARCHAR2(30);
882 l_role_name VARCHAR2(320);
883 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSEN002';
884 l_person_id_stu VARCHAR2(4000);
885 l_person_id_app VARCHAR2(4000);
886 l_person_id VARCHAR2(30);
887
888 -- cursor to get the user_name corresponding to the person_id
889
890 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
891 SELECT user_name
892 FROM fnd_user
893 WHERE person_party_id = cp_person_id;
894
895 l_user_name fnd_user.user_name%TYPE;
896
897 CURSOR c_dup_user (cp_user_name VARCHAR2,
898 cp_role_name VARCHAR2) IS
899 SELECT count(1)
900 FROM WF_LOCAL_USER_ROLES
901 WHERE USER_NAME = cp_user_name
902 AND ROLE_NAME = cp_role_name
903 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
904 AND ROLE_ORIG_SYSTEM_ID = 0;
905
906 l_dup_user NUMBER :=0;
907
908 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
909 SELECT full_name
910 FROM igs_pe_person_base_v
911 WHERE person_id = cp_person_id;
912
913 l_c_full_name c_full_name%ROWTYPE;
914
915 BEGIN
916
917
918
919 IF (funcmode = 'RUN') THEN
920 -- create the adhoc role
921 l_role_name := 'IGS'||substr(itemkey,6);
922
923 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
924 role_display_name => l_role_display_name
925 );
926
927
928
929 --
930 -- fetch student for whom the record has been procesed and add the user name to the
931 -- adhoc role
932 --
933 --
934
935 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
936 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_APPROVER');
937
938 -- Getting the Full Name of the Student
939
940 OPEN c_full_name(l_person_id_stu);
941 FETCH c_full_name INTO l_c_full_name;
942 CLOSE c_full_name;
943
944 -- Setting this full name of the student
945
946 Wf_Engine.SetItemAttrText( ItemType => itemtype,
947 ItemKey => itemkey,
948 aname => 'IA_PERSONNAME',
949 avalue => l_c_full_name.full_name
950 );
951
952 -- Checking for User Name for the corresponding person ID of a student
953
954 OPEN c_user_name (l_person_id_stu);
955 FETCH c_user_name INTO l_user_name;
956 CLOSE c_user_name;
957
958
959
960 -- add this user name to the adhoc role if it is not null and unique
961 OPEN c_dup_user(l_user_name,l_role_name);
962 FETCH c_dup_user INTO l_dup_user;
963 CLOSE c_dup_user;
964
965 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
966 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
967 role_users => l_user_name);
968 END IF;
969
970
971
972 OPEN c_user_name (l_person_id_app);
973 FETCH c_user_name INTO l_user_name;
974 CLOSE c_user_name;
975
976
977
978 -- add the approve to the adhoc role if it is not null and unique
979 OPEN c_dup_user(l_user_name,l_role_name);
980 FETCH c_dup_user INTO l_dup_user;
981 CLOSE c_dup_user;
982
983 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
984 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
985 role_users => l_user_name);
986 END IF;
987
988
989
990 -- now set this role to the workflow
991 Wf_Engine.SetItemAttrText( ItemType => itemtype,
992 ItemKey => itemkey,
993 aname => 'IA_ROLE',
994 avalue => l_role_name
995 );
996
997 Resultout:= 'COMPLETE:';
998 RETURN;
999 END IF;
1000
1001 END intermission_not;
1002
1003 PROCEDURE progtrans_not (
1004 itemtype IN VARCHAR2 ,
1005 itemkey IN VARCHAR2 ,
1006 actid IN NUMBER ,
1007 funcmode IN VARCHAR2 ,
1008 resultout OUT NOCOPY VARCHAR2 ) AS
1009
1010 ------------------------------------------------------------------------------------------------
1011 -- Created by : Deepankar Dey, Oracle India (in)
1012 -- Date created: 30-04-2003
1013 --
1014 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
1015 --
1016 --
1017 --
1018 -- Known limitations/enhancements and/or remarks:
1019 --
1020 -- Change History:
1021 -- Who When What
1022 --
1023 -------------------------------------------------------------------------------------------------
1024 l_date_prod VARCHAR2(30);
1025 l_doc_type VARCHAR2(30);
1026 l_role_name VARCHAR2(320);
1027 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSEN003';
1028 l_person_id_stu VARCHAR2(4000);
1029 l_person_id_app VARCHAR2(4000);
1030 l_person_id VARCHAR2(30);
1031
1032 -- cursor to get the user_name corresponding to the person_id
1033
1034 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
1035 SELECT user_name
1036 FROM fnd_user
1037 WHERE person_party_id = cp_person_id
1038 AND ( end_date IS NULL OR end_date > SYSDATE );
1039
1040 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
1041 SELECT user_name
1042 FROM fnd_user
1043 WHERE user_id = cp_person_id
1044 AND ( end_date IS NULL OR end_date > SYSDATE );
1045
1046
1047 l_user_name fnd_user.user_name%TYPE;
1048 l_user_name_admin fnd_user.user_name%TYPE;
1049
1050 CURSOR c_dup_user (cp_user_name VARCHAR2,
1051 cp_role_name VARCHAR2) IS
1052 SELECT count(1)
1053 FROM WF_LOCAL_USER_ROLES
1054 WHERE USER_NAME = cp_user_name
1055 AND ROLE_NAME = cp_role_name
1056 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
1057 AND ROLE_ORIG_SYSTEM_ID = 0;
1058
1059 l_dup_user NUMBER :=0;
1060
1061 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1062 SELECT full_name
1063 FROM igs_pe_person_base_v
1064 WHERE person_id = cp_person_id;
1065
1066 l_c_full_name c_full_name%ROWTYPE;
1067
1068 BEGIN
1069
1070
1071
1072 IF (funcmode = 'RUN') THEN
1073 -- create the adhoc role
1074
1075 l_role_name := 'IGS' || substr(itemkey,6);
1076
1077 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
1078 role_display_name => l_role_display_name
1079 );
1080
1081
1082 --
1083 -- fetch student for whom the record has been procesed and add the user name to the
1084 -- adhoc role
1085 --
1086 --
1087
1088 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
1089 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
1090
1091
1092 -- Getting the Full Name of the Student
1093
1094 OPEN c_full_name(l_person_id_stu);
1095 FETCH c_full_name INTO l_c_full_name;
1096 CLOSE c_full_name;
1097
1098 -- Setting this full name of the student
1099
1100 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1101 ItemKey => itemkey,
1102 aname => 'IA_PERSONNAME',
1103 avalue => l_c_full_name.full_name
1104 );
1105
1106 -- Checking for User Name for the corresponding person ID of a student
1107
1108 OPEN c_user_name (l_person_id_stu);
1109 FETCH c_user_name INTO l_user_name;
1110 CLOSE c_user_name;
1111
1112
1113
1114 -- add this user name to the adhoc role if it is not null and unique
1115 OPEN c_dup_user(l_user_name,l_role_name);
1116 FETCH c_dup_user INTO l_dup_user;
1117 CLOSE c_dup_user;
1118
1119 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
1120 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1121 role_users => l_user_name);
1122 END IF;
1123
1124
1125 -- Checking for User Name for the corresponding person ID of a Admin
1126 OPEN c_user_name_admin (l_person_id_app);
1127 FETCH c_user_name_admin INTO l_user_name_admin;
1128 CLOSE c_user_name_admin;
1129
1130 -- add this user name to the adhoc role if it is not null and unique
1131 OPEN c_dup_user(l_user_name_admin,l_role_name);
1132 FETCH c_dup_user INTO l_dup_user;
1133 CLOSE c_dup_user;
1134
1135 IF l_user_name_admin IS NOT NULL AND l_dup_user = 0 THEN
1136 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1137 role_users => l_user_name_admin);
1138 END IF;
1139
1140 -- now set this role to the workflow
1141 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1142 ItemKey => itemkey,
1143 aname => 'IA_ROLE',
1144 avalue => l_role_name
1145 );
1146
1147 Resultout:= 'COMPLETE:';
1148 RETURN;
1149 END IF;
1150
1151
1152 END progtrans_not;
1153
1154 PROCEDURE progofropt_not (
1155 itemtype IN VARCHAR2 ,
1156 itemkey IN VARCHAR2 ,
1157 actid IN NUMBER ,
1158 funcmode IN VARCHAR2 ,
1159 resultout OUT NOCOPY VARCHAR2 ) AS
1160
1161 ------------------------------------------------------------------------------------------------
1162 -- Created by : Deepankar Dey, Oracle India (in)
1163 -- Date created: 30-04-2003
1164 --
1165 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
1166 --
1167 --
1168 --
1169 -- Known limitations/enhancements and/or remarks:
1170 --
1171 -- Change History:
1172 -- Who When What
1173 --
1174 -------------------------------------------------------------------------------------------------
1175 l_date_prod VARCHAR2(30);
1176 l_doc_type VARCHAR2(30);
1177 l_role_name VARCHAR2(320);
1178 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSEN004';
1179 l_person_id_stu VARCHAR2(4000);
1180 l_person_id_app VARCHAR2(4000);
1181 l_person_id VARCHAR2(30);
1182
1183 -- cursor to get the user_name corresponding to the person_id
1184
1185 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
1186 SELECT user_name
1187 FROM fnd_user
1188 WHERE person_party_id = cp_person_id;
1189
1190 -- cursor to get the user_name corresponding to the user_id
1191
1192 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
1193 SELECT user_name
1194 FROM fnd_user
1195 WHERE user_id = cp_person_id
1196 AND ( end_date IS NULL OR end_date > SYSDATE );
1197
1198 l_user_name fnd_user.user_name%TYPE;
1199 l_user_name_admin fnd_user.user_name%TYPE;
1200
1201 CURSOR c_dup_user (cp_user_name VARCHAR2,
1202 cp_role_name VARCHAR2) IS
1203 SELECT count(1)
1204 FROM WF_LOCAL_USER_ROLES
1205 WHERE USER_NAME = cp_user_name
1206 AND ROLE_NAME = cp_role_name
1207 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
1208 AND ROLE_ORIG_SYSTEM_ID = 0;
1209
1210 l_dup_user NUMBER :=0;
1211
1212 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1213 SELECT full_name
1214 FROM igs_pe_person_base_v
1215 WHERE person_id = cp_person_id;
1216
1217 l_c_full_name c_full_name%ROWTYPE;
1218
1219 BEGIN
1220
1221
1222
1223 IF (funcmode = 'RUN') THEN
1224 -- create the adhoc role
1225 l_role_name := 'IGS'||substr(itemkey,6);
1226
1227 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
1228 role_display_name => l_role_display_name
1229 );
1230
1231
1232 --
1233 -- fetch student for whom the record has been procesed and add the user name to the
1234 -- adhoc role
1235 --
1236 --
1237
1238 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
1239 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
1240
1241 -- Getting the Full Name of the Student
1242
1243 OPEN c_full_name(l_person_id_stu);
1244 FETCH c_full_name INTO l_c_full_name;
1245 CLOSE c_full_name;
1246
1247
1248 -- Setting this full name of the student
1249
1250 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1251 ItemKey => itemkey,
1252 aname => 'IA_PERSONNAME',
1253 avalue => l_c_full_name.full_name
1254 );
1255
1256
1257
1258 -- Checking for User Name for the corresponding person ID of a student
1259
1260 OPEN c_user_name (l_person_id_stu);
1261 FETCH c_user_name INTO l_user_name;
1262 CLOSE c_user_name;
1263
1264
1265 -- add this user name to the adhoc role if it is not null and unique
1266 OPEN c_dup_user(l_user_name,l_role_name);
1267 FETCH c_dup_user INTO l_dup_user;
1268 CLOSE c_dup_user;
1269
1270 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
1271 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1272 role_users => l_user_name);
1273 END IF;
1274
1275
1276 -- Checking for User Name for the corresponding person ID of a Admin
1277 OPEN c_user_name_admin (l_person_id_app);
1278 FETCH c_user_name_admin INTO l_user_name_admin;
1279 CLOSE c_user_name_admin;
1280
1281 -- add this user name to the adhoc role if it is not null and unique
1282 OPEN c_dup_user(l_user_name_admin,l_role_name);
1283 FETCH c_dup_user INTO l_dup_user;
1284 CLOSE c_dup_user;
1285
1286
1287
1288 IF l_user_name_admin IS NOT NULL AND l_dup_user = 0 THEN
1289 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1290 role_users => l_user_name_admin);
1291 END IF;
1292
1293 -- now set this role to the workflow
1294 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1295 ItemKey => itemkey,
1296 aname => 'IA_ROLE',
1297 avalue => l_role_name
1298 );
1299
1300 Resultout:= 'COMPLETE:';
1301 RETURN;
1302 END IF;
1303
1304
1305 END progofropt_not;
1306
1307 PROCEDURE progdiscont_not (
1308 itemtype IN VARCHAR2 ,
1309 itemkey IN VARCHAR2 ,
1310 actid IN NUMBER ,
1311 funcmode IN VARCHAR2 ,
1312 resultout OUT NOCOPY VARCHAR2 ) AS
1313
1314
1315 ------------------------------------------------------------------------------------------------
1316 -- Created by : Deepankar Dey, Oracle India (in)
1317 -- Date created: 30-04-2003
1318 --
1319 -- Purpose:Bug # 2829275 . Creates the User Role and find the full name of the student.
1320 --
1321 --
1322 --
1323 -- Known limitations/enhancements and/or remarks:
1324 --
1325 -- Change History:
1326 -- Who When What
1327 --
1328 -------------------------------------------------------------------------------------------------
1329 l_date_prod VARCHAR2(30);
1330 l_doc_type VARCHAR2(30);
1331 l_role_name VARCHAR2(320);
1332 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSEN005';
1333 l_person_id_stu VARCHAR2(4000);
1334 l_person_id_app VARCHAR2(4000);
1335 l_person_id VARCHAR2(30);
1336
1337 -- cursor to get the user_name corresponding to the person_id
1338
1339 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
1340 SELECT user_name
1341 FROM fnd_user
1342 WHERE person_party_id = cp_person_id
1343 AND ( end_date IS NULL OR end_date > SYSDATE );
1344
1345 CURSOR c_user_name_admin (cp_person_id fnd_user.user_id%TYPE) IS
1346 SELECT user_name
1347 FROM fnd_user
1348 WHERE user_id = cp_person_id
1349 AND ( end_date IS NULL OR end_date > SYSDATE );
1350
1351
1352 l_user_name fnd_user.user_name%TYPE;
1353 l_user_name_admin fnd_user.user_name%TYPE;
1354
1355 CURSOR c_dup_user (cp_user_name VARCHAR2,
1356 cp_role_name VARCHAR2) IS
1357 SELECT count(1)
1358 FROM WF_LOCAL_USER_ROLES
1359 WHERE USER_NAME = cp_user_name
1360 AND ROLE_NAME = cp_role_name
1361 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
1362 AND ROLE_ORIG_SYSTEM_ID = 0;
1363
1364 l_dup_user NUMBER :=0;
1365
1366 CURSOR c_full_name (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1367 SELECT full_name
1368 FROM igs_pe_person_base_v
1369 WHERE person_id = cp_person_id;
1370
1371 -- Get the description of discontinuation reason code
1372 CURSOR c_discon_rsn (cp_discon_rsn_cd IGS_EN_DCNT_REASONCD.DISCONTINUATION_REASON_CD%TYPE) IS
1373 SELECT description
1374 FROM IGS_EN_DCNT_REASONCD
1375 WHERE DISCONTINUATION_REASON_CD = cp_discon_rsn_cd;
1376
1377 -- Get the description of discontinuation reason type
1378 CURSOR c_discon_type (cp_discon_rsn_type igs_lookup_values.lookup_code%TYPE) IS
1379 SELECT meaning
1380 FROM IGS_LOOKUP_VALUES
1381 WHERE lookup_code = cp_discon_rsn_type
1382 AND lookup_type = 'DISCONTINUATION_REASON_TYPE';
1383
1384 l_c_discon_rsn c_discon_rsn%ROWTYPE;
1385 l_c_discon_type c_discon_type%ROWTYPE;
1386 l_c_full_name c_full_name%ROWTYPE;
1387
1388 l_discon_rsn_cd IGS_EN_DCNT_REASONCD.DISCONTINUATION_REASON_CD%TYPE;
1389 l_discon_rsn_type igs_lookup_values.lookup_code%TYPE;
1390
1391 BEGIN
1392
1393
1394
1395 IF (funcmode = 'RUN') THEN
1396 -- create the adhoc role
1397 l_role_name := 'IGS'||substr(itemkey,6);
1398
1399 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
1400 role_display_name => l_role_display_name
1401 );
1402
1403
1404 --
1405 -- fetch student for whom the record has been procesed and add the user name to the
1406 -- adhoc role
1407 --
1408 --
1409
1410 l_person_id_stu := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSONID');
1411 l_person_id_app := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN');
1412 l_discon_rsn_cd := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_DISCONTINCD');
1413 l_discon_rsn_type := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_DISCONTINTYPE');
1414
1415 -- getting the description of discontinuation reason code
1416 -- and setting it in internal attribute
1417
1418 OPEN c_discon_rsn(l_discon_rsn_cd);
1419 FETCH c_discon_rsn INTO l_c_discon_rsn;
1420 CLOSE c_discon_rsn;
1421
1422 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1423 ItemKey => itemkey,
1424 aname => 'IA_DISCON_RSN_DESC',
1425 avalue => l_c_discon_rsn.description
1426 );
1427
1428
1429 -- getting the description of discontinuation type
1430 -- and setting it in internal attribute
1431
1432 OPEN c_discon_type(l_discon_rsn_type);
1433 FETCH c_discon_type INTO l_c_discon_type;
1434 CLOSE c_discon_type;
1435
1436 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1437 ItemKey => itemkey,
1438 aname => 'IA_DISCON_TYPE_DESC',
1439 avalue => l_c_discon_type.meaning
1440 );
1441
1442 -- Getting the Full Name of the Student
1443
1444 OPEN c_full_name(l_person_id_stu);
1445 FETCH c_full_name INTO l_c_full_name;
1446 CLOSE c_full_name;
1447
1448 -- Setting this full name of the student
1449
1450 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1451 ItemKey => itemkey,
1452 aname => 'IA_PERSONNAME',
1453 avalue => l_c_full_name.full_name
1454 );
1455
1456 -- Checking for User Name for the corresponding person ID of a student
1457
1458 OPEN c_user_name (l_person_id_stu);
1459 FETCH c_user_name INTO l_user_name;
1460 CLOSE c_user_name;
1461
1462 IF l_user_name IS NULL THEN
1463
1464
1465 -- Checking for User Name for the corresponding person ID of a Admin
1466 OPEN c_user_name_admin (l_person_id_app);
1467 FETCH c_user_name_admin INTO l_user_name_admin ;
1468 CLOSE c_user_name_admin ;
1469
1470 -- Add this user name to the adhoc role if it is not null and unique
1471
1472 OPEN c_dup_user(l_user_name_admin,l_role_name);
1473 FETCH c_dup_user INTO l_dup_user;
1474 CLOSE c_dup_user;
1475
1476 IF l_user_name_admin IS NOT NULL AND l_dup_user = 0 THEN
1477 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1478 role_users => l_user_name_admin);
1479 END IF;
1480
1481 resultout := 'COMPLETE:N' ;
1482
1483 ELSE
1484
1485
1486 -- add this user name to the adhoc role if it is not null and unique
1487 OPEN c_dup_user(l_user_name,l_role_name);
1488 FETCH c_dup_user INTO l_dup_user;
1489 CLOSE c_dup_user;
1490
1491 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
1492 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1493 role_users => l_user_name);
1494 END IF;
1495
1496
1497 -- Checking for User Name for the corresponding person ID of a Admin
1498 OPEN c_user_name_admin (l_person_id_app);
1499 FETCH c_user_name_admin INTO l_user_name_admin;
1500 CLOSE c_user_name_admin ;
1501
1502 -- add this user name to the adhoc role if it is not null and unique
1503
1504 OPEN c_dup_user(l_user_name_admin,l_role_name);
1505 FETCH c_dup_user INTO l_dup_user;
1506 CLOSE c_dup_user;
1507
1508 IF l_user_name_admin IS NOT NULL AND l_dup_user = 0 THEN
1509 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
1510 role_users => l_user_name_admin);
1511 END IF;
1512
1513 resultout := 'COMPLETE:Y' ;
1514
1515 END IF;
1516
1517 -- now set this role to the workflow
1518
1519 Wf_Engine.SetItemAttrText( ItemType => itemtype,
1520 ItemKey => itemkey,
1521 aname => 'IA_ROLE',
1522 avalue => l_role_name
1523 );
1524
1525 RETURN;
1526 END IF;
1527
1528 IF ( funcmode = 'CANCEL' ) THEN
1529 resultout := 'COMPLETE' ;
1530 return;
1531 END IF;
1532
1533 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1534 resultout := '' ;
1535 return;
1536 END IF;
1537
1538 END progdiscont_not;
1539
1540 PROCEDURE student_placement_event(p_person_id IN NUMBER ,
1541 p_program_cd IN VARCHAR2,
1542 p_unit_cd IN VARCHAR2,
1543 p_unit_class IN VARCHAR2,
1544 p_location_cd IN VARCHAR2,
1545 p_uoo_id IN NUMBER)
1546 IS
1547 ------------------------------------------------------------------------------------------------
1548 -- Created by : Kamalakar, Oracle India (in)
1549 -- Date created: 18-Nov-2003
1550 --
1551 -- Purpose: Business event is raised to send notiifcation to Admin, when a student attempts a placement unit
1552 -- This procedure is called from unit attempt TBH(IGSEI36B)
1553 --
1554 -- Known limitations/enhancements and/or remarks:
1555 --
1556 -- Change History:
1557 -- Who When What
1558 --
1559 -------------------------------------------------------------------------------------------------
1560
1561 -- Gets a unique sequence number
1562 CURSOR c_seq_num IS
1563 SELECT igs_en_student_placement_s.NEXTVAL
1564 FROM dual;
1565
1566 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
1567 CURSOR cur_prof_value IS
1568 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
1569 FROM dual;
1570
1571 -- Cursor to fetch the person number
1572 CURSOR cur_pers_number(cp_person_id NUMBER) IS
1573 SELECT party_number
1574 FROM hz_parties
1575 WHERE party_id = cp_person_id;
1576
1577 l_cur_prof_value cur_prof_value%ROWTYPE;
1578 l_cur_pers_number cur_pers_number%ROWTYPE;
1579 l_event_t wf_event_t;
1580 l_parameter_list_t wf_parameter_list_t;
1581 l_itemKey varchar2(100);
1582 ln_seq_val NUMBER;
1583
1584 BEGIN
1585
1586 -- Checking if the Workflow is installed at the environment or not.
1587 OPEN cur_prof_value;
1588 FETCH cur_prof_value INTO l_cur_prof_value;
1589 CLOSE cur_prof_value;
1590
1591 IF (l_cur_prof_value.value = 'Y') THEN
1592
1593 -- Get the sequence value
1594 OPEN c_seq_num;
1595 FETCH c_seq_num INTO ln_seq_val ;
1596 CLOSE c_seq_num ;
1597
1598 -- Getting the Person number for the given person id
1599 OPEN cur_pers_number(p_person_id);
1600 FETCH cur_pers_number INTO l_cur_pers_number;
1601 CLOSE cur_pers_number;
1602
1603 --
1604 -- initialize the wf_event_t object
1605 --
1606 wf_event_t.Initialize(l_event_t);
1607
1608 --
1609 -- Adding the parameters to the parameter list
1610 --
1611 wf_event.AddParameterToList (p_name => 'STUDENT_ID',p_value=>p_person_id,p_parameterlist=>l_parameter_list_t);
1612 wf_event.AddParameterToList (p_name => 'PROGRAM_CD', p_Value => p_program_cd, p_ParameterList => l_parameter_list_t);
1613 wf_event.AddParameterToList (p_name => 'UNIT_CD', p_Value => p_unit_cd, p_ParameterList => l_parameter_list_t);
1614 wf_event.AddParameterToList (p_name => 'UNIT_CLASS', p_Value => p_unit_class, p_ParameterList => l_parameter_list_t);
1615 wf_event.AddParameterToList (p_name => 'LOCATION_CD', p_Value => p_location_cd, p_ParameterList => l_parameter_list_t);
1616 wf_event.AddParameterToList (p_name => 'UOO_ID', p_Value => p_uoo_id, p_ParameterList => l_parameter_list_t);
1617 wf_event.AddParameterToList (p_name => 'STUDENT_NUMBER',p_Value =>l_cur_pers_number.party_number,p_ParameterList =>l_parameter_list_t);
1618 wf_event.AddParameterToList (p_name => 'P_ADMIN', p_Value => 'SYSADMIN', p_ParameterList => l_parameter_list_t);
1619
1620 -- Raise the Event
1621 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.en.student.placemnt',
1622 p_event_key => 'STUDPLCMNT'||ln_seq_val,
1623 p_parameters => l_parameter_list_t);
1624 --
1625 -- Deleting the Parameter list after the event is raised
1626 --
1627 l_parameter_list_t.delete;
1628 END IF;
1629
1630 END student_placement_event;
1631
1632
1633 procedure raise_withdraw_perm_evt (p_n_uoo_id IN NUMBER,
1634 p_c_load_cal IN VARCHAR2,
1635 p_n_load_seq_num IN NUMBER,
1636 p_n_person_id IN NUMBER,
1637 p_c_course_cd IN VARCHAR2,
1638 p_c_approval_type IN VARCHAR2)
1639 ------------------------------------------------------------------
1640 --Created by : Vijay Rajagopal, Oracle IDC
1641 --Date created: 11-JUN-2005
1642 --
1643 --Purpose: This procedure raises the withraw permission workflow
1644 --
1645 --Known limitations/enhancements and/or remarks:
1646 --
1647 --Change History:
1648 --Who When What
1649 -------------------------------------------------------------------
1650 IS
1651
1652 l_n_key NUMBER;
1653 l_wf_event_t WF_EVENT_T;
1654 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1655
1656 CURSOR c_uoo( cp_n_uoo_id IN NUMBER,
1657 p_c_load_cal IN VARCHAR2,
1658 p_n_load_seq_num IN NUMBER) IS
1659 SELECT uoo.unit_cd || '/' || uoo.unit_class AS UNIT_SECTION,
1660 uoo.unit_cd,
1661 uoo.version_number,
1662 uoo.cal_type,
1663 uoo.ci_sequence_number,
1664 uoo.location_cd,
1665 uoo.unit_class,
1666 ca.teach_description || ' (' || ca.teach_start_dt || ' - ' || ca.teach_end_dt || ')' teaching_prd,
1667 ca.load_description
1668 FROM IGS_PS_UNIT_OFR_OPT_ALL uoo,
1669 IGS_CA_TEACH_TO_LOAD_V ca
1670 WHERE uoo.cal_type = ca.teach_cal_type
1671 AND uoo.ci_sequence_number = ca.teach_ci_sequence_number
1672 AND uoo.uoo_id = cp_n_uoo_id
1673 AND ca.load_cal_type = p_c_load_cal
1674 AND ca.load_ci_sequence_number = p_n_load_seq_num;
1675
1676 rec_uoo c_uoo%ROWTYPE;
1677
1678 CURSOR c_usec_tch_resp (cp_n_uoo_id IN NUMBER) IS
1679 SELECT usec.instructor_id
1680 FROM igs_ps_usec_tch_resp usec
1681 WHERE usec.uoo_id = cp_n_uoo_id
1682 AND usec.lead_instructor_flag = 'Y';
1683
1684 CURSOR c_person_number (cp_n_person_id IN NUMBER) IS
1685 SELECT person_number
1686 FROM igs_pe_person_base_v
1687 WHERE person_id = cp_n_person_id;
1688
1689 CURSOR c_user_name (cp_n_person_id IN NUMBER) IS
1690 SELECT user_name
1691 FROM fnd_user
1692 WHERE person_party_id = cp_n_person_id;
1693
1694 l_t_temp NUMBER;
1695 l_c_person_num igs_pe_person_base_v.person_number%TYPE;
1696 l_n_instuctor_id igs_ps_usec_tch_resp.instructor_id%TYPE;
1697 l_c_user_name fnd_user.user_name%TYPE;
1698 l_c_perm_type VARCHAR2(2000);
1699
1700 BEGIN
1701 -- initialize the wf_event_t object
1702 --
1703 WF_EVENT_T.Initialize(l_wf_event_t);
1704 --
1705 -- set the event name
1706 --
1707 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.en.withdraw_perm');
1708 --
1709 -- event key to identify uniquely
1710 --
1711 -- set the parameter list
1712 --
1713 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
1714 --
1715 -- now add the parameters to the parameter list
1716
1717 OPEN c_uoo(p_n_uoo_id,p_c_load_cal, p_n_load_seq_num);
1718 FETCH c_uoo INTO rec_uoo;
1719 CLOSE c_uoo;
1720
1721 OPEN c_usec_tch_resp (p_n_uoo_id);
1722 FETCH c_usec_tch_resp INTO l_n_instuctor_id;
1723 CLOSE c_usec_tch_resp;
1724
1725 OPEN c_person_number(p_n_person_id);
1726 FETCH c_person_number INTO l_c_person_num;
1727 CLOSE c_person_number;
1728
1729 OPEN c_user_name (l_n_instuctor_id);
1730 FETCH c_user_name INTO l_c_user_name;
1731 CLOSE c_user_name;
1732
1733 IF p_c_approval_type = 'AUDIT_PERM' THEN
1734 fnd_message.set_name('IGS','IGS_EN_SPL_AUDIT_LINK');
1735 ELSE
1736 fnd_message.set_name('IGS','IGS_EN_SS_SPECIAL_PERM');
1737 END IF;
1738
1739 l_c_perm_type := fnd_message.get;
1740
1741 wf_event.AddParameterToList ( p_name => 'PERSON_ID', p_value =>p_n_person_id , p_parameterlist => l_wf_parameter_list_t);
1742 wf_event.AddParameterToList ( p_name => 'PERSON_NUMBER', p_value =>l_c_person_num, p_parameterlist => l_wf_parameter_list_t);
1743 wf_event.AddParameterToList ( p_name => 'UOO_ID', p_value =>p_n_uoo_id , p_parameterlist => l_wf_parameter_list_t);
1744 wf_event.AddParameterToList ( p_name => 'UNIT_CD', p_value =>rec_uoo.unit_cd, p_parameterlist => l_wf_parameter_list_t);
1745 wf_event.AddParameterToList ( p_name => 'VERSION_NUMBER', p_value =>rec_uoo.version_number, p_parameterlist => l_wf_parameter_list_t);
1746 wf_event.AddParameterToList ( p_name => 'UNIT_LOCATION', p_value =>rec_uoo.location_cd, p_parameterlist => l_wf_parameter_list_t);
1747 wf_event.AddParameterToList ( p_name => 'UNIT_CLASS', p_value =>rec_uoo.unit_class, p_parameterlist => l_wf_parameter_list_t);
1748 wf_event.AddParameterToList ( p_name => 'TEACHING_PRD', p_value =>rec_uoo.teaching_prd, p_parameterlist => l_wf_parameter_list_t);
1749 wf_event.AddParameterToList ( p_name => 'LOAD_CAL_DESC', p_value =>rec_uoo.load_description, p_parameterlist => l_wf_parameter_list_t);
1750 wf_event.AddParameterToList ( p_name => 'PERM_TYPE', p_value =>l_c_perm_type, p_parameterlist => l_wf_parameter_list_t);
1751 wf_event.AddParameterToList ( p_name => 'UNIT_SECTION', p_value =>rec_uoo.unit_section, p_parameterlist => l_wf_parameter_list_t);
1752 wf_event.AddParameterToList ( p_name => 'LEAD_INSTRUCTOR', p_value =>l_c_user_name, p_parameterlist => l_wf_parameter_list_t);
1753
1754 --
1755 -- raise the event
1756
1757 SELECT igs_en_withdraw_perm_S.nextval INTO l_t_temp from dual;
1758
1759 wf_event.raise (
1760 p_event_name => 'oracle.apps.igs.en.withdraw_perm',
1761 p_event_key => 'WITHDRAW'||l_t_temp,
1762 p_parameters => l_wf_parameter_list_t
1763 );
1764 EXCEPTION
1765 WHEN OTHERS THEN
1766 ROLLBACK;
1767 fnd_file.put_line(fnd_file.log,sqlerrm);
1768 END raise_withdraw_perm_evt;
1769
1770 PROCEDURE raise_spi_rcond_event ( p_person_id IN NUMBER,
1771 p_program_cd IN VARCHAR2,
1772 p_intm_type IN VARCHAR2,
1773 p_changed_rconds IN VARCHAR2,
1774 p_changed_rconds_desc IN VARCHAR2) IS
1775
1776 ------------------------------------------------------------------
1777 --Created by : Basanth Devisetty, Oracle IDC
1778 --Date created: 11-JUN-2005
1779 --
1780 --Purpose: This procedure raises the Student Intremission Return Condition
1781 -- Status Change Event.(Bug# 5083465)
1782 --
1783 --Known limitations/enhancements and/or remarks:
1784 --
1785 --Change History:
1786 --Who When What
1787 -------------------------------------------------------------------
1788
1789
1790 CURSOR cur_seq_val IS
1791 SELECT igs_en_intm_rcond_s.nextval seq_val
1792 FROM DUAL;
1793
1794 CURSOR c_person_dtls (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
1795 SELECT party_number,party_name
1796 FROM hz_parties
1797 WHERE party_id = cp_person_id;
1798
1799 l_person_number hz_parties.party_number%TYPE;
1800 l_person_name hz_parties.party_name%TYPE;
1801 l_user_name fnd_user.user_name%TYPE;
1802
1803 l_cur_seq_val cur_seq_val%ROWTYPE;
1804 l_wf_parameter_list_t WF_PARAMETER_LIST_T:= wf_parameter_list_t();
1805 l_wf_installed fnd_lookups.lookup_code%TYPE;
1806
1807 BEGIN
1808 -- get the profile value that is set for checking if workflow is installed
1809 fnd_profile.get('IGS_WF_ENABLE',l_wf_installed);
1810
1811 -- if workflow is installed then carry on with the raising an event
1812 IF (RTRIM(l_wf_installed) ='Y') THEN
1813 -- get the next value of the sequence
1814 OPEN cur_seq_val;
1815 FETCH cur_seq_val INTO l_cur_seq_val;
1816 CLOSE cur_seq_val;
1817
1818 OPEN c_person_dtls ( p_person_id);
1819 FETCH c_person_dtls INTO l_person_number,l_person_name;
1820 CLOSE c_person_dtls;
1821
1822 l_user_name := FND_GLOBAL.USER_NAME;
1823
1824 -- set the event parameters
1825 wf_event.AddParameterToList(p_name=>'P_PERSON_ID', p_value=>p_person_id, p_parameterlist=>l_wf_parameter_list_t);
1826 wf_event.AddParameterToList(p_name=>'P_PROGRAM_CD', p_value=>p_program_cd, p_parameterlist=>l_wf_parameter_list_t);
1827 wf_event.AddParameterToList(p_name=>'P_INTM_TYPE', p_value=>p_intm_type, p_parameterlist=>l_wf_parameter_list_t);
1828 wf_event.AddParameterToList(p_name=>'P_RCOND_CHG', p_value=>p_changed_rconds, p_parameterlist=>l_wf_parameter_list_t);
1829 wf_event.AddParameterToList(p_name=>'P_RCOND_CHG_DESC', p_value=>p_changed_rconds_desc, p_parameterlist=>l_wf_parameter_list_t);
1830 wf_event.AddParameterToList(p_name=>'P_FULL_NAME', p_value=>l_person_name, p_parameterlist=>l_wf_parameter_list_t);
1831 wf_event.AddParameterToList(p_name=>'P_PERSON_NUMBER', p_value=>l_person_number, p_parameterlist=>l_wf_parameter_list_t);
1832 wf_event.AddParameterToList(p_name=>'P_USER_NAME', p_value=>l_user_name, p_parameterlist=>l_wf_parameter_list_t);
1833
1834 -- raise the event
1835 WF_EVENT.RAISE(p_event_name=>'oracle.apps.igs.en.prog.intm.rcond',
1836 p_event_key =>'INTMRCOND'||l_cur_seq_val.seq_val,
1837 p_parameters=>l_wf_parameter_list_t);
1838 END IF;
1839
1840 END raise_spi_rcond_event;
1841
1842 END igs_en_workflow;
[Home] [Help]