The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pps.status from hr_all_positions_f pps -- Venkat - Position DT
where pps.position_id = p_position_id
and p_pa_request_rec.effective_date between pps.effective_start_date
and pps.effective_end_date;
select ppt.system_person_type, ppt.user_person_type from PER_PERSON_TYPES ppt, PER_PEOPLE_F ppf
where ppf.person_id = p_person_id
and trunc(p_eff_date) between ppf.effective_start_date and ppf.effective_end_date
and ppt.person_type_id = ppf.person_type_id;
select asg1.assignment_id
from per_assignments_f asg1
where
asg1.person_id <> p_person_id
and asg1.position_id = p_to_position_id
and asg1.assignment_type NOT IN ('B','A')
and (asg1.effective_start_date >= p_eff_date
or p_eff_date
between asg1.effective_start_date and asg1.effective_end_date);
select asg1.assignment_id
from per_assignments_f asg1
where asg1.person_id <> p_person_id
and asg1.position_id = p_to_position_id
and asg1.assignment_type NOT IN ('B','A')
and (p_eff_date
between asg1.effective_start_date and asg1.effective_end_date);
Select per_system_status from per_assignment_status_types pst, per_assignments_f paf
where paf.assignment_status_type_id = pst.assignment_status_type_id
and paf.assignment_type <> 'B'
and paf.assignment_id = p_asg_id
and pst.per_system_status = p_status
and trunc(p_eff_date) between paf.effective_start_date and paf.effective_end_date;
select 1
from ghr_noac_las nla
where nla.nature_of_action_id = p_pa_request_rec.first_noa_id
and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.first_action_la_code1
and nla.valid_first_lac_flag = 'Y'
and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
select 1
from ghr_noac_las nla
where nla.nature_of_action_id = p_pa_request_rec.first_noa_id
and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.first_action_la_code2
and nla.valid_second_lac_flag = 'Y'
and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
select 1
from ghr_noac_las nla
where nla.nature_of_action_id = p_pa_request_rec.second_noa_id
and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.second_action_la_code1
and nla.valid_first_lac_flag = 'Y'
and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
select 1
from ghr_noac_las nla
where nla.nature_of_action_id = p_pa_request_rec.second_noa_id
and SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.second_action_la_code2
and nla.valid_second_lac_flag = 'Y'
and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
and nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));
SELECT rei_information3 temp_step
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
Select fam.noa_family_code
from ghr_noa_families nfa,
ghr_families fam
where nfa.nature_of_action_id = p_pa_request_rec.first_noa_id
and nfa.noa_family_code = fam.noa_family_code
and fam.update_hr_flag = 'Y'
and l_effective_date between NVL(nfa.start_date_active,l_effective_date)
and NVL(nfa.end_date_active,l_effective_date);
select eev.screen_entry_value
from pay_element_types_f elt,
pay_input_values_f ipv,
pay_element_entries_f ele,
pay_element_entry_values_f eev
where trunc(p_pa_request_rec.effective_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(p_pa_request_rec.effective_date) between ipv.effective_start_date
and ipv.effective_end_date
and trunc(p_pa_request_rec.effective_date) between ele.effective_start_date
and ele.effective_end_date
and trunc(p_pa_request_rec.effective_date) between eev.effective_start_date
and eev.effective_end_date
and elt.element_type_id = ipv.element_type_id
and ele.assignment_id = p_pa_request_rec.employee_assignment_id
and elt.element_name IN ('Supervisory Differential','AUO','Availability Pay')
and ipv.input_value_id = eev.input_value_id
and ele.element_entry_id + 0 = eev.element_entry_id ;
SELECT par.pa_request_id futr_rpa
FROM ghr_pa_routing_history prh
,ghr_pa_requests par
WHERE prh.pa_request_id = par.pa_request_id
AND par.person_id = c_person_id
AND par.effective_date > c_effective_date
AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
FROM ghr_pa_routing_history prh2
WHERE prh2.pa_request_id = par.pa_request_id)
AND prh.action_taken IN ('FUTURE_ACTION','UPDATE_HR_COMPLETE')
AND par.NOA_FAMILY_CODE <> 'CANCEL'
AND ( ( par.second_noa_code IS NULL
AND NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
)
OR ( par.second_noa_code IS NOT NULL
AND par.NOA_FAMILY_CODE <> 'CORRECT'
AND ( NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
OR NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
)
)
OR ( par.second_noa_code IS NOT NULL
AND par.NOA_FAMILY_CODE = 'CORRECT'
AND NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
)
);
select 1 from pay_user_tables
where substr(user_table_name,1,4) = 'ESSL'
and user_table_id = l_user_tab_id;
SELECT NVL(rei_information3,0)+NVL(rei_information4,0)+NVL(rei_information5,0)+NVL(rei_information6,0)
+NVL(rei_information7,0)+NVL(rei_information8,0)+NVL(rei_information9,0)+NVL(rei_information10,0) amount,
rei_information12 nte_date,rei_information13 premium_pay_ind
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_MD_DDS_PAY';
SELECT NVL(rei_information3,0) premium_pay_ind
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_PREMIUM_PAY';
SELECT NVL(rei_information3,0) premium_pay_ind
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_PREMIUM_PAY_IND';
SELECT from_occ_code
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_rec.pa_request_id;
SELECT rei_information3 ,
rei_information4 ,
rei_information5 --bug# 11781477
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_DET_INFO';
SELECT user_status
FROM per_assignment_status_types pst, per_assignments_f paf
WHERE paf.assignment_status_type_id = pst.assignment_status_type_id
AND paf.assignment_type <> 'B'
AND paf.assignment_id = p_asg_id
AND user_status ='Detail NTE'
and paf.effective_end_date = to_date('31/12/4712','DD/MM/RRRR');
SELECT first_noa_information1
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_rec.pa_request_id;
SELECT second_noa_information1
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_rec.pa_request_id;
select assignment_extra_info_id
from per_assignment_extra_info
where assignment_id = p_pa_request_rec.employee_assignment_id
and information_type='GHR_US_ASG_DET_INFO'
and g_effective_date
between fnd_date.canonical_to_date(AEI_INFORMATION1)
and fnd_date.canonical_to_date(AEI_INFORMATION2);
SELECT HR_GENERAL.DECODE_AVAILABILITY_STATUS(hap.availability_status_id) status
FROM hr_all_positions_f hap
WHERE g_effective_date BETWEEN hap.effective_start_date
AND hap.effective_end_date
AND hap.position_id=p_position_id;
SELECT rei_information8 repay_sch,
rei_information9 review_Date
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_STUDENT_LOAN';
select ele.effective_end_date
from pay_element_types_f elt,
pay_element_links_f ell,
pay_element_entries_f ele
where p_pa_request_rec.effective_date between elt.effective_start_date and elt.effective_end_date
and p_pa_request_rec.effective_date between ell.effective_start_date and ell.effective_end_date
and p_pa_request_rec.effective_date between ele.effective_start_date and ele.effective_end_date
and elt.element_type_id = ell.element_type_id
and ell.element_link_id = ele.element_link_id
and ele.assignment_id = p_asg_id
and elt.element_name = p_ele_name;
SELECT effective_end_date
FROM pay_element_entries_f
WHERE element_type_id = (SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_ele_name
and p_pa_request_rec.effective_date between effective_start_Date and effective_end_date )
and assignment_id = p_asg_id;
SELECT gdf.segment1 pay_plan
,gdf.segment2 grade_or_level
FROM per_grade_definitions gdf
,per_grades grd
WHERE grd.grade_id = p_grade_id
AND grd.grade_definition_id = gdf.grade_definition_id;
Select business_group_id bg
from per_assignments_f
where assignment_id = p_assignment_id
and p_eff_date between effective_start_date
and effective_end_date;
SELECT user_status
FROM per_assignment_status_types pst, per_assignments_f paf
WHERE paf.assignment_status_type_id = pst.assignment_status_type_id
AND paf.assignment_type <> 'B'
AND paf.assignment_id = p_asg_id
AND (p_eff_date) BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT pa_incentive_category_end_date,pa_incentive_category_percent
FROM ghr_pa_incentives
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND pa_incentive_category = 'Biweekly';
SELECT count(*) cnt
FROM ghr_pa_incentives
WHERE pa_request_id = p_pa_request_rec.pa_request_id;
SELECT count(*) cnt
FROM pay_element_entries_f ee, pay_element_types_f et
WHERE ee.assignment_id = l_asg_id
AND ee.element_type_id = et.element_type_id
AND et.element_name like '%Incentive%'
AND ee.effective_start_date > l_effective_date;
SELECT 1
FROM ghr_noa_families noa
WHERE noa.nature_of_action_id = l_noa_id
AND noa.noa_family_code = l_noa_fam_code
AND l_eff_date BETWEEN nvl(noa.start_date_active,l_eff_date)
AND nvl(noa.end_date_active,l_eff_date)
AND noa.enabled_flag = 'Y';
SELECT rei_information3 srvc_oblg_type,
rei_information4 srvc_oblg_st_date,
rei_information5 srvc_oblg_end_date
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id AND
information_type = 'GHR_US_PAR_SERVICE_OBLIGATION';
SELECT rei_information6 tsp_scd
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_SCD_INFO';
SELECT rei_information7 tsp_scd
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_CHG_RETIRE_PLAN';
SELECT rei_information3
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = 'GHR_US_PAR_BENEFITS';
Select asg.effective_start_date
from per_all_assignments_f asg
Where asg.assignment_id = p_pa_request_rec.employee_assignment_id
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'B'
order by asg.effective_start_date;
/* Do not allow update to HR if Position_Status is Invalid */
FOR v_get_psn_status IN
c_get_psn_status(p_pa_request_rec.to_position_id) LOOP
l_psn_status := v_get_psn_status.status;
ghr_validate_perwsdpo.update_posn_status(p_pa_request_rec.to_position_id,p_pa_request_rec.effective_date);
hr_utility.set_message(8301,'GHR_38693_NO_UPDATE_TO_POS');
hr_utility.set_message(8301,'GHR_ONE_OP_UPDATE_ONLY');
/**** As per GPPA update 46 req. for 890 any employee is fine
IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
p_pa_request_rec.first_noa_code = '890' THEN
IF l_psi = '00' THEN
hr_utility.set_message(8301, 'GHR_38462_AFHR_POS_SEL');
SELECT eev.screen_entry_value
,eev.effective_start_date
FROM pay_element_types_f elt
,pay_input_values_f ipv
,pay_element_entries_f ele
,pay_element_entry_values_f eev
WHERE p_effective_date < eev.effective_start_date
AND eev.effective_end_date IS NULL
AND elt.element_type_id = ipv.element_type_id
AND upper(elt.element_name)= upper(p_element_name)
AND ipv.input_value_id = eev.input_value_id
AND ele.assignment_id = p_assignment_id
AND ele.element_entry_id+0 = eev.element_entry_id
AND upper(ipv.name ) = upper(p_input_value_name)
-- AND NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
AND (elt.business_group_id is NULL or elt.business_group_id = p_bg_id);
Select distinct business_group_id bg
from per_assignments_f
where assignment_id = p_assignment_id
and p_eff_date between effective_start_date
and effective_end_date;