[Home] [Help]
PACKAGE BODY: APPS.PA_TASK_WORKFLOW_PKG
Source
1 PACKAGE BODY PA_TASK_WORKFLOW_PKG as
2 /* $Header: PATSKWFB.pls 120.4.12010000.2 2009/08/11 07:25:53 anuragar noship $ */
3
4 p_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 g_error_message VARCHAR2(1000) :='';
6 g_error_stack VARCHAR2(500) :='';
7 g_error_stage VARCHAR2(100) :='';
8
9 -- This procedure is for logging debug messages so as to debug the code
10 -- in case of any unknown issues that occur during the entire cycle of
11 -- a deduction request.
12
13 PROCEDURE log_message (p_log_msg IN VARCHAR2, debug_level IN NUMBER) IS
14 BEGIN
15 IF P_DEBUG_MODE = 'Y' THEN
16 pa_debug.write('log_message: ' || 'PA PWP Notification: ', 'log: ' || p_log_msg, debug_level);
17 END IF;
18 END log_message;
19
20 -- This procedure is to initiate Task Approval Workflow
21
22 PROCEDURE Start_Task_Aprv_Wf (p_item_type IN VARCHAR2
23 ,p_process IN VARCHAR2
24 ,p_project_id IN NUMBER
25 ,p_task_id IN NUMBER
26 ,p_parent_struc_ver IN NUMBER
27 ,p_approver_user_id IN NUMBER
28 ,p_ci_id IN NUMBER
29 ,x_err_stack IN OUT NOCOPY VARCHAR2
30 ,x_err_stage IN OUT NOCOPY VARCHAR2
31 ,x_err_code OUT NOCOPY NUMBER
32 ) IS
33
34 -- Cursor to get the user name of the provided user_id
35 CURSOR c_starter_name(l_starter_user_id NUMBER) IS
36 SELECT user_name
37 FROM FND_USER
38 WHERE user_id = l_starter_user_id;
39
40 -- Cursor to get full name of the user
41 CURSOR c_starter_full_name(l_starter_user_id NUMBER) IS
42 SELECT e.first_name||' '||e.last_name
43 FROM FND_USER f, PER_ALL_PEOPLE_F e
44 WHERE f.user_id = l_starter_user_id
45 AND f.employee_id = e.person_id
46 AND e.effective_end_date = ( SELECT MAX(papf.effective_end_date)
47 FROM per_all_people_f papf
48 WHERE papf.person_id = e.person_id);
49 CURSOR c_wf_started_date IS
50 SELECT SYSDATE FROM SYS.DUAL;
51
52 -- Cursor to fetch the complete change document information.
53 CURSOR c_ci_info IS
54 SELECT ci_id,
55 summary,
56 pci.description,
57 pctb.short_name||' ('||pci.ci_number||')' ci_number,
58 ci_type_class_code,
59 pci.created_by created_by,
60 pci.creation_date creation_date
61 FROM PA_CONTROL_ITEMS pci,
62 PA_CI_TYPES_vl pctb
63 WHERE ci_id = p_ci_id
64 AND pctb.ci_type_id = pci.ci_type_id;
65
66 l_proj_info_rec c_proj_info%ROWTYPE;
67
68 itemkey VARCHAR2(30);
69 l_wf_started_date DATE;
70 l_workflow_started_by_id NUMBER;
71 l_user_full_name VARCHAR(400);
72 l_user_name VARCHAR(240);
73 l_resp_id NUMBER;
74 l_err_code NUMBER := 0;
75 l_err_stack VARCHAR2(2000);
76 l_err_stage VARCHAR2(2000);
77 l_content_id NUMBER;
78
79 itemtype CONSTANT VARCHAR2(15) := p_item_type;--'PATASKWF';
80 l_process CONSTANT VARCHAR2(20) := p_process;--'PA_TASK_APPROVAL_WF';
81
82 c_task_info_rec c_task_info%ROWTYPE;
83 c_ci_info_rec c_ci_info%ROWTYPE;
84
85 BEGIN
86
87 log_message('Inside the procedure Start_Task_Aprv_Wf',3);
88 l_content_id := 0;
89
90 --b6694902_debug.debug('In Start_Task_Aprv_Wf ');
91
92 log_message('Before fetching the task info',3);
93 -- Fetch Task Info
94 OPEN c_task_info(p_project_id, p_task_id);
95 FETCH c_task_info INTO c_task_info_rec;
96 IF c_task_info%NOTFOUND THEN
97 log_message('Cursor failed to fetch the task information',3);
98 x_err_code := 100;
99 x_err_stage := 10;
100 x_err_stack := 'PA_TASK_NOT_EXISTS';
101 CLOSE c_task_info;
102 return;
103 END IF;
104 CLOSE c_task_info;
105
106 log_message('Opening the cursor for fetching change document info',3);
107 OPEN c_ci_info;
108 FETCH c_ci_info INTO c_ci_info_rec;
109 CLOSE c_ci_info;
110
111 x_err_code := 0;
112 --get the unique identifier for this specific workflow
113 SELECT pa_workflow_itemkey_s.nextval
114 INTO itemkey
115 FROM DUAL;
116
117 log_message('Initializing the variables',3);
118 -- Need this to populate the attribute information in Workflow
119
120 l_workflow_started_by_id := c_task_info_rec.created_by;
121 l_resp_id := FND_GLOBAL.resp_id;
122
123 log_message('Calling workflow engine to create the process',3);
124 -- Create a new Wf process
125 --b6694902_debug.debug('Before calling createprocess ');
126 WF_ENGINE.CreateProcess( itemtype => itemtype,
127 itemkey => itemkey,
128 process => l_process);
129
130
131 -- Fetch all required info to populate Wf Attributes
132 OPEN c_starter_name(l_workflow_started_by_id );
133 FETCH c_starter_name INTO l_user_name;
134 IF c_starter_name%NOTFOUND THEN
135 x_err_code := 100;
136 x_err_stage := 20;
137 END IF;
138 CLOSE c_starter_name;
139
140 OPEN c_starter_full_name(l_workflow_started_by_id );
141 FETCH c_starter_full_name INTO l_user_full_name;
142 IF c_starter_full_name%NOTFOUND THEN
143 x_err_code := 100;
144 x_err_stage:= 30;
145 END IF;
146 CLOSE c_starter_full_name;
147
148 OPEN c_wf_started_date;
149 FETCH c_wf_started_date INTO l_wf_started_date;
150 CLOSE c_wf_started_date;
151
152 log_message('Fetching the project info',3);
153 OPEN c_proj_info(p_project_id);
154 FETCH c_proj_info INTO l_proj_info_rec;
155 IF c_proj_info%NOTFOUND THEN
156 x_err_code := 100;
157 x_err_stage:= 40;
158 END IF;
159 CLOSE c_proj_info;
160
161 log_message('Assinging the workflow attributes',3);
162
163 log_message('Project Id ['||l_proj_info_rec.project_id||'], '||
164 'Project Number ['||l_proj_info_rec.project_number||'], '||
165 'Project Name ['||l_proj_info_rec.project_name||'], '||
166 'Project Org ['||l_proj_info_rec.organization_id||'] ,'||
167 'Change Document Num ['||c_ci_info_rec.ci_number||'], '||
168 'Change Document Id ['||p_ci_id||']'
169 ,3);
170
171 IF l_proj_info_rec.project_id IS NOT NULL THEN
172 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
173 ,itemkey => itemkey
174 ,aname => 'PROJECT_ID'
175 ,avalue => l_proj_info_rec.project_id
176 );
177 END IF;
178
179 IF l_proj_info_rec.project_number IS NOT NULL THEN
180 WF_ENGINE.SetItemAttrText (itemtype => itemtype
181 ,itemkey => itemkey
182 ,aname => 'PROJECT_NUMBER'
183 ,avalue => l_proj_info_rec.project_number
184 );
185 END IF;
186
187 IF c_ci_info_rec.ci_number IS NOT NULL THEN
188 WF_ENGINE.SetItemAttrText (itemtype => itemtype
189 ,itemkey => itemkey
190 ,aname => 'CONTROL_ITEM_NUMBER'
191 ,avalue => c_ci_info_rec.ci_number
192 );
193 END IF;
194
195 IF l_proj_info_rec.project_name IS NOT NULL THEN
196 WF_ENGINE.SetItemAttrText (itemtype => itemtype
197 ,itemkey => itemkey
198 ,aname => 'PROJECT_NAME'
199 ,avalue => l_proj_info_rec.project_name
200 );
201 END IF;
202
203 IF l_proj_info_rec.organization_id IS NOT NULL THEN
204 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
205 ,itemkey => itemkey
206 ,aname => 'PROJ_ORG_ID'
207 ,avalue => l_proj_info_rec.organization_id
208 );
209 END IF;
210
211 log_message('Task Id ['||c_task_info_rec.task_id||'], '||
212 'Task Number ['||c_task_info_rec.task_number||'], '||
213 'Task Name ['||c_task_info_rec.task_name||'], '||
214 'Parent Task ['||c_task_info_rec.parent_task_id||'] ,'||
215 'Parent Task Num ['||c_task_info_rec.parent_task_number||'], '||
216 'Task Start Date ['||c_task_info_rec.scheduled_start_date||'], '||
217 'Task End Date ['||c_task_info_rec.scheduled_end_date||']'
218 ,3);
219
220 IF c_task_info_rec.parent_task_number IS NOT NULL THEN
221 WF_ENGINE.SetItemAttrText (itemtype => itemtype
222 ,itemkey => itemkey
223 ,aname => 'PARENT_TASK_NUMBER'
224 ,avalue => c_task_info_rec.parent_task_number
225 );
226 END IF;
227
228 IF c_task_info_rec.parent_task_id IS NOT NULL THEN
229 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
230 ,itemkey => itemkey
231 ,aname => 'PARENT_TASK_ID'
232 ,avalue => c_task_info_rec.parent_task_id
233 );
234 END IF;
235
236 IF c_task_info_rec.task_number IS NOT NULL THEN
237 WF_ENGINE.SetItemAttrText (itemtype => itemtype
238 ,itemkey => itemkey
239 ,aname => 'TASK_NUMBER'
240 ,avalue => c_task_info_rec.task_number
241 );
242 END IF;
243
244 IF c_task_info_rec.task_name IS NOT NULL THEN
245 WF_ENGINE.SetItemAttrText (itemtype => itemtype
246 ,itemkey => itemkey
247 ,aname => 'TASK_NAME'
248 ,avalue => c_task_info_rec.task_number
249 );
250 END IF;
251
252 IF c_task_info_rec.task_id IS NOT NULL THEN
253 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
254 ,itemkey => itemkey
255 ,aname => 'TASK_ID'
256 ,avalue => c_task_info_rec.task_id
257 );
258 END IF;
259
260 IF c_task_info_rec.scheduled_start_date IS NOT NULL THEN
261 WF_ENGINE.SetItemAttrDate (itemtype => itemtype
262 ,itemkey => itemkey
263 ,aname => 'TASK_ST_DATE'
264 ,avalue => c_task_info_rec.scheduled_start_date
265 );
266 END IF;
267
268 IF c_task_info_rec.scheduled_end_date IS NOT NULL THEN
269 WF_ENGINE.SetItemAttrDate (itemtype => itemtype
270 ,itemkey => itemkey
271 ,aname => 'TASK_END_DATE'
272 ,avalue => c_task_info_rec.scheduled_end_date
273 );
274 END IF;
275
276 IF p_ci_id IS NOT NULL THEN
277 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
278 ,itemkey => itemkey
279 ,aname => 'CI_ID'
280 ,avalue => p_ci_id
281 );
282 END IF;
283
284 log_message('Content Id ['||l_content_id||'], '||
285 'Workflow started by ['||l_user_full_name||']'
286 ,3);
287
288 IF l_content_id IS NOT NULL THEN
289 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
290 ,itemkey => itemkey
291 ,aname => 'CONTENT_ID'
292 ,avalue => l_content_id
293 );
294 END IF;
295
296 IF l_workflow_started_by_id IS NOT NULL THEN
297 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
298 ,itemkey => itemkey
299 ,aname => 'SUBMITTED_BY_ID'
300 ,avalue => l_workflow_started_by_id
301 );
302 END IF;
303
304 IF l_user_full_name IS NOT NULL THEN
305 WF_ENGINE.SetItemAttrText (itemtype => itemtype
306 ,itemkey => itemkey
307 ,aname => 'SUBMITTED_BY'
308 ,avalue => l_user_full_name
309 );
310 END IF;
311
312 IF l_workflow_started_by_id IS NOT NULL THEN
313 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
314 ,itemkey => itemkey
315 ,aname => 'WORKFLOW_STARTED_BY_ID'
316 ,avalue => l_workflow_started_by_id
317 );
318 END IF;
319
320 IF l_user_name IS NOT NULL THEN
321 WF_ENGINE.SetItemAttrText (itemtype => itemtype
322 ,itemkey => itemkey
323 ,aname => 'WORKFLOW_STARTED_BY_NAME'
324 ,avalue => l_user_name
325 );
326 END IF;
327
328 IF l_user_full_name IS NOT NULL THEN
329 WF_ENGINE.SetItemAttrText (itemtype => itemtype
330 ,itemkey => itemkey
331 ,aname => 'WORKFLOW_STARTED_BY_FULL_NAME'
332 ,avalue => l_user_full_name
333 );
334 END IF;
335
336 IF l_resp_id IS NOT NULL THEN
337 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
338 ,itemkey => itemkey
339 ,aname => 'RESPONSIBILITY_ID'
340 ,avalue => l_resp_id
341 );
342 END IF;
343
344 IF l_wf_started_date IS NOT NULL THEN
345 WF_ENGINE.SetItemAttrText (itemtype => itemtype
346 ,itemkey => itemkey
347 ,aname => 'WF_STARTED_DATE'
348 ,avalue => l_wf_started_date
349 );
350 END IF;
351
352 IF p_approver_user_id IS NOT NULL THEN
353 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
354 ,itemkey => itemkey
355 ,aname => 'TASK_APPROVER_ID'
356 ,avalue => p_approver_user_id
357 );
358 END IF;
359
360 OPEN c_starter_name(p_approver_user_id );
361 FETCH c_starter_name INTO l_user_full_name;
362 IF c_starter_name%NOTFOUND THEN
363 x_err_code := 100;
364 x_err_stage:= 30;
365 END IF;
366 CLOSE c_starter_name;
367
368 log_message('Task approver id ['||p_approver_user_id||'], '||
369 'Task approver ['||l_user_full_name||']'
370 ,3);
371
372 IF l_user_full_name IS NOT NULL THEN
373 WF_ENGINE.SetItemAttrText (itemtype => itemtype
374 ,itemkey => itemkey
375 ,aname => 'TASK_APPROVER_NAME'
376 ,avalue => l_user_full_name
377 );
378 END IF;
379
380 l_user_full_name :='';
381 OPEN c_starter_full_name(p_approver_user_id );
382 FETCH c_starter_full_name INTO l_user_full_name;
383 IF c_starter_full_name%NOTFOUND THEN
384 x_err_code := 100;
385 x_err_stage:= 30;
386 END IF;
387 CLOSE c_starter_full_name;
388
389 IF l_user_full_name IS NOT NULL THEN
390 WF_ENGINE.SetItemAttrText (itemtype => itemtype
391 ,itemkey => itemkey
392 ,aname => 'TASK_APPROVER_FULLNAME'
393 ,avalue => l_user_full_name
394 );
395 END IF;
396
397 IF p_parent_struc_ver IS NOT NULL THEN
398 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
399 ,itemkey => itemkey
400 ,aname => 'PARENT_STRUC_VER_ID'
401 ,avalue => p_parent_struc_ver
402 );
403 END IF;
404
405 WF_ENGINE.StartProcess (itemtype => itemtype
406 ,itemkey => itemkey
407 );
408
409 IF x_err_code = 0 THEN
410 PA_WORKFLOW_UTILS.Insert_WF_Processes (p_wf_type_code => 'PATASKWF'
411 ,p_item_type => ItemType
412 ,p_item_key => ItemKey
413 ,p_entity_key1 => c_task_info_rec.task_id
414 ,p_description => c_task_info_rec.task_number
415 ,p_err_code => l_err_code
416 ,p_err_stage => l_err_stage
417 ,p_err_stack => l_err_stack
418 );
419 END IF;
420
421 IF l_err_code <> 0 THEN
422 x_err_code := l_err_code;
423 x_err_stage := l_err_stage;
424 x_err_stack := l_err_stack;
425 END IF;
426
427 EXCEPTION
428 WHEN FND_API.G_EXC_ERROR THEN
429 WF_CORE.CONTEXT('PA_TASK_APPROVAL_WF ','Start_Task_Aprv_Wf');
430 RAISE;
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 x_err_code := SQLCODE;
433 WF_CORE.CONTEXT('PA_TASK_APPROVAL_WF','Start_Task_Aprv_Wf');
434 RAISE;
435 WHEN OTHERS THEN
436 --b6694902_debug.debug('In Others Exception ');
437 --wf_engine.threshold := l_save_threshold;
438 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 FND_MSG_PUB.Add_Exc_Msg
440 ( p_pkg_name => 'PA_TASK_APPROVAL_PKG'
441 , p_procedure_name => 'Start_Task_Aprv_Wf'
442 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
443 );
444 END Start_Task_Aprv_Wf;
445
446 -- Procedure to generate the task approval notificaiton
447 PROCEDURE Generate_Task_Aprv_Notify
448 (p_item_type IN VARCHAR2
449 ,p_item_key IN VARCHAR2
450 ,p_project_id IN NUMBER
451 ,p_org_id IN NUMBER
452 ,p_task_id IN NUMBER
453 ,p_parent_struc_ver IN NUMBER
454 ,p_ci_id IN NUMBER
455 ,p_cd_yn IN VARCHAR2 := 'Y'
456 ,x_content_id OUT NOCOPY NUMBER) IS
457
458 -- Cursor to fetch the name of the organization for the inputted organization_id
459 CURSOR c_orgz_info (p_carrying_out_organization_id NUMBER) IS
460 SELECT name organization_name
461 FROM HR_ORGANIZATION_UNITS
462 WHERE organization_id = p_carrying_out_organization_id;
463
464 -- Cursor to fetch the change document information for the respective change document id
465 CURSOR c_ci_info IS
466 SELECT ci_id,
467 summary,
468 pci.description,
469 pctb.short_name||' ('||pci.ci_number||')' ci_number,
470 ci_type_class_code,
471 pci.created_by created_by,
472 pci.creation_date creation_date
473 FROM PA_CONTROL_ITEMS pci,
474 PA_CI_TYPES_vl pctb
475 WHERE ci_id = p_ci_id
476 AND pctb.ci_type_id = pci.ci_type_id;
477
478 l_orgz_info_rec c_orgz_info%ROWTYPE;
479 l_project_number pa_projects_all.segment1%TYPE;
480
481 l_clob CLOB;
482 l_text VARCHAR2(32767);
483 l_index NUMBER;
484 x_return_status VARCHAR2(1);
485 x_msg_count NUMBER;
486 x_msg_data VARCHAR2(250);
487
488 l_err_code NUMBER := 0;
489 l_err_stack VARCHAR2(630);
490 l_err_stage VARCHAR2(80);
491
492 l_page_content_id NUMBER :=0;
493
494 l_ci_type_class_code VARCHAR2(15);
495 l_ci_description PA_CONTROL_ITEMS.description%TYPE;
496 l_ci_created_by VARCHAR2(300);
497 l_ci_creation_date DATE;
498 l_ci_number pa_control_items.ci_number%TYPE;
499
500 l_mgr_name VARCHAR2(1000);
501
502 c_task_info_rec c_task_info%ROWTYPE;
503 c_ci_info_rec c_ci_info%ROWTYPE;
504 c_user_info_rec c_user_info%ROWTYPE;
505
506 PRAGMA AUTONOMOUS_TRANSACTION;
507
508
509 BEGIN
510
511 log_message('Inside the procedure Generate_Task_Aprv_Notify',3);
512
513 log_message('Opening cursor for fetching the organization name' ,3);
514 OPEN c_orgz_info(p_org_id);
515 FETCH c_orgz_info INTO l_orgz_info_rec;
516 CLOSE c_orgz_info;
517
518 log_message('Opening cursor for fetching the task info',3);
519 OPEN c_task_info(p_project_id, p_task_id);
520 FETCH c_task_info INTO c_task_info_rec;
521 IF c_task_info%NOTFOUND THEN
522 CLOSE c_task_info;
523 return;
524 END IF;
525 CLOSE c_task_info;
526
527
528 log_message('Opening cursor for fetching task organization information',3);
529
530 OPEN c_orgz_info(c_task_info_rec.organization);
531 FETCH c_orgz_info INTO l_orgz_info_rec;
532 CLOSE c_orgz_info;
533
534 log_message('Fetching Task Manager name',3);
535 BEGIN
536 SELECT e.first_name||' '||e.last_name
537 INTO l_mgr_name
538 FROM PA_EMPLOYEES e
539 WHERE person_id = c_task_info_rec.manager_person_id;
540
541 log_message('Task Manager ['||l_mgr_name||']',3);
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 NULL;
546 END;
547
548 log_message('Fetching the Change document information',3);
549 OPEN c_ci_info;
550 FETCH c_ci_info INTO c_ci_info_rec;
551 CLOSE c_ci_info;
552
553 open c_user_info(c_ci_info_rec.created_by);
554 fetch c_user_info into c_user_info_rec;
555 close c_user_info;
556
557 l_ci_created_by := c_user_info_rec.full_name;
558
559 x_content_id := 0;
560
561 log_message('Creating the page content' ,3);
562
563 -- Creating new page content in pa_page_contents.
564 log_message('Before calling PA_PAGE_CONTENTS_PUB.create_page_contents' ,3);
565 PA_PAGE_CONTENTS_PUB.Create_Page_Contents(p_init_msg_list => fnd_api.g_false
566 ,p_validate_only => fnd_api.g_false
567 ,p_object_type => 'PA_TASK_APPROVAL_WF'
568 ,p_pk1_value => p_task_id
569 ,p_pk2_value => NULL
570 ,x_page_content_id => l_page_content_id
571 ,x_return_status => x_return_status
572 ,x_msg_count => x_msg_count
573 ,x_msg_data => x_msg_data);
574
575 x_content_id := l_page_content_id;
576
577 BEGIN
578 SELECT page_content
579 INTO l_clob
580 FROM PA_PAGE_CONTENTS
581 WHERE page_content_id = l_page_content_id FOR UPDATE NOWAIT;
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 RAISE;
585 END;
586
587 l_text := '';
588
589 log_message('Creating the page content dynamically',3);
590
591 --Starting the page content
592 l_text := '<table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td>';
593 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
594
595 -- START : Task Information Section
596 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
597 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
598
599 --Heading
600 l_text := '<tr><td height="12"><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);">';
601 l_text := l_text || '<tr><td width="100%"><h2 valign="middle" marginheight="0" style="padding:0px 0px 0px 8px;margin:5px 0px 0px 0px;margin-top:1px;margin-bottom:0px;border-bottom:1px ';
602 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Task Information</b></font></h2></td></tr></table></td></tr>';
603 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
604
605 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td bgcolor="#EAEFF5">';
606 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top">';
607 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0">';
608 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
609
610 --Task Number
611 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Task Number</font>';
612 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
613 l_text := l_text || 'color="#000000" size="2"><b>' || c_task_info_rec.task_number || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td>';
614 l_text := l_text || '</tr><tr><td height="3"></td><td></td><td></td></tr>';
615 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
616
617 --Task Name
618 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Task Name</font>';
619 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
620 l_text := l_text || 'color="#000000" size="2"><b>' || c_task_info_rec.task_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
621 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
622 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
623
624 -- Task Manager
625 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Task Manager</font></td>';
626 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
627 l_text := l_text || 'color="#000000" size="2"><b>' || l_mgr_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
628 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
629
630 --Transaction Start
631 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Transaction Start</font>';
632 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
633 l_text := l_text || 'color="#000000" size="2"><b>' || c_task_info_rec.scheduled_start_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
634 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
635 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
636
637 l_text := '</table></td><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><table border="0" cellspacing="0" cellpadding="0">';
638 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
639
640 --Project Number
641 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Number';
642 l_text := l_text || '</font></td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
643 l_text := l_text || 'color="#000000" size="2"><b>' || c_task_info_rec.project_number || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="5" /></font></td></tr><tr><td height="3">';
644 l_text := l_text || '</td><td></td><td></td></tr>';
645 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
646
647 --Project Name
648 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Project Name</font>';
649 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
650 l_text := l_text || 'color="#000000" size="2"><b>' || c_task_info_rec.project_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
651 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
652
653 --Organization
654 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Organization</font></td>';
655 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
656 l_text := l_text || 'color="#000000" size="2"><b>' || l_orgz_info_rec.organization_name || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
657 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
658 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
659
660 --Task finish date
661 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Transaction End</font></td>';
662 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
663 l_text := l_text || 'size="2"><b>' || c_task_info_rec.scheduled_end_date || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
664 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
665
666 l_text := '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
667 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
668
669 -- END : Task Information Section
670
671 IF p_cd_yn = 'Y' THEN
672
673 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td height="10"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
674 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
675
676 --Heading
677 l_text := '<tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);"><tr>';
678 l_text := l_text || '<td width="100%"><h2 valign="middle" marginheight="0" style="padding:0px 0px 0px 8px;margin:5px 0px 0px 0px;margin-top:1px;margin-bottom:0px;border-bottom:1px solid #aabed5">';
679 l_text := l_text || '<font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>Change Document Information</b></font></h2></td></tr></table></td></tr>';
680 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
681
682 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td bgcolor="#EAEFF5">';
683 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top">';
684 l_text := l_text || '<table border="0" cellspacing="0" cellpadding="0">';
685 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
686
687 --Summary
688 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Summary</font></td>';
689 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
690 l_text := l_text || 'size="2"><b>' || c_ci_info_rec.summary || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3">';
691 l_text := l_text || '</td><td></td><td></td></tr>';
692 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
693
694 --Description
695 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Description</font>';
696 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
697 l_text := l_text || 'size="2"><b>' || c_ci_info_rec.description || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr><td height="3">';
698 l_text := l_text || '</td><td></td><td></td></tr>';
699 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
700
701 IF c_ci_info_rec.ci_type_class_code = 'CHANGE_ORDER' THEN
702 l_ci_type_class_code := 'Change Order';
703 ELSE
704 l_ci_type_class_code := 'Change Request';
705 END IF;
706
707 -- Document Type
708 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Change Document Type</font>';
709 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
710 l_text := l_text || 'size="2"><b>' || l_ci_type_class_code || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr>';
711 l_text := l_text || '<tr><td height="3"></td><td></td><td></td></tr>';
712 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
713
714 --Number
715 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Number</font></td>';
716 l_text := l_text || '<td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" ';
717 l_text := l_text || 'size="2"><b>' || c_ci_info_rec.ci_number|| '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" /></font></td></tr><tr>';
718 l_text := l_text || '<td height="3"></td><td></td><td></td></tr>';
719 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
720
721 l_text := '</table></td><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><table border="0" cellspacing="0" cellpadding="0">';
722 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
723
724 --System Number
725 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">System Number</font>';
726 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
727 l_text := l_text || 'color="#000000" size="2"><b>' || to_char(c_ci_info_rec.ci_id) || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
728 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
729
730 --Created By
731 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Created By</font>';
732 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
733 l_text := l_text || 'color="#000000" size="2"><b>' || l_ci_created_by || '</b></font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
734 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
735
736 --Creation Date
737 l_text := '<tr><td align="right" valign="top" nowrap="nowrap"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">Creation Date</font>';
738 l_text := l_text || '</td><td width="12"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" ';
739 l_text := l_text || 'color="#000000" size="2"><b>' || to_char(c_ci_info_rec.creation_date,'DD-MON-YYYY') || '</b><img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="15" />';
740 l_text := l_text || '</font></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
741 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
742
743 --This cell is Empty
744 l_text := '<tr><td height="3"></td><td></td><td></td></tr><tr><td height="3"></td><td></td><td></td></tr>';
745 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
746
747 l_text := '</table></td></tr></table></td></tr></table></td></tr><tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
748 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
749
750 END IF;
751
752 --START : References Section
753 l_text := '<table cellpadding="0" cellspacing="0" border= "0" width="100%"><tr><td height="10"><img src="/OA_HTML/cabo/images/swan/t.gif" /></td></tr>';
754 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
755
756 --Header
757 l_text := '<tr><td><table cellpadding="0" cellspacing="0" border="0" width="100%" style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gif);">';
758 l_text := l_text || '<tr><td width="100%"><h2 valign="middle" marginheight="0" style="padding:0px 0px 0px 8px;margin:5px 0px 0px 0px;margin-top:1px;margin-bottom:0px;border-bottom:1px ';
759 l_text := l_text || 'solid #aabed5"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#3C3C3C" size="2"><b>References</b></font></h2></td></tr></table></td></tr>';
760 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
761
762 --URL Section to view change order request
763 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr><tr><td> <div><div><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr>';
764 l_text := l_text || '<td bgcolor="#EAEFF5"><table border="0" cellspacing="0" cellpadding="0"><tr><td width="5%"><img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td>';
765 l_text := l_text || '<td valign="top"><table border="0" cellspacing="0" cellpadding="0"><tr><td align="right" valign="top" nowrap="nowrap"><span align="right">';
766 l_text := l_text || '<img src="/OA_MEDIA/fwkhp_formsfunc.gif" alt="Change Document" width="16" height="16" border="0"></span></td><td width="12">';
767 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" width="12" /></td><td valign="top"><font face="Tahoma,Arial,Helvetica,Geneva,sans-serif" color="#000000" size="2">';
768 l_text := l_text || '<a href="OA.jsp?_rc=PA_CI_CI_REVIEW_LAYOUT&addBreadCrumb=RP&_ri=275&paProjectId=' || p_project_id || '&paCiId=' ||c_ci_info_rec.ci_id|| '&paCITypeClassCode='||c_ci_info_rec.ci_type_class_code||'">Change Document </a>';
769 l_text := l_text || '<img src="/OA_HTML/cabo/images/swan/t.gif" alt="" width="5" /></font></td></tr><tr>';
770 l_text := l_text || '<td height="3"></td><td></td><td></td></tr></table></tr></table></td></tr></table></td></tr>';
771 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
772
773 l_text := '<tr><td height="8" bgcolor="#EAEFF5"></td></tr></table>';
774 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
775 --END : References Section
776
777 --Closing the page content
778 l_text := '</td></tr></table>';
779 APPEND_VARCHAR_TO_CLOB(l_text, l_clob);
780
781 COMMIT;
782 l_text := '';
783
784 EXCEPTION
785 WHEN OTHERS THEN
786 RAISE;
787 END Generate_Task_Aprv_Notify;
788
789 -- This is being called from the workflow to display the notification generated in html format
790 PROCEDURE SHOW_TASK_NOTIFY_PREVIEW(document_id IN VARCHAR2
791 ,display_type IN VARCHAR2
792 ,document IN OUT NOCOPY CLOB
793 ,document_type IN OUT NOCOPY VARCHAR2) IS
794
795 l_content CLOB;
796
797 CURSOR c_pwp_preview_info IS
798 SELECT page_content
799 FROM PA_PAGE_CONTENTS
800 WHERE page_content_id =document_id
801 AND object_type = 'PA_TASK_APPROVAL_WF'
802 AND pk2_value IS NULL;
803
804 l_size number;
805 l_chunk_size PLS_INTEGER:=10000;
806 l_copy_size INT;
807 l_pos INT := 0;
808 l_line VARCHAR2(30000) := '';
809 l_return_status VARCHAR2(1);
810 l_msg_count NUMBER;
811 l_msg_data VARCHAR2(2000);
812
813
814 BEGIN
815
816 log_message('Inside the procedure SHOW_TASK_NOTIFY_PREVIEW',3);
817
818 OPEN c_pwp_preview_info;
819 FETCH c_pwp_preview_info INTO l_content;
820 IF (c_pwp_preview_info%FOUND) THEN
821 IF c_pwp_preview_info%ISOPEN THEN
822 CLOSE c_pwp_preview_info;
823 END IF;
824 l_size := dbms_lob.getlength(l_content);
825 l_pos := 1;
826 l_copy_size := 0;
827 WHILE (l_copy_size < l_size) LOOP
828 DBMS_LOB.READ(l_content,l_chunk_size,l_pos,l_line);
829 DBMS_LOB.WRITE(document,l_chunk_size,l_pos,l_line);
830 l_copy_size := l_copy_size + l_chunk_size;
831 l_pos := l_pos + l_chunk_size;
832 END LOOP;
833
834 log_message('Before calling PA_WORKFLOW_UTILS.modify_wf_clob_content',3);
835
836 PA_WORKFLOW_UTILS.modify_wf_clob_content(p_document => document
837 ,x_return_status => l_return_status
838 ,x_msg_count => l_msg_count
839 ,x_msg_data => l_msg_data);
840
841 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
842 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
843 DBMS_LOB.writeappend(document, 255, SUBSTR(l_msg_data, 255));
844 END IF;
845 ELSE
846 IF c_pwp_preview_info%ISOPEN THEN
847 CLOSE c_pwp_preview_info;
848 END IF;
849 END IF;
850
851 document_type := 'text/html';
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
856 dbms_lob.writeappend(document, 255, substrb('Testing', 255));
857 NULL;
858 END SHOW_TASK_NOTIFY_PREVIEW;
859
860 -- Procedure to add content to the CLOB column
861 PROCEDURE APPEND_VARCHAR_TO_CLOB(p_varchar IN varchar2
862 ,p_clob IN OUT NOCOPY CLOB) IS
863
864 l_chunkSize INTEGER;
865 v_offset INTEGER := 0;
866 l_clob clob;
867 l_length INTEGER;
868
869 v_size NUMBER;
870 v_text VARCHAR2(3000);
871
872 BEGIN
873
874 l_chunksize := length(p_varchar);
875 l_length := dbms_lob.getlength(p_clob);
876
877 DBMS_LOB.write(p_clob
878 ,l_chunksize
879 ,l_length+1
880 ,p_varchar);
881 v_size := 1000;
882 DBMS_LOB.read(p_clob, v_size, 1, v_text);
883
884 END APPEND_VARCHAR_TO_CLOB;
885
886 FUNCTION show_error(p_error_stack IN VARCHAR2,
887 p_error_stage IN VARCHAR2,
888 p_error_message IN VARCHAR2,
889 p_arg1 IN VARCHAR2 DEFAULT null,
890 p_arg2 IN VARCHAR2 DEFAULT null) RETURN VARCHAR2 IS
891
892 l_result FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
893
894 BEGIN
895 g_error_message := nvl(p_error_message,SUBSTRB(SQLERRM,1,1000));
896
897 fnd_message.set_name('PA','PA_WF_FATAL_ERROR');
898 fnd_message.set_token('ERROR_STACK',p_error_stack);
899 fnd_message.set_token('ERROR_STAGE',p_error_stage);
900 fnd_message.set_token('ERROR_MESSAGE',g_error_message);
901 fnd_message.set_token('ERROR_ARG1',p_arg1);
902 fnd_message.set_token('ERROR_ARG2',p_arg2);
903
904 l_result := fnd_message.get_encoded;
905
906 g_error_message := NULL;
907
908 RETURN l_result;
909 EXCEPTION WHEN OTHERS
910 THEN
911 raise;
912 END show_error;
913
914 -- This is to verify if the submitted task is root task or a child task
915 PROCEDURE Is_Child_Task (itemtype IN VARCHAR2
916 ,itemkey IN VARCHAR2
917 ,actid IN NUMBER
918 ,funcmode IN VARCHAR2
919 ,resultout OUT NOCOPY VARCHAR2) IS
920
921 l_project_id NUMBER;
922 l_proj_element NUMBER;
923 l_parent_struc_ver NUMBER;
924 l_msg_count NUMBER;
925 l_msg_data VARCHAR2(4000);
926 l_return_status VARCHAR2(1);
927 l_proj_org NUMBER;
928 l_ci_id NUMBER;
929 l_content_id NUMBER;
930 BEGIN
931
932 log_message('Inside Is_Child_Task',3);
933
934 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
935 ,itemkey => itemkey
936 ,aname => 'PROJECT_ID');
937 l_proj_element := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
938 ,itemkey => itemkey
939 ,aname => 'TASK_ID');
940 l_parent_struc_ver := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
941 ,itemkey => itemkey
942 ,aname => 'PARENT_STRUC_VER_ID');
943 l_proj_org := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
944 ,itemkey => itemkey
945 ,aname => 'PROJ_ORG_ID');
946 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
947 ,itemkey => itemkey
948 ,aname => 'CI_ID');
949
950 log_message('Before calling PA_TASK_APPROVAL_PKG.Is_Child_Task',3);
951
952 IF PA_TASK_APPROVAL_PKG.Is_Child_Task (
953 l_project_id
954 ,l_proj_element
955 ,l_parent_struc_ver
956 ,l_msg_count
957 ,l_msg_data
958 ,l_return_status ) THEN
959 log_message('Task '||l_proj_element||' is a child task',3);
960 resultout := wf_engine.eng_completed||':'||'T';
961 ELSE
962 log_message('Generating notification for task approval',3);
963 Generate_Task_Aprv_Notify(p_item_type => itemtype
964 ,p_item_key => itemkey
965 ,p_task_id => l_proj_element
966 ,p_project_id => l_project_id
967 ,p_org_id => l_proj_org
968 ,p_parent_struc_ver => l_parent_struc_ver
969 ,p_ci_id => l_ci_id
970 ,x_content_id => l_content_id
971 );
972
973 IF l_content_id IS NOT NULL THEN
974 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
975 ,itemkey => itemkey
976 ,aname => 'CONTENT_ID'
977 ,avalue => l_content_id
978 );
979 END IF;
980
981 UPDATE PA_PROJ_ELEMENTS
982 SET task_status = 'SUBMITTED'
983 WHERE proj_element_id = l_proj_element;
984
985 resultout := wf_engine.eng_completed||':'||'F';
986 log_message('Task '||l_proj_element||' is a top/root task',3);
987 END IF;
988 EXCEPTION
989 WHEN FND_API.G_EXC_ERROR THEN
990 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Child_Task',itemtype, itemkey, to_char(actid), funcmode);
991 RAISE;
992
993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
994 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Child_Task',itemtype, itemkey, to_char(actid), funcmode);
995 RAISE;
996
997 WHEN OTHERS THEN
998 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Child_Task',itemtype, itemkey, to_char(actid), funcmode);
999 RAISE;
1000 END Is_Child_Task;
1001
1002 -- If the task, which is submitted for Approval is a child task, this procedure verifies if its parent
1003 -- task is approved or not. If the parent task is not approved, it will send a notification to the
1004 PROCEDURE Is_Parent_Task_Approved
1005 (itemtype IN VARCHAR2
1006 ,itemkey IN VARCHAR2
1007 ,actid IN NUMBER
1008 ,funcmode IN VARCHAR2
1009 ,resultout OUT NOCOPY VARCHAR2) IS
1010 l_project_id NUMBER;
1011 l_proj_element NUMBER;
1012 l_parent_task_id NUMBER;
1013 l_parent_struc_ver NUMBER;
1014 l_msg_count NUMBER;
1015 l_msg_data VARCHAR2(4000);
1016 l_return_status VARCHAR2(1);
1017 l_proj_org NUMBER;
1018 l_content_id NUMBER;
1019 l_ci_id NUMBER;
1020 BEGIN
1021 log_message('Inside the procedure Is_Parent_Task_Approved',3);
1022
1023 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1024 ,itemkey => itemkey
1025 ,aname => 'PROJECT_ID');
1026
1027 l_proj_element := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1028 ,itemkey => itemkey
1029 ,aname => 'TASK_ID');
1030
1031 l_parent_task_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1032 ,itemkey => itemkey
1033 ,aname => 'PARENT_TASK_ID');
1034 l_parent_struc_ver := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1035 ,itemkey => itemkey
1036 ,aname => 'PARENT_STRUC_VER_ID');
1037
1038 l_proj_org := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1039 ,itemkey => itemkey
1040 ,aname => 'PROJ_ORG_ID');
1041
1042 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1043 ,itemkey => itemkey
1044 ,aname => 'CI_ID');
1045
1046 IF PA_TASK_APPROVAL_PKG.Is_Parent_Task_Approved (
1047 l_project_id
1048 ,l_parent_task_id
1049 ,l_proj_element
1050 ,l_parent_struc_ver
1051 ,l_msg_count
1052 ,l_msg_data
1053 ,l_return_status ) THEN
1054
1055 Generate_Task_Aprv_Notify(p_item_type => itemtype
1056 ,p_item_key => itemkey
1057 ,p_task_id => l_proj_element
1058 ,p_project_id => l_project_id
1059 ,p_org_id => l_proj_org
1060 ,p_parent_struc_ver => l_parent_struc_ver
1061 ,p_ci_id => l_ci_id
1062 ,x_content_id => l_content_id
1063 );
1064 IF l_content_id IS NOT NULL THEN
1065 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1066 ,itemkey => itemkey
1067 ,aname => 'CONTENT_ID'
1068 ,avalue => l_content_id
1069 );
1070 END IF;
1071
1072 log_message('Parent Task '||l_parent_task_id||' of task '||l_proj_element||' is approved',3);
1073 log_message('Updating the task status to Submitted',3);
1074
1075 UPDATE PA_PROJ_ELEMENTS
1076 SET task_status = 'SUBMITTED'
1077 WHERE proj_element_id = l_proj_element;
1078
1079 resultout := wf_engine.eng_completed||':'||'T';
1080 ELSE
1081 log_message('Parent Task '||l_parent_task_id||' of task '||l_proj_element||' is not yet approved',3);
1082 resultout := wf_engine.eng_completed||':'||'F';
1083 END IF;
1084 EXCEPTION
1085 WHEN FND_API.G_EXC_ERROR THEN
1086 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Parent_Task_Approved',itemtype, itemkey, to_char(actid), funcmode);
1087 RAISE;
1088
1089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Parent_Task_Approved',itemtype, itemkey, to_char(actid), funcmode);
1091 RAISE;
1092
1093 WHEN OTHERS THEN
1094 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Parent_Task_Approved',itemtype, itemkey, to_char(actid), funcmode);
1095 RAISE;
1096 END Is_Parent_Task_Approved;
1097
1098 -- This procedure calls PA_TASKS_MAINT_PUB.CREATE_TASK API that populates pa_tasks.
1099 -- This is being called from Task approval workflow on approving the task from Approval Notification.
1100 PROCEDURE Post_Task (itemtype IN VARCHAR2
1101 ,itemkey IN VARCHAR2
1102 ,actid IN NUMBER
1103 ,funcmode IN VARCHAR2
1104 ,resultout OUT NOCOPY VARCHAR2) IS
1105
1106 l_project_id NUMBER;
1107 l_parent_task_id NUMBER;
1108 l_task_number VARCHAR2(30);
1109 l_task_name VARCHAR2(240);
1110 l_task_st_date DATE;
1111 l_task_end_date DATE;
1112
1113 l_task_id NUMBER;
1114 l_org_id NUMBER;
1115 l_ci_id NUMBER;
1116 x_return_status VARCHAR2(1);
1117 x_msg_count NUMBER;
1118 x_msg_data VARCHAR2(4000);
1119
1120 l_result VARCHAR2(30);
1121
1122 l_billable_flag VARCHAR2(1);
1123 l_chargeable_flag VARCHAR2(1);
1124 x_err_stack VARCHAR2(2000);
1125 x_err_stage VARCHAR2(100);
1126 x_err_code NUMBER;
1127 l_item_key pa_wf_processes.item_key%TYPE;
1128
1129 -- Cursor to fetch the element version id and parent structure version id for
1130 -- a specific task.
1131 CURSOR C1(p_proj_elemt_id NUMBER) IS
1132 SELECT ppe.proj_element_id,
1133 ppev.element_version_id,
1134 ppev.parent_structure_version_id
1135 FROM PA_PROJ_ELEMENTS ppe,
1136 PA_PROJ_ELEMENT_VERSIONS ppev
1137 WHERE ppev.proj_element_id = ppe.proj_element_id
1138 AND ppe.proj_element_id = p_proj_elemt_id;
1139
1140 -- Cursor to find out all the immediate child tasks of a task which are in
1141 -- PENDING status.
1142 CURSOR C2(p_element_version_id NUMBER,p_project_id NUMBER) IS
1143 SELECT ppe.proj_element_Id task_id,
1144 ppe.record_version_number,
1145 ppev.parent_structure_version_id,
1146 ppe.task_approver_id task_app_chg_id
1147 FROM PA_PROJ_ELEMENTS PPE, PA_OBJECT_RELATIONSHIPS POR, PA_PROJ_ELEMENT_VERSIONS PPEV
1148 WHERE ppe.project_id = p_project_id
1149 AND ppev.proj_element_id = ppe.proj_element_id
1150 AND ppev.financial_task_flag = 'Y'
1151 AND por.object_id_to1 = ppev.element_version_id
1152 AND por.relationship_type = 'S'
1153 AND por.relationship_subtype = 'TASK_TO_TASK'
1154 AND por.object_id_from1 = p_element_version_id
1155 AND ppe.link_task_flag = 'Y'
1156 AND ppe.task_status ='PENDING';
1157
1158 -- Cursor is to pickup all the Change Documents that have referred this TASK
1159 -- and are in SUBMITTED status.
1160 CURSOR C3(p_project_id NUMBER, p_task_id NUMBER) IS
1161 SELECT pci.ci_id,
1162 pcia.ci_action_id action_id
1163 FROM pa_control_items pci, pa_ci_actions pcia
1164 WHERE pci.project_id = p_project_id
1165 AND pcia.ci_id(+) = pci.ci_id
1166 AND pcia.ci_action_number(+) = pci.open_action_num
1167 AND EXISTS (SELECT 1 FROM pa_budget_versions pbv, pa_resource_assignments pra
1168 WHERE pbv.project_id = pci.project_Id
1169 AND pbv.ci_id = pci.ci_id
1170 AND pra.budget_version_id = pbv.budget_version_id
1171 AND pra.project_id = p_project_id
1172 AND pra.task_id = p_task_id)
1173 AND pci.status_code in ('CI_SUBMITTED');
1174
1175
1176 BEGIN
1177
1178 log_message('Inside procedure POST_TASK ',3);
1179 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1180 ,itemkey => itemkey
1181 ,aname => 'PROJECT_ID');
1182 l_parent_task_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1183 ,itemkey => itemkey
1184 ,aname => 'PARENT_TASK_ID');
1185 l_task_number := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1186 ,itemkey => itemkey
1187 ,aname => 'TASK_NUMBER');
1188 l_task_name := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1189 ,itemkey => itemkey
1190 ,aname => 'TASK_NAME');
1191 l_task_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1192 ,itemkey => itemkey
1193 ,aname => 'TASK_ID');
1194 l_task_st_date := WF_ENGINE.GetItemAttrDate(itemtype => itemtype
1195 ,itemkey => itemkey
1196 ,aname => 'TASK_ST_DATE');
1197 l_task_end_date := WF_ENGINE.GetItemAttrDate(itemtype => itemtype
1198 ,itemkey => itemkey
1199 ,aname => 'TASK_END_DATE');
1200 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1201 ,itemkey => itemkey
1202 ,aname => 'PROJ_ORG_ID');
1203 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1204 ,itemkey => itemkey
1205 ,aname => 'CI_ID');
1206 l_result := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1207 ,itemkey => itemkey
1208 ,aname => 'RESULT');
1209
1210
1211 IF l_parent_task_id = l_task_id THEN
1212 l_parent_task_id := '';
1213 END IF;
1214
1215 --b6694902_debug.debug('l_result '||l_result);
1216 log_message('l_result ['||l_result||']',3);
1217 /* l_result :
1218 CANDB -> Chargeable and Billable
1219 C -> Chargeable only
1220 B -> Billable only
1221 */
1222 IF l_result = 'CANDB' THEN
1223 l_billable_flag := 'Y';
1224 l_chargeable_flag := 'Y';
1225 ELSIF l_result = 'C' THEN
1226 l_billable_flag := 'N';
1227 l_chargeable_flag := 'Y';
1228 ELSIF l_result = 'B' THEN
1229 l_billable_flag := 'Y';
1230 l_chargeable_flag := 'N';
1231 END IF;
1232
1233 log_message('Before calling PA_TASKS_MAINT_PUB.CREATE_TASK',3);
1234 PA_TASKS_MAINT_PUB.CREATE_TASK
1235 (
1236 p_calling_module => 'SELF_SERVICE'
1237 ,p_init_msg_list => FND_API.G_FALSE
1238 ,p_debug_mode => 'Y'
1239 ,p_project_id => l_project_id
1240 ,p_reference_task_id => l_parent_task_id
1241 ,p_peer_or_sub => 'SUB'
1242 ,p_task_number => l_task_number
1243 ,p_task_name => l_task_name
1244 ,p_task_id => l_task_id
1245 ,p_chargeable_flag => l_chargeable_flag
1246 ,p_billable_flag => l_billable_flag
1247 ,p_task_start_date => l_task_st_date
1248 ,p_task_completion_date => l_task_end_date
1249 ,p_wbs_record_version_number => 1
1250 ,p_carrying_out_organization_id => l_org_id
1251 ,x_return_status =>x_return_status
1252 ,x_msg_count =>x_msg_count
1253 ,x_msg_data =>x_msg_data
1254 );
1255 --b6694902_debug.debug('Inside POST_TASK for task '||l_task_id||' Status:'||x_return_status);
1256 --b6694902_debug.debug('Inside POST_TASK for task '||l_task_id||' x_msg_data:'||x_msg_data);
1257
1258 IF x_return_status <> 'S' THEN
1259 log_message('Call to PA_TASKS_MAINT_PUB.CREATE_TASK is errored out',3);
1260 resultout := wf_engine.eng_completed||':'||'F';
1261 ELSE
1262 log_message('Create Task is successful',3);
1263 UPDATE PA_PROJ_ELEMENTS SET link_task_flag = 'N', task_status = ''
1264 WHERE proj_element_id = l_task_id;
1265
1266 log_message('Raising notification for all child tasks in pending status',3);
1267 -- This is to raise notification for all child tasks which are in submitted status when the parent task is approved.
1268 FOR parent_task IN C1(l_task_id) LOOP
1269 FOR child_task IN C2(parent_task.element_version_id, l_project_id) LOOP
1270
1271 PA_TASK_WORKFLOW_PKG.Start_Task_Aprv_Wf (
1272 'PATASKWF'
1273 ,'PA_TASK_APPROVAL_WF'
1274 ,l_project_id
1275 ,child_task.task_id
1276 ,child_task.parent_structure_version_id
1277 ,child_task.task_app_chg_id
1278 ,l_ci_id
1279 ,x_err_stack
1280 ,x_err_stage
1281 ,x_err_code
1282 );
1283 END LOOP;
1284
1285 -- This is to raise notification for all Change Documents which are in CI_SUBMITTED status
1286 -- We raise notification for CD only in case if the task submitted is the last task which is referred in this CD.
1287 log_message('Raising notification for all Change Documents in Submitted status',3);
1288 FOR ci_info IN C3(l_project_id, l_task_id) LOOP
1289
1290 PA_TASK_APPROVAL_PKG.Check_UsedTask_Status
1291 (ci_info.ci_id
1292 ,x_msg_count
1293 ,x_msg_data
1294 ,x_return_status);
1295
1296 IF x_return_status = 'S' THEN
1297 /*PA_CONTROL_ITEMS_WORKFLOW.START_NOTIFICATION_WF
1298 ( p_item_type => 'PAWFCISC'
1299 ,p_process_name => 'PA_CI_PROCESS_APPROVAL'
1300 ,p_ci_id => ci_info.ci_id
1301 ,p_action_id => ci_info.action_id
1302 ,x_item_key => l_item_key
1303 ,x_return_status => x_return_status
1304 ,x_msg_count => x_msg_count
1305 ,x_msg_data => x_msg_data );*/
1306 PA_CONTROL_ITEMS_WORKFLOW.start_workflow
1307 (p_item_type => 'PAWFCISC'
1308 ,p_process_name => 'PA_CI_PROCESS_APPROVAL'
1309 ,p_ci_id => ci_info.ci_id
1310 ,x_item_key => l_item_key
1311 ,x_return_status => x_return_status
1312 ,x_msg_count => x_msg_count
1313 ,x_msg_data => x_msg_data
1314 );
1315 END IF;
1316
1317 END LOOP;
1318
1319 END LOOP;
1320 resultout := wf_engine.eng_completed||':'||'T';
1321 END IF;
1322 EXCEPTION
1323 WHEN FND_API.G_EXC_ERROR THEN
1324 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Post_Task',itemtype, itemkey, to_char(actid), funcmode);
1325 RAISE;
1326
1327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1328 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Post_Task',itemtype, itemkey, to_char(actid), funcmode);
1329 RAISE;
1330
1331 WHEN OTHERS THEN
1332 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Post_Task',itemtype, itemkey, to_char(actid), funcmode);
1333 RAISE;
1334 END Post_Task;
1335
1336 -- This is to update the task status to 'Pending' in case if the submitted task has
1337 -- unapproved parent task.
1338
1339 PROCEDURE Update_Task_Status(itemtype IN VARCHAR2
1340 ,itemkey IN VARCHAR2
1341 ,actid IN NUMBER
1342 ,funcmode IN VARCHAR2
1343 ,resultout OUT NOCOPY VARCHAR2) IS
1344
1345 l_aprv_user_id NUMBER;
1346 l_proj_element_id NUMBER;
1347 l_ci_id NUMBER;
1348 l_project_id NUMBER;
1349 l_parent_struc_ver NUMBER;
1350 l_task_id NUMBER;
1351 l_org_id NUMBER;
1352 x_return_status VARCHAR2(1);
1353 x_msg_count NUMBER;
1354 x_msg_data VARCHAR2(4000);
1355 l_content_id NUMBER;
1356 BEGIN
1357 log_message('Inside Update_Task_Status Procedure',3);
1358
1359 log_message('Fetching all the workflow attribute values required',3);
1360
1361 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1362 ,itemkey => itemkey
1363 ,aname => 'PROJECT_ID');
1364 l_task_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1365 ,itemkey => itemkey
1366 ,aname => 'TASK_ID');
1367 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1368 ,itemkey => itemkey
1369 ,aname => 'PROJ_ORG_ID');
1370
1371 l_parent_struc_ver := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1372 ,itemkey => itemkey
1373 ,aname => 'PARENT_STRUC_VER_ID');
1374
1375 l_aprv_user_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1376 ,itemkey => itemkey
1377 ,aname => 'TASK_APPROVER_ID');
1378
1379 l_proj_element_id := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1380 ,itemkey => itemkey
1381 ,aname => 'TASK_ID');
1382
1383 l_ci_id := WF_ENGINE.GetItemAttrText(itemtype => itemtype
1384 ,itemkey => itemkey
1385 ,aname => 'CI_ID');
1386
1387 log_message('Marking task status to pending for its parent tasks approval',3);
1388 UPDATE PA_PROJ_ELEMENTS SET task_status = 'PENDING',
1389 task_approver_id = l_aprv_user_id
1390 WHERE proj_element_id = l_proj_element_id;
1391
1392 log_message('Calling generate_task_aprv_notify to generate the notification',3);
1393 Generate_Task_Aprv_Notify(p_item_type => itemtype
1394 ,p_item_key => itemkey
1395 ,p_task_id => l_task_id
1396 ,p_project_id => l_project_id
1397 ,p_org_id => l_org_id
1398 ,p_parent_struc_ver => l_parent_struc_ver
1399 ,p_ci_id => l_ci_id
1400 ,p_cd_yn => 'N'
1401 ,x_content_id => l_content_id
1402 );
1403 IF l_content_id IS NOT NULL THEN
1404 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1405 ,itemkey => itemkey
1406 ,aname => 'CONTENT_ID'
1407 ,avalue => l_content_id
1408 );
1409 END IF;
1410 EXCEPTION
1411 WHEN FND_API.G_EXC_ERROR THEN
1412 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Update_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
1413 RAISE;
1414
1415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1416 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Update_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
1417 RAISE;
1418
1419 WHEN OTHERS THEN
1420 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Update_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
1421 RAISE;
1422
1423 END Update_Task_Status;
1424
1425 -- This procedure is to delete all the references of the submitted task when it is rejected by the
1426 -- approver.
1427
1428 PROCEDURE Delete_Task(itemtype IN VARCHAR2
1429 ,itemkey IN VARCHAR2
1430 ,actid IN NUMBER
1431 ,funcmode IN VARCHAR2
1432 ,resultout OUT NOCOPY VARCHAR2) IS
1433
1434 -- Cursor to fetch the element version id and parent structure version id of a given task
1435 CURSOR C1(p_proj_elemt_id NUMBER) IS
1436 SELECT ppe.proj_element_id,
1437 ppev.element_version_id,
1438 ppev.parent_structure_version_id
1439 FROM PA_PROJ_ELEMENTS ppe,
1440 PA_PROJ_ELEMENT_VERSIONS ppev
1441 WHERE ppev.proj_element_id = ppe.proj_element_id
1442 AND ppe.proj_element_id = p_proj_elemt_id;
1443
1444 -- Cursor to fetch the TASK provided along with all of it's child tasks which are
1445 -- in unapproved status.
1446 CURSOR C2 (p_project_id NUMBER, p_element_version_id NUMBER, p_task_id NUMBER) IS
1447 SELECT ppe.proj_element_Id task_id,
1448 ppe.record_version_number,
1449 ppev.parent_structure_version_id,
1450 ppev.element_version_id
1451 FROM PA_PROJ_ELEMENTS PPE,
1452 PA_PROJ_ELEMENT_VERSIONS PPEV
1453 WHERE ppe.project_id = p_project_id
1454 AND ppev.proj_element_id = ppe.proj_element_id
1455 AND ppev.financial_task_flag = 'Y'
1456 AND ppe.task_status IN ('NEW','SUBMITTED','PENDING')
1457 AND ppev.element_version_id in (
1458 SELECT object_id_to1
1459 FROM pa_object_relationships
1460 WHERE relationship_type = 'S'
1461 AND relationship_subtype = 'TASK_TO_TASK'
1462 START WITH object_id_from1 = p_element_version_id
1463 AND relationship_type = 'S'
1464 CONNECT BY object_id_from1 = PRIOR object_id_to1
1465 AND relationship_type = prior relationship_type AND relationship_type = 'S'
1466 ) UNION ALL
1467 SELECT ppe.proj_element_Id task_id,
1468 ppe.record_version_number,
1469 ppev.parent_structure_version_id,
1470 ppev.element_version_id
1471 FROM PA_PROJ_ELEMENTS PPE,
1472 PA_PROJ_ELEMENT_VERSIONS PPEV
1473 WHERE ppe.project_id = p_project_id
1474 AND ppe.proj_element_id = ppev.proj_element_id
1475 AND ppe.proj_element_id =p_task_id;
1476
1477 l_ci_id NUMBER;
1478 l_proj_element_id NUMBER;
1479 l_project_id NUMBER;
1480 l_parent_struc_ver NUMBER;
1481 l_task_id NUMBER;
1482 x_return_status VARCHAR2(1);
1483 x_msg_count NUMBER;
1484 x_msg_data VARCHAR2(4000);
1485 l_loop_ctr NUMBER :=1;
1486 l_aprv_user_id NUMBER;
1487 BEGIN
1488
1489 log_message('Inside Delete_Task Procedure',3);
1490
1491 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1492 ,itemkey => itemkey
1493 ,aname => 'CI_ID');
1494 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1495 ,itemkey => itemkey
1496 ,aname => 'PROJECT_ID');
1497 l_task_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1498 ,itemkey => itemkey
1499 ,aname => 'TASK_ID');
1500
1501 l_aprv_user_id := WF_ENGINE.GetItemAttrNumber
1502 (itemtype => itemtype
1503 ,itemkey => itemkey
1504 ,aname => 'TASK_APPROVER_ID'
1505 );
1506 g_del_taskrec.delete;
1507 log_message('Storing the task details into pl/sql table and we delete the data
1508 furtherly in is_last_task procedure',3);
1509 FOR parent_task IN C1(l_task_id) LOOP
1510 FOR child_task IN C2(l_project_id, parent_task.element_version_id, l_task_id) LOOP
1511 --IF child_task.task_id <> l_task_id THEN
1512 g_del_taskrec(l_loop_ctr).project_id := l_project_id;
1513 g_del_taskrec(l_loop_ctr).task_id := child_task.task_id;
1514 g_del_taskrec(l_loop_ctr).elem_ver_id := child_task.element_version_id;
1515 g_del_taskrec(l_loop_ctr).rec_ver_num := child_task.record_version_number;
1516 g_del_taskrec(l_loop_ctr).parent_struc_ver :=child_task.parent_structure_version_id ;
1517 l_loop_ctr := l_loop_ctr +1;
1518 --END IF;
1519 END LOOP;
1520 END LOOP;
1521
1522 EXCEPTION
1523 WHEN FND_API.G_EXC_ERROR THEN
1524 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Delete_Task',itemtype, itemkey, to_char(actid), funcmode);
1525 RAISE;
1526
1527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1528 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Delete_Task',itemtype, itemkey, to_char(actid), funcmode);
1529 RAISE;
1530
1531 WHEN OTHERS THEN
1532 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Delete_Task',itemtype, itemkey, to_char(actid), funcmode);
1533 RAISE;
1534
1535 END Delete_Task;
1536
1537 -- This is to verify task status in change order workflow. If all tasks used in Change Document
1538 -- are approved we raise notification for Change document for its approval. If an unapproved task is
1539 -- used in multiple Change documents and if any of such Change document is submitted for approval,
1540 -- a notification will be raised for this task with respect to the change document submitted for approval.
1541
1542 PROCEDURE Verify_Task_Status
1543 (itemtype IN VARCHAR2
1544 ,itemkey IN VARCHAR2
1545 ,actid IN NUMBER
1546 ,funcmode IN VARCHAR2
1547 ,resultout OUT NOCOPY VARCHAR2) IS
1548
1549 l_ci_id NUMBER;
1550 l_project_id NUMBER;
1551 x_return_status VARCHAR2(1);
1552 x_msg_count NUMBER;
1553 x_msg_data VARCHAR2(4000);
1554 x_err_stack VARCHAR2(2000);
1555 x_err_stage VARCHAR2(100);
1556 x_err_code NUMBER;
1557
1558 -- Cursor to find out all unapproved tasks referred in one particular Change Document.
1559 CURSOR C1 IS
1560 Select distinct task_id from
1561 pa_resource_assignments pra where
1562 budget_version_id in (
1563 select budget_version_id from pa_budget_versions where ci_id = l_ci_id )
1564 and exists (select 1
1565 from pa_proj_elements ppe,
1566 pa_proj_element_versions ppev,
1567 pa_object_relationships por
1568 where ppe.proj_element_id = pra.task_id
1569 and ppe.project_id = pra.project_id
1570 and ppe.link_task_flag = 'Y'
1571 and ppe.type_id = 1
1572 and ppev.proj_element_id = ppe.proj_element_id
1573 and por.object_id_to1 = ppev.element_version_id
1574 and por.object_type_to = 'PA_TASKS'
1575 and por.relationship_type = 'S'
1576 and ppev.financial_task_flag = 'Y')
1577 and not exists (select 1 from pa_tasks where task_id = pra.task_id and project_id = pra.project_id);
1578
1579 l_unapproved_task_cnt NUMBER :=0;
1580 c_task_rec c_task_info%ROWTYPE;
1581 l_max_notification_id WF_NOTIFICATIONS.notification_id%TYPE;
1582 BEGIN
1583 log_message('Inside Verify_Task_Status',3);
1584 x_return_status := 'S';
1585
1586 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1587 ,itemkey => itemkey
1588 ,aname => 'CI_ID');
1589
1590 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1591 ,itemkey => itemkey
1592 ,aname => 'PROJECT_ID');
1593 log_message('Closing any earlier notifications of task and raising new notification',3);
1594 FOR TaskId in C1 LOOP
1595 l_unapproved_task_cnt := l_unapproved_task_cnt+1;
1596 l_max_notification_id := '';
1597
1598 OPEN c_task_info(l_project_id, TaskId.Task_Id);
1599 FETCH c_task_info INTO c_task_rec;
1600 CLOSE c_task_info;
1601
1602 IF c_task_rec.task_status = 'SUBMITTED' THEN
1603 BEGIN
1604 SELECT max(notification_id)
1605 INTO l_max_notification_id
1606 FROM WF_NOTIFICATIONS WFN
1607 WHERE message_type = 'PATASKWF'
1608 AND status = 'OPEN'
1609 AND EXISTS (
1610 SELECT 1
1611 FROM WF_NOTIFICATION_ATTRIBUTES
1612 WHERE notification_id = wfn.notification_id
1613 AND name = 'TASK_NUMBER'
1614 AND text_value like c_task_rec.task_number
1615 )
1616 AND EXISTS (
1617 SELECT 1
1618 FROM WF_NOTIFICATION_ATTRIBUTES
1619 WHERE notification_id = wfn.notification_id
1620 AND name = 'PROJECT_NUMBER'
1621 AND text_value like c_task_rec.project_number
1622 );
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 NULL;
1626 END;
1627 IF l_max_notification_id IS NOT NULL THEN
1628 UPDATE WF_NOTIFICATIONS
1629 SET status = 'CLOSED'
1630 WHERE notification_id = l_max_notification_id;
1631 PA_TASK_WORKFLOW_PKG.Start_Task_Aprv_Wf (
1632 'PATASKWF'
1633 ,'PA_TASK_APPROVAL_WF'
1634 ,l_project_id
1635 ,c_task_rec.task_id
1636 ,c_task_rec.parent_structure_version_id
1637 ,c_task_rec.task_app_chg_id
1638 ,l_ci_id
1639 ,x_err_stack
1640 ,x_err_stage
1641 ,x_err_code
1642 );
1643 END IF;
1644 END IF;
1645 END LOOP;
1646
1647 IF l_unapproved_task_cnt >0 THEN
1648 log_message('There are '||l_unapproved_task_cnt||' unapproved tasks for this Change Document',3);
1649 resultout := wf_engine.eng_completed||':'||'F';
1650 ELSE
1651 log_message('There is no task pending for Approval, which is used in this Change document',3);
1652 resultout := wf_engine.eng_completed||':'||'T';
1653 END IF;
1654 EXCEPTION
1655 WHEN FND_API.G_EXC_ERROR THEN
1656 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Verify_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
1657 RAISE;
1658
1659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Verify_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
1661 RAISE;
1662
1663 WHEN OTHERS THEN
1664 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Verify_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
1665 RAISE;
1666 END Verify_Task_Status;
1667
1668 -- In case the change document is submitted for approval, we mark the change document to 'CI_SUBMITTED'
1669 -- if any of the task referred in this change document is not yet approved.
1670 PROCEDURE Mark_CO_Status
1671 (itemtype IN VARCHAR2
1672 ,itemkey IN VARCHAR2
1673 ,actid IN NUMBER
1674 ,funcmode IN VARCHAR2
1675 ,resultout OUT NOCOPY VARCHAR2) IS
1676
1677 x_return_status VARCHAR2(1);
1678 x_msg_count NUMBER;
1679 x_msg_data VARCHAR2(4000);
1680 l_ci_id NUMBER;
1681 BEGIN
1682 log_message('Inside MARK_CO_STATUS',3);
1683 x_return_status := 'S';
1684
1685 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1686 ,itemkey => itemkey
1687 ,aname => 'CI_ID');
1688 log_message('Marking change document status to CI_SUBMITTED',3);
1689 PA_TASK_APPROVAL_PKG.Mark_CO_Status(
1690 l_ci_id
1691 ,x_msg_count
1692 ,x_msg_data
1693 ,x_return_status);
1694 IF x_return_status = 'E' THEN
1695 resultout := wf_engine.eng_completed||':'||'F';
1696 ELSE
1697 resultout := wf_engine.eng_completed||':'||'T';
1698 END IF;
1699 EXCEPTION
1700 WHEN FND_API.G_EXC_ERROR THEN
1701 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Mark_CO_Status',itemtype, itemkey, to_char(actid), funcmode);
1702 RAISE;
1703
1704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1705 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Mark_CO_Status',itemtype, itemkey, to_char(actid), funcmode);
1706 RAISE;
1707
1708 WHEN OTHERS THEN
1709 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Mark_CO_Status',itemtype, itemkey, to_char(actid), funcmode);
1710 RAISE;
1711 END Mark_CO_Status;
1712
1713 -- This procedure is being called from Task Approval workflow when the Approver chooses
1714 -- reject the task. We are supposed to delete all the child tasks of the task which is selected
1715 -- for deletion and raise a FYI notification to the user created the task.
1716
1717 PROCEDURE Is_Last_Task (itemtype IN VARCHAR2
1718 ,itemkey IN VARCHAR2
1719 ,actid IN NUMBER
1720 ,funcmode IN VARCHAR2
1721 ,resultout OUT NOCOPY VARCHAR2) IS
1722
1723 l_project_id NUMBER;
1724 l_proj_element NUMBER;
1725
1726 l_proj_org NUMBER;
1727 l_ci_id NUMBER;
1728 l_content_id NUMBER;
1729
1730 l_elem_ver_id NUMBER(15);
1731 l_rec_ver_num NUMBER(15);
1732 l_parent_struc_ver NUMBER(15);
1733
1734 l_user_full_name VARCHAR(400);
1735 l_user_name VARCHAR(240);
1736
1737 x_return_status VARCHAR2(1);
1738 x_msg_count NUMBER;
1739 x_msg_data VARCHAR2(4000);
1740
1741 CURSOR c_starter_name(l_starter_user_id NUMBER) IS
1742 SELECT user_name
1743 FROM FND_USER
1744 WHERE user_id = l_starter_user_id;
1745
1746 CURSOR c_starter_full_name(l_starter_user_id NUMBER) IS
1747 SELECT e.first_name||' '||e.last_name
1748 FROM FND_USER f, PER_ALL_PEOPLE_F e
1749 WHERE f.user_id = l_starter_user_id
1750 AND f.employee_id = e.person_id
1751 AND e.effective_end_date = ( SELECT MAX(papf.effective_end_date)
1752 FROM per_all_people_f papf
1753 WHERE papf.person_id = e.person_id);
1754 c_task_info_rec c_task_info%ROWTYPE;
1755 BEGIN
1756
1757 log_message('Inside Is_Last_Task Procedure ',3);
1758
1759 -- If the Pl/sql table has records, we proceed to delete the task data.
1760 IF g_del_taskrec.COUNT >0 THEN
1761
1762 log_message('Deleting the task :'||g_del_taskrec(g_del_taskrec.FIRST).task_id,3);
1763
1764 l_elem_ver_id := g_del_taskrec(g_del_taskrec.FIRST).elem_ver_id;
1765 l_rec_ver_num := g_del_taskrec(g_del_taskrec.FIRST).rec_ver_num;
1766 l_parent_struc_ver:= g_del_taskrec(g_del_taskrec.FIRST).parent_struc_ver;
1767
1768 -- Fetch Task Info
1769 OPEN c_task_info(g_del_taskrec(g_del_taskrec.FIRST).project_id,
1770 g_del_taskrec(g_del_taskrec.FIRST).task_id);
1771 FETCH c_task_info INTO c_task_info_rec;
1772 CLOSE c_task_info;
1773
1774 OPEN c_starter_name (c_task_info_rec.created_by);
1775 FETCH c_starter_name INTO l_user_name;
1776 CLOSE c_starter_name;
1777
1778 OPEN c_starter_full_name (c_task_info_rec.created_by);
1779 FETCH c_starter_full_name INTO l_user_full_name;
1780 CLOSE c_starter_full_name;
1781
1782 log_message('Setting the item attributes to reflect the respective task data',3);
1783 IF c_task_info_rec.parent_task_number IS NOT NULL THEN
1784 WF_ENGINE.SetItemAttrText (itemtype => itemtype
1785 ,itemkey => itemkey
1786 ,aname => 'PARENT_TASK_NUMBER'
1787 ,avalue => c_task_info_rec.parent_task_number
1788 );
1789 END IF;
1790
1791 IF c_task_info_rec.parent_task_id IS NOT NULL THEN
1792 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1793 ,itemkey => itemkey
1794 ,aname => 'PARENT_TASK_ID'
1795 ,avalue => c_task_info_rec.parent_task_id
1796 );
1797 END IF;
1798
1799 IF c_task_info_rec.task_number IS NOT NULL THEN
1800 WF_ENGINE.SetItemAttrText (itemtype => itemtype
1801 ,itemkey => itemkey
1802 ,aname => 'TASK_NUMBER'
1803 ,avalue => c_task_info_rec.task_number
1804 );
1805 END IF;
1806
1807 IF c_task_info_rec.task_name IS NOT NULL THEN
1808 WF_ENGINE.SetItemAttrText (itemtype => itemtype
1809 ,itemkey => itemkey
1810 ,aname => 'TASK_NAME'
1811 ,avalue => c_task_info_rec.task_number
1812 );
1813 END IF;
1814
1815 IF c_task_info_rec.task_id IS NOT NULL THEN
1816 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1817 ,itemkey => itemkey
1818 ,aname => 'TASK_ID'
1819 ,avalue => c_task_info_rec.task_id
1820 );
1821 END IF;
1822
1823 IF c_task_info_rec.created_by IS NOT NULL THEN
1824 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1825 ,itemkey => itemkey
1826 ,aname => 'SUBMITTED_BY_ID'
1827 ,avalue => c_task_info_rec.created_by
1828 );
1829 END IF;
1830
1831 IF l_user_full_name IS NOT NULL THEN
1832 WF_ENGINE.SetItemAttrText (itemtype => itemtype
1833 ,itemkey => itemkey
1834 ,aname => 'SUBMITTED_BY'
1835 ,avalue => l_user_full_name
1836 );
1837 END IF;
1838
1839 IF c_task_info_rec.created_by IS NOT NULL THEN
1840 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1841 ,itemkey => itemkey
1842 ,aname => 'WORKFLOW_STARTED_BY_ID'
1843 ,avalue => c_task_info_rec.created_by
1844 );
1845 END IF;
1846
1847 IF l_user_name IS NOT NULL THEN
1848 WF_ENGINE.SetItemAttrText (itemtype => itemtype
1849 ,itemkey => itemkey
1850 ,aname => 'WORKFLOW_STARTED_BY_NAME'
1851 ,avalue => l_user_name
1852 );
1853 END IF;
1854
1855 l_project_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1856 ,itemkey => itemkey
1857 ,aname => 'PROJECT_ID');
1858
1859 l_proj_org := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1860 ,itemkey => itemkey
1861 ,aname => 'PROJ_ORG_ID');
1862 l_ci_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
1863 ,itemkey => itemkey
1864 ,aname => 'CI_ID');
1865
1866 log_message('Calling generate_task_aprv_notify to generate notification',3);
1867 Generate_Task_Aprv_Notify(p_item_type => itemtype
1868 ,p_item_key => itemkey
1869 ,p_task_id => c_task_info_rec.task_id
1870 ,p_project_id => l_project_id
1871 ,p_org_id => l_proj_org
1872 ,p_parent_struc_ver => l_parent_struc_ver
1873 ,p_ci_id => l_ci_id
1874 ,x_content_id => l_content_id
1875 );
1876
1877 IF l_content_id IS NOT NULL THEN
1878 WF_ENGINE.SetItemAttrNumber (itemtype => itemtype
1879 ,itemkey => itemkey
1880 ,aname => 'CONTENT_ID'
1881 ,avalue => l_content_id
1882 );
1883 END IF;
1884
1885 DELETE FROM PA_RESOURCE_ASSIGNMENTS
1886 WHERE project_id = l_project_id
1887 AND task_id = c_task_info_rec.task_id;
1888
1889 log_message('Before calling PA_TASK_PUB1.Delete_Task_Version',3);
1890 PA_TASK_PUB1.Delete_Task_Version
1891 ( p_task_version_id => l_elem_ver_id
1892 ,p_record_version_number => l_rec_ver_num
1893 ,p_structure_version_id => l_parent_struc_ver
1894 ,x_return_status => x_return_status
1895 ,x_msg_count => x_msg_count
1896 ,x_msg_data => x_msg_data ) ;
1897
1898 DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
1899 WHERE element_version_id = l_elem_ver_id;
1900
1901 IF g_del_taskrec.COUNT > 0 THEN
1902 resultout := wf_engine.eng_completed||':'||'F';
1903 ELSE
1904 resultout := wf_engine.eng_completed||':'||'T';
1905 END IF;
1906 g_del_taskrec.delete(g_del_taskrec.FIRST,g_del_taskrec.FIRST);
1907 ELSE
1908 resultout := wf_engine.eng_completed||':'||'T';
1909 END IF;
1910 EXCEPTION
1911 WHEN FND_API.G_EXC_ERROR THEN
1912 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Last_Task',itemtype, itemkey, to_char(actid), funcmode);
1913 RAISE;
1914
1915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Last_Task',itemtype, itemkey, to_char(actid), funcmode);
1917 RAISE;
1918
1919 WHEN OTHERS THEN
1920 WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Is_Last_Task',itemtype, itemkey, to_char(actid), funcmode);
1921 RAISE;
1922 END Is_Last_Task;
1923
1924 END PA_TASK_WORKFLOW_PKG;