[Home] [Help]
PACKAGE BODY: APPS.PSP_ER_WORKFLOW
Source
1 PACKAGE BODY PSP_ER_WORKFLOW as
2 /* $Header: PSPERWFB.pls 120.11.12010000.7 2008/08/05 10:12:54 ubhat ship $ */
3 procedure fatal_err_occured(itemtype in varchar2,
4 itemkey in varchar2,
5 actid in number,
6 funcmode in varchar2,
7 result out nocopy varchar2) is
8
9 l_request_id integer;
10 l_retry_request_id NUMBER(15, 0);
11 cursor fatal_error_cursor is
12 select count(*)
13 from psp_report_errors
14 where request_id = l_request_id
15 and (l_retry_request_id = -1
16 OR retry_request_id = l_retry_request_id)
17 and message_level = 'E'; --- count fatal errors that require
18 --- to stop the process.
19 cursor count_er is
20 select count(*)
21 from psp_eff_reports
22 where status_code IN ('N', 'A')
23 and request_id = l_request_id;
24
25 l_er_count integer;
26 l_error_count integer;
27 begin
28 ---hr_utility.trace_on('Y','WF-1');
29 l_request_id :=
30 wf_engine.GetItemAttrText(itemtype => itemtype,
31 itemkey => itemkey,
32 aname => 'REQUEST_ID');
33
34 l_retry_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
35 itemkey => itemkey,
36 aname => 'RETRY_REQUEST_ID');
37
38 hr_utility.trace('er_workflow --> FATAL error .. request_id = '||l_request_id);
39 open fatal_error_cursor;
40 fetch fatal_error_cursor into l_error_count;
41 close fatal_error_cursor;
42
43 hr_utility.trace('er_workflow --> FATAL error .. count = '||l_er_count);
44 open count_er;
45 fetch count_er into l_er_count;
46 close count_er;
47
48 hr_utility.trace('er_workflow --> FATAL error .. count = '||l_error_count);
49 if (l_er_count + l_error_count) = 0 then
50 result := 'COMPLETE:NOERRORNOREPORT';
51 elsif (l_er_count > 0) AND (l_error_count > 0) THEN
52 result := 'COMPLETE:ERRORSANDREPORTS';
53 elsif (l_er_count = 0) AND (l_error_count > 0) THEN
54 result := 'COMPLETE:ALLERRORSNOREPORT';
55 elsif (l_er_count > 0) AND (l_error_count = 0) THEN
56 result := 'COMPLETE:NOERRORALLREPORTS';
57 end if;
58
59 exception
60 when others then
61 -- populate stack with error message
62 result := 'ERROR';
63 wf_core.context('PSP_EFFORT_REPORTS', 'FATAL_ERR_OCCURED', itemtype, itemkey, to_char(actid), funcmode);
64 raise;
65 end;
66
67 /* Main Procedure */
68 procedure pyugen_er_workflow(pactid in number) as
69 cursor get_request_id is
70 select request_id
71 from pay_payroll_actions
72 where pactid = payroll_action_id;
73 l_request_id number;
74
75 CURSOR orig_request_id_cur IS
76 SELECT paa.request_id
77 FROM fnd_concurrent_requests fcr,
78 pay_payroll_actions paa
79 WHERE fcr.request_id = l_request_id
80 AND paa.payroll_action_id = TO_NUMBER(fcr.argument2);
81
82 CURSOR conc_program_name_cur IS
83 SELECT fcp.concurrent_program_name
84 FROM fnd_concurrent_programs fcp,
85 fnd_concurrent_requests fcr
86 WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
87 AND fcr.request_id = l_request_id;
88
89 CURSOR original_request_id_cur IS
90 SELECT request_id
91 FROM psp_report_templates_h
92 WHERE payroll_action_id = pactid;
93
94 l_return_status CHAR(1);
95 l_original_request_id NUMBER(15);
96 l_program_name VARCHAR2(30);
97 begin
98
99 ---fnd_file.put_line(fnd_file.log,'RUNNING THE DEINIT');
100 open get_request_id;
101 fetch get_request_id into l_request_id;
102 close get_request_id;
103
104 OPEN conc_program_name_cur;
105 FETCH conc_program_name_cur INTO l_program_name;
106 CLOSE conc_program_name_cur;
107
108 IF (l_program_name <> 'PSPRTEF') THEN
109 start_initiator_wf(l_request_id);
110
111 psp_xmlgen.update_er_error_details (p_request_id => l_request_id,
112 p_retry_request_id => NULL,
113 p_return_status => l_return_status);
114 ELSE
115 OPEN original_request_id_cur;
116 FETCH original_request_id_cur INTO l_original_request_id;
117 CLOSE original_request_id_cur;
118
119 psp_xmlgen.update_er_error_details (p_request_id => l_original_request_id,
120 p_retry_request_id => l_request_id,
121 p_return_status => l_return_status);
122 END IF;
123
124 -- Moved teh deletion of temporary table to submit and Purge code paths.
125 /* Following statement added to purge psp_selected_persons_t in dinit code, when run is successful * /
126 DELETE from psp_selected_persons_t WHERE request_id=l_request_id and not exists
127 (select 1 from pay_payroll_actions where payroll_action_id =pactid and action_status
128 ='E') ;
129 -- End of comment *****/
130
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN NULL;
133
134
135
136 end;
137
138 procedure record_initiator_apprvl(itemtype in varchar2,
139 itemkey in varchar2,
140 actid in number,
141 funcmode in varchar2,
142 result out nocopy varchar2) is
143 l_request_id integer;
144 cursor check_pre_approved_cur is
145 select 1
146 from psp_report_templates_h
147 where request_id = l_request_id
148 and approval_type = 'PRE';
149 l_flag integer;
150 begin
151 l_request_id :=
152 wf_engine.GetItemAttrText(itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'REQUEST_ID');
155 update psp_report_templates_h
156 set initiator_accept_flag = 'Y'
157 where request_id = l_request_id;
158
159 open check_pre_approved_cur;
160 fetch check_pre_approved_cur into l_flag;
161 if check_pre_approved_cur%found then
162 update psp_eff_reports
163 set status_code = 'A',
164 last_update_date = sysdate,
165 last_update_login = fnd_global.login_id,
166 last_updated_by = fnd_global.user_id
167 where request_id = l_request_id;
168 end if;
169 close check_pre_approved_cur;
170
171 -- Following statement added to purge psp_selected_persons_t in dinit code, when run is successful
172 DELETE FROM psp_selected_persons_t
173 WHERE request_id=l_request_id;
174 exception
175 when others then
176 -- populate stack with error message
177 result := 'ERROR';
178 wf_core.context('PSP_EFFORT_REPORTS', 'RECORD_INITIATOR_APPRVL', itemtype, itemkey, to_char(actid), funcmode);
179 raise;
180
181 end;
182
183 procedure record_initiator_rjct(itemtype in varchar2,
184 itemkey in varchar2,
185 actid in number,
186 funcmode in varchar2,
187 result out nocopy varchar2) is
188 l_request_id integer;
189 begin
190 l_request_id :=
191 wf_engine.GetItemAttrText(itemtype => itemtype,
192 itemkey => itemkey,
193 aname => 'REQUEST_ID');
194 update psp_report_templates_h
195 set initiator_accept_flag = 'N'
196 where request_id = l_request_id;
197 exception
198 when others then
199 -- populate stack with error message
200 result := 'ERROR';
201 wf_core.context('PSP_EFFORT_REPORTS', 'RECORD_INITIATOR', itemtype, itemkey, to_char(actid), funcmode);
202 raise;
203 end;
204
205 procedure init_approvals(itemtype in varchar2,
206 itemkey in varchar2,
207 actid in number,
208 funcmode in varchar2,
209 result out nocopy varchar2) is
210
211 -- Cursor added for bug 4106614: Effort Manual Override
212 cursor effort_manual_override_cur(p_request_id integer) is
213 select MANUAL_ENTRY_OVERRIDE_FLAG
214 from psp_report_templates_h where request_id = p_request_id;
215
216 cursor wf_item_key_cur(p_request_id integer) is
217 select wf_role_name,
218 psp_wf_item_key_s.nextval
219 from (select era.wf_role_name
220 from psp_eff_report_approvals era,
221 psp_eff_reports er,
222 psp_eff_report_details erd
223 where erd.effort_report_id = er.effort_report_id
224 and erd.effort_report_detail_id = era.effort_report_detail_id
225 and era.approval_status = 'P'
226 and era.approver_order_num = 1
227 and er.request_id = p_request_id
228 and er.status_code = 'N'
229 group by wf_role_name);
230
231
232 type t_integer is table of number(15) index by binary_integer;
233 type t_varchar2 is table of varchar2(320) index by binary_integer;
234 wf_rname_array t_varchar2;
235 wf_ikey_array t_integer;
236 k integer;
237 l_initiator_rname wf_roles.name%type;
238 l_request_id integer;
239 l_start_date varchar2(50);
240 l_end_date varchar2(50);
241 l_template_name varchar2(200);
242 l_time_out integer;
243 l_param_string varchar2(1000);
244 effort_manual_override_flag varchar2(10); --added for bug 4106614: Effort Manual Override
245
246 begin
247 ---hr_utility.trace_on('Y','WF-1');
248 hr_utility.trace('er_workflow --> 100');
249 if funcmode = 'RUN' then
250 l_initiator_rname :=
251 wf_engine.GetItemAttrText(itemtype => itemtype,
252 itemkey => itemkey,
253 aname => 'INITIATOR');
254
255 l_request_id :=
256 wf_engine.GetItemAttrText(itemtype => itemtype,
257 itemkey => itemkey,
258 aname => 'REQUEST_ID');
259
260 l_start_date :=
261 wf_engine.GetItemAttrText(itemtype => itemtype,
262 itemkey => itemkey,
263 aname => 'START_DATE');
264 l_end_date :=
265 wf_engine.GetItemAttrText(itemtype => itemtype,
266 itemkey => itemkey,
267 aname => 'END_DATE');
268
269 l_template_name :=
270 wf_engine.GetItemAttrText(itemtype => itemtype,
271 itemkey => itemkey,
272 aname => 'TEMPLATE_NAME');
273
274 l_time_out :=
275 wf_engine.GetItemAttrNumber(itemtype => itemtype,
276 itemkey => itemkey,
277 aname => 'TIMEOUT');
278
279 l_param_string :=
280 wf_engine.GetItemAttrText(itemtype => itemtype,
281 itemkey => itemkey,
282 aname => 'CONC_PARAM_STRING');
283
284
285 --- approval records will be created in the thread
286 --- get distinct role names from approvals table.
287 open wf_item_key_cur(l_request_id);
288 fetch wf_item_key_cur bulk collect into wf_rname_array, wf_ikey_array;
289 close wf_item_key_cur;
290
291 if wf_rname_array.count = 0 then
292 hr_utility.trace('er_workflow --> count =0 req_id='||to_char(l_request_id));
293 --dbms_output.put_line('INIT APPROVALS:no recs for approval.. raise error');
294 ---- raise error here, with appropriate error;
295 RAISE fnd_api.g_exc_unexpected_error;
296 end if;
297
298 forall k in 1..wf_rname_array.count
299 update psp_eff_report_approvals
300 set wf_item_key = wf_ikey_array(k)
301 where wf_role_name = wf_rname_array(k)
302 and effort_report_detail_id in
303 (select erd.effort_report_detail_id
304 from psp_eff_reports er,
305 psp_eff_report_details erd
306 where erd.effort_report_id = er.effort_report_id
307 and er.request_id = l_request_id
308 and er.status_code = 'N');
309 k := 1;
310 hr_utility.trace('er_workflow --> LOOP START ');
311 loop
312 hr_utility.trace('er_workflow --> INSIDE LOOP ');
313 if k > wf_rname_array.count then
314 exit;
315 end if;
316 -- call workflow
317 --dbms_output.put_line('FROM INIT - item key ='||wf_ikey_array(k));
318
319 wf_engine.CreateProcess(itemtype => 'PSPERAVL',
320 itemkey => wf_ikey_array(k),
321 process => 'APPROVER_WORKFLOW');
322
323 /*Added for bug 7004679 */
324 wf_engine.setitemowner(itemtype => 'PSPERAVL',
325 itemkey => wf_ikey_array(k),
326 owner => wf_rname_array(k));
327
328
329 wf_engine.SetItemParent(itemType => 'PSPERAVL',
330 itemKey => wf_ikey_array(k),
331 parent_ItemType => itemType,
332 parent_ItemKey => itemKey,
333 parent_context => null);
334
335 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
336 itemkey => wf_ikey_array(k),
337 aname => 'REQUEST_ID',
338 avalue => l_request_id);
339
340 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
341 itemkey => wf_ikey_array(k),
342 aname => 'APPROVER_ORDER_NUM',
343 avalue => 1);
344
345 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
346 itemkey => wf_ikey_array(k),
347 aname => 'TIMEOUT',
348 avalue => l_time_out);
349
350 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
351 itemkey => wf_ikey_array(k),
352 aname => 'INITIATOR',
353 avalue => l_initiator_rname);
354
355 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
356 itemkey => wf_ikey_array(k),
357 aname => 'ITEM_KEY',
358 avalue => wf_ikey_array(k));
359
360 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
361 itemKey => wf_ikey_array(k),
362 aname => 'RECEIVER_FLAG',
363 avalue => 'AR');
364
365 -- Fix for bug 4106614 : Effort Manual Override START
366 open effort_manual_override_cur(l_request_id);
367 fetch effort_manual_override_cur into effort_manual_override_flag;
368 close effort_manual_override_cur;
369
370 if effort_manual_override_flag = 'N' then
371 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
372 itemKey => wf_ikey_array(k),
373 aname => 'EFFORT_MANUAL_OVERRIDE',
374 avalue => null);
375 /* else -- else part is defaulted in psperavl.wft
376 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
377 itemKey => wf_ikey_array(k),
378 aname => 'EFFORT_MANUAL_OVERRIDE',
379 avalue => 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/psp/effortreporting/workflow/webui/EffManualOverridePG&akRegionApplicationId=8403&requestId=-&MSG_REQUEST_ID-&wfItemKey=-&MSG_IKEY-&wfRoleName=-&APPROVER_ROLE-&approverOrderNum=-&APPROVER_ORDER-');
380 */
381 end if;
382 -- Fix for bug 4106614 : Effort Manual Override END
383
384 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
388
385 itemkey => wf_ikey_array(k),
386 aname => '#ATTACHMENTS',
387 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || wf_ikey_array(k));
389 --Bug 7135471
390 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
391 itemKey => wf_ikey_array(k),
392 aname => 'PDF_ATTACHMENT',
393 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || wf_ikey_array(k));
394
395 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
396 itemkey => wf_ikey_array(k),
397 aname => 'START_DATE',
398 avalue => l_start_date);
399
400 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
401 itemkey => wf_ikey_array(k),
402 aname => 'END_DATE',
403 avalue => l_end_date);
404
405 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
406 itemkey => wf_ikey_array(k),
407 aname => 'TEMPLATE_NAME',
408 avalue => l_template_name);
409
410
411 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
412 itemkey => wf_ikey_array(k),
413 aname => 'CONC_PARAM_STRING',
414 avalue => l_param_string);
415 k := k + 1;
416 end loop;
417 k := 1;
418 loop
419 if k > wf_rname_array.count then
420 exit;
421 end if;
422
423 wf_engine.StartProcess(itemtype => 'PSPERAVL',
424 itemkey => wf_ikey_array(k));
425 k := k + 1;
426 end loop;
427 wf_ikey_array.delete;
428
429 hr_utility.trace('er_workflow --> 1000 ');
430 result := 'COMPLETE';
431 end if; --- funcmode = RUN
432
433 result := 'COMPLETE';
434 exception
435 when others then
436 hr_utility.trace('er_workflow --> err '||sqlerrm);
437 result := 'ERROR';
438 wf_core.context('PSP_EFFORT_REPORTS', 'INIT_APPROVALS', itemtype, itemkey, to_char(actid), funcmode);
439 raise;
440 end;
441
442 procedure purge_er(itemtype in varchar2,
443 itemkey in varchar2,
444 actid in number,
445 funcmode in varchar2,
446 result out nocopy varchar2)
447 is
448 l_request_id integer;
449 begin
450
451 if (funcmode = 'RUN') then
452 l_request_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
453 itemkey => itemkey,
454 aname => 'REQUEST_ID');
455 delete psp_eff_report_approvals
456 where effort_report_detail_id in
457 (select effort_report_detail_id
458 from psp_eff_report_details
459 where effort_report_id in
460 (select effort_report_id
461 from psp_eff_reports
462 where request_id = l_request_id));
463
464 delete psp_eff_report_details
465 where effort_report_id in
466 (select effort_report_id
467 from psp_eff_reports
468 where request_id = l_request_id);
469
470 delete psp_eff_reports
471 where request_id = l_request_id;
472
473 delete psp_report_errors
474 where request_id = l_request_id;
475
476 DELETE fnd_lobs fl
477 WHERE fl.file_id IN (SELECT fdl.media_id
478 FROM fnd_attached_documents fad,
479 fnd_documents_vl fdl
480 WHERE fad.pk1_value = itemkey
481 AND fdl.document_id = fad.document_id
482 AND fad.entity_name = 'ERDETAILS');
483
484 DELETE fnd_lobs fl
485 WHERE fl.file_id IN (SELECT fdl.media_id
486 FROM fnd_attached_documents fad,
487 fnd_documents_vl fdl
488 WHERE fad.pk1_value IN (SELECT wf_item_key
489 FROM psp_eff_report_approvals pera
490 WHERE pera.effort_report_detail_id IN (SELECT perd.effort_report_detail_id
491 FROM psp_eff_report_details perd
492 WHERE perd.effort_report_id IN (SELECT per.effort_report_id
493 FROM psp_eff_reports per
494 WHERE per.request_id = l_request_id)))
495 AND fdl.document_id = fad.document_id
496 AND fad.entity_name = 'ERDETAILS');
497
498 -- Following statement added to purge psp_selected_persons_t in dinit code, when run is successful
499 DELETE FROM psp_selected_persons_t
500 WHERE request_id=l_request_id;
501
502 result := 'COMPLETE';
503 end if;
504
505 exception
506 when others then
507 result := 'ERROR';
508 wf_core.context('PSP_EFFORT_REPORTS', 'PURGE_ER', itemtype, itemkey, to_char(actid), funcmode);
509 raise;
510 end;
511
512 /* GET_NEXT_APPROVER:
513 PURPOSE: Get the next approver from AME
514
515 HISTORY:
516 Aug-1, 2004
517 Following scenarios arise:-
518 A notification can contain many ER details records, and relate to
519 many AME transaction IDs. Approvers are found for each txn id.
520
521 1) AB, A : then B will continue with same item key- Approver FOUND
525
522 2) AB, AC : create 2 new item keys, one for B and other C- FORK
523 3) A, A : approver NOT FOUND.
524 4) AB, AC, A : Then FORK
526 In short following is the precedence
527 FORK (supercedes)--> FOUND (supercedes)--> NOT_FOUND
528
529 ** Sep-2, 2004
530 Removed the notification Forking. As a business rule, user will always
531 identify the lowest notification unit. For example, if there is
532 A, S --- ERD1
533 A, X --- ERD2
534 then user will rearrange it in AME to get following approval sequence
535 S, A -- ERD1
536 X, A -- ERD2
537 Also if user has
538 A, S
539 A
540 Then same notification can be sent to S also. Assume S, X are special
541 approvals.
542
543 However if the user does not re-arrange the approvers into SA, XA then
544 but instead has set up as AS, AX then S and X will be sent their respective
545 notifications, however WF thread will wait till both finish before going
546 to next level if there is one.
547
548 JUSTIFICATION for the business rule that AS, AX should be modified into SA, XA
549 is that S, A, X are horizontal approvers, there is no
550 pyramid type of approvals, or no clear cut hierarchy
551
552 1) AB, AC : should not arise due to new business rule restrictions,
553 if it does arise then , B and C will recieve separate
554 notifications in sequcen */
555
556 procedure get_next_approver(itemtype in varchar2,
557 itemkey in varchar2,
558 actid in number,
559 funcmode in varchar2,
560 result out nocopy varchar2) is
561
562 l_approver_order_num integer;
563 l_role_name varchar2(320);
564 l_role_display_name varchar2(360);
565 l_user_id fnd_user.user_id%type;
566 l_login_id number;
567
568 cursor role_name_cur is
569 select wf_role_name
570 from psp_eff_report_approvals
571 where wf_item_key = itemkey
572 and rownum = 1
573 and approver_order_num = 1;
574
575 cursor ame_txn_id_cur is
576 select distinct erd.ame_transaction_id
577 from psp_eff_report_details erd
578 where erd.effort_report_detail_id in
579 (select era.effort_report_detail_id
580 from psp_eff_report_approvals era
581 where wf_item_key = itemkey
582 and approval_status = 'A');
583
584 --- same level unapproved ER details
585 cursor same_approval_level_cur(p_approver_order_num in integer) is
586 select wf_role_name,
587 wf_role_display_name
588 from psp_eff_report_approvals
589 where wf_item_key = itemkey
590 and approver_order_num = p_approver_order_num
591 and approval_status = 'P'
592 and rownum = 1;
593
594 type t_integer is table of number(15) index by binary_integer;
595 type t1_varchar2 is table of varchar2(320) index by binary_integer;
596 type t2_varchar2 is table of varchar2(50) index by binary_integer;
597
598 wf_rname_array t1_varchar2;
599 app_order_num_array t_integer;
600 ame_txn_id_array t2_varchar2;
601 i integer;
602 l_next_approver ame_util.approversTable2;
603 l_wf_rname varchar2(320);
604 l_fork_flag varchar2(1);
605 l_request_id integer;
606 l_process_complete varchar2(10);
607 l_temp_not_found varchar2(40); --- to indicate atleast
608 -- some ER details did not have approver.
609 l_jsp_string varchar2(4000);
610 l_same_approval_level varchar2(1);
611
612 l_return_status CHAR(1);
613
614 l_approval_type varchar2(10);
615
616 cursor get_approval_type is
617 select approval_type
618 from psp_Report_templates_h
619 where request_id = l_request_id;
620
621 begin
622 hr_utility.trace(' Entering GET_NEXT_APPROVER');
623 l_same_approval_level := 'N';
624 ---hr_utility.trace_on('Y','WF-1');
625
626 l_user_id := fnd_global.user_id;
627 l_login_id := fnd_global.login_id;
628 if funcmode = 'RUN' then
629 l_approver_order_num :=
630 wf_engine.GetItemAttrNumber(itemtype => itemtype,
631 itemkey => itemkey,
632 aname => 'APPROVER_ORDER_NUM');
633 l_request_id :=
634 wf_engine.GetItemAttrNumber(itemtype => itemtype,
635 itemkey => itemkey,
636 aname => 'REQUEST_ID');
637 open get_approval_type;
638 fetch get_approval_type into l_approval_type;
639 close get_approval_type;
640
641 --- all ame transactions have been grouped based on the first
642 --- approver. Since approver is already avaialable, no need to call AME
643 if l_approver_order_num = 1 then
644 open role_name_cur;
645 fetch role_name_cur into l_wf_rname;
646 close role_name_cur;
647 l_role_display_name := wf_directory.GetRoleDisplayName(l_wf_rname);
648 result := 'COMPLETE:FOUND';
649 elsif l_approval_type in ('PMG', 'TMG', 'GPI') then
650 update psp_eff_reports er
651 set er.status_code = 'A',
652 er.last_update_date = sysdate,
656 and er.effort_report_id in
653 er.last_update_login = fnd_global.login_id,
654 er.last_updated_by = fnd_global.user_id
655 where er.status_code = 'N'
657 (select erd.effort_report_id
658 from psp_eff_report_details erd,
659 psp_eff_report_approvals era
660 where era.effort_report_detail_id = erd.effort_report_detail_id
661 and era.wf_item_key = itemkey)
662 and not exists
663 (select 1
664 from psp_eff_report_approvals era,
665 psp_eff_report_details erd
666 where era.effort_report_detail_id = erd.effort_report_detail_id
667 and erd.effort_report_id = er.effort_report_id
668 and era.approval_status <> 'A');
669 result := 'COMPLETE:NOTFOUND';
670
671 else
672 open same_approval_level_cur(l_approver_order_num);
673 fetch same_approval_level_cur into l_wf_rname, l_role_display_name;
674 if same_approval_level_cur%notfound then
675 l_same_approval_level := 'N';
676 else
677 l_same_approval_level := 'Y';
678 result := 'COMPLETE:FOUND';
679 end if;
680 close same_approval_level_cur;
681 if l_same_approval_level = 'N' then
682 open ame_txn_id_cur;
683 fetch ame_txn_id_cur bulk collect into ame_txn_id_array;
684 close ame_txn_id_cur;
685 i := 1;
686 loop
687 if i > ame_txn_id_array.count then
688 exit;
689 end if;
690 ame_api2.getNextApprovers4(applicationidIn => 8403,
691 transactiontypeIn => 'PSP-ER-APPROVAL',
692 transactionIdIn => ame_txn_id_array(i),
693 flagApproversAsNotifiedIn => 'Y',
694 approvalProcessCompleteYNout => l_process_complete,
695 nextApproversOut=> l_next_approver);
696 --- ignoring parallel approvers, assuming only one approver role
697 if l_next_approver.count = 0 then
698 if result is null then
699 result := 'COMPLETE:NOTFOUND';
700 l_temp_not_found := 'Y';
701 end if;
702 else
703 l_wf_rname := l_next_approver(1).name;
704 l_role_display_name := l_next_approver(1).display_name;
705 result := 'COMPLETE:FOUND';
706 insert into psp_eff_report_approvals
707 (effort_report_approval_id,
708 effort_report_detail_id,
709 wf_role_name,
710 wf_orig_system_id,
711 wf_orig_system,
712 approver_order_num,
713 approval_status,
714 wf_item_key,
715 last_update_date,
716 last_updated_by,
717 last_update_login,
718 creation_date,
719 created_by,
720 wf_role_display_name,
721 object_version_number)
722 select psp_eff_report_approvals_s.nextval,
723 erd.effort_report_detail_id,
724 l_next_approver(1).name,
725 l_next_approver(1).orig_system_id,
726 l_next_approver(1).orig_system,
727 l_approver_order_num,
728 nvl(l_next_approver(1).approval_status,'P'),
729 itemkey,
730 sysdate,
731 l_user_id,
732 l_login_id,
733 sysdate,
734 l_user_id,
735 l_next_approver(1).display_name,
736 1
737 from psp_eff_report_details erd
738 where erd.ame_transaction_id = ame_txn_id_array(i)
739 and erd.effort_report_id in
740 (select er.effort_report_id
741 from psp_eff_reports er
742 where er.request_id = l_request_id
743 and er.status_code = 'N');
744 hr_utility.trace(' ER workflow -> order number = '||l_next_approver(1).approver_order_number);
745 -- copy the previous approvers overwrites to the new approver
746 if l_next_approver(1).approver_order_number > 1 then
747 update psp_eff_report_approvals A1
748 set (A1.actual_cost_share, A1.overwritten_effort_percent, comments,
749 /* Add DF Columns for Hospital Effort report */
750 pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
751 pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
752 pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
753 eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
754 eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
755 eff_information11, eff_information12, eff_information13, eff_information14, eff_information15) =
759 pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
756 (select A2.actual_cost_share, A2.overwritten_effort_percent, comments,
757 /* Add DF Columns for Hospital Effort report */
758 pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
760 pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
761 eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
762 eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
763 eff_information11, eff_information12, eff_information13, eff_information14, eff_information15
764 from psp_eff_report_approvals A2
765 where A1.effort_report_detail_id = A2.effort_report_detail_id
766 /* Bug 5235725: Replacing l_next_approver(1).approver_order_number with l_approver_order_num.
767 In case of reassign a notification function ame_api2.getNextApprovers4 resturns nextApproversOut.approver_order_number with an
768 incremented value while in our system (Effort rporting) we do not increment the approver_order_number in case of reassign */
769 -- and A2.approver_order_num = l_next_approver(1).approver_order_number -1)
770 and A2.approver_order_num = l_approver_order_num -1)
771 -- where A1.approver_order_num = l_next_approver(1).approver_order_number and
772 where A1.approver_order_num = l_approver_order_num and
773 A1.effort_report_detail_id in
774 (select erd.effort_report_detail_id
775 from psp_eff_reports er,
776 psp_eff_report_details erd
777 where er.request_id = l_request_id
778 and er.effort_report_id = erd.effort_report_id
779 and erd.ame_transaction_id = ame_txn_id_array(i)
780 and er.status_code = 'N');
781 hr_utility.trace(' ER workflow -> update count= '||sql%rowcount);
782 end if;
783
784
785 end if;
786 i := i +1;
787 end loop;
788 if l_temp_not_found = 'Y' then
789 update psp_eff_reports er
790 set er.status_code = 'A',
791 er.last_update_date = sysdate,
792 er.last_update_login = fnd_global.login_id,
793 er.last_updated_by = fnd_global.user_id
794 where er.status_code = 'N'
795 and er.effort_report_id in
796 (select erd.effort_report_id
797 from psp_eff_report_details erd,
798 psp_eff_report_approvals era
799 where era.effort_report_detail_id = erd.effort_report_detail_id
800 and era.wf_item_key = itemkey)
801 and not exists
802 (select 1
803 from psp_eff_report_approvals era,
804 psp_eff_report_details erd
805 where era.effort_report_detail_id = erd.effort_report_detail_id
806 and erd.effort_report_id = er.effort_report_id
807 and era.approval_status <> 'A');
808 end if;
809 end if; --- same approval level flag = N
810 end if; --- approver order number > 1
811 if result = 'COMPLETE:FOUND' then
812 wf_engine.SetItemAttrText(itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'APPROVER_ROLE_NAME',
815 avalue => l_wf_rname);
816 wf_engine.SetItemAttrText(itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'APPROVER_DISPLAY_NAME',
819 avalue => l_role_display_name);
820 wf_engine.SetItemAttrText(itemtype => itemtype,
821 itemkey => itemkey,
822 aname => '#ATTACHMENTS',
823 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || itemkey || l_wf_rname );
824
825 --Bug 7135471
826 wf_engine.SetItemAttrText(itemType => itemtype,
827 itemKey => itemkey,
828 aname => 'PDF_ATTACHMENT',
829 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || itemtype || ':' || itemkey);
830
831
832 /*Added for bug 7004679 */
833 wf_engine.setitemowner(itemtype => itemtype,
834 itemkey => itemkey,
835 owner => l_wf_rname);
836
837
838
839 /*
840 wf_engine.SetItemAttrText(itemtype => itemtype,
841 itemkey => itemkey,
842 aname => 'EMBED_WF_ER_DETAILS',
843 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_WF_ER_DETAILS&wfItemKey='||itemkey
844 ||'&requestId='||l_request_id
845 ||'&wfRoleName='||l_wf_rname
846 ||'&approverOrderNum='||l_approver_order_num); */
847
848 hr_utility.trace('GET_NEXT value for temp_approver_order ='|| l_approver_order_num);
849 wf_engine.SetItemAttrNumber(itemtype => itemtype,
853
850 itemkey => itemkey,
851 aname => 'TEMP_APPROVER_ORDER',
852 avalue => l_approver_order_num);
854 hr_utility.trace('er_workflow --> GET NEXT APPROVER jsp string='||
855 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_WF_ER_DETAILS&wfItemKey='||itemkey
856 ||'&requestId='||l_request_id
857 ||'&wfRoleName='||l_wf_rname
858 ||'&approverOrderNum='||l_approver_order_num);
859
860 if l_same_approval_level = 'N' then
861 l_approver_order_num := l_approver_order_num + 1;
862 wf_engine.SetItemAttrNumber(itemtype => itemtype,
863 itemkey => itemkey,
864 aname => 'APPROVER_ORDER_NUM',
865 avalue => l_approver_order_num);
866 end if;
867 end if;
868
869 -- Code Moved from process Approvals
870 psp_xmlgen.update_er_person_xml (p_wf_item_key => itemkey,
871 p_return_status => l_return_status);
872
873 IF (l_return_status = 'E') THEN
874 RAISE fnd_api.g_exc_unexpected_error;
875 END IF;
876
877 end if; --- funcmode = RUN
878 exception
879 when others then
880 result := 'ERROR';
881 --result := substr(sqlerrm,15);
882 wf_core.context('PSP_EFFORT_REPORTS', 'GET_NEXT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
883 raise;
884 end;
885
886 procedure process_rejections(itemtype in varchar2,
887 itemkey in varchar2,
888 actid in number,
889 funcmode in varchar2,
890 result out nocopy varchar2) is
891 l_rname wf_roles.name%type;
892 l_request_id integer;
893 l_effort_report_id integer;
894 l_initiator_rname wf_roles.name%type;
895 l_orig_system_id wf_roles.orig_system_id%type;
896 l_orig_system wf_roles.orig_system%type;
897 l_txn_id varchar2(50);
898 approver_rec ame_util.approverRecord2;
899 l_recipnt_role varchar2(300);
900
901 cursor effort_report_id_cur is
902 select effort_report_id
903 from psp_eff_reports
904 where effort_report_id in
905 (select effort_report_id
906 from psp_eff_report_details
907 where effort_report_detail_id in
908 (select effort_report_detail_id
909 from psp_eff_report_approvals
910 where wf_item_key = itemkey
911 and wf_role_name = l_rname));
912
913 --- one person can have more than one approver.
914 --- partial approval by 2 approvers.
915 cursor past_approvers_cur is
916 select distinct era.wf_role_name
917 from psp_eff_reports er,
918 psp_eff_report_details erd,
919 psp_eff_report_approvals era,
920 fnd_user fu -- Bug 6641216
921 where era.effort_report_detail_id = erd.effort_report_detail_id
922 and erd.effort_report_id = er.effort_report_id
923 and era.approval_status in ( 'A','P')
924 and er.request_id = l_request_id
925 and er.effort_report_id in
926 (select effort_report_id
927 from psp_eff_reports
928 where effort_report_id in
929 (select effort_report_id
930 from psp_eff_report_details
931 where effort_report_detail_id in
932 (select effort_report_detail_id
933 from psp_eff_report_approvals
934 where wf_item_key = itemkey
935 and wf_role_name = l_rname)))
936 and era.wf_role_name = fu.user_name -- Bug 6641216
937 and trunc(sysdate) between trunc(fu.start_date) and nvl(trunc(fu.end_date),trunc(sysdate)) -- Bug 6641216
938 union
939 select name
940 from wf_roles
941 where orig_system = 'PER'
942 and orig_system_id in
943 (select initiator_person_id
944 from psp_report_templates_h
945 where request_id = l_request_id);
946
947 cursor get_txn_id_cur is
948 select distinct ame_transaction_id
949 from psp_eff_report_details erd,
950 psp_eff_report_approvals era
951 where erd.effort_report_detail_id = era.effort_report_detail_id
952 and era.wf_item_key = itemkey
953 and era.approval_status = 'P'
954 and era.wf_role_name = l_rname
955 and era.wf_orig_system_id = l_orig_system_id
956 and era.wf_orig_system = l_orig_system;
957
958 --- same level unapproved ER details
959 cursor same_approval_level_cur(p_approver_order_num in integer) is
960 select wf_role_name
961 from psp_eff_report_approvals
962 where wf_item_key = itemkey
963 and approver_order_num = p_approver_order_num
964 and approval_status = 'P'
965 and rownum = 1;
966
967 l_approver_order_num integer;
968
969 begin
970 l_recipnt_role := 'PSP_PAST_APPROVERS_'||itemkey;
971 ---hr_utility.trace_on('Y','WF-1');
972 if funcmode = 'RUN' then
973 hr_utility.trace('er_workflow -->1 ');
977 aname => 'REQUEST_ID');
974 l_request_id :=
975 wf_engine.GetItemAttrNumber(itemtype => itemtype,
976 itemkey => itemkey,
978 l_rname :=
979 wf_engine.GetItemAttrText(itemtype => itemtype,
980 itemkey => itemkey,
981 aname => 'APPROVER_ROLE_NAME');
982
983 l_approver_order_num :=
984 wf_engine.GetItemAttrNumber(itemtype => itemtype,
985 itemkey => itemkey,
986 aname => 'APPROVER_ORDER_NUM');
987 select orig_system_id,
988 orig_system
989 into l_orig_system_id,
990 l_orig_system
991 from wf_roles
992 where name = l_rname;
993
994 approver_rec.name := l_rname;
995 approver_rec.orig_system := l_orig_system;
996 approver_rec.orig_system_id := l_orig_system_id;
997 approver_rec.approval_status:= 'REJECTED';
998
999 hr_utility.trace('er_workflow -->2 ');
1000 open get_txn_id_cur;
1001 loop
1002 hr_utility.trace('er_workflow -->3 ');
1003 fetch get_txn_id_cur into l_txn_id;
1004 if get_txn_id_cur%notfound then
1005 close get_txn_id_cur;
1006 exit;
1007 end if;
1008 hr_utility.trace('er_workflow -->5 ');
1009
1010 ame_api2.updateapprovalstatus(applicationidin => 8403,
1011 transactiontypein => 'PSP-ER-APPROVAL',
1012 transactionidin => l_txn_id,
1013 approverin => approver_rec);
1014 hr_utility.trace('er_workflow -->6 ');
1015 end loop;
1016
1017 hr_utility.trace('er_workflow -->7 ');
1018 update psp_eff_report_approvals era
1019 set era.approval_status = 'S',
1020 era.response_date = sysdate,
1021 era.last_update_date = sysdate,
1022 era.last_update_login = fnd_global.login_id,
1023 era.last_updated_by = fnd_global.user_id
1024 where era.wf_item_key = itemkey
1025 and era.wf_role_name = l_rname
1026 and exists
1027 ( select erd.effort_report_detail_id
1028 from psp_eff_report_details erd,
1029 psp_eff_reports er
1030 where er.effort_report_id = erd.effort_report_id
1031 and erd.effort_report_detail_id = era.effort_report_detail_id
1032 and er.status_code = 'S' );
1033
1034
1035 update psp_eff_report_approvals era
1036 set era.approval_status = 'R',
1037 era.response_date = sysdate,
1038 era.last_update_date = sysdate,
1039 era.last_update_login = fnd_global.login_id,
1040 era.last_updated_by = fnd_global.user_id
1041 where era.wf_item_key = itemkey
1042 and era.wf_role_name = l_rname
1043 and not exists
1044 ( select erd.effort_report_detail_id
1045 from psp_eff_report_details erd,
1046 psp_eff_reports er
1047 where er.effort_report_id = erd.effort_report_id
1048 and erd.effort_report_detail_id = era.effort_report_detail_id
1049 and er.status_code = 'S' );
1050
1051 hr_utility.trace('er_workflow -->8 ');
1052 open effort_report_id_cur;
1053 loop
1054 fetch effort_report_id_cur into l_effort_report_id;
1055 hr_utility.trace('er_workflow -->9 ');
1056 if effort_report_id_cur%notfound then
1057 close effort_report_id_cur;
1058 exit;
1059 end if;
1060 update psp_eff_reports
1061 set status_code = 'R',
1062 last_update_date = sysdate,
1063 last_update_login = fnd_global.login_id,
1064 last_updated_by = fnd_global.user_id
1065 where effort_report_id = l_effort_report_id;
1066 hr_utility.trace('er_workflow -->19 ');
1067 end loop;
1068
1069 l_initiator_rname :=
1070 wf_engine.GetItemAttrText(itemtype => itemtype,
1071 itemkey => itemkey,
1072 aname => 'INITIATOR');
1073
1074 wf_directory.createAdhocRole(l_recipnt_role,
1075 l_recipnt_role);
1076 hr_utility.trace('er_workflow -->29 ');
1077 open past_approvers_cur;
1078 loop
1079 hr_utility.trace('er_workflow -->39 ');
1080 fetch past_approvers_cur into l_rname;
1081 if past_approvers_cur%notfound then
1082 close past_approvers_cur;
1083 exit;
1084 end if;
1085 hr_utility.trace('er_workflow -->49 ');
1086 wf_directory.AddUsersToAdHocRole
1087 (role_name => l_recipnt_role,
1088 role_users => l_rname);
1089 end loop;
1090
1091 hr_utility.trace('er_workflow -->59 ');
1092 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1093 itemkey => itemkey,
1094 aname => 'NOTIFY_REJECTIONS_ROLE',
1095 avalue => l_recipnt_role);
1096 hr_utility.trace('er_workflow -->69 ');
1097
1098 result := 'COMPLETE:REJECTED';
1099 open same_approval_level_cur(l_approver_order_num);
1103 if same_approval_level_cur%found then
1100 fetch same_approval_level_cur into l_rname;
1101 --- this user has rejected.. but there is one or more different
1102 --- users,from which response is required.
1104 result := 'COMPLETE:MORE_RESP_REQD';
1105 end if;
1106 close same_approval_level_cur;
1107 hr_utility.trace('er_workflow -->79 ');
1108 end if; --- funcmode
1109 hr_utility.trace('er_workflow -->89 ');
1110 exception
1111 when others then
1112 hr_utility.trace('er_workflow -->when others then exception occured Error = '||sqlerrm);
1113 result := 'ERROR';
1114 wf_core.context('PSP_EFFORT_REPORTS', 'PROCESS_REJECTIONS', itemtype, itemkey, to_char(actid), funcmode);
1115 --- debug;
1116 raise;
1117 end;
1118
1119 procedure process_approvals(itemtype in varchar2,
1120 itemkey in varchar2,
1121 actid in number,
1122 funcmode in varchar2,
1123 result out nocopy varchar2) is
1124
1125 l_rname wf_roles.name%type;
1126 l_orig_system_id wf_roles.orig_system_id%type;
1127 l_orig_system wf_roles.orig_system%type;
1128 l_txn_id varchar2(50);
1129 approver_rec ame_util.approverRecord2;
1130 l_return_status CHAR(1);
1131
1132 cursor get_orig_system is
1133 select wf_orig_system_id,
1134 wf_orig_system
1135 from psp_eff_report_approvals
1136 where wf_item_key = itemkey
1137 and approval_status = 'P'
1138 and wf_role_name = l_rname;
1139
1140 cursor get_txn_id_cur is
1141 select distinct ame_transaction_id
1142 from psp_eff_report_details erd,
1143 psp_eff_report_approvals era
1144 where erd.effort_report_detail_id = era.effort_report_detail_id
1145 and era.wf_item_key = itemkey
1146 and era.approval_status = 'A'
1147 and era.wf_role_name = l_rname
1148 and era.wf_orig_system_id = l_orig_system_id
1149 and era.wf_orig_system = l_orig_system;
1150
1151 begin
1152 hr_utility.trace(' the valus of Run Id is : '|| funcmode);
1153 if funcmode = 'RUN' then
1154 l_rname :=
1155 wf_engine.GetItemAttrText(itemtype => itemtype,
1156 itemkey => itemkey,
1157 aname => 'APPROVER_ROLE_NAME');
1158
1159 open get_orig_system;
1160 fetch get_orig_system into l_orig_system_id, l_orig_system;
1161 close get_orig_system;
1162
1163
1164 -- Intorduced for Supercedence to set status_code = 'S'
1165 -- when the psp_eff_reports gets superceded
1166
1167 update psp_eff_report_approvals era
1168 set era.approval_status = 'S',
1169 era.response_date = SYSDATE,
1170 era.last_update_date = sysdate,
1171 era.last_update_login = fnd_global.login_id,
1172 era.last_updated_by = fnd_global.user_id
1173 where era.wf_item_key = itemkey
1174 and era.wf_role_name = l_rname
1175 and era.wf_orig_system_id = l_orig_system_id
1176 and era.wf_orig_system = l_orig_system
1177 and era.approval_status = 'P'
1178 and exists
1179 ( select erd.effort_report_detail_id
1180 from psp_eff_report_details erd,
1181 psp_eff_reports er
1182 where er.effort_report_id = erd.effort_report_id
1183 and erd.effort_report_detail_id = era.effort_report_detail_id
1184 and er.status_code = 'S' );
1185
1186
1187 update psp_eff_report_approvals era
1188 set era.approval_status = 'A',
1189 era.response_date = SYSDATE,
1190 era.last_update_date = sysdate,
1191 era.last_update_login = fnd_global.login_id,
1192 era.last_updated_by = fnd_global.user_id
1193 where era.wf_item_key = itemkey
1194 and era.wf_role_name = l_rname
1195 and era.wf_orig_system_id = l_orig_system_id
1196 and era.wf_orig_system = l_orig_system
1197 and era.approval_status = 'P'
1198 and not exists
1199 ( select erd.effort_report_detail_id
1200 from psp_eff_report_details erd,
1201 psp_eff_reports er
1202 where er.effort_report_id = erd.effort_report_id
1203 and erd.effort_report_detail_id = era.effort_report_detail_id
1204 and er.status_code = 'S' );
1205
1206
1207
1208
1209 approver_rec.name := l_rname;
1210 approver_rec.orig_system := l_orig_system;
1211 approver_rec.orig_system_id := l_orig_system_id;
1212 approver_rec.approval_status:= ame_util.approvedStatus;
1213
1214 open get_txn_id_cur;
1215 loop
1216 fetch get_txn_id_cur into l_txn_id;
1217 if get_txn_id_cur%notfound then
1218 close get_txn_id_cur;
1219 exit;
1220 end if;
1221 ame_api2.updateapprovalstatus(applicationidin => 8403,
1222 transactiontypein => 'PSP-ER-APPROVAL',
1223 transactionidin => l_txn_id,
1224 approverin => approver_rec);
1225 end loop;
1226 result := 'COMPLETE';
1227 -- MOving this code to Procedure get_next Approver
1228
1229 psp_xmlgen.update_er_person_xml (p_wf_item_key => itemkey,
1230 p_return_status => l_return_status);
1231
1232 IF (l_return_status = 'E') THEN
1236 end if; ---funcmode
1233 RAISE fnd_api.g_exc_unexpected_error;
1234 END IF;
1235
1237 exception
1238 when others then
1239 result := 'ERROR';
1240 --result := result||'=='||sqlerrm;
1241 wf_core.context('PSP_EFFORT_REPORTS', 'PROCESS_APPROVALS', itemtype, itemkey, to_char(actid), funcmode);
1242 raise;
1243 end;
1244 procedure approver_post_notify(itemtype in varchar2,
1245 itemkey in varchar2,
1246 actid in number,
1247 funcmode in varchar2,
1248 result out nocopy varchar2) is
1249
1250 l_rname wf_roles.name%type;
1251 l_orig_system_id wf_roles.orig_system_id%type;
1252 l_orig_system wf_roles.orig_system%type;
1253 l_rname2 wf_roles.name%type;
1254 l_orig_system_id2 wf_roles.orig_system_id%type;
1255 l_orig_system2 wf_roles.orig_system%type;
1256 l_txn_id varchar2(50);
1257 approver_rec ame_util.approverRecord2;
1258 forward_rec ame_util.approverRecord2;
1259 l_return_status CHAR(1);
1260
1261 cursor get_orig_system is
1262 select wf_orig_system_id,
1263 wf_orig_system
1264 from psp_eff_report_approvals
1265 where wf_item_key = itemkey
1266 and approval_status = 'P'
1267 and wf_role_name = l_rname;
1268
1269 cursor get_txn_id_cur is
1270 select distinct ame_transaction_id
1271 from psp_eff_report_details erd,
1272 psp_eff_report_approvals era
1273 where erd.effort_report_detail_id = era.effort_report_detail_id
1274 and era.wf_item_key = itemkey
1275 and era.approval_status = 'P'
1276 and era.wf_role_name = l_rname2
1277 and era.wf_orig_system_id = l_orig_system_id2
1278 and era.wf_orig_system = l_orig_system2;
1279
1280 cursor get_forwarde_details is
1281 select orig_system,
1282 orig_system_id,
1283 display_name
1284 from wf_roles
1285 where name = l_rname2;
1286 l_role_display_name2 wf_roles.display_name%type;
1287 l_nid number;
1288
1289 /* Added for hundred percent validation for overridden effort */
1290 l_result varchar2(240);
1291 l_request_id Number;
1292 l_wf_role_name VARCHAR2(320);
1293 l_wf_item_key VARCHAR2(240);
1294 l_approver_order_num Number;
1295 l_hundred_pcrent_eff_flag Varchar2(1);
1296 l_start_date Date;
1297 l_end_date Date;
1298 l_person_id Number;
1299 l_assignment_id Number;
1300 l_full_name varchar2(240);
1301 l_assignemnt_number varchar2(30);
1302 l_last_approver varchar2(1);
1303 l_temp Number;
1304 l_percent Number;
1305 l_error_message varchar2(4000);
1306
1307 CURSOR get_person_Assignment_list_csr IS
1308 SELECT distinct prth.hundred_pcent_eff_at_per_asg, per.start_date,
1309 per.end_date, per.person_id, perd.assignment_id, per.full_name, perd.assignment_number
1310 FROM psp_report_templates_h prth,
1311 psp_eff_reports per,
1312 psp_eff_report_details perd,
1313 psp_eff_report_approvals prea
1314 WHERE prth.request_id = per.request_id
1315 AND per.effort_report_id = perd.effort_report_id
1316 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1317 AND per.request_id = l_request_id
1318 AND prea.wf_role_name = l_wf_role_name
1319 AND prea.wf_item_key = l_wf_item_key
1320 AND prea.approver_order_num = l_approver_order_num;
1321
1322 CURSOR is_person_last_approver_csr IS
1323 SELECT 1
1324 FROM psp_eff_reports per,
1325 psp_eff_report_details perd,
1326 psp_eff_report_approvals prea
1327 WHERE per.effort_report_id = perd.effort_report_id
1328 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1329 AND per.person_id = l_person_id
1330 AND per.start_date = l_start_date
1331 AND per.end_date = l_end_date
1332 AND prea.wf_role_name <> l_wf_role_name
1333 AND approver_order_num = l_approver_order_num
1334 AND prea.approval_status ='P'
1335 AND per.status_code IN ('N','A');
1336
1337 CURSOR is_asg_last_approver_csr IS
1338 SELECT 1
1339 FROM psp_eff_reports per,
1340 psp_eff_report_details perd,
1341 psp_eff_report_approvals prea
1342 WHERE per.effort_report_id = perd.effort_report_id
1343 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1344 AND perd.assignment_id = l_assignment_id
1345 AND per.start_date = l_start_date
1346 AND per.end_date = l_end_date
1347 AND prea.wf_role_name <> l_wf_role_name
1348 AND approver_order_num = l_approver_order_num
1349 AND prea.approval_status = 'P'
1350 AND per.status_code IN ('N','A');
1351
1352
1353 CURSOR person_percent_csr IS
1354 SELECT sum(nvl(overwritten_effort_percent,payroll_percent))
1355 FROM psp_eff_reports per,
1356 psp_eff_report_details perd,
1357 psp_eff_report_approvals prea
1358 WHERE per.effort_report_id = perd.effort_report_id
1359 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1360 AND per.person_id = l_person_id
1361 AND per.start_date = l_start_date
1362 AND per.end_date = l_end_date
1363 AND approver_order_num = l_approver_order_num
1364 AND prea.approval_status IN ('P','A')
1365 AND per.status_code IN ('N','A');
1366
1367 CURSOR assignment_percent_csr IS
1368 SELECT sum(nvl(overwritten_effort_percent,payroll_percent))
1369 FROM psp_eff_reports per,
1373 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1370 psp_eff_report_details perd,
1371 psp_eff_report_approvals prea
1372 WHERE per.effort_report_id = perd.effort_report_id
1374 AND perd.assignment_id = l_assignment_id
1375 AND per.start_date = l_start_date
1376 AND per.end_date = l_end_date
1377 AND approver_order_num = l_approver_order_num
1378 AND prea.approval_status IN ('P','A')
1379 AND per.status_code IN ('N','A');
1380
1381 BEGIN
1382
1383 /* Added for Hundred percent validation for overriden effort */
1384 IF funcmode in ('RESPOND') THEN
1385 l_result := Wf_Notification.GetAttrText(wf_engine.context_nid, 'RESULT');
1386 IF l_result = 'APPROVED' THEN
1387 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1388 itemkey => itemkey,
1389 aname => 'REQUEST_ID');
1390 l_wf_role_name := wf_engine.GetItemAttrText(itemtype => itemtype,
1391 itemkey => itemkey,
1392 aname => 'APPROVER_ROLE_NAME');
1393 l_approver_order_num := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1394 itemkey => itemkey,
1395 aname => 'APPROVER_ORDER_NUM');
1396 l_approver_order_num := l_approver_order_num - 1;
1397 l_wf_item_key := itemkey;
1398 OPEN get_person_Assignment_list_csr;
1399 LOOP
1400 FETCH get_person_Assignment_list_csr INTO l_hundred_pcrent_eff_flag, l_start_date,
1401 l_end_date, l_person_id, l_assignment_id, l_full_name, l_assignemnt_number;
1402 IF get_person_Assignment_list_csr%NOTFOUND THEN
1403 CLOSE get_person_Assignment_list_csr;
1404 EXIT;
1405 END IF;
1406 IF l_hundred_pcrent_eff_flag = 'P' THEN
1407 OPEN is_person_last_approver_csr;
1408 FETCH is_person_last_approver_csr INTO l_temp ;
1409 IF is_person_last_approver_csr%NOTFOUND THEN
1410 l_last_approver := 'Y';
1411 ELSE
1412 l_last_approver := 'N';
1413 END IF;
1414 CLOSE is_person_last_approver_csr;
1415
1416 IF l_last_approver = 'Y' THEN
1417 OPEN person_percent_csr;
1418 FETCH person_percent_csr into l_percent;
1419 CLOSE person_percent_csr;
1420 IF l_percent <> 100 THEN
1421 FND_MESSAGE.set_name('PSP','PSP_ER_EMP_PERCENT_NOT_100');
1422 fnd_message.set_token('EMP_NAME', l_full_name);
1423 fnd_message.set_token('PERCENT', l_percent);
1424 l_error_message := fnd_message.get;
1425 raise_application_error(-20002, l_error_message);
1426 END IF;
1427 END IF;
1428 ELSIF l_hundred_pcrent_eff_flag = 'A' THEN
1429 OPEN is_asg_last_approver_csr;
1430 FETCH is_asg_last_approver_csr INTO l_temp;
1431 IF is_asg_last_approver_csr%NOTFOUND THEN
1432 l_last_approver := 'Y';
1433 ELSE
1434 l_last_approver := 'N';
1435 END IF;
1436 CLOSE is_asg_last_approver_csr;
1437 IF l_last_approver = 'Y' THEN
1438 OPEN assignment_percent_csr;
1439 FETCH assignment_percent_csr into l_percent;
1440 CLOSE assignment_percent_csr;
1441 IF l_percent <> 100 THEN
1442 FND_MESSAGE.set_name('PSP','PSP_ER_ASG_PERCENT_NOT_100');
1443 fnd_message.set_token('EMP_NAME', l_full_name);
1444 fnd_message.set_token('ASG_NUMBER', l_assignemnt_number);
1445 fnd_message.set_token('PERCENT', l_percent);
1446 l_error_message := fnd_message.get;
1447 raise_application_error(-20002, l_error_message);
1448 END IF;
1449 END IF;
1450 END IF;
1451 END LOOP;
1452 END IF;
1453 END IF;
1454
1455 if funcmode in ('TRANSFER', 'FORWARD') then
1456 ---hr_utility.trace_on('Y','WF-1');
1457 hr_utility.trace('Post Notification...TRANSFER . FORWARD');
1458 l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1459 itemkey => itemkey,
1460 aname => 'APPROVER_ROLE_NAME');
1461 hr_utility.trace('role name from T and F = '||l_rname);
1462
1463 open get_orig_system;
1464 fetch get_orig_system into l_orig_system_id, l_orig_system;
1465 close get_orig_system;
1466
1467 approver_rec.name := l_rname;
1468 approver_rec.orig_system := l_orig_system;
1469 approver_rec.orig_system_id := l_orig_system_id;
1470 approver_rec.approval_status:= ame_util.forwardStatus;
1471 l_rname2 := wf_engine.context_text;
1472 l_nid := wf_engine.context_nid;
1473 open get_forwarde_details;
1474 fetch get_forwarde_details into l_orig_system2, l_orig_system_id2,l_role_display_name2;
1475 close get_forwarde_details;
1476 forward_rec.name := l_rname2;
1477 hr_utility.trace('post notification...Transfer . Forwardee='||l_rname2||' nid ='||l_nid);
1478 forward_rec.orig_system := l_orig_system2;
1479 forward_rec.orig_system_id := l_orig_system_id2;
1480 forward_rec.approval_status:= ame_util.notifiedStatus;
1481 update psp_eff_report_approvals
1482 set wf_role_name = l_rname2,
1483 wf_role_display_name = l_role_display_name2,
1484 wf_orig_system = l_orig_system2,
1485 wf_orig_system_id = l_orig_system_id2
1489 and wf_orig_system = l_orig_system
1486 where wf_item_key = itemkey
1487 and wf_role_name = l_rname
1488 and wf_orig_system_id = l_orig_system_id
1490 and approval_status = 'P';
1491
1492 -- Added for Bug 6996115
1493 update fnd_attached_documents
1494 set pk1_value = itemkey||l_rname2
1495 where pk1_value = itemkey||l_rname;
1496
1497
1498
1499 wf_engine.SetItemAttrText(itemtype => itemtype,
1500 itemkey => itemkey,
1501 aname => 'APPROVER_ROLE_NAME',
1502 avalue => l_rname2);
1503 wf_engine.SetItemAttrText(itemtype => itemtype,
1504 itemkey => itemkey,
1505 aname => 'APPROVER_DISPLAY_NAME',
1506 avalue => l_role_display_name2);
1507 wf_notification.setAttrText(nid => l_nid,
1508 aname => 'APPROVER_ROLE',
1509 avalue => l_rname2);
1510 open get_txn_id_cur;
1511 loop
1512 fetch get_txn_id_cur into l_txn_id;
1513 if get_txn_id_cur%notfound then
1514 close get_txn_id_cur;
1515 exit;
1516 end if;
1517 hr_utility.trace('Transfer mode CALLING UPDATEAME txn_id ='||l_txn_id);
1518 ame_api2.updateapprovalstatus(applicationidin => 8403,
1519 transactiontypein => 'PSP-ER-APPROVAL',
1520 transactionidin => l_txn_id,
1521 approverin => approver_rec,
1522 forwardeein => forward_rec);
1523 end loop;
1524 else
1525 l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1526 itemkey => itemkey,
1527 aname => 'APPROVER_ROLE_NAME');
1528
1529
1530
1531 hr_utility.trace('funcmode, role name from RUN = '||funcmode||','||l_rname);
1532 end if; ---funcmode
1533
1534 -- BUG 4334816 START
1535 -- New code added to capture the Notification id in psp_eff_report_approvals table
1536 l_nid := wf_engine.context_nid;
1537 l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1538 itemkey => itemkey,
1539 aname => 'APPROVER_ROLE_NAME');
1540
1541 update psp_eff_report_approvals set NOTIFICATION_ID = l_nid
1542 where WF_ITEM_KEY = itemkey
1543 AND WF_ROLE_NAME = l_rname;
1544
1545 -- BUG 4334816 END
1546
1547 exception
1548 when others then
1549 result := 'ERROR';
1550 --result := result||'=='||sqlerrm;
1551 wf_core.context('PSP_EFFORT_REPORTS', 'APPROVER_POST_NOTIFY', itemtype, itemkey, to_char(actid), funcmode);
1552 raise;
1553 end;
1554
1555 procedure set_pdf_gen_failures(itemtype in varchar2,
1556 itemkey in varchar2,
1557 actid in number,
1558 funcmode in varchar2,
1559 result out nocopy varchar2) is
1560 begin
1561 --- delete any left over PDF files due to failed Conc process for generating
1562 --- split PDFs
1563 null;
1564
1565 end;
1566
1567 procedure approver_pdf_fail(itemtype in varchar2,
1568 itemkey in varchar2,
1569 actid in number,
1570 funcmode in varchar2,
1571 result out nocopy varchar2) is
1572
1573 l_person_id integer;
1574 l_rname varchar2(300);
1575 l_request_id integer;
1576
1577 l_pdf_request_id NUMBER;
1578 l_retry_request_id NUMBER;
1579 l_error_count NUMBER;
1580
1581 CURSOR report_error_cur IS
1582 SELECT 1
1583 FROM psp_report_errors
1584 WHERE pdf_request_id = l_pdf_request_id;
1585
1586 /*cursor get_person_id is
1587 select person_id
1588 from psp_eff_reports
1589 where effort_report_id in
1590 (select effort_report_id
1591 from psp_eff_report_details
1592 where request_id = l_request_id
1593 and effort_report_detail_id in
1594 (select effort_report_detail_id
1595 from psp_eff_report_approvals
1596 where wf_item_key = itemkey
1597 and wf_role_name = l_rname));*/
1598 begin
1599 if funcmode = 'RUN' then
1600 l_rname :=
1601 wf_engine.GetItemAttrText(itemtype => itemtype,
1602 itemkey => itemkey,
1603 aname => 'APPROVER_ROLE_NAME');
1604 l_request_id :=
1605 wf_engine.GetItemAttrText(itemtype => itemtype,
1606 itemkey => itemkey,
1607 aname => 'REQUEST_ID');
1608
1609 l_pdf_request_id :=
1610 wf_engine.GetItemAttrNumber(itemtype => itemtype,
1611 itemkey => itemkey,
1612 aname => 'PDF_REQUEST_ID');
1613
1614 l_retry_request_id :=
1615 wf_engine.GetItemAttrNumber(itemtype => itemtype,
1616 itemkey => itemkey,
1620 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1617 aname => 'RETRY_REQUEST_ID');
1618
1619 /*
1621 itemkey => itemkey,
1622 aname => 'WF_EMBED_INITIATOR',
1623 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_INIT_STATUS_DETAILS&erRequestId=-&MSG_REQUEST_ID-'
1624 || '&pdfRequestId=-&PDF_REQUEST_ID-&wfItemKey=-&MSG_IKEY-&processParameters=-&PROCESS_PARAMETERS-');
1625
1626 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1627 itemkey => itemkey,
1628 aname => 'INITIATOR_ERROR_STATUS',
1629 avalue => '');
1630 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1631 itemkey => itemkey,
1632 aname => 'WF_EMBED_INITIATOR_ERRORS',
1633 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_INITIATOR_ERRORS&erRequestId=-&MSG_REQUEST_ID-'
1634 || '&pdfRequestId=-&PDF_REQUEST_ID-&wfItemKey=-&MSG_IKEY-&processParameters=-&PROCESS_PARAMETERS-');
1635 */
1636 /* open get_person_id;
1637 fetch get_person_id into l_person_id;
1638 close get_person_id; */
1639
1640 OPEN report_error_cur;
1641 FETCH report_error_cur INTO l_error_count;
1642 CLOSE report_error_cur;
1643
1644 IF (NVL(l_error_count, 0) = 0) THEN
1645 insert into psp_report_errors
1646 (error_sequence_id, request_id, message_level, source_id,
1647 error_message, retry_request_id, pdf_request_id)
1648 values (psp_report_errors_s.nextval,
1649 l_request_id, 'E',l_person_id,
1650 'PDF Generation Failed, please see the Concurrent process log',
1651 l_retry_request_id, l_pdf_request_id);
1652 END IF;
1653 end if;
1654 result := 'COMPLETE';
1655 exception
1656 when others then
1657 result := 'ERROR';
1658 wf_core.context('PSP_EFFORT_REPORTS', 'APPROVER_PDF_FAIL', itemtype, itemkey, to_char(actid), funcmode);
1659 raise;
1660 end;
1661
1662 procedure start_initiator_wf(p_request_id in integer)
1663 is
1664 l_wf_itemkey varchar2(100);
1665 l_user_name varchar2(100);
1666 l_start_date DATE;
1667 l_end_date DATE;
1668 l_template_name psp_report_templates_h.template_name%TYPE;
1669 l_preview_flag psp_report_templates_h.preview_effort_report_flag%TYPE;
1670 l_time_out integer;
1671
1672 CURSOR template_name_cur IS
1673 SELECT template_name,
1674 preview_effort_report_flag,
1675 notification_reminder_in_days,
1676 fnd_date.canonical_to_date(fnd_date.date_to_canonical(parameter_value_2)) start_date,
1677 fnd_date.canonical_to_date(fnd_date.date_to_canonical(parameter_value_3)) end_date
1678 FROM psp_report_templates_h prth
1679 WHERE prth.request_id = p_request_id;
1680
1681 l_param_string VARCHAR2(2000);
1682 l_icx_date_format VARCHAR2(30);
1683 l_gl_sob NUMBER;
1684
1685 CURSOR get_sob_cur IS
1686 SELECT set_of_books_id
1687 FROM psp_report_templates_h
1688 WHERE request_id = p_request_id;
1689
1690 CURSOR get_param_strings IS
1691 SELECT prt.template_name,
1692 TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), l_icx_date_format) start_date,
1693 TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), l_icx_date_format) end_date,
1694 -- xtt.template_name report_layout,
1695 flv1.meaning sort_option1,
1696 flv2.meaning order_by1,
1697 flv3.meaning sort_option2,
1698 flv4.meaning order_by2,
1699 flv5.meaning sort_option3,
1700 flv6.meaning order_by3,
1701 flv7.meaning sort_option4,
1702 flv8.meaning order_by4
1703 FROM psp_report_templates_h prth,
1704 xdo_templates_tl xtt,
1705 psp_report_templates prt,
1706 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv1,
1707 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv2,
1708 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv3,
1709 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv4,
1710 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv5,
1711 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv6,
1712 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv7,
1713 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv8
1714 WHERE prth.request_id = p_request_id
1715 AND prt.template_id = prth.template_id
1716 AND flv1.lookup_code = prth.parameter_value_5
1717 -- AND flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
1718 AND flv2.lookup_code = prth.parameter_value_6
1719 AND flv3.lookup_code = prth.parameter_value_7
1720 -- AND flv4.lookup_type = 'PSP_ORDERING_CRITERIA'
1721 AND flv4.lookup_code = prth.parameter_value_8
1722 AND flv5.lookup_code (+) = prth.parameter_value_9
1723 -- AND NVL(flv6.lookup_type, 'PSP_ORDERING_CRITERIA') = 'PSP_ORDERING_CRITERIA'
1724 AND flv6.lookup_code (+) = prth.parameter_value_10
1725 AND flv7.lookup_code (+) = prth.parameter_value_11
1726 -- AND NVL(flv8.lookup_type, 'PSP_ORDERING_CRITERIA') = 'PSP_ORDERING_CRITERIA'
1727 AND flv8.lookup_code (+) = prth.parameter_value_12
1728 AND xtt.template_code = prth.report_template_code
1729 AND xtt.application_short_name = 'PSP';
1730
1734 WHERE request_id = p_request_id;
1731 CURSOR payroll_action_id_cur IS
1732 SELECT payroll_action_id
1733 FROM pay_payroll_actions
1735
1736 l_arg1 VARCHAR2(100);
1737 l_arg2 VARCHAR2(100);
1738 l_arg3 VARCHAR2(100);
1739 l_arg4 VARCHAR2(100);
1740 l_arg5 VARCHAR2(100);
1741 l_arg6 VARCHAR2(100);
1742 l_arg7 VARCHAR2(100);
1743 l_arg8 VARCHAR2(100);
1744 l_arg9 VARCHAR2(100);
1745 l_arg10 VARCHAR2(100);
1746 l_arg11 VARCHAR2(100);
1747 l_arg12 VARCHAR2(100);
1748
1749 l_retry_request_id NUMBER(15, 0);
1750 l_payroll_action_id NUMBER(15, 0);
1751 l_emp_matching_selection NUMBER;
1752
1753 CURSOR emp_matching_selection_cur IS
1754 SELECT COUNT(DISTINCT person_id) -- Modified count(*) to count(distinct person_id) for bug fix 4429787
1755 FROM psp_selected_persons_t
1756 WHERE request_id = p_request_id;
1757 begin
1758
1759 l_user_name := fnd_global.user_name;
1760 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
1761 l_retry_request_id := fnd_global.conc_request_id;
1762
1763 OPEN get_sob_cur;
1764 FETCH get_sob_cur INTO l_gl_sob;
1765 CLOSE get_sob_cur;
1766
1767 OPEN payroll_action_id_cur;
1768 FETCH payroll_action_id_cur INTO l_payroll_action_id;
1769 CLOSE payroll_action_id_cur;
1770
1771 select psp_wf_item_key_s.nextval
1772 into l_wf_itemkey
1773 from dual;
1774
1775 OPEN template_name_cur;
1776 FETCH template_name_cur INTO l_template_name, l_preview_flag, l_time_out, l_start_date, l_end_date;
1777 CLOSE template_name_cur;
1778
1779 --BUG 4334816 START
1780 -- code added to store WF_Item_Key in Psp_report_templates_h
1781 update psp_report_templates_h
1782 set INITIATOR_WF_ITEM_KEY = l_wf_itemkey
1783 where request_id = p_request_id;
1784
1785 --BUG 4334816 END
1786
1787 --- dynamic timeout should be expresed in minutes,
1788 --- days to minutes conversion.
1789 l_time_out := l_time_out * 1440;
1790
1791 --dbms_output.put_line('FROM START item key ='||l_wf_itemkey);
1792
1793 wf_engine.CreateProcess(itemtype => 'PSPERAVL',
1794 itemkey => l_wf_itemkey,
1795 process => 'INITIATOR_PROCESS');
1796
1797 hr_utility.trace('er_workflow --> start_int: reqid ='||to_char(p_request_id));
1798
1799 /*Added for bug 7004679 */
1800 wf_engine.setitemowner(itemtype => 'PSPERAVL',
1801 itemkey => l_wf_itemkey,
1802 owner => l_user_name);
1803
1804 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1805 itemkey => l_wf_itemkey,
1806 aname => 'REQUEST_ID',
1807 avalue => p_request_id);
1808
1809 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1810 itemkey => l_wf_itemkey,
1811 aname => 'TIMEOUT',
1812 avalue => l_time_out);
1813
1814 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1815 itemkey => l_wf_itemkey,
1816 aname => 'INITIATOR',
1817 avalue => l_user_name);
1818
1819 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1820 itemkey => l_wf_itemkey,
1821 aname => 'WF_EMBED_INITIATOR',
1822 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_INITIATOR_DETAILS&requestId='||p_request_id);
1823
1824 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1825 itemkey => l_wf_itemkey,
1826 aname => 'WF_EMBED_FINAL_RECIPIENT',
1827 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_FIN_REC_STATUS&requestId='||p_request_id);
1828
1829 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1830 itemkey => l_wf_itemkey,
1831 aname => 'INITIATOR_ERROR_STATUS',
1832 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_ERROR_STATUS&requestId='||p_request_id);
1833
1834 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1835 itemkey => l_wf_itemkey,
1836 aname => 'START_DATE',
1837 avalue => l_start_date);
1838
1839 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1840 itemkey => l_wf_itemkey,
1841 aname => 'END_DATE',
1842 avalue => l_end_date);
1843
1844 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1845 itemkey => l_wf_itemkey,
1846 aname => 'ITEM_KEY',
1847 avalue => l_wf_itemkey);
1848
1849 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1850 itemKey => l_wf_itemkey,
1851 aname => 'RECEIVER_FLAG',
1852 avalue => 'IR');
1853
1854 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1855 itemKey => l_wf_itemkey,
1856 aname => 'TEMPLATE_NAME',
1857 avalue => l_template_name);
1858
1862 aname => '#ATTACHMENTS',
1859 IF (l_preview_flag = 'Y') THEN
1860 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1861 itemkey => l_wf_itemkey,
1863 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || l_wf_itemkey || '&pk2name=RECEIVER_FLAG&pk2value=' || 'IR');
1864 --Bug 7135471
1865 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1866 itemKey => l_wf_itemkey,
1867 aname => 'PDF_ATTACHMENT',
1868 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || l_wf_itemkey);
1869
1870 ELSE
1871 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1872 itemkey => l_wf_itemkey,
1873 aname => '#ATTACHMENTS',
1874 avalue => '');
1875
1876 --Bug 7135471
1877 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1878 itemKey => l_wf_itemkey,
1879 aname => 'PDF_ATTACHMENT',
1880 avalue => '');
1881
1882 END IF;
1883
1884 IF (p_request_id <> l_retry_request_id) THEN
1885 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1886 itemkey => l_wf_itemkey,
1887 aname => 'RETRY_REQUEST_ID',
1888 avalue => l_retry_request_id);
1889 END IF;
1890
1891 OPEN emp_matching_selection_cur;
1892 FETCH emp_matching_selection_cur INTO l_emp_matching_selection;
1893 CLOSE emp_matching_selection_cur;
1894
1895 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1896 itemkey => l_wf_itemkey,
1897 aname => 'EMP_MATCHING_SELECTION',
1898 avalue => l_emp_matching_selection);
1899
1900 OPEN get_param_strings;
1901 FETCH get_param_strings INTO l_arg1, l_arg2, l_arg3, /* l_arg4,*/ l_arg5, l_arg6, l_arg7, l_arg8, l_arg9, l_arg10, l_arg11, l_arg12;
1902 CLOSE get_param_strings;
1903
1904 fnd_message.set_name('PSP', 'PSP_ER_WF_PROCESS_PARAMETERS');
1905 fnd_message.set_token('TEMPLATE_NAME', l_arg1);
1906 fnd_message.set_token('START_DATE', l_arg2);
1907 fnd_message.set_token('END_DATE', l_arg3);
1908 -- fnd_message.set_token('REPORT_LAYOUT', l_arg4);
1909 fnd_message.set_token('FIRST_SORT_BY', l_arg5);
1910 fnd_message.set_token('FIRST_ORDER_BY', l_arg6);
1911 fnd_message.set_token('SECOND_SORT_BY', l_arg7);
1912 fnd_message.set_token('SECOND_ORDER_BY', l_arg8);
1913 fnd_message.set_token('THIRD_SORT_BY', l_arg9);
1914 fnd_message.set_token('THIRD_ORDER_BY', l_arg10);
1915 fnd_message.set_token('FOURTH_SORT_BY', l_arg11);
1916 fnd_message.set_token('FOURTH_ORDER_BY', l_arg12);
1917
1918 l_param_string := fnd_message.get;
1919
1920 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1921 itemKey => l_wf_itemkey,
1922 aname => 'CONC_PARAM_STRING',
1923 avalue => l_param_string);
1924
1925 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1926 itemkey => l_wf_itemkey,
1927 aname => 'PAYROLL_ACTION_ID',
1928 avalue => l_payroll_action_id);
1929
1930 wf_engine.StartProcess(itemtype => 'PSPERAVL',
1931 itemkey => l_wf_itemkey);
1932 end;
1933
1934 procedure initiator_response(itemtype in varchar2,
1935 itemkey in varchar2,
1936 actid in number,
1937 funcmode in varchar2,
1938 result out nocopy varchar2) is
1939 l_request_id integer;
1940 cursor get_init_response is
1941 select initiator_accept_flag
1942 from psp_report_templates_h
1943 where request_id = l_request_id;
1944 l_response varchar2(10);
1945 begin
1946 if funcmode = 'RUN' then
1947 l_request_id :=
1948 wf_engine.GetItemAttrText(itemtype => itemtype,
1949 itemkey => itemkey,
1950 aname => 'REQUEST_ID');
1951 open get_init_response;
1952 fetch get_init_response into l_response;
1953 close get_init_response;
1954 if l_response = 'Y' then
1955 result := 'COMPLETE:APPROVED';
1956 else
1957 result := 'COMPLETE:REJECTED';
1958 end if;
1959 end if;
1960 exception
1961 when others then
1962 result := 'ERROR';
1963 wf_core.context('PSP_EFFORT_REPORTS', 'INITIATOR_RESPONSE', itemtype, itemkey, to_char(actid), funcmode);
1964 raise;
1965 end;
1966
1967 procedure create_frp_role(itemtype in varchar2,
1968 itemkey in varchar2,
1969 actid in number,
1970 funcmode in varchar2,
1971 result out nocopy varchar2) is
1972
1973 l_recipnt_role wf_roles.name%type;
1974 l_request_id integer;
1975 l_member_rname wf_roles.name%type;
1976
1977 cursor get_member_role is
1978 select distinct wf.name
1979 from wf_roles wf,
1980 psp_report_template_details_h temp
1981 where temp.request_id = l_request_id
1982 and wf.orig_system = 'PER'
1983 and to_char(wf.orig_system_id) = temp.criteria_value1
1984 and temp.criteria_lookup_type = 'PSP_SELECTION_CRITERIA'
1985 and temp.criteria_lookup_code = 'FRP';
1986 i integer := 0;
1987 l_debug varchar2(2000);
1988 l_frp_role_display varchar2(100);
1989
1990 cursor check_approval_count is
1991 select count(*)
1992 from psp_eff_reports
1993 where status_code = 'A'
1994 and request_id = l_request_id;
1995
1996 l_count integer;
1997 begin
1998 l_recipnt_role := 'PSP_FINAL_RECIPIENT_'||itemkey;
1999 l_frp_role_display := 'Effort Report Final Recipients';
2000 l_request_id :=
2001 wf_engine.GetItemAttrText(itemtype => itemtype,
2002 itemkey => itemkey,
2003 aname => 'REQUEST_ID');
2004
2005 wf_directory.createAdhocRole(l_recipnt_role,
2006 l_frp_role_display);
2007
2008 open check_approval_count;
2009 fetch check_approval_count into l_count;
2010 close check_approval_count;
2011
2012 if l_count > 0 then
2013 open get_member_role;
2014 loop
2015 fetch get_member_role into l_member_rname;
2016 if get_member_role%notfound then
2017 close get_member_role;
2018 exit;
2019 end if;
2020 i := i + 1;
2021 wf_directory.AddUsersToAdHocRole
2022 (role_name => l_recipnt_role,
2023 role_users => l_member_rname);
2024 end loop;
2025
2026 if i > 0 then
2027
2028 result := 'COMPLETE:Y';
2029
2030 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
2031 itemkey => itemkey,
2032 aname => 'FINAL_RECIPIENT',
2033 avalue => l_recipnt_role);
2034 update psp_report_templates_h
2035 set final_recip_notified_flag = 'Y'
2036 where request_id = l_request_id;
2037 else
2038 result := 'COMPLETE:N';
2039 end if;
2040 else
2041 result := 'COMPLETE:N';
2042 end if;
2043
2044 exception
2045 when others then
2046 ---l_debug := sqlerrm;
2047 ---hr_utility.trace('er_workflow --> 120'||l_debug);
2048 result := 'ERROR';
2049 wf_core.context('PSP_EFFORT_REPORTS', 'CREATE_FRP_ROLE', itemtype, itemkey, to_char(actid), funcmode);
2050 end;
2051
2052 procedure get_pdf_for_apprvr(itemtype in varchar2,
2053 itemkey in varchar2,
2054 actid in number,
2055 funcmode in varchar2,
2056 result out nocopy varchar2) is
2057 begin
2058 -- to consutruct the attachment attribute for the notification.
2059 --- link the PDF using the attachment attribute
2060 --dbms_output.put_line(' attach the pdf here for the notification');
2061 /*
2062
2063 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2064 itemKey => itemkey,
2065 aname => 'PDF_ATTACHMENT',
2066 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || itemtype || ':' || itemkey);
2067 */
2068 null;
2069
2070 end;
2071
2072 procedure gen_modified_pdf(itemtype in varchar2,
2073 itemkey in varchar2,
2074 actid in number,
2075 funcmode in varchar2,
2076 result out nocopy varchar2) is
2077 begin
2078 fnd_wf_standard.executeconcprogram(itemtype ,
2079 itemkey ,
2080 actid ,
2081 funcmode ,
2082 result );
2083
2084 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2085 itemKey => itemkey,
2086 aname => 'PDF_ATTACHMENT',
2087 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || itemtype || ':' || itemkey);
2088
2089 end;
2090
2091 procedure update_receiver(itemtype in varchar2,
2092 itemkey in varchar2,
2093 actid in number,
2094 funcmode in varchar2,
2095 result out nocopy varchar2) is
2096
2097 -- Bug 7135471 starts
2098 l_request_id integer;
2099
2100 cursor get_approval_type is
2101 select approval_type
2102 from psp_report_templates_h
2103 where request_id = l_request_id;
2104
2105 l_approval_type varchar2(100);
2106
2107 begin
2108
2109 l_request_id :=
2110 wf_engine.GetItemAttrText(itemtype => itemtype,
2111 itemkey => itemkey,
2112 aname => 'REQUEST_ID');
2113
2114 open get_approval_type;
2115 fetch get_approval_type into l_approval_type;
2116 close get_approval_type;
2117
2118 IF l_approval_type = 'PRE' THEN
2119
2120 --this code is to delete the pdf that has already been created with the DRAFT watermark
2121 --for PRE APPROVED report types
2122 delete from fnd_lobs
2123 where file_id in(select media_id from fnd_documents_vl
2124 where document_id in(select document_id from fnd_attached_documents
2125 where pk1_value = itemkey));
2126
2127
2128
2129 delete from fnd_attached_documents
2130 where pk1_value = itemkey;
2131
2132 END IF;
2133 -- Bug 7135471 End
2134
2135
2136
2137 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2138 itemKey => itemkey,
2139 aname => 'RECEIVER_FLAG',
2140 avalue => 'FR');
2141
2142 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
2143 itemkey => itemkey,
2144 aname => '#ATTACHMENTS',
2145 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || itemkey || '&pk2name=RECEIVER_FLAG&pk2value=' || 'FR');
2146
2147 --Bug 7135471
2148 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2149 itemKey => itemkey,
2150 aname => 'PDF_ATTACHMENT',
2151 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || itemkey);
2152
2153
2154 end update_receiver;
2155
2156 procedure update_approver(itemtype in varchar2,
2157 itemkey in varchar2,
2158 actid in number,
2159 funcmode in varchar2,
2160 result out nocopy varchar2) is
2161 begin
2162 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2163 itemKey => itemkey,
2164 aname => 'RECEIVER_FLAG',
2165 avalue => 'AR');
2166
2167 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
2168 itemkey => itemkey,
2169 aname => '#ATTACHMENTS',
2170 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || itemkey || '&pk2name=RECEIVER_FLAG&pk2value=' || 'AR');
2171
2172 --Bug 7135471
2173 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2174 itemKey => itemkey,
2175 aname => 'PDF_ATTACHMENT',
2176 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || itemkey);
2177
2178
2179 end update_approver;
2180
2181 procedure pre_approved(itemtype in varchar2,
2182 itemkey in varchar2,
2183 actid in number,
2184 funcmode in varchar2,
2185 result out nocopy varchar2) is
2186
2187 l_request_id integer;
2188 cursor get_approval_type is
2189 select approval_type
2190 from psp_report_templates_h
2191 where request_id = l_request_id;
2192 l_approval_type varchar2(100);
2193 begin
2194 l_request_id :=
2195 wf_engine.GetItemAttrText(itemtype => itemtype,
2196 itemkey => itemkey,
2197 aname => 'REQUEST_ID');
2198 open get_approval_type;
2199 fetch get_approval_type into l_approval_type;
2200 close get_approval_type;
2201
2202 if l_approval_type = 'PRE' then
2203 result := 'COMPLETE:Y';
2204 else
2205 result := 'COMPLETE:N';
2206 end if;
2207 exception
2208 when others then
2209 result := 'ERROR';
2210 wf_core.context('PSP_EFFORT_REPORTS', 'PRE_APPROVED', itemtype, itemkey, to_char(actid),
2211 funcmode);
2212
2213 end;
2214
2215 PROCEDURE update_initiator_message (itemtype IN varchar2,
2216 itemkey IN varchar2,
2217 actid IN number,
2218 funcmode IN varchar2,
2219 result OUT nocopy varchar2) IS
2220 l_message_name fnd_new_messages.message_name%TYPE;
2221 l_message_text fnd_new_messages.message_text%TYPE;
2222 BEGIN
2223 l_message_name := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'INITIATOR_MESSAGE_NAME');
2224 fnd_message.set_name('PSP', l_message_name);
2225 l_message_text := fnd_message.get;
2226 wf_engine.SetItemAttrText(itemtype => itemtype,
2227 itemkey => itemkey,
2228 aname => 'ERROR_MESSAGE',
2229 avalue => l_message_text);
2230
2231 result := 'COMPLETE';
2232
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 -- Populate stack with error message
2236 result := 'ERROR';
2237 wf_core.context('PSP_EFFORT_REPORTS', 'update_initiator_message', itemtype, itemkey, to_char(actid), funcmode);
2238 RAISE;
2239 END update_initiator_message;
2240
2241 PROCEDURE set_wf_admin(itemtype IN varchar2,
2242 itemkey IN varchar2,
2243 actid IN number,
2244 funcmode IN varchar2,
2245 result OUT nocopy varchar2) IS
2246
2247 l_initiator_rname wf_roles.name%type;
2248
2249 BEGIN
2250 l_initiator_rname :=
2251 wf_engine.GetItemAttrText(itemtype => itemtype,
2252 itemkey => itemkey,
2253 aname => 'INITIATOR');
2254
2255 wf_engine.SetItemAttrText(itemtype => itemtype,
2256 itemkey => itemkey,
2257 aname => 'WF_ADMINISTRATOR',
2258 avalue => l_initiator_rname);
2259
2260 --Bug 7135471
2261 If itemkey is not null then
2262
2263 if(item_attribute_exists(itemtype,itemkey,'PDF_ATTACHMENT')) then
2264 hr_utility.trace('PDF_ATTACHMENT attribute exists');
2265 else
2266 wf_engine.additemattr
2267 (itemtype => itemtype,
2268 itemkey => itemkey,
2269 aname => 'PDF_ATTACHMENT',
2270 text_value => '');
2271 end if;
2272
2273 end if;
2274
2275
2276 result := 'COMPLETE';
2277
2278 ---- call the user hook for wf admin role here.
2279 ---- By default setting it to Initiator.
2280 psp_er_wf_custom.set_custom_wf_admin(itemtype ,
2281 itemkey ,
2282 actid ,
2283 funcmode ,
2284 result);
2285 EXCEPTION
2286 WHEN OTHERS THEN
2287 result := 'ERROR';
2288 wf_core.context('PSP_EFFORT_REPORTS', 'SET_WF_ADMIN', itemtype, itemkey, to_char(actid), funcmode);
2289 raise;
2290 END;
2291
2292 PROCEDURE get_timeout_approver(itemtype IN varchar2,
2293 itemkey IN varchar2,
2294 actid IN number,
2295 funcmode IN varchar2,
2296 result OUT nocopy varchar2) IS
2297 BEGIN
2298 result := 'COMPLETE';
2299 ---- user hook for time out approver
2300 psp_er_Wf_custom.set_custom_timeout_approver(itemtype ,
2301 itemkey ,
2302 actid ,
2303 funcmode ,
2304 result );
2305
2306 EXCEPTION
2307 WHEN OTHERS THEN
2308 -- Populate stack with error message
2309 result := 'ERROR';
2310 wf_core.context('PSP_EFFORT_REPORTS', 'GET_TIMEOUT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
2311 RAISE;
2312 END;
2313
2314 PROCEDURE preview_er (itemtype IN varchar2,
2315 itemkey IN varchar2,
2316 actid IN number,
2317 funcmode IN varchar2,
2318 result OUT nocopy varchar2) IS
2319 l_preview_flag psp_report_templates_h.preview_effort_report_flag%TYPE;
2320 l_request_id NUMBER(15, 0);
2321 CURSOR preview_er_cur IS
2322 SELECT preview_effort_report_flag
2323 FROM psp_report_templates_h prth
2324 WHERE prth.request_id = l_request_id;
2325 BEGIN
2326 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
2327 itemkey => itemkey,
2328 aname => 'REQUEST_ID');
2329
2330 OPEN preview_er_cur;
2331 FETCH preview_er_cur INTO l_preview_flag;
2332 CLOSE preview_er_cur;
2333
2334 result := 'COMPLETE:' || l_preview_flag;
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 -- Populate stack with error message
2338 result := 'ERROR';
2339 wf_core.context('PSP_EFFORT_REPORTS', 'PREVIEW_ER', itemtype, itemkey, TO_CHAR(actid), funcmode);
2340 RAISE;
2341 END preview_er;
2342
2343 -- Added this function for Bug 7135471
2344 FUNCTION item_attribute_exists
2345 (p_item_type in wf_items.item_type%type,
2346 p_item_key in wf_item_activity_statuses.item_key%type,
2347 p_name in wf_item_attribute_values.name%type)
2348 return boolean is
2349
2350 l_dummy varchar2(1);
2351
2352 BEGIN
2353
2354 select 'Y'
2355 into l_dummy
2356 from wf_item_attribute_values
2357 where item_type = p_item_type
2358 and item_key = p_item_key
2359 and name = p_name;
2360
2361 return true;
2362
2363 Exception
2364 When others then
2365 return false;
2366
2367 END item_attribute_exists;
2368
2369 end;