[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_shadow_row ( p_shadow_data in ghr_pa_request_shadow%rowtype,
p_result out nocopy Boolean);
/*Procedure fetch_update_routing_details
(p_pa_request_id in ghr_pa_requests.pa_request_id%type,
p_object_version_number in out ghr_pa_requests.object_version_number%type,
p_position_id in ghr_pa_requests.to_position_id%type,
p_effective_date in ghr_pa_requests.effective_date%type,
p_retcode out number,
p_route_flag out boolean
);
select rowid,pa_request_id,first_noa_code,second_noa_code,
object_version_number,first_noa_id,second_noa_id
from ghr_pa_requests
where pa_request_id = p_sf52_data.altered_pa_request_id
and first_noa_code not in ('001','002')
and second_noa_code is not null
and pa_notification_id is not null;
/*and not exists (select 1
from ghr_pa_requests
where nvl(rpa_type,'-1') <> 'DUAL'
and mass_action_id is null
and first_noa_code in ('002')
and pa_notification_id is not null
start with pa_request_id = p_sf52_data.altered_pa_request_id
and altered_pa_request_id is null
connect by altered_pa_request_id = prior pa_request_id);*/
print_sf52('Before Update to HR Single Action : ' , l_sf52_data);
print_sf52('SINGLE_ACTION BEFORE UPDATE : ' , l_sf52_data);
ghr_sf52_validn_pkg.prelim_req_chk_for_update_hr
(p_pa_request_rec => p_sf52_data
);
ghr_sf52_update.main( p_pa_request_rec => l_sf52_data,
p_pa_request_ei_rec => l_sf52_ei_data,
p_generic_ei_rec => l_agency_ei_data,
p_capped_other_pay => l_capped_other_pay);
ghr_sf52_validn_pkg.prelim_req_chk_for_update_hr
(p_pa_request_rec => p_sf52_data
);
ghr_sf52_update.main( p_pa_request_rec => l_sf52_data,
p_pa_request_ei_rec => l_sf52_ei_data,
p_generic_ei_rec => l_agency_ei_data,
p_capped_other_pay => l_capped_other_pay);
ghr_sf52_post_update.Post_sf52_process(
p_pa_request_id => l_sf52_data.pa_request_id,
p_effective_date => l_session_var.date_effective,
p_object_version_number => l_sf52_data.object_version_number,
p_from_position_id => l_sf52_data.from_position_id,
p_to_position_id => l_sf52_data.to_position_id,
p_agency_code => l_sf52_data.agency_code,
p_sf52_data_result => l_sf52_data
);
Update_rfrs_values( p_sf52_data => l_sf52_data,
p_shadow_data => l_shadow_data);
ghr_sf52_validn_pkg.prelim_req_chk_for_update_hr
(p_pa_request_rec => p_sf52_data
);
ghr_sf52_update.main(
p_pa_request_rec => l_sf52_data,
p_pa_request_ei_rec => l_sf52_ei_data,
p_generic_ei_rec => l_agency_ei_data,
p_capped_other_pay => l_capped_other_pay);
ghr_sf52_validn_pkg.prelim_req_chk_for_update_hr
(p_pa_request_rec => p_sf52_data
);
ghr_sf52_update.main(
p_pa_request_rec => l_sf52_data,
p_pa_request_ei_rec => l_sf52_ei_data,
p_generic_ei_rec => l_agency_ei_data,
p_capped_other_pay => l_capped_other_pay);
ghr_sf52_post_update.Post_sf52_process(
p_pa_request_id => p_sf52_data.pa_request_id,
p_effective_date => l_session_var.date_effective,
p_object_version_number => p_sf52_data.object_version_number,
p_from_position_id => p_sf52_data.from_position_id,
p_to_position_id => p_sf52_data.to_position_id,
p_agency_code => p_sf52_data.agency_code,
p_sf52_data_result => l_sf52_data);
Update_rfrs_values( p_sf52_data => l_sf52_data_save,
p_shadow_data => l_shadow_data);
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_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 <> 'GHR_US_PAR_GEN_AGENCY_DATA'
and pit.information_type like 'GHR_US%';
select *
from ghr_pa_request_extra_info
where pa_request_id = cp_pa_request_id and
information_type = cp_info_type;
SELECT min(pa_history_id)
FROM ghr_pa_history ghrpah_1
WHERE ghrpah_1.pa_request_id =
(SELECT min(pa_request_id)
FROM ghr_pa_requests
CONNECT BY pa_request_id = prior altered_pa_request_id
START WITH pa_request_id = cp_pa_request_id)
AND ghrpah_1.nature_of_action_id = cp_noa_id;
SELECT noa_family_code
FROM ghr_dual_actions
WHERE first_noa_id = p_first_noa_id
AND second_noa_id = p_second_noa_id;
SELECT *
FROM ghr_dual_proc_methods
WHERE LOWER(noa_family_code) = LOWER(p_noa_family_code)
AND LOWER(form_field_name) = LOWER(p_form_field_name);
SELECT fld.form_field_name,
met.process_method_code
FROM
ghr_families ghrf,
ghr_noa_fam_proc_methods met,
ghr_pa_data_fields fld,
ghr_noa_families fam
WHERE
fam.noa_family_code = met.noa_family_code
AND ghrf.noa_family_code = met.noa_family_code
AND ghrf.update_hr_flag = 'Y'
AND met.process_method_code in ('AP', 'APUE', 'UE')
AND met.pa_data_field_id = fld.pa_data_field_id
AND fam.nature_of_action_id = p_noa_id;
select
fams.noa_family_code
from ghr_noa_families noafam,
ghr_families fams
where noafam.nature_of_action_id = c_noa_id and
noafam.enabled_flag = 'Y' and
fams.noa_family_code = noafam.noa_family_code and
fams.enabled_flag = 'Y' and
fams.update_hr_flag = 'Y';
select person_id,effective_date
from ghr_pa_requests a, ghr_pa_routing_history b
where effective_date <= sysdate
and pa_notification_id is null
and approval_date is not null
and a.pa_request_id = b.pa_request_id
and p_poi =
(select POEI_INFORMATION3 from per_position_extra_info
where information_type = 'GHR_US_POS_GRP1'
and position_id = nvl(a.to_position_id,a.from_position_id))
and action_taken = 'FUTURE_ACTION'
and exists
(select 1
from per_people_f per
where per.person_id = a.person_id
and a.effective_date between
per.effective_start_date and per.effective_end_date )
and b.pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
group by person_id,effective_date
order by 2,1;
select person_id,effective_date
from ghr_pa_requests a, ghr_pa_routing_history b
where effective_date <= sysdate
and pa_notification_id is null
and approval_date is not null
and a.pa_request_id = b.pa_request_id
and action_taken = 'FUTURE_ACTION'
and exists
(select 1
from per_people_f per
where per.person_id = a.person_id
and a.effective_date between
per.effective_start_date and per.effective_end_date )
and b.pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
group by person_id,effective_date
order by 2,1;
select distinct person_id,effective_date
from ghr_pa_requests a
where effective_date <= sysdate and
pa_notification_id is null and
approval_date is not null and
exists (select 'exists'
from ghr_pa_routing_history
where pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
and action_taken in ('FUTURE_ACTION'))
order by 2,1;
select pa_request_id ,noa.order_of_processing
from ghr_pa_requests a, ghr_nature_of_actions noa
where person_id = l_person_id
and effective_date = l_effective_date
and pa_notification_id is null
and approval_date is not null
and noa.code = a.first_noa_code
and exists (select 'exists'
from ghr_pa_routing_history
where pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
and action_taken in ('FUTURE_ACTION'))
order by 2 asc;
select * from ghr_pa_requests
where pa_request_id = l_pa_request_id;
select userenv('sessionid') sesid from dual;
select pa_request_id ,noa.order_of_processing
from ghr_pa_requests a, ghr_nature_of_actions noa
where person_id = l_person_id
and effective_date = l_effective_date
and pa_notification_id is null
and approval_date is not null
and noa.code = a.first_noa_code
and exists (select 'exists'
from ghr_pa_routing_history
where pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
and action_taken in ('FUTURE_ACTION'))
order by 2 asc;
select * from ghr_pa_requests
where pa_request_id = l_pa_request_id;
select par.object_version_number
from ghr_pa_requests par
where par.pa_request_id = p_pa_request_id; -- 3769917
'Cause: The Personnel Action attempted to update the employee''s salary with a ' || l_new_line ||
'decreased amount of Basic Pay. ' || l_new_line ||
'Action: Please review the personnel action to verify the Grade and Step, Pay Table amounts,' || l_new_line ||
'and Pay Rate Determinant code for this employee.' ; -- Bug 3320086 Changed error message.
ghr_sf52_post_update.get_notification_details
(p_pa_request_id => l_sf52_rec.pa_request_id,
p_effective_date => l_sf52_rec.effective_date,
p_from_position_id => l_sf52_rec.from_position_id,
p_to_position_id => l_sf52_rec.to_position_id,
p_agency_code => l_sf52_rec.agency_code,
p_from_agency_code => l_sf52_rec.from_agency_code,
p_from_agency_desc => l_sf52_rec.from_agency_desc,
p_from_office_symbol => l_sf52_rec.from_office_symbol,
p_personnel_office_id => l_sf52_rec.personnel_office_id,
p_employee_dept_or_agency => l_sf52_rec.employee_dept_or_agency,
p_to_office_symbol => l_sf52_rec.to_office_symbol
);
'Action: RPA related information has changed. Retrieve the RPA from the groupbox to review the refreshed information, make necessary changes, and update HR',1,2000);
update fnd_sessions set SESSION_ID = l_sid
where SESSION_ID = l_sid;
INSERT INTO fnd_sessions
(SESSION_ID,EFFECTIVE_DATE)
VALUES
(l_sid,sysdate);
Procedure fetch_update_routing_details
(p_pa_request_id in ghr_pa_requests.pa_request_id%type,
p_object_version_number in out ghr_pa_requests.object_version_number%type,
p_position_id in ghr_pa_requests.to_position_id%type,
p_effective_date in ghr_pa_requests.effective_date%type,
p_retcode out nocopy number,
p_route_flag out nocopy boolean
)
is
l_groupbox_id ghr_groupboxes.groupbox_id%type;
l_proc varchar2(72) := ' ghr_process_sf52.' || 'update_routing_details';
select pa_routing_history_id,
object_version_number,
user_name,
groupbox_id
from ghr_pa_routing_history
where pa_request_id = p_pa_request_id
order by 1 desc;
l_proc varchar2(72) := 'fetch_update_rout_details ' || 'get_personnel_off_groupbox';
select substr(hl.description,1,30) description
from hr_lookups hl
where hl.application_id = 800
and hl.lookup_type = 'GHR_US_PERSONNEL_OFFICE_ID'
and hl.lookup_code = l_personnel_office_id
and hl.enabled_flag = 'Y'
and nvl(p_effective_date,trunc(sysdate))
between nvl(hl.start_date_active,nvl(p_effective_date,trunc(sysdate)))
and nvl(hl.end_date_active,nvl(p_effective_date,trunc(sysdate)));
select gbx.groupbox_id gpid, gbx.routing_group_id rgpid
--gbx.name, rgp.name
from ghr_groupboxes gbx,
ghr_routing_groups rgp
where gbx.name = l_groupbox_name
and gbx.routing_group_id = rgp.routing_group_id;
savepoint fetch_update_routing_details;
rollback to fetch_update_routing_details;
rollback to fetch_update_routing_details;
end fetch_update_routing_details;
select *
from ghr_pa_requests a
where person_id = cp_person_id and
effective_date > cp_date_effective and
pa_notification_id is null and
approval_date is not null and
exists (select 'exists'
from ghr_pa_routing_history
where pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
and action_taken in ('FUTURE_ACTION')) ;
select *
from ghr_pa_requests a
where person_id = cp_person_id and
effective_date = cp_date_effective and
pa_notification_id is null and
approval_date is not null and
exists (select 'exists'
from ghr_pa_routing_history
where pa_routing_history_id = (select max(pa_routing_history_id)
from ghr_pa_routing_history
where pa_request_id = a.pa_request_id)
and action_taken in ('FUTURE_ACTION')) ;
insert into ghr_process_log
(process_log_id
,program_name
,log_text
,message_name
,log_date
)
values
(ghr_process_log_s.nextval
,p_program_name
,p_log_text
,p_message_name
,p_log_date
);
SELECT per.national_identifier
,per.date_of_birth
,per.first_name
,per.last_name
,per.middle_names
FROM per_people_f per
WHERE per.person_id = p_person_id
AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date and per.effective_end_date;
select
business_group_id
from hr_all_positions_f pos -- Venkat -- Position DT
where pos.position_id = c_position_id
and p_effective_date between pos.effective_start_date
and pos.effective_end_date;
select
noa_family_code, first_noa_code, first_noa_id,
second_noa_code, second_noa_id
, pay_rate_determinant
/* input_pay_rate_determinant*/
from ghr_pa_requests
where pa_request_id = nvl(p_sf52_data.pa_request_id,l_session_var.pa_request_id);
select ppt.system_person_type
from per_people_f ppf,
per_person_types ppt
where ppf.person_id = p_person_id
and p_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and ppt.person_type_id = ppf.person_type_id;
select fpm.process_method_code
from ghr_noa_families nof
,ghr_families fam
,ghr_noa_fam_proc_methods fpm
,ghr_pa_data_fields pdf
where nof.nature_of_action_id = p_noa_id
and nof.noa_family_code = fam.noa_family_code
and nof.enabled_flag = 'Y'
and p_effective_date between nvl(nof.start_date_active,p_effective_date) and nvl(nof.end_date_active,p_effective_date)
and fam.proc_method_flag = 'Y'
and fam.enabled_flag = 'Y'
and p_effective_date between nvl(fam.start_date_active,p_effective_date) and nvl(fam.end_date_active,p_effective_date)
and fam.noa_family_code = fpm.noa_family_code
and fpm.pa_data_field_id = pdf.pa_data_field_id
and fpm.enabled_flag = 'Y'
and p_effective_date between nvl(fpm.start_date_active,p_effective_date) and nvl(fpm.end_date_active,p_effective_date)
and pdf.form_field_name = 'TO_POSITION_TITLE'
and pdf.enabled_flag = 'Y'
and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
SELECT *
FROM ghr_pa_requests par
WHERE par.pa_request_id =
(SELECT min(par1.pa_request_id)
FROM ghr_pa_requests par1
start with pa_request_id = c_pa_request_id
connect by pa_request_id = prior altered_pa_request_id);
select pay_rate_determinant
from ghr_pa_requests
where pa_request_id = (select max(pa_request_id)
from ghr_pa_requests
where pa_notification_id is not null
and (first_noa_code = '740' or second_noa_code = '740')
start with pa_request_id = (select mass_action_id
from ghr_pa_requests
where pa_request_id = l_session_var.pa_request_id)
connect by pa_request_id = prior altered_pa_request_id);
select 1
from ghr_pa_requests
where pa_request_id = (SELECT min(par1.pa_request_id)
FROM ghr_pa_requests par1
start with pa_request_id = l_session_var.altered_pa_request_id
connect by pa_request_id = prior altered_pa_request_id)
and second_noa_id =l_session_var.noa_id_correct;
select fpm.process_method_code
from ghr_noa_families nof
,ghr_families fam
,ghr_noa_fam_proc_methods fpm
,ghr_pa_data_fields pdf
where nof.nature_of_action_id = p_noa_id
and nof.noa_family_code = fam.noa_family_code
and nof.enabled_flag = 'Y'
and p_effective_date between nvl(nof.start_date_active,p_effective_date) and nvl(nof.end_date_active,p_effective_date)
and fam.proc_method_flag = 'Y'
and fam.enabled_flag = 'Y'
and p_effective_date between nvl(fam.start_date_active,p_effective_date) and nvl(fam.end_date_active,p_effective_date)
and fam.noa_family_code = fpm.noa_family_code
and fpm.pa_data_field_id = pdf.pa_data_field_id
and fpm.enabled_flag = 'Y'
and p_effective_date between nvl(fpm.start_date_active,p_effective_date) and nvl(fpm.end_date_active,p_effective_date)
and pdf.form_field_name = 'PAY_RATE_DETERMINANT'
and pdf.enabled_flag = 'Y'
and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
SELECT assignment_id, person_id
FROM per_all_assignments_f
WHERE position_id = c_position_id
AND assignment_type <> 'B'
AND primary_flag = 'Y'
AND c_effective_date
between effective_start_date and effective_end_date;
SELECT fld.form_field_name
FROM
ghr_families ghrf,
ghr_noa_fam_proc_methods met,
ghr_pa_data_fields fld,
ghr_noa_families fam
WHERE
fam.noa_family_code = met.noa_family_code
AND ghrf.noa_family_code = met.noa_family_code
AND ghrf.update_hr_flag = 'Y'
AND met.process_method_code in ('AP', 'APUE')
AND met.pa_data_field_id = fld.pa_data_field_id
AND fam.nature_of_action_id = p_1st_noa_id
UNION
SELECT fld2.form_field_name
FROM
ghr_families ghrf2,
ghr_noa_fam_proc_methods met2,
ghr_pa_data_fields fld2,
ghr_noa_families fam2
WHERE
fam2.noa_family_code = met2.noa_family_code
AND ghrf2.noa_family_code = met2.noa_family_code
AND ghrf2.update_hr_flag = 'Y'
AND met2.process_method_code in ('AP', 'APUE')
AND met2.pa_data_field_id = fld2.pa_data_field_id
AND fam2.nature_of_action_id = p_2nd_noa_id;
SELECT fld.form_field_name
FROM
ghr_families ghrf,
ghr_noa_fam_proc_methods met,
ghr_pa_data_fields fld,
ghr_noa_families fam
WHERE
fam.noa_family_code = met.noa_family_code
AND ghrf.noa_family_code = met.noa_family_code
AND ghrf.update_hr_flag = 'Y'
AND met.process_method_code in ('AP', 'APUE')
AND met.pa_data_field_id = fld.pa_data_field_id
AND fam.nature_of_action_id = p_1st_noa_id;
SELECT fld.form_field_name
FROM
ghr_families ghrf,
ghr_noa_fam_proc_methods met,
ghr_pa_data_fields fld,
ghr_noa_families fam
WHERE
fam.noa_family_code = met.noa_family_code
AND ghrf.noa_family_code = met.noa_family_code
AND ghrf.update_hr_flag = 'Y'
AND met.pa_data_field_id = fld.pa_data_field_id
AND met.process_method_code = 'UE'
AND fld.form_field_name = 'TO_POSITION_TITLE'
AND fam.nature_of_action_id = p_1st_noa_id;
SELECT fld.form_field_name
FROM
ghr_families ghrf,
ghr_noa_fam_proc_methods met,
ghr_pa_data_fields fld,
ghr_noa_families fam
WHERE
fam.noa_family_code = met.noa_family_code
AND ghrf.noa_family_code = met.noa_family_code
AND ghrf.update_hr_flag = 'Y'
AND met.pa_data_field_id = fld.pa_data_field_id
AND met.process_method_code = 'UE'
AND fld.form_field_name = 'TO_POSITION_TITLE'
AND (fam.nature_of_action_id = p_1st_noa_id
OR fam.nature_of_action_id = p_2nd_noa_id);
select *
from ghr_pa_request_shadow
where pa_request_id = c_pa_request_id ;
-- Since redo_pay_calc will be called number of times during the Update HR
-- and we can not use the p_sf52_rec.retention_allowance as this value
-- might be a re-computed value in the earlier call to sql_main_pay_calc
-- That's why we are fetching the retention_allowance from DB
IF p_sf52_rec.employee_assignment_id is not null and
p_sf52_rec.effective_date is not null
THEN
-- Bug 4689374 - If FWFA then, dont take from element entry.
IF p_sf52_rec.noa_family_code like 'GHR_SAL%' AND
p_sf52_rec.pay_rate_determinant IN ('3','4','J','K','U','V') AND
p_sf52_rec.effective_date >= to_date('01/05/2005','dd/mm/yyyy') THEN
l_pay_calc_in_rec.retention_allowance := p_sf52_rec.to_retention_allowance;
Procedure Update_rfrs_values( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
p_shadow_data in ghr_pa_request_shadow%rowtype) is
cursor get_par_ovn is
select
object_version_number
from ghr_pa_requests
where pa_request_id = p_Sf52_data.pa_request_id;
select ppt.system_person_type
from per_people_f ppf,
per_person_types ppt
where ppf.person_id = p_sf52_data.person_id
and p_sf52_data.effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and ppt.person_type_id = ppf.person_type_id;
update_shadow_row ( p_shadow_data => p_shadow_data,
p_result => l_result);
hr_utility.set_location('Leaving Update_rfrs_values ' ,160);
End Update_rfrs_values;
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_retention_allow_percentage
,to_staffing_differential
,to_staffing_diff_percentage
,to_step_or_rate
,to_supervisory_differential
,to_supervisory_diff_percentage
,veterans_preference
,veterans_pref_for_rif
,veterans_status
,work_schedule
,year_degree_attained )
values
(p_shadow_data.pa_request_id
,p_shadow_data.academic_discipline
,p_shadow_data.annuitant_indicator
,p_shadow_data.appropriation_code1
,p_shadow_data.appropriation_code2
,p_shadow_data.bargaining_unit_status
,p_shadow_data.citizenship
,p_shadow_data.duty_station_id
,p_shadow_data.duty_station_location_id
,p_shadow_data.education_level
,p_shadow_data.fegli
,p_shadow_data.flsa_category
,p_shadow_data.forwarding_address_line1
,p_shadow_data.forwarding_address_line2
,p_shadow_data.forwarding_address_line3
,p_shadow_data.forwarding_country_short_name
,p_shadow_data.forwarding_postal_code
,p_shadow_data.forwarding_region_2
,p_shadow_data.forwarding_town_or_city
,p_shadow_data.functional_class
,p_shadow_data.part_time_hours
,p_shadow_data.pay_rate_determinant
,p_shadow_data.position_occupied
,p_shadow_data.retirement_plan
,p_shadow_data.service_comp_date
,p_shadow_data.supervisory_status
,p_shadow_data.tenure
,p_shadow_data.to_ap_premium_pay_indicator
,p_shadow_data.to_auo_premium_pay_indicator
,p_shadow_data.to_occ_code
,p_shadow_data.to_position_id
,p_shadow_data.to_retention_allowance
,p_shadow_data.to_retention_allow_percentage
,p_shadow_data.to_staffing_differential
,p_shadow_data.to_staffing_diff_percentage
,p_shadow_data.to_step_or_rate
,p_shadow_data.to_supervisory_differential
,p_shadow_data.to_supervisory_diff_percentage
,p_shadow_data.veterans_preference
,p_shadow_data.veterans_pref_for_rif
,p_shadow_data.veterans_status
,p_shadow_data.work_schedule
,p_shadow_data.year_degree_attained
);
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_retention_allow_percentage
,to_staffing_differential
,to_staffing_diff_percentage
,to_step_or_rate
,to_supervisory_differential
,to_supervisory_diff_percentage
,veterans_preference
,veterans_pref_for_rif
,veterans_status
,work_schedule
,year_degree_attained )
values
(p_sf52_data.pa_request_id
,p_sf52_data.academic_discipline
,p_sf52_data.annuitant_indicator
,p_sf52_data.appropriation_code1
,p_sf52_data.appropriation_code2
,p_sf52_data.bargaining_unit_status
,p_sf52_data.citizenship
,p_sf52_data.duty_station_id
,p_sf52_data.duty_station_location_id
,p_sf52_data.education_level
,p_sf52_data.fegli
,p_sf52_data.flsa_category
,p_sf52_data.forwarding_address_line1
,p_sf52_data.forwarding_address_line2
,p_sf52_data.forwarding_address_line3
,p_sf52_data.forwarding_country_short_name
,p_sf52_data.forwarding_postal_code
,p_sf52_data.forwarding_region_2
,p_sf52_data.forwarding_town_or_city
,p_sf52_data.functional_class
,p_sf52_data.part_time_hours
,p_sf52_data.pay_rate_determinant
,p_sf52_data.position_occupied
,p_sf52_data.retirement_plan
,p_sf52_data.service_comp_date
,p_sf52_data.supervisory_status
,p_sf52_data.tenure
,p_sf52_data.to_ap_premium_pay_indicator
,p_sf52_data.to_auo_premium_pay_indicator
,p_sf52_data.to_occ_code
,p_sf52_data.to_position_id
,p_sf52_data.to_retention_allowance
,p_sf52_data.to_retention_allow_percentage
,p_sf52_data.to_staffing_differential
,p_sf52_data.to_staffing_diff_percentage
,p_sf52_data.to_step_or_rate
,p_sf52_data.to_supervisory_differential
,p_sf52_data.to_supervisory_diff_percentage
,p_sf52_data.veterans_preference
,p_sf52_data.veterans_pref_for_rif
,p_sf52_data.veterans_status
,p_sf52_data.work_schedule
,p_sf52_data.year_degree_attained
);
Procedure Update_shadow_row ( p_shadow_data in ghr_pa_request_shadow%rowtype,
p_result out nocopy Boolean) is
Begin
Update ghr_pa_request_shadow
Set
pa_request_id = p_shadow_data.pa_request_id
,academic_discipline = p_shadow_data.academic_discipline
,annuitant_indicator = p_shadow_data.annuitant_indicator
,appropriation_code1 = p_shadow_data.appropriation_code1
,appropriation_code2 = p_shadow_data.appropriation_code2
,bargaining_unit_status = p_shadow_data.bargaining_unit_status
,citizenship = p_shadow_data.citizenship
,duty_station_id = p_shadow_data.duty_station_id
,duty_station_location_id = p_shadow_data.duty_station_location_id
,education_level = p_shadow_data.education_level
,fegli = p_shadow_data.fegli
,flsa_category = p_shadow_data.flsa_category
,forwarding_address_line1 = p_shadow_data.forwarding_address_line1
,forwarding_address_line2 = p_shadow_data.forwarding_address_line2
,forwarding_address_line3 = p_shadow_data.forwarding_address_line3
,forwarding_country_short_name = p_shadow_data.forwarding_country_short_name
,forwarding_postal_code = p_shadow_data.forwarding_postal_code
,forwarding_region_2 = p_shadow_data.forwarding_region_2
,forwarding_town_or_city = p_shadow_data.forwarding_town_or_city
,functional_class = p_shadow_data.functional_class
,part_time_hours = p_shadow_data.part_time_hours
,pay_rate_determinant = p_shadow_data.pay_rate_determinant
,position_occupied = p_shadow_data.position_occupied
,retirement_plan = p_shadow_data.retirement_plan
,service_comp_date = p_shadow_data.service_comp_date
,supervisory_status = p_shadow_data.supervisory_status
,tenure = p_shadow_data.tenure
,to_ap_premium_pay_indicator = p_shadow_data.to_ap_premium_pay_indicator
,to_auo_premium_pay_indicator = p_shadow_data.to_auo_premium_pay_indicator
,to_occ_code = p_shadow_data.to_occ_code
,to_position_id = p_shadow_data.to_position_id
,to_retention_allowance = p_shadow_data.to_retention_allowance
,to_retention_allow_percentage = p_shadow_data.to_retention_allow_percentage
,to_staffing_differential = p_shadow_data.to_staffing_differential
,to_staffing_diff_percentage = p_shadow_data.to_staffing_diff_percentage
,to_step_or_rate = p_shadow_data.to_step_or_rate
,to_supervisory_differential = p_shadow_data.to_supervisory_differential
,to_supervisory_diff_percentage = p_shadow_data.to_supervisory_diff_percentage
,veterans_preference = p_shadow_data.veterans_preference
,veterans_pref_for_rif = p_shadow_data.veterans_pref_for_rif
,veterans_status = p_shadow_data.veterans_status
,work_schedule = p_shadow_data.work_schedule
,year_degree_attained = p_shadow_data.year_degree_attained
where pa_request_id = p_shadow_data.pa_request_id;
hr_utility.set_location('Leaving Update_Shadow_row ' ,100);
end update_shadow_row;
select *
from ghr_pa_requests
where pa_request_id = l_pa_request_id;
select *
from ghr_pa_requests
where pa_request_id = p_sf52_data.pa_request_id;
select object_version_number
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
select parem.remark_id,
parem.description,
parem.remark_code_information1,
parem.remark_code_information2,
parem.remark_code_information3,
parem.remark_code_information4,
parem.remark_code_information5
from ghr_pa_remarks parem, ghr_remarks rem
where parem.pa_request_id = p_sf52_data.pa_request_id
and parem.remark_id = rem.remark_id
and substr(rem.code,1,1) = 'C';
SELECT prh.pa_routing_history_id,
prh.object_version_number
FROM ghr_pa_routing_history prh
WHERE prh.pa_request_id = l_sf52_dual_sec_rec.pa_request_id
ORDER by prh.pa_routing_history_id desc;
select *
from ghr_pa_routing_history prh
where prh.pa_request_id = p_sf52_data.pa_request_id
and ACTION_TAKEN = 'UPDATE_HR'
and APPROVAL_STATUS = 'APPROVE';
ghr_sf52_post_update.get_notification_details
(p_pa_request_id => l_sf52_dual_sec_rec.pa_request_id,
p_effective_date => l_sf52_dual_sec_rec.effective_date,
-- p_object_version_number => p_imm_pa_request_rec.object_version_number,
p_from_position_id => l_sf52_dual_sec_rec.from_position_id,
p_to_position_id => l_sf52_dual_sec_rec.to_position_id,
p_agency_code => l_sf52_dual_sec_rec.agency_code,
p_from_agency_code => l_sf52_dual_sec_rec.from_agency_code,
p_from_agency_desc => l_sf52_dual_sec_rec.from_agency_desc,
p_from_office_symbol => l_sf52_dual_sec_rec.from_office_symbol,
p_personnel_office_id => l_sf52_dual_sec_rec.personnel_office_id,
p_employee_dept_or_agency => l_sf52_dual_sec_rec.employee_dept_or_agency,
p_to_office_symbol => l_sf52_dual_sec_rec.to_office_symbol
);
GHR_APPROVED_PA_REQUESTS.Update_Dual_Id(p_parent_pa_request_id => p_pa_request_id,
p_first_dual_action_id => p_sf52_data.pa_request_id,
p_second_dual_action_id => l_pa_request_id);