[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 120.6 21-Jan-11 sgnanama Update submit code in process_access
* 120.7 10-Feb-11 sgnanama Update task status code in process_access
* 120.8 15-Feb-11 sgnanama Send FYI Notification
* Bug fixes : 11731996, 11732038, 11732038, 11741078, 11736671, 11736130
* 120.9 25-Feb-11 sgnanama Bug fixes : 11788202, 11741025, 11803107
* 120.10 02-Jun-11 sgnanama Bug fixes : 12580980, 12571024
* 120.11 03-Jun-11 sgnanama Bug fixes : 12581640, 12607360
* 120.12 03-Jun-11 sgnanama Corrected the msg number for BEN_94739_PAYROLL_RAN
* 120.13 22-Jun-11 naramasa Bug fixes : 12635353
* 120.14 29-Jun-11 sgnanama Bug fixes : 12696399, 12616726
* 120.16 01-Jul-11 sgnanama Bug fixes : 12714771
* 120.17 07-Jul-11 sgnanama Bug fixes : 12714771
* 120.18 30-Jan-12 naramasa Bug fixes : 12581404
* 120.17.x.4 3-Feb-12 sgnanama Bug fixes : 16246942
* 120.17.x.5 22-Apr-13 sgnanama Bug fixes : additional fix for 12581404
* ==========================================================================+
*/
--
-- Global cursor and variables declaration
--
TYPE plan_override_date IS RECORD (
plan ben_cwb_pl_dsgn.pl_id%type,
date ben_cwb_pl_dsgn.ovrid_rt_strt_dt%type);
g_person_selected NUMBER (9) := 0;
SELECT o.oipl_id,
rpt.group_per_in_ler_id,
rpt.pl_id,
group_oipl.oipl_id group_oipl_id,
rpt.person_id
FROM ben_oipl_f o,
ben_cwb_rpt_detail rpt,
ben_oipl_f local_oipl,
ben_opt_f local_opt,
ben_opt_f group_opt,
ben_oipl_f group_oipl
WHERE o.pl_id = rpt.pl_id
AND rpt.benefit_action_id = v_benefit_action_id
AND rpt.oipl_id = -1
AND local_oipl.oipl_id = o.oipl_id
AND local_opt.opt_id = local_oipl.opt_id
AND group_opt.group_opt_id = local_opt.group_opt_id
AND group_oipl.opt_id = group_opt.group_opt_id
AND NOT EXISTS
(SELECT NULL
FROM ben_cwb_rpt_detail
WHERE oipl_id = o.oipl_id
AND benefit_action_id = v_benefit_action_id)
GROUP BY o.oipl_id,
rpt.group_per_in_ler_id,
rpt.pl_id,
group_oipl.oipl_id,
rpt.person_id;
SELECT ws_element_type_id, ws_input_value_id
FROM ben_cwb_pl_dsgn
WHERE lf_evt_ocrd_dt = v_lf_evt_ocrd_date
AND pl_id = v_pl_id
AND oipl_id = v_oipl_id;
SELECT ovrid_rt_strt_dt
FROM ben_cwb_pl_dsgn dsgn
WHERE group_pl_id = v_group_pl_id
AND pl_id = v_pl_id
AND group_oipl_id = v_group_oipl_id
AND oipl_id = v_oipl_id
AND lf_evt_ocrd_dt = v_lf_evt_ocrd_date;
SELECT object_version_number
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = v_per_in_ler_id;
SELECT object_version_number
FROM ben_cwb_person_info info
WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT object_version_number
FROM ben_cwb_person_rates rt
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = v_pl_id
AND rt.oipl_id = v_oipl_id;
SELECT grp.object_version_number
,grp.access_cd
,grp.approval_cd
,grp.submit_cd
,per.full_name
,per.person_id
,per.group_per_in_ler_id
FROM ben_cwb_person_groups grp
,ben_cwb_person_info per
WHERE grp.group_per_in_ler_id = v_group_per_in_ler_id
AND grp.group_pl_id = v_group_pl_id
AND grp.group_oipl_id = v_group_oipl_id
AND per.group_per_in_ler_id = grp.group_per_in_ler_id;
SELECT bg.NAME
, per.full_name
, info.business_group_id
FROM per_business_groups_perf bg
, ben_cwb_person_info info
, per_all_people_f per
, ben_per_in_ler pil
WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
AND bg.business_group_id = info.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
AND info.group_per_in_ler_id = pil.per_in_ler_id
AND pil.ws_mgr_id = per.person_id(+)
AND v_effective_date BETWEEN per.effective_start_date(+) AND per.effective_end_date(+);
SELECT nvl(per.custom_name,per.full_name) full_name
, per.employee_number
, per.assignment_id
, per.business_group_id
, per.legislation_code
FROM ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT info.batch_proc_id
, info.object_version_number
FROM ben_batch_proc_info info
WHERE info.benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND status_cd = 'E'
AND benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND status_cd IN ('WC', 'SC', 'W')
AND benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND lf_evt_closed_flag = 'N'
AND benefit_action_id = v_benefit_action_id;
SELECT COUNT (*) amount
FROM ben_cwb_rpt_detail
WHERE person_rate_id = -9999
AND lf_evt_closed_flag = 'Y'
AND benefit_action_id = v_benefit_action_id;
CURSOR c_person_selection (
v_pl_id IN NUMBER
, v_lf_evt_orcd_date IN DATE
, v_person_id IN NUMBER
, v_manager_id IN NUMBER
, v_business_group_id IN NUMBER
, v_effective_date IN DATE
, v_local_plan_list IN VARCHAR2
)
IS
SELECT pil.person_id
, max(pil.per_in_ler_id) per_in_ler_id
, max(nvl(per.custom_name,per.full_name)) full_name
, max(bg.NAME) NAME
, per.business_group_id
FROM ben_per_in_ler pil
, ben_per_in_ler mgr_pil
, ben_cwb_group_hrchy hrchy
, per_business_groups_perf bg
, ben_cwb_person_info per
, ben_cwb_person_rates rates
, ben_cwb_pl_dsgn dsgn
WHERE pil.per_in_ler_stat_cd = 'PROCD'
AND pil.group_pl_id = v_pl_id
AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
AND per.group_per_in_ler_id = pil.per_in_ler_id
AND ( v_person_id IS NULL
OR pil.person_id = v_person_id)
AND ( v_business_group_id IS NULL
OR per.business_group_id = v_business_group_id)
AND per.business_group_id = bg.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
AND ( ( v_manager_id IS NULL
AND hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
)
OR ( mgr_pil.person_id = v_manager_id
AND hrchy.lvl_num > 0)
)
AND rates.group_per_in_ler_id = pil.per_in_ler_id
AND rates.pl_id = dsgn.pl_id
AND rates.oipl_id = dsgn.oipl_id
--AND rates.elig_flag = 'Y'
AND (v_local_plan_list is NULL
OR (instr(v_local_plan_list,to_char(dsgn.pl_id)) >0 ) )
AND rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND dsgn.oipl_id=-1
AND nvl(dsgn.do_not_process_flag,'N') <> 'Y'
GROUP BY pil.person_id, per.business_group_id
ORDER BY full_name;
CURSOR c_placeholder_selection (
v_pl_id IN NUMBER
, v_lf_evt_orcd_date IN DATE
, v_person_id IN NUMBER
, v_manager_id IN NUMBER
, v_business_group_id IN NUMBER
, v_effective_date IN DATE
, v_local_plan_list IN VARCHAR2
)
IS
SELECT pil.person_id
, (pil.per_in_ler_id) per_in_ler_id
, (nvl(per.custom_name,per.full_name)) full_name
, bg.NAME
, per.legislation_code
, per.business_group_id
FROM
ben_per_in_ler pil,
ben_per_in_ler mgr_pil,
ben_cwb_person_info per,
ben_cwb_group_hrchy hrchy,
per_business_groups_perf bg
where pil.per_in_ler_stat_cd = 'PROCD'
AND pil.group_pl_id = per.group_pl_id
AND pil.lf_evt_ocrd_dt = per.lf_evt_ocrd_dt
AND per.group_per_in_ler_id = pil.per_in_ler_id
AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
AND hrchy.mgr_per_in_ler_id = mgr_pil.per_in_ler_id
AND ( v_person_id IS NULL
OR pil.person_id = v_person_id)
AND ( ( v_manager_id IS NULL
AND hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy
WHERE emp_per_in_ler_id = hrchy.emp_per_in_ler_id)
)
OR ( mgr_pil.person_id = v_manager_id
AND hrchy.lvl_num > 0)
)
and not exists(
select null from
ben_cwb_person_rates
where group_per_in_ler_id = pil.per_in_ler_id
)
and (v_business_group_id is null or
per.business_group_id = v_business_group_id)
AND bg.business_group_id = per.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
and per.group_pl_id = v_pl_id
and per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
;
SELECT distinct (mgr_per.group_per_in_ler_id) per_in_ler_id
, (mgr_per.full_name) full_name
FROM ben_per_in_ler pil
--, ben_per_in_ler mgr_pil
, ben_cwb_group_hrchy hrchy
--, ben_cwb_group_hrchy mgr_hrchy
, per_business_groups_perf bg
, ben_cwb_person_info per
, ben_cwb_person_info mgr_per
--, ben_cwb_person_rates rates
--, ben_cwb_pl_dsgn dsgn
WHERE pil.per_in_ler_stat_cd = 'PROCD'
AND pil.group_pl_id = v_pl_id
AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
AND per.group_per_in_ler_id = pil.per_in_ler_id
AND ( v_person_id IS NULL
OR pil.person_id = v_person_id)
AND ( v_business_group_id IS NULL
OR per.business_group_id = v_business_group_id)
AND per.business_group_id = bg.business_group_id
AND v_effective_date >= bg.date_from
AND ( bg.date_to IS NULL
OR bg.date_to >= v_effective_date)
AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
AND hrchy.lvl_num > 0
AND mgr_per.group_per_in_ler_id = hrchy.mgr_per_in_ler_id
AND ( ( v_manager_id IS NULL
)
OR hrchy.emp_per_in_ler_id in ( select mmgr.per_in_ler_id from ben_per_in_ler mmgr where
mmgr.ws_mgr_id = v_manager_id and mmgr.per_in_ler_stat_cd = 'PROCD')
)
/*AND rates.group_per_in_ler_id = pil.per_in_ler_id
AND rates.pl_id = dsgn.pl_id
AND rates.oipl_id = dsgn.oipl_id
--AND rates.elig_flag = 'Y'
AND (v_local_plan_list is NULL
OR (instr(v_local_plan_list,to_char(dsgn.pl_id)) >0 ) )
AND rates.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND dsgn.oipl_id=-1
AND nvl(dsgn.do_not_process_flag,'N') <> 'Y'*/
-- GROUP BY pil.person_id, per.business_group_id
ORDER BY full_name;
select null
from ben_cwb_person_rates
where group_per_in_ler_id = v_group_per_in_ler_id
and oipl_id = -1
and elig_flag = 'Y';
SELECT pil.per_in_ler_id
, pil.per_in_ler_stat_cd
, pil.object_version_number
FROM ben_per_in_ler pil
WHERE pil.group_pl_id = v_group_pl_id
AND pil.person_id = v_person_id
AND pil.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND pil.per_in_ler_stat_cd = 'PROCD'
AND ( v_employee_in_bg IS NULL
OR pil.business_group_id = v_employee_in_bg);
SELECT ran.range_id
, ran.starting_person_action_id
, ran.ending_person_action_id
FROM ben_batch_ranges ran
WHERE ran.range_status_cd = 'U'
AND ran.benefit_action_id = v_benefit_action_id
AND ROWNUM < 2
FOR UPDATE OF ran.range_status_cd;
SELECT ben.person_id
, ben.person_action_id
, ben.object_version_number
, ben.ler_id
, ben.non_person_cd
FROM ben_person_actions ben
WHERE ben.benefit_action_id = v_benefit_action_id
AND ben.action_status_cd <> 'P'
AND ben.person_action_id BETWEEN v_start_person_action_id AND v_end_person_action_id
ORDER BY ben.person_action_id;
SELECT ben.*
FROM ben_benefit_actions ben
WHERE ben.benefit_action_id = v_benefit_action_id;
SELECT dsgn.perf_revw_strt_dt
, nvl(dsgn.ovr_perf_revw_strt_dt, dsgn.perf_revw_strt_dt)
, dsgn.asg_updt_eff_date
, dsgn.emp_interview_typ_cd
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.pl_id = v_pl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.oipl_id = -1;
SELECT COUNT (*)
FROM ben_oipl_f oipl
, ben_opt_f opt
WHERE oipl.pl_id = v_pl_id
AND oipl.opt_id = opt.opt_id
and opt.component_reason is not null
AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date;
SELECT dsgn.ws_sub_acty_typ_cd
FROM ben_cwb_person_rates rt
, ben_cwb_pl_dsgn dsgn
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt;
SELECT xtra_info.aei_information1
, xtra_info.aei_information2
, xtra_info.aei_information4
, xtra_info.assignment_id
, xtra_info.object_version_number
, xtra_info.assignment_extra_info_id
FROM per_assignment_extra_info xtra_info
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND xtra_info.assignment_id = per.assignment_id
AND xtra_info.information_type = 'CWBRANK'
AND xtra_info.aei_information1 IS NOT NULL
AND xtra_info.aei_information3 = v_group_per_in_ler_id
AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_perf_revw_strt_dt)
AND xtra_info.aei_information6 = v_plan_id;
SELECT xtra_info.aei_information1
, xtra_info.aei_information2
, xtra_info.aei_information4
, xtra_info.assignment_id
, xtra_info.object_version_number
FROM per_assignment_extra_info xtra_info
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND xtra_info.assignment_id = per.assignment_id
AND xtra_info.information_type = 'CWBRANK'
AND xtra_info.aei_information5 = fnd_date.date_to_canonical(v_eff_dt)
AND xtra_info.aei_information2 = v_ranked_by;
select pet.element_name||': '||piv.name
from pay_input_values_f piv,
pay_element_types_f pet
where piv.input_value_id = v_input_value_id
and piv.element_type_id = v_element_type_id
and piv.element_type_id = pet.element_type_id
and v_effective_date between piv.effective_start_date and piv.effective_end_date
and v_effective_date between pet.effective_start_date and pet.effective_end_date;
SELECT asg.assignment_id
, asg.pay_basis_id
, ppp.proposed_salary_n
, ppp.object_version_number
FROM per_all_assignments_f asg
, per_pay_bases ppb
, per_pay_proposals ppp
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND asg.assignment_id = per.assignment_id
AND v_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ppb.pay_basis_id = asg.pay_basis_id
AND ppp.assignment_id = asg.assignment_id
AND ppp.approved = 'Y'
AND ppp.change_date =
(SELECT MAX (ppp1.change_date)
FROM per_pay_proposals ppp1
WHERE ppp1.assignment_id = asg.assignment_id
AND ppp1.approved = 'Y'
AND change_date <= v_effective_date);
SELECT ppp.proposed_salary_n
FROM per_pay_proposals ppp
, ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
AND ppp.assignment_id = per.assignment_id
AND ppp.change_date > v_effective_date
AND ppp.approved='Y'; -- Bug 12635353
SELECT per.business_group_id
, per.base_salary
, per.base_salary_currency
, initcap(base_salary_frequency) base_salary_frequency
, pay_annulization_factor
, fte_factor
, per.assignment_id
FROM ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT dsgn.NAME
, dsgn.group_pl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.pl_id = v_group_pl_id
AND dsgn.pl_id = dsgn.group_pl_id
AND dsgn.group_oipl_id = -1
AND dsgn.oipl_id = dsgn.group_oipl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt;
SELECT dsgn.NAME
, dsgn.group_oipl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.pl_id = v_group_pl_id
AND dsgn.pl_id = dsgn.group_pl_id
AND dsgn.oipl_id = dsgn.group_oipl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.oipl_id <> -1;
SELECT dsgn.NAME
, dsgn.pl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.group_pl_id = v_group_pl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.oipl_id = -1
AND dsgn.pl_id <> dsgn.group_pl_id;
SELECT dsgn.NAME
, dsgn.oipl_id
FROM ben_cwb_pl_dsgn dsgn
WHERE dsgn.group_pl_id = v_group_pl_id
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND dsgn.pl_id <> dsgn.group_pl_id
AND dsgn.oipl_id <> -1;
SELECT COUNT (*) amount
, action_status_cd
FROM ben_person_actions act
WHERE act.benefit_action_id = v_benefit_action_id
AND act.action_status_cd IN ('P', 'E', 'U')
GROUP BY action_status_cd;
select eev.screen_entry_value
from pay_element_entries_f ee,
pay_element_entry_values_f eev
where ee.assignment_id = v_assignment_id
and ee.element_type_id = v_element_type_id
and v_effective_date between ee.effective_start_date and ee.effective_end_date
and eev.element_entry_id = ee.element_entry_id
and eev.input_value_id = v_input_value_id
and eev.effective_start_date = ee.effective_start_date
and eev.effective_end_date = ee.effective_end_date
and eev.screen_entry_value is not null
order by ee.effective_start_date;
select proposed_salary_n
from per_pay_proposals
where pay_proposal_id = v_pay_proposal_id;
select perf.performance_rating
from per_performance_reviews perf
where perf.person_id = v_person_id
and perf.review_date = v_effective_date;
Select null
From fnd_concurrent_requests fnd
Where request_id = v_request_id
and status_code = 'E';
SELECT trans.transaction_id,
trans.attribute1,
trans.attribute2
FROM ben_transaction trans,
ben_cwb_pl_dsgn dsgn
WHERE trans.transaction_id IN
(SELECT DISTINCT pl_id
FROM ben_cwb_person_rates rates
WHERE group_per_in_ler_id = v_group_per_in_ler_id)
AND trans.transaction_type = 'CWBPPOVDT' || to_char(v_lf_evt_ocrd_dt,'yyyy/mm/dd');
SELECT nvl(rt.ws_val,0) ws_val
, rt.person_rate_id
, opt.component_reason
, dsgn.salary_change_reason
, dsgn.pl_id
, dsgn.oipl_id
, dsgn.group_pl_id
, dsgn.group_oipl_id
, dsgn.ws_nnmntry_uom units
, dsgn.ws_sub_acty_typ_cd
, pil.ws_mgr_id
, info.full_name
, info.employee_number
, info.business_group_id
, rt.elig_sal_val
, xchg.xchg_rate
, rt.elig_flag
, rt.currency
, rt.comp_posting_date
FROM ben_cwb_person_rates rt
, ben_oipl_f oipl
, ben_cwb_pl_dsgn dsgn
, ben_opt_f opt
, ben_cwb_person_info info
, ben_per_in_ler pil
, ben_cwb_xchg xchg
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.group_pl_id = v_group_pl_id
--AND rt.oipl_id <> -1
--AND rt.elig_flag = 'Y' (for logging)
AND rt.lf_evt_ocrd_dt = v_lf_evt_orcd_dt
--AND nvl(rt.ws_val,0) <> 0
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND rt.group_per_in_ler_id = info.group_per_in_ler_id
AND rt.group_per_in_ler_id = pil.per_in_ler_id
AND oipl.oipl_id = rt.oipl_id
AND oipl.opt_id = opt.opt_id
AND opt.component_reason is not null
AND v_effective_date BETWEEN opt.effective_start_date AND opt.effective_end_date
AND v_effective_date BETWEEN oipl.effective_start_date AND oipl.effective_end_date
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
SELECT rt.ws_val
, rt.person_rate_id
, rt.pl_id
, rt.oipl_id
, rt.object_version_number
, rt.lf_evt_ocrd_dt
, dsgn.ws_sub_acty_typ_cd
, dsgn.ws_abr_id
, dsgn.salary_change_reason
, dsgn.ws_nnmntry_uom units
, dsgn.acty_ref_perd_cd
, dsgn.business_group_id
, dsgn.group_pl_id
, dsgn.group_oipl_id
, pil.ws_mgr_id
, info.full_name
, info.employee_number
, info.assignment_id
, opt.component_reason
, info.base_salary_currency
, dsgn.uom_precision
, info.base_salary
, rt.elig_sal_val
, initcap(info.base_salary_frequency) base_salary_frequency
, info.pay_annulization_factor
, dsgn.pl_annulization_factor
, xchg.xchg_rate
, rt.elig_flag
, info.fte_factor
, rt.currency
, rt.comp_posting_date
FROM ben_cwb_person_rates rt
, ben_cwb_pl_dsgn dsgn
, ben_cwb_person_info info
, ben_per_in_ler pil
, ben_oipl_f oipl
, ben_opt_f opt
, ben_cwb_xchg xchg
WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
AND rt.pl_id = dsgn.pl_id
AND rt.oipl_id = dsgn.oipl_id
--AND rt.elig_flag = 'Y' (for logging)
AND rt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND rt.group_per_in_ler_id = info.group_per_in_ler_id
AND rt.group_per_in_ler_id = pil.per_in_ler_id
AND rt.oipl_id = oipl.oipl_id (+)
AND oipl.opt_id = opt.opt_id (+)
-- AND opt.component_reason(+) is null --12571024
and not exists (select null from ben_opt_f opt1 where opt.opt_id = opt1.opt_id and opt1.component_reason is not null) --12616726
AND v_effective_date BETWEEN opt.effective_start_date (+) AND opt.effective_end_date (+)
AND v_effective_date BETWEEN oipl.effective_start_date (+)AND oipl.effective_end_date (+)
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
AND exists (select null from ben_cwb_pl_dsgn where pl_id = rt.pl_id and oipl_id = -1 and nvl(do_not_process_flag,'N') <> 'Y');
SELECT decode(piv.uom,NULL,2,'M',nvl(curr.PRECISION,2),5) PRECISION
FROM per_all_assignments_f asg,
per_pay_bases ppb,
pay_input_values_f piv,
pay_element_types_f pet,
fnd_currencies curr
WHERE asg.assignment_id = v_assignment_id
AND v_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.pay_basis_id = ppb.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND v_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND v_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.input_currency_code = curr.currency_code;
SELECT dsgn.pl_annulization_factor,
dsgn.uom_precision,
info.pay_annulization_factor,
dsgn.salary_change_reason
FROM ben_cwb_pl_dsgn dsgn,
ben_cwb_person_info info
WHERE dsgn.group_pl_id = v_group_pl_id
AND dsgn.group_pl_id = dsgn.pl_id
AND dsgn.oipl_id = -1
AND dsgn.lf_evt_ocrd_dt = v_lf_evt_ocrd_dt
AND info.group_per_in_ler_id = v_group_per_in_ler_id;
select 'Y'
from per_all_assignments_f asst,
per_time_periods period
where asst.assignment_id = p_assignmant_id
and asst.payroll_id = period.payroll_id
and p_posting_date between period.start_date and period.end_date
and p_effective_date > period.cut_off_date;
select t.task_id, t.object_version_number, t.status_cd, t.task_last_update_date, g.wksht_grp_cd
from ben_cwb_person_tasks t,
ben_cwb_wksht_grp g
where t.group_per_in_ler_id = p_per_in_ler_id
and CWB_WKSHT_GRP_ID = t.task_id;
BEN_CWB_PERSON_TASKS_API.update_person_task
( p_group_per_in_ler_id => p_per_in_ler_id
,p_task_id => l_task.task_id
,p_status_cd => 'IP'
,p_task_last_update_date => nvl(p_effective_date,l_task.task_last_update_date) --additional fix for bug 12581404
,p_object_version_number => l_task.object_version_number
);
BEN_CWB_PERSON_TASKS_API.update_person_task
( p_group_per_in_ler_id => p_per_in_ler_id
,p_task_id => l_task.task_id
,p_status_cd => 'IP'
,p_object_version_number => l_task.object_version_number
);
SELECT per.full_name
,per.person_id
,per.group_per_in_ler_id
,pl.name plan_name
FROM ben_cwb_pl_dsgn pl
,ben_cwb_person_info per
WHERE per.group_per_in_ler_id = v_group_per_in_ler_id
and per.group_pl_id = pl.pl_id
and pl.oipl_id = -1;
SELECT distinct hrchy.mgr_per_in_ler_id mgr_per_in_ler_id
from ben_cwb_group_hrchy hrchy,
ben_per_in_ler pil
where hrchy.mgr_per_in_ler_id = v_mgr_per_in_ler_id
and hrchy.emp_per_in_ler_id = pil.per_in_ler_id
and hrchy.lvl_num > 0
and pil.per_in_ler_stat_cd = 'STRTD'
AND pil.group_pl_id = p_group_pl_id
AND pil.lf_evt_ocrd_dt = p_lf_evt_orcd_date;
write_m('Access, Approval and Submit code updated for ' || l_grp_ovn.full_name);
ben_cwb_person_groups_api.update_group_budget
(p_validate => l_validate_flag
, p_group_per_in_ler_id => p_mgr_per_id(l_count)
, p_group_pl_id => p_group_pl_id
, p_group_oipl_id => -1
, p_access_cd => 'UP'
, p_approval_date => null
, p_approval_cd => null
, p_submit_cd => 'NS'
, p_submit_date => null
, p_object_version_number => l_grp_ovn.object_version_number
);
insert into ben_transaction ( transaction_id,
transaction_type,
attribute1, -- from_person_id,
attribute2, -- to_person_id,
attribute3, -- to_per_in_ler_id,
attribute4, -- plan_name
attribute5, -- for_strt_dt
attribute6, -- for_end_dt
attribute7, -- new_access_cd
attribute40,-- comments
attribute9, -- last updated date/time
attribute10, -- old_access_cd
attribute11, -- requestor first name
attribute12 -- requestor last name
)
values ( ben_transaction_s.nextval,
'CWBNTF',
null, --p_from_person_id,
l_notify.person_id,
l_notify.group_per_in_ler_id,
l_notify.plan_name,
null,
null,
hr_general.decode_lookup('BEN_WS_ACC', 'UP' ),
'Your worksheet is now available for further updates. Please make any changes as required and re-submit for approval.' ,
fnd_date.date_to_canonical(sysdate),
hr_general.decode_lookup('BEN_WS_ACC', l_grp_ovn.access_cd ),
null, --l_requestor_first_name,
'System Administrator' --l_requestor_last_name
)
returning transaction_id into l_transaction_id ;
write_m('Access and approval cd not update for ' || l_grp_ovn.full_name || ' as status is not RO');
, p_person_selected IN NUMBER
, p_business_group_id IN NUMBER DEFAULT NULL
)
IS
l_actions c_actions%ROWTYPE;
g_exec_param_rec.persons_selected :=
g_exec_param_rec.persons_errored + g_exec_param_rec.persons_proc_succ;
, p_per_slctd => g_exec_param_rec.persons_selected
, p_per_proc => g_exec_param_rec.lf_evt_closed
, p_per_unproc => g_exec_param_rec.lf_evt_not_closed
, p_per_proc_succ => g_exec_param_rec.persons_proc_succ
, p_per_err => g_exec_param_rec.persons_errored
, p_business_group_id => p_business_group_id
, p_object_version_number => l_object_version_number
);
PROCEDURE insert_person_actions (
p_per_actn_id_array IN g_number_type
, p_per_id IN g_number_type
, p_group_per_in_ler_id IN g_number_type
, p_benefit_action_id IN NUMBER
, p_is_placeholder IN g_number_type
)
IS
l_num_rows NUMBER := p_per_actn_id_array.COUNT;
g_proc := 'insert_person_actions';
write('Time before inserting person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_person_actions
(person_action_id
, person_id
, ler_id
, benefit_action_id
, action_status_cd
, object_version_number
, NON_PERSON_CD
)
VALUES (p_per_actn_id_array (l_count)
, p_per_id (l_count)
, p_group_per_in_ler_id (l_count)
, p_benefit_action_id
, 'U'
, 1
, decode(p_is_placeholder (l_count),1,'Y','N')
);
write_m ('Time before inserting ben batch ranges '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_batch_ranges
(range_id
, benefit_action_id
, range_status_cd
, starting_person_action_id
, ending_person_action_id
, object_version_number
)
VALUES (ben_batch_ranges_s.NEXTVAL
, p_benefit_action_id
, 'U'
, p_per_actn_id_array (1)
, p_per_actn_id_array (l_num_rows)
, 1
);
write_m ('Time at end of insert person actions '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_cwb_rpt_detail
(benefit_action_id
, person_rate_id
, pl_id
, person_id
, country_code
, group_per_in_ler_id
, oipl_id
, group_pl_id
, group_oipl_id
, ws_mgr_id
, lf_evt_ocrd_dt
, full_name
, employee_number
, business_group_id
, business_group_name
, manager_name
, pl_name
, opt_name
, amount
, units
, performance_rating
, assignment_changed_flag
, status_cd
, lf_evt_closed_flag
, error_or_warning_text
, cwb_rpt_detail_id
, base_salary_currency
, currency
, base_salary
, elig_salary
, percent_of_elig_sal
, base_sal_freq
, pay_ann_factor
, pl_ann_factor
, conversion_factor
, adjusted_amount
, prev_sal
, new_sal
, pay_proposal_id
, pay_basis_id
, element_entry_id
, exchange_rate
, effective_date
, reason
, eligibility
, fte_factor
, element_input_value
, amount_posted
, assignment_id
, element_entry_value_id
, input_value_id
, element_type_id
, eev_screen_entry_value
, elmnt_processing_type
, uom_precision
, ws_sub_acty_typ_cd
, posted_rating
, rating_type
, rating_date
, prior_job
, posted_job
, proposed_job
, prior_position
, posted_position
, proposed_position
, prior_grade
, posted_grade
, proposed_grade
, prior_group
, posted_group
, proposed_group
, prior_flex1
, posted_flex1
, proposed_flex1
, prior_flex2
, posted_flex2
, proposed_flex2
, prior_flex3
, posted_flex3
, proposed_flex3
, prior_flex4
, posted_flex4
, proposed_flex4
, prior_flex5
, posted_flex5
, proposed_flex5
, prior_flex6
, posted_flex6
, proposed_flex6
, prior_flex7
, posted_flex7
, proposed_flex7
, prior_flex8
, posted_flex8
, proposed_flex8
, prior_flex9
, posted_flex9
, proposed_flex9
, prior_flex10
, posted_flex10
, proposed_flex10
, prior_flex11
, posted_flex11
, proposed_flex11
, prior_flex12
, posted_flex12
, proposed_flex12
, prior_flex13
, posted_flex13
, proposed_flex13
, prior_flex14
, posted_flex14
, proposed_flex14
, prior_flex15
, posted_flex15
, proposed_flex15
, prior_flex16
, posted_flex16
, proposed_flex16
, prior_flex17
, posted_flex17
, proposed_flex17
, prior_flex18
, posted_flex18
, proposed_flex18
, prior_flex19
, posted_flex19
, proposed_flex19
, prior_flex20
, posted_flex20
, proposed_flex20
, prior_flex21
, posted_flex21
, proposed_flex21
, prior_flex22
, posted_flex22
, proposed_flex22
, prior_flex23
, posted_flex23
, proposed_flex23
, prior_flex24
, posted_flex24
, proposed_flex24
, prior_flex25
, posted_flex25
, proposed_flex25
, prior_flex26
, posted_flex26
, proposed_flex26
, prior_flex27
, posted_flex27
, proposed_flex27
, prior_flex28
, posted_flex28
, proposed_flex28
, prior_flex29
, posted_flex29
, proposed_flex29
, prior_flex30
, posted_flex30
, proposed_flex30
, asgn_change_reason
, pending_workflow
, new_rpt
, prev_eev_screen_entry_value)
VALUES (benutils.g_benefit_action_id
, g_cache_cwb_rpt_person (i).person_rate_id
, g_cache_cwb_rpt_person (i).pl_id
, g_cache_cwb_rpt_person (i).person_id
, g_cache_cwb_rpt_person (i).country_code
, g_cache_cwb_rpt_person (i).group_per_in_ler_id
, g_cache_cwb_rpt_person (i).oipl_id
, g_cache_cwb_rpt_person (i).group_pl_id
, g_cache_cwb_rpt_person (i).group_oipl_id
, g_cache_cwb_rpt_person (i).ws_mgr_id
, g_cache_cwb_rpt_person (i).lf_evt_ocrd_date
, g_cache_cwb_rpt_person (i).full_name
, g_cache_cwb_rpt_person (i).emp_number
, g_cache_cwb_rpt_person (i).business_group_id
, g_cache_cwb_rpt_person (i).business_group_name
, g_cache_cwb_rpt_person (i).manager_name
, g_cache_cwb_rpt_person (i).pl_name
, g_cache_cwb_rpt_person (i).opt_name
, g_cache_cwb_rpt_person (i).amount
, g_cache_cwb_rpt_person (i).units
, g_cache_cwb_rpt_person (i).performance_rating
, g_cache_cwb_rpt_person (i).assignment_changed
, g_cache_cwb_rpt_person (i).status
, g_cache_cwb_rpt_person (i).lf_evt_closed
, l_message_text
, ben_cwb_rpt_detail_s.NEXTVAL
, g_cache_cwb_rpt_person (i).base_salary_currency
, g_cache_cwb_rpt_person (i).currency
, round(g_cache_cwb_rpt_person (i).base_salary*g_cache_cwb_rpt_person (i).pay_ann_factor/g_cache_cwb_rpt_person (i).pl_ann_factor,
nvl(g_cache_cwb_rpt_person (i).uom_precision,2))
, g_cache_cwb_rpt_person (i).elig_salary
, g_cache_cwb_rpt_person (i).percent_of_elig_sal
, g_cache_cwb_rpt_person (i).base_sal_freq
, g_cache_cwb_rpt_person (i).pay_ann_factor
, g_cache_cwb_rpt_person (i).pl_ann_factor
, g_cache_cwb_rpt_person (i).conversion_factor
, g_cache_cwb_rpt_person (i).adjusted_amount
, g_cache_cwb_rpt_person (i).prev_sal
, g_cache_cwb_rpt_person (i).new_sal
, g_cache_cwb_rpt_person (i).pay_proposal_id
, g_cache_cwb_rpt_person (i).pay_basis_id
, g_cache_cwb_rpt_person (i).element_entry_id
, g_cache_cwb_rpt_person (i).exchange_rate
, g_cache_cwb_rpt_person (i).effective_date
, substr(hr_general.decode_lookup('PROPOSAL_REASON',g_cache_cwb_rpt_person (i).reason),1,30) -- bug : 7042887/10149579
, g_cache_cwb_rpt_person (i).eligibility
, g_cache_cwb_rpt_person (i).fte_factor
, g_cache_cwb_rpt_person (i).element_input_value
, nvl(g_cache_cwb_rpt_person (i).amount_posted,0)
, g_cache_cwb_rpt_person (i).assignment_id
, g_cache_cwb_rpt_person (i).element_entry_value_id
, g_cache_cwb_rpt_person (i).input_value_id
, g_cache_cwb_rpt_person (i).element_type_id
, g_cache_cwb_rpt_person (i).eev_screen_entry_value
, g_cache_cwb_rpt_person (i).elmnt_processing_type
, g_cache_cwb_rpt_person (i).uom_precision
, g_cache_cwb_rpt_person (i).ws_sub_acty_typ_cd
, substr(g_cache_cwb_rpt_person (i).posted_rating,1,30)
, substr(g_cache_cwb_rpt_person (i).rating_type,1,30)
, substr(g_cache_cwb_rpt_person (i).rating_date,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_job,1,700) --sg
, substr(g_cache_cwb_rpt_person (i).posted_job,1,700) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_job,1,700) --sg
, substr(g_cache_cwb_rpt_person (i).prior_position,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).posted_position,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_position,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).prior_grade,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).posted_grade,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_grade,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).prior_group,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).posted_group,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).proposed_group,1,240) --sg
, substr(g_cache_cwb_rpt_person (i).prior_flex1,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex1,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex1,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex2,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex2,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex2,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex3,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex3,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex3,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex4,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex4,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex4,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex5,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex5,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex5,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex6,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex6,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex6,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex7,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex7,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex7,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex8,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex8,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex8,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex9,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex9,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex9,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex10,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex10,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex10,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex11,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex11,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex11,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex12,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex12,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex12,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex13,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex13,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex13,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex14,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex14,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex14,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex15,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex15,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex15,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex16,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex16,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex16,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex17,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex17,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex17,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex18,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex18,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex18,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex19,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex19,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex19,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex20,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex20,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex20,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex21,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex21,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex21,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex22,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex22,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex22,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex23,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex23,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex23,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex24,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex24,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex24,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex25,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex25,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex25,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex26,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex26,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex26,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex27,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex27,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex27,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex28,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex28,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex28,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex29,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex29,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex29,1,30)
, substr(g_cache_cwb_rpt_person (i).prior_flex30,1,30)
, substr(g_cache_cwb_rpt_person (i).posted_flex30,1,30)
, substr(g_cache_cwb_rpt_person (i).proposed_flex30,1,30)
, substr(g_cache_cwb_rpt_person (i).asgn_change_reason,1,30)
, g_cache_cwb_rpt_person (i).pending_workflow
, 'Y'
, g_cache_cwb_rpt_person (i).prev_eev_screen_entry_value);
INSERT INTO ben_cwb_rpt_detail
(benefit_action_id
, person_rate_id
, person_id
, country_code
, business_group_id
, business_group_name
, status_cd
, lf_evt_closed_flag
, cwb_rpt_detail_id
)
VALUES (benutils.g_benefit_action_id
, -9999
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).person_id
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).country_code
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_id
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).bg_name
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).status
, g_cache_cwb_sum_person (g_cache_cwb_rpt_person (i).person_id).lf_evt_closed
, ben_cwb_rpt_detail_s.NEXTVAL
);
INSERT INTO ben_cwb_rpt_detail (
benefit_action_id,
person_id,
pl_id,
oipl_id,
group_per_in_ler_id,
group_oipl_id,
cwb_rpt_detail_id
)
VALUES (
p_benefit_action_id,
l_table_correction_rec.person_id,
l_table_correction_rec.pl_id,
l_table_correction_rec.oipl_id,
l_table_correction_rec.group_per_in_ler_id,
l_table_correction_rec.group_oipl_id,
ben_cwb_rpt_detail_s.NEXTVAL
);
write_h ('selected per_in_ler_id ' || pil_rec.per_in_ler_id || ' for opening');
ben_person_life_event_api.update_person_life_event
(p_per_in_ler_id => pil_rec.per_in_ler_id
, p_per_in_ler_stat_cd => 'STRTD'
, p_procd_dt => l_procd_dt
, p_voidd_dt => l_voidd_dt
, p_strtd_dt => l_strtd_dt
, p_object_version_number => pil_rec.object_version_number
, p_effective_date => p_effective_date
);
write_h ('selected the group_per_in_ler_id ' || p_group_per_in_ler_id || ' for opening');
ben_person_life_event_api.update_person_life_event
(p_per_in_ler_id => p_group_per_in_ler_id
, p_per_in_ler_stat_cd => 'STRTD'
, p_procd_dt => l_procd_dt
, p_voidd_dt => l_voidd_dt
, p_strtd_dt => l_strtd_dt
, p_object_version_number => l_pil_ovn.object_version_number
, p_effective_date => p_effective_date
);
ben_cwb_person_info_api.update_person_info
(p_group_per_in_ler_id => p_group_per_in_ler_id
, p_post_process_stat_cd => null
, p_object_version_number => l_info_ovn.object_version_number
);
ben_cwb_audit_api.update_per_record(p_per_in_ler_id => p_group_per_in_ler_id
,p_old_val => null
,p_audit_type_cd => 'BOA' );
l_delete_mode boolean;
l_delete_next_change_mode boolean;
l_delete_start_date date;
l_delete_end_date date;
p_delete => l_delete_mode,
p_future_change => l_future_change_mode,
p_delete_next_change => l_delete_next_change_mode); /*,
p_delete_start_date => l_delete_start_date,
p_delete_end_date => l_delete_end_date,
p_del_future_start_date => l_del_future_start_date,
p_del_future_end_date => l_del_future_end_date,
p_del_next_start_date => l_del_next_start_date,
p_del_next_end_date => l_del_next_end_date);*/
,p_dt_delete_mode IN VARCHAR2 default null
,p_amt in number default null ) is
--
l_proc VARCHAR2(72) := 'backout_cwb_element';
l_delete_warning BOOLEAN;
l_dt_delete_mode varchar2(80);
select min(effective_start_date),
max(effective_end_date)
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
select pel.element_link_id,
pel.element_type_id,
pev.input_value_id,
pet.element_name,
pet.processing_type
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.element_entry_value_id = p_element_entry_value_id
and pee.element_entry_id = pev.element_entry_id
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.effective_start_date between pet.effective_start_date
and pet.effective_end_date;
select asg.assignment_id,
asg.payroll_id,
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
pee.original_entry_id,
pee.entry_type,
pee.element_link_id,
pev.screen_entry_value
from per_all_assignments_f asg,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where asg.person_id = p_person_id
and pee.assignment_id = asg.assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and pee.creator_type = 'F'
and pee.entry_type = 'E'
and p_effective_date <= pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = p_element_type_id
and pev.element_entry_id = pee.element_entry_id
and pev.input_value_id = p_input_value_id
and (p_element_entry_value_id is null or
pev.element_entry_value_id = p_element_entry_value_id)
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
order by pee.effective_start_date ;
select object_version_number
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and p_effective_date = pee.effective_end_date;
l_dt_delete_mode := get_ele_dt_del_mode(p_effective_date, l_element_entry_id);
l_dt_delete_mode := hr_api.g_zap;
l_dt_delete_mode := hr_api.g_zap;
l_dt_delete_mode := get_ele_dt_del_mode(l_effective_date, l_element_entry_id);
if l_dt_delete_mode = hr_api.g_future_change then
open c_get_ovn(l_element_entry_id,l_effective_date);
write_h('l_dt_delete_mode = ' || l_dt_delete_mode);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => l_dt_delete_mode
,p_effective_date => l_effective_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning);
procedure delete_elements_and_salary
( p_per_in_ler_id in number
, p_update_summary in boolean
, p_assignment_id in number
, p_warning_text IN OUT NOCOPY VARCHAR2
, p_warning IN OUT NOCOPY BOOLEAN
, p_pay_proposal_id OUT NOCOPY number
, p_element_entry_value_id OUT NOCOPY number
) is
-- CWBGLOBAL
cursor c_cwb_person_rates is
select distinct pl_id, oipl_id, pay_proposal_id
from ben_cwb_person_rates
where group_per_in_ler_id = p_per_in_ler_id
order by pay_proposal_id;
select rates.ELEMENT_ENTRY_VALUE_ID,
rates.COMP_POSTING_DATE,
rates.person_id,
rates.object_version_number,
rates.ws_val,
per_saladmin_utility.get_pay_annualization_factor(info.assignment_id,rates.comp_posting_date,null,'PERIOD') pay_annual_ftr,
dsgn.pl_annulization_factor plan_pay_annual_ftr --12714771
from ben_cwb_person_rates rates,
ben_cwb_person_info info,
ben_cwb_pl_dsgn dsgn
where rates.group_per_in_ler_id = p_per_in_ler_id
and rates.pl_id = l_pl_id
and rates.oipl_id = l_oipl_id
and rates.group_per_in_ler_id = info.group_per_in_ler_id
and dsgn.group_pl_id = info.group_pl_id
and dsgn.group_pl_id = dsgn.pl_id
and dsgn.oipl_id = -1
and dsgn.lf_evt_ocrd_dt = info.lf_evt_ocrd_dt;
select object_version_number, business_group_id
from per_pay_proposals
where pay_proposal_id = l_pay_proposal_id;
select proposed_salary_n
from per_pay_proposals
where pay_proposal_id = l_pay_proposal_id;
select screen_entry_value from pay_element_entry_values_f
where element_entry_value_id = l_element_entry_val_id;
select object_version_number
from ben_cwb_person_info
where group_per_in_ler_id = p_per_in_ler_id;
select
nvl(plRt.ws_val,0) ws_val
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = p_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1
and pl.ws_sub_acty_typ_cd = 'ICM7';
select
(per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
(per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
(per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates plRt
,ben_cwb_person_info per
where plRt.group_per_in_ler_id = p_per_in_ler_id
and plRt.group_per_in_ler_id = per.group_per_in_ler_id
and plRt.pl_id = pl.pl_id
and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and plRt.oipl_id = pl.oipl_id
and pl.oipl_id = -1;
select
nvl(optRt.ws_val,0) ws_val
from
ben_cwb_pl_dsgn pl
,ben_cwb_person_rates optRt
,ben_cwb_person_info per
where optRt.group_per_in_ler_id = p_per_in_ler_id
and optRt.group_per_in_ler_id = per.group_per_in_ler_id
and optRt.pl_id = pl.pl_id
and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and optRt.oipl_id = pl.oipl_id
and pl.oipl_id <> -1
and pl.oipl_ordr_num = l_oipl_ordr_num
and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
l_proc varchar2(50) := g_package||'.cwb_delete_routine';
g_proc := 'delete_elements_and_salary';
g_actn := 'processing delete_elements_and_salary routine...';
write('This employee had a recent update to Salary or Pay Basis after postprocess. Hence could not back-out this employee');
-- Delete element entry if attached to rate row.
--
write_h('l_element_entry_value_id = ' || l_ELEMENT_ENTRY_VALUE_ID);
write('This employee had a recent update to Element after postprocess. Hence could not back-out this employee');
-- has pay_proposal_id populated and this being same ID, we should not call delete API
-- more than once. Hence added following check : l_pay_proposal_id <> l_pay_proposal_id_prev
--
if l_pay_proposal_id is not null AND
l_pay_proposal_id <> l_pay_proposal_id_prev
then
--
write('Calling hr_maintain_proposal_api.delete_salary_proposal');
hr_maintain_proposal_api.delete_salary_proposal
( p_pay_proposal_id => l_pay_proposal_id
,p_business_group_id => l_business_group_id_prop
,p_object_version_number => l_object_version_number_prop
,p_validate => false
,p_salary_warning => l_salary_warning ) ;
write('Finished delete_elements_and_salary routine...');
WRITE('Raising error in delete_elements_and_salary');
WRITE('Error at delete_elements_and_salary');
END delete_elements_and_salary;
,p_txn_rec in ben_cwb_asg_update.g_txn%rowtype
,p_business_group_id in number
,p_audit_log in varchar2 default 'N'
,p_process_status in out nocopy varchar2
,p_group_per_in_ler_id in number default null
,p_effective_date in date) is
l_proc varchar2(80) := g_package || '.backout_rating';
l_update_event_id number;
l_update_review_id number;
select perf.performance_review_id
,perf.event_id
,perf.object_version_number
from per_performance_reviews perf
where perf.person_id = p_person_id
and perf.review_date = l_perf_date;
select evt.type,
evt.object_version_number
from per_events evt
where evt.assignment_id = p_txn_rec.assignment_id
and evt.date_start = l_perf_date
and evt.event_id = l_event_id;
l_update_review_id := l_performance_review_id;
hr_perf_review_api.delete_perf_review(
p_validate => false
,p_performance_review_id => l_performance_review_id
,p_object_version_number => l_perf_ovn);
l_update_review_id := null;
l_update_event_id := l_event_id;
per_events_api.delete_event(
p_validate => false
,p_event_id => l_event_id
,p_object_version_number => l_evt_ovn);
l_update_event_id := null;
update ben_cwb_person_info
set new_perf_event_id = l_update_event_id,
new_perf_review_id = l_update_review_id
where group_per_in_ler_id = p_group_per_in_ler_id;
l_perf_txn ben_cwb_asg_update.g_txn%ROWTYPE;
l_asg_txn ben_cwb_asg_update.g_txn%ROWTYPE;
select pet.element_name,
pet.processing_type
from pay_element_types_f pet
where pet.element_type_id = element_id;
select min(comp_posting_date)
from ben_cwb_person_rates
where group_per_in_ler_id = v_group_per_in_ler_id
and comp_posting_date is not null ;
SAVEPOINT delete_elements_and_salary;
write_m ('Time before processing delete_elements_and_salary '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
ROLLBACK TO delete_elements_and_salary;
delete_elements_and_salary (p_per_in_ler_id => p_group_per_in_ler_id
, p_update_summary => false
, p_assignment_id => asg_rec.assignment_id
, p_warning_text => l_warning_text
, p_warning => l_error
, p_pay_proposal_id => l_pay_proposal_id
, p_element_entry_value_id => l_element_entry_value_id
);
write_m ('Time after processing delete_elements_and_salary '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
WRITE('Error in delete_elements_and_salary : '||SQLERRM);
ROLLBACK TO delete_elements_and_salary;
OPEN ben_cwb_asg_update.g_txn (l_emp_num_and_emp_name.assignment_id,
ben_cwb_asg_update.g_ws_perf_rec_type||to_char(l_perf_revw_strt_dt, 'yyyy/mm/dd')
||l_interview_typ_cd);
FETCH ben_cwb_asg_update.g_txn INTO l_perf_txn;
CLOSE ben_cwb_asg_update.g_txn;
write ('Found a rank which needs to be deleted...');
hr_assignment_extra_info_api.delete_assignment_extra_info(
p_validate => false
,p_assignment_extra_info_id => l_ranking_info.assignment_extra_info_id
,p_object_version_number => l_ranking_info.object_version_number
);
WRITE ('Found a rank which need not be deleted...');
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
, p_action_status_cd => 'P'
, p_object_version_number => p_object_version_number
, p_effective_date => p_effective_date
);
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
, p_action_status_cd => 'E'
, p_object_version_number => p_object_version_number
, p_effective_date => p_effective_date
);
write_h (' and Ending Person Action id ' || l_end_person_action_id || ' is selected');
UPDATE ben_batch_ranges ran
SET ran.range_status_cd = 'P'
WHERE ran.range_id = l_range_id;
g_cache_person_process.DELETE;
WRITE ('Number of Persons selected in this range ' || g_cache_person_process.COUNT);
, p_person_selection_rule_id IN NUMBER DEFAULT NULL
, p_is_backout_perf IN VARCHAR2 DEFAULT 'N'
, p_local_plan_list IN VARCHAR2 DEFAULT NULL
, p_is_open_access IN VARCHAR2 DEFAULT 'N'
, p_send_fyi IN VARCHAR2 DEFAULT 'N'
)
IS
--
-- local variable declaration.
--
l_effective_date DATE;
ps_rec c_person_selection%ROWTYPE;
pl_rec c_placeholder_selection%ROWTYPE;
write_h ('||p_person_select_rule_id -' || p_person_selection_rule_id);
g_exec_param_rec.persons_selected := 0;
, p_program_update_date => SYSDATE
, p_bft_attribute1 => l_process_compents
, p_bft_attribute3 => p_employees_in_bg
, p_bft_attribute4 => p_manager_id
, p_bft_attribute5 => p_person_selection_rule_id
, p_bft_attribute6 => p_is_backout_perf
, p_bft_attribute7 => p_is_open_access
, p_bft_attribute8 => p_send_fyi
);
g_actn := 'Inserting Person Actions...';
write_m ('Time before processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
OPEN c_placeholder_selection (p_pl_id
, l_lf_evt_orcd_date
, p_person_id
, p_manager_id
, p_employees_in_bg
, l_effective_date
, p_local_plan_list
);
FETCH c_placeholder_selection
INTO pl_rec;
EXIT WHEN c_placeholder_selection%NOTFOUND;
If p_person_selection_rule_id is not NULL then
--
begin
ben_batch_utils.person_selection_rule
(p_person_id => l_person_id
,p_business_group_id => pl_rec.business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
insert_person_actions (p_per_actn_id_array => l_person_action_ids
, p_per_id => l_person_ids
, p_group_per_in_ler_id => l_per_in_ler_ids
, p_benefit_action_id => l_benefit_action_id
, p_is_placeholder => l_is_placeholder
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_per_in_ler_ids.DELETE;
l_is_placeholder.DELETE;
CLOSE c_placeholder_selection;
OPEN c_person_selection (p_pl_id
, l_lf_evt_orcd_date
, p_person_id
, p_manager_id
, p_employees_in_bg
, l_effective_date
, p_local_plan_list
);
FETCH c_person_selection
INTO ps_rec;
EXIT WHEN c_person_selection%NOTFOUND;
If p_person_selection_rule_id is not NULL then
--
begin
ben_batch_utils.person_selection_rule
(p_person_id => l_person_id
,p_business_group_id => ps_rec.business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date
,p_return => l_person_ok
,p_err_message => l_err_message );
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
insert_person_actions (p_per_actn_id_array => l_person_action_ids
, p_per_id => l_person_ids
, p_group_per_in_ler_id => l_per_in_ler_ids
, p_benefit_action_id => l_benefit_action_id
, p_is_placeholder => l_is_placeholder
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_per_in_ler_ids.DELETE;
l_is_placeholder.DELETE;
CLOSE c_person_selection;
g_person_selected := l_num_rows;
WRITE ('Total no of person selected - ' || g_person_selected);
g_actn := 'Inserting the last range of persons if exists...';
write_m ('Time after processing the person selections '||to_char(sysdate,'yyyy/mm/dd:hh:mi:ssam'));
insert_person_actions (p_per_actn_id_array => l_person_action_ids
, p_per_id => l_person_ids
, p_group_per_in_ler_id => l_per_in_ler_ids
, p_benefit_action_id => l_benefit_action_id
, p_is_placeholder => l_is_placeholder
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_per_in_ler_ids.DELETE;
l_is_placeholder.DELETE;
ben_batch_utils.g_processes_tbl.DELETE;
WRITE ('<< No Person got selected with above selection criteria >>');
write_h ('||p_person_selected- ' || l_num_persons);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);
, p_person_selected => l_num_persons
, p_business_group_id => p_bg_id
);