The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM ghr_cpdf_temp
WHERE report_type ='DYNAMICS'
AND session_id = USERENV('SESSIONID');
SELECT per.sex
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date;
SELECT par.pa_request_id
FROM ghr_pa_requests par
CONNECT BY par.pa_request_id = prior par.altered_pa_request_id
START WITH par.pa_request_id = p_pa_request_id;
SELECT *
FROM ghr_pa_request_extra_info rei
WHERE rei.information_type = p_information_type
AND rei.pa_request_id = cp_pa_request_id;
SELECT per.employee_number
FROM per_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;
ELSE -- Added select for bug# 1389262
DECLARE
l_effective_date DATE;
SELECT MAX(pan.date_from)
INTO l_effective_date
FROM per_person_analyses pan,
fnd_id_flex_structures flx
WHERE pan.id_flex_num = flx.id_flex_num
AND flx.id_flex_code = 'PEA'
AND flx.application_id = 800
AND flx.id_flex_structure_code = 'US_FED_PERF_APPRAISAL'
AND pan.person_id = p_person_id;
SELECT rit.information_type
FROM ghr_noa_families nfa
,ghr_pa_request_info_types rit
WHERE rit.noa_family_code = nfa.noa_family_code
AND (nfa.nature_of_action_id = p_first_noa_id
OR nfa.nature_of_action_id = p_second_noa_id)
AND rit.information_type IN ('GHR_US_PAR_AWARDS_BONUS' ,'GHR_US_PAR_APPT_INFO'
,'GHR_US_PAR_APPT_TRANSFER' ,'GHR_US_PAR_CONV_APP'
,'GHR_US_PAR_RETURN_TO_DUTY','GHR_US_PAR_CHG_RETIRE_PLAN'
,'GHR_US_PAR_CHG_SCD');
select first_noa_id
from ghr_pa_requests
where pa_request_id = (select min(pa_request_id)
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = p_pa_request_id);
select rpa_type,
mass_action_id,
first_noa_code,
second_noa_code
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
IF p_status = 'UPDATE_HR_COMPLETE' THEN
--
IF p_altered_pa_request_id IS NULL THEN -- ie nothing is really being corrected
l_pa_request_id := p_pa_request_id;
SELECT par.employee_national_identifier prev_ssn
FROM ghr_pa_requests par
WHERE par.pa_request_id = p_altered_pa_request_id;
SELECT NVL(equivalent_pay_plan, '@#')
INTO l_result
FROM ghr_pay_plans
WHERE pay_plan = p_pay_plan;
SELECT lpa.locality_pay_area_code
INTO l_result
FROM ghr_locality_pay_areas_f lpa
,ghr_duty_stations_f dst
WHERE dst.duty_station_id = p_duty_station_id
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN dst.effective_start_date and dst.effective_end_date
AND dst.locality_pay_area_id = lpa.locality_pay_area_id
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN lpa.effective_start_date and lpa.effective_end_date;
SELECT lpa.locality_pay_area_code
INTO l_result
FROM ghr_locality_pay_areas_f lpa
,ghr_duty_stations_f dst
WHERE dst.duty_station_code = p_duty_station_code
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN dst.effective_start_date and dst.effective_end_date
AND dst.locality_pay_area_id = lpa.locality_pay_area_id
AND NVL(p_effective_date,TRUNC(sysdate))
BETWEEN lpa.effective_start_date and lpa.effective_end_date;
PROCEDURE insert_row (p_ghr_cpdf_temp_rec IN ghr_cpdf_temp%rowtype) IS
BEGIN
INSERT INTO ghr_cpdf_temp(
report_type,
session_id,
agency_code,
organizational_component,
personnel_office_id,
to_national_identifier,
employee_date_of_birth,
veterans_preference,
tenure,
service_comp_date,
retirement_plan,
creditable_military_service,
frozen_service,
from_retirement_coverage,
veterans_status,
sex,
race_national_origin,
handicap_code,
first_noa_code,
second_noa_code,
first_action_la_code1,
first_action_la_code2,
effective_date,
to_pay_plan,
to_occ_code,
to_grade_or_level,
to_step_or_rate,
to_basic_pay,
to_pay_basis,
to_work_schedule,
to_pay_rate_determinant,
position_occupied,
supervisory_status,
to_duty_station_code,
current_appointment_auth1,
current_appointment_auth2,
rating_of_record_level,
rating_of_record_pattern,
rating_of_record_period_ends,
individual_group_award,
award_amount,
benefit_amount,
employee_last_name,
from_pay_plan,
from_occ_code,
from_grade_or_level,
from_step_or_rate,
from_basic_pay,
from_pay_basis,
from_work_schedule,
from_pay_rate_determinant,
from_national_identifier,
from_locality_adj,
from_duty_station_code,
to_locality_adj,
to_staffing_differential,
to_supervisory_differential,
to_retention_allowance,
education_level,
academic_discipline,
year_degree_attained,
-- employee_last_name,
employee_first_name,
employee_middle_names,
name_title,
position_title,
award_dollars,
award_hours,
award_percentage,
SCD_retirement,
SCD_rif ,
race_ethnic_info
)
VALUES(
'DYNAMICS',
USERENV('SESSIONID'),
p_ghr_cpdf_temp_rec.agency_code,
p_ghr_cpdf_temp_rec.organizational_component,
p_ghr_cpdf_temp_rec.personnel_office_id,
p_ghr_cpdf_temp_rec.to_national_identifier,
p_ghr_cpdf_temp_rec.employee_date_of_birth,
p_ghr_cpdf_temp_rec.veterans_preference,
p_ghr_cpdf_temp_rec.tenure,
p_ghr_cpdf_temp_rec.service_comp_date,
p_ghr_cpdf_temp_rec.retirement_plan,
p_ghr_cpdf_temp_rec.creditable_military_service,
p_ghr_cpdf_temp_rec.frozen_service,
p_ghr_cpdf_temp_rec.from_retirement_coverage,
p_ghr_cpdf_temp_rec.veterans_status,
p_ghr_cpdf_temp_rec.sex,
p_ghr_cpdf_temp_rec.race_national_origin,
p_ghr_cpdf_temp_rec.handicap_code,
p_ghr_cpdf_temp_rec.first_noa_code,
p_ghr_cpdf_temp_rec.second_noa_code,
p_ghr_cpdf_temp_rec.first_action_la_code1,
p_ghr_cpdf_temp_rec.first_action_la_code2,
p_ghr_cpdf_temp_rec.effective_date,
p_ghr_cpdf_temp_rec.to_pay_plan,
p_ghr_cpdf_temp_rec.to_occ_code,
p_ghr_cpdf_temp_rec.to_grade_or_level,
p_ghr_cpdf_temp_rec.to_step_or_rate,
p_ghr_cpdf_temp_rec.to_basic_pay,
p_ghr_cpdf_temp_rec.to_pay_basis,
p_ghr_cpdf_temp_rec.to_work_schedule,
p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
p_ghr_cpdf_temp_rec.position_occupied,
p_ghr_cpdf_temp_rec.supervisory_status,
p_ghr_cpdf_temp_rec.to_duty_station_code,
p_ghr_cpdf_temp_rec.current_appointment_auth1,
p_ghr_cpdf_temp_rec.current_appointment_auth2,
p_ghr_cpdf_temp_rec.rating_of_record_level,
p_ghr_cpdf_temp_rec.rating_of_record_pattern,
p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
p_ghr_cpdf_temp_rec.individual_group_award,
p_ghr_cpdf_temp_rec.award_amount,
p_ghr_cpdf_temp_rec.benefit_amount,
p_ghr_cpdf_temp_rec.employee_last_name,
p_ghr_cpdf_temp_rec.from_pay_plan,
p_ghr_cpdf_temp_rec.from_occ_code,
p_ghr_cpdf_temp_rec.from_grade_or_level,
p_ghr_cpdf_temp_rec.from_step_or_rate,
p_ghr_cpdf_temp_rec.from_basic_pay,
p_ghr_cpdf_temp_rec.from_pay_basis,
p_ghr_cpdf_temp_rec.from_work_schedule,
p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
p_ghr_cpdf_temp_rec.from_national_identifier,
p_ghr_cpdf_temp_rec.from_locality_adj,
p_ghr_cpdf_temp_rec.from_duty_station_code,
p_ghr_cpdf_temp_rec.to_locality_adj,
p_ghr_cpdf_temp_rec.to_staffing_differential,
p_ghr_cpdf_temp_rec.to_supervisory_differential,
p_ghr_cpdf_temp_rec.to_retention_allowance,
p_ghr_cpdf_temp_rec.education_level,
p_ghr_cpdf_temp_rec.academic_discipline,
p_ghr_cpdf_temp_rec.year_degree_attained,
-- p_ghr_cpdf_temp_rec.employee_last_name,
p_ghr_cpdf_temp_rec.employee_first_name,
p_ghr_cpdf_temp_rec.employee_middle_names,
p_ghr_cpdf_temp_rec.name_title,
p_ghr_cpdf_temp_rec.position_title,
p_ghr_cpdf_temp_rec.award_dollars,
p_ghr_cpdf_temp_rec.award_hours,
p_ghr_cpdf_temp_rec.award_percentage,
p_ghr_cpdf_temp_rec.SCD_retirement,
p_ghr_cpdf_temp_rec.SCD_rif,
p_ghr_cpdf_temp_rec.race_ethnic_info
);
END insert_row;
SELECT INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
LENGTH(p_last_name)
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'GHR_US_NAME_SUFFIX'
AND TRUNC(p_report_date) BETWEEN NVL(START_DATE_ACTIVE,p_report_date)
AND NVL(END_DATE_ACTIVE,p_report_date)
AND RTRIM(SUBSTR(TRANSLATE(UPPER(p_last_name),',.',' '),
INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
LENGTH(p_last_name)),' ') = ' '||UPPER(LOOKUP_CODE)
AND ROWNUM = 1;
SELECT par.*
FROM ghr_pa_requests par,
per_people_f per
WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
AND par.person_id = per.person_id
AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
AND per.effective_end_date
AND trunc(par.sf50_approval_date) BETWEEN p_start_date AND p_end_date
--bug #6976546 removed 'FUTURE_ACTION'
AND par.status IN ('UPDATE_HR_COMPLETE')
AND par.effective_date >= add_months(p_end_date,-24)
--Bug # 10158843 added Bug # 9451305 removed effective date comparison with 6 months future to end date
AND par.effective_date <= p_end_date --add_months(p_end_date,6)
AND exclude_agency(NVL(par.agency_code,par.from_agency_code)) <> 'TRUE'
AND exclude_noac(par.first_noa_code,par.second_noa_code,par.noa_family_code) <> 'TRUE'
--Bug # 10158391 added the union condition to consider the records approved in last month and effective in the reporting period
UNION
SELECT par.*
FROM ghr_pa_requests par,
per_people_f per
WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
AND par.person_id = per.person_id
AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
AND per.effective_end_date
--Bug # 11938755 removed approval date validation
--AND trunc(par.sf50_approval_date) BETWEEN add_months(p_start_date,-1) AND p_start_date
--bug #6976546 removed 'FUTURE_ACTION'
AND par.status IN ('UPDATE_HR_COMPLETE')
AND par.effective_date between p_start_date and p_end_date
AND exclude_agency(NVL(par.agency_code,par.from_agency_code)) <> 'TRUE'
AND exclude_noac(par.first_noa_code,par.second_noa_code,par.noa_family_code) <> 'TRUE';
SELECT title,last_name
FROM per_all_people_f
WHERE person_id = p_person_id;
SELECT REI_INFORMATION3 rif ,REI_INFORMATION8 ret
FROM ghr_pa_request_extra_info parei
WHERE parei.pa_request_id=p_pa_request_id
AND parei.information_type='GHR_US_PAR_CHG_SCD';
Select fam.noa_family_code
from ghr_noa_families nof,
ghr_families fam
where nof.nature_of_action_id = p_noa_id
and fam.noa_family_code = nof.noa_family_code
and nvl(fam.proc_method_flag,hr_api.g_varchar2) = 'Y'
and p_effective_date
between nvl(fam.start_date_active,p_effective_date)
and nvl(fam.end_date_active,p_effective_date);
insert_row(l_ghr_cpdf_temp_rec);
ghr_sf52_pre_update.get_auth_codes
(p_pa_req_rec => l_ghr_pa_requests_rec
,p_auth_code1 => l_ghr_cpdf_temp_rec.current_appointment_auth1
,p_auth_code2 => l_ghr_cpdf_temp_rec.current_appointment_auth2);
insert_row(l_ghr_cpdf_temp_rec);