[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT LEAST(PEE.EFFECTIVE_START_DATE)
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_accrual_plans pap
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 pee.entry_type ='E'
and pee.assignment_id = p_assignment_id
and pap.accrual_plan_id = p_plan_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
and p_effective_date between pet.effective_start_date
and pet.effective_end_date;
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 max(tp.end_date)
from per_time_periods tp
where tp.payroll_id = p_payroll_id;
SELECT accrual_plan_id
FROM pay_accrual_plans
WHERE NVL(business_group_id, p_business_group_id) = p_business_group_id
AND accrual_plan_name = p_accrual_plan_name;
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 + 0 = c_business_group_id
AND c_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pap.accrual_category = 'NZAL' ;
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,
per_all_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 = (
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'NAME_TRANSLATIONS'
AND lookup_code = 'PTO_CONTINUOUS_SD');
SELECT TO_DATE(scl.segment2,'YYYY/MM/DD HH24:MI:SS')
FROM hr_soft_coding_keyflex scl,
per_assignments_f asg
WHERE asg.assignment_id = c_assignment_id
AND asg.business_group_id + 0 = c_business_group_id
AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND scl.id_flex_num = 18
AND c_calculation_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT ptp.end_date
FROM per_time_periods ptp,
per_all_assignments_f paa
WHERE ptp.payroll_id = paa.payroll_id
AND paa.assignment_id = c_assignment_id
AND c_calculation_date BETWEEN ptp.start_date AND ptp.end_date;
SELECT annual_rate
FROM pay_accrual_bands
WHERE c_number_of_years >= lower_limit
AND c_number_of_years < upper_limit
AND accrual_plan_id = c_accrual_plan_id;
select nvl(sum(ab.abs_information2), 0) number_of_complete_weeks
from per_absence_attendances ab
, per_absence_attendance_types aat
, pay_element_entries_f ee
, pay_run_results rr
, pay_assignment_actions aa
, pay_payroll_actions pa
, per_time_periods tp
where aat.absence_attendance_type_id = ab.absence_attendance_type_id
and aat.absence_category in ('NZSL', 'NZVS')
and ee.creator_type = 'A'
and ee.creator_id = ab.absence_attendance_id
and ee.assignment_id = p_assignment_id
and rr.source_id = ee.element_entry_id
and rr.source_type = 'E'
and aa.assignment_action_id = rr.assignment_action_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.effective_date between ee.effective_start_date
and ee.effective_end_date
and tp.time_period_id = pa.time_period_id
and tp.regular_payment_date >= p_start_of_year
and tp.regular_payment_date < add_months(p_start_of_year, 12) ;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = 'Annual Leave Accrual Record'
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
SELECT piv.input_value_id
,piv.name
FROM pay_input_values_f piv
WHERE piv.element_type_id = c_element_type_id
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT pbt.balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = 'Gross Earnings for Holiday Pay'
AND legislation_code = 'NZ'
AND business_group_id IS NULL;
SELECT TPERIOD.start_date,
TPTYPE.number_per_fiscal_year
FROM pay_payroll_actions PACTION,
per_time_periods TPERIOD,
per_time_period_types TPTYPE
where PACTION.payroll_action_id =
(select max(paa.payroll_action_id)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q')
and ppa.payroll_action_id = paa.payroll_action_id)
and PACTION.payroll_id = TPERIOD.payroll_id
and PACTION.date_earned between TPERIOD.start_date and TPERIOD.end_date
and TPTYPE.period_type = TPERIOD.period_type;
select 1
from per_absence_attendances paa,
per_absence_attendance_types paat
where paa.person_id = (select distinct person_id
from per_assignments_f paaf
where paaf.assignment_id = c_assignment_id)
and paa.business_group_id = c_business_group_id
and paa.business_group_id = paat.business_group_id
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.absence_category = 'NZPL'
and (paa.date_start between c_start_date and c_end_date
or paa.date_end between c_start_date and c_end_date );
/* Bug 2798048-NZ Parental leave, update the
previous anniversary date*/
l_prev_anniversary_date := l_anniversary_date;
SELECT TPERIOD.start_date,
TPTYPE.number_per_fiscal_year
FROM pay_payroll_actions PACTION,
per_time_periods TPERIOD,
per_time_period_types TPTYPE
where PACTION.payroll_action_id =
(select max(paa.payroll_action_id)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q')
and ppa.payroll_action_id = paa.payroll_action_id)
and PACTION.payroll_id = TPERIOD.payroll_id
and PACTION.date_earned between TPERIOD.start_date and TPERIOD.end_date
and TPTYPE.period_type = TPERIOD.period_type;
select bt.balance_type_id
from pay_balance_types bt
where bt.balance_name = p_name ;
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 p_effective_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = pet.element_type_id
and p_effective_date between pet.effective_start_date
and pet.effective_end_date
and pap.accrual_plan_element_type_id = pet.element_type_id
and pap.accrual_category = 'NZAL' ;
select ab.absence_hours absence_hours
, to_number(rrv2.result_value) absence_pay
from pay_accrual_plans ap -- annual leave accrualplan
, pay_element_entry_values_f eev -- absence element entry
-- "hours taken" entry value
, pay_element_entries_f ee -- absence element entry
, pay_run_results rr -- run result for absence -- element entry
, per_absence_attendances ab -- absence record
, pay_assignment_actions aa -- assignment action for -- absence element entry
, pay_payroll_actions pa -- payroll action for -- absence element entry
, per_time_periods tp
, pay_run_results rr2 -- run result for Annual -- Leave Pay element type
, pay_run_result_values rrv2 -- run result value for -- Annual Leave Pay element -- pay value ,
, pay_element_types_f et2 -- Annual Leave Pay element
, pay_input_values_f iv2 -- Pay Value input value
where ap.accrual_plan_id = p_accrual_plan_id
and eev.input_value_id = ap.pto_input_value_id
and ee.element_entry_id = eev.element_entry_id
and ee.assignment_id = p_assignment_id
and rr.source_id = ee.element_entry_id
and rr.source_type = 'E'
and ee.creator_type = 'A'
and ab.absence_attendance_id = ee.creator_id
and aa.assignment_action_id = rr.assignment_action_id
and pa.payroll_action_id = aa.payroll_action_id
and (
(tp.regular_payment_date <= p_year_end_date
and pa.effective_date between ee.effective_start_date
and ee.effective_end_date
and pa.effective_date between eev.effective_start_date
and eev.effective_end_date
and pa.time_period_id=tp.time_period_id
)
or
(
pa.payroll_id = tp.payroll_id
and pa.date_earned between tp.start_date and tp.end_date
and p_year_end_date >= tp.start_date
and pa.date_earned between ee.effective_start_date
and ee.effective_end_date
and pa.date_earned between eev.effective_start_date
and eev.effective_end_date
)
)
and et2.element_name = 'Annual Leave Pay'
and pa.effective_date between et2.effective_start_date
and et2.effective_end_date
and rr2.element_type_id = et2.element_type_id
and rr2.source_id = ee.element_entry_id
and rr2.source_type = 'I'
and rr2.assignment_action_id = aa.assignment_action_id
and rrv2.run_result_id = rr2.run_result_id
and iv2.input_value_id = rrv2.input_value_id
and pa.effective_date between iv2.effective_start_date
and iv2.effective_end_date
and iv2.name = 'Pay Value'
order by
aa.action_sequence desc
, ab.date_start desc
, to_date(ab.time_start, 'hh24:mi') ;
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 tp.start_date
, tp.end_date
from per_time_periods tp
where tp.payroll_id = p_payroll_id
and tp.start_date <= p_end_date
and tp.end_date >= p_start_date
order by
tp.start_date ;
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 tp.start_date
, tp.end_date
from per_time_periods tp
where tp.payroll_id = p_payroll_id
and tp.start_date <= p_end_date
and tp.end_date >= p_start_date
order by
tp.start_date ;
SELECT pbt.balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = 'Gross Earnings for Holiday Pay'
AND legislation_code = 'NZ'
AND business_group_id IS NULL;
select prv.result_value
from pay_run_result_values prv
, pay_run_results prr
, pay_input_values_f piv
, pay_element_types_f pet
, pay_element_entries_f pee
where pet.element_name = 'Annual Leave Pay'
and pet.legislation_code = 'NZ'
and pet.element_type_id = piv.element_type_id
and piv.name = 'Pay Value'
and prv.input_value_id = piv.input_value_id
and prr.run_result_id = prv.run_result_id
and pee.element_entry_id = p_element_entry_id
AND PRR.RUN_RESULT_ID = PEE.SOURCE_ID
and prr.assignment_action_id = p_assgt_action_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 pee.effective_start_date and pee.effective_end_date;
SELECT TPERIOD.start_date,
TPTYPE.number_per_fiscal_year
FROM pay_payroll_actions PACTION,
per_time_periods TPERIOD,
per_time_period_types TPTYPE
where PACTION.payroll_action_id =
(select max(paa.payroll_action_id)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q')
and ppa.payroll_action_id = paa.payroll_action_id)
and PACTION.payroll_id = TPERIOD.payroll_id
and PACTION.date_earned between TPERIOD.start_date and TPERIOD.end_date
and TPTYPE.period_type = TPERIOD.period_type;
SELECT action_type
FROM pay_payroll_actions ppa
, pay_payrolls_f ppf
WHERE ppf.payroll_id = p_payroll_id
AND ppa.payroll_id = ppf.payroll_id
AND ppa.business_group_id = ppf.business_group_id
AND (ppa.consolidation_set_id = ppf.consolidation_set_id
OR ppa.consolidation_set_id IS NULL)
AND ppa.action_type LIKE '%'
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
ORDER BY PAYROLL_ACTION_ID DESC;
select max(ppa.effective_date)
,ppa.payroll_id
from pay_payroll_actions ppa
,pay_assignment_actions pac
where pac.assignment_id = p_assignment_id
and pac.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = 'L'
group by ppa.payroll_id ;
select ptp.start_date
from per_time_periods ptp
,pay_all_payrolls_f pap
where pap.payroll_id = p_payroll_id
and ptp.payroll_id = pap.payroll_id
and (p_effective_date - pap.PAY_DATE_OFFSET) between ptp.start_date and ptp.end_date;
select balance_type_id
from pay_balance_types
where balance_name = p_name;
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_adjustment_element
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
/* Start date of adjustment entry must be before end of accrual */
and pee.effective_start_date <= v_calc_end_date
/* End date of adjustment entry must be after start of accrual */
and pee.effective_end_date >= v_start_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;
select 'EXISTS'
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and pee.creator_type = 'RR';