[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
condition in updates
of state and city tax
records.
06-Oct-09 emunisek 115.2 8985595 Added new parameter p_clr_wis_eic
to the procedure reset_overrides
to enable the "Clear Wisconsin EIC"
in "Year Begin Process".An overloaded
function was created with new parameter
to maintain integrity of any other
reference to this procedure.
Added procedure wis_eic_ovr to clear
the Wisconsin EIC through the Year
Begin Process.
The wis_eic_ovr is similar to wis_ind_ovr
except that this will clear "EIC Filing Status"
and "EIC Qualifying Children" for Wisconsin
where as only "EIC Filing Status" was cleared
for Indiana.
8-Dec-09 parusia 115.3 9157658 Updated the last_update_date of the newly created
record to represent the sysdate.
8-Jan-10 parusia 115.4 9157658 Added WHO columns for sui_wb_ovr procedure
5-Oct-11 nvelaga 115.5 11056284 Modified the sui_wb_ovr procedure.
Included the missing Attribute and Information
columns in the insert statement.
*/
PROCEDURE sui_wb_ovr
(p_business_group in varchar2
,p_curr_year in varchar2
)
IS
cursor csr_get_asg(p_start_day DATE,
p_business_group varchar2) is
select *
from pay_us_emp_state_tax_rules_f pst
where pst.sui_wage_base_override_amount is not null
and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
and pst.business_group_id = to_number(p_business_group) ;
update PAY_US_EMP_STATE_TAX_RULES_F
set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
and assignment_id = l_state_rec.assignment_id
and effective_start_date = l_state_rec.effective_start_date
and effective_end_date = l_state_rec.effective_end_date
and sui_wage_base_override_amount is not null;
hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
/* Added Attribute and Information columns in insert statement for bug#11056284 */
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,
sui_wage_base_override_amount,
supp_tax_override_rate,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_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,
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
(l_state_rec.emp_state_tax_rule_id,
l_start_day,
l_state_rec.effective_end_date,
l_state_rec.assignment_id,
l_state_rec.state_code,
l_state_rec.jurisdiction_code,
l_state_rec.business_group_id,
l_state_rec.additional_wa_amount,
l_state_rec.filing_status_code,
l_state_rec.remainder_percent,
l_state_rec.secondary_wa,
l_state_rec.sit_additional_tax,
l_state_rec.sit_override_amount,
l_state_rec.sit_override_rate,
l_state_rec.withholding_allowances,
l_state_rec.excessive_wa_reject_date,
l_state_rec.sdi_exempt,
l_state_rec.sit_exempt,
l_state_rec.sit_optional_calc_ind,
l_state_rec.state_non_resident_cert,
l_state_rec.sui_exempt,
l_state_rec.wc_exempt,
null,
l_state_rec.supp_tax_override_rate,
null , -- bug 9157658
l_state_rec.last_updated_by,
l_state_rec.last_update_login,
l_state_rec.created_by,
l_state_rec.creation_date,
l_state_rec.object_version_number,
l_state_rec.attribute_category,
l_state_rec.attribute1,
l_state_rec.attribute2,
l_state_rec.attribute3,
l_state_rec.attribute4,
l_state_rec.attribute5,
l_state_rec.attribute6,
l_state_rec.attribute7,
l_state_rec.attribute8,
l_state_rec.attribute9,
l_state_rec.attribute10,
l_state_rec.attribute11,
l_state_rec.attribute12,
l_state_rec.attribute13,
l_state_rec.attribute14,
l_state_rec.attribute15,
l_state_rec.attribute16,
l_state_rec.attribute17,
l_state_rec.attribute18,
l_state_rec.attribute19,
l_state_rec.attribute20,
l_state_rec.attribute21,
l_state_rec.attribute22,
l_state_rec.attribute23,
l_state_rec.attribute24,
l_state_rec.attribute25,
l_state_rec.attribute26,
l_state_rec.attribute27,
l_state_rec.attribute28,
l_state_rec.attribute29,
l_state_rec.attribute30,
l_state_rec.sta_information_category,
l_state_rec.sta_information1,
l_state_rec.sta_information2,
l_state_rec.sta_information3,
l_state_rec.sta_information4,
l_state_rec.sta_information5,
l_state_rec.sta_information6,
l_state_rec.sta_information7,
l_state_rec.sta_information8,
l_state_rec.sta_information9,
l_state_rec.sta_information10,
l_state_rec.sta_information11,
l_state_rec.sta_information12,
l_state_rec.sta_information13,
l_state_rec.sta_information14,
l_state_rec.sta_information15,
l_state_rec.sta_information16,
l_state_rec.sta_information17,
l_state_rec.sta_information18,
l_state_rec.sta_information19,
l_state_rec.sta_information20,
l_state_rec.sta_information21,
l_state_rec.sta_information22,
l_state_rec.sta_information23,
l_state_rec.sta_information24,
l_state_rec.sta_information25,
l_state_rec.sta_information26,
l_state_rec.sta_information27,
l_state_rec.sta_information28,
l_state_rec.sta_information29,
l_state_rec.sta_information30) ;
hr_utility.trace('Updated Assignment : ' ||
to_char(l_state_rec.assignment_id));
select *
from pay_us_emp_state_tax_rules_f pst
where pst.sta_information1 is not null
and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
and sta_information_category = 'IN'
and state_code = '15'
and pst.business_group_id = to_number(p_business_group);
update PAY_US_EMP_STATE_TAX_RULES_F
set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
and assignment_id = l_state_rec.assignment_id
and effective_start_date = l_state_rec.effective_start_date
and effective_end_date = l_state_rec.effective_end_date
and sta_information1 is not null;
hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
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 ,
SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
SUPP_TAX_OVERRIDE_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_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 ,
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
( l_state_rec.EMP_STATE_TAX_RULE_ID,
l_start_day,
l_state_rec.EFFECTIVE_END_DATE,
l_state_rec.ASSIGNMENT_ID,
l_state_rec.STATE_CODE ,
l_state_rec.JURISDICTION_CODE ,
l_state_rec.BUSINESS_GROUP_ID ,
l_state_rec.ADDITIONAL_WA_AMOUNT ,
l_state_rec.FILING_STATUS_CODE ,
l_state_rec.REMAINDER_PERCENT ,
l_state_rec.SECONDARY_WA ,
l_state_rec.SIT_ADDITIONAL_TAX ,
l_state_rec.SIT_OVERRIDE_AMOUNT ,
l_state_rec.SIT_OVERRIDE_RATE ,
l_state_rec.WITHHOLDING_ALLOWANCES ,
l_state_rec.EXCESSIVE_WA_REJECT_DATE ,
l_state_rec.SDI_EXEMPT ,
l_state_rec.SIT_EXEMPT ,
l_state_rec.SIT_OPTIONAL_CALC_IND ,
l_state_rec.STATE_NON_RESIDENT_CERT ,
l_state_rec.SUI_EXEMPT ,
l_state_rec.WC_EXEMPT ,
l_state_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
l_state_rec.SUPP_TAX_OVERRIDE_RATE ,
NULL , -- Bug 9157658
l_state_rec.LAST_UPDATED_BY ,
l_state_rec.LAST_UPDATE_LOGIN ,
l_state_rec.CREATED_BY ,
l_state_rec.CREATION_DATE ,
l_state_rec.OBJECT_VERSION_NUMBER ,
l_state_rec.ATTRIBUTE_CATEGORY ,
l_state_rec.ATTRIBUTE1 ,
l_state_rec.ATTRIBUTE2 ,
l_state_rec.ATTRIBUTE3 ,
l_state_rec.ATTRIBUTE4 ,
l_state_rec.ATTRIBUTE5 ,
l_state_rec.ATTRIBUTE6 ,
l_state_rec.ATTRIBUTE7 ,
l_state_rec.ATTRIBUTE8 ,
l_state_rec.ATTRIBUTE9 ,
l_state_rec.ATTRIBUTE10 ,
l_state_rec.ATTRIBUTE11 ,
l_state_rec.ATTRIBUTE12 ,
l_state_rec.ATTRIBUTE13 ,
l_state_rec.ATTRIBUTE14 ,
l_state_rec.ATTRIBUTE15 ,
l_state_rec.ATTRIBUTE16 ,
l_state_rec.ATTRIBUTE17 ,
l_state_rec.ATTRIBUTE18 ,
l_state_rec.ATTRIBUTE19 ,
l_state_rec.ATTRIBUTE20 ,
l_state_rec.ATTRIBUTE21 ,
l_state_rec.ATTRIBUTE22 ,
l_state_rec.ATTRIBUTE23 ,
l_state_rec.ATTRIBUTE24 ,
l_state_rec.ATTRIBUTE25 ,
l_state_rec.ATTRIBUTE26 ,
l_state_rec.ATTRIBUTE27 ,
l_state_rec.ATTRIBUTE28 ,
l_state_rec.ATTRIBUTE29 ,
l_state_rec.ATTRIBUTE30 ,
l_state_rec.STA_INFORMATION_CATEGORY ,
NULL ,
l_state_rec.STA_INFORMATION2 ,
l_state_rec.STA_INFORMATION3 ,
l_state_rec.STA_INFORMATION4 ,
l_state_rec.STA_INFORMATION5 ,
l_state_rec.STA_INFORMATION6 ,
l_state_rec.STA_INFORMATION7 ,
l_state_rec.STA_INFORMATION8 ,
l_state_rec.STA_INFORMATION9 ,
l_state_rec.STA_INFORMATION10 ,
l_state_rec.STA_INFORMATION11 ,
l_state_rec.STA_INFORMATION12 ,
l_state_rec.STA_INFORMATION13 ,
l_state_rec.STA_INFORMATION14 ,
l_state_rec.STA_INFORMATION15 ,
l_state_rec.STA_INFORMATION16 ,
l_state_rec.STA_INFORMATION17 ,
l_state_rec.STA_INFORMATION18 ,
l_state_rec.STA_INFORMATION19 ,
l_state_rec.STA_INFORMATION20 ,
l_state_rec.STA_INFORMATION21 ,
l_state_rec.STA_INFORMATION22 ,
l_state_rec.STA_INFORMATION23 ,
l_state_rec.STA_INFORMATION24 ,
l_state_rec.STA_INFORMATION25 ,
l_state_rec.STA_INFORMATION26 ,
l_state_rec.STA_INFORMATION27 ,
l_state_rec.STA_INFORMATION28 ,
l_state_rec.STA_INFORMATION29 ,
l_state_rec.STA_INFORMATION30 ) ;
hr_utility.trace('Updated Assignment : ' ||
to_char(l_state_rec.assignment_id));
select *
from pay_us_emp_state_tax_rules_f pst
where (pst.sta_information1 is not null
or pst.sta_information6 is not null)
and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
and sta_information_category = 'WI'
and state_code = '50'
and pst.business_group_id = to_number(p_business_group);
update PAY_US_EMP_STATE_TAX_RULES_F
set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
and assignment_id = l_state_rec.assignment_id
and effective_start_date = l_state_rec.effective_start_date
and effective_end_date = l_state_rec.effective_end_date
and sta_information1 is not null;
hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
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 ,
SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
SUPP_TAX_OVERRIDE_RATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_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 ,
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
( l_state_rec.EMP_STATE_TAX_RULE_ID,
l_start_day,
l_state_rec.EFFECTIVE_END_DATE,
l_state_rec.ASSIGNMENT_ID,
l_state_rec.STATE_CODE ,
l_state_rec.JURISDICTION_CODE ,
l_state_rec.BUSINESS_GROUP_ID ,
l_state_rec.ADDITIONAL_WA_AMOUNT ,
l_state_rec.FILING_STATUS_CODE ,
l_state_rec.REMAINDER_PERCENT ,
l_state_rec.SECONDARY_WA ,
l_state_rec.SIT_ADDITIONAL_TAX ,
l_state_rec.SIT_OVERRIDE_AMOUNT ,
l_state_rec.SIT_OVERRIDE_RATE ,
l_state_rec.WITHHOLDING_ALLOWANCES ,
l_state_rec.EXCESSIVE_WA_REJECT_DATE ,
l_state_rec.SDI_EXEMPT ,
l_state_rec.SIT_EXEMPT ,
l_state_rec.SIT_OPTIONAL_CALC_IND ,
l_state_rec.STATE_NON_RESIDENT_CERT ,
l_state_rec.SUI_EXEMPT ,
l_state_rec.WC_EXEMPT ,
l_state_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
l_state_rec.SUPP_TAX_OVERRIDE_RATE ,
NULL , -- Bug 9157658
l_state_rec.LAST_UPDATED_BY ,
l_state_rec.LAST_UPDATE_LOGIN ,
l_state_rec.CREATED_BY ,
l_state_rec.CREATION_DATE ,
l_state_rec.OBJECT_VERSION_NUMBER ,
l_state_rec.ATTRIBUTE_CATEGORY ,
l_state_rec.ATTRIBUTE1 ,
l_state_rec.ATTRIBUTE2 ,
l_state_rec.ATTRIBUTE3 ,
l_state_rec.ATTRIBUTE4 ,
l_state_rec.ATTRIBUTE5 ,
l_state_rec.ATTRIBUTE6 ,
l_state_rec.ATTRIBUTE7 ,
l_state_rec.ATTRIBUTE8 ,
l_state_rec.ATTRIBUTE9 ,
l_state_rec.ATTRIBUTE10 ,
l_state_rec.ATTRIBUTE11 ,
l_state_rec.ATTRIBUTE12 ,
l_state_rec.ATTRIBUTE13 ,
l_state_rec.ATTRIBUTE14 ,
l_state_rec.ATTRIBUTE15 ,
l_state_rec.ATTRIBUTE16 ,
l_state_rec.ATTRIBUTE17 ,
l_state_rec.ATTRIBUTE18 ,
l_state_rec.ATTRIBUTE19 ,
l_state_rec.ATTRIBUTE20 ,
l_state_rec.ATTRIBUTE21 ,
l_state_rec.ATTRIBUTE22 ,
l_state_rec.ATTRIBUTE23 ,
l_state_rec.ATTRIBUTE24 ,
l_state_rec.ATTRIBUTE25 ,
l_state_rec.ATTRIBUTE26 ,
l_state_rec.ATTRIBUTE27 ,
l_state_rec.ATTRIBUTE28 ,
l_state_rec.ATTRIBUTE29 ,
l_state_rec.ATTRIBUTE30 ,
l_state_rec.STA_INFORMATION_CATEGORY ,
NULL ,
l_state_rec.STA_INFORMATION2 ,
l_state_rec.STA_INFORMATION3 ,
l_state_rec.STA_INFORMATION4 ,
l_state_rec.STA_INFORMATION5 ,
NULL ,
l_state_rec.STA_INFORMATION7 ,
l_state_rec.STA_INFORMATION8 ,
l_state_rec.STA_INFORMATION9 ,
l_state_rec.STA_INFORMATION10 ,
l_state_rec.STA_INFORMATION11 ,
l_state_rec.STA_INFORMATION12 ,
l_state_rec.STA_INFORMATION13 ,
l_state_rec.STA_INFORMATION14 ,
l_state_rec.STA_INFORMATION15 ,
l_state_rec.STA_INFORMATION16 ,
l_state_rec.STA_INFORMATION17 ,
l_state_rec.STA_INFORMATION18 ,
l_state_rec.STA_INFORMATION19 ,
l_state_rec.STA_INFORMATION20 ,
l_state_rec.STA_INFORMATION21 ,
l_state_rec.STA_INFORMATION22 ,
l_state_rec.STA_INFORMATION23 ,
l_state_rec.STA_INFORMATION24 ,
l_state_rec.STA_INFORMATION25 ,
l_state_rec.STA_INFORMATION26 ,
l_state_rec.STA_INFORMATION27 ,
l_state_rec.STA_INFORMATION28 ,
l_state_rec.STA_INFORMATION29 ,
l_state_rec.STA_INFORMATION30 ) ;
hr_utility.trace('Updated Assignment : ' ||
to_char(l_state_rec.assignment_id));
select pct.*
from pay_us_emp_city_tax_rules_f pct,
pay_state_rules psr,
pay_us_states pus
where psr.head_tax_period = 'A'
and psr.state_code = pus.state_abbrev
and pus.state_code = pct.state_code
and pct.ht_exempt is not null
and p_start_day between (pct.effective_start_date+1) and pct.effective_end_date
and pct.business_group_id = to_number(p_business_group) ;
update PAY_US_EMP_CITY_TAX_RULES_F
set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
where emp_city_tax_rule_id = l_city_rec.emp_city_tax_rule_id
and assignment_id = l_city_rec.assignment_id
and effective_start_date = l_city_rec.effective_start_date
and effective_end_date = l_city_rec.effective_end_date
and ht_exempt is not null;
hr_utility.trace('Inserting Assignment : ' ||to_char(l_city_rec.assignment_id));
hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_city_rec.effective_end_date));
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 ,
SCHOOL_DISTRICT_CODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_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 ,
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
(
l_city_rec.emp_city_tax_rule_id,
l_start_day,
l_city_rec.effective_end_date,
l_city_rec.ASSIGNMENT_ID ,
l_city_rec.STATE_CODE ,
l_city_rec.COUNTY_CODE ,
l_city_rec.CITY_CODE ,
l_city_rec.BUSINESS_GROUP_ID ,
l_city_rec.ADDITIONAL_WA_RATE ,
l_city_rec.FILING_STATUS_CODE ,
l_city_rec.JURISDICTION_CODE ,
l_city_rec.LIT_ADDITIONAL_TAX ,
l_city_rec.LIT_OVERRIDE_AMOUNT ,
l_city_rec.LIT_OVERRIDE_RATE ,
l_city_rec.WITHHOLDING_ALLOWANCES ,
l_city_rec.LIT_EXEMPT ,
l_city_rec.SD_EXEMPT ,
NULL ,
l_city_rec.SCHOOL_DISTRICT_CODE ,
NULL , -- Bug 9157658
l_city_rec.LAST_UPDATED_BY ,
l_city_rec.LAST_UPDATE_LOGIN ,
l_city_rec.CREATED_BY ,
l_city_rec.CREATION_DATE ,
l_city_rec.OBJECT_VERSION_NUMBER ,
l_city_rec.ATTRIBUTE_CATEGORY ,
l_city_rec.ATTRIBUTE1 ,
l_city_rec.ATTRIBUTE2 ,
l_city_rec.ATTRIBUTE3 ,
l_city_rec.ATTRIBUTE4 ,
l_city_rec.ATTRIBUTE5 ,
l_city_rec.ATTRIBUTE6 ,
l_city_rec.ATTRIBUTE7 ,
l_city_rec.ATTRIBUTE8 ,
l_city_rec.ATTRIBUTE9 ,
l_city_rec.ATTRIBUTE10 ,
l_city_rec.ATTRIBUTE11 ,
l_city_rec.ATTRIBUTE12 ,
l_city_rec.ATTRIBUTE13 ,
l_city_rec.ATTRIBUTE14 ,
l_city_rec.ATTRIBUTE15 ,
l_city_rec.ATTRIBUTE16 ,
l_city_rec.ATTRIBUTE17 ,
l_city_rec.ATTRIBUTE18 ,
l_city_rec.ATTRIBUTE19 ,
l_city_rec.ATTRIBUTE20 ,
l_city_rec.ATTRIBUTE21 ,
l_city_rec.ATTRIBUTE22 ,
l_city_rec.ATTRIBUTE23 ,
l_city_rec.ATTRIBUTE24 ,
l_city_rec.ATTRIBUTE25 ,
l_city_rec.ATTRIBUTE26 ,
l_city_rec.ATTRIBUTE27 ,
l_city_rec.ATTRIBUTE28 ,
l_city_rec.ATTRIBUTE29 ,
l_city_rec.ATTRIBUTE30 ,
l_city_rec.CTY_INFORMATION_CATEGORY ,
l_city_rec.CTY_INFORMATION1 ,
l_city_rec.CTY_INFORMATION2 ,
l_city_rec.CTY_INFORMATION3 ,
l_city_rec.CTY_INFORMATION4 ,
l_city_rec.CTY_INFORMATION5 ,
l_city_rec.CTY_INFORMATION6 ,
l_city_rec.CTY_INFORMATION7 ,
l_city_rec.CTY_INFORMATION8 ,
l_city_rec.CTY_INFORMATION9 ,
l_city_rec.CTY_INFORMATION10 ,
l_city_rec.CTY_INFORMATION11 ,
l_city_rec.CTY_INFORMATION12 ,
l_city_rec.CTY_INFORMATION13 ,
l_city_rec.CTY_INFORMATION14 ,
l_city_rec.CTY_INFORMATION15 ,
l_city_rec.CTY_INFORMATION16 ,
l_city_rec.CTY_INFORMATION17 ,
l_city_rec.CTY_INFORMATION18 ,
l_city_rec.CTY_INFORMATION19 ,
l_city_rec.CTY_INFORMATION20 ,
l_city_rec.CTY_INFORMATION21 ,
l_city_rec.CTY_INFORMATION22 ,
l_city_rec.CTY_INFORMATION23 ,
l_city_rec.CTY_INFORMATION24 ,
l_city_rec.CTY_INFORMATION25 ,
l_city_rec.CTY_INFORMATION26 ,
l_city_rec.CTY_INFORMATION27 ,
l_city_rec.CTY_INFORMATION28 ,
l_city_rec.CTY_INFORMATION29 ,
l_city_rec.CTY_INFORMATION30
);
hr_utility.trace('Updated Assignment : ' ||
to_char(l_city_rec.assignment_id));
select *
from pay_us_emp_fed_tax_rules_f pft
where pft.eic_filing_status_code <> 0
and p_start_day between (pft.effective_start_date+1) and pft.effective_end_date
and pft.business_group_id = to_number(p_business_group);
update PAY_US_EMP_FED_TAX_RULES_F
set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
where assignment_id = l_fed_rec.assignment_id
and effective_start_date = l_fed_rec.effective_start_date
and effective_end_date = l_fed_rec.effective_end_date
and eic_filing_status_code <> 0;
hr_utility.trace('Inserting Assignment : ' ||to_char(l_fed_rec.assignment_id));
hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_fed_rec.effective_end_date));
/* Insert Stmt for FED Tax Rules will come here */
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 ,
STATUTORY_EMPLOYEE ,
W2_FILED_YEAR ,
SUPP_TAX_OVERRIDE_RATE ,
EXCESSIVE_WA_REJECT_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_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_start_day,
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 ,
l_fed_rec.FILING_STATUS_CODE ,
l_fed_rec.FIT_OVERRIDE_AMOUNT ,
l_fed_rec.FIT_OVERRIDE_RATE ,
l_fed_rec.WITHHOLDING_ALLOWANCES ,
l_fed_rec.CUMULATIVE_TAXATION ,
0 ,
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.STATUTORY_EMPLOYEE ,
l_fed_rec.W2_FILED_YEAR ,
l_fed_rec.SUPP_TAX_OVERRIDE_RATE ,
l_fed_rec.EXCESSIVE_WA_REJECT_DATE ,
NULL , -- Bug 9157658
l_fed_rec.LAST_UPDATED_BY ,
l_fed_rec.LAST_UPDATE_LOGIN ,
l_fed_rec.CREATED_BY ,
l_fed_rec.CREATION_DATE ,
l_fed_rec.OBJECT_VERSION_NUMBER ,
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
);
hr_utility.trace('Updated Assignment : ' ||
to_char(l_fed_rec.assignment_id));
pay_us_indiana.update_address(errbuf,retcode,p_business_group,p_curr_year);