[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
for reducing the data inserts.
27 Feb 04 ikasire 115.19 Added cursor to generate and pass
person selection rule in different
business groups
03 Mar 04 ikasire 115.20 Bug 3482033 fixes
22 Mar 04 pbodla 115.21 Bug 3517726 : Do not consider any
data which is not deleted by backout.
06 Apr 04 pbodla 115.22 Added the code to submit the reports
28 Apr 04 pbodla 115.23 Added the code to copy budget
columns from cwb_person_rates to
cwb_person_groups.
06 May 04 pbodla 115.24 Added procedure del_all_cwb_pils
to delete all data if person is
ineligible and track ineligible
flag is N.
26 May 04 pbodla 115.25 Added cursor get_per_info
to be used for online calls.
10 Jun 04 maagrawa 115.26 Pass null as effective_date to
ben_cwb_pl_dsgn_pkg and
ben_cwb_person_info_pkg
12 Jul 04 pbodla 115.27 Bug 3748539: This situation indicates
it is a recursive supervisory heirarchy.
13 Jul 04 pbodla 115.28 Bug 3748539: Added MGRPERSONID token.
20 Jul 04 pbodla 115.29 Added logic for single person run.
p_single_per_clone_all_data
21 Jul 04 pbodla 115.30 Added p_use_eff_dt_flag to
global_online_process_w and other
procedures, if front end
takes the decision to take full control
to clone data then this flag is passed
as Y.
08 oct 04 pbodla 115.31 Added extra parameters to
global_online_process_w, to handle
backout.
11 oct 04 pbodla 115.32 passed correct date parameters to
global_online_process_w
01 Nov 04 pbodla 115.33 Added procedure
sum_oipl_rates_and_upd_pl_rate
Bug : 3968065
03 Nov 04 pbodla 115.34 modified procedure to handle nulls
sum_oipl_rates_and_upd_pl_rate
17 Nov 04 pbodla 115.35 Bug 3510081 : data model changes
and code changes for auto allocation
of budgets.
01 Dec 04 pbodla 115.36 ACCESS_CD in ben_cwb_person_tasks
is populated with UP
06-Dec-04 bmanyam 115.37 Bug: 3979082. Use AME Hierarchy to
fetch manager hierarchy.
06-Dec-04 bmanyam 115.38 Bug: 3979082. Use RULE to
fetch manager hierarchy.
10-Dec-04 pbodla 115.39 Modified p_single_per_clone_all_data
commented the code which nullifies l_ws_mgr_id
if mgr per_in_ler not found.
14-Dec-04 pbodla 115.40 bug 4040013 - Modified cursor c_oipl_exists
to check ws rate at plan level also.
23-Dec-04 pbodla 115.41 bug 4052530 - initialisation of
g_group_per_in_ler is moved
within p_single_per_clone_all proc.
27-Dec-04 pbodla 115.42 Added initialization of globals to
global_online_process_w procedure
06-jan-05 pbodla 115.43 Pop cd is populated only for
auto allocation of budgets and at
plan level only.
24-jan-05 nhunur 115.44 4128034: Pass correct assignment id to the RL
21-Feb-05 pbodla 115.45 bug 4198404 - Added record structure
g_error_log_rec_type to log proper
error messages.
Commit data after refresh_pl_dsgn
only if the call is from conc manager
17-May-05 pbodla 115.46 Added performance hint for cursor
c_person_rates
27-May-05 pbodla 115.47 Bug 4399281 : Assume recursive heirarchy
: A reports to B, B reports to
C, C Reports to B. This scenario is not caught by error
BEN_94020_RECURSIVE_EMP_HEIRAR if person A is picked up
in heirarchy build first.
By adding check l_level > 75 infinite loop is broken and
when person B is picked up to build heirarchy above error
is raised.
14-Jun-05 kmahendr 115.48 Bug#4258200 - l_copy_person_bdgt_count initialised
to 0
20-Jun-05 pbodla 115.49 Bug#4258200 - Data from copy_ attributes are
not copied over for cross business group data.
17-aug-05 pbodla 115.50 Bug#4547916 - Even if manager is processed still
do not re create the group pil data.
Also allow creating heirarchy data based on processed pils.
28-Sep-05 tjesumic 115.51 audit_log_flag support 4 values
Y Log Yes Report YES
N Log NO Report YES
NN Log NO Report NO
YN Log Yes Report No
17-aug-05 pbodla 115.52 Bug#4720746 - typing error corrected.
28-Nov-05 maagrawa 115.53 4766589. Default person task's access
to task definition's hidden_cd.
29-dec-05 pbodla 115.54 PERF 4587770 : Added performance related
changes. Some of the potential code chages are
in comments as, we are waiting for GSI to
validate the fixes. Once GSI gets back
with results, this code can be merged.
03-Jan-06 nhunur 115.55 cwb - changes for person type param.
29-dec-05 pbodla 115.56 Enhancement to handle the recursive
heirarchy properly, so that string containing
personid's will be printed.
Process will not error out, it will
continue building the heirarchy.
Users have to use admin page and reassign
one employee who is in recursive relationship.
23-jan-06 pbodla 115.57 Enhancement to handle the recursive
heirarchy in online run. Added error
message - BEN_94537_REC_REPORTING
08-Feb-06 abparekh 115.58 Bug 4875181 - Added p_run_rollup_only to process
115.59 only Rollup Processes
07-Feb-06 mmudigon 115.59 CWB Multi currency support. Added
procs() exec_element_det_rl and
get_abr_ele_inp
determine_curr_code
17-Feb-06 pbodla/stee115.59 CWB Multi currency support. Added
determine_curr_code
21-Feb-06 pbodla 115.60 currency col populated in ben_cwb_person_rates.
28-Feb-06 pbodla 115.62 Fix currency_det_cd for salary basis and standard
rates.
04-Mar-06 maagrawa 115.63 Include call for exchange rate creation.
24-Mar-06 maagrawa 115.64 GSCC nocopy error.
27-Mar-06 stee 115.65 Populate currency when cloning
person data - Bug 5104388.
07-Apr-06 swjain 115.67 Bug 5141153: Updated procedure exec_element_det_rl
21-Apr-06 ikasired 115.68 5148387 handling for benefit assignment
26-Apr-06 maagrawa 115.70 4636102:Error getting killed in
online mode also.
12-May-06 bmanyam 115.71 Text for BEN_94537_REC_REPORTING changed by anadi.
So, the corresponding log-file buff is also
changed.
12-May-06 bmanyam 115.72 -- do --
22-May-06 pbodla 115.73 Bug 5232223 - Added code to handle the trk inelig flag
If trk inelig flag is set to N at group plan level
then do not create cwb per in ler and all associated data.
22-Jun-06 rbingi 115.74 Bug 5232223 - Calling del_all_cwb_pils when elpros attacthed to
local plan.
26-Jun-06 rbingi 115.75 Contd.5232223.
18-Oct-06 maagrawa 115.76 4587770.Tuned c_no_0_hrchy
01-dec-06 ssarkar 115.77 5124534 : modified popu_missing_person_pil
13-dec-06 ssarkar 115.78 5124534/5702794 : populate cwb_group_persons with pl_id/oipl_id
20-Feb-07 maagrawa 115.79 Further tuned c_no_0_hrchy. Use
ben_cwb_person_info instead of
ben_per_in_ler.
04-Jun-07 maagrawa 115.80 Further tuned c_no_0_hrchy. Check only
level 1 hierarchy.
24-Sep-08 sgnanama 115.81 7393142: process for terminated employee
19-Feb-09 sgnanama 120.31.12010000.3 ER: added logic to copy integrator
14-Jul-11 naramasa 120.31.12010000.4 Bug 12717230 - performance issue
while reassigning worksheet manager via emp admin
20-Dec-11 kgowripe 120.31.12010000.7 Bug#13436491 Modified cursors in auto_allocate_budgets method
for improving performance. Also added gather table stats
in procedure global_process.
15-Mar-13 sgnanama 120.31.x.8 16417003 bug fix, introduced sleep time in check_slaves_status procedure
*/
--------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_manage_cwb_life_events';
select phase_code,
status_code
from fnd_concurrent_requests fnd
where fnd.request_id = c_request_id;
select
parameter_value
from pay_action_parameters
where parameter_name = 'BEN_CWB_SLEEP_SEC';
select 'Y'
from ben_benefit_actions bft
where bft.benefit_action_id = p_benefit_action_id
and bft.request_id = fnd_global.conc_request_id;
select element_det_rl
from ben_acty_base_rt_f
where acty_base_rt_id = p_acty_base_rt_id
and p_effective_date between effective_start_date
and effective_end_date;
select input_currency_code
from pay_element_types_f
where element_type_id = l_element_type_id
and p_effective_date between effective_start_date
and effective_end_date;
select et.input_currency_code
from pay_element_types_f et
,per_pay_bases pb
,pay_input_values_f iv
,per_all_assignments_f asg
where asg.pay_basis_id = pb.pay_basis_id
and pb.input_value_id = iv.input_value_id
and iv.element_type_id = et.element_type_id
and asg.assignment_id = p_assignment_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
and p_effective_date between
et.effective_start_date and et.effective_end_date;
select pln.nip_pl_uom
from ben_pl_f pln
where pln.pl_id = p_pl_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select /*+ leading(MGR_INFO_0) use_nl(HRH_0)*/ unique hrh_0.mgr_per_in_ler_id
-- Bug 12717230
from ben_cwb_group_hrchy hrh_0,
ben_cwb_person_info mgr_info_0
where mgr_info_0.group_per_in_ler_id = hrh_0.emp_per_in_ler_id
and mgr_info_0.group_pl_id = p_pl_id
and mgr_info_0.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and hrh_0.lvl_num = 1
and not exists
( select /*+ nl_aj */ 'Y' -- Bug 12717230
from ben_cwb_group_hrchy hrh
where hrh.mgr_per_in_ler_id = hrh_0.mgr_per_in_ler_id
and hrh.emp_per_in_ler_id = hrh_0.mgr_per_in_ler_id
and hrh.lvl_num = 0
);
select
cwb.emp_per_in_ler_id,
pil.ws_mgr_id,
pil.person_id
from
ben_cwb_group_hrchy cwb,
ben_per_in_ler pil
where
cwb.mgr_per_in_ler_id = -1
and pil.per_in_ler_id = cwb.emp_per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.group_pl_id = cv_pl_id
and cwb.lvl_num = -1;
select full_name
from per_all_people_f per
where person_id = cv_person_id
and cv_lf_evt_ocrd_dt between effective_start_date
and effective_end_date;
select pil.ws_mgr_id,
pil.per_in_ler_id
from ben_per_in_ler pil
where pil.group_pl_id = p_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.ler_id = p_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD'); -- gsi also consider processed pils
procedure insert_mgr_hrchy ( p_emp_per_in_ler_id number,
p_mgr_per_in_ler_id number,
p_lvl_num number ) is
begin
--
if g_debug then
hr_utility.set_location('insert_mgr_hrchy p_emp_per_in_ler_id '
||p_emp_per_in_ler_id,10);
hr_utility.set_location('insert_mgr_hrchy p_mgr_per_in_ler_id '
||p_mgr_per_in_ler_id || ' lvl = '
|| p_lvl_num, 20);
insert into ben_cwb_group_hrchy (
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num )
values (
p_emp_per_in_ler_id,
p_mgr_per_in_ler_id,
p_lvl_num );
end insert_mgr_hrchy;
procedure update_init_pil(cv_pl_id number, cv_lf_evt_ocrd_dt date) is
--
-- CWB bug : 2712602
--
cursor c_cwh is
select rowid
from ben_cwb_group_hrchy cwh
where cwh.lvl_num = -1 and
cwh.mgr_per_in_ler_id = -1
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and exists
(select null
from ben_per_in_ler pil
where pil.per_in_ler_id = cwh.emp_per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.group_pl_id = cv_pl_id
) ;
delete
from ben_cwb_group_hrchy cwh
where (( cwh.lvl_num = -1
and cwh.mgr_per_in_ler_id = -1) OR
( cwh.lvl_num = 0 and
cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )
and not exists
(select null
from ben_cwb_group_hrchy cwh1
where cwh1.mgr_per_in_ler_id = cwh.emp_per_in_ler_id
and cwh1.lvl_num <> 0
)
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and exists
(select null
from ben_per_in_ler pil
where pil.per_in_ler_id = cwh.emp_per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.group_pl_id = cv_pl_id
) ;
select cwh.rowid
from ben_per_in_ler pil, ben_cwb_group_hrchy cwh
where pil.per_in_ler_id = cwh.emp_per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.group_pl_id = cv_pl_id
and (( cwh.lvl_num = -1
and cwh.mgr_per_in_ler_id = -1) OR
( cwh.lvl_num = 0 and
cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )
and not exists
(select null
from ben_cwb_group_hrchy cwh1
where cwh1.mgr_per_in_ler_id = cwh.emp_per_in_ler_id
and cwh1.lvl_num <> 0
);
update ben_cwb_group_hrchy cwh
set cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id,
cwh.lvl_num = 0
where cwh.lvl_num = -1
and cwh.mgr_per_in_ler_id = -1
and cwh.rowid = l_cwh.rowid;
delete from ben_cwb_group_hrchy where rowid = l_cwh.rowid;null;
g_hrchy_tbl.delete;
insert_mgr_hrchy(l_emp_pil,l_mgr_pil,l_level);
hr_utility.set_location('Before call to delete_init_pil',10);
update_init_pil(lv_pl_id, lv_lf_evt_ocrd_dt) ;
hr_utility.set_location('After call to delete_init_pil',10);
delete from ben_cwb_group_hrchy
where emp_per_in_ler_id in (
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.group_pl_id = lv_pl_id
and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
and pil.per_in_ler_stat_cd = 'BCKDT');
delete from ben_cwb_group_hrchy
where mgr_per_in_ler_id in (
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.group_pl_id = lv_pl_id
and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
and pil.per_in_ler_stat_cd = 'BCKDT');
insert into ben_cwb_group_hrchy (
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num )
values (
l_no_0_hrchy.mgr_per_in_ler_id,
l_no_0_hrchy.mgr_per_in_ler_id,
0 );
select pet.pl_id, enp.ASND_LF_EVT_DT,
pet.business_group_id, enp.ler_id,
enp.hrchy_to_use_cd,
enp.pos_structure_version_id,
enp.dflt_ws_acc_cd,
enp.end_dt,
enp.auto_distr_flag,
enp.ws_upd_strt_dt,
enp.ws_upd_end_dt,
enp.uses_bdgt_flag,
enp.hrchy_ame_trn_cd,
enp.hrchy_rl,
-- Bug 5232223
group_pln.trk_inelig_per_flag
from ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp,
ben_ler_f ler,
ben_pl_f pln,
ben_pl_f group_pln
where enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and pet.business_group_id = enp.business_group_id
and cv_lf_evt_ocrd_dt
between pet.effective_start_date
and pet.effective_end_date
and ler.typ_cd = 'COMP'
and ler.business_group_id = pet.business_group_id
and cv_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date
and ler.ler_id = enp.ler_id
and pet.pl_id = group_pln.pl_id
and cv_lf_evt_ocrd_dt
between group_pln.effective_start_date
and group_pln.effective_end_date
and pln.group_pl_id = group_pln.pl_id
and pln.pl_id = cv_pl_id
and cv_lf_evt_ocrd_dt
between pln.effective_start_date
and pln.effective_end_date;
select pet.pl_id,
enp.ASND_LF_EVT_DT,
pet.business_group_id, enp.ler_id,
enp.hrchy_to_use_cd,
enp.pos_structure_version_id,
enp.dflt_ws_acc_cd,
enp.end_dt,
enp.auto_distr_flag,
enp.ws_upd_strt_dt,
enp.ws_upd_end_dt,
enp.uses_bdgt_flag,
enp.hrchy_ame_trn_cd,
enp.hrchy_rl,
-- Bug 5232223
group_pln.trk_inelig_per_flag
from ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp,
ben_ler_f ler,
ben_pl_f group_pln
where enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
-- and pet.business_group_id = enp.business_group_id
and cv_lf_evt_ocrd_dt
between pet.effective_start_date
and pet.effective_end_date
and ler.typ_cd = 'COMP'
-- and ler.business_group_id = pet.business_group_id
and cv_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date
and ler.ler_id = enp.ler_id
and pet.pl_id = group_pln.pl_id
and cv_lf_evt_ocrd_dt
between group_pln.effective_start_date
and group_pln.effective_end_date
and group_pln.pl_id = cv_pl_id
and cv_lf_evt_ocrd_dt
between group_pln.effective_start_date
and group_pln.effective_end_date;
select count(*)
from ben_pl_f
where group_pl_id = cv_group_pl_id
and cv_lf_evt_ocrd_dt between effective_start_date and
effective_end_date;
select emp_per_in_ler_id
from ben_cwb_group_hrchy
where emp_per_in_ler_id = cv_emp_pil_id;
select *
from ben_cwb_wksht_grp
where PL_ID = cv_PL_ID
and STATUS_CD = 'A';
select pli_information1
from ben_pl_extra_info
where pl_id = p_group_pl_id
and information_type = 'CWB_CUSTOM_DOWNLOAD';
insert into ben_cwb_group_hrchy (
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num,
OBJECT_VERSION_NUMBER )
values(
p_group_per_in_ler_id,
-1,
-1,
1);
insert into ben_cwb_person_tasks
(GROUP_PER_IN_LER_ID
,TASK_ID
,GROUP_PL_ID
,LF_EVT_OCRD_DT
,STATUS_CD
,ACCESS_CD
,OBJECT_VERSION_NUMBER)
values
(p_group_per_in_ler_id,
l_cwb_tasks.CWB_WKSHT_GRP_ID,
p_group_pl_id,
p_group_lf_evt_ocrd_dt,
'NS',
nvl(l_cwb_tasks.hidden_cd, 'UP'),
1
);
SELECT assignment_id, position_id, organization_id, supervisor_id, business_group_id
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND primary_flag = 'Y'
AND assignment_type IN ('E', 'C','B') -- Bug 2827121 --Bug 5148387
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
order by decode(assignment_type,'E',1,'C',2,3);
SELECT parent_position_id
FROM per_pos_structure_elements
WHERE subordinate_position_id = p_position_id
AND pos_structure_version_id = p_pos_structure_version_id;
SELECT person_id
FROM per_all_assignments_f ass,
per_assignment_status_types ast
WHERE ass.position_id = p_position_id
AND ass.primary_flag = 'Y'
AND ass.assignment_type IN ('E', 'C') -- Bug 2827121
AND p_effective_date BETWEEN ass.effective_start_date
AND ass.effective_end_date
--Bug 3044311 -- Need to verify what other system types should be considered.
AND ass.assignment_status_type_id = ast.assignment_status_type_id
-- and ast.active_flag = 'Y'
AND ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
SELECT enrtp.enrt_perd_id,
enrtp.strt_dt,
enrtp.end_dt,
enrtp.procg_end_dt,
enrtp.dflt_enrt_dt,
petc.enrt_typ_cycl_cd,
enrtp.cls_enrt_dt_to_use_cd,
enrtp.hrchy_to_use_cd,
enrtp.pos_structure_version_id,
enrtp.enrt_perd_det_ovrlp_bckdt_cd
FROM ben_popl_enrt_typ_cycl_f petc,
ben_enrt_perd enrtp,
ben_ler_f ler
WHERE petc.pl_id = l_pl_id
AND petc.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
AND petc.effective_end_date
AND petc.enrt_typ_cycl_cd <> 'L'
AND enrtp.business_group_id = p_business_group_id
AND enrtp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
and ler.ler_id (+) = enrtp.ler_id
and ler.ler_id (+) = p_ler_id
and l_lf_evt_ocrd_dt between ler.effective_start_date (+)
and ler.effective_end_date (+);
select pel.pil_elctbl_chc_popl_id,
pel.object_version_number pel_ovn
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = cv_per_in_ler_id;
select epe.elig_per_elctbl_chc_id,
epe.object_version_number epe_ovn
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = cv_per_in_ler_id
and cv_pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id;
select pl.trk_inelig_per_flag
from ben_pl_f pl
where pl.pl_id = p_group_pl_id
and cv_effective_date between pl.effective_start_date and
pl.effective_end_date;
select pil.per_in_ler_id,
pil.object_version_number pil_ovn,
pil.business_group_id,
ptnl.ptnl_ler_for_per_id,
ptnl.object_version_number ptnl_ovn
from ben_per_in_ler pil,
ben_ptnl_ler_for_per ptnl,
ben_cwb_person_info cpi
where pil.group_pl_id = cv_group_pl_id
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.per_in_ler_stat_cd = 'STRTD'
and cpi.group_per_in_ler_id = pil.per_in_ler_id
and cpi.person_id = -1
and ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id
and ptnl.ptnl_ler_for_per_stat_cd = 'PROCD'
and not exists
(select 'Y'
from ben_cwb_group_hrchy hrh
where hrh.mgr_per_in_ler_id = pil.per_in_ler_id
and hrh.lvl_num > 0)
and not exists
(select 'Y'
from ben_cwb_person_rates
where group_pl_id = cv_group_pl_id
and person_id = pil.person_id
and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and elig_flag = 'Y');
* Delete data from ben_cwb_person_rates, ben_cwb_person_groups,
* ben_per_in_ler, ben_ptnl_ler_for_per, ben_pil_elctbl_chc_popl,
* ben_elig_per_elctbl_chc, ben_group_heirarchy,
* ben_cwb_person_tasks.
*/
if g_debug then
l_package := g_package||'.del_all_cwb_pils';
ben_elig_per_elc_chc_api.delete_ELIG_PER_ELC_CHC
(p_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id,
p_object_version_number => l_epe_rec.epe_ovn,
p_effective_date => p_group_lf_evt_ocrd_dt);
ben_Pil_Elctbl_chc_Popl_api.delete_Pil_Elctbl_chc_Popl
(p_pil_elctbl_chc_popl_id => l_popl_rec.pil_elctbl_chc_popl_id,
p_object_version_number => l_popl_rec.pel_ovn,
p_effective_date => p_group_lf_evt_ocrd_dt);
ben_cwb_back_out_conc.delete_cwb_data(
p_per_in_ler_id => del_inelg_per_rec.per_in_ler_id
,p_business_group_id => del_inelg_per_rec.business_group_id
);
ben_Person_Life_Event_api.delete_Person_Life_Event
(p_per_in_ler_id => del_inelg_per_rec.per_in_ler_id,
p_object_version_number => del_inelg_per_rec.pil_ovn,
p_effective_date => p_group_lf_evt_ocrd_dt);
ben_ptnl_ler_for_per_api.delete_ptnl_ler_for_per
(p_ptnl_ler_for_per_id => del_inelg_per_rec.ptnl_ler_for_per_id,
p_object_version_number => del_inelg_per_rec.ptnl_ovn,
p_effective_date => p_group_lf_evt_ocrd_dt);
select ler.ler_id
from ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp,
ben_ler_f ler
where enp.business_group_id = cv_business_group_id
and enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and pet.business_group_id = enp.business_group_id
and cv_effective_date between pet.effective_start_date
and pet.effective_end_date
and ler.typ_cd = 'COMP'
and ler.business_group_id = pet.business_group_id
and cv_effective_date between ler.effective_start_date
and ler.effective_end_date
and ler.ler_id = enp.ler_id
and pet.pl_id = cv_pl_id;
select cpg.*
from ben_cwb_person_groups cpg
where cpg.group_pl_id = c_group_pl_id
and cpg.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and cpg.group_per_in_ler_id =
(select cpg1.group_per_in_ler_id
from ben_cwb_person_groups cpg1
where cpg1.group_pl_id = c_group_pl_id
and cpg1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and rownum = 1);
select cpr.rowid, cpr.*
from ben_cwb_person_rates cpr
where cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and cpr.group_pl_id = c_group_pl_id
and cpr.pl_id = c_pl_id
and cpr.person_id =
(select cpr1.person_id
from ben_cwb_person_rates cpr1
where cpr1.group_pl_id = c_group_pl_id
and cpr1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and cpr1.pl_id = c_pl_id
and rownum = 1);
select asg.assignment_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and p_effective_date
between asg.effective_start_date
and asg.effective_end_date
order by asg.assignment_type desc;
select per_in_ler_id
from ben_per_in_ler
where person_id = cv_person_id
and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and ler_id = cv_group_ler_id
and per_in_ler_stat_cd = 'STRTD';
p_program_update_date => sysdate);
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
insert into ben_cwb_person_groups
(group_per_in_ler_id,
group_pl_id ,
group_oipl_id ,
lf_evt_ocrd_dt ,
bdgt_pop_cd ,
due_dt ,
access_cd ,
approval_cd ,
approval_date ,
approval_comments ,
submit_cd ,
submit_date ,
submit_comments ,
dist_bdgt_val ,
ws_bdgt_val ,
rsrv_val ,
dist_bdgt_mn_val ,
dist_bdgt_mx_val ,
dist_bdgt_incr_val ,
ws_bdgt_mn_val ,
ws_bdgt_mx_val ,
ws_bdgt_incr_val ,
rsrv_mn_val ,
rsrv_mx_val ,
rsrv_incr_val ,
dist_bdgt_iss_val ,
ws_bdgt_iss_val ,
dist_bdgt_iss_date ,
ws_bdgt_iss_date ,
ws_bdgt_val_last_upd_date ,
dist_bdgt_val_last_upd_date ,
rsrv_val_last_upd_date ,
ws_bdgt_val_last_upd_by ,
dist_bdgt_val_last_upd_by ,
rsrv_val_last_upd_by ,
object_version_number /* ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date */
) values (
l_curr_per_in_ler_id,
l_cpg_rec.group_pl_id ,
nvl(l_cpg_rec.group_oipl_id, -1) ,
l_cpg_rec.lf_evt_ocrd_dt ,
null, -- bdgt_pop_cd
null, -- l_cpg_rec.due_dt,
g_cache_group_plan_rec.access_cd, -- l_cpg_rec.access_cd,
null, -- approval_cd
null, -- approval_date
null, -- approval_comments
'NS', -- submit_cd
null, -- submit_date
null, -- submit_comments
null, -- l_copy_dist_bdgt_val,
null, -- l_copy_ws_bdgt_val,
null, -- l_copy_rsrv_val,
null, -- l_copy_dist_bdgt_mn_val,
null, -- l_copy_dist_bdgt_mx_val,
null, -- l_copy_dist_bdgt_incr_val,
null, -- l_copy_ws_bdgt_mn_val,
null, -- l_copy_ws_bdgt_mx_val,
null, -- l_copy_ws_bdgt_incr_val,
null, -- l_copy_rsrv_mn_val,
null, -- l_copy_rsrv_mx_val,
null, -- l_copy_rsrv_incr_val,
null, -- l_copy_dist_bdgt_iss_val,
null, -- l_copy_ws_bdgt_iss_val,
null, -- l_copy_dist_bdgt_iss_date,
null, -- l_copy_ws_bdgt_iss_date,
null, -- l_cpg_rec.ws_bdgt_val_last_upd_date ,
null, -- l_cpg_rec.dist_bdgt_val_last_upd_date ,
null, -- l_cpg_rec.rsrv_val_last_upd_date ,
null, -- l_cpg_rec.ws_bdgt_val_last_upd_by ,
null, -- l_cpg_rec.dist_bdgt_val_last_upd_by ,
null, -- l_cpg_rec.rsrv_val_last_upd_by ,
1-- , -- object_version_number
/*
l_cpg_rec.last_update_date ,
l_cpg_rec.last_updated_by ,
l_cpg_rec.last_update_login ,
l_cpg_rec.created_by ,
l_cpg_rec.creation_date
*/
) ;
insert into ben_cwb_person_rates
(person_rate_id ,
group_per_in_ler_id ,
pl_id ,
oipl_id ,
group_pl_id ,
group_oipl_id ,
lf_evt_ocrd_dt ,
person_id ,
assignment_id ,
elig_flag ,
ws_val ,
ws_mn_val ,
ws_mx_val ,
ws_incr_val ,
elig_sal_val ,
stat_sal_val ,
oth_comp_val ,
tot_comp_val ,
misc1_val ,
misc2_val ,
misc3_val ,
rec_val ,
rec_mn_val ,
rec_mx_val ,
rec_incr_val ,
ws_val_last_upd_date ,
ws_val_last_upd_by ,
pay_proposal_id ,
element_entry_value_id ,
inelig_rsn_cd ,
elig_ovrid_dt ,
elig_ovrid_person_id ,
copy_dist_bdgt_val ,
copy_ws_bdgt_val ,
copy_rsrv_val ,
copy_dist_bdgt_mn_val ,
copy_dist_bdgt_mx_val ,
copy_dist_bdgt_incr_val ,
copy_ws_bdgt_mn_val ,
copy_ws_bdgt_mx_val ,
copy_ws_bdgt_incr_val ,
copy_rsrv_mn_val ,
copy_rsrv_mx_val ,
copy_rsrv_incr_val ,
copy_dist_bdgt_iss_val ,
copy_ws_bdgt_iss_val ,
copy_dist_bdgt_iss_date ,
copy_ws_bdgt_iss_date ,
COMP_POSTING_DATE ,
WS_RT_START_DATE ,
currency ,
object_version_number /*,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date */
) values
(ben_cwb_person_rates_s.nextval,
nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1),
l_cpr_rec.pl_id,
nvl(l_cpr_rec.oipl_id, -1),
l_cpr_rec.group_pl_id,
nvl(l_cpr_rec.group_oipl_id, -1), -- group_oipl_id ,
l_cpr_rec.lf_evt_ocrd_dt,
p_person_id,
l_assignment_id ,
'Y', -- l_elig_flag ,
null, -- l_cpr_rec.ws_val ,
null, -- l_cpr_rec.ws_mn_val ,
null, -- l_cpr_rec.ws_mx_val ,
null, -- l_cpr_rec.ws_incr_val ,
null, -- l_cpr_rec.elig_sal_val ,
null, -- l_cpr_rec.stat_sal_val ,
null, -- l_cpr_rec.oth_comp_val ,
null, -- l_cpr_rec.tot_comp_val ,
null, -- l_cpr_rec.misc1_val ,
null, -- l_cpr_rec.misc2_val ,
null, -- l_cpr_rec.misc3_val ,
null, -- l_cpr_rec.rec_val ,
null, -- l_cpr_rec.rec_mn_val ,
null, -- l_cpr_rec.rec_mx_val ,
null, -- l_cpr_rec.rec_incr_val ,
null, -- l_cpr_rec.ws_val_last_upd_date ,
null, -- l_cpr_rec.ws_val_last_upd_by ,
null, -- g_cwb_person_rates_rec.pay_proposal_id ,
null, -- g_cwb_person_rates_rec.element_entry_value_id ,
null, -- l_inelig_rsn_cd ,
null, -- l_cpr_rec.elig_ovrid_dt ,
null, -- l_cpr_rec.elig_ovrid_person_id ,
null, -- g_cwb_person_rates_rec.copy_dist_bdgt_val ,
null, -- g_cwb_person_rates_rec.copy_ws_bdgt_val ,
null, -- g_cwb_person_rates_rec.copy_rsrv_val ,
null, -- g_cwb_person_rates_rec.copy_dist_bdgt_mn_val ,
null, -- g_cwb_person_rates_rec.copy_dist_bdgt_mx_val ,
null, -- g_cwb_person_rates_rec.copy_dist_bdgt_incr_val ,
null, -- g_cwb_person_rates_rec.copy_ws_bdgt_mn_val ,
null, -- g_cwb_person_rates_rec.copy_ws_bdgt_mx_val ,
null, -- g_cwb_person_rates_rec.copy_ws_bdgt_incr_val ,
null, -- g_cwb_person_rates_rec.copy_rsrv_mn_val ,
null, -- g_cwb_person_rates_rec.copy_rsrv_mx_val ,
null, -- g_cwb_person_rates_rec.copy_rsrv_incr_val ,
null, -- g_cwb_person_rates_rec.copy_dist_bdgt_iss_val ,
null, -- g_cwb_person_rates_rec.copy_ws_bdgt_iss_val ,
null, -- g_cwb_person_rates_rec.copy_dist_bdgt_iss_date ,
null, -- g_cwb_person_rates_rec.copy_ws_bdgt_iss_date ,
null, -- l_cpr_rec.COMP_POSTING_DATE,
null, -- l_cpr_rec.WS_RT_START_DATE,
l_cpr_rec.currency, -- Bug 5104388
1 -- object_version_number ,
/* l_cpr_rec.last_update_date ,
l_cpr_rec.last_updated_by ,
l_cpr_rec.last_update_login ,
l_cpr_rec.created_by ,
l_cpr_rec.creation_date
*/
);
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null
,p_ler_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_pl_typ_id in number default null
,p_opt_id in number default null
,p_eligy_prfl_id in number default null
,p_vrbl_rt_prfl_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_commit_data in varchar2 default 'Y'
,p_audit_log_flag in varchar2 default 'N'
,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
,p_cbr_tmprl_evt_flag in varchar2 default 'N'
,p_trace_plans_flag in varchar2 default 'N'
,p_online_call_flag in varchar2 default 'N'
,p_clone_all_data_flag in varchar2 default 'N'
,p_cwb_person_type in varchar2 default NULL
,p_run_rollup_only in varchar2 default 'N' /* Bug 4875181 */
) is
--
l_package varchar2(80) := g_package||'.global_process';
select '1' pln_order,
pln.pl_id,
pln.business_group_id,
pln.name
from ben_pl_f pln
where pln.group_pl_id = p_pl_id
and l_lf_evt_ocrd_dt between pln.effective_start_date
and pln.effective_end_date
and pln.pl_id =pln.group_pl_id
and pln.pl_stat_cd = 'A'
union
select '2' pln_order,
pln.pl_id,
pln.business_group_id,
pln.name
from ben_pl_f pln
where pln.group_pl_id = p_pl_id
and l_lf_evt_ocrd_dt between pln.effective_start_date
and pln.effective_end_date
and pln.pl_id <> pln.group_pl_id
and pln.pl_stat_cd = 'A'
order by pln_order ;
select bnb.benfts_grp_id
from ben_benfts_grp bnb,
ben_benfts_grp bnb1
where bnb.business_group_id = cv_business_group_id
and bnb.name = bnb1.name
and bnb1.benfts_grp_id = cv_benfts_grp_id;
cursor c_person_selection_rl(cv_formula_id number, cv_business_group_id number,
cv_effective_date date ) is
select fff.formula_id
from ff_formulas_f fff,
ff_formulas_f fff1
where fff.business_group_id = cv_business_group_id
and cv_effective_date between fff.effective_start_date
and fff.effective_end_date
and fff.formula_name = fff1.formula_name
and cv_effective_date between fff1.effective_start_date
and fff1.effective_end_date
and fff1.formula_id = cv_formula_id;
Select ppf.person_id person_id
,paf.assignment_id assignment_id
, ppf.original_date_of_hire original_start_date
, ppf.start_date latest_start_date
, ppp.date_start latest_placement_start_date
, ppp.projected_termination_date
/* Changed for bug#7393142
, DECODE(Ppf.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,
DECODE(Ppf.CURRENT_NPW_FLAG,
'Y',PPP.DATE_START,
NULL)
) Hire_Date
*/
,(CASE WHEN ppf.employee_number IS NOT NULL THEN
pps.date_start
WHEN ppf.npw_number IS NOT NULL THEN
ppp.date_start
END) HIRE_DATE
,pps.actual_termination_date actual_termination_date
from per_all_people_f ppf
,per_all_assignments_f paf
,PER_PERIODS_OF_PLACEMENT PPP
,PER_PERIODS_OF_SERVICE PPS
where ppf.person_id = paf.person_id
and paf. assignment_type in ('E','B') -- Need to consider Ex-Employee too
and p_effective_date between
ppf.effective_start_date and ppf.effective_end_date
and p_effective_date between
paf.effective_start_date and paf.effective_end_date
and ppp.person_id (+) = ppf.person_id
and ((ppf.employee_number is null)
or
(Ppf.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START =
(SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = Ppf.PERSON_ID
AND PPS1.DATE_START <= Ppf.EFFECTIVE_END_DATE)
)
)
AND ((Ppf.NPW_NUMBER IS NULL)
OR
(Ppf.NPW_NUMBER IS NOT NULL AND
PPP.DATE_START =
(SELECT MAX(PPP1.DATE_START)
FROM PER_PERIODS_OF_PLACEMENT PPP1
WHERE PPP1.PERSON_ID = Ppf.PERSON_ID
AND PPP1.DATE_START <= Ppf.EFFECTIVE_END_DATE
)
)
)
AND PPS.PERSON_ID (+) = Ppf.PERSON_ID
and ppf.person_id = p_person_id;
select ler.ler_id
from ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp,
ben_ler_f ler
where enp.business_group_id = cv_business_group_id
and enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and pet.business_group_id = enp.business_group_id
and cv_effective_date
between pet.effective_start_date
and pet.effective_end_date
and ler.typ_cd = 'COMP'
and ler.business_group_id = pet.business_group_id
and cv_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.ler_id = enp.ler_id
and pet.pl_id = cv_pl_id;
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.group_pl_id = cv_pl_id
and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and pil.ler_id = cv_ler_id
and pil.person_id = cv_person_id
and pil.business_group_id = cv_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD';
select null
from BEN_PRTN_ELIG_F epa,
BEN_PRTN_ELIG_PRFL_f cep
where epa.pl_id = cv_pl_id
and epa.prtn_elig_id = cep.prtn_elig_id
and cv_eff_dt between epa.effective_start_date
and epa.effective_end_date
and cv_eff_dt between cep.effective_start_date
and cep.effective_end_date;
select null
from BEN_PRTN_ELIG_F epa,
BEN_PRTN_ELIG_PRFL_f cep,
ben_pl_f pln
where pln.group_pl_id = cv_pl_id
and pln.group_pl_id <> pln.pl_id
and epa.pl_id = pln.pl_id
and epa.prtn_elig_id = cep.prtn_elig_id
and cv_eff_dt between epa.effective_start_date
and epa.effective_end_date
and cv_eff_dt between cep.effective_start_date
and cep.effective_end_date;
l_person_selection_rule_id number;
if p_person_selection_rule_id is not null then
--
open c_person_selection_rl(p_person_selection_rule_id,l_count.business_group_id,l_lf_evt_ocrd_dt);
fetch c_person_selection_rl into l_person_selection_rule_id ;
close c_person_selection_rl ;
hr_utility.set_location('l_person_selection_rule_id = '||l_person_selection_rule_id,4321);
argument15 => p_comp_selection_rule_id,
argument16 => l_person_selection_rule_id,
argument17 => p_ler_id,
argument18 => p_organization_id,
argument19 => l_benfts_grp_id,
argument20 => p_location_id,
argument21 => p_pstl_zip_rng_id,
argument22 => p_rptg_grp_id,
argument23 => p_pl_typ_id,
argument24 => p_opt_id,
argument25 => p_eligy_prfl_id,
argument26 => p_vrbl_rt_prfl_id,
argument27 => p_legal_entity_id,
argument28 => p_payroll_id,
argument29 => p_commit_data,
argument30 => l_audit_log_flag,
argument31 => p_lmt_prpnip_by_org_flag,
argument32 => p_cbr_tmprl_evt_flag,
argument33 => p_cwb_person_type
);
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>l_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>l_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag,
p_cwb_person_type => p_cwb_person_type);
g_error_log_rec.calling_proc := 'insert_into_ben_cwb_xchg';
ben_cwb_xchg_pkg.insert_into_ben_cwb_xchg(
p_group_pl_id => g_cache_group_plan_rec.group_pl_id
,p_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt
,p_effective_date => null
,p_refresh_always => 'N');
INSERT INTO ben_transaction
(transaction_id
,transaction_type
,attribute1
,ATTRIBUTE40)
VALUES (ben_transaction_s.NEXTVAL
,'CWBRECURHIER'
,to_char(fnd_global.conc_request_id)
,g_hrchy_tbl(i).hrchy_cat_string);
select group_oipl.oipl_id
from ben_oipl_f oipl
,ben_oipl_f group_oipl
,ben_pl_f pl
,ben_opt_f opt
where oipl.oipl_id = p_oipl_id
and oipl.opt_id = opt.opt_id
and opt.group_opt_id = group_oipl.opt_id
and group_oipl.pl_id = pl.group_pl_id
and pl.pl_id = oipl.pl_id
and cv_lf_evt_ocrd_dt between oipl.effective_start_date
and oipl.effective_end_date
and cv_lf_evt_ocrd_dt between group_oipl.effective_start_date
and group_oipl.effective_end_date
and cv_lf_evt_ocrd_dt between pl.effective_start_date
and pl.effective_end_date
and cv_lf_evt_ocrd_dt between opt.effective_start_date
and opt.effective_end_date;
select elig_flag, inelig_rsn_cd
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = cv_elig_per_elctbl_chc_id;
insert into ben_cwb_person_groups
(group_per_in_ler_id,
group_pl_id ,
group_oipl_id ,
lf_evt_ocrd_dt ,
bdgt_pop_cd ,
due_dt ,
access_cd ,
approval_cd ,
approval_date ,
approval_comments ,
submit_cd ,
submit_date ,
submit_comments ,
dist_bdgt_val ,
ws_bdgt_val ,
-- Bug 3510081 New columns need to be populated.
dflt_dist_bdgt_val ,
dflt_ws_bdgt_val ,
rsrv_val ,
dist_bdgt_mn_val ,
dist_bdgt_mx_val ,
dist_bdgt_incr_val ,
ws_bdgt_mn_val ,
ws_bdgt_mx_val ,
ws_bdgt_incr_val ,
rsrv_mn_val ,
rsrv_mx_val ,
rsrv_incr_val ,
dist_bdgt_iss_val ,
ws_bdgt_iss_val ,
dist_bdgt_iss_date ,
ws_bdgt_iss_date ,
ws_bdgt_val_last_upd_date ,
dist_bdgt_val_last_upd_date ,
rsrv_val_last_upd_date ,
ws_bdgt_val_last_upd_by ,
dist_bdgt_val_last_upd_by ,
rsrv_val_last_upd_by ,
object_version_number ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date
) values (
g_cache_group_plan_rec.group_per_in_ler_id,
g_cache_group_plan_rec.group_pl_id ,
nvl(l_group_oipl_id, -1) ,
g_cache_group_plan_rec.group_lf_evt_ocrd_dt ,
null, -- bdgt_pop_cd
nvl(p_due_dt,g_cache_group_plan_rec.ws_upd_end_dt), -- this can go as null
g_cache_group_plan_rec.access_cd,
null, -- approval_cd
null, -- approval_date
null, -- approval_comments
'NS', -- submit_cd
null, -- submit_date
null, -- submit_comments
--
-- Bug 3510081 : No need to populate the budget values here.
-- Need to verify whether other values need to be populated or not.
--
null, -- g_cwb_person_groups_rec.dist_bdgt_val ,
null, -- g_cwb_person_groups_rec.ws_bdgt_val ,
g_cwb_person_groups_rec.dflt_dist_bdgt_val ,
g_cwb_person_groups_rec.dflt_ws_bdgt_val ,
g_cwb_person_groups_rec.rsrv_val ,
g_cwb_person_groups_rec.dist_bdgt_mn_val ,
g_cwb_person_groups_rec.dist_bdgt_mx_val ,
g_cwb_person_groups_rec.dist_bdgt_incr_val ,
g_cwb_person_groups_rec.ws_bdgt_mn_val ,
g_cwb_person_groups_rec.ws_bdgt_mx_val ,
g_cwb_person_groups_rec.ws_bdgt_incr_val ,
g_cwb_person_groups_rec.rsrv_mn_val ,
g_cwb_person_groups_rec.rsrv_mx_val ,
g_cwb_person_groups_rec.rsrv_incr_val ,
--
-- Bug 3510081 : No need to populate the budget values here.
-- Need to verify whether other values need to be populated or not.
--
null, -- g_cwb_person_groups_rec.dist_bdgt_iss_val ,
null, -- g_cwb_person_groups_rec.ws_bdgt_iss_val ,
null, -- g_cwb_person_groups_rec.dist_bdgt_iss_date ,
null, -- g_cwb_person_groups_rec.ws_bdgt_iss_date ,
g_cwb_person_groups_rec.ws_bdgt_val_last_upd_date ,
g_cwb_person_groups_rec.dist_bdgt_val_last_upd_date ,
g_cwb_person_groups_rec.rsrv_val_last_upd_date ,
g_cwb_person_groups_rec.ws_bdgt_val_last_upd_by ,
g_cwb_person_groups_rec.dist_bdgt_val_last_upd_by ,
g_cwb_person_groups_rec.rsrv_val_last_upd_by ,
1, -- object_version_number
-- Check all the column values.
g_cwb_person_groups_rec.last_update_date ,
g_cwb_person_groups_rec.last_updated_by ,
g_cwb_person_groups_rec.last_update_login ,
g_cwb_person_groups_rec.created_by ,
g_cwb_person_groups_rec.creation_date
) ;
insert into ben_cwb_person_rates
(person_rate_id ,
group_per_in_ler_id ,
pl_id ,
oipl_id ,
group_pl_id ,
group_oipl_id ,
lf_evt_ocrd_dt ,
person_id ,
assignment_id ,
elig_flag ,
ws_val ,
ws_mn_val ,
ws_mx_val ,
ws_incr_val ,
elig_sal_val ,
stat_sal_val ,
oth_comp_val ,
tot_comp_val ,
misc1_val ,
misc2_val ,
misc3_val ,
rec_val ,
rec_mn_val ,
rec_mx_val ,
rec_incr_val ,
ws_val_last_upd_date ,
ws_val_last_upd_by ,
pay_proposal_id ,
element_entry_value_id ,
inelig_rsn_cd ,
elig_ovrid_dt ,
elig_ovrid_person_id ,
copy_dist_bdgt_val ,
copy_ws_bdgt_val ,
copy_rsrv_val ,
copy_dist_bdgt_mn_val ,
copy_dist_bdgt_mx_val ,
copy_dist_bdgt_incr_val ,
copy_ws_bdgt_mn_val ,
copy_ws_bdgt_mx_val ,
copy_ws_bdgt_incr_val ,
copy_rsrv_mn_val ,
copy_rsrv_mx_val ,
copy_rsrv_incr_val ,
copy_dist_bdgt_iss_val ,
copy_ws_bdgt_iss_val ,
copy_dist_bdgt_iss_date ,
copy_ws_bdgt_iss_date ,
COMP_POSTING_DATE ,
WS_RT_START_DATE ,
currency ,
object_version_number ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date
) values
(ben_cwb_person_rates_s.nextval ,
nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1) ,
p_pl_id ,
nvl(p_oipl_id, -1),
g_cache_group_plan_rec.group_pl_id ,
nvl(l_group_oipl_id, -1), -- group_oipl_id ,
g_cache_group_plan_rec.group_lf_evt_ocrd_dt ,
p_person_id ,
p_assignment_id ,
l_elig_flag ,
g_cwb_person_rates_rec.ws_val ,
g_cwb_person_rates_rec.ws_mn_val ,
g_cwb_person_rates_rec.ws_mx_val ,
g_cwb_person_rates_rec.ws_incr_val ,
g_cwb_person_rates_rec.elig_sal_val ,
g_cwb_person_rates_rec.stat_sal_val ,
g_cwb_person_rates_rec.oth_comp_val ,
g_cwb_person_rates_rec.tot_comp_val ,
g_cwb_person_rates_rec.misc1_val ,
g_cwb_person_rates_rec.misc2_val ,
g_cwb_person_rates_rec.misc3_val ,
g_cwb_person_rates_rec.rec_val ,
g_cwb_person_rates_rec.rec_mn_val ,
g_cwb_person_rates_rec.rec_mx_val ,
g_cwb_person_rates_rec.rec_incr_val ,
g_cwb_person_rates_rec.ws_val_last_upd_date ,
g_cwb_person_rates_rec.ws_val_last_upd_by ,
g_cwb_person_rates_rec.pay_proposal_id ,
g_cwb_person_rates_rec.element_entry_value_id ,
l_inelig_rsn_cd ,
g_cwb_person_rates_rec.elig_ovrid_dt ,
g_cwb_person_rates_rec.elig_ovrid_person_id ,
g_cwb_person_rates_rec.copy_dist_bdgt_val ,
g_cwb_person_rates_rec.copy_ws_bdgt_val ,
g_cwb_person_rates_rec.copy_rsrv_val ,
g_cwb_person_rates_rec.copy_dist_bdgt_mn_val ,
g_cwb_person_rates_rec.copy_dist_bdgt_mx_val ,
g_cwb_person_rates_rec.copy_dist_bdgt_incr_val ,
g_cwb_person_rates_rec.copy_ws_bdgt_mn_val ,
g_cwb_person_rates_rec.copy_ws_bdgt_mx_val ,
g_cwb_person_rates_rec.copy_ws_bdgt_incr_val ,
g_cwb_person_rates_rec.copy_rsrv_mn_val ,
g_cwb_person_rates_rec.copy_rsrv_mx_val ,
g_cwb_person_rates_rec.copy_rsrv_incr_val ,
g_cwb_person_rates_rec.copy_dist_bdgt_iss_val ,
g_cwb_person_rates_rec.copy_ws_bdgt_iss_val ,
g_cwb_person_rates_rec.copy_dist_bdgt_iss_date ,
g_cwb_person_rates_rec.copy_ws_bdgt_iss_date ,
g_cwb_person_rates_rec.COMP_POSTING_DATE,
g_cwb_person_rates_rec.WS_RT_START_DATE,
l_currency_cd ,
1, -- object_version_number ,
g_cwb_person_rates_rec.last_update_date ,
g_cwb_person_rates_rec.last_updated_by ,
g_cwb_person_rates_rec.last_update_login ,
g_cwb_person_rates_rec.created_by ,
g_cwb_person_rates_rec.creation_date );
select pil.group_pl_id,
pil.lf_evt_ocrd_dt,
pil.business_group_id,
pil.ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_group_per_in_ler_id ;
insert into ben_cwb_group_hrchy (
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num )
values (
p_group_per_in_ler_id,
-1,
-1 );
select emp_per_in_ler_id
from ben_cwb_group_hrchy
where mgr_per_in_ler_id = p_group_per_in_ler_id
and lvl_num > 0 ;
insert into ben_cwb_group_hrchy (
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num ) values (r_emp_repo.emp_per_in_ler_id, -1, -1);
delete from ben_cwb_group_hrchy
where emp_per_in_ler_id = p_group_per_in_ler_id
and lvl_num >= 0;
delete from ben_cwb_group_hrchy
where emp_per_in_ler_id in (
select emp_per_in_ler_id
from ben_cwb_group_hrchy
where mgr_per_in_ler_id = p_group_per_in_ler_id )
and lvl_num >= 0;
insert into ben_cwb_group_hrchy(
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num )
select
distinct emp_per_in_ler_id,
emp_per_in_ler_id,
0
from ben_cwb_group_hrchy cwb1
where emp_per_in_ler_id =
( select mgr_per_in_ler_id from ben_cwb_group_hrchy
where emp_per_in_ler_id = p_group_per_in_ler_id
and lvl_num = 1 )
and not exists ( select null from ben_cwb_group_hrchy cwb2
where cwb1.emp_per_in_ler_id = cwb2.emp_per_in_ler_id
and lvl_num = 0 ) ;
insert into ben_cwb_group_hrchy(
emp_per_in_ler_id,
mgr_per_in_ler_id,
lvl_num )
select
h.mgr_per_in_ler_id
,h.mgr_per_in_ler_id
,0
from ben_cwb_group_hrchy h
where h.emp_per_in_ler_id = p_group_per_in_ler_id
and h.lvl_num = 1;
select /*+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) */ cpr.rowid, cpr.*
from ben_cwb_person_rates cpr
where cpr.person_id = c_person_id
and cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and cpr.group_pl_id = c_group_pl_id
-- Bug 3517726 : Do not consider any data which is not deleted
-- by backoout.
and cpr.group_per_in_ler_id = -1
for update of cpr.group_per_in_ler_id;
select cpg.*
from ben_cwb_person_groups cpg
where cpg.group_pl_id = c_group_pl_id
and cpg.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and cpg.group_per_in_ler_id =
(select cpg1.group_per_in_ler_id
from ben_cwb_person_groups cpg1
where cpg1.group_pl_id = c_group_pl_id
and cpg1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
and rownum = 1);
SELECT group_oipl_id
FROM ben_cwb_pl_dsgn
WHERE group_pl_id = c_group_pl_id
AND pl_id = group_pl_id
AND lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt;
p_program_update_date => trunc(sysdate));
,p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
update ben_cwb_person_rates cpr
set cpr.group_per_in_ler_id = l_curr_per_in_ler_id
where cpr.rowid = l_per_rt_rec.rowid;
insert into ben_cwb_person_groups
(group_per_in_ler_id,
group_pl_id ,
group_oipl_id ,
lf_evt_ocrd_dt ,
bdgt_pop_cd ,
due_dt ,
access_cd ,
approval_cd ,
approval_date ,
approval_comments ,
submit_cd ,
submit_date ,
submit_comments ,
dist_bdgt_val ,
ws_bdgt_val ,
-- Bug 3510081 New columns need to be populated.
dflt_dist_bdgt_val ,
dflt_ws_bdgt_val ,
rsrv_val ,
dist_bdgt_mn_val ,
dist_bdgt_mx_val ,
dist_bdgt_incr_val ,
ws_bdgt_mn_val ,
ws_bdgt_mx_val ,
ws_bdgt_incr_val ,
rsrv_mn_val ,
rsrv_mx_val ,
rsrv_incr_val ,
dist_bdgt_iss_val ,
ws_bdgt_iss_val ,
dist_bdgt_iss_date ,
ws_bdgt_iss_date ,
ws_bdgt_val_last_upd_date ,
dist_bdgt_val_last_upd_date ,
rsrv_val_last_upd_date ,
ws_bdgt_val_last_upd_by ,
dist_bdgt_val_last_upd_by ,
rsrv_val_last_upd_by ,
object_version_number ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date
) values (
l_curr_per_in_ler_id,
g_cache_cpg_rec(l_cpg_count).group_pl_id ,
nvl(g_cache_cpg_rec(l_cpg_count).group_oipl_id, -1) ,
g_cache_cpg_rec(l_cpg_count).lf_evt_ocrd_dt ,
null, -- bdgt_pop_cd
g_cache_cpg_rec(l_cpg_count).due_dt,
g_cache_cpg_rec(l_cpg_count).access_cd,
null, -- approval_cd
null, -- approval_date
null, -- approval_comments
'NS', -- submit_cd
null, -- submit_date
null, -- submit_comments
/*
g_cache_cpg_rec(l_cpg_count).dist_bdgt_val,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_val,
g_cache_cpg_rec(l_cpg_count).rsrv_val,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_mn_val,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_mx_val,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_incr_val,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_mn_val,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_mx_val,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_incr_val,
g_cache_cpg_rec(l_cpg_count).rsrv_mn_val,
g_cache_cpg_rec(l_cpg_count).rsrv_mx_val,
g_cache_cpg_rec(l_cpg_count).rsrv_incr_val,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_iss_val,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_iss_val,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_iss_date,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_iss_date,
*/
-- Bug 3510081 put null values.
null, -- l_copy_dist_bdgt_val,
null, -- l_copy_ws_bdgt_val,
l_copy_dist_bdgt_val,
l_copy_ws_bdgt_val,
l_copy_rsrv_val,
l_copy_dist_bdgt_mn_val,
l_copy_dist_bdgt_mx_val,
l_copy_dist_bdgt_incr_val,
l_copy_ws_bdgt_mn_val,
l_copy_ws_bdgt_mx_val,
l_copy_ws_bdgt_incr_val,
l_copy_rsrv_mn_val,
l_copy_rsrv_mx_val,
l_copy_rsrv_incr_val,
--
-- Bug 3510081 : No need to populate the budget values here.
-- Need to verify whether other values need to be populated or not.
--
null, -- l_copy_dist_bdgt_iss_val,
null, -- l_copy_ws_bdgt_iss_val,
null, -- l_copy_dist_bdgt_iss_date,
null, -- l_copy_ws_bdgt_iss_date,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_val_last_upd_date ,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_val_last_upd_date ,
g_cache_cpg_rec(l_cpg_count).rsrv_val_last_upd_date ,
g_cache_cpg_rec(l_cpg_count).ws_bdgt_val_last_upd_by ,
g_cache_cpg_rec(l_cpg_count).dist_bdgt_val_last_upd_by ,
g_cache_cpg_rec(l_cpg_count).rsrv_val_last_upd_by ,
1, -- object_version_number
-- Check all the column values.
g_cache_cpg_rec(l_cpg_count).last_update_date ,
g_cache_cpg_rec(l_cpg_count).last_updated_by ,
g_cache_cpg_rec(l_cpg_count).last_update_login ,
g_cache_cpg_rec(l_cpg_count).created_by ,
g_cache_cpg_rec(l_cpg_count).creation_date
) ;
select person_id
from ben_per_in_ler all_pils
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_group_lf_evt_ocrd_dt
and per_in_ler_stat_cd = 'STRTD'
union
select ws_mgr_id
from ben_per_in_ler all_pils
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_group_lf_evt_ocrd_dt
and per_in_ler_stat_cd = 'STRTD'
and ws_mgr_id is not null
minus
select person_id
from ben_per_in_ler all_pils
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_group_lf_evt_ocrd_dt
and per_in_ler_stat_cd in ('STRTD', 'PROCD') -- GSI If a person is already processed but his reportee
-- is processed then data should not be created for manager.
and ler_id = p_group_ler_id;
select cpr.*, cpr.rowid
from ben_cwb_person_rates cpr
where GROUP_PER_IN_LER_ID = -1
and GROUP_PL_ID = cv_group_pl_id
and LF_EVT_OCRD_DT = cv_lf_evt_ocrd_dt;
select per_in_ler_id
from ben_per_in_ler
where person_id = cv_person_id
and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and ler_id = cv_group_ler_id
and per_in_ler_stat_cd = 'STRTD';
For each person in( ((select person_id from ben_per_in_ler all_pils)
union
(select ws_mgr_id from ben_per_in_ler all_pils)
) minus
(select person_id from ben_per_in_ler group_plan_pils)
)
Loop
popu_missing_person_pil(p_mode, p_person_id,
p_group_pl_business_group_id,
p_group_pl_id, p_effective_date,
p_lf_evt_ocrd_dt);
Insert row into ben_cwb_group_hrchy if row already not exists.
Update the group_per_in_ler_id on ben_cwb_person_rates
where group_per_in_ler_id is null and
group_pl_id = p_group_pl_id and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt and
assignment_id = p_assignment_id;
update ben_cwb_person_rates
set group_per_in_ler_id = l_group_per_in_ler_id
where rowid = l_null_gpil_rec.rowid;
g_cache_cpg_rec.delete;
select oipl.oipl_id
from ben_oipl_f oipl
where oipl.pl_id = cv_pl_id
and oipl.OIPL_STAT_CD = 'A'
and cv_lf_evt_ocrd_dt between oipl.effective_start_date
and oipl.effective_end_date
and exists
(select null
from ben_acty_base_rt_f abr
where abr.pl_id = cv_pl_id
and cv_lf_evt_ocrd_dt between abr.effective_start_date
and abr.effective_end_date
and abr.acty_typ_cd = 'CWBWS' -- 9999
);
select *
from ben_cwb_person_rates
where group_pl_id = cv_group_pl_id
and pl_id = cv_pl_id
and nvl(oipl_id, -1) = -1
and person_id = cv_person_id
and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt;
select sum(ws_val) ws_val, sum(nvl(ws_val, 0)) ws_val_0_if_null
from ben_cwb_person_rates
where group_pl_id = cv_group_pl_id
and pl_id = cv_pl_id
and nvl(oipl_id, -1) <> -1
and person_id = cv_person_id
and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and ws_val is not null;
update ben_cwb_person_rates
set ws_val = l_oipl_ws_sum
where person_rate_id = l_cpr_rec.person_rate_id;
select cpg.rowid, cpg.*
from ben_cwb_person_groups cpg
where cpg.group_pl_id = c_group_pl_id
and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
and exists
( SELECT /*+ no_unnest */ NULL
FROM BEN_CWB_GROUP_HRCHY CGH
where CGH.MGR_PER_IN_LER_ID = CPG.GROUP_PER_IN_LER_ID
AND CGH.LVL_NUM > 1
And exists (select /*+ no_unnest */ null
from BEN_CWB_PERSON_RATES CPR
where CGH.EMP_PER_IN_LER_ID = CPR.GROUP_PER_IN_LER_ID
AND CPR.ELIG_FLAG = 'Y' ))
and cpg.dist_bdgt_val_last_upd_date is null
and cpg.ws_bdgt_val_last_upd_date is null
for update;
select cpg.rowid, cpg.*
from ben_cwb_person_groups cpg
where cpg.group_pl_id = c_group_pl_id
and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
and not exists
(select /*+ no_unnest */ null
from ben_cwb_person_rates cpr1,
ben_cwb_group_hrchy cgh1
where cgh1.emp_per_in_ler_id = cpr1.GROUP_PER_IN_LER_ID
and cpr1.ELIG_FLAG = 'Y'
and cgh1.LVL_NUM > 1
and cgh1.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
and exists
(select /*+ no_unnest */ null
from ben_cwb_person_rates cpr,
ben_cwb_group_hrchy cgh
where cgh.emp_per_in_ler_id = cpr.GROUP_PER_IN_LER_ID
and cpr.ELIG_FLAG = 'Y'
and cgh.LVL_NUM = 1
and cgh.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
and cpg.ws_bdgt_val_last_upd_date is null
and cpg.dist_bdgt_val_last_upd_date is null
for update ;
update ben_cwb_person_groups
set dist_bdgt_val = dflt_dist_bdgt_val,
ws_bdgt_val = dflt_ws_bdgt_val,
ws_bdgt_val_last_upd_date = p_lf_evt_ocrd_dt,
dist_bdgt_val_last_upd_date = p_lf_evt_ocrd_dt,
bdgt_pop_cd = l_pop_cd,
dist_bdgt_iss_val = l_dflt_dist_bdgt_val,
ws_bdgt_iss_val = l_dflt_ws_bdgt_val,
dist_bdgt_iss_date = l_iss_dt,
ws_bdgt_iss_date = l_iss_dt
where rowid = l_high_mgr_cpg_rec.rowid;
update ben_cwb_person_groups
set ws_bdgt_val = dflt_ws_bdgt_val,
ws_bdgt_val_last_upd_date = p_lf_evt_ocrd_dt,
ws_bdgt_iss_val = l_dflt_ws_bdgt_val,
ws_bdgt_iss_date = l_iss_dt,
bdgt_pop_cd = l_pop_cd
where rowid = l_leaf_mgr_cpg_rec.rowid;