[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert_row
Update_row
Lock_row
Delete_row
check_unique
get_set_def
NOV-09-1993 RMAMGAIN 40.1 Created Procs to call
call element entry API.
NOV-14-1993 RMAMGAIN 40.2 Changes to create Ele. Ent.
for PAY VALUE.
NOV-21-1993 RMAMGAIN 40.5 Change Time in state and
withholding allow. Columns
SEP-28-1994 RMAMGAIN 40.3 BUG 1257, BUG1257
MAR-06-1995 GPAYTONM 40.9 Added more user friendly error messages
for uniqueness check
(HR_7322_TAX_ONE_RULE_ONLY)
26-APR-95 gpaytonm 40.11 Added function call to addr_val to get geo code
22-JUN-95 gpaytonm 40.12 Changed insert_def_loc to insert default resident county
record and default school district where appropriate.
Changed references to PAY_US_CITIES to PAY_US_CITY_NAMES
+ PAY_US_ZIP_CODES
12-JUL-95 gpaytonm 40.13 Made sure that vertex tax entry not inserted
for default local county tax record.
25-JUL-95 AForte Changed tokenised message
HR_7322_TAX_ONE_RULE_ONLY
to hard coded messages
HR_7719_TAX_ONE_RULE_ONLY
HR_7720_TAX_ONE_RULE_ONLY and
Changed tokenised message
HR_13140_TAX_ELEMNT_ERROR where token was
'Workers Compensation', to
HR_7713_TAX_ELEMENT_ERROR
03-AUG-95 gpaytonm 40.14 Changed get_set_Def to default_tax. Added
on_insert to be called from form such that
defaulting VERTEX entries is easier to
control - on_insert always creates one,
but when defaulting want may not want
a vertex record but do want a tax record.
Added code to cater for unknown cities -
inserts county record in this case. Altered
code to only insert one vertex entry when
work and res cities are the same.
Renamed create_tax_ele_Entry to create_vertex_entry
28-SEP-95 gpaytonm 40.16 Added COMMIT in default_tax if no error
- rather than committing on form
22-AUG-96 lwthomps 40.18 Added 0 default values in defualt tax
for state and federal supplemental
tax override rates. #316663
10-SEP-96 lwthomps 40.19 Now a county tax record is created
for both the work and resident
locality. #391886
16-SEP-96 lwthomps 40.20 #390941 version 40.19skipped for
P1 fix. 40.20 is 40.18 with changes.
Now it will only create county record
after it checks one doesn't already
exists.
17-SEP-96 lwthomps 40.21 #390941 removed unwanted dependency on
316663.
23-SEP-96 lwthomps 40.22 This merges all changes from 40.19
and 40.20. Also adds new Procedure called
Create_County_Record that is called from
form and creates a county record if a
city record is created through the form.
11-NOV-96 lwthomps 40.23 Adds a call to PAY_ASG_GEO_PKG.create_asg_geo_row.
This is dependent on the existence of the
new table: pay_us_asg_reporting.
BUG: 420465.
13-Jan-97 lwthomps 40.24 School Ditrict Changes:
No longer allows the same school district code
in multiple cities in the same state. SD codes
are only defaulted for Primary residences now,
and if that school district exists somewhere else
(another locality for this assignment) then it
is set to NULL.
Added Procedure Update_attribute to maintain
SD codes.
12-Feb-97 lwthomps 40.25 Leap Frog Version
20-Feb-97 lwthomps 40.26 Merged changes from preivous leap
frogged versions.
20-Feb-97 lwthomps 40.27 Defaulting of tax rules. Now creates
records from the assignment and
address forms. 268389
05-Mar-97 jalloun 40.28 Changed all calls to sys.dual to
new standard.
07-Mar-97 lwthomps 40.29 Defaulting of tax rules.
Now catches filing status and
allowances from the Federal record
if it is specified at the GRE level
and there is an appropriate filing
status for the state:
FIT SIT
---------------- -------------
Single -> Single
Married Joint -> Married
Married Sep -> Married
Head of House -> Head of House
07-Mar-97 lwthomps 40.30 Cleaned up change log.
08-Mar-97 lwthomps 40.31 268389. Added check for when
state tax rules had been defined
but no default standard chosen.
31-Mar-97 lwthomps 40.32 268389. Added more messages
for the assignment form in default
tax with validation. Changed
the date effective sections in
create_vertex_entry to ensure
duplicate records are not created
in cases where people move into
a jurisdiction earlier than the
original startdate of the element
entry.
19-May-97 lwthomps 40.33 495165. Added additional validation
to default_tax_with validation such
that it will not update the percent time
in state if the sum accross existing
records is not = 0%.
05-JUN-97 lwthomps 40.34 Created an overloaded version of
default tax such that the package
only commits when entered from the
Tax Rules Form.
This is to fix: 501979
16-JUL-97 lwthomps 40.35 Added additional validation in the
create_vertex_entry procedure so that
it checks that a vertex element entry
of the same jurisdiction does not exist
prior to inserting. Previously this
validation was done on the
per_assignment_extra_information records
only.
07-NOV-97 lwthomps 40.36 Added additional erroring for
invalid resident address and
existence of tax records. If
a tax record has been deleted
and the element_entry is modified
it will roll back and error.
07-NOV-97 lwthomps 40.37 Added one more check for misc
error raised by other packages.
18-MAY-98 ekim 40.39 Bug #657312. Modified csr_qualify_info
to validate against the date.
21-Apr-99 scgrant 115.1 Multi-radix changes.
14-Feb-00 alogue 115.4 Utf8 support.
18-JAN-02 fusman 115.5 Added dbdrv command.
24-JUN-02 rsirigir 115.8 Modified checkfile syntax as
per bug 2429703
09-AUG-02 ahanda 115.12 Changed cursor vtx_info.
21-JAN-04 saurgupt 115.13 Bug 3354046: Changed definitions of
cursors csr_filing_status, csr_wc_element
and csr_fed_or_def to remove FTS and MJC.
*/
--
PROCEDURE Insert_Row(X_Rowid IN OUT nocopy VARCHAR2,
X_Assignment_Extra_Info_Id IN OUT nocopy NUMBER,
X_Assignment_Id NUMBER,
X_Information_Type VARCHAR2,
X_session_date DATE,
X_jurisdiction VARCHAR2,
X_Aei_Information_Category VARCHAR2 default null,
X_Aei_Information1 VARCHAR2 default null,
X_Aei_Information2 VARCHAR2 default null,
X_Aei_Information3 VARCHAR2 default null,
X_Aei_Information4 VARCHAR2 default null,
X_Aei_Information5 VARCHAR2 default null,
X_Aei_Information6 VARCHAR2 default null,
X_Aei_Information7 VARCHAR2 default null,
X_Aei_Information8 VARCHAR2 default null,
X_Aei_Information9 VARCHAR2 default null,
X_Aei_Information10 VARCHAR2 default null,
X_Aei_Information11 VARCHAR2 default null,
X_Aei_Information12 VARCHAR2 default null,
X_Aei_Information13 VARCHAR2 default null,
X_Aei_Information14 VARCHAR2 default null,
X_Aei_Information15 VARCHAR2 default null,
X_Aei_Information16 VARCHAR2 default null,
X_Aei_Information17 VARCHAR2 default null,
X_Aei_Information18 VARCHAR2 default null,
X_Aei_Information19 VARCHAR2 default null,
X_Aei_Information20 VARCHAR2 default null
) IS
X_User_Id NUMBER;
CURSOR C IS SELECT rowid FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE assignment_extra_info_id = X_Assignment_Extra_Info_Id;
CURSOR C2 IS SELECT per_assignment_extra_info_s.nextval FROM sys.dual;
SELECT rowid
FROM per_assignment_extra_info
WHERE assignment_id = X_Assignment_Id
AND aei_information9 IS NOT NULL
AND Information_type = 'LOCALITY'
AND aei_information9 = X_Aei_Information9
AND aei_information1 = X_aei_information1
AND assignment_extra_info_id <> X_assignment_extra_info_id;
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',1);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',2);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',3);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',4);
INSERT INTO PER_ASSIGNMENT_EXTRA_INFO(
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
assignment_extra_info_id,
assignment_id,
information_type,
aei_information_category,
aei_information1,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
aei_information8,
aei_information9,
aei_information10,
aei_information11,
aei_information12,
aei_information13,
aei_information14,
aei_information15,
aei_information16,
aei_information17,
aei_information18,
aei_information19,
aei_information20)
VALUES (
SYSDATE,
X_User_Id,
SYSDATE,
X_User_Id,
X_Login_Id,
X_Assignment_Extra_Info_Id,
X_Assignment_Id,
X_Information_Type,
X_Aei_Information_Category,
X_Aei_Information1,
X_Aei_Information2,
X_Aei_Information3,
X_Aei_Information4,
X_Aei_Information5,
X_Aei_Information6,
X_Aei_Information7,
X_Aei_Information8,
X_Aei_Information9,
X_Aei_Information10,
X_Aei_Information11,
X_Aei_Information12,
X_Aei_Information13,
X_Aei_Information14,
X_Aei_Information15,
X_Aei_Information16,
X_Aei_Information17,
X_Aei_Information18,
X_Aei_Information19,
X_Aei_Information20
);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',5);
hr_utility.set_message_token('PROCEDURE','US_EMP_TAX.INSERT_ROW');
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',6);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',7);
Update_Attribute(p_rowid => x_sd_rowid,
p_attribute_type => 'SCHOOL',
p_new_value => NULL,
p_jurisdiction => NULL,
p_state_abbrev => NULL,
p_assignment_id => NULL);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',9);
END Insert_Row;
SELECT *
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE rowid = X_Rowid
FOR UPDATE of Assignment_Extra_Info_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_assignment_id NUMBER,
X_information_type VARCHAR2,
X_session_date DATE,
X_jurisdiction VARCHAR2,
X_Aei_Information1 VARCHAR2 default null,
X_Aei_Information2 VARCHAR2 default null,
X_Aei_Information3 VARCHAR2 default null,
X_Aei_Information4 VARCHAR2 default null,
X_Aei_Information5 VARCHAR2 default null,
X_Aei_Information6 VARCHAR2 default null,
X_Aei_Information7 VARCHAR2 default null,
X_Aei_Information8 VARCHAR2 default null,
X_Aei_Information9 VARCHAR2 default null,
X_Aei_Information10 VARCHAR2 default null,
X_Aei_Information11 VARCHAR2 default null,
X_Aei_Information12 VARCHAR2 default null,
X_Aei_Information13 VARCHAR2 default null,
X_Aei_Information14 VARCHAR2 default null,
X_Aei_Information15 VARCHAR2 default null,
X_Aei_Information16 VARCHAR2 default null,
X_Aei_Information17 VARCHAR2 default null,
X_Aei_Information18 VARCHAR2 default null,
X_Aei_Information19 VARCHAR2 default null,
X_Aei_Information20 VARCHAR2 default null
) IS
X_User_Id NUMBER;
SELECT rowid
FROM per_assignment_extra_info
WHERE assignment_id = X_Assignment_Id
AND X_Aei_Information9 IS NOT NULL
AND Information_type = 'LOCALITY'
AND aei_information9 = X_Aei_Information9
AND aei_information1 = X_aei_information1
AND rowid <> X_rowid;
UPDATE PER_ASSIGNMENT_EXTRA_INFO
SET
last_updated_by = X_User_Id,
last_update_login = X_Login_Id,
aei_information1 = X_Aei_Information1,
aei_information2 = X_Aei_Information2,
aei_information3 = X_Aei_Information3,
aei_information4 = X_Aei_Information4,
aei_information5 = X_Aei_Information5,
aei_information6 = X_Aei_Information6,
aei_information7 = X_Aei_Information7,
aei_information8 = X_Aei_Information8,
aei_information9 = X_Aei_Information9,
aei_information10 = X_Aei_Information10,
aei_information11 = X_Aei_Information11,
aei_information12 = X_Aei_Information12,
aei_information13 = X_Aei_Information13,
aei_information14 = X_Aei_Information14,
aei_information15 = X_Aei_Information15,
aei_information16 = X_Aei_Information16,
aei_information17 = X_Aei_Information17,
aei_information18 = X_Aei_Information18,
aei_information19 = X_Aei_Information19,
aei_information20 = X_Aei_Information20
WHERE rowid = X_rowid;
Update_Attribute(p_rowid => x_sd_rowid,
p_attribute_type => 'SCHOOL',
p_new_value => NULL,
p_jurisdiction => NULL,
p_state_abbrev => NULL,
p_assignment_id => NULL);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE rowid = X_Rowid;
END Delete_Row;
select 1 from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and information_type = X_information_type;
select 1 from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and information_type = X_information_type
and Aei_Information1 = X_state_code;
select 1 from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and information_type = X_information_type
and Aei_Information1 = X_state_code
and Aei_Information2 = X_locality_code;
select 1
from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and INFORMATION_TYPE = 'FEDERAL';
select 1
from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and INFORMATION_TYPE = 'STATE'
and AEI_INFORMATION1 = P_state;
select ROWID, AEI_INFORMATION1
from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and INFORMATION_TYPE = 'STATE'
and fnd_number.canonical_to_number(AEI_INFORMATION13) = 100;
select 1
from PER_ASSIGNMENT_EXTRA_INFO
where assignment_id = X_assignment_id
and INFORMATION_TYPE = 'LOCALITY'
and AEI_INFORMATION1 = P_state
and AEI_INFORMATION2 = P_local;
select psr.name,
psr.state_code,
psr.state_code,
pa.town_or_city,
pa.town_or_city,
pa.region_1,
pa.postal_code
from PER_ASSIGNMENTS_F paf,
PER_ADDRESSES pa,
PAY_STATE_RULES psr
where paf.assignment_id = P_assignment_id
and P_session_date between paf.effective_start_date and
paf.effective_end_date
and paf.person_id = pa.person_id
and pa.primary_flag = 'Y'
and P_session_date between pa.date_from and
nvl(pa.date_to,P_session_date)
and psr.state_code = pa.region_2;
select psr.name,
psr.state_code,
psr.state_code,
hl.region_1,
hl.postal_code,
psr.jurisdiction_code,
hl.town_or_city,
hl.town_or_city
from PER_ASSIGNMENTS_F paf,
HR_LOCATIONS hl,
PAY_STATE_RULES psr
where paf.assignment_id = P_assignment_id
and P_session_date between paf.effective_start_date and
paf.effective_end_date
and paf.location_id = hl.location_id
and psr.state_code = hl.region_2;
FUNCTION insert_def_fed_rec(P_assignment_id NUMBER,
p_session_date DATE,
P_sui_state VARCHAR2)
RETURN NUMBER IS
--
p_filing_status varchar2(2);
select lookup_code
from hr_lookups
where lookup_type = 'US_FIT_FILING_STATUS'
and upper(meaning) = 'SINGLE';
select lookup_code
from hr_lookups
where lookup_type = 'US_EIC_FILING_STATUS'
and upper(meaning) = 'NO EIC';
PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
X_Rowid => P_temp,
X_Assignment_Extra_Info_Id => P_id,
X_Assignment_Id => P_assignment_id,
X_Information_Type => 'FEDERAL',
X_session_date => P_session_date,
X_jurisdiction => null,
X_Aei_Information_Category => 'FEDERAL',
X_Aei_Information1 => '0',
X_Aei_Information2 => '0',
X_Aei_Information3 => Null,
X_Aei_Information4 => P_filing_status,
X_Aei_Information5 => '0',
X_Aei_Information6 => 'N',
X_Aei_Information7 => 'N',
X_Aei_Information8 => 'N',
X_Aei_Information9 => 'N',
X_Aei_Information10 => 'N',
X_Aei_Information11 => 'N',
X_Aei_Information12 => P_eic_fstatus,
X_Aei_Information13 => Null,
X_Aei_Information14 => '0',
X_Aei_Information15 => '0',
X_Aei_Information16 => Null,
X_Aei_Information17 => '0',/*316663 default supp override=0*/
X_Aei_Information18 => P_sui_state,
X_Aei_Information19 => Null,
X_Aei_Information20 => Null);
END insert_def_fed_rec;
FUNCTION insert_def_state_rec(P_assignment_id NUMBER,
P_state_code VARCHAR2,
P_session_date DATE,
P_time_in_state VARCHAR2,
P_remainder VARCHAR2)
RETURN NUMBER IS
--
P_temp varchar2(30);
select jurisdiction_code
from pay_state_rules
where state_code = P_state_code;
select hl.lookup_code, peft.withholding_allowances -- Bug 3354046: Table pay_us_states is added to remove 2 MJC and FTS on table
from hr_lookups hl, -- pay_state_rules(table in def. of view pay_emp_fed_tax_v1)
pay_emp_fed_tax_v1 peft,
pay_us_states pus
where hl.lookup_type = 'US_FS_'||substr(p_jurisdiction,1,2)
and upper(hl.meaning) = decode(
upper(substr(peft.filing_status,1,7)),
'MARRIED',
'MARRIED',
upper(peft.filing_status))
and peft.assignment_id = p_assignment_id
and pus.state_code = substr(peft.sui_jurisdiction_code,1,2)
and pus.state_abbrev = peft.sui_state_code
and pus.state_name = peft.sui_state_name;
select /*+ index (sr PAY_STATE_RULES_PK) -- MJC.
ordered */hoi.org_information12
from per_assignments_f paf,
hr_soft_coding_keyflex hsck,
hr_organization_information hoi ,
pay_state_rules sr
where paf.assignment_id = p_assignment_id
and SR.state_code = hoi.org_information1
and hoi.organization_id = hsck.segment1
and hoi.org_information_context = 'State Tax Rules'
and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
and sr.jurisdiction_code = substr(p_jurisdiction,1,2)||'-000-0000';
PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
X_Rowid => P_temp,
X_Assignment_Extra_Info_Id => P_id,
X_Assignment_Id => P_assignment_id,
X_Information_Type => 'STATE',
X_session_date => P_session_date,
X_jurisdiction => P_jurisdiction,
X_Aei_Information_Category => 'STATE',
X_Aei_Information1 => P_state_code,
X_Aei_Information2 => l_filing_status,
X_Aei_Information3 => 'N',
X_Aei_Information4 => 'N',
X_Aei_Information5 => 'N',
X_Aei_Information6 => 'N',
X_Aei_Information7 => 'Y',
X_Aei_Information8 => '0',
X_Aei_Information9 => '0',
X_Aei_Information10 => fnd_number.number_to_canonical(l_allowances),
X_Aei_Information11 => '0',
X_Aei_Information12 => Null,
X_Aei_Information13 => P_time_in_state,
X_Aei_Information14 => '0',
X_Aei_Information15 => '0',
X_Aei_Information16 => P_remainder,
X_Aei_Information17 => Null,
X_Aei_Information18 => '0', /*316663 Supp Override rate =0*/
X_Aei_Information19 => Null,
X_Aei_Information20 => Null);
END insert_def_state_rec;
FUNCTION insert_def_loc_rec(P_assignment_id NUMBER,
P_state_code VARCHAR2,
P_session_date DATE,
P_locality_code VARCHAR2,
P_locality_name VARCHAR2,
P_locality_county VARCHAR2,
P_time_in_local VARCHAR2,
p_resident_flag VARCHAR2 DEFAULT 'N')
RETURN NUMBER IS
--
l_filing_status varchar2(2);
select 1
from per_assignment_extra_info
where assignment_id = P_assignment_id
and aei_information2 = Jurisdiction;
select lookup_code
from hr_lookups
where lookup_type = 'US_LIT_FILING_STATUS'
and upper(meaning) = 'SINGLE';
SELECT school_dst_code
FROM pay_us_county_school_dsts
WHERE STATE_CODE = fnd_number.canonical_to_number(substr(P_locality_code,1,2))
AND COUNTY_CODE = fnd_number.canonical_to_number(substr(P_locality_code,4,3))
AND p_resident_flag = 'Y'
ORDER BY fnd_number.canonical_to_number(school_dst_code);
SELECT school_dst_code
FROM pay_us_city_school_dsts
WHERE STATE_CODE = fnd_number.canonical_to_number(substr(P_locality_code,1,2))
AND COUNTY_CODE = fnd_number.canonical_to_number(substr(P_locality_code,4,3))
AND CITY_CODE = fnd_number.canonical_to_number(substr(P_locality_code,8,4))
AND p_resident_flag = 'Y'
ORDER BY fnd_number.canonical_to_number(school_dst_code);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',1);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',2);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',3);
PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
X_Rowid => l_temp,
X_Assignment_Extra_Info_Id => l_id,
X_Assignment_Id => P_assignment_id,
X_Information_Type => 'LOCALITY',
X_session_date => P_session_date,
X_jurisdiction => l_county_locality_code,
X_Aei_Information_Category => 'LOCALITY',
X_Aei_Information1 => P_state_code,
X_Aei_Information2 => l_county_locality_code,
X_Aei_Information3 => l_filing_status,
X_Aei_Information4 => '0',
X_Aei_Information5 => '0',
X_Aei_Information6 => '0',
X_Aei_Information7 => 'N',
X_Aei_Information8 => 'Y',
X_Aei_Information9 => l_county_sd_code,
X_Aei_Information10 => '0',
X_Aei_Information11 => '0',
X_Aei_Information12 => '0',
X_Aei_Information13 => P_locality_county,
X_Aei_Information14 => Null,
X_Aei_Information15 => Null,
X_Aei_Information16 => Null,
X_Aei_Information17 => Null,
X_Aei_Information18 => Null,
X_Aei_Information19 => Null,
X_Aei_Information20 => Null);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',4);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',5);
PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
X_Rowid => l_temp,
X_Assignment_Extra_Info_Id => l_id,
X_Assignment_Id => P_assignment_id,
X_Information_Type => 'LOCALITY',
X_session_date => P_session_date,
X_jurisdiction => P_locality_code,
X_Aei_Information_Category => 'LOCALITY',
X_Aei_Information1 => P_state_code,
X_Aei_Information2 => P_locality_code,
X_Aei_Information3 => l_filing_status,
X_Aei_Information4 => '0',
X_Aei_Information5 => '0',
X_Aei_Information6 => '0',
X_Aei_Information7 => 'N',
X_Aei_Information8 => 'Y',
X_Aei_Information9 => l_city_sd_code,
X_Aei_Information10 => P_time_in_local,
X_Aei_Information11 => '0',
X_Aei_Information12 => '0',
X_Aei_Information13 => P_locality_name,
X_Aei_Information14 => Null,
X_Aei_Information15 => Null,
X_Aei_Information16 => Null,
X_Aei_Information17 => Null,
X_Aei_Information18 => Null,
X_Aei_Information19 => Null,
X_Aei_Information20 => Null);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',6);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',7);
END insert_def_loc_rec;
l_ret := insert_def_fed_rec(X_assignment_id,
X_session_date,
X_work_jurisdiction);
-- if PWS and PRS are different insert one state record for each
--
IF X_res_state_code <> X_work_state_code
THEN
l_ret := insert_def_state_rec(X_assignment_id,
X_work_state_code,
X_session_date,
'100',
l_remainder);
l_ret := insert_def_state_rec(X_assignment_id,
X_res_state_code,
X_session_date,
'0',
'0');
-- insert one state record
--
l_ret := insert_def_state_rec(X_assignment_id,
X_res_state_code,
X_session_date,
'100',
l_remainder);
-- if PW locality and PR locality are different insert one
-- locality record for each
--
IF (X_work_locality <> X_resident_locality)
THEN
l_ret := insert_def_loc_rec(X_assignment_id,
X_work_state_code,
X_session_date,
X_work_locality,
X_work_loc_name,
l_work_county_name, /*391886*/
100);
l_ret := insert_def_loc_rec(X_assignment_id,
X_res_state_code,
X_session_date,
X_resident_locality,
X_resident_loc_name,
l_resident_county_name,
0,
p_resident_flag => 'Y');
l_ret := insert_def_loc_rec(X_assignment_id,
X_res_state_code,
X_session_date,
X_resident_locality,
X_resident_loc_name,
l_resident_county_name,
100,
p_resident_flag => 'Y');
l_ret := insert_def_state_rec(X_assignment_id,
X_work_state_code,
X_session_date,
l_time_in_state,
'0');
l_ret := insert_def_state_rec(X_assignment_id,
X_res_state_code,
X_session_date,
'0',
'0');
l_ret := insert_def_loc_rec(X_assignment_id,
X_res_state_code,
X_session_date,
X_resident_locality,
X_resident_loc_name,
l_resident_county_name,
'0',
p_resident_flag => 'Y');
l_ret := insert_def_loc_rec(X_assignment_id,
X_work_state_code,
X_session_date,
X_work_locality,
X_work_loc_name,
l_work_county_name,/*391886*/
'0');
select pet.ELEMENT_TYPE_ID,
piv.INPUT_VALUE_ID,
piv.NAME
from PAY_ELEMENT_TYPES_F pet,
PAY_INPUT_VALUES_F piv
where PET.ELEMENT_NAME = 'VERTEX'
and P_session_date between pet.effective_start_date and
pet.effective_end_date
and pet.element_type_id = piv.element_type_id
and P_session_date between piv.effective_start_date and
piv.effective_end_date ;
select pee.ELEMENT_ENTRY_ID,
pee.EFFECTIVE_START_DATE
from PAY_ELEMENT_ENTRIES_F pee,
PAY_ELEMENT_ENTRY_VALUES_F peev
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_id
and pee.element_entry_id = peev.element_entry_id
--and P_session_date between peev.effective_start_date and
-- peev.effective_end_date
and peev.input_value_id = P_inp_2
and peev.screen_entry_value = p_jurisdiction;
select effective_start_date
from PER_ASSIGNMENTS_F
where assignment_id = P_assignment_id
and P_session_date between effective_start_date and
effective_end_date;
UPDATE pay_element_entries_f
SET effective_start_date = P_effective_stdt
WHERE element_entry_id = p_element_entry_id;
UPDATE pay_element_entry_values_f
SET effective_start_date = P_effective_stdt
WHERE element_entry_id = p_element_entry_id;
hr_entry_api.insert_element_entry(
p_effective_start_date => P_effective_stdt,
p_effective_end_date => p_effective_end_date,
p_element_entry_id => p_element_entry_id,
p_assignment_id => P_assignment_id,
p_element_link_id => P_element_link_id,
p_creator_type => 'UT',
p_entry_type => 'E',
p_num_entry_values => 3,
p_input_value_id_tbl => inp_value_id_tbl,
p_entry_value_tbl => scr_valuetbl);
hr_entry_api.update_element_entry(
p_dt_update_mode => 'CORRECTION',
p_session_date => P_effective_stdt,
p_element_entry_id => p_element_entry_id,
p_num_entry_values => 3,
p_input_value_id_tbl => inp_value_id_tbl,
p_entry_value_tbl => scr_valuetbl);
select pet.ELEMENT_TYPE_ID, -- Bug 3354046: Upper clause is removed around pet.element_name and WORKERS COMPENSATION
piv.INPUT_VALUE_ID, -- changed to Workers Compensation to remove FTS on pay_element_types_f.
piv.NAME
from PAY_ELEMENT_TYPES_F pet,
PAY_INPUT_VALUES_F piv
where PET.ELEMENT_NAME = 'Workers Compensation'
and P_session_date between pet.effective_start_date and
pet.effective_end_date
and pet.element_type_id = piv.element_type_id
and P_session_date between piv.effective_start_date and
piv.effective_end_date ;
select pee.ELEMENT_ENTRY_ID,
pee.EFFECTIVE_START_DATE
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_id;
select effective_start_date
from PER_ASSIGNMENTS_F
where assignment_id = P_assignment_id
and P_session_date between effective_start_date and
effective_end_date;
hr_entry_api.insert_element_entry(
p_effective_start_date => P_effective_stdt,
p_effective_end_date => p_effective_end_date,
p_element_entry_id => p_element_entry_id,
p_assignment_id => P_assignment_id,
p_element_link_id => P_element_link_id,
p_creator_type => 'UT',
p_entry_type => 'E',
p_num_entry_values => 2,
p_input_value_id_tbl => inp_value_id_tbl,
p_entry_value_tbl => scr_valuetbl);
hr_entry_api.update_element_entry(
p_dt_update_mode => 'CORRECTION',
p_session_date => P_effective_stdt,
p_element_entry_id => p_element_entry_id,
p_num_entry_values => 2,
p_input_value_id_tbl => inp_value_id_tbl,
p_entry_value_tbl => scr_valuetbl);
PROCEDURE on_insert( p_rowid IN OUT nocopy VARCHAR2,
p_Assignment_Extra_Info_Id IN OUT nocopy NUMBER,
p_Assignment_Id NUMBER,
p_Information_Type VARCHAR2,
p_session_date DATE,
p_jurisdiction VARCHAR2,
p_Aei_Information_Category VARCHAR2,
p_Aei_Information1 VARCHAR2,
p_Aei_Information2 VARCHAR2,
p_Aei_Information3 VARCHAR2,
p_Aei_Information4 VARCHAR2,
p_Aei_Information5 VARCHAR2,
p_Aei_Information6 VARCHAR2,
p_Aei_Information7 VARCHAR2,
p_Aei_Information8 VARCHAR2,
p_Aei_Information9 VARCHAR2,
p_Aei_Information10 VARCHAR2,
p_Aei_Information11 VARCHAR2,
p_Aei_Information12 VARCHAR2,
p_Aei_Information13 VARCHAR2,
p_Aei_Information14 VARCHAR2,
p_Aei_Information15 VARCHAR2,
p_Aei_Information16 VARCHAR2,
p_Aei_Information17 VARCHAR2,
p_Aei_Information18 VARCHAR2,
p_Aei_Information19 VARCHAR2,
p_Aei_Information20 VARCHAR2
) IS
BEGIN
--
PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
X_Rowid => p_Rowid ,
X_Assignment_Extra_Info_Id => p_Assignment_Extra_Info_Id,
X_Assignment_Id => p_Assignment_Id ,
X_Information_Type => p_Information_Type ,
X_session_date => p_session_date ,
X_jurisdiction => p_jurisdiction ,
X_Aei_Information_Category => p_Aei_Information_Category,
X_Aei_Information1 => p_Aei_Information1 ,
X_Aei_Information2 => p_Aei_Information2 ,
X_Aei_Information3 => p_Aei_Information3 ,
X_Aei_Information4 => p_Aei_Information4 ,
X_Aei_Information5 => p_Aei_Information5 ,
X_Aei_Information6 => p_Aei_Information6 ,
X_Aei_Information7 => p_Aei_Information7 ,
X_Aei_Information8 => p_Aei_Information8 ,
X_Aei_Information9 => p_Aei_Information9 ,
X_Aei_Information10 => p_Aei_Information10 ,
X_Aei_Information11 => p_Aei_Information11 ,
X_Aei_Information12 => p_Aei_Information12 ,
X_Aei_Information13 => p_Aei_Information13 ,
X_Aei_Information14 => p_Aei_Information14 ,
X_Aei_Information15 => p_Aei_Information15 ,
X_Aei_Information16 => p_Aei_Information16 ,
X_Aei_Information17 => p_Aei_Information17 ,
X_Aei_Information18 => p_Aei_Information18 ,
X_Aei_Information19 => p_Aei_Information19 ,
X_Aei_Information20 => p_Aei_Information20);
hr_utility.set_location('pay_us_emp_tax_rules_pkg.on_insert',11);
END on_insert;
/* This function inserts a county record if a city record is inserted from the form */
Procedure create_county_record( P_Jurisdiction varchar2,
P_assignment_id number,
P_filing_status varchar2,
P_session_date date
)
is
l_assignment_id number;
SELECT school_dst_code
FROM pay_us_county_school_dsts
WHERE STATE_CODE = fnd_number.canonical_to_number(substr(Jurisdiction,1,2))
AND COUNTY_CODE = fnd_number.canonical_to_number(substr(Jurisdiction,4,3))
ORDER BY TO_NUMBER(school_dst_code);
select 1
from per_assignment_extra_info
where assignment_id = P_assignment_id
and aei_information2 = substr(P_jurisdiction,1,6)||'-0000';
select state_abbrev
from pay_us_states
where state_code = fnd_number.canonical_to_number(substr(P_jurisdiction,1,2));
select county_name
from pay_us_counties
where fnd_number.canonical_to_number(substr(P_jurisdiction, 1,2)) = state_code
and fnd_number.canonical_to_number(substr(P_jurisdiction, 4,3)) = county_code;
IF csr_county_for_city%notfound THEN /* insert county record for new city record */
l_assignment_id := p_assignment_id;
/*Insert county record */
PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
X_Rowid => l_temp,
X_Assignment_Extra_Info_Id => l_id,
X_Assignment_Id => l_assignment_id,
X_Information_Type => 'LOCALITY',
X_session_date => l_session_date,
X_jurisdiction => l_county_locality_code,
X_Aei_Information_Category => 'LOCALITY',
X_Aei_Information1 => l_state_code,
X_Aei_Information2 => l_county_locality_code,
X_Aei_Information3 => l_filing_status,
X_Aei_Information4 => '0',
X_Aei_Information5 => '0',
X_Aei_Information6 => '0',
X_Aei_Information7 => 'N',
X_Aei_Information8 => 'Y',
X_Aei_Information9 => l_county_sd_code,
X_Aei_Information10 => '0',
X_Aei_Information11 => '0',
X_Aei_Information12 => '0',
X_Aei_Information13 => l_locality_county,
X_Aei_Information14 => Null,
X_Aei_Information15 => Null,
X_Aei_Information16 => Null,
X_Aei_Information17 => Null,
X_Aei_Information18 => Null,
X_Aei_Information19 => Null,
X_Aei_Information20 => Null);
/* This procedure is used to update single attributes within tax records.*/
/* This was created because often times attributes need to be updated on */
/* records other then the present record on the form. */
/* Attributes to be supported: Percent time, School district code */
---------------------------------------------------------------------------
Procedure Update_Attribute( p_rowid VARCHAR2,
p_attribute_type VARCHAR2,
p_new_value VARCHAR2,
p_jurisdiction VARCHAR2,
p_state_abbrev VARCHAR2,
p_assignment_id NUMBER)
IS
BEGIN
--
hr_utility.set_location('pay_us_emp_tax_rules_pkg.update_attribute '||p_assignment_id||' '||p_jurisdiction,0);
UPDATE per_assignment_extra_info
SET aei_information9 = p_new_value
where rowid = p_rowid;
hr_utility.set_location('pay_us_emp_tax_rules_pkg.update_attribute',1);
UPDATE per_assignment_extra_info
SET aei_information10 = p_new_value
WHERE information_type = 'LOCALITY'
AND aei_information2 = p_jurisdiction
AND assignment_id = p_assignment_id;
UPDATE per_assignment_extra_info
SET aei_information13 = p_new_value
WHERE information_type = 'STATE'
AND aei_information1 = p_state_abbrev
AND assignment_id = p_assignment_id;
hr_utility.set_location('pay_us_emp_tax_rules_pkg.update_attribute',2);
/* for Federal update the SUI state */
UPDATE per_assignment_extra_info
SET aei_information18 = substr(p_jurisdiction,1,2)||'-000-0000'
WHERE information_type = 'FEDERAL'
AND assignment_id = p_assignment_id;
END; /* Update_attribute */
SELECT assignment_id
FROM per_assignments_f
WHERE person_id = per_id
AND assignment_type = 'E'
AND location_id IS NOT NULL
AND payroll_id IS NOT NULL
AND pay_basis_id IS NOT NULL
AND primary_flag = 'Y'
AND p_date between effective_start_date
and effective_end_date;
SELECT paf.assignment_id
FROM per_assignments_f paf,
per_people_f ppf,
per_addresses pa
WHERE paf.person_id = ppf.person_id
AND paf.assignment_type = 'E'
AND ppf.person_id = pa.person_id
AND pa.primary_flag = 'Y'
AND paf.location_id IS NOT NULL
AND paf.payroll_id IS NOT NULL
AND paf.person_id = per_id;
select
psr.state_code,
hl.region_1,
hl.postal_code,
hl.town_or_city
from PER_ASSIGNMENTS_F paf,
HR_LOCATIONS hl,
PAY_STATE_RULES psr
where paf.assignment_id = P_assignment_id
and P_date between paf.effective_start_date and
paf.effective_end_date
and paf.location_id = hl.location_id
and psr.state_code = hl.region_2;
SELECT sum(time_in_state)
FROM pay_emp_state_tax_v1
WHERE assignment_id = p_assignment_id;
Select jurisdiction_code
From pay_emp_local_tax_v1
where assignment_id = p_assignment_id
and jurisdiction_code =p_work_jur;
update_attribute( p_rowid => NULL,
p_attribute_type => 'PERCENT TIME',
p_new_value => '100',
p_jurisdiction => l_work_jurisdiction,
p_state_abbrev => l_work_state_code,
p_assignment_id => l_assignment_id);
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_element_links_f pel,
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 pel.element_type_id = pet.element_type_id
AND peef.element_link_id = pel.element_link_id
AND piv.input_value_id = 0 + peev.input_value_id
AND peev.element_entry_id = peef.element_entry_id
AND p_assignment_id = peef.assignment_id
AND piv.name = 'Jurisdiction' ;
UPDATE per_assignment_extra_info
SET aei_information10 = 0
WHERE assignment_id = p_assignment_id
AND INFORMATION_TYPE = 'LOCALITY';
UPDATE per_assignment_extra_info
SET aei_information13 = 0,
aei_information16 = 0 /* Remainder percent */
WHERE assignment_id = p_assignment_id
AND INFORMATION_TYPE = 'STATE';