[Home] [Help]
PACKAGE BODY: APPS.PA_PROGRESS_REPORT_WORKFLOW
Source
1 package body pa_progress_report_WORKFLOW as
2 /* $Header: PAPRWFPB.pls 120.9.12000000.2 2007/04/26 17:17:16 vvjoshi ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8
9 FILE NAME : PAPRWFPB.pls
10 DESCRIPTION :
11 This file creates package procedures that are called to
12 execute each activity in the Progress Status Workflow.
13
14
15
16 HISTORY : 06/22/00 SYAO Initial Creation
17 17/02/04 sukhanna Bug : 3448380
18 Added check on assignment_type for CWK Changes.
19 31/05/04 sukhanna Removing the covering select clause in the
20 cursor definiton of get_name
21 16/06/04 sulkumar Bug 3629793: Commented code containing
22 summary_version_number.
23 27/08/04 sanantha Bug 3787169. call the api modify_wf_clob_content
24 06/09/04 smekala Bug 3848024. Stopping notifications to end dated users
25 28/09/04 smekala Bug 3905748 Closing the cursors.
26 09/02/05 rvelusam Bug 4165780 Changed attribute 'FORWARD_TO' to
27 FORWARD_TO_USERNAME_RESPONSE and changed the value set
28 for REPORT_APPROVER_USER_NAME.
29 08/05/05 raluthra Bug 4527617. Replaced fnd_user.customer_id with
30 fnd_user.person_party_id for R12 ATG Mandate fix.
31 08/05/05 raluthra Bug 4358517: Changed the definition of
32 l_org local variable from VARCHAR2(60) to
33 pa_project_lists_v.carrying_out_organization_name%TYPE
34 08/09/05 raluthra Bug 4565156. Added code for Manual NOCOPY Changes
35 for usage of same variable for In and Out parameter.
36 06/02/06 posingha Bug 4940945 Changed the query to base tables instead of view
37 to improve performance.
38 31/03/06 posingha Bug 5027098 Added code to set the 'From' role attribute
39 value for notifications.
40 19/04/06 sukhanna Bug 5173760. Did changes for swan UI. Changed these color codes
41 replaced #cccc99 with #cfe0f1
42 Replaced #336699 with #3c3c3c
43 Replaced #f7f7e7 with #f2f2f5
44 26/06/06 sukhanna Bug 5357187. Did changes for swan UI. Changed these color codes
45 replaced #cccc99 with #cfe0f1
46 Replaced #336699 with #3c3c3c
47 Replaced #f7f7e7 with #f2f2f5
48 26/04/07 vvjoshi Bug#5962401: Modified the expiration date for adhoc roles in
49 CreateAdhocRole procedure call.
50 =============================================================================*/
51
52 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
53
54
55 /********************************************************************
56 * Procedure : start_workflow
57 * Purpose :
58 *********************************************************************/
59 Procedure start_workflow
60 (
61 p_item_type IN VARCHAR2
62 , p_process_name IN VARCHAR2
63
64 , p_version_id IN NUMBER
65
66 , x_item_key out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
67 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
68 , x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
69 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
70 )
71 IS
72
73 l_project_name VARCHAR2(30);
74
75 l_item_key NUMBER;
76
77 l_task_num VARCHAR2(25);
78 l_task_name VARCHAR2(20);
79 l_person_id NUMBER;
80 l_name VARCHAR2(250);
81 l_status_code VARCHAR2(30);
82 l_approval_role varchar2(30) := NULL;
83 l_role_users varchar2(30000) := NULL;
84 l_last_item_key NUMBER;
85
86 CURSOR get_last_workflow_info
87 IS
88 SELECT MAX(item_key)
89 FROM pa_wf_processes, pa_progress_report_vers pprv
90 WHERE item_type = p_item_type
91 AND description = p_process_name
92 AND pprv.version_id = p_version_id
93 AND entity_key1 = pprv.object_id
94 AND pprv.object_type = 'PA_PROJECTS'
95 AND wf_type_code = 'Progress Report'
96 AND entity_key2 = p_version_id;
97
98 BEGIN
99
100 SELECT pa_workflow_itemkey_s.nextval
101 INTO l_item_key
102 from dual;
103
104 x_item_key := To_char(l_item_key);
105
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108
109 --debug_msg ( 'before WF_ENGINE createProcess: ' || p_Process_Name);
110
111 -- create the workflow process
112 WF_ENGINE.CreateProcess( p_item_type
113 , x_item_key
114 , p_Process_Name);
115 --debug_msg ( 'after WF_ENGINE createProcess: key = ' || x_item_key);
116
117 pa_report_workflow_client.start_workflow(
118 p_item_type
119 , p_process_name
120 , x_item_key
121 , p_version_id
122 , x_msg_count
123 , x_msg_data
124 , x_return_status
125 );
126
127 IF x_return_status = FND_API.g_ret_sts_success then
128
129 --debug_msg ( 'before WF_ENGINE startProcess' );
130 --debug_msg ( 'startProcess: item_type = ' || p_item_type || ' item_key = ' || x_Item_Key );
131
132
133 OPEN get_last_workflow_info;
134 FETCH get_last_workflow_info INTO l_last_item_key;
135
136 --debug_msg_s1 ('get abort AAAAAAAAAA' || To_char(l_last_item_key));
137
138 IF get_last_workflow_info%found THEN
139
140 begin
141
142 --debug_msg_s1 ('abort AAAAAAAAAA' || p_item_type);
143
144 --debug_msg_s1 ('abort AAAAAAAAAA' || To_char(l_last_item_key));
145 WF_ENGINE.AbortProcess( p_Item_Type
146 , l_last_Item_Key
147 );
148 EXCEPTION
149 WHEN OTHERS THEN
150 --debug_msg_s1 ('exception');
151 NULL;
152 END;
153
154 END IF;
155
156 CLOSE get_last_workflow_info; -- Bug #3905748
157
158 WF_ENGINE.StartProcess(
159 p_Item_Type
160 , x_Item_Key
161 );
162 END IF;
163
164 --debug_msg ( 'after WF_ENGINE startProcess' );
165
166
167 EXCEPTION
168
169 WHEN OTHERS THEN
170 --debug_msg ( 'Exception ' || substr(SQLERRM,1,2000) );
171
172
173 x_msg_count := 1;
174 x_msg_data := substr(SQLERRM,1,2000);
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176
177 RAISE;
178
179 END start_workflow;
180
181 /********************************************************************
182 * Procedure : Cancel_Workflow
183 * Parameters IN :
184 * Parameters OUT: Return Status
185 * Purpose :
186 *********************************************************************/
187 Procedure Cancel_Workflow
188 ( p_Item_type IN VARCHAR2
189 , p_Item_key IN VARCHAR2
190 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
191 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
192 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
193 )
194
195 IS
196 l_task_id NUMBER := 0;
197 l_project_id NUMBER := 0;
198
199 BEGIN
200
201
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 --debug_msg ( 'after client cancel_workflow call' );
205
206 IF (x_return_status = FND_API.g_ret_sts_success) THEN
207 WF_ENGINE.AbortProcess( p_Item_Type
208 , p_Item_Key
209 );
210
211 --debug_msg ( 'after WF_ENGINE abortProcess' );
212
213 --debug_msg ('before get task_id');
214
215 END IF;
216
217
218 EXCEPTION
219
220 WHEN OTHERS THEN
221 --debug_msg ( 'Exception in Cancel_Wf ' || substr(SQLERRM,1,2000) );
222
223 x_msg_count := 1;
224 x_msg_data := substr(SQLERRM,1,2000);
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 RAISE;
227 END Cancel_workflow;
228
229 /********************************************************************
230 * Procedure : get_Workflow_URL
231 * Parameters IN :
232 * Parameters OUT: Return Status
233 * Purpose :
234 *********************************************************************/
235 Procedure Get_Workflow_Url
236 (
237 p_ItemType IN VARCHAR2
238 , p_ItemKey IN VARCHAR2
239 , x_URL OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
240 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
241 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
242 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
243 )
244
245 IS
246
247 BEGIN
248
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251
252 --debug_msg ('before web url = ');
253
254 x_URL := wf_monitor.getDiagramURL( wf_core.translate('WF_WEB_AGENT')
255 , p_ItemType
256 , p_ItemKey
257 , 'NO'
258 );
259
260 --debug_msg ('web url = ' || x_url);
261
262
263 EXCEPTION
264
265 WHEN OTHERS THEN
266 --debug_msg ( 'Exception in Get_Wf_Url ' || substr(SQLERRM,1,2000) );
267 x_msg_count := 1;
268 x_msg_data := substr(SQLERRM,1,2000);
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 RAISE;
271
272 END get_workflow_url;
273
274 PROCEDURE check_progress_status
275 (itemtype IN VARCHAR2
276 ,itemkey IN VARCHAR2
277 ,actid IN NUMBER
278 ,funcmode IN VARCHAR2
279 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
280 IS
281
282 l_version_id NUMBER := 0;
283 l_status VARCHAR2(30);
284 l_ret VARCHAR2(240);
285 l_name fnd_user.user_name%type; -- Added for bug 5027098
286
287 CURSOR get_progress_status IS
288 SELECT report_status_code
289 FROM pa_progress_report_vers
290 WHERE version_id = l_version_id;
291
292
293 BEGIN
294
295 l_version_id := wf_engine.GetItemAttrNumber
296 ( itemtype => itemtype,
297 itemkey => itemkey,
298 aname => 'VERSION_ID');
299
300 OPEN get_progress_status;
301 FETCH get_progress_status INTO l_status;
302
303 CLOSE get_progress_status;
304
305 wf_engine.SetItemAttrText
306 ( itemtype,
307 itemkey,
308 'REPORT_STATUS',
309 l_status);
310 /* Code addition for bug 5027098 starts */
311 l_name := wf_engine.getItemAttrText
312 ( itemtype => itemtype,
313 itemkey => itemkey,
314 aname => 'REPORT_APPROVER_USER_NAME');
315 /* Code addition for bug 5027098 ends */
316
317 IF l_status = 'PROGRESS_REPORT_PUBLISHED' THEN
318 resultout := wf_engine.eng_completed||':'||'PUBLISHED';
319 ELSIF l_status = 'PROGRESS_REPORT_REJECTED' THEN
320 /* Code addition for bug 5027098 starts */
321 wf_engine.SetItemAttrText
322 ( itemtype,
323 itemkey,
324 'FROM_ROLE_VALUE',
325 l_name);
326 /* Code addition for bug 5027098 ends */
327 resultout := wf_engine.eng_completed||':'||'REJECTED';
328 ELSIF l_status = 'PROGRESS_REPORT_APPROVED' THEN
329
330 /* Code addition for bug 5027098 starts */
331 wf_engine.SetItemAttrText
332 ( itemtype,
333 itemkey,
334 'FROM_ROLE_VALUE',
335 l_name);
336 /* Code addition for bug 5027098 ends */
337 resultout := wf_engine.eng_completed||':'||'APPROVED';
338 ELSIF l_status = 'PROGRESS_REPORT_CANCELED' THEN
339 resultout := wf_engine.eng_completed||':'||'CANCELED';
340 END IF;
341
342 -- added by syao
343 -- set notification party based on the notification type
344 pa_report_workflow_client.set_report_notification_party
345 (itemtype,
346 itemkey,
347 l_status,
348 actid,
349 funcmode,
350 l_ret
351 );
352
353
354 END ;
355
356
357 PROCEDURE change_status_working
358 (itemtype IN VARCHAR2
359 ,itemkey IN VARCHAR2
360 ,actid IN NUMBER
361 ,funcmode IN VARCHAR2
362 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
363 IS
364
365 l_version_id NUMBER := 0;
366 l_status VARCHAR2(30);
367 l_record_version_number NUMBER;
368
369
370 l_return_status VARCHAR2(200);
371 l_msg_data VARCHAR2(200);
372 l_msg_count number;
373
374
375 BEGIN
376
377 l_version_id := wf_engine.GetItemAttrNumber
378 ( itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'VERSION_ID');
381
382 l_record_version_number := wf_engine.GetItemAttrNumber
383 ( itemtype => itemtype,
384 itemkey => itemkey,
385 aname => 'RECORD_VERSION_NUMBER');
386
387 pa_progress_report_pub.rework_report
388 (
389 p_validate_only=> 'F',
390 p_commit=>'T',
391
392 p_version_id => l_version_id,
393 p_record_version_number=>l_record_version_number,
394
395 x_return_status=> l_return_status,
396 x_msg_count=>l_msg_count,
397 x_msg_data=> l_msg_data
398
399 );
400
401 END ;
402
403
404 PROCEDURE change_status_rejected
405 (itemtype IN VARCHAR2
406 ,itemkey IN VARCHAR2
407 ,actid IN NUMBER
408 ,funcmode IN VARCHAR2
409 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
410 IS
411
412 l_version_id NUMBER := 0;
413 l_status VARCHAR2(30);
414 l_record_version_number NUMBER;
415
416 l_return_status VARCHAR2(200);
417 l_msg_data VARCHAR2(200);
418 l_msg_count number;
419 l_comment VARCHAR2(2000);
420 l_name VARCHAR2(200);
421
422 BEGIN
423
424 l_version_id := wf_engine.GetItemAttrNumber
425 ( itemtype => itemtype,
426 itemkey => itemkey,
427 aname => 'VERSION_ID');
428
429 l_record_version_number := wf_engine.GetItemAttrNumber
430 ( itemtype => itemtype,
431 itemkey => itemkey,
432 aname => 'RECORD_VERSION_NUMBER');
433
434 pa_progress_report_pub.reject_report
435 (
436 p_validate_only=> 'F',
437 p_commit=>'T',
438
439 p_version_id => l_version_id,
440 p_record_version_number=>l_record_version_number,
441
442 x_return_status=> l_return_status,
443 x_msg_count=>l_msg_count,
444 x_msg_data=> l_msg_data
445
446 );
447
448 IF (l_return_status = 'S' ) THEN
449 l_comment := wf_engine.GetItemAttrText
450 ( itemtype => itemtype,
451 itemkey => itemkey,
452 aname => 'COMMENT');
453
454 l_name := wf_engine.GetItemAttrText( itemtype
455 , itemkey
456 , 'REPORT_APPROVER_USER_NAME');
457
458 pa_workflow_history.save_comment_history (
459 itemtype
460 ,itemkey
461 ,'REJECT'
462 ,l_name ,
463 l_comment);
464
465 check_progress_status
466 (itemtype
467 ,itemkey
468 ,actid
469 ,funcmode
470 ,resultout );
471
472
473 END IF;
474
475
476 END ;
477
478
479 PROCEDURE change_status_approved
480 (itemtype IN VARCHAR2
481 ,itemkey IN VARCHAR2
482 ,actid IN NUMBER
483 ,funcmode IN VARCHAR2
484 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
485 IS
486
487 l_version_id NUMBER := 0;
488 l_status VARCHAR2(30);
489 l_record_version_number NUMBER;
490 -- l_summary_version_number NUMBER; --Commented for Bug 3629793
491
492 l_return_status VARCHAR2(200);
493 l_msg_data VARCHAR2(200);
494 l_msg_count number;
495
496 l_dummy VARCHAR2(1);
497 l_auto_approved VARCHAR2(10);
498
499 CURSOR is_auto_published IS
500 SELECT 'Y'
501 FROM dual
502 WHERE exists
503 (SELECT * FROM pa_progress_report_vers pprv, pa_object_page_layouts popl
504 WHERE pprv.version_id = l_version_id
505 AND pprv.object_id = popl.object_id
506 AND pprv.object_type = popl.object_type
507 AND popl.approval_required = 'A');
508
509 l_comment VARCHAR2(2000);
510 l_name VARCHAR2(200);
511
512 BEGIN
513
514 l_version_id := wf_engine.GetItemAttrNumber
515 ( itemtype => itemtype,
516 itemkey => itemkey,
517 aname => 'VERSION_ID');
518
519 l_record_version_number := wf_engine.GetItemAttrNumber
520 ( itemtype => itemtype,
521 itemkey => itemkey,
522 aname => 'RECORD_VERSION_NUMBER');
523
524 /* Commented for Bug 3629793
525 l_summary_version_number := wf_engine.GetItemAttrNumber
526 ( itemtype => itemtype,
527 itemkey => itemkey,
528 aname => 'SUMMARY_VERSION_NUMBER');
529 */
530
531 -- if autopublish, we will publish the report directly
532 --OPEN is_auto_published;
533 --FETCH is_auto_published INTO l_dummy;
534 --CLOSE is_auto_published;
535
536 -- we get the autopublish info from item attribute now
537
538 l_auto_approved := wf_engine.GetItemAttrText
539 ( itemtype => itemtype,
540 itemkey => itemkey,
541 aname => 'AUTO_APPROVED');
542
543 -- IF l_dummy = 'Y' THEN
544 --debug_msg ('Workflow ended before autopublish ' || l_auto_approved);
545
546
547 IF l_auto_approved = 'A' then
548 pa_progress_report_pub.publish_report
549 (
550 p_validate_only=> 'F',
551 p_commit=>'T',
552
553 p_version_id => l_version_id,
554 p_record_version_number=>l_record_version_number,
555 -- p_summary_version_number=>l_summary_version_number, -- Commented for Bug 3629793
556
557 x_return_status=> l_return_status,
558 x_msg_count=>l_msg_count,
559 x_msg_data=> l_msg_data
560
561 );
562 ELSE
563
564 pa_progress_report_pub.approve_report
565 (
566 p_validate_only=> 'F',
567 p_commit=>'T',
568
569 p_version_id => l_version_id,
570 p_record_version_number=>l_record_version_number,
571
572 x_return_status=> l_return_status,
573 x_msg_count=>l_msg_count,
574 x_msg_data=> l_msg_data
575
576 );
577 END IF;
578
579 IF (l_return_status = 'S') THEN
580
581
582 l_comment := wf_engine.GetItemAttrText
583 ( itemtype => itemtype,
584 itemkey => itemkey,
585 aname => 'COMMENT');
586
587 l_name := wf_engine.GetItemAttrText( itemtype
588 , itemkey
589 , 'REPORT_APPROVER_USER_NAME');
590
591 pa_workflow_history.save_comment_history (
592 itemtype
593 ,itemkey
594 ,'APPROVE'
595 ,l_name ,
596 l_comment);
597
598 -- need to reset the notification party for the APPROVED
599 -- message
600 check_progress_status
601 (itemtype
602 ,itemkey
603 ,actid
604 ,funcmode
605 ,resultout );
606
607 END IF;
608
609
610
611 END ;
612
613
614 PROCEDURE is_submitter_same_as_reporter
615 (itemtype IN VARCHAR2
616 ,itemkey IN VARCHAR2
617 ,actid IN NUMBER
618 ,funcmode IN VARCHAR2
619 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
620 IS
621
622 --reported_by is person_id
623 l_reported_by_id NUMBER;
624 l_submitter_id NUMBER;
625 l_submitter_emp_id NUMBER;
626 --l_reported_emp_id NUMBER;
627
628 CURSOR get_submitter_emp_id IS
629 SELECT employee_id
630 FROM fnd_user
631 WHERE user_id = l_submitter_id;
632
633
634 CURSOR get_reporter_emp_id IS
635 SELECT employee_id
636 FROM fnd_user
637 WHERE user_id = l_reported_by_id;
638
639
640 BEGIN
641
642 l_reported_by_id := wf_engine.GetItemAttrNumber
643 ( itemtype => itemtype,
644 itemkey => itemkey,
645 aname => 'REPORTED_BY_ID');
646
647 l_submitter_id := wf_engine.GetItemAttrNumber
648 ( itemtype => itemtype,
649 itemkey => itemkey,
650 aname => 'SUBMITTER_ID');
651
652 --OPEN get_reporter_emp_id;
653 --FETCH get_reporter_emp_id INTO l_reported_emp_id;
654 --CLOSE get_reporter_emp_id;
655
656 OPEN get_submitter_emp_id;
657 FETCH get_submitter_emp_id INTO l_submitter_emp_id;
658 CLOSE get_submitter_emp_id;
659
660 IF l_submitter_emp_id = l_reported_by_id THEN
661
662 resultout := wf_engine.eng_completed||':'||'T';
663 ELSE
664
665 resultout := wf_engine.eng_completed||':'||'F';
666 END IF;
667
668 --debug_msg('is_submitter_same_as_reporter ');
669 --debug_msg('returning ' || resultout);
670
671 --debug_msg('l_submitter_emp_id ' || To_char(l_submitter_emp_id));
672 --debug_msg('l_reporter_emp_id ' || To_char(l_reported_by_id));
673
674
675 END;
676
677 Procedure start_action_set_workflow
678 (
679 p_item_type IN VARCHAR2
680 , p_process_name IN VARCHAR2
681
682 , p_object_type IN VARCHAR2
683 , p_object_id IN NUMBER
684
685 ,p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
686 ,p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
687
688 ,x_action_line_audit_tbl out NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type --File.Sql.39 bug 4440895
689 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
690 , x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
691 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
692 )
693 IS
694
695 l_item_key VARCHAR2(200);
696 l_last_item_key VARCHAR2(200);
697 l_err_code NUMBER;
698 l_err_stage VARCHAR2(30);
699 l_err_stack VARCHAR2(240);
700 l_customer VARCHAR2(4000);
701 l_project_manager VARCHAR2(240);
702 l_project VARCHAR2(500);
703 l_org pa_project_lists_v.carrying_out_organization_name%TYPE; -- Bug 4358517.
704 l_report_type_name VARCHAR2(80);
705 l_url2 VARCHAR2(600);
706 l_project_id NUMBER;
707 l_report_start_date DATE;
708 l_report_end_date DATE;
709 l_Report_Type_Id NUMBER := null;
710 l_Reporting_Cycle_Id NUMBER := null;
711 l_Reporting_Offset_Days NUMBER := null;
712 l_effective_from DATE;
713
714
715
716 CURSOR get_last_workflow_info
717 IS
718 SELECT MAX(item_key)
719 FROM pa_wf_processes
720 WHERE item_type = p_item_type
721 AND description = p_process_name
722 AND entity_key1 = l_project_id
723 AND wf_type_code = 'Progress Report'
724 ;
725
726
727 CURSOR get_project_info IS
728 SELECT
729 pplv. customer_name,
730 pplv.person_name,
731 pplv.carrying_out_organization_name,
732 pplv.name || '(' || pplv.segment1 || ')',
733 prt.name,
734 pplv.project_id
735 FROM pa_project_lists_v pplv, pa_object_page_layouts popl, pa_report_types prt
736 WHERE pplv.project_id = popl.object_id
737 AND popl.object_page_layout_id = p_object_id
738 AND popl.object_type = 'PA_PROJECTS'
739 and nvl(popl.report_type_id, 1) = prt.report_type_id
740 ;
741
742 CURSOR get_object_page_info IS
743 SELECT
744 report_type_id,
745 reporting_cycle_id,
746 report_offset_days,
747 effective_from
748 FROM pa_object_page_layouts
749 WHERE
750 object_page_layout_id = p_object_id
751 and
752 page_type_code = 'PPR';
753
754
755 BEGIN
756
757 --debug_msg_s1 ('start workflow 1: starting');
758
759 SELECT To_char(pa_workflow_itemkey_s.NEXTVAL)
760 INTO l_item_key
761 from dual;
762
763
764 x_return_status := FND_API.G_RET_STS_SUCCESS;
765
766
767 -- create the workflow process
768
769 --debug_msg_s1 ('start workflow 1: starting');
770 WF_ENGINE.CreateProcess( p_item_type
771 , l_item_key
772 , p_Process_Name);
773
774 --debug_msg_s1 ('start workflow 1: starting 2');
775
776 wf_engine.SetItemAttrNumber( p_item_type
777 , l_item_key
778 , 'OBJECT_ID'
779 ,p_object_id
780 );
781
782 wf_engine.SetItemAttrText( p_item_type
783 , l_item_key
784 , 'OBJECT_TYPE'
785 ,p_object_type
786 );
787
788
789 -- set some of the common item attributes
790 OPEN get_project_info;
791 FETCH get_project_info INTO l_customer,l_project_manager, l_org, l_project, l_report_type_name, l_project_id;
792
793 --debug_msg_s ('mgr ' || l_project_manager);
794
795 wf_engine.SetItemAttrText( p_item_type
796 , l_item_key
797 , 'PROJECT_MANAGER'
798 ,l_project_manager
799 );
800 CLOSE get_project_info;
801
802 --debug_msg_s ('org ' || l_org);
803 wf_engine.SetItemAttrText( p_item_type
804 , l_item_key
805 , 'ORGANIZATION'
806 ,l_org
807 );
808 --debug_msg_s ('customer ' || l_customer);
809 wf_engine.SetItemAttrText( p_item_type
810 , l_item_key
811 , 'CUSTOMER'
812 ,l_customer
813 );
814
815 wf_engine.SetItemAttrText( p_item_type
816 , l_item_key
817 , 'PROJECT'
818 ,l_project
819 );
820
821 wf_engine.SetItemAttrText( p_item_type
822 , l_item_key
823 , 'REPORT_TYPE'
824 ,l_report_type_name
825 );
826
827 OPEN get_object_page_info;
828 FETCH get_object_page_info INTO l_report_type_id, l_reporting_cycle_id,
829 l_reporting_offset_days, l_effective_from;
830 CLOSE get_object_page_info;
831
832
833 l_url2 := 'JSP:/OA_HTML/OA.jsp?paProjectId='|| l_project_id||
834 '&akRegionCode=PA_PROG_RPT_MAINT_LAYOUT&akRegionApplicationId=275&paPageMode=APPROVE&addBreadCrumb=RP&paProgressMode=MAINTENANCE&paReportTypeId='
835 || l_Report_Type_Id;
836
837 wf_engine.SetItemAttrText( p_item_type
838 , l_item_key
839 , 'REPORT_LINK'
840 , l_url2
841 );
842
843
844
845
846 --debug_msg_s1 ('Parameter ' || l_project_id);
847 --debug_msg_s1 ('Parameter ' || l_Report_Type_Id);
848 --debug_msg_s1 ('Parameter ' || l_Reporting_Cycle_Id);
849 --debug_msg_s1 ('Parameter ' || l_Reporting_Offset_Days);
850
851
852 IF l_reporting_cycle_id IS NOT NULL THEN
853 pa_progress_report_utils.Get_Report_Start_End_Dates(
854 p_object_type=>'PA_PROJECTS',
855 p_object_id=>l_project_id,
856 p_report_type_id=>l_Report_Type_Id ,
857 p_reporting_cycle_id=>l_Reporting_Cycle_Id ,
858 p_reporting_offset_days=>l_Reporting_Offset_Days ,
859 p_publish_report=> 'Y',
860 p_report_effective_from => l_effective_from,
861 x_report_start_date=> l_report_start_date,
862 x_report_end_date=> l_report_end_date
863 );
864 ELSE
865 l_report_start_date := Trunc(Sysdate);
866 l_report_end_date := Trunc(Sysdate);
867
868 END IF;
869
870 -- set report start date
871 wf_engine.SetItemAttrDate( p_item_type
872 , l_item_key
873 , 'REPORT_START_DATE'
874 ,l_report_start_date
875 );
876
877 -- set report start date
878 wf_engine.SetItemAttrDate( p_item_type
879 , l_item_key
880 , 'REPORT_END_DATE'
881 ,l_report_end_date
882 );
883
884 -- cancel the last running workflow process on the same item_type,
885 -- process name
886 -- this is required for todo workflow because the last one always
887 -- override the previous workflow process
888
889 -- get the last workflow info
890
891 --debug_msg_s1 ('start workflow 1');
892
893
894 OPEN get_last_workflow_info;
895 FETCH get_last_workflow_info INTO l_last_item_key;
896 IF get_last_workflow_info%found THEN
897
898 begin
899 -- abort this process if it is running
900 WF_ENGINE.AbortProcess( p_Item_Type
901 , l_last_Item_Key
902 );
903 EXCEPTION
904 WHEN OTHERS THEN
905 NULL;
906 END;
907
908 END IF;
909
910
911 CLOSE get_last_workflow_info; -- Bug #3905748
912
913 --debug_msg_s1 ('after workflow 1' ||p_process_name );
914
915 -- set notification party
916
917 IF p_process_name = 'PA_PROJ_STATUS_REPORT_NEXT' THEN
918 -- reminder workflow process
919 set_reminder_report_notify(p_item_type,
920 l_item_key,
921 p_object_type,
922 p_object_id,
923 p_action_set_line_rec,
924 p_action_line_conditions_tbl,
925 x_action_line_audit_tbl);
926 ELSIF p_process_name = 'PA_PROJ_STATUS_REPORT_MISS' THEN
927 -- missing report workflow process
928 set_missing_report_notify(p_item_type,
929 l_item_key,
930 p_object_type,
931 p_object_id,
932 p_action_set_line_rec,
933 p_action_line_conditions_tbl,
934 x_action_line_audit_tbl);
935
936 END IF;
937
938
939 -- ready to start the workflow
940
941 --debug_msg_s1 ('after workflow 1' || x_return_status);
942 IF x_return_status = FND_API.g_ret_sts_success then
943
944
945 --debug_msg_s1 ('after workflow 1: start process');
946
947 WF_ENGINE.StartProcess(
948 p_Item_Type
949 , l_Item_Key
950 );
951 END IF;
952
953 -- added for creating record in the audit table
954
955 -- insert into pa_wf_process table
956
957 --debug_msg_s1 ('after workflow 1: isnertwf processes' || p_item_type);
958 --debug_msg_s1 ('after workflow 1: isnertwf processes' || l_item_key);
959
960
961
962 PA_WORKFLOW_UTILS.Insert_WF_Processes
963 (p_wf_type_code => 'Progress Report'
964 ,p_item_type => p_item_type
965 ,p_item_key => l_item_key
966 ,p_entity_key1 => p_object_id
967 ,p_entity_key2 => l_project_id
968 ,p_description => p_process_name
969 ,p_err_code => l_err_code
970 ,p_err_stage => l_err_stage
971 ,p_err_stack => l_err_stack
972 );
973 --debug_msg_s1 ('after workflow 1: isnertwf processes' || l_err_code);
974
975 IF l_err_code <> 0 THEN
976 PA_UTILS.Add_Message( p_app_short_name => 'PA'
977 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
978 x_return_status := FND_API.G_RET_STS_ERROR;
979
980 END IF;
981
982
983 --debug_msg_s1 ('after workflow 1: isnertwf processes: returns');
984
985 COMMIT;
986
987 EXCEPTION
988
989 WHEN OTHERS THEN
990
991 --debug_msg_s1 ('after workflow 1: exception' || substr(SQLERRM,1,2000));
992 x_msg_count := 1;
993 x_msg_data := substr(SQLERRM,1,2000);
994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995
996 RAISE;
997
998 END start_action_set_workflow;
999
1000
1001
1002
1003 PROCEDURE set_reminder_report_notify( p_item_type IN VARCHAR2
1004 , p_item_key IN NUMBER
1005 , p_object_type IN VARCHAR2
1006 , p_object_id IN NUMBER
1007
1008 , p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
1009 , p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
1010
1011 , x_action_line_audit_tbl out NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type --File.Sql.39 bug 4440895
1012 )
1013
1014 IS
1015
1016 l_object_type VARCHAR2(30);
1017 l_object_id NUMBER;
1018 l_project_id NUMBER;
1019 l_next_reporting_date DATE;
1020 l_reminder_days NUMBER;
1021 l_action_set_id NUMBER;
1022 l_project_name VARCHAR2(30);
1023 l_project_number VARCHAR2(25);
1024 l_action_code VARCHAR2(30);
1025 l_reminder_role varchar2(30) := NULL;
1026 l_reminder_role_display_name varchar2(30) := NULL; -- Bug 4565156.
1027 l_role_users varchar2(30000) := NULL;
1028
1029 l_days NUMBER;
1030
1031 l_INDEX NUMBER;
1032
1033 CURSOR get_obj_page_layout_info
1034 IS
1035 SELECT object_id, reminder_days, next_reporting_date
1036 FROM pa_object_page_layouts
1037 WHERE object_page_layout_id = l_object_id
1038 AND object_type = 'PA_PROJECTS';
1039
1040
1041 CURSOR get_person_name(l_user_name varchar2)
1042 IS
1043 SELECT papf.full_name person_name,
1044 papf.email_address
1045 FROM
1046 fnd_user fu,per_all_people_f papf
1047 where fu.employee_id = papf.person_id
1048 AND fu.user_name = l_user_name
1049 AND trunc(sysdate) between papf.EFFECTIVE_START_DATE
1050 and Nvl(papf.effective_end_date, Sysdate + 1)
1051 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1);
1052
1053 l_user_names pa_distribution_list_utils.pa_vc_1000_150;
1054 l_full_names pa_distribution_list_utils.pa_vc_1000_150;
1055 l_email_addresses pa_distribution_list_utils.pa_vc_1000_150;
1056 l_return_status VARCHAR2(1);
1057 l_msg_count NUMBER;
1058 l_msg_data VARCHAR2(2000);
1059 i INTEGER;
1060
1061 l_t1 VARCHAR2(30);
1062 l_t2 VARCHAR2(30);
1063 l_t3 VARCHAR2(300);
1064
1065 display_name VARCHAR2(2000);
1066 email_address VARCHAR2(2000);
1067 notification_preference VARCHAR2(2000);
1068 language VARCHAR2(2000);
1069 territory VARCHAR2(2000);
1070 BEGIN
1071
1072
1073 l_object_id := p_object_id;
1074
1075 l_days := To_number(p_action_line_conditions_tbl(1).condition_attribute1);
1076
1077 l_object_type := p_object_type;
1078
1079 -- get the project info, reminder days and next reporting days
1080 OPEN get_obj_page_layout_info;
1081 FETCH get_obj_page_layout_info INTO l_project_id, l_reminder_days,
1082 l_next_reporting_date;
1083
1084
1085
1086 CLOSE get_obj_page_layout_info;
1087
1088 -- get Project Name, Number info
1089 pa_utils.getprojinfo(l_project_id, l_project_number, l_project_name);
1090 -- set item attribute for Project Name, Number
1091 wf_engine.SetItemAttrText( p_item_type
1092 , p_item_key
1093 , 'PROJECT_NAME'
1094 ,l_project_name
1095 );
1096
1097 wf_engine.SetItemAttrText( p_item_type
1098 , p_item_key
1099 , 'PROJECT_NUMBER'
1100 ,l_project_number
1101 );
1102
1103 -- set item attribute for reminder days and next reporting date
1104 wf_engine.SetItemAttrDate( p_item_type
1105 , p_item_key
1106 , 'NEXT_REPORT_DATE'
1107 ,l_next_reporting_date
1108 );
1109
1110 wf_engine.SetItemAttrNumber( p_item_type
1111 , p_item_key
1112 , 'REMINDER_DAYS'
1113 ,l_reminder_days
1114 );
1115
1116 -- set notification party
1117 l_reminder_role := 'RMND_' ||p_item_type || p_item_key;
1118 l_reminder_role_display_name := l_reminder_role; -- Bug 4565156.
1119
1120 WF_DIRECTORY.CreateAdHocRole( role_name => l_reminder_role
1121 , role_display_name => l_reminder_role_display_name -- Bug 4565156.
1122 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962401
1123
1124
1125
1126
1127 l_INDEX := 1;
1128
1129
1130 PA_DISTRIBUTION_LIST_UTILS.get_dist_list
1131 (
1132 l_object_type,
1133 l_object_id,
1134
1135 2, -- edit and view priv
1136 l_user_names ,
1137 l_full_names ,
1138 l_email_addresses ,
1139 l_return_status ,
1140 l_msg_count ,
1141 l_msg_data
1142 );
1143
1144 IF (l_return_status = 'S' AND l_user_names IS NOT null) then
1145 FOR i in l_user_names.First..l_user_names.LAST LOOP
1146
1147 IF l_user_names(i) IS NULL THEN
1148 --EXIT ;
1149 l_user_names(i) := Upper(l_email_addresses(i));
1150 l_full_names(i) := l_email_addresses(i);
1151
1152 END IF;
1153
1154 if (l_role_users is not null) then
1155 l_role_users := l_role_users || ',';
1156 END IF;
1157
1158
1159 wf_directory.getroleinfo(Upper(l_user_names(i)),display_name,
1160 email_address,notification_preference,language,territory);
1161 if display_name is null THEN
1162
1163 --IF NOT wf_directory.useractive (l_user_names(i)) THEN
1164
1165 WF_DIRECTORY.CreateAdHocUser( name => l_user_names(i)
1166 , display_name => l_full_names(i)
1167 , EMAIL_ADDRESS =>l_email_addresses(i));
1168 END IF;
1169 l_role_users := l_role_users || l_user_names(i);
1170
1171 x_action_line_audit_tbl(i).reason_code := 'CONDITION_MET';
1172 x_action_line_audit_tbl(i).action_code := p_action_set_line_rec.action_code;
1173 x_action_line_audit_tbl(i).audit_display_attribute := l_full_names(i);
1174 x_action_line_audit_tbl(i).audit_attribute := l_user_names(i);
1175 x_action_line_audit_tbl(i).reversed_action_set_line_id := NULL;
1176 L_INDEX := L_INDEX +1;
1177 end loop;
1178
1179 END IF;
1180
1181
1182
1183
1184 IF (l_role_users is NOT NULL) THEN
1185 --debug_msg2('Add user role' || l_reporter_role);
1186 --debug_msg_s1('Add users: ' || l_role_users);
1187 WF_DIRECTORY.AddUsersToAdHocRole( l_reminder_role
1188 , l_role_users);
1189
1190
1191 wf_engine.SetItemAttrText( p_item_type
1192 , p_item_key
1193 , 'REPORT_REMINDER_NAME'
1194 , l_reminder_role);
1195 --debug_msg ('OK: approver is found ');
1196
1197 ELSE
1198 --debug_msg ('Error: no approver is found ');
1199 NULL;
1200 END IF;
1201
1202 --debug_msg2 ('after add users to the role ' || l_approval_role );
1203
1204 commit;
1205
1206
1207
1208 EXCEPTION
1209
1210 WHEN OTHERS THEN
1211 RAISE;
1212
1213 END set_reminder_report_notify;
1214
1215 PROCEDURE set_missing_report_notify
1216 ( p_item_type IN VARCHAR2
1217 , p_item_key IN NUMBER
1218 , p_object_type IN VARCHAR2
1219 , p_object_id IN NUMBER
1220
1221 , p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
1222 , p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
1223
1224 , x_action_line_audit_tbl out NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type --File.Sql.39 bug 4440895
1225 )
1226 IS
1227
1228 l_object_type VARCHAR2(30);
1229 l_object_id NUMBER;
1230 l_project_id NUMBER;
1231 l_days NUMBER;
1232 l_next_reporting_date DATE;
1233 l_reminder_days NUMBER;
1234 l_action_set_id NUMBER;
1235 l_project_name VARCHAR2(30);
1236 l_project_number VARCHAR2(25);
1237 l_object_page_layout_id NUMBER;
1238 l_reminder_role varchar2(30) := NULL;
1239 l_reminder_role_display_name varchar2(30) := NULL; -- Bug 4565156.
1240 l_role_users varchar2(30000) := NULL;
1241 l_action_attribute1 VARCHAR2(150);
1242 l_role_id VARCHAR2(30);
1243 l_approver_id NUMBER;
1244 l_approver_source_type NUMBER;
1245
1246 CURSOR get_obj_page_layout_info
1247 IS
1248 SELECT object_id, reminder_days, next_reporting_date
1249 FROM pa_object_page_layouts
1250 WHERE object_page_layout_id = l_object_id
1251 AND object_type = 'PA_PROJECTS';
1252
1253 CURSOR l_report_approver_csr_person IS
1254 select distinct
1255 fu.user_id,
1256 fu.user_name,
1257 papf.email_address,
1258 papf.full_name person_name
1259 from
1260 fnd_user fu,per_all_people_f papf
1261 where
1262 fu.employee_id = l_approver_id
1263 and papf.person_id = fu.employee_id
1264 and trunc(sysdate)
1265 between papf.EFFECTIVE_START_DATE
1266 and Nvl(papf.effective_end_date, Sysdate + 1)
1267 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
1268 ;
1269
1270 CURSOR l_report_approver_csr_party IS
1271 select distinct
1272 fu.user_id,
1273 fu.user_name,
1274 papf.email_address,
1275 papf.full_name person_name
1276 from
1277 fnd_user fu,per_all_people_f papf
1278 where
1279 fu.person_party_id = l_approver_id -- Bug 4527617. Replaced customer_id with person_party_id.
1280 and papf.person_id = fu.employee_id
1281 and trunc(sysdate)
1282 between papf.EFFECTIVE_START_DATE
1283 and Nvl(papf.effective_end_date, Sysdate + 1)
1284 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
1285 ;
1286
1287 CURSOR l_missing_csr IS
1288 select distinct
1289 fu.user_id,
1290 fu.user_name,
1291 papf.email_address,
1292 papf.full_name person_name,
1293 ppp.resource_source_id
1294 from
1295 pa_project_parties ppp,
1296 fnd_user fu,per_all_people_f papf
1297 where ppp.project_id = l_project_id
1298 and ppp.project_role_id = To_number(l_role_id)
1299 and ppp.project_id = ppp.object_id
1300 AND fu.employee_id = ppp.resource_source_id
1301 and papf.person_id = fu.employee_id
1302 and trunc(sysdate)
1303 between papf.EFFECTIVE_START_DATE
1304 and Nvl(papf.effective_end_date, Sysdate + 1)
1305 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
1306 and trunc(sysdate) between ppp.START_DATE_active
1307 and nvl(ppp.END_DATE_active, sysdate+1);
1308
1309 /* Bug 2911451 Included checks primary_flag='Y' and Assignment_type ='E'
1310 for the project manager ie p1.person_id
1311 Also included checks primary_flag='Y' and Assignment_type ='E' and date check
1312 for the Supervisor of this person */
1313 CURSOR l_hr_manager_csr IS
1314 select distinct
1315 fu.user_id,
1316 fu.user_name,
1317 p2.email_address,
1318 p2.full_name person_name
1319 from
1320 pa_proj_parties_prog_ev_v ppp,
1321 fnd_user fu,per_all_people_f p2,
1322 per_assignments_f p1
1323 where ppp.project_id = l_project_id
1324 and ppp.project_role_id = 1
1325 and ppp.resource_source_id = p1.person_id
1326 and p1.primary_flag='Y'
1327 and p1.Assignment_type in ('E', 'C')
1328 and p1.supervisor_id = p2.person_id
1329 and p1.supervisor_id = fu.employee_id
1330 and trunc(sysdate)
1331 between p1.EFFECTIVE_START_DATE
1332 and p1.effective_end_date -- Removed nvl for bug 2911451
1333 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1)
1334 and trunc(sysdate) between ppp.START_DATE_active
1335 and nvl(ppp.END_DATE_active, sysdate+1)
1336 and exists ( select 1 from per_assignments_f p3
1337 where p3.person_id = p1.supervisor_id
1338 and p3.primary_flag='Y'
1339 and p3.Assignment_type in ('E', 'C')
1340 and trunc(sysdate) between p3.EFFECTIVE_START_DATE and Nvl(p3.effective_end_date, Sysdate + 1));
1341
1342 CURSOR get_action_attribute
1343 IS
1344 SELECT action_attribute1
1345 FROM pa_action_set_lines
1346 WHERE action_set_line_id = l_action_set_id;
1347
1348 CURSOR get_approver_source_id
1349 IS
1350 SELECT approver_source_id, approver_source_type
1351 FROM pa_progress_report_setup_v
1352 WHERE object_page_layout_id = l_object_page_layout_id;
1353
1354
1355 L_INDEX NUMBER;
1356
1357 l_user_names pa_distribution_list_utils.pa_vc_1000_150;
1358 l_full_names pa_distribution_list_utils.pa_vc_1000_150;
1359 l_email_addresses pa_distribution_list_utils.pa_vc_1000_150;
1360 l_return_status VARCHAR2(1);
1361 l_msg_count NUMBER;
1362 l_msg_data VARCHAR2(2000);
1363 i INTEGER;
1364
1365
1366 l_t1 VARCHAR2(30);
1367 l_t2 VARCHAR2(30);
1368 l_t3 VARCHAR2(300);
1369 l_find_duplicate VARCHAR2(1) := 'N';
1370 display_name VARCHAR2(2000);
1371 email_address VARCHAR2(2000);
1372 notification_preference VARCHAR2(2000);
1373 language VARCHAR2(2000);
1374 territory VARCHAR2(2000);
1375
1376 BEGIN
1377
1378 l_object_id := p_object_id;
1379 l_object_type := p_object_type;
1380
1381 l_days := To_number(p_action_line_conditions_tbl(1).condition_attribute1);
1382 l_role_id := p_action_set_line_rec.action_attribute1;
1383
1384 l_object_page_layout_id := l_object_id;
1385
1386 -- get the project info, reminder days and next reporting days
1387 OPEN get_obj_page_layout_info;
1388 FETCH get_obj_page_layout_info INTO l_project_id, l_reminder_days,
1389 l_next_reporting_date;
1390
1391 CLOSE get_obj_page_layout_info;
1392
1393 -- get Project Name, Number info
1394 pa_utils.getprojinfo(l_project_id, l_project_number, l_project_name);
1395 -- set item attribute for Project Name, Number
1396 wf_engine.SetItemAttrText( p_item_type
1397 , p_item_key
1398 , 'PROJECT_NAME'
1399 ,l_project_name
1400 );
1401
1402 wf_engine.SetItemAttrText( p_item_type
1403 , p_item_key
1404 , 'PROJECT_NUMBER'
1405 ,l_project_number
1406 );
1407
1408 -- set item attribute for reminder days and next reporting date
1409 wf_engine.SetItemAttrDate( p_item_type
1410 , p_item_key
1411 , 'NEXT_REPORT_DATE'
1412 ,l_next_reporting_date
1413 );
1414
1415 wf_engine.SetItemAttrNumber( p_item_type
1416 , p_item_key
1417 , 'REMINDER_DAYS'
1418 ,l_reminder_days
1419 );
1420
1421 -- set notification party
1422 l_reminder_role := 'MRMND_' ||p_item_type || p_item_key;
1423 l_reminder_role_display_name := l_reminder_role; -- Bug 4565156.
1424 WF_DIRECTORY.CreateAdHocRole( role_name => l_reminder_role
1425 , role_display_name => l_reminder_role_display_name -- Bug 4565156.
1426 , expiration_date => sysdate+1); -- Set expiration_date for bug#5962401
1427
1428
1429
1430
1431 /* add people with edit privilege first */
1432 l_INDEX := 1;
1433
1434
1435 PA_DISTRIBUTION_LIST_UTILS.get_dist_list
1436 (
1437
1438 l_object_type,
1439 l_object_id,
1440 2, -- edit and view priv
1441 l_user_names ,
1442 l_full_names ,
1443 l_email_addresses ,
1444 l_return_status ,
1445 l_msg_count ,
1446 l_msg_data
1447 );
1448
1449 IF (l_return_status = 'S' AND l_user_names IS NOT null) then
1450 FOR i in l_user_names.First..l_user_names.LAST LOOP
1451
1452 IF l_user_names(i) IS NULL THEN
1453 --EXIT ;
1454 l_user_names(i) := Upper(l_email_addresses(i));
1455 l_full_names(i) := l_email_addresses(i);
1456
1457 END IF;
1458
1459 if (l_role_users is not null) then
1460 l_role_users := l_role_users || ',';
1461 END IF;
1462
1463
1464 wf_directory.getroleinfo(Upper(l_user_names(i)),display_name,
1465 email_address,notification_preference,language,territory);
1466 if display_name is null THEN
1467
1468 --IF NOT wf_directory.useractive (l_user_names(i)) THEN
1469
1470 WF_DIRECTORY.CreateAdHocUser( name => l_user_names(i)
1471 , display_name => l_full_names(i)
1472 , EMAIL_ADDRESS =>l_email_addresses(i));
1473 END IF;
1474 l_role_users := l_role_users || l_user_names(i);
1475
1476 x_action_line_audit_tbl(i).reason_code := 'CONDITION_MET';
1477 x_action_line_audit_tbl(i).action_code := p_action_set_line_rec.action_code;
1478 x_action_line_audit_tbl(i).audit_display_attribute := l_full_names(i);
1479 x_action_line_audit_tbl(i).audit_attribute := l_user_names(i);
1480 x_action_line_audit_tbl(i).reversed_action_set_line_id := NULL;
1481 L_INDEX := L_INDEX +1;
1482
1483 end loop;
1484
1485 END IF;
1486
1487
1488 IF l_role_id = '-1' THEN
1489 -- HR Manager of Project Manager
1490 -- Approver of the project status report setup
1491 for v_reminders in l_hr_manager_csr loop
1492
1493 --debug_msg2 ('add user name: ' || v_approvers.user_name);
1494 --debug_msg2 ('add user id: ' || v_approvers.person_name);
1495 --debug_msg2 ('add user id: ' || v_approvers.email_address);
1496
1497 l_find_duplicate := 'N';
1498
1499 IF (Instr(l_role_users, v_reminders.user_name||',') = 1) THEN
1500 -- find duplicate
1501 l_find_duplicate := 'Y';
1502 ELSIF (Instr(l_role_users, ','||v_reminders.user_name||',') >0) THEN
1503 -- find duplicate
1504 l_find_duplicate := 'Y';
1505 ELSIF (Instr(l_role_users, ','||v_reminders.user_name) = (Length(l_role_users) - Length(v_reminders.user_name))) THEN
1506 -- find duplicate
1507 l_find_duplicate := 'Y';
1508
1509 END IF;
1510
1511
1512
1513 IF l_find_duplicate = 'N' THEN
1514
1515 if (l_role_users is not null) then
1516 l_role_users := l_role_users || ',';
1517 end if;
1518
1519 -- Create adhoc users
1520 wf_directory.getroleinfo(Upper(v_reminders.user_name),display_name,
1521 email_address,notification_preference,language,territory);
1522 if display_name is null THEN
1523 --IF NOT wf_directory.useractive (v_reminders.user_name) THEN
1524
1525 --debug_msg('Add user');
1526
1527 WF_DIRECTORY.CreateAdHocUser( name => v_reminders.user_name
1528 , display_name => v_reminders.person_name
1529 --, notification_preference => 'MAILTEXT'
1530 , EMAIL_ADDRESS =>v_reminders.email_address);
1531 END IF;
1532 l_role_users := l_role_users || v_reminders.user_name;
1533 x_action_line_audit_tbl(l_index).reason_code := 'CONDITION_MET';
1534 x_action_line_audit_tbl(l_index).action_code := p_action_set_line_rec.action_code;
1535 x_action_line_audit_tbl(l_index).audit_display_attribute := v_reminders.person_name;
1536 x_action_line_audit_tbl(l_index).audit_attribute := v_reminders.user_name;
1537 x_action_line_audit_tbl(l_index).reversed_action_set_line_id := NULL;
1538 L_INDEX := L_INDEX +1 ;
1539 END IF;
1540
1541 end loop;
1542
1543 ELSIF l_role_id = '-2' THEN
1544 -- Approver of the project status report setup
1545
1546 OPEN get_approver_source_id;
1547 FETCH get_approver_source_id INTO l_approver_id, l_approver_source_type;
1548
1549 IF get_approver_source_id%found AND l_approver_id IS NOT NULL then
1550 --IF approver is not null, we will use the ID to get the people
1551 CLOSE get_approver_source_id;
1552
1553 IF l_approver_source_type = 101 THEN
1554 -- source type is person
1555 for v_reminders in l_report_approver_csr_person loop
1556
1557 --debug_msg2 ('add user name: ' || v_approvers.user_name);
1558 --debug_msg2 ('add user id: ' || v_approvers.person_name);
1559 --debug_msg2 ('add user id: ' || v_approvers.email_address);
1560 l_find_duplicate := 'N';
1561
1562 IF (Instr(l_role_users, v_reminders.user_name||',') = 1) THEN
1563 -- find duplicate
1564 l_find_duplicate := 'Y';
1565 ELSIF (Instr(l_role_users, ','||v_reminders.user_name||',') >0) THEN
1566 -- find duplicate
1567 l_find_duplicate := 'Y';
1568 ELSIF (Instr(l_role_users, ','||v_reminders.user_name) = (Length(l_role_users) - Length(v_reminders.user_name))) THEN
1569 -- find duplicate
1570 l_find_duplicate := 'Y';
1571
1572 END IF;
1573
1574
1575
1576 IF l_find_duplicate = 'N' THEN
1577
1578
1579 if (l_role_users is not null) then
1580 l_role_users := l_role_users || ',';
1581 end if;
1582
1583 -- Create adhoc users
1584
1585 wf_directory.getroleinfo(Upper(v_reminders.user_name),display_name,
1586 email_address,notification_preference,language,territory);
1587 if display_name is null THEN
1588 --IF NOT wf_directory.useractive (v_reminders.user_name) THEN
1589 --debug_msg('Add user');
1590
1591 WF_DIRECTORY.CreateAdHocUser( name => v_reminders.user_name
1592 , display_name => v_reminders.person_name
1593 --, notification_preference => 'MAILTEXT'
1594 , EMAIL_ADDRESS =>v_reminders.email_address);
1595 END IF;
1596
1597 l_role_users := l_role_users || v_reminders.user_name;
1598
1599 x_action_line_audit_tbl(l_index).reason_code := 'CONDITION_MET';
1600 x_action_line_audit_tbl(l_index).action_code := p_action_set_line_rec.action_code;
1601 x_action_line_audit_tbl(l_index).audit_display_attribute := v_reminders.person_name;
1602 x_action_line_audit_tbl(l_index).audit_attribute := v_reminders.user_name;
1603 x_action_line_audit_tbl(l_index).reversed_action_set_line_id := NULL;
1604 L_INDEX := L_INDEX +1 ;
1605 END IF;
1606
1607 end loop;
1608 ELSIF l_approver_source_type = 112 THEN
1609 -- source type is party
1610 for v_reminders in l_report_approver_csr_party loop
1611
1612 --debug_msg2 ('add user name: ' || v_approvers.user_name);
1613 --debug_msg2 ('add user id: ' || v_approvers.person_name);
1614 --debug_msg2 ('add user id: ' || v_approvers.email_address);
1615
1616 l_find_duplicate := 'N';
1617
1618 IF (Instr(l_role_users, v_reminders.user_name||',') = 1) THEN
1619 -- find duplicate
1620 l_find_duplicate := 'Y';
1621 ELSIF (Instr(l_role_users, ','||v_reminders.user_name||',') >0) THEN
1622 -- find duplicate
1623 l_find_duplicate := 'Y';
1624 ELSIF (Instr(l_role_users, ','||v_reminders.user_name) = (Length(l_role_users) - Length(v_reminders.user_name))) THEN
1625 -- find duplicate
1626 l_find_duplicate := 'Y';
1627
1628 END IF;
1629
1630
1631
1632 IF l_find_duplicate = 'N' THEN
1633
1634 if (l_role_users is not null) then
1635 l_role_users := l_role_users || ',';
1636 end if;
1637
1638 -- Create adhoc users
1639 wf_directory.getroleinfo(Upper(v_reminders.user_name),display_name,
1640 email_address,notification_preference,language,territory);
1641 if display_name is null THEN
1642 --IF NOT wf_directory.useractive (v_reminders.user_name) THEN
1643 --debug_msg('Add user');
1644
1645 WF_DIRECTORY.CreateAdHocUser( name => v_reminders.user_name
1646 , display_name => v_reminders.person_name
1647 --, notification_preference => 'MAILTEXT'
1648 , EMAIL_ADDRESS =>v_reminders.email_address);
1649 END IF;
1650
1651 l_role_users := l_role_users || v_reminders.user_name;
1652
1653 x_action_line_audit_tbl(l_index).reason_code := 'CONDITION_MET';
1654 x_action_line_audit_tbl(l_index).action_code := p_action_set_line_rec.action_code;
1655 x_action_line_audit_tbl(l_index).audit_display_attribute := v_reminders.person_name;
1656 x_action_line_audit_tbl(l_index).audit_attribute := v_reminders.user_name;
1657 x_action_line_audit_tbl(l_index).reversed_action_set_line_id := NULL;
1658 L_INDEX := L_INDEX +1 ;
1659 END IF;
1660
1661 end loop;
1662 END IF;
1663
1664 ELSE
1665 -- approver id = null else we use the HR manager
1666 CLOSE get_approver_source_id;
1667
1668 -- HR Manager of Project Manager
1669 -- Approver of the project status report setup
1670 for v_reminders in l_hr_manager_csr loop
1671
1672 --debug_msg2 ('add user name: ' || v_approvers.user_name);
1673 --debug_msg2 ('add user id: ' || v_approvers.person_name);
1674 --debug_msg2 ('add user id: ' || v_approvers.email_address);
1675
1676 l_find_duplicate := 'N';
1677
1678 IF (Instr(l_role_users, v_reminders.user_name||',') = 1) THEN
1679 -- find duplicate
1680 l_find_duplicate := 'Y';
1681 ELSIF (Instr(l_role_users, ','||v_reminders.user_name||',') >0) THEN
1682 -- find duplicate
1683 l_find_duplicate := 'Y';
1684 ELSIF (Instr(l_role_users, ','||v_reminders.user_name) = (Length(l_role_users) - Length(v_reminders.user_name))) THEN
1685 -- find duplicate
1686 l_find_duplicate := 'Y';
1687
1688 END IF;
1689
1690
1691
1692 IF l_find_duplicate = 'N' THEN
1693
1694 if (l_role_users is not null) then
1695 l_role_users := l_role_users || ',';
1696 end if;
1697
1698 -- Create adhoc users
1699 wf_directory.getroleinfo(Upper(v_reminders.user_name),display_name,
1700 email_address,notification_preference,language,territory);
1701 if display_name is null THEN
1702 --IF NOT wf_directory.useractive (v_reminders.user_name) THEN
1703
1704 --debug_msg('Add user');
1705
1706 WF_DIRECTORY.CreateAdHocUser( name => v_reminders.user_name
1707 , display_name => v_reminders.person_name
1708 --, notification_preference => 'MAILTEXT'
1709 , EMAIL_ADDRESS =>v_reminders.email_address);
1710 END IF;
1711 l_role_users := l_role_users || v_reminders.user_name;
1712 x_action_line_audit_tbl(l_index).reason_code := 'CONDITION_MET';
1713 x_action_line_audit_tbl(l_index).action_code := p_action_set_line_rec.action_code;
1714 x_action_line_audit_tbl(l_index).audit_display_attribute := v_reminders.person_name;
1715 x_action_line_audit_tbl(l_index).audit_attribute := v_reminders.user_name;
1716 x_action_line_audit_tbl(l_index).reversed_action_set_line_id := NULL;
1717 L_INDEX := L_INDEX +1 ;
1718 END IF;
1719
1720 end loop;
1721 END IF;
1722 ELSE
1723 -- the role id is passed in
1724 for v_reminders in l_missing_csr loop
1725
1726 --debug_msg2 ('add user name: ' || v_approvers.user_name);
1727 --debug_msg2 ('add user id: ' || v_approvers.person_name);
1728 --debug_msg2 ('add user id: ' || v_approvers.email_address);
1729
1730 l_find_duplicate := 'N';
1731
1732 IF (Instr(l_role_users, v_reminders.user_name||',') = 1) THEN
1733 -- find duplicate
1734 l_find_duplicate := 'Y';
1735 ELSIF (Instr(l_role_users, ','||v_reminders.user_name||',') >0) THEN
1736 -- find duplicate
1737 l_find_duplicate := 'Y';
1738 ELSIF (Instr(l_role_users, ','||v_reminders.user_name) = (Length(l_role_users) - Length(v_reminders.user_name))) THEN
1739 -- find duplicate
1740 l_find_duplicate := 'Y';
1741
1742 END IF;
1743
1744
1745
1746 IF l_find_duplicate = 'N' THEN
1747
1748 if (l_role_users is not null) then
1749 l_role_users := l_role_users || ',';
1750 end if;
1751
1752 -- Create adhoc users
1753 wf_directory.getroleinfo(Upper(v_reminders.user_name),display_name,
1754 email_address,notification_preference,language,territory);
1755 if display_name is null THEN
1756 --IF NOT wf_directory.useractive (v_reminders.user_name) THEN
1757
1758 --debug_msg('Add user');
1759
1760 WF_DIRECTORY.CreateAdHocUser( name => v_reminders.user_name
1761 , display_name => v_reminders.person_name
1762 , EMAIL_ADDRESS =>v_reminders.email_address);
1763 END IF;
1764 l_role_users := l_role_users || v_reminders.user_name;
1765 x_action_line_audit_tbl(l_index).reason_code := 'CONDITION_MET';
1766 x_action_line_audit_tbl(l_index).action_code := p_action_set_line_rec.action_code;
1767 x_action_line_audit_tbl(l_index).audit_display_attribute := v_reminders.person_name;
1768 x_action_line_audit_tbl(l_index).audit_attribute := v_reminders.user_name;
1769 x_action_line_audit_tbl(l_index).reversed_action_set_line_id := NULL;
1770 L_INDEX := L_INDEX +1 ;
1771 END IF;
1772
1773 end loop;
1774
1775 END IF;
1776
1777
1778
1779 IF (l_role_users is NOT NULL) THEN
1780 --debug_msg2('Add user role' || l_reporter_role);
1781 --debug_msg2('Add users' || l_reporter_role_users);
1782 WF_DIRECTORY.AddUsersToAdHocRole( l_reminder_role
1783 , l_role_users);
1784
1785
1786 wf_engine.SetItemAttrText( p_item_type
1787 , p_item_key
1788 , 'REPORT_REMINDER_NAME'
1789 , l_reminder_role);
1790
1791 ELSE
1792 NULL;
1793 END IF;
1794
1795 --debug_msg2 ('after add users to the role ' || l_approval_role );
1796
1797 commit;
1798
1799
1800
1801 EXCEPTION
1802
1803 WHEN OTHERS THEN
1804 RAISE;
1805
1806 END set_missing_report_notify;
1807
1808
1809 PROCEDURE forward_notification(
1810 itemtype IN VARCHAR2
1811 ,itemkey IN VARCHAR2
1812 ,actid IN NUMBER
1813 ,funcmode IN VARCHAR2
1814 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1815
1816 IS
1817
1818 l_comment VARCHAR2(2000);
1819 l_forward_to VARCHAR2(200);
1820 l_forward_to_name VARCHAR2(200);
1821 l_name VARCHAR2(200);
1822 l_user_id NUMBER;
1823
1824 l_error_msg VARCHAR2(2000);
1825
1826
1827 CURSOR is_user_valid
1828 IS
1829 SELECT user_id FROM
1830 fnd_user
1831 WHERE user_name = l_forward_to
1832 and trunc(sysdate) between start_date and nvl(end_date, sysdate); /* Bug#3848024 */
1833
1834
1835 CURSOR get_name
1836 IS
1837 -- select party_name from
1838 --(
1839 select hp.party_name
1840 from fnd_user fu,
1841 hz_parties hp
1842 where fu.user_name = l_forward_to --fnd_global.user_id
1843 and fu.employee_id is null
1844 and fu.person_party_id = hp.party_id -- Bug 4527617. Replaced customer_id with person_party_id.
1845 union
1846 select hp.party_name
1847 from fnd_user fu,
1848 hz_parties hp
1849 where fu.user_name = l_forward_to--fnd_global.user_id
1850 and fu.employee_id is not null
1851 and 'PER:' || fu.employee_id = hp.orig_system_reference;
1852 --);
1853
1854 display_name VARCHAR2(2000);
1855 email_address VARCHAR2(2000);
1856 notification_preference VARCHAR2(2000);
1857 language VARCHAR2(2000);
1858 territory VARCHAR2(2000);
1859 BEGIN
1860
1861 --debug_msg_s1 ('call forward AAAAAAAAAAA' || funcmode);
1862
1863 IF funcmode = 'RUN' then
1864 l_comment := wf_engine.GetItemAttrText
1865 ( itemtype => itemtype,
1866 itemkey => itemkey,
1867 aname => 'COMMENT');
1868
1869 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment' || funcmode);
1870 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment' || actid);
1871
1872
1873 l_forward_to := wf_engine.GetItemAttrText
1874 ( itemtype => itemtype,
1875 itemkey => itemkey,
1876 aname => 'FORWARD_TO_USERNAME_RESPONSE'); -- Changed 'FORWARD_TO' to 'FORWARD_TO_USERNAME_RESPONSE' for bug 4165780
1877
1878 OPEN is_user_valid;
1879 FETCH is_user_valid INTO l_user_id;
1880 IF is_user_valid%notfound THEN
1881 -- the forward to is invalid
1882 fnd_message.set_name ('PO', 'PO_WF_NOTIF_INVALID_FORWARD');
1883 l_error_msg := fnd_message.get;
1884
1885 wf_engine.SetItemAttrText
1886 ( itemtype,
1887 itemkey,
1888 'WRONG_FORWARD',
1889 l_error_msg);
1890
1891
1892 ELSE
1893 -- the forward is OK
1894 -- 1. change the notification party
1895 -- 2. save the comment to history table
1896 wf_directory.getroleinfo(Upper(l_forward_to),display_name,
1897 email_address,notification_preference,language,territory);
1898 if display_name is null THEN
1899 --IF NOT wf_directory.useractive (l_forward_to) THEN
1900
1901 --debug_msg('Add user');
1902
1903 WF_DIRECTORY.CreateAdHocUser( name => l_forward_to
1904 , display_name => l_forward_to
1905 --, notification_preference => 'MAILTEXT'
1906 , EMAIL_ADDRESS =>'');
1907 END IF;
1908
1909 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment: forward to = ' || l_forward_to);
1910
1911 wf_engine.SetItemAttrText( itemtype
1912 , itemkey
1913 , 'REPORT_APPROVER_NAME'
1914 , l_forward_to);
1915
1916
1917 OPEN get_name ;
1918 FETCH get_name INTO l_forward_to_name;
1919 CLOSE get_name;
1920
1921
1922 /* Start of Addition for bug 5027098 */
1923
1924 l_name := wf_engine.GetItemAttrText( itemtype
1925 , itemkey
1926 , 'REPORT_APPROVER_USER_NAME');
1927 wf_engine.SetItemAttrText ( itemtype,
1928 itemkey,
1929 'FROM_ROLE_VALUE',
1930 l_name);
1931 /* End of Addition for bug 5027098 */
1932
1933 l_name := wf_engine.GetItemAttrText( itemtype
1934 , itemkey
1935 , 'REPORT_APPROVER_FULL_NAME');
1936
1937
1938 wf_engine.SetItemAttrText( itemtype
1939 , itemkey
1940 , 'REPORT_APPROVER_USER_NAME'
1941 , l_forward_to);
1942
1943 /* Start of Addition for bug 4165780*/
1944 wf_engine.SetItemAttrText( itemtype
1945 , itemkey
1946 , 'REPORT_APPROVER_FULL_NAME'
1947 , l_forward_to_name);
1948 /* End of Addition for bug 4165780*/
1949
1950 --debug_msg_s1 ('forward AAAAAAAAAAA get approval comment: forward to = ' || l_forward_to_name);
1951
1952 pa_workflow_history.save_comment_history (
1953 itemtype
1954 ,itemkey
1955 ,'FORWARD'
1956 ,l_name
1957 ,l_comment);
1958
1959 wf_engine.SetItemAttrText
1960 ( itemtype,
1961 itemkey ,
1962 'COMMENT',
1963 ''
1964 );
1965
1966 wf_engine.SetItemAttrText( itemtype
1967 , itemkey
1968 , 'RESULT'
1969 , '');
1970
1971 wf_engine.SetItemAttrText( itemtype
1972 , itemkey
1973 , 'FORWARD_TO_USERNAME_RESPONSE' -- Changed 'FORWARD_TO' to 'FORWARD_TO_USERNAME_RESPONSE' for bug 4165780
1974 , '');
1975
1976 END IF;
1977
1978 CLOSE is_user_valid; -- #Bug 3905748
1979
1980
1981
1982
1983 END IF;
1984
1985
1986 resultout:='COMPLETE:'||'SUCCESS';
1987
1988 --resultout := wf_engine.eng_completed||':'||'T';
1989
1990
1991 END;
1992
1993 PROCEDURE post_notification(
1994 itemtype IN VARCHAR2
1995 ,itemkey IN VARCHAR2
1996 ,actid IN NUMBER
1997 ,funcmode IN VARCHAR2
1998 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1999
2000 IS
2001
2002 cursor result_codes is
2003 select wfl.lookup_code result_code
2004 from wf_lookups wfl,
2005 wf_activities wfa,
2006 wf_process_activities wfpa,
2007 wf_items wfi
2008 where wfl.lookup_type = wfa.result_type
2009 and wfa.name = wfpa.activity_name
2010 and wfi.begin_date >= wfa.begin_date
2011 and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
2012 and wfpa.activity_item_type = wfa.item_type
2013 and wfpa.instance_id = actid
2014 and wfi.item_key = itemkey
2015 and wfi.item_type = itemtype;
2016
2017 default_result varchar2(30) := '';
2018
2019 BEGIN
2020
2021 --debug_msg_s1 ('call forward AAAAAAAAAAA' || funcmode);
2022
2023
2024 for result_rec in result_codes LOOP
2025
2026 --debug_msg_s1 ('result' || result_rec.result_code);
2027
2028 default_result := result_rec.result_code;
2029
2030 END LOOP;
2031
2032 resultout := wf_engine.eng_completed||':'||default_result;
2033 --resultout := wf_engine.eng_completed||':'||'T';
2034
2035
2036 END;
2037
2038 PROCEDURE show_status_report
2039 (document_id IN VARCHAR2,
2040 display_type IN VARCHAR2,
2041 document IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2042 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2043 IS
2044
2045 l_reported_by_id NUMBER;
2046
2047 cursor pr_vals is
2048 select prval.version_id,prver.overview briefoverview, prval.attribute1 summary,prval.attribute2 issues,prval.attribute3 accomplishments,prval.attribute4 plans
2049 from pa_progress_report_vals prval, pa_progress_report_vers prver
2050 where prval.version_id = document_id
2051 and prver.version_id = document_id
2052 and region_code = 'PA_PROGRESS_GENERAL_TOP';
2053
2054 CURSOR get_report_info IS
2055 SELECT pprv.*, pl.meaning progress_status, papf.full_name FROM
2056 pa_progress_reports_v pprv,
2057 pa_lookups pl,
2058 pa_project_parties ppp,
2059 per_all_people_f papf
2060 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
2061 AND pl.lookup_code = pprv.progress_status_code
2062 and pprv.version_id = document_id
2063 AND pprv.object_type = 'PA_PROJECTS'
2064 and papf.person_id = ppp.resource_source_id
2065 AND pprv.reported_by = ppp.resource_id
2066 AND ppp.object_id = pprv.object_id
2067 and ppp.object_type = pprv.object_type
2068 and trunc(sysdate) between papf.effective_START_DATE and nvl(papf.effective_END_DATE, sysdate+1);
2069 /*
2070 SELECT pprv.*, pl.meaning progress_status FROM
2071 pa_progress_report_vers pprv,
2072 pa_lookups pl
2073 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
2074 AND pl.lookup_code = pprv.progress_status_code
2075 and pprv.version_id = document_id
2076 AND pprv.object_type = 'PA_PROJECTS';*/
2077
2078 CURSOR c_reporter_list IS
2079 select usr.user_id, usr.person_party_id, usr.user_name,papf.email_address,papf.full_name person_name -- Bug 4527617. Replaced customer_id with person_party_id.
2080 from per_all_people_f papf,
2081 fnd_user usr
2082 WHERE
2083 papf.person_id = usr.employee_id
2084 and trunc(sysdate)
2085 between papf.EFFECTIVE_START_DATE
2086 and Nvl(papf.effective_end_date, Sysdate + 1)
2087 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
2088 AND usr.user_id = l_reported_by_id;
2089
2090 BEGIN
2091
2092
2093 document :=
2094 '<table width=100% border=0 cellpadding=0 cellspacing=0><tr><td>
2095 <table sumarry="" width=70% align=LEFT border=0 cellpadding=3 cellspacing=1 bgcolor=white>';
2096 for gri in get_report_info loop
2097
2098 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report Type</font></th>';
2099 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2100 document := document || gri.report_type_name || ' ';
2101 document := document ||'</font></td></tr>';
2102
2103 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report Start Date</font></th>';
2104 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2105 document := document || gri.report_start_date || ' ';
2106 document := document ||'</font></td></tr>';
2107
2108 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report End Date</font></th>';
2109 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2110 document := document || gri.report_end_date || ' ';
2111 document := document ||'</font></td></tr>';
2112
2113 l_reported_by_id :=gri.reported_by;
2114
2115 --debug_msg_s1 ('Reported by = ' || l_reported_by_id);
2116
2117 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Progress Status</font></th>';
2118 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2119 document := document || gri.progress_status || ' ';
2120 document := document ||'</font></td></tr>';
2121
2122
2123 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Reported By</font></th>';
2124 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2125 document := document || gri.full_name || ' ';
2126 document := document ||'</font></td></tr>';
2127
2128 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Last Updated By</font></th>';
2129 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2130 document := document || gri.last_updated_by || ' ';
2131 document := document ||'</font></td></tr>';
2132
2133
2134 end loop;
2135
2136 for rec in pr_vals loop
2137
2138 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Brief Overview</font></th>';
2139 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2140 document := document || rec.briefoverview || ' ';
2141 document := document ||'</font></td></tr>';
2142
2143 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Summary</font></th>';
2144 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2145 document := document || rec.Summary || ' ';
2146 document := document ||'</font></td></tr>';
2147
2148 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Issues</font></th>';
2149 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2150 document := document || rec.issues || ' ';
2151 document := document ||'</font></td></tr>';
2152
2153 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Accomplishments</font></th>';
2154 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2155 document := document || rec.Accomplishments || ' ';
2156 document := document ||'</font></td></tr>';
2157
2158 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Plans</font></th>';
2159 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2160 document := document || rec.Plans || ' ';
2161 document := document ||'</font></td></tr>';
2162
2163
2164
2165
2166 end loop;
2167
2168 document := document || '</table></td></tr></table>';
2169
2170 document_type := 'text/html';
2171
2172 END show_status_report;
2173
2174
2175
2176 PROCEDURE show_status_report_cancel
2177 (document_id IN VARCHAR2,
2178 display_type IN VARCHAR2,
2179 document IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2180 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2181 IS
2182
2183 l_reported_by_id NUMBER;
2184 l_comment VARCHAR2(240);
2185 cursor pr_vals is
2186 select prval.version_id,prver.overview briefoverview, prval.attribute1 summary,prval.attribute2 issues,prval.attribute3 accomplishments,prval.attribute4 plans
2187 from pa_progress_report_vals prval, pa_progress_report_vers prver
2188 where prval.version_id = document_id
2189 and prver.version_id = document_id
2190 and region_code = 'PA_PROGRESS_GENERAL_TOP';
2191
2192 CURSOR get_report_info IS
2193 SELECT pprv.*, pl.meaning progress_status, papf.full_name FROM
2194 pa_progress_reports_v pprv,
2195 pa_lookups pl,
2196 pa_project_parties ppp,
2197 per_all_people_f papf
2198 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
2199 AND pl.lookup_code = pprv.progress_status_code
2200 and pprv.version_id = document_id
2201 AND pprv.object_type = 'PA_PROJECTS'
2202 and papf.person_id = ppp.resource_source_id
2203 AND pprv.reported_by = ppp.resource_id
2204 AND ppp.object_id = pprv.object_id
2205 and ppp.object_type = pprv.object_type
2206 and trunc(sysdate) between papf.effective_START_DATE and nvl(papf.effective_END_DATE, sysdate+1);
2207 /*
2208 SELECT pprv.*, pl.meaning progress_status FROM
2209 pa_progress_report_vers pprv,
2210 pa_lookups pl
2211 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
2212 AND pl.lookup_code = pprv.progress_status_code
2213 and pprv.version_id = document_id
2214 AND pprv.object_type = 'PA_PROJECTS';*/
2215
2216 /* CURSOR c_reporter_list IS
2217 select usr.user_id, usr.customer_id, usr.user_name,papf.email_address,papf.full_name person_name
2218 from per_all_people_f papf,
2219 fnd_user usr
2220 WHERE
2221 papf.person_id = usr.employee_id
2222 and trunc(sysdate)
2223 between papf.EFFECTIVE_START_DATE
2224 and Nvl(papf.effective_end_date, Sysdate + 1)
2225 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
2226 AND usr.user_id = l_reported_by_id;
2227 */
2228 BEGIN
2229
2230
2231 document :=
2232 '<table width=100% border=0 cellpadding=0 cellspacing=0><tr><td>
2233 <table sumarry="" width=70% align=LEFT border=0 cellpadding=3 cellspacing=1 bgcolor=white>';
2234 for gri in get_report_info loop
2235
2236 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report Type</font></th>';
2237 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2238 document := document || gri.report_type_name || ' ';
2239 document := document ||'</font></td></tr>';
2240
2241 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report Start Date</font></th>';
2242 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2243 document := document || gri.report_start_date || ' ';
2244 document := document ||'</font></td></tr>';
2245
2246 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report End Date</font></th>';
2247 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2248 document := document || gri.report_end_date || ' ';
2249 document := document ||'</font></td></tr>';
2250
2251 l_reported_by_id :=gri.reported_by;
2252
2253
2254
2255 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Progress Status</font></th>';
2256 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2257 document := document || gri.progress_status || ' ';
2258 document := document ||'</font></td></tr>';
2259
2260 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Reported By</font></th>';
2261 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2262 document := document || gri.full_name || ' ';
2263 document := document ||'</font></td></tr>';
2264
2265 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Last Updated By</font></th>';
2266 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2267 document := document || gri.last_updated_by || ' ';
2268 document := document ||'</font></td></tr>';
2269
2270
2271
2272 l_comment := gri.comments;
2273
2274
2275 end loop;
2276
2277 for rec in pr_vals loop
2278
2279 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Brief Overview</font></th>';
2280 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2281 document := document || rec.briefoverview || ' ';
2282 document := document ||'</font></td></tr>';
2283
2284 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Summary</font></th>';
2285 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2286 document := document || rec.Summary || ' ';
2287 document := document ||'</font></td></tr>';
2288
2289 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Issues</font></th>';
2290 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2291 document := document || rec.issues || ' ';
2292 document := document ||'</font></td></tr>';
2293
2294 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Accomplishments</font></th>';
2295 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2296 document := document || rec.Accomplishments || ' ';
2297 document := document ||'</font></td></tr>';
2298
2299 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Plans</font></th>';
2300 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2301 document := document || rec.Plans || ' ';
2302 document := document ||'</font></td></tr>';
2303
2304
2305
2306
2307 end loop;
2308
2309
2310 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Obsoletion Reason</font></th>';
2311 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2312 document := document || l_comment || ' ';
2313 document := document ||'</font></td></tr>';
2314
2315
2316
2317 document := document || '</table></td></tr></table>';
2318
2319 document_type := 'text/html';
2320
2321 END show_status_report_cancel;
2322
2323 PROCEDURE show_status_report_submit
2324 (document_id IN VARCHAR2,
2325 display_type IN VARCHAR2,
2326 document IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2327 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2328 IS
2329
2330 l_reported_by_id NUMBER;
2331 l_item_type VARCHAR2(200);
2332 l_item_key VARCHAR2(200);
2333 l_submitter_name VARCHAR2(200);
2334
2335 cursor pr_vals is
2336 select prval.version_id,prver.overview briefoverview, prval.attribute1 summary,prval.attribute2 issues,prval.attribute3 accomplishments,prval.attribute4 plans
2337 from pa_progress_report_vals prval, pa_progress_report_vers prver
2338 where prval.version_id = document_id
2339 and prver.version_id = document_id
2340 and region_code = 'PA_PROGRESS_GENERAL_TOP';
2341
2342 CURSOR get_report_info IS
2343 SELECT pprv.*, pl.meaning progress_status, papf.full_name FROM
2344 pa_progress_report_vers pprv,
2345 pa_lookups pl,
2346 pa_project_parties ppp,
2347 per_all_people_f papf
2348 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
2349 AND pl.lookup_code = pprv.progress_status_code
2350 and pprv.version_id = document_id
2351 AND pprv.object_type = 'PA_PROJECTS'
2352 and papf.person_id = ppp.resource_source_id
2353 AND pprv.reported_by = ppp.resource_id
2354 AND ppp.object_id = pprv.object_id
2355 and ppp.object_type = pprv.object_type
2356 and trunc(sysdate) between papf.effective_START_DATE and nvl(papf.effective_END_DATE, sysdate+1);
2357
2358 CURSOR get_wf_info is
2359 select max(item_type), max(item_key) from pa_wf_processes,
2360 pa_progress_report_vers pprv
2361 where wf_type_code = 'Progress Report'
2362 and entity_key2= document_id
2363 AND entity_key1 = pprv.object_id
2364 AND pprv.object_type = 'PA_PROJECTS'
2365 AND pprv.version_id = document_id;
2366
2367
2368 /*
2369 SELECT pprv.*, pl.meaning progress_status FROM
2370 pa_progress_report_vers pprv,
2371 pa_lookups pl
2372 where lookup_type like 'PROGRESS_SYSTEM_STATUS'
2373 AND pl.lookup_code = pprv.progress_status_code
2374 and pprv.version_id = document_id
2375 AND pprv.object_type = 'PA_PROJECTS';*/
2376
2377 /*
2378 CURSOR c_reporter_list IS
2379 select usr.user_id, usr.customer_id, usr.user_name,papf.email_address,papf.full_name person_name
2380 from per_all_people_f papf,
2381 fnd_user usr
2382 WHERE
2383 papf.person_id = usr.employee_id
2384 and trunc(sysdate)
2385 between papf.EFFECTIVE_START_DATE
2386 and Nvl(papf.effective_end_date, Sysdate + 1)
2387 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1)
2388 AND usr.user_id = l_reported_by_id;
2389 */
2390
2391 BEGIN
2392
2393
2394 document :=
2395 '<table width=100% border=0 cellpadding=0 cellspacing=0><tr><td>
2396 <table sumarry="" width=70% align=LEFT border=0 cellpadding=3 cellspacing=1 bgcolor=white>';
2397 for gri in get_report_info loop
2398
2399 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report Start Date</font></th>';
2400 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2401 document := document || gri.report_start_date || ' ';
2402 document := document ||'</font></td></tr>';
2403
2404 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Report End Date</font></th>';
2405 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2406 document := document || gri.report_end_date || ' ';
2407 document := document ||'</font></td></tr>';
2408
2409 l_reported_by_id :=gri.reported_by;
2410 /*
2411 for crl in c_reporter_list loop
2412
2413
2414 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cccc99><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif">Reported By</font></th>';
2415 document := document || '<td align=LEFT valign=baseline bgcolor=#f7f7e7><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2416 document := document || crl.person_name || ' ';
2417 document := document ||'</font></td></tr>';
2418
2419 end loop;
2420 */
2421
2422 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Progress Status</font></th>';
2423 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2424 document := document || gri.progress_status || ' ';
2425 document := document ||'</font></td></tr>';
2426
2427 end loop;
2428
2429 OPEN get_wf_info;
2430 FETCH get_wf_info INTO l_item_type, l_item_key;
2431 CLOSE get_wf_info;
2432
2433 l_submitter_name := wf_engine.GetItemAttrText( l_item_type
2434 , l_item_key
2435 , 'SUBMITTER_FULL_NAME');
2436
2437 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Submitted By</font></th>';
2438 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2439 document := document || l_submitter_name || ' ';
2440 document := document ||'</font></td></tr>';
2441
2442 for rec in pr_vals loop
2443
2444 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Brief Overview</font></th>';
2445 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2446 document := document || rec.briefoverview || ' ';
2447 document := document ||'</font></td></tr>';
2448
2449 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Summary</font></th>';
2450 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2451 document := document || rec.Summary || ' ';
2452 document := document ||'</font></td></tr>';
2453
2454 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Issues</font></th>';
2455 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2456 document := document || rec.issues || ' ';
2457 document := document ||'</font></td></tr>';
2458
2459 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Accomplishments</font></th>';
2460 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2461 document := document || rec.Accomplishments || ' ';
2462 document := document ||'</font></td></tr>';
2463
2464 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Plans</font></th>';
2465 document := document || '<td align=LEFT valign=baseline bgcolor=#f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2466 document := document || rec.Plans || ' ';
2467 document := document ||'</font></td></tr>';
2468
2469
2470
2471
2472 end loop;
2473 document := document || '</table></td></tr></table>';
2474
2475 document_type := 'text/html';
2476
2477 END show_status_report_submit;
2478
2479
2480 PROCEDURE show_project_info
2481 (document_id IN VARCHAR2,
2482 display_type IN VARCHAR2,
2483 document IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2484 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2485
2486 IS
2487
2488 CURSOR get_project_info(l_project_id number) IS
2489 SELECT
2490 /* Changed the query to base tables pa_projects_all and hr_all_organization_units instead of view
2491 pa_project_lists_v to improve performance. Bug :4940945 */
2492 ppa.name,
2493 PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID) customer_name,
2494 --project_type,
2495 --to_char(project_value) as project_value,
2496 --psi_projfunc_currency_code as project_currency_code,
2497 --person_id,
2498 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME(PPA.PROJECT_ID) person_name,
2499 ppa.segment1,
2500 --start_date,
2501 --completion_date,
2502 hou.name carrying_out_organization_name
2503 --project_status_name,
2504 --description
2505 FROM pa_projects_all ppa, hr_all_organization_units hou
2506 WHERE ppa.project_id = l_project_id
2507 and ppa.CARRYING_OUT_ORGANIZATION_ID = hou.ORGANIZATION_ID
2508 and rownum = 1;
2509 /* changes end for bug 4940945 */
2510 BEGIN
2511
2512 --debug_msg_s1('Project Id ' || document_id);
2513
2514 document :=
2515 '<table width=100% border=0 cellpadding=0 cellspacing=0><tr><td>
2516 <table sumarry="" width=70% align=LEFT border=0 cellpadding=3 cellspacing=1 bgcolor=white>';
2517
2518
2519 FOR rec IN get_project_info (document_id) LOOP
2520
2521
2522 --debug_msg_s1('Project Id 3' || document_id);
2523 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Project </font></th>';
2524 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2525 document := document || rec.name|| '(' || rec.segment1 || ')';
2526 document := document ||'</font></td></tr>';
2527
2528
2529 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Project Manager</font></th>';
2530 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2531 document := document || rec.person_name;
2532 document := document ||'</font></td></tr>';
2533
2534 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Organization</font></th>';
2535 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2536 document := document || rec.carrying_out_organization_name;
2537 document := document ||'</font></td></tr>';
2538
2539 document := document || '<tr><th scope=row width=40% align=RIGHT valign=baseline bgcolor=#cfe0f1><font color=#3c3c3c face="Arial, Helvetica, Geneva, sans-serif">Customer</font></th>';
2540 document := document || '<td align=LEFT valign=baseline bgcolor= #f2f2f5><font color=black face="Arial, Helvetica, Geneva, sans-serif">';
2541 document := document || rec.customer_name;
2542 document := document ||'</font></td></tr>';
2543
2544 /*
2545
2546 document := document ||
2547 htf.tableRowOpen;
2548
2549 document := document ||
2550 htf.tableData('Project Name');
2551
2552 document := document ||
2553 htf.tableData(rec.name);
2554
2555 document := document ||
2556 htf.tableRowClose;
2557
2558 document := document ||
2559 htf.tableRowOpen;
2560
2561 document := document ||
2562 htf.tableData('Project Manager');
2563
2564 document := document ||
2565 htf.tableData(rec.person_name);
2566
2567 document := document ||
2568 htf.tableRowClose;
2569
2570 document := document ||
2571 htf.tableRowOpen;
2572
2573 document := document ||
2574 htf.tableData('Organization');
2575
2576 document := document ||
2577 htf.tableData(rec.carrying_out_organization_name);
2578
2579 document := document ||
2580 htf.tableRowClose;
2581
2582 document := document ||
2583 htf.tableRowOpen;
2584
2585 document := document ||
2586 htf.tableData('Customer');
2587
2588 document := document ||
2589 htf.tableData(rec.customer_name);
2590
2591 document := document ||
2592 htf.tableRowClose;
2593 */
2594 END LOOP;
2595
2596 /*document := document ||
2597 htf.tableClose;*/
2598
2599 document := document ||'</table></td></tr></table>';
2600
2601 --debug_msg_s1('Docu = ' || document);
2602
2603 document_type := 'text/html';
2604
2605 END show_project_info;
2606
2607 PROCEDURE show_report_content
2608 (document_id IN VARCHAR2,
2609 display_type IN VARCHAR2,
2610 document IN OUT NOCOPY clob, --File.Sql.39 bug 4440895
2611 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2612 IS
2613
2614 l_content clob;
2615
2616
2617 CURSOR get_report_info IS
2618 /* SELECT pprv.report_content FROM
2619 pa_progress_report_vers pprv
2620 where
2621 pprv.version_id = document_id
2622 AND pprv.object_type = 'PA_PROJECTS'*/
2623 select PAGE_CONTENT from PA_PAGE_CONTENTS
2624 where object_Type = 'PA_PROGRESS_REPORTS'
2625 and pk1_value = document_id;
2626
2627 l_size number;
2628
2629 l_chunk_size pls_integer:=10000;
2630 l_copy_size int;
2631 l_pos int := 0;
2632
2633 l_line varchar2(30000) := '' ;
2634
2635 --Bug 3787169
2636 l_return_status varchar2(1);
2637 l_msg_count number;
2638 l_msg_data varchar2(2000);
2639
2640 BEGIN
2641 --debug_msg_s1 ('get clob content');
2642 open get_report_info;
2643 fetch get_report_info into l_content;
2644
2645 IF (get_report_info%found) then
2646 close get_report_info;
2647 -- parse the retrieved clob data
2648
2649 l_size := dbms_lob.getlength(l_content);
2650 --debug_msg_s1 ('get clob content size' || l_size);
2651
2652 l_pos := 1;
2653 l_copy_size := 0;
2654
2655 --debug_msg_s1 ('in loop size 1' || l_copy_size);
2656 --debug_msg_s1 ('in loop size 2' || l_chunk_size);
2657
2658 while l_copy_size < l_size loop
2659
2660 --debug_msg_s1 ('before read ');
2661
2662 dbms_lob.read(l_content,l_chunk_size,l_pos,l_line);
2663
2664 -- debug_msg_s1 (l_line);
2665 --debug_msg_s1 ('in loop size 1' || l_copy_size);
2666
2667 dbms_lob.write(document,l_chunk_size,l_pos,l_line);
2668
2669 l_copy_size := l_copy_size + l_chunk_size;
2670 l_pos := l_pos + l_chunk_size;
2671 end loop;
2672
2673 /*
2674 Bug 3787169. The following api is called so as to clean the html for the class
2675 attribute.
2676 */
2677 pa_workflow_utils.modify_wf_clob_content(
2678 p_document => document
2679 ,x_return_status => l_return_status
2680 ,x_msg_count => l_msg_count
2681 ,x_msg_data => l_msg_data
2682 );
2683 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
2684 WF_NOTIFICATION.WriteToClob(document, 'Report Content Generation failed');
2685 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
2686 end if;
2687
2688 --debug_msg_s1 ('total copy size' || l_copy_size);
2689
2690 --dbms_lob.writeappend(document, 5, '12345');
2691 else
2692 close get_report_info;
2693 END IF;
2694
2695 document_type := 'text/html';
2696 EXCEPTION
2697 WHEN OTHERS THEN
2698
2699 --debug_msg_s1('Error '||TO_CHAR(SQLCODE)||': '||substr(Sqlerrm, 255));
2700
2701 WF_NOTIFICATION.WriteToClob(document, 'Report Content Generation failed');
2702 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
2703
2704 END show_report_content;
2705
2706 END pa_progress_report_WORKFLOW;
2707