[Home] [Help]
PACKAGE BODY: APPS.HXC_FIND_NOTIFY_APRS_PKG
Source
1 PACKAGE BODY hxc_find_notify_aprs_pkg as
2 /* $Header: hxcafnawf.pkb 120.24.12020000.2 2012/07/04 09:12:00 amnaraya ship $ */
3
4 g_pkg constant varchar2(30) := 'hxc_find_notify_aprs_pkg.';
5 g_trace VARCHAR2(2000);
6
7 g_debug boolean := hr_utility.debug_enabled;
8
9 --
10 -- useful information from time card building block
11 --
12 cursor csr_tc_info(b_tc_bbid number, b_tc_bbovn number) is
13 SELECT tcbb.resource_id,
14 tcbb.start_time,
15 tcbb.stop_time
16 FROM HXC_TIME_BUILDING_BLOCKS tcbb
17 WHERE tcbb.time_building_block_id = b_tc_bbid
18 and tcbb.object_version_number = b_tc_bbovn;
19
20 --
21 -- useful information from application period building block
22 --
23 cursor csr_ap_info(b_ap_bbid number, b_ap_bbovn number) is
24 SELECT apbb.resource_id,
25 apbb.start_time,
26 apbb.stop_time,
27 ta.attribute1 recipient_app
28 FROM HXC_TIME_ATTRIBUTES ta,
29 HXC_TIME_ATTRIBUTE_USAGES tau,
30 HXC_TIME_BUILDING_BLOCKS apbb
31 WHERE apbb.time_building_block_id = b_ap_bbid
32 and apbb.object_version_number = b_ap_bbovn
33 and tau.time_building_block_id =
34 apbb.time_building_block_id
35 and tau.time_building_block_ovn =
36 apbb.object_version_number
37 and ta.time_attribute_id =
38 tau.time_attribute_id;
39
40 --
41 -- find approval component associated with earliest day building block
42 --
43 cursor csr_apr_comp(b_ap_bbid number, b_ap_bbovn number,
44 b_time_recipient_id number) is
45 SELECT apc.approval_mechanism,
46 apc.approval_mechanism_id,
47 apc.wf_item_type,
48 apc.wf_name
49 FROM HXC_APPROVAL_COMPS apc,
50 HXC_APPROVAL_STYLES aps,
51 HXC_TIME_BUILDING_BLOCKS htb_time,
52 hxc_time_building_blocks htb_appln
53 WHERE htb_appln.TIME_BUILDING_BLOCK_ID = b_ap_bbid AND
54 htb_appln.object_version_number = b_ap_bbovn
55 AND
56 htb_time.resource_id = htb_appln.resource_id AND
57 htb_time.start_time <= htb_appln.stop_time AND
58 htb_time.stop_time >= htb_appln.start_time AND
59 htb_time.scope = 'TIMECARD' AND
60 htb_time.date_to = hr_general.end_of_time
61 AND
62 aps.approval_style_id = htb_time.approval_style_id
63 AND
64 apc.approval_style_id = aps.approval_style_id AND
65 apc.time_recipient_id = b_time_recipient_id;
66
67 --
68 -- global context information
69 --
70 type info_t is record
71 (
72 -- Time Card info
73 tc csr_tc_info%rowtype,
74
75 -- Application Period info
76 ap csr_ap_info%rowtype,
77
78 -- Approval Componets info
79 ac csr_apr_comp%rowtype
80 );
81
82 g_info info_t;
83
84
85
86 --
87 -- raise an assert if 'expression' is false
88 --
89 procedure assert(
90 p_expression in boolean,
91 p_message in varchar2)
92 is
93 begin
94 if not p_expression then
95 hr_utility.set_message(801, 'FFPLU01_ASSERTION_FAILED');
96 hr_utility.set_message_token('LOCATION', p_message);
97 hr_utility.raise_error;
98 end if;
99 end assert;
100
101
102
103 -- Format the value according to the provided
104 -- precision and rounding rule.
105 --
106 FUNCTION apply_round_rule(p_rounding_rule in varchar2,
107 p_decimal_precision in varchar2,
108 p_value in number)
109 return number
110 IS
111 l_value number;
112 l_precision number;
113
114 BEGIN
115
116 l_precision := to_number(p_decimal_precision);
117
118 l_value := p_value;
119 if (p_rounding_rule = 'ROUND_DOWN')
120 then
121 l_value := trunc(l_value,l_precision);
122 elsif (p_rounding_rule = 'ROUND_TO_NEAREST')
123 then
124 l_value := round(l_value,l_precision);
125 elsif (p_rounding_rule = 'ROUND_UP')
126 then
127 if( l_value > trunc(l_value,l_precision))
128 then
129 l_value := trunc(l_value,l_precision) + power(0.1,l_precision);
130 end if;
131 else
132 l_value := round(l_value,l_precision);
133 end if;
134
135 return l_value;
136 end apply_round_rule;
137
138
139 --
140 -- from person id and effective date find username,
141 -- name must exist for duration of time card
142 --
143
144 -- Bug 3562755,3855544
145 function get_name(
146 p_person_id in number,
147 p_effective_date in DATE)
148 return varchar2
149 is
150 cursor csr_name(b_person_id NUMBER, b_effective_date DATE) is
151 select p.full_name from per_people_f p
152 where p.person_id=b_person_id
153 and b_effective_date between p.effective_start_date and p.effective_end_date;
154
155 cursor csr_closest_name1
156 (b_person_id in number,b_effective_date date) is
157 select full_name
158 from per_all_people_f
159 where person_id = b_person_id
160 and (effective_end_date=(select max(effective_end_date) from per_all_people_f
161 where person_id=b_person_id and(effective_end_date<= b_effective_date)));
162
163
164 cursor csr_closest_name2(b_person_id in number,b_effective_date date) is
165 select full_name
166 from per_all_people_f
167 where person_id = b_person_id
168 and (effective_start_date=(select min(effective_start_date) from per_all_people_f
169 where person_id=b_person_id and
170 (effective_start_date>= b_effective_date)));
171
172
173 l_temp_name per_all_people_f.full_name%type;
174 l_name wf_users.name%type;
175 l_display_name wf_users.display_name%type;
176
177 begin
178
179 wf_directory.getusername( p_orig_system => 'PER',
180 p_orig_system_id => p_person_id,
181 p_name => l_name,
182 p_display_name => l_display_name);
183
184 if l_display_name is null then
185
186 open csr_name(p_person_id, trunc(p_effective_date));
187 fetch csr_name into l_temp_name;
188 if csr_name%notfound then
189 open csr_closest_name1(p_person_id,trunc(p_effective_date));
190 fetch csr_closest_name1 into l_temp_name;
191 if csr_closest_name1%notfound then
192 open csr_closest_name2(p_person_id,trunc(p_effective_date));
193 fetch csr_closest_name2 into l_temp_name;
194 close csr_closest_name2;
195 end if;
196 close csr_closest_name1;
197 end if;
198 l_display_name:=l_temp_name;
199 close csr_name;
200 end if;
201 return l_display_name;
202
203 end get_name;
204
205 --
206 -- from person id and effective date find supervisor
207 --
208 function get_supervisor(
209 p_person_id in number,
210 p_effective_date in date)
211 return number
212 is
213
214 cursor csr_get_supervisor(b_person_id number, b_effective_date date) is
215 select asg.supervisor_id
216 from per_all_assignments_f asg
217 where asg.person_id = b_person_id
218 and TRUNC(b_effective_date) between TRUNC(asg.effective_Start_date) and TRUNC(asg.effective_end_date)
219 and asg.primary_flag = 'Y'
220 and asg.assignment_type in ('E','C');
221
222 l_supervisor_id per_all_assignments_f.person_id%type;
223 begin
224 open csr_get_supervisor(p_person_id, trunc(p_effective_date));
225 fetch csr_get_supervisor into l_supervisor_id;
226 close csr_get_supervisor;
227
228 return l_supervisor_id;
229 end get_supervisor;
230
231
232 --
233 -- from person id find self service login name
234 --,
235 function get_login(
236 p_person_id in number,
237 p_user_id In NUMBER DEFAULT NULL)
238 return varchar2
239 is
240 l_name wf_local_roles.name%type;
241 l_display_name wf_local_roles.display_name%type;
242
243 -- Bug 3855544
244
245 cursor c_get_user(p_usr_id NUMBER) is
246 select user_name from fnd_user
247 where user_id=p_usr_id;
248
249 begin
250 -- Bug 3390666
251 -- Fetch the role from wf table instead of fnd_user table.
252 wf_directory.GetUserName(p_orig_system => 'PER',
253 p_orig_system_id => p_person_id,
254 p_name => l_name,
255 p_display_name => l_display_name);
256 --
257 -- if no data found, person does not have a self service login name,
258 -- client must handle this and send notification appropriately
259 --
260
261 if l_name is NULL and p_user_id is not NULL then
262 open c_get_user(p_user_id);
263 fetch c_get_user into l_name;
264 close c_get_user;
265 end if;
266
267
268 return l_name;
269 end get_login;
270
271 --
272 -- +------------------------------------------------------------------------+
273 -- | get_description |
274 -- +------------------------------------------------------------------------+
275 --
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- This procedure is used to set the description text on a timecard
280 -- notification, e.g. 3 total hours (0 premium hours, 0 non worked hours).
281 -- The tokens are only set if the message contains
282 -- the token, thus permitting the customers to turn off bits of the
283 -- description they don't want. This was added for ER 5748501.
284 --
285 -- Prerequisites:
286 -- None.
287 --
288 -- In Parameter:
289 -- p_constraint_name is in upper format and is just the constraint name
290 -- (e.g. not prefixed by brackets, schema owner etc).
291 --
292 -- Post Success:
293 -- Development dependant.
294 --
295 -- Post Failure:
296 -- Developement dependant.
297 --
298 -- Developer Implementation Notes:
299 -- Called from hr_supervisor_approval *and* person_approval
300 --
301 -- Access Status:
302 -- Internal Development Use Only.
303 --
304 -- {End Of Comments}
305 function get_description
306 (p_application_period_id in hxc_app_period_summary.application_period_id%type)
307 return varchar2 is
308
309 l_description fnd_new_messages.message_text%type;
310 begin
311
312 fnd_message.set_name('HXC','HXC_APPR_WF_DESCRIPTION');
313 l_description := fnd_message.get();
314 -- After getting the message, must reset it
315
316 fnd_message.set_name('HXC','HXC_APPR_WF_DESCRIPTION');
317 if(instr(l_description,'TOTAL_HOURS')>0) then
318 fnd_message.set_token('TOTAL_HOURS',category_timecard_hrs(p_application_period_id,''));
319 end if;
320
321 if(instr(l_description,'PREMIUM_HOURS')>0) then
322 fnd_message.set_token('PREMIUM_HOURS',category_timecard_hrs(p_application_period_id,'Total2'));
323 end if;
324
325 if(instr(l_description,'NON_WORKED_HOURS')>0) then
326 fnd_message.set_token('NON_WORKED_HOURS',category_timecard_hrs(p_application_period_id,'Total3'));
327 end if;
328
329 return fnd_message.get();
330
331 end get_description;
332
333 function get_description_tc
334 (p_timecard_id in hxc_timecard_summary.timecard_id%type,
335 p_timecard_ovn in hxc_timecard_summary.timecard_ovn%type)
336 return varchar2 is
337
338 l_description fnd_new_messages.message_text%type;
339
340 begin
341
342 fnd_message.set_name('HXC','HXC_APPR_WF_DESCRIPTION');
343 l_description := fnd_message.get();
344 -- After getting the message, must reset it
345
346 fnd_message.set_name('HXC','HXC_APPR_WF_DESCRIPTION');
347 if(instr(l_description,'TOTAL_HOURS')>0) then
348 fnd_message.set_token
349 ('TOTAL_HOURS',
350 hxc_time_category_utils_pkg.category_timecard_hrs_ind(p_timecard_id,p_timecard_ovn,'')
351 );
352 end if;
353
354 if(instr(l_description,'PREMIUM_HOURS')>0) then
355 fnd_message.set_token
356 ('PREMIUM_HOURS',
357 hxc_time_category_utils_pkg.category_timecard_hrs_ind(p_timecard_id, p_timecard_ovn,'Total2')
358 );
359 end if;
360
361 if(instr(l_description,'NON_WORKED_HOURS')>0) then
362 fnd_message.set_token
363 ('NON_WORKED_HOURS',
364 hxc_time_category_utils_pkg.category_timecard_hrs_ind(p_timecard_id, p_timecard_ovn,'Total3')
365 );
366 end if;
367
368 return fnd_message.get();
369 end get_description_tc;
370
371 function get_description_date
372 (p_start_date in date,
373 p_end_date in date,
374 p_resource_id in number)
375 return varchar2 is
376
377 l_description fnd_new_messages.message_text%type;
378
379 begin
380
381 fnd_message.set_name('HXC','HXC_APPR_WF_DESCRIPTION');
382 l_description := fnd_message.get();
383 -- After getting the message, must reset it
384
385 fnd_message.set_name('HXC','HXC_APPR_WF_DESCRIPTION');
386 if(instr(l_description,'TOTAL_HOURS')>0) then
387 fnd_message.set_token('TOTAL_HOURS',category_timecard_hrs(p_start_date,p_end_date,p_resource_id,''));
388 end if;
389
390 if(instr(l_description,'PREMIUM_HOURS')>0) then
391 fnd_message.set_token('PREMIUM_HOURS',category_timecard_hrs(p_start_date,p_end_date,p_resource_id,'Total2'));
392 end if;
393
394 if(instr(l_description,'NON_WORKED_HOURS')>0) then
395 fnd_message.set_token('NON_WORKED_HOURS',category_timecard_hrs(p_start_date,p_end_date,p_resource_id,'Total3'));
396 end if;
397
398 return fnd_message.get();
399 end get_description_date;
400
401 --this procedure is called to cancel the other outstanding
402 --notifications for the same application period.
403 procedure cancel_notifications(
404 p_app_bb_id IN NUMBER,
405 p_archived IN VARCHAR DEFAULT NULL)
406
407 IS
408 -- Bug 3345143.
409 -- Modified the cursor to fetch item_key associated to the Open notification.
410 -- Bug 3549092.
411 -- Changed the get item key cursor so that the item keys
412 -- are only obtained from the item attribute values table.
413 -- We then **require** the Begin..End around the abort process
414 -- because this can obtain completed workflows. In this case
415 -- the abort process call will fail, but nothing is wrong.
416 --
417
418 CURSOR c_get_old_style_item_key(
419 p_app_bb_id IN NUMBER
420 )
421 IS
422 select wiav.item_key
423 from wf_item_attribute_values wiav
424 where wiav.item_type = 'HXCEMP'
425 and wiav.name = 'APP_BB_ID'
426 and wiav.NUMBER_VALUE = p_app_bb_id;
427
428 CURSOR c_get_item_key(
429 p_app_bb_id IN NUMBER )
430 IS
431 select approval_item_key
432 from hxc_app_period_summary
433 where application_period_id = p_app_bb_id;
434
435 l_item_key varchar2(240);
436 l_old_style_flag BOOLEAN;
437
438 BEGIN
439 -- Bug 3345143
440 --cancel all the notifications associated with current
441 --application period and abort the current process.
442
443 g_debug := hr_utility.debug_enabled;
444
445
446 if g_debug then
447 hr_utility.trace('cancelling notifications for ' || p_app_bb_id);
448 end if;
449 -- OPEN c_duplicate_notifications(p_app_bb_id);
450 -- LOOP
451 -- FETCH c_duplicate_notifications into l_notification_id;
452 -- EXIT WHEN c_duplicate_notifications%NOTFOUND;
453 -- wf_notification.cancel(l_notification_id,
454 -- 'canceled because a new notification is sent');
455 -- if g_debug then
456 -- hr_utility.trace('cancelled ' || l_notification_id);
457 -- end if;
458 -- END LOOP;
459 -- CLOSE c_duplicate_notifications;
460
461 l_old_style_flag :=FALSE;
462
463 open c_get_item_key(p_app_bb_id);
464 Loop
465 fetch c_get_item_key into l_item_key;
466 Exit when c_get_item_key%NOTFOUND;
467 Begin
468
469 IF l_item_key IS NULL THEN
470
471 l_old_style_flag :=TRUE;
472
473 OPEN c_get_old_style_item_key(p_app_bb_id);
474 Loop fetch c_get_old_style_item_key into l_item_key;
475 Exit when c_get_old_style_item_key%NOTFOUND;
476 BEGIN
477 --Updating the WF_NOTIFICATION_ATTRIBUTES, incase of archival.
478 IF (p_archived = 'Yes') THEN
479 hxc_archive_restore_utils.upd_wf_notif_attributes(p_item_type => 'HXCEMP',p_item_key => l_item_key);
480 END IF;
481 wf_engine.AbortProcess(itemkey => l_item_key,
482 itemtype => 'HXCEMP');
483 EXCEPTION
484 When others then
485 null;
486 END;
487 End Loop;
488 CLOSE c_get_old_style_item_key;
489 ELSE
490 --Updating the WF_NOTIFICATION_ATTRIBUTES, incase of archival.
491 IF (p_archived = 'Yes') THEN
492 hxc_archive_restore_utils.upd_wf_notif_attributes(p_item_type => 'HXCEMP',p_item_key => l_item_key);
493 END IF;
494 wf_engine.AbortProcess(itemkey => l_item_key,
495 itemtype => 'HXCEMP');
496 END IF;
497
498 Exception
499 When others then
500 -- Was probably a complete workflow. Ignore.
501 null;
502 End;
503
504 EXIT when l_old_style_flag;
505
506 End Loop;
507 close c_get_item_key;
508
509 END cancel_notifications;
510
511 procedure cancel_previous_notifications
512 (p_app_bb_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
513 ,p_app_bb_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
514 ) is
515
516 cursor c_appl_periods
517 (p_app_bb_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
518 ,p_app_bb_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
519 ) IS
520 select distinct apps.time_building_block_id
521 from hxc_time_building_blocks apps
522 ,hxc_time_building_blocks refapps
523 where refapps.resource_id = apps.resource_id
524 and refapps.scope = apps.scope
525 and refapps.date_to = hr_general.end_of_time
526 and refapps.start_time <= apps.stop_time
527 and refapps.stop_time >= apps.start_time
528 and ((refapps.time_building_block_id <> apps.time_building_block_id)
529 OR
530 ((refapps.object_version_number <> apps.object_version_number)
531 AND
532 (refapps.time_building_block_id = apps.time_building_block_id))
533 )
534 and refapps.time_building_block_id = p_app_bb_id
535 and refapps.object_version_number = p_app_bb_ovn;
536
537 BEGIN
538
539 FOR ap_rec in c_appl_periods(p_app_bb_id,p_app_bb_ovn) LOOP
540
541
542 cancel_notifications(ap_rec.time_building_block_id);
543
544
545 END LOOP;
546
547 END cancel_previous_notifications;
548
549 procedure cancel_previous_notifications(
550 p_itemtype IN varchar2
551 ,p_itemkey in varchar2
552 )
553 IS
554 l_resource_id NUMBER;
555 l_tc_start DATE;
556 l_tc_stop DATE;
557 l_app_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
558
559 CURSOR c_appl_periods(
560 p_tc_start_time IN hxc_time_building_blocks.start_time%TYPE
561 ,p_tc_stop_time IN hxc_time_building_blocks.stop_time%TYPE
562 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
563 )
564 IS
565 SELECT time_building_block_id
566 FROM hxc_time_building_blocks
567 WHERE scope = 'APPLICATION_PERIOD'
568 AND resource_id = p_resource_id
569 AND date_to = hr_general.end_of_time
570 AND TRUNC(start_time) <= TRUNC(p_tc_stop_time)
571 AND TRUNC(stop_time) >= TRUNC(p_tc_start_time);
572
573
574 BEGIN
575 --cancel all the notifications for all the other application periods
576 --associated with the submitted timecard
577
578 l_resource_id := wf_engine.GetItemAttrNumber(
579 itemtype => p_itemtype,
580 itemkey => p_itemkey,
581 aname => 'RESOURCE_ID');
582
583 l_tc_start := wf_engine.GetItemAttrDate(
584 itemtype => p_itemtype,
585 itemkey => p_itemkey,
586 aname => 'TC_START');
587
588 l_tc_stop := wf_engine.GetItemAttrDate(
589 itemtype => p_itemtype,
590 itemkey => p_itemkey,
591 aname => 'TC_STOP');
592
593 OPEN c_appl_periods(
594 p_tc_start_time => l_tc_start
595 ,p_tc_stop_time => l_tc_stop
596 ,p_resource_id => l_resource_id
597 );
598
599 LOOP
600 FETCH c_appl_periods into l_app_bb_id;
601 EXIT WHEN c_appl_periods%NOTFOUND;
602
603 cancel_notifications(l_app_bb_id);
604 END LOOP;
605
606 CLOSE c_appl_periods;
607
608 END cancel_previous_notifications;
609
610
611
612
613 PROCEDURE cancel_previous_notifications(
614 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
615 )
616 IS
617
618 --bug 4946511.
619
620 CURSOR c_app_periods(
621 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
622 )
623 IS
624 SELECT aps.application_period_id
625 FROM hxc_tc_ap_links tcl, hxc_app_period_summary aps
626 WHERE tcl.timecard_id = p_timecard_id
627 AND tcl.application_period_id = aps.application_period_id
628 AND aps.notification_status = 'NOTIFIED';
629
630 l_app_id hxc_time_building_blocks.time_building_block_id%TYPE;
631
632 BEGIN
633 g_debug:=hr_utility.debug_enabled;
634 OPEN c_app_periods(p_timecard_id);
635
636 LOOP
637 FETCH c_app_periods INTO l_app_id;
638
639 EXIT WHEN c_app_periods%NOTFOUND;
640
641 if g_debug then
642 hr_utility.trace('in cancelling notifications: found app id=' || l_app_id);
643 end if;
644 cancel_notifications(l_app_id);
645 END LOOP;
646
647 END cancel_previous_notifications;
648
649
650 --
651 -- nothing workflow specific here
652 --
653 procedure get_context_info(
654 p_ap_bb_id in number,
655 p_ap_bb_ovn in number,
656 p_tc_bb_id in number default null,
657 p_tc_bb_ovn in number default null)
658 is
659 l_proc constant varchar2(61) := g_pkg || '.' || 'get_context_info';
660 begin
661 --
662 -- initalise global information structure
663 --
664 g_info.tc.resource_id := null;
665 g_info.tc.start_time := null;
666 g_info.tc.stop_time := null;
667
668 g_info.ap.resource_id := null;
669 g_info.ap.start_time := null;
670 g_info.ap.stop_time := null;
671 g_info.ap.recipient_app := null;
672
673 g_info.ac.approval_mechanism := null;
674 g_info.ac.approval_mechanism_id := null;
675 g_info.ac.wf_item_type := null;
676 g_info.ac.wf_name := null;
677
678 --
679 -- get time card information if calling in notification mode
680 --
681 if p_tc_bb_id is not null then
682 open csr_tc_info(p_tc_bb_id, p_tc_bb_ovn);
683 fetch csr_tc_info into g_info.tc;
684
685 if csr_tc_info%notfound then
686 close csr_tc_info;
687 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
688 hr_utility.set_message_token('PROCEDURE', l_proc);
689 hr_utility.set_message_token('STEP', '10');
690 hr_utility.raise_error;
691 end if;
692
693 close csr_tc_info;
694 end if;
695
696 --
697 -- get application period information
698 --
699 open csr_ap_info(p_ap_bb_id, p_ap_bb_ovn);
700 fetch csr_ap_info into g_info.ap;
701
702 if csr_ap_info%notfound then
703 close csr_ap_info;
704 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
705 hr_utility.set_message_token('PROCEDURE', l_proc);
706 hr_utility.set_message_token('STEP', '20');
707 hr_utility.raise_error;
708 end if;
709
710 close csr_ap_info;
711
712 --
713 -- get approval component for recipient application,
714 -- if no component exists then no approval required
715 --
716 open csr_apr_comp(p_ap_bb_id, p_ap_bb_ovn, g_info.ap.recipient_app);
717 fetch csr_apr_comp into g_info.ac;
718 close csr_apr_comp;
719
720 --
721 -- dump out pertinent information
722 --
723 --sb_msgs_pkg.trace('g_info.tc.resource_id >' || g_info.tc.resource_id || '<');
724 --sb_msgs_pkg.trace('g_info.tc.start_time >' || g_info.tc.start_time || '<');
725 --sb_msgs_pkg.trace('g_info.tc.stop_time >' || g_info.tc.stop_time || '<');
726
727 --sb_msgs_pkg.trace('g_info.ap.resource_id >' || g_info.ap.resource_id || '<');
728 --sb_msgs_pkg.trace('g_info.ap.start_time >' || g_info.ap.start_time || '<');
729 --sb_msgs_pkg.trace('g_info.ap.stop_time >' || g_info.ap.stop_time || '<');
730 --sb_msgs_pkg.trace('g_info.ap.recipient_app >' || g_info.ap.recipient_app || '<');
731
732 --sb_msgs_pkg.trace('g_info.ac.approval_mechanism >' || g_info.ac.approval_mechanism || '<');
733 --sb_msgs_pkg.trace('g_info.ac.approval_mechanism_id>' || g_info.ac.approval_mechanism_id || '<');
734 --sb_msgs_pkg.trace('g_info.ac.wf_item_type >' || g_info.ac.wf_item_type || '<');
735 --sb_msgs_pkg.trace('g_info.ac.wf_name >' || g_info.ac.wf_name || '<');
736 end get_context_info;
737
738 --
739 -- for application period building block find type of approval required
740 --
741 procedure find_apr_style(
742 p_itemtype in varchar2,
743 p_itemkey in varchar2,
744 p_actid in number,
745 p_funcmode in varchar2,
746 p_result in out nocopy varchar2)
747 is
748
749 CURSOR c_approval_comp(
750 p_ap_bb_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
751 ,p_ap_bb_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
752 )
753 IS
754 SELECT apc.approval_mechanism,
755 apc.approval_mechanism_id,
756 apc.wf_item_type,
757 apc.wf_name
758 FROM hxc_app_period_summary apsum
759 ,hxc_approval_comps apc
760 WHERE apsum.application_period_id = p_ap_bb_id
761 AND apsum.application_period_ovn = p_ap_bb_ovn
762 AND apsum.approval_comp_id = apc.approval_comp_id
763
764 -- Bug 10421528 -- Commented this regression
765 -- AND apsum.time_recipient_id = apc.time_recipient_id; -- Added time recipient check for bug 9747501
766 --
767 -- The above AND clause would fail for ELA where the actual recipient name would
768 -- come up for the parent_comp_id rather than for the approval comp id.
769 -- Modified the condition this way so that it connect back to the parent_comp_id
770
771 AND ( apsum.time_recipient_id = apc.time_recipient_id
772 OR ( EXISTS ( SELECT 1
773 FROM hxc_approval_comps apc2
774 WHERE apc2.approval_comp_id = apc.parent_comp_id
775 AND apc2.approval_mechanism = 'ENTRY_LEVEL_APPROVAL'
776 AND apc2.time_recipient_id = apsum.time_recipient_id
777 )
778 )
779 );
780
781
782
783 cursor c_approval_comp_alt
784 (p_application_period_id in hxc_app_period_summary.application_period_id%type
785 ,p_application_period_ovn in hxc_app_period_summary.application_period_ovn%type) is
786 SELECT apc.approval_mechanism,
787 apc.approval_mechanism_id,
788 apc.wf_item_type,
789 apc.wf_name
790 FROM hxc_app_period_summary apsum
791 ,hxc_approval_comps apc
792 ,hxc_tc_ap_links tcl
793 ,hxc_time_building_blocks tbb
794 WHERE apsum.application_period_id = p_application_period_id
795 AND apsum.application_period_ovn = p_application_period_ovn
796 and apsum.application_period_id = tcl.application_period_id
797 and tcl.timecard_id = tbb.time_building_block_id
798 and tbb.scope = 'TIMECARD'
799 and tbb.date_to = hr_general.end_of_time
800 and tbb.approval_style_id = apc.approval_style_id
801 and apsum.time_recipient_id = apc.time_recipient_id;
802
803 CURSOR c_tc_info(
804 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
805 )
806 IS
807 SELECT tcsum.resource_id,
808 tcsum.start_time,
809 tcsum.stop_time
810 FROM hxc_timecard_summary tcsum
811 WHERE tcsum.timecard_id = p_tc_bbid;
812
813
814 l_proc constant varchar2(61) := g_pkg || '.' || 'find_apr_style';
815 l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type;
816 l_tc_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
817 l_ap_bb_id hxc_time_building_blocks.time_building_block_id%type;
818 l_ap_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
819 l_login fnd_user.user_name%type;
820 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
821 l_tc_start_time hxc_time_building_blocks.start_time%TYPE;
822 l_tc_stop_time hxc_time_building_blocks.stop_time%TYPE;
823 l_approval_mechanism hxc_approval_comps.approval_mechanism%TYPE;
824 l_approval_mechanism_id hxc_approval_comps.approval_mechanism_id%TYPE;
825 l_wf_item_type hxc_approval_comps.wf_item_type%TYPE;
826 l_wf_name hxc_approval_comps.wf_name%TYPE;
827 begin
828 g_debug:=hr_utility.debug_enabled;
829 --sb_msgs_pkg.begin_call(l_proc);
830 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
831 g_trace := '10';
832
833 if p_funcmode = 'RUN' then
834 l_tc_bb_id := wf_engine.GetItemAttrNumber(
835 itemtype => p_itemtype,
836 itemkey => p_itemkey,
837 aname => 'TC_BLD_BLK_ID');
838
839 l_tc_bb_ovn := wf_engine.GetItemAttrNumber(
840 itemtype => p_itemtype,
841 itemkey => p_itemkey,
842 aname => 'TC_BLD_BLK_OVN');
843
844 l_ap_bb_id := wf_engine.GetItemAttrNumber(
845 itemtype => p_itemtype,
846 itemkey => p_itemkey,
847 aname => 'APP_BB_ID');
848
849 l_ap_bb_ovn := wf_engine.GetItemAttrNumber(
850 itemtype => p_itemtype,
851 itemkey => p_itemkey,
852 aname => 'APP_BB_OVN');
853
854 g_trace := '20';
855
856 if l_tc_bb_id is null or l_tc_bb_ovn is null or
857 l_ap_bb_id is null or l_ap_bb_ovn is null then
858 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
859 hr_utility.set_message_token('PROCEDURE', l_proc);
860 hr_utility.set_message_token('STEP', '10');
861 hr_utility.raise_error;
862 end if;
863
864 g_trace := '30';
865
866
867 --fetch timecard detail
868 open c_tc_info(l_tc_bb_id);
869 fetch c_tc_info into l_resource_id, l_tc_start_time, l_tc_stop_time;
870
871 if c_tc_info%notfound then
872
873 close c_tc_info;
874 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
875 hr_utility.set_message_token('PROCEDURE', l_proc);
876 hr_utility.set_message_token('STEP', '12');
877 hr_utility.raise_error;
878 end if;
879
880 close c_tc_info;
881
882
883 g_trace := '40';
884
885 --fetch approval detail
886 if g_debug then
887 hr_utility.trace('app id=' || l_ap_bb_id);
888 hr_utility.trace('app ovn=' || l_ap_bb_ovn);
889 end if;
890 OPEN c_approval_comp(l_ap_bb_id, l_ap_bb_ovn);
891 FETCH c_approval_comp INTO l_approval_mechanism
892 ,l_approval_mechanism_id
893 ,l_wf_item_type
894 ,l_wf_name;
895
896
897 IF c_approval_comp%NOTFOUND
898 THEN
899 g_trace := '50';
900
901 CLOSE c_approval_comp;
902
903 --
904 -- This should never happen, but if it does
905 -- try getting the approval component info
906 -- using the other cursor
907 --
908
909 open c_approval_comp_alt(l_ap_bb_id, l_ap_bb_ovn);
910 FETCH c_approval_comp_alt INTO l_approval_mechanism
911 ,l_approval_mechanism_id
912 ,l_wf_item_type
913 ,l_wf_name;
914
915 if(c_approval_comp_alt%notfound) then
916 close c_approval_comp_alt;
917 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
918 hr_utility.set_message_token('PROCEDURE', l_proc);
919 hr_utility.set_message_token('STEP', '18');
920 hr_utility.raise_error;
921 else
922 close c_approval_comp_alt;
923 end if;
924
925 else
926 CLOSE c_approval_comp;
927 END IF;
928 if g_debug then
929 hr_utility.trace('mechanism=' || l_approval_mechanism);
930 hr_utility.trace('mechanism_id=' || l_approval_mechanism_id);
931 hr_utility.trace('item_type=' || l_wf_item_type);
932 hr_utility.trace('wf_name=' || l_wf_name);
933 end if;
934 g_trace := '60';
935
936 -- set tokens used by all notifications
937 --
938 wf_engine.SetItemAttrText(
939 itemtype => p_itemtype,
940 itemkey => p_itemkey,
941 aname => 'TC_OWNER',
942 avalue => get_name(l_resource_id,l_tc_start_time)
943 );
944
945 g_trace := '70';
946 --
947 -- set attribute to specify timecard owner's self service login
948 --
949 l_login := get_login(p_person_id => l_resource_id);
950
951 g_trace := '80';
952
953 --
954 -- if null returned, timecard owner does not have a self
955 -- service login name, where does notification get sent?
956 --
957 if l_login is null then
958 g_trace := '90';
959
960 -- 5027063: Try creating an adhoc user
961 begin
962
963 l_login := hxc_approval_helper.createAdHocUser
964 (p_resource_id => l_resource_id,
965 p_effective_date => l_tc_start_time
966 );
967
968 exception
969 when others then
970 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
971 hr_utility.set_message_token('PROCEDURE', l_proc);
972 hr_utility.set_message_token('STEP', '20');
973 hr_utility.raise_error;
974 end;
975
976 end if;
977
978 g_trace := '100';
979
980 --set role attribute
981 wf_engine.SetItemAttrText(
982 itemtype => p_itemtype,
983 itemkey => p_itemkey,
984 aname => 'TC_FROM_ROLE',
985 avalue => l_login);
986
987 g_trace := '110';
988
989 wf_engine.SetItemAttrText(itemtype => p_itemtype,
990 itemkey => p_itemkey,
991 aname => 'TC_OWNER_SS_LOGIN',
992 avalue => l_login);
993
994 g_trace := '120';
995
996 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
997 itemkey => p_itemkey,
998 aname => 'TC_START',
999 avalue => l_tc_start_time);
1000 g_trace := '130';
1001
1002 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
1003 itemkey => p_itemkey,
1004 aname => 'TC_STOP',
1005 avalue => l_tc_stop_time);
1006
1007 g_trace := '140';
1008
1009 if l_approval_mechanism = 'AUTO_APPROVE'
1010 then
1011
1012 g_trace := '150';
1013 p_result := 'COMPLETE:AUTO_APPROVE';
1014
1015 elsif l_approval_mechanism = 'PERSON'
1016 then
1017 g_trace := '160';
1018 --
1019 -- set parameters required by next activity
1020 --
1021 wf_engine.SetItemAttrNumber(
1022 itemtype => p_itemtype,
1023 itemkey => p_itemkey,
1024 aname => 'APR_PERSON_ID',
1025 avalue => l_approval_mechanism_id);
1026
1027 g_trace := '170';
1028 p_result := 'COMPLETE:PERSON';
1029
1030 elsif l_approval_mechanism = 'HR_SUPERVISOR'
1031 then
1032 g_trace := '180';
1033 wf_engine.SetItemAttrNumber(
1034 itemtype => p_itemtype,
1035 itemkey => p_itemkey,
1036 aname => 'APR_PERSON_ID',
1037 avalue => l_resource_id);
1038
1039 g_trace := '190';
1040 p_result := 'COMPLETE:HR_SUPERVISOR';
1041
1042 -- GPaytonM fix version 115.6
1043
1044 elsif l_approval_mechanism = 'FORMULA_MECHANISM'
1045 then
1046 g_trace := '200';
1047 wf_engine.SetItemAttrNumber(
1048 itemtype => p_itemtype,
1049 itemkey => p_itemkey,
1050 aname => 'FORMULA_ID',
1051 avalue => l_approval_mechanism_id);
1052
1053 g_trace := '210';
1054 p_result := 'COMPLETE:FORMULA';
1055
1056 elsif l_approval_mechanism = 'WORKFLOW'
1057 then
1058
1059 g_trace := '220';
1060 wf_engine.SetItemAttrText(
1061 itemtype => p_itemtype,
1062 itemkey => p_itemkey,
1063 aname => 'WF_ITEM_TYPE',
1064 avalue => l_wf_item_type);
1065
1066 wf_engine.SetItemAttrText(
1067 itemtype => p_itemtype,
1068 itemkey => p_itemkey,
1069 aname => 'WF_PROCESS_NAME',
1070 avalue => l_wf_name);
1071
1072 g_trace := '230';
1073 p_result := 'COMPLETE:WORKFLOW';
1074
1075 elsif l_approval_mechanism = 'PROJECT_MANAGER'
1076 then
1077 g_trace := '240';
1078 p_result := 'COMPLETE:PROJECT_MANAGER';
1079
1080 else
1081 g_trace := '250';
1082 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1083 hr_utility.set_message_token('PROCEDURE', l_proc);
1084 hr_utility.set_message_token('STEP', '30');
1085 hr_utility.raise_error;
1086 end if;
1087
1088 end if;
1089
1090 g_trace := '260';
1091 if p_funcmode = 'CANCEL' then
1092 p_result := 'COMPLETE';
1093 end if;
1094
1095 g_trace := '270';
1096 if p_funcmode = 'TIMEOUT' then
1097 p_result := 'COMPLETE';
1098 end if;
1099
1100 g_trace := '300';
1101 exception
1102 when others then
1103
1104 --
1105 -- record this function call in the error system in case of an exception
1106 --
1107 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1108 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode,
1109 l_proc || '|' || g_trace);
1110 raise;
1111 end find_apr_style;
1112
1113
1114 PROCEDURE set_real_approver(
1115 p_itemtype in varchar2
1116 ,p_itemkey in varchar2
1117 )
1118 IS
1119 l_employee_id fnd_user.employee_id%TYPE;
1120 l_real_approver VARCHAR2(500);
1121 l_appr_ss_login VARCHAR2(100);
1122
1123 BEGIN
1124 -- Bug 3490263
1125 l_employee_id := hxc_approval_wf_pkg.find_mysterious_approver(p_itemtype,p_itemkey);
1126
1127
1128 IF l_employee_id <> -1
1129 THEN
1130 l_real_approver := get_name(l_employee_id,sysdate);
1131 END IF;
1132
1133
1134 wf_engine.SetItemAttrText(
1135 itemtype => p_itemtype,
1136 itemkey => p_itemkey,
1137 aname => 'APR_NAME',
1138 avalue => l_real_approver
1139 );
1140
1141 -- Bug 8783079
1142 /* The real approver's self service login can be got in 2 ways
1143 1. Directly query on FND_USER using the employee_id of the real approver (l_real_approver) through a
1144 query involving per_all_people_f and fnd_user
1145 2. From wf attribute APR_SS_LOGIN which already has the approver's self-service logn
1146
1147 We go by method 2
1148 */
1149
1150 l_appr_ss_login := wf_engine.GetItemAttrText(
1151 itemtype => p_itemtype,
1152 itemkey => p_itemkey ,
1153 aname => 'APR_SS_LOGIN');
1154
1155
1156 --Bug 5375656
1157 wf_engine.SetItemAttrText(
1158 itemtype => p_itemtype,
1159 itemkey => p_itemkey,
1160 aname => 'TC_APPROVER_FROM_ROLE',
1161 avalue => l_appr_ss_login
1162 );
1163 END set_real_approver;
1164
1165
1166
1167
1168
1169 -- this work flow activity implies that the approver has 'approved'
1170 -- the notification, ie. not a timeout
1171 --
1172 procedure capture_approved_status(
1173 p_itemtype in varchar2,
1174 p_itemkey in varchar2,
1175 p_actid in number,
1176 p_funcmode in varchar2,
1177 p_result in out nocopy varchar2)
1178 is
1179 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_approved_status';
1180 l_approvers_visited number;
1181 begin
1182 g_debug:=hr_utility.debug_enabled;
1183 --sb_msgs_pkg.begin_call(l_proc);
1184 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
1185 if g_debug then
1186 hr_utility.trace('capture approved status');
1187 end if;
1188 if p_funcmode = 'RUN' then
1189
1190 set_real_approver(
1191 p_itemtype => p_itemtype
1192 ,p_itemkey => p_itemkey
1193 );
1194 --
1195 -- set variables for approval hierarchy
1196 --
1197 l_approvers_visited := wf_engine.GetItemAttrNumber(
1198 itemtype => p_itemtype,
1199 itemkey => p_itemkey,
1200 aname => 'APPROVERS_VISITED');
1201 if g_debug then
1202 hr_utility.trace('l_approvers_visited=' || l_approvers_visited);
1203 end if;
1204 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
1205 itemkey => p_itemkey,
1206 aname => 'APPROVED_AT_LEVEL',
1207 avalue => l_approvers_visited);
1208
1209 --
1210 -- set up attribute required for next activity
1211 --
1212 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1213 itemkey => p_itemkey,
1214 aname => 'APPROVAL_STATUS',
1215 avalue => 'APPROVED');
1216
1217 p_result := 'COMPLETE';
1218 end if;
1219
1220 if p_funcmode = 'CANCEL' then
1221 p_result := 'COMPLETE';
1222 end if;
1223
1224 if p_funcmode = 'TIMEOUT' then
1225 p_result := 'COMPLETE';
1226 end if;
1227
1228 --OIT Enhancement
1229 --FYI Notification to PREPARER on timecard APPROVAL
1230 hxc_approval_wf_helper.set_notif_attribute_values
1231 (p_itemtype,
1232 p_itemkey,
1233 hxc_app_comp_notifications_api.c_action_approved,
1234 hxc_app_comp_notifications_api.c_recipient_preparer
1235 );
1236 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
1237 --sb_msgs_pkg.end_call(l_proc);
1238 exception
1239 when others then
1240 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1241 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1242
1243 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1244 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1245 raise;
1246 end capture_approved_status;
1247
1248 procedure capture_timeout_status(
1249 p_itemtype in varchar2,
1250 p_itemkey in varchar2,
1251 p_actid in number,
1252 p_funcmode in varchar2,
1253 p_result in out nocopy varchar2)
1254 is
1255 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_timeout_status';
1256 begin
1257 --sb_msgs_pkg.begin_call(l_proc);
1258 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
1259
1260 if p_funcmode = 'RUN' then
1261 --
1262 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1263 itemkey => p_itemkey,
1264 aname => 'APPROVAL_STATUS',
1265 avalue => 'APPROVED');
1266
1267 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1268 itemkey => p_itemkey,
1269 aname => 'APR_REJ_REASON',
1270 avalue => 'TIMED_OUT');
1271
1272 p_result := 'COMPLETE';
1273 end if;
1274
1275 if p_funcmode = 'CANCEL' then
1276 p_result := 'COMPLETE';
1277 end if;
1278
1279 if p_funcmode = 'TIMEOUT' then
1280 p_result := 'COMPLETE';
1281 end if;
1282
1283 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
1284 --sb_msgs_pkg.end_call(l_proc);
1285 exception
1286 when others then
1287 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1288 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1289
1290 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1291 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1292 raise;
1293 end capture_timeout_status;
1294
1295 --
1296 -- reject comment after notification has been responded to
1297 --
1298 procedure capture_rejected_status(
1299 p_itemtype in varchar2,
1300 p_itemkey in varchar2,
1301 p_actid in number,
1302 p_funcmode in varchar2,
1303 p_result in out nocopy varchar2)
1304 is
1305 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_rejection_status';
1306 begin
1307 --sb_msgs_pkg.begin_call(l_proc);
1308 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
1309
1310 if p_funcmode = 'RUN' then
1311 set_real_approver(
1312 p_itemtype => p_itemtype
1313 ,p_itemkey => p_itemkey
1314 );
1315 --
1316 -- set up attribute required for next activity
1317 --
1318 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1319 itemkey => p_itemkey,
1320 aname => 'APPROVAL_STATUS',
1321 avalue => 'REJECTED');
1322
1323 p_result := 'COMPLETE';
1324 end if;
1325
1326 if p_funcmode = 'CANCEL' then
1327 p_result := 'COMPLETE';
1328 end if;
1329
1330 if p_funcmode = 'TIMEOUT' then
1331 p_result := 'COMPLETE';
1332 end if;
1333
1334 --OIT Enhancement
1335 --FYI Notification to PREPARER on timecard REJECTION
1336 hxc_approval_wf_helper.set_notif_attribute_values
1337 (p_itemtype,
1338 p_itemkey,
1339 hxc_app_comp_notifications_api.c_action_rejected,
1340 hxc_app_comp_notifications_api.c_recipient_preparer
1341 );
1342
1343 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
1344 --sb_msgs_pkg.end_call(l_proc);
1345 exception
1346 when others then
1347 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1348 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1349
1350 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1351 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1352 raise;
1353 end capture_rejected_status;
1354
1355 FUNCTION GetProjectManager(
1356 p_project_id IN NUMBER
1357 )
1358 RETURN NUMBER
1359 IS
1360 BEGIN
1361 RETURN 10250;
1362 END GetProjectManager;
1363
1364 --
1365 -- Project Manager Mechanism
1366 --
1367
1368 procedure find_project_manager(
1369 p_itemtype in varchar2,
1370 p_itemkey in varchar2,
1371 p_actid in number,
1372 p_funcmode in varchar2,
1373 p_result in out nocopy varchar2
1374 )
1375 is
1376
1377 CURSOR c_project_id(
1378 p_ap_bb_id hxc_time_building_blocks.time_building_block_id%TYPE
1379 )
1380 IS
1381 SELECT hta.attribute1
1382 FROM hxc_ap_detail_links apdet
1383 ,hxc_time_building_blocks htbb
1384 ,hxc_time_attribute_usages htau
1385 ,hxc_time_attributes hta
1386 WHERE apdet.application_period_id = p_ap_bb_id
1387 AND apdet.time_building_block_id = htbb.time_building_block_id
1388 AND htbb.date_to = hr_general.end_of_time
1389 AND htbb.time_building_block_id = htau.time_building_block_id
1390 AND htbb.object_version_number = htau.time_building_block_ovn
1391 AND htau.time_attribute_id = hta.time_attribute_id
1392 AND hta.attribute_category = 'PROJECTS';
1393
1394 cursor c_project_id_deleted_detail
1395 (p_app_bb_id in hxc_time_building_blocks.time_building_block_id%type) is
1396 select to_number(ta.attribute1)
1397 from hxc_time_building_blocks details,
1398 hxc_time_building_blocks days,
1399 hxc_time_attribute_usages tau,
1400 hxc_app_period_summary aps,
1401 hxc_time_Attributes ta
1402 where aps.application_period_id = p_app_bb_id
1403 and aps.start_time <= days.stop_time
1404 and aps.stop_time >= days.start_time
1405 and aps.resource_id = days.resource_Id
1406 and details.parent_building_block_Id = days.time_building_block_id
1407 and details.parent_building_block_ovn = days.object_version_number
1408 and details.date_to <> hr_general.end_of_time
1409 and details.object_version_number =
1410 (select max(details2.object_version_number)
1411 from hxc_time_building_blocks details2
1412 where details.time_building_block_id = details2.time_building_block_id)
1413 and details.time_building_block_id = tau.time_building_block_id
1414 and details.object_version_number = tau.time_building_block_ovn
1415 and tau.time_Attribute_Id = ta.time_attribute_id
1416 and ta.attribute_category = 'PROJECTS'
1417 and not exists
1418 (select 1
1419 from hxc_ap_detail_links adl
1420 where adl.application_period_id = aps.application_period_id
1421 and adl.time_building_block_id = details.time_building_block_id
1422 and adl.time_building_block_ovn = details.object_version_number
1423 );
1424
1425 l_project_id VARCHAR2(150);
1426 l_project_procedure VARCHAR2(150) := 'get_project_manager';
1427 l_dyn_sql VARCHAR2(2000);
1428 l_ap_bb_id NUMBER;
1429 l_project_manager NUMBER := NULL;
1430
1431 l_proc VARCHAR2(100);
1432 l_is_blank varchar2(1);
1433 begin
1434 g_debug:=hr_utility.debug_enabled;
1435 if g_debug then
1436 l_proc := g_pkg || 'find_project_manager';
1437 hr_utility.trace('in ' || l_proc);
1438 end if;
1439 l_ap_bb_id := wf_engine.GetItemAttrNumber(
1440 itemtype => p_itemtype,
1441 itemkey => p_itemkey,
1442 aname => 'APP_BB_ID');
1443
1444 l_is_blank := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1445 itemkey => p_itemkey,
1446 aname => 'IS_DIFF_TC',
1447 ignore_notfound => true);
1448
1449
1450 --
1451 -- First: Attempt to find the project id from a live time detail.
1452 --
1453 open c_project_id(l_ap_bb_id);
1454 fetch c_project_id into l_project_id;
1455 if c_project_id%notfound then
1456 close c_project_id;
1457 --
1458 -- Bug fix: 4177451: 115.65. In the case of a deleted line,
1459 -- check to see if the project id can be determined from an
1460 -- end dated detail.
1461 --
1462 open c_project_id_deleted_detail(l_ap_bb_id);
1463 fetch c_project_id_deleted_detail into l_project_id;
1464 close c_project_id_deleted_detail;
1465 else
1466 close c_project_id;
1467 end if;
1468
1469 if l_is_blank = 'Y' then
1470 l_project_id := null;
1471 end if;
1472
1473 if(l_project_id is not null) then
1474
1475 --find project manager id
1476
1477 l_dyn_sql := 'BEGIN '|| fnd_global.newline
1478 || ':1 := Pa_Otc_Api.GetProjectManager' ||fnd_global.newline
1479 ||'(p_project_id => :2);' ||fnd_global.newline
1480 ||'END;';
1481
1482 EXECUTE IMMEDIATE l_dyn_sql
1483 using OUT l_project_manager, IN l_project_id;
1484
1485
1486 IF l_project_manager IS NULL
1487 THEN
1488 g_trace :=' project manager is null';
1489
1490 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1491 hr_utility.set_message_token('PROCEDURE', l_proc);
1492 hr_utility.set_message_token('STEP', '10');
1493 hr_utility.raise_error;
1494
1495 END IF;
1496
1497 wf_engine.SetItemAttrNumber(
1498 itemtype => p_itemtype,
1499 itemkey => p_itemkey,
1500 aname => 'APR_PERSON_ID',
1501 avalue => l_project_manager);
1502
1503 p_result := 'COMPLETE';
1504 ELSE
1505 --
1506 -- Bug fix: 4291206: 115.66. If the user has updated instead of
1507 -- deleting the line, such that the new project manager is different
1508 -- we need to notify the previous project manager to ensure we can complete
1509 -- the ELA approval. We can look up the previous approver, because we
1510 -- stored it when we were generating the application period.
1511 --
1512 l_project_manager := hxc_approval_wf_util.get_previous_approver
1513 (p_itemtype,p_itemkey,l_ap_bb_id);
1514 if(l_project_manager is not null) then
1515 wf_engine.SetItemAttrNumber
1516 (itemtype => p_itemtype,
1517 itemkey => p_itemkey,
1518 aname => 'APR_PERSON_ID',
1519 avalue => l_project_manager);
1520 p_result := 'COMPLETE';
1521 else
1522 if g_debug then
1523 hr_utility.trace('project id and previous approver are null');
1524 end if;
1525 g_trace :='no project id or approver';
1526 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1527 hr_utility.set_message_token('PROCEDURE', l_proc);
1528 hr_utility.set_message_token('STEP', '20');
1529 hr_utility.raise_error;
1530 end if;
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 -- The line below records this function call in the error system
1536 -- in the case of an exception.
1537 --
1538 if g_debug then
1539 hr_utility.set_location(l_proc, 999);
1540 --
1541 hr_utility.trace('IN EXCEPTION IN find_project_manager');
1542 --
1543 end if;
1544 wf_core.context('HCAPPRWF', l_proc,
1545 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode, g_trace);
1546 raise;
1547 p_result := '';
1548 return;
1549 END find_project_manager;
1550
1551
1552
1553 -- auto approval mechanism
1554 --
1555 procedure auto_approval(
1556 p_itemtype in varchar2,
1557 p_itemkey in varchar2,
1558 p_actid in number,
1559 p_funcmode in varchar2,
1560 p_result in out nocopy varchar2)
1561 is
1562
1563 CURSOR c_tc_info(
1564 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
1565 )
1566 IS
1567 SELECT tcsum.resource_id,
1568 tcsum.start_time,
1569 tcsum.stop_time
1570 FROM hxc_timecard_summary tcsum
1571 WHERE tcsum.timecard_id = p_tc_bbid;
1572
1573 CURSOR c_appr_style(
1574 p_app_style_id hxc_approval_styles.approval_style_id%TYPE
1575 )
1576 IS
1577 SELECT name
1578 FROM hxc_approval_styles
1579 WHERE approval_style_id = p_app_style_id;
1580
1581 l_proc constant varchar2(61) := g_pkg || '.' || 'auto_approval';
1582 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
1583 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
1584 l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type;
1585 l_tc_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
1586 l_pref_table hxc_preference_evaluation.t_pref_table;
1587 l_master_pref_table hxc_preference_evaluation.t_pref_table;
1588 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
1589 l_tc_start_time hxc_time_building_blocks.start_time%TYPE;
1590 l_tc_stop_time hxc_time_building_blocks.stop_time%TYPE;
1591
1592 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE;
1593 l_approval_style_name hxc_approval_styles.name%TYPE;
1594
1595 begin
1596 --sb_msgs_pkg.begin_call(l_proc);
1597
1598 hr_utility.trace(l_proc);
1599
1600 -- Bug 9946628
1601 -- Need to validate the approval style before timecard is Auto Approved.
1602
1603 IF (nvl(fnd_profile.value('HXC_DEBUG_CHECK_ENABLED'), 'N') = 'Y') THEN -- OTL: Debug Check Enabled
1604
1605 IF p_funcmode = 'RUN' THEN
1606
1607 l_tc_bb_id := wf_engine.GetItemAttrNumber(
1608 itemtype => p_itemtype,
1609 itemkey => p_itemkey,
1610 aname => 'TC_BLD_BLK_ID');
1611
1612 --fetch timecard details
1613 OPEN c_tc_info(l_tc_bb_id);
1614 FETCH c_tc_info INTO l_resource_id, l_tc_start_time, l_tc_stop_time;
1615
1616 IF c_tc_info%NOTFOUND THEN
1617 CLOSE c_tc_info;
1618 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1619 hr_utility.set_message_token('PROCEDURE', l_proc);
1620 hr_utility.set_message_token('STEP', '10');
1621 hr_utility.raise_error;
1622 END IF;
1623
1624 CLOSE c_tc_info;
1625
1626 IF g_debug THEN
1627 hr_utility.trace('Person Id ::'||l_resource_id);
1628 hr_utility.trace('Timecard Start Time ::'||l_tc_start_time);
1629 hr_utility.trace('Timecard Stop Time ::'||l_tc_stop_time);
1630 END IF;
1631
1632 -- get approval style
1633 hxc_preference_evaluation.resource_preferences(p_resource_id => l_resource_id,
1634 p_preference_code => 'TS_PER_APPROVAL_STYLE',
1635 p_start_evaluation_date => l_tc_start_time,
1636 p_end_evaluation_date => l_tc_stop_time,
1637 p_sorted_pref_table => l_pref_table,
1638 p_master_pref_table => l_master_pref_table );
1639
1640 l_approval_style_id := TO_NUMBER(l_pref_table(1).attribute1);
1641
1642 IF g_debug THEN
1643 hr_utility.trace('Approval Style Id ::'||to_char(l_approval_style_id));
1644 END IF;
1645
1646 -- check whether is set to OTL Auto Approve
1647 IF l_approval_style_id >0 THEN
1648 OPEN c_appr_style(l_approval_style_id);
1649 FETCH c_appr_style INTO l_approval_style_name;
1650 IF c_appr_style%NOTFOUND THEN
1651 CLOSE c_appr_style;
1652 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1653 hr_utility.set_message_token('PROCEDURE', l_proc);
1654 hr_utility.set_message_token('STEP', '12');
1655 hr_utility.raise_error;
1656 END IF;
1657 CLOSE c_appr_style;
1658
1659 IF g_debug THEN
1660 hr_utility.trace('Approval Style Name ::'||l_approval_style_name);
1661 END IF;
1662
1663 IF l_approval_style_name <> 'OTL Auto Approve' THEN
1664 -- Raise an error if approval style is not set to OTL Auto Approve
1665 hr_utility.set_message(809, 'HXC_APPR_WF_WRONG_AS');
1666 hr_utility.set_message_token('APPROVAL_STYLE',l_approval_style_name);
1667 hr_utility.raise_error;
1668 END IF;
1669 END IF;
1670
1671 END IF;
1672
1673 END IF;
1674 -- End of Bug 9946628
1675
1676 if p_funcmode = 'RUN' then
1677 --
1678 -- set up attribute required for next activity
1679 --
1680 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1681 itemkey => p_itemkey,
1682 aname => 'APPROVAL_STATUS',
1683 avalue => 'APPROVED');
1684
1685 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1686 itemkey => p_itemkey,
1687 aname => 'APR_REJ_REASON',
1688 avalue => 'AUTO_APPROVE');
1689
1690 --OIT Enhancement
1691 --FYI Notification to WORKER on timecard AUTO APPROVAL
1692 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
1693 (p_itemtype,
1694 p_itemkey,
1695 hxc_app_comp_notifications_api.c_action_auto_approve,
1696 hxc_app_comp_notifications_api.c_recipient_worker
1697 );
1698
1699 p_result := 'COMPLETE';
1700 end if;
1701
1702 if p_funcmode = 'CANCEL' then
1703 p_result := 'COMPLETE';
1704 end if;
1705
1706 if p_funcmode = 'TIMEOUT' then
1707 p_result := 'COMPLETE';
1708 end if;
1709
1710 --sb_msgs_pkg.end_call(l_proc);
1711 exception
1712 when others then
1713 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1714 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1715
1716 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1717 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1718 raise;
1719 end auto_approval;
1720
1721
1722 FUNCTION category_timecard_hrs (
1723 p_app_per_id NUMBER
1724 , p_time_category_name VARCHAR2 )
1725 RETURN NUMBER
1726 IS
1727
1728 CURSOR csr_get_timecard(p_app_per_id NUMBER) IS
1729 SELECT adl.TIME_BUILDING_BLOCK_ID bb_id,
1730 adl.TIME_BUILDING_BLOCK_OVN ovn
1731 FROM hxc_time_building_blocks tbb
1732 , hxc_ap_detail_links adl
1733 WHERE adl.APPLICATION_PERIOD_ID = p_app_per_id
1734 AND
1735 tbb.time_building_block_id = adl.time_building_block_id AND
1736 tbb.object_version_number = adl.time_building_block_ovn AND
1737 tbb.date_to = hr_general.end_of_time;
1738
1739 CURSOR csr_get_person_id(p_app_per_id NUMBER) IS
1740 SELECT resource_id
1741 FROM hxc_time_building_blocks tbb
1742 WHERE tbb.time_building_block_id = p_app_per_id;
1743
1744 /* Bug fix for 5526281 */
1745 CURSOR get_timecard_start_date(p_app_per_id NUMBER) IS
1746 SELECT tbb.start_time ,tbb.stop_time
1747 FROM hxc_tc_ap_links htl,
1748 hxc_time_building_blocks tbb
1749 WHERE htl.application_period_id = p_app_per_id
1750 AND tbb.time_building_block_id = htl.timecard_id;
1751
1752 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
1753 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
1754 /* end of bug fix for 5526281 */
1755
1756 l_tc_rec csr_get_timecard%ROWTYPE;
1757 l_timecard_hrs NUMBER := 0;
1758 l_detail_hrs NUMBER := 0;
1759 l_time_category_id hxc_time_categories.time_category_id%TYPE;
1760 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
1761 l_precision VARCHAR2(4);
1762 l_rounding_rule VARCHAR2(20);
1763 l_tc_start_date date;
1764
1765 /* Bug fix for 5526281 */
1766 l_tc_end_date date;
1767 l_pref_eval_date date;
1768 l_emp_hire_date date;
1769 /* end of bug fix for 5526281 */
1770
1771 BEGIN
1772
1773 OPEN csr_get_person_id ( p_app_per_id );
1774 FETCH csr_get_person_id into l_resource_id;
1775 CLOSE csr_get_person_id;
1776
1777 /* Bug fix for 5526281 */
1778 OPEN get_timecard_start_date ( p_app_per_id );
1779 FETCH get_timecard_start_date into l_tc_start_date,l_tc_end_date;
1780 CLOSE get_timecard_start_date;
1781
1782 OPEN emp_hire_info (l_resource_id);
1783 FETCH emp_hire_info into l_emp_hire_date;
1784 CLOSE emp_hire_info;
1785
1786 if trunc(l_emp_hire_date) >= trunc(l_tc_start_date) and trunc(l_emp_hire_date) <= trunc(l_tc_end_date) then
1787 l_pref_eval_date := trunc(l_emp_hire_date);
1788 else
1789 l_pref_eval_date := trunc(l_tc_start_date);
1790 end if;
1791
1792 l_precision := hxc_preference_evaluation.resource_preferences
1793 (l_resource_id,
1794 'TC_W_TCRD_UOM',
1795 3,
1796 l_pref_eval_date
1797 );
1798
1799 l_rounding_rule := hxc_preference_evaluation.resource_preferences
1800 (l_resource_id,
1801 'TC_W_TCRD_UOM',
1802 4,
1803 l_pref_eval_date
1804 );
1805
1806 /* end of bug fix for 5526281 */
1807
1808 if l_precision is null
1809 then
1810 l_precision := '2';
1811 end if;
1812
1813 if l_rounding_rule is null
1814 then
1815 l_rounding_rule := 'ROUND_TO_NEAREST';
1816 end if;
1817
1818 l_time_category_id := HXC_TIME_CATEGORY_UTILS_PKG.get_time_category_id ( p_time_category_name => p_time_category_name );
1819
1820 OPEN csr_get_timecard ( p_app_per_id );
1821 FETCH csr_get_timecard into l_tc_rec;
1822
1823 WHILE csr_get_timecard%FOUND
1824 LOOP
1825 -- call category_detail_hrs
1826 l_detail_hrs := HXC_TIME_CATEGORY_UTILS_PKG.category_detail_hrs (
1827 p_tbb_id => l_tc_rec.bb_id
1828 , p_tbb_ovn => l_tc_rec.ovn
1829 , p_time_category_id => l_time_category_id );
1830
1831 l_timecard_hrs := l_timecard_hrs + apply_round_rule(l_rounding_rule,
1832 l_precision,
1833 l_detail_hrs);
1834
1835 FETCH csr_get_timecard INTO l_tc_rec;
1836 END LOOP;
1837
1838 CLOSE csr_get_timecard;
1839 RETURN l_timecard_hrs;
1840
1841 END category_timecard_hrs;
1842
1843 FUNCTION category_timecard_hrs (
1844 p_start_date date,
1845 p_end_date date,
1846 p_resource_id number,
1847 p_time_category_name VARCHAR2 )
1848 RETURN NUMBER
1849 IS
1850
1851 CURSOR csr_get_details
1852 IS
1853 select details.time_building_block_id bb_id, details.object_version_number ovn
1854 from hxc_time_building_blocks timecard,
1855 hxc_time_building_blocks details,
1856 hxc_time_building_blocks days
1857 where timecard.time_building_block_id = days.parent_building_block_id
1858 and timecard.object_version_number = days.parent_building_block_ovn
1859 and days.time_building_block_id = details.parent_building_block_id
1860 and days.object_version_number = details.parent_building_block_ovn
1861 and details.date_to = hr_general.end_of_time
1862 and days.start_time <=p_end_date
1863 and days.stop_time >= p_start_date
1864 and days.resource_id = p_resource_id
1865 and details.scope = 'DETAIL'
1866 and timecard.scope = 'TIMECARD';
1867
1868 l_tc_rec csr_get_details%ROWTYPE;
1869 l_timecard_hrs NUMBER := 0;
1870 l_detail_hrs NUMBER := 0;
1871 l_time_category_id hxc_time_categories.time_category_id%TYPE;
1872 l_precision VARCHAR2(4);
1873 l_rounding_rule VARCHAR2(20);
1874
1875 BEGIN
1876 l_precision := hxc_preference_evaluation.resource_preferences
1877 (p_resource_id,
1878 'TC_W_TCRD_UOM',
1879 3,
1880 p_start_date
1881 );
1882
1883 l_rounding_rule := hxc_preference_evaluation.resource_preferences
1884 (p_resource_id,
1885 'TC_W_TCRD_UOM',
1886 4,
1887 p_start_date
1888 );
1889
1890 if l_precision is null
1891 then
1892 l_precision := '2';
1893 end if;
1894
1895 if l_rounding_rule is null
1896 then
1897 l_rounding_rule := 'ROUND_TO_NEAREST';
1898 end if;
1899
1900 l_time_category_id := HXC_TIME_CATEGORY_UTILS_PKG.get_time_category_id ( p_time_category_name => p_time_category_name );
1901
1902 OPEN csr_get_details;
1903 FETCH csr_get_details into l_tc_rec;
1904
1905 WHILE csr_get_details%FOUND
1906 LOOP
1907 -- call category_detail_hrs
1908 l_detail_hrs := HXC_TIME_CATEGORY_UTILS_PKG.category_detail_hrs (
1909 p_tbb_id => l_tc_rec.bb_id
1910 , p_tbb_ovn => l_tc_rec.ovn
1911 , p_time_category_id => l_time_category_id );
1912
1913 l_timecard_hrs := l_timecard_hrs + apply_round_rule(l_rounding_rule,
1914 l_precision,
1915 l_detail_hrs);
1916
1917 FETCH csr_get_details INTO l_tc_rec;
1918 END LOOP;
1919
1920 CLOSE csr_get_details;
1921 RETURN l_timecard_hrs;
1922
1923 END category_timecard_hrs;
1924 --
1925 -- person approval mechanism
1926 --
1927 procedure person_approval(
1928 p_itemtype in varchar2,
1929 p_itemkey in varchar2,
1930 p_actid in number,
1931 p_funcmode in varchar2,
1932 p_result in out nocopy varchar2)
1933 is
1934
1935 cursor c_item_attribute_values(p_item_key in varchar2) is
1936 select name,text_value
1937 from wf_item_attribute_values
1938 where item_type = 'HXCEMP'
1939 and item_key = p_item_key;
1940
1941 l_proc constant varchar2(61) := g_pkg || '.' || 'person_approval';
1942 l_effective_end_date date;
1943 l_effective_start_date date;
1944 l_apr_person_id per_all_assignments_f.person_id%type;
1945 l_login fnd_user.user_name%type;
1946 --
1947 -- Bug 4153585
1948 -- Increased size for translation
1949 l_title varchar2(4000);
1950 l_total_hours number;
1951 l_otl_appr_id varchar2(50);
1952 l_appl_period_bb_id number;
1953 l_resource_id number;
1954 l_is_blank varchar2(1);
1955 l_supervisor_id number;
1956 begin
1957 if p_funcmode = 'RUN' then
1958 --
1959 -- all datetrack data should be valid for duration of application period
1960 --
1961 l_effective_end_date := wf_engine.GetItemAttrDate(
1962 itemtype => p_itemtype,
1963 itemkey => p_itemkey,
1964 aname => 'APP_END_DATE');
1965
1966 l_effective_start_date := wf_engine.GetItemAttrDate(
1967 itemtype => p_itemtype,
1968 itemkey => p_itemkey,
1969 aname => 'APP_START_DATE');
1970
1971 l_apr_person_id := wf_engine.GetItemAttrNumber(
1972 itemtype => p_itemtype,
1973 itemkey => p_itemkey,
1974 aname => 'APR_PERSON_ID');
1975
1976 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
1977 itemtype => p_itemtype,
1978 itemkey => p_itemkey,
1979 aname => 'APP_BB_ID');
1980 l_is_blank := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1981 itemkey => p_itemkey ,
1982 aname => 'IS_DIFF_TC',
1983 ignore_notfound => true);
1984 --Added as part of OIT enhancement
1985 l_resource_id := wf_engine.GetItemAttrNumber(
1986 itemtype => p_itemtype,
1987 itemkey => p_itemkey,
1988 aname => 'RESOURCE_ID');
1989
1990 -- Check if the approver is terminated (Bug#3160848)
1991 if validate_person (l_apr_person_id,SYSDATE)
1992 then
1993 IF l_is_blank = 'Y' THEN
1994
1995 l_apr_person_id := get_supervisor(l_resource_id, sysdate);
1996
1997 wf_engine.SetItemAttrNumber(
1998 itemtype => p_itemtype,
1999 itemkey => p_itemkey,
2000 aname => 'APR_PERSON_ID',
2001 avalue => l_apr_person_id);
2002 ELSE
2003 -- Check if the approver is terminated (Bug#3160848)
2004
2005 hr_utility.set_message(809, 'HXC_APPR_WF_TERMINATED_APPR');-- If approver is terminated then raise an error
2006 hr_utility.raise_error;
2007 end if;
2008 END IF;
2009
2010 --
2011 -- set attribute to specify approver's self service login
2012 --
2013 l_login := get_login(p_person_id => l_apr_person_id);
2014
2015 --
2016 -- if null returned, approver does not have a self service login name,
2017 -- where does notification get sent?
2018 --
2019 if l_login is null then
2020 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2021 hr_utility.set_message_token('PROCEDURE', l_proc);
2022 hr_utility.set_message_token('STEP', '10');
2023 hr_utility.raise_error;
2024 end if;
2025
2026 --sb_msgs_pkg.trace('approver login>' || l_login || '<');
2027
2028 wf_engine.SetItemAttrText(
2029 itemtype => p_itemtype,
2030 itemkey => p_itemkey,
2031 aname => 'APR_SS_LOGIN',
2032 avalue => l_login);
2033
2034 --
2035 -- set information for notification
2036 --
2037
2038
2039 wf_engine.SetItemAttrText(
2040 itemtype => p_itemtype,
2041 itemkey => p_itemkey,
2042 aname => 'APR_NAME',
2043 avalue => get_name(l_apr_person_id,l_effective_end_date)
2044 );
2045 wf_engine.SetItemAttrText(
2046 itemtype => p_itemtype,
2047 itemkey => p_itemkey,
2048 aname => 'TC_APPROVER_FROM_ROLE',
2049 avalue => l_login);
2050
2051 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
2052 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
2053 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
2054
2055 l_title := fnd_message.get();
2056
2057 wf_engine.SetItemAttrText(
2058 itemtype => p_itemtype,
2059 itemkey => p_itemkey,
2060 aname => 'TITLE',
2061 avalue => l_title);
2062
2063 l_otl_appr_id := l_appl_period_bb_id||'#'||p_itemkey;
2064
2065 wf_engine.SetItemAttrText(
2066 itemtype => p_itemtype,
2067 itemkey => p_itemkey,
2068 aname => 'OTL_APPR_ID',
2069 avalue => l_otl_appr_id);
2070
2071 wf_engine.SetItemAttrText(
2072 itemtype => p_itemtype,
2073 itemkey => p_itemkey,
2074 aname => 'DESCRIPTION',
2075 avalue => get_description(l_appl_period_bb_id));
2076
2077 wf_engine.SetItemAttrNumber(
2078 itemtype => p_itemtype,
2079 itemkey => p_itemkey,
2080 aname => 'TOTAL_TC_HOURS',
2081 avalue => l_total_hours);
2082 --OIT Enhancement
2083 --FYI Notification to SUPERVISOR on timecard SUBMISSION if he is not the direct approver.
2084 if(HXC_APPROVAL_WF_HELPER.is_approver_supervisor(l_apr_person_id,l_resource_id)) then
2085 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
2086 (p_itemtype,
2087 p_itemkey,
2088 null,
2089 null
2090 );
2091 else
2092 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
2093 (p_itemtype,
2094 p_itemkey,
2095 hxc_app_comp_notifications_api.c_action_request_approval,
2096 hxc_app_comp_notifications_api.c_recipient_supervisor
2097 );
2098 end if;
2099
2100 p_result := 'COMPLETE';
2101 end if;
2102
2103 if p_funcmode = 'CANCEL' then
2104 p_result := 'COMPLETE';
2105 end if;
2106
2107 if p_funcmode = 'TIMEOUT' then
2108 p_result := 'COMPLETE';
2109 end if;
2110
2111 /*
2112 Bug 3449786
2113
2114 Added commit. At this point we're about to send a notification
2115 which could take several days for a response, therefore to
2116 save rollback segments, we issue a commit here, knowing that
2117 the workflow data is set appropriately.
2118
2119 */
2120
2121 commit;
2122
2123 exception
2124 when others then
2125 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2126 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2127 raise;
2128 end person_approval;
2129
2130 --Function to check if a given approver is terminated (Bug#3160848)
2131 --Changed for 115.60; used per_all_people_f instead of per_people_f
2132 FUNCTION validate_person(
2133 p_person_id in number,
2134 p_effective_date in date)
2135 RETURN BOOLEAN
2136 is
2137
2138 cursor csr_validate_person(b_person_id number, b_effective_date date) is
2139 SELECT 1
2140 FROM per_all_people_f per,
2141 per_person_types ppt,
2142 per_person_type_usages_f pptu
2143 WHERE per.person_id = b_person_id
2144 AND TRUNC(b_effective_date) between TRUNC(per.effective_Start_date) and TRUNC(per.effective_end_date)
2145 AND TRUNC(b_effective_date) between TRUNC(pptu.effective_Start_date) and TRUNC(pptu.effective_end_date)
2146 AND pptu.person_id = per.person_id
2147 AND pptu.person_type_id = ppt.person_type_id
2148 AND ppt.system_person_type in ('EMP','CWK');
2149
2150 temp number;
2151
2152 begin
2153
2154 open csr_validate_person(p_person_id, trunc(p_effective_date));
2155 fetch csr_validate_person into temp;
2156
2157 IF csr_validate_person%NOTFOUND
2158 THEN
2159 close csr_validate_person;
2160 RETURN TRUE;
2161 END IF;
2162
2163 CLOSE csr_validate_person;
2164 RETURN FALSE;
2165
2166 end validate_person;
2167
2168 --
2169 --
2170 --
2171 procedure inc_approvers_visited(
2172 p_itemtype in varchar2,
2173 p_itemkey in varchar2)
2174 is
2175 l_approvers_visited number;
2176 begin
2177 l_approvers_visited := wf_engine.GetItemAttrNumber(
2178 itemtype => p_itemtype,
2179 itemkey => p_itemkey,
2180 aname => 'APPROVERS_VISITED');
2181
2182 l_approvers_visited := l_approvers_visited + 1;
2183
2184 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2185 itemkey => p_itemkey,
2186 aname => 'APPROVERS_VISITED',
2187 avalue => l_approvers_visited);
2188 end inc_approvers_visited;
2189
2190
2191
2192 PROCEDURE process_extension_func2(
2193 p_tc_id IN hxc_time_building_blocks.time_building_block_id%TYPE
2194 ,p_tc_ovn IN hxc_time_building_blocks.object_version_number%TYPE
2195 ,p_time_recipient IN hxc_time_recipients.time_recipient_id%TYPE
2196 ,p_ext_func2 IN hxc_time_recipients.extension_function2%TYPE
2197 ,p_previous_approver IN NUMBER
2198 ,p_next_approver OUT NOCOPY NUMBER
2199 )
2200 IS
2201
2202 l_previous_approver_id number := p_previous_approver;
2203 l_approver_person_id number := NULL;
2204 l_message varchar2(2000);
2205 l_func_sql varchar2(2000);
2206 l_message_index number;
2207 l_message_table hxc_self_service_time_deposit.message_table;
2208 BEGIN
2209 --
2210 -- Sets up global variables for timecard records.
2211 --
2212 hxc_self_service_time_deposit.get_timecard_tables(
2213 p_timecard_id => p_tc_id
2214 ,p_timecard_ovn => p_tc_ovn
2215 ,p_timecard_blocks => hxc_approval_wf_pkg.g_time_building_blocks
2216 ,p_timecard_app_attributes => hxc_approval_wf_pkg.g_time_app_attributes
2217 ,p_time_recipient_id => p_time_recipient);
2218
2219
2220
2221 l_func_sql := 'BEGIN '||fnd_global.newline
2222 ||p_ext_func2 ||fnd_global.newline
2223 ||'(p_previous_approver_id => :1' ||fnd_global.newline
2224 ||',x_approver_person_id => :2' ||fnd_global.newline
2225 ||',x_messages => :3);' ||fnd_global.newline
2226 ||'END;';
2227
2228 EXECUTE IMMEDIATE l_func_sql
2229 using IN OUT l_previous_approver_id,
2230 IN OUT l_approver_person_id,
2231 IN OUT l_message;
2232
2233 if g_debug then
2234 hr_utility.trace('After client extension');
2235 --
2236 hr_utility.trace('Previous APPR ID is : ' || to_char(l_previous_approver_id));
2237 hr_utility.trace('APPR ID is : ' || to_char(l_approver_person_id));
2238 hr_utility.trace('Message is : ' || l_message);
2239 end if;
2240 IF l_message IS NOT NULL
2241 THEN
2242 l_message_table := hxc_deposit_wrapper_utilities.string_to_messages
2243 (p_message_string => l_message);
2244
2245 IF l_message_table.count > 0
2246 THEN
2247 l_message_index := l_message_table.first;
2248
2249 FND_MESSAGE.SET_NAME
2250 (l_message_table(l_message_index).application_short_name
2251 ,l_message_table(l_message_index).message_name
2252 );
2253
2254 FND_MESSAGE.RAISE_ERROR;
2255 END IF;
2256
2257 END IF;
2258
2259 p_next_approver := l_approver_person_id;
2260
2261
2262 EXCEPTION
2263 WHEN OTHERS THEN
2264 raise;
2265
2266 END process_extension_func2;
2267
2268
2269
2270 --
2271 -- supervisor approval mechanism
2272 --
2273 procedure hr_supervisor_approval(
2274 p_itemtype in varchar2,
2275 p_itemkey in varchar2,
2276 p_actid in number,
2277 p_funcmode in varchar2,
2278 p_result in out nocopy varchar2)
2279 is
2280 --
2281 cursor csr_get_extension(p_time_recipient number) is
2282 select htr.extension_function2
2283 from hxc_time_recipients htr
2284 where htr.time_recipient_id = p_time_recipient;
2285
2286
2287
2288 cursor c_details_of_timecard( l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type )
2289 is
2290 select 'Y'
2291 from hxc_tc_ap_links tcl,
2292 hxc_ap_detail_links adl
2293 where tcl.timecard_id = l_tc_bb_id
2294 and tcl.application_period_id = adl.application_period_id
2295 and rownum < 2;
2296
2297 cursor c_find_app_per_id( l_ap_bb_id in hxc_time_building_blocks.time_building_block_id%type)
2298 is
2299 SELECT 'Y'
2300 FROM hxc_app_period_summary
2301 WHERE time_category_id = 0
2302 AND APPLICATION_PERIOD_ID = l_ap_bb_id
2303 AND approval_comp_id = (SELECT approval_comp_id
2304 FROM hxc_approval_comps
2305 WHERE approval_style_id =
2306 (SELECT approval_style_id
2307 FROM hxc_approval_styles
2308 WHERE NAME = 'SEEDED_APPL_PA_MGR')
2309 AND time_category_id = 0);
2310
2311
2312 cursor get_result
2313 is
2314 select text_value
2315 from wf_item_attribute_values
2316 where item_type = p_itemtype
2317 and item_key = p_itemkey
2318 and name = 'RESULT';
2319
2320 cursor c_appr_comp(p_app_bb_id number,p_app_bb_ovn number)
2321 is
2322 select approval_comp_id
2323 from hxc_app_period_summary
2324 where application_period_id = p_app_bb_id
2325 and application_period_ovn = p_app_bb_ovn;
2326
2327
2328 --
2329 l_proc constant varchar2(61) := g_pkg || '.' || 'hr_supervisor_approval';
2330 l_effective_start_date date;
2331 l_effective_end_date date;
2332 --l_effective_date date;
2333 --l_approval_timeout number;
2334 l_approvers_visited number;
2335 l_default_timeout number;
2336 --
2337 -- person who requires approval
2338 --
2339 l_person_id per_all_assignments_f.person_id%type;
2340 --
2341 l_supervisor_id per_all_assignments_f.supervisor_id%type;
2342 l_next_supervisor_id per_all_assignments_f.supervisor_id%type;
2343 l_login fnd_user.user_name%type;
2344 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
2345 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
2346
2347
2348
2349 l_ap_bb_id hxc_time_building_blocks.time_building_block_id%type;
2350 l_validate_flag varchar2(15);
2351 l_tc_has_details_flag varchar2(15);
2352
2353 --
2354 l_time_recipient varchar2(150);
2355 l_ext_func2 varchar2(2000);
2356 l_auto_approval_flag varchar2(1);
2357 l_approver_person_id number := NULL;
2358 l_previous_approver_id number := NULL;
2359 l_message varchar2(2000);
2360 l_func_sql varchar2(2000);
2361 l_tc_bld_blk_id number;
2362 l_tc_ovn number;
2363 --
2364 -- Bug 4153585
2365 -- Increased size for translation
2366 l_title varchar2(4000);
2367 l_description fnd_new_messages.message_text%type;
2368 l_otl_appr_id varchar2(50);
2369 l_appl_period_bb_id number;
2370 l_total_hours number;
2371 l_result varchar2(20);
2372 l_approval_component_id number;
2373 l_app_ovn number;
2374
2375 --
2376 begin
2377
2378 g_debug:=hr_utility.debug_enabled;
2379
2380 IF p_funcmode <> 'CANCEL' THEN
2381
2382 g_trace := 'Begin hr_supervisor_approval';
2383 if g_debug then
2384 hr_utility.trace('Begin hr_supervisor_approval');
2385 end if;
2386
2387 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
2388 (itemtype => p_itemtype,
2389 itemkey => p_itemkey,
2390 aname => 'TC_BLD_BLK_ID');
2391
2392 g_trace := 'Timecard BB ID is : ' || to_char(l_tc_bld_blk_id);
2393
2394 if g_debug then
2395 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
2396 end if;
2397 l_tc_ovn := wf_engine.GetItemAttrNumber
2398 (itemtype => p_itemtype,
2399 itemkey => p_itemkey,
2400 aname => 'TC_BLD_BLK_OVN');
2401
2402 l_approvers_visited := wf_engine.GetItemAttrNumber(
2403 itemtype => p_itemtype,
2404 itemkey => p_itemkey,
2405 aname => 'APPROVERS_VISITED');
2406
2407 l_time_recipient := wf_engine.GetItemAttrText(
2408 itemtype => p_itemtype,
2409 itemkey => p_itemkey ,
2410 aname => 'TIME_RECIPIENT_ID');
2411
2412 l_person_id := wf_engine.GetItemAttrNumber(
2413 itemtype => p_itemtype,
2414 itemkey => p_itemkey,
2415 aname => 'APR_PERSON_ID');
2416
2417 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
2418 itemtype => p_itemtype,
2419 itemkey => p_itemkey,
2420 aname => 'APP_BB_ID');
2421 l_app_ovn :=wf_engine.GetItemAttrNumber(
2422 itemtype => p_itemtype,
2423 itemkey => p_itemkey,
2424 aname => 'APP_BB_OVN');
2425 --OIT Change.
2426 --When the preparer chooses to resend the notification, we end up with sending the notification to
2427 --supervisor's supervisor since we are setting the 'APR_PERSON_ID' to supervisor id when we initially traversed
2428 --the HR_Supervisor_approval.During the resend we get the person id from APR_PERSON_ID (which is supervisor id)
2429 --and gets the supervisor of this resource id which is supervisor's supervisor.
2430
2431 open get_result;
2432 fetch get_result into l_result;
2433 close get_result;
2434
2435 if l_result = 'RESEND' then
2436
2437 wf_engine.SetItemAttrNumber(
2438 itemtype => p_itemtype,
2439 itemkey => p_itemkey,
2440 aname => 'APPROVERS_VISITED',
2441 avalue => l_approvers_visited-1);
2442 end if;
2443 --added to support OIT desuport
2444
2445 open c_appr_comp(l_appl_period_bb_id,l_app_ovn);
2446 fetch c_appr_comp into l_approval_component_id;
2447 close c_appr_comp;
2448
2449 if(hxc_notification_helper.run_extensions(l_approval_component_id)) then
2450 open csr_get_extension(to_number(l_time_recipient));
2451 fetch csr_get_extension into l_ext_func2;
2452 close csr_get_extension;
2453
2454 g_trace := 'Before client extension=' || l_ext_func2;
2455 if g_debug then
2456 hr_utility.trace('Before client extension=' || l_ext_func2);
2457 end if;
2458 -- Bug 4177487. For an empty Timecard (Timecard that does not have DETAIL level records),
2459 -- we need not call the PA extension function.
2460 open c_details_of_timecard( l_tc_bld_blk_id );
2461 fetch c_details_of_timecard into l_tc_has_details_flag;
2462 close c_details_of_timecard;
2463 else
2464 l_ext_func2 := null;
2465 l_tc_has_details_flag := 'N';
2466 end if; -- Run extensions
2467
2468
2469 IF l_ext_func2 IS NOT NULL and l_tc_has_details_flag = 'Y'
2470 THEN
2471 g_trace := 'extension not null';
2472 if g_debug then
2473 hr_utility.trace('extension not null');
2474 end if;
2475 IF hxc_approval_wf_pkg.code_chk(l_ext_func2)
2476 THEN
2477 g_trace := 'extension code exists';
2478 if g_debug then
2479 hr_utility.trace('extension code exists');
2480 end if;
2481 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2482 itemkey => p_itemkey,
2483 aname => 'APR_KEY_LEVEL',
2484 avalue => '100');
2485
2486 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2487 itemkey => p_itemkey,
2488 aname => 'APPROVAL_TIMEOUT',
2489 avalue => 0);
2490
2491 IF l_approvers_visited = 0
2492 THEN
2493 l_previous_approver_id := null;
2494 ELSE
2495 l_previous_approver_id := l_person_id;
2496 END IF;
2497
2498 g_trace := 'before processing extension l_previous_approver_id='
2499 || to_char(l_person_id);
2500 if g_debug then
2501 hr_utility.trace('before processing extension l_previous_approver_id='
2502 || to_char(l_person_id));
2503 end if;
2504
2505 --Bug 5386274
2506 --We need to get the result attribute, if it is RESEND then set the supervisor id to person id which we set
2507 --to supervisor id when we traversed this procedure initially.
2508 if l_result = 'RESEND' then
2509 l_supervisor_id :=l_person_id;
2510 else
2511 process_extension_func2(
2512 p_tc_id => l_tc_bld_blk_id
2513 ,p_tc_ovn => l_tc_ovn
2514 ,p_time_recipient => to_number(l_time_recipient)
2515 ,p_ext_func2 => l_ext_func2
2516 ,p_previous_approver => l_previous_approver_id
2517 ,p_next_approver => l_supervisor_id
2518 );
2519 end if;
2520
2521 g_trace := 'after processing extension l_supervisor_id='
2522 || to_char(l_supervisor_id);
2523 if g_debug then
2524 hr_utility.trace('after processing extension l_supervisor_id='
2525 || to_char(l_supervisor_id));
2526 end if;
2527 IF l_supervisor_id IS NOT NULL
2528 THEN
2529 g_trace := 'testing if this is the final approver';
2530 if g_debug then
2531 hr_utility.trace('testing if this is the final approver');
2532 end if;
2533 process_extension_func2(
2534 p_tc_id => l_tc_bld_blk_id
2535 ,p_tc_ovn => l_tc_ovn
2536 ,p_time_recipient => to_number(l_time_recipient)
2537 ,p_ext_func2 => l_ext_func2
2538 ,p_previous_approver => l_supervisor_id
2539 ,p_next_approver => l_next_supervisor_id
2540 );
2541
2542 g_trace := 'end calling extension';
2543 if g_debug then
2544 hr_utility.trace('end calling extension');
2545 end if;
2546 IF l_next_supervisor_id = -99
2547 THEN
2548 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2549 itemkey => p_itemkey,
2550 aname => 'FINAL_APR',
2551 avalue => 'YES');
2552
2553 g_trace := 'FINAL_APR is : YES';
2554 if g_debug then
2555 hr_utility.trace('FINAL_APR is : YES');
2556 end if;
2557 END IF;
2558
2559 g_trace := 'end testing final approver';
2560 if g_debug then
2561 hr_utility.trace('end testing final approver');
2562 end if;
2563 END IF;
2564
2565 ELSE
2566 g_trace := 'extension function=' || l_ext_func2 || 'not exist in db';
2567 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2568 hr_utility.set_message_token('PROCEDURE', l_proc);
2569 hr_utility.set_message_token('STEP', '10');
2570 hr_utility.raise_error;
2571
2572 END IF;
2573 ELSE -- if no client extension, find supervisor from assignments
2574 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2575 itemkey => p_itemkey,
2576 aname => 'FINAL_APR',
2577 avalue => 'NO_EXTENSION');
2578
2579
2580 --Bug 2777538 sonarasi 20-FEB-2003
2581 g_trace := 'no extension function checking assignment';
2582 if g_debug then
2583 hr_utility.trace('no extension function checking assignment');
2584 end if;
2585
2586 -- Bug 4202019. These variables need to be set outside this IF block.
2587 /*
2588 l_effective_end_date := wf_engine.GetItemAttrDate(
2589 itemtype => p_itemtype,
2590 itemkey => p_itemkey,
2591 aname => 'APP_END_DATE');
2592
2593 l_effective_start_date := wf_engine.GetItemAttrDate(
2594 itemtype => p_itemtype,
2595 itemkey => p_itemkey,
2596 aname => 'APP_START_DATE');
2597 */
2598
2599 --l_effective_date := l_effective_end_date;
2600
2601 --OIT Change
2602 --We need to get the result attribute, if it is RESEND then set the supervisor id to person id which we set
2603 --to supervisor id when we traversed this procedure initially.
2604
2605 if l_result = 'RESEND' then
2606 l_supervisor_id := l_person_id;
2607 else
2608 l_supervisor_id:= get_supervisor(l_person_id, SYSDATE);
2609 end if;
2610
2611 END IF;
2612
2613
2614 -- no supervisor found, does not make sense here
2615 -- eg. approval key level = 1
2616 -- expect martin to be found as supervisor
2617 --
2618 -- approval key level = 2
2619 -- expect martin, clive to be found as supervisors,
2620 --
2621 if l_supervisor_id is null and l_approvers_visited = 0 then
2622 g_trace := '200';
2623 if g_debug then
2624 hr_utility.trace('200');
2625 end if;
2626
2627 hr_utility.set_message(809, 'HXC_APPR_WF_NO_HR_SUP');
2628 hr_utility.raise_error;
2629 end if;
2630
2631 g_trace := 'supervisor is not null';
2632 if g_debug then
2633 hr_utility.trace('supervisor is not null');
2634 end if;
2635 -- set up timeout properties for first approver in hierarchy
2636
2637 IF l_ext_func2 IS NULL
2638 THEN
2639 g_trace := 'setting timeout';
2640 if g_debug then
2641 hr_utility.trace('setting timeout');
2642 end if;
2643 if l_approvers_visited = 0 then
2644
2645 -- if supervisors exist after first approver then notification
2646 -- for first approver is allowed to timeout
2647
2648 if get_supervisor(l_supervisor_id, SYSDATE) is not null
2649 then
2650 g_trace :='allow timeout';
2651 if g_debug then
2652 hr_utility.trace('allow timeout');
2653 end if;
2654 l_default_timeout := wf_engine.GetItemAttrText(
2655 itemtype => p_itemtype,
2656 itemkey => p_itemkey,
2657 aname => 'DEFAULT_TIMEOUT');
2658
2659
2660 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2661 itemkey => p_itemkey,
2662 aname => 'APPROVAL_TIMEOUT',
2663 avalue => l_default_timeout);
2664
2665 g_trace := 'timeout=' || to_char(l_default_timeout);
2666 if g_debug then
2667 hr_utility.trace('timeout=' || to_char(l_default_timeout));
2668 end if;
2669 -- if no more supervisors exist after first approver then
2670 -- notification sent to next approver NOT allowed to timeout
2671 else
2672 g_trace :='NOT allow timeout';
2673 if g_debug then
2674 hr_utility.trace('NOT allow timeout');
2675 end if;
2676 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2677 itemkey => p_itemkey,
2678 aname => 'APPROVAL_TIMEOUT',
2679 avalue => 0);
2680 end if;
2681 end if;
2682 END IF;
2683
2684
2685
2686
2687 g_trace := 'find supervisor self service login';
2688 if g_debug then
2689 hr_utility.trace('find supervisor self service login');
2690 end if;
2691 -- find supervisor self service login
2692 l_login := get_login(p_person_id =>l_supervisor_id);
2693
2694 if l_login is null then
2695 g_trace := 'RAISE: no self service login for supervisor';
2696 if g_debug then
2697 hr_utility.trace('RAISE: no self service login for supervisor');
2698 end if;
2699 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2700 hr_utility.set_message_token('PROCEDURE', l_proc);
2701 hr_utility.set_message_token('STEP', '30');
2702 hr_utility.raise_error;
2703 end if;
2704
2705 g_trace := 'supervisor ss login=' || l_login;
2706 if g_debug then
2707 hr_utility.trace('supervisor ss login=' || l_login);
2708 end if;
2709
2710 -- supervisor found and notification to be sent,
2711 -- keep track of approvers visted to date
2712
2713 g_trace := 'increase approvers visited';
2714 if g_debug then
2715 hr_utility.trace('increase approvers visited');
2716 end if;
2717 inc_approvers_visited(p_itemtype, p_itemkey);
2718
2719 wf_engine.SetItemAttrText(
2720 itemtype => p_itemtype,
2721 itemkey => p_itemkey,
2722 aname => 'APR_SS_LOGIN',
2723 avalue => l_login
2724 );
2725
2726 g_trace := 'setting apr_name';
2727 if g_debug then
2728 hr_utility.trace('setting apr_name');
2729 end if;
2730 -- set information for notification
2731 wf_engine.SetItemAttrText(
2732 itemtype => p_itemtype,
2733 itemkey => p_itemkey,
2734 aname => 'APR_NAME',
2735 avalue => get_name(l_supervisor_id,sysdate)
2736 );
2737
2738 g_trace := 'setting from_role';
2739 if g_debug then
2740 hr_utility.trace('setting from_role');
2741 end if;
2742 wf_engine.SetItemAttrText(
2743 itemtype => p_itemtype,
2744 itemkey => p_itemkey,
2745 aname => 'TC_APPROVER_FROM_ROLE',
2746 avalue => l_login
2747 );
2748
2749 g_trace := '300';
2750 if g_debug then
2751 hr_utility.trace('Notification Sent to : '||to_char(l_supervisor_id));
2752 end if;
2753 --
2754 -- set supervisor's person id, ready for next iteration
2755
2756 wf_engine.SetItemAttrNumber(
2757 itemtype => p_itemtype,
2758 itemkey => p_itemkey,
2759 aname => 'APR_PERSON_ID',
2760 avalue => l_supervisor_id);
2761
2762 -- Bug 4202019. These variables are set just before they are being used in the TITLE of Worklist notification.
2763
2764 l_effective_end_date := wf_engine.GetItemAttrDate(
2765 itemtype => p_itemtype,
2766 itemkey => p_itemkey,
2767 aname => 'APP_END_DATE');
2768
2769 l_effective_start_date := wf_engine.GetItemAttrDate(
2770 itemtype => p_itemtype,
2771 itemkey => p_itemkey,
2772 aname => 'APP_START_DATE');
2773
2774
2775 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
2776 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
2777 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
2778
2779 l_title := fnd_message.get();
2780
2781 wf_engine.SetItemAttrText(
2782 itemtype => p_itemtype,
2783 itemkey => p_itemkey,
2784 aname => 'TITLE',
2785 avalue => l_title);
2786
2787 l_otl_appr_id := l_appl_period_bb_id||'#'||p_itemkey;
2788
2789 wf_engine.SetItemAttrText(
2790 itemtype => p_itemtype,
2791 itemkey => p_itemkey,
2792 aname => 'OTL_APPR_ID',
2793 avalue => l_otl_appr_id);
2794
2795 l_description := get_description(l_appl_period_bb_id);
2796
2797 open c_find_app_per_id( l_appl_period_bb_id );
2798 fetch c_find_app_per_id into l_validate_flag;
2799 close c_find_app_per_id;
2800
2801 if l_validate_flag = 'Y' then
2802 fnd_message.set_name('HXC','HXC_APPR_WF_DESC_NO_PA_MANAGER');
2803 l_description := l_description || fnd_message.get();
2804 end if;
2805
2806 wf_engine.SetItemAttrText
2807 (itemtype => p_itemtype,
2808 itemkey => p_itemkey,
2809 aname => 'DESCRIPTION',
2810 avalue => l_description);
2811
2812 wf_engine.SetItemAttrNumber(
2813 itemtype => p_itemtype,
2814 itemkey => p_itemkey,
2815 aname => 'TOTAL_TC_HOURS',
2816 avalue => l_total_hours);
2817
2818
2819
2820 g_trace := '500';
2821 if g_debug then
2822 hr_utility.trace('500');
2823 end if;
2824 p_result := 'COMPLETE:Y';
2825
2826 end if;
2827 if p_funcmode = 'CANCEL' then
2828 p_result := 'COMPLETE:Y';
2829 end if;
2830
2831 if p_funcmode = 'TIMEOUT' then
2832 p_result := 'COMPLETE:Y';
2833 end if;
2834
2835
2836
2837 /*
2838 Bug 3449786
2839
2840 Added commit. At this point we're about to send a notification
2841 which could take several days for a response, therefore to
2842 save rollback segments, we issue a commit here, knowing that
2843 the workflow data is set appropriately.
2844
2845 */
2846
2847 commit;
2848
2849 exception
2850 when others then
2851 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
2852 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
2853
2854 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2855 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode,
2856 l_proc || '|' || g_trace);
2857 raise;
2858 end hr_supervisor_approval;
2859 --
2860 -- approval comment arrives after notification has been responded to,
2861 -- this work flow activity implies that the approver has 'approved'
2862 -- the notification, ie. not a timeout
2863 --
2864 procedure capture_apr_comment(
2865 p_itemtype in varchar2,
2866 p_itemkey in varchar2,
2867 p_actid in number,
2868 p_funcmode in varchar2,
2869 p_result in out nocopy varchar2)
2870 is
2871 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_apr_comment';
2872 l_approvers_visited number;
2873 begin
2874 --sb_msgs_pkg.begin_call(l_proc);
2875 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
2876
2877 if p_funcmode = 'RUN' then
2878 --
2879 -- set variables for approval hierarchy
2880 --
2881 /*jxtan should move this to other procedure
2882 l_approvers_visited := wf_engine.GetItemAttrNumber(
2883 itemtype => p_itemtype,
2884 itemkey => p_itemkey,
2885 aname => 'APPROVERS_VISITED');
2886
2887 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2888 itemkey => p_itemkey,
2889 aname => 'APPROVED_AT_LEVEL',
2890 avalue => l_approvers_visited);
2891
2892 */
2893 --
2894 -- set up attribute required for next activity
2895 --
2896 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2897 itemkey => p_itemkey,
2898 aname => 'APPROVAL_STATUS',
2899 avalue => 'APPROVED');
2900
2901 p_result := 'COMPLETE';
2902 end if;
2903
2904 if p_funcmode = 'CANCEL' then
2905 p_result := 'COMPLETE';
2906 end if;
2907
2908 if p_funcmode = 'TIMEOUT' then
2909 p_result := 'COMPLETE';
2910 end if;
2911
2912 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
2913 --sb_msgs_pkg.end_call(l_proc);
2914 exception
2915 when others then
2916 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
2917 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
2918
2919 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2920 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2921 raise;
2922 end capture_apr_comment;
2923
2924
2925
2926 --
2927 -- reject comment after notification has been responded to
2928 --
2929 procedure capture_reject_comment(
2930 p_itemtype in varchar2,
2931 p_itemkey in varchar2,
2932 p_actid in number,
2933 p_funcmode in varchar2,
2934 p_result in out nocopy varchar2)
2935 is
2936 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_reject_comment';
2937 begin
2938 --sb_msgs_pkg.begin_call(l_proc);
2939 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
2940
2941 if p_funcmode = 'RUN' then
2942 --
2943 -- set up attribute required for next activity
2944 --
2945 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2946 itemkey => p_itemkey,
2947 aname => 'APPROVAL_STATUS',
2948 avalue => 'REJECTED');
2949 --
2950
2951 p_result := 'COMPLETE';
2952 end if;
2953
2954 if p_funcmode = 'CANCEL' then
2955 p_result := 'COMPLETE';
2956 end if;
2957
2958 if p_funcmode = 'TIMEOUT' then
2959 p_result := 'COMPLETE';
2960 end if;
2961
2962 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
2963 --sb_msgs_pkg.end_call(l_proc);
2964 exception
2965 when others then
2966 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
2967 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
2968
2969 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2970 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2971 raise;
2972 end capture_reject_comment;
2973
2974
2975
2976 --
2977 -- controls looping logic
2978 --
2979 procedure is_final_apr(
2980 p_itemtype in varchar2,
2981 p_itemkey in varchar2,
2982 p_actid in number,
2983 p_funcmode in varchar2,
2984 p_result in out nocopy varchar2)
2985 is
2986 l_proc constant varchar2(61) := g_pkg || '.' || 'is_final_apr';
2987 l_final_apr varchar2(50);
2988 l_effective_date date;
2989 l_approval_timeout number;
2990 l_default_timeout number;
2991 l_apr_person_id per_all_assignments_f.person_id%type;
2992 l_apr_key_level number;
2993 l_approved_at_level number;
2994 l_approvers_visited number;
2995 begin
2996 g_debug:=hr_utility.debug_enabled;
2997 --sb_msgs_pkg.begin_call(l_proc);
2998 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
2999 if g_debug then
3000 hr_utility.trace('is final approver');
3001 end if;
3002 if p_funcmode = 'RUN' then
3003
3004 l_final_apr := wf_engine.GetItemAttrText(
3005 itemtype => p_itemtype,
3006 itemkey => p_itemkey,
3007 aname => 'FINAL_APR');
3008
3009
3010 IF l_final_apr = 'YES' THEN
3011 if g_debug then
3012 hr_utility.trace('Final!');
3013 end if;
3014 p_result := 'COMPLETE:Y';
3015 return;
3016 ELSIF l_final_apr = 'NO' THEN
3017 if g_debug then
3018 hr_utility.trace('extension not final');
3019 end if;
3020 p_result := 'COMPLETE:N';
3021 return;
3022 END IF;
3023
3024 l_apr_person_id := wf_engine.GetItemAttrNumber(
3025 itemtype => p_itemtype,
3026 itemkey => p_itemkey,
3027 aname => 'APR_PERSON_ID');
3028
3029 IF get_supervisor(l_apr_person_id, SYSDATE) IS NULL
3030 THEN
3031 p_result := 'COMPLETE:Y';
3032 return;
3033 END IF;
3034
3035 if g_debug then
3036 hr_utility.trace('NOT final');
3037 end if;
3038
3039 l_effective_date := wf_engine.GetItemAttrDate(
3040 itemtype => p_itemtype,
3041 itemkey => p_itemkey,
3042 aname => 'APP_END_DATE');
3043
3044 l_approval_timeout := wf_engine.GetItemAttrText(
3045 itemtype => p_itemtype,
3046 itemkey => p_itemkey,
3047 aname => 'APPROVAL_TIMEOUT');
3048
3049 l_default_timeout := wf_engine.GetItemAttrText(
3050 itemtype => p_itemtype,
3051 itemkey => p_itemkey,
3052 aname => 'DEFAULT_TIMEOUT');
3053
3054
3055 --sb_msgs_pkg.trace('current apr per id>' || l_apr_person_id || '<');
3056
3057 l_apr_key_level := wf_engine.GetItemAttrText(
3058 itemtype => p_itemtype,
3059 itemkey => p_itemkey,
3060 aname => 'APR_KEY_LEVEL');
3061
3062 l_approved_at_level := wf_engine.GetItemAttrText(
3063 itemtype => p_itemtype,
3064 itemkey => p_itemkey,
3065 aname => 'APPROVED_AT_LEVEL');
3066
3067 l_approvers_visited := wf_engine.GetItemAttrText(
3068 itemtype => p_itemtype,
3069 itemkey => p_itemkey,
3070 aname => 'APPROVERS_VISITED');
3071
3072 --
3073 -- next iteration takes us below key approver,
3074 -- next approver allowed to timeout
3075 --
3076 if l_approvers_visited + 1 < l_apr_key_level then
3077 --sb_msgs_pkg.trace('10 - if');
3078
3079 if g_debug then
3080 hr_utility.trace('not final 1');
3081 end if;
3082
3083 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3084 itemkey => p_itemkey,
3085 aname => 'APPROVAL_TIMEOUT',
3086 avalue => l_default_timeout);
3087 p_result := 'COMPLETE:N';
3088 --
3089 -- next iteration takes us to key approver,
3090 -- determine timeout properties for next approver
3091 --
3092 elsif l_approvers_visited + 1 = l_apr_key_level then
3093 --sb_msgs_pkg.trace('20 - else if');
3094
3095 --
3096 -- if supervisor exists for next approver,
3097 -- next approver allowed to timeout
3098 --
3099 if get_supervisor(l_apr_person_id,
3100 SYSDATE) is not null then
3101 --sb_msgs_pkg.trace('22 - if');
3102
3103 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3104 itemkey => p_itemkey,
3105 aname => 'APPROVAL_TIMEOUT',
3106 avalue => l_default_timeout);
3107 --
3108 -- if supervisor does not exist for next approver,
3109 -- next approver NOT allowed to timeout
3110 else
3111 --sb_msgs_pkg.trace('24 - else');
3112
3113 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3114 itemkey => p_itemkey,
3115 aname => 'APPROVAL_TIMEOUT',
3116 avalue => 0);
3117 end if;
3118
3119 if g_debug then
3120 hr_utility.trace('not final 2');
3121 end if;
3122
3123 p_result := 'COMPLETE:N';
3124 --
3125 -- next iteration takes us above key approver,
3126 -- if key approver has approved then no need for further notification,
3127 -- if key approver has not approved (ie. timeout) then iterate,
3128 -- next approver not allowed to timeout (notification must wait here)
3129 --
3130 elsif l_approvers_visited + 1 > l_apr_key_level then
3131 --sb_msgs_pkg.trace('30 - else if');
3132
3133 if l_approved_at_level >= l_apr_key_level then
3134 --sb_msgs_pkg.trace('32 - if');
3135 if g_debug then
3136 hr_utility.trace('Yes final 1');
3137 end if;
3138 p_result := 'COMPLETE:Y';
3139 else
3140 --sb_msgs_pkg.trace('34 - else');
3141
3142 if g_debug then
3143 hr_utility.trace('not final 3');
3144 end if;
3145
3146 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3147 itemkey => p_itemkey,
3148 aname => 'APPROVAL_TIMEOUT',
3149 avalue => 0);
3150 p_result := 'COMPLETE:N';
3151 end if;
3152 end if;
3153
3154 --
3155 -- timeout to be used by next notification
3156 --
3157 l_approval_timeout := wf_engine.GetItemAttrText(
3158 itemtype => p_itemtype,
3159 itemkey => p_itemkey,
3160 aname => 'APPROVAL_TIMEOUT');
3161 --sb_msgs_pkg.trace('l_apr_timeout(A)>' || l_approval_timeout || '<');
3162 end if;
3163
3164 if p_funcmode = 'CANCEL' then
3165 if g_debug then
3166 hr_utility.trace('cancelled');
3167 end if;
3168
3169 p_result := 'COMPLETE';
3170 end if;
3171
3172 if p_funcmode = 'TIMEOUT' then
3173
3174 if g_debug then
3175 hr_utility.trace('completed');
3176 end if;
3177
3178 p_result := 'COMPLETE';
3179 end if;
3180
3181 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3182 --sb_msgs_pkg.end_call(l_proc);
3183 exception
3184 when others then
3185 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3186 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3187
3188 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3189 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3190 raise;
3191 end is_final_apr;
3192
3193
3194 -- Added new procedure to check if Timecard owner is active in fnd_user as on Sysdate
3195 -- for bug 8594271
3196 procedure check_user_exists(
3197 p_itemtype in varchar2,
3198 p_itemkey in varchar2,
3199 p_actid in number,
3200 p_funcmode in varchar2,
3201 p_result in out nocopy varchar2)
3202 is
3203 l_proc varchar2(100);
3204 l_tc_user_name fnd_user.user_name%TYPE;
3205 l_count NUMBER;
3206
3207 begin
3208
3209 if g_debug then
3210 l_proc := g_pkg || '.' || 'check_user_exists';
3211 hr_utility.set_location(l_proc, 10);
3212 end if;
3213
3214 if p_funcmode = 'RUN' then
3215
3216 l_tc_user_name := wf_engine.GetItemAttrText(
3217 itemtype => p_itemtype,
3218 itemkey => p_itemkey,
3219 aname => 'TC_OWNER_SS_LOGIN');
3220
3221 select count(*) into l_count
3222 from fnd_user
3223 where user_name = l_tc_user_name
3224 and TRUNC(sysdate) between TRUNC(start_date) and NVL(TRUNC(end_date), TRUNC(sysdate));
3225
3226 if l_count = 0 then
3227 p_result := 'COMPLETE:N';
3228 else
3229 p_result := 'COMPLETE:Y';
3230 end if;
3231
3232 end if;
3233
3234 if p_funcmode = 'CANCEL' then
3235 if g_debug then
3236 hr_utility.trace('cancelled');
3237 end if;
3238
3239 p_result := 'COMPLETE';
3240 end if;
3241
3242 if p_funcmode = 'TIMEOUT' then
3243
3244 if g_debug then
3245 hr_utility.trace('completed');
3246 end if;
3247
3248 p_result := 'COMPLETE';
3249 end if;
3250
3251
3252 end check_user_exists;
3253
3254
3255 --
3256 -- formula decides approval style to use
3257 --
3258 procedure formula_selects_mechanism(
3259 p_itemtype in varchar2,
3260 p_itemkey in varchar2,
3261 p_actid in number,
3262 p_funcmode in varchar2,
3263 p_result in out nocopy varchar2)
3264 is
3265 l_proc varchar2(61);
3266
3267 l_inputs ff_exec.inputs_t;
3268 l_outputs ff_exec.outputs_t;
3269
3270 l_effective_date date;
3271 l_formula_id hxc_approval_comps.approval_mechanism_id%type;
3272
3273 -- formula return values
3274 l_approval_mechanism hxc_approval_comps.approval_mechanism%type;
3275 l_approval_mechanism_id hxc_approval_comps.approval_mechanism_id%type;
3276 l_wf_item_type hxc_approval_comps.wf_item_type%type;
3277 l_wf_process_name hxc_approval_comps.wf_name%type;
3278 l_person_id per_all_assignments_f.person_id%type;
3279 l_formula_status varchar2(10);
3280 l_formula_message varchar2(2000);
3281 begin
3282 --sb_msgs_pkg.begin_call(l_proc);
3283 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
3284
3285 if g_debug then
3286 l_proc := g_pkg || '.' || 'formula_selects_mechanism';
3287 hr_utility.set_location(l_proc, 10);
3288 end if;
3289
3290 l_person_id := wf_engine.GetItemAttrNumber(
3291 itemtype => p_itemtype,
3292 itemkey => p_itemkey,
3293 aname => 'RESOURCE_ID');
3294 if p_funcmode = 'RUN' then
3295 l_effective_date := wf_engine.GetItemAttrDate(
3296 itemtype => p_itemtype,
3297 itemkey => p_itemkey,
3298 aname => 'APP_END_DATE');
3299
3300 l_formula_id := wf_engine.GetItemAttrNumber(
3301 itemtype => p_itemtype,
3302 itemkey => p_itemkey,
3303 aname => 'FORMULA_ID');
3304
3305 if g_debug then
3306 hr_utility.set_location(l_proc, 20);
3307 end if;
3308
3309 --
3310 -- initialise formula
3311 --
3312 --sb_msgs_pkg.trace('STEP 10 - before init_formula()');
3313 -- gaz - remove
3314 ff_utils.set_debug(127);
3315 ff_exec.init_formula(l_formula_id, l_effective_date, l_inputs,l_outputs);
3316
3317 --
3318 -- set up the inputs and contexts to formula
3319 -- nb. no contexts are used
3320 --
3321
3322 if g_debug then
3323 hr_utility.set_location(l_proc, 30);
3324 end if;
3325
3326 hr_utility.trace('Input count is:'||l_inputs.count);
3327
3328 -- Added IF END IF condition for bug 8646570
3329 -- do not assign values to the formula inputs when none of the inputs are used within the formula
3330
3331 if (l_inputs.count > 0) then
3332 for i in l_inputs.first..l_inputs.last loop
3333
3334 hr_utility.trace('Input name:'||l_inputs(i).name);
3335
3336 if l_inputs(i).name = 'TIMECARD_BB_ID' then
3337
3338 if g_debug then
3339 hr_utility.set_location(l_proc, 40);
3340 end if;
3341 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3342 itemtype => p_itemtype,
3343 itemkey => p_itemkey,
3344 aname => 'TC_BLD_BLK_ID');
3345
3346 elsif l_inputs(i).name = 'TIMECARD_BB_OVN' then
3347 if g_debug then
3348 hr_utility.set_location(l_proc, 50);
3349 end if;
3350 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3351 itemtype => p_itemtype,
3352 itemkey => p_itemkey,
3353 aname => 'TC_BLD_BLK_OVN');
3354
3355 elsif l_inputs(i).name = 'APPLICATION_PERIOD_BB_ID' then
3356 if g_debug then
3357 hr_utility.set_location(l_proc, 60);
3358 end if;
3359 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3360 itemtype => p_itemtype,
3361 itemkey => p_itemkey,
3362 aname => 'APP_BB_ID');
3363
3364 elsif l_inputs(i).name = 'APPLICATION_PERIOD_BB_OVN' then
3365 if g_debug then
3366 hr_utility.set_location(l_proc, 70);
3367 end if;
3368 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3369 itemtype => p_itemtype,
3370 itemkey => p_itemkey,
3371 aname => 'APP_BB_OVN');
3372
3373 elsif l_inputs(i).name = 'TIME_RECIPIENT_ID' then
3374 if g_debug then
3375 hr_utility.set_location(l_proc, 80);
3376 end if;
3377 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3378 itemtype => p_itemtype,
3379 itemkey => p_itemkey,
3380 aname => 'TIME_RECIPIENT_ID');
3381 l_inputs(i).value := 5;
3382
3383 elsif l_inputs(i).name = 'RESOURCE_ID' then
3384 if g_debug then
3385 hr_utility.set_location(l_proc, 90);
3386 end if;
3387 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3388 itemtype => p_itemtype,
3389 itemkey => p_itemkey,
3390 aname => 'RESOURCE_ID');
3391
3392 elsif l_inputs(i).name = 'RESOURCE_TYPE' then
3393 if g_debug then
3394 hr_utility.set_location(l_proc, 100);
3395 end if;
3396 l_inputs(i).value := 'abc';
3397
3398 else
3399 --
3400 -- context not recognised
3401 --
3402 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3403 hr_utility.set_message_token('PROCEDURE', l_proc);
3404 hr_utility.set_message_token('STEP', '20');
3405 hr_utility.raise_error;
3406 end if;
3407 end loop;
3408 end if; -- Bug 8646570
3409
3410 if g_debug then
3411 hr_utility.set_location(l_proc, 110);
3412 end if;
3413 --sb_msgs_pkg.trace('STEP 30 - before run_formula()');
3414 ff_exec.run_formula(l_inputs, l_outputs);
3415
3416 if g_debug then
3417 hr_utility.set_location(l_proc, 120);
3418 end if;
3419
3420 --
3421 -- obtain return values,
3422 -- there should be at least three outputs
3423 --
3424 assert(l_outputs.count >= 3, l_proc || ':STEP 40');
3425
3426 for i in l_outputs.first..l_outputs.last loop
3427
3428 if g_debug then
3429 hr_utility.set_location(l_proc, 130);
3430 end if;
3431
3432 if l_outputs(i).name = 'APPROVAL_MECHANISM' then
3433 l_approval_mechanism := l_outputs(i).value;
3434
3435 elsif l_outputs(i).name = 'APPROVAL_MECHANISM_ID' then
3436 l_approval_mechanism_id := l_outputs(i).value;
3437
3438 elsif l_outputs(i).name = 'WF_ITEM_TYPE' then
3439 l_wf_item_type := l_outputs(i).value;
3440
3441 elsif l_outputs(i).name = 'WF_PROCESS_NAME' then
3442 l_wf_process_name := l_outputs(i).value;
3443
3444 elsif l_outputs(i).name = 'FORMULA_STATUS' then
3445 l_formula_status := l_outputs(i).value;
3446
3447 elsif l_outputs(i).name = 'FORMULA_MESSAGE' then
3448 l_formula_message := l_outputs(i).value;
3449
3450 else
3451 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3452 hr_utility.set_message_token('PROCEDURE', l_proc);
3453 hr_utility.set_message_token('STEP', '50');
3454 hr_utility.raise_error;
3455 end if;
3456 end loop;
3457
3458 --
3459 -- check whether formual has raised an error and act appropriately
3460 --
3461 if g_debug then
3462 hr_utility.set_location(l_proc, 140);
3463 end if;
3464
3465 if l_formula_status = 'E' then
3466 --
3467 -- formula has failed, raise an error
3468 --
3469 if g_debug then
3470 hr_utility.set_location(l_proc, 150);
3471 end if;
3472
3473 if l_formula_message is null then
3474 --
3475 -- user not defined an error message, raise OTC default message
3476 -- stub - need our own error message
3477 --
3478 fnd_message.set_name('PAY', 'HR_6648_ELE_ENTRY_FORMULA_ERR');
3479 hr_utility.raise_error;
3480 else
3481 --
3482 -- user has defined message, raise it
3483 --
3484 fnd_message.set_name('PAY', 'HR_ELE_ENTRY_FORMULA_HINT');
3485 fnd_message.set_token('FORMULA_TEXT', l_formula_message, false);
3486 hr_utility.raise_error;
3487 end if;
3488
3489 elsif l_formula_status = 'W' then
3490
3491 if g_debug then
3492 hr_utility.set_location(l_proc, 160);
3493 end if;
3494 --
3495 -- formula has failed, but only warning necessary
3496 --
3497 if l_formula_message is null then
3498 --
3499 -- user has not defined an error message
3500 --
3501 fnd_message.set_name('PAY', 'HR_6648_ELE_ENTRY_FORMULA_ERR');
3502 hr_utility.set_warning;
3503 else
3504 --
3505 -- user has defined message, raise it
3506 --
3507 fnd_message.set_name('PAY', 'HR_ELE_ENTRY_FORMULA_HINT');
3508 fnd_message.set_token('FORMULA_TEXT', l_formula_message, false);
3509 hr_utility.set_warning;
3510 end if;
3511 end if;
3512
3513 --
3514 -- set up context for approval mechanism
3515 --
3516 -- auto approval no further context is required
3517 --
3518 if g_debug then
3519 hr_utility.set_location(l_proc, 170);
3520 end if;
3521
3522 if l_approval_mechanism = 'AUTO_APPROVE' then
3523 --sb_msgs_pkg.trace('formula selects auto approve approval');
3524 null;
3525
3526 --
3527 -- person approval, set approving person id context
3528 --
3529 elsif l_approval_mechanism = 'PERSON' then
3530 --sb_msgs_pkg.trace('formula selects person approval');
3531 if g_debug then
3532 hr_utility.set_location(l_proc, 180);
3533 end if;
3534
3535 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3536 itemkey => p_itemkey,
3537 aname => 'APR_PERSON_ID',
3538 avalue => l_approval_mechanism_id);
3539
3540 if g_debug then
3541 hr_utility.set_location(l_proc, 190);
3542 end if;
3543
3544 --
3545 -- HR supervisor approval, approving person id can derived from
3546 -- resource id on application period,
3547 -- Find Approval Style activity has already been called, this should
3548 -- ensure that global context information has been set
3549 --
3550 elsif l_approval_mechanism = 'HR_SUPERVISOR' then
3551 --sb_msgs_pkg.trace('formula selects hr supervisor approval');
3552
3553 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3554 itemkey => p_itemkey,
3555 aname => 'APR_PERSON_ID',
3556 avalue => l_person_id);
3557
3558 --
3559 -- workflow approval, set workflow item type and workflow process name
3560 --
3561 elsif l_approval_mechanism = 'WORKFLOW' then
3562 --sb_msgs_pkg.trace('formula selects workflow approval');
3563
3564 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3565 itemkey => p_itemkey,
3566 aname => 'WF_ITEM_TYPE',
3567 avalue => l_wf_item_type);
3568
3569 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3570 itemkey => p_itemkey,
3571 aname => 'WF_PROCESS_NAME',
3572 avalue => l_wf_process_name);
3573
3574 elsif l_approval_mechanism = 'PROJECT_MANAGER'
3575 then
3576 NULL;
3577 else
3578 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3579 hr_utility.set_message_token('PROCEDURE', l_proc);
3580 hr_utility.set_message_token('STEP', '60');
3581 hr_utility.raise_error;
3582 end if;
3583
3584 --
3585 -- set result code for transition to next activity
3586 --
3587 p_result := l_approval_mechanism;
3588 end if;
3589
3590 if p_funcmode = 'CANCEL' then
3591 p_result := 'COMPLETE';
3592 end if;
3593
3594 if p_funcmode = 'TIMEOUT' then
3595 p_result := 'COMPLETE';
3596 end if;
3597
3598 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3599 --sb_msgs_pkg.end_call(l_proc);
3600 exception
3601 when others then
3602 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3603 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3604
3605 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3606 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3607 raise;
3608 end formula_selects_mechanism;
3609
3610
3611
3612 --
3613 -- launch user defined workflow process
3614 --
3615 procedure launch_wf_process(
3616 p_itemtype in varchar2,
3617 p_itemkey in varchar2,
3618 p_actid in number,
3619 p_funcmode in varchar2,
3620 p_result in out nocopy varchar2)
3621 is
3622 l_proc constant varchar2(61) := g_pkg || '.' || 'launch_wf_process';
3623 l_item_key number;
3624 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
3625 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
3626 l_wf_item_type hxc_approval_comps.wf_item_type%type;
3627 l_wf_process_name hxc_approval_comps.wf_name%type;
3628 l_apr_person_id per_all_assignments_f.person_id%type;
3629 l_resource_id per_all_assignments_f.person_id%type;
3630 begin
3631 --sb_msgs_pkg.begin_call(l_proc);
3632 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
3633
3634 l_ap_bbid := wf_engine.GetItemAttrNumber(
3635 itemtype => p_itemtype,
3636 itemkey => p_itemkey,
3637 aname => 'APP_BB_ID');
3638
3639 l_ap_bbovn := wf_engine.GetItemAttrNumber(
3640 itemtype => p_itemtype,
3641 itemkey => p_itemkey,
3642 aname => 'APP_BB_OVN');
3643
3644 l_wf_item_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
3645 itemkey => p_itemkey,
3646 aname => 'WF_ITEM_TYPE');
3647
3648 l_wf_process_name := wf_engine.GetItemAttrText(
3649 itemtype => p_itemtype,
3650 itemkey => p_itemkey,
3651 aname => 'WF_PROCESS_NAME');
3652
3653 --
3654 -- get key for item instance
3655 --
3656 SELECT hxc_approval_wf_s.nextval
3657 INTO l_item_key
3658 FROM DUAL;
3659
3660 --sb_msgs_pkg.trace('l_item_key>' || l_item_key || '<');
3661
3662 wf_engine.createProcess(l_wf_item_type, l_item_key, l_wf_process_name);
3663
3664
3665 --
3666 -- set attributes required by child workflow,
3667 -- set parent item type and key in child workflow process
3668 --
3669 wf_engine.SetItemAttrText(itemtype => l_wf_item_type,
3670 itemkey => l_item_key,
3671 aname => 'PARENT_ITEM_TYPE',
3672 avalue => p_itemtype);
3673
3674 wf_engine.SetItemAttrText(itemtype => l_wf_item_type,
3675 itemkey => l_item_key,
3676 aname => 'PARENT_ITEM_KEY',
3677 avalue => p_itemkey);
3678 --
3679 wf_engine.SetItemParent
3680 (itemtype => l_wf_item_type,
3681 itemkey => l_item_key,
3682 parent_itemtype => p_itemtype,
3683 parent_itemkey => p_itemkey,
3684 parent_context => NULL);
3685 --
3686 -- launch customer's workflow process
3687 --
3688 wf_engine.startProcess(l_wf_item_type, l_item_key);
3689
3690 l_apr_person_id := wf_engine.GetItemAttrNumber(
3691 itemtype => p_itemtype,
3692 itemkey => p_itemkey,
3693 aname => 'APR_PERSON_ID');
3694 l_resource_id := wf_engine.GetItemAttrNumber(
3695 itemtype => p_itemtype,
3696 itemkey => p_itemkey,
3697 aname => 'RESOURCE_ID');
3698 --OIT Enhancement
3699 --FYI Notification to SUPERVISOR on timecard SUBMISSION if he is not direct approver
3700 if(hxc_approval_wf_helper.is_approver_supervisor(l_apr_person_id,l_resource_id)) then
3701 hxc_approval_wf_helper.set_notif_attribute_values
3702 (p_itemtype,
3703 p_itemkey,
3704 null,
3705 null
3706 );
3707 else
3708 hxc_approval_wf_helper.set_notif_attribute_values
3709 (p_itemtype,
3710 p_itemkey,
3711 hxc_app_comp_notifications_api.c_action_request_approval,
3712 hxc_app_comp_notifications_api.c_recipient_supervisor
3713 );
3714 end if;
3715
3716 --
3717 -- next activity waits for child processes to complete
3718 --
3719 p_result := 'COMPLETE';
3720
3721 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3722 --sb_msgs_pkg.end_call(l_proc);
3723 exception
3724 when others then
3725 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3726 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3727
3728 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3729 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3730 raise;
3731 end launch_wf_process;
3732
3733
3734
3735 --
3736 -- test out workflow result
3737 --
3738 procedure test_wf_result(
3739 p_itemtype in varchar2,
3740 p_itemkey in varchar2,
3741 p_actid in number,
3742 p_funcmode in varchar2,
3743 p_result in out nocopy varchar2)
3744 is
3745 l_proc constant varchar2(61) := g_pkg || '.' || 'test_wf_result';
3746 l_datetime constant date := SYSDATE;
3747 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
3748 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
3749 l_apr_rej_reason varchar2(2000);
3750 l_wf_apr_result varchar2(80);
3751 begin
3752 --sb_msgs_pkg.begin_call(l_proc);
3753 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
3754
3755 if p_funcmode = 'RUN' then
3756 l_wf_apr_result := wf_engine.GetItemAttrText(
3757 itemtype => p_itemtype,
3758 itemkey => p_itemkey,
3759 aname => 'WF_APPROVAL_RESULT');
3760 --sb_msgs_pkg.trace('WF_APPROVAL_RESULT>' || l_wf_apr_result || '<');
3761
3762 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3763 itemkey => p_itemkey,
3764 aname => 'APPROVAL_STATUS',
3765 avalue => l_wf_apr_result);
3766
3767
3768 if l_wf_apr_result = 'APPROVED' then
3769 --OIT Enhancement
3770 --FYI Notification to PREPARER on timecard APPROVAL
3771 hxc_approval_wf_helper.set_notif_attribute_values
3772 (p_itemtype,
3773 p_itemkey,
3774 hxc_app_comp_notifications_api.c_action_approved,
3775 hxc_app_comp_notifications_api.c_recipient_preparer
3776 );
3777 p_result := 'COMPLETE:APPROVED';
3778
3779 elsif l_wf_apr_result = 'REJECTED' then
3780 --OIT Enhancement
3781 --FYI Notification to PREPARER on timecard REJECTION
3782 hxc_approval_wf_helper.set_notif_attribute_values
3783 (p_itemtype,
3784 p_itemkey,
3785 hxc_app_comp_notifications_api.c_action_rejected,
3786 hxc_app_comp_notifications_api.c_recipient_preparer
3787 );
3788 p_result := 'COMPLETE:REJECTED';
3789
3790 else
3791 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3792 hr_utility.set_message_token('PROCEDURE', l_proc);
3793 hr_utility.set_message_token('STEP', '10');
3794 hr_utility.raise_error;
3795 end if;
3796 end if;
3797
3798 if p_funcmode = 'CANCEL' then
3799 p_result := 'COMPLETE';
3800 end if;
3801
3802 if p_funcmode = 'TIMEOUT' then
3803 p_result := 'COMPLETE';
3804 end if;
3805
3806 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3807 --sb_msgs_pkg.end_call(l_proc);
3808 exception
3809 when others then
3810 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3811 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3812
3813 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3814 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3815 raise;
3816 end test_wf_result;
3817
3818
3819 PROCEDURE set_next_app_period(
3820 p_itemtype in varchar2,
3821 p_itemkey in varchar2,
3822 p_actid in number,
3823 p_funcmode in varchar2,
3824 p_result in out nocopy varchar2
3825 )
3826 IS
3827 l_next_period_id hxc_time_building_blocks.time_building_block_id%TYPE;
3828 l_next_period_ovn hxc_time_building_blocks.object_version_number%TYPE;
3829
3830
3831 l_proc VARCHAR2(150);
3832
3833 BEGIN
3834 g_debug:=hr_utility.debug_enabled;
3835 if g_debug then
3836 l_proc := 'set_next_app_period';
3837 hr_utility.trace('in set next period');
3838 end if;
3839 IF p_funcmode = 'RUN'
3840 THEN
3841 l_next_period_id := wf_engine.GetItemAttrNumber(
3842 itemtype => p_itemtype,
3843 itemkey => p_itemkey,
3844 aname => 'NEXT_APP_BB_ID');
3845
3846 if g_debug then
3847 hr_utility.set_location(l_proc, 60);
3848 end if;
3849
3850 l_next_period_ovn := wf_engine.GetItemAttrNumber(
3851 itemtype => p_itemtype,
3852 itemkey => p_itemkey,
3853 aname => 'NEXT_APP_BB_OVN');
3854
3855 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3856 itemkey => p_itemkey,
3857 aname => 'APP_BB_ID',
3858 avalue => l_next_period_id);
3859
3860 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3861 itemkey => p_itemkey,
3862 aname => 'APP_BB_OVN',
3863 avalue => l_next_period_ovn);
3864
3865 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3866 itemkey => p_itemkey,
3867 aname => 'APR_REJ_REASON',
3868 avalue => '');
3869
3870 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3871 itemkey => p_itemkey,
3872 aname => 'APPROVAL_STATUS',
3873 avalue => '');
3874
3875
3876 if g_debug then
3877 hr_utility.trace('next app id=' || l_next_period_id);
3878 hr_utility.trace('next app ovn=' || l_next_period_ovn);
3879 end if;
3880 p_result := 'COMPLETE';
3881 END IF;
3882
3883 IF p_funcmode = 'CANCEL'
3884 THEN
3885 p_result := 'COMPLETE';
3886 END IF;
3887
3888 IF p_funcmode = 'TIMEOUT'
3889 THEN
3890 p_result := 'COMPLETE';
3891 END IF;
3892
3893 EXCEPTION
3894 WHEN OTHERS THEN
3895
3896
3897 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3898 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3899 raise;
3900 END set_next_app_period;
3901
3902 end hxc_find_notify_aprs_pkg;