[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct a.person_id person_id
FROM per_all_people_f a,hr_organization_information hoi
WHERE a.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'GHR_US_ORG_INFORMATION';
SELECT COUNT(distinct a.person_id) person_count
FROM per_all_people_f a,hr_organization_information hoi
WHERE a.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'GHR_US_ORG_INFORMATION';
SELECT max(completion_status) max_status
FROM GHR_MTS_TEMP
WHERE session_id = c_session_id;
l_update_name pay_upgrade_definitions.short_name%type;
g_person_ids.DELETE;
l_update_name := 'GHR_ELT_BEN_CONV';
DELETE FROM pay_upgrade_status
WHERE upgrade_definition_id = (SELECT upgrade_definition_id
FROM pay_upgrade_definitions
WHERE short_name = l_update_name);
-- Loop through the person records and insert them into the appropriate batch.
-- If the batch size exceeds the limit, then insert the following records into the next batch.
FOR l_c_per_records IN c_per_records
LOOP
l_result := NULL;
INSERT INTO GHR_MTS_TEMP(session_id, batch_no, pa_request_id, action_type)
VALUES(l_session_id,l_batch_no,l_c_per_records.person_id, NULL);
hr_update_utility.setUpdateProcessing(p_update_name => l_update_name);
hr_update_utility.setUpdateComplete(p_update_name => l_update_name);
-- Delete the temporary table data.
DELETE FROM GHR_MTS_TEMP
WHERE session_id = l_session_id;
DELETE FROM GHR_MTS_TEMP
WHERE session_id = l_session_id;
SELECT 'x'
FROM per_people_info_types
WHERE information_type = 'GHR_US_PER_BENEFIT_INFO';
SELECT pa_request_id person_id, batch_no
FROM GHR_MTS_TEMP
WHERE session_id = c_session_id
AND batch_no = c_batch_no;
SELECT userenv('sessionid') sesid
FROM dual;
SELECT *
FROM ghr_pa_history
WHERE person_id = p_person_id
AND (
(table_name = 'PER_PEOPLE_EXTRA_INFO' and information5 = 'GHR_US_PER_GROUP1') OR
(table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
AND information4 IN (SELECT b.input_value_id
FROM pay_element_types_f a,pay_input_values_f b
WHERE a.element_type_id = b.element_type_id
AND (
(a.element_name = 'FEGLI' AND b.NAME = 'Eligibility Expiration') OR
(a.element_name = 'Retirement Plan' AND b.NAME = 'FERS Eligibility Expires') OR
(a.element_name = 'Health Benefits' and b.NAME in('LWOP Contingncy Strt Date','LWOP Contingncy End Date',
'Child Eq Court Ord Date')) OR
(a.element_name = 'Health Benefits Pre tax' and b.NAME in('LWOP Contingncy Strt Date','LWOP Contingncy End Date',
'Child Eq Court Ord Date')) OR
(a.element_name = 'TSP' and b.NAME in('Agncy Contrib Elig Date','Emp Contrib Elig Date'))
)
)
)
)
ORDER BY effective_date,table_name,process_date,information1,pa_history_id;
SELECT per.full_name
,per.national_identifier
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date
and per.effective_end_date;
SELECT a.element_name element, b.name input_value
FROM pay_element_types_f a,pay_input_values_f b
WHERE a.element_type_id = b.element_type_id
AND b.input_value_id = p_input_value_id;
PROCEDURE insert_benefits_eit_rec(p_person_id IN NUMBER,
p_benefits_eit_rec IN ghr_api.per_benefit_info_type,
p_effective_date IN DATE) IS
l_information_type per_people_extra_info.information_type%type;
SELECT person_extra_info_id,
object_version_number
FROM per_people_extra_info
WHERE person_id = p_person_id
AND information_type = 'GHR_US_PER_BENEFIT_INFO';
hr_utility.set_location('Entering Insert benefits EIT REC',0);
ghr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id => l_person_extra_info_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_pei_information3 => p_benefits_eit_rec.FEGLI_Date_Eligibility_Expires
,p_pei_information4 => p_benefits_eit_rec.FEHB_Date_Eligibility_expires
,p_pei_information5 => p_benefits_eit_rec.FEHB_Date_temp_eligibility
,p_pei_information6 => p_benefits_eit_rec.FEHB_Date_dependent_cert_expir
,p_pei_information7 => p_benefits_eit_rec.FEHB_LWOP_contingency_st_date
,p_pei_information8 => p_benefits_eit_rec.FEHB_LWOP_contingency_end_date
,p_pei_information10 => p_benefits_eit_rec.FEHB_Child_equiry_court_date
,p_pei_information11 => p_benefits_eit_rec.FERS_Date_eligibility_expires
,p_pei_information12 => p_benefits_eit_rec.FERS_Election_Date
,p_pei_information13 => p_benefits_eit_rec.FERS_Election_Indicator
,p_pei_information14 => p_benefits_eit_rec.TSP_Agncy_Contrib_Elig_date
,p_pei_information15 => p_benefits_eit_rec.TSP_Emp_Contrib_Elig_date
);
hr_utility.set_location('Leaving Insert Benefits EIT REC',40);
END insert_benefits_eit_rec;
UPDATE fnd_sessions SET SESSION_ID = l_sid
WHERE SESSION_ID = l_sid;
INSERT INTO fnd_sessions(SESSION_ID,EFFECTIVE_DATE)
VALUES (l_sid,sysdate);
insert_benefits_eit_rec(l_current_person_id,l_benefits_eit_rec,l_old_effective_date);
insert_benefits_eit_rec(l_current_person_id,l_benefits_eit_rec,l_old_effective_date);
UPDATE GHR_MTS_TEMP
SET completion_status = p_retcode
WHERE session_id = p_session_id
AND batch_no = p_batch_no;
select status
from fnd_product_installations
where application_id = GHR_APPLICATION_ID;
SELECT ppf.last_name,ppf.first_name, ppf.middle_names, ppf.employee_number --bug# 10419074
FROM per_assignments_f paf, per_people_f ppf
WHERE ppf.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND to_date('2013/01/13','YYYY/MM/DD') BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND to_date('2013/01/13','YYYY/MM/DD') BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.assignment_id =l_assignment_id;
l_update_flag number := 0;
l_datetrack_update_mode varchar2(25);
l_out_update_warning boolean;
select dut.duty_station_code
from hr_location_extra_info lei,
per_all_assignments_f asg,
ghr_duty_stations_v dut
where asg.assignment_id = l_assignment_id
and l_effective_date between
asg.effective_Start_date and asg.effective_end_date
and asg.location_id = lei.location_id
and lei.information_type = 'GHR_US_LOC_INFORMATION'
and lei.lei_information3 = dut.duty_station_id
and dut.duty_station_code like '06%107'
and l_effective_date between
dut.effective_start_date and dut.effective_end_date;
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
c.business_group_id business_group_id,--Bug# 6735031
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits'
and b.name = 'Health Plan'
and f.screen_entry_value in
('L4','IK','SW','YW');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
c.business_group_id business_group_id, --Bug# 6735031
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id) --Bug#6735031
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Health Plan'
and f.screen_entry_value in
('L4','IK','SW','YW');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'Health Benefits'
and b.name = 'Health Plan'
and f.screen_entry_value in
('L4','IK','SW','YW');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Health Plan'
and f.screen_entry_value in
('L4','IK','SW','YW');
select f.input_value_id input_value_id,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits'
and b.name = 'Enrollment'
and e.element_entry_id = l_element_entry_id;
select f.input_value_id input_value_id,
f.screen_entry_value screen_entry_value,
f.effective_start_date effective_start_date,
f.effective_end_date effective_end_date
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and f.effective_start_date = l_effective_start_date
and f.effective_end_date = l_effective_end_date
and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Enrollment'
and e.element_entry_id = l_element_entry_id;
l_update_flag := 0;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
for update_mode_a in cur_hb_fr loop
l_exists := true;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id_enrol
,p_entry_value1 => l_screen_entry_value_enrol
,p_input_value_id2 => l_input_value_id
,p_entry_value2 => l_screen_entry_value
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
l_update_flag := 0;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
for update_mode in cur_hb_pt_fr loop
l_exists := true;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
BEGIN --B2
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id_enrol
,p_entry_value1 => l_screen_entry_value_enrol
,p_input_value_id2 => l_input_value_id
,p_entry_value2 => l_screen_entry_value
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
c.business_group_id business_group_id,--Bug# 6735031
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits'
and b.name = 'Health Plan'
and f.screen_entry_value in ('52');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
c.business_group_id business_group_id,--Bug# 6735031
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Health Plan'
and f.screen_entry_value in ('52');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'Health Benefits'
and b.name = 'Health Plan'
and f.screen_entry_value in ('52');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Health Plan'
and f.screen_entry_value in ('52');
select f.input_value_id input_value_id,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits'
and b.name = 'Enrollment'
and e.element_entry_id = l_element_entry_id;
select f.input_value_id input_value_id,
f.screen_entry_value screen_entry_value,
f.effective_start_date effective_start_date,
f.effective_end_date effective_end_date
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and f.effective_start_date = l_effective_start_date
and f.effective_end_date = l_effective_end_date
and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Enrollment'
and e.element_entry_id = l_element_entry_id;
l_update_flag := 0;
l_update_flag := 1;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
for update_mode_a in cur_hb_fr loop
l_exists := true;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
BEGIN --C2
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id_enrol
,p_entry_value1 => l_screen_entry_value_enrol
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
l_update_flag := 0;
l_update_flag := 1;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
for update_mode in cur_hb_pt_fr loop
l_exists := true;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
BEGIN --C5
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id_enrol
,p_entry_value1 => l_screen_entry_value_enrol
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
c.business_group_id business_group_id,--Bug# 6735031
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits'
and b.name = 'Health Plan'
and f.screen_entry_value in ('KQ');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
c.business_group_id business_group_id,--Bug# 6735031
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)--Bug# 6735031
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Health Plan'
and f.screen_entry_value in ('KQ');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'Health Benefits'
and b.name = 'Health Plan'
and f.screen_entry_value in ('KQ');
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Health Plan'
and f.screen_entry_value in ('KQ');
select f.input_value_id input_value_id,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits'
and b.name = 'Enrollment'
and e.element_entry_id = l_element_entry_id;
select f.input_value_id input_value_id,
f.screen_entry_value screen_entry_value,
f.effective_start_date effective_start_date,
f.effective_end_date effective_end_date
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(l_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and f.effective_start_date = l_effective_start_date
and f.effective_end_date = l_effective_end_date
and f.effective_end_date > to_date('2013/01/12','YYYY/MM/DD')
and a.element_name = 'Health Benefits Pre tax'
and b.name = 'Enrollment'
and e.element_entry_id = l_element_entry_id;
l_update_flag := 0;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
for update_mode_a in cur_hb_fr loop
l_exists := true;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
BEGIN --D2
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_screen_entry_value
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
l_update_flag := 0;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
for update_mode in cur_hb_pt_fr loop
l_exists := true;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
BEGIN --D5
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_input_value_id
,p_entry_value1 => l_screen_entry_value
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
l_update_flag number := 0;
l_cotrib_update_flag number := 0;
l_datetrack_update_mode varchar2(25);
l_out_update_warning boolean;
select paf.position_id,ppf.person_id,paf.assignment_id,
ppei.pei_information14 ,ppf.effective_start_date, ppf.effective_end_date,ppf.national_identifier,
ppf.full_name,ppf.employee_number
from per_all_people_f ppf, per_all_assignments_f paf, per_people_extra_info ppei
where ppf.person_id=paf.person_id
and ppf.person_id=ppei.person_id
and ppei.information_type='GHR_US_PER_BENEFIT_INFO'
and paf.primary_flag='Y'
and paf.assignment_type<>'B'
and ppf.current_employee_flag='Y'
and ppf.effective_end_date > l_agency_effective_date
--and fnd_date.canonical_to_date(ppei.pei_information14) >= l_agency_effective_date
and ppei.pei_information14 IS NOT NULL
and ppf.business_group_id=paf.business_group_id
and ppf.business_group_id= NVL(p_business_group_id,ppf.business_group_id)
AND ghr_api.get_position_agency_code_pos(paf.position_id,paf.business_group_id) like SUBSTR(p_agency_code,1,2)||SUBSTR(p_agency_sub_code,1,2)||'%' ;
SELECT pa.pa_request_id,
pa_ei.rei_information17, pa_ei.rei_information15,
pa.effective_date
FROM ghr_pa_request_extra_info pa_ei, ghr_pa_requests pa
WHERE pa_ei.information_type='GHR_US_PAR_BENEFITS'
AND pa.noa_family_code in ('APP','CONV_APP')
AND pa.RETIREMENT_PLAN IN('K','L','M','N')
AND pa.effective_date > sysdate
AND pa_ei.pa_request_id=pa.pa_request_id
AND status ='FUTURE_ACTION'
AND NVL(pa.agency_code,pa.from_agency_code) LIKE SUBSTR(p_agency_code,1,2)||SUBSTR(p_agency_sub_code,1,2)||'%' ;
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
c.business_group_id business_group_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_agency_effective_date
and a.element_name = 'TSP'
and b.name = 'Status'
and e.assignment_id = l_assignment_id ;
select b.name name,
f.input_value_id input_value_id,
e.effective_start_date effective_start_date,
e.effective_end_date effective_end_date,
e.element_entry_id element_entry_id,
e.assignment_id assignment_id,
e.object_version_number object_version_number,
f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_check_date
and e.element_entry_id = l_element_entry_id
and a.element_name = 'TSP'
and b.name = 'Status'
and e.assignment_id = l_assignment_id
and e.element_entry_id = l_element_entry_id;
select f.screen_entry_value screen_entry_value
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and c.business_group_id = nvl(p_business_group_id,c.business_group_id)
and e.effective_start_date = f.effective_start_date
and e.effective_end_date = f.effective_end_date
and e.effective_end_date > l_agency_effective_date
and a.element_name = 'TSP'
and b.name = 'Status Date'
and e.assignment_id = l_assignment_id ;
select trunc(effective_date)
from fnd_sessions
where session_id = l_session_id;
select object_version_number
from per_people_extra_info
where person_extra_info_id = cp_people_ei_id;
l_update_flag := 0;
l_cotrib_update_flag :=0;
l_update_flag := 1;
l_update_flag := 1;
l_update_flag := 1;
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
if l_update_flag = 1 then
l_update_flag := 0;
ghr_element_entry_api.update_element_entry
( p_datetrack_update_mode => l_datetrack_update_mode
,p_effective_date => l_calculated_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id3 => l_input_value_id
,p_entry_value3 => l_screen_entry_value
,p_effective_start_date => l_out_effective_start_date
,p_effective_end_date => l_out_effective_end_date
,p_update_warning => l_out_update_warning
);
select userenv('sessionid') INTO l_session_id from dual;
INSERT INTO fnd_sessions(SESSION_ID,EFFECTIVE_DATE)
values(l_session_id,l_calculated_date);
update fnd_sessions set effective_date = l_calculated_date
where session_id = l_session_id;
l_cotrib_update_flag:=1;
l_cotrib_update_flag:=0;
l_cotrib_update_flag:=1;
IF l_cotrib_update_flag = 1 THEN
l_cotrib_update_flag:=0;
p_program_update_date => l_peopleei_data.program_update_date ,
p_pei_attribute_category => l_peopleei_data.pei_attribute_category ,
p_pei_attribute1 => l_peopleei_data.pei_attribute1 ,
p_pei_attribute2 => l_peopleei_data.pei_attribute2 ,
p_pei_attribute3 => l_peopleei_data.pei_attribute3 ,
p_pei_attribute4 => l_peopleei_data.pei_attribute4 ,
p_pei_attribute5 => l_peopleei_data.pei_attribute5 ,
p_pei_attribute6 => l_peopleei_data.pei_attribute6 ,
p_pei_attribute7 => l_peopleei_data.pei_attribute7 ,
p_pei_attribute8 => l_peopleei_data.pei_attribute8 ,
p_pei_attribute9 => l_peopleei_data.pei_attribute9 ,
p_pei_attribute10 => l_peopleei_data.pei_attribute10 ,
p_pei_attribute11 => l_peopleei_data.pei_attribute11 ,
p_pei_attribute12 => l_peopleei_data.pei_attribute12 ,
p_pei_attribute13 => l_peopleei_data.pei_attribute13 ,
p_pei_attribute14 => l_peopleei_data.pei_attribute14 ,
p_pei_attribute15 => l_peopleei_data.pei_attribute15 ,
p_pei_attribute16 => l_peopleei_data.pei_attribute16 ,
p_pei_attribute17 => l_peopleei_data.pei_attribute17 ,
p_pei_attribute18 => l_peopleei_data.pei_attribute18 ,
p_pei_attribute19 => l_peopleei_data.pei_attribute19 ,
p_pei_attribute20 => l_peopleei_data.pei_attribute20 ,
p_pei_information_category => l_peopleei_data.pei_information_category ,
p_pei_information1 => l_peopleei_data.pei_information1 ,
p_pei_information2 => l_peopleei_data.pei_information2 ,
p_pei_information3 => l_peopleei_data.pei_information3 ,
p_pei_information4 => l_peopleei_data.pei_information4 ,
p_pei_information5 => l_peopleei_data.pei_information5 ,
p_pei_information6 => l_peopleei_data.pei_information6 ,
p_pei_information7 => l_peopleei_data.pei_information7 ,
p_pei_information8 => l_peopleei_data.pei_information8 ,
p_pei_information9 => l_peopleei_data.pei_information9 ,
p_pei_information10 => l_peopleei_data.pei_information10 ,
p_pei_information11 => l_peopleei_data.pei_information11 ,
p_pei_information12 => l_peopleei_data.pei_information12 ,
p_pei_information13 => l_peopleei_data.pei_information13 ,
p_pei_information14 => l_peopleei_data.pei_information14 ,
p_pei_information15 => l_peopleei_data.pei_information15 ,
p_pei_information16 => l_peopleei_data.pei_information16 ,
p_pei_information17 => l_peopleei_data.pei_information17 ,
p_pei_information18 => l_peopleei_data.pei_information18 ,
p_pei_information19 => l_peopleei_data.pei_information19 ,
p_pei_information20 => l_peopleei_data.pei_information20 ,
p_pei_information21 => l_peopleei_data.pei_information21 ,
p_pei_information22 => l_peopleei_data.pei_information22 ,
p_pei_information23 => l_peopleei_data.pei_information23 ,
p_pei_information24 => l_peopleei_data.pei_information24 ,
p_pei_information25 => l_peopleei_data.pei_information25 ,
p_pei_information26 => l_peopleei_data.pei_information26 ,
p_pei_information27 => l_peopleei_data.pei_information27 ,
p_pei_information28 => l_peopleei_data.pei_information28 ,
p_pei_information29 => l_peopleei_data.pei_information29 ,
p_pei_information30 => l_peopleei_data.pei_information30 ,
p_object_version_number => l_peopleei_data.object_version_number
);
ghr_history_api.post_update_process;
END IF;--IF l_cotrib_update_flag = 1
--Begin Future Actions TSP update
BEGIN --C1
l_tsp_future_rec_cnt :=0;
l_update_flag := 0;
l_update_flag := 1;
l_update_flag := 1;
l_update_flag := 1;
IF l_update_flag = 1 OR l_elig_date_flag =1 THEN
l_tsp_future_rec_cnt := l_tsp_future_rec_cnt+1;
IF l_update_flag = 1 THEN
l_update_flag := 0;
UPDATE ghr_pa_request_extra_info
SET rei_information15 =l_rpa_tsp_status_code
where information_type='GHR_US_PAR_BENEFITS'
AND pa_request_id = l_pa_request_id;
UPDATE ghr_pa_request_extra_info
SET rei_information17 = fnd_date.date_to_canonical(l_rpa_effective_date)
where information_type='GHR_US_PAR_BENEFITS'
AND pa_request_id = l_pa_request_id;
p_message_name => 'Update Script for Future TSP',
p_log_text => 'Total Future Action records Modified successfully.... ' || to_char(l_tsp_future_rec_cnt) || ' rows',
p_log_date => sysdate);
p_message_name => 'Update Script for Future TSP',
p_log_text => 'TSP(Future Actions) Data Not required to modify...',
p_log_date => sysdate);
-- End Future Actions TSP update
END execute_tsp_conversion;