[Home] [Help]
PACKAGE BODY: APPS.HXC_TIME_ENTRY_RULES_UTILS_PKG
Source
1 Package Body hxc_time_entry_rules_utils_pkg as
2 /* $Header: hxcterutl.pkb 120.23.12020000.2 2012/09/12 07:44:10 bbayragi ship $ */
3 --
4 -- Package Variables
5 --
6
7 g_debug boolean := hr_utility.debug_enabled;
8
9 TYPE r_message_record IS RECORD ( name fnd_new_messages.message_name%TYPE,
10 token_name varchar2(240),
11 token_value varchar2(4000),
12 extent varchar2(20) );
13 TYPE t_message_table IS TABLE OF r_message_record INDEX BY BINARY_INTEGER;
14
15
16
17 PROCEDURE get_timecard_info (
18 p_time_building_blocks hxc_self_service_time_deposit.timecard_info
19 , p_timecard_rec IN OUT NOCOPY r_timecard_info ) IS
20
21 l_proc VARCHAR2(72);
22
23 l_tbb_index BINARY_INTEGER;
24
25 deletedFlag boolean := true;
26
27 BEGIN
28 g_debug := hr_utility.debug_enabled;
29
30 if g_debug then
31 l_proc := g_package||'get_timecard_info';
32 hr_utility.set_location('Entering '||l_proc, 10);
33 end if;
34
35 l_tbb_index := p_time_building_blocks.FIRST;
36
37 WHILE ( l_tbb_index IS NOT NULL )
38 LOOP
39
40 IF p_time_building_blocks(l_tbb_index).SCOPE = 'TIMECARD'
41 THEN
42 if g_debug then
43 hr_utility.trace('************* Timecard details *****************');
44 hr_utility.trace('timecard bb id is '||to_char(p_time_building_blocks(l_tbb_index).time_building_block_id));
45 hr_utility.trace('timecard ovn is '||to_char(p_time_building_blocks(l_tbb_index).object_version_number));
46 hr_utility.trace('start time is '||to_char(p_time_building_blocks(l_tbb_index).start_time, 'dd-mon-yyyy hh24:mi:ss'));
47 hr_utility.trace('stop time is ' ||to_char(p_time_building_blocks(l_tbb_index).stop_time, 'dd-mon-yyyy hh24:mi:ss'));
48 hr_utility.trace('resource id is '||to_char(p_time_building_blocks(l_tbb_index).resource_id));
49 hr_utility.trace('************************************************');
50 end if;
51
52 p_timecard_rec.start_date := p_time_building_blocks(l_tbb_index).start_time;
53 p_timecard_rec.end_date := p_time_building_blocks(l_tbb_index).stop_time;
54 p_timecard_rec.resource_id := p_time_building_blocks(l_tbb_index).resource_id;
55 p_timecard_rec.timecard_bb_id := p_time_building_blocks(l_tbb_index).time_building_block_id;
56 p_timecard_rec.timecard_ovn := p_time_building_blocks(l_tbb_index).object_version_number;
57 p_timecard_rec.approval_status := p_time_building_blocks(l_tbb_index).approval_status;
58
59 -- GPM v115.32
60 p_timecard_rec.new := p_time_building_blocks(l_tbb_index).new;
61
62 IF ( p_time_building_blocks(l_tbb_index).date_to = hr_general.end_of_time )
63 THEN
64 --p_timecard_rec.deleted := 'N';
65 deletedFlag := false;
66 EXIT;
67 -- ELSE
68 --p_timecard_rec.deleted := 'Y';
69 END IF;
70 -- EXIT;
71 END IF;
72
73 l_tbb_index := p_time_building_blocks.NEXT(l_tbb_index);
74
75 END LOOP; -- get timecard info loop
76
77 if(deletedFlag) then
78 p_timecard_rec.deleted := 'Y';
79 else
80 p_timecard_rec.deleted := 'N';
81 end if;
82
83
84 if g_debug then
85 hr_utility.set_location('Leaving '||l_proc, 20);
86 end if;
87
88 END get_timecard_info;
89
90
91 PROCEDURE get_timecard_info (
92 p_time_building_blocks hxc_self_service_time_deposit.timecard_info
93 , p_time_attributes hxc_self_service_time_deposit.building_block_attribute_info
94 , p_timecard_rec IN OUT NOCOPY r_timecard_info ) IS
95
96 l_proc VARCHAR2(72);
97
98 l_att_index BINARY_INTEGER;
99
100 BEGIN
101 g_debug := hr_utility.debug_enabled;
102
103 if g_debug then
104 l_proc := g_package||'get_timecard_info';
105 hr_utility.set_location('Entering '||l_proc, 10);
106 end if;
107
108 get_timecard_info ( p_time_building_blocks => p_time_building_blocks
109 , p_timecard_rec => p_timecard_rec );
110
111 l_att_index := p_time_attributes.FIRST;
112
113 WHILE ( l_att_index IS NOT NULL )
114 LOOP
115
116 IF ( p_time_attributes(l_att_index).attribute_category = 'SECURITY' )
117 THEN
118 if g_debug then
119 hr_utility.set_location('Processing '||l_proc, 30);
120 end if;
121
122 p_timecard_rec.bg_id := TO_NUMBER(p_time_attributes(l_att_index).attribute2);
123
124 EXIT;
125
126 END IF;
127
128 l_att_index := p_time_attributes.NEXT(l_att_index);
129
130 END LOOP;
131
132 if g_debug then
133 hr_utility.set_location('Leaving '||l_proc, 20);
134 end if;
135
136 END get_timecard_info;
137
138 PROCEDURE get_timecard_info (
139 p_time_building_blocks HXC_BLOCK_TABLE_TYPE
140 , p_timecard_rec IN OUT NOCOPY r_timecard_info ) IS
141
142 l_blocks hxc_self_service_time_deposit.timecard_info;
143
144 BEGIN
145
146 l_blocks := hxc_timecard_block_utils.convert_to_dpwr_blocks(
147 p_blocks => p_time_building_blocks );
148
149 get_timecard_info ( p_time_building_blocks => l_blocks
150 , p_timecard_rec => p_timecard_rec );
151
152 END get_timecard_info;
153
154
155
156 PROCEDURE calc_timecard_periods (
157 p_timecard_period_start DATE
158 , p_timecard_period_end DATE
159 , p_period_start_date DATE
160 , p_period_end_date DATE
161 , p_duration_in_days NUMBER
162 , p_periods_tab IN OUT NOCOPY t_period ) IS
163
164 l_proc VARCHAR2(72);
165
166 l_cnt BINARY_INTEGER := 1;
167 l_period_start_date DATE;
168 l_period_end_date DATE;
169
170 BEGIN
171 g_debug := hr_utility.debug_enabled;
172
173 if g_debug then
174 l_proc := g_package||'calc_timecard_periods';
175 hr_utility.set_location('Entering '||l_proc, 10);
176 end if;
177
178 l_period_start_date := p_period_start_date;
179 l_period_end_date := p_period_end_date;
180
181 WHILE ( l_period_start_date <= p_timecard_period_end )
182 LOOP
183
184 if g_debug then
185 hr_utility.set_location('Processing '||l_proc, 20);
186 end if;
187
188
189 IF ( l_period_start_date >= p_timecard_period_start AND
190 TRUNC(l_period_end_date) <= p_timecard_period_end )
191 THEN
192
193 if g_debug then
194 hr_utility.set_location('Processing '||l_proc, 30);
195 end if;
196
197 -- period completely within TCO
198
199 p_periods_tab(l_cnt).period_start := l_period_start_date;
200 p_periods_tab(l_cnt).period_end := l_period_end_date;
201
202 p_periods_tab(l_cnt).db_pre_period_start := NULL;
203 p_periods_tab(l_cnt).db_pre_period_end := NULL;
204 p_periods_tab(l_cnt).db_post_period_start := NULL;
205 p_periods_tab(l_cnt).db_post_period_end := NULL;
206
207 ELSIF ( l_period_start_date < p_timecard_period_start AND
208 TRUNC(l_period_end_date) <= p_timecard_period_end )
209 THEN
210
211 if g_debug then
212 hr_utility.set_location('Processing '||l_proc, 40);
213 end if;
214
215 -- period includes TC and stradles TCO start
216
217 p_periods_tab(l_cnt).period_start := p_timecard_period_start;
218 p_periods_tab(l_cnt).period_end := l_period_end_date;
219
220 p_periods_tab(l_cnt).db_pre_period_start := l_period_start_date;
221 p_periods_tab(l_cnt).db_pre_period_end :=
222 TO_DATE(TO_CHAR((p_timecard_period_start-1), 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
223
224 p_periods_tab(l_cnt).db_post_period_start := NULL;
225 p_periods_tab(l_cnt).db_post_period_end := NULL;
226
227
228 -- Bug 7671493 (12.1.1) raised due to previous fix 7380862
229 -- Before 7380862, the following condition used to cover the fourth
230 -- condition also, and the fourth condition never worked.
231 -- Post 7380862, the case of period start date coinciding with the timecard start
232 -- date and period end date greater than the timecard end date condition would
233 -- never be checked -- this is the case of employees terminated mid period.
234 -- Changed '>' to '>='
235
236 ELSIF ( l_period_start_date >= p_timecard_period_start AND
237 TRUNC(l_period_end_date) > p_timecard_period_end )
238 THEN
239
240 if g_debug then
241 hr_utility.set_location('Processing '||l_proc, 50);
242 end if;
243
244 -- period includes TC and stradles TC end
245
246 p_periods_tab(l_cnt).period_start := l_period_start_date;
247 p_periods_tab(l_cnt).period_end :=
248 TO_DATE(TO_CHAR(p_timecard_period_end, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
249
250 p_periods_tab(l_cnt).db_pre_period_start := NULL;
251 p_periods_tab(l_cnt).db_pre_period_end := NULL;
252
253 p_periods_tab(l_cnt).db_post_period_start := TRUNC(p_timecard_period_end) + 1;
254 p_periods_tab(l_cnt).db_post_period_end := l_period_end_date;
255
256
257 ELSIF ( l_period_start_date < p_timecard_period_start AND
258 TRUNC(l_period_end_date) > p_timecard_period_end )
259 THEN
260
261 if g_debug then
262 hr_utility.set_location('Processing '||l_proc, 60);
263 end if;
264
265 -- period completely stradles TCO
266
267 p_periods_tab(l_cnt).period_start := p_timecard_period_start;
268 p_periods_tab(l_cnt).period_end :=
269 TO_DATE(TO_CHAR(p_timecard_period_end, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
270
271 p_periods_tab(l_cnt).db_pre_period_start := l_period_start_date;
272 p_periods_tab(l_cnt).db_pre_period_end :=
273 TO_DATE(TO_CHAR((p_timecard_period_start-1), 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
274
275 p_periods_tab(l_cnt).db_post_period_start := TRUNC(p_timecard_period_end) + 1;
276 p_periods_tab(l_cnt).db_post_period_end := l_period_end_date;
277
278 END IF;
279
280 if g_debug then
281 hr_utility.trace('');
282 hr_utility.trace(' ********** Periods ************** ');
283 hr_utility.trace(' Actual TC period start is :'||TO_CHAR(p_timecard_period_start, 'DD-MON-YY HH24:MI:SS'));
284 hr_utility.trace(' Actual TC period end is :'||TO_CHAR(p_timecard_period_end, 'DD-MON-YY HH24:MI:SS'));
285 hr_utility.trace(' TC period start is :'||TO_CHAR(p_periods_tab(l_cnt).period_start, 'DD-MON-YY HH24:MI:SS'));
286 hr_utility.trace(' TC period end is :'||TO_CHAR(p_periods_tab(l_cnt).period_end, 'DD-MON-YY HH24:MI:SS'));
287 hr_utility.trace(' pre TC period start is :'||TO_CHAR(p_periods_tab(l_cnt).db_pre_period_start, 'DD-MON-YY HH24:MI:SS'));
288 hr_utility.trace(' pre TC period end is :'||TO_CHAR(p_periods_tab(l_cnt).db_pre_period_end, 'DD-MON-YY HH24:MI:SS'));
289 hr_utility.trace(' post TC period start is:'||TO_CHAR(p_periods_tab(l_cnt).db_post_period_start, 'DD-MON-YY HH24:MI:SS'));
290 hr_utility.trace(' post TC period end is :'||TO_CHAR(p_periods_tab(l_cnt).db_post_period_end, 'DD-MON-YY HH24:MI:SS'));
291 end if;
292
293 l_period_start_date := l_period_start_date + p_duration_in_days;
294 l_period_end_date := l_period_end_date + p_duration_in_days;
295
296 if g_debug then
297 hr_utility.trace('');
298 hr_utility.trace('start is '||to_char(l_period_start_date, 'dd-mon-yyyy hh24:mi:ss'));
299 hr_utility.trace('end is '||to_char(l_period_end_date, 'dd-mon-yyyy hh24:mi:ss'));
300 hr_utility.trace('');
301 end if;
302
303 l_cnt := l_cnt + 1;
304
305 END LOOP;
306
307 if g_debug then
308 hr_utility.set_location('Leaving '||l_proc, 70);
309 end if;
310
311 END calc_timecard_periods;
312
313 PROCEDURE calc_reference_periods (
314 p_timecard_period_start DATE
315 , p_timecard_period_end DATE
316 , p_ref_period_start DATE
317 , p_ref_period_end DATE
318 , p_period_start_date DATE
319 , p_period_end_date DATE
320 , p_duration_in_days NUMBER
321 , p_periods_tab IN OUT NOCOPY t_period ) IS
322
323 l_proc VARCHAR2(72);
324
325 l_cnt BINARY_INTEGER := 1;
326
327 l_period_start_date DATE;
328 l_period_end_date DATE;
329
330 l_ref_period_start DATE;
331 l_ref_period_end DATE;
332
333 BEGIN
334
335 g_debug := hr_utility.debug_enabled;
336
337 if g_debug then
338 l_proc := g_package||'calc_reference_periods';
339 hr_utility.set_location('Entering '||l_proc, 10);
340 end if;
341
342 l_period_start_date := p_period_start_date;
343 l_period_end_date := p_period_end_date;
344
345 l_ref_period_start := p_ref_period_start;
346 l_ref_period_end := p_ref_period_end;
347
348 WHILE ( l_period_start_date <= p_timecard_period_end )
349 LOOP
350
351 if g_debug then
352 hr_utility.set_location('Processing '||l_proc, 20);
353 end if;
354
355 IF ( l_period_start_date >= p_timecard_period_start AND
356 TRUNC(l_period_end_date) <= p_timecard_period_end )
357 THEN
358
359 if g_debug then
360 hr_utility.set_location('Entering '||l_proc, 30);
361 end if;
362
363 -- timecard period completely within TCO
364
365 -- set reference period
366 -- note reference period dates are always less than period start
367
368 IF ( l_ref_period_end > p_timecard_period_start AND
369 l_ref_period_start >= p_timecard_period_start )
370 THEN
371
372 if g_debug then
373 hr_utility.set_location('Entering '||l_proc, 40);
374 end if;
375
376 -- ref period completely enclosed in TCO
377
378 p_periods_tab(l_cnt).period_start := l_ref_period_start;
379
380 p_periods_tab(l_cnt).db_ref_period_start := NULL;
381 p_periods_tab(l_cnt).db_ref_period_end := NULL;
382
383 ELSIF ( TRUNC(l_ref_period_end) >= p_timecard_period_start AND
384 l_ref_period_start < p_timecard_period_start )
385 THEN
386
387 if g_debug then
388 hr_utility.set_location('Entering '||l_proc, 50);
389 end if;
390
391 -- ref period stradles start of TCO or end falls exactly on TC start
392
393 p_periods_tab(l_cnt).period_start := p_timecard_period_start;
394
395 p_periods_tab(l_cnt).db_ref_period_start := l_ref_period_start;
396 p_periods_tab(l_cnt).db_ref_period_end :=
397 TO_DATE(TO_CHAR((p_timecard_period_start-1), 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
398
399 ELSE
400
401 if g_debug then
402 hr_utility.set_location('Entering '||l_proc, 70);
403 end if;
404
405 p_periods_tab(l_cnt).db_ref_period_start := l_ref_period_start;
406 p_periods_tab(l_cnt).db_ref_period_end :=
407 TO_DATE(TO_CHAR((p_timecard_period_start-1), 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
408
409 END IF;
410
411
412 ELSIF ( l_period_start_date < p_timecard_period_start AND
413 TRUNC(l_period_end_date) <= p_timecard_period_end )
414 THEN
415
416 if g_debug then
417 hr_utility.set_location('Entering '||l_proc, 80);
418 end if;
419
420 -- period includes TC and stradles TCO start
421
422 p_periods_tab(l_cnt).db_ref_period_start := l_ref_period_start;
423 p_periods_tab(l_cnt).db_ref_period_end :=
424 TO_DATE(TO_CHAR(l_ref_period_end, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
425
426 ELSIF ( l_period_start_date <= p_timecard_period_end AND
427 TRUNC(l_period_end_date) > p_timecard_period_end )
428 THEN
429
430 if g_debug then
431 hr_utility.set_location('Entering '||l_proc, 90);
432 end if;
433
434 -- period includes TC and stradles TC end
435
436 -- set reference period
437
438 IF ( l_ref_period_end > p_timecard_period_start AND
439 l_ref_period_start >= p_timecard_period_start )
440 THEN
441
442 if g_debug then
443 hr_utility.set_location('Entering '||l_proc, 100);
444 end if;
445
446 -- reference period completely enclosed in TCO
447
448 p_periods_tab(l_cnt).period_start := l_ref_period_start;
449
450 p_periods_tab(l_cnt).db_ref_period_start := NULL;
451 p_periods_tab(l_cnt).db_ref_period_end := NULL;
452
453 ELSIF ( TRUNC(l_ref_period_end) >= p_timecard_period_start AND
454 l_ref_period_start < p_timecard_period_start )
455 THEN
456
457 if g_debug then
458 hr_utility.set_location('Entering '||l_proc, 110);
459 end if;
460
461 -- ref period stradles start of TCO or end falls exactly on start of TCO
462
463 p_periods_tab(l_cnt).period_start := p_timecard_period_start;
464
465 p_periods_tab(l_cnt).db_ref_period_start := l_ref_period_start;
466 p_periods_tab(l_cnt).db_ref_period_end :=
467 TO_DATE(TO_CHAR((p_timecard_period_start-1), 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
468
469 ELSE
470
471 if g_debug then
472 hr_utility.set_location('Entering '||l_proc, 130);
473 end if;
474
475 p_periods_tab(l_cnt).db_ref_period_start := l_ref_period_start;
476 p_periods_tab(l_cnt).db_ref_period_end :=
477 TO_DATE(TO_CHAR((p_timecard_period_start-1), 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
478
479 END IF;
480
481 ELSIF ( l_period_start_date < p_timecard_period_start AND
482 l_period_end_date > p_timecard_period_end )
483 THEN
484
485 if g_debug then
486 hr_utility.set_location('Entering '||l_proc, 140);
487 end if;
488
489 -- period completely stradles TCO
490 -- reference period outside of TCO
491
492 p_periods_tab(l_cnt).db_ref_period_start := l_ref_period_start;
493 p_periods_tab(l_cnt).db_ref_period_end :=
494 TO_DATE(TO_CHAR(l_ref_period_end, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
495
496 END IF;
497
498 if g_debug then
499 hr_utility.trace('');
500 hr_utility.trace(' ********** Periods ************** ');
501 hr_utility.trace(' Actual TC period start is :'||TO_CHAR(p_timecard_period_start, 'DD-MON-YY HH24:MI:SS'));
502 hr_utility.trace(' Actual TC period end is :'||TO_CHAR(p_timecard_period_end, 'DD-MON-YY HH24:MI:SS'));
503 hr_utility.trace(' TC period start is :'||TO_CHAR(p_periods_tab(l_cnt).period_start, 'DD-MON-YY HH24:MI:SS'));
504 hr_utility.trace(' TC period end is :'||TO_CHAR(p_periods_tab(l_cnt).period_end, 'DD-MON-YY HH24:MI:SS'));
505 hr_utility.trace(' pre TC period start is :'||TO_CHAR(p_periods_tab(l_cnt).db_pre_period_start, 'DD-MON-YY HH24:MI:SS'));
506 hr_utility.trace(' pre TC period end is :'||TO_CHAR(p_periods_tab(l_cnt).db_pre_period_end, 'DD-MON-YY HH24:MI:SS'));
507 hr_utility.trace(' post TC period start is:'||TO_CHAR(p_periods_tab(l_cnt).db_post_period_start, 'DD-MON-YY HH24:MI:SS'));
508 hr_utility.trace(' post TC period end is :'||TO_CHAR(p_periods_tab(l_cnt).db_post_period_end, 'DD-MON-YY HH24:MI:SS'));
509 hr_utility.trace(' ref period start is :'||TO_CHAR(p_periods_tab(l_cnt).db_ref_period_start, 'DD-MON-YY HH24:MI:SS'));
510 hr_utility.trace(' ref period end is :'||TO_CHAR(p_periods_tab(l_cnt).db_ref_period_end, 'DD-MON-YY HH24:MI:SS'));
511 end if;
512
513 l_period_start_date := l_period_start_date + p_duration_in_days;
514 l_period_end_date := l_period_end_date + p_duration_in_days;
515
516 l_ref_period_start := l_ref_period_start + p_duration_in_days;
517 l_ref_period_end := l_ref_period_end + p_duration_in_days;
518
519 l_cnt := l_cnt + 1;
520
521 END LOOP;
522
523 if g_debug then
524 hr_utility.set_location('Leaving '||l_proc, 160);
525 end if;
526
527 END calc_reference_periods;
528
529
530 -- public function (Overloaded)
531 -- calc_timecard_hrs
532 --
533 -- description
534 -- calculates timecard hrs by traversing the self service time
535 -- deposit building block and attribute PL/SQL tables for a
536 -- specified date range.
537 -- Uses dynamic SQL to determine if the hours fall into the
538 -- time category specified by passing the name, id or setting
539 -- a global variable for the time_category_id
540
541 FUNCTION calc_timecard_hrs (
542 p_hrs_period_start DATE
543 , p_hrs_period_end DATE
544 , p_tco_bb hxc_self_service_time_deposit.timecard_info
545 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info )
546 RETURN NUMBER IS
547
548 l_hours NUMBER := 0;
549 l_time_category_id hxc_time_categories.time_category_id%TYPE;
550
551 BEGIN
552
553 l_hours := calc_timecard_hrs (
554 p_hrs_period_start => p_hrs_period_start
555 , p_hrs_period_end => p_hrs_period_end
556 , p_tco_bb => p_tco_bb
557 , p_tco_att => p_tco_att
558 , p_time_category_id => hxc_time_category_utils_pkg.g_time_category_id );
559
560 RETURN l_hours;
561
562 END calc_timecard_hrs;
563
564
565 -- public function
566 -- calc_timecard_hrs (Overloaded)
567 --
568 -- description
569 -- calculates timecard hrs by traversing the self service time
570 -- deposit building block and attribute PL/SQL tables for a
571 -- specified date range.
572 -- Uses dynamic SQL to determine if the hours fall into the
573 -- time category specified by passing the name, id or setting
574 -- a global variable for the time_category_id
575
576 FUNCTION calc_timecard_hrs (
577 p_hrs_period_start DATE
578 , p_hrs_period_end DATE
579 , p_tco_bb hxc_self_service_time_deposit.timecard_info
580 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
581 , p_time_category_name VARCHAR2 )
582 RETURN NUMBER IS
583
584 l_hours NUMBER := 0;
585 l_time_category_id hxc_time_categories.time_category_id%TYPE;
586
587 BEGIN
588
589 l_time_category_id := hxc_time_category_utils_pkg.get_time_category_id ( p_time_category_name => p_time_category_name );
590
591 l_hours := calc_timecard_hrs (
592 p_hrs_period_start => p_hrs_period_start
593 , p_hrs_period_end => p_hrs_period_end
594 , p_tco_bb => p_tco_bb
595 , p_tco_att => p_tco_att
596 , p_time_category_id => l_time_category_id );
597
598 RETURN l_hours;
599
600 END calc_timecard_hrs;
601
602
603 -- public function
604 -- calc_timecard_hrs (Overloaded)
605 --
606 -- description
607 -- New time category phase II function
608
609 FUNCTION calc_timecard_hrs (
610 p_hrs_period_start DATE
611 , p_hrs_period_end DATE
612 , p_tco_bb HXC_BLOCK_TABLE_TYPE
613 , p_tco_att HXC_ATTRIBUTE_TABLE_TYPE
614 , p_time_category_id NUMBER )
615 RETURN NUMBER IS
616
617 l_proc VARCHAR2(72);
618
619 l_timecard_hrs NUMBER;
620
621 BEGIN -- calc_timecard_hrs
622
623 g_debug := hr_utility.debug_enabled;
624
625 if g_debug then
626 l_proc := g_package||'calc_timecard_hrs';
627 hr_utility.trace('p hrs period start is :'||to_char(p_hrs_period_start, 'DD-MON-YYYY HH24:MI:SS'));
628 hr_utility.trace('p hrs period end is :'||to_char(p_hrs_period_end, 'DD-MON-YYYY HH24:MI:SS'));
629 end if;
630
631 IF ( p_time_category_id IS NOT NULL )
632 THEN
633
634 if g_debug then
635 hr_utility.set_location('Processing '||l_proc, 15);
636 end if;
637
638 hxc_time_category_utils_pkg.initialise_time_category (
639 p_time_category_id => p_time_category_id
640 , p_tco_att => p_tco_att );
641
642 hxc_time_category_utils_pkg.sum_tc_bb_ok_hrs (
643 p_tc_bb_ok_string => hxc_time_category_utils_pkg.g_tc_bb_ok_string
644 , p_hrs => l_timecard_hrs
645 , p_period_start => p_hrs_period_start
646 , p_period_end => p_hrs_period_end );
647
648 ELSE
649
650 -- no time category set this sum all hours on the timecard regardless
651
652 hxc_time_category_utils_pkg.sum_tc_bb_ok_hrs (
653 p_tc_bb_ok_string => NULL
654 , p_hrs => l_timecard_hrs
655 , p_period_start => p_hrs_period_start
656 , p_period_end => p_hrs_period_end );
657
658 END IF;
659
660 if g_debug then
661 hr_utility.trace('');
662 hr_utility.trace(' TC hours are '||TO_CHAR(l_timecard_hrs));
663 hr_utility.trace('');
664 end if;
665
666 RETURN l_timecard_hrs;
667
668 exception when others then
669
670 if g_debug then
671 hr_utility.trace('In exception ....');
672 hr_utility.trace(SUBSTR(SQLERRM,1 ,80));
673 hr_utility.trace(SUBSTR(SQLERRM,81, 160));
674 end if;
675
676 raise;
677
678 END calc_timecard_hrs;
679
680
681
682 -- public function
683 -- calc_timecard_hrs (Overloaded)
684 --
685 -- description
686 -- calculates timecard hrs by traversing the self service time
687 -- deposit building block and attribute PL/SQL tables for a
688 -- specified date range.
689 -- Uses dynamic SQL to determine if the hours fall into the
690 -- time category specified by passing the name, id or setting
691 -- a global variable for the time_category_id
692
693 -- THIS FUNCTION IS FOR BACKWARD COMPATIBILTY ONLY WITH TIME
694 -- CATEGORIES PHASE II
695
696 FUNCTION calc_timecard_hrs (
697 p_hrs_period_start DATE
698 , p_hrs_period_end DATE
699 , p_tco_bb hxc_self_service_time_deposit.timecard_info
700 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
701 , p_time_category_id NUMBER )
702 RETURN NUMBER IS
703
704 l_proc VARCHAR2(72);
705
706 -- for backward compatibility
707
708 l_tco_bb_dummy HXC_BLOCK_TABLE_TYPE;
709 l_tco_att_dummy HXC_ATTRIBUTE_TABLE_TYPE;
710
711 l_hrs NUMBER;
712
713 BEGIN -- calc_timecard_hrs
714
715 g_debug := hr_utility.debug_enabled;
716
717 l_hrs := calc_timecard_hrs (
718 p_hrs_period_start => p_hrs_period_start
719 , p_hrs_period_end => p_hrs_period_end
720 , p_tco_bb => l_tco_bb_dummy
721 , p_tco_att => l_tco_att_dummy
722 , p_time_category_id => p_time_category_id );
723
724 if g_debug then
725 l_proc := g_package||'calc_timecard_hrs';
726 hr_utility.trace('');
727 hr_utility.trace(' TC hours are '||TO_CHAR(l_hrs));
728 hr_utility.trace('');
729 end if;
730
731 RETURN l_hrs;
732
733 exception when others then
734
735 if g_debug then
736 hr_utility.trace(SUBSTR(SQLERRM,1 ,80));
737 hr_utility.trace(SUBSTR(SQLERRM,81, 160));
738 end if;
739
740 raise;
741
742 END calc_timecard_hrs;
743
744
745
746
747 -- public procedure
748 -- add_error_to_table
749 --
750 -- description
751 -- adds error to the TCO message stack
752
753 PROCEDURE add_error_to_table (
754 p_message_table in out nocopy HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE
755 , p_message_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
756 , p_message_token in VARCHAR2
757 , p_message_level in VARCHAR2
758 , p_message_field in VARCHAR2
759 , p_application_short_name IN VARCHAR2 default 'HXC'
760 , p_timecard_bb_id in NUMBER
761 , p_time_attribute_id in NUMBER
762 , p_timecard_bb_ovn in NUMBER default null
763 , p_time_attribute_ovn in NUMBER default null
764 , p_message_extent in VARCHAR2 default null) is --Bug#2873563
765
766 l_last_index BINARY_INTEGER;
767
768 l_proc VARCHAR2(72);
769
770 l_tbb_ovn number(9);
771
772 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
773 l_message_token varchar2(4000);
774
775 BEGIN
776 g_debug := hr_utility.debug_enabled;
777
778 if g_debug then
779 l_proc := g_package||'add_error_to_table';
780 hr_utility.set_location('Entering '||l_proc, 10);
781 end if;
782
783 l_tbb_ovn := p_timecard_bb_ovn+1;
784
785 l_last_index := NVL(p_message_table.last,0);
786
787 if g_debug then
788 hr_utility.trace('index is '||to_char(l_last_index));
789 end if;
790
791 l_message_token := SUBSTR(p_message_token,1,4000);
792
793 IF ( p_message_name = 'EXCEPTION' )
794 THEN
795
796 hr_message.provide_error;
797 l_message_name := hr_message.last_message_name;
798
799 IF ( l_message_name IS NULL )
800 THEN
801 -- Bug 3036930
802 l_message_name := 'HXC_HXT_DEP_VAL_ORAERR';
803 l_message_token := substr('ERROR&' || SQLERRM,1,4000);
804 END IF;
805
806 ELSE
807
808 l_message_name := p_message_name;
809
810 END IF;
811
812 p_message_table(l_last_index+1).message_name := l_message_name;
813 p_message_table(l_last_index+1).message_level := p_message_level;
814 p_message_table(l_last_index+1).message_field := p_message_field;
815 p_message_table(l_last_index+1).message_tokens:= l_message_token;
816 p_message_table(l_last_index+1).application_short_name := p_application_short_name;
817 p_message_table(l_last_index+1).time_building_block_id := p_timecard_bb_id;
818 p_message_table(l_last_index+1).time_building_block_ovn := l_tbb_ovn;
819 p_message_table(l_last_index+1).time_attribute_id := p_time_attribute_id;
820 p_message_table(l_last_index+1).time_attribute_ovn := p_time_attribute_ovn;
821 p_message_table(l_last_index+1).message_extent := p_message_extent; --Bug#2873563
822
823 if g_debug then
824 hr_utility.set_location('Leaving '||l_proc, 20);
825 end if;
826
827 END add_error_to_table;
828
829
830 -- public procedure
831 -- execute_time_entry_rules
832 --
833 -- description
834 -- executes a given time entry rule called from the self service time
835 -- deposit API based on resources preference time entry rule group
836
837 PROCEDURE execute_time_entry_rules (
838 p_operation VARCHAR2
839 , p_time_building_blocks hxc_self_service_time_deposit.timecard_info
840 , p_time_attributes hxc_self_service_time_deposit.building_block_attribute_info
841 , p_messages IN OUT nocopy hxc_self_service_time_deposit.message_table
842 , p_resubmit VARCHAR2
843 , p_blocks hxc_block_table_type
844 , p_attributes hxc_attribute_table_type ) IS
845
846 l_proc VARCHAR2(72);
847
848 l_submission_date DATE;
849
850 l_timecard_info_rec r_timecard_info;
851 l_terg_id hxc_pref_hierarchies.attribute1%TYPE;
852 l_rules_evl hxc_pref_hierarchies.attribute1%TYPE;
853
854 l_alter_session VARCHAR2(1000);
855 l_run_id number;
856
857 l_pref_table hxc_preference_evaluation.t_pref_table;
858
859 p_master_pref_table hxc_preference_evaluation.t_pref_table;
860
861 cursor gaz_time_sql is
862 select time_category_id
863 from hxc_time_Categories
864 where time_category_name = 'GARRYS TIME SQL TEST';
865
866 CURSOR csr_get_first_asg_date ( p_resource_id NUMBER
867 , p_tc_start DATE
868 , p_tc_end DATE ) IS
869 SELECT MAX( asg.effective_start_date)
870 , asg.assignment_id
871 FROM per_assignments_f asg
872 WHERE asg.person_id = p_resource_id
873 AND asg.primary_flag = 'Y'
874 AND asg.assignment_type in ('E','C')
875 AND asg.effective_start_date <= TRUNC(p_tc_end)
876 AND asg.effective_end_date >= TRUNC(p_tc_start)
877 GROUP BY asg.assignment_id;
878
879
880 PROCEDURE set_global_asg_info ( p_resource_id NUMBER
881 , p_start_date DATE
882 , p_end_date DATE ) IS
883
884 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
885
886 BEGIN
887
888 OPEN csr_get_first_asg_date ( p_resource_id
889 , p_start_date
890 , p_end_date );
891
892 FETCH csr_get_first_asg_date INTO l_submission_date, l_assignment_id;
893
894 CLOSE csr_get_first_asg_date;
895
896 l_submission_date := GREATEST( l_submission_date, p_start_date );
897
898 hxc_time_entry_rules_utils_pkg.g_assignment_info(p_resource_id).assignment_id
899 := l_assignment_id;
900 hxc_time_entry_rules_utils_pkg.g_assignment_info(p_resource_id).submission_date
901 := l_submission_date;
902 hxc_time_entry_rules_utils_pkg.g_assignment_info(p_resource_id).start_date
903 := p_start_date;
904 hxc_time_entry_rules_utils_pkg.g_assignment_info(p_resource_id).end_date
905 := p_end_date;
906
907 END set_global_asg_info;
908
909
910
911 -- private procedure
912 -- execute_formula
913 --
914 -- description
915 -- executes and evaluates each WTD rule
916
917 PROCEDURE execute_formula ( p_formula_name varchar2
918 , p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table
919 , p_message_level varchar2
920 , p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
921 , p_tco_bb hxc_self_service_time_deposit.timecard_info
922 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
923 , p_timecard_info r_timecard_info ) IS
924
925 l_proc VARCHAR2(72);
926
927 l_param_rec hxc_ff_dict.r_param;
928
929 l_period_type hxc_recurring_periods.period_type%TYPE;
930 l_period_id hxc_recurring_periods.recurring_period_id%TYPE;
931 l_reference_period NUMBER(10);
932 l_consider_zero_hours VARCHAR2(10) := 'Y'; -- fault tolerant thus larger than 1 char
933
934 l_period_tab t_period;
935
936 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
937 l_period_start DATE;
938 l_period_start_date DATE;
939 l_period_end_date DATE;
940 l_ref_period_start DATE;
941 l_ref_period_end DATE;
942
943 l_timecard_hrs NUMBER := 0;
944
945 l_outputs ff_exec.outputs_t;
946 l_result VARCHAR2(1);
947
948 l_message_table t_message_table;
949 l_message_count PLS_INTEGER;
950
951 l_token_string VARCHAR2(4000) := NULL;
952
953 l_cnt BINARY_INTEGER;
954
955 l_new_index BINARY_INTEGER;
956
957 PROCEDURE process_message (
958 p_output_name IN VARCHAR2
959 , p_output_value IN VARCHAR2
960 , p_output_number IN NUMBER
961 , p_message_table IN OUT NOCOPY t_message_table
962 , p_rule_record IN hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype ) IS
963
964 l_index BINARY_INTEGER;
965
966 BEGIN
967
968 l_index := p_output_number;
969
970 IF ( p_output_name = 'MESSAGE' AND
971 ( p_output_value = 'HXC_WTD_PERIOD_MAXIMUM' OR p_output_value = 'HXC_TER_VIOLATION' ) )
972 THEN
973
974 p_message_table(l_index).name := p_output_value;
975 p_message_table(l_index).token_name := 'TER';
976 p_message_table(l_index).token_value := p_rule_record.ter_message_name;
977
978 IF ( p_message_table(l_index).extent IS NULL )
979 THEN
980 p_message_table(l_index).extent := hxc_timecard.c_blk_children_extent;
981
982 END IF;
983
984 ELSIF ( p_output_name = 'MESSAGE' )
985 THEN
986
987 p_message_table(l_index).name := p_output_value;
988
989 IF ( p_message_table(l_index).extent IS NULL )
990 THEN
991 p_message_table(l_index).extent := hxc_timecard.c_blk_children_extent;
992
993 END IF;
994
995 ELSIF ( p_output_name = 'TOKEN_VALUE' )
996 THEN
997
998 p_message_table(l_index).token_value := p_output_value;
999
1000 ELSIF ( p_output_name = 'TOKEN_NAME' )
1001 THEN
1002
1003 p_message_table(l_index).token_name := UPPER(p_output_Value);
1004
1005 END IF;
1006
1007 END process_message;
1008
1009 FUNCTION check_commit ( p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table
1010 ,p_timecard_info r_timecard_info ) RETURN BOOLEAN IS
1011
1012 CURSOR chk_global_table IS
1013 select 1
1014 from hxc_tmp_blks;
1015
1016
1017 l_dummy number;
1018
1019 BEGIN
1020
1021
1022
1023 IF ( p_timecard_info.deleted = 'N' )
1024 THEN
1025
1026 if g_debug then
1027 hr_utility.trace('Entering check_commit');
1028 end if;
1029
1030 OPEN chk_global_table;
1031 FETCH chk_global_table INTO l_dummy;
1032
1033 IF ( chk_global_table%NOTFOUND )
1034 THEN
1035
1036 CLOSE chk_global_table;
1037
1038 if g_debug then
1039 hr_utility.trace('hxc tmp bld blks empty');
1040 end if;
1041
1042 add_error_to_table (
1043 p_message_table => p_message_table
1044 , p_message_name => 'HXC_TER_NO_COMMIT'
1045 , p_message_token => NULL
1046 , p_message_level => 'ERROR'
1047 , p_message_field => NULL
1048 , p_timecard_bb_id => NULL
1049 , p_time_attribute_id => NULL
1050 , p_timecard_bb_ovn => NULL
1051 , p_time_attribute_ovn => NULL
1052 , p_message_extent => hxc_timecard.c_blk_children_extent );
1053
1054
1055 RETURN FALSE;
1056
1057 ELSE
1058
1059 CLOSE chk_global_table;
1060
1061 if g_debug then
1062 hr_utility.trace('hxc tmp bld blks populated');
1063 end if;
1064
1065 RETURN TRUE;
1066
1067 END IF;
1068
1069 ELSE
1070
1071 RETURN TRUE;
1072
1073 END IF; -- p_timecard_info.delete
1074
1075 END check_commit;
1076
1077
1078
1079
1080 BEGIN -- execute_formula
1081
1082
1083
1084 /**********************************************
1085 * Execute Formula *
1086 **********************************************/
1087
1088 if g_debug then
1089 l_proc := g_package||'execute_formula';
1090 hr_utility.set_location('Processing '||l_proc, 10);
1091 end if;
1092
1093 hxc_ff_dict.decode_formula_segments (
1094 p_formula_name => p_formula_name
1095 , p_rule_rec => p_rule_record
1096 , p_param_rec => l_param_rec
1097 , p_period_value => l_period_id
1098 , p_reference_value => l_reference_period
1099 , p_consider_zero_hours => l_consider_zero_hours );
1100
1101
1102 if g_debug then
1103 hr_utility.trace(' ************* Param values are.... ************ ');
1104 hr_utility.trace('');
1105 hr_utility.trace(' Rule name is '||p_rule_record.name);
1106 hr_utility.trace('');
1107 hr_utility.trace(' Period Id is '||to_char(l_period_id));
1108 hr_utility.trace(' Reference Period is '||to_char(l_reference_period));
1109 hr_utility.trace(' Period Max is '||l_param_rec.param2_value);
1110
1111
1112 hr_utility.set_location('Processing '||l_proc, 20);
1113 end if;
1114
1115 -- if either PERIOD or REFERENCE_PERIOD specified.
1116
1117 IF ( l_period_id IS NOT NULL OR l_reference_period IS NOT NULL )
1118 THEN
1119
1120 -- we are looking for either of the inputs availabe to the seeded formulae
1121
1122 IF ( l_period_id IS NOT NULL )
1123 THEN
1124
1125 OPEN csr_get_period_info ( p_recurring_period_id => l_period_id );
1126 FETCH csr_get_period_info INTO l_period_type, l_duration_in_days, l_period_start;
1127 CLOSE csr_get_period_info;
1128
1129 if g_debug then
1130 hr_utility.trace('');
1131 hr_utility.trace('*********** Period Info ************');
1132 hr_utility.trace('period type is '||l_period_type);
1133 hr_utility.trace('duration in days is '||TO_CHAR(l_duration_in_days));
1134 hr_utility.trace('period start date is '||TO_CHAR(l_period_start,'DD-MON-YY HH24:MI:SS'));
1135 end if;
1136
1137
1138 IF ( l_duration_in_days IS NOT NULL )
1139 THEN
1140
1141 l_period_start_date := l_period_start +
1142 (l_duration_in_days *
1143 FLOOR(((p_timecard_info.start_date - l_period_start)/l_duration_in_days)));
1144
1145 l_period_end_date := l_period_start_date + l_duration_in_days - 1;
1146
1147 ELSE
1148
1149 -- Call application specific function to generate the period
1150 -- start and end dates from the period type.
1151
1152 hr_generic_util.get_period_dates
1153 (p_rec_period_start_date => l_period_start
1154 ,p_period_type => l_period_type
1155 ,p_current_date => p_timecard_info.start_date
1156 ,p_period_start_date => l_period_start_date
1157 ,p_period_end_date => l_period_end_date);
1158
1159 l_duration_in_days := ( l_period_end_date - l_period_start_date ) + 1;
1160
1161 END IF;
1162
1163 -- now add time component to l_period_end
1164
1165 l_period_end_date := TO_DATE(TO_CHAR(l_period_end_date, 'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1166
1167 if g_debug then
1168 Hr_utility.trace('');
1169 hr_utility.trace('*********** Period Start and End ************');
1170 hr_utility.trace('period start date is '||TO_CHAR(l_period_start_date,'DD-MON-YY HH24:MI:SS'));
1171 hr_utility.trace('period end date is '||TO_CHAR(l_period_end_date,'DD-MON-YY HH24:MI:SS'));
1172 hr_utility.trace('duration in days is '||TO_CHAR(l_duration_in_days));
1173 end if;
1174
1175 -- now build up table of time entry rule periods that the timecard
1176 -- may span
1177
1178 calc_timecard_periods (
1179 p_timecard_period_start => p_timecard_info.start_date
1180 , p_timecard_period_end => p_timecard_info.end_date
1181 , p_period_start_date => l_period_start_date
1182 , p_period_end_date => l_period_end_date
1183 , p_duration_in_days => l_duration_in_days
1184 , p_periods_tab => l_period_tab );
1185
1186 END IF; -- ( l_period_id IS NOT NULL )
1187
1188
1189 -- now check to see if the formula uses Reference Period
1190
1191 IF ( l_reference_period IS NOT NULL )
1192 THEN
1193
1194 /*******************************
1195 * Reference Period Stuff *
1196 *******************************/
1197
1198 -- now need to work out the reference period start date in case any of those
1199 -- hours are included on the timecard object. If they are then this will affect
1200 -- the l_period_start_date
1201
1202 -- no need to calculate reference period if reference period is less than or equal to
1203 -- the actual period
1204
1205 IF ( l_reference_period > l_duration_in_days )
1206 THEN
1207
1208 l_ref_period_start := ( l_period_start_date - ( l_reference_period - l_duration_in_days ) );
1209 l_ref_period_end := l_period_start_date - 1;
1210
1211 calc_reference_periods (
1212 p_timecard_period_start => p_timecard_info.start_date
1213 , p_timecard_period_end => p_timecard_info.end_date
1214 , p_ref_period_start => l_ref_period_start
1215 , p_ref_period_end => l_ref_period_end
1216 , p_period_start_date => l_period_start_date
1217 , p_period_end_date => l_period_end_date
1218 , p_duration_in_days => l_duration_in_days
1219 , p_periods_tab => l_period_tab );
1220
1221 END IF;
1222
1223 if g_debug then
1224 hr_utility.trace('');
1225 hr_utility.trace('********** Original reference period ************');
1226 hr_utility.trace('ref period start is '||TO_CHAR(l_ref_period_start,'DD-MON-YY HH24:MI:SS'));
1227 hr_utility.trace('ref period end is '||TO_CHAR(l_ref_period_end,'DD-MON-YY HH24:MI:SS'));
1228 end if;
1229
1230 END IF; -- ( l_reference_period IS NOT NULL )
1231
1232 if g_debug then
1233 hr_utility.set_location('Processing '||l_proc, 30);
1234 end if;
1235
1236 ELSE -- ( l_period and l_ref_period are NULL )
1237
1238 -- set the start date and end date equal to the TC start and end date
1239
1240 l_period_tab(1).period_start := p_timecard_info.start_date;
1241 l_period_tab(1).period_end :=
1242 TO_DATE(TO_CHAR(p_timecard_info.end_date,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1243
1244 l_period_tab(1).db_pre_period_start := NULL;
1245 l_period_tab(1).db_pre_period_end := NULL;
1246 l_period_tab(1).db_post_period_start := NULL;
1247 l_period_tab(1).db_post_period_end := NULL;
1248 l_period_tab(1).db_ref_period_start := NULL;
1249 l_period_tab(1).db_ref_period_end := NULL;
1250
1251 END IF; -- ( l_period_id IS NOT NULL OR l_reference_period_id )
1252
1253 if g_debug then
1254 hr_utility.trace('*********************************************** ******');
1255 hr_utility.trace('****** TIME CARD is ******');
1256 hr_utility.trace('*********************************************** ******');
1257 end if;
1258
1259 l_new_index := p_tco_bb.FIRST;
1260
1261 WHILE ( l_new_index IS NOT NULL )
1262 LOOP
1263
1264 if g_debug then
1265 hr_utility.trace('');
1266 hr_utility.trace('index is '||to_char(l_new_index));
1267 hr_utility.trace('scope is '||p_tco_bb(l_new_index).scope);
1268 hr_utility.trace('bb id '||to_char(p_tco_bb(l_new_index).time_building_block_id));
1269 hr_utility.trace('parent bb id '||to_char(p_tco_bb(l_new_index).parent_building_block_id));
1270 hr_utility.trace('start '||to_char(p_tco_bb(l_new_index).start_time, 'dd-mon-yy'));
1271 hr_utility.trace('measure '||to_char(p_tco_bb(l_new_index).measure));
1272 hr_utility.trace('');
1273 end if;
1274
1275 l_new_index := p_tco_bb.NEXT(l_new_index);
1276
1277 END LOOP;
1278
1279
1280
1281 -- now loop through the table of periods and calc hrs and execute formula
1282
1283 FOR p IN l_period_tab.FIRST .. l_period_tab.LAST
1284 LOOP
1285 if g_debug then
1286 hr_utility.set_location('Processing '||l_proc, 40);
1287 end if;
1288
1289 IF ( hxc_time_entry_rules_utils_pkg.return_archived_status(l_period_tab(p)) = false )
1290 THEN
1291
1292 -- check to see if any time entry rule has issued a commit i.e. the global tmp tables
1293 -- are empty. Stop processing of remaining time entry rules
1294
1295 IF ( NOT check_commit ( p_messages, p_timecard_info ) )
1296 THEN
1297
1298 if g_debug then
1299 hr_utility.trace('Exiting period loop - check_commit');
1300 end if;
1301 EXIT;
1302
1303 END IF;
1304
1305 l_timecard_hrs := 0;
1306
1307 l_timecard_hrs := calc_timecard_hrs (
1308 p_hrs_period_start => l_period_tab(p).period_start
1309 , p_hrs_period_end => l_period_tab(p).period_end
1310 , p_tco_bb => p_tco_bb
1311 , p_tco_att => p_tco_att );
1312
1313 if g_debug then
1314 hr_utility.trace('TER INC PTO plan id is '||hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id);
1315 end if;
1316
1317 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
1318 THEN
1319 -- calc incrementing PTO time category hours
1320
1321 l_timecard_hrs := l_timecard_hrs - calc_timecard_hrs (
1322 p_hrs_period_start => l_period_tab(p).period_start
1323 , p_hrs_period_end => l_period_tab(p).period_end
1324 , p_tco_bb => p_tco_bb
1325 , p_tco_att => p_tco_att
1326 , p_time_category_id => hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id );
1327
1328 END IF;
1329
1330 if g_debug then
1331 hr_utility.set_location('Processing '||l_proc, 50);
1332 end if;
1333
1334 -- now call the formula
1335
1336 if g_debug then
1337 hr_utility.trace('consider zero hours is '||l_consider_zero_hours);
1338 end if;
1339
1340 IF ( l_consider_zero_hours = 'Y' OR ( l_consider_zero_hours = 'N' AND l_timecard_hrs > 0 ) )
1341 THEN
1342
1343 if g_debug then
1344 hr_utility.trace('Calling ff dict . formula');
1345 end if;
1346
1347 l_outputs := hxc_ff_dict.formula (
1348 p_formula_id => p_rule_record.formula_id
1349 , p_resource_id => p_timecard_info.resource_id
1350 , p_submission_date => l_submission_date
1351 , p_ss_timecard_hours => l_timecard_hrs
1352 , p_period_start_date => l_period_tab(p).period_start
1353 , p_period_end_date => l_period_tab(p).period_end
1354 , p_db_pre_period_start => l_period_tab(p).db_pre_period_start
1355 , p_db_pre_period_end => l_period_tab(p).db_pre_period_end
1356 , p_db_post_period_start => l_period_tab(p).db_post_period_start
1357 , p_db_post_period_end => l_period_tab(p).db_post_period_end
1358 , p_db_ref_period_start => l_period_tab(p).db_ref_period_start
1359 , p_db_ref_period_end => l_period_tab(p).db_ref_period_end
1360 , p_duration_in_days => l_duration_in_days
1361 , p_param_rec => l_param_rec );
1362
1363 if g_debug then
1364 hr_utility.set_location('Processing '||l_proc, 60);
1365 end if;
1366
1367 l_message_table.DELETE;
1368
1369 if g_debug then
1370 hr_utility.trace('l_outputs.COUNT = '||l_outputs.COUNT);
1371 end if;
1372
1373 IF (l_outputs.COUNT > 0) THEN -- Bug 8875292
1374 FOR l_count IN l_outputs.FIRST .. l_outputs.LAST
1375 LOOP
1376
1377 IF ( l_outputs(l_count).name = 'RULE_STATUS' )
1378 THEN
1379
1380 l_result := l_outputs(l_count).value;
1381
1382 -- since approval formulas can potentially also be used
1383 -- in time entry rules translate the approval return
1384 -- value to a value the time entry rule code can
1385 -- understand. TO_APPROVE=Y is an exception in the
1386 -- approval world...
1387
1388 -- GPaytonM 115.12
1389
1390 ELSIF ( l_outputs(l_count).name = 'TO_APPROVE' )
1391 THEN
1392
1393 IF ( l_outputs(l_count).value = 'Y' )
1394 THEN
1395 l_result := 'E';
1396 ELSE
1397 l_result := 'S';
1398 END IF;
1399
1400 ELSIF ( l_outputs(l_count).name like 'MESSAGE%' )
1401 THEN
1402
1403 l_cnt := SUBSTR(l_outputs(l_count).name, LENGTH('MESSAGE')+1);
1404
1405 process_message (
1406 p_output_name => 'MESSAGE'
1407 , p_output_value => l_outputs(l_count).value
1408 , p_output_number => l_cnt
1409 , p_message_table => l_message_table
1410 , p_rule_record => p_rule_record );
1411
1412 ELSIF ( l_outputs(l_count).name like 'TOKEN_VALUE%' )
1413 THEN
1414
1415 l_cnt := SUBSTR(l_outputs(l_count).name, LENGTH('TOKEN_VALUE')+1);
1416
1417 process_message (
1418 p_output_name => 'TOKEN_VALUE'
1419 , p_output_value => l_outputs(l_count).value
1420 , p_output_number => l_cnt
1421 , p_message_table => l_message_table
1422 , p_rule_record => p_rule_record );
1423
1424 ELSIF ( l_outputs(l_count).name like 'TOKEN_NAME%' )
1425 THEN
1426
1427 l_cnt := SUBSTR(l_outputs(l_count).name, LENGTH('TOKEN_NAME')+1);
1428
1429 process_message (
1430 p_output_name => 'TOKEN_NAME'
1431 , p_output_value => l_outputs(l_count).value
1432 , p_output_number => l_cnt
1433 , p_message_table => l_message_table
1434 , p_rule_record => p_rule_record );
1435
1436 END IF;
1437
1438 END LOOP; -- formula outputs loop
1439 END IF; -- Bug 8875292
1440
1441 if g_debug then
1442 hr_utility.set_location('Processing '||l_proc, 70);
1443 end if;
1444
1445 -- populate message table
1446
1447 -- GPM v115.4
1448
1449 IF ( ( l_result = 'E' ) AND ( l_message_table.COUNT <> 0 ) )
1450 THEN
1451
1452 if g_debug then
1453 hr_utility.set_location('Processing '||l_proc, 80);
1454 end if;
1455
1456 l_message_count := l_message_table.FIRST;
1457
1458 WHILE l_message_count IS NOT NULL
1459 LOOP
1460
1461 if g_debug then
1462 hr_utility.set_location('Processing '||l_proc, 90);
1463 end if;
1464
1465 IF ( l_message_table(l_message_count).name IS NOT NULL )
1466 THEN
1467
1468
1469 IF ( l_message_table(l_message_count).token_name is not null )
1470 THEN
1471 l_token_string := SUBSTR(UPPER(l_message_table(l_message_count).token_name)
1472 ||'&'|| l_message_table(l_message_count).token_value ,1,4000);
1473 END IF;
1474
1475 add_error_to_table (
1476 p_message_table => p_message_table
1477 , p_message_name => l_message_table(l_message_count).name
1478 , p_message_token => l_token_string
1479 , p_message_level => p_message_level
1480 , p_message_field => NULL
1481 , p_timecard_bb_id => p_timecard_info.timecard_bb_id
1482 , p_time_attribute_id => NULL
1483 , p_timecard_bb_ovn => p_timecard_info.timecard_ovn
1484 , p_time_attribute_ovn => NULL
1485 , p_message_extent =>l_message_table(l_message_count).extent); --Bug#2873563
1486 END IF;
1487
1488 l_message_count := l_message_table.NEXT(l_message_count);
1489
1490 END LOOP;
1491
1492 l_message_table.DELETE; ----Bug#3090409
1493 END IF;
1494
1495 END IF; -- l_consider_zero_hours check
1496
1497 else
1498
1499 add_error_to_table (
1500 p_message_table => p_message_table
1501 ,p_message_name => 'HXC_ARCHIVE_TER_ERROR'
1502 ,p_message_token => 'TER_NAME&'||hxc_time_entry_rules_utils_pkg.g_ter_record.ter_message_name
1503 ,p_message_level => 'ERROR'
1504 ,p_message_field => NULL
1505 ,p_timecard_bb_id=> NULL
1506 ,p_time_attribute_id=> NULL
1507 ,p_timecard_bb_ovn => NULL
1508 ,p_time_attribute_ovn=> NULL
1509 ,p_message_extent =>hxc_timecard.c_blk_children_extent);
1510
1511 end if; -- hxc_time_entry_rules_utils_pkg.return_archived_status(l_period_tab(p)) = false )
1512
1513
1514 if g_debug then
1515 hr_utility.set_location('Processing '||l_proc, 100);
1516 end if;
1517
1518 END LOOP; -- t_periods
1519
1520 if g_debug then
1521 hr_utility.trace('After period loop');
1522 end if;
1523
1524 if g_debug then
1525 hr_utility.set_location('Processing '||l_proc, 110);
1526 end if;
1527
1528 -- reset variables
1529
1530 l_period_tab.delete;
1531
1532 END execute_formula;
1533
1534
1535 PROCEDURE check_time_overlaps
1536 (p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info
1537 ,p_messages IN OUT nocopy hxc_self_service_time_deposit.message_table) IS
1538
1539 l_bb_id NUMBER;
1540 l_bb_id_detail NUMBER;
1541 l_type VARCHAR2(30);
1542 l_type_detail VARCHAR2(30);
1543 l_start_time DATE;
1544 l_start_detail DATE;
1545 l_stop_time DATE;
1546 l_stop_detail DATE;
1547 l_scope VARCHAR2(30);
1548 l_scope_detail VARCHAR2(30);
1549 l_date_to DATE;
1550 l_date_to_detail DATE;
1551 -- vars for used for detecting potential db overlaps.
1552 l_start_day_period DATE;
1553 l_end_day_period DATE;
1554 l_earliest_tc DATE;
1555 l_latest_tc DATE;
1556 l_resource_id NUMBER;
1557 l_cnt NUMBER;
1558 l_detail NUMBER;
1559 l_tc_db_overlap BOOLEAN;
1560
1561 l_detail_ovn number; --added for bug 2796204
1562
1563 l_timecardid number; --added for bug 2796204
1564 l_timecardovn number; --added for bug 2796204
1565
1566
1567 -- Start new code for Bug 2889097
1568
1569 TYPE t_left_overlap_row IS RECORD
1570 ( earliest_tc DATE,
1571 detail_id NUMBER,
1572 detail_ovn NUMBER);
1573
1574 TYPE t_left_overlap_t IS TABLE OF
1575 t_left_overlap_row
1576 INDEX BY BINARY_INTEGER;
1577
1578 l_left_overlap t_left_overlap_t;
1579
1580 l_detail_count number;
1581
1582 l_detailid_right number;
1583 l_detailovn_right number;
1584
1585 -- End new code for Bug 2889097
1586
1587 -- Modified for Bug 8281720
1588 /*
1589 Modified the cursor to take care of overlapping of timecard with another timecard period for the
1590 below scenario.
1591
1592 1. Create TEMPLATE with Start time on LAST day 18:00 and stop time at 07:20
1593 on the last day of the week.
1594 2. DO NOT create a timecard for the current week.
1595 3. Create timecard (without template) for following week with Start time on
1596 FIRST day 06:00 and stop time at 18:00 on the FIRST day of the week.
1597
1598 *
1599 cursor range_details_of_day(p_day_date in DATE,p_resource_id in NUMBER)
1600 IS
1601 select /*+ ORDERED *
1602 tbbdet.start_time, tbbdet.stop_time
1603 from hxc_time_building_blocks tbbdet,
1604 hxc_time_building_blocks tbbday,
1605 hxc_time_building_blocks tbbtc
1606 where
1607 tbbtc.scope = 'TIMECARD'
1608 and tbbdet.scope = 'DETAIL'
1609 and tbbdet.type = 'RANGE'
1610 and tbbday.scope = 'DAY'
1611 and tbbtc.time_building_block_id = tbbday.parent_building_block_id
1612 and tbbtc.object_version_number = tbbday.parent_building_block_ovn
1613 and tbbdet.parent_building_block_id = tbbday.time_building_block_id
1614 and tbbdet.parent_building_block_ovn = tbbday.object_version_number
1615 and trunc(tbbday.start_time) = trunc(p_day_date)
1616 and tbbdet.resource_id = l_resource_id
1617 and tbbday.resource_id = l_resource_id
1618 and tbbtc.resource_id = l_resource_id
1619 and tbbdet.date_to = hr_general.end_of_time;
1620
1621 */
1622
1623 -- Bug 11837942
1624 -- Modified the above cursor to add the following hints sot that
1625 -- the right path is picked up.
1626
1627 CURSOR range_details_of_day(p_day_date IN DATE,
1628 p_resource_id IN NUMBER)
1629 IS
1630 SELECT /*+ LEADING(tbbday)
1631 INDEX(tbbday HXC_TIME_BUILDING_BLOCKS_FK2)
1632 INDEX(tbbdet HXC_TIME_BUILDING_BLOCKS_FK3)
1633 INDEX(tbbtc HXC_TIME_BUILDING_BLOCKS_PK) */
1634 tbbdet.start_time,
1635 tbbdet.stop_time
1636 FROM hxc_time_building_blocks tbbdet,
1637 hxc_time_building_blocks tbbday,
1638 hxc_time_building_blocks tbbtc
1639 WHERE tbbtc.scope = 'TIMECARD'
1640 AND tbbdet.scope = 'DETAIL'
1641 AND tbbdet.type = 'RANGE'
1642 AND tbbday.scope = 'DAY'
1643 AND tbbtc.time_building_block_id = tbbday.parent_building_block_id
1644 AND tbbtc.object_version_number = tbbday.parent_building_block_ovn
1645 AND tbbdet.parent_building_block_id = tbbday.time_building_block_id
1646 AND tbbdet.parent_building_block_ovn = tbbday.object_version_number
1647 AND TRUNC(tbbday.start_time) = TRUNC(p_day_date)
1648 AND tbbdet.resource_id = l_resource_id
1649 AND tbbday.resource_id = l_resource_id
1650 AND tbbtc.resource_id = l_resource_id
1651 AND tbbdet.date_to = hr_general.end_of_time;
1652
1653
1654 BEGIN
1655
1656
1657 l_earliest_tc := hr_general.end_of_time;
1658
1659 l_latest_tc := hr_general.start_of_time;
1660
1661 l_timecardid:=null; --added for bug 2796204
1662 l_timecardovn:=null; --added for bug 2796204
1663
1664 -- Start new code for Bug 2889097
1665
1666 l_detail_count := 0;
1667
1668 l_detailid_right := NULL;
1669
1670 l_detailovn_right := NULL;
1671
1672 -- End new code for Bug 2889097
1673
1674 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1675 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECK_TIME_OVERLAPS',
1676 'Number of BBs '||p_time_building_blocks.count);
1677 end if;
1678
1679 l_cnt := p_time_building_blocks.first ;
1680 LOOP
1681 EXIT WHEN NOT p_time_building_blocks.EXISTS(l_cnt);
1682
1683 l_bb_id := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1684 l_type := p_time_building_blocks(l_cnt).TYPE;
1685 l_start_time := p_time_building_blocks(l_cnt).START_TIME;
1686 l_stop_time := p_time_building_blocks(l_cnt).STOP_TIME;
1687 l_scope := p_time_building_blocks(l_cnt).SCOPE;
1688 l_resource_id := p_time_building_blocks(l_cnt).RESOURCE_ID;
1689 l_date_to := p_time_building_blocks(l_cnt).DATE_TO;
1690
1691 -- Record the period start / end
1692 IF (l_scope = 'TIMECARD') THEN
1693 l_timecardid :=p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID; --added for bug 2796204
1694 l_timecardovn :=p_time_building_blocks(l_cnt).object_version_number; --added for bug 2796204
1695 l_start_day_period := l_start_time;
1696 l_end_day_period := l_stop_time;
1697 END IF;
1698
1699 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1700 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECK_TIME_OVERLAPS',
1701 'Outer Loop BB_ID '||l_bb_id);
1702 end if;
1703 --
1704 -- Check for Overlap Time for DETAIL blocks of type RANGE and start_time
1705 -- and stop_time not null and make sure they are not end-dated!
1706 --
1707 IF (l_scope = 'DETAIL' AND l_type = 'RANGE' AND
1708 l_start_time is NOT NULL AND l_stop_time is NOT NULL AND
1709 l_date_to = hr_general.end_of_time ) THEN
1710
1711 --
1712 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1713 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1714 'Outer is a DETAIL RANGE');
1715 end if;
1716
1717 -- Keep track of earliest / latest building block start / stop times
1718
1719 -- Start commented code for Bug 2889097
1720 -- IF( l_start_time < l_earliest_tc) THEN
1721 -- End commented code for Bug 2889097
1722
1723 -- Start new code for Bug 2889097
1724 -- Trap all the Timecards as on earliest day of timecard period.
1725 IF( TRUNC(l_start_time) <= TRUNC(l_earliest_tc) ) THEN
1726 -- End new code for Bug 2889097
1727 l_earliest_tc := l_start_time;
1728
1729 -- Start new code for Bug 2889097
1730 l_left_overlap(l_detail_count).earliest_tc := l_start_time;
1731 l_left_overlap(l_detail_count).detail_id := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1732 l_left_overlap(l_detail_count).detail_ovn := p_time_building_blocks(l_cnt).object_version_number;
1733 l_detail_count := l_detail_count + 1;
1734 -- End new code for Bug 2889097
1735 END IF;
1736
1737 IF( l_stop_time > l_latest_tc) THEN
1738 l_latest_tc := l_stop_time;
1739 -- Start new code for Bug 2889097
1740 l_detailid_right := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1741 l_detailovn_right := p_time_building_blocks(l_cnt).object_version_number;
1742 -- End new code for Bug 2889097
1743 END IF;
1744
1745 l_detail := l_cnt;
1746 LOOP
1747 EXIT WHEN NOT p_time_building_blocks.EXISTS(l_detail);
1748
1749 l_bb_id_detail := p_time_building_blocks(l_detail).time_building_block_id;
1750 l_type_detail := p_time_building_blocks(l_detail).TYPE;
1751 l_start_detail := p_time_building_blocks(l_detail).START_TIME;
1752 l_stop_detail := p_time_building_blocks(l_detail).STOP_TIME;
1753 l_scope_detail := p_time_building_blocks(l_detail).SCOPE;
1754 l_date_to_detail := p_time_building_blocks(l_detail).DATE_TO;
1755 l_detail_ovn := p_time_building_blocks(l_detail).OBJECT_VERSION_NUMBER; --added for bug 2796204
1756
1757 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1758 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1759 'Inner Loop BB_ID '||l_bb_id_detail);
1760 end if;
1761 --
1762 IF (l_scope_detail = 'DETAIL' AND l_type_detail = 'RANGE'
1763 AND l_start_detail is NOT NULL AND l_stop_detail is NOT NULL AND
1764 l_date_to_detail = hr_general.end_of_time ) THEN
1765 --
1766 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1767 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1768 'Inner is a DETAIL RANGE');
1769 end if;
1770
1771 IF (l_stop_time > l_start_detail AND
1772 l_start_time < l_stop_detail AND
1773 l_bb_id <> l_bb_id_detail)
1774 THEN
1775 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1776 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1777 'Overlap Detected');
1778 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1779 'Start BB Outer '||to_char(l_start_time,'DD-MON-YYYY:HH24:MI'));
1780 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1781 'Stop BB Outer '||to_char(l_stop_time,'DD-MON-YYYY:HH24:MI'));
1782 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1783 'Start BB Inner '||to_char(l_start_detail,'DD-MON-YYYY:HH24:MI'));
1784 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1785 'Stop BB Inner '||to_char(l_stop_detail,'DD-MON-YYYY:HH24:MI'));
1786 end if;
1787 --
1788 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1789 p_message_table => p_messages
1790 , p_message_name => 'HXT_39256_OVERLAPPING_TIME'
1791 , p_message_token => NULL
1792 , p_message_level => 'ERROR'
1793 , p_message_field => NULL
1794 , p_application_short_name => 'HXT'
1795 , p_timecard_bb_id => l_bb_id_detail --added for bug 2796204
1796 , p_time_attribute_id => NULL
1797 , p_timecard_bb_ovn => l_detail_ovn --added for bug 2796204
1798 , p_time_attribute_ovn => NULL );
1799
1800 --
1801 EXIT;
1802 --
1803 END IF;
1804 --
1805 END IF;
1806 --
1807 l_detail := p_time_building_blocks.NEXT(l_detail);
1808 END LOOP;
1809 --
1810 END IF;
1811 --
1812 l_cnt := p_time_building_blocks.NEXT(l_cnt);
1813 END LOOP;
1814
1815 -- we have checked for overlaps within the timecard. Now we need to check to
1816 -- if any of the db ranges overlap the ranges in the timecard due to graveyard type
1817 -- work patterns.
1818 -- We have also stored the start of the earliest DETAIL RANGE (l_earliest_tc) and the
1819 -- stop of the latest DETAIL RANGE (l_latest_tc)
1820
1821 -- We know the latest day and the earliest day in the timecard period.
1822 -- We make the assumption that only the day before the first day in the current period
1823 -- and the day after the last day in the period could have details that overlap.
1824
1825 l_tc_db_overlap := FALSE;
1826
1827 -- Start new code for Bug 2889097
1828 -- Check if the earliest day is infact the start day of Timecard period.
1829 If ( TRUNC(l_start_day_period) = TRUNC(l_earliest_tc) ) Then
1830 -- End new code for Bug 2889097
1831
1832 -- we pick up the details of the day before the first day in the current period
1833 FOR l_range_detail IN range_details_of_day(trunc(l_start_day_period-1),l_resource_id) LOOP
1834 -- Start new code for Bug 2889097
1835 l_detail_count := l_left_overlap.first;
1836 LOOP
1837 EXIT WHEN NOT l_left_overlap.exists(l_detail_count);
1838 -- End new code for Bug 2889097
1839
1840 -- Start commented code for Bug 2889097
1841 -- IF(l_range_detail.stop_time > l_earliest_tc) THEN
1842 -- End commented code for Bug 2889097
1843
1844 -- Start new code for Bug 2889097
1845 IF(l_range_detail.stop_time > l_left_overlap(l_detail_count).earliest_tc) THEN
1846 -- End new code for Bug 2889097
1847 l_tc_db_overlap :=TRUE;
1848
1849 -- Start new code for Bug 2889097
1850 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1851 p_message_table => p_messages
1852 , p_message_name => 'HXC_OVRLPPNG_TIME_TC_V_DB'
1853 , p_message_token => NULL
1854 , p_message_level => 'ERROR'
1855 , p_message_field => NULL
1856 , p_application_short_name => 'HXC'
1857 , p_timecard_bb_id => l_left_overlap(l_detail_count).detail_id
1858 , p_time_attribute_id => NULL
1859 , p_timecard_bb_ovn => l_left_overlap(l_detail_count).detail_ovn
1860 , p_time_attribute_ovn => NULL );
1861 -- End new code for Bug 2889097
1862
1863 END IF;
1864
1865 -- Start new code for Bug 2889097
1866 l_detail_count := l_left_overlap.next(l_detail_count);
1867 END Loop;
1868 -- End new code for Bug 2889097
1869 END LOOP;
1870 -- Start new code for Bug 2889097
1871 End If;
1872 -- End new code for Bug 2889097
1873
1874 -- pick up the details of the day after the last day in the current period
1875
1876 FOR l_range_detail IN range_details_of_day(trunc(l_end_day_period+1),l_resource_id) LOOP
1877 IF(l_range_detail.start_time < l_latest_tc ) THEN
1878 l_tc_db_overlap :=TRUE;
1879 -- Start new code for Bug 2889097
1880 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1881 p_message_table => p_messages
1882 , p_message_name => 'HXC_OVRLPPNG_TIME_TC_V_DB'
1883 , p_message_token => NULL
1884 , p_message_level => 'ERROR'
1885 , p_message_field => NULL
1886 , p_application_short_name => 'HXC'
1887 , p_timecard_bb_id => l_detailid_right
1888 , p_time_attribute_id => NULL
1889 , p_timecard_bb_ovn => l_detailovn_right
1890 , p_time_attribute_ovn => NULL );
1891 -- End new code for Bug 2889097
1892 END IF;
1893 END LOOP;
1894
1895 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1896 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1897 'L_TC_EARLIEST:'||to_char(l_earliest_tc,'DD-MON:HH24:MI'));
1898 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1899 'L_TC_LATEST:'||to_char(l_latest_tc,'DD-MON:HH24:MI'));
1900 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1901 'DAY_BEFORE:'||to_char(trunc(l_start_day_period-1)));
1902 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1903 'DAY_AFTER:'||to_char(trunc(l_end_day_period+1)));
1904 end if;
1905
1906 -- Start commented code for Bug 2889097
1907 -- if we have found an overlap, add and error. Note that the error differs from
1908 -- the error raised if the overlap is detected within the timecard to help the user.
1909 -- Note also that we could give the user more information in the msgs such as
1910 -- times of overlapping ranges if this is required.
1911
1912 --IF (l_tc_db_overlap = TRUE) THEN
1913 -- hxc_time_entry_rules_utils_pkg.add_error_to_table (
1914 -- p_message_table => p_messages
1915 -- , p_message_name => 'HXC_OVRLPPNG_TIME_TC_V_DB'
1916 -- , p_message_token => NULL
1917 -- , p_message_level => 'ERROR'
1918 -- , p_message_field => NULL
1919 -- , p_application_short_name => 'HXC'
1920 -- , p_timecard_bb_id => l_timecardid --added for bug 2796204
1921 -- , p_time_attribute_id => NULL
1922 -- , p_timecard_bb_ovn => l_timecardovn --added for bug 2796204
1923 -- , p_time_attribute_ovn => NULL );
1924 --
1925 --END IF;
1926 -- End commented code for Bug 2889097
1927
1928 END check_time_overlaps;
1929
1930
1931 --
1932 -- ----------------------------------------------------------------------------
1933 -- |------------------------< execute_field_combo_rule >----------------------|
1934 -- ----------------------------------------------------------------------------
1935 --
1936 -- Description: executes rules which reference the two seeded field combination
1937 -- formulae and populates the global error table accordingly
1938 --
1939 --
1940 -- Prerequisites:
1941 --
1942 -- None
1943 --
1944 -- In Parameters:
1945 -- Name Reqd Type Description
1946 --
1947 -- p_formula_name Yes varchar2 formula name
1948 -- p_message_table Yes hxc_self_service_time_deposit.message_table
1949 -- p_message_level Yes varchar2 TER message level
1950 -- p_rule_record Yes hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
1951 -- p_tco_bb Yes hxc_self_service_time_deposit.timecard_info
1952 -- p_tco_att Yes hxc_self_service_time_deposit.building_block_attribute_info
1953 -- p_timecard_info Yes r_timecard_info Timecard Information
1954 --
1955 --
1956 -- Access Status:
1957 -- Public.
1958 --
1959
1960 PROCEDURE execute_field_combo_rule (
1961 p_formula_name varchar2
1962 , p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table
1963 , p_message_level varchar2
1964 , p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
1965 , p_tco_bb hxc_self_service_time_deposit.timecard_info
1966 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info ) IS
1967
1968 l_proc VARCHAR2(72);
1969
1970 l_param_rec hxc_ff_dict.r_param;
1971
1972 l_period_id hxc_recurring_periods.recurring_period_id%TYPE;
1973 l_reference_period NUMBER(10);
1974 l_consider_zero_hours VARCHAR2(10);
1975
1976 l_tc_id_1 hxc_time_categories.time_category_id%TYPE;
1977 l_tc_id_2 hxc_time_categories.time_category_id%TYPE;
1978
1979 l_bb_ind BINARY_INTEGER;
1980
1981 TYPE r_tc1 IS RECORD ( match VARCHAR2(1) );
1982
1983 TYPE t_tc1 IS TABLE OF r_tc1 INDEX BY BINARY_INTEGER;
1984
1985 l_tc1_tab t_tc1;
1986
1987 BEGIN
1988
1989
1990
1991 if g_debug then
1992 l_proc := g_package||'execute_field_combo_rule';
1993 hr_utility.set_location('Processing '||l_proc, 10);
1994 end if;
1995
1996 hxc_ff_dict.decode_formula_segments (
1997 p_formula_name => p_formula_name
1998 , p_rule_rec => p_rule_record
1999 , p_param_rec => l_param_rec
2000 , p_period_value => l_period_id
2001 , p_reference_value => l_reference_period
2002 , p_consider_zero_hours => l_consider_zero_hours );
2003
2004 if g_debug then
2005 hr_utility.trace(' ************* Param values are.... ************ ');
2006 hr_utility.trace('');
2007 hr_utility.trace(' Rule name is '||p_rule_record.name);
2008 hr_utility.trace('');
2009 hr_utility.trace(' Time Category ID I is '||l_param_rec.param1_value);
2010 hr_utility.trace(' Time Category ID II is '||l_param_rec.param2_value);
2011 end if;
2012
2013 l_tc_id_1 := to_number(l_param_rec.param1_value);
2014 l_tc_id_2 := to_number(l_param_rec.param2_value);
2015
2016 if g_debug then
2017 hr_utility.set_location('Processing '||l_proc, 20);
2018 end if;
2019
2020 -- we always have to intialise the first time category so do it now
2021
2022 hxc_time_category_utils_pkg.initialise_time_category (
2023 p_time_category_id => l_tc_id_1
2024 , p_tco_att => p_tco_att );
2025
2026 if g_debug then
2027 hr_utility.set_location('Processing '||l_proc, 30);
2028 end if;
2029
2030 -- now process the first time category
2031
2032 l_bb_ind := p_tco_bb.FIRST;
2033
2034 WHILE l_bb_ind IS NOT NULL
2035 LOOP
2036
2037 IF ( p_tco_bb(l_bb_ind).scope = 'DETAIL' and
2038 p_tco_bb(l_bb_ind).date_to = hr_general.end_of_time ) --Fix for Bug#2943285
2039 THEN
2040
2041 IF ( NOT hxc_time_category_utils_pkg.chk_tc_bb_ok ( p_tco_bb(l_bb_ind).time_building_block_id ) )
2042 THEN
2043
2044 IF ( l_tc_id_2 IS NULL )
2045 THEN
2046
2047 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2048 p_message_table => p_message_table
2049 , p_message_name => 'HXC_TER_VIOLATION' --'HXC_'||p_rule_record.name
2050 , p_message_token => 'TER&'|| p_rule_record.ter_message_name
2051 , p_message_level => p_message_level
2052 , p_message_field => NULL
2053 , p_timecard_bb_id => p_tco_bb(l_bb_ind).time_building_block_id
2054 , p_time_attribute_id => NULL
2055 , p_timecard_bb_ovn => p_tco_bb(l_bb_ind).object_version_number
2056 , p_time_attribute_ovn => NULL );
2057
2058 END IF;
2059
2060 ELSE
2061
2062 IF ( l_tc_id_2 IS NOT NULL )
2063 THEN
2064
2065 l_tc1_tab(p_tco_bb(l_bb_ind).time_building_block_id).match := 'Y';
2066
2067 END IF;
2068
2069 END IF;
2070
2071 END IF;
2072
2073 l_bb_ind := p_tco_bb.NEXT(l_bb_ind);
2074
2075 END LOOP;
2076
2077 IF ( l_tc_id_2 IS NOT NULL )
2078 THEN
2079
2080 hxc_time_category_utils_pkg.initialise_time_category (
2081 p_time_category_id => l_tc_id_2
2082 , p_tco_att => p_tco_att );
2083
2084 -- now process the second time category
2085
2086 l_bb_ind := p_tco_bb.FIRST;
2087
2088 WHILE l_bb_ind IS NOT NULL
2089 LOOP
2090
2091 IF ( p_tco_bb(l_bb_ind).scope = 'DETAIL' and
2092 p_tco_bb(l_bb_ind).date_to = hr_general.end_of_time ) --Fix for Bug#2943285
2093 THEN
2094
2095 IF ( hxc_time_category_utils_pkg.chk_tc_bb_ok ( p_tco_bb(l_bb_ind).time_building_block_id ) )
2096 THEN
2097
2098 -- since this building block matches the time category check to make sure that the bb
2099 -- did not match the first time category, if it did then raise an error
2100
2101 IF l_tc1_tab.EXISTS(p_tco_bb(l_bb_ind).time_building_block_id)
2102 THEN
2103
2104 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2105 p_message_table => p_message_table
2106 , p_message_name => 'HXC_TER_VIOLATION' --'HXC_'||p_rule_record.name
2107 , p_message_token => 'TER&'|| p_rule_record.ter_message_name
2108 , p_message_level => p_message_level
2109 , p_message_field => NULL
2110 , p_timecard_bb_id => p_tco_bb(l_bb_ind).time_building_block_id
2111 , p_time_attribute_id => NULL
2112 , p_timecard_bb_ovn => p_tco_bb(l_bb_ind).object_version_number
2113 , p_time_attribute_ovn => NULL );
2114
2115 END IF;
2116
2117 END IF;
2118
2119 END IF;
2120
2121 l_bb_ind := p_tco_bb.NEXT(l_bb_ind);
2122
2123 END LOOP;
2124
2125 END IF; -- ( l_tc_id_2 IS NOT NULL )
2126
2127 EXCEPTION WHEN OTHERS THEN
2128
2129 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2130 p_message_table => p_message_table
2131 , p_message_name => 'EXCEPTION'
2132 , p_message_token => NULL
2133 , p_message_level => p_message_level
2134 , p_message_field => NULL
2135 , p_timecard_bb_id => p_tco_bb(l_bb_ind).time_building_block_id
2136 , p_time_attribute_id => NULL
2137 , p_timecard_bb_ovn => p_tco_bb(l_bb_ind).object_version_number
2138 , p_time_attribute_ovn => NULL );
2139
2140 END execute_field_combo_rule;
2141
2142
2143 /*****************************************************************
2144 *
2145 * Main Procedure - execute time entry rules
2146 *
2147 *****************************************************************/
2148
2149 BEGIN -- execute_time_entry_rules
2150
2151 g_debug := hr_utility.debug_enabled;
2152
2153 if g_debug then
2154 l_proc := g_package||'execute_time_entry_rules';
2155 hr_utility.set_location('Processing '||l_proc, 10);
2156 end if;
2157
2158 get_timecard_info (
2159 p_time_building_blocks => p_time_building_blocks
2160 , p_timecard_rec => l_timecard_info_rec );
2161
2162 -- set submission date to be within valid assignment
2163
2164 -- GPM v115.69
2165
2166 IF ( hxc_time_entry_rules_utils_pkg.g_assignment_info.EXISTS ( l_timecard_info_Rec.resource_id ) )
2167 THEN
2168
2169 IF ( ( l_timecard_info_Rec.start_date =
2170 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).start_date )
2171 AND
2172 ( l_timecard_info_Rec.end_date =
2173 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).end_date ) )
2174 THEN
2175
2176 l_submission_date :=
2177 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).submission_date;
2178
2179 ELSE
2180
2181 -- overwrite cached assignment info since for a different timecard period
2182 -- this would only happen if the timecard period on a submission change
2183 -- i.e. if the cached value is used for a different timecard submission for the
2184 -- same user
2185
2186 set_global_asg_info ( l_timecard_info_Rec.resource_id
2187 , l_timecard_info_Rec.start_date
2188 , l_timecard_info_Rec.end_date );
2189
2190 l_submission_date :=
2191 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).submission_date;
2192
2193 END IF;
2194 ELSE
2195
2196 set_global_asg_info ( l_timecard_info_Rec.resource_id
2197 , l_timecard_info_Rec.start_date
2198 , l_timecard_info_Rec.end_date );
2199
2200 l_submission_date :=
2201 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).submission_date;
2202
2203 END IF;
2204
2205
2206 IF ( l_timecard_info_rec.resource_id = 13577 )
2207 THEN
2208
2209 null;
2210
2211
2212 END IF;
2213
2214 -- loop through the time entry rules based on the resource's
2215 -- preference and get the message level
2216
2217 if g_debug then
2218 hr_utility.set_location('Processing '||l_proc, 20);
2219 end if;
2220
2221 l_rules_evl := hxc_preference_evaluation.resource_preferences(
2222 p_resource_id => l_timecard_info_rec.resource_id
2223 ,p_pref_code => 'TC_W_RULES_EVALUATION'
2224 ,p_attribute_n => 1
2225 ,p_evaluation_date => l_submission_date );
2226
2227 IF l_rules_evl = 'Y'
2228 THEN
2229 check_time_overlaps (
2230 p_time_building_blocks => p_time_building_blocks
2231 , p_messages => p_messages );
2232
2233 END IF;
2234
2235 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
2236 p_preference_code => 'TS_PER_TIME_ENTRY_RULES',
2237 p_start_evaluation_date => l_timecard_info_rec.start_date,
2238 p_end_evaluation_date => l_timecard_info_rec.end_date,
2239 p_sorted_pref_table => l_pref_table,
2240 p_master_pref_table => p_master_pref_table );
2241
2242 l_terg_id := l_pref_table(1).attribute1 ;
2243
2244 if g_debug then
2245 hr_utility.set_location('Processing '||l_proc, 25);
2246 end if;
2247
2248 FOR r_rules IN csr_get_rules ( p_terg_id => l_terg_id
2249 , p_start_date => l_timecard_info_rec.start_date
2250 , p_end_date => l_timecard_info_rec.end_date )
2251 LOOP
2252 if g_debug then
2253 hr_utility.set_location('Processing '||l_proc, 30);
2254 end if;
2255
2256 -- GPM v115.61 2180942
2257
2258 if g_debug then
2259 hr_utility.trace('GAZ OPERATION IS '||p_operation);
2260 hr_utility.trace('GAZ RESUBMIT IS '||p_resubmit);
2261 end if;
2262
2263 -- maintain global record of TER info
2264
2265 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_name := r_rules.name;
2266 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_message_name := r_rules.ter_message_name;
2267 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_usage := r_rules.rule_usage;
2268 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_formula_name := NVL( r_rules.formula_name, 'NULL FORMULA');
2269 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id := NULL;
2270
2271 IF ( ( p_operation = 'SAVE' AND r_rules.rule_usage = 'SAVE' ) OR
2272 ( p_operation = 'SUBMIT' AND p_resubmit = 'NO' AND r_rules.rule_usage = 'SUBMISSION' ) OR
2273 ( p_operation = 'SUBMIT' AND p_resubmit = 'YES' AND r_rules.rule_usage = 'RESUBMISSION' ) OR
2274 ( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'BOTH' ) OR
2275 ( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'DELETE_ONLY' AND l_timecard_info_rec.deleted = 'Y' ) OR
2276 ( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'BOTH_EX_DEL' AND l_timecard_info_rec.deleted = 'N' )
2277 )
2278 THEN
2279
2280 if g_debug then
2281 hr_utility.trace('');
2282 hr_utility.trace('********** Rule Name is '||r_rules.name||' **************');
2283 hr_utility.trace('');
2284 end if;
2285
2286 IF ( r_rules.formula_id IS NULL )
2287 THEN
2288 if g_debug then
2289 hr_utility.set_location('Processing '||l_proc, 40);
2290 end if;
2291 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2292 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'hxc_time_entry_rules', 'after 40');
2293 end if;
2294
2295 -- GPM v115.87
2296
2297 IF ( r_rules.name in ( 'Overlapping Time Entries', 'Overlapping Time Entries - Save') )
2298 AND l_rules_evl = 'N'
2299 THEN
2300
2301 -- still need to decide if we call the chk mapping changed
2302
2303 check_time_overlaps (
2304 p_time_building_blocks => p_time_building_blocks
2305 , p_messages => p_messages );
2306
2307 END IF;
2308
2309 -- in the case of wtd a rule without a formula is meaningless
2310 -- if the formula is null then we must have a mapping id but if we evaluate
2311 -- the mapping what do we do?
2312 --
2313 -- gaz - maybe need to look into this further or add formula_id is not null in csr
2314
2315 ELSIF ( r_rules.formula_id IS NOT NULL AND r_rules.mapping_id IS NOT NULL )
2316 THEN
2317 if g_debug then
2318 hr_utility.set_location('Processing '||l_proc, 50);
2319 end if;
2320
2321 IF ( hxc_mapping_utilities.chk_mapping_changed(
2322 p_mapping_id => r_rules.mapping_id
2323 ,p_timecard_bb_id => l_timecard_info_rec.timecard_bb_id
2324 ,p_timecard_ovn => l_timecard_info_rec.timecard_ovn
2325 ,p_start_date => l_timecard_info_rec.start_date
2326 ,p_end_date => l_timecard_info_rec.end_date
2327 ,p_last_status =>
2328 p_time_building_blocks(p_time_building_blocks.FIRST).approval_status
2329 ,p_time_building_blocks => p_time_building_blocks
2330 ,p_time_attributes => p_time_attributes
2331 ,p_called_from => 'TIME_ENTRY'
2332 ))
2333 THEN
2334 if g_debug then
2335 hr_utility.set_location('Processing '||l_proc, 60);
2336 end if;
2337
2338 execute_formula ( p_formula_name=> r_rules.formula_name
2339 , p_message_table => p_messages
2340 , p_message_level => r_rules.rule_outcome
2341 , p_rule_record => r_rules
2342 , p_tco_bb => p_time_building_blocks
2343 , p_tco_att => p_time_attributes
2344 , p_timecard_info => l_timecard_info_rec );
2345
2346 END IF;
2347
2348 ELSE -- basically means ( r_rules.formula_id IS NOT NULL AND r_rules.mapping_id IS NULL )
2349
2350 if g_debug then
2351 hr_utility.set_location('Processing '||l_proc, 70);
2352 end if;
2353
2354 -- special case for Field Combination rules
2355
2356 IF ( r_rules.formula_name in ( 'HXC_FIELD_COMBO_INCLUSIVE', 'HXC_FIELD_COMBO_EXCLUSIVE' ) )
2357 THEN
2358
2359 execute_field_combo_rule (
2360 p_formula_name => r_rules.formula_name
2361 , p_message_table => p_messages
2362 , p_message_level => r_rules.rule_outcome
2363 , p_rule_record => r_rules
2364 , p_tco_bb => p_time_building_blocks
2365 , p_tco_att => p_time_attributes );
2366
2367 ELSE
2368
2369 execute_formula ( p_formula_name=> r_rules.formula_name
2370 , p_message_table => p_messages
2371 , p_message_level => r_rules.rule_outcome
2372 , p_rule_record => r_rules
2373 , p_tco_bb => p_time_building_blocks
2374 , p_tco_att => p_time_attributes
2375 , p_timecard_info => l_timecard_info_rec );
2376
2377 END IF;
2378
2379 END IF;
2380
2381 END IF; -- p_operator / r_rules.rule_usage test
2382
2383
2384
2385 IF ( l_timecard_info_rec.resource_id = 13577 )
2386 THEN
2387
2388 null;
2389
2390
2391 END IF;
2392
2393
2394 END LOOP; -- csr_get_rules
2395
2396 if g_debug then
2397 hr_utility.set_location('Processing '||l_proc, 75);
2398 end if;
2399
2400 EXCEPTION WHEN OTHERS THEN
2401
2402 if g_debug then
2403 hr_utility.trace('SQLERRM is '||SQLERRM);
2404 end if;
2405
2406 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2407 p_message_table => p_messages
2408 , p_message_name => 'EXCEPTION'
2409 , p_message_token => NULL
2410 , p_message_level => 'ERROR'
2411 , p_message_field => NULL
2412 , p_timecard_bb_id => l_timecard_info_rec.timecard_bb_id
2413 , p_time_attribute_id => NULL
2414 , p_timecard_bb_ovn => l_timecard_info_rec.timecard_ovn
2415 , p_time_attribute_ovn => NULL );
2416
2417 IF ( l_timecard_info_rec.resource_id = 13577 )
2418 THEN
2419
2420 null;
2421
2422
2423 END IF;
2424
2425 END execute_time_entry_rules;
2426 --
2427
2428 FUNCTION period_maximum (
2429 p_resource_id NUMBER
2430 , p_submission_date VARCHAR2
2431 , p_period_maximum NUMBER
2432 , p_period NUMBER default 1
2433 , p_reference_period NUMBER default 1
2434 , p_pre_period_start VARCHAR2
2435 , p_pre_period_end VARCHAR2
2436 , p_post_period_start VARCHAR2 default null
2437 , p_post_period_end VARCHAR2 default null
2438 , p_ref_period_start VARCHAR2 default null
2439 , p_ref_period_end VARCHAR2 default null
2440 , p_duration_in_days NUMBER default 1
2441 , p_timecard_hrs NUMBER default 0 ) RETURN NUMBER IS
2442
2443 l_return NUMBER;
2444
2445 BEGIN
2446
2447 l_return := period_maximum (
2448 p_resource_id => p_resource_id
2449 , p_submission_date => p_submission_date
2450 , p_period_maximum => p_period_maximum
2451 , p_period => p_period
2452 , p_reference_period => p_reference_period
2453 , p_pre_period_start => p_pre_period_start
2454 , p_pre_period_end => p_pre_period_end
2455 , p_post_period_start => p_post_period_start
2456 , p_post_period_end => p_post_period_end
2457 , p_ref_period_start => p_ref_period_start
2458 , p_ref_period_end => p_ref_period_end
2459 , p_duration_in_days => p_duration_in_days
2460 , p_timecard_hrs => p_timecard_hrs
2461 , p_operator => NULL );
2462
2463 RETURN l_return;
2464
2465 END period_maximum;
2466
2467
2468 FUNCTION period_maximum (
2469 p_resource_id NUMBER
2470 , p_submission_date VARCHAR2
2471 , p_period_maximum NUMBER
2472 , p_period NUMBER default 1
2473 , p_reference_period NUMBER default 1
2474 , p_pre_period_start VARCHAR2
2475 , p_pre_period_end VARCHAR2
2476 , p_post_period_start VARCHAR2 default null
2477 , p_post_period_end VARCHAR2 default null
2478 , p_ref_period_start VARCHAR2 default null
2479 , p_ref_period_end VARCHAR2 default null
2480 , p_duration_in_days NUMBER default 1
2481 , p_timecard_hrs NUMBER default 0
2482 , p_operator VARCHAR2 ) RETURN NUMBER IS
2483
2484
2485 CURSOR csr_get_total_hrs ( p_start_date DATE, p_end_date DATE) IS
2486 SELECT NVL(SUM(SUM(NVL(hxc_time_category_utils_pkg.category_detail_hrs( tbb_detail.time_building_block_id,
2487 tbb_detail.object_version_number),0)) ),0)
2488 FROM
2489 hxc_timecard_summary ts,
2490 hxc_time_building_blocks tbb_day,
2491 hxc_time_building_blocks tbb_detail
2492 WHERE
2493 tbb_day.time_building_block_id = tbb_detail.parent_building_block_id and
2494 ts.timecard_id = tbb_day.parent_building_block_id and
2495 tbb_detail.scope='DETAIL' and
2496 tbb_detail.date_to=hr_general.end_of_time and
2497 tbb_day.scope='DAY' and
2498 tbb_day.type='RANGE' and
2499 tbb_day.date_to=hr_general.end_of_time and
2500 ts.resource_id = p_resource_id and
2501 to_date(to_char(tbb_day.start_time,
2502 'DD-MON-YYYY'),
2503 'DD-MON-YYYY') BETWEEN p_start_date AND p_end_date
2504 AND to_date(to_char(tbb_day.stop_time,
2505 'DD-MON-YYYY'),
2506 'DD-MON-YYYY') BETWEEN p_start_date AND p_end_date
2507 group by ts.timecard_id ;
2508
2509 l_submission_date DATE;
2510
2511 l_period_type hxc_recurring_periods.period_type%TYPE;
2512 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
2513
2514 l_return NUMBER;
2515
2516 l_total_hrs NUMBER := 0;
2517 l_hrs NUMBER := 0;
2518 l_ref_period_hrs NUMBER := 0;
2519 l_number_of_periods NUMBER(6,2);
2520
2521 l_old_tc_id NUMBER(15);
2522
2523 l_period_start DATE;
2524 l_period_start_date DATE;
2525 l_period_end_date DATE;
2526
2527 l_db_pre_period_start DATE;
2528 l_db_pre_period_end DATE;
2529 l_db_post_period_start DATE;
2530 l_db_post_period_end DATE;
2531 l_db_ref_period_start DATE;
2532 l_db_ref_period_end DATE;
2533
2534 l_proc VARCHAR2(72);
2535
2536 l_tc_bld_blks hxc_self_service_time_deposit.timecard_info;
2537 l_tc_ind binary_integer;
2538
2539
2540 BEGIN -- period_maximum
2541
2542 g_debug := hr_utility.debug_enabled;
2543
2544 if g_debug then
2545 l_proc := g_package||'period_maximum';
2546 hr_utility.set_location('Processing '||l_proc, 10);
2547
2548 hr_utility.trace('Lets see what is in the structure');
2549 end if;
2550
2551 l_tc_bld_blks := hxc_self_service_time_deposit.get_building_blocks;
2552
2553 l_tc_ind := l_tc_bld_blks.first;
2554
2555 while l_tc_ind is not null
2556 loop
2557
2558 if g_debug then
2559 hr_utility.trace('scope is '||l_tc_bld_blks(l_tc_ind).scope);
2560 hr_utility.trace('bb id is '||to_number(l_tc_bld_blks(l_tc_ind).time_building_Block_id));
2561 hr_utility.trace('new is '||l_tc_bld_blks(l_tc_ind).new);
2562 hr_utility.trace('changed is '||l_tc_bld_blks(l_tc_ind).changed);
2563 end if;
2564
2565 l_tc_ind := l_tc_bld_blks.NEXT(l_tc_ind);
2566
2567 end loop;
2568
2569 -- first convert dates to proper dates
2570
2571 l_submission_date := TO_DATE(p_submission_date, 'YYYY/MM/DD HH24:MI:SS');
2572
2573 IF ( p_pre_period_start <> ' ' )
2574 THEN
2575 l_db_pre_period_start := TO_DATE(p_pre_period_start, 'YYYY/MM/DD HH24:MI:SS');
2576 ELSE
2577 l_db_pre_period_start := NULL;
2578 END IF;
2579
2580 IF ( p_pre_period_end <> ' ' )
2581 THEN
2582 l_db_pre_period_end := TO_DATE(p_pre_period_end, 'YYYY/MM/DD HH24:MI:SS');
2583 ELSE
2584 l_db_pre_period_end := NULL;
2585 END IF;
2586
2587 IF ( p_post_period_start <> ' ' )
2588 THEN
2589 l_db_post_period_start := TO_DATE(p_post_period_start,'YYYY/MM/DD HH24:MI:SS');
2590 ELSE
2591 l_db_post_period_start := NULL;
2592 END IF;
2593
2594 IF ( p_post_period_end <> ' ' )
2595 THEN
2596 l_db_post_period_end := TO_DATE(p_post_period_end, 'YYYY/MM/DD HH24:MI:SS');
2597 ELSE
2598 l_db_post_period_end := NULL;
2599 END IF;
2600
2601 IF ( p_ref_period_start <> ' ' )
2602 THEN
2603 l_db_ref_period_start := TO_DATE(p_ref_period_start,'YYYY/MM/DD HH24:MI:SS');
2604 ELSE
2605 l_db_ref_period_start := NULL;
2606 END IF;
2607
2608 IF ( p_ref_period_end <> ' ' )
2609 THEN
2610 l_db_ref_period_end := TO_DATE(p_ref_period_end, 'YYYY/MM/DD HH24:MI:SS');
2611 ELSE
2612 l_db_ref_period_end := NULL;
2613 END IF;
2614
2615 -- remember p_period_start/end is the remainder of the period
2616 -- not included in the time card object which we must derive
2617 -- from the database.
2618
2619 IF ( l_db_pre_period_start IS NOT NULL AND l_db_post_period_start IS NOT NULL )
2620 THEN
2621 if g_debug then
2622 hr_utility.set_location('Processing '||l_proc, 20);
2623 end if;
2624
2625 -- now lets get the total hours worked based on the pre TC window
2626
2627 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2628 FETCH csr_get_total_hrs INTO l_hrs;
2629 CLOSE csr_get_total_hrs;
2630
2631 if g_debug then
2632 hr_utility.trace('');
2633 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2634 hr_utility.trace('');
2635 end if;
2636
2637 l_total_hrs := l_hrs;
2638
2639 l_hrs := 0;
2640
2641 -- now lets get the total hours worked based on the post TC window
2642
2643 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2644 FETCH csr_get_total_hrs INTO l_hrs;
2645 CLOSE csr_get_total_hrs;
2646
2647 if g_debug then
2648 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2649 hr_utility.trace('');
2650 end if;
2651
2652 l_total_hrs := l_total_hrs + l_hrs + p_timecard_hrs;
2653
2654 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
2655 THEN
2656
2657 -- calculate PTO INC hours
2658
2659 l_old_tc_id := hxc_time_category_utils_pkg.g_time_category_id;
2660 hxc_time_category_utils_pkg.g_time_category_id
2661 := hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id;
2662
2663 l_hrs := 0;
2664
2665 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2666 FETCH csr_get_total_hrs INTO l_hrs;
2667 CLOSE csr_get_total_hrs;
2668
2669 if g_debug then
2670 hr_utility.trace('');
2671 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2672 hr_utility.trace('');
2673 end if;
2674
2675 l_total_hrs := l_total_hrs - l_hrs;
2676
2677 -- now lets get the total hours worked based on the post TC window
2678
2679 l_hrs := 0;
2680
2681 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2682 FETCH csr_get_total_hrs INTO l_hrs;
2683 CLOSE csr_get_total_hrs;
2684
2685 if g_debug then
2686 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2687 hr_utility.trace('');
2688 end if;
2689
2690 l_total_hrs := l_total_hrs - l_hrs;
2691
2692 hxc_time_category_utils_pkg.g_time_category_id := l_old_tc_id;
2693
2694 END IF;
2695
2696 ELSIF ( l_db_pre_period_start IS NOT NULL )
2697 THEN
2698 if g_debug then
2699 hr_utility.set_location('Processing '||l_proc, 30);
2700 end if;
2701
2702 -- now lets get the total hours worked based on the pre TC window
2703
2704 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2705 FETCH csr_get_total_hrs INTO l_hrs;
2706 CLOSE csr_get_total_hrs;
2707
2708 if g_debug then
2709 hr_utility.trace('');
2710 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2711 hr_utility.trace('');
2712 end if;
2713
2714 l_total_hrs := l_total_hrs + l_hrs + p_timecard_hrs;
2715
2716 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
2717 THEN
2718
2719 -- calculate PTO INC hours
2720
2721 l_old_tc_id := hxc_time_category_utils_pkg.g_time_category_id;
2722 hxc_time_category_utils_pkg.g_time_category_id
2723 := hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id;
2724
2725 l_hrs := 0;
2726
2727 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2728 FETCH csr_get_total_hrs INTO l_hrs;
2729 CLOSE csr_get_total_hrs;
2730
2731 if g_debug then
2732 hr_utility.trace('');
2733 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2734 hr_utility.trace('');
2735 end if;
2736
2737 l_total_hrs := l_total_hrs - l_hrs;
2738
2739 hxc_time_category_utils_pkg.g_time_category_id := l_old_tc_id;
2740
2741 END IF;
2742
2743 ELSIF( l_db_post_period_start IS NOT NULL )
2744 THEN
2745 if g_debug then
2746 hr_utility.set_location('Processing '||l_proc, 40);
2747 end if;
2748
2749 -- now lets get the total hours worked based on the post TC window
2750
2751 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2752 FETCH csr_get_total_hrs INTO l_hrs;
2753 CLOSE csr_get_total_hrs;
2754
2755 if g_debug then
2756 hr_utility.trace('');
2757 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2758 hr_utility.trace('');
2759 end if;
2760
2761 l_total_hrs := l_hrs + p_timecard_hrs;
2762
2763 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
2764 THEN
2765
2766 -- calculate PTO INC hours
2767
2768 l_old_tc_id := hxc_time_category_utils_pkg.g_time_category_id;
2769 hxc_time_category_utils_pkg.g_time_category_id
2770 := hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id;
2771
2772 l_hrs := 0;
2773
2774 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2775 FETCH csr_get_total_hrs INTO l_hrs;
2776 CLOSE csr_get_total_hrs;
2777
2778 if g_debug then
2779 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2780 hr_utility.trace('');
2781 end if;
2782
2783 l_total_hrs := l_total_hrs - l_hrs;
2784
2785 hxc_time_category_utils_pkg.g_time_category_id := l_old_tc_id;
2786
2787 END IF;
2788
2789 ELSE
2790 if g_debug then
2791 hr_utility.set_location('Processing '||l_proc, 50);
2792 end if;
2793
2794 -- i.e. the whole time entry rule period is
2795 -- encompassed by the TCO
2796
2797 l_total_hrs := p_timecard_hrs;
2798
2799 END IF;
2800 if g_debug then
2801 hr_utility.set_location('Processing '||l_proc, 60);
2802 end if;
2803
2804 IF ( l_db_ref_period_start IS NOT NULL )
2805 THEN
2806
2807 -- now get the number of hours in the reference period
2808
2809 l_number_of_periods := ROUND( p_reference_period / p_duration_in_days, 2);
2810
2811 OPEN csr_get_total_hrs ( l_db_ref_period_start, l_db_ref_period_end );
2812 FETCH csr_get_total_hrs INTO l_ref_period_hrs;
2813 CLOSE csr_get_total_hrs;
2814
2815 l_total_hrs := (( l_total_hrs + l_ref_period_hrs ) / l_number_of_periods );
2816
2817 if g_debug then
2818 hr_utility.trace('');
2819 hr_utility.trace('************* reference period info ****************');
2820 hr_utility.trace('reference period start is '||TO_CHAR(l_db_ref_period_start, 'DD-MON-YY HH24:MI:SS'));
2821 hr_utility.trace('reference period end is '||TO_CHAR(l_db_ref_period_end , 'DD-MON-YY HH24:MI:SS'));
2822 hr_utility.trace('number of periods is '||TO_CHAR(l_number_of_periods));
2823 hr_utility.trace('ref period hours are '||to_char(l_ref_period_hrs));
2824 end if;
2825
2826 END IF;
2827
2828 if g_debug then
2829 hr_utility.trace('period maximum is '||to_char(p_period_maximum));
2830 hr_utility.trace('total hours are '||to_char(l_total_hrs));
2831 hr_utility.trace('');
2832 end if;
2833
2834 if g_debug then
2835 hr_utility.set_location('Processing '||l_proc, 70);
2836 end if;
2837
2838 -- WWB 3738796
2839 -- check to see if period max is being called from a TER which is using the seeded PTO formula
2840 -- In this case if the l_total_hrs are 0 then we should always return success since
2841 -- there are no hours which will be deducted from the accrual balance
2842
2843 IF ( ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_formula_name = 'HXC_PTO_ACCRUAL_COMPARISON' )
2844 AND
2845 ( l_total_hrs = 0 ) )
2846 THEN
2847
2848 l_return := 1;
2849 RETURN l_return;
2850
2851 ELSE
2852
2853 IF ( p_operator IS NULL OR p_operator = '<' )
2854 THEN
2855
2856 IF ( p_period_maximum < l_total_hrs )
2857 THEN
2858 l_return := -1;
2859 RETURN l_return;
2860 ELSE
2861 l_return := 1;
2862 RETURN l_return;
2863 END IF;
2864
2865 ELSIF ( p_operator = '<=' )
2866 THEN
2867
2868 IF ( p_period_maximum <= l_total_hrs )
2869 THEN
2870 l_return := -1;
2871 RETURN l_return;
2872 ELSE
2873 l_return := 1;
2874 RETURN l_return;
2875 END IF;
2876
2877
2878 ELSIF ( p_operator = '<>' )
2879 THEN
2880
2881 IF ( p_period_maximum <> l_total_hrs )
2882 THEN
2883 l_return := -1;
2884 RETURN l_return;
2885 ELSE
2886 l_return := 1;
2887 RETURN l_return;
2888 END IF;
2889
2890
2891 ELSIF ( p_operator = '=' )
2892 THEN
2893
2894 IF ( p_period_maximum = l_total_hrs )
2895 THEN
2896 l_return := -1;
2897 RETURN l_return;
2898 ELSE
2899 l_return := 1;
2900 RETURN l_return;
2901 END IF;
2902
2903
2904 ELSIF ( p_operator = '>' )
2905 THEN
2906
2907 IF ( p_period_maximum > l_total_hrs )
2908 THEN
2909 l_return := -1;
2910 RETURN l_return;
2911 ELSE
2912 l_return := 1;
2913 RETURN l_return;
2914 END IF;
2915
2916
2917 ELSIF ( p_operator = '>=' )
2918 THEN
2919
2920 IF ( p_period_maximum >= l_total_hrs )
2921 THEN
2922 l_return := -1;
2923 RETURN l_return;
2924 ELSE
2925 l_return := 1;
2926 RETURN l_return;
2927 END IF;
2928
2929
2930 END IF; -- p_operator
2931
2932 END IF; -- g_ter_record.ter_formula_name = 'HXC_PTO_ACCRUAL_COMPARISON'
2933
2934 if g_debug then
2935 hr_utility.set_location('Processing '||l_proc, 70);
2936 end if;
2937
2938 END period_maximum;
2939
2940 FUNCTION asg_status_id ( p_assignment_id NUMBER
2941 , p_effective_date VARCHAR2 ) RETURN NUMBER IS
2942
2943 l_proc varchar2(72) := g_package||'.asg_status_id';
2944 l_asg_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
2945
2946 CURSOR csr_get_asg_status_id IS
2947 SELECT a.assignment_status_type_id
2948 FROM per_assignment_status_types a
2949 , per_assignments_f asg
2950 WHERE
2951 asg.assignment_id = p_assignment_id AND
2952 TO_DATE(p_effective_date, 'YYYY/MM/DD HH24:MI:SS')
2953 BETWEEN asg.effective_start_date AND asg.effective_end_date
2954 AND
2955 asg.assignment_status_type_id = a.assignment_status_type_id;
2956
2957 BEGIN
2958
2959 OPEN csr_get_asg_status_id;
2960 FETCH csr_get_asg_status_id INTO l_asg_status_id;
2961
2962 IF csr_get_asg_status_id%NOTFOUND
2963 THEN
2964
2965 CLOSE csr_get_asg_status_id;
2966
2967 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2968 fnd_message.set_token('PROCEDURE', l_proc);
2969 fnd_message.set_token('STEP','assignment status');
2970 fnd_message.raise_error;
2971
2972 END IF;
2973
2974 CLOSE csr_get_asg_status_id;
2975
2976 RETURN l_asg_status_id;
2977
2978 END asg_status_id;
2979
2980 PROCEDURE tc_edit_allowed (
2981 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
2982 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
2983 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
2984 ,p_edit_allowed IN OUT nocopy VARCHAR2
2985 ) IS
2986
2987 begin
2988
2989 tc_edit_allowed
2990 (p_timecard_id => p_timecard_id,
2991 p_timecard_ovn => p_timecard_ovn,
2992 p_timecard_status => null,
2993 p_edit_allowed_preference=> p_edit_allowed_preference,
2994 p_edit_allowed => p_edit_allowed
2995 );
2996
2997 end tc_edit_allowed;
2998
2999 PROCEDURE tc_edit_allowed (
3000 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
3001 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
3002 ,p_timecard_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE
3003 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
3004 ,p_edit_allowed IN OUT nocopy VARCHAR2
3005 ) is
3006
3007 CURSOR csr_chk_transfer IS
3008 SELECT 1
3009 FROM dual
3010 WHERE EXISTS (
3011 SELECT 1
3012 FROM hxc_transactions t
3013 , hxc_transaction_details td
3014 WHERE td.time_building_block_id = p_timecard_id
3015 AND
3016 t.transaction_id = td.transaction_id AND
3017 t.type = 'RETRIEVAL' AND
3018 t.status = 'SUCCESS' );
3019
3020 l_proc VARCHAR2(72);
3021
3022 l_tc_status hxc_time_building_blocks.approval_status%TYPE;
3023
3024 l_dummy NUMBER(1);
3025
3026 BEGIN
3027
3028 g_debug := hr_utility.debug_enabled;
3029
3030 if g_debug then
3031 l_proc := g_package||'tc_edit_allowed';
3032 hr_utility.set_location('Entering '||l_proc, 10);
3033 end if;
3034
3035 -- GPM v115.25
3036 -- WWB - 2109325
3037 -- ARR v115.99.11512.7, only fetch approval status if not already known.
3038
3039 if(p_timecard_status is null) then
3040 l_tc_status := hxc_timecard_search_pkg.get_timecard_status_code(p_timecard_id,p_Timecard_Ovn);
3041 else
3042 l_tc_status := p_timecard_status;
3043 end if;
3044
3045 if g_debug then
3046 hr_utility.set_location('Processing '||l_proc, 20);
3047 end if;
3048
3049
3050 IF ( p_edit_allowed_preference = 'NEW_WORKING_REJECTED' )
3051 THEN
3052 if g_debug then
3053 hr_utility.set_location('Processing '||l_proc, 30);
3054 end if;
3055
3056 IF ( ( l_tc_status = 'REJECTED' ) OR ( l_tc_status = 'WORKING' ) )
3057 THEN
3058 p_edit_allowed := 'TRUE';
3059 ELSE
3060 p_edit_allowed := 'FALSE';
3061 END IF;
3062
3063 ELSIF ( p_edit_allowed_preference = 'SUBMITTED' )
3064 THEN
3065 if g_debug then
3066 hr_utility.set_location('Processing '||l_proc, 40);
3067 end if;
3068
3069 IF ( ( l_tc_status = 'REJECTED' ) OR ( l_tc_status = 'WORKING' ) OR ( l_tc_status = 'SUBMITTED' ) )
3070 THEN
3071 p_edit_allowed := 'TRUE';
3072 ELSE
3073 p_edit_allowed := 'FALSE';
3074 END IF;
3075
3076 ELSIF ( p_edit_allowed_preference = 'APPROVALS_INITIATED' )
3077 THEN
3078 if g_debug then
3079 hr_utility.set_location('Processing '||l_proc, 50);
3080 end if;
3081
3082 -- all we need to do here is check that this timecard
3083 -- has not been transferred successfully to any recipient
3084 -- applications
3085
3086 OPEN csr_chk_transfer;
3087 FETCH csr_chk_transfer INTO l_dummy;
3088
3089 IF csr_chk_transfer%FOUND
3090 THEN
3091 p_edit_allowed := 'FALSE';
3092 ELSE
3093 p_edit_allowed := 'TRUE';
3094 END IF;
3095
3096 ELSIF ( p_edit_allowed_preference = 'RETRO' )
3097 THEN
3098 if g_debug then
3099 hr_utility.set_location('Processing '||l_proc, 60);
3100 end if;
3101
3102 IF ( ( l_tc_status = 'REJECTED' ) OR ( l_tc_status = 'WORKING' ) OR ( l_tc_status = 'SUBMITTED' )
3103 OR ( l_tc_status = 'APPROVED' ) OR ( l_tc_status = 'ERROR' ) )
3104 THEN
3105 p_edit_allowed := 'TRUE';
3106 ELSE
3107 p_edit_allowed := 'FALSE';
3108 END IF;
3109
3110 ELSE
3111 if g_debug then
3112 hr_utility.set_location('Processing '||l_proc, 70);
3113 end if;
3114
3115 p_edit_allowed := 'FALSE';
3116
3117 END IF;
3118
3119 -- if the status is ERROR, we don't need to look at
3120 -- the pref -> JUST RETURN TRUE;
3121 IF (l_tc_status = 'ERROR') THEN
3122 p_edit_allowed := 'TRUE';
3123 END IF;
3124
3125
3126 if g_debug then
3127 hr_utility.set_location('Leaving '||l_proc, 80);
3128 end if;
3129
3130 END tc_edit_allowed;
3131
3132
3133
3134
3135
3136
3137
3138 -- Public Function
3139 --
3140 -- Test whether the assignment is enrolled in
3141 -- the specified accrual plan as of the
3142 -- effective date
3143 --
3144 -- Usage
3145 -- Called from the PTO BAl comparison formula
3146 --
3147
3148 FUNCTION chk_pto_plan ( p_assignment_id NUMBER
3149 , p_accrual_plan_id NUMBER
3150 , p_effective_date VARCHAR2 )
3151 RETURN NUMBER IS
3152
3153 l_pto_ok pay_accrual_plans.accrual_plan_id%TYPE := -1;
3154
3155 CURSOR csr_chk_pto_ok IS
3156 SELECT pap.accrual_plan_id
3157 FROM
3158 pay_accrual_plans pap
3159 ,pay_element_types_f pet
3160 ,pay_element_links_f pel
3161 ,pay_element_entries_f pee
3162 WHERE
3163 pap.accrual_plan_id = p_accrual_plan_id AND
3164 pap.accrual_plan_element_type_id = pet.element_type_id
3165 AND
3166 pet.element_type_id = pel.element_type_id AND
3167 pee.effective_start_date BETWEEN
3168 pet.effective_start_date AND pet.effective_end_date
3169 AND
3170 pel.element_link_id = pee.element_link_id AND
3171 pee.effective_start_date BETWEEN
3172 pel.effective_start_date AND pel.effective_end_date
3173 AND
3174 pee.assignment_id = p_assignment_id AND
3175 to_date(p_effective_date, 'YYYY/MM/DD HH24:MI:SS')
3176 BETWEEN pee.effective_start_date AND pee.effective_end_date;
3177
3178
3179 BEGIN
3180
3181 g_debug := hr_utility.debug_enabled;
3182
3183 OPEN csr_chk_pto_ok;
3184 FETCH csr_chk_pto_ok INTO l_pto_ok;
3185
3186 IF csr_chk_pto_ok%FOUND
3187 THEN
3188
3189 if g_debug then
3190 hr_utility.trace('PTO PLAN OK');
3191 end if;
3192 l_pto_ok := 1;
3193
3194 ELSE
3195
3196 if g_debug then
3197 hr_utility.trace('PTO PLAN NOT OK');
3198 end if;
3199
3200
3201 END IF;
3202
3203 CLOSE csr_chk_pto_ok;
3204
3205 RETURN l_pto_ok;
3206
3207 END chk_pto_plan;
3208
3209
3210 PROCEDURE EXECUTE_ELP_TIME_ENTRY_RULES( P_TIME_BUILDING_BLOCKS HXC_BLOCK_TABLE_TYPE
3211 ,P_TIME_ATTRIBUTES HXC_ATTRIBUTE_TABLE_TYPE
3212 ,P_MESSAGES in out NOCOPY hxc_self_service_time_deposit.MESSAGE_TABLE
3213 ,P_TIME_ENTRY_RULE_GROUP_ID NUMBER) IS
3214 n number;
3215 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
3216 l_terg_id hxc_pref_hierarchies.attribute1%TYPE;
3217 l_time_category_id hxc_time_categories.time_category_id%TYPE;
3218
3219 l_prefs hxc_preference_evaluation.t_pref_table;
3220
3221 Begin
3222 g_debug := hr_utility.debug_enabled;
3223
3224 get_timecard_info (
3225 p_time_building_blocks => p_time_building_blocks
3226 , p_timecard_rec => l_timecard_info_rec );
3227 if g_debug then
3228 hr_utility.trace('After get_timecard_info');
3229 hr_utility.trace('start_date ' || l_timecard_info_rec.start_date);
3230 end if;
3231 -- Start 2944785
3232 /*
3233 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
3234 p_preference_code => 'TS_PER_ELP_RULES',
3235 p_start_evaluation_date => l_timecard_info_rec.start_date,
3236 p_end_evaluation_date => l_timecard_info_rec.end_date,
3237 p_sorted_pref_table => l_prefs );
3238
3239 IF ( l_prefs.COUNT > 1 )
3240 THEN
3241
3242 -- error since cannot have more than one ELP preference in timecard period
3243
3244 -- in the interim take the first available value
3245
3246 l_terg_id := l_prefs(1).attribute1;
3247
3248 ELSE
3249
3250 l_terg_id := l_prefs(1).attribute1;
3251
3252 END IF;
3253 -- GPM v115.55
3254 */
3255 l_terg_id := P_TIME_ENTRY_RULE_GROUP_ID;
3256
3257 -- End 2944785
3258
3259 FOR r_rules IN csr_get_rules ( p_terg_id => l_terg_id
3260 , p_start_date => l_timecard_info_rec.start_date
3261 , p_end_date => l_timecard_info_rec.end_date )
3262 LOOP
3263 if g_debug then
3264 hr_utility.trace('r_rules.Timecategory_name' || r_rules.attribute2);
3265 hr_utility.trace('r_rules.Recipient Application' || r_rules.attribute1);
3266 end if;
3267 l_time_category_id := r_rules.attribute2;
3268 if g_debug then
3269 hr_utility.trace('Time Category ID' || l_time_category_id);
3270 end if;
3271 hxc_time_category_utils_pkg.initialise_time_category(
3272 p_time_category_id => to_number(l_time_category_id),
3273 p_tco_att => P_TIME_ATTRIBUTES);
3274 n := p_time_building_blocks.first;
3275 loop
3276 exit when not p_time_building_blocks.exists(n);
3277 if (p_time_building_blocks(n).scope = 'DETAIL') then
3278 if (hxc_time_category_utils_pkg.chk_tc_bb_ok( p_time_building_blocks(n).time_building_block_id)) then
3279 add_error_to_table (
3280 p_message_table => p_messages
3281 , p_message_name => r_rules.attribute1
3282 , p_message_token => NULL
3283 , p_message_level => 'PTE'
3284 , p_message_field => NULL
3285 , p_timecard_bb_id => p_time_building_blocks(n).time_building_block_id
3286 , p_time_attribute_id => NULL
3287 , p_timecard_bb_ovn => p_time_building_blocks(n).object_version_number
3288 , p_time_attribute_ovn => NULL );
3289 end if;
3290 end if;
3291 n := p_time_building_blocks.next(n);
3292 end loop;
3293 end loop;
3294
3295 if g_debug then
3296 hr_utility.trace('Message Table');
3297 end if;
3298 n := p_messages.first;
3299 loop
3300 exit when not p_messages.exists(n);
3301 if (p_messages(n).message_level = 'PTE') then
3302 if g_debug then
3303 hr_utility.trace('Time Building Block Id' || p_messages(n).time_building_block_id);
3304 hr_utility.trace('Time Building Block OVN' || p_messages(n).time_building_block_ovn);
3305 hr_utility.trace('Recipient Application ID' || p_messages(n).message_name);
3306 end if;
3307 end if;
3308 n := p_messages.next(n);
3309 end loop;
3310
3311 if g_debug then
3312 hr_utility.trace('End of execute ELP time entry rules');
3313 end if;
3314
3315 end execute_ELP_time_entry_rules;
3316
3317 PROCEDURE EXECUTE_CLA_TIME_ENTRY_RULES( P_TIME_BUILDING_BLOCKS hxc_self_service_time_deposit.timecard_info
3318 ,P_TIME_ATTRIBUTES hxc_self_service_time_deposit.building_block_attribute_info
3319 ,P_MESSAGES in out NOCOPY hxc_self_service_time_deposit.MESSAGE_TABLE
3320 ,P_TIME_ENTRY_RULE_GROUP_ID NUMBER) IS
3321 n number;
3322 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
3323 l_terg_id hxc_pref_hierarchies.attribute1%TYPE;
3324 l_time_category_id hxc_time_categories.time_category_id%TYPE;
3325
3326 l_prefs hxc_preference_evaluation.t_pref_table;
3327
3328 FUNCTION chk_bb_late (
3329 p_stop_time DATE,
3330 p_st_late_hrs NUMBER,
3331 p_qnt_late_hrs NUMBER,
3332 p_date_worked DATE
3333 )
3334 RETURN BOOLEAN
3335 IS
3336 l_late_measure NUMBER;
3337 l_client_tz fnd_timezones_b.timezone_code%type;
3338 l_server_tz fnd_timezones_b.timezone_code%type;
3339 l_client_time DATE;
3340 BEGIN
3341 g_debug := hr_utility.debug_enabled;
3342
3343 if g_debug then
3344 hr_utility.TRACE ( 'Stop Time '
3345 || p_stop_time);
3346 hr_utility.TRACE ( 'p_st_late_hrs '
3347 || p_st_late_hrs);
3348 hr_utility.TRACE ( 'p_qnt_late_hrs '
3349 || p_qnt_late_hrs);
3350 end if;
3351 --Fix for Bug No:4948883
3352 fnd_date.timezones_enabled := true;
3353 l_client_tz := fnd_timezones.get_client_timezone_code;
3354 l_server_tz := fnd_timezones.get_server_timezone_code;
3355 l_client_time := fnd_date.adjust_datetime(sysdate,l_server_tz,l_client_tz);
3356
3357 IF (p_stop_time IS NOT NULL)
3358 THEN
3359 l_late_measure := ( l_client_time - p_stop_time
3360 ) * 24;
3361 if g_debug then
3362 hr_utility.TRACE ( 'l_late_measure '
3363 || l_late_measure);
3364 end if;
3365
3366 IF (l_late_measure > p_st_late_hrs)
3367 THEN
3368 RETURN TRUE;
3369 END IF;
3370 ELSE
3371 if g_debug then
3372 hr_utility.trace('L_date_worked...............' || to_char(p_date_worked,'dd-mon-rrrr hh:mi:ss'));
3373 end if;
3374 IF (( TRUNC (p_date_worked)
3375 + (p_qnt_late_hrs / 24)
3376 ) < l_client_time
3377 )
3378 THEN
3379 if g_debug then
3380 hr_utility.TRACE ('Late.....');
3381 end if;
3382 RETURN TRUE;
3383 END IF;
3384 END IF;
3385
3386 RETURN FALSE;
3387 END chk_bb_late;
3388
3389 PROCEDURE populate_old_tco (
3390 p_timecard_rec IN hxc_time_entry_rules_utils_pkg.r_timecard_info,
3391 p_timecard_building_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
3392 p_timecard_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
3393 )
3394 IS
3395 CURSOR csr_get_det_bbs
3396 IS
3397 SELECT detail.time_building_block_id, detail.TYPE, detail.measure,
3398 detail.unit_of_measure, detail.start_time,
3399 detail.stop_time, detail.parent_building_block_id,
3400 'N' parent_is_new, detail.SCOPE,
3401 detail.object_version_number, detail.approval_status,
3402 detail.resource_id, detail.resource_type,
3403 detail.approval_style_id, detail.date_from, detail.date_to,
3404 detail.comment_text, detail.parent_building_block_ovn,
3405 'N' NEW, 'N' changed
3406 FROM hxc_time_building_blocks detail,
3407 hxc_time_building_blocks DAY
3408 WHERE DAY.parent_building_block_id =
3409 p_timecard_rec.timecard_bb_id
3410 AND DAY.parent_building_block_ovn =
3411 p_timecard_rec.timecard_ovn
3412 AND detail.date_to = hr_general.end_of_time
3413 AND detail.SCOPE = 'DETAIL'
3414 AND detail.parent_building_block_id =
3415 DAY.time_building_block_id
3416 AND detail.parent_building_block_ovn =
3417 DAY.object_version_number
3418 AND DAY.SCOPE = 'DAY'
3419 AND DAY.date_to = hr_general.end_of_time;
3420
3421 -- Bug 8920374
3422 -- Changed the below query to resolve the perf issue.
3423 /*
3424 CURSOR csr_get_det_attr (p_building_block_id NUMBER, p_bb_ovn NUMBER)
3425 IS
3426 SELECT a.time_attribute_id, au.time_building_block_id,
3427 bbit.bld_blk_info_type, a.attribute_category, a.attribute1,
3428 a.attribute2, a.attribute3, a.attribute4, a.attribute5,
3429 a.attribute6, a.attribute7, a.attribute8, a.attribute9,
3430 a.attribute10, a.attribute11, a.attribute12, a.attribute13,
3431 a.attribute14, a.attribute15, a.attribute16, a.attribute17,
3432 a.attribute18, a.attribute19, a.attribute20, a.attribute21,
3433 a.attribute22, a.attribute23, a.attribute24, a.attribute25,
3434 a.attribute26, a.attribute27, a.attribute28, a.attribute29,
3435 a.attribute30, a.bld_blk_info_type_id,
3436 a.object_version_number, 'N' NEW, 'N' changed
3437 FROM hxc_time_attributes a,
3438 hxc_time_attribute_usages au,
3439 hxc_bld_blk_info_types bbit
3440 WHERE au.time_building_block_id = p_building_block_id
3441 AND au.time_building_block_ovn = p_bb_ovn
3442 AND au.time_attribute_id = a.time_attribute_id
3443 AND NOT (a.attribute_category = 'TEMPLATES')
3444 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
3445 AND a.object_version_number =
3446 (SELECT MAX (object_version_number)
3447 FROM hxc_time_attributes
3448 WHERE time_attribute_id = a.time_attribute_id);
3449 */
3450
3451 CURSOR csr_get_det_attr (p_building_block_id NUMBER, p_bb_ovn NUMBER)
3452 IS
3453 SELECT /*+ LEADING(AU)
3454 INDEX(AU HXC_TIME_ATTRIBUTE_USAGES_FK2)
3455 INDEX(A HXC_TIME_ATTRIBUTES_PK)
3456 INDEX(BBIT HXC_BUILD_BLK_INFO_TYPES_PK) */
3457 a.time_attribute_id, au.time_building_block_id,
3458 bbit.bld_blk_info_type, a.attribute_category, a.attribute1,
3459 a.attribute2, a.attribute3, a.attribute4, a.attribute5,
3460 a.attribute6, a.attribute7, a.attribute8, a.attribute9,
3461 a.attribute10, a.attribute11, a.attribute12, a.attribute13,
3462 a.attribute14, a.attribute15, a.attribute16, a.attribute17,
3463 a.attribute18, a.attribute19, a.attribute20, a.attribute21,
3464 a.attribute22, a.attribute23, a.attribute24, a.attribute25,
3465 a.attribute26, a.attribute27, a.attribute28, a.attribute29,
3466 a.attribute30, a.bld_blk_info_type_id,
3467 a.object_version_number, 'N' NEW, 'N' changed
3468 FROM hxc_time_attributes a,
3469 hxc_time_attribute_usages au,
3470 hxc_bld_blk_info_types bbit
3471 WHERE au.time_building_block_id = p_building_block_id
3472 AND au.time_building_block_ovn = p_bb_ovn
3473 AND au.time_attribute_id = a.time_attribute_id
3474 AND NOT (a.attribute_category = 'TEMPLATES')
3475 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id ;
3476
3477 l_tbb_index NUMBER;
3478 l_att_index NUMBER;
3479 r_det_rec csr_get_det_bbs%ROWTYPE;
3480 BEGIN
3481
3482
3483 if g_debug then
3484 hr_utility.TRACE ('Start of populate old tco');
3485 end if;
3486 p_timecard_building_blocks.DELETE;
3487 p_timecard_attributes.DELETE;
3488 l_tbb_index := 0;
3489 l_att_index := 0;
3490
3491 FOR r_det_rec IN csr_get_det_bbs
3492 LOOP
3493 if g_debug then
3494 hr_utility.TRACE (
3495 'r_det_rec.time_building_block_id '
3496 || r_det_rec.time_building_block_id
3497 );
3498 hr_utility.TRACE (
3499 'r_det_rec.object_version_number '
3500 || r_det_rec.object_version_number
3501 );
3502 end if;
3503 p_timecard_building_blocks (l_tbb_index).time_building_block_id :=
3504 r_det_rec.time_building_block_id;
3505 p_timecard_building_blocks (l_tbb_index).TYPE := r_det_rec.TYPE;
3506 p_timecard_building_blocks (l_tbb_index).measure :=
3507 r_det_rec.measure;
3508 p_timecard_building_blocks (l_tbb_index).unit_of_measure :=
3509 r_det_rec.unit_of_measure;
3510 p_timecard_building_blocks (l_tbb_index).start_time :=
3511 r_det_rec.start_time;
3512 p_timecard_building_blocks (l_tbb_index).stop_time :=
3513 r_det_rec.stop_time;
3514 p_timecard_building_blocks (l_tbb_index).parent_building_block_id :=
3515 r_det_rec.parent_building_block_id;
3516 p_timecard_building_blocks (l_tbb_index).parent_is_new :=
3517 r_det_rec.parent_is_new;
3518 p_timecard_building_blocks (l_tbb_index).SCOPE := r_det_rec.SCOPE;
3519 p_timecard_building_blocks (l_tbb_index).object_version_number :=
3520 r_det_rec.object_version_number;
3521 p_timecard_building_blocks (l_tbb_index).approval_status :=
3522 r_det_rec.approval_status;
3523 p_timecard_building_blocks (l_tbb_index).resource_id :=
3524 r_det_rec.resource_id;
3525 p_timecard_building_blocks (l_tbb_index).resource_type :=
3526 r_det_rec.resource_type;
3527 p_timecard_building_blocks (l_tbb_index).approval_style_id :=
3528 r_det_rec.approval_style_id;
3529 p_timecard_building_blocks (l_tbb_index).date_from :=
3530 r_det_rec.date_from;
3531 p_timecard_building_blocks (l_tbb_index).date_to :=
3532 r_det_rec.date_to;
3533 p_timecard_building_blocks (l_tbb_index).comment_text :=
3534 r_det_rec.comment_text;
3535 p_timecard_building_blocks (l_tbb_index).parent_building_block_ovn :=
3536 r_det_rec.parent_building_block_ovn;
3537 p_timecard_building_blocks (l_tbb_index).NEW := r_det_rec.NEW;
3538 p_timecard_building_blocks (l_tbb_index).changed :=
3539 r_det_rec.changed;
3540 l_tbb_index := l_tbb_index
3541 + 1;
3542
3543 FOR r_der_attr IN
3544 csr_get_det_attr (
3545 r_det_rec.time_building_block_id,
3546 r_det_rec.object_version_number
3547 )
3548 LOOP
3549 if g_debug then
3550 hr_utility.TRACE (
3551 'Attribute Id'
3552 || r_der_attr.time_attribute_id
3553 );
3554 hr_utility.TRACE (
3555 'Attribute Category '
3556 || r_der_attr.attribute_category
3557 );
3558 end if;
3559 p_timecard_attributes (l_att_index).time_attribute_id :=
3560 r_der_attr.time_attribute_id;
3561 p_timecard_attributes (l_att_index).building_block_id :=
3562 r_der_attr.time_building_block_id;
3563 p_timecard_attributes (l_att_index).bld_blk_info_type :=
3564 r_der_attr.bld_blk_info_type;
3565 p_timecard_attributes (l_att_index).attribute_category :=
3566 r_der_attr.attribute_category;
3567 p_timecard_attributes (l_att_index).attribute1 :=
3568 r_der_attr.attribute1;
3569 p_timecard_attributes (l_att_index).attribute2 :=
3570 r_der_attr.attribute2;
3571 p_timecard_attributes (l_att_index).attribute3 :=
3572 r_der_attr.attribute3;
3573 p_timecard_attributes (l_att_index).attribute4 :=
3574 r_der_attr.attribute4;
3575 p_timecard_attributes (l_att_index).attribute5 :=
3576 r_der_attr.attribute5;
3577 p_timecard_attributes (l_att_index).attribute6 :=
3578 r_der_attr.attribute6;
3579 p_timecard_attributes (l_att_index).attribute7 :=
3580 r_der_attr.attribute7;
3581 p_timecard_attributes (l_att_index).attribute8 :=
3582 r_der_attr.attribute8;
3583 p_timecard_attributes (l_att_index).attribute9 :=
3584 r_der_attr.attribute9;
3585 p_timecard_attributes (l_att_index).attribute10 :=
3586 r_der_attr.attribute10;
3587 p_timecard_attributes (l_att_index).attribute11 :=
3588 r_der_attr.attribute11;
3589 p_timecard_attributes (l_att_index).attribute12 :=
3590 r_der_attr.attribute12;
3591 p_timecard_attributes (l_att_index).attribute13 :=
3592 r_der_attr.attribute13;
3593 p_timecard_attributes (l_att_index).attribute14 :=
3594 r_der_attr.attribute14;
3595 p_timecard_attributes (l_att_index).attribute15 :=
3596 r_der_attr.attribute15;
3597 p_timecard_attributes (l_att_index).attribute16 :=
3598 r_der_attr.attribute16;
3599 p_timecard_attributes (l_att_index).attribute17 :=
3600 r_der_attr.attribute17;
3601 p_timecard_attributes (l_att_index).attribute18 :=
3602 r_der_attr.attribute18;
3603 p_timecard_attributes (l_att_index).attribute19 :=
3604 r_der_attr.attribute19;
3605 p_timecard_attributes (l_att_index).attribute20 :=
3606 r_der_attr.attribute20;
3607 p_timecard_attributes (l_att_index).attribute21 :=
3608 r_der_attr.attribute21;
3609 p_timecard_attributes (l_att_index).attribute22 :=
3610 r_der_attr.attribute22;
3611 p_timecard_attributes (l_att_index).attribute23 :=
3612 r_der_attr.attribute23;
3613 p_timecard_attributes (l_att_index).attribute24 :=
3614 r_der_attr.attribute24;
3615 p_timecard_attributes (l_att_index).attribute25 :=
3616 r_der_attr.attribute25;
3617 p_timecard_attributes (l_att_index).attribute26 :=
3618 r_der_attr.attribute26;
3619 p_timecard_attributes (l_att_index).attribute27 :=
3620 r_der_attr.attribute27;
3621 p_timecard_attributes (l_att_index).attribute28 :=
3622 r_der_attr.attribute28;
3623 p_timecard_attributes (l_att_index).attribute29 :=
3624 r_der_attr.attribute29;
3625 p_timecard_attributes (l_att_index).attribute30 :=
3626 r_der_attr.attribute30;
3627 p_timecard_attributes (l_att_index).bld_blk_info_type_id :=
3628 r_der_attr.bld_blk_info_type_id;
3629 p_timecard_attributes (l_att_index).object_version_number :=
3630 r_der_attr.object_version_number;
3631 p_timecard_attributes (l_att_index).NEW := r_der_attr.NEW;
3632 p_timecard_attributes (l_att_index).changed :=
3633 r_der_attr.changed;
3634 l_att_index := l_att_index
3635 + 1;
3636 END LOOP;
3637 END LOOP;
3638
3639 if g_debug then
3640 hr_utility.TRACE ('Leaving populate old tco');
3641 end if;
3642 END populate_old_tco;
3643
3644 FUNCTION compare_tbb_attributes (
3645 p_attribute1 hxc_self_service_time_deposit.attribute_info,
3646 p_attribute2 hxc_self_service_time_deposit.attribute_info,
3647 p_tbb_deleted BOOLEAN,
3648 p_change_att_tab IN OUT NOCOPY t_change_att_tab
3649 )
3650 RETURN VARCHAR2
3651 IS
3652 CURSOR csr_get_mapping_name (
3653 p_attribute VARCHAR2,
3654 p_attribute_id NUMBER,
3655 p_att_ovn NUMBER
3656 )
3657 IS
3658 SELECT hmc.field_name
3659 FROM hxc_mapping_components hmc, hxc_time_attributes hta
3660 WHERE hta.time_attribute_id = p_attribute_id
3661 AND hta.object_version_number = p_att_ovn
3662 AND hta.bld_blk_info_type_id = hmc.bld_blk_info_type_id
3663 AND hmc.SEGMENT = UPPER (p_attribute);
3664
3665 l_category_flag BOOLEAN := FALSE;
3666 l_mapping_name VARCHAR2 (80);
3667 l_return_mapp VARCHAR2 (200);
3668 l_change_att_index NUMBER:=0;
3669
3670 BEGIN
3671
3672
3673
3674 IF p_change_att_tab.count = 0 then
3675 l_change_att_index :=1;
3676 ELSE
3677 l_change_att_index := p_change_att_tab.last+1;
3678 END IF;
3679
3680 IF (NVL (p_attribute1.attribute_category, 'NULL') <>
3681 NVL (p_attribute2.attribute_category, 'NULL')
3682 )
3683 THEN
3684 l_category_flag := TRUE;
3685 --Changes made to make use of bld_blk_info_type always. Removed the Attribute cateogory check
3686 -- for ELEMENT -%.
3687 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3688 l_return_mapp :=
3689 l_return_mapp
3690 || ':'
3691 || p_attribute1.attribute_category;
3692
3693 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE_CATEGORY';
3694 l_change_att_index := l_change_att_index+1;
3695
3696 END IF;
3697
3698
3699 IF ((NVL (p_attribute1.attribute1, 'NULL') <>
3700 NVL (p_attribute2.attribute1, 'NULL')
3701 OR p_tbb_deleted)
3702 )
3703 THEN
3704 l_mapping_name := NULL;
3705
3706 IF ( p_attribute1.attribute1 IS NOT NULL
3707 OR p_attribute2.attribute1 IS NOT NULL
3708 )
3709 THEN
3710
3711 OPEN csr_get_mapping_name (
3712 'attribute1',
3713 p_attribute1.time_attribute_id,
3714 p_attribute1.object_version_number
3715 );
3716 FETCH csr_get_mapping_name INTO l_mapping_name;
3717 CLOSE csr_get_mapping_name;
3718 END IF;
3719 --Changes made to make use of bld_blk_info_type always.
3720 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3721 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE1';
3722 l_change_att_index := l_change_att_index+1;
3723
3724 if g_debug then
3725 hr_utility.TRACE ('Att1');
3726 hr_utility.TRACE ( 'l_mapping_name '
3727 || l_mapping_name);
3728 hr_utility.TRACE ( 'l_return_mapp '
3729 || l_return_mapp);
3730 end if;
3731 l_return_mapp := l_return_mapp
3732 || ':'
3733 || l_mapping_name;
3734 END IF;
3735
3736 IF ((NVL (p_attribute1.attribute2, 'NULL') <>
3737 NVL (p_attribute2.attribute2, 'NULL')
3738 OR p_tbb_deleted)
3739 )
3740 THEN
3741 l_mapping_name := NULL;
3742
3743 IF ( p_attribute1.attribute2 IS NOT NULL
3744 OR p_attribute2.attribute2 IS NOT NULL
3745 )
3746 THEN
3747
3748 OPEN csr_get_mapping_name (
3749 'attribute2',
3750 p_attribute1.time_attribute_id,
3751 p_attribute1.object_version_number
3752 );
3753 FETCH csr_get_mapping_name INTO l_mapping_name;
3754 CLOSE csr_get_mapping_name;
3755 END IF;
3756 --Changes made to make use of bld_blk_info_type always.
3757 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3758 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE2';
3759 l_change_att_index := l_change_att_index+1;
3760
3761 if g_debug then
3762 hr_utility.TRACE ('Att2');
3763 hr_utility.TRACE ( 'l_mapping_name '
3764 || l_mapping_name);
3765 hr_utility.TRACE ( 'l_return_mapp '
3766 || l_return_mapp);
3767 end if;
3768 l_return_mapp := l_return_mapp
3769 || ':'
3770 || l_mapping_name;
3771 END IF;
3772
3773 IF ((NVL (p_attribute1.attribute3, 'NULL') <>
3774 NVL (p_attribute2.attribute3, 'NULL')
3775 OR p_tbb_deleted)
3776 )
3777 THEN
3778 l_mapping_name := NULL;
3779
3780
3781 IF ( p_attribute1.attribute3 IS NOT NULL
3782 OR p_attribute2.attribute3 IS NOT NULL
3783 )
3784 THEN
3785
3786 OPEN csr_get_mapping_name (
3787 'attribute3',
3788 p_attribute1.time_attribute_id,
3789 p_attribute1.object_version_number
3790 );
3791 FETCH csr_get_mapping_name INTO l_mapping_name;
3792 CLOSE csr_get_mapping_name;
3793 END IF;
3794 --Changes made to make use of bld_blk_info_type always.
3795 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3796 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE3';
3797 l_change_att_index := l_change_att_index+1;
3798
3799 if g_debug then
3800 hr_utility.TRACE ('Att3');
3801 hr_utility.TRACE ( 'l_mapping_name '
3802 || l_mapping_name);
3803 hr_utility.TRACE ( 'l_return_mapp '
3804 || l_return_mapp);
3805 end if;
3806 l_return_mapp := l_return_mapp
3807 || ':'
3808 || l_mapping_name;
3809 END IF;
3810
3811 IF ((NVL (p_attribute1.attribute4, 'NULL') <>
3812 NVL (p_attribute2.attribute4, 'NULL')
3813 OR p_tbb_deleted)
3814 )
3815 THEN
3816 l_mapping_name := NULL;
3817
3818 IF ( p_attribute1.attribute4 IS NOT NULL
3819 OR p_attribute2.attribute4 IS NOT NULL
3820 )
3821 THEN
3822
3823 OPEN csr_get_mapping_name (
3824 'attribute4',
3825 p_attribute1.time_attribute_id,
3826 p_attribute1.object_version_number
3827 );
3828 FETCH csr_get_mapping_name INTO l_mapping_name;
3829 CLOSE csr_get_mapping_name;
3830 END IF;
3831 --Changes made to make use of bld_blk_info_type always.
3832 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3833 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE4';
3834 l_change_att_index := l_change_att_index+1;
3835
3836 if g_debug then
3837 hr_utility.TRACE ('Att4');
3838 hr_utility.TRACE ( 'l_mapping_name '
3839 || l_mapping_name);
3840 hr_utility.TRACE ( 'l_return_mapp '
3841 || l_return_mapp);
3842 end if;
3843 l_return_mapp := l_return_mapp
3844 || ':'
3845 || l_mapping_name;
3846 END IF;
3847
3848 IF ((NVL (p_attribute1.attribute5, 'NULL') <>
3849 NVL (p_attribute2.attribute5, 'NULL')
3850 OR p_tbb_deleted)
3851 )
3852 THEN
3853 l_mapping_name := NULL;
3854
3855
3856
3857 IF ( p_attribute1.attribute5 IS NOT NULL
3858 OR p_attribute2.attribute5 IS NOT NULL
3859 )
3860 THEN
3861
3862 OPEN csr_get_mapping_name (
3863 'attribute5',
3864 p_attribute1.time_attribute_id,
3865 p_attribute1.object_version_number
3866 );
3867 FETCH csr_get_mapping_name INTO l_mapping_name;
3868 CLOSE csr_get_mapping_name;
3869 END IF;
3870 --Changes made to make use of bld_blk_info_type always.
3871 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3872 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE5';
3873 l_change_att_index := l_change_att_index+1;
3874
3875 l_return_mapp := l_return_mapp
3876 || ':'
3877 || l_mapping_name;
3878 END IF;
3879
3880 IF ((NVL (p_attribute1.attribute6, 'NULL') <>
3881 NVL (p_attribute2.attribute6, 'NULL')
3882 OR p_tbb_deleted)
3883 )
3884 THEN
3885 l_mapping_name := NULL;
3886
3887 IF ( p_attribute1.attribute6 IS NOT NULL
3888 OR p_attribute2.attribute6 IS NOT NULL
3889 )
3890 THEN
3891
3892
3893 OPEN csr_get_mapping_name (
3894 'attribute6',
3895 p_attribute1.time_attribute_id,
3896 p_attribute1.object_version_number
3897 );
3898 FETCH csr_get_mapping_name INTO l_mapping_name;
3899 CLOSE csr_get_mapping_name;
3900 END IF;
3901 --Changes made to make use of bld_blk_info_type always.
3902 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3903 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE6';
3904 l_change_att_index := l_change_att_index+1;
3905
3906 l_return_mapp := l_return_mapp
3907 || ':'
3908 || l_mapping_name;
3909 END IF;
3910
3911 IF ((NVL (p_attribute1.attribute7, 'NULL') <>
3912 NVL (p_attribute2.attribute7, 'NULL')
3913 OR p_tbb_deleted)
3914 )
3915 THEN
3916 l_mapping_name := NULL;
3917
3918
3919 IF ( p_attribute1.attribute7 IS NOT NULL
3920 OR p_attribute2.attribute7 IS NOT NULL
3921 )
3922 THEN
3923
3924
3925 OPEN csr_get_mapping_name (
3926 'attribute7',
3927 p_attribute1.time_attribute_id,
3928 p_attribute1.object_version_number
3929 );
3930 FETCH csr_get_mapping_name INTO l_mapping_name;
3931 CLOSE csr_get_mapping_name;
3932 END IF;
3933 --Changes made to make use of bld_blk_info_type always.
3934 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3935 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE7';
3936 l_change_att_index := l_change_att_index+1;
3937
3938 l_return_mapp := l_return_mapp
3939 || ':'
3940 || l_mapping_name;
3941 END IF;
3942
3943 IF ((NVL (p_attribute1.attribute8, 'NULL') <>
3944 NVL (p_attribute2.attribute8, 'NULL')
3945 OR p_tbb_deleted)
3946 )
3947 THEN
3948 l_mapping_name := NULL;
3949
3950 IF ( p_attribute1.attribute8 IS NOT NULL
3951 OR p_attribute2.attribute8 IS NOT NULL
3952 )
3953 THEN
3954
3955 OPEN csr_get_mapping_name (
3956 'attribute8',
3957 p_attribute1.time_attribute_id,
3958 p_attribute1.object_version_number
3959 );
3960 FETCH csr_get_mapping_name INTO l_mapping_name;
3961 CLOSE csr_get_mapping_name;
3962 END IF;
3963 --Changes made to make use of bld_blk_info_type always.
3964 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3965 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE8';
3966 l_change_att_index := l_change_att_index+1;
3967
3968 l_return_mapp := l_return_mapp
3969 || ':'
3970 || l_mapping_name;
3971 END IF;
3972
3973 IF ((NVL (p_attribute1.attribute9, 'NULL') <>
3974 NVL (p_attribute2.attribute9, 'NULL')
3975 OR p_tbb_deleted)
3976 )
3977 THEN
3978 l_mapping_name := NULL;
3979
3980 IF ( p_attribute1.attribute9 IS NOT NULL
3981 OR p_attribute2.attribute9 IS NOT NULL
3982 )
3983 THEN
3984 OPEN csr_get_mapping_name (
3985 'attribute9',
3986 p_attribute1.time_attribute_id,
3987 p_attribute1.object_version_number
3988 );
3989 FETCH csr_get_mapping_name INTO l_mapping_name;
3990 CLOSE csr_get_mapping_name;
3991 END IF;
3992 --Changes made to make use of bld_blk_info_type always.
3993 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3994 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE9';
3995 l_change_att_index := l_change_att_index+1;
3996
3997
3998 l_return_mapp := l_return_mapp
3999 || ':'
4000 || l_mapping_name;
4001 END IF;
4002
4003 IF ((NVL (p_attribute1.attribute10, 'NULL') <>
4004 NVL (p_attribute2.attribute10, 'NULL')
4005 OR p_tbb_deleted)
4006 )
4007 THEN
4008 l_mapping_name := NULL;
4009
4010 IF ( p_attribute1.attribute10 IS NOT NULL
4011 OR p_attribute2.attribute10 IS NOT NULL
4012 )
4013 THEN
4014
4015 OPEN csr_get_mapping_name (
4016 'attribute10',
4017 p_attribute1.time_attribute_id,
4018 p_attribute1.object_version_number
4019 );
4020 FETCH csr_get_mapping_name INTO l_mapping_name;
4021 CLOSE csr_get_mapping_name;
4022 END IF;
4023 --Changes made to make use of bld_blk_info_type always.
4024 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4025 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE10';
4026 l_change_att_index := l_change_att_index+1;
4027
4028 l_return_mapp := l_return_mapp
4029 || ':'
4030 || l_mapping_name;
4031 END IF;
4032
4033 IF ((NVL (p_attribute1.attribute11, 'NULL') <>
4034 NVL (p_attribute2.attribute11, 'NULL')
4035 OR p_tbb_deleted)
4036 )
4037 THEN
4038 l_mapping_name := NULL;
4039
4040 IF ( p_attribute1.attribute11 IS NOT NULL
4041 OR p_attribute2.attribute11 IS NOT NULL
4042 )
4043 THEN
4044
4045 OPEN csr_get_mapping_name (
4046 'attribute11',
4047 p_attribute1.time_attribute_id,
4048 p_attribute1.object_version_number
4049 );
4050 FETCH csr_get_mapping_name INTO l_mapping_name;
4051 CLOSE csr_get_mapping_name;
4052 END IF;
4053 --Changes made to make use of bld_blk_info_type always.
4054 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4055 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE11';
4056 l_change_att_index := l_change_att_index+1;
4057
4058
4059 l_return_mapp := l_return_mapp
4060 || ':'
4061 || l_mapping_name;
4062 END IF;
4063
4064 IF ((NVL (p_attribute1.attribute12, 'NULL') <>
4065 NVL (p_attribute2.attribute12, 'NULL')
4066 OR p_tbb_deleted)
4067 )
4068 THEN
4069 l_mapping_name := NULL;
4070
4071 IF ( p_attribute1.attribute12 IS NOT NULL
4072 OR p_attribute2.attribute12 IS NOT NULL
4073 )
4074 THEN
4075
4076 OPEN csr_get_mapping_name (
4077 'attribute12',
4078 p_attribute1.time_attribute_id,
4079 p_attribute1.object_version_number
4080 );
4081 FETCH csr_get_mapping_name INTO l_mapping_name;
4082 CLOSE csr_get_mapping_name;
4083 END IF;
4084 --Changes made to make use of bld_blk_info_type always.
4085 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4086 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE12';
4087 l_change_att_index := l_change_att_index+1;
4088
4089 l_return_mapp := l_return_mapp
4090 || ':'
4091 || l_mapping_name;
4092 END IF;
4093
4094 IF ((NVL (p_attribute1.attribute13, 'NULL') <>
4095 NVL (p_attribute2.attribute13, 'NULL')
4096 OR p_tbb_deleted)
4097 )
4098 THEN
4099
4100 l_mapping_name := NULL;
4101
4102
4103 IF ( p_attribute1.attribute13 IS NOT NULL
4104 OR p_attribute2.attribute13 IS NOT NULL
4105 )
4106 THEN
4107
4108 OPEN csr_get_mapping_name (
4109 'attribute13',
4110 p_attribute1.time_attribute_id,
4111 p_attribute1.object_version_number
4112 );
4113 FETCH csr_get_mapping_name INTO l_mapping_name;
4114 CLOSE csr_get_mapping_name;
4115 END IF;
4116 --Changes made to make use of bld_blk_info_type always.
4117 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4118 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE13';
4119 l_change_att_index := l_change_att_index+1;
4120
4121 l_return_mapp := l_return_mapp
4122 || ':'
4123 || l_mapping_name;
4124 END IF;
4125
4126 IF ((NVL (p_attribute1.attribute14, 'NULL') <>
4127 NVL (p_attribute2.attribute14, 'NULL')
4128 OR p_tbb_deleted)
4129 )
4130 THEN
4131 l_mapping_name := NULL;
4132
4133 IF ( p_attribute1.attribute14 IS NOT NULL
4134 OR p_attribute2.attribute14 IS NOT NULL
4135 )
4136 THEN
4137
4138 OPEN csr_get_mapping_name (
4139 'attribute14',
4140 p_attribute1.time_attribute_id,
4141 p_attribute1.object_version_number
4142 );
4143 FETCH csr_get_mapping_name INTO l_mapping_name;
4144 CLOSE csr_get_mapping_name;
4145 END IF;
4146 --Changes made to make use of bld_blk_info_type always.
4147 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4148 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE14';
4149 l_change_att_index := l_change_att_index+1;
4150
4151 l_return_mapp := l_return_mapp
4152 || ':'
4153 || l_mapping_name;
4154 END IF;
4155
4156 IF ((NVL (p_attribute1.attribute15, 'NULL') <>
4157 NVL (p_attribute2.attribute15, 'NULL')
4158 OR p_tbb_deleted)
4159 )
4160 THEN
4161 l_mapping_name := NULL;
4162
4163
4164 IF ( p_attribute1.attribute15 IS NOT NULL
4165 OR p_attribute2.attribute15 IS NOT NULL
4166 )
4167 THEN
4168
4169 OPEN csr_get_mapping_name (
4170 'attribute15',
4171 p_attribute1.time_attribute_id,
4172 p_attribute1.object_version_number
4173 );
4174 FETCH csr_get_mapping_name INTO l_mapping_name;
4175 CLOSE csr_get_mapping_name;
4176 END IF;
4177 --Changes made to make use of bld_blk_info_type always.
4178 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4179 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE15';
4180 l_change_att_index := l_change_att_index+1;
4181
4182 l_return_mapp := l_return_mapp
4183 || ':'
4184 || l_mapping_name;
4185 END IF;
4186
4187 IF ((NVL (p_attribute1.attribute16, 'NULL') <>
4188 NVL (p_attribute2.attribute16, 'NULL')
4189 OR p_tbb_deleted)
4190 )
4191 THEN
4192 l_mapping_name := NULL;
4193
4194 IF ( p_attribute1.attribute16 IS NOT NULL
4195 OR p_attribute2.attribute16 IS NOT NULL
4196 )
4197 THEN
4198
4199 OPEN csr_get_mapping_name (
4200 'attribute16',
4201 p_attribute1.time_attribute_id,
4202 p_attribute1.object_version_number
4203 );
4204 FETCH csr_get_mapping_name INTO l_mapping_name;
4205 CLOSE csr_get_mapping_name;
4206 END IF;
4207 --Changes made to make use of bld_blk_info_type always.
4208 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4209 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE16';
4210 l_change_att_index := l_change_att_index+1;
4211
4212 l_return_mapp := l_return_mapp
4213 || ':'
4214 || l_mapping_name;
4215 END IF;
4216
4217 IF ((NVL (p_attribute1.attribute17, 'NULL') <>
4218 NVL (p_attribute2.attribute17, 'NULL')
4219 OR p_tbb_deleted)
4220 )
4221 THEN
4222 l_mapping_name := NULL;
4223
4224 IF ( p_attribute1.attribute17 IS NOT NULL
4225 OR p_attribute2.attribute17 IS NOT NULL
4226 )
4227 THEN
4228
4229 OPEN csr_get_mapping_name (
4230 'attribute17',
4231 p_attribute1.time_attribute_id,
4232 p_attribute1.object_version_number
4233 );
4234 FETCH csr_get_mapping_name INTO l_mapping_name;
4235 CLOSE csr_get_mapping_name;
4236 END IF;
4237 --Changes made to make use of bld_blk_info_type always.
4238 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4239 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE17';
4240 l_change_att_index := l_change_att_index+1;
4241
4242 l_return_mapp := l_return_mapp
4243 || ':'
4244 || l_mapping_name;
4245 END IF;
4246
4247 IF ((NVL (p_attribute1.attribute18, 'NULL') <>
4248 NVL (p_attribute2.attribute18, 'NULL')
4249 OR p_tbb_deleted)
4250 )
4251 THEN
4252 l_mapping_name := NULL;
4253
4254 IF ( p_attribute1.attribute18 IS NOT NULL
4255 OR p_attribute2.attribute18 IS NOT NULL
4256 )
4257 THEN
4258
4259 OPEN csr_get_mapping_name (
4260 'attribute18',
4261 p_attribute1.time_attribute_id,
4262 p_attribute1.object_version_number
4263 );
4264 FETCH csr_get_mapping_name INTO l_mapping_name;
4265 CLOSE csr_get_mapping_name;
4266 END IF;
4267 --Changes made to make use of bld_blk_info_type always.
4268 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4269 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE18';
4270 l_change_att_index := l_change_att_index+1;
4271
4272 l_return_mapp := l_return_mapp
4273 || ':'
4274 || l_mapping_name;
4275 END IF;
4276
4277 IF ((NVL (p_attribute1.attribute19, 'NULL') <>
4278 NVL (p_attribute2.attribute19, 'NULL')
4279 OR p_tbb_deleted)
4280 )
4281 THEN
4282 l_mapping_name := NULL;
4283
4284 IF ( p_attribute1.attribute19 IS NOT NULL
4285 OR p_attribute2.attribute19 IS NOT NULL
4286 )
4287 THEN
4288
4289 OPEN csr_get_mapping_name (
4290 'attribute19',
4291 p_attribute1.time_attribute_id,
4292 p_attribute1.object_version_number
4293 );
4294 FETCH csr_get_mapping_name INTO l_mapping_name;
4295 CLOSE csr_get_mapping_name;
4296 END IF;
4297 --Changes made to make use of bld_blk_info_type always.
4298 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4299 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE19';
4300 l_change_att_index := l_change_att_index+1;
4301
4302 l_return_mapp := l_return_mapp
4303 || ':'
4304 || l_mapping_name;
4305 END IF;
4306
4307 IF ((NVL (p_attribute1.attribute20, 'NULL') <>
4308 NVL (p_attribute2.attribute20, 'NULL')
4309 OR p_tbb_deleted)
4310 )
4311 THEN
4312 l_mapping_name := NULL;
4313
4314 IF ( p_attribute1.attribute20 IS NOT NULL
4315 OR p_attribute2.attribute20 IS NOT NULL
4316 )
4317 THEN
4318
4319 OPEN csr_get_mapping_name (
4320 'attribute20',
4321 p_attribute1.time_attribute_id,
4322 p_attribute1.object_version_number
4323 );
4324 FETCH csr_get_mapping_name INTO l_mapping_name;
4325 CLOSE csr_get_mapping_name;
4326 END IF;
4327 --Changes made to make use of bld_blk_info_type always.
4328 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4329 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE20';
4330 l_change_att_index := l_change_att_index+1;
4331
4332 l_return_mapp := l_return_mapp
4333 || ':'
4334 || l_mapping_name;
4335 END IF;
4336
4337 IF ((NVL (p_attribute1.attribute21, 'NULL') <>
4338 NVL (p_attribute2.attribute21, 'NULL')
4339 OR p_tbb_deleted)
4340 )
4341 THEN
4342 l_mapping_name := NULL;
4343
4344 IF ( p_attribute1.attribute21 IS NOT NULL
4345 OR p_attribute2.attribute21 IS NOT NULL
4346 )
4347 THEN
4348
4349
4350 OPEN csr_get_mapping_name (
4351 'attribute21',
4352 p_attribute1.time_attribute_id,
4353 p_attribute1.object_version_number
4354 );
4355 FETCH csr_get_mapping_name INTO l_mapping_name;
4356 CLOSE csr_get_mapping_name;
4357 END IF;
4358 --Changes made to make use of bld_blk_info_type always.
4359 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4360 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE21';
4361 l_change_att_index := l_change_att_index+1;
4362
4363 l_return_mapp := l_return_mapp
4364 || ':'
4365 || l_mapping_name;
4366 END IF;
4367
4368 IF ((NVL (p_attribute1.attribute22, 'NULL') <>
4369 NVL (p_attribute2.attribute22, 'NULL')
4370 OR p_tbb_deleted)
4371 )
4372 THEN
4373 l_mapping_name := NULL;
4374
4375
4376 IF ( p_attribute1.attribute22 IS NOT NULL
4377 OR p_attribute2.attribute22 IS NOT NULL
4378 )
4379 THEN
4380
4381 OPEN csr_get_mapping_name (
4382 'attribute22',
4383 p_attribute1.time_attribute_id,
4384 p_attribute1.object_version_number
4385 );
4386 FETCH csr_get_mapping_name INTO l_mapping_name;
4387 CLOSE csr_get_mapping_name;
4388 END IF;
4389 --Changes made to make use of bld_blk_info_type always.
4390 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4391 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE22';
4392 l_change_att_index := l_change_att_index+1;
4393
4394 l_return_mapp := l_return_mapp
4395 || ':'
4396 || l_mapping_name;
4397 END IF;
4398
4399 IF ((NVL (p_attribute1.attribute23, 'NULL') <>
4400 NVL (p_attribute2.attribute23, 'NULL')
4401 OR p_tbb_deleted)
4402 )
4403 THEN
4404 l_mapping_name := NULL;
4405
4406 IF ( p_attribute1.attribute23 IS NOT NULL
4407 OR p_attribute2.attribute23 IS NOT NULL
4408 )
4409 THEN
4410
4411 OPEN csr_get_mapping_name (
4412 'attribute23',
4413 p_attribute1.time_attribute_id,
4414 p_attribute1.object_version_number
4415 );
4416 FETCH csr_get_mapping_name INTO l_mapping_name;
4417 CLOSE csr_get_mapping_name;
4418 END IF;
4419 --Changes made to make use of bld_blk_info_type always.
4420 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4421 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE23';
4422 l_change_att_index := l_change_att_index+1;
4423
4424 l_return_mapp := l_return_mapp
4425 || ':'
4426 || l_mapping_name;
4427 END IF;
4428
4429 IF ((NVL (p_attribute1.attribute24, 'NULL') <>
4430 NVL (p_attribute2.attribute24, 'NULL')
4431 OR p_tbb_deleted)
4432 )
4433 THEN
4434 l_mapping_name := NULL;
4435
4436 IF ( p_attribute1.attribute24 IS NOT NULL
4437 OR p_attribute2.attribute24 IS NOT NULL
4438 )
4439 THEN
4440
4441 OPEN csr_get_mapping_name (
4442 'attribute24',
4443 p_attribute1.time_attribute_id,
4444 p_attribute1.object_version_number
4445 );
4446 FETCH csr_get_mapping_name INTO l_mapping_name;
4447 CLOSE csr_get_mapping_name;
4448 END IF;
4449 --Changes made to make use of bld_blk_info_type always.
4450 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4451 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE24';
4452 l_change_att_index := l_change_att_index+1;
4453
4454 l_return_mapp := l_return_mapp
4455 || ':'
4456 || l_mapping_name;
4457 END IF;
4458
4459 IF ((NVL (p_attribute1.attribute25, 'NULL') <>
4460 NVL (p_attribute2.attribute25, 'NULL')
4461 OR p_tbb_deleted)
4462 )
4463 THEN
4464 l_mapping_name := NULL;
4465
4466
4467 IF ( p_attribute1.attribute25 IS NOT NULL
4468 OR p_attribute2.attribute25 IS NOT NULL
4469 )
4470 THEN
4471
4472 OPEN csr_get_mapping_name (
4473 'attribute25',
4474 p_attribute1.time_attribute_id,
4475 p_attribute1.object_version_number
4476 );
4477 FETCH csr_get_mapping_name INTO l_mapping_name;
4478 CLOSE csr_get_mapping_name;
4479 END IF;
4480 --Changes made to make use of bld_blk_info_type always.
4481 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4482 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE25';
4483 l_change_att_index := l_change_att_index+1;
4484
4485 l_return_mapp := l_return_mapp
4486 || ':'
4487 || l_mapping_name;
4488 END IF;
4489
4490 IF ((NVL (p_attribute1.attribute26, 'NULL') <>
4491 NVL (p_attribute2.attribute26, 'NULL')
4492 OR p_tbb_deleted)
4493 )
4494 THEN
4495 l_mapping_name := NULL;
4496
4497
4498 IF ( p_attribute1.attribute26 IS NOT NULL
4499 OR p_attribute2.attribute26 IS NOT NULL
4500 )
4501 THEN
4502 OPEN csr_get_mapping_name (
4503 'attribute26',
4504 p_attribute1.time_attribute_id,
4505 p_attribute1.object_version_number
4506 );
4507 FETCH csr_get_mapping_name INTO l_mapping_name;
4508 CLOSE csr_get_mapping_name;
4509 END IF;
4510 --Changes made to make use of bld_blk_info_type always.
4511 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4512 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE26';
4513 l_change_att_index := l_change_att_index+1;
4514
4515
4516 l_return_mapp := l_return_mapp
4517 || ':'
4518 || l_mapping_name;
4519 END IF;
4520
4521 IF ((NVL (p_attribute1.attribute27, 'NULL') <>
4522 NVL (p_attribute2.attribute27, 'NULL')
4523 OR p_tbb_deleted)
4524 )
4525 THEN
4526 l_mapping_name := NULL;
4527
4528 IF ( p_attribute1.attribute27 IS NOT NULL
4529 OR p_attribute2.attribute27 IS NOT NULL
4530 )
4531 THEN
4532
4533 OPEN csr_get_mapping_name (
4534 'attribute27',
4535 p_attribute1.time_attribute_id,
4536 p_attribute1.object_version_number
4537 );
4538 FETCH csr_get_mapping_name INTO l_mapping_name;
4539 CLOSE csr_get_mapping_name;
4540 END IF;
4541 --Changes made to make use of bld_blk_info_type always.
4542 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4543 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE27';
4544 l_change_att_index := l_change_att_index+1;
4545
4546 l_return_mapp := l_return_mapp
4547 || ':'
4548 || l_mapping_name;
4549 END IF;
4550
4551 IF ((NVL (p_attribute1.attribute28, 'NULL') <>
4552 NVL (p_attribute2.attribute28, 'NULL')
4553 OR p_tbb_deleted)
4554 )
4555 THEN
4556 l_mapping_name := NULL;
4557
4558 IF ( p_attribute1.attribute28 IS NOT NULL
4559 OR p_attribute2.attribute28 IS NOT NULL
4560 )
4561 THEN
4562
4563 OPEN csr_get_mapping_name (
4564 'attribute28',
4565 p_attribute1.time_attribute_id,
4566 p_attribute1.object_version_number
4567 );
4568 FETCH csr_get_mapping_name INTO l_mapping_name;
4569 CLOSE csr_get_mapping_name;
4570 END IF;
4571 --Changes made to make use of bld_blk_info_type always.
4572 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4573 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE28';
4574 l_change_att_index := l_change_att_index+1;
4575
4576 l_return_mapp := l_return_mapp
4577 || ':'
4578 || l_mapping_name;
4579 END IF;
4580
4581 IF ((NVL (p_attribute1.attribute29, 'NULL') <>
4582 NVL (p_attribute2.attribute29, 'NULL')
4583 OR p_tbb_deleted)
4584 )
4585 THEN
4586 l_mapping_name := NULL;
4587
4588 IF ( p_attribute1.attribute29 IS NOT NULL
4589 OR p_attribute2.attribute29 IS NOT NULL
4590 )
4591 THEN
4592
4593 OPEN csr_get_mapping_name (
4594 'attribute29',
4595 p_attribute1.time_attribute_id,
4596 p_attribute1.object_version_number
4597 );
4598 FETCH csr_get_mapping_name INTO l_mapping_name;
4599 CLOSE csr_get_mapping_name;
4600 END IF;
4601 --Changes made to make use of bld_blk_info_type always.
4602 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4603 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE29';
4604 l_change_att_index := l_change_att_index+1;
4605
4606 l_return_mapp := l_return_mapp
4607 || ':'
4608 || l_mapping_name;
4609 END IF;
4610
4611 IF ((NVL (p_attribute1.attribute30, 'NULL') <>
4612 NVL (p_attribute2.attribute30, 'NULL')
4613 OR p_tbb_deleted)
4614 )
4615 THEN
4616 l_mapping_name := NULL;
4617
4618 IF ( p_attribute1.attribute30 IS NOT NULL
4619 OR p_attribute2.attribute30 IS NOT NULL
4620 )
4621 THEN
4622
4623 OPEN csr_get_mapping_name (
4624 'attribute30',
4625 p_attribute1.time_attribute_id,
4626 p_attribute1.object_version_number
4627 );
4628 FETCH csr_get_mapping_name INTO l_mapping_name;
4629 CLOSE csr_get_mapping_name;
4630 END IF;
4631 --Changes made to make use of bld_blk_info_type always.
4632 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4633 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE30';
4634 l_change_att_index := l_change_att_index+1;
4635
4636 l_return_mapp := l_return_mapp
4637 || ':'
4638 || l_mapping_name;
4639 END IF;
4640
4641 if g_debug then
4642 hr_utility.TRACE (l_return_mapp);
4643 end if;
4644 RETURN l_return_mapp;
4645 END compare_tbb_attributes;
4646
4647 FUNCTION compare_time_building_blocks (
4648 p_block1 hxc_self_service_time_deposit.building_block_info,
4649 p_block2 hxc_self_service_time_deposit.building_block_info,
4650 p_tbb_deleted BOOLEAN,
4651 p_change_att_tab IN OUT NOCOPY t_change_att_tab
4652 )
4653 RETURN VARCHAR2
4654 IS
4655 CURSOR csr_get_mapping_name(p_segment varchar2)
4656 IS
4657 SELECT hmc.field_name
4658 FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
4659 WHERE hmc.segment = upper(p_segment)
4660 AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
4661 AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';
4662 /*
4663 CURSOR csr_get_mapping_name(p_segment varchar2)
4664 IS
4665 select substr(fcu.form_left_prompt,1,30)
4666 from hxc_mapping_components mc
4667 ,hxc_bld_blk_info_types bbit
4668 ,fnd_descr_flex_col_usage_tl fcu
4669 where
4670 mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
4671 and fcu.application_column_name = mc.segment
4672 and fcu.descriptive_flex_context_code = bbit.bld_blk_info_type
4673 and fcu.descriptive_flexfield_name = 'OTC Information Types'
4674 and fcu.application_id = 809
4675 and fcu.language = userenv('LANG')
4676 and fcu.application_column_name=p_segment
4677 AND bbit.bld_blk_info_type='BUILDING_BLOCKS';
4678 */
4679
4680 l_ret_val VARCHAR2 (300);
4681 l_mapping_name VARCHAR2(80);
4682 l_change_att_index NUMBER;
4683 BEGIN
4684
4685
4686
4687 IF p_change_att_tab.count = 0 then
4688 l_change_att_index :=1;
4689 ELSE
4690 l_change_att_index := p_change_att_tab.last+1;
4691 END IF;
4692
4693 if g_debug then
4694 hr_utility.TRACE ( 'p_block1.scope'
4695 || p_block1.SCOPE);
4696 hr_utility.TRACE ( 'p_block2.scope'
4697 || p_block2.SCOPE);
4698 end if;
4699
4700 IF (p_block1.SCOPE = 'DETAIL')
4701 THEN
4702
4703 --
4704 -- There is only a subset of things that
4705 -- can be changed in the block, we
4706 -- look for these things
4707 --
4708 -- 1. Measure
4709 IF ((NVL (p_block1.measure,0) <> NVL (p_block2.measure, 0))
4710 OR
4711 (p_block1.measure is not null and p_tbb_deleted ))
4712 THEN
4713 if g_debug then
4714 hr_utility.TRACE ('Before assignment');
4715 end if;
4716 OPEN csr_get_mapping_name('ATTRIBUTE1');
4717 FETCH csr_get_mapping_name INTO l_mapping_name;
4718 CLOSE csr_get_mapping_name;
4719
4720 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4721 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4722 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4723 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE1';
4724 l_change_att_index := p_change_att_tab.last+1;
4725
4726 l_ret_val := 'BUILDING_BLOCKS'||'|'||'ATTRIBUTE1'||'|'||l_mapping_name;
4727 if g_debug then
4728 hr_utility.TRACE ('After Assignment');
4729 end if;
4730 END IF;
4731
4732 if g_debug then
4733 hr_utility.TRACE ('l_ret_val');
4734 end if;
4735
4736 -- 2. Start Time
4737 IF ((NVL (p_block1.start_time, to_date('01-01-0090','dd-mm-rrrr')) <>
4738 NVL (p_block2.start_time, to_date('01-01-0090','dd-mm-rrrr')))
4739 OR
4740 (p_block1.start_time is not null and p_tbb_deleted )
4741 )
4742 THEN
4743 if g_debug then
4744 hr_utility.TRACE ( 'l_ret_val'
4745 || l_ret_val);
4746 end if;
4747 OPEN csr_get_mapping_name('ATTRIBUTE2');
4748 FETCH csr_get_mapping_name INTO l_mapping_name;
4749 CLOSE csr_get_mapping_name;
4750
4751 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4752 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4753 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4754 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE2';
4755 l_change_att_index := p_change_att_tab.last+1;
4756
4757 l_ret_val := l_ret_val || ':' || 'BUILDING_BLOCKS'||'|'||'ATTRIBUTE2'||'|'||l_mapping_name;
4758
4759 END IF;
4760
4761
4762 -- 3. Stop Time
4763 IF ((NVL (p_block1.stop_time, to_date('01-01-0090','dd-mm-rrrr')) <>
4764 NVL (p_block2.stop_time, to_date('01-01-0090','dd-mm-rrrr')))
4765 OR
4766 (p_block1.stop_time is not null and p_tbb_deleted )
4767 )
4768 THEN
4769 OPEN csr_get_mapping_name('ATTRIBUTE3');
4770 FETCH csr_get_mapping_name INTO l_mapping_name;
4771 CLOSE csr_get_mapping_name;
4772 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4773 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4774 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4775 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE3';
4776
4777 l_change_att_index := p_change_att_tab.last+1;
4778
4779 l_ret_val := l_ret_val || ':' || 'BUILDING_BLOCKS'||'|'||'ATTRIBUTE3'||'|'||l_mapping_name;
4780 END IF;
4781
4782
4783 -- 4. Comment
4784
4785 IF ((NVL (p_block1.comment_text, 'NULL') <>
4786 NVL (p_block2.comment_text, 'NULL'))
4787 OR
4788 (p_block1.comment_text is not null and p_tbb_deleted )
4789 )
4790 THEN
4791 OPEN csr_get_mapping_name('ATTRIBUTE4');
4792 FETCH csr_get_mapping_name INTO l_mapping_name;
4793 CLOSE csr_get_mapping_name;
4794 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4795 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4796 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4797 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE4';
4798 l_change_att_index := p_change_att_tab.last+1;
4799
4800 l_ret_val := l_ret_val || ':' ||'BUILDING_BLOCKS'||'|'||'ATTRIBUTE4'||'|'||l_mapping_name;
4801
4802 END IF;
4803 END IF;
4804 RETURN l_ret_val;
4805 END compare_time_building_blocks;
4806
4807 PROCEDURE execute_change_ter (
4808 p_tco_bb IN hxc_self_service_time_deposit.timecard_info,
4809 p_tco_att IN hxc_self_service_time_deposit.building_block_attribute_info,
4810 p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
4811 p_message_level VARCHAR2,
4812 p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%ROWTYPE
4813 )
4814 IS
4815 /* CURSOR csr_get_mapping_name(p_segment varchar2)
4816 IS
4817 SELECT hmc.field_name
4818 FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
4819 WHERE hmc.segment = upper(p_segment)
4820 AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
4821 AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';*/
4822
4823 l_time_category_id hxc_time_categories.time_category_id%TYPE;
4824 l_tbb_index NUMBER;
4825 l_att_index NUMBER;
4826 l_old_tbb_index NUMBER;
4827 l_old_att_index NUMBER;
4828 l_old_tco_bb hxc_self_service_time_deposit.timecard_info;
4829 l_old_tco_att hxc_self_service_time_deposit.building_block_attribute_info;
4830 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
4831 l_build_change_list VARCHAR2 (2000);
4832 l_change_list VARCHAR2 (2000);
4833 l_time_cat_tab hxc_time_category_utils_pkg.t_time_category;
4834 l_long LONG;
4835 l_operator hxc_time_categories.OPERATOR%TYPE;
4836 l_tc_bb_ok_tab hxc_time_category_utils_pkg.t_tc_bb_ok;
4837 l_tc_bb_ok_tab_old hxc_time_category_utils_pkg.t_tc_bb_ok;
4838 l_tc_bb_ok_string VARCHAR2(32000);
4839 l_tc_bb_not_ok_string VARCHAR2(32000);
4840 n NUMBER;
4841 l_tbb_deleted BOOLEAN;
4842 l_change_att_tab t_change_att_tab;
4843
4844 BEGIN
4845
4846
4847 if g_debug then
4848 hr_utility.TRACE ('Before get_timecard_info');
4849 end if;
4850 get_timecard_info (
4851 p_time_building_blocks=> P_TIME_BUILDING_BLOCKS,
4852 p_timecard_rec=> l_timecard_info_rec
4853 );
4854
4855 if g_debug then
4856 hr_utility.TRACE ('Before populate_old_tco');
4857 end if;
4858 populate_old_tco (
4859 p_timecard_rec=> l_timecard_info_rec,
4860 p_timecard_building_blocks=> l_old_tco_bb,
4861 p_timecard_attributes=> l_old_tco_att
4862 );
4863
4864 IF (p_rule_record.attribute1 IS NOT NULL)
4865 THEN
4866 if g_debug then
4867 hr_utility.TRACE (
4868 'Attribute 1 -- Time Category '
4869 || p_rule_record.attribute1
4870 );
4871 end if;
4872
4873 -- populate the time category bb ok table
4874
4875 hxc_time_category_utils_pkg.evaluate_time_category (
4876 p_time_category_id => p_rule_record.attribute1
4877 , p_tc_bb_ok_tab => l_tc_bb_ok_tab
4878 , p_tc_bb_ok_string => l_tc_bb_ok_string
4879 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string );
4880
4881 -- populate the old attribute table
4882
4883
4884 hxc_time_category_utils_pkg.push_attributes ( l_old_tco_att );
4885
4886
4887 -- populate the time category bb ok table
4888
4889 hxc_time_category_utils_pkg.evaluate_time_category (
4890 p_time_category_id => p_rule_record.attribute1
4891 , p_tc_bb_ok_tab => l_tc_bb_ok_tab_old
4892 , p_tc_bb_ok_string => l_tc_bb_ok_string
4893 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string
4894 , p_use_tc_bb_cache => FALSE );
4895
4896 -- put back the original attributes
4897
4898 hxc_time_category_utils_pkg.push_attributes ( p_tco_att );
4899
4900 END IF;
4901
4902 if g_debug then
4903 hr_utility.TRACE ('Returned from populate_old_tco');
4904 end if;
4905
4906 n:= l_old_tco_bb.first;
4907 loop
4908 exit when not l_old_tco_bb.exists(n);
4909 if g_debug then
4910 hr_utility.trace('ID ' || 'OVN' || 'SCOPE' || 'Changed' || 'New' || 'Date To');
4911 hr_utility.trace(l_old_tco_bb(n).time_building_block_id||' ' || l_old_tco_bb(n).object_version_number|| ' ' || l_old_tco_bb(n).scope ||' '
4912 || l_old_tco_bb(n).changed|| ' ' ||l_old_tco_bb(n).new || to_char(l_old_tco_bb(n).date_to,'dd-mon-rrrr'));
4913 end if;
4914 n := p_time_building_blocks.next(n);
4915 end loop;
4916
4917 l_tbb_index := p_tco_att.FIRST;
4918
4919 LOOP
4920 EXIT WHEN NOT p_tco_att.EXISTS (l_tbb_index);
4921 if g_debug then
4922 hr_utility.TRACE (
4923 p_tco_att (l_tbb_index).time_attribute_id
4924 || ' ' || p_tco_att (l_tbb_index).object_version_number
4925 || 'attribute_category' || p_tco_att (l_tbb_index).attribute_category
4926 || 'attribute1 '|| p_tco_att (l_tbb_index).attribute1
4927 || 'Attribute2 '|| p_tco_att (l_tbb_index).attribute2
4928 );
4929 end if;
4930 l_tbb_index := p_tco_att.NEXT (l_tbb_index);
4931 END LOOP;
4932
4933 if g_debug then
4934 hr_utility.TRACE ('OLD TBB');
4935 end if;
4936 l_old_tbb_index := l_old_tco_att.FIRST;
4937
4938 LOOP
4939 EXIT WHEN NOT l_old_tco_att.EXISTS (l_old_tbb_index);
4940 if g_debug then
4941 hr_utility.TRACE (
4942 l_old_tco_att (l_old_tbb_index).time_attribute_id
4943 || ' '|| l_old_tco_att (l_old_tbb_index).object_version_number
4944 || 'Attribute_category '|| l_old_tco_att (l_old_tbb_index).attribute_category
4945 || 'Attribute1 '|| l_old_tco_att (l_old_tbb_index).attribute1
4946 || 'Attribute2 '|| l_old_tco_att (l_old_tbb_index).attribute2
4947 );
4948 end if;
4949 l_old_tbb_index := l_old_tco_att.NEXT (l_old_tbb_index);
4950 END LOOP;
4951
4952 l_tbb_index := p_tco_bb.FIRST;
4953
4954
4955 /* Loop through Building blocks */
4956 LOOP
4957 if g_debug then
4958 hr_utility.TRACE ('Timecard Loop');
4959 end if;
4960 l_build_change_list := null;
4961 l_tbb_deleted := FALSE;
4962 EXIT WHEN NOT p_tco_bb.EXISTS (l_tbb_index);
4963
4964 L_CHANGE_ATT_TAB.delete;
4965
4966 IF ( p_tco_bb (l_tbb_index).SCOPE = 'DETAIL'
4967 AND p_tco_bb (l_tbb_index).new <> 'Y'
4968 )
4969 THEN
4970
4971
4972 l_old_tbb_index := l_old_tco_bb.FIRST;
4973 if g_debug then
4974 hr_utility.TRACE ('Old Timecard Loop');
4975 end if;
4976 if (trunc(p_tco_bb(l_tbb_index).date_to) = trunc(sysdate)) then
4977 l_tbb_deleted := TRUE;
4978 else
4979 l_tbb_deleted := FALSE;
4980 end if;
4981 LOOP
4982 EXIT WHEN NOT l_old_tco_bb.EXISTS (l_old_tbb_index); -- OR l_tbb_deleted;
4983 l_change_list := NULL;
4984 if g_debug then
4985 hr_utility.TRACE ('TBB Test');
4986 end if;
4987
4988 if g_debug then
4989 hr_utility.trace('new bb/ovn is '||to_char(p_tco_bb(l_tbb_index).time_building_block_id)||':'
4990 ||to_char(p_tco_bb(l_tbb_index).object_version_number));
4991
4992 hr_utility.trace('old bb/ovn is '||to_char(l_old_tco_bb(l_old_tbb_index).time_building_block_id)||':'
4993 ||to_char(l_old_tco_bb(l_old_tbb_index).object_version_number));
4994 end if;
4995
4996 IF ( p_tco_bb (l_tbb_index).time_building_block_id =
4997 l_old_tco_bb (l_old_tbb_index).time_building_block_id
4998 AND p_tco_bb (l_tbb_index).object_version_number =
4999 l_old_tco_bb (l_old_tbb_index).object_version_number
5000 )
5001 THEN
5002
5003 IF ( (p_rule_record.attribute1 IS NULL)
5004 OR ( p_rule_record.attribute1 IS NOT NULL
5005 AND ( l_tc_bb_ok_tab.EXISTS (
5006 p_tco_bb (l_tbb_index).time_building_block_id
5007 )
5008 OR l_tc_bb_ok_tab_old.EXISTS (
5009 p_tco_bb (l_tbb_index).time_building_block_id
5010 )
5011 )
5012 )
5013 )
5014 THEN
5015
5016 -- GPM v115.76 WWB 3027077
5017
5018 -- moved the compare time building blocks to after the
5019 -- above time category check.
5020
5021 l_change_list := NULL;
5022 l_build_change_list := NULL;
5023
5024 l_change_list :=
5025 compare_time_building_blocks (
5026 l_old_tco_bb (l_old_tbb_index),
5027 p_tco_bb (l_tbb_index),
5028 l_tbb_deleted,
5029 l_change_att_tab
5030 );
5031 /*
5032 l_build_change_list :=
5033 l_build_change_list
5034 || l_change_list;
5035
5036
5037 l_build_change_list := l_change_list;
5038 */
5039 l_old_att_index := l_old_tco_att.FIRST;
5040
5041 LOOP
5042 EXIT WHEN NOT l_old_tco_att.EXISTS (
5043 l_old_att_index
5044 );
5045
5046 IF (l_old_tco_att (l_old_att_index).building_block_id =
5047 p_tco_bb (l_tbb_index).time_building_block_id
5048 AND l_old_tco_att(l_old_att_index).attribute_category <> 'REASON'
5049 )
5050 THEN
5051
5052 l_att_index := p_tco_att.FIRST;
5053
5054 LOOP
5055 EXIT WHEN NOT p_tco_att.EXISTS (l_att_index);
5056
5057
5058 IF (l_old_tco_att (l_old_att_index).time_attribute_id =
5059 p_tco_att (l_att_index).time_attribute_id
5060 --AND p_tco_att(l_att_index).changed = 'Y'
5061 )
5062 THEN
5063 l_change_list := NULL;
5064 -- Added for Bug 14544559
5065 IF p_tco_att(l_att_index).ATTRIBUTE_CATEGORY = 'SECURITY'
5066 AND l_old_tco_att(l_old_att_index).ATTRIBUTE_CATEGORY = 'SECURITY'
5067 THEN
5068 -- No need to compare security attributes..
5069 hr_utility.trace('Exclude comparing SECURITY Attributes');
5070 NULL;
5071 ELSE
5072 l_change_list :=
5073 compare_tbb_attributes (
5074 p_tco_att (l_att_index),
5075 l_old_tco_att (l_old_att_index),
5076 l_tbb_deleted,
5077 l_change_att_tab
5078 );
5079 END IF;-- IF p_tco_att(l_att_index).ATTRIBUTE_CATEGORY = 'SECURITY'
5080
5081 /*
5082 IF (l_change_list IS NOT NULL)
5083 THEN
5084 l_build_change_list :=
5085 l_build_change_list
5086 || l_change_list;
5087 END IF;
5088 */
5089 END IF;
5090
5091 l_att_index := p_tco_att.NEXT (l_att_index);
5092 if g_debug then
5093 hr_utility.TRACE ('After old attr loop');
5094 end if;
5095 END LOOP;
5096 END IF;
5097
5098 l_old_att_index :=l_old_tco_att.NEXT (l_old_att_index);
5099 END LOOP;
5100 END IF; -- TBB in attribute category.
5101 END IF; -- l_tc_bb_ok_tab.EXISTS and l_tc_bb_ok_tab_old.EXISTS check
5102
5103 l_old_tbb_index := l_old_tco_bb.NEXT (l_old_tbb_index);
5104
5105 END LOOP;
5106 END IF;
5107
5108 IF L_CHANGE_ATT_TAB.count >0 then
5109
5110 hxc_alias_utility.time_entry_rules_segment_trans
5111 (
5112 p_timecard_id =>l_timecard_info_rec.timecard_bb_id
5113 ,p_timecard_ovn =>l_timecard_info_rec.timecard_ovn
5114 ,p_start_time =>l_timecard_info_rec.start_date
5115 ,p_stop_time =>l_timecard_info_rec.end_date
5116 ,p_resource_id =>l_timecard_info_rec.resource_id
5117 ,p_attr_change_table =>L_CHANGE_ATT_TAB
5118 );
5119
5120 FOR I IN 1..L_CHANGE_ATT_TAB.COUNT LOOP
5121
5122 IF nvl(L_CHANGE_ATT_TAB(i).attribute_category,'xx') <> 'ATTRIBUTE_CATEGORY'
5123 then
5124 if nvl(L_CHANGE_ATT_TAB(i).org_attribute_category,'XX') ='BUILDING_BLOCKS' then
5125
5126 if l_build_change_list is null then
5127 l_build_change_list := L_CHANGE_ATT_TAB(i).org_attribute_category ||'|'||L_CHANGE_ATT_TAB(i).org_changed_attribute||'|'||L_CHANGE_ATT_TAB(i).field_name;
5128 else
5129 l_build_change_list := l_build_change_list ||':'|| L_CHANGE_ATT_TAB(i).org_attribute_category ||'|'||L_CHANGE_ATT_TAB(i).org_changed_attribute||'|'||L_CHANGE_ATT_TAB(i).field_name;
5130 end if;
5131 else
5132 if l_build_change_list is null then
5133 l_build_change_list := L_CHANGE_ATT_TAB(i).attribute_category ||'|'||L_CHANGE_ATT_TAB(i).changed_attribute||'|'||L_CHANGE_ATT_TAB(i).field_name;
5134 else
5135 l_build_change_list := l_build_change_list ||':'|| L_CHANGE_ATT_TAB(i).attribute_category ||'|'||L_CHANGE_ATT_TAB(i).changed_attribute||'|'||L_CHANGE_ATT_TAB(i).field_name;
5136 end if;
5137 end if;
5138 END IF;
5139
5140 END LOOP;
5141
5142 END IF;
5143
5144
5145 /* if (l_tbb_deleted ) then
5146 open csr_get_mapping_name('attribute1');
5147 fetch csr_get_mapping_name into l_build_change_list;
5148 close csr_get_mapping_name;
5149 end if;*/
5150
5151 IF (l_build_change_list IS NOT NULL)
5152 THEN
5153 add_error_to_table (
5154 p_message_table=> p_message_table,
5155 p_message_name=> 'HXC_AUDIT_MSG',
5156 p_message_token=> 'CHANGE',
5157 p_message_level=> p_message_level,
5158 p_message_field=> substr(l_build_change_list,1,2000),
5159 p_timecard_bb_id=> p_tco_bb (l_tbb_index).time_building_block_id,
5160 p_time_attribute_id=> NULL,
5161 p_timecard_bb_ovn=> p_tco_bb (l_tbb_index).object_version_number,
5162 p_time_attribute_ovn=> NULL
5163 );
5164 END IF;
5165
5166 l_tbb_index := p_tco_bb.NEXT (l_tbb_index);
5167 END LOOP;
5168
5169 END execute_change_ter;
5170
5171 PROCEDURE execute_late_ter (
5172 p_tco_bb IN hxc_self_service_time_deposit.timecard_info,
5173 p_tco_att IN hxc_self_service_time_deposit.building_block_attribute_info,
5174 p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
5175 p_message_level VARCHAR2,
5176 p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%ROWTYPE
5177 )
5178 IS
5179 l_time_category_id hxc_time_categories.time_category_id%TYPE;
5180 l_tbb_index NUMBER;
5181 l_tbb_parent_index NUMBER;
5182 l_date_worked DATE;
5183 n NUMBER;
5184 BEGIN
5185
5186
5187
5188 l_time_category_id := p_rule_record.attribute1;
5189
5190 IF (l_time_category_id IS NOT NULL)
5191 THEN
5192 hxc_time_category_utils_pkg.initialise_time_category (
5193 p_time_category_id=> TO_NUMBER (l_time_category_id),
5194 p_tco_att=> p_tco_att
5195 );
5196 l_tbb_index := p_tco_bb.FIRST;
5197 if g_debug then
5198 hr_utility.TRACE ('Outside Loop');
5199 end if;
5200
5201 LOOP
5202 EXIT WHEN NOT p_tco_bb.EXISTS (l_tbb_index);
5203
5204 IF ( p_tco_bb (l_tbb_index).SCOPE = 'DETAIL'
5205 AND p_tco_bb (l_tbb_index).new = 'Y'
5206 -- Bug 2958441
5207 AND p_tco_bb (l_tbb_index).date_to = hr_general.end_of_time
5208 -- Bug 2958441
5209 )
5210 THEN
5211 l_date_worked := NULL;
5212 If (p_tco_bb (l_tbb_index).stop_time is null) then
5213 l_tbb_parent_index := p_tco_bb.first;
5214 LOOP
5215 exit when not p_tco_bb.exists(l_tbb_parent_index);
5216 if (p_tco_bb(l_tbb_parent_index).time_building_block_id = p_tco_bb(l_tbb_index).parent_building_block_id )
5217 THEN
5218 l_date_worked := p_tco_bb(l_tbb_parent_index).start_time;
5219 exit;
5220 end if;
5221 l_tbb_parent_index := p_tco_bb.next(l_tbb_parent_index);
5222 END LOOP;
5223 END IF;
5224
5225 IF (hxc_time_category_utils_pkg.chk_tc_bb_ok (
5226 p_tco_bb (l_tbb_index).time_building_block_id
5227 )
5228 )
5229 THEN
5230 IF (chk_bb_late (
5231 p_stop_time=> p_tco_bb (l_tbb_index).stop_time,
5232 p_st_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute2),
5233 p_qnt_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute3),
5234 p_date_worked => l_date_worked
5235 )
5236 )
5237 THEN
5238 hxc_time_entry_rules_utils_pkg.add_error_to_table (
5239 p_message_table=> p_messages,
5240 p_message_name=> 'HXC_AUDIT_MSG',
5241 p_message_token=> 'LATE',
5242 p_message_level=> p_message_level,
5243 p_message_field=> NULL,
5244 p_timecard_bb_id=> p_tco_bb (l_tbb_index).time_building_block_id,
5245 p_time_attribute_id=> NULL,
5246 p_timecard_bb_ovn=> p_tco_bb (l_tbb_index).object_version_number,
5247 p_time_attribute_ovn=> NULL
5248 );
5249 END IF;
5250 END IF;
5251 END IF;
5252
5253 l_tbb_index := p_tco_bb.NEXT (l_tbb_index);
5254 END LOOP;
5255 ELSE
5256 l_tbb_index := p_tco_bb.FIRST;
5257
5258 LOOP
5259 EXIT WHEN NOT p_tco_bb.EXISTS (l_tbb_index);
5260
5261 IF ( p_tco_bb (l_tbb_index).SCOPE = 'DETAIL'
5262 AND p_tco_bb (l_tbb_index).new = 'Y'
5263 -- Bug 2958441
5264 AND p_tco_bb (l_tbb_index).date_to = hr_general.end_of_time
5265 -- Bug 2958441
5266 )
5267 THEN
5268 l_date_worked := NULL;
5269 If (p_tco_bb (l_tbb_index).stop_time is null) then
5270 l_tbb_parent_index := p_tco_bb.first;
5271 LOOP
5272 exit when not p_tco_bb.exists(l_tbb_parent_index);
5273 if (p_tco_bb(l_tbb_parent_index).time_building_block_id = p_tco_bb(l_tbb_index).parent_building_block_id )
5274 THEN
5275 l_date_worked := p_tco_bb(l_tbb_parent_index).start_time;
5276 exit;
5277 end if;
5278 l_tbb_parent_index := p_tco_bb.next(l_tbb_parent_index);
5279 END LOOP;
5280 END IF;
5281 IF (chk_bb_late (
5282 p_stop_time=> p_tco_bb (l_tbb_index).stop_time,
5283 p_st_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute2),
5284 p_qnt_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute3),
5285 p_date_worked => l_date_worked
5286 )
5287 )
5288 THEN
5289 hxc_time_entry_rules_utils_pkg.add_error_to_table (
5290 p_message_table=> p_messages,
5291 p_message_name=> 'HXC_AUDIT_MSG',
5292 p_message_token=> 'LATE' --p_rule_record.rule_outcome
5293 ,
5294 p_message_level=> p_message_level,
5295 p_message_field=> NULL,
5296 p_timecard_bb_id=> p_tco_bb (l_tbb_index).time_building_block_id,
5297 p_time_attribute_id=> NULL,
5298 p_timecard_bb_ovn=> p_tco_bb (l_tbb_index).object_version_number,
5299 p_time_attribute_ovn=> NULL
5300 );
5301 END IF;
5302 END IF;
5303
5304 l_tbb_index := p_tco_bb.NEXT (l_tbb_index);
5305 END LOOP;
5306 END IF;
5307
5308 if g_debug then
5309 hr_utility.TRACE ('CLA Lateeeeeeeeeeeeeee Message Table');
5310 hr_utility.trace('Count ' || p_messages.count);
5311 end if;
5312 n := p_messages.FIRST;
5313
5314 LOOP
5315 EXIT WHEN NOT p_messages.EXISTS (n);
5316
5317 IF (p_messages (n).message_level = 'REASON')
5318 THEN
5319 if g_debug then
5320 hr_utility.TRACE ('Time Building Block Id' || 'Time Building Block OVN' || 'message_name');
5321 hr_utility.trace(p_messages (n).time_building_block_id || ' ' || p_messages (n).time_building_block_ovn || ' ' || p_messages (n).message_name);
5322 end if;
5323 END IF;
5324 n := p_messages.NEXT (n);
5325 END LOOP;
5326 if g_debug then
5327 hr_utility.trace('End Lateeeeeeeeeeeeeee');
5328 end if;
5329 END execute_late_ter;
5330 Begin
5331
5332 g_debug := hr_utility.debug_enabled;
5333
5334 n:= p_time_building_blocks.first;
5335 loop
5336 exit when not p_time_building_blocks.exists(n);
5337 if g_debug then
5338 hr_utility.trace('ID ' || 'OVN' || 'SCOPE' || 'Changed' || 'New' || ' ' || 'Date to');
5339 hr_utility.trace(p_time_building_blocks(n).time_building_block_id||' ' || p_time_building_blocks(n).object_version_number|| ' ' || p_time_building_blocks(n).scope ||' '
5340 || p_time_building_blocks(n).changed|| ' ' ||p_time_building_blocks(n).new || to_char(p_time_building_blocks(n).date_to,'dd-mon-rrrr'));
5341 end if;
5342 n := p_time_building_blocks.next(n);
5343 end loop;
5344 get_timecard_info (
5345 p_time_building_blocks => P_TIME_BUILDING_BLOCKS
5346 , p_timecard_rec => l_timecard_info_rec );
5347 if g_debug then
5348 hr_utility.trace('After get_timecard_info');
5349 hr_utility.trace('start_date ' || l_timecard_info_rec.start_date);
5350 end if;
5351
5352 n := p_messages.first;
5353 loop
5354 exit when not p_messages.exists(n);
5355 if (p_messages(n).message_level = 'ERROR') then
5356 return;
5357 end if;
5358 n := p_messages.next(n);
5359 end loop;
5360 /* Start 2944785
5361 -- GPM v115.55 / v115.56
5362 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
5363 p_preference_code => 'TS_PER_AUDIT_REQUIREMENTS',
5364 p_start_evaluation_date => l_timecard_info_rec.start_date,
5365 p_end_evaluation_date => l_timecard_info_rec.end_date,
5366 p_sorted_pref_table => l_prefs );
5367
5368 IF ( l_prefs.COUNT > 1 )
5369 THEN
5370 -- error since cannot have more than one CLA preference in timecard period
5371 -- in the interim take the first available value
5372 l_terg_id := l_prefs(1).attribute1;
5373 ELSE
5374 l_terg_id := l_prefs(1).attribute1;
5375 END IF;
5376 */
5377 l_terg_id := P_TIME_ENTRY_RULE_GROUP_ID;
5378 -- GPM v115.55
5379 -- End 2944785
5380 FOR r_rules IN csr_get_rules ( p_terg_id => l_terg_id
5381 , p_start_date => l_timecard_info_rec.start_date
5382 , p_end_date => l_timecard_info_rec.end_date )
5383 LOOP
5384 IF (r_rules.formula_name = 'HXC_CLA_CHANGE_FORMULA')
5385 THEN
5386 execute_change_ter (
5387 p_tco_bb=> P_TIME_BUILDING_BLOCKS,
5388 p_tco_att=> p_time_attributes,
5389 p_message_table=> p_messages,
5390 p_message_level=> r_rules.rule_outcome,
5391 p_rule_record=> r_rules
5392 );
5393 ELSIF (r_rules.formula_name = 'HXC_CLA_LATE_FORMULA')
5394 THEN
5395 execute_late_ter (
5396 p_tco_bb=> P_TIME_BUILDING_BLOCKS,
5397 p_tco_att=> p_time_attributes,
5398 p_message_table=> p_messages,
5399 p_message_level=> r_rules.rule_outcome,
5400 p_rule_record=> r_rules
5401 );
5402 END IF;
5403 end loop;
5404
5405
5406 if g_debug then
5407 hr_utility.trace('Final Message Table -- Late + Change......');
5408 end if;
5409 n := p_messages.first;
5410 loop
5411 exit when not p_messages.exists(n);
5412 if (p_messages(n).message_level = 'REASON') then
5413 if g_debug then
5414 hr_utility.trace('Time Building Block Id' || p_messages(n).time_building_block_id);
5415 hr_utility.trace('Time Building Block OVN' || p_messages(n).time_building_block_ovn);
5416 hr_utility.trace('Message name' || p_messages(n).message_name);
5417 hr_utility.trace('Message Level' || p_messages(n).message_level);
5418 hr_utility.trace('Message Tokens' || p_messages(n).message_tokens);
5419 hr_utility.trace('Field name' || p_messages(n).message_field);
5420 hr_utility.trace('Recipient Application ID' || p_messages(n).message_name);
5421 end if;
5422 end if;
5423 n := p_messages.next(n);
5424 end loop;
5425
5426 if g_debug then
5427 hr_utility.trace('End of execute CLA time entry rules');
5428 end if;
5429
5430 end EXECUTE_CLA_TIME_ENTRY_RULES;
5431
5432 PROCEDURE GET_PROMPTS (p_block_id IN NUMBER,
5433 p_blk_ovn IN NUMBER,
5434 p_attribute IN VARCHAR2,
5435 p_blk_type IN VARCHAR2,
5436 p_prompt IN OUT NOCOPY VARCHAR2)
5437 IS
5438 CURSOR C_GET_PROMPT_NAME(p_attribute VARCHAR2) IS
5439 SELECT substr(fcu.form_left_prompt,1,30) prompt
5440 FROM hxc_mapping_components mc
5441 ,hxc_bld_blk_info_types bbit
5442 ,fnd_descr_flex_col_usage_tl fcu
5443 ,hxc_time_attributes hta
5444 WHERE mc.SEGMENT= UPPER(p_attribute) --mapping_component_id = p_comp_id
5445 AND mc.bld_blk_info_type_id = hta.bld_blk_info_type_id
5446 AND hta.time_attribute_id = p_block_id
5447 AND hta.object_version_number = p_blk_ovn
5448 AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
5449 AND fcu.application_column_name = mc.segment
5450 AND fcu.descriptive_flex_context_code = bbit.bld_blk_info_type
5451 AND fcu.descriptive_flexfield_name = 'OTC Information Types'
5452 AND fcu.application_id = 809
5453 AND fcu.language = userenv('LANG');
5454
5455 CURSOR C_GET_PROMPT_BLK(p_attribute VARCHAR2) is
5456 SELECT hmc.field_name
5457 FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
5458 WHERE hmc.segment = upper(p_attribute)
5459 AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
5460 AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';
5461 l_prompt_name varchar2(30);
5462 Begin
5463 g_debug := hr_utility.debug_enabled;
5464
5465 if (p_blk_type = 'BUILDING_BLOCK' ) then
5466 open c_get_prompt_blk(p_attribute);
5467 fetch c_get_prompt_blk into l_prompt_name;
5468 if g_debug then
5469 hr_utility.trace('Prompt ' || l_prompt_name);
5470 end if;
5471 if c_get_prompt_blk%FOUND then
5472 p_prompt := l_prompt_name;
5473 end if;
5474 close c_get_prompt_blk;
5475 else
5476 open c_get_prompt_name (p_attribute);
5477 fetch c_get_prompt_name into l_prompt_name;
5478 if c_get_prompt_name%FOUND then
5479 p_prompt := l_prompt_name;
5480 end if;
5481 close c_get_prompt_name;
5482 end if;
5483 END GET_PROMPTS;
5484
5485
5486
5487 PROCEDURE publish_message (
5488 p_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
5489 , p_message_level in VARCHAR2 DEFAULT 'ERROR'
5490 , p_token_name in VARCHAR2 DEFAULT NULL
5491 , p_token_value in VARCHAR2 DEFAULT NULL
5492 , p_application_short_name IN VARCHAR2 default 'HXC'
5493 , p_time_building_block_id in NUMBER
5494 , p_time_attribute_id in NUMBER DEFAULT NULL
5495 , p_message_extent in VARCHAR2 DEFAULT NULL ) IS
5496
5497
5498 l_message_table hxc_message_table_type := hxc_message_table_type();
5499
5500 l_ind PLS_INTEGER;
5501
5502 l_token_string VARCHAR2(4000) := NULL;
5503
5504 BEGIN
5505
5506 g_debug := hr_utility.debug_enabled;
5507
5508 IF ( p_token_name is not null )
5509 THEN
5510
5511 if g_debug then
5512 hr_utility.trace('GAZ token is '||p_token_name);
5513 hr_utility.trace('GAZ length P token value is '||to_char(length(p_token_value)));
5514 end if;
5515
5516 l_token_string := SUBSTR(UPPER(p_token_name)||'&'||p_token_Value,1,4000);
5517
5518 if g_debug then
5519 hr_utility.trace('GAZ length token string is '||to_char(length(l_token_string)));
5520 end if;
5521
5522 END IF;
5523
5524 publish_message (
5525 p_name => p_name
5526 , p_message_level => p_message_level
5527 , p_token_string => l_token_string
5528 , p_application_short_name => p_application_short_name
5529 , p_time_building_block_id => p_time_building_block_id
5530 , p_time_attribute_id => p_time_attribute_id
5531 , p_message_extent => p_message_extent );
5532
5533 END publish_message;
5534
5535
5536 PROCEDURE publish_message (
5537 p_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
5538 , p_message_level in VARCHAR2 DEFAULT 'ERROR'
5539 , p_token_string in VARCHAR2 DEFAULT NULL
5540 , p_application_short_name IN VARCHAR2 default 'HXC'
5541 , p_time_building_block_id in NUMBER
5542 , p_time_attribute_id in NUMBER DEFAULT NULL
5543 , p_message_extent in VARCHAR2 DEFAULT NULL ) IS
5544
5545
5546
5547 l_message_table hxc_message_table_type := hxc_message_table_type();
5548
5549 l_ind PLS_INTEGER;
5550
5551 l_token_string VARCHAR2(4000) := NULL;
5552
5553 BEGIN
5554
5555 IF ( p_token_string is not null )
5556 THEN
5557
5558 l_token_string := SUBSTR(p_token_string,1,4000);
5559
5560 END IF;
5561
5562 l_message_table.extend;
5563
5564 l_ind := l_message_table.last;
5565
5566 l_message_table(l_ind) := hxc_message_type( p_name,
5567 p_message_level,
5568 NULL,
5569 l_token_string,
5570 p_application_short_name,
5571 p_time_building_block_id,
5572 NULL,
5573 p_time_attribute_id,
5574 NULL,
5575 p_message_extent );
5576
5577 hxc_timecard_message_helper.processErrors(p_messages => l_message_table);
5578
5579 END publish_message;
5580
5581 function return_archived_status(p_period in r_period)
5582 return boolean is
5583
5584
5585 cursor csr_status(p_start_date date,p_end_date date) is
5586 select 'Y' from hxc_data_sets
5587 where status in ('OFF_LINE','BACKUP_IN_PROGRESS','RESTORE_IN_PROGRESS')
5588 and trunc(p_start_date) <=end_date
5589 and trunc(p_end_date) >=start_date;
5590
5591 l_dummy varchar2(1);
5592 l_period varchar2(100);
5593
5594 begin
5595
5596 open csr_status(p_period.period_start,p_period.period_end);
5597 fetch csr_status into l_dummy;
5598 if(csr_status%found) then
5599 close csr_status;
5600 return true;
5601 end if;
5602
5603 close csr_status;
5604
5605 if(p_period.db_pre_period_start is not null and p_period.db_pre_period_end is not null) then
5606 open csr_status(p_period.db_pre_period_start,p_period.db_pre_period_end);
5607 fetch csr_status into l_dummy;
5608 if(csr_status%found) then
5609 close csr_status;
5610 return true;
5611 else
5612 close csr_status;
5613 end if;
5614 end if;
5615
5616
5617 if(p_period.db_ref_period_start is not null and p_period.db_ref_period_end is not null) then
5618 open csr_status(p_period.db_ref_period_start,p_period.db_ref_period_end);
5619 fetch csr_status into l_dummy;
5620 if(csr_status%found) then
5621 close csr_status;
5622 return true;
5623 else
5624 close csr_status;
5625 end if;
5626 end if;
5627
5628 return false;
5629
5630 end return_archived_status;
5631
5632 function check_valid_calc_date_accrual(p_resource_id NUMBER, p_calculate_date DATE) return varchar2
5633 is
5634
5635 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
5636 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
5637
5638 l_emp_hire_date date;
5639
5640 begin
5641
5642 OPEN emp_hire_info (p_resource_id);
5643 FETCH emp_hire_info into l_emp_hire_date;
5644 CLOSE emp_hire_info;
5645
5646 if trunc(l_emp_hire_date) >= trunc(p_calculate_date)
5647 then
5648 return 'N';
5649 else
5650 return 'Y';
5651 end if;
5652
5653 end check_valid_calc_date_accrual;
5654
5655 end hxc_time_entry_rules_utils_pkg;