[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pap.accrual_plan_id plan_id
,pap.accrual_category accrual_category
,pap.information3 information3
,pap.information4 information4
,pap_fw.accrual_plan_id fw_plan_id
,pap_fw.accrual_category fw_accrual_category
,pap_fw.information3 fw_information3
,pap_fw.information3 fw_information4
FROM pay_accrual_plans pap
,pay_accrual_plans pap_fw
where pap.accrual_plan_id = c_accrual_plan_id
AND pap.business_group_id = pap_fw.business_group_id(+)
AND pap.accrual_category = pap_fw.accrual_category(+)
AND pap_fw.information3(+) = 'Y'
AND pap_fw.information4(+) = pap.accrual_plan_id;
select
peev.screen_entry_value accrued,
(to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')) start_date,
(to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')) end_date
from
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_element_types_f pet,
pay_input_values_f piv,
pay_input_values_f piv1,
pay_input_values_f piv2
where pee.assignment_id = p_assignment_id
and pee.element_type_id = pet.element_type_id
and pet.element_name IN ('Statutory Paid Parental Leave Payment', 'Rec Statutory PPL Payment')
and pet.legislation_code = 'AU'
and peev.screen_entry_value = 'N'
and pee.element_entry_id = peev.element_entry_id
and pee.effective_start_date = (select Max(pe.effective_start_date) from pay_element_entries_f pe , pay_element_entry_values_f peev,
pay_input_values_f piv
where pe.assignment_id = p_assignment_id
and pee.element_entry_id= pe.element_entry_id
and peev.screen_entry_value = 'N'
and pe.element_entry_id = peev.element_entry_id
and pe.effective_start_date = peev.effective_start_date
and pe.effective_end_date = peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and piv.name = 'Accrued'
and pe.effective_end_date >= greatest(to_date('01/01/2011','DD/MM/YYYY'), c_start_date)
group by pe.element_entry_id)
and pee.effective_start_date = peev.effective_start_date
and pee.effective_end_date = peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and piv.name = 'Accrued'
and pee.element_entry_id = peev1.element_entry_id
and pee.effective_start_date = peev1.effective_start_date
and pee.effective_end_date = peev1.effective_end_date
and peev1.input_value_id = piv1.input_value_id
and piv1.name = 'Start Date'
and pee.element_entry_id = peev2.element_entry_id
and pee.effective_start_date = peev2.effective_start_date
and pee.effective_end_date = peev2.effective_end_date
and peev2.input_value_id = piv2.input_value_id
and piv2.name = 'End Date'
and piv.element_type_id = pet.element_type_id
and piv1.element_type_id = pet.element_type_id
and piv2.element_type_id = pet.element_type_id
and pee.effective_end_date >= greatest(to_date('01/01/2011','DD/MM/YYYY'), c_start_date)
and p_end_date between piv.effective_start_date and piv.effective_end_date
and p_end_date between piv1.effective_start_date and piv1.effective_end_date
and p_end_date between piv2.effective_start_date and piv2.effective_end_date
and p_end_date between pet.effective_start_date and pet.effective_end_date
and pee.creator_type NOT IN ('EE','RR');
SELECT co_formula_id
FROM pay_accrual_plans
WHERE accrual_plan_id = v_accrual_plan_id;
SELECT information2
FROM pay_accrual_plans
WHERE accrual_plan_id = v_accrual_plan_id;
SELECT LEAST(NVL(pps.actual_termination_date,p_calculation_date), p_calculation_date)
FROM per_periods_of_service pps
,per_assignments_f paf
WHERE paf.assignment_id = v_assignment_id
AND paf.period_of_service_id = pps.period_of_service_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 pee.assignment_id = p_assignment_id
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 /*Added for 8482224*/
AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date /*Added for 8482224*/
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
AND NVL(pap.information3,'N') = 'N'; /* Bug 9950136 */
select co_formula_id
from pay_accrual_plans
where accrual_plan_id = v_accrual_plan_id;
select information2
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(fnd_number.canonical_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 CASE when v_start_date between pee.effective_start_date and pee.effective_end_date
then pee.effective_start_date
else
v_start_date
end
and v_end_date; /*Bug 14789375 */
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;
select min(PEE.EFFECTIVE_START_DATE)
from pay_accrual_plans pap,
pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
per_assignments_f paf,
per_periods_of_service pps
where 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 paf.assignment_id = pee.assignment_id
and paf.period_of_service_id =pps.period_of_service_id
and pee.entry_type ='E'
and pee.assignment_id = v_assignment_id
and pap.accrual_plan_id = v_accrual_plan_id
and pee.effective_end_date >= pps.date_start
and pee.effective_start_date <= nvl(pps.actual_termination_date,to_date('31/12/4712','dd/mm/yyyy'))
/* bug9507714 and pps.date_start between paf.effective_start_date and paf.effective_end_date */
and v_calculation_date between pel.effective_start_date
and pel.effective_end_date
and v_calculation_date between pet.effective_start_date
and pet.effective_end_date;