[Home] [Help]
283: v_total_hours NUMBER(15,7) ;
284:
285: BEGIN
286:
287: hr_utility.set_location('Pro_Earn: actual hours worked IN = ', p_act_hrs_worked);
288: --
289: -- Prorate using hourly rate passed in as param:
290: --
291: /*
290: --
291: /*
292: IF UPPER(p_wsched) = 'NOT ENTERED' THEN
293:
294: hr_utility.set_location('Prorate_Earnings', 7);
295: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
296: p_range_start_date,
297: p_range_end_date,
298: p_asg_std_freq);
298: p_asg_std_freq);
299:
300: -- Keep running total of ACTUAL hours worked.
301:
302: hr_utility.set_location('Prorate_Earnings', 11);
303: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
304: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
305:
306: ELSE
300: -- Keep running total of ACTUAL hours worked.
301:
302: hr_utility.set_location('Prorate_Earnings', 11);
303: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
304: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
305:
306: ELSE
307:
308: hr_utility.set_location('Prorate_Earnings', 17);
304: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
305:
306: ELSE
307:
308: hr_utility.set_location('Prorate_Earnings', 17);
309:
310: -- Get work schedule name:
311: -- v_ws_id := to_number(p_wsched);
312: v_ws_id := fnd_number.canonical_to_number(p_wsched);
316: FROM pay_user_columns
317: WHERE user_column_id = v_ws_id
318: AND NVL(legislation_code,'CA') = 'CA';
319:
320: hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
321: hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
322:
323: hr_utility.set_location('calling core udfs', 44);
324: */
317: WHERE user_column_id = v_ws_id
318: AND NVL(legislation_code,'CA') = 'CA';
319:
320: hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
321: hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
322:
323: hr_utility.set_location('calling core udfs', 44);
324: */
325: v_hours_in_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
319:
320: hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
321: hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
322:
323: hr_utility.set_location('calling core udfs', 44);
324: */
325: v_hours_in_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
326: p_assignment_action_id
327: ,p_assignment_id
339: ,v_return_status
340: ,v_return_message);
341:
342: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
343: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
344:
345: -- Hours in date range via work schedule or std hours.
346: /* END IF;*/
347:
345: -- Hours in date range via work schedule or std hours.
346: /* END IF;*/
347:
348: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
349: hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
350: hr_utility.set_location('Prorate_Earnings', 97);
351: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
352: hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
353: RETURN v_prorated_earn;
346: /* END IF;*/
347:
348: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
349: hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
350: hr_utility.set_location('Prorate_Earnings', 97);
351: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
352: hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
353: RETURN v_prorated_earn;
354:
348: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
349: hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
350: hr_utility.set_location('Prorate_Earnings', 97);
351: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
352: hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
353: RETURN v_prorated_earn;
354:
355: END Prorate_Earnings;
356:
408: -- span entire period.
409: -- We know this select will return a row, otherwise there would be no
410: -- EEV changes to detect.
411: --
412: hr_utility.set_location('Prorate_EEV', 103);
413: SELECT EEV.screen_entry_value,
414: GREATEST(EEV.effective_start_date, p_range_start_date),
415: EEV.effective_end_date
416: INTO v_earnings_entry,
422: AND EEV.effective_start_date <= p_range_start_date
423: AND EEV.effective_end_date >= p_range_start_date
424: AND EEV.effective_end_date < p_range_end_date;
425: --
426: hr_utility.set_location('Prorate_EEV', 105);
427: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
428: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
429: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
430:
423: AND EEV.effective_end_date >= p_range_start_date
424: AND EEV.effective_end_date < p_range_end_date;
425: --
426: hr_utility.set_location('Prorate_EEV', 105);
427: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
428: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
429: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
430:
431: hr_utility.set_location('calling core udfs', 45);
424: AND EEV.effective_end_date < p_range_end_date;
425: --
426: hr_utility.set_location('Prorate_EEV', 105);
427: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
428: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
429: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
430:
431: hr_utility.set_location('calling core udfs', 45);
432: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
425: --
426: hr_utility.set_location('Prorate_EEV', 105);
427: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
428: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
429: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
430:
431: hr_utility.set_location('calling core udfs', 45);
432: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
433: p_bus_group_id
427: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
428: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
429: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
430:
431: hr_utility.set_location('calling core udfs', 45);
432: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
433: p_bus_group_id
434: ,p_assignment_id
435: ,p_pay_id
436: ,p_element_entry_id
437: ,p_date_earned
438: ,p_assignment_action_id );
439: --
440: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
441: --
442: hr_utility.set_location('Prorate_EEV', 107);
443: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
444: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
438: ,p_assignment_action_id );
439: --
440: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
441: --
442: hr_utility.set_location('Prorate_EEV', 107);
443: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
444: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
445:
446: v_eev_prorated_earnings := v_eev_prorated_earnings +
439: --
440: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
441: --
442: hr_utility.set_location('Prorate_EEV', 107);
443: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
444: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
445:
446: v_eev_prorated_earnings := v_eev_prorated_earnings +
447: Prorate_Earnings (
440: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
441: --
442: hr_utility.set_location('Prorate_EEV', 107);
443: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
444: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
445:
446: v_eev_prorated_earnings := v_eev_prorated_earnings +
447: Prorate_Earnings (
448: p_bg_id => p_bus_group_id,
457: p_range_start_date => v_entry_start,
458: p_range_end_date => v_entry_end,
459: p_act_hrs_worked => p_actual_hrs_worked);
460: --
461: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
462: --
463: -- SELECT (EEV2):
464: OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
465: LOOP
469: v_entry_start,
470: v_entry_end;
471: EXIT WHEN get_entry_chgs%NOTFOUND;
472: --
473: hr_utility.set_location('Prorate_EEV', 115);
474: --
475: -- For each range of dates found, add to running prorated earnings total.
476: --
477: hr_utility.set_location('Prorate_EEV', 117);
473: hr_utility.set_location('Prorate_EEV', 115);
474: --
475: -- For each range of dates found, add to running prorated earnings total.
476: --
477: hr_utility.set_location('Prorate_EEV', 117);
478:
479: hr_utility.set_location('calling core udfs', 46);
480: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
481: p_bus_group_id
475: -- For each range of dates found, add to running prorated earnings total.
476: --
477: hr_utility.set_location('Prorate_EEV', 117);
478:
479: hr_utility.set_location('calling core udfs', 46);
480: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
481: p_bus_group_id
482: ,p_assignment_id
483: ,p_pay_id
484: ,p_element_entry_id
485: ,p_date_earned
486: ,p_assignment_action_id );
487:
488: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
489: hr_utility.set_location('Prorate_EEV', 119);
490: --
491: v_eev_prorated_earnings := v_eev_prorated_earnings +
492: Prorate_Earnings (
485: ,p_date_earned
486: ,p_assignment_action_id );
487:
488: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
489: hr_utility.set_location('Prorate_EEV', 119);
490: --
491: v_eev_prorated_earnings := v_eev_prorated_earnings +
492: Prorate_Earnings (
493: p_bg_id => p_bus_group_id,
502: p_range_start_date => v_entry_start,
503: p_range_end_date => v_entry_end,
504: p_act_hrs_worked => p_actual_hrs_worked);
505: --
506: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
507: --
508: END LOOP;
509: --
510: CLOSE get_entry_chgs;
513: -- Select for SINGLE record that exists across Period End Date:
514: -- NOTE: Will only return a row if select (2) does not return a row where
515: -- Effective End Date = Period End Date !
516: --
517: hr_utility.set_location('Prorate_EEV', 141);
518: SELECT EEV.screen_entry_value,
519: EEV.effective_start_date,
520: LEAST(EEV.effective_end_date, p_range_end_date)
521: INTO v_earnings_entry,
527: AND EEV.effective_start_date > p_range_start_date
528: AND EEV.effective_start_date <= p_range_end_date
529: AND EEV.effective_end_date > p_range_end_date;
530: --
531: hr_utility.set_location('Prorate_EEV', 147);
532: hr_utility.set_location('calling core udfs', 47);
533: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
534: p_bus_group_id
535: ,p_assignment_id
528: AND EEV.effective_start_date <= p_range_end_date
529: AND EEV.effective_end_date > p_range_end_date;
530: --
531: hr_utility.set_location('Prorate_EEV', 147);
532: hr_utility.set_location('calling core udfs', 47);
533: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
534: p_bus_group_id
535: ,p_assignment_id
536: ,p_pay_id
537: ,p_element_entry_id
538: ,p_date_earned
539: ,p_assignment_action_id );
540:
541: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
542: hr_utility.set_location('Prorate_EEV', 151);
543:
544: v_eev_prorated_earnings := v_eev_prorated_earnings +
545: Prorate_Earnings (
538: ,p_date_earned
539: ,p_assignment_action_id );
540:
541: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
542: hr_utility.set_location('Prorate_EEV', 151);
543:
544: v_eev_prorated_earnings := v_eev_prorated_earnings +
545: Prorate_Earnings (
546: p_bg_id => p_bus_group_id,
555: p_range_start_date => v_entry_start,
556: p_range_end_date => v_entry_end,
557: p_act_hrs_worked => p_actual_hrs_worked);
558:
559: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
560: -- We're Done!
561: hr_utility.set_location('Prorate_EEV', 167);
562: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
563: p_hrly_rate := v_curr_hrly_rate;
557: p_act_hrs_worked => p_actual_hrs_worked);
558:
559: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
560: -- We're Done!
561: hr_utility.set_location('Prorate_EEV', 167);
562: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
563: p_hrly_rate := v_curr_hrly_rate;
564: RETURN v_eev_prorated_earnings;
565:
564: RETURN v_eev_prorated_earnings;
565:
566: EXCEPTION WHEN NO_DATA_FOUND THEN
567:
568: hr_utility.set_location('Prorate_EEV', 177);
569: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
570: p_hrly_rate := v_curr_hrly_rate;
571: RETURN v_eev_prorated_earnings;
572:
602: -- Need to loop thru all "Vacation Plans" and check for entries in the current
603: -- period for this assignment.
604:
605: BEGIN
606: hr_utility.set_location('get_vac_pay', 11);
607: OPEN get_vac_hours (p_asg_id, p_eff_date);
608: LOOP
609:
610: hr_utility.set_location('get_vac_pay', 13);
606: hr_utility.set_location('get_vac_pay', 11);
607: OPEN get_vac_hours (p_asg_id, p_eff_date);
608: LOOP
609:
610: hr_utility.set_location('get_vac_pay', 13);
611: FETCH get_vac_hours
612: INTO l_vac_hours;
613: EXIT WHEN get_vac_hours%NOTFOUND;
614:
615: p_vac_hours := p_vac_hours + l_vac_hours;
616:
617: END LOOP;
618: CLOSE get_vac_hours;
619: hr_utility.set_location('get_vac_pay', 15);
620:
621: IF p_vac_hours <> 0 THEN
622:
623: l_vac_pay := p_vac_hours * p_curr_rate;
657: -- Return immediately when no sick time has been taken.
658:
659: BEGIN
660:
661: hr_utility.set_location('get_sick_pay', 11);
662: OPEN get_sick_hours (p_asg_id, p_eff_date);
663: LOOP
664:
665: hr_utility.set_location('get_sick_pay', 13);
661: hr_utility.set_location('get_sick_pay', 11);
662: OPEN get_sick_hours (p_asg_id, p_eff_date);
663: LOOP
664:
665: hr_utility.set_location('get_sick_pay', 13);
666: FETCH get_sick_hours
667: INTO l_sick_hours;
668: EXIT WHEN get_sick_hours%NOTFOUND;
669:
670: p_sick_hours := p_sick_hours + l_sick_hours;
671:
672: END LOOP;
673: CLOSE get_sick_hours;
674: hr_utility.set_location('get_sick_pay', 15);
675:
676: IF p_sick_hours <> 0 THEN
677:
678: l_sick_pay := p_sick_hours * p_curr_rate;
715: -- input value id as appropriate (according to ele name).
716:
717: IF p_pay_basis IS NOT NULL THEN
718: begin
719: hr_utility.set_location('calculate_period_earnings', 10);
720: SELECT PYB.input_value_id,
721: FCL.meaning,
722: FCL.lookup_code
723: INTO v_inpval_id,
736: AND ASG.effective_end_date;
737:
738: EXCEPTION WHEN NO_DATA_FOUND THEN
739:
740: hr_utility.set_location('calculate_period_earnings', 11);
741: v_period_earn := 0;
742: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
743: RETURN v_period_earn;
744:
741: v_period_earn := 0;
742: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
743: RETURN v_period_earn;
744:
745: -- hr_utility.set_message(801, 'PAY_xxxx_NO_ASST_IN_PERIOD');
746: -- hr_utility.raise_error;
747:
748: END;
749:
742: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
743: RETURN v_period_earn;
744:
745: -- hr_utility.set_message(801, 'PAY_xxxx_NO_ASST_IN_PERIOD');
746: -- hr_utility.raise_error;
747:
748: END;
749:
750: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
748: END;
749:
750: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
751: begin
752: hr_utility.set_location('calculate_period_earnings', 13);
753: SELECT IPV.input_value_id
754: INTO v_inpval_id
755: FROM pay_input_values_f IPV,
756: pay_element_types_f ELT
762: --
763: v_pay_basis := 'HOURLY';
764: --
765: EXCEPTION WHEN NO_DATA_FOUND THEN
766: hr_utility.set_location('calculate_period_earnings', 14);
767: v_period_earn := 0;
768: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
769: RETURN v_period_earn;
770: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
766: hr_utility.set_location('calculate_period_earnings', 14);
767: v_period_earn := 0;
768: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
769: RETURN v_period_earn;
770: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
771: -- hr_utility.raise_error;
772: end;
773: --
774: ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
767: v_period_earn := 0;
768: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
769: RETURN v_period_earn;
770: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
771: -- hr_utility.raise_error;
772: end;
773: --
774: ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
775: begin
772: end;
773: --
774: ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
775: begin
776: hr_utility.set_location('calculate_period_earnings', 15);
777: SELECT IPV.input_value_id
778: INTO v_inpval_id
779: FROM pay_input_values_f IPV,
780: pay_element_types_f ELT
786: --
787: v_pay_basis := 'HOURLY';
788: --
789: EXCEPTION WHEN NO_DATA_FOUND THEN
790: hr_utility.set_location('calculate_period_earnings', 16);
791: v_period_earn := 0;
792: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
793: RETURN v_period_earn;
794: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
790: hr_utility.set_location('calculate_period_earnings', 16);
791: v_period_earn := 0;
792: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
793: RETURN v_period_earn;
794: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
795: -- hr_utility.raise_error;
796: end;
797: --
798: ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
791: v_period_earn := 0;
792: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
793: RETURN v_period_earn;
794: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
795: -- hr_utility.raise_error;
796: end;
797: --
798: ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
799: begin
796: end;
797: --
798: ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
799: begin
800: hr_utility.set_location('calculate_period_earnings', 17);
801: SELECT IPV.input_value_id
802: INTO v_inpval_id
803: FROM pay_input_values_f IPV,
804: pay_element_types_f ELT
810:
811: v_pay_basis := 'MONTHLY';
812:
813: EXCEPTION WHEN NO_DATA_FOUND THEN
814: hr_utility.set_location('calculate_period_earnings', 18);
815: v_period_earn := 0;
816: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
817: RETURN v_period_earn;
818: -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
814: hr_utility.set_location('calculate_period_earnings', 18);
815: v_period_earn := 0;
816: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
817: RETURN v_period_earn;
818: -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
819: -- hr_utility.raise_error;
820: END;
821:
822: END IF;
815: v_period_earn := 0;
816: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
817: RETURN v_period_earn;
818: -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
819: -- hr_utility.raise_error;
820: END;
821:
822: END IF;
823:
855: -- Find # of payroll period types per fiscal year:
856:
857: begin
858:
859: hr_utility.set_location('calculate_period_earnings', 40);
860: SELECT TPT.number_per_fiscal_year
861: INTO v_pay_periods_per_year
862: FROM pay_payrolls_f PRL,
863: per_time_period_types TPT
868: AND PRL.business_group_id + 0 = p_bus_grp_id;
869:
870: exception when NO_DATA_FOUND then
871:
872: hr_utility.set_location('calculate_period_earnings', 41);
873: v_period_earn := 0;
874: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
875: RETURN v_period_earn;
876: -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
872: hr_utility.set_location('calculate_period_earnings', 41);
873: v_period_earn := 0;
874: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
875: RETURN v_period_earn;
876: -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
877: -- hr_utility.raise_error;
878:
879: end;
880:
873: v_period_earn := 0;
874: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
875: RETURN v_period_earn;
876: -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
877: -- hr_utility.raise_error;
878:
879: end;
880:
881: /*
890: -- even when using work schedule - ie. need to find ACTUAL scheduled hours, not
891: -- actual hours for a week, converted to a period figure.
892: */
893: --
894: hr_utility.set_location('calculate_period_earnings', 45);
895: /* IF p_work_schedule <> 'NOT ENTERED' THEN
896: --
897: -- Find hours worked between period start and end dates.
898: --
908: AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
909: AND NVL(legislation_code,'CA') = 'CA';
910: --
911: */
912: hr_utility.set_location('calling core udfs', 45);
913: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
914: p_assignment_action_id
915: ,p_asst_id
916: ,p_bus_grp_id
941: -- Convert HOURLY earnings to PERIOD earnings.
942: -- Passing "NULL" freq to Convert_Period_Type will convert
943: -- to/from the payroll time period type.
944:
945: hr_utility.set_location('calculate_period_earnings', 46);
946: v_period_earn := Convert_Period_Type( p_bus_grp_id,
947: p_payroll_id,
948: p_assignment_action_id,
949: p_asst_id ,
957: p_period_start,
958: p_period_end,
959: p_asst_std_freq);
960:
961: hr_utility.set_location('calculate_period_earnings', 47);
962:
963: p_actual_hours_worked := v_hrs_per_range;
964: hr_utility.set_location('Calc_PE actual_hours_worked = ', p_actual_hours_worked);
965:
960:
961: hr_utility.set_location('calculate_period_earnings', 47);
962:
963: p_actual_hours_worked := v_hrs_per_range;
964: hr_utility.set_location('Calc_PE actual_hours_worked = ', p_actual_hours_worked);
965:
966: -- Check that Pro-rate = 'Y' before continuing.
967:
968: IF p_prorate = 'N' THEN
967:
968: IF p_prorate = 'N' THEN
969:
970: -- Done!!! No pro-ration...
971: hr_utility.set_location('calculate_period_earnings', 49);
972: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
973:
974: RETURN v_period_earn;
975:
982: -- (ASG1) Select for SINGLE record that includes Period Start Date but does not
983: -- span entire period. If no row returned, then ASG record spans period
984: -- and there is no need to run selects (ASG2) or (ASG3).
985:
986: hr_utility.set_location('calculate_period_earnings', 51);
987:
988: /* ************************************************************** */
989:
990: begin
1018: -- for future reference:
1019:
1020: l_mid_period_asg_change := TRUE;
1021:
1022: hr_utility.set_location('calculate_period_earnings', 56);
1023:
1024: begin
1025:
1026: select 1 INTO l_eev_info_changes
1064: -- (ASG1.1)
1065: -- Actually need to select for ASG record that STARTS within period
1066: -- ie. becomes active on a date later than start date!
1067:
1068: hr_utility.set_location('calculate_period_earnings', 52);
1069:
1070: begin
1071:
1072: select 1 INTO l_asg_info_changes
1090:
1091: -- Need to prorate b/c of mid period ASG changes, but let's look for
1092: -- EEV changes for future reference:
1093:
1094: hr_utility.set_location('calculate_period_earnings', 55);
1095:
1096: begin
1097:
1098: select 1 INTO l_eev_info_changes
1137: -- and we can stop now!
1138:
1139: begin
1140:
1141: hr_utility.set_location('calculate_period_earnings', 53);
1142:
1143: select 1 INTO l_eev_info_changes
1144: from dual
1145: where exists (
1282: -- you would not have an assignment record to 'Process' that spans the
1283: -- Start Date of the period!
1284:
1285: p_actual_hours_worked := 0;
1286: hr_utility.set_location('calculate_period_earnings', 70);
1287: -- IF NOT l_mid_period_asg_change THEN
1288:
1289: begin
1290:
1287: -- IF NOT l_mid_period_asg_change THEN
1288:
1289: begin
1290:
1291: hr_utility.set_location('calculate_period_earnings', 71);
1292: SELECT GREATEST(ASG.effective_start_date, p_period_start),
1293: ASG.effective_end_date,
1294: NVL(ASG.NORMAL_HOURS, 0),
1295: NVL(HRL.meaning, 'NOT ENTERED'),
1326:
1327: -- Prorate using hourly rate passed in as param:
1328: -- Should this become a function from here...
1329:
1330: hr_utility.set_location('calculate_period_earnings', 72);
1331: v_prorated_earnings := v_prorated_earnings +
1332: Prorate_Earnings (
1333: p_bg_id => p_bus_grp_id,
1334: p_assignment_id => p_asst_id,
1342: p_range_start_date => v_range_start,
1343: p_range_end_date => v_range_end,
1344: p_act_hrs_worked => p_actual_hours_worked);
1345:
1346: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1347:
1348: -- Just in case mid period change does not span Start of Period!
1349:
1350: EXCEPTION WHEN NO_DATA_FOUND THEN
1359: -- including Period End Date.
1360: -- Not BETWEEN Period Start/End since we already found a record including
1361: -- Start Date in select (1) above.
1362:
1363: hr_utility.set_location('calculate_period_earnings', 77);
1364: OPEN get_asst_chgs; -- SELECT (ASG2)
1365: LOOP
1366:
1367: FETCH get_asst_chgs
1372: v_asst_std_freq_code,
1373: v_work_schedule;
1374: EXIT WHEN get_asst_chgs%NOTFOUND;
1375:
1376: hr_utility.set_location('calculate_period_earnings', 79);
1377:
1378: -- For each range of dates found, add to running prorated earnings total.
1379:
1380: hr_utility.set_location('calculate_period_earnings', 81);
1376: hr_utility.set_location('calculate_period_earnings', 79);
1377:
1378: -- For each range of dates found, add to running prorated earnings total.
1379:
1380: hr_utility.set_location('calculate_period_earnings', 81);
1381: v_prorated_earnings := v_prorated_earnings +
1382: Prorate_Earnings (
1383: p_bg_id => p_bus_grp_id,
1384: p_assignment_id => p_asst_id,
1392: p_range_start_date => v_range_start,
1393: p_range_end_date => v_range_end,
1394: p_act_hrs_worked => p_actual_hours_worked);
1395:
1396: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1397:
1398: END LOOP;
1399:
1400: CLOSE get_asst_chgs;
1405: -- Effective End Date = Period End Date !
1406:
1407: begin
1408:
1409: hr_utility.set_location('calculate_period_earnings', 89);
1410: SELECT ASG.effective_start_date,
1411: LEAST(ASG.effective_end_date, p_period_end),
1412: NVL(ASG.normal_hours, 0),
1413: NVL(HRL.meaning, 'NOT ENTERED'),
1441: -- Changing ASG status check to be for Payroll Status of 'Process'
1442: -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1443: -- AND AST.pay_system_status = 'P'
1444:
1445: hr_utility.set_location('calculate_period_earnings', 91);
1446: v_prorated_earnings := v_prorated_earnings +
1447: Prorate_Earnings (
1448: p_bg_id => p_bus_grp_id,
1449: p_assignment_id => p_asst_id,
1457: p_range_start_date => v_range_start,
1458: p_range_end_date => v_range_end,
1459: p_act_hrs_worked => p_actual_hours_worked);
1460:
1461: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1462:
1463: -- We're done!
1464:
1465: hr_utility.set_location('calculate_period_earnings', 101);
1461: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1462:
1463: -- We're done!
1464:
1465: hr_utility.set_location('calculate_period_earnings', 101);
1466:
1467: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1468: RETURN v_prorated_earnings;
1469:
1468: RETURN v_prorated_earnings;
1469:
1470: EXCEPTION WHEN NO_DATA_FOUND THEN
1471: -- (ASG3) returned no rows, but we're done anyway!
1472: hr_utility.set_location('calculate_period_earnings', 102);
1473:
1474: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1475: RETURN v_prorated_earnings;
1476:
1477: END;
1478:
1479: ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
1480:
1481: hr_utility.set_location('calculate_period_earnings', 103);
1482: p_actual_hours_worked := 0;
1483: v_prorated_earnings := v_prorated_earnings +
1484: Prorate_EEV (
1485: p_bus_group_id => p_bus_grp_id,
1498: p_element_entry_id => p_ele_entry_id,
1499: p_inpval_id => v_inpval_id);
1500:
1501: -- We're Done!
1502: hr_utility.set_location('calculate_period_earnings', 127);
1503:
1504: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1505: RETURN v_prorated_earnings;
1506:
1526: -- NOT! v_curr_hrly_rate := p_ass_hrly_figure;
1527:
1528: begin
1529:
1530: hr_utility.set_location('calculate_period_earnings', 128);
1531: SELECT EEV.screen_entry_value
1532: INTO v_earnings_entry
1533: FROM pay_element_entry_values_f EEV
1534: WHERE EEV.element_entry_id = p_ele_entry_id
1571: AND SCL.enabled_flag = 'Y'
1572: AND HRL.lookup_code(+) = ASG.frequency
1573: AND HRL.lookup_type(+) = 'FREQUENCY';
1574:
1575: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1576: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1577: hr_utility.set_location('calling core udfs', 48);
1578: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1579: p_bus_grp_id
1572: AND HRL.lookup_code(+) = ASG.frequency
1573: AND HRL.lookup_type(+) = 'FREQUENCY';
1574:
1575: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1576: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1577: hr_utility.set_location('calling core udfs', 48);
1578: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1579: p_bus_grp_id
1580: ,p_asst_id
1573: AND HRL.lookup_type(+) = 'FREQUENCY';
1574:
1575: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1576: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1577: hr_utility.set_location('calling core udfs', 48);
1578: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1579: p_bus_grp_id
1580: ,p_asst_id
1581: ,p_payroll_id
1582: ,p_ele_entry_id
1583: ,p_date_earned
1584: ,p_assignment_action_id );
1585:
1586: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1587: hr_utility.set_location('calculate_period_earnings', 130);
1588: -- IF NOT l_mid_period_asg_change THEN
1589:
1590: -- Do not have to do this first bit if Mid period hire/active asg occurs:
1583: ,p_date_earned
1584: ,p_assignment_action_id );
1585:
1586: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1587: hr_utility.set_location('calculate_period_earnings', 130);
1588: -- IF NOT l_mid_period_asg_change THEN
1589:
1590: -- Do not have to do this first bit if Mid period hire/active asg occurs:
1591:
1596:
1597: -- (EEV1) Select for SINGLE record that includes Period Start Date but
1598: -- does not span entire period.
1599:
1600: hr_utility.set_location('calculate_period_earnings', 132);
1601: SELECT COUNT(EEV.element_entry_value_id)
1602: INTO l_eev_info_changes
1603: FROM pay_element_entry_values_f EEV
1604: WHERE EEV.element_entry_id = p_ele_entry_id
1608: AND EEV.effective_end_date < v_range_end;
1609:
1610: IF l_eev_info_changes = 0 THEN
1611: -- Prorate using latest hourly rate:
1612: hr_utility.set_location('calculate_period_earnings', 128);
1613: SELECT EEV.screen_entry_value
1614: INTO v_earnings_entry
1615: FROM pay_element_entry_values_f EEV
1616: WHERE EEV.element_entry_id = p_ele_entry_id
1617: AND EEV.input_value_id = v_inpval_id
1618: AND v_range_end BETWEEN EEV.effective_start_date
1619: AND EEV.effective_end_date;
1620:
1621: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1622: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1623: hr_utility.set_location('calling core udfs', 49);
1624: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1625: p_bus_grp_id
1618: AND v_range_end BETWEEN EEV.effective_start_date
1619: AND EEV.effective_end_date;
1620:
1621: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1622: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1623: hr_utility.set_location('calling core udfs', 49);
1624: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1625: p_bus_grp_id
1626: ,p_asst_id
1619: AND EEV.effective_end_date;
1620:
1621: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1622: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1623: hr_utility.set_location('calling core udfs', 49);
1624: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1625: p_bus_grp_id
1626: ,p_asst_id
1627: ,p_payroll_id
1628: ,p_ele_entry_id
1629: ,p_date_earned
1630: ,p_assignment_action_id );
1631:
1632: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1633: -- Should this become a function from here...
1634: hr_utility.set_location('calculate_period_earnings', 133);
1635: v_prorated_earnings := v_prorated_earnings +
1636: Prorate_Earnings (
1630: ,p_assignment_action_id );
1631:
1632: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1633: -- Should this become a function from here...
1634: hr_utility.set_location('calculate_period_earnings', 133);
1635: v_prorated_earnings := v_prorated_earnings +
1636: Prorate_Earnings (
1637: p_bg_id => p_bus_grp_id,
1638: p_assignment_id => p_asst_id,
1646: p_range_start_date => v_range_start,
1647: p_range_end_date => v_range_end,
1648: p_act_hrs_worked => p_actual_hours_worked);
1649:
1650: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1651:
1652: ELSE
1653: -- Do proration for this ASG range by EEV !
1654:
1651:
1652: ELSE
1653: -- Do proration for this ASG range by EEV !
1654:
1655: hr_utility.set_location('calculate_period_earnings', 134);
1656: v_prorated_earnings := v_prorated_earnings +
1657: Prorate_EEV (
1658: p_bus_group_id => p_bus_grp_id,
1659: p_assignment_id => p_asst_id,
1685: -- including Period End Date.
1686: -- Not BETWEEN Period Start/End since we already found a record including
1687: -- Start Date in select (1) above.
1688:
1689: hr_utility.set_location('calculate_period_earnings', 135);
1690: OPEN get_asst_chgs; -- SELECT (ASG2)
1691: LOOP
1692:
1693: FETCH get_asst_chgs
1698: v_asst_std_freq_code,
1699: v_work_schedule;
1700: EXIT WHEN get_asst_chgs%NOTFOUND;
1701:
1702: hr_utility.set_location('calculate_period_earnings', 79);
1703:
1704: -- For each range of dates found, add to running prorated earnings total.
1705:
1706: -- Check for EEV changes, if also none -> return v_period_earn and done:
1708: -- does not span entire period. If no row returned, then EEV record
1709: -- spans period and there is no need to run selects (EEV2) or (EEV3)
1710: -- and we can stop now!
1711:
1712: hr_utility.set_location('calculate_period_earnings', 133);
1713: SELECT COUNT(EEV.element_entry_value_id)
1714: INTO l_eev_info_changes
1715: FROM pay_element_entry_values_f EEV
1716: WHERE EEV.element_entry_id = p_ele_entry_id
1720: AND EEV.effective_end_date < v_range_end;
1721:
1722: IF l_eev_info_changes = 0 THEN
1723: -- Prorate using latest hourly rate:
1724: hr_utility.set_location('calculate_period_earnings', 128);
1725: SELECT EEV.screen_entry_value
1726: INTO v_earnings_entry
1727: FROM pay_element_entry_values_f EEV
1728: WHERE EEV.element_entry_id = p_ele_entry_id
1729: AND EEV.input_value_id = v_inpval_id
1730: AND v_range_end BETWEEN EEV.effective_start_date
1731: AND EEV.effective_end_date;
1732: --
1733: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1734: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1735:
1736: hr_utility.set_location('calling core udfs', 50);
1737: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1730: AND v_range_end BETWEEN EEV.effective_start_date
1731: AND EEV.effective_end_date;
1732: --
1733: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1734: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1735:
1736: hr_utility.set_location('calling core udfs', 50);
1737: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1738: p_bus_grp_id
1732: --
1733: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1734: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1735:
1736: hr_utility.set_location('calling core udfs', 50);
1737: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1738: p_bus_grp_id
1739: ,p_asst_id
1740: ,p_payroll_id
1741: ,p_ele_entry_id
1742: ,p_date_earned
1743: ,p_assignment_action_id );
1744: --
1745: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1746: hr_utility.set_location('calculate_period_earnings', 91);
1747: v_prorated_earnings := v_prorated_earnings +
1748: Prorate_Earnings (
1749: p_bg_id => p_bus_grp_id,
1742: ,p_date_earned
1743: ,p_assignment_action_id );
1744: --
1745: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1746: hr_utility.set_location('calculate_period_earnings', 91);
1747: v_prorated_earnings := v_prorated_earnings +
1748: Prorate_Earnings (
1749: p_bg_id => p_bus_grp_id,
1750: p_assignment_id => p_asst_id,
1758: p_range_start_date => v_range_start,
1759: p_range_end_date => v_range_end,
1760: p_act_hrs_worked => p_actual_hours_worked);
1761: --
1762: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1763: --
1764: ELSE
1765: -- Do proration for this ASG range by EEV !
1766: --
1794: -- Effective End Date = Period End Date !
1795:
1796: begin
1797:
1798: hr_utility.set_location('calculate_period_earnings', 129);
1799: SELECT ASG.effective_start_date,
1800: LEAST(ASG.effective_end_date, p_period_end),
1801: NVL(ASG.normal_hours, 0),
1802: NVL(HRL.meaning, 'NOT ENTERED'),
1837: -- spans period and there is no need to run selects (EEV2) or (EEV3)
1838: -- and we can stop now! Remember, if eev spans period then get
1839: -- eev.screen_entry_value that spans period (ie latest hrly rate).
1840:
1841: hr_utility.set_location('calculate_period_earnings', 133);
1842: SELECT COUNT(EEV.element_entry_value_id)
1843: INTO l_eev_info_changes
1844: FROM pay_element_entry_values_f EEV
1845: WHERE EEV.element_entry_id = p_ele_entry_id
1849: AND EEV.effective_end_date < v_range_end;
1850:
1851: IF l_eev_info_changes = 0 THEN
1852: -- Prorate using latest hourly rate (ie. rate as of end of period):
1853: hr_utility.set_location('calculate_period_earnings', 128);
1854: SELECT EEV.screen_entry_value
1855: INTO v_earnings_entry
1856: FROM pay_element_entry_values_f EEV
1857: WHERE EEV.element_entry_id = p_ele_entry_id
1858: AND EEV.input_value_id = v_inpval_id
1859: AND v_range_end BETWEEN EEV.effective_start_date
1860: AND EEV.effective_end_date;
1861:
1862: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1863: hr_utility.set_location('calling core udfs', 51);
1864: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1865: p_bus_grp_id
1866: ,p_asst_id
1859: AND v_range_end BETWEEN EEV.effective_start_date
1860: AND EEV.effective_end_date;
1861:
1862: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1863: hr_utility.set_location('calling core udfs', 51);
1864: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1865: p_bus_grp_id
1866: ,p_asst_id
1867: ,p_payroll_id
1868: ,p_ele_entry_id
1869: ,p_date_earned
1870: ,p_assignment_action_id );
1871:
1872: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1873: hr_utility.set_location('calculate_period_earnings', 130);
1874:
1875: hr_utility.set_location('calculate_period_earnings', 137);
1876: v_prorated_earnings := v_prorated_earnings +
1869: ,p_date_earned
1870: ,p_assignment_action_id );
1871:
1872: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1873: hr_utility.set_location('calculate_period_earnings', 130);
1874:
1875: hr_utility.set_location('calculate_period_earnings', 137);
1876: v_prorated_earnings := v_prorated_earnings +
1877: Prorate_Earnings (
1871:
1872: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1873: hr_utility.set_location('calculate_period_earnings', 130);
1874:
1875: hr_utility.set_location('calculate_period_earnings', 137);
1876: v_prorated_earnings := v_prorated_earnings +
1877: Prorate_Earnings (
1878: p_bg_id => p_bus_grp_id,
1879: p_assignment_id => p_asst_id,
1887: p_range_start_date => v_range_start,
1888: p_range_end_date => v_range_end,
1889: p_act_hrs_worked => p_actual_hours_worked);
1890:
1891: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1892:
1893: ELSE
1894: -- Do proration for this ASG range by EEV !
1895:
1892:
1893: ELSE
1894: -- Do proration for this ASG range by EEV !
1895:
1896: hr_utility.set_location('calculate_period_earnings', 139);
1897: v_prorated_earnings := v_prorated_earnings +
1898: Prorate_EEV (
1899: p_bus_group_id => p_bus_grp_id,
1900: p_assignment_id => p_asst_id,
1915: END IF;
1916:
1917: -- We're done!
1918:
1919: hr_utility.set_location('calculate_period_earnings', 141);
1920:
1921: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1922: RETURN v_prorated_earnings;
1923:
1922: RETURN v_prorated_earnings;
1923:
1924: EXCEPTION WHEN NO_DATA_FOUND THEN
1925: -- (ASG3.2) returned no rows, but we're done anyway!
1926: hr_utility.set_location('calculate_period_earnings', 142);
1927:
1928: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1929: RETURN v_prorated_earnings;
1930:
1933: END IF;
1934:
1935: EXCEPTION
1936: WHEN NO_DATA_FOUND THEN
1937: hr_utility.set_location('calculate_period_earnings', 190);
1938:
1939: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1940:
1941: RETURN v_prorated_earnings;
1963:
1964: BEGIN -- standard_hours_worked
1965:
1966: -- Check for valid range
1967: hr_utility.set_location('standard_hours_worked', 5);
1968: IF p_range_start > p_range_end THEN
1969: hr_utility.set_location('standard_hours_worked', 7);
1970: RETURN v_total_hours;
1971: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1965:
1966: -- Check for valid range
1967: hr_utility.set_location('standard_hours_worked', 5);
1968: IF p_range_start > p_range_end THEN
1969: hr_utility.set_location('standard_hours_worked', 7);
1970: RETURN v_total_hours;
1971: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1972: -- hr_utility.raise_error;
1973: END IF;
1967: hr_utility.set_location('standard_hours_worked', 5);
1968: IF p_range_start > p_range_end THEN
1969: hr_utility.set_location('standard_hours_worked', 7);
1970: RETURN v_total_hours;
1971: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1972: -- hr_utility.raise_error;
1973: END IF;
1974: --
1975:
1968: IF p_range_start > p_range_end THEN
1969: hr_utility.set_location('standard_hours_worked', 7);
1970: RETURN v_total_hours;
1971: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1972: -- hr_utility.raise_error;
1973: END IF;
1974: --
1975:
1976: IF UPPER(p_std_freq) = 'W' THEN
1984: END IF;
1985:
1986: v_curr_date := p_range_start;
1987:
1988: hr_utility.set_location('standard_hours_worked', 10);
1989:
1990: hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1991: hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1992: LOOP
1986: v_curr_date := p_range_start;
1987:
1988: hr_utility.set_location('standard_hours_worked', 10);
1989:
1990: hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1991: hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1992: LOOP
1993:
1994: v_day_no := TO_CHAR(v_curr_date, 'D');
1987:
1988: hr_utility.set_location('standard_hours_worked', 10);
1989:
1990: hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1991: hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1992: LOOP
1993:
1994: v_day_no := TO_CHAR(v_curr_date, 'D');
1995:
1992: LOOP
1993:
1994: v_day_no := TO_CHAR(v_curr_date, 'D');
1995:
1996: hr_utility.set_location('standard_hours_worked', 15);
1997:
1998: IF v_day_no > 1 and v_day_no < 7 then
1999:
2000: v_total_hours := v_total_hours + v_wrkday_hours;
1997:
1998: IF v_day_no > 1 and v_day_no < 7 then
1999:
2000: v_total_hours := v_total_hours + v_wrkday_hours;
2001: hr_utility.set_location('standard_hours_worked v_total_hours = ', v_total_hours);
2002: END IF;
2003: v_curr_date := v_curr_date + 1;
2004: EXIT WHEN v_curr_date > p_range_end;
2005: END LOOP;
2003: v_curr_date := v_curr_date + 1;
2004: EXIT WHEN v_curr_date > p_range_end;
2005: END LOOP;
2006: --
2007: hr_utility.set_location('v_total_hours is', to_number(v_total_hours));
2008: RETURN v_total_hours;
2009: --
2010: END standard_hours_worked;
2011: --
2078: -- Selecting "count" because we want to continue processing even if
2079: -- the from_freq is not a pay basis.
2080: --
2081:
2082: --hr_utility.trace_on('Y', 'ORACLE');
2083: hr_utility.set_location('Get_Annualizing_Factor', 1);
2084:
2085: begin -- Is Freq pay basis?
2086:
2079: -- the from_freq is not a pay basis.
2080: --
2081:
2082: --hr_utility.trace_on('Y', 'ORACLE');
2083: hr_utility.set_location('Get_Annualizing_Factor', 1);
2084:
2085: begin -- Is Freq pay basis?
2086:
2087: --
2088: -- Decode pay basis and set v_annualizing_factor accordingly.
2089: -- PAY_BASIS "Meaning" is passed from FF !
2090: --
2091:
2092: hr_utility.set_location('Get_Annualizing_Factor', 13);
2093:
2094: SELECT lookup_code
2095: INTO v_pay_basis
2096: FROM hr_lookups lkp
2099: AND lkp.lookup_code = p_freq;
2100:
2101: v_pay_basis := p_freq;
2102:
2103: hr_utility.trace('v_pay_basis is'|| v_pay_basis);
2104: hr_utility.set_location('Get_Annualizing_Factor', 15);
2105: v_use_pay_basis := 1;
2106:
2107: IF v_pay_basis = 'MONTHLY' THEN
2100:
2101: v_pay_basis := p_freq;
2102:
2103: hr_utility.trace('v_pay_basis is'|| v_pay_basis);
2104: hr_utility.set_location('Get_Annualizing_Factor', 15);
2105: v_use_pay_basis := 1;
2106:
2107: IF v_pay_basis = 'MONTHLY' THEN
2108:
2105: v_use_pay_basis := 1;
2106:
2107: IF v_pay_basis = 'MONTHLY' THEN
2108:
2109: hr_utility.set_location('Get_Annualizing_Factor', 17);
2110: v_annualizing_factor := 12;
2111:
2112: ELSIF v_pay_basis = 'HOURLY' THEN
2113:
2110: v_annualizing_factor := 12;
2111:
2112: ELSIF v_pay_basis = 'HOURLY' THEN
2113:
2114: hr_utility.set_location('Get_Annualizing_Factor', 19);
2115:
2116: IF p_period_start_date IS NOT NULL THEN
2117: v_range_start := p_period_start_date;
2118: v_range_end := p_period_end_date;
2137: WHERE user_column_id = v_ws_id
2138: AND NVL(business_group_id, p_bg) = p_bg
2139: AND NVL(legislation_code,'CA') = 'CA';
2140:
2141: hr_utility.set_location('Get_Annualizing_Factor', 21);
2142: */
2143:
2144: hr_utility.set_location('calling core udfs', 52);
2145: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2140:
2141: hr_utility.set_location('Get_Annualizing_Factor', 21);
2142: */
2143:
2144: hr_utility.set_location('calling core udfs', 52);
2145: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2146: p_assignment_action_id
2147: ,p_assignment_id
2148: ,p_bg
2160: ,v_return_message);
2161:
2162: /* ELSE-- Hourly emp using Standard Hours on asg.
2163:
2164: hr_utility.set_location('Get_Annualizing_Factor', 23);
2165:
2166:
2167: v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2168: v_range_start,
2187: v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2188:
2189: ELSE
2190:
2191: hr_utility.set_location('Get_Annualizing_Factor', 230000);
2192: v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2193:
2194: END IF;
2195:
2194: END IF;
2195:
2196: ELSIF v_pay_basis = 'PERIOD' THEN
2197:
2198: hr_utility.set_location('Get_Annualizing_Factor', 25);
2199:
2200: SELECT TPT.number_per_fiscal_year
2201: INTO v_annualizing_factor
2202: FROM pay_payrolls_f PRL,
2208: AND PRL.effective_end_date
2209: AND PRL.payroll_id = p_payroll
2210: AND PRL.business_group_id + 0 = p_bg;
2211:
2212: hr_utility.set_location('Get_Annualizing_Factor', 27);
2213:
2214: ELSIF v_pay_basis = 'ANNUAL' THEN
2215:
2216: hr_utility.set_location('Get_Annualizing_Factor', 97);
2212: hr_utility.set_location('Get_Annualizing_Factor', 27);
2213:
2214: ELSIF v_pay_basis = 'ANNUAL' THEN
2215:
2216: hr_utility.set_location('Get_Annualizing_Factor', 97);
2217:
2218: v_annualizing_factor := 1;
2219:
2220: ELSE
2221:
2222: -- Did not recognize "pay basis", return -999 as annualizing factor.
2223: -- Remember this for debugging when zeroes come out as results!!!
2224:
2225: hr_utility.set_location('Get_Annualizing_Factor', 99);
2226:
2227: v_annualizing_factor := 0;
2228: RETURN v_annualizing_factor;
2229:
2232: exception
2233:
2234: WHEN NO_DATA_FOUND THEN
2235:
2236: hr_utility.set_location('Get_Annualizing_Factor', 101);
2237: v_use_pay_basis := 0;
2238:
2239: end;
2240:
2247: (UPPER(p_freq) = 'NOT ENTERED') THEN
2248:
2249: -- Get "annuallizing factor" from period type of the payroll.
2250:
2251: hr_utility.set_location('Get_Annualizing_Factor', 20);
2252:
2253: SELECT TPT.number_per_fiscal_year
2254: INTO v_annualizing_factor
2255: FROM pay_payrolls_f PRL,
2261: AND PRL.effective_end_date
2262: AND PRL.payroll_id = p_payroll
2263: AND PRL.business_group_id + 0 = p_bg;
2264:
2265: hr_utility.set_location('Get_Annualizing_Factor', 22);
2266:
2267: ELSIF UPPER(p_freq) <> 'HOURLY' THEN
2268:
2269: -- Not hourly, an actual time period type!
2269: -- Not hourly, an actual time period type!
2270:
2271: begin
2272:
2273: hr_utility.set_location('Get_Annualizing_Factor',24);
2274:
2275: SELECT PT.number_per_fiscal_year
2276: INTO v_annualizing_factor
2277: FROM per_time_period_types PT
2280: /* changed for bug 5155854
2281: decode(UPPER(p_freq),'W','WEEK','M','MONTH','D','DAY','Y','YEAR','H','HOUR');
2282: */
2283:
2284: hr_utility.set_location('Get_Annualizing_Factor',26);
2285:
2286: exception when NO_DATA_FOUND then
2287:
2288: -- Added as part of SALLY CLEANUP.
2288: -- Added as part of SALLY CLEANUP.
2289: -- Could have been passed in an ASG_FREQ dbi which might have the values of
2290: -- 'Day' or 'Month' which do not map to a time period type. So we'll do these by hand.
2291:
2292: hr_utility.set_location('Get_Annualizing_Factor',27);
2293: IF UPPER(p_freq) = 'DAY' THEN /* changed D to DAY and M to Month for bug 5155854 */
2294: v_annualizing_factor := c_days_per_year;
2295: ELSIF UPPER(p_freq) = 'MONTH' THEN
2296: v_annualizing_factor := c_months_per_year;
2299: end;
2300:
2301: ELSE -- Hourly employee...
2302:
2303: hr_utility.set_location('Get_Annualizing_Factor', 28);
2304:
2305: IF p_period_start_date IS NOT NULL THEN
2306: v_range_start := p_period_start_date;
2307: v_range_end := p_period_end_date;
2326: WHERE user_column_id = v_ws_id
2327: AND NVL(business_group_id, p_bg) = p_bg
2328: AND NVL(legislation_code,'CA') = 'CA';
2329:
2330: hr_utility.set_location('Get_Annualizing_Factor',30);
2331:
2332: hr_utility.set_location('calling core udfs', 53);
2333: */
2334: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2328: AND NVL(legislation_code,'CA') = 'CA';
2329:
2330: hr_utility.set_location('Get_Annualizing_Factor',30);
2331:
2332: hr_utility.set_location('calling core udfs', 53);
2333: */
2334: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2335: p_assignment_action_id
2336: ,p_assignment_id
2349: ,v_return_message);
2350:
2351: /* ELSE-- Hourly emp using Standard Hours on asg.
2352:
2353: hr_utility.set_location('Get_Annualizing_Factor', 23);
2354:
2355:
2356: v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2357: v_range_start,
2384: END IF;
2385:
2386: END IF; -- (v_use_pay_basis = 0)
2387:
2388: hr_utility.set_location('v_annualizing_factor is', to_number(v_annualizing_factor));
2389: RETURN v_annualizing_factor;
2390:
2391: END Get_Annualizing_Factor;
2392:
2392:
2393:
2394: BEGIN -- Convert Figure
2395:
2396: --hr_utility.trace_on('Y', 'ORACLE');
2397: hr_utility.set_location('Convert_Period_Type', 10);
2398:
2399: --
2400: -- If From_Freq and To_Freq are the same, then we're done.
2393:
2394: BEGIN -- Convert Figure
2395:
2396: --hr_utility.trace_on('Y', 'ORACLE');
2397: hr_utility.set_location('Convert_Period_Type', 10);
2398:
2399: --
2400: -- If From_Freq and To_Freq are the same, then we're done.
2401: --
2405: RETURN p_figure;
2406:
2407: END IF;
2408:
2409: hr_utility.set_location('Mita a trace1 ',30000);
2410:
2411: hr_utility.trace('v_from_freq is'|| p_from_freq);
2412: hr_utility.trace('v_to_freq is'|| p_to_freq);
2413:
2407: END IF;
2408:
2409: hr_utility.set_location('Mita a trace1 ',30000);
2410:
2411: hr_utility.trace('v_from_freq is'|| p_from_freq);
2412: hr_utility.trace('v_to_freq is'|| p_to_freq);
2413:
2414: v_from_annualizing_factor := Get_Annualizing_Factor(
2415: p_bg => p_bus_grp_id,
2408:
2409: hr_utility.set_location('Mita a trace1 ',30000);
2410:
2411: hr_utility.trace('v_from_freq is'|| p_from_freq);
2412: hr_utility.trace('v_to_freq is'|| p_to_freq);
2413:
2414: v_from_annualizing_factor := Get_Annualizing_Factor(
2415: p_bg => p_bus_grp_id,
2416: p_payroll => p_payroll_id,
2430: --
2431: -- Annualize "Figure" and convert to To_Freq.
2432: --
2433:
2434: hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2435: hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2436: hr_utility.set_location('Convert_Period_Type', 170);
2437:
2438: IF v_to_annualizing_factor = 0 OR
2431: -- Annualize "Figure" and convert to To_Freq.
2432: --
2433:
2434: hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2435: hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2436: hr_utility.set_location('Convert_Period_Type', 170);
2437:
2438: IF v_to_annualizing_factor = 0 OR
2439: v_to_annualizing_factor = -999 OR
2432: --
2433:
2434: hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2435: hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2436: hr_utility.set_location('Convert_Period_Type', 170);
2437:
2438: IF v_to_annualizing_factor = 0 OR
2439: v_to_annualizing_factor = -999 OR
2440: v_from_annualizing_factor = -999 THEN
2438: IF v_to_annualizing_factor = 0 OR
2439: v_to_annualizing_factor = -999 OR
2440: v_from_annualizing_factor = -999 THEN
2441:
2442: hr_utility.set_location('Convert_Period_Type', 175);
2443:
2444: v_converted_figure := 0;
2445: RETURN v_converted_figure;
2446:
2445: RETURN v_converted_figure;
2446:
2447: ELSE
2448:
2449: hr_utility.set_location('Convert_Period_Type', 180);
2450: /* hard coded values are for testing */
2451:
2452: -- v_from_annualizing_factor := 12;
2453: -- v_to_annualizing_factor := 1040;
2495:
2496: -- Set range to a single week if no dates are entered:
2497: -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
2498: --
2499: hr_utility.set_location('work_schedule_total_hours setting dates', 3);
2500: v_range_start := NVL(p_range_start, sysdate);
2501: v_range_end := NVL(p_range_end, sysdate + 6);
2502: --
2503: -- END IF;
2501: v_range_end := NVL(p_range_end, sysdate + 6);
2502: --
2503: -- END IF;
2504: -- Check for valid range
2505: hr_utility.set_location('work_schedule_total_hours', 5);
2506: IF v_range_start > v_range_end THEN
2507: --
2508: hr_utility.set_location('work_schedule_total_hours', 7);
2509: RETURN v_total_hours;
2504: -- Check for valid range
2505: hr_utility.set_location('work_schedule_total_hours', 5);
2506: IF v_range_start > v_range_end THEN
2507: --
2508: hr_utility.set_location('work_schedule_total_hours', 7);
2509: RETURN v_total_hours;
2510: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2511: -- hr_utility.raise_error;
2512: --
2506: IF v_range_start > v_range_end THEN
2507: --
2508: hr_utility.set_location('work_schedule_total_hours', 7);
2509: RETURN v_total_hours;
2510: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2511: -- hr_utility.raise_error;
2512: --
2513: END IF;
2514: --
2507: --
2508: hr_utility.set_location('work_schedule_total_hours', 7);
2509: RETURN v_total_hours;
2510: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2511: -- hr_utility.raise_error;
2512: --
2513: END IF;
2514: --
2515: -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
2529: -- FROM sys.dual;
2530: --
2531: END IF;
2532: --
2533: hr_utility.set_location('work_schedule_total_hours', 10);
2534: -- Track range dates:
2535: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2536: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2537: --
2531: END IF;
2532: --
2533: hr_utility.set_location('work_schedule_total_hours', 10);
2534: -- Track range dates:
2535: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2536: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2537: --
2538: -- Check if the work schedule is an id or a name. If the work
2539: -- schedule does not exist, then return 0.
2532: --
2533: hr_utility.set_location('work_schedule_total_hours', 10);
2534: -- Track range dates:
2535: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2536: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2537: --
2538: -- Check if the work schedule is an id or a name. If the work
2539: -- schedule does not exist, then return 0.
2540: --
2572: end if;
2573: --
2574: v_curr_date := v_range_start;
2575: --
2576: hr_utility.set_location('work_schedule_total_hours curr_date = '||to_char(v_curr_date), 20);
2577: --
2578: LOOP
2579: v_day_no := TO_CHAR(v_curr_date, 'D');
2580:
2577: --
2578: LOOP
2579: v_day_no := TO_CHAR(v_curr_date, 'D');
2580:
2581: hr_utility.set_location('curr_day_no = '||to_char(v_day_no), 20);
2582:
2583: SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
2584: 4,'WED',5,'THU',6,'FRI',7,'SAT')
2585: INTO v_curr_day
2585: INTO v_curr_day
2586: FROM DUAL;
2587:
2588: --
2589: hr_utility.set_location('curr_day = '||v_curr_day, 20);
2590:
2591: --
2592: hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
2593: v_total_hours := v_total_hours +
2588: --
2589: hr_utility.set_location('curr_day = '||v_curr_day, 20);
2590:
2591: --
2592: hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
2593: v_total_hours := v_total_hours +
2594: fnd_number.canonical_to_number(hruserdt.get_table_value(p_bg_id,
2595: c_ws_tab_name,
2596: v_ws_name,
2596: v_ws_name,
2597: v_curr_day));
2598: v_curr_date := v_curr_date + 1;
2599: --
2600: hr_utility.set_location('curr_date = '||to_char(v_curr_date), 20);
2601: --
2602: EXIT WHEN v_curr_date > v_range_end;
2603: --
2604: END LOOP;
2652: v_valid_jurisdiction VARCHAR2(1) := 'E'; -- RETURN var.
2653:
2654: BEGIN
2655:
2656: hr_utility.set_location('Jurisdiction_Validation', 01);
2657:
2658: IF substr(p_jurisdiction_code, 8,4) = '0000' THEN
2659:
2660: IF substr(p_jurisdiction_code, 4,3) = '000' THEN
2696:
2697: EXCEPTION
2698: WHEN NO_DATA_FOUND THEN
2699:
2700: hr_utility.set_location('Jurisdiction_Validation', 03);
2701: v_valid_jurisdiction := 'E';
2702: RETURN v_valid_jurisdiction;
2703:
2704: END us_jurisdiction_val;
2717: --
2718: -- GET
2719: -- This DDF info is held in ELEMENT_INFORMATION3.
2720: --
2721: hr_utility.set_location('get_process_run_flag', 10);
2722: --
2723: begin
2724: SELECT element_information3
2725: INTO v_proc_run_type
2728: BETWEEN effective_start_date
2729: AND effective_end_date
2730: AND element_type_id = p_ele_type_id;
2731: --
2732: hr_utility.set_location('get_process_run_flag', 20);
2733: RETURN v_proc_run_type;
2734: --
2735: exception when NO_DATA_FOUND then
2736: hr_utility.set_location('get_process_run_flag', 30);
2732: hr_utility.set_location('get_process_run_flag', 20);
2733: RETURN v_proc_run_type;
2734: --
2735: exception when NO_DATA_FOUND then
2736: hr_utility.set_location('get_process_run_flag', 30);
2737: RETURN v_proc_run_type;
2738: end;
2739: --
2740: END get_process_run_flag;
2758:
2759: -- Check that
2760: -- This DDF info is held in ELEMENT_INFORMATION3.
2761:
2762: hr_utility.set_location('check_dedn_freq', 10);
2763:
2764: begin
2765: SELECT element_information3
2766: INTO v_proc_run_type
2779: end;
2780: --
2781: -- See if freq rule even comes into play here:
2782: --
2783: hr_utility.set_location('check_dedn_freq', 45);
2784: SELECT COUNT(0)
2785: INTO v_freq_rule_exists
2786: FROM pay_ele_payroll_freq_rules EPF
2787: WHERE element_type_id = p_ele_type_id
2813: -- ...into v_run_number...
2814: -- What we NEED is the actual PERIOD # w/in Month or Year.
2815: --
2816: IF v_number_per_fy < 12 THEN
2817: hr_utility.set_location('check_dedn_freq', 20);
2818:
2819: SELECT COUNT(0)
2820: INTO v_run_number
2821: FROM per_time_periods PTP
2824: AND v_period_end_date
2825: AND PTP.payroll_id = p_payroll_id;
2826:
2827: ELSIF v_number_per_fy > 12 THEN
2828: hr_utility.set_location('check_dedn_freq', 30);
2829:
2830: SELECT COUNT(0)
2831: INTO v_run_number
2832: FROM per_time_periods PTP
2835: AND v_period_end_date
2836: AND PTP.payroll_id = p_payroll_id;
2837:
2838: ELSIF v_number_per_fy = 12 THEN
2839: hr_utility.set_location('check_dedn_freq', 40);
2840: v_skip_element := 'N';
2841: RETURN v_skip_element;
2842: END IF;
2843:
2845: -- Check frequency rule:
2846: -- If none exists, then process!
2847: --
2848:
2849: hr_utility.set_location('check_dedn_freq', 50);
2850: SELECT 'N'
2851: INTO v_skip_element
2852: FROM pay_ele_payroll_freq_rules EPF,
2853: pay_freq_rule_periods FRP
2860: RETURN v_skip_element;
2861:
2862: EXCEPTION
2863: WHEN NO_DATA_FOUND THEN
2864: hr_utility.set_location('check_dedn_freq', 60);
2865: v_skip_element := 'Y';
2866: RETURN v_skip_element;
2867:
2868: END check_dedn_freq;
2899: --
2900: BEGIN -- Separate_Check_Skip
2901: --
2902:
2903: hr_utility.set_location('Separate_Check_Skip', 7);
2904:
2905: SELECT RRV.result_value
2906: INTO v_dedn_proc
2907: FROM pay_run_result_values RRV,
2923: --
2924:
2925: v_skip_element := 'Y';
2926:
2927: hr_utility.set_location('Separate_Check_Skip', 9);
2928:
2929: begin
2930:
2931: SELECT ECL.classification_name
2944: -- Change v_skip_element back to 'N' if this a tax deduction.
2945: -- ie. we know DEDN PROC inpval is not null, meaning it's either TAX ONLY
2946: -- or PRETAX AND TAX ONLY.
2947:
2948: hr_utility.set_location('Separate_Check_Skip', 10);
2949: v_skip_element := 'N';
2950: RETURN v_skip_element;
2951:
2952: ELSIF UPPER(v_ele_class_name) = 'PRE-TAX DEDUCTIONS' AND
2953: v_dedn_proc = 'PTT' THEN
2954:
2955: -- Change v_skip_element back to 'N' if dedn proc = 'PTT'
2956:
2957: hr_utility.set_location('Separate_Check_Skip', 11);
2958: v_skip_element := 'N';
2959: RETURN v_skip_element;
2960:
2961: END IF;
2960:
2961: END IF;
2962:
2963: exception WHEN NO_DATA_FOUND THEN
2964: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2965: v_skip_element := 'Y';
2966: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2967: -- hr_utility.raise_error;
2968: end;
2962:
2963: exception WHEN NO_DATA_FOUND THEN
2964: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2965: v_skip_element := 'Y';
2966: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2967: -- hr_utility.raise_error;
2968: end;
2969:
2970: RETURN v_skip_element;
2963: exception WHEN NO_DATA_FOUND THEN
2964: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2965: v_skip_element := 'Y';
2966: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2967: -- hr_utility.raise_error;
2968: end;
2969:
2970: RETURN v_skip_element;
2971:
2970: RETURN v_skip_element;
2971:
2972: EXCEPTION
2973: WHEN NO_DATA_FOUND THEN
2974: hr_utility.set_location('Separate_Check_Skip', 21);
2975: RETURN v_skip_element;
2976: -- Special Dedn Proc not required. SKIP_FLAG = 'N'.
2977: --
2978: END Separate_Check_Skip;
2995: --
2996:
2997: BEGIN
2998:
2999: hr_utility.set_location('Other_Non_Separate_Check', 10);
3000:
3001: SELECT DECODE(COUNT(IPV.input_value_id), 0, 'N', 'Y')
3002: INTO sepcheck_flag
3003: FROM pay_element_entry_values_f EEV,
3013: AND EEV.input_value_id = IPV.input_value_id
3014: AND UPPER(IPV.name) = 'SEPARATE CHECK';
3015: --
3016: IF sepcheck_flag = 'Y' THEN
3017: hr_utility.set_location('Other_Non_Separate_Check', 15);
3018: RETURN sepcheck_flag;
3019: END IF;
3020: --
3021: hr_utility.set_location('Other_Non_Separate_Check', 20);
3017: hr_utility.set_location('Other_Non_Separate_Check', 15);
3018: RETURN sepcheck_flag;
3019: END IF;
3020: --
3021: hr_utility.set_location('Other_Non_Separate_Check', 20);
3022:
3023: SELECT DECODE(COUNT(ELE.element_entry_id), 0, 'N', 'Y')
3024: INTO sepcheck_flag
3025: FROM pay_element_entries_f ELE,
3053:
3054: --
3055: EXCEPTION
3056: WHEN NO_DATA_FOUND THEN
3057: hr_utility.set_location('Other_Non_Separate_Check', 30);
3058: RETURN sepcheck_flag;
3059: --
3060: END Other_Non_Separate_Check;
3061: --
3313: -- Remember to check for a rate via Rate Code!
3314: --
3315: -- Go ahead and set pay_basis_rate now - will most likely be used somewhere.
3316: --
3317: hr_utility.set_location('OT_Base_Rate', 5);
3318:
3319: select start_date,
3320: end_date
3321: into v_range_start,
3357: --
3358: OPEN get_tew_rate;
3359: --
3360: LOOP
3361: hr_utility.set_location('OT_Base_Rate', 10);
3362: FETCH get_tew_rate
3363: INTO v_tew_rate, v_entry_id;
3364: EXIT WHEN get_tew_rate%NOTFOUND;
3365: --
3366: v_tew_count := v_tew_count + 1;
3367: IF v_tew_rate <> 0 THEN
3368: v_tew_total_rate := v_tew_total_rate + v_tew_rate;
3369: ELSE -- no Rate entered, check Rate Code
3370: hr_utility.set_location('OT_Base_Rate', 15);
3371: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3372: INTO v_tew_rcode
3373: FROM pay_element_entry_values_f EEV,
3374: pay_element_entries_f ELE,
3389: -- Use pay basis salary converted to hourly rate.
3390: v_tew_total_rate := v_tew_total_rate + v_pay_basis_rate;
3391: ELSE
3392: -- Find rate from rate table.
3393: hr_utility.set_location('OT_Base_Rate', 17);
3394: v_tew_total_rate := v_tew_total_rate +
3395: fnd_number.canonical_to_number(hruserdt.get_table_value(
3396: p_bg_id,
3397: c_rate_table_name,
3405: --
3406: CLOSE get_tew_rate;
3407: --
3408: IF v_tew_count = 0 THEN -- ie. only use "Regular" rates if TEW not entered.
3409: hr_utility.set_location('OT_Base_Rate', 20);
3410: SELECT COUNT(IPV.input_value_id)
3411: INTO v_use_regwage
3412: FROM pay_element_entry_values_f EEV,
3413: pay_element_entries_f ELE,
3421: AND IPV.element_type_id = l_reg_wages_ele_id
3422: AND UPPER(IPV.name) = 'RATE';
3423: --
3424: IF v_use_regwage <> 0 THEN
3425: hr_utility.set_location('OT_Base_Rate', 30);
3426: SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3427: EEV.element_entry_id
3428: INTO v_regwage_rate,
3429: v_entry_id
3439: AND IPV.element_type_id = l_reg_wages_ele_id
3440: AND UPPER(IPV.name) = 'RATE';
3441: --
3442: IF v_regwage_rate = 0 THEN
3443: hr_utility.set_location('OT_Base_Rate', 40);
3444: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3445: INTO v_regwage_rcode
3446: FROM pay_element_entry_values_f EEV,
3447: pay_element_entries_f ELE,
3457: AND UPPER(ELT.element_name) = 'REGULAR WAGES'
3458: AND ELT.element_type_id = IPV.element_type_id
3459: AND UPPER(IPV.name) = 'RATE CODE';
3460: --
3461: hr_utility.set_location('OT_Base_Rate', 41);
3462: v_regwage_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
3463: p_bus_group_id => p_bg_id,
3464: p_table_name => c_rate_table_name,
3465: p_col_name => c_rate_table_column,
3467: END IF;
3468: v_ot_base_rate := v_ot_base_rate + v_regwage_rate;
3469: --
3470: ELSE
3471: hr_utility.set_location('OT_Base_Rate', 50);
3472: SELECT COUNT(IPV.input_value_id)
3473: INTO v_use_regsal
3474: FROM pay_element_entry_values_f EEV,
3475: pay_element_entries_f ELE,
3483: AND IPV.element_type_id = l_reg_sal_ele_id
3484: AND UPPER(IPV.name) = 'MONTHLY SALARY';
3485: --
3486: IF v_use_regsal <> 0 THEN
3487: hr_utility.set_location('OT_Base_Rate', 51);
3488: SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
3489: INTO v_regsal_mosal
3490: FROM pay_element_entry_values_f EEV,
3491: pay_element_entries_f ELE,
3498: AND EEV.input_value_id = IPV.input_value_id
3499: AND IPV.element_type_id = l_reg_sal_ele_id
3500: AND UPPER(IPV.name) = 'MONTHLY SALARY';
3501: --
3502: hr_utility.set_location('OT_Base_Rate', 60);
3503:
3504: v_regsal_rate := hr_ca_ff_udfs.Convert_Period_Type(
3505: p_bus_grp_id => p_bg_id,
3506: p_payroll_id => p_pay_id,
3531: --
3532: OPEN get_include_in_ot;
3533: LOOP
3534: --
3535: hr_utility.set_location('OT_Base_Rate', 70);
3536: FETCH get_include_in_ot
3537: INTO v_eletype_id,
3538: v_ele_name,
3539: v_ff_name;
3540: EXIT WHEN get_include_in_ot%NOTFOUND;
3541: --
3542: IF SUBSTR(v_ff_name,1,11) = 'FLAT_AMOUNT' THEN
3543: -- Find "Amount" entered, convert to hourly figure.
3544: hr_utility.set_location('OT_Base_Rate', 80);
3545: OPEN get_flat_amounts;
3546: LOOP
3547: FETCH get_flat_amounts
3548: INTO v_flat_amount;
3547: FETCH get_flat_amounts
3548: INTO v_flat_amount;
3549: EXIT WHEN get_flat_amounts%NOTFOUND;
3550: v_flat_count := v_flat_count + 1;
3551: hr_utility.set_location('OT_Base_Rate', 90);
3552:
3553: v_flat_total := v_flat_total + hr_ca_ff_udfs.Convert_Period_Type(
3554: p_bus_grp_id => p_bg_id,
3555: p_payroll_id => p_pay_id,
3568: --
3569: END LOOP;
3570: CLOSE get_flat_amounts;
3571: --
3572: hr_utility.set_location('OT_Base_Rate', 100);
3573: v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3574: --
3575: ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3576: hr_utility.set_location('OT_Base_Rate', 110);
3572: hr_utility.set_location('OT_Base_Rate', 100);
3573: v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3574: --
3575: ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3576: hr_utility.set_location('OT_Base_Rate', 110);
3577: OPEN get_percentages;
3578: LOOP
3579: FETCH get_percentages
3580: INTO v_percentage;
3581: EXIT WHEN get_percentages%NOTFOUND;
3582: v_pct_count := v_pct_count + 1;
3583: --
3584: IF v_regsal_rate <> 0 THEN
3585: hr_utility.set_location('OT_Base_Rate', 105);
3586: v_pct_total := v_percentage * v_regsal_rate;
3587: END IF;
3588: --
3589: END LOOP;
3589: END LOOP;
3590: --
3591: CLOSE get_percentages;
3592: --
3593: hr_utility.set_location('OT_Base_Rate', 110);
3594: v_ot_base_rate := v_ot_base_rate + (v_pct_total / v_pct_count);
3595: --
3596: ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3597: --
3596: ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3597: --
3598: -- Remember to look for "Rate Code" if necessary and "Multiple" always.
3599: --
3600: hr_utility.set_location('OT_Base_Rate', 115);
3601: OPEN get_rates;
3602: LOOP
3603: FETCH get_rates
3604: INTO v_rate, v_entry_id;
3602: LOOP
3603: FETCH get_rates
3604: INTO v_rate, v_entry_id;
3605: EXIT WHEN get_rates%NOTFOUND;
3606: hr_utility.set_location('OT_Base_Rate', 120);
3607: v_rate_count := v_rate_count + 1;
3608: IF v_rate = 0 THEN
3609: hr_utility.set_location('OT_Base_Rate', 125);
3610: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3605: EXIT WHEN get_rates%NOTFOUND;
3606: hr_utility.set_location('OT_Base_Rate', 120);
3607: v_rate_count := v_rate_count + 1;
3608: IF v_rate = 0 THEN
3609: hr_utility.set_location('OT_Base_Rate', 125);
3610: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3611: INTO v_rate_rcode
3612: FROM pay_element_entry_values_f EEV,
3613: pay_element_entries_f ELE,
3624: AND ELT.element_type_id = IPV.element_type_id
3625: AND UPPER(IPV.name) = 'RATE CODE';
3626: --
3627: IF v_rate_rcode <> 'NOT ENTERED' THEN
3628: hr_utility.set_location('OT_Base_Rate', 130);
3629: v_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
3630: p_bg_id,
3631: c_rate_table_name,
3632: c_rate_table_column,
3635: --
3636: END IF;
3637: -- Now get "Multiple" on this entry, if any.
3638: IF v_rate <> 0 THEN
3639: hr_utility.set_location('OT_Base_Rate', 135);
3640: SELECT COUNT(0)
3641: INTO v_rate_mult_count
3642: FROM pay_element_entry_values_f EEV,
3643: pay_element_entries_f ELE,
3654: AND ELT.element_type_id = IPV.element_type_id
3655: AND UPPER(IPV.name) = 'MULTIPLE';
3656: --
3657: IF v_rate_mult_count <> 0 THEN
3658: hr_utility.set_location('OT_Base_Rate', 140);
3659: SELECT NVL(EEV.screen_entry_value, 0)
3660: INTO v_rate_multiple
3661: FROM pay_element_entry_values_f EEV,
3662: pay_element_entries_f ELE,
3688: --
3689: v_ot_base_rate := v_ot_base_rate + (v_rate_total / v_rate_count);
3690: --
3691: ELSIF SUBSTR(v_ff_name,1,8) = 'GROSS_UP' THEN
3692: hr_utility.set_location('OT_Base_Rate', 150);
3693: OPEN get_grosses;
3694: LOOP
3695: FETCH get_grosses
3696: INTO v_gross_results;
3697: EXIT WHEN get_grosses%NOTFOUND;
3698: v_gross_count := v_gross_count + 1;
3699: IF v_gross_results <> 0 THEN
3700: -- Convert gross result to hourly figure.
3701: hr_utility.set_location('OT_Base_Rate', 160);
3702:
3703: v_gross_total := v_gross_total + hr_ca_ff_udfs.Convert_Period_Type(
3704: p_bus_grp_id => p_bg_id,
3705: p_payroll_id => p_pay_id,
3732: RETURN v_ot_base_rate;
3733: --
3734: EXCEPTION
3735: WHEN NO_DATA_FOUND THEN
3736: hr_utility.set_location('OT_Base_Rate', 170);
3737: RETURN v_ot_base_rate;
3738: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3739: -- hr_utility.raise_error;
3740: --
3734: EXCEPTION
3735: WHEN NO_DATA_FOUND THEN
3736: hr_utility.set_location('OT_Base_Rate', 170);
3737: RETURN v_ot_base_rate;
3738: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3739: -- hr_utility.raise_error;
3740: --
3741: END OT_Base_Rate;
3742: --
3735: WHEN NO_DATA_FOUND THEN
3736: hr_utility.set_location('OT_Base_Rate', 170);
3737: RETURN v_ot_base_rate;
3738: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3739: -- hr_utility.raise_error;
3740: --
3741: END OT_Base_Rate;
3742: --
3743: /*
3843: BEGIN -- Dedn_Freq_Factor
3844: --
3845: -- v_date_earned := p_date_earned;
3846: --
3847: hr_utility.set_location('Dedn_Freq_Factor', 10);
3848: SELECT DECODE(COUNT(FRP.freq_rule_period_id), 0, 'N', 'Y')
3849: INTO v_freq_rules_exist
3850: FROM pay_freq_rule_periods FRP,
3851: pay_ele_payroll_freq_rules EPF
3865: --
3866: --
3867: ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'N' THEN
3868: --
3869: hr_utility.set_location('Dedn_Freq_Factor', 40);
3870: SELECT number_per_fiscal_year
3871: INTO v_ele_period_num_per_fy
3872: FROM per_time_period_types TPT
3873: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3871: INTO v_ele_period_num_per_fy
3872: FROM per_time_period_types TPT
3873: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3874: --
3875: hr_utility.set_location('Dedn_Freq_Factor', 45);
3876: SELECT TPT.number_per_fiscal_year
3877: INTO v_pay_period_num_per_fy
3878: FROM per_time_period_types TPT,
3879: pay_payrolls_f PPF
3886: v_dedn_freq_factor := v_ele_period_num_per_fy / v_pay_period_num_per_fy;
3887: --
3888: ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
3889: --
3890: hr_utility.set_location('Dedn_Freq_Factor', 50);
3891: SELECT number_per_fiscal_year
3892: INTO v_ele_period_num_per_fy
3893: FROM per_time_period_types TPT
3894: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3892: INTO v_ele_period_num_per_fy
3893: FROM per_time_period_types TPT
3894: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3895: --
3896: hr_utility.set_location('Dedn_Freq_Factor', 55);
3897: SELECT TPT.number_per_fiscal_year
3898: INTO v_pay_period_num_per_fy
3899: FROM pay_payrolls_f PPF,
3900: per_time_period_types TPT
3904: BETWEEN PPF.effective_start_date
3905: AND PPF.effective_end_date;
3906: --
3907: IF v_pay_period_num_per_fy >= 12 THEN
3908: hr_utility.set_location('Dedn_Freq_Factor', 60);
3909: SELECT COUNT(0)
3910: INTO v_pay_periods_in_month
3911: FROM per_time_periods PTP
3912: WHERE PTP.end_date
3917: -- Frequency rules exist, so this select should never return 0.
3918: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
3919: -- ie. so v_dedn_freq_factor will also equal 1.
3920: --
3921: hr_utility.set_location('Dedn_Freq_Factor', 65);
3922: SELECT COUNT(0)
3923: INTO v_pay_periods_in_reset
3924: FROM pay_ele_payroll_freq_rules EPF,
3925: pay_freq_rule_periods FRP
3926: WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
3927: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3928: AND EPF.payroll_id = p_payroll_id
3929: AND EPF.element_type_id = p_element_type_id;
3930: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
3931: --
3932: IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
3933: v_dedn_freq_factor := 1;
3934: ELSIF v_pay_periods_in_reset = 0 THEN
3940: v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
3941: END IF;
3942: --
3943: ELSE
3944: hr_utility.set_location('Dedn_Freq_Factor', 70);
3945: SELECT COUNT(0)
3946: INTO v_pay_periods_in_year
3947: FROM per_time_periods PTP
3948: WHERE PTP.end_date
3953: -- Frequency rules exist, so this select should never return 0.
3954: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
3955: -- ie. so v_dedn_freq_factor will also equal 1.
3956: --
3957: hr_utility.set_location('Dedn_Freq_Factor', 75);
3958: SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
3959: INTO v_pay_periods_in_reset
3960: FROM pay_ele_payroll_freq_rules EPF,
3961: pay_freq_rule_periods FRP
3963: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3964: AND EPF.payroll_id = p_payroll_id
3965: AND EPF.element_type_id = p_element_type_id;
3966: --
3967: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
3968: IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
3969: v_dedn_freq_factor := 1;
3970: ELSE
3971: v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
3974: END IF;
3975: --
3976: END IF;
3977: --
3978: hr_utility.set_location('Dedn_Freq_Factor', 80);
3979: RETURN v_dedn_freq_factor;
3980: --
3981: END Dedn_Freq_Factor;
3982: --
4018: --
4019: p_to_arrears := 0;
4020: p_not_taken := 0;
4021:
4022: hr_utility.set_location('hr_ca_ff_udfs.arrearage', 1);
4023:
4024: -- Determine if Arrears = 'Y' for this dedn
4025: -- Can do this by checking for "Clear Arrears" input value on base ele.
4026: -- This input value is only created when Arrears is marked Yes on Deductions
4027: -- screen.
4028:
4029: begin
4030:
4031: hr_utility.set_location('Shadow elename = '||v_shadow_ele_name, 38 );
4032:
4033: select 'Y'
4034: into v_arrears_flag
4035: from pay_input_values_f ipv
4040:
4041: exception
4042:
4043: WHEN NO_DATA_FOUND THEN
4044: hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4045: v_arrears_flag := 'N';
4046:
4047: WHEN TOO_MANY_ROWS THEN
4048: hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4044: hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4045: v_arrears_flag := 'N';
4046:
4047: WHEN TOO_MANY_ROWS THEN
4048: hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4049: v_arrears_flag := 'N';
4050:
4051: end;
4052:
4056:
4057: p_to_arrears := 0;
4058: p_not_taken := 0;
4059: l_dedn_amt := p_dedn_amt;
4060: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4061: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4062: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4063:
4064: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4057: p_to_arrears := 0;
4058: p_not_taken := 0;
4059: l_dedn_amt := p_dedn_amt;
4060: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4061: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4062: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4063:
4064: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4065: -- Don't take anything, no arrears contr either.
4058: p_not_taken := 0;
4059: l_dedn_amt := p_dedn_amt;
4060: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4061: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4062: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4063:
4064: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4065: -- Don't take anything, no arrears contr either.
4066: p_to_arrears := 0;
4065: -- Don't take anything, no arrears contr either.
4066: p_to_arrears := 0;
4067: p_not_taken := p_dedn_amt;
4068: l_dedn_amt := 0;
4069: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4070: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4071: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4072:
4073: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4066: p_to_arrears := 0;
4067: p_not_taken := p_dedn_amt;
4068: l_dedn_amt := 0;
4069: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4070: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4071: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4072:
4073: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4074:
4067: p_not_taken := p_dedn_amt;
4068: l_dedn_amt := 0;
4069: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4070: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4071: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4072:
4073: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4074:
4075: IF p_partial_flag = 'Y' THEN
4076: --
4077: p_to_arrears := 0;
4078: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4079: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4080: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4081: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4082: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4083:
4084: ELSE
4077: p_to_arrears := 0;
4078: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4079: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4080: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4081: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4082: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4083:
4084: ELSE
4085:
4078: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4079: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4080: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4081: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4082: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4083:
4084: ELSE
4085:
4086: p_to_arrears := 0;
4085:
4086: p_to_arrears := 0;
4087: p_not_taken := p_dedn_amt;
4088: l_dedn_amt := 0;
4089: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4090: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4091: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4092:
4093: END IF;
4086: p_to_arrears := 0;
4087: p_not_taken := p_dedn_amt;
4088: l_dedn_amt := 0;
4089: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4090: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4091: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4092:
4093: END IF;
4094:
4087: p_not_taken := p_dedn_amt;
4088: l_dedn_amt := 0;
4089: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4090: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4091: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4092:
4093: END IF;
4094:
4095: END IF;
4101: -- Don't take anything, put it all in arrears.
4102: p_to_arrears := p_dedn_amt;
4103: p_not_taken := p_dedn_amt;
4104: l_dedn_amt := 0;
4105: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4106: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4107: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4108:
4109: ELSE
4102: p_to_arrears := p_dedn_amt;
4103: p_not_taken := p_dedn_amt;
4104: l_dedn_amt := 0;
4105: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4106: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4107: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4108:
4109: ELSE
4110:
4103: p_not_taken := p_dedn_amt;
4104: l_dedn_amt := 0;
4105: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4106: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4107: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4108:
4109: ELSE
4110:
4111: l_total_dedn := p_dedn_amt + p_arrears_itd;
4117: -- there's enough net to take it all, clear arrears:
4118: p_to_arrears := -1 * p_arrears_itd;
4119: l_dedn_amt := l_total_dedn;
4120: p_not_taken := 0;
4121: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4122: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4123: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4124:
4125: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4118: p_to_arrears := -1 * p_arrears_itd;
4119: l_dedn_amt := l_total_dedn;
4120: p_not_taken := 0;
4121: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4122: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4123: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4124:
4125: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4126: try and take as much of the total deduction amount (current dedn +
4119: l_dedn_amt := l_total_dedn;
4120: p_not_taken := 0;
4121: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4122: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4123: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4124:
4125: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4126: try and take as much of the total deduction amount (current dedn +
4127: arrears) and leave the rest in arrears. */
4137: ELSE
4138: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4139: END IF;
4140: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4141: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4142: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4143: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4144:
4145: ELSE -- p_partial_flag = 'N'
4138: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4139: END IF;
4140: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4141: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4142: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4143: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4144:
4145: ELSE -- p_partial_flag = 'N'
4146: IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4139: END IF;
4140: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4141: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4142: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4143: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4144:
4145: ELSE -- p_partial_flag = 'N'
4146: IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4147: -- Take the whole deduction amount.
4153: p_to_arrears := p_dedn_amt;
4154: p_not_taken := p_dedn_amt;
4155: l_dedn_amt := 0;
4156: END IF;
4157: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4158: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4159: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4160:
4161: END IF;
4154: p_not_taken := p_dedn_amt;
4155: l_dedn_amt := 0;
4156: END IF;
4157: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4158: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4159: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4160:
4161: END IF;
4162:
4155: l_dedn_amt := 0;
4156: END IF;
4157: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4158: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4159: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4160:
4161: END IF;
4162:
4163: END IF;
4210: RETURN l_geocode;
4211: --
4212: EXCEPTION
4213: WHEN OTHERS THEN
4214: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 20);
4215: l_geocode := '00-000-0000';
4216: RETURN l_geocode;
4217: --
4218: END addr_val; -- addr_val
4246: IF (p_skip_rule = 'Y') THEN
4247: RAISE TOO_MANY_ROWS;
4248: END IF;
4249:
4250: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 1);
4251:
4252: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4253: INTO l_geocode
4254: FROM pay_us_zip_codes z,
4262: --
4263: EXCEPTION -- (2)
4264: --
4265: WHEN NO_DATA_FOUND THEN -- Invalid city/zip combo
4266: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 3);
4267: l_geocode := '00-000-0000';
4268: RETURN l_geocode;
4269: --
4270: WHEN TOO_MANY_ROWS THEN -- city/zip does not uniquely defn geo
4273: INTO l_state_code
4274: FROM pay_us_states
4275: WHERE state_abbrev = p_state_abbrev;
4276:
4277: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 5);
4278: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4279: INTO l_geocode
4280: FROM pay_us_zip_codes z,
4281: pay_us_city_names a,
4295: --
4296: -- Fallout from (2) ie. county/city/zip combo invalid or does not
4297: -- uniquely define geocode.
4298: WHEN NO_DATA_FOUND THEN
4299: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 7);
4300: l_geocode := '00-000-0000';
4301: RETURN l_geocode;
4302: --
4303: WHEN TOO_MANY_ROWS THEN
4300: l_geocode := '00-000-0000';
4301: RETURN l_geocode;
4302: --
4303: WHEN TOO_MANY_ROWS THEN
4304: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 9);
4305: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4306: INTO l_geocode
4307: FROM pay_us_zip_codes z,
4308: pay_us_city_names a,
4326: end; -- (1)
4327: --
4328: -- We're in Main
4329: --
4330: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 11);
4331: --
4332: if (substr(l_geocode,8,1) = 'U') THEN
4333: l_geocode := substr(l_geocode,1,7)||'0000';
4334: END IF;
4338: EXCEPTION -- Main addr_val
4339: -- Fallout from (1) state/county/city/zip does not uniquely define a geo.
4340: -- Return failure geocode.
4341: WHEN NO_DATA_FOUND THEN
4342: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 13);
4343: l_geocode := '00-000-0000';
4344: RETURN l_geocode;
4345: --
4346: WHEN TOO_MANY_ROWS THEN
4343: l_geocode := '00-000-0000';
4344: RETURN l_geocode;
4345: --
4346: WHEN TOO_MANY_ROWS THEN
4347: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 15);
4348: l_geocode := '00-000-0000';
4349: RETURN l_geocode;
4350: --
4351: END addr_val; -- Main addr_val
4404: l_value NUMBER;
4405: l_input_value_name VARCHAR2(200);
4406:
4407: BEGIN
4408: hr_utility.set_location('in get_earnings_and_type',10);
4409: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4405: l_input_value_name VARCHAR2(200);
4406:
4407: BEGIN
4408: hr_utility.set_location('in get_earnings_and_type',10);
4409: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4406:
4407: BEGIN
4408: hr_utility.set_location('in get_earnings_and_type',10);
4409: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4414: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4407: BEGIN
4408: hr_utility.set_location('in get_earnings_and_type',10);
4409: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4414: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4415: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4408: hr_utility.set_location('in get_earnings_and_type',10);
4409: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4414: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4415: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4416:
4409: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4414: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4415: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4416:
4417: l_value := 0;
4410: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4414: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4415: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4416:
4417: l_value := 0;
4418:
4411: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4412: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4413: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4414: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4415: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4416:
4417: l_value := 0;
4418:
4419: OPEN csr_reg_earnings(p_asst_id, p_bus_grp_id, p_date_earned);
4428: p_input_value_name := l_input_value_name;
4429: END IF;
4430: CLOSE csr_reg_earnings;
4431:
4432: hr_utility.set_location('returning p_value '|| p_value,10);
4433: hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4434: hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4435:
4436: RETURN 0;
4429: END IF;
4430: CLOSE csr_reg_earnings;
4431:
4432: hr_utility.set_location('returning p_value '|| p_value,10);
4433: hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4434: hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4435:
4436: RETURN 0;
4437:
4430: CLOSE csr_reg_earnings;
4431:
4432: hr_utility.set_location('returning p_value '|| p_value,10);
4433: hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4434: hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4435:
4436: RETURN 0;
4437:
4438: