[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
19-FEB-2001 meshah 115.8 Changed update_tax_records to insert one
record for Federal and State each.
02-MAR-2001 meshah 115.9 now inserting source3 in pay_stat_trans_audit
and attribute_category = W4 State for state
records.
05-MAR-2001 meshah 115.10 Bug 1668926 is fixed now. We need to check the
filing status and exemptions between federal and
state records before updating state information.
Now inserting source3 for federal also.
15-MAR-2001 meshah 115.11 Now truncating transaction_date before inserting
in the pay_stat_trans_audit table.
27-APR-2001 meshah 115.12 Made changes to the package so that the same
package works with the new techstack.
1. Added procedure check_update_status.
2. Removed the checking from the cursors to get
retiree employees also.
3. New parameters and additional logic in procedure
validate_submission and update_tax_records.
4. New procedure get_transaction_values.
5. New procedure update_w4_info.
6. Removed the old calls to review and confirmation
page also the old workflow is removed.
03-MAY-2001 meshah 115.13 Commented the code where we check if the field id
displayed or not in validate_submission procedure.
07-MAY-2001 meshah 115.14 Now getting the business group id for the person
changes made in update_tax_record.
11-MAY-2001 meshah 115.15 new parameter to validate_submission and
new function GET_STATE_LIST.
11-MAY-2001 meshah 115.16 new source4 and source4_type for State record
in update_tax_record.Also removed item_type and
added transaction_id.
23-MAY-2001 meshah 115.17 added a order by clause in get_transaction_values.
25-MAY-2001 meshah 115.18 New parameter in validate_submission, inserting
filing_status_code into transaction table.
In get_transaction_values fetching one more
value of filing_status_code.
02-AUG-2001 meshah 115.19 setting the value of l_exempt_status_code to
Yes or No in validate_submission.
setting the value of l_exempt to Y or N in
update_w4_info.
Updated update_tax_record, cursor c_fed_tax_rows
now selecting paf.primary_flag and depending
on the value of the primary flag inserting
parent_transaction_id.
20-AUG-2001 meshah 115.20 Added two more parameter to validate_submission.
Removed procedure check_update_status.
04-SEP-2001 meshah 115.01 adding p_original_exempt. showing message if
there is a change in filing status or allowances
or exempt status. for this we are now saving
the actual Filing status, allowances and exempt
satus in transaction tables. changed update_tax_records
also.
10-SEP-2001 meshah 115.02 changed the field name from FitAdditionalTax to
TaxString and AgreementFlag to Agreementflag
because the names changed in AK.
bugs 1986371 and 1983167.
20-SEP-2001 meshah 115.03 setting l_state_count = 1 when the cursor fetched
a record. Depending on the value of l_state_count
we print the state message. bug#2004478.
01-OCT-2001 meshah 115.04 Bug 2006653. Now selecting sit_additional_amount
in c_state_tax_record cursor in update_tax_record
procedure and updating with the same value.
Bug 2015129. In update_tax_record we are now
opening the state cursor within the Fed cursor
and passing the assignment_id from fed to state.
Bug 2015300. Now checking for all the values that
are returned from the profile value. (None,null,
Primary and All).
15-OCT-2001 meshah 115.05 bug 2027211, now while validating the record we are
calling the update_tax_records procedure to insert
the record and also to check if the state will be
changed, if yes then set the global variable g_state_list.
bug 2038691. Commented the checking for the start
and end date with sysdate and the 31-dec-4712.
20-DEC-2002 meshah 115.06 added index hints for c_excess_over_state and
c_future_state_recs cursors for 1159.
also added nocopy and dbdrv.
28-OCT-2003 meshah 115.08 p_exempt_state_list parameters has been
added to validate_submission. Function
get_state_list has been modified.
update_tax_records and validate_submission
have been changed to get the states that
do not default the exempt status from federal.
Bug 3151569. Also now we are updating the
transaction values if transaction exists.
24-Nov-2003 meshah We insert only those states that are affected
by the W4 change.
04-DEC-2003 meshah 115.09 defaulting cu_sit_exempt to l_state_exempt when
the state does not default from federal for
exempt status.
09-DEC-2003 meshah 115.10 made changes to the code since the filing status
have been changed for certain states. Example
AZ, LA, MA, WV these states don't have a
equvivalent code for Married at federal.
PROCEDURE update_tax_records has been updated.
09-APR-2004 meshah 115.11 p_original_aa parameter has been added to procedure
validate_submission. Also now we are storing
original additional amount in the transaction
since we are using a VO to display before and after
values in the Review Page.
07-MAR-2005 meshah 115.12 for bug 4225569 removed the checking for
SUI_WAGEBASE_OVERRIDE from function
check_update_status.
Also added SUI_WAGEBASE_OVERRIDE in
update_tax_records so that the same amount
gets updated when update the state records.
23-MAY-2005 rsethupa 115.13 Bug 4070034: Changes to insert new field
P_LAST_NAME_DIFF in Audit Table
16-JUN-2005 rsethupa 115.14 Bug 4204103: Added check for comparing the no. of
Tax Exemptions at the Fed and State level
26-sep-2005 jgoswami 115.15 Bug 4599982 - Added update_alien_tax_records
to support pqp calls to old w4 packages.
26-oct-2005 jgoswami 115.16 Bug 4671389 modified update_tax_records.
03-nov-2005 jgoswami 115.17 Bug 4671389 modified update_tax_records.
03-dec-2005 jgoswami 115.18 Bug 4707873 when SUPPLEMENTAL rate for FIT/SIT
override is enetred then error message should
not be received.
17-jan-2006 jgoswami 115.19 Bug 4956850 - added new parameter
p_transaction_type and p_source_name to
procedure update_tax_records
30-jun-2006 jgoswami 115.21 Bug 5334081 - Not changing state Filing Status
to Federal Filing Status when State Does Not
Follow Federal. Arizona does NOT use marital
status in calculating SIT , they have % of FIT
which the employee elects.
11-aug-2006 jgoswami 115.22 Bug 5198005 - Suppress W4 Notifications for the
W4 forms that are exempt or at a level above 10
allowances as IRS does not require Employer to
Send it. Based on the value of the DFF the
Notification will be sent or suppressed. Default
the Notification will be Suppressed.
Similarly Information message on Review page is
also suppressed.
Created function get_org_context.
06-sep-2006 jgoswami 115.23 Bug 3852021 - Modified validate_submission
changed data type for p_additional_amount,
p_original_aa from varchar2 to Number .
05-dec-2006 vaprakas 115.24 Bug 5607135 - Modified the procedure
validate submission to implement check for NRAs.
13-AUG-2007 vaprakas Bug 6200677 modified
17-Nov-2007 sudedas SS W4 Added Function Fed_State_Filing_Status_Match
Modified update_tax_records,
update_w4_info,
validate_submission.
19-Nov-2007 sudedas 6333947 Included Changes for this bug.
21-Nov-2007 sudedas Changed update_tax_records, Addl Tax Defaulted.
28-Nov-2007 sudedas Fixed some issues identified during QA
115.30 Fixed Informational Message Display Issue.
19-May-2008 Pannapur 115.31 Modified update_tax_records to Fix Bug no 7005814
28-may-2008 asgugupt 115.32 Modified for bug no 7121877
04-Nov-2008 Pannapur 115.33 Modified for Bug no 7521930
*******************************************************************/
AS
/******************************************************************
** private package global declarations
******************************************************************/
gv_package_name VARCHAR2(50) := 'pay_us_web_w4';
SELECT state_name
FROM pay_us_states
WHERE state_code = p_st_cd;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'US_FS_'||p_state_code
AND lpad(lookup_code,2,'0') = p_filing_status_code
AND application_id = 800
AND enabled_flag = 'Y';
hr_utility.trace('Not Going to Update State Info From Federal.');
hr_utility.trace('Not Going to Update State Info From Federal.');
hr_utility.trace('Not Going to Update State Info From Federal.');
hr_utility.trace('Not Going to Update State Info From Federal.');
hr_utility.trace('Not Going to Update State Info From Federal.');
hr_utility.trace('Not Going to Update State Info From Federal.');
hr_utility.trace('Not Going to Update State Info From Federal.');
FUNCTION check_update_status(p_person_id IN per_people_f.person_id%TYPE)
RETURN VARCHAR2
/******************************************************************
**
** Description:
** checks whether person meets update allowed status
**
** Access Status:
** Public
**
******************************************************************/
IS
l_primary_only VARCHAR2(1);
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 trunc(sysdate) between ftr.effective_start_date and
ftr.effective_end_date
and trunc(sysdate) 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
-- bug 4707873 --or nvl(ftr.supp_tax_override_rate,0) <> 0
or nvl(ftr.fit_override_amount,0) <> 0);
select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ '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 trunc(sysdate) between str.effective_start_date and
str.effective_end_date
and trunc(sysdate) 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
-- bug 4707873 --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 > trunc(sysdate)
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date;
select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ '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 > trunc(sysdate)
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between stif.effective_start_date and
stif.effective_end_date;
lv_update_method VARCHAR2(30);
hr_utility.trace('Entering ' || gv_package_name || '.check_update_status');
lv_update_method := fnd_profile.value('HR_OTF_UPDATE_METHOD');
hr_utility.trace('OTF Update Method = ' || lv_update_method);
hr_utility.trace('Testing PROFILE HR_OTF_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 or null
-- we always default the value to primary
l_primary_only := 'Y';
hr_utility.trace('Passed PROFILE HR_OTF_UPDATE_METHOD');
hr_utility.trace('Leaving ' || gv_package_name || '.check_update_status');
end check_update_status;
lv_update_error_msg VARCHAR2(10000);
lv_update_method VARCHAR2(30) := 'PRIMARY' ;
select fed_information1
from pay_us_federal_tax_info_f
where fed_information_category = 'ALLOWANCES LIMIT'
and trunc(sysdate) between effective_start_date and effective_end_date;
select fff.function_id, fff.function_name
from fnd_form_functions_vl fff
where fff.function_name = ( select iav.text_value
from wf_item_attribute_values iav
where iav.item_type = p_item_type
and iav.item_key = p_item_key
and iav.name = 'P_CALLED_FROM') ;
select information_type,pei_information_category,pei_information5,pei_information9
from per_people_extra_info where person_id=p_person_id
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');
select pei_information1,pei_information2
from per_people_extra_info where person_id=p_person_id
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');
-- dates since we do updates on the sysdate and only if there are
-- no future dated records.
pay_fed_bus.chk_filing_status_code(
p_emp_fed_tax_rule_id => null
,p_filing_status_code => p_filing_status_code
,p_effective_date => trunc(sysdate)
,p_validation_start_date => trunc(sysdate)
,p_validation_end_date => to_date('31/12/4712','DD/MM/YYYY')
);
hr_utility.trace('Validation Error - returning to Update');
update_tax_records(p_filing_status_code => p_filing_status_code,
p_org_filing_status_code => p_original_fs,
p_allowances => l_allowances,
p_org_allowances => p_original_wa,
p_additional_amount => l_additional_tax,
p_last_name_diff => l_last_name_diff_flag,
p_exempt_status_code => substr(l_exempt_status_code,1,1),
p_org_exempt_status_code => substr(p_original_exempt,1,1),
p_transaction_id => null,
p_person_id => ln_person_id,
p_transaction_type => lv_trans_type,
p_source_name => lv_source_name,
--p_update_method => lv_update_method,
p_validate => TRUE );
/* If we have come so far that means we have no errors. So insert data
into hr_api_transactions, hr_api_transaction_steps and hr_api_transaction_values.
Need to insert data into these tables to store OLD and NEW values for W4. These
OLD and NEW values are used in the review page. */
-- First the hr_api_transactions table
l_transaction_id := hr_transaction_ss.get_transaction_id(p_itemtype, p_itemkey);
hr_utility.trace('l_transaction_id is null INSERTING');
,p_selected_person_id =>ln_person_id
,p_item_type =>p_itemtype
,p_item_key =>p_itemkey
,p_transaction_effective_date=>sysdate
,p_process_name =>lv_process_name
);
p_api_name => gv_package_name||'.update_w4_info',
p_item_type => p_itemtype,
p_item_key => p_itemkey,
p_activity_id => p_activity_id,
p_transaction_step_id => l_transaction_step_id,
p_object_version_number => l_step_obj_version_number);
select transaction_step_id into l_transaction_step_id
from HR_API_TRANSACTION_STEPS
where transaction_id = l_transaction_id;
select fcl.meaning Meaning into transaction_value_fs
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_FIT_FILING_STATUS'
and fcl.lookup_code = p_filing_status_code ;
select fcl.meaning Meaning into transaction_value_fs
from fnd_common_lookups fcl
where fcl.lookup_type = 'US_FIT_FILING_STATUS'
and fcl.lookup_code = p_original_fs ;
select v.datatype,v.name,v.varchar2_value,number_value
from hr_api_transaction_values v
where v.transaction_step_id = l_step_id
order by transaction_value_id;
select transaction_step_id into l_step_id
from hr_api_transaction_steps
where transaction_id = to_number(p_trans_id);
PROCEDURE update_alien_tax_records(
p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
,p_process VARCHAR2
,p_itemtype VARCHAR2
,p_person_id per_people_f.person_id%TYPE default null
,p_effective_date date default null
,p_source_name VARCHAR2 default null
)
/******************************************************************
**
** Description: OTF Fed W4 update procedure
** 1. locks all applicable rows
** 2. update each fed row using fed api
** 3. update each state row using state api
** 4. archive the submission
**
** Access Status:
** Public
**
******************************************************************/
IS
ln_person_id per_people_f.person_id%TYPE;
lv_update_method VARCHAR2(30);
lv_update_error_msg VARCHAR2(10000);
e_no_update_allowed EXCEPTION;
SAVEPOINT update_alien_tax_records;
lv_update_method := 'PRIMARY';
update_tax_records(p_filing_status_code => p_filing_status_code,
p_org_filing_status_code => lv_org_filing_status_code,
p_allowances => lv_allowances,
p_org_allowances => lv_org_allowances,
p_additional_amount => lv_additional_tax,
p_last_name_diff => lv_last_name_diff_flag,
p_exempt_status_code => lv_exempt_status_code,
p_org_exempt_status_code => lv_org_exempt_status_code,
p_transaction_id => null,
p_person_id => ln_person_id,
p_transaction_type => lv_trans_type,
p_source_name => lv_source_name,
--p_update_method => lv_update_method,
p_validate => FALSE );
rollback to update_alien_tax_records;
END update_alien_tax_records;
PROCEDURE update_tax_records(
p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
,p_org_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
,p_org_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
,p_last_name_diff VARCHAR2 DEFAULT 'N'
,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
,p_org_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
,p_transaction_id hr_api_transactions.transaction_id%type
,p_person_id VARCHAR2
,p_transaction_type VARCHAR2
,p_source_name VARCHAR2
--,p_update_method VARCHAR2
,p_validate boolean default false
)
/******************************************************************
**
** Description: OTF Fed W4 update procedure
** 1. locks all applicable rows
** 2. update each fed row using fed api
** 3. update each state row using state api
** 4. archive the submission
**
** Access Status:
** Public
**
******************************************************************/
IS
ln_person_id per_people_f.person_id%TYPE;
lv_update_method VARCHAR2(30) := 'PRIMARY';
lv_update_error_msg VARCHAR2(10000);
e_no_update_allowed EXCEPTION;
lv_insert_flag boolean := false;
select ftr.emp_fed_tax_rule_id,
ftr.filing_status_code,
ftr.withholding_allowances,
ftr.object_version_number,
ftr.effective_start_date,
paf.assignment_id,
hsck.segment1,
paf.primary_flag,
decode(ftr.fit_exempt,'Y',1,0),
decode(ftr.futa_tax_exempt,'Y',1,0),
decode(ftr.medicare_tax_exempt,'Y',1,0),
decode(ftr.ss_tax_exempt,'Y',1,0)
from pay_us_emp_fed_tax_rules_f ftr, per_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.person_id = ln_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 trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between ftr.effective_start_date and
ftr.effective_end_date
order by paf.assignment_id
for update nowait;
select ftr.*,
hsck.segment1 gre_id,
paf.primary_flag primary_flag,
trunc(sysdate) cur_sysdate,
decode(ftr.fit_exempt,'Y',1,0) fit_exempt_count,
decode(ftr.futa_tax_exempt,'Y',1,0) futa_tax_exempt_count,
decode(ftr.medicare_tax_exempt,'Y',1,0) medicare_tax_exempt_count,
decode(ftr.ss_tax_exempt,'Y',1,0) ss_tax_exempt_count
from pay_us_emp_fed_tax_rules_f ftr
,per_assignments_f paf
,hr_soft_coding_keyflex hsck
where paf.person_id = ln_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 trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between ftr.effective_start_date and
ftr.effective_end_date
order by paf.assignment_id
for update nowait;
select str.emp_state_tax_rule_id,
str.object_version_number,
str.effective_start_date,
str.filing_status_code,
str.withholding_allowances,
str.sit_additional_tax,
pus.state_name,
pus.state_code,
paf.assignment_id,
stif.sta_information7,
hsck.segment1
,str.sit_exempt
,nvl(stif.sta_information9,'N') -- does the exempt status default from federal
,str.sui_wage_base_override_amount
,decode(str.sit_exempt,'Y',1,0)
,decode(str.sui_exempt,'Y',1,0)
,decode(str.sdi_exempt,'Y',1,0)
,decode(str.wc_exempt,'Y',1,0)
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 = ln_person_id
and paf.assignment_id = curvar_assignment_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 trunc(sysdate) between stif.effective_start_date and
stif.effective_end_date
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between str.effective_start_date and
str.effective_end_date
for update nowait;
select str.*
,pus.state_name
,stif.sta_information7 state_as_fed
,hsck.segment1 gre_id
,nvl(stif.sta_information9,'N') exmpt_status_state_as_fed -- does the exempt status default from federal
,trunc(sysdate) cur_sysdate
,decode(str.sit_exempt,'Y',1,0) sit_exempt_count
,decode(str.sui_exempt,'Y',1,0) sui_exempt_count
,decode(str.sdi_exempt,'Y',1,0) sdi_exempt_count
,decode(str.wc_exempt,'Y',1,0) wc_exempt_count
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 = ln_person_id
and paf.assignment_id = curvar_assignment_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 trunc(sysdate) between stif.effective_start_date and
stif.effective_end_date
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between str.effective_start_date and
str.effective_end_date
for update nowait;
hr_utility.trace('Entering ' || gv_package_name || '.update_tax_records');
SAVEPOINT update_tax_records;
-- get the update method
if lv_source_name = 'PQP_US_ALIEN_WINDSTAR' then
lv_update_method := 'PRIMARY';
lv_update_method := fnd_profile.value('HR_OTF_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 or null
-- we always default the value to primary
l_primary_only := 'Y';
hr_utility.trace('Update Method = ' || lv_update_method);
hr_utility.trace(gv_package_name||'.update_tax_records - Locking Employee Tax Records');
hr_utility.trace(gv_package_name ||'.update_tax_records - BEFORE FED UPDATE');
-- We insert using datetrack mode of UPDATE
-- future dated records will cause an error
-- if the old start date = sysdate, we perform a correction instead
--if ld_old_start_date = trunc(sysdate) then
if l_fed_tax_rec.effective_start_date = trunc(sysdate) 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_allowances
,p_fit_additional_tax => p_additional_amount
,p_filing_status_code => lv_filing_status_code
,p_fit_exempt => p_exempt_status_code
,p_object_version_number => ln_ovn
,p_effective_start_date => ld_start_date
,p_effective_end_date => ld_end_date
,p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => lv_datetrack_mode
,p_validate => p_validate
);
pay_federal_tax_rule_api.update_fed_tax_rule(
p_validate =>p_validate
,p_effective_date =>l_fed_tax_rec.cur_sysdate
,p_datetrack_update_mode =>lv_datetrack_mode
,p_emp_fed_tax_rule_id =>l_fed_tax_rec.emp_fed_tax_rule_id
,p_object_version_number =>l_fed_tax_rec.object_version_number
,p_sui_state_code =>l_fed_tax_rec.sui_state_code
,p_additional_wa_amount =>l_fed_tax_rec.additional_wa_amount
,p_filing_status_code =>lv_filing_status_code
,p_fit_override_amount =>l_fed_tax_rec.fit_override_amount
,p_fit_override_rate =>l_fed_tax_rec.fit_override_rate
,p_withholding_allowances =>p_allowances
,p_cumulative_taxation =>l_fed_tax_rec.cumulative_taxation
,p_eic_filing_status_code =>l_fed_tax_rec.eic_filing_status_code
,p_fit_additional_tax =>p_additional_amount
,p_fit_exempt =>p_exempt_status_code
,p_futa_tax_exempt =>l_fed_tax_rec.futa_tax_exempt
,p_medicare_tax_exempt =>l_fed_tax_rec.medicare_tax_exempt
,p_ss_tax_exempt =>l_fed_tax_rec.ss_tax_exempt
,p_statutory_employee =>l_fed_tax_rec.statutory_employee
,p_w2_filed_year =>l_fed_tax_rec.w2_filed_year
,p_supp_tax_override_rate =>l_fed_tax_rec.supp_tax_override_rate
,p_excessive_wa_reject_date =>l_fed_tax_rec.excessive_wa_reject_date
,p_attribute_category =>l_fed_tax_rec.attribute_category
,p_attribute1 =>l_fed_tax_rec.attribute1
,p_attribute2 =>l_fed_tax_rec.attribute2
,p_attribute3 =>l_fed_tax_rec.attribute3
,p_attribute4 =>l_fed_tax_rec.attribute4
,p_attribute5 =>l_fed_tax_rec.attribute5
,p_attribute6 =>l_fed_tax_rec.attribute6
,p_attribute7 =>l_fed_tax_rec.attribute7
,p_attribute8 =>l_fed_tax_rec.attribute8
,p_attribute9 =>l_fed_tax_rec.attribute9
,p_attribute10 =>l_fed_tax_rec.attribute10
,p_attribute11 =>l_fed_tax_rec.attribute11
,p_attribute12 =>l_fed_tax_rec.attribute12
,p_attribute13 =>l_fed_tax_rec.attribute13
,p_attribute14 =>l_fed_tax_rec.attribute14
,p_attribute15 =>l_fed_tax_rec.attribute15
,p_attribute16 =>l_fed_tax_rec.attribute16
,p_attribute17 =>l_fed_tax_rec.attribute17
,p_attribute18 =>l_fed_tax_rec.attribute18
,p_attribute19 =>l_fed_tax_rec.attribute19
,p_attribute20 =>l_fed_tax_rec.attribute20
,p_attribute21 =>l_fed_tax_rec.attribute21
,p_attribute22 =>l_fed_tax_rec.attribute22
,p_attribute23 =>l_fed_tax_rec.attribute23
,p_attribute24 =>l_fed_tax_rec.attribute24
,p_attribute25 =>l_fed_tax_rec.attribute25
,p_attribute26 =>l_fed_tax_rec.attribute26
,p_attribute27 =>l_fed_tax_rec.attribute27
,p_attribute28 =>l_fed_tax_rec.attribute28
,p_attribute29 =>l_fed_tax_rec.attribute29
,p_attribute30 =>l_fed_tax_rec.attribute30
,p_fed_information_category =>l_fed_tax_rec.fed_information_category
,p_fed_information1 =>l_fed_tax_rec.fed_information1
,p_fed_information2 =>l_fed_tax_rec.fed_information2
,p_fed_information3 =>l_fed_tax_rec.fed_information3
,p_fed_information4 =>l_fed_tax_rec.fed_information4
,p_fed_information5 =>l_fed_tax_rec.fed_information5
,p_fed_information6 =>l_fed_tax_rec.fed_information6
,p_fed_information7 =>l_fed_tax_rec.fed_information7
,p_fed_information8 =>l_fed_tax_rec.fed_information8
,p_fed_information9 =>l_fed_tax_rec.fed_information9
,p_fed_information10 =>l_fed_tax_rec.fed_information10
,p_fed_information11 =>l_fed_tax_rec.fed_information11
,p_fed_information12 =>l_fed_tax_rec.fed_information12
,p_fed_information13 =>l_fed_tax_rec.fed_information13
,p_fed_information14 =>l_fed_tax_rec.fed_information14
,p_fed_information15 =>l_fed_tax_rec.fed_information15
,p_fed_information16 =>l_fed_tax_rec.fed_information16
,p_fed_information17 =>l_fed_tax_rec.fed_information17
,p_fed_information18 =>l_fed_tax_rec.fed_information18
,p_fed_information19 =>l_fed_tax_rec.fed_information19
,p_fed_information20 =>l_fed_tax_rec.fed_information20
,p_fed_information21 =>l_fed_tax_rec.fed_information21
,p_fed_information22 =>l_fed_tax_rec.fed_information22
,p_fed_information23 =>l_fed_tax_rec.fed_information23
,p_fed_information24 =>l_fed_tax_rec.fed_information24
,p_fed_information25 =>l_fed_tax_rec.fed_information25
,p_fed_information26 =>l_fed_tax_rec.fed_information26
,p_fed_information27 =>l_fed_tax_rec.fed_information27
,p_fed_information28 =>l_fed_tax_rec.fed_information28
,p_fed_information29 =>l_fed_tax_rec.fed_information29
,p_fed_information30 =>l_fed_tax_rec.fed_information30
,p_effective_start_date =>ld_start_date
,p_effective_end_date =>ld_end_date
);
-- we insert a row into the transaction table to show the change
-- to this assignment
/* we want to get the transaction id of the primary assignment and update
that id for all the corresponding transactions hence we need to check
for the primary flag. */
if l_fed_tax_rec.primary_flag = 'Y' then
hr_utility.trace('Primary Flag is = ' || l_fed_tax_rec.primary_flag);
-- next we update all state tax records for this assignment id.
-- we don't update the amount withheld, because it is probably of a different magnitude
-- then the state taxes.
-- We will update state record only if the one of the following has changed.
/* to update state tax Yes or No */
hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
hr_utility.trace(gv_package_name||'.update_tax_records-BEFORE STATE UPDATE');
lv_datetrack_mode := 'UPDATE';
/* Before we update the state tax records, need to check if the
filing status and withholding allowances are same at federal
and state level, if not then we should not update the state
tax records. If this is the first time then we will update
the state records also. Bug 1668926.
Check fed = state FS and WA */
hr_utility.trace('cu_state_fs_code = ' || l_state_tax_rec.filing_status_code);
/* allow update of state tax record(s).
We pass null as the id because if we passed the
id we would need to call some other functions to set
up certain globals, etc. This will be done when the
update procedure does its validation, so we take the
quick way here. */
lv_state_filing_status_code := lv_filing_status_code_o;
lv_insert_flag := TRUE;
lv_insert_flag := TRUE;
lv_insert_flag := TRUE;
lv_insert_flag := TRUE;
lv_insert_flag := FALSE;
lv_insert_flag := TRUE;
if lv_insert_flag then /* mehul */
hr_utility.trace('Update state info : '|| l_state_tax_rec.emp_state_tax_rule_id);
pay_state_tax_rule_api.update_state_tax_rule
(p_emp_state_tax_rule_id => l_state_tax_rec.emp_state_tax_rule_id
,p_withholding_allowances => p_allowances
,p_sit_additional_tax => ln_state_addtional_tax
,p_filing_status_code => lv_state_filing_status_code
--,p_sit_exempt => p_exempt_status_code
,p_sit_exempt => l_state_exempt
,p_object_version_number => ln_ovn
,p_effective_start_date => ld_start_date
,p_effective_end_date => ld_end_date
,p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => lv_datetrack_mode
,p_validate => p_validate
,p_sui_wage_base_override_amoun => l_state_tax_rec.sui_wage_base_override_amt
);
pay_state_tax_rule_api.update_state_tax_rule(
p_validate =>p_validate
,p_effective_date =>l_state_tax_rec.cur_sysdate
,p_datetrack_update_mode =>lv_datetrack_mode
,p_emp_state_tax_rule_id =>l_state_tax_rec.emp_state_tax_rule_id
,p_object_version_number =>l_state_tax_rec.object_version_number
,p_additional_wa_amount =>l_state_tax_rec.additional_wa_amount
,p_filing_status_code =>lv_state_filing_status_code
,p_remainder_percent =>l_state_tax_rec.remainder_percent
,p_secondary_wa =>l_state_tax_rec.secondary_wa
,p_sit_additional_tax =>p_additional_amount -- l_state_tax_rec.sit_additional_tax
,p_sit_override_amount =>l_state_tax_rec.sit_override_amount
,p_sit_override_rate =>l_state_tax_rec.sit_override_rate
,p_withholding_allowances =>p_allowances
,p_excessive_wa_reject_date =>l_state_tax_rec.excessive_wa_reject_date
,p_sdi_exempt =>l_state_tax_rec.sdi_exempt
,p_sit_exempt =>l_state_exempt
,p_sit_optional_calc_ind =>l_state_tax_rec.sit_optional_calc_ind
,p_state_non_resident_cert =>l_state_tax_rec.state_non_resident_cert
,p_sui_exempt =>l_state_tax_rec.sui_exempt
,p_wc_exempt =>l_state_tax_rec.wc_exempt
,p_sui_wage_base_override_amoun =>l_state_tax_rec.sui_wage_base_override_amount
,p_supp_tax_override_rate =>l_state_tax_rec.supp_tax_override_rate
,p_attribute_category =>l_state_tax_rec.attribute_category
,p_attribute1 =>l_state_tax_rec.attribute1
,p_attribute2 =>l_state_tax_rec.attribute2
,p_attribute3 =>l_state_tax_rec.attribute3
,p_attribute4 =>l_state_tax_rec.attribute4
,p_attribute5 =>l_state_tax_rec.attribute5
,p_attribute6 =>l_state_tax_rec.attribute6
,p_attribute7 =>l_state_tax_rec.attribute7
,p_attribute8 =>l_state_tax_rec.attribute8
,p_attribute9 =>l_state_tax_rec.attribute9
,p_attribute10 =>l_state_tax_rec.attribute10
,p_attribute11 =>l_state_tax_rec.attribute11
,p_attribute12 =>l_state_tax_rec.attribute12
,p_attribute13 =>l_state_tax_rec.attribute13
,p_attribute14 =>l_state_tax_rec.attribute14
,p_attribute15 =>l_state_tax_rec.attribute15
,p_attribute16 =>l_state_tax_rec.attribute16
,p_attribute17 =>l_state_tax_rec.attribute17
,p_attribute18 =>l_state_tax_rec.attribute18
,p_attribute19 =>l_state_tax_rec.attribute19
,p_attribute20 =>l_state_tax_rec.attribute20
,p_attribute21 =>l_state_tax_rec.attribute21
,p_attribute22 =>l_state_tax_rec.attribute22
,p_attribute23 =>l_state_tax_rec.attribute23
,p_attribute24 =>l_state_tax_rec.attribute24
,p_attribute25 =>l_state_tax_rec.attribute25
,p_attribute26 =>l_state_tax_rec.attribute26
,p_attribute27 =>l_state_tax_rec.attribute27
,p_attribute28 =>l_state_tax_rec.attribute28
,p_attribute29 =>l_state_tax_rec.attribute29
,p_attribute30 =>l_state_tax_rec.attribute30
,p_sta_information_category =>l_state_tax_rec.sta_information_category
,p_sta_information1 =>l_state_tax_rec.sta_information1
,p_sta_information2 =>l_state_tax_rec.sta_information2
,p_sta_information3 =>l_state_tax_rec.sta_information3
,p_sta_information4 =>l_state_tax_rec.sta_information4
,p_sta_information5 =>l_state_tax_rec.sta_information5
,p_sta_information6 =>l_state_tax_rec.sta_information6
,p_sta_information7 =>l_state_tax_rec.sta_information7
,p_sta_information8 =>l_state_tax_rec.sta_information8
,p_sta_information9 =>l_state_tax_rec.sta_information9
,p_sta_information10 =>l_state_tax_rec.sta_information10
,p_sta_information11 =>l_state_tax_rec.sta_information11
,p_sta_information12 =>l_state_tax_rec.sta_information12
,p_sta_information13 =>l_state_tax_rec.sta_information13
,p_sta_information14 =>l_state_tax_rec.sta_information14
,p_sta_information15 =>l_state_tax_rec.sta_information15
,p_sta_information16 =>l_state_tax_rec.sta_information16
,p_sta_information17 =>l_state_tax_rec.sta_information17
,p_sta_information18 =>l_state_tax_rec.sta_information18
,p_sta_information19 =>l_state_tax_rec.sta_information19
,p_sta_information20 =>l_state_tax_rec.sta_information20
,p_sta_information21 =>l_state_tax_rec.sta_information21
,p_sta_information22 =>l_state_tax_rec.sta_information22
,p_sta_information23 =>l_state_tax_rec.sta_information23
,p_sta_information24 =>l_state_tax_rec.sta_information24
,p_sta_information25 =>l_state_tax_rec.sta_information25
,p_sta_information26 =>l_state_tax_rec.sta_information26
,p_sta_information27 =>l_state_tax_rec.sta_information27
,p_sta_information28 =>l_state_tax_rec.sta_information28
,p_sta_information29 =>l_state_tax_rec.sta_information29
,p_sta_information30 =>l_state_tax_rec.sta_information30
,p_effective_start_date =>ld_start_date
,p_effective_end_date =>ld_end_date
);
-- 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
lv_context := 'W4 State';
-- insert a row in the transaction table
pay_aud_ins.ins(
p_effective_date => l_state_tax_rec.cur_sysdate
,p_transaction_type => lv_trans_type --'ONLINE_TAX_FORMS'
,p_transaction_date => l_state_tax_rec.cur_sysdate
,p_transaction_effective_date => l_state_tax_rec.cur_sysdate
,p_business_group_id => ln_business_group_id
,p_transaction_subtype => 'W4'
,p_person_id => ln_person_id
,p_assignment_id => l_state_tax_rec.assignment_id
,p_source1 => l_state_tax_rec.state_code || '-000-0000'
,p_source1_type => 'JURISDICTION'
,p_source2 => fnd_number.number_to_canonical(l_state_tax_rec.gre_id)
,p_source2_type => 'GRE'
,p_source3 => lv_source_name --'ONLINE W4 FORM'
,p_source3_type => 'SOURCE_NAME'
,p_source4 => p_transaction_id
,p_source4_type => 'TRANSACTION_ID'
,p_audit_information_category => lv_context
,p_audit_information1 => lv_state_filing_status_code
,p_audit_information2 => fnd_number.number_to_canonical(p_allowances)
,p_audit_information3 => fnd_number.number_to_canonical(NVL(l_state_tax_rec.sit_additional_tax, 0))
--,p_audit_information4 => p_exempt_status_code
,p_audit_information4 => NVL(l_state_exempt, 'N') -- Bug# 6333947
,p_transaction_parent_id => ln_parent_audit_id
,p_stat_trans_audit_id => ln_dummy
,p_object_version_number => l_state_tax_rec.object_version_number
);
hr_utility.trace('State Context is : ' || lv_context ||' after insert ');
end if; /* to update state tax Yes or No */
-- all updates and processes have been successful if we are here
-- so we commit
if p_validate then
rollback to update_tax_records;
hr_utility.trace('Leaving ' || gv_package_name || '.update_tax_records');
rollback to update_tax_records;
end update_tax_records;
PROCEDURE update_w4_info(
p_validate in boolean default false ,
p_transaction_step_id in number) IS
Cursor c_trans_value is
select datatype,name,varchar2_value,number_value
from hr_api_transaction_values
where transaction_step_id = p_transaction_step_id
order by transaction_value_id;
select transaction_id, creator_person_id
from hr_api_transaction_steps
where transaction_step_id = p_transaction_step_id;
hr_utility.trace('Entering package update_w4_info ');
update_tax_records(p_filing_status_code => l_filing_status_code,
p_org_filing_status_code => l_org_filing_status_code,
p_allowances => l_allowances,
p_org_allowances => l_org_allowances,
p_additional_amount => l_add_tax,
p_last_name_diff => l_last_name_diff, --'N',
p_exempt_status_code => l_exempt,
p_org_exempt_status_code => l_org_exempt,
p_transaction_id => l_transaction_id,
p_person_id => l_person_id,
p_transaction_type => lv_trans_type,
p_source_name => lv_source_name,
p_validate => p_validate);
hr_utility.trace('Leaving package update_w4_info ');
select pus.state_name,
stif.sta_information7,
nvl(stif.sta_information9,'N')
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(p_primary_flag,'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 trunc(sysdate) between stif.effective_start_date and
stif.effective_end_date
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between str.effective_start_date and
str.effective_end_date;
select ppf.business_group_id,
paf.organization_id,
paf.assignment_id,
ppf.person_id,
ppf.employee_number,
ppf.national_identifier,
ppf.full_name
from per_people_f ppf,
per_assignments_f paf
where ppf.person_id = p_person_id
and paf.person_id = ppf.person_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and
ppf.effective_end_date;
select nvl(hoi.ORG_INFORMATION2,'N') ORG_INFORMATION2
from hr_organization_information hoi
where hoi.org_information_context = p_context
and hoi.organization_id = ln_organization_id;