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