[Home] [Help]
277: v_total_hours NUMBER(15,7) ;
278:
279: BEGIN
280:
281: hr_utility.set_location('Pro_Earn: actual hours worked IN = ', p_act_hrs_worked);
282: --
283: -- Prorate using hourly rate passed in as param:
284: --
285: /*
284: --
285: /*
286: IF UPPER(p_wsched) = 'NOT ENTERED' THEN
287:
288: hr_utility.set_location('Prorate_Earnings', 7);
289: v_hours_in_range := Standard_Hours_Worked( p_asg_std_hours,
290: p_range_start_date,
291: p_range_end_date,
292: p_asg_std_freq);
292: p_asg_std_freq);
293:
294: -- Keep running total of ACTUAL hours worked.
295:
296: hr_utility.set_location('Prorate_Earnings', 11);
297: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
298: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
299:
300: ELSE
294: -- Keep running total of ACTUAL hours worked.
295:
296: hr_utility.set_location('Prorate_Earnings', 11);
297: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
298: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
299:
300: ELSE
301:
302: hr_utility.set_location('Prorate_Earnings', 17);
298: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
299:
300: ELSE
301:
302: hr_utility.set_location('Prorate_Earnings', 17);
303:
304: -- Get work schedule name:
305: -- v_ws_id := to_number(p_wsched);
306: v_ws_id := fnd_number.canonical_to_number(p_wsched);
310: FROM pay_user_columns
311: WHERE user_column_id = v_ws_id
312: AND NVL(legislation_code,'CA') = 'CA';
313:
314: hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
315: hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
316:
317: hr_utility.set_location('calling core udfs', 44);
318: */
311: WHERE user_column_id = v_ws_id
312: AND NVL(legislation_code,'CA') = 'CA';
313:
314: hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
315: hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
316:
317: hr_utility.set_location('calling core udfs', 44);
318: */
319: v_hours_in_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
313:
314: hr_utility.set_location('p_range_start_date='||to_char(p_range_start_date), 19);
315: hr_utility.set_location('p_range_end_date='||to_char(p_range_end_date), 19);
316:
317: hr_utility.set_location('calling core udfs', 44);
318: */
319: v_hours_in_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
320: p_assignment_action_id
321: ,p_assignment_id
333: ,v_return_status
334: ,v_return_message);
335:
336: p_act_hrs_worked := p_act_hrs_worked + v_hours_in_range;
337: hr_utility.set_location('actual_hours_worked = ', p_act_hrs_worked);
338:
339: -- Hours in date range via work schedule or std hours.
340: /* END IF;*/
341:
339: -- Hours in date range via work schedule or std hours.
340: /* END IF;*/
341:
342: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
343: hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
344: hr_utility.set_location('Prorate_Earnings', 97);
345: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
346: hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
347: RETURN v_prorated_earn;
340: /* END IF;*/
341:
342: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
343: hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
344: hr_utility.set_location('Prorate_Earnings', 97);
345: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
346: hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
347: RETURN v_prorated_earn;
348:
342: v_prorated_earn := v_prorated_earn + (p_asg_hrly_rate * v_hours_in_range);
343: hr_utility.set_location('v_prorated_earnings = ', v_prorated_earn);
344: hr_utility.set_location('Prorate_Earnings', 97);
345: p_act_hrs_worked := ROUND(p_act_hrs_worked, 3);
346: hr_utility.set_location('Pro_Earn: actual hours worked OUT = ', p_act_hrs_worked);
347: RETURN v_prorated_earn;
348:
349: END Prorate_Earnings;
350:
402: -- span entire period.
403: -- We know this select will return a row, otherwise there would be no
404: -- EEV changes to detect.
405: --
406: hr_utility.set_location('Prorate_EEV', 103);
407: SELECT EEV.screen_entry_value,
408: GREATEST(EEV.effective_start_date, p_range_start_date),
409: EEV.effective_end_date
410: INTO v_earnings_entry,
416: AND EEV.effective_start_date <= p_range_start_date
417: AND EEV.effective_end_date >= p_range_start_date
418: AND EEV.effective_end_date < p_range_end_date;
419: --
420: hr_utility.set_location('Prorate_EEV', 105);
421: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
422: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
423: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
424:
417: AND EEV.effective_end_date >= p_range_start_date
418: AND EEV.effective_end_date < p_range_end_date;
419: --
420: hr_utility.set_location('Prorate_EEV', 105);
421: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
422: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
423: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
424:
425: hr_utility.set_location('calling core udfs', 45);
418: AND EEV.effective_end_date < p_range_end_date;
419: --
420: hr_utility.set_location('Prorate_EEV', 105);
421: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
422: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
423: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
424:
425: hr_utility.set_location('calling core udfs', 45);
426: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
419: --
420: hr_utility.set_location('Prorate_EEV', 105);
421: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
422: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
423: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
424:
425: hr_utility.set_location('calling core udfs', 45);
426: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
427: p_bus_group_id
421: hr_utility.set_location('p_asg_std_hrs=', p_asg_std_hrs);
422: hr_utility.set_location('p_pay_basis='||p_pay_basis, 105);
423: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 105);
424:
425: hr_utility.set_location('calling core udfs', 45);
426: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
427: p_bus_group_id
428: ,p_assignment_id
429: ,p_pay_id
430: ,p_element_entry_id
431: ,p_date_earned
432: ,p_assignment_action_id );
433: --
434: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
435: --
436: hr_utility.set_location('Prorate_EEV', 107);
437: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
438: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
432: ,p_assignment_action_id );
433: --
434: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
435: --
436: hr_utility.set_location('Prorate_EEV', 107);
437: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
438: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
439:
440: v_eev_prorated_earnings := v_eev_prorated_earnings +
433: --
434: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
435: --
436: hr_utility.set_location('Prorate_EEV', 107);
437: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
438: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
439:
440: v_eev_prorated_earnings := v_eev_prorated_earnings +
441: Prorate_Earnings (
434: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
435: --
436: hr_utility.set_location('Prorate_EEV', 107);
437: hr_utility.set_location('v_entry_start='||to_char(v_entry_start), 107);
438: hr_utility.set_location('v_entry_end='||to_char(v_entry_end), 107);
439:
440: v_eev_prorated_earnings := v_eev_prorated_earnings +
441: Prorate_Earnings (
442: p_bg_id => p_bus_group_id,
451: p_range_start_date => v_entry_start,
452: p_range_end_date => v_entry_end,
453: p_act_hrs_worked => p_actual_hrs_worked);
454: --
455: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
456: --
457: -- SELECT (EEV2):
458: OPEN get_entry_chgs (p_range_start_date, p_range_end_date);
459: LOOP
463: v_entry_start,
464: v_entry_end;
465: EXIT WHEN get_entry_chgs%NOTFOUND;
466: --
467: hr_utility.set_location('Prorate_EEV', 115);
468: --
469: -- For each range of dates found, add to running prorated earnings total.
470: --
471: hr_utility.set_location('Prorate_EEV', 117);
467: hr_utility.set_location('Prorate_EEV', 115);
468: --
469: -- For each range of dates found, add to running prorated earnings total.
470: --
471: hr_utility.set_location('Prorate_EEV', 117);
472:
473: hr_utility.set_location('calling core udfs', 46);
474: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
475: p_bus_group_id
469: -- For each range of dates found, add to running prorated earnings total.
470: --
471: hr_utility.set_location('Prorate_EEV', 117);
472:
473: hr_utility.set_location('calling core udfs', 46);
474: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
475: p_bus_group_id
476: ,p_assignment_id
477: ,p_pay_id
478: ,p_element_entry_id
479: ,p_date_earned
480: ,p_assignment_action_id );
481:
482: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
483: hr_utility.set_location('Prorate_EEV', 119);
484: --
485: v_eev_prorated_earnings := v_eev_prorated_earnings +
486: Prorate_Earnings (
479: ,p_date_earned
480: ,p_assignment_action_id );
481:
482: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
483: hr_utility.set_location('Prorate_EEV', 119);
484: --
485: v_eev_prorated_earnings := v_eev_prorated_earnings +
486: Prorate_Earnings (
487: p_bg_id => p_bus_group_id,
496: p_range_start_date => v_entry_start,
497: p_range_end_date => v_entry_end,
498: p_act_hrs_worked => p_actual_hrs_worked);
499: --
500: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
501: --
502: END LOOP;
503: --
504: CLOSE get_entry_chgs;
507: -- Select for SINGLE record that exists across Period End Date:
508: -- NOTE: Will only return a row if select (2) does not return a row where
509: -- Effective End Date = Period End Date !
510: --
511: hr_utility.set_location('Prorate_EEV', 141);
512: SELECT EEV.screen_entry_value,
513: EEV.effective_start_date,
514: LEAST(EEV.effective_end_date, p_range_end_date)
515: INTO v_earnings_entry,
521: AND EEV.effective_start_date > p_range_start_date
522: AND EEV.effective_start_date <= p_range_end_date
523: AND EEV.effective_end_date > p_range_end_date;
524: --
525: hr_utility.set_location('Prorate_EEV', 147);
526: hr_utility.set_location('calling core udfs', 47);
527: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
528: p_bus_group_id
529: ,p_assignment_id
522: AND EEV.effective_start_date <= p_range_end_date
523: AND EEV.effective_end_date > p_range_end_date;
524: --
525: hr_utility.set_location('Prorate_EEV', 147);
526: hr_utility.set_location('calling core udfs', 47);
527: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
528: p_bus_group_id
529: ,p_assignment_id
530: ,p_pay_id
531: ,p_element_entry_id
532: ,p_date_earned
533: ,p_assignment_action_id );
534:
535: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
536: hr_utility.set_location('Prorate_EEV', 151);
537:
538: v_eev_prorated_earnings := v_eev_prorated_earnings +
539: Prorate_Earnings (
532: ,p_date_earned
533: ,p_assignment_action_id );
534:
535: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
536: hr_utility.set_location('Prorate_EEV', 151);
537:
538: v_eev_prorated_earnings := v_eev_prorated_earnings +
539: Prorate_Earnings (
540: p_bg_id => p_bus_group_id,
549: p_range_start_date => v_entry_start,
550: p_range_end_date => v_entry_end,
551: p_act_hrs_worked => p_actual_hrs_worked);
552:
553: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
554: -- We're Done!
555: hr_utility.set_location('Prorate_EEV', 167);
556: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
557: p_hrly_rate := v_curr_hrly_rate;
551: p_act_hrs_worked => p_actual_hrs_worked);
552:
553: hr_utility.set_location('Prorate_EEV.v_eev_prorated_earnings = ', v_eev_prorated_earnings);
554: -- We're Done!
555: hr_utility.set_location('Prorate_EEV', 167);
556: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
557: p_hrly_rate := v_curr_hrly_rate;
558: RETURN v_eev_prorated_earnings;
559:
558: RETURN v_eev_prorated_earnings;
559:
560: EXCEPTION WHEN NO_DATA_FOUND THEN
561:
562: hr_utility.set_location('Prorate_EEV', 177);
563: p_actual_hrs_worked := ROUND(p_actual_hrs_worked, 3);
564: p_hrly_rate := v_curr_hrly_rate;
565: RETURN v_eev_prorated_earnings;
566:
596: -- Need to loop thru all "Vacation Plans" and check for entries in the current
597: -- period for this assignment.
598:
599: BEGIN
600: hr_utility.set_location('get_vac_pay', 11);
601: OPEN get_vac_hours (p_asg_id, p_eff_date);
602: LOOP
603:
604: hr_utility.set_location('get_vac_pay', 13);
600: hr_utility.set_location('get_vac_pay', 11);
601: OPEN get_vac_hours (p_asg_id, p_eff_date);
602: LOOP
603:
604: hr_utility.set_location('get_vac_pay', 13);
605: FETCH get_vac_hours
606: INTO l_vac_hours;
607: EXIT WHEN get_vac_hours%NOTFOUND;
608:
609: p_vac_hours := p_vac_hours + l_vac_hours;
610:
611: END LOOP;
612: CLOSE get_vac_hours;
613: hr_utility.set_location('get_vac_pay', 15);
614:
615: IF p_vac_hours <> 0 THEN
616:
617: l_vac_pay := p_vac_hours * p_curr_rate;
651: -- Return immediately when no sick time has been taken.
652:
653: BEGIN
654:
655: hr_utility.set_location('get_sick_pay', 11);
656: OPEN get_sick_hours (p_asg_id, p_eff_date);
657: LOOP
658:
659: hr_utility.set_location('get_sick_pay', 13);
655: hr_utility.set_location('get_sick_pay', 11);
656: OPEN get_sick_hours (p_asg_id, p_eff_date);
657: LOOP
658:
659: hr_utility.set_location('get_sick_pay', 13);
660: FETCH get_sick_hours
661: INTO l_sick_hours;
662: EXIT WHEN get_sick_hours%NOTFOUND;
663:
664: p_sick_hours := p_sick_hours + l_sick_hours;
665:
666: END LOOP;
667: CLOSE get_sick_hours;
668: hr_utility.set_location('get_sick_pay', 15);
669:
670: IF p_sick_hours <> 0 THEN
671:
672: l_sick_pay := p_sick_hours * p_curr_rate;
709: -- input value id as appropriate (according to ele name).
710:
711: IF p_pay_basis IS NOT NULL THEN
712: begin
713: hr_utility.set_location('calculate_period_earnings', 10);
714: SELECT PYB.input_value_id,
715: FCL.meaning,
716: FCL.lookup_code
717: INTO v_inpval_id,
730: AND ASG.effective_end_date;
731:
732: EXCEPTION WHEN NO_DATA_FOUND THEN
733:
734: hr_utility.set_location('calculate_period_earnings', 11);
735: v_period_earn := 0;
736: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
737: RETURN v_period_earn;
738:
735: v_period_earn := 0;
736: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
737: RETURN v_period_earn;
738:
739: -- hr_utility.set_message(801, 'PAY_xxxx_NO_ASST_IN_PERIOD');
740: -- hr_utility.raise_error;
741:
742: END;
743:
736: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
737: RETURN v_period_earn;
738:
739: -- hr_utility.set_message(801, 'PAY_xxxx_NO_ASST_IN_PERIOD');
740: -- hr_utility.raise_error;
741:
742: END;
743:
744: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
742: END;
743:
744: ELSIF UPPER(p_inpval_name) = 'RATE' THEN
745: begin
746: hr_utility.set_location('calculate_period_earnings', 13);
747: SELECT IPV.input_value_id
748: INTO v_inpval_id
749: FROM pay_input_values_f IPV,
750: pay_element_types_f ELT
756: --
757: v_pay_basis := 'HOURLY';
758: --
759: EXCEPTION WHEN NO_DATA_FOUND THEN
760: hr_utility.set_location('calculate_period_earnings', 14);
761: v_period_earn := 0;
762: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
763: RETURN v_period_earn;
764: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
760: hr_utility.set_location('calculate_period_earnings', 14);
761: v_period_earn := 0;
762: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
763: RETURN v_period_earn;
764: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
765: -- hr_utility.raise_error;
766: end;
767: --
768: ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
761: v_period_earn := 0;
762: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
763: RETURN v_period_earn;
764: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATE_NOTFOUND');
765: -- hr_utility.raise_error;
766: end;
767: --
768: ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
769: begin
766: end;
767: --
768: ELSIF UPPER(p_inpval_name) = 'RATE CODE' THEN
769: begin
770: hr_utility.set_location('calculate_period_earnings', 15);
771: SELECT IPV.input_value_id
772: INTO v_inpval_id
773: FROM pay_input_values_f IPV,
774: pay_element_types_f ELT
780: --
781: v_pay_basis := 'HOURLY';
782: --
783: EXCEPTION WHEN NO_DATA_FOUND THEN
784: hr_utility.set_location('calculate_period_earnings', 16);
785: v_period_earn := 0;
786: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
787: RETURN v_period_earn;
788: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
784: hr_utility.set_location('calculate_period_earnings', 16);
785: v_period_earn := 0;
786: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
787: RETURN v_period_earn;
788: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
789: -- hr_utility.raise_error;
790: end;
791: --
792: ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
785: v_period_earn := 0;
786: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
787: RETURN v_period_earn;
788: -- hr_utility.set_message(801, 'PAY_xxx_REGWAGE_RATECODE_NOTFOUND');
789: -- hr_utility.raise_error;
790: end;
791: --
792: ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
793: begin
790: end;
791: --
792: ELSIF UPPER(p_inpval_name) = 'MONTHLY SALARY' THEN
793: begin
794: hr_utility.set_location('calculate_period_earnings', 17);
795: SELECT IPV.input_value_id
796: INTO v_inpval_id
797: FROM pay_input_values_f IPV,
798: pay_element_types_f ELT
804:
805: v_pay_basis := 'MONTHLY';
806:
807: EXCEPTION WHEN NO_DATA_FOUND THEN
808: hr_utility.set_location('calculate_period_earnings', 18);
809: v_period_earn := 0;
810: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
811: RETURN v_period_earn;
812: -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
808: hr_utility.set_location('calculate_period_earnings', 18);
809: v_period_earn := 0;
810: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
811: RETURN v_period_earn;
812: -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
813: -- hr_utility.raise_error;
814: END;
815:
816: END IF;
809: v_period_earn := 0;
810: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
811: RETURN v_period_earn;
812: -- hr_utility.set_message(801, 'PAY_xxx_REGSAL_NOTFOUND');
813: -- hr_utility.raise_error;
814: END;
815:
816: END IF;
817:
849: -- Find # of payroll period types per fiscal year:
850:
851: begin
852:
853: hr_utility.set_location('calculate_period_earnings', 40);
854: SELECT TPT.number_per_fiscal_year
855: INTO v_pay_periods_per_year
856: FROM pay_payrolls_f PRL,
857: per_time_period_types TPT
862: AND PRL.business_group_id + 0 = p_bus_grp_id;
863:
864: exception when NO_DATA_FOUND then
865:
866: hr_utility.set_location('calculate_period_earnings', 41);
867: v_period_earn := 0;
868: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
869: RETURN v_period_earn;
870: -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
866: hr_utility.set_location('calculate_period_earnings', 41);
867: v_period_earn := 0;
868: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
869: RETURN v_period_earn;
870: -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
871: -- hr_utility.raise_error;
872:
873: end;
874:
867: v_period_earn := 0;
868: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
869: RETURN v_period_earn;
870: -- hr_utility.set_message(801, 'PAY_xxxx_NUM_PER_FY_NOT_FOUND');
871: -- hr_utility.raise_error;
872:
873: end;
874:
875: /*
884: -- even when using work schedule - ie. need to find ACTUAL scheduled hours, not
885: -- actual hours for a week, converted to a period figure.
886: */
887: --
888: hr_utility.set_location('calculate_period_earnings', 45);
889: /* IF p_work_schedule <> 'NOT ENTERED' THEN
890: --
891: -- Find hours worked between period start and end dates.
892: --
902: AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
903: AND NVL(legislation_code,'CA') = 'CA';
904: --
905: */
906: hr_utility.set_location('calling core udfs', 45);
907: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
908: p_assignment_action_id
909: ,p_asst_id
910: ,p_bus_grp_id
935: -- Convert HOURLY earnings to PERIOD earnings.
936: -- Passing "NULL" freq to Convert_Period_Type will convert
937: -- to/from the payroll time period type.
938:
939: hr_utility.set_location('calculate_period_earnings', 46);
940: v_period_earn := Convert_Period_Type( p_bus_grp_id,
941: p_payroll_id,
942: p_assignment_action_id,
943: p_asst_id ,
951: p_period_start,
952: p_period_end,
953: p_asst_std_freq);
954:
955: hr_utility.set_location('calculate_period_earnings', 47);
956:
957: p_actual_hours_worked := v_hrs_per_range;
958: hr_utility.set_location('Calc_PE actual_hours_worked = ', p_actual_hours_worked);
959:
954:
955: hr_utility.set_location('calculate_period_earnings', 47);
956:
957: p_actual_hours_worked := v_hrs_per_range;
958: hr_utility.set_location('Calc_PE actual_hours_worked = ', p_actual_hours_worked);
959:
960: -- Check that Pro-rate = 'Y' before continuing.
961:
962: IF p_prorate = 'N' THEN
961:
962: IF p_prorate = 'N' THEN
963:
964: -- Done!!! No pro-ration...
965: hr_utility.set_location('calculate_period_earnings', 49);
966: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
967:
968: RETURN v_period_earn;
969:
976: -- (ASG1) Select for SINGLE record that includes Period Start Date but does not
977: -- span entire period. If no row returned, then ASG record spans period
978: -- and there is no need to run selects (ASG2) or (ASG3).
979:
980: hr_utility.set_location('calculate_period_earnings', 51);
981:
982: /* ************************************************************** */
983:
984: begin
1012: -- for future reference:
1013:
1014: l_mid_period_asg_change := TRUE;
1015:
1016: hr_utility.set_location('calculate_period_earnings', 56);
1017:
1018: begin
1019:
1020: select 1 INTO l_eev_info_changes
1058: -- (ASG1.1)
1059: -- Actually need to select for ASG record that STARTS within period
1060: -- ie. becomes active on a date later than start date!
1061:
1062: hr_utility.set_location('calculate_period_earnings', 52);
1063:
1064: begin
1065:
1066: select 1 INTO l_asg_info_changes
1084:
1085: -- Need to prorate b/c of mid period ASG changes, but let's look for
1086: -- EEV changes for future reference:
1087:
1088: hr_utility.set_location('calculate_period_earnings', 55);
1089:
1090: begin
1091:
1092: select 1 INTO l_eev_info_changes
1131: -- and we can stop now!
1132:
1133: begin
1134:
1135: hr_utility.set_location('calculate_period_earnings', 53);
1136:
1137: select 1 INTO l_eev_info_changes
1138: from dual
1139: where exists (
1245: -- you would not have an assignment record to 'Process' that spans the
1246: -- Start Date of the period!
1247:
1248: p_actual_hours_worked := 0;
1249: hr_utility.set_location('calculate_period_earnings', 70);
1250: -- IF NOT l_mid_period_asg_change THEN
1251:
1252: begin
1253:
1250: -- IF NOT l_mid_period_asg_change THEN
1251:
1252: begin
1253:
1254: hr_utility.set_location('calculate_period_earnings', 71);
1255: SELECT GREATEST(ASG.effective_start_date, p_period_start),
1256: ASG.effective_end_date,
1257: NVL(ASG.NORMAL_HOURS, 0),
1258: NVL(HRL.meaning, 'NOT ENTERED'),
1289:
1290: -- Prorate using hourly rate passed in as param:
1291: -- Should this become a function from here...
1292:
1293: hr_utility.set_location('calculate_period_earnings', 72);
1294: v_prorated_earnings := v_prorated_earnings +
1295: Prorate_Earnings (
1296: p_bg_id => p_bus_grp_id,
1297: p_assignment_id => p_asst_id,
1305: p_range_start_date => v_range_start,
1306: p_range_end_date => v_range_end,
1307: p_act_hrs_worked => p_actual_hours_worked);
1308:
1309: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1310:
1311: -- Just in case mid period change does not span Start of Period!
1312:
1313: EXCEPTION WHEN NO_DATA_FOUND THEN
1322: -- including Period End Date.
1323: -- Not BETWEEN Period Start/End since we already found a record including
1324: -- Start Date in select (1) above.
1325:
1326: hr_utility.set_location('calculate_period_earnings', 77);
1327: OPEN get_asst_chgs; -- SELECT (ASG2)
1328: LOOP
1329:
1330: FETCH get_asst_chgs
1335: v_asst_std_freq_code,
1336: v_work_schedule;
1337: EXIT WHEN get_asst_chgs%NOTFOUND;
1338:
1339: hr_utility.set_location('calculate_period_earnings', 79);
1340:
1341: -- For each range of dates found, add to running prorated earnings total.
1342:
1343: hr_utility.set_location('calculate_period_earnings', 81);
1339: hr_utility.set_location('calculate_period_earnings', 79);
1340:
1341: -- For each range of dates found, add to running prorated earnings total.
1342:
1343: hr_utility.set_location('calculate_period_earnings', 81);
1344: v_prorated_earnings := v_prorated_earnings +
1345: Prorate_Earnings (
1346: p_bg_id => p_bus_grp_id,
1347: p_assignment_id => p_asst_id,
1355: p_range_start_date => v_range_start,
1356: p_range_end_date => v_range_end,
1357: p_act_hrs_worked => p_actual_hours_worked);
1358:
1359: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1360:
1361: END LOOP;
1362:
1363: CLOSE get_asst_chgs;
1368: -- Effective End Date = Period End Date !
1369:
1370: begin
1371:
1372: hr_utility.set_location('calculate_period_earnings', 89);
1373: SELECT ASG.effective_start_date,
1374: LEAST(ASG.effective_end_date, p_period_end),
1375: NVL(ASG.normal_hours, 0),
1376: NVL(HRL.meaning, 'NOT ENTERED'),
1404: -- Changing ASG status check to be for Payroll Status of 'Process'
1405: -- AND AST.per_system_status = 'ACTIVE_ASSIGN'
1406: -- AND AST.pay_system_status = 'P'
1407:
1408: hr_utility.set_location('calculate_period_earnings', 91);
1409: v_prorated_earnings := v_prorated_earnings +
1410: Prorate_Earnings (
1411: p_bg_id => p_bus_grp_id,
1412: p_assignment_id => p_asst_id,
1420: p_range_start_date => v_range_start,
1421: p_range_end_date => v_range_end,
1422: p_act_hrs_worked => p_actual_hours_worked);
1423:
1424: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1425:
1426: -- We're done!
1427:
1428: hr_utility.set_location('calculate_period_earnings', 101);
1424: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1425:
1426: -- We're done!
1427:
1428: hr_utility.set_location('calculate_period_earnings', 101);
1429:
1430: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1431: RETURN v_prorated_earnings;
1432:
1431: RETURN v_prorated_earnings;
1432:
1433: EXCEPTION WHEN NO_DATA_FOUND THEN
1434: -- (ASG3) returned no rows, but we're done anyway!
1435: hr_utility.set_location('calculate_period_earnings', 102);
1436:
1437: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1438: RETURN v_prorated_earnings;
1439:
1440: END;
1441:
1442: ELSIF (l_asg_info_changes = 0) AND (l_eev_info_changes > 0) THEN
1443:
1444: hr_utility.set_location('calculate_period_earnings', 103);
1445: p_actual_hours_worked := 0;
1446: v_prorated_earnings := v_prorated_earnings +
1447: Prorate_EEV (
1448: p_bus_group_id => p_bus_grp_id,
1461: p_element_entry_id => p_ele_entry_id,
1462: p_inpval_id => v_inpval_id);
1463:
1464: -- We're Done!
1465: hr_utility.set_location('calculate_period_earnings', 127);
1466:
1467: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1468: RETURN v_prorated_earnings;
1469:
1489: -- NOT! v_curr_hrly_rate := p_ass_hrly_figure;
1490:
1491: begin
1492:
1493: hr_utility.set_location('calculate_period_earnings', 128);
1494: SELECT EEV.screen_entry_value
1495: INTO v_earnings_entry
1496: FROM pay_element_entry_values_f EEV
1497: WHERE EEV.element_entry_id = p_ele_entry_id
1534: AND SCL.enabled_flag = 'Y'
1535: AND HRL.lookup_code(+) = ASG.frequency
1536: AND HRL.lookup_type(+) = 'FREQUENCY';
1537:
1538: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1539: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1540: hr_utility.set_location('calling core udfs', 48);
1541: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1542: p_bus_grp_id
1535: AND HRL.lookup_code(+) = ASG.frequency
1536: AND HRL.lookup_type(+) = 'FREQUENCY';
1537:
1538: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1539: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1540: hr_utility.set_location('calling core udfs', 48);
1541: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1542: p_bus_grp_id
1543: ,p_asst_id
1536: AND HRL.lookup_type(+) = 'FREQUENCY';
1537:
1538: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1539: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1540: hr_utility.set_location('calling core udfs', 48);
1541: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1542: p_bus_grp_id
1543: ,p_asst_id
1544: ,p_payroll_id
1545: ,p_ele_entry_id
1546: ,p_date_earned
1547: ,p_assignment_action_id );
1548:
1549: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1550: hr_utility.set_location('calculate_period_earnings', 130);
1551: -- IF NOT l_mid_period_asg_change THEN
1552:
1553: -- Do not have to do this first bit if Mid period hire/active asg occurs:
1546: ,p_date_earned
1547: ,p_assignment_action_id );
1548:
1549: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1550: hr_utility.set_location('calculate_period_earnings', 130);
1551: -- IF NOT l_mid_period_asg_change THEN
1552:
1553: -- Do not have to do this first bit if Mid period hire/active asg occurs:
1554:
1559:
1560: -- (EEV1) Select for SINGLE record that includes Period Start Date but
1561: -- does not span entire period.
1562:
1563: hr_utility.set_location('calculate_period_earnings', 132);
1564: SELECT COUNT(EEV.element_entry_value_id)
1565: INTO l_eev_info_changes
1566: FROM pay_element_entry_values_f EEV
1567: WHERE EEV.element_entry_id = p_ele_entry_id
1571: AND EEV.effective_end_date < v_range_end;
1572:
1573: IF l_eev_info_changes = 0 THEN
1574: -- Prorate using latest hourly rate:
1575: hr_utility.set_location('calculate_period_earnings', 128);
1576: SELECT EEV.screen_entry_value
1577: INTO v_earnings_entry
1578: FROM pay_element_entry_values_f EEV
1579: WHERE EEV.element_entry_id = p_ele_entry_id
1580: AND EEV.input_value_id = v_inpval_id
1581: AND v_range_end BETWEEN EEV.effective_start_date
1582: AND EEV.effective_end_date;
1583:
1584: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1585: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1586: hr_utility.set_location('calling core udfs', 49);
1587: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1588: p_bus_grp_id
1581: AND v_range_end BETWEEN EEV.effective_start_date
1582: AND EEV.effective_end_date;
1583:
1584: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1585: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1586: hr_utility.set_location('calling core udfs', 49);
1587: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1588: p_bus_grp_id
1589: ,p_asst_id
1582: AND EEV.effective_end_date;
1583:
1584: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1585: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1586: hr_utility.set_location('calling core udfs', 49);
1587: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1588: p_bus_grp_id
1589: ,p_asst_id
1590: ,p_payroll_id
1591: ,p_ele_entry_id
1592: ,p_date_earned
1593: ,p_assignment_action_id );
1594:
1595: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1596: -- Should this become a function from here...
1597: hr_utility.set_location('calculate_period_earnings', 133);
1598: v_prorated_earnings := v_prorated_earnings +
1599: Prorate_Earnings (
1593: ,p_assignment_action_id );
1594:
1595: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1596: -- Should this become a function from here...
1597: hr_utility.set_location('calculate_period_earnings', 133);
1598: v_prorated_earnings := v_prorated_earnings +
1599: Prorate_Earnings (
1600: p_bg_id => p_bus_grp_id,
1601: p_assignment_id => p_asst_id,
1609: p_range_start_date => v_range_start,
1610: p_range_end_date => v_range_end,
1611: p_act_hrs_worked => p_actual_hours_worked);
1612:
1613: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1614:
1615: ELSE
1616: -- Do proration for this ASG range by EEV !
1617:
1614:
1615: ELSE
1616: -- Do proration for this ASG range by EEV !
1617:
1618: hr_utility.set_location('calculate_period_earnings', 134);
1619: v_prorated_earnings := v_prorated_earnings +
1620: Prorate_EEV (
1621: p_bus_group_id => p_bus_grp_id,
1622: p_assignment_id => p_asst_id,
1648: -- including Period End Date.
1649: -- Not BETWEEN Period Start/End since we already found a record including
1650: -- Start Date in select (1) above.
1651:
1652: hr_utility.set_location('calculate_period_earnings', 135);
1653: OPEN get_asst_chgs; -- SELECT (ASG2)
1654: LOOP
1655:
1656: FETCH get_asst_chgs
1661: v_asst_std_freq_code,
1662: v_work_schedule;
1663: EXIT WHEN get_asst_chgs%NOTFOUND;
1664:
1665: hr_utility.set_location('calculate_period_earnings', 79);
1666:
1667: -- For each range of dates found, add to running prorated earnings total.
1668:
1669: -- Check for EEV changes, if also none -> return v_period_earn and done:
1671: -- does not span entire period. If no row returned, then EEV record
1672: -- spans period and there is no need to run selects (EEV2) or (EEV3)
1673: -- and we can stop now!
1674:
1675: hr_utility.set_location('calculate_period_earnings', 133);
1676: SELECT COUNT(EEV.element_entry_value_id)
1677: INTO l_eev_info_changes
1678: FROM pay_element_entry_values_f EEV
1679: WHERE EEV.element_entry_id = p_ele_entry_id
1683: AND EEV.effective_end_date < v_range_end;
1684:
1685: IF l_eev_info_changes = 0 THEN
1686: -- Prorate using latest hourly rate:
1687: hr_utility.set_location('calculate_period_earnings', 128);
1688: SELECT EEV.screen_entry_value
1689: INTO v_earnings_entry
1690: FROM pay_element_entry_values_f EEV
1691: WHERE EEV.element_entry_id = p_ele_entry_id
1692: AND EEV.input_value_id = v_inpval_id
1693: AND v_range_end BETWEEN EEV.effective_start_date
1694: AND EEV.effective_end_date;
1695: --
1696: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1697: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1698:
1699: hr_utility.set_location('calling core udfs', 50);
1700: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1693: AND v_range_end BETWEEN EEV.effective_start_date
1694: AND EEV.effective_end_date;
1695: --
1696: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1697: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1698:
1699: hr_utility.set_location('calling core udfs', 50);
1700: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1701: p_bus_grp_id
1695: --
1696: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1697: hr_utility.set_location('v_asst_std_hrs=', v_asst_std_hrs);
1698:
1699: hr_utility.set_location('calling core udfs', 50);
1700: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1701: p_bus_grp_id
1702: ,p_asst_id
1703: ,p_payroll_id
1704: ,p_ele_entry_id
1705: ,p_date_earned
1706: ,p_assignment_action_id );
1707: --
1708: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1709: hr_utility.set_location('calculate_period_earnings', 91);
1710: v_prorated_earnings := v_prorated_earnings +
1711: Prorate_Earnings (
1712: p_bg_id => p_bus_grp_id,
1705: ,p_date_earned
1706: ,p_assignment_action_id );
1707: --
1708: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1709: hr_utility.set_location('calculate_period_earnings', 91);
1710: v_prorated_earnings := v_prorated_earnings +
1711: Prorate_Earnings (
1712: p_bg_id => p_bus_grp_id,
1713: p_assignment_id => p_asst_id,
1721: p_range_start_date => v_range_start,
1722: p_range_end_date => v_range_end,
1723: p_act_hrs_worked => p_actual_hours_worked);
1724: --
1725: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1726: --
1727: ELSE
1728: -- Do proration for this ASG range by EEV !
1729: --
1757: -- Effective End Date = Period End Date !
1758:
1759: begin
1760:
1761: hr_utility.set_location('calculate_period_earnings', 129);
1762: SELECT ASG.effective_start_date,
1763: LEAST(ASG.effective_end_date, p_period_end),
1764: NVL(ASG.normal_hours, 0),
1765: NVL(HRL.meaning, 'NOT ENTERED'),
1800: -- spans period and there is no need to run selects (EEV2) or (EEV3)
1801: -- and we can stop now! Remember, if eev spans period then get
1802: -- eev.screen_entry_value that spans period (ie latest hrly rate).
1803:
1804: hr_utility.set_location('calculate_period_earnings', 133);
1805: SELECT COUNT(EEV.element_entry_value_id)
1806: INTO l_eev_info_changes
1807: FROM pay_element_entry_values_f EEV
1808: WHERE EEV.element_entry_id = p_ele_entry_id
1812: AND EEV.effective_end_date < v_range_end;
1813:
1814: IF l_eev_info_changes = 0 THEN
1815: -- Prorate using latest hourly rate (ie. rate as of end of period):
1816: hr_utility.set_location('calculate_period_earnings', 128);
1817: SELECT EEV.screen_entry_value
1818: INTO v_earnings_entry
1819: FROM pay_element_entry_values_f EEV
1820: WHERE EEV.element_entry_id = p_ele_entry_id
1821: AND EEV.input_value_id = v_inpval_id
1822: AND v_range_end BETWEEN EEV.effective_start_date
1823: AND EEV.effective_end_date;
1824:
1825: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1826: hr_utility.set_location('calling core udfs', 51);
1827: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1828: p_bus_grp_id
1829: ,p_asst_id
1822: AND v_range_end BETWEEN EEV.effective_start_date
1823: AND EEV.effective_end_date;
1824:
1825: hr_utility.set_location('v_earnings_entry='||v_earnings_entry, 129);
1826: hr_utility.set_location('calling core udfs', 51);
1827: v_curr_hrly_rate := PAY_CORE_FF_UDFS.get_hourly_rate(
1828: p_bus_grp_id
1829: ,p_asst_id
1830: ,p_payroll_id
1831: ,p_ele_entry_id
1832: ,p_date_earned
1833: ,p_assignment_action_id );
1834:
1835: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1836: hr_utility.set_location('calculate_period_earnings', 130);
1837:
1838: hr_utility.set_location('calculate_period_earnings', 137);
1839: v_prorated_earnings := v_prorated_earnings +
1832: ,p_date_earned
1833: ,p_assignment_action_id );
1834:
1835: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1836: hr_utility.set_location('calculate_period_earnings', 130);
1837:
1838: hr_utility.set_location('calculate_period_earnings', 137);
1839: v_prorated_earnings := v_prorated_earnings +
1840: Prorate_Earnings (
1834:
1835: hr_utility.set_location('v_curr_hrly_rate = ', v_curr_hrly_rate);
1836: hr_utility.set_location('calculate_period_earnings', 130);
1837:
1838: hr_utility.set_location('calculate_period_earnings', 137);
1839: v_prorated_earnings := v_prorated_earnings +
1840: Prorate_Earnings (
1841: p_bg_id => p_bus_grp_id,
1842: p_assignment_id => p_asst_id,
1850: p_range_start_date => v_range_start,
1851: p_range_end_date => v_range_end,
1852: p_act_hrs_worked => p_actual_hours_worked);
1853:
1854: hr_utility.set_location('Calculate_Period_Earnings.v_prorated_earnings = ', v_prorated_earnings);
1855:
1856: ELSE
1857: -- Do proration for this ASG range by EEV !
1858:
1855:
1856: ELSE
1857: -- Do proration for this ASG range by EEV !
1858:
1859: hr_utility.set_location('calculate_period_earnings', 139);
1860: v_prorated_earnings := v_prorated_earnings +
1861: Prorate_EEV (
1862: p_bus_group_id => p_bus_grp_id,
1863: p_assignment_id => p_asst_id,
1878: END IF;
1879:
1880: -- We're done!
1881:
1882: hr_utility.set_location('calculate_period_earnings', 141);
1883:
1884: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1885: RETURN v_prorated_earnings;
1886:
1885: RETURN v_prorated_earnings;
1886:
1887: EXCEPTION WHEN NO_DATA_FOUND THEN
1888: -- (ASG3.2) returned no rows, but we're done anyway!
1889: hr_utility.set_location('calculate_period_earnings', 142);
1890:
1891: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1892: RETURN v_prorated_earnings;
1893:
1896: END IF;
1897:
1898: EXCEPTION
1899: WHEN NO_DATA_FOUND THEN
1900: hr_utility.set_location('calculate_period_earnings', 190);
1901:
1902: p_actual_hours_worked := ROUND(p_actual_hours_worked, 3);
1903:
1904: RETURN v_prorated_earnings;
1926:
1927: BEGIN -- standard_hours_worked
1928:
1929: -- Check for valid range
1930: hr_utility.set_location('standard_hours_worked', 5);
1931: IF p_range_start > p_range_end THEN
1932: hr_utility.set_location('standard_hours_worked', 7);
1933: RETURN v_total_hours;
1934: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1928:
1929: -- Check for valid range
1930: hr_utility.set_location('standard_hours_worked', 5);
1931: IF p_range_start > p_range_end THEN
1932: hr_utility.set_location('standard_hours_worked', 7);
1933: RETURN v_total_hours;
1934: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1935: -- hr_utility.raise_error;
1936: END IF;
1930: hr_utility.set_location('standard_hours_worked', 5);
1931: IF p_range_start > p_range_end THEN
1932: hr_utility.set_location('standard_hours_worked', 7);
1933: RETURN v_total_hours;
1934: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1935: -- hr_utility.raise_error;
1936: END IF;
1937: --
1938:
1931: IF p_range_start > p_range_end THEN
1932: hr_utility.set_location('standard_hours_worked', 7);
1933: RETURN v_total_hours;
1934: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1935: -- hr_utility.raise_error;
1936: END IF;
1937: --
1938:
1939: IF UPPER(p_std_freq) = 'W' THEN
1947: END IF;
1948:
1949: v_curr_date := p_range_start;
1950:
1951: hr_utility.set_location('standard_hours_worked', 10);
1952:
1953: hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1954: hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1955: LOOP
1949: v_curr_date := p_range_start;
1950:
1951: hr_utility.set_location('standard_hours_worked', 10);
1952:
1953: hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1954: hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1955: LOOP
1956:
1957: v_day_no := TO_CHAR(v_curr_date, 'D');
1950:
1951: hr_utility.set_location('standard_hours_worked', 10);
1952:
1953: hr_utility.trace('p_range_start is'|| to_char(p_range_start));
1954: hr_utility.trace('p_range_end is'|| to_char(p_range_end));
1955: LOOP
1956:
1957: v_day_no := TO_CHAR(v_curr_date, 'D');
1958:
1955: LOOP
1956:
1957: v_day_no := TO_CHAR(v_curr_date, 'D');
1958:
1959: hr_utility.set_location('standard_hours_worked', 15);
1960:
1961: IF v_day_no > 1 and v_day_no < 7 then
1962:
1963: v_total_hours := v_total_hours + v_wrkday_hours;
1960:
1961: IF v_day_no > 1 and v_day_no < 7 then
1962:
1963: v_total_hours := v_total_hours + v_wrkday_hours;
1964: hr_utility.set_location('standard_hours_worked v_total_hours = ', v_total_hours);
1965: END IF;
1966: v_curr_date := v_curr_date + 1;
1967: EXIT WHEN v_curr_date > p_range_end;
1968: END LOOP;
1966: v_curr_date := v_curr_date + 1;
1967: EXIT WHEN v_curr_date > p_range_end;
1968: END LOOP;
1969: --
1970: hr_utility.set_location('v_total_hours is', to_number(v_total_hours));
1971: RETURN v_total_hours;
1972: --
1973: END standard_hours_worked;
1974: --
2041: -- Selecting "count" because we want to continue processing even if
2042: -- the from_freq is not a pay basis.
2043: --
2044:
2045: --hr_utility.trace_on('Y', 'ORACLE');
2046: hr_utility.set_location('Get_Annualizing_Factor', 1);
2047:
2048: begin -- Is Freq pay basis?
2049:
2042: -- the from_freq is not a pay basis.
2043: --
2044:
2045: --hr_utility.trace_on('Y', 'ORACLE');
2046: hr_utility.set_location('Get_Annualizing_Factor', 1);
2047:
2048: begin -- Is Freq pay basis?
2049:
2050: --
2051: -- Decode pay basis and set v_annualizing_factor accordingly.
2052: -- PAY_BASIS "Meaning" is passed from FF !
2053: --
2054:
2055: hr_utility.set_location('Get_Annualizing_Factor', 13);
2056:
2057: SELECT lookup_code
2058: INTO v_pay_basis
2059: FROM hr_lookups lkp
2062: AND lkp.lookup_code = p_freq;
2063:
2064: v_pay_basis := p_freq;
2065:
2066: hr_utility.trace('v_pay_basis is'|| v_pay_basis);
2067: hr_utility.set_location('Get_Annualizing_Factor', 15);
2068: v_use_pay_basis := 1;
2069:
2070: IF v_pay_basis = 'MONTHLY' THEN
2063:
2064: v_pay_basis := p_freq;
2065:
2066: hr_utility.trace('v_pay_basis is'|| v_pay_basis);
2067: hr_utility.set_location('Get_Annualizing_Factor', 15);
2068: v_use_pay_basis := 1;
2069:
2070: IF v_pay_basis = 'MONTHLY' THEN
2071:
2068: v_use_pay_basis := 1;
2069:
2070: IF v_pay_basis = 'MONTHLY' THEN
2071:
2072: hr_utility.set_location('Get_Annualizing_Factor', 17);
2073: v_annualizing_factor := 12;
2074:
2075: ELSIF v_pay_basis = 'HOURLY' THEN
2076:
2073: v_annualizing_factor := 12;
2074:
2075: ELSIF v_pay_basis = 'HOURLY' THEN
2076:
2077: hr_utility.set_location('Get_Annualizing_Factor', 19);
2078:
2079: IF p_period_start_date IS NOT NULL THEN
2080: v_range_start := p_period_start_date;
2081: v_range_end := p_period_end_date;
2100: WHERE user_column_id = v_ws_id
2101: AND NVL(business_group_id, p_bg) = p_bg
2102: AND NVL(legislation_code,'CA') = 'CA';
2103:
2104: hr_utility.set_location('Get_Annualizing_Factor', 21);
2105: */
2106:
2107: hr_utility.set_location('calling core udfs', 52);
2108: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2103:
2104: hr_utility.set_location('Get_Annualizing_Factor', 21);
2105: */
2106:
2107: hr_utility.set_location('calling core udfs', 52);
2108: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2109: p_assignment_action_id
2110: ,p_assignment_id
2111: ,p_bg
2123: ,v_return_message);
2124:
2125: /* ELSE-- Hourly emp using Standard Hours on asg.
2126:
2127: hr_utility.set_location('Get_Annualizing_Factor', 23);
2128:
2129:
2130: v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2131: v_range_start,
2150: v_annualizing_factor := v_hrs_per_range * v_periods_per_fiscal_yr;
2151:
2152: ELSE
2153:
2154: hr_utility.set_location('Get_Annualizing_Factor', 230000);
2155: v_annualizing_factor := v_hrs_per_range * c_weeks_per_year;
2156:
2157: END IF;
2158:
2157: END IF;
2158:
2159: ELSIF v_pay_basis = 'PERIOD' THEN
2160:
2161: hr_utility.set_location('Get_Annualizing_Factor', 25);
2162:
2163: SELECT TPT.number_per_fiscal_year
2164: INTO v_annualizing_factor
2165: FROM pay_payrolls_f PRL,
2171: AND PRL.effective_end_date
2172: AND PRL.payroll_id = p_payroll
2173: AND PRL.business_group_id + 0 = p_bg;
2174:
2175: hr_utility.set_location('Get_Annualizing_Factor', 27);
2176:
2177: ELSIF v_pay_basis = 'ANNUAL' THEN
2178:
2179: hr_utility.set_location('Get_Annualizing_Factor', 97);
2175: hr_utility.set_location('Get_Annualizing_Factor', 27);
2176:
2177: ELSIF v_pay_basis = 'ANNUAL' THEN
2178:
2179: hr_utility.set_location('Get_Annualizing_Factor', 97);
2180:
2181: v_annualizing_factor := 1;
2182:
2183: ELSE
2184:
2185: -- Did not recognize "pay basis", return -999 as annualizing factor.
2186: -- Remember this for debugging when zeroes come out as results!!!
2187:
2188: hr_utility.set_location('Get_Annualizing_Factor', 99);
2189:
2190: v_annualizing_factor := 0;
2191: RETURN v_annualizing_factor;
2192:
2195: exception
2196:
2197: WHEN NO_DATA_FOUND THEN
2198:
2199: hr_utility.set_location('Get_Annualizing_Factor', 101);
2200: v_use_pay_basis := 0;
2201:
2202: end;
2203:
2210: (UPPER(p_freq) = 'NOT ENTERED') THEN
2211:
2212: -- Get "annuallizing factor" from period type of the payroll.
2213:
2214: hr_utility.set_location('Get_Annualizing_Factor', 20);
2215:
2216: SELECT TPT.number_per_fiscal_year
2217: INTO v_annualizing_factor
2218: FROM pay_payrolls_f PRL,
2224: AND PRL.effective_end_date
2225: AND PRL.payroll_id = p_payroll
2226: AND PRL.business_group_id + 0 = p_bg;
2227:
2228: hr_utility.set_location('Get_Annualizing_Factor', 22);
2229:
2230: ELSIF UPPER(p_freq) <> 'HOURLY' THEN
2231:
2232: -- Not hourly, an actual time period type!
2232: -- Not hourly, an actual time period type!
2233:
2234: begin
2235:
2236: hr_utility.set_location('Get_Annualizing_Factor',24);
2237:
2238: SELECT PT.number_per_fiscal_year
2239: INTO v_annualizing_factor
2240: FROM per_time_period_types PT
2243: /* changed for bug 5155854
2244: decode(UPPER(p_freq),'W','WEEK','M','MONTH','D','DAY','Y','YEAR','H','HOUR');
2245: */
2246:
2247: hr_utility.set_location('Get_Annualizing_Factor',26);
2248:
2249: exception when NO_DATA_FOUND then
2250:
2251: -- Added as part of SALLY CLEANUP.
2251: -- Added as part of SALLY CLEANUP.
2252: -- Could have been passed in an ASG_FREQ dbi which might have the values of
2253: -- 'Day' or 'Month' which do not map to a time period type. So we'll do these by hand.
2254:
2255: hr_utility.set_location('Get_Annualizing_Factor',27);
2256: IF UPPER(p_freq) = 'DAY' THEN /* changed D to DAY and M to Month for bug 5155854 */
2257: v_annualizing_factor := c_days_per_year;
2258: ELSIF UPPER(p_freq) = 'MONTH' THEN
2259: v_annualizing_factor := c_months_per_year;
2262: end;
2263:
2264: ELSE -- Hourly employee...
2265:
2266: hr_utility.set_location('Get_Annualizing_Factor', 28);
2267:
2268: IF p_period_start_date IS NOT NULL THEN
2269: v_range_start := p_period_start_date;
2270: v_range_end := p_period_end_date;
2289: WHERE user_column_id = v_ws_id
2290: AND NVL(business_group_id, p_bg) = p_bg
2291: AND NVL(legislation_code,'CA') = 'CA';
2292:
2293: hr_utility.set_location('Get_Annualizing_Factor',30);
2294:
2295: hr_utility.set_location('calling core udfs', 53);
2296: */
2297: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2291: AND NVL(legislation_code,'CA') = 'CA';
2292:
2293: hr_utility.set_location('Get_Annualizing_Factor',30);
2294:
2295: hr_utility.set_location('calling core udfs', 53);
2296: */
2297: v_hrs_per_range := PAY_CORE_FF_UDFS.calculate_actual_hours_worked (
2298: p_assignment_action_id
2299: ,p_assignment_id
2312: ,v_return_message);
2313:
2314: /* ELSE-- Hourly emp using Standard Hours on asg.
2315:
2316: hr_utility.set_location('Get_Annualizing_Factor', 23);
2317:
2318:
2319: v_hrs_per_range := Standard_Hours_Worked( p_asg_std_hrs,
2320: v_range_start,
2347: END IF;
2348:
2349: END IF; -- (v_use_pay_basis = 0)
2350:
2351: hr_utility.set_location('v_annualizing_factor is', to_number(v_annualizing_factor));
2352: RETURN v_annualizing_factor;
2353:
2354: END Get_Annualizing_Factor;
2355:
2355:
2356:
2357: BEGIN -- Convert Figure
2358:
2359: --hr_utility.trace_on('Y', 'ORACLE');
2360: hr_utility.set_location('Convert_Period_Type', 10);
2361:
2362: --
2363: -- If From_Freq and To_Freq are the same, then we're done.
2356:
2357: BEGIN -- Convert Figure
2358:
2359: --hr_utility.trace_on('Y', 'ORACLE');
2360: hr_utility.set_location('Convert_Period_Type', 10);
2361:
2362: --
2363: -- If From_Freq and To_Freq are the same, then we're done.
2364: --
2368: RETURN p_figure;
2369:
2370: END IF;
2371:
2372: hr_utility.set_location('Mita a trace1 ',30000);
2373:
2374: hr_utility.trace('v_from_freq is'|| p_from_freq);
2375: hr_utility.trace('v_to_freq is'|| p_to_freq);
2376:
2370: END IF;
2371:
2372: hr_utility.set_location('Mita a trace1 ',30000);
2373:
2374: hr_utility.trace('v_from_freq is'|| p_from_freq);
2375: hr_utility.trace('v_to_freq is'|| p_to_freq);
2376:
2377: v_from_annualizing_factor := Get_Annualizing_Factor(
2378: p_bg => p_bus_grp_id,
2371:
2372: hr_utility.set_location('Mita a trace1 ',30000);
2373:
2374: hr_utility.trace('v_from_freq is'|| p_from_freq);
2375: hr_utility.trace('v_to_freq is'|| p_to_freq);
2376:
2377: v_from_annualizing_factor := Get_Annualizing_Factor(
2378: p_bg => p_bus_grp_id,
2379: p_payroll => p_payroll_id,
2393: --
2394: -- Annualize "Figure" and convert to To_Freq.
2395: --
2396:
2397: hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2398: hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2399: hr_utility.set_location('Convert_Period_Type', 170);
2400:
2401: IF v_to_annualizing_factor = 0 OR
2394: -- Annualize "Figure" and convert to To_Freq.
2395: --
2396:
2397: hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2398: hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2399: hr_utility.set_location('Convert_Period_Type', 170);
2400:
2401: IF v_to_annualizing_factor = 0 OR
2402: v_to_annualizing_factor = -999 OR
2395: --
2396:
2397: hr_utility.set_location('v_from_annualizing_factor is', to_number(v_from_annualizing_factor));
2398: hr_utility.set_location('v_to_annualizing_factor is', to_number(v_to_annualizing_factor));
2399: hr_utility.set_location('Convert_Period_Type', 170);
2400:
2401: IF v_to_annualizing_factor = 0 OR
2402: v_to_annualizing_factor = -999 OR
2403: v_from_annualizing_factor = -999 THEN
2401: IF v_to_annualizing_factor = 0 OR
2402: v_to_annualizing_factor = -999 OR
2403: v_from_annualizing_factor = -999 THEN
2404:
2405: hr_utility.set_location('Convert_Period_Type', 175);
2406:
2407: v_converted_figure := 0;
2408: RETURN v_converted_figure;
2409:
2408: RETURN v_converted_figure;
2409:
2410: ELSE
2411:
2412: hr_utility.set_location('Convert_Period_Type', 180);
2413: /* hard coded values are for testing */
2414:
2415: -- v_from_annualizing_factor := 12;
2416: -- v_to_annualizing_factor := 1040;
2458:
2459: -- Set range to a single week if no dates are entered:
2460: -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
2461: --
2462: hr_utility.set_location('work_schedule_total_hours setting dates', 3);
2463: v_range_start := NVL(p_range_start, sysdate);
2464: v_range_end := NVL(p_range_end, sysdate + 6);
2465: --
2466: -- END IF;
2464: v_range_end := NVL(p_range_end, sysdate + 6);
2465: --
2466: -- END IF;
2467: -- Check for valid range
2468: hr_utility.set_location('work_schedule_total_hours', 5);
2469: IF v_range_start > v_range_end THEN
2470: --
2471: hr_utility.set_location('work_schedule_total_hours', 7);
2472: RETURN v_total_hours;
2467: -- Check for valid range
2468: hr_utility.set_location('work_schedule_total_hours', 5);
2469: IF v_range_start > v_range_end THEN
2470: --
2471: hr_utility.set_location('work_schedule_total_hours', 7);
2472: RETURN v_total_hours;
2473: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2474: -- hr_utility.raise_error;
2475: --
2469: IF v_range_start > v_range_end THEN
2470: --
2471: hr_utility.set_location('work_schedule_total_hours', 7);
2472: RETURN v_total_hours;
2473: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2474: -- hr_utility.raise_error;
2475: --
2476: END IF;
2477: --
2470: --
2471: hr_utility.set_location('work_schedule_total_hours', 7);
2472: RETURN v_total_hours;
2473: -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
2474: -- hr_utility.raise_error;
2475: --
2476: END IF;
2477: --
2478: -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
2492: -- FROM sys.dual;
2493: --
2494: END IF;
2495: --
2496: hr_utility.set_location('work_schedule_total_hours', 10);
2497: -- Track range dates:
2498: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2499: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2500: --
2494: END IF;
2495: --
2496: hr_utility.set_location('work_schedule_total_hours', 10);
2497: -- Track range dates:
2498: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2499: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2500: --
2501: -- Check if the work schedule is an id or a name. If the work
2502: -- schedule does not exist, then return 0.
2495: --
2496: hr_utility.set_location('work_schedule_total_hours', 10);
2497: -- Track range dates:
2498: hr_utility.set_location('range start = '||to_char(v_range_start), 5);
2499: hr_utility.set_location('range end = '||to_char(v_range_end), 6);
2500: --
2501: -- Check if the work schedule is an id or a name. If the work
2502: -- schedule does not exist, then return 0.
2503: --
2535: end if;
2536: --
2537: v_curr_date := v_range_start;
2538: --
2539: hr_utility.set_location('work_schedule_total_hours curr_date = '||to_char(v_curr_date), 20);
2540: --
2541: LOOP
2542: v_day_no := TO_CHAR(v_curr_date, 'D');
2543:
2540: --
2541: LOOP
2542: v_day_no := TO_CHAR(v_curr_date, 'D');
2543:
2544: hr_utility.set_location('curr_day_no = '||to_char(v_day_no), 20);
2545:
2546: SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
2547: 4,'WED',5,'THU',6,'FRI',7,'SAT')
2548: INTO v_curr_day
2548: INTO v_curr_day
2549: FROM DUAL;
2550:
2551: --
2552: hr_utility.set_location('curr_day = '||v_curr_day, 20);
2553:
2554: --
2555: hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
2556: v_total_hours := v_total_hours +
2551: --
2552: hr_utility.set_location('curr_day = '||v_curr_day, 20);
2553:
2554: --
2555: hr_utility.set_location('work_schedule_total_hours.gettabval', 25);
2556: v_total_hours := v_total_hours +
2557: fnd_number.canonical_to_number(hruserdt.get_table_value(p_bg_id,
2558: c_ws_tab_name,
2559: v_ws_name,
2559: v_ws_name,
2560: v_curr_day));
2561: v_curr_date := v_curr_date + 1;
2562: --
2563: hr_utility.set_location('curr_date = '||to_char(v_curr_date), 20);
2564: --
2565: EXIT WHEN v_curr_date > v_range_end;
2566: --
2567: END LOOP;
2615: v_valid_jurisdiction VARCHAR2(1) := 'E'; -- RETURN var.
2616:
2617: BEGIN
2618:
2619: hr_utility.set_location('Jurisdiction_Validation', 01);
2620:
2621: IF substr(p_jurisdiction_code, 8,4) = '0000' THEN
2622:
2623: IF substr(p_jurisdiction_code, 4,3) = '000' THEN
2659:
2660: EXCEPTION
2661: WHEN NO_DATA_FOUND THEN
2662:
2663: hr_utility.set_location('Jurisdiction_Validation', 03);
2664: v_valid_jurisdiction := 'E';
2665: RETURN v_valid_jurisdiction;
2666:
2667: END us_jurisdiction_val;
2680: --
2681: -- GET
2682: -- This DDF info is held in ELEMENT_INFORMATION3.
2683: --
2684: hr_utility.set_location('get_process_run_flag', 10);
2685: --
2686: begin
2687: SELECT element_information3
2688: INTO v_proc_run_type
2691: BETWEEN effective_start_date
2692: AND effective_end_date
2693: AND element_type_id = p_ele_type_id;
2694: --
2695: hr_utility.set_location('get_process_run_flag', 20);
2696: RETURN v_proc_run_type;
2697: --
2698: exception when NO_DATA_FOUND then
2699: hr_utility.set_location('get_process_run_flag', 30);
2695: hr_utility.set_location('get_process_run_flag', 20);
2696: RETURN v_proc_run_type;
2697: --
2698: exception when NO_DATA_FOUND then
2699: hr_utility.set_location('get_process_run_flag', 30);
2700: RETURN v_proc_run_type;
2701: end;
2702: --
2703: END get_process_run_flag;
2721:
2722: -- Check that
2723: -- This DDF info is held in ELEMENT_INFORMATION3.
2724:
2725: hr_utility.set_location('check_dedn_freq', 10);
2726:
2727: begin
2728: SELECT element_information3
2729: INTO v_proc_run_type
2742: end;
2743: --
2744: -- See if freq rule even comes into play here:
2745: --
2746: hr_utility.set_location('check_dedn_freq', 45);
2747: SELECT COUNT(0)
2748: INTO v_freq_rule_exists
2749: FROM pay_ele_payroll_freq_rules EPF
2750: WHERE element_type_id = p_ele_type_id
2776: -- ...into v_run_number...
2777: -- What we NEED is the actual PERIOD # w/in Month or Year.
2778: --
2779: IF v_number_per_fy < 12 THEN
2780: hr_utility.set_location('check_dedn_freq', 20);
2781:
2782: SELECT COUNT(0)
2783: INTO v_run_number
2784: FROM per_time_periods PTP
2787: AND v_period_end_date
2788: AND PTP.payroll_id = p_payroll_id;
2789:
2790: ELSIF v_number_per_fy > 12 THEN
2791: hr_utility.set_location('check_dedn_freq', 30);
2792:
2793: SELECT COUNT(0)
2794: INTO v_run_number
2795: FROM per_time_periods PTP
2798: AND v_period_end_date
2799: AND PTP.payroll_id = p_payroll_id;
2800:
2801: ELSIF v_number_per_fy = 12 THEN
2802: hr_utility.set_location('check_dedn_freq', 40);
2803: v_skip_element := 'N';
2804: RETURN v_skip_element;
2805: END IF;
2806:
2808: -- Check frequency rule:
2809: -- If none exists, then process!
2810: --
2811:
2812: hr_utility.set_location('check_dedn_freq', 50);
2813: SELECT 'N'
2814: INTO v_skip_element
2815: FROM pay_ele_payroll_freq_rules EPF,
2816: pay_freq_rule_periods FRP
2823: RETURN v_skip_element;
2824:
2825: EXCEPTION
2826: WHEN NO_DATA_FOUND THEN
2827: hr_utility.set_location('check_dedn_freq', 60);
2828: v_skip_element := 'Y';
2829: RETURN v_skip_element;
2830:
2831: END check_dedn_freq;
2862: --
2863: BEGIN -- Separate_Check_Skip
2864: --
2865:
2866: hr_utility.set_location('Separate_Check_Skip', 7);
2867:
2868: SELECT RRV.result_value
2869: INTO v_dedn_proc
2870: FROM pay_run_result_values RRV,
2886: --
2887:
2888: v_skip_element := 'Y';
2889:
2890: hr_utility.set_location('Separate_Check_Skip', 9);
2891:
2892: begin
2893:
2894: SELECT ECL.classification_name
2907: -- Change v_skip_element back to 'N' if this a tax deduction.
2908: -- ie. we know DEDN PROC inpval is not null, meaning it's either TAX ONLY
2909: -- or PRETAX AND TAX ONLY.
2910:
2911: hr_utility.set_location('Separate_Check_Skip', 10);
2912: v_skip_element := 'N';
2913: RETURN v_skip_element;
2914:
2915: ELSIF UPPER(v_ele_class_name) = 'PRE-TAX DEDUCTIONS' AND
2916: v_dedn_proc = 'PTT' THEN
2917:
2918: -- Change v_skip_element back to 'N' if dedn proc = 'PTT'
2919:
2920: hr_utility.set_location('Separate_Check_Skip', 11);
2921: v_skip_element := 'N';
2922: RETURN v_skip_element;
2923:
2924: END IF;
2923:
2924: END IF;
2925:
2926: exception WHEN NO_DATA_FOUND THEN
2927: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2928: v_skip_element := 'Y';
2929: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2930: -- hr_utility.raise_error;
2931: end;
2925:
2926: exception WHEN NO_DATA_FOUND THEN
2927: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2928: v_skip_element := 'Y';
2929: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2930: -- hr_utility.raise_error;
2931: end;
2932:
2933: RETURN v_skip_element;
2926: exception WHEN NO_DATA_FOUND THEN
2927: hr_utility.set_location('Separate_Check_Skip - Error EleClass NOTFOUND', 12);
2928: v_skip_element := 'Y';
2929: -- hr_utility.set_message(801, 'PAY_ELE_CLASS_NOTFOUND');
2930: -- hr_utility.raise_error;
2931: end;
2932:
2933: RETURN v_skip_element;
2934:
2933: RETURN v_skip_element;
2934:
2935: EXCEPTION
2936: WHEN NO_DATA_FOUND THEN
2937: hr_utility.set_location('Separate_Check_Skip', 21);
2938: RETURN v_skip_element;
2939: -- Special Dedn Proc not required. SKIP_FLAG = 'N'.
2940: --
2941: END Separate_Check_Skip;
2958: --
2959:
2960: BEGIN
2961:
2962: hr_utility.set_location('Other_Non_Separate_Check', 10);
2963:
2964: SELECT DECODE(COUNT(IPV.input_value_id), 0, 'N', 'Y')
2965: INTO sepcheck_flag
2966: FROM pay_element_entry_values_f EEV,
2976: AND EEV.input_value_id = IPV.input_value_id
2977: AND UPPER(IPV.name) = 'SEPARATE CHECK';
2978: --
2979: IF sepcheck_flag = 'Y' THEN
2980: hr_utility.set_location('Other_Non_Separate_Check', 15);
2981: RETURN sepcheck_flag;
2982: END IF;
2983: --
2984: hr_utility.set_location('Other_Non_Separate_Check', 20);
2980: hr_utility.set_location('Other_Non_Separate_Check', 15);
2981: RETURN sepcheck_flag;
2982: END IF;
2983: --
2984: hr_utility.set_location('Other_Non_Separate_Check', 20);
2985:
2986: SELECT DECODE(COUNT(ELE.element_entry_id), 0, 'N', 'Y')
2987: INTO sepcheck_flag
2988: FROM pay_element_entries_f ELE,
3016:
3017: --
3018: EXCEPTION
3019: WHEN NO_DATA_FOUND THEN
3020: hr_utility.set_location('Other_Non_Separate_Check', 30);
3021: RETURN sepcheck_flag;
3022: --
3023: END Other_Non_Separate_Check;
3024: --
3276: -- Remember to check for a rate via Rate Code!
3277: --
3278: -- Go ahead and set pay_basis_rate now - will most likely be used somewhere.
3279: --
3280: hr_utility.set_location('OT_Base_Rate', 5);
3281:
3282: select start_date,
3283: end_date
3284: into v_range_start,
3320: --
3321: OPEN get_tew_rate;
3322: --
3323: LOOP
3324: hr_utility.set_location('OT_Base_Rate', 10);
3325: FETCH get_tew_rate
3326: INTO v_tew_rate, v_entry_id;
3327: EXIT WHEN get_tew_rate%NOTFOUND;
3328: --
3329: v_tew_count := v_tew_count + 1;
3330: IF v_tew_rate <> 0 THEN
3331: v_tew_total_rate := v_tew_total_rate + v_tew_rate;
3332: ELSE -- no Rate entered, check Rate Code
3333: hr_utility.set_location('OT_Base_Rate', 15);
3334: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3335: INTO v_tew_rcode
3336: FROM pay_element_entry_values_f EEV,
3337: pay_element_entries_f ELE,
3352: -- Use pay basis salary converted to hourly rate.
3353: v_tew_total_rate := v_tew_total_rate + v_pay_basis_rate;
3354: ELSE
3355: -- Find rate from rate table.
3356: hr_utility.set_location('OT_Base_Rate', 17);
3357: v_tew_total_rate := v_tew_total_rate +
3358: fnd_number.canonical_to_number(hruserdt.get_table_value(
3359: p_bg_id,
3360: c_rate_table_name,
3368: --
3369: CLOSE get_tew_rate;
3370: --
3371: IF v_tew_count = 0 THEN -- ie. only use "Regular" rates if TEW not entered.
3372: hr_utility.set_location('OT_Base_Rate', 20);
3373: SELECT COUNT(IPV.input_value_id)
3374: INTO v_use_regwage
3375: FROM pay_element_entry_values_f EEV,
3376: pay_element_entries_f ELE,
3384: AND IPV.element_type_id = l_reg_wages_ele_id
3385: AND UPPER(IPV.name) = 'RATE';
3386: --
3387: IF v_use_regwage <> 0 THEN
3388: hr_utility.set_location('OT_Base_Rate', 30);
3389: SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
3390: EEV.element_entry_id
3391: INTO v_regwage_rate,
3392: v_entry_id
3402: AND IPV.element_type_id = l_reg_wages_ele_id
3403: AND UPPER(IPV.name) = 'RATE';
3404: --
3405: IF v_regwage_rate = 0 THEN
3406: hr_utility.set_location('OT_Base_Rate', 40);
3407: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3408: INTO v_regwage_rcode
3409: FROM pay_element_entry_values_f EEV,
3410: pay_element_entries_f ELE,
3420: AND UPPER(ELT.element_name) = 'REGULAR WAGES'
3421: AND ELT.element_type_id = IPV.element_type_id
3422: AND UPPER(IPV.name) = 'RATE CODE';
3423: --
3424: hr_utility.set_location('OT_Base_Rate', 41);
3425: v_regwage_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
3426: p_bus_group_id => p_bg_id,
3427: p_table_name => c_rate_table_name,
3428: p_col_name => c_rate_table_column,
3430: END IF;
3431: v_ot_base_rate := v_ot_base_rate + v_regwage_rate;
3432: --
3433: ELSE
3434: hr_utility.set_location('OT_Base_Rate', 50);
3435: SELECT COUNT(IPV.input_value_id)
3436: INTO v_use_regsal
3437: FROM pay_element_entry_values_f EEV,
3438: pay_element_entries_f ELE,
3446: AND IPV.element_type_id = l_reg_sal_ele_id
3447: AND UPPER(IPV.name) = 'MONTHLY SALARY';
3448: --
3449: IF v_use_regsal <> 0 THEN
3450: hr_utility.set_location('OT_Base_Rate', 51);
3451: SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
3452: INTO v_regsal_mosal
3453: FROM pay_element_entry_values_f EEV,
3454: pay_element_entries_f ELE,
3461: AND EEV.input_value_id = IPV.input_value_id
3462: AND IPV.element_type_id = l_reg_sal_ele_id
3463: AND UPPER(IPV.name) = 'MONTHLY SALARY';
3464: --
3465: hr_utility.set_location('OT_Base_Rate', 60);
3466:
3467: v_regsal_rate := hr_ca_ff_udfs.Convert_Period_Type(
3468: p_bus_grp_id => p_bg_id,
3469: p_payroll_id => p_pay_id,
3494: --
3495: OPEN get_include_in_ot;
3496: LOOP
3497: --
3498: hr_utility.set_location('OT_Base_Rate', 70);
3499: FETCH get_include_in_ot
3500: INTO v_eletype_id,
3501: v_ele_name,
3502: v_ff_name;
3503: EXIT WHEN get_include_in_ot%NOTFOUND;
3504: --
3505: IF SUBSTR(v_ff_name,1,11) = 'FLAT_AMOUNT' THEN
3506: -- Find "Amount" entered, convert to hourly figure.
3507: hr_utility.set_location('OT_Base_Rate', 80);
3508: OPEN get_flat_amounts;
3509: LOOP
3510: FETCH get_flat_amounts
3511: INTO v_flat_amount;
3510: FETCH get_flat_amounts
3511: INTO v_flat_amount;
3512: EXIT WHEN get_flat_amounts%NOTFOUND;
3513: v_flat_count := v_flat_count + 1;
3514: hr_utility.set_location('OT_Base_Rate', 90);
3515:
3516: v_flat_total := v_flat_total + hr_ca_ff_udfs.Convert_Period_Type(
3517: p_bus_grp_id => p_bg_id,
3518: p_payroll_id => p_pay_id,
3531: --
3532: END LOOP;
3533: CLOSE get_flat_amounts;
3534: --
3535: hr_utility.set_location('OT_Base_Rate', 100);
3536: v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3537: --
3538: ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3539: hr_utility.set_location('OT_Base_Rate', 110);
3535: hr_utility.set_location('OT_Base_Rate', 100);
3536: v_ot_base_rate := v_ot_base_rate + (v_flat_total / v_flat_count);
3537: --
3538: ELSIF SUBSTR(v_ff_name,1,10) = 'PERCENTAGE' THEN
3539: hr_utility.set_location('OT_Base_Rate', 110);
3540: OPEN get_percentages;
3541: LOOP
3542: FETCH get_percentages
3543: INTO v_percentage;
3544: EXIT WHEN get_percentages%NOTFOUND;
3545: v_pct_count := v_pct_count + 1;
3546: --
3547: IF v_regsal_rate <> 0 THEN
3548: hr_utility.set_location('OT_Base_Rate', 105);
3549: v_pct_total := v_percentage * v_regsal_rate;
3550: END IF;
3551: --
3552: END LOOP;
3552: END LOOP;
3553: --
3554: CLOSE get_percentages;
3555: --
3556: hr_utility.set_location('OT_Base_Rate', 110);
3557: v_ot_base_rate := v_ot_base_rate + (v_pct_total / v_pct_count);
3558: --
3559: ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3560: --
3559: ELSIF SUBSTR(v_ff_name,1,12) = 'HOURS_X_RATE' THEN
3560: --
3561: -- Remember to look for "Rate Code" if necessary and "Multiple" always.
3562: --
3563: hr_utility.set_location('OT_Base_Rate', 115);
3564: OPEN get_rates;
3565: LOOP
3566: FETCH get_rates
3567: INTO v_rate, v_entry_id;
3565: LOOP
3566: FETCH get_rates
3567: INTO v_rate, v_entry_id;
3568: EXIT WHEN get_rates%NOTFOUND;
3569: hr_utility.set_location('OT_Base_Rate', 120);
3570: v_rate_count := v_rate_count + 1;
3571: IF v_rate = 0 THEN
3572: hr_utility.set_location('OT_Base_Rate', 125);
3573: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3568: EXIT WHEN get_rates%NOTFOUND;
3569: hr_utility.set_location('OT_Base_Rate', 120);
3570: v_rate_count := v_rate_count + 1;
3571: IF v_rate = 0 THEN
3572: hr_utility.set_location('OT_Base_Rate', 125);
3573: SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
3574: INTO v_rate_rcode
3575: FROM pay_element_entry_values_f EEV,
3576: pay_element_entries_f ELE,
3587: AND ELT.element_type_id = IPV.element_type_id
3588: AND UPPER(IPV.name) = 'RATE CODE';
3589: --
3590: IF v_rate_rcode <> 'NOT ENTERED' THEN
3591: hr_utility.set_location('OT_Base_Rate', 130);
3592: v_rate := fnd_number.canonical_to_number(hruserdt.get_table_value(
3593: p_bg_id,
3594: c_rate_table_name,
3595: c_rate_table_column,
3598: --
3599: END IF;
3600: -- Now get "Multiple" on this entry, if any.
3601: IF v_rate <> 0 THEN
3602: hr_utility.set_location('OT_Base_Rate', 135);
3603: SELECT COUNT(0)
3604: INTO v_rate_mult_count
3605: FROM pay_element_entry_values_f EEV,
3606: pay_element_entries_f ELE,
3617: AND ELT.element_type_id = IPV.element_type_id
3618: AND UPPER(IPV.name) = 'MULTIPLE';
3619: --
3620: IF v_rate_mult_count <> 0 THEN
3621: hr_utility.set_location('OT_Base_Rate', 140);
3622: SELECT NVL(EEV.screen_entry_value, 0)
3623: INTO v_rate_multiple
3624: FROM pay_element_entry_values_f EEV,
3625: pay_element_entries_f ELE,
3651: --
3652: v_ot_base_rate := v_ot_base_rate + (v_rate_total / v_rate_count);
3653: --
3654: ELSIF SUBSTR(v_ff_name,1,8) = 'GROSS_UP' THEN
3655: hr_utility.set_location('OT_Base_Rate', 150);
3656: OPEN get_grosses;
3657: LOOP
3658: FETCH get_grosses
3659: INTO v_gross_results;
3660: EXIT WHEN get_grosses%NOTFOUND;
3661: v_gross_count := v_gross_count + 1;
3662: IF v_gross_results <> 0 THEN
3663: -- Convert gross result to hourly figure.
3664: hr_utility.set_location('OT_Base_Rate', 160);
3665:
3666: v_gross_total := v_gross_total + hr_ca_ff_udfs.Convert_Period_Type(
3667: p_bus_grp_id => p_bg_id,
3668: p_payroll_id => p_pay_id,
3695: RETURN v_ot_base_rate;
3696: --
3697: EXCEPTION
3698: WHEN NO_DATA_FOUND THEN
3699: hr_utility.set_location('OT_Base_Rate', 170);
3700: RETURN v_ot_base_rate;
3701: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3702: -- hr_utility.raise_error;
3703: --
3697: EXCEPTION
3698: WHEN NO_DATA_FOUND THEN
3699: hr_utility.set_location('OT_Base_Rate', 170);
3700: RETURN v_ot_base_rate;
3701: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3702: -- hr_utility.raise_error;
3703: --
3704: END OT_Base_Rate;
3705: --
3698: WHEN NO_DATA_FOUND THEN
3699: hr_utility.set_location('OT_Base_Rate', 170);
3700: RETURN v_ot_base_rate;
3701: -- hr_utility.set_message(801, 'PAY_SCL_SEG_NOTFOUND');
3702: -- hr_utility.raise_error;
3703: --
3704: END OT_Base_Rate;
3705: --
3706: /*
3806: BEGIN -- Dedn_Freq_Factor
3807: --
3808: -- v_date_earned := p_date_earned;
3809: --
3810: hr_utility.set_location('Dedn_Freq_Factor', 10);
3811: SELECT DECODE(COUNT(FRP.freq_rule_period_id), 0, 'N', 'Y')
3812: INTO v_freq_rules_exist
3813: FROM pay_freq_rule_periods FRP,
3814: pay_ele_payroll_freq_rules EPF
3828: --
3829: --
3830: ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'N' THEN
3831: --
3832: hr_utility.set_location('Dedn_Freq_Factor', 40);
3833: SELECT number_per_fiscal_year
3834: INTO v_ele_period_num_per_fy
3835: FROM per_time_period_types TPT
3836: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3834: INTO v_ele_period_num_per_fy
3835: FROM per_time_period_types TPT
3836: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3837: --
3838: hr_utility.set_location('Dedn_Freq_Factor', 45);
3839: SELECT TPT.number_per_fiscal_year
3840: INTO v_pay_period_num_per_fy
3841: FROM per_time_period_types TPT,
3842: pay_payrolls_f PPF
3849: v_dedn_freq_factor := v_ele_period_num_per_fy / v_pay_period_num_per_fy;
3850: --
3851: ELSIF p_ele_period_type <> 'NOT ENTERED' AND v_freq_rules_exist = 'Y' THEN
3852: --
3853: hr_utility.set_location('Dedn_Freq_Factor', 50);
3854: SELECT number_per_fiscal_year
3855: INTO v_ele_period_num_per_fy
3856: FROM per_time_period_types TPT
3857: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3855: INTO v_ele_period_num_per_fy
3856: FROM per_time_period_types TPT
3857: WHERE UPPER(period_type) = UPPER(p_ele_period_type);
3858: --
3859: hr_utility.set_location('Dedn_Freq_Factor', 55);
3860: SELECT TPT.number_per_fiscal_year
3861: INTO v_pay_period_num_per_fy
3862: FROM pay_payrolls_f PPF,
3863: per_time_period_types TPT
3867: BETWEEN PPF.effective_start_date
3868: AND PPF.effective_end_date;
3869: --
3870: IF v_pay_period_num_per_fy >= 12 THEN
3871: hr_utility.set_location('Dedn_Freq_Factor', 60);
3872: SELECT COUNT(0)
3873: INTO v_pay_periods_in_month
3874: FROM per_time_periods PTP
3875: WHERE PTP.end_date
3880: -- Frequency rules exist, so this select should never return 0.
3881: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
3882: -- ie. so v_dedn_freq_factor will also equal 1.
3883: --
3884: hr_utility.set_location('Dedn_Freq_Factor', 65);
3885: SELECT COUNT(0)
3886: INTO v_pay_periods_in_reset
3887: FROM pay_ele_payroll_freq_rules EPF,
3888: pay_freq_rule_periods FRP
3889: WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
3890: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3891: AND EPF.payroll_id = p_payroll_id
3892: AND EPF.element_type_id = p_element_type_id;
3893: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
3894: --
3895: IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
3896: v_dedn_freq_factor := 1;
3897: ELSIF v_pay_periods_in_reset = 0 THEN
3903: v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
3904: END IF;
3905: --
3906: ELSE
3907: hr_utility.set_location('Dedn_Freq_Factor', 70);
3908: SELECT COUNT(0)
3909: INTO v_pay_periods_in_year
3910: FROM per_time_periods PTP
3911: WHERE PTP.end_date
3916: -- Frequency rules exist, so this select should never return 0.
3917: -- Just in case, we'll decode for 0 and set v_pay_periods_in_reset to 1.
3918: -- ie. so v_dedn_freq_factor will also equal 1.
3919: --
3920: hr_utility.set_location('Dedn_Freq_Factor', 75);
3921: SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
3922: INTO v_pay_periods_in_reset
3923: FROM pay_ele_payroll_freq_rules EPF,
3924: pay_freq_rule_periods FRP
3926: AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
3927: AND EPF.payroll_id = p_payroll_id
3928: AND EPF.element_type_id = p_element_type_id;
3929: --
3930: hr_utility.set_location('v_pay_periods_in_reset = ', v_pay_periods_in_reset);
3931: IF v_ele_period_num_per_fy = v_pay_period_num_per_fy THEN
3932: v_dedn_freq_factor := 1;
3933: ELSE
3934: v_dedn_freq_factor := v_ele_period_num_per_fy / c_months_per_fy / v_pay_periods_in_reset;
3937: END IF;
3938: --
3939: END IF;
3940: --
3941: hr_utility.set_location('Dedn_Freq_Factor', 80);
3942: RETURN v_dedn_freq_factor;
3943: --
3944: END Dedn_Freq_Factor;
3945: --
3981: --
3982: p_to_arrears := 0;
3983: p_not_taken := 0;
3984:
3985: hr_utility.set_location('hr_ca_ff_udfs.arrearage', 1);
3986:
3987: -- Determine if Arrears = 'Y' for this dedn
3988: -- Can do this by checking for "Clear Arrears" input value on base ele.
3989: -- This input value is only created when Arrears is marked Yes on Deductions
3990: -- screen.
3991:
3992: begin
3993:
3994: hr_utility.set_location('Shadow elename = '||v_shadow_ele_name, 38 );
3995:
3996: select 'Y'
3997: into v_arrears_flag
3998: from pay_input_values_f ipv
4003:
4004: exception
4005:
4006: WHEN NO_DATA_FOUND THEN
4007: hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4008: v_arrears_flag := 'N';
4009:
4010: WHEN TOO_MANY_ROWS THEN
4011: hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4007: hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
4008: v_arrears_flag := 'N';
4009:
4010: WHEN TOO_MANY_ROWS THEN
4011: hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
4012: v_arrears_flag := 'N';
4013:
4014: end;
4015:
4019:
4020: p_to_arrears := 0;
4021: p_not_taken := 0;
4022: l_dedn_amt := p_dedn_amt;
4023: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4024: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4025: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4026:
4027: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4020: p_to_arrears := 0;
4021: p_not_taken := 0;
4022: l_dedn_amt := p_dedn_amt;
4023: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4024: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4025: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4026:
4027: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4028: -- Don't take anything, no arrears contr either.
4021: p_not_taken := 0;
4022: l_dedn_amt := p_dedn_amt;
4023: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4024: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', p_dedn_amt);
4025: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4026:
4027: ELSIF p_net_asg_run <= p_guaranteed_net THEN
4028: -- Don't take anything, no arrears contr either.
4029: p_to_arrears := 0;
4028: -- Don't take anything, no arrears contr either.
4029: p_to_arrears := 0;
4030: p_not_taken := p_dedn_amt;
4031: l_dedn_amt := 0;
4032: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4033: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4034: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4035:
4036: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4029: p_to_arrears := 0;
4030: p_not_taken := p_dedn_amt;
4031: l_dedn_amt := 0;
4032: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4033: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4034: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4035:
4036: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4037:
4030: p_not_taken := p_dedn_amt;
4031: l_dedn_amt := 0;
4032: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4033: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4034: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4035:
4036: ELSIF p_net_asg_run - p_dedn_amt < p_guaranteed_net THEN
4037:
4038: IF p_partial_flag = 'Y' THEN
4039: --
4040: p_to_arrears := 0;
4041: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4042: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4043: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4044: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4045: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4046:
4047: ELSE
4040: p_to_arrears := 0;
4041: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4042: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4043: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4044: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4045: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4046:
4047: ELSE
4048:
4041: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4042: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4043: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4044: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4045: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4046:
4047: ELSE
4048:
4049: p_to_arrears := 0;
4048:
4049: p_to_arrears := 0;
4050: p_not_taken := p_dedn_amt;
4051: l_dedn_amt := 0;
4052: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4053: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4054: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4055:
4056: END IF;
4049: p_to_arrears := 0;
4050: p_not_taken := p_dedn_amt;
4051: l_dedn_amt := 0;
4052: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4053: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4054: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4055:
4056: END IF;
4057:
4050: p_not_taken := p_dedn_amt;
4051: l_dedn_amt := 0;
4052: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4053: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4054: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4055:
4056: END IF;
4057:
4058: END IF;
4064: -- Don't take anything, put it all in arrears.
4065: p_to_arrears := p_dedn_amt;
4066: p_not_taken := p_dedn_amt;
4067: l_dedn_amt := 0;
4068: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4069: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4070: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4071:
4072: ELSE
4065: p_to_arrears := p_dedn_amt;
4066: p_not_taken := p_dedn_amt;
4067: l_dedn_amt := 0;
4068: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4069: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4070: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4071:
4072: ELSE
4073:
4066: p_not_taken := p_dedn_amt;
4067: l_dedn_amt := 0;
4068: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4069: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4070: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4071:
4072: ELSE
4073:
4074: l_total_dedn := p_dedn_amt + p_arrears_itd;
4080: -- there's enough net to take it all, clear arrears:
4081: p_to_arrears := -1 * p_arrears_itd;
4082: l_dedn_amt := l_total_dedn;
4083: p_not_taken := 0;
4084: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4085: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4086: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4087:
4088: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4081: p_to_arrears := -1 * p_arrears_itd;
4082: l_dedn_amt := l_total_dedn;
4083: p_not_taken := 0;
4084: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4085: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4086: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4087:
4088: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4089: try and take as much of the total deduction amount (current dedn +
4082: l_dedn_amt := l_total_dedn;
4083: p_not_taken := 0;
4084: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4085: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4086: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4087:
4088: /* Deleted a load of code above to fix 504970. If partial_flag = Y, then
4089: try and take as much of the total deduction amount (current dedn +
4090: arrears) and leave the rest in arrears. */
4100: ELSE
4101: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4102: END IF;
4103: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4104: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4105: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4106: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4107:
4108: ELSE -- p_partial_flag = 'N'
4101: p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
4102: END IF;
4103: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4104: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4105: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4106: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4107:
4108: ELSE -- p_partial_flag = 'N'
4109: IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4102: END IF;
4103: l_dedn_amt := p_net_asg_run - p_guaranteed_net;
4104: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4105: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4106: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4107:
4108: ELSE -- p_partial_flag = 'N'
4109: IF (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt THEN
4110: -- Take the whole deduction amount.
4116: p_to_arrears := p_dedn_amt;
4117: p_not_taken := p_dedn_amt;
4118: l_dedn_amt := 0;
4119: END IF;
4120: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4121: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4122: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4123:
4124: END IF;
4117: p_not_taken := p_dedn_amt;
4118: l_dedn_amt := 0;
4119: END IF;
4120: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4121: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4122: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4123:
4124: END IF;
4125:
4118: l_dedn_amt := 0;
4119: END IF;
4120: -- hr_utility.set_location('pycaudfs.arrearage.to_arrears = ', p_to_arrears);
4121: hr_utility.set_location('pycaudfs.arrearage.dedn_amt = ', l_dedn_amt);
4122: -- hr_utility.set_location('pycaudfs.arrearage.not_taken = ', p_not_taken);
4123:
4124: END IF;
4125:
4126: END IF;
4173: RETURN l_geocode;
4174: --
4175: EXCEPTION
4176: WHEN OTHERS THEN
4177: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 20);
4178: l_geocode := '00-000-0000';
4179: RETURN l_geocode;
4180: --
4181: END addr_val; -- addr_val
4209: IF (p_skip_rule = 'Y') THEN
4210: RAISE TOO_MANY_ROWS;
4211: END IF;
4212:
4213: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 1);
4214:
4215: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4216: INTO l_geocode
4217: FROM pay_us_zip_codes z,
4225: --
4226: EXCEPTION -- (2)
4227: --
4228: WHEN NO_DATA_FOUND THEN -- Invalid city/zip combo
4229: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 3);
4230: l_geocode := '00-000-0000';
4231: RETURN l_geocode;
4232: --
4233: WHEN TOO_MANY_ROWS THEN -- city/zip does not uniquely defn geo
4236: INTO l_state_code
4237: FROM pay_us_states
4238: WHERE state_abbrev = p_state_abbrev;
4239:
4240: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 5);
4241: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4242: INTO l_geocode
4243: FROM pay_us_zip_codes z,
4244: pay_us_city_names a,
4258: --
4259: -- Fallout from (2) ie. county/city/zip combo invalid or does not
4260: -- uniquely define geocode.
4261: WHEN NO_DATA_FOUND THEN
4262: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 7);
4263: l_geocode := '00-000-0000';
4264: RETURN l_geocode;
4265: --
4266: WHEN TOO_MANY_ROWS THEN
4263: l_geocode := '00-000-0000';
4264: RETURN l_geocode;
4265: --
4266: WHEN TOO_MANY_ROWS THEN
4267: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 9);
4268: SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
4269: INTO l_geocode
4270: FROM pay_us_zip_codes z,
4271: pay_us_city_names a,
4289: end; -- (1)
4290: --
4291: -- We're in Main
4292: --
4293: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 11);
4294: --
4295: if (substr(l_geocode,8,1) = 'U') THEN
4296: l_geocode := substr(l_geocode,1,7)||'0000';
4297: END IF;
4301: EXCEPTION -- Main addr_val
4302: -- Fallout from (1) state/county/city/zip does not uniquely define a geo.
4303: -- Return failure geocode.
4304: WHEN NO_DATA_FOUND THEN
4305: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 13);
4306: l_geocode := '00-000-0000';
4307: RETURN l_geocode;
4308: --
4309: WHEN TOO_MANY_ROWS THEN
4306: l_geocode := '00-000-0000';
4307: RETURN l_geocode;
4308: --
4309: WHEN TOO_MANY_ROWS THEN
4310: hr_utility.set_location('hr_ca_ff_udfs.addr_val', 15);
4311: l_geocode := '00-000-0000';
4312: RETURN l_geocode;
4313: --
4314: END addr_val; -- Main addr_val
4367: l_value NUMBER;
4368: l_input_value_name VARCHAR2(200);
4369:
4370: BEGIN
4371: hr_utility.set_location('in get_earnings_and_type',10);
4372: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4368: l_input_value_name VARCHAR2(200);
4369:
4370: BEGIN
4371: hr_utility.set_location('in get_earnings_and_type',10);
4372: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4369:
4370: BEGIN
4371: hr_utility.set_location('in get_earnings_and_type',10);
4372: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4370: BEGIN
4371: hr_utility.set_location('in get_earnings_and_type',10);
4372: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4378: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4371: hr_utility.set_location('in get_earnings_and_type',10);
4372: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4378: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4379:
4372: hr_utility.set_location('p_bus_grp_id -> '|| p_bus_grp_id ,10);
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4378: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4379:
4380: l_value := 0;
4373: hr_utility.set_location(' p_asst_id -> '|| p_asst_id ,10);
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4378: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4379:
4380: l_value := 0;
4381:
4374: hr_utility.set_location(' p_assignment_action_id -> '|| p_assignment_action_id,10);
4375: hr_utility.set_location(' p_date_earned -> '|| p_date_earned ,10);
4376: hr_utility.set_location(' p_period_start -> '|| to_char(p_period_start) ,10);
4377: hr_utility.set_location(' p_period_end -> '|| to_char(p_period_end),10);
4378: hr_utility.set_location(' p_pay_basis -> '|| p_pay_basis,10);
4379:
4380: l_value := 0;
4381:
4382: OPEN csr_reg_earnings(p_asst_id, p_bus_grp_id, p_date_earned);
4391: p_input_value_name := l_input_value_name;
4392: END IF;
4393: CLOSE csr_reg_earnings;
4394:
4395: hr_utility.set_location('returning p_value '|| p_value,10);
4396: hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4397: hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4398:
4399: RETURN 0;
4392: END IF;
4393: CLOSE csr_reg_earnings;
4394:
4395: hr_utility.set_location('returning p_value '|| p_value,10);
4396: hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4397: hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4398:
4399: RETURN 0;
4400:
4393: CLOSE csr_reg_earnings;
4394:
4395: hr_utility.set_location('returning p_value '|| p_value,10);
4396: hr_utility.set_location('returning p_element_type '|| p_element_type,10);
4397: hr_utility.set_location('returning p_input_value_name '|| p_input_value_name,10);
4398:
4399: RETURN 0;
4400:
4401: