[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT paa.absence_attendance_id
,paa.date_start
,paa.date_end
,paa.absence_attendance_type_id
FROM per_absence_attendances paa
WHERE paa.absence_attendance_id = p_absence_attendance_id;
SELECT paat.NAME
FROM per_absence_attendance_types paat
WHERE paat.absence_attendance_type_id = p_absence_attendance_type_id ;
PROCEDURE update_absence_plan_details(
p_person_id IN NUMBER
,p_assignment_id IN NUMBER
,p_business_group_id IN NUMBER
,p_legislation_code IN VARCHAR2
,p_effective_date IN DATE
,p_element_type_id IN NUMBER
,p_pl_id IN NUMBER
,p_pl_typ_id IN NUMBER
,p_ler_id IN NUMBER
,p_per_in_ler_id IN NUMBER
,p_absence_attendance_id IN NUMBER
,p_effective_start_date IN DATE
,p_effective_end_date IN DATE -- NULL 31-DEC-4712
,p_formula_outputs IN ff_exec.outputs_t
,p_error_code OUT NOCOPY NUMBER
,p_error_message OUT NOCOPY VARCHAR2
)
IS
l_absence_dates csr_absence_dates%ROWTYPE;
g_package_name || 'update_absence_plan_details';
IF l_absence_dates.absence_attendance_id IS NOT NULL -- absence not been deleted
THEN
IF g_debug THEN
l_proc_step:=20;
pqp_gb_absence_plan_process.update_absence_plan_details(
p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => p_legislation_code
,p_effective_date => p_effective_date
,p_absence_attendance_id => p_absence_attendance_id
,p_absence_date_start => l_absence_dates.date_start
,p_absence_date_end => NVL(
l_absence_dates.date_end
,hr_api.g_eot
)
,p_pl_id => p_pl_id
,p_pl_typ_id => p_pl_typ_id
,p_element_type_id => p_element_type_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_ler_id => p_ler_id
,p_per_in_ler_id => p_per_in_ler_id
,p_formula_outputs => p_formula_outputs
,p_error_code => p_error_code
,p_error_message => p_error_message
);
END update_absence_plan_details;
PROCEDURE delete_absence_plan_details(
p_assignment_id IN NUMBER
,p_business_group_id IN NUMBER
,p_legislation_code IN VARCHAR2
,p_effective_date IN DATE
,p_pl_id IN NUMBER
,p_pl_typ_id IN NUMBER
,p_ler_id IN NUMBER
,p_per_in_ler_id IN NUMBER
,p_absence_attendance_id IN NUMBER
,p_effective_start_date IN DATE
,p_effective_end_date IN DATE -- NULL 31-DEC-4712
,p_formula_outputs IN ff_exec.outputs_t
,p_error_code OUT NOCOPY NUMBER
,p_error_message OUT NOCOPY VARCHAR2
,p_element_type_id IN NUMBER DEFAULT NULL
)
IS
l_proc_step NUMBER(20,10);
:= g_package_name || 'update_absence_plan_details';
pqp_gb_absence_plan_process.delete_absence_plan_details(
p_assignment_id => p_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => p_legislation_code
,p_effective_date => p_effective_date
,p_pl_id => p_pl_id
,p_pl_typ_id => p_pl_typ_id
,p_ler_id => p_ler_id
,p_per_in_ler_id => p_per_in_ler_id
,p_absence_attendance_id => p_absence_attendance_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_formula_outputs => p_formula_outputs
,p_error_code => p_error_code
,p_error_message => p_error_message
);
END delete_absence_plan_details;
SELECT 1
FROM hr_lookups
WHERE lookup_type = 'PQP_GAP_ABSENCE_TYPES_LIST'
AND lookup_code = p_absence_type_id ;
SELECT 1
FROM per_absence_attendances a, per_absence_attendance_types paat
WHERE a.absence_Attendance_id = p_absence_attendance_id
AND paat.absence_attendance_type_id = a. absence_attendance_type_id -- kvinayku
AND EXISTS ( SELECT 1
FROM per_absence_attendances b, per_absence_attendance_types paat1
WHERE a.person_id = b.person_id
AND (
(a.date_start BETWEEN b.date_start
AND NVL(b.date_end,hr_api.g_eot) )
OR (NVL(a.date_end,hr_api.g_eot) BETWEEN b.date_start
AND NVL(b.date_end,hr_api.g_eot) )
OR (b.date_start BETWEEN a.date_start
AND NVL(a.date_end,hr_api.g_eot))
)
AND is_gap_absence_type(b.absence_attendance_type_id) = 1
-- checking only for the absence_type_id in table, it is assumed that a
-- is the driving absence for which the process is triggered and if that
-- has reached this point of code means its a gap absence type
AND a.absence_attendance_id <> b.absence_attendance_id
AND paat1.absence_attendance_type_id = b. absence_attendance_type_id -- kvinayku
AND paat.absence_category = paat1.absence_category -- kvinayku
) ;