[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select gdf.segment1
,gdf.segment2
from per_grades grd,
per_grade_definitions gdf
where grd.grade_id = grd_id
and grd.grade_definition_id = gdf.grade_definition_id;
SELECT *
INTO g_requests_rec
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
SELECT paf.assignment_id, paf.grade_id
INTO l_assignment_id, l_grade_id
FROM per_assignments_f paf
WHERE paf.person_id = g_requests_rec.person_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type <> 'B'
AND g_requests_rec.effective_date BETWEEN
paf.effective_start_date AND
NVL(paf.effective_end_date,
g_requests_rec.effective_date+1);
SELECT equivalent_pay_plan
INTO g_equivalent_pay_plan
FROM ghr_pay_plans
WHERE pay_plan = g_pay_plan;
SELECT locality_pay_area_id
FROM ghr_duty_stations_f
WHERE duty_station_id=p_ds_id
AND p_eff_date between effective_start_date and effective_end_date;
SELECT adjustment_percentage
FROM ghr_locality_pay_areas_f
WHERE locality_pay_area_id = p_ds_loc_area_id
AND p_eff_date between effective_start_date and effective_end_date;
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_remark_desc,
p_information1 => l_remark_information1,
p_information2 => l_remark_information2,
p_information3 => l_remark_information3,
p_information4 => l_remark_information4,
p_information5 => l_remark_information5,
p_desc_out => l_remark_desc_out
);
l_location := 'Apply_894_Rules:Determining Insertion Value';
IF l_la_code1 IS NOT NULL THEN -- Update GHR_PA_REQUESTS with new LACs
l_location := 'Apply_894_Rules:Replacing Insertion Value';
SELECT description
INTO l_la_desc1
FROM fnd_common_lookups fcl
WHERE fcl.lookup_code = l_la_code1
AND fcl.application_id = 800
AND fcl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
AND fcl.enabled_flag = 'Y'
AND g_requests_rec.effective_date BETWEEN
NVL(fcl.start_date_active,
g_requests_rec.effective_date) AND
NVL(fcl.end_date_active, g_requests_rec.effective_date);
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_la_desc1,
p_information1 => l_insrt_value1,
p_desc_out => l_la_desc1_out);
SELECT description
INTO l_la_desc2
FROM fnd_common_lookups fcl
WHERE fcl.lookup_code = l_la_code2
AND fcl.application_id = 800
AND fcl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
AND fcl.enabled_flag = 'Y'
AND g_requests_rec.effective_date BETWEEN
NVL(fcl.start_date_active,
g_requests_rec.effective_date) AND
NVL(fcl.end_date_active,
g_requests_rec.effective_date);
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_la_desc2,
p_information1 => l_insrt_value2,
p_desc_out => l_la_desc2_out);
UPDATE GHR_PA_REQUESTS
SET first_action_la_code1 = l_la_code1,
first_action_la_code2 = l_la_code2,
first_action_la_desc1 = l_la_desc1,
first_action_la_desc2 = DECODE(l_la_code2, NULL, NULL,
l_la_desc2),
first_lac1_information1 = DECODE(l_la_code1, 'ZLM',
l_insrt_value1,'UNM',l_insrt_value1, NULL),
first_lac1_information2 = NULL,
first_lac1_information3 = NULL,
first_lac1_information4 = NULL,
first_lac1_information5 = NULL,
first_lac2_information1 = DECODE(l_la_code2, 'ZLM',
l_insrt_value2, NULL),
first_lac2_information2 = NULL,
first_lac2_information3 = NULL,
first_lac2_information4 = NULL,
first_lac2_information5 = NULL
WHERE pa_request_id = p_pa_request_id;
SELECT first_action_la_code1
INTO l_la_code1
FROM GHR_PA_REQUESTS
WHERE pa_request_id = p_pa_request_id;
l_location := 'Apply_895_Rules:Replacing Insertion Value';
SELECT description
INTO l_la_desc1
FROM fnd_common_lookups fcl
WHERE fcl.lookup_code = l_la_code1
AND fcl.application_id = 800
AND fcl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
AND fcl.enabled_flag = 'Y'
AND g_requests_rec.effective_date BETWEEN
NVL(fcl.start_date_active,
g_requests_rec.effective_date) AND
NVL(fcl.end_date_active, g_requests_rec.effective_date);
UPDATE GHR_PA_REQUESTS
SET first_action_la_code1 = l_la_code1,
first_action_la_desc1 = l_la_desc1,
first_lac1_information1 = NULL,
first_lac1_information2 = NULL,
first_lac1_information3 = NULL,
first_lac1_information4 = NULL,
first_lac1_information5 = NULL
WHERE pa_request_id = p_pa_request_id;
SELECT first_action_la_code1
INTO l_la_code1
FROM GHR_PA_REQUESTS
WHERE pa_request_id = p_pa_request_id;
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_la_desc1,
p_information1 => l_insrt_value,
p_desc_out => l_la_desc1_out);
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_la_desc2,
p_information1 => l_insrt_value,
p_desc_out => l_la_desc2_out);
UPDATE GHR_PA_REQUESTS
SET first_action_la_code1 = l_la_code1,
first_action_la_code2 = l_la_code2,
first_action_la_desc1 = l_la_desc1,
first_action_la_desc2 = l_la_Desc2,
first_lac1_information1 = DECODE(l_la_code1, 'ZLM',
l_insrt_value,NULL),
first_lac1_information2 = NULL,
first_lac1_information3 = NULL,
first_lac1_information4 = NULL,
first_lac1_information5 = NULL,
first_lac2_information1 = DECODE(l_la_code2, 'ZLM',
l_insrt_value, NULL),
first_lac2_information2 = NULL,
first_lac2_information3 = NULL,
first_lac2_information4 = NULL,
first_lac2_information5 = NULL
WHERE pa_request_id = p_pa_request_id;