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