[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM ghr_pa_request_extra_info
WHERE pa_request_id = p_old_pa_request_id;
SELECT pei.pei_information4 initiator_flag
,pei.pei_information5 requester_flag
,pei.pei_information6 authorizer_flag
,pei.pei_information7 personnelist_flag
,pei.pei_information8 approver_flag
,pei.pei_information9 reviewer_flag
FROM per_people_extra_info pei
,fnd_user usr
WHERE usr.user_name = p_user_name
AND pei.person_id = usr.employee_id
AND pei.information_type = 'GHR_US_PER_WF_ROUTING_GROUPS'
AND pei.pei_information3 = p_routing_group_id;
SELECT PA_NOTIFICATION_ID
FROM GHR_PA_REQUESTS_V1
WHERE person_id = p_person_id
AND effective_date >= p_effective_date;*/
select PA_NOTIFICATION_ID,effective_date
from ghr_pa_requests par
where (level = 1 and pa_notification_id is not null) or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
start with altered_pa_request_id is null
and person_id = p_person_id
and NVL(first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
and first_noa_canc_pa_request_id is null
connect by prior pa_request_id = altered_pa_request_id
and
prior decode(first_noa_code, '002', second_noa_code, '001' , second_noa_code ,first_noa_code) = second_noa_code
UNION ALL
select PA_NOTIFICATION_ID,effective_date
from ghr_pa_requests par
where (level = 1 and pa_notification_id is not null)
or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
start with altered_pa_request_id is null
and person_id = p_person_id
and NVL(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
and second_noa_code is not null and second_noa_canc_pa_request_id is null
connect by prior pa_request_id = altered_pa_request_id
and
prior second_noa_code = second_noa_code;
select 'EX_EMP' system_person_type
from ghr_pa_requests pa
where pa.noa_family_code = 'SEPARATION'
and pa.effective_date < p_effective_date
and pa.person_id = p_person_id
and exists (select '1'
from ghr_pa_history pah
where pah.pa_request_id = pa.pa_request_id);
SELECT
effective_date
, DECODE(first_noa_code, '002', second_noa_code
, '001', second_noa_code
, first_noa_code) noa_code
, ROWNUM row_num
, LEVEL hierarchy_level
, pa_request_id
, pa_notification_id
, approval_date
, person_id
, employee_assignment_id
, 1 WHICH_NOA
, ROWID ROW_ID
, DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
, altered_pa_request_id
, status
FROM ghr_pa_requests par
WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
or (level > 1
and ( nvl(status, 'CANCELED') <> 'CANCELED'
AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND first_noa_code <> '001'
)
)
START WITH altered_pa_request_id IS NULl
AND NVL(first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND ROWID = CHARTOROWID(p_row_id)
AND p_which_noa = 1
AND first_noa_canc_pa_request_id IS NULL
CONNECT BY PRIOR pa_request_id = altered_pa_request_id
AND PRIOR DECODE(first_noa_code, '002', second_noa_code, '001', second_noa_code ,first_noa_code) = second_noa_code
UNION ALL
SELECT
effective_date
, second_noa_code
, ROWNUM
, LEVEL
, pa_request_id
, pa_notification_id
, approval_date
, par.person_id
, par.employee_assignment_id
, 2 which_noa
, par.ROWID
, DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
, altered_pa_request_id
, par.status
FROM ghr_pa_requests par
WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
or (level > 1
and ( nvl(status, 'CANCELED') <> 'CANCELED'
AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND first_noa_code <> '001'
)
)
START WITH altered_pa_request_id IS NULL
AND NVL(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND second_noa_code IS NOT NULL
AND ROWID = chartorowid(p_row_id)
AND p_which_noa = 2
AND second_noa_canc_pa_request_id IS NULL
CONNECT BY PRIOR pa_request_id = altered_pa_request_id
AND PRIOR second_noa_code = second_noa_code
ORDER BY 1, 2, 3;*/
SELECT
effective_date
, DECODE(first_noa_code, '002', second_noa_code
, '001', second_noa_code
, first_noa_code) noa_code
, ROWNUM row_num
, LEVEL hierarchy_level
, pa_request_id
, pa_notification_id
, approval_date
, person_id
, employee_assignment_id
, 1 WHICH_NOA
, ROWID ROW_ID
, DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
, altered_pa_request_id
, status
FROM ghr_pa_requests par
WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
or (level > 1
and ( nvl(status, 'CANCELED') <> 'CANCELED'
AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND first_noa_code <> '001'
)
)
START WITH altered_pa_request_id IS NULl
AND NVL(first_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND ROWID = CHARTOROWID(p_row_id)
-- AND p_which_noa = 1
AND first_noa_canc_pa_request_id IS NULL
CONNECT BY PRIOR pa_request_id = altered_pa_request_id
AND PRIOR DECODE(first_noa_code, '002', second_noa_code, '001', second_noa_code ,first_noa_code) = second_noa_code
ORDER BY 1, 2, 3;
SELECT
effective_date
, second_noa_code
, ROWNUM
, LEVEL
, pa_request_id
, pa_notification_id
, approval_date
, par.person_id
, par.employee_assignment_id
, 2 which_noa
, par.ROWID
, DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
, altered_pa_request_id
, par.status
FROM ghr_pa_requests par
WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
or (level > 1
and ( nvl(status, 'CANCELED') <> 'CANCELED'
AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND first_noa_code <> '001'
)
)
START WITH altered_pa_request_id IS NULL
AND NVL(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'
AND second_noa_code IS NOT NULL
AND ROWID = chartorowid(p_row_id)
-- AND p_which_noa = 2
AND second_noa_canc_pa_request_id IS NULL
CONNECT BY PRIOR pa_request_id = altered_pa_request_id
AND PRIOR second_noa_code = second_noa_code
ORDER BY 1, 2, 3;
SELECT *
FROM ghr_pa_requests
WHERE ROWID = p_rowid;
select first_action_la_code1,first_action_la_code2,second_action_la_code1,second_action_la_code2
from ghr_pa_request_shadow
where pa_request_id = p_pa_request_id;
select *
from ghr_pa_requests
where rowid = chartorowid(p_row_id);
select nature_of_action_id, description
from ghr_nature_of_actions
where code = p_noa_code
and p_effective_date between date_from and nvl(date_to, p_effective_date);
select code
from ghr_nature_of_actions where nature_of_action_id = p_noa_id;
SELECT person_id
FROM per_all_people_f
WHERE person_id = p_person_id ;
insert into ghr_pa_request_shadow (
pa_request_id,
academic_discipline,
annuitant_indicator,
appropriation_code1,
appropriation_code2,
bargaining_unit_status,
citizenship,
duty_station_id,
duty_station_location_id,
education_level,
fegli,
flsa_category,
forwarding_address_line1,
forwarding_address_line2,
forwarding_address_line3,
forwarding_country_short_name,
forwarding_postal_code,
forwarding_region_2,
forwarding_town_or_city,
functional_class,
part_time_hours,
pay_rate_determinant,
position_occupied,
retirement_plan,
service_comp_date,
supervisory_status,
tenure,
to_ap_premium_pay_indicator,
to_auo_premium_pay_indicator,
to_occ_code,
to_position_id,
to_retention_allowance,
to_staffing_differential,
to_step_or_rate,
to_supervisory_differential,
veterans_preference,
veterans_pref_for_rif,
veterans_status,
work_schedule,
year_degree_attained,
employee_first_name,
employee_last_name,
employee_middle_names,
employee_national_identifier,
employee_date_of_birth,
first_action_la_code1, --Begin Bug# 8344672
first_action_la_code2,
second_action_la_code1,
second_action_la_code2) --End Bug# 8344672
values
(
l_pa_req_rec.pa_request_id,
l_pa_req_rec.academic_discipline,
l_pa_req_rec.annuitant_indicator,
l_pa_req_rec.appropriation_code1,
l_pa_req_rec.appropriation_code2,
l_pa_req_rec.bargaining_unit_status,
l_pa_req_rec.citizenship,
l_pa_req_rec.duty_station_id,
l_pa_req_rec.duty_station_location_id,
l_pa_req_rec.education_level,
l_pa_req_rec.fegli,
l_pa_req_rec.flsa_category,
l_pa_req_rec.forwarding_address_line1,
l_pa_req_rec.forwarding_address_line2,
l_pa_req_rec.forwarding_address_line3,
l_pa_req_rec.forwarding_country_short_name,
l_pa_req_rec.forwarding_postal_code,
l_pa_req_rec.forwarding_region_2,
l_pa_req_rec.forwarding_town_or_city,
l_pa_req_rec.functional_class,
l_pa_req_rec.part_time_hours,
l_pa_req_rec.pay_rate_determinant,
l_pa_req_rec.position_occupied,
l_pa_req_rec.retirement_plan,
l_pa_req_rec.service_comp_date,
l_pa_req_rec.supervisory_status,
l_pa_req_rec.tenure,
l_pa_req_rec.to_ap_premium_pay_indicator,
l_pa_req_rec.to_auo_premium_pay_indicator,
l_pa_req_rec.to_occ_code,
l_pa_req_rec.to_position_id,
l_pa_req_rec.to_retention_allowance,
l_pa_req_rec.to_staffing_differential,
l_pa_req_rec.to_step_or_rate,
l_pa_req_rec.to_supervisory_differential,
l_pa_req_rec.veterans_preference,
l_pa_req_rec.veterans_pref_for_rif,
l_pa_req_rec.veterans_status,
l_pa_req_rec.work_schedule,
l_pa_req_rec.year_degree_attained,
l_pa_req_rec.employee_first_name,
l_pa_req_rec.employee_last_name,
l_pa_req_rec.employee_middle_names,
l_pa_req_rec.employee_national_identifier,
l_pa_req_rec.employee_date_of_birth,
l_first_action_la_code1,--Begin Bug# 8344672
l_first_action_la_code2,
l_second_action_la_code1,
l_second_action_la_code2); --end Bug# 8344672
hr_utility.set_location('Updated SF52 - OVN '||to_char(p_par_object_version_number)|| '-'|| l_proc, 45);
select first_action_la_code1,first_action_la_code2,second_action_la_code1,second_action_la_code2
from ghr_pa_request_shadow
where pa_request_id = l_pa_req_rec.pa_request_id;
select *
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
select * from ghr_pa_requests
where pa_request_id = l_retro_pa_request_id;
SELECT *
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND pa_notification_id IS NOT NULL
AND effective_date < c_retro_eff_date
AND first_noa_code <> '001'
AND NVL(first_noa_cancel_or_correct,'C') <> 'CANCEL'
AND NVL(second_noa_cancel_or_correct,'C') <> 'CANCEL'
ORDER BY pa_request_id desc;
select pa_notification_id,noa_family_code,
second_noa_code
from ghr_pa_requests
where pa_request_id = p_par_id;
select nature_of_action_id, description
from ghr_nature_of_actions
where code = p_noa_code
and p_effective_date between date_from and nvl(date_to, p_effective_date);
select code
from ghr_nature_of_actions where nature_of_action_id = p_noa_id;
select noa_family_code from ghr_noa_families
where nature_of_action_id in
( select nature_of_action_id from ghr_nature_of_actions
where code = p_noa_code )
and noa_family_code in
( select noa_family_code from ghr_families
where update_hr_flag = 'Y')
and p_effective_date between NVL(start_date_active,p_effective_date)
and NVL(end_date_active,p_effective_date);
SELECT to_position_org_line1,
to_position_org_line2,
to_position_org_line3,
to_position_org_line4,
to_position_org_line5,
to_position_org_line6
FROM ghr_pa_requests
WHERE pa_request_id = c_pa_request_id;
hr_utility.set_location('Inserting incentive records',0);
INSERT INTO ghr_pa_incentives( pa_incentive_id,
pa_request_id ,
pa_incentive_category,
pa_incentive_category_percent ,
pa_incentive_category_amount,
pa_incentive_category_pmnt_dt ,
pa_incentive_category_end_date)
SELECT
ghr_pa_incentives_s.nextval,
l_pa_req_rec.pa_request_id,
pa_incentive_category,
pa_incentive_category_percent ,
pa_incentive_category_amount,
pa_incentive_category_pmnt_dt ,
pa_incentive_category_end_date
FROM GHR_PA_INCENTIVES
WHERE pa_request_id = l_pa_req_rec.altered_pa_request_id;
hr_utility.set_location('After Inserting incentive records',10);
insert into ghr_pa_request_shadow (
pa_request_id,
academic_discipline,
annuitant_indicator,
appropriation_code1,
appropriation_code2,
bargaining_unit_status,
citizenship,
duty_station_id,
duty_station_location_id,
education_level,
fegli,
flsa_category,
forwarding_address_line1,
forwarding_address_line2,
forwarding_address_line3,
forwarding_country_short_name,
forwarding_postal_code,
forwarding_region_2,
forwarding_town_or_city,
functional_class,
part_time_hours,
pay_rate_determinant,
position_occupied,
retirement_plan,
service_comp_date,
supervisory_status,
tenure,
to_ap_premium_pay_indicator,
to_auo_premium_pay_indicator,
to_occ_code,
to_position_id,
to_retention_allowance,
to_staffing_differential,
to_step_or_rate,
to_supervisory_differential,
veterans_preference,
veterans_pref_for_rif,
veterans_status,
work_schedule,
year_degree_attained,
employee_first_name,
employee_last_name,
employee_middle_names,
employee_national_identifier,
employee_date_of_birth,
first_action_la_code1, --Begin Bug# 8344672
first_action_la_code2,
second_action_la_code1,
second_action_la_code2) --End Bug# 8344672
values
(
l_pa_req_rec.pa_request_id,
l_pa_req_rec.academic_discipline,
l_pa_req_rec.annuitant_indicator,
l_pa_req_rec.appropriation_code1,
l_pa_req_rec.appropriation_code2,
l_pa_req_rec.bargaining_unit_status,
l_pa_req_rec.citizenship,
l_pa_req_rec.duty_station_id,
l_pa_req_rec.duty_station_location_id,
l_pa_req_rec.education_level,
l_pa_req_rec.fegli,
l_pa_req_rec.flsa_category,
l_pa_req_rec.forwarding_address_line1,
l_pa_req_rec.forwarding_address_line2,
l_pa_req_rec.forwarding_address_line3,
l_pa_req_rec.forwarding_country_short_name,
l_pa_req_rec.forwarding_postal_code,
l_pa_req_rec.forwarding_region_2,
l_pa_req_rec.forwarding_town_or_city,
l_pa_req_rec.functional_class,
l_pa_req_rec.part_time_hours,
l_pa_req_rec.pay_rate_determinant,
l_pa_req_rec.position_occupied,
l_pa_req_rec.retirement_plan,
l_pa_req_rec.service_comp_date,
l_pa_req_rec.supervisory_status,
l_pa_req_rec.tenure,
l_pa_req_rec.to_ap_premium_pay_indicator,
l_pa_req_rec.to_auo_premium_pay_indicator,
l_pa_req_rec.to_occ_code,
l_pa_req_rec.to_position_id,
l_pa_req_rec.to_retention_allowance,
l_pa_req_rec.to_staffing_differential,
l_pa_req_rec.to_step_or_rate,
l_pa_req_rec.to_supervisory_differential,
l_pa_req_rec.veterans_preference,
l_pa_req_rec.veterans_pref_for_rif,
l_pa_req_rec.veterans_status,
l_pa_req_rec.work_schedule,
l_pa_req_rec.year_degree_attained,
l_pa_req_rec.employee_first_name,
l_pa_req_rec.employee_last_name,
l_pa_req_rec.employee_middle_names,
l_pa_req_rec.employee_national_identifier,
l_pa_req_rec.employee_date_of_birth,
l_first_action_la_code1,--Begin Bug# 8344672
l_first_action_la_code2,
l_second_action_la_code1,
l_second_action_la_code2);--end Bug# 8344672
hr_utility.set_location('Updated SF52 - PA Request ID '||to_char(l_last_pa_request_rec.pa_request_id)|| '-'|| l_proc, 26);
hr_utility.set_location('Updated SF52 - OVN '||to_char(l_last_pa_request_rec.object_version_number)|| '-'|| l_proc, 25);
hr_utility.set_location('Updated PA Requests '|| l_proc, 20);
select count(*)
from ghr_pa_requests
where pa_notification_id is not null
and person_id = p_person_id
and effective_date = p_effective_date
group by person_id
having count(*) > 1
;
select 'Y' from
ghr_pa_requests a
where person_id = p_person_id
and effective_date = p_effective_date
and pa_notification_id is not null
and pa_notification_id < p_pa_notification_id
and not exists ( select 'Y' from ghr_pa_requests b
where b.person_id = a.person_id
and b.pa_notification_id is not null
and b.pa_notification_id > a.pa_notification_id
and b.altered_pa_request_id = a.pa_request_id
and b.first_noa_code = '002'
and b.second_noa_code = a.first_noa_code )
;
select effective_date,first_noa_code,
second_noa_code,pa_notification_id,pa_request_id,
first_noa_id,second_noa_id,rpa_type,mass_action_id
from ghr_pa_requests a
where pa_notification_id is not null
and person_id = p_person_id
and pa_notification_id > p_pa_notification_id
and effective_date <= p_effective_date
-- and first_noa_code not in ('001') -- Exclude all cancellations
and pa_request_id not in ( -- Exclude all cancellation of correction actions
select nvl(altered_pa_request_id,0)
from ghr_pa_requests b
where a.person_id = b.person_id
and b.first_noa_code in ('001')
and b.pa_notification_id is not null )
and pa_request_id not in ( -- Exclude all the corrections on the current action
select nvl(pa_request_id,0)
from ghr_pa_requests c
connect by prior pa_request_id = altered_pa_request_id
start with altered_pa_request_id = p_pa_request_id )
order by pa_notification_id desc;
select 1
from ghr_nature_of_actions
where nature_of_action_id = p_noa_id_correct
and order_of_processing < (select order_of_processing
from ghr_nature_of_actions
where code = ../../../www.oracle.com/splash/support/intgeneric/error-page2-495599.html
and p_effective_date between nvl(date_from,p_effective_date)
and nvl(date_to,p_effective_date)
);
select min(pa_request_id)
from ghr_pa_requests
where pa_notification_id is not null
and altered_pa_request_id is null
start with pa_request_id = p_pa_request_id
connect by pa_request_id = prior altered_pa_request_id;
procedure Update_Dual_Id(p_parent_pa_request_id in number,
p_first_dual_action_id in number,
p_second_dual_action_id in number)
is
l_ovn1 ghr_pa_requests.object_version_number%type;
select object_version_number
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
end Update_Dual_Id;