[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
FAM.NAME ACTION_REQUESTED
,PA_REQUEST_ID
,REQUEST_NUMBER
,ADDITIONAL_INFO_TEL_NUMBER
,DECODE(PROPOSED_EFFECTIVE_ASAP_FLAG,'Y','ASAP',TO_CHAR(PROPOSED_EFFECTIVE_DATE,'MM-DD-YYYY')) PROPOSED_EFFECTIVE_DATE
,REQUESTED_BY_TITLE ACTION_REQUESTED_BY_TITLE
,REQUESTED_DATE ACTION_REQUESTED_DATE
,AUTHORIZED_BY_TITLE
,CONCURRENCE_DATE
,EMPLOYEE_LAST_NAME E_LAST_NAME
,EMPLOYEE_FIRST_NAME
,NVL(EMPLOYEE_MIDDLE_NAMES, '') EMPLOYEE_MIDDLE_NAME
,EMPLOYEE_NATIONAL_IDENTIFIER SS_NUMBER
,EMPLOYEE_DATE_OF_BIRTH
,NVL(GHR_UPD_HR_VALIDATION.GET_EXEMP_AWARD_DATE(PA_REQUEST_ID),EFFECTIVE_DATE) EFFECTIVE_DATE
,DECODE(LENGTH(NVL(FIRST_NOA_CODE, '')), 4, SUBSTR(FIRST_NOA_CODE, 2, 3), FIRST_NOA_CODE) FIRST_NOA_CODE
,GHR_PA_REQUESTS.NOA_FAMILY_CODE
,FIRST_NOA_DESC
,FIRST_ACTION_LA_CODE1
,FIRST_ACTION_LA_CODE2
,FIRST_ACTION_LA_DESC1
,FIRST_ACTION_LA_DESC2
,FROM_POSITION_ID
,FROM_POSITION_TITLE
,FROM_POSITION_NUMBER || DECODE(FROM_POSITION_NUMBER, NULL, NULL, ' - ')|| TO_CHAR(FROM_POSITION_SEQ_NO) FROM_POSITION_NUMBER
,FROM_PAY_PLAN
,FROM_OCC_CODE
,FROM_GRADE_OR_LEVEL
,FROM_STEP_OR_RATE
,FROM_TOTAL_SALARY
,FROM_PAY_BASIS
,FROM_BASIC_PAY
,FROM_LOCALITY_ADJ
,FROM_ADJ_BASIC_PAY
,FROM_OTHER_PAY_AMOUNT
,FROM_POSITION_ORG_LINE1
,FROM_POSITION_ORG_LINE2
,FROM_POSITION_ORG_LINE3
,FROM_POSITION_ORG_LINE4
,FROM_POSITION_ORG_LINE5
,FROM_POSITION_ORG_LINE6
,SECOND_ACTION_LA_CODE1
,SECOND_ACTION_LA_CODE2
,SECOND_ACTION_LA_DESC1
,SECOND_ACTION_LA_DESC2
,SECOND_NOA_ID
,DECODE(LENGTH(NVL(SECOND_NOA_CODE, '')), 4, SUBSTR(SECOND_NOA_CODE, 2, 3), SECOND_NOA_CODE) SECOND_NOA_CODE
,SECOND_NOA_DESC
,TO_POSITION_TITLE
,TO_POSITION_NUMBER || DECODE(TO_POSITION_NUMBER, NULL, NULL, ' - ')|| TO_CHAR(TO_POSITION_SEQ_NO) TO_POSITION_NUMBER
,TO_PAY_PLAN
,TO_OCC_CODE
,TO_GRADE_OR_LEVEL
,TO_STEP_OR_RATE
,TO_TOTAL_SALARY
,TO_AVAILABILITY_PAY
,TO_AUO_PREMIUM_PAY_INDICATOR
,TO_AU_OVERTIME
,AWARD_AMOUNT
,AWARD_UOM
,TO_PAY_BASIS
,TO_BASIC_PAY
,TO_LOCALITY_ADJ
,TO_ADJ_BASIC_PAY
,TO_OTHER_PAY_AMOUNT
,TO_POSITION_ORG_LINE1
,TO_POSITION_ORG_LINE2
,TO_POSITION_ORG_LINE3
,TO_POSITION_ORG_LINE4
,TO_POSITION_ORG_LINE5
,TO_POSITION_ORG_LINE6
,TO_POSITION_ID
,VETERANS_PREFERENCE
,TENURE
--,AGENCY_USE
,VETERANS_PREF_FOR_RIF
,FEGLI
,ANNUITANT_INDICATOR
,PAY_RATE_DETERMINANT
,RETIREMENT_PLAN
,SERVICE_COMP_DATE
,WORK_SCHEDULE
,PART_TIME_HOURS
,POSITION_OCCUPIED
,FLSA_CATEGORY
,APPROPRIATION_CODE1 || ' | ' || APPROPRIATION_CODE2 APPROPRIATION_CODE
,BARGAINING_UNIT_STATUS
,DUTY_STATION_ID
,DUTY_STATION_CODE
,DUTY_STATION_DESC
,EDUCATION_LEVEL
,YEAR_DEGREE_ATTAINED
,ACADEMIC_DISCIPLINE
,FUNCTIONAL_CLASS
,CITIZENSHIP
,VETERANS_STATUS
,SUPERVISORY_STATUS
,REQUESTING_OFFICE_REMARKS_FLAG REQUESTING_OFFICE_REMARKS_FLAG
,REQUESTING_OFFICE_REMARKS_DESC REQUESTING_OFFICE_REMARKS_D
,RESIGN_AND_RETIRE_REASON_DESC
,EFFECTIVE_DATE RETIRE_EFFECTIVE_DATE
,FORWARDING_ADDRESS_LINE1 || ' '
|| FORWARDING_ADDRESS_LINE2 || ' '
|| FORWARDING_ADDRESS_LINE3 FORWARDING_ADDRESS1
,FORWARDING_TOWN_OR_CITY || ' ' || FORWARDING_REGION_2 || ' ' || FORWARDING_POSTAL_CODE FORWARDING_CITY
,NULL A
,PERSON_ID
,EMPLOYEE_ASSIGNMENT_ID
,ADDITIONAL_INFO_PERSON_ID
,REQUESTED_BY_PERSON_ID
,AUTHORIZED_BY_PERSON_ID
,APPROVING_OFFICIAL_FULL_NAME
,APPROVING_OFFICIAL_WORK_TITLE
,APPROVAL_DATE
,NOTEPAD
,ALTERED_PA_REQUEST_ID
FROM GHR_PA_REQUESTS, GHR_FAMILIES FAM
WHERE
PA_REQUEST_ID = c_pa_request_id
AND GHR_PA_REQUESTS.NOA_FAMILY_CODE = FAM.NOA_FAMILY_CODE;
SELECT
pre.description,
pre.pa_request_id,
remk.code remark_code
FROM
ghr_pa_remarks pre,
ghr_remarks remk
WHERE remk.remark_id = pre.remark_id
AND pre.pa_request_id = c_pa_request_id;
SELECT per1.first_name||' '|| DECODE(per1.middle_names,NULL,'NMN',SUBSTR(per1.middle_names,1,1)||'. ') || per1.last_name person_name
from per_all_people_f per1
where per1.person_id = p_person_id
and NVL(p_effective_date,TRUNC(sysdate)) between per1.effective_start_date and per1.effective_end_date;
SELECT rei_information3
,rei_information4
,rei_information5
,rei_information6
,rei_information7
,rei_information8
FROM ghr_pa_request_extra_info
WHERE information_type = 'GHR_US_PAR_GEN_AGENCY_DATA'
AND pa_request_id = c_pa_request_id;
SELECT COUNT(*) cnt
FROM GHR_PA_ROUTING_HISTORY PRH,
PER_PEOPLE_F PER,
PER_ASSIGNMENTS_F ASG,
PER_POSITION_EXTRA_INFO PG1
WHERE PRH.pa_request_id = p_pa_request_id
AND (PRH.personnelist_flag = 'Y' OR PRH.approver_flag = 'Y')
AND PRH.user_name_employee_id = PER.person_id
AND PRH.LAST_UPDATE_DATE = (SELECT MAX(P.last_update_date)
FROM GHR_PA_ROUTING_HISTORY P
WHERE P.pa_request_id = PRH.pa_request_id
AND (P.personnelist_flag = 'Y' OR P.approver_flag = 'Y')
AND P.user_name_employee_id = PRH.user_name_employee_id)
AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN PER.effective_start_date AND PER.effective_end_date
AND PER.person_id = ASG.person_id (+)
AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN ASG.effective_start_date AND ASG.effective_end_date
AND ASG.primary_flag (+) = 'Y'
AND ASG.position_id = PG1.position_id (+)
AND PG1.information_type (+) = 'GHR_US_POS_GRP1';
SELECT PG1.POEI_INFORMATION4 Office_Symbol,
PER.LAST_NAME || ', ' || PER.FIRST_NAME || ' ' || PER.MIDDLE_NAMES FULL_NAME,
TO_CHAR(PRH.LAST_UPDATE_DATE, 'MM-DD-YYYY') LAST_UPDATE_DATE
FROM GHR_PA_ROUTING_HISTORY PRH,
PER_PEOPLE_F PER,
PER_ASSIGNMENTS_F ASG,
PER_POSITION_EXTRA_INFO PG1
WHERE PRH.pa_request_id = p_pa_request_id
AND (PRH.personnelist_flag = 'Y' OR PRH.approver_flag = 'Y')
AND PRH.user_name_employee_id = PER.person_id
AND PRH.LAST_UPDATE_DATE = (SELECT MAX(P.last_update_date)
FROM GHR_PA_ROUTING_HISTORY P
WHERE P.pa_request_id = PRH.pa_request_id
AND (P.personnelist_flag = 'Y' OR P.approver_flag = 'Y')
AND P.user_name_employee_id = PRH.user_name_employee_id)
AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN PER.effective_start_date AND PER.effective_end_date
AND PER.person_id = ASG.person_id (+)
AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN ASG.effective_start_date AND ASG.effective_end_date
AND ASG.primary_flag (+) = 'Y'
AND ASG.position_id = PG1.position_id (+)
AND PG1.information_type (+) = 'GHR_US_POS_GRP1'
ORDER BY PRH.LAST_UPDATE_DATE DESC;
l_rpa_misc_fields.DELETE;
l_rpa_report_tags.DELETE;
l_signature_rec(1).office_date := r_signatures.last_update_date;
l_signature_rec(2).office_date := r_signatures.last_update_date;
l_signature_rec(3).office_date := r_signatures.last_update_date;
l_signature_rec(4).office_date := r_signatures.last_update_date;
l_signature_rec(5).office_date := r_signatures.last_update_date;
l_signature_rec(6).office_date := r_signatures.last_update_date;
l_rpa_report_tags.DELETE;
SELECT ppi.ppi_percentage
FROM ghr_premium_pay_indicators ppi
WHERE code = p_ppi_code;
SELECT rei_information11 amount
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND information_type='GHR_US_PAR_MD_DDS_PAY';
SELECT
PERSON_ID,
EMPLOYEE_LAST_NAME ,
EMPLOYEE_FIRST_NAME ,
NVL(EMPLOYEE_MIDDLE_NAMES, 'NMN') EMPLOYEE_MIDDLE_NAME
,EMPLOYEE_NATIONAL_IDENTIFIER SS_NUMBER
,EMPLOYEE_DATE_OF_BIRTH
,EMPLOYEE_ASSIGNMENT_ID
,NVL(GHR_UPD_HR_VALIDATION.GET_EXEMP_AWARD_DATE(PA_REQUEST_ID),EFFECTIVE_DATE) EFFECTIVE_DATE
,NOA_FAMILY_CODE
,DECODE(LENGTH(NVL(FIRST_NOA_CODE, '')), 4, SUBSTR(FIRST_NOA_CODE, -3), FIRST_NOA_CODE) FIRST_NOA_CODE
,FIRST_NOA_DESC
,FIRST_ACTION_LA_CODE1
,FIRST_ACTION_LA_CODE2
,FIRST_ACTION_LA_DESC1
,FIRST_ACTION_LA_DESC2
,SECOND_ACTION_LA_CODE1
,SECOND_ACTION_LA_CODE2
,SECOND_ACTION_LA_DESC1
,SECOND_ACTION_LA_DESC2
,DECODE(LENGTH(NVL(SECOND_NOA_CODE, '')), 4, SUBSTR(SECOND_NOA_CODE, -3), SECOND_NOA_CODE) SECOND_NOA_CODE
,SECOND_NOA_DESC
,FROM_POSITION_ID
,FROM_POSITION_NUMBER || DECODE(FROM_POSITION_NUMBER, NULL, NULL, ' - ')|| TO_CHAR(FROM_POSITION_SEQ_NO) FROM_POSITION_NUMBER
,FROM_POSITION_TITLE
,FROM_PAY_PLAN
,FROM_OCC_CODE
,FROM_GRADE_OR_LEVEL
,FROM_STEP_OR_RATE
,FROM_TOTAL_SALARY
,FROM_PAY_BASIS
,TO_NUMBER(FROM_BASIC_PAY) FROM_BASIC_PAY
,FROM_LOCALITY_ADJ
,FROM_ADJ_BASIC_PAY
,FROM_OTHER_PAY_AMOUNT
,FROM_POSITION_ORG_LINE1
,FROM_POSITION_ORG_LINE2
,FROM_POSITION_ORG_LINE3
,FROM_POSITION_ORG_LINE4
,FROM_POSITION_ORG_LINE5
,FROM_POSITION_ORG_LINE6
,FROM_AGENCY_CODE
,FROM_AGENCY_DESC
,TO_POSITION_ID
,TO_POSITION_TITLE
,TO_POSITION_NUMBER || DECODE(TO_POSITION_NUMBER, NULL, NULL, ' - ')|| TO_CHAR(TO_POSITION_SEQ_NO) TO_POSITION_NUMBER
,TO_PAY_PLAN
,TO_OCC_CODE
,TO_GRADE_OR_LEVEL
,TO_STEP_OR_RATE
,TO_TOTAL_SALARY
,TO_AUO_PREMIUM_PAY_INDICATOR
,TO_AU_OVERTIME
,TO_AVAILABILITY_PAY
,AWARD_AMOUNT
,AWARD_UOM
,TO_PAY_BASIS
,TO_BASIC_PAY
,TO_LOCALITY_ADJ
,TO_ADJ_BASIC_PAY
,TO_OTHER_PAY_AMOUNT
,TO_POSITION_ORG_LINE1
,TO_POSITION_ORG_LINE2
,TO_POSITION_ORG_LINE3
,TO_POSITION_ORG_LINE4
,TO_POSITION_ORG_LINE5
,TO_POSITION_ORG_LINE6
,VETERANS_PREFERENCE
,TENURE
,VETERANS_PREF_FOR_RIF
,FEGLI
,FEGLI_DESC
,ANNUITANT_INDICATOR
,ANNUITANT_INDICATOR_DESC
,PAY_RATE_DETERMINANT
,RETIREMENT_PLAN
,RETIREMENT_PLAN_DESC
,SERVICE_COMP_DATE
,WORK_SCHEDULE
,WORK_SCHEDULE_DESC
,PART_TIME_HOURS
,POSITION_OCCUPIED
,FLSA_CATEGORY
,APPROPRIATION_CODE1 || ' | ' || APPROPRIATION_CODE2 APPROPRIATION_CODE
,BARGAINING_UNIT_STATUS
,DUTY_STATION_CODE
,DUTY_STATION_DESC
,EMPLOYEE_DEPT_OR_AGENCY
,AGENCY_CODE
,PERSONNEL_OFFICE_ID
,SF50_APPROVAL_DATE SF50_APPROVAL_DATE
,SF50_APPROVING_OFCL_FULL_NAME
,SF50_APPROVING_OFCL_WORK_TITLE
,APPROVING_OFFICIAL_FULL_NAME
,APPROVING_OFFICIAL_WORK_TITLE
,APPROVAL_DATE APPROVAL_DATE
,PA_REQUEST_ID
,PA_NOTIFICATION_ID
,SECOND_NOA_ID
,ALTERED_PA_REQUEST_ID
FROM GHR_PA_REQUESTS PAR
WHERE PA_REQUEST_ID = c_pa_request_id;
SELECT
pre.description,
pre.pa_request_id,
remk.code remark_code
FROM
ghr_pa_remarks pre,
ghr_remarks remk
WHERE remk.remark_id = pre.remark_id
AND pre.pa_request_id = c_pa_request_id;
SELECT rei_information3
,rei_information4
,rei_information5
,rei_information6
,rei_information7
,rei_information8
FROM ghr_pa_request_extra_info
WHERE information_type = 'GHR_US_PAR_GEN_AGENCY_DATA'
AND pa_request_id = c_pa_request_id;
l_npa_report_tags.DELETE;
l_npa_misc_fields.DELETE;
SELECT ppi.ppi_percentage
FROM ghr_premium_pay_indicators ppi
WHERE code = p_ppi_code;
SELECT rei_information11 amount
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND information_type='GHR_US_PAR_MD_DDS_PAY';
l_npa_report_tags.DELETE;
SELECT FILE_DATA
FROM FND_LOBS
WHERE PROGRAM_NAME = c_program_name;