[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select tp.number_per_fiscal_year
into no_periods
from per_time_period_types tp
where tp.period_type = p_proc_period_type;
select min(start_date),
max(end_date),
count(time_period_id)
into earliest_start_date,
latest_end_date,
no_periods
from per_time_periods ptp
where ptp.payroll_id = p_pay_det.payroll_id;
select to_char(p_date,'IW') from dual;
procedure insert_proc_periods(p_pay_det in out nocopy payroll_rec_type,
p_last_update_date in date default sysdate,
p_last_updated_by in number default -1,
p_last_update_login in number default -1,
p_created_by in number default -1,
p_creation_date in date default sysdate,
p_first_gen_date out nocopy date) is
--
proc_name CONSTANT varchar2(50) := 'hr_payrolls.insert_proc_periods';
select tp.number_per_fiscal_year
from per_time_period_types tp
where tp.period_type = p_pay_det.period_type;
select min(ppy.effective_start_date),
max(ppy.effective_end_date)
into payroll_start_date,
payroll_end_date
from pay_all_payrolls_f ppy
where ppy.payroll_id = p_pay_det.payroll_id;
select to_date(plr.rule_mode || '/' ||
to_char(regular_pay_date, 'YYYY'), 'DD/MM/YYYY')
into leg_start_date
from pay_legislation_rules plr
where plr.rule_type = 'L'
and plr.legislation_code = p_pay_det.legislation_code ;
select plr.rule_mode
into rulemode
from pay_legislation_rules plr
where plr.rule_type = 'PDO'
and plr.legislation_code = p_pay_det.legislation_code ;
-- first period end date of the payroll has to be updated
if p_pay_det.first_start_date = p_pay_det.first_gen_start_date and
p_pay_det.first_end_date = p_pay_det.first_gen_end_date then
p_pay_det.first_end_date := period_end_date;
update pay_all_payrolls_f
set first_period_end_date = period_end_date
where payroll_id = p_pay_det.payroll_id;
select NVL(tpt.display_period_type, p_pay_det.period_type)
into l_display_period_type
from per_time_period_types_vl tpt
where tpt.period_type = p_pay_det.period_type;
insert into per_time_periods
(time_period_id,
payroll_id,
start_date,
end_date,
regular_payment_date,
cut_off_date,
pay_advice_date,
default_dd_date,
period_type,
period_num,
period_name,
status,
run_display_number,
quickpay_display_number,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
payslip_view_date)
select
per_time_periods_s.nextval,
p_pay_det.payroll_id,
period_start_date,
period_end_date,
current_regular_pay_date,
period_end_date + p_pay_det.cut_off_date_offset,
period_end_date + p_pay_det.pay_advice_date_offset,
period_end_date + p_pay_det.direct_deposit_date_offset,
p_pay_det.period_type,
l_period_number,
l_period_name,
'O',
1,
1,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date,
Current_regular_pay_date + p_pay_det.payslip_view_date_offset
from sys.dual;
end insert_proc_periods;
select max(end_date)
from per_time_periods
where payroll_id = c_payroll_id;
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
p_created_by in number,
p_creation_date in date) is
--
proc_name CONSTANT varchar2(50) := 'hr_payrolls.create_payroll_proc_periods';
select ppy.number_of_years,
ppy.period_type,
ppy.pay_date_offset,
ppy.cut_off_date_offset,
ppy.pay_advice_date_offset,
ppy.direct_deposit_date_offset,
ppy.first_period_end_date,
ppy.period_reset_years,
hr_api.return_legislation_code(ppy.business_group_id),
ppy.payslip_view_date_offset
into payroll_details.no_years,
payroll_details.period_type,
payroll_details.pay_date_offset,
payroll_details.cut_off_date_offset,
payroll_details.pay_advice_date_offset,
payroll_details.direct_deposit_date_offset,
payroll_details.first_end_date,
payroll_details.period_reset_years,
payroll_details.legislation_code,
payroll_details.payslip_view_date_offset
from pay_all_payrolls ppy
where ppy.payroll_id = p_payroll_id;
insert_proc_periods(payroll_details,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date,
l_first_gen_end_date);
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
p_created_by in number,
p_creation_date in date,
p_effective_date in date ) is
--
proc_name CONSTANT varchar2(50) := 'hr_payrolls.create_payroll_proc_periods';
select ppy.number_of_years,
ppy.period_type,
ppy.pay_date_offset,
ppy.cut_off_date_offset,
ppy.pay_advice_date_offset,
ppy.direct_deposit_date_offset,
ppy.first_period_end_date,
pbg.legislation_code,
ppy.payslip_view_date_offset,
ppy.period_reset_years -- Added for bug 8616134
into payroll_details.no_years,
payroll_details.period_type,
payroll_details.pay_date_offset,
payroll_details.cut_off_date_offset,
payroll_details.pay_advice_date_offset,
payroll_details.direct_deposit_date_offset,
payroll_details.first_end_date,
payroll_details.legislation_code,
payroll_details.payslip_view_date_offset,
payroll_details.period_reset_years -- Added for bug 8616134
from pay_all_payrolls_f ppy,
per_business_groups pbg
where ppy.payroll_id = p_payroll_id
and ppy.business_group_id + 0 = pbg.business_group_id + 0
and ppy.effective_start_date <= p_effective_date
and ppy.effective_end_date >= p_effective_date ;
insert_proc_periods(payroll_details,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date,
l_first_gen_end_date);
SELECT payroll_id,
time_period_id,
effective_date,
start_date,
date_earned,
action_type
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_action_id;
SELECT period_name
FROM per_time_periods
WHERE payroll_id = c_payroll_id
AND c_date_earned between start_date and end_date;