[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select accrual_plan_element_type_id,
accrual_formula_id
from pay_accrual_plans
where accrual_plan_id = p_plan_id;
select 1
from 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 = p_assignment_id
and pet.element_type_id = p_accrual_plan_element_type_id
and p_calculation_date between pee.effective_start_date
and pee.effective_end_date;
select null from
per_all_assignments_f asg,
per_periods_of_service pps
where asg.assignment_id = P_Assignment_ID
and P_calculation_date between asg.effective_start_date
and asg.effective_end_date
and asg.period_of_service_id = pps.period_of_service_id;
select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
from per_absence_attendances abs,
per_absence_attendance_types abt,
pay_net_calculation_rules ncr
where abs.absence_attendance_type_id =
abt.absence_attendance_type_id
and abt.input_value_id = ncr.input_value_id
and ((ncr.absence_attendance_type_id is not null
and ncr.absence_attendance_type_id =
abt.absence_attendance_type_id)
OR (ncr.absence_attendance_type_id is null
and exists
(select 'Y' from pay_accrual_plans
where accrual_plan_id = ncr.accrual_plan_id
and ncr.input_value_id = pto_input_value_id)
))
and exists (select 'Y'
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.person_id = abs.person_id)
and abs.date_start between p_start_date and p_calculation_date
and ncr.accrual_plan_id = p_plan_id;
select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
from per_absence_attendances abs,
pay_net_calculation_rules ncr
where ncr.absence_attendance_type_id = p_absence_attendance_type_id
and ncr.absence_attendance_type_id = abs.absence_attendance_type_id
and exists (select 'Y'
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.person_id = abs.person_id)
and abs.date_start between p_start_date and p_calculation_date
and ncr.accrual_plan_id = p_plan_id;
select nvl(sum(nvl(abs.absence_days, abs.absence_hours)), 0)
from per_absence_attendances abs,
per_absence_attendance_types abt
where abs.absence_attendance_type_id = abt.absence_attendance_type_id
and abt.input_value_id = p_pto_input_value_id
and exists ( select 1
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and abs.person_id = asg.person_id
)
and abs.date_start between p_start_date and p_calculation_date;
select /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53,iv PAY_INPUT_VALUES_F_N50 )*/ fnd_number.canonical_to_number(pev.screen_entry_value) amount,
-- Bug 4551666, bug6621800
ncr.add_or_subtract add_or_subtract
from pay_accrual_plans pap,
pay_net_calculation_rules ncr,
pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f iv
where pap.accrual_plan_id = p_plan_id
and pee.assignment_id = p_assignment_id
and pee.element_entry_id = pev.element_entry_id
and pev.input_value_id = ncr.input_value_id
and pap.accrual_plan_id = ncr.accrual_plan_id
and ncr.input_value_id not in
(pap.co_input_value_id,pap.pto_input_value_id)
and pev.screen_entry_value is not null
and ((p_input_value_id is not null and p_input_value_id = ncr.input_value_id)
or p_input_value_id is null)
and pev.effective_start_date = pee.effective_start_date
and pev.effective_end_date = pee.effective_end_date
and iv.input_value_id = ncr.input_value_id
and p_calculation_date between iv.effective_start_date and iv.effective_end_date
and pee.element_type_id = iv.element_type_id
and exists
(select /*+ index(piv2 PAY_INPUT_VALUES_F_N50)*/ null -- bug6621800
from pay_element_entry_values_f pev1,
pay_input_values_f piv2
where pev1.element_entry_id = pev.element_entry_id
and pev1.input_value_id = ncr.date_input_value_id
and pev1.effective_start_date = pev.effective_start_date
and pev1.effective_end_date = pev.effective_end_date
and ncr.date_input_value_id = piv2.input_value_id
and pee.element_type_id = piv2.element_type_id
and p_calculation_date between piv2.effective_start_date
and piv2.effective_end_date
and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
pev1.screen_entry_value, Null))
between p_start_date and p_calculation_date);
select /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53)*/ fnd_number.canonical_to_number(nvl(pev.screen_entry_value, 0)) carryover,
fnd_date.canonical_to_date(nvl(pev1.screen_entry_value,
p_calculation_date)) expiry_date
from pay_accrual_plans pap,
pay_element_entry_values_f pev,
pay_element_entry_values_f pev1,
pay_input_values_f piv,
pay_input_values_f piv1,
pay_element_entries_f pee
where pap.accrual_plan_id = p_plan_id
and pee.assignment_id = p_assignment_id
and pee.element_entry_id = pev.element_entry_id
and pee.element_entry_id = pev1.element_entry_id
and pev.input_value_id = pap.co_input_value_id
and pev1.input_value_id = pap.co_exp_date_input_value_id
and pap.co_input_value_id = piv.input_value_id
and pap.co_exp_date_input_value_id = piv1.input_value_id
and p_calculation_date between piv.effective_start_date and piv.effective_end_date
and p_calculation_date between piv1.effective_start_date and piv1.effective_end_date
and pee.element_type_id = piv.element_type_id
and pee.element_type_id = piv1.element_type_id
and exists
(select null
from pay_element_entry_values_f pev2,
pay_input_values_f piv2
where pev2.element_entry_id = pev.element_entry_id
and pev2.input_value_id = pap.co_date_input_value_id
and pev2.input_value_id = piv2.input_value_id
and pev2.effective_start_date = pev.effective_start_date
and pev2.effective_end_date = pev.effective_end_date
and pap.co_date_input_value_id = piv2.input_value_id
and pee.element_type_id = piv2.element_type_id
and p_calculation_date between piv2.effective_start_date
and piv2.effective_end_date
and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
pev2.screen_entry_value, Null)) <=
fnd_date.canonical_to_date(nvl(pev1.screen_entry_value,p_calculation_date))
and fnd_date.canonical_to_date(decode(substr(piv2.uom, 1, 1),'D',
pev2.screen_entry_value, Null))
between p_start_date and p_calculation_date)
order by expiry_date;
select defined_balance_id
from pay_accrual_plans
where accrual_plan_id = p_plan_id;
SELECT asg.effective_start_date,
asg.effective_end_date,
ast.per_system_status
FROM per_all_assignments_f asg,
per_assignment_status_types ast
WHERE asg.assignment_id = p_assignment_id
AND ((asg.effective_start_date BETWEEN p_period_sd AND p_period_ed
OR asg.effective_end_date BETWEEN p_period_sd AND p_period_ed)
OR (p_period_sd BETWEEN asg.effective_start_date AND asg.effective_end_date))
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'ACTIVE_ASSIGN';