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