[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : This procedure can be used to create as well as update the vertex
element entry for an assignment. It calls the element entries api
to insert and update the element entry record.
Parameters :
p_assignment_id -> The assignment for which the vertex elemnt entry is to be
created/modified.
p_effective_start_date -> The start date of the element entry.
p_effective_end_date -> The end date of the element entry.
p_session_date -> This will be helpful for the various update modes.
p_jurisdiction_code -> The jurisdiction code for which the elemnt entry is to
created/updated.
p_percentage_time -> Time in the jurisdiction.
p_mode -> If can have the following values :
'INSERT'
'CORRECTION',
'UPDATE',
'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE',
'ZAP'
'INSERT_OLD'
Note : Since the change in location might lead us to scenarios where we might
want to do various kinds of updates, all kinds of update modes have been
added.
*/
procedure maintain_element_entry (p_assignment_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_session_date in date,
p_jurisdiction_code in varchar2,
p_percentage_time in number,
p_mode in varchar2) is
l_inp_value_id_table hr_entry.number_table;
l_delete_flag varchar2(1) := 'N';
select pet.element_type_id,
piv.input_value_id,
piv.name
from PAY_INPUT_VALUES_F piv,
PAY_ELEMENT_TYPES_F pet
where p_session_date between piv.effective_start_date
and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_name = 'VERTEX';
select pee.element_entry_id
from PAY_ELEMENT_ENTRY_VALUES_F pev,
PAY_ELEMENT_ENTRIES_F pee
where pev.screen_entry_value = p_jurisdiction_code
and pev.input_value_id + 0 = p_inp_val
and p_session_date between pev.effective_start_date
and pev.effective_end_date
and pev.element_entry_id = pee.element_entry_id
and p_session_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link
and pee.assignment_id = p_assignment_id;
select pev.screen_entry_value
from PAY_ELEMENT_ENTRY_VALUES_F pev
where pev.screen_entry_value is not null
and pev.input_value_id + 0 = p_inp_val
and p_session_date between pev.effective_start_date
and pev.effective_end_date
and pev.element_entry_id = p_ele_entry_id;
if p_mode = 'INSERT' then
/* Create the vertex element entry */
hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry' ,5);
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 => 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_inp_value_id_table,
P_entry_value_tbl => l_scr_value_table);
elsif p_mode in ('CORRECTION','UPDATE', 'UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE','ZAP','DELETE_NEXT_CHANGE','FUTURE_CHANGE','INSERT_OLD') then
/* Get the element entry of the vertex element entry that is to be updated
or deleted */
hr_utility.set_location('pay_us_emp_dt_tax_rules.maintain_element_entry' ,7);
/* Added the delete flag for the upgrade. Currently, there
may be state tax records which might not have a vertex
element entry */
if csr_ele_entry%NOTFOUND then
if p_mode in('ZAP','DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
l_delete_flag := 'N';
l_delete_flag := 'Y';
if p_mode = 'INSERT_OLD' then
open csr_get_curr_percnt(l_element_entry_id, l_inp_value_id_table(3));
l_mode := 'UPDATE';
l_mode := 'UPDATE_CHANGE_INSERT';
if p_mode in ('ZAP','DELETE_NEXT_CHANGE','FUTURE_CHANGE')
and l_delete_flag = 'Y' then
hr_entry_api.delete_element_entry(
p_dt_delete_mode => l_mode,
p_session_date => p_session_date,
p_element_entry_id => l_element_entry_id);
elsif p_mode in ('CORRECTION','UPDATE', 'UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE','INSERT_OLD') then
hr_entry_api.update_element_entry(
p_dt_update_mode => l_mode,
p_session_date => p_session_date,
p_element_entry_id => l_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => l_inp_value_id_table,
p_entry_value_tbl => l_scr_value_table);
Purpose : This procedure can be used to create as well as update the worker's
compensation element entry for an assignment. It calls the element
entries api to insert and update the element entry record.
Parameters :
p_assignment_id -> The assignment for which the vertex elemnt entry is to be
created/modified.
p_effective_start_date -> The start date of the element entry.
p_effective_end_date -> The end date of the element entry.
p_session_date -> This will be helpful for changing the wc element
entry for change in the federal record.
p_jurisdiction_code -> The jurisdiction code for which the elemnt entry is to
created/updated.
p_mode -> If can have the following values :
'INSERT'
'CORRECTION',
'UPDATE',
'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE',
'ZAP'
Note : For every change in federal record, we will be changing the worker's comp element entry.
*/
procedure maintain_wc_ele_entry (p_assignment_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_session_date in date,
p_jurisdiction_code in varchar2,
p_mode in varchar2) is
l_inp_value_id_table hr_entry.number_table;
l_delete_flag varchar2(1);
select pet.element_type_id,
piv.input_value_id,
piv.name
from PAY_INPUT_VALUES_F piv,
PAY_ELEMENT_TYPES_F pet
where p_session_date between piv.effective_start_date
and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_name = 'Workers Compensation'; -- Bug 3354060 FTS on PAY_ELEMENT_TYPES_F was removed. Done by removing
select pee.element_entry_id
from PAY_ELEMENT_ENTRIES_F pee
where p_session_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link
and pee.assignment_id = p_assignment_id;
if p_mode = 'INSERT'
then
/* Insert the worker's compensation element entry */
hr_utility.set_location(
'pay_us_emp_dt_tax_rules.maintain_wc_ele_entry' ,5);
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 => p_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_mode in ('CORRECTION', 'UPDATE', 'UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE','ZAP')then
/* Update the worker's compensation element entry */
open csr_wc_ele_entry(l_element_link_id);
if p_mode in('ZAP','DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
l_delete_flag := 'N';
l_delete_flag := 'Y';
if p_mode = 'ZAP' 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 => p_session_date,
p_element_entry_id => l_element_entry_id);
elsif p_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 => p_session_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);
select min(effective_start_date)
from PAY_US_EMP_FED_TAX_RULES_F
where assignment_id = p_assignment_id;
select paf1.location_id,
paf1.effective_start_date,
paf1.effective_start_date - 1
from per_assignments_f paf1
where paf1.assignment_id = passignment
and paf1.effective_start_date >= pdefault_date
order by 2;
l_mode := 'INSERT';
l_mode := 'UPDATE';
p_mode => 'INSERT');
p_mode => 'UPDATE');
p_mode => 'INSERT');
p_mode => 'UPDATE');
/* Name : insert_fed_tax_row
Purpose : To create the federal tax rule record. It also calls the
maintain_wc_ele_entry routine to create the worker's compensation
for the SUI state
*/
procedure insert_fed_tax_row ( p_emp_fed_tax_rule_id in out nocopy number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_sui_state_code in varchar2,
p_sui_jurisdiction_code in varchar2,
p_business_group_id in number,
p_additional_wa_amount in number,
p_filing_status_code in varchar2,
p_fit_override_amount in number,
p_fit_override_rate in number,
p_withholding_allowances in number,
p_cumulative_taxation in varchar2,
p_eic_filing_status_code in varchar2,
p_fit_additional_tax in number,
p_fit_exempt in varchar2,
p_futa_tax_exempt in varchar2,
p_medicare_tax_exempt in varchar2,
p_ss_tax_exempt in varchar2,
p_wage_exempt in varchar2,
p_statutory_employee in varchar2,
p_w2_filed_year in number,
p_supp_tax_override_rate in number,
p_excessive_wa_reject_date in date,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_fed_information_category in varchar2,
p_fed_information1 in varchar2,
p_fed_information2 in varchar2,
p_fed_information3 in varchar2,
p_fed_information4 in varchar2,
p_fed_information5 in varchar2,
p_fed_information6 in varchar2,
p_fed_information7 in varchar2,
p_fed_information8 in varchar2,
p_fed_information9 in varchar2,
p_fed_information10 in varchar2,
p_fed_information11 in varchar2,
p_fed_information12 in varchar2,
p_fed_information13 in varchar2,
p_fed_information14 in varchar2,
p_fed_information15 in varchar2,
p_fed_information16 in varchar2,
p_fed_information17 in varchar2,
p_fed_information18 in varchar2,
p_fed_information19 in varchar2,
p_fed_information20 in varchar2,
p_fed_information21 in varchar2,
p_fed_information22 in varchar2,
p_fed_information23 in varchar2,
p_fed_information24 in varchar2,
p_fed_information25 in varchar2,
p_fed_information26 in varchar2,
p_fed_information27 in varchar2,
p_fed_information28 in varchar2,
p_fed_information29 in varchar2,
p_fed_information30 in varchar2,
p_mode in varchar2) is
l_step number;
select PAY_US_EMP_FED_TAX_RULES_S.nextval
from sys.DUAL;
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
' - Opening cursor', 1);
if p_mode = 'INSERT' then
open csr_fed_tax_rule_id;
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
' - Fetching cursor', 2);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
' - Closing cursor', 3);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
' - inserting row', 4);
if p_mode = 'UPDATE' then
select p_effective_start_date -1
into l_new_date
from DUAL;
/* Update the Federal tax record as of the p_effective_start_date */
l_step := 2;
update PAY_US_EMP_FED_TAX_RULES_F
set effective_end_date = l_new_date
where assignment_id = p_assignment_id
and effective_end_date = p_effective_end_date;
insert into PAY_US_EMP_FED_TAX_RULES_F
(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)
values
(p_emp_fed_tax_rule_id,
p_effective_start_date,
p_effective_end_date,
p_assignment_id,
p_sui_state_code,
p_sui_jurisdiction_code,
p_business_group_id,
p_additional_wa_amount,
lpad(p_filing_status_code,2,'0'),
p_fit_override_amount,
p_fit_override_rate,
p_withholding_allowances,
p_cumulative_taxation,
p_eic_filing_status_code,
p_fit_additional_tax,
p_fit_exempt,
p_futa_tax_exempt,
p_medicare_tax_exempt,
p_ss_tax_exempt,
p_wage_exempt,
p_statutory_employee,
p_w2_filed_year,
p_supp_tax_override_rate,
p_excessive_wa_reject_date,
0,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_attribute21,
p_attribute22,
p_attribute23,
p_attribute24,
p_attribute25,
p_attribute26,
p_attribute27,
p_attribute28,
p_attribute29,
p_attribute30,
p_fed_information_category,
p_fed_information1,
p_fed_information2,
p_fed_information3,
p_fed_information4,
p_fed_information5,
p_fed_information6,
p_fed_information7,
p_fed_information8,
p_fed_information9,
p_fed_information10,
p_fed_information11,
p_fed_information12,
p_fed_information13,
p_fed_information14,
p_fed_information15,
p_fed_information16,
p_fed_information17,
p_fed_information18,
p_fed_information19,
p_fed_information20,
p_fed_information21,
p_fed_information22,
p_fed_information23,
p_fed_information24,
p_fed_information25,
p_fed_information26,
p_fed_information27,
p_fed_information28,
p_fed_information29,
p_fed_information30);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_fed_tax_row'||
' - inserting row', 5);
'pay_us_emp_dt_tax_rules.insert_fed_tax_row - '|| substr(sqlerrm,1,60));
end insert_fed_tax_row;
/* Name : insert_state_tax_row
Purpose : To create the state tax rule record. It also calls the
create_tax_percentage routine to create the %age records
for the state, for every change in location of the assignment
*/
procedure insert_state_tax_row ( p_row_id in out nocopy varchar2,
p_emp_state_tax_rule_id in out nocopy number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_state_code in varchar2,
p_jurisdiction_code in varchar2,
p_business_group_id in number,
p_additional_wa_amount in number,
p_filing_status_code in varchar2,
p_remainder_percent in number,
p_secondary_wa in number,
p_sit_additional_tax in number,
p_sit_override_amount in number,
p_sit_override_rate in number,
p_withholding_allowances in number,
p_excessive_wa_reject_date in date,
p_sdi_exempt in varchar2,
p_sit_exempt in varchar2,
p_sit_optional_calc_ind in varchar2,
p_state_non_resident_cert in varchar2,
p_sui_exempt in varchar2,
p_wc_exempt in varchar2,
p_wage_exempt in varchar2,
p_sui_wage_base_override_amt in number,
p_supp_tax_override_rate in number,
p_time_in_state in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_sta_information_category in varchar2,
p_sta_information1 in varchar2,
p_sta_information2 in varchar2,
p_sta_information3 in varchar2,
p_sta_information4 in varchar2,
p_sta_information5 in varchar2,
p_sta_information6 in varchar2,
p_sta_information7 in varchar2,
p_sta_information8 in varchar2,
p_sta_information9 in varchar2,
p_sta_information10 in varchar2,
p_sta_information11 in varchar2,
p_sta_information12 in varchar2,
p_sta_information13 in varchar2,
p_sta_information14 in varchar2,
p_sta_information15 in varchar2,
p_sta_information16 in varchar2,
p_sta_information17 in varchar2,
p_sta_information18 in varchar2,
p_sta_information19 in varchar2,
p_sta_information20 in varchar2,
p_sta_information21 in varchar2,
p_sta_information22 in varchar2,
p_sta_information23 in varchar2,
p_sta_information24 in varchar2,
p_sta_information25 in varchar2,
p_sta_information26 in varchar2,
p_sta_information27 in varchar2,
p_sta_information28 in varchar2,
p_sta_information29 in varchar2,
p_sta_information30 in varchar2
) is
cursor csr_state_tax_rule_id is
select PAY_US_EMP_STATE_TAX_RULES_S.nextval
from sys.DUAL;
select rowidtochar(rowid)
from PAY_US_EMP_STATE_TAX_RULES_F str
where str.emp_state_tax_rule_id = p_emp_state_tax_rule_id
and str.effective_start_date = p_effective_start_date
and str.effective_end_date = p_effective_end_date;
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;
' - inserting row', 4);
insert into PAY_US_EMP_STATE_TAX_RULES_F
(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)
values
(p_emp_state_tax_rule_id,
p_effective_start_date,
p_effective_end_date,
p_assignment_id,
p_state_code,
p_jurisdiction_code,
p_business_group_id,
p_additional_wa_amount,
lpad(p_filing_status_code,2,'0'),
p_remainder_percent,
p_secondary_wa,
p_sit_additional_tax,
p_sit_override_amount,
p_sit_override_rate,
p_withholding_allowances,
p_excessive_wa_reject_date,
p_sdi_exempt,
p_sit_exempt,
p_sit_optional_calc_ind,
p_state_non_resident_cert,
p_sui_exempt,
p_wc_exempt,
p_wage_exempt,
p_sui_wage_base_override_amt,
p_supp_tax_override_rate,
0,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_attribute21,
p_attribute22,
p_attribute23,
p_attribute24,
p_attribute25,
p_attribute26,
p_attribute27,
p_attribute28,
p_attribute29,
p_attribute30,
p_sta_information_category,
p_sta_information1,
p_sta_information2,
p_sta_information3,
p_sta_information4,
p_sta_information5,
p_sta_information6,
p_sta_information7,
p_sta_information8,
p_sta_information9,
p_sta_information10,
p_sta_information11,
p_sta_information12,
p_sta_information13,
p_sta_information14,
p_sta_information15,
p_sta_information16,
p_sta_information17,
p_sta_information18,
p_sta_information19,
p_sta_information20,
p_sta_information21,
p_sta_information22,
p_sta_information23,
p_sta_information24,
p_sta_information25,
p_sta_information26,
p_sta_information27,
p_sta_information28,
p_sta_information29,
p_sta_information30);
'pay_us_emp_dt_tax_rules.insert_state_tax_row');
/* Insert row into the pay_us_asg_reporting table */
pay_asg_geo_pkg.create_asg_geo_row(P_assignment_id => p_assignment_id,
P_jurisdiction => p_jurisdiction_code,
P_tax_unit_id => NULL );
end insert_state_tax_row;
/* Name : insert_county_tax_row
Purpose : To create the county tax rule record. It also calls the
create_tax_percentage routine to create the %age records
for the county, for every change in location of the assignment
*/
procedure insert_county_tax_row ( p_row_id in out nocopy varchar2,
p_emp_county_tax_rule_id in out nocopy number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2,
p_business_group_id in number,
p_additional_wa_rate in number,
p_filing_status_code in varchar2,
p_jurisdiction_code in varchar2,
p_lit_additional_tax in number,
p_lit_override_amount in number,
p_lit_override_rate in number,
p_withholding_allowances in number,
p_lit_exempt in varchar2,
p_sd_exempt in varchar2,
p_ht_exempt in varchar2,
p_wage_exempt in varchar2,
p_school_district_code in varchar2,
p_time_in_county in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_cnt_information_category in varchar2,
p_cnt_information1 in varchar2,
p_cnt_information2 in varchar2,
p_cnt_information3 in varchar2,
p_cnt_information4 in varchar2,
p_cnt_information5 in varchar2,
p_cnt_information6 in varchar2,
p_cnt_information7 in varchar2,
p_cnt_information8 in varchar2,
p_cnt_information9 in varchar2,
p_cnt_information10 in varchar2,
p_cnt_information11 in varchar2,
p_cnt_information12 in varchar2,
p_cnt_information13 in varchar2,
p_cnt_information14 in varchar2,
p_cnt_information15 in varchar2,
p_cnt_information16 in varchar2,
p_cnt_information17 in varchar2,
p_cnt_information18 in varchar2,
p_cnt_information19 in varchar2,
p_cnt_information20 in varchar2,
p_cnt_information21 in varchar2,
p_cnt_information22 in varchar2,
p_cnt_information23 in varchar2,
p_cnt_information24 in varchar2,
p_cnt_information25 in varchar2,
p_cnt_information26 in varchar2,
p_cnt_information27 in varchar2,
p_cnt_information28 in varchar2,
p_cnt_information29 in varchar2,
p_cnt_information30 in varchar2) is
cursor csr_county_tax_rule_id is
select PAY_US_EMP_COUNTY_TAX_RULES_S.nextval
from sys.DUAL;
select rowidtochar(rowid)
from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
where ctr.emp_county_tax_rule_id = p_emp_county_tax_rule_id
and ctr.effective_start_date = p_effective_start_date
and ctr.effective_end_date = p_effective_end_date;
select null
from pay_us_emp_county_tax_rules_f sta
where sta.assignment_id = p_assignment_id
and sta.state_code = p_state_code
and sta.county_code= p_county_code;
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
' - Opening cursor', 1);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
' - Fetching cursor', 2);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
' - Closing cursor', 3);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
' - inserting row', 4);
insert into pay_us_emp_county_tax_rules_f
(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)
values
(p_emp_county_tax_rule_id,
p_effective_start_date,
p_effective_end_date,
p_assignment_id,
p_state_code,
p_county_code,
p_business_group_id,
p_additional_wa_rate,
lpad(p_filing_status_code,2,'0'),
p_jurisdiction_code,
p_lit_additional_tax,
p_lit_override_amount,
p_lit_override_rate,
p_withholding_allowances,
p_lit_exempt,
p_sd_exempt,
p_ht_exempt,
p_wage_exempt,
p_school_district_code,
0,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_attribute21,
p_attribute22,
p_attribute23,
p_attribute24,
p_attribute25,
p_attribute26,
p_attribute27,
p_attribute28,
p_attribute29,
p_attribute30,
p_cnt_information_category,
p_cnt_information1,
p_cnt_information2,
p_cnt_information3,
p_cnt_information4,
p_cnt_information5,
p_cnt_information6,
p_cnt_information7,
p_cnt_information8,
p_cnt_information9,
p_cnt_information10,
p_cnt_information11,
p_cnt_information12,
p_cnt_information13,
p_cnt_information14,
p_cnt_information15,
p_cnt_information16,
p_cnt_information17,
p_cnt_information18,
p_cnt_information19,
p_cnt_information20,
p_cnt_information21,
p_cnt_information22,
p_cnt_information23,
p_cnt_information24,
p_cnt_information25,
p_cnt_information26,
p_cnt_information27,
p_cnt_information28,
p_cnt_information29,
p_cnt_information30);
'pay_us_emp_dt_tax_rules.insert_county_tax_row');
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_county_tax_row'||
' - creating %age record ', 5);
/* Insert row into the pay_us_asg_reporting table */
pay_asg_geo_pkg.create_asg_geo_row(P_assignment_id => p_assignment_id,
P_jurisdiction => p_jurisdiction_code,
P_tax_unit_id => NULL );
end insert_county_tax_row;
/* Name : insert_city_tax_row
Purpose : To create the city tax rule record. It also calls the
create_tax_percentage routine to create the %age records
for the city, for every change in location of the assignment
*/
procedure insert_city_tax_row ( p_row_id in out nocopy varchar2,
p_emp_city_tax_rule_id in out nocopy number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2,
p_city_code in varchar2,
p_business_group_id in number,
p_additional_wa_rate in number,
p_filing_status_code in varchar2,
p_jurisdiction_code in varchar2,
p_lit_additional_tax in number,
p_lit_override_amount in number,
p_lit_override_rate in number,
p_withholding_allowances in number,
p_lit_exempt in varchar2,
p_sd_exempt in varchar2,
p_ht_exempt in varchar2,
p_wage_exempt in varchar2,
p_school_district_code in varchar2,
p_time_in_city in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_cty_information_category in varchar2,
p_cty_information1 in varchar2,
p_cty_information2 in varchar2,
p_cty_information3 in varchar2,
p_cty_information4 in varchar2,
p_cty_information5 in varchar2,
p_cty_information6 in varchar2,
p_cty_information7 in varchar2,
p_cty_information8 in varchar2,
p_cty_information9 in varchar2,
p_cty_information10 in varchar2,
p_cty_information11 in varchar2,
p_cty_information12 in varchar2,
p_cty_information13 in varchar2,
p_cty_information14 in varchar2,
p_cty_information15 in varchar2,
p_cty_information16 in varchar2,
p_cty_information17 in varchar2,
p_cty_information18 in varchar2,
p_cty_information19 in varchar2,
p_cty_information20 in varchar2,
p_cty_information21 in varchar2,
p_cty_information22 in varchar2,
p_cty_information23 in varchar2,
p_cty_information24 in varchar2,
p_cty_information25 in varchar2,
p_cty_information26 in varchar2,
p_cty_information27 in varchar2,
p_cty_information28 in varchar2,
p_cty_information29 in varchar2,
p_cty_information30 in varchar2) is
cursor csr_city_tax_rule_id is
select PAY_US_EMP_CITY_TAX_RULES_S.nextval
from sys.DUAL;
select rowidtochar(rowid)
from PAY_US_EMP_CITY_TAX_RULES_F ctr
where ctr.emp_city_tax_rule_id = p_emp_city_tax_rule_id
and ctr.effective_start_date = p_effective_start_date
and ctr.effective_end_date = p_effective_end_date;
select null
from pay_us_emp_city_tax_rules_f sta
where sta.assignment_id = p_assignment_id
and sta.state_code = p_state_code
and sta.county_code= p_county_code
and sta.city_code= p_city_code;
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
' - Opening cursor', 1);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
' - Fetching cursor', 2);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
' - Closing cursor', 3);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
' - inserting row', 4);
insert into PAY_US_EMP_CITY_TAX_RULES_F
(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)
values
(p_emp_city_tax_rule_id,
p_effective_start_date,
p_effective_end_date,
p_assignment_id,
p_state_code,
p_county_code,
p_city_code,
p_business_group_id,
p_additional_wa_rate,
lpad(p_filing_status_code,2,'0'),
p_jurisdiction_code,
p_lit_additional_tax,
p_lit_override_amount,
p_lit_override_rate,
p_withholding_allowances,
p_lit_exempt,
p_sd_exempt,
p_ht_exempt,
p_wage_exempt,
p_school_district_code,
0,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_attribute21,
p_attribute22,
p_attribute23,
p_attribute24,
p_attribute25,
p_attribute26,
p_attribute27,
p_attribute28,
p_attribute29,
p_attribute30,
p_cty_information_category,
p_cty_information1,
p_cty_information2,
p_cty_information3,
p_cty_information4,
p_cty_information5,
p_cty_information6,
p_cty_information7,
p_cty_information8,
p_cty_information9,
p_cty_information10,
p_cty_information11,
p_cty_information12,
p_cty_information13,
p_cty_information14,
p_cty_information15,
p_cty_information16,
p_cty_information17,
p_cty_information18,
p_cty_information19,
p_cty_information20,
p_cty_information21,
p_cty_information22,
p_cty_information23,
p_cty_information24,
p_cty_information25,
p_cty_information26,
p_cty_information27,
p_cty_information28,
p_cty_information29,
p_cty_information30);
'pay_us_emp_dt_tax_rules.insert_city_tax_row');
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_city_tax_row'||
' - creating %age record ', 5);
/* Insert row into the pay_us_asg_reporting table */
pay_asg_geo_pkg.create_asg_geo_row(P_assignment_id => p_assignment_id,
P_jurisdiction => p_jurisdiction_code,
P_tax_unit_id => NULL );
end insert_city_tax_row;
/* Name : update_fed_tax_row
Purpose : To update the federal tax rule record. It also calls the
maintain_wc_ele_entry routine to update the worker's compensation
for the SUI state
*/
procedure update_fed_tax_row ( p_row_id in varchar2,
p_emp_fed_tax_rule_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_sui_state_code in varchar2,
p_sui_jurisdiction_code in varchar2,
p_business_group_id in number,
p_additional_wa_amount in number,
p_filing_status_code in varchar2,
p_fit_override_amount in number,
p_fit_override_rate in number,
p_withholding_allowances in number,
p_cumulative_taxation in varchar2,
p_eic_filing_status_code in varchar2,
p_fit_additional_tax in number,
p_fit_exempt in varchar2,
p_futa_tax_exempt in varchar2,
p_medicare_tax_exempt in varchar2,
p_ss_tax_exempt in varchar2,
p_wage_exempt in varchar2,
p_statutory_employee in varchar2,
p_w2_filed_year in number,
p_supp_tax_override_rate in number,
p_excessive_wa_reject_date in date,
p_session_date in date,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_fed_information_category in varchar2,
p_fed_information1 in varchar2,
p_fed_information2 in varchar2,
p_fed_information3 in varchar2,
p_fed_information4 in varchar2,
p_fed_information5 in varchar2,
p_fed_information6 in varchar2,
p_fed_information7 in varchar2,
p_fed_information8 in varchar2,
p_fed_information9 in varchar2,
p_fed_information10 in varchar2,
p_fed_information11 in varchar2,
p_fed_information12 in varchar2,
p_fed_information13 in varchar2,
p_fed_information14 in varchar2,
p_fed_information15 in varchar2,
p_fed_information16 in varchar2,
p_fed_information17 in varchar2,
p_fed_information18 in varchar2,
p_fed_information19 in varchar2,
p_fed_information20 in varchar2,
p_fed_information21 in varchar2,
p_fed_information22 in varchar2,
p_fed_information23 in varchar2,
p_fed_information24 in varchar2,
p_fed_information25 in varchar2,
p_fed_information26 in varchar2,
p_fed_information27 in varchar2,
p_fed_information28 in varchar2,
p_fed_information29 in varchar2,
p_fed_information30 in varchar2,
p_mode in varchar2) is
lv_warning VARCHAR2(300);
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_fed_tax_row'||
' - updating row', 1);
update PAY_US_EMP_FED_TAX_RULES_F
set emp_fed_tax_rule_id = p_emp_fed_tax_rule_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
assignment_id = p_assignment_id ,
sui_state_code = p_sui_state_code,
sui_jurisdiction_code = p_sui_jurisdiction_code,
business_group_id = p_business_group_id ,
additional_wa_amount = p_additional_wa_amount,
filing_status_code = lpad(p_filing_status_code,2,'0'),
fit_override_amount = p_fit_override_amount,
fit_override_rate = p_fit_override_rate,
withholding_allowances = p_withholding_allowances,
cumulative_taxation = p_cumulative_taxation,
eic_filing_status_code = p_eic_filing_status_code,
fit_additional_tax = p_fit_additional_tax,
fit_exempt = p_fit_exempt,
futa_tax_exempt = p_futa_tax_exempt,
medicare_tax_exempt = p_medicare_tax_exempt,
ss_tax_exempt = p_ss_tax_exempt,
wage_exempt = p_wage_exempt,
statutory_employee = p_statutory_employee,
w2_filed_year = p_w2_filed_year,
supp_tax_override_rate = p_supp_tax_override_rate,
excessive_wa_reject_date = p_excessive_wa_reject_date,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute21,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30,
fed_information_category = p_fed_information_category,
fed_information1 = p_fed_information1,
fed_information2 = p_fed_information2,
fed_information3 = p_fed_information3,
fed_information4 = p_fed_information4,
fed_information5 = p_fed_information5,
fed_information6 = p_fed_information6,
fed_information7 = p_fed_information7,
fed_information8 = p_fed_information8,
fed_information9 = p_fed_information9,
fed_information10 = p_fed_information10,
fed_information11 = p_fed_information11,
fed_information12 = p_fed_information12,
fed_information13 = p_fed_information13,
fed_information14 = p_fed_information14,
fed_information15 = p_fed_information15,
fed_information16 = p_fed_information16,
fed_information17 = p_fed_information17,
fed_information18 = p_fed_information18,
fed_information19 = p_fed_information19,
fed_information20 = p_fed_information20,
fed_information21 = p_fed_information21,
fed_information22 = p_fed_information22,
fed_information23 = p_fed_information23,
fed_information24 = p_fed_information24,
fed_information25 = p_fed_information25,
fed_information26 = p_fed_information26,
fed_information27 = p_fed_information27,
fed_information28 = p_fed_information28,
fed_information29 = p_fed_information29,
fed_information30 = p_fed_information30
where rowid = chartorowid(p_row_id);
'pay_us_emp_dt_tax_rules.update_fed_tax');
/* Update workers compensation element entry for the sui state in
the federal record */
maintain_wc_ele_entry (p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_session_date => p_session_date,
p_jurisdiction_code => p_sui_jurisdiction_code,
p_mode => p_mode);
end update_fed_tax_row;
/* Name : update_state_tax_row
Purpose : To update the state tax rule record.
*/
procedure update_state_tax_row ( p_row_id in varchar2,
p_emp_state_tax_rule_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_state_code in varchar2,
p_jurisdiction_code in varchar2,
p_business_group_id in number,
p_additional_wa_amount in number,
p_filing_status_code in varchar2,
p_remainder_percent in number,
p_secondary_wa in number,
p_sit_additional_tax in number,
p_sit_override_amount in number,
p_sit_override_rate in number,
p_withholding_allowances in number,
p_excessive_wa_reject_date in date,
p_sdi_exempt in varchar2,
p_sit_exempt in varchar2,
p_sit_optional_calc_ind in varchar2,
p_state_non_resident_cert in varchar2,
p_sui_exempt in varchar2,
p_wc_exempt in varchar2,
p_wage_exempt in varchar2,
p_sui_wage_base_override_amt in number,
p_supp_tax_override_rate in number,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_sta_information_category in varchar2,
p_sta_information1 in varchar2,
p_sta_information2 in varchar2,
p_sta_information3 in varchar2,
p_sta_information4 in varchar2,
p_sta_information5 in varchar2,
p_sta_information6 in varchar2,
p_sta_information7 in varchar2,
p_sta_information8 in varchar2,
p_sta_information9 in varchar2,
p_sta_information10 in varchar2,
p_sta_information11 in varchar2,
p_sta_information12 in varchar2,
p_sta_information13 in varchar2,
p_sta_information14 in varchar2,
p_sta_information15 in varchar2,
p_sta_information16 in varchar2,
p_sta_information17 in varchar2,
p_sta_information18 in varchar2,
p_sta_information19 in varchar2,
p_sta_information20 in varchar2,
p_sta_information21 in varchar2,
p_sta_information22 in varchar2,
p_sta_information23 in varchar2,
p_sta_information24 in varchar2,
p_sta_information25 in varchar2,
p_sta_information26 in varchar2,
p_sta_information27 in varchar2,
p_sta_information28 in varchar2,
p_sta_information29 in varchar2,
p_sta_information30 in varchar2) is
begin
hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_st_tax_row'||
' - updating row', 1);
update PAY_US_EMP_STATE_TAX_RULES_F
set emp_state_tax_rule_id = p_emp_state_tax_rule_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
assignment_id = p_assignment_id,
state_code = p_state_code,
jurisdiction_code = p_jurisdiction_code,
business_group_id = p_business_group_id,
additional_wa_amount = p_additional_wa_amount,
filing_status_code = lpad(p_filing_status_code,2,'0'),
remainder_percent = p_remainder_percent,
secondary_wa = p_secondary_wa,
sit_additional_tax = p_sit_additional_tax,
sit_override_amount = p_sit_override_amount,
sit_override_rate = p_sit_override_rate,
withholding_allowances = p_withholding_allowances,
excessive_wa_reject_date = p_excessive_wa_reject_date,
sdi_exempt = p_sdi_exempt,
sit_exempt = p_sit_exempt,
sit_optional_calc_ind = p_sit_optional_calc_ind,
state_non_resident_cert = p_state_non_resident_cert,
sui_exempt = p_sui_exempt,
wc_exempt = p_wc_exempt,
wage_exempt = p_wage_exempt,
sui_wage_base_override_amount = p_sui_wage_base_override_amt,
supp_tax_override_rate = p_supp_tax_override_rate,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute21,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30,
sta_information_category = p_sta_information_category,
sta_information1 = p_sta_information1,
sta_information2 = p_sta_information2,
sta_information3 = p_sta_information3,
sta_information4 = p_sta_information4,
sta_information5 = p_sta_information5,
sta_information6 = p_sta_information6,
sta_information7 = p_sta_information7,
sta_information8 = p_sta_information8,
sta_information9 = p_sta_information9,
sta_information10 = p_sta_information10,
sta_information11 = p_sta_information11,
sta_information12 = p_sta_information12,
sta_information13 = p_sta_information13,
sta_information14 = p_sta_information14,
sta_information15 = p_sta_information15,
sta_information16 = p_sta_information16,
sta_information17 = p_sta_information17,
sta_information18 = p_sta_information18,
sta_information19 = p_sta_information19,
sta_information20 = p_sta_information20,
sta_information21 = p_sta_information21,
sta_information22 = p_sta_information22,
sta_information23 = p_sta_information23,
sta_information24 = p_sta_information24,
sta_information25 = p_sta_information25,
sta_information26 = p_sta_information26,
sta_information27 = p_sta_information27,
sta_information28 = p_sta_information28,
sta_information29 = p_sta_information29,
sta_information30 = p_sta_information30
where rowid = chartorowid(p_row_id);
'pay_us_emp_dt_tax_rules.update_state_tax_row');
' - updated row', 2);
end update_state_tax_row;
/* Name : update_county_tax_row
Purpose : To update the county tax rule record.
*/
procedure update_county_tax_row ( p_row_id in varchar2,
p_emp_county_tax_rule_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2,
p_business_group_id in number,
p_additional_wa_rate in number,
p_filing_status_code in varchar2,
p_jurisdiction_code in varchar2,
p_lit_additional_tax in number,
p_lit_override_amount in number,
p_lit_override_rate in number,
p_withholding_allowances in number,
p_lit_exempt in varchar2,
p_sd_exempt in varchar2,
p_ht_exempt in varchar2,
p_wage_exempt in varchar2,
p_school_district_code in varchar2,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_cnt_information_category in varchar2,
p_cnt_information1 in varchar2,
p_cnt_information2 in varchar2,
p_cnt_information3 in varchar2,
p_cnt_information4 in varchar2,
p_cnt_information5 in varchar2,
p_cnt_information6 in varchar2,
p_cnt_information7 in varchar2,
p_cnt_information8 in varchar2,
p_cnt_information9 in varchar2,
p_cnt_information10 in varchar2,
p_cnt_information11 in varchar2,
p_cnt_information12 in varchar2,
p_cnt_information13 in varchar2,
p_cnt_information14 in varchar2,
p_cnt_information15 in varchar2,
p_cnt_information16 in varchar2,
p_cnt_information17 in varchar2,
p_cnt_information18 in varchar2,
p_cnt_information19 in varchar2,
p_cnt_information20 in varchar2,
p_cnt_information21 in varchar2,
p_cnt_information22 in varchar2,
p_cnt_information23 in varchar2,
p_cnt_information24 in varchar2,
p_cnt_information25 in varchar2,
p_cnt_information26 in varchar2,
p_cnt_information27 in varchar2,
p_cnt_information28 in varchar2,
p_cnt_information29 in varchar2,
p_cnt_information30 in varchar2) is
begin
if p_school_district_code is not null
then
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_county_tax_row'||
' - checking sd', 1);
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_county_tax_row'||
' - updating row', 2);
update PAY_US_EMP_COUNTY_TAX_RULES_F
set emp_county_tax_rule_id = p_emp_county_tax_rule_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
assignment_id = p_assignment_id,
state_code = p_state_code,
county_code = p_county_code,
business_group_id = p_business_group_id,
additional_wa_rate = p_additional_wa_rate,
filing_status_code = lpad(p_filing_status_code,2,'0'),
jurisdiction_code = p_jurisdiction_code,
lit_additional_tax = p_lit_additional_tax,
lit_override_amount = p_lit_override_amount,
lit_override_rate = p_lit_override_rate,
withholding_allowances = p_withholding_allowances,
lit_exempt = p_lit_exempt,
sd_exempt = p_sd_exempt,
ht_exempt = p_ht_exempt,
wage_exempt = p_wage_exempt,
school_district_code = p_school_district_code,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute21,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30,
cnt_information_category = p_cnt_information_category,
cnt_information1 = p_cnt_information1,
cnt_information2 = p_cnt_information2,
cnt_information3 = p_cnt_information3,
cnt_information4 = p_cnt_information4,
cnt_information5 = p_cnt_information5,
cnt_information6 = p_cnt_information6,
cnt_information7 = p_cnt_information7,
cnt_information8 = p_cnt_information8,
cnt_information9 = p_cnt_information9,
cnt_information10 = p_cnt_information10,
cnt_information11 = p_cnt_information11,
cnt_information12 = p_cnt_information12,
cnt_information13 = p_cnt_information13,
cnt_information14 = p_cnt_information14,
cnt_information15 = p_cnt_information15,
cnt_information16 = p_cnt_information16,
cnt_information17 = p_cnt_information17,
cnt_information18 = p_cnt_information18,
cnt_information19 = p_cnt_information19,
cnt_information20 = p_cnt_information20,
cnt_information21 = p_cnt_information21,
cnt_information22 = p_cnt_information22,
cnt_information23 = p_cnt_information23,
cnt_information24 = p_cnt_information24,
cnt_information25 = p_cnt_information25,
cnt_information26 = p_cnt_information26,
cnt_information27 = p_cnt_information27,
cnt_information28 = p_cnt_information28,
cnt_information29 = p_cnt_information29,
cnt_information30 = p_cnt_information30
where rowid = chartorowid(p_row_id);
'pay_us_emp_dt_tax_rules.update_county_tax_row');
/* Insert row into the pay_us_asg_reporting table */
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_county_tax_row'||
' - asg_geo row', 3);
end update_county_tax_row;
/* Name : update_city_tax_row
Purpose : To update the city tax rule record.
*/
procedure update_city_tax_row ( p_row_id in varchar2,
p_emp_city_tax_rule_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2,
p_city_code in varchar2,
p_business_group_id in number,
p_additional_wa_rate in number,
p_filing_status_code in varchar2,
p_jurisdiction_code in varchar2,
p_lit_additional_tax in number,
p_lit_override_amount in number,
p_lit_override_rate in number,
p_withholding_allowances in number,
p_lit_exempt in varchar2,
p_sd_exempt in varchar2,
p_ht_exempt in varchar2,
p_wage_exempt in varchar2,
p_school_district_code in varchar2,
p_attribute_category in varchar2,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute21 in varchar2,
p_attribute22 in varchar2,
p_attribute23 in varchar2,
p_attribute24 in varchar2,
p_attribute25 in varchar2,
p_attribute26 in varchar2,
p_attribute27 in varchar2,
p_attribute28 in varchar2,
p_attribute29 in varchar2,
p_attribute30 in varchar2,
p_cty_information_category in varchar2,
p_cty_information1 in varchar2,
p_cty_information2 in varchar2,
p_cty_information3 in varchar2,
p_cty_information4 in varchar2,
p_cty_information5 in varchar2,
p_cty_information6 in varchar2,
p_cty_information7 in varchar2,
p_cty_information8 in varchar2,
p_cty_information9 in varchar2,
p_cty_information10 in varchar2,
p_cty_information11 in varchar2,
p_cty_information12 in varchar2,
p_cty_information13 in varchar2,
p_cty_information14 in varchar2,
p_cty_information15 in varchar2,
p_cty_information16 in varchar2,
p_cty_information17 in varchar2,
p_cty_information18 in varchar2,
p_cty_information19 in varchar2,
p_cty_information20 in varchar2,
p_cty_information21 in varchar2,
p_cty_information22 in varchar2,
p_cty_information23 in varchar2,
p_cty_information24 in varchar2,
p_cty_information25 in varchar2,
p_cty_information26 in varchar2,
p_cty_information27 in varchar2,
p_cty_information28 in varchar2,
p_cty_information29 in varchar2,
p_cty_information30 in varchar2) is
begin
if p_school_district_code is not null
and p_state_code <> '39'
then
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_city_tax_row'||
' - checking sd', 1);
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_city_tax_row'||
' - updating row', 2);
update PAY_US_EMP_CITY_TAX_RULES_F
set emp_city_tax_rule_id = p_emp_city_tax_rule_id,
effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
assignment_id = p_assignment_id,
state_code = p_state_code,
county_code = p_county_code,
city_code = p_city_code,
business_group_id = p_business_group_id,
additional_wa_rate = p_additional_wa_rate,
filing_status_code = lpad(p_filing_status_code,2,'0'),
jurisdiction_code = p_jurisdiction_code,
lit_additional_tax = p_lit_additional_tax,
lit_override_amount = p_lit_override_amount,
lit_override_rate = p_lit_override_rate,
withholding_allowances = p_withholding_allowances,
lit_exempt = p_lit_exempt,
sd_exempt = p_sd_exempt,
ht_exempt = p_ht_exempt,
wage_exempt = p_wage_exempt,
school_district_code = p_school_district_code,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute21,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30,
cty_information_category = p_cty_information_category,
cty_information1 = p_cty_information1,
cty_information2 = p_cty_information2,
cty_information3 = p_cty_information3,
cty_information4 = p_cty_information4,
cty_information5 = p_cty_information5,
cty_information6 = p_cty_information6,
cty_information7 = p_cty_information7,
cty_information8 = p_cty_information8,
cty_information9 = p_cty_information9,
cty_information10 = p_cty_information10,
cty_information11 = p_cty_information11,
cty_information12 = p_cty_information12,
cty_information13 = p_cty_information13,
cty_information14 = p_cty_information14,
cty_information15 = p_cty_information15,
cty_information16 = p_cty_information16,
cty_information17 = p_cty_information17,
cty_information18 = p_cty_information18,
cty_information19 = p_cty_information19,
cty_information20 = p_cty_information20,
cty_information21 = p_cty_information21,
cty_information22 = p_cty_information22,
cty_information23 = p_cty_information23,
cty_information24 = p_cty_information24,
cty_information25 = p_cty_information25,
cty_information26 = p_cty_information26,
cty_information27 = p_cty_information27,
cty_information28 = p_cty_information28,
cty_information29 = p_cty_information29,
cty_information30 = p_cty_information30
where rowid = chartorowid(p_row_id);
'pay_us_emp_dt_tax_rules.update_city_tax_row');
/* Insert row into the pay_us_asg_reporting table */
hr_utility.set_location('pay_us_emp_dt_tax_rules.update_city_tax_row'||
' - asg_geo row', 3);
end update_city_tax_row;
/* Name : delete_tax_row
Purpose : This routine will be called by the W4 form to purge a tax rule record.
Only purging(i.e. ZAP) of the tax record will be allowed. No other kind
of delete will be allowed for the tax record. If a state record is purged,
then all of the county and city records for that state, will also be purged.
Similarly, is a county record is purged then all of the city records under
that county, will also be purged.
Along with the tax rule record, the tax %age records associated with that
tax rules record, will also be purged i.e. delete cascade
Parameters :
p_assignment_id -> The assignment whose tax record will be purged.
p_state_code -> State whose tax record will be purged
p_county_code -> County whose tax record will be purged
p_city_code -> City whose tax record will be purged
*/
procedure delete_tax_row ( p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2,
p_city_code in varchar2) is
l_ret_code number;
select puc.jurisdiction_code
from PAY_US_EMP_COUNTY_TAX_RULES_F puc
where puc.assignment_id = p_assignment_id
and puc.state_code = p_state_code;
select puc.jurisdiction_code
from PAY_US_EMP_CITY_TAX_RULES_F puc
where puc.assignment_id = p_assignment_id
and puc.state_code = p_state_code;
select puc.jurisdiction_code
from PAY_US_EMP_CITY_TAX_RULES_F puc
where puc.assignment_id = p_assignment_id
and puc.state_code = p_state_code
and puc.county_code = p_county_code;
select min(effective_start_date)
from PAY_US_EMP_FED_TAX_RULES_F
where assignment_id = p_assignment_id;
/* Now all validations done. Go ahead and delete the element entries.
Once the element entries are deleted, delete the tax rules records */
/* Get the start date of the tax percentage records */
open csr_get_eff_date;
'pay_us_emp_dt_tax_rules.delete_tax_row');
/* Processing for deleteing the state tax rule record */
if p_state_code is not null and p_county_code is null
and p_city_code is null then
/* Delete the element entries only if Payroll is installed */
if l_payroll_installed then
/* Get the cities for the state and call the maintain_element_entry routine
to delete the city %age records for the cities in the state */
open csr_state_cities;
/* Delete the %age tax record for the jurisdiction */
maintain_element_entry(p_assignment_id => p_assignment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => to_date('31-12-4712','dd-mm-yyyy'),
p_session_date => l_effective_start_date,
p_jurisdiction_code => l_jurisdiction_code,
p_percentage_time => 0,
p_mode => 'ZAP');
to delete the county %age records for the cities in the state */
open csr_state_counties;
/* Delete the %age tax record for the jurisdiction */
maintain_element_entry(p_assignment_id => p_assignment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => to_date('31-12-4712','dd-mm-yyyy'),
p_session_date => l_effective_start_date,
p_jurisdiction_code => l_jurisdiction_code,
p_percentage_time => 0,
p_mode => 'ZAP');
/* Delete the state %age records for the state */
maintain_element_entry(p_assignment_id => p_assignment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => to_date('31-12-4712','dd-mm-yyyy'),
p_session_date => l_effective_start_date,
p_jurisdiction_code => p_state_code || '-000-0000',
p_percentage_time => 0,
p_mode => 'ZAP');
/* Delete records from PAY_US_EMP_CITY_TAX_RULES_F */
delete PAY_US_EMP_CITY_TAX_RULES_F
where assignment_id = p_assignment_id
and state_code = p_state_code;
/* Delete records from PAY_US_EMP_COUNTY_TAX_RULES_F */
delete PAY_US_EMP_COUNTY_TAX_RULES_F
where assignment_id = p_assignment_id
and state_code = p_state_code;
/* Delete records from PAY_US_EMP_STATE_TAX_RULES_F */
delete PAY_US_EMP_STATE_TAX_RULES_F
where assignment_id = p_assignment_id
and state_code = p_state_code;
to delete the city %age records for the cities in the county */
open csr_county_cities;
/* Delete the %age tax record for the jurisdiction */
maintain_element_entry(p_assignment_id => p_assignment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => to_date('31-12-4712','dd-mm-yyyy'),
p_session_date => l_effective_start_date,
p_jurisdiction_code => l_jurisdiction_code,
p_percentage_time => 0,
p_mode => 'ZAP');
/* Delete the state %age records for the county */
maintain_element_entry(p_assignment_id => p_assignment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => to_date('31-12-4712','dd-mm-yyyy'),
p_session_date => l_effective_start_date,
p_jurisdiction_code => p_state_code ||'-' ||
p_county_code ||'-0000',
p_percentage_time => 0,
p_mode => 'ZAP');
/* Delete records from PAY_US_EMP_CITY_TAX_RULES_F */
delete PAY_US_EMP_CITY_TAX_RULES_F
where assignment_id = p_assignment_id
and state_code = p_state_code
and county_code = p_county_code;
/* Delete records from PAY_US_EMP_COUNTY_TAX_RULES_F */
delete PAY_US_EMP_COUNTY_TAX_RULES_F
where assignment_id = p_assignment_id
and state_code = p_state_code
and county_code = p_county_code;
/* Delete the state %age records for the city */
maintain_element_entry(p_assignment_id => p_assignment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => to_date('31-12-4712','dd-mm-yyyy'),
p_session_date => l_effective_start_date,
p_jurisdiction_code => p_state_code ||'-' ||
p_county_code ||'-'|| p_city_code,
p_percentage_time => 0,
p_mode => 'ZAP');
/* Delete records from PAY_US_EMP_CITY_TAX_RULES_F */
delete PAY_US_EMP_CITY_TAX_RULES_F
where assignment_id = p_assignment_id
and state_code = p_state_code
and county_code = p_county_code
and city_code = p_city_code;
end delete_tax_row;
select assignment_id
from PER_ASSIGNMENTS_F
where assignment_id = p_assignment_id
and p_effective_start_date between effective_start_date
and effective_end_date
for update of assignment_id nowait;
select *
from PAY_US_EMP_FED_TAX_RULES_F
where rowid = chartorowid(p_row_id)
for update of emp_fed_tax_rule_id nowait;
select assignment_id
from PER_ASSIGNMENTS_F
where assignment_id = p_assignment_id
and p_effective_start_date between effective_start_date
and effective_end_date
for update of assignment_id nowait;
select *
from PAY_US_EMP_STATE_TAX_RULES_F
where rowid = chartorowid(p_row_id)
for update of emp_state_tax_rule_id nowait;
select assignment_id
from PER_ASSIGNMENTS_F
where assignment_id = p_assignment_id
and p_effective_start_date between effective_start_date
and effective_end_date
for update of assignment_id nowait;
select *
from PAY_US_EMP_COUNTY_TAX_RULES_F
where rowid = chartorowid(p_row_id)
for update of emp_county_tax_rule_id nowait;
select assignment_id
from PER_ASSIGNMENTS_F
where assignment_id = p_assignment_id
and p_effective_start_date between effective_start_date
and effective_end_date
for update of assignment_id nowait;
select *
from PAY_US_EMP_CITY_TAX_RULES_F
where rowid = chartorowid(p_row_id)
for update of emp_city_tax_rule_id nowait;
function insert_def_fed_rec(p_assignment_id number,
p_effective_start_date date,
p_effective_end_date date,
p_sui_state_code varchar2,
p_business_group_id number)
return number is
l_filing_status_code varchar2(2);
select lookup_code
from HR_LOOKUPS
where lookup_type = 'US_FIT_FILING_STATUS'
and upper(meaning) = 'SINGLE';
select lookup_code
from FND_LOOKUP_VALUES
where lookup_type = 'US_FIT_FILING_STATUS'
and upper(meaning) = 'SINGLE'
and language = 'US';
select lookup_code
from hr_lookups
where lookup_type = 'US_EIC_FILING_STATUS'
and upper(meaning) = 'NO EIC';
select lookup_code
from fnd_lookup_values
where lookup_type = 'US_EIC_FILING_STATUS'
and upper(meaning) = 'NO EIC'
and language = 'US';
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',1);
hr_utility.trace('insert_def_fed_rec** ' || to_char(p_assignment_id));
hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_start_date));
hr_utility.trace('insert_def_fed_rec** ' || to_char(p_effective_end_date));
hr_utility.trace('insert_def_fed_rec** ' || p_sui_state_code);
hr_utility.trace('insert_def_fed_rec** ' || to_char(p_business_group_id));
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',2);
/* Insert Federal Tax Record */
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',3);
l_mode := 'INSERT';
insert_fed_tax_row(p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_assignment_id => p_assignment_id,
p_sui_state_code => p_sui_state_code,
p_sui_jurisdiction_code => p_sui_state_code || '-000-0000',
p_business_group_id => p_business_group_id,
p_additional_wa_amount => 0,
p_filing_status_code => lpad(l_filing_status_code,2,'0'),
p_fit_override_amount => 0,
p_fit_override_rate => 0,
p_withholding_allowances => 0,
p_cumulative_taxation => 'N',
p_eic_filing_status_code => l_eic_fstatus_code,
p_fit_additional_tax => 0,
p_fit_exempt => 'N',
p_futa_tax_exempt => 'N',
p_medicare_tax_exempt => 'N',
p_ss_tax_exempt => 'N',
p_wage_exempt => 'N',
p_statutory_employee => 'N',
p_w2_filed_year => null,
p_supp_tax_override_rate => 0,
p_excessive_wa_reject_date => null,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute16 => null,
p_attribute17 => null,
p_attribute18 => null,
p_attribute19 => null,
p_attribute20 => null,
p_attribute21 => null,
p_attribute22 => null,
p_attribute23 => null,
p_attribute24 => null,
p_attribute25 => null,
p_attribute26 => null,
p_attribute27 => null,
p_attribute28 => null,
p_attribute29 => null,
p_attribute30 => null,
p_fed_information_category => null,
p_fed_information1 => null,
p_fed_information2 => null,
p_fed_information3 => null,
p_fed_information4 => null,
p_fed_information5 => null,
p_fed_information6 => null,
p_fed_information7 => null,
p_fed_information8 => null,
p_fed_information9 => null,
p_fed_information10 => null,
p_fed_information11 => null,
p_fed_information12 => null,
p_fed_information13 => null,
p_fed_information14 => null,
p_fed_information15 => null,
p_fed_information16 => null,
p_fed_information17 => null,
p_fed_information18 => null,
p_fed_information19 => null,
p_fed_information20 => null,
p_fed_information21 => null,
p_fed_information22 => null,
p_fed_information23 => null,
p_fed_information24 => null,
p_fed_information25 => null,
p_fed_information26 => null,
p_fed_information27 => null,
p_fed_information28 => null,
p_fed_information29 => null,
p_fed_information30 => null,
p_mode => 'INSERT');
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_fed_rec',5);
end insert_def_fed_rec;
/* Insert state record */
function insert_def_state_rec(p_assignment_id number,
p_effective_start_date date,
p_effective_end_date date,
p_state_code varchar2,
p_business_group_id number,
p_percent_time number)
return number is
l_emp_state_tax_rule_id number;
select hrl.lookup_code, peft.withholding_allowances
from HR_LOOKUPS hrl
, PAY_US_EMP_FED_TAX_RULES_V peft
where hrl.lookup_type = 'US_FS_'||p_state
and upper(hrl.meaning) = decode(
upper(substr(peft.filing_status,1,7)),
'MARRIED',
'MARRIED',
upper(peft.filing_status))
and peft.assignment_id = p_assignment ;
select flv.lookup_code, peft.withholding_allowances
from FND_LOOKUP_VALUES flv
, PAY_US_EMP_FED_TAX_RULES_V peft
where flv.lookup_type = 'US_FS_'||p_state
and upper(flv.meaning) = decode(
upper(substr(peft.filing_status,1,7)),
'MARRIED',
'MARRIED',
upper(peft.filing_status))
and peft.assignment_id = p_assignment
and language = 'US' ;
select hsck.segment1
from hr_soft_coding_keyflex hsck,
per_assignments_f paf
where paf.assignment_id = p_assignment
and p_effective_start_date between paf.effective_start_date
and paf.effective_end_date
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id;
select hoi.org_information12
from pay_us_states pus,
hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'State Tax Rules'
and pus.state_code = p_state
and hoi.org_information1 = pus.state_abbrev;
select 'Y'
from dual
where exists (select null
from pay_us_emp_state_tax_rules_f pst
where pst.assignment_id = p_assignment_id
and state_code = p_state_code
and business_group_id + 0 = p_business_group_id);
/* Insert State Tax record */
hr_utility.set_location('pay_us_emp_dt_tax_rules.ins_def_state',8);
insert_state_tax_row ( p_row_id => l_row_id,
p_emp_state_tax_rule_id => l_emp_state_tax_rule_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_assignment_id => p_assignment_id,
p_state_code => p_state_code,
p_jurisdiction_code => p_state_code ||'-000-0000',
p_business_group_id => p_business_group_id,
p_additional_wa_amount => 0,
p_filing_status_code => lpad(l_filing_status_code,2,'0'),
p_remainder_percent => 0,
p_secondary_wa => 0,
p_sit_additional_tax => 0,
p_sit_override_amount => 0,
p_sit_override_rate => 0,
p_withholding_allowances => l_allowances,
p_excessive_wa_reject_date => null,
p_sdi_exempt => 'N',
p_sit_exempt => 'N',
p_sit_optional_calc_ind => null,
p_state_non_resident_cert => 'N',
p_sui_exempt => 'N',
p_wc_exempt => null,
p_wage_exempt => 'N',
p_sui_wage_base_override_amt => null,
p_supp_tax_override_rate => 0,
p_time_in_state => nvl(p_percent_time,0),
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute16 => null,
p_attribute17 => null,
p_attribute18 => null,
p_attribute19 => null,
p_attribute20 => null,
p_attribute21 => null,
p_attribute22 => null,
p_attribute23 => null,
p_attribute24 => null,
p_attribute25 => null,
p_attribute26 => null,
p_attribute27 => null,
p_attribute28 => null,
p_attribute29 => null,
p_attribute30 => null,
p_sta_information_category => null,
p_sta_information1 => null,
p_sta_information2 => null,
p_sta_information3 => null,
p_sta_information4 => null,
p_sta_information5 => null,
p_sta_information6 => null,
p_sta_information7 => null,
p_sta_information8 => null,
p_sta_information9 => null,
p_sta_information10 => null,
p_sta_information11 => null,
p_sta_information12 => null,
p_sta_information13 => null,
p_sta_information14 => null,
p_sta_information15 => null,
p_sta_information16 => null,
p_sta_information17 => null,
p_sta_information18 => null,
p_sta_information19 => null,
p_sta_information20 => null,
p_sta_information21 => null,
p_sta_information22 => null,
p_sta_information23 => null,
p_sta_information24 => null,
p_sta_information25 => null,
p_sta_information26 => null,
p_sta_information27 => null,
p_sta_information28 => null,
p_sta_information29 => null,
p_sta_information30 => null );
end insert_def_state_rec;
function insert_def_county_rec(p_assignment_id number,
p_effective_start_date date,
p_effective_end_date date,
p_state_code varchar2,
p_county_code varchar2,
p_business_group_id number,
p_percent_time number)
return number is
l_filing_status_code varchar2(2);
select lookup_code
from HR_LOOKUPS
where lookup_type = 'US_LIT_FILING_STATUS'
and upper(meaning) = 'SINGLE';
select lookup_code
from FND_LOOKUP_VALUES
where lookup_type = 'US_LIT_FILING_STATUS'
and upper(meaning) = 'SINGLE'
and language = 'US';
select 'Y'
from dual
where exists (select null
from pay_us_emp_county_tax_rules_f pst
where pst.assignment_id = p_assignment_id
and state_code = p_state_code
and county_code = p_county_code
and business_group_id + 0 = p_business_group_id);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',1);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',91);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',2);
/* Insert County Tax record */
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',3);
insert_county_tax_row(p_row_id => l_row_id,
p_emp_county_tax_rule_id => l_emp_county_tax_rule_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_assignment_id => p_assignment_id,
p_state_code => p_state_code,
p_county_code => p_county_code,
p_business_group_id => p_business_group_id,
p_additional_wa_rate => 0,
p_filing_status_code => lpad(l_filing_status_code,2,'0'),
p_jurisdiction_code => p_state_code || '-' ||
p_county_code ||'-0000',
p_lit_additional_tax => 0,
p_lit_override_amount => 0,
p_lit_override_rate => 0,
p_withholding_allowances => 0,
p_lit_exempt => 'N',
p_sd_exempt => null,
p_ht_exempt => null,
p_wage_exempt => 'N',
p_school_district_code => null,
p_time_in_county => nvl(p_percent_time,0),
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute16 => null,
p_attribute17 => null,
p_attribute18 => null,
p_attribute19 => null,
p_attribute20 => null,
p_attribute21 => null,
p_attribute22 => null,
p_attribute23 => null,
p_attribute24 => null,
p_attribute25 => null,
p_attribute26 => null,
p_attribute27 => null,
p_attribute28 => null,
p_attribute29 => null,
p_attribute30 => null,
p_cnt_information_category => null,
p_cnt_information1 => null,
p_cnt_information2 => null,
p_cnt_information3 => null,
p_cnt_information4 => null,
p_cnt_information5 => null,
p_cnt_information6 => null,
p_cnt_information7 => null,
p_cnt_information8 => null,
p_cnt_information9 => null,
p_cnt_information10 => null,
p_cnt_information11 => null,
p_cnt_information12 => null,
p_cnt_information13 => null,
p_cnt_information14 => null,
p_cnt_information15 => null,
p_cnt_information16 => null,
p_cnt_information17 => null,
p_cnt_information18 => null,
p_cnt_information19 => null,
p_cnt_information20 => null,
p_cnt_information21 => null,
p_cnt_information22 => null,
p_cnt_information23 => null,
p_cnt_information24 => null,
p_cnt_information25 => null,
p_cnt_information26 => null,
p_cnt_information27 => null,
p_cnt_information28 => null,
p_cnt_information29 => null,
p_cnt_information30 => null );
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',4);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_county_rec',5);
end insert_def_county_rec;
function insert_def_city_rec(p_assignment_id number,
p_effective_start_date date,
p_effective_end_date date,
p_state_code varchar2,
p_county_code varchar2,
p_city_code varchar2,
p_business_group_id number,
p_percent_time number)
return number is
l_filing_status_code varchar2(2);
select lookup_code
from HR_LOOKUPS
where lookup_type = 'US_LIT_FILING_STATUS'
and upper(meaning) = 'SINGLE';
select lookup_code
from FND_LOOKUP_VALUES
where lookup_type = 'US_LIT_FILING_STATUS'
and upper(meaning) = 'SINGLE'
and language = 'US';
select 'Y'
from dual
where exists (select null
from pay_us_emp_city_tax_rules_f pst
where pst.assignment_id = p_assignment_id
and state_code = p_state_code
and county_code = p_county_code
and city_code = p_city_code
and business_group_id + 0 = p_business_group_id);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',1);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',91);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',2);
/* Insert City Tax record */
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',3);
insert_city_tax_row(p_row_id => l_row_id,
p_emp_city_tax_rule_id => l_emp_city_tax_rule_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_assignment_id => p_assignment_id,
p_state_code => p_state_code,
p_county_code => p_county_code,
p_city_code => p_city_code,
p_business_group_id => p_business_group_id,
p_additional_wa_rate => 0,
p_filing_status_code => lpad(l_filing_status_code,2,'0'),
p_jurisdiction_code => p_state_code || '-' ||
p_county_code ||'-' || p_city_code,
p_lit_additional_tax => 0,
p_lit_override_amount => 0,
p_lit_override_rate => 0,
p_withholding_allowances => 0,
p_lit_exempt => 'N',
p_sd_exempt => null,
p_ht_exempt => null,
p_wage_exempt => 'N',
p_school_district_code => null,
p_time_in_city => nvl(p_percent_time,0),
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute16 => null,
p_attribute17 => null,
p_attribute18 => null,
p_attribute19 => null,
p_attribute20 => null,
p_attribute21 => null,
p_attribute22 => null,
p_attribute23 => null,
p_attribute24 => null,
p_attribute25 => null,
p_attribute26 => null,
p_attribute27 => null,
p_attribute28 => null,
p_attribute29 => null,
p_attribute30 => null,
p_cty_information_category => null,
p_cty_information1 => null,
p_cty_information2 => null,
p_cty_information3 => null,
p_cty_information4 => null,
p_cty_information5 => null,
p_cty_information6 => null,
p_cty_information7 => null,
p_cty_information8 => null,
p_cty_information9 => null,
p_cty_information10 => null,
p_cty_information11 => null,
p_cty_information12 => null,
p_cty_information13 => null,
p_cty_information14 => null,
p_cty_information15 => null,
p_cty_information16 => null,
p_cty_information17 => null,
p_cty_information18 => null,
p_cty_information19 => null,
p_cty_information20 => null,
p_cty_information21 => null,
p_cty_information22 => null,
p_cty_information23 => null,
p_cty_information24 => null,
p_cty_information25 => null,
p_cty_information26 => null,
p_cty_information27 => null,
p_cty_information28 => null,
p_cty_information29 => null,
p_cty_information30 => null );
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',4);
hr_utility.set_location('pay_us_emp_dt_tax_rules.insert_def_city_rec',5);
end insert_def_city_rec;
select peev.screen_entry_value jurisdiction,
peef.effective_start_date start_date
from PAY_ELEMENT_ENTRY_VALUES_F peev,
PAY_ELEMENT_ENTRIES_F peef,
PAY_INPUT_VALUES_F piv,
PAY_ELEMENT_TYPES_F pet
where pet.element_name = 'VERTEX'
and pet.element_type_id = piv.element_type_id
and piv.name = 'Jurisdiction'
and piv.input_value_id = 0 + peev.input_value_id
and peev.element_entry_id = peef.element_entry_id
and peev.effective_start_date = peef.effective_start_date
and peev.effective_end_date = peef.effective_end_date
and p_assignment_id = peef.assignment_id
and peef.effective_start_date = p_effective_start_date
and peef.effective_end_date = p_effective_end_date
and peef.effective_start_date between pet.effective_start_date and pet.effective_end_date; -- Bug 3354060 added to remove MJC between
Purpose : To update sui_wage_base_override_amount for the new work location,
with respect to every change in location state. The procedure
will also take care of the condition of changing GRE when work
location state remains unchanged / changed as well Rehire condition.
*/
procedure set_sui_wage_base_override(p_assignment_id in number,
p_state_code in varchar2 default null,
p_session_date in date)
is
l_sui_er_wg_lt_curr_state pay_us_state_tax_info_f.sui_er_wage_limit%type ;
select state_code
from pay_us_emp_state_tax_rules_f
where assignment_id = p_assignment_id ;
select to_number(segment1) tax_unit_id
from hr_soft_coding_keyflex a,
per_assignments_f b
where b.assignment_id = p_assignment_id
and b.soft_coding_keyflex_id = a.soft_coding_keyflex_id
and p_session_date between b.effective_start_date and b.effective_end_date ;
select fnd_number.canonical_to_number(UE.creator_id)
from ff_user_entities UE,
ff_database_items DI
where DI.user_name = p_dbi_name --'SUI_ER_TAXABLE_PER_JD_GRE_YTD'
and UE.user_entity_id = DI.user_entity_id
and Ue.creator_type = 'B'
and UE.legislation_code = 'US' ;
select max(effective_start_date)
from per_assignments_f paf,
per_assignment_status_types past
where paf.assignment_id = p_assignment_id
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_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_i
where paf_i.assignment_id = p_assignment_id
and paf_i.business_group_id = past.business_group_id)
)
)
and paf.payroll_id is not null ;
select sui_er_wage_limit
from pay_us_state_tax_info_f
where p_effective_date between effective_start_date
and effective_end_date
and sta_information_category = 'State tax limit rate info'
and state_code = p_state_code ;
select max(effective_date)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q','B','V','I')
and nvl(ppa.date_earned,ppa.effective_date) between trunc(p_session_date,'Y')
and last_day(add_months(trunc(p_session_date,'Y'),11)) ;
select 'Y'
from per_assignments_f paf_o,
per_assignment_status_types past
where paf_o.assignment_id = p_assignment_id
and paf_o.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_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 = p_assignment_id
and paf_a.business_group_id = past.business_group_id)
)
)
and exists
(
select distinct paf_i.assignment_id
from per_assignments_f paf_i,
per_assignments_f paf_term
where paf_i.person_id = paf_o.person_id
and paf_i.person_id = paf_term.person_id
and paf_i.assignment_id > paf_term.assignment_id
and paf_i.effective_start_date >= paf_term.effective_end_date
and ( trunc(paf_i.effective_end_date,'Y') = trunc(p_session_date,'Y')
or (trunc(paf_i.effective_start_date,'Y') = trunc(p_session_date,'Y')
and trunc(paf_i.effective_end_date,'Y') > trunc(p_session_date,'Y')))
) ;
select distinct paf.person_id
from per_assignments_f paf
where paf.assignment_id = p_assignment_id ;
select distinct paf.assignment_id
from per_assignments_f paf
where paf.person_id = p_person_id
and ( trunc(paf.effective_end_date,'Y') = trunc(p_session_date,'Y')
or (trunc(paf.effective_start_date,'Y') = trunc(p_session_date,'Y')
and trunc(paf.effective_end_date,'Y') > trunc(p_session_date,'Y'))) ;
update pay_us_emp_state_tax_rules_f
set sui_wage_base_override_amount = l_sui_wg_base
where assignment_id = p_assignment_id
and state_code = i_all_curr_states.state_code ;
update pay_us_emp_state_tax_rules_f
set sui_wage_base_override_amount = null
where assignment_id = p_assignment_id
and state_code = i_all_curr_states.state_code ;
hr_utility.trace('SUI Wage Base Updated...') ;
update pay_us_emp_state_tax_rules_f
set sui_wage_base_override_amount = l_sui_wg_base
where assignment_id = p_assignment_id
and state_code = state_code_tab(i) ;
update pay_us_emp_state_tax_rules_f
set sui_wage_base_override_amount = null
where assignment_id = p_assignment_id
and state_code = state_code_tab(i) ;
hr_utility.trace('SUI Wage Base Updated...') ;
update pay_us_emp_state_tax_rules_f
set sui_wage_base_override_amount = l_sui_er_wg_lt_curr_state
where assignment_id = p_assignment_id
and state_code = p_state_code ;
select pus.state_code,
puco.county_code,
puci.city_code,
pus1.state_code,
puco1.county_code,
puci1.city_code
from PAY_US_CITY_NAMES puci1,
PAY_US_COUNTIES puco1,
PAY_US_STATES pus1,
PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puco,
PAY_US_STATES pus,
HR_LOCATIONS hrl
where hrl.location_id = p_location
and pus.state_abbrev = hrl.region_2
and puco.state_code = pus.state_code
and puco.county_name = hrl.region_1
and puci.state_code = puco.state_code
and puci.county_code = puco.county_code
and puci.city_name = hrl.town_or_city
and pus1.state_abbrev = nvl(hrl.loc_information17,hrl.region_2)
and puco1.state_code = pus1.state_code
and puco1.county_name = nvl(hrl.loc_information19,hrl.region_1)
and puci1.state_code = puco1.state_code
and puci1.county_code = puco1.county_code
and puci1.city_name = nvl(hrl.loc_information18,hrl.town_or_city);
select min(effective_start_date)
from PAY_US_EMP_FED_TAX_RULES_F
where assignment_id = p_assignment_id;
select max(effective_end_date)
from per_assignments_f
where assignment_id = p_assignment_id;
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
-- p_effective_end_date => l_end_of_time,
p_state_code => l_state_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
-- p_effective_end_date => l_end_of_time,
p_state_code => l_state_code,
p_county_code => l_county_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
-- p_effective_end_date => l_end_of_time,
p_state_code => l_state_code,
p_county_code => l_county_code,
p_city_code => l_city_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
-- call the procedure which does the update
-- Turning Off SUI Wage Base Override Functionality due to Bug# 5486281
/*
IF hr_utility.chk_product_install(p_product =>'Oracle Payroll',
p_legislation => 'US')
then
hr_utility.set_location('pay_us_emp_dt_tax_rules.create_new_location_rec',102);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
-- p_effective_end_date => l_end_of_time,
p_state_code => l_state_code,
p_county_code => l_county_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
-- p_effective_end_date => l_end_of_time,
p_state_code => l_state_code,
p_county_code => l_county_code,
p_city_code => l_city_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_max_assign_end_dt, -- Bug 2535501
-- p_effective_end_date => l_end_of_time,
p_state_code => l_state_code,
p_county_code => l_county_code,
p_city_code => l_city_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_end_of_time,
p_state_code => l_ovrd_state_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_end_of_time,
p_state_code => l_ovrd_state_code,
p_county_code => l_ovrd_county_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => l_default_date,
p_effective_end_date => l_end_of_time,
p_state_code => l_ovrd_state_code,
p_county_code => l_ovrd_county_code,
p_city_code => l_ovrd_city_code,
p_business_group_id => p_business_group,
p_percent_time => 0);
/* Now update the city record and set it to 100% */
if l_state_code = l_ovrd_state_code and l_county_code = l_ovrd_county_code
and l_city_code = l_ovrd_city_code then
l_jurisdiction_code := l_state_code ||'-' || l_county_code ||'-'|| l_city_code;
an assignment. It can also be used to delete the element entries
for a given date range.
Parameters :
p_assignment_id -> The assignment for which the vertex elemnt entries are
to be modified for their start and/or end dates.
p_session_date -> The start date of the element entry.
p_new_start_date -> The new start date of the element entry.
p_new_end_date -> The new end date of the element entry.
p_mode -> 'U' -> for update
'D' -> for Delete
'F' -> FUTURE_CHANGE (for Delete)
'N' -> DELETE_NEXT_CHANGE
*/
procedure del_updt_entries_for_dates (p_assignment_id in number,
p_jurisdiction_code in varchar2,
p_session_date in date,
p_new_start_date in date,
p_new_end_date in date,
p_mode in varchar2) is
l_inp_value_id_table hr_entry.number_table;
select pet.element_type_id,
piv.input_value_id,
piv.name
from PAY_INPUT_VALUES_F piv,
PAY_ELEMENT_TYPES_F pet
where p_session_date between piv.effective_start_date
and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_name = 'VERTEX';
select pee.element_entry_id
from PAY_ELEMENT_ENTRY_VALUES_F pev,
PAY_ELEMENT_ENTRIES_F pee
where pev.screen_entry_value = p_jurisdiction_code
and pev.input_value_id + 0 = p_inp_val
and p_session_date between pev.effective_start_date
and pev.effective_end_date
and pev.element_entry_id = pee.element_entry_id
and pee.element_link_id = p_element_link
and p_session_date between pee.effective_start_date
and pee.effective_end_date
and pee.assignment_id = p_assignment_id;
/* Update Element Entries and Element Entry values as well */
if p_new_start_date is not null
then
l_step := 4;
update PAY_ELEMENT_ENTRIES_F
set effective_start_date = p_new_start_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
update PAY_ELEMENT_ENTRY_VALUES_F
set effective_start_date = p_new_start_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
update PAY_ELEMENT_ENTRIES_F
set effective_end_date = p_new_end_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
update PAY_ELEMENT_ENTRY_VALUES_F
set effective_end_date = p_new_end_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
/* Delete the element entries */
l_step := 8;
delete PAY_ELEMENT_ENTRY_VALUES_F
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
/* Delete the element entry values */
l_step := 9;
delete PAY_ELEMENT_ENTRIES_F
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
elsif p_mode = 'N' then /* Delete next change */
l_mode := 'DELETE_NEXT_CHANGE';
elsif p_mode = 'F' then /* Delete future change */
l_mode := 'FUTURE_CHANGE';
Purpose : Since we have to update the element entries of all the
jurisdictions, we can get the jurisdictions that are
valid as of the session date. from the tax rules tables.
Then for each of the jurisdiction, we will call the
del_updt_entries_for_dates to change their effective dates.
This rotuine will also be called to delete the element entries
for a specific date range.
Parameters : p_assignment_id -> The assignment id.
p_session_date -> The session date for which the element
entries have to be updated/deleted.
p_new_start_date -> The new effective start date to which
the effective start date of the records
needs to be changed.
p_new_end_date -> The new end date for the element entries
effective as of the session date.
p_mode -> 'U' -> Update
'D' -> Delete
'F' -> FUTURE_CHANGE (for Delete)
'N' -> DELETE_NEXT_CHANGE
*/
procedure upd_del_entries(p_assignment_id in number,
p_session_date in date,
p_new_start_date in date,
p_new_end_date in date,
p_mode in varchar2) is
l_state_code varchar2(2);
select state_code
from PAY_US_EMP_STATE_TAX_RULES_F str
where str.assignment_id = p_assignment_id
and p_session_date between str.effective_start_date
and str.effective_end_date;
select state_code,
county_code
from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
where ctr.assignment_id = p_assignment_id
and p_session_date between ctr.effective_start_date
and ctr.effective_end_date;
select state_code,
county_code,
city_code
from PAY_US_EMP_CITY_TAX_RULES_F ctr
where ctr.assignment_id = p_assignment_id
and p_session_date between ctr.effective_start_date
and ctr.effective_end_date;
/* Update the entries for their effective start and/or
effective end date */
l_jurisdiction_code := l_state_code || '-000-0000';
/* Update the entries for their effective start and/or
effective end date */
l_jurisdiction_code := l_state_code || '-' || l_county_code ||
'-0000';
/* Update the entries for their effective start and/or
effective end date */
l_jurisdiction_code := l_state_code || '-' || l_county_code ||
'-' || l_city_code;
can also be used to delete the workers comp. element entry
for a given date range.
Parameters :
p_assignment_id -> The assignment for which the workers
comp. element entry are to be modified
for their start and/or end dates.
p_session_date -> The start date of the element entry.
p_new_start_date -> The new start date of the element entry.
p_new_end_date -> The new end date of the element entry.
p_mode -> 'U' -> for update
'D' -> for Delete
*/
procedure del_updt_wc_entry_for_dates (p_assignment_id in number,
p_session_date in date,
p_new_start_date in date,
p_new_end_date in date,
p_mode in varchar2) is
l_element_type_id number :=0;
select pet.element_type_id
from PAY_ELEMENT_TYPES_F pet
where pet.element_name = 'Workers Compensation' -- Bug 3354060 FTS on PAY_ELEMENT_TYPES_F was removed. Done by removing
and p_session_date between pet.effective_start_date -- 'upper' from pet.element_name and 'WORKERS COMPENSATION' was changed to
and pet.effective_end_date; -- 'Workers Compensation'
select pee.element_entry_id
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = p_assignment_id
and p_session_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link;
/* Update Element Entries and Element Entry values as well */
if p_new_start_date is not null
then
l_step := 4;
update PAY_ELEMENT_ENTRIES_F
set effective_start_date = p_new_start_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
update PAY_ELEMENT_ENTRY_VALUES_F
set effective_start_date = p_new_start_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
update PAY_ELEMENT_ENTRIES_F
set effective_end_date = p_new_end_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
update PAY_ELEMENT_ENTRY_VALUES_F
set effective_end_date = p_new_end_date
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
/* Delete the element entry */
l_step := 8;
delete PAY_ELEMENT_ENTRY_VALUES_F
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
/* Delete the element entry values */
l_step := 9;
delete PAY_ELEMENT_ENTRIES_F
where element_entry_id = l_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
Purpose : To create or update the %age tax records for
all of the existing tax rules records, for a given date
range i.e. from the p_start_date till p_end_date.
When this procedure is called with the mode of 'INSERT_OLD'
the %age passed by this routine to the maintain_element_entry
routine will not be of any importance because the maintain_element_entry
routine will do an update insert with the existing %age
*/
procedure change_entries(p_assignment_id in number,
p_session_date in date,
p_start_date in date,
p_end_date in date,
p_mode in varchar2) is
l_state_code varchar2(2);
select state_code
from PAY_US_EMP_STATE_TAX_RULES_F str
where str.assignment_id = p_assignment_id
and p_session_date between
str.effective_start_date and str.effective_end_date;
select state_code,
county_code
from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
where ctr.assignment_id = p_assignment_id
and p_session_date between
ctr.effective_start_date and ctr.effective_end_date;
select state_code,
county_code,
city_code
from PAY_US_EMP_CITY_TAX_RULES_F ctr
where ctr.assignment_id = p_assignment_id
and p_session_date between
ctr.effective_start_date and ctr.effective_end_date;
select paf.location_id, paf.effective_end_date
from PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and p_next_eff_date between paf.effective_start_date
and paf.effective_end_date;
select pet.element_type_id
from PAY_ELEMENT_TYPES_F pet
where pet.element_name = 'VERTEX'
and p_session_date between pet.effective_start_date
and pet.effective_end_date;
select pee.effective_end_date
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = p_assignment_id
and p_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link
and rownum < 2;
select nvl(hsck.segment18, paf.location_id)
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;
/* Do an update insert for the existing %age records with the same %age */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => p_default_date,
p_start_date => l_validation_start_date,
p_end_date => l_next_end_date,
p_mode => 'INSERT_OLD');
/* inserting rec for all jurisdcitions for the new date
range */
zero_out_time(p_assignment_id => p_assignment_id,
p_effective_start_date => l_validation_start_date,
p_effective_end_date => l_validation_end_date);
select min(ftr.effective_start_date)
from PAY_US_EMP_FED_TAX_RULES_F ftr
where ftr.assignment_id = passignment;
select paf1.location_id,
paf1.effective_start_date,
paf1.effective_start_date - 1
from per_assignments_f paf1
where paf1.assignment_id = passignment
and paf1.effective_start_date >= p_start_date
and paf1.effective_end_date <= p_end_date
order by 2;
select pet.element_type_id
from PAY_ELEMENT_TYPES_F pet
where pet.element_name = 'VERTEX'
and p_session_date between pet.effective_start_date
and pet.effective_end_date;
select 'Y'
from pay_element_entries_f pef
where pef.assignment_id = p_assignment_id
and pef.element_link_id = p_ele_link
and pef.effective_start_date >= p_def_date
and exists (select null
from pay_element_entries_f pee
where pee.assignment_id = p_assignment_id
and pee.element_entry_id = pef.element_entry_id
and pee.effective_start_date >= p_def_date
and pee.effective_start_date <> pef.effective_start_date);
select pee.effective_start_date,
pee.effective_end_date,
pee.effective_start_date -1
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = p_assignment_id
and p_session_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link
and rownum < 2;
select paf.location_id
from PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id and
p_next_eff_date between paf.effective_start_date
and paf.effective_end_date;
select pee.effective_end_date
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = p_assignment_id
and p_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link
and rownum < 2;
select nvl(hsck.segment18, paf.location_id)
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;
select paf.location_id
from PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment and
p_prev_eff_date between paf.effective_start_date
and paf.effective_end_date;
select pee.effective_start_date
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = p_assignment
and pee.element_link_id = p_element_link
and p_date between pee.effective_start_date
and pee.effective_end_date
and rownum < 2;
select p_eff_start_date -1
into l_eff_prev_date
from dual;
select p_effective_end_date + 1
into l_pef_new_start_date
from sys.DUAL;
/* Do an update insert for the same %age as of the l_pef_new_start_date */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => l_pef_new_start_date,
p_start_date => l_pef_start_date,
p_end_date => l_pef_end_date,
p_mode => 'INSERT_OLD');
/* Do an update insert for the same %age as of the l_validation_start_date */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => l_validation_start_date,
p_start_date => l_pef_start_date,
p_end_date => l_validation_end_date,
p_mode => 'INSERT_OLD');
select l_validation_end_date + 1
into l_pef_next_date
from SYS.DUAL;
/* Do an update insert as of the l_validation_start_date */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => l_validation_start_date,
p_start_date => l_pef_start_date,
p_end_date => l_pef_end_date,
p_mode => 'UPDATE_CHANGE_INSERT');
/* Do an update as of the l_validation_start_date */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => l_validation_start_date,
p_start_date => l_pef_start_date,
p_end_date => l_pef_end_date,
p_mode => 'UPDATE');
/* Do an update insert as of the l_validation_start_date */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => l_validation_start_date,
p_start_date => l_pef_start_date,
p_end_date => l_pef_end_date,
p_mode => 'UPDATE_CHANGE_INSERT');
procedure update_percentage (p_assignment_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_session_date in date,
p_new_location_id in number,
p_business_group_id in number,
p_mode in varchar2,
p_ret_code in out nocopy number,
p_ret_text in out nocopy varchar2) is
l_validation_start_date date := null;
select pet.element_type_id
from PAY_ELEMENT_TYPES_F pet
where pet.element_name = 'VERTEX'
and p_session_date between pet.effective_start_date
and pet.effective_end_date;
select pef.effective_start_date,
pef.effective_end_date,
pef.effective_end_date + 1
from PAY_ELEMENT_ENTRIES_F pef
where pef.assignment_id = p_assignment_id
and p_session_dt between pef.effective_start_date
and pef.effective_end_date
and pef.element_link_id = p_element_link
and rownum < 2;
select paf.location_id
from PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id and
p_next_eff_date between paf.effective_start_date
and paf.effective_end_date;
select pee.effective_end_date
from PAY_ELEMENT_ENTRIES_F pee
where pee.assignment_id = p_assignment_id
and p_date between pee.effective_start_date
and pee.effective_end_date
and pee.element_link_id = p_element_link
and rownum < 2;
select nvl(hsck.segment18, paf.location_id)
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;
/* Check for Update
| Session date
V
|-------------------------------------
*/
if p_mode in ('UPDATE','UPDATE_OVERRIDE') then
l_validation_start_date := p_session_date;
/* Do an update for the element entries for all of the
existing jurisdictions of the assignment */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => p_session_date,
p_start_date => p_effective_start_date,
p_end_date => p_effective_end_date,
p_mode => p_mode);
elsif p_mode = 'UPDATE_CHANGE_INSERT' then
open csr_tax_element;
'pay_us_emp_dt_tax_rules.update_percentage');
'pay_us_emp_dt_tax_rules.update_percentage');
/* First do an update insert with the existing value of the
existing jurisdictions as of the p_effective_end_date + 1 */
select p_effective_end_date + 1
into l_new_session_date
from DUAL;
p_mode => 'INSERT_OLD');
/* Again do an update insert for the element entries for all of the
existing jurisdictions of the assignment, as of the session date */
change_entries(p_assignment_id => p_assignment_id,
p_session_date => p_session_date,
p_start_date => l_pef_start_date,
p_end_date => l_validation_end_date,
p_mode => 'UPDATE_CHANGE_INSERT');
'pay_us_emp_dt_tax_rules.update_percentage');
select l_validation_start_date - 1
into l_prev_end_date
from SYS.DUAL;
p_mode => 'UPDATE_CHANGE_INSERT');
end update_percentage;
select pus.state_code
from PAY_US_STATES pus,
HR_LOCATIONS hrl
where hrl.location_id = p_new_location_id
and pus.state_abbrev = nvl(hrl.loc_information17,hrl.region_2);
select pef.effective_start_date, pef.effective_end_date,
pef.sui_jurisdiction_code
from PAY_US_EMP_FED_TAX_RULES_F pef
where pef.assignment_id = p_assignment_id
and p_effective_start_date <= pef.effective_end_date
and p_effective_end_date >= pef.effective_start_date;
select * from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id
and effective_start_date = p_start_date
and effective_end_date = p_end_date;
select rowid
from PAY_US_EMP_FED_TAX_RULES_F
where assignment_id = p_assignment_id
and effective_start_date = p_start_date
and effective_end_date = p_end_date
for update nowait;
/* Update the federal tax record for the SUI state */
l_step := 6;
update PAY_US_EMP_FED_TAX_RULES_F
set sui_state_code = l_work_state_code,
sui_jurisdiction_code = l_work_state_code ||'-000-0000'
where rowid = l_row_id;
select p_effective_start_date -1
into l_new_date
from DUAL;
/* Update the Federal tax record as of the p_effective_start_date */
l_step := 10;
update PAY_US_EMP_FED_TAX_RULES_F
set effective_end_date = l_new_date
where assignment_id = p_assignment_id
and effective_start_date = l_eff_start_date
and effective_end_date = l_eff_end_date;
insert into PAY_US_EMP_FED_TAX_RULES_F
(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
)
values
(l_fed_rec.emp_fed_tax_rule_id,
p_effective_start_date,
l_fed_rec.effective_end_date,
l_fed_rec.assignment_id,
l_work_state_code,
l_work_state_code || '-000-0000',
l_fed_rec.business_group_id,
l_fed_rec.additional_wa_amount,
lpad(l_fed_rec.filing_status_code,2,'0'),
l_fed_rec.fit_override_amount,
l_fed_rec.fit_override_rate,
l_fed_rec.withholding_allowances,
l_fed_rec.cumulative_taxation,
l_fed_rec.eic_filing_status_code,
l_fed_rec.fit_additional_tax,
l_fed_rec.fit_exempt,
l_fed_rec.futa_tax_exempt,
l_fed_rec.medicare_tax_exempt,
l_fed_rec.ss_tax_exempt,
l_fed_rec.wage_exempt,
l_fed_rec.statutory_employee,
l_fed_rec.w2_filed_year,
l_fed_rec.supp_tax_override_rate,
l_fed_rec.excessive_wa_reject_date,
0,
l_fed_rec.attribute_category,
l_fed_rec.attribute1,
l_fed_rec.attribute2,
l_fed_rec.attribute3,
l_fed_rec.attribute4,
l_fed_rec.attribute5,
l_fed_rec.attribute6,
l_fed_rec.attribute7,
l_fed_rec.attribute8,
l_fed_rec.attribute9,
l_fed_rec.attribute10,
l_fed_rec.attribute11,
l_fed_rec.attribute12,
l_fed_rec.attribute13,
l_fed_rec.attribute14,
l_fed_rec.attribute15,
l_fed_rec.attribute16,
l_fed_rec.attribute17,
l_fed_rec.attribute18,
l_fed_rec.attribute19,
l_fed_rec.attribute20,
l_fed_rec.attribute21,
l_fed_rec.attribute22,
l_fed_rec.attribute23,
l_fed_rec.attribute24,
l_fed_rec.attribute25,
l_fed_rec.attribute26,
l_fed_rec.attribute27,
l_fed_rec.attribute28,
l_fed_rec.attribute29,
l_fed_rec.attribute30,
l_fed_rec.fed_information_category,
l_fed_rec.fed_information1,
l_fed_rec.fed_information2,
l_fed_rec.fed_information3,
l_fed_rec.fed_information4,
l_fed_rec.fed_information5,
l_fed_rec.fed_information6,
l_fed_rec.fed_information7,
l_fed_rec.fed_information8,
l_fed_rec.fed_information9,
l_fed_rec.fed_information10,
l_fed_rec.fed_information11,
l_fed_rec.fed_information12,
l_fed_rec.fed_information13,
l_fed_rec.fed_information14,
l_fed_rec.fed_information15,
l_fed_rec.fed_information16,
l_fed_rec.fed_information17,
l_fed_rec.fed_information18,
l_fed_rec.fed_information19,
l_fed_rec.fed_information20,
l_fed_rec.fed_information21,
l_fed_rec.fed_information22,
l_fed_rec.fed_information23,
l_fed_rec.fed_information24,
l_fed_rec.fed_information25,
l_fed_rec.fed_information26,
l_fed_rec.fed_information27,
l_fed_rec.fed_information28,
l_fed_rec.fed_information29,
l_fed_rec.fed_information30
);
/* Update the workers compensation for the new jurisdiction as of the
p_effective_start_date */
l_step := 12;
p_mode => 'UPDATE');
/* Update Insert the workers compensation for the new jurisdiction as of the
p_effective_start_date */
l_step := 13;
p_mode => 'UPDATE_CHANGE_INSERT');
select p_effective_end_date +1
into l_new_date
from DUAL;
/* Update the Federal tax record as of the p_effective_start_date */
l_step := 15;
insert into PAY_US_EMP_FED_TAX_RULES_F
select * from pay_us_emp_fed_tax_rules_f
where assignment_id = p_assignment_id
and effective_start_date = l_eff_start_date
and effective_end_date = l_eff_end_date;
update PAY_US_EMP_FED_TAX_RULES_F
set effective_end_date = p_effective_end_date,
sui_state_code = l_work_state_code,
sui_jurisdiction_code = l_work_state_code || '-000-0000'
where assignment_id = p_assignment_id
and effective_start_date = l_eff_start_date
and effective_end_date = l_eff_end_date
and rownum < 2;
insert into PAY_US_EMP_FED_TAX_RULES_F
(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
)
values
(l_fed_rec.emp_fed_tax_rule_id,
l_new_date,
l_fed_rec.effective_end_date,
l_fed_rec.assignment_id,
l_fed_rec.sui_state_code,
l_fed_rec.sui_jurisdiction_code,
l_fed_rec.business_group_id,
l_fed_rec.additional_wa_amount,
lpad(l_fed_rec.filing_status_code,2,'0'),
l_fed_rec.fit_override_amount,
l_fed_rec.fit_override_rate,
l_fed_rec.withholding_allowances,
l_fed_rec.cumulative_taxation,
l_fed_rec.eic_filing_status_code,
l_fed_rec.fit_additional_tax,
l_fed_rec.fit_exempt,
l_fed_rec.futa_tax_exempt,
l_fed_rec.medicare_tax_exempt,
l_fed_rec.ss_tax_exempt,
l_fed_rec.wage_exempt,
l_fed_rec.statutory_employee,
l_fed_rec.w2_filed_year,
l_fed_rec.supp_tax_override_rate,
l_fed_rec.excessive_wa_reject_date,
0,
l_fed_rec.attribute_category,
l_fed_rec.attribute1,
l_fed_rec.attribute2,
l_fed_rec.attribute3,
l_fed_rec.attribute4,
l_fed_rec.attribute5,
l_fed_rec.attribute6,
l_fed_rec.attribute7,
l_fed_rec.attribute8,
l_fed_rec.attribute9,
l_fed_rec.attribute10,
l_fed_rec.attribute11,
l_fed_rec.attribute12,
l_fed_rec.attribute13,
l_fed_rec.attribute14,
l_fed_rec.attribute15,
l_fed_rec.attribute16,
l_fed_rec.attribute17,
l_fed_rec.attribute18,
l_fed_rec.attribute19,
l_fed_rec.attribute20,
l_fed_rec.attribute21,
l_fed_rec.attribute22,
l_fed_rec.attribute23,
l_fed_rec.attribute24,
l_fed_rec.attribute25,
l_fed_rec.attribute26,
l_fed_rec.attribute27,
l_fed_rec.attribute28,
l_fed_rec.attribute29,
l_fed_rec.attribute30,
l_fed_rec.fed_information_category,
l_fed_rec.fed_information1,
l_fed_rec.fed_information2,
l_fed_rec.fed_information3,
l_fed_rec.fed_information4,
l_fed_rec.fed_information5,
l_fed_rec.fed_information6,
l_fed_rec.fed_information7,
l_fed_rec.fed_information8,
l_fed_rec.fed_information9,
l_fed_rec.fed_information10,
l_fed_rec.fed_information11,
l_fed_rec.fed_information12,
l_fed_rec.fed_information13,
l_fed_rec.fed_information14,
l_fed_rec.fed_information15,
l_fed_rec.fed_information16,
l_fed_rec.fed_information17,
l_fed_rec.fed_information18,
l_fed_rec.fed_information19,
l_fed_rec.fed_information20,
l_fed_rec.fed_information21,
l_fed_rec.fed_information22,
l_fed_rec.fed_information23,
l_fed_rec.fed_information24,
l_fed_rec.fed_information25,
l_fed_rec.fed_information26,
l_fed_rec.fed_information27,
l_fed_rec.fed_information28,
l_fed_rec.fed_information29,
l_fed_rec.fed_information30
);
/* Update the workers compensation for the old jurisdiction as of the
l_new_date */
l_step := 19;
p_mode => 'UPDATE');
/* Update Insert the workers compensation for the old jurisdiction as of the
l_new_date */
l_step := 20;
p_mode => 'UPDATE_CHANGE_INSERT');
select p_effective_end_date +1
into l_new_date
from DUAL;
/* Update the Federal tax record as of the p_effective_end_date + 1 */
l_step := 23;
update PAY_US_EMP_FED_TAX_RULES_F
set effective_end_date = p_effective_end_date
where assignment_id = p_assignment_id
and effective_start_date = l_eff_start_date
and effective_end_date = l_eff_end_date;
insert into PAY_US_EMP_FED_TAX_RULES_F
(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 )
values
(l_fed_rec.emp_fed_tax_rule_id,
l_new_date,
l_fed_rec.effective_end_date,
l_fed_rec.assignment_id,
l_fed_rec.sui_state_code,
l_fed_rec.sui_jurisdiction_code,
l_fed_rec.business_group_id,
l_fed_rec.additional_wa_amount,
lpad(l_fed_rec.filing_status_code,2,'0'),
l_fed_rec.fit_override_amount,
l_fed_rec.fit_override_rate,
l_fed_rec.withholding_allowances,
l_fed_rec.cumulative_taxation,
l_fed_rec.eic_filing_status_code,
l_fed_rec.fit_additional_tax,
l_fed_rec.fit_exempt,
l_fed_rec.futa_tax_exempt,
l_fed_rec.medicare_tax_exempt,
l_fed_rec.ss_tax_exempt,
l_fed_rec.wage_exempt,
l_fed_rec.statutory_employee,
l_fed_rec.w2_filed_year,
l_fed_rec.supp_tax_override_rate,
l_fed_rec.excessive_wa_reject_date,
0,
l_fed_rec.attribute_category,
l_fed_rec.attribute1,
l_fed_rec.attribute2,
l_fed_rec.attribute3,
l_fed_rec.attribute4,
l_fed_rec.attribute5,
l_fed_rec.attribute6,
l_fed_rec.attribute7,
l_fed_rec.attribute8,
l_fed_rec.attribute9,
l_fed_rec.attribute10,
l_fed_rec.attribute11,
l_fed_rec.attribute12,
l_fed_rec.attribute13,
l_fed_rec.attribute14,
l_fed_rec.attribute15,
l_fed_rec.attribute16,
l_fed_rec.attribute17,
l_fed_rec.attribute18,
l_fed_rec.attribute19,
l_fed_rec.attribute20,
l_fed_rec.attribute21,
l_fed_rec.attribute22,
l_fed_rec.attribute23,
l_fed_rec.attribute24,
l_fed_rec.attribute25,
l_fed_rec.attribute26,
l_fed_rec.attribute27,
l_fed_rec.attribute28,
l_fed_rec.attribute29,
l_fed_rec.attribute30,
l_fed_rec.fed_information_category,
l_fed_rec.fed_information1,
l_fed_rec.fed_information2,
l_fed_rec.fed_information3,
l_fed_rec.fed_information4,
l_fed_rec.fed_information5,
l_fed_rec.fed_information6,
l_fed_rec.fed_information7,
l_fed_rec.fed_information8,
l_fed_rec.fed_information9,
l_fed_rec.fed_information10,
l_fed_rec.fed_information11,
l_fed_rec.fed_information12,
l_fed_rec.fed_information13,
l_fed_rec.fed_information14,
l_fed_rec.fed_information15,
l_fed_rec.fed_information16,
l_fed_rec.fed_information17,
l_fed_rec.fed_information18,
l_fed_rec.fed_information19,
l_fed_rec.fed_information20,
l_fed_rec.fed_information21,
l_fed_rec.fed_information22,
l_fed_rec.fed_information23,
l_fed_rec.fed_information24,
l_fed_rec.fed_information25,
l_fed_rec.fed_information26,
l_fed_rec.fed_information27,
l_fed_rec.fed_information28,
l_fed_rec.fed_information29,
l_fed_rec.fed_information30
);
/* Update the workers compensation for the old jurisdiction as of the
l_new_date */
l_step := 26;
p_mode => 'UPDATE');
/* Update Insert the workers compensation for the old jurisdiction as of the
l_new_date */
l_step := 27;
p_mode => 'UPDATE_CHANGE_INSERT');
select p_effective_start_date -1
into l_new_date
from DUAL;
/* Update the Federal tax record as of the p_effective_start_date */
l_step := 30;
update PAY_US_EMP_FED_TAX_RULES_F
set effective_end_date = l_new_date
where assignment_id = p_assignment_id
and effective_start_date = l_eff_start_date
and effective_end_date = p_effective_end_date;
insert into PAY_US_EMP_FED_TAX_RULES_F
(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 )
values
(l_fed_rec.emp_fed_tax_rule_id,
p_effective_start_date,
p_effective_end_date,
l_fed_rec.assignment_id,
l_work_state_code,
l_work_state_code || '-000-0000',
l_fed_rec.business_group_id,
l_fed_rec.additional_wa_amount,
lpad(l_fed_rec.filing_status_code,2,'0'),
l_fed_rec.fit_override_amount,
l_fed_rec.fit_override_rate,
l_fed_rec.withholding_allowances,
l_fed_rec.cumulative_taxation,
l_fed_rec.eic_filing_status_code,
l_fed_rec.fit_additional_tax,
l_fed_rec.fit_exempt,
l_fed_rec.futa_tax_exempt,
l_fed_rec.medicare_tax_exempt,
l_fed_rec.ss_tax_exempt,
l_fed_rec.wage_exempt,
l_fed_rec.statutory_employee,
l_fed_rec.w2_filed_year,
l_fed_rec.supp_tax_override_rate,
l_fed_rec.excessive_wa_reject_date,
0,
l_fed_rec.attribute_category,
l_fed_rec.attribute1,
l_fed_rec.attribute2,
l_fed_rec.attribute3,
l_fed_rec.attribute4,
l_fed_rec.attribute5,
l_fed_rec.attribute6,
l_fed_rec.attribute7,
l_fed_rec.attribute8,
l_fed_rec.attribute9,
l_fed_rec.attribute10,
l_fed_rec.attribute11,
l_fed_rec.attribute12,
l_fed_rec.attribute13,
l_fed_rec.attribute14,
l_fed_rec.attribute15,
l_fed_rec.attribute16,
l_fed_rec.attribute17,
l_fed_rec.attribute18,
l_fed_rec.attribute19,
l_fed_rec.attribute20,
l_fed_rec.attribute21,
l_fed_rec.attribute22,
l_fed_rec.attribute23,
l_fed_rec.attribute24,
l_fed_rec.attribute25,
l_fed_rec.attribute26,
l_fed_rec.attribute27,
l_fed_rec.attribute28,
l_fed_rec.attribute29,
l_fed_rec.attribute30,
l_fed_rec.fed_information_category,
l_fed_rec.fed_information1,
l_fed_rec.fed_information2,
l_fed_rec.fed_information3,
l_fed_rec.fed_information4,
l_fed_rec.fed_information5,
l_fed_rec.fed_information6,
l_fed_rec.fed_information7,
l_fed_rec.fed_information8,
l_fed_rec.fed_information9,
l_fed_rec.fed_information10,
l_fed_rec.fed_information11,
l_fed_rec.fed_information12,
l_fed_rec.fed_information13,
l_fed_rec.fed_information14,
l_fed_rec.fed_information15,
l_fed_rec.fed_information16,
l_fed_rec.fed_information17,
l_fed_rec.fed_information18,
l_fed_rec.fed_information19,
l_fed_rec.fed_information20,
l_fed_rec.fed_information21,
l_fed_rec.fed_information22,
l_fed_rec.fed_information23,
l_fed_rec.fed_information24,
l_fed_rec.fed_information25,
l_fed_rec.fed_information26,
l_fed_rec.fed_information27,
l_fed_rec.fed_information28,
l_fed_rec.fed_information29,
l_fed_rec.fed_information30
);
/* Update Insert the workers compensation for the new jurisdiction as of the
p_effective_start_date */
l_step := 32;
p_mode => 'UPDATE_CHANGE_INSERT');
elsif p_mode = 'UPDATE' then
l_validation_start_date := p_session_date;
elsif p_mode = 'UPDATE_CHANGE_INSERT' then
l_validation_start_date := p_session_date;
elsif p_mode = 'UPDATE_OVERRIDE' then
l_validation_start_date := p_session_date;
/* First update the tax rules records */
update PAY_US_EMP_FED_TAX_RULES_F
set effective_start_date = p_new_start_date
where assignment_id = p_assignment_id
and l_ef_date between effective_start_date and effective_end_date;
update PAY_US_EMP_STATE_TAX_RULES_F
set effective_start_date = p_new_start_date
where assignment_id = p_assignment_id
and l_ef_date between effective_start_date and effective_end_date;
update PAY_US_EMP_COUNTY_TAX_RULES_F
set effective_start_date = p_new_start_date
where assignment_id = p_assignment_id
and l_ef_date between effective_start_date and effective_end_date;
update PAY_US_EMP_CITY_TAX_RULES_F
set effective_start_date = p_new_start_date
where assignment_id = p_assignment_id
and l_ef_date between effective_start_date and effective_end_date;
/* Next delete any orphaned rows */
if p_new_start_date > p_default_date then
hr_utility.set_location(l_proc, 10);
delete PAY_US_EMP_FED_TAX_RULES_F
where assignment_id = p_assignment_id
and p_new_start_date > effective_start_date;
delete PAY_US_EMP_STATE_TAX_RULES_F
where assignment_id = p_assignment_id
and p_new_start_date > effective_start_date;
delete PAY_US_EMP_COUNTY_TAX_RULES_F
where assignment_id = p_assignment_id
and p_new_start_date > effective_start_date;
delete PAY_US_EMP_CITY_TAX_RULES_F
where assignment_id = p_assignment_id
and p_new_start_date > effective_start_date;
/* Now time to update the workers comp element entry */
del_updt_wc_entry_for_dates (p_assignment_id => p_assignment_id,
p_session_date => p_default_date,
p_new_start_date => p_s_start_date,
p_new_end_date => null,
p_mode => 'U');
/* Finally update the vertex element entries and close the
chapter */
upd_del_entries(p_assignment_id => p_assignment_id,
p_session_date => p_default_date,
p_new_start_date => p_s_start_date,
p_new_end_date => null,
p_mode => 'U');
select pus.state_code,
puc.county_code,
pcn.city_code
from pay_us_city_names pcn,
pay_us_counties puc,
pay_us_states pus,
per_addresses pa,
per_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_effective_start_date between paf.effective_start_date and
paf.effective_end_date
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
and p_effective_start_date between pa.date_from and
nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
and pa.add_information17 is not null
and pa.add_information19 is not null
and pa.add_information18 is not null
and pa.add_information17 = pus.state_abbrev
and puc.state_code = pus.state_code
and puc.county_name = pa.add_information19
and pcn.state_code = puc.state_code
and pcn.county_code = puc.county_code
and pcn.city_name = add_information18;
/* Insert the default Federal tax Record */
l_fed_tax_rule_id :=
insert_def_fed_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_sui_state_code => l_sui_state_code,
p_business_group_id => p_business_group_id);
/* Insert the default State tax record */
/* Create state record for works and if needed resident state rec also */
l_state_tax_rule_id :=
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work_state_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work1_state_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work2_state_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work3_state_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_res_state_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_state_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_add_state_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
/* Insert the default county tax record */
l_county_tax_rule_id :=
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work_state_code,
p_county_code => l_work_county_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work1_state_code,
p_county_code => l_work1_county_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work2_state_code,
p_county_code => l_work2_county_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work3_state_code,
p_county_code => l_work3_county_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_res_state_code,
p_county_code => l_res_county_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_county_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_add_state_code,
p_county_code => l_add_county_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
/* Insert the default city tax record */
if l_loc_city = l_work_state_code ||'-'||l_work_county_code ||'-'||l_work_city_code
then
l_percent := 100;
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work_state_code,
p_county_code => l_work_county_code,
p_city_code => l_work_city_code,
p_business_group_id => p_business_group_id,
p_percent_time => l_percent);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work1_state_code,
p_county_code => l_work1_county_code,
p_city_code => l_work1_city_code,
p_business_group_id => p_business_group_id,
p_percent_time => l_percent);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work2_state_code,
p_county_code => l_work2_county_code,
p_city_code => l_work2_city_code,
p_business_group_id => p_business_group_id,
p_percent_time => l_percent);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_work3_state_code,
p_county_code => l_work3_county_code,
p_city_code => l_work3_city_code,
p_business_group_id => p_business_group_id,
p_percent_time => l_percent);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_res_state_code,
p_county_code => l_res_county_code,
p_city_code => l_res_city_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
insert_def_city_rec(p_assignment_id => p_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_state_code => l_add_state_code,
p_county_code => l_add_county_code,
p_city_code => l_add_city_code,
p_business_group_id => p_business_group_id,
p_percent_time => 0);
select 'Y'
from DUAL
where exists ( select null
from PAY_US_EMP_FED_TAX_RULES_F ftr
where ftr.assignment_id = p_assignment_id);
select max(effective_end_date)
from PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id;
select paf.assignment_id, min(paf.effective_start_date)
from per_addresses pa,
hr_soft_coding_keyflex hsck,
per_assignments_f paf
where paf.person_id = p_person
and paf.assignment_type = 'E'
and paf.soft_coding_keyflex_id is not null
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.location_id is not null
and paf.payroll_id is not null
and paf.pay_basis_id is not null
and pa.person_id = paf.person_id
-- and pa.primary_flag = 'Y'
and (paf.effective_start_date between
pa.date_from and nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
or pa.date_from between paf.effective_start_date and paf.effective_end_date)
group by assignment_id;
select paf.assignment_id, min(paf.effective_start_date)
from per_addresses pa,
hr_soft_coding_keyflex hsck,
per_assignments_f paf
where paf.person_id = p_person
and paf.assignment_type = 'E'
and paf.soft_coding_keyflex_id is not null
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.location_id is not null
and paf.payroll_id is not null
and paf.pay_basis_id is not null
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
-- and (paf.effective_end_date = to_date('12/31/4712','MM/DD/YYYY')
--Added for bug 2535501 June 10, 2003 except for the group by
-- p_effective_start_date is the p_date_from in the Address table
and paf.effective_end_date >= p_effective_start_date
group by assignment_id ;
select paf.assignment_id, min(paf.effective_start_date)
from per_addresses pa,
hr_soft_coding_keyflex hsck,
per_assignments_f paf
where paf.person_id = p_person
and paf.assignment_type = 'E'
and paf.soft_coding_keyflex_id is not null
and paf.effective_end_date = to_date('12/31/4712','MM/DD/YYYY')
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.location_id is not null
and paf.payroll_id is not null
and paf.pay_basis_id is not null
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
group by assignment_id ;
select 1
from per_addresses pa,
per_people_f ppf,
hr_soft_coding_keyflex hsck,
per_assignments_f paf
where paf.assignment_id = p_assignment
and p_session_date between paf.effective_start_date
and paf.effective_end_date
and paf.soft_coding_keyflex_id is not null
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.location_id is not null
and paf.payroll_id is not null
and paf.pay_basis_id is not null
and ppf.person_id = paf.person_id
and pa.person_id = ppf.person_id
and pa.primary_flag = 'Y';
select min(paf.effective_start_date)
from per_addresses pa,
per_people_f ppf,
hr_soft_coding_keyflex hsck,
per_assignments_f paf
where paf.assignment_id = p_assignment
and paf.soft_coding_keyflex_id is not null
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.location_id is not null
and paf.payroll_id is not null
and paf.pay_basis_id is not null
and ppf.person_id = paf.person_id
and pa.person_id = ppf.person_id
and pa.primary_flag = 'Y';
select min(effective_start_date)
from PAY_US_EMP_FED_TAX_RULES_F pef
where pef.assignment_id = p_assignment;
select effective_end_date
from PAY_US_EMP_FED_TAX_RULES_F pef
where pef.assignment_id = p_assignment
and pef.effective_start_date = p_default_date;
select pus.state_code,
puc.county_code,
pcn.city_code
from pay_us_city_names pcn,
pay_us_counties puc,
pay_us_states pus,
per_addresses pa,
per_assignments_f paf
where paf.assignment_id = p_assignment
and p_effective_start_date between paf.effective_start_date and
paf.effective_end_date
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
and p_effective_start_date between pa.date_from and
nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
and pa.add_information17 is not null
and pa.add_information19 is not null
and pa.add_information18 is not null
and pa.add_information17 = pus.state_abbrev
and puc.state_code = pus.state_code
and puc.county_name = pa.add_information19
and pcn.state_code = puc.state_code
and pcn.county_code = puc.county_code
and pcn.city_name = add_information18;
select pus.state_code,
puc.county_code,
pcn.city_code
from pay_us_city_names pcn,
pay_us_counties puc,
pay_us_states pus,
per_addresses pa,
per_assignments_f paf
where paf.assignment_id = p_assignment
and p_effective_start_date between paf.effective_start_date and
paf.effective_end_date
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
and p_effective_start_date between pa.date_from and
nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
and pa.add_information17 is not null
and pa.add_information19 is not null
and pa.add_information18 is not null
and pa.add_information17 = pus.state_abbrev
and puc.state_code = pus.state_code
and puc.county_name = pa.add_information19
and pcn.state_code = puc.state_code
and pcn.county_code = puc.county_code
and pcn.city_name = add_information18;
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 min(paf.effective_start_date) - 1
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.effective_start_date > p_ef_date
and paf.location_id <> p_loc_id;
select max(paf.effective_end_date) + 1
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.effective_end_date < p_ef_date
and paf.location_id <> p_loc_id;
select *
from pay_us_emp_fed_tax_rules_f ftr
where ftr.assignment_id = p_assignment_id
and ftr.effective_start_date <= p_max_date
and ftr.effective_end_date >= p_min_date;
select pus.state_code
from pay_us_states pus,
hr_locations hl
where hl.location_id = p_location_id
and pus.state_abbrev = nvl(loc_information17,region_2);
-- call the procedure which does the update
IF hr_utility.chk_product_install(p_product =>'Oracle Payroll',
p_legislation => 'US')
then
if p_assignment_id is not null and p_session_date is not null
then
set_sui_wage_base_override(p_assignment_id,
null,
p_session_date) ;
select p_effective_end_date + 1
into l_next_date
from SYS.DUAL;
/* First update the tax rules records */
pull_tax_records(p_assignment_id => l_assignment_id,
p_new_start_date => p_effective_start_date,
p_default_date => l_default_date);
elsif p_mode in ('UPDATE','UPDATE_OVERRIDE','UPDATE_CHANGE_INSERT') then
/* Update the federal tax record and the worker's comp element entry for
the new SUI Jurisdiction code and SUI state */
change_wc_entry (p_assignment_id => l_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_session_date => p_session_date,
p_new_location_id => l_loc_id,
p_mode => p_mode,
p_ret_code => l_ret_code,
p_ret_text => l_ret_text);
/* Change the %age records for the type of update in the
location of the assignment */
update_percentage (p_assignment_id => l_assignment_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_session_date => p_session_date,
p_new_location_id => p_location_id,
p_business_group_id => p_business_group_id,
p_mode => p_mode,
p_ret_code => l_ret_code,
p_ret_text => l_ret_text);
elsif p_mode = 'DELETE_NEXT_CHANGE' then
/* In case of DELETE_NEXT_CHANGE, if the next location is different from
the current location then the assignment screen will error it out.
If the next location is same as the current location then :
| Session Date
L1 v L1
Asg. |--------------------|-------------------------
Tax Rules |-------------------------
Tax %age |-------------------------
In this scenario, the assignment routine deletes the tax %age records
but does not delete the tax rules records. So, our tax routine will have
to delete the tax rules records.
| Session Date
T1 v T2 T3 T4 T5
L1 L1 L1 L1 L1
Asg. |---------|---------|--------|-------|---------
Tax Rules |------------------------------------
Tax %age |------------------------------------
Here, the tax rules and the tax %age records will have to be pulled forward to
time T3.
| Session Date
L1 L1 v L1 L2 L3
Asg. |---------|---------|--------|-------|---------
Tax Rules |------------------------------------
Tax %age |------------------|-------|---------
In the above scenario, the assignment routine will only delete the next
assignment record and will not do anything to the tax %age records, which
is fine and that's how it should be.
| Session Date
L1 L1 L1 L3 v L3
Asg. |---------|---------|--------|-------|---------
Tax Rules |------------------------------------
Tax %age |------------------|-----------------
Here also, we do not need to do anything as the %age records do not get affected
by the deletion of the assignment record. */
open csr_get_end_date(p_assignment_id,l_default_date);
/* Delete records from PAY_US_EMP_CITY_TAX_RULES_F */
delete PAY_US_EMP_CITY_TAX_RULES_F
where assignment_id = p_assignment_id
and business_group_id = p_business_group_id;
/* Delete records from PAY_US_EMP_COUNTY_TAX_RULES_F */
delete PAY_US_EMP_COUNTY_TAX_RULES_F
where assignment_id = p_assignment_id
and business_group_id = p_business_group_id;
/* Delete records from PAY_US_EMP_STATE_TAX_RULES_F */
delete PAY_US_EMP_STATE_TAX_RULES_F
where assignment_id = p_assignment_id
and business_group_id = p_business_group_id;
/* Delete records from PAY_US_EMP_FED_TAX_RULES_F */
delete PAY_US_EMP_FED_TAX_RULES_F
where assignment_id = p_assignment_id
and business_group_id = p_business_group_id;
select l_default_date + 1
into l_next_start_date
from DUAL;
/* Delete the next set of %age records */
upd_del_entries(p_assignment_id => l_assignment_id,
p_session_date => p_session_date,
p_new_start_date => null,
p_new_end_date => null,
p_mode => 'F');
end if; /* for correction/update/delete */
/* we update all records that partially fall within that date range */
for tax_rec in csr_fed_tax_loc(p_assignment_id,l_loc_min_date,l_loc_max_date) loop
if tax_rec.effective_start_date < l_loc_min_date then
/* we go from:
ASG --------------|-------L1-----------------
TAX ----------|------------------------------
to:
ASG --------------|-------L1-----------------
TAX ----------|---|--------------------------
*/
hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',120);
/* insert the middle record */
insert_fed_tax_row(
tax_rec.emp_fed_tax_rule_id,
l_loc_min_date,
tax_rec.effective_end_date,
tax_rec.assignment_id,
l_loc_state_code,
l_loc_state_code || '-000-0000',
tax_rec.business_group_id,
tax_rec.additional_wa_amount,
tax_rec.filing_status_code,
tax_rec.fit_override_amount,
tax_rec.fit_override_rate,
tax_rec.withholding_allowances,
tax_rec.cumulative_taxation,
tax_rec.eic_filing_status_code,
tax_rec.fit_additional_tax,
tax_rec.fit_exempt,
tax_rec.futa_tax_exempt,
tax_rec.medicare_tax_exempt,
tax_rec.ss_tax_exempt,
tax_rec.wage_exempt,
tax_rec.statutory_employee,
tax_rec.w2_filed_year,
tax_rec.supp_tax_override_rate,
tax_rec.excessive_wa_reject_date,
tax_rec.attribute_category,
tax_rec.attribute1,
tax_rec.attribute2,
tax_rec.attribute3,
tax_rec.attribute4,
tax_rec.attribute5,
tax_rec.attribute6,
tax_rec.attribute7,
tax_rec.attribute8,
tax_rec.attribute9,
tax_rec.attribute10,
tax_rec.attribute11,
tax_rec.attribute12,
tax_rec.attribute13,
tax_rec.attribute14,
tax_rec.attribute15,
tax_rec.attribute16,
tax_rec.attribute17,
tax_rec.attribute18,
tax_rec.attribute19,
tax_rec.attribute20,
tax_rec.attribute21,
tax_rec.attribute22,
tax_rec.attribute23,
tax_rec.attribute24,
tax_rec.attribute25,
tax_rec.attribute26,
tax_rec.attribute27,
tax_rec.attribute28,
tax_rec.attribute29,
tax_rec.attribute30,
tax_rec.fed_information_category,
tax_rec.fed_information1,
tax_rec.fed_information2,
tax_rec.fed_information3,
tax_rec.fed_information4,
tax_rec.fed_information5,
tax_rec.fed_information6,
tax_rec.fed_information7,
tax_rec.fed_information8,
tax_rec.fed_information9,
tax_rec.fed_information10,
tax_rec.fed_information11,
tax_rec.fed_information12,
tax_rec.fed_information13,
tax_rec.fed_information14,
tax_rec.fed_information15,
tax_rec.fed_information16,
tax_rec.fed_information17,
tax_rec.fed_information18,
tax_rec.fed_information19,
tax_rec.fed_information20,
tax_rec.fed_information21,
tax_rec.fed_information22,
tax_rec.fed_information23,
tax_rec.fed_information24,
tax_rec.fed_information25,
tax_rec.fed_information26,
tax_rec.fed_information27,
tax_rec.fed_information28,
tax_rec.fed_information29,
tax_rec.fed_information30,
'UPDATE');
/* here we simply update the sui codes */
hr_utility.set_location('pay_us_emp_dt_tax_rules.validate_default',125);
update pay_us_emp_fed_tax_rules_f
set sui_state_code = l_loc_state_code,
sui_jurisdiction_code = l_loc_state_code || '-000-0000'
where emp_fed_tax_rule_id = tax_rec.emp_fed_tax_rule_id
and effective_start_date = tax_rec.effective_start_date;
insert_fed_tax_row(
tax_rec.emp_fed_tax_rule_id,
l_loc_max_date + 1,
tax_rec.effective_end_date,
tax_rec.assignment_id,
tax_rec.sui_state_code,
tax_rec.sui_jurisdiction_code,
tax_rec.business_group_id,
tax_rec.additional_wa_amount,
tax_rec.filing_status_code,
tax_rec.fit_override_amount,
tax_rec.fit_override_rate,
tax_rec.withholding_allowances,
tax_rec.cumulative_taxation,
tax_rec.eic_filing_status_code,
tax_rec.fit_additional_tax,
tax_rec.fit_exempt,
tax_rec.futa_tax_exempt,
tax_rec.medicare_tax_exempt,
tax_rec.ss_tax_exempt,
tax_rec.wage_exempt,
tax_rec.statutory_employee,
tax_rec.w2_filed_year,
tax_rec.supp_tax_override_rate,
tax_rec.excessive_wa_reject_date,
tax_rec.attribute_category,
tax_rec.attribute1,
tax_rec.attribute2,
tax_rec.attribute3,
tax_rec.attribute4,
tax_rec.attribute5,
tax_rec.attribute6,
tax_rec.attribute7,
tax_rec.attribute8,
tax_rec.attribute9,
tax_rec.attribute10,
tax_rec.attribute11,
tax_rec.attribute12,
tax_rec.attribute13,
tax_rec.attribute14,
tax_rec.attribute15,
tax_rec.attribute16,
tax_rec.attribute17,
tax_rec.attribute18,
tax_rec.attribute19,
tax_rec.attribute20,
tax_rec.attribute21,
tax_rec.attribute22,
tax_rec.attribute23,
tax_rec.attribute24,
tax_rec.attribute25,
tax_rec.attribute26,
tax_rec.attribute27,
tax_rec.attribute28,
tax_rec.attribute29,
tax_rec.attribute30,
tax_rec.fed_information_category,
tax_rec.fed_information1,
tax_rec.fed_information2,
tax_rec.fed_information3,
tax_rec.fed_information4,
tax_rec.fed_information5,
tax_rec.fed_information6,
tax_rec.fed_information7,
tax_rec.fed_information8,
tax_rec.fed_information9,
tax_rec.fed_information10,
tax_rec.fed_information11,
tax_rec.fed_information12,
tax_rec.fed_information13,
tax_rec.fed_information14,
tax_rec.fed_information15,
tax_rec.fed_information16,
tax_rec.fed_information17,
tax_rec.fed_information18,
tax_rec.fed_information19,
tax_rec.fed_information20,
tax_rec.fed_information21,
tax_rec.fed_information22,
tax_rec.fed_information23,
tax_rec.fed_information24,
tax_rec.fed_information25,
tax_rec.fed_information26,
tax_rec.fed_information27,
tax_rec.fed_information28,
tax_rec.fed_information29,
tax_rec.fed_information30,
'UPDATE');
select pei_information1,pei_information2
from per_people_extra_info where person_id=(select distinct person_id from per_all_assignments_f
where assignment_id=p_assignment_id
and primary_flag='Y')
and information_type like 'PER_US_ADDITIONAL_DETAILS'
and pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
and (pei_information1 = 'Y'
or pei_information2 = 'Y');
select information_type,pei_information_category,pei_information5,pei_information9
from per_people_extra_info where person_id=(select distinct person_id from per_all_assignments_f
where assignment_id=p_assignment_id
and primary_flag='Y')
and information_type like 'PER_US_ADDITIONAL_DETAILS'
and pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
and pei_information5 like 'N'
and pei_information9 not in ('US');