[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure is used by worksheet when rank is updated to
prevent any duplicates getting created.
*/
procedure insert_new_rank
(p_assignment_id in number
,p_rank in number
,p_rank_by_person_id in number
,p_level_number in number
,p_assignment_extra_info_id out nocopy number
,p_object_version_number out nocopy number) is
Cursor Csr_EIT_Dtls IS
SELECT 'Y'
FROM PER_ASSIGNMENT_EXTRA_INFO ASS_EIT
WHERE ASS_EIT.INFORMATION_TYPE = 'CWBRANK'
AND ASS_EIT.ASSIGNMENT_ID = p_assignment_Id
AND ASS_EIT.AEI_INFORMATION2 = p_rank_by_person_id
AND ASS_EIT.AEI_INFORMATION5 IS NULL
AND ASS_EIT.AEI_INFORMATION6 IS NULL;
end insert_new_rank;
PROCEDURE UPDATE_RANK(P_GROUP_PER_IN_LER_ID IN NUMBER
,P_RANK IN NUMBER
,P_USER_ID IN VARCHAR2) IS
Cursor Csr_Assignment_ID IS
Select pil_emp.assignment_id
,mgr.lvl_num
From ben_per_in_ler pil_emp
,ben_cwb_group_hrchy mgr
,ben_per_in_ler pil_mgr
Where pil_emp.per_in_ler_id = p_group_per_in_ler_id
and pil_emp.per_in_ler_id = mgr.emp_per_in_ler_id
and mgr.lvl_num > 0
and mgr.mgr_per_in_ler_id = pil_mgr.per_in_ler_id
and pil_mgr.person_id = p_user_id;
SELECT ASS_EIT.ASSIGNMENT_EXTRA_INFO_ID ,
ASS_EIT.OBJECT_VERSION_NUMBER
FROM PER_ASSIGNMENT_EXTRA_INFO ASS_EIT
WHERE ASS_EIT.INFORMATION_TYPE = 'CWBRANK'
AND ASS_EIT.ASSIGNMENT_ID = l_assignment_Id
AND ASS_EIT.AEI_INFORMATION2 = P_USER_ID
AND ASS_EIT.AEI_INFORMATION5 IS NULL
AND ASS_EIT.AEI_INFORMATION6 IS NULL;
l_proc varchar2(72) := g_package||'UPDATE_RANK';
hr_assignment_extra_info_api.update_assignment_extra_info
(p_assignment_extra_info_id => l_assignment_extra_info_id
,p_object_version_number => l_ovn
,p_aei_information_category => 'CWBRANK'
,p_aei_information1 => P_RANK
,p_aei_information4 => l_lvl_num);
END UPDATE_RANK;
function UPDATE_WS_AMOUNT (P_PERSON_RATE_ID IN NUMBER
,P_WS_VAL IN NUMBER default null
,p_add_val in number default null
,P_USER_ID IN VARCHAR2
,P_WS_RT_START_DATE IN DATE DEFAULT NULL)
return number
IS
Cursor Csr_PlRt_Dtls
IS
Select PlRt.GROUP_PER_IN_LER_ID GROUP_PER_IN_LER_ID,
PlRt.PL_ID PL_ID,
PlRt.OIPL_ID OIPL_ID,
PlRt.GROUP_PL_ID GROUP_PL_ID,
PlRt.GROUP_OIPL_ID GROUP_OIPL_ID,
PlRt.LF_EVT_OCRD_DT LF_EVT_OCRD_DT,
PlRt.Object_Version_Number OVN,
PlRt.ws_val ws_val,
plrt.ws_rt_start_date WS_RT_START_DATE
From BEN_CWB_PERSON_RATES PlRt
,ben_cwb_pl_dsgn dsgn
Where PlRt.PERSON_RATE_ID = P_PERSON_RATE_ID
And PlRt.ELIG_FLAG='Y'
and plRt.pl_id = dsgn.pl_id
and plRt.oipl_id = dsgn.oipl_id
and plRt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
and dsgn.ws_abr_id is not null;
Select EMPLOYEE_ID
From FND_USER
Where User_Id = l_USER_ID;
l_proc varchar2(72) := g_package||'UPDATE_WS_AMOUNT';
BEN_CWB_PERSON_RATES_API.update_person_rate
( p_group_per_in_ler_id => l_upd_rec.GROUP_PER_IN_LER_ID
,p_pl_id => l_upd_rec.PL_ID
,p_oipl_id => l_upd_rec.OIPL_ID
,p_group_pl_id => l_upd_rec.GROUP_PL_ID
,p_group_oipl_id => l_upd_rec.GROUP_OIPL_ID
,p_lf_evt_ocrd_dt => l_upd_rec.LF_EVT_OCRD_DT
,p_ws_val_last_upd_date => trunc(Sysdate)
,p_ws_val_last_upd_by => l_person_id
,p_ws_val => l_WS_VAL
,p_object_version_number => l_ovn
,p_ws_rt_start_date => l_ws_rt_start_date
);
END UPDATE_WS_AMOUNT;
Select group_pl_id
,lf_evt_ocrd_dt
from ben_cwb_person_rates
where person_rate_id = l_person_rate_id;
Select STATUS_CD
,OBJECT_VERSION_NUMBER
From ben_cwb_person_tasks
Where GROUP_PER_IN_LER_ID = P_SEC_MGR_LER_ID
And TASK_ID = P_TASK_ID
And GROUP_PL_ID = l_group_pl_id
And LF_EVT_OCRD_DT = l_lf_evt_ocrd_dt ;
BEN_CWB_PERSON_TASKS_API.update_person_task
( p_group_per_in_ler_id => P_SEC_MGR_LER_ID
,p_task_id => P_TASK_ID
,p_group_pl_id => l_group_pl_id
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
,p_status_cd => 'IP'
,p_task_last_update_date => sysdate
,p_object_version_number => l_ovn
);
Select group_per_in_ler_id
from ben_cwb_person_rates
where person_rate_id = l_person_rate_id;
Select POST_PROCESS_STAT_CD
From Ben_Cwb_Person_Info
Where Group_Per_In_Ler_Id = l_ler_id;
SELECT submit_cd
FROM ben_cwb_person_groups mgr,
ben_cwb_group_hrchy hrchy
WHERE hrchy.emp_per_in_ler_id = l_ler_id
and hrchy.mgr_per_in_ler_id = mgr.group_per_in_ler_id
and hrchy.lvl_num = 1*/
SELECT 'SU'
FROM ben_cwb_group_hrchy h
WHERE emp_per_in_ler_id = p_mgr_ler_id
AND EXISTS (SELECT 'x'
FROM ben_cwb_person_groups
WHERE group_per_in_ler_id = h.mgr_per_in_ler_id
AND SUBMIT_CD = 'SU')
;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM BEN_CWB_GROUP_HRCHY
WHERE MGR_PER_IN_LER_ID = p_mgr_ler_id
AND EMP_PER_IN_LER_ID = p_emp_ler_id );
select ben_cwb_utils.get_task_access (bcwg.hidden_cd,
bcpt.access_cd,
bcpg.access_cd,
bcwg.wksht_grp_cd,
bcpg.bdgt_pop_cd,
bcpt.status_cd,
bcpg.dist_bdgt_iss_date,
NVL (bcpd.upd_start_dt,
bcpd.enrt_perd_start_dt
),
NVL (bcpd.upd_end_dt,
bcpd.enrt_perd_end_dt
),
sysdate
) accesscd
from ben_cwb_person_info bcpi,
ben_cwb_person_groups bcpg,
ben_cwb_pl_dsgn bcpd,
ben_cwb_person_tasks bcpt,
ben_cwb_wksht_grp bcwg
WHERE bcpi.group_per_in_ler_id = P_MGR_PER_IN_LER_ID
AND bcpg.group_per_in_ler_id = bcpi.group_per_in_ler_id
AND bcpg.group_pl_id = bcpi.group_pl_id
AND bcpg.group_oipl_id = -1
AND bcpd.pl_id = bcpi.group_pl_id
AND bcpd.lf_evt_ocrd_dt = bcpi.lf_evt_ocrd_dt
AND bcpd.oipl_id = -1
AND bcpt.group_per_in_ler_id = bcpi.group_per_in_ler_id
AND bcwg.cwb_wksht_grp_id = bcpt.task_id
AND bcwg.wksht_grp_cd = 'COMP';
Procedure update_perf_rating(P_PROPOSED_PERFORMANCE_RATING IN Varchar2
,P_ACTING_PERSON_ID IN NUMBER Default Null
,P_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
IS
l_proc Varchar2(72) := g_package||'update_perf_rating';
Select per.assignment_id Assignment_id
,per.person_id Person_id
,per.business_group_id business_group_id
,per.full_name Person_name
From ben_cwb_person_info per
Where per.group_per_in_ler_id = l_group_per_in_ler_id;
select txn.attribute3 rating
from ben_transaction txn
where txn.transaction_id = l_assignment_id
and txn.transaction_type = 'CWBPERF'|| trim(l_perf_revw_strt_dt) || trim(l_emp_interview_typ_cd);
Select dsgn.emp_interview_typ_cd
,to_char(dsgn.perf_revw_strt_dt,'yyyy/mm/dd') perf_revw_strt_dt
,dsgn.group_pl_id
From ben_cwb_pl_dsgn dsgn
,ben_cwb_person_info inf
Where dsgn.group_oipl_id = -1
And dsgn.pl_id = inf.group_pl_id
And dsgn.lf_evt_ocrd_dt = inf.lf_evt_ocrd_dt
And inf.group_per_in_ler_id = l_group_per_in_ler_id;
select new_assgn_ovn, new_perf_event_id, new_perf_review_id
from ben_cwb_person_info
where group_per_in_ler_id = v_group_per_in_ler_id;
ben_cwb_asg_update.process_rating
(p_validate_data => 'Y'
,p_assignment_id => l_person_info.Assignment_id
,p_person_id => l_person_info.Person_id
,p_business_group_id => l_person_info.business_group_id
,p_perf_revw_strt_dt => l_pl_dsgn_info.perf_revw_strt_dt --to_char(l_pl_dsgn_info.perf_revw_strt_dt,'yyyy/mm/dd')
,p_perf_type => l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD
,p_perf_rating => P_PROPOSED_PERFORMANCE_RATING
,p_person_name => l_person_info.Person_name
,p_update_person_id => P_ACTING_PERSON_ID
,p_update_date => Sysdate
,p_group_pl_id => l_pl_dsgn_info.group_pl_id);
End update_perf_rating;
Procedure update_promotions(P_PROPOSED_JOB IN Varchar2
,P_PROPOSED_POSITION IN Varchar2
,P_PROPOSED_GRADE IN Varchar2
,P_CHANGE_REASON IN Varchar2
,P_ACTING_PERSON_ID IN NUMBER Default Null
,P_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
IS
l_proc Varchar2(72) := g_package||'update_promotions';
Select per.assignment_id Assignment_id
,per.person_id Person_id
,per.business_group_id business_group_id
,per.full_name Person_name
From ben_cwb_person_info per
Where per.group_per_in_ler_id = l_group_per_in_ler_id;
Select dsgn.EMP_INTERVIEW_TYP_CD
,to_char(dsgn.asg_updt_eff_date,'yyyy/mm/dd') asg_updt_eff_date
,dsgn.group_pl_id
From ben_cwb_pl_dsgn dsgn
,ben_cwb_person_info inf
Where dsgn.group_oipl_id = -1
And dsgn.pl_id = inf.group_pl_id
And dsgn.lf_evt_ocrd_dt = inf.lf_evt_ocrd_dt
And inf.group_per_in_ler_id = l_group_per_in_ler_id;
select txn.attribute3 change_reason
,to_number(txn.attribute5) job_id
,to_number(txn.attribute6) position_id
,to_number(txn.attribute7) grade_id
from ben_transaction txn
where txn.transaction_id = l_assignment_id
and txn.transaction_type = 'CWBASG'|| trim(l_asg_updt_eff_date);
select j.job_id
from per_jobs_tl jtl , per_jobs j
where jtl.name = P_PROPOSED_JOB
and jtl.language = 'US'
and jtl.job_id = j.job_id
and j.business_group_id = bg_id
and rownum =1;
select p.position_id
from hr_all_positions_f_tl ptl, hr_all_positions_f p
where ptl.name = P_PROPOSED_POSITION
and ptl.language = 'US'
and ptl.position_id = p.position_id
and p.business_group_id = bg_id
and rownum =1;
select p.grade_id
from per_grades_tl ptl, per_grades p
where ptl.name = P_PROPOSED_GRADE
and ptl.language = 'US'
and ptl.grade_id = p.grade_id
and p.business_group_id = bg_id
and rownum =1;
select new_assgn_ovn, new_perf_event_id, new_perf_review_id
from ben_cwb_person_info
where group_per_in_ler_id = v_group_per_in_ler_id;
ben_cwb_asg_update.process_promotions
(p_validate_data => 'Y'
,p_assignment_id => l_person_info.Assignment_id
,p_person_id => l_person_info.Person_id
,p_business_group_id => l_person_info.business_group_id
,p_asg_updt_eff_date => l_pl_dsgn_info.asg_updt_eff_date --to_char(l_pl_dsgn_info.asg_updt_eff_date,'yyyy/mm/dd')
,p_change_reason => l_change_reason
,p_job_id => l_job_id
,p_position_id => l_pos_id
,p_grade_id => l_grade_id
,p_people_group_id => null
,p_soft_coding_keyflex_id => null
,p_ass_attribute1 => null
,p_ass_attribute2 => null
,p_ass_attribute3 => null
,p_ass_attribute4 => null
,p_ass_attribute5 => null
,p_ass_attribute6 => null
,p_ass_attribute7 => null
,p_ass_attribute8 => null
,p_ass_attribute9 => null
,p_ass_attribute10 => null
,p_ass_attribute11 => null
,p_ass_attribute12 => null
,p_ass_attribute13 => null
,p_ass_attribute14 => null
,p_ass_attribute15 => null
,p_ass_attribute16 => null
,p_ass_attribute17 => null
,p_ass_attribute18 => null
,p_ass_attribute19 => null
,p_ass_attribute20 => null
,p_ass_attribute21 => null
,p_ass_attribute22 => null
,p_ass_attribute23 => null
,p_ass_attribute24 => null
,p_ass_attribute25 => null
,p_ass_attribute26 => null
,p_ass_attribute27 => null
,p_ass_attribute28 => null
,p_ass_attribute29 => null
,p_ass_attribute30 => null
,p_person_name => l_person_info.Person_name
,p_update_person_id => P_ACTING_PERSON_ID
,p_update_date => Sysdate
,p_group_pl_id => l_pl_dsgn_info.group_pl_id);
End update_promotions;
procedure update_other_rates (P_PERSON_RATE_ID IN NUMBER
,p_interface_seq IN g_iterface_seq_type
,p_values in g_iterface_seq_type
,p_interface_code in varchar2
,p_base_layout_code in varchar2 )
IS
p_final_values g_iterface_seq_type := g_iterface_seq_type(null,null,null,null,null,null);
Select PlRt.GROUP_PER_IN_LER_ID GROUP_PER_IN_LER_ID,
PlRt.PL_ID PL_ID,
PlRt.OIPL_ID OIPL_ID,
PlRt.GROUP_PL_ID GROUP_PL_ID,
PlRt.GROUP_OIPL_ID GROUP_OIPL_ID,
PlRt.LF_EVT_OCRD_DT LF_EVT_OCRD_DT,
PlRt.Object_Version_Number OVN,
PlRt.STAT_SAL_VAL,
PlRt.OTH_COMP_VAL,
PlRt.TOT_COMP_VAL,
PlRt.MISC1_VAL,
PlRt.MISC2_VAL,
PlRt.MISC3_VAL
,dsgn.misc1_rndg_cd m1_rndg_cd
,dsgn.misc2_rndg_cd m2_rndg_cd
,dsgn.misc3_rndg_cd m3_rndg_cd
,dsgn.tot_comp_rndg_cd tc_rndg_cd
,dsgn.oth_comp_rndg_cd oc_rndg_cd
,dsgn.stat_sal_rndg_cd ss_rndg_cd
From BEN_CWB_PERSON_RATES PlRt
,ben_cwb_pl_dsgn dsgn
Where PlRt.PERSON_RATE_ID = P_PERSON_RATE_ID
And PlRt.ELIG_FLAG='Y'
and plRt.pl_id = dsgn.pl_id
and plRt.oipl_id = dsgn.oipl_id
and plRt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt;
select interface_seq_num, decode(nvl(lay.read_only_flag,'N')||nvl(intf.read_only_flag,'N'), 'NN','N','Y') read_only
from bne_interface_cols_b intf, bne_layout_cols lay
where intf.interface_code = nvl(p_interface_code,'BEN_CWB_WRK_SHT_INTF')
and intf.application_id = 800
and intf.interface_code = lay.interface_code
and lay.layout_code = nvl(p_base_layout_code,'BEN_CWB_WRK_SHT_BASE_LYT')
and lay.application_id = 800
and intf.sequence_num = lay.interface_seq_num
and intf.sequence_num in (l_seq1,l_seq2,l_seq3,l_seq4,l_seq5,l_seq6);
l_proc varchar2(72) := g_package||'update_other_rates';
BEN_CWB_PERSON_RATES_API.update_person_rate
( p_group_per_in_ler_id => l_upd_rec.GROUP_PER_IN_LER_ID
,p_pl_id => l_upd_rec.PL_ID
,p_oipl_id => l_upd_rec.OIPL_ID
,p_group_pl_id => l_upd_rec.GROUP_PL_ID
,p_group_oipl_id => l_upd_rec.GROUP_OIPL_ID
,p_lf_evt_ocrd_dt => l_upd_rec.LF_EVT_OCRD_DT
,p_stat_sal_val => p_final_values(1)
,p_oth_comp_val => p_final_values(2)
,p_tot_comp_val => p_final_values(3)
,p_misc1_val => p_final_values(4)
,p_misc2_val => p_final_values(5)
,p_misc3_val => p_final_values(6)
,p_object_version_number => l_upd_rec.ovn
);
END update_other_rates;
Select group_pl_id,
lf_evt_ocrd_dt
from ben_cwb_person_rates
where person_rate_id = l_person_rate_id;
SELECT PlRt.person_rate_id
FROM BEN_CWB_PERSON_RATES PlRt
,ben_cwb_person_rates optRt
WHERE optRt.PERSON_RATE_ID = p_opt_person_rate_id
and optRt.group_per_in_ler_id = plRt.group_per_in_ler_id
and optRt.pl_id = plRt.pl_id
and plRt.oipl_id = -1;
SELECT PlRt.WS_VAL
FROM BEN_CWB_PERSON_RATES PlRt
WHERE PlRt.PERSON_RATE_ID = l_PL_PERSON_RATE_ID;
SELECT cpi_attribute_category,
cpi_attribute1,
cpi_attribute2,
cpi_attribute3,
cpi_attribute4,
cpi_attribute5,
cpi_attribute6,
cpi_attribute7,
cpi_attribute8,
cpi_attribute9,
cpi_attribute10,
cpi_attribute11,
cpi_attribute12,
cpi_attribute13,
cpi_attribute14,
cpi_attribute15,
cpi_attribute16,
cpi_attribute17,
cpi_attribute18,
cpi_attribute19,
cpi_attribute20,
cpi_attribute21,
cpi_attribute22,
cpi_attribute23,
cpi_attribute24,
cpi_attribute25,
cpi_attribute26,
cpi_attribute27,
cpi_attribute28,
cpi_attribute29,
cpi_attribute30,
custom_segment1,
custom_segment2,
custom_segment3,
custom_segment4,
custom_segment5,
custom_segment6,
custom_segment7,
custom_segment8,
custom_segment9,
custom_segment10,
custom_segment11,
custom_segment12,
custom_segment13,
custom_segment14,
custom_segment15,
custom_segment16,
custom_segment17,
custom_segment18,
custom_segment19,
custom_segment20,
object_version_number
FROM ben_cwb_person_info
WHERE group_per_in_ler_id = l_group_per_in_ler_id;
SELECT group_pl_id, lf_evt_ocrd_dt
FROM ben_per_in_ler
WHERE per_in_ler_id = l_group_per_in_ler_id
AND ROWNUM < 2;
select 'BEN_CWB_WS_INTF_' || trim(group_pl_id) intf,
'BEN_CWB_WS_LYT1_' || trim(group_pl_id) base_layout
from ben_cwb_pl_dsgn
where custom_integrator is not null
and group_pl_id = l_group_pl_id
and lf_evt_ocrd_dt = l_lf_evt_ocrd_dt;
select interface_seq_num, decode(nvl(lay.read_only_flag,'N')||nvl(intf.read_only_flag,'N'), 'NN','N','Y') read_only
from bne_interface_cols_b intf, bne_layout_cols lay
where intf.interface_code = nvl(l_intf,'BEN_CWB_WRK_SHT_INTF')
and intf.application_id = 800
and intf.interface_code = lay.interface_code
and lay.layout_code = nvl(l_base_layout,'BEN_CWB_WRK_SHT_BASE_LYT')
and lay.application_id = 800
and intf.sequence_num = lay.interface_seq_num
and ((intf.sequence_num between 200 and 234) or (intf.sequence_num between 136 and 150));
savepoint update_data;
BEN_CWB_SUMMARY_PKG.delete_pl_sql_tab;
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,P_WS_VAL => P_PL_WS_VAL
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE =>P_PL_RT_START_DATE );
hr_utility.set_location('Updated Plan WS Amt Sucessfully',130);
update_other_rates(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,p_interface_seq => g_iterface_seq_type(8,10,11,19,20,21)
,p_values => g_iterface_seq_type(P_PL_STAT_SAL_VAL,P_PL_OTH_COMP_VAL,P_PL_TOT_COMP_VAL,
P_PL_MISC1_VAL,P_PL_MISC2_VAL,P_PL_MISC3_VAL)
,p_interface_code => l_interfac_code
,p_base_layout_code => l_base_layout_code );
hr_utility.set_location('Updated Plan Other Rates Sucessfully',130);
update_other_rates(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,p_interface_seq => g_iterface_seq_type(8,10,11,19,20,21)
,p_values => g_iterface_seq_type(P_PL_STAT_SAL_VAL,P_PL_OTH_COMP_VAL,P_PL_TOT_COMP_VAL,
P_PL_MISC1_VAL,P_PL_MISC2_VAL,P_PL_MISC3_VAL)
,p_interface_code => l_interfac_code
,p_base_layout_code => l_base_layout_code );
hr_utility.set_location('Updated Plan Other Rates Sucessfully',130);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
,P_WS_VAL => P_OPT1_WS_VAL
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_OPT1_RT_START_DATE);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,p_add_val => l_diff
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
hr_utility.set_location('Updated Option1 WS Amt Sucessfully',160);
update_other_rates(P_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
,p_interface_seq => g_iterface_seq_type(26,28,29,37,38,39)
,p_values => g_iterface_seq_type(P_OPT1_STAT_SAL_VAL,P_OPT1_OTH_COMP_VAL,P_OPT1_TOT_COMP_VAL,
P_OPT1_MISC1_VAL,P_OPT1_MISC2_VAL,P_OPT1_MISC3_VAL)
,p_interface_code => l_interfac_code
,p_base_layout_code => l_base_layout_code );
hr_utility.set_location('Updated Option 1 Other Rates Sucessfully',130);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
,P_WS_VAL => P_OPT2_WS_VAL
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_OPT2_RT_START_DATE);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,p_add_val => l_diff
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
hr_utility.set_location('Updated Option2 WS Amt Sucessfully',170);
update_other_rates(P_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
,p_interface_seq => g_iterface_seq_type(44,46,47,55,56,57)
,p_values => g_iterface_seq_type(P_OPT2_STAT_SAL_VAL,P_OPT2_OTH_COMP_VAL,P_OPT2_TOT_COMP_VAL,
P_OPT2_MISC1_VAL,P_OPT2_MISC2_VAL,P_OPT2_MISC3_VAL)
,p_interface_code => l_interfac_code
,p_base_layout_code => l_base_layout_code );
hr_utility.set_location('Updated Option 2 Other Rates Sucessfully',130);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
,P_WS_VAL => P_OPT3_WS_VAL
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_OPT3_RT_START_DATE);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,p_add_val => l_diff
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
hr_utility.set_location('Updated Option3 WS Amt Sucessfully',180);
update_other_rates(P_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
,p_interface_seq => g_iterface_seq_type(62,64,65,73,74,75)
,p_values => g_iterface_seq_type(P_OPT3_STAT_SAL_VAL,P_OPT3_OTH_COMP_VAL,P_OPT3_TOT_COMP_VAL,
P_OPT3_MISC1_VAL,P_OPT3_MISC2_VAL,P_OPT3_MISC3_VAL)
,p_interface_code => l_interfac_code
,p_base_layout_code => l_base_layout_code );
hr_utility.set_location('Updated Option 3 Other Rates Sucessfully',130);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
,P_WS_VAL => P_OPT4_WS_VAL
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_OPT4_RT_START_DATE);
l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,p_add_val => l_diff
,P_USER_ID => P_USER_ID
,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
hr_utility.set_location('Updated Option4 WS Amt Sucessfully',190);
update_other_rates(P_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
,p_interface_seq => g_iterface_seq_type(80,82,83,91,92,93)
,p_values => g_iterface_seq_type(P_OPT4_STAT_SAL_VAL,P_OPT4_OTH_COMP_VAL,P_OPT4_TOT_COMP_VAL,
P_OPT4_MISC1_VAL,P_OPT4_MISC2_VAL,P_OPT4_MISC3_VAL)
,p_interface_code => l_interfac_code
,p_base_layout_code => l_base_layout_code );
hr_utility.set_location('Updated Option 4 Other Rates Sucessfully',130);
hr_utility.set_location('Updated Worksheet Amount Sucessfully ',200);
UPDATE_RANK(P_GROUP_PER_IN_LER_ID => l_group_per_in_ler_id
,P_RANK => P_RANK
,P_USER_ID => l_ACTING_PERSON_ID);
hr_utility.set_location('Updated Rank Sucessfully ',220);
update_perf_rating(P_PROPOSED_PERFORMANCE_RATING =>P_PROPOSED_PERFORMANCE_RATING
,P_ACTING_PERSON_ID => l_ACTING_PERSON_ID
,P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID);
hr_utility.set_location('Updated Proposed Performance Rating Sucessfully ',240);
update_promotions(P_PROPOSED_JOB =>P_PROPOSED_JOB
,P_PROPOSED_POSITION => P_PROPOSED_POSITION
,P_PROPOSED_GRADE => P_PROPOSED_GRADE
,P_CHANGE_REASON => P_CHANGE_REASON
,P_ACTING_PERSON_ID => l_ACTING_PERSON_ID
,P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID);
hr_utility.set_location('Updated Proposed Promotion Sucessfully ',240);
hr_utility.set_location('Update Person Tasks Table Sucessfully ',250);
BEN_CWB_PERSON_INFO_API.update_person_info
( P_GROUP_PER_IN_LER_ID => l_GROUP_PER_IN_LER_ID
,P_CPI_ATTRIBUTE_CATEGORY => P_CPI_ATTRIBUTE_CATEGORY
,P_CPI_ATTRIBUTE1 => L_CPI_ATTRIBUTE1
,P_CPI_ATTRIBUTE2 => L_CPI_ATTRIBUTE2
,P_CPI_ATTRIBUTE3 => l_CPI_ATTRIBUTE3
,P_CPI_ATTRIBUTE4 => l_CPI_ATTRIBUTE4
,P_CPI_ATTRIBUTE5 => l_CPI_ATTRIBUTE5
,P_CPI_ATTRIBUTE6 => l_CPI_ATTRIBUTE6
,P_CPI_ATTRIBUTE7 => l_CPI_ATTRIBUTE7
,P_CPI_ATTRIBUTE8 => l_CPI_ATTRIBUTE8
,P_CPI_ATTRIBUTE9 => l_CPI_ATTRIBUTE9
,P_CPI_ATTRIBUTE10 => l_CPI_ATTRIBUTE10
,P_CPI_ATTRIBUTE11 => l_CPI_ATTRIBUTE11
,P_CPI_ATTRIBUTE12 => l_CPI_ATTRIBUTE12
,P_CPI_ATTRIBUTE13 => l_CPI_ATTRIBUTE13
,P_CPI_ATTRIBUTE14 => l_CPI_ATTRIBUTE14
,P_CPI_ATTRIBUTE15 => l_CPI_ATTRIBUTE15
,P_CPI_ATTRIBUTE16 => l_CPI_ATTRIBUTE16
,P_CPI_ATTRIBUTE17 => l_CPI_ATTRIBUTE17
,P_CPI_ATTRIBUTE18 => l_CPI_ATTRIBUTE18
,P_CPI_ATTRIBUTE19 => l_CPI_ATTRIBUTE19
,P_CPI_ATTRIBUTE20 => l_CPI_ATTRIBUTE20
,P_CPI_ATTRIBUTE21 => l_CPI_ATTRIBUTE21
,P_CPI_ATTRIBUTE22 => l_CPI_ATTRIBUTE22
,P_CPI_ATTRIBUTE23 => l_CPI_ATTRIBUTE23
,P_CPI_ATTRIBUTE24 => l_CPI_ATTRIBUTE24
,P_CPI_ATTRIBUTE25 => l_CPI_ATTRIBUTE25
,P_CPI_ATTRIBUTE26 => l_CPI_ATTRIBUTE26
,P_CPI_ATTRIBUTE27 => l_CPI_ATTRIBUTE27
,P_CPI_ATTRIBUTE28 => l_CPI_ATTRIBUTE28
,P_CPI_ATTRIBUTE29 => l_CPI_ATTRIBUTE29
,P_CPI_ATTRIBUTE30 => l_CPI_ATTRIBUTE30
,P_CUSTOM_SEGMENT1 => l_CUSTOM_SEGMENT1
,P_CUSTOM_SEGMENT2 => l_CUSTOM_SEGMENT2
,P_CUSTOM_SEGMENT3 => l_CUSTOM_SEGMENT3
,P_CUSTOM_SEGMENT4 => l_CUSTOM_SEGMENT4
,P_CUSTOM_SEGMENT5 => l_CUSTOM_SEGMENT5
,P_CUSTOM_SEGMENT6 => l_CUSTOM_SEGMENT6
,P_CUSTOM_SEGMENT7 => l_CUSTOM_SEGMENT7
,P_CUSTOM_SEGMENT8 => l_CUSTOM_SEGMENT8
,P_CUSTOM_SEGMENT9 => l_CUSTOM_SEGMENT9
,P_CUSTOM_SEGMENT10 => l_CUSTOM_SEGMENT10
,P_CUSTOM_SEGMENT11 => l_CUSTOM_SEGMENT11
,P_CUSTOM_SEGMENT12 => l_CUSTOM_SEGMENT12
,P_CUSTOM_SEGMENT13 => l_CUSTOM_SEGMENT13
,P_CUSTOM_SEGMENT14 => l_CUSTOM_SEGMENT14
,P_CUSTOM_SEGMENT15 => l_CUSTOM_SEGMENT15
,P_CUSTOM_SEGMENT16 => l_CUSTOM_SEGMENT16
,P_CUSTOM_SEGMENT17 => l_CUSTOM_SEGMENT17
,P_CUSTOM_SEGMENT18 => l_CUSTOM_SEGMENT18
,P_CUSTOM_SEGMENT19 => l_CUSTOM_SEGMENT19
,P_CUSTOM_SEGMENT20 => l_CUSTOM_SEGMENT20
,P_OBJECT_VERSION_NUMBER => L_OVN);
hr_utility.set_location('Updated CPI Flex Rating Sucessfully ',281);
Rollback to Update_data;