[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;
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 dsgn.EMP_INTERVIEW_TYP_CD
,dsgn.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;
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 => 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;
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 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 = 'BEN_CWB_WRK_SHT_INTF'
and intf.application_id = 800
and intf.interface_code = lay.interface_code
and lay.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 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);
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);
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);
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);
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);
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);
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;