[Home] [Help]
PACKAGE BODY: APPS.PA_WORKPLAN_WORKFLOW
Source
1 package body PA_WORKPLAN_WORKFLOW as
2 /*$Header: PAXSTWWB.pls 120.9 2011/04/15 06:52:38 smadhava ship $*/
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_workplan_workflow';
5
6 procedure START_WORKFLOW
7 (
8 p_item_type IN VARCHAR2
9 ,p_process_name IN VARCHAR2
10 ,p_structure_version_id IN NUMBER
11 ,p_responsibility_id IN NUMBER
12 ,p_user_id IN NUMBER
13 ,x_item_key OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
14 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
15 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
16 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 )
18 IS
19 l_item_key NUMBER;
20 BEGIN
21 x_return_status := FND_API.g_ret_sts_success;
22
23 select pa_workflow_itemkey_s.nextval
24 into l_item_key
25 from dual;
26
27 x_item_key := to_char(l_item_key);
28
29 wf_engine.createProcess(p_item_type,
30 x_item_key,
31 p_process_name);
32
33 pa_workplan_workflow_client.start_workflow(p_item_type,
34 x_item_key,
35 p_process_name,
36 p_structure_version_id,
37 p_responsibility_id,
38 p_user_id,
39 x_msg_count,
40 x_msg_data,
41 x_return_status
42 );
43
44
45 IF x_return_status = FND_API.g_ret_sts_success THEN
46 WF_ENGINE.startProcess(p_item_type,
47 x_item_key);
48 END IF;
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 x_msg_count :=1;
53 x_msg_data:= substrb(SQLERRM, 1, 2000); -- 4537865 : Replaced substr with substrb
54 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
55 x_item_key := NULL ; -- 4537865
56 END START_WORKFLOW;
57
58
59 procedure cancel_workflow
60 (
61 p_item_type IN VARCHAR2
62 ,p_item_key IN VARCHAR2
63 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
64 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
65 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
66 )
67 IS
68
69 BEGIN
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 WF_ENGINE.ABORTPROCESS(p_item_type,
73 p_item_key);
74 EXCEPTION
75 WHEN OTHERS THEN
76 x_msg_count :=1;
77 x_msg_data:= substrb(SQLERRM, 1, 2000); -- 4537865 : Replaced substr with substrb
78 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
79 END CANCEL_WORKFLOW;
80
81
82 procedure check_workplan_status
83 (
84 itemtype IN VARCHAR2
85 ,itemkey IN VARCHAR2
86 ,actid IN NUMBER
87 ,funcmode IN VARCHAR2
88 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
89 )
90 IS
91 l_project_id NUMBER;
92 l_structure_version_id NUMBER;
93 l_status VARCHAR2(30);
94 l_ret VARCHAR2(240);
95
96 cursor getWorkplanStatus IS
97 select STATUS_CODE
98 from pa_proj_elem_ver_structure
99 where project_id = l_project_id
100 and element_version_id = l_structure_version_id;
101
102 cursor get_working_ver(c_structure_version_id NUMBER) IS
103 select a.element_version_id
104 from pa_proj_elem_ver_structure a,
105 pa_proj_element_versions b
106 where b.project_id = a.project_id
107 and b.proj_element_id = a.proj_element_id
108 and a.status_code = 'STRUCTURE_WORKING'
109 and b.element_version_id = c_structure_version_id;
110 l_working_ver_id NUMBER;
111
112 BEGIN
113 l_project_id := wf_engine.GetItemAttrNumber(
114 itemtype => itemtype,
115 itemkey => itemkey,
116 aname => 'PROJECT_ID');
117
118 l_structure_version_id := wf_engine.GetItemAttrNumber(
119 itemtype => itemtype,
120 itemkey => itemkey,
121 aname => 'STRUCTURE_VER_ID');
122
123 OPEN getWorkplanStatus;
124 FETCH getWorkplanStatus into l_status;
125 CLOSE getWorkplanStatus;
126
127 IF l_status = 'STRUCTURE_APPROVED' THEN
128 resultout := wf_engine.eng_completed||':'||'APPROVED';
129 ELSIF l_status = 'STRUCTURE_PUBLISHED' THEN
130 OPEN get_working_ver(l_structure_version_id);
131 FETCH get_working_ver into l_working_ver_id;
132 CLOSE get_working_ver;
133
134 wf_engine.SetItemAttrText(itemtype, itemkey,
135 'STRUCTURE_VER_ID_T',to_char(l_working_ver_id));
136 resultout := wf_engine.eng_completed||':'||'PUBLISHED';
137 ELSIF l_status = 'STRUCTURE_REJECTED' THEN
138 resultout := wf_engine.eng_completed||':'||'REJECTED';
139 END IF;
140
141 pa_workplan_workflow_client.set_notification_party
142 (
143 itemtype,
144 itemkey,
145 l_status,
146 actid,
147 funcmode,
148 l_ret
149 );
150 -- 4537865 : Included EXCEPTION BLOCK
151 EXCEPTION
152 WHEN OTHERS THEN
153 resultout := wf_engine.eng_null ; -- This is a Non existent value : 4537865
154 -- Included as per discussion with Rajnish : 4537865
155 Wf_Core.Context('pa_workplan_workflow','check_workplan_status',itemtype,itemkey,to_char(actid),funcmode);
156 RAISE ;
157 END check_workplan_status;
158
159 procedure change_status_working
160 (
161 itemtype IN VARCHAR2
162 ,itemkey IN VARCHAR2
163 ,actid IN NUMBER
164 ,funcmode IN VARCHAR2
165 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
166 )
167 IS
168 l_project_id NUMBER;
169 l_structure_version_id NUMBER;
170 l_record_version_num NUMBER;
171
172 BEGIN
173 --nofity party already set in start_workflow
174 --change status to working
175
176 l_project_id := wf_engine.GetItemAttrNumber(
177 itemtype => itemtype,
178 itemkey => itemkey,
179 aname => 'PROJECT_ID');
180
181 l_structure_version_id := wf_engine.getItemAttrNumber(
182 itemtype => itemtype
183 ,itemkey => itemkey
184 ,aname => 'STRUCTURE_VER_ID');
185
186 l_record_version_num := wf_engine.getItemAttrNumber(
187 itemtype => itemtype
188 ,itemkey => itemkey
189 ,aname => 'RECORD_VERSION_NUMBER');
190
191 update pa_proj_elem_ver_structure
192 set status_code = 'STRUCTURE_WORKING',
193 record_version_number = l_record_version_num + 1
194 where project_id = l_project_id
195 and element_version_id = l_structure_version_id;
196 -- 4537865 : Included EXCEPTION BLOCK
197 EXCEPTION
198 WHEN OTHERS THEN
199 -- I havent reset value of resultout as this param is not assigned value anywhere in this API.
200 -- The Workflow function (Change Status to Working) doesnt expect any result type
201
202 -- Included as per discussion with Rajnish : 4537865
203 Wf_Core.Context('pa_workplan_workflow','change_status_working',itemtype,itemkey,to_char(actid),funcmode);
204 RAISE;
205 END change_status_working;
206
207 procedure change_status_rejected
208 (
209 itemtype IN VARCHAR2
210 ,itemkey IN VARCHAR2
211 ,actid IN NUMBER
212 ,funcmode IN VARCHAR2
213 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
214 )
215 IS
216 l_project_id NUMBER;
217 l_structure_version_id NUMBER;
218 l_record_version_num NUMBER;
219
220 l_return_status VARCHAR2(1);
221 l_msg_count NUMBER;
222 l_msg_data VARCHAR2(300);
223 BEGIN
224 l_project_id := wf_engine.GetItemAttrNumber(
225 itemtype => itemtype,
226 itemkey => itemkey,
227 aname => 'PROJECT_ID');
228
229 l_structure_version_id := wf_engine.getItemAttrNumber(
230 itemtype => itemtype
231 ,itemkey => itemkey
232 ,aname => 'STRUCTURE_VER_ID');
233
234 l_record_version_num := wf_engine.getItemAttrNumber(
235 itemtype => itemtype
236 ,itemkey => itemkey
237 ,aname => 'RECORD_VERSION_NUMBER');
238
239
240 --call PA_PROJECT_STRUCTURE_PVT1.change_workplan_status
241 PA_PROJECT_STRUCTURE_PVT1.change_workplan_status
242 (
243 p_project_id => l_project_id
244 ,p_structure_version_id => l_structure_version_id
245 ,p_status_code => 'STRUCTURE_REJECTED'
246 ,p_record_version_number => l_record_version_num
247 ,x_return_status => l_return_status
248 ,x_msg_count => l_msg_count
249 ,x_msg_data => l_msg_data
250 );
251 -- 4537865 : Included EXCEPTION BLOCK
252 EXCEPTION
253 WHEN OTHERS THEN
254 -- I havent reset value of resultout as this param is not assigned value anywhere in this API.
255 -- The Workflow function (Change Status to Rejected) doesnt expect any result type
256
257 -- Included as per discussion with Rajnish : 4537865
258 Wf_Core.Context('pa_workplan_workflow','change_status_rejected',itemtype,itemkey,to_char(actid),funcmode);
259 RAISE;
260 END change_status_rejected;
261
262 procedure change_status_approved
263 (
264 itemtype IN VARCHAR2
265 ,itemkey IN VARCHAR2
266 ,actid IN NUMBER
267 ,funcmode IN VARCHAR2
268 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
269 )
270 IS
271 l_project_id NUMBER;
272 l_structure_version_id NUMBER;
273 l_structure_version_name VARCHAR2(240);
274 l_structure_version_desc VARCHAR2(250);
275 l_responsibility_id NUMBER;
276 l_user_id NUMBER;
277 l_record_version_num NUMBER;
278 l_dummy VARCHAR2(1);
279 l_auto_publish_flag VARCHAR2(1);
280 l_published_struc_ver_id NUMBER;
281 l_return_status VARCHAR2(1);
282 l_msg_count NUMBER;
283 l_msg_data VARCHAR2(300);
284
285 CURSOR Is_auto_published IS
286 SELECT 'Y'
287 from pa_proj_workplan_attr
288 where project_id = l_project_id
289 and WP_AUTO_PUBLISH_FLAG = 'Y';
290
291 -- 4609421 : Following three variables
292 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
293 l_msg_index_out NUMBER;
294 l_data VARCHAR2(300);
295
296 BEGIN
297 l_project_id := wf_engine.GetItemAttrNumber(
298 itemtype => itemtype,
299 itemkey => itemkey,
300 aname => 'PROJECT_ID');
301
302 l_structure_version_id := wf_engine.getItemAttrNumber(
303 itemtype => itemtype
304 ,itemkey => itemkey
305 ,aname => 'STRUCTURE_VER_ID');
306
307 l_record_version_num := wf_engine.getItemAttrNumber(
308 itemtype => itemtype
309 ,itemkey => itemkey
310 ,aname => 'RECORD_VERSION_NUMBER');
311
312 -- 4609421 : Added debug messages
313 IF l_debug_mode = 'Y' THEN
314 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED Start', x_Log_Level=> 3);
315 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_project_id='||l_project_id, x_Log_Level=> 3);
316 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_structure_version_id='||l_structure_version_id, x_Log_Level=> 3);
317 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_record_version_num='||l_record_version_num, x_Log_Level=> 3);
318 END IF;
319
320 OPEN is_auto_published;
321 FETCH is_auto_published into l_dummy;
322 IF is_auto_published%NOTFOUND THEN
323 l_auto_publish_flag := 'N';
324 ELSE
325 l_auto_publish_flag := 'Y';
326 END IF;
327 CLOSE is_auto_published;
328
329 IF l_debug_mode = 'Y' THEN
330 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_auto_publish_flag='||l_auto_publish_flag, x_Log_Level=> 3);
331 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'Calling change_workplan_status', x_Log_Level=> 3);
332 END IF;
333
334
335 PA_PROJECT_STRUCTURE_PVT1.change_workplan_status
336 (
337 p_project_id => l_project_id
338 ,p_structure_version_id => l_structure_version_id
339 ,p_status_code => 'STRUCTURE_APPROVED'
340 ,p_record_version_number => l_record_version_num
341 ,x_return_status => l_return_status
342 ,x_msg_count => l_msg_count
343 ,x_msg_data => l_msg_data
344 );
345
346 IF l_debug_mode = 'Y' THEN
347 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_return_status='||l_return_status, x_Log_Level=> 3);
348 END IF;
349
350
351 IF (l_return_status = FND_API.g_ret_sts_success) THEN
352 wf_engine.SetItemAttrNumber(itemtype, itemkey,
353 'RECORD_VERSION_NUMBER',l_record_version_num+1);
354 END IF;
355
356 l_record_version_num := wf_engine.getItemAttrNumber(
357 itemtype => itemtype
358 ,itemkey => itemkey
359 ,aname => 'RECORD_VERSION_NUMBER');
360
361
362 IF l_auto_publish_flag = 'Y' THEN
363 l_structure_version_name := wf_engine.getItemAttrText(
364 itemtype => itemtype
365 ,itemkey => itemkey
366 ,aname => 'STRUCTURE_VER_NAME');
367
368 l_structure_version_desc := wf_engine.getItemAttrText(
369 itemtype => itemtype
370 ,itemkey => itemkey
371 ,aname => 'STRUCTURE_VER_DESC');
372
373 l_record_version_num := wf_engine.getItemAttrNumber(
374 itemtype => itemtype
375 ,itemkey => itemkey
376 ,aname => 'RECORD_VERSION_NUMBER');
377
378 l_responsibility_id := wf_engine.getItemAttrNumber(
379 itemtype => itemtype
380 ,itemkey => itemkey
381 ,aname => 'RESPONSIBILITY_ID');
382
383 l_user_id := wf_engine.getItemAttrNumber(
384 itemtype => itemtype
385 ,itemkey => itemkey
386 ,aname => 'USER_ID');
387
388 IF l_debug_mode = 'Y' THEN
389 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_structure_version_name='||l_structure_version_name, x_Log_Level=> 3);
390 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_structure_version_desc='||l_structure_version_desc, x_Log_Level=> 3);
391 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_record_version_num='||l_record_version_num, x_Log_Level=> 3);
392 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_responsibility_id='||l_responsibility_id, x_Log_Level=> 3);
393 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_user_id='||l_user_id, x_Log_Level=> 3);
394 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'Calling set global Info', x_Log_Level=> 3);
395 END IF;
396
397 -- 4609421 : Added call of Set_Global_Info
398 -- Bug 6786278 - Changed l_msg_count to l_msg_data for parameter p_msg_data
399 PA_INTERFACE_UTILS_PUB.Set_Global_Info
400 ( p_api_version_number => 1.0
401 , p_responsibility_id => l_responsibility_id
402 ,p_user_id => l_user_id
403 ,p_msg_count => l_msg_count
404 ,p_msg_data => l_msg_data
405 ,p_return_status => l_return_status);
406
407
408 IF l_debug_mode = 'Y' THEN
409 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_return_status='||l_return_status, x_Log_Level=> 3);
410 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'Calling Publish_Structure', x_Log_Level=> 3);
411 END IF;
412
413 --call publish api
414 PA_PROJECT_STRUCTURE_PUB1.Publish_Structure(
415 p_responsibility_id => l_responsibility_id
416 ,p_user_id => l_user_id
417 ,p_structure_version_id => l_structure_version_id
418 ,p_publish_structure_ver_name => l_structure_version_name
419 ,p_structure_ver_desc => l_structure_version_desc
420 ,p_effective_date => TRUNC(SYSDATE)
421 ,p_current_baseline_flag => 'N'
422 ,x_published_struct_ver_id => l_published_struc_ver_id
423 ,x_return_status => l_return_status
424 ,x_msg_count => l_msg_count
425 ,x_msg_data => l_msg_data
426 );
427
428 IF l_debug_mode = 'Y' THEN
429 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'l_return_status='||l_return_status, x_Log_Level=> 3);
430 for i in 1..fnd_msg_pub.count_msg loop
431 pa_interface_utils_pub.get_messages (
432 p_encoded => Fnd_Api.G_FALSE
433 ,p_data => l_data
434 ,p_msg_index => i
435 ,p_msg_count => l_msg_count
436 ,p_msg_data => l_msg_data
437 ,p_msg_index_out => l_msg_index_out );
438 pa_debug.write(x_Module=>'PA_WORKPLAN_WORKFLOW.CHANGE_STATUS_APPROVED', x_Msg => 'Error='||l_data, x_Log_Level=> 3);
439 end loop;
440 END IF;
441
442 --set structure_ver_id
443 wf_engine.SetItemAttrNumber(itemtype, itemkey,
444 'STRUCTURE_VER_ID',l_published_struc_ver_id);
445 wf_engine.SetItemAttrText(itemtype, itemkey,
446 'STRUCTURE_VER_ID_T',to_char(l_published_struc_ver_id));
447 --call PA_PROJECT_STRUCTURE_PVT1.change_workplan_status
448 END IF;
449 -- 4537865 : Included EXCEPTION BLOCK
450 EXCEPTION
451 WHEN OTHERS THEN
452 -- I havent reset value of resultout as this param is not assigned value anywhere in this API.
453 -- The Workflow function (Change Status to Approved) doesnt expect any result type
454
455 -- Included as per discussion with Rajnish : 4537865
456 Wf_Core.Context('pa_workplan_workflow','change_status_approved',itemtype,itemkey,to_char(actid),funcmode);
457 RAISE;
458 END change_status_approved;
459
460
461 procedure SELECT_ERROR_RECEIVER
462 (
463 p_item_type IN VARCHAR2
464 ,p_item_key IN VARCHAR2
465 ,actid IN NUMBER
466 ,funcmode IN VARCHAR2
467 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
468 )
469 IS
470 l_project_id NUMBER;
471
472 --get all project member who has edit privileges
473 CURSOR getProjMemberEditPerson IS
474 select distinct fu.user_id, fu.user_name, papf.email_address,
475 papf.full_name person_name
476 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
477 pa_project_role_types pprt
478 where object_type = 'PA_PROJECTS'
479 and object_id = l_project_id
480 and ppp.resource_type_id = 101
481 and ppp.project_role_id = pprt.project_role_id
482 and ppp.resource_source_id = fu.employee_id
483 and papf.person_id = fu.employee_id
484 and trunc(sysdate) between papf.effective_start_date
485 and nvl(papf.effective_end_date, sysdate+1)
486 and trunc(sysdate) between fu.start_date
487 and nvl(fu.end_date, sysdate+1)
488 and trunc(sysdate) between ppp.start_date_active
489 and nvl(ppp.end_date_active, sysdate+1)
490 and pprt.menu_id IN (select f1.menu_id
491 from fnd_compiled_menu_functions f1, fnd_form_functions f2
492 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
493 and f2.function_id = f1.function_id)
494 UNION /*Added this clause for 4482957 */
495 select distinct fu.user_id, fu.user_name, papf.email_address,
496 papf.full_name person_name
497 from fnd_user fu, per_all_people_f papf
498 where fu.user_id = fnd_global.USER_ID
499 and papf.person_id = fu.employee_id
500 and trunc(sysdate) between papf.effective_start_date
501 and nvl(papf.effective_end_date, sysdate+1)
502 and trunc(sysdate) between fu.start_date
503 and nvl(fu.end_date, sysdate+1) ;
504
505 --get all project member who has edit privileges
506
507 -- 4586987 customer_id is changed to person_party_id
508 /*
509 CURSOR getProjMemberEditParty IS
510 select distinct fu.user_id, fu.user_name, papf.email_address,
511 papf.full_name person_name
512 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
513 pa_project_role_types pprt
514 where object_type = 'PA_PROJECTS'
515 and object_id = l_project_id
516 and ppp.resource_type_id = 112
517 and ppp.project_role_id = pprt.project_role_id
518 and ppp.resource_id = fu.customer_id
519 and papf.person_id = fu.employee_id
520 and trunc(sysdate) between papf.effective_start_date
521 and nvl(papf.effective_end_date, sysdate+1)
522 and trunc(sysdate) between fu.start_date
523 and nvl(fu.end_date, sysdate+1)
524 and trunc(sysdate) between ppp.start_date_active
525 and nvl(ppp.end_date_active, sysdate+1)
526 and pprt.menu_id IN (select f1.menu_id
527 from fnd_compiled_menu_functions f1, fnd_form_functions f2
528 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
529 and f2.function_id = f1.function_id);
530 */
531
532 CURSOR getProjMemberEditParty IS
533 select distinct fu.user_id, fu.user_name, papf.email_address,
534 papf.full_name person_name
535 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
536 pa_project_role_types pprt
537 where object_type = 'PA_PROJECTS'
538 and object_id = l_project_id
539 and ppp.resource_type_id = 112
540 and ppp.project_role_id = pprt.project_role_id
541 and ppp.resource_id = fu.person_party_id -- customer_id is changed to person_party_id
542 and papf.person_id = fu.employee_id
543 and trunc(sysdate) between papf.effective_start_date
544 and nvl(papf.effective_end_date, sysdate+1)
545 and trunc(sysdate) between fu.start_date
546 and nvl(fu.end_date, sysdate+1)
547 and trunc(sysdate) between ppp.start_date_active
548 and nvl(ppp.end_date_active, sysdate+1)
549 and pprt.menu_id IN (select f1.menu_id
550 from fnd_compiled_menu_functions f1, fnd_form_functions f2
551 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
552 and f2.function_id = f1.function_id);
553 -- 4586987 end
554
555 l_structure_version_id NUMBER;
556 l_error_role VARCHAR2(30);
557 l_error_role_user VARCHAR2(300);
558
559 display_name VARCHAR2(2000);
560 email_address VARCHAR2(2000);
561 notification_preference VARCHAR2(2000);
562 language VARCHAR2(2000);
563 territory VARCHAR2(2000);
564 BEGIN
565 l_project_id := wf_engine.GetItemAttrNumber(
566 itemtype => p_item_type,
567 itemkey => p_item_key,
568 aname => 'PROJECT_ID');
569
570 l_structure_version_id := wf_engine.GetItemAttrNumber(
571 itemtype => p_item_type,
572 itemkey => p_item_key,
573 aname => 'STRUCTURE_VER_ID');
574
575 l_error_role := 'APRJ_'||p_item_type||p_item_key;
576 WF_DIRECTORY.CREATEADHOCROLE(role_name => l_error_role
577 ,role_display_name => l_error_role
578 ,expiration_date => sysdate+15); -- Set expiration_date for bug#5962401
579
580 l_error_role_user := NULL;
581 FOR v_1 IN getProjMemberEditPerson LOOP
582 IF (l_error_role_user IS NOT NULL) THEN
583 l_error_role_user := l_error_role_user||',';
584 END IF;
585 WF_DIRECTORY.GetRoleInfo(v_1.user_name,
586 display_name,
587 email_address,
588 notification_preference,
589 language,
590 territory);
591
592 IF display_name IS NULL THEN
593 wf_directory.createadhocuser( name => v_1.user_name
594 ,display_name => v_1.person_name
595 ,email_address => v_1.email_address);
596 END IF;
597 l_error_role_user := l_error_role_user||v_1.user_name;
598 END LOOP;
599
600 FOR v_1 IN getProjMemberEditParty LOOP
601 IF (l_error_role_user IS NOT NULL) THEN
602 l_error_role_user := l_error_role_user||',';
603 END IF;
604 WF_DIRECTORY.GetRoleInfo(v_1.user_name,
605 display_name,
606 email_address,
607 notification_preference,
608 language,
609 territory);
610
611 IF display_name IS NULL THEN
612 wf_directory.createadhocuser( name => v_1.user_name
613 ,display_name => v_1.person_name
614 ,email_address => v_1.email_address);
615 END IF;
616 l_error_role_user := l_error_role_user||v_1.user_name;
617 END LOOP;
618
619 IF (l_error_role_user IS NOT NULL) THEN
620 wf_directory.adduserstoadhocRole(l_error_role
621 ,l_error_role_user);
622 wf_engine.setitemattrtext(p_item_type
623 ,p_item_key
624 ,'WORKPLAN_ERR_RECEIVER'
625 ,l_error_role);
626 END IF;
627 -- 4537865 : Included EXCEPTION BLOCK
628 EXCEPTION
629 WHEN OTHERS THEN
630 -- I havent reset value of resultout as this param is not assigned value anywhere in this API.
631 -- The Workflow function (Select Error Receiver) doesnt expect any result type
632
633 -- Included as per discussion with Rajnish : 4537865
634 Wf_Core.Context('pa_workplan_workflow','SELECT_ERROR_RECEIVER',p_item_type,p_item_key,to_char(actid),funcmode);
635 RAISE;
636 END SELECT_ERROR_RECEIVER;
637
638
639 procedure SHOW_WORKPLAN_PUB_ERR
640 (document_id IN VARCHAR2,
641 display_type IN VARCHAR2,
642 document IN OUT NOCOPY clob, --File.Sql.39 bug 4440895
643 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
644 IS
645 l_content clob;
646
647 CURSOR get_error_info IS
648 SELECT page_content
649 FROM PA_PAGE_CONTENTS
650 WHERE pk1_value = document_id
651 AND object_type = 'PA_STRUCTURES'
652 AND pk2_value = 1;
653
654 l_size number;
655
656 l_chunk_size pls_integer:=10000;
657 l_copy_size int;
658 l_pos int := 0;
659
660 l_line varchar2(10000) := '' ;
661 -- Bug 3861540
662 l_return_status varchar2(1);
663 l_msg_count number;
664 l_msg_data varchar2(2000);
665
666 BEGIN
667
668 open get_error_info;
669 fetch get_error_info into l_content;
670 IF (get_error_info%FOUND) THEN
671 close get_error_info;
672
673 -- parse the retrieved clob data
674 l_size := dbms_lob.getlength(l_content);
675
676
677 l_pos := 1;
678 l_copy_size := 0;
679
680 while (l_copy_size < l_size) loop
681
682 dbms_lob.read(l_content,l_chunk_size,l_pos,l_line);
683
684 dbms_lob.write(document,l_chunk_size,l_pos,l_line);
685 l_copy_size := l_copy_size + l_chunk_size;
686 l_pos := l_pos + l_chunk_size;
687 end loop;
688
689 --Bug 3861540
690
691 pa_workflow_utils.modify_wf_clob_content(
692 p_document => document
693 ,x_return_status => l_return_status
694 ,x_msg_count => l_msg_count
695 ,x_msg_data => l_msg_data
696 );
697
698 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
699 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
700 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
701 end if;
702
703
704 --End of Changes Bug 3861540
705 ELSE
706 close get_error_info;
707 END IF;
708
709 document_type := 'text/html';
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
714 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
715 NULL;
716 END SHOW_WORKPLAN_PUB_ERR;
717
718 -- FP M : Project Execution Workflow
719 PROCEDURE START_PROJECT_EXECUTION_WF
720 (
721 p_project_id IN pa_projects_all.project_id%TYPE --changed type from varchar to column type 3619185 Satish
722 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
723 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
724 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
725 )
726 IS
727 l_item_key NUMBER;
728 l_item_type VARCHAR2(30) := 'PAPRJEX' ;
729 l_process_name VARCHAR2(30) := 'PA_PROJ_EXECUTION_PROCESS';
730 l_wf_enabled VARCHAR2(1) ;
731
732 l_err_code NUMBER;
733 l_err_stage VARCHAR2(30);
734 l_err_stack VARCHAR2(240);
735 l_debug_mode VARCHAR2(1) ;
736
737 CURSOR proj_number
738 IS
739 SELECT segment1
740 from pa_projects_all
741 where project_id = p_project_id ;
742
743 -- 5369295 for pqe bug5366726 , added to_char function call for p_project_id passed parameter
744 CURSOR is_wf_running
745 IS
746 SELECT 'Y' from pa_wf_processes where item_key= to_char(p_project_id) and item_type = 'PAPRJEX' ;
747
748 proj_number_rec proj_number%ROWTYPE ;
749 is_wf_running_rec is_wf_running%ROWTYPE ;
750
751 BEGIN
752 x_msg_count := 0;
753 x_return_status := FND_API.G_RET_STS_SUCCESS;
754 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
755
756 IF l_debug_mode = 'Y' THEN
757 PA_DEBUG.set_curr_function( p_function => 'START_PROJECT_EXECUTION_WF',
758 p_debug_mode => l_debug_mode );
759 END IF;
760
761 OPEN is_wf_running;
762 FETCH is_wf_running INTO is_wf_running_rec;
763 IF is_wf_running%FOUND THEN
764 CLOSE is_wf_running ;
765 RETURN ;
766 END IF ;
767 CLOSE is_wf_running ; /*5369295 for pqe bug5366726*/
768
769 -- Check whether WF is enabled for structure
770 l_wf_enabled := PA_PROJ_STRUCTURE_UTILS.IS_WF_ENABLED_FOR_STRUCTURE(
771 p_project_id => p_project_id
772 ,p_structure_type => 'WORKPLAN'
773 );
774
775 IF nvl(l_wf_enabled,'N') = 'N' THEN
776 -- Stop further processing and return .
777 -- PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
778 -- p_msg_name => 'PA_PS_CREATE_WF_FAILED');
779 -- x_return_status := FND_API.G_RET_STS_ERROR;
780 RETURN ;
781 END IF ;
782
783
784 IF l_debug_mode = 'Y' THEN
785 Pa_Debug.g_err_stage:= 'START_PROJECT_EXECUTION_WF : Calling wf_engine.createprocess';
786 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
787 END IF ;
788
789 OPEN proj_number;
790 FETCH proj_number INTO proj_number_rec;
791 CLOSE proj_number ;
792
793 wf_engine.createprocess(itemtype => l_item_type,
794 itemkey => to_char(p_project_id),
795 process => l_process_name
796 );
797
798 IF l_debug_mode = 'Y' THEN
799 Pa_Debug.g_err_stage:= 'START_PROJECT_EXECUTION_WF : Setting project Id';
800 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
801 END IF ;
802
803 -- Set attribute Project Id
804 wf_engine.SetItemAttrNumber
805 (itemtype => l_item_type
806 ,itemkey => to_char(p_project_id)
807 ,aname => 'PROJECT_ID'
808 ,avalue => p_project_id
809 );
810
811 -- Set attribute Project Number
812 wf_engine.SetItemAttrText
813 (itemtype => l_item_type
814 ,itemkey => to_char(p_project_id)
815 ,aname => 'PROJECT_NUMBER'
816 ,avalue => proj_number_rec.segment1
817 );
818
819 -- Set User Key
820 wf_engine.SetItemUserKey
821 (itemtype => l_item_type ,
822 itemkey => to_char(p_project_id) ,
823 userkey => proj_number_rec.segment1
824 );
825
826 IF l_debug_mode = 'Y' THEN
827 Pa_Debug.g_err_stage:= 'START_PROJECT_EXECUTION_WF : Calling wf_engine.startprocess';
828 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
829 END IF ;
830
831 wf_engine.startprocess(l_item_type
832 ,to_char(p_project_id));
833
834 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
835 --update pa_wf_process_table
836 PA_WORKFLOW_UTILS.INSERT_WF_PROCESSES
837 (
838 p_wf_type_code => 'PROJECT'
839 ,p_item_type => l_item_type
840 ,p_item_key => to_char(p_project_id)
841 ,p_entity_key1 => to_char(p_project_id)
842 ,p_entity_key2 => to_char(p_project_id)
843 ,p_description => NULL
844 ,p_err_code => l_err_code
845 ,p_err_stage => l_err_stage
846 ,p_err_stack => l_err_stack
847 );
848
849 IF (l_err_code <> 0) THEN
850 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
851 p_msg_name => 'PA_PS_CREATE_WF_FAILED');
852 x_return_status := FND_API.G_RET_STS_ERROR;
853 END IF;
854
855 ELSE
856 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
857 p_msg_name => 'PA_PS_CREATE_WF_FAILED');
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 END IF;
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 x_msg_count :=1;
864 x_msg_data:= substrb(SQLERRM, 1, 2000); -- 4537865 : Replaced substr with substrb
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 /* 5369295 -- Added fnd_msg_pub.add_exc_msg() for bug 5366726 */
867 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_WORKFLOW',
868 p_procedure_name => 'START_PROJECT_EXECUTION_WF',
869 p_error_text => SUBSTRB(SQLERRM,1,240));
870
871 END START_PROJECT_EXECUTION_WF ;
872
873 PROCEDURE CANCEL_PROJECT_EXECUTION_WF
874 (
875 p_project_id IN pa_projects_all.project_id%TYPE --changed type from varchar to column type 3619185 Satish
876 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
877 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
878 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
879 )
880 IS
881
882 l_item_type VARCHAR2(30) := 'PAPRJEX' ;
883
884 BEGIN
885 x_return_status := FND_API.g_ret_sts_success;
886
887 WF_ENGINE.AbortProcess(l_item_type
888 , to_char(p_project_id)
889 );
890 --Bug#3693248
891 --Added item_type join as part of performance fix.
892
893 DELETE FROM PA_WF_PROCESSES
894 WHERE item_key = to_char(p_project_id)
895 AND item_type = 'PAPRJEX' ;
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 x_msg_count :=1;
900 x_msg_data:= substrb(SQLERRM, 1, 2000); -- 4537865 : Replaced substr with substrb
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902 END CANCEL_PROJECT_EXECUTION_WF ;
903
904 PROCEDURE START_TASK_EXECUTION_WF
905 ( itemtype in varchar2
906 ,itemkey in varchar2
907 ,actid in number
908 ,funcmode in varchar2
909 ,resultout out NOCOPY varchar2 --File.Sql.39 bug 4440895
910 )
911 IS
912 l_item_key NUMBER;
913 l_item_type VARCHAR2(30) ;
914 l_process_name VARCHAR2(30);
915 l_lead_days NUMBER;
916 l_project_id NUMBER ;
917 l_project_number VARCHAR2(25) ;
918 l_versioned VARCHAR2(1) ;
919 l_structure_version_id NUMBER ;
920 l_err_code NUMBER;
921 l_err_stage VARCHAR2(30);
922 l_err_stack VARCHAR2(240);
923 l_start_date DATE ;
924 l_debug_mode VARCHAR2(1) ;
925
926
927 -- Cursor to get the WP structure version id in version disabled case
928 --
929
930 CURSOR get_struct_version_id
931 IS
932 SELECT pev.element_version_id
933 FROM pa_proj_element_versions pev ,
934 pa_proj_structure_types pst
935 WHERE pev.project_id = l_project_id
936 AND pev.object_type = 'PA_STRUCTURES'
937 AND pev.proj_element_id = pst.proj_element_id
938 AND pst.structure_type_id = 1; -- WORKPLAN
939
940 -- Bug#3693248 : Performace fix
941 -- Modified the joins , icluded project_id join between
942 -- pev and pevs.
943 CURSOR get_all_tasks(c_parent_struct_ver_id IN NUMBER)
944 IS
945 SELECT ppe.proj_element_id
946 ,pevs.scheduled_start_date
947 ,ppe.wf_item_type
948 ,ppe.wf_process
949 ,ppe.wf_start_lead_days
950 ,element_number
951 FROM pa_proj_elem_ver_schedule pevs ,
952 pa_proj_element_versions pev,
953 pa_proj_elements ppe
954 WHERE pev.parent_structure_version_id = c_parent_struct_ver_id
955 AND pev.object_type = 'PA_TASKS'
956 AND ppe.object_type = 'PA_TASKS'
957 AND pev.proj_element_id = ppe.proj_element_id
958 AND pev.project_id = ppe.project_id
959 AND nvl(ppe.enable_wf_flag,'N')= 'Y'
960 AND pevs.element_version_id = pev.element_version_id
961 AND pev.project_id = pevs.project_id
962 AND pev.proj_element_id = pevs.proj_element_id ;
963
964 get_all_tasks_rec get_all_tasks%ROWTYPE ;
965
966 BEGIN
967
968 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
969
970 IF l_debug_mode = 'Y' THEN
971 PA_DEBUG.set_curr_function( p_function => 'START_TASK_EXECUTION_WF',
972 p_debug_mode => l_debug_mode );
973 END IF;
974
975 -- Return if WF Not Running
976 IF (funcmode <> wf_engine.eng_run) THEN
977 resultout := wf_engine.eng_null;
978 RETURN;
979 END IF;
980
981 IF l_debug_mode = 'Y' THEN
982 Pa_Debug.g_err_stage:= 'START_TASK_EXECUTION_WF : Get Project Id';
983 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
984 END IF ;
985
986 l_project_id := wf_engine.GetItemAttrNumber
987 (itemtype => itemtype
988 ,itemkey => itemkey
989 ,aname => 'PROJECT_ID') ;
990
991 l_project_number := wf_engine.GetItemAttrText
992 (itemtype => itemtype
993 ,itemkey => itemkey
994 ,aname => 'PROJECT_NUMBER') ;
995
996 IF l_debug_mode = 'Y' THEN
997 Pa_Debug.g_err_stage:= 'START_TASK_EXECUTION_WF : Get the latest published version';
998 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
999 END IF ;
1000
1001 -- Check whether versioning is enabled
1002 l_versioned := PA_WORKPLAN_ATTR_UTILS.Check_Wp_Versioning_Enabled(l_project_id);
1003
1004 -- Get the latest published structure version
1005 -- id for versioning enabled case and only version
1006 -- for versioning disbled case .
1007
1008 IF nvl(l_versioned,'N') = 'Y' then
1009 l_structure_version_id := PA_PROJ_ELEMENTS_UTILS.LATEST_PUBLISHED_VER_ID(l_project_id,'WORKPLAN');
1010 ELSE
1011 OPEN get_struct_version_id ;
1012 FETCH get_struct_version_id INTO l_structure_version_id ;
1013 CLOSE get_struct_version_id ;
1014 END IF ;
1015
1016 IF l_debug_mode = 'Y' THEN
1017 Pa_Debug.g_err_stage:= 'START_TASK_EXECUTION_WF : Get all the tasks ';
1018 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1019 END IF ;
1020
1021 -- For all the tasks for which WP is enabled loop.
1022 FOR get_all_tasks_rec IN get_all_tasks(l_structure_version_id) LOOP
1023
1024 -- Check is Task Execution Process is already
1025 -- running for the task. Proceed if its not.
1026
1027 --Bug#3619754 : Added nvl as the API returns null in case WF is not running.
1028 IF (nvl(PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_WF_STATUS(get_all_tasks_rec.proj_element_id,l_project_id,'TASK_EXECUTION'),'X') <> 'ACTIVE') THEN
1029
1030 -- The task execution workflow will be started on the date
1031 -- when the schedule start date minus the Task execution Lead Time
1032
1033 PA_WORKPLAN_WORKFLOW_CLIENT.SET_LEAD_DAYS(
1034 p_item_type => get_all_tasks_rec.wf_item_type
1035 ,p_task_number => get_all_tasks_rec.element_number
1036 ,p_project_number => l_project_number
1037 ,x_lead_days => l_lead_days
1038 ) ;
1039 l_start_date := trunc((nvl(get_all_tasks_rec.scheduled_start_date,sysdate) - nvl(l_lead_days,0) )) ;
1040 IF l_start_date <= trunc(SYSDATE) THEN -- Bug : 4089623 Included 'less than' condition also
1041
1042 l_item_key := null ;
1043 SELECT pa_workflow_itemkey_s.nextval
1044 INTO l_item_key
1045 FROM dual;
1046
1047 IF l_debug_mode = 'Y' THEN
1048 Pa_Debug.g_err_stage:= 'START_PROJECT_EXECUTION_WF : Create Process';
1049 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1050 END IF ;
1051
1052 wf_engine.createprocess(itemtype => get_all_tasks_rec.wf_item_type,
1053 itemkey => to_char(l_item_key),
1054 process => get_all_tasks_rec.wf_process
1055 );
1056
1057
1058 wf_engine.setItemParent(itemtype => get_all_tasks_rec.wf_item_type,
1059 itemkey => to_char(l_item_key),
1060 parent_itemtype => itemtype,
1061 parent_itemkey => itemkey,
1062 parent_context => null);
1063
1064 IF l_debug_mode = 'Y' THEN
1065 Pa_Debug.g_err_stage:= 'START_PROJECT_EXECUTION_WF : Calling wf_engine.startprocess';
1066 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1067 END IF ;
1068
1069 wf_engine.startprocess(get_all_tasks_rec.wf_item_type
1070 ,to_char(l_item_key));
1071
1072
1073 --update pa_wf_process_table
1074 PA_WORKFLOW_UTILS.INSERT_WF_PROCESSES
1075 (
1076 p_wf_type_code => 'TASK_EXECUTION'
1077 ,p_item_type => get_all_tasks_rec.wf_item_type
1078 ,p_item_key => to_char(l_item_key)
1079 ,p_entity_key1 => to_char(l_project_id)
1080 ,p_entity_key2 => to_char(get_all_tasks_rec.proj_element_id)
1081 ,p_description => NULL
1082 ,p_err_code => l_err_code
1083 ,p_err_stage => l_err_stage
1084 ,p_err_stack => l_err_stack
1085 );
1086
1087 END IF ;
1088 END IF ;
1089 END LOOP ;
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 resultout := wf_engine.eng_null;
1093 Wf_Core.Context('pa_workplan_workflow','START_TASK_EXECUTION_WF',itemtype,itemkey,to_char(actid),funcmode);
1094 RAISE ;
1095 END START_TASK_EXECUTION_WF ;
1096
1097
1098 PROCEDURE RESTART_TASK_EXECUTION_WF
1099 ( p_task_id IN NUMBER
1100 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1101 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1102 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1103 )
1104 IS
1105
1106 CURSOR task_info
1107 IS
1108 SELECT enable_wf_flag
1109 ,wf_item_type
1110 ,wf_process
1111 ,wf_start_lead_days
1112 ,project_id
1113 FROM pa_proj_elements
1114 WHERE proj_element_id = p_task_id ;
1115
1116 l_err_code NUMBER;
1117 l_err_stage VARCHAR2(30);
1118 l_err_stack VARCHAR2(240);
1119 l_start_date DATE ;
1120 l_debug_mode VARCHAR2(1) ;
1121 l_item_key NUMBER ;
1122
1123 task_info_rec task_info%ROWTYPE ;
1124
1125 BEGIN
1126
1127 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1128
1129 IF l_debug_mode = 'Y' THEN
1130 PA_DEBUG.set_curr_function( p_function => 'RESTART_TASK_EXECUTION_WF',
1131 p_debug_mode => l_debug_mode );
1132 END IF;
1133
1134
1135 IF l_debug_mode = 'Y' THEN
1136 Pa_Debug.g_err_stage:= 'RESTART_TASK_EXECUTION_WF : Open Cursor task_info';
1137 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1138 END IF ;
1139
1140 OPEN task_info ;
1141 FETCH task_info INTO task_info_rec ;
1142 CLOSE task_info ;
1143
1144 IF nvl(task_info_rec.enable_wf_flag,'N') = 'N' THEN
1145 x_return_status := FND_API.G_RET_STS_ERROR;
1146 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1147 ,p_msg_name => 'PA_WF_IS_NOT_ENABLED_TASK');
1148 RETURN ;
1149 END IF ;
1150
1151
1152
1153 -- Check is Task Execution Process is already
1154 -- running for the task. Proceed if its not.
1155
1156 --Bug#3619754 : Added nvl as the API returns null in case WF is not running.
1157 IF (nvl(PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_WF_STATUS(p_task_id,task_info_rec.project_id,'TASK_EXECUTION'),'X') <> 'ACTIVE') THEN
1158
1159 -- Not performing the lead days validation as
1160 -- this API is explicitly called to restart the
1161 -- task execution WF after cancelling it.
1162
1163 -- The restart option will be available in the
1164 -- task details page for which the WF is in cancelled status.
1165
1166 l_item_key := null ;
1167 SELECT pa_workflow_itemkey_s.nextval
1168 INTO l_item_key
1169 FROM dual;
1170
1171 IF l_debug_mode = 'Y' THEN
1172 Pa_Debug.g_err_stage:= 'RESTART_TASK_EXECUTION_WF : Create Process';
1173 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1174 END IF ;
1175
1176 wf_engine.createprocess(itemtype => task_info_rec.wf_item_type,
1177 itemkey => to_char(l_item_key),
1178 process => task_info_rec.wf_process
1179 );
1180
1181
1182 wf_engine.setItemParent(itemtype => task_info_rec.wf_item_type ,
1183 itemkey => to_char(l_item_key),
1184 parent_itemtype => 'PAPRJEX' ,
1185 parent_itemkey => to_char(task_info_rec.project_id),
1186 parent_context => null);
1187
1188 IF l_debug_mode = 'Y' THEN
1189 Pa_Debug.g_err_stage:= 'RESTART_TASK_EXECUTION_WF : Calling wf_engine.startprocess';
1190 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1191 END IF ;
1192
1193 wf_engine.startprocess(task_info_rec.wf_item_type
1194 ,to_char(l_item_key));
1195
1196
1197 --update pa_wf_process_table
1198 PA_WORKFLOW_UTILS.INSERT_WF_PROCESSES
1199 (
1200 p_wf_type_code => 'TASK_EXECUTION'
1201 ,p_item_type => task_info_rec.wf_item_type
1202 ,p_item_key => to_char(l_item_key)
1203 ,p_entity_key1 => to_char(task_info_rec.project_id)
1204 ,p_entity_key2 => to_char(p_task_id)
1205 ,p_description => NULL
1206 ,p_err_code => l_err_code
1207 ,p_err_stage => l_err_stage
1208 ,p_err_stack => l_err_stack
1209 );
1210
1211 END IF ;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 x_msg_count := 1; -- 4537865
1215 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ; -- 4537865
1217 RAISE ;
1218 END RESTART_TASK_EXECUTION_WF ;
1219
1220
1221 PROCEDURE CANCEL_TASK_EXECUTION_WF
1222 (
1223 p_task_id IN VARCHAR2
1224 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1225 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1226 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1227 )
1228 IS
1229
1230 l_item_key NUMBER;
1231 l_item_type VARCHAR2(30) ;
1232
1233 l_err_code NUMBER;
1234 l_err_stage VARCHAR2(30);
1235 l_err_stack VARCHAR2(240);
1236 l_debug_mode VARCHAR2(1) ;
1237
1238 Cursor get_item_type IS
1239 Select enable_wf_flag
1240 ,wf_item_type
1241 ,project_id
1242 from pa_proj_elements
1243 where proj_element_id = p_task_id ;
1244
1245 get_item_type_rec get_item_type%ROWTYPE ;
1246
1247 BEGIN
1248
1249 x_return_status := FND_API.g_ret_sts_success;
1250 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1251
1252 IF l_debug_mode = 'Y' THEN
1253 Pa_Debug.g_err_stage:= 'CANCEL_TASK_EXECUTION_WF : Cancel Task Execution Workflow';
1254 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1255 END IF ;
1256
1257 OPEN get_item_type ;
1258 FETCH get_item_type INTO get_item_type_rec ;
1259 CLOSE get_item_type ;
1260
1261 -- Cancel only if its running.
1262 IF nvl(get_item_type_rec.enable_wf_flag,'N') = 'Y' THEN
1263 IF nvl(PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_WF_STATUS(p_task_id,get_item_type_rec.project_id,'TASK_EXECUTION'),'X') = 'ACTIVE' THEN
1264 l_item_key := PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_WF_ITEMKEY
1265 (p_proj_element_id => p_task_id
1266 ,p_project_id => get_item_type_rec.project_id
1267 ,p_wf_type_code =>'TASK_EXECUTION'
1268 );
1269
1270 WF_ENGINE.AbortProcess
1271 (get_item_type_rec.wf_item_type
1272 ,to_char(l_item_key)
1273 );
1274 END IF ;
1275 END IF ;
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 x_msg_count :=1;
1279 x_msg_data:= substrb(SQLERRM, 1, 2000); -- 4537865 : Replaced substr with substrb
1280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1281 END CANCEL_TASK_EXECUTION_WF ;
1282
1283
1284 PROCEDURE IS_PROJECT_CLOSED
1285 ( itemtype in varchar2
1286 ,itemkey in varchar2
1287 ,actid in number
1288 ,funcmode in varchar2
1289 ,resultout out NOCOPY varchar2 --File.Sql.39 bug 4440895
1290 )
1291 IS
1292 CURSOR proj_status_cur(c_project_id in number)
1293 IS
1294 SELECT pst.project_system_status_code
1295 FROM pa_projects_all pa ,
1296 pa_project_statuses pst
1297 WHERE pa.project_id = c_project_id
1298 AND pa.project_status_code = pst.project_status_code;
1299
1300 l_project_id NUMBER ;
1301 l_debug_mode VARCHAR2(1) ;
1302 proj_status_cur_rec proj_status_cur%ROWTYPE ;
1303
1304 BEGIN
1305
1306 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1307
1308 IF l_debug_mode = 'Y' THEN
1309 PA_DEBUG.set_curr_function( p_function => 'IS_PROJECT_CLOSED',
1310 p_debug_mode => l_debug_mode );
1311 END IF;
1312
1313 -- Return if WF Not Running
1314 IF (funcmode <> wf_engine.eng_run) THEN
1315 resultout := wf_engine.eng_null;
1316 RETURN;
1317 END IF;
1318
1319 IF l_debug_mode = 'Y' THEN
1320 Pa_Debug.g_err_stage:= 'IS_PROJECT_CLOSED : Get Project Id';
1321 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,3);
1322 END IF ;
1323
1324 l_project_id := wf_engine.GetItemAttrNumber
1325 (itemtype => itemtype
1326 ,itemkey => itemkey
1327 ,aname => 'PROJECT_ID') ;
1328
1329 OPEN proj_status_cur(l_project_id);
1330 FETCH proj_status_cur INTO proj_status_cur_rec ;
1331 CLOSE proj_status_cur ;
1332
1333 IF proj_status_cur_rec.project_system_status_code = 'CLOSED' THEN
1334 resultout := wf_engine.eng_completed||':'||'Y';
1335 ELSE
1336 resultout := wf_engine.eng_completed||':'||'N';
1337 END IF ;
1338 -- 4537865 : Included EXCEPTION BLOCK
1339 EXCEPTION
1340 WHEN OTHERS THEN
1341 resultout := wf_engine.eng_null;
1342
1343 -- Included as per discussion with Rajnish : 4537865
1344 Wf_Core.Context('pa_workplan_workflow','SELECT_ERROR_RECEIVER',itemtype,itemkey,to_char(actid),funcmode);
1345 RAISE;
1346 END IS_PROJECT_CLOSED ;
1347 -- FP M : Project Execution Workflow
1348
1349 END PA_WORKPLAN_WORKFLOW;