[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT first_noa_code,second_noa_code
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
SELECT rei_information3 temp_step
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
SELECT pei.person_extra_info_id
-- Bug#4423679 Added date_from,date_to columns.
,fnd_date.canonical_to_date(pei.pei_information1) date_from
,fnd_date.canonical_to_date(pei.pei_information2) date_to
-- Bug#4423679
,pei.pei_information3 retained_grade
,pei.pei_information4 retained_step_or_rate
,pei.pei_information5 retained_pay_plan
,pei.pei_information6 retained_user_table_id
---- ,pei.pei_information7 retained_locality_percent
,pei.pei_information8 retained_pay_basis
,pei.pei_information9 retained_temp_step
FROM per_people_extra_info pei
WHERE pei.person_id = p_person_id
AND pei.information_type = 'GHR_US_RETAINED_GRADE'
AND p_effective_date BETWEEN NVL(fnd_date.canonical_to_date(pei.pei_information1),p_effective_date)
AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date)
AND fnd_date.canonical_to_date(pei.pei_information1) =
(SELECT MIN (NVL(fnd_date.canonical_to_date(pei2.pei_information1),p_effective_date) )
FROM per_people_extra_info pei2
WHERE pei2.person_id = p_person_id
AND pei2.information_type = 'GHR_US_RETAINED_GRADE'
AND p_effective_date
BETWEEN NVL(fnd_date.canonical_to_date(pei2.pei_information1),p_effective_date)
AND NVL(fnd_date.canonical_to_date(pei2.pei_information2),p_effective_date)
AND pei2.person_extra_info_id NOT IN (SELECT rei_information3
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND (rei_information5 is null OR rei_information5 = 'Y')
AND information_type in ('GHR_US_PAR_TERM_RET_GRADE',
'GHR_US_PAR_TERM_RG_PROMO',
'GHR_US_PAR_TERM_RG_POSN_CHG')
)
)
AND pei.person_extra_info_id NOT IN (SELECT rei_information3
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND information_type in ( 'GHR_US_PAR_TERM_RET_GRADE',
'GHR_US_PAR_TERM_RG_PROMO',
'GHR_US_PAR_TERM_RG_POSN_CHG')
AND (rei_information5 is null OR rei_information5 = 'Y'));
-- Bug 3221361 In case if TPS record is deleted, it shd return NULL as the value.
FOR cur_temp_step_rec IN cur_temp_step LOOP
l_retained_grade_rec.temp_step := cur_temp_step_rec.temp_step;
SELECT pei.person_extra_info_id
-- Bug#4423679 Added date_from,date_to columns.
,fnd_date.canonical_to_date(pei.pei_information1) date_from
,fnd_date.canonical_to_date(pei.pei_information2) date_to
-- Bug#4423679
,pei.pei_information3 retained_grade
,pei.pei_information4 retained_step_or_rate
,pei.pei_information5 retained_pay_plan
,pei.pei_information6 retained_user_table_id
---- ,pei.pei_information7 retained_locality_percent
,pei.pei_information8 retained_pay_basis
,pei.pei_information9 retained_temp_step
FROM per_people_extra_info pei
WHERE pei.person_id = p_person_id
AND pei.information_type = 'GHR_US_RETAINED_GRADE'
AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date) < p_effective_date
AND fnd_date.canonical_to_date(pei.pei_information1) =
(SELECT MIN (NVL(fnd_date.canonical_to_date(pei2.pei_information1),p_effective_date) )
FROM per_people_extra_info pei2
WHERE pei2.person_id = p_person_id
AND pei2.information_type = 'GHR_US_RETAINED_GRADE'
AND NVL(fnd_date.canonical_to_date(pei2.pei_information2),p_effective_date) < p_effective_date
AND pei2.person_extra_info_id NOT IN (SELECT rei_information3
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND (rei_information5 is null OR rei_information5 = 'Y')
AND information_type in ('GHR_US_PAR_TERM_RET_GRADE',
'GHR_US_PAR_TERM_RG_PROMO',
'GHR_US_PAR_TERM_RG_POSN_CHG')
)
)
AND pei.person_extra_info_id NOT IN (SELECT rei_information3
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_id
AND information_type in ( 'GHR_US_PAR_TERM_RET_GRADE',
'GHR_US_PAR_TERM_RG_PROMO',
'GHR_US_PAR_TERM_RG_POSN_CHG')
AND (rei_information5 is null OR rei_information5 = 'Y'));
SELECT cin.value basic_pay
,col.user_column_name step_or_rate
,cin.effective_start_date
,cin.effective_end_date
FROM pay_user_column_instances_f cin
,pay_user_rows_f urw
,pay_user_columns col
WHERE col.user_table_id = p_user_table_id
AND urw.user_table_id = p_user_table_id
AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
AND cin.user_row_id = urw.user_row_id
AND cin.user_column_id = col.user_column_id
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
ORDER BY TO_NUMBER(cin.value) ASC;
SELECT cin.value basic_pay
,col.user_column_name step_or_rate
,cin.effective_start_date
,cin.effective_end_date
FROM pay_user_column_instances_f cin
,pay_user_rows_f urw
,pay_user_columns col
WHERE col.user_table_id = p_user_table_id
AND urw.user_table_id = p_user_table_id
AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
AND cin.user_row_id = urw.user_row_id
AND cin.user_column_id = col.user_column_id
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
ORDER BY TO_NUMBER(cin.value) DESC;
SELECT cin.value basic_pay
,col.user_column_name step_or_rate
,cin.effective_start_date
,cin.effective_end_date
FROM pay_user_column_instances_f cin
,pay_user_rows_f urw
,pay_user_columns col
WHERE col.user_table_id = p_user_table_id
AND urw.user_table_id = p_user_table_id
AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
AND cin.user_row_id = urw.user_row_id
AND cin.user_column_id = col.user_column_id
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
ORDER BY TO_NUMBER(cin.value) ASC;
SELECT cin.value basic_pay
,col.user_column_name step_or_rate
,cin.effective_start_date
,cin.effective_end_date
FROM pay_user_column_instances_f cin
,pay_user_rows_f urw
,pay_user_columns col
WHERE col.user_table_id = p_user_table_id
AND urw.user_table_id = p_user_table_id
AND urw.row_low_range_or_name = p_pay_plan||'-'||p_grade_or_level
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
AND cin.user_row_id = urw.user_row_id
AND cin.user_column_id = col.user_column_id
AND NVL(p_effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date
AND cin.value >= p_x
ORDER BY TO_NUMBER(cin.value) ASC;
SELECT asg.position_id, asg.assignment_id
FROM per_assignments_f asg
WHERE asg.person_id = p_pay_calc_data.person_id
AND trunc(nvl(p_effective_date,sysdate))
between asg.effective_start_date and asg.effective_end_date
AND asg.assignment_type <> 'B'
AND asg.primary_flag = 'Y';
SELECT asg.effective_start_date,asg.position_id, asg.assignment_id
FROM per_assignments_f asg
WHERE asg.person_id = p_pay_calc_data.person_id
AND asg.position_id is not null
AND asg.assignment_type <> 'B'
AND asg.primary_flag = 'Y'
ORDER BY asg.effective_start_date;
SELECT pei.person_extra_info_id
,pei.pei_information6 retained_user_table_id
,pei.pei_information9 retained_temp_step
FROM per_people_extra_info pei
WHERE pei.person_id = p_pay_calc_data.person_id
AND pei.information_type = 'GHR_US_RETAINED_GRADE'
AND p_effective_date BETWEEN NVL(fnd_date.canonical_to_date(pei.pei_information1),p_effective_date)
AND NVL(fnd_date.canonical_to_date(pei.pei_information2),p_effective_date);
SELECT ppw.to_step
,ppl.maximum_step
FROM ghr_pay_plan_waiting_periods ppw
,ghr_pay_plans ppl
WHERE ppl.pay_plan = p_pay_plan
AND ppl.equivalent_pay_plan = ppw.pay_plan
AND ppw.from_step = p_current_step;
select 1 from ghr_pay_plans
where EQUIVALENT_PAY_PLAN = 'FW'
and PAY_PLAN = l_pay_plan;
select 1 from ghr_pay_plans
where EQUIVALENT_PAY_PLAN = 'ES'
and PAY_PLAN = l_pay_plan;