[Home] [Help]
17: ******************************************************************
18: Name : pay_us_pto_accrual
19: Description : This package holds building blocks used in PTO accrual
20: calculation.
21: Uses : hr_utility
22: Change List
23: -----------
24: Date Name Vers Bug No Description
25: ---- ---- ---- ------ -----------
337: P_first_period_end := null;
338: P_last_period_start := null;
339: P_last_period_end := null;
340: --
341: hr_utility.set_location('get_accrual',5);
342: ---
343: --- If both param null. RETURN
344: --
345: IF P_plan_id is null AND P_plan_category is null
343: --- If both param null. RETURN
344: --
345: IF P_plan_id is null AND P_plan_category is null
346: THEN
347: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
348: hr_utility.set_message_token('PROCEDURE','get_accrual');
349: hr_utility.set_message_token('STEP','1');
350: hr_utility.raise_error;
351: END IF;
344: --
345: IF P_plan_id is null AND P_plan_category is null
346: THEN
347: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
348: hr_utility.set_message_token('PROCEDURE','get_accrual');
349: hr_utility.set_message_token('STEP','1');
350: hr_utility.raise_error;
351: END IF;
352:
345: IF P_plan_id is null AND P_plan_category is null
346: THEN
347: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
348: hr_utility.set_message_token('PROCEDURE','get_accrual');
349: hr_utility.set_message_token('STEP','1');
350: hr_utility.raise_error;
351: END IF;
352:
353: OPEN csr_get_payroll(P_assignment_id, P_calculation_date);
346: THEN
347: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
348: hr_utility.set_message_token('PROCEDURE','get_accrual');
349: hr_utility.set_message_token('STEP','1');
350: hr_utility.raise_error;
351: END IF;
352:
353: OPEN csr_get_payroll(P_assignment_id, P_calculation_date);
354: FETCH csr_get_payroll INTO P_payroll_id,
359: l_termination_date;
360: IF csr_get_payroll%NOTFOUND
361: THEN
362: CLOSE csr_get_payroll;
363: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
364: hr_utility.set_message_token('PROCEDURE','get_accrual');
365: hr_utility.set_message_token('STEP','2');
366: hr_utility.raise_error;
367: END IF;
360: IF csr_get_payroll%NOTFOUND
361: THEN
362: CLOSE csr_get_payroll;
363: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
364: hr_utility.set_message_token('PROCEDURE','get_accrual');
365: hr_utility.set_message_token('STEP','2');
366: hr_utility.raise_error;
367: END IF;
368: CLOSE csr_get_payroll;
361: THEN
362: CLOSE csr_get_payroll;
363: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
364: hr_utility.set_message_token('PROCEDURE','get_accrual');
365: hr_utility.set_message_token('STEP','2');
366: hr_utility.raise_error;
367: END IF;
368: CLOSE csr_get_payroll;
369: hr_utility.set_location('get_accrual',10);
362: CLOSE csr_get_payroll;
363: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
364: hr_utility.set_message_token('PROCEDURE','get_accrual');
365: hr_utility.set_message_token('STEP','2');
366: hr_utility.raise_error;
367: END IF;
368: CLOSE csr_get_payroll;
369: hr_utility.set_location('get_accrual',10);
370: --
365: hr_utility.set_message_token('STEP','2');
366: hr_utility.raise_error;
367: END IF;
368: CLOSE csr_get_payroll;
369: hr_utility.set_location('get_accrual',10);
370: --
371: -- Get start and end date for the Calculation date
372: --
373: hr_utility.set_location('get_accrual',15);
369: hr_utility.set_location('get_accrual',10);
370: --
371: -- Get start and end date for the Calculation date
372: --
373: hr_utility.set_location('get_accrual',15);
374:
375: OPEN csr_get_period(P_payroll_id, P_calculation_date);
376: FETCH csr_get_period INTO l_calc_period_num,
377: l_calc_start_date,
378: l_calc_end_date;
379: IF csr_get_period%NOTFOUND
380: THEN
381: CLOSE csr_get_period;
382: hr_utility.set_message(801,'HR_51731_PTO_DATE_OUT_TIMEPRD');
383: -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
384: -- hr_utility.set_message_token('PROCEDURE','get_accrual');
385: -- hr_utility.set_message_token('STEP','3');
386: hr_utility.raise_error;
379: IF csr_get_period%NOTFOUND
380: THEN
381: CLOSE csr_get_period;
382: hr_utility.set_message(801,'HR_51731_PTO_DATE_OUT_TIMEPRD');
383: -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
384: -- hr_utility.set_message_token('PROCEDURE','get_accrual');
385: -- hr_utility.set_message_token('STEP','3');
386: hr_utility.raise_error;
387: END IF;
380: THEN
381: CLOSE csr_get_period;
382: hr_utility.set_message(801,'HR_51731_PTO_DATE_OUT_TIMEPRD');
383: -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
384: -- hr_utility.set_message_token('PROCEDURE','get_accrual');
385: -- hr_utility.set_message_token('STEP','3');
386: hr_utility.raise_error;
387: END IF;
388: CLOSE csr_get_period;
381: CLOSE csr_get_period;
382: hr_utility.set_message(801,'HR_51731_PTO_DATE_OUT_TIMEPRD');
383: -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
384: -- hr_utility.set_message_token('PROCEDURE','get_accrual');
385: -- hr_utility.set_message_token('STEP','3');
386: hr_utility.raise_error;
387: END IF;
388: CLOSE csr_get_period;
389: hr_utility.set_location('get_accrual',20);
382: hr_utility.set_message(801,'HR_51731_PTO_DATE_OUT_TIMEPRD');
383: -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
384: -- hr_utility.set_message_token('PROCEDURE','get_accrual');
385: -- hr_utility.set_message_token('STEP','3');
386: hr_utility.raise_error;
387: END IF;
388: CLOSE csr_get_period;
389: hr_utility.set_location('get_accrual',20);
390: --
385: -- hr_utility.set_message_token('STEP','3');
386: hr_utility.raise_error;
387: END IF;
388: CLOSE csr_get_period;
389: hr_utility.set_location('get_accrual',20);
390: --
391: -- Partial first period if start
392: --
393: -- Set return dates for the net process if nothing to accrue in this period
418: l_number_of_period;
419: IF csr_get_total_periods%NOTFOUND
420: THEN
421: CLOSE csr_get_total_periods;
422: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
423: hr_utility.set_message_token('PROCEDURE','get_accrual');
424: hr_utility.set_message_token('STEP','4');
425: hr_utility.raise_error;
426: END IF;
419: IF csr_get_total_periods%NOTFOUND
420: THEN
421: CLOSE csr_get_total_periods;
422: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
423: hr_utility.set_message_token('PROCEDURE','get_accrual');
424: hr_utility.set_message_token('STEP','4');
425: hr_utility.raise_error;
426: END IF;
427: CLOSE csr_get_total_periods;
420: THEN
421: CLOSE csr_get_total_periods;
422: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
423: hr_utility.set_message_token('PROCEDURE','get_accrual');
424: hr_utility.set_message_token('STEP','4');
425: hr_utility.raise_error;
426: END IF;
427: CLOSE csr_get_total_periods;
428: -- Set l_number_of_period such that it is based on NUMBER_PER_FISCAL_YEAR
421: CLOSE csr_get_total_periods;
422: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
423: hr_utility.set_message_token('PROCEDURE','get_accrual');
424: hr_utility.set_message_token('STEP','4');
425: hr_utility.raise_error;
426: END IF;
427: CLOSE csr_get_total_periods;
428: -- Set l_number_of_period such that it is based on NUMBER_PER_FISCAL_YEAR
429: -- for period type of payroll. Ie. The number returned from
439: AND PPF.payroll_id = P_payroll_id
440: AND l_calc_end_date BETWEEN PPF.effective_start_date
441: AND PPF.effective_end_date;
442: --
443: hr_utility.set_location('get_accrual',25);
444: --
445: -- In case of carry over a dummy date of 31-JUL-YYYY is passed in order to get
446: -- the no. of periods first and last period od that year etc. Check if P_mode
447: -- is 'C' then set the calculation date to the end date of last period and
446: -- the no. of periods first and last period od that year etc. Check if P_mode
447: -- is 'C' then set the calculation date to the end date of last period and
448: -- get period number for that period again.
449: --
450: hr_utility.set_location('get_accrual',27);
451: IF P_mode = 'C'
452: THEN
453: l_calc_period_num := l_number_of_period;
454: l_calc_start_date := P_last_period_start;
480: p_param_first_pendt := P_first_period_end;
481:
482: */
483:
484: hr_utility.set_location('get_accrual',30);
485: OPEN csr_get_period (P_payroll_id, P_first_period_start);
486: FETCH csr_get_period INTO p_param_first_pnum,
487: p_param_first_pstdt,
488: p_param_first_pendt;
488: p_param_first_pendt;
489: IF csr_get_period%NOTFOUND
490: THEN
491: CLOSE csr_get_period;
492: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
493: hr_utility.set_message_token('PROCEDURE','get_accrual');
494: hr_utility.set_message_token('STEP','5');
495: hr_utility.raise_error;
496: END IF;
489: IF csr_get_period%NOTFOUND
490: THEN
491: CLOSE csr_get_period;
492: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
493: hr_utility.set_message_token('PROCEDURE','get_accrual');
494: hr_utility.set_message_token('STEP','5');
495: hr_utility.raise_error;
496: END IF;
497: CLOSE csr_get_period;
490: THEN
491: CLOSE csr_get_period;
492: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
493: hr_utility.set_message_token('PROCEDURE','get_accrual');
494: hr_utility.set_message_token('STEP','5');
495: hr_utility.raise_error;
496: END IF;
497: CLOSE csr_get_period;
498: --
491: CLOSE csr_get_period;
492: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
493: hr_utility.set_message_token('PROCEDURE','get_accrual');
494: hr_utility.set_message_token('STEP','5');
495: hr_utility.raise_error;
496: END IF;
497: CLOSE csr_get_period;
498: --
499: -- Check termination date and adjust end date of the last calc Period
505: p_param_acc_calc_edt;
506: IF csr_get_period%NOTFOUND
507: THEN
508: CLOSE csr_get_period;
509: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
510: hr_utility.set_message_token('PROCEDURE','get_accrual');
511: hr_utility.set_message_token('STEP','6');
512: hr_utility.raise_error;
513: END IF;
506: IF csr_get_period%NOTFOUND
507: THEN
508: CLOSE csr_get_period;
509: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
510: hr_utility.set_message_token('PROCEDURE','get_accrual');
511: hr_utility.set_message_token('STEP','6');
512: hr_utility.raise_error;
513: END IF;
514: CLOSE csr_get_period;
507: THEN
508: CLOSE csr_get_period;
509: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
510: hr_utility.set_message_token('PROCEDURE','get_accrual');
511: hr_utility.set_message_token('STEP','6');
512: hr_utility.raise_error;
513: END IF;
514: CLOSE csr_get_period;
515: --
508: CLOSE csr_get_period;
509: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
510: hr_utility.set_message_token('PROCEDURE','get_accrual');
511: hr_utility.set_message_token('STEP','6');
512: hr_utility.raise_error;
513: END IF;
514: CLOSE csr_get_period;
515: --
516: hr_utility.set_location('get_accrual',35);
512: hr_utility.raise_error;
513: END IF;
514: CLOSE csr_get_period;
515: --
516: hr_utility.set_location('get_accrual',35);
517: --
518: -- No accruals for the partial periods
519: --
520: IF nvl(l_termination_date,P_calculation_date) < p_param_acc_calc_edt
518: -- No accruals for the partial periods
519: --
520: IF nvl(l_termination_date,P_calculation_date) < p_param_acc_calc_edt
521: THEN
522: hr_utility.set_location('get_accrual',36);
523: p_param_acc_calc_pno := p_param_acc_calc_pno - 1;
524: p_param_acc_calc_edt := l_temp_date - 1;
525:
526: END IF;
526: END IF;
527: --
528: -- Open plan cursor and check at least one plan should be there
529: --
530: hr_utility.set_location('get_accrual',40);
531: OPEN csr_get_plan_details(l_business_group_id);
532: FETCH csr_get_plan_details INTO l_acc_plan_type_id,
533: l_acc_plan_ele_type,
534: l_acc_uom,
539: l_element_entry_id;
540: IF csr_get_plan_details%NOTFOUND
541: THEN
542: CLOSE csr_get_plan_details;
543: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
544: hr_utility.set_message_token('PROCEDURE','get_accrual');
545: hr_utility.set_message_token('STEP','7');
546: hr_utility.raise_error;
547: END IF;
540: IF csr_get_plan_details%NOTFOUND
541: THEN
542: CLOSE csr_get_plan_details;
543: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
544: hr_utility.set_message_token('PROCEDURE','get_accrual');
545: hr_utility.set_message_token('STEP','7');
546: hr_utility.raise_error;
547: END IF;
548: --
541: THEN
542: CLOSE csr_get_plan_details;
543: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
544: hr_utility.set_message_token('PROCEDURE','get_accrual');
545: hr_utility.set_message_token('STEP','7');
546: hr_utility.raise_error;
547: END IF;
548: --
549: -- Loop thru all the plans and call function to calc. accruals for a plan
542: CLOSE csr_get_plan_details;
543: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
544: hr_utility.set_message_token('PROCEDURE','get_accrual');
545: hr_utility.set_message_token('STEP','7');
546: hr_utility.raise_error;
547: END IF;
548: --
549: -- Loop thru all the plans and call function to calc. accruals for a plan
550: --
547: END IF;
548: --
549: -- Loop thru all the plans and call function to calc. accruals for a plan
550: --
551: hr_utility.set_location('get_accrual',45);
552: LOOP
553: l_temp_date := null;
554: --
555: hr_utility.set_location('get_accrual',50);
551: hr_utility.set_location('get_accrual',45);
552: LOOP
553: l_temp_date := null;
554: --
555: hr_utility.set_location('get_accrual',50);
556: --
557: -- "Continous Service Date" is ALWAYS determined by:
558: -- 1. "Continuous Service Date" entry value on accrual plan.
559: -- 2. Hire Date of current period of service (ie. in absence of 1.)
559: -- 2. Hire Date of current period of service (ie. in absence of 1.)
560: --
561: IF l_csd_screen_value is null
562: THEN
563: hr_utility.set_location('get_accrual',51);
564: l_cont_service_date := l_service_start_date;
565: ELSE
566: hr_utility.set_location('get_accrual',52);
567: l_cont_service_date := fnd_date.canonical_to_date(l_csd_screen_value);
562: THEN
563: hr_utility.set_location('get_accrual',51);
564: l_cont_service_date := l_service_start_date;
565: ELSE
566: hr_utility.set_location('get_accrual',52);
567: l_cont_service_date := fnd_date.canonical_to_date(l_csd_screen_value);
568: END IF;
569: --
570: -- The "p_param_first..." variables determine when accrual begins for this
586: p_param_first_pendt;
587: IF csr_get_period%NOTFOUND
588: THEN
589: CLOSE csr_get_period;
590: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
591: hr_utility.set_message_token('PROCEDURE','get_accrual');
592: hr_utility.set_message_token('STEP','8');
593: hr_utility.raise_error;
594: END IF;
587: IF csr_get_period%NOTFOUND
588: THEN
589: CLOSE csr_get_period;
590: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
591: hr_utility.set_message_token('PROCEDURE','get_accrual');
592: hr_utility.set_message_token('STEP','8');
593: hr_utility.raise_error;
594: END IF;
595: CLOSE csr_get_period;
588: THEN
589: CLOSE csr_get_period;
590: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
591: hr_utility.set_message_token('PROCEDURE','get_accrual');
592: hr_utility.set_message_token('STEP','8');
593: hr_utility.raise_error;
594: END IF;
595: CLOSE csr_get_period;
596: l_temp_date := null;
589: CLOSE csr_get_period;
590: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
591: hr_utility.set_message_token('PROCEDURE','get_accrual');
592: hr_utility.set_message_token('STEP','8');
593: hr_utility.raise_error;
594: END IF;
595: CLOSE csr_get_period;
596: l_temp_date := null;
597: ELSIF l_accrual_start = 'HD'
626: l_temp_date;
627: IF csr_get_period%NOTFOUND
628: THEN
629: CLOSE csr_get_period;
630: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
631: hr_utility.set_message_token('PROCEDURE','get_accrual');
632: hr_utility.set_message_token('STEP','10');
633: hr_utility.raise_error;
634: END IF;
627: IF csr_get_period%NOTFOUND
628: THEN
629: CLOSE csr_get_period;
630: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
631: hr_utility.set_message_token('PROCEDURE','get_accrual');
632: hr_utility.set_message_token('STEP','10');
633: hr_utility.raise_error;
634: END IF;
635: CLOSE csr_get_period;
628: THEN
629: CLOSE csr_get_period;
630: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
631: hr_utility.set_message_token('PROCEDURE','get_accrual');
632: hr_utility.set_message_token('STEP','10');
633: hr_utility.raise_error;
634: END IF;
635: CLOSE csr_get_period;
636: --
629: CLOSE csr_get_period;
630: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
631: hr_utility.set_message_token('PROCEDURE','get_accrual');
632: hr_utility.set_message_token('STEP','10');
633: hr_utility.raise_error;
634: END IF;
635: CLOSE csr_get_period;
636: --
637: OPEN csr_get_period (P_payroll_id, l_temp_date + 1);
640: p_param_first_pendt;
641: IF csr_get_period%NOTFOUND
642: THEN
643: CLOSE csr_get_period;
644: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
645: hr_utility.set_message_token('PROCEDURE','get_accrual');
646: hr_utility.set_message_token('STEP','11');
647: hr_utility.raise_error;
648: END IF;
641: IF csr_get_period%NOTFOUND
642: THEN
643: CLOSE csr_get_period;
644: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
645: hr_utility.set_message_token('PROCEDURE','get_accrual');
646: hr_utility.set_message_token('STEP','11');
647: hr_utility.raise_error;
648: END IF;
649: CLOSE csr_get_period;
642: THEN
643: CLOSE csr_get_period;
644: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
645: hr_utility.set_message_token('PROCEDURE','get_accrual');
646: hr_utility.set_message_token('STEP','11');
647: hr_utility.raise_error;
648: END IF;
649: CLOSE csr_get_period;
650: l_temp_date := null;
643: CLOSE csr_get_period;
644: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
645: hr_utility.set_message_token('PROCEDURE','get_accrual');
646: hr_utility.set_message_token('STEP','11');
647: hr_utility.raise_error;
648: END IF;
649: CLOSE csr_get_period;
650: l_temp_date := null;
651: END IF;
648: END IF;
649: CLOSE csr_get_period;
650: l_temp_date := null;
651: END IF;
652: hr_utility.set_location('get_accrual',55);
653: --
654: -- Add period of ineligibility
655: --
656: IF l_accrual_start <> 'PLUS_SIX_MONTHS' AND
655: --
656: IF l_accrual_start <> 'PLUS_SIX_MONTHS' AND
657: l_inelig_p_length > 0
658: THEN
659: hr_utility.set_location('get_accrual',60);
660: IF l_inelig_period = 'BM'
661: THEN
662: l_temp_date := ADD_MONTHS(l_cont_service_date,
663: (l_inelig_p_length * 2));
700: -- Determine start and end date and setup return parmas.
701: -- check Period of Service start date, plan element entry start date
702: -- if later then first period start. Accrual period start date accordingly.
703: --
704: hr_utility.set_location('get_accrual',65);
705: select min(effective_start_date)
706: into l_plan_start_date
707: from pay_element_entries_f
708: where element_entry_id = l_element_entry_id;
705: select min(effective_start_date)
706: into l_plan_start_date
707: from pay_element_entries_f
708: where element_entry_id = l_element_entry_id;
709: hr_utility.set_location('get_accrual',67);
710: ---
711:
712: --- Set the return params
713: --
717: P_end_date := LEAST(NVL(L_termination_date,P_calculation_date)
718: ,P_calculation_date);
719:
720: --
721: hr_utility.set_location('get_accrual',68);
722: IF ( l_temp_date is not null AND
723: l_temp_date >= p_param_acc_calc_edt ) OR
724: l_cont_service_date >= p_param_acc_calc_edt OR
725:
725:
726: p_param_first_pstdt >= p_param_acc_calc_edt
727:
728: THEN
729: hr_utility.set_location('get_accrual',70);
730: l_plan_accrual := 0;
731: ELSE
732: --
733: -- Set the Start Date appropriately.
743: --
744: IF l_temp_date > P_first_period_start
745: AND l_temp_date > nvl(p_param_first_pstdt, l_temp_date - 1)
746: THEN
747: hr_utility.set_location('get_accrual',71);
748: OPEN csr_get_period (P_payroll_id, l_temp_date);
749: FETCH csr_get_period INTO p_param_first_pnum,
750: p_param_first_pstdt,
751: p_param_first_pendt;
751: p_param_first_pendt;
752: IF csr_get_period%NOTFOUND
753: THEN
754: CLOSE csr_get_period;
755: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
756: hr_utility.set_message_token('PROCEDURE','get_accrual');
757: hr_utility.set_message_token('STEP','12');
758: hr_utility.raise_error;
759: END IF;
752: IF csr_get_period%NOTFOUND
753: THEN
754: CLOSE csr_get_period;
755: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
756: hr_utility.set_message_token('PROCEDURE','get_accrual');
757: hr_utility.set_message_token('STEP','12');
758: hr_utility.raise_error;
759: END IF;
760: CLOSE csr_get_period;
753: THEN
754: CLOSE csr_get_period;
755: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
756: hr_utility.set_message_token('PROCEDURE','get_accrual');
757: hr_utility.set_message_token('STEP','12');
758: hr_utility.raise_error;
759: END IF;
760: CLOSE csr_get_period;
761: hr_utility.set_location('get_accrual',80);
754: CLOSE csr_get_period;
755: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
756: hr_utility.set_message_token('PROCEDURE','get_accrual');
757: hr_utility.set_message_token('STEP','12');
758: hr_utility.raise_error;
759: END IF;
760: CLOSE csr_get_period;
761: hr_utility.set_location('get_accrual',80);
762: --
757: hr_utility.set_message_token('STEP','12');
758: hr_utility.raise_error;
759: END IF;
760: CLOSE csr_get_period;
761: hr_utility.set_location('get_accrual',80);
762: --
763: -- No Accruals fro the partial periods. First period to start the
764: -- accrual will be next one.
765: --
764: -- accrual will be next one.
765: --
766: IF l_temp_date > p_param_first_pstdt
767: THEN
768: hr_utility.set_location('get_accrual',85);
769: p_param_first_pendt := p_param_first_pendt +1;
770: OPEN csr_get_period (P_payroll_id, p_param_first_pendt);
771: FETCH csr_get_period INTO p_param_first_pnum,
772: p_param_first_pstdt,
773: p_param_first_pendt;
774: IF csr_get_period%NOTFOUND
775: THEN
776: CLOSE csr_get_period;
777: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
778: hr_utility.set_message_token('PROCEDURE','get_accrual');
779: hr_utility.set_message_token('STEP','13');
780: hr_utility.raise_error;
781: END IF;
774: IF csr_get_period%NOTFOUND
775: THEN
776: CLOSE csr_get_period;
777: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
778: hr_utility.set_message_token('PROCEDURE','get_accrual');
779: hr_utility.set_message_token('STEP','13');
780: hr_utility.raise_error;
781: END IF;
782: CLOSE csr_get_period;
775: THEN
776: CLOSE csr_get_period;
777: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
778: hr_utility.set_message_token('PROCEDURE','get_accrual');
779: hr_utility.set_message_token('STEP','13');
780: hr_utility.raise_error;
781: END IF;
782: CLOSE csr_get_period;
783: END IF;
776: CLOSE csr_get_period;
777: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
778: hr_utility.set_message_token('PROCEDURE','get_accrual');
779: hr_utility.set_message_token('STEP','13');
780: hr_utility.raise_error;
781: END IF;
782: CLOSE csr_get_period;
783: END IF;
784: END IF;
789: THEN
790: l_plan_accrual := 0;
791: ELSE
792: --
793: hr_utility.set_location('get_accrual_for_plan',90);
794: pay_us_pto_accrual.get_accrual_for_plan
795: ( p_plan_id => l_acc_plan_type_id,
796: p_first_p_start_date => p_param_first_pstdt,
797: p_first_p_end_date => p_param_first_pendt,
811: END IF;
812: --
813: -- Add accrual to the total and Fetch next set of plan
814: --
815: hr_utility.set_location('get_accrual',95);
816: l_total_accrual := l_total_accrual + l_plan_accrual;
817: l_plan_accrual := 0;
818:
819: FETCH csr_get_plan_details INTO l_acc_plan_type_id,
826: l_element_entry_id;
827: --
828:
829: EXIT WHEN csr_get_plan_details%NOTFOUND;
830: hr_utility.set_location('get_accrual',100);
831: --
832: END LOOP;
833: --
834: CLOSE csr_get_plan_details;
834: CLOSE csr_get_plan_details;
835: --
836: IF l_total_accrual is null
837: THEN
838: hr_utility.set_location('get_accrual',105);
839: l_total_accrual := 0;
840: END IF;
841: hr_utility.set_location('get_accrual',110);
842: l_total_accrual := round(l_total_accrual,3);
837: THEN
838: hr_utility.set_location('get_accrual',105);
839: l_total_accrual := 0;
840: END IF;
841: hr_utility.set_location('get_accrual',110);
842: l_total_accrual := round(l_total_accrual,3);
843: P_accrual := l_total_accrual;
844: --
845: -- Partial first period if end
977: --
978: --
979: BEGIN
980: --
981: hr_utility.set_location('get_accrual_for_plan',1);
982: l_year_1 := TRUNC(ABS(months_between(P_continuous_service_date,
983: P_first_p_end_date)/12));
984: l_year_2 := TRUNC(ABS(months_between(P_continuous_service_date,
985: p_accrual_calc_p_end_date)/12));
990: OPEN csr_get_bands (l_year_1);
991: FETCH csr_get_bands INTO l_annual_rate,l_ceiling_1,
992: l_lower_limit,l_upper_limit,
993: l_carry_over_1;
994: hr_utility.set_location('get_accrual_for_plan',5);
995:
996: IF csr_get_bands%NOTFOUND THEN
997: l_acc_rate_pp_1 := 0;
998: ELSE
1001: l_ceiling_flag := 'Y';
1002: END IF;
1003: END IF;
1004: CLOSE csr_get_bands;
1005: hr_utility.set_location('get_accrual_for_plan',10);
1006: --
1007: IF l_year_2 < l_upper_limit and l_acc_rate_pp_1 > 0 THEN
1008: l_acc_rate_pp_2 := 0;
1009: ELSE
1006: --
1007: IF l_year_2 < l_upper_limit and l_acc_rate_pp_1 > 0 THEN
1008: l_acc_rate_pp_2 := 0;
1009: ELSE
1010: hr_utility.set_location('get_accrual_for_plan',15);
1011: OPEN csr_get_bands (l_year_2);
1012: FETCH csr_get_bands INTO l_annual_rate,l_ceiling_2,
1013: l_lower_limit,l_upper_limit,
1014: l_carry_over_2;
1027: END IF;
1028: CLOSE csr_get_bands;
1029: END IF;
1030: END IF;
1031: hr_utility.set_location('get_accrual_for_plan',20);
1032: --
1033: --
1034: IF ((l_acc_rate_pp_1 <> l_acc_rate_pp_2) AND
1035: l_acc_rate_pp_2 <> 0 ) THEN
1058: ELSE
1059: P_current_carry_over := l_carry_over_2;
1060: END IF;
1061: --
1062: hr_utility.set_location('get_accrual_for_plan',25);
1063: OPEN csr_all_asg_status;
1064: FETCH csr_all_asg_status into l_asg_eff_start_date,
1065: l_asg_eff_end_date,
1066: l_asg_status;
1063: OPEN csr_all_asg_status;
1064: FETCH csr_all_asg_status into l_asg_eff_start_date,
1065: l_asg_eff_end_date,
1066: l_asg_status;
1067: hr_utility.set_location('get_accrual_for_plan',30);
1068: --
1069: -- Check if calc method should use ceiling calculation or Non-ceiling
1070: -- calculation. For simplicity if there is any asg. status change then
1071: -- ceiling calculation method is used.
1078: -- Non Ceiling Calc
1079: --
1080: OPEN csr_get_period(P_Payroll_id, l_band_change_date);
1081: FETCH csr_get_period INTO l_curr_p_num,l_curr_p_stdt,l_curr_p_endt;
1082: hr_utility.set_location('get_accrual_for_plan',35);
1083: IF csr_get_period%NOTFOUND THEN
1084: CLOSE csr_get_period;
1085: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1086: hr_utility.set_message_token('PROCEDURE','get_accrual_for_plan');
1081: FETCH csr_get_period INTO l_curr_p_num,l_curr_p_stdt,l_curr_p_endt;
1082: hr_utility.set_location('get_accrual_for_plan',35);
1083: IF csr_get_period%NOTFOUND THEN
1084: CLOSE csr_get_period;
1085: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1086: hr_utility.set_message_token('PROCEDURE','get_accrual_for_plan');
1087: hr_utility.set_message_token('STEP','14');
1088: hr_utility.raise_error;
1089: END IF;
1082: hr_utility.set_location('get_accrual_for_plan',35);
1083: IF csr_get_period%NOTFOUND THEN
1084: CLOSE csr_get_period;
1085: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1086: hr_utility.set_message_token('PROCEDURE','get_accrual_for_plan');
1087: hr_utility.set_message_token('STEP','14');
1088: hr_utility.raise_error;
1089: END IF;
1090: CLOSE csr_get_period;
1083: IF csr_get_period%NOTFOUND THEN
1084: CLOSE csr_get_period;
1085: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1086: hr_utility.set_message_token('PROCEDURE','get_accrual_for_plan');
1087: hr_utility.set_message_token('STEP','14');
1088: hr_utility.raise_error;
1089: END IF;
1090: CLOSE csr_get_period;
1091: --
1084: CLOSE csr_get_period;
1085: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1086: hr_utility.set_message_token('PROCEDURE','get_accrual_for_plan');
1087: hr_utility.set_message_token('STEP','14');
1088: hr_utility.raise_error;
1089: END IF;
1090: CLOSE csr_get_period;
1091: --
1092: -- gpaytonm 15-nov mod - added close csr_get_period
1091: --
1092: -- gpaytonm 15-nov mod - added close csr_get_period
1093: --
1094: --
1095: hr_utility.set_location('get_accrual_for_plan',40);
1096: if l_curr_p_num = 1 AND
1097: p_accrual_calc_p_end_date < l_band_change_date
1098: then
1099: l_curr_p_num := P_number_of_periods;
1105: --
1106: -- Entitlement from first period to Band change date.
1107: --
1108: l_accrual := l_acc_rate_pp_1 * (l_curr_p_num - (p_first_calc_P_number - 1));
1109: hr_utility.set_location('get_accrual_for_plan',45);
1110: --
1111: -- Entitlement from Band change date to Calc. date
1112: --
1113: IF p_accrual_calc_p_end_date >= l_band_change_date THEN
1116: ELSE
1117: --
1118: -- Ceiling Calc
1119: --
1120: hr_utility.set_location('get_accrual_for_plan',50);
1121: OPEN csr_get_time_periods;
1122: l_running_total := 0;
1123: l_curr_p_acc := 0;
1124: LOOP
1121: OPEN csr_get_time_periods;
1122: l_running_total := 0;
1123: l_curr_p_acc := 0;
1124: LOOP
1125: hr_utility.set_location('get_accrual_for_plan',55);
1126: FETCH csr_get_time_periods into l_start_Date,
1127: l_end_date,
1128: l_period_num;
1129: EXIT WHEN csr_get_time_periods%NOTFOUND;
1141: l_working_day := 0;
1142: l_unpaid_day := 0;
1143: l_vac_taken := 0;
1144: l_prev_end_date := l_asg_eff_end_date;
1145: hr_utility.set_location('get_accrual_for_plan',60);
1146: --
1147: IF l_asg_eff_end_date between l_start_Date and l_end_date
1148: THEN
1149: IF l_asg_status <> 'ACTIVE_ASSIGN' THEN
1151: l_asg_eff_end_date);
1152: END IF;
1153: --
1154: --
1155: hr_utility.set_location('get_accrual_for_plan',65);
1156: LOOP
1157: hr_utility.set_location('get_accrual_for_plan',70);
1158: l_prev_end_date := l_asg_eff_end_date;
1159: FETCH csr_all_asg_status into l_asg_eff_start_date,
1153: --
1154: --
1155: hr_utility.set_location('get_accrual_for_plan',65);
1156: LOOP
1157: hr_utility.set_location('get_accrual_for_plan',70);
1158: l_prev_end_date := l_asg_eff_end_date;
1159: FETCH csr_all_asg_status into l_asg_eff_start_date,
1160: l_asg_eff_end_date,
1161: l_asg_status;
1174: --
1175: --
1176: ELSIF csr_all_asg_status%ISOPEN and l_asg_status <> 'ACTIVE_ASSIGN' THEN
1177: l_mult_factor := 0;
1178: hr_utility.set_location('get_accrual_for_plan',75);
1179: ELSIF NOT (csr_all_asg_status%ISOPEN ) THEN
1180: hr_utility.set_location('get_accrual_for_plan',80);
1181: l_mult_factor := 0;
1182: ELSE
1176: ELSIF csr_all_asg_status%ISOPEN and l_asg_status <> 'ACTIVE_ASSIGN' THEN
1177: l_mult_factor := 0;
1178: hr_utility.set_location('get_accrual_for_plan',75);
1179: ELSIF NOT (csr_all_asg_status%ISOPEN ) THEN
1180: hr_utility.set_location('get_accrual_for_plan',80);
1181: l_mult_factor := 0;
1182: ELSE
1183: hr_utility.set_location('get_accrual_for_plan',85);
1184: l_mult_factor := 1;
1179: ELSIF NOT (csr_all_asg_status%ISOPEN ) THEN
1180: hr_utility.set_location('get_accrual_for_plan',80);
1181: l_mult_factor := 0;
1182: ELSE
1183: hr_utility.set_location('get_accrual_for_plan',85);
1184: l_mult_factor := 1;
1185: END IF;
1186: --
1187: --
1185: END IF;
1186: --
1187: --
1188: IF l_unpaid_day <> 0 THEN
1189: hr_utility.set_location('get_accrual_for_plan',90);
1190: l_working_day := get_working_days(l_start_Date,l_end_date);
1191: IF l_working_day = l_unpaid_day THEN
1192: l_mult_factor := 0;
1193: ELSE
1197: --
1198: -- Find out vacation and carry over if the method is ceiling
1199: --
1200: IF l_ceiling_flag = 'Y' THEN
1201: hr_utility.set_location('get_accrual_for_plan',95);
1202: OPEN csr_calc_accrual(l_start_Date, l_end_date,
1203: P_assignment_id, P_plan_id);
1204: FETCH csr_calc_accrual INTO l_vac_taken;
1205: IF csr_calc_accrual%NOTFOUND or l_vac_taken is null THEN
1210: --
1211: -- Multiply the Accrual rate for the current band and Multiplication
1212: -- Factor to get current period accrual.
1213: --
1214: hr_utility.set_location('get_accrual_for_plan',100);
1215: IF (l_band_change_date between l_start_Date and l_end_date)
1216: OR ( l_band_change_date < l_end_date)
1217: THEN
1218: l_curr_p_acc := l_acc_rate_pp_2 * l_mult_factor;
1224: --
1225: --
1226: -- Check for ceiling limits
1227: --
1228: hr_utility.set_location('get_accrual_for_plan',105);
1229: IF l_ceiling_flag = 'Y' THEN
1230: l_running_total := l_running_total + l_vac_taken + l_curr_p_acc;
1231: IF l_running_total > l_curr_ceiling THEN
1232: IF (l_running_total - l_curr_ceiling) < l_curr_p_acc
1242: END IF;
1243: ELSE
1244: l_accrual := l_accrual + l_curr_p_acc;
1245: END IF;
1246: hr_utility.set_location('get_accrual_for_plan',110);
1247: --
1248: --
1249: -- #305751 Remove the END IF matching the removed IF above.
1250: --
1278: --
1279: BEGIN
1280: --
1281: -- Check for valid range
1282: hr_utility.set_location('get_working_days', 5);
1283: IF p_start_date > P_end_date THEN
1284: hr_utility.set_location('get_working_days', 8);
1285: RETURN l_total_days;
1286: END IF;
1280: --
1281: -- Check for valid range
1282: hr_utility.set_location('get_working_days', 5);
1283: IF p_start_date > P_end_date THEN
1284: hr_utility.set_location('get_working_days', 8);
1285: RETURN l_total_days;
1286: END IF;
1287: --
1288: l_curr_date := P_start_date;
1285: RETURN l_total_days;
1286: END IF;
1287: --
1288: l_curr_date := P_start_date;
1289: hr_utility.set_location('get_working_days', 10);
1290: LOOP
1291: l_curr_day := TO_CHAR(l_curr_date, 'DY');
1292: hr_utility.set_location('get_working_days', 15);
1293: IF UPPER(l_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
1288: l_curr_date := P_start_date;
1289: hr_utility.set_location('get_working_days', 10);
1290: LOOP
1291: l_curr_day := TO_CHAR(l_curr_date, 'DY');
1292: hr_utility.set_location('get_working_days', 15);
1293: IF UPPER(l_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
1294: l_total_days := l_total_days + 1;
1295: hr_utility.set_location('get_working_days', 20);
1296: END IF;
1291: l_curr_day := TO_CHAR(l_curr_date, 'DY');
1292: hr_utility.set_location('get_working_days', 15);
1293: IF UPPER(l_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
1294: l_total_days := l_total_days + 1;
1295: hr_utility.set_location('get_working_days', 20);
1296: END IF;
1297: l_curr_date := l_curr_date + 1;
1298: EXIT WHEN l_curr_date > P_end_date;
1299: END LOOP;
1348: d7 date;
1349: --
1350: BEGIN
1351: --
1352: hr_utility.set_location('Entering: '||l_proc, 10);
1353:
1354: IF NOT use_fast_formula(p_effective_date => p_calculation_date
1355: ,p_plan_id => p_plan_id) THEN
1356: --
1361: -- payroll balance.
1362: -- For this reason, the old 10.7 code is called because it
1363: -- is significantly faster than executing Fast Formula.
1364: --
1365: hr_utility.set_location(l_proc, 20);
1366:
1367: pay_us_pto_accrual.net_accruals(
1368: P_assignment_id => P_assignment_id,
1369: P_calculation_date => c_date,
1415: else
1416: l_assignment_action_id := p_assignment_action_id;
1417: end if;
1418:
1419: hr_utility.set_location(l_proc, 30);
1420:
1421: per_accrual_calc_functions.get_net_accrual(
1422: P_assignment_id => p_assignment_id,
1423: P_plan_id => p_plan_id,
1436: );
1437: --
1438: end if;
1439:
1440: hr_utility.trace('l_entitlement: '||to_char(l_entitlement));
1441:
1442: hr_utility.set_location('Leaving: '||l_proc, 90);
1443:
1444: RETURN(l_entitlement);
1438: end if;
1439:
1440: hr_utility.trace('l_entitlement: '||to_char(l_entitlement));
1441:
1442: hr_utility.set_location('Leaving: '||l_proc, 90);
1443:
1444: RETURN(l_entitlement);
1445: --
1446: END get_net_accrual;
1479: BEGIN
1480: --
1481: -- Get vaction accrued
1482: --
1483: hr_utility.set_location('get_net_accrual',5);
1484: pay_us_pto_accrual.accrual_calc_detail(
1485: P_assignment_id => P_assignment_id,
1486: P_calculation_date => P_calculation_date,
1487: P_plan_id => P_plan_id,
1510: THEN
1511: l_taken := 0;
1512: END IF;
1513: CLOSE csr_calc_accrual;
1514: hr_utility.set_location('get_net_accrual',20);
1515: --
1516: --
1517: P_net_accrual := ROUND((P_accrual + l_taken),3);
1518:
1522: --
1523: IF P_mode = 'C'
1524: THEN
1525: OPEN csr_get_period(p_payroll_id,(P_last_period_end +1));
1526: hr_utility.set_location('get_net_accrual',21);
1527: FETCH csr_get_period into l_temp,P_start_date,P_end_date;
1528: IF csr_get_period%NOTFOUND THEN
1529: CLOSE csr_get_period;
1530: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1526: hr_utility.set_location('get_net_accrual',21);
1527: FETCH csr_get_period into l_temp,P_start_date,P_end_date;
1528: IF csr_get_period%NOTFOUND THEN
1529: CLOSE csr_get_period;
1530: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1531: hr_utility.set_message_token('PROCEDURE','net_accruals');
1532: hr_utility.set_message_token('STEP','15');
1533: hr_utility.raise_error;
1534: END IF;
1527: FETCH csr_get_period into l_temp,P_start_date,P_end_date;
1528: IF csr_get_period%NOTFOUND THEN
1529: CLOSE csr_get_period;
1530: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1531: hr_utility.set_message_token('PROCEDURE','net_accruals');
1532: hr_utility.set_message_token('STEP','15');
1533: hr_utility.raise_error;
1534: END IF;
1535: CLOSE csr_get_period;
1528: IF csr_get_period%NOTFOUND THEN
1529: CLOSE csr_get_period;
1530: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1531: hr_utility.set_message_token('PROCEDURE','net_accruals');
1532: hr_utility.set_message_token('STEP','15');
1533: hr_utility.raise_error;
1534: END IF;
1535: CLOSE csr_get_period;
1536: hr_utility.set_location('get_net_accrual',22);
1529: CLOSE csr_get_period;
1530: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1531: hr_utility.set_message_token('PROCEDURE','net_accruals');
1532: hr_utility.set_message_token('STEP','15');
1533: hr_utility.raise_error;
1534: END IF;
1535: CLOSE csr_get_period;
1536: hr_utility.set_location('get_net_accrual',22);
1537: END IF;
1532: hr_utility.set_message_token('STEP','15');
1533: hr_utility.raise_error;
1534: END IF;
1535: CLOSE csr_get_period;
1536: hr_utility.set_location('get_net_accrual',22);
1537: END IF;
1538: --
1539: --
1540: END net_accruals;