206:
207: Description: User-Defined
208: Functions required for US
209: implementations.
210: 19-MAY-99 gpaytonm 115.12 Removed hr_utility.trace
211: 20-MAY-99 VMehta 115.13 Converted p_tax_unit_id to
212: character while comparing it to segment1 in hr_soft_coding_
213: keyflex table to overcome
214: 'invali number' error.
517:
518: --p_wsched := 'NOT ENTERED';
519: v_prorated_earn := 0;
520:
521: hr_utility.trace('UDFS Entered Prorate Earnings');
522: hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
518: --p_wsched := 'NOT ENTERED';
519: v_prorated_earn := 0;
520:
521: hr_utility.trace('UDFS Entered Prorate Earnings');
522: hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
519: v_prorated_earn := 0;
520:
521: hr_utility.trace('UDFS Entered Prorate Earnings');
522: hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
520:
521: hr_utility.trace('UDFS Entered Prorate Earnings');
522: hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
528: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
521: hr_utility.trace('UDFS Entered Prorate Earnings');
522: hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
528: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
529: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
522: hr_utility.trace('p_bg_id ='||to_char(p_bg_id));
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
528: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
529: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
530:
523: hr_utility.trace('p_asg_hrly_rate ='||to_char(p_asg_hrly_rate));
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
528: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
529: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
530:
531: -- Prorate using hourly rate passed in as param:
524: hr_utility.trace('p_wsched ='||p_wsched);
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
528: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
529: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
530:
531: -- Prorate using hourly rate passed in as param:
532:
525: hr_utility.trace('p_asg_std_hours ='||to_char(p_asg_std_hours));
526: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
527: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
528: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
529: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
530:
531: -- Prorate using hourly rate passed in as param:
532:
533:
532:
533:
534: IF UPPER(p_wsched) = 'NOT ENTERED' THEN
535:
536: hr_utility.set_location('Prorate_Earnings', 7);
537: hr_utility.trace('p_wsched NOT ENTERED');
538: hr_utility.trace('Calling Standard Hours Worked');
539:
540: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
533:
534: IF UPPER(p_wsched) = 'NOT ENTERED' THEN
535:
536: hr_utility.set_location('Prorate_Earnings', 7);
537: hr_utility.trace('p_wsched NOT ENTERED');
538: hr_utility.trace('Calling Standard Hours Worked');
539:
540: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
541: p_range_start_date,
534: IF UPPER(p_wsched) = 'NOT ENTERED' THEN
535:
536: hr_utility.set_location('Prorate_Earnings', 7);
537: hr_utility.trace('p_wsched NOT ENTERED');
538: hr_utility.trace('Calling Standard Hours Worked');
539:
540: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
541: p_range_start_date,
542: p_range_end_date,
542: p_range_end_date,
543: p_asg_std_freq);
544:
545: -- Keep running total of ACTUAL hours worked.
546: hr_utility.set_location('Prorate_Earnings', 11);
547:
548: hr_utility.trace('Keep running total of ACTUAL hours worked');
549:
550: hr_utility.trace('actual_hours_worked before call= '||
544:
545: -- Keep running total of ACTUAL hours worked.
546: hr_utility.set_location('Prorate_Earnings', 11);
547:
548: hr_utility.trace('Keep running total of ACTUAL hours worked');
549:
550: hr_utility.trace('actual_hours_worked before call= '||
551: to_char(p_act_hrs_worked));
552: hr_utility.trace('v_hours_in_range in current call= '||
546: hr_utility.set_location('Prorate_Earnings', 11);
547:
548: hr_utility.trace('Keep running total of ACTUAL hours worked');
549:
550: hr_utility.trace('actual_hours_worked before call= '||
551: to_char(p_act_hrs_worked));
552: hr_utility.trace('v_hours_in_range in current call= '||
553: to_char(v_hours_in_range));
554:
548: hr_utility.trace('Keep running total of ACTUAL hours worked');
549:
550: hr_utility.trace('actual_hours_worked before call= '||
551: to_char(p_act_hrs_worked));
552: hr_utility.trace('v_hours_in_range in current call= '||
553: to_char(v_hours_in_range));
554:
555: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
556:
553: to_char(v_hours_in_range));
554:
555: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
556:
557: hr_utility.trace('UDFS actual_hours_worked after call = '||
558: to_char(p_act_hrs_worked));
559:
560: ELSE
561:
558: to_char(p_act_hrs_worked));
559:
560: ELSE
561:
562: hr_utility.set_location('Prorate_Earnings', 17);
563: hr_utility.trace('Entered WORK SCHEDULE');
564:
565: hr_utility.trace('Getting WORK SCHEDULE Name');
566:
559:
560: ELSE
561:
562: hr_utility.set_location('Prorate_Earnings', 17);
563: hr_utility.trace('Entered WORK SCHEDULE');
564:
565: hr_utility.trace('Getting WORK SCHEDULE Name');
566:
567: -- Get work schedule name:
561:
562: hr_utility.set_location('Prorate_Earnings', 17);
563: hr_utility.trace('Entered WORK SCHEDULE');
564:
565: hr_utility.trace('Getting WORK SCHEDULE Name');
566:
567: -- Get work schedule name:
568:
569: v_ws_id := fnd_number.canonical_to_number(p_wsched);
567: -- Get work schedule name:
568:
569: v_ws_id := fnd_number.canonical_to_number(p_wsched);
570:
571: hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
572:
573: SELECT user_column_name
574: INTO v_ws_name
575: FROM pay_user_columns
576: WHERE user_column_id = v_ws_id
577: AND NVL(business_group_id, p_bg_id) = p_bg_id
578: AND NVL(legislation_code,'US') = 'US';
579:
580: hr_utility.trace('v_ws_name ='||v_ws_name );
581: hr_utility.trace('Calling Work_Schedule_Total_Hours');
582:
583: v_hours_in_range := Work_Schedule_Total_Hours(
584: p_bg_id,
577: AND NVL(business_group_id, p_bg_id) = p_bg_id
578: AND NVL(legislation_code,'US') = 'US';
579:
580: hr_utility.trace('v_ws_name ='||v_ws_name );
581: hr_utility.trace('Calling Work_Schedule_Total_Hours');
582:
583: v_hours_in_range := Work_Schedule_Total_Hours(
584: p_bg_id,
585: v_ws_name,
586: p_range_start_date,
587: p_range_end_date);
588:
589: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
590: hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
591:
592: END IF; -- Hours in date range via work schedule or std hours.
593:
594: hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
590: hr_utility.trace('v_hours_in_range = '||to_char(v_hours_in_range));
591:
592: END IF; -- Hours in date range via work schedule or std hours.
593:
594: hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
595:
596: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
597:
598: hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
594: hr_utility.trace('v_prorated_earnings = p_asg_hrly_rate * v_hours_in_range');
595:
596: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
597:
598: hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
599: hr_utility.set_location('Prorate_Earnings', 97);
600: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
601: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
602: hr_utility.trace('UDFS Leaving Prorated Earnings');
595:
596: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
597:
598: hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
599: hr_utility.set_location('Prorate_Earnings', 97);
600: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
601: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
602: hr_utility.trace('UDFS Leaving Prorated Earnings');
603:
597:
598: hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
599: hr_utility.set_location('Prorate_Earnings', 97);
600: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
601: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
602: hr_utility.trace('UDFS Leaving Prorated Earnings');
603:
604: RETURN v_prorated_earn;
605:
598: hr_utility.trace('UDFS final v_prorated_earnings = '||to_char(v_prorated_earn));
599: hr_utility.set_location('Prorate_Earnings', 97);
600: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
601: hr_utility.trace('p_act_hrs_worked ='||to_char(p_act_hrs_worked));
602: hr_utility.trace('UDFS Leaving Prorated Earnings');
603:
604: RETURN v_prorated_earn;
605:
606: END Prorate_Earnings;
654: --p_work_sched := 'NOT ENTERED';
655: v_eev_prorated_earnings := 0;
656:
657:
658: hr_utility.trace('UDFS Entering PRORATE_EEV');
659: hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
655: v_eev_prorated_earnings := 0;
656:
657:
658: hr_utility.trace('UDFS Entering PRORATE_EEV');
659: hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
656:
657:
658: hr_utility.trace('UDFS Entering PRORATE_EEV');
659: hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
657:
658: hr_utility.trace('UDFS Entering PRORATE_EEV');
659: hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
658: hr_utility.trace('UDFS Entering PRORATE_EEV');
659: hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
659: hr_utility.trace('p_bus_group_id ='||to_char(p_bus_group_id));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
660: hr_utility.trace('p_pay_id ='||to_char(p_pay_id));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
661: hr_utility.trace('p_work_sched ='||p_work_sched);
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
669: hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
662: hr_utility.trace('p_asg_std_hrs ='||to_char(p_asg_std_hrs));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
669: hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
670: hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
663: hr_utility.trace('p_asg_std_freq ='||p_asg_std_freq);
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
669: hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
670: hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
671: --
664: hr_utility.trace('p_pay_basis ='||p_pay_basis);
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
669: hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
670: hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
671: --
672: -- Find all EEV changes, calculate new hourly rate, prorate:
665: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
669: hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
670: hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
671: --
672: -- Find all EEV changes, calculate new hourly rate, prorate:
673: -- SELECT (EEV1):
666: hr_utility.trace('UDFS p_range_start_date ='||to_char(p_range_start_date));
667: hr_utility.trace('UDFS p_range_end_date ='||to_char(p_range_end_date));
668: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
669: hr_utility.trace('p_element_entry_id ='||to_char(p_element_entry_id));
670: hr_utility.trace('p_inpval_id ='||to_char(p_inpval_id));
671: --
672: -- Find all EEV changes, calculate new hourly rate, prorate:
673: -- SELECT (EEV1):
674: -- Select for SINGLE record that includes Period Start Date but does not
675: -- span entire period.
676: -- We know this select will return a row, otherwise there would be no
677: -- EEV changes to detect.
678: --
679: hr_utility.set_location('Prorate_EEV', 103);
680: SELECT EEV.screen_entry_value,
681: GREATEST(EEV.effective_start_date, p_range_start_date),
682: EEV.effective_end_date
683: INTO v_earnings_entry,
690: AND EEV.effective_end_date >= p_range_start_date
691: AND EEV.effective_end_date < p_range_end_date;
692:
693:
694: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
695: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
696: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
697: hr_utility.trace('Calling Convert_Period_Type ');
698: hr_utility.set_location('Prorate_EEV', 105);
691: AND EEV.effective_end_date < p_range_end_date;
692:
693:
694: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
695: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
696: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
697: hr_utility.trace('Calling Convert_Period_Type ');
698: hr_utility.set_location('Prorate_EEV', 105);
699:
692:
693:
694: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
695: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
696: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
697: hr_utility.trace('Calling Convert_Period_Type ');
698: hr_utility.set_location('Prorate_EEV', 105);
699:
700: v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
693:
694: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
695: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
696: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
697: hr_utility.trace('Calling Convert_Period_Type ');
698: hr_utility.set_location('Prorate_EEV', 105);
699:
700: v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
701: p_pay_id,
694: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
695: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
696: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
697: hr_utility.trace('Calling Convert_Period_Type ');
698: hr_utility.set_location('Prorate_EEV', 105);
699:
700: v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
701: p_pay_id,
702: p_work_sched,
706: 'HOURLY',
707: p_period_start,
708: p_period_end,
709: p_asg_std_freq);
710: hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
711: hr_utility.set_location('Prorate_EEV', 107);
712:
713: v_eev_prorated_earnings := v_eev_prorated_earnings +
714: Prorate_Earnings (
707: p_period_start,
708: p_period_end,
709: p_asg_std_freq);
710: hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
711: hr_utility.set_location('Prorate_EEV', 107);
712:
713: v_eev_prorated_earnings := v_eev_prorated_earnings +
714: Prorate_Earnings (
715: p_bg_id => p_bus_group_id,
720: p_range_start_date => v_entry_start,
721: p_range_end_date => v_entry_end,
722: p_act_hrs_worked => p_actual_hrs_worked);
723:
724: hr_utility.trace('v_eev_prorated_earnings ='||
725: to_char(v_eev_prorated_earnings));
726: -- SELECT (EEV2):
727: hr_utility.trace('Opening get_entry_chgs cursor EEV2');
728:
723:
724: hr_utility.trace('v_eev_prorated_earnings ='||
725: to_char(v_eev_prorated_earnings));
726: -- SELECT (EEV2):
727: hr_utility.trace('Opening get_entry_chgs cursor EEV2');
728:
729: OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
730: LOOP
731: --
734: v_entry_start,
735: v_entry_end;
736: EXIT WHEN get_entry_chgs%NOTFOUND;
737: --
738: hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
739: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
740: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
741: hr_utility.set_location('Prorate_EEV', 115);
742: --
735: v_entry_end;
736: EXIT WHEN get_entry_chgs%NOTFOUND;
737: --
738: hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
739: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
740: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
741: hr_utility.set_location('Prorate_EEV', 115);
742: --
743: -- For each range of dates found, add to running prorated earnings total.
736: EXIT WHEN get_entry_chgs%NOTFOUND;
737: --
738: hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
739: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
740: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
741: hr_utility.set_location('Prorate_EEV', 115);
742: --
743: -- For each range of dates found, add to running prorated earnings total.
744: --
737: --
738: hr_utility.trace('v_earnings_entry ='||v_earnings_entry);
739: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
740: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
741: hr_utility.set_location('Prorate_EEV', 115);
742: --
743: -- For each range of dates found, add to running prorated earnings total.
744: --
745: hr_utility.trace('Calling Convert_Period_Type ');
741: hr_utility.set_location('Prorate_EEV', 115);
742: --
743: -- For each range of dates found, add to running prorated earnings total.
744: --
745: hr_utility.trace('Calling Convert_Period_Type ');
746:
747: v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
748: p_pay_id,
749: p_work_sched,
755: p_period_end,
756: p_asg_std_freq);
757:
758:
759: hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
760: hr_utility.set_location('Prorate_EEV', 119);
761: v_eev_prorated_earnings := v_eev_prorated_earnings +
762: Prorate_Earnings (
763: p_bg_id => p_bus_group_id,
756: p_asg_std_freq);
757:
758:
759: hr_utility.trace('v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
760: hr_utility.set_location('Prorate_EEV', 119);
761: v_eev_prorated_earnings := v_eev_prorated_earnings +
762: Prorate_Earnings (
763: p_bg_id => p_bus_group_id,
764: p_asg_hrly_rate => v_curr_hrly_rate,
768: p_range_start_date => v_entry_start,
769: p_range_end_date => v_entry_end,
770: p_act_hrs_worked => p_actual_hrs_worked);
771:
772: hr_utility.trace('v_eev_prorated_earnings ='||to_char(v_eev_prorated_earnings));
773:
774: END LOOP;
775: --
776: CLOSE get_entry_chgs;
779: -- Select for SINGLE record that exists across Period End Date:
780: -- NOTE: Will only return a row if select (2) does not return a row where
781: -- Effective End Date = Period End Date !
782:
783: hr_utility.trace('Select EEV3');
784: hr_utility.set_location('Prorate_EEV', 141);
785: SELECT EEV.screen_entry_value,
786: EEV.effective_start_date,
787: LEAST(EEV.effective_end_date, p_range_end_date)
780: -- NOTE: Will only return a row if select (2) does not return a row where
781: -- Effective End Date = Period End Date !
782:
783: hr_utility.trace('Select EEV3');
784: hr_utility.set_location('Prorate_EEV', 141);
785: SELECT EEV.screen_entry_value,
786: EEV.effective_start_date,
787: LEAST(EEV.effective_end_date, p_range_end_date)
788: INTO v_earnings_entry,
793: AND EEV.input_value_id = p_inpval_id
794: AND EEV.effective_start_date > p_range_start_date
795: AND EEV.effective_start_date <= p_range_end_date
796: AND EEV.effective_end_date > p_range_end_date;
797: hr_utility.set_location('Prorate_EEV', 147);
798: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
799: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
800: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
801:
794: AND EEV.effective_start_date > p_range_start_date
795: AND EEV.effective_start_date <= p_range_end_date
796: AND EEV.effective_end_date > p_range_end_date;
797: hr_utility.set_location('Prorate_EEV', 147);
798: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
799: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
800: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
801:
802: hr_utility.trace('Calling Convert_Period_Type ');
795: AND EEV.effective_start_date <= p_range_end_date
796: AND EEV.effective_end_date > p_range_end_date;
797: hr_utility.set_location('Prorate_EEV', 147);
798: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
799: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
800: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
801:
802: hr_utility.trace('Calling Convert_Period_Type ');
803:
796: AND EEV.effective_end_date > p_range_end_date;
797: hr_utility.set_location('Prorate_EEV', 147);
798: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
799: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
800: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
801:
802: hr_utility.trace('Calling Convert_Period_Type ');
803:
804: v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
798: hr_utility.trace('screen_entry_value ='||v_earnings_entry);
799: hr_utility.trace('v_entry_start ='||to_char(v_entry_start));
800: hr_utility.trace('v_entry_end ='||to_char(v_entry_end));
801:
802: hr_utility.trace('Calling Convert_Period_Type ');
803:
804: v_curr_hrly_rate := Convert_Period_Type( p_bus_group_id,
805: p_pay_id,
806: p_work_sched,
810: 'HOURLY',
811: p_period_start,
812: p_period_end,
813: p_asg_std_freq);
814: hr_utility.set_location('Prorate_EEV', 151);
815: hr_utility.trace('After Call v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
816:
817: v_eev_prorated_earnings := v_eev_prorated_earnings +
818: Prorate_Earnings (
811: p_period_start,
812: p_period_end,
813: p_asg_std_freq);
814: hr_utility.set_location('Prorate_EEV', 151);
815: hr_utility.trace('After Call v_curr_hrly_rate ='||to_char(v_curr_hrly_rate));
816:
817: v_eev_prorated_earnings := v_eev_prorated_earnings +
818: Prorate_Earnings (
819: p_bg_id => p_bus_group_id,
825: p_range_end_date => v_entry_end,
826: p_act_hrs_worked => p_actual_hrs_worked);
827:
828: -- We're Done!
829: hr_utility.trace('v_eev_prorated_earnings ='||
830: to_char(v_eev_prorated_earnings));
831: hr_utility.set_location('Prorate_EEV', 167);
832: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
833: p_hrly_rate := v_curr_hrly_rate;
827:
828: -- We're Done!
829: hr_utility.trace('v_eev_prorated_earnings ='||
830: to_char(v_eev_prorated_earnings));
831: hr_utility.set_location('Prorate_EEV', 167);
832: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
833: p_hrly_rate := v_curr_hrly_rate;
834:
835: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
831: hr_utility.set_location('Prorate_EEV', 167);
832: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
833: p_hrly_rate := v_curr_hrly_rate;
834:
835: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
836: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
837:
838: hr_utility.trace('UDFS Leaving Prorated EEV');
839:
832: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
833: p_hrly_rate := v_curr_hrly_rate;
834:
835: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
836: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
837:
838: hr_utility.trace('UDFS Leaving Prorated EEV');
839:
840: RETURN v_eev_prorated_earnings;
834:
835: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
836: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
837:
838: hr_utility.trace('UDFS Leaving Prorated EEV');
839:
840: RETURN v_eev_prorated_earnings;
841:
842: EXCEPTION WHEN NO_DATA_FOUND THEN
839:
840: RETURN v_eev_prorated_earnings;
841:
842: EXCEPTION WHEN NO_DATA_FOUND THEN
843: hr_utility.set_location('Prorate_EEV', 177);
844: hr_utility.trace('Into exception of Prorate_EEV');
845:
846: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
847: p_hrly_rate := v_curr_hrly_rate;
840: RETURN v_eev_prorated_earnings;
841:
842: EXCEPTION WHEN NO_DATA_FOUND THEN
843: hr_utility.set_location('Prorate_EEV', 177);
844: hr_utility.trace('Into exception of Prorate_EEV');
845:
846: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
847: p_hrly_rate := v_curr_hrly_rate;
848:
845:
846: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
847: p_hrly_rate := v_curr_hrly_rate;
848:
849: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
850: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
851:
852: RETURN v_eev_prorated_earnings;
853:
846: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
847: p_hrly_rate := v_curr_hrly_rate;
848:
849: hr_utility.trace('p_actual_hrs_worked ='||to_char(p_actual_hrs_worked));
850: hr_utility.trace('p_hrly_rate ='||to_char(p_hrly_rate));
851:
852: RETURN v_eev_prorated_earnings;
853:
854: END Prorate_EEV;
887:
888: /* Init */
889: l_vac_pay := 0;
890:
891: hr_utility.set_location('get_vac_pay', 11);
892: hr_utility.trace('Entered Vacation Pay');
893:
894: OPEN get_vac_hours (p_asg_id, p_eff_date);
895: LOOP
888: /* Init */
889: l_vac_pay := 0;
890:
891: hr_utility.set_location('get_vac_pay', 11);
892: hr_utility.trace('Entered Vacation Pay');
893:
894: OPEN get_vac_hours (p_asg_id, p_eff_date);
895: LOOP
896:
893:
894: OPEN get_vac_hours (p_asg_id, p_eff_date);
895: LOOP
896:
897: hr_utility.set_location('get_vac_pay', 13);
898: hr_utility.trace('Opened get_vac_hours');
899:
900: FETCH get_vac_hours
901: INTO l_vac_hours;
894: OPEN get_vac_hours (p_asg_id, p_eff_date);
895: LOOP
896:
897: hr_utility.set_location('get_vac_pay', 13);
898: hr_utility.trace('Opened get_vac_hours');
899:
900: FETCH get_vac_hours
901: INTO l_vac_hours;
902: EXIT WHEN get_vac_hours%NOTFOUND;
905:
906: END LOOP;
907: CLOSE get_vac_hours;
908:
909: hr_utility.set_location('get_vac_pay', 15);
910:
911: IF p_vac_hours <> 0 THEN
912:
913: l_vac_pay := p_vac_hours * p_curr_rate;
913: l_vac_pay := p_vac_hours * p_curr_rate;
914:
915: END IF;
916:
917: hr_utility.trace('Leaving Vacation Pay');
918: RETURN l_vac_pay;
919:
920: END vacation_pay;
921:
951:
952: /* Init */
953: l_sick_pay :=0;
954:
955: hr_utility.set_location('get_sick_pay', 11);
956: hr_utility.trace('Entered Sick Pay');
957:
958: OPEN get_sick_hours (p_asg_id, p_eff_date);
959: LOOP
952: /* Init */
953: l_sick_pay :=0;
954:
955: hr_utility.set_location('get_sick_pay', 11);
956: hr_utility.trace('Entered Sick Pay');
957:
958: OPEN get_sick_hours (p_asg_id, p_eff_date);
959: LOOP
960:
957:
958: OPEN get_sick_hours (p_asg_id, p_eff_date);
959: LOOP
960:
961: hr_utility.trace('get_sick_pay');
962: hr_utility.set_location('get_sick_pay', 13);
963:
964: FETCH get_sick_hours
965: INTO l_sick_hours;
958: OPEN get_sick_hours (p_asg_id, p_eff_date);
959: LOOP
960:
961: hr_utility.trace('get_sick_pay');
962: hr_utility.set_location('get_sick_pay', 13);
963:
964: FETCH get_sick_hours
965: INTO l_sick_hours;
966: EXIT WHEN get_sick_hours%NOTFOUND;
969:
970: END LOOP;
971: CLOSE get_sick_hours;
972:
973: hr_utility.set_location('get_sick_pay', 15);
974: hr_utility.trace('get_sick_pay');
975:
976: IF p_sick_hours <> 0 THEN
977:
970: END LOOP;
971: CLOSE get_sick_hours;
972:
973: hr_utility.set_location('get_sick_pay', 15);
974: hr_utility.trace('get_sick_pay');
975:
976: IF p_sick_hours <> 0 THEN
977:
978: l_sick_pay := p_sick_hours * p_curr_rate;
978: l_sick_pay := p_sick_hours * p_curr_rate;
979:
980: END IF;
981:
982: hr_utility.trace('Leaving get_sick_pay');
983: RETURN l_sick_pay;
984:
985: END sick_pay;
986:
992: v_prorated_earnings := 0;
993: v_curr_hrly_rate := 0;
994: l_mid_period_asg_change := FALSE;
995:
996: hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
997: hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
993: v_curr_hrly_rate := 0;
994: l_mid_period_asg_change := FALSE;
995:
996: hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
997: hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
994: l_mid_period_asg_change := FALSE;
995:
996: hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
997: hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
995:
996: hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
997: hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
996: hr_utility.trace('UDFS Entered Calculate_Period_Earnings');
997: hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
997: hr_utility.trace('p_asst_id ='||to_char(p_asst_id));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
998: hr_utility.trace('p_payroll_id ='||to_char(p_payroll_id));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
999: hr_utility.trace('p_ele_entry_id ='||to_char(p_ele_entry_id));
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1000: hr_utility.trace('p_tax_unit_id ='||to_char(p_tax_unit_id));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1001: hr_utility.trace('p_date_earned ='||to_char(p_date_earned));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1002: hr_utility.trace('p_pay_basis ='||p_pay_basis);
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1003: hr_utility.trace('p_inpval_name ='||p_inpval_name);
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1004: hr_utility.trace('p_ass_hrly_figure ='||to_char(p_ass_hrly_figure));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1005: hr_utility.trace('UDFS p_period_start ='||to_char(p_period_start));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1006: hr_utility.trace('UDFS p_period_end ='||to_char(p_period_end));
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1007: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1015: hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1008: hr_utility.trace('p_asst_std_hrs ='||to_char(p_asst_std_hrs));
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1015: hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1016:
1009: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1015: hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1016:
1017: hr_utility.trace('Find earnings element input value id');
1010: hr_utility.trace('p_vac_hours_worked ='||to_char(p_vac_hours_worked));
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1015: hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1016:
1017: hr_utility.trace('Find earnings element input value id');
1018:
1011: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1012: hr_utility.trace('p_sick_hours_worked ='||to_char(p_sick_hours_worked));
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1015: hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1016:
1017: hr_utility.trace('Find earnings element input value id');
1018:
1019: p_actual_hours_worked := 0;
1013: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1014: hr_utility.trace('UDFS p_prorate ='||p_prorate);
1015: hr_utility.trace('p_asst_std_freq ='||p_asst_std_freq);
1016:
1017: hr_utility.trace('Find earnings element input value id');
1018:
1019: p_actual_hours_worked := 0;
1020:
1021: -- Step (1): Find earnings element input value.
1026: IF p_pay_basis IS NOT NULL THEN
1027:
1028: BEGIN
1029:
1030: hr_utility.trace(' p_pay_basis IS NOT NULL');
1031: hr_utility.set_location('calculate_period_earnings', 10);
1032:
1033: SELECT PYB.input_value_id,
1034: FCL.meaning
1027:
1028: BEGIN
1029:
1030: hr_utility.trace(' p_pay_basis IS NOT NULL');
1031: hr_utility.set_location('calculate_period_earnings', 10);
1032:
1033: SELECT PYB.input_value_id,
1034: FCL.meaning
1035: INTO v_inpval_id,
1045: AND p_date_earned BETWEEN ASG.effective_start_date
1046: AND ASG.effective_end_date;
1047:
1048: EXCEPTION WHEN NO_DATA_FOUND THEN
1049: hr_utility.set_location('calculate_period_earnings', 11);
1050: hr_utility.trace(' In EXCEPTION p_pay_basis IS NOT NULL');
1051:
1052: v_period_earn := 0;
1053: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1046: AND ASG.effective_end_date;
1047:
1048: EXCEPTION WHEN NO_DATA_FOUND THEN
1049: hr_utility.set_location('calculate_period_earnings', 11);
1050: hr_utility.trace(' In EXCEPTION p_pay_basis IS NOT NULL');
1051:
1052: v_period_earn := 0;
1053: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1054:
1051:
1052: v_period_earn := 0;
1053: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1054:
1055: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1056:
1057: RETURN v_period_earn;
1058:
1059:
1058:
1059:
1060: END;
1061:
1062: hr_utility.trace('p_inpval_name = '||p_inpval_name);
1063:
1064: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
1065:
1066: hr_utility.trace(' p_pay_basis IS NULL');
1062: hr_utility.trace('p_inpval_name = '||p_inpval_name);
1063:
1064: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
1065:
1066: hr_utility.trace(' p_pay_basis IS NULL');
1067: hr_utility.trace('In p_inpval_name = RATE');
1068: /* Changed the element_name and name to init case and added
1069: the date join for pay_element_types_f */
1070:
1063:
1064: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
1065:
1066: hr_utility.trace(' p_pay_basis IS NULL');
1067: hr_utility.trace('In p_inpval_name = RATE');
1068: /* Changed the element_name and name to init case and added
1069: the date join for pay_element_types_f */
1070:
1071: begin
1085: v_pay_basis := 'HOURLY';
1086: --
1087: EXCEPTION WHEN NO_DATA_FOUND THEN
1088:
1089: hr_utility.trace('Exception of RATE ');
1090:
1091: v_period_earn := 0;
1092: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1093:
1090:
1091: v_period_earn := 0;
1092: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1093:
1094: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1095:
1096: RETURN v_period_earn;
1097: end;
1098: --
1100: /* Changed the element_name and name to init case and added
1101: the date join for pay_element_types_f */
1102:
1103: begin
1104: hr_utility.trace('In RATE CODE');
1105:
1106: SELECT IPV.input_value_id
1107: INTO v_inpval_id
1108: FROM pay_input_values_f IPV,
1118: --
1119: v_pay_basis := 'HOURLY';
1120: --
1121: EXCEPTION WHEN NO_DATA_FOUND THEN
1122: hr_utility.trace('Exception of Rate Code');
1123:
1124: v_period_earn := 0;
1125: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1126:
1123:
1124: v_period_earn := 0;
1125: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1126:
1127: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1128:
1129: RETURN v_period_earn;
1130:
1131: end;
1135: /* Changed the element_name and name to init case and added
1136: the date join for pay_element_types_f */
1137:
1138: begin
1139: hr_utility.trace('in MONTHLY SALARY');
1140:
1141: SELECT IPV.input_value_id
1142: INTO v_inpval_id
1143: FROM pay_input_values_f IPV,
1153:
1154: v_pay_basis := 'MONTHLY';
1155:
1156: EXCEPTION WHEN NO_DATA_FOUND THEN
1157: hr_utility.set_location('calculate_period_earnings', 18);
1158: v_period_earn := 0;
1159: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1160: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1161: RETURN v_period_earn;
1156: EXCEPTION WHEN NO_DATA_FOUND THEN
1157: hr_utility.set_location('calculate_period_earnings', 18);
1158: v_period_earn := 0;
1159: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1160: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1161: RETURN v_period_earn;
1162: END;
1163:
1164: END IF;
1162: END;
1163:
1164: END IF;
1165:
1166: hr_utility.trace('Now know the pay basis for this assignment');
1167: hr_utility.trace('v_inpval_id ='||to_char(v_inpval_id));
1168: hr_utility.trace('v_pay_basis ='||v_pay_basis);
1169: /*
1170: -- Now know the pay basis for this assignment (v_pay_basis).
1163:
1164: END IF;
1165:
1166: hr_utility.trace('Now know the pay basis for this assignment');
1167: hr_utility.trace('v_inpval_id ='||to_char(v_inpval_id));
1168: hr_utility.trace('v_pay_basis ='||v_pay_basis);
1169: /*
1170: -- Now know the pay basis for this assignment (v_pay_basis).
1171: -- Want to convert entered earnings to pay period earnings.
1164: END IF;
1165:
1166: hr_utility.trace('Now know the pay basis for this assignment');
1167: hr_utility.trace('v_inpval_id ='||to_char(v_inpval_id));
1168: hr_utility.trace('v_pay_basis ='||v_pay_basis);
1169: /*
1170: -- Now know the pay basis for this assignment (v_pay_basis).
1171: -- Want to convert entered earnings to pay period earnings.
1172: -- For pay basis of Annual, Monthly, Bi-Weekly, Semi-Monthly,
1202: -- Find # of payroll period types per fiscal year:
1203:
1204: begin
1205:
1206: hr_utility.trace('Find # of payroll period types per fiscal year');
1207: hr_utility.set_location('calculate_period_earnings', 40);
1208:
1209: SELECT TPT.number_per_fiscal_year
1210: INTO v_pay_periods_per_year
1203:
1204: begin
1205:
1206: hr_utility.trace('Find # of payroll period types per fiscal year');
1207: hr_utility.set_location('calculate_period_earnings', 40);
1208:
1209: SELECT TPT.number_per_fiscal_year
1210: INTO v_pay_periods_per_year
1211: FROM pay_payrolls_f PRL,
1215: and PRL.effective_end_date
1216: AND PRL.payroll_id = p_payroll_id
1217: AND PRL.business_group_id + 0 = p_bus_grp_id;
1218:
1219: hr_utility.trace('v_pay_periods_per_year ='||to_char(v_pay_periods_per_year));
1220:
1221: exception when NO_DATA_FOUND then
1222:
1223: hr_utility.set_location('calculate_period_earnings', 41);
1219: hr_utility.trace('v_pay_periods_per_year ='||to_char(v_pay_periods_per_year));
1220:
1221: exception when NO_DATA_FOUND then
1222:
1223: hr_utility.set_location('calculate_period_earnings', 41);
1224: hr_utility.trace('Exception Find # of payroll period');
1225: v_period_earn := 0;
1226: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1227: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1220:
1221: exception when NO_DATA_FOUND then
1222:
1223: hr_utility.set_location('calculate_period_earnings', 41);
1224: hr_utility.trace('Exception Find # of payroll period');
1225: v_period_earn := 0;
1226: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1227: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1228:
1223: hr_utility.set_location('calculate_period_earnings', 41);
1224: hr_utility.trace('Exception Find # of payroll period');
1225: v_period_earn := 0;
1226: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1227: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1228:
1229: RETURN v_period_earn;
1230:
1231: end;
1244: -- scheduled hours, not
1245: -- actual hours for a week, converted to a period figure.
1246: */
1247: --
1248: hr_utility.set_location('calculate_period_earnings', 45);
1249: hr_utility.trace('Get hours scheduled for the current period');
1250:
1251: IF p_work_schedule <> 'NOT ENTERED' THEN
1252: --
1245: -- actual hours for a week, converted to a period figure.
1246: */
1247: --
1248: hr_utility.set_location('calculate_period_earnings', 45);
1249: hr_utility.trace('Get hours scheduled for the current period');
1250:
1251: IF p_work_schedule <> 'NOT ENTERED' THEN
1252: --
1253: -- Find hours worked between period start and end dates.
1251: IF p_work_schedule <> 'NOT ENTERED' THEN
1252: --
1253: -- Find hours worked between period start and end dates.
1254: --
1255: hr_utility.trace('Asg has Work Schedule');
1256: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1257:
1258: v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
1259: hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
1252: --
1253: -- Find hours worked between period start and end dates.
1254: --
1255: hr_utility.trace('Asg has Work Schedule');
1256: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1257:
1258: v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
1259: hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
1260: --
1255: hr_utility.trace('Asg has Work Schedule');
1256: hr_utility.trace('p_work_schedule ='||p_work_schedule);
1257:
1258: v_ws_id := fnd_number.canonical_to_number(p_work_schedule);
1259: hr_utility.trace('v_ws_id ='||to_char(v_ws_id));
1260: --
1261: SELECT user_column_name
1262: INTO v_work_sched_name
1263: FROM pay_user_columns
1264: WHERE user_column_id = v_ws_id
1265: AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
1266: AND NVL(legislation_code,'US') = 'US';
1267:
1268: hr_utility.trace('v_work_sched_name ='||v_work_sched_name);
1269: hr_utility.trace('Calling Work_Schedule_Total_Hours');
1270:
1271: v_hrs_per_range := Work_Schedule_Total_Hours( p_bus_grp_id,
1272: v_work_sched_name,
1265: AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
1266: AND NVL(legislation_code,'US') = 'US';
1267:
1268: hr_utility.trace('v_work_sched_name ='||v_work_sched_name);
1269: hr_utility.trace('Calling Work_Schedule_Total_Hours');
1270:
1271: v_hrs_per_range := Work_Schedule_Total_Hours( p_bus_grp_id,
1272: v_work_sched_name,
1273: p_period_start,
1271: v_hrs_per_range := Work_Schedule_Total_Hours( p_bus_grp_id,
1272: v_work_sched_name,
1273: p_period_start,
1274: p_period_end);
1275: hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1276: ELSE
1277:
1278: hr_utility.trace('Asg has No Work Schedule');
1279: hr_utility.trace('Calling Standard_Hours_Worked');
1274: p_period_end);
1275: hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1276: ELSE
1277:
1278: hr_utility.trace('Asg has No Work Schedule');
1279: hr_utility.trace('Calling Standard_Hours_Worked');
1280:
1281: v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
1282: p_period_start,
1275: hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1276: ELSE
1277:
1278: hr_utility.trace('Asg has No Work Schedule');
1279: hr_utility.trace('Calling Standard_Hours_Worked');
1280:
1281: v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
1282: p_period_start,
1283: p_period_end,
1281: v_hrs_per_range := Standard_Hours_Worked( p_asst_std_hrs,
1282: p_period_start,
1283: p_period_end,
1284: p_asst_std_freq);
1285: hr_utility.trace('v_hrs_per_range ='||to_char(v_hrs_per_range));
1286:
1287: END IF;
1288:
1289:
1286:
1287: END IF;
1288:
1289:
1290: hr_utility.trace('Compute earnings and actual hours');
1291: hr_utility.trace('calling convert_period_type from calculate_period_earnings');
1292: hr_utility.set_location('calculate_period_earnings', 46);
1293:
1294: v_period_earn := Convert_Period_Type( p_bus_grp_id,
1287: END IF;
1288:
1289:
1290: hr_utility.trace('Compute earnings and actual hours');
1291: hr_utility.trace('calling convert_period_type from calculate_period_earnings');
1292: hr_utility.set_location('calculate_period_earnings', 46);
1293:
1294: v_period_earn := Convert_Period_Type( p_bus_grp_id,
1295: p_payroll_id,
1288:
1289:
1290: hr_utility.trace('Compute earnings and actual hours');
1291: hr_utility.trace('calling convert_period_type from calculate_period_earnings');
1292: hr_utility.set_location('calculate_period_earnings', 46);
1293:
1294: v_period_earn := Convert_Period_Type( p_bus_grp_id,
1295: p_payroll_id,
1296: p_work_schedule,
1301: p_period_start,
1302: p_period_end,
1303: p_asst_std_freq);
1304:
1305: hr_utility.trace('v_period_earn ='||to_char(v_period_earn));
1306: hr_utility.set_location('calculate_period_earnings', 47);
1307:
1308: p_actual_hours_worked := v_hrs_per_range;
1309:
1302: p_period_end,
1303: p_asst_std_freq);
1304:
1305: hr_utility.trace('v_period_earn ='||to_char(v_period_earn));
1306: hr_utility.set_location('calculate_period_earnings', 47);
1307:
1308: p_actual_hours_worked := v_hrs_per_range;
1309:
1310: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1306: hr_utility.set_location('calculate_period_earnings', 47);
1307:
1308: p_actual_hours_worked := v_hrs_per_range;
1309:
1310: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1311:
1312: IF p_prorate = 'N' THEN
1313:
1314: hr_utility.trace('No proration');
1310: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1311:
1312: IF p_prorate = 'N' THEN
1313:
1314: hr_utility.trace('No proration');
1315: hr_utility.trace('Calling p_vac_pay');
1316: hr_utility.set_location('calculate_period_earnings', 49);
1317:
1318: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1311:
1312: IF p_prorate = 'N' THEN
1313:
1314: hr_utility.trace('No proration');
1315: hr_utility.trace('Calling p_vac_pay');
1316: hr_utility.set_location('calculate_period_earnings', 49);
1317:
1318: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1319: p_asg_id => p_asst_id,
1312: IF p_prorate = 'N' THEN
1313:
1314: hr_utility.trace('No proration');
1315: hr_utility.trace('Calling p_vac_pay');
1316: hr_utility.set_location('calculate_period_earnings', 49);
1317:
1318: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1319: p_asg_id => p_asst_id,
1320: p_eff_date => p_period_end,
1319: p_asg_id => p_asst_id,
1320: p_eff_date => p_period_end,
1321: p_curr_rate => p_ass_hrly_figure);
1322:
1323: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1324:
1325: hr_utility.trace('Calling sick Pay');
1326: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1327: p_asg_id => p_asst_id,
1321: p_curr_rate => p_ass_hrly_figure);
1322:
1323: hr_utility.trace('p_vac_pay ='||to_char(p_vac_pay));
1324:
1325: hr_utility.trace('Calling sick Pay');
1326: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1327: p_asg_id => p_asst_id,
1328: p_eff_date => p_period_end,
1329: p_curr_rate => p_ass_hrly_figure);
1328: p_eff_date => p_period_end,
1329: p_curr_rate => p_ass_hrly_figure);
1330:
1331:
1332: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1333:
1334: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1335:
1336: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1332: hr_utility.trace('p_sick_pay ='||to_char(p_sick_pay));
1333:
1334: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1335:
1336: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1337: hr_utility.trace('UDFS v_period_earn ='||to_char(v_period_earn));
1338:
1339: RETURN v_period_earn;
1340:
1333:
1334: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1335:
1336: hr_utility.trace('p_actual_hours_worked ='||to_char(p_actual_hours_worked));
1337: hr_utility.trace('UDFS v_period_earn ='||to_char(v_period_earn));
1338:
1339: RETURN v_period_earn;
1340:
1341: END IF; /* IF p_prorate = 'N' */
1340:
1341: END IF; /* IF p_prorate = 'N' */
1342:
1343:
1344: hr_utility.trace('UDFS check for ASGMPE changes');
1345: hr_utility.set_location('calculate_period_earnings', 51);
1346: /* ************************************************************** */
1347:
1348: BEGIN /* Check ASGMPE */
1341: END IF; /* IF p_prorate = 'N' */
1342:
1343:
1344: hr_utility.trace('UDFS check for ASGMPE changes');
1345: hr_utility.set_location('calculate_period_earnings', 51);
1346: /* ************************************************************** */
1347:
1348: BEGIN /* Check ASGMPE */
1349:
1363: AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
1364: AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1365: AND SCL.enabled_flag = 'Y' );
1366:
1367: hr_utility.trace('ASGMPE Changes found');
1368: hr_utility.trace('Need to prorate b/c of ASGMPE');
1369: hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
1370:
1371: l_mid_period_asg_change := TRUE;
1364: AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
1365: AND SCL.enabled_flag = 'Y' );
1366:
1367: hr_utility.trace('ASGMPE Changes found');
1368: hr_utility.trace('Need to prorate b/c of ASGMPE');
1369: hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
1370:
1371: l_mid_period_asg_change := TRUE;
1372:
1365: AND SCL.enabled_flag = 'Y' );
1366:
1367: hr_utility.trace('ASGMPE Changes found');
1368: hr_utility.trace('Need to prorate b/c of ASGMPE');
1369: hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
1370:
1371: l_mid_period_asg_change := TRUE;
1372:
1373: hr_utility.set_location('calculate_period_earnings', 56);
1369: hr_utility.trace('Set l_mid_period_asg_change to TRUE I');
1370:
1371: l_mid_period_asg_change := TRUE;
1372:
1373: hr_utility.set_location('calculate_period_earnings', 56);
1374: hr_utility.trace('Look for EEVMPE changes');
1375:
1376: BEGIN /* EEVMPE check - maybe pick*/
1377:
1370:
1371: l_mid_period_asg_change := TRUE;
1372:
1373: hr_utility.set_location('calculate_period_earnings', 56);
1374: hr_utility.trace('Look for EEVMPE changes');
1375:
1376: BEGIN /* EEVMPE check - maybe pick*/
1377:
1378: select 1 INTO l_eev_info_changes
1389: ) );
1390:
1391:
1392:
1393: hr_utility.trace('EEVMPE changes found after ASGMPE');
1394:
1395: EXCEPTION
1396:
1397: WHEN NO_DATA_FOUND THEN
1396:
1397: WHEN NO_DATA_FOUND THEN
1398: l_eev_info_changes := 0;
1399:
1400: hr_utility.trace('From EXCEPTION ASGMPE changes found No EEVMPE changes');
1401:
1402: END; /* EEV1 check*/
1403:
1404: EXCEPTION
1405:
1406: WHEN NO_DATA_FOUND THEN
1407:
1408: l_asg_info_changes := 0;
1409: hr_utility.trace('From EXCEPTION No ASGMPE changes, nor EEVMPE changes');
1410:
1411: END; /* ASGMPE check*/
1412:
1413: /* ************************************************ */
1413: /* ************************************************ */
1414:
1415: IF l_asg_info_changes = 0 THEN /* Check ASGMPS */
1416:
1417: hr_utility.trace(' Into l_asg_info_changes = 0');
1418: hr_utility.trace('UDFS looking for ASGMPS changes');
1419: hr_utility.set_location('calculate_period_earnings', 56);
1420:
1421: BEGIN /* ASGMPS changes */
1414:
1415: IF l_asg_info_changes = 0 THEN /* Check ASGMPS */
1416:
1417: hr_utility.trace(' Into l_asg_info_changes = 0');
1418: hr_utility.trace('UDFS looking for ASGMPS changes');
1419: hr_utility.set_location('calculate_period_earnings', 56);
1420:
1421: BEGIN /* ASGMPS changes */
1422:
1415: IF l_asg_info_changes = 0 THEN /* Check ASGMPS */
1416:
1417: hr_utility.trace(' Into l_asg_info_changes = 0');
1418: hr_utility.trace('UDFS looking for ASGMPS changes');
1419: hr_utility.set_location('calculate_period_earnings', 56);
1420:
1421: BEGIN /* ASGMPS changes */
1422:
1423: select 1 INTO l_asg_info_changes
1437: AND SCL.enabled_flag = 'Y');
1438:
1439: l_mid_period_asg_change := TRUE;
1440:
1441: hr_utility.trace('Need to prorate for ASGMPS changes');
1442: hr_utility.set_location('calculate_period_earnings', 57);
1443:
1444: BEGIN /* EEVMPE changes ASGMPS */
1445:
1438:
1439: l_mid_period_asg_change := TRUE;
1440:
1441: hr_utility.trace('Need to prorate for ASGMPS changes');
1442: hr_utility.set_location('calculate_period_earnings', 57);
1443:
1444: BEGIN /* EEVMPE changes ASGMPS */
1445:
1446: select 1 INTO l_eev_info_changes
1456: --OR ( EEV.effective_start_date between p_period_start and p_period_end)
1457: ) );
1458:
1459:
1460: hr_utility.trace('Need to prorate EEVMPS changes after ASGMPS ');
1461:
1462: EXCEPTION
1463:
1464: WHEN NO_DATA_FOUND THEN
1464: WHEN NO_DATA_FOUND THEN
1465:
1466: l_eev_info_changes := 0;
1467:
1468: hr_utility.trace('From EXCEPTIION No EEVMPE changes');
1469:
1470: END; /* EEVMPE changes */
1471:
1472: EXCEPTION
1474: WHEN NO_DATA_FOUND THEN
1475:
1476: l_asg_info_changes := 0;
1477:
1478: hr_utility.trace('From EXCEPTION no changes due to ASGMPS or EEVMPE');
1479:
1480: END; /* ASGMPS changes */
1481:
1482: END IF; /* Check ASGMPS */
1486: IF l_asg_info_changes = 0 THEN /* ASGMPE=0 and ASGMPS=0 */
1487:
1488: BEGIN /* Check for EEVMPE changes */
1489:
1490: hr_utility.set_location('calculate_period_earnings', 58);
1491: hr_utility.trace('Check for EEVMPE changes nevertheless');
1492:
1493: select 1 INTO l_eev_info_changes
1494: from dual
1487:
1488: BEGIN /* Check for EEVMPE changes */
1489:
1490: hr_utility.set_location('calculate_period_earnings', 58);
1491: hr_utility.trace('Check for EEVMPE changes nevertheless');
1492:
1493: select 1 INTO l_eev_info_changes
1494: from dual
1495: where exists (
1500: AND EEV.effective_start_date <= p_period_start
1501: AND EEV.effective_end_date >= p_period_start
1502: AND EEV.effective_end_date < p_period_end);
1503:
1504: hr_utility.trace('Proration due to EEVMPE changes');
1505:
1506:
1507: EXCEPTION
1508:
1507: EXCEPTION
1508:
1509: WHEN NO_DATA_FOUND THEN
1510:
1511: hr_utility.trace('ASG AND EEV changes DO NOT EXIST EXCEPT ');
1512:
1513: -- Either there are no changes to an Active Assignment OR
1514: -- the assignment was not active at all this period.
1515: -- Check assignment status of current asg record.
1513: -- Either there are no changes to an Active Assignment OR
1514: -- the assignment was not active at all this period.
1515: -- Check assignment status of current asg record.
1516:
1517: hr_utility.trace(' Check assignment status of current asg record');
1518:
1519: SELECT AST.per_system_status
1520: INTO v_asg_status
1521: FROM per_assignments_f ASG,
1530: AND SCL.enabled_flag = 'Y';
1531:
1532: IF v_asg_status <> 'ACTIVE_ASSIGN' THEN
1533:
1534: hr_utility.trace(' Asg not active');
1535: v_period_earn := 0;
1536: p_actual_hours_worked := 0;
1537:
1538: END IF;
1536: p_actual_hours_worked := 0;
1537:
1538: END IF;
1539:
1540: hr_utility.trace('Chk for vac pay since no ASG EEV changes to prorate' );
1541:
1542: p_vac_pay := vacation_pay(p_vac_hours => p_vac_hours_worked,
1543: p_asg_id => p_asst_id,
1544: p_eff_date => p_period_end,
1543: p_asg_id => p_asst_id,
1544: p_eff_date => p_period_end,
1545: p_curr_rate => p_ass_hrly_figure);
1546:
1547: hr_utility.trace('p_vac_pay ='||p_vac_pay);
1548: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1549: p_asg_id => p_asst_id,
1550: p_eff_date => p_period_end,
1551: p_curr_rate => p_ass_hrly_figure);
1550: p_eff_date => p_period_end,
1551: p_curr_rate => p_ass_hrly_figure);
1552:
1553:
1554: hr_utility.trace('p_sick_pay ='||p_sick_pay);
1555:
1556: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1557: RETURN v_period_earn;
1558:
1570:
1571: /* ************** ONLY ASG CHANGES START **** */
1572:
1573: p_actual_hours_worked := 0;
1574: hr_utility.set_location('calculate_period_earnings', 70);
1575: hr_utility.trace('UDFS ONLY ASG CHANGES START');
1576:
1577: BEGIN /* Get Asg Details ASGMPE */
1578:
1571: /* ************** ONLY ASG CHANGES START **** */
1572:
1573: p_actual_hours_worked := 0;
1574: hr_utility.set_location('calculate_period_earnings', 70);
1575: hr_utility.trace('UDFS ONLY ASG CHANGES START');
1576:
1577: BEGIN /* Get Asg Details ASGMPE */
1578:
1579: hr_utility.trace('Get Asg details - ASGMPE');
1575: hr_utility.trace('UDFS ONLY ASG CHANGES START');
1576:
1577: BEGIN /* Get Asg Details ASGMPE */
1578:
1579: hr_utility.trace('Get Asg details - ASGMPE');
1580: hr_utility.set_location('calculate_period_earnings', 71);
1581:
1582: SELECT GREATEST(ASG.effective_start_date, p_period_start),
1583: ASG.effective_end_date,
1576:
1577: BEGIN /* Get Asg Details ASGMPE */
1578:
1579: hr_utility.trace('Get Asg details - ASGMPE');
1580: hr_utility.set_location('calculate_period_earnings', 71);
1581:
1582: SELECT GREATEST(ASG.effective_start_date, p_period_start),
1583: ASG.effective_end_date,
1584: NVL(ASG.NORMAL_HOURS, 0),
1606: AND HRL.lookup_code(+) = ASG.frequency
1607: AND HRL.lookup_type(+) = 'FREQUENCY';
1608:
1609:
1610: hr_utility.trace('If ASGMPE Details succ. then Calling Prorate_Earnings');
1611: hr_utility.set_location('calculate_period_earnings', 72);
1612: v_prorated_earnings := v_prorated_earnings +
1613: Prorate_Earnings (
1614: p_bg_id => p_bus_grp_id,
1607: AND HRL.lookup_type(+) = 'FREQUENCY';
1608:
1609:
1610: hr_utility.trace('If ASGMPE Details succ. then Calling Prorate_Earnings');
1611: hr_utility.set_location('calculate_period_earnings', 72);
1612: v_prorated_earnings := v_prorated_earnings +
1613: Prorate_Earnings (
1614: p_bg_id => p_bus_grp_id,
1615: p_asg_hrly_rate => p_ass_hrly_figure,
1619: p_range_start_date => v_range_start,
1620: p_range_end_date => v_range_end,
1621: p_act_hrs_worked => p_actual_hours_worked);
1622:
1623: hr_utility.trace('After Calling Prorate_Earnings');
1624:
1625: EXCEPTION WHEN NO_DATA_FOUND THEN
1626:
1627: NULL;
1628:
1629: END; /* Get Asg Details */
1630:
1631:
1632: hr_utility.trace('ONLY ASG , select MULTIASG');
1633: hr_utility.set_location('calculate_period_earnings', 77);
1634:
1635: OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
1636: LOOP
1629: END; /* Get Asg Details */
1630:
1631:
1632: hr_utility.trace('ONLY ASG , select MULTIASG');
1633: hr_utility.set_location('calculate_period_earnings', 77);
1634:
1635: OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
1636: LOOP
1637:
1641: v_asst_std_hrs,
1642: v_asst_std_freq,
1643: v_work_schedule;
1644: EXIT WHEN get_asst_chgs%NOTFOUND;
1645: hr_utility.set_location('calculate_period_earnings', 79);
1646:
1647:
1648: hr_utility.trace('ONLY ASG Calling Prorate_Earning as MULTIASG successful');
1649:
1644: EXIT WHEN get_asst_chgs%NOTFOUND;
1645: hr_utility.set_location('calculate_period_earnings', 79);
1646:
1647:
1648: hr_utility.trace('ONLY ASG Calling Prorate_Earning as MULTIASG successful');
1649:
1650: v_prorated_earnings := v_prorated_earnings +
1651: Prorate_Earnings (
1652: p_bg_id => p_bus_grp_id,
1658: p_range_end_date => v_range_end,
1659: p_act_hrs_worked => p_actual_hours_worked);
1660:
1661:
1662: hr_utility.trace('After calling Prorate_Earnings from MULTIASG');
1663:
1664: END LOOP;
1665:
1666: CLOSE get_asst_chgs;
1666: CLOSE get_asst_chgs;
1667:
1668: BEGIN /* END_SPAN_RECORD */
1669:
1670: hr_utility.set_location('calculate_period_earnings', 89);
1671: hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
1672:
1673: SELECT ASG.effective_start_date,
1674: LEAST(ASG.effective_end_date, p_period_end),
1667:
1668: BEGIN /* END_SPAN_RECORD */
1669:
1670: hr_utility.set_location('calculate_period_earnings', 89);
1671: hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
1672:
1673: SELECT ASG.effective_start_date,
1674: LEAST(ASG.effective_end_date, p_period_end),
1675: NVL(ASG.normal_hours, 0),
1696: AND SCL.enabled_flag = 'Y'
1697: AND HRL.lookup_code(+) = ASG.frequency
1698: AND HRL.lookup_type(+) = 'FREQUENCY';
1699:
1700: hr_utility.trace('Calling Prorate_Earnings for ONLY ASG END_SPAN_RECORD');
1701: hr_utility.set_location('calculate_period_earnings', 91);
1702: v_prorated_earnings := v_prorated_earnings +
1703: Prorate_Earnings (
1704: p_bg_id => p_bus_grp_id,
1697: AND HRL.lookup_code(+) = ASG.frequency
1698: AND HRL.lookup_type(+) = 'FREQUENCY';
1699:
1700: hr_utility.trace('Calling Prorate_Earnings for ONLY ASG END_SPAN_RECORD');
1701: hr_utility.set_location('calculate_period_earnings', 91);
1702: v_prorated_earnings := v_prorated_earnings +
1703: Prorate_Earnings (
1704: p_bg_id => p_bus_grp_id,
1705: p_asg_hrly_rate => p_ass_hrly_figure,
1710: p_range_end_date => v_range_end,
1711: p_act_hrs_worked => p_actual_hours_worked);
1712:
1713:
1714: hr_utility.trace('Calling Vacation Pay as END_SPAN succ');
1715: hr_utility.set_location('calculate_period_earnings', 101);
1716:
1717: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1718: p_asg_id => p_asst_id,
1711: p_act_hrs_worked => p_actual_hours_worked);
1712:
1713:
1714: hr_utility.trace('Calling Vacation Pay as END_SPAN succ');
1715: hr_utility.set_location('calculate_period_earnings', 101);
1716:
1717: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1718: p_asg_id => p_asst_id,
1719: p_eff_date => p_period_end,
1718: p_asg_id => p_asst_id,
1719: p_eff_date => p_period_end,
1720: p_curr_rate => p_ass_hrly_figure);
1721:
1722: hr_utility.trace('Calling Sick Pay as ASG3 succ');
1723:
1724: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1725: p_asg_id => p_asst_id,
1726: p_eff_date => p_period_end,
1730: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1731: RETURN v_prorated_earnings;
1732:
1733: EXCEPTION WHEN NO_DATA_FOUND THEN
1734: hr_utility.set_location('calculate_period_earnings', 102);
1735: hr_utility.trace('Exception of ASG_MID_START_LAST_SPAN_END_DT');
1736:
1737: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1738: p_asg_id => p_asst_id,
1731: RETURN v_prorated_earnings;
1732:
1733: EXCEPTION WHEN NO_DATA_FOUND THEN
1734: hr_utility.set_location('calculate_period_earnings', 102);
1735: hr_utility.trace('Exception of ASG_MID_START_LAST_SPAN_END_DT');
1736:
1737: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1738: p_asg_id => p_asst_id,
1739: p_eff_date => p_period_end,
1738: p_asg_id => p_asst_id,
1739: p_eff_date => p_period_end,
1740: p_curr_rate => p_ass_hrly_figure);
1741:
1742: hr_utility.trace('Calling Sick Pay as ASG3 not succ');
1743: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1744: p_asg_id => p_asst_id,
1745: p_eff_date => p_period_end,
1746: p_curr_rate => p_ass_hrly_figure);
1757: ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
1758:
1759: /* ******************* ONLY EEV CHANGES START ****** */
1760:
1761: hr_utility.trace(' Only EEV changes exist');
1762: hr_utility.set_location('calculate_period_earnings', 103);
1763: p_actual_hours_worked := 0;
1764:
1765:
1758:
1759: /* ******************* ONLY EEV CHANGES START ****** */
1760:
1761: hr_utility.trace(' Only EEV changes exist');
1762: hr_utility.set_location('calculate_period_earnings', 103);
1763: p_actual_hours_worked := 0;
1764:
1765:
1766: hr_utility.trace('Calling Prorate_EEV');
1762: hr_utility.set_location('calculate_period_earnings', 103);
1763: p_actual_hours_worked := 0;
1764:
1765:
1766: hr_utility.trace('Calling Prorate_EEV');
1767:
1768: v_prorated_earnings := v_prorated_earnings +
1769: Prorate_EEV (
1770: p_bus_group_id => p_bus_grp_id,
1779: p_actual_hrs_worked => p_actual_hours_worked,
1780: p_element_entry_id => p_ele_entry_id,
1781: p_inpval_id => v_inpval_id);
1782:
1783: hr_utility.trace('After Calling Prorate_EEV');
1784: hr_utility.set_location('calculate_period_earnings', 127);
1785:
1786: hr_utility.trace('Calling vacation_pay');
1787:
1780: p_element_entry_id => p_ele_entry_id,
1781: p_inpval_id => v_inpval_id);
1782:
1783: hr_utility.trace('After Calling Prorate_EEV');
1784: hr_utility.set_location('calculate_period_earnings', 127);
1785:
1786: hr_utility.trace('Calling vacation_pay');
1787:
1788: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1782:
1783: hr_utility.trace('After Calling Prorate_EEV');
1784: hr_utility.set_location('calculate_period_earnings', 127);
1785:
1786: hr_utility.trace('Calling vacation_pay');
1787:
1788: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
1789: p_asg_id => p_asst_id,
1790: p_eff_date => p_period_end,
1789: p_asg_id => p_asst_id,
1790: p_eff_date => p_period_end,
1791: p_curr_rate => p_ass_hrly_figure);
1792:
1793: hr_utility.trace('Calling sick_pay');
1794:
1795: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
1796: p_asg_id => p_asst_id,
1797: p_eff_date => p_period_end,
1807:
1808: /* ******************* BOTH ASG AND EEV CHANGES START ************ */
1809:
1810:
1811: hr_utility.trace('UDFS BOTH ASG and EEV chages exist');
1812:
1813:
1814: p_actual_hours_worked := 0;
1815:
1815:
1816:
1817: BEGIN /* Latest Screen Entry Value */
1818:
1819: hr_utility.trace('BOTH ASG Get latest screen entry value for EEVMPE');
1820: hr_utility.set_location('calculate_period_earnings', 128);
1821:
1822: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1823: INTO v_earnings_entry
1816:
1817: BEGIN /* Latest Screen Entry Value */
1818:
1819: hr_utility.trace('BOTH ASG Get latest screen entry value for EEVMPE');
1820: hr_utility.set_location('calculate_period_earnings', 128);
1821:
1822: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1823: INTO v_earnings_entry
1824: FROM pay_element_entry_values_f EEV
1831: AND EEV.effective_end_date > p_period_start;
1832: */
1833: --AND EEV.effective_end_date < p_period_end
1834:
1835: hr_utility.trace('BOTH ASG Get ASGMPE ');
1836:
1837: SELECT GREATEST(ASG.effective_start_date, p_period_start),
1838: ASG.effective_end_date,
1839: NVL(ASG.NORMAL_HOURS, 0),
1860: AND SCL.enabled_flag = 'Y'
1861: AND HRL.lookup_code(+) = ASG.frequency
1862: AND HRL.lookup_type(+) = 'FREQUENCY';
1863:
1864: hr_utility.trace('Calling Convert_Period_Type from ASGMPE');
1865: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1866:
1867: v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
1868: p_payroll_id,
1861: AND HRL.lookup_code(+) = ASG.frequency
1862: AND HRL.lookup_type(+) = 'FREQUENCY';
1863:
1864: hr_utility.trace('Calling Convert_Period_Type from ASGMPE');
1865: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1866:
1867: v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
1868: p_payroll_id,
1869: v_work_schedule,
1874: p_period_start,
1875: p_period_end,
1876: v_asst_std_freq);
1877:
1878: hr_utility.trace('Select app. EEVMPE again after range is determined');
1879: hr_utility.set_location('calculate_period_earnings', 130);
1880:
1881: SELECT COUNT(EEV.element_entry_value_id)
1882: INTO l_eev_info_changes
1875: p_period_end,
1876: v_asst_std_freq);
1877:
1878: hr_utility.trace('Select app. EEVMPE again after range is determined');
1879: hr_utility.set_location('calculate_period_earnings', 130);
1880:
1881: SELECT COUNT(EEV.element_entry_value_id)
1882: INTO l_eev_info_changes
1883: FROM pay_element_entry_values_f EEV
1889:
1890: IF l_eev_info_changes = 0 THEN
1891:
1892:
1893: hr_utility.trace('NO EEVMPE changes');
1894: hr_utility.set_location('calculate_period_earnings', 132);
1895:
1896: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1897: INTO v_earnings_entry
1890: IF l_eev_info_changes = 0 THEN
1891:
1892:
1893: hr_utility.trace('NO EEVMPE changes');
1894: hr_utility.set_location('calculate_period_earnings', 132);
1895:
1896: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1897: INTO v_earnings_entry
1898: FROM pay_element_entry_values_f EEV
1900: AND EEV.input_value_id = v_inpval_id
1901: AND v_range_end BETWEEN EEV.effective_start_date
1902: AND EEV.effective_end_date;
1903:
1904: hr_utility.trace('Calling Convert_Period_Type');
1905: hr_utility.set_location('calculate_period_earnings', 134);
1906:
1907: v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
1908: p_payroll_id,
1901: AND v_range_end BETWEEN EEV.effective_start_date
1902: AND EEV.effective_end_date;
1903:
1904: hr_utility.trace('Calling Convert_Period_Type');
1905: hr_utility.set_location('calculate_period_earnings', 134);
1906:
1907: v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
1908: p_payroll_id,
1909: v_work_schedule,
1914: p_period_start,
1915: p_period_end,
1916: v_asst_std_freq);
1917:
1918: hr_utility.trace('Calling Prorate_Earnings');
1919: hr_utility.set_location('calculate_period_earnings', 135);
1920:
1921: v_prorated_earnings := v_prorated_earnings +
1922: Prorate_Earnings (
1915: p_period_end,
1916: v_asst_std_freq);
1917:
1918: hr_utility.trace('Calling Prorate_Earnings');
1919: hr_utility.set_location('calculate_period_earnings', 135);
1920:
1921: v_prorated_earnings := v_prorated_earnings +
1922: Prorate_Earnings (
1923: p_bg_id => p_bus_grp_id,
1928: p_range_start_date => v_range_start,
1929: p_range_end_date => v_range_end,
1930: p_act_hrs_worked => p_actual_hours_worked);
1931:
1932: hr_utility.set_location('calculate_period_earnings', 137);
1933:
1934: ELSE
1935: -- Do proration for this ASG range by EEV !
1936:
1933:
1934: ELSE
1935: -- Do proration for this ASG range by EEV !
1936:
1937: hr_utility.trace('EEVMPE True');
1938: hr_utility.trace('Do proration for this ASG range by EEV');
1939: hr_utility.set_location('calculate_period_earnings', 139);
1940:
1941: hr_utility.trace('Calling Prorate_EEV');
1934: ELSE
1935: -- Do proration for this ASG range by EEV !
1936:
1937: hr_utility.trace('EEVMPE True');
1938: hr_utility.trace('Do proration for this ASG range by EEV');
1939: hr_utility.set_location('calculate_period_earnings', 139);
1940:
1941: hr_utility.trace('Calling Prorate_EEV');
1942:
1935: -- Do proration for this ASG range by EEV !
1936:
1937: hr_utility.trace('EEVMPE True');
1938: hr_utility.trace('Do proration for this ASG range by EEV');
1939: hr_utility.set_location('calculate_period_earnings', 139);
1940:
1941: hr_utility.trace('Calling Prorate_EEV');
1942:
1943: v_prorated_earnings := v_prorated_earnings +
1937: hr_utility.trace('EEVMPE True');
1938: hr_utility.trace('Do proration for this ASG range by EEV');
1939: hr_utility.set_location('calculate_period_earnings', 139);
1940:
1941: hr_utility.trace('Calling Prorate_EEV');
1942:
1943: v_prorated_earnings := v_prorated_earnings +
1944: Prorate_EEV (
1945: p_bus_group_id => p_bus_grp_id,
1953: p_range_end_date => v_range_end,
1954: p_actual_hrs_worked => p_actual_hours_worked,
1955: p_element_entry_id => p_ele_entry_id,
1956: p_inpval_id => v_inpval_id);
1957: hr_utility.set_location('calculate_period_earnings', 140);
1958:
1959: END IF; -- EEV info changes
1960:
1961: EXCEPTION WHEN NO_DATA_FOUND THEN
1962: NULL;
1963:
1964: END; /* Latest Screen Entry Value */
1965:
1966: hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
1967: hr_utility.set_location('calculate_period_earnings', 141);
1968:
1969: OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
1970: LOOP
1963:
1964: END; /* Latest Screen Entry Value */
1965:
1966: hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
1967: hr_utility.set_location('calculate_period_earnings', 141);
1968:
1969: OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
1970: LOOP
1971:
1977: v_work_schedule;
1978: EXIT WHEN get_asst_chgs%NOTFOUND;
1979:
1980: --EEV_BEFORE_RANGE_END
1981: hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
1982: hr_utility.set_location('calculate_period_earnings', 145);
1983:
1984: SELECT COUNT(EEV.element_entry_value_id)
1985: INTO l_eev_info_changes
1978: EXIT WHEN get_asst_chgs%NOTFOUND;
1979:
1980: --EEV_BEFORE_RANGE_END
1981: hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
1982: hr_utility.set_location('calculate_period_earnings', 145);
1983:
1984: SELECT COUNT(EEV.element_entry_value_id)
1985: INTO l_eev_info_changes
1986: FROM pay_element_entry_values_f EEV
1993: IF l_eev_info_changes = 0 THEN /* IF l_eev_info_changes = 0 */
1994:
1995: -- EEV_FOR_CURR_RANGE_END
1996:
1997: hr_utility.trace('BOTH ASG - EEV false');
1998: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
1999: INTO v_earnings_entry
2000: FROM pay_element_entry_values_f EEV
2001: WHERE EEV.element_entry_id = p_ele_entry_id
2001: WHERE EEV.element_entry_id = p_ele_entry_id
2002: AND EEV.input_value_id = v_inpval_id
2003: AND v_range_end BETWEEN EEV.effective_start_date
2004: AND EEV.effective_end_date;
2005: hr_utility.set_location('calculate_period_earnings', 150);
2006: v_curr_hrly_rate := Convert_Period_Type( p_bus_grp_id,
2007: p_payroll_id,
2008: v_work_schedule,
2009: v_asst_std_hrs,
2024: p_range_start_date => v_range_start,
2025: p_range_end_date => v_range_end,
2026: p_act_hrs_worked => p_actual_hours_worked);
2027:
2028: hr_utility.set_location('calculate_period_earnings', 155);
2029: ELSE
2030: hr_utility.trace('BOTH ASG - EEV true');
2031: v_prorated_earnings := v_prorated_earnings +
2032: Prorate_EEV (
2026: p_act_hrs_worked => p_actual_hours_worked);
2027:
2028: hr_utility.set_location('calculate_period_earnings', 155);
2029: ELSE
2030: hr_utility.trace('BOTH ASG - EEV true');
2031: v_prorated_earnings := v_prorated_earnings +
2032: Prorate_EEV (
2033: p_bus_group_id => p_bus_grp_id,
2034: p_pay_id => p_payroll_id,
2051:
2052:
2053: BEGIN /* SPAN_RECORD */
2054:
2055: hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
2056: hr_utility.set_location('calculate_period_earnings', 160);
2057:
2058: SELECT ASG.effective_start_date,
2059: LEAST(ASG.effective_end_date, p_period_end),
2052:
2053: BEGIN /* SPAN_RECORD */
2054:
2055: hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
2056: hr_utility.set_location('calculate_period_earnings', 160);
2057:
2058: SELECT ASG.effective_start_date,
2059: LEAST(ASG.effective_end_date, p_period_end),
2060: NVL(ASG.normal_hours, 0),
2083: AND HRL.lookup_type(+) = 'FREQUENCY';
2084:
2085:
2086:
2087: hr_utility.trace('SELECT EEVMPE');
2088:
2089: SELECT COUNT(EEV.element_entry_value_id)
2090: INTO l_eev_info_changes
2091: FROM pay_element_entry_values_f EEV
2096: AND EEV.effective_end_date < v_range_end;
2097:
2098: IF l_eev_info_changes = 0 THEN
2099:
2100: hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
2101: hr_utility.set_location('calculate_period_earnings', 165);
2102:
2103: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2104: INTO v_earnings_entry
2097:
2098: IF l_eev_info_changes = 0 THEN
2099:
2100: hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
2101: hr_utility.set_location('calculate_period_earnings', 165);
2102:
2103: SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
2104: INTO v_earnings_entry
2105: FROM pay_element_entry_values_f EEV
2129: p_range_start_date => v_range_start,
2130: p_range_end_date => v_range_end,
2131: p_act_hrs_worked => p_actual_hours_worked);
2132:
2133: hr_utility.set_location('calculate_period_earnings', 170);
2134: ELSE /* EEV succ */
2135:
2136: hr_utility.trace('BOTH ASG END_SPAN - EEV true');
2137: v_prorated_earnings := v_prorated_earnings +
2132:
2133: hr_utility.set_location('calculate_period_earnings', 170);
2134: ELSE /* EEV succ */
2135:
2136: hr_utility.trace('BOTH ASG END_SPAN - EEV true');
2137: v_prorated_earnings := v_prorated_earnings +
2138: Prorate_EEV (
2139: p_bus_group_id => p_bus_grp_id,
2140: p_pay_id => p_payroll_id,
2147: p_range_end_date => v_range_end,
2148: p_actual_hrs_worked => p_actual_hours_worked,
2149: p_element_entry_id => p_ele_entry_id,
2150: p_inpval_id => v_inpval_id);
2151: hr_utility.set_location('calculate_period_earnings', 175);
2152: END IF;
2153:
2154:
2155: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2155: p_vac_pay := vacation_pay( p_vac_hours => p_vac_hours_worked,
2156: p_asg_id => p_asst_id,
2157: p_eff_date => p_period_end,
2158: p_curr_rate => p_ass_hrly_figure);
2159: hr_utility.set_location('calculate_period_earnings', 180);
2160:
2161: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2162: p_asg_id => p_asst_id,
2163: p_eff_date => p_period_end,
2161: p_sick_pay := sick_pay( p_sick_hours => p_sick_hours_worked,
2162: p_asg_id => p_asst_id,
2163: p_eff_date => p_period_end,
2164: p_curr_rate => p_ass_hrly_figure);
2165: hr_utility.set_location('calculate_period_earnings', 185);
2166:
2167: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
2168: RETURN v_prorated_earnings;
2169:
2239: v_curr_date := NULL;
2240: v_curr_day :=NULL;
2241:
2242: -- Check for valid range
2243: hr_utility.trace('Entered standard_hours_worked');
2244:
2245: IF p_range_start > p_range_end THEN
2246: hr_utility.trace('p_range_start greater than p_range_end');
2247: RETURN v_total_hours;
2242: -- Check for valid range
2243: hr_utility.trace('Entered standard_hours_worked');
2244:
2245: IF p_range_start > p_range_end THEN
2246: hr_utility.trace('p_range_start greater than p_range_end');
2247: RETURN v_total_hours;
2248: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2249: -- hr_utility.raise_error;
2250: END IF;
2244:
2245: IF p_range_start > p_range_end THEN
2246: hr_utility.trace('p_range_start greater than p_range_end');
2247: RETURN v_total_hours;
2248: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2249: -- hr_utility.raise_error;
2250: END IF;
2251: --
2252:
2245: IF p_range_start > p_range_end THEN
2246: hr_utility.trace('p_range_start greater than p_range_end');
2247: RETURN v_total_hours;
2248: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2249: -- hr_utility.raise_error;
2250: END IF;
2251: --
2252:
2253: IF UPPER(p_std_freq) = 'WEEK' THEN
2250: END IF;
2251: --
2252:
2253: IF UPPER(p_std_freq) = 'WEEK' THEN
2254: hr_utility.trace('p_std_freq = WEEK ');
2255:
2256: v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
2257:
2258: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2254: hr_utility.trace('p_std_freq = WEEK ');
2255:
2256: v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
2257:
2258: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2259: hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
2260: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2261:
2262: ELSIF UPPER(p_std_freq) = 'MONTH' THEN
2255:
2256: v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
2257:
2258: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2259: hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
2260: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2261:
2262: ELSIF UPPER(p_std_freq) = 'MONTH' THEN
2263:
2256: v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
2257:
2258: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2259: hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
2260: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2261:
2262: ELSIF UPPER(p_std_freq) = 'MONTH' THEN
2263:
2264: hr_utility.trace('p_std_freq = MONTH ');
2260: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2261:
2262: ELSIF UPPER(p_std_freq) = 'MONTH' THEN
2263:
2264: hr_utility.trace('p_std_freq = MONTH ');
2265:
2266: v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
2267:
2268:
2265:
2266: v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
2267:
2268:
2269: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2270: hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
2271: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2272:
2273: ELSIF UPPER(p_std_freq) = 'YEAR' THEN
2266: v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
2267:
2268:
2269: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2270: hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
2271: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2272:
2273: ELSIF UPPER(p_std_freq) = 'YEAR' THEN
2274:
2267:
2268:
2269: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2270: hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
2271: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2272:
2273: ELSIF UPPER(p_std_freq) = 'YEAR' THEN
2274:
2275: hr_utility.trace('p_std_freq = YEAR ');
2271: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2272:
2273: ELSIF UPPER(p_std_freq) = 'YEAR' THEN
2274:
2275: hr_utility.trace('p_std_freq = YEAR ');
2276: v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
2277:
2278: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2279: hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2274:
2275: hr_utility.trace('p_std_freq = YEAR ');
2276: v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
2277:
2278: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2279: hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2280: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2281:
2282: ELSE
2275: hr_utility.trace('p_std_freq = YEAR ');
2276: v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
2277:
2278: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2279: hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2280: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2281:
2282: ELSE
2283: hr_utility.trace('p_std_freq in ELSE ');
2276: v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
2277:
2278: hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
2279: hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2280: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2281:
2282: ELSE
2283: hr_utility.trace('p_std_freq in ELSE ');
2284: v_wrkday_hours := p_std_hrs;
2279: hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
2280: hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
2281:
2282: ELSE
2283: hr_utility.trace('p_std_freq in ELSE ');
2284: v_wrkday_hours := p_std_hrs;
2285: END IF;
2286:
2287: v_curr_date := p_range_start;
2285: END IF;
2286:
2287: v_curr_date := p_range_start;
2288:
2289: hr_utility.trace('v_curr_date is range start'||to_char(v_curr_date));
2290:
2291:
2292: LOOP
2293:
2298:
2299:
2300: v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
2301:
2302: hr_utility.trace(' v_day_no = '||to_char(v_day_no));
2303: hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
2304: END IF;
2305:
2306: v_curr_date := v_curr_date + 1;
2299:
2300: v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
2301:
2302: hr_utility.trace(' v_day_no = '||to_char(v_day_no));
2303: hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
2304: END IF;
2305:
2306: v_curr_date := v_curr_date + 1;
2307: EXIT WHEN v_curr_date > p_range_end;
2305:
2306: v_curr_date := v_curr_date + 1;
2307: EXIT WHEN v_curr_date > p_range_end;
2308: END LOOP;
2309: hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
2310: hr_utility.trace(' Leaving standard_hours_worked' );
2311: --
2312: RETURN v_total_hours;
2313: --
2306: v_curr_date := v_curr_date + 1;
2307: EXIT WHEN v_curr_date > p_range_end;
2308: END LOOP;
2309: hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
2310: hr_utility.trace(' Leaving standard_hours_worked' );
2311: --
2312: RETURN v_total_hours;
2313: --
2314: END standard_hours_worked;
2380: -- Selecting "count" because we want to continue processing even if
2381: -- the from_freq is not a pay basis.
2382: --
2383:
2384: hr_utility.trace(' Entered Get_Annualizing_Factor ');
2385:
2386: BEGIN -- Is Freq pay basis?
2387:
2388: --
2389: -- Decode pay basis and set v_annualizing_factor accordingly.
2390: -- PAY_BASIS "Meaning" is passed from FF !
2391: --
2392:
2393: hr_utility.trace(' Getting lookup code for lookup_type = PAY_BASIS');
2394: hr_utility.trace(' p_freq ='||p_freq);
2395:
2396: SELECT lookup_code
2397: INTO v_pay_basis
2390: -- PAY_BASIS "Meaning" is passed from FF !
2391: --
2392:
2393: hr_utility.trace(' Getting lookup code for lookup_type = PAY_BASIS');
2394: hr_utility.trace(' p_freq ='||p_freq);
2395:
2396: SELECT lookup_code
2397: INTO v_pay_basis
2398: FROM hr_lookups lkp
2399: WHERE lkp.application_id = 800
2400: AND lkp.lookup_type = 'PAY_BASIS'
2401: AND lkp.meaning = p_freq;
2402:
2403: hr_utility.trace(' Lookup_code ie v_pay_basis ='||v_pay_basis);
2404: v_use_pay_basis := 1;
2405:
2406: IF v_pay_basis = 'MONTHLY' THEN
2407:
2404: v_use_pay_basis := 1;
2405:
2406: IF v_pay_basis = 'MONTHLY' THEN
2407:
2408: hr_utility.trace(' Entered for MONTHLY v_pay_basis');
2409:
2410: v_annualizing_factor := 12;
2411:
2412: hr_utility.trace(' v_annualizing_factor = 12 ');
2408: hr_utility.trace(' Entered for MONTHLY v_pay_basis');
2409:
2410: v_annualizing_factor := 12;
2411:
2412: hr_utility.trace(' v_annualizing_factor = 12 ');
2413: ELSIF v_pay_basis = 'HOURLY' THEN
2414:
2415: hr_utility.trace(' Entered for HOURLY v_pay_basis');
2416:
2411:
2412: hr_utility.trace(' v_annualizing_factor = 12 ');
2413: ELSIF v_pay_basis = 'HOURLY' THEN
2414:
2415: hr_utility.trace(' Entered for HOURLY v_pay_basis');
2416:
2417: IF p_period_start_date IS NOT NULL THEN
2418:
2419: hr_utility.trace(' p_period_start_date IS NOT NULL v_period_hours=T');
2415: hr_utility.trace(' Entered for HOURLY v_pay_basis');
2416:
2417: IF p_period_start_date IS NOT NULL THEN
2418:
2419: hr_utility.trace(' p_period_start_date IS NOT NULL v_period_hours=T');
2420: v_range_start := p_period_start_date;
2421: v_range_end := p_period_end_date;
2422: v_period_hours := TRUE;
2423: ELSE
2421: v_range_end := p_period_end_date;
2422: v_period_hours := TRUE;
2423: ELSE
2424:
2425: hr_utility.trace(' p_period_start_date IS NULL');
2426:
2427: v_range_start := sysdate;
2428: v_range_end := sysdate + 6;
2429: v_period_hours := FALSE;
2433:
2434: -- Hourly employee using work schedule.
2435: -- Get work schedule name
2436:
2437: hr_utility.trace(' Hourly employee using work schedule');
2438: hr_utility.trace(' Get work schedule name');
2439:
2440: v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2441:
2434: -- Hourly employee using work schedule.
2435: -- Get work schedule name
2436:
2437: hr_utility.trace(' Hourly employee using work schedule');
2438: hr_utility.trace(' Get work schedule name');
2439:
2440: v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2441:
2442: hr_utility.trace(' v_ws_id ='||to_number(v_ws_id));
2438: hr_utility.trace(' Get work schedule name');
2439:
2440: v_ws_id := fnd_number.canonical_to_number(p_asg_work_sched);
2441:
2442: hr_utility.trace(' v_ws_id ='||to_number(v_ws_id));
2443:
2444:
2445: SELECT user_column_name
2446: INTO v_work_sched_name
2448: WHERE user_column_id = v_ws_id
2449: AND NVL(business_group_id, p_bg) = p_bg
2450: AND NVL(legislation_code,'US') = 'US';
2451:
2452: hr_utility.trace(' v_work_sched_name ='||v_work_sched_name);
2453: hr_utility.trace(' Calling Work_Schedule_Total_Hours');
2454:
2455: v_hrs_per_range := Work_Schedule_Total_Hours( p_bg,
2456: v_work_sched_name,
2449: AND NVL(business_group_id, p_bg) = p_bg
2450: AND NVL(legislation_code,'US') = 'US';
2451:
2452: hr_utility.trace(' v_work_sched_name ='||v_work_sched_name);
2453: hr_utility.trace(' Calling Work_Schedule_Total_Hours');
2454:
2455: v_hrs_per_range := Work_Schedule_Total_Hours( p_bg,
2456: v_work_sched_name,
2457: v_range_start,
2458: v_range_end);
2459:
2460: ELSE-- Hourly emp using Standard Hours on asg.
2461:
2462: hr_utility.trace(' Hourly emp using Standard Hours on asg');
2463:
2464:
2465: hr_utility.trace(' calling Standard_Hours_Worked');
2466: v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2461:
2462: hr_utility.trace(' Hourly emp using Standard Hours on asg');
2463:
2464:
2465: hr_utility.trace(' calling Standard_Hours_Worked');
2466: v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2467: v_range_start,
2468: v_range_end,
2469: p_asg_std_freq);
2471: END IF;
2472:
2473: IF v_period_hours THEN
2474:
2475: hr_utility.trace(' v_period_hours is TRUE');
2476:
2477: select TPT.number_per_fiscal_year
2478: into v_periods_per_fiscal_yr
2479: from pay_payrolls_f PPF,
2493: END IF;
2494:
2495: ELSIF v_pay_basis = 'PERIOD' THEN
2496:
2497: hr_utility.trace(' v_pay_basis = PERIOD');
2498:
2499: SELECT TPT.number_per_fiscal_year
2500: INTO v_annualizing_factor
2501: FROM pay_payrolls_f PRL,
2511:
2512: ELSIF v_pay_basis = 'ANNUAL' THEN
2513:
2514:
2515: hr_utility.trace(' v_pay_basis = ANNUAL');
2516: v_annualizing_factor := 1;
2517:
2518: ELSE
2519:
2519:
2520: -- Did not recognize "pay basis", return -999 as annualizing factor.
2521: -- Remember this for debugging when zeroes come out as results!!!
2522:
2523: hr_utility.trace(' Did not recognize pay basis');
2524:
2525: v_annualizing_factor := 0;
2526: RETURN v_annualizing_factor;
2527:
2530: EXCEPTION
2531:
2532: WHEN NO_DATA_FOUND THEN
2533:
2534: hr_utility.trace(' When no data found' );
2535: v_use_pay_basis := 0;
2536:
2537: END; /* SELECT LOOKUP CODE */
2538:
2537: END; /* SELECT LOOKUP CODE */
2538:
2539: IF v_use_pay_basis = 0 THEN
2540:
2541: hr_utility.trace(' Not using pay basis as frequency');
2542:
2543: -- Not using pay basis as frequency...
2544:
2545: IF (p_freq IS NULL) OR
2547: (UPPER(p_freq) = 'NOT ENTERED') THEN
2548:
2549: -- Get "annuallizing factor" from period type of the payroll.
2550:
2551: hr_utility.trace('Get annuallizing factor from period type of the payroll');
2552:
2553: SELECT TPT.number_per_fiscal_year
2554: INTO v_annualizing_factor
2555: FROM pay_payrolls_f PRL,
2561: AND PRL.effective_end_date
2562: AND PRL.payroll_id = p_payroll
2563: AND PRL.business_group_id + 0 = p_bg;
2564:
2565: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2566:
2567: ELSIF UPPER(p_freq) <> 'HOURLY' THEN
2568:
2569: -- Not hourly, an actual time period type!
2566:
2567: ELSIF UPPER(p_freq) <> 'HOURLY' THEN
2568:
2569: -- Not hourly, an actual time period type!
2570: hr_utility.trace('Not hourly - an actual time period type');
2571:
2572: BEGIN
2573:
2574: hr_utility.trace(' selecting from per_time_period_types');
2570: hr_utility.trace('Not hourly - an actual time period type');
2571:
2572: BEGIN
2573:
2574: hr_utility.trace(' selecting from per_time_period_types');
2575:
2576: SELECT PT.number_per_fiscal_year
2577: INTO v_annualizing_factor
2578: FROM per_time_period_types PT
2577: INTO v_annualizing_factor
2578: FROM per_time_period_types PT
2579: WHERE UPPER(PT.period_type) = UPPER(p_freq);
2580:
2581: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2582:
2583: EXCEPTION when NO_DATA_FOUND then
2584:
2585: -- Added as part of SALLY CLEANUP.
2586: -- Could have been passed in an ASG_FREQ dbi which might have the values of
2587: -- 'Day' or 'Month' which do not map to a time period type. So we'll do these by hand.
2588:
2589: IF UPPER(p_freq) = 'DAY' THEN
2590: hr_utility.trace(' p_freq = DAY');
2591: v_annualizing_factor := c_days_per_year;
2592: ELSIF UPPER(p_freq) = 'MONTH' THEN
2593: v_annualizing_factor := c_months_per_year;
2594: hr_utility.trace(' p_freq = MONTH');
2590: hr_utility.trace(' p_freq = DAY');
2591: v_annualizing_factor := c_days_per_year;
2592: ELSIF UPPER(p_freq) = 'MONTH' THEN
2593: v_annualizing_factor := c_months_per_year;
2594: hr_utility.trace(' p_freq = MONTH');
2595: END IF;
2596:
2597: END;
2598:
2596:
2597: END;
2598:
2599: ELSE -- Hourly employee...
2600: hr_utility.trace(' Hourly Employee');
2601:
2602: IF p_period_start_date IS NOT NULL THEN
2603: v_range_start := p_period_start_date;
2604: v_range_end := p_period_end_date;
2630: v_range_end);
2631:
2632: ELSE-- Hourly emp using Standard Hours on asg.
2633:
2634: hr_utility.trace(' Hourly emp using Standard Hours on asg');
2635:
2636: hr_utility.trace('calling Standard_Hours_Worked');
2637:
2638: v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
2632: ELSE-- Hourly emp using Standard Hours on asg.
2633:
2634: hr_utility.trace(' Hourly emp using Standard Hours on asg');
2635:
2636: hr_utility.trace('calling Standard_Hours_Worked');
2637:
2638: v_hrs_per_range := Standard_Hours_Worked(p_asg_std_hrs,
2639: v_range_start,
2640: v_range_end,
2639: v_range_start,
2640: v_range_end,
2641: p_asg_std_freq);
2642:
2643: hr_utility.trace('returned Standard_Hours_Worked');
2644: END IF;
2645:
2646:
2647: IF v_period_hours THEN
2645:
2646:
2647: IF v_period_hours THEN
2648:
2649: hr_utility.trace('v_period_hours = TRUE');
2650:
2651: select TPT.number_per_fiscal_year
2652: into v_periods_per_fiscal_yr
2653: from pay_payrolls_f PPF,
2658: and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
2659: and TPT.period_type = PPF.period_type;
2660:
2661: v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2662: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2663: hr_utility.trace('v_periods_per_fiscal_yr ='||to_number(v_periods_per_fiscal_yr));
2664: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2665:
2666: ELSE
2659: and TPT.period_type = PPF.period_type;
2660:
2661: v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2662: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2663: hr_utility.trace('v_periods_per_fiscal_yr ='||to_number(v_periods_per_fiscal_yr));
2664: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2665:
2666: ELSE
2667:
2660:
2661: v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2662: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2663: hr_utility.trace('v_periods_per_fiscal_yr ='||to_number(v_periods_per_fiscal_yr));
2664: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2665:
2666: ELSE
2667:
2668: hr_utility.trace('v_period_hours = FALSE');
2664: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2665:
2666: ELSE
2667:
2668: hr_utility.trace('v_period_hours = FALSE');
2669:
2670: v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2671:
2672: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2668: hr_utility.trace('v_period_hours = FALSE');
2669:
2670: v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2671:
2672: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2673: hr_utility.trace('c_weeks_per_year ='||to_number(c_weeks_per_year));
2674: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2675:
2676: END IF;
2669:
2670: v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2671:
2672: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2673: hr_utility.trace('c_weeks_per_year ='||to_number(c_weeks_per_year));
2674: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2675:
2676: END IF;
2677:
2670: v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2671:
2672: hr_utility.trace('v_hrs_per_range ='||to_number(v_hrs_per_range));
2673: hr_utility.trace('c_weeks_per_year ='||to_number(c_weeks_per_year));
2674: hr_utility.trace('v_annualizing_factor ='||to_number(v_annualizing_factor));
2675:
2676: END IF;
2677:
2678: END IF;
2679:
2680: END IF; -- (v_use_pay_basis = 0)
2681:
2682:
2683: hr_utility.trace(' Getting out of Get_Annualizing_Factor for '||v_pay_basis);
2684: RETURN v_annualizing_factor;
2685:
2686: END Get_Annualizing_Factor;
2687:
2688:
2689: BEGIN -- Convert Figure
2690: --begin_convert_period_type
2691:
2692: --hr_utility.trace_on(null,'UDFS');
2693:
2694: hr_utility.trace('UDFS Entered Convert_Period_Type');
2695:
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2690: --begin_convert_period_type
2691:
2692: --hr_utility.trace_on(null,'UDFS');
2693:
2694: hr_utility.trace('UDFS Entered Convert_Period_Type');
2695:
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2692: --hr_utility.trace_on(null,'UDFS');
2693:
2694: hr_utility.trace('UDFS Entered Convert_Period_Type');
2695:
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2693:
2694: hr_utility.trace('UDFS Entered Convert_Period_Type');
2695:
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2694: hr_utility.trace('UDFS Entered Convert_Period_Type');
2695:
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2695:
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2696: hr_utility.trace(' p_bus_grp_id: '|| p_bus_grp_id);
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2704:
2697: hr_utility.trace(' p_payroll_id: '||p_payroll_id);
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2704:
2705: hr_utility.trace(' p_period_end_date: '||to_char(p_period_end_date));
2698: hr_utility.trace(' p_asst_work_schedule: '||p_asst_work_schedule);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2704:
2705: hr_utility.trace(' p_period_end_date: '||to_char(p_period_end_date));
2706: hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
2699: hr_utility.trace(' p_asst_std_hours: '||p_asst_std_hours);
2700: hr_utility.trace(' p_figure: '||p_figure);
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2704:
2705: hr_utility.trace(' p_period_end_date: '||to_char(p_period_end_date));
2706: hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
2707:
2701: hr_utility.trace(' p_from_freq : '||p_from_freq);
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2704:
2705: hr_utility.trace(' p_period_end_date: '||to_char(p_period_end_date));
2706: hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
2707:
2708:
2709: --
2702: hr_utility.trace(' p_to_freq: '||p_to_freq);
2703: hr_utility.trace(' p_period_start_date: '||to_char(p_period_start_date));
2704:
2705: hr_utility.trace(' p_period_end_date: '||to_char(p_period_end_date));
2706: hr_utility.trace(' p_asst_std_freq: '||p_asst_std_freq);
2707:
2708:
2709: --
2710: -- If From_Freq and To_Freq are the same, then we're done.
2714:
2715: RETURN p_figure;
2716:
2717: END IF;
2718: hr_utility.trace('Calling Get_Annualizing_Factor for FROM case');
2719: v_from_annualizing_factor := Get_Annualizing_Factor(
2720: p_bg => p_bus_grp_id,
2721: p_payroll => p_payroll_id,
2722: p_freq => p_from_freq,
2723: p_asg_work_sched => p_asst_work_schedule,
2724: p_asg_std_hrs => p_asst_std_hours,
2725: p_asg_std_freq => p_asst_std_freq);
2726:
2727: hr_utility.trace('Calling Get_Annualizing_Factor for TO case');
2728:
2729: v_to_annualizing_factor := Get_Annualizing_Factor(
2730: p_bg => p_bus_grp_id,
2731: p_payroll => p_payroll_id,
2736:
2737: --
2738: -- Annualize "Figure" and convert to To_Freq.
2739: --
2740: hr_utility.trace('v_from_annualizing_factor ='||to_char(v_from_annualizing_factor));
2741: hr_utility.trace('v_to_annualizing_factor ='||to_char(v_to_annualizing_factor));
2742:
2743: IF v_to_annualizing_factor = 0 OR
2744: v_to_annualizing_factor = -999 OR
2737: --
2738: -- Annualize "Figure" and convert to To_Freq.
2739: --
2740: hr_utility.trace('v_from_annualizing_factor ='||to_char(v_from_annualizing_factor));
2741: hr_utility.trace('v_to_annualizing_factor ='||to_char(v_to_annualizing_factor));
2742:
2743: IF v_to_annualizing_factor = 0 OR
2744: v_to_annualizing_factor = -999 OR
2745: v_from_annualizing_factor = -999 THEN
2743: IF v_to_annualizing_factor = 0 OR
2744: v_to_annualizing_factor = -999 OR
2745: v_from_annualizing_factor = -999 THEN
2746:
2747: hr_utility.trace(' v_to_ann =0 or -999 or v_from = -999');
2748:
2749: v_converted_figure := 0;
2750: RETURN v_converted_figure;
2751:
2750: RETURN v_converted_figure;
2751:
2752: ELSE
2753:
2754: hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
2755:
2756: hr_utility.trace('p_figure Monthly Salary = '||p_figure);
2757: hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
2758: hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
2752: ELSE
2753:
2754: hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
2755:
2756: hr_utility.trace('p_figure Monthly Salary = '||p_figure);
2757: hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
2758: hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
2759:
2760: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2753:
2754: hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
2755:
2756: hr_utility.trace('p_figure Monthly Salary = '||p_figure);
2757: hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
2758: hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
2759:
2760: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2761: hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
2754: hr_utility.trace(' v_to_ann NOT 0 or -999 or v_from = -999');
2755:
2756: hr_utility.trace('p_figure Monthly Salary = '||p_figure);
2757: hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
2758: hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
2759:
2760: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2761: hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
2762:
2757: hr_utility.trace('v_from_annualizing_factor = '||v_from_annualizing_factor);
2758: hr_utility.trace('v_to_annualizing_factor = '||v_to_annualizing_factor);
2759:
2760: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2761: hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
2762:
2763: hr_utility.trace('UDFS v_converted_figure := '||v_converted_figure);
2764:
2765: END IF;
2759:
2760: v_converted_figure := (p_figure * v_from_annualizing_factor) / v_to_annualizing_factor;
2761: hr_utility.trace('conv figure is monthly_sal * ann_from div by ann to');
2762:
2763: hr_utility.trace('UDFS v_converted_figure := '||v_converted_figure);
2764:
2765: END IF;
2766:
2767: -- Done
2887: -- Check for valid range
2888: IF v_range_start > v_range_end THEN
2889: --
2890: RETURN v_total_hours;
2891: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2892: -- hr_utility.raise_error;
2893: --
2894: END IF;
2895: --
2888: IF v_range_start > v_range_end THEN
2889: --
2890: RETURN v_total_hours;
2891: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2892: -- hr_utility.raise_error;
2893: --
2894: END IF;
2895: --
2896: -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
2993: -- Get formula context values: bg, payroll, asst ids; date earned.
2994: --
2995: -- ...
2996: --
2997: hr_utility.trace('UDFS Entered chained_element_exists');
2998:
2999: SELECT DECODE(COUNT(0), 0, 'N', 'Y')
3000: INTO v_ele_exists
3001: FROM PAY_ELEMENT_ENTRIES_F ELE,
3009: AND ELI.element_type_id = ELT.element_type_id
3010: AND NVL(ELT.business_group_id, p_bg_id) = p_bg_id
3011: AND UPPER(ELT.element_name) = UPPER(p_ele_name);
3012:
3013: hr_utility.trace('UDFS Leaving chained_element_exists');
3014: RETURN v_ele_exists;
3015:
3016: END chained_element_exists;
3017:
3106: --
3107: RETURN v_proc_run_type;
3108: --
3109: exception when NO_DATA_FOUND then
3110: hr_utility.set_location('get_process_run_flag', 30);
3111: RETURN v_proc_run_type;
3112: end;
3113: --
3114: END get_process_run_flag;
3135:
3136: -- Check that
3137: -- This DDF info is held in ELEMENT_INFORMATION3.
3138:
3139: hr_utility.set_location('check_dedn_freq', 10);
3140:
3141: begin
3142: SELECT element_information3
3143: INTO v_proc_run_type
3162: p_date_earned,
3163: p_ele_type_id,
3164: v_skip_element);
3165:
3166: hr_utility.set_location('check_dedn_freq', 45);
3167:
3168: RETURN v_skip_element;
3169:
3170: END check_dedn_freq;
3203: /* Init */
3204: v_skip_element := 'N';
3205: --
3206:
3207: hr_utility.set_location('Separate_Check_Skip', 7);
3208:
3209: SELECT RRV.result_value
3210: INTO v_dedn_proc
3211: FROM pay_run_result_values RRV,
3228: --
3229:
3230: v_skip_element := 'Y';
3231:
3232: hr_utility.set_location('Separate_Check_Skip', 9);
3233:
3234: begin
3235:
3236: SELECT ECL.classification_name
3248: -- Change v_skip_element back to 'N' if this a tax deduction.
3249: -- ie. we know DEDN PROC inpval is not null, meaning it's either TAX ONLY
3250: -- or PRETAX AND TAX ONLY.
3251:
3252: hr_utility.set_location('Separate_Check_Skip', 10);
3253: v_skip_element := 'N';
3254: RETURN v_skip_element;
3255:
3256: ELSIF UPPER(v_ele_class_name) = 'PRE-TAX DEDUCTIONS' AND
3257: v_dedn_proc = 'PTT' THEN
3258:
3259: -- Change v_skip_element back to 'N' if dedn proc = 'PTT'
3260:
3261: hr_utility.set_location('Separate_Check_Skip', 11);
3262: v_skip_element := 'N';
3263: RETURN v_skip_element;
3264:
3265: END IF;
3264:
3265: END IF;
3266:
3267: exception WHEN NO_DATA_FOUND THEN
3268: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
3269: v_skip_element := 'Y';
3270: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
3271: -- hr_utility.raise_error;
3272: end;
3266:
3267: exception WHEN NO_DATA_FOUND THEN
3268: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
3269: v_skip_element := 'Y';
3270: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
3271: -- hr_utility.raise_error;
3272: end;
3273:
3274: RETURN v_skip_element;
3267: exception WHEN NO_DATA_FOUND THEN
3268: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
3269: v_skip_element := 'Y';
3270: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
3271: -- hr_utility.raise_error;
3272: end;
3273:
3274: RETURN v_skip_element;
3275:
3274: RETURN v_skip_element;
3275:
3276: EXCEPTION
3277: WHEN NO_DATA_FOUND THEN
3278: hr_utility.set_location('Separate_Check_Skip', 21);
3279: RETURN v_skip_element;
3280: -- Special Dedn Proc not required. SKIP_FLAG = 'N'.
3281: --
3282: END Separate_Check_Skip;
3303: /* Init */
3304: sepcheck_flag :='N';
3305:
3306:
3307: hr_utility.set_location('Other_Non_Separate_Check', 10);
3308:
3309: SELECT DECODE(COUNT(IPV.input_value_id), 0, 'N', 'Y')
3310: INTO sepcheck_flag
3311: FROM pay_element_entry_values_f EEV,
3321: AND EEV.input_value_id = IPV.input_value_id
3322: AND UPPER(IPV.name) = 'SEPARATE CHECK';
3323: --
3324: IF sepcheck_flag = 'Y' THEN
3325: hr_utility.set_location('Other_Non_Separate_Check', 15);
3326: RETURN sepcheck_flag;
3327: END IF;
3328: --
3329: hr_utility.set_location('Other_Non_Separate_Check', 20);
3325: hr_utility.set_location('Other_Non_Separate_Check', 15);
3326: RETURN sepcheck_flag;
3327: END IF;
3328: --
3329: hr_utility.set_location('Other_Non_Separate_Check', 20);
3330:
3331: SELECT DECODE(COUNT(ELE.element_entry_id), 0, 'N', 'Y')
3332: INTO sepcheck_flag
3333: FROM pay_element_entries_f ELE,
3361:
3362: --
3363: EXCEPTION
3364: WHEN NO_DATA_FOUND THEN
3365: hr_utility.set_location('Other_Non_Separate_Check', 30);
3366: RETURN sepcheck_flag;
3367: --
3368: END Other_Non_Separate_Check;
3369: --
3663: -- Remember to check for a rate via Rate Code!
3664: --
3665: -- Go ahead and set pay_basis_rate now - will most likely be used somewhere.
3666: --
3667: hr_utility.set_location('OT_Base_Rate', 5);
3668:
3669: select start_date,
3670: end_date
3671: into v_range_start,
3688: --
3689: OPEN get_tew_rate;
3690: --
3691: LOOP
3692: hr_utility.set_location('OT_Base_Rate', 10);
3693: FETCH get_tew_rate
3694: INTO v_tew_rate, v_entry_id;
3695: EXIT WHEN get_tew_rate%NOTFOUND;
3696: --
3697: v_tew_count := v_tew_count + 1;
3698: IF v_tew_rate <> 0 THEN
3699: v_tew_total_rate := v_tew_total_rate + v_tew_rate;
3700: ELSE -- no Rate entered, check Rate Code
3701: hr_utility.set_location('OT_Base_Rate', 15);
3702: SELECT /*+ ORDERED */ NVL(EEV.screen_entry_value, 'NOT ENTERED')
3703: INTO v_tew_rcode
3704: FROM pay_element_entries_f ELE,
3705: pay_element_entry_values_f EEV,
3726: -- Use pay basis salary converted to hourly rate.
3727: v_tew_total_rate := v_tew_total_rate + v_pay_basis_rate;
3728: ELSE
3729: -- Find rate from rate table.
3730: hr_utility.set_location('OT_Base_Rate', 17);
3731: v_tew_total_rate := v_tew_total_rate +
3732: FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
3733: p_bg_id,
3734: c_rate_table_name,
3742: --
3743: CLOSE get_tew_rate;
3744: --
3745: IF v_tew_count = 0 THEN -- ie. only use "Regular" rates if TEW not entered.
3746: hr_utility.set_location('OT_Base_Rate', 20);
3747: SELECT /*+ ORDERED */ COUNT(IPV.input_value_id)
3748: INTO v_use_regwage
3749: FROM pay_element_entries_f ELE,
3750: pay_element_entry_values_f EEV,
3768: AND IPV.name = 'Rate';
3769:
3770: --
3771: IF v_use_regwage <> 0 THEN
3772: hr_utility.set_location('OT_Base_Rate', 30);
3773: SELECT /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3774: EEV.element_entry_id
3775: INTO v_regwage_rate,
3776: v_entry_id
3796: AND IPV.legislation_code = ELT.legislation_code;
3797:
3798: --
3799: IF v_regwage_rate = 0 THEN
3800: hr_utility.set_location('OT_Base_Rate', 40);
3801: SELECT /*+ ORDERED */ NVL(EEV.screen_entry_value, 'NOT ENTERED')
3802: INTO v_regwage_rcode
3803: FROM pay_element_entries_f ELE,
3804: pay_element_entry_values_f EEV,
3822: AND IPV.business_group_id = ELT.business_group_id
3823: AND IPV.legislation_code = ELT.legislation_code;
3824:
3825: --
3826: hr_utility.set_location('OT_Base_Rate', 41);
3827: v_regwage_rate := FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
3828: p_bus_group_id => p_bg_id,
3829: p_table_name => c_rate_table_name,
3830: p_col_name => c_rate_table_column,
3832: END IF;
3833: v_ot_base_rate := v_ot_base_rate + v_regwage_rate;
3834: --
3835: ELSE
3836: hr_utility.set_location('OT_Base_Rate', 50);
3837: SELECT /*+ ORDERED */ COUNT(IPV.input_value_id)
3838: INTO v_use_regsal
3839: FROM pay_element_entries_f ELE,
3840: pay_element_entry_values_f EEV,
3858: AND IPV.legislation_code = ELT.legislation_code;
3859:
3860: --
3861: IF v_use_regsal <> 0 THEN
3862: hr_utility.set_location('OT_Base_Rate', 51);
3863: SELECT /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
3864: INTO v_regsal_mosal
3865: FROM pay_element_entries_f ELE,
3866: pay_element_entry_values_f EEV,
3883: AND IPV.business_group_id = ELT.business_group_id
3884: AND IPV.legislation_code = ELT.legislation_code;
3885:
3886: --
3887: hr_utility.set_location('OT_Base_Rate', 60);
3888:
3889: v_regsal_rate := hr_us_ff_udfs.Convert_Period_Type(
3890: p_bus_grp_id => p_bg_id,
3891: p_payroll_id => p_pay_id,
3912: --
3913: OPEN get_include_in_ot;
3914: LOOP
3915: --
3916: hr_utility.set_location('OT_Base_Rate', 70);
3917: FETCH get_include_in_ot
3918: INTO v_eletype_id,
3919: v_ele_name,
3920: v_ff_name;
3921: EXIT WHEN get_include_in_ot%NOTFOUND;
3922: --
3923: IF SUBSTR(v_ff_name,1,11) = 'FLAT_AMOUNT' THEN
3924: -- Find "Amount" entered, convert to hourly figure.
3925: hr_utility.set_location('OT_Base_Rate', 80);
3926: OPEN get_flat_amounts;
3927: LOOP
3928: FETCH get_flat_amounts
3929: INTO v_flat_amount;
3928: FETCH get_flat_amounts
3929: INTO v_flat_amount;
3930: EXIT WHEN get_flat_amounts%NOTFOUND;
3931: v_flat_count := v_flat_count + 1;
3932: hr_utility.set_location('OT_Base_Rate', 90);
3933:
3934: v_flat_total := v_flat_total + hr_us_ff_udfs.Convert_Period_Type(
3935: p_bus_grp_id => p_bg_id,
3936: p_payroll_id => p_pay_id,
3945: --
3946: END LOOP;
3947: CLOSE get_flat_amounts;
3948: --
3949: hr_utility.set_location('OT_Base_Rate', 100);
3950: v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3951: --
3952: ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3953: hr_utility.set_location('OT_Base_Rate', 110);
3949: hr_utility.set_location('OT_Base_Rate', 100);
3950: v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3951: --
3952: ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3953: hr_utility.set_location('OT_Base_Rate', 110);
3954: OPEN get_percentages;
3955: LOOP
3956: FETCH get_percentages
3957: INTO v_percentage;
3958: EXIT WHEN get_percentages%NOTFOUND;
3959: v_pct_count := v_pct_count + 1;
3960: --
3961: IF v_regsal_rate <> 0 THEN
3962: hr_utility.set_location('OT_Base_Rate', 105);
3963: v_pct_total := v_percentage * v_regsal_rate;
3964: END IF;
3965: --
3966: END LOOP;
3966: END LOOP;
3967: --
3968: CLOSE get_percentages;
3969: --
3970: hr_utility.set_location('OT_Base_Rate', 110);
3971: v_ot_base_rate := v_ot_base_rate + (v_pct_total / v_pct_count);
3972: --
3973: ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3974: --
3973: ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3974: --
3975: -- Remember to look for "Rate Code" if necessary and "Multiple" always.
3976: --
3977: hr_utility.set_location('OT_Base_Rate', 115);
3978: OPEN get_rates;
3979: LOOP
3980: FETCH get_rates
3981: INTO v_rate, v_entry_id;
3979: LOOP
3980: FETCH get_rates
3981: INTO v_rate, v_entry_id;
3982: EXIT WHEN get_rates%NOTFOUND;
3983: hr_utility.set_location('OT_Base_Rate', 120);
3984: v_rate_count := v_rate_count + 1;
3985: IF v_rate = 0 THEN
3986: hr_utility.set_location('OT_Base_Rate', 125);
3987: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3982: EXIT WHEN get_rates%NOTFOUND;
3983: hr_utility.set_location('OT_Base_Rate', 120);
3984: v_rate_count := v_rate_count + 1;
3985: IF v_rate = 0 THEN
3986: hr_utility.set_location('OT_Base_Rate', 125);
3987: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3988: INTO v_rate_rcode
3989: FROM pay_element_entry_values_f EEV,
3990: pay_element_entries_f ELE,
4000: AND ELT.element_type_id = IPV.element_type_id
4001: AND UPPER(IPV.name) = 'RATE CODE';
4002: --
4003: IF v_rate_rcode <> 'NOT ENTERED' THEN
4004: hr_utility.set_location('OT_Base_Rate', 130);
4005: v_rate := FND_NUMBER.CANONICAL_TO_NUMBER(hruserdt.get_table_value(
4006: p_bg_id,
4007: c_rate_table_name,
4008: c_rate_table_column,
4011: --
4012: END IF;
4013: -- Now get "Multiple" on this entry, if any.
4014: IF v_rate <> 0 THEN
4015: hr_utility.set_location('OT_Base_Rate', 135);
4016: SELECT COUNT(0)
4017: INTO v_rate_mult_count
4018: FROM pay_element_entry_values_f EEV,
4019: pay_element_entries_f ELE,
4029: AND ELT.element_type_id = IPV.element_type_id
4030: AND UPPER(IPV.name) = 'MULTIPLE';
4031: --
4032: IF v_rate_mult_count <> 0 THEN
4033: hr_utility.set_location('OT_Base_Rate', 140);
4034: SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
4035: INTO v_rate_multiple
4036: FROM pay_element_entry_values_f EEV,
4037: pay_element_entries_f ELE,
4062: --
4063: v_ot_base_rate := v_ot_base_rate + (v_rate_total / v_rate_count);
4064: --
4065: ELSIF SUBSTR(v_ff_name,1,8) = 'GROSS_UP' THEN
4066: hr_utility.set_location('OT_Base_Rate', 150);
4067: OPEN get_grosses;
4068: LOOP
4069: FETCH get_grosses
4070: INTO v_gross_results;
4071: EXIT WHEN get_grosses%NOTFOUND;
4072: v_gross_count := v_gross_count + 1;
4073: IF v_gross_results <> 0 THEN
4074: -- Convert gross result to hourly figure.
4075: hr_utility.set_location('OT_Base_Rate', 160);
4076:
4077: v_gross_total := v_gross_total + hr_us_ff_udfs.Convert_Period_Type(
4078: p_bus_grp_id => p_bg_id,
4079: p_payroll_id => p_pay_id,
4102: RETURN v_ot_base_rate;
4103: --
4104: EXCEPTION
4105: WHEN NO_DATA_FOUND THEN
4106: hr_utility.set_location('OT_Base_Rate', 170);
4107: RETURN v_ot_base_rate;
4108: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
4109: -- hr_utility.raise_error;
4110: --
4104: EXCEPTION
4105: WHEN NO_DATA_FOUND THEN
4106: hr_utility.set_location('OT_Base_Rate', 170);
4107: RETURN v_ot_base_rate;
4108: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
4109: -- hr_utility.raise_error;
4110: --
4111: END OT_Base_Rate;
4112: --
4105: WHEN NO_DATA_FOUND THEN
4106: hr_utility.set_location('OT_Base_Rate', 170);
4107: RETURN v_ot_base_rate;
4108: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
4109: -- hr_utility.raise_error;
4110: --
4111: END OT_Base_Rate;
4112: --
4113: /*
4218: c_months_per_fy := 12;
4219: c_years_per_fy := 1;
4220:
4221:
4222: hr_utility.set_location('Dedn_Freq_Factor', 10);
4223: SELECT DECODE(COUNT(FRP.freq_rule_period_id), 0, 'N', 'Y')
4224: INTO v_freq_rules_exist
4225: FROM pay_freq_rule_periods FRP,
4226: pay_ele_payroll_freq_rules EPF
4239: v_dedn_freq_factor := 1;
4240: --
4241: ELSIF p_ele_period_type = 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
4242: --
4243: hr_utility.set_location('Dedn_Freq_Factor', 15);
4244: SELECT TPT.number_per_fiscal_year
4245: INTO v_pay_period_num_per_fy
4246: FROM pay_payrolls_f PPF,
4247: per_time_period_types TPT
4253: IF v_pay_period_num_per_fy >= 12 THEN
4254: IF v_pay_period_num_per_fy = 12 THEN
4255: v_dedn_freq_factor := 1;
4256: ELSE
4257: hr_utility.set_location('Dedn_Freq_Factor', 20);
4258: SELECT COUNT(0)
4259: INTO v_pay_periods_in_month
4260: FROM per_time_periods PTP
4261: WHERE PTP.end_date
4261: WHERE PTP.end_date
4262: BETWEEN TRUNC(p_date_earned, 'MONTH')
4263: AND LAST_DAY(p_date_earned)
4264: AND PTP.payroll_id = p_payroll_id;
4265: hr_utility.set_location('v_pay_periods_in_month', v_pay_periods_in_month);
4266: --
4267: -- Frequency rules exist, so this select should never return 0.
4268: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4269: -- ie. so v_dedn_freq_factor will also equal 1.
4267: -- Frequency rules exist, so this select should never return 0.
4268: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4269: -- ie. so v_dedn_freq_factor will also equal 1.
4270: --
4271: hr_utility.set_location('Dedn_Freq_Factor', 25);
4272: SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
4273: INTO v_pay_periods_in_reset
4274: FROM pay_ele_payroll_freq_rules EPF,
4275: pay_freq_rule_periods FRP
4277: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4278: AND EPF.payroll_id = p_payroll_id
4279: AND EPF.element_type_id = p_element_type_id;
4280: --
4281: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4282: v_dedn_freq_factor := 1 / v_pay_periods_in_reset;
4283: --
4284: END IF;
4285: ELSE
4282: v_dedn_freq_factor := 1 / v_pay_periods_in_reset;
4283: --
4284: END IF;
4285: ELSE
4286: hr_utility.set_location('Dedn_Freq_Factor', 30);
4287: SELECT COUNT(0)
4288: INTO v_pay_periods_in_year
4289: FROM per_time_periods PTP
4290: WHERE PTP.end_date
4295: -- Frequency rules exist, so this select should never return 0.
4296: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4297: -- ie. so v_dedn_freq_factor will also equal 1.
4298: --
4299: hr_utility.set_location('Dedn_Freq_Factor', 35);
4300: SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
4301: INTO v_pay_periods_in_reset
4302: FROM pay_ele_payroll_freq_rules EPF,
4303: pay_freq_rule_periods FRP
4305: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4306: AND EPF.payroll_id = p_payroll_id
4307: AND EPF.element_type_id = p_element_type_id;
4308: --
4309: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4310: v_dedn_freq_factor := 1 / v_pay_periods_in_reset;
4311: --
4312: END IF;
4313: --
4312: END IF;
4313: --
4314: ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'N' THEN
4315: --
4316: hr_utility.set_location('Dedn_Freq_Factor', 40);
4317: SELECT number_per_fiscal_year
4318: INTO v_ele_period_num_per_fy
4319: FROM per_time_period_types TPT
4320: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
4318: INTO v_ele_period_num_per_fy
4319: FROM per_time_period_types TPT
4320: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
4321: --
4322: hr_utility.set_location('Dedn_Freq_Factor', 45);
4323: SELECT TPT.number_per_fiscal_year
4324: INTO v_pay_period_num_per_fy
4325: FROM per_time_period_types TPT,
4326: pay_payrolls_f PPF
4332: v_dedn_freq_factor := v_ele_period_num_per_fy / v_pay_period_num_per_fy;
4333: --
4334: ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
4335: --
4336: hr_utility.set_location('Dedn_Freq_Factor', 50);
4337: SELECT number_per_fiscal_year
4338: INTO v_ele_period_num_per_fy
4339: FROM per_time_period_types TPT
4340: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
4338: INTO v_ele_period_num_per_fy
4339: FROM per_time_period_types TPT
4340: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
4341: --
4342: hr_utility.set_location('Dedn_Freq_Factor', 55);
4343: SELECT TPT.number_per_fiscal_year
4344: INTO v_pay_period_num_per_fy
4345: FROM pay_payrolls_f PPF,
4346: per_time_period_types TPT
4349: AND p_date_earned BETWEEN PPF.effective_start_date
4350: AND PPF.effective_end_date;
4351: --
4352: IF v_pay_period_num_per_fy >= 12 THEN
4353: hr_utility.set_location('Dedn_Freq_Factor', 60);
4354: SELECT COUNT(0)
4355: INTO v_pay_periods_in_month
4356: FROM per_time_periods PTP
4357: WHERE PTP.end_date
4362: -- Frequency rules exist, so this select should never return 0.
4363: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4364: -- ie. so v_dedn_freq_factor will also equal 1.
4365: --
4366: hr_utility.set_location('Dedn_Freq_Factor', 65);
4367: SELECT COUNT(0)
4368: INTO v_pay_periods_in_reset
4369: FROM pay_ele_payroll_freq_rules EPF,
4370: pay_freq_rule_periods FRP
4371: WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
4372: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4373: AND EPF.payroll_id = p_payroll_id
4374: AND EPF.element_type_id = p_element_type_id;
4375: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4376: --
4377: IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
4378: v_dedn_freq_factor := 1;
4379: ELSIF v_pay_periods_in_reset = 0 THEN
4385: v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
4386: END IF;
4387: --
4388: ELSE
4389: hr_utility.set_location('Dedn_Freq_Factor', 70);
4390: SELECT COUNT(0)
4391: INTO v_pay_periods_in_year
4392: FROM per_time_periods PTP
4393: WHERE PTP.end_date
4398: -- Frequency rules exist, so this select should never return 0.
4399: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
4400: -- ie. so v_dedn_freq_factor will also equal 1.
4401: --
4402: hr_utility.set_location('Dedn_Freq_Factor', 75);
4403: SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
4404: INTO v_pay_periods_in_reset
4405: FROM pay_ele_payroll_freq_rules EPF,
4406: pay_freq_rule_periods FRP
4408: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
4409: AND EPF.payroll_id = p_payroll_id
4410: AND EPF.element_type_id = p_element_type_id;
4411: --
4412: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
4413: IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
4414: v_dedn_freq_factor := 1;
4415: ELSE
4416: v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
4419: END IF;
4420: --
4421: END IF;
4422: --
4423: hr_utility.set_location('Dedn_Freq_Factor', 80);
4424: RETURN v_dedn_freq_factor;
4425: --
4426: END Dedn_Freq_Factor;
4427: --
4463: --
4464: p_to_arrears := 0;
4465: p_not_taken := 0;
4466:
4467: hr_utility.set_location('hr_us_ff_udfs.arrearage', 1);
4468:
4469: -- Determine if Arrears = 'Y' for this dedn
4470: -- Can do this by checking for "Clear Arrears" input value on base ele.
4471: -- This input value is only created when Arrears is marked Yes on Deductions
4472: -- screen.
4473:
4474: begin
4475:
4476: hr_utility.set_location('Shadow elename = '||v_shadow_ele_name, 38 );
4477:
4478: select 'Y'
4479: into v_arrears_flag
4480: from pay_input_values_f ipv
4485:
4486: exception
4487:
4488: WHEN NO_DATA_FOUND THEN
4489: hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4490: v_arrears_flag := 'N';
4491:
4492: WHEN TOO_MANY_ROWS THEN
4493: hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4489: hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4490: v_arrears_flag := 'N';
4491:
4492: WHEN TOO_MANY_ROWS THEN
4493: hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4494: v_arrears_flag := 'N';
4495:
4496: end;
4497:
4501:
4502: p_to_arrears := 0;
4503: p_not_taken := 0;
4504: l_dedn_amt := p_dedn_amt;
4505: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4506: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', p_dedn_amt);
4507: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4508:
4509: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4502: p_to_arrears := 0;
4503: p_not_taken := 0;
4504: l_dedn_amt := p_dedn_amt;
4505: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4506: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', p_dedn_amt);
4507: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4508:
4509: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4510: -- Don't take anything, no arrears contr either.
4503: p_not_taken := 0;
4504: l_dedn_amt := p_dedn_amt;
4505: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4506: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', p_dedn_amt);
4507: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4508:
4509: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4510: -- Don't take anything, no arrears contr either.
4511: p_to_arrears := 0;
4510: -- Don't take anything, no arrears contr either.
4511: p_to_arrears := 0;
4512: p_not_taken := p_dedn_amt;
4513: l_dedn_amt := 0;
4514: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4515: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4516: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4517:
4518: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4511: p_to_arrears := 0;
4512: p_not_taken := p_dedn_amt;
4513: l_dedn_amt := 0;
4514: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4515: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4516: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4517:
4518: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4519:
4512: p_not_taken := p_dedn_amt;
4513: l_dedn_amt := 0;
4514: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4515: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4516: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4517:
4518: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4519:
4520: IF p_partial_flag = 'Y' THEN
4526: l_dedn_amt := 0 ;
4527: ELSE
4528: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4529: END IF;
4530: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4531: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4532: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4533:
4534: ELSE
4527: ELSE
4528: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4529: END IF;
4530: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4531: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4532: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4533:
4534: ELSE
4535:
4528: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4529: END IF;
4530: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4531: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4532: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4533:
4534: ELSE
4535:
4536: p_to_arrears := 0;
4535:
4536: p_to_arrears := 0;
4537: p_not_taken := p_dedn_amt;
4538: l_dedn_amt := 0;
4539: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4540: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4541: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4542:
4543: END IF;
4536: p_to_arrears := 0;
4537: p_not_taken := p_dedn_amt;
4538: l_dedn_amt := 0;
4539: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4540: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4541: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4542:
4543: END IF;
4544:
4537: p_not_taken := p_dedn_amt;
4538: l_dedn_amt := 0;
4539: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4540: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4541: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4542:
4543: END IF;
4544:
4545: END IF;
4551: -- Don't take anything, put it all in arrears.
4552: p_to_arrears := p_dedn_amt;
4553: p_not_taken := p_dedn_amt;
4554: l_dedn_amt := 0;
4555: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4556: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4557: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4558:
4559: ELSE
4552: p_to_arrears := p_dedn_amt;
4553: p_not_taken := p_dedn_amt;
4554: l_dedn_amt := 0;
4555: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4556: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4557: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4558:
4559: ELSE
4560:
4553: p_not_taken := p_dedn_amt;
4554: l_dedn_amt := 0;
4555: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4556: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4557: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4558:
4559: ELSE
4560:
4561: l_total_dedn := p_dedn_amt + p_arrears_itd;
4567: -- there's enough net to take it all, clear arrears:
4568: p_to_arrears := -1 * p_arrears_itd;
4569: l_dedn_amt := l_total_dedn;
4570: p_not_taken := 0;
4571: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4572: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4573: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4574:
4575: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4568: p_to_arrears := -1 * p_arrears_itd;
4569: l_dedn_amt := l_total_dedn;
4570: p_not_taken := 0;
4571: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4572: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4573: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4574:
4575: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4576: try and take as much of the total deduction amount (current dedn +
4569: l_dedn_amt := l_total_dedn;
4570: p_not_taken := 0;
4571: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4572: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4573: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4574:
4575: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4576: try and take as much of the total deduction amount (current dedn +
4577: arrears) and leave the rest in arrears. */
4593: ELSE
4594: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4595: END IF;
4596:
4597: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4598: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4599: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4600:
4601: ELSE -- p_partial_flag = 'N'
4594: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4595: END IF;
4596:
4597: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4598: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4599: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4600:
4601: ELSE -- p_partial_flag = 'N'
4602: IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4595: END IF;
4596:
4597: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4598: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4599: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4600:
4601: ELSE -- p_partial_flag = 'N'
4602: IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4603: -- Take the whole deduction amount.
4609: p_to_arrears := p_dedn_amt;
4610: p_not_taken := p_dedn_amt;
4611: l_dedn_amt := 0;
4612: END IF;
4613: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4614: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4615: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4616:
4617: END IF;
4610: p_not_taken := p_dedn_amt;
4611: l_dedn_amt := 0;
4612: END IF;
4613: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4614: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4615: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4616:
4617: END IF;
4618:
4611: l_dedn_amt := 0;
4612: END IF;
4613: -- hr_utility.set_location('pyusudfs.arrearage.to_arrears = ', p_to_arrears);
4614: hr_utility.set_location('pyusudfs.arrearage.dedn_amt = ', l_dedn_amt);
4615: -- hr_utility.set_location('pyusudfs.arrearage.not_taken = ', p_not_taken);
4616:
4617: END IF;
4618:
4619: END IF;
4666: RETURN l_geocode;
4667: --
4668: EXCEPTION
4669: WHEN OTHERS THEN
4670: hr_utility.set_location('hr_us_ff_udfs.addr_val', 20);
4671: l_geocode := '00-000-0000';
4672: RETURN l_geocode;
4673: --
4674: END addr_val; -- addr_val
4742: IF (p_skip_rule = 'Y') THEN
4743: RAISE TOO_MANY_ROWS;
4744: END IF;
4745:
4746: hr_utility.set_location('hr_us_ff_udfs.addr_val', 1);
4747:
4748: IF (nvl(hr_general2.get_oracle_db_version, 0) < 9.0) THEN
4749: SELECT /*+ ORDERED */ a.state_code||'-'||a.county_code||'-'||a.city_code
4750: INTO l_geocode
4772: --
4773: EXCEPTION -- (2)
4774: --
4775: WHEN NO_DATA_FOUND THEN -- Invalid city/zip combo
4776: hr_utility.set_location('hr_us_ff_udfs.addr_val', 3);
4777: l_geocode := '00-000-0000';
4778: RETURN l_geocode;
4779: --
4780:
4784: INTO l_state_code
4785: FROM pay_us_states
4786: WHERE state_abbrev = p_state_abbrev;
4787:
4788: hr_utility.set_location('hr_us_ff_udfs.addr_val', 5);
4789: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4790: INTO l_geocode
4791: FROM pay_us_zip_codes z,
4792: pay_us_city_names a,
4806: --
4807: -- Fallout from (2) ie. county/city/zip combo invalid or does not
4808: -- uniquely define geocode.
4809: WHEN NO_DATA_FOUND THEN
4810: hr_utility.set_location('hr_us_ff_udfs.addr_val', 7);
4811: l_geocode := '00-000-0000';
4812: RETURN l_geocode;
4813: --
4814: WHEN TOO_MANY_ROWS THEN
4811: l_geocode := '00-000-0000';
4812: RETURN l_geocode;
4813: --
4814: WHEN TOO_MANY_ROWS THEN
4815: hr_utility.set_location('hr_us_ff_udfs.addr_val', 9);
4816: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4817: INTO l_geocode
4818: FROM pay_us_zip_codes z,
4819: pay_us_city_names a,
4837: end; -- (1)
4838: --
4839: -- We're in Main
4840: --
4841: hr_utility.set_location('hr_us_ff_udfs.addr_val', 11);
4842: --
4843: if (substr(l_geocode,8,1) = 'U') THEN
4844: l_geocode := substr(l_geocode,1,7)||'0000';
4845: END IF;
4849: EXCEPTION -- Main addr_val
4850: -- Fallout from (1) state/county/city/zip does not uniquely define a geo.
4851: -- Return failure geocode.
4852: WHEN NO_DATA_FOUND THEN
4853: hr_utility.set_location('hr_us_ff_udfs.addr_val', 13);
4854: l_geocode := '00-000-0000';
4855: RETURN l_geocode;
4856: --
4857: WHEN TOO_MANY_ROWS THEN
4854: l_geocode := '00-000-0000';
4855: RETURN l_geocode;
4856: --
4857: WHEN TOO_MANY_ROWS THEN
4858: hr_utility.set_location('hr_us_ff_udfs.addr_val', 15);
4859: l_geocode := '00-000-0000';
4860: RETURN l_geocode;
4861: --
4862: END addr_val; -- Main addr_val
4870: RETURN varchar2 IS
4871: r_territory_short_name fnd_territories_tl.territory_short_name%type;
4872: BEGIN
4873:
4874: hr_utility.set_location('Pay us country territory code : ', p_territory_code);
4875: IF p_territory_code IS NOT NULL THEN
4876:
4877:
4878: SELECT territory_short_name