[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM ghr_cpdf_temp
WHERE report_type = 'STATUS'
AND session_id = userenv('SESSIONID')
;
SELECT per.hire_date
FROM per_people_v per
WHERE per.person_id = p_person_id; */
SELECT
DECODE(PER.CURRENT_EMPLOYEE_FLAG, 'Y', PPS.DATE_START, DECODE(PER.CURRENT_NPW_FLAG, 'Y', PPP.DATE_START, NULL)) hire_date
FROM
PER_PEOPLE_F PER ,
PER_PERIODS_OF_SERVICE PPS ,
PER_PERIODS_OF_PLACEMENT PPP
WHERE
PPS.PERSON_ID (+) = PER.PERSON_ID AND
PPP.PERSON_ID (+) = PER.PERSON_ID AND
PER.PERSON_ID = p_person_id AND
(
(PER.EMPLOYEE_NUMBER IS NULL) OR
(PER.EMPLOYEE_NUMBER IS NOT NULL AND
PPS.DATE_START = (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID = PER.PERSON_ID AND
PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND
((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL AND
PPP.DATE_START = (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND
PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)));
INSERT INTO fnd_sessions
(session_id
,effective_date)
VALUES
(userenv('sessionid')
,p_report_date);
DELETE FROM fnd_sessions
WHERE session_id = userenv('sessionid');
SELECT SEX,
DATE_OF_BIRTH,
NATIONAL_IDENTIFIER
FROM PER_PEOPLE_F
WHERE (TRUNC(p_sr_report_date) between effective_start_date and
effective_end_date) AND
PERSON_ID = g_person_id;
SELECT per.employee_number
FROM per_people_f per
WHERE per.person_id = p_sr_person_id
AND NVL(p_sr_report_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT SEGMENT1,
SEGMENT2
FROM PER_GRADE_DEFINITIONS
WHERE GRADE_DEFINITION_ID =
(SELECT MAX(GRADE_DEFINITION_ID)
FROM PER_GRADES
WHERE GRADE_ID = g_grade_id);
SELECT SEGMENT1
FROM PER_JOB_DEFINITIONS
WHERE JOB_DEFINITION_ID =
(SELECT JOB_DEFINITION_ID
FROM PER_JOBS
WHERE JOB_ID = g_job_id);
SELECT DUTY_STATION_CODE, DUTY_STATION_ID
FROM GHR_DUTY_STATIONS_F
WHERE trunc(p_sr_report_date) between effective_start_date and
nvl(effective_end_date, p_sr_report_date)
AND DUTY_STATION_ID =
(SELECT LEI_INFORMATION3
FROM HR_LOCATION_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
AND LOCATION_ID = g_location_id);
SELECT STATE_OR_COUNTRY_CODE
FROM GHR_DUTY_STATIONS_F
WHERE trunc(p_report_date) between effective_start_date and
nvl(effective_end_date, p_report_date)
AND DUTY_STATION_ID =
(SELECT LEI_INFORMATION3
FROM HR_LOCATION_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
AND LOCATION_ID = g_location_id);
PROCEDURE insert_row
IS
l_proc varchar2(30) := 'insert_row';
INSERT INTO ghr_cpdf_temp (
report_type
,session_id
,academic_discipline
,agency_code
,annuitant_indicator
,award_amount
,bargaining_unit_status
,benefit_amount
,citizenship
,creditable_military_service
,current_appointment_auth1
,current_appointment_auth2
,to_duty_station_code
,education_level
,effective_date
,employee_date_of_birth
,employee_first_name
,employee_last_name
,employee_middle_names
,from_national_identifier
,fegli
,fers_coverage
,first_action_la_code1
,first_action_la_code2
,first_noa_code
,flsa_category
,from_basic_pay
,from_duty_station_code
,from_grade_or_level
,from_locality_adj
,from_occ_code
,from_pay_table_id
,from_pay_basis
,from_pay_plan
,from_pay_rate_determinant
,from_retirement_coverage
,from_step_or_rate
,from_total_salary
,from_work_schedule
,frozen_service
,functional_class
,handicap_code
,health_plan
,individual_group_award
,organizational_component
,pay_status
,personnel_office_id
,position_occupied
,race_national_origin
,rating_of_record
,rating_of_record_level
,rating_of_record_pattern
,rating_of_record_period_ends
,retained_grade_or_level
,retained_pay_plan
,retained_step_or_rate
,retirement_plan
,second_noa_code
,service_comp_date
,sex
,supervisory_status
,tenure
,to_basic_pay
,to_grade_or_level
,to_locality_adj
,to_national_identifier
,to_occ_code
,to_pay_basis
,to_pay_plan
,to_pay_rate_determinant
,to_pay_table_id
,to_retention_allowance
,to_staffing_differential
,to_step_or_rate
,to_supervisory_differential
,to_total_salary
,to_work_schedule
,veterans_preference
,veterans_status
,year_degree_attained,
-- employee_first_name,
-- employee_middle_names,
name_title,
position_title,
award_dollars,
award_hours,
award_percentage,
SCD_retirement,
SCD_rif,
race_ethnic_info
-- created_by,
-- creation_Date,
-- last_updated_by,
-- last_update_date,
-- last_update_login
)
values (
'STATUS'
,userenv('SESSIONID')
,g_ghr_cpdf_temp.academic_discipline
,g_ghr_cpdf_temp.agency_code
,g_ghr_cpdf_temp.annuitant_indicator
,g_ghr_cpdf_temp.award_amount
,g_ghr_cpdf_temp.bargaining_unit_status
,g_ghr_cpdf_temp.benefit_amount
,g_ghr_cpdf_temp.citizenship
,g_ghr_cpdf_temp.creditable_military_service
,g_ghr_cpdf_temp.current_appointment_auth1
,g_ghr_cpdf_temp.current_appointment_auth2
,g_ghr_cpdf_temp.to_duty_station_code
,g_ghr_cpdf_temp.education_level
,g_ghr_cpdf_temp.effective_date
,g_ghr_cpdf_temp.employee_date_of_birth
,g_ghr_cpdf_temp.employee_first_name
,g_ghr_cpdf_temp.employee_last_name
,g_ghr_cpdf_temp.employee_middle_names
,g_ghr_cpdf_temp.from_national_identifier
,g_ghr_cpdf_temp.fegli
,g_ghr_cpdf_temp.fers_coverage
,g_ghr_cpdf_temp.first_action_la_code1
,g_ghr_cpdf_temp.first_action_la_code2
,g_ghr_cpdf_temp.first_noa_code
,g_ghr_cpdf_temp.flsa_category
,g_ghr_cpdf_temp.from_basic_pay
,g_ghr_cpdf_temp.from_duty_station_code
,g_ghr_cpdf_temp.from_grade_or_level
,g_ghr_cpdf_temp.from_locality_adj
,g_ghr_cpdf_temp.from_occ_code
,g_ghr_cpdf_temp.from_pay_table_id
,g_ghr_cpdf_temp.from_pay_basis
,g_ghr_cpdf_temp.from_pay_plan
,g_ghr_cpdf_temp.from_pay_rate_determinant
,g_ghr_cpdf_temp.from_retirement_coverage
,g_ghr_cpdf_temp.from_step_or_rate
,g_ghr_cpdf_temp.from_total_salary
,g_ghr_cpdf_temp.from_work_schedule
,g_ghr_cpdf_temp.frozen_service
,g_ghr_cpdf_temp.functional_class
,g_ghr_cpdf_temp.handicap_code
,g_ghr_cpdf_temp.health_plan
,g_ghr_cpdf_temp.individual_group_award
,g_ghr_cpdf_temp.organizational_component
,g_ghr_cpdf_temp.pay_status
,g_ghr_cpdf_temp.personnel_office_id
,g_ghr_cpdf_temp.position_occupied
,g_ghr_cpdf_temp.race_national_origin
,g_ghr_cpdf_temp.rating_of_record
,g_ghr_cpdf_temp.rating_of_record_level
,g_ghr_cpdf_temp.rating_of_record_pattern
,g_ghr_cpdf_temp.rating_of_record_period_ends
,g_ghr_cpdf_temp.retained_grade_or_level
,g_ghr_cpdf_temp.retained_pay_plan
,g_ghr_cpdf_temp.retained_step_or_rate
,g_ghr_cpdf_temp.retirement_plan
,g_ghr_cpdf_temp.second_noa_code
,g_ghr_cpdf_temp.service_comp_date
,g_ghr_cpdf_temp.sex
,g_ghr_cpdf_temp.supervisory_status
,g_ghr_cpdf_temp.tenure
,g_ghr_cpdf_temp.to_basic_pay
,g_ghr_cpdf_temp.to_grade_or_level
,g_ghr_cpdf_temp.to_locality_adj
,g_ghr_cpdf_temp.to_national_identifier
,g_ghr_cpdf_temp.to_occ_code
,g_ghr_cpdf_temp.to_pay_basis
,g_ghr_cpdf_temp.to_pay_plan
,g_ghr_cpdf_temp.to_pay_rate_determinant
,g_ghr_cpdf_temp.to_pay_table_id
,g_ghr_cpdf_temp.to_retention_allowance
,g_ghr_cpdf_temp.to_staffing_differential
,g_ghr_cpdf_temp.to_step_or_rate
,g_ghr_cpdf_temp.to_supervisory_differential
,g_ghr_cpdf_temp.to_total_salary
,g_ghr_cpdf_temp.to_work_schedule
,g_ghr_cpdf_temp.veterans_preference
,g_ghr_cpdf_temp.veterans_status
,g_ghr_cpdf_temp.year_degree_attained,
-- p_ghr_cpdf_temp_rec.employee_first_name,
-- p_ghr_cpdf_temp_rec.employee_middle_names,
g_ghr_cpdf_temp.name_title,
g_ghr_cpdf_temp.position_title,
g_ghr_cpdf_temp.award_dollars,
g_ghr_cpdf_temp.award_hours,
g_ghr_cpdf_temp.award_percentage,
g_ghr_cpdf_temp.SCD_retirement,
g_ghr_cpdf_temp.SCD_rif,
g_ghr_cpdf_temp.race_ethnic_info
-- 1,sysdate,1,sysdate,1
);
END insert_row;
DELETE FROM ghr_cpdf_temp
WHERE (report_type='STATUS')
AND (
-- *** SUPPRESS NON APPROPRIATED EMPLOYEES / COMMISSIONED OFFICERS
( to_pay_plan IN ('NA','NL','NS','CC') )
-- *** EXCLUDE NON US CITIZENS WORKING IN FOREIGN DUTY STATIONS
OR ( from_duty_station_code = 'Y'
AND decode(citizenship, NULL, ' ', citizenship) <> '1' )
-- *** EXCLUDE CERTAIN AGENCIES
OR ( agency_code IN ('CI00','DD05','DD28','FR00',
'PO00','PJ00','TV00','WH01') )
-- *** EXCLUDE CERTAIN SUBELEMENTS
OR ( substr(agency_code,1,2) IN ('LL','LB','LA','LD','LG','LC') )
-- *** EXCLUDE NON SELECTED AGENCY CODE
-- OR ( decode(agency_code, NULL, ' ', agency_code)
-- not like DECODE(g_agency,NULL,'%',rtrim(g_agency)||'%'))
)
;
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 asg.assignment_id,
asg.person_id,
asg.position_id,
asg.grade_id,
asg.job_id,
asg.location_id,
asg.effective_start_date,
asg.business_group_id,
ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) agency_code
FROM PER_ASSIGNMENTS_F asg
WHERE
-- only consider "Active" assignments as defined by below, also only look at
-- assignments that are assigned to a valid person as of the report date.
p_report_date between asg.effective_start_date and asg.effective_end_date
AND asg.assignment_status_type_id in
(select ast.assignment_status_type_id
from PER_ASSIGNMENT_STATUS_TYPES ast
where ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
AND asg.assignment_type <> 'B'
AND asg.position_id IS NOT NULL
AND ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) like p_agency
ORDER BY assignment_id;
SELECT full_name name ,national_identifier ssn,last_name,first_name,middle_names, title
FROM per_all_people_f
WHERE person_id=p_person_id;
SELECT SUBSTR(user_table_name,1,4) user_table_name
FROM pay_user_tables
WHERE user_table_id = p_user_table_id;
SELECT 1
FROM ghr_pay_plans ppl
WHERE ppl.pay_plan = p_pay_plan
AND ppl.equivalent_pay_plan = 'GS';
insert_row;