1 package body pa_control_items_wf_client as
2 /* $Header: PACIWFCB.pls 120.4.12010000.2 2008/10/15 11:38:16 rthumma ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8
9 FILE NAME : PACIWFCB.pls
10 DESCRIPTION :
11 This file provided client extension procedures that are called
12 to execute each activity in the Issue and Change Document Workflow.
13
14 USAGE: sqlplus apps/apps @PAPRWFCB.pls
15
16 PROCEDURES AND PARAMETERS:
17
18 start_workflow
19
20 p_item_type : The workflow item type.
21 p_process_name : Name of the workflow process.
22 p_item_key : The workflow item key.
23 p_ci_id : Control Item Identifier.
24 x_msg_count : The number of messages being sent.
25 x_msg_data : The content of the message.
26 x_return_status : The return status of the message.
27
28
29 set_ci_approver
30
31 p_item_type : The workflow item type.
32 p_item_key : The workflow item key.
33 actid : Identifier of the action.
34 funcmode : Workflow function mode.
35 resultout : Process result.
36
37 set_notification_party
38
39 p_item_type : The workflow item type.
40 p_item_key : The workflow item key.
41 p_status : The control item status.
42 actid : Identifier of the action.
43 funcmode : Workflow function mode.
44 resultout : Process result.
45
46
47
48 HISTORY : 07/22/02 SYAO Initial Creation
49 08/18/04 mumohan Bug#3838957: Added the condition to exclude
50 the end dated users in the cursors
51 get_approver_list and get_notification_list.
52 08/03/05 raluthra Bug 4527617. Replaced the usage of fnd_user.
53 customer_id with fnd_user.person_party_id
54 for R12 ATG Mandate.
55 08/05/05 raluthra Bug 4358517: Changed the definition of
56 l_org local variable from VARCHAR2(60) to
57 pa_project_lists_v.carrying_out_organization_name%TYPE
58 10/15/08 rthumma Bug 6843085: Added changes in set_ci_approver to set the
59 control item status to previous status when there is no project manager.
60 =============================================================================*/
61
62 /*======================Beginning of template code=========================+
63
64 The following templates demonstrate how you can use the client extension
65 to customize the behavior of the Issue and Change Document Workflow.
66 Three examples are included:
67 1. Start_Workflow: You can set additional Workflow Item Attributes
68
69 2. Set_Ci_Approver: You can override the logic to set your own
70 approver for the Approvel Process
71
72 3. Set_notification_party: You can override the logic to set your
73 own notification party in case the control item is approved or
74 rejected.
75
76 Procedure start_workflow
77 (
78 p_item_type IN VARCHAR2
79 , p_process_name IN VARCHAR2
80 , p_item_key IN NUMBER
81 , p_ci_id IN NUMBER
82 , x_msg_count out NUMBER
83 , x_msg_data OUT VARCHAR2
84 , x_return_status OUT VARCHAR2
85 ) is
86
87 -- The Purpose of Procedure is to save the workflow item attributes needed
88 -- for the workflow processes.
89
90 -- define the cursors you will need to retrieve information
91
92 -- define local variables you will need
93 begin
94
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97 -- set item attributes for the workflow process.
98 -- an example of setting of various item attributes are shown below
99 -- please note that certain item attributes must be set in order for the
100 -- workflow approval process to work properly. For example, item_type,
101 -- item_key, etc. The best way to override this procedure is to copy the
102 -- code from start_workflow procedure and add your logic to the very end.
103
104
105 wf_engine.SetItemAttrText( p_item_type
106 , p_item_key
107 , 'ITEM_TYPE'
108 , p_item_type
109 );
110
111 wf_engine.SetItemAttrText( p_item_type
112 , p_item_key
113 , 'ITEM_KEY'
114 , p_item_key
115 );
116
117 wf_engine.SetItemAttrText(itemtype => p_item_type,
118 itemkey => p_item_key,
119 aname => 'HISTORY',
120 avalue =>
121 'PLSQL:PA_WORKFLOW_HISTORY.show_
122 history/'||
123 p_item_type||':'||
124 p_item_key );
125
126 -- Add your own setting here. Please be aware that the item attributes you
127 -- are setting must exist in the workflow file. So customize the workflow to
128 -- create new item attribute, then add your code here to set them when
129 -- the workflow process is launched.
130
131 EXCEPTION
132
133 WHEN OTHERS THEN
134
135 x_msg_count := 1;
136 x_msg_data := substr(SQLERRM,1,2000);
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138
139 end start_workflow;
140
141
142 PROCEDURE set_ci_approver(
143 p_item_type IN VARCHAR2
144 ,p_item_key IN VARCHAR2
145 ,actid IN NUMBER
146 ,funcmode IN VARCHAR2
147 ,resultout OUT VARCHAR2 )
148 IS
149
150 -- The Purpose of Procedure is to set the approver for the control item
151 -- approval process.
152 -- The way to set to approver is to set the workflow item attribute
153 -- 'CI_APPROVER' and 'CI_APPROVER_NAME'. The approval process will pick
154 -- up these values when it tries to decide to whom it should send out
155 -- the notification.
156 -- 'CI_APPROVER' is a workflow adhoc user role, which is created within
157 -- this procedure.
158 -- 'CI_APPROVER_NAME' is a place holder to store the name of the approver
159
160
161
162
163 -- define the cursors you will need to retrieve information
164
165 -- define local variables you will need
166
167 begin
168
169 -- retrieve the item attributes from workflow process, such as Control Item
170 -- ID, Submitted By ID, and Project Manager ID, etc. These attributes can
171 -- be used to determine the approver of the Control Item
172
173 -- create a adhoc role so that we can use this role for notification
174 -- you can keep the logic of creating role in this procedure untouched.
175
176 -- run the cursor the retrieve your approver, your approver needs to have
177 -- user_name , person_name or email_address.
178 -- this is part which you can override to insert your own logic
179
180 -- create a adhoc user if the user does not exists in the system already.
181
182 -- add user name to a user list. This user list can not have duplicate.
183 -- so remove any duplicate if necessary.
184
185 -- add user the adhoc role by calling WF_DIRECTORY.AddUsersToAdHocRole
186 -- you can look at the original code in the procedure for example.
187
188 -- set the item attribute CI_APPROVER and CI_APPROVER_NAME
189
190 -- set return result to wf_engine.eng_completed||':'||'T';
191
192 EXCEPTION
193
194 WHEN OTHERS THEN
195 RAISE;
196
197
198 end set_ci_approver;
199
200
201 PROCEDURE set_notification_party(
202 p_item_type IN VARCHAR2
203 ,p_item_key IN VARCHAR2
204 ,p_status IN VARCHAR2
205 ,actid IN NUMBER
206 ,funcmode IN VARCHAR2
207 ,resultout OUT VARCHAR2
208 ) IS
209
210 -- The Purpose of Procedure is to set the notification party for the
211 -- approval rejected or approved notification message.
212 --
213 -- The way to set to approver is to set the workflow item attribute
214 -- 'CI_NOTIFICATION_PARTY'. The notification process will pick
215 -- up these values when it tries to decide to whom it should send out
216 -- the notification.
217 -- 'CI_NOTIFICATION_PARTY' is a workflow adhoc user role, which is created
218 -- within this procedure.
219
220 -- define the cursors you will need to retrieve information
221
222 -- define local variables you will need
223
224 begin
225
226 -- retrieve the item attributes from workflow process, such as Control Item
227 -- ID, Submitted By ID, and Project Manager ID, etc. These attributes can
228 -- be used to determine the notification party.
229
230 -- create a adhoc role so that we can use this role for notification
231 -- you can keep the logic of creating role in this procedure untouched.
232
233 -- run the cursor the retrieve your party, your party needs to have
234 -- user_name , person_name or email_address.
235 -- this is part which you can override to insert your own logic
236
237 -- create a adhoc user if the user does not exists in the system already.
238
239 -- add user name to a user list. This user list can not have duplicate.
240 -- so remove any duplicate if necessary.
241
242 -- add user the adhoc role by calling WF_DIRECTORY.AddUsersToAdHocRole
243 -- you can look at the original code in the procedure for example.
244
245 -- set the item attribute CI_NOTIFICATION_PARTY
246
247 -- set return result to wf_engine.eng_completed||':'||'T';
248 end set_notification_party;
249
250 =============================================================================*/
251
252
253 /********************************************************************
254 * Procedure : start_workflow
255 * Purpose :
256 *********************************************************************/
257 Procedure start_workflow
258 (
259 p_item_type IN VARCHAR2
260 , p_process_name IN VARCHAR2
261 , p_item_key IN NUMBER
262
263 , p_ci_id IN NUMBER
264
265 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
266 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
267 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
268 )
269 IS
270 l_project_mgr_id NUMBER;
271
272 l_submitted_by_id NUMBER;
273
274 l_url VARCHAR2(2000);
275
276 CURSOR get_project_mgr_party_id
277 IS
278 select hp.party_id
279 from pa_project_parties ppp,
280 pa_control_items pci,
281 hz_parties hp
282 where pci.ci_id = p_ci_id
283 and ppp.project_role_id = 1
284 and ppp.project_id = pci.project_id
285 and trunc(sysdate) between ppp.start_date_active and nvl(ppp.end_date_active, sysdate)
286 AND ((ppp.resource_type_id = 101 and hp.orig_system_reference = 'PER:' || ppp.resource_source_id
287 ) or (ppp.resource_type_id = 112 and hp.party_id = ppp.resource_source_id
288 ));
289
290
291 CURSOR get_submitted_by_id
292 IS
293 select party_id from
294 (
295 select hp.party_id
296 from fnd_user fu,
297 hz_parties hp
298 where fu.user_id = fnd_global.user_id
299 and fu.employee_id is null
300 and fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
301 union
302 select hp.party_id
303 from fnd_user fu,
304 hz_parties hp
305 where fu.user_id = fnd_global.user_id
306 and fu.employee_id is not null
307 and 'PER:' || fu.employee_id = hp.orig_system_reference);
308
309 CURSOR get_ci_info
310 IS
311 SELECT
312 pci.project_id,
313 pci.object_id,
314 pci.object_type,
315 pci.date_required,
316 pct.short_name ci_type_name,
317 pci.ci_number,
318 summary,
319 decode(highlighted_flag, 'N', 'No', 'Yes') highlighted,
320 priority_code,
321 hp.party_name,
322 pcc.class_code classification,
323 pci.record_version_number record_version_number,
324 pl.meaning ci_type_class
325 FROM pa_control_items pci, pa_ci_types_tl pct,
326 pa_ci_types_b pcb, pa_lookups pl, hz_parties hp,
327 pa_class_codes pcc
328 WHERE ci_id = p_ci_id
329 and pci.ci_type_id = pct.ci_type_id
330 and pl.lookup_code = pcb.ci_type_class_code
331 AND pcb.ci_type_id = pct.ci_type_id
332 and pl.lookup_type = 'PA_CI_TYPE_CLASSES'
333 and pci.owner_id = hp.party_id
334 AND pcc.class_code_id = pci.classification_code_id;
335
336
337
338 CURSOR get_project_info(l_project_id number) IS
339 SELECT
340 customer_name,
341 person_name,
342 carrying_out_organization_name
343 FROM pa_project_lists_v
344 WHERE project_id = l_project_id;
345
346 l_project_name VARCHAR2(30);
347 l_project_number VARCHAR2(25);
348
349 l_task_number VARCHAR2(25);
350 l_task_name VARCHAR2(30);
351 l_customer VARCHAR2(4000);
352 l_project_manager VARCHAR2(240);
353 l_org pa_project_lists_v.carrying_out_organization_name%TYPE; -- Bug 4358517.
354
355 BEGIN
356
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358
359 wf_engine.SetItemAttrText( p_item_type
360 , p_item_key
361 , 'ITEM_TYPE'
362 , p_item_type
363 );
364
365 wf_engine.SetItemAttrText( p_item_type
366 , p_item_key
367 , 'ITEM_KEY'
368 , p_item_key
369 );
370
371
372 wf_engine.SetItemAttrText(itemtype => p_item_type,
373 itemkey => p_item_key,
374 aname => 'HISTORY',
375 avalue =>
376 'PLSQL:PA_WORKFLOW_HISTORY.show_history/'||
377 p_item_type||':'||
378 p_item_key );
379
380
381 wf_engine.SetItemAttrText(itemtype => p_item_type,
382 itemkey => p_item_key,
383 aname => 'CLOB_CONTENT',
384 avalue =>
385 'plsqlclob:pa_control_items_workflow.show_clob_content/'||
386 p_ci_id);
387
388
389
390 wf_engine.SetItemAttrNumber( p_item_type
391 , p_item_key
392 , 'CI_ID'
393 , p_ci_id
394 );
395
396
397 wf_engine.SetItemAttrNumber( p_item_type
398 , p_item_key
399 , 'WF_OBJECT_ID'
400 , p_ci_id
401 );
402
403 OPEN get_project_mgr_party_id;
404 FETCH get_project_mgr_party_id INTO l_project_mgr_id;
405 CLOSE get_project_mgr_party_id;
406
407 wf_engine.SetItemAttrNumber( p_item_type
408 , p_item_key
409 , 'PROJ_MGR_ID'
410 , l_project_mgr_id
411 );
412
413 OPEN get_submitted_by_id;
414 FETCH get_submitted_by_id INTO l_submitted_by_id;
415 CLOSE get_submitted_by_id;
416
417
418 wf_engine.SetItemAttrNumber( p_item_type
422 );
419 , p_item_key
420 , 'SUBMITTED_BY_ID'
421 , l_submitted_by_id
423
424 FOR rec IN get_ci_info LOOP
425 pa_utils.getprojinfo(rec.project_id, l_project_number, l_project_name);
426
427 wf_engine.SetItemAttrNumber( p_item_type
428 , p_item_key
429 , 'PROJECT_ID'
430 , rec.project_id
431 );
432
433
434
435 wf_engine.SetItemAttrNumber( p_item_type
436 , p_item_key
437 , 'RECORD_VERSION_NUMBER'
438 , rec.record_version_number
439 );
440
441
442 wf_engine.SetItemAttrText( p_item_type
443 , p_item_key
444 , 'PROJECT_NAME'
445 ,l_project_name
446 );
447
448 wf_engine.SetItemAttrText( p_item_type
449 , p_item_key
450 , 'PROJECT_NUMBER'
451 ,l_project_number
452 );
453
454 wf_engine.SetItemAttrText( p_item_type
455 , p_item_key
456 , 'PROJECT'
457 ,l_project_name||'('||l_project_number||')'
458 );
459
460 wf_engine.SetItemAttrDate( p_item_type
461 , p_item_key
462 , 'DATE_REQUIRED'
463 ,rec.date_required
464 );
465
466 wf_engine.SetItemAttrText( p_item_type
467 , p_item_key
468 , 'SUMMARY'
469 ,rec.summary
470 );
471
472 wf_engine.SetItemAttrText( p_item_type
473 , p_item_key
474 , 'CONTROL_ITEM_TYPE'
475 ,rec.ci_type_name
476 );
477
478 wf_engine.SetItemAttrText( p_item_type
479 , p_item_key
480 , 'CONTROL_ITEM_CLASS'
481 ,rec.ci_type_class
482 );
483
484 wf_engine.SetItemAttrText( p_item_type
485 , p_item_key
486 , 'CONTROL_ITEM_NUMBER'
487 ,rec.ci_number
488 );
489
490 wf_engine.SetItemAttrText( p_item_type
491 , p_item_key
492 , 'PRIORITY'
493 ,rec.priority_code
494 );
495
496 wf_engine.SetItemAttrText( p_item_type
497 , p_item_key
498 , 'CI_OWNER'
499 ,rec.party_name
500 );
501
502 wf_engine.SetItemAttrText( p_item_type
503 , p_item_key
504 , 'CLASSIFICATION'
505 ,rec.classification
506 );
507
508 -- set project manager, organization name and customer
509 OPEN get_project_info(rec.project_id);
510
511 FETCH get_project_info INTO l_customer,l_project_manager, l_org;
512
513
514 wf_engine.SetItemAttrText( p_item_type
515 , p_item_key
516 , 'PROJECT_MANAGER'
517 ,l_project_manager
518 );
519
520
521 wf_engine.SetItemAttrText( p_item_type
522 , p_item_key
523 , 'ORGANIZATION'
524 ,l_org
525 );
526
527 wf_engine.SetItemAttrText( p_item_type
528 , p_item_key
529 , 'CUSTOMER'
530 ,l_customer
531 );
532
533 CLOSE get_project_info;
534
535 IF rec.object_type = 'PA_TASKS' THEN
536 -- set task name, task number
537 pa_utils.gettaskinfo(rec.object_id, l_task_number, l_task_name);
538 wf_engine.SetItemAttrText( p_item_type
539 , p_item_key
540 , 'TASK_NAME'
541 ,l_task_name
542 );
543
544 wf_engine.SetItemAttrText( p_item_type
545 , p_item_key
546 , 'TASK_NUMBER'
547 ,l_task_number
548 );
549
550 END IF;
551
552
553 l_url := 'JSP:/OA_HTML/OA.jsp?' ||
554 'akRegionCode=PA_CI_CI_REVIEW_LAYOUT&akRegionApplicationId=275&addBreadCrumb=RP&paCiId='||p_ci_id || '&paProjectId=' || rec.project_id;
555
556 wf_engine.SetItemAttrText( p_item_type
557 , p_item_key
558 , 'CI_LINK'
562
559 , l_url
560 );
561
563
564
565 END LOOP;
566
567
568
569 EXCEPTION
570
571 WHEN OTHERS THEN
572
573 x_msg_count := 1;
574 x_msg_data := substr(SQLERRM,1,2000);
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
576
577
578 END start_workflow;
579
580
581 /********************************************************************
582 * Procedure : set_ci_approver
583 * Parameters IN :
584 * Parameters OUT: Return Status
585 * Purpose :
586 *********************************************************************/
587 PROCEDURE set_ci_approver(
588 p_item_type IN VARCHAR2
589 ,p_item_key IN VARCHAR2
590 ,actid IN NUMBER
591 ,funcmode IN VARCHAR2
592 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
593
594 IS
595
596
597 l_approval_role varchar2(30) := NULL;
598 l_role_users varchar2(30000) := NULL;
599
600 l_ci_id NUMBER;
601 l_proj_mgr_id NUMBER;
602 l_proj_mgr_name VARCHAR2(200);
603 l_proj_mgr_full_name VARCHAR2(200);
604 l_submitted_by_id NUMBER;
605 l_user_name VARCHAR2(200);
606
607 -- Bug 6843085
608 x_return_status VARCHAR2(100);
609 x_msg_count NUMBER;
610 x_msg_data VARCHAR2(200);
611
612 CURSOR get_user_name
613 IS
614 SELECT user_name
615 FROM fnd_user
616 WHERE user_id = FND_GLOBAL.user_id;
617
618
619 CURSOR get_approver_list
620 IS
621 select user_name, party_name, email_address
622 from (
623 select fu.user_name, hp.party_name, hp.email_address
624 from fnd_user fu,
625 hz_parties hp
626 where fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
627 and hp.party_id = l_proj_mgr_id
628 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
629 union
630 select fu.user_name, hp.party_name, hp.email_address
631 from fnd_user fu,
632 hz_parties hp,
633 per_all_people_f papf
634 where
635 fu.employee_id = Substr(hp.orig_system_reference, 5, Length(hp.orig_system_reference))
636 AND 'PER:' = Substr(hp.orig_system_reference,1,4) -- 'PER:' || fu.employee_id = hp.orig_system_reference
637 and hp.party_id = l_proj_mgr_id
638 and trunc(sysdate)
639 between papf.EFFECTIVE_START_DATE
640 and Nvl(papf.effective_end_date, Sysdate + 1)
641 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
642 and papf.person_id = fu.employee_id)
643 ;
644
645 -- Bug 6843085
646 CURSOR get_prev_status(p_ci_id IN VARCHAR2) is
647 select a.old_project_status_code, a.new_project_status_code
648 from (select obj_status_change_id,
649 old_project_status_code,
650 new_project_status_code
651 from pa_obj_status_changes
652 where object_type = 'PA_CI_TYPES'
653 and object_id = p_ci_id
654 order by obj_status_change_id desc) a
655 where rownum = 1;
656
657 l_prev_status pa_obj_status_changes.old_project_status_code%TYPE;
658 l_curr_status pa_obj_status_changes.new_project_status_code%TYPE;
659 l_comment pa_ci_comments.comment_text%TYPE;
660 -- Bug 6843085
661
662 display_name VARCHAR2(2000);
663 email_address VARCHAR2(2000);
664 notification_preference VARCHAR2(2000);
665 language VARCHAR2(2000);
666 territory VARCHAR2(2000);
667
668 BEGIN
669
670
671 l_ci_id := wf_engine.GetItemAttrNumber
672 ( itemtype => p_item_type,
673 itemkey => p_item_key,
674 aname => 'CI_ID');
675
676 l_submitted_by_id := wf_engine.GetItemAttrNumber
677 ( itemtype => p_item_type,
678 itemkey => p_item_key,
679 aname => 'SUBMITTED_BY_ID');
680
681
682 l_proj_mgr_id := wf_engine.GetItemAttrNumber
683 ( itemtype => p_item_type,
684 itemkey => p_item_key,
685 aname => 'PROJ_MGR_ID');
686
687
688
689 l_approval_role := 'APPR_' ||p_item_type || p_item_key;
690
691
692 IF l_proj_mgr_id IS NOT null THEN
693
694 for v_approvers in get_approver_list loop
695
696 l_proj_mgr_name := v_approvers.user_name;
697 l_proj_mgr_full_name:= v_approvers.party_name;
698
699
700 if (l_role_users is not null) then
701 l_role_users := l_role_users || ',';
702 end if;
703
704 -- Create adhoc users
705 wf_directory.getroleinfo(v_approvers.user_name,display_name,
706 email_address,notification_preference,language,territory);
710 WF_DIRECTORY.CreateAdHocUser( name => v_approvers.user_name
707 if display_name is null THEN
708
709
711 , display_name => v_approvers.party_name
712 --, notification_preference => 'MAILTEXT'
713 , EMAIL_ADDRESS =>v_approvers.email_address);
714 END IF;
715 l_role_users := l_role_users || v_approvers.user_name;
716 end loop;
717
718 END IF;
719
720
721
722 WF_DIRECTORY.CreateAdHocRole( role_name => l_approval_role
723 , role_display_name => l_proj_mgr_full_name
724 , expiration_date => sysdate+1 -- Set expiration_date for bug#5962401
725 );
726
727
728 IF (l_role_users is NOT NULL) THEN
729
730 WF_DIRECTORY.AddUsersToAdHocRole( l_approval_role
731 , l_role_users);
732
733
734
735
736
737 wf_engine.SetItemAttrText( p_item_type
738 , p_item_key
739 , 'CI_APPROVER'
740 , l_approval_role);
741
742 wf_engine.SetItemAttrText( p_item_type
743 , p_item_key
744 , 'CI_APPROVER_NAME'
745 , l_proj_mgr_name);
746
747
748
749 resultout := wf_engine.eng_completed||':'||'T';
750
751
752 ELSE
753
754 resultout := wf_engine.eng_completed||':'||'F';
755 -- Bug 6843085
756 IF l_proj_mgr_id IS null THEN
757 OPEN get_prev_status(l_ci_id);
758 FETCH get_prev_status INTO l_prev_status, l_curr_status;
759 CLOSE get_prev_status;
760
761 pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS (
762 p_api_version => 1.0
763 ,p_init_msg_list => FND_API.G_TRUE
764 ,p_validate_only => FND_API.G_FALSE
765 ,p_ci_id => l_ci_id
766 ,p_status_code => l_prev_status
767 ,p_record_version_number => NULL
768 ,x_return_status => x_return_status
769 ,x_msg_count => x_msg_count
770 ,x_msg_data => x_msg_data);
771
772 If x_return_status = FND_API.G_RET_STS_SUCCESS then
773
774 fnd_message.set_name('PA', 'PA_CI_ERR_PM_WF_COMMENT');
775 l_comment := fnd_message.get;
776 DBMS_LOCK.SLEEP(1);
777 PA_CONTROL_ITEMS_UTILS.ADD_STATUS_CHANGE_COMMENT( p_object_type => 'PA_CI_TYPES'
778 ,p_object_id => l_ci_id
779 ,p_type_code => 'CHANGE_STATUS'
780 ,p_status_type => 'CONTROL_ITEM'
781 ,p_new_project_status => l_prev_status
782 ,p_old_project_status => l_curr_status
783 ,p_comment => l_comment
784 ,x_return_status => x_return_status
785 ,x_msg_count => x_msg_count
786 ,x_msg_data => x_msg_data );
787
788 end if;
789 end if;
790 -- Bug 6843085
791
792 END IF;
793
794
795
796 commit;
797
798
799 EXCEPTION
800
801 WHEN OTHERS THEN
802 RAISE;
803
804 END ;
805
806 /********************************************************************
807 * Procedure : set_notification_party
808 * Parameters IN :
809 * Parameters OUT: Return Status
810 * Purpose :
811 *********************************************************************/
812 PROCEDURE set_notification_party(
813 p_item_type IN VARCHAR2
814 ,p_item_key IN VARCHAR2
815 ,p_status IN VARCHAR2
816 ,actid IN NUMBER
817 ,funcmode IN VARCHAR2
818 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
819 ) IS
820
821 l_ci_id NUMBER;
822 l_owner_id NUMBER;
823
824 CURSOR get_notification_list
825 IS
826 select user_name, party_name, email_address
827 from (
828 select fu.user_name, hp.party_name, hp.email_address
829 from fnd_user fu,
830 hz_parties hp
831 where fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
832 and hp.party_id = l_owner_id
833 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
834 union
835 select fu.user_name, hp.party_name, hp.email_address
836 from fnd_user fu,
837 hz_parties hp,
838 per_all_people_f papf
839 where
840 --'PER:' || fu.employee_id = hp.orig_system_reference
841 fu.employee_id = Substr(hp.orig_system_reference, 5, Length(hp.orig_system_reference))
842 AND 'PER:' = Substr(hp.orig_system_reference,1,4)
843 and hp.party_id = l_owner_id
844 and trunc(sysdate)
845 between papf.EFFECTIVE_START_DATE
846 and Nvl(papf.effective_end_date, Sysdate + 1)
847 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
848 and papf.person_id = fu.employee_id)
849 ;
850 CURSOR get_owner_id
851 is
852 SELECT
853 owner_id
854 FROM pa_control_items
855 WHERE ci_id = l_ci_id;
856
857 l_role varchar2(30) := NULL;
858 l_role_users varchar2(30000) := NULL;
859 display_name VARCHAR2(2000);
860 email_address VARCHAR2(2000);
861 notification_preference VARCHAR2(2000);
862 language VARCHAR2(2000);
863 territory VARCHAR2(2000);
864
865 BEGIN
866
867 l_ci_id := wf_engine.GetItemAttrNumber
868 ( itemtype => p_item_type,
869 itemkey => p_item_key,
870 aname => 'CI_ID');
871
872 OPEN get_owner_id;
873 FETCH get_owner_id INTO l_owner_id;
874 CLOSE get_owner_id;
875
876 l_role := 'NOTFY_' ||p_item_type || p_item_key;
877
878 WF_DIRECTORY.CreateAdHocRole( role_name => l_role
879 , role_display_name => l_role
880 , expiration_date => sysdate+1 -- Set expiration_date for bug#5962401
881 );
882
883 for v_party in get_notification_list loop
884
885
886 if (l_role_users is not null) then
887 l_role_users := l_role_users || ',';
888 end if;
889
890 -- Create adhoc users
891 wf_directory.getroleinfo(v_party.user_name,display_name,
892 email_address,notification_preference,language,territory);
893 if display_name is null THEN
894
895 WF_DIRECTORY.CreateAdHocUser( name => v_party.user_name
896 , display_name => v_party.party_name
897 --, notification_preference => 'MAILTEXT'
898 , EMAIL_ADDRESS =>v_party.email_address);
899 END IF;
900 l_role_users := l_role_users || v_party.user_name;
901 end loop;
902
903 IF (l_role_users is NOT NULL) THEN
904 WF_DIRECTORY.AddUsersToAdHocRole( l_role, l_role_users);
905
906 wf_engine.SetItemAttrText( p_item_type
907 , p_item_key
908 , 'CI_NOTIFICATION_PARTY'
909 , l_role);
910 resultout := wf_engine.eng_completed||':'||'T';
911 ELSE
912
913 resultout := wf_engine.eng_completed||':'||'F';
914
915 END IF;
916
917
918
919
920 END;
921
922
923 END pa_control_items_wf_client;
924
[Home] [Help]