[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM GHR_PA_REMARKS parem,
GHR_REMARKS grem
WHERE parem.remark_id = grem.remark_id
AND grem.code = p_remark_code
AND parem.pa_request_id = p_pa_request_id;
SELECT *
FROM GHR_REMARKS
WHERE code = p_remark_code
and p_effective_date between date_from and nvl(date_to,p_effective_date);
select 1
from ghr_pay_plans
where pay_plan = p_to_pay_plan
and equivalent_pay_plan = 'GS';
select SUBSTR(user_table_name,0,4)
from pay_user_tables
where user_table_id = p_to_pay_table_identifier;
select to_pay_plan, to_grade_or_level
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
select first_action_la_code1,first_action_la_code2
,second_action_la_code1,second_action_la_code2,
first_noa_code,
second_noa_code
from ghr_pa_requests
where pa_request_id = p_pa_request_id;
l_retained_grade_info := ghr_sf52_do_update.g_retained_grade_info;
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_remark.description,
p_information1 => l_retained_grade,
p_information2 => TO_CHAR(l_retained_grade_info.date_to,'DD-MON-RRRR'),
p_information3 => NULL,
p_information4 => NULL,
p_information5 => NULL,
p_desc_out => l_description
);
ghr_mass_actions_pkg.replace_insertion_values
(p_desc => l_remark.description,
p_information1 => l_user_table_name,
p_information2 => NULL,
p_information3 => NULL,
p_information4 => NULL,
p_information5 => NULL,
p_desc_out => l_description
);
procedure Process_Immediate_Update
( p_imm_pa_request_rec in out nocopy ghr_pa_requests%rowtype,
p_imm_pa_request_ei_rec in ghr_pa_request_extra_info%rowtype,
p_imm_generic_ei_rec in ghr_pa_request_extra_info%rowtype,
p_capped_other_pay in out nocopy number
)
is
l_session ghr_history_api.g_session_var_type;
l_proc varchar2(70) := 'Process_Immediate_Update';
l_employee_api_update_criteria varchar2(1);
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 ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment,
eef.element_entry_id ,
eef.object_version_number
FROM pay_element_entries_f eef,
pay_element_types_f elt
WHERE assignment_id = p_asg_id
AND elt.element_type_id = eef.element_type_id
AND eef.effective_start_date BETWEEN elt.effective_start_date AND
elt.effective_end_date
AND p_effective_date between eef.effective_start_date and eef.effective_end_date
AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
p_business_group_id,
p_effective_date))
IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX');
l_delete_warning boolean;
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 1
FROM ben_prtt_enrt_rslt_f
WHERE person_id = c_person_id
AND pgm_id = c_pgm_id
AND prtt_enrt_rslt_stat_cd IS NULL
AND c_effective_date between effective_start_date and effective_end_date;
SELECT gdf.segment1 pay_plan,
gdf.segment2 grade_or_level
FROM per_grade_definitions gdf,
per_grades grd
WHERE grd.grade_id = l_imm_pos_valid_grade.valid_grade
AND grd.grade_definition_id = gdf.grade_definition_id
AND grd.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID');
SELECT cin.value basic_pay
FROM pay_user_column_instances_f cin
,pay_user_rows_f urw
,pay_user_columns col
WHERE col.user_table_id = l_imm_pos_valid_grade.pay_table_id
AND urw.user_table_id = col.user_table_id
AND urw.row_low_range_or_name = l_pay_plan||'-'||l_grade_or_level
AND NVL(p_imm_pa_request_rec.effective_date,TRUNC(SYSDATE)) BETWEEN urw.effective_start_date AND urw.effective_end_date
AND cin.user_row_id = urw.user_row_id
AND cin.user_column_id = col.user_column_id
AND NVL(p_imm_pa_request_rec.effective_date,TRUNC(SYSDATE)) BETWEEN cin.effective_start_date AND cin.effective_end_date;
select noa_family_code,
second_noa_id,
second_action_la_code1,
second_action_la_code2
from ghr_pa_requests
where pa_request_id = l_session.pa_request_id
and second_noa_code is not null
and first_noa_code not in ('001','002')
and first_noa_id = p_imm_pa_request_rec.first_noa_id;
select noa_family_code
from ghr_pa_requests
where pa_request_id = l_session.pa_request_id
and second_noa_code is not null
and first_noa_code not in ('001','002')
and second_noa_id = p_imm_pa_request_rec.first_noa_id;
select mass_action_id
from ghr_pa_requests
where pa_request_id = l_session.pa_request_id
and first_noa_code = '002'
and rpa_type = 'DUAL'
and mass_action_id is not null;
select pa_request_id,first_noa_code,second_noa_code,
first_noa_id,second_noa_id,second_action_la_code1,second_action_la_code2,
noa_family_code
from ghr_pa_requests
where pa_request_id = (select min(pa_request_id)
from ghr_pa_requests
where pa_notification_id is not null
connect by pa_request_id = prior altered_pa_request_id
start with pa_request_id = l_session.altered_pa_request_id)
and second_noa_code is not null;
Select fam.noa_family_code
from ghr_noa_families nof,
ghr_families fam
where nof.nature_of_action_id = p_second_noa_id
and fam.noa_family_code = nof.noa_family_code
and nvl(fam.proc_method_flag,hr_api.g_varchar2) = 'Y'
and p_imm_pa_request_rec.effective_date
between nvl(fam.start_date_active,p_imm_pa_request_rec.effective_date)
and nvl(fam.end_date_active,p_imm_pa_request_rec.effective_date);
ghr_sf52_post_update.get_notification_details
(p_pa_request_id => p_imm_pa_request_rec.pa_request_id,
p_effective_date => p_imm_pa_request_rec.effective_date,
-- p_object_version_number => p_imm_pa_request_rec.object_version_number,
p_from_position_id => p_imm_pa_request_rec.from_position_id,
p_to_position_id => p_imm_pa_request_rec.to_position_id,
p_agency_code => p_imm_pa_request_rec.agency_code,
p_from_agency_code => p_imm_pa_request_rec.from_agency_code,
p_from_agency_desc => p_imm_pa_request_rec.from_agency_desc,
p_from_office_symbol => p_imm_pa_request_rec.from_office_symbol,
p_personnel_office_id => p_imm_pa_request_rec.personnel_office_id,
p_employee_dept_or_agency => p_imm_pa_request_rec.employee_dept_or_agency,
p_to_office_symbol => p_imm_pa_request_rec.to_office_symbol
);
ghr_sf52_pre_update.populate_record_groups
(
p_pa_request_rec => p_imm_pa_request_rec,
p_generic_ei_rec => p_imm_generic_ei_rec,
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_asg_detail_info => l_imm_asg_detail_info,--Bug# 8653508
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,
--Pradeep
p_imm_per_race_ethnic_info => l_imm_per_race_ethnic_info, -- Race and National Origin changes
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 parameter p_imm_fegli
p_imm_fegli => l_imm_fegli,
p_imm_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay,
-- Bug#3385386 Added parameter p_imm_fta
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 added new parameters to the procedure for RPA -- EIT Benefits
p_imm_ipa_benefits_cont => l_imm_ipa_benefits_cont,
p_imm_retirement_info => l_imm_retirement_info,
--BUG # 13571004 added TSP Roth
p_imm_tsp_roth => l_imm_roth_tsp
);
GHR_SF52_DO_UPDATE.Process_Family
(P_Pa_request_Rec => p_imm_pa_request_rec,
P_agency_code => l_agency_code );
GHR_SF52_DO_UPDATE.call_extra_info_api
(P_PA_REQUEST_REC => p_imm_PA_REQUEST_REC
,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_asg_detail_info => l_imm_asg_detail_info --Bug# 8653508
, 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
--Bug#4486823 RRR Changes
, P_Per_service_oblig => l_imm_Per_service_oblig
, P_Per_conversions => l_imm_Per_conversions
-- 4352589 BEN_EIT Changes
, p_per_benefit_info => l_imm_per_benefit_info
, 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_perf_appraisal => l_imm_perf_appraisal
, p_conduct_performance => l_imm_conduct_performance
, P_Loc_Info => l_imm_Loc_Info
, P_generic_Extra_Info_Rec => l_imm_generic_Extra_Info_Rec
, P_par_term_retained_grade => l_imm_par_term_retained_grade
, p_per_race_ethnic_info => l_imm_per_race_ethnic_info -- Bug 4724337 Race or National Origin changes
, p_ipa_benefits_cont => l_imm_ipa_benefits_cont --Bug # 6312144 RPA - EIT Benefits
, p_retirement_info => l_imm_retirement_info
);
GHR_SF52_DO_UPDATE.Process_salary_Info
(p_pa_request_rec => p_imm_pa_request_rec
,p_wgi => l_imm_wgi
,p_retention_allow_review => l_imm_retention_allow_review
,p_capped_other_pay => p_capped_other_pay );
GHR_SF52_DO_UPDATE.Process_non_salary_Info
(p_pa_request_rec => p_imm_pa_request_rec
,p_recruitment_bonus => l_imm_recruitment_bonus
,p_relocation_bonus => l_imm_relocation_bonus
,p_student_loan_repay => l_imm_student_loan_repay
--Pradeep
,p_mddds_special_pay => l_imm_mddds_special_pay
,p_premium_pay_ind => l_imm_premium_pay_ind
,p_gov_award => l_imm_gov_awards
,p_entitlement => l_imm_entitlement
-- Bug#2759379 Added parameter p_fegli
,p_fegli => l_imm_fegli
,p_foreign_lang_prof_pay => l_imm_foreign_lang_prof_pay
-- Bug#3385386 Added parameter p_fta
,p_fta => l_imm_fta
,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_health_ben_pre_tax => l_imm_health_ben_pre_tax
--Bug # 13571004
,p_tsp_roth => l_imm_roth_tsp
);
ghr_agency_update.ghr_agency_upd
(p_pa_request_rec => p_imm_pa_request_rec,
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
--Begin Bug# 10181661
p_asg_detail_info => l_imm_asg_detail_info,
p_ipa_detail_ben_cont_info => l_imm_ipa_benefits_cont
--End Bug# 10181661
);
ghr_history_api.post_update_process;
End Process_Immediate_Update;
select pa_notification_id
from ghr_pa_requests par
where par.pa_request_id = p_pa_request_rec.pa_request_id;
select date_end
from hr_all_positions_f pos -- Venkat -- Position DT
where pos.position_id = l_position_id
and p_pa_request_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
Process_Immediate_Update
(p_imm_pa_request_rec => l_pa_request_rec,
p_imm_pa_request_ei_rec => p_pa_request_ei_rec,
p_imm_generic_ei_rec => p_generic_ei_rec,
p_capped_other_pay => l_capped_other_pay
);
end GHR_SF52_UPDATE;