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