[Home] [Help]
PACKAGE BODY: APPS.HXC_APPROVAL_WF_PKG
Source
1 PACKAGE BODY hxc_approval_wf_pkg AS
2 /* $Header: hxcapprwf.pkb 120.29.12010000.2 2008/08/12 06:03:08 bbayragi ship $ */
3 --
4 -- Possible Notification Status Constants.
5 --
6 c_not_notified constant varchar2(12) := 'NOT_NOTIFIED';
7 c_notified constant varchar2(8) := 'NOTIFIED';
8 c_finished constant varchar2(8) := 'FINISHED';
9 g_debug BOOLEAN :=hr_utility.debug_enabled;
10
11 g_trace VARCHAR2(2000);
12
13 TYPE approval_comp IS RECORD(
14 approval_comp_id hxc_approval_comps.approval_comp_id%TYPE
15 ,object_version_number hxc_approval_comps.object_version_number%TYPE
16 ,approval_mechanism hxc_approval_comps.approval_mechanism%TYPE
17 ,approval_mechanism_id hxc_approval_comps.approval_mechanism_id%TYPE
18 ,wf_item_type hxc_approval_comps.wf_item_type%TYPE
19 ,wf_name hxc_approval_comps.wf_name%TYPE
20 ,time_category_id hxc_approval_comps.time_category_id%TYPE
21 ,approval_order hxc_approval_comps.approval_order%TYPE
22 );
23
24
25 TYPE approval_attribute is RECORD(
26 time_recipient_id VARCHAR2(150)
27 ,item_key VARCHAR2(150)
28 --all the following fields are not needed. they are here just so custom code won't break
29 ,approver_id VARCHAR2(150)
30 ,notified_status VARCHAR2(150)
31 ,approved_time VARCHAR2(150)
32 ,approver_comment VARCHAR2(150)
33 ,approval_status VARCHAR2(150)
34 );
35
36 TYPE block_info IS RECORD(
37 block_id hxc_time_building_blocks.time_building_block_id%TYPE
38 ,block_ovn hxc_time_building_blocks.object_version_number%TYPE
39 ,added VARCHAR2(1)
40 );
41
42 TYPE block_table IS TABLE OF
43 block_info
44 INDEX BY BINARY_INTEGER;
45
46 ------ Project manager changes
47 g_tab_project_id hxc_proj_manager_approval_pkg.tab_project_id;
48
49 g_block_exist_for_ap varchar2(1);
50
51 FUNCTION get_creation_date(
52 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
53 ,p_app_ovn hxc_time_building_blocks.object_version_number%TYPE
54 )
55 RETURN DATE
56 IS
57 CURSOR c_creation_date(
58 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
59 ,p_app_ovn hxc_time_building_blocks.object_version_number%TYPE
60 )
61 IS
62 SELECT creation_date
63 FROM hxc_time_building_blocks
64 WHERE time_building_block_id = p_app_id
65 AND object_version_number = p_app_ovn;
66
67 l_creation_date hxc_time_building_blocks.creation_date%TYPE := NULL;
68 BEGIN
69 OPEN c_creation_date(
70 p_app_id => p_app_id
71 ,p_app_ovn => p_app_ovn
72 );
73
74 FETCH c_creation_date INTO l_creation_date;
75 CLOSE c_creation_date;
76
77 RETURN l_creation_date;
78
79 END get_creation_date;
80
81
82
83 --this procedure gets all the detail blocks associated with
84 --timecard p_timecard_id and also fall between p_start_time
85 --and p_stop_time of an application period
86
87 PROCEDURE get_detail_blocks(
88 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
89 ,p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
90 ,p_start_time IN hxc_time_building_blocks.start_time%TYPE
91 ,p_stop_time IN hxc_time_building_blocks.stop_time%TYPE
92 ,p_detail_blocks IN OUT NOCOPY block_table
93 ,p_new_detail_blocks IN OUT NOCOPY hxc_block_table_type
94 )
95 IS
96 CURSOR c_detail_blocks(
97 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
98 ,p_timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
99 ,p_start_time hxc_time_building_blocks.start_time%TYPE
100 ,p_stop_time hxc_time_building_blocks.stop_time%TYPE
101 )
102 IS
103 SELECT
104 details.TIME_BUILDING_BLOCK_ID,
105 details.TYPE,
106 details.MEASURE,
107 details.UNIT_OF_MEASURE,
108 DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.start_time),
109 FND_DATE.DATE_TO_CANONICAL(days.start_time) ) CANONICAL_START_TIME,
110 DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.stop_time),
111 FND_DATE.DATE_TO_CANONICAL(days.stop_time) ) CANONICAL_STOP_TIME,
112 details.PARENT_BUILDING_BLOCK_ID,
113 NULL PARENT_IS_NEW,
114 details.SCOPE,
115 details.OBJECT_VERSION_NUMBER,
116 details.APPROVAL_STATUS,
117 details.RESOURCE_ID,
118 details.RESOURCE_TYPE,
119 details.APPROVAL_STYLE_ID,
120 FND_DATE.DATE_TO_CANONICAL(details.date_from) CANONICAL_DATE_FROM,
121 FND_DATE.DATE_TO_CANONICAL(details.date_to) CANONICAL_DATE_TO,
122 details.COMMENT_TEXT,
123 details.PARENT_BUILDING_BLOCK_OVN,
124 'N' NEW,
125 'N' CHANGED,
126 'N' PROCESS,
127 details.APPLICATION_SET_ID,
128 details.TRANSLATION_DISPLAY_KEY
129 FROM hxc_time_building_blocks days
130 ,hxc_time_building_blocks details
131 WHERE days.parent_building_block_id = p_timecard_id
132 AND days.parent_building_block_ovn = p_timecard_ovn
133 AND days.scope = 'DAY'
134 AND TRUNC(days.start_time) BETWEEN TRUNC(p_start_time) AND TRUNC(p_stop_time)
135 AND days.date_to = hr_general.end_of_time
136 AND details.scope = 'DETAIL'
137 AND details.parent_building_block_id = days.time_building_block_id
138 AND details.parent_building_block_ovn = days.object_version_number
139 AND details.date_to = hr_general.end_of_time;
140
141 l_cursor_blocks c_detail_blocks%ROWTYPE;
142
143 l_detail_blocks block_table;
144 l_new_detail_blocks hxc_block_table_type := hxc_block_table_type ();
145 l_block_index PLS_INTEGER := 1;
146
147 BEGIN
148 OPEN c_detail_blocks(
149 p_timecard_id => p_timecard_id
150 ,p_timecard_ovn => p_timecard_ovn
151 ,p_start_time => p_start_time
152 ,p_stop_time => p_stop_time
153 );
154
155 LOOP
156 FETCH c_detail_blocks INTO l_cursor_blocks;
157 EXIT WHEN c_detail_blocks%NOTFOUND;
158
159 l_detail_blocks(l_block_index).block_id := l_cursor_blocks.time_building_block_id;
160 l_detail_blocks(l_block_index).block_ovn := l_cursor_blocks.object_version_number;
161 l_detail_blocks(l_block_index).added := 'N';
162
163 l_new_detail_blocks.extend();
164 l_new_detail_blocks(l_block_index) := HXC_BLOCK_TYPE(l_cursor_blocks.TIME_BUILDING_BLOCK_ID,
165 l_cursor_blocks.TYPE,
166 l_cursor_blocks.MEASURE,
167 l_cursor_blocks.UNIT_OF_MEASURE,
168 l_cursor_blocks.CANONICAL_START_TIME,
169 l_cursor_blocks.CANONICAL_STOP_TIME,
170 l_cursor_blocks.PARENT_BUILDING_BLOCK_ID,
171 l_cursor_blocks.PARENT_IS_NEW,
172 l_cursor_blocks.SCOPE,
173 l_cursor_blocks.OBJECT_VERSION_NUMBER,
174 l_cursor_blocks.APPROVAL_STATUS,
175 l_cursor_blocks.RESOURCE_ID,
176 l_cursor_blocks.RESOURCE_TYPE,
177 l_cursor_blocks.APPROVAL_STYLE_ID,
178 l_cursor_blocks.CANONICAL_DATE_FROM,
179 l_cursor_blocks.CANONICAL_DATE_TO,
180 l_cursor_blocks.COMMENT_TEXT,
181 l_cursor_blocks.PARENT_BUILDING_BLOCK_OVN,
182 l_cursor_blocks.NEW,
183 l_cursor_blocks.CHANGED,
184 l_cursor_blocks.PROCESS,
185 l_cursor_blocks.APPLICATION_SET_ID,
186 l_cursor_blocks.TRANSLATION_DISPLAY_KEY);
187
188 l_block_index := l_block_index + 1;
189 END LOOP;
190
191 CLOSE c_detail_blocks;
192
193 p_detail_blocks := l_detail_blocks;
194 p_new_detail_blocks := l_new_detail_blocks;
195
196
197 END get_detail_blocks;
198
199
200 --this function returns all the attributes associated with the detail blocks
201 PROCEDURE get_detail_attributes(
202 p_detail_blocks IN block_table,
203 p_detail_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info,
204 p_new_detail_attributes IN OUT NOCOPY hxc_attribute_table_type
205 )
206
207 IS
208 l_attribute_index PLS_INTEGER := 1;
209 l_new_attributes hxc_attribute_table_type := hxc_attribute_table_type ();
210 l_detail_attributes hxc_self_service_time_deposit.building_block_attribute_info;
211 l_block_index PLS_INTEGER;
212
213 CURSOR c_block_attributes(
214 p_detail_id hxc_time_building_blocks.time_building_block_id%TYPE
215 ,p_detail_ovn hxc_time_building_blocks.object_version_number%TYPE
216 )
217 IS
218 select a.time_attribute_id
219 ,au.time_building_block_id building_block_id
220 ,bbit.bld_blk_info_type
221 ,a.attribute_category
222 ,a.attribute1
223 ,a.attribute2
224 ,a.attribute3
225 ,a.attribute4
226 ,a.attribute5
227 ,a.attribute6
228 ,a.attribute7
229 ,a.attribute8
230 ,a.attribute9
231 ,a.attribute10
232 ,a.attribute11
233 ,a.attribute12
234 ,a.attribute13
235 ,a.attribute14
236 ,a.attribute15
237 ,a.attribute16
238 ,a.attribute17
239 ,a.attribute18
240 ,a.attribute19
241 ,a.attribute20
242 ,a.attribute21
243 ,a.attribute22
244 ,a.attribute23
245 ,a.attribute24
246 ,a.attribute25
247 ,a.attribute26
248 ,a.attribute27
249 ,a.attribute28
250 ,a.attribute29
251 ,a.attribute30
252 ,a.bld_blk_info_type_id
253 ,a.object_version_number
254 ,'N' NEW
255 ,'N' CHANGED
256 ,'N' PROCESS
257 from hxc_time_attributes a,
258 hxc_time_attribute_usages au,
259 hxc_bld_blk_info_types bbit
260 where au.time_building_block_id = p_detail_id
261 and au.time_building_block_ovn = p_detail_ovn
262 and au.time_attribute_id = a.time_attribute_id
263 and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
264
265 l_cursor_attributes c_block_attributes%ROWTYPE;
266
267
268 BEGIN
269 l_block_index := p_detail_blocks.first;
270
271 LOOP
272 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
273
274 OPEN c_block_attributes(
275 p_detail_id => p_detail_blocks(l_block_index).block_id
276 ,p_detail_ovn => p_detail_blocks(l_block_index).block_ovn
277 );
278
279 LOOP
280 FETCH c_block_attributes INTO l_detail_attributes(l_attribute_index);
281 EXIT WHEN c_block_attributes%NOTFOUND;
282
283
284 -- populate new structure
285 l_new_attributes.extend();
286 l_new_attributes(l_attribute_index) := HXC_ATTRIBUTE_TYPE (l_detail_attributes(l_attribute_index).TIME_ATTRIBUTE_ID,
287 l_detail_attributes(l_attribute_index).BUILDING_BLOCK_ID,
288 l_detail_attributes(l_attribute_index).ATTRIBUTE_CATEGORY,
289 l_detail_attributes(l_attribute_index).ATTRIBUTE1,
290 l_detail_attributes(l_attribute_index).ATTRIBUTE2,
291 l_detail_attributes(l_attribute_index).ATTRIBUTE3,
292 l_detail_attributes(l_attribute_index).ATTRIBUTE4,
293 l_detail_attributes(l_attribute_index).ATTRIBUTE5,
294 l_detail_attributes(l_attribute_index).ATTRIBUTE6,
295 l_detail_attributes(l_attribute_index).ATTRIBUTE7,
296 l_detail_attributes(l_attribute_index).ATTRIBUTE8,
297 l_detail_attributes(l_attribute_index).ATTRIBUTE9,
298 l_detail_attributes(l_attribute_index).ATTRIBUTE10,
299 l_detail_attributes(l_attribute_index).ATTRIBUTE11,
300 l_detail_attributes(l_attribute_index).ATTRIBUTE12,
301 l_detail_attributes(l_attribute_index).ATTRIBUTE13,
302 l_detail_attributes(l_attribute_index).ATTRIBUTE14,
303 l_detail_attributes(l_attribute_index).ATTRIBUTE15,
304 l_detail_attributes(l_attribute_index).ATTRIBUTE16,
305 l_detail_attributes(l_attribute_index).ATTRIBUTE17,
306 l_detail_attributes(l_attribute_index).ATTRIBUTE18,
307 l_detail_attributes(l_attribute_index).ATTRIBUTE19,
308 l_detail_attributes(l_attribute_index).ATTRIBUTE20,
309 l_detail_attributes(l_attribute_index).ATTRIBUTE21,
310 l_detail_attributes(l_attribute_index).ATTRIBUTE22,
311 l_detail_attributes(l_attribute_index).ATTRIBUTE23,
312 l_detail_attributes(l_attribute_index).ATTRIBUTE24,
313 l_detail_attributes(l_attribute_index).ATTRIBUTE25,
314 l_detail_attributes(l_attribute_index).ATTRIBUTE26,
315 l_detail_attributes(l_attribute_index).ATTRIBUTE27,
316 l_detail_attributes(l_attribute_index).ATTRIBUTE28,
317 l_detail_attributes(l_attribute_index).ATTRIBUTE29,
318 l_detail_attributes(l_attribute_index).ATTRIBUTE30,
319 l_detail_attributes(l_attribute_index).BLD_BLK_INFO_TYPE_ID,
320 l_detail_attributes(l_attribute_index).OBJECT_VERSION_NUMBER,
321 l_detail_attributes(l_attribute_index).NEW,
322 l_detail_attributes(l_attribute_index).CHANGED,
323 l_detail_attributes(l_attribute_index).BLD_BLK_INFO_TYPE,
324 NULL,
325 1 );
326
327 l_attribute_index := l_attribute_index + 1;
328 END LOOP;
329
330 CLOSE c_block_attributes;
331
332 l_block_index := p_detail_blocks.next(l_block_index);
333 END LOOP;
334
335 p_detail_attributes := l_detail_attributes;
336 p_new_detail_attributes := l_new_attributes;
337
338 END get_detail_attributes;
339
340 Function same_block
341 (p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
342 p_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
343 p_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
344 ) return boolean is
345
346 cursor c_block
347 (p_app_id in hxc_time_building_blocks.time_building_block_id%TYPE,
348 p_block_id in hxc_time_building_blocks.time_building_block_id%TYPE
349 ) is
350 select max(time_building_block_ovn)
351 from hxc_ap_detail_links
352 where application_period_id = p_app_id
353 and time_building_block_id = p_block_id;
354
355 cursor c_test_translation_key
356 (p_block1_id in hxc_time_building_blocks.time_building_block_id%TYPE,
357 p_block1_ovn in hxc_time_building_blocks.object_version_number%TYPE,
358 p_block2_ovn in hxc_time_building_blocks.object_version_number%TYPE
359 ) is
360 select tbb2.object_version_number
361 from hxc_time_building_blocks tbb1,
362 hxc_time_building_blocks tbb2
363 where tbb1.time_building_block_id = p_block1_id
364 and tbb1.time_building_block_id = tbb2.time_building_block_id
365 and tbb1.object_version_number = p_block1_ovn
366 and tbb2.object_version_number = p_block2_ovn
367 and tbb1.type = tbb2.type
368 and nvl(tbb1.measure,hr_api.g_number) = nvl(tbb2.measure,hr_api.g_number)
369 and nvl(tbb1.unit_of_measure,hr_api.g_varchar2) = nvl(tbb2.unit_of_measure,hr_api.g_varchar2)
370 and nvl(tbb1.start_time,hr_api.g_date) = nvl(tbb2.start_time,hr_api.g_date)
371 and nvl(tbb1.stop_time,hr_api.g_date) = nvl(tbb2.stop_time,hr_api.g_date)
372 and tbb1.approval_status = tbb2.approval_status
373 and nvl(tbb1.approval_style_id,hr_api.g_number) = nvl(tbb2.approval_style_id,hr_api.g_number)
374 and nvl(tbb1.comment_text,hr_api.g_varchar2) = nvl(tbb2.comment_text,hr_api.g_varchar2)
375 and nvl(tbb1.application_set_id,hr_api.g_number) = nvl(tbb1.application_set_id,hr_api.g_number)
376 and nvl(tbb1.data_set_id,hr_api.g_number) = nvl(tbb1.data_set_id,hr_api.g_number)
377 and nvl(tbb1.translation_display_key,hr_api.g_varchar2) <> nvl(tbb2.translation_display_key,hr_api.g_varchar2);
378
379 l_block_ovn hxc_ap_detail_links.time_building_block_ovn%type;
380
381 Begin
382 open c_block(p_app_id, p_block_id);
383 fetch c_block into l_block_ovn;
384
385 if (c_block%notfound) then
386 close c_block;
387 return false;
388 elsif (p_block_ovn = l_block_ovn) then
389 close c_block;
390 return true;
391 else
392 close c_block;
393 --
394 -- check to see if it is just the translation display key
395 -- that is different
396 --
397 open c_test_translation_key(p_block_id,p_block_ovn,l_block_ovn);
398 fetch c_test_translation_key into l_block_ovn;
399 if(c_test_translation_key%found) then
400 close c_test_translation_key;
401 return true;
402 else
403 close c_test_translation_key;
404 return false;
405 end if;
406 end if;
407
408 End same_block;
409
410 function no_blocks(
411 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
412 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
413 )
414
415 RETURN NUMBER
416 IS
417 CURSOR c_no_blocks(
418 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
419 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
420 )
421 IS
422 SELECT count(p_app_id)
423 FROM hxc_ap_detail_links apdetail
424 ,hxc_time_building_blocks days
425 ,hxc_time_building_blocks details
426 WHERE apdetail.application_period_id = p_app_id
427 AND days.parent_building_block_id = p_timecard_id
428 AND details.parent_building_block_id = days.time_building_block_id
429 AND details.time_building_block_id = apdetail.time_building_block_id
430 AND details.object_version_number = apdetail.time_building_block_ovn
431 AND details.date_to <> hr_general.end_of_time
432 and not exists(
433 select 1
434 from hxc_time_building_blocks details2
435 where details2.time_building_block_id = details.time_building_block_id
436 and details2.date_to = hr_general.end_of_time
437 );
438
439 l_count number := 0;
440
441 BEGIN
442 OPEN c_no_blocks(p_app_id, p_timecard_id);
443 FETCH c_no_blocks INTO l_count;
444 CLOSE c_no_blocks;
445
446 RETURN l_count;
447 END no_blocks;
448
449 FUNCTION changed(
450 p_detail_blocks IN OUT NOCOPY block_table
451 ,p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
452 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
453 ,p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
454 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
455 )
456 RETURN BOOLEAN
457 IS
458 l_block_index NUMBER;
459 l_count number := 0;
460 l_same boolean := true;
461
462 l_proc varchar2(50) := 'HXC_APPROVAL_WF_PKG.changed';
463 BEGIN
464 g_debug:=hr_utility.debug_enabled;
465 if g_debug then
466 hr_utility.set_location(l_proc, 10);
467 end if;
468
469 IF p_time_category_id IS NULL OR p_time_category_id = 0
470 THEN
471 l_block_index := p_detail_blocks.first;
472 LOOP
473 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
474 if g_debug then
475 hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
476 hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
477 hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
478 end if;
479
480 IF p_detail_blocks(l_block_index).added <> 'Y'
481 THEN
482 l_count := l_count + 1;
483 if g_debug then
484 hr_utility.set_location(l_proc, 20);
485 end if;
486
487 IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
488 , p_detail_blocks(l_block_index).block_ovn)
489 THEN
490 if g_debug then
491 hr_utility.set_location(l_proc, 30);
492 end if;
493
494 g_block_exist_for_ap := 'Y';
495
496 RETURN TRUE;
497 END IF;
498
499 END IF;
500
501 l_block_index := p_detail_blocks.next(l_block_index);
502 END LOOP;
503
504 ELSE
505 if g_debug then
506 hr_utility.set_location(l_proc, 40);
507 end if;
508
509 hxc_time_category_utils_pkg.initialise_time_category(
510 p_time_category_id => p_time_category_id
511 ,p_tco_att => p_attributes
512 );
513
514 l_block_index := p_detail_blocks.first;
515 LOOP
516 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
517 IF hxc_time_category_utils_pkg.chk_tc_bb_ok
518 (p_detail_blocks(l_block_index).block_id)THEN
519 p_detail_blocks(l_block_index).added := 'Y';
520 l_count := l_count + 1;
521
522 IF NOT same_block(p_app_id, p_detail_blocks(l_block_index).block_id
523 , p_detail_blocks(l_block_index).block_ovn)
524 THEN
525 if g_debug then
526 hr_utility.set_location(l_proc, 60);
527 end if;
528
529 g_block_exist_for_ap := 'Y';
530
531 RETURN TRUE;
532 END IF;
533
534 END IF;
535
536 l_block_index := p_detail_blocks.next(l_block_index);
537 END LOOP;
538 END IF;
539
540 IF no_blocks(p_app_id, p_timecard_id) = 0
541 THEN
542 if g_debug then
543 hr_utility.trace('number not changed');
544 end if;
545 RETURN FALSE;
546 ELSE
547 if g_debug then
548 hr_utility.trace('number changed');
549 end if;
550 RETURN TRUE;
551 END IF;
552 END changed;
553
554 PROCEDURE remove_ap_detail_links(
555 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
556 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
557 )
558 IS
559 CURSOR c_detail_blocks(
560 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
561 )
562 IS
563 SELECT details.time_building_block_id
564 ,details.object_version_number
565 FROM hxc_time_building_blocks days
566 ,hxc_time_building_blocks details
567 WHERE days.parent_building_block_id = p_timecard_id
568 AND details.parent_building_block_id = days.time_building_block_id
569 AND days.scope = 'DAY'
570 AND details.scope = 'DETAIL';
571
572 CURSOR c_old_blocks(
573 p_app_period hxc_time_building_blocks.time_building_block_id%TYPE
574 )
575 IS
576 SELECT details.time_building_block_id
577 ,details.object_version_number
578 FROM hxc_ap_detail_links apdetail
579 ,hxc_time_building_blocks details
580 WHERE apdetail.application_period_id = p_app_period
581 AND apdetail.time_building_block_id = details.time_building_block_id
582 AND apdetail.time_building_block_ovn = details.object_version_number
583 AND details.date_to <> hr_general.end_of_time;
584
585
586 l_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
587 l_detail_ovn hxc_time_building_blocks.object_version_number%TYPE;
588 BEGIN
589 OPEN c_detail_blocks(p_timecard_id);
590
591 LOOP
592 FETCH c_detail_blocks INTO l_detail_id, l_detail_ovn;
593 EXIT WHEN c_detail_blocks%NOTFOUND;
594
595 delete from hxc_ap_detail_links
596 where time_building_block_id = l_detail_id
597 and time_building_block_ovn = l_detail_ovn
598 and application_period_id = p_app_id;
599
600 END LOOP;
601
602 CLOSE c_detail_blocks;
603
604 OPEN c_old_blocks(p_app_id);
605 LOOP
606 FETCH c_old_blocks INTO l_detail_id, l_detail_ovn;
607 EXIT WHEN c_old_blocks%NOTFOUND;
608
609 delete from hxc_ap_detail_links
610 where time_building_block_id = l_detail_id
611 and time_building_block_ovn = l_detail_ovn
612 and application_period_id = p_app_id;
613 END LOOP;
614
615 CLOSE c_old_blocks;
616
617 END remove_ap_detail_links;
618
619
620 FUNCTION is_empty(
621 p_detail_blocks IN OUT NOCOPY block_table
622 ,p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
623 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
624 )
625 RETURN BOOLEAN
626 IS
627 l_block_index NUMBER;
628 BEGIN
629
630 --
631 -- Check the attributes are ok
632
633 hxc_time_category_utils_pkg.initialise_time_category(
634 p_time_category_id => p_time_category_id
635 ,p_tco_att => p_attributes
636 );
637
638 l_block_index := p_detail_blocks.first;
639 LOOP
640 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
641
642 if g_debug then
643 hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
644 hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
645 hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
646 end if;
647
648 IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
649 p_detail_blocks(l_block_index).block_id
650 )
651 THEN
652 RETURN FALSE;
653 END IF;
654
655 l_block_index := p_detail_blocks.next(l_block_index);
656 END LOOP;
657
658 RETURN TRUE;
659 END is_empty;
660
661
662
663 PROCEDURE link_ap_details(
664 p_detail_blocks IN OUT NOCOPY block_table
665 ,p_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
666 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
667 ,p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
668 )
669 IS
670 l_block_index NUMBER;
671 BEGIN
672
673 hxc_time_category_utils_pkg.initialise_time_category(
674 p_time_category_id => p_time_category_id
675 ,p_tco_att => p_attributes
676 );
677
678 l_block_index := p_detail_blocks.first;
679 LOOP
680 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
681
682 if g_debug then
683 hr_utility.trace('detail_id=' || p_detail_blocks(l_block_index).block_id);
684 hr_utility.trace('detail_ovn=' || p_detail_blocks(l_block_index).block_ovn);
685 hr_utility.trace('detail_added=' || p_detail_blocks(l_block_index).added);
686 end if;
687
688 IF hxc_time_category_utils_pkg.chk_tc_bb_ok(
689 p_detail_blocks(l_block_index).block_id
690 )
691 THEN
692 --set added flag
693 --we use this flag to find all the category 0 blocks
694 p_detail_blocks(l_block_index).added := 'Y';
695
696 --insert a line in hxc_detail_summary;
697 hxc_ap_detail_links_pkg.insert_summary_row(
698 p_app_id
699 ,p_detail_blocks(l_block_index).block_id
700 ,p_detail_blocks(l_block_index).block_ovn
701 );
702
703 if g_debug then
704 hr_utility.trace('linked!');
705 end if;
706
707 END IF;
708
709 l_block_index := p_detail_blocks.next(l_block_index);
710 END LOOP;
711
712 END link_ap_details;
713
714 PROCEDURE link_ap_details_all(
715 p_detail_blocks IN OUT NOCOPY block_table
716 ,p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
717 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
718 )
719 IS
720 l_block_index NUMBER;
721
722 BEGIN
723
724 if g_debug then
725 hr_utility.trace('in link_ap_details_all');
726 end if;
727
728 l_block_index := p_detail_blocks.first;
729 LOOP
730 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
731
732 if g_debug then
733 hr_utility.trace('block_id=' || p_detail_blocks(l_block_index).block_id);
734 hr_utility.trace('block_ovn=' || p_detail_blocks(l_block_index).block_ovn);
735 hr_utility.trace('added=' || p_detail_blocks(l_block_index).added);
736 end if;
737 l_block_index := p_detail_blocks.next(l_block_index);
738 END LOOP;
739
740 l_block_index := p_detail_blocks.first;
741
742 LOOP
743 EXIT WHEN NOT p_detail_blocks.exists(l_block_index);
744
745 IF p_time_category_id IS NULL
746 OR (p_time_category_id = 0 and p_detail_blocks(l_block_index).added <> 'Y')
747 THEN
748 --set added flag
749 --we use this flag to find all the category 0 blocks
750 if g_debug then
751 hr_utility.trace('inserting id=' || p_detail_blocks(l_block_index).block_id
752 || '|ovn=' || p_detail_blocks(l_block_index).block_ovn);
753 end if;
754
755 p_detail_blocks(l_block_index).added := 'Y';
756
757
758 --insert a line in hxc_detail_summary;
759 hxc_ap_detail_links_pkg.insert_summary_row(
760 p_app_id
761 ,p_detail_blocks(l_block_index).block_id
762 ,p_detail_blocks(l_block_index).block_ovn
763 );
764
765
766 END IF;
767
768 l_block_index := p_detail_blocks.next(l_block_index);
769
770 END LOOP;
771
772 if g_debug then
773 hr_utility.trace('end link_ap_details_all');
774 end if;
775 END link_ap_details_all;
776
777 FUNCTION get_person_id(
778 p_user_name IN fnd_user.user_name%TYPE
779 )
780 RETURN fnd_user.employee_id%TYPE
781 IS
782 CURSOR c_person_id(p_user_name fnd_user.user_name%TYPE)
783 IS
784 SELECT u.employee_id
785 FROM FND_USER u
786 WHERE u.user_name = p_user_name;
787
788 l_person_id fnd_user.employee_id%TYPE;
789 BEGIN
790 OPEN c_person_id(p_user_name);
791
792 FETCH c_person_id INTO l_person_id;
793 IF c_person_id%NOTFOUND
794 THEN
795 CLOSE c_person_id;
796
797 --raise; ???
798 END IF;
799
800 CLOSE c_person_id;
801
802 RETURN l_person_id;
803 END get_person_id;
804
805
806 FUNCTION get_empty_attribute
807 RETURN hxc_time_attributes_api.timecard
808 IS
809 t_attributes hxc_time_attributes_api.timecard;
810
811 BEGIN
812 t_attributes.delete;
813 t_attributes(1).attribute_name := NULL;
814 t_attributes(1).attribute_value := NULL;
815 t_attributes(1).information_type := NULL;
816 t_attributes(1).column_name := NULL;
817 t_attributes(1).info_mapping_type := NULL;
818
819 RETURN t_attributes;
820
821 END get_empty_attribute;
822 Function find_mysterious_approver
823 (p_item_type in wf_items.item_type%type
824 ,p_item_key in wf_item_activity_statuses.item_key%type
825 ) return number is
826
827 cursor c_find_approver_role
828 (itemType in wf_items.item_type%type
829 ,itemKey in wf_item_activity_statuses.item_key%type) is
830 select wlr.orig_system, wlr.orig_system_id
831 from wf_notifications wn, wf_process_activities pa, wf_item_activity_statuses wias, wf_local_roles wlr
832 where pa.activity_name = 'TC_APR_NOTIFICATION'
833 and pa.activity_item_type = itemType
834 and pa.instance_id = wias.process_activity
835 and wias.notification_id = wn.notification_id
836 and wias.item_key = itemKey
837 and wlr.name = wn.recipient_role
838 and wias.item_type = pa.activity_item_type;
839
840 cursor c_find_employee_id
841 (userId in fnd_user.user_id%type) is
842 select employee_id
843 from fnd_user
844 where user_id = userId;
845
846 l_approver_id wf_local_roles.orig_system_id%type;
847 l_approver_system wf_local_roles.orig_system%type;
848
849
850 Begin
851
852 open c_find_approver_role(p_item_type,p_item_key);
853 fetch c_find_approver_role into l_approver_system, l_approver_id;
854 if(c_find_approver_role%notfound) then
855 close c_find_approver_role;
856 l_approver_id := -1;
857 else
858 close c_find_approver_role;
859 if((l_approver_system <> 'PER') AND (l_approver_system <> 'FND_USR')) then
860 l_approver_id := -1;
861 elsif(l_approver_system = 'FND_USR') then
862 open c_find_employee_id(l_approver_id);
863 fetch c_find_employee_id into l_approver_id;
864 if (c_find_employee_id%notfound) then
865 close c_find_employee_id;
866 l_approver_id := -1;
867 else
868 close c_find_employee_id;
869 end if;
870 end if; -- other option is PER, and then it's already set properly
871 end if;
872
873 return l_approver_id;
874
875 End find_mysterious_approver;
876
877 PROCEDURE update_latest_details(p_app_bb_id in number)
878 is
879
880 l_bb_id number;
881 l_bb_ovn number;
882 l_other_app_id number;
883
884 cursor get_building_blocks(p_app_bb_id in number)
885 is
886 select time_building_block_id, time_building_block_ovn
887 from hxc_ap_detail_links
888 where application_period_id = p_app_bb_id;
889
890 cursor get_app_period(p_bb_id in number, p_bb_ovn in number ,p_app_bb_id in number)
891 is
892 select adl.application_period_id
893 from hxc_ap_detail_links adl,
894 hxc_app_period_summary haps
895 where adl.time_building_block_id = p_bb_id
896 and adl.time_building_block_ovn = p_bb_ovn
897 and adl.application_period_id <> p_app_bb_id
898 and adl.application_period_id = haps.application_period_id
899 and haps.approval_status <> 'APPROVED';
900 begin
901
902 open get_building_blocks(p_app_bb_id);
903 fetch get_building_blocks into l_bb_id,l_bb_ovn;
904
905 LOOP
906 exit when get_building_blocks%notfound;
907
908 open get_app_period(l_bb_id, l_bb_ovn, p_app_bb_id);
909 fetch get_app_period into l_other_app_id;
910
911 IF get_app_period%notfound then
912 update hxc_latest_details
913 set last_update_date = sysdate
914 where time_building_block_id = l_bb_id
915 and object_version_number = l_bb_ovn;
916 END IF;
917 close get_app_period;
918
919 fetch get_building_blocks into l_bb_id, l_bb_ovn;
920
921 END LOOP;
922
923 close get_building_blocks;
924
925 END update_latest_details;
926
927 PROCEDURE update_app_period(
928 itemtype IN varchar2,
929 itemkey IN varchar2,
930 actid IN number,
931 funcmode IN varchar2,
932 result IN OUT NOCOPY varchar2
933 )
934 IS
935 t_attributes hxc_time_attributes_api.timecard;
936 l_attribute approval_attribute;
937 l_approver varchar2(150);
938 l_user_name varchar2(150);
939 l_appl_period_bb_id number;
940 l_appl_period_bb_ovn number;
941 l_tc_resource_id number;
942 l_period_start_date date;
943 l_period_end_date date;
944 l_approval_status hxc_time_building_blocks.approval_status%type;
945 l_approver_comment hxc_time_building_blocks.comment_text%TYPE;
946 l_creation_date hxc_time_building_blocks.creation_date%TYPE;
947 l_wf_item_type varchar2(500) := NULL;
948 l_is_blank varchar2(10);
949 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.update_appl_period';
950
951
952 BEGIN
953 g_debug:=hr_utility.debug_enabled;
954 if g_debug then
955 hr_utility.set_location(l_proc, 10);
956 end if;
957
958 l_tc_resource_id := wf_engine.GetItemAttrNumber(
959 itemtype => itemtype,
960 itemkey => itemkey ,
961 aname => 'RESOURCE_ID');
962
963
964 if g_debug then
965 hr_utility.set_location(l_proc, 30);
966 end if;
967
968 l_period_start_date := wf_engine.GetItemAttrDate(
969 itemtype => itemtype,
970 itemkey => itemkey ,
971 aname => 'APP_START_DATE');
972
973 if g_debug then
974 hr_utility.set_location(l_proc, 40);
975 end if;
976
977 l_period_end_date := wf_engine.GetItemAttrDate(
978 itemtype => itemtype,
979 itemkey => itemkey ,
980 aname => 'APP_END_DATE');
981
982 if g_debug then
983 hr_utility.set_location(l_proc, 50);
984 end if;
985
986 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
987 itemtype => itemtype,
988 itemkey => itemkey,
989 aname => 'APP_BB_ID');
990
991 if g_debug then
992 hr_utility.set_location(l_proc, 60);
993 end if;
994
995 l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
996 itemtype => itemtype,
997 itemkey => itemkey,
998 aname => 'APP_BB_OVN');
999
1000 if g_debug then
1001 hr_utility.set_location(l_proc, 70);
1002 end if;
1003
1004 -- Set up the approval status - get the value for the APPROVAL_STATUS
1005 -- attribute, which is set up in the activity previous to this one.
1006 --
1007 l_approval_status := wf_engine.GetItemAttrText(
1008 itemtype => itemtype,
1009 itemkey => itemkey ,
1010 aname => 'APPROVAL_STATUS');
1011
1012 l_approver_comment := wf_engine.GetItemAttrText(
1013 itemtype => itemtype,
1014 itemkey => itemkey,
1015 aname => 'APR_REJ_REASON');
1016
1017 if g_debug then
1018 hr_utility.set_location(l_proc, 80);
1019
1020 hr_utility.trace('l_approval_status is : ' || l_approval_status);
1021 end if;
1022
1023 --get approver id
1024 --what happens to l_approver if AUTO_APPROVE??
1025
1026 l_wf_item_type := wf_engine.GetItemAttrText(
1027 itemtype => itemtype
1028 ,itemkey => itemkey
1029 ,aname => 'WF_ITEM_TYPE'
1030 );
1031
1032 IF l_wf_item_type IS NOT NULL
1033 THEN
1034 --current workflow doesn't populate this fied for custom
1035 --workflow either
1036 l_approver := NULL;
1037 ELSE
1038 IF l_approver_comment = 'AUTO_APPROVE'
1039 OR l_approver_comment = 'TIMED_OUT'
1040 THEN
1041 l_approver := NULL;
1042 ELSE
1043 --
1044 -- 115.90 Change. Since this could be an e-mail notification
1045 -- response, our first check is to use the find approver
1046 -- function, since the employee id could be anyone in the case
1047 -- of e-mail. The notification information definitely will
1048 -- provide the right approver.
1049 --
1050 l_approver := find_mysterious_approver
1051 (itemtype, itemkey);
1052 END IF;
1053 END IF;
1054 /*
1055
1056 Bug: 3205338: If the approver id is -1,
1057 i.e. fnd_global.employee_id is
1058
1059 */
1060
1061 l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
1062 itemkey => itemkey ,
1063 aname => 'IS_DIFF_TC',
1064 ignore_notfound => true);
1065 if l_is_blank = 'Y' then
1066 l_approver_comment := l_approver_comment ||'BLANK_NOTIFICATION';
1067 end if;
1068
1069 t_attributes := get_empty_attribute;
1070
1071 hxc_deposit_process_pkg.execute_deposit_process(
1072 p_process_name => g_process_name
1073 ,p_source_name => g_source_name
1074 ,p_effective_date => trunc(sysdate)
1075 ,p_type => 'RANGE'
1076 ,p_measure => null
1077 ,p_unit_of_measure => null
1078 ,p_start_time => l_period_start_date
1079 ,p_stop_time => l_period_end_date
1080 ,p_parent_building_block_id => null
1081 ,p_parent_building_block_ovn => null
1082 ,p_scope => 'APPLICATION_PERIOD'
1083 ,p_approval_style_id => NULL
1084 ,p_approval_status => l_approval_status
1085 ,p_resource_id => l_tc_resource_id
1086 ,p_resource_type => g_resource_type
1087 ,p_comment_text => l_approver_comment
1088 ,p_timecard => t_attributes
1089 ,p_time_building_block_id => l_appl_period_bb_id
1090 ,p_object_version_number => l_appl_period_bb_ovn
1091 );
1092
1093
1094 if(l_approver = -1) then
1095 l_approver := find_mysterious_approver(itemtype,itemkey);
1096 end if;
1097
1098 if g_debug then
1099 hr_utility.set_location(l_proc, 90);
1100 end if;
1101
1102 --update hxc_application_period_summary table
1103 l_creation_date := get_creation_date(l_appl_period_bb_id, l_appl_period_bb_ovn);
1104 update hxc_app_period_summary
1105 set application_period_ovn = l_appl_period_bb_ovn
1106 ,approval_status = l_approval_status
1107 ,approver_id = l_approver
1108 ,notification_status = 'FINISHED'
1109 ,creation_date = l_creation_date
1110 where application_period_id = l_appl_period_bb_id;
1111
1112 hxc_timecard_summary_api.reevaluate_timecard_statuses
1113 (p_application_period_id => l_appl_period_bb_id);
1114
1115 update_latest_details(l_appl_period_bb_id);
1116
1117 -- Set up the result as APPROVED or REJECTED, so that the process_appl_periods
1118 -- is only done again if this row has been APPROVED.
1119 --
1120 IF upper(l_approval_status) = 'APPROVED' THEN
1121 result := 'COMPLETE:APPROVED';
1122 ELSIF upper(l_approval_status) = 'REJECTED' THEN
1123 result := 'COMPLETE:REJECTED';
1124 END IF;
1125
1126
1127 if g_debug then
1128 hr_utility.set_location(l_proc, 110);
1129 end if;
1130
1131 /*
1132 Since this could be the last operation in the approvals process
1133 as at the end of the workflow, there will be no more approval
1134 components to process and hence process_appl_periods (the next
1135 activity in the sequence), won't actually do anything, we issue
1136 a commit at this point, to commit the outstanding approval data
1137 from this transaction
1138
1139 This is bug 3449786
1140 */
1141
1142 commit;
1143
1144 return;
1145
1146 exception
1147 when others then
1148 -- The line below records this function call in the error system
1149 -- in the case of an exception.
1150 --
1151 if g_debug then
1152 hr_utility.trace(sqlerrm);
1153 hr_utility.trace('lllllllllllllllllll');
1154 hr_utility.trace(hr_message.last_message_name);
1155 hr_utility.trace('----');
1156 end if;
1157 IF sqlerrm like '%HXC_TIME_BLD_BLK_NOT_LATEST%' THEN
1158 RETURN;
1159 END IF;
1160 --
1161 if g_debug then
1162 hr_utility.set_location(l_proc, 999);
1163 hr_utility.trace('IN EXCEPTION IN update_appl_period');
1164 end if;
1165 --
1166 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
1167 itemtype, itemkey, to_char(actid), funcmode);
1168 raise;
1169 result := '';
1170 return;
1171 --
1172 --
1173 END update_app_period;
1174
1175
1176 --this procedure basically creates a duplicate of the current application period
1177 --we need this for HR Supervisor mechanism
1178 PROCEDURE create_next_period(
1179 itemtype IN varchar2,
1180 itemkey IN varchar2,
1181 actid IN number,
1182 funcmode IN varchar2,
1183 result IN OUT NOCOPY varchar2
1184 )
1185 IS
1186 CURSOR c_current_period(
1187 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
1188 )
1189 IS
1190 SELECT *
1191 FROM hxc_app_period_summary
1192 WHERE application_period_id = p_app_id;
1193
1194 CURSOR c_timecards(
1195 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
1196 )
1197 IS
1198 SELECT timecard_id
1199 FROM hxc_tc_ap_links
1200 WHERE application_period_id = p_app_id;
1201
1202 CURSOR c_ap_details(
1203 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE
1204 )
1205 IS
1206 SELECT time_building_block_id
1207 ,time_building_block_ovn
1208 FROM hxc_ap_detail_links
1209 WHERE application_period_id = p_app_id;
1210
1211
1212 l_current_period hxc_app_period_summary%rowtype;
1213 l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
1214 l_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
1215 l_detail_ovn hxc_time_building_blocks.object_version_number%TYPE;
1216 l_new_appl_bb_id hxc_time_building_blocks.time_building_block_id%TYPE := NULL;
1217 l_new_appl_bb_ovn hxc_time_building_blocks.object_version_number%TYPE := NULL;
1218 l_period_start_date hxc_time_building_blocks.start_time%TYPE;
1219 l_period_end_date hxc_time_building_blocks.stop_time%TYPE;
1220 l_tc_resource_id hxc_time_building_blocks.resource_id%TYPE;
1221 l_attribute approval_attribute;
1222 t_attributes hxc_time_attributes_api.timecard;
1223 l_period_id hxc_time_building_blocks.time_building_block_id%TYPE;
1224 l_creation_date hxc_time_building_blocks.creation_date%TYPE;
1225
1226 l_proc VARCHAR2(150) := 'create_next_period';
1227 BEGIN
1228 g_debug:=hr_utility.debug_enabled;
1229 if g_debug then
1230 hr_utility.trace('in create_next_period');
1231 end if;
1232
1233 IF funcmode = 'RUN'
1234 THEN
1235 l_period_id := wf_engine.GetItemAttrNumber(
1236 itemtype => itemtype,
1237 itemkey => itemkey ,
1238 aname => 'APP_BB_ID');
1239
1240 l_period_start_date := wf_engine.GetItemAttrDate(
1241 itemtype => itemtype,
1242 itemkey => itemkey ,
1243 aname => 'APP_START_DATE');
1244
1245 l_period_end_date := wf_engine.GetItemAttrDate(
1246 itemtype => itemtype,
1247 itemkey => itemkey ,
1248 aname => 'APP_END_DATE');
1249
1250 l_tc_resource_id := wf_engine.GetItemAttrNumber(
1251 itemtype => itemtype,
1252 itemkey => itemkey ,
1253 aname => 'RESOURCE_ID');
1254
1255 t_attributes := get_empty_attribute;
1256
1257 hxc_deposit_process_pkg.execute_deposit_process
1258 (p_process_name => g_process_name
1259 ,p_source_name => g_source_name
1260 ,p_effective_date => trunc(sysdate)
1261 ,p_type => 'RANGE'
1262 ,p_measure => null
1263 ,p_unit_of_measure => null
1264 ,p_start_time => trunc(l_period_start_date)
1265 ,p_stop_time => trunc(l_period_end_date)
1266 ,p_parent_building_block_id => null
1267 ,p_parent_building_block_ovn => null
1268 ,p_scope => 'APPLICATION_PERIOD'
1269 ,p_approval_style_id => NULL
1270 ,p_approval_status => 'SUBMITTED'
1271 ,p_resource_id => l_tc_resource_id
1272 ,p_resource_type => g_resource_type
1273 ,p_comment_text => null
1274 ,p_timecard => t_attributes
1275 ,p_time_building_block_id => l_new_appl_bb_id
1276 ,p_object_version_number => l_new_appl_bb_ovn);
1277
1278 if g_debug then
1279 hr_utility.trace('next period created=' || l_new_appl_bb_id);
1280 hr_utility.trace('next period created=' || l_new_appl_bb_ovn);
1281 end if;
1282
1283 --populating summary tables
1284 l_creation_date := get_creation_date(l_new_appl_bb_id, l_new_appl_bb_ovn);
1285
1286 OPEN c_current_period(
1287 p_app_id => l_period_id
1288 );
1289
1290 FETCH c_current_period INTO l_current_period;
1291 IF c_current_period%NOTFOUND
1292 THEN
1293 CLOSE c_current_period;
1294
1295 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296 hr_utility.set_message_token('PROCEDURE', l_proc);
1297 hr_utility.set_message_token('STEP', '20');
1298 hr_utility.raise_error;
1299 END IF;
1300
1301 CLOSE c_current_period;
1302
1303 g_trace := l_proc || '80';
1304 /*
1305 --populate hxc_app_period_summary with the new row
1306 INSERT INTO hxc_app_period_summary
1307 (APPLICATION_PERIOD_ID
1308 ,APPLICATION_PERIOD_OVN
1309 ,APPROVAL_STATUS
1310 ,TIME_RECIPIENT_ID
1311 ,TIME_CATEGORY_ID
1312 ,START_TIME
1313 ,STOP_TIME
1314 ,RESOURCE_ID
1315 ,RECIPIENT_SEQUENCE
1316 ,CATEGORY_SEQUENCE
1317 ,CREATION_DATE
1318 ,NOTIFICATION_STATUS
1319 ,APPROVER_ID
1320 ,APPROVAL_COMP_ID
1321 )
1322 VALUES
1323 */
1324 hxc_app_period_summary_pkg.insert_summary_row
1325 (l_new_appl_bb_id
1326 ,l_new_appl_bb_ovn
1327 ,'SUBMITTED'
1328 ,l_current_period.time_recipient_id
1329 ,l_current_period.time_category_id
1330 ,l_current_period.start_time
1331 ,l_current_period.stop_time
1332 ,l_current_period.resource_id
1333 ,l_current_period.recipient_sequence
1334 ,l_current_period.category_sequence
1335 ,l_creation_date
1336 ,'NOTIFIED'
1337 ,NULL
1338 ,l_current_period.approval_comp_id
1339 ,NULL
1340 ,NULL
1341 ,Null
1342 ,l_current_period.data_set_id
1343 );
1344
1345 --populate hxc_tc_ap_links
1346 OPEN c_timecards(
1347 p_app_id => l_period_id
1348 );
1349
1350 LOOP
1351 FETCH c_timecards into l_timecard_id;
1352
1353 EXIT WHEN c_timecards%NOTFOUND;
1354 -- 115.76 Not changed this one, since this procedure
1355 -- no longer seems to be called.
1356 hxc_tc_ap_links_pkg.insert_summary_row(
1357 l_timecard_id
1358 ,l_new_appl_bb_id);
1359
1360 --Bug 5554020.
1361 hxc_timecard_summary_api.reevaluate_timecard_statuses(l_new_appl_bb_id);
1362
1363 END LOOP;
1364
1365 CLOSE c_timecards;
1366
1367 --populating hxc_ap_detail_links
1368 OPEN c_ap_details(
1369 p_app_id => l_period_id
1370 );
1371
1372 LOOP
1373 FETCH c_ap_details INTO l_detail_id, l_detail_ovn;
1374
1375 EXIT WHEN c_ap_details%NOTFOUND;
1376 /*
1377 INSERT INTO hxc_ap_detail_links
1378 (application_period_id,
1379 time_building_block_id,
1380 time_building_block_ovn)
1381 VALUES
1382 */
1383
1384 hxc_ap_detail_links_pkg.insert_summary_row
1385 (l_new_appl_bb_id,
1386 l_detail_id,
1387 l_detail_ovn);
1388 END LOOP;
1389
1390 CLOSE c_ap_details;
1391
1392 --now set workflow attribute to the new application period
1393 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1394 itemkey => itemkey,
1395 aname => 'NEXT_APP_BB_ID',
1396 avalue => l_new_appl_bb_id);
1397
1398 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1399 itemkey => itemkey,
1400 aname => 'NEXT_APP_BB_OVN',
1401 avalue => l_new_appl_bb_ovn);
1402
1403 result := 'COMPLETE';
1404 return;
1405 END IF;
1406
1407 exception
1408 when others then
1409 -- The line below records this function call in the error system
1410 -- in the case of an exception.
1411 --
1412 if g_debug then
1413 hr_utility.set_location(l_proc, 999);
1414 --
1415 hr_utility.trace('IN EXCEPTION IN create_next_period');
1416 --
1417 end if;
1418 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_next_period',
1419 itemtype, itemkey, to_char(actid), funcmode);
1420 raise;
1421 result := '';
1422 return;
1423
1424 END create_next_period;
1425
1426
1427 ------------------------- get_approval_period_id --------------------------
1428 --
1429 FUNCTION get_approval_period_id(
1430 p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
1431 ,p_time_recipient_id in HXC_TIME_RECIPIENTS.TIME_RECIPIENT_ID%TYPE
1432 ,p_day_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
1433 ,p_timecard_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
1434 ,p_timecard_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
1435 )
1436 RETURN HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE
1437 IS
1438
1439 cursor csr_get_app_rec_period(
1440 p_time_recipient_id number,
1441 p_app_periods number)
1442 is
1443 select hapc.recurring_period_id
1444 from hxc_approval_period_comps hapc,
1445 hxc_approval_period_sets haps
1446 where haps.approval_period_set_id = p_app_periods
1447 and hapc.approval_period_set_id = haps.approval_period_set_id
1448 and hapc.time_recipient_id = p_time_recipient_id;
1449
1450 l_app_periods HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE;
1451 l_recurring_period_id HXC_RECURRING_PERIODS.RECURRING_PERIOD_ID%TYPE;
1452 l_day_number NUMBER;
1453 l_day_count NUMBER;
1454 l_pref_found BOOLEAN;
1455 l_day_check DATE;
1456
1457 BEGIN
1458
1459 -- Attempt to find the approval period preference value
1460
1461 BEGIN
1462
1463 l_app_periods := hxc_preference_evaluation.resource_preferences(
1464 p_resource_id => p_resource_id,
1465 p_pref_code => 'TS_PER_APPROVAL_PERIODS',
1466 p_attribute_n => 1,
1467 p_evaluation_date => trunc(p_day_start_time));
1468
1469 EXCEPTION
1470 when others then
1471 --
1472 -- Ok, now we loop over all the days in the timecard period
1473 -- looking for an application period preference
1474 --
1475 l_day_number := trunc(p_timecard_stop_time) - trunc(p_timecard_start_time);
1476 l_day_count := 0;
1477 l_pref_found := false;
1478
1479 LOOP
1480 EXIT WHEN l_day_count > l_day_number;
1481 EXIT WHEN l_pref_found;
1482 l_day_check := trunc(p_timecard_start_time) + l_day_count;
1483 BEGIN
1484
1485 l_app_periods := hxc_preference_evaluation.resource_preferences(
1486 p_resource_id => p_resource_id,
1487 p_pref_code => 'TS_PER_APPROVAL_PERIODS',
1488 p_attribute_n => 1,
1489 p_evaluation_date => l_day_check);
1490
1491 l_pref_found := true;
1492
1493 EXCEPTION
1494 When others then
1495 null;
1496 END;
1497
1498 l_day_count := l_day_count +1;
1499
1500 END LOOP;
1501
1502 if (NOT l_pref_found) then
1503
1504 g_error_count := g_error_count + 1;
1505 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_NO_APRL_PERIOD_PREF';
1506 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
1507 --
1508 FND_MESSAGE.SET_NAME('HXC','HXC_NO_APRL_PERIOD_PREF');
1509 FND_MESSAGE.SET_TOKEN('DATE',FND_DATE.DATE_TO_CANONICAL(p_day_start_time));
1510 FND_MESSAGE.SET_TOKEN('RESOURCE_ID',p_resource_id);
1511 FND_MESSAGE.RAISE_ERROR;
1512
1513 end if;
1514
1515 END;
1516
1517 --
1518 -- Use the application period id to get the recurring period id
1519 --
1520
1521 open csr_get_app_rec_period(p_time_recipient_id,to_number(l_app_periods));
1522 fetch csr_get_app_rec_period into l_recurring_period_id;
1523
1524 if csr_get_app_rec_period%NOTFOUND then
1525 close csr_get_app_rec_period;
1526 g_error_count := g_error_count + 1;
1527 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_REC_PERIOD';
1528 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
1529 --
1530 FND_MESSAGE.SET_NAME('HXC','HXC_APR_NO_REC_PERIOD');
1531 FND_MESSAGE.SET_TOKEN('TIME_RECIPIENT',p_time_recipient_id);
1532 FND_MESSAGE.SET_TOKEN('APP_PERIOD_PREF',l_app_periods);
1533 FND_MESSAGE.RAISE_ERROR;
1534 else
1535 close csr_get_app_rec_period;
1536 end if;
1537
1538 return l_recurring_period_id;
1539
1540 END get_approval_period_id;
1541
1542
1543 PROCEDURE get_application_period(
1544 p_app_period_func IN VARCHAR2
1545 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1546 ,p_day IN hxc_time_building_blocks.start_time%TYPE
1547 ,p_time_recipient IN hxc_time_recipients.time_recipient_id%TYPE
1548 ,p_tc_start_time IN hxc_time_building_blocks.start_time%TYPE
1549 ,p_tc_stop_time IN hxc_time_building_blocks.stop_time%TYPE
1550 ,p_assignment_periods IN hxc_timecard_utilities.periods
1551 ,p_period_start OUT NOCOPY hxc_time_building_blocks.start_time%TYPE
1552 ,p_period_end OUT NOCOPY hxc_time_building_blocks.stop_time%TYPE
1553 )
1554 IS
1555 l_period_start_date date;
1556 l_period_end_date date;
1557 l_override_allowed boolean;
1558 l_app_period hxc_timecard_utilities.time_period;
1559 l_valid_periods hxc_timecard_utilities.periods;
1560
1561
1562 l_call_proc varchar2(2000);
1563 l_cursor number;
1564 l_ret number;
1565
1566 l_rec_period_id number;
1567 l_rec_start_date date;
1568 l_rec_period_type varchar2(80);
1569 l_duration_in_days number(10);
1570
1571 l_proc varchar2(50) := 'get_application_period';
1572
1573 cursor csr_get_rec_period_info(p_recurring_period_id number) is
1574 select hrp.start_date,
1575 hrp.period_type,
1576 hrp.duration_in_days
1577 from hxc_recurring_periods hrp
1578 where hrp.recurring_period_id = p_recurring_period_id;
1579
1580 BEGIN
1581
1582 IF p_app_period_func IS NOT NULL
1583 THEN
1584 if g_debug then
1585 hr_utility.set_location(l_proc, 95);
1586 end if;
1587
1588 l_call_proc := p_app_period_func ||
1589 '(p_building_block_date => ' || p_day ||
1590 ',p_resource_id => ' || p_resource_id ||
1591 ',p_period_start_date => l_period_start_date' ||
1592 ',p_period_end_date => l_period_end_date' ||
1593 ',p_override_allowed => l_override_allowed)';
1594
1595 if g_debug then
1596 hr_utility.trace('Period Start Date (from function) is : ' ||
1597 to_char(l_period_start_date, 'DD-MM-YYYY'));
1598 hr_utility.trace('Period End Date (from function) is : ' ||
1599 to_char(l_period_end_date, 'DD-MM-YYYY'));
1600 end if;
1601
1602 l_cursor := dbms_sql.open_cursor;
1603 dbms_sql.parse(l_cursor, l_call_proc, DBMS_SQL.V7);
1604 l_ret := dbms_sql.execute(l_cursor);
1605 dbms_sql.close_cursor(l_cursor);
1606
1607 if g_debug then
1608 hr_utility.set_location(l_proc, 110);
1609 end if;
1610 ELSE
1611
1612 l_override_allowed := TRUE;
1613
1614 END IF;
1615
1616 -- If override allowed then, get application period start and
1617 -- end dates.
1618
1619 IF l_override_allowed
1620 THEN
1621
1622 if g_debug then
1623 hr_utility.set_location(l_proc, 120);
1624 end if;
1625
1626 l_rec_period_id := get_approval_period_id(
1627 p_resource_id
1628 ,p_time_recipient
1629 ,p_day
1630 ,p_tc_start_time
1631 ,p_tc_stop_time
1632 );
1633
1634 if g_debug then
1635 hr_utility.trace('Recurring Period ID is : ' || to_char(l_rec_period_id));
1636 end if;
1637
1638 open csr_get_rec_period_info(l_rec_period_id);
1639 fetch csr_get_rec_period_info into l_rec_start_date,
1640 l_rec_period_type,
1641 l_duration_in_days;
1642 close csr_get_rec_period_info;
1643
1644 hxc_timecard_utilities.find_current_period(
1645 p_rec_period_start_date => l_rec_start_date
1646 ,p_period_type => l_rec_period_type
1647 ,p_duration_in_days => l_duration_in_days
1648 ,p_current_date => p_day
1649 ,p_period_start => l_period_start_date
1650 ,p_period_end => l_period_end_date
1651 );
1652
1653 if g_debug then
1654 hr_utility.trace('Appl Period Start Date is : ' ||
1655 to_char(l_period_start_date, 'DD-MM-YYYY'));
1656 hr_utility.trace('Appl Period End Date is : ' ||
1657 to_char(l_period_end_date, 'DD-MM-YYYY'));
1658 end if;
1659
1660 END IF;
1661
1662 p_period_start := l_period_start_date;
1663 p_period_end := l_period_end_date;
1664
1665 -- JOEL
1666 --processing assignment to remove days without an active assignment
1667 l_app_period.start_date := l_period_start_date;
1668 l_app_period.end_date := l_period_end_date;
1669
1670 l_valid_periods.delete;
1671
1672 hxc_timecard_utilities.process_assignments(
1673 p_period => l_app_period
1674 ,p_assignment_periods => p_assignment_periods
1675 ,p_return_periods => l_valid_periods
1676 );
1677
1678 FOR i IN l_valid_periods.first .. l_valid_periods.last
1679 LOOP
1680 IF p_day BETWEEN l_valid_periods(i).start_date
1681 AND l_valid_periods(i).end_date
1682 THEN
1683 p_period_start := l_valid_periods(i).start_date;
1684 p_period_end := l_valid_periods(i).end_date;
1685
1686 EXIT;
1687 END IF;
1688 END LOOP;
1689 -- JOEL
1690
1691 END get_application_period;
1692
1693
1694 FUNCTION get_rest_detail_blocks(
1695 p_detail_blocks IN block_table
1696 )
1697 RETURN NUMBER
1698 IS
1699
1700 l_block_index NUMBER;
1701 l_block_count NUMBER := 0;
1702
1703 BEGIN
1704 IF p_detail_blocks.count = 0
1705 THEN
1706 RETURN 0;
1707 END IF;
1708 FOR l_block_index in p_detail_blocks.first .. p_detail_blocks.last LOOP
1709 IF p_detail_blocks(l_block_index).added <> 'Y'
1710 THEN
1711 l_block_count := l_block_count + 1;
1712 END IF;
1713 END LOOP;
1714
1715 RETURN l_block_count;
1716
1717 END;
1718
1719
1720 FUNCTION has_details(
1721 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
1722 )
1723 RETURN BOOLEAN
1724 IS
1725 CURSOR c_details(
1726 p_app_id IN hxc_time_building_blocks.time_building_block_id%TYPE
1727 )
1728 IS
1729 SELECT details.time_building_block_id
1730 FROM hxc_ap_detail_links details
1731 ,hxc_time_building_blocks blocks
1732 WHERE details.application_period_id = p_app_id
1733 AND details.time_building_block_id = blocks.time_building_block_id
1734 AND details.time_building_block_ovn = blocks.object_version_number
1735 AND blocks.date_to = hr_general.end_of_time;
1736
1737 l_detail_id hxc_time_building_blocks.time_building_block_id%TYPE;
1738 BEGIN
1739 OPEN c_details(p_app_id);
1740 FETCH c_details INTO l_detail_id;
1741 IF c_details%NOTFOUND
1742 THEN
1743 CLOSE c_details;
1744
1745 RETURN FALSE;
1746 END IF;
1747
1748 CLOSE c_details;
1749 RETURN TRUE;
1750 END has_details;
1751
1752 Procedure get_detail_links(p_app_id in hxc_time_building_blocks.time_building_block_id%TYPE,
1753 p_timecard_id in hxc_time_building_blocks.time_building_block_id%TYPE,
1754 p_blocks out nocopy block_table )
1755 IS
1756 Cursor c_detail_links IS
1757 select time_building_block_id, time_building_block_ovn
1758 from hxc_ap_detail_links
1759 where application_period_id = p_app_id
1760 and time_building_block_id
1761 not in ( select detail.time_building_block_id
1762 from hxc_time_building_blocks detail,
1763 hxc_time_building_blocks day
1764 where detail.parent_building_block_id = day.time_building_block_id
1765 and detail.parent_building_block_ovn = day.object_version_number
1766 and day.scope = 'DAY'
1767 and detail.scope = 'DETAIL'
1768 and day.parent_building_block_id = p_timecard_id
1769 );
1770
1771
1772 l_block_index BINARY_INTEGER;
1773 BEGIN
1774 l_block_index := 1;
1775
1776 Open c_detail_links;
1777 Loop
1778 Fetch c_detail_links into p_blocks(l_block_index).block_id,
1779 p_blocks(l_block_index).block_ovn;
1780 Exit when c_detail_links%notfound;
1781 l_block_index := l_block_index + 1;
1782 End Loop;
1783 Close c_detail_links;
1784 End get_detail_links;
1785
1786 Procedure create_removed_links(p_removed_blocks block_table,
1787 p_app_id hxc_time_building_blocks.time_building_block_id%TYPE)
1788
1789 IS
1790 Cursor c_detail_exists (p_app_id hxc_time_building_blocks.time_building_block_id%TYPE,
1791 p_time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE
1792 ) is
1793 select 1
1794 from hxc_ap_detail_links
1795 where application_period_id = p_app_id
1796 and time_building_block_id = p_time_building_block_id;
1797
1798 l_block_index BINARY_INTEGER;
1799 l_dummy PLS_INTEGER;
1800 BEGIN
1801
1802 l_block_index := p_removed_blocks.first;
1803
1804 LOOP
1805 EXIT WHEN NOT p_removed_blocks.exists(l_block_index);
1806
1807
1808 open c_detail_exists(p_app_id,
1809 p_removed_blocks(l_block_index).block_id
1810 );
1811
1812 fetch c_detail_exists into l_dummy;
1813
1814 if c_detail_exists%notfound then
1815 --insert a line in hxc_detail_summary;
1816 hxc_ap_detail_links_pkg.insert_summary_row(
1817 p_app_id
1818 ,p_removed_blocks(l_block_index).block_id
1819 ,p_removed_blocks(l_block_index).block_ovn
1820 );
1821 end if;
1822 close c_detail_exists;
1823
1824 l_block_index := p_removed_blocks.next(l_block_index);
1825
1826 END LOOP;
1827
1828 End create_removed_links;
1829
1830 FUNCTION item_attribute_exists
1831 (p_item_type in wf_items.item_type%type,
1832 p_item_key in wf_item_activity_statuses.item_key%type,
1833 p_name in wf_item_attribute_values.name%type)
1834 return boolean is
1835
1836 l_dummy varchar2(1);
1837
1838 BEGIN
1839
1840 select 'Y'
1841 into l_dummy
1842 from wf_item_attribute_values
1843 where item_type = p_item_type
1844 and item_key = p_item_key
1845 and name = p_name;
1846
1847 return true;
1848
1849 Exception
1850 When others then
1851 return false;
1852
1853 END item_attribute_exists;
1854
1855
1856 PROCEDURE generate_app_period(
1857 p_item_type IN wf_item_types.name%type
1858 ,p_item_key IN wf_item_attribute_values.item_key%type
1859 ,p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
1860 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1861 ,p_start_time IN hxc_time_building_blocks.start_time%TYPE
1862 ,p_stop_time IN hxc_time_building_blocks.stop_time%TYPE
1863 ,p_time_recipient_id IN hxc_time_recipients.time_recipient_id%TYPE
1864 ,p_recipient_sequence IN hxc_approval_comps.approval_order%TYPE
1865 ,p_approval_comp IN approval_comp
1866 ,p_tc_resubmitted IN VARCHAR2
1867 -- ,p_first IN VARCHAR2
1868 ,p_detail_blocks IN OUT NOCOPY block_table
1869 ,p_detail_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
1870 )
1871 IS
1872
1873 CURSOR c_app_period(
1874 p_resource_id hxc_time_building_blocks.resource_id%TYPE
1875 ,p_start_time hxc_time_building_blocks.start_time%TYPE
1876 ,p_stop_time hxc_time_building_blocks.stop_time%TYPE
1877 ,p_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE
1878 ,p_recipient_sequence IN hxc_approval_comps.approval_order%TYPE
1879 ,p_time_category_id hxc_time_categories.time_category_id%TYPE
1880 ,p_category_sequence hxc_approval_comps.approval_order%TYPE
1881 )
1882 IS
1883 SELECT application_period_id
1884 ,application_period_ovn
1885 ,approval_status
1886 ,notification_status
1887 ,approval_comp_id
1888 FROM hxc_app_period_summary
1889 WHERE resource_id = p_resource_id
1890 AND start_time = p_start_time
1891 AND stop_time = p_stop_time
1892 AND time_recipient_id = p_time_recipient_id
1893 AND recipient_sequence = p_recipient_sequence
1894 AND NVL(time_category_id, -1) = NVL(p_time_category_id, -1)
1895 AND NVL(category_sequence, -1) = NVL(p_category_sequence, -1)
1896 --following added may12 for hr supervisor
1897 ORDER BY application_period_id asc;
1898
1899 CURSOR c_tc_ap_link(
1900 p_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
1901 ,p_app_period_id hxc_time_building_blocks.time_building_block_id%TYPE
1902 )
1903 IS
1904 SELECT 'Y'
1905 FROM hxc_tc_ap_links
1906 WHERE timecard_id = p_timecard_id
1907 AND application_period_id = p_app_period_id;
1908
1909 cursor c_previous_actioner(
1910 p_app_period_id in hxc_app_period_summary.application_period_id%type) is
1911 select approver_id
1912 from hxc_app_period_summary
1913 where application_period_id = p_app_period_id;
1914
1915 CURSOR c_get_detail_blocks(p_application_period_id in hxc_time_building_blocks.time_building_block_id%type)
1916 is
1917 select adl.time_building_block_id,
1918 adl.time_building_block_ovn
1919 from hxc_ap_detail_links adl
1920 where adl.application_period_id = p_application_period_id;
1921
1922 cursor get_max_ovn(p_bb_id in hxc_time_building_blocks.time_building_block_id%type)
1923 is
1924 select max(object_version_number)
1925 from hxc_time_building_blocks
1926 where time_building_block_id = p_bb_id;
1927
1928 cursor get_item_key(p_bb_id in number)
1929 is
1930 select approval_item_key
1931 from hxc_app_period_summary
1932 where application_period_id = p_bb_id;
1933
1934
1935 l_app_id hxc_time_building_blocks.time_building_block_id%TYPE := NULL;
1936 l_app_ovn hxc_time_building_blocks.object_version_number%TYPE := NULL;
1937 l_approval_status hxc_time_building_blocks.approval_status%TYPE := NULL;
1938 l_notification_status VARCHAR2(150) := NULL;
1939 l_app_comp_id hxc_app_period_summary.approval_comp_id%type;
1940 l_app_id_temp hxc_time_building_blocks.time_building_block_id%TYPE := NULL;
1941 l_app_ovn_temp hxc_time_building_blocks.object_version_number%TYPE := NULL;
1942 l_app_status_temp hxc_time_building_blocks.approval_status%TYPE := NULL;
1943 l_notif_status_temp VARCHAR2(150) := NULL;
1944 l_app_comp_id_temp hxc_app_period_summary.approval_comp_id%type;
1945 l_first_app_period BOOLEAN;
1946 l_time_category_id hxc_time_categories.time_category_id%TYPE := NULL;
1947 l_category_sequence hxc_app_period_summary.category_sequence%TYPE := NULL;
1948 l_creation_date DATE;
1949 t_attributes hxc_time_attributes_api.timecard;
1950 l_tc_ap_link_exists VARCHAR2(50) := NULL;
1951 l_app_exists BOOLEAN;
1952 l_removed_blocks block_table;
1953
1954 l_is_empty Boolean := true;
1955
1956 l_dummy number;
1957 l_number_of_details number;
1958 i number;
1959 l_max_ovn number;
1960 l_item_key number ;
1961 l_blank varchar2(2) := 'N';
1962 type rec_type is record(p_id hxc_time_building_blocks.time_building_block_id%TYPE,
1963 p_ovn hxc_time_building_blocks.time_building_block_id%TYPE);
1964
1965
1966 TYPE tab_type IS TABLE OF rec_type INDEX BY BINARY_INTEGER;
1967
1968 l_tab_type_a tab_type;
1969 l_proc VARCHAR2(100) := g_package || 'generate_app_period';
1970 BEGIN
1971 g_debug := true;
1972 g_trace := l_proc || '10';
1973 l_item_key := null;
1974 if g_debug then
1975 hr_utility.trace('start generating period');
1976 end if;
1977
1978 l_time_category_id := p_approval_comp.time_category_id;
1979
1980 IF l_time_category_id IS NULL
1981 THEN
1982 l_category_sequence := NULL;
1983 ELSE
1984 l_category_sequence := p_approval_comp.approval_order;
1985 END IF;
1986
1987
1988 OPEN c_app_period(
1989 p_resource_id
1990 ,p_start_time
1991 ,p_stop_time
1992 ,p_time_recipient_id
1993 ,p_recipient_sequence
1994 ,l_time_category_id
1995 ,l_category_sequence
1996 );
1997
1998 l_first_app_period := TRUE;
1999
2000 LOOP
2001
2002 FETCH c_app_period INTO l_app_id_temp,
2003 l_app_ovn_temp,
2004 l_app_status_temp,
2005 l_notif_status_temp,
2006 l_app_comp_id_temp;
2007
2008 EXIT WHEN c_app_period%NOTFOUND;
2009
2010 hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2011 hr_utility.trace('|--------------------------------------------------------------------|');
2012 hr_utility.trace('| Application Period Id:'||l_app_id_temp||lpad('|',(68-(length(' Application Period Id:')+length(to_char(l_app_id_temp))))));
2013 hr_utility.trace('| Application Period Ovn:'||l_app_ovn_temp||lpad('|',(68-(length(' Application Period Ovn:')+length(to_char(l_app_ovn_temp))))));
2014 hr_utility.trace('| Application Period Status:'||l_app_status_temp||lpad('|',(68-(length(' Application Period Status:')+length(to_char(l_app_status_temp))))));
2015 hr_utility.trace('| Time Category Id:'||l_time_category_id||lpad('|',(68-(length(' Time Category Id:')+length(to_char(l_time_category_id))))));
2016 hr_utility.trace('|--------------------------------------------------------------------|');
2017 hr_utility.trace('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2018
2019 --for hr supervisor, end date all the later supervisor's app periods
2020 --only keep the first supervisor's app period. we will regenereate new
2021 --app periods for later supervisors
2022 --Here we also delete app period for default approval style when the new
2023 --timecard doesn't have any block for default approval style.
2024 IF l_first_app_period
2025 THEN
2026
2027 l_app_id := l_app_id_temp;
2028 l_app_ovn := l_app_ovn_temp;
2029 l_approval_status := l_app_status_temp;
2030 l_notification_status := l_notif_status_temp;
2031 l_app_comp_id := l_app_comp_id_temp;
2032
2033 l_first_app_period := FALSE;
2034
2035 ELSE
2036 --should also end date them from hxc_time_building_blocks
2037 --remove them from hxc_app_period_summary
2038 --from hxc_tc_ap_links and hxc_ap_detail_links
2039 hxc_app_period_summary_api.app_period_delete(l_app_id_temp);
2040
2041 END IF;
2042
2043 END LOOP;
2044
2045 CLOSE c_app_period;
2046 hr_utility.trace('l_time_category:'||l_time_category_id);
2047
2048 --
2049 -- If this is a new application period, and has no associated details
2050 -- do not create it. However, if it has been previously created,
2051 -- we must resend, even if empty, to ensure ELA timecards can be
2052 -- approved. 115.87
2053 -- 115.91 Change: We have some competing requirements here for
2054 -- Project Manager verses the rest of ELA. Making this logic
2055 -- a bit clearer.
2056 IF l_time_category_id IS NOT NULL then
2057 -- This is an ELA application period
2058 IF l_time_category_id <> 0 then
2059 l_is_empty := is_empty(p_detail_blocks,p_detail_attributes,l_time_category_id);
2060 -- This is not the default ELA approval style
2061 IF l_is_empty then
2062 -- There are no details associated with this application period
2063 IF l_app_id is null then
2064 -- This is a new application, do not generate a new, empty
2065 -- application period.
2066 hr_utility.trace('Return(0)');
2067 RETURN;
2068 END IF;
2069 IF ((l_app_id is not null)
2070 AND (l_app_ovn = 1)
2071 AND (l_notification_status = c_notified OR l_notification_status = c_not_notified )) then
2072 -- Application period is not new, but the approver has not seen it
2073 hr_utility.trace('Return(1)');
2074 RETURN;
2075 END IF;
2076 END IF;
2077 END IF;
2078 END IF;
2079
2080 get_detail_links(l_app_id, p_timecard_id, l_removed_blocks);
2081
2082 --if this timecard doesn't have any block for default style,
2083 --delete existing app period if any, and do nothing.
2084 IF l_time_category_id = 0
2085 AND p_detail_blocks.count > 0
2086 THEN
2087
2088 l_is_empty := false; -- Bug 5640516.
2089
2090 IF get_rest_detail_blocks(p_detail_blocks) = 0
2091 THEN
2092 IF l_app_id IS NOT NULL
2093 THEN
2094 --check to see if all the detail blocks associated with this
2095 --app period have been deleted. If so, delete this app period.
2096 --otherwise, it means, this app period still has details from
2097 --other timecards, can't delete the app period
2098 IF NOT has_details(l_app_id)
2099 THEN
2100 hxc_app_period_summary_api.app_period_delete(l_app_id);
2101 END IF;
2102 END IF;
2103
2104 hr_utility.trace('Return(2)');
2105 RETURN;
2106 END IF;
2107 END IF;
2108 --
2109 -- 115.93 Change: Include check that the approval component id
2110 -- is the same in the statement below, otherwise we will surely
2111 -- need to regenerate the application period. Bug 4302997.
2112 --
2113 hr_utility.trace('l_app_id:'||l_app_id);
2114 hr_utility.trace('l_approval_status:'||l_approval_status);
2115 hr_utility.trace('Approval Comp Comparison:'||l_app_comp_id||','||p_approval_comp.approval_comp_id);
2116
2117 if(changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id)) then
2118 hr_utility.trace('changed is true');
2119 else
2120 hr_utility.trace('changed is false');
2121 end if;
2122
2123 select count(*) into l_number_of_details
2124 from hxc_ap_detail_links
2125 where application_period_id = l_app_id;
2126
2127 hr_utility.trace('Count for '||l_app_id||' is:'||l_number_of_details);
2128
2129
2130 IF l_app_id IS NOT NULL
2131 AND p_approval_comp.approval_comp_id = l_app_comp_id
2132 AND (NOT changed( p_detail_blocks,p_detail_attributes,l_time_category_id
2133 ,l_app_id, p_timecard_id))
2134 THEN
2135 --
2136 -- 115.107: An empty complete period should not be reattached to the
2137 -- timecard.
2138 --
2139 if(l_is_empty) then
2140 --
2141 -- 115.115 - Bug 5182298
2142 -- Now have two possibliities:
2143 -- 1. The app period is empty now, and it was before, in which case
2144 -- we must just return and do nothing (discard this application period)
2145 -- 2. The app period is empty now, but it wasn't before, in which case
2146 -- we should continue and generate the notification / app period
2147
2148 if(l_number_of_details = 0 AND l_approval_status <> 'SUBMITTED') then
2149 -- it is case 1
2150 hr_utility.trace('Return(3) - empty actioned App Period - do nothing');
2151 RETURN;
2152 else
2153 -- it is case 2
2154 hr_utility.trace('Return(3.5) - newly empty period - notify previous approver');
2155 open get_item_key(l_app_id);
2156 fetch get_item_key into l_item_key;
2157 close get_item_key;
2158
2159 If l_item_key is not null then
2160
2161 if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2162 wf_engine.SetItemAttrText(
2163 itemtype => 'HXCEMP',
2164 itemkey => l_item_key,
2165 aname => 'IS_DIFF_TC',
2166 avalue => 'Y');
2167 else
2168 wf_engine.additemattr
2169 (itemtype => 'HXCEMP',
2170 itemkey => l_item_key,
2171 aname => 'IS_DIFF_TC',
2172 text_value => 'Y');
2173 end if;
2174
2175 end if;
2176
2177 l_blank := 'Y';
2178 null;
2179 end if;
2180
2181 else
2182 IF l_approval_status <> 'SUBMITTED' THEN
2183 --
2184 -- 115.76 Change. Ensure this link is created using the normal
2185 -- interface, not this one which is internal.
2186 -- hxc_tc_ap_links_pkg.insert_summary_row(p_timecard_id, l_app_id);
2187 hxc_tc_ap_links_pkg.create_app_period_links(l_app_id);
2188 --
2189 -- 115.91 Change: At this point we also ensure the timecard
2190 -- status is reevaluated so that if a period is not sent due
2191 -- to other changes in the timecard, the timecard has the
2192 -- appropriate status.
2193 --
2194 hxc_timecard_summary_api.reevaluate_timecard_statuses(l_app_id);
2195 hr_utility.trace('Return(4) - populated actioned App period - no changes. Use, but do not renotify');
2196 RETURN;
2197 END IF;
2198 end if;
2199 END IF;
2200 g_block_exist_for_ap := 'N';
2201
2202 IF changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id) AND
2203 l_number_of_details <> 0 AND no_blocks(l_app_id, p_timecard_id) = l_number_of_details
2204 and g_block_exist_for_ap <> 'Y' THEN
2205
2206 open get_item_key(l_app_id);
2207 fetch get_item_key into l_item_key;
2208 close get_item_key;
2209
2210 If l_item_key is not null then
2211 if(item_attribute_exists('HXCEMP',l_item_key,'IS_DIFF_TC')) then
2212 wf_engine.SetItemAttrText(
2213 itemtype => 'HXCEMP',
2214 itemkey => l_item_key,
2215 aname => 'IS_DIFF_TC',
2216 avalue => 'Y');
2217 else
2218 wf_engine.additemattr
2219 (itemtype => 'HXCEMP',
2220 itemkey => l_item_key,
2221 aname => 'IS_DIFF_TC',
2222 text_value => 'Y');
2223 end if;
2224 end if;
2225
2226 l_blank := 'Y';
2227 END IF;
2228
2229
2230 IF l_blank = 'Y' THEN
2231
2232 open c_get_detail_blocks(l_app_id);
2233 fetch c_get_detail_blocks bulk collect INTO l_tab_type_a;
2234 close c_get_detail_blocks;
2235
2236 END IF;
2237
2238 IF l_app_id IS NOT NULL
2239 THEN
2240 remove_ap_detail_links(l_app_id, p_timecard_id);
2241 END IF;
2242
2243 --jxtan: when modifying detail blocks and resubmit the timecard,
2244 --p_tc_resubmitted is set to NO in deposit wrapper. Need to ask
2245 --andrew. For now added the extra logic here to deal with the scenario:
2246 --a timecard is approved, but resubmitted. in this case, update the ovn
2247 --of the application period
2248 IF l_app_id IS NULL -- no row
2249 OR (l_app_id IS NOT NULL AND l_approval_status <> 'SUBMITTED')
2250 THEN
2251 if(l_app_id is not null) then
2252 --
2253 -- Keep the previous approver, in case we need it!
2254 -- 115.92 Change
2255 --
2256 l_dummy := hxc_approval_wf_util.keep_previous_approver
2257 (p_item_type,
2258 p_item_key,
2259 l_app_id
2260 );
2261 end if;
2262
2263 g_trace := l_proc || '30';
2264
2265 if g_debug then
2266 hr_utility.trace('Generate it!');
2267 end if;
2268
2269 t_attributes := get_empty_attribute;
2270
2271 hxc_deposit_process_pkg.execute_deposit_process(
2272 p_process_name => g_process_name
2273 ,p_source_name => g_source_name
2274 ,p_effective_date => trunc(sysdate)
2275 ,p_type => 'RANGE'
2276 ,p_measure => null
2277 ,p_unit_of_measure => null
2278 ,p_start_time => trunc(p_start_time)
2279 ,p_stop_time => trunc(p_stop_time)
2280 ,p_parent_building_block_id => null
2281 ,p_parent_building_block_ovn => null
2282 ,p_scope => 'APPLICATION_PERIOD'
2283 ,p_approval_style_id => NULL
2284 ,p_approval_status => 'SUBMITTED'
2285 ,p_resource_id => p_resource_id
2286 ,p_resource_type => g_resource_type
2287 ,p_comment_text => null
2288 ,p_timecard => t_attributes
2289 ,p_time_building_block_id => l_app_id
2290 ,p_object_version_number => l_app_ovn
2291 );
2292
2293 g_trace := l_proc || '40';
2294 if g_debug then
2295 hr_utility.trace('Generated the period');
2296 hr_utility.trace('app_id=' || l_app_id);
2297 hr_utility.trace('app_ovn=' || l_app_ovn);
2298 hr_utility.trace('Populating hxc_app_period_summary');
2299 end if;
2300 l_creation_date := get_creation_date(l_app_id, l_app_ovn);
2301
2302 g_trace := l_proc || '80';
2303
2304 --populate hxc_app_period_summary with the new row
2305 hxc_app_period_summary_api.app_period_create(
2306 p_application_period_id => l_app_id
2307 ,p_application_period_ovn => l_app_ovn
2308 ,p_approval_status => 'SUBMITTED'
2309 ,p_time_recipient_id => p_time_recipient_id
2310 ,p_time_category_id => p_approval_comp.time_category_id
2311 ,p_start_time => p_start_time
2312 ,p_stop_time => p_stop_time
2313 ,p_resource_id => p_resource_id
2314 ,p_recipient_sequence => p_recipient_sequence
2315 ,p_category_sequence => l_category_sequence
2316 ,p_creation_date => l_creation_date
2317 ,p_notification_status => 'NOT_NOTIFIED'
2318 ,p_approver_id => NULL
2319 ,p_approval_comp_id => p_approval_comp.approval_comp_id
2320 ,p_approval_item_key => l_item_key
2321 );
2322
2323 g_trace := l_proc || '90';
2324
2325 ELSE
2326
2327 IF l_notification_status <> 'NOT_NOTIFIED' THEN
2328 -- don't create a new application period, but need to change status
2329 UPDATE hxc_app_period_summary
2330 SET notification_status = 'NOT_NOTIFIED'
2331 ,approval_comp_id = p_approval_comp.approval_comp_id
2332 WHERE application_period_id = l_app_id;
2333 elsif(p_approval_comp.approval_comp_id <> l_app_comp_id) then
2334 -- do not create a new application period, but ensure the
2335 -- correct approval component id is used.
2336 UPDATE hxc_app_period_summary
2337 SET approval_comp_id = p_approval_comp.approval_comp_id
2338 WHERE application_period_id = l_app_id;
2339 END IF;
2340
2341 END IF;
2342
2343 if g_debug then
2344 hr_utility.trace('Populating hxc_ap_detail_links');
2345 end if;
2346 --populate hxc_ap_detail_links
2347 IF l_time_category_id IS NULL
2348 OR l_time_category_id = 0
2349 THEN
2350 g_trace := l_proc || '100';
2351
2352 if g_debug then
2353 hr_utility.trace('Populating all');
2354 end if;
2355 link_ap_details_all(
2356 p_detail_blocks => p_detail_blocks
2357 ,p_app_id => l_app_id
2358 ,p_time_category_id => l_time_category_id
2359 );
2360
2361 ELSE
2362 g_trace := l_proc || '110';
2363
2364 if g_debug then
2365 hr_utility.trace('Populating time category : ' || l_time_category_id );
2366 end if;
2367 link_ap_details(
2368 p_detail_blocks => p_detail_blocks
2369 ,p_attributes => p_detail_attributes
2370 ,p_time_category_id => l_time_category_id
2371 ,p_app_id => l_app_id
2372 );
2373
2374 g_trace := l_proc || '120';
2375 END IF;
2376
2377 create_removed_links(l_removed_blocks, l_app_id);
2378
2379 IF l_blank = 'Y' THEN
2380
2381 FOR i IN l_tab_type_a.first..l_tab_type_a.last LOOP
2382
2383 open get_max_ovn(l_tab_type_a(i).p_id);
2384 fetch get_max_ovn into l_max_ovn;
2385 close get_max_ovn;
2386
2387 hxc_ap_detail_links_pkg.insert_summary_row(l_app_id, l_tab_type_a(i).p_id, l_max_ovn);
2388
2389 END LOOP;
2390 END IF;
2391
2392 g_trace := l_proc || '130';
2393
2394
2395 if g_debug then
2396 hr_utility.trace('Populating hxc_tc_ap_links');
2397 end if;
2398 --populate hxc_tc_ap_links
2399
2400 OPEN c_tc_ap_link(p_timecard_id, l_app_id);
2401 FETCH c_tc_ap_link INTO l_tc_ap_link_exists;
2402 CLOSE c_tc_ap_link;
2403
2404 g_trace := l_proc || '160';
2405
2406 IF l_tc_ap_link_exists IS NULL
2407 THEN
2408 g_trace := l_proc || '170';
2409 --
2410 -- 115.76 Change. It is ok to leave this call, since
2411 -- the link is explicitly checked not to exist in the
2412 -- first place.
2413 --
2414 hxc_tc_ap_links_pkg.insert_summary_row(
2415 p_timecard_id => p_timecard_id
2416 ,p_application_period_id => l_app_id
2417 );
2418 --
2419 --115.118, Bug - 5554020
2420 --
2421 hxc_timecard_summary_api.reevaluate_timecard_statuses(l_app_id);
2422
2423 g_trace := l_proc || '180';
2424 END IF;
2425
2426 if g_debug then
2427 hr_utility.trace('End generating app period');
2428 end if;
2429
2430 EXCEPTION
2431 WHEN OTHERS THEN
2432 RAISE;
2433
2434 END generate_app_period;
2435
2436
2437
2438 ------------------------- get_approval_style_id ----------------------------
2439 --
2440 FUNCTION get_approval_style_id(p_period_start_date date,
2441 p_period_end_date date,
2442 p_resource_id number) RETURN NUMBER IS
2443 --
2444 -- Andrew: Bug 3211251: Use date_to = end of time filter, instead of
2445 -- max ovn sub-query. Faster, and avoids picking up approval styles
2446 -- from deleted timecards.
2447 --
2448 -- Andrew: Bug 4178239: This cursor could previously pick up the
2449 -- approval styles associated with templates created in the same
2450 -- week as the timecard being approved. This could lead to the
2451 -- incorrect approval style being used. Thus, check the day
2452 -- driving the style is attached to an active timecard before
2453 -- choosing that style.
2454 --
2455 cursor csr_get_appr_style is
2456 SELECT day1.approval_style_id
2457 FROM hxc_time_building_blocks day1,
2458 hxc_time_building_blocks timecard
2459 WHERE day1.resource_id = p_resource_id
2460 AND day1.scope = 'DAY'
2461 AND day1.start_time BETWEEN p_period_start_date AND p_period_end_date
2462 AND day1.date_to = hr_general.end_of_time
2463 AND timecard.time_building_block_id = day1.parent_building_block_id
2464 AND timecard.object_version_number = day1.parent_building_block_ovn
2465 AND timecard.scope = 'TIMECARD'
2466 AND timecard.date_to = hr_general.end_of_time
2467 ORDER BY day1.start_time desc;
2468 --
2469 l_approval_style_id number;
2470 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.get_approval_style_id';
2471 --
2472 BEGIN
2473 --
2474 if g_debug then
2475 hr_utility.set_location(l_proc, 10);
2476 end if;
2477 --
2478 open csr_get_appr_style;
2479 fetch csr_get_appr_style into l_approval_style_id;
2480 IF csr_get_appr_style%NOTFOUND THEN
2481 --
2482 if g_debug then
2483 hr_utility.set_location(l_proc, 20);
2484 end if;
2485 --
2486 g_error_count := g_error_count + 1;
2487 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPR_STYLE';
2488 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2489 --
2490 hr_utility.set_message(809, 'HXC_APR_NO_APPR_STYLE');
2491 hr_utility.raise_error;
2492 --
2493 END IF;
2494 --
2495 close csr_get_appr_style;
2496 --
2497 RETURN(l_approval_style_id);
2498 --
2499 END get_approval_style_id;
2500
2501 Function dayHasActiveAssignment
2502 (p_assignment_periods in hxc_timecard_utilities.periods,
2503 p_day_start in date) return boolean is
2504 l_asg_index binary_integer;
2505 l_found boolean;
2506 Begin
2507 l_asg_index := p_assignment_periods.first;
2508 l_found := false;
2509 Loop
2510 Exit when (not p_assignment_periods.exists(l_asg_index) OR l_found);
2511 if(trunc(p_day_start) between trunc(p_assignment_periods(l_asg_index).start_date)
2512 and trunc(p_assignment_periods(l_asg_index).end_date)) then
2513 l_found := true;
2514 end if;
2515 l_asg_index := p_assignment_periods.next(l_asg_index);
2516 End Loop;
2517 return l_found;
2518 End dayHasActiveAssignment;
2519
2520 PROCEDURE create_appl_period_info(itemtype IN varchar2,
2521 itemkey IN varchar2,
2522 actid IN number,
2523 funcmode IN varchar2,
2524 result IN OUT NOCOPY varchar2) is
2525 --
2526 cursor csr_get_app_set_from_tc
2527 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
2528 p_timecard_ovn in hxc_time_building_blocks.object_version_number%type) is
2529 select to_char(application_set_id)
2530 from hxc_time_building_blocks
2531 where time_building_block_id = p_timecard_id
2532 and object_version_number = p_timecard_ovn;
2533 --
2534 cursor csr_get_tc_info(p_bld_blk_id number,
2535 p_ovn number) is
2536 select tc.resource_id, tc.start_time, tc.stop_time
2537 from hxc_time_building_blocks tc
2538 where tc.time_building_block_id = p_bld_blk_id
2539 and tc.object_version_number = p_ovn;
2540 --
2541 cursor csr_get_apps(p_app_set varchar2) is
2542 select htr.name,
2543 htr.application_id,
2544 htr.application_period_function,
2545 htr.time_recipient_id
2546 from hxc_application_sets_v has,
2547 hxc_application_set_comps_v hasc,
2548 hxc_time_recipients htr
2549 where to_char(has.application_set_id) = p_app_set
2550 and hasc.application_set_id = has.application_set_id
2551 and hasc.time_recipient_id = htr.time_recipient_id;
2552 --
2553 cursor csr_get_days(p_tc_bld_blk_id number,
2554 p_tc_ovn number) is
2555 select day.time_building_block_id,
2556 day.start_time,
2557 day.stop_time,
2558 day.object_version_number
2559 from hxc_time_building_blocks day
2560 where day.parent_building_block_id = p_tc_bld_blk_id
2561 and day.parent_building_block_ovn = p_tc_ovn
2562 and day.scope = 'DAY'
2563 and day.object_version_number = (select max(day2.object_version_number)
2564 from hxc_time_building_blocks day2
2565 where day.time_building_block_id =
2566 day2.time_building_block_id)
2567 order by 2;
2568 --
2569 ------ Project manager changes
2570 l_detail_project_id NUMBER;
2571 l_tab_project_id hxc_proj_manager_approval_pkg.tab_project_id;
2572
2573 l_index number;
2574 l_index_1 number;
2575 l_index_2 number;
2576 l_index_3 number;
2577 l_no_project_manager number;
2578 l_already_present number;
2579 l_approval_style_id number;
2580 l_original_approval_order number;
2581
2582
2583
2584
2585 l_tc_bld_blk_id number;
2586 l_tc_date_from date;
2587 l_tc_date_to date;
2588 l_tc_ovn number;
2589 l_tc_resubmitted varchar2(10);
2590 --
2591 l_tc_resource_id number;
2592 l_tc_start_time date;
2593 l_tc_stop_time date;
2594 --
2595 l_exist_bb_id number;
2596 l_exist_status varchar2(30);
2597 l_exist_ovn number;
2598 --l_first number;
2599 --
2600 l_app_set varchar2(150);
2601 l_application varchar2(80);
2602 l_application_id number;
2603 l_time_recipient_id number;
2604 l_time_recipient varchar2(150);
2605 l_app_period_func varchar2(240) := NULL;
2606 --
2607
2608 --
2609 l_rec_period_id number;
2610 --
2611 l_day_bld_blk_id number;
2612 l_day_start_time date;
2613 l_day_stop_time date;
2614 l_day_ovn number;
2615 l_appl_period_bb_id number;
2616 l_appl_period_bb_ovn number;
2617 l_period_start_date date;
2618 l_period_end_date date;
2619 l_override_allowed boolean;
2620 t_attributes hxc_time_attributes_api.timecard;
2621 --
2622 l_all_apps varchar2(1000);
2623 l_cnt number;
2624 --
2625 l_exists varchar2(1);
2626 l_chk_days varchar2(1);
2627 l_item_key wf_items.item_key%type;
2628 l_process_name varchar2(30);
2629 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.create_appl_period_info';
2630
2631 --
2632 l_detail_blocks block_table;
2633 l_detail_attributes hxc_self_service_time_deposit.building_block_attribute_info;
2634
2635 l_new_detail_blocks hxc_block_table_type := hxc_block_table_type ();
2636 l_new_detail_attributes hxc_attribute_table_type := hxc_attribute_table_type ();
2637
2638 l_approval_style hxc_approval_styles.approval_style_id%TYPE;
2639 l_assignment_periods hxc_timecard_utilities.periods;
2640
2641
2642 CURSOR c_approval_comp(
2643 p_approval_style hxc_approval_styles.approval_style_id%TYPE
2644 ,p_time_recipient hxc_time_recipients.time_recipient_id%TYPE
2645 )
2646 IS
2647 SELECT approval_comp_id
2648 ,object_version_number
2649 ,approval_mechanism
2650 ,approval_mechanism_id
2651 ,wf_item_type
2652 ,wf_name
2653 ,time_category_id
2654 ,approval_order
2655 FROM hxc_approval_comps
2656 WHERE approval_style_id = p_approval_style
2657 AND time_recipient_id = p_time_recipient;
2658
2659
2660 CURSOR c_ela_comps(
2661 p_comp_id hxc_approval_comps.approval_comp_id%TYPE
2662 ,p_comp_ovn hxc_approval_comps.object_version_number%TYPE
2663 )
2664 IS
2665 SELECT approval_comp_id
2666 ,object_version_number
2667 ,approval_mechanism
2668 ,approval_mechanism_id
2669 ,wf_item_type
2670 ,wf_name
2671 ,time_category_id
2672 ,approval_order
2673 FROM hxc_approval_comps
2674 WHERE parent_comp_id = p_comp_id
2675 AND parent_comp_ovn = p_comp_ovn
2676 ORDER BY time_category_id desc;
2677
2678 cursor c_app_overlap_data_set(l_app_start_date hxc_time_building_blocks.start_time%type,
2679 l_app_stop_date hxc_time_building_blocks.start_time%type,
2680 l_tc_start_time hxc_time_building_blocks.start_time%type )
2681 is
2682 select '1' from hxc_data_sets
2683 where (((l_app_start_date between start_date and end_date) and (l_app_start_date<l_tc_start_time))
2684 or l_app_stop_date between start_date and end_date)
2685 and status in('OFF_LINE','RESTORE_IN_PROGRESS','BACKUP_IN_PROGRESS');
2686
2687 l_approval_comp approval_comp;
2688 l_ela_comp approval_comp;
2689 l_default_comp approval_comp;
2690 l_count NUMBER;
2691 l_dummy NUMBER;
2692
2693 l_processed_app_start hxc_time_building_blocks.start_time%TYPE;
2694 l_processed_app_stop hxc_time_building_blocks.stop_time%TYPE;
2695 l_gen_app_period boolean;
2696
2697 BEGIN
2698
2699 g_debug:=hr_utility.debug_enabled;
2700 g_trace := '10';
2701 if g_debug then
2702 hr_utility.set_location(l_proc, 10);
2703 end if;
2704
2705 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
2706 (itemtype => itemtype,
2707 itemkey => itemkey,
2708 aname => 'TC_BLD_BLK_ID');
2709
2710 if g_debug then
2711 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
2712 end if;
2713 l_tc_ovn := wf_engine.GetItemAttrNumber
2714 (itemtype => itemtype,
2715 itemkey => itemkey,
2716 aname => 'TC_BLD_BLK_OVN');
2717
2718 if g_debug then
2719 hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
2720 end if;
2721
2722 l_tc_resubmitted := wf_engine.GetItemAttrText
2723 (itemtype => itemtype,
2724 itemkey => itemkey,
2725 aname => 'TC_RESUBMITTED');
2726
2727 if g_debug then
2728 hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
2729 end if;
2730
2731 g_trace := '20';
2732
2733 open csr_get_tc_info(l_tc_bld_blk_id,
2734 l_tc_ovn);
2735 fetch csr_get_tc_info into l_tc_resource_id,
2736 l_tc_start_time,
2737 l_tc_stop_time;
2738
2739
2740 IF csr_get_tc_info%NOTFOUND
2741 THEN
2742
2743 g_trace := '30';
2744
2745 g_error_count := g_error_count + 1;
2746 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
2747 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2748
2749 hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
2750 hr_utility.raise_error;
2751 END IF;
2752 g_trace := '40';
2753
2754 close csr_get_tc_info;
2755 -- Bug 4716082, try the application set on the timecard first, not the current preference
2756 open csr_get_app_set_from_tc(l_tc_bld_blk_id,l_tc_ovn);
2757 fetch csr_get_app_set_from_tc into l_app_set;
2758 if((csr_get_app_set_from_tc%notfound)OR(l_app_set is null)) then
2759 close csr_get_app_set_from_tc;
2760 g_trace := '45';
2761 l_app_set := hxc_preference_evaluation.resource_preferences
2762 (p_resource_id => l_tc_resource_id,
2763 p_pref_code => 'TS_PER_APPLICATION_SET',
2764 p_attribute_n => 1);
2765 else
2766 g_trace := '47 -'||l_app_set;
2767 close csr_get_app_set_from_tc;
2768 end if;
2769
2770 g_trace := '50';
2771
2772 open csr_get_apps(l_app_set);
2773 fetch csr_get_apps into l_application,
2774 l_application_id,
2775 l_app_period_func,
2776 l_time_recipient_id;
2777
2778 IF csr_get_apps%NOTFOUND
2779 THEN
2780
2781 g_trace := '60';
2782
2783 g_error_count := g_error_count + 1;
2784 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_APPL_SET_PREF';
2785 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
2786
2787 hr_utility.set_message(809, 'HXC_APR_NO_APPL_SET_PREF');
2788 hr_utility.raise_error;
2789
2790 END IF;
2791 close csr_get_apps;
2792
2793 l_assignment_periods
2794 := hxc_timecard_utilities.get_assignment_periods(l_tc_resource_id);
2795
2796
2797 g_trace := '70';
2798
2799 open csr_get_apps(l_app_set);
2800
2801 LOOP -- loop through all apps for this timecard
2802
2803 g_trace := '80';
2804
2805 fetch csr_get_apps into l_application,
2806 l_application_id,
2807 l_app_period_func,
2808 l_time_recipient_id;
2809
2810 exit when csr_get_apps%NOTFOUND;
2811
2812 g_trace := '90 Application=' || l_application;
2813 g_trace := '90 Time Recipient ID=' || to_char(l_time_recipient_id);
2814
2815 if g_debug then
2816 hr_utility.trace('90 Application=' || l_application);
2817 hr_utility.trace('90 Time Recipient ID=' || to_char(l_time_recipient_id));
2818 end if;
2819
2820 l_processed_app_start := NULL;
2821 l_processed_app_stop := NULL;
2822
2823 -- open cursor to get all related DAY blocks for this timecard.
2824
2825 open csr_get_days(l_tc_bld_blk_id, l_tc_ovn);
2826 LOOP -- loop through all related days
2827 g_trace := '100';
2828
2829 fetch csr_get_days into l_day_bld_blk_id,
2830 l_day_start_time,
2831 l_day_stop_time,
2832 l_day_ovn;
2833 exit when csr_get_days%NOTFOUND;
2834
2835 if(dayHasActiveAssignment(l_assignment_periods,l_day_start_time))then
2836
2837 if g_debug then
2838 hr_utility.set_location(l_proc, 90);
2839 hr_utility.trace('day start=' || to_char(l_day_start_time, 'YYYY/MM/DD'));
2840 end if;
2841
2842 get_application_period
2843 (p_app_period_func => l_app_period_func,
2844 p_resource_id => l_tc_resource_id,
2845 p_day => l_day_start_time,
2846 p_time_recipient => l_time_recipient_id,
2847 p_tc_start_time => l_tc_start_time,
2848 p_tc_stop_time => l_tc_stop_time,
2849 p_assignment_periods => l_assignment_periods,
2850 p_period_start => l_period_start_date,
2851 p_period_end => l_period_end_date
2852 );
2853
2854 g_trace := '120' || 'app_start=' || to_char(l_period_start_date, 'YYYY/MM/DD')
2855 || '|app_end=' || to_char(l_period_end_date, 'YYYY/MM/DD');
2856
2857
2858 l_gen_app_period:=true;
2859
2860 open c_app_overlap_data_set(l_period_start_date,l_period_end_date,l_tc_start_time);
2861 fetch c_app_overlap_data_set into l_dummy;
2862 if(c_app_overlap_data_set%found) then
2863 l_gen_app_period:=false;
2864 else
2865 l_gen_app_period:=true;
2866 end if;
2867 close c_app_overlap_data_set;
2868
2869 IF l_processed_app_start IS NULL
2870 OR (l_processed_app_start IS NOT NULL
2871 AND l_processed_app_stop IS NOT NULL
2872 AND l_processed_app_start <> l_period_start_date) THEN
2873 l_processed_app_start := l_period_start_date;
2874 l_processed_app_stop := l_period_end_date;
2875
2876 l_approval_style := get_approval_style_id
2877 (p_period_start_date => l_period_start_date,
2878 p_period_end_date => l_period_end_date,
2879 p_resource_id => l_tc_resource_id
2880 );
2881
2882 g_trace := '130 approval style_id=' || l_approval_style;
2883 --prepare data
2884 get_detail_blocks(p_timecard_id => l_tc_bld_blk_id,
2885 p_timecard_ovn => l_tc_ovn,
2886 p_start_time => l_period_start_date,
2887 p_stop_time => l_period_end_date,
2888 p_detail_blocks => l_detail_blocks,
2889 p_new_detail_blocks => l_new_detail_blocks
2890 );
2891
2892 OPEN c_approval_comp(l_approval_style, l_time_recipient_id);
2893 FETCH c_approval_comp INTO l_approval_comp;
2894
2895 if c_approval_comp%notfound then
2896 g_trace := l_approval_style||' - '||l_time_recipient_id;
2897 close c_approval_comp;
2898 fnd_message.set_name(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2899 fnd_message.set_token('PROCEDURE', l_proc);
2900 fnd_message.set_token('STEP', '130');
2901 fnd_message.raise_error;
2902 end if;
2903
2904 close c_approval_comp;
2905
2906 /* We check whether the approval style is Project manager, if so we
2907 need to replace it with the special ELA approval style created */
2908
2909
2910 IF l_approval_comp.approval_mechanism = 'PROJECT_MANAGER' THEN
2911
2912 -- Bug 4297436. Setting the original Project Manager Sequenceto the special ELA style app. period.
2913 -- Change for version 115.94
2914
2915 l_original_approval_order := l_approval_comp.approval_order ;
2916
2917 l_approval_style_id := l_approval_style;
2918 get_detail_attributes(p_detail_blocks => l_detail_blocks,
2919 p_detail_attributes => l_detail_attributes,
2920 p_new_detail_attributes => l_new_detail_attributes);
2921
2922 /* looping through all the projects present in the Timecard */
2923
2924 l_index_1 := 1;
2925 l_index := l_detail_attributes.first;
2926 WHILE l_index IS NOT NULL LOOP
2927 IF l_detail_attributes(l_index).attribute_category = 'PROJECTS' THEN
2928 l_detail_project_id := l_detail_attributes(l_index).attribute1;
2929 l_already_present := 0;
2930 /*l_no_project_manager := 0;
2931
2932 IF NOT ( g_tab_project_id.exists(l_detail_project_id ) ) THEN
2933 g_tab_project_id( l_detail_project_id).manager_id := NULL;
2934 l_no_project_manager := 1;
2935 ELSIF g_tab_project_id(l_detail_project_id).manager_id IS NULL THEN
2936 l_no_project_manager := 1;
2937 END IF;
2938
2939 IF l_no_project_manager = 1 THEN*/
2940
2941 l_index_2 := l_tab_project_id.first;
2942 WHILE l_index_2 IS NOT NULL LOOP
2943 IF l_tab_project_id(l_index_2).project_id = l_detail_project_id THEN
2944 l_already_present := 1;
2945 EXIT;
2946 END IF;
2947 l_index_2 := l_tab_project_id.next (l_index_2 );
2948 END LOOP;
2949 IF l_already_present = 0 THEN
2950 l_tab_project_id(l_index_1).project_id := l_detail_attributes(l_index).attribute1;
2951 l_index_1 := l_index_1 + 1;
2952 END IF;
2953 --END IF; -- if no project manager block
2954 END IF; -- if PROJECTS block
2955 l_index := l_detail_attributes.next(l_index );
2956 END LOOP;
2957
2958 /* call the procedure to replace the proj. manager approval style by the special ELA approval style */
2959
2960 hxc_proj_manager_approval_pkg.replace_projman_by_spl_ela
2961 (p_tab_project_id => l_tab_project_id,
2962 p_new_spl_ela_style_id => l_approval_style);
2963
2964 l_index_3 := l_tab_project_id.first;
2965
2966
2967 WHILE l_index_3 IS NOT NULL LOOP
2968 g_tab_project_id( l_tab_project_id( l_index_3 ).project_id ).manager_id := l_tab_project_id( l_index_3 ).manager_id;
2969 l_index_3 := l_tab_project_id.next( l_index_3 ) ;
2970 END LOOP;
2971
2972 /* After replacement finding the approval comp associated with the spl. ela style */
2973
2974 OPEN c_approval_comp(l_approval_style, l_time_recipient_id);
2975 FETCH c_approval_comp INTO l_approval_comp;
2976 CLOSE c_approval_comp;
2977
2978 l_approval_comp.approval_order := l_original_approval_order ;
2979
2980 END IF; --- If Project manager block
2981
2982
2983
2984 IF l_approval_comp.approval_mechanism = 'ENTRY_LEVEL_APPROVAL' THEN
2985 if g_debug then
2986 hr_utility.trace(l_proc || 'Entry level approvals 150');
2987 end if;
2988
2989 g_trace := '150 ELA';
2990
2991 --get detail attributes
2992 get_detail_attributes
2993 (p_detail_blocks => l_detail_blocks,
2994 p_detail_attributes => l_detail_attributes,
2995 p_new_detail_attributes => l_new_detail_attributes );
2996
2997
2998 -- push the block and attribute structures into the temporary
2999 -- tables used by Time Categories
3000
3001 hxc_time_category_utils_pkg.push_timecard ( l_new_detail_blocks, l_new_detail_attributes, TRUE );
3002
3003 if g_debug then
3004 hr_utility.trace(l_proc || 'Entry level approvals 160');
3005 end if;
3006
3007 OPEN c_ela_comps(l_approval_comp.approval_comp_id,
3008 l_approval_comp.object_version_number);
3009
3010 LOOP
3011 FETCH c_ela_comps INTO l_ela_comp;
3012 EXIT WHEN c_ela_comps%NOTFOUND;
3013
3014 IF l_ela_comp.time_category_id = 0 THEN
3015 l_default_comp := l_ela_comp;
3016 ELSE
3017 if g_debug then
3018 hr_utility.trace(l_proc || 'Entry level approvals 170');
3019 end if;
3020 g_trace := '170 ELA generating period';
3021
3022 if(l_gen_app_period) then
3023 generate_app_period(p_item_type => itemtype,
3024 p_item_key => itemkey,
3025 p_timecard_id => l_tc_bld_blk_id,
3026 p_resource_id => l_tc_resource_id,
3027 p_start_time => l_period_start_date,
3028 p_stop_time => l_period_end_date,
3029 p_time_recipient_id => l_time_recipient_id,
3030 p_recipient_sequence=> l_approval_comp.approval_order,
3031 p_approval_comp => l_ela_comp,
3032 p_tc_resubmitted => l_tc_resubmitted,
3033 p_detail_blocks => l_detail_blocks,
3034 p_detail_attributes => l_detail_attributes
3035 );
3036 end if;
3037
3038 if g_debug then
3039 hr_utility.trace(l_proc || 'Entry level approvals 180');
3040 end if;
3041 g_trace :=' 180 ELA finish generating period';
3042
3043 END IF;
3047 CLOSE c_ela_comps;
3044
3045 END LOOP;
3046
3048
3049 if g_debug then
3050 hr_utility.trace(l_proc || 'Entry level approvals 200');
3051 end if;
3052 g_trace := '200 any detail left??';
3053
3054 --Now take care of the rest of the blocks
3055 l_count := get_rest_detail_blocks(l_detail_blocks);
3056
3057 if g_debug then
3058 hr_utility.trace('210 rest_detail_count=' || l_count);
3059 end if;
3060
3061 g_trace := '210 rest_detail_count=' || l_count;
3062 if g_debug then
3063 hr_utility.trace('220 rest_detail_count > 0');
3064 end if;
3065
3066 g_trace := '220 rest_detail_count > 0';
3067
3068 if(l_gen_app_period) then
3069 generate_app_period(p_item_type => itemtype,
3070 p_item_key => itemkey,
3071 p_timecard_id => l_tc_bld_blk_id,
3072 p_resource_id => l_tc_resource_id,
3073 p_start_time => l_period_start_date,
3074 p_stop_time => l_period_end_date,
3075 p_time_recipient_id => l_time_recipient_id,
3076 p_recipient_sequence=> l_approval_comp.approval_order,
3077 p_approval_comp => l_default_comp,
3078 p_tc_resubmitted => l_tc_resubmitted,
3079 p_detail_blocks => l_detail_blocks,
3080 p_detail_attributes => l_detail_attributes
3081 );
3082 end if;
3083
3084 if g_debug then
3085 hr_utility.trace('230 finished generating period for rest details');
3086 end if;
3087 g_trace := '230 finished generating period for rest details';
3088 ELSE
3089 if g_debug then
3090 hr_utility.trace('250 NON ELA');
3091 end if;
3092 g_trace := '250 NON ELA';
3093
3094 -- non ELA mechanism
3095 if(l_gen_app_period) then
3096 generate_app_period(p_item_type => itemtype,
3097 p_item_key => itemkey,
3098 p_timecard_id => l_tc_bld_blk_id,
3099 p_resource_id => l_tc_resource_id,
3100 p_start_time => l_period_start_date,
3101 p_stop_time => l_period_end_date,
3102 p_time_recipient_id => l_time_recipient_id,
3103 p_recipient_sequence=> l_approval_comp.approval_order,
3104 p_approval_comp => l_approval_comp,
3105 p_tc_resubmitted => l_tc_resubmitted,
3106 p_detail_blocks => l_detail_blocks,
3107 p_detail_attributes => l_detail_attributes
3108 );
3109 end if;
3110
3111 if g_debug then
3112 hr_utility.trace( '260 finished generating period for NON ELA');
3113 end if;
3114 g_trace := '260 finished generating period for NON ELA';
3115 END IF;
3116 END IF;
3117
3118 End If; -- Is the day within an active assignment.
3119
3120 END LOOP; -- loop through all related days
3121
3122 close csr_get_days;
3123
3124 END LOOP; -- loop through all apps for this timecard
3125
3126 --OIT Enhancement.
3127 --FYI Notification to WORKER on timecard SUBMISSION
3128 hxc_approval_wf_helper.set_notif_attribute_values
3129 (itemtype,
3130 itemkey,
3131 hxc_app_comp_notifications_api.c_action_submission,
3132 hxc_app_comp_notifications_api.c_recipient_worker
3133 );
3134
3135 if g_debug then
3136 hr_utility.set_location(l_proc, 200);
3137 end if;
3138 close csr_get_apps;
3139
3140 if g_debug then
3141 hr_utility.trace('300 END of create_appl_period_info');
3142 end if;
3143
3144
3145 g_trace := '300 END of create_appl_period_info';
3146
3147 result := '';
3148 return;
3149
3150 exception
3151 when others then
3152 -- The line below records this function call in the error system
3153 -- in the case of an exception.
3154 --
3155 if g_debug then
3156 hr_utility.set_location(l_proc, 999);
3157 --
3158 hr_utility.trace('IN EXCEPTION IN create_appl_period_info');
3159 --
3160 end if;
3161 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.create_appl_period_info',
3162 itemtype, itemkey, to_char(actid), funcmode, g_trace);
3163
3164 raise;
3165 result := '';
3166 return;
3167 --
3168 --
3169
3170 END create_appl_period_info;
3171 --
3172
3173 --
3174
3175
3176 FUNCTION chk_app_approved(
3177 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
3181 ,p_recipient_sequence IN hxc_app_period_summary.recipient_sequence%TYPE
3178 ,p_period_start_date IN hxc_time_building_blocks.start_time%TYPE
3179 ,p_period_end_date IN hxc_time_building_blocks.stop_time%TYPE
3180 ,p_time_recipient_id IN hxc_time_recipients.time_recipient_id%TYPE
3182 ,p_time_category_id IN hxc_time_categories.time_category_id%TYPE
3183 ,p_category_sequence IN hxc_app_period_summary.category_sequence%TYPE
3184 )
3185 RETURN VARCHAR2
3186 IS
3187
3188 CURSOR csr_chk(
3189 p_date DATE
3190 ,p_resource_id hxc_time_building_blocks.resource_id%TYPE
3191 ,p_period_start_date hxc_time_building_blocks.start_time%TYPE
3192 ,p_period_end_date hxc_time_building_blocks.stop_time%TYPE
3193 ,p_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE
3194 ,p_recipient_sequence hxc_app_period_summary.recipient_sequence%TYPE
3195 ,p_time_category_id hxc_time_categories.time_category_id%TYPE
3196 ,p_category_sequence hxc_app_period_summary.category_sequence%TYPE
3197 )
3198 IS
3199 SELECT aps.approval_status
3200 FROM hxc_app_period_summary aps
3201 WHERE aps.resource_id = p_resource_id
3202 AND p_date BETWEEN aps.start_time AND aps.stop_time
3203 AND aps.approval_status <> 'APPROVED'
3204 AND (
3205 (aps.recipient_sequence < p_recipient_sequence)
3206 OR (p_category_sequence IS NOT NULL
3207 AND aps.time_recipient_id = p_time_recipient_id
3208 AND aps.recipient_sequence = p_recipient_sequence
3209 AND aps.time_category_id IS NOT NULL
3210 AND aps.category_sequence IS NOT NULL
3211 AND aps.time_category_id = p_time_category_id
3212 AND aps.category_sequence < p_category_sequence)
3213 )
3214 AND exists
3215 (select 'Y'
3216 from hxc_tc_ap_links tcl
3217 where tcl.application_period_id = aps.application_period_id
3218 );
3219
3220 l_days number := p_period_end_date - p_period_start_date + 1;
3221 l_date date;
3222 l_approved varchar2(1);
3223 l_approval_status varchar2(30);
3224 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_app_approved';
3225
3226 BEGIN
3227
3228 if g_debug then
3229 hr_utility.set_location(l_proc, 10);
3230 end if;
3231 l_date := p_period_start_date;
3232
3233 l_approved := 'Y';
3234
3235 FOR i in 1 .. l_days LOOP
3236
3237 OPEN csr_chk(
3238 p_date => l_date
3239 ,p_resource_id => p_resource_id
3240 ,p_period_start_date => p_period_start_date
3241 ,p_period_end_date => p_period_end_date
3242 ,p_time_recipient_id => p_time_recipient_id
3243 ,p_recipient_sequence => p_recipient_sequence
3244 ,p_time_category_id => p_time_category_id
3245 ,p_category_sequence => p_category_sequence
3246 );
3247
3248 FETCH csr_chk into l_approval_status;
3249
3250 IF csr_chk%FOUND
3251 THEN
3252 CLOSE csr_chk;
3253
3254 RETURN 'N'; --not completed approved
3255 END IF;
3256
3257 CLOSE csr_chk;
3258
3259 l_date := p_period_start_date + i;
3260
3261 END LOOP;
3262
3263 if g_debug then
3264 hr_utility.set_location(l_proc, 4);
3265 end if;
3266
3267 RETURN 'Y';
3268
3269 END chk_app_approved;
3270
3271
3272 FUNCTION has_valid_assign(
3273 p_day IN DATE
3274 ,p_assignment_periods IN hxc_timecard_utilities.periods
3275 )
3276 RETURN BOOLEAN
3277 IS
3278 i pls_integer;
3279 Begin
3280
3281 i := p_assignment_periods.first;
3282 Loop
3283 Exit when NOT p_assignment_periods.exists(i);
3284 IF p_day BETWEEN p_assignment_periods(i).start_date AND p_assignment_periods(i).end_date then
3285 return true;
3286 End if;
3287 i := p_assignment_periods.next(i);
3288 End loop;
3289
3290 return false;
3291
3292 End has_valid_assign;
3293
3294 FUNCTION is_submitted(
3295 p_day IN DATE
3296 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
3297 )
3298 RETURN BOOLEAN
3299 IS
3300 CURSOR c_submitted(
3301 p_day IN DATE
3302 ,p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
3303 )
3304 IS
3305 SELECT 'Y'
3306 FROM hxc_time_building_blocks day
3307 ,hxc_time_building_blocks tc
3308 WHERE TRUNC(day.start_time) = p_day
3309 AND day.scope = 'DAY'
3310 AND day.approval_status = 'SUBMITTED'
3311 AND day.resource_id = p_resource_id
3312 AND day.date_to = hr_general.end_of_time
3313 AND day.parent_building_block_id = tc.time_building_block_id
3314 AND day.parent_building_block_ovn = tc.object_version_number
3315 AND tc.scope = 'TIMECARD'
3316 AND tc.date_to = hr_general.end_of_time;
3317
3318 l_submitted VARCHAR2(1);
3319 BEGIN
3320 OPEN c_submitted(p_day, p_resource_id);
3321 FETCH c_submitted INTO l_submitted;
3322
3323 IF c_submitted%NOTFOUND
3324 THEN
3325 CLOSE c_submitted;
3326 RETURN FALSE;
3327 END IF;
3328
3329 CLOSE c_submitted;
3330 RETURN TRUE;
3331 END is_submitted;
3332
3333 --
3337 p_period_end_date date,
3334 -------------------------- chk_submitted_days ------------------------------
3335 --
3336 FUNCTION chk_submitted_days(p_period_start_date date,
3338 p_resource_id number)
3339 RETURN VARCHAR2 IS
3340
3341
3342 l_assignment_periods hxc_timecard_utilities.periods;
3343 l_day DATE;
3344 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_submitted_days';
3345 --
3346 BEGIN
3347 --
3348 if g_debug then
3349 hr_utility.set_location(l_proc, 10);
3350 end if;
3351 --
3352 BEGIN
3353 --added by jxtan to fix mid period hiring
3354 l_assignment_periods := hxc_timecard_utilities.get_assignment_periods(p_resource_id);
3355
3356 l_day := p_period_start_date;
3357 LOOP
3358 EXIT WHEN l_day > p_period_end_date;
3359 IF has_valid_assign(l_day, l_assignment_periods)
3360 THEN
3361 IF NOT is_submitted(l_day, p_resource_id)
3362 THEN
3363 RETURN 'N';
3364 END IF;
3365 END IF;
3366 l_day := l_day + 1;
3367 END LOOP;
3368
3369 RETURN 'Y';
3370 END;
3371
3372 END chk_submitted_days;
3373
3374 PROCEDURE process_appl_periods(itemtype IN varchar2,
3375 itemkey IN varchar2,
3376 actid IN number,
3377 funcmode IN varchar2,
3378 result IN OUT NOCOPY varchar2)
3379 IS
3380
3381 CURSOR csr_get_tc_info(
3382 p_bld_blk_id number,
3383 p_ovn number
3384 )
3385 IS
3386 select tc.resource_id, tc.start_time, tc.stop_time,tc.last_updated_by
3387 from hxc_time_building_blocks tc
3388 where tc.time_building_block_id = p_bld_blk_id
3389 and tc.object_version_number = p_ovn;
3390
3391
3392 CURSOR csr_get_appl_periods(
3393 p_resource_id in hxc_app_period_summary.resource_id%type
3394 ,p_timecard_id in hxc_timecard_summary.timecard_id%type
3395 )
3396 IS
3397 select aps.application_period_id,
3398 aps.start_time, -- period_start_date
3399 aps.stop_time, -- period_end_date
3400 aps.application_period_ovn,
3401 aps.time_recipient_id,
3402 aps.recipient_sequence,
3403 aps.time_category_id,
3404 aps.category_sequence,
3405 aps.approval_item_key
3406 from hxc_app_period_summary aps, hxc_tc_ap_links tcl
3407 where aps.resource_id = p_resource_id
3408 and aps.approval_status = 'SUBMITTED'
3409 and aps.notification_status = 'NOT_NOTIFIED'
3410 and aps.application_period_id = tcl.application_period_id
3411 and tcl.timecard_id = p_timecard_id;
3412
3413
3414 CURSOR c_period_notified(
3415 p_period_id number
3416 )
3417 IS
3418 select 'N'
3419 from hxc_app_period_summary
3420 where application_period_id = p_period_id
3421 and approval_status = 'SUBMITTED'
3422 and notification_status = 'NOT_NOTIFIED';
3423
3424 l_notified_status varchar2(1);
3425 l_tc_bld_blk_id number;
3426 l_tc_ovn number;
3427 l_tc_resubmitted varchar2(10);
3428 l_bb_new varchar2(10);
3429 --
3430 l_tc_url varchar2(1000);
3431 l_tc_resource_id number;
3432 l_tc_start_time date;
3433 l_tc_stop_time date;
3434 --
3435 l_application varchar2(80);
3436 l_application_id number;
3437 l_time_recipient_id number;
3438 l_time_recipient varchar2(150);
3439 l_time_recipient_seq number;
3440 --
3441
3442 l_approval_recipient number;
3443 l_approver_seq number;
3444 --
3445 l_day_bld_blk_id number;
3446 l_day_start_time date;
3447 l_day_stop_time date;
3448 l_day_ovn number;
3449 l_appl_period_bb_id number;
3450 l_appl_period_bb_ovn number;
3451 l_period_start_date date;
3452 l_period_end_date date;
3453 --
3454 l_cnt number;
3455 l_approval_style_id number;
3456 l_exists varchar2(1);
3457 l_chk_days varchar2(1);
3458 l_approved varchar2(1);
3459 l_not_notified varchar2(1);
3460 l_item_key wf_items.item_key%type;
3461 l_process_name varchar2(30);
3462 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.process_appl_periods';
3463 l_last_updated_by number;
3464 l_process varchar2(1) := 'Y';
3465
3466 l_recipient_sequence hxc_approval_comps.approval_order%TYPE;
3467 l_time_category_id hxc_time_categories.time_category_id%TYPE;
3468 l_category_sequence hxc_approval_comps.approval_order%TYPE;
3469 l_approval_item_key wf_items.item_key%type;
3470 l_is_blank varchar2(1) := NULL;
3471
3472 BEGIN
3473 l_approval_item_key := null;
3474 g_debug:=hr_utility.debug_enabled;
3475 if g_debug then
3476 hr_utility.set_location(l_proc, 10);
3477 end if;
3478 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
3479 (itemtype => itemtype,
3480 itemkey => itemkey,
3481 aname => 'TC_BLD_BLK_ID');
3482
3483 if g_debug then
3487 (itemtype => itemtype,
3484 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
3485 end if;
3486 l_tc_ovn := wf_engine.GetItemAttrNumber
3488 itemkey => itemkey,
3489 aname => 'TC_BLD_BLK_OVN');
3490
3491 if g_debug then
3492 hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
3493 end if;
3494 l_tc_resubmitted := wf_engine.GetItemAttrText
3495 (itemtype => itemtype,
3496 itemkey => itemkey,
3497 aname => 'TC_RESUBMITTED');
3498
3499 if g_debug then
3500 hr_utility.trace('Timecard Resubmitted is : ' || l_tc_resubmitted);
3501 end if;
3502 l_bb_new := wf_engine.GetItemAttrText
3503 (itemtype => itemtype,
3504 itemkey => itemkey,
3505 aname => 'BB_NEW');
3506
3507 if g_debug then
3508 hr_utility.trace('Building Block New is : ' || l_bb_new);
3509 hr_utility.set_location(l_proc, 20);
3510 end if;
3511
3512 open csr_get_tc_info(l_tc_bld_blk_id,
3513 l_tc_ovn);
3514 fetch csr_get_tc_info into l_tc_resource_id,
3515 l_tc_start_time,
3516 l_tc_stop_time,
3517 l_last_updated_by;
3518
3519 if g_debug then
3520 hr_utility.set_location(l_proc, 30);
3521 hr_utility.trace('Timecard Resource ID is : ' || to_char(l_tc_resource_id));
3522 hr_utility.trace('Timecard Start Time is : ' ||
3523 to_char(l_tc_start_time, 'DD-MM-YYYY'));
3524 hr_utility.trace('Timecard End Time is : ' ||
3525 to_char(l_tc_stop_time, 'DD-MM-YYYY'));
3526 end if;
3527 IF csr_get_tc_info%NOTFOUND
3528 THEN
3529
3530 if g_debug then
3531 hr_utility.set_location(l_proc, 40);
3532 end if;
3533
3534 g_error_count := g_error_count + 1;
3535 g_error_table(g_error_count).MESSAGE_NAME := 'HXC_APR_NO_TIMECARD_INFO';
3536 g_error_table(g_error_count).APPLICATION_SHORT_NAME := 'HXC';
3537
3538 hr_utility.set_message(809, 'HXC_APR_NO_TIMECARD_INFO');
3539 hr_utility.raise_error;
3540
3541 END IF;
3542
3543 if g_debug then
3544 hr_utility.set_location(l_proc, 50);
3545 end if;
3546
3547 close csr_get_tc_info;
3548
3549 l_process_name := 'HXC_APPLY_NOTIFY';
3550
3551 if g_debug then
3552 hr_utility.set_location(l_proc, 60);
3553 end if;
3554
3555 l_approval_style_id := get_approval_style_id(l_tc_start_time,
3556 l_tc_stop_time,
3557 l_tc_resource_id);
3558 --
3559 if g_debug then
3560 hr_utility.set_location(l_proc, 70);
3561 --
3562 hr_utility.trace('l_approval_style_id is : ' || to_char(l_approval_style_id));
3563 -- For all the application periods created for this timecard,
3564 -- create a workflow process to continue with the approval - to
3565 -- apply the approval rules and notify the approver(s).
3566
3567 hr_utility.set_location(l_proc, 110);
3568 end if;
3569
3570 open csr_get_appl_periods(l_tc_resource_id, l_tc_bld_blk_id);
3571
3572 LOOP
3573
3574 if g_debug then
3575 hr_utility.set_location(l_proc, 120);
3576 end if;
3577 fetch csr_get_appl_periods into l_appl_period_bb_id,
3578 l_period_start_date,
3579 l_period_end_date,
3580 l_appl_period_bb_ovn,
3581 l_time_recipient,
3582 l_recipient_sequence,
3583 l_time_category_id,
3584 l_category_sequence,
3585 l_approval_item_key;
3586
3587
3588 exit when csr_get_appl_periods%NOTFOUND;
3589
3590 if g_debug then
3591 hr_utility.trace('l_appl_period_bb_id is : ' ||
3592 to_char(l_appl_period_bb_id));
3593 hr_utility.trace('l_appl_period_bb_ovn is : ' ||
3594 to_char(l_appl_period_bb_ovn));
3595 hr_utility.trace('l_period_start_date is : ' ||
3596 to_char(l_period_start_date, 'DD-MM-YYYY'));
3597 hr_utility.trace('l_period_end_date is : ' ||
3598 to_char(l_period_end_date, 'DD-MM-YYYY'));
3599 hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
3600 hr_utility.trace('l_approval_item_key is : ' || l_approval_item_key);
3601
3602 hr_utility.set_location(l_proc, 150);
3603 end if;
3604 -- Check to see if all the days in the application period have
3605 -- submitted days.
3606 --
3607 l_chk_days := chk_submitted_days(l_period_start_date,
3608 l_period_end_date,
3609 l_tc_resource_id);
3610
3611 IF l_tc_stop_time < l_period_end_date THEN
3612 l_process := 'N';
3613 END IF;
3614
3615 if g_debug then
3616 hr_utility.trace('Checked days:'||l_chk_days);
3617 hr_utility.set_location(l_proc, 160);
3618 end if;
3619 IF l_chk_days = 'Y' AND l_process = 'Y' THEN
3620
3621 if g_debug then
3622 hr_utility.set_location(l_proc, 170);
3623 end if;
3624 --
3628 l_approved := chk_app_approved
3625 -- Check to see if all applications before this one in the approval
3626 -- style have approved all the days in this period.
3627 --
3629 (p_resource_id => l_tc_resource_id
3630 ,p_period_start_date => l_period_start_date
3631 ,p_period_end_date => l_period_end_date
3632 ,p_time_recipient_id => l_time_recipient
3633 ,p_recipient_sequence => l_recipient_sequence
3634 ,p_time_category_id => l_time_category_id
3635 ,p_category_sequence => l_category_sequence);
3636
3637 if g_debug then
3638 hr_utility.trace('All previous approved:'||l_approved);
3639 end if;
3640
3641 IF l_approved = 'Y' THEN
3642 OPEN c_period_notified(l_appl_period_bb_id);
3643 FETCH c_period_notified INTO l_notified_status;
3644
3645 IF c_period_notified%NOTFOUND THEN
3646 CLOSE c_period_notified;
3647 if g_debug then
3648 hr_utility.trace('already processed ' || l_appl_period_bb_id);
3649 end if;
3650 ELSE
3651 CLOSE c_period_notified;
3652 if g_debug then
3653 hr_utility.set_location(l_proc, 210);
3654 hr_utility.trace('itemtype is : ' || itemtype);
3655 hr_utility.trace('l_process_name is : ' || l_process_name);
3656 end if;
3657 --
3658 -- Setup l_item_key from a sequence.
3659 --
3660 if l_approval_item_key is not null then
3661 l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
3662 itemkey => l_approval_item_key ,
3663 aname => 'IS_DIFF_TC',
3664 ignore_notfound => true);
3665 else
3666 l_is_blank := null;
3667 end if;
3668
3669 SELECT hxc_approval_item_key_s.nextval
3670 INTO l_item_key
3671 FROM dual;
3672
3673 update hxc_app_period_summary
3674 set notification_status = 'NOTIFIED',
3675 approval_item_type = itemtype,
3676 approval_process_name = l_process_name,
3677 approval_item_key = l_item_key
3678 where application_period_id = l_appl_period_bb_id
3679 and application_period_ovn = l_appl_period_bb_ovn;
3680
3681 if g_debug then
3682 hr_utility.trace('l_item_key is : ' || l_item_key);
3683 end if;
3684
3685 wf_engine.CreateProcess(itemtype => itemtype,
3686 itemkey => l_item_key,
3687 process => l_process_name);
3688 wf_engine.setitemowner(itemtype,
3689 l_item_key,
3690 HXC_FIND_NOTIFY_APRS_PKG.get_login(p_person_id=>l_tc_resource_id,
3691 p_user_id => l_last_updated_by)
3692 );
3693 if g_debug then
3694 hr_utility.set_location(l_proc, 260);
3695 end if;
3696 if(item_attribute_exists(itemtype,l_item_key,'IS_DIFF_TC')) then
3697 wf_engine.SetItemAttrText(
3698 itemtype => itemtype,
3699 itemkey => l_item_key,
3700 aname => 'IS_DIFF_TC',
3701 avalue => l_is_blank);
3702 else
3703 wf_engine.additemattr
3704 (itemtype => itemtype,
3705 itemkey => l_item_key,
3706 aname => 'IS_DIFF_TC',
3707 text_value => l_is_blank);
3708 end if;
3709
3710 wf_engine.SetItemAttrDate(itemtype => itemtype,
3711 itemkey => l_item_key,
3712 aname => 'APP_START_DATE',
3713 avalue => l_period_start_date);
3714
3715 wf_engine.SetItemAttrText(itemtype => itemtype,
3716 itemkey => l_item_key,
3717 aname => 'FORMATTED_APP_START_DATE',
3718 avalue => to_char(l_period_start_date,'YYYY/MM/DD'));
3719 if g_debug then
3720 hr_utility.set_location(l_proc, 270);
3721 hr_utility.trace('APP_START_DATE is : ' ||
3722 to_char(l_period_start_date, 'DD-MM-YYYY'));
3723 end if;
3724
3725 wf_engine.SetItemAttrDate(itemtype => itemtype,
3726 itemkey => l_item_key,
3727 aname => 'APP_END_DATE',
3728 avalue => l_period_end_date);
3729
3730 if g_debug then
3731 hr_utility.set_location(l_proc, 280);
3732 hr_utility.trace('APP_END_DATE is : ' ||
3733 to_char(l_period_end_date, 'DD-MM-YYYY'));
3734 end if;
3735
3736 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3737 itemkey => l_item_key,
3741 if g_debug then
3738 aname => 'APP_BB_ID',
3739 avalue => l_appl_period_bb_id);
3740
3742 hr_utility.set_location(l_proc, 290);
3743 hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3744 end if;
3745
3746 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3747 itemkey => l_item_key,
3748 aname => 'APP_BB_OVN',
3749 avalue => l_appl_period_bb_ovn);
3750
3751 if g_debug then
3752 hr_utility.set_location(l_proc, 300);
3753 hr_utility.trace('APP_BB_OVN is : ' ||
3754 to_char(l_appl_period_bb_ovn));
3755 end if;
3756
3757 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3758 itemkey => l_item_key,
3759 aname => 'RESOURCE_ID',
3760 avalue => l_tc_resource_id);
3761
3762 if g_debug then
3763 hr_utility.set_location(l_proc, 310);
3764 hr_utility.trace('RESOURCE_ID is : ' || to_char(l_tc_resource_id));
3765 end if;
3766
3767 wf_engine.SetItemAttrText(itemtype => itemtype,
3768 itemkey => l_item_key,
3769 aname => 'TIME_RECIPIENT_ID',
3770 avalue => l_time_recipient);
3771
3772 if g_debug then
3773 hr_utility.set_location(l_proc, 320);
3774 hr_utility.trace('TIME_RECIPIENT_ID is : ' || l_time_recipient);
3775 end if;
3776
3777 wf_engine.SetItemAttrText(itemtype => itemtype,
3778 itemkey => l_item_key,
3779 aname => 'TC_RESUBMITTED',
3780 avalue => l_tc_resubmitted);
3781
3782 if g_debug then
3783 hr_utility.set_location(l_proc, 330);
3784 hr_utility.trace('TC_RESUBMITTED is : ' || l_tc_resubmitted);
3785 end if;
3786
3787 wf_engine.SetItemAttrText(itemtype => itemtype,
3788 itemkey => l_item_key,
3789 aname => 'BB_NEW',
3790 avalue => l_bb_new);
3791
3792 if g_debug then
3793 hr_utility.set_location(l_proc, 335);
3794 hr_utility.trace('BB_NEW is : ' || l_bb_new);
3795 end if;
3796
3797 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3798 itemkey => l_item_key,
3799 aname => 'TC_BLD_BLK_ID',
3800 avalue => l_tc_bld_blk_id);
3801
3802 if g_debug then
3803 hr_utility.set_location(l_proc, 340);
3804 hr_utility.trace('TC_BLD_BLK_ID is : ' || to_char(l_tc_bld_blk_id));
3805 end if;
3806
3807 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3808 itemkey => l_item_key,
3809 aname => 'TC_BLD_BLK_OVN',
3810 avalue => l_tc_ovn);
3811
3812 if g_debug then
3813 hr_utility.set_location(l_proc, 350);
3814 hr_utility.trace('TC_BLD_BLK_OVN is : ' || to_char(l_tc_ovn));
3815 end if;
3816
3817 wf_engine.SetItemAttrNumber(itemtype => itemtype,
3818 itemkey => l_item_key,
3819 aname => 'APPROVAL_STYLE_ID',
3820 avalue => l_approval_style_id);
3821
3822 l_tc_url :='JSP:OA_HTML/OA.jsp?akRegionCode=HXCAPRVPAGE&akRegionApplicationId=' ||
3823 '809&retainAM=Y&Action=Details&AprvTimecardId=' || l_appl_period_bb_id ||
3824 '&AprvTimecardOvn=' || l_appl_period_bb_ovn ||
3825 '&AprvStartTime=' || to_char(l_period_start_date,'YYYY/MM/DD')||
3826 '&AprvStopTime=' || to_char(l_period_end_date,'YYYY/MM/DD') ||
3827 '&AprvResourceId=' || to_char(l_tc_resource_id) ||
3828 '&OAFunc=HXC_TIME_ENTER'||
3829 '&NtfId=-&#NID-';
3830
3831 wf_engine.SetItemAttrText(itemtype => itemtype,
3832 itemkey => l_item_key,
3833 aname => 'HXC_TIMECARD_URL',
3834 avalue => l_tc_url);
3835
3836 --
3837 -- For bug 4291206, copy the previous approvers
3838 -- in the new process
3839 -- 115.92 Change.
3840 --
3841 hxc_approval_wf_util.copy_previous_approvers
3842 (p_item_type => itemtype,
3843 p_current_key => itemkey,
3847
3844 p_copyto_key => l_item_key);
3845
3846 -- Update attribute4 with NOTIFIED and attribute2 with the Item Key.
3848 if g_debug then
3849 hr_utility.trace('APP_BB_OVN is : ' ||
3850 to_char(l_appl_period_bb_ovn));
3851 hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
3852 hr_utility.trace('Before Update');
3853 hr_utility.set_location(l_proc, 360);
3854 end if;
3855
3856 update hxc_app_period_summary
3857 set notification_status = 'NOTIFIED'
3858 where application_period_id = l_appl_period_bb_id
3859 and application_period_ovn = l_appl_period_bb_ovn;
3860
3861
3862 wf_engine.StartProcess(itemtype => itemtype,
3863 itemkey => l_item_key);
3864
3865 if g_debug then
3866 hr_utility.set_location(l_proc, 365);
3867 end if;
3868 END IF; -- if not notified;
3869 END IF; -- approved
3870
3871 END IF; -- l_chk_days
3872
3873 if g_debug then
3874 hr_utility.set_location(l_proc, 380);
3875 end if;
3876
3877 END LOOP;
3878
3879 if g_debug then
3880 hr_utility.trace('OUTSIDE END LOOP');
3881 end if;
3882
3883 close csr_get_appl_periods;
3884
3885 result := '';
3886 return;
3887 exception
3888 when others then
3889 -- The line below records this function call in the error system
3890 -- in the case of an exception.
3891 --
3892 if g_debug then
3893 hr_utility.set_location(l_proc, 999);
3894 --
3895 hr_utility.trace('IN EXCEPTION IN process_appl_periods');
3896 end if;
3897 --
3898 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.process_appl_periods',
3899 itemtype, itemkey, to_char(actid), funcmode);
3900 raise;
3901 result := '';
3902 return;
3903
3904 END process_appl_periods;
3905
3906
3907
3908 -----------------------------------------new procedures
3909
3910 /*
3911 FUNCTION get_approval_style(
3912 p_app_id IN hxc_time_building_blocks.time_building_block_id
3913 ,p_app_ovn IN hxc_time_building_blocks.object_version_number
3914 ) RETURN hxc_approval_styles.approval_style_id%TYPE
3915 IS
3916 l_approval_style hxc_approval_styles.approval_style_id%TYPE;
3917
3918 CURSOR c_approval_style(
3919 p_app_id IN hxc_time_building_blocks.time_building_block_id
3920 )
3921 IS
3922 SELECT tc.approval_style_id
3923 FROM hxc_timecard_summary tc
3924 ,hxc_timecard_application_summary ta
3925 WHERE ta.application_period_id = p_app_id
3926 AND tc.time_building_block_id = ta.time_building_block_id
3927
3928 BEGIN
3929 OPEN c_approval_style(p_app_id);
3930 FETCH c_approval_style INTO l_appproval_style;
3931
3932 IF c_approval_style%NOTFOUND
3933 THEN
3934 CLOSE c_approval_style;
3935
3936 RETURN NULL;
3937 END IF;
3938
3939 RETURN l_approval_style;
3940 END get_approval_style;
3941 */
3942
3943
3944 FUNCTION get_approval_style(
3945 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3946 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
3947 ) RETURN hxc_approval_styles.approval_style_id%TYPE
3948 IS
3949 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE := NULL;
3950 /*
3951 CURSOR c_approval_style(
3952 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
3953 )
3954 IS
3955 select approval_style_id
3956 from hxc_timecard_summary
3957 where timecard_id = p_timecard_id;
3958 */
3959 BEGIN
3960 /*
3961 OPEN c_approval_style(p_timecard_id);
3962
3963 FETCH c_approval_style INTO l_approval_style_id;
3964 IF c_approval_style%NOTFOUND
3965 THEN
3966 CLOSE c_approval_style;
3967 END IF;
3968 */
3969 RETURN l_approval_style_id;
3970 END get_approval_style;
3971
3972
3973 -- Procedure
3974 -- start_approval_wf_process
3975 --
3976 -- Description
3977 -- Start the Approval workflow process for the given timecard.
3978 -- This overloaded version added by A.Rundell, version 115.49, for
3979 -- the second generation deposit wrapper.
3980 --
3981 PROCEDURE start_approval_wf_process
3982 (p_item_type IN varchar2
3983 ,p_item_key IN varchar2
3984 ,p_process_name IN varchar2
3985 ,p_tc_bb_id IN number
3986 ,p_tc_ovn IN number
3987 ,p_tc_resubmitted IN varchar2
3988 ,p_bb_new IN varchar2
3989 )is
3990
3991 l_proc varchar2(70) := 'HXC_APPROVAL_WF_PKG.start_approval_wf_process';
3992 l_defer FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%type;
3993
3994 Begin
3995 --
3996 -- Fetch the defer option
3997 --
3998
3999 l_defer := fnd_profile.value('HXC_DEFER_WORKFLOW');
4000
4004
4001 if(l_defer is null) then
4002 l_defer := 'Y';
4003 end if;
4005 --
4006 -- Initialization
4007 --
4008 g_error_table.delete;
4009 g_error_count := 0;
4010 if(l_defer='Y') then
4011 wf_engine.threshold := -1; -- Ensures a deferred process
4012 else
4013 wf_engine.threshold := 100;
4014 end if;
4015
4016 wf_engine.createProcess
4017 (itemtype => p_item_type
4018 ,itemkey => p_item_key
4019 ,process => p_process_name
4020 );
4021
4022 wf_engine.SetItemAttrNumber
4023 (itemtype => p_item_type
4024 ,itemkey => p_item_key
4025 ,aname => 'TC_BLD_BLK_ID'
4026 ,avalue => p_tc_bb_id
4027 );
4028
4029 wf_engine.SetItemAttrNumber
4030 (itemtype => p_item_type
4031 ,itemkey => p_item_key
4032 ,aname => 'TC_BLD_BLK_OVN'
4033 ,avalue => p_tc_ovn
4034 );
4035
4036 wf_engine.SetItemAttrText
4037 (itemtype => p_item_type
4038 ,itemkey => p_item_key
4039 ,aname => 'TC_RESUBMITTED'
4040 ,avalue => p_tc_resubmitted
4041 );
4042
4043 wf_engine.SetItemAttrText
4044 (itemtype => p_item_type
4045 ,itemkey => p_item_key
4046 ,aname => 'BB_NEW'
4047 ,avalue => p_bb_new
4048 );
4049
4050 wf_engine.StartProcess
4051 (itemtype => p_item_type
4052 ,itemkey => p_item_key
4053 );
4054
4055 wf_engine.threshold := 50;
4056
4057 END start_approval_wf_process;
4058
4059
4060 --
4061 --
4062 -- Procedure
4063 -- start_approval_wf_process
4064 --
4065 -- Description
4066 -- Start the Approval workflow process for the given timecard
4067 --
4068 PROCEDURE start_approval_wf_process
4069 (p_item_type IN varchar2
4070 ,p_item_key IN varchar2
4071 ,p_tc_bb_id IN number
4072 ,p_tc_ovn IN number
4073 ,p_tc_resubmitted IN varchar2
4074 ,p_error_table OUT NOCOPY hxc_self_service_time_deposit.message_table
4075 ,p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info
4076 ,p_time_attributes IN hxc_self_service_time_deposit.building_block_attribute_info
4077 ,p_bb_new IN varchar2)
4078 is
4079 --
4080 --
4081 -- l_item_key wf_items.item_key%type;
4082 -- l_process_name varchar2(30);
4083 --
4084 l_process_name wf_process_activities.process_name%type;
4085 --
4086 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.start_approval_wf_process';
4087 --
4088 BEGIN
4089 g_debug:=hr_utility.debug_enabled;
4090 --
4091 if g_debug then
4092 hr_utility.set_location(l_proc, 10);
4093 --
4094 hr_utility.trace('Start Approval - BB ID is : ' || to_char(p_tc_bb_id));
4095 hr_utility.trace('Start Approval - BB OVN is : ' || to_char(p_tc_ovn));
4096 hr_utility.trace('Start Approval - TC RESUBMITTED is : ' || p_tc_resubmitted);
4097 --
4098 end if;
4099 -- Nulls out the error table.
4100 --
4101 g_error_table.delete;
4102 g_error_count := 0;
4103 --
4104 -- Sets up global variables for timecard records.
4105 --
4106 g_time_building_blocks := p_time_building_blocks;
4107 g_time_attributes := p_time_attributes;
4108 --
4109 -- Creates a new runtime process for the WF item type passed.
4110 -- p_process_name is HXC_APPROVAL.
4111 --
4112 l_process_name := 'HXC_APPROVAL';
4113 --
4114 wf_engine.threshold := -1;
4115 --
4116 wf_engine.createProcess(itemtype => p_item_type,
4117 itemkey => p_item_key,
4118 process => l_process_name);
4119 --
4120 if g_debug then
4121 hr_utility.set_location(l_proc, 20);
4122 end if;
4123 --
4124 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4125 itemkey => p_item_key,
4126 aname => 'TC_BLD_BLK_ID',
4127 avalue => p_tc_bb_id);
4128 --
4129 if g_debug then
4130 hr_utility.set_location(l_proc, 30);
4131 end if;
4132 --
4133 wf_engine.SetItemAttrNumber(itemtype => p_item_type,
4134 itemkey => p_item_key,
4135 aname => 'TC_BLD_BLK_OVN',
4136 avalue => p_tc_ovn);
4137 --
4138 if g_debug then
4139 hr_utility.set_location(l_proc, 40);
4140 end if;
4141 --
4142 wf_engine.SetItemAttrText(itemtype => p_item_type,
4143 itemkey => p_item_key,
4144 aname => 'TC_RESUBMITTED',
4145 avalue => p_tc_resubmitted);
4146 --
4147 if g_debug then
4148 hr_utility.set_location(l_proc, 50);
4149 end if;
4150 --
4151 IF p_bb_new = 'Y' THEN
4152 wf_engine.SetItemAttrText(itemtype => p_item_type,
4153 itemkey => p_item_key,
4154 aname => 'BB_NEW',
4155 avalue => 'YES');
4156 ELSE
4157 wf_engine.SetItemAttrText(itemtype => p_item_type,
4158 itemkey => p_item_key,
4162 --
4159 aname => 'BB_NEW',
4160 avalue => 'NO');
4161 END IF;
4163 if g_debug then
4164 hr_utility.set_location(l_proc, 60);
4165 end if;
4166 --
4167 wf_engine.StartProcess(itemtype => p_item_type,
4168 itemkey => p_item_key);
4169 --
4170 wf_engine.threshold := 50;
4171 --
4172 if g_debug then
4173 hr_utility.set_location(l_proc, 70);
4174 end if;
4175 --
4176 p_error_table := g_error_table;
4177 --
4178
4179 --
4180 END start_approval_wf_process;
4181
4182
4183 --
4184 ---------------------------- upd_apr_details ------------------------------
4185 --
4186 PROCEDURE upd_apr_details(p_app_bb_id IN number,
4187 p_app_bb_ovn IN number,
4188 p_approver_id IN number,
4189 p_approved_time IN date,
4190 p_approval_comment IN varchar2,
4191 p_approval_status IN varchar2,
4192 p_delegated_for IN varchar2) is
4193
4194 --
4195 /*
4196 cursor csr_get_attributes(p_app_bb_id in number,
4197 p_app_bb_ovn in number) is
4198 select attribute1, attribute2,
4199 attribute8, attribute9
4200 from hxc_time_attributes
4201 where time_attribute_id = (select min(time_attribute_id)
4202 from hxc_time_attribute_usages
4203 where time_building_block_id = p_app_bb_id
4204 and time_building_block_ovn = p_app_bb_ovn);
4205 */
4206 --
4207 t_attributes hxc_time_attributes_api.timecard;
4208 l_appl_period_bb_id number := p_app_bb_id;
4209 l_appl_period_bb_ovn number := p_app_bb_ovn;
4210 l_approver_id number := p_approver_id;
4211 -- l_time_recipient varchar2(150);
4212 -- l_item_key varchar2(150);
4213 l_notified_status varchar2(150) := 'FINISHED';
4214 l_approved_time varchar2(150)
4215 := fnd_date.date_to_canonical(p_approved_time);
4216 -- := to_char(p_approved_time, 'DD-MM-YYYY HH:MM:SS');
4217 l_approval_comment varchar2(150) := p_approval_comment;
4218 l_approved_status varchar2(150) := p_approval_status;
4219 l_delegated_for varchar2(150) := p_delegated_for;
4220 -- l_approver_sequence varchar2(150);
4221 --
4222 l_time_attribute_id number;
4223 l_ovn number;
4224 --
4225 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.upd_apr_details';
4226 --
4227 BEGIN
4228 g_debug:=hr_utility.debug_enabled;
4229 --
4230 if g_debug then
4231 hr_utility.set_location(l_proc, 10);
4232 --
4233 hr_utility.trace('l_appl_period_bb_id is : ' || to_char(l_appl_period_bb_id));
4234 hr_utility.trace('l_appl_period_bb_ovn is : ' || to_char(l_appl_period_bb_ovn));
4235 hr_utility.trace('l_approver_id is : ' || to_char(l_approver_id));
4236 hr_utility.trace('l_approved_time is : ' || l_approved_time);
4237 hr_utility.trace('l_approval_comment is : ' || l_approval_comment);
4238 hr_utility.trace('l_approved_status is : ' || l_approved_status);
4239 --
4240 hr_utility.set_location(l_proc, 20);
4241 end if;
4242 -- Perf Rep Fix - SQL ID :3170802
4243 -- Added attribute_category = APPROVAL to the where clause.
4244
4245 update hxc_time_attributes
4246 set attribute4 = l_notified_status,
4247 attribute5 = l_approved_time,
4248 attribute6 = l_approval_comment,
4249 attribute7 = l_approved_status,
4250 attribute8 = l_delegated_for
4251 where time_attribute_id in (select time_attribute_id
4252 from hxc_time_attribute_usages
4253 where time_building_block_id = p_app_bb_id
4254 and time_building_block_ovn = p_app_bb_ovn)
4255 and attribute3 = to_char(l_approver_id)
4256 and attribute_category = 'APPROVAL';
4257 --
4258 if g_debug then
4259 hr_utility.set_location(l_proc, 30);
4260 end if;
4261 --
4262 END upd_apr_details;
4263 --
4264 --
4265 -- Don't need these procedures for now. Not using them but leaving them
4266 -- here in case they are needed in future.
4267 --
4268
4269 --
4270 FUNCTION code_chk (p_code IN VARCHAR2) RETURN BOOLEAN IS
4271 --
4272 l_package_name user_source.name%TYPE;
4273 l_dummy VARCHAR2(1);
4274 l_code BOOLEAN := FALSE;
4275 --
4276 BEGIN
4277 --
4278 l_package_name := SUBSTR(p_code,1,INSTR(p_code,'.')-1);
4279 --
4280 BEGIN
4281 SELECT 'Y' into l_dummy
4282 FROM SYS.OBJ$ O, SYS.SOURCE$ S
4283 WHERE O.OBJ# = S.OBJ#
4284 AND O.TYPE# = 11 --PACKAGE BODY
4285 AND O.OWNER# = USERENV('SCHEMAID')
4286 AND O.NAME = l_package_name
4287 AND S.LINE = 1;
4288 --
4289 l_code := TRUE;
4290 --
4291 EXCEPTION
4292 WHEN OTHERS THEN
4293 l_code := FALSE;
4294 END;
4295 --
4296 RETURN l_code;
4297 --
4298 END code_chk;
4299 ------------------------ is_appr_required ----------------------------
4300 --
4301 PROCEDURE is_appr_required(itemtype IN varchar2,
4302 itemkey IN varchar2,
4306 --
4303 actid IN number,
4304 funcmode IN varchar2,
4305 result IN OUT NOCOPY varchar2) is
4307 cursor csr_get_extension(p_time_recipient number) is
4308 select htr.extension_function1
4309 from hxc_time_recipients htr
4310 where htr.time_recipient_id = p_time_recipient;
4311
4312 cursor c_appr_comp(p_app_bb_id number,p_app_bb_ovn number)
4313 is
4314 select approval_comp_id
4315 from hxc_app_period_summary
4316 where application_period_id = p_app_bb_id
4317 and application_period_ovn = p_app_bb_ovn;
4318
4319 cursor c_app_comp_pm(p_bb_id number,p_bb_ovn number)
4320 is
4321 select hac.approval_comp_id
4322 from hxc_approval_comps hac,
4323 hxc_approval_styles has,
4324 hxc_time_building_blocks htb
4325 where htb.time_building_block_id =p_bb_id
4326 and htb.object_version_number = p_bb_ovn
4327 and htb.approval_style_id = has.approval_style_id
4328 and has.approval_style_id = hac.APPROVAL_STYLE_ID
4329 and hac.approval_mechanism = 'PROJECT_MANAGER'
4330 and hac.parent_comp_id is null
4331 and hac.parent_comp_ovn is null;
4332
4333 --
4334 l_tc_bld_blk_id number;
4335 l_tc_ovn number;
4336 l_time_recipient varchar2(150);
4337 l_ext_func1 varchar2(2000);
4338 l_auto_approval_flag varchar2(1);
4339 l_message varchar2(2000);
4340 l_message_table hxc_self_service_time_deposit.message_table;
4341 l_func_sql varchar2(2000);
4342 l_app_bld_blk_id number;
4343 l_app_ovn number;
4344 l_token_table hxc_deposit_wrapper_utilities.t_simple_table;
4345 l_exception varchar2(10000);
4346 l_approval_component_id number;
4347 --
4348 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.is_appr_required';
4349
4350 --
4351 BEGIN
4352 g_debug:=hr_utility.debug_enabled;
4353
4354 --
4355 if g_debug then
4356 hr_utility.set_location(l_proc, 10);
4357 end if;
4358 --
4359 l_app_bld_blk_id := wf_engine.GetItemAttrNumber
4360 (itemtype => itemtype,
4361 itemkey => itemkey ,
4362 aname => 'APP_BB_ID');
4363 --
4364 if g_debug then
4365 hr_utility.set_location(l_proc, 20);
4366 end if;
4367 --
4368 l_app_ovn := wf_engine.GetItemAttrNumber
4369 (itemtype => itemtype,
4370 itemkey => itemkey ,
4371 aname => 'APP_BB_OVN');
4372 --
4373 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
4374 (itemtype => itemtype,
4375 itemkey => itemkey,
4376 aname => 'TC_BLD_BLK_ID');
4377 --
4378 if g_debug then
4379 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
4380 end if;
4381 --
4382 l_tc_ovn := wf_engine.GetItemAttrNumber
4383 (itemtype => itemtype,
4384 itemkey => itemkey,
4385 aname => 'TC_BLD_BLK_OVN');
4386 --
4387 l_time_recipient := wf_engine.GetItemAttrText(
4388 itemtype => itemtype,
4389 itemkey => itemkey ,
4390 aname => 'TIME_RECIPIENT_ID');
4391
4392 if g_debug then
4393 hr_utility.set_location(l_proc, 30);
4394 end if;
4395 --
4396 -- Sets up global variables for timecard records.
4397 --
4398 hxc_self_service_time_deposit.get_timecard_tables(
4399 p_timecard_id => l_tc_bld_blk_id
4400 ,p_timecard_ovn => l_tc_ovn
4401 ,p_timecard_blocks => g_time_building_blocks
4402 ,p_timecard_app_attributes => g_time_app_attributes
4403 ,p_time_recipient_id => to_number(l_time_recipient));
4404
4405 if g_debug then
4406 hr_utility.set_location(l_proc, 40);
4407 end if;
4408 --
4409 -- Get the package.procedure from the extension_function1 column
4410 -- on hxc_time_recipients.
4411 --
4412 -- package.procedure(
4413 -- p_timecard_building_blocks => g_time_building_blocks,
4414 -- p_time_attributes => g_time_attributes,
4415 -- x_autoapproval_flag => l_auto_approval_flag,
4416 -- x_messages => l_message);
4417 --
4418 -- 115.96 change.
4419 -- Uncommenting this code that was commented out in version 115.91
4420
4421 --added to support OIT desuport
4422
4423 open c_appr_comp(l_app_bld_blk_id,l_app_ovn);
4424 fetch c_appr_comp into l_approval_component_id;
4425 close c_appr_comp;
4426
4427 --In the case of PM mechanism, the approval comp id on the app period summary table will be
4428 --different to the app comp id on approval style, hence we need to fetch it from using timcard id
4429 --for non PM cases the below cursor will not be found hence it retains the app com id found in the above cursor.
4430 --If it is PM case then the original app comp id is fetched.
4431 open c_app_comp_pm(l_tc_bld_blk_id,l_tc_ovn);
4432 fetch c_app_comp_pm into l_approval_component_id;
4433 close c_app_comp_pm;
4434
4435 if(hxc_notification_helper.run_extensions(l_approval_component_id)) then
4436 open csr_get_extension(to_number(l_time_recipient));
4440 IF l_ext_func1 IS NOT NULL THEN
4437 fetch csr_get_extension into l_ext_func1;
4438 close csr_get_extension;
4439 --
4441
4442 if g_debug then
4443 hr_utility.set_location(l_proc, 50);
4444 end if;
4445
4446 IF code_chk(l_ext_func1) THEN
4447
4448 if g_debug then
4449 hr_utility.set_location(l_proc, 60);
4450 end if;
4451 --
4452 l_func_sql := 'BEGIN '||fnd_global.newline
4453 ||l_ext_func1 ||fnd_global.newline
4454 ||'(x_autoapproval_flag => :1' ||fnd_global.newline
4455 ||',x_messages => :2);' ||fnd_global.newline
4456 ||'END;';
4457 --
4458 EXECUTE IMMEDIATE l_func_sql
4459 using IN OUT l_auto_approval_flag,
4460 IN OUT l_message;
4461 --
4462 END IF;
4463 END IF;
4464
4465 ELSE
4466 l_auto_approval_flag := 'N';
4467 END IF;
4468 if g_debug then
4469 hr_utility.set_location(l_proc, 70);
4470 end if;
4471 --
4472 IF l_auto_approval_flag = 'Y' THEN
4473
4474 if g_debug then
4475 hr_utility.set_location(l_proc, 80);
4476 end if;
4477 --
4478 wf_engine.SetItemAttrText(itemtype => itemtype,
4479 itemkey => itemkey,
4480 aname => 'APPR_REQ',
4481 avalue => 'NO');
4482 --
4483 END IF;
4484
4485 if g_debug then
4486 hr_utility.set_location(l_proc, 90);
4487 end if;
4488 --
4489 l_exception := NULL;
4490 --
4491 IF l_message IS NOT NULL THEN
4492
4493 if g_debug then
4494 hr_utility.set_location(l_proc, 100);
4495 end if;
4496 --
4497 l_message_table := hxc_deposit_wrapper_utilities.string_to_messages
4498 (p_message_string => l_message);
4499 --
4500 IF l_message_table.COUNT <> 0 THEN
4501 --
4502 FOR i in l_message_table.first .. l_message_table.last LOOP
4503 --
4504 FND_MESSAGE.SET_NAME
4505 (l_message_table(i).application_short_name
4506 ,l_message_table(i).message_name
4507 );
4508 --
4509 IF l_message_table(i).message_tokens IS NOT NULL THEN
4510 --
4511 -- parse string into a more accessible form
4512 --
4513 hxc_deposit_wrapper_utilities.string_to_table('&',
4514 '&'||l_message_table(i).message_tokens,
4515 l_token_table);
4516 --
4517 FOR l_token in 0..(l_token_table.count/2)-1 LOOP
4518 --
4519 FND_MESSAGE.SET_TOKEN
4520 (TOKEN => l_token_table(2*l_token)
4521 ,VALUE => l_token_table(2*l_token+1)
4522 );
4523 --
4524 END LOOP;
4525 --
4526 END IF;
4527 --
4528 l_exception := SUBSTR((l_exception||fnd_message.get),1,10000);
4529 --
4530 END LOOP;
4531 --
4532 END IF;
4533
4534 if g_debug then
4535 hr_utility.set_location(l_proc, 110);
4536 end if;
4537 --jxtan in new implementation, comment is save in hxc_time_building_blocks
4538 UPDATE hxc_time_building_blocks
4539 SET comment_text = substr(l_exception, 1, 2000)
4540 WHERE time_building_block_id = l_app_bld_blk_id
4541 AND object_version_number = l_app_ovn;
4542
4543 if g_debug then
4544 hr_utility.set_location(l_proc, 120);
4545 end if;
4546 --
4547 wf_engine.SetItemAttrText(itemtype => itemtype,
4548 itemkey => itemkey,
4549 aname => 'EXT_MESSAGE',
4550 avalue => l_exception);
4551 --
4552 END IF;
4553
4554 if g_debug then
4555 hr_utility.set_location(l_proc, 130);
4556 end if;
4557 --
4558 IF upper(wf_engine.GetItemAttrText(itemtype => itemtype,
4559 itemkey => itemkey ,
4560 aname => 'APPR_REQ')) = 'YES' THEN
4561 --
4562 result := 'COMPLETE:Y';
4563 --
4564 if g_debug then
4565 hr_utility.set_location(l_proc, 140);
4566 --
4567 hr_utility.trace('APPR_REQ attribute is : YES');
4568 --
4569 end if;
4570 return;
4571 --
4572 ELSE
4573 --
4574 if g_debug then
4575 hr_utility.set_location(l_proc, 150);
4576 --
4577 hr_utility.trace('APPR_REQ attribute is : NO');
4578 --
4579 end if;
4580 wf_engine.SetItemAttrText(itemtype => itemtype,
4581 itemkey => itemkey,
4582 aname => 'APPROVAL_STATUS',
4583 avalue => 'APPROVED');
4584 --
4585 wf_engine.SetItemAttrText(itemtype => itemtype,
4586 itemkey => itemkey,
4587 aname => 'APR_REJ_REASON',
4588 avalue => 'AUTO_APPROVE');
4589
4590 if g_debug then
4591 hr_utility.set_location(l_proc, 160);
4592 --
4593 --
4594 hr_utility.trace('APPROVAL_STATUS attribute is : APPROVED');
4595 --
4596 end if;
4600 (itemtype,
4597 --OIT Enhancement.
4598 --FYI Notification to WORKER on timecard AUTO APPROVE
4599 HXC_APPROVAL_WF_HELPER.set_notif_attribute_values
4601 itemkey,
4602 hxc_app_comp_notifications_api.c_action_auto_approve,
4603 hxc_app_comp_notifications_api.c_recipient_worker
4604 );
4605 result := 'COMPLETE:N';
4606 return;
4607 --
4608 END IF;
4609 --
4610 if g_debug then
4611 hr_utility.set_location(l_proc, 170);
4612 end if;
4613 --
4614 exception
4615 when others then
4616 -- The line below records this function call in the error system
4617 -- in the case of an exception.
4618 --
4619 if g_debug then
4620 hr_utility.set_location(l_proc, 999);
4621 --
4622 hr_utility.trace('IN EXCEPTION IN is_appr_required');
4623 --
4624 end if;
4625 wf_core.context('HCAPPRWF',
4626 'hxc_approval_wf_pkg.is_appr_required',
4627 itemtype, itemkey, to_char(actid), funcmode);
4628 raise;
4629 result := '';
4630 return;
4631 --
4632 --
4633 END is_appr_required;
4634 --
4635 --
4636 ------------------------ chk_appr_rules ----------------------------
4637 --
4638 PROCEDURE chk_appr_rules(itemtype IN varchar2,
4639 itemkey IN varchar2,
4640 actid IN number,
4641 funcmode IN varchar2,
4642 result IN OUT NOCOPY varchar2) is
4643 --
4644 cursor csr_get_tc_info(p_app_bld_blk_id number,
4645 p_app_ovn number) is
4646 select day.resource_id,
4647 day.time_building_block_id,
4648 day.approval_style_id,
4649 max(day.object_version_number)
4650 from hxc_time_building_blocks day,
4651 hxc_time_building_blocks app
4652 where app.time_building_block_id = p_app_bld_blk_id
4653 and app.object_version_number = p_app_ovn
4654 and app.scope = 'APPLICATION_PERIOD'
4655 and app.resource_id = day.resource_id
4656 and day.scope = 'DAY'
4657 and day.start_time between app.start_time and app.stop_time
4658 group by day.resource_id,
4659 day.time_building_block_id,
4660 day.approval_style_id,
4661 day.object_version_number
4662 order by day.time_building_block_id;
4663 --
4664 cursor csr_get_appr_rule_id(p_appr_style_id number,
4665 p_time_recipient_id varchar2) is
4666 select dru.time_entry_rule_id
4667 from hxc_data_app_rule_usages dru
4668 where dru.approval_style_id = p_appr_style_id
4669 and to_char(dru.time_recipient_id) = p_time_recipient_id;
4670 --
4671 -- l_tc_bld_blk_id number;
4672 -- l_tc_ovn number;
4673 l_app_bld_blk_id number;
4674 l_app_ovn number;
4675 l_tc_date_from date;
4676 l_tc_date_to date;
4677 --
4678 l_tc_resource_id number;
4679 l_tc_appr_style_id hxc_data_app_rule_usages.approval_style_id%type;
4680 l_day_bb_id number;
4681 l_day_ovn number;
4682 l_time_recipient varchar2(150);
4683 l_tc_start_time date;
4684 l_tc_stop_time date;
4685 l_data_appr_rule_id hxc_time_entry_rules.time_entry_rule_id%type;
4686 --
4687 l_cnt number;
4688 -- l_item_key wf_items.item_key%type;
4689 l_current_rule varchar2(1000);
4690 l_all_rules varchar2(1000);
4691 --
4692 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_appr_rules';
4693 --
4694 BEGIN
4695 g_debug:=hr_utility.debug_enabled;
4696 --
4697 if g_debug then
4698 hr_utility.set_location(l_proc, 10);
4699 end if;
4700 --
4701 l_app_bld_blk_id := wf_engine.GetItemAttrNumber
4702 (itemtype => itemtype,
4703 itemkey => itemkey ,
4704 aname => 'APP_BB_ID');
4705 --
4706 if g_debug then
4707 hr_utility.set_location(l_proc, 20);
4708 end if;
4709 --
4710 l_app_ovn := wf_engine.GetItemAttrNumber
4711 (itemtype => itemtype,
4712 itemkey => itemkey ,
4713 aname => 'APP_BB_OVN');
4714 --
4715 if g_debug then
4716 hr_utility.set_location(l_proc, 30);
4717 end if;
4718 --
4719 l_time_recipient := wf_engine.GetItemAttrText
4720 (itemtype => itemtype,
4721 itemkey => itemkey,
4722 aname => 'TIME_RECIPIENT_ID');
4723 --
4724 if g_debug then
4725 hr_utility.set_location(l_proc, 40);
4726 --
4727 hr_utility.trace('l_app_bld_blk_id is : ' || to_char(l_app_bld_blk_id));
4728 hr_utility.trace('l_app_ovn is : ' || to_char(l_app_ovn));
4729 hr_utility.trace('l_time_recipient is : ' || l_time_recipient);
4730 --
4731
4732 hr_utility.set_location(l_proc, 70);
4733 --
4734 end if;
4735 l_tc_appr_style_id := wf_engine.GetItemAttrNumber
4736 (itemtype => itemtype,
4737 itemkey => itemkey,
4738 aname => 'APPROVAL_STYLE_ID');
4739 --
4743 --
4740 if g_debug then
4741 hr_utility.trace('l_tc_appr_style_id is : ' || to_char(l_tc_appr_style_id));
4742 end if;
4744 open csr_get_appr_rule_id(l_tc_appr_style_id,
4745 l_time_recipient);
4746 fetch csr_get_appr_rule_id into l_data_appr_rule_id;
4747 --
4748 if g_debug then
4749 hr_utility.set_location(l_proc, 80);
4750 --
4751 hr_utility.trace('l_data_appr_rule_id is : ' || to_char(l_data_appr_rule_id));
4752 --
4753 end if;
4754 IF csr_get_appr_rule_id%NOTFOUND THEN
4755
4756 CLOSE csr_get_appr_rule_id;
4757 --
4758 result := 'COMPLETE:N';
4759 l_all_rules := 'NO_RULES';
4760 --
4761 if g_debug then
4762 hr_utility.set_location(l_proc, 90);
4763 --
4764 -- hr_utility.trace('Setting Status to Approved');
4765 --
4766 end if;
4767 -- wf_engine.SetItemAttrText(itemtype => itemtype,
4768 -- itemkey => itemkey,
4769 -- aname => 'APPROVAL_STATUS',
4770 -- avalue => 'APPROVED');
4771 --
4772 return;
4773 --
4774 ELSE
4775 --
4776 result := 'COMPLETE:Y';
4777 --
4778 if g_debug then
4779 hr_utility.set_location(l_proc, 100);
4780 end if;
4781 --
4782 l_cnt := 1;
4783 --
4784 LOOP
4785 --
4786 IF l_cnt = 1 THEN
4787 l_all_rules := to_char(l_data_appr_rule_id) || '|';
4788 ELSE
4789 l_all_rules := l_all_rules || to_char(l_data_appr_rule_id) || '|';
4790 END IF;
4791 --
4792 l_cnt := l_cnt + 1;
4793 --
4794 fetch csr_get_appr_rule_id into l_data_appr_rule_id;
4795 exit when csr_get_appr_rule_id%NOTFOUND;
4796 --
4797 END LOOP;
4798
4799 CLOSE csr_get_appr_rule_id;
4800 --
4801 if g_debug then
4802 hr_utility.set_location(l_proc, 110);
4803 --
4804 hr_utility.trace('l_all_rules is : ' || l_all_rules);
4805 --
4806 end if;
4807 END IF;
4808 --
4809 IF l_all_rules <> 'NO_RULES' THEN
4810 --
4811 if g_debug then
4812 hr_utility.set_location(l_proc, 120);
4813 end if;
4814 --
4815 wf_engine.SetItemAttrText(itemtype => itemtype,
4816 itemkey => itemkey,
4817 aname => 'ALL_RULES',
4818 avalue => l_all_rules);
4819 --
4820 if g_debug then
4821 hr_utility.trace('ALL_RULES Attribute is : ' || l_all_rules);
4822 end if;
4823 --
4824 END IF;
4825 --
4826 return;
4827 --
4828 exception
4829 when others then
4830 -- The line below records this function call in the error system
4831 -- in the case of an exception.
4832 --
4833 if g_debug then
4834 hr_utility.set_location(l_proc, 999);
4835 --
4836 hr_utility.trace('IN EXCEPTION IN chk_appr_rules');
4837 --
4838 end if;
4839 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_appr_rules',
4840 itemtype, itemkey, to_char(actid), funcmode);
4841 raise;
4842 result := '';
4843 return;
4844 --
4845 --
4846 END chk_appr_rules;
4847 --
4848 --
4849 ------------------------ find_approval_rule ----------------------------
4850 --
4851 PROCEDURE find_approval_rule(itemtype IN varchar2,
4852 itemkey IN varchar2,
4853 actid IN number,
4854 funcmode IN varchar2,
4855 result IN OUT NOCOPY varchar2) is
4856 --
4857 l_current_rule varchar2(1000);
4858 l_all_rules varchar2(1000);
4859 --
4860 l_app_bld_blk_id number;
4861 l_app_ovn number;
4862 l_cnt number;
4863 -- l_item_key wf_items.item_key%type;
4864 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.find_approval_rule';
4865 --
4866 BEGIN
4867 g_debug:=hr_utility.debug_enabled;
4868 --
4869 if g_debug then
4870 hr_utility.set_location(l_proc, 10);
4871 end if;
4872 --
4873 l_all_rules := wf_engine.GetItemAttrText(itemtype => itemtype,
4874 itemkey => itemkey ,
4875 aname => 'ALL_RULES');
4876 --
4877 if g_debug then
4878 hr_utility.set_location(l_proc, 20);
4879 --
4880 hr_utility.trace('ALL_RULES is : ' || l_all_rules);
4881 --
4882 end if;
4883 l_cnt := instr(l_all_rules, '|');
4884 --
4885 IF l_cnt <> 0 THEN
4886 --
4887 if g_debug then
4888 hr_utility.set_location(l_proc, 30);
4889 end if;
4890 --
4891 l_current_rule := substr(l_all_rules, 1, l_cnt - 1);
4892 l_all_rules := replace(l_all_rules, l_current_rule || '|');
4893 --
4894 if g_debug then
4895 hr_utility.trace('l_current_rule is : ' || l_current_rule);
4896 end if;
4897 --
4898 result := 'COMPLETE:Y';
4899 --
4900 ELSE
4901 --
4902 if g_debug then
4903 hr_utility.set_location(l_proc, 40);
4904 end if;
4905 --
4906 result := 'COMPLETE:N';
4910 end if;
4907 --
4908 if g_debug then
4909 hr_utility.trace('No More Rules - Setting status to APPROVED');
4911 --
4912 wf_engine.SetItemAttrText(itemtype => itemtype,
4913 itemkey => itemkey,
4914 aname => 'APPROVAL_STATUS',
4915 avalue => 'APPROVED');
4916
4917
4918 wf_engine.SetItemAttrText(itemtype => itemtype,
4919 itemkey => itemkey,
4920 aname => 'APR_REJ_REASON',
4921 avalue => 'AUTO_APPROVE');
4922 --OIT Enhancement.
4923 --FYI Notification to WORKER on timecard AUTO APPROVE
4924 hxc_approval_wf_helper.set_notif_attribute_values
4925 (itemtype,
4926 itemkey,
4927 hxc_app_comp_notifications_api.c_action_auto_approve,
4928 hxc_app_comp_notifications_api.c_recipient_worker
4929 );
4930
4931
4932 return;
4933 --
4934 END IF;
4935 --
4936 wf_engine.SetItemAttrText(itemtype => itemtype,
4937 itemkey => itemkey,
4938 aname => 'CURRENT_RULE',
4939 avalue => l_current_rule);
4940 --
4941 if g_debug then
4942 hr_utility.set_location(l_proc, 60);
4943 --
4944 hr_utility.trace('CURRENT_RULE is : ' || l_current_rule);
4945 --
4946 end if;
4947 wf_engine.SetItemAttrText(itemtype => itemtype,
4948 itemkey => itemkey,
4949 aname => 'ALL_RULES',
4950 avalue => l_all_rules);
4951 --
4952 if g_debug then
4953 hr_utility.set_location(l_proc, 60);
4954 --
4955 hr_utility.trace('ALL_RULES is : ' || l_all_rules);
4956 --
4957 end if;
4958 return;
4959 --
4960 exception
4961 when others then
4962 -- The line below records this function call in the error system
4963 -- in the case of an exception.
4964 --
4965 if g_debug then
4966 hr_utility.set_location(l_proc, 999);
4967 --
4968 hr_utility.trace('IN EXCEPTION IN find_approval_rule');
4969 --
4970 end if;
4971 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.find_approval_rule',
4972 itemtype, itemkey, to_char(actid), funcmode);
4973 raise;
4974 result := '';
4975 return;
4976 --
4977 --
4978 END find_approval_rule;
4979 --
4980 --
4981 FUNCTION was_approved(
4982 p_app_period_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4983 ,p_app_period_ovn IN hxc_time_building_blocks.object_version_number%TYPE
4984 )
4985 RETURN BOOLEAN
4986 IS
4987 CURSOR c_was_approved(
4988 p_app_period_id IN hxc_time_building_blocks.time_building_block_id%TYPE
4989 ,p_app_period_ovn IN hxc_time_building_blocks.object_version_number%TYPE
4990 )
4991 IS
4992 SELECT 'Y'
4993 FROM hxc_time_building_blocks
4994 WHERE time_building_block_id = p_app_period_id
4995 AND object_version_number < p_app_period_ovn
4996 AND approval_status = 'APPROVED';
4997
4998 l_was_approved VARCHAR2(1) := 'N';
4999 BEGIN
5000 OPEN c_was_approved(p_app_period_id, p_app_period_ovn);
5001 FETCH c_was_approved INTO l_was_approved;
5002 CLOSE c_was_approved;
5003
5004 IF l_was_approved = 'Y'
5005 THEN
5006 RETURN TRUE;
5007 END IF;
5008
5009 RETURN FALSE;
5010
5011 END was_approved;
5012
5013 FUNCTION same_no_blocks(
5014 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5015 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5016 )
5017 RETURN BOOLEAN
5018 IS
5019 CURSOR c_no_blocks(
5020 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5021 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5022 )
5023 IS
5024 SELECT COUNT(*)
5025 FROM hxc_time_building_blocks
5026 START WITH time_building_block_id = p_timecard_id
5027 AND object_version_number = p_timecard_ovn
5028 CONNECT by prior time_building_block_id =
5029 parent_building_block_id
5030 and prior object_version_number =
5031 parent_building_block_ovn;
5032
5033 CURSOR c_old_tc(
5034 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE
5035 ,p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5036 )
5037 IS
5038 SELECT object_version_number
5039 FROM hxc_time_building_blocks
5040 WHERE time_building_block_id = p_timecard_id
5041 AND approval_status = 'SUBMITTED'
5042 AND object_version_number < p_timecard_ovn
5043 ORDER BY object_version_number desc;
5044
5045 l_current_tc_count NUMBER;
5046 l_old_tc_count NUMBER;
5047 l_previous_tc_ovn hxc_time_building_blocks.object_version_number%TYPE := NULL;
5048 BEGIN
5049 OPEN c_old_tc(p_timecard_id, p_timecard_ovn);
5050 FETCH c_old_tc INTO l_previous_tc_ovn;
5051 CLOSE c_old_tc;
5052
5053 IF l_previous_tc_ovn IS NULL
5054 THEN
5055 RETURN FALSE;
5056 END IF;
5057
5058
5059 OPEN c_no_blocks(p_timecard_id, p_timecard_ovn);
5060 FETCH c_no_blocks INTO l_current_tc_count;
5061 CLOSE c_no_blocks;
5062
5063 OPEN c_no_blocks(p_timecard_id, l_previous_tc_ovn);
5064 FETCH c_no_blocks INTO l_old_tc_count;
5065 CLOSE c_no_blocks;
5066
5067 IF l_current_tc_count = l_old_tc_count
5068 THEN
5069 RETURN TRUE;
5070 END IF;
5071
5072 RETURN FALSE;
5073 END same_no_blocks;
5074
5075
5076
5077 ------------------------ execute_appr_rule ----------------------------
5078 --
5079 PROCEDURE execute_appr_rule(itemtype IN varchar2,
5080 itemkey IN varchar2,
5081 actid IN number,
5082 funcmode IN varchar2,
5083 result IN OUT NOCOPY varchar2) is
5084 --
5085 cursor csr_get_appr_rule_info(p_data_appr_rule_id number,
5086 p_end_date date) is
5087 select dar.name
5088 ,NVL( dar.description, dar.name ) ter_message_name
5089 ,dar.rule_usage
5090 ,dar.formula_id
5091 ,dar.mapping_id
5092 ,dar.attribute1
5093 ,dar.attribute2
5094 ,dar.attribute3
5095 ,dar.attribute4
5096 ,dar.attribute5
5097 ,dar.attribute6
5098 ,dar.attribute7
5099 ,dar.attribute8
5100 ,dar.attribute9
5101 ,dar.attribute10
5102 ,dar.attribute11
5103 ,dar.attribute12
5104 ,dar.attribute13
5105 ,dar.attribute14
5106 ,dar.attribute15
5107 ,ff.formula_name
5108 ,''
5109 from ff_formulas_f ff
5110 ,hxc_time_entry_rules dar
5111 where dar.time_entry_rule_id = p_data_appr_rule_id
5112 and p_end_date between dar.start_date and dar.end_date
5113 and ff.formula_id(+) = dar.formula_id
5114 and dar.start_date BETWEEN ff.effective_start_date(+)
5115 AND ff.effective_end_date(+)
5116 order by dar.start_date;
5117
5118 CURSOR csr_get_tc_dates ( p_bb_id NUMBER, p_bb_ovn NUMBER )IS
5119 SELECT start_time, stop_time
5120 FROM hxc_time_building_blocks
5121 WHERE time_building_block_id = p_bb_id
5122 AND object_version_number = p_bb_ovn;
5123 --
5124 l_data_appr_rule_id hxc_time_entry_rules.time_entry_rule_id%type;
5125 l_rule_usage hxc_time_entry_rules.rule_usage%type;
5126 l_formula_id hxc_time_entry_rules.formula_id%type;
5127 l_tc_start_date date;
5128 l_tc_end_date date;
5129 --
5130 l_app_start_date date;
5131 l_app_end_date date;
5132 l_tc_resource_id number;
5133 l_tc_bld_blk_id number;
5134 l_tc_ovn number;
5135 l_current_rule varchar2(1000);
5136 l_all_rules varchar2(1000);
5137 --
5141 --
5138 l_rule_rec hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype;
5139 l_outputs ff_exec.outputs_t;
5140 l_result varchar2(1000);
5142 l_error_table hxc_self_service_time_deposit.message_table;
5143 l_mapping_changed boolean;
5144 l_bld_blk_changed boolean;
5145 l_changed varchar2(10);
5146 l_cnt number;
5147 l_resubmit varchar2(10);
5148 l_bb_new varchar2(10);
5149 l_appl_period_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
5150 l_appl_period_bb_ovn hxc_time_building_blocks.object_version_number%TYPE;
5151 -- l_item_key wf_items.item_key%type;
5152 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.execute_appr_rule';
5153 --
5154 BEGIN
5155 g_debug:=hr_utility.debug_enabled;
5156 --
5157 if g_debug then
5158 hr_utility.set_location(l_proc, 10);
5159 end if;
5160 --
5161
5162
5163 l_current_rule := wf_engine.GetItemAttrText(itemtype => itemtype,
5164 itemkey => itemkey,
5165 aname => 'CURRENT_RULE');
5166 --
5167 if g_debug then
5168 hr_utility.set_location(l_proc, 20);
5169 end if;
5170 --
5171 l_app_start_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5172 itemkey => itemkey,
5173 aname => 'APP_START_DATE');
5174 --
5175 if g_debug then
5176 hr_utility.set_location(l_proc, 25);
5177 end if;
5178 --
5179 l_app_end_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
5180 itemkey => itemkey,
5181 aname => 'APP_END_DATE');
5182 --
5183 if g_debug then
5184 hr_utility.set_location(l_proc, 30);
5185 end if;
5186 --
5187 l_tc_resource_id := wf_engine.GetItemAttrNumber(
5188 itemtype => itemtype,
5189 itemkey => itemkey,
5190 aname => 'RESOURCE_ID');
5191 --
5192 if g_debug then
5193 hr_utility.set_location(l_proc, 40);
5194 end if;
5195 --
5196 l_tc_bld_blk_id := wf_engine.GetItemAttrNumber
5197 (itemtype => itemtype,
5198 itemkey => itemkey,
5199 aname => 'TC_BLD_BLK_ID');
5200 --
5201 if g_debug then
5202 hr_utility.trace('Timecard BB ID is : ' || to_char(l_tc_bld_blk_id));
5203 end if;
5204 --
5205 l_tc_ovn := wf_engine.GetItemAttrNumber
5206 (itemtype => itemtype,
5207 itemkey => itemkey,
5208 aname => 'TC_BLD_BLK_OVN');
5209 --
5210 if g_debug then
5211 hr_utility.trace('Timecard BB OVN is : ' || to_char(l_tc_ovn));
5212 end if;
5213 --
5214 -- Set up l_resubmit to indicate whether this is a resubmission or not.
5215 -- (YES means it is a resubmission; NO means it is a submission).
5216 --
5217 l_resubmit := wf_engine.GetItemAttrText(itemtype => itemtype,
5218 itemkey => itemkey,
5219 aname => 'TC_RESUBMITTED');
5220 --
5221 if g_debug then
5222 hr_utility.set_location(l_proc, 50);
5223 end if;
5224 --
5225 l_bb_new := wf_engine.GetItemAttrText(itemtype => itemtype,
5226 itemkey => itemkey,
5227 aname => 'BB_NEW');
5228 --
5229 if g_debug then
5230 hr_utility.set_location(l_proc, 52);
5231 end if;
5232 --
5233 l_data_appr_rule_id := to_number(l_current_rule);
5234 --
5235 if g_debug then
5236 hr_utility.trace('l_current_rule is : ' || l_current_rule);
5237 hr_utility.trace('l_app_end_date is : ' ||
5238 to_char(l_app_end_date, 'DD-MM-YYYY'));
5239 hr_utility.trace('l_tc_resource_id is : ' || to_char(l_tc_resource_id));
5240 hr_utility.trace('l_resubmit is : ' || l_resubmit);
5241 end if;
5242 --
5243 open csr_get_appr_rule_info(l_current_rule, l_app_end_date);
5244 fetch csr_get_appr_rule_info into l_rule_rec;
5245 close csr_get_appr_rule_info;
5246 --
5247 if g_debug then
5248 hr_utility.set_location(l_proc, 60);
5249 end if;
5250 --
5251 IF (l_resubmit = 'YES' AND l_rule_rec.rule_usage <> 'SUBMISSION') OR
5252 (l_resubmit = 'NO' AND l_rule_rec.rule_usage <> 'RESUBMISSION') THEN
5253 --
5254 if g_debug then
5255 hr_utility.set_location(l_proc, 70);
5256 end if;
5257 --
5258 -- Apply rule
5259 --
5260 IF l_rule_rec.mapping_id IS NOT NULL THEN
5261 --
5262 -- Mapping needs to be checked. If any of the fields in the
5263 -- mapping have changed, and it is a resubmission
5264 -- then need to approve this timecard. Set l_changed to YES
5265 -- or NO accordingly, and use it to decide whether the formula,
5266 -- if there is one, needs to be applied.
5267 -- Then, check for formula, since it is possible to have both
5268 -- a mapping and a formula.
5269 --
5270 if g_debug then
5271 hr_utility.set_location(l_proc, 80);
5272 end if;
5273 --
5274 IF l_resubmit = 'YES' THEN
5275 --
5276 if g_debug then
5277 hr_utility.set_location(l_proc, 90);
5278 end if;
5279 l_appl_period_bb_id := wf_engine.GetItemAttrNumber(
5280 itemtype => itemtype,
5284 if g_debug then
5281 itemkey => itemkey,
5282 aname => 'APP_BB_ID');
5283
5285 hr_utility.trace('APP_BB_ID is : ' || to_char(l_appl_period_bb_id));
5286 end if;
5287 l_appl_period_bb_ovn := wf_engine.GetItemAttrNumber(
5288 itemtype => itemtype,
5289 itemkey => itemkey,
5290 aname => 'APP_BB_OVN');
5291
5292 IF NOT was_approved(l_appl_period_bb_id, l_appl_period_bb_ovn)
5293 THEN
5294 wf_engine.SetItemAttrText(itemtype => itemtype,
5295 itemkey => itemkey,
5296 aname => 'TO_APPROVE',
5297 avalue => 'YES');
5298
5299 result := '';
5300 RETURN;
5301 END IF;
5302
5303 --
5304 -- Check to see if any values in hxc_time_building_blocks have
5305 -- changed for the submitted timecard.
5306 --
5307 IF l_bb_new = 'YES' THEN
5308 l_bld_blk_changed := TRUE;
5309 ELSE
5310 l_bld_blk_changed := same_no_blocks(l_tc_bld_blk_id, l_tc_ovn);
5311
5312 IF NOT l_bld_blk_changed
5313 THEN
5314 l_bld_blk_changed := hxc_mapping_utilities.chk_bld_blk_changed (
5315 p_timecard_bb_id => l_tc_bld_blk_id
5316 ,p_timecard_ovn => l_tc_ovn
5317 ,p_start_date => l_app_start_date
5318 ,p_end_date => l_app_end_date
5319 ,p_last_status => 'SUBMITTED'
5320 ,p_time_bld_blks => g_time_building_blocks);
5321 END IF;
5322 END IF;
5323 --
5324 -- Check to see if the mappings have changed for the submitted
5325 -- timecard, if nothing in hxc_time_building_blocks has changed.
5326 --
5327 IF l_bld_blk_changed THEN
5328 --
5329 if g_debug then
5330 hr_utility.set_location(l_proc, 130);
5331 end if;
5332 --
5333 l_changed := 'YES';
5334 --
5335 ELSE
5336 --
5337 if g_debug then
5338 hr_utility.set_location(l_proc, 140);
5339 end if;
5340 --
5341 l_mapping_changed := hxc_mapping_utilities.chk_mapping_changed(
5342 p_mapping_id => l_rule_rec.mapping_id
5343 ,p_timecard_bb_id => l_tc_bld_blk_id
5344 ,p_timecard_ovn => l_tc_ovn
5345 ,p_start_date => l_app_start_date
5346 ,p_end_date => l_app_end_date
5347 ,p_last_status => 'SUBMITTED'
5348 ,p_time_building_blocks => g_time_building_blocks
5349 ,p_time_attributes => g_time_attributes);
5350 --
5351 if g_debug then
5352 hr_utility.set_location(l_proc, 120);
5353 end if;
5354 --
5355 -- If there are differences, set l_changed to YES; else set to NO.
5356 --
5357 IF l_mapping_changed THEN
5358 --
5359 if g_debug then
5360 hr_utility.set_location(l_proc, 130);
5361 end if;
5362 --
5363 l_changed := 'YES';
5364 --
5365 ELSE
5366 --
5367 if g_debug then
5368 hr_utility.set_location(l_proc, 140);
5369 end if;
5370 --
5371 l_changed := 'NO';
5372 --
5373 END IF;
5374 --
5375 END IF;
5376 --
5377 ELSE -- not a resubmission, so fields in mapping are new.
5378 --
5379 -- Set l_changed to YES
5380 --
5381 if g_debug then
5382 hr_utility.set_location(l_proc, 150);
5383 end if;
5384 --
5385 l_changed := 'YES';
5386 --
5387 END IF;
5388 --
5389 ELSE
5390 --
5391 -- No Mapping ID, but there might still be a formula so set
5392 -- l_changed to YES
5393 --
5394 if g_debug then
5395 hr_utility.set_location(l_proc, 160);
5396 end if;
5397 --
5398 l_changed := 'NO'; -- GPM v115.48 WWB 2724576
5399 --
5400 END IF;
5401 --
5402 if g_debug then
5403 hr_utility.set_location(l_proc, 170);
5404 end if;
5405 --
5406 -- Check to see is a formula needs to be applied.
5407 --
5408 IF (l_rule_rec.formula_id IS NOT NULL AND l_changed = 'NO') -- GPM v115.48 WWB 2724576
5409 THEN
5410 --
5411 if g_debug then
5412 hr_utility.set_location(l_proc, 180);
5413 --
5414 hr_utility.trace('l_formula_name is : ' || l_rule_rec.formula_name);
5415 --
5416 hr_utility.set_location(l_proc, 190);
5417 end if;
5418 --
5419 -- call execute approval formula
5420 --
5421
5422 -- get tc period start and stop times
5423
5424 OPEN csr_get_tc_dates ( l_tc_bld_blk_id, l_tc_ovn );
5428 l_error_table.delete;
5425 FETCH csr_get_tc_dates INTO l_tc_start_date, l_tc_end_date;
5426 CLOSE csr_get_tc_dates;
5427
5429 --
5430 -- GPM v115.21
5431 l_result := hxc_ff_dict.execute_approval_formula(
5432 p_resource_id => l_tc_resource_id
5433 ,p_period_start_date => l_app_start_date
5434 ,p_period_end_date => l_app_end_date
5435 ,p_tc_period_start_date => l_tc_start_date
5436 ,p_tc_period_end_date => l_tc_end_date
5437 ,p_rule_rec => l_rule_rec
5438 ,p_message_table => l_error_table);
5439 --
5440 IF upper(l_result) = 'Y' THEN
5441 l_result := 'YES';
5442 END IF;
5443 --
5444 IF upper(l_result) = 'N' THEN
5445 l_result := 'NO';
5446 END IF;
5447 --
5448 IF upper(l_result) <> 'YES' AND upper(l_result) <> 'NO' THEN
5449 hr_utility.raise_error;
5450 END IF;
5451 --
5452 IF l_error_table.count > 0 THEN
5453 hr_utility.set_message(809, l_error_table(1).message_name);
5454 hr_utility.raise_error;
5455 END IF;
5456 --
5457 wf_engine.SetItemAttrText(itemtype => itemtype,
5458 itemkey => itemkey,
5459 aname => 'TO_APPROVE',
5460 avalue => upper(l_result));
5461 --
5462 if g_debug then
5463 hr_utility.trace('TO_APPROVE is : ' || l_result);
5464 end if;
5465 --
5466 ELSE
5467 --
5468 -- Set TO_APPROVE to l_changed.
5469 --
5470 if g_debug then
5471 hr_utility.set_location(l_proc, 230);
5472 end if;
5473 --
5474 wf_engine.SetItemAttrText(itemtype => itemtype,
5475 itemkey => itemkey,
5476 aname => 'TO_APPROVE',
5477 avalue => l_changed);
5478 --
5479 if g_debug then
5480 hr_utility.trace('TO_APPROVE is : ' || l_changed);
5481 end if;
5482 --
5483 END IF;
5484 --
5485 ELSE
5486 --
5487 -- Set TO_APPROVE attribute to YES since the rule does not apply.
5488 --
5489 if g_debug then
5490 hr_utility.set_location(l_proc, 240);
5491 end if;
5492 --
5493 -- Modifying the 'TO_APPROVE' value from 'NO' to 'YES' and Commenting out the following code for bug#3497011.
5494
5495 /* wf_engine.SetItemAttrText(itemtype => itemtype,
5496 itemkey => itemkey,
5497 aname => 'TO_APPROVE',
5498 avalue => 'NO');
5499 --
5500 if g_debug then
5501 hr_utility.trace('TO_APPROVE is : NO');
5502 end if; */
5503
5504 wf_engine.SetItemAttrText(itemtype => itemtype,
5505 itemkey => itemkey,
5506 aname => 'TO_APPROVE',
5507 avalue => 'YES');
5508 --
5509 if g_debug then
5510 hr_utility.trace('TO_APPROVE is : YES');
5511 end if;--
5512 END IF;
5513 --
5514 if g_debug then
5515 hr_utility.set_location(l_proc, 250);
5516 end if;
5517 --
5518 --
5519 result := '';
5520 return;
5521 --
5522 exception
5523 when others then
5524 -- The line below records this function call in the error system
5525 -- in the case of an exception.
5526 if g_debug then
5527 hr_utility.set_location(l_proc, 999);
5528 --
5529 hr_utility.trace('IN EXCEPTION IN execute_appr_rule');
5530 end if;
5531 --
5532 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.execute_appr_rule',
5533 itemtype, itemkey, to_char(actid), funcmode);
5534 raise;
5535 result := '';
5536 return;
5537 --
5538 --
5539 END execute_appr_rule;
5540 --
5541 --
5542 ------------------------ chk_approval_req ----------------------------
5543 --
5544 PROCEDURE chk_approval_req(itemtype IN varchar2,
5545 itemkey IN varchar2,
5546 actid IN number,
5547 funcmode IN varchar2,
5548 result IN OUT NOCOPY varchar2) is
5549 --
5550 l_current_rule varchar2(1000);
5551 l_all_rules varchar2(1000);
5552 l_to_approve varchar2(1000);
5553 --
5554 l_cnt number;
5555 -- l_item_key wf_items.item_key%type;
5556 l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.chk_approval_req';
5557 --
5558 BEGIN
5559 g_debug:=hr_utility.debug_enabled;
5560 --
5561 if g_debug then
5562 hr_utility.set_location(l_proc, 10);
5563 end if;
5564 --
5565 l_to_approve := wf_engine.GetItemAttrText(itemtype => itemtype,
5566 itemkey => itemkey ,
5567 aname => 'TO_APPROVE');
5568 --
5569 if g_debug then
5570 hr_utility.set_location(l_proc, 20);
5571 end if;
5572 --
5573 if g_debug then
5574 hr_utility.trace('l_to_approve is : ' || l_to_approve);
5575 end if;
5576 --
5577 IF l_to_approve = 'YES' THEN
5578 --
5579 if g_debug then
5580 hr_utility.set_location(l_proc, 30);
5581 end if;
5582 --
5583 result := 'COMPLETE:Y';
5584 --
5585 return;
5586 --
5587 ELSE
5588 --
5589 if g_debug then
5590 hr_utility.set_location(l_proc, 40);
5591 end if;
5592 --
5593 result := 'COMPLETE:N';
5594 --
5595 return;
5596 --
5597 END IF;
5598 --
5599 exception
5600 when others then
5601 -- The line below records this function call in the error system
5602 -- in the case of an exception.
5603 --
5604 if g_debug then
5605 hr_utility.set_location(l_proc, 999);
5606 --
5607 hr_utility.trace('IN EXCEPTION IN chk_approval_req');
5608 end if;
5609 --
5610 wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.chk_approval_req',
5611 itemtype, itemkey, to_char(actid), funcmode);
5612 raise;
5613 --
5614 result := '';
5615 return;
5616 --
5617 --
5618 END chk_approval_req;
5619 --
5620 --------------------------- get_override -------------------------------
5621 --
5622 FUNCTION get_override(p_timecard_bb_id NUMBER
5623 ,p_timecard_ovn NUMBER) RETURN NUMBER IS
5624 --
5625 l_return hxc_time_building_blocks.resource_id%TYPE;
5626 --
5627 cursor csr_get_override_id is
5628 select to_number(ta.attribute10)
5629 from hxc_time_attributes ta,
5630 hxc_time_attribute_usages tau,
5631 hxc_time_building_blocks tbb
5632 where tbb.time_building_block_id = p_timecard_bb_id
5633 and tbb.object_version_number = p_timecard_ovn
5634 and tbb.time_building_block_id = tau.time_building_block_id
5635 and tbb.object_version_number = tau.time_building_block_ovn
5636 and ta.time_attribute_id = tau.time_attribute_id
5637 and ta.attribute_category = 'APPROVAL';
5638 --
5639 --
5640 BEGIN
5641 --
5642 OPEN csr_get_override_id;
5643 FETCH csr_get_override_id INTO l_return;
5644 CLOSE csr_get_override_id;
5645 --
5646 RETURN l_return;
5647 --
5648 end get_override;
5649
5650 PROCEDURE is_different_time_category (itemtype IN varchar2,
5651 itemkey IN varchar2,
5652 actid IN number,
5653 funcmode IN varchar2,
5654 result IN OUT NOCOPY varchar2) is
5655
5656 l_is_blank varchar2(1);
5657 l_total_hours number;
5658 l_app_bb_id number;
5659 l_approval_mechansim varchar2(20);
5660
5661 begin
5662
5663 l_is_blank := wf_engine.GetItemAttrText(itemtype => itemtype,
5664 itemkey => itemkey ,
5665 aname => 'IS_DIFF_TC',
5669 l_app_bb_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
5670 itemkey => itemkey ,
5671 aname => 'APP_BB_ID');
5672
5673 if l_is_blank = 'Y' then
5674
5675 l_total_hours:= HXC_FIND_NOTIFY_APRS_PKG.category_timecard_hrs(l_app_bb_id,'');
5676
5677 wf_engine.SetItemAttrNumber(
5678 itemtype => itemtype,
5679 itemkey => itemkey,
5680 aname => 'TOTAL_TC_HOURS',
5681 avalue => l_total_hours);
5682
5683 result := 'COMPLETE:Y';
5684 else
5685
5686 result := 'COMPLETE:N';
5687
5688 end if;
5689
5690 end is_different_time_category;
5691 --
5692 --
5693 end hxc_approval_wf_pkg;5668
5666 ignore_notfound => true
5667 );