[Home] [Help]
PACKAGE BODY: APPS.HXT_BATCH_VAL
Source
1 PACKAGE BODY hxt_batch_val AS
2 /* $Header: hxtbtval.pkb 120.16 2010/10/01 10:02:58 asrajago ship $ */
3 -- Global package name
4 g_package CONSTANT VARCHAR2 (33) := 'hxt_batch_val.';
5 -- Global Error Level - used to store worst error that occured
6 g_error_level NUMBER (1) := 0;
7 g_tc_error_level NUMBER (1) := 0;
8 g_debug boolean := hr_utility.debug_enabled;
9
10 -- Bug 6785744
11 -- Global variable to record the date inserted into fnd_sessions when
12 -- the process starts. In case the process crosses over a midnight, a new
13 -- date is put in.
14
15 g_start_sysdate DATE := TRUNC(SYSDATE);
16
17 -- Bug 8584436
18 -- Global variables added to pick up error message and description from
19 -- HXT_TIMECARD_VALIDATION
20 g_lookup_error VARCHAR2(500);
21 g_lookup_desc VARCHAR2(500);
22
23
24 FUNCTION error_level
25 RETURN NUMBER
26 IS
27 l_proc VARCHAR2 (72) ;
28 BEGIN
29 g_debug :=hr_utility.debug_enabled;
30 if g_debug then
31 l_proc := g_package
32 || 'error_level';
33 hr_utility.set_location ( 'Entering:'
34 || l_proc, 10);
35 hr_utility.set_location (
36 ' returning error level '
37 || g_error_level,
38 20
39 );
40 hr_utility.set_location ( 'Leaving:'
41 || l_proc, 30);
42 end if;
43 RETURN g_error_level;
44 END error_level;
45
46 FUNCTION tc_error_level
47 RETURN NUMBER
48 IS
49 l_proc VARCHAR2 (72) ;
50 BEGIN
51 g_debug :=hr_utility.debug_enabled;
52 if g_debug then
53 l_proc := g_package
54 || 'tc_error_level';
55 hr_utility.set_location ( 'Entering:'
56 || l_proc, 10);
57 hr_utility.set_location (
58 ' returning timecard error level '
59 || g_tc_error_level,
60 20
61 );
62 hr_utility.set_location ( 'Leaving:'
63 || l_proc, 30);
64 end if;
65 RETURN g_tc_error_level;
66 END tc_error_level;
67
68 PROCEDURE set_tc_error_level (p_tc_error_level IN NUMBER)
69 IS
70 l_proc VARCHAR2 (72) ;
71 BEGIN
72 g_debug :=hr_utility.debug_enabled;
73 if g_debug then
74 l_proc := g_package
75 || 'set_tc_error_level';
76 hr_utility.set_location ( 'Entering:'
77 || l_proc, 10);
78 end if;
79 g_tc_error_level := p_tc_error_level;
80 if g_debug then
81 hr_utility.set_location ( 'Leaving:'
82 || l_proc, 30);
83 end if;
84 END set_tc_error_level;
85
86 PROCEDURE set_tc_error_level (p_valid IN VARCHAR, p_msg_level IN VARCHAR2)
87 IS
88 l_proc VARCHAR2 (72) ;
89 BEGIN
90 g_debug :=hr_utility.debug_enabled;
91 if g_debug then
92 l_proc := g_package
93 || 'set_tc_error_level';
94 hr_utility.set_location ( 'Entering:'
95 || l_proc, 10);
96 end if;
97 IF p_valid = 'N' THEN
98 IF (p_msg_level = 'S') THEN
99 set_tc_error_level (p_tc_error_level => 3);
100 ELSIF (p_msg_level = 'E') THEN
101 set_tc_error_level (p_tc_error_level => 2);
102 ELSIF (p_msg_level = 'W') THEN
103 set_tc_error_level (p_tc_error_level => 1);
104 END IF;
105 END IF;
106
107 if g_debug then
108 hr_utility.set_location ( 'Leaving:'
109 || l_proc, 30);
110 end if;
111 END set_tc_error_level;
112
113 PROCEDURE set_error_level (p_error_level IN NUMBER)
114 IS
115 l_proc VARCHAR2 (72) ;
116 BEGIN
117 g_debug :=hr_utility.debug_enabled;
118 if g_debug then
119 l_proc := g_package
120 || 'set_error_level';
121 hr_utility.set_location ( 'Entering:'
122 || l_proc, 10);
123 end if;
124 g_error_level := p_error_level;
125 if g_debug then
126 hr_utility.set_location ( 'Leaving:'
127 || l_proc, 30);
128 end if;
129 END set_error_level;
130
131 PROCEDURE reset_error_level
132 IS
133 l_proc VARCHAR2 (72) ;
134 BEGIN
135 g_debug :=hr_utility.debug_enabled;
136 if g_debug then
137 l_proc := g_package
138 || 'reset_error_level';
139 hr_utility.set_location ( 'Entering:'
140 || l_proc, 10);
141 end if;
142 g_error_level := 0;
143 if g_debug then
144 hr_utility.set_location ( 'Leaving:'
145 || l_proc, 30);
146 end if;
147 END reset_error_level;
148
149 PROCEDURE set_error_level (p_valid IN VARCHAR, p_msg_level IN VARCHAR2)
150 IS
151 l_proc VARCHAR2 (72) ;
152 BEGIN
153 g_debug :=hr_utility.debug_enabled;
154 if g_debug then
155 l_proc := g_package
156 || 'set_error_level';
157 hr_utility.set_location ( 'Entering:'
158 || l_proc, 10);
159 end if;
160 IF p_valid = 'N'
161 THEN
162 IF (p_msg_level = 'S')
163 THEN
164 set_error_level (p_error_level => 3);
165 ELSIF ((p_msg_level = 'E') AND (NVL (error_level, 0) < 3))
166 THEN
167 set_error_level (p_error_level => 2);
168 ELSIF ((p_msg_level = 'W') AND (NVL (error_level, 0) < 2))
169 THEN
170 set_error_level (p_error_level => 1);
171 END IF;
172 END IF;
173
174 if g_debug then
175 hr_utility.set_location ( 'Leaving:'
176 || l_proc, 30);
177 end if;
178 END set_error_level;
179
180 PROCEDURE delete_prev_val_errors (
181 p_batch_id IN hxt_timecards_f.batch_id%TYPE
182 )
183 AS
184 l_proc VARCHAR2 (72) ;
185 BEGIN
186 g_debug :=hr_utility.debug_enabled;
187 if g_debug then
188 l_proc := g_package
189 || 'delete_prev_val_errors';
190 hr_utility.set_location ( 'Entering:'
191 || l_proc, 10);
192 end if;
193 DELETE FROM hxt_errors_x
194 WHERE ppb_id = p_batch_id AND location LIKE 'Validate%';
195
196 if g_debug then
197 hr_utility.set_location ( 'Leaving:'
198 || l_proc, 20);
199 end if;
200 END delete_prev_val_errors;
201
202 PROCEDURE delete_prev_val_errors (p_tim_id IN hxt_timecards_f.id%TYPE)
203 AS
204 l_proc VARCHAR2 (72) ;
205 BEGIN
206 g_debug :=hr_utility.debug_enabled;
207 if g_debug then
208 l_proc := g_package
209 || 'delete_prev_val_errors';
210 hr_utility.set_location ( 'Entering:'
211 || l_proc, 10);
212 end if;
213 DELETE FROM hxt_errors_x
214 WHERE tim_id = p_tim_id AND location LIKE 'Validate%';
215
216 if g_debug then
217 hr_utility.set_location ( 'Leaving:'
218 || l_proc, 20);
219 end if;
220 END delete_prev_val_errors;
221
222 FUNCTION errors_exist (p_tim_id IN hxt_timecards.id%TYPE)
223 RETURN BOOLEAN
224 AS
225 l_proc VARCHAR2 (72) ;
226
227 CURSOR find_errors (p_tim_id hxt_timecards.id%TYPE)
228 IS
229 SELECT 1
230 FROM hxt_errors_x
231 WHERE tim_id = p_tim_id;
232
233 l_find_error NUMBER;
234 l_error_exist BOOLEAN;
235 BEGIN
236 g_debug :=hr_utility.debug_enabled;
237 if g_debug then
238 l_proc := g_package
239 || 'errors_exist';
240 hr_utility.set_location ( 'Entering:'
241 || l_proc, 10);
242 end if;
243 OPEN find_errors (p_tim_id);
244 FETCH find_errors INTO l_find_error;
245
246 IF find_errors%FOUND
247 THEN
248 if g_debug then
249 hr_utility.set_location (
250 ' Errors exist for TC '
251 || p_tim_id,
252 20
253 );
254 end if;
255 l_error_exist := TRUE;
256 ELSE
257 l_error_exist := FALSE;
258 END IF;
259
260 CLOSE find_errors;
261 if g_debug then
262 hr_utility.set_location ( 'Leaving:'
263 || l_proc, 100);
264 end if;
265 RETURN l_error_exist;
266 END errors_exist;
267
268 FUNCTION timecard_end_date (p_tim_id IN hxt_timecards_f.id%TYPE)
269 RETURN per_time_periods.end_date%TYPE
270 AS
271 l_proc VARCHAR2 (72) ;
272
273 CURSOR csr_timecard_end_date (p_tim_id hxt_timecards_f.id%TYPE)
274 IS
275 SELECT ptp.end_date
276 FROM per_time_periods ptp, hxt_timecards_x htx
277 WHERE ptp.time_period_id = htx.time_period_id AND htx.id = p_tim_id;
278
279 l_tc_end_dt per_time_periods.end_date%TYPE;
280 BEGIN
281 g_debug :=hr_utility.debug_enabled;
282 if g_debug then
283 l_proc := g_package
284 || 'timecard_end_date';
285 hr_utility.set_location ( 'Entering:'
286 || l_proc, 10);
287 end if;
288 OPEN csr_timecard_end_date (p_tim_id);
289 FETCH csr_timecard_end_date INTO l_tc_end_dt;
290 CLOSE csr_timecard_end_date;
291 if g_debug then
292 hr_utility.set_location ( ' returning '
293 || l_tc_end_dt, 30);
294 hr_utility.set_location ( 'Leaving:'
295 || l_proc, 20);
296 end if;
297 RETURN l_tc_end_dt;
298 END timecard_end_date;
299
300 FUNCTION person_effective_at_tc_end (
301 p_person_id IN per_people_f.person_id%TYPE,
302 p_tim_id IN hxt_timecards.id%TYPE
303 )
304 RETURN BOOLEAN
305 IS
306 l_proc VARCHAR2 (72);
307
308
309 CURSOR csr_person_effective (
310 p_effective_date per_people_f.effective_end_date%TYPE,
311 p_person_id per_people_f.person_id%TYPE
312 )
313 IS
314 SELECT 1
315 FROM per_people_f ppf
316 WHERE person_id = p_person_id
317 AND p_effective_date BETWEEN ppf.effective_start_date
318 AND ppf.effective_end_date;
319
320 l_person_effective BOOLEAN;
321 l_found_person NUMBER;
322 l_eff_dt per_people_f.effective_end_date%TYPE;
323 BEGIN
324 g_debug :=hr_utility.debug_enabled;
325 if g_debug then
326 l_proc := g_package
327 || 'person_effective_at_tc_end';
328 hr_utility.set_location ( 'Entering:'
329 || l_proc, 10);
330 end if;
331 l_eff_dt := timecard_end_date (p_tim_id => p_tim_id);
332 OPEN csr_person_effective (l_eff_dt, p_person_id);
333 FETCH csr_person_effective INTO l_found_person;
334
335 IF (csr_person_effective%FOUND)
336 THEN
337 if g_debug then
338 hr_utility.set_location (
339 ' Person '
340 || p_person_id
341 || ' is effective on '
342 || l_eff_dt
343 || '.',
344 20
345 );
346 end if;
347 l_person_effective := TRUE;
348 ELSE
349 l_person_effective := FALSE;
350 END IF;
351
352 CLOSE csr_person_effective;
353 if g_debug then
354 hr_utility.set_location ( 'Leaving:'
355 || l_proc, 20);
356 end if;
357 RETURN l_person_effective;
358 END person_effective_at_tc_end;
359
360 PROCEDURE record_error (
361 p_batch_id IN NUMBER,
362 p_period_id IN hxt_timecards.time_period_id%TYPE,
363 p_tim_id IN hxt_timecards.id%TYPE,
364 p_error_code IN VARCHAR2
365 )
366 AS
367 l_proc VARCHAR2 (72) ;
368 l_validate_time CONSTANT VARCHAR2 (72) := 'Validate Time';
369 l_error_msg VARCHAR2 (255);
370 l_valid VARCHAR2 (1);
371 l_msg_level VARCHAR2 (1);
372 BEGIN
373 g_debug :=hr_utility.debug_enabled;
374 if g_debug then
375 l_proc := g_package
376 || 'record_error';
377 hr_utility.set_location ( 'Entering:'
378 || l_proc, 10);
379 end if;
380 hxt_util.set_timecard_error (
381 p_batch_id,
382 p_tim_id,
383 NULL,
384 p_period_id,
385 l_error_msg,
386 l_validate_time,
387 SQLERRM,
388 p_error_code,
389 l_valid,
390 l_msg_level
391 );
392 set_error_level (p_valid => l_valid, p_msg_level => l_msg_level);
393 set_tc_error_level (p_valid => l_valid, p_msg_level => l_msg_level);
394 if g_debug then
395 hr_utility.set_location ( 'Leaving:'
396 || l_proc, 100);
397 end if;
398 END record_error;
399
400 PROCEDURE person_validation (
401 p_batch_id IN NUMBER,
402 p_person_id IN hxt_timecards.for_person_id%TYPE,
403 p_period_id IN hxt_timecards.time_period_id%TYPE,
404 p_tim_id IN hxt_timecards.id%TYPE
405 )
406 AS
407 l_proc VARCHAR2 (72) ;
408 l_error_msg VARCHAR2 (255);
409 l_valid VARCHAR2 (1);
410 l_msg_level VARCHAR2 (1);
411 BEGIN
412 g_debug :=hr_utility.debug_enabled;
413 if g_debug then
414 l_proc := g_package
415 || 'person_validation';
416 hr_utility.set_location ( 'Entering:'
417 || l_proc, 10);
418 end if;
419 IF NOT (person_effective_at_tc_end (
420 p_person_id=> p_person_id,
421 p_tim_id=> p_tim_id
422 )
423 )
424 THEN
425
426 -- Bug 8584436
427 -- Picking and processing the message with tokens before recording
428 -- into hxt_errors_f.
429
430 hxt_util.get_quick_codes(c_39316_person_nf,
431 'HXT_TIMECARD_VALIDATION',
432 808,
433 g_lookup_error,
434 g_lookup_desc);
435
436 IF ( g_lookup_error = 'XXX' )
437 THEN
438 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
439 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
440 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
441 fnd_message.raise_error;
442 END IF;
443
444 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
445 FND_MESSAGE.set_token('PERIOD_END_DATE',period_end_date(p_period_id));
446 FND_MESSAGE.set_token('TIMECARD_ID',p_tim_id);
447 FND_MESSAGE.set_token('NAME',person_name(p_person_id));
448 g_errtab(c_39316_person_nf).errmsg := FND_MESSAGE.GET;
449 g_errtab(c_39316_person_nf).errtype := g_lookup_desc;
450
451
452 record_error (
453 p_batch_id=> p_batch_id,
454 p_tim_id=> p_tim_id,
455 p_period_id=> p_period_id,
456 p_error_code=> c_39316_person_nf
457 );
458 g_errtab.DELETE(c_39316_person_nf);
459 END IF;
460
461 if g_debug then
462 hr_utility.set_location ( 'Leaving:'
463 || l_proc, 100);
464 end if;
465 END person_validation;
466
467 PROCEDURE excess_pto (
468 p_batch_id IN NUMBER,
469 p_calculation_date IN hxt_sum_hours_worked_x.date_worked%TYPE,
470 p_person_id IN hxt_timecards.for_person_id%TYPE,
471 p_period_id IN hxt_timecards.time_period_id%TYPE,
472 p_tim_id IN hxt_timecards.id%TYPE
473 )
474 IS
475 l_proc VARCHAR2 (72) ;
476 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
477 l_charged_hrs NUMBER;
478 l_accrued_hrs NUMBER;
479 BEGIN
480 g_debug :=hr_utility.debug_enabled;
481 if g_debug then
482 l_proc := g_package
483 || 'excess_pto';
484 hr_utility.set_location ( 'Entering:'
485 || l_proc, 10);
486 end if;
487
488 -- M.Bhammar - Bug:5107665
489 -- hr_session_utilities.insert_session_row (TO_DATE (SYSDATE,'YYYY/MM/DD'));
490 -- hr_session_utilities.insert_session_row (SYSDATE); /* Bug 6024976 */
491
492 IF (hxt_util.accrual_exceeded (
493 p_tim_id,
494 -- M.Bhammar - bug 5214727
495 -- timecard_end_date (p_tim_id => p_tim_id),
496 p_calculation_date,
497 l_accrual_plan_name,
498 l_charged_hrs,
499 l_accrued_hrs
500 )
501 )
502 THEN
503
504 -- Bug 8584436
505 -- Picking and processing the message with tokens before recording
506 -- into hxt_errors_f.
507
508 hxt_util.get_quick_codes(c_39335_exceeded_accrued_hrs,
509 'HXT_TIMECARD_VALIDATION',
510 808,
511 g_lookup_error,
512 g_lookup_desc);
513
514 IF ( g_lookup_error = 'XXX' )
515 THEN
516 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
517 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
518 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
519 fnd_message.raise_error;
520 END IF;
521
522 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
523 FND_MESSAGE.set_token('USED_HRS',l_charged_hrs);
524 FND_MESSAGE.set_token('ACCRUED_HRS',l_accrued_hrs);
525 FND_MESSAGE.set_token('NAME',person_name(p_person_id));
526 g_errtab(c_39335_exceeded_accrued_hrs).errmsg := FND_MESSAGE.GET;
527 g_errtab(c_39335_exceeded_accrued_hrs).errtype := g_lookup_desc;
528
529
530 record_error (
531 p_batch_id=> p_batch_id,
532 p_tim_id=> p_tim_id,
533 p_period_id=> p_period_id,
534 p_error_code=> c_39335_exceeded_accrued_hrs
535 );
536 g_errtab.DELETE(c_39335_exceeded_accrued_hrs);
537 END IF;
538
539 -- hr_session_utilities.remove_session_row; /* Bug 6024976 */
540 if g_debug then
541 hr_utility.set_location ( 'Leaving:'
542 || l_proc, 100);
543 end if;
544 END excess_pto;
545
546 FUNCTION primary_assignment_id (
547 p_person_id IN per_people_f.person_id%TYPE,
548 p_effective_date IN DATE
549 )
550 RETURN per_all_assignments_f.assignment_id%TYPE
551 IS
552 l_proc VARCHAR2 (72) ;
553
554 CURSOR csr_prim_asg_id (
555 p_person_id per_people_f.person_id%TYPE,
556 p_effective_date DATE
557 )
558 IS
559
560 SELECT /*+ ORDERED
561 INDEX(paf PER_ASSIGNMENTS_F_N12)
562 INDEX(past PER_ASSIGNMENT_STATUS_TYPE_PK) */
563 /* Hints supplied to always force the correct execution plan */
564 paf.assignment_id
565 FROM
566 per_assignments_f paf,
567 per_assignment_status_types past
568 WHERE
569 paf.person_id = p_person_id AND
570 p_effective_date BETWEEN
571 paf.effective_start_date AND paf.effective_end_date AND
572 paf.primary_flag = 'Y'
573 AND
574 past.assignment_status_type_id = paf.assignment_status_type_id AND
575 past.per_system_status = 'ACTIVE_ASSIGN';
576
577 l_prim_asg_id per_all_assignments_f.assignment_id%TYPE;
578 BEGIN
579 g_debug :=hr_utility.debug_enabled;
580 if g_debug then
581 l_proc := g_package
582 || 'primary_assignment_id';
583 hr_utility.set_location ( 'Entering:'
584 || l_proc, 10);
585 end if;
586 OPEN csr_prim_asg_id (p_person_id, p_effective_date);
587 FETCH csr_prim_asg_id INTO l_prim_asg_id;
588 CLOSE csr_prim_asg_id;
589 if g_debug then
590 hr_utility.set_location ( ' returning '
591 || l_prim_asg_id, 20);
592 hr_utility.set_location ( 'Leaving:'
593 || l_proc, 30);
594 end if;
595 RETURN l_prim_asg_id;
596 END primary_assignment_id;
597
598 FUNCTION holiday_calendar_id (
599 p_person_id IN per_people_f.person_id%TYPE,
600 p_effective_date IN DATE
601 )
602 RETURN hxt_holiday_calendars.id%TYPE
603 IS
604 l_proc VARCHAR2 (72) ;
605 l_holiday_calendar_id hxt_holiday_calendars.id%TYPE;
606 BEGIN
607 g_debug :=hr_utility.debug_enabled;
608 if g_debug then
609 l_proc := g_package
610 || 'holiday_calendar_id';
611 hr_utility.set_location ( 'Entering:'
612 || l_proc, 10);
613 end if;
614 l_holiday_calendar_id :=
615 holiday_calendar_id (
616 p_assignment_id=> primary_assignment_id (
617 p_person_id=> p_person_id,
618 p_effective_date=> p_effective_date
619 ),
620 p_effective_date=> p_effective_date
621 );
622 if g_debug then
623 hr_utility.set_location (
624 ' returning '
625 || l_holiday_calendar_id,
626 20
627 );
628 hr_utility.set_location ( 'Leaving:'
629 || l_proc, 30);
630 end if;
631 RETURN l_holiday_calendar_id;
632 END holiday_calendar_id;
633
634 FUNCTION holiday_calendar_id (
635 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
636 p_effective_date IN DATE
637 )
638 RETURN hxt_holiday_calendars.id%TYPE
639 IS
640 l_proc VARCHAR2 (72) ;
641
642 CURSOR csr_holiday_calendar_id (
643 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
644 p_effective_date DATE
645 )
646 IS
647 SELECT hhc.id
648 FROM hxt_holiday_calendars hhc,
649 hxt_earning_policies hep,
650 hxt_add_assign_info_f haaif
651 WHERE p_effective_date BETWEEN hhc.effective_start_date
652 AND hhc.effective_end_date
653 AND hep.hcl_id = hhc.id
654 AND p_effective_date BETWEEN hep.effective_start_date
655 AND hep.effective_end_date
656 AND haaif.earning_policy = hep.id
657 AND p_effective_date BETWEEN haaif.effective_start_date
658 AND haaif.effective_end_date
659 AND haaif.assignment_id = p_assignment_id;
660
661 l_holiday_calendar_id hxt_holiday_calendars.id%TYPE;
662 BEGIN
663 g_debug :=hr_utility.debug_enabled;
664 if g_debug then
665 l_proc := g_package
666 || 'holiday_calendar_id';
667 hr_utility.set_location ( 'Entering:'
668 || l_proc, 10);
669 end if;
670 OPEN csr_holiday_calendar_id (p_assignment_id, p_effective_date);
671 FETCH csr_holiday_calendar_id INTO l_holiday_calendar_id;
672 CLOSE csr_holiday_calendar_id;
673 if g_debug then
674 hr_utility.set_location (
675 ' returning '
676 || l_holiday_calendar_id,
677 20
678 );
679 hr_utility.set_location ( 'Leaving:'
680 || l_proc, 30);
681 end if;
682 RETURN l_holiday_calendar_id;
683 END holiday_calendar_id;
684
685 FUNCTION holiday_element_id (p_hol_cal_id IN hxt_holiday_calendars.id%TYPE)
686 RETURN hxt_holiday_calendars.element_type_id%TYPE
687 IS
688 l_proc VARCHAR2 (72);
689
690 CURSOR csr_holiday_element_id (
691 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
692 )
693 IS
694 SELECT hhc.element_type_id
695 FROM hxt_holiday_calendars hhc
696 WHERE hhc.id = p_hol_cal_id;
697
698 l_holiday_element_id hxt_holiday_calendars.element_type_id%TYPE;
699 BEGIN
700 g_debug :=hr_utility.debug_enabled;
701 if g_debug then
702 l_proc := g_package
703 || 'holiday_element_id';
704 hr_utility.set_location ( 'Entering:'
705 || l_proc, 10);
706 hr_utility.set_location ( ' p_hol_cal_id IN = '
707 || p_hol_cal_id, 20);
708 end if;
709 OPEN csr_holiday_element_id (p_hol_cal_id);
710 FETCH csr_holiday_element_id INTO l_holiday_element_id;
711 CLOSE csr_holiday_element_id;
712 if g_debug then
713 hr_utility.set_location ( ' returning '
714 || l_holiday_element_id, 30);
715 hr_utility.set_location ( 'Leaving:'
716 || l_proc, 40);
717 end if;
718 RETURN l_holiday_element_id;
719 END holiday_element_id;
720
721 FUNCTION day_is_holiday (
722 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
723 p_day IN DATE
724 )
725 RETURN BOOLEAN
726 IS
727 l_proc VARCHAR2 (72);
728
729 CURSOR csr_holiday_today (
730 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
731 p_day DATE
732 )
733 IS
734 SELECT 1
735 FROM hxt_holiday_calendars hhc, hxt_holiday_days hhd
736 WHERE hhc.id = p_hol_cal_id
737 AND hhc.id = hhd.hcl_id
738 AND hhd.holiday_date = p_day
739 AND hhd.hours >= 0;
740
741 l_rec_holiday_today csr_holiday_today%ROWTYPE;
742 l_holiday_today BOOLEAN;
743 BEGIN
744 g_debug :=hr_utility.debug_enabled;
745 if g_debug then
746 l_proc := g_package
747 || 'day_is_holiday';
748 hr_utility.set_location ( 'Entering:'
749 || l_proc, 10);
750 end if;
751 OPEN csr_holiday_today (p_hol_cal_id, p_day);
752 FETCH csr_holiday_today INTO l_rec_holiday_today;
753
754 IF csr_holiday_today%FOUND
755 THEN
756 if g_debug then
757 hr_utility.set_location ( ' '
758 || p_day
759 || ' is a holiday', 20);
760 end if;
761 l_holiday_today := TRUE;
762 ELSE
763 l_holiday_today := FALSE;
764 END IF;
765
766 CLOSE csr_holiday_today;
767 if g_debug then
768 hr_utility.set_location ( 'Leaving:'
769 || l_proc, 30);
770 end if;
771 RETURN l_holiday_today;
772 END day_is_holiday;
773
774 FUNCTION timecard_approved (
775 p_tim_id IN hxt_holiday_calendars.id%TYPE,
776 p_approver_id IN hxt_timecards_f.approv_person_id%TYPE,
777 p_source_flag IN hxt_timecards_f.auto_gen_flag%TYPE
778 )
779 RETURN BOOLEAN
780 IS
781 l_proc VARCHAR2 (72) ;
782 l_approved BOOLEAN;
783 BEGIN
784 g_debug :=hr_utility.debug_enabled;
785 if g_debug then
786 l_proc := g_package
787 || 'timecard_approved';
788 hr_utility.set_location ( 'Entering:'
789 || l_proc, 10);
790 end if;
791 IF (NVL (p_source_flag, 'NOT S') = 'S')
792 THEN
793 if g_debug then
794 hr_utility.set_location ( ' '
795 || p_tim_id
796 || ' is approved', 20);
797 end if;
798 l_approved := TRUE;
799 ELSE
800 IF (p_approver_id IS NOT NULL)
801 THEN
802 if g_debug then
803 hr_utility.set_location ( ' '
804 || p_tim_id
805 || ' is approved', 30);
806 end if;
807 l_approved := TRUE;
808 ELSE
809 l_approved := FALSE;
810 END IF;
811 END IF;
812
813 if g_debug then
814 hr_utility.set_location ( 'Leaving:'
815 || l_proc, 100);
816 end if;
817 RETURN l_approved;
818 END timecard_approved;
819
820 PROCEDURE tcard_approved (
821 p_batch_id IN NUMBER,
822 p_person_id IN hxt_timecards.for_person_id%TYPE,
823 p_period_id IN hxt_timecards.time_period_id%TYPE,
824 p_tim_id IN hxt_timecards.id%TYPE,
825 p_approver_id IN hxt_timecards_f.approv_person_id%TYPE,
826 p_source_flag IN hxt_timecards_f.auto_gen_flag%TYPE
827 )
828 IS
829 l_proc VARCHAR2 (72) ;
830 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
831 l_charged_hrs NUMBER;
832 l_accrued_hrs NUMBER;
833 BEGIN
834 g_debug :=hr_utility.debug_enabled;
835 if g_debug then
836 l_proc := g_package
837 || 'tcard_approved';
838 hr_utility.set_location ( 'Entering:'
839 || l_proc, 10);
840 end if;
841 IF NOT (timecard_approved (
842 p_tim_id=> p_tim_id,
843 p_approver_id=> p_approver_id,
844 p_source_flag=> p_source_flag
845 )
846 )
847 THEN
848
849
850 -- Bug 8584436
851 -- Picking and processing the message with tokens before recording
852 -- into hxt_errors_f.
853
854 hxt_util.get_quick_codes(c_39337_timecard_not_apprved,
855 'HXT_TIMECARD_VALIDATION',
856 808,
857 g_lookup_error,
858 g_lookup_desc);
859
860 IF ( g_lookup_error = 'XXX' )
861 THEN
862 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
863 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
864 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
865 fnd_message.raise_error;
866 END IF;
867
868 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
869 FND_MESSAGE.set_token('PERIOD_END_DATE',period_end_date(p_period_id));
870 FND_MESSAGE.set_token('TIMECARD_ID',p_tim_id);
871 FND_MESSAGE.set_token('NAME',person_name(p_person_id));
872 g_errtab(c_39337_timecard_not_apprved).errmsg := FND_MESSAGE.GET;
873 g_errtab(c_39337_timecard_not_apprved).errtype := g_lookup_desc;
874
875
876 record_error (
877 p_batch_id=> p_batch_id,
878 p_tim_id=> p_tim_id,
879 p_period_id=> p_period_id,
880 p_error_code=> c_39337_timecard_not_apprved
881 );
882 g_errtab.DELETE( c_39337_timecard_not_apprved);
883 END IF;
884
885 if g_debug then
886 hr_utility.set_location ( 'Leaving:'
887 || l_proc, 100);
888 end if;
889 END tcard_approved;
890
891 FUNCTION legislation_code (
892 p_bg_id IN per_business_groups.business_group_id%TYPE
893 )
894 RETURN VARCHAR2 -- per_business_groups.legislation_code%TYPE
895 IS
896 l_proc VARCHAR2 (72) ;
897
898 CURSOR csr_legislation_code (
899 p_bg_id IN hr_all_organization_units.business_group_id%TYPE
900 )
901 IS
902 SELECT pbg.legislation_code
903 FROM per_business_groups pbg
904 WHERE pbg.business_group_id = p_bg_id;
905
906 l_legislation_code per_business_groups.legislation_code%TYPE;
907 BEGIN
908 g_debug :=hr_utility.debug_enabled;
909 if g_debug then
910 l_proc := g_package
911 || 'legislation_code';
912 hr_utility.set_location ( 'Entering:'
913 || l_proc, 10);
914 end if;
915 OPEN csr_legislation_code (p_bg_id);
916 FETCH csr_legislation_code INTO l_legislation_code;
917 CLOSE csr_legislation_code;
918 if g_debug then
919 hr_utility.set_location ( ' returning '
920 || l_legislation_code, 20);
921 hr_utility.set_location ( 'Leaving:'
922 || l_proc, 30);
923 end if;
924 RETURN l_legislation_code;
925 END legislation_code;
926
927 FUNCTION legislation_code (
928 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
929 p_effective_date IN DATE
930 )
931 RETURN per_business_groups.legislation_code%TYPE
932 IS
933 l_proc VARCHAR2 (72) ;
934
935 CURSOR csr_business_group (
936 p_asg_id per_all_assignments_f.assignment_id%TYPE,
937 p_effective_date DATE
938 )
939 IS
940 SELECT paf.business_group_id
941 FROM per_assignments_f paf
942 WHERE paf.assignment_id = p_asg_id
943 AND p_effective_date BETWEEN paf.effective_start_date
944 AND paf.effective_end_date;
945
946 l_business_group_id per_assignments_f.business_group_id%TYPE;
947 l_legislation_code per_business_groups.legislation_code%TYPE;
948 BEGIN
949 g_debug :=hr_utility.debug_enabled;
950 if g_debug then
951 l_proc := g_package
952 || 'legislation_code';
953 hr_utility.set_location ( 'Entering:'
954 || l_proc, 10);
955 end if;
956 OPEN csr_business_group (p_asg_id, p_effective_date);
957 FETCH csr_business_group INTO l_business_group_id;
958 CLOSE csr_business_group;
959 l_legislation_code := legislation_code (p_bg_id => l_business_group_id);
960 if g_debug then
961 hr_utility.set_location ( ' returning '
962 || l_legislation_code, 20);
963 hr_utility.set_location ( 'Leaving:'
964 || l_proc, 30);
965 end if;
966 RETURN l_legislation_code;
967 END legislation_code;
968
969 FUNCTION assignment_is_active (
970 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
971 p_effective_date IN DATE
972 )
973 RETURN BOOLEAN
974 IS
975 l_proc VARCHAR2 (72) ;
976
977 CURSOR csr_active_assignment (
978 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
979 p_effective_date DATE
980 )
981 IS
982 SELECT /*+ ORDERED
983 INDEX(paf PER_ASSIGNMENTS_F_PK)
984 INDEX(past PER_ASSIGNMENT_STATUS_TYPE_PK) */
985 /* Hints supplied to always force the correct execution plan */
986 1
987 FROM
988 per_assignments_f paf,
989 per_assignment_status_types past
990 WHERE
991 paf.assignment_id = p_asg_id AND
992 p_effective_date BETWEEN
993 paf.effective_start_date AND paf.effective_end_date
994 AND
995 past.assignment_status_type_id = paf.assignment_status_type_id AND
996 ( past.business_group_id = paf.business_group_id OR
997 past.business_group_id IS NULL) AND
998 ( past.legislation_code IS NULL OR
999 past.legislation_code = hxt_batch_val.legislation_code(paf.business_group_id)) AND
1000 past.per_system_status = 'ACTIVE_ASSIGN';
1001
1002 l_rec_active_assignment csr_active_assignment%ROWTYPE;
1003 l_active_assignment BOOLEAN;
1004 BEGIN
1005 g_debug :=hr_utility.debug_enabled;
1006 if g_debug then
1007 l_proc := g_package
1008 || 'assignment_is_active';
1009 hr_utility.set_location ( 'Entering:'
1010 || l_proc, 10);
1011 end if;
1012 OPEN csr_active_assignment (p_asg_id, p_effective_date);
1013 FETCH csr_active_assignment INTO l_rec_active_assignment;
1014
1015 IF csr_active_assignment%FOUND
1016 THEN
1017 if g_debug then
1018 hr_utility.set_location (
1019 ' assignment '
1020 || p_asg_id
1021 || ' is active on '
1022 || p_effective_date,
1023 20
1024 );
1025 end if;
1026 l_active_assignment := TRUE;
1027 ELSE
1028 l_active_assignment := FALSE;
1029 END IF;
1030
1031 CLOSE csr_active_assignment;
1032 if g_debug then
1033 hr_utility.set_location ( 'Leaving:'
1034 || l_proc, 30);
1035 end if;
1036 RETURN l_active_assignment;
1037 END assignment_is_active;
1038
1039 PROCEDURE inactive_emp_tcard (
1040 p_batch_id IN NUMBER,
1041 p_person_id IN hxt_timecards.for_person_id%TYPE,
1042 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1043 p_period_id IN hxt_timecards.time_period_id%TYPE,
1044 p_tim_id IN hxt_timecards.id%TYPE,
1045 p_day IN DATE
1046 )
1047 IS
1048 l_proc VARCHAR2 (72) ;
1049 BEGIN
1050 g_debug :=hr_utility.debug_enabled;
1051 if g_debug then
1052 l_proc := g_package
1053 || 'inactive_emp_tcard';
1054 hr_utility.set_location ( 'Entering:'
1055 || l_proc, 10);
1056 end if;
1057 IF NOT (assignment_is_active (
1058 p_asg_id=> p_assignment_id,
1059 p_effective_date=> p_day
1060 )
1061 )
1062 THEN
1063
1064 -- Bug 8584436
1065 -- Picking and processing the message with tokens before recording
1066 -- into hxt_errors_f.
1067
1068 hxt_util.get_quick_codes(c_39317_empl_inactive,
1069 'HXT_TIMECARD_VALIDATION',
1070 808,
1071 g_lookup_error,
1072 g_lookup_desc);
1073
1074
1075 IF ( g_lookup_error = 'XXX' )
1076 THEN
1077 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1078 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
1079 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
1080 fnd_message.raise_error;
1081 END IF;
1082
1083 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
1084 FND_MESSAGE.set_token('PERIOD_END_DATE',period_end_date(p_period_id));
1085 FND_MESSAGE.set_token('TIMECARD_ID',p_tim_id);
1086 FND_MESSAGE.set_token('NAME',person_name(p_person_id));
1087 g_errtab(c_39317_empl_inactive).errmsg := FND_MESSAGE.GET;
1088 g_errtab(c_39317_empl_inactive).errtype := g_lookup_desc;
1089
1090
1091
1092 record_error (
1093 p_batch_id=> p_batch_id,
1094 p_tim_id=> p_tim_id,
1095 p_period_id=> p_period_id,
1096 p_error_code=> c_39317_empl_inactive
1097 );
1098 g_errtab.DELETE(c_39317_empl_inactive);
1099 END IF;
1100
1101 if g_debug then
1102 hr_utility.set_location ( 'Leaving:'
1103 || l_proc, 100);
1104 end if;
1105 END inactive_emp_tcard;
1106
1107 PROCEDURE get_holiday_info (
1108 p_day IN DATE,
1109 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
1110 p_hol_hours OUT NOCOPY hxt_holiday_days.hours%TYPE,
1111 p_hol_element_type_id OUT NOCOPY hxt_holiday_calendars.element_type_id%TYPE
1112 )
1113 IS
1114 l_proc VARCHAR2 (72) := g_package
1115 || 'get_holiday_info';
1116
1117 CURSOR csr_holiday_info (
1118 p_hol_cal_id hxt_holiday_calendars.id%TYPE,
1119 p_day DATE
1120 )
1121 IS
1122 SELECT hhd.hours, hhc.element_type_id
1123 FROM hxt_holiday_days hhd, hxt_holiday_calendars hhc
1124 WHERE hhd.holiday_date = p_day
1125 AND hhc.id = p_hol_cal_id
1126 AND hhd.hcl_id = hhc.id;
1127 BEGIN
1128 g_debug :=hr_utility.debug_enabled;
1129 if g_debug then
1130 hr_utility.set_location ( 'Entering:'
1131 || l_proc, 10);
1132 hr_utility.set_location ( ' p_hol_cal_id = '
1133 || p_hol_cal_id, 20);
1134 hr_utility.set_location ( ' p_day = '
1135 || p_day, 30);
1136 end if;
1137 OPEN csr_holiday_info (p_hol_cal_id, p_day);
1138 FETCH csr_holiday_info INTO p_hol_hours, p_hol_element_type_id;
1139 CLOSE csr_holiday_info;
1140 if g_debug then
1141 hr_utility.set_location (
1142 ' Found p_hol_hours = '
1143 || p_hol_hours,
1144 40
1145 );
1146 hr_utility.set_location (
1147 ' p_hol_element_type_id = '
1148 || p_hol_element_type_id,
1149 50
1150 );
1151 hr_utility.set_location ( 'Leaving:'
1152 || l_proc, 100);
1153 end if;
1154 END get_holiday_info;
1155
1156 PROCEDURE get_holiday_info (
1157 p_person_id IN per_people_f.person_id%TYPE,
1158 p_day IN DATE,
1159 p_effective_date IN DATE,
1160 p_hol_hours OUT NOCOPY hxt_holiday_days.hours%TYPE,
1161 p_hol_element_type_id OUT NOCOPY hxt_holiday_calendars.element_type_id%TYPE
1162 )
1163 IS
1164 l_proc VARCHAR2 (72) ;
1165 BEGIN
1166 g_debug :=hr_utility.debug_enabled;
1167 if g_debug then
1168 l_proc := g_package
1169 || 'get_holiday_info';
1170 hr_utility.set_location ( 'Entering:'
1171 || l_proc, 10);
1172 end if;
1173 get_holiday_info (
1174 p_day => p_day,
1175 p_hol_cal_id=> holiday_calendar_id (
1176 p_person_id=> p_person_id,
1177 p_effective_date=> p_effective_date
1178 ),
1179 p_hol_hours=> p_hol_hours,
1180 p_hol_element_type_id=> p_hol_element_type_id
1181 );
1182 if g_debug then
1183 hr_utility.set_location ( 'Leaving:'
1184 || l_proc, 20);
1185 end if;
1186 END get_holiday_info;
1187
1188 FUNCTION sum_unexploded_hours (
1189 p_tim_id IN hxt_timecards.id%TYPE,
1190 p_day IN hxt_det_hours_worked_f.date_worked%TYPE,
1191 p_hours_type IN hxt_det_hours_worked_f.hours%TYPE DEFAULT NULL
1192 )
1193 RETURN NUMBER
1194 AS
1195 l_proc VARCHAR2 (72) ;
1196
1197 -- Sum the hours of one day
1198 -- .If an hours type is passed in we only sum those hours
1199 -- .If no hours type is passed in we sum all hours, with our without
1200 -- hours override
1201 CURSOR csr_sum_hours (
1202 p_tim_id IN hxt_timecards.id%TYPE,
1203 p_day hxt_det_hours_worked_f.date_worked%TYPE,
1204 p_hours_type hxt_det_hours_worked_f.hours%TYPE
1205 )
1206 IS
1207 SELECT SUM (hshwx.hours)
1208 FROM hxt_sum_hours_worked_x hshwx
1209 WHERE hshwx.tim_id = p_tim_id
1210 AND hshwx.date_worked = p_day
1211 AND ( (p_hours_type IS NULL)
1212 OR ( (p_hours_type IS NOT NULL)
1213 AND (p_hours_type = hshwx.element_type_id)
1214 )
1215 );
1216
1217 l_sum_hours NUMBER;
1218 BEGIN
1219 g_debug :=hr_utility.debug_enabled;
1220 if g_debug then
1221 l_proc := g_package
1222 || 'sum_unexploded_hours';
1223 hr_utility.set_location ( 'Entering:'
1224 || l_proc, 10);
1225 end if;
1226 OPEN csr_sum_hours (p_tim_id, p_day, p_hours_type);
1227 FETCH csr_sum_hours INTO l_sum_hours;
1228 CLOSE csr_sum_hours;
1229 if g_debug then
1230 hr_utility.set_location ( ' returning '
1231 || NVL (l_sum_hours, 0), 30);
1232 hr_utility.set_location ( 'Leaving:'
1233 || l_proc, 20);
1234 end if;
1235 RETURN NVL (l_sum_hours, 0);
1236 END sum_unexploded_hours;
1237
1238 PROCEDURE holiday_mismatch (
1239 p_batch_id IN NUMBER,
1240 p_person_id IN hxt_timecards.for_person_id%TYPE,
1241 p_period_id IN hxt_timecards.time_period_id%TYPE,
1242 p_tim_id IN hxt_timecards.id%TYPE,
1243 p_day IN DATE,
1244 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1245 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1246 )
1247 IS
1248 l_proc VARCHAR2 (72) ;
1249 l_hol_hours hxt_holiday_days.hours%TYPE;
1250 l_hol_element hxt_holiday_calendars.element_type_id%TYPE;
1251 BEGIN
1252 g_debug :=hr_utility.debug_enabled;
1253 if g_debug then
1254 l_proc := g_package
1255 || 'holiday_mismatch';
1256 hr_utility.set_location ( 'Entering:'
1257 || l_proc, 10);
1258 end if;
1259 get_holiday_info (
1260 p_day => p_day,
1261 p_hol_cal_id=> p_hol_cal_id,
1262 p_hol_hours=> l_hol_hours,
1263 p_hol_element_type_id=> l_hol_element
1264 );
1265
1266 IF ( (l_hol_hours <>
1267 sum_unexploded_hours (
1268 p_tim_id=> p_tim_id,
1269 p_day => p_day,
1270 p_hours_type=> l_hol_element
1271 )
1272 )
1273 AND (NVL (p_hours_type, -1) = l_hol_element)
1274 )
1275 THEN
1276
1277 -- Bug 8584436
1278 -- Picking and processing the message with tokens before recording
1279 -- into hxt_errors_f.
1280
1281 hxt_util.get_quick_codes(c_39334_hrs_chged_ne_cal_hrs,
1282 'HXT_TIMECARD_VALIDATION',
1283 808,
1284 g_lookup_error,
1285 g_lookup_desc);
1286
1287 IF ( g_lookup_error = 'XXX' )
1288 THEN
1289 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1290 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
1291 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
1292 fnd_message.raise_error;
1293 END IF;
1294
1295
1296 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
1297 FND_MESSAGE.set_token('DATE_WORKED',to_char(p_day,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1298 FND_MESSAGE.set_token('CAL_HRS',l_hol_hours);
1299 g_errtab(c_39334_hrs_chged_ne_cal_hrs).errmsg := FND_MESSAGE.GET;
1300 g_errtab(c_39334_hrs_chged_ne_cal_hrs).errtype := g_lookup_desc;
1301
1302
1303
1304 record_error (
1305 p_batch_id=> p_batch_id,
1306 p_tim_id=> p_tim_id,
1307 p_period_id=> p_period_id,
1308 p_error_code=> c_39334_hrs_chged_ne_cal_hrs
1309 );
1310
1311 g_errtab.DELETE(c_39334_hrs_chged_ne_cal_hrs);
1312 END IF;
1313
1314 if g_debug then
1315 hr_utility.set_location ( 'Leaving:'
1316 || l_proc, 100);
1317 end if;
1318 END holiday_mismatch;
1319
1320 PROCEDURE holiday_valid (
1321 p_batch_id IN NUMBER,
1322 p_person_id IN hxt_timecards.for_person_id%TYPE,
1323 p_period_id IN hxt_timecards.time_period_id%TYPE,
1324 p_tim_id IN hxt_timecards.id%TYPE,
1325 p_day IN DATE,
1326 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1327 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1328 )
1329 IS
1330 l_proc VARCHAR2 (72) ;
1331 BEGIN
1332 g_debug :=hr_utility.debug_enabled;
1333 if g_debug then
1334 l_proc := g_package
1335 || 'holiday_valid';
1336 hr_utility.set_location ( 'Entering:'
1337 || l_proc, 10);
1338 end if;
1339 IF (NVL (p_hours_type, -1) =
1340 holiday_element_id (p_hol_cal_id => p_hol_cal_id)
1341 )
1342 AND NOT (day_is_holiday (
1343 p_day => p_day,
1344 p_hol_cal_id=> p_hol_cal_id
1345 )
1346 )
1347 THEN
1348
1349 -- Bug 8584436
1350 -- Picking and processing the message with tokens before recording
1351 -- into hxt_errors_f.
1352
1353 hxt_util.get_quick_codes(c_39333_hol_not_valid_on_cal,
1354 'HXT_TIMECARD_VALIDATION',
1355 808,
1356 g_lookup_error,
1357 g_lookup_desc);
1358
1359
1360 IF ( g_lookup_error = 'XXX' )
1361 THEN
1362 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1363 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
1364 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
1365 fnd_message.raise_error;
1366 END IF;
1367
1368 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
1369 FND_MESSAGE.set_token('DATE_WORKED',to_char(p_day,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1370 FND_MESSAGE.set_token('HOLIDAY_CAL',holiday_calendar_name(p_hol_cal_id));
1371 FND_MESSAGE.set_token('ASSIGN',assignment(p_person_id));
1372 g_errtab(c_39333_hol_not_valid_on_cal).errmsg := FND_MESSAGE.GET;
1373 g_errtab(c_39333_hol_not_valid_on_cal).errtype := g_lookup_desc;
1374
1375
1376 record_error (
1377 p_batch_id=> p_batch_id,
1378 p_tim_id=> p_tim_id,
1379 p_period_id=> p_period_id,
1380 p_error_code=> c_39333_hol_not_valid_on_cal
1381 );
1382 g_errtab.DELETE(c_39333_hol_not_valid_on_cal);
1383 END IF;
1384
1385 if g_debug then
1386 hr_utility.set_location ( 'Leaving:'
1387 || l_proc, 20);
1388 end if;
1389 END holiday_valid;
1390
1391 FUNCTION element_link (
1392 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
1393 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1394 p_effective_date IN DATE
1395 )
1396 RETURN pay_element_links_f.element_link_id%TYPE
1397 IS
1398 l_proc VARCHAR2 (72) ;
1399 l_element_link_id NUMBER;
1400 BEGIN
1401 g_debug :=hr_utility.debug_enabled;
1402 if g_debug then
1403 l_proc := g_package
1404 || 'element_link';
1405 hr_utility.set_location ( 'Entering:'
1406 || l_proc, 10);
1407 end if;
1408 BEGIN
1409 SELECT el.element_link_id
1410 INTO l_element_link_id
1411 FROM per_assignments_f asg, pay_element_links_f el
1412 WHERE asg.assignment_id = p_assignment_id
1413 AND el.business_group_id
1414 + 0 = asg.business_group_id
1415 + 0
1416 AND el.element_type_id = p_element_type_id
1417 AND p_effective_date BETWEEN asg.effective_start_date
1418 AND asg.effective_end_date
1419 AND p_effective_date BETWEEN el.effective_start_date
1420 AND el.effective_end_date
1421 AND ( ( el.payroll_id IS NOT NULL
1422 AND el.payroll_id = asg.payroll_id
1423 )
1424 OR ( el.link_to_all_payrolls_flag = 'Y'
1425 AND asg.payroll_id IS NOT NULL
1426 )
1427 OR ( el.payroll_id IS NULL
1428 AND el.link_to_all_payrolls_flag = 'N'
1429 )
1430 )
1431 AND ( el.job_id IS NULL
1432 OR el.job_id = asg.job_id
1433 )
1434 AND ( el.grade_id IS NULL
1435 OR el.grade_id = asg.grade_id
1436 )
1437 AND ( el.position_id IS NULL
1438 OR el.position_id = asg.position_id
1439 )
1440 AND ( el.organization_id IS NULL
1441 OR el.organization_id = asg.organization_id
1442 )
1443 AND ( el.location_id IS NULL
1444 OR el.location_id = asg.location_id
1445 )
1446 AND ( el.pay_basis_id IS NULL
1447 OR el.pay_basis_id = asg.pay_basis_id
1448 )
1449 AND ( el.employment_category IS NULL
1450 OR el.employment_category = asg.employment_category
1451 )
1452 AND ( el.people_group_id IS NULL
1453 OR EXISTS (
1454 SELECT NULL
1455 FROM pay_assignment_link_usages_f alu
1456 WHERE alu.assignment_id = asg.assignment_id
1457 AND alu.element_link_id = el.element_link_id
1458 AND p_effective_date
1459 BETWEEN alu.effective_start_date
1460 AND alu.effective_end_date)
1461 );
1462
1463 if g_debug then
1464 hr_utility.set_location ( 'Leaving:'
1465 || l_proc, 20);
1466 end if;
1467 EXCEPTION
1468 WHEN NO_DATA_FOUND
1469 THEN
1470 l_element_link_id := NULL;
1471 END;
1472
1473 if g_debug then
1474 hr_utility.set_location (
1475 ' returning l_element_link_id = '
1476 || l_element_link_id,
1477 20
1478 );
1479 hr_utility.set_location ( 'Leaving:'
1480 || l_proc, 30);
1481 end if;
1482 RETURN l_element_link_id;
1483 END element_link;
1484
1485 FUNCTION element_linked (
1486 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
1487 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1488 p_effective_date IN DATE
1489 )
1490 RETURN BOOLEAN
1491 IS
1492 l_proc VARCHAR2 (72);
1493 l_element_linked BOOLEAN;
1494 l_element_link_id pay_element_links_f.element_link_id%TYPE;
1495 BEGIN
1496 g_debug :=hr_utility.debug_enabled;
1497 if g_debug then
1498 l_proc := g_package
1499 || 'element_linked';
1500 hr_utility.set_location ( 'Entering:'
1501 || l_proc, 10);
1502 end if;
1503 l_element_link_id :=
1504 element_link (
1505 p_element_type_id=> p_element_type_id,
1506 p_assignment_id=> p_assignment_id,
1507 p_effective_date=> p_effective_date
1508 );
1509
1510 IF (l_element_link_id IS NOT NULL)
1511 THEN
1512 if g_debug then
1513 hr_utility.set_location (
1514 ' '
1515 || p_element_type_id
1516 || ' is a valid element for asg '
1517 || p_assignment_id
1518 || ' on '
1519 || p_effective_date,
1520 20
1521 );
1522 end if;
1523 l_element_linked := TRUE;
1524 ELSE
1525 l_element_linked := FALSE;
1526 END IF;
1527
1528 if g_debug then
1529 hr_utility.set_location ( 'Leaving:'
1530 || l_proc, 30);
1531 end if;
1532 RETURN l_element_linked;
1533 END element_linked;
1534
1535 FUNCTION valid_for_summing (
1536 p_element_id IN pay_element_types_f.element_type_id%TYPE,
1537 p_earnings_category IN hxt_add_elem_info_f.earning_category%TYPE,
1538 p_day IN DATE,
1539 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1540 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
1541 p_valid_earn_cat1 IN hxt_add_elem_info_f.earning_category%TYPE
1542 DEFAULT NULL,
1543 p_valid_earn_cat2 IN hxt_add_elem_info_f.earning_category%TYPE
1544 DEFAULT NULL,
1545 p_valid_earn_cat3 IN hxt_add_elem_info_f.earning_category%TYPE
1546 DEFAULT NULL,
1547 p_valid_earn_cat4 IN hxt_add_elem_info_f.earning_category%TYPE
1548 DEFAULT NULL,
1549 p_valid_earn_cat5 IN hxt_add_elem_info_f.earning_category%TYPE
1550 DEFAULT NULL,
1551 p_valid_earn_cat6 IN hxt_add_elem_info_f.earning_category%TYPE
1552 DEFAULT NULL,
1553 p_valid_earn_cat7 IN hxt_add_elem_info_f.earning_category%TYPE
1554 DEFAULT NULL
1555 )
1556 RETURN BOOLEAN
1557 IS
1558 l_proc VARCHAR2 (72) ;
1559 l_valid_for_summing BOOLEAN;
1560 BEGIN
1561 g_debug :=hr_utility.debug_enabled;
1562 if g_debug then
1563 l_proc := g_package
1564 || 'valid_for_summing';
1565 hr_utility.set_location ( 'Entering:'
1566 || l_proc, 10);
1567 end if;
1568 IF ( (element_linked (
1569 p_element_type_id=> p_element_id,
1570 p_assignment_id=> p_assignment_id,
1571 p_effective_date=> p_day
1572 )
1573 )
1574 AND (p_element_id <>
1575 NVL (holiday_element_id (p_hol_cal_id => p_hol_cal_id), -1)
1576 )
1577 AND (p_earnings_category IN (p_valid_earn_cat1,
1578 p_valid_earn_cat2,
1579 p_valid_earn_cat3,
1580 p_valid_earn_cat4,
1581 p_valid_earn_cat5,
1582 p_valid_earn_cat6,
1583 p_valid_earn_cat7
1584 )
1585 )
1586 )
1587 THEN
1588 if g_debug then
1589 hr_utility.set_location (
1590 ' element '
1591 || p_element_id
1592 || ' is valid for summing',
1593 20
1594 );
1595 end if;
1596 l_valid_for_summing := TRUE;
1597 ELSE
1598 l_valid_for_summing := FALSE;
1599 END IF;
1600
1601 if g_debug then
1602 hr_utility.set_location ( 'Leaving:'
1603 || l_proc, 30);
1604 end if;
1605 RETURN l_valid_for_summing;
1606 END valid_for_summing;
1607
1608 FUNCTION sum_valid_det_hours (
1609 p_tim_id IN hxt_timecards.id%TYPE,
1610 p_day IN DATE,
1611 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1612 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1613 )
1614 RETURN NUMBER
1615 IS
1616 l_proc VARCHAR2 (72) ;
1617
1618 CURSOR csr_det_hours (
1619 p_tim_id hxt_timecards.id%TYPE,
1620 p_date_worked hxt_det_hours_worked_f.date_worked%TYPE
1621 )
1622 IS
1623 SELECT SUM (hdhwx.hours) hours, hdhwx.element_type_id,
1624 haeif.earning_category
1625 FROM hxt_det_hours_worked_x hdhwx, hxt_add_elem_info_f haeif
1626 WHERE hdhwx.tim_id = p_tim_id
1627 AND hdhwx.date_worked = p_date_worked
1628 AND hdhwx.element_type_id = haeif.element_type_id
1629 AND hdhwx.date_worked BETWEEN haeif.effective_start_date
1630 AND haeif.effective_end_date
1631 GROUP BY hdhwx.date_worked,
1632 hdhwx.element_type_id,
1633 haeif.earning_category;
1634
1635 l_summed_hours NUMBER := 0;
1636 BEGIN
1637 g_debug :=hr_utility.debug_enabled;
1638 if g_debug then
1639 l_proc := g_package
1640 || 'sum_valid_det_hours';
1641 hr_utility.set_location ( 'Entering:'
1642 || l_proc, 10);
1643 end if;
1644 FOR rec_det_hours IN csr_det_hours (p_tim_id, p_day)
1645 LOOP
1646 IF (valid_for_summing (
1647 p_element_id=> rec_det_hours.element_type_id,
1648 p_earnings_category=> rec_det_hours.earning_category,
1649 p_assignment_id=> p_assignment_id,
1650 p_day => p_day,
1651 p_hol_cal_id=> p_hol_cal_id,
1652 p_valid_earn_cat1=> 'REG',
1653 p_valid_earn_cat2=> 'OVT',
1654 p_valid_earn_cat3=> 'ABS'
1655 )
1656 )
1657 THEN
1658 l_summed_hours := l_summed_hours
1659 + rec_det_hours.hours;
1660 END IF;
1661 END LOOP;
1662 if g_debug then
1663 hr_utility.set_location (
1664 ' returning l_summed_hours = '
1665 || l_summed_hours,
1666 20
1667 );
1668 hr_utility.set_location ( 'Leaving:'
1669 || l_proc, 30);
1670 end if;
1671 RETURN NVL (l_summed_hours, 0);
1672 END sum_valid_det_hours;
1673
1674 PROCEDURE day_over_24 (
1675 p_batch_id IN NUMBER,
1676 p_person_id IN hxt_timecards.for_person_id%TYPE,
1677 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1678 p_period_id IN hxt_timecards.time_period_id%TYPE,
1679 p_tim_id IN hxt_timecards.id%TYPE,
1680 p_day IN DATE,
1681 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1682 )
1683 IS
1684 l_proc VARCHAR2 (72) ;
1685 l_hol_hours hxt_holiday_days.hours%TYPE;
1686 l_hol_element hxt_holiday_calendars.element_type_id%TYPE;
1687 BEGIN
1688 g_debug :=hr_utility.debug_enabled;
1689 if g_debug then
1690 l_proc := g_package
1691 || 'day_over_24';
1692 hr_utility.set_location ( 'Entering:'
1693 || l_proc, 10);
1694 end if;
1695 IF (sum_valid_det_hours (
1696 p_tim_id=> p_tim_id,
1697 p_day => p_day,
1698 p_assignment_id=> p_assignment_id,
1699 p_hol_cal_id=> p_hol_cal_id
1700 ) > 24
1701 )
1702 THEN
1703
1704 -- Bug 8584436
1705 -- Picking and processing the message with tokens before recording
1706 -- into hxt_errors_f.
1707
1708 hxt_util.get_quick_codes(c_39315_max_hrs_exceeded,
1709 'HXT_TIMECARD_VALIDATION',
1710 808,
1711 g_lookup_error,
1712 g_lookup_desc);
1713
1714 IF ( g_lookup_error = 'XXX' )
1715 THEN
1716 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1717 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
1718 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
1719 fnd_message.raise_error;
1720 END IF;
1721
1722 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
1723 FND_MESSAGE.set_token('DATE_WORKED',to_char(p_day,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1724 FND_MESSAGE.set_token('NAME',person_name(p_person_id));
1725 g_errtab(c_39315_max_hrs_exceeded).errmsg := FND_MESSAGE.GET;
1726 g_errtab(c_39315_max_hrs_exceeded).errtype := g_lookup_desc;
1727
1728
1729 record_error (
1730 p_batch_id=> p_batch_id,
1731 p_tim_id=> p_tim_id,
1732 p_period_id=> p_period_id,
1733 p_error_code=> c_39315_max_hrs_exceeded
1734 );
1735 g_errtab.DELETE(c_39315_max_hrs_exceeded);
1736 END IF;
1737
1738 if g_debug then
1739 hr_utility.set_location ( 'Leaving:'
1740 || l_proc, 100);
1741 end if;
1742 END day_over_24;
1743
1744 PROCEDURE holiday_as_reg (
1745 p_batch_id IN NUMBER,
1746 p_person_id IN hxt_timecards.for_person_id%TYPE,
1747 p_period_id IN hxt_timecards.time_period_id%TYPE,
1748 p_tim_id IN hxt_timecards.id%TYPE,
1749 p_day IN DATE,
1750 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1751 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1752 )
1753 IS
1754 l_proc VARCHAR2 (72) ;
1755 BEGIN
1756 g_debug :=hr_utility.debug_enabled;
1757 if g_debug then
1758 l_proc := g_package
1759 || 'holiday_as_reg';
1760 hr_utility.set_location ( 'Entering:'
1761 || l_proc, 10);
1762 hr_utility.set_location ( ' p_hours_type = '
1763 || p_hours_type, 20);
1764 end if;
1765 IF ( (day_is_holiday (p_day => p_day, p_hol_cal_id => p_hol_cal_id))
1766 AND (NVL (p_hours_type, -1) <>
1767 holiday_element_id (p_hol_cal_id => p_hol_cal_id)
1768 )
1769 )
1770 THEN
1771
1772 -- Bug 8584436
1773 -- Picking and processing the message with tokens before recording
1774 -- into hxt_errors_f.
1775
1776 hxt_util.get_quick_codes(c_39332_day_on_hol_cal,
1777 'HXT_TIMECARD_VALIDATION',
1778 808,
1779 g_lookup_error,
1780 g_lookup_desc);
1781
1782 IF ( g_lookup_error = 'XXX' )
1783 THEN
1784 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1785 fnd_message.set_token('PROCEDURE', 'HXTBTVAL');
1786 fnd_message.set_token('STEP','Invalid HXT_TIMECARD_VALIDATION-'||g_lookup_error);
1787 fnd_message.raise_error;
1788 END IF;
1789
1790 FND_MESSAGE.SET_NAME('HXT',g_lookup_error);
1791 FND_MESSAGE.set_token('DATE_WORKED',to_char(p_day,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
1792 FND_MESSAGE.set_token('HOLIDAY_CAL',holiday_calendar_name(p_hol_cal_id));
1793 FND_MESSAGE.set_token('ASSIGN',assignment(p_person_id));
1794 g_errtab(c_39332_day_on_hol_cal).errmsg := FND_MESSAGE.GET;
1795 g_errtab(c_39332_day_on_hol_cal).errtype := g_lookup_desc;
1796
1797
1798 record_error (
1799 p_batch_id=> p_batch_id,
1800 p_tim_id=> p_tim_id,
1801 p_period_id=> p_period_id,
1802 p_error_code=> c_39332_day_on_hol_cal
1803 );
1804 g_errtab.DELETE(c_39332_day_on_hol_cal);
1805 END IF;
1806
1807 if g_debug then
1808 hr_utility.set_location ( 'Leaving:'
1809 || l_proc, 100);
1810 end if;
1811 END holiday_as_reg;
1812
1813 PROCEDURE perform_holiday_validations (
1814 p_batch_id IN NUMBER,
1815 p_person_id IN hxt_timecards.for_person_id%TYPE,
1816 p_period_id IN hxt_timecards.time_period_id%TYPE,
1817 p_tim_id IN hxt_timecards.id%TYPE,
1818 p_day IN DATE,
1819 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1820 p_hol_cal_id OUT NOCOPY hxt_holiday_calendars.id%TYPE
1821 )
1822 AS
1823 l_proc VARCHAR2 (72);
1824
1825 l_hol_cal_id hxt_holiday_calendars.id%TYPE;
1826 BEGIN
1827
1828 if g_debug then
1829 l_proc := g_package
1830 || 'perform_holiday_validations';
1831 hr_utility.set_location ( 'Entering:'
1832 || l_proc, 10);
1833 end if;
1834 l_hol_cal_id := holiday_calendar_id (
1835 p_person_id=> p_person_id,
1836 p_effective_date=> p_day
1837 );
1838 holiday_mismatch (
1839 p_batch_id=> p_batch_id,
1840 p_person_id=> p_person_id,
1841 p_period_id=> p_period_id,
1842 p_tim_id=> p_tim_id,
1843 p_day => p_day,
1844 p_hours_type=> p_hours_type,
1845 p_hol_cal_id=> l_hol_cal_id
1846 );
1847 holiday_valid (
1848 p_batch_id=> p_batch_id,
1849 p_person_id=> p_person_id,
1850 p_period_id=> p_period_id,
1851 p_tim_id=> p_tim_id,
1852 p_day => p_day,
1853 p_hours_type=> p_hours_type,
1854 p_hol_cal_id=> l_hol_cal_id
1855 );
1856 holiday_as_reg (
1857 p_batch_id=> p_batch_id,
1858 p_person_id=> p_person_id,
1859 p_period_id=> p_period_id,
1860 p_tim_id=> p_tim_id,
1861 p_day => p_day,
1862 p_hours_type=> p_hours_type,
1863 p_hol_cal_id=> l_hol_cal_id
1864 );
1865 p_hol_cal_id := l_hol_cal_id;
1866 if g_debug then
1867 hr_utility.set_location ( 'Leaving:'
1868 || l_proc, 100);
1869 end if;
1870 END perform_holiday_validations;
1871
1872 PROCEDURE perform_day_validations (
1873 p_batch_id IN NUMBER,
1874 p_person_id IN hxt_timecards.for_person_id%TYPE,
1875 p_period_id IN hxt_timecards.time_period_id%TYPE,
1876 p_tim_id IN hxt_timecards.id%TYPE
1877 )
1878 AS
1879 l_proc VARCHAR2 (72) ;
1880
1881 CURSOR csr_days (p_tim_id hxt_timecards.id%TYPE)
1882 IS
1883 SELECT DISTINCT hshwx.date_worked, hshwx.element_type_id,
1884 hshwx.assignment_id
1885 FROM hxt_sum_hours_worked_x hshwx
1886 WHERE tim_id = p_tim_id
1887 AND hshwx.effective_end_date = hr_general.end_of_time;
1888
1889 CURSOR c_no_mid_period_change (p_person_id in number)
1890 is
1891 select 'Y'
1892 from per_all_assignments_f p1,
1893 hxt_timecards_x tim
1894 where tim.effective_start_date between p1.effective_start_date and p1.effective_end_date
1895 and tim.effective_end_date between p1.effective_start_date and p1.effective_end_date
1896 and p1.person_id = p_person_id
1897 and p1.primary_flag = 'Y'
1898 and tim.id = p_tim_id;
1899
1900
1901 l_hol_cal_id hxt_holiday_calendars.id%TYPE;
1902 l_no_mid_period_change varchar2(1);
1903
1904 BEGIN
1905
1906 if g_debug then
1907 l_proc := g_package
1908 || 'perform_day_validations';
1909 hr_utility.set_location ( 'Entering:'
1910 || l_proc, 10);
1911 end if;
1912
1913 open c_no_mid_period_change(p_person_id );
1914 fetch c_no_mid_period_change into l_no_mid_period_change;
1915
1916 if c_no_mid_period_change%notfound then
1917 l_no_mid_period_change := 'N';
1918 end if;
1919
1920 close c_no_mid_period_change;
1921
1922 if g_debug then
1923 hr_utility.trace ('l_no_mid_period_change - '||l_no_mid_period_change);
1924 end if;
1925
1926 FOR rec_days IN csr_days (p_tim_id)
1927 LOOP
1928 perform_holiday_validations (
1929 p_batch_id=> p_batch_id,
1930 p_person_id=> p_person_id,
1931 p_period_id=> p_period_id,
1932 p_tim_id=> p_tim_id,
1933 p_day => rec_days.date_worked,
1934 p_hours_type=> rec_days.element_type_id,
1935 p_hol_cal_id=> l_hol_cal_id
1936 );
1937 inactive_emp_tcard (
1938 p_batch_id=> p_batch_id,
1939 p_person_id=> p_person_id,
1940 p_assignment_id=> rec_days.assignment_id,
1941 p_period_id=> p_period_id,
1942 p_tim_id=> p_tim_id,
1943 p_day => rec_days.date_worked
1944 );
1945 day_over_24 (
1946 p_batch_id=> p_batch_id,
1947 p_person_id=> p_person_id,
1948 p_assignment_id=> rec_days.assignment_id,
1949 p_period_id=> p_period_id,
1950 p_tim_id=> p_tim_id,
1951 p_day => rec_days.date_worked,
1952 p_hol_cal_id=> l_hol_cal_id
1953 );
1954
1955 if l_no_mid_period_change = 'N' then
1956 excess_pto (
1957 p_batch_id=> p_batch_id,
1958 p_calculation_date=>rec_days.date_worked,
1959 p_person_id=> p_person_id,
1960 p_period_id=> p_period_id,
1961 p_tim_id=> p_tim_id
1962 );
1963 end if;
1964 END LOOP;
1965
1966 if l_no_mid_period_change = 'Y' then
1967 excess_pto (
1968 p_batch_id=> p_batch_id,
1969 p_calculation_date=> timecard_end_date (p_tim_id => p_tim_id),
1970 p_person_id=> p_person_id,
1971 p_period_id=> p_period_id,
1972 p_tim_id=> p_tim_id
1973 );
1974 end if;
1975
1976 if g_debug then
1977 hr_utility.set_location ( 'Leaving:'
1978 || l_proc, 100);
1979 end if;
1980 END perform_day_validations;
1981
1982 PROCEDURE validate_tc (
1983 p_batch_id IN NUMBER,
1984 p_tim_id IN hxt_timecards.id%TYPE,
1985 p_person_id IN hxt_timecards.for_person_id%TYPE,
1986 p_period_id IN hxt_timecards.time_period_id%TYPE,
1987 p_approv_person_id IN hxt_timecards.approv_person_id%TYPE,
1988 p_auto_gen_flag IN hxt_timecards.auto_gen_flag%TYPE,
1989 p_error_level IN OUT NOCOPY NUMBER
1990 )
1991 AS
1992 l_proc VARCHAR2 (72) ;
1993 BEGIN
1994 g_debug :=hr_utility.debug_enabled;
1995 if g_debug then
1996 l_proc := g_package
1997 || 'validate_tc';
1998 hr_utility.set_location ( 'Entering:'
1999 || l_proc, 10);
2000 end if;
2001
2002
2003 -- Bug 6785744
2004 -- Process started its run on g_start_sysdate.
2005 -- If the current sysdate is not equal to the start date
2006 -- ie. in case the process ran thru a midnight, update the date
2007 -- and reassign g_start_sysdate.
2008
2009 IF g_start_sysdate <> TRUNC(SYSDATE)
2010 THEN
2011 hr_session_utilities.insert_session_row (SYSDATE);
2012 g_start_sysdate := TRUNC(SYSDATE);
2013 END IF;
2014
2015 perform_day_validations (
2016 p_batch_id=> p_batch_id,
2017 p_person_id=> p_person_id,
2018 p_period_id=> p_period_id,
2019 p_tim_id=> p_tim_id
2020 );
2021
2022 -- Bug 6785744
2023 -- No need to remove the session row after each timecard.
2024 -- hr_session_utilities.remove_session_row; /* Bug 6024976 */
2025
2026 /* M.Bhammar - bug 5214727
2027 excess_pto (
2028 p_batch_id=> p_batch_id,
2029 p_person_id=> p_person_id,
2030 p_period_id=> p_period_id,
2031 p_tim_id=> p_tim_id
2032 );
2033 */
2034 tcard_approved (
2035 p_batch_id=> p_batch_id,
2036 p_person_id=> p_person_id,
2037 p_period_id=> p_period_id,
2038 p_tim_id=> p_tim_id,
2039 p_approver_id=> p_approv_person_id,
2040 p_source_flag=> p_auto_gen_flag
2041 );
2042 person_validation (
2043 p_batch_id=> p_batch_id,
2044 p_person_id=> p_person_id,
2045 p_period_id=> p_period_id,
2046 p_tim_id=> p_tim_id
2047 );
2048 p_error_level := error_level;
2049 if g_debug then
2050 hr_utility.set_location ( 'Leaving:'
2051 || l_proc, 100);
2052 end if;
2053 END validate_tc;
2054
2055 PROCEDURE val_batch (
2056 p_batch_id IN NUMBER,
2057 p_time_period_id IN NUMBER,
2058 p_valid_retcode IN OUT NOCOPY NUMBER,
2059 p_merge_flag IN VARCHAR2 DEFAULT '0',
2060 p_merge_batches OUT NOCOPY HXT_BATCH_PROCESS.MERGE_BATCHES_TYPE_TABLE
2061 )
2062 IS
2063 l_proc VARCHAR2 (72) ;
2064 l_msg_level VARCHAR2 (1) := 'E';
2065 l_valid VARCHAR2 (1) := 'Y';
2066 l_ret NUMBER;
2067 l_msg VARCHAR2 (240);
2068 l_loc VARCHAR2 (70);
2069 l_sql_error VARCHAR2 (80);
2070 l_id NUMBER;
2071 l_cnt BINARY_INTEGER;
2072
2073 CURSOR csr_tcs_in_batch (p_batch_id hxt_timecards_f.batch_id%TYPE)
2074 IS
2075 SELECT tim.id tim_id, tim.for_person_id, tim.time_period_id,
2076 tim.approv_person_id, tim.auto_gen_flag,
2077 tim.approved_timestamp , tim.created_by,
2078 tim.creation_date , tim.last_updated_by,
2079 tim.last_update_date , tim.last_update_login,
2080 tim.payroll_id , tim.status,
2081 tim.effective_start_date , tim.effective_end_date,
2082 tim.object_version_number, tim.rowid
2083 FROM hxt_timecards_x tim
2084 WHERE tim.batch_id = p_batch_id;
2085 BEGIN
2086 g_debug :=hr_utility.debug_enabled;
2087 if g_debug then
2088 l_proc := g_package
2089 || 'val_batch';
2090 hr_utility.set_location ( 'Entering:'
2091 || l_proc, 10);
2092 end if;
2093 reset_error_level;
2094 delete_prev_val_errors (p_batch_id => p_batch_id);
2095
2096 -- Bug 6785744
2097 -- Insert a new row in fnd_sessions before processing this batch.
2098
2099 hr_session_utilities.insert_session_row (SYSDATE);
2100 <<process_all_tcs_in_batch>>
2101 FOR rec_tcs_in_batch IN csr_tcs_in_batch (p_batch_id)
2102 LOOP
2103 set_tc_error_level(p_tc_error_level => 0);
2104
2105 /********Bug: 5037996 **********/
2106
2107 -- IF p_merge_flag = 'Y' THEN /* commented for bug: 5112412 */
2108 delete_prev_val_errors (p_tim_id => rec_tcs_in_batch.tim_id);
2109 -- END IF;
2110
2111 /********Bug: 5037996 **********/
2112
2113 if g_debug then
2114 hr_utility.set_location (
2115 ' process timecard '
2116 || rec_tcs_in_batch.tim_id,
2117 20
2118 );
2119 hr_utility.set_location (
2120 ' Error Level at start = '
2121 || error_level,
2122 30
2123 );
2124 end if;
2125 IF (errors_exist (p_tim_id => rec_tcs_in_batch.tim_id))
2126 THEN
2127 p_valid_retcode := 2;
2128 ELSE
2129 validate_tc (
2130 p_batch_id=> p_batch_id,
2131 p_tim_id=> rec_tcs_in_batch.tim_id,
2132 p_person_id=> rec_tcs_in_batch.for_person_id,
2133 p_period_id=> rec_tcs_in_batch.time_period_id,
2134 p_approv_person_id=> rec_tcs_in_batch.approv_person_id,
2135 p_auto_gen_flag=> rec_tcs_in_batch.auto_gen_flag,
2136 p_error_level=> p_valid_retcode
2137 );
2138 END IF;
2139
2140 -- Bug 6795140
2141
2142 COMMIT;
2143
2144 /********Bug: 5037996 **********/
2145 /*** To record the validated timecards details ***/
2146
2147 IF p_merge_flag = '1' THEN
2148
2149 if g_debug then
2150 hr_utility.trace('Populating merge_batches record'||
2151 ' batch_id: '||p_batch_id||' tc_id '||rec_tcs_in_batch.tim_id);
2152 end if;
2153
2154 l_cnt := NVL(p_merge_batches.LAST,0) +1;
2155 p_merge_batches(l_cnt).batch_id := p_batch_id;
2156 p_merge_batches(l_cnt).tc_id := rec_tcs_in_batch.tim_id;
2157 p_merge_batches(l_cnt).valid_tc_retcode := tc_error_level;
2158 p_merge_batches(l_cnt).tc_rowid := rec_tcs_in_batch.rowid;
2159 p_merge_batches(l_cnt).for_person_id := rec_tcs_in_batch.for_person_id;
2160 p_merge_batches(l_cnt).time_period_id := rec_tcs_in_batch.time_period_id;
2161 p_merge_batches(l_cnt).auto_gen_flag := rec_tcs_in_batch.auto_gen_flag;
2162 p_merge_batches(l_cnt).approv_person_id := rec_tcs_in_batch.approv_person_id;
2163 p_merge_batches(l_cnt).approved_timestamp := rec_tcs_in_batch.approved_timestamp;
2164 p_merge_batches(l_cnt).created_by := rec_tcs_in_batch.created_by;
2165 p_merge_batches(l_cnt).creation_date := rec_tcs_in_batch.creation_date;
2166 p_merge_batches(l_cnt).last_updated_by := rec_tcs_in_batch.last_updated_by;
2167 p_merge_batches(l_cnt).last_update_date := rec_tcs_in_batch.last_update_date;
2168 p_merge_batches(l_cnt).last_update_login := rec_tcs_in_batch.last_update_login;
2169 p_merge_batches(l_cnt).payroll_id := rec_tcs_in_batch.payroll_id;
2170 p_merge_batches(l_cnt).status := rec_tcs_in_batch.status;
2171 p_merge_batches(l_cnt).effective_start_date := rec_tcs_in_batch.effective_start_date;
2172 p_merge_batches(l_cnt).effective_end_date := rec_tcs_in_batch.effective_end_date;
2173 p_merge_batches(l_cnt).object_version_number := rec_tcs_in_batch.object_version_number;
2174
2175 END IF;
2176
2177 /********Bug: 5037996 **********/
2178
2179 END LOOP process_all_tcs_in_batch;
2180
2181 -- Bug 6785744
2182 -- Remove the session row after processing this batch.
2183
2184 hr_session_utilities.remove_session_row;
2185
2186 if g_debug then
2187 hr_utility.set_location ( 'Leaving:'
2188 || l_proc, 100);
2189 end if;
2190 END val_batch;
2191
2192
2193 -- Bug 8584436
2194 -- Added the following functions to return appropriate values for the
2195 -- tokens.
2196
2197 FUNCTION person_name ( p_person_id IN NUMBER)
2198 RETURN VARCHAR2
2199 IS
2200
2201 BEGIN
2202 IF g_name_tab.EXISTS(p_person_id)
2203 THEN
2204 RETURN g_name_tab(p_person_id);
2205 ELSE
2206 SELECT full_name
2207 INTO g_name_tab(p_person_id)
2208 FROM per_all_people_f
2209 WHERE person_id = p_person_id
2210 AND TRUNC(SYSDATE) BETWEEN effective_start_date -- Bug 10143866
2211 AND effective_end_date;
2212 RETURN g_name_tab(p_person_id);
2213 END IF;
2214 END person_name;
2215
2216
2217 FUNCTION holiday_calendar_name ( p_hcl_id IN NUMBER)
2218 RETURN VARCHAR2
2219 IS
2220
2221 BEGIN
2222 IF g_holiday_calendar.EXISTS(p_hcl_id)
2223 THEN
2224 RETURN g_holiday_calendar(p_hcl_id);
2225 ELSE
2226 SELECT name
2227 INTO g_holiday_calendar(p_hcl_id)
2228 FROM hxt_holiday_calendars
2229 WHERE id = p_hcl_id ;
2230 RETURN g_holiday_calendar(p_hcl_id);
2231 END IF;
2232 END holiday_calendar_name;
2233
2234
2235 FUNCTION assignment ( p_person_id IN NUMBER)
2236 RETURN VARCHAR2
2237 IS
2238
2239 BEGIN
2240 IF g_assig_tab.EXISTS(p_person_id)
2241 THEN
2242 RETURN g_assig_tab(p_person_id);
2243 ELSE
2244 SELECT employee_number
2245 INTO g_assig_tab(p_person_id)
2246 FROM per_all_people_f
2247 WHERE person_id = p_person_id
2248 AND TRUNC(SYSDATE) BETWEEN effective_start_date -- Bug 10143866
2249 AND effective_end_date;
2250 RETURN g_assig_tab(p_person_id);
2251 END IF;
2252 END assignment;
2253
2254 FUNCTION period_end_date( p_period_id IN NUMBER)
2255 RETURN VARCHAR2
2256 IS
2257
2258 BEGIN
2259 IF g_period_end_date.EXISTS(p_period_id)
2260 THEN
2261 RETURN g_period_end_date(p_period_id);
2262 ELSE
2263 SELECT TO_CHAR(end_date,FND_profile.value('ICX_DATE_FORMAT_MASK'))
2264 INTO g_period_end_date(p_period_id)
2265 FROM per_time_periods
2266 WHERE time_period_id = p_period_id;
2267 RETURN g_period_end_date(p_period_id);
2268 END IF;
2269
2270 END period_end_date;
2271
2272
2273
2274 END hxt_batch_val;