414:
415: SELECT paa.absence_attendance_id
416: ,paa.date_start
417: --,nvl(paa.date_end,'31-jan-2000') date_end
418: ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
419: ,paa.time_start
420: ,paa.time_end
421: ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
422: (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
417: --,nvl(paa.date_end,'31-jan-2000') date_end
418: ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
419: ,paa.time_start
420: ,paa.time_end
421: ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
422: (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
423: FROM per_absence_attendances paa,
424: per_absence_attendance_types pat
425: WHERE paa.person_id = l_person_id
418: ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
419: ,paa.time_start
420: ,paa.time_end
421: ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
422: (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
423: FROM per_absence_attendances paa,
424: per_absence_attendance_types pat
425: WHERE paa.person_id = l_person_id
426: /*AND paa.date_start >=p_abs_start_date
693:
694: SELECT paa.absence_attendance_id
695: ,paa.date_start
696: --,nvl(paa.date_end,'31-jan-2000') date_end
697: ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
698: ,paa.time_start
699: ,paa.time_end
700: ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
701: (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
696: --,nvl(paa.date_end,'31-jan-2000') date_end
697: ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
698: ,paa.time_start
699: ,paa.time_end
700: ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
701: (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
702: FROM per_absence_attendances paa,
703: per_absence_attendance_types pat
704: WHERE paa.person_id = l_person_id
697: ,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
698: ,paa.time_start
699: ,paa.time_end
700: ,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
701: (least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
702: FROM per_absence_attendances paa,
703: per_absence_attendance_types pat
704: WHERE paa.person_id = l_person_id
705: AND paa.date_start >=p_abs_start_date
3470: csr_v_fourteenth_date DATE)
3471: IS
3472: SELECT paa.absence_attendance_id
3473: ,greatest(paa.date_start,csr_v_fourteenth_date,csr_v_payroll_start_date) date_start
3474: ,least(nvl(paa.date_end,csr_v_payroll_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_payroll_end_date)) date_end
3475: ,paa.time_start
3476: ,paa.time_end
3477: --,(date_end-date_start) date_diff
3478: FROM per_absence_attendances paa,
3566: csr_v_start_date DATE, csr_v_end_date DATE )
3567: IS
3568: SELECT paa.absence_attendance_id
3569: ,greatest(paa.date_start,csr_v_start_date) date_start
3570: ,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
3571: ,paa.time_start
3572: ,paa.time_end
3573: --,(date_end-date_start) date_diff
3574: FROM per_absence_attendances paa,
3621:
3622: /*SELECT paa.absence_attendance_id
3623: ,paa.date_start
3624: --,nvl(paa.date_end,'31-jan-2000') date_end
3625: ,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
3626: ,paa.time_start
3627: ,paa.time_end
3628: ,DECODE(paa.date_start,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)), 1,
3629: (least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date))-paa.date_start)+1) AS days_diff
3624: --,nvl(paa.date_end,'31-jan-2000') date_end
3625: ,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
3626: ,paa.time_start
3627: ,paa.time_end
3628: ,DECODE(paa.date_start,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)), 1,
3629: (least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date))-paa.date_start)+1) AS days_diff
3630: FROM per_absence_attendances paa,
3631: per_absence_attendance_types pat
3632: WHERE paa.person_id = csr_v_person_id
3625: ,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
3626: ,paa.time_start
3627: ,paa.time_end
3628: ,DECODE(paa.date_start,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)), 1,
3629: (least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date))-paa.date_start)+1) AS days_diff
3630: FROM per_absence_attendances paa,
3631: per_absence_attendance_types pat
3632: WHERE paa.person_id = csr_v_person_id
3633: AND paa.date_start >=csr_v_start_date