[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(gph1.pa_history_id) pa_history_id
from ghr_pa_history gph1,
PER_PEOPLE_EXTRA_INFO pei1
where gph1.table_name = 'PER_PEOPLE_EXTRA_INFO'
and pei1.person_id = p_person_id
and pei1.information_type = p_information_type
and gph1.information1 = to_char(pei1.person_extra_info_id)
and gph1.effective_date = ( select max(gph2.effective_date)maxdate
from ghr_pa_history gph2
where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO'
and gph2.effective_date <= p_effective_date
and gph2.information1 = to_char(pei1.person_extra_info_id)) ;
select max(par1.pa_notification_id) notification_id
from ghr_pa_requests par1
where par1.person_id = p_person_id
and par1.pa_notification_id is NOT Null
and par1.noa_family_code NOT in ('CORRECT', 'CANCEL')
and nvl(par1.first_noa_cancel_or_correct, 'normal') <> 'CANCEL'
and par1.effective_date = ( select max(par2.effective_date) maxdate
from ghr_pa_requests par2
where par2.person_id = p_person_id
and par2.pa_notification_id is NOT Null
and par2.effective_date <= p_effective_date
and par2.noa_family_code NOT in ('CORRECT', 'CANCEL')
and nvl(par2.first_noa_cancel_or_correct, 'normal') <> 'CANCEL' ) ;*/
select max(par1.pa_notification_id) notification_id
from ghr_pa_requests par1
where par1.person_id = p_person_id
and par1.pa_notification_id is NOT Null
and par1.noa_family_code NOT in ('CANCEL')
and nvl(par1.altered_pa_request_id,par1.pa_request_id) not in
( select par2.altered_pa_request_id from ghr_pa_requests par2
where par2.altered_pa_request_id = nvl(par1.altered_pa_request_id,par1.pa_request_id)
and par2.pa_notification_id is not null
and par2.first_noa_code = '001')
and par1.effective_date =
(select max(effective_date) from ghr_pa_requests par3
where par3.pa_notification_id is not null
and par3.person_id=p_person_id
and par3.effective_date <= p_effective_date
and par3.first_noa_code <> '001'
and nvl(par3.altered_pa_request_id,par3.pa_request_id) not in
(select par4.altered_pa_request_id from ghr_pa_requests par4
where par4.altered_pa_request_id = nvl(par3.altered_pa_request_id,par3.pa_request_id)
and par4.pa_notification_id is not null
and par4.first_noa_code = '001'));
select pa_request_id
from ghr_pa_requests g
where g.pa_notification_id = c_notification_id ;
select flx.id_flex_num id_flex_num
from fnd_id_flex_structures_tl flx
where flx.id_flex_code = 'PEA' and
flx.application_id = 800 and
flx.id_flex_structure_name = 'US Fed Perf Appraisal' and
flx.language = 'US' ;
select pan.person_analysis_id, pea.segment2 rating_of_record
from per_analysis_criteria pea,
per_person_analyses pan
where pan.person_id = p_person_id and
pan.id_flex_num = g_perf_flex_num and
pea.id_flex_num = pan.id_flex_num and
nvl(pan.date_from,sysdate) between nvl(pea.start_date_active,nvl(pan.date_from,sysdate) )
and nvl(pea.end_date_active,nvl(pan.date_from,sysdate) ) and
pan.analysis_criteria_id = pea.analysis_criteria_id and
trunc(nvl(pan.date_from,sysdate)) = (select max(trunc(nvl(pan.date_from,sysdate))) max_date_from
from per_person_analyses pan
where pan.person_id = p_person_id and
pan.id_flex_num = g_perf_flex_num and
trunc(nvl(pan.date_from,sysdate)) <= p_effective_date )
order by person_analysis_id desc ;
select elt.input_currency_code input_curr_code
from pay_element_types_f elt,
pay_element_entries_f ele
where
trunc(p_effective_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(p_effective_date) between ele.effective_start_date
and ele.effective_end_date
and ele.assignment_id = p_assignment_id
and elt.element_type_id = ele.element_type_id
and upper(elt.element_name) = upper(l_new_element_name)
and (elt.business_group_id is null or elt.business_group_id = p_business_group_id) ;
SELECT eef.element_entry_id
FROM pay_element_entries_f eef,
pay_element_types_f elt
WHERE eef.assignment_id = l_assignment_id
AND eef.effective_start_date <= l_effective_date
AND elt.element_type_id = eef.element_type_id
AND eef.effective_start_date BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND UPPER(pqp_fedhr_uspay_int_utils.return_old_element_name
(elt.element_name,
elt.business_group_id,
eef.effective_start_date)) = 'FEDERAL AWARDS' ;
SELECT eef.element_entry_id
FROM pay_element_entries_f eef,
pay_element_types_f elt
WHERE eef.assignment_id = l_assignment_id
AND eef.effective_start_date <= l_effective_date
AND elt.element_type_id = eef.element_type_id
AND eef.effective_start_date BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND UPPER(pqp_fedhr_uspay_int_utils.return_old_element_name
(elt.element_name,
elt.business_group_id,
eef.effective_start_date)) IN ('RELOCATION BONUS',
'RECRUITMENT BONUS' );
select nvl(substr((select ghr_ss_views_pkg.get_people_ei_id_ason_date(p_person_id,p_information_type,p_effective_date) from dual), 0, 10),-1)
into l_history_id
from dual;
select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
from ghr_pa_history gph1,
PER_ASSIGNMENT_EXTRA_INFO pei1
where gph1.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
and pei1.assignment_id = p_asg_id
and pei1.information_type = p_information_type
and gph1.information1 = to_char(pei1.assignment_extra_info_id)
and gph1.effective_date = ( select max(gph2.effective_date)maxdate
from ghr_pa_history gph2
where gph2.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
and gph2.effective_date <= p_effective_date
and gph2.information1 = to_char(pei1.assignment_extra_info_id)) ;
select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
from ghr_pa_history gph1,
PER_POSITION_EXTRA_INFO pei1
where gph1.table_name = 'PER_POSITION_EXTRA_INFO'
and pei1.position_id = p_position_id
and pei1.information_type = p_information_type
and gph1.information1 = to_char(pei1.position_extra_info_id)
and gph1.effective_date = ( select max(gph2.effective_date)maxdate
from ghr_pa_history gph2
where gph2.table_name = 'PER_POSITION_EXTRA_INFO'
and gph2.effective_date <= p_effective_date
and gph2.information1 = to_char(pei1.position_extra_info_id)) ;
select flx.id_flex_num id_flex_num
from fnd_id_flex_structures_tl flx
where flx.id_flex_code = 'PEA' and flx.application_id = 800 and
flx.id_flex_structure_name = 'US Fed Perf Appraisal' and
flx.language = 'US';
SELECT pan.person_analysis_id, pea.segment5 rating_of_record
FROM per_analysis_criteria pea, per_person_analyses pan
WHERE pan.person_id = p_person_id and
pan.id_flex_num = g_perf_flex_num AND
pea.id_flex_num = pan.id_flex_num AND
nvl(pan.date_from, sysdate) BETWEEN
nvl(pea.start_date_active, nvl(pan.date_from, sysdate)) AND
nvl(pea.end_date_active, nvl(pan.date_from, sysdate)) AND
pan.analysis_criteria_id = pea.analysis_criteria_id AND
trunc(nvl(to_date(pea.segment3,'yyyy/mm/dd hh24:mi:ss'),nvl(pan.date_from, sysdate)))
= (SELECT max(trunc(nvl( to_date(pea.segment3,'yyyy/mm/dd hh24:mi:ss'),nvl(pan.date_from, sysdate)))) max_eff_date
FROM per_analysis_criteria pea,
per_person_analyses pan
WHERE pan.person_id = p_person_id AND
pan.id_flex_num = g_perf_flex_num AND
pea.id_flex_num = pan.id_flex_num AND
pan.analysis_criteria_id = pea.analysis_criteria_id AND
trunc(nvl(to_date(pea.segment3,'yyyy/mm/dd hh24:mi:ss'),nvl(pan.date_from, sysdate))) <= sysdate)
ORDER BY person_analysis_id DESC;
SELECT MIN(effective_start_date) effective_start_date
FROM per_all_assignments_f
WHERE person_id = p_person_id AND
assignment_id =
(SELECT MAX(assignment_id)
FROM per_all_assignments_f
WHERE person_id = p_person_id AND assignment_type in ('E','C')) AND
assignment_type in ('E','C');
select max(effective_end_date) effective_end_date
from per_all_assignments_f
where person_id = p_person_id and
assignment_type in ('E','C') ;
select
GDSV.DUTY_STATION_CODE,
GDSV.DUTY_STATION_DESC Description, --bug# 9646458
GDSV.DUTY_STATION_ID
from
GHR_DUTY_STATIONS_F GDS, GHR_LOCALITY_PAY_AREAS_F LPA, GHR_DUTY_STATIONS_V GDSV
WHERE GDS.DUTY_STATION_ID = GDSV.DUTY_STATION_ID
AND GDS.LOCALITY_PAY_AREA_ID = LPA.LOCALITY_PAY_AREA_ID
AND p_EFFECTIVE_DATE BETWEEN LPA.EFFECTIVE_START_DATE AND LPA.EFFECTIVE_END_DATE
AND p_EFFECTIVE_DATE BETWEEN GDS.EFFECTIVE_START_DATE AND GDS.EFFECTIVE_END_DATE
AND p_EFFECTIVE_DATE BETWEEN NVL(GDSV.EFFECTIVE_START_DATE,p_EFFECTIVE_DATE)
AND NVL(GDSV.EFFECTIVE_END_DATE,p_EFFECTIVE_DATE)
AND GDSV.DUTY_STATION_ID = p_DUTY_STATION_ID
ORDER BY GDSV.DUTY_STATION_CODE;
SELECT MAX(assignment_id) assg_id
FROM per_all_assignments_f
WHERE person_id = p_person_id AND assignment_type in ('E','C');
SELECT MIN(effective_start_date) effective_start_date,
MIN(assignment_id) assignment_id
FROM per_all_assignments_f
WHERE person_id = p_person_id AND
assignment_id =
(SELECT MAX(assignment_id)
FROM per_all_assignments_f
WHERE person_id = p_person_id AND assignment_type in ('E','C'))
AND assignment_type in ('E','C');
select elt.input_currency_code input_curr_code
from pay_element_types_f elt,
pay_element_entries_f ele
where
trunc(p_effective_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(p_effective_date) between ele.effective_start_date
and ele.effective_end_date
and ele.assignment_id = p_assignment_id
and elt.element_type_id = ele.element_type_id
and upper(elt.element_name) = upper(l_new_element_name)
and (elt.business_group_id is null or elt.business_group_id = p_business_group_id) ;