[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.15.12010000.5 2008/12/29 14:30:45 asrajago 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 FOR l_count IN l_outputs.FIRST .. l_outputs.LAST
1370 LOOP
1371
1372 IF ( l_outputs(l_count).name = 'RULE_STATUS' )
1373 THEN
1374
1375 l_result := l_outputs(l_count).value;
1376
1377 -- since approval formulas can potentially also be used
1378 -- in time entry rules translate the approval return
1379 -- value to a value the time entry rule code can
1380 -- understand. TO_APPROVE=Y is an exception in the
1381 -- approval world...
1382
1383 -- GPaytonM 115.12
1384
1385 ELSIF ( l_outputs(l_count).name = 'TO_APPROVE' )
1386 THEN
1387
1388 IF ( l_outputs(l_count).value = 'Y' )
1389 THEN
1390 l_result := 'E';
1391 ELSE
1392 l_result := 'S';
1393 END IF;
1394
1395 ELSIF ( l_outputs(l_count).name like 'MESSAGE%' )
1396 THEN
1397
1398 l_cnt := SUBSTR(l_outputs(l_count).name, LENGTH('MESSAGE')+1);
1399
1400 process_message (
1401 p_output_name => 'MESSAGE'
1402 , p_output_value => l_outputs(l_count).value
1403 , p_output_number => l_cnt
1404 , p_message_table => l_message_table
1405 , p_rule_record => p_rule_record );
1406
1407 ELSIF ( l_outputs(l_count).name like 'TOKEN_VALUE%' )
1408 THEN
1409
1410 l_cnt := SUBSTR(l_outputs(l_count).name, LENGTH('TOKEN_VALUE')+1);
1411
1412 process_message (
1413 p_output_name => 'TOKEN_VALUE'
1414 , p_output_value => l_outputs(l_count).value
1415 , p_output_number => l_cnt
1416 , p_message_table => l_message_table
1417 , p_rule_record => p_rule_record );
1418
1419 ELSIF ( l_outputs(l_count).name like 'TOKEN_NAME%' )
1420 THEN
1421
1422 l_cnt := SUBSTR(l_outputs(l_count).name, LENGTH('TOKEN_NAME')+1);
1423
1424 process_message (
1425 p_output_name => 'TOKEN_NAME'
1426 , p_output_value => l_outputs(l_count).value
1427 , p_output_number => l_cnt
1428 , p_message_table => l_message_table
1429 , p_rule_record => p_rule_record );
1430
1431 END IF;
1432
1433 END LOOP; -- formula outputs loop
1434
1435 if g_debug then
1436 hr_utility.set_location('Processing '||l_proc, 70);
1437 end if;
1438
1439 -- populate message table
1440
1441 -- GPM v115.4
1442
1443 IF ( ( l_result = 'E' ) AND ( l_message_table.COUNT <> 0 ) )
1444 THEN
1445
1446 if g_debug then
1447 hr_utility.set_location('Processing '||l_proc, 80);
1448 end if;
1449
1450 l_message_count := l_message_table.FIRST;
1451
1452 WHILE l_message_count IS NOT NULL
1453 LOOP
1454
1455 if g_debug then
1456 hr_utility.set_location('Processing '||l_proc, 90);
1457 end if;
1458
1459 IF ( l_message_table(l_message_count).name IS NOT NULL )
1460 THEN
1461
1462
1463 IF ( l_message_table(l_message_count).token_name is not null )
1464 THEN
1465 l_token_string := SUBSTR(UPPER(l_message_table(l_message_count).token_name)
1466 ||'&'|| l_message_table(l_message_count).token_value ,1,4000);
1467 END IF;
1468
1469 add_error_to_table (
1470 p_message_table => p_message_table
1471 , p_message_name => l_message_table(l_message_count).name
1472 , p_message_token => l_token_string
1473 , p_message_level => p_message_level
1474 , p_message_field => NULL
1475 , p_timecard_bb_id => p_timecard_info.timecard_bb_id
1476 , p_time_attribute_id => NULL
1477 , p_timecard_bb_ovn => p_timecard_info.timecard_ovn
1478 , p_time_attribute_ovn => NULL
1479 , p_message_extent =>l_message_table(l_message_count).extent); --Bug#2873563
1480 END IF;
1481
1482 l_message_count := l_message_table.NEXT(l_message_count);
1483
1484 END LOOP;
1485
1486 l_message_table.DELETE; ----Bug#3090409
1487 END IF;
1488
1489 END IF; -- l_consider_zero_hours check
1490
1491 else
1492
1493 add_error_to_table (
1494 p_message_table => p_message_table
1495 ,p_message_name => 'HXC_ARCHIVE_TER_ERROR'
1496 ,p_message_token => 'TER_NAME&'||hxc_time_entry_rules_utils_pkg.g_ter_record.ter_message_name
1497 ,p_message_level => 'ERROR'
1498 ,p_message_field => NULL
1499 ,p_timecard_bb_id=> NULL
1500 ,p_time_attribute_id=> NULL
1501 ,p_timecard_bb_ovn => NULL
1502 ,p_time_attribute_ovn=> NULL
1503 ,p_message_extent =>hxc_timecard.c_blk_children_extent);
1504
1505 end if; -- hxc_time_entry_rules_utils_pkg.return_archived_status(l_period_tab(p)) = false )
1506
1507
1508 if g_debug then
1509 hr_utility.set_location('Processing '||l_proc, 100);
1510 end if;
1511
1512 END LOOP; -- t_periods
1513
1514 if g_debug then
1515 hr_utility.trace('After period loop');
1516 end if;
1517
1518 if g_debug then
1519 hr_utility.set_location('Processing '||l_proc, 110);
1520 end if;
1521
1522 -- reset variables
1523
1524 l_period_tab.delete;
1525
1526 END execute_formula;
1527
1528
1529 PROCEDURE check_time_overlaps
1530 (p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info
1531 ,p_messages IN OUT nocopy hxc_self_service_time_deposit.message_table) IS
1532
1533 l_bb_id NUMBER;
1534 l_bb_id_detail NUMBER;
1535 l_type VARCHAR2(30);
1536 l_type_detail VARCHAR2(30);
1537 l_start_time DATE;
1538 l_start_detail DATE;
1539 l_stop_time DATE;
1540 l_stop_detail DATE;
1541 l_scope VARCHAR2(30);
1542 l_scope_detail VARCHAR2(30);
1543 l_date_to DATE;
1544 l_date_to_detail DATE;
1545 -- vars for used for detecting potential db overlaps.
1546 l_start_day_period DATE;
1547 l_end_day_period DATE;
1548 l_earliest_tc DATE;
1549 l_latest_tc DATE;
1550 l_resource_id NUMBER;
1551 l_cnt NUMBER;
1552 l_detail NUMBER;
1553 l_tc_db_overlap BOOLEAN;
1554
1555 l_detail_ovn number; --added for bug 2796204
1556
1557 l_timecardid number; --added for bug 2796204
1558 l_timecardovn number; --added for bug 2796204
1559
1560
1561 -- Start new code for Bug 2889097
1562
1563 TYPE t_left_overlap_row IS RECORD
1564 ( earliest_tc DATE,
1565 detail_id NUMBER,
1566 detail_ovn NUMBER);
1567
1568 TYPE t_left_overlap_t IS TABLE OF
1569 t_left_overlap_row
1570 INDEX BY BINARY_INTEGER;
1571
1572 l_left_overlap t_left_overlap_t;
1573
1574 l_detail_count number;
1575
1576 l_detailid_right number;
1577 l_detailovn_right number;
1578
1579 -- End new code for Bug 2889097
1580
1581 cursor range_details_of_day(p_day_date in DATE,p_resource_id in NUMBER)
1582 IS
1583 select tbbdet.start_time, tbbdet.stop_time
1584 from hxc_time_building_blocks tbbday,
1585 hxc_time_building_blocks tbbdet
1586 where
1587 tbbdet.scope = 'DETAIL'
1588 and tbbdet.type = 'RANGE'
1589 and tbbday.scope = 'DAY'
1590 and tbbdet.parent_building_block_id = tbbday.time_building_block_id
1591 and tbbdet.parent_building_block_ovn = tbbday.object_version_number
1592 and trunc(tbbday.start_time)=trunc(p_day_date)
1593 and tbbdet.resource_id = l_resource_id
1594 and tbbday.resource_id = l_resource_id
1595 and tbbdet.date_to = hr_general.end_of_time;
1596
1597
1598
1599 BEGIN
1600
1601
1602 l_earliest_tc := hr_general.end_of_time;
1603
1604 l_latest_tc := hr_general.start_of_time;
1605
1606 l_timecardid:=null; --added for bug 2796204
1607 l_timecardovn:=null; --added for bug 2796204
1608
1609 -- Start new code for Bug 2889097
1610
1611 l_detail_count := 0;
1612
1613 l_detailid_right := NULL;
1614
1615 l_detailovn_right := NULL;
1616
1617 -- End new code for Bug 2889097
1618
1619 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1620 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECK_TIME_OVERLAPS',
1621 'Number of BBs '||p_time_building_blocks.count);
1622 end if;
1623
1624 l_cnt := p_time_building_blocks.first ;
1625 LOOP
1626 EXIT WHEN NOT p_time_building_blocks.EXISTS(l_cnt);
1627
1628 l_bb_id := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1629 l_type := p_time_building_blocks(l_cnt).TYPE;
1630 l_start_time := p_time_building_blocks(l_cnt).START_TIME;
1631 l_stop_time := p_time_building_blocks(l_cnt).STOP_TIME;
1632 l_scope := p_time_building_blocks(l_cnt).SCOPE;
1633 l_resource_id := p_time_building_blocks(l_cnt).RESOURCE_ID;
1634 l_date_to := p_time_building_blocks(l_cnt).DATE_TO;
1635
1636 -- Record the period start / end
1637 IF (l_scope = 'TIMECARD') THEN
1638 l_timecardid :=p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID; --added for bug 2796204
1639 l_timecardovn :=p_time_building_blocks(l_cnt).object_version_number; --added for bug 2796204
1640 l_start_day_period := l_start_time;
1641 l_end_day_period := l_stop_time;
1642 END IF;
1643
1644 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECK_TIME_OVERLAPS',
1646 'Outer Loop BB_ID '||l_bb_id);
1647 end if;
1648 --
1649 -- Check for Overlap Time for DETAIL blocks of type RANGE and start_time
1650 -- and stop_time not null and make sure they are not end-dated!
1651 --
1652 IF (l_scope = 'DETAIL' AND l_type = 'RANGE' AND
1653 l_start_time is NOT NULL AND l_stop_time is NOT NULL AND
1654 l_date_to = hr_general.end_of_time ) THEN
1655
1656 --
1657 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1658 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1659 'Outer is a DETAIL RANGE');
1660 end if;
1661
1662 -- Keep track of earliest / latest building block start / stop times
1663
1664 -- Start commented code for Bug 2889097
1665 -- IF( l_start_time < l_earliest_tc) THEN
1666 -- End commented code for Bug 2889097
1667
1668 -- Start new code for Bug 2889097
1669 -- Trap all the Timecards as on earliest day of timecard period.
1670 IF( TRUNC(l_start_time) <= TRUNC(l_earliest_tc) ) THEN
1671 -- End new code for Bug 2889097
1672 l_earliest_tc := l_start_time;
1673
1674 -- Start new code for Bug 2889097
1675 l_left_overlap(l_detail_count).earliest_tc := l_start_time;
1676 l_left_overlap(l_detail_count).detail_id := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1677 l_left_overlap(l_detail_count).detail_ovn := p_time_building_blocks(l_cnt).object_version_number;
1678 l_detail_count := l_detail_count + 1;
1679 -- End new code for Bug 2889097
1680 END IF;
1681
1682 IF( l_stop_time > l_latest_tc) THEN
1683 l_latest_tc := l_stop_time;
1684 -- Start new code for Bug 2889097
1685 l_detailid_right := p_time_building_blocks(l_cnt).TIME_BUILDING_BLOCK_ID;
1686 l_detailovn_right := p_time_building_blocks(l_cnt).object_version_number;
1687 -- End new code for Bug 2889097
1688 END IF;
1689
1690 l_detail := l_cnt;
1691 LOOP
1692 EXIT WHEN NOT p_time_building_blocks.EXISTS(l_detail);
1693
1694 l_bb_id_detail := p_time_building_blocks(l_detail).time_building_block_id;
1695 l_type_detail := p_time_building_blocks(l_detail).TYPE;
1696 l_start_detail := p_time_building_blocks(l_detail).START_TIME;
1697 l_stop_detail := p_time_building_blocks(l_detail).STOP_TIME;
1698 l_scope_detail := p_time_building_blocks(l_detail).SCOPE;
1699 l_date_to_detail := p_time_building_blocks(l_detail).DATE_TO;
1700 l_detail_ovn := p_time_building_blocks(l_detail).OBJECT_VERSION_NUMBER; --added for bug 2796204
1701
1702 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1703 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1704 'Inner Loop BB_ID '||l_bb_id_detail);
1705 end if;
1706 --
1707 IF (l_scope_detail = 'DETAIL' AND l_type_detail = 'RANGE'
1708 AND l_start_detail is NOT NULL AND l_stop_detail is NOT NULL AND
1709 l_date_to_detail = hr_general.end_of_time ) THEN
1710 --
1711 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1712 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1713 'Inner is a DETAIL RANGE');
1714 end if;
1715
1716 IF (l_stop_time > l_start_detail AND
1717 l_start_time < l_stop_detail AND
1718 l_bb_id <> l_bb_id_detail)
1719 THEN
1720 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1721 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1722 'Overlap Detected');
1723 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1724 'Start BB Outer '||to_char(l_start_time,'DD-MON-YYYY:HH24:MI'));
1725 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1726 'Stop BB Outer '||to_char(l_stop_time,'DD-MON-YYYY:HH24:MI'));
1727 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1728 'Start BB Inner '||to_char(l_start_detail,'DD-MON-YYYY:HH24:MI'));
1729 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1730 'Stop BB Inner '||to_char(l_stop_detail,'DD-MON-YYYY:HH24:MI'));
1731 end if;
1732 --
1733 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1734 p_message_table => p_messages
1735 , p_message_name => 'HXT_39256_OVERLAPPING_TIME'
1736 , p_message_token => NULL
1737 , p_message_level => 'ERROR'
1738 , p_message_field => NULL
1739 , p_application_short_name => 'HXT'
1740 , p_timecard_bb_id => l_bb_id_detail --added for bug 2796204
1741 , p_time_attribute_id => NULL
1742 , p_timecard_bb_ovn => l_detail_ovn --added for bug 2796204
1743 , p_time_attribute_ovn => NULL );
1744
1745 --
1746 EXIT;
1747 --
1748 END IF;
1749 --
1750 END IF;
1751 --
1752 l_detail := p_time_building_blocks.NEXT(l_detail);
1753 END LOOP;
1754 --
1755 END IF;
1756 --
1757 l_cnt := p_time_building_blocks.NEXT(l_cnt);
1758 END LOOP;
1759
1760 -- we have checked for overlaps within the timecard. Now we need to check to
1761 -- if any of the db ranges overlap the ranges in the timecard due to graveyard type
1762 -- work patterns.
1763 -- We have also stored the start of the earliest DETAIL RANGE (l_earliest_tc) and the
1764 -- stop of the latest DETAIL RANGE (l_latest_tc)
1765
1766 -- We know the latest day and the earliest day in the timecard period.
1767 -- We make the assumption that only the day before the first day in the current period
1768 -- and the day after the last day in the period could have details that overlap.
1769
1770 l_tc_db_overlap := FALSE;
1771
1772 -- Start new code for Bug 2889097
1773 -- Check if the earliest day is infact the start day of Timecard period.
1774 If ( TRUNC(l_start_day_period) = TRUNC(l_earliest_tc) ) Then
1775 -- End new code for Bug 2889097
1776
1777 -- we pick up the details of the day before the first day in the current period
1778 FOR l_range_detail IN range_details_of_day(trunc(l_start_day_period-1),l_resource_id) LOOP
1779 -- Start new code for Bug 2889097
1780 l_detail_count := l_left_overlap.first;
1781 LOOP
1782 EXIT WHEN NOT l_left_overlap.exists(l_detail_count);
1783 -- End new code for Bug 2889097
1784
1785 -- Start commented code for Bug 2889097
1786 -- IF(l_range_detail.stop_time > l_earliest_tc) THEN
1787 -- End commented code for Bug 2889097
1788
1789 -- Start new code for Bug 2889097
1790 IF(l_range_detail.stop_time > l_left_overlap(l_detail_count).earliest_tc) THEN
1791 -- End new code for Bug 2889097
1792 l_tc_db_overlap :=TRUE;
1793
1794 -- Start new code for Bug 2889097
1795 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1796 p_message_table => p_messages
1797 , p_message_name => 'HXC_OVRLPPNG_TIME_TC_V_DB'
1798 , p_message_token => NULL
1799 , p_message_level => 'ERROR'
1800 , p_message_field => NULL
1801 , p_application_short_name => 'HXC'
1802 , p_timecard_bb_id => l_left_overlap(l_detail_count).detail_id
1803 , p_time_attribute_id => NULL
1804 , p_timecard_bb_ovn => l_left_overlap(l_detail_count).detail_ovn
1805 , p_time_attribute_ovn => NULL );
1806 -- End new code for Bug 2889097
1807
1808 END IF;
1809
1810 -- Start new code for Bug 2889097
1811 l_detail_count := l_left_overlap.next(l_detail_count);
1812 END Loop;
1813 -- End new code for Bug 2889097
1814 END LOOP;
1815 -- Start new code for Bug 2889097
1816 End If;
1817 -- End new code for Bug 2889097
1818
1819 -- pick up the details of the day after the last day in the current period
1820
1821 FOR l_range_detail IN range_details_of_day(trunc(l_end_day_period+1),l_resource_id) LOOP
1822 IF(l_range_detail.start_time < l_latest_tc ) THEN
1823 l_tc_db_overlap :=TRUE;
1824 -- Start new code for Bug 2889097
1825 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1826 p_message_table => p_messages
1827 , p_message_name => 'HXC_OVRLPPNG_TIME_TC_V_DB'
1828 , p_message_token => NULL
1829 , p_message_level => 'ERROR'
1830 , p_message_field => NULL
1831 , p_application_short_name => 'HXC'
1832 , p_timecard_bb_id => l_detailid_right
1833 , p_time_attribute_id => NULL
1834 , p_timecard_bb_ovn => l_detailovn_right
1835 , p_time_attribute_ovn => NULL );
1836 -- End new code for Bug 2889097
1837 END IF;
1838 END LOOP;
1839
1840 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1841 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1842 'L_TC_EARLIEST:'||to_char(l_earliest_tc,'DD-MON:HH24:MI'));
1843 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1844 'L_TC_LATEST:'||to_char(l_latest_tc,'DD-MON:HH24:MI'));
1845 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1846 'DAY_BEFORE:'||to_char(trunc(l_start_day_period-1)));
1847 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_CHECKTIME_OVERLAPS',
1848 'DAY_AFTER:'||to_char(trunc(l_end_day_period+1)));
1849 end if;
1850
1851 -- Start commented code for Bug 2889097
1852 -- if we have found an overlap, add and error. Note that the error differs from
1853 -- the error raised if the overlap is detected within the timecard to help the user.
1854 -- Note also that we could give the user more information in the msgs such as
1855 -- times of overlapping ranges if this is required.
1856
1857 --IF (l_tc_db_overlap = TRUE) THEN
1858 -- hxc_time_entry_rules_utils_pkg.add_error_to_table (
1859 -- p_message_table => p_messages
1860 -- , p_message_name => 'HXC_OVRLPPNG_TIME_TC_V_DB'
1861 -- , p_message_token => NULL
1862 -- , p_message_level => 'ERROR'
1863 -- , p_message_field => NULL
1864 -- , p_application_short_name => 'HXC'
1865 -- , p_timecard_bb_id => l_timecardid --added for bug 2796204
1866 -- , p_time_attribute_id => NULL
1867 -- , p_timecard_bb_ovn => l_timecardovn --added for bug 2796204
1868 -- , p_time_attribute_ovn => NULL );
1869 --
1870 --END IF;
1871 -- End commented code for Bug 2889097
1872
1873 END check_time_overlaps;
1874
1875
1876 --
1877 -- ----------------------------------------------------------------------------
1878 -- |------------------------< execute_field_combo_rule >----------------------|
1879 -- ----------------------------------------------------------------------------
1880 --
1881 -- Description: executes rules which reference the two seeded field combination
1882 -- formulae and populates the global error table accordingly
1883 --
1884 --
1885 -- Prerequisites:
1886 --
1887 -- None
1888 --
1889 -- In Parameters:
1890 -- Name Reqd Type Description
1891 --
1892 -- p_formula_name Yes varchar2 formula name
1893 -- p_message_table Yes hxc_self_service_time_deposit.message_table
1894 -- p_message_level Yes varchar2 TER message level
1895 -- p_rule_record Yes hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
1896 -- p_tco_bb Yes hxc_self_service_time_deposit.timecard_info
1897 -- p_tco_att Yes hxc_self_service_time_deposit.building_block_attribute_info
1898 -- p_timecard_info Yes r_timecard_info Timecard Information
1899 --
1900 --
1901 -- Access Status:
1902 -- Public.
1903 --
1904
1905 PROCEDURE execute_field_combo_rule (
1906 p_formula_name varchar2
1907 , p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table
1908 , p_message_level varchar2
1909 , p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%rowtype
1910 , p_tco_bb hxc_self_service_time_deposit.timecard_info
1911 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info ) IS
1912
1913 l_proc VARCHAR2(72);
1914
1915 l_param_rec hxc_ff_dict.r_param;
1916
1917 l_period_id hxc_recurring_periods.recurring_period_id%TYPE;
1918 l_reference_period NUMBER(10);
1919 l_consider_zero_hours VARCHAR2(10);
1920
1921 l_tc_id_1 hxc_time_categories.time_category_id%TYPE;
1922 l_tc_id_2 hxc_time_categories.time_category_id%TYPE;
1923
1924 l_bb_ind BINARY_INTEGER;
1925
1926 TYPE r_tc1 IS RECORD ( match VARCHAR2(1) );
1927
1928 TYPE t_tc1 IS TABLE OF r_tc1 INDEX BY BINARY_INTEGER;
1929
1930 l_tc1_tab t_tc1;
1931
1932 BEGIN
1933
1934
1935
1936 if g_debug then
1937 l_proc := g_package||'execute_field_combo_rule';
1938 hr_utility.set_location('Processing '||l_proc, 10);
1939 end if;
1940
1941 hxc_ff_dict.decode_formula_segments (
1942 p_formula_name => p_formula_name
1943 , p_rule_rec => p_rule_record
1944 , p_param_rec => l_param_rec
1945 , p_period_value => l_period_id
1946 , p_reference_value => l_reference_period
1947 , p_consider_zero_hours => l_consider_zero_hours );
1948
1949 if g_debug then
1950 hr_utility.trace(' ************* Param values are.... ************ ');
1951 hr_utility.trace('');
1952 hr_utility.trace(' Rule name is '||p_rule_record.name);
1953 hr_utility.trace('');
1954 hr_utility.trace(' Time Category ID I is '||l_param_rec.param1_value);
1955 hr_utility.trace(' Time Category ID II is '||l_param_rec.param2_value);
1956 end if;
1957
1958 l_tc_id_1 := to_number(l_param_rec.param1_value);
1959 l_tc_id_2 := to_number(l_param_rec.param2_value);
1960
1961 if g_debug then
1962 hr_utility.set_location('Processing '||l_proc, 20);
1963 end if;
1964
1965 -- we always have to intialise the first time category so do it now
1966
1967 hxc_time_category_utils_pkg.initialise_time_category (
1968 p_time_category_id => l_tc_id_1
1969 , p_tco_att => p_tco_att );
1970
1971 if g_debug then
1972 hr_utility.set_location('Processing '||l_proc, 30);
1973 end if;
1974
1975 -- now process the first time category
1976
1977 l_bb_ind := p_tco_bb.FIRST;
1978
1979 WHILE l_bb_ind IS NOT NULL
1980 LOOP
1981
1982 IF ( p_tco_bb(l_bb_ind).scope = 'DETAIL' and
1983 p_tco_bb(l_bb_ind).date_to = hr_general.end_of_time ) --Fix for Bug#2943285
1984 THEN
1985
1986 IF ( NOT hxc_time_category_utils_pkg.chk_tc_bb_ok ( p_tco_bb(l_bb_ind).time_building_block_id ) )
1987 THEN
1988
1989 IF ( l_tc_id_2 IS NULL )
1990 THEN
1991
1992 hxc_time_entry_rules_utils_pkg.add_error_to_table (
1993 p_message_table => p_message_table
1994 , p_message_name => 'HXC_TER_VIOLATION' --'HXC_'||p_rule_record.name
1995 , p_message_token => 'TER&'|| p_rule_record.ter_message_name
1996 , p_message_level => p_message_level
1997 , p_message_field => NULL
1998 , p_timecard_bb_id => p_tco_bb(l_bb_ind).time_building_block_id
1999 , p_time_attribute_id => NULL
2000 , p_timecard_bb_ovn => p_tco_bb(l_bb_ind).object_version_number
2001 , p_time_attribute_ovn => NULL );
2002
2003 END IF;
2004
2005 ELSE
2006
2007 IF ( l_tc_id_2 IS NOT NULL )
2008 THEN
2009
2010 l_tc1_tab(p_tco_bb(l_bb_ind).time_building_block_id).match := 'Y';
2011
2012 END IF;
2013
2014 END IF;
2015
2016 END IF;
2017
2018 l_bb_ind := p_tco_bb.NEXT(l_bb_ind);
2019
2020 END LOOP;
2021
2022 IF ( l_tc_id_2 IS NOT NULL )
2023 THEN
2024
2025 hxc_time_category_utils_pkg.initialise_time_category (
2026 p_time_category_id => l_tc_id_2
2027 , p_tco_att => p_tco_att );
2028
2029 -- now process the second time category
2030
2031 l_bb_ind := p_tco_bb.FIRST;
2032
2033 WHILE l_bb_ind IS NOT NULL
2034 LOOP
2035
2036 IF ( p_tco_bb(l_bb_ind).scope = 'DETAIL' and
2037 p_tco_bb(l_bb_ind).date_to = hr_general.end_of_time ) --Fix for Bug#2943285
2038 THEN
2039
2040 IF ( hxc_time_category_utils_pkg.chk_tc_bb_ok ( p_tco_bb(l_bb_ind).time_building_block_id ) )
2041 THEN
2042
2043 -- since this building block matches the time category check to make sure that the bb
2044 -- did not match the first time category, if it did then raise an error
2045
2046 IF l_tc1_tab.EXISTS(p_tco_bb(l_bb_ind).time_building_block_id)
2047 THEN
2048
2049 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2050 p_message_table => p_message_table
2051 , p_message_name => 'HXC_TER_VIOLATION' --'HXC_'||p_rule_record.name
2052 , p_message_token => 'TER&'|| p_rule_record.ter_message_name
2053 , p_message_level => p_message_level
2054 , p_message_field => NULL
2055 , p_timecard_bb_id => p_tco_bb(l_bb_ind).time_building_block_id
2056 , p_time_attribute_id => NULL
2057 , p_timecard_bb_ovn => p_tco_bb(l_bb_ind).object_version_number
2058 , p_time_attribute_ovn => NULL );
2059
2060 END IF;
2061
2062 END IF;
2063
2064 END IF;
2065
2066 l_bb_ind := p_tco_bb.NEXT(l_bb_ind);
2067
2068 END LOOP;
2069
2070 END IF; -- ( l_tc_id_2 IS NOT NULL )
2071
2072 EXCEPTION WHEN OTHERS THEN
2073
2074 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2075 p_message_table => p_message_table
2076 , p_message_name => 'EXCEPTION'
2077 , p_message_token => NULL
2078 , p_message_level => p_message_level
2079 , p_message_field => NULL
2080 , p_timecard_bb_id => p_tco_bb(l_bb_ind).time_building_block_id
2081 , p_time_attribute_id => NULL
2082 , p_timecard_bb_ovn => p_tco_bb(l_bb_ind).object_version_number
2083 , p_time_attribute_ovn => NULL );
2084
2085 END execute_field_combo_rule;
2086
2087
2088 /*****************************************************************
2089 *
2090 * Main Procedure - execute time entry rules
2091 *
2092 *****************************************************************/
2093
2094 BEGIN -- execute_time_entry_rules
2095
2096 g_debug := hr_utility.debug_enabled;
2097
2098 if g_debug then
2099 l_proc := g_package||'execute_time_entry_rules';
2100 hr_utility.set_location('Processing '||l_proc, 10);
2101 end if;
2102
2103 get_timecard_info (
2104 p_time_building_blocks => p_time_building_blocks
2105 , p_timecard_rec => l_timecard_info_rec );
2106
2107 -- set submission date to be within valid assignment
2108
2109 -- GPM v115.69
2110
2111 IF ( hxc_time_entry_rules_utils_pkg.g_assignment_info.EXISTS ( l_timecard_info_Rec.resource_id ) )
2112 THEN
2113
2114 IF ( ( l_timecard_info_Rec.start_date =
2115 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).start_date )
2116 AND
2117 ( l_timecard_info_Rec.end_date =
2118 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).end_date ) )
2119 THEN
2120
2121 l_submission_date :=
2122 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).submission_date;
2123
2124 ELSE
2125
2126 -- overwrite cached assignment info since for a different timecard period
2127 -- this would only happen if the timecard period on a submission change
2128 -- i.e. if the cached value is used for a different timecard submission for the
2129 -- same user
2130
2131 set_global_asg_info ( l_timecard_info_Rec.resource_id
2132 , l_timecard_info_Rec.start_date
2133 , l_timecard_info_Rec.end_date );
2134
2135 l_submission_date :=
2136 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).submission_date;
2137
2138 END IF;
2139 ELSE
2140
2141 set_global_asg_info ( l_timecard_info_Rec.resource_id
2142 , l_timecard_info_Rec.start_date
2143 , l_timecard_info_Rec.end_date );
2144
2145 l_submission_date :=
2146 hxc_time_entry_rules_utils_pkg.g_assignment_info(l_timecard_info_Rec.resource_id).submission_date;
2147
2148 END IF;
2149
2150
2151 IF ( l_timecard_info_rec.resource_id = 13577 )
2152 THEN
2153
2154 null;
2155
2156
2157 END IF;
2158
2159 -- loop through the time entry rules based on the resource's
2160 -- preference and get the message level
2161
2162 if g_debug then
2163 hr_utility.set_location('Processing '||l_proc, 20);
2164 end if;
2165
2166 l_rules_evl := hxc_preference_evaluation.resource_preferences(
2167 p_resource_id => l_timecard_info_rec.resource_id
2168 ,p_pref_code => 'TC_W_RULES_EVALUATION'
2169 ,p_attribute_n => 1
2170 ,p_evaluation_date => l_submission_date );
2171
2172 IF l_rules_evl = 'Y'
2173 THEN
2174 check_time_overlaps (
2175 p_time_building_blocks => p_time_building_blocks
2176 , p_messages => p_messages );
2177
2178 END IF;
2179
2180 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
2181 p_preference_code => 'TS_PER_TIME_ENTRY_RULES',
2182 p_start_evaluation_date => l_timecard_info_rec.start_date,
2183 p_end_evaluation_date => l_timecard_info_rec.end_date,
2184 p_sorted_pref_table => l_pref_table,
2185 p_master_pref_table => p_master_pref_table );
2186
2187 l_terg_id := l_pref_table(1).attribute1 ;
2188
2189 if g_debug then
2190 hr_utility.set_location('Processing '||l_proc, 25);
2191 end if;
2192
2193 FOR r_rules IN csr_get_rules ( p_terg_id => l_terg_id
2194 , p_start_date => l_timecard_info_rec.start_date
2195 , p_end_date => l_timecard_info_rec.end_date )
2196 LOOP
2197 if g_debug then
2198 hr_utility.set_location('Processing '||l_proc, 30);
2199 end if;
2200
2201 -- GPM v115.61 2180942
2202
2203 if g_debug then
2204 hr_utility.trace('GAZ OPERATION IS '||p_operation);
2205 hr_utility.trace('GAZ RESUBMIT IS '||p_resubmit);
2206 end if;
2207
2208 -- maintain global record of TER info
2209
2210 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_name := r_rules.name;
2211 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_message_name := r_rules.ter_message_name;
2212 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_usage := r_rules.rule_usage;
2213 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_formula_name := NVL( r_rules.formula_name, 'NULL FORMULA');
2214 hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id := NULL;
2215
2216 IF ( ( p_operation = 'SAVE' AND r_rules.rule_usage = 'SAVE' ) OR
2217 ( p_operation = 'SUBMIT' AND p_resubmit = 'NO' AND r_rules.rule_usage = 'SUBMISSION' ) OR
2218 ( p_operation = 'SUBMIT' AND p_resubmit = 'YES' AND r_rules.rule_usage = 'RESUBMISSION' ) OR
2219 ( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'BOTH' ) OR
2220 ( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'DELETE_ONLY' AND l_timecard_info_rec.deleted = 'Y' ) OR
2221 ( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'BOTH_EX_DEL' AND l_timecard_info_rec.deleted = 'N' )
2222 )
2223 THEN
2224
2225 if g_debug then
2226 hr_utility.trace('');
2227 hr_utility.trace('********** Rule Name is '||r_rules.name||' **************');
2228 hr_utility.trace('');
2229 end if;
2230
2231 IF ( r_rules.formula_id IS NULL )
2232 THEN
2233 if g_debug then
2234 hr_utility.set_location('Processing '||l_proc, 40);
2235 end if;
2236 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2237 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'hxc_time_entry_rules', 'after 40');
2238 end if;
2239
2240 -- GPM v115.87
2241
2242 IF ( r_rules.name in ( 'Overlapping Time Entries', 'Overlapping Time Entries - Save') )
2243 AND l_rules_evl = 'N'
2244 THEN
2245
2246 -- still need to decide if we call the chk mapping changed
2247
2248 check_time_overlaps (
2249 p_time_building_blocks => p_time_building_blocks
2250 , p_messages => p_messages );
2251
2252 END IF;
2253
2254 -- in the case of wtd a rule without a formula is meaningless
2255 -- if the formula is null then we must have a mapping id but if we evaluate
2256 -- the mapping what do we do?
2257 --
2258 -- gaz - maybe need to look into this further or add formula_id is not null in csr
2259
2260 ELSIF ( r_rules.formula_id IS NOT NULL AND r_rules.mapping_id IS NOT NULL )
2261 THEN
2262 if g_debug then
2263 hr_utility.set_location('Processing '||l_proc, 50);
2264 end if;
2265
2266 IF ( hxc_mapping_utilities.chk_mapping_changed(
2267 p_mapping_id => r_rules.mapping_id
2268 ,p_timecard_bb_id => l_timecard_info_rec.timecard_bb_id
2269 ,p_timecard_ovn => l_timecard_info_rec.timecard_ovn
2270 ,p_start_date => l_timecard_info_rec.start_date
2271 ,p_end_date => l_timecard_info_rec.end_date
2272 ,p_last_status =>
2273 p_time_building_blocks(p_time_building_blocks.FIRST).approval_status
2274 ,p_time_building_blocks => p_time_building_blocks
2275 ,p_time_attributes => p_time_attributes
2276 ,p_called_from => 'TIME_ENTRY'
2277 ))
2278 THEN
2279 if g_debug then
2280 hr_utility.set_location('Processing '||l_proc, 60);
2281 end if;
2282
2283 execute_formula ( p_formula_name=> r_rules.formula_name
2284 , p_message_table => p_messages
2285 , p_message_level => r_rules.rule_outcome
2286 , p_rule_record => r_rules
2287 , p_tco_bb => p_time_building_blocks
2288 , p_tco_att => p_time_attributes
2289 , p_timecard_info => l_timecard_info_rec );
2290
2291 END IF;
2292
2293 ELSE -- basically means ( r_rules.formula_id IS NOT NULL AND r_rules.mapping_id IS NULL )
2294
2295 if g_debug then
2296 hr_utility.set_location('Processing '||l_proc, 70);
2297 end if;
2298
2299 -- special case for Field Combination rules
2300
2301 IF ( r_rules.formula_name in ( 'HXC_FIELD_COMBO_INCLUSIVE', 'HXC_FIELD_COMBO_EXCLUSIVE' ) )
2302 THEN
2303
2304 execute_field_combo_rule (
2305 p_formula_name => r_rules.formula_name
2306 , p_message_table => p_messages
2307 , p_message_level => r_rules.rule_outcome
2308 , p_rule_record => r_rules
2309 , p_tco_bb => p_time_building_blocks
2310 , p_tco_att => p_time_attributes );
2311
2312 ELSE
2313
2314 execute_formula ( p_formula_name=> r_rules.formula_name
2315 , p_message_table => p_messages
2316 , p_message_level => r_rules.rule_outcome
2317 , p_rule_record => r_rules
2318 , p_tco_bb => p_time_building_blocks
2319 , p_tco_att => p_time_attributes
2320 , p_timecard_info => l_timecard_info_rec );
2321
2322 END IF;
2323
2324 END IF;
2325
2326 END IF; -- p_operator / r_rules.rule_usage test
2327
2328
2329
2330 IF ( l_timecard_info_rec.resource_id = 13577 )
2331 THEN
2332
2333 null;
2334
2335
2336 END IF;
2337
2338
2339 END LOOP; -- csr_get_rules
2340
2341 if g_debug then
2342 hr_utility.set_location('Processing '||l_proc, 75);
2343 end if;
2344
2345 EXCEPTION WHEN OTHERS THEN
2346
2347 if g_debug then
2348 hr_utility.trace('SQLERRM is '||SQLERRM);
2349 end if;
2350
2351 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2352 p_message_table => p_messages
2353 , p_message_name => 'EXCEPTION'
2354 , p_message_token => NULL
2355 , p_message_level => 'ERROR'
2356 , p_message_field => NULL
2357 , p_timecard_bb_id => l_timecard_info_rec.timecard_bb_id
2358 , p_time_attribute_id => NULL
2359 , p_timecard_bb_ovn => l_timecard_info_rec.timecard_ovn
2360 , p_time_attribute_ovn => NULL );
2361
2362 IF ( l_timecard_info_rec.resource_id = 13577 )
2363 THEN
2364
2365 null;
2366
2367
2368 END IF;
2369
2370 END execute_time_entry_rules;
2371 --
2372
2373 FUNCTION period_maximum (
2374 p_resource_id NUMBER
2375 , p_submission_date VARCHAR2
2376 , p_period_maximum NUMBER
2377 , p_period NUMBER default 1
2378 , p_reference_period NUMBER default 1
2379 , p_pre_period_start VARCHAR2
2380 , p_pre_period_end VARCHAR2
2381 , p_post_period_start VARCHAR2 default null
2382 , p_post_period_end VARCHAR2 default null
2383 , p_ref_period_start VARCHAR2 default null
2384 , p_ref_period_end VARCHAR2 default null
2385 , p_duration_in_days NUMBER default 1
2386 , p_timecard_hrs NUMBER default 0 ) RETURN NUMBER IS
2387
2388 l_return NUMBER;
2389
2390 BEGIN
2391
2392 l_return := period_maximum (
2393 p_resource_id => p_resource_id
2394 , p_submission_date => p_submission_date
2395 , p_period_maximum => p_period_maximum
2396 , p_period => p_period
2397 , p_reference_period => p_reference_period
2398 , p_pre_period_start => p_pre_period_start
2399 , p_pre_period_end => p_pre_period_end
2400 , p_post_period_start => p_post_period_start
2401 , p_post_period_end => p_post_period_end
2402 , p_ref_period_start => p_ref_period_start
2403 , p_ref_period_end => p_ref_period_end
2404 , p_duration_in_days => p_duration_in_days
2405 , p_timecard_hrs => p_timecard_hrs
2406 , p_operator => NULL );
2407
2408 RETURN l_return;
2409
2410 END period_maximum;
2411
2412
2413 FUNCTION period_maximum (
2414 p_resource_id NUMBER
2415 , p_submission_date VARCHAR2
2416 , p_period_maximum NUMBER
2417 , p_period NUMBER default 1
2418 , p_reference_period NUMBER default 1
2419 , p_pre_period_start VARCHAR2
2420 , p_pre_period_end VARCHAR2
2421 , p_post_period_start VARCHAR2 default null
2422 , p_post_period_end VARCHAR2 default null
2423 , p_ref_period_start VARCHAR2 default null
2424 , p_ref_period_end VARCHAR2 default null
2425 , p_duration_in_days NUMBER default 1
2426 , p_timecard_hrs NUMBER default 0
2427 , p_operator VARCHAR2 ) RETURN NUMBER IS
2428
2429
2430 CURSOR csr_get_total_hrs ( p_start_date DATE, p_end_date DATE) IS
2431 SELECT NVL(SUM(SUM(NVL(hxc_time_category_utils_pkg.category_detail_hrs( tbb_detail.time_building_block_id,
2432 tbb_detail.object_version_number),0)) ),0)
2433 FROM
2434 hxc_timecard_summary ts,
2435 hxc_time_building_blocks tbb_day,
2436 hxc_time_building_blocks tbb_detail
2437 WHERE
2438 tbb_day.time_building_block_id = tbb_detail.parent_building_block_id and
2439 ts.timecard_id = tbb_day.parent_building_block_id and
2440 tbb_detail.scope='DETAIL' and
2441 tbb_detail.date_to=hr_general.end_of_time and
2442 tbb_day.scope='DAY' and
2443 tbb_day.type='RANGE' and
2444 tbb_day.date_to=hr_general.end_of_time and
2445 ts.resource_id = p_resource_id and
2446 to_date(to_char(tbb_day.start_time,
2447 'DD-MON-YYYY'),
2448 'DD-MON-YYYY') BETWEEN p_start_date AND p_end_date
2449 AND to_date(to_char(tbb_day.stop_time,
2450 'DD-MON-YYYY'),
2451 'DD-MON-YYYY') BETWEEN p_start_date AND p_end_date
2452 group by ts.timecard_id ;
2453
2454 l_submission_date DATE;
2455
2456 l_period_type hxc_recurring_periods.period_type%TYPE;
2457 l_duration_in_days hxc_recurring_periods.duration_in_days%TYPE;
2458
2459 l_return NUMBER;
2460
2461 l_total_hrs NUMBER := 0;
2462 l_hrs NUMBER := 0;
2463 l_ref_period_hrs NUMBER := 0;
2464 l_number_of_periods NUMBER(6,2);
2465
2466 l_old_tc_id NUMBER(15);
2467
2468 l_period_start DATE;
2469 l_period_start_date DATE;
2470 l_period_end_date DATE;
2471
2472 l_db_pre_period_start DATE;
2473 l_db_pre_period_end DATE;
2474 l_db_post_period_start DATE;
2475 l_db_post_period_end DATE;
2476 l_db_ref_period_start DATE;
2477 l_db_ref_period_end DATE;
2478
2479 l_proc VARCHAR2(72);
2480
2481 l_tc_bld_blks hxc_self_service_time_deposit.timecard_info;
2482 l_tc_ind binary_integer;
2483
2484
2485 BEGIN -- period_maximum
2486
2487 g_debug := hr_utility.debug_enabled;
2488
2489 if g_debug then
2490 l_proc := g_package||'period_maximum';
2491 hr_utility.set_location('Processing '||l_proc, 10);
2492
2493 hr_utility.trace('Lets see what is in the structure');
2494 end if;
2495
2496 l_tc_bld_blks := hxc_self_service_time_deposit.get_building_blocks;
2497
2498 l_tc_ind := l_tc_bld_blks.first;
2499
2500 while l_tc_ind is not null
2501 loop
2502
2503 if g_debug then
2504 hr_utility.trace('scope is '||l_tc_bld_blks(l_tc_ind).scope);
2505 hr_utility.trace('bb id is '||to_number(l_tc_bld_blks(l_tc_ind).time_building_Block_id));
2506 hr_utility.trace('new is '||l_tc_bld_blks(l_tc_ind).new);
2507 hr_utility.trace('changed is '||l_tc_bld_blks(l_tc_ind).changed);
2508 end if;
2509
2510 l_tc_ind := l_tc_bld_blks.NEXT(l_tc_ind);
2511
2512 end loop;
2513
2514 -- first convert dates to proper dates
2515
2516 l_submission_date := TO_DATE(p_submission_date, 'YYYY/MM/DD HH24:MI:SS');
2517
2518 IF ( p_pre_period_start <> ' ' )
2519 THEN
2520 l_db_pre_period_start := TO_DATE(p_pre_period_start, 'YYYY/MM/DD HH24:MI:SS');
2521 ELSE
2522 l_db_pre_period_start := NULL;
2523 END IF;
2524
2525 IF ( p_pre_period_end <> ' ' )
2526 THEN
2527 l_db_pre_period_end := TO_DATE(p_pre_period_end, 'YYYY/MM/DD HH24:MI:SS');
2528 ELSE
2529 l_db_pre_period_end := NULL;
2530 END IF;
2531
2532 IF ( p_post_period_start <> ' ' )
2533 THEN
2534 l_db_post_period_start := TO_DATE(p_post_period_start,'YYYY/MM/DD HH24:MI:SS');
2535 ELSE
2536 l_db_post_period_start := NULL;
2537 END IF;
2538
2539 IF ( p_post_period_end <> ' ' )
2540 THEN
2541 l_db_post_period_end := TO_DATE(p_post_period_end, 'YYYY/MM/DD HH24:MI:SS');
2542 ELSE
2543 l_db_post_period_end := NULL;
2544 END IF;
2545
2546 IF ( p_ref_period_start <> ' ' )
2547 THEN
2548 l_db_ref_period_start := TO_DATE(p_ref_period_start,'YYYY/MM/DD HH24:MI:SS');
2549 ELSE
2550 l_db_ref_period_start := NULL;
2551 END IF;
2552
2553 IF ( p_ref_period_end <> ' ' )
2554 THEN
2555 l_db_ref_period_end := TO_DATE(p_ref_period_end, 'YYYY/MM/DD HH24:MI:SS');
2556 ELSE
2557 l_db_ref_period_end := NULL;
2558 END IF;
2559
2560 -- remember p_period_start/end is the remainder of the period
2561 -- not included in the time card object which we must derive
2562 -- from the database.
2563
2564 IF ( l_db_pre_period_start IS NOT NULL AND l_db_post_period_start IS NOT NULL )
2565 THEN
2566 if g_debug then
2567 hr_utility.set_location('Processing '||l_proc, 20);
2568 end if;
2569
2570 -- now lets get the total hours worked based on the pre TC window
2571
2572 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2573 FETCH csr_get_total_hrs INTO l_hrs;
2574 CLOSE csr_get_total_hrs;
2575
2576 if g_debug then
2577 hr_utility.trace('');
2578 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2579 hr_utility.trace('');
2580 end if;
2581
2582 l_total_hrs := l_hrs;
2583
2584 l_hrs := 0;
2585
2586 -- now lets get the total hours worked based on the post TC window
2587
2588 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2589 FETCH csr_get_total_hrs INTO l_hrs;
2590 CLOSE csr_get_total_hrs;
2591
2592 if g_debug then
2593 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2594 hr_utility.trace('');
2595 end if;
2596
2597 l_total_hrs := l_total_hrs + l_hrs + p_timecard_hrs;
2598
2599 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
2600 THEN
2601
2602 -- calculate PTO INC hours
2603
2604 l_old_tc_id := hxc_time_category_utils_pkg.g_time_category_id;
2605 hxc_time_category_utils_pkg.g_time_category_id
2606 := hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id;
2607
2608 l_hrs := 0;
2609
2610 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2611 FETCH csr_get_total_hrs INTO l_hrs;
2612 CLOSE csr_get_total_hrs;
2613
2614 if g_debug then
2615 hr_utility.trace('');
2616 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2617 hr_utility.trace('');
2618 end if;
2619
2620 l_total_hrs := l_total_hrs - l_hrs;
2621
2622 -- now lets get the total hours worked based on the post TC window
2623
2624 l_hrs := 0;
2625
2626 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2627 FETCH csr_get_total_hrs INTO l_hrs;
2628 CLOSE csr_get_total_hrs;
2629
2630 if g_debug then
2631 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2632 hr_utility.trace('');
2633 end if;
2634
2635 l_total_hrs := l_total_hrs - l_hrs;
2636
2637 hxc_time_category_utils_pkg.g_time_category_id := l_old_tc_id;
2638
2639 END IF;
2640
2641 ELSIF ( l_db_pre_period_start IS NOT NULL )
2642 THEN
2643 if g_debug then
2644 hr_utility.set_location('Processing '||l_proc, 30);
2645 end if;
2646
2647 -- now lets get the total hours worked based on the pre TC window
2648
2649 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2650 FETCH csr_get_total_hrs INTO l_hrs;
2651 CLOSE csr_get_total_hrs;
2652
2653 if g_debug then
2654 hr_utility.trace('');
2655 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2656 hr_utility.trace('');
2657 end if;
2658
2659 l_total_hrs := l_total_hrs + l_hrs + p_timecard_hrs;
2660
2661 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
2662 THEN
2663
2664 -- calculate PTO INC hours
2665
2666 l_old_tc_id := hxc_time_category_utils_pkg.g_time_category_id;
2667 hxc_time_category_utils_pkg.g_time_category_id
2668 := hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id;
2669
2670 l_hrs := 0;
2671
2672 OPEN csr_get_total_hrs ( l_db_pre_period_start, l_db_pre_period_end );
2673 FETCH csr_get_total_hrs INTO l_hrs;
2674 CLOSE csr_get_total_hrs;
2675
2676 if g_debug then
2677 hr_utility.trace('');
2678 hr_utility.trace('Pre period hours are '||to_char(l_hrs));
2679 hr_utility.trace('');
2680 end if;
2681
2682 l_total_hrs := l_total_hrs - l_hrs;
2683
2684 hxc_time_category_utils_pkg.g_time_category_id := l_old_tc_id;
2685
2686 END IF;
2687
2688 ELSIF( l_db_post_period_start IS NOT NULL )
2689 THEN
2690 if g_debug then
2691 hr_utility.set_location('Processing '||l_proc, 40);
2692 end if;
2693
2694 -- now lets get the total hours worked based on the post TC window
2695
2696 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2697 FETCH csr_get_total_hrs INTO l_hrs;
2698 CLOSE csr_get_total_hrs;
2699
2700 if g_debug then
2701 hr_utility.trace('');
2702 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2703 hr_utility.trace('');
2704 end if;
2705
2706 l_total_hrs := l_hrs + p_timecard_hrs;
2707
2708 IF ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id IS NOT NULL )
2709 THEN
2710
2711 -- calculate PTO INC hours
2712
2713 l_old_tc_id := hxc_time_category_utils_pkg.g_time_category_id;
2714 hxc_time_category_utils_pkg.g_time_category_id
2715 := hxc_time_entry_rules_utils_pkg.g_ter_record.ter_inc_pto_plan_id;
2716
2717 l_hrs := 0;
2718
2719 OPEN csr_get_total_hrs ( l_db_post_period_start, l_db_post_period_end );
2720 FETCH csr_get_total_hrs INTO l_hrs;
2721 CLOSE csr_get_total_hrs;
2722
2723 if g_debug then
2724 hr_utility.trace('Post period hours are '||to_char(l_hrs));
2725 hr_utility.trace('');
2726 end if;
2727
2728 l_total_hrs := l_total_hrs - l_hrs;
2729
2730 hxc_time_category_utils_pkg.g_time_category_id := l_old_tc_id;
2731
2732 END IF;
2733
2734 ELSE
2735 if g_debug then
2736 hr_utility.set_location('Processing '||l_proc, 50);
2737 end if;
2738
2739 -- i.e. the whole time entry rule period is
2740 -- encompassed by the TCO
2741
2742 l_total_hrs := p_timecard_hrs;
2743
2744 END IF;
2745 if g_debug then
2746 hr_utility.set_location('Processing '||l_proc, 60);
2747 end if;
2748
2749 IF ( l_db_ref_period_start IS NOT NULL )
2750 THEN
2751
2752 -- now get the number of hours in the reference period
2753
2754 l_number_of_periods := ROUND( p_reference_period / p_duration_in_days, 2);
2755
2756 OPEN csr_get_total_hrs ( l_db_ref_period_start, l_db_ref_period_end );
2757 FETCH csr_get_total_hrs INTO l_ref_period_hrs;
2758 CLOSE csr_get_total_hrs;
2759
2760 l_total_hrs := (( l_total_hrs + l_ref_period_hrs ) / l_number_of_periods );
2761
2762 if g_debug then
2763 hr_utility.trace('');
2764 hr_utility.trace('************* reference period info ****************');
2765 hr_utility.trace('reference period start is '||TO_CHAR(l_db_ref_period_start, 'DD-MON-YY HH24:MI:SS'));
2766 hr_utility.trace('reference period end is '||TO_CHAR(l_db_ref_period_end , 'DD-MON-YY HH24:MI:SS'));
2767 hr_utility.trace('number of periods is '||TO_CHAR(l_number_of_periods));
2768 hr_utility.trace('ref period hours are '||to_char(l_ref_period_hrs));
2769 end if;
2770
2771 END IF;
2772
2773 if g_debug then
2774 hr_utility.trace('period maximum is '||to_char(p_period_maximum));
2775 hr_utility.trace('total hours are '||to_char(l_total_hrs));
2776 hr_utility.trace('');
2777 end if;
2778
2779 if g_debug then
2780 hr_utility.set_location('Processing '||l_proc, 70);
2781 end if;
2782
2783 -- WWB 3738796
2784 -- check to see if period max is being called from a TER which is using the seeded PTO formula
2785 -- In this case if the l_total_hrs are 0 then we should always return success since
2786 -- there are no hours which will be deducted from the accrual balance
2787
2788 IF ( ( hxc_time_entry_rules_utils_pkg.g_ter_record.ter_formula_name = 'HXC_PTO_ACCRUAL_COMPARISON' )
2789 AND
2790 ( l_total_hrs = 0 ) )
2791 THEN
2792
2793 l_return := 1;
2794 RETURN l_return;
2795
2796 ELSE
2797
2798 IF ( p_operator IS NULL OR p_operator = '<' )
2799 THEN
2800
2801 IF ( p_period_maximum < l_total_hrs )
2802 THEN
2803 l_return := -1;
2804 RETURN l_return;
2805 ELSE
2806 l_return := 1;
2807 RETURN l_return;
2808 END IF;
2809
2810 ELSIF ( p_operator = '<=' )
2811 THEN
2812
2813 IF ( p_period_maximum <= l_total_hrs )
2814 THEN
2815 l_return := -1;
2816 RETURN l_return;
2817 ELSE
2818 l_return := 1;
2819 RETURN l_return;
2820 END IF;
2821
2822
2823 ELSIF ( p_operator = '<>' )
2824 THEN
2825
2826 IF ( p_period_maximum <> l_total_hrs )
2827 THEN
2828 l_return := -1;
2829 RETURN l_return;
2830 ELSE
2831 l_return := 1;
2832 RETURN l_return;
2833 END IF;
2834
2835
2836 ELSIF ( p_operator = '=' )
2837 THEN
2838
2839 IF ( p_period_maximum = l_total_hrs )
2840 THEN
2841 l_return := -1;
2842 RETURN l_return;
2843 ELSE
2844 l_return := 1;
2845 RETURN l_return;
2846 END IF;
2847
2848
2849 ELSIF ( p_operator = '>' )
2850 THEN
2851
2852 IF ( p_period_maximum > l_total_hrs )
2853 THEN
2854 l_return := -1;
2855 RETURN l_return;
2856 ELSE
2857 l_return := 1;
2858 RETURN l_return;
2859 END IF;
2860
2861
2862 ELSIF ( p_operator = '>=' )
2863 THEN
2864
2865 IF ( p_period_maximum >= l_total_hrs )
2866 THEN
2867 l_return := -1;
2868 RETURN l_return;
2869 ELSE
2870 l_return := 1;
2871 RETURN l_return;
2872 END IF;
2873
2874
2875 END IF; -- p_operator
2876
2877 END IF; -- g_ter_record.ter_formula_name = 'HXC_PTO_ACCRUAL_COMPARISON'
2878
2879 if g_debug then
2880 hr_utility.set_location('Processing '||l_proc, 70);
2881 end if;
2882
2883 END period_maximum;
2884
2885 FUNCTION asg_status_id ( p_assignment_id NUMBER
2886 , p_effective_date VARCHAR2 ) RETURN NUMBER IS
2887
2888 l_proc varchar2(72) := g_package||'.asg_status_id';
2889 l_asg_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
2890
2891 CURSOR csr_get_asg_status_id IS
2892 SELECT a.assignment_status_type_id
2893 FROM per_assignment_status_types a
2894 , per_assignments_f asg
2895 WHERE
2896 asg.assignment_id = p_assignment_id AND
2897 TO_DATE(p_effective_date, 'YYYY/MM/DD HH24:MI:SS')
2898 BETWEEN asg.effective_start_date AND asg.effective_end_date
2899 AND
2900 asg.assignment_status_type_id = a.assignment_status_type_id;
2901
2902 BEGIN
2903
2904 OPEN csr_get_asg_status_id;
2905 FETCH csr_get_asg_status_id INTO l_asg_status_id;
2906
2907 IF csr_get_asg_status_id%NOTFOUND
2908 THEN
2909
2910 CLOSE csr_get_asg_status_id;
2911
2912 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2913 fnd_message.set_token('PROCEDURE', l_proc);
2914 fnd_message.set_token('STEP','assignment status');
2915 fnd_message.raise_error;
2916
2917 END IF;
2918
2919 CLOSE csr_get_asg_status_id;
2920
2921 RETURN l_asg_status_id;
2922
2923 END asg_status_id;
2924
2925 PROCEDURE tc_edit_allowed (
2926 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
2927 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
2928 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
2929 ,p_edit_allowed IN OUT nocopy VARCHAR2
2930 ) IS
2931
2932 begin
2933
2934 tc_edit_allowed
2935 (p_timecard_id => p_timecard_id,
2936 p_timecard_ovn => p_timecard_ovn,
2937 p_timecard_status => null,
2938 p_edit_allowed_preference=> p_edit_allowed_preference,
2939 p_edit_allowed => p_edit_allowed
2940 );
2941
2942 end tc_edit_allowed;
2943
2944 PROCEDURE tc_edit_allowed (
2945 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
2946 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
2947 ,p_timecard_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE
2948 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
2949 ,p_edit_allowed IN OUT nocopy VARCHAR2
2950 ) is
2951
2952 CURSOR csr_chk_transfer IS
2953 SELECT 1
2954 FROM dual
2955 WHERE EXISTS (
2956 SELECT 1
2957 FROM hxc_transactions t
2958 , hxc_transaction_details td
2959 WHERE td.time_building_block_id = p_timecard_id
2960 AND
2961 t.transaction_id = td.transaction_id AND
2962 t.type = 'RETRIEVAL' AND
2963 t.status = 'SUCCESS' );
2964
2965 l_proc VARCHAR2(72);
2966
2967 l_tc_status hxc_time_building_blocks.approval_status%TYPE;
2968
2969 l_dummy NUMBER(1);
2970
2971 BEGIN
2972
2973 g_debug := hr_utility.debug_enabled;
2974
2975 if g_debug then
2976 l_proc := g_package||'tc_edit_allowed';
2977 hr_utility.set_location('Entering '||l_proc, 10);
2978 end if;
2979
2980 -- GPM v115.25
2981 -- WWB - 2109325
2982 -- ARR v115.99.11512.7, only fetch approval status if not already known.
2983
2984 if(p_timecard_status is null) then
2985 l_tc_status := hxc_timecard_search_pkg.get_timecard_status_code(p_timecard_id,p_Timecard_Ovn);
2986 else
2987 l_tc_status := p_timecard_status;
2988 end if;
2989
2990 if g_debug then
2991 hr_utility.set_location('Processing '||l_proc, 20);
2992 end if;
2993
2994
2995 IF ( p_edit_allowed_preference = 'NEW_WORKING_REJECTED' )
2996 THEN
2997 if g_debug then
2998 hr_utility.set_location('Processing '||l_proc, 30);
2999 end if;
3000
3001 IF ( ( l_tc_status = 'REJECTED' ) OR ( l_tc_status = 'WORKING' ) )
3002 THEN
3003 p_edit_allowed := 'TRUE';
3004 ELSE
3005 p_edit_allowed := 'FALSE';
3006 END IF;
3007
3008 ELSIF ( p_edit_allowed_preference = 'SUBMITTED' )
3009 THEN
3010 if g_debug then
3011 hr_utility.set_location('Processing '||l_proc, 40);
3012 end if;
3013
3014 IF ( ( l_tc_status = 'REJECTED' ) OR ( l_tc_status = 'WORKING' ) OR ( l_tc_status = 'SUBMITTED' ) )
3015 THEN
3016 p_edit_allowed := 'TRUE';
3017 ELSE
3018 p_edit_allowed := 'FALSE';
3019 END IF;
3020
3021 ELSIF ( p_edit_allowed_preference = 'APPROVALS_INITIATED' )
3022 THEN
3023 if g_debug then
3024 hr_utility.set_location('Processing '||l_proc, 50);
3025 end if;
3026
3027 -- all we need to do here is check that this timecard
3028 -- has not been transferred successfully to any recipient
3029 -- applications
3030
3031 OPEN csr_chk_transfer;
3032 FETCH csr_chk_transfer INTO l_dummy;
3033
3034 IF csr_chk_transfer%FOUND
3035 THEN
3036 p_edit_allowed := 'FALSE';
3037 ELSE
3038 p_edit_allowed := 'TRUE';
3039 END IF;
3040
3041 ELSIF ( p_edit_allowed_preference = 'RETRO' )
3042 THEN
3043 if g_debug then
3044 hr_utility.set_location('Processing '||l_proc, 60);
3045 end if;
3046
3047 IF ( ( l_tc_status = 'REJECTED' ) OR ( l_tc_status = 'WORKING' ) OR ( l_tc_status = 'SUBMITTED' )
3048 OR ( l_tc_status = 'APPROVED' ) OR ( l_tc_status = 'ERROR' ) )
3049 THEN
3050 p_edit_allowed := 'TRUE';
3051 ELSE
3052 p_edit_allowed := 'FALSE';
3053 END IF;
3054
3055 ELSE
3056 if g_debug then
3057 hr_utility.set_location('Processing '||l_proc, 70);
3058 end if;
3059
3060 p_edit_allowed := 'FALSE';
3061
3062 END IF;
3063
3064 -- if the status is ERROR, we don't need to look at
3065 -- the pref -> JUST RETURN TRUE;
3066 IF (l_tc_status = 'ERROR') THEN
3067 p_edit_allowed := 'TRUE';
3068 END IF;
3069
3070
3071 if g_debug then
3072 hr_utility.set_location('Leaving '||l_proc, 80);
3073 end if;
3074
3075 END tc_edit_allowed;
3076
3077
3078
3079
3080
3081
3082
3083 -- Public Function
3084 --
3085 -- Test whether the assignment is enrolled in
3086 -- the specified accrual plan as of the
3087 -- effective date
3088 --
3089 -- Usage
3090 -- Called from the PTO BAl comparison formula
3091 --
3092
3093 FUNCTION chk_pto_plan ( p_assignment_id NUMBER
3094 , p_accrual_plan_id NUMBER
3095 , p_effective_date VARCHAR2 )
3096 RETURN NUMBER IS
3097
3098 l_pto_ok pay_accrual_plans.accrual_plan_id%TYPE := -1;
3099
3100 CURSOR csr_chk_pto_ok IS
3101 SELECT pap.accrual_plan_id
3102 FROM
3103 pay_accrual_plans pap
3104 ,pay_element_types_f pet
3105 ,pay_element_links_f pel
3106 ,pay_element_entries_f pee
3107 WHERE
3108 pap.accrual_plan_id = p_accrual_plan_id AND
3109 pap.accrual_plan_element_type_id = pet.element_type_id
3110 AND
3111 pet.element_type_id = pel.element_type_id AND
3112 pee.effective_start_date BETWEEN
3113 pet.effective_start_date AND pet.effective_end_date
3114 AND
3115 pel.element_link_id = pee.element_link_id AND
3116 pee.effective_start_date BETWEEN
3117 pel.effective_start_date AND pel.effective_end_date
3118 AND
3119 pee.assignment_id = p_assignment_id AND
3120 to_date(p_effective_date, 'YYYY/MM/DD HH24:MI:SS')
3121 BETWEEN pee.effective_start_date AND pee.effective_end_date;
3122
3123
3124 BEGIN
3125
3126 g_debug := hr_utility.debug_enabled;
3127
3128 OPEN csr_chk_pto_ok;
3129 FETCH csr_chk_pto_ok INTO l_pto_ok;
3130
3131 IF csr_chk_pto_ok%FOUND
3132 THEN
3133
3134 if g_debug then
3135 hr_utility.trace('PTO PLAN OK');
3136 end if;
3137 l_pto_ok := 1;
3138
3139 ELSE
3140
3141 if g_debug then
3142 hr_utility.trace('PTO PLAN NOT OK');
3143 end if;
3144
3145
3146 END IF;
3147
3148 CLOSE csr_chk_pto_ok;
3149
3150 RETURN l_pto_ok;
3151
3152 END chk_pto_plan;
3153
3154
3155 PROCEDURE EXECUTE_ELP_TIME_ENTRY_RULES( P_TIME_BUILDING_BLOCKS HXC_BLOCK_TABLE_TYPE
3156 ,P_TIME_ATTRIBUTES HXC_ATTRIBUTE_TABLE_TYPE
3157 ,P_MESSAGES in out NOCOPY hxc_self_service_time_deposit.MESSAGE_TABLE
3158 ,P_TIME_ENTRY_RULE_GROUP_ID NUMBER) IS
3159 n number;
3160 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
3161 l_terg_id hxc_pref_hierarchies.attribute1%TYPE;
3162 l_time_category_id hxc_time_categories.time_category_id%TYPE;
3163
3164 l_prefs hxc_preference_evaluation.t_pref_table;
3165
3166 Begin
3167 g_debug := hr_utility.debug_enabled;
3168
3169 get_timecard_info (
3170 p_time_building_blocks => p_time_building_blocks
3171 , p_timecard_rec => l_timecard_info_rec );
3172 if g_debug then
3173 hr_utility.trace('After get_timecard_info');
3174 hr_utility.trace('start_date ' || l_timecard_info_rec.start_date);
3175 end if;
3176 -- Start 2944785
3177 /*
3178 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
3179 p_preference_code => 'TS_PER_ELP_RULES',
3180 p_start_evaluation_date => l_timecard_info_rec.start_date,
3181 p_end_evaluation_date => l_timecard_info_rec.end_date,
3182 p_sorted_pref_table => l_prefs );
3183
3184 IF ( l_prefs.COUNT > 1 )
3185 THEN
3186
3187 -- error since cannot have more than one ELP preference in timecard period
3188
3189 -- in the interim take the first available value
3190
3191 l_terg_id := l_prefs(1).attribute1;
3192
3193 ELSE
3194
3195 l_terg_id := l_prefs(1).attribute1;
3196
3197 END IF;
3198 -- GPM v115.55
3199 */
3200 l_terg_id := P_TIME_ENTRY_RULE_GROUP_ID;
3201
3202 -- End 2944785
3203
3204 FOR r_rules IN csr_get_rules ( p_terg_id => l_terg_id
3205 , p_start_date => l_timecard_info_rec.start_date
3206 , p_end_date => l_timecard_info_rec.end_date )
3207 LOOP
3208 if g_debug then
3209 hr_utility.trace('r_rules.Timecategory_name' || r_rules.attribute2);
3210 hr_utility.trace('r_rules.Recipient Application' || r_rules.attribute1);
3211 end if;
3212 l_time_category_id := r_rules.attribute2;
3213 if g_debug then
3214 hr_utility.trace('Time Category ID' || l_time_category_id);
3215 end if;
3216 hxc_time_category_utils_pkg.initialise_time_category(
3217 p_time_category_id => to_number(l_time_category_id),
3218 p_tco_att => P_TIME_ATTRIBUTES);
3219 n := p_time_building_blocks.first;
3220 loop
3221 exit when not p_time_building_blocks.exists(n);
3222 if (p_time_building_blocks(n).scope = 'DETAIL') then
3223 if (hxc_time_category_utils_pkg.chk_tc_bb_ok( p_time_building_blocks(n).time_building_block_id)) then
3224 add_error_to_table (
3225 p_message_table => p_messages
3226 , p_message_name => r_rules.attribute1
3227 , p_message_token => NULL
3228 , p_message_level => 'PTE'
3229 , p_message_field => NULL
3230 , p_timecard_bb_id => p_time_building_blocks(n).time_building_block_id
3231 , p_time_attribute_id => NULL
3232 , p_timecard_bb_ovn => p_time_building_blocks(n).object_version_number
3233 , p_time_attribute_ovn => NULL );
3234 end if;
3235 end if;
3236 n := p_time_building_blocks.next(n);
3237 end loop;
3238 end loop;
3239
3240 if g_debug then
3241 hr_utility.trace('Message Table');
3242 end if;
3243 n := p_messages.first;
3244 loop
3245 exit when not p_messages.exists(n);
3246 if (p_messages(n).message_level = 'PTE') then
3247 if g_debug then
3248 hr_utility.trace('Time Building Block Id' || p_messages(n).time_building_block_id);
3249 hr_utility.trace('Time Building Block OVN' || p_messages(n).time_building_block_ovn);
3250 hr_utility.trace('Recipient Application ID' || p_messages(n).message_name);
3251 end if;
3252 end if;
3253 n := p_messages.next(n);
3254 end loop;
3255
3256 if g_debug then
3257 hr_utility.trace('End of execute ELP time entry rules');
3258 end if;
3259
3260 end execute_ELP_time_entry_rules;
3261
3262 PROCEDURE EXECUTE_CLA_TIME_ENTRY_RULES( P_TIME_BUILDING_BLOCKS hxc_self_service_time_deposit.timecard_info
3263 ,P_TIME_ATTRIBUTES hxc_self_service_time_deposit.building_block_attribute_info
3264 ,P_MESSAGES in out NOCOPY hxc_self_service_time_deposit.MESSAGE_TABLE
3265 ,P_TIME_ENTRY_RULE_GROUP_ID NUMBER) IS
3266 n number;
3267 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
3268 l_terg_id hxc_pref_hierarchies.attribute1%TYPE;
3269 l_time_category_id hxc_time_categories.time_category_id%TYPE;
3270
3271 l_prefs hxc_preference_evaluation.t_pref_table;
3272
3273 FUNCTION chk_bb_late (
3274 p_stop_time DATE,
3275 p_st_late_hrs NUMBER,
3276 p_qnt_late_hrs NUMBER,
3277 p_date_worked DATE
3278 )
3279 RETURN BOOLEAN
3280 IS
3281 l_late_measure NUMBER;
3282 l_client_tz fnd_timezones_b.timezone_code%type;
3283 l_server_tz fnd_timezones_b.timezone_code%type;
3284 l_client_time DATE;
3285 BEGIN
3286 g_debug := hr_utility.debug_enabled;
3287
3288 if g_debug then
3289 hr_utility.TRACE ( 'Stop Time '
3290 || p_stop_time);
3291 hr_utility.TRACE ( 'p_st_late_hrs '
3292 || p_st_late_hrs);
3293 hr_utility.TRACE ( 'p_qnt_late_hrs '
3294 || p_qnt_late_hrs);
3295 end if;
3296 --Fix for Bug No:4948883
3297 fnd_date.timezones_enabled := true;
3298 l_client_tz := fnd_timezones.get_client_timezone_code;
3299 l_server_tz := fnd_timezones.get_server_timezone_code;
3300 l_client_time := fnd_date.adjust_datetime(sysdate,l_server_tz,l_client_tz);
3301
3302 IF (p_stop_time IS NOT NULL)
3303 THEN
3304 l_late_measure := ( l_client_time - p_stop_time
3305 ) * 24;
3306 if g_debug then
3307 hr_utility.TRACE ( 'l_late_measure '
3308 || l_late_measure);
3309 end if;
3310
3311 IF (l_late_measure > p_st_late_hrs)
3312 THEN
3313 RETURN TRUE;
3314 END IF;
3315 ELSE
3316 if g_debug then
3317 hr_utility.trace('L_date_worked...............' || to_char(p_date_worked,'dd-mon-rrrr hh:mi:ss'));
3318 end if;
3319 IF (( TRUNC (p_date_worked)
3320 + (p_qnt_late_hrs / 24)
3321 ) < l_client_time
3322 )
3323 THEN
3324 if g_debug then
3325 hr_utility.TRACE ('Late.....');
3326 end if;
3327 RETURN TRUE;
3328 END IF;
3329 END IF;
3330
3331 RETURN FALSE;
3332 END chk_bb_late;
3333
3334 PROCEDURE populate_old_tco (
3335 p_timecard_rec IN hxc_time_entry_rules_utils_pkg.r_timecard_info,
3336 p_timecard_building_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
3337 p_timecard_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
3338 )
3339 IS
3340 CURSOR csr_get_det_bbs
3341 IS
3342 SELECT detail.time_building_block_id, detail.TYPE, detail.measure,
3343 detail.unit_of_measure, detail.start_time,
3344 detail.stop_time, detail.parent_building_block_id,
3345 'N' parent_is_new, detail.SCOPE,
3346 detail.object_version_number, detail.approval_status,
3347 detail.resource_id, detail.resource_type,
3348 detail.approval_style_id, detail.date_from, detail.date_to,
3349 detail.comment_text, detail.parent_building_block_ovn,
3350 'N' NEW, 'N' changed
3351 FROM hxc_time_building_blocks detail,
3352 hxc_time_building_blocks DAY
3353 WHERE DAY.parent_building_block_id =
3354 p_timecard_rec.timecard_bb_id
3355 AND DAY.parent_building_block_ovn =
3356 p_timecard_rec.timecard_ovn
3357 AND detail.date_to = hr_general.end_of_time
3358 AND detail.SCOPE = 'DETAIL'
3359 AND detail.parent_building_block_id =
3360 DAY.time_building_block_id
3361 AND detail.parent_building_block_ovn =
3362 DAY.object_version_number
3363 AND DAY.SCOPE = 'DAY'
3364 AND DAY.date_to = hr_general.end_of_time;
3365
3366 CURSOR csr_get_det_attr (p_building_block_id NUMBER, p_bb_ovn NUMBER)
3367 IS
3368 SELECT a.time_attribute_id, au.time_building_block_id,
3369 bbit.bld_blk_info_type, a.attribute_category, a.attribute1,
3370 a.attribute2, a.attribute3, a.attribute4, a.attribute5,
3371 a.attribute6, a.attribute7, a.attribute8, a.attribute9,
3372 a.attribute10, a.attribute11, a.attribute12, a.attribute13,
3373 a.attribute14, a.attribute15, a.attribute16, a.attribute17,
3374 a.attribute18, a.attribute19, a.attribute20, a.attribute21,
3375 a.attribute22, a.attribute23, a.attribute24, a.attribute25,
3376 a.attribute26, a.attribute27, a.attribute28, a.attribute29,
3377 a.attribute30, a.bld_blk_info_type_id,
3378 a.object_version_number, 'N' NEW, 'N' changed
3379 FROM hxc_time_attributes a,
3380 hxc_time_attribute_usages au,
3381 hxc_bld_blk_info_types bbit
3382 WHERE au.time_building_block_id = p_building_block_id
3383 AND au.time_building_block_ovn = p_bb_ovn
3384 AND au.time_attribute_id = a.time_attribute_id
3385 AND NOT (a.attribute_category = 'TEMPLATES')
3386 AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
3387 AND a.object_version_number =
3388 (SELECT MAX (object_version_number)
3389 FROM hxc_time_attributes
3390 WHERE time_attribute_id = a.time_attribute_id);
3391
3392 l_tbb_index NUMBER;
3393 l_att_index NUMBER;
3394 r_det_rec csr_get_det_bbs%ROWTYPE;
3395 BEGIN
3396
3397
3398 if g_debug then
3399 hr_utility.TRACE ('Start of populate old tco');
3400 end if;
3401 p_timecard_building_blocks.DELETE;
3402 p_timecard_attributes.DELETE;
3403 l_tbb_index := 0;
3404 l_att_index := 0;
3405
3406 FOR r_det_rec IN csr_get_det_bbs
3407 LOOP
3408 if g_debug then
3409 hr_utility.TRACE (
3410 'r_det_rec.time_building_block_id '
3411 || r_det_rec.time_building_block_id
3412 );
3413 hr_utility.TRACE (
3414 'r_det_rec.object_version_number '
3415 || r_det_rec.object_version_number
3416 );
3417 end if;
3418 p_timecard_building_blocks (l_tbb_index).time_building_block_id :=
3419 r_det_rec.time_building_block_id;
3420 p_timecard_building_blocks (l_tbb_index).TYPE := r_det_rec.TYPE;
3421 p_timecard_building_blocks (l_tbb_index).measure :=
3422 r_det_rec.measure;
3423 p_timecard_building_blocks (l_tbb_index).unit_of_measure :=
3424 r_det_rec.unit_of_measure;
3425 p_timecard_building_blocks (l_tbb_index).start_time :=
3426 r_det_rec.start_time;
3427 p_timecard_building_blocks (l_tbb_index).stop_time :=
3428 r_det_rec.stop_time;
3429 p_timecard_building_blocks (l_tbb_index).parent_building_block_id :=
3430 r_det_rec.parent_building_block_id;
3431 p_timecard_building_blocks (l_tbb_index).parent_is_new :=
3432 r_det_rec.parent_is_new;
3433 p_timecard_building_blocks (l_tbb_index).SCOPE := r_det_rec.SCOPE;
3434 p_timecard_building_blocks (l_tbb_index).object_version_number :=
3435 r_det_rec.object_version_number;
3436 p_timecard_building_blocks (l_tbb_index).approval_status :=
3437 r_det_rec.approval_status;
3438 p_timecard_building_blocks (l_tbb_index).resource_id :=
3439 r_det_rec.resource_id;
3440 p_timecard_building_blocks (l_tbb_index).resource_type :=
3441 r_det_rec.resource_type;
3442 p_timecard_building_blocks (l_tbb_index).approval_style_id :=
3443 r_det_rec.approval_style_id;
3444 p_timecard_building_blocks (l_tbb_index).date_from :=
3445 r_det_rec.date_from;
3446 p_timecard_building_blocks (l_tbb_index).date_to :=
3447 r_det_rec.date_to;
3448 p_timecard_building_blocks (l_tbb_index).comment_text :=
3449 r_det_rec.comment_text;
3450 p_timecard_building_blocks (l_tbb_index).parent_building_block_ovn :=
3451 r_det_rec.parent_building_block_ovn;
3452 p_timecard_building_blocks (l_tbb_index).NEW := r_det_rec.NEW;
3453 p_timecard_building_blocks (l_tbb_index).changed :=
3454 r_det_rec.changed;
3455 l_tbb_index := l_tbb_index
3456 + 1;
3457
3458 FOR r_der_attr IN
3459 csr_get_det_attr (
3460 r_det_rec.time_building_block_id,
3461 r_det_rec.object_version_number
3462 )
3463 LOOP
3464 if g_debug then
3465 hr_utility.TRACE (
3466 'Attribute Id'
3467 || r_der_attr.time_attribute_id
3468 );
3469 hr_utility.TRACE (
3470 'Attribute Category '
3471 || r_der_attr.attribute_category
3472 );
3473 end if;
3474 p_timecard_attributes (l_att_index).time_attribute_id :=
3475 r_der_attr.time_attribute_id;
3476 p_timecard_attributes (l_att_index).building_block_id :=
3477 r_der_attr.time_building_block_id;
3478 p_timecard_attributes (l_att_index).bld_blk_info_type :=
3479 r_der_attr.bld_blk_info_type;
3480 p_timecard_attributes (l_att_index).attribute_category :=
3481 r_der_attr.attribute_category;
3482 p_timecard_attributes (l_att_index).attribute1 :=
3483 r_der_attr.attribute1;
3484 p_timecard_attributes (l_att_index).attribute2 :=
3485 r_der_attr.attribute2;
3486 p_timecard_attributes (l_att_index).attribute3 :=
3487 r_der_attr.attribute3;
3488 p_timecard_attributes (l_att_index).attribute4 :=
3489 r_der_attr.attribute4;
3490 p_timecard_attributes (l_att_index).attribute5 :=
3491 r_der_attr.attribute5;
3492 p_timecard_attributes (l_att_index).attribute6 :=
3493 r_der_attr.attribute6;
3494 p_timecard_attributes (l_att_index).attribute7 :=
3495 r_der_attr.attribute7;
3496 p_timecard_attributes (l_att_index).attribute8 :=
3497 r_der_attr.attribute8;
3498 p_timecard_attributes (l_att_index).attribute9 :=
3499 r_der_attr.attribute9;
3500 p_timecard_attributes (l_att_index).attribute10 :=
3501 r_der_attr.attribute10;
3502 p_timecard_attributes (l_att_index).attribute11 :=
3503 r_der_attr.attribute11;
3504 p_timecard_attributes (l_att_index).attribute12 :=
3505 r_der_attr.attribute12;
3506 p_timecard_attributes (l_att_index).attribute13 :=
3507 r_der_attr.attribute13;
3508 p_timecard_attributes (l_att_index).attribute14 :=
3509 r_der_attr.attribute14;
3510 p_timecard_attributes (l_att_index).attribute15 :=
3511 r_der_attr.attribute15;
3512 p_timecard_attributes (l_att_index).attribute16 :=
3513 r_der_attr.attribute16;
3514 p_timecard_attributes (l_att_index).attribute17 :=
3515 r_der_attr.attribute17;
3516 p_timecard_attributes (l_att_index).attribute18 :=
3517 r_der_attr.attribute18;
3518 p_timecard_attributes (l_att_index).attribute19 :=
3519 r_der_attr.attribute19;
3520 p_timecard_attributes (l_att_index).attribute20 :=
3521 r_der_attr.attribute20;
3522 p_timecard_attributes (l_att_index).attribute21 :=
3523 r_der_attr.attribute21;
3524 p_timecard_attributes (l_att_index).attribute22 :=
3525 r_der_attr.attribute22;
3526 p_timecard_attributes (l_att_index).attribute23 :=
3527 r_der_attr.attribute23;
3528 p_timecard_attributes (l_att_index).attribute24 :=
3529 r_der_attr.attribute24;
3530 p_timecard_attributes (l_att_index).attribute25 :=
3531 r_der_attr.attribute25;
3532 p_timecard_attributes (l_att_index).attribute26 :=
3533 r_der_attr.attribute26;
3534 p_timecard_attributes (l_att_index).attribute27 :=
3535 r_der_attr.attribute27;
3536 p_timecard_attributes (l_att_index).attribute28 :=
3537 r_der_attr.attribute28;
3538 p_timecard_attributes (l_att_index).attribute29 :=
3539 r_der_attr.attribute29;
3540 p_timecard_attributes (l_att_index).attribute30 :=
3541 r_der_attr.attribute30;
3542 p_timecard_attributes (l_att_index).bld_blk_info_type_id :=
3543 r_der_attr.bld_blk_info_type_id;
3544 p_timecard_attributes (l_att_index).object_version_number :=
3545 r_der_attr.object_version_number;
3546 p_timecard_attributes (l_att_index).NEW := r_der_attr.NEW;
3547 p_timecard_attributes (l_att_index).changed :=
3548 r_der_attr.changed;
3549 l_att_index := l_att_index
3550 + 1;
3551 END LOOP;
3552 END LOOP;
3553
3554 if g_debug then
3555 hr_utility.TRACE ('Leaving populate old tco');
3556 end if;
3557 END populate_old_tco;
3558
3559 FUNCTION compare_tbb_attributes (
3560 p_attribute1 hxc_self_service_time_deposit.attribute_info,
3561 p_attribute2 hxc_self_service_time_deposit.attribute_info,
3562 p_tbb_deleted BOOLEAN,
3563 p_change_att_tab IN OUT NOCOPY t_change_att_tab
3564 )
3565 RETURN VARCHAR2
3566 IS
3567 CURSOR csr_get_mapping_name (
3568 p_attribute VARCHAR2,
3569 p_attribute_id NUMBER,
3570 p_att_ovn NUMBER
3571 )
3572 IS
3573 SELECT hmc.field_name
3574 FROM hxc_mapping_components hmc, hxc_time_attributes hta
3575 WHERE hta.time_attribute_id = p_attribute_id
3576 AND hta.object_version_number = p_att_ovn
3577 AND hta.bld_blk_info_type_id = hmc.bld_blk_info_type_id
3578 AND hmc.SEGMENT = UPPER (p_attribute);
3579
3580 l_category_flag BOOLEAN := FALSE;
3581 l_mapping_name VARCHAR2 (80);
3582 l_return_mapp VARCHAR2 (200);
3583 l_change_att_index NUMBER:=0;
3584
3585 BEGIN
3586
3587
3588
3589 IF p_change_att_tab.count = 0 then
3590 l_change_att_index :=1;
3591 ELSE
3592 l_change_att_index := p_change_att_tab.last+1;
3593 END IF;
3594
3595 IF (NVL (p_attribute1.attribute_category, 'NULL') <>
3596 NVL (p_attribute2.attribute_category, 'NULL')
3597 )
3598 THEN
3599 l_category_flag := TRUE;
3600 --Changes made to make use of bld_blk_info_type always. Removed the Attribute cateogory check
3601 -- for ELEMENT -%.
3602 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3603 l_return_mapp :=
3604 l_return_mapp
3605 || ':'
3606 || p_attribute1.attribute_category;
3607
3608 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE_CATEGORY';
3609 l_change_att_index := l_change_att_index+1;
3610
3611 END IF;
3612
3613
3614 IF ((NVL (p_attribute1.attribute1, 'NULL') <>
3615 NVL (p_attribute2.attribute1, 'NULL')
3616 OR p_tbb_deleted)
3617 )
3618 THEN
3619 l_mapping_name := NULL;
3620
3621 IF ( p_attribute1.attribute1 IS NOT NULL
3622 OR p_attribute2.attribute1 IS NOT NULL
3623 )
3624 THEN
3625
3626 OPEN csr_get_mapping_name (
3627 'attribute1',
3628 p_attribute1.time_attribute_id,
3629 p_attribute1.object_version_number
3630 );
3631 FETCH csr_get_mapping_name INTO l_mapping_name;
3632 CLOSE csr_get_mapping_name;
3633 END IF;
3634 --Changes made to make use of bld_blk_info_type always.
3635 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3636 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE1';
3637 l_change_att_index := l_change_att_index+1;
3638
3639 if g_debug then
3640 hr_utility.TRACE ('Att1');
3641 hr_utility.TRACE ( 'l_mapping_name '
3642 || l_mapping_name);
3643 hr_utility.TRACE ( 'l_return_mapp '
3644 || l_return_mapp);
3645 end if;
3646 l_return_mapp := l_return_mapp
3647 || ':'
3648 || l_mapping_name;
3649 END IF;
3650
3651 IF ((NVL (p_attribute1.attribute2, 'NULL') <>
3652 NVL (p_attribute2.attribute2, 'NULL')
3653 OR p_tbb_deleted)
3654 )
3655 THEN
3656 l_mapping_name := NULL;
3657
3658 IF ( p_attribute1.attribute2 IS NOT NULL
3659 OR p_attribute2.attribute2 IS NOT NULL
3660 )
3661 THEN
3662
3663 OPEN csr_get_mapping_name (
3664 'attribute2',
3665 p_attribute1.time_attribute_id,
3666 p_attribute1.object_version_number
3667 );
3668 FETCH csr_get_mapping_name INTO l_mapping_name;
3669 CLOSE csr_get_mapping_name;
3670 END IF;
3671 --Changes made to make use of bld_blk_info_type always.
3672 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3673 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE2';
3674 l_change_att_index := l_change_att_index+1;
3675
3676 if g_debug then
3677 hr_utility.TRACE ('Att2');
3678 hr_utility.TRACE ( 'l_mapping_name '
3679 || l_mapping_name);
3680 hr_utility.TRACE ( 'l_return_mapp '
3681 || l_return_mapp);
3682 end if;
3683 l_return_mapp := l_return_mapp
3684 || ':'
3685 || l_mapping_name;
3686 END IF;
3687
3688 IF ((NVL (p_attribute1.attribute3, 'NULL') <>
3689 NVL (p_attribute2.attribute3, 'NULL')
3690 OR p_tbb_deleted)
3691 )
3692 THEN
3693 l_mapping_name := NULL;
3694
3695
3696 IF ( p_attribute1.attribute3 IS NOT NULL
3697 OR p_attribute2.attribute3 IS NOT NULL
3698 )
3699 THEN
3700
3701 OPEN csr_get_mapping_name (
3702 'attribute3',
3703 p_attribute1.time_attribute_id,
3704 p_attribute1.object_version_number
3705 );
3706 FETCH csr_get_mapping_name INTO l_mapping_name;
3707 CLOSE csr_get_mapping_name;
3708 END IF;
3709 --Changes made to make use of bld_blk_info_type always.
3710 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3711 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE3';
3712 l_change_att_index := l_change_att_index+1;
3713
3714 if g_debug then
3715 hr_utility.TRACE ('Att3');
3716 hr_utility.TRACE ( 'l_mapping_name '
3717 || l_mapping_name);
3718 hr_utility.TRACE ( 'l_return_mapp '
3719 || l_return_mapp);
3720 end if;
3721 l_return_mapp := l_return_mapp
3722 || ':'
3723 || l_mapping_name;
3724 END IF;
3725
3726 IF ((NVL (p_attribute1.attribute4, 'NULL') <>
3727 NVL (p_attribute2.attribute4, 'NULL')
3728 OR p_tbb_deleted)
3729 )
3730 THEN
3731 l_mapping_name := NULL;
3732
3733 IF ( p_attribute1.attribute4 IS NOT NULL
3734 OR p_attribute2.attribute4 IS NOT NULL
3735 )
3736 THEN
3737
3738 OPEN csr_get_mapping_name (
3739 'attribute4',
3740 p_attribute1.time_attribute_id,
3741 p_attribute1.object_version_number
3742 );
3743 FETCH csr_get_mapping_name INTO l_mapping_name;
3744 CLOSE csr_get_mapping_name;
3745 END IF;
3746 --Changes made to make use of bld_blk_info_type always.
3747 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3748 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE4';
3749 l_change_att_index := l_change_att_index+1;
3750
3751 if g_debug then
3752 hr_utility.TRACE ('Att4');
3753 hr_utility.TRACE ( 'l_mapping_name '
3754 || l_mapping_name);
3755 hr_utility.TRACE ( 'l_return_mapp '
3756 || l_return_mapp);
3757 end if;
3758 l_return_mapp := l_return_mapp
3759 || ':'
3760 || l_mapping_name;
3761 END IF;
3762
3763 IF ((NVL (p_attribute1.attribute5, 'NULL') <>
3764 NVL (p_attribute2.attribute5, 'NULL')
3765 OR p_tbb_deleted)
3766 )
3767 THEN
3768 l_mapping_name := NULL;
3769
3770
3771
3772 IF ( p_attribute1.attribute5 IS NOT NULL
3773 OR p_attribute2.attribute5 IS NOT NULL
3774 )
3775 THEN
3776
3777 OPEN csr_get_mapping_name (
3778 'attribute5',
3779 p_attribute1.time_attribute_id,
3780 p_attribute1.object_version_number
3781 );
3782 FETCH csr_get_mapping_name INTO l_mapping_name;
3783 CLOSE csr_get_mapping_name;
3784 END IF;
3785 --Changes made to make use of bld_blk_info_type always.
3786 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3787 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE5';
3788 l_change_att_index := l_change_att_index+1;
3789
3790 l_return_mapp := l_return_mapp
3791 || ':'
3792 || l_mapping_name;
3793 END IF;
3794
3795 IF ((NVL (p_attribute1.attribute6, 'NULL') <>
3796 NVL (p_attribute2.attribute6, 'NULL')
3797 OR p_tbb_deleted)
3798 )
3799 THEN
3800 l_mapping_name := NULL;
3801
3802 IF ( p_attribute1.attribute6 IS NOT NULL
3803 OR p_attribute2.attribute6 IS NOT NULL
3804 )
3805 THEN
3806
3807
3808 OPEN csr_get_mapping_name (
3809 'attribute6',
3810 p_attribute1.time_attribute_id,
3811 p_attribute1.object_version_number
3812 );
3813 FETCH csr_get_mapping_name INTO l_mapping_name;
3814 CLOSE csr_get_mapping_name;
3815 END IF;
3816 --Changes made to make use of bld_blk_info_type always.
3817 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3818 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE6';
3819 l_change_att_index := l_change_att_index+1;
3820
3821 l_return_mapp := l_return_mapp
3822 || ':'
3823 || l_mapping_name;
3824 END IF;
3825
3826 IF ((NVL (p_attribute1.attribute7, 'NULL') <>
3827 NVL (p_attribute2.attribute7, 'NULL')
3828 OR p_tbb_deleted)
3829 )
3830 THEN
3831 l_mapping_name := NULL;
3832
3833
3834 IF ( p_attribute1.attribute7 IS NOT NULL
3835 OR p_attribute2.attribute7 IS NOT NULL
3836 )
3837 THEN
3838
3839
3840 OPEN csr_get_mapping_name (
3841 'attribute7',
3842 p_attribute1.time_attribute_id,
3843 p_attribute1.object_version_number
3844 );
3845 FETCH csr_get_mapping_name INTO l_mapping_name;
3846 CLOSE csr_get_mapping_name;
3847 END IF;
3848 --Changes made to make use of bld_blk_info_type always.
3849 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3850 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE7';
3851 l_change_att_index := l_change_att_index+1;
3852
3853 l_return_mapp := l_return_mapp
3854 || ':'
3855 || l_mapping_name;
3856 END IF;
3857
3858 IF ((NVL (p_attribute1.attribute8, 'NULL') <>
3859 NVL (p_attribute2.attribute8, 'NULL')
3860 OR p_tbb_deleted)
3861 )
3862 THEN
3863 l_mapping_name := NULL;
3864
3865 IF ( p_attribute1.attribute8 IS NOT NULL
3866 OR p_attribute2.attribute8 IS NOT NULL
3867 )
3868 THEN
3869
3870 OPEN csr_get_mapping_name (
3871 'attribute8',
3872 p_attribute1.time_attribute_id,
3873 p_attribute1.object_version_number
3874 );
3875 FETCH csr_get_mapping_name INTO l_mapping_name;
3876 CLOSE csr_get_mapping_name;
3877 END IF;
3878 --Changes made to make use of bld_blk_info_type always.
3879 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3880 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE8';
3881 l_change_att_index := l_change_att_index+1;
3882
3883 l_return_mapp := l_return_mapp
3884 || ':'
3885 || l_mapping_name;
3886 END IF;
3887
3888 IF ((NVL (p_attribute1.attribute9, 'NULL') <>
3889 NVL (p_attribute2.attribute9, 'NULL')
3890 OR p_tbb_deleted)
3891 )
3892 THEN
3893 l_mapping_name := NULL;
3894
3895 IF ( p_attribute1.attribute9 IS NOT NULL
3896 OR p_attribute2.attribute9 IS NOT NULL
3897 )
3898 THEN
3899 OPEN csr_get_mapping_name (
3900 'attribute9',
3901 p_attribute1.time_attribute_id,
3902 p_attribute1.object_version_number
3903 );
3904 FETCH csr_get_mapping_name INTO l_mapping_name;
3905 CLOSE csr_get_mapping_name;
3906 END IF;
3907 --Changes made to make use of bld_blk_info_type always.
3908 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3909 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE9';
3910 l_change_att_index := l_change_att_index+1;
3911
3912
3913 l_return_mapp := l_return_mapp
3914 || ':'
3915 || l_mapping_name;
3916 END IF;
3917
3918 IF ((NVL (p_attribute1.attribute10, 'NULL') <>
3919 NVL (p_attribute2.attribute10, 'NULL')
3920 OR p_tbb_deleted)
3921 )
3922 THEN
3923 l_mapping_name := NULL;
3924
3925 IF ( p_attribute1.attribute10 IS NOT NULL
3926 OR p_attribute2.attribute10 IS NOT NULL
3927 )
3928 THEN
3929
3930 OPEN csr_get_mapping_name (
3931 'attribute10',
3932 p_attribute1.time_attribute_id,
3933 p_attribute1.object_version_number
3934 );
3935 FETCH csr_get_mapping_name INTO l_mapping_name;
3936 CLOSE csr_get_mapping_name;
3937 END IF;
3938 --Changes made to make use of bld_blk_info_type always.
3939 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3940 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE10';
3941 l_change_att_index := l_change_att_index+1;
3942
3943 l_return_mapp := l_return_mapp
3944 || ':'
3945 || l_mapping_name;
3946 END IF;
3947
3948 IF ((NVL (p_attribute1.attribute11, 'NULL') <>
3949 NVL (p_attribute2.attribute11, 'NULL')
3950 OR p_tbb_deleted)
3951 )
3952 THEN
3953 l_mapping_name := NULL;
3954
3955 IF ( p_attribute1.attribute11 IS NOT NULL
3956 OR p_attribute2.attribute11 IS NOT NULL
3957 )
3958 THEN
3959
3960 OPEN csr_get_mapping_name (
3961 'attribute11',
3962 p_attribute1.time_attribute_id,
3963 p_attribute1.object_version_number
3964 );
3965 FETCH csr_get_mapping_name INTO l_mapping_name;
3966 CLOSE csr_get_mapping_name;
3967 END IF;
3968 --Changes made to make use of bld_blk_info_type always.
3969 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
3970 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE11';
3971 l_change_att_index := l_change_att_index+1;
3972
3973
3974 l_return_mapp := l_return_mapp
3975 || ':'
3976 || l_mapping_name;
3977 END IF;
3978
3979 IF ((NVL (p_attribute1.attribute12, 'NULL') <>
3980 NVL (p_attribute2.attribute12, 'NULL')
3981 OR p_tbb_deleted)
3982 )
3983 THEN
3984 l_mapping_name := NULL;
3985
3986 IF ( p_attribute1.attribute12 IS NOT NULL
3987 OR p_attribute2.attribute12 IS NOT NULL
3988 )
3989 THEN
3990
3991 OPEN csr_get_mapping_name (
3992 'attribute12',
3993 p_attribute1.time_attribute_id,
3994 p_attribute1.object_version_number
3995 );
3996 FETCH csr_get_mapping_name INTO l_mapping_name;
3997 CLOSE csr_get_mapping_name;
3998 END IF;
3999 --Changes made to make use of bld_blk_info_type always.
4000 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4001 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE12';
4002 l_change_att_index := l_change_att_index+1;
4003
4004 l_return_mapp := l_return_mapp
4005 || ':'
4006 || l_mapping_name;
4007 END IF;
4008
4009 IF ((NVL (p_attribute1.attribute13, 'NULL') <>
4010 NVL (p_attribute2.attribute13, 'NULL')
4011 OR p_tbb_deleted)
4012 )
4013 THEN
4014
4015 l_mapping_name := NULL;
4016
4017
4018 IF ( p_attribute1.attribute13 IS NOT NULL
4019 OR p_attribute2.attribute13 IS NOT NULL
4020 )
4021 THEN
4022
4023 OPEN csr_get_mapping_name (
4024 'attribute13',
4025 p_attribute1.time_attribute_id,
4026 p_attribute1.object_version_number
4027 );
4028 FETCH csr_get_mapping_name INTO l_mapping_name;
4029 CLOSE csr_get_mapping_name;
4030 END IF;
4031 --Changes made to make use of bld_blk_info_type always.
4032 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4033 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE13';
4034 l_change_att_index := l_change_att_index+1;
4035
4036 l_return_mapp := l_return_mapp
4037 || ':'
4038 || l_mapping_name;
4039 END IF;
4040
4041 IF ((NVL (p_attribute1.attribute14, 'NULL') <>
4042 NVL (p_attribute2.attribute14, 'NULL')
4043 OR p_tbb_deleted)
4044 )
4045 THEN
4046 l_mapping_name := NULL;
4047
4048 IF ( p_attribute1.attribute14 IS NOT NULL
4049 OR p_attribute2.attribute14 IS NOT NULL
4050 )
4051 THEN
4052
4053 OPEN csr_get_mapping_name (
4054 'attribute14',
4055 p_attribute1.time_attribute_id,
4056 p_attribute1.object_version_number
4057 );
4058 FETCH csr_get_mapping_name INTO l_mapping_name;
4059 CLOSE csr_get_mapping_name;
4060 END IF;
4061 --Changes made to make use of bld_blk_info_type always.
4062 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4063 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE14';
4064 l_change_att_index := l_change_att_index+1;
4065
4066 l_return_mapp := l_return_mapp
4067 || ':'
4068 || l_mapping_name;
4069 END IF;
4070
4071 IF ((NVL (p_attribute1.attribute15, 'NULL') <>
4072 NVL (p_attribute2.attribute15, 'NULL')
4073 OR p_tbb_deleted)
4074 )
4075 THEN
4076 l_mapping_name := NULL;
4077
4078
4079 IF ( p_attribute1.attribute15 IS NOT NULL
4080 OR p_attribute2.attribute15 IS NOT NULL
4081 )
4082 THEN
4083
4084 OPEN csr_get_mapping_name (
4085 'attribute15',
4086 p_attribute1.time_attribute_id,
4087 p_attribute1.object_version_number
4088 );
4089 FETCH csr_get_mapping_name INTO l_mapping_name;
4090 CLOSE csr_get_mapping_name;
4091 END IF;
4092 --Changes made to make use of bld_blk_info_type always.
4093 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4094 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE15';
4095 l_change_att_index := l_change_att_index+1;
4096
4097 l_return_mapp := l_return_mapp
4098 || ':'
4099 || l_mapping_name;
4100 END IF;
4101
4102 IF ((NVL (p_attribute1.attribute16, 'NULL') <>
4103 NVL (p_attribute2.attribute16, 'NULL')
4104 OR p_tbb_deleted)
4105 )
4106 THEN
4107 l_mapping_name := NULL;
4108
4109 IF ( p_attribute1.attribute16 IS NOT NULL
4110 OR p_attribute2.attribute16 IS NOT NULL
4111 )
4112 THEN
4113
4114 OPEN csr_get_mapping_name (
4115 'attribute16',
4116 p_attribute1.time_attribute_id,
4117 p_attribute1.object_version_number
4118 );
4119 FETCH csr_get_mapping_name INTO l_mapping_name;
4120 CLOSE csr_get_mapping_name;
4121 END IF;
4122 --Changes made to make use of bld_blk_info_type always.
4123 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4124 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE16';
4125 l_change_att_index := l_change_att_index+1;
4126
4127 l_return_mapp := l_return_mapp
4128 || ':'
4129 || l_mapping_name;
4130 END IF;
4131
4132 IF ((NVL (p_attribute1.attribute17, 'NULL') <>
4133 NVL (p_attribute2.attribute17, 'NULL')
4134 OR p_tbb_deleted)
4135 )
4136 THEN
4137 l_mapping_name := NULL;
4138
4139 IF ( p_attribute1.attribute17 IS NOT NULL
4140 OR p_attribute2.attribute17 IS NOT NULL
4141 )
4142 THEN
4143
4144 OPEN csr_get_mapping_name (
4145 'attribute17',
4146 p_attribute1.time_attribute_id,
4147 p_attribute1.object_version_number
4148 );
4149 FETCH csr_get_mapping_name INTO l_mapping_name;
4150 CLOSE csr_get_mapping_name;
4151 END IF;
4152 --Changes made to make use of bld_blk_info_type always.
4153 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4154 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE17';
4155 l_change_att_index := l_change_att_index+1;
4156
4157 l_return_mapp := l_return_mapp
4158 || ':'
4159 || l_mapping_name;
4160 END IF;
4161
4162 IF ((NVL (p_attribute1.attribute18, 'NULL') <>
4163 NVL (p_attribute2.attribute18, 'NULL')
4164 OR p_tbb_deleted)
4165 )
4166 THEN
4167 l_mapping_name := NULL;
4168
4169 IF ( p_attribute1.attribute18 IS NOT NULL
4170 OR p_attribute2.attribute18 IS NOT NULL
4171 )
4172 THEN
4173
4174 OPEN csr_get_mapping_name (
4175 'attribute18',
4176 p_attribute1.time_attribute_id,
4177 p_attribute1.object_version_number
4178 );
4179 FETCH csr_get_mapping_name INTO l_mapping_name;
4180 CLOSE csr_get_mapping_name;
4181 END IF;
4182 --Changes made to make use of bld_blk_info_type always.
4183 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4184 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE18';
4185 l_change_att_index := l_change_att_index+1;
4186
4187 l_return_mapp := l_return_mapp
4188 || ':'
4189 || l_mapping_name;
4190 END IF;
4191
4192 IF ((NVL (p_attribute1.attribute19, 'NULL') <>
4193 NVL (p_attribute2.attribute19, 'NULL')
4194 OR p_tbb_deleted)
4195 )
4196 THEN
4197 l_mapping_name := NULL;
4198
4199 IF ( p_attribute1.attribute19 IS NOT NULL
4200 OR p_attribute2.attribute19 IS NOT NULL
4201 )
4202 THEN
4203
4204 OPEN csr_get_mapping_name (
4205 'attribute19',
4206 p_attribute1.time_attribute_id,
4207 p_attribute1.object_version_number
4208 );
4209 FETCH csr_get_mapping_name INTO l_mapping_name;
4210 CLOSE csr_get_mapping_name;
4211 END IF;
4212 --Changes made to make use of bld_blk_info_type always.
4213 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4214 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE19';
4215 l_change_att_index := l_change_att_index+1;
4216
4217 l_return_mapp := l_return_mapp
4218 || ':'
4219 || l_mapping_name;
4220 END IF;
4221
4222 IF ((NVL (p_attribute1.attribute20, 'NULL') <>
4223 NVL (p_attribute2.attribute20, 'NULL')
4224 OR p_tbb_deleted)
4225 )
4226 THEN
4227 l_mapping_name := NULL;
4228
4229 IF ( p_attribute1.attribute20 IS NOT NULL
4230 OR p_attribute2.attribute20 IS NOT NULL
4231 )
4232 THEN
4233
4234 OPEN csr_get_mapping_name (
4235 'attribute20',
4236 p_attribute1.time_attribute_id,
4237 p_attribute1.object_version_number
4238 );
4239 FETCH csr_get_mapping_name INTO l_mapping_name;
4240 CLOSE csr_get_mapping_name;
4241 END IF;
4242 --Changes made to make use of bld_blk_info_type always.
4243 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4244 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE20';
4245 l_change_att_index := l_change_att_index+1;
4246
4247 l_return_mapp := l_return_mapp
4248 || ':'
4249 || l_mapping_name;
4250 END IF;
4251
4252 IF ((NVL (p_attribute1.attribute21, 'NULL') <>
4253 NVL (p_attribute2.attribute21, 'NULL')
4254 OR p_tbb_deleted)
4255 )
4256 THEN
4257 l_mapping_name := NULL;
4258
4259 IF ( p_attribute1.attribute21 IS NOT NULL
4260 OR p_attribute2.attribute21 IS NOT NULL
4261 )
4262 THEN
4263
4264
4265 OPEN csr_get_mapping_name (
4266 'attribute21',
4267 p_attribute1.time_attribute_id,
4268 p_attribute1.object_version_number
4269 );
4270 FETCH csr_get_mapping_name INTO l_mapping_name;
4271 CLOSE csr_get_mapping_name;
4272 END IF;
4273 --Changes made to make use of bld_blk_info_type always.
4274 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4275 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE21';
4276 l_change_att_index := l_change_att_index+1;
4277
4278 l_return_mapp := l_return_mapp
4279 || ':'
4280 || l_mapping_name;
4281 END IF;
4282
4283 IF ((NVL (p_attribute1.attribute22, 'NULL') <>
4284 NVL (p_attribute2.attribute22, 'NULL')
4285 OR p_tbb_deleted)
4286 )
4287 THEN
4288 l_mapping_name := NULL;
4289
4290
4291 IF ( p_attribute1.attribute22 IS NOT NULL
4292 OR p_attribute2.attribute22 IS NOT NULL
4293 )
4294 THEN
4295
4296 OPEN csr_get_mapping_name (
4297 'attribute22',
4298 p_attribute1.time_attribute_id,
4299 p_attribute1.object_version_number
4300 );
4301 FETCH csr_get_mapping_name INTO l_mapping_name;
4302 CLOSE csr_get_mapping_name;
4303 END IF;
4304 --Changes made to make use of bld_blk_info_type always.
4305 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4306 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE22';
4307 l_change_att_index := l_change_att_index+1;
4308
4309 l_return_mapp := l_return_mapp
4310 || ':'
4311 || l_mapping_name;
4312 END IF;
4313
4314 IF ((NVL (p_attribute1.attribute23, 'NULL') <>
4315 NVL (p_attribute2.attribute23, 'NULL')
4316 OR p_tbb_deleted)
4317 )
4318 THEN
4319 l_mapping_name := NULL;
4320
4321 IF ( p_attribute1.attribute23 IS NOT NULL
4322 OR p_attribute2.attribute23 IS NOT NULL
4323 )
4324 THEN
4325
4326 OPEN csr_get_mapping_name (
4327 'attribute23',
4328 p_attribute1.time_attribute_id,
4329 p_attribute1.object_version_number
4330 );
4331 FETCH csr_get_mapping_name INTO l_mapping_name;
4332 CLOSE csr_get_mapping_name;
4333 END IF;
4334 --Changes made to make use of bld_blk_info_type always.
4335 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4336 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE23';
4337 l_change_att_index := l_change_att_index+1;
4338
4339 l_return_mapp := l_return_mapp
4340 || ':'
4341 || l_mapping_name;
4342 END IF;
4343
4344 IF ((NVL (p_attribute1.attribute24, 'NULL') <>
4345 NVL (p_attribute2.attribute24, 'NULL')
4346 OR p_tbb_deleted)
4347 )
4348 THEN
4349 l_mapping_name := NULL;
4350
4351 IF ( p_attribute1.attribute24 IS NOT NULL
4352 OR p_attribute2.attribute24 IS NOT NULL
4353 )
4354 THEN
4355
4356 OPEN csr_get_mapping_name (
4357 'attribute24',
4358 p_attribute1.time_attribute_id,
4359 p_attribute1.object_version_number
4360 );
4361 FETCH csr_get_mapping_name INTO l_mapping_name;
4362 CLOSE csr_get_mapping_name;
4363 END IF;
4364 --Changes made to make use of bld_blk_info_type always.
4365 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4366 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE24';
4367 l_change_att_index := l_change_att_index+1;
4368
4369 l_return_mapp := l_return_mapp
4370 || ':'
4371 || l_mapping_name;
4372 END IF;
4373
4374 IF ((NVL (p_attribute1.attribute25, 'NULL') <>
4375 NVL (p_attribute2.attribute25, 'NULL')
4376 OR p_tbb_deleted)
4377 )
4378 THEN
4379 l_mapping_name := NULL;
4380
4381
4382 IF ( p_attribute1.attribute25 IS NOT NULL
4383 OR p_attribute2.attribute25 IS NOT NULL
4384 )
4385 THEN
4386
4387 OPEN csr_get_mapping_name (
4388 'attribute25',
4389 p_attribute1.time_attribute_id,
4390 p_attribute1.object_version_number
4391 );
4392 FETCH csr_get_mapping_name INTO l_mapping_name;
4393 CLOSE csr_get_mapping_name;
4394 END IF;
4395 --Changes made to make use of bld_blk_info_type always.
4396 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4397 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE25';
4398 l_change_att_index := l_change_att_index+1;
4399
4400 l_return_mapp := l_return_mapp
4401 || ':'
4402 || l_mapping_name;
4403 END IF;
4404
4405 IF ((NVL (p_attribute1.attribute26, 'NULL') <>
4406 NVL (p_attribute2.attribute26, 'NULL')
4407 OR p_tbb_deleted)
4408 )
4409 THEN
4410 l_mapping_name := NULL;
4411
4412
4413 IF ( p_attribute1.attribute26 IS NOT NULL
4414 OR p_attribute2.attribute26 IS NOT NULL
4415 )
4416 THEN
4417 OPEN csr_get_mapping_name (
4418 'attribute26',
4419 p_attribute1.time_attribute_id,
4420 p_attribute1.object_version_number
4421 );
4422 FETCH csr_get_mapping_name INTO l_mapping_name;
4423 CLOSE csr_get_mapping_name;
4424 END IF;
4425 --Changes made to make use of bld_blk_info_type always.
4426 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4427 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE26';
4428 l_change_att_index := l_change_att_index+1;
4429
4430
4431 l_return_mapp := l_return_mapp
4432 || ':'
4433 || l_mapping_name;
4434 END IF;
4435
4436 IF ((NVL (p_attribute1.attribute27, 'NULL') <>
4437 NVL (p_attribute2.attribute27, 'NULL')
4438 OR p_tbb_deleted)
4439 )
4440 THEN
4441 l_mapping_name := NULL;
4442
4443 IF ( p_attribute1.attribute27 IS NOT NULL
4444 OR p_attribute2.attribute27 IS NOT NULL
4445 )
4446 THEN
4447
4448 OPEN csr_get_mapping_name (
4449 'attribute27',
4450 p_attribute1.time_attribute_id,
4451 p_attribute1.object_version_number
4452 );
4453 FETCH csr_get_mapping_name INTO l_mapping_name;
4454 CLOSE csr_get_mapping_name;
4455 END IF;
4456 --Changes made to make use of bld_blk_info_type always.
4457 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4458 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE27';
4459 l_change_att_index := l_change_att_index+1;
4460
4461 l_return_mapp := l_return_mapp
4462 || ':'
4463 || l_mapping_name;
4464 END IF;
4465
4466 IF ((NVL (p_attribute1.attribute28, 'NULL') <>
4467 NVL (p_attribute2.attribute28, 'NULL')
4468 OR p_tbb_deleted)
4469 )
4470 THEN
4471 l_mapping_name := NULL;
4472
4473 IF ( p_attribute1.attribute28 IS NOT NULL
4474 OR p_attribute2.attribute28 IS NOT NULL
4475 )
4476 THEN
4477
4478 OPEN csr_get_mapping_name (
4479 'attribute28',
4480 p_attribute1.time_attribute_id,
4481 p_attribute1.object_version_number
4482 );
4483 FETCH csr_get_mapping_name INTO l_mapping_name;
4484 CLOSE csr_get_mapping_name;
4485 END IF;
4486 --Changes made to make use of bld_blk_info_type always.
4487 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4488 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE28';
4489 l_change_att_index := l_change_att_index+1;
4490
4491 l_return_mapp := l_return_mapp
4492 || ':'
4493 || l_mapping_name;
4494 END IF;
4495
4496 IF ((NVL (p_attribute1.attribute29, 'NULL') <>
4497 NVL (p_attribute2.attribute29, 'NULL')
4498 OR p_tbb_deleted)
4499 )
4500 THEN
4501 l_mapping_name := NULL;
4502
4503 IF ( p_attribute1.attribute29 IS NOT NULL
4504 OR p_attribute2.attribute29 IS NOT NULL
4505 )
4506 THEN
4507
4508 OPEN csr_get_mapping_name (
4509 'attribute29',
4510 p_attribute1.time_attribute_id,
4511 p_attribute1.object_version_number
4512 );
4513 FETCH csr_get_mapping_name INTO l_mapping_name;
4514 CLOSE csr_get_mapping_name;
4515 END IF;
4516 --Changes made to make use of bld_blk_info_type always.
4517 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4518 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE29';
4519 l_change_att_index := l_change_att_index+1;
4520
4521 l_return_mapp := l_return_mapp
4522 || ':'
4523 || l_mapping_name;
4524 END IF;
4525
4526 IF ((NVL (p_attribute1.attribute30, 'NULL') <>
4527 NVL (p_attribute2.attribute30, 'NULL')
4528 OR p_tbb_deleted)
4529 )
4530 THEN
4531 l_mapping_name := NULL;
4532
4533 IF ( p_attribute1.attribute30 IS NOT NULL
4534 OR p_attribute2.attribute30 IS NOT NULL
4535 )
4536 THEN
4537
4538 OPEN csr_get_mapping_name (
4539 'attribute30',
4540 p_attribute1.time_attribute_id,
4541 p_attribute1.object_version_number
4542 );
4543 FETCH csr_get_mapping_name INTO l_mapping_name;
4544 CLOSE csr_get_mapping_name;
4545 END IF;
4546 --Changes made to make use of bld_blk_info_type always.
4547 p_change_att_tab(l_change_att_index).attribute_category :=p_attribute1.bld_blk_info_type;
4548 p_change_att_tab(l_change_att_index).changed_attribute :='ATTRIBUTE30';
4549 l_change_att_index := l_change_att_index+1;
4550
4551 l_return_mapp := l_return_mapp
4552 || ':'
4553 || l_mapping_name;
4554 END IF;
4555
4556 if g_debug then
4557 hr_utility.TRACE (l_return_mapp);
4558 end if;
4559 RETURN l_return_mapp;
4560 END compare_tbb_attributes;
4561
4562 FUNCTION compare_time_building_blocks (
4563 p_block1 hxc_self_service_time_deposit.building_block_info,
4564 p_block2 hxc_self_service_time_deposit.building_block_info,
4565 p_tbb_deleted BOOLEAN,
4566 p_change_att_tab IN OUT NOCOPY t_change_att_tab
4567 )
4568 RETURN VARCHAR2
4569 IS
4570 CURSOR csr_get_mapping_name(p_segment varchar2)
4571 IS
4572 SELECT hmc.field_name
4573 FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
4574 WHERE hmc.segment = upper(p_segment)
4575 AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
4576 AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';
4577 /*
4578 CURSOR csr_get_mapping_name(p_segment varchar2)
4579 IS
4580 select substr(fcu.form_left_prompt,1,30)
4581 from hxc_mapping_components mc
4582 ,hxc_bld_blk_info_types bbit
4583 ,fnd_descr_flex_col_usage_tl fcu
4584 where
4585 mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
4586 and fcu.application_column_name = mc.segment
4587 and fcu.descriptive_flex_context_code = bbit.bld_blk_info_type
4588 and fcu.descriptive_flexfield_name = 'OTC Information Types'
4589 and fcu.application_id = 809
4590 and fcu.language = userenv('LANG')
4591 and fcu.application_column_name=p_segment
4592 AND bbit.bld_blk_info_type='BUILDING_BLOCKS';
4593 */
4594
4595 l_ret_val VARCHAR2 (300);
4596 l_mapping_name VARCHAR2(80);
4597 l_change_att_index NUMBER;
4598 BEGIN
4599
4600
4601
4602 IF p_change_att_tab.count = 0 then
4603 l_change_att_index :=1;
4604 ELSE
4605 l_change_att_index := p_change_att_tab.last+1;
4606 END IF;
4607
4608 if g_debug then
4609 hr_utility.TRACE ( 'p_block1.scope'
4610 || p_block1.SCOPE);
4611 hr_utility.TRACE ( 'p_block2.scope'
4612 || p_block2.SCOPE);
4613 end if;
4614
4615 IF (p_block1.SCOPE = 'DETAIL')
4616 THEN
4617
4618 --
4619 -- There is only a subset of things that
4620 -- can be changed in the block, we
4621 -- look for these things
4622 --
4623 -- 1. Measure
4624 IF ((NVL (p_block1.measure,0) <> NVL (p_block2.measure, 0))
4625 OR
4626 (p_block1.measure is not null and p_tbb_deleted ))
4627 THEN
4628 if g_debug then
4629 hr_utility.TRACE ('Before assignment');
4630 end if;
4631 OPEN csr_get_mapping_name('ATTRIBUTE1');
4632 FETCH csr_get_mapping_name INTO l_mapping_name;
4633 CLOSE csr_get_mapping_name;
4634
4635 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4636 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4637 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4638 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE1';
4639 l_change_att_index := p_change_att_tab.last+1;
4640
4641 l_ret_val := 'BUILDING_BLOCKS'||'|'||'ATTRIBUTE1'||'|'||l_mapping_name;
4642 if g_debug then
4643 hr_utility.TRACE ('After Assignment');
4644 end if;
4645 END IF;
4646
4647 if g_debug then
4648 hr_utility.TRACE ('l_ret_val');
4649 end if;
4650
4651 -- 2. Start Time
4652 IF ((NVL (p_block1.start_time, to_date('01-01-0090','dd-mm-rrrr')) <>
4653 NVL (p_block2.start_time, to_date('01-01-0090','dd-mm-rrrr')))
4654 OR
4655 (p_block1.start_time is not null and p_tbb_deleted )
4656 )
4657 THEN
4658 if g_debug then
4659 hr_utility.TRACE ( 'l_ret_val'
4660 || l_ret_val);
4661 end if;
4662 OPEN csr_get_mapping_name('ATTRIBUTE2');
4663 FETCH csr_get_mapping_name INTO l_mapping_name;
4664 CLOSE csr_get_mapping_name;
4665
4666 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4667 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4668 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4669 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE2';
4670 l_change_att_index := p_change_att_tab.last+1;
4671
4672 l_ret_val := l_ret_val || ':' || 'BUILDING_BLOCKS'||'|'||'ATTRIBUTE2'||'|'||l_mapping_name;
4673
4674 END IF;
4675
4676
4677 -- 3. Stop Time
4678 IF ((NVL (p_block1.stop_time, to_date('01-01-0090','dd-mm-rrrr')) <>
4679 NVL (p_block2.stop_time, to_date('01-01-0090','dd-mm-rrrr')))
4680 OR
4681 (p_block1.stop_time is not null and p_tbb_deleted )
4682 )
4683 THEN
4684 OPEN csr_get_mapping_name('ATTRIBUTE3');
4685 FETCH csr_get_mapping_name INTO l_mapping_name;
4686 CLOSE csr_get_mapping_name;
4687 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4688 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4689 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4690 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE3';
4691
4692 l_change_att_index := p_change_att_tab.last+1;
4693
4694 l_ret_val := l_ret_val || ':' || 'BUILDING_BLOCKS'||'|'||'ATTRIBUTE3'||'|'||l_mapping_name;
4695 END IF;
4696
4697
4698 -- 4. Comment
4699
4700 IF ((NVL (p_block1.comment_text, 'NULL') <>
4701 NVL (p_block2.comment_text, 'NULL'))
4702 OR
4703 (p_block1.comment_text is not null and p_tbb_deleted )
4704 )
4705 THEN
4706 OPEN csr_get_mapping_name('ATTRIBUTE4');
4707 FETCH csr_get_mapping_name INTO l_mapping_name;
4708 CLOSE csr_get_mapping_name;
4709 p_change_att_tab(l_change_att_index).attribute_category :=NULL;
4710 p_change_att_tab(l_change_att_index).changed_attribute :=l_mapping_name;
4711 p_change_att_tab(l_change_att_index).org_attribute_category :='BUILDING_BLOCKS';
4712 p_change_att_tab(l_change_att_index).org_changed_attribute :='ATTRIBUTE4';
4713 l_change_att_index := p_change_att_tab.last+1;
4714
4715 l_ret_val := l_ret_val || ':' ||'BUILDING_BLOCKS'||'|'||'ATTRIBUTE4'||'|'||l_mapping_name;
4716
4717 END IF;
4718 END IF;
4719 RETURN l_ret_val;
4720 END compare_time_building_blocks;
4721
4722 PROCEDURE execute_change_ter (
4723 p_tco_bb IN hxc_self_service_time_deposit.timecard_info,
4724 p_tco_att IN hxc_self_service_time_deposit.building_block_attribute_info,
4725 p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
4726 p_message_level VARCHAR2,
4727 p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%ROWTYPE
4728 )
4729 IS
4730 /* CURSOR csr_get_mapping_name(p_segment varchar2)
4731 IS
4732 SELECT hmc.field_name
4733 FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
4734 WHERE hmc.segment = upper(p_segment)
4735 AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
4736 AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';*/
4737
4738 l_time_category_id hxc_time_categories.time_category_id%TYPE;
4739 l_tbb_index NUMBER;
4740 l_att_index NUMBER;
4741 l_old_tbb_index NUMBER;
4742 l_old_att_index NUMBER;
4743 l_old_tco_bb hxc_self_service_time_deposit.timecard_info;
4744 l_old_tco_att hxc_self_service_time_deposit.building_block_attribute_info;
4745 l_timecard_info_rec hxc_time_entry_rules_utils_pkg.r_timecard_info;
4746 l_build_change_list VARCHAR2 (2000);
4747 l_change_list VARCHAR2 (2000);
4748 l_time_cat_tab hxc_time_category_utils_pkg.t_time_category;
4749 l_long LONG;
4750 l_operator hxc_time_categories.OPERATOR%TYPE;
4751 l_tc_bb_ok_tab hxc_time_category_utils_pkg.t_tc_bb_ok;
4752 l_tc_bb_ok_tab_old hxc_time_category_utils_pkg.t_tc_bb_ok;
4753 l_tc_bb_ok_string VARCHAR2(32000);
4754 l_tc_bb_not_ok_string VARCHAR2(32000);
4755 n NUMBER;
4756 l_tbb_deleted BOOLEAN;
4757 l_change_att_tab t_change_att_tab;
4758
4759 BEGIN
4760
4761
4762 if g_debug then
4763 hr_utility.TRACE ('Before get_timecard_info');
4764 end if;
4765 get_timecard_info (
4766 p_time_building_blocks=> P_TIME_BUILDING_BLOCKS,
4767 p_timecard_rec=> l_timecard_info_rec
4768 );
4769
4770 if g_debug then
4771 hr_utility.TRACE ('Before populate_old_tco');
4772 end if;
4773 populate_old_tco (
4774 p_timecard_rec=> l_timecard_info_rec,
4775 p_timecard_building_blocks=> l_old_tco_bb,
4776 p_timecard_attributes=> l_old_tco_att
4777 );
4778
4779 IF (p_rule_record.attribute1 IS NOT NULL)
4780 THEN
4781 if g_debug then
4782 hr_utility.TRACE (
4783 'Attribute 1 -- Time Category '
4784 || p_rule_record.attribute1
4785 );
4786 end if;
4787
4788 -- populate the time category bb ok table
4789
4790 hxc_time_category_utils_pkg.evaluate_time_category (
4791 p_time_category_id => p_rule_record.attribute1
4792 , p_tc_bb_ok_tab => l_tc_bb_ok_tab
4793 , p_tc_bb_ok_string => l_tc_bb_ok_string
4794 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string );
4795
4796 -- populate the old attribute table
4797
4798
4799 hxc_time_category_utils_pkg.push_attributes ( l_old_tco_att );
4800
4801
4802 -- populate the time category bb ok table
4803
4804 hxc_time_category_utils_pkg.evaluate_time_category (
4805 p_time_category_id => p_rule_record.attribute1
4806 , p_tc_bb_ok_tab => l_tc_bb_ok_tab_old
4807 , p_tc_bb_ok_string => l_tc_bb_ok_string
4808 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string
4809 , p_use_tc_bb_cache => FALSE );
4810
4811 -- put back the original attributes
4812
4813 hxc_time_category_utils_pkg.push_attributes ( p_tco_att );
4814
4815 END IF;
4816
4817 if g_debug then
4818 hr_utility.TRACE ('Returned from populate_old_tco');
4819 end if;
4820
4821 n:= l_old_tco_bb.first;
4822 loop
4823 exit when not l_old_tco_bb.exists(n);
4824 if g_debug then
4825 hr_utility.trace('ID ' || 'OVN' || 'SCOPE' || 'Changed' || 'New' || 'Date To');
4826 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 ||' '
4827 || l_old_tco_bb(n).changed|| ' ' ||l_old_tco_bb(n).new || to_char(l_old_tco_bb(n).date_to,'dd-mon-rrrr'));
4828 end if;
4829 n := p_time_building_blocks.next(n);
4830 end loop;
4831
4832 l_tbb_index := p_tco_att.FIRST;
4833
4834 LOOP
4835 EXIT WHEN NOT p_tco_att.EXISTS (l_tbb_index);
4836 if g_debug then
4837 hr_utility.TRACE (
4838 p_tco_att (l_tbb_index).time_attribute_id
4839 || ' ' || p_tco_att (l_tbb_index).object_version_number
4840 || 'attribute_category' || p_tco_att (l_tbb_index).attribute_category
4841 || 'attribute1 '|| p_tco_att (l_tbb_index).attribute1
4842 || 'Attribute2 '|| p_tco_att (l_tbb_index).attribute2
4843 );
4844 end if;
4845 l_tbb_index := p_tco_att.NEXT (l_tbb_index);
4846 END LOOP;
4847
4848 if g_debug then
4849 hr_utility.TRACE ('OLD TBB');
4850 end if;
4851 l_old_tbb_index := l_old_tco_att.FIRST;
4852
4853 LOOP
4854 EXIT WHEN NOT l_old_tco_att.EXISTS (l_old_tbb_index);
4855 if g_debug then
4856 hr_utility.TRACE (
4857 l_old_tco_att (l_old_tbb_index).time_attribute_id
4858 || ' '|| l_old_tco_att (l_old_tbb_index).object_version_number
4859 || 'Attribute_category '|| l_old_tco_att (l_old_tbb_index).attribute_category
4860 || 'Attribute1 '|| l_old_tco_att (l_old_tbb_index).attribute1
4861 || 'Attribute2 '|| l_old_tco_att (l_old_tbb_index).attribute2
4862 );
4863 end if;
4864 l_old_tbb_index := l_old_tco_att.NEXT (l_old_tbb_index);
4865 END LOOP;
4866
4867 l_tbb_index := p_tco_bb.FIRST;
4868
4869
4870 /* Loop through Building blocks */
4871 LOOP
4872 if g_debug then
4873 hr_utility.TRACE ('Timecard Loop');
4874 end if;
4875 l_build_change_list := null;
4876 l_tbb_deleted := FALSE;
4877 EXIT WHEN NOT p_tco_bb.EXISTS (l_tbb_index);
4878
4879 L_CHANGE_ATT_TAB.delete;
4880
4881 IF ( p_tco_bb (l_tbb_index).SCOPE = 'DETAIL'
4882 AND p_tco_bb (l_tbb_index).new <> 'Y'
4883 )
4884 THEN
4885
4886
4887 l_old_tbb_index := l_old_tco_bb.FIRST;
4888 if g_debug then
4889 hr_utility.TRACE ('Old Timecard Loop');
4890 end if;
4891 if (trunc(p_tco_bb(l_tbb_index).date_to) = trunc(sysdate)) then
4892 l_tbb_deleted := TRUE;
4893 else
4894 l_tbb_deleted := FALSE;
4895 end if;
4896 LOOP
4897 EXIT WHEN NOT l_old_tco_bb.EXISTS (l_old_tbb_index); -- OR l_tbb_deleted;
4898 l_change_list := NULL;
4899 if g_debug then
4900 hr_utility.TRACE ('TBB Test');
4901 end if;
4902
4903 if g_debug then
4904 hr_utility.trace('new bb/ovn is '||to_char(p_tco_bb(l_tbb_index).time_building_block_id)||':'
4905 ||to_char(p_tco_bb(l_tbb_index).object_version_number));
4906
4907 hr_utility.trace('old bb/ovn is '||to_char(l_old_tco_bb(l_old_tbb_index).time_building_block_id)||':'
4908 ||to_char(l_old_tco_bb(l_old_tbb_index).object_version_number));
4909 end if;
4910
4911 IF ( p_tco_bb (l_tbb_index).time_building_block_id =
4912 l_old_tco_bb (l_old_tbb_index).time_building_block_id
4913 AND p_tco_bb (l_tbb_index).object_version_number =
4914 l_old_tco_bb (l_old_tbb_index).object_version_number
4915 )
4916 THEN
4917
4918 IF ( (p_rule_record.attribute1 IS NULL)
4919 OR ( p_rule_record.attribute1 IS NOT NULL
4920 AND ( l_tc_bb_ok_tab.EXISTS (
4921 p_tco_bb (l_tbb_index).time_building_block_id
4922 )
4923 OR l_tc_bb_ok_tab_old.EXISTS (
4924 p_tco_bb (l_tbb_index).time_building_block_id
4925 )
4926 )
4927 )
4928 )
4929 THEN
4930
4931 -- GPM v115.76 WWB 3027077
4932
4933 -- moved the compare time building blocks to after the
4934 -- above time category check.
4935
4936 l_change_list := NULL;
4937 l_build_change_list := NULL;
4938
4939 l_change_list :=
4940 compare_time_building_blocks (
4941 l_old_tco_bb (l_old_tbb_index),
4942 p_tco_bb (l_tbb_index),
4943 l_tbb_deleted,
4944 l_change_att_tab
4945 );
4946 /*
4947 l_build_change_list :=
4948 l_build_change_list
4949 || l_change_list;
4950
4951
4952 l_build_change_list := l_change_list;
4953 */
4954 l_old_att_index := l_old_tco_att.FIRST;
4955
4956 LOOP
4957 EXIT WHEN NOT l_old_tco_att.EXISTS (
4958 l_old_att_index
4959 );
4960
4961 IF (l_old_tco_att (l_old_att_index).building_block_id =
4962 p_tco_bb (l_tbb_index).time_building_block_id
4963 AND l_old_tco_att(l_old_att_index).attribute_category <> 'REASON'
4964 )
4965 THEN
4966
4967 l_att_index := p_tco_att.FIRST;
4968
4969 LOOP
4970 EXIT WHEN NOT p_tco_att.EXISTS (l_att_index);
4971 IF (l_old_tco_att (l_old_att_index).time_attribute_id =
4972 p_tco_att (l_att_index).time_attribute_id
4973 --AND p_tco_att(l_att_index).changed = 'Y'
4974 )
4975 THEN
4976 l_change_list := NULL;
4977 l_change_list :=
4978 compare_tbb_attributes (
4979 p_tco_att (l_att_index),
4980 l_old_tco_att (l_old_att_index),
4981 l_tbb_deleted,
4982 l_change_att_tab
4983 );
4984 /*
4985 IF (l_change_list IS NOT NULL)
4986 THEN
4987 l_build_change_list :=
4988 l_build_change_list
4989 || l_change_list;
4990 END IF;
4991 */
4992 END IF;
4993
4994 l_att_index := p_tco_att.NEXT (l_att_index);
4995 if g_debug then
4996 hr_utility.TRACE ('After old attr loop');
4997 end if;
4998 END LOOP;
4999 END IF;
5000
5001 l_old_att_index :=l_old_tco_att.NEXT (l_old_att_index);
5002 END LOOP;
5003 END IF; -- TBB in attribute category.
5004 END IF; -- l_tc_bb_ok_tab.EXISTS and l_tc_bb_ok_tab_old.EXISTS check
5005
5006 l_old_tbb_index := l_old_tco_bb.NEXT (l_old_tbb_index);
5007
5008 END LOOP;
5009 END IF;
5010
5011 IF L_CHANGE_ATT_TAB.count >0 then
5012
5013 hxc_alias_utility.time_entry_rules_segment_trans
5014 (
5015 p_timecard_id =>l_timecard_info_rec.timecard_bb_id
5016 ,p_timecard_ovn =>l_timecard_info_rec.timecard_ovn
5017 ,p_start_time =>l_timecard_info_rec.start_date
5018 ,p_stop_time =>l_timecard_info_rec.end_date
5019 ,p_resource_id =>l_timecard_info_rec.resource_id
5020 ,p_attr_change_table =>L_CHANGE_ATT_TAB
5021 );
5022
5023 FOR I IN 1..L_CHANGE_ATT_TAB.COUNT LOOP
5024
5025 IF nvl(L_CHANGE_ATT_TAB(i).attribute_category,'xx') <> 'ATTRIBUTE_CATEGORY'
5026 then
5027 if nvl(L_CHANGE_ATT_TAB(i).org_attribute_category,'XX') ='BUILDING_BLOCKS' then
5028
5029 if l_build_change_list is null then
5030 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;
5031 else
5032 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;
5033 end if;
5034 else
5035 if l_build_change_list is null then
5036 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;
5037 else
5038 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;
5039 end if;
5040 end if;
5041 END IF;
5042
5043 END LOOP;
5044
5045 END IF;
5046
5047
5048 /* if (l_tbb_deleted ) then
5049 open csr_get_mapping_name('attribute1');
5050 fetch csr_get_mapping_name into l_build_change_list;
5051 close csr_get_mapping_name;
5052 end if;*/
5053
5054 IF (l_build_change_list IS NOT NULL)
5055 THEN
5056 add_error_to_table (
5057 p_message_table=> p_message_table,
5058 p_message_name=> 'HXC_AUDIT_MSG',
5059 p_message_token=> 'CHANGE',
5060 p_message_level=> p_message_level,
5061 p_message_field=> substr(l_build_change_list,1,2000),
5062 p_timecard_bb_id=> p_tco_bb (l_tbb_index).time_building_block_id,
5063 p_time_attribute_id=> NULL,
5064 p_timecard_bb_ovn=> p_tco_bb (l_tbb_index).object_version_number,
5065 p_time_attribute_ovn=> NULL
5066 );
5067 END IF;
5068
5069 l_tbb_index := p_tco_bb.NEXT (l_tbb_index);
5070 END LOOP;
5071
5072 END execute_change_ter;
5073
5074 PROCEDURE execute_late_ter (
5075 p_tco_bb IN hxc_self_service_time_deposit.timecard_info,
5076 p_tco_att IN hxc_self_service_time_deposit.building_block_attribute_info,
5077 p_message_table IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
5078 p_message_level VARCHAR2,
5079 p_rule_record hxc_time_entry_rules_utils_pkg.csr_get_rules%ROWTYPE
5080 )
5081 IS
5082 l_time_category_id hxc_time_categories.time_category_id%TYPE;
5083 l_tbb_index NUMBER;
5084 l_tbb_parent_index NUMBER;
5085 l_date_worked DATE;
5086 n NUMBER;
5087 BEGIN
5088
5089
5090
5091 l_time_category_id := p_rule_record.attribute1;
5092
5093 IF (l_time_category_id IS NOT NULL)
5094 THEN
5095 hxc_time_category_utils_pkg.initialise_time_category (
5096 p_time_category_id=> TO_NUMBER (l_time_category_id),
5097 p_tco_att=> p_tco_att
5098 );
5099 l_tbb_index := p_tco_bb.FIRST;
5100 if g_debug then
5101 hr_utility.TRACE ('Outside Loop');
5102 end if;
5103
5104 LOOP
5105 EXIT WHEN NOT p_tco_bb.EXISTS (l_tbb_index);
5106
5107 IF ( p_tco_bb (l_tbb_index).SCOPE = 'DETAIL'
5108 AND p_tco_bb (l_tbb_index).new = 'Y'
5109 -- Bug 2958441
5110 AND p_tco_bb (l_tbb_index).date_to = hr_general.end_of_time
5111 -- Bug 2958441
5112 )
5113 THEN
5114 l_date_worked := NULL;
5115 If (p_tco_bb (l_tbb_index).stop_time is null) then
5116 l_tbb_parent_index := p_tco_bb.first;
5117 LOOP
5118 exit when not p_tco_bb.exists(l_tbb_parent_index);
5119 if (p_tco_bb(l_tbb_parent_index).time_building_block_id = p_tco_bb(l_tbb_index).parent_building_block_id )
5120 THEN
5121 l_date_worked := p_tco_bb(l_tbb_parent_index).start_time;
5122 exit;
5123 end if;
5124 l_tbb_parent_index := p_tco_bb.next(l_tbb_parent_index);
5125 END LOOP;
5126 END IF;
5127
5128 IF (hxc_time_category_utils_pkg.chk_tc_bb_ok (
5129 p_tco_bb (l_tbb_index).time_building_block_id
5130 )
5131 )
5132 THEN
5133 IF (chk_bb_late (
5134 p_stop_time=> p_tco_bb (l_tbb_index).stop_time,
5135 p_st_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute2),
5136 p_qnt_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute3),
5137 p_date_worked => l_date_worked
5138 )
5139 )
5140 THEN
5141 hxc_time_entry_rules_utils_pkg.add_error_to_table (
5142 p_message_table=> p_messages,
5143 p_message_name=> 'HXC_AUDIT_MSG',
5144 p_message_token=> 'LATE',
5145 p_message_level=> p_message_level,
5146 p_message_field=> NULL,
5147 p_timecard_bb_id=> p_tco_bb (l_tbb_index).time_building_block_id,
5148 p_time_attribute_id=> NULL,
5149 p_timecard_bb_ovn=> p_tco_bb (l_tbb_index).object_version_number,
5150 p_time_attribute_ovn=> NULL
5151 );
5152 END IF;
5153 END IF;
5154 END IF;
5155
5156 l_tbb_index := p_tco_bb.NEXT (l_tbb_index);
5157 END LOOP;
5158 ELSE
5159 l_tbb_index := p_tco_bb.FIRST;
5160
5161 LOOP
5162 EXIT WHEN NOT p_tco_bb.EXISTS (l_tbb_index);
5163
5164 IF ( p_tco_bb (l_tbb_index).SCOPE = 'DETAIL'
5165 AND p_tco_bb (l_tbb_index).new = 'Y'
5166 -- Bug 2958441
5167 AND p_tco_bb (l_tbb_index).date_to = hr_general.end_of_time
5168 -- Bug 2958441
5169 )
5170 THEN
5171 l_date_worked := NULL;
5172 If (p_tco_bb (l_tbb_index).stop_time is null) then
5173 l_tbb_parent_index := p_tco_bb.first;
5174 LOOP
5175 exit when not p_tco_bb.exists(l_tbb_parent_index);
5176 if (p_tco_bb(l_tbb_parent_index).time_building_block_id = p_tco_bb(l_tbb_index).parent_building_block_id )
5177 THEN
5178 l_date_worked := p_tco_bb(l_tbb_parent_index).start_time;
5179 exit;
5180 end if;
5181 l_tbb_parent_index := p_tco_bb.next(l_tbb_parent_index);
5182 END LOOP;
5183 END IF;
5184 IF (chk_bb_late (
5185 p_stop_time=> p_tco_bb (l_tbb_index).stop_time,
5186 p_st_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute2),
5187 p_qnt_late_hrs=> fnd_number.canonical_to_number(p_rule_record.attribute3),
5188 p_date_worked => l_date_worked
5189 )
5190 )
5191 THEN
5192 hxc_time_entry_rules_utils_pkg.add_error_to_table (
5193 p_message_table=> p_messages,
5194 p_message_name=> 'HXC_AUDIT_MSG',
5195 p_message_token=> 'LATE' --p_rule_record.rule_outcome
5196 ,
5197 p_message_level=> p_message_level,
5198 p_message_field=> NULL,
5199 p_timecard_bb_id=> p_tco_bb (l_tbb_index).time_building_block_id,
5200 p_time_attribute_id=> NULL,
5201 p_timecard_bb_ovn=> p_tco_bb (l_tbb_index).object_version_number,
5202 p_time_attribute_ovn=> NULL
5203 );
5204 END IF;
5205 END IF;
5206
5207 l_tbb_index := p_tco_bb.NEXT (l_tbb_index);
5208 END LOOP;
5209 END IF;
5210
5211 if g_debug then
5212 hr_utility.TRACE ('CLA Lateeeeeeeeeeeeeee Message Table');
5213 hr_utility.trace('Count ' || p_messages.count);
5214 end if;
5215 n := p_messages.FIRST;
5216
5217 LOOP
5218 EXIT WHEN NOT p_messages.EXISTS (n);
5219
5220 IF (p_messages (n).message_level = 'REASON')
5221 THEN
5222 if g_debug then
5223 hr_utility.TRACE ('Time Building Block Id' || 'Time Building Block OVN' || 'message_name');
5224 hr_utility.trace(p_messages (n).time_building_block_id || ' ' || p_messages (n).time_building_block_ovn || ' ' || p_messages (n).message_name);
5225 end if;
5226 END IF;
5227 n := p_messages.NEXT (n);
5228 END LOOP;
5229 if g_debug then
5230 hr_utility.trace('End Lateeeeeeeeeeeeeee');
5231 end if;
5232 END execute_late_ter;
5233 Begin
5234
5235 g_debug := hr_utility.debug_enabled;
5236
5237 n:= p_time_building_blocks.first;
5238 loop
5239 exit when not p_time_building_blocks.exists(n);
5240 if g_debug then
5241 hr_utility.trace('ID ' || 'OVN' || 'SCOPE' || 'Changed' || 'New' || ' ' || 'Date to');
5242 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 ||' '
5243 || p_time_building_blocks(n).changed|| ' ' ||p_time_building_blocks(n).new || to_char(p_time_building_blocks(n).date_to,'dd-mon-rrrr'));
5244 end if;
5245 n := p_time_building_blocks.next(n);
5246 end loop;
5247 get_timecard_info (
5248 p_time_building_blocks => P_TIME_BUILDING_BLOCKS
5249 , p_timecard_rec => l_timecard_info_rec );
5250 if g_debug then
5251 hr_utility.trace('After get_timecard_info');
5252 hr_utility.trace('start_date ' || l_timecard_info_rec.start_date);
5253 end if;
5254
5255 n := p_messages.first;
5256 loop
5257 exit when not p_messages.exists(n);
5258 if (p_messages(n).message_level = 'ERROR') then
5259 return;
5260 end if;
5261 n := p_messages.next(n);
5262 end loop;
5263 /* Start 2944785
5264 -- GPM v115.55 / v115.56
5265 hxc_preference_evaluation.resource_preferences(p_resource_id => l_timecard_info_rec.resource_id,
5266 p_preference_code => 'TS_PER_AUDIT_REQUIREMENTS',
5267 p_start_evaluation_date => l_timecard_info_rec.start_date,
5268 p_end_evaluation_date => l_timecard_info_rec.end_date,
5269 p_sorted_pref_table => l_prefs );
5270
5271 IF ( l_prefs.COUNT > 1 )
5272 THEN
5273 -- error since cannot have more than one CLA preference in timecard period
5274 -- in the interim take the first available value
5275 l_terg_id := l_prefs(1).attribute1;
5276 ELSE
5277 l_terg_id := l_prefs(1).attribute1;
5278 END IF;
5279 */
5280 l_terg_id := P_TIME_ENTRY_RULE_GROUP_ID;
5281 -- GPM v115.55
5282 -- End 2944785
5283 FOR r_rules IN csr_get_rules ( p_terg_id => l_terg_id
5284 , p_start_date => l_timecard_info_rec.start_date
5285 , p_end_date => l_timecard_info_rec.end_date )
5286 LOOP
5287 IF (r_rules.formula_name = 'HXC_CLA_CHANGE_FORMULA')
5288 THEN
5289 execute_change_ter (
5290 p_tco_bb=> P_TIME_BUILDING_BLOCKS,
5291 p_tco_att=> p_time_attributes,
5292 p_message_table=> p_messages,
5293 p_message_level=> r_rules.rule_outcome,
5294 p_rule_record=> r_rules
5295 );
5296 ELSIF (r_rules.formula_name = 'HXC_CLA_LATE_FORMULA')
5297 THEN
5298 execute_late_ter (
5299 p_tco_bb=> P_TIME_BUILDING_BLOCKS,
5300 p_tco_att=> p_time_attributes,
5301 p_message_table=> p_messages,
5302 p_message_level=> r_rules.rule_outcome,
5303 p_rule_record=> r_rules
5304 );
5305 END IF;
5306 end loop;
5307
5308
5309 if g_debug then
5310 hr_utility.trace('Final Message Table -- Late + Change......');
5311 end if;
5312 n := p_messages.first;
5313 loop
5314 exit when not p_messages.exists(n);
5315 if (p_messages(n).message_level = 'REASON') then
5316 if g_debug then
5317 hr_utility.trace('Time Building Block Id' || p_messages(n).time_building_block_id);
5318 hr_utility.trace('Time Building Block OVN' || p_messages(n).time_building_block_ovn);
5319 hr_utility.trace('Message name' || p_messages(n).message_name);
5320 hr_utility.trace('Message Level' || p_messages(n).message_level);
5321 hr_utility.trace('Message Tokens' || p_messages(n).message_tokens);
5322 hr_utility.trace('Field name' || p_messages(n).message_field);
5323 hr_utility.trace('Recipient Application ID' || p_messages(n).message_name);
5324 end if;
5325 end if;
5326 n := p_messages.next(n);
5327 end loop;
5328
5329 if g_debug then
5330 hr_utility.trace('End of execute CLA time entry rules');
5331 end if;
5332
5333 end EXECUTE_CLA_TIME_ENTRY_RULES;
5334
5335 PROCEDURE GET_PROMPTS (p_block_id IN NUMBER,
5336 p_blk_ovn IN NUMBER,
5337 p_attribute IN VARCHAR2,
5338 p_blk_type IN VARCHAR2,
5339 p_prompt IN OUT NOCOPY VARCHAR2)
5340 IS
5341 CURSOR C_GET_PROMPT_NAME(p_attribute VARCHAR2) IS
5342 SELECT substr(fcu.form_left_prompt,1,30) prompt
5343 FROM hxc_mapping_components mc
5344 ,hxc_bld_blk_info_types bbit
5345 ,fnd_descr_flex_col_usage_tl fcu
5346 ,hxc_time_attributes hta
5347 WHERE mc.SEGMENT= UPPER(p_attribute) --mapping_component_id = p_comp_id
5348 AND mc.bld_blk_info_type_id = hta.bld_blk_info_type_id
5349 AND hta.time_attribute_id = p_block_id
5350 AND hta.object_version_number = p_blk_ovn
5351 AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
5352 AND fcu.application_column_name = mc.segment
5353 AND fcu.descriptive_flex_context_code = bbit.bld_blk_info_type
5354 AND fcu.descriptive_flexfield_name = 'OTC Information Types'
5355 AND fcu.application_id = 809
5356 AND fcu.language = userenv('LANG');
5357
5358 CURSOR C_GET_PROMPT_BLK(p_attribute VARCHAR2) is
5359 SELECT hmc.field_name
5360 FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
5361 WHERE hmc.segment = upper(p_attribute)
5362 AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
5363 AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';
5364 l_prompt_name varchar2(30);
5365 Begin
5366 g_debug := hr_utility.debug_enabled;
5367
5368 if (p_blk_type = 'BUILDING_BLOCK' ) then
5369 open c_get_prompt_blk(p_attribute);
5370 fetch c_get_prompt_blk into l_prompt_name;
5371 if g_debug then
5372 hr_utility.trace('Prompt ' || l_prompt_name);
5373 end if;
5374 if c_get_prompt_blk%FOUND then
5375 p_prompt := l_prompt_name;
5376 end if;
5377 close c_get_prompt_blk;
5378 else
5379 open c_get_prompt_name (p_attribute);
5380 fetch c_get_prompt_name into l_prompt_name;
5381 if c_get_prompt_name%FOUND then
5382 p_prompt := l_prompt_name;
5383 end if;
5384 close c_get_prompt_name;
5385 end if;
5386 END GET_PROMPTS;
5387
5388
5389
5390 PROCEDURE publish_message (
5391 p_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
5392 , p_message_level in VARCHAR2 DEFAULT 'ERROR'
5393 , p_token_name in VARCHAR2 DEFAULT NULL
5394 , p_token_value in VARCHAR2 DEFAULT NULL
5395 , p_application_short_name IN VARCHAR2 default 'HXC'
5396 , p_time_building_block_id in NUMBER
5397 , p_time_attribute_id in NUMBER DEFAULT NULL
5398 , p_message_extent in VARCHAR2 DEFAULT NULL ) IS
5399
5400
5401 l_message_table hxc_message_table_type := hxc_message_table_type();
5402
5403 l_ind PLS_INTEGER;
5404
5405 l_token_string VARCHAR2(4000) := NULL;
5406
5407 BEGIN
5408
5409 g_debug := hr_utility.debug_enabled;
5410
5411 IF ( p_token_name is not null )
5412 THEN
5413
5414 if g_debug then
5415 hr_utility.trace('GAZ token is '||p_token_name);
5416 hr_utility.trace('GAZ length P token value is '||to_char(length(p_token_value)));
5417 end if;
5418
5419 l_token_string := SUBSTR(UPPER(p_token_name)||'&'||p_token_Value,1,4000);
5420
5421 if g_debug then
5422 hr_utility.trace('GAZ length token string is '||to_char(length(l_token_string)));
5423 end if;
5424
5425 END IF;
5426
5427 publish_message (
5428 p_name => p_name
5429 , p_message_level => p_message_level
5430 , p_token_string => l_token_string
5431 , p_application_short_name => p_application_short_name
5432 , p_time_building_block_id => p_time_building_block_id
5433 , p_time_attribute_id => p_time_attribute_id
5434 , p_message_extent => p_message_extent );
5435
5436 END publish_message;
5437
5438
5439 PROCEDURE publish_message (
5440 p_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
5441 , p_message_level in VARCHAR2 DEFAULT 'ERROR'
5442 , p_token_string in VARCHAR2 DEFAULT NULL
5443 , p_application_short_name IN VARCHAR2 default 'HXC'
5444 , p_time_building_block_id in NUMBER
5445 , p_time_attribute_id in NUMBER DEFAULT NULL
5446 , p_message_extent in VARCHAR2 DEFAULT NULL ) IS
5447
5448
5449
5450 l_message_table hxc_message_table_type := hxc_message_table_type();
5451
5452 l_ind PLS_INTEGER;
5453
5454 l_token_string VARCHAR2(4000) := NULL;
5455
5456 BEGIN
5457
5458 IF ( p_token_string is not null )
5459 THEN
5460
5461 l_token_string := SUBSTR(p_token_string,1,4000);
5462
5463 END IF;
5464
5465 l_message_table.extend;
5466
5467 l_ind := l_message_table.last;
5468
5469 l_message_table(l_ind) := hxc_message_type( p_name,
5470 p_message_level,
5471 NULL,
5472 l_token_string,
5473 p_application_short_name,
5474 p_time_building_block_id,
5475 NULL,
5476 p_time_attribute_id,
5477 NULL,
5478 p_message_extent );
5479
5480 hxc_timecard_message_helper.processErrors(p_messages => l_message_table);
5481
5482 END publish_message;
5483
5484 function return_archived_status(p_period in r_period)
5485 return boolean is
5486
5487
5488 cursor csr_status(p_start_date date,p_end_date date) is
5489 select 'Y' from hxc_data_sets
5490 where status in ('OFF_LINE','BACKUP_IN_PROGRESS','RESTORE_IN_PROGRESS')
5491 and trunc(p_start_date) <=end_date
5492 and trunc(p_end_date) >=start_date;
5493
5494 l_dummy varchar2(1);
5495 l_period varchar2(100);
5496
5497 begin
5498
5499 open csr_status(p_period.period_start,p_period.period_end);
5500 fetch csr_status into l_dummy;
5501 if(csr_status%found) then
5502 close csr_status;
5503 return true;
5504 end if;
5505
5506 close csr_status;
5507
5508 if(p_period.db_pre_period_start is not null and p_period.db_pre_period_end is not null) then
5509 open csr_status(p_period.db_pre_period_start,p_period.db_pre_period_end);
5510 fetch csr_status into l_dummy;
5511 if(csr_status%found) then
5512 close csr_status;
5513 return true;
5514 else
5515 close csr_status;
5516 end if;
5517 end if;
5518
5519
5520 if(p_period.db_ref_period_start is not null and p_period.db_ref_period_end is not null) then
5521 open csr_status(p_period.db_ref_period_start,p_period.db_ref_period_end);
5522 fetch csr_status into l_dummy;
5523 if(csr_status%found) then
5524 close csr_status;
5525 return true;
5526 else
5527 close csr_status;
5528 end if;
5529 end if;
5530
5531 return false;
5532
5533 end return_archived_status;
5534
5535 function check_valid_calc_date_accrual(p_resource_id NUMBER, p_calculate_date DATE) return varchar2
5536 is
5537
5538 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
5539 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
5540
5541 l_emp_hire_date date;
5542
5543 begin
5544
5545 OPEN emp_hire_info (p_resource_id);
5546 FETCH emp_hire_info into l_emp_hire_date;
5547 CLOSE emp_hire_info;
5548
5549 if trunc(l_emp_hire_date) >= trunc(p_calculate_date)
5550 then
5551 return 'N';
5552 else
5553 return 'Y';
5554 end if;
5555
5556 end check_valid_calc_date_accrual;
5557
5558 end hxc_time_entry_rules_utils_pkg;