[Home] [Help]
PACKAGE BODY: APPS.PA_CONTROL_ITEMS_WORKFLOW
Source
1 package body pa_control_items_workflow as
2 /* $Header: PACIWFPB.pls 120.7.12000000.5 2007/07/12 10:07:48 rballamu ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8
9 FILE NAME : PACIWFPB.pls
10 DESCRIPTION :
11 This file creates package procedures that are called to
12 execute each activity in the Control Item Workflow.
13
14
15
16 HISTORY : 08/19/02 SYAO Initial Creation
17 20/01/04 sanantha Bug 3297238. FP M changes.
18 30/01/04 sukhanna Bug 3297238 FP M changes. Currently the notification list is processed for only one party id. After this
19 change, it will process the notification list for each of the party id.
20 31/01/04 sukhanna Bug 3297238 FP M changes. After this change the
21 notification list will be processed for each of the party id.
22 27/01/04 mumohan Bug# 3297238 FP M changes
23 04/02/04 sukhanna Bug 3297238 FP M changes.
24 31/05/04 sukhanna Removing the covering select clause in the
25 cursor definiton of get_notification_list & get_name
26 23/06/04 rasinha Bug# 3691192 FP M Changes
27 Added three procedures namely CLOSE_CI_ACTION,KEEP_OPEN and CANCEL_NOTIF_AND_ABORT_WF.
28 CLOSE_CI_ACTION and KEEP_OPEN are called from the PAWFCIAC workflow funtions.
29 CLOSE_CI_ACTION closes an Action without signing it off,
30 KEEP_OPEN keeps the action open and registers any comment given by the user and
31 CANCEL_NOTIF_AND_ABORT_WF cancels any open notification for an action and also aborts the workflow.
32 Also added some item attributes in the workflow PAWFCIAC.
33 30-07-04 rasinha Modified the file for Bug# 3802238.Captured the Sign-off flag value from workflow Notification
34 and updated the ci action in CI_CLOSE_ACTION_PROCEDURE.
35 18-08-04 mumohan Bug#3838957: Added the condition to exclude the end dated users in the cursor
36 get_notification_list, is_user_valid and get_name.
37 27/08/04 sanantha Bug 3787169. call the api modify_wf_clob_content
38 before passing the clob to workflow
39 24-09-04 rasinha Bug 3877985. Modified the file update the who columns in the procedure
40 CLOSE_CI_ACTION and KEEP_OPEN with action assignee and not by fnd_global.user_id.
41 01-12-04 sukhanna Bug 3974641. Replacing PA_CI_CI_REVIEW_LAYOUT AK region name with the xml file name CiCiReviewPG.
42 10-Dec-04 rasinha Bug 4049901. Modified the procedure to set the role_display_name for
43 Action Assignee notification to the full name of the action assignee.
44 03-Aug-05 raluthra Bug 4527617. Replaced the usage of fnd_user.
45 customer_id with fnd_user.person_party_id
46 for R12 ATG Mandate.
47 10-Aug-05 rasinha Bug# 4527911:
48 1)Added the procedure close_notification to close an open action notification.
49 2)Modified the KEEP_OPEN procedure to avoid adding comments if it is already being done.
50 3)Modifed the procedure CANCEL_NOTIF_AND_ABORT_WF to find out open notification for an action
51 and canel the notification. This is called when an action is cancelled.
52 08-Sep-05 raluthra Bug 4565156. Added code for Manual NOCOPY Changes
53 for usage of same variable for In and Out parameter.
54 01-Feb-06 vgottimu Bug 4923945. Changed the cursor cur_ci_status_n_owner query,
55 pa_ci_list_v is replaced with the base table pa_control_items and
56 included table hz_parties to get the owner name.
57 26-Apr-07 vvjoshi Bug#5962401:Modified set_workflow_attributes procedure to set expiration date for adhoc role.
58 25-Jun-07 rballamu Bug#6053648:Modified change_status_approved to initialize the application context.
59 =============================================================================*/
60
61 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
62
63
64 /********************************************************************
65 * Procedure : start_workflow
66 * Purpose :
67 *********************************************************************/
68 Procedure start_workflow
69 (
70 p_item_type IN VARCHAR2
71 , p_process_name IN VARCHAR2
72
73 , p_ci_id IN NUMBER
74
75 , x_item_key out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
76 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
77 , x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
78 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
79 )
80 IS
81
82 l_item_key NUMBER;
83
84 l_approval_role varchar2(30) := NULL;
85 l_role_users varchar2(300) := NULL;
86 l_last_item_key VARCHAR2(200);
87
88 l_err_code NUMBER;
89 l_err_stage VARCHAR2(30);
90 l_err_stack VARCHAR2(240);
91
92 l_project_id NUMBER;
93
94 CURSOR get_last_workflow_info
95 IS
96 SELECT MAX(item_key)
97 FROM pa_wf_processes
98 WHERE item_type = p_item_type
99 AND description = p_process_name
100 AND entity_key2 = p_ci_id
101 AND entity_key1 = l_project_id
102 AND wf_type_code = 'Control Item';
103
104 CURSOR get_project_id
105 IS
106 SELECT project_id
107 FROM pa_control_items
108 WHERE ci_id = p_ci_id;
109 BEGIN
110
111 OPEN get_project_id;
112 FETCH get_project_id INTO l_project_id;
113 CLOSE get_project_id;
114
115 SELECT pa_workflow_itemkey_s.nextval
116 INTO l_item_key
117 from dual;
118
119 x_item_key := To_char(l_item_key);
120
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122
123
124 --debug_msg ( 'before WF_ENGINE createProcess: ' || p_Process_Name);
125
126 -- create the workflow process
127 WF_ENGINE.CreateProcess( p_item_type
128 , x_item_key
129 , p_Process_Name);
130 --debug_msg ( 'after WF_ENGINE createProcess: key = ' || x_item_key);
131
132 pa_control_items_wf_client.start_workflow(
133 p_item_type
134 , p_process_name
135 , x_item_key
136 , p_ci_id
137 , x_msg_count
138 , x_msg_data
139 , x_return_status
140 );
141
142 IF x_return_status = FND_API.g_ret_sts_success then
143
144 -- cancle the last running workflow if any
145
146 OPEN get_last_workflow_info;
147 FETCH get_last_workflow_info INTO l_last_item_key;
148 IF get_last_workflow_info%found THEN
149 begin
150 -- abort this process if it is running
151 WF_ENGINE.AbortProcess( p_Item_Type
152 , l_last_Item_Key
153 );
154 EXCEPTION
155 WHEN OTHERS THEN
156 NULL;
157 END;
158
159 END IF;
160
161 --debug_msg_s1 ( 'before WF_ENGINE startProcess' );
162 --debug_msg_s1 ( 'startProcess: item_type = ' || p_item_type || ' item_key = ' || x_Item_Key );
163
164 WF_ENGINE.StartProcess(
165 p_Item_Type
166 , x_Item_Key
167 );
168 -- debug_msg_s1 ( 'after start Process: item_type = ' || p_item_type || ' item_key = ' || x_Item_Key );
169 END IF;
170
171 --debug_msg ( 'after WF_ENGINE startProcess' );
172
173 -- insert into pa_wf_process table
174
175 --debug_msg_s1 ('after workflow 1: isnertwf processes' || p_item_type);
176 --debug_msg_s1 ('after workflow 1: isnertwf processes' || l_item_key);
177
178 --debug_msg_s1 ( 'b4 get project id ' );
179
180 l_project_id := wf_engine.GetItemAttrNumber( p_item_type
181 , l_item_key
182 , 'PROJECT_ID'
183 );
184
185 --debug_msg_s1 ( 'startProcess: item_type = ' || l_project_id );
186
187 PA_WORKFLOW_UTILS.Insert_WF_Processes
188 (p_wf_type_code => 'Control Item'
189 ,p_item_type => p_item_type
190 ,p_item_key => l_item_key
191 ,p_entity_key1 => l_project_id
192 ,p_entity_key2 => p_ci_id
193 ,p_description => p_process_name
194 ,p_err_code => l_err_code
195 ,p_err_stage => l_err_stage
196 ,p_err_stack => l_err_stack
197 );
198
199 IF l_err_code <> 0 THEN
200
201 PA_UTILS.Add_Message( p_app_short_name => 'PA'
202 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
203 x_return_status := FND_API.G_RET_STS_ERROR;
204
205
206 -- abort the workflow process just launched, there is a problem
207 WF_ENGINE.AbortProcess( p_Item_Type
208 , l_Item_Key
209 );
210
211 END IF;
212
213
214 EXCEPTION
215
216 WHEN OTHERS THEN
217 --debug_msg ( 'Exception ' || substr(SQLERRM,1,2000) );
218
219
220 x_msg_count := 1;
221 x_msg_data := substr(SQLERRM,1,2000);
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223
224 END start_workflow;
225
226 /********************************************************************
227 * Procedure : Cancel_Workflow
228 * Parameters IN :
229 * Parameters OUT: Return Status
230 * Purpose :
231 *********************************************************************/
232 Procedure Cancel_Workflow
233 ( p_Item_type IN VARCHAR2
234 , p_Item_key IN VARCHAR2
235 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
236 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
237 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
238 )
239
240 IS
241
242
243 BEGIN
244
245
246 x_return_status := FND_API.G_RET_STS_SUCCESS;
247
248 --debug_msg ( 'after client cancel_workflow call' );
249
250 IF (x_return_status = FND_API.g_ret_sts_success) THEN
251 WF_ENGINE.AbortProcess( p_Item_Type
252 , p_Item_Key
253 );
254
255 --debug_msg ( 'after WF_ENGINE abortProcess' );
256
257 --debug_msg ('before get task_id');
258
259 END IF;
260
261
262 EXCEPTION
263
264 WHEN OTHERS THEN
265 --debug_msg ( 'Exception in Cancel_Wf ' || substr(SQLERRM,1,2000) );
266
267 x_msg_count := 1;
268 x_msg_data := substr(SQLERRM,1,2000);
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270
271 END Cancel_workflow;
272
273 PROCEDURE change_status_working
274 (itemtype IN VARCHAR2
275 ,itemkey IN VARCHAR2
276 ,actid IN NUMBER
277 ,funcmode IN VARCHAR2
278 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
279 IS
280
281 l_ci_id NUMBER := 0;
282 l_record_version_number NUMBER := 0;
283
284 l_return_status VARCHAR2(200);
285 l_msg_data VARCHAR2(200);
286 l_msg_count number;
287
288 l_num_of_actions NUMBER;
289
290
291 BEGIN
292
293 l_ci_id := wf_engine.GetItemAttrNumber
294 ( itemtype => itemtype,
295 itemkey => itemkey,
296 aname => 'CI_ID');
297
298
299
300
301 l_record_version_number := wf_engine.GetItemAttrNumber
302 ( itemtype => itemtype,
303 itemkey => itemkey,
304 aname => 'RECORD_VERSION_NUMBER');
305
306 pa_control_items_utils.ChangeCIStatus
307 (
308 p_validate_only=> 'F',
309 p_commit=>'T',
310
311 p_ci_id => l_ci_id,
312 p_status=>'CI_WORKING',
313
314 p_record_version_number=>l_record_version_number,
315 x_num_of_actions => l_num_of_actions,
316 x_return_status=> l_return_status,
317 x_msg_count=>l_msg_count,
318 x_msg_data=> l_msg_data
319
320 );
321
322 IF l_return_status <> 'S' then
323
324 --debug_msg_s1('Error: || ' || fnd_msg_pub.get(p_msg_index => 1,
325 -- p_encoded => FND_API.G_FALSE));
326 NULL;
327
328 END IF;
329
330
331
332
333 END ;
334
335
336 PROCEDURE change_status_rejected
337 (itemtype IN VARCHAR2
338 ,itemkey IN VARCHAR2
339 ,actid IN NUMBER
340 ,funcmode IN VARCHAR2
341 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
342 IS
343
344 l_ci_id NUMBER := 0;
345 l_record_version_number NUMBER := 0;
346 l_name VARCHAR2(200);
347 l_return_status VARCHAR2(200);
348 l_msg_data VARCHAR2(200);
349 l_msg_count number;
350 l_comment VARCHAR2(2000);
351 l_num_of_actions NUMBER;
352 l_status VARCHAR2(30);
353
354 cursor c_status(p_ci_id NUMBER, p_item_type VARCHAR2) is
355 SELECT wf_failure_status_code
356 FROM pa_project_statuses ps,
357 pa_control_items ci
358 WHERE ci.ci_id = p_ci_id
359 and ci.status_code = ps.project_status_code
360 and ps.status_type = 'CONTROL_ITEM'
361 and ps.workflow_item_type = p_item_type
362 and ps.enable_wf_flag = 'Y'
363 and ps.wf_failure_status_code is NOT NULL;
364
365 BEGIN
366
367 l_ci_id := wf_engine.GetItemAttrNumber
368 ( itemtype => itemtype,
369 itemkey => itemkey,
370 aname => 'CI_ID');
371
372 --debug_msg_s1 ('AAAAAAAAAAA report rejected' || WF_ENGINE.context_text);
373
374 l_comment := wf_engine.GetItemAttrText
375 ( itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'COMMENT');
378
379 l_name := wf_engine.GetItemAttrText( itemtype
380 , itemkey
381 , 'CI_APPROVER_NAME');
382
383
384 l_record_version_number := wf_engine.GetItemAttrNumber
385 ( itemtype => itemtype,
386 itemkey => itemkey,
387 aname => 'RECORD_VERSION_NUMBER');
388
389
390 pa_workflow_history.save_comment_history (
391 itemtype
392 ,itemkey
393 ,'REJECT'
394 ,l_name ,
395 l_comment);
396
397
398
399 -- set notification party based on the notification type
400 pa_control_items_wf_client.set_notification_party
401 (itemtype,
402 itemkey,
403 'CI_REJECTED',
404 actid,
405 funcmode,
406 resultout
407 );
408
409
410 /* Bug# 3297238 FP M changes */
411 open c_status(l_ci_id, itemtype);
412 fetch c_status into l_status;
413 close c_status;
414
415 -- debug_msg_s1 ('b4 reject the ci ' || l_return_status);
416 pa_control_items_utils.ChangeCIStatus
417 (
418 p_validate_only=> 'F',
419 p_commit=>'T',
420 p_ci_id => l_ci_id,
421 -- p_status=>'CI_REJECTED', /* Bug# 3297238 FP M changes */
422 p_status=> l_status, /* Bug# 3297238 FP M changes */
423 p_comment=> l_comment,
424 p_record_version_number=>l_record_version_number,
425 x_num_of_actions => l_num_of_actions,
426 x_return_status=> l_return_status,
427 x_msg_count=>l_msg_count,
428 x_msg_data=> l_msg_data
429
430 );
431
432 --debug_msg_s1 ('after reject the ci ' || l_return_status);
433
434 IF l_return_status <> 'S' then
435
436 --debug_msg_s1('Error: || ' || fnd_msg_pub.get(p_msg_index => 1,
437 --p_encoded => FND_API.G_FALSE));
438 NULL;
439
440
441 END IF;
442
443 END ;
444
445
446 PROCEDURE change_status_approved
447 (itemtype IN VARCHAR2
448 ,itemkey IN VARCHAR2
449 ,actid IN NUMBER
450 ,funcmode IN VARCHAR2
451 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
452 IS
453
454 l_ci_id NUMBER := 0;
455 l_status VARCHAR2(30);
456 l_record_version_number NUMBER := 0;
457 l_name VARCHAR2(200);
458
459 l_return_status VARCHAR2(200);
460 l_msg_data VARCHAR2(200);
461 l_msg_count number;
462
463 l_comment VARCHAR2(2000);
464
465 l_num_of_actions NUMBER;
466
467 cursor c_status(p_ci_id NUMBER, p_item_type VARCHAR2) is
468 SELECT wf_success_status_code
469 FROM pa_project_statuses ps,
470 pa_control_items ci
471 WHERE ci.ci_id = p_ci_id
472 and ci.status_code = ps.project_status_code
473 and ps.status_type = 'CONTROL_ITEM'
474 and ps.workflow_item_type = p_item_type
475 and ps.enable_wf_flag = 'Y'
476 and ps.wf_success_status_code is NOT NULL;
477
478 -- Added for Bug 6053648
479 cursor c_user_id(p_user_name fnd_user.user_name%type) is
480 select user_id
481 from fnd_user
482 where user_name = p_user_name;
483
484 l_user_id fnd_user.user_id%type;
485 -- End for Bug 6053648
486
487
488 BEGIN
489
490 l_ci_id := wf_engine.GetItemAttrNumber
491 ( itemtype => itemtype,
492 itemkey => itemkey,
493 aname => 'CI_ID');
494
495
496 -- debug_msg_s1 ('AAAAAAAAAAA report approved ' || WF_ENGINE.context_text);
497
498 l_comment := wf_engine.GetItemAttrText
499 ( itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'COMMENT');
502
503 l_name := wf_engine.GetItemAttrText( itemtype
504 , itemkey
505 , 'CI_APPROVER_NAME');
506
507 -- Added for Bug 6053648
508 l_user_id := fnd_global.user_id;
509 -- If approved via E-Mail we need to set the application context explicitly
510 if (l_user_id is null or
511 l_user_id = 0 or
512 l_user_id = -1) then
513 begin
514 open c_user_id(l_name);
515 fetch c_user_id into l_user_id;
516 close c_user_id;
517 fnd_global.apps_initialize(
518 user_id=>l_user_id,
519 resp_id=>fnd_global.resp_id,
520 resp_appl_id=>275);
521 end;
522 end if;
523 -- End for Bug 6053648
524
525
526 -- debug_msg_s1 ('AAAAAAAAAAA report approved approver name' || l_name);
527
528 l_record_version_number := wf_engine.GetItemAttrNumber
529 ( itemtype => itemtype,
530 itemkey => itemkey,
531 aname => 'RECORD_VERSION_NUMBER');
532
533 IF l_name IS NOT NULL THEN
534
535 pa_workflow_history.save_comment_history (
536 itemtype
537 ,itemkey
538 ,'APPROVE'
539 ,l_name,
540 l_comment);
541 END IF;
542
543
544
545 -- set notification party based on the notification type
546
547
548
549 pa_control_items_wf_client.set_notification_party
550 (itemtype,
551 itemkey,
552 'CI_APPROVED',
553 actid,
554 funcmode,
555 resultout
556 );
557
558
559 --debug_msg_s1 ('b4 approve the ci ');
560
561 /* Bug# 3297238 FP M changes */
562 open c_status(l_ci_id, itemtype);
563 fetch c_status into l_status;
564 close c_status;
565
566 pa_control_items_utils.ChangeCIStatus
567 (
568 p_validate_only=> 'F',
569 p_commit=>'T',
570 p_ci_id => l_ci_id,
571 -- p_status=>'CI_APPROVED', /* Bug# 3297238 FP M changes */
572 p_status=> l_status, /* Bug# 3297238 FP M changes */
573 p_comment=> l_comment,
574 p_record_version_number=>l_record_version_number,
575 x_num_of_actions => l_num_of_actions,
576 x_return_status=> l_return_status,
577 x_msg_count=>l_msg_count,
578 x_msg_data=> l_msg_data
579
580 );
581
582
583 --debug_msg_s1 ('after approve the ci ' || l_return_status);
584
585 IF l_return_status <> 'S' then
586
587 --debug_msg_s1('Error: || ' || fnd_msg_pub.get(p_msg_index => 1,
588 -- p_encoded => FND_API.G_FALSE));
589
590 NULL;
591
592 END IF;
593
594
595 END ;
596
597
598 PROCEDURE is_approver_same_as_submitter(
599 itemtype IN VARCHAR2
600 ,itemkey IN VARCHAR2
601 ,actid IN NUMBER
602 ,funcmode IN VARCHAR2
603 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
604
605 IS
606
607 l_proj_mgr_id NUMBER;
608 l_submitter_id NUMBER;
609 l_user_name VARCHAR2(200);
610
611 CURSOR get_user_name
612 IS
613 SELECT user_name
614 FROM fnd_user
615 WHERE user_id = FND_GLOBAL.user_id;
616
617 BEGIN
618
619 l_proj_mgr_id := wf_engine.GetItemAttrNumber
620 ( itemtype => itemtype,
621 itemkey => itemkey,
622 aname => 'PROJ_MGR_ID');
623
624 l_submitter_id := wf_engine.GetItemAttrNumber
625 ( itemtype => itemtype,
626 itemkey => itemkey,
627 aname => 'SUBMITTED_BY_ID');
628
629 --debug_msg_s1 ('l_proj_mgr_id = ' || l_proj_mgr_id);
630 --debug_msg_s1 ('l_submitter_id = ' || l_submitter_id);
631
632
633 OPEN get_user_name;
634 FETCH get_user_name INTO l_user_name;
635 CLOSE get_user_name;
636
637 --debug_msg_s1('b4 save history');
638
639
640 pa_workflow_history.save_comment_history (
641 itemtype
642 ,itemkey
643 ,'SUBMIT'
644 , l_user_name
645 ,'');
646
647 IF l_submitter_id = l_proj_mgr_id THEN
648
649 wf_engine.SetItemAttrText( itemtype
650 , itemkey
651 , 'CI_APPROVER_NAME'
652 , l_user_name);
653
654 resultout := wf_engine.eng_completed||':'||'T';
655 ELSE
656
657 resultout := wf_engine.eng_completed||':'||'F';
658 END IF;
659
660
661 END;
662
663
664
665
666 PROCEDURE check_status_change
667 (itemtype IN VARCHAR2
668 ,itemkey IN VARCHAR2
669 ,actid IN NUMBER
670 ,funcmode IN VARCHAR2
671 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
672 IS
673
674 l_ci_id NUMBER := 0;
675 l_status VARCHAR2(30);
676 l_ret VARCHAR2(240);
677
678 CURSOR get_status IS
679 SELECT status_code
680 FROM pa_control_items
681 WHERE ci_id = l_ci_id;
682
683
684 BEGIN
685
686 l_ci_id := wf_engine.GetItemAttrNumber
687 ( itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'CI_ID');
690
691 OPEN get_status;
692 FETCH get_status INTO l_status;
693
694 CLOSE get_status;
695
696 wf_engine.SetItemAttrText
697 ( itemtype,
698 itemkey,
699 'STATUS_CODE',
700 l_status);
701
702
703 IF l_status = 'CI_APPROVED' THEN
704 resultout := wf_engine.eng_completed||':'||'APPROVED';
705 ELSIF l_status = 'CI_REJECTED' THEN
706 resultout := wf_engine.eng_completed||':'||'REJECTED';
707 END IF;
708
709
710 -- added by syao
711 -- set notification party based on the notification type
712 pa_control_items_wf_client.set_notification_party
713 (itemtype,
714 itemkey,
715 l_status,
716 actid,
717 funcmode,
718 l_ret
719 );
720
721
722 END ;
723
724 PROCEDURE approval_request_post_notfy(
725 itemtype IN VARCHAR2
726 ,itemkey IN VARCHAR2
727 ,actid IN NUMBER
728 ,funcmode IN VARCHAR2
729 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
730
731 IS
732 BEGIN
733
734 resultout:='COMPLETE:'||'SUCCESS';
735 --resultout := wf_engine.eng_completed||':'||'T';
736
737 END;
738
739 PROCEDURE forward_notification(
740 itemtype IN VARCHAR2
741 ,itemkey IN VARCHAR2
742 ,actid IN NUMBER
743 ,funcmode IN VARCHAR2
744 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
745
746 IS
747
748 l_comment VARCHAR2(2000);
749 l_forward_to VARCHAR2(200);
750 l_forward_to_display_name VARCHAR2(200); -- Bug 4565156.
751 l_forward_to_name VARCHAR2(200);
752 l_name VARCHAR2(200);
753 l_user_id NUMBER;
754
755 l_error_msg VARCHAR2(2000);
756
757
758 CURSOR is_user_valid
759 IS
760 SELECT user_id FROM
761 fnd_user
762 WHERE user_name = l_forward_to
763 and trunc(sysdate) between start_date and nvl(end_date, sysdate); /* Bug#3838957 */
764
765
766 CURSOR get_name
767 IS
768 -- select party_name from
769 --(
770 select hp.party_name
771 from fnd_user fu,
772 hz_parties hp
773 where fu.user_name = l_forward_to --fnd_global.user_id
774 and fu.employee_id is null
775 and fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
776 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
777 union
778 select hp.party_name
779 from fnd_user fu,
780 hz_parties hp
781 where fu.user_name = l_forward_to--fnd_global.user_id
782 and fu.employee_id is not null
783 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
784 and 'PER:' || fu.employee_id = hp.orig_system_reference;
785 --);
786
787 display_name VARCHAR2(2000);
788 email_address VARCHAR2(2000);
789 notification_preference VARCHAR2(2000);
790 language VARCHAR2(2000);
791 territory VARCHAR2(2000);
792 BEGIN
793
794 --debug_msg_s1 ('call forward AAAAAAAAAAA' || funcmode);
795
796 IF funcmode = 'RUN' then
797 l_comment := wf_engine.GetItemAttrText
798 ( itemtype => itemtype,
799 itemkey => itemkey,
800 aname => 'COMMENT');
801
802 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment' || funcmode);
803 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment' || actid);
804
805
806 l_forward_to := wf_engine.GetItemAttrText
807 ( itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'FORWARD_TO');
810
811 l_forward_to_display_name := l_forward_to; -- Bug 4565156.
812
813 OPEN is_user_valid;
814 FETCH is_user_valid INTO l_user_id;
815 IF is_user_valid%notfound THEN
816 -- the forward to is invalid
817 fnd_message.set_name ('PO', 'PO_WF_NOTIF_INVALID_FORWARD');
818 l_error_msg := fnd_message.get;
819
820 wf_engine.SetItemAttrText
821 ( itemtype,
822 itemkey,
823 'WRONG_FORWARD',
824 l_error_msg);
825
826
827 ELSE
828 -- the forward is OK
829 -- 1. change the notification party
830 -- 2. save the comment to history table
831
832 wf_directory.getroleinfo(l_forward_to,display_name,
833 email_address,notification_preference,language,territory);
834 if display_name is null THEN
835 --IF NOT wf_directory.useractive (l_forward_to) THEN
836
837 --debug_msg('Add user');
838
839 WF_DIRECTORY.CreateAdHocUser( name => l_forward_to
840 , display_name => l_forward_to_display_name -- Bug 4565156.
841 --, notification_preference => 'MAILTEXT'
842 , EMAIL_ADDRESS =>'');
843 END IF;
844
845 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment: forward to = ' || l_forward_to);
846
847 wf_engine.SetItemAttrText( itemtype
848 , itemkey
849 , 'CI_APPROVER'
850 , l_forward_to);
851
852
853 OPEN get_name ;
854 FETCH get_name INTO l_forward_to_name;
855 CLOSE get_name;
856
857 l_name := wf_engine.GetItemAttrText( itemtype
858 , itemkey
859 , 'CI_APPROVER_NAME');
860
861
862
863 wf_engine.SetItemAttrText( itemtype
864 , itemkey
865 , 'CI_APPROVER_NAME'
866 , l_forward_to);
867
868
869
870 pa_workflow_history.save_comment_history (
871 itemtype
872 ,itemkey
873 ,'FORWARD'
874 ,l_name
875 ,l_comment);
876
877 wf_engine.SetItemAttrText
878 ( itemtype,
879 itemkey ,
880 'COMMENT',
881 ''
882 );
883
884 wf_engine.SetItemAttrText( itemtype
885 , itemkey
886 , 'RESULT'
887 , '');
888
889 wf_engine.SetItemAttrText( itemtype
890 , itemkey
891 , 'FORWARD_TO'
892 , '');
893
894 END IF;
895 END IF;
896
897
898 resultout:='COMPLETE:'||'SUCCESS';
899
900 --resultout := wf_engine.eng_completed||':'||'T';
901
902
903 END;
904
905 PROCEDURE show_clob_content
906 (document_id IN VARCHAR2,
907 display_type IN VARCHAR2,
908 document IN OUT NOCOPY clob, --File.Sql.39 bug 4440895
909 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
910 IS
911
912 l_content clob;
913
914
915 CURSOR get_report_info IS
916 /* SELECT pprv.report_content FROM
917 pa_progress_report_vers pprv
918 where
919 pprv.version_id = document_id
920 AND pprv.object_type = 'PA_PROJECTS'*/
921 select PAGE_CONTENT from PA_PAGE_CONTENTS
922 where object_Type = 'PA_CONTROL_ITEMS'
923 and pk1_value = document_id;
924
925 l_size number;
926
927 l_chunk_size pls_integer:=10000;
928 l_copy_size int;
929 l_pos int := 0;
930
931 l_line varchar2(30000) := '' ; -- Bug 2885704 Changed size from 10000 to 30000
932
933 --Bug 3787169
934 l_return_status varchar2(1);
935 l_msg_count number;
936 l_msg_data varchar2(2000);
937
938
939 BEGIN
940
941 --debug_msg_s1 ('get clob content' || document_id);
942 open get_report_info;
943 fetch get_report_info into l_content;
944
945 IF (get_report_info%found) then
946 close get_report_info;
947
948 -- parse the retrieved clob data
949
950 l_size := dbms_lob.getlength(l_content);
951
952 --debug_msg_s1 ('get clob content size' || l_size);
953
954 l_pos := 1;
955 l_copy_size := 0;
956
957 --debug_msg_s1 ('in loop size 1' || l_copy_size);
958 --debug_msg_s1 ('in loop size 2' || l_chunk_size);
959
960 while l_copy_size < l_size loop
961
962 --debug_msg_s1 ('before read ');
963
964 dbms_lob.read(l_content,l_chunk_size,l_pos,l_line);
965
966 -- debug_msg_s1 (l_line);
967 --debug_msg_s1 ('in loop size 1' || l_copy_size);
968
969 dbms_lob.write(document,l_chunk_size,l_pos,l_line);
970
971 l_copy_size := l_copy_size + l_chunk_size;
972 l_pos := l_pos + l_chunk_size;
973 end loop;
974
975 /*
976 Bug 3787169. The following api is called so as to clean the html for the class
977 attribute.
978 */
979
980 pa_workflow_utils.modify_wf_clob_content(
981 p_document => document
982 ,x_return_status => l_return_status
983 ,x_msg_count => l_msg_count
984 ,x_msg_data => l_msg_data
985 );
986
987 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
988 WF_NOTIFICATION.WriteToClob(document, 'Report Content Generation failed');
989 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
990 end if;
991
992 --debug_msg_s1 ('total copy size' || l_copy_size);
993
994 --dbms_lob.writeappend(document, 5, '12345');
995 ELSE
996 close get_report_info;
997 END IF;
998
999
1000 document_type := 'text/html';
1001
1002 --debug_msg_s1 ('end' );
1003
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007
1008 --debug_msg_s1('Error '||TO_CHAR(SQLCODE)||': '||substr(Sqlerrm, 255));
1009
1010
1011 WF_NOTIFICATION.WriteToClob(document, 'Report Content Generation failed');
1012 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
1013
1014 END show_clob_content;
1015
1016
1017 /*==================================================================
1018 This api will start the workflow when passed with the required
1019 arguments.
1020 Bug 3297238. FP M changes.
1021 =================================================================*/
1022
1023
1024 PROCEDURE START_NOTIFICATION_WF
1025 ( p_item_type In VARCHAR2
1026 ,p_process_name In VARCHAR2
1027 ,p_ci_id In pa_control_items.ci_id%TYPE
1028 ,p_action_id In pa_ci_actions.ci_action_id%TYPE
1029 ,x_item_key Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1030 ,x_return_status Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1031 ,x_msg_count Out NOCOPY NUMBER --File.Sql.39 bug 4440895
1032 ,x_msg_data Out NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1033 AS
1034
1035 l_item_key NUMBER;
1036 l_last_item_key VARCHAR2(200);
1037 l_project_id pa_projects_all.project_id%TYPE;
1038 l_wf_type_code pa_wf_processes.wf_type_code%TYPE;
1039 l_entity_key1 pa_wf_processes.entity_key1%TYPE;
1040 l_entity_key2 pa_wf_processes.entity_key2%TYPE;
1041
1042 l_msg_count NUMBER := 0;
1043 l_data VARCHAR2(2000);
1044 l_msg_data VARCHAR2(2000);
1045 l_msg_index_out NUMBER;
1046 l_debug_mode VARCHAR2(1);
1047 l_err_code NUMBER;
1048 l_err_stage VARCHAR2(30);
1049 l_err_stack VARCHAR2(240);
1050
1051 l_debug_level2 CONSTANT NUMBER := 2;
1052 l_debug_level3 CONSTANT NUMBER := 3;
1053 l_debug_level4 CONSTANT NUMBER := 4;
1054 l_debug_level5 CONSTANT NUMBER := 5;
1055
1056 l_module_name VARCHAR2(100) := 'pa.plsql.START_NOTIFICATION_WF';
1057 Invalid_Arg_Exc_CI Exception;
1058
1059 --cursor to obtain the project id.
1060 CURSOR get_project_id(c_ci_id pa_control_items.ci_id%TYPE)
1061 IS
1062 SELECT project_id
1063 FROM pa_control_items
1064 WHERE ci_id = c_ci_id;
1065
1066 BEGIN
1067 x_msg_count := 0;
1068 x_return_status := FND_API.G_RET_STS_SUCCESS;
1069 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1070
1071 IF l_debug_mode = 'Y' THEN
1072 pa_debug.set_curr_function( p_function => 'START_NOTIFICATION_WF',
1073 p_debug_mode => l_debug_mode );
1074 END IF;
1075
1076 -- Check for business rules violations
1077
1078 IF l_debug_mode = 'Y' THEN
1079 pa_debug.g_err_stage:= 'Validating input parameters';
1080 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1081 pa_debug.g_err_stage:= 'p_item_type = '|| p_item_type;
1082 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1083 pa_debug.g_err_stage:= 'p_process_name = '|| p_process_name;
1084 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1085 pa_debug.g_err_stage:= 'p_ci_id = '|| p_ci_id;
1086 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1087 pa_debug.g_err_stage:= 'p_action_id = '|| p_action_id;
1088 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1089 END IF;
1090
1091 IF (p_item_type IS NULL) OR
1092 (p_process_name IS NULL) OR
1093 (p_ci_id IS NULL)
1094 THEN
1095 PA_UTILS.ADD_MESSAGE
1096 (p_app_short_name => 'PA',
1097 p_msg_name => 'PA_INV_PARAM_PASSED');
1098 RAISE Invalid_Arg_Exc_CI;
1099 END IF;
1100
1101
1102 OPEN get_project_id(p_ci_id);
1103 FETCH get_project_id INTO l_project_id;
1104 CLOSE get_project_id;
1105
1106 --Identify the wf_type_code based on action_id parameter.
1107 if(p_action_id is NULL) then
1108 l_wf_type_code := 'Control Item';
1109 l_entity_key1 := l_project_id;
1110 l_entity_key2 := p_ci_id;
1111 else
1112 l_wf_type_code := 'Control Item Action';
1113 l_entity_key1 := l_project_id;
1114 l_entity_key2 := p_action_id;
1115 end if;
1116
1117 -- Get the item key from sequence.
1118 SELECT pa_workflow_itemkey_s.nextval
1119 INTO l_item_key
1120 from dual;
1121 x_item_key := To_char(l_item_key);
1122
1123
1124 IF l_debug_mode = 'Y' THEN
1125 pa_debug.g_err_stage:= 'x_item_key = '|| x_item_key;
1126 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1127 END IF;
1128 -- create the workflow process
1129 WF_ENGINE.CreateProcess(p_item_type
1130 ,x_item_key
1131 ,p_process_name);
1132
1133
1134 -- The following API will set all the required attributes for the workflow to function.
1135
1136 pa_control_items_workflow.set_workflow_attributes(
1137 p_item_type => p_item_type
1138 ,p_process_name => p_process_name
1139 ,p_ci_id => p_ci_id
1140 ,p_action_id => p_action_id
1141 ,p_item_key => x_item_key
1142 ,x_return_status => x_return_status
1143 ,x_msg_count => x_msg_count
1144 ,x_msg_data => x_msg_data
1145 );
1146
1147
1148 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1149 IF l_debug_mode = 'Y' THEN
1150 pa_debug.g_err_stage:= 'Error calling pa_control_item_workflow.SET_WORKFLOW_ATTRIBUTES';
1151 pa_debug.write('START_NOTIFICATION_WF: ' || l_module_name,pa_debug.g_err_stage,l_debug_level5);
1152
1153 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1154 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
1155 END IF;
1156 RAISE Invalid_Arg_Exc_CI;
1157 END IF;
1158
1159 IF l_debug_mode = 'Y' THEN
1160 pa_debug.g_err_stage:= 'returned from pa_control_items_workflow.set_workflow_attributes';
1161 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1162 END IF;
1163
1164
1165 WF_ENGINE.StartProcess(p_item_type
1166 ,x_item_key);
1167
1168 PA_WORKFLOW_UTILS.Insert_WF_Processes
1169 (p_wf_type_code => l_wf_type_code
1170 ,p_item_type => p_item_type
1171 ,p_item_key => l_item_key
1172 ,p_entity_key1 => l_entity_key1
1173 ,p_entity_key2 => l_entity_key2
1174 ,p_description => p_process_name
1175 ,p_err_code => l_err_code
1176 ,p_err_stage => l_err_stage
1177 ,p_err_stack => l_err_stack
1178 );
1179
1180 IF l_err_code <> 0 THEN
1181 -- abort the workflow process just launched, there is a problem
1182 WF_ENGINE.AbortProcess(p_Item_Type
1183 ,l_Item_Key);
1184
1185 --Log an error message and go to exception section.
1186 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1187 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
1188 x_return_status := FND_API.G_RET_STS_ERROR;
1189 Raise Invalid_Arg_Exc_CI;
1190 END IF;
1191
1192
1193 IF l_debug_mode = 'Y' THEN
1194 pa_debug.g_err_stage:= 'Exiting START_NOTIFICATION_WF';
1195 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1196 pa_debug.reset_curr_function;
1197 END IF;
1198 EXCEPTION
1199
1200 WHEN Invalid_Arg_Exc_CI THEN
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 l_msg_count := FND_MSG_PUB.count_msg;
1203
1204 IF get_project_id%ISOPEN THEN
1205 CLOSE get_project_id;
1206 END IF;
1207
1208 IF l_msg_count = 1 and x_msg_data IS NULL THEN
1209 PA_INTERFACE_UTILS_PUB.get_messages
1210 (p_encoded => FND_API.G_TRUE
1211 ,p_msg_index => 1
1212 ,p_msg_count => l_msg_count
1213 ,p_msg_data => l_msg_data
1214 ,p_data => l_data
1215 ,p_msg_index_out => l_msg_index_out);
1216 x_msg_data := l_data;
1217 x_msg_count := l_msg_count;
1218 ELSE
1219 x_msg_count := l_msg_count;
1220 END IF;
1221 IF l_debug_mode = 'Y' THEN
1222 pa_debug.reset_curr_function;
1223 END IF;
1224
1225 RETURN;
1226
1227 WHEN others THEN
1228
1229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1230 x_msg_count := 1;
1231 x_msg_data := SQLERRM;
1232
1233 IF get_project_id%ISOPEN THEN
1234 CLOSE get_project_id;
1235 END IF;
1236
1237 FND_MSG_PUB.add_exc_msg
1238 ( p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW'
1239 ,p_procedure_name => 'START_NOTIFICATION_WF'
1240 ,p_error_text => x_msg_data);
1241
1242 IF l_debug_mode = 'Y' THEN
1243 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1244 pa_debug.write(l_module_name,pa_debug.g_err_stage,
1245 l_debug_level5);
1246 pa_debug.reset_curr_function;
1247 END IF;
1248 RAISE;
1249 END START_NOTIFICATION_WF;
1250
1251 /*==================================================================
1252 The required arguments for the workflow are set in this API. This
1253 API also identifies to whom the notification has to be sent to.
1254 Bug 3297238. FP M changes.
1255 =================================================================*/
1256 PROCEDURE set_workflow_attributes
1257 ( p_item_type In VARCHAR2
1258 ,p_process_name In VARCHAR2
1259 ,p_ci_id In pa_control_items.ci_id%TYPE
1260 ,p_action_id In pa_ci_actions.ci_action_id%TYPE
1261 ,p_item_key In NUMBER
1262 ,x_return_status Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1263 ,x_msg_count Out NOCOPY NUMBER --File.Sql.39 bug 4440895
1264 ,x_msg_data Out NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1265 AS
1266
1267 l_msg_count NUMBER := 0;
1268 l_data VARCHAR2(2000);
1269 l_msg_data VARCHAR2(2000);
1270 l_msg_index_out NUMBER;
1271 l_debug_mode VARCHAR2(1);
1272 l_err_code NUMBER;
1273 l_err_stage VARCHAR2(30);
1274 l_err_stack VARCHAR2(240);
1275
1276 l_debug_level2 CONSTANT NUMBER := 2;
1277 l_debug_level3 CONSTANT NUMBER := 3;
1278 l_debug_level4 CONSTANT NUMBER := 4;
1279 l_debug_level5 CONSTANT NUMBER := 5;
1280
1281 l_module_name VARCHAR2(100) := 'pa.plsql.set_workflow_attributes';
1282 Invalid_Arg_Exc_CI Exception;
1283
1284 l_project_id pa_projects_all.project_id%TYPE;
1285 l_url VARCHAR2(2000);
1286 l_act_url VARCHAR2(2000);
1287 l_project_name pa_projects_all.name%TYPE;
1288 l_project_number pa_projects_all.segment1%TYPE;
1289 l_customer pa_project_lists_v.customer_name%TYPE;
1290 l_project_manager pa_project_lists_v.person_name%TYPE;
1291 l_org pa_project_lists_v.carrying_out_organization_name%TYPE;
1292 l_owner_id pa_control_items.owner_id%TYPE;
1293
1294 l_action_number pa_ci_actions_v.ci_action_number%TYPE;
1295 l_action_request pa_ci_actions_v.comment_text%TYPE;
1296 l_action_requestor pa_ci_actions_v.create_name%TYPE;
1297 l_action_date_required pa_ci_actions_v.date_required%TYPE;
1298 l_assign_party_id pa_ci_actions_v.assign_party_id%TYPE;
1299 l_create_party_id pa_ci_actions_v.create_party_id%TYPE;
1300 l_action_status_code pa_ci_actions_v.status_code%TYPE;
1301 --FP.M.IB1 Sanity
1302 l_action_closure_comment pa_ci_actions_v.cancel_comment%TYPE;
1303
1304
1305 l_role varchar2(30) := NULL;
1306 l_role_users varchar2(30000) := NULL;
1307 display_name VARCHAR2(2000);
1308 email_address VARCHAR2(2000);
1309 notification_preference VARCHAR2(2000);
1310 language VARCHAR2(2000);
1311 territory VARCHAR2(2000);
1312 l_priority_name pa_lookups.meaning%TYPE;
1313 l_comment_text pa_ci_comments.comment_text%TYPE;
1314 l_loop_var1 NUMBER := 1;
1315 l_record_version_number NUMBER;
1316 l_ci_owner_id pa_control_items.owner_id%TYPE;
1317 l_ci_owner_name VARCHAR2(2000);
1318 l_ci_status_code pa_control_items.status_code%TYPE;
1319 l_ci_status_name VARCHAR2(2000);
1320 l_action_type pa_ci_actions_v.action_type%TYPE;
1321 l_action_type_code pa_ci_actions_v.action_type_code%TYPE;
1322 l_last_updated pa_ci_actions_v.last_update_date%TYPE;
1323 l_action_status_meaning pa_ci_actions_v.status_meaning%TYPE;
1324 l_sign_off_req_flag pa_ci_actions_v.sign_off_required_flag%TYPE;
1325 l_sign_off_req_meaning pa_ci_actions_v.sign_off_required_flag_meaning%TYPE;
1326 l_role_display_name per_all_people_f.full_name%TYPE; --Added for bug 4049901
1327
1328 -- This cursor gets the info about the control item.
1329 CURSOR get_ci_info
1330 IS
1331 SELECT
1332 pci.project_id,
1333 pci.date_required,
1334 pct.name ci_type_name,
1335 pct.short_name ci_type_sn,
1336 pci.ci_number,
1337 pci.owner_id,
1338 summary,
1339 priority_code,
1340 pcc.class_code classification,
1341 pci.record_version_number record_version_number,
1342 pl.meaning ci_type_class,
1343 pcb.ci_type_class_code
1344 FROM pa_control_items pci,
1345 pa_ci_types_tl pct,
1346 pa_ci_types_b pcb,
1347 pa_lookups pl,
1348 pa_class_codes pcc
1349 WHERE ci_id = p_ci_id
1350 and pci.ci_type_id = pct.ci_type_id
1351 and pl.lookup_code = pcb.ci_type_class_code
1352 AND pcb.ci_type_id = pct.ci_type_id
1353 and pl.lookup_type = 'PA_CI_TYPE_CLASSES'
1354 AND pcc.class_code_id = pci.classification_code_id;
1355
1356 --This cursor gets the info about the project.
1357 CURSOR get_project_info(l_project_id number)
1358 IS
1359 SELECT
1360 customer_name,
1361 person_name,
1362 carrying_out_organization_name
1363 FROM pa_project_lists_v
1364 WHERE project_id = l_project_id;
1365
1366 --This cursor gets the info about the action.
1367 CURSOR cur_ci_action_info(c_action_id pa_ci_actions.ci_action_id%TYPE)
1368 IS
1369 select ci_action_number,
1370 date_required,
1371 create_name,
1372 comment_text,
1373 assign_party_id,
1374 create_party_id,
1375 status_code,
1376 cancel_comment,
1377 record_version_number,
1378 action_type_code,
1379 action_type,
1380 last_update_date,
1381 status_meaning,
1382 sign_off_required_flag,
1383 sign_off_required_flag_meaning--FP.M.IB1 Sanity
1384 from pa_ci_actions_v
1385 where ci_action_id = c_action_id;
1386
1387 --Bug 4923945 Begining of Code changes.
1388 --This Cursor is added for the bug# 3691192 to get Ci Owner and Ci Status
1389 CURSOR cur_ci_status_n_owner(p_ci_id number)
1390 IS
1391 select pci.status_code,
1392 pps.project_status_name,
1393 pci.owner_id,
1394 hzp.party_name
1395 from pa_control_items pci,
1396 pa_project_statuses pps,
1397 hz_parties hzp
1398 where pci.ci_id = p_ci_id AND
1399 pci.status_code=pps.project_status_code AND
1400 hzp.party_id = pci.owner_id;
1401
1402 --Bug 4923945 End of Code Changes.
1403
1404
1405
1406
1407 --This cursor gets the users to whom the notification should be sent to.
1408 CURSOR get_notification_list(c_owner_id pa_control_items.owner_id%TYPE)
1409 IS
1410 --select user_name, party_name, email_address
1411 --from (
1412 select fu.user_name, hp.party_name, hp.email_address
1413 from fnd_user fu,
1414 hz_parties hp
1415 where
1416 fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
1417 and hp.party_id = c_owner_id
1418 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
1419 union
1420 select fu.user_name, hp.party_name, hp.email_address
1421 from
1422 fnd_user fu,
1423 hz_parties hp,
1424 per_all_people_f papf
1425 where
1426 fu.employee_id = Substr(hp.orig_system_reference, 5, Length(hp.orig_system_reference))
1427 AND 'PER:' = Substr(hp.orig_system_reference,1,4)
1428 and hp.party_id = c_owner_id
1429 and trunc(sysdate) between papf.EFFECTIVE_START_DATE and Nvl(papf.effective_end_date, Sysdate + 1)
1430 and trunc(sysdate) between fu.start_date and nvl(fu.end_date, sysdate) /* Bug#3838957 */
1431 and papf.person_id = fu.employee_id;
1432 --);
1433
1434 --This cursor helps to get meaning given the lookup type and code.
1435 Cursor get_lookup_meaning(c_lookup_type pa_lookups.lookup_type%TYPE, c_lookup_code pa_lookups.lookup_code%TYPE)
1436 IS
1437 Select
1438 lkp.meaning
1439 from
1440 pa_lookups lkp
1441 where
1442 lkp.lookup_type = c_lookup_type
1443 and lkp.lookup_code = c_lookup_code;
1444
1445 --This cursor gets the change owner comment text.
1446 Cursor get_comment_text(c_ci_id pa_control_items.ci_id%TYPE)
1447 IS
1448 select comment_text
1449 from pa_ci_comments
1450 where ci_comment_id =
1451 (select max(ci_comment_id) from pa_ci_comments where ci_id = c_ci_id and type_code = 'CHANGE_OWNER');
1452
1453 -- This cursor gets the partyid of all the people involved in the hierarchy
1454 Cursor get_parties_in_hierarchy(c_action_id pa_ci_actions.ci_action_id%TYPE)
1455 Is
1456 select PA_UTILS.get_party_id(created_by) party_id
1457 from pa_ci_actions
1458 start with ci_action_id = c_action_id
1459 connect by prior source_ci_action_id = ci_action_id;
1460
1461 -- Added the cursor for bug 4049901
1462 -- This cursor gets the full name of the assignee of an action
1463 Cursor get_role_display_name( p_party_id per_all_people_f.party_id%TYPE)
1464 IS
1465 select full_name
1466 from per_all_people_f
1467 where party_id= p_party_id
1468 and sysdate between nvl(effective_start_date,sysdate) and nvl(effective_end_date,sysdate)
1469 and rownum=1;
1470
1471 -- This table contains PartyId's of all involved in hirarchy
1472 type l_table is table of pa_control_items.owner_id%TYPE index by binary_integer;
1473 PartyId_Tbl l_table ;
1474
1475 -- This table checks for duplicates of PartyId's of all involved in hirarchy
1476 type l_table_dupck is table of CHAR index by binary_integer;
1477 PartyId_Tbl_DupCk l_table_dupck;
1478
1479 BEGIN
1480
1481 x_msg_count := 0;
1482 x_return_status := FND_API.G_RET_STS_SUCCESS;
1483 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1484
1485 IF l_debug_mode = 'Y' THEN
1486 pa_debug.set_curr_function( p_function => 'set_workflow_attributes',
1487 p_debug_mode => l_debug_mode );
1488 END IF;
1489
1490 -- Check for business rules violations
1491
1492 IF l_debug_mode = 'Y' THEN
1493 pa_debug.g_err_stage:= 'Validating input parameters';
1494 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1495 pa_debug.g_err_stage:= 'p_item_type = '|| p_item_type;
1496 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
1497 pa_debug.g_err_stage:= 'p_process_name = '|| p_process_name;
1498 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
1499 pa_debug.g_err_stage:= 'p_ci_id = '|| p_ci_id;
1500 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
1501 END IF;
1502
1503 IF (p_item_type IS NULL) OR
1504 (p_process_name IS NULL) OR
1505 (p_ci_id IS NULL)
1506 THEN
1507
1508 PA_UTILS.ADD_MESSAGE
1509 (p_app_short_name => 'PA',
1510 p_msg_name => 'PA_INV_PARAM_PASSED');
1511 RAISE Invalid_Arg_Exc_CI;
1512 END IF;
1513
1514 -- Set the workflow attributes.
1515 wf_engine.SetItemAttrText( p_item_type
1516 ,p_item_key
1517 ,'ITEM_TYPE'
1518 ,p_item_type);
1519
1520 wf_engine.SetItemAttrText( p_item_type
1521 ,p_item_key
1522 ,'ITEM_KEY'
1523 ,p_item_key);
1524
1525 wf_engine.SetItemAttrNumber( p_item_type
1526 , p_item_key
1527 , 'CI_ID'
1528 , p_ci_id);
1529
1530 FOR rec IN get_ci_info LOOP
1531
1532 pa_utils.getprojinfo(rec.project_id, l_project_number, l_project_name);
1533 l_owner_id := rec.owner_id;
1534
1535 wf_engine.SetItemAttrNumber(p_item_type
1536 , p_item_key
1537 , 'PROJECT_ID'
1538 , rec.project_id);
1539
1540 wf_engine.SetItemAttrText( p_item_type
1541 , p_item_key
1542 , 'PROJECT_NAME'
1543 , l_project_name);
1544
1545 wf_engine.SetItemAttrText( p_item_type
1546 , p_item_key
1547 , 'PROJECT_NUMBER'
1548 , l_project_number);
1549
1550 wf_engine.SetItemAttrText( p_item_type
1551 , p_item_key
1552 , 'PROJECT'
1553 , l_project_name||'('||l_project_number||')');
1554
1555 wf_engine.SetItemAttrDate( p_item_type
1556 , p_item_key
1557 , 'DATE_REQUIRED'
1558 , rec.date_required);
1559
1560 wf_engine.SetItemAttrText( p_item_type
1561 , p_item_key
1562 , 'SUMMARY'
1563 , rec.summary);
1564
1565 wf_engine.SetItemAttrText( p_item_type
1566 , p_item_key
1567 , 'CONTROL_ITEM_TYPE'
1568 , rec.ci_type_name);
1569
1570 wf_engine.SetItemAttrText( p_item_type
1571 , p_item_key
1572 , 'CONTROL_ITEM_TYPE_SN'
1573 , rec.ci_type_sn);
1574
1575 wf_engine.SetItemAttrText( p_item_type
1576 , p_item_key
1577 , 'CONTROL_ITEM_NUMBER'
1578 , rec.ci_number);
1579
1580 wf_engine.SetItemAttrText( p_item_type
1581 , p_item_key
1582 , 'CONTROL_ITEM_CLASS'
1583 , rec.ci_type_class);
1584
1585
1586 if (rec.priority_code is not null) then
1587 open get_lookup_meaning('PA_TASK_PRIORITY_CODE',rec.priority_code);
1588 fetch get_lookup_meaning into l_priority_name;
1589 close get_lookup_meaning;
1590 else
1591 l_priority_name := null;
1592 end if;
1593 wf_engine.SetItemAttrText( p_item_type
1594 , p_item_key
1595 , 'PRIORITY'
1596 , l_priority_name);
1597
1598 wf_engine.SetItemAttrText( p_item_type
1599 , p_item_key
1600 , 'CLASSIFICATION'
1601 , rec.classification);
1602
1603 -- set project manager, organization name and customer
1604 OPEN get_project_info(rec.project_id);
1605 FETCH get_project_info INTO l_customer,l_project_manager, l_org;
1606
1607 wf_engine.SetItemAttrText( p_item_type
1608 , p_item_key
1609 , 'PROJECT_MANAGER'
1610 , l_project_manager);
1611
1612
1613 wf_engine.SetItemAttrText( p_item_type
1614 , p_item_key
1615 , 'ORGANIZATION'
1616 , l_org);
1617
1618 wf_engine.SetItemAttrText( p_item_type
1619 , p_item_key
1620 , 'CUSTOMER'
1621 , l_customer);
1622 CLOSE get_project_info;
1623
1624 --FP.M.IB1 - Set the url depending on the context. This is for the control item.
1625 --if (p_action_id is null) then
1626 --Bug 3974641. Replacing PA_CI_CI_REVIEW_LAYOUT AK region name with the xml file name CiCiReviewPG.
1627 l_url := 'JSP:/OA_HTML/OA.jsp?'||'page=/oracle/apps/pa/ci/webui/CiCiReviewPG' ||
1628 '&addBreadCrumb=N&paCiId='||p_ci_id || '&paProjectId=' || rec.project_id ||
1629 '&paCITypeClassCode=' || rec.ci_type_class_code|| '&paNotificationId=-&#NID-';
1630
1631 wf_engine.SetItemAttrText( p_item_type
1632 , p_item_key
1633 , 'CI_LINK'
1634 , l_url);
1635 --end if;
1636
1637 if (p_action_id is null) then
1638 open get_comment_text(p_ci_id);
1639 fetch get_comment_text into l_comment_text;
1640 close get_comment_text;
1641
1642
1643 wf_engine.SetItemAttrText( p_item_type
1644 , p_item_key
1645 , 'COMMENT'
1646 , l_comment_text);
1647 end if;
1648
1649 if (p_action_id is not null) then
1650 --Changes for the bug# 3691192 starts
1651 -- these are the new attributes added in the workflow PAWFCIAC to
1652 -- show Ci Status and Ci Owner in the notification
1653 open cur_ci_status_n_owner(p_ci_id);
1654 fetch cur_ci_status_n_owner into l_ci_status_code,
1655 l_ci_status_name,
1656 l_ci_owner_id,
1657 l_ci_owner_name;
1658 close cur_ci_status_n_owner; /* Added for Bug#4124900 */
1659
1660 wf_engine.SetItemAttrText( p_item_type
1661 , p_item_key
1662 , 'CI_STATUS_CODE'
1663 , l_ci_status_code);
1664
1665 wf_engine.SetItemAttrText( p_item_type
1666 , p_item_key
1667 , 'CI_STATUS'
1668 , l_ci_status_name);
1669
1670 wf_engine.SetItemAttrNumber( p_item_type
1671 , p_item_key
1672 , 'CI_OWNER_ID'
1673 , l_ci_owner_id);
1674
1675 wf_engine.SetItemAttrText( p_item_type
1676 , p_item_key
1677 , 'CI_OWNER_NAME'
1678 , l_ci_owner_name);
1679 --Changes for the bug# 3691192 ends
1680
1681 open cur_ci_action_info(p_action_id);
1682 fetch cur_ci_action_info into l_action_number,
1683 l_action_date_required,
1684 l_action_requestor,
1685 l_action_request,
1686 l_assign_party_id,
1687 l_create_party_id,
1688 l_action_status_code,
1689 l_action_closure_comment,
1690 l_record_version_number,
1691 l_action_type_code,
1692 l_action_type,
1693 l_last_updated,
1694 l_action_status_meaning,
1695 l_sign_off_req_flag,
1696 l_sign_off_req_meaning; --FP.M.IB1 Sanity
1697
1698 wf_engine.SetItemAttrNumber( p_item_type
1699 , p_item_key
1700 , 'ACTION_ID'
1701 , p_action_id);
1702
1703 wf_engine.SetItemAttrNumber( p_item_type
1704 , p_item_key
1705 , 'RECORD_VERSION_NUMBER'
1706 , l_record_version_number);
1707
1708 wf_engine.SetItemAttrNumber( p_item_type
1709 , p_item_key
1710 , 'ACTION_NUMBER'
1711 , l_action_number);
1712
1713 wf_engine.SetItemAttrText( p_item_type
1714 , p_item_key
1715 , 'ACTION_REQUEST'
1716 , l_action_request);
1717
1718
1719 wf_engine.SetItemAttrText( p_item_type
1720 , p_item_key
1721 , 'ACTION_REQUESTOR'
1722 , l_action_requestor);
1723
1724 wf_engine.SetItemAttrDate( p_item_type
1725 , p_item_key
1726 , 'ACTION_DATE_REQUIRED'
1727 , l_action_date_required);
1728
1729 wf_engine.SetItemAttrNumber( p_item_type
1730 , p_item_key
1731 , 'ASSIGN_PARTY_ID'
1732 , l_assign_party_id);
1733
1734
1735 --Changes for the bug# 3691192 starts
1736 -- Follwing are the new Item Attributes added in the workflow PAWFCIAC
1737 wf_engine.SetItemAttrText( p_item_type
1738 , p_item_key
1739 , 'ACTION_TYPE_CODE'
1740 , l_action_type_code);
1741
1742 wf_engine.SetItemAttrText( p_item_type
1743 , p_item_key
1744 , 'ACTION_TYPE'
1745 , l_action_type);
1746
1747
1748 wf_engine.SetItemAttrDate( p_item_type
1749 , p_item_key
1750 , 'SYSDATE'
1751 , sysdate);
1752
1753 wf_engine.SetItemAttrDate( p_item_type
1754 , p_item_key
1755 , 'LAST_UPDATE_DATE'
1756 , l_last_updated);
1757
1758 wf_engine.SetItemAttrText( p_item_type
1759 , p_item_key
1760 , 'ACTION_STATUS_CODE'
1761 , l_action_status_code);
1762
1763 wf_engine.SetItemAttrText( p_item_type
1764 , p_item_key
1765 , 'ACTION_STATUS'
1766 , l_action_status_meaning);
1767
1768 wf_engine.SetItemAttrText( p_item_type
1769 , p_item_key
1770 , 'SIGN_OFF_REQUESTED_FLAG'
1771 , l_sign_off_req_flag);
1772
1773 wf_engine.SetItemAttrText( p_item_type
1774 , p_item_key
1775 , 'SIGN_OFF_REQUESTED'
1776 , l_sign_off_req_meaning);
1777
1778
1779
1780 -- --Changes for the bug# 3691192
1781 -- In the Notification a Take Action Link is added that will take user to the View Action Page which will have
1782 -- a Take Action button if the user has access to take Actions.
1783
1784 l_act_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=PA_CI_VIEW_ACTION_LAYOUT&akRegionApplicationId=275' ||
1785 '&addBreadCrumb=N&paCiId='||p_ci_id || '&paProjectId=' || rec.project_id || '&paCiActionId='
1786 || p_action_id || '&paCITypeClassCode=' || rec.ci_type_class_code || '&paNotificationId=-&#NID-';
1787
1788 --FP.M.IB1. The related application link should point to take action if action assignment
1789 --or view action if action closure.
1790 if(l_action_status_code = 'CI_ACTION_OPEN') then
1791 /*l_act_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=PA_CI_TAKE_ACTION_LAYOUT&akRegionApplicationId=275' ||
1792 '&addBreadCrumb=N&paCiId='||p_ci_id || '&paProjectId=' || rec.project_id || '&paCiActionId='
1793 || p_action_id || '&paCITypeClassCode=' || rec.ci_type_class_code ||'&paNotificationId=-&#NID-'; */
1794 null;
1795 elsif(l_action_status_code = 'CI_ACTION_CLOSED') then
1796 /*l_act_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=PA_CI_VIEW_ACTION_LAYOUT&akRegionApplicationId=275' ||
1797 '&addBreadCrumb=N&paCiId='||p_ci_id || '&paProjectId=' || rec.project_id || '&paCiActionId='
1798 || p_action_id || '&paCITypeClassCode=' || rec.ci_type_class_code || '&paNotificationId=-&#NID-';*/
1799
1800 --Set the closure comment if it is action closure.
1801 wf_engine.SetItemAttrText( p_item_type
1802 , p_item_key
1803 , 'ACTION_CLOSURE_COMMENT'
1804 , l_action_closure_comment);
1805 end if;
1806
1807 -- New item attribute added in the workflow PAWFCIAC for the bug# 3691192
1808 if (l_act_url is not null) then
1809 wf_engine.SetItemAttrText( p_item_type
1810 , p_item_key
1811 , 'CI_ACT_LINK'
1812 , l_act_url);
1813 end if;
1814
1815 close cur_ci_action_info;
1816 end if;
1817 END LOOP;
1818
1819
1820
1821 --All the required attributes have been set. Now identify to whom the
1822 --notification should be sent to.
1823 if(p_action_id is null) then
1824 --in this case send the notification to the item owner.
1825 PartyId_Tbl(l_loop_var1) := l_owner_id;
1826 else
1827 if (l_action_status_code = 'CI_ACTION_OPEN') then
1828 -- This action has just been created. notification should be sent to assignee and store at position 1 in PLSQL table.
1829 PartyId_Tbl(l_loop_var1) := l_assign_party_id;
1830
1831 elsif(l_action_status_code = 'CI_ACTION_CLOSED' ) then
1832 -- This action has just been closed. Generate notification list for each of the party id.
1833
1834 --Bug 3608031.
1835 l_loop_var1 := 0;
1836
1837 for x in get_parties_in_hierarchy(p_action_id)
1838 LOOP
1839 IF NOT(PartyId_Tbl_DupCk.exists( x.party_id)) THEN
1840 PartyId_Tbl_DupCk(x.party_id) := 'Y';
1841
1842 --Bug 3608031
1843 l_loop_var1 := l_loop_var1 + 1;
1844
1845 PartyId_Tbl(l_loop_var1) := x.party_id;
1846 IF l_debug_mode = 'Y' THEN
1847 pa_debug.g_err_stage:= 'notification sent to :'||PartyId_Tbl(l_loop_var1);
1848 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1849 END IF;
1850 --Bug 3608031
1851 --l_loop_var1 := l_loop_var1 + 1;
1852 END IF;
1853 END LOOP;
1854 end if;
1855
1856 --Added for bug 4049901
1857 -- Altering the Display name of the notification recipient for CI Action Workflow
1858 open get_role_display_name(l_assign_party_id);
1859 fetch get_role_display_name into l_role_display_name;
1860 close get_role_display_name;
1861 -- End of code added for 4049901
1862
1863 end if;
1864
1865
1866 l_role := 'NOTFY_' ||p_item_type || p_item_key;
1867
1868 --Added if condition for bug 4049901
1869 -- No need to alter the role display name for Control Item Workflow
1870 -- Changing the Role Display name only for CI Action Workflow
1871 if l_role_display_name is null then
1872 l_role_display_name := l_role;
1873 end if;
1874
1875 WF_DIRECTORY.CreateAdHocRole( role_name => l_role
1876 , role_display_name => l_role_display_name --Modified for bug 4049901
1877 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962401
1878
1879 --for ctr in 1..(l_loop_var1-1) Bug 3608031
1880 for ctr in 1..l_loop_var1
1881 loop
1882
1883 for v_party in get_notification_list(PartyId_Tbl(ctr))
1884 loop
1885 if (l_role_users is not null) then
1886 l_role_users := l_role_users || ',';
1887 end if;
1888
1889 -- Create adhoc users
1890
1891 wf_directory.getroleinfo(v_party.user_name,
1892 display_name,
1893 email_address,
1894 notification_preference,
1895 language,
1896 territory);
1897 if display_name is null THEN
1898
1899 WF_DIRECTORY.CreateAdHocUser( name => v_party.user_name
1900 , display_name => v_party.party_name
1901 , EMAIL_ADDRESS => v_party.email_address);
1902 END IF;
1903 l_role_users := l_role_users || v_party.user_name;
1904
1905 end loop;
1906 end loop;
1907
1908 IF (l_role_users is NOT NULL) THEN
1909 WF_DIRECTORY.AddUsersToAdHocRole( l_role, l_role_users);
1910
1911 wf_engine.SetItemAttrText( p_item_type
1912 , p_item_key
1913 , 'CI_NOTIFICATION_PARTY'
1914 , l_role);
1915 END IF;
1916
1917 IF l_debug_mode = 'Y' THEN
1918 pa_debug.g_err_stage:= 'Exiting set_workflow_attributes';
1919 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
1920 pa_debug.reset_curr_function;
1921 END IF;
1922 EXCEPTION
1923
1924 WHEN Invalid_Arg_Exc_CI THEN
1925 x_return_status := FND_API.G_RET_STS_ERROR;
1926 l_msg_count := FND_MSG_PUB.count_msg;
1927
1928 IF get_project_info%ISOPEN THEN
1929 CLOSE get_project_info;
1930 END IF;
1931
1932 IF get_ci_info%ISOPEN THEN
1933 CLOSE get_ci_info;
1934 END IF;
1935
1936 IF cur_ci_action_info%ISOPEN THEN
1937 CLOSE cur_ci_action_info;
1938 END IF;
1939
1940 IF get_notification_list%ISOPEN THEN
1941 CLOSE get_notification_list;
1942 END IF;
1943
1944 IF get_lookup_meaning%ISOPEN THEN
1945 CLOSE get_lookup_meaning;
1946 END IF;
1947
1948 IF get_comment_text%ISOPEN THEN
1949 CLOSE get_comment_text;
1950 END IF;
1951
1952 IF l_msg_count = 1 and x_msg_data IS NULL THEN
1953 PA_INTERFACE_UTILS_PUB.get_messages
1954 (p_encoded => FND_API.G_TRUE
1955 ,p_msg_index => 1
1956 ,p_msg_count => l_msg_count
1957 ,p_msg_data => l_msg_data
1958 ,p_data => l_data
1959 ,p_msg_index_out => l_msg_index_out);
1960 x_msg_data := l_data;
1961 x_msg_count := l_msg_count;
1962 ELSE
1963 x_msg_count := l_msg_count;
1964 END IF;
1965 IF l_debug_mode = 'Y' THEN
1966 pa_debug.reset_curr_function;
1967 END IF;
1968
1969 RETURN;
1970
1971 WHEN others THEN
1972
1973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1974 x_msg_count := 1;
1975 x_msg_data := SQLERRM;
1976
1977 IF get_project_info%ISOPEN THEN
1978 CLOSE get_project_info;
1979 END IF;
1980
1981 IF get_ci_info%ISOPEN THEN
1982 CLOSE get_ci_info;
1983 END IF;
1984
1985 IF cur_ci_action_info%ISOPEN THEN
1986 CLOSE cur_ci_action_info;
1987 END IF;
1988
1989 IF get_notification_list%ISOPEN THEN
1990 CLOSE get_notification_list;
1991 END IF;
1992
1993 IF get_lookup_meaning%ISOPEN THEN
1994 CLOSE get_lookup_meaning;
1995 END IF;
1996
1997 IF get_comment_text%ISOPEN THEN
1998 CLOSE get_comment_text;
1999 END IF;
2000
2001 FND_MSG_PUB.add_exc_msg
2002 ( p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW'
2003 ,p_procedure_name => 'set_workflow_attributes'
2004 ,p_error_text => x_msg_data);
2005
2006 IF l_debug_mode = 'Y' THEN
2007 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
2008 pa_debug.write(l_module_name,pa_debug.g_err_stage,
2009 l_debug_level5);
2010 pa_debug.reset_curr_function;
2011 END IF;
2012 RAISE;
2013 END set_workflow_attributes;
2014
2015 PROCEDURE CLOSE_CI_ACTION (
2016 itemtype IN VARCHAR2
2017 ,itemkey IN VARCHAR2
2018 ,actid IN NUMBER
2019 ,funcmode IN VARCHAR2
2020 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2021 IS
2022
2023 Cursor check_record_changed(p_ci_action_id number,p_record_version_number number) IS
2024 select rowid
2025 from pa_ci_actions
2026 where ci_action_id = p_ci_action_id
2027 and record_version_number = p_record_version_number
2028 for update;
2029
2030 Cursor ci_action(p_ci_action_id number) IS
2031 select ci_id, type_code, assigned_to, date_required,
2032 sign_off_required_flag, source_ci_action_id, created_by, creation_date
2033 from pa_ci_actions
2034 where ci_action_id = p_ci_action_id;
2035
2036 l_party_id number;
2037 l_created_by number;
2038 l_creation_date date;
2039 l_ci_id number;
2040 l_type_code varchar2(30);
2041 l_assigned_to number;
2042 l_date_required date;
2043 l_sign_off_required_flag varchar2(1);
2044 l_source_ci_action_id number;
2045 l_error_msg_code varchar2(30);
2046 l_rowid rowid;
2047 l_ci_comment_id number;
2048 l_ci_record_version_number number;
2049 l_num_of_actions number;
2050 l_comment_text varchar2(32767);
2051 l_ci_action_id number;
2052 l_record_version_number number;
2053 l_return_status VARCHAR2(1) :=fnd_api.g_ret_sts_success;
2054 l_msg_count number;
2055 l_msg_data varchar2(2000);
2056 l_user_sign_off VARCHAR2(1):='N';
2057 l_assign_party_id NUMBER; --added for bug# 3877985
2058 l_fnd_usr_id NUMBER; --added for bug# 3877985
2059
2060
2061
2062 --bug 3297238
2063 l_item_key pa_wf_processes.item_key%TYPE;
2064
2065 Cursor getRecordVersionNumber IS
2066 select record_version_number
2067 from pa_control_items
2068 where ci_id = l_ci_id;
2069
2070 -- Added the cursor for bug# 3877985 Issue# 2
2071 CURSOR get_fnd_usr( p_party_id NUMBER) IS
2072 select user_id
2073 from fnd_user
2074 where person_party_id = p_party_id
2075 and sysdate between trunc(start_date) and nvl(trunc(end_date),sysdate)
2076 and rownum = 1;
2077
2078 BEGIN
2079 -- Initialize the Error Stack
2080 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_WORKFLOW.CLOSE_CI_ACTION');
2081
2082
2083 l_ci_id := wf_engine.GetItemAttrNumber
2084 ( itemtype => itemtype,
2085 itemkey => itemkey,
2086 aname => 'CI_ID');
2087
2088 l_ci_action_id := wf_engine.GetItemAttrNumber
2089 ( itemtype => itemtype,
2090 itemkey => itemkey,
2091 aname => 'ACTION_ID');
2092
2093 l_record_version_number := wf_engine.GetItemAttrNumber
2094 ( itemtype => itemtype,
2095 itemkey => itemkey,
2096 aname => 'RECORD_VERSION_NUMBER');
2097
2098 l_comment_text := wf_engine.GetItemAttrText
2099 ( itemtype => itemtype,
2100 itemkey => itemkey,
2101 aname => 'COMMENT');
2102
2103 l_comment_text := wf_engine.GetItemAttrText
2104 ( itemtype => itemtype,
2105 itemkey => itemkey,
2106 aname => 'COMMENT');
2107 --Added for Bug# 3802238
2108 l_user_sign_off := wf_engine.GetItemAttrText
2109 ( itemtype => itemtype,
2110 itemkey => itemkey,
2111 aname => 'SIGN_OFF');
2112
2113 l_assign_party_id := wf_engine.GetItemAttrNumber
2114 ( itemtype => itemtype,
2115 itemkey => itemkey,
2116 aname => 'ASSIGN_PARTY_ID');
2117
2118 SAVEPOINT CLOSE_CI_ACTION;
2119
2120 -- Validate the Input Values
2121 OPEN ci_action(l_ci_action_id);
2122 FETCH ci_action INTO l_ci_id, l_type_code, l_assigned_to,
2123 l_date_required, l_sign_off_required_flag, l_source_ci_action_id,
2124 l_created_by, l_creation_date;
2125 IF ci_action%NOTFOUND THEN
2126 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2127 ,p_msg_name => 'PA_NO_ACTION_FOUND');
2128 resultout := wf_engine.eng_completed||':'||'F';
2129 CLOSE ci_action;
2130 return;
2131 END IF;
2132
2133 --LOCK the ROW
2134 OPEN check_record_changed(l_ci_action_id,l_record_version_number);
2135 FETCH check_record_changed INTO l_rowid;
2136 IF check_record_changed%NOTFOUND THEN
2137 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2138 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
2139 resultout := wf_engine.eng_completed||':'||'F';
2140 CLOSE check_record_changed;
2141 return;
2142 END IF;
2143
2144 if (check_record_changed%ISOPEN) then
2145 CLOSE check_record_changed;
2146 end if;
2147
2148 --Added for bug# 3877985 Issue# 2
2149 if (l_sign_off_required_flag = 'N') then
2150 l_user_sign_off := 'N';
2151 end if;
2152
2153 --Added for bug# 3877985.
2154 --Fetching the fnd user_id for the action asignee to update who columns
2155 OPEN get_fnd_usr( l_assign_party_id);
2156 FETCH get_fnd_usr INTO l_fnd_usr_id;
2157 CLOSE get_fnd_usr;
2158 PA_CI_ACTIONS_PKG.UPDATE_ROW(
2159 P_CI_ACTION_ID => l_ci_action_id,
2160 P_CI_ID => l_ci_id,
2161 P_STATUS_CODE => 'CI_ACTION_CLOSED',
2162 P_TYPE_CODE => l_type_code,
2163 P_ASSIGNED_TO => l_assigned_to,
2164 P_DATE_REQUIRED => l_date_required,
2165 P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
2166 P_DATE_CLOSED => sysdate,
2167 P_SIGN_OFF_FLAG => l_user_sign_off,
2168 P_SOURCE_CI_ACTION_ID => l_source_ci_action_id,
2169 P_LAST_UPDATED_BY => l_fnd_usr_id, --Modified for bug# 3877985
2170 P_CREATED_BY => l_created_by,
2171 P_CREATION_DATE => l_creation_date,
2172 P_LAST_UPDATE_DATE => sysdate,
2173 P_LAST_UPDATE_LOGIN => l_fnd_usr_id, --Modified for bug# 3877985
2174 P_RECORD_VERSION_NUMBER => l_record_version_number);
2175
2176 if (l_comment_text IS NULL) THEN
2177 l_comment_text := ' ';
2178 end if;
2179 PA_CI_ACTIONS_PVT.ADD_CI_COMMENT(
2180 p_api_version => 1.0,
2181 p_init_msg_list => fnd_api.g_true,
2182 p_commit => FND_API.g_false,
2183 p_validate_only => FND_API.g_false,
2184 p_max_msg_count => FND_API.g_miss_num,
2185 p_ci_comment_id => l_ci_comment_id,
2186 p_ci_id => l_ci_id,
2187 p_type_code => 'CLOSURE',
2188 p_comment_text => l_comment_text,
2189 p_ci_action_id => l_ci_action_id,
2190 p_created_by => l_fnd_usr_id, --Added for bug# 3877985
2191 p_last_updated_by => l_fnd_usr_id, --Added for bug# 3877985
2192 p_last_update_login => l_fnd_usr_id, --Added for bug# 3877985
2193 x_return_status => l_return_status,
2194 x_msg_count => l_msg_count,
2195 x_msg_data => l_msg_data
2196 );
2197
2198 OPEN getRecordVersionNumber;
2199 FETCH getRecordVersionNumber into l_ci_record_version_number;
2200 CLOSE getRecordVersionNumber;
2201
2202 If (l_return_status = fnd_api.g_ret_sts_success) then
2203
2204 PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
2205 p_api_version => 1.0,
2206 p_init_msg_list => fnd_api.g_true,
2207 p_commit => FND_API.g_false,
2208 p_validate_only => FND_API.g_true,
2209 p_max_msg_count => FND_API.g_miss_num,
2210 p_ci_id =>l_CI_ID,
2211 p_num_of_actions => -1,
2212 p_record_version_number =>l_ci_record_version_number,
2213 x_num_of_actions => l_num_of_actions,
2214 x_return_status => l_return_status,
2215 x_msg_count => l_msg_count,
2216 x_msg_data => l_msg_data,
2217 p_last_updated_by => l_fnd_usr_id, --Added for bug# 3877985
2218 p_last_update_login => l_fnd_usr_id --Added for bug# 3877985
2219 );
2220 end if;
2221
2222
2223 -- Commit the changes if requested
2224 if l_return_status = fnd_api.g_ret_sts_success then
2225 commit;
2226 resultout := wf_engine.eng_completed||':'||'T';
2227 end if;
2228 commit;
2229
2230
2231 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
2232 ROLLBACK TO CLOSE_CI_ACTION;
2233 resultout := wf_engine.eng_completed||':'||'F';
2234 WHEN OTHERS THEN
2235 ROLLBACK TO CLOSE_CI_ACTION;
2236 resultout := wf_engine.eng_completed||':'||'F';
2237 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW',
2238 p_procedure_name => 'CLOSE_CI_ACTIONS',
2239 p_error_text => SUBSTRB(SQLERRM,1,240));
2240 RAISE;
2241 END CLOSE_CI_ACTION;
2242
2243 PROCEDURE KEEP_OPEN (
2244 itemtype IN VARCHAR2
2245 ,itemkey IN VARCHAR2
2246 ,actid IN NUMBER
2247 ,funcmode IN VARCHAR2
2248 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2249 IS
2250
2251 Cursor ci_action(p_ci_action_id number) IS
2252 select ci_id, type_code, assigned_to, date_required,
2253 sign_off_required_flag, source_ci_action_id, created_by, creation_date
2254 from pa_ci_actions
2255 where ci_action_id = p_ci_action_id;
2256
2257 -- Added the cursor for bug# 3877985 Issue# 2
2258 CURSOR get_fnd_usr( p_party_id NUMBER) IS
2259 select user_id
2260 from fnd_user
2261 where person_party_id = p_party_id
2262 and sysdate between trunc(start_date) and nvl(trunc(end_date),sysdate)
2263 and rownum = 1;
2264
2265 -- Added the cursor for bug# 4527911
2266 cursor is_comment_inserted_cur(p_ci_action_id NUMBER) IS
2267 select 1
2268 from pa_ci_comments
2269 where ci_action_id = p_ci_action_id
2270 and type_code='UNSOLICITED';
2271
2272 l_party_id number;
2273 l_created_by number;
2274 l_creation_date date;
2275 l_ci_id number;
2276 l_type_code varchar2(30);
2277 l_assigned_to number;
2278 l_date_required date;
2279 l_sign_off_required_flag varchar2(1);
2280 l_source_ci_action_id number;
2281 l_error_msg_code varchar2(30);
2282 l_rowid rowid;
2283 l_ci_comment_id number;
2284 l_ci_record_version_number number;
2285 l_num_of_actions number;
2286 l_comment_text varchar2(32767);
2287 l_ci_action_id number;
2288 l_record_version_number number;
2289 l_return_status VARCHAR2(1) :=fnd_api.g_ret_sts_success;
2290 l_msg_count number;
2291 l_msg_data varchar2(2000);
2292 l_assign_party_id NUMBER; --added for bug# 3877985
2293 l_fnd_usr_id NUMBER; --added for bug# 3877985
2294 l_num_var NUMBER;
2295
2296
2297
2298 --bug 3297238
2299 l_item_key pa_wf_processes.item_key%TYPE;
2300
2301 Cursor getRecordVersionNumber IS
2302 select record_version_number
2303 from pa_control_items
2304 where ci_id = l_ci_id;
2305 BEGIN
2306 -- Initialize the Error Stack
2307 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_WORKFLOW.KEEP_OPEN');
2308
2309
2310
2311 l_ci_id := wf_engine.GetItemAttrNumber
2312 ( itemtype => itemtype,
2313 itemkey => itemkey,
2314 aname => 'CI_ID');
2315
2316 l_ci_action_id := wf_engine.GetItemAttrNumber
2317 ( itemtype => itemtype,
2318 itemkey => itemkey,
2319 aname => 'ACTION_ID');
2320
2321 l_record_version_number := wf_engine.GetItemAttrNumber
2322 ( itemtype => itemtype,
2323 itemkey => itemkey,
2324 aname => 'RECORD_VERSION_NUMBER');
2325
2326 l_comment_text := wf_engine.GetItemAttrText
2327 ( itemtype => itemtype,
2328 itemkey => itemkey,
2329 aname => 'COMMENT');
2330
2331 l_assign_party_id := wf_engine.GetItemAttrNumber
2332 ( itemtype => itemtype,
2333 itemkey => itemkey,
2334 aname => 'ASSIGN_PARTY_ID');
2335
2336
2337 SAVEPOINT KEEP_OPEN;
2338
2339 -- Validate the Input Values
2340 OPEN ci_action(l_ci_action_id);
2341 FETCH ci_action INTO l_ci_id, l_type_code, l_assigned_to,
2342 l_date_required, l_sign_off_required_flag, l_source_ci_action_id,
2343 l_created_by, l_creation_date;
2344 IF ci_action%NOTFOUND THEN
2345 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2346 ,p_msg_name => 'PA_NO_ACTION_FOUND');
2347 resultout := wf_engine.eng_completed||':'||'F';
2348 CLOSE ci_action;
2349 return;
2350 END IF;
2351
2352
2353 if (l_comment_text IS NULL) THEN
2354 l_comment_text := ' ';
2355 end if;
2356
2357 --Fetching the fnd user_id for the action asignee to update who columns
2358 OPEN get_fnd_usr( l_assign_party_id);
2359 FETCH get_fnd_usr INTO l_fnd_usr_id;
2360 CLOSE get_fnd_usr;
2361
2362 /* Code added for bug# 4527911
2363 There are two ways in which this api can be called:
2364 1) When user takes keep open action from the workflow notification.
2365 2) When user takes keep open action from take action page. When the notification result is set to
2366 KEEP_OPEN from backend then again this api is called by the workflow system.
2367 In case 2 the user comment will already be inserted in PA_CI_COMMENTS from the application, so no need to insert it again.
2368 IF there is already a line in pa_ci_comments for the action with type_code UNSOLICITED it means that the comment is already
2369 inserted.*/
2370
2371
2372 OPEN is_comment_inserted_cur(l_ci_action_id);
2373 FETCH is_comment_inserted_cur into l_num_var;
2374 IF is_comment_inserted_cur%NOTFOUND THEN
2375 CLOSE is_comment_inserted_cur;
2376 PA_CI_ACTIONS_PVT.ADD_CI_COMMENT(
2377 p_api_version => 1.0,
2378 p_init_msg_list => fnd_api.g_true,
2379 p_commit => FND_API.g_false,
2380 p_validate_only => FND_API.g_false,
2381 p_max_msg_count => FND_API.g_miss_num,
2382 p_ci_comment_id => l_ci_comment_id,
2383 p_ci_id => l_ci_id,
2384 p_type_code => 'UNSOLICITED',
2385 p_comment_text => l_comment_text,
2386 p_ci_action_id => l_ci_action_id,
2387 p_created_by => l_fnd_usr_id, --Added for bug# 3877985
2388 p_last_updated_by => l_fnd_usr_id, --Added for bug# 3877985
2389 p_last_update_login => l_fnd_usr_id, --Added for bug# 3877985
2390 x_return_status => l_return_status,
2391 x_msg_count => l_msg_count,
2392 x_msg_data => l_msg_data
2393 );
2394
2395
2396 if l_return_status = fnd_api.g_ret_sts_success then
2397 commit;
2398 resultout := wf_engine.eng_completed||':'||'T';
2399 end if;
2400 commit;
2401 ELSE
2402 CLOSE is_comment_inserted_cur;
2403 END IF;
2404
2405 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
2406 ROLLBACK TO KEEP_OPEN;
2407 resultout := wf_engine.eng_completed||':'||'F';
2408 WHEN OTHERS THEN
2409 ROLLBACK TO KEEP_OPEN;
2410 resultout := wf_engine.eng_completed||':'||'F';
2411 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_WORKFLOW',
2412 p_procedure_name => 'KEEP_OPEN',
2413 p_error_text => SUBSTRB(SQLERRM,1,240));
2414 RAISE;
2415 END KEEP_OPEN;
2416
2417 /***********************************************************************************
2418 Modified this procedure for Bug# 4527911
2419 This procedure is called when an action is cancelled from Action list Page.
2420 A new parameter p_ci_action_id is being added in the procedure and the parameters
2421 p_item_type,p_item_key and p_nid has been removed as the itme_type, item_key and
2422 notification_id is being derived in the procedure itself using the parameter
2423 p_ci_action_id
2424 ***********************************************************************************/
2425 PROCEDURE cancel_notif_and_abort_wf(
2426 p_ci_action_id IN NUMBER,
2427 x_msg_count OUT NOCOPY NUMBER ,
2428 x_msg_data OUT NOCOPY VARCHAR2 ,
2429 x_return_status OUT NOCOPY VARCHAR2 )
2430
2431 IS
2432 cursor get_open_notification(p_action_id VARCHAR2)
2433 IS
2434 select wfi.notification_id,
2435 wfi.item_type,
2436 wfi.item_key
2437 from pa_wf_processes pwp,
2438 wf_item_activity_statuses_v wfi
2439 where pwp.entity_key2=p_action_id
2440 and pwp.item_type='PAWFCIAC'
2441 and wfi.item_type = pwp.item_type
2442 and wfi.item_key = pwp.item_key
2443 and wfi.activity_type_code='NOTICE'
2444 and wfi.activity_status_code='NOTIFIED';
2445
2446 BEGIN
2447 x_return_status := FND_API.G_RET_STS_SUCCESS;
2448 for nid in get_open_notification(to_char(p_ci_action_id))
2449 loop
2450 WF_NOTIFICATION.CANCEL
2451 ( nid => to_number(nid.notification_id),
2452 cancel_comment => null
2453 );
2454
2455 Cancel_Workflow
2456 ( p_Item_type => nid.item_type,
2457 p_Item_key => nid.item_key,
2458 x_msg_count => x_msg_count,
2459 x_msg_data => x_msg_data,
2460 x_return_status => x_return_status
2461 );
2462 end loop;
2463
2464
2465 EXCEPTION
2466
2467 WHEN OTHERS THEN
2468 x_msg_count := 1;
2469 x_msg_data := substr(SQLERRM,1,2000);
2470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471 END cancel_notif_and_abort_wf;
2472
2473
2474
2475 /***********************************************************************************
2476 Added this procedure for Bug# 4527911
2477 This procedure is called when any action is taken from the Take Action Page.
2478 Here as per the action taken on take action page the Open action notification
2479 is closed with appropriate result.
2480 1) If on take action page the Action is Closed then the notification is also
2481 closed with result 'BBB_CLOSE'.If user provides a comment or sign-off then
2482 that also is updated in the notification.
2483 2) If on take action page the Action is kept open then the notification is
2484 closed with result 'AAA_KEEP_OPEN'.If user provides a comment or sign-off
2485 then that also is updated in the notification.
2486 3) If on take action page the Action is reassigned then the notification is
2487 closed with result 'BBB_CLOSE'
2488 ***********************************************************************************/
2489 PROCEDURE close_notification(
2490 p_item_type in VARCHAR2,
2491 p_item_key in VARCHAR2,
2492 p_nid in NUMBER,
2493 p_action in VARCHAR2,
2494 p_sign_off_flag in VARCHAR2,
2495 p_response in VARCHAR2,
2496 x_msg_count OUT NOCOPY NUMBER ,
2497 x_msg_data OUT NOCOPY VARCHAR2 ,
2498 x_return_status OUT NOCOPY VARCHAR2 )
2499
2500 IS
2501 l_sign_off_requested VARCHAR2(1);
2502
2503 BEGIN
2504
2505 x_return_status := FND_API.G_RET_STS_SUCCESS;
2506
2507 l_sign_off_requested:= wf_engine.GetItemAttrText
2508 ( itemtype => p_item_type,
2509 itemkey => p_item_key,
2510 aname => 'SIGN_OFF_REQUESTED_FLAG');
2511
2512
2513 if l_sign_off_requested = 'Y' then
2514 wf_notification.setAttrText(p_nid, 'SIGN_OFF',p_sign_off_flag);
2515 end if;
2516
2517 wf_notification.setAttrText(p_nid, 'COMMENT', p_response);
2518
2519 if p_action in ('C','R') then
2520 wf_notification.setAttrText(p_nid, 'RESULT', 'BBB_CLOSE');
2521 else
2522 wf_notification.setAttrText(p_nid, 'RESULT', 'AAA_KEEP_OPEN');
2523 end if;
2524
2525 wf_notification.respond(p_nid, null, fnd_global.user_name);
2526
2527
2528 EXCEPTION
2529
2530 WHEN OTHERS THEN
2531 x_msg_count := 1;
2532 x_msg_data := substr(SQLERRM,1,2000);
2533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2534
2535 END close_notification;
2536
2537
2538 END pa_control_items_workflow;
2539