[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_all_people_f per -- Bug 4349372
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 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_BENEFIT_INFO' ,'GHR_US_PAR_RETIRMENT_SYS_INFO');
SELECT employee_assignment_id
FROM ghr_pa_requests
WHERE pa_request_id=p_request_id
AND person_id=p_person_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 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,
to_national_identifier,
employee_date_of_birth,
ehri_employee_id,
agency_code,
effective_date,
first_noa_code,
first_action_la_code1,
first_action_la_code2,
second_noa_code,
NOA_CORRECTED, -- THIS IS NOT SUPPORTED, REPORT BLANK
EFFECTIVE_DATE_CORRECTED, -- THIS IS NOT SUPPORTED, REPORT BLANK
current_appointment_auth1,
current_appointment_auth2,
APPOINTMENT_NTE_DATE,
personnel_office_id,
organizational_component,
sex,
race_national_origin,
handicap_code,
SSN_CORRECTED,
veterans_preference,
tenure,
AGENCY_USE_CODE_FIELD,
AGENCY_USE_TEXT_FIELD,
VETERANS_PREF_FOR_RIF,
FEGLI,
annuitant_indicator,
retirement_plan,
leave_SCD,
SCD_retirement,
SCD_rif,
SCD_SES,
SCD_SPCL_RETIRE,
TSP_SCD,
position_occupied,
FLSA_category,
appropriation_code, -- New
bargaining_unit_status,
supervisory_status,
creditable_military_service,
frozen_service,
from_retirement_coverage,
veterans_status,
education_level,
academic_discipline,
year_degree_attained,
rating_of_record_level,
rating_of_record_pattern,
RATING_OF_RECORD_PERIOD_STARTS, -- New
rating_of_record_period_ends,
PRIOR_FAMILY_NAME, --
PRIOR_GIVEN_NAME, --
PRIOR_MIDDLE_NAME, --
PRIOR_NAME_SUFFIX, -- New
PRIOR_POSITION_TITLE, --
PRIOR_POSITION_NUMBER, --
PRIOR_POSITION_ORG, --
from_pay_plan,
from_occ_code,
from_grade_or_level,
from_step_or_rate,
from_pay_basis,
from_TOTAL_SALARY,
from_basic_pay,
from_ADJ_BASIC_PAY,
from_locality_adj,
from_work_schedule,
from_pay_rate_determinant,
from_duty_station_code,
employee_last_name,
employee_first_name,
employee_middle_names,
name_title,
position_title,
POSITION_NUMBER,
POSITION_ORG,
to_pay_plan,
to_occ_code,
to_grade_or_level,
to_step_or_rate,
to_pay_basis,
to_TOTAL_SALARY,
to_basic_pay,
to_ADJ_BASIC_PAY,
to_locality_adj,
to_supervisory_differential,
to_retention_allowance,
award_dollars,
award_hours,
award_percentage,
to_work_schedule,
PART_TIME_HOURS, --- can v have this as to_part_time_hours ?
to_pay_rate_determinant,
to_duty_station_code,
AGENCY_DATA1,
AGENCY_DATA2,
AGENCY_DATA3,
AGENCY_DATA4,
AGENCY_DATA5,
ACTION_APPROVAL_DATE,
ACTION_AUTHR_FAMILY_NAME,
ACTION_AUTHR_GIVEN_NAME,
ACTION_AUTHR_MIDDLE_NAME,
ACTION_AUTHR_NAME_SUFFIX,
ACTION_AUTHR_TITLE,
REMARKS_TEXT,
race_ethnic_info,
from_spl_rate_supplement,
to_spl_rate_supplement,
--Bug# 6158983
world_citizenship,
health_plan,
special_population_code,
csrs_exc_appts,
fers_exc_appts,
fica_coverage_ind1,
fica_coverage_ind2,
hyp_full_reg_duty_part_emp,
fegli_assg_indicator,
fegli_post_elc_basic_ins_amt,
fegli_court_ord_ind,
fegli_benf_desg_ind,
fehb_event_code,
pareq_last_updated_date,
fehb_elect_eff_date
--Bug# 6158983
)
VALUES(
'DYNAMICS',
USERENV('SESSIONID'),
p_ghr_cpdf_temp_rec.to_national_identifier,
p_ghr_cpdf_temp_rec.employee_date_of_birth,
p_ghr_cpdf_temp_rec.ehri_employee_id, -- new
p_ghr_cpdf_temp_rec.agency_code,
p_ghr_cpdf_temp_rec.effective_date,
p_ghr_cpdf_temp_rec.first_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.second_noa_code,
p_ghr_cpdf_temp_rec.NOA_CORRECTED, -- new
p_ghr_cpdf_temp_rec.EFFECTIVE_DATE_CORRECTED, --new
p_ghr_cpdf_temp_rec.current_appointment_auth1,
p_ghr_cpdf_temp_rec.current_appointment_auth2,
p_ghr_cpdf_temp_rec.APPOINTMENT_NTE_DATE, -- New
p_ghr_cpdf_temp_rec.personnel_office_id,
p_ghr_cpdf_temp_rec.organizational_component,
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.SSN_CORRECTED, --new
p_ghr_cpdf_temp_rec.veterans_preference,
p_ghr_cpdf_temp_rec.tenure,
p_ghr_cpdf_temp_rec.AGENCY_USE_CODE_FIELD,
p_ghr_cpdf_temp_rec.AGENCY_USE_TEXT_FIELD,
p_ghr_cpdf_temp_rec.VETERANS_PREF_FOR_RIF,
p_ghr_cpdf_temp_rec.FEGLI, -- existing but not coded
p_ghr_cpdf_temp_rec.annuitant_indicator, -- existing but nt coded
p_ghr_cpdf_temp_rec.retirement_plan,
p_ghr_cpdf_temp_rec.LEAVE_SCD, --new
p_ghr_cpdf_temp_rec.SCD_retirement,
p_ghr_cpdf_temp_rec.SCD_RIF,
p_ghr_cpdf_temp_rec.SCD_SES, -- NEW
p_ghr_cpdf_temp_rec.SCD_spcl_retire, -- NEW
p_ghr_cpdf_temp_rec.TSP_SCD, -- NEW
p_ghr_cpdf_temp_rec.position_occupied,
p_ghr_cpdf_temp_rec.FLSA_category, -- existing but not coded
p_ghr_cpdf_temp_rec.appropriation_code, -- NEW
p_ghr_cpdf_temp_rec.bargaining_unit_status, -- existing but not coded
p_ghr_cpdf_temp_rec.supervisory_status,
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.education_level,
p_ghr_cpdf_temp_rec.academic_discipline,
p_ghr_cpdf_temp_rec.year_degree_attained,
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_starts, -- NEW
p_ghr_cpdf_temp_rec.rating_of_record_period_ends,
p_ghr_cpdf_temp_rec.PRIOR_FAMILY_NAME, --
p_ghr_cpdf_temp_rec.PRIOR_GIVEN_NAME, --
p_ghr_cpdf_temp_rec.PRIOR_MIDDLE_NAME, --
p_ghr_cpdf_temp_rec.PRIOR_NAME_SUFFIX, -- New
p_ghr_cpdf_temp_rec.PRIOR_POSITION_TITLE, --
p_ghr_cpdf_temp_rec.PRIOR_POSITION_NUMBER, --
p_ghr_cpdf_temp_rec.PRIOR_POSITION_ORG, --
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_pay_basis,
p_ghr_cpdf_temp_rec.from_total_salary, -- existing but not coded
p_ghr_cpdf_temp_rec.from_basic_pay,
p_ghr_cpdf_temp_rec.from_adj_basic_pay,
p_ghr_cpdf_temp_rec.from_locality_adj,
p_ghr_cpdf_temp_rec.from_work_schedule,
p_ghr_cpdf_temp_rec.from_pay_rate_determinant,
p_ghr_cpdf_temp_rec.from_duty_station_code,
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.POSITION_NUMBER, -- NEW
p_ghr_cpdf_temp_rec.POSITION_ORG, -- NEW
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_pay_basis,
p_ghr_cpdf_temp_rec.to_total_salary, -- existing but not coded
p_ghr_cpdf_temp_rec.to_basic_pay,
p_ghr_cpdf_temp_rec.TO_ADJ_BASIC_PAY, -- NEW
p_ghr_cpdf_temp_rec.to_locality_adj,
p_ghr_cpdf_temp_rec.to_supervisory_differential,
p_ghr_cpdf_temp_rec.to_retention_allowance,
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.to_work_schedule,
p_ghr_cpdf_temp_rec.PART_TIME_HOURS, --NEW
p_ghr_cpdf_temp_rec.to_pay_rate_determinant,
p_ghr_cpdf_temp_rec.to_duty_station_code,
p_ghr_cpdf_temp_rec.AGENCY_DATA1, -- NEW
p_ghr_cpdf_temp_rec.AGENCY_DATA2, -- NEW
p_ghr_cpdf_temp_rec.AGENCY_DATA3, --NEW
p_ghr_cpdf_temp_rec.AGENCY_DATA4, -- NEW
p_ghr_cpdf_temp_rec.AGENCY_DATA5, --NEW
p_ghr_cpdf_temp_rec.ACTION_APPROVAL_DATE, --NEW
p_ghr_cpdf_temp_rec.ACTION_AUTHR_FAMILY_NAME, --NEW
p_ghr_cpdf_temp_rec.ACTION_AUTHR_GIVEN_NAME, --NEW
p_ghr_cpdf_temp_rec.ACTION_AUTHR_MIDDLE_NAME, --NEW
p_ghr_cpdf_temp_rec.ACTION_AUTHR_NAME_SUFFIX, --NEW
p_ghr_cpdf_temp_rec.ACTION_AUTHR_TITLE, --NEW
p_ghr_cpdf_temp_rec.REMARKS_TEXT, --NEW
p_ghr_cpdf_temp_rec.race_ethnic_info,
p_ghr_cpdf_temp_rec.from_spl_rate_supplement,
p_ghr_cpdf_temp_rec.to_spl_rate_supplement,
--Bug# 6158983
p_ghr_cpdf_temp_rec.world_citizenship,
p_ghr_cpdf_temp_rec.health_plan,
p_ghr_cpdf_temp_rec.special_population_code,
p_ghr_cpdf_temp_rec.csrs_exc_appts,
p_ghr_cpdf_temp_rec.fers_exc_appts,
p_ghr_cpdf_temp_rec.fica_coverage_ind1,
p_ghr_cpdf_temp_rec.fica_coverage_ind2,
p_ghr_cpdf_temp_rec.hyp_full_reg_duty_part_emp,
p_ghr_cpdf_temp_rec.fegli_assg_indicator,
p_ghr_cpdf_temp_rec.fegli_post_elc_basic_ins_amt,
p_ghr_cpdf_temp_rec.fegli_court_ord_ind,
p_ghr_cpdf_temp_rec.fegli_benf_desg_ind,
p_ghr_cpdf_temp_rec.fehb_event_code,
p_ghr_cpdf_temp_rec.pareq_last_updated_date,
p_ghr_cpdf_temp_rec.fehb_elect_eff_date
--Bug# 6158983
);
END insert_row;
SELECT *
FROM GHR_CPDF_TEMP
WHERE SESSION_ID = c_session_id
AND report_type='DYNAMICS'
ORDER BY agency_code,to_national_identifier,effective_date,pareq_last_updated_date;
SELECT outfile_name
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = c_request_id;
select value
into l_audit_log_dir
from v$parameter
where name = 'utl_file_dir';
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_all_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'
AND decode(hr_general.get_xbg_profile,'Y',per.business_group_id , hr_general.get_business_group_id) = per.business_group_id;
SELECT last_name, middle_names, first_name,title,business_group_id
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_eff_date between effective_start_date and effective_end_date;
SELECT user_name_employee_id approver_id
FROM ghr_pa_routing_history
WHERE pa_request_id = c_pa_request_id
AND approval_status = 'APPROVE'
AND action_taken IN ('UPDATE_HR','FUTURE_ACTION');
SELECT person_id
FROM ghr_pois
WHERE personnel_office_id = c_poid;
SELECT name
FROM hr_organization_units
WHERE organization_id = ( SELECT organization_id
FROM hr_positions_f
WHERE position_id=p_position_id
AND p_eff_date between effective_start_date and effective_end_date);
SELECT name
FROM hr_organization_units
WHERE organization_id = p_org_id
AND p_eff_date between date_from and NVL(date_to,to_Date('31/12/4712','DD/MM/YYYY'));
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 employee_national_identifier
FROM ghr_pa_requests
WHERE pa_request_id = p_altered_pa_request_id
AND employee_national_identifier <> p_to_national_identifier;
SELECT Description
FROM ghr_pa_remarks
WHERE pa_request_id=p_pa_request_id;
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);
l_ghr_cpdf_temp_rec.pareq_last_updated_date := l_ghr_pa_requests_rec.sf50_approval_date;
insert_row(l_ghr_cpdf_temp_rec);