[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_RSGN_EMP
Source
1 PACKAGE BODY BEN_CWB_RSGN_EMP as
2 /* $Header: bencwbre.pkb 120.5.12020000.2 2012/07/03 12:24:48 amnaraya ship $ */
3 /* ===========================================================================+
4 * Name
5 * Compensation workbench reassign employee
6 * Purpose
7 *
8 *
9 *
10 *
11 *
12 * Version Date Author Comment
13 * -------+-----------+---------+----------------------------------------------
14 * 115.0 01-July-2002 aupadhya created
15 * 115.1 07-Aug -2002 aupadhya Modified, Calling ben_manage_life_events.rebuild_heirarchy
16 * procedure for each employee instead of proposed ws manager.
17 * 115.2 08-Aug-2002 aupadhya Removed to_char used with string values.
18 * 115.3 08-Aug-2002 aupadhya Changed for workflow attributes name change.
19 * 115.4 27-Aug-2002 aupadhya Removed unused method and Fixed Bug#2526333.
20 * 115.5 12-Sep-2002 aupadhya Added code to provide value to a work-flow attribute FROM_ROLE
21 * 115.6 28-Oct-2002 aupadhya Added code to send an notification when exception occurs in
22 * set_approval method.
23 * 115.7 30-Oct-2002 aupadhya replaced sqlerrm with fnd_message.get in set_approval's Exception
24 * block.
25 * 115.8 16 Dec 2002 hnarayan Added NOCOPY hint
26 * 115.9 24-Dec-2002 aupadhya Modified for CWB Itemization.
27 * 115.10 20-Feb-2003 aupadhya Modified For Bug#2786444.
28 * 115.11 24-Mar-2003 aupadhya Modified For Bug#2786444, commented
29 * hr_utility.set_location for fnd_message.get string, because
30 * it is erroring out for korean;
31 * ==========================================================================+
32 * 115.12 01-Mar-2004 aupadhya Global Budgeting Changes.
33 * 115.13 15-Mar-2004 aupadhya Added summary refresh call.
34 * 115.14 08-Jun-2004 aupadhya Modifed logic for approver_hrchy cursor.
35 * 115.15 20-Sep-2004 aupadhya Global Budgeting 11.5.10
36 * 115.16 22-Feb-2005 aupadhya Audit Changes.
37 * 115.16 14-Jul-2006 aupadhya Added support for customer defined workflow
38 * process using profile value.
39 * 115.17 15-Jul-2006 aupadhya Custom workflow code logic change.
40 * 115.21 04-Sep-2006 steotia 4722976: Chgd date fmt from -MON-
41 * 115.22 10-Sep-2008 cakunuru 7159487: Changed recCount in start_workflow
42 * 115.23 07-May-2011 sgnanama 11062444 bug fix
43 * 115.24 06-MAR-2012 kgowripe Changed procedure store_emp_details
44 * ==========================================================================+
45 */
46
47 g_package varchar2(80) := 'ben_cwb_rsgn_emp';
48
49 FUNCTION get_for_period(p_group_per_in_ler_id IN NUMBER)
50 RETURN VARCHAR2
51 IS
52 CURSOR c11
53 IS
54 SELECT nvl(dsgn.wthn_yr_start_dt,dsgn.yr_perd_start_dt)||' - '||
55 nvl(dsgn.wthn_yr_end_dt,dsgn.yr_perd_end_dt) forPeriod
56 FROM ben_per_in_ler pil,
57 ben_cwb_pl_dsgn dsgn
58 WHERE pil.per_in_ler_id = p_group_per_in_ler_id
59 AND pil.group_pl_id = dsgn.group_pl_id
60 AND pil.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
61 AND dsgn.oipl_id = -1
62 AND dsgn.group_pl_id = dsgn.pl_id
63 AND dsgn.group_oipl_id = dsgn.oipl_id;
64 l_info c11%ROWTYPE;
65 BEGIN
66 OPEN c11;
67 FETCH c11 INTO l_info;
68 CLOSE c11;
69
70 RETURN l_info.forPeriod;
71 END;
72
73
74 procedure check_approver (itemtype in varchar2
75 , itemkey in varchar2
76 , actid in number
77 , funcmode in varchar2
78 , result out nocopy varchar2)
79 IS
80 l_approver_user varchar2(240);
81 l_approver_name varchar2(240);
82 l_approver_last_name varchar2(240);
83 c_next_approver_out ame_util.approverRecord;
84 c_all_approvers ame_util.approversTable;
85 l_recCount number;
86 l_flag number;
87 l_requestorId number;
88 l_package varchar2(80) := g_package||'.check_approver';
89 l_error varchar2(5000);
90 --l_itemkey varchar2(40):=itemkey;
91 cursor get_curr_approver_name(c_approver_id in number) is
92 select users.user_name , ppf.first_name , ppf.last_name
93 from fnd_user users
94 ,per_All_people_f ppf
95 where users.employee_id=ppf.person_id
96 and users.employee_id=c_approver_id
97 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
98
99 BEGIN
100 /* Added modify logic from here */
101
102 --hr_utility.trace_on (null, 'ORACLE');
103
104 hr_utility.set_location('Entering '||l_package ,30);
105
106 l_requestorId:=wf_engine.GetItemAttrNumber(itemtype => itemtype,
107 itemkey => itemkey,
108 aname => 'REQUESTOR_ID');
109 hr_utility.set_location('in Find approver : 2',20);
110
111 l_flag :=wf_engine.GetItemAttrNumber(itemtype => itemtype,
112 itemkey => itemkey,
113 aname => 'REQUESTOR_FLAG');
114
115
116 hr_utility.set_location('flag '||l_flag ,90);
117
118 hr_utility.set_location('From workflow : ' || wf_engine.GetItemAttrNumber(itemtype => itemtype,
119 itemkey => itemkey,
120 aname => 'REQUESTOR_FLAG'),90);
121 -- if l_flag = 1 then
122
123 if (wf_engine.GetItemAttrNumber(itemtype => itemtype,
124 itemkey => itemkey,
125 aname => 'REQUESTOR_FLAG')) = 1 then
126 hr_utility.set_location('true condition ' ,100);
127 result:='COMPLETE:' ||'NOT FOUND';
128 else
129
130 ame_api.getNextApprover(applicationIdIn => 805,
131 transactionIdIn => itemkey,
132 transactionTypeIn => 'PROPEMPRSGN',
133 nextApproverOut => c_next_approver_out);
134 hr_utility.set_location('false condition ' ,100);
135
136 if(c_next_approver_out.person_id is not null) then
137 wf_engine.SetItemAttrNumber( itemtype => itemtype
138 , itemkey => itemkey
139 , aname => 'RECEIVER_USER_ID'
140 , avalue => c_next_approver_out.person_id);
141 open get_curr_approver_name(c_next_approver_out.person_id);
142 fetch get_curr_approver_name into l_approver_user,l_approver_name , l_approver_last_name;
143 If get_curr_approver_name%found Then
144 hr_utility.set_location('appr is'||l_approver_user || ' , ' || c_next_approver_out.person_id || ' , ' ||l_approver_name,100);
145
146 Else
147 hr_utility.set_location ('No Data Found'||l_approver_user || ' , ' || c_next_approver_out.person_id || ' , ' ||l_approver_name,100);
148
149 End If;
150 wf_engine.SetItemAttrText( itemtype => itemtype
151 , itemkey => itemkey
152 , aname => 'RECEIVER_USER_NAME'
153 , avalue => l_approver_user);
154 hr_utility.set_location('Approver name is: '|| l_approver_name,200);
155
156
157 update ben_transaction set attribute12= l_approver_name , attribute13= l_approver_last_name
158 where attribute2= itemkey
159 and attribute1= 'EMP'
160 and transaction_type='CWBEMPRSGN';
161
162
163
164 result:='COMPLETE:' ||'FOUND';
165
166 close get_curr_approver_name;
167 else
168 result:='COMPLETE:' ||'NOT FOUND';
169
170 end if;
171 end if;
172
173
174
175 hr_utility.set_location('Leaving '||l_package ,30);
176 EXCEPTION
177 when others then
178 l_error:=sqlerrm;
179 hr_utility.set_location ('exception is'||l_error , 300);
180 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'check_approver',l_error);
181 raise;
182 END check_approver;
183
184
185
186
187 procedure store_approval_details(itemtype in varchar2
188 , itemkey in varchar2
189 , actid in number
190 , funcmode in varchar2
191 , result out nocopy varchar2)
192 is
193 l_approver_name varchar2(240);
194 l_approver_user varchar2(240);
195 l_approver_id number;
196 l_package varchar2(80) := g_package||'.store_approval_details';
197 l_error varchar2(5000);
198 begin
199 --hr_utility.trace_on (null, 'ORACLE');
200
201 hr_utility.set_location('Entering '||l_package ,30);
202
203 l_approver_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
204 itemkey => itemkey,
205 aname => 'RECEIVER_USER_ID');
206 l_approver_user := wf_engine.GetItemAttrText(itemtype => itemtype,
207 itemkey => itemkey,
208 aname => 'RECEIVER_USER_NAME');
209
210 hr_utility.set_location('store approver details : Reveiver user id : '|| l_approver_id,10);
211
212 wf_engine.SetItemAttrText(itemtype => itemtype
213 , itemkey => itemkey
214 , aname => 'FROM_ROLE'
215 , avalue =>
216 wf_engine.GetItemAttrText(itemtype => itemtype,
217 itemkey => itemkey,
218 aname => 'RECEIVER_USER_NAME')
219 );
220
221 -- Need to catch notification id , it will used in approved/rejected
222 -- notifications to show action history....
223 -- Begin
224
225 UPDATE ben_transaction
226 SET attribute21 =
227 ( select notification_id
228 from wf_item_activity_statuses
229 where
230 item_key = itemkey
231 and item_type = itemtype
232 and assigned_user= l_approver_user
233 )
234 , attribute22 = l_approver_user
235 WHERE attribute3=itemkey
236 and transaction_type='CWBEMPRSGN'
237 and attribute1='APPR'
238 and attribute21 is null;
239
240 -- End..
241
242
243 ame_api.updateApprovalStatus2(applicationIdIn =>805,
244 transactionIdIn => itemkey,
245 approvalStatusIn => ame_util.approvedStatus,
246 approverPersonIdIn=> l_approver_id,
247 transactionTypeIn=> 'PROPEMPRSGN' );
248
249 hr_utility.set_location('Leaving '||l_package ,30);
250 result:='COMPLETE:';
251
252 EXCEPTION
253 when others then
254 l_error:=sqlerrm;
255 hr_utility.set_location ('exception is'||l_error , 300);
256 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'store_emp_details',l_error);
257 raise;
258
259
260 end store_approval_details;
261
262
263
264 procedure curr_ws_mgr_check(itemtype in varchar2
265 , itemkey in varchar2
266 , actid in number
267 , funcmode in varchar2
268 , result out nocopy varchar2)
269 is
270 l_curr_manager_name varchar2(240);
271 l_requestor varchar2(240);
272 l_package varchar2(80) := g_package||'.curr_ws_mgr_check';
273 l_error varchar2(5000);
274 begin
275 hr_utility.set_location('Entering '||l_package ,30);
276 l_curr_manager_name:= wf_engine.GetItemAttrText(itemtype => itemtype,
277 itemkey => itemkey,
278 aname => 'CURRENT_WS_MANAGER');
279 l_requestor:= wf_engine.GetItemAttrText(itemtype => itemtype,
280 itemkey => itemkey,
281 aname => 'REQUESTOR');
282 if ( l_curr_manager_name = l_requestor) then
283 result:= 'COMPLETE:' ||'YES';
284 else
285 result:= 'COMPLETE:' ||'NO';
286 end if;
287 hr_utility.set_location('Leaving '||l_package ,30);
288 EXCEPTION
289 when others then
290 l_error:=sqlerrm;
291 hr_utility.set_location ('exception is'||l_error , 300);
292 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'curr_ws_mgr_check',l_error);
293 raise;
294
295 end curr_ws_mgr_check;
296
297
298
299 procedure prop_ws_mgr_check(itemtype in varchar2
300 , itemkey in varchar2
301 , actid in number
302 , funcmode in varchar2
303 , result out nocopy varchar2)
304 is
305 l_prop_manager_name varchar2(240);
306 l_requestor varchar2(240);
307 l_package varchar2(80) := g_package||'.prop_ws_mgr_check';
308 l_error varchar2(5000);
309 begin
310 hr_utility.set_location('Entering '||l_package ,30);
311 l_prop_manager_name:= wf_engine.GetItemAttrText(itemtype => itemtype,
312 itemkey => itemkey,
313 aname => 'PROPOSED_WS_MANAGER');
314 l_requestor:= wf_engine.GetItemAttrText(itemtype => itemtype,
315 itemkey => itemkey,
316 aname => 'REQUESTOR');
317 if ( l_prop_manager_name = l_requestor) then
318 result:= 'COMPLETE:' ||'YES';
319 else
320 result:= 'COMPLETE:' ||'NO';
321 end if;
322 hr_utility.set_location('Leaving '||l_package ,30);
323 EXCEPTION
324 when others then
325 l_error:=sqlerrm;
326 hr_utility.set_location ('exception is'||l_error , 300);
327 --Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'prop_ws_mgr_check',l_error);
328 raise;
329 end prop_ws_mgr_check;
330
331
332
333 procedure which_message(itemtype in varchar2
334 , itemkey in varchar2
335 , actid in number
336 , funcmode in varchar2
337 , result out nocopy varchar2)
338 is
339 l_message_type varchar2(40);
340 l_error_message varchar2(2000);
341 l_package varchar2(80) := g_package||'.which_message';
342 l_error varchar2(5000);
343 begin
344 hr_utility.set_location('Entering '||l_package ,30);
345 l_message_type:= wf_engine.GetItemAttrText(itemtype => itemtype,
346 itemkey => itemkey,
347 aname => 'MESSAGE_TYPE');
348 if( l_message_type='COMPLETED') then
349 l_error_message := wf_engine.GetItemAttrText(itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'ERROR_MESSAGE');
352 UPDATE ben_transaction
353 SET attribute40 = l_error_message
354 WHERE attribute2=itemkey
355 and transaction_type='CWBEMPRSGN'
356 and attribute1='EMP';
357
358 end if;
359
360 result:='COMPLETE:'||l_message_type;
361 hr_utility.set_location('Leaving '||l_package ,30);
362
363 EXCEPTION
364 when others then
365 l_error:=sqlerrm;
366 hr_utility.set_location ('exception is'||l_error , 300);
367 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'store_emp_details',l_error);
368 raise;
369 end which_message;
370
371
372
373
374 procedure set_rejection(itemtype in varchar2
375 , itemkey in varchar2
376 , actid in number
377 , funcmode in varchar2
378 , result out nocopy varchar2)
379 is
380
381 l_requestor varchar2(40);
382 l_approver_user varchar2(240);
383 l_package varchar2(80) := g_package||'.set_rejection';
384 l_error varchar2(5000);
385 begin
386 hr_utility.set_location('Entering '||l_package ,30);
387
388 l_approver_user := wf_engine.GetItemAttrText(itemtype => itemtype,
389 itemkey => itemkey,
390 aname => 'RECEIVER_USER_NAME');
391
392 wf_engine.SetItemAttrText(itemtype => itemtype
393 , itemkey => itemkey
394 , aname => 'FROM_ROLE'
395 , avalue =>
396 wf_engine.GetItemAttrText(itemtype => itemtype,
397 itemkey => itemkey,
398 aname => 'RECEIVER_USER_NAME')
399 );
400
401
402 -- Need to catch notification id , it will used in approved/rejected
403 -- notifications to show action history....
404 -- Begin
405
406 UPDATE ben_transaction
407 SET attribute21 =
408 ( select notification_id
409 from wf_item_activity_statuses
410 where
411 item_key = itemkey
412 and item_type = itemtype
413 and assigned_user= l_approver_user
414 )
415 , attribute22 = l_approver_user
416 WHERE attribute3=itemkey
417 and transaction_type='CWBEMPRSGN'
418 and attribute1='APPR'
419 and attribute21 is null;
420
421 -- End..
422
423 wf_engine.SetItemAttrText( itemtype => itemtype
424 , itemkey => itemkey
425 , aname => 'MESSAGE_TYPE'
426 , avalue => 'REJECTED');
427 result:='COMPLETE:';
428 hr_utility.set_location('Leaving '||l_package ,30);
429 EXCEPTION
430 when others then
431 l_error:=sqlerrm;
432
433 hr_utility.set_location ('exception is'||l_error , 300);
434
435 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'set_rejection',l_error);
436 raise;
437 end set_rejection;
438
439
440
441
442
443 procedure set_approval(itemtype in varchar2
444 , itemkey in varchar2
445 , actid in number
446 , funcmode in varchar2
447 , result out nocopy varchar2)
448 is
449 l_requestor varchar2(240);
450 l_obj_ver_num number;
451 l_ws_mgr_id_old number;
452 l_ws_mgr_id number;
453 l_ws_mgr_per_in_ler_id number;
454 l_prop_mgr_per_in_ler_id number;
455 l_per_in_ler_id number;
456 l_pl_id number;
457 l_ovr_id number;
458 l_error fnd_new_messages.message_text%type;
459 l_approval_cd varchar2(10);
460 l_flag number:=0;
461 l_curr_ws_mgr varchar2(240);
462 l_prop_ws_mgr varchar2(240);
463 l_message fnd_new_messages.message_text%type;
464 l_package varchar2(80) := g_package||'.set_approval';
465
466 -- For API call begin
467 l_procd_dt date;
468 l_strtd_dt date;
469 l_voidd_dt date;
470 -- For API call end
471
472 cursor getEmpToReassign is
473 select attribute3 , attribute10 , attribute8 ,attribute14 , attribute16 , attribute5 || ' ' || attribute18 , attribute7 || ' ' || attribute19 , attribute21 , attribute6
474 from ben_transaction
475 where attribute2=itemkey
476 and attribute1='EMP'
477 and transaction_type = 'CWBEMPRSGN';
478
479 cursor getObjVerNum(c_per_in_ler_id in number) is
480 select object_version_number
481 from ben_per_in_ler
482 where per_in_ler_id = c_per_in_ler_id;
483
484 cursor get_approval_cd(c_per_in_ler_id in number, c_pl_id in number) is
485 select approval_cd
486 from ben_cwb_person_groups
487 where group_per_in_ler_id =c_per_in_ler_id
488 and group_pl_id=c_pl_id
489 and group_oipl_id=-1;
490
491 begin
492
493 --hr_utility.trace_on (null, 'ORACLE');
494
495 hr_utility.set_location('Entering '||l_package ,30);
496
497 wf_engine.SetItemAttrText( itemtype => itemtype
498 , itemkey => itemkey
499 , aname => 'MESSAGE_TYPE'
500 , avalue => 'APPROVED');
501 open getEmpToReassign;
502
503 fetch getEmpToReassign into l_per_in_ler_id,l_ovr_id,l_ws_mgr_id,l_prop_mgr_per_in_ler_id ,l_ws_mgr_per_in_ler_id,l_curr_ws_mgr,l_prop_ws_mgr,l_pl_id , l_ws_mgr_id_old;
504
505 close getEmpToReassign;
506
507 hr_utility.set_location ('Current manager choice id '||l_ws_mgr_per_in_ler_id,200);
508 hr_utility.set_location ('Proposed manager choice id '||l_prop_mgr_per_in_ler_id,200);
509
510 open get_approval_cd(l_ws_mgr_per_in_ler_id,l_pl_id);
511 fetch get_approval_cd into l_approval_cd;
512 close get_approval_cd;
513
514
515 hr_utility.set_location ('current manager status '||l_approval_cd,200);
516
517 if ( ((l_approval_cd ='AP')or(l_approval_cd='PR'))) then
518 l_flag:=1;
519 hr_utility.set_location('Current manager processed ',200);
520
521 fnd_message.set_name('BEN','BEN_93118_CWB_RSGN_COMPLETED');
522 fnd_message.set_token('PERSON_NAME',l_curr_ws_mgr);
523 l_message:=fnd_message.get;
524
525 end if;
526
527
528
529 open get_approval_cd(l_prop_mgr_per_in_ler_id,l_pl_id);
530 fetch get_approval_cd into l_approval_cd;
531 close get_approval_cd;
532
533
534 hr_utility.set_location('Proposed manager status '||l_approval_cd,200);
535
536 if (l_flag = 0 ) then
537
538 if ( ((l_approval_cd ='AP')or(l_approval_cd ='PR'))) then
539 l_flag:=1;
540 hr_utility.set_location ('Proposed manager processed ',200);
541 fnd_message.set_name('BEN','BEN_93118_CWB_RSGN_COMPLETED');
542 fnd_message.set_token('PERSON_NAME',l_prop_ws_mgr);
543 l_message:=fnd_message.get;
544 end if;
545 end if;
546
547 if l_flag=0 then
548
549 for i in getEmpToReassign loop
550
551 hr_utility.set_location('set Approval',200);
552
553
554
555 l_per_in_ler_id := i.attribute3;
556 l_ovr_id := i.attribute10;
557 l_ws_mgr_id := i.attribute8;
558 l_ws_mgr_per_in_ler_id := i.attribute16;
559
560 open getObjVerNum(l_per_in_ler_id);
561 fetch getObjVerNum into l_obj_ver_num;
562 close getObjVerNum;
563
564
565
566 ben_Person_Life_Event_api.update_Person_Life_Event
567 (
568 p_validate => false
569 ,p_per_in_ler_id => l_per_in_ler_id
570 ,p_mgr_ovrid_dt => sysdate
571 ,p_mgr_ovrid_person_id => l_ovr_id
572 ,p_ws_mgr_id => l_ws_mgr_id
573 ,p_object_version_number => l_obj_ver_num
574 ,p_effective_date => sysdate
575 ,p_group_pl_id => l_pl_id
576 ,p_procd_dt => l_procd_dt
577 ,p_strtd_dt => l_strtd_dt
578 ,p_voidd_dt => l_voidd_dt
579 );
580
581 BEN_MANAGE_CWB_LIFE_EVENTS.rebuild_heirarchy(p_group_per_in_ler_id => l_per_in_ler_id);
582
583 ben_cwb_summary_pkg.delete_pl_sql_tab;
584
585 hr_utility.set_location ('Current manager for summary '||l_ws_mgr_per_in_ler_id,200);
586 hr_utility.set_location ('Proposed manager for summary '||l_prop_mgr_per_in_ler_id,200);
587
588
589 BEN_CWB_SUMMARY_PKG.update_summary_on_reassignment
590 (p_old_mgr_per_in_ler_id => l_ws_mgr_per_in_ler_id
591 ,p_new_mgr_per_in_ler_id => l_prop_mgr_per_in_ler_id
592 ,p_emp_per_in_ler_id => l_per_in_ler_id
593 );
594
595 ben_cwb_summary_pkg.save_pl_sql_tab;
596
597
598 ben_cwb_audit_api.update_per_record
599 (p_per_in_ler_id => l_per_in_ler_id
600 ,p_old_val => l_ws_mgr_id_old
601 ,p_audit_type_cd => 'MG'
602 );
603
604
605 end loop;
606 --ben_manage_life_events.rebuild_heirarchy(p_elig_per_elctbl_chc_id => l_ws_mgr_chc_id);
607
608 wf_engine.SetItemAttrText( itemtype => itemtype
609 , itemkey => itemkey
610 , aname => 'MESSAGE_TYPE'
611 , avalue => 'APPROVED');
612 else
613 wf_engine.SetItemAttrText( itemtype => itemtype
614 , itemkey => itemkey
615 , aname => 'MESSAGE_TYPE'
616 , avalue => 'COMPLETED');
617
618 wf_engine.SetItemAttrText( itemtype => itemtype
619 , itemkey => itemkey
620 , aname => 'ERROR_MESSAGE'
621 , avalue => l_message);
622
623
624 end if;
625 result:='COMPLETE:';
626 hr_utility.set_location('Leaving '||l_package ,30);
627 EXCEPTION
628 when others then
629 hr_utility.set_location('Leaving '||sqlerrm ,30);
630 l_error:=fnd_message.get;
631 wf_engine.SetItemAttrText( itemtype => itemtype
632 , itemkey => itemkey
633 , aname => 'MESSAGE_TYPE'
634 , avalue => 'COMPLETED');
635 wf_engine.SetItemAttrText( itemtype => itemtype
636 , itemkey => itemkey
637 , aname => 'ERROR_MESSAGE'
638 , avalue => l_error);
639
640 end set_approval;
641
642
643
644
645
646
647
648 /* To start workflow process
649 When approval is required before reassignment
650 */
651
652
653
654 procedure start_workflow(p_requestor_id in number,
655 p_curr_ws_manager_id number,
656 p_prop_ws_manager_id number,
657 p_plan_name varchar2,
658 p_message varchar2,
659 p_transaction_id number,
660 p_request_date varchar2,
661 p_reccount number,
662 p_prop_ws_mgr_per_in_ler_id number,
663 p_plan_id number)
664 is
665 l_itemkey number:=p_transaction_id;
666 l_itemtype varchar2(60) := 'BENCWBFY';
667 l_process_name varchar2(60) := 'RSGNP';
668 l_process_name_c varchar2(60);
669 l_package varchar2(80) := g_package||'.start_workflow';
670
671 cursor approver_name(c_approver_id in number) is
672 select users.user_name , ppf.first_name , ppf.last_name
673 from fnd_user users
674 ,per_all_people_f ppf
675 where
676 users.employee_id=ppf.person_id
677 and employee_id=c_approver_id
678 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
679
680 cursor approver_hrchy(c_per_in_ler_id in number) is
681
682 select pil.person_id , pil.per_in_ler_id
683
684 from ben_cwb_group_hrchy hrchy,
685 ben_per_in_ler pil
686 where hrchy.emp_per_in_ler_id = c_per_in_ler_id
687 --and hrchy.mgr_per_in_ler_id <> hrchy.emp_per_in_ler_id
688 and hrchy.lvl_num <> 0
689 and hrchy.mgr_per_in_ler_id = pil.per_in_ler_id;
690
691
692 cursor get_manager_id_from_pil(c_per_in_ler_id in number) is
693
694 select pil.person_id
695
696 from ben_cwb_group_hrchy hrchy,
697 ben_per_in_ler pil
698
699 where
700 hrchy.emp_per_in_ler_id =c_per_in_ler_id
701 and hrchy.mgr_per_in_ler_id <>hrchy.emp_per_in_ler_id
702 and hrchy.mgr_per_in_ler_id = pil.per_in_ler_id
703 and LVL_NUM =1;
704
705
706 cursor get_top_person_id(c_plan_id in number) is
707 select distinct pil.person_id
708 from ben_cwb_group_hrchy hrchy,
709 ben_per_in_ler pil
710 where
711 pil.group_pl_id = c_plan_id
712 and pil.per_in_ler_id= hrchy.mgr_per_in_ler_id
713 and emp_per_in_ler_id=p_prop_ws_mgr_per_in_ler_id
714 and hrchy.lvl_num = (select max(h1.lvl_num)
715 from ben_cwb_group_hrchy h1
716 where h1.emp_per_in_ler_id = p_prop_ws_mgr_per_in_ler_id
717 );
718
719 l_person_id number;
720 l_requestor_per_in_ler_id number;
721 l_requestor_person_id number;
722 l_requestor_name varchar2(240);
723 l_requestor_last_name varchar2(240);
724 l_requestor_user varchar2(240);
725 l_curr_ws_manager_name varchar2(240);
726 l_curr_ws_manager_last_name varchar2(240);
727 l_curr_ws_manager_user varchar2(240);
728 l_prop_ws_manager_name varchar2(240);
729 l_prop_ws_manager_last_name varchar2(240);
730 l_prop_ws_manager_user varchar2(240);
731 l_top_person_id number;
732 c_next_approver_out ame_util.approverRecord;
733 c_all_approvers ame_util.approversTable;
734 l_recCount number:=0;
735 l_flag number;
736 l_requestor_flag number:=0;
737 l_transaction_id number;
738 l_error varchar2(5000);
739 l_for_period VARCHAR2(30);
740 Begin
741
742 --select BEN_CWB_WF_NTF_S.NEXTVAL into l_itemkey from dual;
743
744 hr_utility.set_location('Entering '||l_package ,30);
745
746 hr_utility.set_location ('Seeded Rsgn Process Name'||l_process_name ,55);
747
748 hr_utility.set_location ('Profile :: '|| fnd_profile.value('BEN_CWB_EMP_RSGN_W_PROCESS') ,55);
749
750 l_process_name := nvl(fnd_profile.value('BEN_CWB_EMP_RSGN_W_PROCESS'),'RSGNP');
751
752 hr_utility.set_location ('Rsgn Process Name After reading profile'||l_process_name ,55);
753
754 wf_engine.createProcess( ItemType => l_itemtype,
755 ItemKey => l_itemkey,
756 process => l_process_name );
757
758 /*wf_engine.SetItemAttrText( itemtype => l_itemtype
759 , itemkey => l_itemkey
760 , aname => 'MANAGER_NAME'
761 , avalue => 'TY');*/
762
763 -- Changed for embedded region
764
765 wf_engine.setitemattrtext (itemtype => l_itemtype,
766 itemkey => l_itemkey,
767 aname => 'TRANSACTION_ID',
768 avalue => p_transaction_id
769 );
770
771 wf_engine.SetItemAttrText( itemtype => l_itemtype
772 , itemkey => l_itemkey
773 , aname => 'PLAN_NAME'
774 , avalue => p_plan_name );
775 /*wf_engine.SetItemAttrText( itemtype => l_itemtype
776 , itemkey => l_itemkey
777 , aname => 'COMMENTS_CURR_WS_MANAGER'
778 , avalue => p_message ); */
779
780 open approver_name(p_curr_ws_manager_id);
781
782 fetch approver_name into l_curr_ws_manager_user , l_curr_ws_manager_name , l_curr_ws_manager_last_name ;
783 wf_engine.SetItemAttrText( itemtype => l_itemtype
784 , itemkey => l_itemkey
785 , aname => 'CURRENT_WS_MANAGER'
786
787 , avalue => l_curr_ws_manager_user );
788
789
790
791 close approver_name;
792 open approver_name(p_prop_ws_manager_id);
793 fetch approver_name into l_prop_ws_manager_user , l_prop_ws_manager_name , l_prop_ws_manager_last_name;
794 wf_engine.SetItemAttrText( itemtype => l_itemtype
795 , itemkey => l_itemkey
796 , aname => 'PROPOSED_WS_MANAGER'
797
798 , avalue => l_prop_ws_manager_user);
799
800
801
802 close approver_name;
803 open approver_name(p_requestor_id);
804 fetch approver_name into l_requestor_user , l_requestor_name , l_requestor_last_name ;
805
806
807 wf_engine.SetItemAttrText( itemtype => l_itemtype
808 , itemkey => l_itemkey
809 , aname => 'REQUESTOR_ID'
810 , avalue => p_requestor_id);
811 wf_engine.SetItemAttrText( itemtype => l_itemtype
812 , itemkey => l_itemkey
813 , aname => 'REQUESTOR_PERSON_NAME'
814 , avalue => l_requestor_name);
815 wf_engine.SetItemAttrText( itemtype => l_itemtype
816 , itemkey => l_itemkey
817 , aname => 'REQUESTOR'
818 , avalue => l_requestor_user);
819
820 close approver_name;
821
822
823 wf_engine.SetItemAttrText(itemtype => l_itemtype
824 , itemkey => l_itemkey
825 , aname => 'FROM_ROLE'
826 , avalue => l_requestor_user);
827
828 -- No longer is use
829 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
830 -- itemkey => l_itemkey
831 -- ,aname => 'EMP_RSGN_SUMMARY'
832 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_detail_html/'||l_itemkey);
833 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
834 -- itemkey => l_itemkey
835 -- ,aname => 'EMP_RSGN_EMP'
836 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_employee_table_html/'||l_itemkey);
837 --
838 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
839 -- itemkey => l_itemkey
840 -- ,aname => 'EMP_RSGN_APPR'
841 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_approver_table_html/'||l_itemkey);
842 --
843 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
844 -- itemkey => l_itemkey
845 -- ,aname => 'EMP_RSGN_ERROR'
846 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_error_html/'||l_itemkey);
847 -- No longer is use
848
849
850 l_for_period := get_for_period (p_prop_ws_mgr_per_in_ler_id);
851 hr_utility.TRACE ('l_for_period ' || l_for_period);
852
853 wf_engine.setitemattrtext (itemtype => 'BENCWBFY',
854 itemkey => l_itemkey,
855 aname => 'FOR_PERIOD',
856 avalue => l_for_period
857 );
858
859 /*wf_engine.StartProcess ( ItemType => l_itemtype,
860 ItemKey => l_itemkey ); */
861
862 -- hr_utility.trace_on (null,'ORACLE');
863
864
865
866 -- Insert Approval information record , to fetch approver list in AME
867 open get_top_person_id(p_plan_id);
868 fetch get_top_person_id into l_top_person_id;
869
870 close get_top_person_id;
871
872 select BEN_TRANSACTION_S.NEXTVAL into l_transaction_id from dual;
873
874 insert into ben_transaction(transaction_id,
875 transaction_type,
876 attribute1,
877 attribute2,
878 attribute3,
879 attribute4,
880 attribute5,
881 attribute6,
882 attribute7,
883 attribute8,
884 attribute9,
885 attribute10,
886 attribute40,
887 attribute12,
888 attribute13,
889 attribute14,
890 attribute15,
891 attribute16)
892
893 values (l_transaction_id,
894 'CWBEMPRSGN',
895 'APPR',
896 p_prop_ws_manager_id,
897 l_itemkey,
898 p_plan_name,
899 p_reccount,
900 l_curr_ws_manager_name,
901 l_prop_ws_manager_name,
902 l_requestor_name,
903 p_request_date,
904 p_prop_ws_mgr_per_in_ler_id,
905 p_message,
906 l_top_person_id,
907 p_prop_ws_manager_id,
908 l_requestor_last_name,
909 l_curr_ws_manager_last_name,
910 l_prop_ws_manager_last_name
911 );
912
913
914 -- Approver change logic
915
916
917
918 hr_utility.set_location('in Find approver : 3.....',30);
919
920 -- To check whetehr requestor comes up in hrchy of proposed manager then
921 -- approver chain won't start from propsed manager but +1 'l_flag=1'
922 -- Start
923
924 open approver_hrchy(p_prop_ws_mgr_per_in_ler_id);
925 loop
926 fetch approver_hrchy into l_person_id,l_requestor_per_in_ler_id;
927 exit when approver_hrchy%NOTFOUND; -- bug: 7159487
928
929 l_recCount:=l_recCount+1; -- bug: 7159487
930
931 hr_utility.set_location(l_person_id , 50);
932 if(l_person_id = p_requestor_id ) then
933 l_flag:=1;
934 exit;
935 end if;
936
937
938 end loop;
939 close approver_hrchy;
940
941 if l_recCount=0 then
942 l_requestor_flag:=1;
943 end if;
944
945
946 hr_utility.set_location('after looping'||l_itemkey,300);
947
948 if l_flag=1 then
949
950 open get_manager_id_from_pil(l_requestor_per_in_ler_id);
951
952 fetch get_manager_id_from_pil into l_requestor_person_id;
953 if get_manager_id_from_pil%NOTFOUND then
954 l_requestor_flag:=1;
955 end if;
956
957 if l_requestor_person_id is null then
958 l_requestor_flag:=1;
959
960 end if;
961 hr_utility.set_location('flag'||l_requestor_flag,300);
962 close get_manager_id_from_pil;
963
964
965 if l_requestor_flag <> 1 then
966 update ben_transaction set attribute2=l_requestor_person_id
967 where attribute3=l_itemkey
968 and transaction_type='CWBEMPRSGN'
969 and attribute1='APPR';
970 end if;
971
972 end if;
973
974 -- End Approver change logic
975
976 -- Check if current and proposed worksheet manager both are same.
977 -- Start
978 if p_requestor_id=p_prop_ws_manager_id then
979
980 hr_utility.set_location('Requestor and proposed ws manager same .....',30);
981
982 --open get_popl_id_requestor(p_prop_ws_mgr_per_in_ler_id);
983 --fetch get_popl_id_requestor into l_requestor_popl_id;
984 --close get_popl_id_requestor;
985
986 l_requestor_per_in_ler_id := p_prop_ws_mgr_per_in_ler_id;
987 -- Same logic as above
988
989 open get_manager_id_from_pil(l_requestor_per_in_ler_id);
990
991 fetch get_manager_id_from_pil into l_requestor_person_id;
992 if get_manager_id_from_pil%NOTFOUND then
993 l_requestor_flag:=1;
994 end if;
995
996 if l_requestor_person_id is null then
997 l_requestor_flag:=1;
998
999 end if;
1000 hr_utility.set_location('flag'||l_requestor_flag,300);
1001 close get_manager_id_from_pil;
1002
1003 if l_requestor_flag <> 1 then
1004 update ben_transaction set attribute2=l_requestor_person_id
1005 where attribute3=l_itemkey
1006 and transaction_type='CWBEMPRSGN'
1007 and attribute1='APPR';
1008 end if;
1009
1010 end if;
1011
1012 -- End Current and proposed ws manager check
1013
1014 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1015 , itemkey => l_itemkey
1016 , aname => 'REQUESTOR_FLAG'
1017 , avalue => l_requestor_flag);
1018
1019
1020 wf_engine.StartProcess ( ItemType => l_itemtype,
1021 ItemKey => l_itemkey );
1022
1023 hr_utility.set_location('Leaving '||l_package ,30);
1024
1025 EXCEPTION
1026 when others then
1027 l_error:=sqlerrm;
1028
1029 hr_utility.set_location ('exception is'||l_error , 300);
1030
1031 Wf_Core.Context('BEN_CWB_RSGN_EMP', 'start_workflow',l_error);
1032 raise;
1033
1034 END start_workflow;
1035
1036
1037
1038
1039 /* To send FYI notifications
1040 when approval is not require
1041 */
1042
1043
1044 procedure send_fyi_notifications
1045 (p_requestor_id in number,
1046 p_curr_ws_manager_id number,
1047 p_prop_ws_manager_id number,
1048 p_plan_name varchar2,
1049 p_message varchar2,
1050 p_transaction_id number,
1051 p_request_date varchar2,
1052 p_reccount number,
1053 p_prop_mgr_per_in_ler_id number)
1054 is
1055
1056 l_itemkey number:=p_transaction_id;
1057 l_itemtype varchar2(60) := 'BENCWBFY';
1058 l_process_name varchar2(60) := 'RSGNNTFP';
1059 l_package varchar2(80) := g_package||'.send_fyi_notifications';
1060 l_error varchar2(5000);
1061 l_for_period VARCHAR2(30);
1062
1063 /*l_itemtype varchar2(60) := 'CWBREWF';
1064 l_process_name varchar2(60) := 'CWBREP';*/
1065
1066 cursor approver_name(c_approver_id in number) is
1067 select users.user_name , ppf.first_name , ppf.last_name
1068 from fnd_user users
1069 ,per_all_people_f ppf
1070 where
1071 users.employee_id=ppf.person_id
1072 and users.employee_id=c_approver_id
1073 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
1074
1075 l_requestor_name varchar2(240);
1076 l_requestor_last_name varchar2(240);
1077 l_requestor_user varchar2(240);
1078 l_curr_ws_manager_name varchar2(240);
1079 l_curr_ws_manager_last_name varchar2(240);
1080 l_curr_ws_manager_user varchar2(240);
1081 l_prop_ws_manager_name varchar2(240);
1082 l_prop_ws_manager_last_name varchar2(240);
1083 l_prop_ws_manager_user varchar2(240);
1084 l_transaction_id number;
1085
1086 Begin
1087 /*select BEN_CWB_WF_NTF_S.NEXTVAL into l_itemkey from dual;*/
1088 hr_utility.set_location('Entering '||l_package ,30);
1089 wf_engine.createProcess( ItemType => l_itemtype,
1090 ItemKey => l_itemkey,
1091
1092 process => l_process_name );
1093 /*wf_engine.SetItemAttrText( itemtype => l_itemtype
1094 , itemkey => l_itemkey
1095 , aname => 'HTML_TRY'
1096 , avalue => l_html );
1097 */
1098
1099
1100 -- Changed for embedded region
1101
1102 wf_engine.setitemattrtext (itemtype => l_itemtype,
1103 itemkey => l_itemkey,
1104 aname => 'TRANSACTION_ID',
1105 avalue => p_transaction_id
1106 );
1107
1108 wf_engine.SetItemAttrText( itemtype => l_itemtype
1109 , itemkey => l_itemkey
1110 , aname => 'PLAN_NAME'
1111 , avalue => p_plan_name );
1112 /*wf_engine.SetItemAttrText( itemtype => l_itemtype
1113 , itemkey => l_itemkey
1114 , aname => 'COMMENTS_CURR_WS_MANAGER'
1115 , avalue => p_message ); */
1116 open approver_name(p_curr_ws_manager_id);
1117
1118 fetch approver_name into l_curr_ws_manager_user , l_curr_ws_manager_name , l_curr_ws_manager_last_name ;
1119 wf_engine.SetItemAttrText( itemtype => l_itemtype
1120 , itemkey => l_itemkey
1121 , aname => 'CURRENT_WS_MANAGER'
1122 , avalue => l_curr_ws_manager_user );
1123 close approver_name;
1124 open approver_name(p_prop_ws_manager_id);
1125 fetch approver_name into l_prop_ws_manager_user , l_prop_ws_manager_name , l_prop_ws_manager_last_name;
1126 wf_engine.SetItemAttrText( itemtype => l_itemtype
1127 , itemkey => l_itemkey
1128 , aname => 'PROPOSED_WS_MANAGER'
1129 , avalue => l_prop_ws_manager_user);
1130 close approver_name;
1131
1132 open approver_name(p_requestor_id);
1133 fetch approver_name into l_requestor_user , l_requestor_name , l_requestor_last_name ;
1134 wf_engine.SetItemAttrText( itemtype => l_itemtype
1135 , itemkey => l_itemkey
1136 , aname => 'REQUESTOR_PERSON_NAME'
1137 , avalue => l_requestor_name);
1138 wf_engine.SetItemAttrText( itemtype => l_itemtype
1139 , itemkey => l_itemkey
1140 , aname => 'REQUESTOR'
1141 , avalue => l_requestor_user);
1142 close approver_name;
1143
1144 wf_engine.SetItemAttrText(itemtype => l_itemtype
1145 , itemkey => l_itemkey
1146 , aname => 'FROM_ROLE'
1147 , avalue => l_requestor_user);
1148
1149 wf_engine.SetItemAttrText( itemtype => l_itemtype
1150 , itemkey => l_itemkey
1151 , aname => 'MESSAGE_TYPE'
1152 , avalue => 'FYI');
1153 -- No longer is use
1154 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
1155 -- itemkey => l_itemkey
1156 -- ,aname => 'EMP_RSGN_SUMMARY'
1157 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_detail_html/'||l_itemkey);
1158 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
1159 -- itemkey => l_itemkey
1160 -- ,aname => 'EMP_RSGN_EMP'
1161 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_employee_table_html/'||l_itemkey);
1162 --
1163 -- wf_engine.SetItemAttrText(itemtype => l_itemtype,
1164 -- itemkey => l_itemkey
1165 -- ,aname => 'EMP_RSGN_APPR'
1166 -- ,avalue => 'PLSQL:BEN_CWB_RSGN_EMP.generate_approver_table_html/'||l_itemkey);
1167 -- No longer is use
1168
1169
1170 l_for_period := get_for_period (p_prop_mgr_per_in_ler_id);
1171 hr_utility.TRACE ('l_for_period ' || l_for_period);
1172
1173 wf_engine.setItemattrText (itemtype => l_itemtype,
1174 itemkey => l_itemkey ,
1175 aname => 'FOR_PERIOD',
1176 avalue => l_for_period
1177 );
1178
1179
1180 -- Make entry to make notification header
1181
1182 select BEN_TRANSACTION_S.NEXTVAL into l_transaction_id from dual;
1183
1184 insert into ben_transaction(transaction_id,
1185 transaction_type,
1186 attribute1,
1187 attribute2,
1188 attribute3,
1189 attribute4,
1190 attribute5,
1191 attribute6,
1192 attribute7,
1193 attribute8,
1194 attribute9,
1195 attribute40,
1196 attribute14,
1197 attribute15,
1198 attribute16)
1199
1200 values (l_transaction_id,
1201 'CWBEMPRSGN',
1202 'APPR',
1203 p_prop_ws_manager_id,
1204 l_itemkey,
1205 p_plan_name,
1206 p_reccount,
1207 l_curr_ws_manager_name,
1208 l_prop_ws_manager_name,
1209 l_requestor_name,
1210 fnd_date.canonical_to_date(sysdate), --p_request_date,
1211 p_message,
1212 l_requestor_last_name,
1213 l_curr_ws_manager_last_name,
1214 l_prop_ws_manager_last_name);
1215
1216
1217 wf_engine.StartProcess ( ItemType => l_itemtype,
1218 ItemKey => l_itemkey );
1219
1220
1221 hr_utility.set_location('Leaving '||l_package ,30);
1222
1223 EXCEPTION
1224 when others then
1225 l_error:=sqlerrm;
1226
1227 hr_utility.set_location ('exception is'||l_error , 300);
1228
1229 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'send_fyi_notifications',l_error);
1230 raise;
1231 end send_fyi_notifications;
1232
1233
1234
1235
1236 procedure store_emp_details
1237 (p_per_in_ler_id in number,
1238 p_transaction_id in number,
1239 p_emp_name in varchar2,
1240 p_emp_num in varchar2,
1241 p_curr_ws_mgr in varchar2,
1242 p_curr_ws_mgr_id in number,
1243 p_prop_ws_mgr in varchar2,
1244 p_prop_ws_mgr_id in number,
1245 p_requestor in varchar2,
1246 p_requestor_id in number,
1247 p_request_date in varchar2,
1248 p_prop_ws_mgr_per_in_ler_id in number,
1249 p_curr_ws_mgr_per_in_ler_id in number,
1250 p_group_pl_id in number,
1251 p_business_group in varchar2
1252 )
1253 is
1254
1255
1256
1257 l_transaction_id number;
1258 l_emp_first_name varchar2(240);
1259 l_emp_last_name varchar2(240);
1260 l_curr_mgr_first_name varchar2(240);
1261 l_curr_mgr_last_name varchar2(240);
1262 l_prop_mgr_first_name varchar2(240);
1263 l_prop_mgr_last_name varchar2(240);
1264 l_requestor_first_name varchar2(240);
1265 l_requestor_last_name varchar2(240);
1266 l_package varchar2(80) := g_package||'.store_emp_details';
1267 l_error varchar2(5000);
1268 cursor requestor_name(c_requestor_id in number) is
1269 select ppf.first_name , ppf.last_name
1270 from fnd_user users
1271 ,per_all_people_f ppf
1272 where
1273 users.employee_id=ppf.person_id
1274 and users.employee_id=c_requestor_id
1275 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
1276
1277
1278 begin
1279 --hr_utility.trace_on (null, 'ORACLE');
1280 hr_utility.set_location('Entering '||l_package ,30);
1281
1282 l_emp_first_name := substr(p_emp_name,0, instr(p_emp_name,' ')-1);
1283 l_emp_last_name := substr(p_emp_name, instr(p_emp_name,' ')+1);
1284
1285 l_curr_mgr_first_name:= substr(p_curr_ws_mgr,0, instr(p_curr_ws_mgr,' ')-1);
1286 l_curr_mgr_last_name:= substr(p_curr_ws_mgr, instr(p_curr_ws_mgr,' ')+1);
1287
1288 l_prop_mgr_first_name:= substr(p_prop_ws_mgr,0, instr(p_prop_ws_mgr,' ')-1);
1289 l_prop_mgr_last_name:= substr(p_prop_ws_mgr, instr(p_prop_ws_mgr,' ')+1);
1290
1291 open requestor_name(p_requestor_id);
1292 fetch requestor_name into l_requestor_first_name , l_requestor_last_name ;
1293 close requestor_name;
1294
1295 select BEN_TRANSACTION_S.NEXTVAL into l_transaction_id from dual;
1296
1297 insert into ben_transaction(transaction_id,
1298 transaction_type,
1299 attribute1,
1300 attribute2,
1301 attribute3,
1302 attribute4,
1303 attribute5,
1304 attribute6,
1305 attribute7,
1306 attribute8,
1307 attribute9,
1308 attribute10,
1309 attribute11,
1310 attribute14,
1311 attribute15,
1312 attribute16,
1313 attribute17,
1314 attribute18,
1315 attribute19,
1316 attribute20,
1317 attribute21,
1318 attribute22
1319 )
1320 values (l_transaction_id,
1321 'CWBEMPRSGN',
1322 'EMP',
1323 p_transaction_id,
1324 p_per_in_ler_id,
1325 p_emp_num,
1326 l_curr_mgr_first_name,
1327 p_curr_ws_mgr_id,
1328 l_prop_mgr_first_name,
1329 p_prop_ws_mgr_id,
1330 l_requestor_first_name,
1331 p_requestor_id,
1332 fnd_date.date_to_canonical(sysdate) , --p_request_date,
1333 p_prop_ws_mgr_per_in_ler_id,
1334 l_emp_first_name,
1335 p_curr_ws_mgr_per_in_ler_id,
1336 l_emp_last_name,
1337 l_curr_mgr_last_name,
1338 l_prop_mgr_last_name,
1339 l_requestor_last_name,
1340 p_group_pl_id,
1341 p_business_group
1342 );
1343
1344 hr_utility.set_location('Leaving '||l_package ,30);
1345
1346 EXCEPTION
1347 when others then
1348 l_error:=sqlerrm;
1349
1350 hr_utility.set_location ('exception is'||l_error , 300);
1351
1352 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'store_emp_details',l_error);
1353 raise;
1354
1355
1356 end store_emp_details;
1357
1358
1359
1360
1361 procedure remove_emp_details(itemtype in varchar2
1362 , itemkey in varchar2
1363 , actid in number
1364 , funcmode in varchar2
1365 , result out nocopy varchar2)
1366 is
1367
1368 l_package varchar2(80) := g_package||'.remove_emp_details';
1369 l_error varchar2(5000);
1370 begin
1371
1372 hr_utility.set_location('Entering '||l_package ,30);
1373 update ben_transaction set status='DEL'
1374 where attribute1= 'EMP'
1375 and transaction_type='CWBEMPRSGN'
1376 and attribute2=to_number(itemkey);
1377
1378 result:='COMPLETE:';
1379 hr_utility.set_location('Leaving '||l_package ,30);
1380
1381 EXCEPTION
1382 when others then
1383 l_error:=sqlerrm;
1384
1385 hr_utility.set_location ('exception is'||l_error , 300);
1386
1387 Wf_Core.Context('BEN_CWB_RSGN_EMP' , 'remove_emp_details',l_error);
1388 raise;
1389 end remove_emp_details;
1390
1391
1392 -- fixed bug 11062444
1393 function is_in_comp_manager_role(p_person_id in number) return varchar2 is
1394 cursor c1(c_role_id in number) is
1395 SELECT pei.person_id person_id, ppf.full_name person_name ,
1396 usr.user_name user_name, usr.user_id user_id
1397 FROM per_people_extra_info pei , per_all_people_f ppf ,
1398 fnd_user usr , pqh_roles rls
1399 WHERE information_type = 'PQH_ROLE_USERS' and pei.person_id = ppf.person_id
1400 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
1401 and usr.employee_id = ppf.person_id
1402 and rls.role_id = to_number(nvl(pei.pei_information3,-1)) --to_number(pei.pei_information3)
1403 and nvl(pei.pei_information5,'Y')='Y'
1404 and rls.role_id = c_role_id
1405 and pei.person_id = p_person_id;
1406
1407 cursor c2 is select role_id,role_name
1408 from pqh_roles
1409 where role_type_cd ='CWB';
1410
1411 l_package varchar2(80) := g_package||'.is_in_comp_manager_role';
1412
1413 begin
1414 hr_utility.set_location('Entering '||l_package ,30);
1415 for i in c2
1416 loop
1417 for j in c1(i.role_id)
1418 loop
1419 --if (j.person_id = p_person_id ) then
1420 return 'Y';
1421 --end if;
1422 end loop;
1423 end loop;
1424 hr_utility.set_location('Leaving '||l_package ,30);
1425 return 'N';
1426 end;
1427
1428
1429 PROCEDURE generate_detail_html
1430 (
1431 document_id IN VARCHAR2,
1432 display_type IN VARCHAR2,
1433 document IN OUT NOCOPY VARCHAR2,
1434 document_type IN OUT NOCOPY VARCHAR2
1435 )
1436 IS
1437 l_package varchar2(80) := g_package||'.generate_detail_html';
1438 BEGIN
1439 hr_utility.set_location('Entering '||l_package ,30);
1440 -- No longer is use
1441 hr_utility.set_location('Leaving '||l_package ,30);
1442 END;
1443
1444 PROCEDURE generate_employee_table_html
1445 (
1446 document_id IN VARCHAR2,
1447 display_type IN VARCHAR2,
1448 document IN OUT NOCOPY VARCHAR2,
1449 document_type IN OUT NOCOPY VARCHAR2
1450 )
1451 IS
1452 l_package varchar2(80) := g_package||'.generate_employee_table_html';
1453 BEGIN
1454 hr_utility.set_location('Entering '||l_package ,30);
1455 -- No longer is use
1456 hr_utility.set_location('Leaving '||l_package ,30);
1457 END;
1458
1459 /*
1460 generate_approver_table_html
1461 */
1462
1463 PROCEDURE generate_approver_table_html
1464 (
1465 document_id IN VARCHAR2,
1466 display_type IN VARCHAR2,
1467 document IN OUT NOCOPY VARCHAR2,
1468 document_type IN OUT NOCOPY VARCHAR2
1469 )
1470 IS
1471 l_package varchar2(80) := g_package||'.generate_approver_table_html';
1472 BEGIN
1473 hr_utility.set_location('Entering '||l_package ,30);
1474 -- No longer is use
1475 hr_utility.set_location('Leaving '||l_package ,30);
1476 END;
1477
1478 PROCEDURE generate_error_html
1479 (
1480 document_id IN VARCHAR2,
1481 display_type IN VARCHAR2,
1482 document IN OUT NOCOPY VARCHAR2,
1483 document_type IN OUT NOCOPY VARCHAR2
1484 )
1485 IS
1486 l_package varchar2(80) := g_package||'.generate_error_html';
1487 BEGIN
1488 hr_utility.set_location('Entering '||l_package ,30);
1489 -- No longer is use
1490 hr_utility.set_location('Leaving '||l_package ,30);
1491 END;
1492
1493 END BEN_CWB_RSGN_EMP;