[Home] [Help]
PACKAGE BODY: APPS.PER_CHECKLIST_EVENTS
Source
1 package body PER_CHECKLIST_EVENTS as
2 /* $Header: pecklevt.pkb 120.12.12020000.10 2012/07/05 02:04:02 amnaraya ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PER_CHECKLIST_EVENTS.';
7 g_debug boolean := hr_utility.debug_enabled;
8
9
10 procedure check_approvers_exist
11 (itemtype in varchar2,
12 itemkey in varchar2,
13 actid in number,
14 funcmode in varchar2,
15 resultout out nocopy varchar2)
16 is
17 all_approvers ame_util.approversTable2;
18 l_taskin_cklid number;
19 process_complete varchar2(1000);
20 l_proc varchar2(72);
21
22 --
23 l_person_id number; -- 8861932
24 l_allocated_task_id number; -- 8861932
25 l_transaction_id varchar2(100); -- 8861932
26 --
27 begin
28
29 l_proc := g_package||'check_process';
30 hr_utility.set_location('Entering:'|| l_proc, 10);
31 hr_utility.set_location(l_proc,1);
32
33 l_taskin_cklid := wf_engine.GetItemAttrNumber(
34 itemtype => itemtype
35 ,itemkey => itemkey
36 ,aname => 'TASKIN_CKLID'
37 ,ignore_notfound => false
38 );
39 -- 8891632 starts
40 l_allocated_task_id := wf_engine.GetItemAttrNumber(
41 itemtype => itemtype
42 ,itemkey => itemkey
43 ,aname => 'ALLOC_TASKID'
44 ,ignore_notfound => false
45 );
46 SELECT pac.person_id INTO l_person_id
47 FROM per_allocated_tasks pat, per_allocated_checklists pac
48 WHERE allocated_task_id = l_allocated_task_id
49 AND pac.allocated_checklist_id = pat.allocated_checklist_id;
50
51 l_transaction_id := l_taskin_cklid||'-'||l_person_id;
52 -- 8891632 ends
53
54 ame_api2.getAllApprovers7(
55 APPLICATIONIDIN => 800,
56 TRANSACTIONTYPEIN => 'CHECKLISTID',
57 --TRANSACTIONIDIN => l_taskin_cklid,
58 TRANSACTIONIDIN => l_transaction_id, -- 8891632
59 approvalProcessCompleteYNout => process_complete,
60 APPROVERSOUT => ALL_APPROVERS);
61
62 if all_approvers.count =0 then
63 resultout:='COMPLETE:N';
64 else
65 resultout:='COMPLETE:Y';
66 end if;
67
68 hr_utility.set_location('retvalue:'|| resultout, 20);
69 hr_utility.set_location('Leaving:'|| l_proc, 10);
70
71 end;
72
73
74 function getRoleForAllApproversList(p_transaction_id in varchar2) return varchar2
75 is
76
77 l_approver_table ame_util.approversTable2;
78 l_invalid_usr_rec ame_util.approverRecord2;
79 l_ame_admin_rec ame_util.approverRecord2;
80 l_process_complete VARCHAR2(1);
81 l_party_id NUMBER;
82 l_index NUMBER;
83 l_role_name VARCHAR2(30) ;
84 l_role_display VARCHAR2(60) ;
85 l_exp_date DATE ;
86 l_role_exists VARCHAR2(1) ;
87 l_transaction_type VARCHAR2(30);
88 l_users VARCHAR2(2000);
89 l_invalid_users VARCHAR2(2000);
90 l_valid_user VARCHAR2(1);
91 l_index integer :=0;
92 ln_notification_id number;
93 l_task_in_chklist_id varchar2(100); -- 8891632
94
95
96 CURSOR c_chk_wf_role (p_role_name VARCHAR2) IS
97 SELECT 'Y'
98 FROM wf_local_roles
99 WHERE name = p_role_name;
100
101 BEGIN
102 l_task_in_chklist_id := substr(p_transaction_id,1,instr(p_transaction_id,'-')-1); -- 8861932
103
104 -- l_role_name:='CKLLST_ADHOC_'||p_transaction_id;
105 -- l_role_display:='Checklist Group For '||p_transaction_id;
106 l_role_name:='CKLLST_ADHOC_'||l_task_in_chklist_id; -- 8861932
107 l_role_display:='Checklist Group For '||l_task_in_chklist_id; -- 8861932
108
109 l_role_exists:='N';
110 -- Check if the ADHOC role already exists
111 OPEN c_chk_wf_role(l_role_name);
112 FETCH c_chk_wf_role INTO l_role_exists;
113 CLOSE c_chk_wf_role;
114
115 -- Call the AME API to get the list of ALL approvers
116
117 ame_api2.getAllApprovers7 (
118 applicationIdIn => 800,
119 transactionTypeIn => 'CHECKLISTID',
120 transactionIdIn => p_transaction_id,
121 approvalProcessCompleteYNOut => l_process_complete,
122 ApproversOut => l_approver_table);
123
124 if l_approver_table.count > 0 then
125 for l_index in 1 .. l_approver_table.count
126 loop
127 IF l_users IS NULL THEN
128 l_users := l_approver_table(l_index).name;
129 ELSE
130 l_users := l_users ||','||l_approver_table(l_index).name;
131 END IF;
132 end loop;
133 end if;
134
135 -- Check if AME encountered any errors.
136 IF l_approver_table.COUNT <> 0 then
137 -- Check if the ADHOC role already exists
138 IF l_role_exists = 'Y' THEN
139 -- If the role exists, then empty the existing role list
140 wf_directory.RemoveUsersFromAdHocRole
141 (role_name => l_role_name,
142 role_users => NULL);
143
144 -- Add the users we have identified to the role list.
145 wf_directory.AddUsersToAdHocRole
146 (role_name => l_role_name,
147 role_users => l_users);
148 ELSE
149 -- Create an ADHOC role for the approver list and add the
150 -- users.
151 wf_directory.CreateAdHocRole
152 (role_name => l_role_name,
153 role_display_name => l_role_display,
154 role_users => l_users,
155 expiration_date => NULL);
156
157 END IF; -- Check ADHOC role exists
158
159
160 END IF; -- approver count
161
162 return l_role_name;
163 end;
164
165 procedure PROCESS_VOTING
166 (itemtype in varchar2,
167 itemkey in varchar2,
168 actid in number,
169 funcmode in varchar2,
170 resultout out nocopy varchar2)
171
172 is
173 l_proc varchar2(72);
174 l_status varchar2(30);
175 l_responder varchar2(320);
176
177 cursor cur_check_wf_status(cur_p_context varchar2) is
178 select
179 TEXT_VALUE,RESPONDER
180 from
181 wf_notifications wfn,
182 wf_notification_attributes wfna
183 where
184 wfn.notification_id=wfna.notification_id and context like cur_p_context and responder is not null and name ='RESULT';
185
186 begin
187 l_proc := g_package||'PROCESS_VOTING';
188 hr_utility.set_location('Entering:'|| l_proc, 10);
189 hr_utility.set_location(l_proc,1);
190
191 if(funcmode='RUN') then
192 open cur_check_wf_status(itemtype||':'|| itemkey ||':%');
193 fetch cur_check_wf_status into l_status,l_responder;
194 if(cur_check_wf_status%FOUND) then
195 if(l_status is not null and l_status='COMPLETE' )then
196 wf_engine.SetItemAttrText(itemtype,itemkey,'TASK_STATUS', hr_general.decode_lookup('PER_CHECKLIST_TASK_STATUS','COM'));
197 wf_engine.SetItemAttrText(itemtype,itemkey,'TASK_DONE_BY',l_responder);
198 resultout:='COMPLETE:'||l_status;
199 else
200 if (l_status is not null and l_status='NOT COMPLETED' )then
201 wf_engine.SetItemAttrText(itemtype,itemkey,'TASK_STATUS',hr_general.decode_lookup('PER_CHECKLIST_TASK_STATUS','REJ'));
202 wf_engine.SetItemAttrText(itemtype,itemkey,'TASK_DONE_BY',l_responder);
203 resultout:='COMPLETE:'||l_status;
204 end if;
205 end if;
206 end if;
207 close cur_check_wf_status;
208 else
209 resultout:=null;
210 end if;
211 hr_utility.set_location('result:'|| resultout, 20);
212 hr_utility.set_location('Leaving:'|| l_proc, 10);
213 end;
214
215 --
216 -- ------------------------------------------------------------------------
217 -- |----------------------------< create_event>---------------------------|
218 -- ------------------------------------------------------------------------
219 --
220 procedure CREATE_EVENT
221 (p_effective_date in date,
222 P_person_id in number,
223 P_assignment_id in number,
224 P_ler_id in number)
225 is
226 l_event_id number;
227 --
228 l_proc varchar2(72) ;
229 --cursor event_exists(p_person_id number,p_assignment_id number,p_ler_id number, p_effective_date date) is
230 cursor event_exists is
231 SELECT 'X'
232 FROM per_ben_identified_events
233 WHERE person_id = p_person_id
234 AND nvl(assignment_id,-1) = nvl(p_assignment_id,-1)
235 AND event_reason_id = p_ler_id
236 AND effective_date = p_effective_date;
237 --
238 l_exists varchar2(30);
239 --
240 --
241 begin
242 --
243 g_debug := hr_utility.debug_enabled;
244 if g_debug then
245 l_proc := g_package||'create_event';
246 hr_utility.set_location('Entering:'|| l_proc, 10);
247 end if;
248 --
249 open event_exists;
250 fetch event_exists into l_exists;
251 if event_exists%notfound then
252 if g_debug then
253 hr_utility.set_location('Entering '||l_proc,20);
254 end if;
255 insert into per_ben_identified_events(
256 ben_identified_event_id,
257 event_reason_id,
258 person_id,
259 assignment_id,
260 status,
261 effective_date,
262 object_version_number)
263 values(
264 per_ben_identified_events_s.nextval,
265 p_ler_id,
266 p_person_id,
267 p_assignment_id,
268 'Pending',
269 p_effective_date,
270 1);
271 end if;
272 close event_exists;
273 if g_debug then
274 hr_utility.set_location('Leaving '||l_proc,30);
275 end if;
276 End create_event;
277 --
278
279 -- ---------------------------------------------------------------------------------
280 -- |------------------------------PROCESS EVENT------------------------------------|
281 -- ---------------------------------------------------------------------------------
282 --
283 procedure PROCESS_EVENT
284 (p_person_id in number
285 ,p_assignment_id in number default null
286 ,p_date in date
287 ,p_ler_event_id in number) as
288
289 l_ler_event_id number;
290 l_person_id number;
291 l_assignment_id number;
292 --
293 l_proc varchar2(72) ;
294 --
295 --
296 -- Cursor fetches all the tasks in checklist for the event attached for the checklist
297 --
298 cursor c1(pl_ler_event number) is
299 select
300 ctsk.eligibility_object_id
301 ,ckl.checklist_id
302 ,ckl.name checklist_name
303 ,ckl.description checklist_description
304 ,ckl.checklist_category
305 ,ctsk.checklist_task_name
306 ,ctsk.description checklist_task_description
307 ,ctsk.task_in_checklist_id
308 ,ctsk.target_duration
309 ,ctsk.target_duration_uom
310 ,ctsk.mandatory_flag
311 ,ctsk.ame_attribute_identifier
312 ,ctsk.action_url
313 ,ckl.event_reason_id
314 ,ckl.business_group_id
315 from
316 per_tasks_in_checklist ctsk,
317 per_checklists ckl
318 where ckl.event_reason_id = pl_ler_event
319 and ctsk.checklist_id = ckl.checklist_id
320 order by ckl.checklist_id,ctsk.eligibility_object_id;
321 x_cur c1%rowtype;
322 --
323 --
324 -- Cursor to check whether a task is already allocated
325 --
326 cursor chk_exists (p_person_id number
327 ,p_task_in_checklist_id number) is
328 select
329 'Exists'
330 from
331 per_tasks_in_checklist tic,
332 per_allocated_checklists pac,
333 per_allocated_tasks pat
334 where
335 tic.task_in_checklist_id = p_task_in_checklist_id
336 and pac.person_id = p_person_id
337 and tic.checklist_id = pac.checklist_id
338 and tic.checklist_task_name = pat.task_name
339 and pac.allocated_checklist_id = pat.allocated_checklist_id;
340 --
341 cursor alloc_ckl_exists (p_person_id number
342 ,p_assignment_id number
343 ,p_checklist_id number) is
344 select allocated_checklist_id
345 FROM per_allocated_checklists
346 WHERE person_id = p_person_id
347 AND nvl(assignment_id,-1) = nvl(p_assignment_id,-1)
348 AND checklist_id = p_checklist_id;
349
350 x1 varchar2(10);
351 l_chk varchar2(2000);
352 --==============AME=======================
353 i integer; --approver_count
354 l_approver_display_name varchar2(360);
355 l_approver_name varchar2(320);
356 all_approvers ame_util.approversTable2;
357 process_complete varchar2(1000);
358 l_orig_system varchar2(30);
359 l_orig_system_id number;
360 l_approver_order_number number;
361 l_approver_count number;
362 l_current_approver number :=1;
363 l_transaction_id varchar2(100); -- 8861932
364 --==============AME=======================
365
366 l_dummy varchar2(10);
367 current_checklist_id number;
368 current_alloc_ckl_id number;
369 l_allocated_task_id number;
370 l_alloc_task_ovn number;
371 l_alloc_ckl_id number;
372 l_alloc_ckl_ovn number;
373 l_target_end_date date;
374 --
375 begin
376 --
377 --
378 g_debug := hr_utility.debug_enabled;
379 if g_debug then
380 l_proc := g_package||'create_event';
381 hr_utility.set_location('Entering:'|| l_proc, 10);
382 hr_utility.set_location('person_id '||to_char(p_person_id),10);
383 hr_utility.set_location('asg id '||to_char(p_assignment_id),10);
384 hr_utility.set_location('event reason '||to_char(p_ler_event_id),10);
385 end if;
386 --
387 --loop throught the number of records fetched which are pending to process.
388 --
389
390 for x_cur in c1(p_ler_event_id) loop
391 if g_debug then
392 hr_utility.set_location('In the first loop',20);
393 end if;
394
395 --
396 -- if not a concurrent request ben code BEN_PER_ASG_ELIG
397 -- lines are intializing so deferring in that case
398
399 if x_cur.eligibility_object_id is not null and fnd_global.conc_request_id not in (0,-1) then
400 ben_env_object.init
401 (p_business_group_id => x_cur.business_group_id,
402 p_thread_id => null,
403 p_chunk_size => null,
404 p_threads => null,
405 p_max_errors => null,
406 p_benefit_action_id => null,
407 p_effective_date=> p_date);
408 end if;
409 --
410 if x_cur.eligibility_object_id is null or ben_per_asg_elig.Eligible(p_person_id,
411 p_assignment_id,
412 x_cur.eligibility_object_id,
413 p_date,
414 x_cur.business_group_id,
415 false)
416 then
417 if g_debug then
418 hr_utility.set_location('In the first loop',30);
419 end if;
420 --
421 open chk_exists(p_person_id,x_cur.task_in_checklist_id);
422 fetch chk_exists into l_dummy;
423 if chk_exists%notfound then
424 if g_debug then
425 hr_utility.set_location('In the first loop',40);
426 end if;
427 --
428 if nvl(x_cur.checklist_id,-1) <> nvl(current_checklist_id,-1) then
429 --
430 if g_debug then
431 hr_utility.set_location('In the first loop',50);
432 end if;
433 --
434 open alloc_ckl_exists (p_person_id,p_assignment_id,x_cur.checklist_id);
435 fetch alloc_ckl_exists into current_alloc_ckl_id;
436 if alloc_ckl_exists%notfound then
437 --
438 l_alloc_ckl_id := null;
439 current_checklist_id := x_cur.checklist_id;
440 PER_ALLOCATED_CHECKLIST_API.CREATE_ALLOC_CHECKLIST
441 (p_validate => false
442 ,p_effective_date => p_date
443 ,p_checklist_id => current_checklist_id
444 ,p_checklist_name => x_cur.checklist_name
445 ,p_description => x_cur.checklist_description
446 ,p_checklist_category => x_cur.checklist_category
447 ,p_person_id => p_person_id
448 ,p_assignment_id => p_assignment_id
449 ,p_allocated_checklist_id => l_alloc_ckl_id
450 ,p_object_version_number => l_alloc_ckl_ovn
451 );
452 current_alloc_ckl_id := l_alloc_ckl_id;
453 end if;
454 close alloc_ckl_exists;
455 --
456 if g_debug then
457 hr_utility.set_location('In the first loop',80);
458 end if;
459 --
460 end if;
461 if g_debug then
462 hr_utility.set_location('In the first loop',90);
463 end if;
464 --
465 -- ---------------------------------------------------------
466 -- ---------------------------------------------------------
467
468 -- ==============================================================================
469 -- ==============Begin of Initial approver from AME =============================
470 -- ==============================================================================
471
472 --
473 -- run AME and get first performer and count for populating
474 -- PER_ALLOCATED_TASK columns for performer.
475 --
476 begin
477 if x_cur.ame_attribute_identifier is not null then
478
479 if g_debug then
480 hr_utility.set_location('before approver'||l_proc,75);
481 end if;
482 --
483 -- Get All approvers
484 --
485 l_transaction_id := x_cur.task_in_checklist_id||'-'||p_person_id; -- 8861932
486
487 ame_api2.getAllApprovers7(
488 APPLICATIONIDIN => 800,
489 TRANSACTIONTYPEIN => 'CHECKLISTID',
490 TRANSACTIONIDIN => l_transaction_id, -- 8861932
491 approvalProcessCompleteYNout => process_complete,
492 APPROVERSOUT => ALL_APPROVERS);
493 if g_debug then
494 hr_utility.set_location('after approver'||l_proc,76);
495 end if;
496
497 if all_approvers.count > 0 then
498 i := 1; -- approver_count
499 l_orig_system := all_approvers(i).orig_system;
500 l_orig_system_id := all_approvers(i).orig_system_id;
501 l_approver_count := all_approvers.count;
502 l_approver_display_name := all_approvers(i).display_name;
503 l_approver_name := all_approvers(i).name;
504 end if;
505 end if; -- ame_attribute_identifier not null
506
507 if x_cur.ame_attribute_identifier is null or
508 all_approvers.count=0 then
509 l_orig_system := null;
510 l_orig_system_id := null;
511 l_approver_order_number := null;
512 l_approver_display_name := null;
513 l_approver_name := null;
514 end if;
515 end;
516 --
517 -- ==============================================================================
518 -- ================End of Initial approver from AME =============================
519 -- ==============================================================================
520
521 begin
522 if g_debug then
523 hr_utility.set_location('Before target date'||l_proc,200);
524 end if;
525
526 --
527 -- Calculate target end date
528 --
529 if nvl(x_cur.target_duration,-1) <> -1 and
530 nvl(x_cur.target_duration_uom,'ZZ') <> 'ZZ' then
531 --
532 if x_cur.target_duration_uom = 'D' then
533 l_target_end_date := trunc(sysdate+x_cur.target_duration);
534 end if;
535 if x_cur.target_duration_uom = 'W' then
536 l_target_end_date := trunc(sysdate+x_cur.target_duration*7);
537 end if;
538 if x_cur.target_duration_uom = 'M' then
539 l_target_end_date := add_months(trunc(sysdate),x_cur.target_duration);
540 end if;
541 --
542 else
543 l_target_end_date := null;
544 end if;
545 --
546 -- Default the Performer Orig System if not already supplied
547 --
548 IF l_orig_system IS NULL THEN
549 l_orig_system := 'PER';
550 END IF;
551 --
552 if g_debug then
553 hr_utility.set_location('After Target Date'||l_proc,210);
554 hr_utility.set_location('Target date '||to_char(l_target_end_date),210);
555 hr_utility.set_location('alloc_task_id '||to_char(l_allocated_task_id),210);
556 end if;
557 --
558 PER_ALLOCATED_TASK_API.CREATE_ALLOC_TASK
559 ( p_validate => false
560 ,p_effective_date => p_date
561 ,p_allocated_checklist_id => current_alloc_ckl_id
562 ,p_task_name => x_cur.checklist_task_name
563 ,p_description => x_cur.checklist_task_description
564 ,p_performer_orig_system => l_orig_system
565 ,p_performer_orig_sys_id => l_orig_system_id
566 ,p_status => 'INI' -- It may be better to populate by global variable
567 ,p_mandatory_flag => x_cur.mandatory_flag
568 ,p_action_url => x_cur.action_url
569 --,p_task_owner_person_id =>
570 --,p_task_sequence => 1
571 ,p_target_start_date => trunc(sysdate)
572 ,p_target_end_date => l_target_end_date
573 ,p_allocated_task_id => l_allocated_task_id
574 ,p_object_version_number => l_alloc_task_ovn
575 );
576 -- TASK_STATUS ......!!!!!
577 hr_utility.set_location('In the first loop',110);
578 end;
579 --
580 hr_utility.set_location('after insert',70);
581 --
582 -- ==============================================================================
583 -- ===============Begin of start workflow if eligible ===========================
584 -- ==============================================================================
585 --
586 -- call workflow only if an approver is found.
587 --
588 if l_approver_name is not null then
589 if g_debug then
590 hr_utility.set_location('Before wkflow'||l_proc,80);
591 hr_utility.set_location('approver_order num '||to_char(l_approver_order_number),999);
592 end if;
593 --
594 begin
595 --
596 per_checklist_events.Start_wf_Process
597 (p_person_id => p_person_id
598 ,p_assignment_id => p_assignment_id
599 ,p_task_name => x_cur.checklist_task_name
600 ,p_task_description => x_cur.checklist_task_description
601 ,p_checklist_name => x_cur.checklist_name
602 ,p_checklist_description => x_cur.checklist_description
603 ,p_performer_name => l_approver_name
604 ,p_performer_display_name => l_approver_display_name
605 ,p_target_date => l_target_end_date
606 ,p_total_approvers => l_approver_count
607 ,p_current_approver_num => l_current_approver
608 ,p_allocated_task_id => l_allocated_task_id
609 ,p_task_in_checklist_id => x_cur.task_in_checklist_id
610 );
611
612 --
613 if g_debug then
614 hr_utility.set_location('After wkflow'||l_proc,80);
615 end if;
616 --
617 end;
618 --
619 --
620 end if; -- approver is not null
621 --
622 l_orig_system := null;
623 l_orig_system_id := null;
624 l_approver_name := null;
625 l_approver_display_name := null;
626 l_approver_count := null;
627 l_current_approver := null;
628 l_allocated_task_id := null;
629 --
630 -- ==============================================================================
631 -- ========================= End of Workflow ====================================
632 -- ==============================================================================
633 end if; -- chk_exists
634 close chk_exists;
635 hr_utility.set_location('In the first loop',120);
636 --
637 end if; -- BEN Eligibility engine fetches an eligible object
638 --
639 hr_utility.set_location('In the first loop',130);
640 end loop;
641 --
642
643 hr_utility.set_location('In the first loop',140);
644
645 exception
646 when others then
647 --
648 raise;
649 --
650 end process_event; -- Procedure
651
652 --
653 --
654 -- ---------------------------------------------------------------------------------
655 -- |------------------------------Allocate Tasks-----------------------------------|
656 -- ---------------------------------------------------------------------------------
657 --
658 Procedure ALLOCATE_TASKS(errbuf out nocopy varchar2
659 ,retcode out nocopy number
660 ,p_purge in varchar2) as
661 --
662 cursor pending_events is
663 SELECT person_id ,
664 assignment_id ,
665 effective_date,
666 event_reason_id
667 FROM per_ben_identified_events
668 WHERE status = 'Pending'
669 FOR UPDATE OF status;
670 --
671 l_proc varchar2(72) ;
672 l_errbuf varchar2(2000);
673 l_retcode varchar2(1000);
674 --
675 --l_events pending_events%rowtype;
676 --
677 begin
678 --
679 fnd_file.put_line(FND_FILE.LOG, ' Allocate Tasks Process Started');
680 --
681 g_debug := hr_utility.debug_enabled;
682 if g_debug then
683 l_proc := g_package||'Allocate_Tasks';
684 hr_utility.set_location('Entering:'|| l_proc, 10);
685 hr_utility.set_location('Before process call',1);
686 end if;
687 --
688 FOR p_cur in pending_events loop
689 if nvl(p_cur.assignment_id,-1) <> -1 then
690 per_checklist_events.process_event
691 ( p_person_id => p_cur.person_id
692 ,p_assignment_id => p_cur.assignment_id
693 ,p_date => p_cur.effective_date
694 ,p_ler_event_id => p_cur.event_reason_id
695 );
696 if g_debug then
697 hr_utility.set_location(l_proc||'after process event call',2);
698 end if;
699 else
700 if g_debug then
701 hr_utility.set_location(l_proc||'Before Procecss with ASG',3);
702 end if;
703 --
704 per_checklist_events.process_event
705 ( p_person_id => p_cur.person_id
706 ,p_assignment_id => null
707 ,p_date => p_cur.effective_date
708 ,p_ler_event_id => p_cur.event_reason_id
709 );
710 end if;
711 if g_debug then
712 hr_utility.set_location(l_proc||'After Procecss Call',4);
713 end if;
714 --
715 -- Update the status from PENDING to Processed
716 --
717 UPDATE per_ben_identified_events
718 SET status = 'Processed'
719 WHERE CURRENT OF pending_events;
720 end loop;
721 --
722 -- Purge records that are processed. Purges all records
723 --
724 if nvl(p_purge,'N') = 'Y' then
725 delete from per_ben_identified_events
726 where status = 'Processed';
727 end if;
728 --
729 commit;
730 --
731 fnd_file.put_line(FND_FILE.LOG, ' Allocate Tasks Process Completed');
732 retcode := 0;
733 --
734 exception
735 when others then
736 --
737 fnd_file.put_line(FND_FILE.LOG, ' Allocate Tasks Process Errored');
738 errbuf := substr(sqlerrm,0,240);
739 retcode := sqlcode;
740 --
741 fnd_file.put_line(fnd_file.log,sqlerrm||' '||sqlcode);
742 --
743 raise;
744 end allocate_tasks;
745 --
746 --
747 -- ---------------------------------------------------------------------------------
748 -- |---------------------------Allocate Person Tasks-------------------------------|
749 -- ---------------------------------------------------------------------------------
750 --
751 Procedure ALLOCATE_PERSON_TASKS(p_person_id in number) as
752 --
753 cursor pending_events is
754 SELECT person_id ,
755 assignment_id ,
756 effective_date,
757 event_reason_id
758 FROM per_ben_identified_events
759 WHERE status = 'Pending'
760 AND person_id = p_person_id
761 FOR UPDATE OF status;
762
763 --l_events pending_events%rowtype;
764 l_proc varchar2(72) ;
765 --
766 --
767 begin
768 --
769 --
770 g_debug := hr_utility.debug_enabled;
771 if g_debug then
772 l_proc := g_package||'Allocate_person_tasks';
773 hr_utility.set_location('Entering:'|| l_proc, 10);
774 hr_utility.set_location('Before process call',1);
775 end if;
776 --
777 FOR p_cur in pending_events loop
778 if nvl(p_cur.assignment_id,-1) <> -1 then
779 per_checklist_events.process_event
780 ( p_person_id => p_cur.person_id
781 ,p_assignment_id => p_cur.assignment_id
782 ,p_date => p_cur.effective_date
783 ,p_ler_event_id => p_cur.event_reason_id
784 );
785 hr_utility.set_location('after process call',2);
786 else
787 hr_utility.set_location('after process call',3);
788 per_checklist_events.process_event
789 ( p_person_id => p_cur.person_id
790 ,p_assignment_id => null
791 ,p_date => p_cur.effective_date
792 ,p_ler_event_id => p_cur.event_reason_id
793 );
794 end if;
795 hr_utility.set_location('after process call',4);
796 --
797 -- Update the status from PENDING to Processed
798 --
799 UPDATE per_ben_identified_events
800 SET status = 'Processed'
801 WHERE CURRENT OF pending_events;
802 end loop;
803
804 --
805
806 commit;
807 if g_debug then
808 hr_utility.set_location('Leaving:'|| l_proc, 100);
809 end if;
810 --
811 exception
812 when others then
813 raise;
814 end allocate_person_tasks;
815 --
816 -- ------------------------------------------------------------------------
817 -- |----------------------< Start_WF_Process>-----------------------|
818 -- ------------------------------------------------------------------------
819 --
820 -- Description
821 --
822 -- Initialize the Checklist Workflow process
823 --
824 --
825 procedure START_WF_PROCESS (p_person_id in number
826 ,p_assignment_id in number default null
827 ,p_checklist_name in varchar2
828 ,p_checklist_description in varchar2
829 ,p_task_name in varchar2
830 ,p_task_description in varchar2
831 ,p_performer_name in varchar2
832 ,p_performer_display_name in varchar2
833 ,p_target_date in date
834 ,p_total_approvers in number
835 ,p_current_approver_num in number default 1
836 ,p_allocated_task_id in number
837 ,p_task_in_checklist_id in number) as
838
839
840 l_item_key varchar2(240) := 'Checklist Task '||p_allocated_task_id;
841 l_process varchar2(30) := 'PERCHECKLISTPROCESS';
842 l_item_type varchar2(8) := 'HRCKLTSK';
843 l_person_name varchar2(240);
844 l_proc varchar2(72) ;
845 l_transaction_id varchar2(100); -- 8861932
846 --
847 l_user_key varchar2(240) := l_Item_Key;
848 --
849 -- Added effective date in place of sysdate to enable checklist notification for future dated employees as per Bug#12385504
850 /*CURSOR csr_person_name is
851 SELECT papf.full_name
852 FROM per_all_people_f papf
853 WHERE papf.person_id = p_person_id
854 AND
855 (
856 SELECT max (ppos.date_start)
857 FROM per_periods_of_service ppos
858 WHERE ppos.person_id = p_person_id
859 ) BETWEEN papf.effective_start_date
860 AND papf.effective_end_date;*/ --Commented for Bug#12385504
861
862 CURSOR csr_person_name is
863 SELECT papf.full_name
864 FROM per_all_people_f papf,
865 (
866 SELECT to_date(max (ppos.date_start)) effective_date
867 FROM per_periods_of_service ppos
868 WHERE ppos.person_id = p_person_id
869 ) ppfe
870 WHERE papf.person_id = p_person_id
871 AND papf.effective_start_date =
872 NVL(ppfe.effective_date,(SELECT to_date(max (effective_start_date))
873 FROM per_all_people_f
874 WHERE person_id = p_person_id));
875
876
877 -- End of changes as per Bug#12385504
878
879 varname Wf_Engine.NameTabTyp;
880 varvalue Wf_Engine.TextTabTyp;
881 numname Wf_Engine.NameTabTyp;
882 numvalue Wf_Engine.NumTabTyp;
883 --
884 l_performer_name varchar2(3000);
885
886 begin
887 --
888 g_debug := hr_utility.debug_enabled;
889 if g_debug then
890 l_proc := g_package||'Start_WF_Process';
891 hr_utility.set_location('Entering:'|| l_proc, 10);
892 hr_utility.set_location('Before process call',1);
893 end if;
894 --
895 OPEN csr_person_name;
896 FETCH csr_person_name into l_person_name;
897 CLOSE csr_person_name;
898
899
900 wf_engine.CreateProcess (ItemType => l_Item_Type
901 ,ItemKey => l_Item_Key
902 ,process => l_process
903 ,User_Key => l_user_key
904 ,Owner_Role => 'COREHR' --l_task_owner -- p_task_owner
905 );
906
907 --
908 -- Here l_performer_name is role name created as adhoc
909 l_transaction_id := p_task_in_checklist_id||'-'||p_person_id; -- 8861932
910 -- l_performer_name:=getRoleForAllApproversList(p_task_in_checklist_id);
911 l_performer_name:=getRoleForAllApproversList(l_transaction_id); -- 8861932
912
913 varname(1) := 'PERFORMER';
914 varvalue(1) := l_performer_name;
915 varname(2) := 'PERFORMER_NAME';
916 varvalue(2) := p_performer_display_name;
917 varname(3) := 'TASK';
918 varvalue(3) := p_task_name;
919 varname(4) := 'TASK_DESCRIPTION';
920 varvalue(4) := p_task_description;
921 varname(5) := 'CHECKLIST';
922 varvalue(5) := p_checklist_name;
923 varname(6) := 'CHECKLIST_DESCRIPTION';
924 varvalue(6) := p_checklist_description;
925 varname(7) := 'PERSON';
926 varvalue(7) := l_person_name;
927
928 wf_engine.SetItemAttrTextArray(l_Item_Type,l_Item_Key,varname,varvalue);
929 --
930 numname(1) := 'ALLOC_TASKID';
931 numvalue(1) := p_allocated_task_id;
932 numname(2) := 'TASKIN_CKLID';
933 numvalue(2) := p_task_in_checklist_id;
934
935 wf_engine.SetItemAttrNumberArray(l_Item_Type,l_Item_Key,numname,numvalue);
936 --
937 wf_engine.SetItemAttrDate (itemtype => l_Item_Type
938 ,itemkey => l_Item_Key
939 ,aname => 'TARGETDATE'
940 ,avalue => p_target_date
941 );
942
943 wf_engine.StartProcess (itemtype => l_Item_Type
944 ,itemkey => l_Item_Key
945 );
946 --
947 if g_debug then
948 hr_utility.set_location('Leaving:'|| l_proc, 100);
949 end if;
950 --
951 end start_wf_process;
952 --
953 -- ------------------------------------------------------------------------
954 -- |----------------------< approve_wf_Process>-----------------------|
955 -- ------------------------------------------------------------------------
956 --
957 procedure APPROVE_WF_PROCESS
958 --
959 (itemtype in varchar2,
960 itemkey in varchar2,
961 actid in number,
962 funcmode in varchar2,
963 resultout out nocopy varchar2)
964
965 is
966 l_alloc_id number;
967 l_proc varchar2(72);
968 -- bug 7560762
969 l_task_done_by wf_local_roles.name%type;
970 l_PERFORMER_ORIG_SYS_ID PER_ALLOCATED_TASKS.PERFORMER_ORIG_SYS_ID%type;
971
972 cursor c_performer_orig_sys_id(p_task_done_by varchar2) is
973 SELECT ORIG_SYSTEM_ID
974 FROM wf_local_roles
975 where ORIG_SYSTEM = 'PER'
976 and name = p_task_done_by;
977 -- bug 7560762
978 --
979 begin
980 --
981 g_debug := hr_utility.debug_enabled;
982 if g_debug then
983 l_proc := g_package||'APPROVE_WF_PROCESS';
984 hr_utility.set_location('Entering:'|| l_proc, 10);
985 hr_utility.set_location(l_proc,1);
986 end if;
987 -- Debug code added in 120.9 removed in 120.10
988 --my_test_pkg.ins_my_values(l_proc||'funmode',funcmode);
989 --
990 if (funcmode = 'RUN') then
991 --
992 --
993 l_alloc_id := wf_engine.GetItemAttrNumber(
994 itemtype => itemtype
995 ,itemkey => itemkey
996 ,aname => 'ALLOC_TASKID'
997 ,ignore_notfound => false
998 );
999 --
1000 -- bug 7560762
1001 l_task_done_by := wf_engine.GetItemAttrText(
1002 itemtype => itemtype
1003 ,itemkey => itemkey
1004 ,aname => 'TASK_DONE_BY');
1005 hr_utility.set_location('l_task_done_by '||l_task_done_by,99);
1006 open c_performer_orig_sys_id(l_task_done_by);
1007 fetch c_performer_orig_sys_id into l_PERFORMER_ORIG_SYS_ID;
1008 close c_performer_orig_sys_id;
1009 hr_utility.set_location('l_PERFORMER_ORIG_SYS_ID '||l_PERFORMER_ORIG_SYS_ID,100);
1010 -- bug 7560762
1011 --
1012 update PER_ALLOCATED_TASKS
1013 set status = 'COM',
1014 performer_orig_sys_id = nvl(l_performer_orig_sys_id,performer_orig_sys_id) -- bug 7560762
1015 where ALLOCATED_TASK_ID = l_alloc_id;
1016 --
1017 resultout := l_alloc_id || 'COMPLETE';
1018 --
1019 return;
1020 elsif ( funcmode = 'CANCEL' )
1021 then
1022 --
1023 null;
1024 --
1025 end if;
1026 --
1027 if g_debug then
1028 hr_utility.set_location('Leaving:'|| l_proc, 100);
1029 end if;
1030 --
1031 end approve_wf_process;
1032 --
1033 -- ------------------------------------------------------------------------
1034 -- |-------------------------< rejected_wf_Process>-----------------------|
1035 -- ------------------------------------------------------------------------
1036 --
1037 procedure REJECTED_WF_PROCESS
1038 (itemtype in varchar2,
1039 itemkey in varchar2,
1040 actid in number,
1041 funcmode in varchar2,
1042 resultout out nocopy varchar2) is
1043 --
1044 l_alloc_id number;
1045 l_proc varchar2(72);
1046 -- bug 7560762
1047 l_task_done_by wf_local_roles.name%type;
1048 l_PERFORMER_ORIG_SYS_ID PER_ALLOCATED_TASKS.PERFORMER_ORIG_SYS_ID%type;
1049
1050 cursor c_performer_orig_sys_id(p_task_done_by varchar2) is
1051 SELECT ORIG_SYSTEM_ID
1052 FROM wf_local_roles
1053 where ORIG_SYSTEM = 'PER'
1054 and name = p_task_done_by;
1055 -- bug 7560762
1056
1057 --
1058 begin
1059 --
1060 g_debug := hr_utility.debug_enabled;
1061 if g_debug then
1062 l_proc := g_package||'REJECT_WF_PROCESS';
1063 hr_utility.set_location('Entering:'|| l_proc, 10);
1064 hr_utility.set_location(l_proc,1);
1065 end if;
1066 --
1067 if (funcmode = 'RUN')
1068 then
1069 --
1070 l_alloc_id := wf_engine.GetItemAttrNumber(
1071 itemtype => itemtype
1072 ,itemkey => itemkey
1073 ,aname => 'ALLOC_TASKID'
1074 ,ignore_notfound => false
1075 );
1076 --
1077 -- bug 7560762 starts
1078 l_task_done_by := wf_engine.GetItemAttrText(
1079 itemtype => itemtype
1080 ,itemkey => itemkey
1081 ,aname => 'TASK_DONE_BY');
1082
1083 open c_performer_orig_sys_id(l_task_done_by);
1084 fetch c_performer_orig_sys_id into l_PERFORMER_ORIG_SYS_ID;
1085 close c_performer_orig_sys_id;
1086 -- bug 7560762 ends
1087
1088 update PER_ALLOCATED_TASKS
1089 set status = 'REJ',
1090 performer_orig_sys_id = nvl(l_performer_orig_sys_id,performer_orig_sys_id) -- bug 7560762
1091 where ALLOCATED_TASK_ID = l_alloc_id;
1092 --
1093 resultout := l_alloc_id || 'TASK_REJECTED';
1094 return;
1095 elsif ( funcmode = 'CANCEL' ) then
1096 --
1097 null;
1098 --
1099 end if;
1100 --
1101 if g_debug then
1102 hr_utility.set_location('Leaving:'|| l_proc, 100);
1103 end if;
1104 --
1105 end rejected_wf_process;
1106 --
1107
1108 -- ------------------------------------------------------------------------
1109 -- |------------------------------< Process_fyi>--------------------------|
1110 -- ------------------------------------------------------------------------
1111 --
1112 procedure PROCESS_FYI
1113 (itemtype in varchar2,
1114 itemkey in varchar2,
1115 actid in number,
1116 funcmode in varchar2,
1117 resultout out nocopy varchar2)
1118 is
1119 --
1120 l_total_approvers number;
1121 l_curr_approver number;
1122 l_taskin_cklid number;
1123 l_recipient varchar2(320);
1124 l_recipient_name varchar2(360);
1125 all_approvers ame_util.approversTable2;
1126 process_complete varchar2(1000);
1127 i number;
1128 l_count number;
1129 l_t_count number;
1130 l_boolean boolean;
1131 l_proc varchar2(72);
1132 --
1133 begin
1134 --
1135 g_debug := hr_utility.debug_enabled;
1136 if g_debug then
1137 l_proc := g_package||'PROCESS_FYI';
1138 hr_utility.set_location('Entering:'|| l_proc, 10);
1139 hr_utility.set_location(l_proc,1);
1140 end if;
1141 --
1142 --
1143 if (funcmode = 'RUN')
1144 then
1145 --
1146 l_total_approvers := wf_engine.GetItemAttrNumber(
1147 itemtype => itemtype
1148 ,itemkey => itemkey
1149 ,aname => 'TOTAL_APPROVERS'
1150 ,ignore_notfound => false
1151 );
1152 l_curr_approver := wf_engine.GetItemAttrNumber(
1153 itemtype => itemtype
1154 ,itemkey => itemkey
1155 ,aname => 'CURRENT_APPROVER'
1156 ,ignore_notfound => false
1157 );
1158
1159 if l_total_approvers > 1 and (l_curr_approver < l_total_approvers) then
1160 --
1161 l_count := l_curr_approver+1;
1162 -- insert into tp_temp values ('Current Apr lcount ',l_count);
1163 l_taskin_cklid := wf_engine.GetItemAttrNumber(
1164 itemtype => itemtype
1165 ,itemkey => itemkey
1166 ,aname => 'TASKIN_CKLID'
1167 ,ignore_notfound => false
1168 );
1169 begin
1170 ame_api2.getAllApprovers7(
1171 APPLICATIONIDIN => 800,
1172 TRANSACTIONTYPEIN => 'CHECKLISTID',
1173 TRANSACTIONIDIN => l_taskin_cklid,
1174 approvalProcessCompleteYNout => process_complete,
1175 APPROVERSOUT => ALL_APPROVERS);
1176
1177 l_t_count := all_approvers.count;
1178 l_recipient := all_approvers(l_count).name;
1179 l_recipient_name := all_approvers(l_count).display_name;
1180
1181 end;
1182 wf_engine.SetItemAttrNumber (
1183 itemtype => itemtype
1184 ,itemkey => itemkey
1185 ,aname => 'CURRENT_APPROVER'
1186 ,avalue => l_count
1187 );
1188 wf_engine.SetItemAttrText (
1189 itemtype => itemtype
1190 ,itemkey => itemkey
1191 ,aname => 'RECIPIENT'
1192 ,avalue => l_recipient
1193 );
1194 wf_engine.SetItemAttrText (
1195 itemtype => itemtype
1196 ,itemkey => itemkey
1197 ,aname => 'RECIPIENT_NAME'
1198 ,avalue => l_recipient_name
1199 );
1200
1201 resultout := 'COMPLETE:Y';
1202 else
1203 resultout := 'COMPLETE:N';
1204 end if;
1205 --
1206 return;
1207 --
1208 elsif ( funcmode = 'CANCEL' ) then
1209 --
1210 null;
1211 --
1212 end if;
1213 --
1214 if g_debug then
1215 hr_utility.set_location('Leaving:'|| l_proc, 100);
1216 end if;
1217 --
1218 end process_fyi;
1219 --
1220 -- ------------------------------------------------------------------------
1221 -- |------------------------------< Copy_Tasks >--------------------------|
1222 -- ------------------------------------------------------------------------
1223 --
1224 PROCEDURE Copy_Tasks (p_from_ckl_id IN NUMBER
1225 ,p_to_alloc_ckl_id IN NUMBER
1226 ,p_task_owner_person_id IN NUMBER
1227 ) IS
1228 --
1229 l_proc VARCHAR2(50);
1230 l_alloc_task_id NUMBER;
1231 l_ovn NUMBER;
1232 l_target_end_date DATE;
1233 --
1234 CURSOR c_tasks(cp_checklist_id IN NUMBER) IS
1235 SELECT checklist_task_name
1236 ,description
1237 ,task_sequence
1238 ,mandatory_flag
1239 ,target_duration
1240 ,target_duration_uom
1241 ,action_url
1242 ,attribute_category
1243 ,attribute1
1244 ,attribute2
1245 ,attribute3
1246 ,attribute4
1247 ,attribute5
1248 ,attribute6
1249 ,attribute7
1250 ,attribute8
1251 ,attribute9
1252 ,attribute10
1253 ,attribute11
1254 ,attribute12
1255 ,attribute13
1256 ,attribute14
1257 ,attribute15
1258 ,attribute16
1259 ,attribute17
1260 ,attribute18
1261 ,attribute19
1262 ,attribute20
1263 ,information_category
1264 ,information1
1265 ,information2
1266 ,information3
1267 ,information4
1268 ,information5
1269 ,information6
1270 ,information7
1271 ,information8
1272 ,information9
1273 ,information10
1274 ,information11
1275 ,information12
1276 ,information13
1277 ,information14
1278 ,information15
1279 ,information16
1280 ,information17
1281 ,information18
1282 ,information19
1283 ,information20
1284 FROM per_tasks_in_checklist
1285 WHERE checklist_id = cp_checklist_id;
1286 --
1287 lr_tasks c_tasks%ROWTYPE;
1288 --
1289 BEGIN
1290 --
1291 l_proc:= 'per_checklist_events.copy_tasks';
1292 hr_utility.set_location('Entering: '|| l_proc, 10);
1293 --
1294 OPEN c_tasks(p_from_ckl_id);
1295 --
1296 LOOP
1297 FETCH c_tasks INTO lr_tasks;
1298 EXIT WHEN c_tasks%NOTFOUND;
1299 --
1300 -- Calculate target end date
1301 IF lr_tasks.target_duration IS NOT NULL AND lr_tasks.target_duration_uom IS NOT NULL THEN
1302 IF lr_tasks.target_duration_uom = 'D' THEN
1303 l_target_end_date := SYSDATE + lr_tasks.target_duration;
1304 ELSIF lr_tasks.target_duration_uom = 'W' THEN
1305 l_target_end_date := SYSDATE + (7 * lr_tasks.target_duration);
1306 ELSIF lr_tasks.target_duration_uom = 'M' THEN
1307 SELECT ADD_MONTHS(SYSDATE,lr_tasks.target_duration)
1308 INTO l_target_end_date FROM DUAL;
1309 END IF;
1310 ELSE
1311 l_target_end_date := NULL;
1312 END IF;
1313 --
1314 per_allocated_task_api.create_alloc_task
1315 (p_effective_date => SYSDATE
1316 ,p_allocated_checklist_id => p_to_alloc_ckl_id
1317 ,p_task_name => lr_tasks.checklist_task_name
1318 ,p_description => lr_tasks.description
1319 ,p_performer_orig_system => 'PER'
1320 ,p_task_owner_person_id => p_task_owner_person_id
1321 ,p_task_sequence => lr_tasks.task_sequence
1322 ,p_target_start_date => SYSDATE
1323 ,p_target_end_date => l_target_end_date
1324 ,p_action_url => lr_tasks.action_url
1325 ,p_mandatory_flag => lr_tasks.mandatory_flag
1326 ,p_status => 'INP'
1327 ,p_attribute_category => lr_tasks.attribute_category
1328 ,p_attribute1 => lr_tasks.attribute1
1329 ,p_attribute2 => lr_tasks.attribute2
1330 ,p_attribute3 => lr_tasks.attribute3
1331 ,p_attribute4 => lr_tasks.attribute4
1332 ,p_attribute5 => lr_tasks.attribute5
1333 ,p_attribute6 => lr_tasks.attribute6
1334 ,p_attribute7 => lr_tasks.attribute7
1335 ,p_attribute8 => lr_tasks.attribute8
1336 ,p_attribute9 => lr_tasks.attribute9
1337 ,p_attribute10 => lr_tasks.attribute10
1338 ,p_attribute11 => lr_tasks.attribute11
1339 ,p_attribute12 => lr_tasks.attribute12
1340 ,p_attribute13 => lr_tasks.attribute13
1341 ,p_attribute14 => lr_tasks.attribute14
1342 ,p_attribute15 => lr_tasks.attribute15
1343 ,p_attribute16 => lr_tasks.attribute16
1344 ,p_attribute17 => lr_tasks.attribute17
1345 ,p_attribute18 => lr_tasks.attribute18
1346 ,p_attribute19 => lr_tasks.attribute19
1347 ,p_attribute20 => lr_tasks.attribute20
1348 ,p_information_category => lr_tasks.information_category
1349 ,p_information1 => lr_tasks.information1
1350 ,p_information2 => lr_tasks.information2
1351 ,p_information3 => lr_tasks.information3
1352 ,p_information4 => lr_tasks.information4
1353 ,p_information5 => lr_tasks.information5
1354 ,p_information6 => lr_tasks.information6
1355 ,p_information7 => lr_tasks.information7
1356 ,p_information8 => lr_tasks.information8
1357 ,p_information9 => lr_tasks.information9
1358 ,p_information10 => lr_tasks.information10
1359 ,p_information11 => lr_tasks.information11
1360 ,p_information12 => lr_tasks.information12
1361 ,p_information13 => lr_tasks.information13
1362 ,p_information14 => lr_tasks.information14
1363 ,p_information15 => lr_tasks.information15
1364 ,p_information16 => lr_tasks.information16
1365 ,p_information17 => lr_tasks.information17
1366 ,p_information18 => lr_tasks.information18
1367 ,p_information19 => lr_tasks.information19
1368 ,p_information20 => lr_tasks.information20
1369 ,p_allocated_task_id => l_alloc_task_id
1370 ,p_object_version_number => l_ovn
1371 );
1372 END LOOP;
1373 --
1374 CLOSE c_tasks;
1375 --
1376 hr_utility.set_location('Leaving: '|| l_proc, 20);
1377 --
1378 EXCEPTION
1379 --
1380 WHEN OTHERS THEN
1381 --
1382 hr_utility.set_location('Leaving: '|| l_proc, 30);
1383 hr_utility.set_location(SQLERRM, 35);
1384 RAISE;
1385 --
1386 END Copy_Tasks;
1387 --
1388 -- ER 8861932 starts
1389 FUNCTION get_person_id (p_transaction_id IN VARCHAR2) RETURN NUMBER
1390 IS
1391 l_person_id per_people_f.person_id%type;
1392 BEGIN
1393 select substr(p_transaction_id,instr(p_transaction_id,'-')+1,length(p_transaction_id))
1394 INTO l_person_id FROM dual;
1395
1396 RETURN l_person_id ;
1397 END get_person_id ;
1398 --
1399 FUNCTION get_supervisor_id (p_transaction_id IN VARCHAR2) RETURN NUMBER
1400 IS
1401 l_person_id per_people_f.person_id%type;
1402 l_supervisor_id per_assignments_f.supervisor_id%type;
1403 l_effective_date per_ben_identified_events.effective_date%type;
1404 BEGIN
1405 select substr(p_transaction_id,instr(p_transaction_id,'-')+1,length(p_transaction_id))
1406 INTO l_person_id FROM dual;
1407 -- first get the effective_date from per_ben_identified_events table. we expect that there would be only one event with status 'PENDING'
1408 SELECT effective_date
1409 INTO l_effective_date
1410 FROM per_ben_identified_events
1411 WHERE status = 'Pending'
1412 AND person_id = l_person_id;
1413
1414 -- get supervisor_id from assignment table as of the effective_date
1415 SELECT supervisor_id INTO l_supervisor_id
1416 FROM per_assignments_f
1417 WHERE person_id = l_person_id
1418 AND l_effective_date BETWEEN effective_start_date AND effective_end_date
1419 AND assignment_type = 'E'
1420 AND primary_flag = 'Y';
1421
1422 RETURN l_supervisor_id ;
1423
1424 EXCEPTION
1425 WHEN NO_DATA_FOUND THEN
1426 -- supervisor does not exist on the event date
1427 l_supervisor_id := NULL ;
1428 RETURN l_supervisor_id;
1429 END get_supervisor_id ;
1430 ---
1431 FUNCTION get_ame_attribute_identifier (p_transaction_id IN VARCHAR2) RETURN VARCHAR2
1432 IS
1433 l_ame_attribute_identifier per_tasks_in_checklist.ame_attribute_identifier%type;
1434 BEGIN
1435 SELECT AME_ATTRIBUTE_IDENTIFIER INTO l_ame_attribute_identifier
1436 FROM PER_TASKS_IN_CHECKLIST
1437 WHERE task_in_checklist_id= substr(p_transaction_id,1,instr(p_transaction_id,'-')-1);
1438
1439 RETURN l_ame_attribute_identifier ;
1440
1441 END get_ame_attribute_identifier ;
1442 ---
1443 -- ER 8861932 ends
1444 --
1445 end per_checklist_events;