[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
*
from ghr_pa_requests
where pa_request_id = c_pa_request_id;
Procedure delete_element_entry(
p_hist_rec in ghr_pa_history%rowtype,
p_del_mode in varchar2 default hr_api.g_delete_next_change,
p_cannot_cancel out nocopy Boolean) ;
Procedure delete_eleentval( p_hist_rec in ghr_pa_history%rowtype) ;
Procedure delete_peop_row(
p_person_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) ;
Procedure delete_asgn_row(
p_assignment_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) ;
Procedure delete_peopei_row( p_person_extra_info_id in varchar2) ;
Procedure delete_asgnei_row( p_assignment_extra_info_id in varchar2) ;
Procedure delete_posnei_row( p_position_extra_info_id in varchar2) ;
Procedure delete_address_row(p_address_id in varchar2) ;
Procedure delete_person_analyses_row ( p_person_analysis_id in number);
Procedure delete_appl_row(
p_table_name in varchar2,
p_table_pk_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) ;
Procedure delete_hist_row (
p_row_id in rowid);
Procedure delete_hist_row (
p_pa_history_id in ghr_pa_history.pa_history_id%type);
p_can_delete out nocopy boolean,
p_last_row out nocopy boolean,
p_cannot_cancel out nocopy boolean) ;
Procedure delete_other_pay_entries(p_hist_rec in ghr_pa_history%rowtype,
p_element_name in varchar2);
select
rowid row_id, table_name,pa_history_id,information5
,information9, information10
from ghr_pa_history
where pa_request_id = c_pa_request_id
and nature_of_action_id = c_noa_id
for update of person_id
order by pa_history_id desc; -- Bug# 1316321
select pa_history_id
from ghr_pa_history
where table_name = c_table_name
and information1 = c_pk_id
and pa_history_id > c_pa_history_id;
select *
from ghr_pa_history
where pa_request_id = c_pa_request_id
and table_name = c_table_name;
ghr_sf52_pre_update.populate_record_groups (
p_pa_request_rec => l_sf52_data,
p_generic_ei_rec => l_agency_ei_data,
p_imm_asg_sf52 => l_imm_asg_sf52,
p_imm_asg_non_sf52 => l_imm_asg_non_sf52,
p_imm_asg_nte_dates => l_imm_asg_nte_dates,
p_imm_per_sf52 => l_imm_per_sf52,
p_imm_per_group1 => l_imm_per_group1,
p_imm_per_group2 => l_imm_per_group2,
p_imm_per_scd_info => l_imm_per_scd_info,
p_imm_per_retained_grade => l_imm_per_retained_grade,
p_imm_per_probations => l_imm_per_probations,
p_imm_per_sep_retire => l_imm_per_sep_retire,
p_imm_per_security => l_imm_per_security,
--Bug#4486823 RRR Changes
p_imm_per_service_oblig => l_imm_per_service_oblig,
p_imm_per_conversions => l_imm_per_conversions,
-- 4352589 BEN_EIT Changes
p_imm_per_benefit_info => l_imm_per_benefit_info,
p_imm_per_uniformed_services => l_imm_per_uniformed_services,
p_imm_pos_oblig => l_imm_pos_oblig,
p_imm_pos_grp2 => l_imm_pos_grp2,
p_imm_pos_grp1 => l_imm_pos_grp1,
p_imm_pos_valid_grade => l_imm_pos_valid_grade,
p_imm_pos_car_prog => l_imm_pos_car_prog,
p_imm_loc_info => l_imm_loc_info,
p_imm_wgi => l_imm_wgi,
p_imm_gov_awards => l_imm_gov_awards,
p_imm_recruitment_bonus => l_imm_recruitment_bonus,
p_imm_relocation_bonus => l_imm_relocation_bonus,
p_imm_student_loan_repay => l_imm_student_loan_repay,
p_imm_per_race_ethnic_info => l_imm_per_race_ethnic_info, -- Bug 4724337 Race or National Origin changes
--Pradeep
p_imm_mddds_special_pay => l_imm_mddds_special_pay,
p_imm_premium_pay_ind => l_imm_premium_pay_ind,
p_imm_perf_appraisal => l_imm_perf_appraisal,
p_imm_conduct_performance => l_imm_conduct_performance,
p_imm_payroll_type => l_imm_payroll_type,
p_imm_extra_info_rec => l_imm_extra_info_rec,
p_imm_sf52_from_data => l_imm_sf52_from_data,
p_imm_personal_info => l_imm_personal_info,
p_imm_generic_extra_info_rec => l_imm_generic_extra_info_rec,
p_imm_agency_sf52 => l_imm_agency_sf52,
p_agency_code => l_agency_code,
p_imm_par_term_retained_grade => l_imm_par_term_retained_grade,
p_imm_entitlement => l_imm_entitlement,
-- Bug#2759379 Added FEGLI Record
p_imm_fegli => l_imm_fegli,
p_imm_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
-- Bug#3385386 Added FTA Record
p_imm_fta => l_imm_fta,
p_imm_edp_pay => l_imm_edp_pay,
p_imm_hazard_pay => l_imm_hazard_pay,
p_imm_health_benefits => l_imm_health_benefits,
p_imm_danger_pay => l_imm_danger_pay,
p_imm_imminent_danger_pay => l_imm_imminent_danger_pay,
p_imm_living_quarters_allow => l_imm_living_quarters_allow,
p_imm_post_diff_amt => l_imm_post_diff_amt,
p_imm_post_diff_percent => l_imm_post_diff_percent,
p_imm_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_imm_supplemental_post_allow => l_imm_supplemental_post_allow,
p_imm_temp_lodge_allow => l_imm_temp_lodge_allow,
p_imm_premium_pay => l_imm_premium_pay,
p_imm_retirement_annuity => l_imm_retirement_annuity,
p_imm_severance_pay => l_imm_severance_pay,
p_imm_thrift_saving_plan => l_imm_thrift_saving_plan,
p_imm_retention_allow_review => l_imm_retention_allow_review,
p_imm_health_ben_pre_tax => l_imm_health_ben_pre_tax,
--Bug# 6312144 RPA EIT Benefits
p_imm_ipa_benefits_cont => l_imm_ipa_benefits_cont,
p_imm_retirement_info => l_imm_retirement_info
);
-- Need to delete end dated position record only if it's MTO -- Sundar 2835138
IF (UPPER(SUBSTR(p_sf52_data.request_number,1,3)) = 'MTO') THEN
-- End 2835138
SELECT name, object_version_number
INTO l_pos_name, l_object_version_number
FROM hr_all_positions_f
WHERE position_id = p_sf52_data.from_position_id
AND (p_sf52_data.effective_date - 1) BETWEEN effective_start_date
AND effective_end_date;
hr_position_api.delete_position
(p_position_id => p_sf52_data.from_position_id,
p_object_version_number => l_object_version_number,
p_effective_date => p_sf52_data.effective_date - 1,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_datetrack_mode => 'DELETE_NEXT_CHANGE'
);
UPDATE per_positions
SET date_end = null
WHERE position_id = p_sf52_data.from_position_id;
SELECT *
INTO l_hist_rec
FROM ghr_pa_history
WHERE pa_request_id = p_sf52_data.altered_pa_request_id
AND information5 = 'GHR_US_PER_SEPARATE_RETIRE'
AND table_name = ghr_history_api.g_peopei_table
AND nature_of_action_id = (select nature_of_action_id from ghr_nature_of_actions where
code = '352');
-- only delete application table if there are no following records.
open c_follow_rec( c_table_name => l_hist_rec.table_name,
c_pk_id => l_hist_rec.information1,
c_pa_history_id => l_hist_rec.pa_history_id);
delete_appl_row(
p_table_name => ghr_history_api.g_peopei_table,
p_table_pk_id => l_hist_rec.information1,
p_dt_mode => null,
p_date_effective => l_hist_rec.effective_date);
delete_element_entry( p_hist_rec => l_hist_sevpay,
p_del_mode => hr_api.g_zap,
p_cannot_cancel => l_cannot_cancel_sevpay);
hr_utility.set_location('Delete rows.'|| l_proc,40);
delete_hist_row ( l_hist_sevpay.pa_history_id);
delete_eleentval( l_hist_sevpay);
-- delete all history records for the termination that is being cancelled.
-- if an address table record is encountered, then set application
-- table to what it currently should be according to history.
hr_utility.set_location( l_proc, 20);
SELECT *
INTO l_hist_rec
FROM ghr_pa_history
WHERE pa_history_id = l_hist.pa_history_id;
-- only delete application table if there are no following records.
open c_follow_rec( c_table_name => l_hist_rec.table_name,
c_pk_id => l_hist_rec.information1,
c_pa_history_id => l_hist_rec.pa_history_id);
delete_appl_row(
p_table_name => l_hist.table_name,
p_table_pk_id => l_hist_rec.information1,
p_dt_mode => null,
p_date_effective => l_hist_rec.effective_date);
delete_hist_row( l_hist.row_id);
hr_utility.set_location('Non 352 and delete_hist_row '||l_proc,27);
-- delete all history records for the termination that is being cancelled.
-- if an address table record is encountered, then set application
-- table to what it currently should be according to history.
hr_utility.set_location( l_proc, 20);
delete_hist_row( l_hist.row_id);
ghr_agency_update.ghr_agency_upd(
p_pa_request_rec => l_sf52_data,
p_asg_sf52 => l_imm_asg_sf52,
p_asg_non_sf52 => l_imm_asg_non_sf52,
p_asg_nte_dates => l_imm_asg_nte_dates,
p_per_sf52 => l_imm_per_sf52,
p_per_group1 => l_imm_per_group1,
p_per_group2 => l_imm_per_group2,
p_per_scd_info => l_imm_per_scd_info,
p_per_retained_grade => l_imm_per_retained_grade,
p_per_probations => l_imm_per_probations,
p_per_sep_Retire => l_imm_per_sep_retire,
p_per_security => l_imm_per_security,
p_per_conversions => l_imm_per_conversions,
p_per_uniformed_services => l_imm_per_uniformed_services,
p_pos_oblig => l_imm_pos_oblig,
p_pos_grp2 => l_imm_pos_grp2,
p_pos_grp1 => l_imm_pos_grp1,
p_pos_valid_grade => l_imm_pos_valid_grade,
p_pos_car_prog => l_imm_pos_car_prog,
p_loc_info => l_imm_loc_info,
p_wgi => l_imm_wgi,
p_recruitment_bonus => l_imm_recruitment_bonus,
p_relocation_bonus => l_imm_relocation_bonus ,
p_sf52_from_data => l_imm_sf52_from_data,
p_personal_info => l_imm_personal_info,
p_gov_awards_type => l_imm_gov_awards,
p_perf_appraisal_type => l_imm_perf_appraisal,
p_payroll_type => l_imm_payroll_type,
p_conduct_perf_type => l_imm_conduct_performance,
p_agency_code => l_agency_code,
p_agency_sf52 => l_imm_agency_sf52,
p_entitlement => l_imm_entitlement,
p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
p_edp_pay => l_imm_edp_pay,
p_hazard_pay => l_imm_hazard_pay,
p_health_benefits => l_imm_health_benefits,
p_danger_pay => l_imm_danger_pay,
p_imminent_danger_pay => l_imm_imminent_danger_pay,
p_living_quarters_allow => l_imm_living_quarters_allow,
p_post_diff_amt => l_imm_post_diff_amt,
p_post_diff_percent => l_imm_post_diff_percent,
p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_supplemental_post_allow => l_imm_supplemental_post_allow,
p_temp_lodge_allow => l_imm_temp_lodge_allow,
p_premium_pay => l_imm_premium_pay,
p_retirement_annuity => l_imm_retirement_annuity,
p_severance_pay => l_imm_severance_pay,
p_thrift_saving_plan => l_imm_thrift_saving_plan,
p_retention_allow_review => l_imm_retention_allow_review,
p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
p_per_benefit_info => l_imm_per_benefit_info,
p_imm_retirement_info => l_imm_retirement_info); --Bug# 7131104
ghr_sf52_post_update.post_sf52_cancel(
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);
select
business_group_id
from per_people_f
where person_id = c_person_id and
c_as_on_date between effective_start_date and effective_end_date;
-- the rows for update.
-- note that it selects all the records that were effective on or after the effective_date passed.
-- also note that it orders by dml_operation, in order to ensure that updated rows are handled
-- before inserted rows. This is necessary for the way we are handling the setting of extra info tables
-- back to what they were prior to the appointment. If updated rows were handled before inserted rows, then
-- the cascade_appl_table_data call may fail since it will be trying to fetch a pre-record for the
-- updated row, but the pre-record has been deleted since we handled inserted rows first.
cursor c_hist (c_person_id number,
c_eff_date date) is
select
pah.rowid row_id,
pah.*
from ghr_pa_history pah
where person_id = c_person_id and
(effective_date > c_eff_date or
(effective_date = c_eff_date and pa_request_id is not NULL))
for update of person_id
order by dml_operation desc;
-- the rows for update.
-- It returns all pa request rows that were effective on or after the effective date passed, as long as the
-- pa request has been approved.
cursor c_par ( c_person_id number,
c_eff_date date) is
select
par.rowid,
par.*
from ghr_pa_requests par
where person_id = c_person_id and
effective_date >= c_eff_date and
pa_notification_id is not null and
approval_date is not null
for update of person_id;
ghr_sf52_pre_update.populate_record_groups (
p_pa_request_rec => l_sf52_data,
p_generic_ei_rec => l_agency_ei_data,
p_imm_asg_sf52 => l_imm_asg_sf52,
p_imm_asg_non_sf52 => l_imm_asg_non_sf52,
p_imm_asg_nte_dates => l_imm_asg_nte_dates,
p_imm_per_sf52 => l_imm_per_sf52,
p_imm_per_group1 => l_imm_per_group1,
p_imm_per_group2 => l_imm_per_group2,
p_imm_per_scd_info => l_imm_per_scd_info,
p_imm_per_retained_grade => l_imm_per_retained_grade,
p_imm_per_probations => l_imm_per_probations,
p_imm_per_sep_retire => l_imm_per_sep_retire,
p_imm_per_security => l_imm_per_security,
--Bug#4486823 RRR Changes
p_imm_per_service_oblig => l_imm_per_service_oblig,
p_imm_per_conversions => l_imm_per_conversions,
-- 4352589 BEN_EIT Changes
p_imm_per_benefit_info => l_imm_per_benefit_info,
p_imm_per_uniformed_services => l_imm_per_uniformed_services,
p_imm_pos_oblig => l_imm_pos_oblig,
p_imm_pos_grp2 => l_imm_pos_grp2,
p_imm_pos_grp1 => l_imm_pos_grp1,
p_imm_pos_valid_grade => l_imm_pos_valid_grade,
p_imm_pos_car_prog => l_imm_pos_car_prog,
p_imm_loc_info => l_imm_loc_info,
p_imm_wgi => l_imm_wgi,
p_imm_gov_awards => l_imm_gov_awards,
p_imm_recruitment_bonus => l_imm_recruitment_bonus,
p_imm_relocation_bonus => l_imm_relocation_bonus,
p_imm_student_loan_repay => l_imm_student_loan_repay,
p_imm_per_race_ethnic_info => l_imm_per_race_ethnic_info, -- Bug 4724337 Race or National Origin changes
--Pradeep
p_imm_mddds_special_pay => l_imm_mddds_special_pay,
p_imm_premium_pay_ind => l_imm_premium_pay_ind,
p_imm_perf_appraisal => l_imm_perf_appraisal,
p_imm_conduct_performance => l_imm_conduct_performance,
p_imm_payroll_type => l_imm_payroll_type,
p_imm_extra_info_rec => l_imm_extra_info_rec,
p_imm_sf52_from_data => l_imm_sf52_from_data,
p_imm_personal_info => l_imm_personal_info,
p_imm_generic_extra_info_rec => l_imm_generic_extra_info_rec,
p_imm_agency_sf52 => l_imm_agency_sf52,
p_agency_code => l_agency_code,
p_imm_par_term_retained_grade => l_imm_par_term_retained_grade,
p_imm_entitlement => l_imm_entitlement,
-- Bug#2759379 Added FEGLI Record
p_imm_fegli => l_imm_fegli,
p_imm_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
-- Bug#3385386 Added FTA Record
p_imm_fta => l_imm_fta,
p_imm_edp_pay => l_imm_edp_pay,
p_imm_hazard_pay => l_imm_hazard_pay,
p_imm_health_benefits => l_imm_health_benefits,
p_imm_danger_pay => l_imm_danger_pay,
p_imm_imminent_danger_pay => l_imm_imminent_danger_pay,
p_imm_living_quarters_allow => l_imm_living_quarters_allow,
p_imm_post_diff_amt => l_imm_post_diff_amt,
p_imm_post_diff_percent => l_imm_post_diff_percent,
p_imm_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_imm_supplemental_post_allow => l_imm_supplemental_post_allow,
p_imm_temp_lodge_allow => l_imm_temp_lodge_allow,
p_imm_premium_pay => l_imm_premium_pay,
p_imm_retirement_annuity => l_imm_retirement_annuity,
p_imm_severance_pay => l_imm_severance_pay,
p_imm_thrift_saving_plan => l_imm_thrift_saving_plan,
p_imm_retention_allow_review => l_imm_retention_allow_review,
p_imm_health_ben_pre_tax => l_imm_health_ben_pre_tax,
--Bug #6312144 RPA EIT Benefits
p_imm_ipa_benefits_cont => l_imm_ipa_benefits_cont,
p_imm_retirement_info => l_imm_retirement_info);
delete_hist_row( l_hist.row_id);
-- Delete all extraInfo table records which were created by the SF52.
if upper(l_hist.table_name) in (upper(ghr_history_api.g_peopei_table),
upper(ghr_history_api.g_posnei_table),
upper(ghr_history_api.g_asgnei_table),
upper(ghr_history_api.g_addres_table),
upper(ghr_history_api.g_perana_table)) then
hr_utility.set_location( 'table_name qualifies: ' || l_hist.table_name|| l_proc, 57);
hr_utility.set_location( 'delete appl row table_name: ' || l_hist.table_name|| l_proc, 51);
hr_utility.set_location( 'delete appl row information1: ' || l_hist.information1|| l_proc, 52);
hr_utility.set_location( 'delete appl row effective_date: ' || l_hist.effective_date|| l_proc, 53);
delete_appl_row(
p_table_name => l_hist.table_name,
p_table_pk_id => l_hist.information1,
p_dt_mode => null,
p_date_effective => l_hist.effective_date);
,p_delete_routine => 'ASSIGNMENT' );
ghr_agency_update.ghr_agency_upd(
p_pa_request_rec => l_sf52_data,
p_asg_sf52 => l_imm_asg_sf52,
p_asg_non_sf52 => l_imm_asg_non_sf52,
p_asg_nte_dates => l_imm_asg_nte_dates,
p_per_sf52 => l_imm_per_sf52,
p_per_group1 => l_imm_per_group1,
p_per_group2 => l_imm_per_group2,
p_per_scd_info => l_imm_per_scd_info,
p_per_retained_grade => l_imm_per_retained_grade,
p_per_probations => l_imm_per_probations,
p_per_sep_Retire => l_imm_per_sep_retire,
p_per_security => l_imm_per_security,
p_per_conversions => l_imm_per_conversions,
p_per_uniformed_services => l_imm_per_uniformed_services,
p_pos_oblig => l_imm_pos_oblig,
p_pos_grp2 => l_imm_pos_grp2,
p_pos_grp1 => l_imm_pos_grp1,
p_pos_valid_grade => l_imm_pos_valid_grade,
p_pos_car_prog => l_imm_pos_car_prog,
p_loc_info => l_imm_loc_info,
p_wgi => l_imm_wgi,
p_recruitment_bonus => l_imm_recruitment_bonus,
p_relocation_bonus => l_imm_relocation_bonus ,
p_sf52_from_data => l_imm_sf52_from_data,
p_personal_info => l_imm_personal_info,
p_gov_awards_type => l_imm_gov_awards,
p_perf_appraisal_type => l_imm_perf_appraisal,
p_payroll_type => l_imm_payroll_type,
p_conduct_perf_type => l_imm_conduct_performance,
p_agency_code => l_agency_code,
p_agency_sf52 => l_imm_agency_sf52,
p_entitlement => l_imm_entitlement,
p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
p_edp_pay => l_imm_edp_pay,
p_hazard_pay => l_imm_hazard_pay,
p_health_benefits => l_imm_health_benefits,
p_danger_pay => l_imm_danger_pay,
p_imminent_danger_pay => l_imm_imminent_danger_pay,
p_living_quarters_allow => l_imm_living_quarters_allow,
p_post_diff_amt => l_imm_post_diff_amt,
p_post_diff_percent => l_imm_post_diff_percent,
p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_supplemental_post_allow => l_imm_supplemental_post_allow,
p_temp_lodge_allow => l_imm_temp_lodge_allow,
p_premium_pay => l_imm_premium_pay,
p_retirement_annuity => l_imm_retirement_annuity,
p_severance_pay => l_imm_severance_pay,
p_thrift_saving_plan => l_imm_thrift_saving_plan,
p_retention_allow_review => l_imm_retention_allow_review,
p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
p_per_benefit_info => l_imm_per_benefit_info,
p_imm_retirement_info => l_imm_retirement_info); --Bug# 7131104
ghr_sf52_post_update.post_sf52_cancel(
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);
-- mark delete
if l_par.first_noa_code not in ('001' , '002') then
-- no need to do anything with cancellation/correction actions as they
-- already will not appear in cancellation/correction form.
-- EDWARD NUNEZ 03/03/2000
-- NOTE: To fix bug# 1222525 please change following line
-- "if l_par.first_noa_cancel_or_correct is null then" to
-- "if l_par.first_noa_cancel_or_correct is null OR
-- NVL(l_par.first_noa_cancel_or_correct, '***') = 'CORRECT' then"
if l_par.first_noa_cancel_or_correct is null or
NVL(l_par.first_noa_cancel_or_correct, '***') = 'CORRECT'
then
l_par.first_noa_cancel_or_correct := ghr_history_api.g_cancel;
SELECT name, object_version_number
INTO l_pos_name, l_object_version_number
FROM hr_all_positions_f
WHERE position_id = l_par.from_position_id
AND (l_par.effective_date - 1) BETWEEN effective_start_date
AND effective_end_date;
hr_position_api.delete_position
(p_position_id => l_par.from_position_id,
p_object_version_number => l_object_version_number,
p_effective_date => l_par.effective_date - 1,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_datetrack_mode => 'DELETE_NEXT_CHANGE'
);
ghr_sf52_api.update_Sf52(
p_pa_request_id => l_par.pa_request_id,
p_par_object_version_number => l_par.object_version_number,
p_first_noa_cancel_or_correct => l_par.first_noa_cancel_or_correct,
p_second_noa_cancel_or_correct => l_par.second_noa_cancel_or_correct,
p_u_prh_object_version_number => l_u_prh_object_version_number,
p_i_pa_routing_history_id => l_i_pa_routing_history_id,
p_i_prh_object_version_number => l_i_prh_object_version_number,
p_u_action_taken => 'NONE'
);
l_can_delete boolean;
l_deleted boolean;
SELECT *
FROM GHR_PA_HISTORY
WHERE pa_request_id = cp_pa_request_id
AND nature_of_action_id = cp_noa_id
ORDER BY DECODE(table_name, 'PAY_ELEMENT_ENTRY_VALUES_F', -2,
'PAY_ELEMENT_ENTRIES_F', -1,
pa_history_id) asc;
SELECT 1
FROM PER_PERSON_ANALYSES
WHERE person_analysis_id = p_person_analysis_id;
Select paf.assignment_id
from per_assignments_f paf
where paf.person_id = p_sf52_data.person_id
and paf.assignment_type='E'
and p_sf52_data.effective_date-1 between paf.effective_start_date and paf.effective_end_date;
Select par.first_noa_code,
par.first_noa_id
from ghr_pa_requests par
where par.pa_request_id = p_sf52_data.altered_pa_request_id;
Select fam.noa_family_code
from ghr_noa_families nof,
ghr_families fam
where nof.nature_of_action_id = p_noa_id
and fam.noa_family_code = nof.noa_family_code
and nvl(fam.proc_method_flag,hr_api.g_varchar2) = 'Y'
and p_sf52_data.effective_date
between nvl(fam.start_date_active,p_sf52_data.effective_date)
and nvl(fam.end_date_active,p_sf52_data.effective_date);
select pa_history_id
from ghr_pa_history
where table_name = c_table_name
and information1 = c_pk_id
and pa_history_id > c_pa_history_id;
select
rowid row_id, table_name,pa_history_id,information5
,information9, information10
from ghr_pa_history
where pa_request_id = c_pa_request_id
and table_name = ghr_history_api.g_addres_table
and nature_of_action_id = c_noa_id
for update of person_id
order by pa_history_id desc;
Select distinct business_group_id bg
from per_assignments_f
where person_id = p_person_id
and p_eff_date between effective_start_date
and effective_end_date;
SELECT elt.element_name
FROM PAY_ELEMENT_LINKS_F ell,PAY_ELEMENT_TYPES_F elt
WHERE ell.ELEMENT_type_id = elt.element_type_id
AND ell.element_link_id = p_element_link_id;
SELECT elt.element_name
FROM PAY_ELEMENT_LINKS_F ell,
PAY_ELEMENT_TYPES_F elt,
pay_element_entries_f ele
WHERE ell.ELEMENT_type_id = elt.element_type_id
AND ele.element_type_id = elt.element_type_id
AND ell.element_link_id = ele.element_link_id
AND ele.element_entry_id = p_element_entry_id;
ghr_sf52_pre_update.populate_record_groups (
p_pa_request_rec => l_sf52_data,
p_generic_ei_rec => l_agency_ei_data,
p_imm_asg_sf52 => l_imm_asg_sf52,
p_imm_asg_non_sf52 => l_imm_asg_non_sf52,
p_imm_asg_nte_dates => l_imm_asg_nte_dates,
p_imm_per_sf52 => l_imm_per_sf52,
p_imm_per_group1 => l_imm_per_group1,
p_imm_per_group2 => l_imm_per_group2,
p_imm_per_scd_info => l_imm_per_scd_info,
p_imm_per_retained_grade => l_imm_per_retained_grade,
p_imm_per_probations => l_imm_per_probations,
p_imm_per_sep_retire => l_imm_per_sep_retire,
p_imm_per_security => l_imm_per_security,
--Bug#4486823 RRR Changes
p_imm_per_service_oblig => l_imm_per_service_oblig,
p_imm_per_conversions => l_imm_per_conversions,
-- 4352589 BEN_EIT Changes
p_imm_per_benefit_info => l_imm_per_benefit_info,
p_imm_per_uniformed_services => l_imm_per_uniformed_services,
p_imm_pos_oblig => l_imm_pos_oblig,
p_imm_pos_grp2 => l_imm_pos_grp2,
p_imm_pos_grp1 => l_imm_pos_grp1,
p_imm_pos_valid_grade => l_imm_pos_valid_grade,
p_imm_pos_car_prog => l_imm_pos_car_prog,
p_imm_loc_info => l_imm_loc_info,
p_imm_wgi => l_imm_wgi,
p_imm_gov_awards => l_imm_gov_awards,
p_imm_recruitment_bonus => l_imm_recruitment_bonus,
p_imm_relocation_bonus => l_imm_relocation_bonus,
p_imm_student_loan_repay => l_imm_student_loan_repay,
p_imm_per_race_ethnic_info => l_imm_per_race_ethnic_info, -- Bug 4724337 Race or National Origin changes
--Pradeep
p_imm_mddds_special_pay => l_imm_mddds_special_pay,
p_imm_premium_pay_ind => l_imm_premium_pay_ind,
p_imm_perf_appraisal => l_imm_perf_appraisal,
p_imm_conduct_performance => l_imm_conduct_performance,
p_imm_payroll_type => l_imm_payroll_type,
p_imm_extra_info_rec => l_imm_extra_info_rec,
p_imm_sf52_from_data => l_imm_sf52_from_data,
p_imm_personal_info => l_imm_personal_info,
p_imm_generic_extra_info_rec => l_imm_generic_extra_info_rec,
p_imm_agency_sf52 => l_imm_agency_sf52,
p_agency_code => l_agency_code,
p_imm_par_term_retained_grade => l_imm_par_term_retained_grade,
p_imm_entitlement => l_imm_entitlement,
-- Bug#2759379 Added FEGLI Record
p_imm_fegli => l_imm_fegli,
p_imm_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
-- Bug#3385386 Added FTA Record
p_imm_fta => l_imm_fta,
p_imm_edp_pay => l_imm_edp_pay,
p_imm_hazard_pay => l_imm_hazard_pay,
p_imm_health_benefits => l_imm_health_benefits,
p_imm_danger_pay => l_imm_danger_pay,
p_imm_imminent_danger_pay => l_imm_imminent_danger_pay,
p_imm_living_quarters_allow => l_imm_living_quarters_allow,
p_imm_post_diff_amt => l_imm_post_diff_amt,
p_imm_post_diff_percent => l_imm_post_diff_percent,
p_imm_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_imm_supplemental_post_allow => l_imm_supplemental_post_allow,
p_imm_temp_lodge_allow => l_imm_temp_lodge_allow,
p_imm_premium_pay => l_imm_premium_pay,
p_imm_retirement_annuity => l_imm_retirement_annuity,
p_imm_severance_pay => l_imm_severance_pay,
p_imm_thrift_saving_plan => l_imm_thrift_saving_plan,
p_imm_retention_allow_review => l_imm_retention_allow_review,
p_imm_health_ben_pre_tax => l_imm_health_ben_pre_tax,
--Bug# 6312144 RPA EIT Benefits
p_imm_ipa_benefits_cont => l_imm_ipa_benefits_cont,
p_imm_retirement_info => l_imm_retirement_info);
SELECT *
INTO l_hist_address_rec
FROM ghr_pa_history
WHERE pa_history_id = l_hist.pa_history_id;
delete_appl_row(
p_table_name => ghr_history_api.g_addres_table,
p_table_pk_id => l_hist_address_rec.information1,
p_dt_mode => null,
p_date_effective => l_hist_address_rec.effective_date);
delete_hist_row( l_hist.row_id);
-- Call Delete_element_entry;
-- delete all entry values from history;
-- To be able to delete this entry we have to pass previous record and
-- ask to delete next change.
-- If there is no pre-record then Call IF_ZAP_ELE_ENT to find if we need to zap
-- the entry.
l_result := FALSE;
l_del_mode := hr_api.g_delete_next_change;
delete_element_entry( p_hist_rec => l_hist_pre,
p_del_mode => l_del_mode,
p_cannot_cancel => l_cannot_cancel);
hr_utility.set_location('Delete rows.'|| l_proc,40);
delete_hist_row ( l_hist_rec.pa_history_id);
delete_eleentval( l_hist_rec);
delete_other_pay_entries(p_hist_rec => l_hist_rec,
p_element_name => l_element_name);
-- Delete history record
delete_hist_row ( l_hist_rec.pa_history_id);
hr_utility.set_location('Record was updated' || l_proc,48);
-- the same date and the update should not be applied to pay_element_entry_values_f table.
-- If it is the same, then there are no intervening actions on the same date and the
-- update should be applied.
ghr_history_fetch.get_date_eff_eleevl(p_element_entry_value_id => l_hist_pre.information1,
p_date_effective => l_hist_rec.effective_date,
p_element_entry_data => l_date_eff_rec,
p_result_code => l_result_code,
p_pa_history_id => l_pa_history_id);
-- update element_entry_value here.
-- update application table with pre-values
update_eleentval (p_hist_pre => l_hist_pre);
-- Delete history record
delete_hist_row ( l_hist_rec.pa_history_id);
-- Determine if this record was created or updated.
if l_datetrack_table then
hr_utility.set_location('In Cancel - Date track table' ,1);
-- if this sf50 updated the row, then the pre must have the same date.
-- The triggers work such that we will only have update from sf50 operation
-- when the effective_date is the same. If the pre has a different effective
-- date, then the original row that was created must have been deleted due to
-- a cancellation. In this case, the current row becomes the row that was created
-- by the sf52. So, we must set the rec_created_flag to true for this row. Note
-- this stands true with date tracked tables only as with non-date tracked tables there
-- is only one row in a record, so this case will never occur.
elsif l_hist_rec.DML_operation = ghr_history_api.g_upd_operation then
hr_utility.set_location('in Cancel - DML - Upd' ,1);
p_can_delete => l_can_delete,
p_last_row => l_last_row,
p_cannot_cancel => l_cannot_cancel);
-- delete row under conditions outlined below.
l_deleted := FALSE;
if (l_can_delete = TRUE) then
hr_utility.set_location('CAN delete'|| l_proc,60);
l_datetrack_mode := hr_api.g_delete_next_change;
-- only do the delete if this is a datetrack table or if it is the only row of a
-- non datetrack table. In all other cases, cascade will properly handle it.
if (l_datetrack_table = TRUE or (l_last_row = TRUE and l_pre_record = FALSE)) then
hr_utility.set_location('Deleting Application row'|| l_proc,80);
l_deleted := TRUE;
delete_appl_row( p_table_name => l_hist_rec.table_name,
p_table_pk_id => l_hist_rec.information1,
p_dt_mode => l_datetrack_mode,
p_date_effective => l_hist_rec.effective_date);
-- Delete history record
delete_hist_row ( l_hist_rec.pa_history_id);
-- cascade changes thru application table, if the application table record was not already deleted above.
if (l_deleted = FALSE)
or (l_datetrack_mode = hr_api.g_delete_next_change) then
if (l_deleted = FALSE) then
hr_utility.set_location('deleted is false : Bef cascade appl_table',1);
hr_utility.set_location('delete next change : Bef cascade appl_table',1);
-- Sundar Bug#2872298 Cascade should not occur if records are already deleted and
-- delete mode in DELETE_NEXT_CHANGE
IF (l_deleted = TRUE) AND (l_datetrack_mode = hr_api.g_delete_next_change) --AND (p_sf52_data.second_noa_code = '790')
THEN
NULL;
ghr_agency_update.ghr_agency_upd(
p_pa_request_rec => l_sf52_data,
p_asg_sf52 => l_imm_asg_sf52,
p_asg_non_sf52 => l_imm_asg_non_sf52,
p_asg_nte_dates => l_imm_asg_nte_dates,
p_per_sf52 => l_imm_per_sf52,
p_per_group1 => l_imm_per_group1,
p_per_group2 => l_imm_per_group2,
p_per_scd_info => l_imm_per_scd_info,
p_per_retained_grade => l_imm_per_retained_grade,
p_per_probations => l_imm_per_probations,
p_per_sep_Retire => l_imm_per_sep_retire,
p_per_security => l_imm_per_security,
p_per_conversions => l_imm_per_conversions,
p_per_uniformed_services => l_imm_per_uniformed_services,
p_pos_oblig => l_imm_pos_oblig,
p_pos_grp2 => l_imm_pos_grp2,
p_pos_grp1 => l_imm_pos_grp1,
p_pos_valid_grade => l_imm_pos_valid_grade,
p_pos_car_prog => l_imm_pos_car_prog,
p_loc_info => l_imm_loc_info,
p_wgi => l_imm_wgi,
p_recruitment_bonus => l_imm_recruitment_bonus,
p_relocation_bonus => l_imm_relocation_bonus ,
p_sf52_from_data => l_imm_sf52_from_data,
p_personal_info => l_imm_personal_info,
p_gov_awards_type => l_imm_gov_awards,
p_perf_appraisal_type => l_imm_perf_appraisal,
p_payroll_type => l_imm_payroll_type,
p_conduct_perf_type => l_imm_conduct_performance,
p_agency_code => l_agency_code,
p_agency_sf52 => l_imm_agency_sf52,
p_entitlement => l_imm_entitlement,
p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
p_edp_pay => l_imm_edp_pay,
p_hazard_pay => l_imm_hazard_pay,
p_health_benefits => l_imm_health_benefits,
p_danger_pay => l_imm_danger_pay,
p_imminent_danger_pay => l_imm_imminent_danger_pay,
p_living_quarters_allow => l_imm_living_quarters_allow,
p_post_diff_amt => l_imm_post_diff_amt,
p_post_diff_percent => l_imm_post_diff_percent,
p_sep_maintenance_allow => l_imm_sep_maintenance_allow,
p_supplemental_post_allow => l_imm_supplemental_post_allow,
p_temp_lodge_allow => l_imm_temp_lodge_allow,
p_premium_pay => l_imm_premium_pay,
p_retirement_annuity => l_imm_retirement_annuity,
p_severance_pay => l_imm_severance_pay,
p_thrift_saving_plan => l_imm_thrift_saving_plan,
p_retention_allow_review => l_imm_retention_allow_review,
p_health_ben_pre_tax => l_imm_health_ben_pre_tax,
p_per_benefit_info => l_imm_per_benefit_info,
p_imm_retirement_info => l_imm_retirement_info); --Bug# 7131104
ghr_sf52_post_update.post_sf52_cancel(
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);
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 *
from ghr_pa_requests
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = c_pa_request_id
order by level desc;
select
min(pa_history_id)
from ghr_pa_history
where pa_request_id = c_pa_request_id;
select *
from ghr_pa_request_shadow
where pa_request_id = c_pa_request_id;
select pa_history_id
from ghr_pa_history
where pa_request_id = p_sf52_rec.altered_pa_request_id and
nature_of_action_id = p_sf52_rec.second_noa_id;
ghr_process_sf52.update_rfrs_values(p_sf52_data => l_sf52_data,
p_shadow_data => l_shadow_data);
ghr_process_sf52.update_rfrs_values(p_sf52_data => l_root_sf52,
p_shadow_data => l_shadow_data);
hr_utility.set_location('national_identifier right before update sf52: '|| l_sf52_data_result.employee_national_identifier || l_proc, 915);
hr_utility.set_location('from_step_or_rate right before update sf52: '|| l_sf52_data_result.from_step_or_rate || l_proc, 915);
hr_utility.set_location('to_step_or_rate right before update sf52: '|| l_sf52_data_result.to_step_or_rate || l_proc, 915);
ghr_sf52_validn_pkg.prelim_req_chk_for_update_hr(p_pa_request_rec => l_sf52_data_result);
ghr_sf52_update.main( p_pa_request_rec => l_sf52_data_result,
p_pa_request_ei_rec => l_sf52_ei_data,
p_generic_ei_rec => l_agency_ei_data,
p_capped_other_pay => p_capped_other_pay);
ghr_sf52_update.main( p_pa_request_rec => l_sf52_data_result,
p_pa_request_ei_rec => l_sf52_ei_data,
p_generic_ei_rec => l_agency_ei_data,
p_capped_other_pay => p_capped_other_pay);
hr_utility.set_location('to_position_id right before update sf52: '|| l_sf52_data_result.to_position_id || l_proc, 915);
hr_utility.set_location('After main update :'|| l_proc, 20);
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 => l_sf52_data1.object_version_number,
p_from_position_id => l_sf52_data_result.from_position_id,
p_to_position_id => l_sf52_data_result.to_position_id,
p_agency_code => l_sf52_data_result.agency_code,
p_sf52_data_result => l_sf52_data_result,
p_called_from => 'CORRECTION_SF52'
);
Procedure delete_hist_row ( p_row_id in rowid) is
l_proc varchar2(30):='delete_hist_row';
delete ghr_pa_history
where rowid = p_row_id;
End delete_hist_row;
Procedure delete_hist_row ( p_pa_history_id in ghr_pa_history.pa_history_id%type) is
l_proc varchar2(30):='delete_hist_row';
delete ghr_pa_history
where pa_history_id = p_pa_history_id;
End delete_hist_row;
select *
from ghr_pa_requests
connect by prior altered_pa_request_id = pa_request_id
start with pa_request_id = p_sf52_data.pa_request_id
order by level desc;
select pa_request_id,pa_notification_id,person_id,
effective_date,from_position_id,
to_position_id
from ghr_pa_requests
where pa_request_id = p_sf52_data.altered_pa_request_id;
select
min(pa_history_id)
from ghr_pa_history
where pa_request_id = c_pa_request_id;
select gdf.segment1
,gdf.segment2
from per_grades grd,
per_grade_definitions gdf
where grd.grade_id = grd_id
and grd.grade_definition_id = gdf.grade_definition_id;
select 1
from ghr_pa_history hist_1
where pa_request_id is null
and hist_1.pa_history_id > (select min(pa_history_id)
from ghr_pa_history
where pa_request_id = l_prev_request_id)
and hist_1.pa_history_id < nvl(l_curr_pa_history_id,999999999)
and information1 in (select position_extra_info_id
from per_position_extra_info
where position_id = p_to_position_id
and information_type in ('GHR_US_POS_GRP1'))
and effective_date = p_effective_date
and table_name = 'PER_POSITION_EXTRA_INFO';
p_can_delete out nocopy boolean,
p_last_row out nocopy boolean,
p_cannot_cancel out nocopy boolean) IS
l_proc varchar2(72) := 'what_to_do?';
p_can_delete := FALSE;
-- there is no pre and no following records, so we CAN cancel and we CAN delete. And this is
-- the last row in history.
p_can_delete := TRUE;
p_can_delete := TRUE;
p_can_delete := TRUE;
p_can_delete := null;
Procedure delete_element_entry( p_hist_rec in ghr_pa_history%rowtype,
p_del_mode in varchar2 default hr_api.g_delete_next_change,
p_cannot_cancel out nocopy Boolean) is
l_del_warning boolean;
-- this cursor selects the element_entry_id and object_version_number from
-- pay_element_entries_f for the element_entry_id and date_effective passed.
cursor c_elmt ( cp_element_entry_id number,
cp_date_Effective date) is
select element_entry_id,
object_version_number
from pay_element_entries_f
where element_entry_id = cp_element_entry_id and
cp_date_effective between effective_start_date and effective_end_date;
l_proc varchar2(30):='delete_element_entry';
PY_element_entry_api.delete_element_entry(
p_datetrack_delete_mode => nvl(p_del_mode, hr_api.g_delete_next_change),
p_effective_date => to_date(p_hist_rec.information2, ghr_history_conv_rg.g_hist_date_format),
p_element_entry_id => l_c_elmt.element_entry_id,
p_object_version_number => l_c_elmt.object_version_number,
p_effective_start_date => l_eff_start_date,
p_effective_end_date => l_eff_end_date,
p_delete_warning => l_del_warning);
End delete_element_entry;
PROCEDURE update_eleentval( p_hist_pre in ghr_pa_history%rowtype) IS
-- this cursor selects the input value name given the input_value_id and effective date.
CURSOR c_input_value (cp_input_value_id in number
,cp_eff_date in date
,p_bg_id in NUMBER)
IS
SELECT IPV.NAME
FROM PAY_INPUT_VALUES_F IPV
WHERE TRUNC(cp_eff_date)
BETWEEN IPV.EFFECTIVE_START_DATE AND IPV.EFFECTIVE_END_DATE
AND IPV.INPUT_VALUE_ID = cp_input_value_id;
-- this cursor selects the element name given the element_entry_id and effective date.
CURSOR c_element_name( cp_element_entry_id in number
,cp_eff_date in date
,p_bg_id IN number)
IS
SELECT ELT.ELEMENT_NAME
FROM PAY_ELEMENT_TYPES_F ELT,
PAY_ELEMENT_LINKS_F ELL,
PAY_ELEMENT_ENTRIES_F ELE
WHERE TRUNC(cp_eff_date) BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND TRUNC(cp_eff_date) BETWEEN ELL.EFFECTIVE_START_DATE
AND ELL.EFFECTIVE_END_DATE
AND TRUNC(cp_eff_date) BETWEEN ELE.EFFECTIVE_START_DATE
AND ELE.EFFECTIVE_END_DATE
AND ELE.ELEMENT_ENTRY_ID = cp_element_entry_id
AND ELL.ELEMENT_LINK_ID = ELE.ELEMENT_LINK_ID
AND ELT.ELEMENT_TYPE_ID = ELL.ELEMENT_TYPE_ID
AND (ELT.BUSINESS_GROUP_ID is null OR ELT.BUSINESS_GROUP_ID = p_bg_id);
SELECT ASSIGNMENT_ID
FROM GHR_PA_HISTORY
WHERE INFORMATION1 = cp_information1
AND INFORMATION2 = cp_information2
AND INFORMATION3 = cp_information3
AND TABLE_NAME = cp_table_name
ORDER BY PROCESS_DATE DESC; -- Line Added by ENUNEZ (04/11/2000) bug# 1235958
l_proc varchar2(30):='update_eleentval';
select ipv.uom
from pay_element_types_f elt,
pay_input_values_f ipv
where trunc(eff_date) between elt.effective_start_date
and elt.effective_end_date
and trunc(eff_date) between ipv.effective_start_date
and ipv.effective_end_date
and elt.element_type_id = ipv.element_type_id
and upper(elt.element_name) = upper(ele_name)
and upper(ipv.name) = upper(input_name);
SELECT business_group_id bg
FROM per_assignments_f
WHERE person_id = p_person_id
AND p_eff_Date between effective_start_Date
AND effective_end_Date;
SELECT pcv_information1 ele_name
FROM pqp_configuration_Values
WHERE pcv_information_category='PQP_FEDHR_ELEMENT'
AND business_group_id is NULL and legislation_code='US'
AND upper(pcv_information1)=upper(p_ele_name);
/*To be included after Martin Reid's element api handles the create and update warning
if l_proc_warn = FALSE then
hr_utility.set_message(8301,'GHR_99999_FL_TO_UPD_ELEMENT_FOR_CANC');
END update_eleentval;
Procedure delete_eleentval( p_hist_rec in ghr_pa_history%rowtype) is
-- this cursor gets the rowid of the history_rec for the ghr_pa_history row passed
-- to us.
cursor c_hist is
select rowid row_id
from ghr_pa_history
where table_name = ghr_history_api.g_eleevl_table and
pa_request_id = p_hist_rec.pa_request_id and
nature_of_action_id = p_hist_rec.nature_of_action_id
for update of table_name;
l_proc varchar2(30):='delete_eleentval';
delete_hist_row( c_data.row_id);
end delete_eleentval;
PROCEDURE delete_peop_row( p_person_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) IS
l_proc varchar2(72) := 'delete_per_people_f_row';
SELECT object_version_number
FROM PER_PEOPLE_F
WHERE person_id = cp_person_id
AND cp_date_effective between effective_start_date and effective_end_date;
if (p_dt_mode = hr_api.g_delete_next_change) then
open c_get_ovn(p_person_id, p_date_effective -1);
-- can't delete without object_version_number.
close c_get_ovn;
-- can't delete without object_version_number.
close c_get_ovn;
END delete_peop_row;
PROCEDURE delete_posn_row( p_position_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) IS
l_proc varchar2(72) := 'delete_hr_all_positions_f_row';
SELECT object_version_number
FROM HR_ALL_POSITIONS_F
WHERE position_id = cp_position_id
AND cp_date_effective between effective_start_date and effective_end_date;
SELECT psf.position_definition_id
FROM hr_all_positions_f psf
WHERE position_id = c_position_id
AND effective_end_date = hr_api.g_eot
FOR UPDATE;
if (p_dt_mode = hr_api.g_delete_next_change) then
open c_get_ovn(p_position_id, p_date_effective -1);
-- can't delete without object_version_number.
close c_get_ovn;
-- can't delete without object_version_number.
close c_get_ovn;
END delete_posn_row;
PROCEDURE delete_asgn_row( p_assignment_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) IS
l_proc varchar2(72) := 'delete_per_assignments_f_row';
SELECT object_version_number
FROM PER_ASSIGNMENTS_F
WHERE assignment_id = cp_assignment_id
AND cp_date_effective between effective_start_date and effective_end_date;
Select ppp.pay_proposal_id proposal_id
,ppp.object_version_number ovn
from per_pay_proposals ppp
where ppp.assignment_id = p_assignment_id
and change_date <= p_eff_date;
if (p_dt_mode = hr_api.g_delete_next_change) then
open c_get_ovn(p_assignment_id, p_date_effective -1);
-- can't delete without object_version_number.
-- raise error;
-- can't delete without object_version_number.
-- raise error;
p_datetrack_mode => 'DELETE_NEXT_CHANGE',
p_org_now_no_manager_warning => l_org_now_no_manager_warning );
/* hr_maintain_proposal_api.delete_salary_proposal
(
p_pay_proposal_id => l_pay_proposal_id ,
p_business_group_id => l_business_group_id ,
p_object_version_number => l_sal_admin_ovn ,
p_validate => FALSE ,
p_salary_warning => l_sal_warn
); */
hr_entry_api.delete_element_entry
('DELETE_NEXT_CHANGE',
p_date_effective - 1,
l_ele_entry_id );
END delete_asgn_row;
PROCEDURE delete_peopei_row( p_person_extra_info_id in varchar2) IS
l_proc varchar2(72) := 'delete_per_people_extra_info_row';
SELECT object_version_number
FROM PER_PEOPLE_EXTRA_INFO
WHERE person_extra_info_id = cp_person_extra_info_id ;
-- can't do delete without object_version_number.
close c_get_ovn;
-- if record is not there means it has already been deleted thru core form.
-- hr_utility.set_message(8301,'GHR_38217_PEI_OVN_NOTFOUND');
END delete_peopei_row;
PROCEDURE delete_asgnei_row( p_assignment_extra_info_id in varchar2) IS
l_proc varchar2(72) := 'delete_per_assignment_extra_info_row';
SELECT object_version_number
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE assignment_extra_info_id = cp_assignment_extra_info_id ;
-- can't do delete without object_version_number.
close c_get_ovn;
-- if record is not there means it has already been deleted thru core form.
-- hr_utility.set_message(8301,'GHR_38218_ASGEI_OVN_NOTFOUND');
END delete_asgnei_row;
PROCEDURE delete_posnei_row( p_position_extra_info_id in varchar2) IS
l_proc varchar2(72) := 'delete_per_position_extra_info_row';
SELECT object_version_number
FROM PER_POSITION_EXTRA_INFO
WHERE position_extra_info_id = cp_position_extra_info_id ;
-- can't do delete without object_version_number.
close c_get_ovn;
-- if record is not there means it has already been deleted thru core form.
-- hr_utility.set_message(8301,'GHR_38218_ASGEI_OVN_NOTFOUND');
END delete_posnei_row;
PROCEDURE delete_address_row( p_address_id in varchar2) IS
l_proc varchar2(72) := 'delete_per_addresses_row';
SELECT object_version_number
FROM PER_ADDRESSES
WHERE address_id = cp_address_id ;
-- can't do delete without object_version_number.
close c_get_ovn;
-- if record is not there means it has already been deleted thru core form.
-- hr_utility.set_message(8301,'GHR_38220_ADDRESS_OVN_NOTFOUND');
END delete_address_row;
PROCEDURE delete_person_analyses_row ( p_person_analysis_id in number) is
l_proc varchar2(72) := 'delete_person_analyses_row';
SELECT object_version_number
FROM PER_PERSON_ANALYSES
WHERE person_analysis_id = cp_person_analysis_id ;
-- can't do delete without object_version_number.
-- raise error;
-- if record is not there means it has already been deleted thru core form.
-- hr_utility.set_message(8301,'GHR_38272_PERSON_ANALYSE_OV_NF');
End delete_person_analyses_row;
PROCEDURE delete_appl_row( p_table_name in varchar2,
p_table_pk_id in varchar2,
p_dt_mode in varchar2,
p_date_effective in date) IS
l_proc varchar2(72) := 'delete_appl_row';
delete_peop_row( p_person_id => p_table_pk_id,
p_dt_mode => p_dt_mode,
p_date_effective => p_date_effective);
delete_posn_row( p_position_id => p_table_pk_id,
p_dt_mode => p_dt_mode,
p_date_effective => p_date_effective);
delete_asgn_row( p_assignment_id => p_table_pk_id,
p_dt_mode => p_dt_mode,
p_date_effective => p_date_effective);
delete_peopei_row( p_person_extra_info_id => p_table_pk_id);
delete_asgnei_row( p_assignment_extra_info_id => p_table_pk_id);
delete_posnei_row( p_position_extra_info_id => p_table_pk_id);
delete_address_row( p_address_id => p_table_pk_id);
delete_person_analyses_row( p_person_analysis_id => p_table_pk_id);
END delete_appl_row;
select
pa_request_id,
altered_pa_request_id,
first_noa_id,
second_noa_id,
object_version_number ovn
from ghr_pa_requests
where pa_request_id = cp_pa_request_id;
-- this cursor selects all rows in the correction chain from ghr_pa_requests
cursor l_sf52_cursor is
select *
from ghr_pa_requests
connect by prior altered_pa_request_id = pa_request_id
start with pa_request_id = p_pa_request_id
order by level desc;
select *
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
select pa_request_id,pa_notification_id,person_id,
effective_date,from_position_id,
to_position_id
from ghr_pa_requests
where pa_request_id in (
select altered_pa_request_id from
ghr_pa_requests where pa_request_id = p_pa_request_id);
select gdf.segment1
,gdf.segment2
from per_grades grd,
per_grade_definitions gdf
where grd.grade_id = grd_id
and grd.grade_definition_id = gdf.grade_definition_id;
select fam.noa_family_code family_code
from ghr_noa_families fam
where fam.nature_of_action_id = c_noa_id;
SELECT *
INTO l_sf52_data
FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
select
pet.effective_start_date,
pet.effective_end_date,
pet.processing_type
from
pay_element_types_f pet, pay_element_links_f pel, pay_element_entries_f pee
where pee.element_entry_id = cp_element_entry_id and
pee.element_link_id = pel.element_link_id and
pet.element_type_id = pel.element_type_id and
cp_effective_start_date between pee.effective_start_date and pee.effective_end_date and
cp_effective_start_date between pet.effective_start_date and pet.effective_end_date and
cp_effective_start_date between pel.effective_start_date and pel.effective_end_date ;
select min(pa_history_id)
from ghr_pa_history
where pa_request_id = (select min(pa_request_id)
from ghr_pa_requests
start with pa_request_id = (select pa_request_id -- Bug# 1253981
from ghr_pa_history
where pa_history_id = cp_pa_history_id)
connect by prior altered_pa_request_id = pa_request_id)
-- connect by pa_request_id = altered_pa_request_id)
and nature_of_action_id = (select nature_of_action_id
from ghr_pa_history
where pa_history_id = cp_pa_history_id); */
select min(pa_history_id)
from ghr_pa_history
where pa_request_id = (select pa_request_id -- Bug# 1253981
from ghr_pa_history
where pa_history_id = cp_pa_history_id);
Select pa_history_id
from ghr_pa_history pah
where table_name = ghr_history_api.g_eleevl_table
and information5 = c_information1
and (effective_date > c_effective_date or
(effective_date = c_effective_date and
c_root_hist_id <
(select min(pa_history_id)
from ghr_pa_history
where pa_request_id =
(select min(pa_request_id)
from ghr_pa_requests
connect by prior altered_pa_request_id = pa_request_id
start with pa_request_id = pah.pa_request_id))));
select
pa_request_id
,object_version_number
,status
,rowid row_id
from ghr_pa_requests
where (level = 1 or ( level > 1 and
nvl(second_noa_cancel_or_correct, '@#$') <> ghr_history_api.g_cancel)) and
first_noa_code <> '001' and
nvl(status, '@!#') <> 'CANCELED'
start with pa_request_id = c_pa_request_id
connect by prior pa_request_id = altered_pa_request_id and
prior c_noa_id = second_noa_id;
elsif nvl(l_subsequent_pa_req(l_rev_count).status, 'CANCELED') <> 'UPDATE_HR_COMPLETE' then
-- SF52 has not been processed
-- Soft Cancel this SF52
-- No need to re-fetch object_version_number. As this must be last SF52 in the chain and
-- OVN must not change since last fetch
hr_utility.set_location('Soft Cancelling SF52 : ' ||
l_subsequent_pa_req(l_rev_count).pa_request_id || ' ' || l_proc , 90);
select
*
from ghr_pa_requests
where pa_request_id = c_pa_request_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 per_in_ler_id, ptnl_ler_for_per_stat_cd
from ben_per_in_ler pil,
ben_ler_f lf,
ben_ptnl_ler_for_per ptnl
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.lf_evt_ocrd_dt = p_effective_date
and pil.PER_IN_LER_STAT_CD IN ('PROCD','STRTD')
and lf.ler_id = pil.ler_id
and pil.ptnl_ler_for_per_id = ptnl.ptnl_ler_for_per_id
and name <> 'Unrestricted'
and p_effective_date between lf.effective_start_date
and lf.effective_end_date;
select per_in_ler_id,PER_IN_LER_STAT_CD
from ben_per_in_ler pil,ben_ler_f lf
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.lf_evt_ocrd_dt = p_effective_date
and PER_IN_LER_STAT_CD IN ('PROCD','STRTD')
and lf.ler_id = pil.ler_id
and name = 'Unrestricted'
and p_effective_date between lf.effective_start_date
and lf.effective_end_date;
select 1
from ghr_pa_requests par
where person_id = p_person_id
and effective_date <= p_effective_date
and first_noa_code not in ('100')
and pa_notification_id is not null
and not exists (select 1
from ghr_pa_requests b
where person_id = p_person_id
and altered_pa_request_id = par.pa_request_id
and first_noa_code = '001'
and second_noa_code = par.first_noa_code);
select business_group_id bg_id
from per_all_people_f
where person_id = c_person_id
and c_effective_date between effective_start_date
and effective_end_date;
select pgm.pgm_id
from ben_pgm_f pgm
where pgm.name = c_prog_name
and pgm.business_group_id = c_business_group_id
and c_effective_date between effective_start_date and effective_end_date;
select prtt_enrt_rslt_id,
object_version_number,
effective_start_date,
enrt_cvg_thru_dt
from ben_prtt_enrt_rslt_f
where person_id = p_person_id
and pgm_id = p_pgm_id
and per_in_ler_id = p_per_in_ler_id
and p_effective_date between effective_start_date and effective_end_date
and enrt_cvg_strt_dt >= p_effective_date;
hr_utility.set_location('Hiring Status - Calling Delete API',45);
hr_position_api.delete_position(p_validate => FALSE
,p_position_id => to_number(l_sf52_data.from_position_id)
,p_effective_date => l_posn_eff_start_date-1
,p_effective_start_date => l_posn_eff_start_date
,p_effective_end_date => l_posn_eff_end_date
,p_object_version_number => l_prior_posn_ovn
,p_datetrack_mode => 'DELETE_NEXT_CHANGE');
l_datetrack_mode := hr_api.g_delete_next_change;
ben_prtt_enrt_result_api.delete_enrollment
(p_validate => false
,p_per_in_ler_id => rec_le.per_in_ler_id
,p_prtt_enrt_rslt_id => enrt_rec.prtt_enrt_rslt_id
,p_business_group_id => l_bg_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => enrt_rec.effective_start_date
,p_datetrack_mode => hr_api.g_zap
,p_enrt_cvg_thru_dt => enrt_rec.enrt_cvg_thru_dt
,p_multi_row_validate => FALSE);
-- -- user defined families not supported by update to database
-- -- should generate a message and raise_error here.
-- hr_utility.set_location( l_proc, 80);
select employee_assignment_id
from ghr_pa_requests
where pa_request_id in
( select altered_pa_request_id from
ghr_pa_requests where
pa_request_id = p_corr_sf52_detail.pa_request_id);
-- Call update_sf52
-- This is a recursive call as Process_SF52 is called from ghr_sf52_api.update_sf52
hr_utility.set_location(' BEGIN Update_SF52 for pa_request_id : ' ||
l_sf52_canc.pa_request_id || ' ' || l_proc , 110);
ghr_sf52_api.update_sf52(
p_pa_request_id => l_sf52_canc.pa_request_id
,p_employee_assignment_id => l_asg_id
-- OUT parameters
,p_par_object_version_number => l_sf52_canc.object_version_number
,p_u_prh_object_version_number=> l_dummy_number
,p_i_pa_routing_history_id => l_dummy_number
,p_i_prh_object_version_number=> l_dummy_number
,p_u_action_taken => 'UPDATE_HR'
,p_u_approval_status => 'APPROVE'
);
hr_utility.set_location(' END Update_SF52 for pa_request_id : ' ||
l_sf52_canc.pa_request_id || ' ' || l_proc , 120);
-- Delete GHR_Routing_History
delete from ghr_pa_routing_history
where pa_request_id = l_sf52_canc.pa_request_id;
delete ghr_pa_request_ei_shadow
where pa_request_id = l_sf52_canc.pa_request_id;
-- Delete Cancelation SF50 Shadow row
hr_utility.set_location(' Deleting Shadow ' || l_sf52_canc.pa_request_id || ' ' || l_proc , 130);
Delete from ghr_pa_request_shadow
where pa_request_id = l_sf52_canc.pa_request_id;
delete ghr_pa_request_extra_info
where pa_request_id = l_sf52_canc.pa_request_id;
-- Delete pa_reques row
l_sf52_canc.object_version_number := get_sf52_ovn( l_sf52_canc.pa_request_id);
Procedure delete_other_pay_entries(p_hist_rec in ghr_pa_history%rowtype,
p_element_name IN VARCHAR2 ) IS
l_history_exists BOOLEAN := FALSE;
SELECT *
FROM ghr_pa_history
WHERE table_name = 'PAY_ELEMENT_ENTRIES_F'
AND information1 = p_hist_rec.information1
AND information2 = p_hist_rec.information2
AND nvl(pa_request_id,0) <> nvl(p_hist_rec.pa_request_id,0);
SELECT min(effective_date) effective_date
FROM ghr_pa_requests
where noa_family_code = 'OTHER_PAY'
and pa_notification_id is not null
and effective_date > p_effective_date
and status = 'UPDATE_HR_COMPLETE'
and employee_assignment_id = p_assignment_id
and NVL(first_noa_cancel_or_correct,'C') <> 'CANCEL';
hr_utility.set_location('Entering Delete Oth Pay Entr '||p_element_name,0);
-- Delete the other pay element from this effective date to the future
-- other pay action (effective date - 1).
-- Else
-- Delete the Other Pay element with this element entry ID.
-- End If;
-- DELETE THE RECORD FROM PAY_ELEMENT_ENTRY_VALUES_F;
DELETE pay_element_entry_values_f
where element_entry_id = p_hist_rec.information1
AND effective_start_date BETWEEN fnd_date.canonical_to_date(p_hist_rec.information2)
AND (l_future_othpay_effective_date - 1) ;
-- DELETE THE RECORD FROM PAY_ELEMENT_ENTRIES_F;
DELETE PAY_ELEMENT_ENTRIES_F
where element_entry_id = p_hist_rec.information1
AND effective_start_date BETWEEN fnd_date.canonical_to_date(p_hist_rec.information2)
AND (l_future_othpay_effective_date - 1) ;
-- DELETE THE HISTORY RECORDS with the same element entry ID created after the current
-- action and prior to the next other pay action.
DELETE ghr_pa_history
WHERE (information5 = p_hist_rec.information1
OR information1 = p_hist_rec.information1)
AND effective_date BETWEEN (fnd_date.canonical_to_date(p_hist_rec.information2) + 1)
AND (l_future_othpay_effective_date - 1);
-- DELETE THE RECORD FROM PAY_ELEMENT_ENTRY_VALUES_F;
DELETE pay_element_entry_values_f
where element_entry_id = p_hist_rec.information1;
-- DELETE THE RECORD FROM PAY_ELEMENT_ENTRIES_F;
DELETE PAY_ELEMENT_ENTRIES_F
where element_entry_id = p_hist_rec.information1;
-- DELETE THE HISTORY RECORDS with the same element entry ID created after the
-- current other pay action.
DELETE ghr_pa_history
WHERE (information5 = p_hist_rec.information1 OR information1 = p_hist_rec.information1)
AND effective_date > (fnd_date.canonical_to_date(p_hist_rec.information2) + 1);
-- DELETE THE RECORD FROM PAY_ELEMENT_ENTRY_VALUES_F;
DELETE pay_element_entry_values_f
where element_entry_id = p_hist_rec.information1;
DELETE PAY_ELEMENT_ENTRIES_F
where element_entry_id = p_hist_rec.information1;
hr_utility.set_location('Leaving delete_other_pay_entries',170);
DELETE pay_element_entry_values_f
where element_entry_id = p_hist_rec.information1
AND effective_start_date = fnd_date.canonical_to_date(p_hist_rec.information2);
DELETE PAY_ELEMENT_ENTRIES_F
where element_entry_id = p_hist_rec.information1
AND effective_start_date = fnd_date.canonical_to_date(p_hist_rec.information2);
hr_utility.set_location('Leaving delete_other_pay_entries',70);
hr_utility.set_location('Leaving delete_other_pay_entries',90);
END delete_other_pay_entries;
select
object_version_number
from ghr_pa_requests
where pa_request_id = c_pa_request_id;
SELECT * FROM ghr_pa_requests
WHERE pa_request_id = p_pa_request_id;
SELECT grade_id
FROM per_grades
WHERE business_group_id = c_business_group_id
AND substr(name,1,2) = c_pay_plan
AND substr(name,length(name)-1) = c_grade
AND c_effective_date BETWEEN NVL(date_from,to_date('01/01/1951','dd/mm/yyyy')) AND NVL(date_to,to_date('31/12/4712','dd/mm/yyyy'));
SELECT '1'
FROM pay_element_types_f elt
,pay_element_entries_f ele
WHERE elt.element_type_id = ele.element_type_id
AND elt.element_name= p_element_name
AND ele.assignment_id = p_assignment_id
AND elt.business_group_id is NULL
AND p_effective_date BETWEEN elt.effective_start_date AND elt.effective_end_date
AND p_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date;
select fam.noa_family_code
from ghr_noa_families fam
where fam.nature_of_action_id = p_noa_id;
select apf.effective_start_date, apf.effective_end_date
from HR_ALL_POSITIONS_F apf
where apf.position_id = p_position_id
and apf.availability_status_id <> 1
and p_effective_date between apf.effective_start_date and apf.effective_end_date;
select apf.object_version_number
from HR_ALL_POSITIONS_F apf
where apf.position_id = p_position_id
and p_posn_eff_start_date-1 between apf.effective_start_date and apf.effective_end_date;