1 package body pa_report_workflow_client as
2 /* $Header: PAPRWFCB.pls 120.6.12010000.2 2008/09/16 07:06:59 rthumma ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8
9 FILE NAME : PAPRWFCB.pls
10
11 DESCRIPTION :
12 This file provides client extension procedures for
13 various Status Reports Workflow activities.
14
15 USAGE: sqlplus apps/apps @PAPRWFCB.pls
16
17 PROCEDURES AND PARAMETERS:
18
19 start_workflow
20
21 p_item_type: The workflow item type.
22 p_process_name: Name of the workflow process.
23 p_item_key: The workflow item key.
24 p_version_id: Identifier of the version.
25 x_msg_count: The number of messages being sent.
26 x_msg_data: The content of the message.
27 x_return_status: The return status of the message.
28
29
30 set_report_approver
31
32 p_process: Name of the workflow process.
33 p_item_key: The workflow item key.
34 actid: Identifier of the action.
35 funcmode: Workflow function mode.
36 resultout: Process result.
37
38 set_report_notification_party
39
40 p_item_type: The workflow item type.
41 p_item_key: The workflow item key.
42 p_status: The report status.
43 actid: Identifier of the action.
44 funcmode: Workflow function mode.
45 resultout: Process result.
46
47
48
49 HISTORY : 06/22/00 SYAO Initial Creation
50
51 23-Apr-2003 sacgupta Bug 2911451. Changes for Assignment Type Validation.
52 Included condition Assignment_type ='E' and
53 Primary_flag ='Y'in cursor l_approver_csr for
54 procedure set_report_approver
55
56 17-Feb-2004 sukhanna Bug : 3448380
57 Added Check on assignment_type for CWK Changes.
58
59 07-Feb-2005 rvelusam Bug : 4165764
60 In the procedure set_report_approver added new variable
61 l_approver_name. And changed to code to set the correct
62 'To' field value.
63 05-Aug-2005 raluthra Bug 4527617. Replaced fnd_user.customer_id with
64 fnd_user.person_party_id for R12 ATG Mandate fix.
65 09-Feb-2006 posingha Bug 4530998. Changed the l_org variable from VARCHAR(60) to
66 pa_project_lists_v.carrying_out_organization_name%TYPE to allow
67 longer organization names to be accepted.
68 31-Mar-2006 posingha Bug 5027098, Added code to set the 'From' role attribute
69 value for notifications.
70 22-Feb-2007 posingha Bug 5716959: Changed calls to deprecated
71 WF_DIRECTORY.AddUsersToAdHocRole.
72 instead directly called
73 WF_DIRECTORY.AddUsersToAdHocRole2 API
74 Changes done in : Start_Workflow
75 Set_report_Approver
76 Set_report_notification_party
77 26-Apr-2007 vvjoshi Bug#5962410 : Set an expiration date in CreateAdhocRole
78 procedure call.
79 16-Sep-2008 rthumma Bug 6843694 : In procedure 'set_report_notification_party',
80 changed the calls from get_dist_list to get_dist_list_email.
81 =============================================================================*/
82
83 /*======================Beginning of template code=========================+
84
85 The following templates demonstrate how you can use the client extension
86 to customize the behavior of the Status Report Workflow.
87 Three examples are included:
88 1. Start_Workflow: You can set additional Workflow Item Attributes
89
90 2. Set_report_Approver: You can override the logic to set your own
91 approver for the Approvel Process
92
93 3. Set_report_notification_party: You can override the logic to set your
94 own notification party for the Pulished, Obsoleted(Canceled),
95 Rejected and Approved Notification message
96
97 Procedure start_workflow
98 (
99 p_item_type IN VARCHAR2
100 , p_process_name IN VARCHAR2
101 , p_item_key IN NUMBER
102 , p_ci_id IN NUMBER
103 , x_msg_count out NUMBER
104 , x_msg_data OUT VARCHAR2
105 , x_return_status OUT VARCHAR2
106 ) is
107
108 -- The Purpose of Procedure is to save the workflow item attributes needed
109 -- for the workflow processes.
110
111 -- define the cursors you will need to retrieve information
112
113 -- define local variables you will need
114 begin
115
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 -- set item attributes for the workflow process.
119 -- an example of setting of various item attributes are shown below
120 -- please note that certain item attributes must be set in order for the
121 -- workflow approval process to work properly. For example, item_type,
122 -- item_key, etc. The best way to override this procedure is to copy the
123 -- code from start_workflow procedure and add your logic to the very end.
124
125 wf_engine.SetItemAttrNumber( p_item_type
126 , p_item_key
127 , 'VERSION_ID'
128 , p_version_id
129 );
130
131
132 wf_engine.SetItemAttrNumber( p_item_type
133 , p_item_key
134 , 'WF_OBJECT_ID'
135 , p_version_id
136 );
137
138 wf_engine.SetItemAttrText(itemtype => p_item_type,
139 itemkey => p_item_key,
140 aname => 'HISTORY',
141 avalue =>
142 'PLSQL:PA_WORKFLOW_HISTORY.show_history/'||
143 p_item_type||':'||
144 p_item_key );
145
146 -- Add your own setting here. Please be aware that the item attributes you
147 -- are setting must exist in the workflow file. So customize the workflow to
148 -- create new item attribute, then add your code here to set them when
149 -- the workflow process is launched.
150
151 EXCEPTION
152
153 WHEN OTHERS THEN
154
155 x_msg_count := 1;
156 x_msg_data := substr(SQLERRM,1,2000);
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158
159 end start_workflow;
160
161 PROCEDURE set_report_approver(
162 p_item_type IN VARCHAR2
163 ,p_item_key IN VARCHAR2
164 ,actid IN NUMBER
165 ,funcmode IN VARCHAR2
166 ,resultout OUT VARCHAR2 )
167 IS
168
169 -- The Purpose of Procedure is to set the approver for the status report
170 -- approval process.
171 -- The way to set to approver is to set the workflow item attribute
172 -- 'REPORT_APPROVER_NAME' . The approval process will pick
173 -- up this value when it tries to decide to whom it should send out
174 -- the notification.
175 -- 'REPORT_APPROVER_NAME' is a workflow adhoc user role, which is created
176 -- within this procedure.
177 -- In Addition, you need to set 'REPORT_APPROVER_FULL_NAME' and
178 -- 'REPORT_APPROVER_USER_NAME'. These item attributes will be used
179 -- as placeholders to store the approver name for tracking purpose.
180
181
182
183
184 -- define the cursors you will need to retrieve information
185
186 -- define local variables you will need
187
188 begin
189
190 -- retrieve the item attributes from workflow process, such as Report Version
191 -- ID, Submitted By ID, and Project Manager ID, etc. These attributes can
192 -- be used to determine the approver of the Control Item
193
194 -- create a adhoc role so that we can use this role for notification
195 -- you can keep the logic of creating role in this procedure untouched.
196
197 l_approval_role := 'APVR_' ||p_item_type || p_item_key;
198
199 -- run the cursor the retrieve your approver, your approver needs to have
200 -- user_name , person_name or email_address.
201 -- this is part which you can override to insert your own logic
202
203 -- create a adhoc user if the user does not exists in the system already.
204
205 -- add user name to a user list. This user list can not have duplicate.
206 -- so remove any duplicate if necessary.
207
208 -- add user the adhoc role by calling WF_DIRECTORY.AddUsersToAdHocRole
209 -- you can look at the original code in the procedure for example.
210
211 -- set the item attribute REPORT_APPROVER_FULL_NAME and
212 -- REPORT_APPROVER_USER_NAME
213
214 -- set item attribute REPORT_APPROVER_NAME
215 -- this is very important, with out this attribute set, the approval process
216 -- will not work
217
218 -- set return result to wf_engine.eng_completed||':'||'T';
219
220 -- save the comment history to record info such as
221 -- "Submitter zzzz submit report xxx on this date"
222
223 pa_workflow_history.save_comment_history (
224 p_item_type
225 ,p_item_key
226 ,'SUBMIT'
227 , l_user_name --- Submitter User Name
228 ,'');
229
230 EXCEPTION
231
232 WHEN OTHERS THEN
233 RAISE;
234
235
236 end set_report_approver;
237
238 PROCEDURE set_report_notification_party(
239 p_item_type IN VARCHAR2
240 ,p_item_key IN VARCHAR2
241 ,p_status IN VARCHAR2
242 ,actid IN NUMBER
243 ,funcmode IN VARCHAR2
244 ,resultout OUT VARCHAR2
245 )
246
247
248 -- The Purpose of Procedure is to set the notification party for the
249 -- published, obsoleted (canceled), rejected and approved message
250 --
251 -- The way to set to approver is to set the workflow item attribute
252 -- 'REPORT_NOTFY_NAME'. The notification process will pick
253 -- up this value when it tries to decide to whom it should send out
254 -- the notification.
255
256
257 -- define the cursors you will need to retrieve information
258
259 -- define local variables you will need
260
261 begin
262
263 -- retrieve the item attributes from workflow process, such as Report Version
264 -- ID, Project ID, etc. These attributes can
265 -- be used to determine the notification party.
266
267
268 -- Determine the status of the report given the version ID. This will
269 -- what kind of report we should send out (APPROVED, PUBLISHED, etc.)
270 -- Based on the report notification type, we will select different users
271 -- as notification party.
272
273 -- create a adhoc role so that we can use this role for notification
274 -- you can keep the logic of creating role in this procedure untouched.
275 l_notify_role := 'RNT_' || p_item_type || p_item_key;
276
277
278 -- run the cursor the retrieve your party, your party needs to have
279 -- user_name , person_name or email_address.
280 -- please note that in my original code, this is done through access list.
281 -- this is part which you can override to insert your own logic
282
283 -- create a adhoc user if the user does not exists in the system already.
284
285 -- add user name to a user list. This user list can not have duplicate.
286 -- so remove any duplicate if necessary.
287
288 -- add user the adhoc role by calling WF_DIRECTORY.AddUsersToAdHocRole
289 -- you can look at the original code in the procedure for example.
290
291 -- set the item attribute REPORT_NOTFY_NAME
292
293 -- set return result based on the report status, PUBLISHED, REJECTED, etc.
294 -- you should not customize this logic.
295
296
297 end set_report_notification_party;
298
299 =============================================================================*/
300
301
302 /********************************************************************
303 * Procedure : start_workflow
304 *********************************************************************/
305 Procedure start_workflow
306 (
307 p_item_type IN VARCHAR2
308 , p_process_name IN VARCHAR2
309 , p_item_key IN NUMBER
310
311 , p_version_id IN NUMBER
312
313 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
314 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
315 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
316 )
317 IS
318
319 l_project_name VARCHAR2(30);
320 l_project_number VARCHAR2(25);
321
322 l_task_num VARCHAR2(25);
323 l_task_name VARCHAR2(20);
324 l_person_id NUMBER;
325 l_name VARCHAR2(250);
326 l_user_name VARCHAR2(250);
327 l_status_code VARCHAR2(30);
328 l_submitter_role varchar2(30) := NULL;
329 l_reporter_role varchar2(30) := NULL;
330 l_role_users varchar2(30000) := NULL;
331 l_reporter_role_users varchar2(30000) := NULL;
332 l_reported_by_id NUMBER;
333 l_url VARCHAR2(600);
334 l_url2 VARCHAR2(600);
335 l_user_id NUMBER;
336 l_approval_required VARCHAR2(10);
337 l_submitted_by_user_name VARCHAR2(100);
338 l_project_id NUMBER;
339 l_role_users_table WF_DIRECTORY.UserTable; /*bug 5716959*/
340 l_reporter_role_users_table WF_DIRECTORY.UserTable; /*bug 5716959*/
341 l_count NUMBER := 1; /*bug 5716959*/
342
343 CURSOR get_report_info IS
344 SELECT pprv.*, pl.meaning progress_status FROM
345 pa_progress_reports_v pprv,
346 pa_lookups pl
347 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
348 AND pl.lookup_code = pprv.progress_status_code
349 and pprv.version_id = p_version_id;
350
351
352 CURSOR get_full_name_csr (l_person_id number)
353 IS
354 select papf.full_name
355 from
356 per_all_people_f papf
357 WHERE
358 papf.person_id = l_person_id;
359
360
361 CURSOR get_user_name_csr (l_usr_id number)
362 IS
363 SELECT fu.user_name
364 from
368 and
365 per_all_people_f papf, fnd_user fu
366 WHERE
367 fu.user_id = l_usr_id
369 fu.employee_id = papf.person_id;
370
371
372 CURSOR c_submitter_list IS
373 select UNIQUE usr.user_id, usr.person_party_id, usr.user_name,papf.email_address,papf.full_name person_name -- Bug 4527617. Replaced customer_id with person_party_id.
374 from per_all_people_f papf,
375 fnd_user usr
376 WHERE
377 papf.person_id = usr.employee_id
378 and trunc(sysdate)
379 between papf.EFFECTIVE_START_DATE
380 and Nvl(papf.effective_end_date, Sysdate + 1)
381 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
382 AND usr.user_id = l_user_id;
383
384 CURSOR c_reporter_list IS
385 select UNIQUE usr.user_id, usr.person_party_id, -- Bug 4527617. Replaced customer_id with person_party_id.
386 usr.user_name,papf.email_address,papf.full_name person_name
387 from per_all_people_f papf,
388 fnd_user usr,
389 pa_project_parties ppp
390 WHERE
391 papf.person_id = usr.employee_id
392 and trunc(sysdate)
393 between papf.EFFECTIVE_START_DATE
394 and Nvl(papf.effective_end_date, Sysdate + 1)
395 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
396 and papf.person_id = ppp.resource_source_id
397 and ppp.object_id = l_project_id
398 and ppp.object_type = 'PA_PROJECTS';
399
400
401 CURSOR get_approval_required IS
402 SELECT popl.approval_required
403 FROM pa_object_page_layouts popl, pa_progress_report_vers pprv
404 WHERE pprv.version_id = p_version_id
405 AND popl.object_id = pprv.object_id
406 AND popl.object_type = pprv.object_type
407 AND popl.page_id = pprv.page_id
408 and popl.report_type_id = pprv.report_type_id;
409
410
411 l_customer VARCHAR2(4000);
412 l_project_manager VARCHAR2(240);
413 l_org pa_project_lists_v.carrying_out_organization_name%TYPE; --VARCHAR2(60); Modified for bug 4530998
414
415 CURSOR get_project_info(l_project_id number) IS
416 SELECT
417 customer_name,
418 person_name,
419 carrying_out_organization_name
420 FROM pa_project_lists_v
421 WHERE project_id = l_project_id;
422
423 CURSOR get_person_id_from_resource_id (l_resource_id number) IS
424 select resource_source_id from pa_project_parties
425 where object_id = l_project_id
426 and object_type = 'PA_PROJECTS'
427 and resource_id = l_resource_id;
428
429 l_find_duplicate VARCHAR2(1) := 'N';
430
431 display_name VARCHAR2(2000);
432 email_address VARCHAR2(2000);
433 notification_preference VARCHAR2(2000);
434 language VARCHAR2(2000);
435 territory VARCHAR2(2000);
436 BEGIN
437
438 x_return_status := FND_API.G_RET_STS_SUCCESS;
439
440
441 wf_engine.SetItemAttrNumber( p_item_type
442 , p_item_key
443 , 'VERSION_ID'
444 , p_version_id
445 );
446
447
448 wf_engine.SetItemAttrNumber( p_item_type
449 , p_item_key
450 , 'WF_OBJECT_ID'
451 , p_version_id
452 );
453
454 wf_engine.SetItemAttrText(itemtype => p_item_type,
455 itemkey => p_item_key,
456 aname => 'HISTORY',
457 avalue =>
458 'PLSQL:PA_WORKFLOW_HISTORY.show_history/'||
459 p_item_type||':'||
460 p_item_key );
461
462
463 -- set approval_required attribute
464
465 OPEN get_approval_required;
466 FETCH get_approval_required INTO l_approval_required;
467 CLOSE get_approval_required;
468
469 wf_engine.SetItemAttrText( p_item_type
470 , p_item_key
471 , 'AUTO_APPROVED'
472 , l_approval_required
473 );
474
475 -- set item attributes for the workflow process
476 FOR rec IN get_report_info LOOP
477
478
479 -- set project id
480 wf_engine.SetItemAttrText( p_item_type
481 , p_item_key
482 , 'REPORT_TYPE'
483 , rec.report_type_name);
484
485 wf_engine.SetItemAttrText( p_item_type
486 , p_item_key
487 , 'LAST_UPDATED_BY'
488 , rec.last_updated_by);
489
490
491 wf_engine.SetItemAttrText( p_item_type
492 , p_item_key
493 , 'PROJECT_ID'
494 ,rec.object_id
495 );
496
497 l_url := 'JSP:OA_HTML/OA.jsp?paReportVersionId='||p_version_id ||'&paProjectId='|| rec.object_id||
498 '&akRegionCode=PA_PROG_REP_REVIEW_LAYOUT&akRegionApplicationId=275&paPageMode=APPROVE&addBreadCrumb=RP';
499
503
500 l_url2 := 'JSP:/OA_HTML/OA.jsp?paReportVersionId='||p_version_id ||'&paProjectId='|| rec.object_id||
501 '&akRegionCode=PA_PROG_REP_REVIEW_LAYOUT&akRegionApplicationId=275&paPageMode=APPROVE&addBreadCrumb=RP';
502
504 wf_engine.SetItemAttrText( p_item_type
505 , p_item_key
506 , 'REPORT_LINK'
507 , l_url2
508 );
509
510 l_project_id := rec.object_id;
511
512 -- set project manager, organization name and customer
513 OPEN get_project_info(rec.object_id);
514
515 FETCH get_project_info INTO l_customer,l_project_manager, l_org;
516
517
518 wf_engine.SetItemAttrText( p_item_type
519 , p_item_key
520 , 'PROJECT_MANAGER'
521 ,l_project_manager
522 );
523
524 wf_engine.SetItemAttrText( p_item_type
525 , p_item_key
526 , 'ORGANIZATION'
527 ,l_org
528 );
529 wf_engine.SetItemAttrText( p_item_type
530 , p_item_key
531 , 'CUSTOMER'
532 ,l_customer
533 );
534
535
536 CLOSE get_project_info;
537
538
539
540 -- set project name and number
541 pa_utils.getprojinfo(rec.object_id, l_project_number, l_project_name);
542
543
544 wf_engine.SetItemAttrText( p_item_type
545 , p_item_key
546 , 'PROJECT_NAME'
547 ,l_project_name
548 );
549
550 wf_engine.SetItemAttrText( p_item_type
551 , p_item_key
552 , 'PROJECT_NUMBER'
553 ,l_project_number
554 );
555
556
557
558 -- set record_version_number
559 wf_engine.SetItemAttrText( p_item_type
560 , p_item_key
561 , 'RECORD_VERSION_NUMBER'
562 ,rec.record_version_number
563 );
564
565
566 -- set report start date
567 wf_engine.SetItemAttrDate( p_item_type
568 , p_item_key
569 , 'REPORT_START_DATE'
570 ,rec.report_start_date
571 );
572
573 -- set progress status
574 wf_engine.SetItemAttrText( p_item_type
575 , p_item_key
576 , 'PROGRESS_STATUS'
577 ,rec.progress_status
578 );
579
580
581 -- set report status code
582 wf_engine.SetItemAttrText( p_item_type
583 , p_item_key
584 , 'REPORT_STATUS'
585 ,rec.report_status_code
586 );
587
588
589 -- set report end date
590 wf_engine.SetItemAttrDate( p_item_type
591 , p_item_key
592 , 'REPORT_END_DATE'
593 ,rec.report_end_date
594 );
595
596 -- set reported by ID and Name
597
598 -- rec.reported_by is resource_id, we need to change to person_id
599
600
601 l_reported_by_id := pa_resource_utils.get_person_id(rec.reported_by);
602
603
604 wf_engine.SetItemAttrNumber( p_item_type
605 , p_item_key
606 , 'REPORTED_BY_ID'
607 ,l_reported_by_id
608 );
609
610 -- send notification to reported by person
611
612
613 -- Role must be less than 30 chars and all uppsercase
614 l_reporter_role := 'RPTBY_' || p_item_type || p_item_key;
615
616
617 WF_DIRECTORY.CreateAdHocRole( role_name => l_reporter_role
618 , role_display_name => l_reporter_role
619 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962410
620
621 -- l_reporter_role_users := NULL; /* Commented for bug 5716959 */
622
623
624 for v_reporters in c_reporter_list loop
625
626 -- if (l_reporter_role_users is not null) then
627 -- l_reporter_role_users := l_reporter_role_users || ',';
628 -- end if; */ /* Commented for Bug 5716959*/
629
630 -- Create adhoc users
631 wf_directory.getroleinfo(Upper(v_reporters.user_name),display_name,
632 email_address,notification_preference,language,territory);
633 if display_name is null THEN
634
635
636 WF_DIRECTORY.CreateAdHocUser( name => v_reporters.user_name
637 , display_name => v_reporters.person_name
641 END IF;
638 --, notification_preference => 'MAILTEXT'
639 , EMAIL_ADDRESS =>v_reporters.email_address);
640
642 -- l_reporter_role_users := l_reporter_role_users || v_reporters.user_name; /* Commented for bug 5716959 */
643
644 l_reporter_role_users_table(l_count) := v_reporters.user_name; /*bug 5716959*/
645 l_count := l_count + 1; /*bug 5716959*/
646
647 end loop;
648
649 -- IF (l_reporter_role_users is NOT NULL) THEN /* commented out for Bug 5716959 */
650 IF (l_reporter_role_users_table.COUNT > 0 ) THEN /*bug 5716959*/
651
652
653 -- WF_DIRECTORY.AddUsersToAdHocRole( l_reporter_role
654 -- , l_reporter_role_users); -- /*Commented for Bug 5716959*/
655 WF_DIRECTORY.AddUsersToAdHocRole2( l_reporter_role
656 , l_reporter_role_users_table); /*bug 5716959 */
657
658
659 wf_engine.SetItemAttrText( p_item_type
660 , p_item_key
661 , 'REPORTED_BY_NAME'
662 , l_reporter_role);
663 END IF;
664
665
666 OPEN get_full_name_csr(l_reported_by_id);
667 FETCH get_full_name_csr INTO l_name ;
668
669 wf_engine.SetItemAttrText( p_item_type
670 , p_item_key
671 , 'REPORTED_BY_FULL_NAME'
672 , l_name
673 );
674
675
676 CLOSE get_full_name_csr;
677
678
679 -- set submitter by ID and Name
680
681 l_user_id := FND_GLOBAL.user_id;
682
683
684 wf_engine.SetItemAttrNumber( p_item_type
685 , p_item_key
686 , 'SUBMITTER_ID'
687 , l_user_id
688 );
689
690 OPEN get_full_name_csr(l_user_id);
691 FETCH get_full_name_csr INTO l_name;
692
693
694 wf_engine.SetItemAttrText( p_item_type
695 , p_item_key
696 , 'SUBMITTER_FULL_NAME'
697 , l_name
698 );
699
700 CLOSE get_full_name_csr;
701
702
703 /*Start of addition for bug 5027098 */
704 wf_engine.SetItemAttrText( p_item_type
705 , p_item_key
706 , 'FROM_ROLE_VALUE'
707 , FND_GLOBAL.USER_NAME);
708 /* End of addition for bug 5027098 */
709
710
711
712 END LOOP;
713
714
715 -- set submitter info
716
717 -- Create adhoc role
718 -- Role must be less than 30 chars and all uppsercase
719 l_submitter_role := 'RPT_' || p_item_type || p_item_key;
720
721
722 WF_DIRECTORY.CreateAdHocRole( role_name => l_submitter_role
723 , role_display_name => l_submitter_role
724 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962410
725
726 l_count := 1; /*Bug 5716959*/
727 for v_submitters in c_submitter_list loop
728
729 if (l_role_users is not null) then
730 l_role_users := l_role_users || ',';
731 end if;
732
733 -- Create adhoc users
734 wf_directory.getroleinfo(Upper(v_submitters.user_name),display_name,
735 email_address,notification_preference,language,territory);
736 if display_name is null THEN
737
738
739 WF_DIRECTORY.CreateAdHocUser( name => v_submitters.user_name
740 , display_name => v_submitters.person_name
741 --, notification_preference => 'MAILTEXT'
742 , EMAIL_ADDRESS =>v_submitters.email_address);
743 END IF;
744 l_role_users := l_role_users || v_submitters.user_name;
745 l_role_users_table(l_count) := v_submitters.user_name; /*bug 5716959*/
746 l_count := l_count + 1; /*bug 5716959*/
747 l_submitted_by_user_name := v_submitters.user_name;
748 end loop;
749
750
751 for v_reporters in c_reporter_list loop
752
753 l_find_duplicate := 'N';
754
755 IF (Instr(l_role_users, v_reporters.user_name||',') = 1) THEN
756 -- find duplicate
757 l_find_duplicate := 'Y';
758 ELSIF (Instr(l_role_users, ','||v_reporters.user_name||',') >0) THEN
759 -- find duplicate
760 l_find_duplicate := 'Y';
761 ELSIF (Instr(l_role_users, ','||v_reporters.user_name) = (Length(l_role_users) - Length(v_reporters.user_name))) THEN
762 -- find duplicate
763 l_find_duplicate := 'Y';
764
765 END IF;
766
767
768 IF l_find_duplicate = 'N' THEN
769 if (l_role_users is not null) then
770 l_role_users := l_role_users || ',';
771 end if;
772
773 -- Create adhoc users
774 wf_directory.getroleinfo(Upper(v_reporters.user_name),display_name,
775 email_address,notification_preference,language,territory);
776 if display_name is null THEN
777
778 WF_DIRECTORY.CreateAdHocUser( name => v_reporters.user_name
779 , display_name => v_reporters.person_name
783 END IF;
780 --, notification_preference => 'MAILTEXT'
781 , EMAIL_ADDRESS =>v_reporters.email_address);
782
784 l_role_users := l_role_users || v_reporters.user_name;
785 l_role_users_table(l_count) := v_reporters.user_name; /*bug 5716959*/
786 l_count := l_count + 1; /*bug 5716959*/
787 END IF;
788
789 end loop;
790
791
792 -- IF (l_role_users is NOT NULL) THEN /* commented for bug 5716959 */
793 IF (l_role_users_table.COUNT > 0) THEN /*bug 5716959*/
794
795
796 -- WF_DIRECTORY.AddUsersToAdHocRole( l_submitter_role
797 -- , l_role_users); /* commented for bug 5716959 */
798 WF_DIRECTORY.AddUsersToAdHocRole2( l_submitter_role
799 , l_role_users_table); /*bug 5716959*/
800
801
802 wf_engine.SetItemAttrText( p_item_type
803 , p_item_key
804 , 'REPORT_NOTFY_NAME'
805 , l_submitter_role);
806
807 wf_engine.SetItemAttrText( p_item_type
808 , p_item_key
809 , 'REPORT_SUBMITTER'
810 , l_submitter_role);
811
812
813 END IF;
814
815
816 EXCEPTION
817
818 WHEN OTHERS THEN
819
820 x_msg_count := 1;
821 x_msg_data := substr(SQLERRM,1,2000);
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 RAISE;
824
825 END start_workflow;
826
827
828 /********************************************************************
829 * Procedure : set_report_approver
830 *********************************************************************/
831 PROCEDURE set_report_approver(
832 p_item_type IN VARCHAR2
833 ,p_item_key IN VARCHAR2
834 ,actid IN NUMBER
835 ,funcmode IN VARCHAR2
836 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
837
838 IS
839
840
841 l_approval_role varchar2(30) := NULL;
842 l_role_users varchar2(30000) := NULL;
843 l_version_id NUMBER;
844 l_reported_by_id NUMBER;
845 l_approver_id NUMBER;
846 l_approver_source_type NUMBER;
847 l_approver_name per_all_people_f.full_name%type; --Added for bug 4165764.
848
849 l_user_name VARCHAR2(250);
850 l_role_users_table WF_DIRECTORY.UserTable; /*bug 5716959*/
851 l_count NUMBER := 1; /*bug 5716959*/
852 CURSOR get_user_name
853 IS
854 SELECT user_name
855 FROM fnd_user
856 WHERE user_id = FND_GLOBAL.user_id;
857
858 CURSOR l_report_approver_csr_person IS
859 select distinct
860 fu.user_id,
861 fu.user_name,
862 papf.email_address,
863 papf.full_name person_name
864 from
865 fnd_user fu,per_all_people_f papf
866 where
867 fu.employee_id = l_approver_id
868 and papf.person_id = fu.employee_id
869 and trunc(sysdate)
870 between papf.EFFECTIVE_START_DATE
871 and Nvl(papf.effective_end_date, Sysdate + 1)
872 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
873 ;
874
875 CURSOR l_report_approver_csr_party IS
876 select distinct
877 fu.user_id,
878 fu.user_name,
879 papf.email_address,
880 papf.full_name person_name
881 from
882 fnd_user fu,per_all_people_f papf
883 where
884 fu.person_party_id = l_approver_id -- Bug 4527617. Replaced customer_id with person_party_id.
885 and papf.person_id = fu.employee_id
886 and trunc(sysdate)
887 between papf.EFFECTIVE_START_DATE
888 and Nvl(papf.effective_end_date, Sysdate + 1)
889 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
890 ;
891
892 CURSOR get_approver_source_id
893 IS
894 select approver_source_id,
895 approver_source_type
896 from pa_progress_report_vers pprv,
897 pa_object_page_layouts popl
898 where pprv.object_id = popl.object_id
899 and pprv.object_type = popl.object_type
900 and pprv.report_type_id = popl.report_type_id
901 and version_id = l_version_id;
902
903
904 CURSOR l_approver_csr is
905 SELECT DISTINCT
906 fu.user_name,
907 p1.supervisor_id person_id, p2.full_name person_name, p2.email_address
908 FROM per_assignments_f p1, per_all_people_f p2
909 , fnd_user fu
910 WHERE p1.person_id = l_reported_by_id
911 and p1.supervisor_id = p2.person_id
912 AND p1.assignment_type in ('E', 'C') -- Added for bug 2911451
913 AND p1.primary_flag ='Y' -- Added for bug 2911451
914 AND TRUNC(sysdate) BETWEEN p1.EFFECTIVE_START_DATE
915 AND p1.EFFECTIVE_END_DATE -- Removed null for bug 2911451
916 AND TRUNC(sysdate) BETWEEN p2.EFFECTIVE_START_DATE
917 AND NVL(p2.EFFECTIVE_END_DATE, sysdate)
918 AND fu.employee_id = p1.supervisor_id
919 ;
920
924 language VARCHAR2(2000);
921 display_name VARCHAR2(2000);
922 email_address VARCHAR2(2000);
923 notification_preference VARCHAR2(2000);
925 territory VARCHAR2(2000);
926
927 BEGIN
928
929 l_version_id := wf_engine.GetItemAttrNumber
930 ( itemtype => p_item_type,
931 itemkey => p_item_key,
932 aname => 'VERSION_ID');
933
934 l_reported_by_id := wf_engine.GetItemAttrNumber
935 ( itemtype => p_item_type,
936 itemkey => p_item_key,
937 aname => 'REPORTED_BY_ID');
938
939 l_approval_role := 'APVR_' ||p_item_type || p_item_key;
940
941 /* Commented for bug 4165764
942 WF_DIRECTORY.CreateAdHocRole( role_name => l_approval_role
943 , role_display_name => l_approval_role
944 , expiration_date => null
945 ); */
946
947
948
949 OPEN get_approver_source_id;
950 FETCH get_approver_source_id INTO l_approver_id, l_approver_source_type;
951
952
953
954 IF get_approver_source_id%notfound OR
955 l_approver_id IS NULL THEN
956
957
958 -- if no approver select from setup
959 CLOSE get_approver_source_id;
960 for v_approvers in l_approver_csr loop
961
962
963 -- if (l_role_users is not null) then
964 -- l_role_users := l_role_users || ',';
965 -- end if; /* commented for bug 5716959 */
966
967 -- Create adhoc users
968 wf_directory.getroleinfo(Upper(v_approvers.user_name),display_name,
969 email_address,notification_preference,language,territory);
970 if display_name is null THEN
971
972
973 WF_DIRECTORY.CreateAdHocUser( name => v_approvers.user_name
974 , display_name => v_approvers.person_name
975 --, notification_preference => 'MAILTEXT'
976 , EMAIL_ADDRESS =>v_approvers.email_address);
977
978
979 END IF;
980
981
982 wf_engine.SetItemAttrText( p_item_type
983 , p_item_key
984 , 'REPORT_APPROVER_FULL_NAME'
985 , v_approvers.person_name);
986
987
988 wf_engine.SetItemAttrText( p_item_type
989 , p_item_key
990 , 'REPORT_APPROVER_USER_NAME'
991 , v_approvers.user_name);
992
993 -- l_role_users := l_role_users || v_approvers.user_name; /* commented out for bug 5716959*/
994 l_role_users_table(l_count) := v_approvers.user_name; /*bug 5716959 */
995 l_count := l_count + 1; /*bug 5716959 */
996 end loop;
997 ELSE
998 CLOSE get_approver_source_id;
999
1000
1001 IF l_approver_source_type = 101 THEN
1002 -- source type is person
1003
1004
1005 for v_approvers in l_report_approver_csr_person loop
1006 -- if (l_role_users is not null) then
1007 -- l_role_users := l_role_users || ',';
1008 -- end if; /* commented for bug 5716959 */
1009
1010 -- Create adhoc users
1011 wf_directory.getroleinfo(Upper(v_approvers.user_name),display_name,
1012 email_address,notification_preference,language,territory);
1013 if display_name is null THEN
1014
1015
1016 WF_DIRECTORY.CreateAdHocUser( name => v_approvers.user_name
1017 , display_name => v_approvers.person_name
1018 --, notification_preference => 'MAILTEXT'
1019 , EMAIL_ADDRESS =>v_approvers.email_address);
1020
1021 END IF;
1022 wf_engine.SetItemAttrText( p_item_type
1023 , p_item_key
1024 , 'REPORT_APPROVER_FULL_NAME'
1025 , v_approvers.person_name);
1026
1027 wf_engine.SetItemAttrText( p_item_type
1028 , p_item_key
1029 , 'REPORT_APPROVER_USER_NAME'
1030 , v_approvers.user_name);
1031
1032 -- l_role_users := l_role_users || v_approvers.user_name; /* commented for bug 5716959 */
1033 l_role_users_table(l_count) := v_approvers.user_name; /*bug 5716959 */
1034 l_count := l_count + 1; /*bug 5716959 */
1035 END LOOP;
1036 ELSIF l_approver_source_type = 112 THEN
1037
1038 for v_approvers in l_report_approver_csr_party loop
1039 -- if (l_role_users is not null) then
1040 -- l_role_users := l_role_users || ',';
1041 -- end if; /* commented for bug 5716959 */
1042
1043 -- Create adhoc users
1044 wf_directory.getroleinfo(Upper(v_approvers.user_name),display_name,
1045 email_address,notification_preference,language,territory);
1046 if display_name is null THEN
1047
1048
1049 WF_DIRECTORY.CreateAdHocUser( name => v_approvers.user_name
1050 , display_name => v_approvers.person_name
1051 --, notification_preference => 'MAILTEXT'
1052 , EMAIL_ADDRESS =>v_approvers.email_address);
1053 END IF;
1054
1055 wf_engine.SetItemAttrText( p_item_type
1056 , p_item_key
1057 , 'REPORT_APPROVER_FULL_NAME'
1058 , v_approvers.person_name);
1059 wf_engine.SetItemAttrText( p_item_type
1060 , p_item_key
1064
1061 , 'REPORT_APPROVER_USER_NAME'
1062 , v_approvers.user_name);
1063
1065 -- l_role_users := l_role_users || v_approvers.user_name; /* commented for bug 5716959 */
1066 l_role_users_table(l_count) := v_approvers.user_name; /*bug 5716959 */
1067 l_count := l_count + 1; /*bug 5716959 */
1068 END LOOP;
1069 END IF;
1070
1071 END IF;
1072
1073 --Start of addition for bug 4165764
1074 l_approver_name :=wf_engine.GetItemAttrText( p_item_type
1075 , p_item_key
1076 , 'REPORT_APPROVER_FULL_NAME'
1077 );
1078 WF_DIRECTORY.CreateAdHocRole( role_name => l_approval_role
1079 , role_display_name => l_approver_name
1080 , expiration_date => sysdate+1 -- Set expiration_date for bug#5962410
1081 );
1082
1083 -- End of Addition for bug 4165764
1084
1085 -- IF (l_role_users is NOT NULL) THEN /* commented for bug 5716959 */
1086 IF (l_role_users_table.COUNT > 0 ) THEN /* bug 5716959 */
1087 -- WF_DIRECTORY.AddUsersToAdHocRole( l_approval_role
1088 -- , l_role_users); /* commented for bug 5716959 */
1089
1090 WF_DIRECTORY.AddUsersToAdHocRole2( l_approval_role
1091 , l_role_users_table); /*bug 5716959 */
1092
1093 wf_engine.SetItemAttrText( p_item_type
1094 , p_item_key
1095 , 'REPORT_APPROVER_NAME'
1096 , l_approval_role);
1097
1098
1099 resultout := wf_engine.eng_completed||':'||'T';
1100
1101 OPEN get_user_name;
1102 FETCH get_user_name INTO l_user_name;
1103 CLOSE get_user_name;
1104
1105
1106
1107
1108 pa_workflow_history.save_comment_history (
1109 p_item_type
1110 ,p_item_key
1111 ,'SUBMIT'
1112 , l_user_name
1113 ,'');
1114
1115 ELSE
1116
1117 resultout := wf_engine.eng_completed||':'||'F';
1118 END IF;
1119
1120
1121
1122 commit;
1123
1124
1125
1126 EXCEPTION
1127
1128 WHEN OTHERS THEN
1129 RAISE;
1130
1131 END set_report_approver;
1132
1133
1134 /********************************************************************
1135 * Procedure : set_report_notification_party
1136 *********************************************************************/
1137 PROCEDURE set_report_notification_party(
1138 p_item_type IN VARCHAR2
1139 ,p_item_key IN VARCHAR2
1140 ,p_status IN VARCHAR2
1141 ,actid IN NUMBER
1142 ,funcmode IN VARCHAR2
1143 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1144 )
1145
1146 IS
1147
1148 l_version_id NUMBER;
1149 l_project_id NUMBER;
1150 l_object_page_layout_id NUMBER;
1151 l_notify_role varchar2(30) := NULL;
1152 l_role_users varchar2(30000) := NULL;
1153
1154 l_user_names pa_distribution_list_utils.pa_vc_1000_150 := null;
1155 l_full_names pa_distribution_list_utils.pa_vc_1000_150 := null;
1156 l_email_addresses pa_distribution_list_utils.pa_vc_1000_150 := null;
1157 l_return_status VARCHAR2(1);
1158 l_msg_count NUMBER;
1159 l_msg_data VARCHAR2(2000);
1160 i INTEGER;
1161
1162 l_role_users_table WF_DIRECTORY.UserTable; /*bug 5716959*/
1163 l_count NUMBER := 1 ; /*bug 5716959 */
1164
1165 CURSOR get_object_page_layout_id
1166 IS
1167 select popl.object_page_layout_id
1168 from pa_object_page_layouts popl,
1169 pa_progress_report_vers pprv
1170 where popl.object_id = l_project_id
1171 and popl.object_type = 'PA_PROJECTS'
1172 and popl.page_type_code = 'PPR'
1173 and sysdate between popl.effective_from and nvl(popl.effective_to, sysdate+1)
1174 and pprv.version_id = l_version_id
1175 and pprv.report_type_id = popl.report_type_id;
1176
1177
1178 CURSOR get_proj_mgr
1179 IS
1180 SELECT
1181 usr.user_id, usr.person_party_id, usr.user_name,papf.email_address,papf.full_name person_name -- Bug 4527617. Replaced customer_id with person_party_id.
1182 FROM pa_project_lists_v pplv,
1183 per_all_people_f papf,
1184 fnd_user usr
1185 WHERE pplv.project_id = l_project_id
1186 and papf.person_id = usr.employee_id
1187 and trunc(sysdate)
1188 between papf.EFFECTIVE_START_DATE
1189 and Nvl(papf.effective_end_date, Sysdate + 1)
1190 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
1191 and papf.person_id = pplv.person_id;
1192
1193 l_submitter_role varchar2(30) := NULL;
1194 l_find_duplicate VARCHAR2(1) := 'N';
1195 display_name VARCHAR2(2000);
1196 email_address VARCHAR2(2000);
1197 notification_preference VARCHAR2(2000);
1198 language VARCHAR2(2000);
1199 territory VARCHAR2(2000);
1200 BEGIN
1201
1202 l_version_id := wf_engine.GetItemAttrNumber
1206
1203 ( itemtype => p_item_type,
1204 itemkey => p_item_key,
1205 aname => 'VERSION_ID');
1207
1208 l_project_id := wf_engine.GetItemAttrNumber
1209 ( itemtype => p_item_type,
1210 itemkey => p_item_key,
1211 aname => 'PROJECT_ID');
1212
1213 OPEN get_object_page_layout_id;
1214 FETCH get_object_page_layout_id INTO l_object_page_layout_id;
1215 CLOSE get_object_page_layout_id;
1216
1217 IF (p_status = 'PROGRESS_REPORT_PUBLISHED' or
1218 p_status = 'PROGRESS_REPORT_CANCELED'
1219 )THEN
1220 -- set notifcation party for project people with View
1221 -- privilege
1222
1223 -- Create adhoc role
1224 -- Role must be less than 30 chars and all uppsercase
1225 l_notify_role := 'RNT_' || p_item_type || p_item_key;
1226
1227
1228
1229
1230 WF_DIRECTORY.CreateAdHocRole( role_name => l_notify_role
1231 , role_display_name => l_notify_role
1232 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962410
1233
1234
1235 -- Bug 6843694 : Changed call to procedure get_dist_list_email
1236 PA_DISTRIBUTION_LIST_UTILS.get_dist_list_email
1237 (
1238
1239 'PA_OBJECT_PAGE_LAYOUT',
1240 l_object_page_layout_id,
1241
1242 1, -- view priv
1243 l_user_names ,
1244 l_full_names ,
1245 l_email_addresses ,
1246 l_return_status ,
1247 l_msg_count ,
1248 l_msg_data
1249 );
1250
1251
1252
1253
1254 IF (l_return_status = 'S' AND l_user_names IS NOT null) THEN
1255
1256 FOR i in l_user_names.First..l_user_names.LAST LOOP
1257 IF l_user_names(i) IS NULL THEN
1258
1259
1260 l_user_names(i) := Upper(l_email_addresses(i));
1261 l_full_names(i) := l_email_addresses(i);
1262 END IF;
1263
1264 if (l_role_users is not null) then
1265 l_role_users := l_role_users || ',';
1266 end if;
1267
1268
1269
1270 wf_directory.getroleinfo(Upper(l_user_names(i)),display_name,
1271 email_address,notification_preference,language,territory);
1272 if display_name is null THEN
1273
1274
1275 WF_DIRECTORY.CreateAdHocUser( name => l_user_names(i)
1276 , display_name => l_full_names(i)
1277 , EMAIL_ADDRESS =>l_email_addresses(i));
1278 END IF;
1279 l_role_users := l_role_users || l_user_names(i);
1280 l_role_users_table(l_count) := l_user_names(i); /*bug 5716959*/
1281 l_count := l_count + 1 ; /*bug 5716959*/
1282 end loop;
1283
1284 END IF;
1285
1286
1287 -- IF (l_role_users is NOT NULL) THEN /* commented out for bug 5716959 */
1288 IF (l_role_users_table.COUNT > 0) THEN /* bug 5716959 */
1289
1290 -- WF_DIRECTORY.AddUsersToAdHocRole( l_notify_role
1291 -- , l_role_users); /* commented for bug 5716959 */
1292 WF_DIRECTORY.AddUsersToAdHocRole2( l_notify_role
1293 , l_role_users_table); /*bug 5716959 */
1294
1295 wf_engine.SetItemAttrText( p_item_type
1296 , p_item_key
1297 , 'REPORT_NOTFY_NAME'
1298 , l_notify_role);
1299 END IF;
1300
1301 IF p_status = 'PROGRESS_REPORT_CANCELED' THEN
1302 resultout := wf_engine.eng_completed||':'||'CANCELED';
1303 ELSIF p_status = 'PROGRESS_REPORT_PUBLISHED' then
1304 resultout := wf_engine.eng_completed||':'||'PUBLISHED';
1305 END IF;
1306
1307 ELSIF (p_status = 'PROGRESS_REPORT_REJECTED' or
1308 p_status = 'PROGRESS_REPORT_APPROVED' )THEN
1309
1310 IF p_status = 'PROGRESS_REPORT_REJECTED' then
1311 resultout := wf_engine.eng_completed||':'||'REJECTED';
1312 ELSIF p_status = 'PROGRESS_REPORT_APPROVED' THEN
1313 resultout := wf_engine.eng_completed||':'||'APPROVED';
1314 END IF;
1315
1316
1317 -- Create adhoc role
1318 -- Role must be less than 30 chars and all uppsercase
1319 l_notify_role := 'RNT_' || p_item_type || p_item_key;
1320
1321
1322 WF_DIRECTORY.CreateAdHocRole( role_name => l_notify_role
1323 , role_display_name => l_notify_role
1324 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962410
1325
1326 -- Bug 6843694 : Changed call to procedure get_dist_list_email
1327 PA_DISTRIBUTION_LIST_UTILS.get_dist_list_email
1328 (
1329
1330 'PA_OBJECT_PAGE_LAYOUT',
1331 l_object_page_layout_id,
1332 2, -- view priv
1333 l_user_names ,
1334 l_full_names ,
1335 l_email_addresses ,
1336 l_return_status ,
1337 l_msg_count ,
1338 l_msg_data
1339 );
1340
1341 IF (l_return_status = 'S' AND l_user_names IS NOT null) THEN
1342
1343 FOR i in l_user_names.First..l_user_names.LAST LOOP
1344
1345 if (l_role_users is not null) THEN
1346 IF l_user_names(i) IS NULL THEN
1347 --EXIT ;
1348 l_user_names(i) := Upper(l_email_addresses(i));
1349 l_full_names(i) := l_email_addresses(i);
1350
1351 END IF;
1352
1353 l_role_users := l_role_users || ',';
1354 end if;
1355
1356
1357 wf_directory.getroleinfo(Upper(l_user_names(i)),display_name,
1358 email_address,notification_preference,language,territory);
1359 if display_name is null THEN
1360
1361 WF_DIRECTORY.CreateAdHocUser( name => l_user_names(i)
1362 , display_name => l_full_names(i)
1363 , EMAIL_ADDRESS =>l_email_addresses(i));
1364 END IF;
1365 l_role_users := l_role_users || l_user_names(i);
1366 l_role_users_table(l_count) := l_user_names(i); /*bug 5716959*/
1367 l_count := l_count + 1 ; /*bug 5716959*/
1368 end loop;
1369
1370 END IF;
1371
1372
1373 --get proj mgr
1374 for v_people in get_proj_mgr loop
1375
1376
1377 l_find_duplicate := 'N';
1378
1379
1380 -- check if there are duplicate names
1381 IF (l_role_users IS NOT NULL AND Instr(l_role_users, ',')<1) THEN
1382 -- only one name in the role_users
1383 IF (Instr(l_role_users, v_people.user_name) > 0) THEN
1384 -- find duplicate
1385 l_find_duplicate := 'Y';
1386 END IF;
1387 ELSIF l_role_users IS NOT NULL THEN
1388 IF (Instr(l_role_users, v_people.user_name||',') = 1) THEN
1389 -- find duplicate
1390 l_find_duplicate := 'Y';
1391 ELSIF (Instr(l_role_users, ','||v_people.user_name||',') >0) THEN
1392 -- find duplicate
1393 l_find_duplicate := 'Y';
1394 ELSIF (Instr(l_role_users, ','||v_people.user_name) = (Length(l_role_users) - Length(v_people.user_name))) THEN
1395 -- find duplicate
1396 l_find_duplicate := 'Y';
1397
1398 END IF;
1399 END IF;
1400
1401 IF l_find_duplicate = 'N' then
1402 if (l_role_users is not null) then
1403 l_role_users := l_role_users || ',';
1404 end if;
1405
1406
1407 -- Create adhoc users
1408 wf_directory.getroleinfo(Upper(v_people.user_name),
1409 display_name,
1410 email_address,
1411 notification_preference,
1412 language,territory);
1413 if display_name is null THEN
1414
1415 WF_DIRECTORY.CreateAdHocUser( name => v_people.user_name
1416 , display_name => v_people.person_name
1417 --, notification_preference => 'MAILTEXT'
1418 , EMAIL_ADDRESS =>v_people.email_address);
1419 END IF;
1420 l_role_users := l_role_users || v_people.user_name;
1421 l_role_users_table(l_count) := l_user_names(i); /*bug 5716959*/
1422 l_count := l_count + 1 ; /*bug 5716959*/
1423 END IF;
1424
1425 end loop;
1426
1427 -- IF (l_role_users is NOT NULL) THEN /* commented for bug 5716959 */
1428 IF (l_role_users_table.COUNT > 0) THEN /*bug 5716959*/
1429 -- WF_DIRECTORY.AddUsersToAdHocRole( l_notify_role
1430 -- , l_role_users); /* commented for bug 5716959 */
1431 WF_DIRECTORY.AddUsersToAdHocRole2( l_notify_role
1432 , l_role_users_table); /*bug 5716959*/
1433
1434 wf_engine.SetItemAttrText( p_item_type
1435 , p_item_key
1436 , 'REPORT_NOTFY_NAME'
1437 , l_notify_role);
1438 END IF;
1439
1440 END IF;
1441
1442 EXCEPTION
1443 WHEN OTHERS then
1444 NULL;
1445
1446
1447 END set_report_notification_party;
1448
1449
1450
1451 END pa_report_workflow_client;
1452
[Home] [Help]