[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
hr_periods_of_service_bk1.update_pds_details_b for US
legislation. It is used to correctly End Date the Tax
Records (Federal / State / County / City) as per the
Final Process Date entered by the user.
Change List:
------------
Name Date Version Bug Text
------------- ----------- ------- ------- ------------------------------
sudedas 13-NOV-2006 115.0 5460532 Created.
sudedas 02-MAR-2007 115.1 5460532 Corrected Issues experienced
by customers.
sudedas 08-MAR-2007 115.3 5898172 To take care of SSHR Issue
when FPD defaulted to 01Jan(-4712)
*/
procedure update_tax_rules(p_period_of_service_id in number,
p_final_process_date in date) is
/* Fetching Old Final Process Date and Person ID From Period of Service */
cursor c_get_person_oldfpd(p_period_of_service_id in number) is
select distinct pds.person_id,
pds.final_process_date
from per_periods_of_service pds
where pds.period_of_service_id = p_period_of_service_id ;
select distinct assignment_id
from per_assignments_f paf_o,
per_assignment_status_types past
where paf_o.person_id = p_person_id
and paf_o.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'TERM_ASSIGN'
and ((past.business_group_id is null
and past.legislation_code is null)
OR (past.business_group_id is null
and past.legislation_code = 'US')
OR (past.legislation_code is null
and exists
(select 'x'
from per_assignments_f paf_a
where paf_a.assignment_id = paf_o.assignment_id
and paf_a.business_group_id = past.business_group_id)
)
)
and paf_o.effective_end_date = (select max(paf_i.effective_end_date)
from per_assignments_f paf_i
where paf_i.assignment_id = paf_o.assignment_id) ;
select pueo.emp_fed_tax_rule_id,
pueo.effective_start_date,
pueo.effective_end_date
from pay_us_emp_fed_tax_rules_f pueo
where pueo.assignment_id = p_assignment_id
and pueo.effective_start_date = (select max(puei.effective_start_date)
from pay_us_emp_fed_tax_rules_f puei
where puei.assignment_id = pueo.assignment_id
and puei.effective_start_date <= p_final_process_date) ;
select pueo.emp_state_tax_rule_id,
pueo.effective_start_date,
pueo.effective_end_date
from pay_us_emp_state_tax_rules_f pueo
where pueo.assignment_id = p_assignment_id
and pueo.effective_start_date = (select max(puei.effective_start_date)
from pay_us_emp_state_tax_rules_f puei
where puei.assignment_id = pueo.assignment_id
and puei.state_code = pueo.state_code
and puei.effective_start_date <= p_final_process_date) ;
select pueo.emp_county_tax_rule_id,
pueo.effective_start_date,
pueo.effective_end_date
from pay_us_emp_county_tax_rules_f pueo
where pueo.assignment_id = p_assignment_id
and pueo.effective_start_date = (select max(puei.effective_start_date)
from pay_us_emp_county_tax_rules_f puei
where puei.assignment_id = pueo.assignment_id
and puei.state_code = pueo.state_code
and puei.county_code = pueo.county_code
and puei.effective_start_date <= p_final_process_date) ;
select pueo.emp_city_tax_rule_id,
pueo.effective_start_date,
pueo.effective_end_date
from pay_us_emp_city_tax_rules_f pueo
where pueo.assignment_id = p_assignment_id
and pueo.effective_start_date = (select max(puei.effective_start_date)
from pay_us_emp_city_tax_rules_f puei
where puei.assignment_id = pueo.assignment_id
and puei.state_code = pueo.state_code
and puei.county_code = pueo.county_code
and puei.city_code = pueo.city_code
and puei.effective_start_date <= p_final_process_date) ;
SELECT NULL
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,per_assignments_f asg
,per_periods_of_service pds
WHERE pds.period_of_service_id = p_period_of_service_id
AND pds.person_id = asg.person_id
AND asg.period_of_service_id = pds.period_of_service_id
AND asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type NOT IN ('X','BEE')
AND ppa.effective_date > p_new_final_process_date ;
hr_utility.trace('Entering HR_US_PERSON_TERM_LEG_HOOK.update_tax_rules');
update pay_us_emp_fed_tax_rules_f
set effective_end_date = l_final_process_date
where emp_fed_tax_rule_id = l_emp_fed_tax_rule_id
and effective_start_date = l_eff_st_dt
and effective_end_date = l_eff_end_dt ;
update pay_us_emp_state_tax_rules_f
set effective_end_date = l_final_process_date
where emp_state_tax_rule_id = l_emp_st_tax_rule_id
and effective_start_date = l_eff_st_dt
and effective_end_date = l_eff_end_dt ;
update pay_us_emp_county_tax_rules_f
set effective_end_date = l_final_process_date
where emp_county_tax_rule_id = l_emp_county_tax_rule_id
and effective_start_date = l_eff_st_dt
and effective_end_date = l_eff_end_dt ;
update pay_us_emp_city_tax_rules_f
set effective_end_date = l_final_process_date
where emp_city_tax_rule_id = l_emp_city_tax_rule_id
and effective_start_date = l_eff_st_dt
and effective_end_date = l_eff_end_dt ;
hr_utility.trace('Leaving HR_US_PERSON_TERM_LEG_HOOK.update_tax_rules');
end update_tax_rules ;