1 PACKAGE BODY hxt_td_util AS
2 /* $Header: hxttdutl.pkb 120.14.12020000.2 2012/07/04 07:15:23 amnaraya ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4
5
6 -- Bug 12379517
7 -- New types.
8 TYPE VARCHARTAB IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
9 g_proj_pay_intg VARCHARTAB;
10
11
12
13 -- Bug 12379517
14 -- Created this new function call to check person's preferences for PA PAY integration.
15
16 FUNCTION verify_proj_pay ( p_timecard_id IN NUMBER)
17 RETURN BOOLEAN
18 IS
19
20 CURSOR get_period( p_id IN NUMBER)
21 IS SELECT ptp.start_date,
22 ptp.end_date,
23 tim.for_person_id
24 FROM hxt_timecards_f tim,
25 per_time_periods ptp
26 WHERE tim.id = p_id
27 AND tim.time_period_id = ptp.time_period_id ;
28
29 l_start DATE;
30 l_end DATE;
31 l_resource_id NUMBER;
32
33 l_pref_tab hxc_preference_evaluation.t_pref_table;
34
35 BEGIN
36
37
38 -- Get start.end dates of the timecard from per_time_periods.
39 OPEN get_period(p_timecard_id);
40 FETCH get_period INTO l_start,l_end,l_resource_id;
41 CLOSE get_period;
42
43 IF g_debug
44 THEN
45 hr_utility.trace('Verify_proj_pay');
46 hr_utility.trace('p_timecard_id '||p_timecard_id);
47 hr_utility.trace('p_resource_id '||l_resource_id);
48 hr_utility.trace('l_start '||l_start);
49 hr_utility.trace('l_end '||l_end);
50 END IF;
51
52 -- Call preference evaluation.
53 hxc_preference_evaluation.resource_preferences(l_resource_id,
54 l_start,
55 l_end,
56 l_pref_tab);
57
58 IF l_pref_tab.COUNT > 0
59 THEN
60 g_proj_pay_intg(p_timecard_id) := 'N';
61 FOR i IN l_pref_tab.FIRST..l_pref_tab.LAST
62 LOOP
63
64 IF g_debug
65 THEN
66 hr_utility.trace(' Preference - '||l_pref_tab(i).preference_code);
67 hr_utility.trace(' Attribute1 - '||l_pref_tab(i).attribute1);
68 hr_utility.trace(' Attribute2 - '||l_pref_tab(i).attribute2);
69 hr_utility.trace(' Attribute3 - '||l_pref_tab(i).attribute3);
70 hr_utility.trace(' Attribute4 - '||l_pref_tab(i).attribute4);
71 hr_utility.trace(' Attribute5 - '||l_pref_tab(i).attribute5);
72 END IF;
73
74 -- If this is our preference, check if it is set.
75 -- If yes, return TRUE, and set the global variable.
76
77 IF l_pref_tab(i).preference_code = 'TS_PA_PAY_INTG'
78 AND l_pref_tab(i).attribute1 = 'Y'
79 THEN
80 g_proj_pay_intg(p_timecard_id) := 'Y';
81 IF g_debug
82 THEN
83 hr_utility.trace(' Integration is enabled ');
84 END IF;
85 RETURN TRUE;
86 END IF;
87 --There is no ELSE and EXIT loop here because there could be multiple
88 -- preferences ( if there are mid period changes ). Either one of them
89 -- being Y, error should be thrown.
90 END LOOP;
91 END IF;
92
93 IF g_debug
94 THEN
95 hr_utility.trace('Integration not enabled ');
96 END IF;
97 RETURN FALSE;
98
99 END verify_proj_pay;
100
101
102
103 -------------------------------------
104 -- PROCEDURE retro_restrict_edit --
105 -------------------------------------
106 -- The purpose of this procedure is to control the datetrack mode that can
107 -- be used to update a detail record.
108 --
109 -- Once data has been transferred to BEE, we should not allow DT Corrections
110 -- anymore. This correction would overwrite the record in the database and we
111 -- would not know anymore which data was transferred to BEE (and we do need to
112 -- know this as you will see later). So only data that has not been transferred
113 -- yet to BEE can be DT CORRECTED.
114 --
115 -- Data that has been Transferred already can only be DT UPDATED. This will
116 -- retain the record that was transferred and create a new DT record with the
117 -- updated information.
118 --
119 -- The problem with datetrack however is that it does not allow DT UPDATES on
120 -- records that are created on the current session date. E.g. a record that
121 -- was created on 01-JAN-2003 cannot be UPDATED on 01-JAN-2003 because the
122 -- datetrack granularity is on day level so we cannot have a DT record active
123 -- from 01-JAN-2003 00:01 till 01-JAN-2003 12:00 and another one from
124 -- 01-JAN-2003 12:01 till 31-DEC-4712. Because of this DT restriction, we cannot
125 -- allow ANY updates on detail records if they have been transferred already
126 -- and were updated today. The fact that it has already been transferred means
127 -- we need to do a DT UPDATE, but we can't because it was already update today.
128 -- This procedure will raise an error for such a situation.
129 --
130 -- The reason we need to keep records that already have been transferred to BEE
131 -- intact is because any update after that is send as a delta between the old
132 -- (already send record) and the update (new DT) record. A correction would wipe
133 -- out the old record and we would not be able to to a delta anymore.
134
135 -- Added for Bug 6067007
136 -- There was one corner scenario, when the timecard is transferred to BEE
137 -- and the next day a change is made to it. This time, the code allows only
138 -- an update. But once this is transferred to PUI, the status of the detail
139 -- records changes to R. ( for retro ). If you try to update again, the time
140 -- card will let you CORRECT the timecard ( even delete a day's entry and add
141 -- another entry.) Now when this data moves to BEE, it would create overpayment
142 -- because the day is already having an entry for a given attribute, and a newer
143 -- entry is brought in with retro ( this time a different attribute, so the
144 -- existing entry wont be reversed). This can be avoided only by switching to
145 -- UPDATE only allowed while doing a retro change. So after this change, any
146 -- timecard which has history of transfer to BEE can be updated only, no delete
147 -- is allowed.
148
149 PROCEDURE retro_restrict_edit (
150 p_tim_id IN hxt_det_hours_worked_f.tim_id%TYPE,
151 p_session_date IN DATE,
152 o_dt_update_mod OUT NOCOPY VARCHAR2,
153 o_error_message OUT NOCOPY VARCHAR2,
154 o_return_code OUT NOCOPY NUMBER,
155 p_parent_id IN hxt_det_hours_worked_f.parent_id%TYPE,
156 p_do_intg_check IN VARCHAR2
157 )
158 IS
159
160 -- Bug 7359347
161 -- Changed the below cursor to pick up session date from global variable
162 -- instead of fnd_sessions table.
163
164 /*
165 CURSOR csr_not_transferred (
166 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
167 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
168 )
169 IS
170 SELECT 1
171 FROM hxt_det_hours_worked hdhw
172 WHERE hdhw.tim_id = v_tim_id
173 AND hdhw.pay_status = 'P' -- Bug 6067007
174 AND hdhw.pa_status = 'P' -- Bug 6067007
175 AND NOT EXISTS ( SELECT 1
176 FROM hxt_det_hours_worked_f hdhw2
177 WHERE hdhw.tim_id = hdhw2.tim_id
178 AND hdhw2.effective_start_date >
179 hdhw.effective_start_date);
180 */
181 CURSOR csr_not_transferred (
182 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
183 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE,
184 v_session_date DATE
185 )
186 IS
187 SELECT 1
188 FROM hxt_det_hours_worked_f hdhw
189 WHERE hdhw.tim_id = v_tim_id
190 AND v_session_date BETWEEN hdhw.effective_start_date
191 AND hdhw.effective_end_date
192 AND hdhw.pay_status = 'P' -- Bug 6067007
193 AND hdhw.pa_status = 'P' -- Bug 6067007
194 AND NOT EXISTS ( SELECT 1
195 FROM hxt_det_hours_worked_f hdhw2
196 WHERE hdhw.tim_id = hdhw2.tim_id
197 AND hdhw2.effective_start_date >
198 hdhw.effective_start_date);
199
200
201
202 -- Bug 6067007
203 CURSOR csr_transferred_retro (
204 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
205 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
206 )
207 IS
208 SELECT 1
209 FROM hxt_det_hours_worked_f hdhw
210 WHERE hdhw.tim_id = v_tim_id
211 AND ( hdhw.pay_status = 'R'
212 OR hdhw.pa_status = 'R'
213 );
214 -- Bug 6067007
215
216
217 CURSOR csr_transferred (
218 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
219 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
220 )
221 IS
222 SELECT 1
223 FROM hxt_det_hours_worked_f hdhw
224 WHERE hdhw.tim_id = v_tim_id
225 AND ( hdhw.pay_status = 'C'
226 OR hdhw.pa_status = 'C'
227 );
228
229 CURSOR csr_changed_today (
230 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
231 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE,
232 v_session_dt DATE
233 )
234 IS
235 SELECT 1
236 FROM hxt_det_hours_worked_f hdhw
237 WHERE hdhw.tim_id = v_tim_id
238 AND trunc(hdhw.effective_start_date)
239 = trunc(v_session_dt);
240
241
242 l_transferred csr_transferred%ROWTYPE := NULL;
243 l_transfered_retro csr_transferred_retro%ROWTYPE := NULL; -- Bug 6067007
244 l_not_transferred csr_not_transferred%ROWTYPE := NULL;
245 l_changed_today csr_changed_today%ROWTYPE := NULL;
246 l_proc VARCHAR2 (30) ;
247
248 FUNCTION details (p_tim_id IN hxt_det_hours_worked_f.tim_id%TYPE,
249 p_date IN DATE)
250 RETURN BOOLEAN
251 IS
252 -- Bug 7359347
253 -- Changed the below cursor to pick up session date from global variable
254 -- instead of fnd_sessions table.
255
256 /*
257 CURSOR csr_debug (v_tim_id hxt_det_hours_worked_f.tim_id%TYPE)
258 IS
259 SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
260 hdhw.effective_start_date
261 FROM hxt_det_hours_worked hdhw
262 WHERE hdhw.tim_id = v_tim_id;
263 */
264
265 CURSOR csr_debug (v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
266 v_sess_date DATE)
267 IS
268 SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
269 hdhw.effective_start_date
270 FROM hxt_det_hours_worked_f hdhw
271 WHERE hdhw.tim_id = v_tim_id
272 AND v_sess_date BETWEEN hdhw.effective_start_date
273 AND hdhw.effective_end_date;
274
275
276 l_details BOOLEAN := FALSE;
277 BEGIN
278
279 FOR rec_debug IN csr_debug (p_tim_id,p_date)
280 LOOP
281 l_details := TRUE;
282 if g_debug then
283
284 hr_utility.TRACE (
285 LPAD (rec_debug.id, 10)
286 || ' '
287 || LPAD (rec_debug.parent_id, 10)
288 || ' '
289 || LPAD (rec_debug.date_worked, 10)
290 || ' '
291 || LPAD (rec_debug.pay_status, 10)
292 || ' '
293 || LPAD (rec_debug.effective_start_date, 10)
294 );
295 end if;
296 END LOOP;
297
298 RETURN l_details;
299 END details;
300 BEGIN
301
302 -- Bug 7359347
303 -- Setting session date
304 g_td_session_date := p_session_date;
305
306
307 -- Bug 12379517
308 -- Checking if proj pay integration is enabled.
309 -- Bug 12850901
310 -- Added this condition to take care of Xfer time from OTL to BEE
311 -- when tim_id is null
312 IF p_tim_id IS NOT NULL
313 AND p_do_intg_check = 'Y'
314 THEN
315 IF g_proj_pay_intg.EXISTS(p_tim_id)
316 AND g_proj_pay_intg(p_tim_id) = 'Y'
317 THEN
318 IF g_debug
319 THEN
320 hr_utility.trace('Integration enabled; verified earlier ');
321 END IF;
322 fnd_message.set_name ('HXC','HXC_PROJ_PAY_NO_OTLR');
323 o_error_message := fnd_message.get;
324 o_return_code := 1;
325 RETURN ;
326 ELSIF g_proj_pay_intg.EXISTS(p_tim_id)
327 AND g_proj_pay_intg(p_tim_id) = 'N'
328 THEN
329 IF g_debug
330 THEN
331 hr_utility.trace('Integration disabled; verified earlier ');
332 END IF;
333 ELSIF verify_proj_pay(p_tim_id)
334 THEN
335 fnd_message.set_name ('HXC','HXC_PROJ_PAY_NO_OTLR');
336 o_error_message := fnd_message.get;
337 o_return_code := 1;
338 RETURN ;
339 END IF;
340 END IF;
341
342
343
344 g_debug :=hr_utility.debug_enabled;
345 o_return_code := 0; -- indicates all is OK
346 if g_debug then
347 l_proc := 'retro_restrict_edit';
348 hr_utility.set_location ( 'Entering '
349 || l_proc, 10);
350 hr_utility.set_location ('Parameters In: ', 20);
351 hr_utility.set_location ( ' p_tim_id = '
352 || p_tim_id, 30);
353 hr_utility.set_location (
354 ' p_session_date = '
355 || p_session_date,
356 40
357 );
358 hr_utility.set_location ( ' p_parent_id = '
359 || p_parent_id, 50);
360 end if;
361
362 -- Bug 7359347
363 -- Setting session date
364 -- Check this before processing further because
365 -- you already have this in cache.
366
367 IF g_rre_details_tab.EXISTS(TO_CHAR(p_tim_id))
368 THEN
369 o_dt_update_mod := g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode;
370 o_return_code := g_rre_details_tab(TO_CHAR(p_tim_id)).ret_code;
371 RETURN;
372 ELSE
373
374 IF p_tim_id IS NOT NULL
375 THEN
376 g_rre_details_tab(TO_CHAR(p_tim_id)).session_date := p_session_date;
377 END IF;
378
379 -- Bug 7359347
380 -- Passing in session date to the function.
381 IF (details (p_tim_id,p_session_date))
382 THEN
383 -- First we check if the records have not been transferred yet
384 -- Bug 7359347
385 OPEN csr_not_transferred (p_tim_id, p_parent_id,p_session_date);
386 FETCH csr_not_transferred INTO l_not_transferred;
387
388 IF csr_not_transferred%FOUND
389 THEN -- we can do a correction
390 if g_debug then
391 hr_utility.set_location ('Do CORRECTION', 60);
392 end if;
393
394 o_dt_update_mod := 'CORRECTION';
395
396 ELSE -- look for records that have been transferred and undergoing RETRO
397
398
399 -- Bug 6067007 - Addition
400 OPEN csr_transferred_retro (p_tim_id, p_parent_id);
401 FETCH csr_transferred_retro INTO l_transfered_retro;
402
403 IF csr_transferred_retro%FOUND
404 THEN -- TC undergoing RETRO now, only UPDATE allowed.
405
406 if g_debug then
407 hr_utility.set_location ('Do UPDATE', 70);
408 end if;
409 o_dt_update_mod := 'UPDATE';
410 -- Bug 6067007
411
412
413 ELSE -- look for records that have been transferred
414
415 OPEN csr_transferred (p_tim_id, p_parent_id);
416 FETCH csr_transferred INTO l_transferred;
417
418 IF csr_transferred%FOUND
419 THEN -- Was it already updated today?
420 OPEN csr_changed_today (p_tim_id, p_parent_id, p_session_date);
421 FETCH csr_changed_today INTO l_changed_today;
422
423 IF csr_changed_today%FOUND
424 THEN -- DT will not allow an update so error
425 o_return_code := 1; -- this means that an error should be raised
426 o_dt_update_mod := NULL;
427 ELSE -- We can allow an DT UPDATE because that will keep the history
428 if g_debug then
429 hr_utility.set_location ('Do UPDATE', 70);
430 end if;
431 o_dt_update_mod := 'UPDATE';
432 END IF; -- IF csr_changed_today%FOUND
433
434 CLOSE csr_changed_today;
435 ELSE -- We can allow an DT UPDATE because that will keep the history
436 if g_debug then
437 hr_utility.set_location ('Do UPDATE', 80);
438 end if;
439 o_dt_update_mod := 'UPDATE';
440 END IF; -- IF csr_transferred%FOUND
441 CLOSE csr_transferred;
442
443 END IF; -- IF csr_transferred_retro%FOUND
444 CLOSE csr_transferred_retro;
445
446 END IF; -- IF csr_not_transferred%FOUND
447 CLOSE csr_not_transferred;
448
449 ELSE -- if details
450 if g_debug then
451 hr_utility.set_location ('Do CORRECTION', 90);
452 end if;
453 o_dt_update_mod := 'CORRECTION';
454 END IF;
455
456 -- Bug 7359347
457 -- Cache this value if tim id is not null
458 IF p_tim_id IS NOT NULL
459 THEN
460 g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode := o_dt_update_mod ;
461 g_rre_details_tab(TO_CHAR(p_tim_id)).ret_code := o_return_code ;
462 END IF;
463 RETURN;
464 END IF;
465
466
467 EXCEPTION
468 WHEN OTHERS
469 THEN
470 o_return_code := 4;
471 o_error_message := 'Error('
472 || SQLERRM
473 || ') occured in Retro_restrict_edit procedure';
474 RETURN;
475 END retro_restrict_edit;
476
477
478 PROCEDURE retro_restrict_edit (
479 p_tim_id IN hxt_det_hours_worked_f.tim_id%TYPE,
480 p_session_date IN DATE,
481 o_dt_update_mod OUT NOCOPY VARCHAR2,
482 o_error_message OUT NOCOPY VARCHAR2,
483 o_return_code OUT NOCOPY NUMBER,
484 p_parent_id IN hxt_det_hours_worked_f.parent_id%TYPE
485 )
486 IS
487
488 -- Bug 7359347
489 -- Changed the below cursor to pick up session date from global variable
490 -- instead of fnd_sessions table.
491
492 /*
493 CURSOR csr_not_transferred (
494 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
495 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
496 )
497 IS
498 SELECT 1
499 FROM hxt_det_hours_worked hdhw
500 WHERE hdhw.tim_id = v_tim_id
501 AND hdhw.pay_status = 'P' -- Bug 6067007
502 AND hdhw.pa_status = 'P' -- Bug 6067007
503 AND NOT EXISTS ( SELECT 1
504 FROM hxt_det_hours_worked_f hdhw2
505 WHERE hdhw.tim_id = hdhw2.tim_id
506 AND hdhw2.effective_start_date >
507 hdhw.effective_start_date);
508 */
509 CURSOR csr_not_transferred (
510 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
511 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE,
512 v_session_date DATE
513 )
514 IS
515 SELECT 1
516 FROM hxt_det_hours_worked_f hdhw
517 WHERE hdhw.tim_id = v_tim_id
518 AND v_session_date BETWEEN hdhw.effective_start_date
519 AND hdhw.effective_end_date
520 AND hdhw.pay_status = 'P' -- Bug 6067007
521 AND hdhw.pa_status = 'P' -- Bug 6067007
522 AND NOT EXISTS ( SELECT 1
523 FROM hxt_det_hours_worked_f hdhw2
524 WHERE hdhw.tim_id = hdhw2.tim_id
525 AND hdhw2.effective_start_date >
526 hdhw.effective_start_date);
527
528
529
530 -- Bug 6067007
531 CURSOR csr_transferred_retro (
532 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
533 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
534 )
535 IS
536 SELECT 1
537 FROM hxt_det_hours_worked_f hdhw
538 WHERE hdhw.tim_id = v_tim_id
539 AND ( hdhw.pay_status = 'R'
540 OR hdhw.pa_status = 'R'
541 );
542 -- Bug 6067007
543
544
545 CURSOR csr_transferred (
546 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
547 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
548 )
549 IS
550 SELECT 1
551 FROM hxt_det_hours_worked_f hdhw
552 WHERE hdhw.tim_id = v_tim_id
553 AND ( hdhw.pay_status = 'C'
554 OR hdhw.pa_status = 'C'
555 );
556
557 CURSOR csr_changed_today (
558 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
559 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE,
560 v_session_dt DATE
561 )
562 IS
563 SELECT 1
564 FROM hxt_det_hours_worked_f hdhw
565 WHERE hdhw.tim_id = v_tim_id
566 AND trunc(hdhw.effective_start_date)
567 = trunc(v_session_dt);
568
569
570 l_transferred csr_transferred%ROWTYPE := NULL;
571 l_transfered_retro csr_transferred_retro%ROWTYPE := NULL; -- Bug 6067007
572 l_not_transferred csr_not_transferred%ROWTYPE := NULL;
573 l_changed_today csr_changed_today%ROWTYPE := NULL;
574 l_proc VARCHAR2 (30) ;
575
576 FUNCTION details (p_tim_id IN hxt_det_hours_worked_f.tim_id%TYPE,
577 p_date IN DATE)
578 RETURN BOOLEAN
579 IS
580 -- Bug 7359347
581 -- Changed the below cursor to pick up session date from global variable
582 -- instead of fnd_sessions table.
583
584 /*
585 CURSOR csr_debug (v_tim_id hxt_det_hours_worked_f.tim_id%TYPE)
586 IS
587 SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
588 hdhw.effective_start_date
589 FROM hxt_det_hours_worked hdhw
590 WHERE hdhw.tim_id = v_tim_id;
591 */
592
593 CURSOR csr_debug (v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
594 v_sess_date DATE)
595 IS
596 SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
597 hdhw.effective_start_date
598 FROM hxt_det_hours_worked_f hdhw
599 WHERE hdhw.tim_id = v_tim_id
600 AND v_sess_date BETWEEN hdhw.effective_start_date
601 AND hdhw.effective_end_date;
602
603
604 l_details BOOLEAN := FALSE;
605 BEGIN
606
607 FOR rec_debug IN csr_debug (p_tim_id,p_date)
608 LOOP
609 l_details := TRUE;
610 if g_debug then
611
612 hr_utility.TRACE (
613 LPAD (rec_debug.id, 10)
614 || ' '
615 || LPAD (rec_debug.parent_id, 10)
616 || ' '
617 || LPAD (rec_debug.date_worked, 10)
618 || ' '
619 || LPAD (rec_debug.pay_status, 10)
620 || ' '
621 || LPAD (rec_debug.effective_start_date, 10)
622 );
623 end if;
624 END LOOP;
625
626 RETURN l_details;
627 END details;
628 BEGIN
629
630 -- Bug 7359347
631 -- Setting session date
632 g_td_session_date := p_session_date;
633
634
635 -- Bug 12379517
636 -- Checking if proj pay integration is enabled.
637 -- Bug 12850901
638 -- Added this condition to take care of Xfer time from OTL to BEE
639 -- when tim_id is null
640 IF p_tim_id IS NOT NULL
641 THEN
642 IF g_proj_pay_intg.EXISTS(p_tim_id)
643 AND g_proj_pay_intg(p_tim_id) = 'Y'
644 THEN
645 IF g_debug
646 THEN
647 hr_utility.trace('Integration enabled; verified earlier ');
648 END IF;
649 fnd_message.set_name ('HXC','HXC_PROJ_PAY_NO_OTLR');
650 o_error_message := fnd_message.get;
651 o_return_code := 1;
652 RETURN ;
653 ELSIF g_proj_pay_intg.EXISTS(p_tim_id)
654 AND g_proj_pay_intg(p_tim_id) = 'N'
655 THEN
656 IF g_debug
657 THEN
658 hr_utility.trace('Integration disabled; verified earlier ');
659 END IF;
660 ELSIF verify_proj_pay(p_tim_id)
661 THEN
662 fnd_message.set_name ('HXC','HXC_PROJ_PAY_NO_OTLR');
663 o_error_message := fnd_message.get;
664 o_return_code := 1;
665 RETURN ;
666 END IF;
667 END IF;
668
669
670
671 g_debug :=hr_utility.debug_enabled;
672 o_return_code := 0; -- indicates all is OK
673 if g_debug then
674 l_proc := 'retro_restrict_edit';
675 hr_utility.set_location ( 'Entering '
676 || l_proc, 10);
677 hr_utility.set_location ('Parameters In: ', 20);
678 hr_utility.set_location ( ' p_tim_id = '
679 || p_tim_id, 30);
680 hr_utility.set_location (
681 ' p_session_date = '
682 || p_session_date,
683 40
684 );
685 hr_utility.set_location ( ' p_parent_id = '
686 || p_parent_id, 50);
687 end if;
688
689 -- Bug 7359347
690 -- Setting session date
691 -- Check this before processing further because
692 -- you already have this in cache.
693
694 -- Bug 12919783
695 -- Pick from cache only if there is an error earlier
696 -- If an update or Correction was mentioned earlier, check again
697
698 IF g_rre_details_tab.EXISTS(TO_CHAR(p_tim_id))
699 AND g_rre_details_tab(TO_CHAR(p_tim_id)).ret_code = 1
700 THEN
701 o_dt_update_mod := g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode;
702 o_return_code := g_rre_details_tab(TO_CHAR(p_tim_id)).ret_code;
703 RETURN;
704 ELSE
705
706 IF p_tim_id IS NOT NULL
707 THEN
708 g_rre_details_tab(TO_CHAR(p_tim_id)).session_date := p_session_date;
709 END IF;
710
711 -- Bug 7359347
712 -- Passing in session date to the function.
713 IF (details (p_tim_id,p_session_date))
714 THEN
715 -- First we check if the records have not been transferred yet
716 -- Bug 7359347
717 OPEN csr_not_transferred (p_tim_id, p_parent_id,p_session_date);
718 FETCH csr_not_transferred INTO l_not_transferred;
719
720 IF csr_not_transferred%FOUND
721 THEN -- we can do a correction
722 if g_debug then
723 hr_utility.set_location ('Do CORRECTION', 60);
724 end if;
725
726 o_dt_update_mod := 'CORRECTION';
727
728 ELSE -- look for records that have been transferred and undergoing RETRO
729
730
731 -- Bug 6067007 - Addition
732 OPEN csr_transferred_retro (p_tim_id, p_parent_id);
733 FETCH csr_transferred_retro INTO l_transfered_retro;
734
735 IF csr_transferred_retro%FOUND
736 THEN -- TC undergoing RETRO now, only UPDATE allowed.
737
738 if g_debug then
739 hr_utility.set_location ('Do UPDATE', 70);
740 end if;
741 o_dt_update_mod := 'UPDATE';
742 -- Bug 6067007
743
744
745 ELSE -- look for records that have been transferred
746
747 OPEN csr_transferred (p_tim_id, p_parent_id);
748 FETCH csr_transferred INTO l_transferred;
749
750 IF csr_transferred%FOUND
751 THEN -- Was it already updated today?
752 OPEN csr_changed_today (p_tim_id, p_parent_id, p_session_date);
753 FETCH csr_changed_today INTO l_changed_today;
754
755 IF csr_changed_today%FOUND
756 THEN -- DT will not allow an update so error
757 o_return_code := 1; -- this means that an error should be raised
758 o_dt_update_mod := NULL;
759 ELSE -- We can allow an DT UPDATE because that will keep the history
760 if g_debug then
761 hr_utility.set_location ('Do UPDATE', 70);
762 end if;
763 o_dt_update_mod := 'UPDATE';
764 END IF; -- IF csr_changed_today%FOUND
765
766 CLOSE csr_changed_today;
767 ELSE -- We can allow an DT UPDATE because that will keep the history
768 if g_debug then
769 hr_utility.set_location ('Do UPDATE', 80);
770 end if;
771 o_dt_update_mod := 'UPDATE';
772 END IF; -- IF csr_transferred%FOUND
773 CLOSE csr_transferred;
774
775 END IF; -- IF csr_transferred_retro%FOUND
776 CLOSE csr_transferred_retro;
777
778 END IF; -- IF csr_not_transferred%FOUND
779 CLOSE csr_not_transferred;
780
781 ELSE -- if details
782 if g_debug then
783 hr_utility.set_location ('Do CORRECTION', 90);
784 end if;
785 o_dt_update_mod := 'CORRECTION';
786 END IF;
787
788 -- Bug 7359347
789 -- Cache this value if tim id is not null
790 IF p_tim_id IS NOT NULL
791 THEN
792 g_rre_details_tab(TO_CHAR(p_tim_id)).upd_mode := o_dt_update_mod ;
793 g_rre_details_tab(TO_CHAR(p_tim_id)).ret_code := o_return_code ;
794 END IF;
795 RETURN;
796 END IF;
797
798
799 EXCEPTION
800 WHEN OTHERS
801 THEN
802 o_return_code := 4;
803 o_error_message := 'Error('
804 || SQLERRM
805 || ') occured in Retro_restrict_edit procedure';
806 RETURN;
807 END retro_restrict_edit;
808
809
810
811 FUNCTION get_weekly_total (
812 a_location IN VARCHAR2,
813 a_date_worked IN DATE,
814 a_start_day_of_week IN VARCHAR2,
815 a_tim_id IN NUMBER,
816 a_base_element_type_id IN NUMBER,
817 a_ep_id IN NUMBER,
818
819 -- Added the following parameter for
820 -- OTLR Recurring Period Preference Support.
821 a_for_person_id IN NUMBER
822 )
823 RETURN NUMBER
824 IS
825 l_weekly_total NUMBER;
826
827
828 -- MHANDA changed this cursor to get total weekly hours between
829 -- start_day_of_week and (g_date_worked - 1) to support 3tier weekly rules for
830 -- SPECIAL Earning Policy.
831
832 -- Bug 7359347
833 -- Changed the below cursor to pick up session date from global variable
834 -- instead of fnd_sessions table.
835
836 /*
837 CURSOR weekly_total
838 IS
839 SELECT NVL (SUM (hrw.hours), 0)
840 FROM hxt_det_hours_worked hrw, --C421
841 hxt_timecards tim,
842 hxt_earn_groups erg,
843 hxt_earn_group_types egt,
844 hxt_earning_policies erp
845 -- WHERE tim.id = a_tim_id
846 -- AND hrw.tim_id = a_tim_id
847
848 -- Changed the above where clause as follows for supporting the
849 -- OTLR Recurring Period Preference.
850 WHERE tim.for_person_id = a_for_person_id
851 AND hrw.tim_id = tim.id
852
853 -- This has been changed back to get total weekly hours between
854 -- start_day_of_week and (g_date_worked) as it was not calculating the totals
855 -- correctly when entering hours on more than one summary row for the same day
856 -- AND hrw.date_worked between NEXT_DAY(a_date_worked-7,a_start_day_of_week)
857 -- and (a_date_worked - 1)
858
859 AND hrw.date_worked BETWEEN NEXT_DAY (
860 a_date_worked
861 - 7,
862 a_start_day_of_week
863 )
864 AND a_date_worked
865
866 -- only include earnings to be counted toward
867 -- hours to be worked before being eligible for overtime.
868 AND erp.id = a_ep_id
869 AND egt.id = erp.egt_id
870 AND erg.egt_id = egt.id
871 AND erg.element_type_id = hrw.element_type_id
872 AND hrw.date_worked BETWEEN erp.effective_start_date
873 AND erp.effective_end_date; */
874
875
876 CURSOR weekly_total(session_date DATE)
877 IS
878 SELECT NVL (SUM (hrw.hours), 0)
879 FROM hxt_det_hours_worked_f hrw,
880 hxt_timecards_f tim,
881 hxt_earn_groups erg,
882 hxt_earn_group_types egt,
883 hxt_earning_policies erp
884 WHERE tim.for_person_id = a_for_person_id
885 AND session_date between hrw.effective_start_date
886 and hrw.effective_end_Date
887
888 AND session_date between tim.effective_start_date
889 and tim.effective_end_Date
890 AND hrw.tim_id = tim.id
891 AND hrw.date_worked BETWEEN NEXT_DAY (
892 a_date_worked
893 - 7,
894 a_start_day_of_week
895 )
896 AND a_date_worked
897 AND erp.id = a_ep_id
898 AND egt.id = erp.egt_id
899 AND erg.egt_id = egt.id
900 AND erg.element_type_id = hrw.element_type_id
901 AND hrw.date_worked BETWEEN erp.effective_start_date
902 AND erp.effective_end_date;
903
904
905 -- Bug 11837942
906 -- Added this new cursor mimicking the above one, but with two
907 -- tim_ids, the current and previous one as params. The Query now
908 -- drives from hxt_timecards_f with PK index.
909
910 CURSOR weekly_total2(session_date DATE,
911 p_tim_id IN NUMBER,
912 p_prev_id IN NUMBER)
913 IS
914 SELECT NVL (SUM (hrw.hours), 0)
915 FROM hxt_det_hours_worked_f hrw,
916 hxt_timecards_f tim,
917 hxt_earn_groups erg,
918 hxt_earn_group_types egt,
919 hxt_earning_policies erp
920 WHERE tim.id IN (p_tim_id, p_prev_id)
921 AND session_date between hrw.effective_start_date
922 and hrw.effective_end_Date
923 AND session_date between tim.effective_start_date
924 and tim.effective_end_Date
925 AND hrw.tim_id = tim.id
926 AND hrw.date_worked BETWEEN NEXT_DAY (
927 a_date_worked
928 - 7,
929 a_start_day_of_week
930 )
931 AND a_date_worked
932 AND erp.id = a_ep_id
933 AND egt.id = erp.egt_id
934 AND erg.egt_id = egt.id
935 AND erg.element_type_id = hrw.element_type_id
936 AND hrw.date_worked BETWEEN erp.effective_start_date
937 AND erp.effective_end_date;
938
939
940
941
942
943 l_proc VARCHAR2 (200) ;
944 l_prev_id NUMBER;
945
946 BEGIN
947 g_debug :=hr_utility.debug_enabled;
948 if g_debug then
949 l_proc := 'hxt_td_util.GET_WEEKLY_TOTAL';
950 hr_utility.set_location (l_proc, 10);
951 hr_utility.TRACE (
952 'a_date_worked :'
953 || TO_CHAR (a_date_worked, 'dd-mon-yyyy hh24:mi:ss')
954 );
955 hr_utility.TRACE ( 'a_start_day_of_week :'
956 || a_start_day_of_week);
957 hr_utility.TRACE ( 'a_tim_id :'
958 || a_tim_id);
959 hr_utility.TRACE (
960 'a_base_element_type_id :'
961 || a_base_element_type_id
962 );
963 hr_utility.TRACE ( 'a_ep_id :'
964 || a_ep_id);
965 hr_utility.TRACE ( 'a_for_person_id :'
966 || a_for_person_id);
967 end if;
968 -- Bug 7359347
969 -- Pass session date to the cursor.
970
971
972
973 -- Bug 11837942
974 -- Check if there is a previous tim_id available.
975 -- If no, pass -1.
976
977 IF hxt_time_summary.g_sum_prev_ids.EXISTS(to_char(a_tim_id))
978 THEN
979 l_prev_id := hxt_time_summary.g_sum_prev_ids(to_char(a_tim_id));
980 ELSE
981 l_prev_id := -1;
982 END IF;
983
984 OPEN weekly_total2(g_td_session_date,
985 a_tim_id,
986 l_prev_id);
987
988 FETCH weekly_total2 INTO l_weekly_total;
989 if g_debug then
990 hr_utility.TRACE ( 'l_weekly_total :'
991 || l_weekly_total);
992 end if;
993 CLOSE weekly_total2;
994 if g_debug then
995 hr_utility.set_location (l_proc, 20.1);
996 end if;
997
998
999 -- Bug 11837942
1000 -- This part is commented out now so that
1001 -- the above cursor is called to calculate weekly total.
1002
1003 /*
1004 OPEN weekly_total(g_td_session_date);
1005
1006 FETCH weekly_total INTO l_weekly_total;
1007 if g_debug then
1008 hr_utility.TRACE ( 'l_weekly_total :'
1009 || l_weekly_total);
1010 end if;
1011 CLOSE weekly_total;
1012 if g_debug then
1013 hr_utility.set_location (l_proc, 20);
1014 end if;
1015 */
1016
1017
1018 RETURN l_weekly_total;
1019 END;
1020
1021 FUNCTION get_weekly_total_prev_days (
1022 a_location IN VARCHAR2,
1023 a_date_worked IN DATE,
1024 a_start_day_of_week IN VARCHAR2,
1025 a_tim_id IN NUMBER,
1026 a_base_element_type_id IN NUMBER,
1027 a_ep_id IN NUMBER,
1028
1029 -- Added the following parameter for
1030 -- OTLR Recurring Period Preference Support.
1031 a_for_person_id IN NUMBER
1032 )
1033 RETURN NUMBER
1034 IS
1035 l_weekly_total NUMBER;
1036
1037
1038 -- MHANDA changed this cursor to get total weekly hours between
1039 -- start_day_of_week and (g_date_worked - 1) to support 3tier weekly rules for
1040 -- SPECIAL Earning Policy.
1041
1042 -- Bug 7359347
1043 -- Changed the below cursor to pick up session date from global variable
1044 -- instead of fnd_sessions table.
1045
1046 /*
1047 CURSOR weekly_total
1048 IS
1049 SELECT NVL (SUM (hrw.hours), 0)
1050 FROM hxt_det_hours_worked hrw, --C421
1051 hxt_timecards tim,
1052 hxt_earn_groups erg,
1053 hxt_earn_group_types egt,
1054 hxt_earning_policies erp
1055 -- WHERE tim.id = a_tim_id
1056 -- AND hrw.tim_id = a_tim_id
1057
1058 -- Changed the above where clause as follows for supporting the
1059 -- OTLR Recurring Period Preference.
1060 WHERE tim.for_person_id = a_for_person_id
1061 AND hrw.tim_id = tim.id
1062
1063 -- This has been changed back to get total weekly hours between
1064 -- start_day_of_week and (g_date_worked) as it was not calculating the totals
1065 -- correctly when entering hours on more than one summary row for the same day
1066 -- AND hrw.date_worked between NEXT_DAY(a_date_worked-7,a_start_day_of_week)
1067 -- and (a_date_worked - 1)
1068
1069 AND hrw.date_worked BETWEEN NEXT_DAY (
1070 a_date_worked
1071 - 7,
1072 a_start_day_of_week
1073 )
1074 AND a_date_worked
1075 - 1
1076
1077 -- only include earnings to be counted toward
1078 -- hours to be worked before being eligible for overtime.
1079 AND erp.id = a_ep_id
1080 AND egt.id = erp.egt_id
1081 AND erg.egt_id = egt.id
1082 AND erg.element_type_id = hrw.element_type_id
1083 AND hrw.date_worked BETWEEN erp.effective_start_date
1084 AND erp.effective_end_date;
1085
1086 */
1087
1088 CURSOR weekly_total(session_date DATE)
1089 IS
1090 SELECT NVL (SUM (hrw.hours), 0)
1091 FROM hxt_det_hours_worked_f hrw, --C421
1092 hxt_timecards_f tim,
1093 hxt_earn_groups erg,
1094 hxt_earn_group_types egt,
1095 hxt_earning_policies erp
1096 WHERE tim.for_person_id = a_for_person_id
1097 AND hrw.tim_id = tim.id
1098 AND session_date BETWEEN hrw.effective_start_date
1099 AND hrw.effective_end_date
1100 AND session_date BETWEEN tim.effective_start_date
1101 AND tim.effective_end_date
1102 AND hrw.date_worked BETWEEN NEXT_DAY (
1103 a_date_worked
1104 - 7,
1105 a_start_day_of_week
1106 )
1107 AND a_date_worked
1108 - 1
1109 AND erp.id = a_ep_id
1110 AND egt.id = erp.egt_id
1111 AND erg.egt_id = egt.id
1112 AND erg.element_type_id = hrw.element_type_id
1113 AND hrw.date_worked BETWEEN erp.effective_start_date
1114 AND erp.effective_end_date;
1115
1116 -- Bug 11837942
1117 -- Added this new cursor mimicking the above one, but with two
1118 -- tim_ids, the current and previous one as params. The Query now
1119 -- drives from hxt_timecards_f with PK index.
1120
1121 CURSOR weekly_total2(session_date DATE,
1122 p_tim_id IN NUMBER,
1123 p_prev_id IN NUMBER)
1124 IS
1125 SELECT NVL (SUM (hrw.hours), 0)
1126 FROM hxt_det_hours_worked_f hrw,
1127 hxt_timecards_f tim,
1128 hxt_earn_groups erg,
1129 hxt_earn_group_types egt,
1130 hxt_earning_policies erp
1131 WHERE tim.id IN (p_tim_id, p_prev_id)
1132 AND session_date between hrw.effective_start_date
1133 and hrw.effective_end_Date
1134 AND session_date between tim.effective_start_date
1135 and tim.effective_end_Date
1136 AND hrw.tim_id = tim.id
1137 AND hrw.date_worked BETWEEN NEXT_DAY (
1138 a_date_worked
1139 - 7,
1140 a_start_day_of_week
1141 )
1142 AND a_date_worked
1143 - 1
1144 AND erp.id = a_ep_id
1145 AND egt.id = erp.egt_id
1146 AND erg.egt_id = egt.id
1147 AND erg.element_type_id = hrw.element_type_id
1148 AND hrw.date_worked BETWEEN erp.effective_start_date
1149 AND erp.effective_end_date;
1150
1151
1152
1153 l_proc VARCHAR2 (200) ;
1154 l_prev_id NUMBER;
1155 BEGIN
1156 g_debug :=hr_utility.debug_enabled;
1157 if g_debug then
1158 l_proc := 'hxt_td_util.GET_WEEKLY_TOTAL';
1159 hr_utility.set_location (l_proc, 10);
1160 hr_utility.TRACE (
1161 'a_date_worked :'
1162 || TO_CHAR (a_date_worked, 'dd-mon-yyyy hh24:mi:ss')
1163 );
1164 hr_utility.TRACE ( 'a_start_day_of_week :'
1165 || a_start_day_of_week);
1166 hr_utility.TRACE ( 'a_tim_id :'
1167 || a_tim_id);
1168 hr_utility.TRACE (
1169 'a_base_element_type_id :'
1170 || a_base_element_type_id
1171 );
1172 hr_utility.TRACE ( 'a_ep_id :'
1173 || a_ep_id);
1174 hr_utility.TRACE ( 'a_for_person_id :'
1175 || a_for_person_id);
1176 end if;
1177
1178 -- Bug 7359347
1179 -- Pass session date to the cursor.
1180
1181 -- Bug 11837942
1182 -- Check if there is a previous tim_id available.
1183 -- If no, pass -1.
1184
1185 IF hxt_time_summary.g_sum_prev_ids.EXISTS(to_char(a_tim_id))
1186 THEN
1187 l_prev_id := hxt_time_summary.g_sum_prev_ids(to_char(a_tim_id));
1188 ELSE
1189 l_prev_id := -1;
1190 END IF;
1191
1192 OPEN weekly_total2(g_td_session_date,
1193 a_tim_id,
1194 l_prev_id);
1195
1196 FETCH weekly_total2 INTO l_weekly_total;
1197 if g_debug then
1198 hr_utility.TRACE ( 'l_weekly_total :'
1199 || l_weekly_total);
1200 end if;
1201 CLOSE weekly_total2;
1202 if g_debug then
1203 hr_utility.set_location (l_proc, 20.1);
1204 end if;
1205
1206 -- Bug 11837942
1207 -- This part is commented out now so that
1208 -- the above cursor is called to calculate weekly total.
1209 /*
1210
1211 OPEN weekly_total(g_td_session_date);
1212 FETCH weekly_total INTO l_weekly_total;
1213 if g_debug then
1214 hr_utility.TRACE ( 'l_weekly_total :'
1215 || l_weekly_total);
1216 end if;
1217 CLOSE weekly_total;
1218 if g_debug then
1219 hr_utility.set_location (l_proc, 20);
1220 end if;
1221
1222 */
1223
1224 RETURN l_weekly_total;
1225 END get_weekly_total_prev_days;
1226
1227
1228 --
1229 ----------------- Include For ot Cap ---------------------------------
1230 FUNCTION include_for_ot_cap (
1231 a_earn_group IN NUMBER,
1232 a_element_type IN NUMBER,
1233 a_base_element IN NUMBER,
1234 a_date_worked IN DATE
1235 )
1236 RETURN BOOLEAN
1237 IS
1238
1239 -- returns true if a particular earning is counted toward the weekly overtime cap
1240 returned_element NUMBER;
1241 BEGIN
1242 IF a_element_type = a_base_element
1243 THEN
1244 RETURN TRUE;
1245 END IF;
1246
1247 SELECT 1
1248 INTO returned_element
1249 FROM hxt_earn_group_types egt
1250 WHERE egt.fcl_eg_type = 'INCLUDE'
1251 AND a_date_worked BETWEEN egt.effective_start_date
1252 AND egt.effective_end_date
1253 AND egt.id = a_earn_group
1254 AND EXISTS ( SELECT 'x'
1255 FROM hxt_earn_groups egr
1256 WHERE egr.egt_id = egt.id
1257 AND egr.element_type_id = a_element_type);
1258
1259 IF returned_element = 1
1260 THEN
1261 RETURN TRUE;
1262 ELSE
1263 RETURN FALSE;
1264 END IF;
1265 EXCEPTION
1266 WHEN NO_DATA_FOUND
1267 THEN
1268 RETURN FALSE;
1269 END;
1270
1271
1272 --
1273 ------------------ Load Changed Status -------------------------------
1274 FUNCTION load_changed_status (a_hrw_id IN NUMBER)
1275 RETURN VARCHAR2
1276 IS
1277 v_status CHAR (1) := ''; --HXT11i1 Return null instead of space
1278
1279
1280 -- Bug 7359347
1281 -- Changed the below cursor to pick up session date from global variable
1282 -- instead of fnd_sessions table.
1283
1284 CURSOR changed_cur(session_date DATE)
1285 IS
1286 SELECT 'C'
1287 FROM hxt_sum_hours_worked_f hrwp
1288 WHERE hrwp.id = a_hrw_id
1289 AND session_date BETWEEN hrwp.effective_start_date
1290 AND hrwp.effective_end_date
1291 AND ( creation_date <> last_update_date
1292 OR EXISTS ( SELECT '1'
1293 FROM hxt_sum_hours_worked_f hrw
1294 WHERE hrw.id = a_hrw_id
1295 AND hrw.effective_start_date <>
1296 hrwp.effective_start_date)
1297 );
1298 BEGIN
1299 -- Bug 7359347
1300 -- Pass session date to the cursor.
1301 OPEN changed_cur(g_td_session_date);
1302 FETCH changed_cur INTO v_status;
1303 CLOSE changed_cur;
1304 RETURN v_status;
1305 EXCEPTION
1306 WHEN OTHERS
1307 THEN
1308 RETURN 'X';
1309 END;
1310
1311
1312 --
1313 ------------------- Load Error Status -------------------------------
1314 --BEGIN HXT11i1
1315 /***************************************************************************
1316 ** Modify Load_Error_Status to select the Value stored in ERR_TYPE from **
1317 ** HXT_ERRORS rather then selecting a hard coded 'E' if the record exists. **
1318 ***************************************************************************/
1319 FUNCTION load_error_status (a_hrw_id IN NUMBER)
1320 RETURN VARCHAR2
1321 IS
1322 returned_status VARCHAR2 (03) := '';
1323
1324 CURSOR error_status
1325 IS
1326 SELECT err_type
1327 FROM hxt_errors err
1328 WHERE err.hrw_id = a_hrw_id;
1329 BEGIN
1330 OPEN error_status;
1331 FETCH error_status INTO returned_status;
1332 CLOSE error_status;
1333 RETURN returned_status;
1334 END load_error_status;
1335
1336
1337 ---------------------- Determine Fixed Premium ------------------------
1338 /* Begin OHM104 RJT; put into TA35 May27,97 RTF */
1339 /* ******************************************************************
1340 * FUNCTION determine_fixed_premium *
1341 * *
1342 * Purpose *
1343 * To determine if a element is fixed premium and the of the *
1344 * fixed premium. The amount of the fixed premium is place in *
1345 * amount field on the time card. *
1346 * rate *
1347 * *
1348 * Returns *
1349 * p_return_code - 0 - no errors 2 - errors occured. *
1350 * p_amount - Null if the element is not a fixed premium *
1351 * - The amount of the fixed premium *
1352 * *
1353 * Arguments *
1354 * p_element_type_id - The hours type. *
1355 * *
1356 *****************************************************************/
1357 FUNCTION determine_fixed_premium (
1358 p_tim_id NUMBER,
1359 p_id NUMBER,
1360 p_hours NUMBER,
1361 p_element_type_id NUMBER,
1362 p_effective_start_date DATE,
1363 p_effective_end_date DATE,
1364 p_return_code OUT NOCOPY NUMBER
1365 )
1366 RETURN NUMBER
1367 IS
1368 CURSOR fixed_amount_cur (c_element_type_id NUMBER)
1369 IS
1370 SELECT petv.hxt_premium_amount
1371 FROM hxt_pay_element_types_f_ddf_v petv
1372 WHERE petv.hxt_earning_category = 'OTH'
1373 AND petv.hxt_premium_type = 'FIXED'
1374 AND petv.element_type_id = c_element_type_id
1375 AND petv.effective_start_date <= p_effective_start_date
1376 AND petv.effective_end_date >= p_effective_end_date;
1377
1378 v_amount hxt_det_hours_worked_f.amount%TYPE;
1379 BEGIN
1380
1381 -- Determine if the element is a fixed amount and what the fixed amount is.
1382
1383 OPEN fixed_amount_cur (p_element_type_id);
1384 FETCH fixed_amount_cur INTO v_amount;
1385
1386 IF fixed_amount_cur%NOTFOUND
1387 THEN
1388 CLOSE fixed_amount_cur;
1389 RETURN NULL;
1390 END IF;
1391
1392 CLOSE fixed_amount_cur;
1393
1394
1395 -- Ensure that the hours field is null.
1396
1397 IF p_hours > 0
1398 THEN
1399 p_return_code := 2;
1400 fnd_message.set_name ('HXT', 'HXT_39467_NO_HRS_4_FIX_PREM');
1401 hxt_util.gen_error (
1402 p_tim_id,
1403 p_id,
1404 NULL,
1405 NULL,
1406 '',
1407 'tdutilbd.determine_fixed_premium ',
1408 NULL,
1409 p_effective_start_date,
1410 p_effective_end_date,
1411 'ERR'
1412 );
1413 RETURN NULL;
1414 END IF;
1415
1416 p_return_code := 0;
1417 RETURN v_amount;
1418 EXCEPTION
1419 WHEN OTHERS
1420 THEN
1421 p_return_code := 2;
1422 fnd_message.set_name ('HXT', 'HXT_39468_OR_ERR_SEL_PREM_AMT');
1423 hxt_util.gen_error (
1424 p_tim_id,
1425 p_id,
1426 NULL,
1427 NULL,
1428 '',
1429 'tdutilbd.fixed_amount. ',
1430 SQLERRM,
1431 p_effective_start_date,
1432 p_effective_end_date,
1433 'ERR'
1434 );
1435 RETURN NULL;
1436 END determine_fixed_premium;
1437
1438
1439 --
1440 --------------------- Get Hourly Rate ------------------------
1441 -- begin CERTPAY
1442 FUNCTION get_hourly_rate (
1443 p_eff_date DATE,
1444 p_ptp_id NUMBER,
1445 p_assignment_id NUMBER,
1446 p_hourly_rate OUT NOCOPY NUMBER
1447 )
1448 RETURN NUMBER
1449 IS
1450 CURSOR sal_cur
1451 IS
1452 SELECT pro.proposed_salary, ppb.pay_basis
1453 FROM per_pay_proposals pro,
1454 per_pay_bases ppb,
1455 per_assignments_f asg
1456 WHERE pro.assignment_id = p_assignment_id
1457 AND ppb.pay_basis_id = asg.pay_basis_id
1458 AND asg.assignment_id = pro.assignment_id
1459 AND p_eff_date BETWEEN asg.effective_start_date
1460 AND asg.effective_end_date
1461 AND pro.approved = 'Y'
1462 AND pro.change_date = (SELECT MAX (pro2.change_date)
1463 FROM per_pay_proposals pro2
1464 WHERE pro2.assignment_id =
1465 p_assignment_id
1466 AND pro2.approved = 'Y'
1467 AND p_eff_date >= pro2.change_date);
1468
1469 CURSOR num_periods_cur
1470 IS
1471 SELECT ptpt.number_per_fiscal_year
1472 FROM per_time_periods ptp, per_time_period_types ptpt
1473 WHERE p_ptp_id = ptp.time_period_id
1474 AND ptp.period_type = ptpt.period_type;
1475
1476 v_proposed_salary per_pay_proposals_v.proposed_salary%TYPE;
1477 v_pay_basis per_pay_proposals_v.pay_basis%TYPE;
1478 l_hours_per_year NUMBER (22, 5)
1479 := TO_NUMBER (fnd_profile.VALUE ('HXT_HOURS_PER_YEAR'));
1480 v_annual_pay_periods per_time_period_types.number_per_fiscal_year%TYPE;
1481 BEGIN
1482 OPEN sal_cur;
1483 FETCH sal_cur INTO v_proposed_salary, v_pay_basis;
1484
1485 IF sal_cur%NOTFOUND
1486 THEN
1487 RETURN 1; -- 'No salary information for the employee'
1488 END IF;
1489
1490 OPEN num_periods_cur;
1491 FETCH num_periods_cur INTO v_annual_pay_periods;
1492
1493 IF num_periods_cur%NOTFOUND
1494 THEN
1495 RETURN 2; -- 'Unable to determine number of pay periods.'
1496 END IF;
1497
1498
1499 -- Calculate an hourly rate for the salary basis
1500 IF v_pay_basis = 'ANNUAL'
1501 THEN
1502 p_hourly_rate := v_proposed_salary / l_hours_per_year;
1503 ELSIF v_pay_basis = 'MONTHLY'
1504 THEN
1505 p_hourly_rate := (v_proposed_salary * 12)
1506 / l_hours_per_year;
1507 ELSIF v_pay_basis = 'PERIOD'
1508 THEN
1509 p_hourly_rate :=
1510 (v_proposed_salary * v_annual_pay_periods)
1511 / l_hours_per_year;
1512 ELSE -- 'HOURLY'
1513 p_hourly_rate := v_proposed_salary;
1514 END IF;
1515
1516 RETURN 0;
1517 END;
1518
1519
1520 -- end CERTPAY
1521 --BEGIN HXT11i1
1522 /*****************************************************************
1523 * FUNCTION Load_Tim_Error_Status -- ER177 SDM 09-03-98 *
1524 * *
1525 * Purpose *
1526 * Select and return the value ERR_TYPE from the Table *
1527 * HXT_ERRORS where the tim_id passed in = HXT_ERRORS.TIM_ID. *
1528 * *
1529 * Returns *
1530 * returned_status - Value of ERR_TYPE where tim_id passed in *
1531 * equals HXT_ERRORS.TIM_ID.0 *
1532 * *
1533 * Arguments *
1534 * p_tim_id - Time Card ID *
1535 * *
1536 *****************************************************************/
1537 FUNCTION load_tim_error_status (p_tim_id IN NUMBER)
1538 RETURN VARCHAR2
1539 IS
1540 returned_status VARCHAR2 (3) := '';
1541
1542 CURSOR error_status
1543 IS
1544 SELECT err_type
1545 FROM hxt_errors err
1546 WHERE err.tim_id = p_tim_id
1547 ORDER BY err_type; -- So that 'ERR' has precedence over 'WRN'
1548 BEGIN
1549 OPEN error_status;
1550 FETCH error_status INTO returned_status;
1551 CLOSE error_status;
1552 RETURN returned_status;
1553 END load_tim_error_status;
1554
1555
1556 /*****************************************************************
1557 * FUNCTION Load_HRW_Error_Change_Status -- ER177 SDM 09-03-98 *
1558 * *
1559 * Purpose *
1560 * Retrieve Error Type and Change Type for HRW row and retrun *
1561 * Concatenated value. *
1562 * *
1563 * Returns *
1564 * returned_status - Value of Concatenated value of Error type *
1565 * and changed type *
1566 * *
1567 * Arguments *
1568 * p_hrw_id - ID of summary Row *
1569 * p_tim_status - Error type of the time card record *
1570 * *
1571 *****************************************************************/
1572 FUNCTION load_hrw_error_change_status (
1573 p_hrw_id IN NUMBER,
1574 p_tim_status IN VARCHAR2
1575 )
1576 RETURN VARCHAR2
1577 IS
1578 hrw_status VARCHAR2 (01);
1579 change_status VARCHAR2 (01);
1580 BEGIN
1581
1582 -- Bug Fix : 2538832
1583 -- Added return statement to ensure that the function returns
1584 -- only the error status, if timecard status is Error.
1585 -- i.e. ignore Change status.
1586
1587 IF p_tim_status = 'E'
1588 THEN
1589 hrw_status := SUBSTR (load_error_status (p_hrw_id), 1, 1);
1590 RETURN hrw_status;
1591 END IF;
1592
1593 change_status := load_changed_status (p_hrw_id);
1594 RETURN hrw_status
1595 || change_status;
1596 END load_hrw_error_change_status;
1597
1598
1599 /*****************************************************************
1600 * FUNCTION Get_Sum_Hours_Worked -- ER183 SDM 09-03-98 *
1601 * *
1602 * Purpose *
1603 * Get the sum of the hours from hxt_sum_hours_worked based on *
1604 * parameters passed in. Return this value to the calling *
1605 * module *
1606 * *
1607 * Returns *
1608 * total_hrs - Total hrs employee worked based on the date *
1609 * passed in *
1610 * *
1611 * Arguments *
1612 * p_tim_id - ID of Timecard Row *
1613 * p_hrw_id - ID of summary Row *
1614 * p_date_worked - Date the hours are to be summed upon *
1615 * *
1616 *****************************************************************/
1617 FUNCTION get_sum_hours_worked (
1618 p_tim_id IN NUMBER, -- p_hrw_group_id IN NUMBER,
1619 p_date_worked IN DATE
1620 )
1621 RETURN NUMBER
1622 IS
1623 -- Bug 7359347
1624 -- Changed the below cursor to pick up session date from global variable
1625 -- instead of fnd_sessions table.
1626
1627 /*
1628 CURSOR edit24_cur
1629 IS
1630 SELECT SUM (hours)
1631 FROM hxt_sum_hours_worked
1632 WHERE tim_id = p_tim_id -- AND GROUP_ID <> NVL(p_hrw_group_id, 0)
1633 AND date_worked = p_date_worked;
1634 */
1635 CURSOR edit24_cur
1636 IS
1637 SELECT SUM (hours)
1638 FROM hxt_sum_hours_worked_f
1639 WHERE tim_id = p_tim_id -- AND GROUP_ID <> NVL(p_hrw_group_id, 0)
1640 AND date_worked = p_date_worked
1641 AND g_td_session_date BETWEEN effective_start_date
1642 AND effective_end_date;
1643
1644
1645 l_total_hours NUMBER;
1646 BEGIN
1647 OPEN edit24_cur;
1648 FETCH edit24_cur INTO l_total_hours;
1649
1650 IF edit24_cur%NOTFOUND
1651 THEN
1652 l_total_hours := 0;
1653 END IF;
1654
1655 CLOSE edit24_cur;
1656 RETURN l_total_hours;
1657 END get_sum_hours_worked;
1658
1659
1660 -----------------------------------------------------------------------------
1661 -- This procedure cannot be used for OVT calculation. That's because
1662 -- it doesn't check the Include for OT group in the case of Absences.
1663 -- See package TIMDETBD.PLS get_contig_hours for an example of how
1664 -- to do that.
1665 -- This procedure is intended to be used for shift differential calculations.
1666 -----------------------------------------------------------------------------
1667 PROCEDURE get_contig_hrs_and_start (
1668 p_date_worked IN DATE,
1669 p_person_id IN NUMBER,
1670 p_current_time_in IN DATE,
1671 p_egt_id IN NUMBER,
1672 p_tim_id IN NUMBER,
1673 o_first_time_in OUT NOCOPY DATE,
1674 o_contig_hrs OUT NOCOPY NUMBER
1675 )
1676 IS
1677
1678 -- We do not want to get records where the time_out is the exact time and day
1679 -- as the time_in of a previous record. This is an error condition that should
1680 -- never happen, but did due to an autogen error. This code prevents endless
1681 -- looping. SIR282
1682 -- Bug 7359347
1683 -- Changed the below cursor to pick up session date from global variable
1684 -- instead of fnd_sessions table.
1685
1686 /*
1687 CURSOR contig_hrs (
1688 c_date_worked DATE,
1689 c_current_time_in DATE,
1690 c_tim_id NUMBER
1691 )
1692 IS
1693 SELECT hrw.time_in, hrw.time_out, hrw.hours, hrw.element_type_id,
1694 hrw.date_worked, eltv.hxt_earning_category
1695 FROM hxt_det_hours_worked hrw,
1696 hxt_pay_element_types_f_ddf_v eltv,
1697 pay_element_types_f elt
1698 WHERE elt.element_type_id = hrw.element_type_id
1699 AND eltv.hxt_earning_category IN ('REG', 'OVT', 'ABS')
1700 AND hrw.date_worked BETWEEN elt.effective_start_date
1701 AND elt.effective_end_date
1702 AND eltv.element_type_id = elt.element_type_id
1703 AND hrw.date_worked BETWEEN eltv.effective_start_date
1704 AND eltv.effective_end_date
1705 AND hrw.tim_id = c_tim_id
1706 AND hrw.time_out = c_current_time_in
1707 AND hrw.time_in <> hrw.time_out; --FIX endless loop PWM 01/28/99 SIR282
1708 */
1709 CURSOR contig_hrs (
1710 c_date_worked DATE,
1711 c_current_time_in DATE,
1712 c_tim_id NUMBER,
1713 c_session_date DATE
1714 )
1715 IS
1716 SELECT hrw.time_in, hrw.time_out, hrw.hours, hrw.element_type_id,
1717 hrw.date_worked, eltv.hxt_earning_category
1718 FROM hxt_det_hours_worked_f hrw,
1719 hxt_pay_element_types_f_ddf_v eltv,
1720 pay_element_types_f elt
1721 WHERE elt.element_type_id = hrw.element_type_id
1722 AND eltv.hxt_earning_category IN ('REG', 'OVT', 'ABS')
1723 AND c_session_date BETWEEN hrw.effective_start_date
1724 AND hrw.effective_end_date
1725 AND hrw.date_worked BETWEEN elt.effective_start_date
1726 AND elt.effective_end_date
1727 AND eltv.element_type_id = elt.element_type_id
1728 AND hrw.date_worked BETWEEN eltv.effective_start_date
1729 AND eltv.effective_end_date
1730 AND hrw.tim_id = c_tim_id
1731 AND hrw.time_out = c_current_time_in
1732 AND hrw.time_in <> hrw.time_out; --FIX endless loop PWM 01/28/99 SIR282
1733
1734
1735 l_rec contig_hrs%ROWTYPE;
1736 l_contig_hrs NUMBER (7, 3) := 0;
1737 l_continue BOOLEAN := TRUE;
1738 l_current_time_in DATE := p_current_time_in;
1739 l_date_worked DATE := p_date_worked;
1740 loop_counter NUMBER := 0; -- counts loop iterations for checking SIR282
1741 error_code NUMBER := 0; -- PWM Fix for endless loop 01/28/99 SIR282
1742 BEGIN
1743 hxt_util.DEBUG (
1744 'Top of get_contig_hrs. time_in = '
1745 || TO_CHAR (l_current_time_in, 'HH24:MI')
1746 );
1747
1748 WHILE l_continue = TRUE
1749 LOOP
1750 -- Bug 7359347
1751 -- Pass session date to the cursor.
1752 OPEN contig_hrs (l_date_worked, l_current_time_in, p_tim_id,g_td_session_date);
1753 FETCH contig_hrs INTO l_rec;
1754
1755 IF contig_hrs%FOUND
1756 THEN
1757 hxt_util.DEBUG (
1758 ' previous summary found. time_in = '
1759 || TO_CHAR (l_rec.time_in, 'HH24:MI')
1760 );
1761 l_contig_hrs := l_contig_hrs
1762 + l_rec.hours;
1763 l_current_time_in := l_rec.time_in;
1764 l_date_worked := l_rec.date_worked;
1765 CLOSE contig_hrs;
1766 ELSE
1767 CLOSE contig_hrs;
1768 l_continue := FALSE;
1769 END IF;
1770
1771 loop_counter := loop_counter
1772 + 1; --PWM 01/28/99 Fix for endless loop SIR282
1773
1774 IF loop_counter > 50
1775 THEN
1776 l_continue := FALSE;
1777 fnd_message.set_name ('HXT', 'HXT_39506_LOOP_COUNT_EXCEEDED');
1778 hxt_util.gen_error (
1779 p_tim_id,
1780 NULL,
1781 NULL,
1782 '',
1783 'hxttdutl.get_contig_hrs_and_start ',
1784 SQLERRM,
1785 p_date_worked,
1786 p_date_worked,
1787 'ERR'
1788 );
1789 END IF;
1790 END LOOP;
1791
1792 hxt_util.DEBUG ('');
1793 hxt_util.DEBUG ( 'Done. hours = '
1794 || TO_CHAR (l_contig_hrs));
1795 o_contig_hrs := l_contig_hrs;
1796 o_first_time_in := l_current_time_in;
1797 END;
1798 --END HXT11i1
1799 END;
[Home] [Help]