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