[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMEKEEPER_WF_PKG
Source
1 PACKAGE BODY hxc_timekeeper_wf_pkg AS
2 /* $Header: hxctimekeeperwf.pkb 120.4 2007/01/10 21:14:38 arundell noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 -------------------------------------------------------------------
7 -- Function to get the next Item Key for the Process
8 -------------------------------------------------------------------
9 FUNCTION GET_ITEM_KEY RETURN NUMBER IS
10 l_item_key number;
11 BEGIN
12
13 select hxc_approval_item_key_s.nextval
14 Into l_item_key
15 from dual;
16
17 RETURN l_item_key;
18 END GET_ITEM_KEY;
19
20 -------------------------------------------------------------------
21 -- Function to get the supervisor
22 -------------------------------------------------------------------
23 FUNCTION GET_SUPERVISOR(
24 p_person_id in number,
25 p_effective_date in date)
26 return number
27 is
28
29 cursor csr_get_supervisor(b_person_id number, b_effective_date date) is
30 select asg.supervisor_id
31 from per_all_assignments_f asg
32 where asg.person_id = b_person_id
33 and TRUNC(b_effective_date) between TRUNC(asg.effective_Start_date) and TRUNC(asg.effective_end_date)
34 and asg.primary_flag = 'Y'
35 and asg.assignment_type in ('E','C');
36
37 l_supervisor_id per_all_assignments_f.person_id%type;
38 begin
39 open csr_get_supervisor(p_person_id, trunc(p_effective_date));
40 fetch csr_get_supervisor into l_supervisor_id;
41 close csr_get_supervisor;
42
43 return l_supervisor_id;
44 end get_supervisor;
45
46 ----------------------------------------------------------------------------
47 -- Timekeeper Audit Workflow Notification
48 ----------------------------------------------------------------------------
49 Function begin_audit_process
50 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
51 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
52 ,p_resource_id in hxc_time_building_blocks.resource_id%type
53 ,p_timekeeper_id in hxc_time_building_blocks.resource_id%type
54 ,p_tk_audit_enabled in VARCHAR2
55 ,p_tk_notify_to in VARCHAR2
56 ,p_tk_notify_type in VARCHAR2
57 ,p_property_table hxc_timecard_prop_table_type
58 ) return VARCHAR2 IS
59
60 l_item_key NUMBER := NULL;
61 l_cla_terg_id NUMBER :=Null;
62
63 BEGIN
64
65 l_cla_terg_id := to_number(hxc_timecard_properties.find_property_value
66 (p_property_table
67 ,'TsPerAuditRequirementsAuditRequirements'
68 ,null
69 ,null
70 ,sysdate
71 ,sysdate
72 ));
73
74 if(l_cla_terg_id is not null and
75 p_tk_notify_to <>'NONE' and
76 p_tk_audit_enabled ='Y' ) then
77
78 l_item_key := HXC_TIMEKEEPER_WF_PKG.GET_ITEM_KEY;
79
80 HXC_TIMEKEEPER_WF_PKG.START_TK_WF_PROCESS
81 ( p_item_type => 'HXCTKWF'
82 ,p_item_key => l_item_key
83 ,p_process_name => 'HXC_TK_AUDIT_PROCESS'
84 ,p_tc_bb_id => p_timecard_id
85 ,p_tc_ovn => p_timecard_ovn
86 ,p_tc_resource_id => p_resource_id
87 ,p_timekeeper_id => p_timekeeper_id
88 ,p_tk_nofity_type => p_tk_notify_type
89 ,p_tk_nofity_to => p_tk_notify_to
90 );
91
92 end if;
93
94 return to_char(l_item_key);
95
96 END begin_audit_process;
97
98 -------------------------------------------------------------------
99 -- Function to handle timeout case
100 -------------------------------------------------------------------
101 procedure capture_timeout_status(
102 p_itemtype in varchar2,
103 p_itemkey in varchar2,
104 p_actid in number,
105 p_funcmode in varchar2,
106 p_result in out nocopy varchar2) IS
107
108 cursor c_transaction_id
109 (p_tbb_id in number,
110 p_tbb_ovn in number) is
111 select transaction_id
112 from hxc_transaction_details
113 where time_building_block_id = p_tbb_id
114 and object_version_number = p_tbb_ovn;
115
116 l_tx_id NUMBER := NULL;
117 l_tc_resource_id number;
118 l_period_start_date date;
119 l_period_end_date date;
120
121 l_timeouts NUMBER ;
122
123 l_tbb_id hxc_time_building_blocks.time_building_block_id%type;
124 l_tbb_ovn hxc_time_building_blocks.time_building_block_id%type;
125 l_error_id NUMBER;
126 l_ovn NUMBER;
127 l_approver_type VARCHAR2(30);
128 l_message_name VARCHAR2(250);
129
130 begin
131
132 IF p_funcmode = 'TIMEOUT' then
133
134 l_timeouts := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
135 itemkey => p_itemkey,
136 aname => 'TIMEOUT_COUNT');
137
138 if l_timeouts > 0 then
139
140 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
141 itemkey => p_itemkey,
142 aname => 'DEFAULT_TIMEOUT',
143 avalue => 0);
144
145
146 l_approver_type := wf_engine.GetItemAttrText(
147 itemtype => p_itemtype,
148 itemkey => p_itemkey,
149 aname => 'APR_NOTIFY_TO');
150
151 l_tbb_id := wf_engine.GetItemAttrNumber(
152 itemtype => p_itemtype,
153 itemkey => p_itemkey,
154 aname => 'TC_BLD_BLK_ID');
155
156 l_tbb_ovn := wf_engine.GetItemAttrNumber(
157 itemtype => p_itemtype,
158 itemkey => p_itemkey,
159 aname => 'TC_BLD_BLK_OVN');
160
161
162 IF l_approver_type ='WORKER' THEN
163 l_message_name := 'HXC_WF_TK_WORKER_NO_RESPOND';
164 ELSIF l_approver_type ='SUPERVISOR' THEN
165 l_message_name := 'HXC_WF_TK_SUPER_NO_RESPOND';
166 END IF;
167
168 OPEN c_transaction_id(l_tbb_id,l_tbb_ovn);
169 FETCH c_transaction_id INTO l_tx_id;
170 CLOSE c_transaction_id;
171
172 IF (l_tx_id is null) THEN
173 l_tx_id := -1;
174 END IF;
175
176 hxc_err_ins.ins
177 (p_transaction_detail_id => l_tx_id
178 ,p_time_building_block_id => l_tbb_id
179 ,p_time_building_block_ovn => l_tbb_ovn
180 ,p_time_attribute_id => NULL
181 ,p_time_attribute_ovn => NULL
182 ,p_message_name => l_message_name
183 ,p_message_level => 'BUSINESS_MESSAGE'
184 ,p_message_field => NULL
185 ,p_message_tokens => NULL
186 ,p_application_short_name => 'HXC'
187 ,p_error_id => l_error_id
188 ,p_object_version_number => l_ovn
189 ,p_date_from => sysdate
190 ,p_date_to => hr_general.end_of_time
191 );
192
193 end if;
194
195 l_timeouts :=l_timeouts +1;
196
197 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
198 itemkey => p_itemkey,
199 aname => 'TIMEOUT_COUNT',
200 avalue => l_timeouts );
201
202 end if;
203 end capture_timeout_status;
204 -------------------------------------------------------------------
205 -- Function to get the login
206 -------------------------------------------------------------------
207 FUNCTION set_login(
208 p_person_id in number,
209 p_tc_bb_id in number,
210 p_tc_bb_ovn in number)
211 return varchar2
212 is
213
214 cursor c_updated_by
215 (p_tc_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
216 ,p_tc_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
217 ) is
218 select u.user_name
219 from fnd_user u, hxc_time_building_blocks tbb
220 where tbb.time_building_block_id = p_tc_id
221 and tbb.object_version_number = p_tc_ovn
222 and tbb.last_updated_by = u.user_id;
223
224 l_proc constant varchar2(61) :='hxc_timekeeper_wf_pkg.set_login';
225 l_login fnd_user.user_name%type;
226 begin
227
228 l_login := hxc_find_notify_aprs_pkg.get_login(p_person_id => p_person_id);
229
230 --
231 -- if null returned, timecard owner does not have a self
232 -- service login name, where does notification get sent?
233 --
234 if l_login is null then
235 open c_updated_by(p_tc_bb_id,p_tc_bb_ovn);
236 fetch c_updated_by into l_login;
237
238 if (c_updated_by%NOTFOUND) then
239 close c_updated_by;
240 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
241 hr_utility.set_message_token('PROCEDURE', l_proc);
242 hr_utility.set_message_token('STEP', '20');
243 hr_utility.raise_error;
244 end if;
245 close c_updated_by;
246 end if;
247
248 return l_login;
249 end set_login;
250
251 ------------------------------------------------------------------
252 -- Cancel Prvious Notifications
253 ------------------------------------------------------------------
254 PROCEDURE cancel_previous_notifications
255 ( p_tk_audit_item_type in varchar2
256 ,p_tk_audit_item_key in varchar2
257 ) IS
258
259 CURSOR c_audit_keys is
260 SELECT ITEM_KEY,ITEM_TYPE
261 from wf_items
262 where parent_item_key =p_tk_audit_item_key
263 and parent_item_type = p_tk_audit_item_type;
264
265
266 -- Changed the query in the cursor for the bug 4696149.
267 /*
268 CURSOR c_notification_id(p_item_key varchar2,p_item_type varchar2) is
269 select Notification_id
270 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
271 where WIAS.ITEM_TYPE = p_item_type
272 and WIAS.ITEM_KEY = p_item_key
273 and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
274 and WPA.ACTIVITY_NAME = 'FYI_NOTIFICATION'
275 and WPA.PROCESS_NAME = nvl('HXC_TK_AUDIT', WPA.PROCESS_NAME);
276 */
277
278 CURSOR c_notification_id(p_item_key varchar2,p_item_type varchar2) is
279 select wn.notification_id nid
280 from WF_NOTIFICATIONS WN, WF_ITEM_ACTIVITY_STATUSES WIAS
281 where WN.GROUP_ID = WIAS.NOTIFICATION_ID
282 and WIAS.ITEM_TYPE = p_item_type
283 and WIAS.ITEM_KEY = p_item_key;
284
285 l_item_key wf_items.item_key%type;
286 l_item_type wf_items.item_type%type;
287 l_ntfid Number;
288
289 BEGIN
290
291 FOR audit_keys in c_audit_keys LOOP
292
293 l_item_key := audit_keys.item_key;
294 l_item_type := audit_keys.item_type;
295
296 BEGIN
297 BEGIN
298 -- As of now abort process will cancel all notifications which are
299 -- in Notified state
300 -- This will start calcelling the notifications when the bug 3468491 get's fixed
301 -- We need not call cancel notifications after this bug is fixed.
302
303 wf_engine.abortprocess
304 (itemkey => audit_keys.item_key
305 ,itemtype => audit_keys.item_typE);
306
307 EXCEPTION
308 When others then
309
310 -- to cancel FYI notifications we use the workaround to get the notification id and
311 --cancel the notification
312
313 l_ntfid :=Null;
314 Open c_notification_id(l_item_key,l_item_type);
315 fetch c_notification_id into l_ntfid;
316 close c_notification_id;
317
318 if l_ntfid is not Null then
319 wf_notification.cancel(l_ntfid);
320 end if;
321 END;
322
323 EXCEPTION
324 When others then
325 Null;
326 END;
327 END LOOP;
328
329 END cancel_previous_notifications;
330
331 -------------------------------------------------------------------
332 -- Prodedure to set timecard Hours
333 -------------------------------------------------------------------
334 PROCEDURE set_timecard_hours(
335 p_itemtype in varchar2,
336 p_itemkey in varchar2,
337 p_tc_bb_id in number,
338 p_tc_bb_ovn in number) is
339
340 l_total_hours number;
341
342 BEGIN
343 wf_engine.SetItemAttrText
344 (itemtype => p_itemtype,
345 itemkey => p_itemkey,
346 aname => 'DESCRIPTION',
347 avalue => hxc_find_notify_aprs_pkg.get_description_tc(p_tc_bb_id,p_tc_bb_ovn)
348 );
349
350 l_total_hours := HXC_TIME_CATEGORY_UTILS_PKG.category_timecard_hrs (
351 p_tbb_id => p_tc_bb_id
352 , p_tbb_ovn => p_tc_bb_ovn
353 , P_TIME_CATEGORY_NAME => '' );
354
355 wf_engine.SetItemAttrNumber(
356 itemtype => p_itemtype,
357 itemkey => p_itemkey,
358 aname => 'TOTAL_TC_HOURS',
359 avalue => round(l_total_hours,3));
360
361
362 end set_timecard_hours;
363
364
365 -------------------------------------------------------------------
366 -- Function to get the name for the approver
367 -------------------------------------------------------------------
368 function get_name(
369 p_person_id in number,
370 p_effective_date in DATE)
371 return varchar2
372 is
373 cursor csr_name(b_person_id NUMBER, b_effective_date DATE) is
374 select p.full_name from per_people_f p
375 where p.person_id=b_person_id
376 and b_effective_date between p.effective_start_date and p.effective_end_date;
377
378 cursor csr_closest_name1
379 (b_person_id in number,b_effective_date date) is
380 select full_name
381 from per_all_people_f
382 where person_id = b_person_id
383 and (effective_end_date=(select max(effective_end_date) from per_all_people_f
384 where person_id=b_person_id and(effective_end_date<= b_effective_date)));
385
386
387 cursor csr_closest_name2(b_person_id in number,b_effective_date date) is
388 select full_name
389 from per_all_people_f
390 where person_id = b_person_id
391 and (effective_start_date=(select min(effective_start_date) from per_all_people_f
392 where person_id=b_person_id and
393 (effective_start_date>= b_effective_date)));
394
395
396 l_temp_name per_all_people_f.full_name%type;
397 l_name wf_users.name%type;
398 l_display_name wf_users.display_name%type;
399
400 begin
401
402 wf_directory.getusername( p_orig_system => 'PER',
403 p_orig_system_id => p_person_id,
404 p_name => l_name,
405 p_display_name => l_display_name);
406
407 if l_display_name is null then
408
409 open csr_name(p_person_id, trunc(p_effective_date));
410 fetch csr_name into l_temp_name;
411 if csr_name%notfound then
412 open csr_closest_name1(p_person_id,trunc(p_effective_date));
413 fetch csr_closest_name1 into l_temp_name;
414 if csr_closest_name1%notfound then
415 open csr_closest_name2(p_person_id,trunc(p_effective_date));
416 fetch csr_closest_name2 into l_temp_name;
417 close csr_closest_name2;
418 end if;
419 close csr_closest_name1;
420 end if;
421 l_display_name:=l_temp_name;
422 close csr_name;
423 end if;
424 return l_display_name;
425
426 end get_name;
427
428 -------------------------------------------------------------------
429 -- Function to set the attributes and start the child process based
430 -- on the nofitication to
431 -------------------------------------------------------------------
432 PROCEDURE start_child_process
433 (p_tc_item_type IN VARCHAR2
434 ,p_tc_item_key IN VARCHAR2
435 ,p_tc_process_name IN VARCHAR2
436 ,p_tc_bb_id IN NUMBER
437 ,p_tc_bb_ovn IN NUMBER
438 ,p_tc_start_time IN DATE
439 ,p_tc_stop_time IN DATE
440 ,p_tc_resource_id IN NUMBER
441 ,p_tc_timekeeper_id IN NUMBER
442 ,p_tc_tk_nofity_type IN VARCHAR2
443 ,p_tc_tk_nofity_to IN VARCHAR2
444 )is
445
446 l_child_item_key wf_items.item_key%type;
447
448 BEGIN
449 l_child_item_key := hxc_timekeeper_wf_pkg.get_item_key;
450
451 wf_engine.CreateProcess(itemtype => p_tc_item_type,
452 itemkey => l_child_item_key,
453 process => p_tc_process_name);
454
455 wf_engine.setitemowner(p_tc_item_type,
456 l_child_item_key,
457 HXC_FIND_NOTIFY_APRS_PKG.get_login(p_tc_timekeeper_id));
458
459
460 wf_engine.SetItemAttrNumber
461 (itemtype => p_tc_item_type
462 ,itemkey => l_child_item_key
463 ,aname => 'TC_BLD_BLK_ID'
464 ,avalue => p_tc_bb_id
465 );
466
467 wf_engine.SetItemAttrNumber
468 (itemtype => p_tc_item_type
469 ,itemkey => l_child_item_key
470 ,aname => 'TC_BLD_BLK_OVN'
471 ,avalue => p_tc_bb_ovn
472 );
473
474 wf_engine.SetItemAttrNumber
475 (itemtype => p_tc_item_type
476 ,itemkey => l_child_item_key
477 ,aname => 'RESOURCE_ID'
478 ,avalue => p_tc_resource_id
479 );
480
481 wf_engine.SetItemAttrDate
482 (itemtype => p_tc_item_type
483 ,itemkey => l_child_item_key
484 ,aname => 'TC_START'
485 ,avalue => p_tc_start_time
486 );
487
488 wf_engine.SetItemAttrText
489 (itemtype => p_tc_item_type
490 ,itemkey => l_child_item_key
491 ,aname => 'FORMATTED_TC_START'
492 ,avalue => to_char(p_tc_start_time,'YYYY/MM/DD')
493 );
494
495 wf_engine.SetItemAttrDate
496 (itemtype => p_tc_item_type
497 ,itemkey => l_child_item_key
498 ,aname => 'TC_STOP'
499 ,avalue => p_tc_stop_time
500 );
501
502 wf_engine.SetItemAttrText
503 (itemtype => p_tc_item_type
504 ,itemkey => l_child_item_key
505 ,aname => 'FORMATTED_TC_STOP'
506 ,avalue => to_char(p_tc_stop_time,'YYYY/MM/DD')
507 );
508
509 wf_engine.SetItemAttrNumber
510 (itemtype => p_tc_item_type
511 ,itemkey => l_child_item_key
512 ,aname => 'TIMEKEEPER_ID'
513 ,avalue => p_tc_timekeeper_id
514 );
515
516 wf_engine.SetItemAttrText
517 (itemtype => p_tc_item_type,
518 itemkey => l_child_item_key,
519 aname => 'APR_NOTIFY_TYPE',
520 avalue => p_tc_tk_nofity_type
521 );
522
523 wf_engine.SetItemAttrText
524 (itemtype => p_tc_item_type,
525 itemkey => l_child_item_key,
526 aname => 'APR_NOTIFY_TO',
527 avalue => p_tc_tk_nofity_to
528 );
529
530 wf_engine.SetItemParent
531 (itemtype => p_tc_item_type,
532 itemkey => l_child_item_key,
533 parent_itemtype => p_tc_item_type,
534 parent_itemkey => p_tc_item_key,
535 parent_context => NULL);
536
537 wf_engine.StartProcess(itemtype => p_tc_item_type,
538 itemkey => l_child_item_key);
539 END START_CHILD_PROCESS;
540
541 -------------------------------------------------------------------
542 -- Staring point from Tk form
543 -------------------------------------------------------------------
544 PROCEDURE start_tk_wf_process
545 (p_item_type IN varchar2
546 ,p_item_key IN varchar2
547 ,p_process_name IN varchar2
548 ,p_tc_bb_id IN number
549 ,p_tc_ovn IN number
550 ,p_tc_resource_id IN NUMBER
551 ,p_timekeeper_id IN NUMBER
552 ,p_tk_nofity_type IN VARCHAR2
553 ,p_tk_nofity_to IN VARCHAR2
554 )is
555 BEGIN
556
557 wf_engine.createProcess
558 (itemtype => p_item_type
559 ,itemkey => p_item_key
560 ,process => p_process_name
561 );
562
563 wf_engine.SetItemAttrNumber
564 (itemtype => p_item_type
565 ,itemkey => p_item_key
566 ,aname => 'TC_BLD_BLK_ID'
567 ,avalue => p_tc_bb_id
568 );
569
570 wf_engine.SetItemAttrNumber
571 (itemtype => p_item_type
572 ,itemkey => p_item_key
573 ,aname => 'TC_BLD_BLK_OVN'
574 ,avalue => p_tc_ovn
575 );
576
577 wf_engine.SetItemAttrNumber
578 (itemtype => p_item_type
579 ,itemkey => p_item_key
580 ,aname => 'RESOURCE_ID'
581 ,avalue => p_tc_resource_id
582 );
583
584 wf_engine.SetItemAttrNumber
585 (itemtype => p_item_type
586 ,itemkey => p_item_key
587 ,aname => 'TIMEKEEPER_ID'
588 ,avalue => p_timekeeper_id
589 );
590
591 wf_engine.SetItemAttrText
592 (itemtype => p_item_type,
593 itemkey => p_item_key,
594 aname => 'TK_NOTIFY_TYPE',
595 avalue => p_tk_nofity_type
596 );
597
598 wf_engine.SetItemAttrText
599 (itemtype => p_item_type,
600 itemkey => p_item_key,
601 aname => 'TK_NOTIFY_TO',
602 avalue => p_tk_nofity_to
603 );
604
605 wf_engine.setitemowner
606 (itemtype => p_item_type
607 ,itemkey => p_item_key
608 ,owner => HXC_FIND_NOTIFY_APRS_PKG.get_login(p_tc_resource_id));
609
610 wf_engine.StartProcess
611 (itemtype => p_item_type
612 ,itemkey => p_item_key
613 );
614
615 wf_engine.threshold := 50;
616
617 END start_tk_wf_process;
618
619 -------------------------------------------------------------------
620 -- TO get the Notification Type from Tk preference and decide
621 -- to whom the notification should be sent to
622 -------------------------------------------------------------------
623 PROCEDURE START_TK_NOTIFICATION (
624 p_itemtype in varchar2,
625 p_itemkey in varchar2,
626 p_actid in number,
627 p_funcmode in varchar2,
628 p_result in out nocopy varchar2)
629 IS
630
631
632 CURSOR c_tc_info(
633 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
634 ,p_tc_ovn hxc_time_building_blocks.object_version_number%TYPE
635 )
636 IS
637 SELECT tcsum.resource_id,
638 tcsum.start_time,
639 tcsum.stop_time
640 FROM hxc_time_building_blocks tcsum
641 WHERE tcsum.time_building_block_id = p_tc_bbid
642 AND tcsum.object_version_number = p_tc_ovn ;
643
644
645 l_proc constant varchar2(61) :='hxc_timekeeper_wf_pkg.find_tk_ntf_style';
646 l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type;
647 l_tc_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
648 l_tc_start_time hxc_time_building_blocks.start_time%TYPE;
649 l_tc_stop_time hxc_time_building_blocks.stop_time%TYPE;
650 l_ntf_type VARCHAR2(150);
651 l_ntf_to VARCHAR2(150);
652 l_timkeeper_id hxc_time_building_blocks.resource_id%TYPE;
653 l_child_item_key wf_items.item_key%type;
654 l_process_name varchar2(150);
655 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
656
657 BEGIN
658
659 l_tc_bb_id := wf_engine.GetItemAttrNumber(
660 itemtype => p_itemtype,
661 itemkey => p_itemkey,
662 aname => 'TC_BLD_BLK_ID');
663
664 l_tc_bb_ovn := wf_engine.GetItemAttrNumber(
665 itemtype => p_itemtype,
666 itemkey => p_itemkey,
667 aname => 'TC_BLD_BLK_OVN');
668
669 l_timkeeper_id := wf_engine.GetItemAttrNumber(
670 itemtype => p_itemtype,
671 itemkey => p_itemkey,
672 aname => 'TIMEKEEPER_ID');
673
674 l_resource_id := wf_engine.GetItemAttrNumber(
675 itemtype => p_itemtype,
676 itemkey => p_itemkey,
677 aname => 'RESOURCE_ID');
678
679 l_ntf_to := wf_engine.GetItemAttrText(
680 itemtype => p_itemtype,
681 itemkey => p_itemkey ,
682 aname => 'TK_NOTIFY_TO');
683
684 l_ntf_type := wf_engine.GetItemAttrText(
685 itemtype => p_itemtype,
686 itemkey => p_itemkey ,
687 aname => 'TK_NOTIFY_TYPE');
688
689 open c_tc_info(l_tc_bb_id,l_tc_bb_ovn);
690 fetch c_tc_info
691 into l_resource_id, l_tc_start_time, l_tc_stop_time;
692
693 if c_tc_info%notfound then
694 close c_tc_info;
695 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
696 hr_utility.set_message_token('PROCEDURE', l_proc);
697 hr_utility.set_message_token('STEP', '12');
698 hr_utility.raise_error;
699 end if;
700 close c_tc_info;
701
702 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
703 itemkey => p_itemkey,
704 aname => 'TC_START',
705 avalue => l_tc_start_time);
706
707 wf_engine.SetItemAttrDate(itemtype => p_itemtype,
708 itemkey => p_itemkey,
709 aname => 'TC_STOP',
710 avalue => l_tc_stop_time);
711
712 IF l_ntf_to ='NONE' THEN
713 p_result := 'COMPLETE';
714 ELSIF l_ntf_to ='WORKER' OR l_ntf_to ='SUPERVISOR' THEN
715
716 start_child_process
717 (p_tc_item_type => p_itemtype
718 ,p_tc_item_key => p_itemkey
719 ,p_tc_process_name => 'HXC_TK_AUDIT'
720 ,p_tc_bb_id => l_tc_bb_id
721 ,p_tc_bb_ovn => l_tc_bb_ovn
722 ,p_tc_start_time => l_tc_start_time
723 ,p_tc_stop_time => l_tc_stop_time
724 ,p_tc_resource_id => l_resource_id
725 ,p_tc_timekeeper_id => l_timkeeper_id
726 ,p_tc_tk_nofity_type => l_ntf_type
727 ,p_tc_tk_nofity_to => l_ntf_to
728 );
729 ELSIF l_ntf_to ='WORKER_SUPERVISOR' THEN
730
731 start_child_process
732 (p_tc_item_type => p_itemtype
733 ,p_tc_item_key => p_itemkey
734 ,p_tc_process_name => 'HXC_TK_AUDIT'
735 ,p_tc_bb_id => l_tc_bb_id
736 ,p_tc_bb_ovn => l_tc_bb_ovn
737 ,p_tc_start_time => l_tc_start_time
738 ,p_tc_stop_time => l_tc_stop_time
739 ,p_tc_resource_id => l_resource_id
740 ,p_tc_timekeeper_id => l_timkeeper_id
741 ,p_tc_tk_nofity_type => l_ntf_type
742 ,p_tc_tk_nofity_to => 'WORKER'
743 );
744 start_child_process
745 (p_tc_item_type => p_itemtype
746 ,p_tc_item_key => p_itemkey
747 ,p_tc_process_name => 'HXC_TK_AUDIT'
748 ,p_tc_bb_id => l_tc_bb_id
749 ,p_tc_bb_ovn => l_tc_bb_ovn
750 ,p_tc_start_time => l_tc_start_time
751 ,p_tc_stop_time => l_tc_stop_time
752 ,p_tc_resource_id => l_resource_id
753 ,p_tc_timekeeper_id => l_timkeeper_id
754 ,p_tc_tk_nofity_type => l_ntf_type
755 ,p_tc_tk_nofity_to => 'SUPERVISOR'
756 );
757 END IF;
758
759 p_result := '';
760
761 END START_TK_NOTIFICATION;
762
763 -------------------------------------------------------------------
764 -- Decide the Notification To
765 -------------------------------------------------------------------
766
767 PROCEDURE FIND_NTF_TO(
768 p_itemtype in varchar2,
769 p_itemkey in varchar2,
770 p_actid in number,
771 p_funcmode in varchar2,
772 p_result in out nocopy varchar2)
773 IS
774 l_apr_ntf_to VARCHAR2(150);
775
776 BEGIN
777
778 l_apr_ntf_to := wf_engine.GetItemAttrText(
779 itemtype => p_itemtype,
780 itemkey => p_itemkey ,
781 aname => 'APR_NOTIFY_TO');
782
783 IF l_apr_ntf_to='WORKER' then
784 p_result := 'COMPLETE:WORKER';
785 ELSIF l_apr_ntf_to='SUPERVISOR' then
786 p_result := 'COMPLETE:SUPERVISOR';
787 END IF;
788
789 END find_ntf_to;
790
791 -------------------------------------------------------------------
792 -- Person Notification
793 -------------------------------------------------------------------
794 PROCEDURE PERSON_NOTIFY(
795 p_itemtype in varchar2,
796 p_itemkey in varchar2,
797 p_actid in number,
798 p_funcmode in varchar2,
799 p_result in out nocopy varchar2)
800 IS
801
802 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
803 l_login fnd_user.user_name%type;
804 l_fyi_login fnd_user.user_name%type;
805 l_start_date DATE;
806 l_stop_date DATE;
807 l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type;
808 l_tc_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
809 l_ntf_type VARCHAR2(150);
810 l_proc varchar2(61);
811 l_title VARCHAR2(2000);
812 l_otl_appr_id VARCHAR2(2000);
813 l_timekeeper_id hxc_time_building_blocks.resource_id%TYPE;
814 l_tc_url varchar2(1000);
815 BEGIN
816
817 g_debug := hr_utility.debug_enabled;
818
819 l_tc_bb_id := wf_engine.GetItemAttrNumber(
820 itemtype => p_itemtype,
821 itemkey => p_itemkey,
822 aname => 'TC_BLD_BLK_ID');
823
824 if g_debug then
825 l_proc := 'hxc_timekeeper_wf_pkg.PERSON_NOTIFY';
826 hr_utility.trace('l_tc_bb_id='||l_tc_bb_id);
827 end if;
828
829 l_tc_bb_ovn := wf_engine.GetItemAttrNumber(
830 itemtype => p_itemtype,
831 itemkey => p_itemkey,
832 aname => 'TC_BLD_BLK_OVN');
833
834 if g_debug then
835 hr_utility.trace('l_tc_bb_ovn='||l_tc_bb_ovn);
836 end if;
837
838 l_resource_id := wf_engine.GetItemAttrNumber(
839 itemtype => p_itemtype,
840 itemkey => p_itemkey,
841 aname => 'RESOURCE_ID');
842
843 if g_debug then
844 hr_utility.trace('l_resource_id='||l_resource_id);
845 end if;
846
847 l_start_date := wf_engine.GetItemAttrDate(
848 itemtype => p_itemtype,
849 itemkey => p_itemkey ,
850 aname => 'TC_START');
851
852 if g_debug then
853 hr_utility.trace('l_start_date='||to_char(l_start_date));
854 end if;
855
856 l_stop_date := wf_engine.GetItemAttrDate(
857 itemtype => p_itemtype,
858 itemkey => p_itemkey ,
859 aname => 'TC_STOP');
860
861 if g_debug then
862 hr_utility.trace('l_stop_date='||to_char(l_stop_date));
863 end if;
864
865 l_ntf_type := wf_engine.GetItemAttrText(
866 itemtype => p_itemtype,
867 itemkey => p_itemkey ,
868 aname => 'APR_NOTIFY_TYPE');
869
870 if g_debug then
871 hr_utility.trace('l_ntf_type='||l_ntf_type);
872 end if;
873
874 l_timekeeper_id := wf_engine.GetItemAttrNumber(
875 itemtype => p_itemtype,
876 itemkey => p_itemkey,
877 aname => 'TIMEKEEPER_ID');
878
879 if g_debug then
880 hr_utility.trace('l_timekeeper_id='||l_timekeeper_id);
881 end if;
882
883 wf_engine.SetItemAttrNumber(
884 itemtype => p_itemtype,
885 itemkey => p_itemkey,
886 aname => 'APR_PERSON_ID',
887 avalue => l_resource_id);
888
889 if g_debug then
890 hr_utility.trace('before login');
891 end if;
892
893 l_login := set_login(p_person_id => l_timekeeper_id
894 ,p_tc_bb_id => l_tc_bb_id
895 ,p_tc_bb_ovn => l_tc_bb_ovn );
896
897 if g_debug then
898 hr_utility.trace('l_login'||l_login);
899 end if;
900
901 --set role attribute
902 wf_engine.SetItemAttrText(
903 itemtype => p_itemtype,
904 itemkey => p_itemkey,
905 aname => 'TC_FROM_ROLE',
906 avalue => l_login);
907
908 l_fyi_login := set_login(p_person_id => l_resource_id
909 ,p_tc_bb_id => l_tc_bb_id
910 ,p_tc_bb_ovn => l_tc_bb_ovn );
911
912 if g_debug then
913 hr_utility.trace('l_fyi_login='||l_fyi_login);
914 end if;
915
916 wf_engine.SetItemAttrText(
917 itemtype => p_itemtype,
918 itemkey => p_itemkey,
919 aname => 'TC_OWNER',
920 avalue => get_name(l_resource_id,l_start_date)
921 );
922
923 wf_engine.SetItemAttrText(
924 itemtype => p_itemtype,
925 itemkey => p_itemkey,
926 aname => 'TC_TIMEKEEPER',
927 avalue => get_name(l_timekeeper_id,l_start_date)
928 );
929
930 wf_engine.SetItemAttrText(itemtype => p_itemtype,
931 itemkey => p_itemkey,
932 aname => 'TC_OWNER_SS_LOGIN',
933 avalue => l_fyi_login);
934
935 wf_engine.SetItemAttrText(
936 itemtype => p_itemtype,
937 itemkey => p_itemkey,
938 aname => 'APR_SS_LOGIN',
939 avalue => l_fyi_login);
940
941 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
942 fnd_message.set_token('START_DATE',to_char(l_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
943 fnd_message.set_token('END_DATE',to_char(l_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
944
945 l_title := fnd_message.get();
946
947 if g_debug then
948 hr_utility.trace('l_title='||substr(l_title,1,20));
949 end if;
950
951 wf_engine.SetItemAttrText(
952 itemtype => p_itemtype,
953 itemkey => p_itemkey,
954 aname => 'TITLE',
955 avalue => l_title);
956
957 l_otl_appr_id := l_tc_bb_id||'#'||p_itemkey;
958
959 if g_debug then
960 hr_utility.trace('l_otl_appr_id='||l_otl_appr_id);
961 end if;
962
963 wf_engine.SetItemAttrText(
964 itemtype => p_itemtype,
965 itemkey => p_itemkey,
966 aname => 'OTL_APPR_ID',
967 avalue => l_otl_appr_id);
968 set_timecard_hours(
969 p_itemtype => p_itemtype,
970 p_itemkey => p_itemkey,
971 p_tc_bb_id => l_tc_bb_id ,
972 p_tc_bb_ovn => l_tc_bb_ovn );
973
974 p_result :='COMPLETE:'||upper(l_ntf_type);
975
976 END PERSON_NOTIFY;
977
978 -------------------------------------------------------------------
979 -- Supervisor Notification
980 -------------------------------------------------------------------
981
982 PROCEDURE SUPERVISOR_NOTIFY(
983 p_itemtype in varchar2,
984 p_itemkey in varchar2,
985 p_actid in number,
986 p_funcmode in varchar2,
987 p_result in out nocopy varchar2)
988 IS
989
990 cursor c_updated_by
991 (p_tc_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
992 ,p_tc_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
993 ) is
994 select u.user_name
995 from fnd_user u, hxc_time_building_blocks tbb
996 where tbb.time_building_block_id = p_tc_id
997 and tbb.object_version_number = p_tc_ovn
998 and tbb.last_updated_by = u.user_id;
999
1000 l_resource_id hxc_time_building_blocks.resource_id%TYPE;
1001 l_login fnd_user.user_name%type;
1002 l_start_date DATE;
1003 l_stop_date DATE;
1004 l_supervisor hxc_time_building_blocks.resource_id%TYPE;
1005 l_tc_bb_id hxc_time_building_blocks.time_building_block_id%type;
1006 l_tc_bb_ovn hxc_time_building_blocks.time_building_block_id%type;
1007 l_ntf_type VARCHAR2(150);
1008 l_proc constant varchar2(61) :='hxc_timekeeper_wf_pkg.SUPERVISOR_NOTIFY';
1009 l_title VARCHAR2(2000);
1010 l_otl_appr_id VARCHAR2(2000);
1011 l_timekeeper_id hxc_time_building_blocks.resource_id%TYPE;
1012 l_total_hours number;
1013 l_premium_hours number;
1014 l_non_worked_hours number;
1015 l_description varchar2(100);
1016
1017
1018 BEGIN
1019
1020 l_tc_bb_id := wf_engine.GetItemAttrNumber(
1021 itemtype => p_itemtype,
1022 itemkey => p_itemkey,
1023 aname => 'TC_BLD_BLK_ID');
1024
1025 l_tc_bb_ovn := wf_engine.GetItemAttrNumber(
1026 itemtype => p_itemtype,
1027 itemkey => p_itemkey,
1028 aname => 'TC_BLD_BLK_OVN');
1029
1030 l_resource_id := wf_engine.GetItemAttrNumber(
1031 itemtype => p_itemtype,
1032 itemkey => p_itemkey,
1033 aname => 'RESOURCE_ID');
1034
1035 l_start_date := wf_engine.GetItemAttrDate(
1036 itemtype => p_itemtype,
1037 itemkey => p_itemkey ,
1038 aname => 'TC_START');
1039 l_stop_date := wf_engine.GetItemAttrDate(
1040 itemtype => p_itemtype,
1041 itemkey => p_itemkey ,
1042 aname => 'TC_STOP');
1043
1044 l_timekeeper_id := wf_engine.GetItemAttrNumber(
1045 itemtype => p_itemtype,
1046 itemkey => p_itemkey,
1047 aname => 'TIMEKEEPER_ID');
1048
1049 l_ntf_type := wf_engine.GetItemAttrText(
1050 itemtype => p_itemtype,
1051 itemkey => p_itemkey ,
1052 aname => 'APR_NOTIFY_TYPE');
1053
1054 l_login := set_login(p_person_id => l_timekeeper_id
1055 ,p_tc_bb_id => l_tc_bb_id
1056 ,p_tc_bb_ovn => l_tc_bb_ovn);
1057
1058 wf_engine.SetItemAttrText(
1059 itemtype => p_itemtype,
1060 itemkey => p_itemkey,
1061 aname => 'TC_TIMEKEEPER',
1062 avalue => get_name(l_timekeeper_id,l_start_date)
1063 );
1064
1065 --set role attribute
1066 wf_engine.SetItemAttrText(
1067 itemtype => p_itemtype,
1068 itemkey => p_itemkey,
1069 aname => 'TC_FROM_ROLE',
1070 avalue => l_login);
1071
1072 l_login := set_login(p_person_id => l_resource_id
1073 ,p_tc_bb_id => l_tc_bb_id
1074 ,p_tc_bb_ovn => l_tc_bb_ovn);
1075
1076 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1077 itemkey => p_itemkey,
1078 aname => 'TC_OWNER_SS_LOGIN',
1079 avalue => l_login);
1080
1081 wf_engine.SetItemAttrText(
1082 itemtype => p_itemtype,
1083 itemkey => p_itemkey,
1084 aname => 'TC_OWNER',
1085 avalue => get_name(l_resource_id,l_start_date)
1086 );
1087
1088 l_supervisor := get_supervisor(l_resource_id,l_start_date);
1089
1090
1091 wf_engine.SetItemAttrNumber(
1092 itemtype => p_itemtype,
1093 itemkey => p_itemkey,
1094 aname => 'APR_PERSON_ID',
1095 avalue => l_supervisor);
1096
1097 l_login := set_login(p_person_id => l_supervisor
1098 ,p_tc_bb_id => l_tc_bb_id
1099 ,p_tc_bb_ovn => l_tc_bb_ovn);
1100
1101 wf_engine.SetItemAttrText(
1102 itemtype => p_itemtype,
1103 itemkey => p_itemkey,
1104 aname => 'APR_SS_LOGIN',
1105 avalue => l_login);
1106 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
1107 fnd_message.set_token('START_DATE',to_char(l_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1108 fnd_message.set_token('END_DATE',to_char(l_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1109
1110 l_title := fnd_message.get();
1111
1112 wf_engine.SetItemAttrText(
1113 itemtype => p_itemtype,
1114 itemkey => p_itemkey,
1115 aname => 'TITLE',
1116 avalue => l_title);
1117
1118 l_otl_appr_id := l_tc_bb_id||'#'||p_itemkey;
1119
1120 wf_engine.SetItemAttrText(
1121 itemtype => p_itemtype,
1122 itemkey => p_itemkey,
1123 aname => 'OTL_APPR_ID',
1124 avalue => l_otl_appr_id);
1125 set_timecard_hours(
1126 p_itemtype => p_itemtype,
1127 p_itemkey => p_itemkey,
1128 p_tc_bb_id => l_tc_bb_id ,
1129 p_tc_bb_ovn => l_tc_bb_ovn );
1130
1131
1132 p_result :='COMPLETE:'||UPPER(l_ntf_type);
1133
1134 END SUPERVISOR_NOTIFY;
1135
1136 -------------------------------------------------------------------
1137 -- Set the Approval Status
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) IS
1145
1146 l_resource_id NUMBER;
1147 l_name VARCHAR2(250);
1148 l_display_name VARCHAR2(250);
1149
1150
1151 BEGIN
1152
1153 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1154 itemkey => p_itemkey,
1155 aname => 'APPROVAL_STATUS',
1156 avalue => 'APPROVED');
1157
1158 p_result := 'COMPLETE';
1159 END capture_approved_status;
1160
1161 -------------------------------------------------------------------
1162 -- Set the Rejected Status
1163 -------------------------------------------------------------------
1164
1165 PROCEDURE capture_rejected_status(
1166 p_itemtype in varchar2,
1167 p_itemkey in varchar2,
1168 p_actid in number,
1169 p_funcmode in varchar2,
1170 p_result in out nocopy varchar2) IS
1171
1172 l_resource_id NUMBER;
1173 l_name VARCHAR2(250);
1174 l_display_name VARCHAR2(250);
1175
1176 BEGIN
1177
1178 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1179 itemkey => p_itemkey,
1180 aname => 'APPROVAL_STATUS',
1181 avalue => 'REJECTED');
1182
1183 p_result := 'COMPLETE';
1184 END capture_rejected_status;
1185
1186 -------------------------------------------------------------------
1187 -- Attach proper messages based on responses to TK
1188 -------------------------------------------------------------------
1189 PROCEDURE update_tk_ntf_result(
1190 p_itemtype in varchar2,
1191 p_itemkey in varchar2,
1192 p_actid in number,
1193 p_funcmode in varchar2,
1194 p_result in out nocopy varchar2) IS
1195
1196 cursor c_transaction_id
1197 (p_tbb_id in number,
1198 p_tbb_ovn in number) is
1199 select transaction_id
1200 from hxc_transaction_details
1201 where time_building_block_id = p_tbb_id
1202 and object_version_number = p_tbb_ovn;
1203
1204 l_tx_id NUMBER := NULL;
1205 l_tc_resource_id number;
1206 l_period_start_date date;
1207 l_period_end_date date;
1208 l_approval_status hxc_time_building_blocks.approval_status%type;
1209 l_approver_comment hxc_time_building_blocks.comment_text%TYPE;
1210 l_tbb_id hxc_time_building_blocks.time_building_block_id%type;
1211 l_tbb_ovn hxc_time_building_blocks.time_building_block_id%type;
1212 l_approver_type VARCHAR2(250);
1213 l_message_name VARCHAR2(250);
1214 l_error_id NUMBER;
1215 l_ovn NUMBER;
1216
1217 cursor c_error_id
1218 (p_tbb_id in number,
1219 p_tbb_ovn in number) is
1220 select *
1221 from hxc_errors
1222 where time_building_block_id = p_tbb_id
1223 and time_building_block_ovn = p_tbb_ovn
1224 and message_name in ('HXC_WF_TK_WORKER_NO_RESPOND','HXC_WF_TK_SUPER_NO_RESPOND')
1225 and (date_to=hr_general.end_of_time OR date_to is NULL);
1226
1227 BEGIN
1228
1229 g_debug := hr_utility.debug_enabled;
1230
1231 l_tc_resource_id := wf_engine.GetItemAttrNumber(
1232 itemtype => p_itemtype,
1233 itemkey => p_itemkey ,
1234 aname => 'RESOURCE_ID');
1235
1236 l_period_start_date := wf_engine.GetItemAttrDate(
1237 itemtype => p_itemtype,
1238 itemkey => p_itemkey ,
1239 aname => 'TC_START');
1240
1241 l_period_end_date := wf_engine.GetItemAttrDate(
1242 itemtype => p_itemtype,
1243 itemkey => p_itemkey ,
1244 aname => 'TC_STOP');
1245
1246 l_tbb_id := wf_engine.GetItemAttrNumber(
1247 itemtype => p_itemtype,
1248 itemkey => p_itemkey,
1249 aname => 'TC_BLD_BLK_ID');
1250
1251 l_tbb_ovn := wf_engine.GetItemAttrNumber(
1252 itemtype => p_itemtype,
1253 itemkey => p_itemkey,
1254 aname => 'TC_BLD_BLK_OVN');
1255
1256 l_approval_status := wf_engine.GetItemAttrText(
1257 itemtype => p_itemtype,
1258 itemkey => p_itemkey ,
1259 aname => 'APPROVAL_STATUS');
1260
1261 l_approver_comment := wf_engine.GetItemAttrText(
1262 itemtype => p_itemtype,
1263 itemkey => p_itemkey,
1264 aname => 'APR_REJ_REASON');
1265
1266 l_approver_type := wf_engine.GetItemAttrText(
1267 itemtype => p_itemtype,
1268 itemkey => p_itemkey,
1269 aname => 'APR_NOTIFY_TO');
1270
1271
1272 OPEN c_transaction_id(l_tbb_id,l_tbb_ovn);
1273 FETCH c_transaction_id INTO l_tx_id;
1274 CLOSE c_transaction_id;
1275
1276 IF (l_tx_id is null) THEN
1277 l_tx_id := -1;
1278 END IF;
1279
1280 IF l_approver_type ='WORKER' AND upper(l_approval_status) = 'APPROVED' THEN
1281 l_message_name := 'HXC_WF_TK_WORKER_OK';
1282 ELSIF l_approver_type ='WORKER' AND upper(l_approval_status) = 'REJECTED' THEN
1283 l_message_name := 'HXC_WF_TK_WORKER_NOT_OK';
1284 ELSIF l_approver_type ='SUPERVISOR' AND upper(l_approval_status) = 'APPROVED' THEN
1285 l_message_name := 'HXC_WF_TK_SUPER_OK';
1286 ELSIF l_approver_type ='SUPERVISOR' AND upper(l_approval_status) = 'REJECTED' THEN
1287 l_message_name := 'HXC_WF_TK_SUPER_NOT_OK';
1288 END IF;
1289
1290 -- can cel the error message associated
1291 For error_rec in c_error_id(l_tbb_id,l_tbb_ovn) LOOP
1292 hxc_err_upd.upd
1293 (p_error_id => error_rec.error_id
1294 ,p_object_version_number => error_rec.object_version_number
1295 ,p_date_from => error_rec.date_from
1296 ,p_date_to => sysdate-1
1297 );
1298 END LOOP;
1299
1300 hxc_err_ins.ins
1301 (p_transaction_detail_id => l_tx_id
1302 ,p_time_building_block_id => l_tbb_id
1303 ,p_time_building_block_ovn => l_tbb_ovn
1304 ,p_time_attribute_id => NULL
1305 ,p_time_attribute_ovn => NULL
1306 ,p_message_name => l_message_name
1307 ,p_message_level => 'BUSINESS_MESSAGE'
1308 ,p_message_field => NULL
1309 ,p_message_tokens => NULL
1310 ,p_application_short_name => 'HXC'
1311 ,p_error_id => l_error_id
1312 ,p_object_version_number => l_ovn
1313 ,p_date_from => sysdate
1314 ,p_date_to => hr_general.end_of_time
1315 );
1316
1317 IF upper(l_approval_status) = 'APPROVED' THEN
1318 p_result := 'COMPLETE:APPROVED';
1319 ELSIF upper(l_approval_status) = 'REJECTED' THEN
1320 p_result := 'COMPLETE:REJECTED';
1321 END IF;
1322
1323 return;
1324
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 --
1328 if g_debug then
1329 hr_utility.trace(sqlerrm);
1330 end if;
1331 IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1332 RETURN;
1333 END IF;
1334 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1335 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1336 raise;
1337 p_result := '';
1338 return;
1339 --
1340 --
1341 END UPDATE_TK_NTF_RESULT;
1342
1343 end hxc_timekeeper_wf_pkg;