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