1 PACKAGE BODY igs_as_notify_student AS
2 /* $Header: IGSAS50B.pls 120.0 2005/07/05 11:30:00 appldev noship $ */
3
4
5
6 PROCEDURE wf_notify_student(
7 p_busEvent IN VARCHAR2,
8 p_name IN VARCHAR2,
9 p_users IN VARCHAR2,
10 p_subject IN VARCHAR2,
11 p_message IN VARCHAR2) AS
12 /******************************************************************
13 Created By : Deepankar Dey
14 Date Created By : 21-Sept-2002
15 Purpose : This procedure will be used for launching
16 the workflow process.
17 Change History
18 Who When What
19 ******************************************************************/
20
21
22 l_event_t wf_event_t;
23 l_parameter_list_t wf_parameter_list_t;
24 l_itemKey varchar2(100);
25 ln_seq_val NUMBER;
26
27 -- Gets a unique sequence number
28 CURSOR
29 c_seq_num
30 IS
31 SELECT
32 igs_as_wf_beas006_s.NEXTVAL
33 FROM
34 dual;
35
36
37
38 BEGIN
39
40
41
42 -- Get the sequence value
43 OPEN c_seq_num;
44 FETCH c_seq_num INTO ln_seq_val ;
45 CLOSE c_seq_num ;
46
47
48 --
49 -- initialize the wf_event_t object
50 --
51
52 wf_event_t.Initialize(l_event_t);
53
54
55
56 --
57 -- Adding the parameters to the parameter list
58 --
59
60
61 wf_event.AddParameterToList (p_name=>'IA_NAME',p_value=>p_name,p_parameterlist=>l_parameter_list_t);
62 wf_event.AddParameterToList ( p_Name => 'IA_USERS', p_Value => p_users, p_ParameterList => l_parameter_list_t);
63 wf_event.AddParameterToList ( p_Name => 'IA_SUBJECT', p_Value => p_subject, p_ParameterList => l_parameter_list_t);
64 wf_event.AddParameterToList ( p_Name => 'IA_MESSAGE', p_Value => p_message, p_ParameterList => l_parameter_list_t);
65
66
67 -- Raise the Event
68
69
70
71 WF_EVENT.RAISE (p_event_name => p_busEvent,
72 p_event_key => 'AS006'||ln_seq_val,
73 p_parameters => l_parameter_list_t);
74
75
76 --
77 -- Deleting the Parameter list after the event is raised
78 --
79
80 l_parameter_list_t.delete;
81
82 END wf_notify_student;
83
84 PROCEDURE wf_set_role (itemtype IN VARCHAR2 ,
85 itemkey IN VARCHAR2 ,
86 actid IN NUMBER ,
87 funcmode IN VARCHAR2 ,
88 resultout OUT NOCOPY VARCHAR2
89 ) AS
90
91 /******************************************************************
92 Created By : Deepankar Dey
93 Date Created By : 21-Sept-2002
94 Purpose : This workflow procedure is a wrapper procedure,
95 which will be called from the workflow builder
96 IGSAS006. This would set the adhoc role for the
97 notification
98 Remarks :
99 Change History
100 Who When What
101 ******************************************************************/
102
103 l_date_prod VARCHAR2(30);
104 l_doc_type VARCHAR2(30);
105 l_role_name VARCHAR2(320);
106 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSAS006';
107 l_person_id_sep VARCHAR2(4000);
108 l_person_id VARCHAR2(30);
109
110 -- cursor to get the user_name corresponding to the person_id
111
112 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
113 SELECT user_name
114 FROM fnd_user
115 WHERE person_party_id = cp_person_id;
116
117 l_user_name fnd_user.user_name%TYPE;
118
119 CURSOR c_dup_user (cp_user_name VARCHAR2,
120 cp_role_name VARCHAR2) IS
121 SELECT count(1)
122 FROM WF_LOCAL_USER_ROLES
123 WHERE USER_NAME = cp_user_name
124 AND ROLE_NAME = cp_role_name
125 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
126 AND ROLE_ORIG_SYSTEM_ID = 0;
127
128 l_dup_user NUMBER :=0;
129
130
131 BEGIN
132
133
134
135 IF (funcmode = 'RUN') THEN
136 -- create the adhoc role
137 l_role_name := 'IGS'||substr(itemkey,6);
138
139 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
140 role_display_name => l_role_display_name
141 );
142
143
144 --
145 -- fetch student for whom the record has been procesed and add the user name to the
146 -- adhoc role
147 --
148 --
149
150 l_person_id_sep := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_USERS');
151
152 WHILE (LENGTH (l_person_id_sep) > 0)
153 LOOP
154 IF (INSTR (l_person_id_sep, ',') > 0) THEN
155 l_person_id := SUBSTR (l_person_id_sep, 1, INSTR (l_person_id_sep, ',') - 1);
156 l_person_id_sep := SUBSTR (l_person_id_sep, INSTR (l_person_id_sep, ',') + 1);
157
158 OPEN c_user_name (l_person_id);
159 FETCH c_user_name INTO l_user_name;
160 CLOSE c_user_name;
161
162 -- add this user name to the adhoc role if it is not null and unique
163 OPEN c_dup_user(l_user_name,l_role_name);
164 FETCH c_dup_user INTO l_dup_user;
165 CLOSE c_dup_user;
166
167 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
168 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
169 role_users => l_user_name);
170 END IF;
171
172 ELSE
173
174 OPEN c_user_name (l_person_id_sep);
175 FETCH c_user_name INTO l_user_name;
176 CLOSE c_user_name;
177
178 -- add this user name to the adhoc role if it is not null and unique
179 OPEN c_dup_user(l_user_name,l_role_name);
180 FETCH c_dup_user INTO l_dup_user;
181 CLOSE c_dup_user;
182
183 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
184 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
185 role_users => l_user_name);
186 END IF;
187
188 l_person_id := l_person_id_sep;
189 l_person_id_sep := NULL;
190 END IF;
191
192
193 END LOOP;
194
195 -- now set this role to the workflow
196 Wf_Engine.SetItemAttrText( ItemType => itemtype,
197 ItemKey => itemkey,
198 aname => 'IA_ADHOCROLE',
199 avalue => l_role_name
200 );
201
202 Resultout:= 'COMPLETE:';
203 RETURN;
204 END IF;
205
206 END wf_set_role;
207
208 PROCEDURE wf_launch_as007 (
209 p_user IN VARCHAR2,
210 p_stud_id IN NUMBER,
211 p_stud_number IN VARCHAR2,
212 p_stud_name IN VARCHAR2,
213 p_order_number IN NUMBER,
214 p_item_number IN NUMBER) IS
215
216
217 lv_item_type VARCHAR2(100) := 'IGSAS007' ;
218 lv_item_key VARCHAR2(100) :=
219 'AS007'||to_char(SYSDATE,'YYYYMMDDHH24MISS');
220
221 BEGIN
222 -- Create the process
223 Wf_Engine.createprocess( ItemType => lv_item_type,
224 ItemKey => lv_item_key,
225 process => 'P_AS007'
226 );
227 -- set the attribuites
228 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
229 ItemKey => lv_item_key,
230 aname => 'IA_USER',
231 avalue => p_user
232 );
233
234 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
235 ItemKey => lv_item_key,
236 aname => 'IA_STUD_ID',
237 avalue => p_stud_id
238 );
239
240 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
241 ItemKey => lv_item_key,
242 aname => 'IA_STUD_NUMBER',
243 avalue => p_stud_number
244 );
245
246 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
247 ItemKey => lv_item_key,
248 aname => 'IA_STUD_NAME',
249 avalue => p_stud_name
250 );
251 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
252 ItemKey => lv_item_key,
253 aname => 'IA_ORDER_NUMBER',
254 avalue => p_order_number
255 );
256
257 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
258 ItemKey => lv_item_key,
259 aname => 'IA_ITEM_NUMBER',
260 avalue => p_item_number
261 );
262
263 Wf_Engine.StartProcess ( ItemType => lv_item_type,
264 ItemKey => lv_item_key
265 );
266
267 -- Handle the exception using WF_CORE.Context
268 EXCEPTION
269
270 WHEN OTHERS THEN
271 Wf_Core.Context('IGS_AS_NOTIFY_STUDENT', 'WF_LAUNCH_AS007',
272 lv_item_type, lv_item_key);
273 RAISE;
274
275 END wf_launch_as007;
276
277
278
279 PROCEDURE raise_rel_subdate_event (
280 p_unit_cd IN VARCHAR2,
281 p_term IN VARCHAR2,
282 p_location IN VARCHAR2,
283 p_title IN VARCHAR2,
284 p_teaching_cal IN VARCHAR2,
285 p_sec_number IN VARCHAR2,
286 p_instructor IN NUMBER,
287 p_ass_id IN NUMBER,
288 p_ass_type IN VARCHAR2,
289 p_reference IN VARCHAR2 ,
290 p_grading_period IN VARCHAR2,
291 p_rel_sub_dt IN VARCHAR2 ,
292 p_event IN VARCHAR2
293 ) IS
294 /*************************************************************
295 Created By :Deepankar
296 Date Created on : 9-Nov-2003
297 Purpose : This procedure will be called to launch workflow IGSAS006.
298 Change History
299 Who When What
300
301 (reverse chronological order - newest change first)
302 ***************************************************************/
303
304 l_event_t wf_event_t;
305 l_parameter_list_t wf_parameter_list_t;
306 l_itemKey varchar2(100);
307 ln_seq_val NUMBER;
308
309 -- Gets a unique sequence number
310
311 CURSOR c_seq_num IS
312 SELECT igs_as_reldate_s.NEXTVAL
313 FROM dual;
314
315
316 -- Getting the Profile value for the profile IGS_WF_ENABLE, to check if workflow is installed in the environment
317
318 CURSOR cur_prof_value IS
319 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
320 FROM dual;
321
322 -- Getting the user name to whom the notification to be send
323
324 CURSOR c_user_name IS
325 SELECT user_name
326 FROM fnd_user
327 WHERE person_party_id = p_instructor;
328
329 -- Getting the logged in User
330
331 CURSOR cur_from_user IS
332 SELECT user_name
333 FROM fnd_user
334 WHERE user_id = (SELECT FND_GLOBAL.USER_ID user_id FROM dual);
335
336 l_cur_prof_value cur_prof_value%ROWTYPE;
337 l_cur_from_user fnd_user.user_name%TYPE;
338 l_c_user_name fnd_user.user_name%TYPE;
339
340 BEGIN
341
342 -- Checking if the Workflow is installed at the environment or not.
343
344 OPEN cur_prof_value;
345 FETCH cur_prof_value INTO l_cur_prof_value;
346 CLOSE cur_prof_value;
347
348 IF (l_cur_prof_value.value = 'Y') THEN
349
350 -- Get the sequence value
351
352 OPEN c_seq_num;
353 FETCH c_seq_num INTO ln_seq_val ;
354 CLOSE c_seq_num ;
355
356 OPEN cur_from_user;
357 FETCH cur_from_user INTO l_cur_from_user;
358 CLOSE cur_from_user ;
359
360 OPEN c_user_name;
361 FETCH c_user_name INTO l_c_user_name;
362 CLOSE c_user_name ;
363
364 --
365 -- initialize the wf_event_t object
366 --
367
368 wf_event_t.Initialize(l_event_t);
369
370
371 --
372 -- Adding the parameters to the parameter list
373 --
374
375 wf_event.AddParameterToList (p_name => 'P_UNIT_CD',p_value=>p_unit_cd,p_parameterlist=>l_parameter_list_t);
376 wf_event.AddParameterToList (p_name => 'P_TERM', p_Value => p_term, p_ParameterList => l_parameter_list_t);
377 wf_event.AddParameterToList (p_name => 'P_LOCATION', p_Value => p_location, p_ParameterList => l_parameter_list_t);
378 wf_event.AddParameterToList (p_name => 'P_TITLE', p_Value => p_title, p_ParameterList => l_parameter_list_t);
379 wf_event.AddParameterToList (p_name => 'P_TEACHING_CAL', p_Value => p_teaching_cal, p_ParameterList => l_parameter_list_t);
380 wf_event.AddParameterToList (p_name => 'P_SEC_NUMBER',p_value=>p_sec_number,p_parameterlist=>l_parameter_list_t);
381 wf_event.AddParameterToList (p_name => 'P_INSTRUCTOR', p_Value => p_instructor, p_ParameterList => l_parameter_list_t);
382 wf_event.AddParameterToList (p_name => 'P_ASS_ID', p_Value => p_ass_type, p_ParameterList => l_parameter_list_t);
383 wf_event.AddParameterToList (p_name => 'P_ASS_TYPE', p_Value => p_title, p_ParameterList => l_parameter_list_t);
384 wf_event.AddParameterToList (p_name => 'P_REFERENCE', p_Value => p_reference, p_ParameterList => l_parameter_list_t);
385 wf_event.AddParameterToList (p_name => 'IA_FROM_ROLE',p_value=>l_c_user_name,p_parameterlist=>l_parameter_list_t);
386 wf_event.AddParameterToList (p_name => 'IA_USER_ROLE', p_Value => l_c_user_name, p_ParameterList => l_parameter_list_t);
387
388
389 IF (p_event = 'RELDATE') THEN
390
391 wf_event.AddParameterToList (p_name => 'P_RELEASE_DT', p_Value => p_rel_sub_dt, p_ParameterList => l_parameter_list_t);
392
393
394 -- Raise the Release Grade Business Event
395
396 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.as.aigrdrel',
397 p_event_key => 'IGSAS010'||ln_seq_val,
398 p_parameters => l_parameter_list_t);
399
400
401
402 ELSIF (p_event = 'SUBDATE') THEN
403
404 wf_event.AddParameterToList (p_name => 'P_SUB_DT', p_Value => p_rel_sub_dt, p_ParameterList => l_parameter_list_t);
405 wf_event.AddParameterToList (p_name => 'P_GRADING_PERIOD', p_Value => p_grading_period, p_ParameterList => l_parameter_list_t);
406
407 -- Raise the Submit Grade Business Event
408
409 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.as.usgrdsub',
410 p_event_key => 'IGSAS011'||ln_seq_val,
411 p_parameters => l_parameter_list_t);
412
413 END IF;
414
415
416 --
417 -- Deleting the Parameter list after the event is raised
418 --
419
420 l_parameter_list_t.delete;
421
422 END IF;
423
424 END raise_rel_subdate_event;
425
426
427 PROCEDURE set_adhoc_role (itemtype IN VARCHAR2 ,
428 itemkey IN VARCHAR2 ,
429 actid IN NUMBER ,
430 funcmode IN VARCHAR2 ,
431 resultout OUT NOCOPY VARCHAR2
432 ) AS
433
434 /******************************************************************
435 Created By : Deepankar Dey
436 Date Created By : 21-Sept-2002
437 Purpose : This workflow procedure is a wrapper procedure,
438 which will be called from the workflow builder
439 IGSAS006. This would set the adhoc role for the
440 notification
441 Remarks :
442 Change History
443 Who When What
444 ******************************************************************/
445
446 l_date_prod VARCHAR2(30);
447 l_doc_type VARCHAR2(30);
448 l_role_name VARCHAR2(320);
449 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSAS0101';
450 l_person_id VARCHAR2(30);
451
452 -- cursor to get the user_name corresponding to the person_id
453
454 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
455 SELECT user_name
456 FROM fnd_user
457 WHERE person_party_id = cp_person_id;
458
459 l_user_name fnd_user.user_name%TYPE;
460
461 CURSOR c_dup_user (cp_user_name VARCHAR2,
462 cp_role_name VARCHAR2) IS
463 SELECT count(1)
464 FROM WF_LOCAL_USER_ROLES
465 WHERE USER_NAME = cp_user_name
466 AND ROLE_NAME = cp_role_name
467 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
468 AND ROLE_ORIG_SYSTEM_ID = 0;
469
470 l_dup_user NUMBER :=0;
471
472
473 BEGIN
474
475
476
477 IF (funcmode = 'RUN') THEN
478 -- create the adhoc role
479 l_role_name := 'IGS'||substr(itemkey,6);
480
481 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
482 role_display_name => l_role_display_name
483 );
484
485
486 --
487 -- fetch student for whom the record has been procesed and add the user name to the
488 -- adhoc role
489 --
490 --
491
492 l_person_id := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_INSTRUCTOR');
493
494 OPEN c_user_name (l_person_id);
495 FETCH c_user_name INTO l_user_name;
496 CLOSE c_user_name;
497
498 -- add this user name to the adhoc role if it is not null and unique
499 OPEN c_dup_user(l_user_name,l_role_name);
500 FETCH c_dup_user INTO l_dup_user;
501 CLOSE c_dup_user;
502
503 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
504 Wf_Directory.AddUsersToAdHocRole (role_name => l_role_name,
505 role_users => l_user_name);
506 END IF;
507
508 -- now set this role to the workflow
509 Wf_Engine.SetItemAttrText( ItemType => itemtype,
510 ItemKey => itemkey,
511 aname => 'IA_USER_ROLE',
512 avalue => l_role_name
513 );
514
515
516 Resultout:= 'COMPLETE:';
517 RETURN;
518 END IF;
519
520 END set_adhoc_role;
521
522
523 END igs_as_notify_student;
[Home] [Help]