[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.payroll_id,
a.effective_start_date,
a.effective_end_date,
a.business_group_id,
b.DATE_START,
b.ACTUAL_TERMINATION_DATE
from PER_ASSIGNMENTS_F a,
PER_PERIODS_OF_SERVICE b
where a.assignment_id = P_assignment_id
and P_calculation_date between a.effective_start_date and
a.effective_end_date
and a.PERIOD_OF_SERVICE_ID = b.PERIOD_OF_SERVICE_ID;
select PERIOD_NUM,
START_DATE,
END_DATE
from PER_TIME_PERIODS
where PAYROLL_ID = p_payroll_id
and p_effective_date between START_DATE and END_DATE;
select sum(to_number(nvl(pev.SCREEN_ENTRY_VALUE,'0')) *
to_number(pnc.add_or_subtract))
from pay_net_calculation_rules pnc,
pay_element_entry_values_f pev,
pay_element_entries_f pee
where pnc.accrual_plan_id = p_plan_id
and pnc.input_value_id = pev.input_value_id + 0
and pev.element_entry_id = pee.element_entry_id
and pee.assignment_id = P_assignment_id
and pee.effective_start_date between P_start_date and
P_end_date;
select min(start_date),
min(end_date),
max(start_date),
max(end_date),
count(period_num)
from per_time_periods
where payroll_id = p_payroll_id
-- and to_char(P_date,'YYYY/MM/DD') = to_char(end_date,'YYYY/MM/DD'); -- bug 6706398
select count(*)
into l_grade_step
from per_spinal_point_steps_f psps
, per_spinal_points psp
, per_spinal_point_steps_f psps1
where psps.grade_spine_id = p_grade_spine_id
and psps.step_id = p_step_id
and psps1.grade_spine_id = psps.grade_spine_id
and psp.spinal_point_id = psps.spinal_point_id
and psps.sequence >= psps1.sequence
and psp.parent_spine_id = p_parent_spine_id
and p_effective_start_date between psps.effective_start_date
and psps.effective_end_date
and p_effective_start_date between psps1.effective_start_date
and psps1.effective_end_date ;
select gr.minimum
, gr.mid_value
, gr.maximum
--changes for bug no 5945278 starts here
, a.PAY_BASIS_ID
--changes for bug no 5945278 ends here
from pay_grade_rules_f gr
, per_assignments_f a
, per_pay_proposals pp
where gr.grade_or_spinal_point_id = a.grade_id
and pp.change_date between gr.effective_start_date
and gr.effective_end_date
and pp.change_date between a.effective_start_date
and a.effective_end_date
and a.assignment_id = pp.assignment_id
and pp.assignment_id = l_assignment_id
and gr.grade_or_spinal_point_id = l_grade_id
and gr.rate_id = l_rate_id
and l_change_date between gr.effective_start_date
and gr.effective_end_date
order by gr.effective_start_date desc ;
select PAY_ANNUALIZATION_FACTOR,GRADE_ANNUALIZATION_FACTOR from PER_PAY_BASES
where PAY_BASIS_ID = l_PAY_BASIS_ID;
select fnd_number.canonical_to_number(working_hours)
into v_working_hours
from per_business_groups
where business_group_id = p_business_group_id ;
select frequency
into v_frequency
from per_business_groups
where business_group_id = p_business_group_id ;
select str.organization_id_parent
into org_id_parent
from per_org_structure_elements str
where level = p_level
connect by str.organization_id_child = prior str.organization_id_parent
and str.org_structure_version_id = p_org_structure_version_id
and str.business_group_id = p_business_group_id
start with str.organization_id_child = p_org_child
and str.org_structure_version_id = p_org_structure_version_id
and str.business_group_id = p_business_group_id ;
select min(EFFECTIVE_START_DATE) - 1
from PER_ALL_ASSIGNMENTS_F
where ASSIGNMENT_ID = p_assignment_id
and EFFECTIVE_START_DATE > p_effective_start_date ;
select max(EFFECTIVE_END_DATE)
from PER_ALL_ASSIGNMENTS_F
where ASSIGNMENT_ID = p_assignment_id ;
select count(distinct PERSON_ID)
into l_number_of_emps
from PER_ASSIGNMENTS_X ass
where ass.ORGANIZATION_ID = p_organization_id
and ass.ASSIGNMENT_TYPE = 'E' ;
select nvl(sum(to_number(nvl(pev.SCREEN_ENTRY_VALUE,'0'))), 0)
into l_accrual
from pay_element_entry_values_f pev,
pay_element_entries_f pee
where pev.input_value_id = p_input_value_id
and pev.element_entry_id = pee.element_entry_id
and pee.assignment_id = p_assignment_id
and pee.effective_start_date between d6 and d7 ;
select pap.accrual_plan_id,
pap.accrual_plan_element_type_id,
pap.accrual_units_of_measure,
pap.ineligible_period_type,
pap.ineligible_period_length,
pap.accrual_start,
pev.SCREEN_ENTRY_VALUE,
pee.element_entry_id
from pay_accrual_plans pap,
pay_element_entry_values_f pev,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv
where ( pap.accrual_plan_id = p_plan_id OR
pap.accrual_category = P_plan_category )
and pap.business_group_id + 0 = P_business_group
and pap.accrual_plan_element_type_id = pet.element_type_id
and P_calculation_date between pet.effective_start_date and
pet.effective_end_date
and pet.element_type_id = pel.element_type_id
and P_calculation_date between pel.effective_start_date and
pel.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = P_assignment_id
and P_calculation_date between pee.effective_start_date and
pee.effective_end_date
and piv.element_type_id =
pap.accrual_plan_element_type_id
and piv.name = 'Continuous Service Date'
and P_calculation_date between piv.effective_start_date and
piv.effective_end_date
and pev.element_entry_id = pee.element_entry_id
and pev.input_value_id + 0 = piv.input_value_id
and P_calculation_date between pev.effective_start_date and
pev.effective_end_date;
SELECT number_per_fiscal_year
INTO l_number_of_period
FROM per_time_period_types TPT,
pay_payrolls_f PPF
WHERE TPT.period_type = PPF.period_type
AND PPF.payroll_id = P_payroll_id
AND l_calc_end_date BETWEEN PPF.effective_start_date
AND PPF.effective_end_date;
select min(effective_start_date)
into l_plan_start_date
from pay_element_entries_f
where element_entry_id = l_element_entry_id;
select a.effective_start_date,
a.effective_end_date,
b.PER_SYSTEM_STATUS
from per_assignments_f a,
per_assignment_status_types b
where a.assignment_id = P_assignment_id
and a.effective_end_date between p_first_p_start_date and
hr_general.end_of_time
and a.ASSIGNMENT_STATUS_TYPE_ID =
b.ASSIGNMENT_STATUS_TYPE_ID;
select annual_rate,
ceiling,
lower_limit,
upper_limit,
max_carry_over
from pay_accrual_bands
where accrual_plan_id = P_plan_id
and P_time_worked >= lower_limit
and P_time_worked < upper_limit;
select start_date,
end_date,
period_num
from per_time_periods
where to_char(end_date,'YYYY/MM/DD') =
to_char(p_accrual_calc_p_end_date,'YYYY/MM/DD')
and end_date <= p_accrual_calc_p_end_date
and period_num >=
decode (to_char(p_first_p_start_date,'YYYY/MM/DD'),
to_char(p_accrual_calc_p_end_date,'YYYY/MM/DD'),
p_first_calc_P_number, 1)
and payroll_id = p_payroll_id
ORDER by period_num; */
select start_date,
end_date,
period_num
from per_time_periods
where to_char(end_date,'YYYY') =
to_char(p_accrual_calc_p_end_date,'YYYY')
and end_date <= p_accrual_calc_p_end_date
and period_num >=
decode (to_char(p_first_p_start_date,'YYYY'),
to_char(p_accrual_calc_p_end_date,'YYYY'),
p_first_calc_P_number, 1)
and payroll_id = p_payroll_id
ORDER by period_num;