[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(abs.absence_hours, 0),
nvl(abs.absence_days, 0),
to_date('01/01/'||
to_char(abs.date_end,'YYYY'),'DD/MM/YYYY'),
abt.hours_or_days,
abt.increasing_or_decreasing_flag
from per_absence_attendances abs,
per_absence_attendance_types abt
where abs.person_id = p_person_id
and abs.absence_attendance_type_id = abt.absence_attendance_type_id
and abs.date_end is not null
and abs.date_end <= p_effective_date
and abt.input_value_id is not null
and abt.input_value_id = (select abt2.input_value_id
from per_absence_attendance_types abt2
where abt2.absence_attendance_type_id
= p_absence_attendance_type_id);
select abt.hours_or_days
from per_absence_attendance_types abt
where abt.absence_attendance_type_id = p_absence_attendance_type_id;
select nvl(fnd_number.canonical_to_number(pev.screen_entry_value), 0), -- bug 7579341, hint removed
pev.effective_start_date,
pev.effective_end_date
from pay_element_entry_values_f pev,
pay_element_entries_f pee,
per_all_assignments_f paa,
per_absence_attendance_types abt
where pev.element_entry_id = pee.element_entry_id
and pev.input_value_id = abt.input_value_id
and pee.assignment_id = paa.assignment_id
and paa.person_id = p_person_id
and abt.absence_attendance_type_id = p_absence_attendance_type_id
-- and pee.creator_type <> 'A' -- Bug 4422696
and pee.creator_type not in('A','EE','NR','PR','R','RR') -- Bug 4422696
and pee.element_type_id =
(select pet.element_type_id
from pay_element_types_f pet,
pay_input_values_f piv
where abt.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and p_effective_date between piv.effective_start_date
and piv.effective_end_date
and p_effective_date between pet.effective_start_date
and pet.effective_end_date)
and p_effective_date between paa.effective_start_date
and paa.effective_end_date
and paa.primary_flag = 'Y';
select null
from per_all_people_f ppf,
per_periods_of_service pos
where ppf.person_id = p_person_id
and ppf.person_id = pos.person_id
and ppf.current_employee_flag = 'Y'
and ((nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' and
ppf.business_group_id = p_business_group_id)
or nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'Y')
and (p_date_projected_start is null or p_date_projected_start
between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
and (p_date_projected_end is null or p_date_projected_end
between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
and (p_date_start is null or p_date_start
between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
and (p_date_end is null or p_date_end
between pos.date_start and nvl(pos.actual_termination_date,hr_api.g_eot))
union select null
from per_all_people_f ppf,
per_periods_of_placement pop,
per_all_workforce_v pawv
where ppf.person_id = p_person_id
and ppf.person_id = pop.person_id
and ppf.person_id = pawv.person_id
and ppf.current_npw_flag = 'Y'
and ((nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' and
ppf.business_group_id = p_business_group_id)
or nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'Y')
and (p_date_projected_start is null or p_date_projected_start
between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
and (p_date_projected_end is null or p_date_projected_end
between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
and (p_date_start is null or p_date_start
between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot))
and (p_date_end is null or p_date_end
between pop.date_start and nvl(pop.actual_termination_date,hr_api.g_eot));
select ((substr(p_time_end,1,2) * 60) + substr(p_time_end,4,2)) -
((substr(p_time_start,1,2) * 60) + substr(p_time_start,4,2))
from dual;
SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
select abt.hours_or_days,
piv.element_type_id
from per_absence_attendance_types abt,
pay_input_values_f piv
where abt.absence_attendance_type_id = p_absence_attendance_type_id
and abt.input_value_id = piv.input_value_id(+);
select nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
FROM per_all_assignments_f asg,
per_business_groups pbg
WHERE asg.assignment_id = p_assignment_id
AND asg.business_group_id = pbg.business_group_id
AND p_effective_date between asg.effective_start_date
and asg.effective_end_date;
select abt.hours_or_days,
piv.element_type_id
from per_absence_attendance_types abt,
pay_input_values_f piv
where abt.absence_attendance_type_id = p_absence_attendance_type_id
and abt.input_value_id = piv.input_value_id(+);
select nvl(nvl(asg.time_normal_start, pbg.default_start_time), '00:00'),
nvl(nvl(asg.time_normal_finish, pbg.default_end_time), '23:59')
FROM per_all_assignments_f asg,
per_business_groups pbg
WHERE asg.assignment_id = p_assignment_id
AND asg.business_group_id = pbg.business_group_id
AND p_effective_date between asg.effective_start_date
and asg.effective_end_date;
select ff.formula_id
into l_formula_id
from ff_formulas_f ff
where ff.formula_name = 'BG_ABSENCE_DURATION'
and ff.business_group_id = p_business_group_id
and p_effective_date between ff.effective_start_date and
ff.effective_end_date;
select ff.formula_id
into l_formula_id
from ff_formulas_f ff
where ff.formula_name = 'LEGISLATION_ABSENCE_DURATION'
and ff.legislation_code = l_legislation_code
and ff.business_group_id is null
and p_effective_date between ff.effective_start_date and
ff.effective_end_date;
select ff.formula_id
into l_formula_id
from ff_formulas_f ff
where ff.formula_name = 'CORE_ABSENCE_DURATION'
and ff.legislation_code is null
and ff.business_group_id is null
and p_effective_date between ff.effective_start_date and
ff.effective_end_date;
select instr(l_invalid_message,' ',1,1)
into l_invalid_message_num from dual;
select null
from per_absence_attendance_types abt
where abt.absence_attendance_type_id = p_absence_attendance_type_id
and abt.business_group_id = p_business_group_id
and (p_date_projected_start is null or p_date_projected_start
between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
and (p_date_projected_end is null or p_date_projected_end
between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
and (p_date_start is null or p_date_start
between abt.date_effective and nvl(abt.date_end,hr_api.g_eot))
and (p_date_end is null or p_date_end
between abt.date_effective and nvl(abt.date_end,hr_api.g_eot));
select null
from per_abs_attendance_reasons abr,
hr_lookups hrl
where abr.business_group_id = p_business_group_id
and abr.absence_attendance_type_id = p_absence_attendance_type_id
and abr.abs_attendance_reason_id = p_abs_attendance_reason_id
and abr.name = hrl.lookup_code
and hrl.lookup_type = 'ABSENCE_REASON'
and p_effective_date between
nvl(hrl.start_date_active,hr_api.g_sot)
and nvl(hrl.end_date_active,hr_api.g_eot)
and hrl.enabled_flag = 'Y';
select pbg.legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select abt.hours_or_days,
abt.increasing_or_decreasing_flag,
abt.absence_overlap_flag
from per_absence_attendance_types abt
where abt.absence_attendance_type_id = p_absence_attendance_type_id;
select nvl(abs.time_start,'00:00') start_time,
nvl(abs.time_end,'23:59') end_time
from per_absence_attendances abs
where 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;
select null
from per_absence_attendances abs,
per_absence_attendance_types abt
where abs.person_id = p_person_id
and abs.absence_attendance_type_id = abt.absence_attendance_type_id
and (p_absence_attendance_id is null or
p_absence_attendance_id <> abs.absence_attendance_id)
and abs.date_end = p_date_end -1
and abt.absence_category = 'S';
select pap.accrual_plan_id, asg.payroll_id
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv,
per_all_assignments_f asg,
per_absence_attendance_types abt,
pay_accrual_plans pap
where abt.absence_attendance_type_id = p_absence_attendance_type_id
and abt.input_value_id = piv.input_value_id
and piv.input_value_id = pap.pto_input_value_id
and asg.assignment_id = p_assignment_id
and pee.assignment_id = asg.assignment_id
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = pet.element_type_id
and pet.element_type_id = pap.accrual_plan_element_type_id
and p_effective_date between asg.effective_start_date and
asg.effective_end_date
and p_effective_date between pee.effective_start_date and
pee.effective_end_date
and p_effective_date between pel.effective_start_date and
pel.effective_end_date
and p_effective_date between pet.effective_start_date and
pet.effective_end_date
and p_effective_date between piv.effective_start_date and
piv.effective_end_date;
select abtl.name from PER_ABS_ATTENDANCE_TYPES_TL abtl
where abtl.absence_attendance_type_id = p_absence_attendance_type_id
and abtl.language = userenv('LANG');
select pbg.legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select pbg.security_group_id
from per_business_groups pbg
, per_absence_attendances abs
where abs.absence_attendance_id = p_absence_attendance_id
and pbg.business_group_id = abs.business_group_id;
select pbg.legislation_code
from per_business_groups pbg
, per_absence_attendances abs
where abs.absence_attendance_id = p_absence_attendance_id
and pbg.business_group_id = abs.business_group_id;
Procedure chk_non_updateable_args
(p_effective_date in date
,p_rec in per_abs_shd.g_rec_type
) IS
--
l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
End chk_non_updateable_args;
Procedure insert_validate
(p_effective_date in date
,p_rec in per_abs_shd.g_rec_type
,p_dur_dys_less_warning out nocopy boolean
,p_dur_hrs_less_warning out nocopy boolean
,p_exceeds_pto_entit_warning out nocopy boolean
,p_exceeds_run_total_warning out nocopy boolean
,p_abs_overlap_warning out nocopy boolean
,p_abs_day_after_warning out nocopy boolean
,p_dur_overwritten_warning out nocopy boolean
) is
--
l_proc varchar2(72) := g_package||'insert_validate';
select absence_category from per_absence_attendance_types
where absence_attendance_type_id = p_absence_attendance_type_id;
End insert_validate;
Procedure update_validate
(p_effective_date in date
,p_rec in per_abs_shd.g_rec_type
,p_dur_dys_less_warning out nocopy boolean
,p_dur_hrs_less_warning out nocopy boolean
,p_exceeds_pto_entit_warning out nocopy boolean
,p_exceeds_run_total_warning out nocopy boolean
,p_abs_overlap_warning out nocopy boolean
,p_abs_day_after_warning out nocopy boolean
,p_dur_overwritten_warning out nocopy boolean
) is
--
l_proc varchar2(72) := g_package||'update_validate';
chk_non_updateable_args
(p_effective_date => p_effective_date
,p_rec => p_rec
);
End update_validate;
Procedure delete_validate
(p_rec in per_abs_shd.g_rec_type
) is
--
l_proc varchar2(72) := g_package||'delete_validate';
End delete_validate;