[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.18.12010000.2 2008/08/05 11:59:06 ubhat 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
765 cursor c_approval_comp_alt
766 (p_application_period_id in hxc_app_period_summary.application_period_id%type
767 ,p_application_period_ovn in hxc_app_period_summary.application_period_ovn%type) is
768 SELECT apc.approval_mechanism,
769 apc.approval_mechanism_id,
770 apc.wf_item_type,
771 apc.wf_name
772 FROM hxc_app_period_summary apsum
773 ,hxc_approval_comps apc
774 ,hxc_tc_ap_links tcl
775 ,hxc_time_building_blocks tbb
776 WHERE apsum.application_period_id = p_application_period_id
777 AND apsum.application_period_ovn = p_application_period_ovn
778 and apsum.application_period_id = tcl.application_period_id
779 and tcl.timecard_id = tbb.time_building_block_id
780 and tbb.scope = 'TIMECARD'
781 and tbb.date_to = hr_general.end_of_time
782 and tbb.approval_style_id = apc.approval_style_id
783 and apsum.time_recipient_id = apc.time_recipient_id;
784
785 CURSOR c_tc_info(
786 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
787 )
788 IS
789 SELECT tcsum.resource_id,
790 tcsum.start_time,
791 tcsum.stop_time
792 FROM hxc_timecard_summary tcsum
793 WHERE tcsum.timecard_id = p_tc_bbid;
794
795
796 l_proc constant varchar2(61) := g_pkg || '.' || 'find_apr_style';
797 l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type;
798 l_tc_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
799 l_ap_bb_id hxc_time_building_blocks.time_building_block_id%type;
800 l_ap_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
801 l_login fnd_user.user_name%type;
802 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
803 l_tc_start_time hxc_time_building_blocks.start_time%TYPE;
804 l_tc_stop_time hxc_time_building_blocks.stop_time%TYPE;
805 l_approval_mechanism hxc_approval_comps.approval_mechanism%TYPE;
806 l_approval_mechanism_id hxc_approval_comps.approval_mechanism_id%TYPE;
807 l_wf_item_type hxc_approval_comps.wf_item_type%TYPE;
808 l_wf_name hxc_approval_comps.wf_name%TYPE;
809 begin
810 g_debug:=hr_utility.debug_enabled;
811 --sb_msgs_pkg.begin_call(l_proc);
812 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
813 g_trace := '10';
814
815 if p_funcmode = 'RUN' then
816 l_tc_bb_id := wf_engine.GetItemAttrNumber(
817 itemtype => p_itemtype,
818 itemkey => p_itemkey,
819 aname => 'TC_BLD_BLK_ID');
820
821 l_tc_bb_ovn := wf_engine.GetItemAttrNumber(
822 itemtype => p_itemtype,
823 itemkey => p_itemkey,
824 aname => 'TC_BLD_BLK_OVN');
825
826 l_ap_bb_id := wf_engine.GetItemAttrNumber(
827 itemtype => p_itemtype,
828 itemkey => p_itemkey,
829 aname => 'APP_BB_ID');
830
831 l_ap_bb_ovn := wf_engine.GetItemAttrNumber(
832 itemtype => p_itemtype,
833 itemkey => p_itemkey,
834 aname => 'APP_BB_OVN');
835
836 g_trace := '20';
837
838 if l_tc_bb_id is null or l_tc_bb_ovn is null or
839 l_ap_bb_id is null or l_ap_bb_ovn is null then
840 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
841 hr_utility.set_message_token('PROCEDURE', l_proc);
842 hr_utility.set_message_token('STEP', '10');
843 hr_utility.raise_error;
844 end if;
845
846 g_trace := '30';
847
848
849 --fetch timecard detail
850 open c_tc_info(l_tc_bb_id);
851 fetch c_tc_info into l_resource_id, l_tc_start_time, l_tc_stop_time;
852
853 if c_tc_info%notfound then
854
855 close c_tc_info;
856 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
857 hr_utility.set_message_token('PROCEDURE', l_proc);
858 hr_utility.set_message_token('STEP', '12');
859 hr_utility.raise_error;
860 end if;
861
862 close c_tc_info;
863
864
865 g_trace := '40';
866
867 --fetch approval detail
868 if g_debug then
869 hr_utility.trace('app id=' || l_ap_bb_id);
870 hr_utility.trace('app ovn=' || l_ap_bb_ovn);
871 end if;
872 OPEN c_approval_comp(l_ap_bb_id, l_ap_bb_ovn);
873 FETCH c_approval_comp INTO l_approval_mechanism
874 ,l_approval_mechanism_id
875 ,l_wf_item_type
876 ,l_wf_name;
877
878
879 IF c_approval_comp%NOTFOUND
880 THEN
881 g_trace := '50';
882
883 CLOSE c_approval_comp;
884
885 --
886 -- This should never happen, but if it does
887 -- try getting the approval component info
888 -- using the other cursor
889 --
890
891 open c_approval_comp_alt(l_ap_bb_id, l_ap_bb_ovn);
892 FETCH c_approval_comp_alt INTO l_approval_mechanism
893 ,l_approval_mechanism_id
894 ,l_wf_item_type
895 ,l_wf_name;
896
897 if(c_approval_comp_alt%notfound) then
898 close c_approval_comp_alt;
899 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
900 hr_utility.set_message_token('PROCEDURE', l_proc);
901 hr_utility.set_message_token('STEP', '18');
902 hr_utility.raise_error;
903 else
904 close c_approval_comp_alt;
905 end if;
906
907 else
908 CLOSE c_approval_comp;
909 END IF;
910 if g_debug then
911 hr_utility.trace('mechanism=' || l_approval_mechanism);
912 hr_utility.trace('mechanism_id=' || l_approval_mechanism_id);
913 hr_utility.trace('item_type=' || l_wf_item_type);
914 hr_utility.trace('wf_name=' || l_wf_name);
915 end if;
916 g_trace := '60';
917
918 -- set tokens used by all notifications
919 --
920 wf_engine.SetItemAttrText(
921 itemtype => p_itemtype,
922 itemkey => p_itemkey,
923 aname => 'TC_OWNER',
924 avalue => get_name(l_resource_id,l_tc_start_time)
925 );
926
927 g_trace := '70';
928 --
929 -- set attribute to specify timecard owner's self service login
930 --
931 l_login := get_login(p_person_id => l_resource_id);
932
933 g_trace := '80';
934
935 --
936 -- if null returned, timecard owner does not have a self
937 -- service login name, where does notification get sent?
938 --
939 if l_login is null then
940 g_trace := '90';
941
942 -- 5027063: Try creating an adhoc user
943 begin
944
945 l_login := hxc_approval_helper.createAdHocUser
946 (p_resource_id => l_resource_id,
947 p_effective_date => l_tc_start_time
948 );
949
950 exception
951 when others then
952 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
953 hr_utility.set_message_token('PROCEDURE', l_proc);
954 hr_utility.set_message_token('STEP', '20');
955 hr_utility.raise_error;
956 end;
957
958 end if;
959
960 g_trace := '100';
961
962 --set role attribute
963 wf_engine.SetItemAttrText(
964 itemtype => p_itemtype,
965 itemkey => p_itemkey,
966 aname => 'TC_FROM_ROLE',
967 avalue => l_login);
968
969 g_trace := '110';
970
971 wf_engine.SetItemAttrText(itemtype => p_itemtype,
972 itemkey => p_itemkey,
973 aname => 'TC_OWNER_SS_LOGIN',
974 avalue => l_login);
975
976 g_trace := '120';
977
978 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
979 itemkey => p_itemkey,
980 aname => 'TC_START',
981 avalue => l_tc_start_time);
982 g_trace := '130';
983
984 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
985 itemkey => p_itemkey,
986 aname => 'TC_STOP',
987 avalue => l_tc_stop_time);
988
989 g_trace := '140';
990
991 if l_approval_mechanism = 'AUTO_APPROVE'
992 then
993
994 g_trace := '150';
995 p_result := 'COMPLETE:AUTO_APPROVE';
996
997 elsif l_approval_mechanism = 'PERSON'
998 then
999 g_trace := '160';
1000 --
1001 -- set parameters required by next activity
1002 --
1003 wf_engine.SetItemAttrNumber(
1004 itemtype => p_itemtype,
1005 itemkey => p_itemkey,
1006 aname => 'APR_PERSON_ID',
1007 avalue => l_approval_mechanism_id);
1008
1009 g_trace := '170';
1010 p_result := 'COMPLETE:PERSON';
1011
1012 elsif l_approval_mechanism = 'HR_SUPERVISOR'
1013 then
1014 g_trace := '180';
1015 wf_engine.SetItemAttrNumber(
1016 itemtype => p_itemtype,
1017 itemkey => p_itemkey,
1018 aname => 'APR_PERSON_ID',
1019 avalue => l_resource_id);
1020
1021 g_trace := '190';
1022 p_result := 'COMPLETE:HR_SUPERVISOR';
1023
1024 -- GPaytonM fix version 115.6
1025
1026 elsif l_approval_mechanism = 'FORMULA_MECHANISM'
1027 then
1028 g_trace := '200';
1029 wf_engine.SetItemAttrNumber(
1030 itemtype => p_itemtype,
1031 itemkey => p_itemkey,
1032 aname => 'FORMULA_ID',
1033 avalue => l_approval_mechanism_id);
1034
1035 g_trace := '210';
1036 p_result := 'COMPLETE:FORMULA';
1037
1038 elsif l_approval_mechanism = 'WORKFLOW'
1039 then
1040
1041 g_trace := '220';
1042 wf_engine.SetItemAttrText(
1043 itemtype => p_itemtype,
1044 itemkey => p_itemkey,
1045 aname => 'WF_ITEM_TYPE',
1046 avalue => l_wf_item_type);
1047
1048 wf_engine.SetItemAttrText(
1049 itemtype => p_itemtype,
1050 itemkey => p_itemkey,
1051 aname => 'WF_PROCESS_NAME',
1052 avalue => l_wf_name);
1053
1054 g_trace := '230';
1055 p_result := 'COMPLETE:WORKFLOW';
1056
1057 elsif l_approval_mechanism = 'PROJECT_MANAGER'
1058 then
1059 g_trace := '240';
1060 p_result := 'COMPLETE:PROJECT_MANAGER';
1061
1062 else
1063 g_trace := '250';
1064 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1065 hr_utility.set_message_token('PROCEDURE', l_proc);
1066 hr_utility.set_message_token('STEP', '30');
1067 hr_utility.raise_error;
1068 end if;
1069
1070 end if;
1071
1072 g_trace := '260';
1073 if p_funcmode = 'CANCEL' then
1074 p_result := 'COMPLETE';
1075 end if;
1076
1077 g_trace := '270';
1078 if p_funcmode = 'TIMEOUT' then
1079 p_result := 'COMPLETE';
1080 end if;
1081
1082 g_trace := '300';
1083 exception
1084 when others then
1085
1086 --
1087 -- record this function call in the error system in case of an exception
1088 --
1089 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1090 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode,
1091 l_proc || '|' || g_trace);
1092 raise;
1093 end find_apr_style;
1094
1095
1096 PROCEDURE set_real_approver(
1097 p_itemtype in varchar2
1098 ,p_itemkey in varchar2
1099 )
1100 IS
1101 l_employee_id fnd_user.employee_id%TYPE;
1102 l_real_approver VARCHAR2(500);
1103
1104
1105 BEGIN
1106 -- Bug 3490263
1107 l_employee_id := hxc_approval_wf_pkg.find_mysterious_approver(p_itemtype,p_itemkey);
1108
1109
1110 IF l_employee_id <> -1
1111 THEN
1112 l_real_approver := get_name(l_employee_id,sysdate);
1113 END IF;
1114
1115
1116 wf_engine.SetItemAttrText(
1117 itemtype => p_itemtype,
1118 itemkey => p_itemkey,
1119 aname => 'APR_NAME',
1120 avalue => l_real_approver
1121 );
1122
1123 --Bug 5375656
1124 wf_engine.SetItemAttrText(
1125 itemtype => p_itemtype,
1126 itemkey => p_itemkey,
1127 aname => 'TC_APPROVER_FROM_ROLE',
1128 avalue => l_real_approver
1129 );
1130 END set_real_approver;
1131
1132
1133
1134
1135
1136 -- this work flow activity implies that the approver has 'approved'
1137 -- the notification, ie. not a timeout
1138 --
1139 procedure capture_approved_status(
1140 p_itemtype in varchar2,
1141 p_itemkey in varchar2,
1142 p_actid in number,
1143 p_funcmode in varchar2,
1144 p_result in out nocopy varchar2)
1145 is
1146 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_approved_status';
1147 l_approvers_visited number;
1148 begin
1149 g_debug:=hr_utility.debug_enabled;
1150 --sb_msgs_pkg.begin_call(l_proc);
1151 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
1152 if g_debug then
1153 hr_utility.trace('capture approved status');
1154 end if;
1155 if p_funcmode = 'RUN' then
1156
1157 set_real_approver(
1158 p_itemtype => p_itemtype
1159 ,p_itemkey => p_itemkey
1160 );
1161 --
1162 -- set variables for approval hierarchy
1163 --
1164 l_approvers_visited := wf_engine.GetItemAttrNumber(
1165 itemtype => p_itemtype,
1166 itemkey => p_itemkey,
1167 aname => 'APPROVERS_VISITED');
1168 if g_debug then
1169 hr_utility.trace('l_approvers_visited=' || l_approvers_visited);
1170 end if;
1171 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
1172 itemkey => p_itemkey,
1173 aname => 'APPROVED_AT_LEVEL',
1174 avalue => l_approvers_visited);
1175
1176 --
1177 -- set up attribute required for next activity
1178 --
1179 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1180 itemkey => p_itemkey,
1181 aname => 'APPROVAL_STATUS',
1182 avalue => 'APPROVED');
1183
1184 p_result := 'COMPLETE';
1185 end if;
1186
1187 if p_funcmode = 'CANCEL' then
1188 p_result := 'COMPLETE';
1189 end if;
1190
1191 if p_funcmode = 'TIMEOUT' then
1192 p_result := 'COMPLETE';
1193 end if;
1194
1195 --OIT Enhancement
1196 --FYI Notification to PREPARER on timecard APPROVAL
1197 hxc_approval_wf_helper.set_notif_attribute_values
1198 (p_itemtype,
1199 p_itemkey,
1200 hxc_app_comp_notifications_api.c_action_approved,
1201 hxc_app_comp_notifications_api.c_recipient_preparer
1202 );
1203 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
1204 --sb_msgs_pkg.end_call(l_proc);
1205 exception
1206 when others then
1207 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1208 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1209
1210 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1211 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1212 raise;
1213 end capture_approved_status;
1214
1215 procedure capture_timeout_status(
1216 p_itemtype in varchar2,
1217 p_itemkey in varchar2,
1218 p_actid in number,
1219 p_funcmode in varchar2,
1220 p_result in out nocopy varchar2)
1221 is
1222 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_timeout_status';
1223 begin
1224 --sb_msgs_pkg.begin_call(l_proc);
1225 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
1226
1227 if p_funcmode = 'RUN' then
1228 --
1229 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1230 itemkey => p_itemkey,
1231 aname => 'APPROVAL_STATUS',
1232 avalue => 'APPROVED');
1233
1234 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1235 itemkey => p_itemkey,
1236 aname => 'APR_REJ_REASON',
1237 avalue => 'TIMED_OUT');
1238
1239 p_result := 'COMPLETE';
1240 end if;
1241
1242 if p_funcmode = 'CANCEL' then
1243 p_result := 'COMPLETE';
1244 end if;
1245
1246 if p_funcmode = 'TIMEOUT' then
1247 p_result := 'COMPLETE';
1248 end if;
1249
1250 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
1251 --sb_msgs_pkg.end_call(l_proc);
1252 exception
1253 when others then
1254 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1255 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1256
1257 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1258 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1259 raise;
1260 end capture_timeout_status;
1261
1262 --
1263 -- reject comment after notification has been responded to
1264 --
1265 procedure capture_rejected_status(
1266 p_itemtype in varchar2,
1267 p_itemkey in varchar2,
1268 p_actid in number,
1269 p_funcmode in varchar2,
1270 p_result in out nocopy varchar2)
1271 is
1272 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_rejection_status';
1273 begin
1274 --sb_msgs_pkg.begin_call(l_proc);
1275 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
1276
1277 if p_funcmode = 'RUN' then
1278 set_real_approver(
1279 p_itemtype => p_itemtype
1280 ,p_itemkey => p_itemkey
1281 );
1282 --
1283 -- set up attribute required for next activity
1284 --
1285 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1286 itemkey => p_itemkey,
1287 aname => 'APPROVAL_STATUS',
1288 avalue => 'REJECTED');
1289
1290 p_result := 'COMPLETE';
1291 end if;
1292
1293 if p_funcmode = 'CANCEL' then
1294 p_result := 'COMPLETE';
1295 end if;
1296
1297 if p_funcmode = 'TIMEOUT' then
1298 p_result := 'COMPLETE';
1299 end if;
1300
1301 --OIT Enhancement
1302 --FYI Notification to PREPARER on timecard REJECTION
1303 hxc_approval_wf_helper.set_notif_attribute_values
1304 (p_itemtype,
1305 p_itemkey,
1306 hxc_app_comp_notifications_api.c_action_rejected,
1307 hxc_app_comp_notifications_api.c_recipient_preparer
1308 );
1309
1310 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
1311 --sb_msgs_pkg.end_call(l_proc);
1312 exception
1313 when others then
1314 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1315 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1316
1317 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1318 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1319 raise;
1320 end capture_rejected_status;
1321
1322 FUNCTION GetProjectManager(
1323 p_project_id IN NUMBER
1324 )
1325 RETURN NUMBER
1326 IS
1327 BEGIN
1328 RETURN 10250;
1329 END GetProjectManager;
1330
1331 --
1332 -- Project Manager Mechanism
1333 --
1334
1335 procedure find_project_manager(
1336 p_itemtype in varchar2,
1337 p_itemkey in varchar2,
1338 p_actid in number,
1339 p_funcmode in varchar2,
1340 p_result in out nocopy varchar2
1341 )
1342 is
1343
1344 CURSOR c_project_id(
1345 p_ap_bb_id hxc_time_building_blocks.time_building_block_id%TYPE
1346 )
1347 IS
1348 SELECT hta.attribute1
1349 FROM hxc_ap_detail_links apdet
1350 ,hxc_time_building_blocks htbb
1351 ,hxc_time_attribute_usages htau
1352 ,hxc_time_attributes hta
1353 WHERE apdet.application_period_id = p_ap_bb_id
1354 AND apdet.time_building_block_id = htbb.time_building_block_id
1355 AND htbb.date_to = hr_general.end_of_time
1356 AND htbb.time_building_block_id = htau.time_building_block_id
1357 AND htbb.object_version_number = htau.time_building_block_ovn
1358 AND htau.time_attribute_id = hta.time_attribute_id
1359 AND hta.attribute_category = 'PROJECTS';
1360
1361 cursor c_project_id_deleted_detail
1362 (p_app_bb_id in hxc_time_building_blocks.time_building_block_id%type) is
1363 select to_number(ta.attribute1)
1364 from hxc_time_building_blocks details,
1365 hxc_time_building_blocks days,
1366 hxc_time_attribute_usages tau,
1367 hxc_app_period_summary aps,
1368 hxc_time_Attributes ta
1369 where aps.application_period_id = p_app_bb_id
1370 and aps.start_time <= days.stop_time
1371 and aps.stop_time >= days.start_time
1372 and aps.resource_id = days.resource_Id
1373 and details.parent_building_block_Id = days.time_building_block_id
1374 and details.parent_building_block_ovn = days.object_version_number
1375 and details.date_to <> hr_general.end_of_time
1376 and details.object_version_number =
1377 (select max(details2.object_version_number)
1378 from hxc_time_building_blocks details2
1379 where details.time_building_block_id = details2.time_building_block_id)
1380 and details.time_building_block_id = tau.time_building_block_id
1381 and details.object_version_number = tau.time_building_block_ovn
1382 and tau.time_Attribute_Id = ta.time_attribute_id
1383 and ta.attribute_category = 'PROJECTS'
1384 and not exists
1385 (select 1
1386 from hxc_ap_detail_links adl
1387 where adl.application_period_id = aps.application_period_id
1388 and adl.time_building_block_id = details.time_building_block_id
1389 and adl.time_building_block_ovn = details.object_version_number
1390 );
1391
1392 l_project_id VARCHAR2(150);
1393 l_project_procedure VARCHAR2(150) := 'get_project_manager';
1394 l_dyn_sql VARCHAR2(2000);
1395 l_ap_bb_id NUMBER;
1396 l_project_manager NUMBER := NULL;
1397
1398 l_proc VARCHAR2(100);
1399 l_is_blank varchar2(1);
1400 begin
1401 g_debug:=hr_utility.debug_enabled;
1402 if g_debug then
1403 l_proc := g_pkg || 'find_project_manager';
1404 hr_utility.trace('in ' || l_proc);
1405 end if;
1406 l_ap_bb_id := wf_engine.GetItemAttrNumber(
1407 itemtype => p_itemtype,
1408 itemkey => p_itemkey,
1409 aname => 'APP_BB_ID');
1410
1411 l_is_blank := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1412 itemkey => p_itemkey,
1413 aname => 'IS_DIFF_TC',
1414 ignore_notfound => true);
1415
1416
1417 --
1418 -- First: Attempt to find the project id from a live time detail.
1419 --
1420 open c_project_id(l_ap_bb_id);
1421 fetch c_project_id into l_project_id;
1422 if c_project_id%notfound then
1423 close c_project_id;
1424 --
1425 -- Bug fix: 4177451: 115.65. In the case of a deleted line,
1426 -- check to see if the project id can be determined from an
1427 -- end dated detail.
1428 --
1429 open c_project_id_deleted_detail(l_ap_bb_id);
1430 fetch c_project_id_deleted_detail into l_project_id;
1431 close c_project_id_deleted_detail;
1432 else
1433 close c_project_id;
1434 end if;
1435
1436 if l_is_blank = 'Y' then
1437 l_project_id := null;
1438 end if;
1439
1440 if(l_project_id is not null) then
1441
1442 --find project manager id
1443
1444 l_dyn_sql := 'BEGIN '|| fnd_global.newline
1445 || ':1 := Pa_Otc_Api.GetProjectManager' ||fnd_global.newline
1446 ||'(p_project_id => :2);' ||fnd_global.newline
1447 ||'END;';
1448
1449 EXECUTE IMMEDIATE l_dyn_sql
1450 using OUT l_project_manager, IN l_project_id;
1451
1452
1453 IF l_project_manager IS NULL
1454 THEN
1455 g_trace :=' project manager is null';
1456
1457 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1458 hr_utility.set_message_token('PROCEDURE', l_proc);
1459 hr_utility.set_message_token('STEP', '10');
1460 hr_utility.raise_error;
1461
1462 END IF;
1463
1464 wf_engine.SetItemAttrNumber(
1465 itemtype => p_itemtype,
1466 itemkey => p_itemkey,
1467 aname => 'APR_PERSON_ID',
1468 avalue => l_project_manager);
1469
1470 p_result := 'COMPLETE';
1471 ELSE
1472 --
1473 -- Bug fix: 4291206: 115.66. If the user has updated instead of
1474 -- deleting the line, such that the new project manager is different
1475 -- we need to notify the previous project manager to ensure we can complete
1476 -- the ELA approval. We can look up the previous approver, because we
1477 -- stored it when we were generating the application period.
1478 --
1479 l_project_manager := hxc_approval_wf_util.get_previous_approver
1480 (p_itemtype,p_itemkey,l_ap_bb_id);
1481 if(l_project_manager is not null) then
1482 wf_engine.SetItemAttrNumber
1483 (itemtype => p_itemtype,
1484 itemkey => p_itemkey,
1485 aname => 'APR_PERSON_ID',
1486 avalue => l_project_manager);
1487 p_result := 'COMPLETE';
1488 else
1489 if g_debug then
1490 hr_utility.trace('project id and previous approver are null');
1491 end if;
1492 g_trace :='no project id or approver';
1493 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1494 hr_utility.set_message_token('PROCEDURE', l_proc);
1495 hr_utility.set_message_token('STEP', '20');
1496 hr_utility.raise_error;
1497 end if;
1498 END IF;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 -- The line below records this function call in the error system
1503 -- in the case of an exception.
1504 --
1505 if g_debug then
1506 hr_utility.set_location(l_proc, 999);
1507 --
1508 hr_utility.trace('IN EXCEPTION IN find_project_manager');
1509 --
1510 end if;
1511 wf_core.context('HCAPPRWF', l_proc,
1512 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode, g_trace);
1513 raise;
1514 p_result := '';
1515 return;
1516 END find_project_manager;
1517
1518
1519
1520 -- auto approval mechanism
1521 --
1522 procedure auto_approval(
1523 p_itemtype in varchar2,
1524 p_itemkey in varchar2,
1525 p_actid in number,
1526 p_funcmode in varchar2,
1527 p_result in out nocopy varchar2)
1528 is
1529 l_proc constant varchar2(61) := g_pkg || '.' || 'auto_approval';
1530 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
1531 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
1532 begin
1533 --sb_msgs_pkg.begin_call(l_proc);
1534
1535 if p_funcmode = 'RUN' then
1536 --
1537 -- set up attribute required for next activity
1538 --
1539 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1540 itemkey => p_itemkey,
1541 aname => 'APPROVAL_STATUS',
1542 avalue => 'APPROVED');
1543
1544 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1545 itemkey => p_itemkey,
1546 aname => 'APR_REJ_REASON',
1547 avalue => 'AUTO_APPROVE');
1548
1549 --OIT Enhancement
1550 --FYI Notification to WORKER on timecard AUTO APPROVAL
1551 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
1552 (p_itemtype,
1553 p_itemkey,
1554 hxc_app_comp_notifications_api.c_action_auto_approve,
1555 hxc_app_comp_notifications_api.c_recipient_worker
1556 );
1557
1558 p_result := 'COMPLETE';
1559 end if;
1560
1561 if p_funcmode = 'CANCEL' then
1562 p_result := 'COMPLETE';
1563 end if;
1564
1565 if p_funcmode = 'TIMEOUT' then
1566 p_result := 'COMPLETE';
1567 end if;
1568
1569 --sb_msgs_pkg.end_call(l_proc);
1570 exception
1571 when others then
1572 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
1573 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
1574
1575 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1576 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1577 raise;
1578 end auto_approval;
1579
1580
1581 FUNCTION category_timecard_hrs (
1582 p_app_per_id NUMBER
1583 , p_time_category_name VARCHAR2 )
1584 RETURN NUMBER
1585 IS
1586
1587 CURSOR csr_get_timecard(p_app_per_id NUMBER) IS
1588 SELECT adl.TIME_BUILDING_BLOCK_ID bb_id,
1589 adl.TIME_BUILDING_BLOCK_OVN ovn
1590 FROM hxc_time_building_blocks tbb
1591 , hxc_ap_detail_links adl
1592 WHERE adl.APPLICATION_PERIOD_ID = p_app_per_id
1593 AND
1594 tbb.time_building_block_id = adl.time_building_block_id AND
1595 tbb.object_version_number = adl.time_building_block_ovn AND
1596 tbb.date_to = hr_general.end_of_time;
1597
1598 CURSOR csr_get_person_id(p_app_per_id NUMBER) IS
1599 SELECT resource_id
1600 FROM hxc_time_building_blocks tbb
1601 WHERE tbb.time_building_block_id = p_app_per_id;
1602
1603 /* Bug fix for 5526281 */
1604 CURSOR get_timecard_start_date(p_app_per_id NUMBER) IS
1605 SELECT tbb.start_time ,tbb.stop_time
1606 FROM hxc_tc_ap_links htl,
1607 hxc_time_building_blocks tbb
1608 WHERE htl.application_period_id = p_app_per_id
1609 AND tbb.time_building_block_id = htl.timecard_id;
1610
1611 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
1612 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
1613 /* end of bug fix for 5526281 */
1614
1615 l_tc_rec csr_get_timecard%ROWTYPE;
1616 l_timecard_hrs NUMBER := 0;
1617 l_detail_hrs NUMBER := 0;
1618 l_time_category_id hxc_time_categories.time_category_id%TYPE;
1619 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
1620 l_precision VARCHAR2(4);
1621 l_rounding_rule VARCHAR2(20);
1622 l_tc_start_date date;
1623
1624 /* Bug fix for 5526281 */
1625 l_tc_end_date date;
1626 l_pref_eval_date date;
1627 l_emp_hire_date date;
1628 /* end of bug fix for 5526281 */
1629
1630 BEGIN
1631
1632 OPEN csr_get_person_id ( p_app_per_id );
1633 FETCH csr_get_person_id into l_resource_id;
1634 CLOSE csr_get_person_id;
1635
1636 /* Bug fix for 5526281 */
1637 OPEN get_timecard_start_date ( p_app_per_id );
1638 FETCH get_timecard_start_date into l_tc_start_date,l_tc_end_date;
1639 CLOSE get_timecard_start_date;
1640
1641 OPEN emp_hire_info (l_resource_id);
1642 FETCH emp_hire_info into l_emp_hire_date;
1643 CLOSE emp_hire_info;
1644
1645 if trunc(l_emp_hire_date) >= trunc(l_tc_start_date) and trunc(l_emp_hire_date) <= trunc(l_tc_end_date) then
1646 l_pref_eval_date := trunc(l_emp_hire_date);
1647 else
1648 l_pref_eval_date := trunc(l_tc_start_date);
1649 end if;
1650
1651 l_precision := hxc_preference_evaluation.resource_preferences
1652 (l_resource_id,
1653 'TC_W_TCRD_UOM',
1654 3,
1655 l_pref_eval_date
1656 );
1657
1658 l_rounding_rule := hxc_preference_evaluation.resource_preferences
1659 (l_resource_id,
1660 'TC_W_TCRD_UOM',
1661 4,
1662 l_pref_eval_date
1663 );
1664
1665 /* end of bug fix for 5526281 */
1666
1667 if l_precision is null
1668 then
1669 l_precision := '2';
1670 end if;
1671
1672 if l_rounding_rule is null
1673 then
1674 l_rounding_rule := 'ROUND_TO_NEAREST';
1675 end if;
1676
1677 l_time_category_id := HXC_TIME_CATEGORY_UTILS_PKG.get_time_category_id ( p_time_category_name => p_time_category_name );
1678
1679 OPEN csr_get_timecard ( p_app_per_id );
1680 FETCH csr_get_timecard into l_tc_rec;
1681
1682 WHILE csr_get_timecard%FOUND
1683 LOOP
1684 -- call category_detail_hrs
1685 l_detail_hrs := HXC_TIME_CATEGORY_UTILS_PKG.category_detail_hrs (
1686 p_tbb_id => l_tc_rec.bb_id
1687 , p_tbb_ovn => l_tc_rec.ovn
1688 , p_time_category_id => l_time_category_id );
1689
1690 l_timecard_hrs := l_timecard_hrs + apply_round_rule(l_rounding_rule,
1691 l_precision,
1692 l_detail_hrs);
1693
1694 FETCH csr_get_timecard INTO l_tc_rec;
1695 END LOOP;
1696
1697 CLOSE csr_get_timecard;
1698 RETURN l_timecard_hrs;
1699
1700 END category_timecard_hrs;
1701
1702 FUNCTION category_timecard_hrs (
1703 p_start_date date,
1704 p_end_date date,
1705 p_resource_id number,
1706 p_time_category_name VARCHAR2 )
1707 RETURN NUMBER
1708 IS
1709
1710 CURSOR csr_get_details
1711 IS
1712 select details.time_building_block_id bb_id, details.object_version_number ovn
1713 from hxc_time_building_blocks timecard,
1714 hxc_time_building_blocks details,
1715 hxc_time_building_blocks days
1716 where timecard.time_building_block_id = days.parent_building_block_id
1717 and timecard.object_version_number = days.parent_building_block_ovn
1718 and days.time_building_block_id = details.parent_building_block_id
1719 and days.object_version_number = details.parent_building_block_ovn
1720 and details.date_to = hr_general.end_of_time
1721 and days.start_time <=p_end_date
1722 and days.stop_time >= p_start_date
1723 and days.resource_id = p_resource_id
1724 and details.scope = 'DETAIL'
1725 and timecard.scope = 'TIMECARD';
1726
1727 l_tc_rec csr_get_details%ROWTYPE;
1728 l_timecard_hrs NUMBER := 0;
1729 l_detail_hrs NUMBER := 0;
1730 l_time_category_id hxc_time_categories.time_category_id%TYPE;
1731 l_precision VARCHAR2(4);
1732 l_rounding_rule VARCHAR2(20);
1733
1734 BEGIN
1735 l_precision := hxc_preference_evaluation.resource_preferences
1736 (p_resource_id,
1737 'TC_W_TCRD_UOM',
1738 3,
1739 p_start_date
1740 );
1741
1742 l_rounding_rule := hxc_preference_evaluation.resource_preferences
1743 (p_resource_id,
1744 'TC_W_TCRD_UOM',
1745 4,
1746 p_start_date
1747 );
1748
1749 if l_precision is null
1750 then
1751 l_precision := '2';
1752 end if;
1753
1754 if l_rounding_rule is null
1755 then
1756 l_rounding_rule := 'ROUND_TO_NEAREST';
1757 end if;
1758
1759 l_time_category_id := HXC_TIME_CATEGORY_UTILS_PKG.get_time_category_id ( p_time_category_name => p_time_category_name );
1760
1761 OPEN csr_get_details;
1762 FETCH csr_get_details into l_tc_rec;
1763
1764 WHILE csr_get_details%FOUND
1765 LOOP
1766 -- call category_detail_hrs
1767 l_detail_hrs := HXC_TIME_CATEGORY_UTILS_PKG.category_detail_hrs (
1768 p_tbb_id => l_tc_rec.bb_id
1769 , p_tbb_ovn => l_tc_rec.ovn
1770 , p_time_category_id => l_time_category_id );
1771
1772 l_timecard_hrs := l_timecard_hrs + apply_round_rule(l_rounding_rule,
1773 l_precision,
1774 l_detail_hrs);
1775
1776 FETCH csr_get_details INTO l_tc_rec;
1777 END LOOP;
1778
1779 CLOSE csr_get_details;
1780 RETURN l_timecard_hrs;
1781
1782 END category_timecard_hrs;
1783 --
1784 -- person approval mechanism
1785 --
1786 procedure person_approval(
1787 p_itemtype in varchar2,
1788 p_itemkey in varchar2,
1789 p_actid in number,
1790 p_funcmode in varchar2,
1791 p_result in out nocopy varchar2)
1792 is
1793
1794 cursor c_item_attribute_values(p_item_key in varchar2) is
1795 select name,text_value
1796 from wf_item_attribute_values
1797 where item_type = 'HXCEMP'
1798 and item_key = p_item_key;
1799
1800 l_proc constant varchar2(61) := g_pkg || '.' || 'person_approval';
1801 l_effective_end_date date;
1802 l_effective_start_date date;
1803 l_apr_person_id per_all_assignments_f.person_id%type;
1804 l_login fnd_user.user_name%type;
1805 --
1806 -- Bug 4153585
1807 -- Increased size for translation
1808 l_title varchar2(4000);
1809 l_total_hours number;
1810 l_otl_appr_id varchar2(50);
1811 l_appl_period_bb_id number;
1812 l_resource_id number;
1813 l_is_blank varchar2(1);
1814 l_supervisor_id number;
1815 begin
1816 if p_funcmode = 'RUN' then
1817 --
1818 -- all datetrack data should be valid for duration of application period
1819 --
1820 l_effective_end_date := wf_engine.GetItemAttrDate(
1821 itemtype => p_itemtype,
1822 itemkey => p_itemkey,
1823 aname => 'APP_END_DATE');
1824
1825 l_effective_start_date := wf_engine.GetItemAttrDate(
1826 itemtype => p_itemtype,
1827 itemkey => p_itemkey,
1828 aname => 'APP_START_DATE');
1829
1830 l_apr_person_id := wf_engine.GetItemAttrNumber(
1831 itemtype => p_itemtype,
1832 itemkey => p_itemkey,
1833 aname => 'APR_PERSON_ID');
1834
1835 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
1836 itemtype => p_itemtype,
1837 itemkey => p_itemkey,
1838 aname => 'APP_BB_ID');
1839 l_is_blank := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1840 itemkey => p_itemkey ,
1841 aname => 'IS_DIFF_TC',
1842 ignore_notfound => true);
1843 --Added as part of OIT enhancement
1844 l_resource_id := wf_engine.GetItemAttrNumber(
1845 itemtype => p_itemtype,
1846 itemkey => p_itemkey,
1847 aname => 'RESOURCE_ID');
1848
1849 -- Check if the approver is terminated (Bug#3160848)
1850 if validate_person (l_apr_person_id,SYSDATE)
1851 then
1852 IF l_is_blank = 'Y' THEN
1853
1854 l_apr_person_id := get_supervisor(l_resource_id, sysdate);
1855
1856 wf_engine.SetItemAttrNumber(
1857 itemtype => p_itemtype,
1858 itemkey => p_itemkey,
1859 aname => 'APR_PERSON_ID',
1860 avalue => l_apr_person_id);
1861 ELSE
1862 -- Check if the approver is terminated (Bug#3160848)
1863
1864 hr_utility.set_message(809, 'HXC_APPR_WF_TERMINATED_APPR');-- If approver is terminated then raise an error
1865 hr_utility.raise_error;
1866 end if;
1867 END IF;
1868
1869 --
1870 -- set attribute to specify approver's self service login
1871 --
1872 l_login := get_login(p_person_id => l_apr_person_id);
1873
1874 --
1875 -- if null returned, approver does not have a self service login name,
1876 -- where does notification get sent?
1877 --
1878 if l_login is null then
1879 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1880 hr_utility.set_message_token('PROCEDURE', l_proc);
1881 hr_utility.set_message_token('STEP', '10');
1882 hr_utility.raise_error;
1883 end if;
1884
1885 --sb_msgs_pkg.trace('approver login>' || l_login || '<');
1886
1887 wf_engine.SetItemAttrText(
1888 itemtype => p_itemtype,
1889 itemkey => p_itemkey,
1890 aname => 'APR_SS_LOGIN',
1891 avalue => l_login);
1892
1893 --
1894 -- set information for notification
1895 --
1896
1897
1898 wf_engine.SetItemAttrText(
1899 itemtype => p_itemtype,
1900 itemkey => p_itemkey,
1901 aname => 'APR_NAME',
1902 avalue => get_name(l_apr_person_id,l_effective_end_date)
1903 );
1904 wf_engine.SetItemAttrText(
1905 itemtype => p_itemtype,
1906 itemkey => p_itemkey,
1907 aname => 'TC_APPROVER_FROM_ROLE',
1908 avalue => l_login);
1909
1910 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1911 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1912 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1913
1914 l_title := fnd_message.get();
1915
1916 wf_engine.SetItemAttrText(
1917 itemtype => p_itemtype,
1918 itemkey => p_itemkey,
1919 aname => 'TITLE',
1920 avalue => l_title);
1921
1922 l_otl_appr_id := l_appl_period_bb_id||'#'||p_itemkey;
1923
1924 wf_engine.SetItemAttrText(
1925 itemtype => p_itemtype,
1926 itemkey => p_itemkey,
1927 aname => 'OTL_APPR_ID',
1928 avalue => l_otl_appr_id);
1929
1930 wf_engine.SetItemAttrText(
1931 itemtype => p_itemtype,
1932 itemkey => p_itemkey,
1933 aname => 'DESCRIPTION',
1934 avalue => get_description(l_appl_period_bb_id));
1935
1936 wf_engine.SetItemAttrNumber(
1937 itemtype => p_itemtype,
1938 itemkey => p_itemkey,
1939 aname => 'TOTAL_TC_HOURS',
1940 avalue => l_total_hours);
1941 --OIT Enhancement
1942 --FYI Notification to SUPERVISOR on timecard SUBMISSION if he is not the direct approver.
1943 if(HXC_APPROVAL_WF_HELPER.is_approver_supervisor(l_apr_person_id,l_resource_id)) then
1944 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
1945 (p_itemtype,
1946 p_itemkey,
1947 null,
1948 null
1949 );
1950 else
1951 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
1952 (p_itemtype,
1953 p_itemkey,
1954 hxc_app_comp_notifications_api.c_action_request_approval,
1955 hxc_app_comp_notifications_api.c_recipient_supervisor
1956 );
1957 end if;
1958
1959 p_result := 'COMPLETE';
1960 end if;
1961
1962 if p_funcmode = 'CANCEL' then
1963 p_result := 'COMPLETE';
1964 end if;
1965
1966 if p_funcmode = 'TIMEOUT' then
1967 p_result := 'COMPLETE';
1968 end if;
1969
1970 /*
1971 Bug 3449786
1972
1973 Added commit. At this point we're about to send a notification
1974 which could take several days for a response, therefore to
1975 save rollback segments, we issue a commit here, knowing that
1976 the workflow data is set appropriately.
1977
1978 */
1979
1980 commit;
1981
1982 exception
1983 when others then
1984 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
1985 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1986 raise;
1987 end person_approval;
1988
1989 --Function to check if a given approver is terminated (Bug#3160848)
1990 --Changed for 115.60; used per_all_people_f instead of per_people_f
1991 FUNCTION validate_person(
1992 p_person_id in number,
1993 p_effective_date in date)
1994 RETURN BOOLEAN
1995 is
1996
1997 cursor csr_validate_person(b_person_id number, b_effective_date date) is
1998 SELECT 1
1999 FROM per_all_people_f per,
2000 per_person_types ppt,
2001 per_person_type_usages_f pptu
2002 WHERE per.person_id = b_person_id
2003 AND TRUNC(b_effective_date) between TRUNC(per.effective_Start_date) and TRUNC(per.effective_end_date)
2004 AND TRUNC(b_effective_date) between TRUNC(pptu.effective_Start_date) and TRUNC(pptu.effective_end_date)
2005 AND pptu.person_id = per.person_id
2006 AND pptu.person_type_id = ppt.person_type_id
2007 AND ppt.system_person_type in ('EMP','CWK');
2008
2009 temp number;
2010
2011 begin
2012
2013 open csr_validate_person(p_person_id, trunc(p_effective_date));
2014 fetch csr_validate_person into temp;
2015
2016 IF csr_validate_person%NOTFOUND
2017 THEN
2018 close csr_validate_person;
2019 RETURN TRUE;
2020 END IF;
2021
2022 CLOSE csr_validate_person;
2023 RETURN FALSE;
2024
2025 end validate_person;
2026
2027 --
2028 --
2029 --
2030 procedure inc_approvers_visited(
2031 p_itemtype in varchar2,
2032 p_itemkey in varchar2)
2033 is
2034 l_approvers_visited number;
2035 begin
2036 l_approvers_visited := wf_engine.GetItemAttrNumber(
2037 itemtype => p_itemtype,
2038 itemkey => p_itemkey,
2039 aname => 'APPROVERS_VISITED');
2040
2041 l_approvers_visited := l_approvers_visited + 1;
2042
2043 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2044 itemkey => p_itemkey,
2045 aname => 'APPROVERS_VISITED',
2046 avalue => l_approvers_visited);
2047 end inc_approvers_visited;
2048
2049
2050
2051 PROCEDURE process_extension_func2(
2052 p_tc_id IN hxc_time_building_blocks.time_building_block_id%TYPE
2053 ,p_tc_ovn IN hxc_time_building_blocks.object_version_number%TYPE
2054 ,p_time_recipient IN hxc_time_recipients.time_recipient_id%TYPE
2055 ,p_ext_func2 IN hxc_time_recipients.extension_function2%TYPE
2056 ,p_previous_approver IN NUMBER
2057 ,p_next_approver OUT NOCOPY NUMBER
2058 )
2059 IS
2060
2061 l_previous_approver_id number := p_previous_approver;
2062 l_approver_person_id number := NULL;
2063 l_message varchar2(2000);
2064 l_func_sql varchar2(2000);
2065 l_message_index number;
2066 l_message_table hxc_self_service_time_deposit.message_table;
2067 BEGIN
2068 --
2069 -- Sets up global variables for timecard records.
2070 --
2071 hxc_self_service_time_deposit.get_timecard_tables(
2072 p_timecard_id => p_tc_id
2073 ,p_timecard_ovn => p_tc_ovn
2074 ,p_timecard_blocks => hxc_approval_wf_pkg.g_time_building_blocks
2075 ,p_timecard_app_attributes => hxc_approval_wf_pkg.g_time_app_attributes
2076 ,p_time_recipient_id => p_time_recipient);
2077
2078
2079
2080 l_func_sql := 'BEGIN '||fnd_global.newline
2081 ||p_ext_func2 ||fnd_global.newline
2082 ||'(p_previous_approver_id => :1' ||fnd_global.newline
2083 ||',x_approver_person_id => :2' ||fnd_global.newline
2084 ||',x_messages => :3);' ||fnd_global.newline
2085 ||'END;';
2086
2087 EXECUTE IMMEDIATE l_func_sql
2088 using IN OUT l_previous_approver_id,
2089 IN OUT l_approver_person_id,
2090 IN OUT l_message;
2091
2092 if g_debug then
2093 hr_utility.trace('After client extension');
2094 --
2095 hr_utility.trace('Previous APPR ID is : ' || to_char(l_previous_approver_id));
2096 hr_utility.trace('APPR ID is : ' || to_char(l_approver_person_id));
2097 hr_utility.trace('Message is : ' || l_message);
2098 end if;
2099 IF l_message IS NOT NULL
2100 THEN
2101 l_message_table := hxc_deposit_wrapper_utilities.string_to_messages
2102 (p_message_string => l_message);
2103
2104 IF l_message_table.count > 0
2105 THEN
2106 l_message_index := l_message_table.first;
2107
2108 FND_MESSAGE.SET_NAME
2109 (l_message_table(l_message_index).application_short_name
2110 ,l_message_table(l_message_index).message_name
2111 );
2112
2113 FND_MESSAGE.RAISE_ERROR;
2114 END IF;
2115
2116 END IF;
2117
2118 p_next_approver := l_approver_person_id;
2119
2120
2121 EXCEPTION
2122 WHEN OTHERS THEN
2123 raise;
2124
2125 END process_extension_func2;
2126
2127
2128
2129 --
2130 -- supervisor approval mechanism
2131 --
2132 procedure hr_supervisor_approval(
2133 p_itemtype in varchar2,
2134 p_itemkey in varchar2,
2135 p_actid in number,
2136 p_funcmode in varchar2,
2137 p_result in out nocopy varchar2)
2138 is
2139 --
2140 cursor csr_get_extension(p_time_recipient number) is
2141 select htr.extension_function2
2142 from hxc_time_recipients htr
2143 where htr.time_recipient_id = p_time_recipient;
2144
2145
2146
2147 cursor c_details_of_timecard( l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type )
2148 is
2149 select 'Y'
2150 from hxc_tc_ap_links tcl,
2151 hxc_ap_detail_links adl
2152 where tcl.timecard_id = l_tc_bb_id
2153 and tcl.application_period_id = adl.application_period_id
2154 and rownum < 2;
2155
2156 cursor c_find_app_per_id( l_ap_bb_id in hxc_time_building_blocks.time_building_block_id%type)
2157 is
2158 SELECT 'Y'
2159 FROM hxc_app_period_summary
2160 WHERE time_category_id = 0
2161 AND APPLICATION_PERIOD_ID = l_ap_bb_id
2162 AND approval_comp_id = (SELECT approval_comp_id
2163 FROM hxc_approval_comps
2164 WHERE approval_style_id =
2165 (SELECT approval_style_id
2166 FROM hxc_approval_styles
2167 WHERE NAME = 'SEEDED_APPL_PA_MGR')
2168 AND time_category_id = 0);
2169
2170
2171 cursor get_result
2172 is
2173 select text_value
2174 from wf_item_attribute_values
2175 where item_type = p_itemtype
2176 and item_key = p_itemkey
2177 and name = 'RESULT';
2178
2179 cursor c_appr_comp(p_app_bb_id number,p_app_bb_ovn number)
2180 is
2181 select approval_comp_id
2182 from hxc_app_period_summary
2183 where application_period_id = p_app_bb_id
2184 and application_period_ovn = p_app_bb_ovn;
2185
2186
2187 --
2188 l_proc constant varchar2(61) := g_pkg || '.' || 'hr_supervisor_approval';
2189 l_effective_start_date date;
2190 l_effective_end_date date;
2191 --l_effective_date date;
2192 --l_approval_timeout number;
2193 l_approvers_visited number;
2194 l_default_timeout number;
2195 --
2196 -- person who requires approval
2197 --
2198 l_person_id per_all_assignments_f.person_id%type;
2199 --
2200 l_supervisor_id per_all_assignments_f.supervisor_id%type;
2201 l_next_supervisor_id per_all_assignments_f.supervisor_id%type;
2202 l_login fnd_user.user_name%type;
2203 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
2204 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
2205
2206
2207
2208 l_ap_bb_id hxc_time_building_blocks.time_building_block_id%type;
2209 l_validate_flag varchar2(15);
2210 l_tc_has_details_flag varchar2(15);
2211
2212 --
2213 l_time_recipient varchar2(150);
2214 l_ext_func2 varchar2(2000);
2215 l_auto_approval_flag varchar2(1);
2216 l_approver_person_id number := NULL;
2217 l_previous_approver_id number := NULL;
2218 l_message varchar2(2000);
2219 l_func_sql varchar2(2000);
2220 l_tc_bld_blk_id number;
2221 l_tc_ovn number;
2222 --
2223 -- Bug 4153585
2224 -- Increased size for translation
2225 l_title varchar2(4000);
2226 l_description fnd_new_messages.message_text%type;
2227 l_otl_appr_id varchar2(50);
2228 l_appl_period_bb_id number;
2229 l_total_hours number;
2230 l_result varchar2(20);
2231 l_approval_component_id number;
2232 l_app_ovn number;
2233
2234 --
2235 begin
2236
2237 g_debug:=hr_utility.debug_enabled;
2238
2239 IF p_funcmode <> 'CANCEL' THEN
2240
2241 g_trace := 'Begin hr_supervisor_approval';
2242 if g_debug then
2243 hr_utility.trace('Begin hr_supervisor_approval');
2244 end if;
2245
2246 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
2247 (itemtype => p_itemtype,
2248 itemkey => p_itemkey,
2249 aname => 'TC_BLD_BLK_ID');
2250
2251 g_trace := 'Timecard BB ID is : ' || to_char(l_tc_bld_blk_id);
2252
2253 if g_debug then
2254 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
2255 end if;
2256 l_tc_ovn := wf_engine.GetItemAttrNumber
2257 (itemtype => p_itemtype,
2258 itemkey => p_itemkey,
2259 aname => 'TC_BLD_BLK_OVN');
2260
2261 l_approvers_visited := wf_engine.GetItemAttrNumber(
2262 itemtype => p_itemtype,
2263 itemkey => p_itemkey,
2264 aname => 'APPROVERS_VISITED');
2265
2266 l_time_recipient := wf_engine.GetItemAttrText(
2267 itemtype => p_itemtype,
2268 itemkey => p_itemkey ,
2269 aname => 'TIME_RECIPIENT_ID');
2270
2271 l_person_id := wf_engine.GetItemAttrNumber(
2272 itemtype => p_itemtype,
2273 itemkey => p_itemkey,
2274 aname => 'APR_PERSON_ID');
2275
2276 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
2277 itemtype => p_itemtype,
2278 itemkey => p_itemkey,
2279 aname => 'APP_BB_ID');
2280 l_app_ovn :=wf_engine.GetItemAttrNumber(
2281 itemtype => p_itemtype,
2282 itemkey => p_itemkey,
2283 aname => 'APP_BB_OVN');
2284 --OIT Change.
2285 --When the preparer chooses to resend the notification, we end up with sending the notification to
2286 --supervisor's supervisor since we are setting the 'APR_PERSON_ID' to supervisor id when we initially traversed
2287 --the HR_Supervisor_approval.During the resend we get the person id from APR_PERSON_ID (which is supervisor id)
2288 --and gets the supervisor of this resource id which is supervisor's supervisor.
2289
2290 open get_result;
2291 fetch get_result into l_result;
2292 close get_result;
2293
2294 if l_result = 'RESEND' then
2295
2296 wf_engine.SetItemAttrNumber(
2297 itemtype => p_itemtype,
2298 itemkey => p_itemkey,
2299 aname => 'APPROVERS_VISITED',
2300 avalue => l_approvers_visited-1);
2301 end if;
2302 --added to support OIT desuport
2303
2304 open c_appr_comp(l_appl_period_bb_id,l_app_ovn);
2305 fetch c_appr_comp into l_approval_component_id;
2306 close c_appr_comp;
2307
2308 if(hxc_notification_helper.run_extensions(l_approval_component_id)) then
2309 open csr_get_extension(to_number(l_time_recipient));
2310 fetch csr_get_extension into l_ext_func2;
2311 close csr_get_extension;
2312
2313 g_trace := 'Before client extension=' || l_ext_func2;
2314 if g_debug then
2315 hr_utility.trace('Before client extension=' || l_ext_func2);
2316 end if;
2317 -- Bug 4177487. For an empty Timecard (Timecard that does not have DETAIL level records),
2318 -- we need not call the PA extension function.
2319 open c_details_of_timecard( l_tc_bld_blk_id );
2320 fetch c_details_of_timecard into l_tc_has_details_flag;
2321 close c_details_of_timecard;
2322 else
2323 l_ext_func2 := null;
2324 l_tc_has_details_flag := 'N';
2325 end if; -- Run extensions
2326
2327
2328 IF l_ext_func2 IS NOT NULL and l_tc_has_details_flag = 'Y'
2329 THEN
2330 g_trace := 'extension not null';
2331 if g_debug then
2332 hr_utility.trace('extension not null');
2333 end if;
2334 IF hxc_approval_wf_pkg.code_chk(l_ext_func2)
2335 THEN
2336 g_trace := 'extension code exists';
2337 if g_debug then
2338 hr_utility.trace('extension code exists');
2339 end if;
2340 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2341 itemkey => p_itemkey,
2342 aname => 'APR_KEY_LEVEL',
2343 avalue => '100');
2344
2345 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2346 itemkey => p_itemkey,
2347 aname => 'APPROVAL_TIMEOUT',
2348 avalue => 0);
2349
2350 IF l_approvers_visited = 0
2351 THEN
2352 l_previous_approver_id := null;
2353 ELSE
2354 l_previous_approver_id := l_person_id;
2355 END IF;
2356
2357 g_trace := 'before processing extension l_previous_approver_id='
2358 || to_char(l_person_id);
2359 if g_debug then
2360 hr_utility.trace('before processing extension l_previous_approver_id='
2361 || to_char(l_person_id));
2362 end if;
2363
2364 --Bug 5386274
2365 --We need to get the result attribute, if it is RESEND then set the supervisor id to person id which we set
2366 --to supervisor id when we traversed this procedure initially.
2367 if l_result = 'RESEND' then
2368 l_supervisor_id :=l_person_id;
2369 else
2370 process_extension_func2(
2371 p_tc_id => l_tc_bld_blk_id
2372 ,p_tc_ovn => l_tc_ovn
2373 ,p_time_recipient => to_number(l_time_recipient)
2374 ,p_ext_func2 => l_ext_func2
2375 ,p_previous_approver => l_previous_approver_id
2376 ,p_next_approver => l_supervisor_id
2377 );
2378 end if;
2379
2380 g_trace := 'after processing extension l_supervisor_id='
2381 || to_char(l_supervisor_id);
2382 if g_debug then
2383 hr_utility.trace('after processing extension l_supervisor_id='
2384 || to_char(l_supervisor_id));
2385 end if;
2386 IF l_supervisor_id IS NOT NULL
2387 THEN
2388 g_trace := 'testing if this is the final approver';
2389 if g_debug then
2390 hr_utility.trace('testing if this is the final approver');
2391 end if;
2392 process_extension_func2(
2393 p_tc_id => l_tc_bld_blk_id
2394 ,p_tc_ovn => l_tc_ovn
2395 ,p_time_recipient => to_number(l_time_recipient)
2396 ,p_ext_func2 => l_ext_func2
2397 ,p_previous_approver => l_supervisor_id
2398 ,p_next_approver => l_next_supervisor_id
2399 );
2400
2401 g_trace := 'end calling extension';
2402 if g_debug then
2403 hr_utility.trace('end calling extension');
2404 end if;
2405 IF l_next_supervisor_id = -99
2406 THEN
2407 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2408 itemkey => p_itemkey,
2409 aname => 'FINAL_APR',
2410 avalue => 'YES');
2411
2412 g_trace := 'FINAL_APR is : YES';
2413 if g_debug then
2414 hr_utility.trace('FINAL_APR is : YES');
2415 end if;
2416 END IF;
2417
2418 g_trace := 'end testing final approver';
2419 if g_debug then
2420 hr_utility.trace('end testing final approver');
2421 end if;
2422 END IF;
2423
2424 ELSE
2425 g_trace := 'extension function=' || l_ext_func2 || 'not exist in db';
2426 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2427 hr_utility.set_message_token('PROCEDURE', l_proc);
2428 hr_utility.set_message_token('STEP', '10');
2429 hr_utility.raise_error;
2430
2431 END IF;
2432 ELSE -- if no client extension, find supervisor from assignments
2433 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2434 itemkey => p_itemkey,
2435 aname => 'FINAL_APR',
2436 avalue => 'NO_EXTENSION');
2437
2438
2439 --Bug 2777538 sonarasi 20-FEB-2003
2440 g_trace := 'no extension function checking assignment';
2441 if g_debug then
2442 hr_utility.trace('no extension function checking assignment');
2443 end if;
2444
2445 -- Bug 4202019. These variables need to be set outside this IF block.
2446 /*
2447 l_effective_end_date := wf_engine.GetItemAttrDate(
2448 itemtype => p_itemtype,
2449 itemkey => p_itemkey,
2450 aname => 'APP_END_DATE');
2451
2452 l_effective_start_date := wf_engine.GetItemAttrDate(
2453 itemtype => p_itemtype,
2454 itemkey => p_itemkey,
2455 aname => 'APP_START_DATE');
2456 */
2457
2458 --l_effective_date := l_effective_end_date;
2459
2460 --OIT Change
2461 --We need to get the result attribute, if it is RESEND then set the supervisor id to person id which we set
2462 --to supervisor id when we traversed this procedure initially.
2463
2464 if l_result = 'RESEND' then
2465 l_supervisor_id := l_person_id;
2466 else
2467 l_supervisor_id:= get_supervisor(l_person_id, SYSDATE);
2468 end if;
2469
2470 END IF;
2471
2472
2473 -- no supervisor found, does not make sense here
2474 -- eg. approval key level = 1
2475 -- expect martin to be found as supervisor
2476 --
2477 -- approval key level = 2
2478 -- expect martin, clive to be found as supervisors,
2479 --
2480 if l_supervisor_id is null and l_approvers_visited = 0 then
2481 g_trace := '200';
2482 if g_debug then
2483 hr_utility.trace('200');
2484 end if;
2485
2486 hr_utility.set_message(809, 'HXC_APPR_WF_NO_HR_SUP');
2487 hr_utility.raise_error;
2488 end if;
2489
2490 g_trace := 'supervisor is not null';
2491 if g_debug then
2492 hr_utility.trace('supervisor is not null');
2493 end if;
2494 -- set up timeout properties for first approver in hierarchy
2495
2496 IF l_ext_func2 IS NULL
2497 THEN
2498 g_trace := 'setting timeout';
2499 if g_debug then
2500 hr_utility.trace('setting timeout');
2501 end if;
2502 if l_approvers_visited = 0 then
2503
2504 -- if supervisors exist after first approver then notification
2505 -- for first approver is allowed to timeout
2506
2507 if get_supervisor(l_supervisor_id, SYSDATE) is not null
2508 then
2509 g_trace :='allow timeout';
2510 if g_debug then
2511 hr_utility.trace('allow timeout');
2512 end if;
2513 l_default_timeout := wf_engine.GetItemAttrText(
2514 itemtype => p_itemtype,
2515 itemkey => p_itemkey,
2516 aname => 'DEFAULT_TIMEOUT');
2517
2518
2519 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2520 itemkey => p_itemkey,
2521 aname => 'APPROVAL_TIMEOUT',
2522 avalue => l_default_timeout);
2523
2524 g_trace := 'timeout=' || to_char(l_default_timeout);
2525 if g_debug then
2526 hr_utility.trace('timeout=' || to_char(l_default_timeout));
2527 end if;
2528 -- if no more supervisors exist after first approver then
2529 -- notification sent to next approver NOT allowed to timeout
2530 else
2531 g_trace :='NOT allow timeout';
2532 if g_debug then
2533 hr_utility.trace('NOT allow timeout');
2534 end if;
2535 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2536 itemkey => p_itemkey,
2537 aname => 'APPROVAL_TIMEOUT',
2538 avalue => 0);
2539 end if;
2540 end if;
2541 END IF;
2542
2543
2544
2545
2546 g_trace := 'find supervisor self service login';
2547 if g_debug then
2548 hr_utility.trace('find supervisor self service login');
2549 end if;
2550 -- find supervisor self service login
2551 l_login := get_login(p_person_id =>l_supervisor_id);
2552
2553 if l_login is null then
2554 g_trace := 'RAISE: no self service login for supervisor';
2555 if g_debug then
2556 hr_utility.trace('RAISE: no self service login for supervisor');
2557 end if;
2558 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2559 hr_utility.set_message_token('PROCEDURE', l_proc);
2560 hr_utility.set_message_token('STEP', '30');
2561 hr_utility.raise_error;
2562 end if;
2563
2564 g_trace := 'supervisor ss login=' || l_login;
2565 if g_debug then
2566 hr_utility.trace('supervisor ss login=' || l_login);
2567 end if;
2568
2569 -- supervisor found and notification to be sent,
2570 -- keep track of approvers visted to date
2571
2572 g_trace := 'increase approvers visited';
2573 if g_debug then
2574 hr_utility.trace('increase approvers visited');
2575 end if;
2576 inc_approvers_visited(p_itemtype, p_itemkey);
2577
2578 wf_engine.SetItemAttrText(
2579 itemtype => p_itemtype,
2580 itemkey => p_itemkey,
2581 aname => 'APR_SS_LOGIN',
2582 avalue => l_login
2583 );
2584
2585 g_trace := 'setting apr_name';
2586 if g_debug then
2587 hr_utility.trace('setting apr_name');
2588 end if;
2589 -- set information for notification
2590 wf_engine.SetItemAttrText(
2591 itemtype => p_itemtype,
2592 itemkey => p_itemkey,
2593 aname => 'APR_NAME',
2594 avalue => get_name(l_supervisor_id,sysdate)
2595 );
2596
2597 g_trace := 'setting from_role';
2598 if g_debug then
2599 hr_utility.trace('setting from_role');
2600 end if;
2601 wf_engine.SetItemAttrText(
2602 itemtype => p_itemtype,
2603 itemkey => p_itemkey,
2604 aname => 'TC_APPROVER_FROM_ROLE',
2605 avalue => l_login
2606 );
2607
2608 g_trace := '300';
2609 if g_debug then
2610 hr_utility.trace('Notification Sent to : '||to_char(l_supervisor_id));
2611 end if;
2612 --
2613 -- set supervisor's person id, ready for next iteration
2614
2615 wf_engine.SetItemAttrNumber(
2616 itemtype => p_itemtype,
2617 itemkey => p_itemkey,
2618 aname => 'APR_PERSON_ID',
2619 avalue => l_supervisor_id);
2620
2621 -- Bug 4202019. These variables are set just before they are being used in the TITLE of Worklist notification.
2622
2623 l_effective_end_date := wf_engine.GetItemAttrDate(
2624 itemtype => p_itemtype,
2625 itemkey => p_itemkey,
2626 aname => 'APP_END_DATE');
2627
2628 l_effective_start_date := wf_engine.GetItemAttrDate(
2629 itemtype => p_itemtype,
2630 itemkey => p_itemkey,
2631 aname => 'APP_START_DATE');
2632
2633
2634 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
2635 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
2636 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
2637
2638 l_title := fnd_message.get();
2639
2640 wf_engine.SetItemAttrText(
2641 itemtype => p_itemtype,
2642 itemkey => p_itemkey,
2643 aname => 'TITLE',
2644 avalue => l_title);
2645
2646 l_otl_appr_id := l_appl_period_bb_id||'#'||p_itemkey;
2647
2648 wf_engine.SetItemAttrText(
2649 itemtype => p_itemtype,
2650 itemkey => p_itemkey,
2651 aname => 'OTL_APPR_ID',
2652 avalue => l_otl_appr_id);
2653
2654 l_description := get_description(l_appl_period_bb_id);
2655
2656 open c_find_app_per_id( l_appl_period_bb_id );
2657 fetch c_find_app_per_id into l_validate_flag;
2658 close c_find_app_per_id;
2659
2660 if l_validate_flag = 'Y' then
2661 fnd_message.set_name('HXC','HXC_APPR_WF_DESC_NO_PA_MANAGER');
2662 l_description := l_description || fnd_message.get();
2663 end if;
2664
2665 wf_engine.SetItemAttrText
2666 (itemtype => p_itemtype,
2667 itemkey => p_itemkey,
2668 aname => 'DESCRIPTION',
2669 avalue => l_description);
2670
2671 wf_engine.SetItemAttrNumber(
2672 itemtype => p_itemtype,
2673 itemkey => p_itemkey,
2674 aname => 'TOTAL_TC_HOURS',
2675 avalue => l_total_hours);
2676
2677
2678
2679 g_trace := '500';
2680 if g_debug then
2681 hr_utility.trace('500');
2682 end if;
2683 p_result := 'COMPLETE:Y';
2684
2685 end if;
2686 if p_funcmode = 'CANCEL' then
2687 p_result := 'COMPLETE:Y';
2688 end if;
2689
2690 if p_funcmode = 'TIMEOUT' then
2691 p_result := 'COMPLETE:Y';
2692 end if;
2693
2694
2695
2696 /*
2697 Bug 3449786
2698
2699 Added commit. At this point we're about to send a notification
2700 which could take several days for a response, therefore to
2701 save rollback segments, we issue a commit here, knowing that
2702 the workflow data is set appropriately.
2703
2704 */
2705
2706 commit;
2707
2708 exception
2709 when others then
2710 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
2711 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
2712
2713 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2714 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode,
2715 l_proc || '|' || g_trace);
2716 raise;
2717 end hr_supervisor_approval;
2718 --
2719 -- approval comment arrives after notification has been responded to,
2720 -- this work flow activity implies that the approver has 'approved'
2721 -- the notification, ie. not a timeout
2722 --
2723 procedure capture_apr_comment(
2724 p_itemtype in varchar2,
2725 p_itemkey in varchar2,
2726 p_actid in number,
2727 p_funcmode in varchar2,
2728 p_result in out nocopy varchar2)
2729 is
2730 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_apr_comment';
2731 l_approvers_visited number;
2732 begin
2733 --sb_msgs_pkg.begin_call(l_proc);
2734 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
2735
2736 if p_funcmode = 'RUN' then
2737 --
2738 -- set variables for approval hierarchy
2739 --
2740 /*jxtan should move this to other procedure
2741 l_approvers_visited := wf_engine.GetItemAttrNumber(
2742 itemtype => p_itemtype,
2743 itemkey => p_itemkey,
2744 aname => 'APPROVERS_VISITED');
2745
2746 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2747 itemkey => p_itemkey,
2748 aname => 'APPROVED_AT_LEVEL',
2749 avalue => l_approvers_visited);
2750
2751 */
2752 --
2753 -- set up attribute required for next activity
2754 --
2755 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2756 itemkey => p_itemkey,
2757 aname => 'APPROVAL_STATUS',
2758 avalue => 'APPROVED');
2759
2760 p_result := 'COMPLETE';
2761 end if;
2762
2763 if p_funcmode = 'CANCEL' then
2764 p_result := 'COMPLETE';
2765 end if;
2766
2767 if p_funcmode = 'TIMEOUT' then
2768 p_result := 'COMPLETE';
2769 end if;
2770
2771 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
2772 --sb_msgs_pkg.end_call(l_proc);
2773 exception
2774 when others then
2775 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
2776 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
2777
2778 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2779 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2780 raise;
2781 end capture_apr_comment;
2782
2783
2784
2785 --
2786 -- reject comment after notification has been responded to
2787 --
2788 procedure capture_reject_comment(
2789 p_itemtype in varchar2,
2790 p_itemkey in varchar2,
2791 p_actid in number,
2792 p_funcmode in varchar2,
2793 p_result in out nocopy varchar2)
2794 is
2795 l_proc constant varchar2(61) := g_pkg || '.' || 'capture_reject_comment';
2796 begin
2797 --sb_msgs_pkg.begin_call(l_proc);
2798 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
2799
2800 if p_funcmode = 'RUN' then
2801 --
2802 -- set up attribute required for next activity
2803 --
2804 wf_engine.SetItemAttrText(itemtype => p_itemtype,
2805 itemkey => p_itemkey,
2806 aname => 'APPROVAL_STATUS',
2807 avalue => 'REJECTED');
2808 --
2809
2810 p_result := 'COMPLETE';
2811 end if;
2812
2813 if p_funcmode = 'CANCEL' then
2814 p_result := 'COMPLETE';
2815 end if;
2816
2817 if p_funcmode = 'TIMEOUT' then
2818 p_result := 'COMPLETE';
2819 end if;
2820
2821 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
2822 --sb_msgs_pkg.end_call(l_proc);
2823 exception
2824 when others then
2825 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
2826 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
2827
2828 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
2829 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2830 raise;
2831 end capture_reject_comment;
2832
2833
2834
2835 --
2836 -- controls looping logic
2837 --
2838 procedure is_final_apr(
2839 p_itemtype in varchar2,
2840 p_itemkey in varchar2,
2841 p_actid in number,
2842 p_funcmode in varchar2,
2843 p_result in out nocopy varchar2)
2844 is
2845 l_proc constant varchar2(61) := g_pkg || '.' || 'is_final_apr';
2846 l_final_apr varchar2(50);
2847 l_effective_date date;
2848 l_approval_timeout number;
2849 l_default_timeout number;
2850 l_apr_person_id per_all_assignments_f.person_id%type;
2851 l_apr_key_level number;
2852 l_approved_at_level number;
2853 l_approvers_visited number;
2854 begin
2855 g_debug:=hr_utility.debug_enabled;
2856 --sb_msgs_pkg.begin_call(l_proc);
2857 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
2858 if g_debug then
2859 hr_utility.trace('is final approver');
2860 end if;
2861 if p_funcmode = 'RUN' then
2862
2863 l_final_apr := wf_engine.GetItemAttrText(
2864 itemtype => p_itemtype,
2865 itemkey => p_itemkey,
2866 aname => 'FINAL_APR');
2867
2868
2869 IF l_final_apr = 'YES' THEN
2870 if g_debug then
2871 hr_utility.trace('Final!');
2872 end if;
2873 p_result := 'COMPLETE:Y';
2874 return;
2875 ELSIF l_final_apr = 'NO' THEN
2876 if g_debug then
2877 hr_utility.trace('extension not final');
2878 end if;
2879 p_result := 'COMPLETE:N';
2880 return;
2881 END IF;
2882
2883 l_apr_person_id := wf_engine.GetItemAttrNumber(
2884 itemtype => p_itemtype,
2885 itemkey => p_itemkey,
2886 aname => 'APR_PERSON_ID');
2887
2888 IF get_supervisor(l_apr_person_id, SYSDATE) IS NULL
2889 THEN
2890 p_result := 'COMPLETE:Y';
2891 return;
2892 END IF;
2893
2894 if g_debug then
2895 hr_utility.trace('NOT final');
2896 end if;
2897
2898 l_effective_date := wf_engine.GetItemAttrDate(
2899 itemtype => p_itemtype,
2900 itemkey => p_itemkey,
2901 aname => 'APP_END_DATE');
2902
2903 l_approval_timeout := wf_engine.GetItemAttrText(
2904 itemtype => p_itemtype,
2905 itemkey => p_itemkey,
2906 aname => 'APPROVAL_TIMEOUT');
2907
2908 l_default_timeout := wf_engine.GetItemAttrText(
2909 itemtype => p_itemtype,
2910 itemkey => p_itemkey,
2911 aname => 'DEFAULT_TIMEOUT');
2912
2913
2914 --sb_msgs_pkg.trace('current apr per id>' || l_apr_person_id || '<');
2915
2916 l_apr_key_level := wf_engine.GetItemAttrText(
2917 itemtype => p_itemtype,
2918 itemkey => p_itemkey,
2919 aname => 'APR_KEY_LEVEL');
2920
2921 l_approved_at_level := wf_engine.GetItemAttrText(
2922 itemtype => p_itemtype,
2923 itemkey => p_itemkey,
2924 aname => 'APPROVED_AT_LEVEL');
2925
2926 l_approvers_visited := wf_engine.GetItemAttrText(
2927 itemtype => p_itemtype,
2928 itemkey => p_itemkey,
2929 aname => 'APPROVERS_VISITED');
2930
2931 --
2932 -- next iteration takes us below key approver,
2933 -- next approver allowed to timeout
2934 --
2935 if l_approvers_visited + 1 < l_apr_key_level then
2936 --sb_msgs_pkg.trace('10 - if');
2937
2938 if g_debug then
2939 hr_utility.trace('not final 1');
2940 end if;
2941
2942 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2943 itemkey => p_itemkey,
2944 aname => 'APPROVAL_TIMEOUT',
2945 avalue => l_default_timeout);
2946 p_result := 'COMPLETE:N';
2947 --
2948 -- next iteration takes us to key approver,
2949 -- determine timeout properties for next approver
2950 --
2951 elsif l_approvers_visited + 1 = l_apr_key_level then
2952 --sb_msgs_pkg.trace('20 - else if');
2953
2954 --
2955 -- if supervisor exists for next approver,
2956 -- next approver allowed to timeout
2957 --
2958 if get_supervisor(l_apr_person_id,
2959 SYSDATE) is not null then
2960 --sb_msgs_pkg.trace('22 - if');
2961
2962 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2963 itemkey => p_itemkey,
2964 aname => 'APPROVAL_TIMEOUT',
2965 avalue => l_default_timeout);
2966 --
2967 -- if supervisor does not exist for next approver,
2968 -- next approver NOT allowed to timeout
2969 else
2970 --sb_msgs_pkg.trace('24 - else');
2971
2972 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
2973 itemkey => p_itemkey,
2974 aname => 'APPROVAL_TIMEOUT',
2975 avalue => 0);
2976 end if;
2977
2978 if g_debug then
2979 hr_utility.trace('not final 2');
2980 end if;
2981
2982 p_result := 'COMPLETE:N';
2983 --
2984 -- next iteration takes us above key approver,
2985 -- if key approver has approved then no need for further notification,
2986 -- if key approver has not approved (ie. timeout) then iterate,
2987 -- next approver not allowed to timeout (notification must wait here)
2988 --
2989 elsif l_approvers_visited + 1 > l_apr_key_level then
2990 --sb_msgs_pkg.trace('30 - else if');
2991
2992 if l_approved_at_level >= l_apr_key_level then
2993 --sb_msgs_pkg.trace('32 - if');
2994 if g_debug then
2995 hr_utility.trace('Yes final 1');
2996 end if;
2997 p_result := 'COMPLETE:Y';
2998 else
2999 --sb_msgs_pkg.trace('34 - else');
3000
3001 if g_debug then
3002 hr_utility.trace('not final 3');
3003 end if;
3004
3005 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3006 itemkey => p_itemkey,
3007 aname => 'APPROVAL_TIMEOUT',
3008 avalue => 0);
3009 p_result := 'COMPLETE:N';
3010 end if;
3011 end if;
3012
3013 --
3014 -- timeout to be used by next notification
3015 --
3016 l_approval_timeout := wf_engine.GetItemAttrText(
3017 itemtype => p_itemtype,
3018 itemkey => p_itemkey,
3019 aname => 'APPROVAL_TIMEOUT');
3020 --sb_msgs_pkg.trace('l_apr_timeout(A)>' || l_approval_timeout || '<');
3021 end if;
3022
3023 if p_funcmode = 'CANCEL' then
3024 if g_debug then
3025 hr_utility.trace('cancelled');
3026 end if;
3027
3028 p_result := 'COMPLETE';
3029 end if;
3030
3031 if p_funcmode = 'TIMEOUT' then
3032
3033 if g_debug then
3034 hr_utility.trace('completed');
3035 end if;
3036
3037 p_result := 'COMPLETE';
3038 end if;
3039
3040 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3041 --sb_msgs_pkg.end_call(l_proc);
3042 exception
3043 when others then
3044 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3045 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3046
3047 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3048 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3049 raise;
3050 end is_final_apr;
3051
3052
3053
3054 --
3055 -- formula decides approval style to use
3056 --
3057 procedure formula_selects_mechanism(
3058 p_itemtype in varchar2,
3059 p_itemkey in varchar2,
3060 p_actid in number,
3061 p_funcmode in varchar2,
3062 p_result in out nocopy varchar2)
3063 is
3064 l_proc varchar2(61);
3065
3066 l_inputs ff_exec.inputs_t;
3067 l_outputs ff_exec.outputs_t;
3068
3069 l_effective_date date;
3070 l_formula_id hxc_approval_comps.approval_mechanism_id%type;
3071
3072 -- formula return values
3073 l_approval_mechanism hxc_approval_comps.approval_mechanism%type;
3074 l_approval_mechanism_id hxc_approval_comps.approval_mechanism_id%type;
3075 l_wf_item_type hxc_approval_comps.wf_item_type%type;
3076 l_wf_process_name hxc_approval_comps.wf_name%type;
3077 l_person_id per_all_assignments_f.person_id%type;
3078 l_formula_status varchar2(10);
3079 l_formula_message varchar2(2000);
3080 begin
3081 --sb_msgs_pkg.begin_call(l_proc);
3082 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
3083
3084 if g_debug then
3085 l_proc := g_pkg || '.' || 'formula_selects_mechanism';
3086 hr_utility.set_location(l_proc, 10);
3087 end if;
3088
3089 l_person_id := wf_engine.GetItemAttrNumber(
3090 itemtype => p_itemtype,
3091 itemkey => p_itemkey,
3092 aname => 'RESOURCE_ID');
3093 if p_funcmode = 'RUN' then
3094 l_effective_date := wf_engine.GetItemAttrDate(
3095 itemtype => p_itemtype,
3096 itemkey => p_itemkey,
3097 aname => 'APP_END_DATE');
3098
3099 l_formula_id := wf_engine.GetItemAttrNumber(
3100 itemtype => p_itemtype,
3101 itemkey => p_itemkey,
3102 aname => 'FORMULA_ID');
3103
3104 if g_debug then
3105 hr_utility.set_location(l_proc, 20);
3106 end if;
3107
3108 --
3109 -- initialise formula
3110 --
3111 --sb_msgs_pkg.trace('STEP 10 - before init_formula()');
3112 -- gaz - remove
3113 ff_utils.set_debug(127);
3114 ff_exec.init_formula(l_formula_id, l_effective_date, l_inputs,l_outputs);
3115
3116 --
3117 -- set up the inputs and contexts to formula
3118 -- nb. no contexts are used
3119 --
3120
3121 if g_debug then
3122 hr_utility.set_location(l_proc, 30);
3123 end if;
3124
3125 hr_utility.trace('Input count is:'||l_inputs.count);
3126
3127 for i in l_inputs.first..l_inputs.last loop
3128
3129 hr_utility.trace('Input name:'||l_inputs(i).name);
3130
3131 if l_inputs(i).name = 'TIMECARD_BB_ID' then
3132
3133 if g_debug then
3134 hr_utility.set_location(l_proc, 40);
3135 end if;
3136 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3137 itemtype => p_itemtype,
3138 itemkey => p_itemkey,
3139 aname => 'TC_BLD_BLK_ID');
3140
3141 elsif l_inputs(i).name = 'TIMECARD_BB_OVN' then
3142 if g_debug then
3143 hr_utility.set_location(l_proc, 50);
3144 end if;
3145 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3146 itemtype => p_itemtype,
3147 itemkey => p_itemkey,
3148 aname => 'TC_BLD_BLK_OVN');
3149
3150 elsif l_inputs(i).name = 'APPLICATION_PERIOD_BB_ID' then
3151 if g_debug then
3152 hr_utility.set_location(l_proc, 60);
3153 end if;
3154 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3155 itemtype => p_itemtype,
3156 itemkey => p_itemkey,
3157 aname => 'APP_BB_ID');
3158
3159 elsif l_inputs(i).name = 'APPLICATION_PERIOD_BB_OVN' then
3160 if g_debug then
3161 hr_utility.set_location(l_proc, 70);
3162 end if;
3163 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3164 itemtype => p_itemtype,
3165 itemkey => p_itemkey,
3166 aname => 'APP_BB_OVN');
3167
3168 elsif l_inputs(i).name = 'TIME_RECIPIENT_ID' then
3169 if g_debug then
3170 hr_utility.set_location(l_proc, 80);
3171 end if;
3172 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3173 itemtype => p_itemtype,
3174 itemkey => p_itemkey,
3175 aname => 'TIME_RECIPIENT_ID');
3176 l_inputs(i).value := 5;
3177
3178 elsif l_inputs(i).name = 'RESOURCE_ID' then
3179 if g_debug then
3180 hr_utility.set_location(l_proc, 90);
3181 end if;
3182 l_inputs(i).value := wf_engine.GetItemAttrNumber(
3183 itemtype => p_itemtype,
3184 itemkey => p_itemkey,
3185 aname => 'RESOURCE_ID');
3186
3187 elsif l_inputs(i).name = 'RESOURCE_TYPE' then
3188 if g_debug then
3189 hr_utility.set_location(l_proc, 100);
3190 end if;
3191 l_inputs(i).value := 'abc';
3192
3193 else
3194 --
3195 -- context not recognised
3196 --
3197 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3198 hr_utility.set_message_token('PROCEDURE', l_proc);
3199 hr_utility.set_message_token('STEP', '20');
3200 hr_utility.raise_error;
3201 end if;
3202 end loop;
3203
3204 if g_debug then
3205 hr_utility.set_location(l_proc, 110);
3206 end if;
3207 --sb_msgs_pkg.trace('STEP 30 - before run_formula()');
3208 ff_exec.run_formula(l_inputs, l_outputs);
3209
3210 if g_debug then
3211 hr_utility.set_location(l_proc, 120);
3212 end if;
3213
3214 --
3215 -- obtain return values,
3216 -- there should be at least three outputs
3217 --
3218 assert(l_outputs.count >= 3, l_proc || ':STEP 40');
3219
3220 for i in l_outputs.first..l_outputs.last loop
3221
3222 if g_debug then
3223 hr_utility.set_location(l_proc, 130);
3224 end if;
3225
3226 if l_outputs(i).name = 'APPROVAL_MECHANISM' then
3227 l_approval_mechanism := l_outputs(i).value;
3228
3229 elsif l_outputs(i).name = 'APPROVAL_MECHANISM_ID' then
3230 l_approval_mechanism_id := l_outputs(i).value;
3231
3232 elsif l_outputs(i).name = 'WF_ITEM_TYPE' then
3233 l_wf_item_type := l_outputs(i).value;
3234
3235 elsif l_outputs(i).name = 'WF_PROCESS_NAME' then
3236 l_wf_process_name := l_outputs(i).value;
3237
3238 elsif l_outputs(i).name = 'FORMULA_STATUS' then
3239 l_formula_status := l_outputs(i).value;
3240
3241 elsif l_outputs(i).name = 'FORMULA_MESSAGE' then
3242 l_formula_message := l_outputs(i).value;
3243
3244 else
3245 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3246 hr_utility.set_message_token('PROCEDURE', l_proc);
3247 hr_utility.set_message_token('STEP', '50');
3248 hr_utility.raise_error;
3249 end if;
3250 end loop;
3251
3252 --
3253 -- check whether formual has raised an error and act appropriately
3254 --
3255 if g_debug then
3256 hr_utility.set_location(l_proc, 140);
3257 end if;
3258
3259 if l_formula_status = 'E' then
3260 --
3261 -- formula has failed, raise an error
3262 --
3263 if g_debug then
3264 hr_utility.set_location(l_proc, 150);
3265 end if;
3266
3267 if l_formula_message is null then
3268 --
3269 -- user not defined an error message, raise OTC default message
3270 -- stub - need our own error message
3271 --
3272 fnd_message.set_name('PAY', 'HR_6648_ELE_ENTRY_FORMULA_ERR');
3273 hr_utility.raise_error;
3274 else
3275 --
3276 -- user has defined message, raise it
3277 --
3278 fnd_message.set_name('PAY', 'HR_ELE_ENTRY_FORMULA_HINT');
3279 fnd_message.set_token('FORMULA_TEXT', l_formula_message, false);
3280 hr_utility.raise_error;
3281 end if;
3282
3283 elsif l_formula_status = 'W' then
3284
3285 if g_debug then
3286 hr_utility.set_location(l_proc, 160);
3287 end if;
3288 --
3289 -- formula has failed, but only warning necessary
3290 --
3291 if l_formula_message is null then
3292 --
3293 -- user has not defined an error message
3294 --
3295 fnd_message.set_name('PAY', 'HR_6648_ELE_ENTRY_FORMULA_ERR');
3296 hr_utility.set_warning;
3297 else
3298 --
3299 -- user has defined message, raise it
3300 --
3301 fnd_message.set_name('PAY', 'HR_ELE_ENTRY_FORMULA_HINT');
3302 fnd_message.set_token('FORMULA_TEXT', l_formula_message, false);
3303 hr_utility.set_warning;
3304 end if;
3305 end if;
3306
3307 --
3308 -- set up context for approval mechanism
3309 --
3310 -- auto approval no further context is required
3311 --
3312 if g_debug then
3313 hr_utility.set_location(l_proc, 170);
3314 end if;
3315
3316 if l_approval_mechanism = 'AUTO_APPROVE' then
3317 --sb_msgs_pkg.trace('formula selects auto approve approval');
3318 null;
3319
3320 --
3321 -- person approval, set approving person id context
3322 --
3323 elsif l_approval_mechanism = 'PERSON' then
3324 --sb_msgs_pkg.trace('formula selects person approval');
3325 if g_debug then
3326 hr_utility.set_location(l_proc, 180);
3327 end if;
3328
3329 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3330 itemkey => p_itemkey,
3331 aname => 'APR_PERSON_ID',
3332 avalue => l_approval_mechanism_id);
3333
3334 if g_debug then
3335 hr_utility.set_location(l_proc, 190);
3336 end if;
3337
3338 --
3339 -- HR supervisor approval, approving person id can derived from
3340 -- resource id on application period,
3341 -- Find Approval Style activity has already been called, this should
3342 -- ensure that global context information has been set
3343 --
3344 elsif l_approval_mechanism = 'HR_SUPERVISOR' then
3345 --sb_msgs_pkg.trace('formula selects hr supervisor approval');
3346
3347 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3348 itemkey => p_itemkey,
3349 aname => 'APR_PERSON_ID',
3350 avalue => l_person_id);
3351
3352 --
3353 -- workflow approval, set workflow item type and workflow process name
3354 --
3355 elsif l_approval_mechanism = 'WORKFLOW' then
3356 --sb_msgs_pkg.trace('formula selects workflow approval');
3357
3358 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3359 itemkey => p_itemkey,
3360 aname => 'WF_ITEM_TYPE',
3361 avalue => l_wf_item_type);
3362
3363 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3364 itemkey => p_itemkey,
3365 aname => 'WF_PROCESS_NAME',
3366 avalue => l_wf_process_name);
3367
3368 elsif l_approval_mechanism = 'PROJECT_MANAGER'
3369 then
3370 NULL;
3371 else
3372 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3373 hr_utility.set_message_token('PROCEDURE', l_proc);
3374 hr_utility.set_message_token('STEP', '60');
3375 hr_utility.raise_error;
3376 end if;
3377
3378 --
3379 -- set result code for transition to next activity
3380 --
3381 p_result := l_approval_mechanism;
3382 end if;
3383
3384 if p_funcmode = 'CANCEL' then
3385 p_result := 'COMPLETE';
3386 end if;
3387
3388 if p_funcmode = 'TIMEOUT' then
3389 p_result := 'COMPLETE';
3390 end if;
3391
3392 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3393 --sb_msgs_pkg.end_call(l_proc);
3394 exception
3395 when others then
3396 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3397 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3398
3399 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3400 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3401 raise;
3402 end formula_selects_mechanism;
3403
3404
3405
3406 --
3407 -- launch user defined workflow process
3408 --
3409 procedure launch_wf_process(
3410 p_itemtype in varchar2,
3411 p_itemkey in varchar2,
3412 p_actid in number,
3413 p_funcmode in varchar2,
3414 p_result in out nocopy varchar2)
3415 is
3416 l_proc constant varchar2(61) := g_pkg || '.' || 'launch_wf_process';
3417 l_item_key number;
3418 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
3419 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
3420 l_wf_item_type hxc_approval_comps.wf_item_type%type;
3421 l_wf_process_name hxc_approval_comps.wf_name%type;
3422 l_apr_person_id per_all_assignments_f.person_id%type;
3423 l_resource_id per_all_assignments_f.person_id%type;
3424 begin
3425 --sb_msgs_pkg.begin_call(l_proc);
3426 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
3427
3428 l_ap_bbid := wf_engine.GetItemAttrNumber(
3429 itemtype => p_itemtype,
3430 itemkey => p_itemkey,
3431 aname => 'APP_BB_ID');
3432
3433 l_ap_bbovn := wf_engine.GetItemAttrNumber(
3434 itemtype => p_itemtype,
3435 itemkey => p_itemkey,
3436 aname => 'APP_BB_OVN');
3437
3438 l_wf_item_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
3439 itemkey => p_itemkey,
3440 aname => 'WF_ITEM_TYPE');
3441
3442 l_wf_process_name := wf_engine.GetItemAttrText(
3443 itemtype => p_itemtype,
3444 itemkey => p_itemkey,
3445 aname => 'WF_PROCESS_NAME');
3446
3447 --
3448 -- get key for item instance
3449 --
3450 SELECT hxc_approval_wf_s.nextval
3451 INTO l_item_key
3452 FROM DUAL;
3453
3454 --sb_msgs_pkg.trace('l_item_key>' || l_item_key || '<');
3455
3456 wf_engine.createProcess(l_wf_item_type, l_item_key, l_wf_process_name);
3457
3458
3459 --
3460 -- set attributes required by child workflow,
3461 -- set parent item type and key in child workflow process
3462 --
3463 wf_engine.SetItemAttrText(itemtype => l_wf_item_type,
3464 itemkey => l_item_key,
3465 aname => 'PARENT_ITEM_TYPE',
3466 avalue => p_itemtype);
3467
3468 wf_engine.SetItemAttrText(itemtype => l_wf_item_type,
3469 itemkey => l_item_key,
3470 aname => 'PARENT_ITEM_KEY',
3471 avalue => p_itemkey);
3472 --
3473 wf_engine.SetItemParent
3474 (itemtype => l_wf_item_type,
3475 itemkey => l_item_key,
3476 parent_itemtype => p_itemtype,
3477 parent_itemkey => p_itemkey,
3478 parent_context => NULL);
3479 --
3480 -- launch customer's workflow process
3481 --
3482 wf_engine.startProcess(l_wf_item_type, l_item_key);
3483
3484 l_apr_person_id := wf_engine.GetItemAttrNumber(
3485 itemtype => p_itemtype,
3486 itemkey => p_itemkey,
3487 aname => 'APR_PERSON_ID');
3488 l_resource_id := wf_engine.GetItemAttrNumber(
3489 itemtype => p_itemtype,
3490 itemkey => p_itemkey,
3491 aname => 'RESOURCE_ID');
3492 --OIT Enhancement
3493 --FYI Notification to SUPERVISOR on timecard SUBMISSION if he is not direct approver
3494 if(hxc_approval_wf_helper.is_approver_supervisor(l_apr_person_id,l_resource_id)) then
3495 hxc_approval_wf_helper.set_notif_attribute_values
3496 (p_itemtype,
3497 p_itemkey,
3498 null,
3499 null
3500 );
3501 else
3502 hxc_approval_wf_helper.set_notif_attribute_values
3503 (p_itemtype,
3504 p_itemkey,
3505 hxc_app_comp_notifications_api.c_action_request_approval,
3506 hxc_app_comp_notifications_api.c_recipient_supervisor
3507 );
3508 end if;
3509
3510 --
3511 -- next activity waits for child processes to complete
3512 --
3513 p_result := 'COMPLETE';
3514
3515 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3516 --sb_msgs_pkg.end_call(l_proc);
3517 exception
3518 when others then
3519 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3520 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3521
3522 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3523 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3524 raise;
3525 end launch_wf_process;
3526
3527
3528
3529 --
3530 -- test out workflow result
3531 --
3532 procedure test_wf_result(
3533 p_itemtype in varchar2,
3534 p_itemkey in varchar2,
3535 p_actid in number,
3536 p_funcmode in varchar2,
3537 p_result in out nocopy varchar2)
3538 is
3539 l_proc constant varchar2(61) := g_pkg || '.' || 'test_wf_result';
3540 l_datetime constant date := SYSDATE;
3541 l_ap_bbid hxc_time_building_blocks.time_building_block_id%type;
3542 l_ap_bbovn hxc_time_building_blocks.time_building_block_id%type;
3543 l_apr_rej_reason varchar2(2000);
3544 l_wf_apr_result varchar2(80);
3545 begin
3546 --sb_msgs_pkg.begin_call(l_proc);
3547 --sb_msgs_pkg.trace('p_funcmode>' || p_funcmode || '<');
3548
3549 if p_funcmode = 'RUN' then
3550 l_wf_apr_result := wf_engine.GetItemAttrText(
3551 itemtype => p_itemtype,
3552 itemkey => p_itemkey,
3553 aname => 'WF_APPROVAL_RESULT');
3554 --sb_msgs_pkg.trace('WF_APPROVAL_RESULT>' || l_wf_apr_result || '<');
3555
3556 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3557 itemkey => p_itemkey,
3558 aname => 'APPROVAL_STATUS',
3559 avalue => l_wf_apr_result);
3560
3561
3562 if l_wf_apr_result = 'APPROVED' then
3563 --OIT Enhancement
3564 --FYI Notification to PREPARER on timecard APPROVAL
3565 hxc_approval_wf_helper.set_notif_attribute_values
3566 (p_itemtype,
3567 p_itemkey,
3568 hxc_app_comp_notifications_api.c_action_approved,
3569 hxc_app_comp_notifications_api.c_recipient_preparer
3570 );
3571 p_result := 'COMPLETE:APPROVED';
3572
3573 elsif l_wf_apr_result = 'REJECTED' then
3574 --OIT Enhancement
3575 --FYI Notification to PREPARER on timecard REJECTION
3576 hxc_approval_wf_helper.set_notif_attribute_values
3577 (p_itemtype,
3578 p_itemkey,
3579 hxc_app_comp_notifications_api.c_action_rejected,
3580 hxc_app_comp_notifications_api.c_recipient_preparer
3581 );
3582 p_result := 'COMPLETE:REJECTED';
3583
3584 else
3585 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3586 hr_utility.set_message_token('PROCEDURE', l_proc);
3587 hr_utility.set_message_token('STEP', '10');
3588 hr_utility.raise_error;
3589 end if;
3590 end if;
3591
3592 if p_funcmode = 'CANCEL' then
3593 p_result := 'COMPLETE';
3594 end if;
3595
3596 if p_funcmode = 'TIMEOUT' then
3597 p_result := 'COMPLETE';
3598 end if;
3599
3600 --sb_msgs_pkg.trace('p_result>' || p_result || '<');
3601 --sb_msgs_pkg.end_call(l_proc);
3602 exception
3603 when others then
3604 --sb_msgs_pkg.trace('sqlcode>' || sqlcode || '<');
3605 --sb_msgs_pkg.trace('sqlerrm>' || sqlerrm || '<');
3606
3607 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3608 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3609 raise;
3610 end test_wf_result;
3611
3612
3613 PROCEDURE set_next_app_period(
3614 p_itemtype in varchar2,
3615 p_itemkey in varchar2,
3616 p_actid in number,
3617 p_funcmode in varchar2,
3618 p_result in out nocopy varchar2
3619 )
3620 IS
3621 l_next_period_id hxc_time_building_blocks.time_building_block_id%TYPE;
3622 l_next_period_ovn hxc_time_building_blocks.object_version_number%TYPE;
3623
3624
3625 l_proc VARCHAR2(150);
3626
3627 BEGIN
3628 g_debug:=hr_utility.debug_enabled;
3629 if g_debug then
3630 l_proc := 'set_next_app_period';
3631 hr_utility.trace('in set next period');
3632 end if;
3633 IF p_funcmode = 'RUN'
3634 THEN
3635 l_next_period_id := wf_engine.GetItemAttrNumber(
3636 itemtype => p_itemtype,
3637 itemkey => p_itemkey,
3638 aname => 'NEXT_APP_BB_ID');
3639
3640 if g_debug then
3641 hr_utility.set_location(l_proc, 60);
3642 end if;
3643
3644 l_next_period_ovn := wf_engine.GetItemAttrNumber(
3645 itemtype => p_itemtype,
3646 itemkey => p_itemkey,
3647 aname => 'NEXT_APP_BB_OVN');
3648
3649 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3650 itemkey => p_itemkey,
3651 aname => 'APP_BB_ID',
3652 avalue => l_next_period_id);
3653
3654 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3655 itemkey => p_itemkey,
3656 aname => 'APP_BB_OVN',
3657 avalue => l_next_period_ovn);
3658
3659 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3660 itemkey => p_itemkey,
3661 aname => 'APR_REJ_REASON',
3662 avalue => '');
3663
3664 wf_engine.SetItemAttrText(itemtype => p_itemtype,
3665 itemkey => p_itemkey,
3666 aname => 'APPROVAL_STATUS',
3667 avalue => '');
3668
3669
3670 if g_debug then
3671 hr_utility.trace('next app id=' || l_next_period_id);
3672 hr_utility.trace('next app ovn=' || l_next_period_ovn);
3673 end if;
3674 p_result := 'COMPLETE';
3675 END IF;
3676
3677 IF p_funcmode = 'CANCEL'
3678 THEN
3679 p_result := 'COMPLETE';
3680 END IF;
3681
3682 IF p_funcmode = 'TIMEOUT'
3683 THEN
3684 p_result := 'COMPLETE';
3685 END IF;
3686
3687 EXCEPTION
3688 WHEN OTHERS THEN
3689
3690
3691 wf_core.context(g_pkg, substr(l_proc, instr(l_proc, '.') + 1),
3692 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3693 raise;
3694 END set_next_app_period;
3695
3696 end hxc_find_notify_aprs_pkg;