1 Package Body HXC_APPROVAL_WF_HELPER as
2 /* $Header: hxcaprwfhelper.pkb 120.12.12010000.4 2010/01/20 12:34:30 amakrish 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
83 SELECT
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
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
231
228 where person_id = p_system_id
229 and p_effective_date between effective_start_date and effective_end_date;
230
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
374 --that role, i.e. if recipient code is supervisor then fetch c_the role by calling find_supervisor_role etc.
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
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 ,
521
518 itemkey => p_item_key ,
519 aname => hxc_approval_wf_helper.c_action_code_attribute,
520 ignore_notfound => true);
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
531
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
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
619 END item_attribute_value_exists;
616 When others then
617 return false;
618
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
707 (p_approval_comp_id in number,
704 END set_notif_attribute_values;
705
706 FUNCTION send_notification
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
834
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
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;
989
986
987 END if;
988 --check for notify worker on auto approval
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_exclude_hours varchar2(1) := 'N';
1062 l_proc constant varchar2(61) :=g_pkg||'Prepare Notifications';
1063
1064 CURSOR c_tc_info(
1065 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
1066 )
1067 IS
1068 SELECT tcsum.resource_id,
1069 tcsum.start_time,
1070 tcsum.stop_time
1071 FROM hxc_timecard_summary tcsum
1072 WHERE tcsum.timecard_id = p_tc_bbid;
1073
1074 CURSOR get_timecards (p_app_bb_id in hxc_timecard_summary.timecard_id%type)
1075
1076 is
1077
1078 select hts.timecard_id,hts.timecard_ovn
1079 from hxc_tc_ap_links tcl, hxc_timecard_summary hts
1080 where tcl.application_period_id = p_app_bb_id
1081 and tcl.timecard_id = hts.timecard_id;
1082
1083
1084 BEGIN
1085
1086 g_debug:=hr_utility.debug_enabled;
1087
1088 if g_debug then
1089 hr_utility.set_location(l_proc, 10);
1090 end if;
1091
1092 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1093 itemkey => itemkey,
1094 aname => 'APP_BB_ID');
1095
1096 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1097 itemkey => itemkey,
1098 aname => 'APP_BB_OVN');
1099
1100 l_timecard_id := wf_engine.GetItemAttrNumber
1101 (itemtype => itemtype,
1102 itemkey => itemkey,
1103 aname => 'TC_BLD_BLK_ID');
1104 l_timecard_ovn := wf_engine.GetItemAttrNumber
1105 (itemtype => itemtype,
1106 itemkey => itemkey,
1107 aname => 'TC_BLD_BLK_OVN');
1108
1109 --Instead of fetching these from item attributes, fetch it from sumary table since in the case of
1110 --submission-worker these attributes will not be set.
1111
1112 open c_tc_info(l_timecard_id);
1113 fetch c_tc_info into l_resource_id, l_tc_start_date, l_tc_stop_date;
1114
1115 if c_tc_info%notfound then
1116
1117 close c_tc_info;
1118 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1119 hr_utility.set_message_token('PROCEDURE', l_proc);
1120 hr_utility.set_message_token('STEP', '10');
1121 hr_utility.raise_error;
1122 END if;
1123
1124 close c_tc_info;
1125
1126 l_effective_end_date := wf_engine.GetItemAttrDate(
1130
1127 itemtype => itemtype,
1128 itemkey => itemkey,
1129 aname => 'APP_END_DATE');
1131 l_effective_start_date := wf_engine.GetItemAttrDate(
1132 itemtype => itemtype,
1133 itemkey => itemkey,
1134 aname => 'APP_START_DATE');
1135
1136 l_worker_role :=find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_worker,l_timecard_id,l_timecard_ovn);
1137
1138
1139 --There is a possibility that worker may not be having SS login, in that case worker role will be null.
1140 --But we need to have worker full name for various notification purposes, hence we need to check worker role
1141 --for null, if it is null we need to fetch from the per tables.
1142 if l_worker_role is null then
1143 l_worker_full_name :=hxc_find_notify_aprs_pkg.get_name(l_resource_id,l_tc_stop_date);
1144 else
1145 l_worker_full_name := find_full_name_from_role(l_worker_role,l_tc_start_date);
1146 end if;
1147
1148 l_preparer_role := find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_preparer,l_timecard_id,l_timecard_ovn);
1149
1150 l_preparer_full_name := find_full_name_from_role(l_preparer_role,l_tc_start_date);
1151
1152 if g_debug then
1153 hr_utility.set_location(l_proc, 20);
1154 end if;
1155
1156 get_notif_attribute_values
1157 (itemtype,
1158 itemkey,
1159 nvl(l_app_bb_id,l_timecard_id), --l_app_bb_id will be null in the case SUBMISSION-WORKER, in that case we
1160 l_action_code, -- we need to pass timecard id not app id.
1161 l_recipient_code,
1162 l_approval_comp_id,
1163 l_can_notify);
1164
1165 if g_debug then
1166 hr_utility.set_location(l_proc, 30);
1167 end if;
1168
1169 if(l_can_notify) then
1170 if g_debug then
1171 hr_utility.set_location(l_proc, 40);
1172 end if;
1173 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
1174 if g_debug then
1175 hr_utility.set_location(l_proc, 50);
1176 end if;
1177 if(notification_with_details(l_action_code,l_recipient_code)) then
1178 if g_debug then
1179 hr_utility.set_location(l_proc, 60);
1180 end if;
1181
1182 if(l_action_code=hxc_app_comp_notifications_api.c_action_auto_approve
1183 and l_recipient_code=hxc_app_comp_notifications_api.c_recipient_worker) then
1184 if g_debug then
1185 hr_utility.set_location(l_proc, 70);
1186 end if;
1187
1188 -- set FROM ROLE to Worker Role
1189 l_tc_from_role :=find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_worker,l_timecard_id,l_timecard_ovn);
1190 wf_engine.SetItemAttrText(itemtype => itemtype,
1191 itemkey => itemkey,
1192 aname => 'TC_FROM_ROLE',
1193 avalue => l_tc_from_role);
1194 --set TITLE
1195 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1196 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1197 fnd_message.set_token('END_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1198
1199 l_title := fnd_message.get();
1200
1201 wf_engine.SetItemAttrText(
1202 itemtype => itemtype,
1203 itemkey => itemkey,
1204 aname => 'TITLE',
1205 avalue => l_title);
1206
1207 --set DESCRIPTION
1208 wf_engine.SetItemAttrText
1209 (itemtype => itemtype,
1210 itemkey => itemkey,
1211 aname => 'DESCRIPTION',
1212 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
1213 --set FYI_SUBJECT
1214 fnd_message.set_name('HXC','HXC_APPR_AUTO_WORKER');
1215 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1216 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1217 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1218
1219 l_fyi_subject :=fnd_message.get();
1220
1221 wf_engine.SetItemAttrText(
1222 itemtype => itemtype,
1223 itemkey => itemkey,
1224 aname => 'FYI_SUBJECT',
1225 avalue => l_fyi_subject);
1226
1227 --set FYI_RECIPIENT_LOGIN
1228
1229 if l_worker_role is null then
1230
1231 begin
1232 l_worker_role := hxc_approval_helper.createAdHocUser
1233 (p_resource_id => l_resource_id,
1234 p_effective_date => l_tc_start_date
1235 );
1236 exception
1237 when others then
1238 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1239 hr_utility.set_message_token('PROCEDURE', l_proc);
1240 hr_utility.set_message_token('STEP', '20');
1241 hr_utility.raise_error;
1242 end;
1243
1244 end if;
1245 wf_engine.SetItemAttrText(
1246 itemtype => itemtype,
1247 itemkey => itemkey,
1248 aname => 'FYI_RECIPIENT_LOGIN',
1249 avalue =>l_worker_role);
1250 wf_engine.SetItemAttrText(
1251 itemtype => itemtype,
1252 itemkey => itemkey,
1253 aname => '#HIDE_REASSIGN',
1254 avalue => 'Y');
1255 wf_engine.SetItemAttrText(
1256 itemtype => itemtype,
1257 itemkey => itemkey,
1258 aname => '#HIDE_MOREINFO',
1259 avalue => 'Y');
1260
1264 hr_utility.set_location(l_proc, 80);
1261
1262 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
1263 if g_debug then
1265 end if;
1266 --set TC_FROM_ROLE
1267 l_tc_from_role :=wf_engine.GetItemAttrText(
1268 itemtype => itemtype,
1269 itemkey => itemkey,
1270 aname => 'APR_NAME');
1271 wf_engine.SetItemAttrText(itemtype => itemtype,
1272 itemkey => itemkey,
1273 aname => 'TC_FROM_ROLE',
1274 avalue => l_tc_from_role);
1275 --set TITLE
1276 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1277 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1278 fnd_message.set_token('END_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1279
1280 l_title := fnd_message.get();
1281
1282 wf_engine.SetItemAttrText(
1283 itemtype => itemtype,
1284 itemkey => itemkey,
1285 aname => 'TITLE',
1286 avalue => l_title);
1287 --set DESCRIPTION
1288 wf_engine.SetItemAttrText
1289 (itemtype => itemtype,
1290 itemkey => itemkey,
1291 aname => 'DESCRIPTION',
1292 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
1293
1294 --set FYI_SUBJECT
1295 fnd_message.set_name('HXC','HXC_APPR_REJ_PREPARER');
1296 fnd_message.set_token('APPROVER_FULL_NAME',l_tc_from_role);
1297 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1298 fnd_message.set_token('APPLICATION_PERIOD_STOP_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1299 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1300
1301 l_fyi_subject :=fnd_message.get();
1302
1303 wf_engine.SetItemAttrText(
1304 itemtype => itemtype,
1305 itemkey => itemkey,
1306 aname => 'FYI_SUBJECT',
1307 avalue => l_fyi_subject);
1308
1309 --set FYI_RECIPIENT_LOGIN
1310 wf_engine.SetItemAttrText(
1311 itemtype => itemtype,
1312 itemkey => itemkey,
1313 aname => 'FYI_RECIPIENT_LOGIN',
1314 avalue => l_preparer_role);
1315
1316 wf_engine.SetItemAttrText(
1317 itemtype => itemtype,
1318 itemkey => itemkey,
1319 aname => '#HIDE_REASSIGN',
1320 avalue => 'Y');
1321 wf_engine.SetItemAttrText(
1322 itemtype => itemtype,
1323 itemkey => itemkey,
1324 aname => '#HIDE_MOREINFO',
1325 avalue => 'N');
1326
1327 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
1328 if g_debug then
1329 hr_utility.set_location(l_proc, 90);
1330 end if;
1331 -- We need to attach timecard Id to Application Id..it looks crazy...
1332 --but we need to do this as the TIMECARD attribute is using APP_BB_ID
1333 --in the URL.
1334 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1335 itemkey => itemkey,
1336 aname => 'APP_BB_ID',
1337 avalue => l_timecard_id);
1338 wf_engine.SetItemAttrText(
1339 itemtype => itemtype,
1340 itemkey => itemkey,
1341 aname => 'FORMATTED_APP_START_DATE',
1342 avalue => to_char(l_tc_start_date,'YYYY/MM/DD'));
1343 --set TC_FROM_ROLE
1344 wf_engine.SetItemAttrText(itemtype => itemtype,
1345 itemkey => itemkey,
1346 aname => 'TC_FROM_ROLE',
1347 avalue => l_preparer_role);
1348
1349 --set TITLE
1350 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1351 fnd_message.set_token('START_DATE',to_char(l_tc_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1352 fnd_message.set_token('END_DATE',to_char(l_tc_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1353
1354 l_title := fnd_message.get();
1355
1356 wf_engine.SetItemAttrText(
1357 itemtype => itemtype,
1358 itemkey => itemkey,
1359 aname => 'TITLE',
1360 avalue => l_title);
1361 --set DESCRIPTION
1362 wf_engine.SetItemAttrText(itemtype => itemtype,
1363 itemkey => itemkey,
1364 aname => 'DESCRIPTION',
1365 avalue => hxc_find_notify_aprs_pkg.get_description_tc(l_timecard_id,l_timecard_ovn));
1366
1367 -- for bug 8888588
1368 l_exclude_hours := hxc_notification_process_pkg.evaluate_abs_pref
1369 (l_resource_id,
1370 TRUNC(l_tc_start_date)
1371 );
1372
1373 IF l_exclude_hours = 'N' THEN
1374 -- Added for bug 6369091
1375 --set TOTAL_HOURS
1376 l_total_hours := hxc_time_category_utils_pkg.category_timecard_hrs_ind(l_timecard_id,l_timecard_ovn,'');
1377 --set FYI_SUBJECT
1378 fnd_message.set_name('HXC','HXC_APPR_SUB_WORKER');
1379 fnd_message.set_token('TOTAL_HOURS',l_total_hours);
1380 ELSE
1381 --set FYI_SUBJECT for Absences enabled resource
1382 fnd_message.set_name('HXC','HXC_APPR_SUB_WORKER_ABS');
1383
1384 END IF;
1388 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1385
1386 fnd_message.set_token('START_DATE',to_char(l_tc_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1387 fnd_message.set_token('END_DATE',to_char(l_tc_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1389 fnd_message.set_token('PREPARER_FULL_NAME',l_preparer_full_name);
1390
1391 l_fyi_subject :=fnd_message.get();
1392
1393 wf_engine.SetItemAttrText(
1394 itemtype => itemtype,
1395 itemkey => itemkey,
1396 aname => 'FYI_SUBJECT',
1397 avalue => l_fyi_subject);
1398
1399 --set FYI_RECIPIENT_LOGIN
1400 --Worker role is null that means worker does not have SS login, raise
1401 --an error.
1402 if l_worker_role is null then
1403
1404 begin
1405 l_worker_role := hxc_approval_helper.createAdHocUser
1406 (p_resource_id => l_resource_id,
1407 p_effective_date => l_tc_start_date
1408 );
1409 exception
1410 when others then
1411 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1412 hr_utility.set_message_token('PROCEDURE', l_proc);
1413 hr_utility.set_message_token('STEP', '30');
1414 hr_utility.raise_error;
1415 end;
1416
1417 end if;
1418 wf_engine.SetItemAttrText(
1419 itemtype => itemtype,
1420 itemkey => itemkey,
1421 aname => 'FYI_RECIPIENT_LOGIN',
1422 avalue => l_worker_role);
1423 wf_engine.SetItemAttrText(
1424 itemtype => itemtype,
1425 itemkey => itemkey,
1426 aname => '#HIDE_REASSIGN',
1427 avalue => 'Y');
1428 wf_engine.SetItemAttrText(
1429 itemtype => itemtype,
1430 itemkey => itemkey,
1431 aname => '#HIDE_MOREINFO',
1432 avalue => 'N');
1433 wf_engine.SetItemAttrText(
1434 itemtype => itemtype,
1435 itemkey => itemkey,
1436 aname => 'RESOURCE_ID',
1437 avalue => l_resource_id);
1438
1439 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
1440 if g_debug then
1441 hr_utility.set_location(l_proc, 100);
1442 end if;
1443 l_supervisor_role :=find_role_for_recipient('SUPERVISOR',l_timecard_id,l_timecard_ovn);
1444
1445 if l_supervisor_role is null then
1446 result := 'COMPLETE:NO_NOTIFICATION';
1447 return;
1448 end if;
1449
1450 --set TC_FROM_ROLE
1451 wf_engine.SetItemAttrText(itemtype => itemtype,
1452 itemkey => itemkey,
1453 aname => 'TC_FROM_ROLE',
1454 avalue => l_worker_role);
1455 --set TITLE
1456 hr_utility.trace('set TITLE');
1457 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1458 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1459 fnd_message.set_token('END_DATE',to_char(l_effective_END_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1460
1461 l_title := fnd_message.get();
1462
1463 wf_engine.SetItemAttrText(
1464 itemtype => itemtype,
1465 itemkey => itemkey,
1466 aname => 'TITLE',
1467 avalue => l_title);
1468 --set DESCRIPTION
1469 wf_engine.SetItemAttrText
1470 (itemtype => itemtype,
1471 itemkey => itemkey,
1472 aname => 'DESCRIPTION',
1473 avalue => hxc_find_notify_aprs_pkg.get_description_date(l_effective_start_date,l_effective_end_date,l_resource_id));
1474
1475 --set FYI_SUBJECT
1476 l_tc_to_role :=wf_engine.GetItemAttrText(
1477 itemtype => itemtype,
1478 itemkey => itemkey,
1479 aname => 'TC_APPROVER_FROM_ROLE');
1480
1481 fnd_message.set_name('HXC','HXC_APPR_SUB_SUPERVISOR');
1482 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1483 fnd_message.set_token('APPLICATION_PERIOD_END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1484 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1485
1486 l_fyi_subject :=fnd_message.get();
1487
1488 wf_engine.SetItemAttrText(
1489 itemtype => itemtype,
1490 itemkey => itemkey,
1491 aname => 'FYI_SUBJECT',
1492 avalue => l_fyi_subject);
1493 --set FYI_RECIPIENT_LOGIN
1494
1495 wf_engine.SetItemAttrText(
1496 itemtype => itemtype,
1497 itemkey => itemkey,
1498 aname => 'FYI_RECIPIENT_LOGIN',
1499 avalue =>l_supervisor_role );
1500 wf_engine.SetItemAttrText(
1501 itemtype => itemtype,
1502 itemkey => itemkey,
1503 aname => '#HIDE_REASSIGN',
1504 avalue => 'Y');
1505 wf_engine.SetItemAttrText(
1506 itemtype => itemtype,
1507 itemkey => itemkey,
1508 aname => '#HIDE_MOREINFO',
1509 avalue => 'N');
1510
1511 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
1515 end if;
1512
1513 if g_debug then
1514 hr_utility.set_location(l_proc, 110);
1516
1517 wf_engine.SetItemAttrText(itemtype => itemtype,
1518 itemkey => itemkey,
1519 aname => 'TC_FROM_ROLE',
1520 avalue => l_preparer_role);
1521 --set TITLE
1522 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1523 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1524 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1525
1526 l_title := fnd_message.get();
1527
1528 wf_engine.SetItemAttrText(
1529 itemtype => itemtype,
1530 itemkey => itemkey,
1531 aname => 'TITLE',
1532 avalue => l_title);
1533 --set DESCRIPTION
1534 wf_engine.SetItemAttrText
1535 (itemtype => itemtype,
1536 itemkey => itemkey,
1537 aname => 'DESCRIPTION',
1538 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
1539
1540 --set FYI_SUBJECT
1541 l_apr_person_id :=wf_engine.GetItemAttrText(
1542 itemtype => itemtype,
1543 itemkey => itemkey,
1544 aname => 'APR_PERSON_ID');
1545 l_supervisor_id :=hxc_find_notify_aprs_pkg.get_supervisor(l_apr_person_id,SYSDATE);
1546 l_supervisor_name :=hxc_find_notify_aprs_pkg.get_name(
1547 l_supervisor_id,SYSDATE);
1548 fnd_message.set_name('HXC','HXC_APPR_TRAN_APPROVER');
1549 fnd_message.set_token('APPROVER_FULL_NAME',l_preparer_full_name);
1550 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1551 fnd_message.set_token('APPLICATION_PERIOD_STOP_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1552 fnd_message.set_token('SUPERVISOR_FULL_NAME',l_supervisor_name);
1553
1554 l_fyi_subject :=fnd_message.get();
1555
1556 wf_engine.SetItemAttrText(
1557 itemtype => itemtype,
1558 itemkey => itemkey,
1559 aname => 'FYI_SUBJECT',
1560 avalue => l_fyi_subject);
1561 --set FYI_RECIPIENT_LOGIN
1562 l_tc_from_role :=hxc_find_notify_aprs_pkg.get_login(p_person_id => l_apr_person_id);
1563
1564 wf_engine.SetItemAttrText(
1565 itemtype => itemtype,
1566 itemkey => itemkey,
1567 aname => 'FYI_RECIPIENT_LOGIN',
1568 avalue => l_tc_from_role);
1569
1570 wf_engine.SetItemAttrText(
1571 itemtype => itemtype,
1572 itemkey => itemkey,
1573 aname => '#HIDE_REASSIGN',
1574 avalue => 'Y');
1575 wf_engine.SetItemAttrText(
1576 itemtype => itemtype,
1577 itemkey => itemkey,
1578 aname => '#HIDE_MOREINFO',
1579 avalue => 'N');
1580
1581
1582 END if;
1583
1584 result := 'COMPLETE:INCLUDE_TIMECARD_DETAILS';
1585
1586 else
1587 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
1588
1589 if g_debug then
1590 hr_utility.set_location(l_proc, 120);
1591 end if;
1592 --set TC_FROM_ROLE
1593 l_tc_from_role :=wf_engine.GetItemAttrText(
1594 itemtype => itemtype,
1595 itemkey => itemkey,
1596 aname => 'APR_NAME');
1597 wf_engine.SetItemAttrText(itemtype => itemtype,
1598 itemkey => itemkey,
1599 aname => 'TC_FROM_ROLE',
1600 avalue => l_tc_from_role);
1601
1602 --set FYI_SUBJECT
1603 fnd_message.set_name('HXC','HXC_APPR_APPR_PREPARER');
1604 fnd_message.set_token('APPROVER_FULL_NAME',l_tc_from_role);
1605 fnd_message.set_token('TIMECARD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1606 fnd_message.set_token('TIMECARD_STOP_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1607 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
1608
1609 l_fyi_subject :=fnd_message.get();
1610
1611 wf_engine.SetItemAttrText(
1612 itemtype => itemtype,
1613 itemkey => itemkey,
1614 aname => 'FYI_SUBJECT',
1615 avalue => l_fyi_subject);
1616
1617 -- set FYI_NO_DETAIL_BODY
1618 fnd_message.set_name('HXC','HXC_APPR_NO_DETAILS_BODY');
1619 fnd_message.set_token('PREPARER_FULL_NAME',l_preparer_full_name);
1620
1621
1622
1623 l_apr_rej_reason :=wf_engine.GetItemAttrText(itemtype => itemtype,
1624 itemkey => itemkey,
1625 aname => 'APR_REJ_REASON');
1626
1627 fnd_message.set_token('APR_REJ_REASON',l_apr_rej_reason);
1628
1629 l_fyi_no_detail_body :=fnd_message.get();
1630
1631 wf_engine.SetItemAttrText(
1632 itemtype => itemtype,
1633 itemkey => itemkey,
1634 aname => 'FYI_NO_DETAIL_BODY',
1635 avalue => l_fyi_no_detail_body);
1636
1637
1638 wf_engine.SetItemAttrText(
1639 itemtype => itemtype,
1640 itemkey => itemkey,
1641 aname => 'FYI_RECIPIENT_LOGIN',
1642 avalue => l_preparer_role);
1643 wf_engine.SetItemAttrText(
1644 itemtype => itemtype,
1645 itemkey => itemkey,
1646 aname => '#HIDE_REASSIGN',
1647 avalue => 'Y');
1651 aname => '#HIDE_MOREINFO',
1648 wf_engine.SetItemAttrText(
1649 itemtype => itemtype,
1650 itemkey => itemkey,
1652 avalue => 'N');
1653
1654 end if;
1655
1656 result := 'COMPLETE:TEXT_ONLY';
1657
1658 END if; -- END Notification with details or not
1659 else
1660 if g_debug then
1661 hr_utility.set_location(l_proc, 130);
1662 end if;
1663 result := 'COMPLETE:NO_NOTIFICATION';
1664 END if; -- END SEND Notification
1665
1666 else
1667 if g_debug then
1668 hr_utility.set_location(l_proc, 140);
1669 end if;
1670 result := 'COMPLETE:NO_NOTIFICATION';
1671 END if;--END can notify or not
1672
1673 return;
1674
1675 -- error handler
1676 hr_utility.trace('Leaving preparing notifications');
1677 exception
1678 when others then
1679
1680 -- The line below records this FUNCTION call in the error system
1681 -- in the case of an exception.
1682 --
1683
1684 if g_debug then
1685 hr_utility.set_location(l_proc, 999);
1686 end if;
1687 wf_core.context('HCAPPRWFHELPER', 'hxc_approval_wf_helper.prepare_notification',
1688 itemtype, itemkey, to_char(actid), funcmode);
1689 raise;
1690 result := '';
1691 return;
1692
1693 END prepare_notification;
1694 ----------------------------------------------------------------------------------------------------------------
1695 procedure cleanup(itemtype IN varchar2,
1696 itemkey IN varchar2,
1697 actid IN number,
1698 funcmode IN varchar2,
1699 result IN OUT NOCOPY varchar2)
1700 is
1701
1702 l_timecard_id number;
1703 l_app_bb_id number;
1704 l_app_bb_ovn number;
1705 l_result varchar2(50);
1706 l_proc constant varchar2(61) := g_pkg ||'cleanup';
1707
1708 /*CURSOR c_get_app_periods(p_timecard_id in number)
1709 is
1710 select aps.application_period_id
1711 from hxc_tc_ap_links tcl,
1712 hxc_app_period_summary aps
1713 where tcl.timecard_id = p_timecard_id
1714 and tcl.application_period_id = aps.application_period_id ;*/
1715
1716 CURSOR get_result
1717 is
1718 select text_value
1719 from wf_item_attribute_values
1720 where item_type = itemtype
1721 and item_key = itemkey
1722 and name = 'RESULT';
1723
1724 begin
1725
1726 --Bug 5374013
1727 --We should not 'cleanup' the process in the case of RESTART.
1728 open get_result;
1729 fetch get_result into l_result;
1730 close get_result;
1731
1732 if l_result <> 'RESTART' then
1733
1734 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1735 itemkey => itemkey,
1736 aname => 'APP_BB_ID');
1737 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
1738 itemkey => itemkey,
1739 aname => 'APP_BB_OVN');
1740
1741 update hxc_app_period_summary
1742 set approval_status = 'SUBMITTED'
1743 where application_period_id = l_app_bb_id
1744 and application_period_ovn = l_app_bb_ovn;
1745 end if;
1746
1747 result := 'COMPLETE';
1748
1749 exception
1750 when others then
1751
1752 -- The line below records this FUNCTION call in the error system
1753 -- in the case of an exception.
1754 --
1755
1756 if g_debug then
1757 hr_utility.set_location(l_proc, 999);
1758 end if;
1759 wf_core.context('HCAPPRWFHELPER', 'hxc_approval_wf_helper.cleanup',
1760 itemtype, itemkey, to_char(actid), funcmode);
1761 raise;
1762 result := '';
1763 return;
1764
1765 End cleanup;
1766 ----------------------------------------------------------------------------------------------------------------
1767 END HXC_APPROVAL_WF_HELPER;
1768
[Home] [Help]