[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select per.sex
from per_all_people_f per
where per.person_id = P_pa_request_rec.person_id
and g_effective_date
between per.effective_start_date
and per.effective_end_date;
select rei_information3 payroll_name
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = 'GHR_US_PAR_PAYROLL_TYPE';
select rei_information3 rat_rec,
rei_information4 rec_pattern,
rei_information5 rec_level,
rei_information6 app_ends,
rei_information7 app_type,
rei_information8 date_init_appr_due,
rei_information9 date_effective,
rei_information10 unit,
rei_information11 org_str_id,
rei_information12 off_symbol,
rei_information13 pay_plan,
rei_information14 grade,
rei_information15 date_due,
rei_information16 appr_sys_ident,
rei_information17 optional_info,
rei_information18 performance_rating_points,
rei_information19 app_starts
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = 'GHR_US_PAR_PERF_APPRAISAL';
select ppt.system_person_type
from per_person_types ppt,
per_all_people_f ppf
where ppf.person_id = p_pa_request_rec.person_id
and ppt.person_type_id = ppf.person_type_id
and p_pa_request_rec.effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
Select asg.effective_start_date
from per_all_assignments_f asg
Where asg.assignment_id = p_pa_request_rec.employee_assignment_id
order by 1 desc;
Else -- for update get from history
hr_utility.set_location('populate_record_groups ', 35);
select ppt.system_person_type
from per_person_types ppt,
per_all_people_f ppf
where ppf.person_id = p_pa_request_rec.person_id
and ppt.person_type_id = ppf.person_type_id
and g_effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
select pei_information1 date_from,
pei_information2 date_to,
pei_information3 grade_or_level,
pei_information4 step_or_rate,
pei_information5 pay_plan,
pei_information6 pay_table_id,
pei_information7 locality_percent,
pei_information8 pay_basis
from per_people_Extra_info pei
where pei.person_Extra_info_id = p_per_retained_grade.person_extra_info_id;
SELECT
ast.user_status,
ast.per_system_status,
asg.effective_start_date
FROM
per_assignment_status_types ast,
per_all_assignments_f asg
WHERE
asg.assignment_id = c_assignment_id AND
ast.assignment_status_type_id = asg.assignment_status_type_id and
c_effective_date between asg.effective_start_date and asg.effective_end_date;
SELECT par.altered_pa_request_id
FROM ghr_pa_requests par
WHERE par.pa_request_id = c_request_id;
SELECT par.pa_request_id, par.altered_pa_request_id, pei.rei_information3
FROM ghr_pa_requests par, ghr_pa_request_extra_info pei
WHERE par.pa_request_id = pei.pa_request_id
AND pei.pa_request_id = c_request_id
AND pei.information_type = 'GHR_US_PAR_TERM_RET_GRADE';
select pos.business_group_id
from hr_all_positions_f pos -- Venkat - Position DT
where pos.position_id = p_position_id
and p_pa_request_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
Select *
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = l_information_type;
select ppt.system_person_type
from per_person_types ppt,
per_all_people_f ppf
where ppf.person_id = p_pa_request_rec.person_id
and ppt.person_type_id = ppf.person_type_id
and g_effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
Select rei_information4,rei_information8
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = l_information_type; */
-- Bug 3226555 Commented below code as they can never be equal. It was unnecessarily inserting a
-- row into the history for the table PER_POSITIONS_EXTRA_INFO and thereby making the Position
-- status as invalid when cancellation to any action was done.
/* If p_pa_request_rec.to_occ_code is not null then
If P_pa_request_rec.to_Occ_Code <>
nvl(p_pos_grp1.occupation_category_code,hr_api.g_varchar2) then
hr_utility.set_location(l_proc,105);
hr_utility.set_location('JH Update WS = ' || p_pos_grp1.work_schedule,115);
hr_utility.set_location('JH Update PTH = ' || p_pos_grp1.part_time_hours,117);
Select pit.information_type
from ghr_pa_request_info_types pit,
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.pa_info_type_flag = 'Y'
and pit.noa_family_code = fam.noa_family_code
and pit.information_type like 'GHR_US%'
and p_pa_request_rec.effective_date BETWEEN NVL(nfa.start_date_active,p_pa_request_rec.effective_date)
AND NVL(nfa.end_date_active,p_pa_request_rec.effective_date);
Select *
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = l_information_type;
Select *
from ghr_pa_request_extra_info
where pa_request_id = p_pa_request_rec.pa_request_id
and information_type = l_information_type
and fnd_date.canonical_to_date(rei_information6) = (select MIN (NVL(fnd_date.canonical_to_date(rei_information6),p_pa_request_rec.effective_date) )
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_pa_request_rec.pa_request_id
AND information_type = l_information_type);
select ast.user_status,
ast.per_system_status,
asg.effective_start_date
from
per_assignment_status_types ast,
per_all_assignments_f asg
where asg.assignment_id = p_pa_request_rec.employee_assignment_id
and ast.assignment_status_type_id = asg.assignment_status_type_id
and p_pa_request_rec.effective_date
between asg.effective_start_date
and asg.effective_end_date;
select ast.user_status,asg.effective_start_date
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_pa_request_rec.employee_assignment_id
and asg.primary_flag = 'Y'
order by asg.effective_start_date;
select ast.user_status
from per_assignment_status_types ast,
per_all_assignments_f asg
where ast.assignment_status_type_id = asg.assignment_status_type_id
and asg.assignment_id = p_pa_request_rec.employee_assignment_id
and asg.primary_flag = 'Y'
and user_status='Active Appointment';
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 NAME from PER_GRADES
where GRADE_ID = to_number(p_grade_id);*/
p_update_flag in out nocopy varchar2,
p_auto_populate_flag in varchar2 default 'N'
) is
Begin
If nvl(p_hr_extra_info,hr_api.g_varchar2)
<> nvl(p_ghr_extra_info,hr_api.g_varchar2) then
If p_auto_populate_flag = 'Y' and
l_session.noa_id_correct is null then
-- since it is autopopulated the user might have made it null
-- and is not a correction
p_hr_extra_info := p_ghr_extra_info;
p_update_flag := 'Y';
select *
from ghr_pa_request_ei_shadow
where pa_request_id = p_pa_request_id
and information_type = p_information_type;
SELECT per.business_group_id
FROM per_all_people_f per
WHERE per.person_id = p_pa_request_rec.person_id
and p_pa_request_rec.effective_date between per.effective_start_date and per.effective_end_date;
SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment
FROM pay_element_entries_f eef,
pay_element_types_f elt
WHERE assignment_id = p_pa_request_rec.employee_assignment_id
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 p_pa_request_rec.effective_date between eef.effective_start_date and eef.effective_end_date
AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
p_business_group_id,
p_pa_request_rec.effective_date)) IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX');
-- and also made null value to be updated only if previous enrollment is Z and Current Enrollment is other
-- than Z
--bug# 12761010 added familiy code EXT_NTE condition
If (p_pa_request_rec.noa_family_code = 'CONV_APP' OR (p_pa_request_rec.noa_family_code = 'EXT_NTE' AND l_pa_request_ei_rec.rei_information3 IS NOT NULL))
AND (l_prev_enrollment = 'Z' and NVL(l_pa_request_ei_rec.rei_information6,'Z') <> 'Z') THEN
set_extra_info(p_per_benefit_info.FEHB_Date_temp_eligibility,
l_pa_request_ei_rec.rei_information4,p_per_benefit_info.per_benefit_info_flag, 'Y');
--Bug # 12552732 Modified to send auto populate flag as N to not to update Null unless the user
--- Nullifies any field
if p_pa_request_rec.noa_family_code = 'APP' then
set_extra_info(p_per_benefit_info.FEHB_Date_Eligibility_Expires,
l_pa_request_ei_rec.rei_information3,p_per_benefit_info.per_benefit_info_flag, 'Y');
--Bug # 12552732 Modified to send auto populate flag as N to not to update Null unless the user
--- Nullifies any field
If shadow_ei_rec.rei_information3 is NOT NULL and l_pa_request_ei_rec.rei_information3 is NULL then
set_extra_info(p_per_benefit_info.FEHB_Date_Eligibility_Expires,
l_pa_request_ei_rec.rei_information3,p_per_benefit_info.per_benefit_info_flag, 'Y');
SELECT par.pa_request_id, par.altered_pa_request_id, pei.rei_information3
FROM ghr_pa_requests par, ghr_pa_request_extra_info pei
WHERE par.pa_request_id = pei.pa_request_id(+)
AND pei.pa_request_id = c_request_id
AND pei.information_type = 'GHR_US_PAR_TERM_RET_GRADE';
SELECT person_extra_info_id, object_version_number
FROM per_people_extra_info
WHERE person_extra_info_id = p_pei_id;
hr_utility.set_location('promotion_eligibility_date update '|| l_valid_grade_name,353);
End GHR_SF52_PRE_UPDATE;