[Home] [Help]
PACKAGE BODY: APPS.PSP_ER_WORKFLOW
Source
1 PACKAGE BODY PSP_ER_WORKFLOW as
2 /* $Header: PSPERWFB.pls 120.20.12020000.2 2012/10/08 19:59:22 lkodaman 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',
385 itemkey => wf_ikey_array(k),
386 aname => '#ATTACHMENTS',
387 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || wf_ikey_array(k));
388
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
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
525
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 l_resp_appl_id number; -- 10185794
622 l_user number;
623 l_resp_id number;
624 l_set_options_success boolean;
625
626 begin
627
628 /*Changes for the bug 10185794 */
629 select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
630 select apps.fnd_global.resp_id into l_resp_id from dual;
631 select apps.fnd_global.user_id into l_user from dual;
632
633 if l_resp_id = -1
634 then
635 l_set_options_success := fnd_request.set_options(datagroup=> 'Standard');
636 end if;
637 /*End of changes for the bug 10185794 */
638
639 l_same_approval_level := 'N';
640 ---hr_utility.trace_on('Y','WF-1');
641
642 l_user_id := fnd_global.user_id;
643 l_login_id := fnd_global.login_id;
644 if funcmode = 'RUN' then
645 l_approver_order_num :=
646 wf_engine.GetItemAttrNumber(itemtype => itemtype,
647 itemkey => itemkey,
648 aname => 'APPROVER_ORDER_NUM');
649 l_request_id :=
650 wf_engine.GetItemAttrNumber(itemtype => itemtype,
651 itemkey => itemkey,
652 aname => 'REQUEST_ID');
653 open get_approval_type;
654 fetch get_approval_type into l_approval_type;
655 close get_approval_type;
656
657 --- all ame transactions have been grouped based on the first
658 --- approver. Since approver is already avaialable, no need to call AME
659 if l_approver_order_num = 1 then
660 open role_name_cur;
661 fetch role_name_cur into l_wf_rname;
662 close role_name_cur;
663 l_role_display_name := wf_directory.GetRoleDisplayName(l_wf_rname);
664 result := 'COMPLETE:FOUND';
665 elsif l_approval_type in ('PMG', 'TMG', 'GPI') then
666 update psp_eff_reports er
667 set er.status_code = 'A',
668 er.last_update_date = sysdate,
669 er.last_update_login = fnd_global.login_id,
670 er.last_updated_by = fnd_global.user_id
671 where er.status_code = 'N'
672 and er.effort_report_id in
673 (select erd.effort_report_id
674 from psp_eff_report_details erd,
675 psp_eff_report_approvals era
676 where era.effort_report_detail_id = erd.effort_report_detail_id
677 and era.wf_item_key = itemkey)
678 and not exists
679 (select 1
680 from psp_eff_report_approvals era,
681 psp_eff_report_details erd
682 where era.effort_report_detail_id = erd.effort_report_detail_id
683 and erd.effort_report_id = er.effort_report_id
684 and era.approval_status <> 'A');
685 result := 'COMPLETE:NOTFOUND';
686
687 else
688 open same_approval_level_cur(l_approver_order_num);
689 fetch same_approval_level_cur into l_wf_rname, l_role_display_name;
690 if same_approval_level_cur%notfound then
691 l_same_approval_level := 'N';
692 else
693 l_same_approval_level := 'Y';
694 result := 'COMPLETE:FOUND';
695 end if;
696 close same_approval_level_cur;
697 if l_same_approval_level = 'N' then
698 open ame_txn_id_cur;
699 fetch ame_txn_id_cur bulk collect into ame_txn_id_array;
700 close ame_txn_id_cur;
701 i := 1;
702 loop
703 if i > ame_txn_id_array.count then
704 exit;
705 end if;
706 ame_api2.getNextApprovers4(applicationidIn => 8403,
707 transactiontypeIn => 'PSP-ER-APPROVAL',
708 transactionIdIn => ame_txn_id_array(i),
709 flagApproversAsNotifiedIn => 'Y',
710 approvalProcessCompleteYNout => l_process_complete,
711 nextApproversOut=> l_next_approver);
712 --- ignoring parallel approvers, assuming only one approver role
713 if l_next_approver.count = 0 then
714 if result is null then
715 result := 'COMPLETE:NOTFOUND';
716 l_temp_not_found := 'Y';
717 end if;
718 else
719 l_wf_rname := l_next_approver(1).name;
720 l_role_display_name := l_next_approver(1).display_name;
721 result := 'COMPLETE:FOUND';
722 insert into psp_eff_report_approvals
723 (effort_report_approval_id,
724 effort_report_detail_id,
725 wf_role_name,
726 wf_orig_system_id,
727 wf_orig_system,
728 approver_order_num,
729 approval_status,
730 wf_item_key,
731 last_update_date,
732 last_updated_by,
733 last_update_login,
734 creation_date,
735 created_by,
736 wf_role_display_name,
737 object_version_number)
738 select psp_eff_report_approvals_s.nextval,
739 erd.effort_report_detail_id,
740 l_next_approver(1).name,
741 l_next_approver(1).orig_system_id,
742 l_next_approver(1).orig_system,
743 l_approver_order_num,
744 nvl(l_next_approver(1).approval_status,'P'),
745 itemkey,
746 sysdate,
747 l_user_id,
748 l_login_id,
749 sysdate,
750 l_user_id,
751 l_next_approver(1).display_name,
752 1
753 from psp_eff_report_details erd
754 where erd.ame_transaction_id = ame_txn_id_array(i)
755 and erd.effort_report_id in
756 (select er.effort_report_id
757 from psp_eff_reports er
758 where er.request_id = l_request_id
759 and er.status_code = 'N');
760 hr_utility.trace(' ER workflow -> order number = '||l_next_approver(1).approver_order_number);
761 -- copy the previous approvers overwrites to the new approver
762 if l_next_approver(1).approver_order_number > 1 then
763 update psp_eff_report_approvals A1
764 set (A1.actual_cost_share, A1.overwritten_effort_percent, comments,
765 /* Add DF Columns for Hospital Effort report */
766 pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
767 pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
768 pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
769 eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
770 eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
771 eff_information11, eff_information12, eff_information13, eff_information14, eff_information15) =
772 (select A2.actual_cost_share, A2.overwritten_effort_percent, comments,
773 /* Add DF Columns for Hospital Effort report */
774 pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
775 pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
776 pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
777 eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
778 eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
779 eff_information11, eff_information12, eff_information13, eff_information14, eff_information15
780 from psp_eff_report_approvals A2
781 where A1.effort_report_detail_id = A2.effort_report_detail_id
782 /* Bug 5235725: Replacing l_next_approver(1).approver_order_number with l_approver_order_num.
783 In case of reassign a notification function ame_api2.getNextApprovers4 resturns nextApproversOut.approver_order_number with an
784 incremented value while in our system (Effort rporting) we do not increment the approver_order_number in case of reassign */
785 -- and A2.approver_order_num = l_next_approver(1).approver_order_number -1)
786 and A2.approver_order_num = l_approver_order_num -1)
787 -- where A1.approver_order_num = l_next_approver(1).approver_order_number and
788 where A1.approver_order_num = l_approver_order_num and
789 A1.effort_report_detail_id in
790 (select erd.effort_report_detail_id
791 from psp_eff_reports er,
792 psp_eff_report_details erd
793 where er.request_id = l_request_id
794 and er.effort_report_id = erd.effort_report_id
795 and erd.ame_transaction_id = ame_txn_id_array(i)
796 and er.status_code = 'N');
797 hr_utility.trace(' ER workflow -> update count= '||sql%rowcount);
798 end if;
799
800
801 end if;
802 i := i +1;
803 end loop;
804 if l_temp_not_found = 'Y' then
805 update psp_eff_reports er
806 set er.status_code = 'A',
807 er.last_update_date = sysdate,
808 er.last_update_login = fnd_global.login_id,
809 er.last_updated_by = fnd_global.user_id
810 where er.status_code = 'N'
811 and er.effort_report_id in
812 (select erd.effort_report_id
813 from psp_eff_report_details erd,
814 psp_eff_report_approvals era
815 where era.effort_report_detail_id = erd.effort_report_detail_id
816 and era.wf_item_key = itemkey)
817 and not exists
818 (select 1
819 from psp_eff_report_approvals era,
820 psp_eff_report_details erd
821 where era.effort_report_detail_id = erd.effort_report_detail_id
822 and erd.effort_report_id = er.effort_report_id
823 and era.approval_status <> 'A');
824 end if;
825 end if; --- same approval level flag = N
826 end if; --- approver order number > 1
827 if result = 'COMPLETE:FOUND' then
828 wf_engine.SetItemAttrText(itemtype => itemtype,
829 itemkey => itemkey,
830 aname => 'APPROVER_ROLE_NAME',
831 avalue => l_wf_rname);
832 wf_engine.SetItemAttrText(itemtype => itemtype,
833 itemkey => itemkey,
834 aname => 'APPROVER_DISPLAY_NAME',
835 avalue => l_role_display_name);
836 wf_engine.SetItemAttrText(itemtype => itemtype,
837 itemkey => itemkey,
838 aname => '#ATTACHMENTS',
839 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || itemkey || l_wf_rname );
840
841 --Bug 7135471
842 wf_engine.SetItemAttrText(itemType => itemtype,
843 itemKey => itemkey,
844 aname => 'PDF_ATTACHMENT',
845 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || itemtype || ':' || itemkey);
846
847
848 /*Added for bug 7004679 */
849 wf_engine.setitemowner(itemtype => itemtype,
850 itemkey => itemkey,
851 owner => l_wf_rname);
852
853
854
855 /*
856 wf_engine.SetItemAttrText(itemtype => itemtype,
857 itemkey => itemkey,
858 aname => 'EMBED_WF_ER_DETAILS',
859 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_WF_ER_DETAILS&wfItemKey='||itemkey
860 ||'&requestId='||l_request_id
861 ||'&wfRoleName='||l_wf_rname
862 ||'&approverOrderNum='||l_approver_order_num); */
863
864 hr_utility.trace('GET_NEXT value for temp_approver_order ='|| l_approver_order_num);
865 wf_engine.SetItemAttrNumber(itemtype => itemtype,
866 itemkey => itemkey,
867 aname => 'TEMP_APPROVER_ORDER',
868 avalue => l_approver_order_num);
869
870 hr_utility.trace('er_workflow --> GET NEXT APPROVER jsp string='||
871 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_WF_ER_DETAILS&wfItemKey='||itemkey
872 ||'&requestId='||l_request_id
873 ||'&wfRoleName='||l_wf_rname
874 ||'&approverOrderNum='||l_approver_order_num);
875
876 if l_same_approval_level = 'N' then
877 l_approver_order_num := l_approver_order_num + 1;
878 wf_engine.SetItemAttrNumber(itemtype => itemtype,
879 itemkey => itemkey,
880 aname => 'APPROVER_ORDER_NUM',
881 avalue => l_approver_order_num);
882 end if;
883 end if;
884
885 -- Code Moved from process Approvals
886 psp_xmlgen.update_er_person_xml (p_wf_item_key => itemkey,
887 p_return_status => l_return_status);
888
889 IF (l_return_status = 'E') THEN
890 RAISE fnd_api.g_exc_unexpected_error;
891 END IF;
892
893 end if; --- funcmode = RUN
894 exception
895 when others then
896 result := 'ERROR';
897 --result := substr(sqlerrm,15);
898 wf_core.context('PSP_EFFORT_REPORTS', 'GET_NEXT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
899 raise;
900 end;
901
902 procedure process_rejections(itemtype in varchar2,
903 itemkey in varchar2,
904 actid in number,
905 funcmode in varchar2,
906 result out nocopy varchar2) is
907 l_rname wf_roles.name%type;
908 l_request_id integer;
909 l_effort_report_id integer;
910 l_initiator_rname wf_roles.name%type;
911 l_orig_system_id wf_roles.orig_system_id%type;
912 l_orig_system wf_roles.orig_system%type;
913 l_txn_id varchar2(50);
914 approver_rec ame_util.approverRecord2;
915 l_recipnt_role varchar2(300);
916
917 cursor effort_report_id_cur is
918 select effort_report_id
919 from psp_eff_reports
920 where effort_report_id in
921 (select effort_report_id
922 from psp_eff_report_details
923 where effort_report_detail_id in
924 (select effort_report_detail_id
925 from psp_eff_report_approvals
926 where wf_item_key = itemkey
927 and wf_role_name = l_rname));
928
929 --- one person can have more than one approver.
930 --- partial approval by 2 approvers.
931 cursor past_approvers_cur is
932 select distinct era.wf_role_name
933 from psp_eff_reports er,
934 psp_eff_report_details erd,
935 psp_eff_report_approvals era,
936 fnd_user fu -- Bug 6641216
937 where era.effort_report_detail_id = erd.effort_report_detail_id
938 and erd.effort_report_id = er.effort_report_id
939 and era.approval_status in ( 'A','P')
940 and er.request_id = l_request_id
941 and er.effort_report_id in
942 (select effort_report_id
943 from psp_eff_reports
944 where effort_report_id in
945 (select effort_report_id
946 from psp_eff_report_details
947 where effort_report_detail_id in
948 (select effort_report_detail_id
949 from psp_eff_report_approvals
950 where wf_item_key = itemkey
951 and wf_role_name = l_rname)))
952 and era.wf_role_name = fu.user_name -- Bug 6641216
953 and trunc(sysdate) between trunc(fu.start_date) and nvl(trunc(fu.end_date),trunc(sysdate)) -- Bug 6641216
954 union
955 select name
956 from wf_roles
957 where orig_system = 'PER'
958 and orig_system_id in
959 (select initiator_person_id
960 from psp_report_templates_h
961 where request_id = l_request_id);
962
963 cursor get_txn_id_cur is
964 select distinct ame_transaction_id
965 from psp_eff_report_details erd,
966 psp_eff_report_approvals era
967 where erd.effort_report_detail_id = era.effort_report_detail_id
968 and era.wf_item_key = itemkey
969 and era.approval_status = 'P'
970 and era.wf_role_name = l_rname
971 and era.wf_orig_system_id = l_orig_system_id
972 and era.wf_orig_system = l_orig_system;
973
974 --- same level unapproved ER details
975 cursor same_approval_level_cur(p_approver_order_num in integer) is
976 select wf_role_name
977 from psp_eff_report_approvals
978 where wf_item_key = itemkey
979 and approver_order_num = p_approver_order_num
980 and approval_status = 'P'
981 and rownum = 1;
982
983 l_approver_order_num integer;
984
985 begin
986 l_recipnt_role := 'PSP_PAST_APPROVERS_'||itemkey;
987 ---hr_utility.trace_on('Y','WF-1');
988 if funcmode = 'RUN' then
989 hr_utility.trace('er_workflow -->1 ');
990 l_request_id :=
991 wf_engine.GetItemAttrNumber(itemtype => itemtype,
992 itemkey => itemkey,
993 aname => 'REQUEST_ID');
994 l_rname :=
995 wf_engine.GetItemAttrText(itemtype => itemtype,
996 itemkey => itemkey,
997 aname => 'APPROVER_ROLE_NAME');
998
999 l_approver_order_num :=
1000 wf_engine.GetItemAttrNumber(itemtype => itemtype,
1001 itemkey => itemkey,
1002 aname => 'APPROVER_ORDER_NUM');
1003 select orig_system_id,
1004 orig_system
1005 into l_orig_system_id,
1006 l_orig_system
1007 from wf_roles
1008 where name = l_rname;
1009
1010 approver_rec.name := l_rname;
1011 approver_rec.orig_system := l_orig_system;
1012 approver_rec.orig_system_id := l_orig_system_id;
1013 approver_rec.approval_status:= 'REJECTED';
1014
1015 hr_utility.trace('er_workflow -->2 ');
1016 open get_txn_id_cur;
1017 loop
1018 hr_utility.trace('er_workflow -->3 ');
1019 fetch get_txn_id_cur into l_txn_id;
1020 if get_txn_id_cur%notfound then
1021 close get_txn_id_cur;
1022 exit;
1023 end if;
1024 hr_utility.trace('er_workflow -->5 ');
1025
1026 ame_api2.updateapprovalstatus(applicationidin => 8403,
1027 transactiontypein => 'PSP-ER-APPROVAL',
1028 transactionidin => l_txn_id,
1029 approverin => approver_rec);
1030 hr_utility.trace('er_workflow -->6 ');
1031 end loop;
1032
1033 hr_utility.trace('er_workflow -->7 ');
1034 update psp_eff_report_approvals era
1035 set era.approval_status = 'S',
1036 era.response_date = sysdate,
1037 era.last_update_date = sysdate,
1038 era.last_update_login = fnd_global.login_id,
1039 era.last_updated_by = fnd_global.user_id
1040 where era.wf_item_key = itemkey
1041 and era.wf_role_name = l_rname
1042 and exists
1043 ( select erd.effort_report_detail_id
1044 from psp_eff_report_details erd,
1045 psp_eff_reports er
1046 where er.effort_report_id = erd.effort_report_id
1047 and erd.effort_report_detail_id = era.effort_report_detail_id
1048 and er.status_code = 'S' );
1049
1050
1051 update psp_eff_report_approvals era
1052 set era.approval_status = 'R',
1053 era.response_date = sysdate,
1054 era.last_update_date = sysdate,
1055 era.last_update_login = fnd_global.login_id,
1056 era.last_updated_by = fnd_global.user_id
1057 where era.wf_item_key = itemkey
1058 and era.wf_role_name = l_rname
1059 and not exists
1060 ( select erd.effort_report_detail_id
1061 from psp_eff_report_details erd,
1062 psp_eff_reports er
1063 where er.effort_report_id = erd.effort_report_id
1064 and erd.effort_report_detail_id = era.effort_report_detail_id
1065 and er.status_code = 'S' );
1066
1067 hr_utility.trace('er_workflow -->8 ');
1068 open effort_report_id_cur;
1069 loop
1070 fetch effort_report_id_cur into l_effort_report_id;
1071 hr_utility.trace('er_workflow -->9 ');
1072 if effort_report_id_cur%notfound then
1073 close effort_report_id_cur;
1074 exit;
1075 end if;
1076 update psp_eff_reports
1077 set status_code = 'R',
1078 last_update_date = sysdate,
1079 last_update_login = fnd_global.login_id,
1080 last_updated_by = fnd_global.user_id
1081 where effort_report_id = l_effort_report_id;
1082 hr_utility.trace('er_workflow -->19 ');
1083 end loop;
1084
1085 l_initiator_rname :=
1086 wf_engine.GetItemAttrText(itemtype => itemtype,
1087 itemkey => itemkey,
1088 aname => 'INITIATOR');
1089
1090 wf_directory.createAdhocRole(l_recipnt_role,
1091 l_recipnt_role);
1092 hr_utility.trace('er_workflow -->29 ');
1093 open past_approvers_cur;
1094 loop
1095 hr_utility.trace('er_workflow -->39 ');
1096 fetch past_approvers_cur into l_rname;
1097 if past_approvers_cur%notfound then
1098 close past_approvers_cur;
1099 exit;
1100 end if;
1101 hr_utility.trace('er_workflow -->49 ');
1102 wf_directory.AddUsersToAdHocRole
1103 (role_name => l_recipnt_role,
1104 role_users => l_rname);
1105 end loop;
1106
1107 hr_utility.trace('er_workflow -->59 ');
1108 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1109 itemkey => itemkey,
1110 aname => 'NOTIFY_REJECTIONS_ROLE',
1111 avalue => l_recipnt_role);
1112 hr_utility.trace('er_workflow -->69 ');
1113
1114 result := 'COMPLETE:REJECTED';
1115 open same_approval_level_cur(l_approver_order_num);
1116 fetch same_approval_level_cur into l_rname;
1117 --- this user has rejected.. but there is one or more different
1118 --- users,from which response is required.
1119 if same_approval_level_cur%found then
1120 result := 'COMPLETE:MORE_RESP_REQD';
1121 end if;
1122 close same_approval_level_cur;
1123 hr_utility.trace('er_workflow -->79 ');
1124 end if; --- funcmode
1125 hr_utility.trace('er_workflow -->89 ');
1126 exception
1127 when others then
1128 hr_utility.trace('er_workflow -->when others then exception occured Error = '||sqlerrm);
1129 result := 'ERROR';
1130 wf_core.context('PSP_EFFORT_REPORTS', 'PROCESS_REJECTIONS', itemtype, itemkey, to_char(actid), funcmode);
1131 --- debug;
1132 raise;
1133 end;
1134
1135 procedure process_approvals(itemtype in varchar2,
1136 itemkey in varchar2,
1137 actid in number,
1138 funcmode in varchar2,
1139 result out nocopy varchar2) is
1140
1141 l_rname wf_roles.name%type;
1142 l_orig_system_id wf_roles.orig_system_id%type;
1143 l_orig_system wf_roles.orig_system%type;
1144 l_txn_id varchar2(50);
1145 approver_rec ame_util.approverRecord2;
1146 l_return_status CHAR(1);
1147
1148 cursor get_orig_system is
1149 select wf_orig_system_id,
1150 wf_orig_system
1151 from psp_eff_report_approvals
1152 where wf_item_key = itemkey
1153 and approval_status = 'P'
1154 and wf_role_name = l_rname;
1155
1156 cursor get_txn_id_cur is
1157 select distinct ame_transaction_id
1158 from psp_eff_report_details erd,
1159 psp_eff_report_approvals era
1160 where erd.effort_report_detail_id = era.effort_report_detail_id
1161 and era.wf_item_key = itemkey
1162 and era.approval_status = 'A'
1163 and era.wf_role_name = l_rname
1164 and era.wf_orig_system_id = l_orig_system_id
1165 and era.wf_orig_system = l_orig_system;
1166
1167 l_resp_appl_id number; -- 10185794
1168 l_user number;
1169 l_resp_id number;
1170 l_set_options_success boolean;
1171
1172 begin
1173
1174 /*Changes for the bug 10185794 */
1175 select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
1176 select apps.fnd_global.resp_id into l_resp_id from dual;
1177 select apps.fnd_global.user_id into l_user from dual;
1178
1179 if l_resp_id = -1
1180 then
1181 l_set_options_success := fnd_request.set_options(datagroup=> 'Standard');
1182 end if;
1183 /*End of changes for the bug 10185794 */
1184
1185 if funcmode = 'RUN' then
1186 l_rname :=
1187 wf_engine.GetItemAttrText(itemtype => itemtype,
1188 itemkey => itemkey,
1189 aname => 'APPROVER_ROLE_NAME');
1190
1191 open get_orig_system;
1192 fetch get_orig_system into l_orig_system_id, l_orig_system;
1193 close get_orig_system;
1194
1195
1196 -- Intorduced for Supercedence to set status_code = 'S'
1197 -- when the psp_eff_reports gets superceded
1198
1199 update psp_eff_report_approvals era
1200 set era.approval_status = 'S',
1201 era.response_date = SYSDATE,
1202 era.last_update_date = sysdate,
1203 era.last_update_login = fnd_global.login_id,
1204 era.last_updated_by = fnd_global.user_id
1205 where era.wf_item_key = itemkey
1206 and era.wf_role_name = l_rname
1207 and era.wf_orig_system_id = l_orig_system_id
1208 and era.wf_orig_system = l_orig_system
1209 and era.approval_status = 'P'
1210 and exists
1211 ( select erd.effort_report_detail_id
1212 from psp_eff_report_details erd,
1213 psp_eff_reports er
1214 where er.effort_report_id = erd.effort_report_id
1215 and erd.effort_report_detail_id = era.effort_report_detail_id
1216 and er.status_code = 'S' );
1217
1218
1219 update psp_eff_report_approvals era
1220 set era.approval_status = 'A',
1221 era.response_date = SYSDATE,
1222 era.last_update_date = sysdate,
1223 era.last_update_login = fnd_global.login_id,
1224 era.last_updated_by = fnd_global.user_id
1225 where era.wf_item_key = itemkey
1226 and era.wf_role_name = l_rname
1227 and era.wf_orig_system_id = l_orig_system_id
1228 and era.wf_orig_system = l_orig_system
1229 and era.approval_status = 'P'
1230 and not exists
1231 ( select erd.effort_report_detail_id
1232 from psp_eff_report_details erd,
1233 psp_eff_reports er
1234 where er.effort_report_id = erd.effort_report_id
1235 and erd.effort_report_detail_id = era.effort_report_detail_id
1236 and er.status_code = 'S' );
1237
1238
1239
1240
1241 approver_rec.name := l_rname;
1242 approver_rec.orig_system := l_orig_system;
1243 approver_rec.orig_system_id := l_orig_system_id;
1244 approver_rec.approval_status:= ame_util.approvedStatus;
1245
1246 open get_txn_id_cur;
1247 loop
1248 fetch get_txn_id_cur into l_txn_id;
1249 if get_txn_id_cur%notfound then
1250 close get_txn_id_cur;
1251 exit;
1252 end if;
1253 ame_api2.updateapprovalstatus(applicationidin => 8403,
1254 transactiontypein => 'PSP-ER-APPROVAL',
1255 transactionidin => l_txn_id,
1256 approverin => approver_rec);
1257 end loop;
1258 result := 'COMPLETE';
1259 -- MOving this code to Procedure get_next Approver
1260
1261 psp_xmlgen.update_er_person_xml (p_wf_item_key => itemkey,
1262 p_return_status => l_return_status);
1263
1264 IF (l_return_status = 'E') THEN
1265 RAISE fnd_api.g_exc_unexpected_error;
1266 END IF;
1267
1268 end if; ---funcmode
1269 exception
1270 when others then
1271 result := 'ERROR';
1272 --result := result||'=='||sqlerrm;
1273 wf_core.context('PSP_EFFORT_REPORTS', 'PROCESS_APPROVALS', itemtype, itemkey, to_char(actid), funcmode);
1274 raise;
1275 end;
1276 procedure approver_post_notify(itemtype in varchar2,
1277 itemkey in varchar2,
1278 actid in number,
1279 funcmode in varchar2,
1280 result out nocopy varchar2) is
1281
1282 l_rname wf_roles.name%type;
1283 l_orig_system_id wf_roles.orig_system_id%type;
1284 l_orig_system wf_roles.orig_system%type;
1285 l_rname2 wf_roles.name%type;
1286 l_orig_system_id2 wf_roles.orig_system_id%type;
1287 l_orig_system2 wf_roles.orig_system%type;
1288 l_txn_id varchar2(50);
1289 approver_rec ame_util.approverRecord2;
1290 forward_rec ame_util.approverRecord2;
1291 l_return_status CHAR(1);
1292
1293 cursor get_orig_system is
1294 select wf_orig_system_id,
1295 wf_orig_system
1296 from psp_eff_report_approvals
1297 where wf_item_key = itemkey
1298 and approval_status = 'P'
1299 and wf_role_name = l_rname;
1300
1301 cursor get_txn_id_cur is
1302 select distinct ame_transaction_id
1303 from psp_eff_report_details erd,
1304 psp_eff_report_approvals era
1305 where erd.effort_report_detail_id = era.effort_report_detail_id
1306 and era.wf_item_key = itemkey
1307 and era.approval_status = 'P'
1308 and era.wf_role_name = l_rname2
1309 and era.wf_orig_system_id = l_orig_system_id2
1310 and era.wf_orig_system = l_orig_system2;
1311
1312 cursor get_forwarde_details is
1313 select orig_system,
1314 orig_system_id,
1315 display_name
1316 from wf_roles
1317 where name = l_rname2;
1318 l_role_display_name2 wf_roles.display_name%type;
1319 l_nid number;
1320
1321 /* Added for hundred percent validation for overridden effort */
1322 l_result varchar2(240);
1323 l_request_id Number;
1324 l_wf_role_name VARCHAR2(320);
1325 l_wf_item_key VARCHAR2(240);
1326 l_approver_order_num Number;
1327 l_hundred_pcrent_eff_flag Varchar2(1);
1328 l_start_date Date;
1329 l_end_date Date;
1330 l_person_id Number;
1331 l_assignment_id Number;
1332 l_full_name varchar2(240);
1333 l_assignemnt_number varchar2(30);
1334 l_last_approver varchar2(1);
1335 l_temp Number;
1336 l_percent Number;
1337 l_error_message varchar2(4000);
1338
1339 l_salary_amt Number; --14653352
1340
1341 CURSOR get_person_Assignment_list_csr IS
1342 SELECT distinct prth.hundred_pcent_eff_at_per_asg, per.start_date,
1343 per.end_date, per.person_id, perd.assignment_id, per.full_name, perd.assignment_number
1344 FROM psp_report_templates_h prth,
1345 psp_eff_reports per,
1346 psp_eff_report_details perd,
1347 psp_eff_report_approvals prea
1348 WHERE prth.request_id = per.request_id
1349 AND per.effort_report_id = perd.effort_report_id
1350 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1351 AND per.request_id = l_request_id
1352 AND prea.wf_role_name = l_wf_role_name
1353 AND prea.wf_item_key = l_wf_item_key
1354 AND prea.approver_order_num = l_approver_order_num;
1355
1356 CURSOR is_person_last_approver_csr IS
1357 SELECT 1
1358 FROM psp_eff_reports per,
1359 psp_eff_report_details perd,
1360 psp_eff_report_approvals prea
1361 WHERE per.effort_report_id = perd.effort_report_id
1362 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1363 AND per.person_id = l_person_id
1364 AND per.start_date = l_start_date
1365 AND per.end_date = l_end_date
1366 AND prea.wf_role_name <> l_wf_role_name
1367 AND approver_order_num = l_approver_order_num
1368 AND prea.approval_status ='P'
1369 AND per.status_code IN ('N','A');
1370
1371 CURSOR is_asg_last_approver_csr IS
1372 SELECT 1
1373 FROM psp_eff_reports per,
1374 psp_eff_report_details perd,
1375 psp_eff_report_approvals prea
1376 WHERE per.effort_report_id = perd.effort_report_id
1377 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1378 AND perd.assignment_id = l_assignment_id
1379 AND per.start_date = l_start_date
1380 AND per.end_date = l_end_date
1381 AND prea.wf_role_name <> l_wf_role_name
1382 AND approver_order_num = l_approver_order_num
1383 AND prea.approval_status = 'P'
1384 AND per.status_code IN ('N','A');
1385
1386
1387 CURSOR person_percent_csr IS
1388 SELECT sum(nvl(overwritten_effort_percent,payroll_percent)),sum(actual_salary_amt) -- 14653352 : Added sum(actual_salary_amt)
1389 FROM psp_eff_reports per,
1390 psp_eff_report_details perd,
1391 psp_eff_report_approvals prea
1392 WHERE per.effort_report_id = perd.effort_report_id
1393 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1394 AND per.person_id = l_person_id
1395 AND per.start_date = l_start_date
1396 AND per.end_date = l_end_date
1397 AND approver_order_num = l_approver_order_num
1398 AND prea.approval_status IN ('P','A')
1399 AND per.status_code IN ('N','A');
1400
1401 CURSOR assignment_percent_csr IS
1402 SELECT sum(nvl(overwritten_effort_percent,payroll_percent)),sum(actual_salary_amt) -- 14653352 : Added sum(actual_salary_amt)
1403 FROM psp_eff_reports per,
1404 psp_eff_report_details perd,
1405 psp_eff_report_approvals prea
1406 WHERE per.effort_report_id = perd.effort_report_id
1407 AND perd.effort_report_detail_id = prea.effort_report_detail_id
1408 AND perd.assignment_id = l_assignment_id
1409 AND per.start_date = l_start_date
1410 AND per.end_date = l_end_date
1411 AND approver_order_num = l_approver_order_num
1412 AND prea.approval_status IN ('P','A')
1413 AND per.status_code IN ('N','A');
1414
1415 BEGIN
1416
1417 /* Added for Hundred percent validation for overriden effort */
1418 IF funcmode in ('RESPOND') THEN
1419 l_result := Wf_Notification.GetAttrText(wf_engine.context_nid, 'RESULT');
1420 IF l_result = 'APPROVED' THEN
1421 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1422 itemkey => itemkey,
1423 aname => 'REQUEST_ID');
1424 l_wf_role_name := wf_engine.GetItemAttrText(itemtype => itemtype,
1425 itemkey => itemkey,
1426 aname => 'APPROVER_ROLE_NAME');
1427 l_approver_order_num := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1428 itemkey => itemkey,
1429 aname => 'APPROVER_ORDER_NUM');
1430 l_approver_order_num := l_approver_order_num - 1;
1431 l_wf_item_key := itemkey;
1432 OPEN get_person_Assignment_list_csr;
1433 LOOP
1434 FETCH get_person_Assignment_list_csr INTO l_hundred_pcrent_eff_flag, l_start_date,
1435 l_end_date, l_person_id, l_assignment_id, l_full_name, l_assignemnt_number;
1436 IF get_person_Assignment_list_csr%NOTFOUND THEN
1437 CLOSE get_person_Assignment_list_csr;
1438 EXIT;
1439 END IF;
1440 IF l_hundred_pcrent_eff_flag = 'P' THEN
1441 OPEN is_person_last_approver_csr;
1442 FETCH is_person_last_approver_csr INTO l_temp ;
1443 IF is_person_last_approver_csr%NOTFOUND THEN
1444 l_last_approver := 'Y';
1445 ELSE
1446 l_last_approver := 'N';
1447 END IF;
1448 CLOSE is_person_last_approver_csr;
1449
1450 IF l_last_approver = 'Y' THEN
1451 OPEN person_percent_csr;
1452 FETCH person_percent_csr into l_percent,l_salary_amt; -- 14653352 : Added l_salary_amt variable
1453 CLOSE person_percent_csr;
1454 IF l_salary_amt <> 0 -- 14653352 : Included if condition which validates amount before validating the percentage.
1455 THEN
1456 IF l_percent <> 100 THEN
1457 FND_MESSAGE.set_name('PSP','PSP_ER_EMP_PERCENT_NOT_100');
1458 fnd_message.set_token('EMP_NAME', l_full_name);
1459 fnd_message.set_token('PERCENT', l_percent);
1460 l_error_message := fnd_message.get;
1461 raise_application_error(-20002, l_error_message);
1462 END IF;
1463 END IF; -- 14653352 : End of If.
1464 END IF;
1465 ELSIF l_hundred_pcrent_eff_flag = 'A' THEN
1466 OPEN is_asg_last_approver_csr;
1467 FETCH is_asg_last_approver_csr INTO l_temp;
1468 IF is_asg_last_approver_csr%NOTFOUND THEN
1469 l_last_approver := 'Y';
1470 ELSE
1471 l_last_approver := 'N';
1472 END IF;
1473 CLOSE is_asg_last_approver_csr;
1474 IF l_last_approver = 'Y' THEN
1475 OPEN assignment_percent_csr;
1476 FETCH assignment_percent_csr into l_percent,l_salary_amt; -- 14653352 : Added l_salary_amt variable
1477 CLOSE assignment_percent_csr;
1478 IF l_salary_amt <> 0 -- 14653352 : Included if condition which validates amount before validating the percentage.
1479 THEN
1480 IF l_percent <> 100 THEN
1481 FND_MESSAGE.set_name('PSP','PSP_ER_ASG_PERCENT_NOT_100');
1482 fnd_message.set_token('EMP_NAME', l_full_name);
1483 fnd_message.set_token('ASG_NUMBER', l_assignemnt_number);
1484 fnd_message.set_token('PERCENT', l_percent);
1485 l_error_message := fnd_message.get;
1486 raise_application_error(-20002, l_error_message);
1487 END IF;
1488 END IF; -- 14653352 : End of If.
1489 END IF;
1490 END IF;
1491 END LOOP;
1492 END IF;
1493 END IF;
1494
1495 if funcmode in ('TRANSFER', 'FORWARD') then
1496 ---hr_utility.trace_on('Y','WF-1');
1497 hr_utility.trace('Post Notification...TRANSFER . FORWARD');
1498 l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1499 itemkey => itemkey,
1500 aname => 'APPROVER_ROLE_NAME');
1501 hr_utility.trace('role name from T and F = '||l_rname);
1502
1503 open get_orig_system;
1504 fetch get_orig_system into l_orig_system_id, l_orig_system;
1505 close get_orig_system;
1506
1507 approver_rec.name := l_rname;
1508 approver_rec.orig_system := l_orig_system;
1509 approver_rec.orig_system_id := l_orig_system_id;
1510 approver_rec.approval_status:= ame_util.forwardStatus;
1511 l_rname2 := wf_engine.context_text;
1512 l_nid := wf_engine.context_nid;
1513 open get_forwarde_details;
1514 fetch get_forwarde_details into l_orig_system2, l_orig_system_id2,l_role_display_name2;
1515 close get_forwarde_details;
1516 forward_rec.name := l_rname2;
1517 hr_utility.trace('post notification...Transfer . Forwardee='||l_rname2||' nid ='||l_nid);
1518 forward_rec.orig_system := l_orig_system2;
1519 forward_rec.orig_system_id := l_orig_system_id2;
1520 forward_rec.approval_status:= ame_util.notifiedStatus;
1521 update psp_eff_report_approvals
1522 set wf_role_name = l_rname2,
1523 wf_role_display_name = l_role_display_name2,
1524 wf_orig_system = l_orig_system2,
1525 wf_orig_system_id = l_orig_system_id2
1526 where wf_item_key = itemkey
1527 and wf_role_name = l_rname
1528 and wf_orig_system_id = l_orig_system_id
1529 and wf_orig_system = l_orig_system
1530 and approval_status = 'P';
1531
1532 -- Added for Bug 6996115
1533 update fnd_attached_documents
1534 set pk1_value = itemkey||l_rname2
1535 where pk1_value = itemkey||l_rname;
1536
1537
1538
1539 wf_engine.SetItemAttrText(itemtype => itemtype,
1540 itemkey => itemkey,
1541 aname => 'APPROVER_ROLE_NAME',
1542 avalue => l_rname2);
1543 wf_engine.SetItemAttrText(itemtype => itemtype,
1544 itemkey => itemkey,
1545 aname => 'APPROVER_DISPLAY_NAME',
1546 avalue => l_role_display_name2);
1547 wf_notification.setAttrText(nid => l_nid,
1548 aname => 'APPROVER_ROLE',
1549 avalue => l_rname2);
1550 open get_txn_id_cur;
1551 loop
1552 fetch get_txn_id_cur into l_txn_id;
1553 if get_txn_id_cur%notfound then
1554 close get_txn_id_cur;
1555 exit;
1556 end if;
1557 hr_utility.trace('Transfer mode CALLING UPDATEAME txn_id ='||l_txn_id);
1558 ame_api2.updateapprovalstatus(applicationidin => 8403,
1559 transactiontypein => 'PSP-ER-APPROVAL',
1560 transactionidin => l_txn_id,
1561 approverin => approver_rec,
1562 forwardeein => forward_rec);
1563 end loop;
1564 else
1565 l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1566 itemkey => itemkey,
1567 aname => 'APPROVER_ROLE_NAME');
1568
1569
1570
1571 hr_utility.trace('funcmode, role name from RUN = '||funcmode||','||l_rname);
1572 end if; ---funcmode
1573
1574 -- BUG 4334816 START
1575 -- New code added to capture the Notification id in psp_eff_report_approvals table
1576 l_nid := wf_engine.context_nid;
1577 l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1578 itemkey => itemkey,
1579 aname => 'APPROVER_ROLE_NAME');
1580
1581 update psp_eff_report_approvals set NOTIFICATION_ID = l_nid
1582 where WF_ITEM_KEY = itemkey
1583 AND WF_ROLE_NAME = l_rname;
1584
1585 -- BUG 4334816 END
1586
1587 exception
1588 when others then
1589 result := 'ERROR';
1590 --result := result||'=='||sqlerrm;
1591 wf_core.context('PSP_EFFORT_REPORTS', 'APPROVER_POST_NOTIFY', itemtype, itemkey, to_char(actid), funcmode);
1592 raise;
1593 end;
1594
1595 procedure set_pdf_gen_failures(itemtype in varchar2,
1596 itemkey in varchar2,
1597 actid in number,
1598 funcmode in varchar2,
1599 result out nocopy varchar2) is
1600 begin
1601 --- delete any left over PDF files due to failed Conc process for generating
1602 --- split PDFs
1603 null;
1604
1605 end;
1606
1607 procedure approver_pdf_fail(itemtype in varchar2,
1608 itemkey in varchar2,
1609 actid in number,
1610 funcmode in varchar2,
1611 result out nocopy varchar2) is
1612
1613 l_person_id integer;
1614 l_rname varchar2(300);
1615 l_request_id integer;
1616
1617 l_pdf_request_id NUMBER;
1618 l_retry_request_id NUMBER;
1619 l_error_count NUMBER;
1620
1621 CURSOR report_error_cur IS
1622 SELECT 1
1623 FROM psp_report_errors
1624 WHERE pdf_request_id = l_pdf_request_id;
1625
1626 /*cursor get_person_id is
1627 select person_id
1628 from psp_eff_reports
1629 where effort_report_id in
1630 (select effort_report_id
1631 from psp_eff_report_details
1632 where request_id = l_request_id
1633 and effort_report_detail_id in
1634 (select effort_report_detail_id
1635 from psp_eff_report_approvals
1636 where wf_item_key = itemkey
1637 and wf_role_name = l_rname));*/
1638 begin
1639 if funcmode = 'RUN' then
1640 l_rname :=
1641 wf_engine.GetItemAttrText(itemtype => itemtype,
1642 itemkey => itemkey,
1643 aname => 'APPROVER_ROLE_NAME');
1644 l_request_id :=
1645 wf_engine.GetItemAttrText(itemtype => itemtype,
1646 itemkey => itemkey,
1647 aname => 'REQUEST_ID');
1648
1649 l_pdf_request_id :=
1650 wf_engine.GetItemAttrNumber(itemtype => itemtype,
1651 itemkey => itemkey,
1652 aname => 'PDF_REQUEST_ID');
1653
1654 l_retry_request_id :=
1655 wf_engine.GetItemAttrNumber(itemtype => itemtype,
1656 itemkey => itemkey,
1657 aname => 'RETRY_REQUEST_ID');
1658
1659 /*
1660 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1661 itemkey => itemkey,
1662 aname => 'WF_EMBED_INITIATOR',
1663 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_INIT_STATUS_DETAILS&erRequestId=-&MSG_REQUEST_ID-'
1664 || '&pdfRequestId=-&PDF_REQUEST_ID-&wfItemKey=-&MSG_IKEY-&processParameters=-&PROCESS_PARAMETERS-');
1665
1666 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1667 itemkey => itemkey,
1668 aname => 'INITIATOR_ERROR_STATUS',
1669 avalue => '');
1670 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1671 itemkey => itemkey,
1672 aname => 'WF_EMBED_INITIATOR_ERRORS',
1673 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_INITIATOR_ERRORS&erRequestId=-&MSG_REQUEST_ID-'
1674 || '&pdfRequestId=-&PDF_REQUEST_ID-&wfItemKey=-&MSG_IKEY-&processParameters=-&PROCESS_PARAMETERS-');
1675 */
1676 /* open get_person_id;
1677 fetch get_person_id into l_person_id;
1678 close get_person_id; */
1679
1680 OPEN report_error_cur;
1681 FETCH report_error_cur INTO l_error_count;
1682 CLOSE report_error_cur;
1683
1684 IF (NVL(l_error_count, 0) = 0) THEN
1685 insert into psp_report_errors
1686 (error_sequence_id, request_id, message_level, source_id,
1687 error_message, retry_request_id, pdf_request_id)
1688 values (psp_report_errors_s.nextval,
1689 l_request_id, 'E',l_person_id,
1690 'PDF Generation Failed, please see the Concurrent process log',
1691 l_retry_request_id, l_pdf_request_id);
1692 END IF;
1693 end if;
1694 result := 'COMPLETE';
1695 exception
1696 when others then
1697 result := 'ERROR';
1698 wf_core.context('PSP_EFFORT_REPORTS', 'APPROVER_PDF_FAIL', itemtype, itemkey, to_char(actid), funcmode);
1699 raise;
1700 end;
1701
1702 procedure start_initiator_wf(p_request_id in integer)
1703 is
1704 l_wf_itemkey varchar2(100);
1705 l_user_name varchar2(100);
1706 l_start_date DATE;
1707 l_end_date DATE;
1708 l_template_name psp_report_templates_h.template_name%TYPE;
1709 l_preview_flag psp_report_templates_h.preview_effort_report_flag%TYPE;
1710 l_time_out integer;
1711
1712 CURSOR template_name_cur IS
1713 SELECT template_name,
1714 preview_effort_report_flag,
1715 notification_reminder_in_days,
1716 fnd_date.canonical_to_date(fnd_date.date_to_canonical(parameter_value_2)) start_date,
1717 fnd_date.canonical_to_date(fnd_date.date_to_canonical(parameter_value_3)) end_date
1718 FROM psp_report_templates_h prth
1719 WHERE prth.request_id = p_request_id;
1720
1721 l_param_string VARCHAR2(2000);
1722 l_icx_date_format VARCHAR2(30);
1723 l_gl_sob NUMBER;
1724
1725 CURSOR get_sob_cur IS
1726 SELECT set_of_books_id
1727 FROM psp_report_templates_h
1728 WHERE request_id = p_request_id;
1729
1730 CURSOR get_param_strings IS
1731 SELECT prt.template_name,
1732 TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), l_icx_date_format) start_date,
1733 TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), l_icx_date_format) end_date,
1734 -- xtt.template_name report_layout,
1735 flv1.meaning sort_option1,
1736 flv2.meaning order_by1,
1737 flv3.meaning sort_option2,
1738 flv4.meaning order_by2,
1739 flv5.meaning sort_option3,
1740 flv6.meaning order_by3,
1741 flv7.meaning sort_option4,
1742 flv8.meaning order_by4
1743 FROM psp_report_templates_h prth,
1744 xdo_templates_tl xtt,
1745 psp_report_templates prt,
1746 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv1,
1747 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv2,
1748 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv3,
1749 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv4,
1750 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv5,
1751 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv6,
1752 (select * from psp_layout_lookup_code_v where set_of_books_id in (-1, l_gl_sob)) flv7,
1753 (select * from fnd_lookup_values_vl where lookup_type = 'PSP_ORDERING_CRITERIA') flv8
1754 WHERE prth.request_id = p_request_id
1755 AND prt.template_id = prth.template_id
1756 AND flv1.lookup_code = prth.parameter_value_5
1757 -- AND flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
1758 AND flv2.lookup_code = prth.parameter_value_6
1759 AND flv3.lookup_code = prth.parameter_value_7
1760 -- AND flv4.lookup_type = 'PSP_ORDERING_CRITERIA'
1761 AND flv4.lookup_code = prth.parameter_value_8
1762 AND flv5.lookup_code (+) = prth.parameter_value_9
1763 -- AND NVL(flv6.lookup_type, 'PSP_ORDERING_CRITERIA') = 'PSP_ORDERING_CRITERIA'
1764 AND flv6.lookup_code (+) = prth.parameter_value_10
1765 AND flv7.lookup_code (+) = prth.parameter_value_11
1766 -- AND NVL(flv8.lookup_type, 'PSP_ORDERING_CRITERIA') = 'PSP_ORDERING_CRITERIA'
1767 AND flv8.lookup_code (+) = prth.parameter_value_12
1768 AND xtt.template_code = prth.report_template_code
1769 AND xtt.application_short_name = 'PSP';
1770
1771 CURSOR payroll_action_id_cur IS
1772 SELECT payroll_action_id
1773 FROM pay_payroll_actions
1774 WHERE request_id = p_request_id;
1775
1776 l_arg1 VARCHAR2(100);
1777 l_arg2 VARCHAR2(100);
1778 l_arg3 VARCHAR2(100);
1779 l_arg4 VARCHAR2(100);
1780 l_arg5 VARCHAR2(100);
1781 l_arg6 VARCHAR2(100);
1782 l_arg7 VARCHAR2(100);
1783 l_arg8 VARCHAR2(100);
1784 l_arg9 VARCHAR2(100);
1785 l_arg10 VARCHAR2(100);
1786 l_arg11 VARCHAR2(100);
1787 l_arg12 VARCHAR2(100);
1788
1789 l_retry_request_id NUMBER(15, 0);
1790 l_payroll_action_id NUMBER(15, 0);
1791 l_emp_matching_selection NUMBER;
1792
1793 CURSOR emp_matching_selection_cur IS
1794 SELECT COUNT(DISTINCT person_id) -- Modified count(*) to count(distinct person_id) for bug fix 4429787
1795 FROM psp_selected_persons_t
1796 WHERE request_id = p_request_id;
1797 begin
1798
1799 l_user_name := fnd_global.user_name;
1800 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
1801 l_retry_request_id := fnd_global.conc_request_id;
1802
1803 OPEN get_sob_cur;
1804 FETCH get_sob_cur INTO l_gl_sob;
1805 CLOSE get_sob_cur;
1806
1807 OPEN payroll_action_id_cur;
1808 FETCH payroll_action_id_cur INTO l_payroll_action_id;
1809 CLOSE payroll_action_id_cur;
1810
1811 select psp_wf_item_key_s.nextval
1812 into l_wf_itemkey
1813 from dual;
1814
1815 OPEN template_name_cur;
1816 FETCH template_name_cur INTO l_template_name, l_preview_flag, l_time_out, l_start_date, l_end_date;
1817 CLOSE template_name_cur;
1818
1819 --BUG 4334816 START
1820 -- code added to store WF_Item_Key in Psp_report_templates_h
1821 update psp_report_templates_h
1822 set INITIATOR_WF_ITEM_KEY = l_wf_itemkey
1823 where request_id = p_request_id;
1824
1825 --BUG 4334816 END
1826
1827 --- dynamic timeout should be expresed in minutes,
1828 --- days to minutes conversion.
1829 l_time_out := l_time_out * 1440;
1830
1831 --dbms_output.put_line('FROM START item key ='||l_wf_itemkey);
1832
1833 wf_engine.CreateProcess(itemtype => 'PSPERAVL',
1834 itemkey => l_wf_itemkey,
1835 process => 'INITIATOR_PROCESS');
1836
1837 hr_utility.trace('er_workflow --> start_int: reqid ='||to_char(p_request_id));
1838
1839 /*Added for bug 7004679 */
1840 wf_engine.setitemowner(itemtype => 'PSPERAVL',
1841 itemkey => l_wf_itemkey,
1842 owner => l_user_name);
1843
1844 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1845 itemkey => l_wf_itemkey,
1846 aname => 'REQUEST_ID',
1847 avalue => p_request_id);
1848
1849 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1850 itemkey => l_wf_itemkey,
1851 aname => 'TIMEOUT',
1852 avalue => l_time_out);
1853
1854 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1855 itemkey => l_wf_itemkey,
1856 aname => 'INITIATOR',
1857 avalue => l_user_name);
1858
1859 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1860 itemkey => l_wf_itemkey,
1861 aname => 'WF_EMBED_INITIATOR',
1862 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_INITIATOR_DETAILS&requestId='||p_request_id);
1863
1864 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1865 itemkey => l_wf_itemkey,
1866 aname => 'WF_EMBED_FINAL_RECIPIENT',
1867 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_FIN_REC_STATUS&requestId='||p_request_id);
1868
1869 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1870 itemkey => l_wf_itemkey,
1871 aname => 'INITIATOR_ERROR_STATUS',
1872 avalue => 'JSP:/OA_HTML/OA.jsp?OAFunc=PSP_ER_WF_ERROR_STATUS&requestId='||p_request_id);
1873
1874 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1875 itemkey => l_wf_itemkey,
1876 aname => 'START_DATE',
1877 avalue => l_start_date);
1878
1879 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1880 itemkey => l_wf_itemkey,
1881 aname => 'END_DATE',
1882 avalue => l_end_date);
1883
1884 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1885 itemkey => l_wf_itemkey,
1886 aname => 'ITEM_KEY',
1887 avalue => l_wf_itemkey);
1888
1889 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1890 itemKey => l_wf_itemkey,
1891 aname => 'RECEIVER_FLAG',
1892 avalue => 'IR');
1893
1894 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1895 itemKey => l_wf_itemkey,
1896 aname => 'TEMPLATE_NAME',
1897 avalue => l_template_name);
1898
1899 IF (l_preview_flag = 'Y') THEN
1900 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1901 itemkey => l_wf_itemkey,
1902 aname => '#ATTACHMENTS',
1903 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || l_wf_itemkey || '&pk2name=RECEIVER_FLAG&pk2value=' || 'IR');
1904 --Bug 7135471
1905 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1906 itemKey => l_wf_itemkey,
1907 aname => 'PDF_ATTACHMENT',
1908 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || l_wf_itemkey);
1909
1910 ELSE
1911 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
1912 itemkey => l_wf_itemkey,
1913 aname => '#ATTACHMENTS',
1914 avalue => '');
1915
1916 --Bug 7135471
1917 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1918 itemKey => l_wf_itemkey,
1919 aname => 'PDF_ATTACHMENT',
1920 avalue => '');
1921
1922 END IF;
1923
1924 IF (p_request_id <> l_retry_request_id) THEN
1925 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1926 itemkey => l_wf_itemkey,
1927 aname => 'RETRY_REQUEST_ID',
1928 avalue => l_retry_request_id);
1929 END IF;
1930
1931 OPEN emp_matching_selection_cur;
1932 FETCH emp_matching_selection_cur INTO l_emp_matching_selection;
1933 CLOSE emp_matching_selection_cur;
1934
1935 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1936 itemkey => l_wf_itemkey,
1937 aname => 'EMP_MATCHING_SELECTION',
1938 avalue => l_emp_matching_selection);
1939
1940 OPEN get_param_strings;
1941 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;
1942 CLOSE get_param_strings;
1943
1944 fnd_message.set_name('PSP', 'PSP_ER_WF_PROCESS_PARAMETERS');
1945 fnd_message.set_token('TEMPLATE_NAME', l_arg1);
1946 fnd_message.set_token('START_DATE', l_arg2);
1947 fnd_message.set_token('END_DATE', l_arg3);
1948 -- fnd_message.set_token('REPORT_LAYOUT', l_arg4);
1949 fnd_message.set_token('FIRST_SORT_BY', l_arg5);
1950 fnd_message.set_token('FIRST_ORDER_BY', l_arg6);
1951 fnd_message.set_token('SECOND_SORT_BY', l_arg7);
1952 fnd_message.set_token('SECOND_ORDER_BY', l_arg8);
1953 fnd_message.set_token('THIRD_SORT_BY', l_arg9);
1954 fnd_message.set_token('THIRD_ORDER_BY', l_arg10);
1955 fnd_message.set_token('FOURTH_SORT_BY', l_arg11);
1956 fnd_message.set_token('FOURTH_ORDER_BY', l_arg12);
1957
1958 l_param_string := fnd_message.get;
1959
1960 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
1961 itemKey => l_wf_itemkey,
1962 aname => 'CONC_PARAM_STRING',
1963 avalue => l_param_string);
1964
1965 wf_engine.SetItemAttrNumber(itemtype => 'PSPERAVL',
1966 itemkey => l_wf_itemkey,
1967 aname => 'PAYROLL_ACTION_ID',
1968 avalue => l_payroll_action_id);
1969
1970 wf_engine.StartProcess(itemtype => 'PSPERAVL',
1971 itemkey => l_wf_itemkey);
1972 end;
1973
1974 procedure initiator_response(itemtype in varchar2,
1975 itemkey in varchar2,
1976 actid in number,
1977 funcmode in varchar2,
1978 result out nocopy varchar2) is
1979 l_request_id integer;
1980 cursor get_init_response is
1981 select initiator_accept_flag
1982 from psp_report_templates_h
1983 where request_id = l_request_id;
1984 l_response varchar2(10);
1985 begin
1986 if funcmode = 'RUN' then
1987 l_request_id :=
1988 wf_engine.GetItemAttrText(itemtype => itemtype,
1989 itemkey => itemkey,
1990 aname => 'REQUEST_ID');
1991 open get_init_response;
1992 fetch get_init_response into l_response;
1993 close get_init_response;
1994 if l_response = 'Y' then
1995 result := 'COMPLETE:APPROVED';
1996 else
1997 result := 'COMPLETE:REJECTED';
1998 end if;
1999 end if;
2000 exception
2001 when others then
2002 result := 'ERROR';
2003 wf_core.context('PSP_EFFORT_REPORTS', 'INITIATOR_RESPONSE', itemtype, itemkey, to_char(actid), funcmode);
2004 raise;
2005 end;
2006
2007 procedure create_frp_role(itemtype in varchar2,
2008 itemkey in varchar2,
2009 actid in number,
2010 funcmode in varchar2,
2011 result out nocopy varchar2) is
2012
2013 l_recipnt_role wf_roles.name%type;
2014 l_request_id integer;
2015 l_member_rname wf_roles.name%type;
2016
2017 cursor get_member_role is
2018 select distinct wf.name
2019 from wf_roles wf,
2020 psp_report_template_details_h temp
2021 where temp.request_id = l_request_id
2022 and wf.orig_system = 'PER'
2023 and to_char(wf.orig_system_id) = temp.criteria_value1
2024 and temp.criteria_lookup_type = 'PSP_SELECTION_CRITERIA'
2025 and temp.criteria_lookup_code = 'FRP';
2026 i integer := 0;
2027 l_debug varchar2(2000);
2028 l_frp_role_display varchar2(100);
2029
2030 cursor check_approval_count is
2031 select count(*)
2032 from psp_eff_reports
2033 where status_code = 'A'
2034 and request_id = l_request_id;
2035
2036 l_count integer;
2037 begin
2038 l_recipnt_role := 'PSP_FINAL_RECIPIENT_'||itemkey;
2039 l_frp_role_display := 'Effort Report Final Recipients';
2040 l_request_id :=
2041 wf_engine.GetItemAttrText(itemtype => itemtype,
2042 itemkey => itemkey,
2043 aname => 'REQUEST_ID');
2044
2045 wf_directory.createAdhocRole(l_recipnt_role,
2046 l_frp_role_display);
2047
2048 open check_approval_count;
2049 fetch check_approval_count into l_count;
2050 close check_approval_count;
2051
2052 if l_count > 0 then
2053 open get_member_role;
2054 loop
2055 fetch get_member_role into l_member_rname;
2056 if get_member_role%notfound then
2057 close get_member_role;
2058 exit;
2059 end if;
2060 i := i + 1;
2061 wf_directory.AddUsersToAdHocRole
2062 (role_name => l_recipnt_role,
2063 role_users => l_member_rname);
2064 end loop;
2065
2066 if i > 0 then
2067
2068 result := 'COMPLETE:Y';
2069
2070 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
2071 itemkey => itemkey,
2072 aname => 'FINAL_RECIPIENT',
2073 avalue => l_recipnt_role);
2074 update psp_report_templates_h
2075 set final_recip_notified_flag = 'Y'
2076 where request_id = l_request_id;
2077 else
2078 result := 'COMPLETE:N';
2079 end if;
2080 else
2081 result := 'COMPLETE:N';
2082 end if;
2083
2084 exception
2085 when others then
2086 ---l_debug := sqlerrm;
2087 ---hr_utility.trace('er_workflow --> 120'||l_debug);
2088 result := 'ERROR';
2089 wf_core.context('PSP_EFFORT_REPORTS', 'CREATE_FRP_ROLE', itemtype, itemkey, to_char(actid), funcmode);
2090 end;
2091
2092 procedure get_pdf_for_apprvr(itemtype in varchar2,
2093 itemkey in varchar2,
2094 actid in number,
2095 funcmode in varchar2,
2096 result out nocopy varchar2) is
2097 begin
2098 -- to consutruct the attachment attribute for the notification.
2099 --- link the PDF using the attachment attribute
2100 --dbms_output.put_line(' attach the pdf here for the notification');
2101 /*
2102
2103 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2104 itemKey => itemkey,
2105 aname => 'PDF_ATTACHMENT',
2106 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || itemtype || ':' || itemkey);
2107 */
2108 null;
2109
2110 end;
2111
2112 procedure gen_modified_pdf(itemtype in varchar2,
2113 itemkey in varchar2,
2114 actid in number,
2115 funcmode in varchar2,
2116 result out nocopy varchar2) is
2117 begin
2118 fnd_wf_standard.executeconcprogram(itemtype ,
2119 itemkey ,
2120 actid ,
2121 funcmode ,
2122 result );
2123
2124 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2125 itemKey => itemkey,
2126 aname => 'PDF_ATTACHMENT',
2127 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || itemtype || ':' || itemkey);
2128
2129 end;
2130
2131 procedure update_receiver(itemtype in varchar2,
2132 itemkey in varchar2,
2133 actid in number,
2134 funcmode in varchar2,
2135 result out nocopy varchar2) is
2136
2137 -- Bug 7135471 starts
2138 l_request_id integer;
2139
2140 cursor get_approval_type is
2141 select approval_type
2142 from psp_report_templates_h
2143 where request_id = l_request_id;
2144
2145 l_approval_type varchar2(100);
2146
2147 l_resp_appl_id number; -- 10185794
2148 l_user number;
2149 l_resp_id number;
2150 l_set_options_success boolean;
2151
2152 begin
2153
2154 /*Changes for the bug 10185794 */
2155 select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
2156 select apps.fnd_global.resp_id into l_resp_id from dual;
2157 select apps.fnd_global.user_id into l_user from dual;
2158
2159 if l_resp_id = -1
2160 then
2161 l_set_options_success := fnd_request.set_options(datagroup=> 'Standard');
2162 end if;
2163 /*End of changes for the bug 10185794 */
2164
2165 l_request_id :=
2166 wf_engine.GetItemAttrText(itemtype => itemtype,
2167 itemkey => itemkey,
2168 aname => 'REQUEST_ID');
2169
2170 open get_approval_type;
2171 fetch get_approval_type into l_approval_type;
2172 close get_approval_type;
2173
2174 IF l_approval_type = 'PRE' THEN
2175
2176 --this code is to delete the pdf that has already been created with the DRAFT watermark
2177 --for PRE APPROVED report types
2178 delete from fnd_lobs
2179 where file_id in(select media_id from fnd_documents_vl
2180 where document_id in(select document_id from fnd_attached_documents
2181 where pk1_value = itemkey));
2182
2183
2184
2185 delete from fnd_attached_documents
2186 where pk1_value = itemkey;
2187
2188 END IF;
2189 -- Bug 7135471 End
2190
2191
2192
2193 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2194 itemKey => itemkey,
2195 aname => 'RECEIVER_FLAG',
2196 avalue => 'FR');
2197
2198 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
2199 itemkey => itemkey,
2200 aname => '#ATTACHMENTS',
2201 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || itemkey || '&pk2name=RECEIVER_FLAG&pk2value=' || 'FR');
2202
2203 --Bug 7135471
2204 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2205 itemKey => itemkey,
2206 aname => 'PDF_ATTACHMENT',
2207 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || itemkey);
2208
2209
2210 end update_receiver;
2211
2212 procedure update_approver(itemtype in varchar2,
2213 itemkey in varchar2,
2214 actid in number,
2215 funcmode in varchar2,
2216 result out nocopy varchar2) is
2217
2218
2219 l_resp_appl_id number; -- 10185794
2220 l_user number;
2221 l_resp_id number;
2222 l_set_options_success boolean;
2223
2224 begin
2225
2226 /*Changes for the bug 10185794 */
2227 select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
2228 select apps.fnd_global.resp_id into l_resp_id from dual;
2229 select apps.fnd_global.user_id into l_user from dual;
2230
2231 if l_resp_id = -1
2232 then
2233 l_set_options_success := fnd_request.set_options(datagroup=> 'Standard');
2234 end if;
2235 /*End of changes for the bug 10185794 */
2236
2237 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2238 itemKey => itemkey,
2239 aname => 'RECEIVER_FLAG',
2240 avalue => 'AR');
2241
2242 wf_engine.SetItemAttrText(itemtype => 'PSPERAVL',
2243 itemkey => itemkey,
2244 aname => '#ATTACHMENTS',
2245 avalue => 'FND:entity=ERDETAILS&pk1name=WF_ITEM_KEY&pk1value=' || itemkey || '&pk2name=RECEIVER_FLAG&pk2value=' || 'AR');
2246
2247 --Bug 7135471
2248 wf_engine.SetItemAttrText(itemType => 'PSPERAVL',
2249 itemKey => itemkey,
2250 aname => 'PDF_ATTACHMENT',
2251 avalue => 'PLSQLBLOB:psp_xmlgen.attach_pdf/' || 'PSPERAVL' || ':' || itemkey);
2252
2253
2254 end update_approver;
2255
2256 procedure pre_approved(itemtype in varchar2,
2257 itemkey in varchar2,
2258 actid in number,
2259 funcmode in varchar2,
2260 result out nocopy varchar2) is
2261
2262 l_request_id integer;
2263 cursor get_approval_type is
2264 select approval_type
2265 from psp_report_templates_h
2266 where request_id = l_request_id;
2267 l_approval_type varchar2(100);
2268 begin
2269 l_request_id :=
2270 wf_engine.GetItemAttrText(itemtype => itemtype,
2271 itemkey => itemkey,
2272 aname => 'REQUEST_ID');
2273 open get_approval_type;
2274 fetch get_approval_type into l_approval_type;
2275 close get_approval_type;
2276
2277 if l_approval_type = 'PRE' then
2278 result := 'COMPLETE:Y';
2279 else
2280 result := 'COMPLETE:N';
2281 end if;
2282 exception
2283 when others then
2284 result := 'ERROR';
2285 wf_core.context('PSP_EFFORT_REPORTS', 'PRE_APPROVED', itemtype, itemkey, to_char(actid),
2286 funcmode);
2287
2288 end;
2289
2290 PROCEDURE update_initiator_message (itemtype IN varchar2,
2291 itemkey IN varchar2,
2292 actid IN number,
2293 funcmode IN varchar2,
2294 result OUT nocopy varchar2) IS
2295 l_message_name fnd_new_messages.message_name%TYPE;
2296 l_message_text fnd_new_messages.message_text%TYPE;
2297 BEGIN
2298 l_message_name := wf_engine.GetActivityAttrText(itemtype, itemkey, actid, 'INITIATOR_MESSAGE_NAME');
2299 fnd_message.set_name('PSP', l_message_name);
2300 l_message_text := fnd_message.get;
2301 wf_engine.SetItemAttrText(itemtype => itemtype,
2302 itemkey => itemkey,
2303 aname => 'ERROR_MESSAGE',
2304 avalue => l_message_text);
2305
2306 result := 'COMPLETE';
2307
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310 -- Populate stack with error message
2311 result := 'ERROR';
2312 wf_core.context('PSP_EFFORT_REPORTS', 'update_initiator_message', itemtype, itemkey, to_char(actid), funcmode);
2313 RAISE;
2314 END update_initiator_message;
2315
2316 PROCEDURE set_wf_admin(itemtype IN varchar2,
2317 itemkey IN varchar2,
2318 actid IN number,
2319 funcmode IN varchar2,
2320 result OUT nocopy varchar2) IS
2321
2322 l_initiator_rname wf_roles.name%type;
2323
2324 BEGIN
2325 l_initiator_rname :=
2326 wf_engine.GetItemAttrText(itemtype => itemtype,
2327 itemkey => itemkey,
2328 aname => 'INITIATOR');
2329
2330 wf_engine.SetItemAttrText(itemtype => itemtype,
2331 itemkey => itemkey,
2332 aname => 'WF_ADMINISTRATOR',
2333 avalue => l_initiator_rname);
2334
2335 --Bug 7135471
2336 If itemkey is not null then
2337
2338 if(item_attribute_exists(itemtype,itemkey,'PDF_ATTACHMENT')) then
2339 hr_utility.trace('PDF_ATTACHMENT attribute exists');
2340 else
2341 wf_engine.additemattr
2342 (itemtype => itemtype,
2343 itemkey => itemkey,
2344 aname => 'PDF_ATTACHMENT',
2345 text_value => '');
2346 end if;
2347
2348 end if;
2349
2350
2351 result := 'COMPLETE';
2352
2353 ---- call the user hook for wf admin role here.
2354 ---- By default setting it to Initiator.
2355 psp_er_wf_custom.set_custom_wf_admin(itemtype ,
2356 itemkey ,
2357 actid ,
2358 funcmode ,
2359 result);
2360 EXCEPTION
2361 WHEN OTHERS THEN
2362 result := 'ERROR';
2363 wf_core.context('PSP_EFFORT_REPORTS', 'SET_WF_ADMIN', itemtype, itemkey, to_char(actid), funcmode);
2364 raise;
2365 END;
2366
2367 PROCEDURE get_timeout_approver(itemtype IN varchar2,
2368 itemkey IN varchar2,
2369 actid IN number,
2370 funcmode IN varchar2,
2371 result OUT nocopy varchar2) IS
2372 BEGIN
2373 result := 'COMPLETE';
2374 ---- user hook for time out approver
2375 psp_er_Wf_custom.set_custom_timeout_approver(itemtype ,
2376 itemkey ,
2377 actid ,
2378 funcmode ,
2379 result );
2380
2381 EXCEPTION
2382 WHEN OTHERS THEN
2383 -- Populate stack with error message
2384 result := 'ERROR';
2385 wf_core.context('PSP_EFFORT_REPORTS', 'GET_TIMEOUT_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
2386 RAISE;
2387 END;
2388
2389 PROCEDURE preview_er (itemtype IN varchar2,
2390 itemkey IN varchar2,
2391 actid IN number,
2392 funcmode IN varchar2,
2393 result OUT nocopy varchar2) IS
2394 l_preview_flag psp_report_templates_h.preview_effort_report_flag%TYPE;
2395 l_request_id NUMBER(15, 0);
2396 CURSOR preview_er_cur IS
2397 SELECT preview_effort_report_flag
2398 FROM psp_report_templates_h prth
2399 WHERE prth.request_id = l_request_id;
2400
2401 l_resp_appl_id number; -- 10185794
2402 l_user number;
2403 l_resp_id number;
2404 l_set_options_success boolean;
2405
2406 begin
2407
2408 /*Changes for the bug 10185794 */
2409 select apps.fnd_global.resp_appl_id into l_resp_appl_id from dual;
2410 select apps.fnd_global.resp_id into l_resp_id from dual;
2411 select apps.fnd_global.user_id into l_user from dual;
2412
2413 if l_resp_id = -1
2414 then
2415 l_set_options_success := fnd_request.set_options(datagroup=> 'Standard');
2416 end if;
2417 /*End of changes for the bug 10185794 */
2418
2419 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
2420 itemkey => itemkey,
2421 aname => 'REQUEST_ID');
2422
2423 OPEN preview_er_cur;
2424 FETCH preview_er_cur INTO l_preview_flag;
2425 CLOSE preview_er_cur;
2426
2427 result := 'COMPLETE:' || l_preview_flag;
2428 EXCEPTION
2429 WHEN OTHERS THEN
2430 -- Populate stack with error message
2431 result := 'ERROR';
2432 wf_core.context('PSP_EFFORT_REPORTS', 'PREVIEW_ER', itemtype, itemkey, TO_CHAR(actid), funcmode);
2433 RAISE;
2434 END preview_er;
2435
2436 -- Added this function for Bug 7135471
2437 FUNCTION item_attribute_exists
2438 (p_item_type in wf_items.item_type%type,
2439 p_item_key in wf_item_activity_statuses.item_key%type,
2440 p_name in wf_item_attribute_values.name%type)
2441 return boolean is
2442
2443 l_dummy varchar2(1);
2444
2445 BEGIN
2446
2447 select 'Y'
2448 into l_dummy
2449 from wf_item_attribute_values
2450 where item_type = p_item_type
2451 and item_key = p_item_key
2452 and name = p_name;
2453
2454 return true;
2455
2456 Exception
2457 When others then
2458 return false;
2459
2460 END item_attribute_exists;
2461
2462 end;