[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(asg.effective_end_date - asg.effective_start_date + 1) days_inactive
from per_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';
select person_id
from per_assignments_f
where assignment_id = p_assignment_id
and p_calculation_date between effective_start_date and effective_end_date;
select nvl(to_number(val.screen_entry_value),0)
from pay_element_entries_f ent
, pay_element_types_f el
, pay_input_values_f piv
, pay_element_entry_values_f val
, pay_element_links_f link
where ent.assignment_id = p_assignment_id
and ent.element_entry_id = val.element_entry_id
and p_effective_date between ent.effective_start_date and ent.effective_end_date
and ent.element_link_id = link.element_link_id
and link.element_type_id = el.element_type_id
and el.element_name = 'Leave Information'
and el.legislation_code = g_legislation_code
and p_effective_date between el.effective_start_date and el.effective_end_date
and el.element_type_id = piv.element_type_id
and piv.name = 'Standard Work Week'
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and val.input_value_id = piv.input_value_id
and p_effective_date between val.effective_start_date and val.effective_end_date;
select nvl(val.screen_entry_value,'Y')
from pay_element_entries_f ent
, pay_element_types_f el
, pay_input_values_f piv
, pay_element_entry_values_f val
, pay_element_links_f link
where ent.assignment_id = p_assignment_id
and ent.element_entry_id = val.element_entry_id
and p_effective_date between ent.effective_start_date and ent.effective_end_date
and ent.element_link_id = link.element_link_id
and link.element_type_id = el.element_type_id
and el.element_name = 'Leave Information'
and el.legislation_code = g_legislation_code
and p_effective_date between el.effective_start_date and el.effective_end_date
and el.element_type_id = piv.element_type_id
and piv.name = 'Use Assignment Working Hours'
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and val.input_value_id = piv.input_value_id
and p_effective_date between val.effective_start_date and val.effective_end_date;
select ab.absence_attendance_id
, ab.person_id
, ab.absence_days
, ab.absence_hours
, abt.hours_or_days
, ab.date_start
, ab.date_end
, ((ab.date_end - ab.date_start) - g_unpaid_absence_days) add_days
-- however, do not move the initial qualifying period
from per_absence_attendances ab
, per_absence_attendance_types abt
where ab.absence_attendance_type_id = abt.absence_attendance_type_id
and ab.person_id = p_person_id
and abt.absence_category = g_unpaid_absence_category
and ab.date_start between p_start_date and p_end_date
and ((ab.date_end - ab.date_start) > g_unpaid_absence_days);
select accrual_units_of_measure
from pay_accrual_plans
where accrual_plan_id = p_accrual_plan_id;
select defined_balance_id
from pay_defined_balances pdb
, pay_balance_types pbt
, pay_balance_dimensions dim
where pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = dim.balance_dimension_id
and pbt.balance_name = p_balance_name
and dim.dimension_name = p_dimension_name
and dim.legislation_code = g_legislation_code
and pbt.legislation_code = g_legislation_code;
SELECT MAX(paa.assignment_action_id)
FROM pay_assignment_actions paa
, per_assignments_f paf
, pay_payrolls_f ppf
, pay_payroll_actions ppa
, per_time_periods ptp
WHERE paf.assignment_id = p_assignment_id
AND ppa.action_type in ('R','Q')
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppf.payroll_id = paf.payroll_id
AND ppa.time_period_id = ptp.time_period_id
AND ppf.payroll_id = ppa.payroll_id
AND ppa.effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND p_effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND ppf.payroll_id = ppa.payroll_id
AND ppf.payroll_id = ptp.payroll_id
AND paf.assignment_id = paa.assignment_id
AND paa.action_status IN ('C','P','U')
AND ppa.action_status IN ('C','P','U')
GROUP BY paa.assignment_action_id
HAVING paa.assignment_action_id = MAX(paa.assignment_action_id);
select defined_balance_id
from pay_defined_balances pdb
, pay_balance_types pbt
, pay_balance_dimensions dim
where pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = dim.balance_dimension_id
and pbt.balance_name = p_balance_name
and dim.dimension_name = p_dimension_name
and dim.legislation_code = g_legislation_code
and pbt.legislation_code = g_legislation_code;
SELECT date_start
FROM per_periods_of_service pps
, per_assignments_f paf
WHERE pps.period_of_service_id = paf.period_of_service_id
AND pps.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id;
select normal_hours
, frequency
from per_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
select ptp.end_date - ptp.start_date days_in_period
from per_time_periods ptp
, per_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_current_day between paf.effective_start_date and paf.effective_end_date
and paf.payroll_id = ptp.payroll_id
and p_current_day between ptp.start_date and ptp.end_date;
select normal_hours
, frequency
from per_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
select ptp.end_date - ptp.start_date days_in_period
from per_time_periods ptp
, per_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_current_day between paf.effective_start_date and paf.effective_end_date
and paf.payroll_id = ptp.payroll_id
and p_current_day between ptp.start_date and ptp.end_date;
select bnd.annual_rate annual_rate
, acc.accrual_units_of_measure uom
from pay_accrual_bands bnd
, pay_accrual_plans acc
where p_number_of_years >= bnd.lower_limit
and p_number_of_years < bnd.upper_limit
and bnd.accrual_plan_id = p_accrual_plan_id
and bnd.accrual_plan_id = acc.accrual_plan_id;
select ast.per_system_status,asg.EFFECTIVE_START_DATE,asg.EFFECTIVE_END_DATE
bulk collect into l_system_status, l_eff_start_date, l_eff_end_date
from per_assignments_f asg
,per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'ACTIVE_ASSIGN';
select (ab.date_start + g_unpaid_absence_days + 1), ab.date_end
bulk collect into l_abs_start_date, l_abs_end_date
from per_absence_attendances ab
, per_absence_attendance_types abt
where ab.absence_attendance_type_id = abt.absence_attendance_type_id
and ab.person_id = l_person_id
and abt.absence_category = g_unpaid_absence_category
and ((ab.date_end - ab.date_start) > g_unpaid_absence_days);
select normal_hours,frequency,effective_start_date,effective_end_date
bulk collect into l_normal_hours,l_frequency,l_wrk_eff_start_date,l_wrk_eff_end_date
from per_assignments_f
where assignment_id = p_assignment_id;
select fnd_number.canonical_to_number(information1)
from pay_accrual_plans pap
where information_category = 'NZ_NZAL'
and pap.accrual_plan_id = p_accrual_plan_id;
select pptp.regular_payment_date
from per_time_periods bptp
, per_time_periods pptp
where bptp.payroll_id = p_payroll_id -- identify driving period
and bptp.time_period_id = p_time_period_id -- identify driving period
and pptp.payroll_id = p_payroll_id -- match payroll
and bptp.start_date = pptp.end_date + 1; -- idenfity previous period
select co_formula_id
from pay_accrual_plans
where accrual_plan_id = p_accrual_plan_id;
select to_number(result.result_value)
from pay_run_results runs
, pay_input_values_f input
, pay_run_result_values result
, pay_assignment_actions paa
, pay_assignment_actions cur_paa
, pay_payroll_actions cur_ppa
, pay_element_entries_f entry
, per_time_periods ptp
where runs.element_type_id = p_element_type_id
and input.element_type_id = runs.element_type_id
and input.name = p_rate_name
and result.run_result_id = runs.run_result_id
and result.input_value_id = input.input_value_id
and cur_paa.assignment_action_id = p_assignment_action_id
and cur_ppa.payroll_action_id = cur_paa.payroll_action_id
and paa.assignment_action_id = runs.assignment_action_id
and paa.assignment_id = cur_paa.assignment_id
and entry.assignment_id = cur_paa.assignment_id
and entry.element_entry_id = runs.source_id
and ptp.time_period_id = cur_ppa.time_period_id
and cur_ppa.effective_date between input.effective_start_date
and input.effective_end_date
and (ptp.start_date between entry.effective_start_date
and entry.effective_end_date
or ptp.end_date between entry.effective_start_date
and entry.effective_end_date);
SELECT 1
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_assignments_f paf
WHERE paa.person_id = paf.person_id
AND paf.assignment_id = p_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 )
AND c_end_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT ptp.time_period_id
,ptp.end_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND c_eff_date BETWEEN ptp.start_date
AND ptp.end_date;
SELECT max(paa.assignment_action_id)
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_id = p_payroll_id
AND ppa.time_period_id = c_time_period_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.action_status = 'C';
SELECT to_number(nvl(prrv.result_value,0))
FROM pay_run_result_values prrv
,pay_run_results prr
,pay_element_types_f alp_pet
,pay_input_values_f alp_piv
,pay_element_entries_f pee
,pay_element_links_f pel
,pay_element_types_f abs_pet
,pay_input_values_f abs_piv
,pay_accrual_plans pap
WHERE prr.run_result_id = prrv.run_result_id
AND prr.assignment_action_id = c_assignment_action_id
AND prr.element_type_id = alp_pet.element_type_id
AND alp_pet.element_name = 'Annual Leave Payment'
AND alp_pet.element_type_id = alp_piv.element_type_id
AND alp_piv.name = 'Leave Taken'
AND alp_piv.input_value_id = prrv.input_value_id
AND prr.source_id = pee.element_entry_id
AND pee.creator_type = 'A'
AND pee.effective_end_date > c_prev_period_end_date
AND pee.element_link_id = pel.element_link_id
AND pel.element_type_id = abs_pet.element_type_id
AND abs_pet.processing_type = 'R'
AND abs_pet.element_type_id = abs_piv.element_type_id
AND abs_piv.input_value_id = pap.pto_input_value_id
AND pap.accrual_plan_id = p_plan_id
AND c_prev_period_end_date BETWEEN alp_pet.effective_start_date
AND alp_pet.effective_end_date
AND c_prev_period_end_date BETWEEN alp_piv.effective_start_date
AND alp_piv.effective_end_date
AND c_prev_period_end_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND c_prev_period_end_date BETWEEN abs_pet.effective_start_date
AND abs_pet.effective_end_date
AND c_prev_period_end_date BETWEEN abs_piv.effective_start_date
AND abs_piv.effective_end_date;
SELECT nvl(nvl(sum(absence_days),sum(absence_hours)),0) cnt_abs
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,pay_accrual_plans pap
,per_assignments_f paf
WHERE paa.person_id = paf.person_id
AND paf.assignment_id = c_assignment_id
AND paa.business_group_id = c_business_group_id
AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
AND pap.accrual_plan_id = p_plan_id
AND pap.accrual_category = 'NZAL'
AND paa.date_start BETWEEN c_start_date
AND c_end_date
AND paa.date_end BETWEEN c_start_date
AND c_end_date
AND c_end_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT ptp.start_date
FROM per_time_periods ptp
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.time_period_id = ptp.time_period_id;