[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
ast.per_system_status assignment_status_type,
ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) agency_code
FROM PER_ALL_ASSIGNMENTS_F asg, PER_ASSIGNMENT_STATUS_TYPES ast
WHERE ast.assignment_status_type_id = asg.assignment_status_type_id
AND p_report_date > asg.effective_start_date
AND TO_CHAR(asg.effective_end_date,'YYYY') >= to_char(p_report_date,'YYYY')
AND 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_code||NVL(p_agency_sub_code,'%')
AND decode(hr_general.get_xbg_profile,'Y',asg.business_group_id , p_business_group) = asg.business_group_id
AND effective_start_date = (select max(effective_start_date) from PER_ALL_ASSIGNMENTS_F asg1, PER_ASSIGNMENT_STATUS_TYPES ast1
WHERE asg1.assignment_id = asg.assignment_id
AND ast1.assignment_status_type_id = asg1.assignment_status_type_id
AND p_report_date > asg1.effective_start_date
AND TO_CHAR(asg1.effective_end_date,'YYYY') >= to_char(p_report_date,'YYYY')
AND ast1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND asg1.assignment_type <> 'B'
AND asg1.position_id IS NOT NULL
AND ghr_api.get_position_agency_code_pos(asg1.position_id,asg1.business_group_id) like p_agency_code||NVL(p_agency_sub_code,'%'))
ORDER BY assignment_id;
insert_row;
SELECT pap.sex,
pap.date_of_birth,
pap.full_name,
pap.employee_number
FROM per_all_people pap
WHERE p_person_id = pap.person_id
AND p_report_date between pap.effective_start_date AND pap.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 = p_grade_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_all_people 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
P_REPORT_DATE BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE 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)));
DELETE FROM ghr_cpdf_temp
WHERE report_type = p_type
AND session_id = userenv('SESSIONID');
PROCEDURE Insert_Row IS
l_proc varchar2(30) := 'insert_row';
INSERT INTO ghr_cpdf_temp(report_type
,session_id
,agency_code
,bargaining_unit_status
,education_level
,effective_date
,employee_date_of_birth
,handicap_code
,pay_status
,race_national_origin
,service_comp_date
,sex
,supervisory_status
,tenure
,to_basic_pay
,to_grade_or_level
,to_occ_code
,to_pay_basis
,to_pay_plan
,to_step_or_rate
,to_total_salary
,from_national_identifier
,ehri_employee_id
,appoint_type_code
,to_adj_basic_pay
,appropriation_code
,race_ethnic_info
,cont_pay_type_code)
values (
'EEOCSTATUS'
,userenv('SESSIONID')
,g_temp_rec.agency_code
,g_temp_rec.bargaining_unit_status
,g_temp_rec.education_level
,g_temp_rec.effective_date
,g_temp_rec.employee_date_of_birth
,g_temp_rec.handicap_code
,g_temp_rec.pay_status
,g_temp_rec.race_national_origin
,g_temp_rec.service_comp_date
,g_temp_rec.sex
,g_temp_rec.supervisory_status
,g_temp_rec.tenure
,g_temp_rec.to_basic_pay
,g_temp_rec.to_grade_or_level
,g_temp_rec.to_occ_code
,g_temp_rec.to_pay_basis
,g_temp_rec.to_pay_plan
,g_temp_rec.to_step_or_rate
,g_temp_rec.to_total_salary
,g_temp_rec.from_national_identifier
,g_temp_rec.ehri_employee_id
,g_temp_rec.appoint_type_code
,g_temp_rec.to_adj_basic_pay
,g_temp_rec.appropriation_code
,g_temp_rec.race_ethnic_info
,g_temp_rec.cont_pay_type_code);
l_log_text := 'Unhandled Error under procedure insert_row'||
'; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
END insert_row;
SELECT *
FROM GHR_CPDF_TEMP
WHERE SESSION_ID = c_session_id
AND REPORT_TYPE = 'EEOCSTATUS';
SELECT value
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT 1
FROM GHR_PAY_PLANS
WHERE PAY_PLAN = p_eeoc_rec.to_pay_plan
AND EQUIVALENT_PAY_PLAN = 'GS';
SELECT TO_CHAR(TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE),p_eeoc_rec.employee_date_of_birth)/12)) age
FROM DUAL;
INSERT INTO fnd_sessions
(session_id
,effective_date)
VALUES
(userenv('sessionid')
,l_report_date);
DELETE FROM fnd_sessions
WHERE session_id = userenv('sessionid');