[Home] [Help]
PACKAGE BODY: APPS.HXC_NOTIFICATION_PROCESS_PKG
Source
1 Package Body HXC_NOTIFICATION_PROCESS_PKG as
2 /* $Header: hxcnotifprocess.pkb 120.14.12020000.2 2012/07/04 09:28:12 amnaraya ship $ */
3 g_pkg constant varchar2(30) := 'hxc_notification_process_pkg.';
4 g_debug BOOLEAN :=hr_utility.debug_enabled;
5
6
7 function IS_TCOWNER_SAMEAS_APPROVER(
8 p_notficationId in varchar2)
9 return varchar2
10 is
11
12 l_item_key WF_NOTIFICATIONS.ITEM_KEY%TYPE;
13 l_item_type WF_NOTIFICATIONS.MESSAGE_TYPE%TYPE;
14 l_tc_owner_resource_id varchar2(30);
15 l_app_resource_id FND_USER.EMPLOYEE_ID%TYPE;
16
17
18 cursor cur_get_tcapprover_and_ItemKey(l_notification_id WF_NOTIFICATIONS.NOTIFICATION_ID%TYPE)
19 is
20 select fuser.employee_id,wf.message_type,wf.item_key
21 from wf_notifications wf,
22 fnd_user fuser
23 where wf.notification_id = l_notification_id
24 and fuser.user_name = wf.RECIPIENT_ROLE;
25
26 begin
27 g_debug:=hr_utility.debug_enabled;
28 if g_debug then
29 hr_utility.trace('HXC_NOTIFICATION_PROCESS_PKG.IS_TCOWNER_SAMEAS_APPROVER');
30 hr_utility.trace('Opening cursor to get tc approver, item type and key');
31 end if;
32
33 open cur_get_tcapprover_and_ItemKey(p_notficationId);
34 fetch cur_get_tcapprover_and_ItemKey into l_app_resource_id,l_item_type,l_item_key;
35 close cur_get_tcapprover_and_ItemKey;
36
37
38 l_tc_owner_resource_id := wf_engine.GetItemAttrText(
39 itemtype => l_item_type,
40 itemkey => l_item_key,
41 aname => 'RESOURCE_ID');
42
43 if l_app_resource_id <> l_tc_owner_resource_id then
44 if g_debug then
45 hr_utility.trace('Approver '|| l_app_resource_id || 'and Timecard Owner ' || l_tc_owner_resource_id ||'are not same');
46 end if;
47 return 'N';
48 end if;
49 if g_debug then
50 hr_utility.trace('Approver '|| l_app_resource_id || 'and Timecard Owner ' || l_tc_owner_resource_id ||'are same');
51 end if;
52 return 'Y';
53
54 end IS_TCOWNER_SAMEAS_APPROVER;
55
56
57 --This process activity procedure simply determines whether the previous approval was performed by the supervisor
58 --the approver.We look it up off the application period id and check the approval mechanism on the corresponding
59 --approval component id.
60
61 PROCEDURE approved_by
62 (p_itemtype in varchar2,
63 p_itemkey in varchar2,
64 p_actid in number,
65 p_funcmode in varchar2,
66 p_result in out nocopy varchar2) is
67
68 CURSOR c_get_app_mechanism(p_app_bb_id IN NUMBER,p_app_bb_ovn IN NUMBER)
69 IS
70 select approval_mechanism
71 from hxc_approval_comps hac, hxc_app_period_summary haps
72 where haps.application_period_id = p_app_bb_id
73 and haps.application_period_ovn = p_app_bb_ovn
74 and haps.approval_comp_id = hac.approval_comp_id;
75
76 l_app_bb_id hxc_app_period_summary.application_period_id%type;
77 l_app_bb_ovn hxc_app_period_summary.application_period_ovn%type;
78 l_app_mechanism varchar2(30);
79 l_label varchar2(50);
80 l_approval_style_id number;
81 l_preparer_timeout number;
82 l_proc constant varchar2(61) := g_pkg ||'approved_by';
83
84 BEGIN
85 g_debug:=hr_utility.debug_enabled;
86 if g_debug then
87 hr_utility.set_location(l_proc, 10);
88 end if;
89
90 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
91 itemkey => p_itemkey,
92 aname => 'APP_BB_ID');
93 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
94 itemkey => p_itemkey,
95 aname => 'APP_BB_OVN');
96 open c_get_app_mechanism(l_app_bb_id,l_app_bb_ovn);
97 fetch c_get_app_mechanism into l_app_mechanism;
98
99 --Bug 5361995.
100 --We should not show the button 'Send to next approver' in the preparer notification when the current
101 --approver is the final approver
102 l_label := wf_engine.GetActivityLabel ( actid => p_actid);
103
104 if l_label = 'APPROVAL_NOTIFICATION:APPROVED_BY_APR_INACTION' then
105
106 HXC_FIND_NOTIFY_APRS_PKG.is_final_apr(
107 p_itemtype ,
108 p_itemkey ,
109 p_actid ,
110 p_funcmode ,
111 p_result );
112
113 l_approval_style_id:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
114 itemkey => p_itemkey,
115 aname => 'APPROVAL_STYLE_ID');
116
117 l_preparer_timeout := hxc_notification_helper.preparer_timeout_value(l_approval_style_id);
118
119 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
120 itemkey => p_itemkey,
121 aname => 'APPROVAL_TIMEOUT',
122 avalue => l_preparer_timeout);
123
124 end if;
125
126 if l_app_mechanism = 'HR_SUPERVISOR' then
127
128 if g_debug then
129 hr_utility.set_location(l_proc, 20);
130 end if;
131 hxc_approval_wf_helper.set_notif_attribute_values
132 (p_itemtype,
133 p_itemkey,
134 hxc_app_comp_notifications_api.c_action_transfer,
135 hxc_app_comp_notifications_api.c_recipient_preparer
136 );
137
138 --Bug 5361995.
139 if p_result = 'COMPLETE:Y' and l_label = 'APPROVAL_NOTIFICATION:APPROVED_BY_APR_INACTION' then
140 p_result := 'COMPLETE:PERSON';
141 else
142 p_result := 'COMPLETE:SUPERVISOR';
143 end if;
144
145 else
146 if g_debug then
147 hr_utility.set_location(l_proc, 30);
148 end if;
149 p_result := 'COMPLETE:PERSON';
150 end if;
151
152 close c_get_app_mechanism;
153 exception
154 when others then
155
156 -- The line below records this function call in the error system
157 -- in the case of an exception.
158 --
159 if g_debug then
160 hr_utility.set_location(l_proc, 999);
161 end if;
162
163 wf_core.context('HXCNOTIFPROCESS', 'hxc_notification_process_pkg.approved_by',
164 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
165 raise;
166 p_result := '';
167 return;
168 END approved_by;
169
170 --This procedure initializes the loop item attribute value for use with the resending of notifications. It sets
171 --the loop variable in a safe fashion, ensuring that if by accident an existing workflow comes into this
172 --procedure it won't fail
173 PROCEDURE loop_initialization
174 (p_item_type in wf_items.item_type%type,
175 p_item_key in wf_item_activity_statuses.item_key%type,
176 p_resend_number in number) is
177
178 BEGIN
179
180 if(hxc_approval_wf_helper.item_attribute_value_exists(p_item_type,p_item_key,'NOTIFICATION_RESEND_COUNTER')) then
181
182 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
183 itemkey => p_item_key,
184 aname => 'NOTIFICATION_RESEND_COUNTER',
185 avalue => p_resend_number);
186
187 else
188
189 wf_engine.AddItemAttr(itemtype => p_item_type,
190 itemkey => p_item_key,
191 aname => 'NOTIFICATION_RESEND_COUNTER',
192 number_value => p_resend_number);
193
194 end if;
195
196 END loop_initialization;
197
198
199 PROCEDURE timeouts_enabled
200 (p_itemtype in varchar2,
201 p_itemkey in varchar2,
202 p_actid in number,
203 p_funcmode in varchar2,
204 p_result in out nocopy varchar2) is
205
206 CURSOR c_timeout_enabled(p_app_bb_id in number)
207 IS
208 select hacn.notification_number_retries
209 from hxc_app_period_summary haps
210 ,hxc_approval_comps hac
211 ,hxc_app_comp_notif_usages hacnu
212 ,hxc_app_comp_notifications hacn
213 where haps.application_period_id = p_app_bb_id
214 and haps.approval_comp_id = hac.approval_comp_id
215 and hacnu.approval_comp_id = hac.approval_comp_id
216 and hacnu.approval_comp_ovn = hac.object_version_number
217 and hacnu.comp_notification_id = hacn.comp_notification_id
218 and hacnu.comp_notification_ovn=hacn.object_version_number
219 and hacnu.enabled_flag = 'Y'
220 and hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND';
221
222 cursor c_app_comp_pm(p_bb_id number,p_bb_ovn number)
223 is
224 select hac.approval_comp_id
225 from hxc_approval_comps hac,
226 hxc_approval_styles has,
227 hxc_time_building_blocks htb
228 where htb.time_building_block_id =p_bb_id
229 and htb.object_version_number = p_bb_ovn
230 and htb.approval_style_id = has.approval_style_id
231 and has.approval_style_id = hac.APPROVAL_STYLE_ID
232 and hac.approval_mechanism = 'PROJECT_MANAGER'
233 and hac.parent_comp_id is null
234 and hac.parent_comp_ovn is null;
235
236 CURSOR c_timeout_enabled_pm(p_app_comp_id in number)
237 IS
238 select hacn.notification_number_retries
239 from hxc_approval_comps hac
240 ,hxc_app_comp_notif_usages hacnu
241 ,hxc_app_comp_notifications hacn
242 where hac.approval_comp_id = p_app_comp_id
243 and hacnu.approval_comp_id = hac.approval_comp_id
244 and hacnu.approval_comp_ovn = hac.object_version_number
245 and hacnu.comp_notification_id = hacn.comp_notification_id
246 and hacnu.comp_notification_ovn=hacn.object_version_number
247 and hacnu.enabled_flag = 'Y'
248 and hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND';
249
250
251
252 l_resend_number number := 0;
253 l_approval_style_id number;
254 l_app_bb_id number;
255 l_approver_timeout number;
256 l_role_name varchar2(50);
257 l_role_display varchar2(50);
258 l_user_name varchar2(50);
259 l_user_display varchar2(50);
260 l_total_hours number;
261 l_approval_comp_id number;
262 p_tc_bbid number;
263 p_tc_bbovn number;
264 l_preparer_role wf_local_roles.name%type;
265 l_proc constant varchar2(61) := g_pkg ||'timeouts_enabled';
266
267 BEGIN
268 g_debug:=hr_utility.debug_enabled;
269
270 if g_debug then
271 hr_utility.set_location(l_proc, 10);
272 end if;
273
274
275 --find application period id item attribute value
276
277 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
278 itemkey => p_itemkey,
279 aname => 'APP_BB_ID');
280
281 -- We need to set the total hours and Description in this procedure because we are over writing these values in
282 -- prepare notification process while sending superviosr notification.
283 wf_engine.SetItemAttrText
284 (itemtype => p_itemtype,
285 itemkey => p_itemkey,
286 aname => 'DESCRIPTION',
287 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id));
288
289 l_total_hours:= HXC_FIND_NOTIFY_APRS_PKG.category_timecard_hrs(l_app_bb_id,'');
290 wf_engine.SetItemAttrNumber(
291 itemtype => p_itemtype,
292 itemkey => p_itemkey,
293 aname => 'TOTAL_TC_HOURS',
294 avalue => l_total_hours);
295
296 p_tc_bbid:= wf_engine.GetItemAttrNumber
297 (itemtype => p_itemtype,
298 itemkey => p_itemkey,
299 aname => 'TC_BLD_BLK_ID');
300 p_tc_bbovn:= wf_engine.GetItemAttrNumber
301 (itemtype => p_itemtype,
302 itemkey => p_itemkey,
303 aname => 'TC_BLD_BLK_OVN');
304
305 open c_app_comp_pm(p_tc_bbid,p_tc_bbovn);
306 fetch c_app_comp_pm into l_approval_comp_id;
307
308 if c_app_comp_pm%found then
309
310 open c_timeout_enabled_pm(l_approval_comp_id);
311 fetch c_timeout_enabled_pm into l_resend_number;
312 close c_timeout_enabled_pm;
313
314 else
315 close c_app_comp_pm;
316 open c_timeout_enabled(l_app_bb_id);
317 fetch c_timeout_enabled into l_resend_number;
318 close c_timeout_enabled;
319 end if;
320
321 if l_resend_number > 0 then
322 -- set Approval timeout
323 if g_debug then
324 hr_utility.set_location(l_proc, 20);
325 end if;
326
327 --Bug 5359397.
328 --TC_FROM_ROLE needs ro reset to preparer role since we are overwriting this attribute with approver role
329 --in reset_for_next_timeout.
330 l_preparer_role := hxc_approval_wf_helper.find_role_for_recipient('PREPARER',p_tc_bbid,p_tc_bbovn);
331 wf_engine.SetItemAttrText(itemtype => p_itemtype,
332 itemkey => p_itemkey,
333 aname => 'TC_FROM_ROLE',
334 avalue => l_preparer_role);
335
336 loop_initialization(p_itemtype ,p_itemkey ,l_resend_number-1);
337
338 l_approval_style_id:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
339 itemkey => p_itemkey,
340 aname => 'APPROVAL_STYLE_ID');
341
342 l_approver_timeout := hxc_notification_helper.approver_timeout_value(l_approval_style_id);
343
344 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
345 itemkey => p_itemkey,
346 aname => 'APPROVAL_TIMEOUT',
347 avalue => l_approver_timeout);
348 wf_engine.SetItemAttrText(
349 itemtype => p_itemtype,
350 itemkey => p_itemkey,
351 aname => hxc_approval_wf_helper.c_recipient_code_attribute,
352 avalue => hxc_app_comp_notifications_api.c_recipient_approver);
353
354
355 p_result := 'COMPLETE:Y';
356 else
357 if g_debug then
358 hr_utility.set_location(l_proc, 30);
359 end if;
360 p_result := 'COMPLETE:N';
361 end if;
362 exception
363 when others then
364
365 -- The line below records this function call in the error system
366 -- in the case of an exception.
367 --
368 if g_debug then
369 hr_utility.set_location(l_proc, 999);
370 end if;
371
372 wf_core.context('HXCNOTIFPROCESS', 'hxc_notification_process_pkg.timeouts_enabled',
373 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
374 raise;
375 p_result := '';
376 return;
377
378 END timeouts_enabled;
379
380 PROCEDURE reset_for_next_timeout(p_itemtype in varchar2,
381 p_itemkey in varchar2,
382 p_actid in number,
383 p_funcmode in varchar2,
384 p_result in out nocopy varchar2) is
385
386 l_current_recipient varchar2(30);
387 l_preparer_timeout number;
388 l_admin_timeout number;
389 l_approval_style_id number;
390 l_tc_from_role wf_local_roles.name%type;
391 l_worker_role wf_local_roles.name%type;
392 l_worker_full_name per_all_people_f.full_name%type;
393 l_preparer_role wf_local_roles.name%type;
394 l_preparer_full_name per_all_people_f.full_name%type;
395 l_admin_role wf_local_roles.name%type;
396 l_admin_full_name per_all_people_f.full_name%type;
397 l_apr_name per_all_people_f.full_name%type;
398 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
399 l_timecard_ovn hxc_time_building_blocks.time_building_block_id%TYPE;
400 l_tc_stop_date date;
401 l_tc_start_date date;
402 l_effective_start_date date;
403 l_effective_end_date date;
404 l_fyi_subject varchar2(1000);
405 l_recipient_login fnd_user.user_name%type;
406 l_recipient_id number;
407 l_resend_counter number;
408 l_proc constant varchar2(61) := g_pkg ||'reset_for_next_timeout';
409 BEGIN
410
411 g_debug:=hr_utility.debug_enabled;
412
413 if g_debug then
414 hr_utility.set_location(l_proc, 10);
415 end if;
416
417 l_timecard_id := wf_engine.GetItemAttrNumber
418 (itemtype => p_itemtype,
419 itemkey => p_itemkey,
420 aname => 'TC_BLD_BLK_ID');
421 l_timecard_ovn := wf_engine.GetItemAttrNumber
422 (itemtype => p_itemtype,
423 itemkey => p_itemkey,
424 aname => 'TC_BLD_BLK_OVN');
425 l_tc_stop_date := wf_engine.GetItemAttrDate(
426 itemtype => p_itemtype,
427 itemkey => p_itemkey,
428 aname => 'TC_STOP');
429
430 l_tc_start_date := wf_engine.GetItemAttrDate(
431 itemtype => p_itemtype,
432 itemkey => p_itemkey,
433 aname => 'TC_START');
434
435 l_effective_end_date := wf_engine.GetItemAttrDate(
436 itemtype => p_itemtype,
437 itemkey => p_itemkey,
438 aname => 'APP_END_DATE');
439
440 l_effective_start_date := wf_engine.GetItemAttrDate(
441 itemtype => p_itemtype,
442 itemkey => p_itemkey,
443 aname => 'APP_START_DATE');
444
445 l_current_recipient := wf_engine.getItemAttrText(
446 itemtype => p_itemtype,
447 itemkey => p_itemkey,
448 aname => hxc_approval_wf_helper.c_recipient_code_attribute,
449 ignore_notfound => true);
450
451 l_approval_style_id:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
452 itemkey => p_itemkey,
453 aname => 'APPROVAL_STYLE_ID');
454
455 l_worker_role :=hxc_approval_wf_helper.find_role_for_recipient('WORKER',l_timecard_id,l_timecard_ovn);
456
457 l_worker_full_name := hxc_approval_wf_helper.find_full_name_from_role(l_worker_role,l_tc_start_date);
458
459 l_preparer_role := hxc_approval_wf_helper.find_role_for_recipient('PREPARER',l_timecard_id,l_timecard_ovn);
460
461 l_preparer_full_name := hxc_approval_wf_helper.find_full_name_from_role(l_preparer_role,l_tc_start_date);
462
463 l_admin_role :=hxc_approval_wf_helper.find_role_for_recipient('ADMIN',l_timecard_id,l_timecard_ovn);
464
465 l_admin_full_name := hxc_approval_wf_helper.find_full_name_from_role(l_admin_role,l_tc_start_date);
466
467
468 if l_current_recipient = hxc_app_comp_notifications_api.c_recipient_approver then
469
470 if g_debug then
471 hr_utility.set_location(l_proc, 20);
472 end if;
473
474 --set the attibutes for the PREPARER
475
476 l_preparer_timeout := hxc_notification_helper.preparer_timeout_value(l_approval_style_id);
477
478 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
479 itemkey => p_itemkey,
480 aname => 'APPROVAL_TIMEOUT',
481 avalue => l_preparer_timeout);
482 --loop variable seeting is not required as it is already set in the timeout_enabled
483
484
485 l_tc_from_role :=wf_engine.GetItemAttrText(
486
487 itemtype => p_itemtype,
488 itemkey => p_itemkey,
489 aname => 'TC_APPROVER_FROM_ROLE');
490 wf_engine.SetItemAttrText(itemtype => p_itemtype,
491 itemkey => p_itemkey,
492 aname => 'TC_FROM_ROLE',
493 avalue => l_tc_from_role);
494
495
496 fnd_message.set_name('HXC','HXC_APPR_TO_PREPARER');
497 fnd_message.set_token('TIMECARD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
498 fnd_message.set_token('TIMECARD_STOP_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
499 fnd_message.set_token('WORKER_FULL_NAME',l_worker_full_name);
500 fnd_message.set_token('APPROVER_FULL_NAME',hxc_approval_wf_helper.find_full_name_from_role(l_tc_from_role,l_tc_start_date));
501
502 l_fyi_subject :=fnd_message.get();
503
504 wf_engine.SetItemAttrText(
505 itemtype => p_itemtype,
506 itemkey => p_itemkey,
507 aname => 'FYI_SUBJECT',
508 avalue => l_fyi_subject);
509
510 wf_engine.SetItemAttrText(
511 itemtype => p_itemtype,
512 itemkey => p_itemkey,
513 aname => 'PREPARER_ROLE',
514 avalue => l_preparer_role );
515
516
517
518
519 wf_engine.SetItemAttrText(
520 itemtype => p_itemtype,
521 itemkey => p_itemkey,
522 aname => hxc_approval_wf_helper.c_recipient_code_attribute,
523 avalue => hxc_app_comp_notifications_api.c_recipient_preparer);
524
525
526 elsif l_current_recipient = hxc_app_comp_notifications_api.c_recipient_preparer then
527 --set the attibutes for the ADMIN
528
529 if g_debug then
530 hr_utility.set_location(l_proc, 30);
531 end if;
532
533 l_admin_timeout := hxc_notification_helper.admin_timeout_value(l_approval_style_id);
534 l_resend_counter := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
535 itemkey => p_itemkey,
536 aname => 'NOTIFICATION_RESEND_COUNTER');
537 if l_resend_counter = 0 then
538 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
539 itemkey => p_itemkey,
540 aname => 'APPROVAL_TIMEOUT',
541 avalue => 0);
542
543 else
544 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
545 itemkey => p_itemkey,
546 aname => 'APPROVAL_TIMEOUT',
547 avalue => l_admin_timeout);
548
549 loop_initialization(p_itemtype ,p_itemkey ,l_resend_counter-1);
550 end if;
551
552 --loop variable seeting is not required as it is already set in the timeout_enabled
553 l_apr_name := wf_engine.GetItemAttrText(itemtype => p_itemtype,
554 itemkey => p_itemkey,
555 aname => 'TC_APPROVER_FROM_ROLE');
556
557 fnd_message.set_name('HXC','HXC_APPR_TO_ADMIN');
558 fnd_message.set_token('TIMECARD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
559 fnd_message.set_token('TIMECARD_STOP_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
560 fnd_message.set_token('WORKER_FULL_NAME',l_worker_full_name);
561 fnd_message.set_token('APPROVER_FULL_NAME',hxc_approval_wf_helper.find_full_name_from_role(l_apr_name,l_tc_start_date));
562
563 l_fyi_subject :=fnd_message.get();
564
565
566
567 wf_engine.SetItemAttrText(
568 itemtype => p_itemtype,
569 itemkey => p_itemkey,
570 aname => 'FYI_SUBJECT',
571 avalue => l_fyi_subject);
572
573 wf_engine.SetItemAttrText(itemtype => p_itemtype,
574 itemkey => p_itemkey,
575 aname => 'TC_FROM_ROLE',
576 avalue => l_preparer_role);
577
578 wf_engine.SetItemAttrText(
579 itemtype => p_itemtype,
580 itemkey => p_itemkey,
581 aname => 'ADMIN_ROLE',
582 avalue => l_admin_role );
583
584 wf_engine.SetItemAttrText(
585 itemtype => p_itemtype,
586 itemkey => p_itemkey,
587 aname => hxc_approval_wf_helper.c_recipient_code_attribute,
588 avalue => hxc_app_comp_notifications_api.c_recipient_admin);
589
590 elsif l_current_recipient = hxc_app_comp_notifications_api.c_recipient_admin then
591
592 --In this case all the attributes were already set in the l_current_recipient = 'PREPARER' section.
593
594 if g_debug then
595 hr_utility.set_location(l_proc, 40);
596 end if;
597
598 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
599 itemkey => p_itemkey,
600 aname => 'APPROVAL_TIMEOUT',
601 avalue => 0);
602
603 end if;
604
605 p_result := 'COMPLETE';
606
607 exception
608 when others then
609
610 -- The line below records this function call in the error system
611 -- in the case of an exception.
612 --
613 if g_debug then
614 hr_utility.set_location(l_proc, 999);
615 end if;
616 wf_core.context('HXCNOTIFPROCESS', 'hxc_notification_process_pkg.reset_for_next_timeout',
617 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
618 raise;
619 p_result := '';
620 return;
621
622 end reset_for_next_timeout;
623
624 PROCEDURE restart_workflow(p_itemtype in varchar2,
625 p_item_key in varchar2,
626 p_actid in number,
627 p_funcmode in varchar2,
628 p_result in out nocopy varchar2) is
629
630 CURSOR c_csr_get_tc_info(
631 p_bld_blk_id number,
632 p_ovn number
633 )
634 IS
635 select tc.resource_id, tc.start_time, tc.stop_time,tc.last_updated_by
636 from hxc_time_building_blocks tc
637 where tc.time_building_block_id = p_bld_blk_id
638 and tc.object_version_number = p_ovn;
639
640 CURSOR c_csr_get_appl_periods(
641 p_app_bb_id in number
642 ,p_app_bb_ovn in number
643 )
644 IS
645 select aps.start_time, -- period_start_date
646 aps.stop_time, -- period_end_date
647 aps.time_recipient_id,
648 aps.recipient_sequence,
649 aps.time_category_id,
650 aps.category_sequence
651 from hxc_app_period_summary aps
652 where aps.application_period_id = p_app_bb_id
653 and aps.application_period_ovn = p_app_bb_ovn;
654
655 CURSOR c_is_error_process(p_itemtype in varchar2, p_item_key in varchar2)
656 is
657 select parent_item_key
658 from wf_items
659 where item_type = p_itemtype
660 and item_key = p_item_key
661 and root_activity = 'OTL_ERROR_PROCESS';
662
663 cursor c_get_attributes(p_timecard_bb_id in number,p_timecard_ovn in number )
664 is
665 select ta.attribute3,
666 ta.attribute4,
667 ta.attribute5,
668 ta.attribute6
669 from hxc_time_attributes ta,
670 hxc_time_attribute_usages tau,
671 hxc_time_building_blocks tbb
672 where tbb.time_building_block_id = p_timecard_bb_id
673 and tbb.object_version_number = p_timecard_ovn
674 and tbb.time_building_block_id = tau.time_building_block_id
675 and tbb.object_version_number = tau.time_building_block_ovn
676 and ta.time_attribute_id = tau.time_attribute_id
677 and ta.attribute_category = 'SECURITY';
678 l_timecard_id number;
679 l_timecard_ovn number;
680 l_tc_resubmitted varchar2(10);
681 l_bb_new varchar2(10);
682 l_app_bb_id number;
683 l_app_bb_ovn number;
684 l_tc_resource_id number;
685 l_tc_start_time date;
686 l_tc_stop_time date;
687 l_last_updated_by number;
688 l_process_name varchar2(30);
689 l_period_start_date date;
690 l_period_end_date date;
691 l_time_recipient varchar2(150);
692 l_recipient_sequence number;
693 l_time_category_id hxc_time_categories.time_category_id%TYPE;
694 l_category_sequence hxc_approval_comps.approval_order%TYPE;
695 l_approval_style_id number;
696 l_tc_url varchar2(1000);
697 l_item_key wf_items.item_key%type;
698 itemkey wf_items.item_key%type;
699 l_user_id number;
700 l_resp_id number;
701 l_resp_appl_id number;
702 l_sec_grp_id number;
703 l_proc constant varchar2(61) := g_pkg ||'restart_workflow';
704
705 begin
706
707 g_debug:=hr_utility.debug_enabled;
708
709 l_process_name := 'HXC_APPLY_NOTIFY';
710
711
712 if g_debug then
713 hr_utility.set_location(l_proc, 10);
714 end if;
715
716 --This restart_workflow is called from two places
717 --1.) When Adminstrator chooses to restart the workflow.
718 --2.) When Error Adminstrator chosses to restart the workflow.
719 --In the second case the itemkey passed to this procedure is not the itemkey assoicated with application period id.
720 --This is a entirely a different item key(error item key). The parent to this error item key is the item key in App bb id.
721 --So in the second case fetch the parent item key.
722
723 open c_is_error_process(p_itemtype,p_item_key);
724 fetch c_is_error_process into itemkey; -- When Error adminstartor restarts
725
726 if c_is_error_process%notfound then
727 itemkey := p_item_key; --When Adminstrator restarts
728 end if;
729
730 close c_is_error_process;
731
732
733 l_timecard_id := wf_engine.GetItemAttrNumber
734 (itemtype => p_itemtype,
735 itemkey => itemkey,
736 aname => 'TC_BLD_BLK_ID');
737 l_timecard_ovn := wf_engine.GetItemAttrNumber
738 (itemtype => p_itemtype,
739 itemkey => itemkey,
740 aname => 'TC_BLD_BLK_OVN');
741 l_tc_resubmitted := wf_engine.GetItemAttrText
742 (itemtype => p_itemtype,
743 itemkey => itemkey,
744 aname => 'TC_RESUBMITTED');
745 l_bb_new := wf_engine.GetItemAttrText
746 (itemtype => p_itemtype,
747 itemkey => itemkey,
748 aname => 'BB_NEW');
749
750 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
751 itemkey => itemkey,
752 aname => 'APP_BB_ID');
753
754 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
755 itemkey => itemkey,
756 aname => 'APP_BB_OVN');
757 open c_csr_get_tc_info(l_timecard_id,
758 l_timecard_ovn);
759 fetch c_csr_get_tc_info into l_tc_resource_id,
760 l_tc_start_time,
761 l_tc_stop_time,
762 l_last_updated_by;
763 close c_csr_get_tc_info;
764
765 open c_get_attributes(l_timecard_id,l_timecard_ovn);
766 fetch c_get_attributes into l_user_id,l_resp_id,l_resp_appl_id,l_sec_grp_id;
767 close c_get_attributes;
768
769 if l_user_id <> fnd_global.user_id OR l_resp_id <>fnd_global.resp_id
770 OR l_resp_appl_id <> fnd_global.resp_appl_id OR l_sec_grp_id <>fnd_global.security_group_id then
771 fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id,l_resp_appl_id,l_sec_grp_id);
772 end if;
773
774 SELECT hxc_approval_item_key_s.nextval
775 INTO l_item_key
776 FROM dual;
777
778
779 update hxc_app_period_summary
780 set notification_status = 'NOTIFIED',
781 approval_item_type = p_itemtype,
782 approval_process_name = l_process_name,
783 approval_item_key = l_item_key
784 where application_period_id = l_app_bb_id
785 and application_period_ovn = l_app_bb_ovn;
786
787 if g_debug then
788 hr_utility.trace('l_item_key is : ' || l_item_key);
789 end if;
790
791 wf_engine.CreateProcess(itemtype => p_itemtype,
792 itemkey => l_item_key,
793 process => l_process_name);
794 wf_engine.setitemowner(p_itemtype,
795 p_item_key,
796 HXC_FIND_NOTIFY_APRS_PKG.get_login(p_person_id=>l_tc_resource_id,
797 p_user_id => l_last_updated_by)
798 );
799
800 open c_csr_get_appl_periods(l_app_bb_id,l_app_bb_ovn);
801
802 fetch c_csr_get_appl_periods into l_period_start_date,
803 l_period_end_date,
804 l_time_recipient,
805 l_recipient_sequence,
806 l_time_category_id,
807 l_category_sequence;
808
809 close c_csr_get_appl_periods;
810 if g_debug then
811 hr_utility.set_location(l_proc, 20);
812 end if;
813
814 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
815 itemkey => l_item_key,
816 aname => 'APP_START_DATE',
817 avalue => l_period_start_date);
818
819 wf_engine.SetItemAttrText(itemtype => p_itemtype,
820 itemkey => l_item_key,
821 aname => 'FORMATTED_APP_START_DATE',
822 avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
823 if g_debug then
824 hr_utility.set_location(l_proc, 30);
825 hr_utility.trace('APP_START_DATE is : ' ||
826 to_char(l_period_start_date, 'DD-MM-YYYY'));
827 end if;
828
829 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
830 itemkey => l_item_key,
831 aname => 'APP_END_DATE',
832 avalue => l_period_end_date);
833
834 if g_debug then
835 hr_utility.set_location(l_proc, 40);
836 hr_utility.trace('APP_END_DATE is : ' ||
837 to_char(l_period_end_date, 'DD-MM-YYYY'));
838 end if;
839
840 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
841 itemkey => l_item_key,
842 aname => 'APP_BB_ID',
843 avalue => l_app_bb_id);
844
845 if g_debug then
846 hr_utility.set_location(l_proc, 50);
847 hr_utility.trace('APP_BB_ID is : ' || to_char(l_app_bb_id));
848 end if;
849
850 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
851 itemkey => l_item_key,
852 aname => 'APP_BB_OVN',
853 avalue => l_app_bb_ovn);
854
855
856 if g_debug then
857 hr_utility.set_location(l_proc, 60);
858 hr_utility.trace('APP_BB_OVN is : ' ||
859 to_char(l_app_bb_ovn));
860 end if;
861
862 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
863 itemkey => l_item_key,
864 aname => 'RESOURCE_ID',
865 avalue => l_tc_resource_id);
866
867 if g_debug then
868 hr_utility.set_location(l_proc, 70);
869 hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
870 end if;
871
872 wf_engine.SetItemAttrText(itemtype => p_itemtype,
873 itemkey => l_item_key,
874 aname => 'TIME_RECIPIENT_ID',
875 avalue => l_time_recipient);
876
877 if g_debug then
878 hr_utility.set_location(l_proc, 80);
879 hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
880 end if;
881
882 wf_engine.SetItemAttrText(itemtype => p_itemtype,
883 itemkey => l_item_key,
884 aname => 'TC_RESUBMITTED',
885 avalue => l_tc_resubmitted);
886
887 if g_debug then
888 hr_utility.set_location(l_proc, 90);
889 hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
890 end if;
891
892 wf_engine.SetItemAttrText(itemtype => p_itemtype,
893 itemkey => l_item_key,
894 aname => 'BB_NEW',
895 avalue => l_bb_new);
896
897 if g_debug then
898 hr_utility.set_location(l_proc, 100);
899 hr_utility.trace('BB_NEW is : ' || l_bb_new);
900 end if;
901
902 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
903 itemkey => l_item_key,
904 aname => 'TC_BLD_BLK_ID',
905 avalue => l_timecard_id);
906
907 if g_debug then
908 hr_utility.set_location(l_proc, 110);
909 hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_timecard_id));
910 end if;
911
912 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
913 itemkey => l_item_key,
914 aname => 'TC_BLD_BLK_OVN',
915 avalue => l_timecard_ovn);
916
917 if g_debug then
918 hr_utility.set_location(l_proc, 120);
919 hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_timecard_ovn));
920 end if;
921 l_approval_style_id := hxc_approval_wf_pkg.get_approval_style_id(l_tc_start_time,
922 l_tc_stop_time,
923 l_tc_resource_id);
924
925 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
926 itemkey => l_item_key,
927 aname => 'APPROVAL_STYLE_ID',
928 avalue => l_approval_style_id);
929
930 l_tc_url :='JSP:OA_HTML/OA.jsp?akRegionCode=HXCAPRVPAGE\&akRegionApplicationId=' ||
931 '809\&retainAM=Y\&Action=Details\&AprvTimecardId=' || l_app_bb_id ||
932 '\&AprvTimecardOvn=' || l_app_bb_ovn ||
933 '\&AprvStartTime=' || to_char(l_period_start_date,'YYYY/MM/DD')||
934 '\&AprvStopTime=' || to_char(l_period_end_date,'YYYY/MM/DD') ||
935 '\&AprvResourceId=' || to_char(l_tc_resource_id) ||
936 '\&OAFunc=HXC_TIME_ENTER'||
937 '\&NtfId=-&#NID-';
938
939 wf_engine.SetItemAttrText(itemtype => p_itemtype,
940 itemkey => l_item_key,
941 aname => 'HXC_TIMECARD_URL',
942 avalue => l_tc_url);
943
944 --
945 -- For bug 4291206, copy the previous approvers
946 -- in the new process
947 -- 115.92 Change.
948 --
949 hxc_approval_wf_util.copy_previous_approvers
950 (p_item_type => p_itemtype,
951 p_current_key => itemkey,
952 p_copyto_key => l_item_key);
953
954 -- Update attribute4 with NOTIFIED and attribute2 with the Item Key.
955
956
957 update hxc_app_period_summary
958 set notification_status = 'NOTIFIED'
959 where application_period_id = l_app_bb_id
960 and application_period_ovn = l_app_bb_ovn;
961
962
963 wf_engine.StartProcess(itemtype => p_itemtype,itemkey => l_item_key);
964
965 p_result := 'COMPLETE';
966
967 exception
968 when others then
969
970 -- The line below records this function call in the error system
971 -- in the case of an exception.
972 --
973 wf_core.context('HXCNOTIFPROCESS', 'hxc_notification_process_pkg.restart_workflow',
974 p_itemtype, l_item_key, to_char(p_actid), p_funcmode);
975 raise;
976 p_result := '';
977 return;
978 end restart_workflow;
979
980
981 Procedure is_transfer(p_itemtype in varchar2,
982 p_itemkey in varchar2,
983 p_actid in number,
984 p_funcmode in varchar2,
985 p_result in out nocopy varchar2) is
986
987 cursor c_get_notification(p_itemtype varchar2,p_itemkey varchar2)
988 is
989 select wn.original_recipient
990 from wf_item_activity_statuses wias,
991 wf_process_activities wpa,
992 wf_notifications wn
993 where wias.item_type = p_itemtype
994 and wias.item_key = p_itemkey
995 and wias.process_activity = wpa.instance_id
996 and wpa.activity_name IN('TC_APR_NOTIFICATION', 'TC_APR_NOTIFICATION_ABS')
997 and wias.notification_id = wn.notification_id;
998
999 l_notification_id number;
1000 l_original_recipient varchar2(50);
1001 l_approver varchar2(50);
1002
1003 BEGIN
1004
1005 open c_get_notification(p_itemtype,p_itemkey);
1006 fetch c_get_notification into l_original_recipient; --This will fetch the notification id that is in action currently
1007 close c_get_notification;
1008
1009 --Get the present owner of the notification
1010 l_approver := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1011 itemkey => p_itemkey,
1012 aname => 'APR_SS_LOGIN' );
1013 --Compare the present owner with the original_recipient, if these not same, then it is a transfer action
1014 --Delegate case:
1015 --l_approver = Approver1
1016 --l_original_recipient = Approver1
1017 --Transfer case:
1018 --l_approver = Approver1
1019 --l_original_recipient = Approver2
1020
1021 if l_approver <> l_original_recipient then
1022 --This condition ensures that we are resetting the attribute only in the case of transfer
1023 --If it is a transfer action set the item attribute APR_SS_LOGIN with the new owner i.e. original_recipient.
1024 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1025 itemkey => p_itemkey,
1026 aname => 'APR_SS_LOGIN',
1027 avalue => l_original_recipient);
1028 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1029 itemkey => p_itemkey,
1030 aname => 'TC_APPROVER_FROM_ROLE',
1031 avalue => l_original_recipient);
1032 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1033 itemkey => p_itemkey,
1034 aname => 'TC_FROM_ROLE',
1035 avalue => l_approver);
1036 end if;
1037
1038 p_result := 'COMPLETE';
1039
1040 END is_transfer;
1041
1042
1043 -- Absences start
1044 -- Bug 8888588 - For notification subject when Absences is set
1045
1046 PROCEDURE exclude_total_hours(p_itemtype in varchar2,
1047 p_itemkey in varchar2,
1048 p_actid in number,
1049 p_funcmode in varchar2,
1050 p_result in out nocopy varchar2) is
1051
1052 l_resource_id per_all_people_f.person_id%type;
1053 l_tc_start_date date;
1054 l_exclude_hours varchar2(1);
1055 l_proc varchar2(50) := 'exclude_total_hours';
1056
1057 BEGIN
1058
1059
1060
1061 l_resource_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1062 itemkey => p_itemkey,
1063 aname => 'RESOURCE_ID' );
1064
1065 l_tc_start_date := wf_engine.GetItemAttrDate(itemtype => p_itemtype,
1066 itemkey => p_itemkey,
1067 aname => 'TC_START');
1068
1069 l_exclude_hours := evaluate_abs_pref(l_resource_id,
1070 trunc(l_tc_start_date)
1071 );
1072
1073 IF not(hxc_approval_wf_pkg.item_attribute_exists(p_itemtype, p_itemkey, 'IS_ABS_ENABLED')) THEN
1074
1075 wf_engine.additemattr
1076 (itemtype => p_itemtype,
1077 itemkey => p_itemkey,
1078 aname => 'IS_ABS_ENABLED',
1079 text_value => 'N');
1080
1081 END IF;
1082
1083
1084 if l_exclude_hours = 'Y' then
1085 wf_engine.SetItemAttrText(
1086 itemtype => p_itemtype,
1087 itemkey => p_itemkey,
1088 aname => 'IS_ABS_ENABLED',
1089 avalue => 'Y');
1090
1091 p_result := 'COMPLETE:Y';
1092
1093 else
1094 wf_engine.SetItemAttrText(
1095 itemtype => p_itemtype,
1096 itemkey => p_itemkey,
1097 aname => 'IS_ABS_ENABLED',
1098 avalue => 'N');
1099
1100 p_result := 'COMPLETE:N';
1101 end if;
1102
1103
1104 EXCEPTION
1105 when others then
1106
1107 -- The line below records this function call in the error system
1108 -- in the case of an exception.
1109 --
1110 if g_debug then
1111 hr_utility.set_location(l_proc, 999);
1112 end if;
1113
1114 wf_core.context('HXCNOTIFPROCESS', 'hxc_notification_process_pkg.exclude_total_hours',
1115 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1116 raise;
1117 p_result := '';
1118 return;
1119
1120 END exclude_total_hours;
1121
1122
1123 FUNCTION evaluate_abs_pref(p_resource_id IN NUMBER,
1124 p_eval_date IN DATE)
1125 return varchar2 is
1126
1127 l_pref_table hxc_preference_evaluation.t_pref_table;
1128 l_abs_integ_enabled varchar2(5);
1129 l_exclude_hours varchar2(1) := 'N';
1130
1131 BEGIN
1132
1133 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
1134 -- Absence Integration Profile is ON
1135 hxc_preference_evaluation.resource_preferences
1136 (p_resource_id => p_resource_id,
1137 p_pref_code_list => 'TS_ABS_PREFERENCES',
1138 p_pref_table => l_pref_table,
1139 p_evaluation_date => trunc(p_eval_date)
1140 );
1141
1142 IF (l_pref_table.COUNT = 1)
1143 THEN
1144 l_abs_integ_enabled := nvl(l_pref_table (l_pref_table.FIRST).attribute1, 'N');
1145
1146 if l_abs_integ_enabled = 'Y' then
1147 l_exclude_hours := nvl(l_pref_table (l_pref_table.FIRST).attribute8, 'N');
1148 else
1149 l_exclude_hours := 'N';
1150 end if;
1151
1152 END IF;
1153
1154 ELSE
1155 -- Absence Integration Profile is OFF
1156 l_exclude_hours := 'N';
1157 END IF;
1158
1159 return l_exclude_hours;
1160
1161 END evaluate_abs_pref;
1162
1163 END hxc_notification_process_pkg;