[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_mode OUT NOCOPY VARCHAR2,
p_delete_mode OUT NOCOPY VARCHAR2) IS
l_proc VARCHAR2(72) := g_package||'calculate_datetrack_mode';
p_update_mode := NULL;
p_delete_mode := 'X';
p_delete_mode := 'DELETE_NEXT_CHANGE';
p_update_mode := 'CORRECTION';
p_update_mode := 'UPDATE_CHANGE_INSERT';
p_update_mode := 'UPDATE';
SELECT cost_allocation_structure
FROM per_business_groups
WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id;
INSERT INTO hr_h2pi_assignments
(assignment_id,
effective_start_date,
effective_end_date,
last_upd_date,
business_group_id,
client_id,
person_id,
organization_id,
primary_flag)
SELECT asg1.assignment_id,
asg1.end_date+1,
g_eot,
g_eot,
asg.business_group_id,
p_from_client_id,
asg.person_id,
-1,
asg.primary_flag
FROM (SELECT asg2.assignment_id,
MAX(asg2.effective_end_date) end_date
FROM hr_h2pi_assignments asg2
WHERE asg2.client_id = p_from_client_id
GROUP BY asg2.person_id, asg2.assignment_id
HAVING MAX(asg2.effective_end_date) <> g_eot) asg1,
hr_h2pi_assignments asg
WHERE asg.assignment_id = asg1.assignment_id
AND asg.client_id = p_from_client_id
AND asg.effective_end_date = asg1.end_date
AND asg.primary_flag = 'Y';
INSERT INTO hr_h2pi_assignments
(assignment_id,
effective_start_date,
effective_end_date,
last_upd_date,
business_group_id,
client_id,
person_id,
organization_id,
primary_flag)
SELECT asg1.assignment_id,
asg1.end_date+1,
g_eot,
g_eot,
asg.business_group_id,
p_from_client_id,
asg.person_id,
-1,
asg.primary_flag
FROM (SELECT asg2.assignment_id,
MAX(asg2.effective_end_date) end_date
FROM hr_h2pi_assignments asg2
WHERE asg2.client_id = p_from_client_id
GROUP BY asg2.person_id, asg2.assignment_id
HAVING MAX(asg2.effective_end_date) <> g_eot) asg1,
hr_h2pi_assignments asg
WHERE asg.assignment_id = asg1.assignment_id
AND asg.client_id = p_from_client_id
AND asg.effective_end_date = asg1.end_date
AND asg.primary_flag = 'N'
AND NOT EXISTS (SELECT 1
FROM (SELECT sasg2.assignment_id,
MAX(sasg2.effective_end_date) end_date
FROM hr_h2pi_assignments sasg2
WHERE sasg2.client_id = p_from_client_id
GROUP BY sasg2.person_id, sasg2.assignment_id
HAVING MAX(sasg2.effective_end_date) = g_eot) sasg1,
hr_h2pi_assignments sasg
WHERE sasg.assignment_id = sasg1.assignment_id
AND sasg.client_id = p_from_client_id
AND sasg.effective_end_date = sasg1.end_date
AND sasg.last_upd_date = g_eot
AND sasg.primary_flag = 'Y'
AND sasg.effective_start_date = asg.effective_end_date + 1
AND sasg.person_id = asg.person_id);
INSERT INTO hr_h2pi_payment_methods
(personal_payment_method_id,
effective_start_date,
effective_end_date,
last_upd_date,
business_group_id,
client_id,
person_id,
assignment_id,
org_payment_method_id)
SELECT personal_payment_method_id,
MAX(effective_end_date)+1,
g_eot,
g_eot,
business_group_id,
p_from_client_id,
person_id,
-1,
-1
FROM hr_h2pi_payment_methods
WHERE client_id = p_from_client_id
GROUP BY person_id, personal_payment_method_id,business_group_id
HAVING MAX(effective_end_date) <> g_eot;
INSERT INTO hr_h2pi_cost_allocations
(cost_allocation_id,
effective_start_date,
effective_end_date,
last_upd_date,
business_group_id,
client_id,
person_id,
assignment_id,
proportion,
id_flex_num,
summary_flag,
enabled_flag)
SELECT cost_allocation_id,
MAX(effective_end_date)+1,
g_eot,
g_eot,
business_group_id,
p_from_client_id,
person_id,
-1,
-1,
id_flex_num,
summary_flag,
enabled_flag
FROM hr_h2pi_cost_allocations
WHERE client_id = p_from_client_id
GROUP BY person_id, cost_allocation_id,business_group_id,
id_flex_num,summary_flag,enabled_flag
HAVING MAX(effective_end_date) <> g_eot;
INSERT INTO hr_h2pi_element_entries
(element_entry_id,
effective_start_date,
effective_end_date,
last_upd_date,
business_group_id,
client_id,
person_id,
element_link_id,
assignment_id,
creator_type,
entry_type)
SELECT element_entry_id,
MAX(effective_end_date)+1,
g_eot,
g_eot,
business_group_id,
p_from_client_id,
person_id,
-1,
-1,
'x',
'x'
FROM hr_h2pi_element_entries
WHERE client_id = p_from_client_id
AND creator_type <> 'UT'
GROUP BY person_id, element_entry_id,business_group_id
HAVING MAX(effective_end_date) <> g_eot;
DELETE FROM hr_h2pi_employees
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_addresses
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_assignments
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_periods_of_service
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_locations
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_pay_bases
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_hr_organizations
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_organization_class
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_organization_info
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_payrolls
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_element_types
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_input_values
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_element_links
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_bg_and_gre
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_org_payment_methods
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_federal_tax_rules
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_state_tax_rules
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_county_tax_rules
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_city_tax_rules
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_salaries
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_cost_allocations
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_payment_methods
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_element_names
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_element_entries
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_element_entry_values
WHERE client_id = p_from_client_id
AND status = 'C';
DELETE FROM hr_h2pi_bg_and_gre
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_payrolls
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_pay_bases
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_org_payment_methods
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_element_types
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_input_values
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_element_links
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_us_modified_geocodes
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_us_city_names
WHERE client_id = p_from_client_id;
DELETE FROM hr_h2pi_patch_status
WHERE client_id = p_from_client_id;
SELECT DISTINCT person_id
FROM hr_h2pi_employees
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_addresses
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_assignments
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_periods_of_service
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_salaries
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_payment_methods
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_cost_allocations
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_element_entries
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_federal_tax_rules
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_state_tax_rules
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_county_tax_rules
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
UNION
SELECT DISTINCT person_id
FROM hr_h2pi_city_tax_rules
WHERE (status IS NULL OR status <> 'C')
AND client_id = p_bg_id
ORDER BY person_id;
SELECT person_id id,
effective_start_date eff_date,
1 ord,
'upload_person' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_EMPLOYEES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT address_id id,
date_from eff_date,
2 ord,
'upload_address' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_ADDRESSES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT assignment_id id,
effective_start_date eff_date,
3 ord,
'upload_assignment' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_ASSIGNMENTS
WHERE (status IS NULL OR status <> 'C')
AND primary_flag = 'Y'
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT assignment_id id,
effective_start_date eff_date,
4 ord,
'upload_assignment' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_ASSIGNMENTS
WHERE (status IS NULL OR status <> 'C')
AND primary_flag = 'N'
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT period_of_service_id id,
date_start eff_date,
5 ord,
'upload_period_of_service' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_PERIODS_OF_SERVICE
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT pay_proposal_id id,
change_date eff_date,
6 ord,
'upload_salary' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_SALARIES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT personal_payment_method_id id,
effective_start_date eff_date,
7 ord,
'upload_payment_method' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_PAYMENT_METHODS
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT cost_allocation_id id,
effective_start_date eff_date,
8 ord,
'upload_cost_allocation' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_COST_ALLOCATIONS
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT element_entry_id id,
effective_start_date eff_date,
9 ord,
'upload_element_entry' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_ELEMENT_ENTRIES
WHERE (status IS NULL OR status <> 'C')
AND creator_type <> 'UT'
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT emp_fed_tax_rule_id id,
effective_start_date eff_date,
10 ord,
'upload_federal_tax_record' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_FEDERAL_TAX_RULES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT emp_state_tax_rule_id id,
effective_start_date eff_date,
11 ord,
'upload_state_tax_record' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_STATE_TAX_RULES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT emp_county_tax_rule_id id,
effective_start_date eff_date,
12 ord,
'upload_county_tax_record' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_COUNTY_TAX_RULES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
UNION
SELECT emp_city_tax_rule_id id,
effective_start_date eff_date,
13 ord,
'upload_city_tax_record' fn_name,
DECODE(last_upd_date, g_eot, 1, 2) sub_order
FROM HR_H2PI_CITY_TAX_RULES
WHERE (status IS NULL OR status <> 'C')
AND person_id = p_per_id
AND client_id = p_from_client_id
ORDER BY eff_date,
ord,
sub_order;
delete_address(l_from_client_id,
v_per.person_id);
SELECT *
FROM hr_h2pi_employees per
WHERE per.person_id = p_per_id
AND per.client_id = p_from_client_id
AND per.effective_start_date = p_esd;
SELECT per.person_type,
per.effective_start_date,
per.effective_end_date
FROM hr_h2pi_employees_v per
WHERE per.person_id = p_per_id
AND p_esd BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT per.object_version_number
FROM per_all_people_f per
WHERE per.person_id = p_per_id
AND p_esd BETWEEN per.effective_start_date
AND per.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
SELECT asg.assignment_id
INTO l_ud_assignment_id
FROM hr_h2pi_assignments asg
WHERE asg.person_id = v_ud_per.person_id
AND asg.client_id = p_from_client_id
AND asg.effective_start_date = v_ud_per.effective_start_date
AND asg.primary_flag = 'Y';
SELECT pos.period_of_service_id
INTO l_ud_period_of_service_id
FROM hr_h2pi_periods_of_service pos
WHERE pos.person_id = v_ud_per.person_id
AND pos.client_id = p_from_client_id
AND pos.date_start = v_ud_per.effective_start_date;
SELECT pos.period_of_service_id
INTO l_period_of_service_id
FROM hr_h2pi_periods_of_service_v pos
WHERE pos.person_id = l_person_id
AND pos.date_start = l_esd;
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(per.effective_end_date)
INTO l_max_eed
FROM per_all_people_f per
WHERE per.person_id = l_person_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 110);
,p_datetrack_mode => 'DELETE_NEXT_CHANGE');
hr_person_api.update_person(
p_effective_date => v_ud_per.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_person_id => l_person_id
,p_object_version_number => l_ovn
,p_last_name => v_ud_per.last_name
,p_date_of_birth => v_ud_per.date_of_birth
,p_email_address => v_ud_per.email_address
,p_employee_number => v_ud_per.employee_number
,p_expense_check_send_to_addres
=> v_ud_per.expense_check_send_to_address
,p_first_name => v_ud_per.first_name
,p_marital_status => v_ud_per.marital_status
,p_middle_names => v_ud_per.middle_names
-- ,p_nationality => v_ud_per.nationality
,p_national_identifier => v_ud_per.national_identifier
,p_registered_disabled_flag => v_ud_per.registered_disabled_flag
,p_sex => v_ud_per.sex
,p_title => v_ud_per.title
,p_attribute_category => v_ud_per.attribute_category
,p_attribute1 => v_ud_per.attribute1
,p_attribute2 => v_ud_per.attribute2
,p_attribute3 => v_ud_per.attribute3
,p_attribute4 => v_ud_per.attribute4
,p_attribute5 => v_ud_per.attribute5
,p_attribute6 => v_ud_per.attribute6
,p_attribute7 => v_ud_per.attribute7
,p_attribute8 => v_ud_per.attribute8
,p_attribute9 => v_ud_per.attribute9
,p_attribute10 => v_ud_per.attribute10
,p_attribute11 => v_ud_per.attribute11
,p_attribute12 => v_ud_per.attribute12
,p_attribute13 => v_ud_per.attribute13
,p_attribute14 => v_ud_per.attribute14
,p_attribute15 => v_ud_per.attribute15
,p_attribute16 => v_ud_per.attribute16
,p_attribute17 => v_ud_per.attribute17
,p_attribute18 => v_ud_per.attribute18
,p_attribute19 => v_ud_per.attribute19
,p_attribute20 => v_ud_per.attribute20
,p_attribute21 => v_ud_per.attribute21
,p_attribute22 => v_ud_per.attribute22
,p_attribute23 => v_ud_per.attribute23
,p_attribute24 => v_ud_per.attribute24
,p_attribute25 => v_ud_per.attribute25
,p_attribute26 => v_ud_per.attribute26
,p_attribute27 => v_ud_per.attribute27
,p_attribute28 => v_ud_per.attribute28
,p_attribute29 => v_ud_per.attribute29
,p_attribute30 => v_ud_per.attribute30
,p_per_information_category => v_ud_per.per_information_category
,p_per_information1 => v_ud_per.per_information1
,p_per_information2 => v_ud_per.per_information2
,p_per_information3 => v_ud_per.per_information3
,p_per_information4 => v_ud_per.per_information4
,p_per_information5 => v_ud_per.per_information5
,p_per_information6 => v_ud_per.per_information6
,p_per_information7 => v_ud_per.per_information7
,p_per_information8 => v_ud_per.per_information8
,p_per_information9 => v_ud_per.per_information9
,p_per_information10 => v_ud_per.per_information10
,p_per_information11 => v_ud_per.per_information11
,p_per_information12 => v_ud_per.per_information12
,p_per_information13 => v_ud_per.per_information13
,p_per_information14 => v_ud_per.per_information14
,p_per_information15 => v_ud_per.per_information15
,p_per_information16 => v_ud_per.per_information16
,p_per_information17 => v_ud_per.per_information17
,p_per_information18 => v_ud_per.per_information18
,p_per_information19 => v_ud_per.per_information19
,p_per_information20 => v_ud_per.per_information20
,p_per_information21 => v_ud_per.per_information21
,p_per_information22 => v_ud_per.per_information22
,p_per_information23 => v_ud_per.per_information23
,p_per_information24 => v_ud_per.per_information24
,p_per_information25 => v_ud_per.per_information25
,p_per_information26 => v_ud_per.per_information26
,p_per_information27 => v_ud_per.per_information27
,p_per_information28 => v_ud_per.per_information28
,p_per_information29 => v_ud_per.per_information29
,p_per_information30 => v_ud_per.per_information30
,p_date_of_death => v_ud_per.date_of_death
,p_correspondence_language => v_ud_per.correspondence_language
,p_office_number => v_ud_per.office_number
,p_pre_name_adjunct => v_ud_per.pre_name_adjunct
,p_suffix => v_ud_per.suffix
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_full_name => l_full_name
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
SELECT asg.assignment_id
INTO l_ud_assignment_id
FROM hr_h2pi_assignments asg
WHERE asg.person_id = v_ud_per.person_id
AND asg.client_id = p_from_client_id
AND asg.effective_start_date = v_ud_per.effective_start_date
AND asg.primary_flag = 'Y';
SELECT pos.period_of_service_id
INTO l_ud_period_of_service_id
FROM hr_h2pi_periods_of_service pos
WHERE pos.person_id = v_ud_per.person_id
AND pos.client_id = p_from_client_id
AND pos.date_start = v_ud_per.effective_start_date ;
SELECT pos.period_of_service_id
INTO l_period_of_service_id
FROM hr_h2pi_periods_of_service_v pos
WHERE pos.person_id = l_person_id
AND pos.date_start = l_esd ;
UPDATE hr_h2pi_employees per
SET status = 'C'
WHERE per.person_id = v_ud_per.person_id
AND per.client_id = p_from_client_id
AND per.effective_start_date = v_ud_per.effective_start_date
AND per.effective_end_date = v_ud_per.effective_end_date;
SELECT *
FROM hr_h2pi_periods_of_service pos
WHERE pos.person_id = p_per_id
AND pos.client_id = p_from_client_id
AND pos.actual_termination_date = p_esd - 1;
SELECT pos.object_version_number,
pos.actual_termination_date,
pos.final_process_date
FROM per_periods_of_service pos
WHERE pos.period_of_service_id = p_pos_id;
PROCEDURE delete_address (p_from_client_id NUMBER,
p_person_id NUMBER) IS
CURSOR csr_ud_address (p_adr_id NUMBER) IS
SELECT address_id,
date_from,
date_to
FROM hr_h2pi_addresses adr
WHERE adr.address_id = p_adr_id
AND adr.client_id = p_from_client_id
AND (adr.status IS NULL OR adr.status <> 'C');
SELECT address_id,
object_version_number,
date_from,
date_to
FROM per_addresses adr
WHERE adr.person_id = p_per_id;
l_proc VARCHAR2(72) := g_package||'delete_address';
DELETE FROM hr_h2pi_id_mapping
WHERE table_name = 'PER_ADDRESSES'
AND to_id = v_ed_adr.address_id
AND to_business_group_id = hr_h2pi_upload.g_to_business_group_id;
SELECT *
FROM hr_h2pi_addresses adr
WHERE adr.address_id = p_adr_id
AND adr.client_id = p_from_client_id;
SELECT object_version_number
FROM per_addresses adr
WHERE adr.address_id = p_adr_id;
SELECT MIN(per.effective_start_date)
INTO l_per_start_date
FROM per_all_people_f per
WHERE per.person_id = l_person_id;
hr_person_address_api.update_person_address(
p_effective_date => v_ud_adr.date_from
,p_address_id => l_address_id
,p_object_version_number => l_ovn
,p_date_from => v_ud_adr.date_from
,p_date_to => v_ud_adr.date_to
-- ,p_address_type => v_ud_adr.address_type
,p_address_line1 => v_ud_adr.address_line1
,p_address_line2 => v_ud_adr.address_line2
,p_address_line3 => v_ud_adr.address_line3
,p_town_or_city => v_ud_adr.town_or_city
,p_region_1 => v_ud_adr.region_1
,p_region_2 => v_ud_adr.region_2
,p_region_3 => v_ud_adr.region_3
,p_postal_code => v_ud_adr.postal_code
,p_country => v_ud_adr.country
,p_telephone_number_1 => v_ud_adr.telephone_number_1
,p_telephone_number_2 => v_ud_adr.telephone_number_2
,p_telephone_number_3 => v_ud_adr.telephone_number_3
,p_add_information17 => v_ud_adr.add_information17
,p_add_information18 => v_ud_adr.add_information18
,p_add_information19 => v_ud_adr.add_information19
,p_add_information20 => v_ud_adr.add_information20
,p_addr_attribute_category => v_ud_adr.addr_attribute_category
,p_addr_attribute1 => v_ud_adr.addr_attribute1
,p_addr_attribute2 => v_ud_adr.addr_attribute2
,p_addr_attribute3 => v_ud_adr.addr_attribute3
,p_addr_attribute4 => v_ud_adr.addr_attribute4
,p_addr_attribute5 => v_ud_adr.addr_attribute5
,p_addr_attribute6 => v_ud_adr.addr_attribute6
,p_addr_attribute7 => v_ud_adr.addr_attribute7
,p_addr_attribute8 => v_ud_adr.addr_attribute8
,p_addr_attribute9 => v_ud_adr.addr_attribute9
,p_addr_attribute10 => v_ud_adr.addr_attribute10
,p_addr_attribute11 => v_ud_adr.addr_attribute11
,p_addr_attribute12 => v_ud_adr.addr_attribute12
,p_addr_attribute13 => v_ud_adr.addr_attribute13
,p_addr_attribute14 => v_ud_adr.addr_attribute14
,p_addr_attribute15 => v_ud_adr.addr_attribute15
,p_addr_attribute16 => v_ud_adr.addr_attribute16
,p_addr_attribute17 => v_ud_adr.addr_attribute17
,p_addr_attribute18 => v_ud_adr.addr_attribute18
,p_addr_attribute19 => v_ud_adr.addr_attribute19
,p_addr_attribute20 => v_ud_adr.addr_attribute20
);
UPDATE hr_h2pi_addresses adr
SET status = 'C'
WHERE adr.address_id = v_ud_adr.address_id
AND adr.client_id = p_from_client_id;
SELECT *
FROM hr_h2pi_assignments asg
WHERE asg.assignment_id = p_asg_id
AND asg.client_id = p_from_client_id
AND asg.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_assignments_v asg
WHERE asg.assignment_id = p_asg_id
AND p_esd BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT asg.object_version_number
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_asg_id
AND p_esd BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT pos.object_version_number,
pos.period_of_service_id
FROM per_all_assignments_f asg,
per_all_people_f per,
per_periods_of_service pos
WHERE asg.assignment_id = p_asg_id
AND p_esd BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.person_id = per.person_id
AND p_esd BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.person_id = pos.person_id
AND pos.actual_termination_date = per.effective_start_date - 1;
SELECT emp_state_tax_rule_id,
jurisdiction_code
FROM hr_h2pi_state_tax_rules_v
WHERE assignment_id = p_asg_id
AND p_date BETWEEN effective_start_date
AND effective_end_date;
SELECT emp_county_tax_rule_id,
jurisdiction_code
FROM hr_h2pi_county_tax_rules_v
WHERE assignment_id = p_asg_id
AND p_date BETWEEN effective_start_date
AND effective_end_date;
SELECT emp_city_tax_rule_id,
jurisdiction_code
FROM hr_h2pi_city_tax_rules_v
WHERE assignment_id = p_asg_id
AND p_date BETWEEN effective_start_date
AND effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_spp_delete_warning BOOLEAN;
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(asg.effective_end_date)
INTO l_max_eed
FROM per_all_assignments_f asg
WHERE asg.assignment_id = l_assignment_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 134);
SELECT DISTINCT person_type
INTO l_dummy_person_type
FROM hr_h2pi_employees_v
WHERE person_id = l_person_id
AND effective_start_date < v_ud_asg.effective_end_date
AND effective_end_date > v_ud_asg.effective_start_date;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
,p_datetrack_mode => 'DELETE_NEXT_CHANGE');
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
hr_assignment_api.update_emp_asg(
p_effective_date => v_ud_asg.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_ovn
,p_assignment_number => v_ud_asg.assignment_number
,p_frequency => v_ud_asg.frequency
,p_normal_hours => v_ud_asg.normal_hours
,p_hourly_salaried_code => v_ud_asg.hourly_salaried_code
-- ,p_source_type => v_ud_asg.source_type
,p_time_normal_finish => v_ud_asg.time_normal_finish
,p_time_normal_start => v_ud_asg.time_normal_start
,p_ass_attribute_category => v_ud_asg.ass_attribute_category
,p_ass_attribute1 => v_ud_asg.ass_attribute1
,p_ass_attribute2 => v_ud_asg.ass_attribute2
,p_ass_attribute3 => v_ud_asg.ass_attribute3
,p_ass_attribute4 => v_ud_asg.ass_attribute4
,p_ass_attribute5 => v_ud_asg.ass_attribute5
,p_ass_attribute6 => v_ud_asg.ass_attribute6
,p_ass_attribute7 => v_ud_asg.ass_attribute7
,p_ass_attribute8 => v_ud_asg.ass_attribute8
,p_ass_attribute9 => v_ud_asg.ass_attribute9
,p_ass_attribute10 => v_ud_asg.ass_attribute10
,p_ass_attribute11 => v_ud_asg.ass_attribute11
,p_ass_attribute12 => v_ud_asg.ass_attribute12
,p_ass_attribute13 => v_ud_asg.ass_attribute13
,p_ass_attribute14 => v_ud_asg.ass_attribute14
,p_ass_attribute15 => v_ud_asg.ass_attribute15
,p_ass_attribute16 => v_ud_asg.ass_attribute16
,p_ass_attribute17 => v_ud_asg.ass_attribute17
,p_ass_attribute18 => v_ud_asg.ass_attribute18
,p_ass_attribute19 => v_ud_asg.ass_attribute19
,p_ass_attribute20 => v_ud_asg.ass_attribute20
,p_ass_attribute21 => v_ud_asg.ass_attribute21
,p_ass_attribute22 => v_ud_asg.ass_attribute22
,p_ass_attribute23 => v_ud_asg.ass_attribute23
,p_ass_attribute24 => v_ud_asg.ass_attribute24
,p_ass_attribute25 => v_ud_asg.ass_attribute25
,p_ass_attribute26 => v_ud_asg.ass_attribute26
,p_ass_attribute27 => v_ud_asg.ass_attribute27
,p_ass_attribute28 => v_ud_asg.ass_attribute28
,p_ass_attribute29 => v_ud_asg.ass_attribute29
,p_ass_attribute30 => v_ud_asg.ass_attribute30
,p_title => v_ud_asg.title
,p_segment1 => v_ud_asg.segment1
-- ,p_segment2 => v_ud_asg.segment2
,p_segment3 => v_ud_asg.segment3
-- ,p_segment4 => v_ud_asg.segment4
,p_segment5 => v_ud_asg.segment5
,p_segment6 => v_ud_asg.segment6
,p_segment7 => v_ud_asg.segment7
,p_segment8 => v_ud_asg.segment8
,p_segment9 => v_ud_asg.segment9
,p_segment10 => v_ud_asg.segment10
,p_segment11 => v_ud_asg.segment11
,p_segment12 => v_ud_asg.segment12
,p_segment13 => v_ud_asg.segment13
,p_segment14 => v_ud_asg.segment14
,p_segment15 => v_ud_asg.segment15
,p_segment16 => v_ud_asg.segment16
,p_segment17 => v_ud_asg.segment17
,p_segment18 => v_ud_asg.segment18
,p_segment19 => v_ud_asg.segment19
,p_segment20 => v_ud_asg.segment20
,p_segment21 => v_ud_asg.segment21
,p_segment22 => v_ud_asg.segment22
,p_segment23 => v_ud_asg.segment23
,p_segment24 => v_ud_asg.segment24
,p_segment25 => v_ud_asg.segment25
,p_segment26 => v_ud_asg.segment26
,p_segment27 => v_ud_asg.segment27
,p_segment28 => v_ud_asg.segment28
,p_segment29 => v_ud_asg.segment29
,p_segment30 => v_ud_asg.segment30
,p_concatenated_segments => l_concat_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_no_managers_warning => l_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
);
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
hr_assignment_api.update_emp_asg_criteria(
p_effective_date => v_ud_asg.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_object_version_number => l_ovn
,p_payroll_id => l_payroll_id
,p_location_id => l_location_id
,p_organization_id => l_organization_id
,p_pay_basis_id => l_pay_basis_id
,p_employment_category => v_ud_asg.employment_category
-- added for the enhancement
,p_segment1 => v_ud_asg.ppg_segment1
,p_segment2 => v_ud_asg.ppg_segment2
,p_segment3 => v_ud_asg.ppg_segment3
,p_segment4 => v_ud_asg.ppg_segment4
,p_segment5 => v_ud_asg.ppg_segment5
,p_segment6 => v_ud_asg.ppg_segment6
,p_segment7 => v_ud_asg.ppg_segment7
,p_segment8 => v_ud_asg.ppg_segment8
,p_segment9 => v_ud_asg.ppg_segment9
,p_segment10 => v_ud_asg.ppg_segment10
,p_segment11 => v_ud_asg.ppg_segment11
,p_segment12 => v_ud_asg.ppg_segment12
,p_segment13 => v_ud_asg.ppg_segment13
,p_segment14 => v_ud_asg.ppg_segment14
,p_segment15 => v_ud_asg.ppg_segment15
,p_segment16 => v_ud_asg.ppg_segment16
,p_segment17 => v_ud_asg.ppg_segment17
,p_segment18 => v_ud_asg.ppg_segment18
,p_segment19 => v_ud_asg.ppg_segment19
,p_segment20 => v_ud_asg.ppg_segment20
,p_segment21 => v_ud_asg.ppg_segment21
,p_segment22 => v_ud_asg.ppg_segment22
,p_segment23 => v_ud_asg.ppg_segment23
,p_segment24 => v_ud_asg.ppg_segment24
,p_segment25 => v_ud_asg.ppg_segment25
,p_segment26 => v_ud_asg.ppg_segment26
,p_segment27 => v_ud_asg.ppg_segment27
,p_segment28 => v_ud_asg.ppg_segment28
,p_segment29 => v_ud_asg.ppg_segment29
,p_segment30 => v_ud_asg.ppg_segment30
--
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_group_name => l_group_name
,p_people_group_id => l_people_group_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
);
SELECT emp_fed_tax_rule_id
INTO l_emp_fed_tax_rule_id
FROM hr_h2pi_federal_tax_rules_v
WHERE assignment_id = l_assignment_id
AND v_ud_asg.effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT emp_fed_tax_rule_id
INTO l_ud_emp_fed_tax_rule_id
FROM hr_h2pi_federal_tax_rules
WHERE assignment_id = v_ud_asg.assignment_id
AND client_id = p_from_client_id
AND v_ud_asg.effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT emp_state_tax_rule_id
INTO l_ud_emp_state_tax_rule_id
FROM hr_h2pi_state_tax_rules
WHERE assignment_id = v_ud_asg.assignment_id
AND client_id = p_from_client_id
AND jurisdiction_code = v_sta.jurisdiction_code
AND v_ud_asg.effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT emp_county_tax_rule_id
INTO l_ud_emp_county_tax_rule_id
FROM hr_h2pi_county_tax_rules
WHERE assignment_id = v_ud_asg.assignment_id
AND client_id = p_from_client_id
AND jurisdiction_code = v_cnt.jurisdiction_code
AND v_ud_asg.effective_start_date BETWEEN effective_start_date
AND effective_end_date;
SELECT emp_city_tax_rule_id
INTO l_ud_emp_city_tax_rule_id
FROM hr_h2pi_city_tax_rules
WHERE assignment_id = v_ud_asg.assignment_id
AND client_id = p_from_client_id
AND jurisdiction_code = v_cty.jurisdiction_code
AND v_ud_asg.effective_start_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE hr_h2pi_assignments asg
SET status = 'C'
WHERE asg.assignment_id = v_ud_asg.assignment_id
AND asg.client_id = p_from_client_id
AND asg.effective_start_date = v_ud_asg.effective_start_date
AND asg.effective_end_date = v_ud_asg.effective_end_date;
SELECT *
FROM hr_h2pi_periods_of_service pos
WHERE pos.period_of_service_id = p_pos_id
AND pos.client_id = p_from_client_id;
SELECT pos.object_version_number
FROM per_periods_of_service pos
WHERE pos.period_of_service_id = p_pos_id;
hr_ex_employee_api.update_term_details_emp(
p_effective_date => v_ud_pos.date_start
,p_period_of_service_id => l_period_of_service_id
,p_object_version_number => l_ovn
-- ,p_accepted_termination_date => v_ud_pos.accepted_termination_date
,p_leaving_reason => v_ud_pos.leaving_reason
-- ,p_notified_termination_date => v_ud_pos.notified_termination_date
-- ,p_projected_termination_date => v_ud_pos.projected_termination_date
);
UPDATE hr_h2pi_periods_of_service pos
SET status = 'C'
WHERE pos.period_of_service_id = v_ud_pos.period_of_service_id
AND pos.client_id = p_from_client_id;
SELECT *
FROM hr_h2pi_salaries ppp
WHERE ppp.pay_proposal_id = p_ppp_id
AND ppp.client_id = p_from_client_id;
SELECT object_version_number
FROM per_pay_proposals ppp
WHERE ppp.pay_proposal_id = p_ppp_id;
SELECT element_entry_id
FROM pay_element_entries_f
WHERE creator_type = 'SP'
AND p_date BETWEEN effective_start_date and effective_end_date
AND assignment_id = p_asg_id;
hr_maintain_proposal_api.insert_salary_proposal(
p_assignment_id => l_assignment_id
,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
,p_change_date => v_ud_ppp.change_date
,p_proposed_salary_n => v_ud_ppp.proposed_salary_n
,p_attribute_category => v_ud_ppp.attribute_category
,p_attribute1 => v_ud_ppp.attribute1
,p_attribute2 => v_ud_ppp.attribute2
,p_attribute3 => v_ud_ppp.attribute3
,p_attribute4 => v_ud_ppp.attribute4
,p_attribute5 => v_ud_ppp.attribute5
,p_attribute6 => v_ud_ppp.attribute6
,p_attribute7 => v_ud_ppp.attribute7
,p_attribute8 => v_ud_ppp.attribute8
,p_attribute9 => v_ud_ppp.attribute9
,p_attribute10 => v_ud_ppp.attribute10
,p_attribute11 => v_ud_ppp.attribute11
,p_attribute12 => v_ud_ppp.attribute12
,p_attribute13 => v_ud_ppp.attribute13
,p_attribute14 => v_ud_ppp.attribute14
,p_attribute15 => v_ud_ppp.attribute15
,p_attribute16 => v_ud_ppp.attribute16
,p_attribute17 => v_ud_ppp.attribute17
,p_attribute18 => v_ud_ppp.attribute18
,p_attribute19 => v_ud_ppp.attribute19
,p_attribute20 => v_ud_ppp.attribute20
,p_object_version_number => l_ovn
,p_multiple_components => 'N'
,p_approved => 'Y'
,p_element_entry_id => l_element_entry_id
,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
,p_proposed_salary_warning => l_proposed_salary_warning
,p_approved_warning => l_approved_warning
,p_payroll_warning => l_payroll_warning
,p_pay_proposal_id => l_pay_proposal_id
);
hr_maintain_proposal_api.update_salary_proposal(
p_pay_proposal_id => l_pay_proposal_id
,p_object_version_number => l_ovn
,p_change_date => v_ud_ppp.change_date
,p_proposed_salary_n => v_ud_ppp.proposed_salary_n
,p_attribute_category => v_ud_ppp.attribute_category
,p_attribute1 => v_ud_ppp.attribute1
,p_attribute2 => v_ud_ppp.attribute2
,p_attribute3 => v_ud_ppp.attribute3
,p_attribute4 => v_ud_ppp.attribute4
,p_attribute5 => v_ud_ppp.attribute5
,p_attribute6 => v_ud_ppp.attribute6
,p_attribute7 => v_ud_ppp.attribute7
,p_attribute8 => v_ud_ppp.attribute8
,p_attribute9 => v_ud_ppp.attribute9
,p_attribute10 => v_ud_ppp.attribute10
,p_attribute11 => v_ud_ppp.attribute11
,p_attribute12 => v_ud_ppp.attribute12
,p_attribute13 => v_ud_ppp.attribute13
,p_attribute14 => v_ud_ppp.attribute14
,p_attribute15 => v_ud_ppp.attribute15
,p_attribute16 => v_ud_ppp.attribute16
,p_attribute17 => v_ud_ppp.attribute17
,p_attribute18 => v_ud_ppp.attribute18
,p_attribute19 => v_ud_ppp.attribute19
,p_attribute20 => v_ud_ppp.attribute20
,p_approved => 'Y'
,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
,p_proposed_salary_warning => l_proposed_salary_warning
,p_approved_warning => l_approved_warning
,p_payroll_warning => l_payroll_warning
);
UPDATE hr_h2pi_salaries ppp
SET status = 'C'
WHERE ppp.pay_proposal_id = v_ud_ppp.pay_proposal_id
AND ppp.client_id = p_from_client_id;
SELECT *
FROM hr_h2pi_payment_methods ppm
WHERE ppm.personal_payment_method_id = p_ppm_id
AND ppm.client_id = p_from_client_id
AND ppm.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_payment_methods_v ppm
WHERE ppm.personal_payment_method_id = p_ppm_id
AND p_esd BETWEEN ppm.effective_start_date
AND ppm.effective_end_date;
SELECT ppm.object_version_number
FROM pay_personal_payment_methods_f ppm
WHERE ppm.personal_payment_method_id = p_ppm_id
AND p_esd BETWEEN ppm.effective_start_date
AND ppm.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_mode := 'DELETE';
hr_personal_pay_method_api.delete_personal_pay_method(
p_effective_date => v_ud_ppm.effective_start_date-1
,p_datetrack_delete_mode => l_delete_mode
,p_personal_payment_method_id => l_personal_pay_method_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(ppm.effective_end_date)
INTO l_max_eed
FROM pay_personal_payment_methods_f ppm
WHERE ppm.personal_payment_method_id = l_personal_pay_method_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 140);
hr_personal_pay_method_api.delete_personal_pay_method(
p_effective_date => v_ud_ppm.effective_start_date
,p_datetrack_delete_mode => l_delete_mode
,p_personal_payment_method_id => l_personal_pay_method_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
hr_personal_pay_method_api.update_personal_pay_method(
p_effective_date => v_ud_ppm.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_amount => v_ud_ppm.amount
,p_percentage => v_ud_ppm.percentage
,p_priority => v_ud_ppm.priority
,p_attribute_category => v_ud_ppm.attribute_category
,p_attribute1 => v_ud_ppm.attribute1
,p_attribute2 => v_ud_ppm.attribute2
,p_attribute3 => v_ud_ppm.attribute3
,p_attribute4 => v_ud_ppm.attribute4
,p_attribute5 => v_ud_ppm.attribute5
,p_attribute6 => v_ud_ppm.attribute6
,p_attribute7 => v_ud_ppm.attribute7
,p_attribute8 => v_ud_ppm.attribute8
,p_attribute9 => v_ud_ppm.attribute9
,p_attribute10 => v_ud_ppm.attribute10
,p_attribute11 => v_ud_ppm.attribute11
,p_attribute12 => v_ud_ppm.attribute12
,p_attribute13 => v_ud_ppm.attribute13
,p_attribute14 => v_ud_ppm.attribute14
,p_attribute15 => v_ud_ppm.attribute15
,p_attribute16 => v_ud_ppm.attribute16
,p_attribute17 => v_ud_ppm.attribute17
,p_attribute18 => v_ud_ppm.attribute18
,p_attribute19 => v_ud_ppm.attribute19
,p_attribute20 => v_ud_ppm.attribute20
,p_territory_code => v_ud_ppm.territory_code
,p_segment1 => v_ud_ppm.segment1
,p_segment2 => v_ud_ppm.segment2
,p_segment3 => v_ud_ppm.segment3
,p_segment4 => v_ud_ppm.segment4
,p_segment5 => v_ud_ppm.segment5
,p_segment6 => v_ud_ppm.segment6
,p_segment7 => v_ud_ppm.segment7
,p_segment8 => v_ud_ppm.segment8
,p_segment9 => v_ud_ppm.segment9
,p_segment10 => v_ud_ppm.segment10
,p_segment11 => v_ud_ppm.segment11
,p_segment12 => v_ud_ppm.segment12
,p_segment13 => v_ud_ppm.segment13
,p_segment14 => v_ud_ppm.segment14
,p_segment15 => v_ud_ppm.segment15
,p_segment16 => v_ud_ppm.segment16
,p_segment17 => v_ud_ppm.segment17
,p_segment18 => v_ud_ppm.segment18
,p_segment19 => v_ud_ppm.segment19
,p_segment20 => v_ud_ppm.segment20
,p_segment21 => v_ud_ppm.segment21
,p_segment22 => v_ud_ppm.segment22
,p_segment23 => v_ud_ppm.segment23
,p_segment24 => v_ud_ppm.segment24
,p_segment25 => v_ud_ppm.segment25
,p_segment26 => v_ud_ppm.segment26
,p_segment27 => v_ud_ppm.segment27
,p_segment28 => v_ud_ppm.segment28
,p_segment29 => v_ud_ppm.segment29
,p_segment30 => v_ud_ppm.segment30
,p_personal_payment_method_id => l_personal_pay_method_id
,p_object_version_number => l_ovn
,p_external_account_id => l_external_account_id
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_comment_id => l_comment_id
);
UPDATE hr_h2pi_payment_methods ppm
SET status = 'C'
WHERE ppm.personal_payment_method_id = v_ud_ppm.personal_payment_method_id
AND ppm.client_id = p_from_client_id
AND ppm.effective_start_date = v_ud_ppm.effective_start_date
AND ppm.effective_end_date = v_ud_ppm.effective_end_date;
SELECT *
FROM hr_h2pi_cost_allocations hca
WHERE hca.cost_allocation_id = p_cost_allocation_id
AND hca.client_id = p_from_client_id
AND hca.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_cost_allocations_v hca
WHERE hca.cost_allocation_id = p_cost_allocation_id
AND p_esd BETWEEN hca.effective_start_date
AND hca.effective_end_date;
SELECT pca.object_version_number
FROM pay_cost_allocations_f pca
WHERE pca.cost_allocation_id = p_cost_allocation_id
AND p_esd BETWEEN pca.effective_start_date
AND pca.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_mode := 'DELETE';
pay_cost_allocation_api.delete_cost_allocation(
p_validate => FALSE
,p_effective_date => v_ud_hca.effective_start_date - 1
,p_datetrack_delete_mode => l_delete_mode
,p_cost_allocation_id => l_cost_allocation_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed);
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(caf.effective_end_date)
INTO l_max_eed
FROM pay_cost_allocations_f caf
WHERE caf.cost_allocation_id = l_cost_allocation_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 140);
pay_cost_allocation_api.delete_cost_allocation(
p_effective_date => v_ud_hca.effective_start_date
,p_datetrack_delete_mode => l_delete_mode
,p_cost_allocation_id => l_cost_allocation_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed );
pay_cost_allocation_api.update_cost_allocation(
p_effective_date => v_ud_hca.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_cost_allocation_id => l_cost_allocation_id
,p_object_version_number => l_ovn
,p_proportion => v_ud_hca.proportion
,p_segment1 => v_ud_hca.segment1
,p_segment2 => v_ud_hca.segment2
,p_segment3 => v_ud_hca.segment3
,p_segment4 => v_ud_hca.segment4
,p_segment5 => v_ud_hca.segment5
,p_segment6 => v_ud_hca.segment6
,p_segment7 => v_ud_hca.segment7
,p_segment8 => v_ud_hca.segment8
,p_segment9 => v_ud_hca.segment9
,p_segment10 => v_ud_hca.segment10
,p_segment11 => v_ud_hca.segment11
,p_segment12 => v_ud_hca.segment12
,p_segment13 => v_ud_hca.segment13
,p_segment14 => v_ud_hca.segment14
,p_segment15 => v_ud_hca.segment15
,p_segment16 => v_ud_hca.segment16
,p_segment17 => v_ud_hca.segment17
,p_segment18 => v_ud_hca.segment18
,p_segment19 => v_ud_hca.segment19
,p_segment20 => v_ud_hca.segment20
,p_segment21 => v_ud_hca.segment21
,p_segment22 => v_ud_hca.segment22
,p_segment23 => v_ud_hca.segment23
,p_segment24 => v_ud_hca.segment24
,p_segment25 => v_ud_hca.segment25
,p_segment26 => v_ud_hca.segment26
,p_segment27 => v_ud_hca.segment27
,p_segment28 => v_ud_hca.segment28
,p_segment29 => v_ud_hca.segment29
,p_segment30 => v_ud_hca.segment30
,p_concat_segments => v_ud_hca.concatenated_segments
,p_combination_name => l_combination_name
,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed );
UPDATE hr_h2pi_cost_allocations hca
SET status = 'C'
WHERE hca.cost_allocation_id = v_ud_hca.cost_allocation_id
AND hca.client_id = p_from_client_id
AND hca.effective_start_date = v_ud_hca.effective_start_date
AND hca.effective_end_date = v_ud_hca.effective_end_date;
SELECT *
FROM hr_h2pi_element_entries ele
WHERE ele.element_entry_id = p_ele_id
AND ele.client_id = p_from_client_id
AND ele.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_element_entry_values eev
WHERE eev.element_entry_id = p_ele_id
AND eev.screen_entry_value IS NOT NULL
AND eev.client_id = p_from_client_id
AND p_esd BETWEEN eev.effective_start_date
AND eev.effective_end_date;
SELECT *
FROM hr_h2pi_element_entries_v ele
WHERE ele.element_entry_id = p_ele_id
AND p_esd BETWEEN ele.effective_start_date
AND ele.effective_end_date;
SELECT ele.object_version_number
FROM pay_element_entries_f ele
WHERE ele.element_entry_id = p_ele_id
AND p_esd BETWEEN ele.effective_start_date
AND ele.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_warning BOOLEAN;
l_update_warning BOOLEAN;
l_delete_mode := 'DELETE';
py_element_entry_api.delete_element_entry(
p_effective_date => v_ud_ele.effective_start_date-1
,p_datetrack_delete_mode => l_delete_mode
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_delete_warning => l_delete_warning
);
SELECT uom
INTO l_uom
FROM pay_input_values_f
WHERE input_value_id = a_input_value_id(l_index)
AND v_ud_ele.effective_start_date BETWEEN effective_start_date
AND effective_end_date;
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(ele.effective_end_date)
INTO l_max_eed
FROM pay_element_entries_f ele
WHERE ele.element_entry_id = l_element_entry_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 140);
py_element_entry_api.delete_element_entry(
p_effective_date => v_ud_ele.effective_start_date
,p_datetrack_delete_mode => l_delete_mode
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_delete_warning => l_delete_warning
);
py_element_entry_api.update_element_entry(
p_datetrack_update_mode => l_update_mode
,p_effective_date => v_ud_ele.effective_start_date
,p_business_group_id => hr_h2pi_upload.g_to_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_ovn
,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
-- ,p_subpriority => v_ud_ele.subpriority
,p_date_earned => v_ud_ele.date_earned
,p_personal_payment_method_id => l_ee_personal_pay_method_id
,p_attribute_category => v_ud_ele.attribute_category
,p_attribute1 => v_ud_ele.attribute1
,p_attribute2 => v_ud_ele.attribute2
,p_attribute3 => v_ud_ele.attribute3
,p_attribute4 => v_ud_ele.attribute4
,p_attribute5 => v_ud_ele.attribute5
,p_attribute6 => v_ud_ele.attribute6
,p_attribute7 => v_ud_ele.attribute7
,p_attribute8 => v_ud_ele.attribute8
,p_attribute9 => v_ud_ele.attribute9
,p_attribute10 => v_ud_ele.attribute10
,p_attribute11 => v_ud_ele.attribute11
,p_attribute12 => v_ud_ele.attribute12
,p_attribute13 => v_ud_ele.attribute13
,p_attribute14 => v_ud_ele.attribute14
,p_attribute15 => v_ud_ele.attribute15
,p_attribute16 => v_ud_ele.attribute16
,p_attribute17 => v_ud_ele.attribute17
,p_attribute18 => v_ud_ele.attribute18
,p_attribute19 => v_ud_ele.attribute19
,p_attribute20 => v_ud_ele.attribute20
,p_input_value_id1 => a_input_value_id(1)
,p_input_value_id2 => a_input_value_id(2)
,p_input_value_id3 => a_input_value_id(3)
,p_input_value_id4 => a_input_value_id(4)
,p_input_value_id5 => a_input_value_id(5)
,p_input_value_id6 => a_input_value_id(6)
,p_input_value_id7 => a_input_value_id(7)
,p_input_value_id8 => a_input_value_id(8)
,p_input_value_id9 => a_input_value_id(9)
,p_input_value_id10 => a_input_value_id(10)
,p_input_value_id11 => a_input_value_id(11)
,p_input_value_id12 => a_input_value_id(12)
,p_input_value_id13 => a_input_value_id(13)
,p_input_value_id14 => a_input_value_id(14)
,p_input_value_id15 => a_input_value_id(15)
,p_entry_value1 => a_ud_sev(1)
,p_entry_value2 => a_ud_sev(2)
,p_entry_value3 => a_ud_sev(3)
,p_entry_value4 => a_ud_sev(4)
,p_entry_value5 => a_ud_sev(5)
,p_entry_value6 => a_ud_sev(6)
,p_entry_value7 => a_ud_sev(7)
,p_entry_value8 => a_ud_sev(8)
,p_entry_value9 => a_ud_sev(9)
,p_entry_value10 => a_ud_sev(10)
,p_entry_value11 => a_ud_sev(11)
,p_entry_value12 => a_ud_sev(12)
,p_entry_value13 => a_ud_sev(13)
,p_entry_value14 => a_ud_sev(14)
,p_entry_value15 => a_ud_sev(15)
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_update_warning => l_update_warning
);
UPDATE hr_h2pi_element_entries ele
SET status = 'C'
WHERE ele.element_entry_id = v_ud_ele.element_entry_id
AND ele.client_id = p_from_client_id
AND ele.effective_start_date = v_ud_ele.effective_start_date
AND ele.effective_end_date = v_ud_ele.effective_end_date;
UPDATE hr_h2pi_element_entry_values eev
SET status = 'C'
WHERE eev.element_entry_id = v_ud_ele.element_entry_id
AND eev.client_id = p_from_client_id
AND eev.effective_start_date = v_ud_ele.effective_start_date
AND eev.effective_end_date = v_ud_ele.effective_end_date;
SELECT *
FROM hr_h2pi_federal_tax_rules fed
WHERE fed.emp_fed_tax_rule_id = p_fed_id
AND fed.client_id = p_from_client_id
AND fed.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_federal_tax_rules_v fed
WHERE fed.emp_fed_tax_rule_id = p_fed_id
AND p_esd BETWEEN fed.effective_start_date
AND fed.effective_end_date;
SELECT fed.object_version_number
FROM pay_us_emp_fed_tax_rules_f fed
WHERE fed.emp_fed_tax_rule_id = p_fed_id
AND p_esd BETWEEN fed.effective_start_date
AND fed.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(fed.effective_end_date)
INTO l_max_eed
FROM pay_us_emp_fed_tax_rules_f fed
WHERE fed.emp_fed_tax_rule_id = l_emp_fed_tax_rule_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 140);
pay_federal_tax_rule_api.update_fed_tax_rule(
p_effective_date => v_ud_fed.effective_start_date
,p_datetrack_update_mode => 'UPDATE_OVERRIDE'
,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
,p_object_version_number => l_ovn
,p_sui_state_code => v_ud_fed.sui_state_code
,p_additional_wa_amount => v_ud_fed.additional_wa_amount
,p_filing_status_code => v_ud_fed.filing_status_code
,p_fit_override_amount => v_ud_fed.fit_override_amount
,p_fit_override_rate => v_ud_fed.fit_override_rate
,p_withholding_allowances => v_ud_fed.withholding_allowances
,p_cumulative_taxation => v_ud_fed.cumulative_taxation
,p_eic_filing_status_code => v_ud_fed.eic_filing_status_code
,p_fit_additional_tax => v_ud_fed.fit_additional_tax
,p_fit_exempt => v_ud_fed.fit_exempt
,p_futa_tax_exempt => v_ud_fed.futa_tax_exempt
,p_medicare_tax_exempt => v_ud_fed.medicare_tax_exempt
,p_ss_tax_exempt => v_ud_fed.ss_tax_exempt
,p_statutory_employee => v_ud_fed.statutory_employee
,p_w2_filed_year => v_ud_fed.w2_filed_year
,p_supp_tax_override_rate => v_ud_fed.supp_tax_override_rate
,p_excessive_wa_reject_date => v_ud_fed.excessive_wa_reject_date
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
,p_datetrack_mode => l_delete_mode
,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_delete_routine => NULL
);
pay_federal_tax_rule_api.update_fed_tax_rule(
p_effective_date => v_ud_fed.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_emp_fed_tax_rule_id => l_emp_fed_tax_rule_id
,p_object_version_number => l_ovn
,p_sui_state_code => v_ud_fed.sui_state_code
,p_additional_wa_amount => v_ud_fed.additional_wa_amount
,p_filing_status_code => v_ud_fed.filing_status_code
,p_fit_override_amount => v_ud_fed.fit_override_amount
,p_fit_override_rate => v_ud_fed.fit_override_rate
,p_withholding_allowances => v_ud_fed.withholding_allowances
,p_cumulative_taxation => v_ud_fed.cumulative_taxation
,p_eic_filing_status_code => v_ud_fed.eic_filing_status_code
,p_fit_additional_tax => v_ud_fed.fit_additional_tax
,p_fit_exempt => v_ud_fed.fit_exempt
,p_futa_tax_exempt => v_ud_fed.futa_tax_exempt
,p_medicare_tax_exempt => v_ud_fed.medicare_tax_exempt
,p_ss_tax_exempt => v_ud_fed.ss_tax_exempt
,p_statutory_employee => v_ud_fed.statutory_employee
,p_w2_filed_year => v_ud_fed.w2_filed_year
,p_supp_tax_override_rate => v_ud_fed.supp_tax_override_rate
,p_excessive_wa_reject_date => v_ud_fed.excessive_wa_reject_date
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
UPDATE hr_h2pi_federal_tax_rules fed
SET status = 'C'
WHERE fed.emp_fed_tax_rule_id = v_ud_fed.emp_fed_tax_rule_id
AND fed.client_id = p_from_client_id
AND fed.effective_start_date = v_ud_fed.effective_start_date
AND fed.effective_end_date = v_ud_fed.effective_end_date;
SELECT *
FROM hr_h2pi_state_tax_rules sta
WHERE sta.emp_state_tax_rule_id = p_sta_id
AND sta.client_id = p_from_client_id
AND sta.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_state_tax_rules_v sta
WHERE sta.emp_state_tax_rule_id = p_sta_id
AND p_esd BETWEEN sta.effective_start_date
AND sta.effective_end_date;
SELECT sta.object_version_number
FROM pay_us_emp_state_tax_rules_f sta
WHERE sta.emp_state_tax_rule_id = p_sta_id
AND p_esd BETWEEN sta.effective_start_date
AND sta.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(sta.effective_end_date)
INTO l_max_eed
FROM pay_us_emp_state_tax_rules_f sta
WHERE sta.emp_state_tax_rule_id = l_emp_state_tax_rule_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 120);
pay_state_tax_rule_api.update_state_tax_rule(
p_effective_date => v_ud_sta.effective_start_date
,p_datetrack_update_mode => 'UPDATE_OVERRIDE'
,p_object_version_number => l_ovn
,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
,p_additional_wa_amount => v_ud_sta.additional_wa_amount
,p_filing_status_code => v_ud_sta.filing_status_code
,p_remainder_percent => v_ud_sta.remainder_percent
,p_secondary_wa => v_ud_sta.secondary_wa
,p_sit_additional_tax => v_ud_sta.sit_additional_tax
,p_sit_override_amount => v_ud_sta.sit_override_amount
,p_sit_override_rate => v_ud_sta.sit_override_rate
,p_withholding_allowances => v_ud_sta.withholding_allowances
,p_excessive_wa_reject_date => v_ud_sta.excessive_wa_reject_date
,p_sdi_exempt => v_ud_sta.sdi_exempt
,p_sit_exempt => v_ud_sta.sit_exempt
,p_sit_optional_calc_ind => v_ud_sta.sit_optional_calc_ind
,p_state_non_resident_cert => v_ud_sta.state_non_resident_cert
,p_sui_exempt => v_ud_sta.sui_exempt
,p_wc_exempt => v_ud_sta.wc_exempt
,p_sui_wage_base_override_amoun
=> v_ud_sta.sui_wage_base_override_amount
,p_supp_tax_override_rate => v_ud_sta.supp_tax_override_rate
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
,p_datetrack_mode => l_delete_mode
,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_delete_routine => NULL
);
pay_state_tax_rule_api.update_state_tax_rule(
p_effective_date => v_ud_sta.effective_start_date
,p_datetrack_update_mode => l_update_mode
,p_object_version_number => l_ovn
,p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
,p_additional_wa_amount => v_ud_sta.additional_wa_amount
,p_filing_status_code => v_ud_sta.filing_status_code
,p_remainder_percent => v_ud_sta.remainder_percent
,p_secondary_wa => v_ud_sta.secondary_wa
,p_sit_additional_tax => v_ud_sta.sit_additional_tax
,p_sit_override_amount => v_ud_sta.sit_override_amount
,p_sit_override_rate => v_ud_sta.sit_override_rate
,p_withholding_allowances => v_ud_sta.withholding_allowances
,p_excessive_wa_reject_date => v_ud_sta.excessive_wa_reject_date
,p_sdi_exempt => v_ud_sta.sdi_exempt
,p_sit_exempt => v_ud_sta.sit_exempt
,p_sit_optional_calc_ind => v_ud_sta.sit_optional_calc_ind
,p_state_non_resident_cert => v_ud_sta.state_non_resident_cert
,p_sui_exempt => v_ud_sta.sui_exempt
,p_wc_exempt => v_ud_sta.wc_exempt
,p_sui_wage_base_override_amoun
=> v_ud_sta.sui_wage_base_override_amount
,p_supp_tax_override_rate => v_ud_sta.supp_tax_override_rate
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
UPDATE hr_h2pi_state_tax_rules sta
SET status = 'C'
WHERE sta.emp_state_tax_rule_id = v_ud_sta.emp_state_tax_rule_id
AND sta.client_id = p_from_client_id
AND sta.effective_start_date = v_ud_sta.effective_start_date
AND sta.effective_end_date = v_ud_sta.effective_end_date;
SELECT *
FROM hr_h2pi_county_tax_rules cnt
WHERE cnt.emp_county_tax_rule_id = p_cnt_id
AND cnt.client_id = p_from_client_id
AND cnt.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_county_tax_rules_v cnt
WHERE cnt.emp_county_tax_rule_id = p_cnt_id
AND p_esd BETWEEN cnt.effective_start_date
AND cnt.effective_end_date;
SELECT cnt.object_version_number
FROM pay_us_emp_county_tax_rules_f cnt
WHERE cnt.emp_county_tax_rule_id = p_cnt_id
AND p_esd BETWEEN cnt.effective_start_date
AND cnt.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(cnt.effective_end_date)
INTO l_max_eed
FROM pay_us_emp_county_tax_rules_f cnt
WHERE cnt.emp_county_tax_rule_id = l_emp_county_tax_rule_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 120);
pay_county_tax_rule_api.update_county_tax_rule(
p_effective_date => v_ud_cnt.effective_start_date
,p_datetrack_mode => 'UPDATE_OVERRIDE'
,p_object_version_number => l_ovn
,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
,p_additional_wa_rate => v_ud_cnt.additional_wa_rate
,p_filing_status_code => v_ud_cnt.filing_status_code
,p_lit_additional_tax => v_ud_cnt.lit_additional_tax
,p_lit_override_amount => v_ud_cnt.lit_override_amount
,p_lit_override_rate => v_ud_cnt.lit_override_rate
,p_withholding_allowances => v_ud_cnt.withholding_allowances
,p_lit_exempt => v_ud_cnt.lit_exempt
,p_sd_exempt => v_ud_cnt.sd_exempt
,p_ht_exempt => v_ud_cnt.ht_exempt
,p_school_district_code => v_ud_cnt.school_district_code
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
,p_datetrack_mode => l_delete_mode
,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_delete_routine => NULL
);
pay_county_tax_rule_api.update_county_tax_rule(
p_effective_date => v_ud_cnt.effective_start_date
,p_datetrack_mode => l_update_mode
,p_object_version_number => l_ovn
,p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
,p_additional_wa_rate => v_ud_cnt.additional_wa_rate
,p_filing_status_code => v_ud_cnt.filing_status_code
,p_lit_additional_tax => v_ud_cnt.lit_additional_tax
,p_lit_override_amount => v_ud_cnt.lit_override_amount
,p_lit_override_rate => v_ud_cnt.lit_override_rate
,p_withholding_allowances => v_ud_cnt.withholding_allowances
,p_lit_exempt => v_ud_cnt.lit_exempt
,p_sd_exempt => v_ud_cnt.sd_exempt
,p_ht_exempt => v_ud_cnt.ht_exempt
,p_school_district_code => v_ud_cnt.school_district_code
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
UPDATE hr_h2pi_county_tax_rules cnt
SET status = 'C'
WHERE cnt.emp_county_tax_rule_id = v_ud_cnt.emp_county_tax_rule_id
AND cnt.client_id = p_from_client_id
AND cnt.effective_start_date = v_ud_cnt.effective_start_date
AND cnt.effective_end_date = v_ud_cnt.effective_end_date;
SELECT *
FROM hr_h2pi_city_tax_rules cty
WHERE cty.emp_city_tax_rule_id = p_cty_id
AND cty.client_Id = p_from_client_id
AND cty.effective_start_date = p_esd;
SELECT *
FROM hr_h2pi_city_tax_rules_v cty
WHERE cty.emp_city_tax_rule_id = p_cty_id
AND p_esd BETWEEN cty.effective_start_date
AND cty.effective_end_date;
SELECT cty.object_version_number
FROM pay_us_emp_city_tax_rules_f cty
WHERE cty.emp_city_tax_rule_id = p_cty_id
AND p_esd BETWEEN cty.effective_start_date
AND cty.effective_end_date;
l_update_mode VARCHAR2(30);
l_delete_mode VARCHAR2(30);
l_delete_mode := 'DELETE_NEXT_CHANGE';
SELECT MAX(sta.effective_end_date)
INTO l_max_eed
FROM pay_us_emp_city_tax_rules_f sta
WHERE sta.emp_city_tax_rule_id = l_emp_city_tax_rule_id;
,p_update_mode => l_update_mode
,p_delete_mode => l_delete_mode);
EXIT WHEN l_delete_mode = 'X';
IF l_delete_mode = 'DELETE_NEXT_CHANGE' THEN
hr_utility.set_location(l_proc, 120);
pay_city_tax_rule_api.update_city_tax_rule(
p_effective_date => v_ud_cty.effective_start_date
,p_datetrack_mode => 'UPDATE_OVERRIDE'
,p_object_version_number => l_ovn
,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
,p_additional_wa_rate => v_ud_cty.additional_wa_rate
,p_filing_status_code => v_ud_cty.filing_status_code
,p_lit_additional_tax => v_ud_cty.lit_additional_tax
,p_lit_override_amount => v_ud_cty.lit_override_amount
,p_lit_override_rate => v_ud_cty.lit_override_rate
,p_withholding_allowances => v_ud_cty.withholding_allowances
,p_lit_exempt => v_ud_cty.lit_exempt
,p_sd_exempt => v_ud_cty.sd_exempt
,p_ht_exempt => v_ud_cty.ht_exempt
,p_school_district_code => v_ud_cty.school_district_code
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
,p_datetrack_mode => l_delete_mode
,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_delete_routine => NULL
);
pay_city_tax_rule_api.update_city_tax_rule(
p_effective_date => v_ud_cty.effective_start_date
,p_datetrack_mode => l_update_mode
,p_object_version_number => l_ovn
,p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
,p_additional_wa_rate => v_ud_cty.additional_wa_rate
,p_filing_status_code => v_ud_cty.filing_status_code
,p_lit_additional_tax => v_ud_cty.lit_additional_tax
,p_lit_override_amount => v_ud_cty.lit_override_amount
,p_lit_override_rate => v_ud_cty.lit_override_rate
,p_withholding_allowances => v_ud_cty.withholding_allowances
,p_lit_exempt => v_ud_cty.lit_exempt
,p_sd_exempt => v_ud_cty.sd_exempt
,p_ht_exempt => v_ud_cty.ht_exempt
,p_school_district_code => v_ud_cty.school_district_code
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
);
UPDATE hr_h2pi_city_tax_rules sta
SET status = 'C'
WHERE sta.emp_city_tax_rule_id = v_ud_cty.emp_city_tax_rule_id
AND sta.client_id = p_from_client_id
AND sta.effective_start_date = v_ud_cty.effective_start_date
AND sta.effective_end_date = v_ud_cty.effective_end_date;
SELECT DISTINCT asg.assignment_id
FROM hr_h2pi_assignments_v asg
WHERE asg.person_id = p_per_id;
SELECT SUBSTR(fr_eev.screen_entry_value, 8, 4) city_code,
SUM (fr_eev2.screen_entry_value) percentage,
SUM (fr_eev2.screen_entry_value) - SUM (to_eev2.screen_entry_value) pct_diff
FROM hr_h2pi_element_entry_values_v to_eev,
hr_h2pi_element_entry_values_v to_eev2,
hr_h2pi_element_entries_v to_ele,
hr_h2pi_element_entry_values fr_eev,
hr_h2pi_element_entry_values fr_eev2,
hr_h2pi_element_entries fr_ele
WHERE to_ele.element_entry_id = to_eev.element_entry_id
AND to_eev.input_value_id = p_ed_iv_id1
AND to_eev2.input_value_id = p_ed_iv_id2
AND fr_eev.input_value_id = p_ud_iv_id1
AND fr_eev.client_id = p_from_client_id --
AND fr_eev2.input_value_id = p_ud_iv_id2
AND fr_eev2.client_id = p_from_client_id --
AND to_ele.effective_start_date = (SELECT MAX(to_ele_t.effective_start_date)
from hr_h2pi_element_entries_v to_ele_t
WHERE to_ele_t.element_entry_id = to_ele.element_entry_id)
AND to_eev.effective_start_date BETWEEN to_ele.effective_start_date
AND to_ele.effective_end_date
AND to_ele.element_entry_id = to_eev2.element_entry_id
AND to_eev2.effective_start_date BETWEEN to_ele.effective_start_date
AND to_ele.effective_end_date
AND to_ele.creator_type = 'UT'
AND fr_ele.element_entry_id = fr_eev.element_entry_id
AND fr_ele.client_id = p_from_client_id --
AND fr_ele.effective_start_date = (SELECT MAX(fr_ele_t.effective_start_date)
from hr_h2pi_element_entries fr_ele_t
WHERE fr_ele_t.element_entry_id = fr_ele.element_entry_id
AND fr_ele_t.client_id = p_from_client_id)
AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
AND fr_ele.effective_end_date
AND fr_ele.element_entry_id = fr_eev2.element_entry_id
--AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
-- AND fr_ele.effective_end_date
AND fr_eev2.effective_start_date BETWEEN fr_ele.effective_start_date
AND fr_ele.effective_end_date
AND fr_ele.creator_type = 'UT'
and fr_ele.assignment_id = p_ud_asg_id
and to_ele.assignment_id = p_ed_asg_id
and to_eev.screen_entry_value = fr_eev.screen_entry_value
and SUBSTR(to_eev.screen_entry_value,8,4) <> '0000'
and SUBSTR(fr_eev.screen_entry_value,1,6) = p_county
group by SUBSTR(fr_eev.screen_entry_value, 8, 4)
order by 3;
SELECT SUBSTR(fr_eev.screen_entry_value,4,3) county_code,
SUM (fr_eev2.screen_entry_value) percentage,
SUM (fr_eev2.screen_entry_value) - SUM (to_eev2.screen_entry_value) pct_diff
FROM hr_h2pi_element_entry_values_v to_eev,
hr_h2pi_element_entry_values_v to_eev2,
hr_h2pi_element_entries_v to_ele,
hr_h2pi_element_entry_values fr_eev,
hr_h2pi_element_entry_values fr_eev2,
hr_h2pi_element_entries fr_ele
WHERE to_ele.element_entry_id = to_eev.element_entry_id
AND to_eev.input_value_id = p_ed_iv_id1
AND to_eev2.input_value_id = p_ed_iv_id2
AND fr_eev.input_value_id = p_ud_iv_id1
AND fr_eev.client_id = p_from_client_id --
AND fr_eev2.input_value_id = p_ud_iv_id2
AND fr_eev2.client_id = p_from_client_id --
AND to_ele.effective_start_date = (SELECT MAX(to_ele_t.effective_start_date)
from hr_h2pi_element_entries_v to_ele_t
WHERE to_ele_t.element_entry_id = to_ele.element_entry_id)
AND to_eev.effective_start_date BETWEEN to_ele.effective_start_date
AND to_ele.effective_end_date
AND to_ele.element_entry_id = to_eev2.element_entry_id
AND to_eev2.effective_start_date BETWEEN to_ele.effective_start_date
AND to_ele.effective_end_date
AND to_ele.creator_type = 'UT'
AND fr_ele.element_entry_id = fr_eev.element_entry_id
AND fr_ele.client_id = p_from_client_id --
AND fr_ele.effective_start_date = (SELECT MAX(fr_ele_t.effective_start_date)
from hr_h2pi_element_entries fr_ele_t
WHERE fr_ele_t.element_entry_id = fr_ele.element_entry_id
AND fr_ele_t.client_id = p_from_client_id)
AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
AND fr_ele.effective_end_date
AND fr_ele.element_entry_id = fr_eev2.element_entry_id
--AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
-- AND fr_ele.effective_end_date
AND fr_eev2.effective_start_date BETWEEN fr_ele.effective_start_date
AND fr_ele.effective_end_date
AND fr_ele.creator_type = 'UT'
and fr_ele.assignment_id = p_ud_asg_id
and to_ele.assignment_id = p_ed_asg_id
and to_eev.screen_entry_value = fr_eev.screen_entry_value
and SUBSTR(to_eev.screen_entry_value,4,8) <> '000-0000'
and SUBSTR(fr_eev.screen_entry_value,1,2) = p_state
group by SUBSTR(fr_eev.screen_entry_value,4,3)
order by 3;
SELECT SUBSTR(fr_eev.screen_entry_value,1,2) state_code,
SUM (fr_eev2.screen_entry_value) percentage,
SUM (fr_eev2.screen_entry_value) - SUM (to_eev2.screen_entry_value) pct_diff
FROM hr_h2pi_element_entry_values_v to_eev,
hr_h2pi_element_entry_values_v to_eev2,
hr_h2pi_element_entries_v to_ele,
hr_h2pi_element_entry_values fr_eev,
hr_h2pi_element_entry_values fr_eev2,
hr_h2pi_element_entries fr_ele
WHERE to_ele.element_entry_id = to_eev.element_entry_id
AND to_eev.input_value_id = p_ed_iv_id1
AND to_eev2.input_value_id = p_ed_iv_id2
AND fr_eev.input_value_id = p_ud_iv_id1
AND fr_eev.client_id = p_from_client_id --
AND fr_eev2.input_value_id = p_ud_iv_id2
AND fr_eev2.client_id = p_from_client_id --
AND to_ele.effective_start_date = (SELECT MAX(to_ele_t.effective_start_date)
from hr_h2pi_element_entries_v to_ele_t
WHERE to_ele_t.element_entry_id = to_ele.element_entry_id)
AND to_eev.effective_start_date BETWEEN to_ele.effective_start_date
AND to_ele.effective_end_date
AND to_ele.element_entry_id = to_eev2.element_entry_id
AND to_eev2.effective_start_date BETWEEN to_ele.effective_start_date
AND to_ele.effective_end_date
AND to_ele.creator_type = 'UT'
AND fr_ele.element_entry_id = fr_eev.element_entry_id
AND fr_ele.client_id = p_from_client_id --
AND fr_ele.effective_start_date = (SELECT MAX(fr_ele_t.effective_start_date)
from hr_h2pi_element_entries fr_ele_t
WHERE fr_ele_t.element_entry_id = fr_ele.element_entry_id
AND fr_ele_t.client_id = p_from_client_id)
AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
AND fr_ele.effective_end_date
AND fr_ele.element_entry_id = fr_eev2.element_entry_id
--AND fr_eev.effective_start_date BETWEEN fr_ele.effective_start_date
-- AND fr_ele.effective_end_date
AND fr_eev2.effective_start_date BETWEEN fr_ele.effective_start_date
AND fr_ele.effective_end_date
AND fr_ele.creator_type = 'UT'
and fr_ele.assignment_id = p_ud_asg_id
and to_ele.assignment_id = p_ed_asg_id
and to_eev.screen_entry_value = fr_eev.screen_entry_value
group by SUBSTR(fr_eev.screen_entry_value,1,2)
order by 3;
SELECT ipv1.input_value_id,
ipv2.input_value_id
FROM pay_input_values_f ipv1,
pay_input_values_f ipv2,
pay_element_types_f elt
WHERE element_name = 'VERTEX'
AND elt.element_type_id = ipv1.element_type_id
AND elt.element_type_id = ipv2.element_type_id
AND ipv1.name = 'Jurisdiction'
AND ipv2.name = 'Percentage';
SELECT emp_state_tax_rule_id,
jurisdiction_code
FROM hr_h2pi_state_tax_rules_v
WHERE assignment_id = p_asg_id;
SELECT MAX(effective_end_date)
INTO l_effective_date
FROM hr_h2pi_element_entries
WHERE assignment_id = l_ud_assignment_id;
UPDATE hr_h2pi_element_entry_values eev
SET eev.status = 'C'
WHERE eev.element_entry_id IN (SELECT ee.element_entry_id
FROM hr_h2pi_element_entries ee
WHERE ee.person_id = p_person_id
AND ee.creator_type = 'UT'
AND ee.client_id = p_from_client_id);
UPDATE hr_h2pi_element_entries ee
SET ee.status = 'C'
WHERE ee.person_id = p_person_id
AND ee.creator_type = 'UT'
AND ee.client_id = p_from_client_id;