[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete Boolean;
l_delete_next_change Boolean;
,p_delete => l_delete
,p_future_change => l_future_change
,p_delete_next_change => l_delete_next_change
);
elsif l_delete then
Return 'DELETE';
elsif l_delete_next_change then
Return 'DELETE_NEXT_CHANGE';
L_UPDATE Boolean;
L_UPDATE_OVERRIDE Boolean;
L_UPDATE_CHANGE_INSERT Boolean;
,P_UPDATE => L_UPDATE
,P_UPDATE_OVERRIDE => L_UPDATE_OVERRIDE
,P_UPDATE_CHANGE_INSERT => L_UPDATE_CHANGE_INSERT);
If L_Update Then
Return 'UPDATE';
Elsif L_UPDATE_CHANGE_INSERT then
return 'UPDATE_CHANGE_INSERT';
Elsif L_Update_Override Then
Return 'UPDATE_OVERRIDE';
l_Update_Salary_cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
l_Mass_update_Call Varchar2(1) := 'N';
Select Full_name, Last_NAME, Person.Person_Id, Asgt.Grade_id, Asgt.Grade_Ladder_Pgm_Id
from Per_All_Assignments_F Asgt,
PER_ASSIGNMENT_STATUS_TYPES Pas,
Per_All_People_F Person
Where Assignment_Id = P_Assignment_Id
and P_Effective_Date
Between Asgt.Effective_Start_Date
and Asgt.Effective_End_Date
and Person.Person_Id = Asgt.Person_Id
and asgt.assignment_type ='E'
and asgt.primary_flag ='Y'
and asgt.ASSIGNMENT_STATUS_TYPE_ID = Pas.ASSIGNMENT_STATUS_TYPE_ID
and pas.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
and P_Effective_Date
Between Person.Effective_Start_Date
and Person.Effective_End_Date;
Select Dflt_Step_cd, Update_Salary_Cd
from Ben_Pgm_F
Where Pgm_id = l_Pgm_Id
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select Pgm.Pgm_Id
From Ben_Pgm_F Pgm,
Ben_Plip_F Plip,
Ben_Pl_F Plan
where Pgm.Dflt_Pgm_Flag = 'Y'
and Pgm.Pgm_Typ_Cd = 'GSP'
and P_effective_date
Between Pgm.Effective_Start_Date
and Pgm.Effective_End_Date
and Pgm.Business_Group_id = hr_general.get_Business_group_Id
and Plan.Mapping_Table_name = 'PER_GRADES'
and Plan.Mapping_Table_Pk_id = l_grade_Id
and P_Effective_Date
Between Plan.Effective_Start_Date
and Plan.Effective_End_Date
and Plan.Pl_Id = Plip.Pl_Id
and Pgm.Pgm_Id = Plip.Pgm_id
and P_Effective_Date
between Plip.Effective_Start_Date
and Plip.Effective_End_Date;
Select Plcmt.Step_id
From Per_Spinal_POint_Placements_f Plcmt,
Per_Spinal_point_Steps_F Step
Where Plcmt.Assignment_id = P_Assignment_Id
and P_Effective_Date
Between Plcmt.Effective_Start_Date
and plcmt.Effective_End_Date
and Step.Step_id = Plcmt.Step_Id
and P_Effective_Date
Between Step.Effective_Start_Date
and Step.Effective_End_Date;
Select max(pil.Per_in_Ler_Id)
From Ben_Per_in_ler PIL,
Ben_Ler_F LER
Where Pil.Ler_Id = LER.Ler_Id
And Pil.LF_EVT_OCRD_DT = P_Effective_Date
And ler.typ_Cd = 'GSP'
And Pil.person_Id = l_person_id
And Pil.Per_In_Ler_Stat_Cd = 'PROCD';
Select Rate.Rt_Strt_Dt Sal_Chg_Dt
From Ben_Elig_Per_Elctbl_Chc Elct,
Ben_Enrt_Rt Rate
Where Elct.DFLT_FLAG = 'Y'
and Elct.Elctbl_Flag = 'Y'
and Elct.Per_in_ler_id = l_Last_Per_In_ler_Id
and Elct.Enrt_Cvg_Strt_Dt is Not NULL
And Rate.ELIG_PER_ELCTBL_CHC_ID(+) = Elct.ELIG_PER_ELCTBL_CHC_ID;
Select 'X'
from Ben_Per_in_ler Pler,
Ben_Ler_F Ler
where Person_id = l_Person_Id
and Per_In_ler_Stat_Cd = 'STRTD'
and Pler.Ler_Id = Ler.ler_ID
and Ler.Typ_Cd = 'GSP'
and P_Effective_Date
Between Ler.Effective_Start_Date
and Ler.Effective_End_Date;
Fetch Dflt_Step into l_Dflt_Step_Cd, L_Update_Salary_Cd;
l_Mass_Update_Call := 'N';
l_Mass_Update_Call := 'Y';
If L_Update_Salary_Cd <> 'NO_UPDATE' and l_Elig_per_Elctbl_Chc_Id is NOT NULL Then
pqh_gsp_Post_Process.Update_Salary_Info
(P_Elig_per_Elctbl_Chc_Id => l_Elig_per_Elctbl_Chc_Id
,P_Effective_Date => P_Effective_Date
,P_Dt_Mode => P_DATE_TRACK_MODE
,P_Called_From => 'A'
,P_Prv_Sal_Chg_Dt => l_Lst_Rt_Chg_Dt);
hr_utility.set_location(' Completed Salary Update ', 40);
If l_Mass_Update_call = 'N' then
/* Not Called from Mass Update and hence logging the process Completion */
Pqh_Gsp_process_Log.Log_process_Dtls
(P_Master_txn_Id => P_Assignment_Id
,P_Txn_Id => P_Assignment_Id
,p_module_cd => 'PQH_GSP_ASSIGN_ENTL'
,p_message_type_cd => 'C'
,p_message_text => NULL
,P_Effective_Date => P_Effective_Date);
if l_Mass_Update_Call = 'N' Then
Pqh_Gsp_process_Log.Log_process_Dtls
(P_Master_txn_Id => P_Assignment_Id
,P_Txn_Id => P_Assignment_Id
,p_module_cd => 'PQH_GSP_ASSIGN_ENTL'
,p_message_type_cd => 'E'
,p_message_text => Nvl(fnd_Message.Get,sqlerrm)
,P_Effective_Date => P_Effective_Date);
Pqh_Process_Batch_log.Insert_log
(P_Message_Type_Cd => Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','ERR')
,P_Message_text => Nvl(fnd_message.get,Sqlerrm));
Select Pler.Per_In_Ler_Id, Pler.Object_Version_Number, Elct.Pgm_Id
From Ben_Elig_per_Elctbl_Chc Elct,
Ben_Per_In_Ler Pler
Where Elct.Elig_per_Elctbl_Chc_Id = P_Elig_per_Elctbl_Chc_Id
and Pler.Per_In_Ler_Id = Elct.Per_In_Ler_Id;
Select Update_Salary_Cd
from Ben_Pgm_F
where Pgm_Id = P_Pgm_Id
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
l_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
Fetch Pgm_Dtl into L_UPdate_Salary_Cd;
/* Update the Assignment Record with the new grade , Step */
pqh_gsp_Post_Process.Update_Assgmt_Info
(P_Elig_Per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
,P_Effective_Date => P_Effective_Date);
hr_utility.set_location(' Completed Asgt Update ', 30);
/* Update the Salary */
If Nvl(l_Update_Salary_Cd,'NO_UPDATE') <> 'NO_UPDATE' Then
pqh_gsp_Post_Process.Update_Salary_Info
(P_Elig_per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
,P_Effective_Date => P_Effective_Date);
hr_utility.set_location(' Completed Sal Update ', 40);
Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
(P_PER_IN_LER_ID => l_PER_IN_LER_ID
,P_PER_IN_LER_STAT_CD => 'PROCD'
,P_PROCD_DT => L_PROCD_DT
,P_STRTD_DT => L_STRTD_DT
,P_VOIDD_DT => L_VOIDD_DT
,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
,P_EFFECTIVE_DATE => P_Effective_Date);
hr_utility.set_location(' Completed PIL Update ', 20);
,p_person_selection_rule_id IN Number Default Null) Is
l_Concurrent_Req_Id Number(18);
Select Paa.Assignment_Id, Paa.Grade_ladder_Pgm_Id, Paa.Grade_Id, Paa.Person_Id,
paa.soft_coding_keyflex_id,paa.people_group_id,paa.special_ceiling_step_id,paa.object_version_number
from Per_All_Assignments_F Paa
Where L_Effective_Date Between paa.Effective_Start_Date and Paa.Effective_End_Date
and Paa.Business_group_id = hr_General.get_Business_group_id
and Paa.Person_id = Nvl(P_Person_Id, Paa.Person_Id)
and paa.assignment_type ='E'
and paa.primary_flag ='Y'
and Paa.Grade_ladder_Pgm_Id is null
and Paa.grade_id in
(select pl.mapping_table_pk_id
from ben_plip_f plip,ben_pl_f pl
where plip.pgm_id = p_grade_ladder_id
and plip.pl_id = pl.pl_id
and L_Effective_Date between
plip.effective_start_date and plip.effective_end_date
and l_effective_date between
pl.effective_start_date and pl.effective_end_date
and pl.mapping_table_pk_id = nvl(p_grade_id,pl.mapping_table_pk_id));
l_spp_delete_warning Boolean;
Select Count(*) into L_Cnt
from Ben_Per_in_ler Pler,
Ben_Ler_F Ler
where Person_id = Asgt_Rec.Person_Id
and Per_In_ler_Stat_Cd = 'STRTD'
and Pler.Ler_Id = Ler.ler_ID
and Ler.Typ_Cd = 'GSP'
and L_Effective_Date
Between Ler.Effective_Start_Date
and Ler.Effective_End_Date;
if p_person_selection_rule_id is not null then
l_outputs:=benutils.formula(
p_formula_id => p_person_selection_rule_id
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_assignment_id => l_assignment_id);
IF l_date_track_mode = 'CORRECTION' OR l_date_track_mode = 'UPDATE' THEN
Hr_Assignment_Api.Update_Emp_Asg_Criteria
(p_effective_date => L_Effective_Date
,p_datetrack_update_mode => L_Date_Track_Mode
,p_assignment_id => L_Assignment_id
,p_grade_ladder_pgm_id => P_Grade_Ladder_Id
,p_object_version_number => L_Assignment_Ovn
,p_special_ceiling_step_id => L_special_ceiling_step_id
,p_people_group_id => L_People_Group_Id
,p_soft_coding_keyflex_id => L_Soft_Coding_Keyflex_Id
,p_group_name => L_group_name
,p_effective_start_date => L_Asg_effective_start_date
,p_effective_end_date => L_Asg_effective_end_date
,p_org_now_no_manager_warning => L_org_now_no_manager_warning
,p_other_manager_warning => L_other_manager_warning
,p_spp_delete_warning => L_spp_delete_warning
,p_entries_changed_warning => L_entries_changed_warning
,p_tax_district_changed_warning => L_tax_district_changed_warning
,p_concatenated_segments => L_concatenated_segments);
Select Ler_id
from Ben_Ler_F
Where Typ_Cd = 'GSP'
and LF_EVT_OPER_CD = 'PROG' --ggnanagu 4032221
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date
and Business_Group_id = P_Business_Group_id;
Select Asgt.Person_Id, Asgt.Business_Group_id,
Asgt.Grade_Id , Asgt.pay_basis_id, Asgt.GRADE_LADDER_PGM_ID, Asgt.Effective_Start_Date
from Per_All_Assignments_f Asgt
Where Assignment_Id = P_Assignment_Id
and P_Effective_Date
Between Asgt.Effective_Start_Date
and Asgt.Effective_End_Date;
Select Pl.Pl_Id, plip.Plip_Id, Pgm.DFLT_STEP_CD,
Pgm.UPDATE_SALARY_CD , DFLT_ELEMENT_TYPE_ID
from Ben_Pgm_F Pgm,
Ben_Pl_F Pl,
Ben_Plip_f Plip
Where Pgm.Pgm_Id = P_Pgm_Id
and P_Effective_Date
Between Pgm.Effective_Start_Date
and Pgm.Effective_End_Date
and Pl.Mapping_table_Name = 'PER_GRADES'
and Pl.Mapping_Table_Pk_Id = P_Grd_Id
and P_Effective_Date
Between Pl.Effective_Start_Date
and Pl.Effective_End_Date
and Plip.Pgm_Id = Pgm.Pgm_id
and Plip.Pl_id = Pl.Pl_Id
and P_Effective_Date
Between Plip.Effective_Start_Date
and Plip.Effective_End_Date;
Select Plcmt.Step_id, Step.Spinal_point_id, Plcmt.Effective_Start_Date
From Per_Spinal_POint_Placements_f Plcmt,
Per_Spinal_point_Steps_F Step
Where Plcmt.Assignment_id = P_Assignment_Id
and P_Effective_Date
Between Plcmt.Effective_Start_Date
and plcmt.Effective_End_Date
and Step.Step_id = Plcmt.Step_Id
and P_Effective_Date
Between Step.Effective_Start_Date
and Step.Effective_End_Date;
Select Oipl.Oipl_Id, Opt.Opt_Id
From Ben_Opt_F Opt,
Ben_Oipl_F Oipl
Where Opt.Mapping_table_name = 'PER_SPINAL_POINTS'
and Opt.mapping_table_Pk_Id = P_Point_id
and P_Effective_Date
Between Opt.Effective_Start_Date
and Opt.Effective_End_Date
and Oipl.Pl_Id = P_Pl_Id
and Oipl.Opt_id = Opt.Opt_id
and P_Effective_Date
Between Oipl.Effective_Start_Date
and Oipl.Effective_End_Date;
Select Pl_typ_Id
From Ben_Pl_Typ_F
Where Opt_typ_Cd = 'GSP'
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
select 'Y'
from pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where Pet.Element_type_id = P_Element_type_Id
and p_Effective_date
Between pet.effective_start_date
and pet.effective_end_date
and Pel.Element_type_Id = Pet.Element_type_Id
and p_Effective_date
Between pel.effective_start_date
and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and p_Effective_date
between pee.effective_start_date
and pee.effective_end_date;
Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
From Ben_Acty_base_Rt_f
where Pl_id = l_Pl_Id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
From Ben_Acty_Base_rt_f
where Opt_Id = L_Opt_id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select Lrsn.Lee_Rsn_Id
From Ben_Popl_Enrt_Typ_Cycl_F Cylc,
Ben_Lee_Rsn_f Lrsn
Where Cylc.ENRT_TYP_CYCL_CD = 'L'
and Cylc.Pgm_Id = P_Pgm_Id
and P_Effective_Date
Between Cylc.Effective_Start_Date
and Cylc.Effective_End_Date
and Lrsn.POPL_ENRT_TYP_CYCL_ID = Cylc.POPL_ENRT_TYP_CYCL_ID
and P_Effective_Date
Between Lrsn.Effective_Start_Date
and Lrsn.Effective_End_Date;
Select Pl_Id, Oipl_Id
from Ben_Elig_per_Elctbl_Chc
Where Pgm_Id = P_Pgm_Id
and Dflt_Flag = 'Y'
and Elctbl_Flag = 'Y'
and Per_In_ler_Id = (Select Max(Per_In_ler_Id)
from Ben_per_in_Ler
Where Ler_id = P_Ler_Id
and Person_Id = P_Person_Id
and LF_EVT_OCRD_DT = ( SELECT max(LF_EVT_OCRD_DT) FROM Ben_per_in_Ler
where Ler_id = P_Ler_Id
and Person_Id = P_Person_Id
and PER_IN_LER_STAT_CD = 'PROCD'
and LF_EVT_OCRD_DT <= p_effective_date)
and PER_IN_LER_STAT_CD = 'PROCD'); -- Query changed for Bug 6519699
select max(LF_EVT_OCRD_DT)
from Ben_per_in_Ler
Where Person_Id = P_Person_Id
and Ler_id = P_Ler_Id;
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and assignment_type ='E'
and primary_flag ='Y'
and p_max_lf_evt_date
between effective_start_date and effective_end_date;
SELECT pyp.yr_perd_id,
pyp.popl_yr_perd_id,
yp.start_date,
yp.end_date
FROM ben_popl_yr_perd pyp,
ben_yr_perd yp
WHERE pyp.pl_id = P_Pl_Id
AND pyp.yr_perd_id = yp.yr_perd_id
AND pyp.business_group_id = P_Bg_Id
AND P_Effective_Date
BETWEEN yp.start_date AND yp.end_date
AND yp.business_group_id = P_Bg_Id; */
Select Yr_Perd_id
From Ben_Yr_Perd
Where P_Effective_Date
Between Start_Date and End_Date;
Select Pgm_Id
From Ben_PGM_F
Where DFLT_PGM_FLAG = 'Y'
and Pgm_Typ_Cd = 'GSP'
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date
and Business_Group_id = Hr_general.get_business_group_id;
L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
Fetch Ben_Grd_Dtls into L_Pl_id, l_plip_Id , l_Prog_style, L_Update_Salary_Cd, l_Dflt_Element_typ_Id;
If L_Update_Salary_Cd = 'SALARY_BASIS' and L_pay_basis_id is NULL Then
/* Grade Ladder is defined for Salary basis and Pay Basis is not attached to the assignment */
fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
ElsIf L_Update_Salary_Cd = 'SALARY_ELEMENT' then
/* Grade Ladder uses Salary Element, but Default Salary Element type is not defined */
If l_Dflt_Element_typ_Id is NULL Then
fnd_message.set_name('PQH','PQH_GSP_DFLT_ELMNT_NOTDFND');
If L_Update_Salary_Cd is NULL Then
fnd_message.set_name('PQH','PQH_GSP_POSTSTYL_NOT_SET');
hr_utility.set_location('L_Update_Salary_Cd :' || L_Update_Salary_Cd, 180);
If L_Update_Salary_Cd in ('SALARY_BASIS','SALARY_ELEMENT') Then
/* Not Batch Mode and Salary Update is Set for the Grade ladder */
if g_debug then
hr_utility.set_location(' Determine Rates ', 190);
Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
(P_PER_IN_LER_ID => l_PER_IN_LER_ID
,P_PER_IN_LER_STAT_CD => 'PROCD'
,P_PROCD_DT => L_PROCD_DT
,P_STRTD_DT => L_STRTD_DT
,P_VOIDD_DT => L_VOIDD_DT
,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
,P_EFFECTIVE_DATE => l_New_Enrlmt_Dt);
Select Elctbl.PRTT_ENRT_RSLT_ID, Elctbl.Enrt_Cvg_Strt_Dt , Elctbl.Crntly_Enrd_Flag, Rate.ENRT_RT_ID, Rate.ENRT_BNFT_ID,
Rate.Val , Elctbl.Business_Group_Id, Elctbl.Per_in_Ler_Id , Rate.ANN_VAL
From Ben_Elig_Per_Elctbl_Chc Elctbl,
Ben_Enrt_Rt Rate
Where Elctbl.ELIG_PER_ELCTBL_CHC_ID = P_ELIG_PER_ELCTBL_CHC_ID
and Rate.ELIG_PER_ELCTBL_CHC_ID = Elctbl.ELIG_PER_ELCTBL_CHC_ID;
l_Datetrack_Mode := hr_api.g_Insert;
l_Datetrack_Mode := hr_api.g_update;
L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
Select Update_Salary_Cd, DFLT_INPUT_VALUE_ID, DFLT_ELEMENT_TYPE_ID, DFLT_STEP_CD
From ben_Pgm_F
Where Pgm_id = P_Pgm_Id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
select pee.Element_Entry_Id, pee.Object_version_Number
from pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where Pet.Element_type_id = L_DFLT_ELEMENT_TYPE_ID
and p_Effective_date
Between pet.effective_start_date
and pet.effective_end_date
and Pel.Element_type_Id = Pet.Element_type_Id
and p_Effective_date
Between pel.effective_start_date
and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and p_Effective_date
between pee.effective_start_date
and pee.effective_end_date;
Select pil.Per_in_Ler_Id, pil.person_id
From Ben_Per_in_ler PIL, Ben_Elig_Per_Elctbl_Chc Enrt
Where Enrt.Elig_per_Elctbl_Chc_id = P_Elig_per_Elctbl_Chc_Id
And Enrt.Per_In_Ler_id = Pil.Per_In_Ler_id;
Select max(pil.Per_in_Ler_Id)
From Ben_Per_in_ler PIL,
Ben_Ler_F LER
Where Pil.Ler_Id = LER.Ler_Id
And ler.typ_Cd = 'GSP'
And Pil.person_Id = l_person_id
And Pil.Per_In_Ler_Stat_Cd = 'PROCD'
And pil.Per_in_Ler_Id <> l_curr_per_in_ler_id;
Select Elct.Pgm_id, Rate.Rt_Strt_Dt Sal_Chg_Dt,Rate.Val Val
From Ben_Elig_Per_Elctbl_Chc Elct,
Ben_Enrt_Rt Rate
Where Elct.DFLT_FLAG = 'Y'
and Elct.Elctbl_Flag = 'Y'
and Elct.Per_in_ler_id = l_Last_Per_In_ler_Id
and Elct.Enrt_Cvg_Strt_Dt is Not NULL
And Elct.ELIG_PER_ELCTBL_CHC_ID = Rate.ELIG_PER_ELCTBL_CHC_ID(+);
Select max(Rate.Rt_Strt_Dt) Sal_Chg_Dt
From Ben_Elig_Per_Elctbl_Chc Elct,
Ben_Enrt_Rt Rate
Where Elct.DFLT_FLAG = 'Y'
and Elct.Elctbl_Flag = 'Y'
and Elct.Per_in_ler_id in (Select pil.Per_in_Ler_Id
From Ben_Per_in_ler PIL,
Ben_Ler_F LER
Where Pil.Ler_Id = LER.Ler_Id
And ler.typ_Cd = 'GSP'
And Pil.person_Id = l_person_id
And Pil.Per_In_Ler_Stat_Cd = 'PROCD'
And pil.Per_in_Ler_Id <> l_curr_per_in_ler_id)
and Elct.Enrt_Cvg_Strt_Dt is Not NULL
And Elct.ELIG_PER_ELCTBL_CHC_ID = Rate.ELIG_PER_ELCTBL_CHC_ID(+);
Select Pay_Proposal_Id, Object_Version_Number, nvl(date_to,to_date('31-12-4712','dd-mm-yyyy')) -- bug 6856664
From Per_Pay_Proposals
Where Change_Date = P_Change_Dt
and Assignment_id = P_Assignment_id;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and l_prev_sal_change_dt between
effective_start_date and effective_end_date;
Fetch Pgm_Dtl into L_Update_Salary_Cd, L_DFLT_INPUT_VALUE_ID, L_DFLT_ELEMENT_TYPE_ID, l_Dflt_Step_Cd;
If L_Update_Salary_Cd = 'SALARY_ELEMENT' AND
l_prev_sal is not null Then
--
hr_utility.set_location('Sal Element used to pay previously',5);
hr_entry_api.delete_element_entry
('ZAP'
,p_current_rate_change_dt
,l_element_entry_id);
hr_entry_api.delete_element_entry
('DELETE'
,p_current_rate_change_dt - 1
,l_element_entry_id);
hr_entry_api.delete_element_entry
(L_DATE_TRACK_MODE
,p_current_rate_change_dt
,l_element_entry_id);
ElsIf L_Update_Salary_Cd = 'SALARY_BASIS' AND
l_prev_sal is not null Then
--
hr_utility.set_location('Sal Basis used to pay previously',5);
Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Del_proposal_Id
,P_BUSINESS_GROUP_ID => p_business_Group_Id
,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
,P_SALARY_WARNING => l_Del_Warn);
hr_entry_api.delete_element_entry
('ZAP'
,p_current_rate_change_dt - 1
,l_element_entry_id);
hr_maintain_proposal_api.update_salary_proposal(
p_validate => false,
p_pay_proposal_id => l_Del_proposal_Id,
p_date_to => p_current_rate_change_dt -1,
p_object_version_number => l_Del_Proposal_Ovn,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning);
hr_entry_api.delete_element_entry
('DELETE'
,p_current_rate_change_dt - 1
,l_element_entry_id);
End if; -- L_Update_Salary_Cd = 'SALARY_ELEMENT'
/************************** Update Salary Info. ***************************/
/**************************************************************************/
Procedure Update_Salary_Info
(P_Elig_per_Elctbl_Chc_Id IN Number
,P_Effective_Date IN Date
,P_Dt_Mode IN Varchar2
,P_Called_From IN Varchar2
,P_Prv_Sal_Chg_Dt IN Date) Is
--
/* Cursor Enroll_Info is
Select Rate.rt_Val , Rate.Rt_Strt_Dt, Rate.Prtt_Rt_Val_Id,
Rate.pk_Id , Rate.Object_Version_Number, Enrt.Pgm_Id,
Asgt.Assignment_Id, Asgt.pay_basis_id, Enrt.Business_Group_id
From Ben_PRTT_ENRT_RSLT_F Enrt,
ben_prtt_rt_val Rate,
Per_All_Assignments_F Asgt
Where Enrt.PRTT_ENRT_RSLT_ID = P_PRTT_ENRT_RSLT_ID
and P_Effective_Date
between Enrt.Effective_Start_Date
and Enrt.Effective_End_Date
and Enrt.Prtt_Enrt_Rslt_Id = Rate.Prtt_Enrt_Rslt_Id
And Asgt.Person_id = Enrt.Person_id
And Asgt.PRIMARY_FLAG = 'Y'
And Asgt.assignment_type = 'E'
And P_Effective_Date
Between Asgt.Effective_start_Date and Asgt.Effective_end_Date; */
L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
Select Rate.Val , Rate.Rt_Strt_Dt, Rate.Prtt_Rt_Val_Id,
Rate.Object_Version_Number, Enrt.Pgm_Id, Enrt.OiPl_Id,
Asgt.Assignment_Id, Asgt.pay_basis_id, Asgt.Grade_Id, Enrt.Business_Group_id
From Ben_ELig_per_Elctbl_Chc Enrt,
ben_Enrt_Rt Rate,
Ben_Per_in_ler PIL,
Per_All_Assignments_F Asgt
Where Enrt.Elig_per_Elctbl_Chc_id = P_Elig_per_Elctbl_Chc_Id
And Enrt.Per_In_Ler_id = Pil.Per_In_Ler_id
And Asgt.Person_id = PIL.Person_id
And P_Effective_Date
Between Asgt.Effective_start_Date and Asgt.Effective_end_Date
and Enrt.Elig_per_Elctbl_Chc_id = Rate.Elig_per_Elctbl_Chc_id(+)
and asgt.assignment_type ='E'
And Asgt.PRIMARY_FLAG = 'Y';
Select ele.element_entry_id
from per_pay_bases bas,
pay_element_entries_f ele,
pay_element_entry_values_f entval
where bas.pay_basis_id = P_pay_basis_id
and entval.input_value_id = bas.input_value_id
and p_effective_date
between entval.effective_start_date
and entval.effective_end_date
and ele.assignment_id = P_assignmnet_id
and p_effective_date between ele.effective_start_date
and ele.effective_end_date
and ele.element_entry_id = entval.element_entry_id;
Select Update_Salary_Cd, DFLT_INPUT_VALUE_ID, DFLT_ELEMENT_TYPE_ID, DFLT_STEP_CD
From ben_Pgm_F
Where Pgm_id = P_Pgm_Id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
select pee.Element_Entry_Id, pee.Object_version_Number
from pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where Pet.Element_type_id = L_DFLT_ELEMENT_TYPE_ID
and p_Effective_date
Between pet.effective_start_date
and pet.effective_end_date
and Pel.Element_type_Id = Pet.Element_type_Id
and p_Effective_date
Between pel.effective_start_date
and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and p_Effective_date
between pee.effective_start_date
and pee.effective_end_date;
Select Max(Change_Date)
from Per_Pay_Proposals
Where Assignment_Id = P_Assignment_id
AND p_Effective_date BETWEEN Change_Date AND
nvl(DATE_TO,to_date('31-12-4712','dd-mm-yyyy')) ;-- added for bug 6880958
Select Pay_Proposal_Id, Object_Version_Number
From Per_Pay_Proposals
Where Change_Date = l_Change_Dt
and Assignment_id = P_Assignment_id;
select change_date ,Pay_Proposal_Id, Object_Version_Number
from per_pay_proposals
where assignment_id = P_Assignment_id
and change_date > p_Effective_date;
select count(*)
from per_pay_proposals
where assignment_id = P_Assignment_id
and change_date > p_Effective_date;
hr_utility.set_location(' Inside Salary Update: Elec_id :' || P_Elig_Per_Elctbl_Chc_Id, 10);
Fetch Pgm_Dtl into L_Update_Salary_Cd, L_DFLT_INPUT_VALUE_ID, L_DFLT_ELEMENT_TYPE_ID, l_Dflt_Step_Cd;
If L_Update_Salary_Cd = 'SALARY_BASIS' Then
If L_Enroll_Info.Pay_Basis_Id is NULL Then
fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Del_proposal_Id
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
,P_SALARY_WARNING => l_Del_Warn);
-- update replace operation.
--
hr_utility.set_location(' P_Dt_Mode :' || P_Dt_Mode, 1);
if P_Dt_Mode = 'UPDATE_OVERRIDE' THEN
open csr_count_pay_det(L_Enroll_Info.Assignment_Id);
Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Del_proposal_Id
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
,P_SALARY_WARNING => l_Del_Warn);
hr_utility.set_location(' after calling delete Proposal ' || l_Del_Proposal_Id, 20);
END IF; -- update override IF ..
select max(effective_end_date) into L_entry_date
from pay_element_entries_f
where assignment_id=L_Enroll_Info.Assignment_Id
and creator_type='SP';
Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_CHANGE_DATE => L_Enroll_Info.RT_Strt_Dt
,P_PROPOSED_SALARY_N => l_Salary
,P_OBJECT_VERSION_NUMBER => L_Pay_Proposals_Ovn
,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
,P_MULTIPLE_COMPONENTS => 'N'
,P_APPROVED => 'Y'
,P_PROPOSAL_REASON => 'GSP'
,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
,P_APPROVED_WARNING => L_APPROVED_WARNING
,P_PAYROLL_WARNING => L_PAYROLL_WARNING);
Ben_Prtt_Rt_Val_Api.UPDATE_PRTT_RT_VAL
(P_PRTT_RT_VAL_ID => L_Enroll_Info.PRTT_RT_VAL_ID
,P_PK_ID_TABLE_NAME => 'PER_PAY_PROPOSALS'
,P_PK_ID => l_Pay_Proposal_Id
,P_OBJECT_VERSION_NUMBER => L_Rt_Ovn
,P_EFFECTIVE_DATE => P_Effective_Date); */
Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
-- If current grade ladder uses a salary element.
--
-- Get default element and input value of current ladder.
--
If L_DFLT_INPUT_VALUE_ID is NULL or L_DFLT_ELEMENT_TYPE_ID is NULL Then
fnd_message.set_name('PQH','PQH_GSP_DFLY_ELMNT_NOT_LNKD');
Pay_Element_Entry_Api.UPDATE_ELEMENT_ENTRY
(P_DATETRACK_UPDATE_MODE => L_DATE_TRACK_MODE
,P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_ELEMENT_ENTRY_ID => L_ELEMENT_ENTRY_ID
,P_OBJECT_VERSION_NUMBER => L_Ele_Ovn
,P_INPUT_VALUE_ID1 => L_DFLT_INPUT_VALUE_ID
,P_ENTRY_VALUE1 => L_Salary
,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
,P_EFFECTIVE_END_DATE => L_Effective_End_Date
,P_UPDATE_WARNING => l_Create_Warn);
End If; /* Update_Salary_Cd */
End Update_Salary_info;
/************************* Update Assignment info *************************/
/**************************************************************************/
Procedure Update_Assgmt_Info
(P_Elig_Per_Elctbl_Chc_Id IN Number,
P_Effective_Date IN Date) is
Cursor Enrlmt_Dtls is
Select Enrlmt.PL_Id , Enrlmt.Pgm_Id , Enrlmt.Oipl_Id , Enrlmt.Business_Group_Id,
Enrlmt.Enrt_Cvg_Strt_Dt, Pgm.Dflt_Step_CD, Pl.MAPPING_TABLE_PK_ID, Asgt.Assignment_Id,
Asgt.Object_Version_Number , Asgt.SPECIAL_CEILING_STEP_ID, Asgt.People_Group_Id,
Asgt.Soft_Coding_Keyflex_Id, Asgt.Grade_Id, Asgt.Grade_Ladder_Pgm_Id
From Ben_Elig_Per_Elctbl_Chc Enrlmt,
Ben_Per_in_ler PIL,
Ben_Pgm_F Pgm,
Ben_PLip_F Plip,
Ben_Pl_F Pl,
Per_All_Assignments_F Asgt
Where Enrlmt.Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_Id
And Enrlmt.Per_In_Ler_id = Pil.Per_In_Ler_id
And Asgt.Person_id = PIL.Person_id
and Pgm.Pgm_Id = Enrlmt.Pgm_Id
and P_Effective_Date
Between Pgm.Effective_Start_Date
and Pgm.Effective_End_Date
and Pl.Pl_Id = Enrlmt.Pl_Id
and P_Effective_Date
Between Pl.Effective_Start_Date
and Pl.Effective_End_Date
and Plip.Pgm_Id = Pgm.Pgm_Id
and Plip.Pl_Id = Pl.Pl_Id
and P_Effective_Date
Between Plip.Effective_Start_Date
and Plip.Effective_End_Date
and Asgt.Person_id = Pil.Person_id
And Asgt.PRIMARY_FLAG = 'Y'
And Asgt.assignment_type = 'E'
And P_Effective_Date
Between Asgt.Effective_start_Date and Asgt.Effective_end_Date;
Select Step.Step_id, Step.Spinal_point_id, Step.Grade_spine_id
From Ben_Oipl_F Oipl,
Ben_pl_F Pl,
Ben_Opt_F Opt,
Per_Spinal_points point,
Per_Grade_Spines_f GSpine,
Per_Spinal_point_Steps_F Step
Where Oipl.Oipl_id = P_Oipl_Id
and P_Effective_Date
Between OiPl.Effective_Start_Date and OiPl.Effective_End_Date
and Pl.Pl_id = Oipl.Pl_Id
and P_Effective_Date
Between Pl.Effective_Start_Date and Pl.Effective_End_Date
and Oipl.Opt_id = Opt.Opt_id
and P_Effective_Date
Between Opt.Effective_Start_Date and Opt.Effective_End_Date
and Point.Spinal_Point_id = Opt.Mapping_Table_Pk_Id
and Pl.Mapping_Table_Pk_Id = Gspine.Grade_Id
and P_Effective_Date
Between Gspine.Effective_Start_Date and Gspine.Effective_End_Date
and Step.Grade_Spine_Id = Gspine.Grade_Spine_Id
and P_Effective_Date
Between Step.Effective_Start_Date and Step.Effective_End_Date
and Step.SPINAL_POINT_ID = Point.SPINAL_POINT_ID;
Select PLACEMENT_ID , Object_Version_Number,
Effective_Start_Date, Effective_End_Date
from Per_Spinal_Point_Placements_F
Where Assignment_Id = P_Assignment_Id
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
l_spp_delete_warning Boolean;
hr_utility.set_location(' Inside Update_Assgmt_Info ', 10);
/* Update Assignments with the Grade Ladder / Grade Details */
L_DATE_TRACK_MODE := DT_Mode
(P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date)
,P_BASE_TABLE_NAME => 'PER_ALL_ASSIGNMENTS_F'
,P_BASE_KEY_COLUMN => 'ASSIGNMENT_ID'
,P_BASE_KEY_VALUE => L_Assignment_id);
Hr_Sp_Placement_Api.DELETE_SPP
(P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date) - 1
,P_DATETRACK_MODE => 'DELETE'
,P_PLACEMENT_ID => l_PLACEMENT_ID
,P_OBJECT_VERSION_NUMBER => l_Placement_Ovn
,P_EFFECTIVE_START_DATE => l_Effective_Start_Date
,P_EFFECTIVE_END_DATE => L_Effective_End_Date);
Hr_Assignment_Api.Update_Emp_Asg_Criteria
(p_effective_date => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date)
,p_datetrack_update_mode => L_Date_Track_Mode
,p_assignment_id => L_Assignment_id
,p_grade_id => L_Grade_Id
,p_grade_ladder_pgm_id => L_Pgm_Id
,p_object_version_number => L_Assignment_Ovn
,p_special_ceiling_step_id => L_special_ceiling_step_id
,p_people_group_id => L_People_Group_Id
,p_soft_coding_keyflex_id => L_Soft_Coding_Keyflex_Id
,p_group_name => L_group_name
,p_effective_start_date => L_Asg_effective_start_date
,p_effective_end_date => L_Asg_effective_end_date
,p_org_now_no_manager_warning => L_org_now_no_manager_warning
,p_other_manager_warning => L_other_manager_warning
,p_spp_delete_warning => L_spp_delete_warning
,p_entries_changed_warning => L_entries_changed_warning
,p_tax_district_changed_warning => L_tax_district_changed_warning
,p_concatenated_segments => L_concatenated_segments);
/* Update Placements with the New Step Details */
Hr_Sp_Placement_Api.UPDATE_SPP
(P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date),
P_DATETRACK_MODE => l_Date_Track_Mode,
P_PLACEMENT_ID => l_PLACEMENT_ID,
P_OBJECT_VERSION_NUMBER => l_Placement_Ovn,
P_REASON => 'GSP',
P_STEP_ID => L_Step_id,
P_EFFECTIVE_START_DATE => l_Effective_Start_Date,
P_EFFECTIVE_END_DATE => L_Effective_End_Date);
l_Date_track_Mode := hr_api.g_Insert;
/* Insert Placements with the New Step Details */
Hr_Sp_Placement_Api.CREATE_SPP
(P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date),
P_BUSINESS_GROUP_ID => l_Business_Group_Id,
P_ASSIGNMENT_ID => L_Assignment_Id,
P_STEP_ID => l_Step_Id,
P_PLACEMENT_ID => l_PLACEMENT_ID,
P_OBJECT_VERSION_NUMBER => l_Placement_Ovn,
P_REASON => 'GSP',
P_EFFECTIVE_START_DATE => l_Effective_Start_Date,
P_EFFECTIVE_END_DATE => L_Effective_End_Date);
hr_utility.set_location(' Leaving Assigmt Updates ', 30);
End Update_Assgmt_Info;
select ptnl.ptnl_ler_for_per_id, ptnl.object_version_number
from ben_per_in_ler per
,ben_ptnl_ler_for_per ptnl
where per.per_in_ler_id = p_per_in_ler_id
and per.ptnl_ler_for_per_id = ptnl.ptnl_ler_for_per_id;
Select Elct.ELIG_PER_ELCTBL_CHC_ID, Pil.Person_Id, Pil.Per_In_Ler_id, Pil. Object_Version_Number,
Elct.Enrt_Cvg_Strt_Dt, Pgm.Update_Salary_Cd, Pgm.Dflt_Step_Cd, Elct.Approval_Status_Cd, Person.Full_name
From ben_Elig_Per_Elctbl_Chc Elct,
Ben_Ler_F Ler,
Ben_Per_in_ler Pil,
Ben_Pgm_F PGM,
Per_All_People_F Person
where Ler.typ_Cd = 'GSP'
and L_Effective_Date
Between Ler.Effective_Start_Date
and Ler.Effective_End_Date
and Ler.Business_group_id = Hr_general.get_Business_Group_id
and Pil.Ler_Id = Ler.Ler_id
and Pil.PER_IN_LER_STAT_CD = 'STRTD'
and Elct.Per_In_Ler_id = Pil.Per_in_Ler_id
and Elct.DFLT_FLAG = 'Y'
and ((P_Approval_Status_Cd is NULL
and Elct.Approval_Status_Cd in ('PQH_GSP_A','PQH_GSP_R'))
or (Elct.Approval_Status_Cd = P_Approval_Status_Cd))
and Pgm.Pgm_Id = Elct.Pgm_Id
and L_Effective_Date
Between Pgm.Effective_Start_Date
and Pgm.Effective_End_Date
and Person.Person_Id = Pil.Person_id
and l_Effective_Date
Between Person.Effective_Start_Date
and Person.Effective_End_Date
Order By Person.Full_name Desc;
/* Update Employee Salary */
if g_debug then
hr_utility.set_location(' Approve ', 30);
pqh_gsp_Post_Process.Update_Assgmt_Info
(P_ELIG_PER_ELCTBL_CHC_ID => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
,P_Effective_Date => Elctbl_Rec.Enrt_Cvg_Strt_Dt);
hr_utility.set_location(' Update Salary ', 40);
/* If Update_Salary_Cd is NO_UPDATE -- Salary Updateis not required.
Hence will not make a call to Salary Update */
If Elctbl_Rec.Update_Salary_Cd <> 'NO_UPDATE' Then
pqh_gsp_Post_Process.Update_Salary_Info
(P_ELIG_PER_ELCTBL_CHC_ID => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
,P_Effective_Date => Elctbl_Rec.Enrt_Cvg_Strt_Dt);
Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
(P_PER_IN_LER_ID => l_PER_IN_LER_ID
,P_PER_IN_LER_STAT_CD => l_Per_In_Ler_Stat_Cd
,P_PROCD_DT => L_PROCD_DT
,P_STRTD_DT => L_STRTD_DT
,P_VOIDD_DT => L_VOIDD_DT
,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
,P_EFFECTIVE_DATE => L_Effective_Date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
(p_ptnl_ler_for_per_id => l_PTNL_LER_FOR_PER_ID
,p_ptnl_ler_for_per_stat_cd => l_Per_In_Ler_Stat_Cd
,p_voidd_dt => l_effective_date
,p_object_version_number => l_PTNL_LER_FOR_PER_OVN
,p_effective_date => l_effective_date);
Select Enrt_Rt_Id,
Object_Version_Number
From Ben_Enrt_Rt
Where Elig_per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
Update Ben_Elig_per_elctbl_Chc
Set Approval_Status_Cd = 'PQH_GSP_A'
,Enrt_Cvg_Strt_Dt = P_Prog_Dt
,Comments = P_Comments
Where Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
Update Ben_Enrt_Rt
Set Rt_Strt_Dt = P_Sal_Chg_Dt
Where Enrt_Rt_Id = L_Enrt_Rt_Id;
Update Ben_Elig_per_elctbl_Chc
Set Approval_Status_Cd = 'PQH_GSP_R'
Where Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
Select grade_ladder_pgm_id,grade_id
From per_all_assignments_f
Where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type ='E'
and p_effective_date between effective_start_date and effective_end_date;
Select pgm_id,dflt_step_cd
From ben_pgm_f
Where business_group_id = p_business_group_id
and pgm_typ_cd = 'GSP'
and pgm_stat_cd = 'A'
and nvl(dflt_pgm_flag,'N') = 'Y'
and p_effective_date between effective_start_date and effective_end_date;
Select dflt_step_cd
From ben_pgm_f
Where pgm_id = p_pgm_id
and p_effective_date between effective_start_date and effective_end_date;
select pl_id
from ben_pl_f pl,ben_pl_typ_f pltyp
where pltyp.business_group_id = p_business_group_id
and pltyp.opt_typ_cd = 'GSP'
and p_effective_date between pltyp.effective_start_date and pltyp.effective_end_date
and pl.pl_typ_id = pltyp.pl_typ_id
and pl.business_group_id = p_business_group_id
and pl.mapping_table_name = 'PER_GRADES'
and pl.mapping_table_pk_id = p_grade_id
and p_effective_date between pl.effective_start_date and pl.effective_end_date
and pl.pl_stat_cd = 'A';
select plip_id from ben_plip_f
where pl_id = p_pl_id
and pgm_id = p_pgm_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date and effective_end_date
and plip_stat_cd = 'A';
select ler_id,program_id,business_group_id
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
Select Asgt.Business_Group_id,
Asgt.Grade_Id , Asgt.pay_basis_id, Asgt.GRADE_LADDER_PGM_ID
from Per_All_Assignments_f Asgt
Where P_Effective_Date
Between Asgt.Effective_Start_Date
and Asgt.Effective_End_Date
and Asgt.Person_id = p_person_id
And Asgt.assignment_id = p_assignment_id;
Select Pl.Pl_Id, plip.Plip_Id, Pgm.DFLT_STEP_CD,
Pgm.UPDATE_SALARY_CD , DFLT_ELEMENT_TYPE_ID
from Ben_Pgm_F Pgm,
Ben_Pl_F Pl,
Ben_Plip_f Plip
Where Pgm.Pgm_Id = P_Pgm_Id
and P_Effective_Date
Between Pgm.Effective_Start_Date
and Pgm.Effective_End_Date
and Pl.Mapping_table_Name = 'PER_GRADES'
and Pl.Mapping_Table_Pk_Id = P_Grd_Id
and P_Effective_Date
Between Pl.Effective_Start_Date
and Pl.Effective_End_Date
and Plip.Pgm_Id = Pgm.Pgm_id
and Plip.Pl_id = Pl.Pl_Id
and P_Effective_Date
Between Plip.Effective_Start_Date
and Plip.Effective_End_Date;
Select Plcmt.Step_id, Step.Spinal_point_id
From Per_Spinal_POint_Placements_f Plcmt,
Per_Spinal_point_Steps_F Step
Where Plcmt.Assignment_id = P_Assignment_Id
and P_Effective_Date
Between Plcmt.Effective_Start_Date
and plcmt.Effective_End_Date
and Step.Step_id = Plcmt.Step_Id
and P_Effective_Date
Between Step.Effective_Start_Date
and Step.Effective_End_Date;
Select Oipl.Oipl_Id, Opt.Opt_Id
From Ben_Opt_F Opt,
Ben_Oipl_F Oipl
Where Opt.Mapping_table_name = 'PER_SPINAL_POINTS'
and Opt.mapping_table_Pk_Id = P_Point_id
and P_Effective_Date
Between Opt.Effective_Start_Date
and Opt.Effective_End_Date
and Oipl.Pl_Id = P_Pl_Id
and Oipl.Opt_id = Opt.Opt_id
and P_Effective_Date
Between Oipl.Effective_Start_Date
and Oipl.Effective_End_Date;
Select Pl_typ_Id
From Ben_Pl_Typ_F
Where Opt_typ_Cd = 'GSP'
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date
and business_group_id = p_business_group_id;
select 'Y'
from pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where Pet.Element_type_id = P_Element_type_Id
and p_Effective_date
Between pet.effective_start_date
and pet.effective_end_date
and Pel.Element_type_Id = Pet.Element_type_Id
and p_Effective_date
Between pel.effective_start_date
and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and p_Effective_date
between pee.effective_start_date
and pee.effective_end_date;
Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
From Ben_Acty_base_Rt_f
where Pl_id = l_Pl_Id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
From Ben_Acty_Base_rt_f
where Opt_Id = L_Opt_id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select Yr_Perd_id
From Ben_Yr_Perd
Where P_Effective_Date
Between Start_Date and End_Date;
Select Pgm_Id
From Ben_PGM_F
Where DFLT_PGM_FLAG = 'Y'
and Pgm_Typ_Cd = 'GSP'
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date
and Business_Group_id = Hr_general.get_business_group_id;
L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
Fetch Ben_Grd_Dtls into L_Pl_id, l_plip_Id , l_Prog_style, L_Update_Salary_Cd, l_Dflt_Element_typ_Id;
If L_Update_Salary_Cd = 'SALARY_BASIS' and L_pay_basis_id is NULL Then
/* Grade Ladder is defined for Salary basis and Pay Basis is not attached to the assignment */
fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
ElsIf L_Update_Salary_Cd = 'SALARY_ELEMENT' then */
/* Grade Ladder uses Salary Element, but Default Salary Element type is not defined */
/* If l_Dflt_Element_typ_Id is NULL Then
fnd_message.set_name('PQH','PQH_GSP_DFLT_ELMNT_NOTDFND');
Select Plcmt.Step_id, Step.Spinal_point_id, Plcmt.Effective_Start_Date
*/
End If;
If L_Update_Salary_Cd is NULL Then
fnd_message.set_name('PQH','PQH_GSP_POSTSTYL_NOT_SET');
/************************** Update Rate Sync Salary. **********************/
/**************************************************************************/
Procedure Update_Rate_Sync_Salary
(P_per_in_ler_Id IN Number
,P_Effective_Date IN Date
) Is
/*
********* Called from benmngle. Don't change the signature ********
*/
Cursor csr_elct_chcs
is
Select Elig_per_Elctbl_Chc_Id,COMP_LVL_CD,pgm_id
from Ben_ELig_per_Elctbl_Chc
where per_in_ler_id = p_per_in_ler_id;
select object_version_number,PTNL_LER_FOR_PER_ID
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id;
select object_version_number
from ben_ptnl_ler_for_per
where PTNL_LER_FOR_PER_ID = p_PTNL_LER_FOR_PER_ID;
L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
Select Rate.Val , Rate.Rt_Strt_Dt, Rate.Prtt_Rt_Val_Id,
Rate.Object_Version_Number, Enrt.Pgm_Id, Enrt.OiPl_Id,
Asgt.Assignment_Id, Asgt.pay_basis_id, Asgt.Grade_Id, Enrt.Business_Group_id,Pil.per_in_ler_id, Rate.Ann_val
From Ben_ELig_per_Elctbl_Chc Enrt,
ben_Enrt_Rt Rate,
Ben_Per_in_ler PIL,
Per_All_Assignments_F Asgt
Where Enrt.Elig_per_Elctbl_Chc_id = P_Elig_per_Elctbl_Chc_Id
And Enrt.Per_In_Ler_id = Pil.Per_In_Ler_id
And Asgt.Person_id = PIL.Person_id
And P_Effective_Date
Between Asgt.Effective_start_Date and Asgt.Effective_end_Date
and Enrt.Elig_per_Elctbl_Chc_id = Rate.Elig_per_Elctbl_Chc_id(+)
and asgt.assignment_type ='E'
And Asgt.PRIMARY_FLAG = 'Y';
Select ele.element_entry_id
from per_pay_bases bas,
pay_element_entries_f ele,
pay_element_entry_values_f entval
where bas.pay_basis_id = P_pay_basis_id
and entval.input_value_id = bas.input_value_id
and p_effective_date
between entval.effective_start_date
and entval.effective_end_date
and ele.assignment_id = P_assignmnet_id
and p_effective_date between ele.effective_start_date
and ele.effective_end_date
and ele.element_entry_id = entval.element_entry_id;
Select Update_Salary_Cd, DFLT_INPUT_VALUE_ID, DFLT_ELEMENT_TYPE_ID, DFLT_STEP_CD,ACTY_REF_PERD_CD
From ben_Pgm_F
Where Pgm_id = P_Pgm_Id
and P_effective_Date
Between Effective_Start_Date
and Effective_End_Date;
select pee.Element_Entry_Id, pee.Object_version_Number
from pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
where Pet.Element_type_id = L_DFLT_ELEMENT_TYPE_ID
and p_Effective_date
Between pet.effective_start_date
and pet.effective_end_date
and Pel.Element_type_Id = Pet.Element_type_Id
and p_Effective_date
Between pel.effective_start_date
and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and p_Effective_date
between pee.effective_start_date
and pee.effective_end_date;
Select Max(Change_Date)
from Per_Pay_Proposals
Where Assignment_Id = P_Assignment_id;
Select Pay_Proposal_Id, Object_Version_Number
From Per_Pay_Proposals
Where Change_Date = l_Change_Dt
and Assignment_id = P_Assignment_id;
select pay_basis,PAY_ANNUALIZATION_FACTOR
from per_pay_bases
where pay_basis_id =p_pay_basis_id;
hr_utility.set_location(' Entering pqh_gsp_post_process.update_rate_sync_salary ', 5);
Fetch Pgm_Dtl into L_Update_Salary_Cd, L_DFLT_INPUT_VALUE_ID, L_DFLT_ELEMENT_TYPE_ID, l_Dflt_Step_Cd,l_acty_ref_perd_cd;
hr_utility.set_location(' Inside Salary Update: Elec_id :' ||l_def_elct_chc_id , 10);
Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
(P_PER_IN_LER_ID => p_PER_IN_LER_ID
,P_PER_IN_LER_STAT_CD => 'VOIDD'
,P_PROCD_DT => l_procd_date
,P_STRTD_DT => l_strtd_date
,P_VOIDD_DT => l_voidd_date
,P_OBJECT_VERSION_NUMBER => l_per_in_ler_ovn
,P_EFFECTIVE_DATE => P_Effective_Date);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
(p_ptnl_ler_for_per_id => l_PTNL_LER_FOR_PER_ID
,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
,p_voidd_dt => p_effective_date
,p_object_version_number => l_PTNL_LER_FOR_PER_OVN
,p_effective_date => p_effective_date);
If L_Update_Salary_Cd = 'SALARY_BASIS' Then
If L_Enroll_Info.Pay_Basis_Id is NULL Then
fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Del_proposal_Id
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
,P_SALARY_WARNING => l_Del_Warn);
Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
(P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_CHANGE_DATE => L_Enroll_Info.RT_Strt_Dt
,P_PROPOSED_SALARY_N => l_Salary
,P_OBJECT_VERSION_NUMBER => L_Pay_Proposals_Ovn
,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
,P_MULTIPLE_COMPONENTS => 'N'
,P_APPROVED => 'Y'
,P_PROPOSAL_REASON => 'GSP'
,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
,P_APPROVED_WARNING => L_APPROVED_WARNING
,P_PAYROLL_WARNING => L_PAYROLL_WARNING);
Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
If L_DFLT_INPUT_VALUE_ID is NULL or L_DFLT_ELEMENT_TYPE_ID is NULL Then
fnd_message.set_name('PQH','PQH_GSP_DFLY_ELMNT_NOT_LNKD');
Pay_Element_Entry_Api.UPDATE_ELEMENT_ENTRY
(P_DATETRACK_UPDATE_MODE => L_DATE_TRACK_MODE
,P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
,P_ELEMENT_ENTRY_ID => L_ELEMENT_ENTRY_ID
,P_OBJECT_VERSION_NUMBER => L_Ele_Ovn
,P_INPUT_VALUE_ID1 => L_DFLT_INPUT_VALUE_ID
,P_ENTRY_VALUE1 => L_Salary
,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
,P_EFFECTIVE_END_DATE => L_Effective_End_Date
,P_UPDATE_WARNING => l_Create_Warn);
End If; /* Update_Salary_Cd */
Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
(P_PER_IN_LER_ID => p_PER_IN_LER_ID
,P_PER_IN_LER_STAT_CD => 'PROCD'
,P_PROCD_DT => l_procd_date
,P_STRTD_DT => l_strtd_date
,P_VOIDD_DT => l_voidd_date
,P_OBJECT_VERSION_NUMBER => l_per_in_ler_ovn
,P_EFFECTIVE_DATE => P_Effective_Date);
End Update_Rate_Sync_Salary;
Select Enrt_Rt_Id,
Object_Version_Number
From Ben_Enrt_Rt
Where Elig_per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
pqh_rank_utility.update_proposed_rank
(p_proposed_rank => p_proposed_rank
,p_assignment_id => p_assignment_id
,p_life_event_dt => p_life_event_dt
,p_pgm_id => p_grade_ladder_id
,p_pl_id => p_pl_id
);
Update Ben_Elig_per_elctbl_Chc
Set Enrt_Cvg_Strt_Dt = P_PROGRESSION_DATE
Where Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
Update Ben_Enrt_Rt
Set Rt_Strt_Dt = P_Sal_Chg_Dt
Where Enrt_Rt_Id = L_Enrt_Rt_Id;