[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PTP.end_date+1
INTO l_next_to_end_date
FROM per_time_periods ptp,
pay_payroll_actions pact
WHERE pact.payroll_action_id = p_payroll_action_id
AND pact.payroll_id = ptp.payroll_id
AND p_given_date between ptp.start_date and ptp.end_date;
select 1
from pay_payroll_actions ppa2,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = p_owner_payroll_action_id
and ppa2.payroll_action_id = p_user_payroll_action_id
and ppa2.time_period_id <> ppa1.time_period_id;
select fnd_date.canonical_to_date(hoi.org_information11)
from hr_organization_information hoi,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and hoi.organization_id = ppa.business_group_id
and hoi.org_information_context = 'Business Group Information';
select pps.date_start
from per_periods_of_service pps,
per_assignments_f pa,
pay_assignment_actions paa
where paa.assignment_action_id = p_owner_assignment_action_id
and pa.assignment_id = paa.assignment_id
and p_owner_effective_date
between pa.effective_start_date and pa.effective_end_date
and pps.period_of_service_id = pa.period_of_service_id;
select ppos.date_start
from per_periods_of_service ppos,
pay_assignment_actions pac,
per_assignments_f pa
Where pac.assignment_action_id = p_asg_action
and pac.assignment_id = pa.assignment_id
and ppos.period_of_service_id = pa.period_of_service_id
and p_effective_date
between pa.effective_start_date and pa.effective_end_date;
select pps.date_start
from per_periods_of_service pps,
per_assignments_f pa,
pay_assignment_actions paa
where paa.assignment_action_id = p_owner_assignment_action_id
and pa.assignment_id = paa.assignment_id
and p_owner_effective_date
between pa.effective_start_date and pa.effective_end_date
and pps.period_of_service_id = pa.period_of_service_id;
select prt.run_type_name
from pay_run_types_f prt,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and prt.run_type_id = ppa.run_type_id
and ppa.effective_date
between prt.effective_start_date and prt.effective_end_date;
select prt.run_type_name
from pay_run_types_f prt,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_assact_id
and ppa.payroll_action_id = p_payroll_action_id
and prt.run_type_id = paa.run_type_id
and ppa.effective_date
between prt.effective_start_date and prt.effective_end_date;
select run_type_name
from pay_run_types_f
where run_type_id = p_run_type_id
and p_effective_date
between effective_start_date and effective_end_date;
select
prt.run_type_name,
ppa.assignment_set_id,
ptp.start_date
from pay_run_types_f prt,
per_time_periods ptp,
pay_payroll_actions ppa
where ppa.payroll_id = p_payroll_id
and ppa.action_type = 'R'
and ppa.effective_date
between l_soy and p_effective_date
and ptp.time_period_id = ppa.time_period_id
and p_effective_date
not between ptp.start_date and ptp.end_date
and prt.run_type_id = ppa.run_type_id
and ppa.effective_date
between prt.effective_start_date and prt.effective_end_date
and (
prt.run_type_name = 'MTH'
or
/* Bonuses within current payroll period are out of scope. */
(
prt.run_type_name like 'BON\_%' escape '\'
and p_effective_date
not between ptp.start_date and ptp.end_date
)
)
order by ppa.effective_date desc, ppa.action_sequence desc;
select start_date
from per_time_periods
where payroll_id = p_payroll_id
and p_effective_date
between start_date and end_date;
select
fnd_date.canonical_to_date(prrv.result_value)
from pay_run_result_values prrv,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_types_f pet,
pay_input_values_f piv
where
paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = pet.element_type_id
and pet.element_name = 'OVRD_BONUS_PERIOD'
and pet.legislation_code = 'KR'
and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = piv.input_value_id
and piv.name = 'BONUS_PERIOD_START_DATE'
and piv.legislation_code = 'KR'
and ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
select
min(greatest(ptp.start_date, trunc(ppa1.effective_date, 'YYYY')))
from per_time_periods ptp,
pay_run_types_f prt2,
pay_payroll_actions ppa2,
pay_assignment_actions paa2,
pay_run_types_f prt1,
pay_payroll_actions ppa1,
pay_assignment_actions paa1
where paa1.assignment_action_id = p_assignment_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and prt1.run_type_id = paa1.run_type_id
and ppa1.effective_date
between prt1.effective_start_date and prt1.effective_end_date
and paa2.assignment_id = nvl(paa1.assignment_id, prt1.run_type_id)
/* Including current bonus assignment_action_id */
and paa2.action_sequence <= paa1.action_sequence
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type in ('R', 'Q')
and ppa2.effective_date >= trunc(ppa1.effective_date, 'YYYY')
and prt2.run_type_id = paa2.run_type_id
and ppa2.effective_date
between prt2.effective_start_date and prt2.effective_end_date
/* Including current bonus assignment_action_id */
and decode(paa2.assignment_action_id, paa1.assignment_action_id, 'MTH', prt2.run_type_name) = 'MTH'
and ptp.time_period_id = nvl(ppa2.time_period_id, prt2.run_type_id)
and paa2.action_sequence > (
/* Latest bonus with same run_type category as corrent bonus. */
select nvl(max(paa3.action_sequence), 0)
from pay_run_types_f prt3,
pay_payroll_actions ppa3,
pay_assignment_actions paa3
where paa3.assignment_id = paa1.assignment_id
and paa3.action_sequence < paa1.action_sequence
and ppa3.payroll_action_id = paa3.payroll_action_id
and ppa3.action_type in ('R', 'Q')
and ppa3.effective_date >= trunc(ppa1.effective_date, 'YYYY')
/* Bonuses within current payroll period are out of scope. */
and ppa3.time_period_id <> ppa1.time_period_id
and prt3.run_type_id = paa3.run_type_id
and ppa3.effective_date
between prt3.effective_start_date and prt3.effective_end_date
and decode(prt3.run_type_name, 'BON_RWOP', -1, 'BON_I', -1, prt3.run_type_id) = decode(prt1.run_type_name, 'BON_RWOP', -1, 'BON_I', -1, prt1.run_type_id)
);