[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_tax_rule
(p_validate in boolean default false
,p_assignment_id in number
,p_state_code in varchar2
,p_county_code in varchar2 default '000'
,p_city_code in varchar2 default '0000'
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2 default 'ZAP'
,p_delete_routine in varchar2 default null
) is
--
-- Declare types, cursors and local variables
--
TYPE gen_rec is RECORD
(
state_code pay_us_emp_city_tax_rules_f.state_code%TYPE,
county_code pay_us_emp_city_tax_rules_f.state_code%TYPE,
city_code pay_us_emp_city_tax_rules_f.state_code%TYPE,
object_version_number number
);
select emp_city_tax_rule_id, object_version_number,
effective_start_date, effective_end_date
from pay_us_emp_city_tax_rules_f
where assignment_id = p_assignment_id
and jurisdiction_code = l_jurisdiction
and p_effective_date between effective_start_date
and effective_end_date;
select emp_county_tax_rule_id, object_version_number,
effective_start_date, effective_end_date
from pay_us_emp_county_tax_rules_f
where assignment_id = p_assignment_id
and jurisdiction_code = l_jurisdiction
and p_effective_date between effective_start_date
and effective_end_date;
select emp_state_tax_rule_id, object_version_number,
effective_start_date, effective_end_date
from pay_us_emp_state_tax_rules_f
where assignment_id = p_assignment_id
and jurisdiction_code = l_jurisdiction
and p_effective_date between effective_start_date
and effective_end_date;
select state_code, county_code,
city_code, object_version_number
from 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 <> '0000'
and city_code is not null)
and p_effective_date
between effective_start_date
and effective_end_date;
select state_code, county_code,
'0000' city_code, object_version_number
from pay_us_emp_county_tax_rules_f
where assignment_id = p_assignment_id
and state_code = p_state_code
and (county_code <> '000'
and county_code is not null)
and p_effective_date
between effective_start_date
and effective_end_date;
select count(*)
from pay_element_entries_f peef,
pay_element_entry_values_f peevf,
pay_element_types_f petf,
pay_element_links_f pelf
where peef.assignment_id=p_assignment_id
and p_effective_date < peef.effective_end_date
and petf.element_name='VERTEX'
and pelf.element_type_id=petf.element_type_id
and peef.element_link_id=pelf.element_link_id
and peevf.screen_entry_value = p_jurisdiction
and p_effective_date < peevf.effective_end_date
and peevf.element_entry_id = peef.element_entry_id;
l_proc varchar2(72) := g_package||'delete_tax_rule';
l_delete_jurisdiction number;
savepoint delete_tax_rule;
l_delete_jurisdiction := CITY_JURISDICTION;
l_delete_jurisdiction := COUNTY_JURISDICTION;
delete_tax_rule
(
p_validate => FALSE
,p_assignment_id => p_assignment_id
,p_state_code => l_jurisdiction_rec.state_code
,p_county_code => l_jurisdiction_rec.county_code
,p_city_code => l_jurisdiction_rec.city_code
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_jurisdiction_rec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_delete_routine => p_delete_routine
);
l_delete_jurisdiction := STATE_JURISDICTION;
delete_tax_rule
(
p_validate => FALSE
,p_assignment_id => p_assignment_id
,p_state_code => l_jurisdiction_rec.state_code
,p_county_code => l_jurisdiction_rec.county_code
,p_city_code => l_jurisdiction_rec.city_code
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_jurisdiction_rec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_delete_routine => p_delete_routine
);
if l_delete_jurisdiction = CITY_JURISDICTION then
--
--
--
open get_city_tax_rule_id(l_jurisdiction_code);
,p_delete_routine => p_delete_routine
);
elsif l_delete_jurisdiction = COUNTY_JURISDICTION then
--
--
--
open get_county_tax_rule_id(l_jurisdiction_code);
,p_delete_routine => p_delete_routine
);
elsif l_delete_jurisdiction = STATE_JURISDICTION then
--
--
--
open get_state_tax_rule_id(l_jurisdiction_code);
,p_delete_routine => p_delete_routine
);
if p_datetrack_mode = 'DELETE' then
l_effective_date := p_effective_date;
ROLLBACK TO delete_tax_rule;
ROLLBACK TO delete_tax_rule;
end delete_tax_rule;
lv_update_method VARCHAR2(30);
lv_update_error_msg VARCHAR2(10000);
select business_group_id
from per_people_f
where person_id = p_person_id;
select ftr.emp_fed_tax_rule_id,
ftr.object_version_number,
ftr.effective_start_date,
paf.assignment_id,
hsck.segment1
from pay_us_emp_fed_tax_rules_f ftr, per_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.person_id = p_person_id
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and paf.assignment_id = ftr.assignment_id
and paf.assignment_type = 'E'
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and p_effective_date between ftr.effective_start_date and ftr.effective_end_date
and not exists( select 'x'
from hr_organization_information hoi,
hr_soft_coding_keyflex sck
where paf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = hoi.organization_id
and hoi.org_information_context = '1099R Magnetic Report Rules')
for update nowait;
select str.emp_state_tax_rule_id,
str.object_version_number,
str.effective_start_date,
pus.state_abbrev,
pus.state_code,
paf.assignment_id,
stif.sta_information7,
hsck.segment1
from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
pay_us_state_tax_info_f stif, pay_us_states pus,
hr_soft_coding_keyflex hsck
where paf.person_id = p_person_id
and paf.assignment_id = str.assignment_id
and paf.assignment_type = 'E'
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and str.state_code = stif.state_code
and str.state_code = pus.state_code
and stif.sta_information7 like 'Y%'
and p_effective_date between stif.effective_start_date and stif.effective_end_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and p_effective_date between str.effective_start_date and str.effective_end_date
and not exists( select 'x'
from hr_organization_information hoi,
hr_soft_coding_keyflex sck
where paf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and sck.segment1 = hoi.organization_id
and hoi.org_information_context = '1099R Magnetic Report Rules')
for update nowait;
-- get the update method
lv_update_method := 'PRIMARY';
if lv_update_method = 'PRIMARY' then
l_primary_only := 'Y';
-- make sure we can update
lv_update_error_msg := chk_w4_allowed(p_person_id => p_person_id,
p_effective_date => p_effective_date,
p_source_name => p_source_name
);
if lv_update_error_msg is not null then
hr_utility.set_message(801, lv_update_error_msg);
-- We insert using datetrack mode of UPDATE
-- future dated records will cause an error
-- if the old start date = p_ef_date, we perform a correction instead
if ld_old_start_date = p_effective_date then
lv_datetrack_mode := 'CORRECTION';
lv_datetrack_mode := 'UPDATE';
pay_federal_tax_rule_api.update_fed_tax_rule
(p_emp_fed_tax_rule_id => ln_fed_tax_rule_id
,p_withholding_allowances => p_withholding_allowances
,p_fit_additional_tax => p_fit_additional_tax
,p_filing_status_code => p_filing_status_code
,p_fit_exempt => p_fit_exempt
,p_object_version_number => ln_ovn
,p_effective_start_date => ld_start_date
,p_effective_end_date => ld_end_date
,p_effective_date => p_effective_date
,p_datetrack_update_mode => lv_datetrack_mode
);
-- we insert a row into the transaction table to show the change to this assignment
pay_aud_ins.ins(
p_effective_date => p_effective_date
,p_transaction_type => lv_trans_type
,p_transaction_date => trunc(sysdate)
,p_transaction_effective_date => p_effective_date
,p_business_group_id => ln_business_group_id
,p_transaction_subtype => lv_trans_subtype
,p_person_id => p_person_id
,p_assignment_id => ln_assignment_id
,p_source1 => '00-000-0000'
,p_source1_type => 'JURISDICTION'
,p_source2 => fnd_number.number_to_canonical(ln_gre_id)
,p_source2_type => 'GRE'
,p_source3 => p_source_name
,p_source3_type => 'SOURCE_NAME'
,p_transaction_parent_id => ln_parent_audit_id
,p_stat_trans_audit_id => ln_dummy
,p_object_version_number => ln_ovn
);
-- next we update state tax records
-- we don't update the amount withheld, because it is probably of a different magnitude
-- then the state taxes.
hr_utility.set_location(l_proc, 40);
lv_datetrack_mode := 'UPDATE';
-- We need to test whether or not the state being updated has a filing status
-- that we are filing. We do this by validating it in the state api. If it fails
-- validation, we default to single.
-- Also, if the fed type is '03', we change it to '04' for the states
if p_filing_status_code = '03' then
lv_state_filing_status_code := '04';
pay_state_tax_rule_api.update_state_tax_rule
(p_emp_state_tax_rule_id => c_state_rec.emp_state_tax_rule_id
,p_withholding_allowances => p_withholding_allowances
,p_sit_additional_tax => 0
,p_filing_status_code => lv_state_filing_status_code
,p_sit_exempt => p_fit_exempt
,p_object_version_number => c_state_rec.object_version_number
,p_effective_start_date => ld_start_date
,p_effective_end_date => ld_end_date
,p_effective_date => p_effective_date
,p_datetrack_update_mode => lv_datetrack_mode
);
-- when we insert into the transaction audit table, we only show
-- where the child record is different from the parent record
-- therefore, if state filing status <> fed filing status we
-- store it, otherwise there is nothing stored except the child
-- record info
if p_filing_status_code <> lv_state_filing_status_code then
lv_context := 'W4 FED';
-- insert a row in the transaction table
pay_aud_ins.ins(
p_effective_date => p_effective_date
,p_transaction_type => lv_trans_type
,p_transaction_date => trunc(sysdate)
,p_transaction_effective_date => p_effective_date
,p_business_group_id => ln_business_group_id
,p_transaction_subtype => lv_trans_subtype
,p_person_id => p_person_id
,p_assignment_id => c_state_rec.assignment_id
,p_source1 => c_state_rec.state_code || '-000-0000'
,p_source1_type => 'JURISDICTION'
,p_source2 => fnd_number.number_to_canonical(c_state_rec.segment1) --gre
,p_source2_type => 'GRE'
,p_source3 => p_source_name
,p_source3_type => 'SOURCE_NAME'
,p_audit_information_category => lv_context
,p_audit_information1 => lv_state_filing_status_code
,p_transaction_parent_id => ln_parent_audit_id
,p_stat_trans_audit_id => ln_dummy
,p_object_version_number => ln_ovn
);
select 'x'
from pay_us_emp_fed_tax_rules_f prtf,
per_assignments_f paf
where paf.person_id = p_person_id
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and prtf.assignment_id = paf.assignment_id
and prtf.effective_start_date <= p_effective_date;
select 'x'
from per_assignments_f paf,
hr_organization_information hoi,
hr_soft_coding_keyflex sck
where paf.person_id = p_person_id
and paf.primary_flag = 'Y'
and paf.effective_end_date >= p_effective_date
and paf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and paf.assignment_type = 'E'
and sck.segment1 = hoi.organization_id
and hoi.org_information_context = '1099R Magnetic Report Rules';
select 'x'
from per_assignments_f paf,
pay_us_emp_fed_tax_rules_f ftr
where paf.person_id = p_person_id
and ftr.assignment_id = paf.assignment_id
and paf.assignment_type = 'E'
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and p_effective_date between ftr.effective_start_date and ftr.effective_end_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and (ftr.excessive_wa_reject_date is not null
or nvl(ftr.fit_override_rate,0) <> 0
or nvl(ftr.supp_tax_override_rate,0) <> 0
or nvl(ftr.fit_override_amount,0) <> 0);
select 'x'
from per_assignments_f paf,
pay_us_emp_state_tax_rules_f str
where paf.person_id = p_person_id
and paf.assignment_type = 'E'
and str.assignment_id = paf.assignment_id
and str.state_code = p_state_code
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and p_effective_date between str.effective_start_date and str.effective_end_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and (str.excessive_wa_reject_date is not null
or nvl(str.sit_override_amount,0) <> 0
or nvl(str.sit_override_rate,0) <> 0
or nvl(str.sui_wage_base_override_amount,0) <> 0
or nvl(str.supp_tax_override_rate,0) <> 0);
select 'x'
from per_assignments_f paf,
pay_us_emp_state_tax_rules_f str,
pay_us_state_tax_info_f stif
where paf.person_id = p_person_id
and paf.assignment_type = 'E'
and str.assignment_id = paf.assignment_id
and stif.state_code = str.state_code
and stif.sta_information7 like 'Y%'
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and p_effective_date between str.effective_start_date and str.effective_end_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and (str.excessive_wa_reject_date is not null
or nvl(str.sit_override_amount,0) <> 0
or nvl(str.sit_override_rate,0) <> 0
or nvl(str.sui_wage_base_override_amount,0) <> 0
or nvl(str.supp_tax_override_rate,0) <> 0);
select 'x'
from per_assignments_f paf,
pay_us_emp_fed_tax_rules_f ftr
where paf.person_id = p_person_id
and paf.assignment_type = 'E'
and ftr.assignment_id = paf.assignment_id
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and ftr.effective_start_date > p_effective_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date;
select 'x'
from per_assignments_f paf,
pay_us_emp_state_tax_rules_f str
where paf.person_id = p_person_id
and str.assignment_id = paf.assignment_id
and paf.assignment_type = 'E'
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and str.state_code = p_state_code
and str.effective_start_date > p_effective_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date;
select 'x'
from per_assignments_f paf,
pay_us_emp_state_tax_rules_f str,
pay_us_state_tax_info_f stif
where paf.person_id = p_person_id
and str.assignment_id = paf.assignment_id
and paf.assignment_type = 'E'
and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
and stif.state_code = str.state_code
and stif.sta_information7 like 'Y%'
and str.effective_start_date > p_effective_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and p_effective_date between stif.effective_start_date and stif.effective_end_date;
lv_update_method VARCHAR2(30);
lv_update_method := 'PRIMARY';
hr_utility.trace(l_proc || ' - Testing W4_UPDATE_METHOD');
if lv_update_method = 'PRIMARY' then
l_primary_only := 'Y';
elsif lv_update_method = 'ALL' then
l_primary_only := 'N';
else -- update_method = NONE
hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Method = None');
return 'PAY_US_OTF_NO_UPDATE_ALLOWED';