APPS.PER_GB_ABSENCE_RULES SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 25

     select 1
     from   per_absence_attendances abs, per_absence_attendance_types paat
     where  paat.absence_category = p_absence_category
     and    paat.absence_attendance_type_id = abs.absence_attendance_type_id
     and    abs.person_id = p_person_id
     and    (p_absence_attendance_id is null or
             p_absence_attendance_id <> abs.absence_attendance_id)
     and    abs.date_start is not null
     and    p_date_start is not null
     and   ((
            to_date(to_char(abs.date_start,'YYYY-MM-DD')|| ' ' ||
            nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
            between
            to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||
            nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
            AND
            to_date(to_char(nvl(p_date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
            nvl(p_time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS'))
          OR
            (
             to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||
             nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
            between
            to_date(to_char(abs.date_start,'YYYY-MM-DD')|| ' ' ||
            nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
            AND
            to_date(to_char(nvl(abs.date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
            nvl(abs.time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS')
       )
       );

Line: 79

select nvl(abs.time_start,'00:00') start_time, nvl(abs.time_end,'23:59') end_time
from   per_absence_attendances abs, per_absence_attendance_types paat
where  paat.absence_category = p_absence_category
and    paat.absence_attendance_type_id = abs.absence_attendance_type_id
and    abs.person_id = p_person_id
and    (p_absence_attendance_id is null or
     p_absence_attendance_id <> abs.absence_attendance_id)
and    abs.date_start is not null
and    p_date_start is not null
and   (abs.date_start between p_date_start AND nvl(p_date_end,hr_api.g_eot)
       OR
    p_date_start between abs.date_start and nvl(abs.date_end,hr_api.g_eot) )
order by 1;

Line: 130

PROCEDURE sickness_date_update
  (p_absence_attendance_id        IN    NUMBER
  ) IS
--
l_proc VARCHAR2(30) ;

Line: 137

SELECT paat.absence_category
FROM   per_absence_attendance_types paat,
       per_absence_attendances paa
WHERE  paa.absence_attendance_id = p_absence_attendance_id
AND    paa.absence_attendance_type_id = paat.absence_attendance_type_id;

Line: 179

END sickness_date_update;

Line: 195

    SELECT paat.absence_category
    FROM   per_absence_attendance_types paat
    WHERE  paat.absence_attendance_type_id = p_absence_attendance_type_id
    AND    paat.business_group_id = p_business_group_id;

Line: 202

    SELECT 1
    FROM   per_absence_attendances PAA
    WHERE  PAA.person_id           = p_person_id
    and    PAA.business_group_id   = p_business_group_id
    AND    PAA.sickness_start_date is not null
    AND    p_date_start          <  (select max(ABS.sickness_start_date)
                                     from per_absence_attendances ABS
		                     where ABS.business_group_id   = p_business_group_id
		                     and   ABS.person_id           = p_person_id);

Line: 262

PROCEDURE validate_abs_update(p_date_start            IN DATE,
                              p_date_end              IN DATE,
                              p_time_start IN VARCHAR2,  -- Bug 6708992
                              p_time_end IN VARCHAR2,    -- Bug 6708992
                              p_sickness_start_date in date, -- Bug 9864927
                              p_sickness_end_date in date,   -- Bug 9864927
                              p_absence_attendance_id IN NUMBER) IS

    cursor csr_abs_details is
    select absence_attendance_type_id,
           business_group_id,
           person_id,
           sickness_start_date,
           sickness_end_date
--7287548 begin
	,date_start,time_start,date_end,time_end
--7287548 End
    from   per_absence_attendances
    where  absence_attendance_id = p_absence_attendance_id;

Line: 284

    SELECT paat.absence_category
    FROM   per_absence_attendance_types paat
    WHERE  paat.absence_attendance_type_id = p_abs_type_id
    AND    paat.business_group_id = p_bus_group;

Line: 292

    SELECT 1
    FROM   per_absence_attendances PAA
    WHERE  PAA.person_id           = p_person_id
    and    PAA.business_group_id   = p_business_group_id
    AND    PAA.sickness_start_date is not null
    AND    PAA.sickness_start_date > p_start
    AND    PAA.absence_attendance_id <> p_absence_attendance_id;

Line: 321

  hr_utility.trace(' Entering PER_GB_ABSENCE_RULES.VALIDATE_ABS_UPDATE ');

Line: 387

         hr_utility.trace(' going to update start or end date, check for future absence ');

Line: 466

hr_utility.trace('Leaving  validate_abs_update ');

Line: 467

END validate_abs_update;

Line: 472

PROCEDURE validate_abs_delete(p_absence_attendance_id   IN NUMBER
                            ) IS



CURSOR get_abs_category IS
SELECT paat.absence_category
FROM   per_absence_attendance_types paat,
       per_absence_attendances paa
WHERE  paa.absence_attendance_id = p_absence_attendance_id
AND    paa.absence_attendance_type_id = paat.absence_attendance_type_id;

Line: 486

    SELECT 1
    FROM   per_absence_attendances PAA
    WHERE  PAA.absence_attendance_id  = p_absence_attendance_id
    AND    PAA.sickness_start_date is not null
    AND    PAA.sickness_start_date <   (select max(ABS.sickness_start_date)
                                        from per_absence_attendances ABS
				        where ABS.person_id =PAA.person_id);

Line: 502

  hr_utility.trace(' Entering PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE ');

Line: 507

     hr_utility.trace('PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE : Fetching absence category');

Line: 511

     hr_utility.trace('PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE : absence category :'||l_abs_category);

Line: 525

 hr_utility.trace(' Leaving PER_GB_ABSENCE_RULES.VALIDATE_ABS_DELETE ');

Line: 526

END validate_abs_delete;