1 Package Body HXC_APPROVAL_WF_HELPER as
2 /* $Header: hxcaprwfhelper.pkb 120.12.12010000.2 2008/08/05 12:00:08 ubhat ship $ */
3 g_pkg constant varchar2(30) := 'hxc_approval_wf_helper.';
4 g_debug boolean :=hr_utility.debug_enabled;
5
6 -- This will determine the error admin role
7 FUNCTION find_admin_role(
8 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
9 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
10 return hxc_approval_styles.admin_role%type
11 is
12
13 l_admin_role hxc_approval_styles.admin_role%type;
14 l_proc constant varchar2(61) := g_pkg ||'find_admin_role';
15
16 CURSOR c_admin_role(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
17 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
18 is
19 SELECT
20 has.admin_role
21 FROM
22 hxc_time_building_blocks htbb,
23 hxc_approval_styles has
24 WHERE
25 htbb.time_building_block_id = p_timecard_id and
26 htbb.object_version_number = p_timecard_ovn and
27 htbb.approval_style_id = has.approval_style_id;
28
29
30 CURSOR c_get_admin
31 is
32 select text
33 from wf_resources
34 where name = 'WF_ADMIN_ROLE';
35
36 BEGIN
37
38 if g_debug then
39 hr_utility.set_location(l_proc, 10);
40 end if;
41
42 open c_admin_role(p_timecard_id,p_timecard_ovn);
43 fetch c_admin_role into l_admin_role;
44
45 if l_admin_role is null then
46 --if OTL Admin Role is not found i.e. if user doen't select any OTL Workflow adminstrator in the form
47 --then we shall return Workflow Administrator.
48 if g_debug then
49 hr_utility.set_location(l_proc, 20);
50 end if;
51
52 open c_get_admin;
53 fetch c_get_admin into l_admin_role;
54 close c_get_admin;
55
56 END if;
57
58 close c_admin_role;
59
60 if g_debug then
61 hr_utility.set_location(l_proc, 30);
62 end if;
63
64 return l_admin_role;
65
66 END find_admin_role;
67 --------------------------------------------------------------------------------------
68
69 -- This will determine the error admin role
70
71 FUNCTION find_error_admin_role(
72 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
73 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
74 return hxc_approval_styles.error_admin_role%type
75 is
76
77 l_error_admin_role hxc_approval_styles.error_admin_role%type;
78 l_proc constant varchar2(61) := g_pkg ||'find_error_admin_role';
79
80 CURSOR c_error_admin_role(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
81 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
82 is
83 SELECT
84 has.error_admin_role
85 FROM
86 hxc_time_building_blocks htbb,
87 hxc_approval_styles has
88 WHERE
89 htbb.time_building_block_id = p_timecard_id and
90 htbb.object_version_number = p_timecard_ovn and
91 htbb.approval_style_id = has.approval_style_id;
92
93 CURSOR c_get_admin
94 is
95 select text
96 from wf_resources
97 where name = 'WF_ADMIN_ROLE';
98
99 BEGIN
100
101 if g_debug then
102 hr_utility.set_location(l_proc, 10);
103 end if;
104
105 open c_error_admin_role(p_timecard_id,p_timecard_ovn);
106 fetch c_error_admin_role into l_error_admin_role;
107
108 if l_error_admin_role is null then
109 --if OTL Error Admin Role is not found i.e. if user doen't select any OTL Error Workflow adminstrator in
110 --the form then we shall return Workflow Administrator.
111
112 if g_debug then
113 hr_utility.set_location(l_proc, 20);
114 end if;
115 open c_get_admin;
116 fetch c_get_admin into l_error_admin_role;
117 close c_get_admin;
118 END if;
119
120 close c_error_admin_role;
121
122 if g_debug then
123 hr_utility.set_location(l_proc, 30);
124 end if;
125 return l_error_admin_role;
126
127 END find_error_admin_role;
128 ----------------------------------------------------------------------------------------------
129 FUNCTION find_preparer_role(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
130 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
131 return wf_local_roles.name%type
132 is
133
134 l_user_id number;
135 l_person_id number;
136 l_name wf_local_roles.name%type;
137 l_display_name wf_local_roles.display_name%type;
138 l_proc constant varchar2(61) := g_pkg ||'find_preparer_role';
139
140 CURSOR c_get_user_id(p_user_id IN number)
141 is
142 select employee_id
143 from fnd_user
144 where user_id = p_user_id;
145
146 --Bug 5370557
147 --Since for a blank timecard there are no detail blocks hence we need to fetch the preparer role from
148 --the most recent block, it can be TIMECARD,DAY or DETAIL.
149 CURSOR c_latest_detail_block(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
150 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
151 is
152 select distinct last_updated_by
153 from hxc_time_building_blocks
154 where last_update_date = (select max(last_update_date)
155 from HXC_TIME_BUILDING_BLOCKS
156 where date_to=hr_general.END_of_time
157 connect by prior time_building_block_id=parent_building_block_id
158 start with time_building_block_id=p_timecard_id)
159 and date_to=hr_general.END_of_time
160 connect by prior time_building_block_id=parent_building_block_id
161 start with time_building_block_id=p_timecard_id;
162
163 BEGIN
164 g_debug:=hr_utility.debug_enabled;
165 if g_debug then
166 hr_utility.set_location(l_proc, 10);
167 end if;
168 --The most recent detail building block should be found for the timecard, and the user id on the WHO column for
169 --that detail used to subsequently pass to the workflow directory service, getrolename, PROCEDURE to obtain the
170 --appropriate role
171 open c_latest_detail_block(p_timecard_id,p_timecard_ovn);
172 fetch c_latest_detail_block into l_user_id;
173 close c_latest_detail_block;
174
175 --Get role form FND system
176 wf_directory.getrolename( p_orig_system => 'FND_USR',
177 p_orig_system_id => l_user_id,
178 p_name => l_name,
179 p_display_name => l_display_name);
180
181 if l_name is NULL and l_user_id is not NULL then
182
183 --If the role name can not be found from the user id, original system is FND, then we should look up the person
184 --id associated with the user id from the FND_USER record, and try finding the role using the PER orginal system.
185 if g_debug then
186 hr_utility.set_location(l_proc, 20);
187 end if;
188
189 open c_get_user_id(l_user_id);
190 fetch c_get_user_id into l_person_id;
191 close c_get_user_id;
192 wf_directory.getrolename( p_orig_system => 'PER',
193 p_orig_system_id => l_person_id,
194 p_name => l_name,
195 p_display_name => l_display_name);
196 end if;
197
198 if g_debug then
199 hr_utility.set_location(l_proc, 30);
200 end if;
201 return l_name;
202
203 END find_preparer_role;
204
205 -------------------------------------------------------------------------------------------------
206 FUNCTION find_full_name_from_role(p_role_name in wf_local_roles.name%type,
207 p_effective_date in date)
208 return varchar2
209 is
210
211 l_system_id wf_local_roles.orig_system_id%type;
212 l_original_system wf_local_roles.orig_system%type;
213 l_full_name per_all_people_f.full_name%type;
214 l_display_name wf_local_roles.display_name%type;
215 l_proc constant varchar2(61) := g_pkg ||'find_full_name_from_role';
216
217 CURSOR c_get_original_system(p_role_name in wf_local_roles.name%type)
218 is
219 select orig_system,orig_system_id,display_name
220 from wf_local_roles
221 where name= p_role_name;
222
223 CURSOR c_get_name_ppf(p_system_id in wf_local_roles.orig_system_id%type,
224 p_effective_date in date)
225 is
226 select full_name
227 from per_all_people_f
228 where person_id = p_system_id
229 and p_effective_date between effective_start_date and effective_end_date;
230
231
232 BEGIN
233
234 g_debug:=hr_utility.debug_enabled;
235 if g_debug then
236 hr_utility.set_location(l_proc, 10);
237 end if;
238 --Find what is the Original system i.e. PER/FND
239 open c_get_original_system(p_role_name);
240 fetch c_get_original_system into l_original_system,l_system_id,l_display_name;
241 close c_get_original_system;
242
243 if(l_original_system = 'PER') then
244
245 if g_debug then
246 hr_utility.set_location(l_proc, 20);
247 end if;
248 --If the original system is PER then we can do a direct query against PER_ALL_PEOPLE_F to fetch the full name.
249 open c_get_name_ppf(l_system_id, p_effective_date);
250 fetch c_get_name_ppf into l_full_name;
251 close c_get_name_ppf;
252 END if;
253
254 if(l_original_system = 'FND_USR') then
255
256 if g_debug then
257 hr_utility.set_location(l_proc, 30);
258 end if;
259
260 l_full_name := l_display_name;
261 END if;
262
263 return l_full_name;
264
265 END find_full_name_from_role;
266
267 --------------------------------------------------------------------------------------------------------
268 FUNCTION find_supervisor_role(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
269 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
270 return wf_local_roles.name%type
271 is
272
273 l_supervisor_id per_all_assignments_f.supervisor_id%type;
274 l_name wf_local_roles.name%type;
275 l_display_name wf_local_roles.display_name%type;
276 l_proc constant varchar2(61) := g_pkg ||'find_supervisor_role';
277
278 CURSOR c_get_supervisor(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
279 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
280 is
281 select supervisor_id
282 from per_all_assignments_f
283 where person_id = (select resource_id
284 from hxc_time_building_blocks
285 where time_building_block_id=p_timecard_id
286 and object_version_number =p_timecard_ovn)
287 and assignment_type in ('E','C')
288 and primary_flag = 'Y'
289 and sysdate between effective_start_date and effective_END_Date;
290
291 BEGIN
292 if g_debug then
293 hr_utility.set_location(l_proc, 10);
294 end if;
295 --This FUNCTION uses the HXC_TIME_BUILDING_BLOCKS table and PER_ALL_ASSIGNMENTS_F to find the supervisor
296 --corresponding to the primary employee or contingent worker assignment, and then subsequently use that
297 --supervisor id to find the appropriate role from the workflow directory service using the PER original system.
298
299 open c_get_supervisor(p_timecard_id,p_timecard_ovn);
300 fetch c_get_supervisor into l_supervisor_id;
301 close c_get_supervisor;
302
303 wf_directory.getrolename( p_orig_system => 'PER',
304 p_orig_system_id => l_supervisor_id,
305 p_name => l_name,
306 p_display_name => l_display_name);
307
308 return l_name;
309
310 END find_supervisor_role;
311 -----------------------------------------------------------------------------------------------------
312 FUNCTION find_worker_role(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
313 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
314 return wf_local_roles.name%type
315 is
316
317 l_worker_id hxc_time_building_blocks.resource_id%TYPE;
318 l_name wf_local_roles.name%type;
319 l_display_name wf_local_roles.display_name%type;
320 l_proc constant varchar2(61) := g_pkg ||'find_worker_role';
321
322
323 CURSOR c_get_worker_id(p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
324 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE )
325 is
326 select resource_id
327 from hxc_time_building_blocks
328 where time_building_block_id=p_timecard_id
329 and object_version_number =p_timecard_ovn;
330
331
332 BEGIN
333 if g_debug then
334 hr_utility.set_location(l_proc, 10);
335 end if;
336 --This is a private FUNCTION, which is passed the timecard id and object version number. This uses
337 --HXC_TIME_BUILDING_BLOCKS to locate the resource id associated with the timecard, and from there uses the
338 --workflow directory service getrolename call to obtain the role associated with the user, using the PER original system.
339
340 open c_get_worker_id(p_timecard_id,p_timecard_ovn);
341 fetch c_get_worker_id into l_worker_id;
342 close c_get_worker_id;
343
344 wf_directory.getrolename( p_orig_system => 'PER',
345 p_orig_system_id => l_worker_id,
346 p_name => l_name,
347 p_display_name => l_display_name);
348
349 if g_debug then
350 hr_utility.set_location(l_proc, 20);
351 end if;
352
353 return l_name;
354
355 END find_worker_role;
356
357 -----------------------------------------------------------------------------------------------------
358 FUNCTION find_role_for_recipient
359 (p_recipient_code in wf_item_attribute_values.text_value%type,
360 p_timecard_id in number,
361 p_timecard_ovn in number)
362 Return wf_local_roles.name%type
363
364 is
365 l_role wf_local_roles.name%type;
366 l_proc constant varchar2(61) := g_pkg ||'find_role_for_recipient';
367
368 BEGIN
369 g_debug:=hr_utility.debug_enabled;
370 if g_debug then
371 hr_utility.set_location(l_proc, 10);
372 end if;
373 --This FUNCTION is essentially a switch on the recipient code, to call one of the private specific FUNCTIONs for
374 --that role, i.e. if recipient code is supervisor then fetch c_the role by calling find_supervisor_role etc.
375
376 if(p_recipient_code = hxc_app_comp_notifications_api.c_recipient_admin) then
377
378 if g_debug then
379 hr_utility.set_location(l_proc, 20);
380 end if;
381
382 l_role := find_admin_role(p_timecard_id,p_timecard_ovn);
383
384 elsif ( p_recipient_code = hxc_app_comp_notifications_api.c_recipient_error_admin) then
385
386 if g_debug then
387 hr_utility.set_location(l_proc, 30);
388 end if;
389
390 l_role :=find_error_admin_role(p_timecard_id,p_timecard_ovn);
391
392 elsif (p_recipient_code = hxc_app_comp_notifications_api.c_recipient_preparer) then
393
394 if g_debug then
395 hr_utility.set_location(l_proc, 40);
396 end if;
397
398 l_role :=find_preparer_role(p_timecard_id,p_timecard_ovn);
399
400 elsif(p_recipient_code = hxc_app_comp_notifications_api.c_recipient_supervisor) then
401
402 if g_debug then
403 hr_utility.set_location(l_proc, 50);
404 end if;
405
406 l_role :=find_supervisor_role(p_timecard_id,p_timecard_ovn);
407 elsif (p_recipient_code = hxc_app_comp_notifications_api.c_recipient_worker) then
408
409 if g_debug then
410 hr_utility.set_location(l_proc, 60);
411 end if;
412 l_role :=find_worker_role(p_timecard_id,p_timecard_ovn);
413 END if;
414
415 return l_role;
416
417 END find_role_for_recipient;
418
419 -----------------------------------------------------------------------------------------------------
420
421 --This helper FUNCTION returns true if the approver resource (person) id passed is the same as the supervisor id
422 --on the passed worker resource id this FUNCTION returns true, otherwise it returns false
423
424 FUNCTION is_approver_supervisor
425 (p_approver_resource_id in number,
426 p_resource_id in number)
427 Return Boolean is
428 l_supervisor_id per_all_assignments_f.supervisor_id%type;
429 l_proc constant varchar2(61) := g_pkg ||'is_approver_supervisor';
430
431 Cursor get_supervisor(p_resource_id number)
432 Is
433 select supervisor_id
434 from per_all_assignments_f
435 where person_id = p_resource_id
436 and assignment_type in ('E','C')
437 and primary_flag = 'Y'
438 and sysdate between effective_start_date and effective_END_Date;
439
440 BEGIN
441 g_debug:=hr_utility.debug_enabled;
442
443 open get_supervisor(p_resource_id);
444 fetch get_supervisor into l_supervisor_id;
445 close get_supervisor;
446
447 if(l_supervisor_id = p_approver_resource_id) then
448 if g_debug then
449 hr_utility.set_location(l_proc, 10);
450 end if;
451 return true;
452 else
453 if g_debug then
454 hr_utility.set_location(l_proc, 20);
455 end if;
456 return false;
457 END if;
458
459 END is_approver_supervisor;
460 -----------------------------------------------------------------------------------------------------------
461
462 PROCEDURE get_notif_attribute_values
463 (p_item_type in wf_items.item_type%type,
464 p_item_key in wf_item_activity_statuses.item_key%type,
465 p_app_bb_id in number,
466 p_notif_action_code out nocopy varchar2,
467 p_notif_recipient_code out nocopy varchar2,
468 p_approval_comp_id out nocopy number,
469 p_can_notify out nocopy boolean)
470 is
471
472 l_approval_comp_id number;
473 p_tc_bbid number;
474 p_tc_bbovn number;
475 l_proc constant varchar2(61) := g_pkg ||'get_notif_attribute_values';
476
477
478 CURSOR c_get_app_comp_id(p_app_bb_id hxc_time_building_blocks.time_building_block_id%TYPE)
479 is
480 select approval_comp_id
481 from hxc_app_period_summary
482 where application_period_id = p_app_bb_id;
483
484 CURSOR c_get_any_comp_id(p_app_bb_id hxc_time_building_blocks.time_building_block_id%TYPE)
485 is
486 select happs.approval_comp_id
487 from hxc_tc_ap_links htal, hxc_app_period_summary happs
488 where htal.timecard_id = p_app_bb_id
489 and happs.application_period_id = htal.application_period_id
490 and rownum <2;
491
492 cursor c_app_comp_pm(p_bb_id number,p_bb_ovn number)
493 is
494 select hac.approval_comp_id
495 from hxc_approval_comps hac,
496 hxc_approval_styles has,
497 hxc_time_building_blocks htb
498 where htb.time_building_block_id =p_bb_id
499 and htb.object_version_number = p_bb_ovn
500 and htb.approval_style_id = has.approval_style_id
501 and has.approval_style_id = hac.APPROVAL_STYLE_ID
502 and hac.approval_mechanism = 'PROJECT_MANAGER'
503 and hac.parent_comp_id is null
504 and hac.parent_comp_ovn is null;
505
506
507
508 BEGIN
509 g_debug:=hr_utility.debug_enabled;
510 if g_debug then
511 hr_utility.set_location(l_proc, 10);
512 end if;
513
514 --The values for the recipient action code and recipient code are obtained via the safe method.
515
516 p_notif_action_code := wf_engine.getitemattrtext
517 (itemtype => p_item_type ,
518 itemkey => p_item_key ,
519 aname => hxc_approval_wf_helper.c_action_code_attribute,
520 ignore_notfound => true);
521
522 p_notif_recipient_code := wf_engine.getitemattrtext
523 (itemtype => p_item_type,
524 itemkey => p_item_key ,
525 aname => hxc_approval_wf_helper.c_recipient_code_attribute,
526 ignore_notfound => true);
527
528 --The approval component id is derived from a CURSOR c_based on the passed application building block id.
529
530 if p_notif_action_code = 'SUBMISSION' and p_notif_recipient_code = 'WORKER' then
531
532 --In the case of the 'WORKER' recipient code, and the 'SUBMISSION' action code, the value passed in the
533 --application building block id parameter will actually correspond to a timecard id. In this case, the
534 --application period will have been created, but the item attribute value has yet to be created. We use a
535 --different CURSOR in this case to find any approval component associated with the application periods which
536 --have been created as part of this submission.
537
538 if g_debug then
539 hr_utility.set_location(l_proc, 20);
540 end if;
541 open c_get_any_comp_id(p_app_bb_id);
542 fetch c_get_any_comp_id into l_approval_comp_id;
543 close c_get_any_comp_id;
544 else
545
546 if g_debug then
547 hr_utility.set_location(l_proc, 30);
548 end if;
549 open c_get_app_comp_id(p_app_bb_id);
550 fetch c_get_app_comp_id into l_approval_comp_id;
551 close c_get_app_comp_id;
552
553 END if;
554
555 p_tc_bbid:= wf_engine.GetItemAttrNumber
556 (itemtype => p_item_type,
557 itemkey => p_item_key,
558 aname => 'TC_BLD_BLK_ID');
559 p_tc_bbovn:= wf_engine.GetItemAttrNumber
560 (itemtype => p_item_type,
561 itemkey => p_item_key,
562 aname => 'TC_BLD_BLK_OVN');
563
564
565 p_approval_comp_id :=l_approval_comp_id;
566
567 open c_app_comp_pm(p_tc_bbid,p_tc_bbovn);
568 fetch c_app_comp_pm into p_approval_comp_id;
569 close c_app_comp_pm;
570
571 --The return boolean, p_can_notify, is set to true by default, and is set to false if any of the action code,
572 --recipient code or approval component id return parameters are null.
573
574 --For the existing workflow action and recipient code will be null and this condition fails and no notification.
575
576 if(p_notif_action_code is null OR p_notif_recipient_code is null OR p_approval_comp_id is null)
577 then
578 if g_debug then
579 hr_utility.set_location(l_proc, 40);
580 end if;
581
582 p_can_notify :=false;
583 else
584 if g_debug then
585 hr_utility.set_location(l_proc, 50);
586 end if;
587
588 p_can_notify :=true;
589 END if;
590
591
592 END get_notif_attribute_values;
593
594 ---------------------------------------------------------------------------------------------------------------
595 --This FUNCTION simply checks that the item attribute value name passed exists for the supplied item type and item key
596 FUNCTION item_attribute_value_exists
597 (p_item_type in wf_items.item_type%type,
598 p_item_key in wf_item_activity_statuses.item_key%type,
599 p_name in wf_item_attribute_values.name%type)
600 return boolean is
601
602 l_dummy varchar2(1);
603
604 BEGIN
605
606 select 'Y'
607 into l_dummy
608 from wf_item_attribute_values
609 where item_type = p_item_type
610 and item_key = p_item_key
611 and name = p_name;
612
613 return true;
614
615 Exception
616 When others then
617 return false;
618
619 END item_attribute_value_exists;
620 ----------------------------------------------------------------------------------------------------------------
621 --This is a simple FUNCTION, which given the recipient and action codes works out whether to send a notification
622 --with the timecard details, or just a text-based FYI notification. Currently all notifications include the
623 --timecard details, with the exception of the 'Notification to timecard preparer that the timecard is
624 --approved' - with action codes APPROVED and recipient code PREPARER, these do not contain the details.
625 FUNCTION notification_with_details
626 (p_notif_action_code in varchar2,
627 p_notif_recipient_code in varchar2)
628 return boolean is
629
630 BEGIN
631
632 if(p_notif_action_code = hxc_app_comp_notifications_api.c_action_approved
633 AND p_notif_recipient_code = hxc_app_comp_notifications_api.c_recipient_preparer)
634 then
635 return false;
636 else
637 return true;
638 END if;
639
640 END notification_with_details;
641 ---------------------------------------------------------------------------------------------------------------
642 PROCEDURE set_notif_attribute_values
643 (p_item_type in wf_items.item_type%type,
644 p_item_key in wf_item_activity_statuses.item_key%type,
645 p_notif_action_code in wf_item_attribute_values.text_value%type,
646 p_notif_recipient_code in wf_item_attribute_values.text_value%type)
647
648 is
649
650 BEGIN
651 g_debug:=hr_utility.debug_enabled;
652
653 --run check PROCEDURE to validate the passed parameters
654
655 hxc_han_bus.chk_notification_action_code(p_notif_action_code);
656
657 hxc_han_bus.chk_notification_recip_code(p_notif_recipient_code);
658
659 --Add via safe method..
660 --I.e. for each of the two codes, it checks to see if they exist first, and if so, simply set the right value,
661 --if not, create them dynamically setting the value at the time. Again this helps ensure that any existing
662 --process does not fail.
663
664 if(item_attribute_value_exists(p_item_type,p_item_key,c_action_code_attribute)) then
665
666 wf_engine.setitemattrtext
667 (itemtype => p_item_type,
668 itemkey => p_item_key,
669 aname => c_action_code_attribute,
670 avalue => p_notif_action_code
671 );
672
673 else
674
675 wf_engine.additemattr
676 (itemtype => p_item_type,
677 itemkey => p_item_key,
678 aname =>c_action_code_attribute,
679 text_value => p_notif_action_code
680 );
681
682 END if;
683
684 if(item_attribute_value_exists(p_item_type,p_item_key,c_recipient_code_attribute)) then
685
686 wf_engine.setitemattrtext
687 (itemtype => p_item_type,
688 itemkey => p_item_key,
689 aname => c_recipient_code_attribute,
690 avalue => p_notif_recipient_code
691 );
692
693 else
694
695 wf_engine.additemattr
696 (itemtype => p_item_type,
697 itemkey => p_item_key,
698 aname => c_recipient_code_attribute,
699 text_value => p_notif_recipient_code
700 );
701
702 END if;
703
704 END set_notif_attribute_values;
705
706 FUNCTION send_notification
707 (p_approval_comp_id in number,
708 p_action_code in wf_item_attribute_values.text_value%type,
709 p_recipient_code in wf_item_attribute_values.text_value%type,
710 p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
711 p_timecard_ovn in hxc_time_building_blocks.object_version_number%type,
712 p_app_period_id in hxc_time_building_blocks.time_building_block_id%type,
713 p_app_period_ovn in hxc_time_building_blocks.object_version_number%type)
714
715 Return boolean is
716
717
718 l_result boolean;
719 l_dummy varchar2(1) := 'N';
720 l_app_bb_id hxc_time_building_blocks.time_building_block_id%type;
721 l_timecard_fyi varchar2(1000);
722 l_item_key wf_items.item_key%type;
723 l_tc_url varchar2(1000);
724 l_block_index PLS_INTEGER := 1;
725 l_tk_audit_item_key hxc_timecard_summary.tk_audit_item_key%type;
726 l_resource_id hxc_time_building_blocks.resource_id%type;
727 l_recipient_action_code wf_item_attribute_values.text_value%type;
728 l_action_code wf_item_attribute_values.text_value%type;
729 l_notification_id wf_notifications.notification_id%type;
730 l_recipient_role wf_local_roles.name%type;
731 l_app_mech varchar2(30);
732 l_app_start_date date;
733 l_app_end_date date;
734 l_tc_start_date date;
735 l_tc_end_date date;
736 l_proc constant varchar2(61) := g_pkg ||'send_notification';
737
738
739 CURSOR c_is_notification_exists(p_approval_comp_id in number,
740 p_action_code in wf_item_attribute_values.text_value%type,
741 p_retrieval_code in wf_item_attribute_values.text_value%type)
742 is
743 select 'Y'
744 from hxc_app_comp_notifications hacn,
745 hxc_app_comp_notif_usages hacnu
746 where hacnu.comp_notification_id = hacn.comp_notification_id
747 and hacnu.comp_notification_ovn = hacn.object_version_number
748 and hacnu.approval_comp_id = p_approval_comp_id
749 and hacn.notification_action_code = p_action_code
750 and hacn.notification_recipient_code = p_recipient_code
751 and hacnu.enabled_flag = 'Y';
752
753
754
755 CURSOR c_get_detail_blocks(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type)
756 is
757 select adl.time_building_block_id,
758 adl.time_building_block_ovn
759 from hxc_ap_detail_links adl
760 where adl.application_period_id = p_application_period_id;
761
762
763 CURSOR c_get_notifications(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
764 ,p_recipient_role in wf_local_roles.name%type)
765 is
766 select wias.item_key,
767 wn.notification_id,
768 wna3.text_value bb_id
769 from wf_notifications wn,
770 wf_notification_attributes wna1,
771 wf_notification_attributes wna2,
772 wf_notification_attributes wna3,
773 wf_item_activity_statuses wias
774 where wn.message_name = 'HXC_GENERIC_NOTIF_MESSAGE'
775 and wn.message_type = 'HXCEMP'
776 and (wn.recipient_role = p_recipient_role OR wn.original_recipient = p_recipient_role)
777 and wna1.notification_id = wn.notification_id
778 and wna2.notification_id = wn.notification_id
779 and wna3.notification_id = wn.notification_id
780 and wna1.name = 'FYI_ACTION_CODE'
781 and wna1.text_value = p_action_code
782 and wna2.name = 'FYI_RECIPIENT_CODE'
783 and wna2.text_value = p_recipient_code
784 and wna3.name = 'APP_BB_ID'
785 and wn.notification_id = wias.notification_id
786 and wias.item_type = 'HXCEMP'
787 and wias.item_key in (
788 select ts.approval_item_key item_key
789 from hxc_timecard_summary ts
790 where ts.timecard_id = p_timecard_id
791 union
792 select aps.approval_item_key item_key
793 from hxc_tc_ap_links tcl,
794 hxc_app_period_summary aps
795 where tcl.timecard_id = p_timecard_id
796 and tcl.application_period_id = aps.application_period_id
797
798 );
799
800 CURSOR c_chk_auto_approve(p_app_bb_id in number)
801 is
802
803 select d.approval_mechanism
804 from hxc_ap_detail_links a,
805 hxc_latest_details b,
806 hxc_app_period_summary c,
807 hxc_approval_comps d
808 where a.time_building_block_id in (select time_building_block_id
809 from hxc_ap_detail_links
810 where application_period_id =p_app_period_id)
811 and a.application_period_id <>p_app_period_id
812 and b.time_building_block_id = a.time_building_block_id
813 and b.object_version_number = a.time_building_block_ovn
814 and c.application_period_id = a.application_period_id
815 and c.approval_comp_id = d.approval_comp_id
816 and d.approval_mechanism <>'AUTO_APPROVE';
817
818 CURSOR c_get_app_dates(p_app_bb_id in number)
819 is
820 select start_time,stop_time
821 from hxc_app_period_summary
822 where application_period_id = p_app_bb_id;
823
824 CURSOR c_get_tc_dates(p_timecard_id in number)
825 is
826 select start_time,stop_time
827 from hxc_timecard_summary
828 where timecard_id = p_timecard_id;
829
830
831 type rec_type is record(p_id hxc_time_building_blocks.time_building_block_id%TYPE,
832 p_ovn hxc_time_building_blocks.time_building_block_id%TYPE);
833
834
835 TYPE tab_type IS TABLE OF rec_type INDEX BY BINARY_INTEGER;
836
837 l_tab_type_a tab_type;
838 l_tab_type_b tab_type;
839
840
841
842 BEGIN
843 if g_debug then
844 hr_utility.set_location(l_proc, 10);
845 end if;
846
847 open c_is_notification_exists(p_approval_comp_id,p_action_code,p_recipient_code);
848 fetch c_is_notification_exists into l_dummy;
849 close c_is_notification_exists;
850
851 --This PROCEDURE looks up whether for this approval component id there exists an enabled usage for the passed
852 --action and recipient codes. If one does not exist, it returns false.
853 --If the timecard preparer chooses to send the approval notification to the next approver, we will not be having
854 --any row in the notification for this TRANSFER-PREPARER case since there is no check box on the form for this
855 --notification, hence we need to bypass the check for the notification check.
856
857 --Notify supervisor if the notification is sent to next approver
858 if p_action_code = 'TRANSFER' and p_recipient_code = 'PREPARER' then
859 l_dummy := 'Y';
860 end if;
861
862 if l_dummy <> 'Y' then
863 return false;
864 else
865 --If the notification does exist, then we need to check if this notification has already been sent for
866 --the set of details which would sent in this notification.
867
868 if g_debug then
869 hr_utility.set_location(l_proc, 20);
870 end if;
871
872 l_recipient_role :=find_role_for_recipient(p_recipient_code,p_timecard_id,p_timecard_ovn);
873
874
875 --Now get all the notifications that are sent for this timecard
876
877 open c_get_notifications(p_timecard_id,l_recipient_role);
878 fetch c_get_notifications into l_item_key,l_notification_id,l_app_bb_id;
879 if(c_get_notifications%found) then
880
881 if g_debug then
882 hr_utility.set_location(l_proc, 30);
883 end if;
884
885 open c_get_app_dates(p_app_period_id);
886 fetch c_get_app_dates into l_app_start_date,l_app_end_date;
887 close c_get_app_dates;
888
889 open c_get_tc_dates(p_timecard_id);
890 fetch c_get_tc_dates into l_tc_start_date,l_tc_end_date;
891 close c_get_tc_dates;
892 --For the 'Notify supervisor on approval request' notification, since we are sending only once
893 --return false if we found out that notification has been sent.
894 if p_action_code = hxc_app_comp_notifications_api.c_action_request_approval
895 and p_recipient_code=hxc_app_comp_notifications_api.c_recipient_supervisor
896 and trunc(l_tc_end_date - l_tc_start_date) <= (l_app_end_date - l_app_start_date) then
897
898 if g_debug then
899 hr_utility.set_location(l_proc, 40);
900 end if;
901
902 return false;
903
904 else
905
906 if g_debug then
907 hr_utility.set_location(l_proc, 50);
908 end if;
909
910 --fetch c_detail blocks for current app_bb_id;
911 open c_get_detail_blocks(p_app_period_id);
912 fetch c_get_detail_blocks bulk collect INTO l_tab_type_a; --l_tab_type.p_id,l_tab_type.p_ovn ;
913 close c_get_detail_blocks;
914
915 --fetch c_detail blocks for the app_bb_id found in the CURSOR c_get_notifications
916
917 open c_get_detail_blocks(l_app_bb_id);
918 fetch c_get_detail_blocks bulk collect INTO l_tab_type_b;
919 close c_get_detail_blocks;
920 --Now compare the detail blocks attached with the app_bb_ids. If there exists same
921 --detail blocks for these two application periods then that means notification has
922 --aldready been sent with the detail and we should not resend the notification with same details.
923 if(l_tab_type_a.COUNT = l_tab_type_b.COUNT) then
924
925 loop
926 exit when l_block_index > l_tab_type_a.COUNT;
927
928 if((l_tab_type_a(l_block_index).p_id
929 = l_tab_type_b(l_block_index).p_id)
930 AND
931 (l_tab_type_a(l_block_index).p_ovn
932 = l_tab_type_b(l_block_index).p_ovn)) then
933
934 l_result := false;
935
936 else
937 l_result := true;
938 exit;
939 END if;
940
941 l_block_index := l_block_index + 1;
942 END LOOP;
943
944
945 else
946 if g_debug then
947 hr_utility.set_location(l_proc, 60);
948 end if;
949
950 l_result := true;
951 END if;-- if(l_tab_type_a.COUNT = l_tab_type_b.COUNT)
952 END IF;--if p_action_code = 'SUBMISSION' and p_recipient_code='SUPERVISOR' then
953 else
954
955 if g_debug then
956 hr_utility.set_location(l_proc, 70);
957 end if;
958
959 l_result := true;
960
961 END if;--if(get_notifications%found)
962
963 close c_get_notifications;
964
965 END if;--if(is_notification_exists%notfound)
966
967
968 --check for notify worker on timecard submission for TK
969
970 if(p_action_code = hxc_app_comp_notifications_api.c_action_submission
971 and p_recipient_code=hxc_app_comp_notifications_api.c_recipient_worker and l_result = true) then
972
973 if g_debug then
974 hr_utility.set_location(l_proc, 80);
975 end if;
976
977 select tk_audit_item_key
978 into l_tk_audit_item_key
979 from hxc_timecard_summary
980 where timecard_id= p_timecard_id;
981
982 if(l_tk_audit_item_key is not null) then
983 hr_utility.trace('TK itemkey '||l_tk_audit_item_key);
984 l_result := false;
985 END if;
986
987 END if;
988 --check for notify worker on auto approval
989
990 if(p_action_code = hxc_app_comp_notifications_api.c_action_auto_approve and
991 p_recipient_code=hxc_app_comp_notifications_api.c_recipient_worker and l_result = true) then
992
993 if g_debug then
994 hr_utility.set_location(l_proc, 90);
995 end if;
996
997 open c_chk_auto_approve(p_app_period_id);
998 fetch c_chk_auto_approve into l_app_mech;
999
1000 if(c_chk_auto_approve%found) then
1001 --we cannot send auto-approve notification to worker as the same detail block is attached to another
1002 --application period which does not have auto-approval mechanism.
1003
1004 l_result := false;
1005 end if;
1006
1007 close c_chk_auto_approve;
1008 end if;
1009
1010 -- If it passes all the tests above then return true.
1011
1012 return l_result;
1013
1014 END send_notification;
1015 ------------------------------------------------------------------------------------------------------------
1016 PROCEDURE prepare_notification(
1017 itemtype IN varchar2,
1018 itemkey IN varchar2,
1019 actid IN number,
1020 funcmode IN varchar2,
1021 result IN OUT NOCOPY varchar2)
1022 is
1023
1024 l_app_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
1025 l_app_bb_ovn hxc_time_building_blocks.time_building_block_id%TYPE;
1026 l_action_code varchar2(30);
1027 l_recipient_code varchar2(30);
1028 l_approval_comp_id number;
1029 l_can_notify boolean;
1030 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1031 l_timecard_ovn hxc_time_building_blocks.time_building_block_id%TYPE;
1032 l_tc_from_role wf_local_roles.name%type;
1033 l_tc_to_role wf_local_roles.name%type;
1034 l_title varchar2(4000);
1035 l_total_hours number ;
1036 l_premium_hours number ;
1037 l_non_worked_hours number ;
1038 l_worker_role wf_local_roles.name%type;
1039 l_worker_full_name per_all_people_f.full_name%type;
1040 l_preparer_role wf_local_roles.name%type;
1041 l_preparer_full_name per_all_people_f.full_name%type;
1042 l_fyi_subject varchar2(4000);
1043 l_description varchar2(4000);
1044 l_tc_url varchar2(1000);
1045 l_resource_id hxc_time_building_blocks.resource_id%type;
1046 l_effective_END_date date;
1047 l_effective_start_date date;
1048 l_tc_stop_date date;
1049 l_tc_start_date date;
1050 l_apr_rej_reason varchar2(2000);
1051 l_fyi_no_detail_body varchar2(4000);
1052 l_recipient_login fnd_user.user_name%type;
1053 l_recipient_id number;
1054 l_supervisor_role wf_local_roles.name%type;
1055 l_tc_id number;
1056 l_tc_ovn number;
1057 l_apr_person_id number;
1058 l_apr_name per_all_people_f.full_name%type;
1059 l_supervisor_name per_all_people_f.full_name%type;
1060 l_supervisor_id number;
1061 l_proc constant varchar2(61) :=g_pkg||'Prepare Notifications';
1062
1063 CURSOR c_tc_info(
1064 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
1065 )
1066 IS
1067 SELECT tcsum.resource_id,
1068 tcsum.start_time,
1069 tcsum.stop_time
1070 FROM hxc_timecard_summary tcsum
1071 WHERE tcsum.timecard_id = p_tc_bbid;
1072
1073 CURSOR get_timecards (p_app_bb_id in hxc_timecard_summary.timecard_id%type)
1074
1075 is
1076
1077 select hts.timecard_id,hts.timecard_ovn
1078 from hxc_tc_ap_links tcl, hxc_timecard_summary hts
1079 where tcl.application_period_id = p_app_bb_id
1080 and tcl.timecard_id = hts.timecard_id;
1081
1082
1083 BEGIN
1084
1085 g_debug:=hr_utility.debug_enabled;
1086
1087 if g_debug then
1088 hr_utility.set_location(l_proc, 10);
1089 end if;
1090
1091 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1092 itemkey => itemkey,
1093 aname => 'APP_BB_ID');
1094
1095 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1096 itemkey => itemkey,
1097 aname => 'APP_BB_OVN');
1098
1099 l_timecard_id := wf_engine.GetItemAttrNumber
1100 (itemtype => itemtype,
1101 itemkey => itemkey,
1102 aname => 'TC_BLD_BLK_ID');
1103 l_timecard_ovn := wf_engine.GetItemAttrNumber
1104 (itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'TC_BLD_BLK_OVN');
1107
1108 --Instead of fetching these from item attributes, fetch it from sumary table since in the case of
1109 --submission-worker these attributes will not be set.
1110
1111 open c_tc_info(l_timecard_id);
1112 fetch c_tc_info into l_resource_id, l_tc_start_date, l_tc_stop_date;
1113
1114 if c_tc_info%notfound then
1115
1116 close c_tc_info;
1117 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1118 hr_utility.set_message_token('PROCEDURE', l_proc);
1119 hr_utility.set_message_token('STEP', '10');
1120 hr_utility.raise_error;
1121 END if;
1122
1123 close c_tc_info;
1124
1125 l_effective_end_date := wf_engine.GetItemAttrDate(
1126 itemtype => itemtype,
1127 itemkey => itemkey,
1128 aname => 'APP_END_DATE');
1129
1130 l_effective_start_date := wf_engine.GetItemAttrDate(
1131 itemtype => itemtype,
1132 itemkey => itemkey,
1133 aname => 'APP_START_DATE');
1134
1135 l_worker_role :=find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_worker,l_timecard_id,l_timecard_ovn);
1136
1137
1138 --There is a possibility that worker may not be having SS login, in that case worker role will be null.
1139 --But we need to have worker full name for various notification purposes, hence we need to check worker role
1140 --for null, if it is null we need to fetch from the per tables.
1141 if l_worker_role is null then
1142 l_worker_full_name :=hxc_find_notify_aprs_pkg.get_name(l_resource_id,l_tc_stop_date);
1143 else
1144 l_worker_full_name := find_full_name_from_role(l_worker_role,l_tc_start_date);
1145 end if;
1146
1147 l_preparer_role := find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_preparer,l_timecard_id,l_timecard_ovn);
1148
1149 l_preparer_full_name := find_full_name_from_role(l_preparer_role,l_tc_start_date);
1150
1151 if g_debug then
1152 hr_utility.set_location(l_proc, 20);
1153 end if;
1154
1155 get_notif_attribute_values
1156 (itemtype,
1157 itemkey,
1158 nvl(l_app_bb_id,l_timecard_id), --l_app_bb_id will be null in the case SUBMISSION-WORKER, in that case we
1159 l_action_code, -- we need to pass timecard id not app id.
1160 l_recipient_code,
1161 l_approval_comp_id,
1162 l_can_notify);
1163
1164 if g_debug then
1165 hr_utility.set_location(l_proc, 30);
1166 end if;
1167
1168 if(l_can_notify) then
1169 if g_debug then
1170 hr_utility.set_location(l_proc, 40);
1171 end if;
1172 if(send_notification(l_approval_comp_id,l_action_code,l_recipient_code,l_timecard_id,l_timecard_ovn,l_app_bb_id,l_app_bb_ovn)) then
1173 if g_debug then
1174 hr_utility.set_location(l_proc, 50);
1175 end if;
1176 if(notification_with_details(l_action_code,l_recipient_code)) then
1177 if g_debug then
1178 hr_utility.set_location(l_proc, 60);
1179 end if;
1180
1181 if(l_action_code=hxc_app_comp_notifications_api.c_action_auto_approve
1182 and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_worker) then
1183 if g_debug then
1184 hr_utility.set_location(l_proc, 70);
1185 end if;
1186
1187 -- set FROM ROLE to Worker Role
1188 l_tc_from_role :=find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_worker,l_timecard_id,l_timecard_ovn);
1189 wf_engine.SetItemAttrText(itemtype => itemtype,
1190 itemkey => itemkey,
1191 aname => 'TC_FROM_ROLE',
1192 avalue => l_tc_from_role);
1193 --set TITLE
1194 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1195 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1196 fnd_message.set_token('END_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1197
1198 l_title := fnd_message.get();
1199
1200 wf_engine.SetItemAttrText(
1201 itemtype => itemtype,
1202 itemkey => itemkey,
1203 aname => 'TITLE',
1204 avalue => l_title);
1205
1206 --set DESCRIPTION
1207 wf_engine.SetItemAttrText
1208 (itemtype => itemtype,
1209 itemkey => itemkey,
1210 aname => 'DESCRIPTION',
1211 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
1212 --set FYI_SUBJECT
1213 fnd_message.set_name('HXC','HXC_APPR_AUTO_WORKER');
1214 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1215 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1216 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1217
1218 l_fyi_subject :=fnd_message.get();
1219
1220 wf_engine.SetItemAttrText(
1221 itemtype => itemtype,
1222 itemkey => itemkey,
1223 aname => 'FYI_SUBJECT',
1224 avalue => l_fyi_subject);
1225
1226 --set FYI_RECIPIENT_LOGIN
1227
1228 if l_worker_role is null then
1229
1230 begin
1231 l_worker_role := hxc_approval_helper.createAdHocUser
1232 (p_resource_id => l_resource_id,
1233 p_effective_date => l_tc_start_date
1234 );
1235 exception
1236 when others then
1237 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1238 hr_utility.set_message_token('PROCEDURE', l_proc);
1239 hr_utility.set_message_token('STEP', '20');
1240 hr_utility.raise_error;
1241 end;
1242
1243 end if;
1244 wf_engine.SetItemAttrText(
1245 itemtype => itemtype,
1246 itemkey => itemkey,
1247 aname => 'FYI_RECIPIENT_LOGIN',
1248 avalue =>l_worker_role);
1249 wf_engine.SetItemAttrText(
1250 itemtype => itemtype,
1251 itemkey => itemkey,
1252 aname => '#HIDE_REASSIGN',
1253 avalue => 'Y');
1254 wf_engine.SetItemAttrText(
1255 itemtype => itemtype,
1256 itemkey => itemkey,
1257 aname => '#HIDE_MOREINFO',
1258 avalue => 'Y');
1259
1260
1261 elsif(l_action_code=hxc_app_comp_notifications_api.c_action_rejected and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_preparer) then
1262 if g_debug then
1263 hr_utility.set_location(l_proc, 80);
1264 end if;
1265 --set TC_FROM_ROLE
1266 l_tc_from_role :=wf_engine.GetItemAttrText(
1267 itemtype => itemtype,
1268 itemkey => itemkey,
1269 aname => 'APR_NAME');
1270 wf_engine.SetItemAttrText(itemtype => itemtype,
1271 itemkey => itemkey,
1272 aname => 'TC_FROM_ROLE',
1273 avalue => l_tc_from_role);
1274 --set TITLE
1275 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1276 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1277 fnd_message.set_token('END_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1278
1279 l_title := fnd_message.get();
1280
1281 wf_engine.SetItemAttrText(
1282 itemtype => itemtype,
1283 itemkey => itemkey,
1284 aname => 'TITLE',
1285 avalue => l_title);
1286 --set DESCRIPTION
1287 wf_engine.SetItemAttrText
1288 (itemtype => itemtype,
1289 itemkey => itemkey,
1290 aname => 'DESCRIPTION',
1291 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
1292
1293 --set FYI_SUBJECT
1294 fnd_message.set_name('HXC','HXC_APPR_REJ_PREPARER');
1295 fnd_message.set_token('APPROVER_FULL_NAME',l_tc_from_role);
1296 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1297 fnd_message.set_token('APPLICATION_PERIOD_STOP_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1298 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1299
1300 l_fyi_subject :=fnd_message.get();
1301
1302 wf_engine.SetItemAttrText(
1303 itemtype => itemtype,
1304 itemkey => itemkey,
1305 aname => 'FYI_SUBJECT',
1306 avalue => l_fyi_subject);
1307
1308 --set FYI_RECIPIENT_LOGIN
1309 wf_engine.SetItemAttrText(
1310 itemtype => itemtype,
1311 itemkey => itemkey,
1312 aname => 'FYI_RECIPIENT_LOGIN',
1313 avalue => l_preparer_role);
1314
1315 wf_engine.SetItemAttrText(
1316 itemtype => itemtype,
1317 itemkey => itemkey,
1318 aname => '#HIDE_REASSIGN',
1319 avalue => 'Y');
1320 wf_engine.SetItemAttrText(
1321 itemtype => itemtype,
1322 itemkey => itemkey,
1323 aname => '#HIDE_MOREINFO',
1324 avalue => 'N');
1325
1326 elsif(l_action_code=hxc_app_comp_notifications_api.c_action_submission and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_worker) then
1327 if g_debug then
1328 hr_utility.set_location(l_proc, 90);
1329 end if;
1330 -- We need to attach timecard Id to Application Id..it looks crazy...
1331 --but we need to do this as the TIMECARD attribute is using APP_BB_ID
1332 --in the URL.
1333 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1334 itemkey => itemkey,
1335 aname => 'APP_BB_ID',
1336 avalue => l_timecard_id);
1337 wf_engine.SetItemAttrText(
1338 itemtype => itemtype,
1339 itemkey => itemkey,
1340 aname => 'FORMATTED_APP_START_DATE',
1341 avalue => to_char(l_tc_start_date,'YYYY/MM/DD'));
1342 --set TC_FROM_ROLE
1343 wf_engine.SetItemAttrText(itemtype => itemtype,
1344 itemkey => itemkey,
1345 aname => 'TC_FROM_ROLE',
1346 avalue => l_preparer_role);
1347
1348 --set TITLE
1349 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1350 fnd_message.set_token('START_DATE',to_char(l_tc_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1351 fnd_message.set_token('END_DATE',to_char(l_tc_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1352
1353 l_title := fnd_message.get();
1354
1355 wf_engine.SetItemAttrText(
1356 itemtype => itemtype,
1357 itemkey => itemkey,
1358 aname => 'TITLE',
1359 avalue => l_title);
1360 --set DESCRIPTION
1361 wf_engine.SetItemAttrText(itemtype => itemtype,
1362 itemkey => itemkey,
1363 aname => 'DESCRIPTION',
1364 avalue => hxc_find_notify_aprs_pkg.get_description_tc(l_timecard_id,l_timecard_ovn));
1365 --set FYI_SUBJECT
1366 fnd_message.set_name('HXC','HXC_APPR_SUB_WORKER');
1367
1368 fnd_message.set_token('START_DATE',to_char(l_tc_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1369 fnd_message.set_token('END_DATE',to_char(l_tc_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1370 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1371 fnd_message.set_token('TOTAL_HOURS',l_total_hours);
1372 fnd_message.set_token('PREPARER_FULL_NAME',l_preparer_full_name);
1373
1374 l_fyi_subject :=fnd_message.get();
1375
1376 wf_engine.SetItemAttrText(
1377 itemtype => itemtype,
1378 itemkey => itemkey,
1379 aname => 'FYI_SUBJECT',
1380 avalue => l_fyi_subject);
1381
1382 --set FYI_RECIPIENT_LOGIN
1383 --Worker role is null that means worker does not have SS login, raise
1384 --an error.
1385 if l_worker_role is null then
1386
1387 begin
1388 l_worker_role := hxc_approval_helper.createAdHocUser
1389 (p_resource_id => l_resource_id,
1390 p_effective_date => l_tc_start_date
1391 );
1392 exception
1393 when others then
1394 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1395 hr_utility.set_message_token('PROCEDURE', l_proc);
1396 hr_utility.set_message_token('STEP', '30');
1397 hr_utility.raise_error;
1398 end;
1399
1400 end if;
1401 wf_engine.SetItemAttrText(
1402 itemtype => itemtype,
1403 itemkey => itemkey,
1404 aname => 'FYI_RECIPIENT_LOGIN',
1405 avalue => l_worker_role);
1406 wf_engine.SetItemAttrText(
1407 itemtype => itemtype,
1408 itemkey => itemkey,
1409 aname => '#HIDE_REASSIGN',
1410 avalue => 'Y');
1411 wf_engine.SetItemAttrText(
1412 itemtype => itemtype,
1413 itemkey => itemkey,
1414 aname => '#HIDE_MOREINFO',
1415 avalue => 'N');
1416 wf_engine.SetItemAttrText(
1417 itemtype => itemtype,
1418 itemkey => itemkey,
1419 aname => 'RESOURCE_ID',
1420 avalue => l_resource_id);
1421
1422 elsif(l_action_code=hxc_app_comp_notifications_api.c_action_request_approval and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_supervisor) then
1423 if g_debug then
1424 hr_utility.set_location(l_proc, 100);
1425 end if;
1426 l_supervisor_role :=find_role_for_recipient('SUPERVISOR',l_timecard_id,l_timecard_ovn);
1427
1428 if l_supervisor_role is null then
1429 result := 'COMPLETE:NO_NOTIFICATION';
1430 return;
1431 end if;
1432
1433 --set TC_FROM_ROLE
1434 wf_engine.SetItemAttrText(itemtype => itemtype,
1435 itemkey => itemkey,
1436 aname => 'TC_FROM_ROLE',
1437 avalue => l_worker_role);
1438 --set TITLE
1439 hr_utility.trace('set TITLE');
1440 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1441 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1442 fnd_message.set_token('END_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1443
1444 l_title := fnd_message.get();
1445
1446 wf_engine.SetItemAttrText(
1447 itemtype => itemtype,
1448 itemkey => itemkey,
1449 aname => 'TITLE',
1450 avalue => l_title);
1451 --set DESCRIPTION
1452 wf_engine.SetItemAttrText
1453 (itemtype => itemtype,
1454 itemkey => itemkey,
1455 aname => 'DESCRIPTION',
1456 avalue => hxc_find_notify_aprs_pkg.get_description_date(l_effective_start_date,l_effective_end_date,l_resource_id));
1457
1458 --set FYI_SUBJECT
1459 l_tc_to_role :=wf_engine.GetItemAttrText(
1460 itemtype => itemtype,
1461 itemkey => itemkey,
1462 aname => 'TC_APPROVER_FROM_ROLE');
1463
1464 fnd_message.set_name('HXC','HXC_APPR_SUB_SUPERVISOR');
1465 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1466 fnd_message.set_token('APPLICATION_PERIOD_END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1467 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1468
1469 l_fyi_subject :=fnd_message.get();
1470
1471 wf_engine.SetItemAttrText(
1472 itemtype => itemtype,
1473 itemkey => itemkey,
1474 aname => 'FYI_SUBJECT',
1475 avalue => l_fyi_subject);
1476 --set FYI_RECIPIENT_LOGIN
1477
1478 wf_engine.SetItemAttrText(
1479 itemtype => itemtype,
1480 itemkey => itemkey,
1481 aname => 'FYI_RECIPIENT_LOGIN',
1482 avalue =>l_supervisor_role );
1483 wf_engine.SetItemAttrText(
1484 itemtype => itemtype,
1485 itemkey => itemkey,
1486 aname => '#HIDE_REASSIGN',
1487 avalue => 'Y');
1488 wf_engine.SetItemAttrText(
1489 itemtype => itemtype,
1490 itemkey => itemkey,
1491 aname => '#HIDE_MOREINFO',
1492 avalue => 'N');
1493
1494 elsif(l_action_code=hxc_app_comp_notifications_api.c_action_transfer and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_preparer) then
1495
1496 if g_debug then
1497 hr_utility.set_location(l_proc, 110);
1498 end if;
1499
1500 wf_engine.SetItemAttrText(itemtype => itemtype,
1501 itemkey => itemkey,
1502 aname => 'TC_FROM_ROLE',
1503 avalue => l_preparer_role);
1504 --set TITLE
1505 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1506 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1507 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1508
1509 l_title := fnd_message.get();
1510
1511 wf_engine.SetItemAttrText(
1512 itemtype => itemtype,
1513 itemkey => itemkey,
1514 aname => 'TITLE',
1515 avalue => l_title);
1516 --set DESCRIPTION
1517 wf_engine.SetItemAttrText
1518 (itemtype => itemtype,
1519 itemkey => itemkey,
1520 aname => 'DESCRIPTION',
1521 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
1522
1523 --set FYI_SUBJECT
1524 l_apr_person_id :=wf_engine.GetItemAttrText(
1525 itemtype => itemtype,
1526 itemkey => itemkey,
1527 aname => 'APR_PERSON_ID');
1528 l_supervisor_id :=hxc_find_notify_aprs_pkg.get_supervisor(l_apr_person_id,SYSDATE);
1529 l_supervisor_name :=hxc_find_notify_aprs_pkg.get_name(
1530 l_supervisor_id,SYSDATE);
1531 fnd_message.set_name('HXC','HXC_APPR_TRAN_APPROVER');
1532 fnd_message.set_token('APPROVER_FULL_NAME',l_preparer_full_name);
1533 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1534 fnd_message.set_token('APPLICATION_PERIOD_STOP_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1535 fnd_message.set_token('SUPERVISOR_FULL_NAME',l_supervisor_name);
1536
1537 l_fyi_subject :=fnd_message.get();
1538
1539 wf_engine.SetItemAttrText(
1540 itemtype => itemtype,
1541 itemkey => itemkey,
1542 aname => 'FYI_SUBJECT',
1543 avalue => l_fyi_subject);
1544 --set FYI_RECIPIENT_LOGIN
1545 l_tc_from_role :=hxc_find_notify_aprs_pkg.get_login(p_person_id => l_apr_person_id);
1546
1547 wf_engine.SetItemAttrText(
1548 itemtype => itemtype,
1549 itemkey => itemkey,
1550 aname => 'FYI_RECIPIENT_LOGIN',
1551 avalue => l_tc_from_role);
1552
1553 wf_engine.SetItemAttrText(
1554 itemtype => itemtype,
1555 itemkey => itemkey,
1556 aname => '#HIDE_REASSIGN',
1557 avalue => 'Y');
1558 wf_engine.SetItemAttrText(
1559 itemtype => itemtype,
1560 itemkey => itemkey,
1561 aname => '#HIDE_MOREINFO',
1562 avalue => 'N');
1563
1564
1565 END if;
1566
1567 result := 'COMPLETE:INCLUDE_TIMECARD_DETAILS';
1568
1569 else
1570 if(l_action_code=hxc_app_comp_notifications_api.c_action_approved and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_preparer) then
1571
1572 if g_debug then
1573 hr_utility.set_location(l_proc, 120);
1574 end if;
1575 --set TC_FROM_ROLE
1576 l_tc_from_role :=wf_engine.GetItemAttrText(
1577 itemtype => itemtype,
1578 itemkey => itemkey,
1579 aname => 'APR_NAME');
1580 wf_engine.SetItemAttrText(itemtype => itemtype,
1581 itemkey => itemkey,
1582 aname => 'TC_FROM_ROLE',
1583 avalue => l_tc_from_role);
1584
1585 --set FYI_SUBJECT
1586 fnd_message.set_name('HXC','HXC_APPR_APPR_PREPARER');
1587 fnd_message.set_token('APPROVER_FULL_NAME',l_tc_from_role);
1588 fnd_message.set_token('TIMECARD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1589 fnd_message.set_token('TIMECARD_STOP_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1590 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1591
1592 l_fyi_subject :=fnd_message.get();
1593
1594 wf_engine.SetItemAttrText(
1595 itemtype => itemtype,
1596 itemkey => itemkey,
1597 aname => 'FYI_SUBJECT',
1598 avalue => l_fyi_subject);
1599
1600 -- set FYI_NO_DETAIL_BODY
1601 fnd_message.set_name('HXC','HXC_APPR_NO_DETAILS_BODY');
1602 fnd_message.set_token('PREPARER_FULL_NAME',l_preparer_full_name);
1603
1604
1605
1606 l_apr_rej_reason :=wf_engine.GetItemAttrText(itemtype => itemtype,
1607 itemkey => itemkey,
1608 aname => 'APR_REJ_REASON');
1609
1610 fnd_message.set_token('APR_REJ_REASON',l_apr_rej_reason);
1611
1612 l_fyi_no_detail_body :=fnd_message.get();
1613
1614 wf_engine.SetItemAttrText(
1615 itemtype => itemtype,
1616 itemkey => itemkey,
1617 aname => 'FYI_NO_DETAIL_BODY',
1618 avalue => l_fyi_no_detail_body);
1619
1620
1621 wf_engine.SetItemAttrText(
1622 itemtype => itemtype,
1623 itemkey => itemkey,
1624 aname => 'FYI_RECIPIENT_LOGIN',
1625 avalue => l_preparer_role);
1626 wf_engine.SetItemAttrText(
1627 itemtype => itemtype,
1628 itemkey => itemkey,
1629 aname => '#HIDE_REASSIGN',
1630 avalue => 'Y');
1631 wf_engine.SetItemAttrText(
1632 itemtype => itemtype,
1633 itemkey => itemkey,
1634 aname => '#HIDE_MOREINFO',
1635 avalue => 'N');
1636
1637 end if;
1638
1639 result := 'COMPLETE:TEXT_ONLY';
1640
1641 END if; -- END Notification with details or not
1642 else
1643 if g_debug then
1644 hr_utility.set_location(l_proc, 130);
1645 end if;
1646 result := 'COMPLETE:NO_NOTIFICATION';
1647 END if; -- END SEND Notification
1648
1649 else
1650 if g_debug then
1651 hr_utility.set_location(l_proc, 140);
1652 end if;
1653 result := 'COMPLETE:NO_NOTIFICATION';
1654 END if;--END can notify or not
1655
1656 return;
1657
1658 -- error handler
1659 hr_utility.trace('Leaving preparing notifications');
1660 exception
1661 when others then
1662
1663 -- The line below records this FUNCTION call in the error system
1664 -- in the case of an exception.
1665 --
1666
1667 if g_debug then
1668 hr_utility.set_location(l_proc, 999);
1669 end if;
1670 wf_core.context('HCAPPRWFHELPER', 'hxc_approval_wf_helper.prepare_notification',
1671 itemtype, itemkey, to_char(actid), funcmode);
1672 raise;
1673 result := '';
1674 return;
1675
1676 END prepare_notification;
1677 ----------------------------------------------------------------------------------------------------------------
1678 procedure cleanup(itemtype IN varchar2,
1679 itemkey IN varchar2,
1680 actid IN number,
1681 funcmode IN varchar2,
1682 result IN OUT NOCOPY varchar2)
1683 is
1684
1685 l_timecard_id number;
1686 l_app_bb_id number;
1687 l_app_bb_ovn number;
1688 l_result varchar2(50);
1689 l_proc constant varchar2(61) := g_pkg ||'cleanup';
1690
1691 /*CURSOR c_get_app_periods(p_timecard_id in number)
1692 is
1693 select aps.application_period_id
1694 from hxc_tc_ap_links tcl,
1695 hxc_app_period_summary aps
1696 where tcl.timecard_id = p_timecard_id
1697 and tcl.application_period_id = aps.application_period_id ;*/
1698
1699 CURSOR get_result
1700 is
1701 select text_value
1702 from wf_item_attribute_values
1703 where item_type = itemtype
1704 and item_key = itemkey
1705 and name = 'RESULT';
1706
1707 begin
1708
1709 --Bug 5374013
1710 --We should not 'cleanup' the process in the case of RESTART.
1711 open get_result;
1712 fetch get_result into l_result;
1713 close get_result;
1714
1715 if l_result <> 'RESTART' then
1716
1717 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1718 itemkey => itemkey,
1719 aname => 'APP_BB_ID');
1720 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1721 itemkey => itemkey,
1722 aname => 'APP_BB_OVN');
1723
1724 update hxc_app_period_summary
1725 set approval_status = 'SUBMITTED'
1726 where application_period_id = l_app_bb_id
1727 and application_period_ovn = l_app_bb_ovn;
1728 end if;
1729
1730 result := 'COMPLETE';
1731
1732 exception
1733 when others then
1734
1735 -- The line below records this FUNCTION call in the error system
1736 -- in the case of an exception.
1737 --
1738
1739 if g_debug then
1740 hr_utility.set_location(l_proc, 999);
1741 end if;
1742 wf_core.context('HCAPPRWFHELPER', 'hxc_approval_wf_helper.cleanup',
1743 itemtype, itemkey, to_char(actid), funcmode);
1744 raise;
1745 result := '';
1746 return;
1747
1748 End cleanup;
1749 ----------------------------------------------------------------------------------------------------------------
1750 END HXC_APPROVAL_WF_HELPER;
1751