[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pap.accrual_plan_id
FROM pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE pee.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pap.accrual_plan_element_type_id = pet.element_type_id
AND pap.accrual_category = p_plan_category ;
select co_formula_id
from pay_accrual_plans
where accrual_plan_id = v_accrual_plan_id;
SELECT pap.accrual_plan_id
FROM pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE pel.element_link_id = pee.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pee.assignment_id = c_assignment_id
AND pet.element_type_id = pap.accrual_plan_element_type_id
AND pap.business_group_id = c_business_group_id
AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pap.accrual_category = (
SELECT lookup_code
FROM hr_lookups
WHERE lookup_type = 'ABSENCE_CATEGORY'
AND meaning = 'Annual Leave');
SELECT NVL(TO_DATE(pev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),pps.date_start)
FROM pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv,
pay_accrual_plans pap,
hr_lookups hrl,
per_assignments_f asg,
per_periods_of_service pps
WHERE pev.element_entry_id = pee.element_entry_id
AND pap.accrual_plan_element_type_id = piv.element_type_id
AND piv.input_value_id = pev.input_value_id
AND pee.entry_type ='E'
AND asg.assignment_id = pee.assignment_id
AND asg.assignment_id = c_assignment_id
AND pap.accrual_plan_id = c_accrual_plan_id
AND asg.business_group_id = c_business_group_id
AND asg.period_of_service_id = pps.period_of_service_id
AND c_calculation_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND c_calculation_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND c_calculation_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND piv.name = hrl.meaning
AND hrl.lookup_type = 'NAME_TRANSLATIONS'
AND hrl.lookup_code = 'PTO_CONTINUOUS_SD';
select tpt.number_per_fiscal_year
from pay_payrolls_f p
, per_time_period_types tpt
where p.payroll_id = p_payroll_id
and p_effective_date between p.effective_start_date
and p.effective_end_date
and tpt.period_type = p.period_type ;
select a.effective_start_date
, a.effective_end_date
, a.normal_hours
, a.frequency
from per_assignments_f a
where a.assignment_id = p_assignment_id
and a.effective_start_date <= p_end_date
and a.effective_end_date >= p_start_date
order by
a.effective_start_date ;
select ab.lower_limit
, ab.upper_limit
, ab.annual_rate
from pay_accrual_bands ab
where ab.accrual_plan_id = p_accrual_plan_id
order by
ab.lower_limit ;
select greatest(tp.start_date,paf.effective_start_date) start_date,least(tp.end_date,paf.effective_end_date) end_date
from per_time_periods tp,per_assignments_f paf
where paf.assignment_id = p_assignment_id
and tp.payroll_id = paf.payroll_id
and tp.start_date <= paf.effective_end_date
and tp.end_date >= paf.effective_start_date
and tp.start_date <= p_end_date
and tp.end_date >= p_start_date
and paf.effective_start_date <= p_end_date
and paf.effective_end_date >= p_start_date
order by tp.start_date ;
SELECT
NVL(SUM(1+
LEAST(effective_end_date, c_end_date)
- GREATEST(effective_start_date, c_start_date)),0)
FROM
per_assignments_f asg
,per_assignment_status_types t
WHERE
assignment_id = c_assignment_id
AND t.assignment_status_type_id = asg.assignment_status_type_id
AND effective_start_date <= c_end_date
AND effective_end_date >= c_start_date
AND per_system_status = 'SUSP_ASSIGN';
select max(tp.end_date)
from per_time_periods tp
where tp.payroll_id = p_payroll_id;
select
sum(nvl(to_number(pev1.screen_entry_value),0))
from
pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_element_entries_f pee
,pay_element_entry_values_f pev1
,pay_element_entry_values_f pev2
where
pee.assignment_id = v_assignment_id
and pet.element_name = v_initialise_type
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and pee.element_entry_id = pev1.element_entry_id
and pev1.input_value_id = piv1.input_value_id
and piv1.name = 'Hours'
and piv1.element_type_id = pet.element_type_id
and pee.element_entry_id = pev2.element_entry_id
and pev2.input_value_id = piv2.input_value_id
and piv2.name = 'Accrual Plan'
and piv2.element_type_id = pet.element_type_id
and pev2.screen_entry_value = pap.accrual_plan_name
and pap.accrual_plan_id = v_accrual_plan_id
and pee.effective_start_date <= v_calc_end_date
and pee.effective_start_date between pet.effective_start_date and pet.effective_end_date
and pee.effective_start_date between pel.effective_start_date and pel.effective_end_date
and pee.effective_start_date between piv1.effective_start_date and piv1.effective_end_date
and pee.effective_start_date between pev1.effective_start_date and pev1.effective_end_date
and pee.effective_start_date between piv2.effective_start_date and piv2.effective_end_date
and pee.effective_start_date between pev2.effective_start_date and pev2.effective_end_date
and pee.effective_start_date between v_start_date and v_end_date;
select (ab.upper_limit - ab.lower_limit)
from pay_accrual_bands ab
where ab.accrual_plan_id = v_accrual_plan_id
order by
ab.lower_limit ;
select 1
from pay_accrual_plans pap
where pap.business_group_id = v_business_group_id
and pap.accrual_plan_name = v_entry_value;