[Home] [Help]
86: ) is
87: -- need to add 1 as to include the finale day
88: -- e.g. 01-JAN-2000 minus 01-JAN-2000 is actually 1 day but the arithmetic returns 0
89: select sum(asg.effective_end_date - asg.effective_start_date + 1) days_inactive
90: from per_assignments_f asg
91: , per_assignment_status_types ast
92: where asg.assignment_id = p_assignment_id
93: and ((asg.effective_start_date between p_period_sd and p_period_ed
94: or asg.effective_end_date between p_period_sd and p_period_ed)
100: (p_assignment_id number
101: ,p_calculation_date date
102: ) is
103: select person_id
104: from per_assignments_f
105: where assignment_id = p_assignment_id
106: and p_calculation_date between effective_start_date and effective_end_date;
107: --
108: -- Cursor to retrieve the input value of
245: ,p_effective_date DATE
246: ) IS
247: SELECT MAX(paa.assignment_action_id)
248: FROM pay_assignment_actions paa
249: , per_assignments_f paf
250: , pay_payrolls_f ppf
251: , pay_payroll_actions ppa
252: , per_time_periods ptp
253: WHERE paf.assignment_id = p_assignment_id
481: CURSOR csr_hire_date
482: IS
483: SELECT date_start
484: FROM per_periods_of_service pps
485: , per_assignments_f paf
486: WHERE pps.period_of_service_id = paf.period_of_service_id
487: AND pps.person_id = paf.person_id
488: AND paf.assignment_id = p_assignment_id;
489: --
851: --
852: cursor csr_asg_hours(p_effective_date date) is
853: select normal_hours
854: , frequency
855: from per_assignments_f
856: where assignment_id = p_assignment_id
857: and p_effective_date between effective_start_date and effective_end_date;
858: --
859: cursor csr_get_days_in_period is
858: --
859: cursor csr_get_days_in_period is
860: select ptp.end_date - ptp.start_date days_in_period
861: from per_time_periods ptp
862: , per_assignments_f paf
863: where paf.assignment_id = p_assignment_id
864: and p_current_day between paf.effective_start_date and paf.effective_end_date
865: and paf.payroll_id = ptp.payroll_id
866: and p_current_day between ptp.start_date and ptp.end_date;
1044: --
1045: cursor csr_asg_hours(p_effective_date date) is
1046: select normal_hours
1047: , frequency
1048: from per_assignments_f
1049: where assignment_id = p_assignment_id
1050: and p_effective_date between effective_start_date and effective_end_date;
1051: --
1052: cursor csr_get_days_in_period is
1051: --
1052: cursor csr_get_days_in_period is
1053: select ptp.end_date - ptp.start_date days_in_period
1054: from per_time_periods ptp
1055: , per_assignments_f paf
1056: where paf.assignment_id = p_assignment_id
1057: and p_current_day between paf.effective_start_date and paf.effective_end_date
1058: and paf.payroll_id = ptp.payroll_id
1059: and p_current_day between ptp.start_date and ptp.end_date;
1252: close csr_person_id;
1253:
1254: select ast.per_system_status,asg.EFFECTIVE_START_DATE,asg.EFFECTIVE_END_DATE
1255: bulk collect into l_system_status, l_eff_start_date, l_eff_end_date
1256: from per_assignments_f asg
1257: ,per_assignment_status_types ast
1258: where asg.assignment_id = p_assignment_id
1259: and asg.assignment_status_type_id = ast.assignment_status_type_id
1260: and ast.per_system_status <> 'ACTIVE_ASSIGN';
1269: and ((ab.date_end - ab.date_start) > g_unpaid_absence_days);
1270:
1271: select normal_hours,frequency,effective_start_date,effective_end_date
1272: bulk collect into l_normal_hours,l_frequency,l_wrk_eff_start_date,l_wrk_eff_end_date
1273: from per_assignments_f
1274: where assignment_id = p_assignment_id;
1275:
1276: l_eligible_inactive:= true;
1277: l_eligible_abs:= true;
2179: --
2180: SELECT 1
2181: FROM per_absence_attendances paa
2182: ,per_absence_attendance_types paat
2183: ,per_assignments_f paf
2184: WHERE paa.person_id = paf.person_id
2185: AND paf.assignment_id = p_assignment_id
2186: AND paa.business_group_id = c_business_group_id
2187: AND paa.business_group_id = paat.business_group_id
2434: SELECT nvl(nvl(sum(absence_days),sum(absence_hours)),0) cnt_abs
2435: FROM per_absence_attendances paa
2436: ,per_absence_attendance_types paat
2437: ,pay_accrual_plans pap
2438: ,per_assignments_f paf
2439: WHERE paa.person_id = paf.person_id
2440: AND paf.assignment_id = c_assignment_id
2441: AND paa.business_group_id = c_business_group_id
2442: AND paa.absence_attendance_type_id = paat.absence_attendance_type_id