[Home] [Help]
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');
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 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)
AND par.effective_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';
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';
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);