[Home] [Help]
235: AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
236: --
237: BEGIN
238:
239: hr_utility.set_location('Entering:'|| l_proc, 10);
240:
241: -- Setting up reference dates required while calculating eligibility
242: -- Get last day of previous calendar month
243: l_bal_date_to := LAST_DAY(ADD_MONTHS(p_absence_start_date,-1)) ;
304: RETURN 'Y';
305: END IF;
306: END IF;
307:
308: hr_utility.set_location(' Leaving:'||l_proc, 70);
309:
310: END IJSS_Eligibility_Working_hours;
311: --
312:
339: AND p_absence_start_date BETWEEN papf.effective_start_date and papf.effective_end_date;
340: --
341: BEGIN
342:
343: --hr_utility.trace_on(NULL,'REQID');
344: hr_utility.set_location(' Entering'||l_proc, 10);
345:
346: -- Setting up reference dates required while calculating eligibility
347: -- Get last day of previous calendar month
340: --
341: BEGIN
342:
343: --hr_utility.trace_on(NULL,'REQID');
344: hr_utility.set_location(' Entering'||l_proc, 10);
345:
346: -- Setting up reference dates required while calculating eligibility
347: -- Get last day of previous calendar month
348: l_bal_date_to := LAST_DAY(ADD_MONTHS(p_Absence_start_date,-1)) ;
367:
368: l_rolling_SMID_balance := round((l_rolling_SMID_balance/(l_bal_date_to - l_hire_date +1)) * 180, 2);
369: END IF;
370: --
371: hr_utility.set_location(' Leaving:'||l_proc, 20);
372: RETURN l_rolling_SMID_balance;
373: ELSE
374: -- For absences longer than 180 days,
375: -- function calculates SMID contributions over last 12 calendar mths
390: IF l_hire_date > l_bal_date_from AND (l_bal_date_to - l_hire_date +1) <> 0 THEN --Bug #2651568
391: l_rolling_SMID_balance := round((l_rolling_SMID_balance/(l_bal_date_to - l_hire_date +1)) * 360, 2);
392: END IF;
393: --
394: hr_utility.set_location(' Leaving:'||l_proc, 30);
395: RETURN l_rolling_SMID_balance;
396: END IF;
397: --hr_utility.trace_off;
398: END IJSS_Eligibility_SMID;
393: --
394: hr_utility.set_location(' Leaving:'||l_proc, 30);
395: RETURN l_rolling_SMID_balance;
396: END IF;
397: --hr_utility.trace_off;
398: END IJSS_Eligibility_SMID;
399: --
400:
401: -- Returns Amount of salary for a period
431: begin
432:
433: begin
434:
435: hr_utility.set_location('Entering:'|| l_proc,10);
436: if p_absence_category = 'S' then
437:
438: -- Check if g_ijss_refsal_defined_balance_id has been set,
439: -- if not fetch defined_balance_id for
456: pay_balance_pkg.get_value(l_defined_balance_id
457: ,p_assignment_id
458: ,p_period_end_date);
459:
460: hr_utility.set_location(' Leaving:'||l_proc, 70);
461: EXCEPTION
462: WHEN NO_DATA_FOUND THEN --Bug #2659884
463: l_balance_value := 0;
464: END;
482: pay_balance_pkg.get_value(g_map_refsal_defbal_id
483: ,p_assignment_id
484: ,p_period_end_date);
485:
486: hr_utility.set_location(' Leaving:'||l_proc, 72);
487: EXCEPTION
488: WHEN NO_DATA_FOUND THEN
489: l_balance_value := 0;
490: END;
572: l_proc varchar2(72) := g_package||'Get_backdated_payments';
573:
574: begin
575: --
576: hr_utility.set_location('Entering:'|| l_proc,10);
577: -- Select the apt balance name (bug#3779780)
578: IF P_Absence_category = 'S' THEN
579: --
580: l_balance_name := 'FR_SICKNESS_IJSS_REFERENCE_SALARY';
598: --
599: p_payment_backyr_1 := nvl(l_pymt_yr1,0);
600: p_payment_backyr_2 := nvl(l_pymt_yr2,0);
601: --
602: hr_utility.set_location(' Leaving:'||l_proc, 70);
603: end get_backdated_payments;
604: --
605: -- GET_OPEN_DAYS
606: -- Returns open days between two dates
616: l_open_days Number;
617:
618: BEGIN
619: l_proc := g_package||'Get_open_days';
620: hr_utility.set_location('Entering:'|| l_proc,10);
621:
622: l_calendar_days := p_end_date - p_start_date + 1;
623:
624: l_open_days := l_calendar_days - CEIL ((l_calendar_days - mod(to_number(to_char(p_end_date,'J'))+1,7))/ 7);
622: l_calendar_days := p_end_date - p_start_date + 1;
623:
624: l_open_days := l_calendar_days - CEIL ((l_calendar_days - mod(to_number(to_char(p_end_date,'J'))+1,7))/ 7);
625:
626: hr_utility.set_location(' Leaving:'||l_proc, 70);
627:
628: RETURN l_open_days;
629:
630: END Get_Open_days;
649:
650: BEGIN
651:
652: l_proc := g_package||'Calculate_Sickness_Deduction';
653: hr_utility.set_location('Entering:'|| l_proc,10);
654:
655: --hr_utility.trace_on(null, 'SRJ01');
656: --hr_utility.set_location('Starting', 100);
657:
651:
652: l_proc := g_package||'Calculate_Sickness_Deduction';
653: hr_utility.set_location('Entering:'|| l_proc,10);
654:
655: --hr_utility.trace_on(null, 'SRJ01');
656: --hr_utility.set_location('Starting', 100);
657:
658: /* Raise error if deduction formula is not set */
659:
652: l_proc := g_package||'Calculate_Sickness_Deduction';
653: hr_utility.set_location('Entering:'|| l_proc,10);
654:
655: --hr_utility.trace_on(null, 'SRJ01');
656: --hr_utility.set_location('Starting', 100);
657:
658: /* Raise error if deduction formula is not set */
659:
660: IF p_asg.deduct_formula is null then
658: /* Raise error if deduction formula is not set */
659:
660: IF p_asg.deduct_formula is null then
661:
662: --hr_utility.set_location('In l_deduction_formula is null', 100);
663:
664: fnd_message.set_name ('PAY', 'PY_75027_SICK_DEDUCT_FF_NULL');
665: fnd_message.raise_error;
666:
667: END IF;
668:
669: /* set context value before calling fast formula */
670:
671: --hr_utility.set_location('set context value before calling fast formula', 100);
672:
673: pay_balance_pkg.set_context('ASSIGNMENT_ID'
674: , p_asg.assignment_id);
675: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID'
754: p_absence_arch.sick_deduction := l_outputs(1).value;
755: p_absence_arch.sick_deduction_rate := l_outputs(2).value;
756: p_absence_arch.sick_deduction_base := l_outputs(3).value;
757:
758: hr_utility.set_location(' Leaving:'||l_proc, 70);
759:
760: --hr_utility.trace_off;
761:
762:
756: p_absence_arch.sick_deduction_base := l_outputs(3).value;
757:
758: hr_utility.set_location(' Leaving:'||l_proc, 70);
759:
760: --hr_utility.trace_off;
761:
762:
763: END Calculate_Sickness_Deduction;
764:
912: --
913: --
914: BEGIN
915: l_proc := g_package||'calc_ijss';
916: -- hr_utility.trace_off;
917: hr_utility.set_location('Entering:'|| l_proc,10);
918: --
919: OPEN csr_abs_ids;
920: FETCH csr_abs_ids INTO l_person_id,l_parent_abs_id,l_orig_hiredate,l_this_abs_start_date;
913: --
914: BEGIN
915: l_proc := g_package||'calc_ijss';
916: -- hr_utility.trace_off;
917: hr_utility.set_location('Entering:'|| l_proc,10);
918: --
919: OPEN csr_abs_ids;
920: FETCH csr_abs_ids INTO l_person_id,l_parent_abs_id,l_orig_hiredate,l_this_abs_start_date;
921: CLOSE csr_abs_ids;
933:
934: l_end_date := p_end_date;
935:
936: -- Fix for "Bug #2661851 and 2665751: 2ND Absence in period has incorrect days processed in guarantee"
937: hr_utility.set_location(' Current count '||g_overlap.COUNT,20);
938: IF (g_overlap.COUNT <> 0) THEN
939: g_overlap.DELETE;
940: hr_utility.set_location(' Modified count '||g_overlap.COUNT,20);
941: END IF;
936: -- Fix for "Bug #2661851 and 2665751: 2ND Absence in period has incorrect days processed in guarantee"
937: hr_utility.set_location(' Current count '||g_overlap.COUNT,20);
938: IF (g_overlap.COUNT <> 0) THEN
939: g_overlap.DELETE;
940: hr_utility.set_location(' Modified count '||g_overlap.COUNT,20);
941: END IF;
942:
943: -- Determining if IJSS for the absence is to be estimated
944: IF l_ijss_calculate = 'Y' THEN
944: IF l_ijss_calculate = 'Y' THEN
945: -- IJSS is to be estimated
946: -- (a) check for ineligibility date
947: IF (l_inelig_date <= p_start_date) THEN
948: hr_utility.set_location('PAY_75022_ABS_MARKED_INELIG',20);
949: -- Fix for "Bug #2659924: SICKNESS DEDUCTION NOT PROCESSED WHEN IJSS ELIGIBILITY NOT MET "
950: --fnd_message.set_name ('PAY', 'PAY_75022_ABS_MARKED_INELIG');
951: --fnd_message.raise_error;
952: ELSE
967: P_short_term_eligibility => l_short_term_elig,
968: P_long_term_eligibility => l_long_term_elig,
969: P_Message => l_message);
970:
971: hr_utility.set_location(' Short term IJSS Eligibility?'||l_short_term_elig,20);
972: hr_utility.set_location(' Long term IJSS Eligibility? '||l_long_term_elig,20);
973: -- finding the number of rows to be populated
974: -- in g_overlap table for this absence period
975: l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
968: P_long_term_eligibility => l_long_term_elig,
969: P_Message => l_message);
970:
971: hr_utility.set_location(' Short term IJSS Eligibility?'||l_short_term_elig,20);
972: hr_utility.set_location(' Long term IJSS Eligibility? '||l_long_term_elig,20);
973: -- finding the number of rows to be populated
974: -- in g_overlap table for this absence period
975: l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
976:
974: -- in g_overlap table for this absence period
975: l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
976:
977: l_total_abs_duration := l_total_overlap_rows + p_absence_duration;
978: hr_utility.set_location(' Prevs absence durn '||p_absence_duration,30);
979: hr_utility.set_location(' Total absence durn '||l_total_abs_duration,30);
980: -- Eligibility needs to be checked as soon as the absence crosses the 180 day mark
981: -- In case eligibility is lost when the absence becomes longer than 180(ie. else if 2)
982: -- i) if in this processing period, only the eligible part of the absence should be processed
975: l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
976:
977: l_total_abs_duration := l_total_overlap_rows + p_absence_duration;
978: hr_utility.set_location(' Prevs absence durn '||p_absence_duration,30);
979: hr_utility.set_location(' Total absence durn '||l_total_abs_duration,30);
980: -- Eligibility needs to be checked as soon as the absence crosses the 180 day mark
981: -- In case eligibility is lost when the absence becomes longer than 180(ie. else if 2)
982: -- i) if in this processing period, only the eligible part of the absence should be processed
983: -- ii) if not, error should be returned
998: l_end_date := l_inelig_date - 1;
999: END IF;
1000: l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
1001: l_elig_for_IJSS := 'Y';
1002: hr_utility.set_location(' Modified end date to '||l_end_date||' as absence does not have long eligibility',20);
1003: ELSE -- ii)
1004: hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',40);
1005: -- Fix for "Bug #2659924: SICKNESS DEDUCTION NOT PROCESSED WHEN IJSS ELIGIBILITY NOT MET "
1006:
1000: l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
1001: l_elig_for_IJSS := 'Y';
1002: hr_utility.set_location(' Modified end date to '||l_end_date||' as absence does not have long eligibility',20);
1003: ELSE -- ii)
1004: hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',40);
1005: -- Fix for "Bug #2659924: SICKNESS DEDUCTION NOT PROCESSED WHEN IJSS ELIGIBILITY NOT MET "
1006:
1007: --fnd_message.set_name ('PAY', 'PAY_75021_ABS_INELIG_FOR_IJSS');
1008: --fnd_message.raise_error;
1008: --fnd_message.raise_error;
1009: END IF;
1010: END IF;
1011: ELSE
1012: hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',50);
1013: -- Fix for "Bug #2659924: Sickness deduction not processed when ijss eligibility not met "
1014:
1015: --fnd_message.set_name ('PAY', 'PAY_75021_ABS_INELIG_FOR_IJSS');
1016: --fnd_message.raise_error;
1021:
1022: -- Only when the absence is to be estimated and eligible,
1023: -- populate the g_overlap with the dates (possibly restricted)
1024:
1025: hr_utility.set_location('Calculation '||l_elig_for_IJSS,20);
1026: hr_utility.set_location(' Total rows to be in overlap table:'||to_char(l_total_overlap_rows),20);
1027:
1028: -- Populate g_overlap table with absence dates
1029: -- index being the actual day of the absence
1022: -- Only when the absence is to be estimated and eligible,
1023: -- populate the g_overlap with the dates (possibly restricted)
1024:
1025: hr_utility.set_location('Calculation '||l_elig_for_IJSS,20);
1026: hr_utility.set_location(' Total rows to be in overlap table:'||to_char(l_total_overlap_rows),20);
1027:
1028: -- Populate g_overlap table with absence dates
1029: -- index being the actual day of the absence
1030:
1044: IF l_hol_exist >0 THEN
1045: g_overlap(l_count_abs_days).Holiday :='H';
1046: END IF;
1047: --
1048: hr_utility.set_location(to_char(l_count_abs_days)||'th row and Date:'
1049: ||to_char(g_overlap(l_count_abs_days).Absence_day)||
1050: ' Hols? '||g_overlap(l_count_abs_days).Holiday,20);
1051: --
1052: END LOOP;
1074: OPEN csr_assignment_actions(l_date_earned);
1075: FETCH csr_assignment_actions INTO l_assignment_action_id;
1076: CLOSE csr_assignment_actions;
1077: -- find the balance value for each period
1078: hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1079: hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1080: l_period_ref_sal :=pay_balance_pkg.get_value(l_ref_sal_bal_id,
1081: l_assignment_action_id);
1082: */
1075: FETCH csr_assignment_actions INTO l_assignment_action_id;
1076: CLOSE csr_assignment_actions;
1077: -- find the balance value for each period
1078: hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1079: hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1080: l_period_ref_sal :=pay_balance_pkg.get_value(l_ref_sal_bal_id,
1081: l_assignment_action_id);
1082: */
1083: -- limit the total balance value to SS ceiling
1099: -- prorate reference salary
1100: l_period_ref_sal := (l_period_ref_sal/(l_date_earned - l_orig_hiredate +1)) * 30;
1101: END IF;
1102: END IF;
1103: hr_utility.set_location('reference period :'||to_char(l_current_ref_start_dt),40);
1104: l_total_ref_sal_bal := l_total_ref_sal_bal+ l_period_ref_sal;
1105: hr_utility.set_location('total ref salary'||to_char(l_total_ref_sal_bal),40);
1106: END LOOP;
1107: -- Check for null reference salary
1101: END IF;
1102: END IF;
1103: hr_utility.set_location('reference period :'||to_char(l_current_ref_start_dt),40);
1104: l_total_ref_sal_bal := l_total_ref_sal_bal+ l_period_ref_sal;
1105: hr_utility.set_location('total ref salary'||to_char(l_total_ref_sal_bal),40);
1106: END LOOP;
1107: -- Check for null reference salary
1108: -- Error commented out, do need to reconsider it later
1109: /*
1122: -- look for maternity absence
1123: OPEN csr_maternity(l_abs_start_date,l_person_id);
1124: FETCH csr_maternity INTO l_maternity_related;
1125: CLOSE csr_maternity;
1126: hr_utility.trace(' l_maternity_related:'||(l_maternity_related));
1127: -- subtract the ones already covered in the previous period or
1128: -- previous linked/parent absence
1129: if l_maternity_related = 'N' then
1130: l_delay_days := hruserdt.get_table_value(p_business_group_id,
1131: 'FR_IJSS_NON_OCCUP_RATES_MAX','Delay',
1132: p_absence_duration+1,l_ref_start_dt);
1133: else l_delay_days := 0;
1134: end if;
1135: hr_utility.set_location(' Delay Days :'||to_char(l_delay_days),22);
1136: -- Get the no. of dependents on parent absence start date
1137: OPEN csr_dependent(l_person_id,l_abs_start_date);
1138: FETCH csr_dependent INTO l_dependent_count;
1139: CLOSE csr_dependent;
1169: l_count_ijss_loop = l_lower_range_value(2).l_boundary_value OR
1170: l_count_ijss_loop = l_lower_range_value(3).l_boundary_value THEN
1171: -- (ii) retrieve rates if required
1172: -- according to the number of dependents
1173: hr_utility.set_location('Absence duration boundary for non-occup :'||to_char(l_count_ijss_loop),22);
1174: IF l_dependent_count<3 THEN
1175: l_ijss_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Rate for others (%)',l_count_ijss_loop,l_abs_start_date);
1176: l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Max for others - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1177: l_min_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Min for others:Factor of global minimal invalidity pension',l_count_ijss_loop,l_abs_start_date);
1180: l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Max for 3 dependents or more - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1181: l_min_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Min for 3 dependents or more:Factor of global minimal invalidity pension',l_count_ijss_loop,l_abs_start_date);
1182: END IF;
1183: END IF;
1184: hr_utility.set_location('ijss rate:'||to_char(l_ijss_rate)||' max rate:'||to_char(l_max_rate)||' min rate:'||to_char(l_min_rate),22);
1185: -- (iii) calculate IJSS
1186: -- get minimum daily salary
1187: l_min_daily_sal := l_ann_min_pension * l_min_rate;
1188: l_max_daily_sal := l_ann_ss_ceiling * l_max_rate;
1218: g_overlap(l_count_ijss_loop).IJSS_Gross:= 0;
1219: g_overlap(l_count_ijss_loop).IJSS_Net := 0;
1220: END IF;
1221: --
1222: hr_utility.set_location('Cnt of pymnts:'||to_char(l_count_ijss_loop)||
1223: ' Gross Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Gross),1,10)||
1224: ' Net Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Net),1,10),30);
1225: --
1226: END LOOP;
1240: OPEN csr_assignment_actions(l_date_earned);
1241: FETCH csr_assignment_actions INTO l_assignment_action_id;
1242: CLOSE csr_assignment_actions;
1243: -- find the balance value for this period
1244: hr_utility.set_location(' OCCUP ',40);
1245: hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1246: hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1247: l_total_ref_sal_bal := pay_balance_pkg.get_value(l_ref_sal_bal_id,
1248: l_assignment_action_id);
1241: FETCH csr_assignment_actions INTO l_assignment_action_id;
1242: CLOSE csr_assignment_actions;
1243: -- find the balance value for this period
1244: hr_utility.set_location(' OCCUP ',40);
1245: hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1246: hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1247: l_total_ref_sal_bal := pay_balance_pkg.get_value(l_ref_sal_bal_id,
1248: l_assignment_action_id);
1249: */
1242: CLOSE csr_assignment_actions;
1243: -- find the balance value for this period
1244: hr_utility.set_location(' OCCUP ',40);
1245: hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1246: hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1247: l_total_ref_sal_bal := pay_balance_pkg.get_value(l_ref_sal_bal_id,
1248: l_assignment_action_id);
1249: */
1250: -- Check for new hires
1300: --l_incr_abs_duration:= g_overlap(l_count_ijss_loop).Absence_Day - l_this_abs_start_date +1;
1301: IF l_count_ijss_loop =p_absence_duration+1 OR -- first time
1302: l_count_ijss_loop = l_lower_range_value(2).l_boundary_value THEN
1303: -- (ii) retrieve rates if required
1304: hr_utility.set_location('Absence duration boundary for occup :'||to_char(l_count_ijss_loop),22);
1305: l_ijss_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Rate (%)',l_count_ijss_loop,l_abs_start_date);
1306: l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Max - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1307: END IF;
1308: hr_utility.set_location('ijss rate:'||to_char(l_ijss_rate)||' max rate:'||to_char(l_max_rate),22);
1304: hr_utility.set_location('Absence duration boundary for occup :'||to_char(l_count_ijss_loop),22);
1305: l_ijss_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Rate (%)',l_count_ijss_loop,l_abs_start_date);
1306: l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Max - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1307: END IF;
1308: hr_utility.set_location('ijss rate:'||to_char(l_ijss_rate)||' max rate:'||to_char(l_max_rate),22);
1309: -- (iii) calculate IJSS
1310: -- the maximum daily salary
1311: l_ijss_daily_gross := l_ijss_daily_ref_sal * l_ijss_rate/100;
1312: -- Cap IJSS to maximum
1330: g_overlap(l_count_ijss_loop).IJSS_Gross:= 0;
1331: g_overlap(l_count_ijss_loop).IJSS_Net := 0;
1332: END IF;
1333: --
1334: hr_utility.set_location('Cnt of pymnts:'||to_char(l_count_ijss_loop)||
1335: ' Grss Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Gross),1,10)||
1336: ' Net Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Net),1,10),30);
1337: --
1338: END LOOP;
1338: END LOOP;
1339: END IF; -- Work Incident check
1340: --
1341: ELSE
1342: hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',60);
1343: -- Fix for "Bug #2659924:
1344: --fnd_message.set_name ('PAY', 'PAY_75021_ABS_INELIG_FOR_IJSS');
1345: --fnd_message.raise_error;
1346: END IF; -- Absence Eligibility check
1351: -- IJSS is not to be estimated, find Actual IJSS
1352:
1353: END IF; -- IJSS Estimation flag check
1354:
1355: hr_utility.set_location(' Leaving:'||l_proc, 70);
1356:
1357:
1358: END Calc_IJSS;
1359: --
1405: BEGIN
1406:
1407: l_proc := g_package||'IJSS_Eligibility_Check';
1408:
1409: hr_utility.set_location('Entering:'|| l_proc,10);
1410:
1411: -- Check for stored eligibility on DDF segment of absence
1412: OPEN csr_elig_details;
1413: FETCH csr_elig_details INTO l_smid_6_mths,l_smid_12_mths,l_plus_200_hrs,l_plus_800_hrs;
1508: END IF;
1509: P_short_term_eligibility := l_short_term_elig_flg;
1510: P_long_term_eligibility := l_long_term_elig_flg;
1511:
1512: hr_utility.set_location(' Leaving:'||l_proc, 70);
1513:
1514: END IJSS_Eligibility_Check;
1515: --
1516:
1572: --
1573: BEGIN
1574: --
1575: -- fetch all absences linked to this absence
1576: hr_utility.set_location('Entering:'|| l_proc,10);
1577:
1578: l_absence_start_date := null; -- initialize start of parent absence
1579:
1580: FOR abs_details IN csr_abs_details LOOP
1624: FETCH csr_maternity INTO p_maternity_related;
1625: CLOSE csr_maternity;
1626:
1627:
1628: hr_utility.set_location(' Leaving:'||l_proc, 70);
1629: END Get_abs_print_flg;
1630: --
1631: -- get_sickness_skip
1632: FUNCTION Get_Sickness_skip(
1740:
1741: l_proc varchar2(72) := g_package||'check_gap';
1742:
1743: begin
1744: hr_utility.set_location('Entering:'|| l_proc,10);
1745: if p_absence_start_date - p_absence_end_date - 1 > 2 then
1746: fnd_message.set_name ('PAY', 'PAY_75031_INVALID_LINK_ABS');
1747: fnd_message.raise_error;
1748: end if;
1745: if p_absence_start_date - p_absence_end_date - 1 > 2 then
1746: fnd_message.set_name ('PAY', 'PAY_75031_INVALID_LINK_ABS');
1747: fnd_message.raise_error;
1748: end if;
1749: hr_utility.set_location(' Leaving:'||l_proc, 70);
1750: end check_gap;
1751: --
1752: begin
1753: --
1753: --
1754: -- Fetch absence attendance id corresponding to the element entry
1755: --
1756: l_proc := g_package||'get_sickness_skip';
1757: hr_utility.set_location('Entering:'|| l_proc,10);
1758:
1759: open c_get_absence;
1760: fetch c_get_absence into abs_rec;
1761: close c_get_absence;
1800: --
1801: l_absence_end_date := a.date_end;
1802: l_duration := l_duration + a.duration;
1803:
1804: hr_utility.set_location(' In Sickness Skip and duration ='||l_duration,60);
1805: end loop;
1806:
1807:
1808: --
1825: g_absence_calc.parent_absence_start_date
1826: := parent_abs_rec.date_start;
1827: g_absence_calc.work_incident := parent_abs_rec.work_incident;
1828: g_absence_calc.prior_linked_absence_duration := l_duration;
1829: hr_utility.set_location(' In Sickness Skip and final duration ='||g_absence_calc.prior_linked_absence_duration,65);
1830: --
1831: if g_absence_calc.ijss_estimated = 'Y' then
1832: hr_utility.set_location(' Leaving:'||l_proc, 70);
1833: return 'N';
1828: g_absence_calc.prior_linked_absence_duration := l_duration;
1829: hr_utility.set_location(' In Sickness Skip and final duration ='||g_absence_calc.prior_linked_absence_duration,65);
1830: --
1831: if g_absence_calc.ijss_estimated = 'Y' then
1832: hr_utility.set_location(' Leaving:'||l_proc, 70);
1833: return 'N';
1834: else
1835: hr_utility.set_location(' Leaving:'||l_proc, 70);
1836: return 'Y';
1831: if g_absence_calc.ijss_estimated = 'Y' then
1832: hr_utility.set_location(' Leaving:'||l_proc, 70);
1833: return 'N';
1834: else
1835: hr_utility.set_location(' Leaving:'||l_proc, 70);
1836: return 'Y';
1837: end if;
1838: END get_sickness_skip;
1839:
1844: l_proc VARCHAR2(72) := g_package||'Get_Sickness_Skip_result';
1845: BEGIN
1846:
1847: if g_absence_calc.ijss_estimated = 'Y' then
1848: hr_utility.set_location(' Leaving:'||l_proc, 70);
1849: return 'N';
1850: else
1851: hr_utility.set_location(' Leaving:'||l_proc, 70);
1852: return 'Y';
1847: if g_absence_calc.ijss_estimated = 'Y' then
1848: hr_utility.set_location(' Leaving:'||l_proc, 70);
1849: return 'N';
1850: else
1851: hr_utility.set_location(' Leaving:'||l_proc, 70);
1852: return 'Y';
1853: end if;
1854:
1855: END;
1875: --
1876: l_proc varchar2(72) := g_package||'Compare_Guarantee';
1877: begin
1878: --
1879: hr_utility.set_location('Entering:'|| l_proc,10);
1880:
1881: for i in p_coverages.first..p_coverages.last loop
1882: -- identify the total guarantee
1883: total_net := nvl(p_coverages(i).net,0) + nvl(p_coverages(i).previous_net,0);
1880:
1881: for i in p_coverages.first..p_coverages.last loop
1882: -- identify the total guarantee
1883: total_net := nvl(p_coverages(i).net,0) + nvl(p_coverages(i).previous_net,0);
1884: hr_utility.trace('in p_coverages i:'||to_char(i)|| ' total_net:'||to_char(total_net));
1885: --
1886: -- if the total guarantee is better than the currently identified best
1887: -- then reset the current best
1888: --
1928: --
1929: -- Set the best method flag
1930: --
1931: p_coverages(best_net_index).best_method := 'Y';
1932: hr_utility.set_location(' Leaving:'||l_proc, 70);
1933: --
1934: end compare_guarantee;
1935: --
1936: PROCEDURE Calc_IJSS_Gross
1965: l_ijss_net_for_guarantee2 number := 0;
1966: --
1967: l_proc varchar2(72) := g_package||'Calc_IJSS_Gross';
1968: begin
1969: hr_utility.set_location('Entering:'|| l_proc,10);
1970: l_ijss_gross(1).Gross := 0;
1971: l_ijss_gross(2).Gross := 0;
1972: l_ijss_gross(1).Number_of_days := 0;
1973: l_ijss_gross(2).Number_of_days := 0;
2039: END IF;
2040:
2041: end if;
2042: --
2043: hr_utility.set_location(' Leaving:'||l_proc, 70);
2044: end;
2045: --
2046:
2047: -- +********************************************************************+
2219: l_date := add_months(l_entry_rec.effective_start_date,i-1);
2220:
2221: If l_date < p_sickness_start_date Then
2222:
2223: hr_utility.trace('pre csr_get_entry_processed. l_entry_rec.element_entry_id:'||to_char(l_entry_rec.element_entry_id)|| ' l_entry_rec.assignment_id:'||to_char(l_entry_rec.assignment_id)|| ' l_date:'||to_char(l_date));
2224: Open csr_get_entry_processed(l_entry_rec.element_entry_id,l_entry_rec.assignment_id,l_date);
2225: Fetch csr_get_entry_processed Into l_temp;
2226:
2227: If csr_get_entry_processed%NotFound Then
2313: l_idx_bm number;
2314: --
2315: l_proc varchar2(72) := g_package||'Calc_Sickness';
2316: begin
2317: hr_utility.set_location('Entering:'|| l_proc,10);
2318: if p_mode = 'I' then
2319: -- Code added for CPAM payment processing
2320: IF g_absence_calc.initiator = 'CPAM' THEN
2321: -- get the absence period dates
2395: --
2396: --
2397: -- Calculate the sum of the IJSS_Net values for the days to be paid
2398: --
2399: hr_utility.set_location(' 1st row in g_overlap='||g_overlap.first||' and last row='||g_overlap.last,20);
2400: if g_overlap.count > 0 THEN
2401:
2402: for i in g_overlap.first..g_overlap.last loop
2403: l_net := l_net + g_overlap(i).IJSS_Net;
2401:
2402: for i in g_overlap.first..g_overlap.last loop
2403: l_net := l_net + g_overlap(i).IJSS_Net;
2404: --
2405: hr_utility.set_location(' Date of row(idx='||g_overlap.first||')in g_overlap='||g_overlap(i).absence_day,40);
2406: if g_overlap(i).Holiday is null then
2407: if l_first_day is null then
2408: l_first_day := g_overlap(i).absence_day;
2409: end if;
2411: end loop;
2412: --
2413:
2414: if l_first_day is not null then
2415: hr_utility.set_location(' For GIs,start date='||l_first_day||' and end date='||g_overlap(g_overlap.last).Absence_day,50);
2416: g_absence_calc.IJSS_payment_start_date := l_first_day;
2417: g_absence_calc.IJSS_payment_end_date
2418: := g_overlap(g_overlap.last).Absence_day;
2419:
2451: end if;
2452: -- Added for calculating paid days
2453: -- Find total days
2454: l_total_days := p_absence_arch.sick_deduct_end_date - p_absence_arch.sick_deduct_start_date +1;
2455: hr_utility.set_location('Total Days: '||l_total_days, 22);
2456:
2457: -- Find the best method row in p_coverages
2458: FOR l_idx_coverages IN p_coverages.FIRST..p_coverages.LAST LOOP
2459: hr_utility.set_location('Index for p_coverages: '||l_idx_coverages, 22);
2455: hr_utility.set_location('Total Days: '||l_total_days, 22);
2456:
2457: -- Find the best method row in p_coverages
2458: FOR l_idx_coverages IN p_coverages.FIRST..p_coverages.LAST LOOP
2459: hr_utility.set_location('Index for p_coverages: '||l_idx_coverages, 22);
2460: hr_utility.set_location('Best Method Flag for Index Number '||l_idx_coverages||' is: '||p_coverages(l_idx_coverages).BEST_METHOD, 22);
2461: IF p_coverages(l_idx_coverages).BEST_METHOD ='Y' THEN
2462: l_idx_bm := l_idx_coverages;
2463: EXIT;
2456:
2457: -- Find the best method row in p_coverages
2458: FOR l_idx_coverages IN p_coverages.FIRST..p_coverages.LAST LOOP
2459: hr_utility.set_location('Index for p_coverages: '||l_idx_coverages, 22);
2460: hr_utility.set_location('Best Method Flag for Index Number '||l_idx_coverages||' is: '||p_coverages(l_idx_coverages).BEST_METHOD, 22);
2461: IF p_coverages(l_idx_coverages).BEST_METHOD ='Y' THEN
2462: l_idx_bm := l_idx_coverages;
2463: EXIT;
2464: ELSE
2464: ELSE
2465: l_idx_bm := -1;
2466: END IF;
2467: END LOOP;
2468: hr_utility.set_location('Idx for best method: '||l_idx_bm, 22);
2469:
2470: -- Check g_type, overlapping it with the g_guarantee_type_lookups
2471: IF l_idx_bm <> -1 THEN
2472: IF p_coverages(l_idx_bm).g_type <> -1 AND
2470: -- Check g_type, overlapping it with the g_guarantee_type_lookups
2471: IF l_idx_bm <> -1 THEN
2472: IF p_coverages(l_idx_bm).g_type <> -1 AND
2473: p_coverages(l_idx_bm).g_type = pay_fr_sick_pay_processing.get_guarantee_id('GN') THEN
2474: hr_utility.set_location('Calculations when guarantee is GN', 22);
2475: p_absence_arch.partial_paid_days := 0;
2476: p_absence_arch.unpaid_days := l_total_days - (p_coverages(l_idx_bm).gi_days1 + p_coverages(l_idx_bm).gi_days2);
2477: ELSE
2478: hr_utility.set_location('Calculations when guarantee is NOT GN', 22);
2474: hr_utility.set_location('Calculations when guarantee is GN', 22);
2475: p_absence_arch.partial_paid_days := 0;
2476: p_absence_arch.unpaid_days := l_total_days - (p_coverages(l_idx_bm).gi_days1 + p_coverages(l_idx_bm).gi_days2);
2477: ELSE
2478: hr_utility.set_location('Calculations when guarantee is NOT GN', 22);
2479: p_absence_arch.partial_paid_days := p_coverages(l_idx_bm).gi_days1 + p_coverages(l_idx_bm).gi_days2;
2480: p_absence_arch.unpaid_days := l_total_days - p_absence_arch.partial_paid_days;
2481: END IF;
2482: END IF;
2480: p_absence_arch.unpaid_days := l_total_days - p_absence_arch.partial_paid_days;
2481: END IF;
2482: END IF;
2483: --
2484: hr_utility.set_location(' Leaving:'||l_proc, 70);
2485: end calc_sickness;
2486: --
2487: -----------------------------------------------------------------------
2488: -- Function FR_ROLLING_BALANCE
2510: i number := 0;
2511: l_value number := 0;
2512: l_proc varchar2(72) := g_package||'fr_rolling_balance';
2513: BEGIN
2514: hr_utility.set_location('Entering:'|| l_proc,10);
2515: open csr_def_bal_id;
2516: fetch csr_def_bal_id into l_defined_balance_id;
2517: close csr_def_bal_id;
2518: --
2528: WHEN NO_DATA_FOUND THEN --Bug #2651568
2529: l_value := 0;
2530: END;
2531: i := i + 1;
2532: hr_utility.set_location(' BAL VAL='||l_value, 60);
2533: end loop;
2534: hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2535: hr_utility.set_location(' Leaving:'||l_proc, 70);
2536: return l_value;
2530: END;
2531: i := i + 1;
2532: hr_utility.set_location(' BAL VAL='||l_value, 60);
2533: end loop;
2534: hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2535: hr_utility.set_location(' Leaving:'||l_proc, 70);
2536: return l_value;
2537: END;
2538: -----------------------------------------------------------------------------
2531: i := i + 1;
2532: hr_utility.set_location(' BAL VAL='||l_value, 60);
2533: end loop;
2534: hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2535: hr_utility.set_location(' Leaving:'||l_proc, 70);
2536: return l_value;
2537: END;
2538: -----------------------------------------------------------------------------
2539: -- SET_GLOBAL_IDS
2541: -----------------------------------------------------------------------------
2542: PROCEDURE SET_GLOBAL_IDS(p_effective_date in DATE) is
2543: l_proc varchar2(72) := g_package||'SET_GLOBAL_IDS';
2544: begin
2545: hr_utility.set_location('Entering:'|| l_proc,10);
2546: select max(e.element_type_id)
2547: ,max(decode(i.name,'Parent Absence ID',i.input_value_id,null))
2548: ,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
2549: ,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
2618: and e.element_type_id = i.element_type_id
2619: and p_effective_date between e.effective_start_date and e.effective_end_date
2620: and p_effective_date between i.effective_start_date and i.effective_end_date;
2621:
2622: hr_utility.set_location('Leaving:'|| l_proc,90);
2623:
2624: end SET_GLOBAL_IDS;
2625: --
2626: -----------------------------------------------------------------------
2744:
2745: l_proc varchar2(72) := g_package||'Get_GI_Payments_Audit';
2746:
2747: begin
2748: hr_utility.set_location('Entering:'|| l_proc,10);
2749: begin
2750: -- fetch ids of element_type and its inputs
2751: if g_gi_info_element_type_id is null then
2752: set_global_ids(p_effective_date => p_current_date);
2751: if g_gi_info_element_type_id is null then
2752: set_global_ids(p_effective_date => p_current_date);
2753: END IF;
2754:
2755: hr_utility.set_location('Entering:'||l_proc||' g_gi_info_element_type_id:'||
2756: to_char(g_gi_info_element_type_id),10);
2757:
2758: end;
2759: --
2778: else p_gi_best_method := '';
2779: end if;
2780: end loop;
2781:
2782: hr_utility.set_location('Leaving :'|| l_proc, 90);
2783: end;
2784: --
2785: hr_utility.set_location(' Leaving:'||l_proc, 70);
2786: end get_gi_payments_audit;
2781:
2782: hr_utility.set_location('Leaving :'|| l_proc, 90);
2783: end;
2784: --
2785: hr_utility.set_location(' Leaving:'||l_proc, 70);
2786: end get_gi_payments_audit;
2787:
2788:
2789: PROCEDURE Band_Overlaps
2826: --
2827: -- The following statement will find the result id for the overlap if one exists.
2828: --
2829: BEGIN
2830: hr_utility.set_location('Entering:'|| l_proc,10);
2831: select /*+ORDERED*/
2832: fnd_date.canonical_to_date(rrv_end.result_value) payment_end_date
2833: ,nvl(sum(decode(target.input_value_id,
2834: g_gi_info_band1_iv_id,target.result_value,
2894: --
2895: -- Now add the relevant part back in
2896: --
2897: l_number_of_days_to_add_back := (l_payment_end_date - p_date_from) + 1;
2898: hr_utility.trace('pymt end date = ' || l_payment_end_date || ' p_date_from = ' || p_date_from);
2899: hr_utility.trace('Band_Overlaps - number Of days to add back = ' || l_number_of_days_to_add_back);
2900: --
2901: -- For the overlap find which bands were used and how many days were used, starting with
2902: -- the highest band.
2895: -- Now add the relevant part back in
2896: --
2897: l_number_of_days_to_add_back := (l_payment_end_date - p_date_from) + 1;
2898: hr_utility.trace('pymt end date = ' || l_payment_end_date || ' p_date_from = ' || p_date_from);
2899: hr_utility.trace('Band_Overlaps - number Of days to add back = ' || l_number_of_days_to_add_back);
2900: --
2901: -- For the overlap find which bands were used and how many days were used, starting with
2902: -- the highest band.
2903: --
2940: END IF;
2941: END IF;
2942:
2943:
2944: hr_utility.set_location(' Leaving:'||l_proc, 70);
2945:
2946: Exception
2947: When no_data_found THEN
2948: hr_utility.trace (' band_overlaps - no data found');
2944: hr_utility.set_location(' Leaving:'||l_proc, 70);
2945:
2946: Exception
2947: When no_data_found THEN
2948: hr_utility.trace (' band_overlaps - no data found');
2949:
2950: when others then
2951: hr_utility.trace(SQLCODE);
2952: hr_utility.trace(SQLERRM);
2947: When no_data_found THEN
2948: hr_utility.trace (' band_overlaps - no data found');
2949:
2950: when others then
2951: hr_utility.trace(SQLCODE);
2952: hr_utility.trace(SQLERRM);
2953: Raise;
2954:
2955:
2948: hr_utility.trace (' band_overlaps - no data found');
2949:
2950: when others then
2951: hr_utility.trace(SQLCODE);
2952: hr_utility.trace(SQLERRM);
2953: Raise;
2954:
2955:
2956: END Band_Overlaps;
3035: --
3036:
3037: BEGIN
3038:
3039: hr_utility.set_location('Entering:'|| l_proc,10);
3040:
3041: -- If p_band_expiry_duration = 'Y' (rolling year) then go back to the
3042: -- equivalent day last year + 1 day, the date to becomes the parent absence
3043: -- start date - 1 day
3093: p_band2_days := l_total_band2_days;
3094: p_band3_days := l_total_band3_days;
3095: p_band4_days := l_total_band4_days;
3096:
3097: hr_utility.set_location(' Leaving:'||l_proc, 70);
3098:
3099: exception when no_data_found THEN -- return zeros as no band usage
3100: hr_utility.trace ('get_gi_bands_audit - no band usage');
3101: p_band1_days := 0;
3096:
3097: hr_utility.set_location(' Leaving:'||l_proc, 70);
3098:
3099: exception when no_data_found THEN -- return zeros as no band usage
3100: hr_utility.trace ('get_gi_bands_audit - no band usage');
3101: p_band1_days := 0;
3102: p_band2_days := 0;
3103: p_band3_days := 0;
3104: p_band4_days := 0;
3103: p_band3_days := 0;
3104: p_band4_days := 0;
3105:
3106: when others then
3107: hr_utility.set_location('get_gi_bands audit',20);
3108: hr_utility.trace(SQLCODE);
3109: hr_utility.trace(SQLERRM);
3110: Raise;
3111:
3104: p_band4_days := 0;
3105:
3106: when others then
3107: hr_utility.set_location('get_gi_bands audit',20);
3108: hr_utility.trace(SQLCODE);
3109: hr_utility.trace(SQLERRM);
3110: Raise;
3111:
3112: END Get_GI_Bands_Audit;
3105:
3106: when others then
3107: hr_utility.set_location('get_gi_bands audit',20);
3108: hr_utility.trace(SQLCODE);
3109: hr_utility.trace(SQLERRM);
3110: Raise;
3111:
3112: END Get_GI_Bands_Audit;
3113: --
3129: l_band_idx PLS_INTEGER := p_band_name;
3130: l_band_rate NUMBER;
3131: --
3132: BEGIN
3133: hr_utility.set_location(' Entering:'||l_proc, 10);
3134:
3135: IF (p_for_days >= p_band_avail_days) THEN
3136: l_band_curr_days := p_band_avail_days ;
3137: p_for_days := p_for_days - l_band_curr_days;
3149: l_bands(l_band_idx).band_from_dt := p_band_start_date;
3150: l_bands(l_band_idx).band_to_dt := p_band_start_date + (l_band_curr_days - 1);
3151: l_bands(l_band_idx).band_days := l_band_curr_days;
3152:
3153: hr_utility.set_location(' Populating l_Bands for current GI for band:'||l_band_idx, 11) ;
3154: hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155: hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156: hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157: hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3150: l_bands(l_band_idx).band_to_dt := p_band_start_date + (l_band_curr_days - 1);
3151: l_bands(l_band_idx).band_days := l_band_curr_days;
3152:
3153: hr_utility.set_location(' Populating l_Bands for current GI for band:'||l_band_idx, 11) ;
3154: hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155: hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156: hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157: hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3158: hr_utility.set_location(' l_Bands to :'||l_bands(l_band_idx).band_to_dt, 11) ;
3151: l_bands(l_band_idx).band_days := l_band_curr_days;
3152:
3153: hr_utility.set_location(' Populating l_Bands for current GI for band:'||l_band_idx, 11) ;
3154: hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155: hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156: hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157: hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3158: hr_utility.set_location(' l_Bands to :'||l_bands(l_band_idx).band_to_dt, 11) ;
3159:
3152:
3153: hr_utility.set_location(' Populating l_Bands for current GI for band:'||l_band_idx, 11) ;
3154: hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155: hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156: hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157: hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3158: hr_utility.set_location(' l_Bands to :'||l_bands(l_band_idx).band_to_dt, 11) ;
3159:
3160:
3153: hr_utility.set_location(' Populating l_Bands for current GI for band:'||l_band_idx, 11) ;
3154: hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155: hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156: hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157: hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3158: hr_utility.set_location(' l_Bands to :'||l_bands(l_band_idx).band_to_dt, 11) ;
3159:
3160:
3161:
3154: hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155: hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156: hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157: hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3158: hr_utility.set_location(' l_Bands to :'||l_bands(l_band_idx).band_to_dt, 11) ;
3159:
3160:
3161:
3162: END IF;
3162: END IF;
3163: -- Absence date moved on for lower Band utilisations
3164: p_band_start_date := p_band_start_date + l_band_curr_days;
3165:
3166: hr_utility.set_location(' Leaving:'||l_proc, 70);
3167: END Use_GI_bands;
3168: -- ----------------------------------------------------------------------
3169:
3170: -- ----------------------------------------------------------------------
3179: l_proc varchar2(72) := g_package||'Get_GI_ref_sal_divisor';
3180: l_days NUMBER;
3181:
3182: BEGIN
3183: hr_utility.set_location(' Entering:'||l_proc, 10);
3184: --
3185: IF p_start_date IS NOT NULL
3186: AND p_end_date IS NOT NULL
3187: AND (p_end_date >= p_start_date) THEN
3189: ELSE
3190: l_days := 0;
3191: END IF;
3192: --
3193: hr_utility.set_location(' Leaving:'||l_proc, 70);
3194: RETURN l_days;
3195: END;
3196:
3197: --------------********************--------------
3256: l_proc varchar2(72) := g_package||'Calc_LEGAL_GI';
3257:
3258: BEGIN
3259:
3260: hr_utility.set_location('Entering:'|| l_proc,10);
3261: hr_utility.set_location('Entering:'|| l_proc || l_gi_start_date,10);
3262: hr_utility.set_location('Entering:'|| l_proc|| l_gi_end_date,10);
3263:
3264: -- Get Service period(in years) of the person
3257:
3258: BEGIN
3259:
3260: hr_utility.set_location('Entering:'|| l_proc,10);
3261: hr_utility.set_location('Entering:'|| l_proc || l_gi_start_date,10);
3262: hr_utility.set_location('Entering:'|| l_proc|| l_gi_end_date,10);
3263:
3264: -- Get Service period(in years) of the person
3265: BEGIN
3258: BEGIN
3259:
3260: hr_utility.set_location('Entering:'|| l_proc,10);
3261: hr_utility.set_location('Entering:'|| l_proc || l_gi_start_date,10);
3262: hr_utility.set_location('Entering:'|| l_proc|| l_gi_end_date,10);
3263:
3264: -- Get Service period(in years) of the person
3265: BEGIN
3266: SELECT TRUNC((MONTHS_BETWEEN(paa.date_start,
3289: 'DAYS_DELAY',
3290: l_work_incident_type,
3291: l_parent_absence_start_date);
3292:
3293: hr_utility.set_location(' IN '|| l_proc ||' and previous duration '||l_abs_duration,20);
3294: hr_utility.set_location(' and waiting days='||l_waiting_days,20);
3295: -- Any waiting days still to be used
3296: l_curr_waiting_days := l_waiting_days - l_abs_duration;
3297: IF l_curr_waiting_days <= 0 THEN -- Absence duration has exhausted waiting days
3290: l_work_incident_type,
3291: l_parent_absence_start_date);
3292:
3293: hr_utility.set_location(' IN '|| l_proc ||' and previous duration '||l_abs_duration,20);
3294: hr_utility.set_location(' and waiting days='||l_waiting_days,20);
3295: -- Any waiting days still to be used
3296: l_curr_waiting_days := l_waiting_days - l_abs_duration;
3297: IF l_curr_waiting_days <= 0 THEN -- Absence duration has exhausted waiting days
3298: l_curr_waiting_days:= 0;
3479: ,p_coverages => p_coverages
3480: ,p_absence_arch => p_absence_arch);
3481: END IF;
3482:
3483: hr_utility.set_location(' Leaving:'||l_proc, 70);
3484:
3485: EXCEPTION
3486: WHEN OTHERS THEN
3487: hr_utility.set_location('calc_legal_GI',100);
3483: hr_utility.set_location(' Leaving:'||l_proc, 70);
3484:
3485: EXCEPTION
3486: WHEN OTHERS THEN
3487: hr_utility.set_location('calc_legal_GI',100);
3488: hr_utility.trace(SQLCODE);
3489: hr_utility.trace(SQLERRM);
3490: Raise;
3491:
3484:
3485: EXCEPTION
3486: WHEN OTHERS THEN
3487: hr_utility.set_location('calc_legal_GI',100);
3488: hr_utility.trace(SQLCODE);
3489: hr_utility.trace(SQLERRM);
3490: Raise;
3491:
3492: END Calc_LEGAL_GI;
3485: EXCEPTION
3486: WHEN OTHERS THEN
3487: hr_utility.set_location('calc_legal_GI',100);
3488: hr_utility.trace(SQLCODE);
3489: hr_utility.trace(SQLERRM);
3490: Raise;
3491:
3492: END Calc_LEGAL_GI;
3493:
3525: -- dimension of this balance is calculated for the current action.
3526: -- a daily rate is found by deviding by the number of days in the run
3527: -- proration period ( or full period if not prorated ).
3528:
3529: hr_utility.set_location('Entering:'|| l_proc,10);
3530:
3531: if p_gi_type in ('CA_N','GN') then -- { net guarantee
3532: l_value := p_asg.base_net;
3533:
3532: l_value := p_asg.base_net;
3533:
3534: else -- gross guarantee
3535:
3536: hr_utility.set_location(' Entering:'|| l_proc,20);
3537: begin
3538: open csr_def_bal_id;
3539: fetch csr_def_bal_id into l_defined_balance_id;
3540: if csr_def_bal_id%notfound THEN
3537: begin
3538: open csr_def_bal_id;
3539: fetch csr_def_bal_id into l_defined_balance_id;
3540: if csr_def_bal_id%notfound THEN
3541: hr_utility.trace('Defined balance not found:' || p_balance_name||'_ASG_PRO_RUN');
3542: hr_utility.set_message (801, 'PAY_75042_DEF_BAL_MISSING');
3543: hr_utility.set_message_token('BALANCE_NAME',p_balance_name);
3544: hr_utility.raise_error;
3545: end if;
3538: open csr_def_bal_id;
3539: fetch csr_def_bal_id into l_defined_balance_id;
3540: if csr_def_bal_id%notfound THEN
3541: hr_utility.trace('Defined balance not found:' || p_balance_name||'_ASG_PRO_RUN');
3542: hr_utility.set_message (801, 'PAY_75042_DEF_BAL_MISSING');
3543: hr_utility.set_message_token('BALANCE_NAME',p_balance_name);
3544: hr_utility.raise_error;
3545: end if;
3546: close csr_def_bal_id;
3539: fetch csr_def_bal_id into l_defined_balance_id;
3540: if csr_def_bal_id%notfound THEN
3541: hr_utility.trace('Defined balance not found:' || p_balance_name||'_ASG_PRO_RUN');
3542: hr_utility.set_message (801, 'PAY_75042_DEF_BAL_MISSING');
3543: hr_utility.set_message_token('BALANCE_NAME',p_balance_name);
3544: hr_utility.raise_error;
3545: end if;
3546: close csr_def_bal_id;
3547: end;
3540: if csr_def_bal_id%notfound THEN
3541: hr_utility.trace('Defined balance not found:' || p_balance_name||'_ASG_PRO_RUN');
3542: hr_utility.set_message (801, 'PAY_75042_DEF_BAL_MISSING');
3543: hr_utility.set_message_token('BALANCE_NAME',p_balance_name);
3544: hr_utility.raise_error;
3545: end if;
3546: close csr_def_bal_id;
3547: end;
3548: --
3545: end if;
3546: close csr_def_bal_id;
3547: end;
3548: --
3549: hr_utility.set_location(' Entering:'|| l_proc,30);
3550: -- Added condition for CPAM processing
3551: IF g_absence_calc.initiator = 'CPAM' THEN
3552: l_value := pay_balance_pkg.get_value
3553: (l_defined_balance_id
3558: (l_defined_balance_id
3559: ,p_asg.assignment_action_id);
3560: END IF;
3561: --
3562: hr_utility.trace('cagr_balance_value:'||to_char(l_value));
3563:
3564: end if; -- }
3565: -- balance is fr prorated period, divide by number of days in prorated period
3566: -- to get daily rate
3563:
3564: end if; -- }
3565: -- balance is fr prorated period, divide by number of days in prorated period
3566: -- to get daily rate
3567: hr_utility.set_location(' starting:'||l_proc, 40);
3568:
3569: -- Added condition for CPAM processing
3570: IF g_absence_calc.initiator = 'CPAM' THEN
3571: l_value := l_value / Get_GI_ref_salary_divisor(
3576: p_start_date => p_asg.action_start_date,
3577: p_end_date => p_asg.action_end_date);
3578: END IF;
3579: --
3580: hr_utility.set_location('Leaving:'||l_proc, 70);
3581:
3582: return l_value;
3583:
3584: END Get_CAGR_reference_salary;
3682: l_effective_end_date date;
3683:
3684:
3685: BEGIN
3686: hr_utility.set_location('Entering:'|| l_proc,10);
3687:
3688: BEGIN
3689:
3690: -- fetch ids of element_type and its inputs
3688: BEGIN
3689:
3690: -- fetch ids of element_type and its inputs
3691: IF g_ben_element_type_id is null then
3692: hr_utility.set_location(' Entering:'|| l_proc,20);
3693: select max(e.element_type_id)
3694: ,max(decode(i.name,'Absence ID',i.input_value_id,null))
3695: ,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
3696: ,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
3726: and e.legislation_code = 'FR'
3727: and e.element_type_id = i.element_type_id
3728: and g_absence_calc.IJSS_payment_start_date between e.effective_start_date and e.effective_end_date
3729: and g_absence_calc.IJSS_payment_start_date between i.effective_start_date and i.effective_end_date;
3730: hr_utility.set_location(' Entering:'|| l_proc ||' g_ben_absence_id_iv_id '||g_ben_absence_id_iv_id,25);
3731: END IF;
3732: EXCEPTION
3733: WHEN OTHERS THEN
3734: hr_utility.set_location('Exception in '||l_proc,10);
3730: hr_utility.set_location(' Entering:'|| l_proc ||' g_ben_absence_id_iv_id '||g_ben_absence_id_iv_id,25);
3731: END IF;
3732: EXCEPTION
3733: WHEN OTHERS THEN
3734: hr_utility.set_location('Exception in '||l_proc,10);
3735: hr_utility.trace(SQLCODE);
3736: hr_utility.trace(SQLERRM);
3737: Raise;
3738:
3731: END IF;
3732: EXCEPTION
3733: WHEN OTHERS THEN
3734: hr_utility.set_location('Exception in '||l_proc,10);
3735: hr_utility.trace(SQLCODE);
3736: hr_utility.trace(SQLERRM);
3737: Raise;
3738:
3739: END;
3732: EXCEPTION
3733: WHEN OTHERS THEN
3734: hr_utility.set_location('Exception in '||l_proc,10);
3735: hr_utility.trace(SQLCODE);
3736: hr_utility.trace(SQLERRM);
3737: Raise;
3738:
3739: END;
3740: -----------------------------------------------------------------------------
3745: -- all the linked absences.
3746:
3747: BEGIN
3748:
3749: hr_utility.set_location(' Entering:'|| l_proc,30);
3750: l_guarantee_id := 0;
3751: l_effective_end_date := g_absence_calc.IJSS_payment_end_date; --g_absence_calc.effective_end_date;
3752:
3753: hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3749: hr_utility.set_location(' Entering:'|| l_proc,30);
3750: l_guarantee_id := 0;
3751: l_effective_end_date := g_absence_calc.IJSS_payment_end_date; --g_absence_calc.effective_end_date;
3752:
3753: hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3754: hr_utility.trace(' p_asg.assignment_id:'||to_char(p_asg.assignment_id));
3755: hr_utility.trace(' p_absence_arch.ID:'||to_char(p_absence_arch.ID));
3756: hr_utility.trace(' l_parent_absence_id:'||to_char(l_parent_absence_id));
3757: hr_utility.trace(' l_effective_end_date:'||to_char(l_effective_end_date));
3750: l_guarantee_id := 0;
3751: l_effective_end_date := g_absence_calc.IJSS_payment_end_date; --g_absence_calc.effective_end_date;
3752:
3753: hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3754: hr_utility.trace(' p_asg.assignment_id:'||to_char(p_asg.assignment_id));
3755: hr_utility.trace(' p_absence_arch.ID:'||to_char(p_absence_arch.ID));
3756: hr_utility.trace(' l_parent_absence_id:'||to_char(l_parent_absence_id));
3757: hr_utility.trace(' l_effective_end_date:'||to_char(l_effective_end_date));
3758:
3751: l_effective_end_date := g_absence_calc.IJSS_payment_end_date; --g_absence_calc.effective_end_date;
3752:
3753: hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3754: hr_utility.trace(' p_asg.assignment_id:'||to_char(p_asg.assignment_id));
3755: hr_utility.trace(' p_absence_arch.ID:'||to_char(p_absence_arch.ID));
3756: hr_utility.trace(' l_parent_absence_id:'||to_char(l_parent_absence_id));
3757: hr_utility.trace(' l_effective_end_date:'||to_char(l_effective_end_date));
3758:
3759: -- Absence ids removed as not used within the cursor, bug #2647436
3752:
3753: hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3754: hr_utility.trace(' p_asg.assignment_id:'||to_char(p_asg.assignment_id));
3755: hr_utility.trace(' p_absence_arch.ID:'||to_char(p_absence_arch.ID));
3756: hr_utility.trace(' l_parent_absence_id:'||to_char(l_parent_absence_id));
3757: hr_utility.trace(' l_effective_end_date:'||to_char(l_effective_end_date));
3758:
3759: -- Absence ids removed as not used within the cursor, bug #2647436
3760: FOR l_ben in get_ben( p_asg.assignment_id,
3753: hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3754: hr_utility.trace(' p_asg.assignment_id:'||to_char(p_asg.assignment_id));
3755: hr_utility.trace(' p_absence_arch.ID:'||to_char(p_absence_arch.ID));
3756: hr_utility.trace(' l_parent_absence_id:'||to_char(l_parent_absence_id));
3757: hr_utility.trace(' l_effective_end_date:'||to_char(l_effective_end_date));
3758:
3759: -- Absence ids removed as not used within the cursor, bug #2647436
3760: FOR l_ben in get_ben( p_asg.assignment_id,
3761: l_effective_end_date)
3759: -- Absence ids removed as not used within the cursor, bug #2647436
3760: FOR l_ben in get_ben( p_asg.assignment_id,
3761: l_effective_end_date)
3762: LOOP
3763: hr_utility.set_location(' Entering:'|| l_proc,35);
3764: -- check if this guarantee has already been processed if so do nothing
3765: -- (the element is defined a multiple entries allowed but there shouldn't
3766: -- be more than 1 entry for the same guarantee so ignore the duplicate )
3767:
3767:
3768: IF l_guarantee_id <> l_ben.guarantee_id THEN -- {process
3769: l_guarantee_id := l_ben.guarantee_id;
3770:
3771: hr_utility.set_location(' IN:'|| l_proc,40);
3772:
3773:
3774: -- Setting input dates for GI to be calculated for each GI
3775: l_gi_start_date := g_absence_calc.IJSS_payment_start_date;
3774: -- Setting input dates for GI to be calculated for each GI
3775: l_gi_start_date := g_absence_calc.IJSS_payment_start_date;
3776: l_gi_end_date := g_absence_calc.IJSS_payment_end_date;
3777:
3778: hr_utility.set_location(' Initial GI_START:'|| l_gi_start_date,40);
3779: hr_utility.set_location(' IN '|| l_proc ||' and previous duration='||l_abs_duration,40);
3780: hr_utility.set_location(' and waiting days='||l_ben.waiting_days,40);
3781: -- Check for waiting days
3782: l_curr_waiting_days := nvl(l_ben.waiting_days,0) - l_abs_duration;
3775: l_gi_start_date := g_absence_calc.IJSS_payment_start_date;
3776: l_gi_end_date := g_absence_calc.IJSS_payment_end_date;
3777:
3778: hr_utility.set_location(' Initial GI_START:'|| l_gi_start_date,40);
3779: hr_utility.set_location(' IN '|| l_proc ||' and previous duration='||l_abs_duration,40);
3780: hr_utility.set_location(' and waiting days='||l_ben.waiting_days,40);
3781: -- Check for waiting days
3782: l_curr_waiting_days := nvl(l_ben.waiting_days,0) - l_abs_duration;
3783: IF l_curr_waiting_days <= 0 THEN -- Absence duration has exhausted waiting days
3776: l_gi_end_date := g_absence_calc.IJSS_payment_end_date;
3777:
3778: hr_utility.set_location(' Initial GI_START:'|| l_gi_start_date,40);
3779: hr_utility.set_location(' IN '|| l_proc ||' and previous duration='||l_abs_duration,40);
3780: hr_utility.set_location(' and waiting days='||l_ben.waiting_days,40);
3781: -- Check for waiting days
3782: l_curr_waiting_days := nvl(l_ben.waiting_days,0) - l_abs_duration;
3783: IF l_curr_waiting_days <= 0 THEN -- Absence duration has exhausted waiting days
3784: l_curr_waiting_days:= 0;
3786: -- Modify start_date for calculating GI
3787: l_gi_start_date := l_gi_start_date + l_curr_waiting_days;
3788: END IF;
3789:
3790: hr_utility.set_location(' Modified GI_START:'|| l_gi_start_date,40);
3791: hr_utility.set_location(' GI_END :'|| l_gi_end_date,40);
3792: --
3793: -- Get Bands used over the Rolling year
3794: --
3787: l_gi_start_date := l_gi_start_date + l_curr_waiting_days;
3788: END IF;
3789:
3790: hr_utility.set_location(' Modified GI_START:'|| l_gi_start_date,40);
3791: hr_utility.set_location(' GI_END :'|| l_gi_end_date,40);
3792: --
3793: -- Get Bands used over the Rolling year
3794: --
3795: Get_GI_Bands_Audit
3818:
3819: -- Get index for g_coverages table
3820: l_idx := nvl(p_coverages.LAST, 0) + 1;
3821:
3822: hr_utility.set_location(' IN:'|| l_proc,45);
3823: --
3824: -- Get numeric code for the Guarantee type from global table g_guarantee_lookup_types
3825: -- in the PAY_FR_SICK_PAY_PROCESSING package.
3826: -- Using a common function for both LEGI and CAGR
3832: p_balance_name => l_ben.balance,
3833: p_gi_type => l_ben.guarantee_type,
3834: p_asg => p_asg);
3835:
3836: hr_utility.set_location(' IN:'|| l_proc,50);
3837:
3838: -- Calculate GI payments for the current absence days passed
3839: --
3840: l_for_days := l_gi_end_date - l_gi_start_date + 1;
3884: p_gi_type => l_ben.guarantee_type,
3885: p_asg => p_asg);
3886:
3887:
3888: hr_utility.set_location(' IN:'|| l_proc,50);
3889:
3890: hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3886:
3887:
3888: hr_utility.set_location(' IN:'|| l_proc,50);
3889:
3890: hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893: hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894: hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3887:
3888: hr_utility.set_location(' IN:'|| l_proc,50);
3889:
3890: hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893: hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894: hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3895: hr_utility.set_location(' Ben cursor: B2_rate '|| l_ben.b2_rate, 55);
3888: hr_utility.set_location(' IN:'|| l_proc,50);
3889:
3890: hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893: hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894: hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3895: hr_utility.set_location(' Ben cursor: B2_rate '|| l_ben.b2_rate, 55);
3896:
3889:
3890: hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893: hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894: hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3895: hr_utility.set_location(' Ben cursor: B2_rate '|| l_ben.b2_rate, 55);
3896:
3897: -- Use available bands for current absence
3890: hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893: hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894: hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3895: hr_utility.set_location(' Ben cursor: B2_rate '|| l_ben.b2_rate, 55);
3896:
3897: -- Use available bands for current absence
3898: -- Outputs are populated in table l_bands
3891: hr_utility.set_location(' For_days '|| l_for_days, 55);
3892: hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893: hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894: hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3895: hr_utility.set_location(' Ben cursor: B2_rate '|| l_ben.b2_rate, 55);
3896:
3897: -- Use available bands for current absence
3898: -- Outputs are populated in table l_bands
3899: IF (l_B1_avail_days > 0) AND (l_for_days >0) THEN
3937: -- Max of 2 rows will be populated in l_bands
3938: -- as there can only be 1 GI band completely used up in a period
3939: -- and hence only 1 crossover of bands
3940: --
3941: hr_utility.set_location(' l_Bands count '||l_bands.COUNT||' 1st: '||l_bands.FIRST||' last: '||l_bands.LAST, 52) ;
3942:
3943: IF l_bands.COUNT > 0 THEN
3944: p_coverages(l_idx).cagr_id := l_ben.guarantee_id;
3945: p_coverages(l_idx).g_type := l_gi_code;
3945: p_coverages(l_idx).g_type := l_gi_code;
3946:
3947: FOR i IN l_bands.FIRST..l_bands.LAST -- i contains Band Numbers(index of l_bands)
3948: LOOP
3949: hr_utility.set_location(' In l_Bands for band'||i, 55) ;
3950: hr_utility.set_location(' rate '||l_bands(i).band_rate, 55) ;
3951: hr_utility.set_location(' days '||l_bands(i).band_days, 55) ;
3952: hr_utility.set_location(' paymnt '||l_bands(i).band_payment, 55) ;
3953: hr_utility.set_location(' Existing l_net for GI '||l_net, 55) ;
3946:
3947: FOR i IN l_bands.FIRST..l_bands.LAST -- i contains Band Numbers(index of l_bands)
3948: LOOP
3949: hr_utility.set_location(' In l_Bands for band'||i, 55) ;
3950: hr_utility.set_location(' rate '||l_bands(i).band_rate, 55) ;
3951: hr_utility.set_location(' days '||l_bands(i).band_days, 55) ;
3952: hr_utility.set_location(' paymnt '||l_bands(i).band_payment, 55) ;
3953: hr_utility.set_location(' Existing l_net for GI '||l_net, 55) ;
3954:
3947: FOR i IN l_bands.FIRST..l_bands.LAST -- i contains Band Numbers(index of l_bands)
3948: LOOP
3949: hr_utility.set_location(' In l_Bands for band'||i, 55) ;
3950: hr_utility.set_location(' rate '||l_bands(i).band_rate, 55) ;
3951: hr_utility.set_location(' days '||l_bands(i).band_days, 55) ;
3952: hr_utility.set_location(' paymnt '||l_bands(i).band_payment, 55) ;
3953: hr_utility.set_location(' Existing l_net for GI '||l_net, 55) ;
3954:
3955: --l_first_band := l_bands.FIRST;
3948: LOOP
3949: hr_utility.set_location(' In l_Bands for band'||i, 55) ;
3950: hr_utility.set_location(' rate '||l_bands(i).band_rate, 55) ;
3951: hr_utility.set_location(' days '||l_bands(i).band_days, 55) ;
3952: hr_utility.set_location(' paymnt '||l_bands(i).band_payment, 55) ;
3953: hr_utility.set_location(' Existing l_net for GI '||l_net, 55) ;
3954:
3955: --l_first_band := l_bands.FIRST;
3956: --IF l_IJSS_gross_from_date IS NULL THEN
3949: hr_utility.set_location(' In l_Bands for band'||i, 55) ;
3950: hr_utility.set_location(' rate '||l_bands(i).band_rate, 55) ;
3951: hr_utility.set_location(' days '||l_bands(i).band_days, 55) ;
3952: hr_utility.set_location(' paymnt '||l_bands(i).band_payment, 55) ;
3953: hr_utility.set_location(' Existing l_net for GI '||l_net, 55) ;
3954:
3955: --l_first_band := l_bands.FIRST;
3956: --IF l_IJSS_gross_from_date IS NULL THEN
3957: IF (i = l_bands.FIRST) THEN -- Only for 1st row in l_bands
3958: l_IJSS_gross_from_date := l_bands(i).band_from_dt;
3959: p_coverages(l_idx).gi_from_date1:= l_bands(i).band_from_dt;
3960: END IF;
3961:
3962: hr_utility.set_location(' Gross from :'||l_bands(i).band_from_dt, 11) ;
3963: hr_utility.set_location(' Gross to :'||l_bands(i).band_to_dt, 11) ;
3964:
3965: l_IJSS_gross_to_date := l_bands(i).band_to_dt;
3966:
3959: p_coverages(l_idx).gi_from_date1:= l_bands(i).band_from_dt;
3960: END IF;
3961:
3962: hr_utility.set_location(' Gross from :'||l_bands(i).band_from_dt, 11) ;
3963: hr_utility.set_location(' Gross to :'||l_bands(i).band_to_dt, 11) ;
3964:
3965: l_IJSS_gross_to_date := l_bands(i).band_to_dt;
3966:
3967: IF (i = l_bands.FIRST) THEN
3980: ELSIF i = 4 THEN
3981: p_coverages(l_idx).band4 := l_bands(i).band_days;
3982: END IF;
3983:
3984: hr_utility.set_location(' Populating g_covg from each row in l_bands for CAGR id '||p_coverages(l_idx).cagr_id, 60);
3985: hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).g_type||' IDX:'|| l_idx ||' NET:'|| p_coverages(l_idx).net,60);
3986:
3987: IF l_ben.guarantee_type IN ('GN','CA_N') THEN
3988: -- For net CAGRs, net(includes amt for present days) is summed across bands and populated
3981: p_coverages(l_idx).band4 := l_bands(i).band_days;
3982: END IF;
3983:
3984: hr_utility.set_location(' Populating g_covg from each row in l_bands for CAGR id '||p_coverages(l_idx).cagr_id, 60);
3985: hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).g_type||' IDX:'|| l_idx ||' NET:'|| p_coverages(l_idx).net,60);
3986:
3987: IF l_ben.guarantee_type IN ('GN','CA_N') THEN
3988: -- For net CAGRs, net(includes amt for present days) is summed across bands and populated
3989: p_coverages(l_idx).net := l_net + l_bands(i).band_payment;
4002: END IF;
4003:
4004: END IF; -- guarantee_type check
4005:
4006: hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).g_type ||' IDX:'|| l_idx||' NET:'|| p_coverages(l_idx).net,60);
4007:
4008: END LOOP;
4009:
4010: END IF; -- l_bands.COUNT check
4021: ,p_absence_arch => p_absence_arch);
4022:
4023: IF l_ben.guarantee_type IN ('GN','CA_N') THEN
4024: p_coverages(l_idx).net := nvl(p_coverages(l_idx).net,0) + nvl(p_coverages(l_idx).ijss_net_adjustment,0);
4025: hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).net,60);
4026: END IF;
4027:
4028: END IF;
4029:
4031: END IF; -- }new guarantee row
4032:
4033: END LOOP;
4034:
4035: hr_utility.set_location('Leaving:'||l_proc, 90);
4036:
4037: END;
4038:
4039: EXCEPTION
4037: END;
4038:
4039: EXCEPTION
4040: WHEN OTHERS THEN
4041: hr_utility.set_location('Exception in '||l_proc,100);
4042: hr_utility.trace(SQLCODE);
4043: hr_utility.trace(SQLERRM);
4044: Raise;
4045:
4038:
4039: EXCEPTION
4040: WHEN OTHERS THEN
4041: hr_utility.set_location('Exception in '||l_proc,100);
4042: hr_utility.trace(SQLCODE);
4043: hr_utility.trace(SQLERRM);
4044: Raise;
4045:
4046: END CALC_CAGR_GI;
4039: EXCEPTION
4040: WHEN OTHERS THEN
4041: hr_utility.set_location('Exception in '||l_proc,100);
4042: hr_utility.trace(SQLCODE);
4043: hr_utility.trace(SQLERRM);
4044: Raise;
4045:
4046: END CALC_CAGR_GI;
4047: --------------********************--------------
4227: g_absence_calc.ijss_gross_daily_rate := P_gross_daily_rate;
4228: g_absence_calc.initiator := 'CPAM';
4229: --
4230: if g_absence_calc.ijss_estimated = 'Y' then
4231: hr_utility.set_location(' Leaving:'||l_proc, 70);
4232: return 'Y';
4233: else
4234: hr_utility.set_location(' Leaving:'||l_proc, 70);
4235: return 'N';
4230: if g_absence_calc.ijss_estimated = 'Y' then
4231: hr_utility.set_location(' Leaving:'||l_proc, 70);
4232: return 'Y';
4233: else
4234: hr_utility.set_location(' Leaving:'||l_proc, 70);
4235: return 'N';
4236: end if;
4237: END get_sickness_cpam_skip;
4238: --
4314: BEGIN
4315:
4316: IF (g_overlap.COUNT <> 0) THEN
4317: g_overlap.DELETE;
4318: hr_utility.set_location(' Modified count '||g_overlap.COUNT,20);
4319: END IF;
4320:
4321: -- find the number of rows of the global table
4322: l_total_process_days := p_end_date - p_start_date +1;