[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hsck.segment18
from HR_SOFT_CODING_KEYFLEX hsck,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment
and p_session_dt between paf.effective_start_date
and paf.effective_end_date
and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and hsck.segment18 is not null;
select sta.state_code, cnt.county_code, cty.city_code,
sta2.state_code, cnt2.county_code, cty2.city_code
from hr_locations loc
,pay_us_states sta
,pay_us_counties cnt
,pay_us_city_names cty
,pay_us_states sta2
,pay_us_counties cnt2
,pay_us_city_names cty2
where loc.location_id = p_lc_id
AND cnt.state_code = sta.state_code
AND loc.town_or_city = cty.city_name
AND loc.region_1 = cnt.county_name
AND loc.region_2 = sta.state_abbrev
AND cnt.state_code = cty.state_code
AND cnt.county_code = cty.county_code
AND cnt2.state_code = sta2.state_code
AND nvl(loc.loc_information18,loc.town_or_city) = cty2.city_name
AND nvl(loc.loc_information19,loc.region_1) = cnt2.county_name
AND nvl(loc.loc_information17,loc.region_2) = sta2.state_abbrev
AND cnt2.state_code = cty2.state_code
AND cnt2.county_code = cty2.county_code;
select sta.state_code, cnt.county_code, cty.city_code,
sta2.state_code, cnt2.county_code, cty2.city_code
from per_addresses adr
,pay_us_states sta
,pay_us_counties cnt
,pay_us_city_names cty
,pay_us_states sta2
,pay_us_counties cnt2
,pay_us_city_names cty2
where adr.person_id = p_person_id
and adr.primary_flag = 'Y'
and p_effective_date between adr.date_from
and nvl(adr.date_to, hr_api.g_eot)
AND CNT.STATE_CODE = STA.STATE_CODE
AND ADR.TOWN_OR_CITY = CTY.CITY_NAME
AND ADR.REGION_1 = CNT.COUNTY_NAME
AND ADR.REGION_2 = STA.STATE_ABBREV
AND CNT.STATE_CODE = CTY.STATE_CODE
AND CNT.COUNTY_CODE = CTY.COUNTY_CODE
AND CNT2.STATE_CODE = STA2.STATE_CODE
AND nvl(adr.add_information18,ADR.TOWN_OR_CITY) = CTY2.CITY_NAME
AND nvl(adr.add_information19,ADR.REGION_1) = CNT2.COUNTY_NAME
AND nvl(adr.add_information17,ADR.REGION_2) = STA2.STATE_ABBREV
AND CNT2.STATE_CODE = CTY2.STATE_CODE
AND CNT2.COUNTY_CODE = CTY2.COUNTY_CODE;
select sta.state_code, cnt.county_code, cty.city_code,
sta2.state_code, cnt2.county_code, cty2.city_code
from per_addresses adr
,pay_us_states sta
,pay_us_counties cnt
,pay_us_city_names cty
,pay_us_states sta2
,pay_us_counties cnt2
,pay_us_city_names cty2
where adr.person_id = p_person_id
and adr.primary_flag = 'Y'
AND CNT.STATE_CODE = STA.STATE_CODE
AND ADR.TOWN_OR_CITY = CTY.CITY_NAME
AND ADR.REGION_1 = CNT.COUNTY_NAME
AND ADR.REGION_2 = STA.STATE_ABBREV
AND CNT.STATE_CODE = CTY.STATE_CODE
AND CNT.COUNTY_CODE = CTY.COUNTY_CODE
AND CNT2.STATE_CODE = STA2.STATE_CODE
AND nvl(adr.add_information18,ADR.TOWN_OR_CITY) = CTY2.CITY_NAME
AND nvl(adr.add_information19,ADR.REGION_1) = CNT2.COUNTY_NAME
AND nvl(adr.add_information17,ADR.REGION_2) = STA2.STATE_ABBREV
AND CNT2.STATE_CODE = CTY2.STATE_CODE
AND CNT2.COUNTY_CODE = CTY2.COUNTY_CODE;
select null
from pay_us_emp_state_tax_rules_f sta
where sta.assignment_id = p_assignment_id
and sta.state_code = p_state_code
and sta.effective_start_date > l_csr_date;
select null
from pay_us_emp_county_tax_rules_f cnt
where cnt.assignment_id = p_assignment_id
and cnt.state_code = p_state_code
and cnt.county_code = p_county_code
and cnt.effective_start_date > l_csr_date;
select null
from pay_us_emp_city_tax_rules_f cty
where cty.assignment_id = p_assignment_id
and cty.state_code = p_state_code
and cty.county_code = p_county_code
and cty.city_code = p_city_code
and cty.effective_start_date > l_csr_date;
select null
from per_assignments_f asg, per_addresses adr
where asg.assignment_id = p_asg_id
and p_ef_date between asg.effective_start_date
and asg.effective_end_date
and asg.pay_basis_id is not null
and asg.payroll_id is not null
and asg.location_id is not null
and asg.soft_coding_keyflex_id is not null
and asg.assignment_type = 'E'
and asg.person_id = adr.person_id
and p_ef_date between adr.date_from
and nvl(adr.date_to, hr_api.g_eot)
and adr.primary_flag = 'Y'
and rownum = 1
and exists (select null
from hr_soft_coding_keyflex sck
where sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and sck.segment1 is not null);
select min(effective_start_date)
from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id;
select location_id,
effective_start_date,
effective_end_date
from per_assignments_f
where assignment_id = p_assignment_id
and effective_start_date >= p_def_date
order by effective_start_date;
select peef.effective_start_date,
peef.effective_end_date,
peevf.screen_entry_value
from pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_input_values_f pivf,
pay_element_types_f petf,
pay_element_links_f pelf
where peef.assignment_id = p_assignment_id
and peef.creator_type = 'UT'
and petf.element_name = 'VERTEX'
and peevf.screen_entry_value like p_subordinate_jurisdiction||'%'
and peevf.screen_entry_value not like '%'||p_exclude_jurisdiction||'%'
and upper(pivf.name)='JURISDICTION'
and peevf.input_value_id = pivf.input_value_id
and peef.element_entry_id = peevf.element_entry_id
and p_effective_date between peef.effective_start_date and
peef.effective_end_date
and peef.effective_start_date = peevf.effective_start_date
and peef.effective_end_date = peevf.effective_end_date
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
order by peef.assignment_id asc,
petf.element_name asc,
peef.element_entry_id asc,
peef.effective_start_date asc,
peevf.element_entry_value_id asc;
select petf.element_type_id,
pivf.input_value_id,
pivf.name
from PAY_ELEMENT_TYPES_F petf,
PAY_INPUT_VALUES_F pivf
where petf.element_name = 'VERTEX'
and p_effective_date between petf.effective_start_date
and petf.effective_end_date
and petf.element_type_id = pivf.element_type_id
and p_effective_date between pivf.effective_start_date
and pivf.effective_end_date;
select peef.element_entry_id
from PAY_ELEMENT_ENTRIES_F peef,
PAY_ELEMENT_ENTRY_VALUES_F pevf
where peef.assignment_id = p_assignment_id
and p_effective_date between peef.effective_start_date
and peef.effective_end_date
and peef.element_link_id = p_element_link
and pevf.element_entry_id = peef.element_entry_id
and p_effective_date between pevf.effective_start_date
and pevf.effective_end_date
and pevf.input_value_id = p_input_val
and pevf.screen_entry_value like p_jurisdiction;
select peef.element_entry_id, peef.effective_start_date
from PAY_ELEMENT_ENTRIES_F peef,
PAY_ELEMENT_ENTRY_VALUES_F pevf
where peef.assignment_id = p_assignment_id
and peef.element_link_id = p_element_link
and pevf.element_entry_id = peef.element_entry_id
and pevf.input_value_id = p_input_val
and pevf.screen_entry_value = p_jurisdiction
and rownum = 1
order by peef.effective_start_date asc;
select peef.element_entry_id, peef.effective_start_date,
peef.effective_end_date
from PAY_ELEMENT_ENTRIES_F peef,
PAY_ELEMENT_ENTRY_VALUES_F pevf
where peef.assignment_id = p_assignment_id
and peef.element_link_id = p_element_link
and pevf.element_entry_id = peef.element_entry_id
and pevf.effective_start_date = peef.effective_start_date
and pevf.effective_end_date = peef.effective_end_date /* bug 9491711 */
and p_effective_date between pevf.effective_start_date
and pevf.effective_end_date
and pevf.input_value_id = p_input_val
and pevf.screen_entry_value =
p_state_code ||'-'|| p_county_code ||'-'|| p_city_code
order by peef.effective_start_date;
select screen_entry_value
from PAY_ELEMENT_ENTRY_VALUES_F
where element_entry_id = p_ele_entry_id
and p_effective_date between effective_start_date
and effective_end_date
and input_value_id = p_input_val
and screen_entry_value is not null;
select /*+ ORDERED
INDEX(PAF PER_ASSIGNMENTS_F_PK)
USE_NL(PAF, HL, PUS, PUC, PUCN) */
pus.state_code ||'-'|| puc.county_code ||'-'|| pucn.city_code
from per_assignments_f paf,
hr_locations hl,
pay_us_states pus,
pay_us_counties puc,
pay_us_city_names pucn
where paf.assignment_id = l_assignment_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and hl.location_id = paf.location_id
and nvl(hl.loc_information18, hl.town_or_city) = pucn.city_name
and nvl(hl.loc_information19, hl.region_1) = puc.county_name
and nvl(hl.loc_information17, hl.region_2) = pus.state_abbrev
and pus.state_code = puc.state_code
and puc.state_code = pucn.state_code
and puc.county_code = pucn.county_code;
select peef.element_entry_id
from pay_element_entries_f peef, pay_element_entry_values_f peevf,
pay_element_links_f pelf, pay_element_types_f petf
where peef.assignment_id= p_assignment_id
and petf.element_name='VERTEX'
and peevf.screen_entry_value = p_jurisdiction
and p_effective_date between peef.effective_start_date
and peef.effective_end_date
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and peef.element_entry_id = peevf.element_entry_id
and p_effective_date between peevf.effective_start_date
and peevf.effective_end_date;
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'CORRECTION',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => p_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
If p_datetrack_mode in ('DELETE_NEXT_CHANGE',
'FUTURE_CHANGE',
'DELETE',
'CORRECTION',
'UPDATE',
'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE',
'INSERT_OVERRIDE',
'INSERT_OLD') then
--
hr_utility.set_location(l_proc, 40);
if p_datetrack_mode = 'INSERT_OVERRIDE' then
Open csr_ele_entry_io (
l_element_link_id,
l_input_value_id_tbl(2),
l_jurisdiction,
p_assignment_id);
if p_datetrack_mode not in ('UPDATE','INSERT_OVERRIDE', 'UPDATE_OVERRIDE') then
open csr_get_curr_percnt(l_element_entry_id,
l_input_value_id_tbl(3),
p_effective_date);
for l_insert_old_recs in csr_vertex_in_jurisdiction
(
l_element_link_id,
l_input_value_id_tbl(2)
) loop
l_inc := l_inc + 1;
:= l_insert_old_recs.element_entry_id;
:= l_insert_old_recs.effective_start_date;
:= l_insert_old_recs.effective_end_date;
if l_insert_old_recs.effective_start_date < p_effective_date then
l_io := TRUE;
if p_effective_date >= l_insert_old_recs.effective_start_date
and p_effective_date <= l_insert_old_recs.effective_end_date then
l_pct_inc := l_inc;
If p_datetrack_mode = 'INSERT_OLD' then
--
hr_utility.set_location(l_proc, 70);
for l_insert_old_recs in csr_vertex_in_jurisdiction
(
l_element_link_id,
l_input_value_id_tbl(2)
) loop
l_inc := l_inc + 1;
:= l_insert_old_recs.element_entry_id;
:= l_insert_old_recs.effective_start_date;
:= l_insert_old_recs.effective_end_date;
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
elsif p_datetrack_mode = 'INSERT_OVERRIDE' then
--
hr_utility.set_location(l_proc, 85);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl);
Delete from pay_element_entry_values_f
Where element_entry_id = l_element_entry_id
and effective_start_date < p_effective_date;
Delete from pay_element_entries_f
Where element_entry_id = l_element_entry_id
and effective_start_date < p_effective_date;
Update pay_element_entry_values_f
Set effective_start_date = p_effective_date
Where element_entry_id = l_element_entry_id
and effective_start_date = t_effective_start_date;
Update pay_element_entries_f
Set effective_start_date = p_effective_date
Where element_entry_id = l_element_entry_id
and effective_start_date = t_effective_start_date;
calling hr_entry_element.update_element_entry with MODE
= 'UPDATE' at the start date for each location change.
c. correct the percentage for the current jurisdiction record to
0% for assignment jurisdiction not equal to the current
jurisdiction and 100% where the assignment jurisdiction and
current jurisdiction are equal and the current jurisdiction
is a city.
*/
elsif p_datetrack_mode = 'INSERT' then
--
hr_utility.set_location(l_proc ,110);
hr_entry_api.insert_element_entry
(
p_effective_start_date => l_defaulting_date,
p_effective_end_date => l_effective_end_date,
p_element_entry_id => l_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => l_element_link_id,
p_creator_type => 'UT',
p_entry_type => 'E',
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE',
p_session_date => l_location_chg_tbl(l_inc).start_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'CORRECTION',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry with a mode of 'UPDATE' and
a session date = the curr ent assignments effective end date + 1.
d. else if the current assignment location record's effective end
date < the currect assignments' element entry record's effective end
date, then call hr_entry_api.update_element_entry with a mode of
'UPDATE_INSERT' and a session date = the current assignments
effective end date + 1.
e. set the percentage value to the new percentage.
f. if the current percentage record's effective start date < the
current assignment location record's effective start date then call
hr_entry_api.update_element_entry with a mode of 'UPDATE_CHANGE_INSERT'
and at the assignments effective start date + 1.
g. else if not modified then find the current assignment's defaulting
date.
h. check to see if the value of the assignment's location for the next
record. if it is the same as the current assignment record's
location, then call hr_entry_api.delete_element_entry with a mode of
'DELETE_NEXT_CHANGE' at the effective date.
g. check the value of the location for the assignment's prior record, if
it is the same as the location for the assignment's current record,
call hr_entry_api.delete_element_entry with a mode of
'DELETE_NEXT_CHANGE' at the effective end date of the assignment's
next record.
h. if this is a call from the public api correct percentage, calculate
the jurisdiction's new element entry value.
i. else call hr_entry_api.update_element_entry with a mode of 'CORRECTION'
at the effective date.
*/
elsif p_datetrack_mode = 'CORRECTION' then
--
hr_utility.set_location(l_proc ,140);
for l_insert_old_recs in csr_vertex_in_jurisdiction
(l_element_link_id,
l_input_value_id_tbl(2)) loop
l_inc := l_inc + 1;
:= l_insert_old_recs.element_entry_id;
:= l_insert_old_recs.effective_start_date;
:= l_insert_old_recs.effective_end_date;
if l_insert_old_recs.effective_end_date >= p_effective_date and
l_insert_old_recs.effective_start_date <= p_effective_date then
l_pct_inc := l_inc;
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE',
p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => l_location_chg_tbl(l_asg_inc).start_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.delete_element_entry
(
p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id
);
hr_entry_api.delete_element_entry
(
p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
p_session_date => l_element_rec_tbl(l_pct_inc).effective_start_date - 1,
p_element_entry_id => l_element_entry_id
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'CORRECTION',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
record doesn't exist then call hr_entry_api.update_element_entry with
a mode of 'UPDATE' at the the assignment record's effective end
date + 1.
b. else if the current assignment location record's effective end date
< the current percentage record's effective end date then call
hr_entry_api.update_element_entry with a mode of 'UPDATE_CHANGE_INSERT'
at the next assignment record's effective end date.
c. call hr_entry_api.update_element_entry with a mode of
'UPDATE_CHANGE_INSERT' at the effective date and with the new percentage
value.
d. if the assignment's next record's location is the same as the current
assignment record's location then call hr_entry_api.delete_element_entry
with a mode of 'DELETE_NEXT_CHANGE' at the effective date.
*/
elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
--
hr_utility.set_location(l_proc ,180);
for l_insert_old_recs in csr_vertex_in_jurisdiction
(l_element_link_id,
l_input_value_id_tbl(2)) loop
l_inc := l_inc + 1;
:= l_insert_old_recs.element_entry_id;
:= l_insert_old_recs.effective_start_date;
:= l_insert_old_recs.effective_end_date;
if l_insert_old_recs.effective_end_date >= p_effective_date and
l_insert_old_recs.effective_start_date <= p_effective_date then
l_pct_inc := l_inc;
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE',
p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => p_datetrack_mode,
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.delete_element_entry
(
p_dt_delete_mode => 'DELETE_NEXT_CHANGE',
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id
);
a. call hr_entry_api.update_element_entry with a mode of p_datetrack_mode
at the effective date.
*/
elsif p_datetrack_mode in ('UPDATE_OVERRIDE', 'UPDATE') then
hr_utility.set_location(l_proc ,205);
hr_entry_api.update_element_entry
(
p_dt_update_mode => p_datetrack_mode,
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
elsif p_datetrack_mode in ('ZAP', 'DELETE', 'FUTURE_CHANGE') then
--
hr_utility.set_location(l_proc ,210);
hr_entry_api.delete_element_entry
(
p_dt_delete_mode => p_datetrack_mode,
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id
);
elsif p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
--
hr_utility.set_location(l_proc ,215);
for l_insert_old_recs in csr_vertex_in_jurisdiction
(
l_element_link_id,
l_input_value_id_tbl(2)
) loop
l_inc := l_inc + 1;
:= l_insert_old_recs.element_entry_id;
:= l_insert_old_recs.effective_start_date;
:= l_insert_old_recs.effective_end_date;
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE',
p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.update_element_entry
(
p_dt_update_mode => 'UPDATE_CHANGE_INSERT',
p_session_date => l_location_chg_tbl(l_asg_inc).end_date + 1,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_input_value_id_tbl,
p_entry_value_tbl => l_new_vertex_value_tbl
);
hr_entry_api.delete_element_entry
(
p_dt_delete_mode => p_datetrack_mode,
p_session_date => p_effective_date,
p_element_entry_id => l_element_entry_id
);
l_delete_flag varchar2(1);
select fed.assignment_id,
fed.sui_jurisdiction_code
from pay_us_emp_fed_tax_rules_f fed
where fed.emp_fed_tax_rule_id = p_emp_fed_tax_rule_id
and l_get_old_value_date between fed.effective_start_date
and fed.effective_end_date;
select null
from pay_us_emp_fed_tax_rules_f fed
where fed.emp_fed_tax_rule_id = p_emp_fed_tax_rule_id
and fed.effective_start_date = l_effective_end_date + 1;
select min(effective_start_date)
from pay_us_emp_fed_tax_rules_f
where emp_fed_tax_rule_id = p_emp_fed_tax_rule_id;
select pet.element_type_id,
piv.input_value_id,
piv.name
from PAY_ELEMENT_TYPES_F pet,
PAY_INPUT_VALUES_F piv
where pet.element_name = lc_workers_comp
and l_get_old_value_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_type_id = piv.element_type_id
and l_get_old_value_date between piv.effective_start_date
and piv.effective_end_date;
select pee.element_entry_id
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = l_assignment_id
and pee.element_link_id = p_element_link
and rownum < 2;
select min(pee.effective_start_date)
from PAY_ELEMENT_ENTRIES_F pee
where pee.element_entry_id = l_element_entry_id;
select pev.screen_entry_value
from pay_element_entry_values_f pev
where pev.element_entry_id = p_csr_ele_entry_id
and l_get_old_value_date between pev.effective_start_date
and pev.effective_end_date
and pev.input_value_id = p_csr_inp_val
and pev.screen_entry_value is not null;
select peef.element_entry_id
from pay_element_entries_f peef
,pay_element_entry_values_f peevf
,pay_element_links_f pelf
,pay_element_types_f petf
where peef.assignment_id= l_csr_assignment_id
and petf.element_name = lc_workers_comp
and l_csr_effective_date < peef.effective_end_date
and peef.element_link_id = pelf.element_link_id
and pelf.element_type_id = petf.element_type_id
and peef.element_entry_id = peevf.element_entry_id
and l_csr_effective_date < peevf.effective_end_date;
if p_datetrack_mode = 'INSERT_OVERRIDE' then
open csr_min_fed_tax_date;
if p_datetrack_mode in('ZAP','DELETE') then
if p_datetrack_mode = hr_api.g_zap then
l_tmp_date := hr_api.g_date;
hr_entry_api.delete_element_entry(
p_dt_delete_mode => p_datetrack_mode,
p_session_date => l_effective_date,
p_element_entry_id => l_element_entry_id);
else -- p_datetrack_mode not in ('ZAP','DELETE')
--
-- Get element_type_id and input values for the workers comp element.
--
open csr_wc_tax_element;
if p_datetrack_mode = 'INSERT' then
/* Insert the worker's compensation element entry */
hr_utility.set_location('Entering:'|| l_proc, 50);
hr_entry_api.insert_element_entry(
P_effective_start_date => l_effective_start_date,
P_effective_end_date => l_effective_end_date,
P_element_entry_id => l_element_entry_id,
P_assignment_id => l_assignment_id,
P_element_link_id => l_element_link_id,
P_creator_type => 'UT',
P_entry_type => 'E',
P_num_entry_values => 2,
P_input_value_id_tbl => l_inp_value_id_table,
P_entry_value_tbl => l_scr_value_table);
elsif p_datetrack_mode in ('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE', 'DELETE_NEXT_CHANGE',
'FUTURE_CHANGE', 'INSERT_OVERRIDE', 'INSERT_OLD') then
/* Get the worker's compensation element entry id */
open csr_wc_ele_entry(l_element_link_id);
if p_datetrack_mode in('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
l_delete_flag := 'N';
l_delete_flag := 'Y';
if p_datetrack_mode in('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE')
and l_delete_flag = 'Y' then
/* All of the tax %age records will be created from the date on which the
default tax rules criteria was met till the end of time. So, we should
get records for the state, county and city for the same effective start
date */
hr_entry_api.delete_element_entry(
p_dt_delete_mode => l_mode,
p_session_date => l_effective_date,
p_element_entry_id => l_element_entry_id);
elsif p_datetrack_mode in ('CORRECTION','UPDATE', 'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE') then
hr_entry_api.update_element_entry(
p_dt_update_mode => l_mode,
p_session_date => l_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 2,
p_input_value_id_tbl => l_inp_value_id_table,
p_entry_value_tbl => l_scr_value_table);
elsif p_datetrack_mode in ('INSERT_OVERRIDE') then
open csr_wc_min_start;
l_mode := 'UPDATE';
l_mode := 'UPDATE_CHANGE_INSERT';
hr_entry_api.update_element_entry(
p_dt_update_mode => l_mode,
p_session_date => l_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 2,
p_input_value_id_tbl => l_inp_value_id_table,
p_entry_value_tbl => l_scr_value_table);
delete from pay_element_entry_values_f pev
where pev.element_entry_id = l_element_entry_id
and pev.effective_start_date < l_effective_date;
delete from pay_element_entries_f pee
where pee.element_entry_id = l_element_entry_id
and pee.effective_start_date < l_effective_date;
update pay_element_entry_values_f pev
set pev.effective_start_date = l_effective_date
where pev.element_entry_id = l_element_entry_id
and pev.effective_start_date = l_wc_min_start_date;
update pay_element_entries_f pee
set pee.effective_start_date = l_effective_date
where pee.element_entry_id = l_element_entry_id
and pee.effective_start_date = l_wc_min_start_date;
elsif p_datetrack_mode in ('INSERT_OLD') then
open csr_get_curr_jurisd(l_element_entry_id, l_inp_value_id_table(2));
l_mode := 'UPDATE';
l_mode := 'UPDATE_CHANGE_INSERT';
hr_entry_api.update_element_entry(
p_dt_update_mode => l_mode,
p_session_date => l_effective_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 2,
p_input_value_id_tbl => l_inp_value_id_table,
p_entry_value_tbl => l_scr_value_table);
procedure delete_fed_tax_rule
(p_effective_date in date
,p_datetrack_delete_mode in varchar2
,p_assignment_id in number
,p_delete_routine in varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number out nocopy number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_fed_tax_rule';
select fed.emp_fed_tax_rule_id, fed.object_version_number
from pay_us_emp_fed_tax_rules_f fed
where fed.assignment_id = p_assignment_id
and l_effective_date between fed.effective_start_date
and fed.effective_end_date;
select sta.state_code, sta.object_version_number
from pay_us_emp_state_tax_rules_f sta
where sta.assignment_id = p_assignment_id
and l_effective_date between sta.effective_start_date
and sta.effective_end_date;
if p_datetrack_delete_mode NOT IN ('ZAP', 'DELETE') then
hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
if nvl(p_delete_routine,'X') <> 'ASSIGNMENT' then
hr_utility.set_message(801, 'HR_6674_PAY_ASSIGN');
pay_us_tax_api.delete_tax_rule(
p_validate => NULL
,p_effective_date => l_effective_date
,p_assignment_id => p_assignment_id
,p_state_code => l_state_code
,p_county_code => '000'
,p_city_code => '0000'
,p_datetrack_mode => p_datetrack_delete_mode
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_object_version_number => l_tmp_object_version_number
,p_delete_routine => p_delete_routine
);
,p_datetrack_mode => p_datetrack_delete_mode
);
,p_datetrack_mode => p_datetrack_delete_mode
,p_delete_routine => p_delete_routine
);
end delete_fed_tax_rule;
select null
from per_people_f peo
where peo.person_id = p_person_id;
select asg.person_id
from per_assignments_f asg
where asg.assignment_id = p_assignment_id;
select asg.assignment_id
from per_assignments_f asg
where asg.person_id = p_person_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select final_process_date
from per_periods_of_service pos
where pos.person_id = p_person_id
and pos.date_start =
(select max(date_start)
from per_periods_of_service pos2
where pos2.person_id = pos.person_id
and date_start <= p_effective_date);
select asg.business_group_id
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select min(asg.effective_start_date)
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.pay_basis_id is not null
and asg.payroll_id is not null
and asg.soft_coding_keyflex_id is not null
and asg.location_id is not null
and asg.assignment_type = 'E'
and exists (select null
from hr_soft_coding_keyflex sck
where sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and sck.segment1 is not null);
select min(adr.date_from)
from per_addresses adr
,per_assignments_f asg
where asg.assignment_id = p_assignment_id
and adr.person_id = asg.person_id
and adr.primary_flag = 'Y';
select location_id
from per_assignments_f
where assignment_id = p_assignment_id
and effective_end_date > l_effective_date
order by effective_end_date;
select location_id,person_id
from per_assignments_f
where assignment_id = p_assignment_id
and l_defaulting_date between effective_start_date and effective_end_date;
select lookup_code
into l_eic_filing_status_code
from fnd_common_lookups
where lookup_type ='US_EIC_FILING_STATUS'
and Meaning='No EIC';
,p_datetrack_mode => 'INSERT'
);
,p_delete_routine in varchar2 default null
) is
TYPE assign_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
select null
from per_assignments_f asg,
hr_organization_information bus
where asg.assignment_id = p_csr_assignment_id
and bus.organization_id = asg.business_group_id
and bus.org_information9 = 'US'
and bus.org_information_context = 'Business Group Information'
and p_effective_date between asg.effective_start_date
and asg.effective_end_date ;
select person_id, primary_flag, style -- #2858888
from per_addresses adr
where adr.address_id = p_adr_id;
select asg.assignment_id
from per_assignments_f asg,
per_addresses adr
where asg.person_id = adr.person_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and adr.address_id = p_adr_id;
/*Bug8285850 Cursor to check if the current update or correction statement
is removing payroll information from the assignment.This will
return records only when we have Vertex or Workers compensation links setup
as link to all payrolls and trying to remove payroll after defaulting*/
cursor csr_defaultpayrollremoved(p_assignment_id number,p_effective_date date) is
select null
from per_all_assignments_f paa,
pay_element_links_f pel,
pay_element_types_f pet
where paa.assignment_id=p_assignment_id
and paa.payroll_id is null
and p_effective_date between paa.effective_start_date
and paa.effective_end_date
and paa.business_group_id=pel.business_group_id
and pel.link_to_all_payrolls_flag = 'Y'
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 pet.element_name in ('VERTEX','Workers Compensation');
/*Bug8285850 Cursor to get the details of the assignment that is being updated */
cursor csr_asgmt_details(p_assignment_id number,p_effective_date date) is
select payroll_id,
effective_start_date,
effective_end_date
from per_all_assignments_f
where assignment_id=p_assignment_id
and p_effective_date between effective_start_date
and effective_end_date;
'DELETE',
'UPDATE',
'CORRECTION',
'UPDATE_OVERRIDE',
'UPDATE_CHANGE_INSERT') then
hr_utility.set_message(801, 'HR_7204_DT_DEL_MODE_INVALID');
elsif p_datetrack_mode in ('ZAP', 'DELETE') then
hr_utility.set_location(l_proc, 40);
delete_fed_tax_rule(
p_effective_date => l_effective_date
,p_datetrack_delete_mode => p_datetrack_mode
,p_assignment_id => l_assignment_tbl(l_cnt)
,p_delete_routine => p_delete_routine
,p_effective_start_date => l_fed_eff_start_date
,p_effective_end_date => l_fed_eff_end_date
,p_object_version_number => l_fed_object_version_number
);
to check if the current update or correction operation is trying
to remove the payroll information after the defaulting for a setup
with vertex or Workers compensation link set as Link to All Payrolls.*/
if csr_defaulting_met%NOTFOUND then
open csr_defaultpayrollremoved(p_assignment_id,p_effective_date);
elsif p_datetrack_mode='UPDATE' then
hrentmnt.check_payroll_changes_asg
(p_assignment_id,l_payroll_id,p_datetrack_mode,
l_effective_date,l_effective_end_date);
if p_datetrack_mode = 'UPDATE_OVERRIDE' THEN
l_location_id := per_us_extra_assignment_rules.g_old_assgt_location;
select min(effective_start_date), max(effective_end_date)
from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id;
select asg.location_id, asg.effective_start_date, asg.effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select null
from hr_locations loc
where loc.location_id = p_location_id;
select emp_fed_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,sui_state_code
,sui_jurisdiction_code
,business_group_id
,additional_wa_amount
,filing_status_code
,fit_override_amount
,fit_override_rate
,withholding_allowances
,cumulative_taxation
,eic_filing_status_code
,fit_additional_tax
,fit_exempt
,futa_tax_exempt
,medicare_tax_exempt
,ss_tax_exempt
,wage_exempt
,statutory_employee
,w2_filed_year
,supp_tax_override_rate
,excessive_wa_reject_date
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,fed_information_category
,fed_information1
,fed_information2
,fed_information3
,fed_information4
,fed_information5
,fed_information6
,fed_information7
,fed_information8
,fed_information9
,fed_information10
,fed_information11
,fed_information12
,fed_information13
,fed_information14
,fed_information15
,fed_information16
,fed_information17
,fed_information18
,fed_information19
,fed_information20
,fed_information21
,fed_information22
,fed_information23
,fed_information24
,fed_information25
,fed_information26
,fed_information27
,fed_information28
,fed_information29
,fed_information30
from pay_us_emp_fed_tax_rules_f
where assignment_id = l_csr_assignment_id
and effective_end_date >= l_csr_start_date
and effective_start_date <= l_csr_end_date;
select emp_fed_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,sui_state_code
,sui_jurisdiction_code
,business_group_id
,additional_wa_amount
,filing_status_code
,fit_override_amount
,fit_override_rate
,withholding_allowances
,cumulative_taxation
,eic_filing_status_code
,fit_additional_tax
,fit_exempt
,futa_tax_exempt
,medicare_tax_exempt
,ss_tax_exempt
,wage_exempt
,statutory_employee
,w2_filed_year
,supp_tax_override_rate
,excessive_wa_reject_date
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,fed_information_category
,fed_information1
,fed_information2
,fed_information3
,fed_information4
,fed_information5
,fed_information6
,fed_information7
,fed_information8
,fed_information9
,fed_information10
,fed_information11
,fed_information12
,fed_information13
,fed_information14
,fed_information15
,fed_information16
,fed_information17
,fed_information18
,fed_information19
,fed_information20
,fed_information21
,fed_information22
,fed_information23
,fed_information24
,fed_information25
,fed_information26
,fed_information27
,fed_information28
,fed_information29
,fed_information30
from pay_us_emp_fed_tax_rules_f
where assignment_id = l_csr_assignment_id
and effective_start_date between l_csr_start_date and l_csr_end_date;
select emp_state_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,state_code
,jurisdiction_code
,business_group_id
,additional_wa_amount
,filing_status_code
,remainder_percent
,secondary_wa
,sit_additional_tax
,sit_override_amount
,sit_override_rate
,withholding_allowances
,excessive_wa_reject_date
,sdi_exempt
,sit_exempt
,sit_optional_calc_ind
,state_non_resident_cert
,sui_exempt
,wc_exempt
,wage_exempt
,sui_wage_base_override_amount
,supp_tax_override_rate
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,sta_information_category
,sta_information1
,sta_information2
,sta_information3
,sta_information4
,sta_information5
,sta_information6
,sta_information7
,sta_information8
,sta_information9
,sta_information10
,sta_information11
,sta_information12
,sta_information13
,sta_information14
,sta_information15
,sta_information16
,sta_information17
,sta_information18
,sta_information19
,sta_information20
,sta_information21
,sta_information22
,sta_information23
,sta_information24
,sta_information25
,sta_information26
,sta_information27
,sta_information28
,sta_information29
,sta_information30
from pay_us_emp_state_tax_rules_f
where assignment_id = l_csr_assignment_id
and effective_end_date >= l_csr_start_date
and effective_start_date <= l_csr_end_date;
select emp_county_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,state_code
,county_code
,business_group_id
,additional_wa_rate
,filing_status_code
,jurisdiction_code
,lit_additional_tax
,lit_override_amount
,lit_override_rate
,withholding_allowances
,lit_exempt
,sd_exempt
,ht_exempt
,wage_exempt
,school_district_code
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,cnt_information_category
,cnt_information1
,cnt_information2
,cnt_information3
,cnt_information4
,cnt_information5
,cnt_information6
,cnt_information7
,cnt_information8
,cnt_information9
,cnt_information10
,cnt_information11
,cnt_information12
,cnt_information13
,cnt_information14
,cnt_information15
,cnt_information16
,cnt_information17
,cnt_information18
,cnt_information19
,cnt_information20
,cnt_information21
,cnt_information22
,cnt_information23
,cnt_information24
,cnt_information25
,cnt_information26
,cnt_information27
,cnt_information28
,cnt_information29
,cnt_information30
from pay_us_emp_county_tax_rules_f
where assignment_id = l_csr_assignment_id
and effective_end_date >= l_csr_start_date
and effective_start_date <= l_csr_end_date;
select emp_city_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,state_code
,county_code
,city_code
,business_group_id
,additional_wa_rate
,filing_status_code
,jurisdiction_code
,lit_additional_tax
,lit_override_amount
,lit_override_rate
,withholding_allowances
,lit_exempt
,sd_exempt
,ht_exempt
,wage_exempt
,school_district_code
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,cty_information_category
,cty_information1
,cty_information2
,cty_information3
,cty_information4
,cty_information5
,cty_information6
,cty_information7
,cty_information8
,cty_information9
,cty_information10
,cty_information11
,cty_information12
,cty_information13
,cty_information14
,cty_information15
,cty_information16
,cty_information17
,cty_information18
,cty_information19
,cty_information20
,cty_information21
,cty_information22
,cty_information23
,cty_information24
,cty_information25
,cty_information26
,cty_information27
,cty_information28
,cty_information29
,cty_information30
from pay_us_emp_city_tax_rules_f
where assignment_id = l_csr_assignment_id
and effective_end_date >= l_csr_start_date
and effective_start_date <= l_csr_end_date;
select sta.state_code
from pay_us_emp_state_tax_rules_f sta
where sta.assignment_id = p_assignment_id;
select cnt.state_code, cnt.county_code
from pay_us_emp_county_tax_rules_f cnt
where cnt.assignment_id = p_assignment_id;
select cty.state_code, cty.county_code, cty.city_code
from pay_us_emp_city_tax_rules_f cty
where cty.assignment_id = p_assignment_id;
select null
from pay_us_emp_fed_tax_rules_f fed
where fed.assignment_id = p_assignment_id
and fed.effective_start_date = l_csr_tmp_date;
if p_datetrack_mode in('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE') and p_location_id is null then
hr_utility.set_message(801, 'HR_7880_PDT_VALUE_NOT_FOUND');
if p_datetrack_mode in ('CORRECTION', 'UPDATE',
'UPDATE_CHANGE_INSERT' ) and
p_location_id = per_us_extra_assignment_rules.g_old_assgt_location then
RAISE l_exit_quietly;
if p_datetrack_mode in('CORRECTION', 'UPDATE',
'UPDATE_CHANGE_INSERT' ) then
open csr_chk_location_id;
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_CHANGE_INSERT';
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_CHANGE_INSERT';
elsif p_datetrack_mode = 'UPDATE' then
--
hr_utility.set_location(l_proc, 60);
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_CHANGE_INSERT';
elsif p_datetrack_mode = 'UPDATE_CHANGE_INSERT' then
--
hr_utility.set_location(l_proc, 70);
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_CHANGE_INSERT';
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_CHANGE_INSERT';
elsif p_datetrack_mode = 'UPDATE_OVERRIDE' then
--
hr_utility.set_location(l_proc, 80);
l_dt_mode := 'UPDATE';
l_dt_mode := 'UPDATE_CHANGE_INSERT';
elsif p_datetrack_mode in ('ZAP', 'DELETE',
'DELETE_NEXT_CHANGE',
'FUTURE_CHANGE') then
--
hr_utility.set_location(l_proc, 110);
/* we check to see if the entry being updated matches the ovrd jurisdiction, which
defaults to the mailing address jurisdiction if not present. If so, set that
percentage at 100 */
if l_loc_ovrd_state_code||l_loc_ovrd_county_code||l_loc_ovrd_city_code =
l_csr_state_code||l_csr_county_code||l_csr_city_code then
l_city_pct := 100;
select null
from per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
where paf.assignment_id = p_assignment_id
and ((paf.effective_start_date <= p_csr_date1
and paf.effective_end_date >= p_csr_date1)
OR (paf.effective_start_date between p_csr_date1 and p_csr_date2 ) )
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('Q','R')
and ppa.date_earned between p_csr_date1 and p_csr_date2
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.assignment_action_id = prr.assignment_action_id ;
select min(effective_start_date), min(effective_end_date),
emp_fed_tax_rule_id
from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id
group by emp_fed_tax_rule_id;
select asg.effective_start_date, asg.effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_csr_asg_id
and p_csr_eff_date between asg.effective_start_date
and asg.effective_end_date;
select null
from per_assignments_f asg
where asg.assignment_id = p_assignment_id;
select location_id
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_date between asg.effective_start_date and asg.effective_end_date;
select null
from pay_us_emp_fed_tax_rules_f fed
where fed.assignment_id = p_assignment_id
and fed.effective_start_date > l_csr_date;
select emp_fed_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,sui_state_code
,sui_jurisdiction_code
,business_group_id
,additional_wa_amount
,filing_status_code
,fit_override_amount
,fit_override_rate
,withholding_allowances
,cumulative_taxation
,eic_filing_status_code
,fit_additional_tax
,fit_exempt
,futa_tax_exempt
,medicare_tax_exempt
,ss_tax_exempt
,wage_exempt
,statutory_employee
,w2_filed_year
,supp_tax_override_rate
,excessive_wa_reject_date
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,fed_information_category
,fed_information1
,fed_information2
,fed_information3
,fed_information4
,fed_information5
,fed_information6
,fed_information7
,fed_information8
,fed_information9
,fed_information10
,fed_information11
,fed_information12
,fed_information13
,fed_information14
,fed_information15
,fed_information16
,fed_information17
,fed_information18
,fed_information19
,fed_information20
,fed_information21
,fed_information22
,fed_information23
,fed_information24
,fed_information25
,fed_information26
,fed_information27
,fed_information28
,fed_information29
,fed_information30
from pay_us_emp_fed_tax_rules_f
where assignment_id = l_csr_assignment_id
and l_csr_effective_date between effective_start_date
and effective_end_date;
select emp_fed_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,sui_state_code
,sui_jurisdiction_code
,business_group_id
,additional_wa_amount
,filing_status_code
,fit_override_amount
,fit_override_rate
,withholding_allowances
,cumulative_taxation
,eic_filing_status_code
,fit_additional_tax
,fit_exempt
,futa_tax_exempt
,medicare_tax_exempt
,ss_tax_exempt
,wage_exempt
,statutory_employee
,w2_filed_year
,supp_tax_override_rate
,excessive_wa_reject_date
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,fed_information_category
,fed_information1
,fed_information2
,fed_information3
,fed_information4
,fed_information5
,fed_information6
,fed_information7
,fed_information8
,fed_information9
,fed_information10
,fed_information11
,fed_information12
,fed_information13
,fed_information14
,fed_information15
,fed_information16
,fed_information17
,fed_information18
,fed_information19
,fed_information20
,fed_information21
,fed_information22
,fed_information23
,fed_information24
,fed_information25
,fed_information26
,fed_information27
,fed_information28
,fed_information29
,fed_information30
from pay_us_emp_fed_tax_rules_f
where assignment_id = l_csr_assignment_id
and l_csr_effective_date <= effective_end_date
order by effective_start_date;
select emp_state_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,state_code
,jurisdiction_code
,business_group_id
,additional_wa_amount
,filing_status_code
,remainder_percent
,secondary_wa
,sit_additional_tax
,sit_override_amount
,sit_override_rate
,withholding_allowances
,excessive_wa_reject_date
,sdi_exempt
,sit_exempt
,sit_optional_calc_ind
,state_non_resident_cert
,sui_exempt
,wc_exempt
,wage_exempt
,sui_wage_base_override_amount
,supp_tax_override_rate
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,sta_information_category
,sta_information1
,sta_information2
,sta_information3
,sta_information4
,sta_information5
,sta_information6
,sta_information7
,sta_information8
,sta_information9
,sta_information10
,sta_information11
,sta_information12
,sta_information13
,sta_information14
,sta_information15
,sta_information16
,sta_information17
,sta_information18
,sta_information19
,sta_information20
,sta_information21
,sta_information22
,sta_information23
,sta_information24
,sta_information25
,sta_information26
,sta_information27
,sta_information28
,sta_information29
,sta_information30
from pay_us_emp_state_tax_rules_f
where assignment_id = l_csr_assignment_id
and l_csr_effective_date between effective_start_date
and effective_end_date;
select emp_county_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,state_code
,county_code
,business_group_id
,additional_wa_rate
,filing_status_code
,jurisdiction_code
,lit_additional_tax
,lit_override_amount
,lit_override_rate
,withholding_allowances
,lit_exempt
,sd_exempt
,ht_exempt
,wage_exempt
,school_district_code
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,cnt_information_category
,cnt_information1
,cnt_information2
,cnt_information3
,cnt_information4
,cnt_information5
,cnt_information6
,cnt_information7
,cnt_information8
,cnt_information9
,cnt_information10
,cnt_information11
,cnt_information12
,cnt_information13
,cnt_information14
,cnt_information15
,cnt_information16
,cnt_information17
,cnt_information18
,cnt_information19
,cnt_information20
,cnt_information21
,cnt_information22
,cnt_information23
,cnt_information24
,cnt_information25
,cnt_information26
,cnt_information27
,cnt_information28
,cnt_information29
,cnt_information30
from pay_us_emp_county_tax_rules_f
where assignment_id = l_csr_assignment_id
and l_csr_effective_date between effective_start_date
and effective_end_date;
select emp_city_tax_rule_id
,effective_start_date
,effective_end_date
,assignment_id
,state_code
,county_code
,city_code
,business_group_id
,additional_wa_rate
,filing_status_code
,jurisdiction_code
,lit_additional_tax
,lit_override_amount
,lit_override_rate
,withholding_allowances
,lit_exempt
,sd_exempt
,ht_exempt
,wage_exempt
,school_district_code
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,cty_information_category
,cty_information1
,cty_information2
,cty_information3
,cty_information4
,cty_information5
,cty_information6
,cty_information7
,cty_information8
,cty_information9
,cty_information10
,cty_information11
,cty_information12
,cty_information13
,cty_information14
,cty_information15
,cty_information16
,cty_information17
,cty_information18
,cty_information19
,cty_information20
,cty_information21
,cty_information22
,cty_information23
,cty_information24
,cty_information25
,cty_information26
,cty_information27
,cty_information28
,cty_information29
,cty_information30
from pay_us_emp_city_tax_rules_f
where assignment_id = l_csr_assignment_id
and l_csr_effective_date between effective_start_date
and effective_end_date;
update pay_us_emp_fed_tax_rules_f
set effective_start_date = p_new_default_date
where assignment_id = p_assignment_id
and effective_start_date = p_defaulting_date;
,p_datetrack_mode => 'INSERT_OVERRIDE'
);
update pay_us_emp_state_tax_rules_f
set effective_start_date = p_new_default_date
where effective_start_date = p_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_state_rec.state_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
update pay_us_emp_county_tax_rules_f
set effective_start_date = p_new_default_date
where effective_start_date = p_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_county_rec.state_code
and county_code = l_county_rec.county_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
update pay_us_emp_city_tax_rules_f
set effective_start_date = p_new_default_date
where effective_start_date = p_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_city_rec.state_code
and county_code = l_city_rec.county_code
and city_code = l_city_rec.city_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
,p_datetrack_mode => 'INSERT_OVERRIDE'
);
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
,p_datetrack_mode => 'UPDATE');
,p_datetrack_mode => 'UPDATE_CHANGE_INSERT');
delete from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id
and effective_start_date < p_new_default_date;
,p_datetrack_mode => 'INSERT_OVERRIDE'
);
,p_datetrack_mode => 'UPDATE');
,p_datetrack_mode => 'UPDATE_CHANGE_INSERT');
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
delete from pay_us_emp_state_tax_rules_f
where assignment_id = p_assignment_id
and effective_start_date < p_new_default_date;
,p_datetrack_mode => 'UPDATE');
,p_datetrack_mode => 'UPDATE_CHANGE_INSERT');
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
delete from pay_us_emp_county_tax_rules_f
where assignment_id = p_assignment_id
and effective_start_date < p_new_default_date;
,p_datetrack_mode => 'UPDATE');
,p_datetrack_mode => 'UPDATE_CHANGE_INSERT');
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_esd
,p_effective_end_date => l_tmp_eed
,p_calculate_pct => FALSE
);
delete from pay_us_emp_city_tax_rules_f
where assignment_id = p_assignment_id
and effective_start_date < p_new_default_date;
if p_datetrack_mode in (hr_api.g_correction,hr_api.g_update_change_insert) then
open csr_asg_loc_id(l_new_default_date);
if p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
if l_defaulting_met then
--
-- Pull back the start date of all tax rules and tax element entries.
--
pull_back_taxes(p_assignment_id => p_assignment_id
,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
,p_fed_eed => l_fed_eed
,p_new_default_date => l_new_default_date
,p_defaulting_date => l_defaulting_date
);
else -- DELETE_NEXT_CHANGE, defaulting not met
--
if l_asg_eed = hr_api.g_eot then
--
-- This delete extends to the end of time, so it removes the taxes.
--
delete_fed_tax_rule(
p_effective_date => l_defaulting_date
,p_datetrack_delete_mode => 'ZAP'
,p_assignment_id => p_assignment_id
,p_delete_routine => 'ASSIGNMENT'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_object_version_number => l_tmp_object_version_number
);
else -- DELETE_NEXT_CHANGE, push defaulting date forward to
-- assignment break
--
push_forward_taxes(p_assignment_id => p_assignment_id
,p_fed_eed => l_fed_eed
,p_new_default_date => l_asg_eed + 1
,p_defaulting_date => l_defaulting_date
);
end if; -- DELETE_NEXT_CHANGE, l_fed_eed at end of time?
end if; -- DELETE_NEXT_CHANGE, defaulting met?
delete_fed_tax_rule(
p_effective_date => l_defaulting_date
,p_datetrack_delete_mode => 'ZAP'
,p_assignment_id => p_assignment_id
,p_delete_routine => 'ASSIGNMENT'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_object_version_number => l_tmp_object_version_number
);
elsif p_datetrack_mode in('CORRECTION', 'UPDATE_CHANGE_INSERT') then
if l_defaulting_met then
if l_new_location_id is not null then
--
-- Pull back start date of federal tax rule and workers comp entry.
--
update pay_us_emp_fed_tax_rules_f
set effective_start_date = l_new_default_date
where assignment_id = p_assignment_id
and effective_start_date = l_defaulting_date;
,p_datetrack_mode => 'INSERT_OVERRIDE'
);
,p_datetrack_mode => 'UPDATE');
,p_datetrack_mode => 'UPDATE_CHANGE_INSERT');
,p_datetrack_mode => 'INSERT_OLD'
);
update pay_us_emp_state_tax_rules_f
set effective_start_date = l_new_default_date
where effective_start_date = l_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_state_rec.state_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
,p_datetrack_mode => 'INSERT_OLD'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
update pay_us_emp_county_tax_rules_f
set effective_start_date = l_new_default_date
where effective_start_date = l_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_county_rec.state_code
and county_code = l_county_rec.county_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
,p_datetrack_mode => 'INSERT_OLD'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
update pay_us_emp_city_tax_rules_f
set effective_start_date = l_new_default_date
where effective_start_date = l_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_city_rec.state_code
and county_code = l_city_rec.county_code
and city_code = l_city_rec.city_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
,p_datetrack_mode => 'INSERT_OLD'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
elsif p_datetrack_mode = 'UPDATE_OVERRIDE' then
--
if l_defaulting_met then
if l_new_location_id is not null then
--
-- Purge the taxes, then create new default records at the new default
-- date for the new location.
--
delete_fed_tax_rule (
p_effective_date => l_defaulting_date
,p_datetrack_delete_mode => 'ZAP'
,p_assignment_id => p_assignment_id
,p_delete_routine => 'ASSIGNMENT'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_object_version_number => l_tmp_object_version_number
);
else -- UPDATE_OVERRIDE, defaulting met, same location
--
-- Pull back the start date of the federal tax rule and the workers
-- comp element entry.
--
update pay_us_emp_fed_tax_rules_f
set effective_start_date = l_new_default_date
where assignment_id = p_assignment_id
and effective_start_date = l_defaulting_date;
,p_datetrack_mode => 'INSERT_OVERRIDE'
);
update pay_us_emp_state_tax_rules_f
set effective_start_date = l_new_default_date
where effective_start_date = l_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_state_rec.state_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
update pay_us_emp_county_tax_rules_f
set effective_start_date = l_new_default_date
where effective_start_date = l_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_county_rec.state_code
and county_code = l_county_rec.county_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
update pay_us_emp_city_tax_rules_f
set effective_start_date = l_new_default_date
where effective_start_date = l_defaulting_date
and assignment_id = p_assignment_id
and state_code = l_city_rec.state_code
and county_code = l_city_rec.county_code
and city_code = l_city_rec.city_code;
,p_datetrack_mode => 'INSERT_OVERRIDE'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_calculate_pct => FALSE
);
end if; -- UPDATE_OVERRIDE, defaulting met, location null?
else -- UPDATE_OVERRIDE, defaulting not met
--
-- Remove all tax records for this assignment.
--
delete_fed_tax_rule (
p_effective_date => l_defaulting_date
,p_datetrack_delete_mode => 'ZAP'
,p_assignment_id => p_assignment_id
,p_delete_routine => 'ASSIGNMENT'
,p_effective_start_date => l_tmp_effective_start_date
,p_effective_end_date => l_tmp_effective_end_date
,p_object_version_number => l_tmp_object_version_number
);
end if; -- UPDATE_OVERRIDE, defaulting_met?