[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_PL_DSGN_PKG
Source
1 package body BEN_CWB_PL_DSGN_PKG as
2 /* $Header: bencwbpl.pkb 120.3.12000000.1 2007/01/19 15:29:25 appldev noship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_pl_dsgn_pkg.'; --Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- --------------------------------------------------------------------------
12 -- |------------------------< get_opt_ordr_in_grp >-------------------------|
13 -- --------------------------------------------------------------------------
14 -- This is an internal functional called by refresh_pl_dsgn.
15 --
16 -- Cursor csr_grp_opt_ordr finds all the opt ids attached to the given group
17 -- plan and orders them on oipl_ordr_num. The result is placed in l_grp_opt
18 -- by refresh_pl_dsgn procedure before starint any process.
19 --
20 -- For a given oipl which is attached to a group oipl, this function
21 -- get_opt_ordr_in_grp finds the order num of the option in the group options
22 -- by searching l_grp_opt.
23 --
24 type grp_opt_type is table of ben_oipl_f.opt_id%type;
25 g_grp_opt grp_opt_type;
26 --
27 function get_opt_ordr_in_grp(oipl_opt in number)
28 return number is
29 --
30 l_proc varchar2(72) := g_package||'get_opt_ordr_in_grp';
31 --
32 begin
33 --
34 if g_debug then
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 end if;
37 --
38 if g_grp_opt.count = 0 then
39 return 0;
40 end if;
41 --
42 if g_debug then
43 hr_utility.set_location(l_proc, 20);
44 end if;
45 --
46 for i in g_grp_opt.first .. g_grp_opt.last
47 loop
48 if g_grp_opt(i) = oipl_opt then
49 return i;
50 end if;
51 end loop;
52 -- this case should never happen
53 return 0;
54 --
55 if g_debug then
56 hr_utility.set_location(' Leaving:'|| l_proc, 99);
57 end if;
58 --
59 end get_opt_ordr_in_grp;
60 --
61 -- --------------------------------------------------------------------------
62 -- |---------------------------< get_actual_flag >---------------------------|
63 -- --------------------------------------------------------------------------
64 --
65 -- This function checks whether the given plan is an actual plan or part of
66 -- a group plan.
67 --
68 function get_actual_flag(p_pl_id number
69 ,p_group_pl_id number
70 ,p_effective_date date)
71 return varchar2 is
72 --
73 l_dummy varchar2(1);
74 --
75 l_proc varchar2(72) := g_package||'get_actual_flag';
76 --
77 begin
78 --
79 if g_debug then
80 hr_utility.set_location('Entering:'|| l_proc, 10);
81 end if;
82 --
83 if (p_pl_id <> p_group_pl_id) then
84 --pl_id and group_pl_id differs. So this is an actual plan
85 --
86 if g_debug then
87 hr_utility.set_location(' Leaving:'|| l_proc, 66);
88 end if;
89 --
90 return 'Y';
91 else
92 -- pl_id and group_pl_id are same. so check if any other plans
93 -- attached to this plan(this plan id is used as group_pl_id
94 -- in other plans)
95 begin
96 select null into l_dummy
97 from dual
98 where exists(select null from ben_pl_f pl
99 where pl.group_pl_id = p_pl_id
100 and pl.pl_id <> p_pl_id
101 and p_effective_date between pl.effective_start_date
102 and pl.effective_end_date);
103
104 -- Some other plans have this pl_id as group_pl_id. So not an
105 -- acutal plan
106 --
107 if g_debug then
108 hr_utility.set_location(' Leaving:'|| l_proc, 77);
109 end if;
110 --
111 return 'N';
112 exception
113 when no_data_found then
114 -- no other plans are attached to pl_id. so this is an actual plan
115 --
116 if g_debug then
117 hr_utility.set_location(' Leaving:'|| l_proc, 99);
118 end if;
119 --
120 return 'Y';
121 end;
122 end if;
123 end;
124 --
125 -- --------------------------------------------------------------------------
126 -- |---------------------------< get_exchg_rate >----------------------------|
127 -- --------------------------------------------------------------------------
128 --
129 function get_exchg_rate(p_from_currency varchar2
130 ,p_to_currency varchar2
131 ,p_effective_date date
132 ,p_business_group_id number)
133 return number is
134 --
135 l_exchg_rate number;
136 --
137 l_proc varchar2(72) := g_package||'get_exchg_rate';
138 --
139 begin
140 --
141 if g_debug then
142 hr_utility.set_location('Entering:'|| l_proc, 10);
143 end if;
144 --
145 l_exchg_rate := hr_currency_pkg.get_rate
146 (p_from_currency -- From currency
147 ,p_to_currency -- To currency
148 ,p_effective_date -- conversion date
149 ,hr_currency_pkg.get_rate_type -- rate type
150 (p_business_group_id -- bg Id
151 ,p_effective_date -- conversion date
152 ,'R')); -- processing type
153 --
154 if g_debug then
155 hr_utility.set_location(' Leaving:'|| l_proc, 88);
156 end if;
157 --
158 return l_exchg_rate;
159 exception
160 when others then
161 --
162 if g_debug then
163 hr_utility.set_location(' Leaving:'|| l_proc, 99);
164 end if;
165 --
166 return 1;
167 end get_exchg_rate;
168 --
169 -- --------------------------------------------------------------------------
170 -- |---------------------------< get_valid_date >---------------------------|
171 -- --------------------------------------------------------------------------
172 --
173 function get_valid_date(p_day in number
174 ,p_month in number
175 ,p_start_date in date
176 ,p_end_date in date
177 ,p_default_date in date)
178 return date is
179 --
180 l_start_mo number(2) := to_number(to_char(p_start_date, 'MM'));
181 l_start_yy number(4) := to_number(to_char(p_start_date, 'YYYY'));
182 l_end_yy number(4) := to_number(to_char(p_end_date, 'YYYY'));
183 l_year number(4);
184 l_date date;
185 l_proc varchar2(72) := g_package||'get_valid_date';
186 --
187 begin
188 --
189 if g_debug then
190 hr_utility.set_location('Entering:'|| l_proc, 10);
191 end if;
192 --
193 if l_start_yy = l_end_yy then
194 l_year := l_start_yy;
195 elsif p_month >= l_start_mo then
196 l_year := l_start_yy;
197 else
198 l_year := l_end_yy;
199 end if;
200
201 l_date := fnd_date.canonical_to_date(l_year||'/'||p_month||'/'||p_day);
202 --
203 if g_debug then
204 hr_utility.set_location(' Leaving:'|| l_proc, 88);
205 end if;
206 --
207 return l_date;
208 --
209 exception
210 when others then
211 --
212 if g_debug then
213 hr_utility.set_location(' Leaving:'|| l_proc, 99);
214 end if;
215 --
216 return p_default_date;
217 end get_valid_date;
218 --
219 -- --------------------------------------------------------------------------
220 -- |--------------------------< refresh_pl_dsgn >---------------------------|
221 -- --------------------------------------------------------------------------
222 --
223 procedure refresh_pl_dsgn(p_group_pl_id in number
224 ,p_lf_evt_ocrd_dt in date
225 ,p_effective_date date
226 ,p_refresh_always in varchar2 default 'N') is
227 -- cursor to fetch group plan details
228 cursor csr_group_pl(p_group_pl_id number
229 ,p_lf_evt_ocrd_dt date
230 ,p_effective_date date) is
231 select pl.pl_id pl_id
232 ,-1 oipl_id
233 ,nvl(p_effective_date,nvl(enp.data_freeze_date,p_lf_evt_ocrd_dt))
234 effective_date
235 ,pl.name name
236 ,pl.group_pl_id group_pl_id
237 ,-1 group_oipl_id
238 ,pl.nip_pl_uom pl_uom
239 ,1 pl_xchg_rate
240 ,null opt_count
241 ,enp.uses_bdgt_flag uses_bdgt_flag
242 ,enp.prsvr_bdgt_cd prsrv_bdgt_cd
243 ,enp.ws_upd_strt_dt upd_start_dt
244 ,enp.ws_upd_end_dt upd_end_dt
245 ,enp.approval_mode_cd approval_mode
246 ,enp.strt_dt enrt_perd_start_dt
247 ,enp.end_dt enrt_perd_end_dt
248 ,yr.start_date yr_perd_start_dt
249 ,yr.end_date yr_perd_end_dt
250 ,to_date(null) wthn_yr_start_dt
251 ,to_date(null) wthn_yr_end_dt
252 ,wyr.strt_day wthn_strt_day
253 ,wyr.strt_mo wthn_strt_mo
254 ,wyr.end_day wthn_end_day
255 ,wyr.end_mo wthn_end_mo
256 ,enp.enrt_perd_id enrt_perd_id
257 ,yr.yr_perd_id yr_perd_id
258 ,enp.business_group_id business_group_id
259 ,enp.perf_revw_strt_dt perf_revw_strt_dt
260 ,enp.asg_updt_eff_date asg_updt_eff_date
261 ,enp.emp_interview_type_cd emp_interview_typ_cd
262 ,enp.sal_chg_reason_cd salary_change_reason
263 ,enp.data_freeze_date data_freeze_date
264 from ben_pl_f pl
265 ,ben_popl_enrt_typ_cycl_f petc
266 ,ben_enrt_perd enp
267 ,ben_yr_perd yr
268 ,ben_wthn_yr_perd wyr
269 where pl.pl_id = p_group_pl_id
270 and nvl(p_effective_date,nvl(enp.data_freeze_date,p_lf_evt_ocrd_dt))
271 between pl.effective_start_date and pl.effective_end_date
272 and petc.pl_id = pl.pl_id
273 and petc.enrt_typ_cycl_cd = 'COMP'
274 and nvl(p_effective_date,nvl(enp.data_freeze_date,p_lf_evt_ocrd_dt))
275 between petc.effective_start_date and petc.effective_end_date
276 and enp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
277 and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
278 and yr.yr_perd_id = enp.yr_perd_id
279 and wyr.wthn_yr_perd_id (+) = enp.wthn_yr_perd_id;
280
281 -- cursor to fetch the plan details
282 -- Bug 3975857 : In cursor CSR_PLS pick up only Active Standard Rates. Added clause <<ACTY_BASE_RT_STAT_CD (+)= 'A'>>
283 -- to the cursor query
284 cursor csr_pls(p_group_pl_id number
285 ,p_effective_date date
286 ,p_group_pl_uom varchar2
287 ,p_group_pl_bg_id number) is
288 select pl.pl_id pl_id
289 ,pl.name name
290 ,pl.nip_pl_uom pl_uom
291 ,pl.ordr_num pl_ordr_num
292 ,get_exchg_rate(p_group_pl_uom -- From currency
293 ,pl.nip_pl_uom -- To currency
294 ,p_effective_date -- conversion date
295 ,p_group_pl_bg_id -- bg Id
296 ) pl_xchg_rate
297 ,pl.business_group_id business_group_id
298 ,ws.acty_base_rt_id ws_abr_id
299 ,ws.nnmntry_uom ws_nnmntry_uom
300 ,ws.rndg_cd ws_rndg_cd
301 ,ws.sub_acty_typ_cd ws_sub_acty_typ_cd
302 ,ws.element_type_id ws_element_type_id
303 ,ws.input_value_id ws_input_value_id
304 ,db.acty_base_rt_id dist_bdgt_abr_id
305 ,db.nnmntry_uom dist_bdgt_nnmntry_uom
306 ,db.rndg_cd dist_bdgt_rndg_cd
307 ,wb.acty_base_rt_id ws_bdgt_abr_id
308 ,wb.nnmntry_uom ws_bdgt_nnmntry_uom
309 ,wb.rndg_cd ws_bdgt_rndg_cd
310 ,rsrv.acty_base_rt_id rsrv_abr_id
311 ,rsrv.nnmntry_uom rsrv_nnmntry_uom
312 ,rsrv.rndg_cd rsrv_rndg_cd
313 ,es.acty_base_rt_id elig_sal_abr_id
314 ,es.nnmntry_uom elig_sal_nnmntry_uom
318 ,misc1.rndg_cd misc1_rndg_cd
315 ,es.rndg_cd elig_sal_rndg_cd
316 ,misc1.acty_base_rt_id misc1_abr_id
317 ,misc1.nnmntry_uom misc1_nnmntry_uom
319 ,misc2.acty_base_rt_id misc2_abr_id
320 ,misc2.nnmntry_uom misc2_nnmntry_uom
321 ,misc2.rndg_cd misc2_rndg_cd
322 ,misc3.acty_base_rt_id misc3_abr_id
323 ,misc3.nnmntry_uom misc3_nnmntry_uom
324 ,misc3.rndg_cd misc3_rndg_cd
325 ,ss.acty_base_rt_id stat_sal_abr_id
326 ,ss.nnmntry_uom stat_sal_nnmntry_uom
327 ,ss.rndg_cd stat_sal_rndg_cd
328 ,rec.acty_base_rt_id rec_abr_id
329 ,rec.nnmntry_uom rec_nnmntry_uom
330 ,rec.rndg_cd rec_rndg_cd
331 ,tc.acty_base_rt_id tot_comp_abr_id
332 ,tc.nnmntry_uom tot_comp_nnmntry_uom
333 ,tc.rndg_cd tot_comp_rndg_cd
334 ,oc.acty_base_rt_id oth_comp_abr_id
335 ,oc.nnmntry_uom oth_comp_nnmntry_uom
336 ,oc.rndg_cd oth_comp_rndg_cd
337 ,get_actual_flag(pl.pl_id,pl.group_pl_id,p_effective_date)
338 actual_flag
339 ,pl.nip_acty_ref_perd_cd acty_ref_perd_cd
340 ,bg.legislation_code legislation_code
341 ,benutils.get_pl_annualization_factor(pl.nip_acty_ref_perd_cd)
342 pl_annulization_factor
343 ,decode(pl.pl_id,
344 p_group_pl_id, pl.pl_stat_cd,
345 'A') pl_stat_cd
346 ,nvl(cur.precision, 2) uom_precision
347 ,enp.enrt_perd_id enrt_perd_id
348 ,enp.yr_perd_id yr_perd_id
349 from ben_pl_f pl
350 ,ben_acty_base_rt_f ws
351 ,ben_acty_base_rt_f db
352 ,ben_acty_base_rt_f wb
353 ,ben_acty_base_rt_f rsrv
354 ,ben_acty_base_rt_f es
355 ,ben_acty_base_rt_f misc1
356 ,ben_acty_base_rt_f misc2
357 ,ben_acty_base_rt_f misc3
358 ,ben_acty_base_rt_f ss
359 ,ben_acty_base_rt_f rec
360 ,ben_acty_base_rt_f tc
361 ,ben_acty_base_rt_f oc
362 ,per_business_groups bg
363 ,fnd_currencies cur
364 ,ben_popl_enrt_typ_cycl_f petc
365 ,ben_enrt_perd enp
366 where pl.group_pl_id = p_group_pl_id
367 and p_effective_date between pl.effective_start_date and
368 pl.effective_end_date
369 and pl.pl_stat_cd in ('A', 'I')
370 and petc.pl_id = pl.pl_id
371 and petc.enrt_typ_cycl_cd = 'COMP'
372 and p_effective_date between
373 petc.effective_start_date and petc.effective_end_date
374 and enp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
375 and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
376 and ws.pl_id (+) = pl.pl_id
377 and p_effective_date between ws.effective_start_date(+) and
378 ws.effective_end_date (+)
379 and ws.acty_typ_cd (+) = 'CWBWS'
380 and ws.ACTY_BASE_RT_STAT_CD (+)= 'A'
381 and db.pl_id (+) = pl.pl_id
382 and p_effective_date between db.effective_start_date(+) and
383 db.effective_end_date (+)
384 and db.acty_typ_cd (+) = 'CWBDB'
385 and db.ACTY_BASE_RT_STAT_CD (+)= 'A'
386 and wb.pl_id (+) = pl.pl_id
387 and p_effective_date between wb.effective_start_date(+) and
388 wb.effective_end_date (+)
389 and wb.acty_typ_cd (+) = 'CWBWB'
390 and wb.ACTY_BASE_RT_STAT_CD (+)= 'A'
391 and rsrv.pl_id (+) = pl.pl_id
392 and p_effective_date between rsrv.effective_start_date(+) and
393 rsrv.effective_end_date (+)
394 and rsrv.acty_typ_cd (+) = 'CWBR'
395 and rsrv.ACTY_BASE_RT_STAT_CD (+)= 'A'
396 and es.pl_id (+) = pl.pl_id
397 and p_effective_date between es.effective_start_date(+) and
398 es.effective_end_date (+)
399 and es.acty_typ_cd (+) = 'CWBES'
400 and es.ACTY_BASE_RT_STAT_CD (+)= 'A'
401 and misc1.pl_id (+) = pl.pl_id
402 and p_effective_date between misc1.effective_start_date(+) and
403 misc1.effective_end_date (+)
404 and misc1.acty_typ_cd (+) = 'CWBMR1'
405 and misc1.ACTY_BASE_RT_STAT_CD (+)= 'A'
406 and misc2.pl_id (+) = pl.pl_id
407 and p_effective_date between misc2.effective_start_date(+) and
408 misc2.effective_end_date (+)
409 and misc2.acty_typ_cd (+) = 'CWBMR2'
410 and misc2.ACTY_BASE_RT_STAT_CD (+)= 'A'
411 and misc3.pl_id (+) = pl.pl_id
412 and p_effective_date between misc3.effective_start_date(+) and
413 misc3.effective_end_date (+)
414 and misc3.acty_typ_cd (+) = 'CWBMR3'
415 and misc3.ACTY_BASE_RT_STAT_CD (+)= 'A'
416 and ss.pl_id (+) = pl.pl_id
417 and p_effective_date between ss.effective_start_date(+) and
418 ss.effective_end_date (+)
419 and ss.acty_typ_cd (+) = 'CWBSS'
420 and ss.ACTY_BASE_RT_STAT_CD (+)= 'A'
421 and rec.pl_id (+) = pl.pl_id
422 and p_effective_date between rec.effective_start_date(+) and
423 rec.effective_end_date (+)
424 and rec.acty_typ_cd (+) = 'CWBRA'
425 and rec.ACTY_BASE_RT_STAT_CD (+)= 'A'
429 and tc.acty_typ_cd (+) = 'CWBTC'
426 and tc.pl_id (+) = pl.pl_id
427 and p_effective_date between tc.effective_start_date(+) and
428 tc.effective_end_date (+)
430 and tc.ACTY_BASE_RT_STAT_CD (+)= 'A'
431 and oc.pl_id (+) = pl.pl_id
432 and p_effective_date between oc.effective_start_date(+) and
433 oc.effective_end_date (+)
434 and oc.acty_typ_cd (+) = 'CWBOS'
435 and oc.ACTY_BASE_RT_STAT_CD (+)= 'A'
436 and bg.business_group_id = pl.business_group_id
437 and pl.nip_pl_uom = cur.currency_code (+)
438 -- Refresh that local/group plan only if no rows exist for the plan.
439 and not exists (select 'Y'
440 from ben_cwb_pl_dsgn dsgn
441 where dsgn.group_pl_id = p_group_pl_id
442 and dsgn.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
443 and dsgn.pl_id = pl.pl_id);
444
445 -- Bug 3975857 : In cursor CSR_OIPLS pick up only Active Standard Rates. Added clause <<ACTY_BASE_RT_STAT_CD (+)= 'A'>>
446 -- to the cursor query
447 cursor csr_oipls(p_pl_id number
448 ,p_effective_date date) is
449 select oipl.oipl_id oipl_id
450 ,opt.name name
451 ,group_oipl.oipl_id group_oipl_id
452 ,oipl.hidden_flag opt_hidden_flag
453 ,oipl.opt_id opt_id
454 ,opt.group_opt_id group_opt_id
455 ,oipl.business_group_id business_group_id
456 ,ws.acty_base_rt_id ws_abr_id
457 ,ws.nnmntry_uom ws_nnmntry_uom
458 ,ws.rndg_cd ws_rndg_cd
459 ,ws.sub_acty_typ_cd ws_sub_acty_typ_cd
460 ,ws.element_type_id ws_element_type_id
461 ,ws.input_value_id ws_input_value_id
462 ,db.acty_base_rt_id dist_bdgt_abr_id
463 ,db.nnmntry_uom dist_bdgt_nnmntry_uom
464 ,db.rndg_cd dist_bdgt_rndg_cd
465 ,wb.acty_base_rt_id ws_bdgt_abr_id
466 ,wb.nnmntry_uom ws_bdgt_nnmntry_uom
467 ,wb.rndg_cd ws_bdgt_rndg_cd
468 ,rsrv.acty_base_rt_id rsrv_abr_id
469 ,rsrv.nnmntry_uom rsrv_nnmntry_uom
470 ,rsrv.rndg_cd rsrv_rndg_cd
471 ,es.acty_base_rt_id elig_sal_abr_id
472 ,es.nnmntry_uom elig_sal_nnmntry_uom
473 ,es.rndg_cd elig_sal_rndg_cd
474 ,misc1.acty_base_rt_id misc1_abr_id
475 ,misc1.nnmntry_uom misc1_nnmntry_uom
476 ,misc1.rndg_cd misc1_rndg_cd
477 ,misc2.acty_base_rt_id misc2_abr_id
478 ,misc2.nnmntry_uom misc2_nnmntry_uom
479 ,misc2.rndg_cd misc2_rndg_cd
480 ,misc3.acty_base_rt_id misc3_abr_id
481 ,misc3.nnmntry_uom misc3_nnmntry_uom
482 ,misc3.rndg_cd misc3_rndg_cd
483 ,ss.acty_base_rt_id stat_sal_abr_id
484 ,ss.nnmntry_uom stat_sal_nnmntry_uom
485 ,ss.rndg_cd stat_sal_rndg_cd
486 ,rec.acty_base_rt_id rec_abr_id
487 ,rec.nnmntry_uom rec_nnmntry_uom
488 ,rec.rndg_cd rec_rndg_cd
489 ,tc.acty_base_rt_id tot_comp_abr_id
490 ,tc.nnmntry_uom tot_comp_nnmntry_uom
491 ,tc.rndg_cd tot_comp_rndg_cd
492 ,oc.acty_base_rt_id oth_comp_abr_id
493 ,oc.nnmntry_uom oth_comp_nnmntry_uom
494 ,oc.rndg_cd oth_comp_rndg_cd
495 from ben_oipl_f oipl
496 ,ben_opt_f opt
497 ,ben_oipl_f group_oipl
498 ,ben_pl_f pl
499 ,ben_acty_base_rt_f ws
500 ,ben_acty_base_rt_f db
501 ,ben_acty_base_rt_f wb
502 ,ben_acty_base_rt_f rsrv
503 ,ben_acty_base_rt_f es
504 ,ben_acty_base_rt_f misc1
505 ,ben_acty_base_rt_f misc2
506 ,ben_acty_base_rt_f misc3
507 ,ben_acty_base_rt_f ss
508 ,ben_acty_base_rt_f rec
509 ,ben_acty_base_rt_f tc
510 ,ben_acty_base_rt_f oc
511 where oipl.pl_id = p_pl_id
512 and p_effective_date between oipl.effective_start_date and
513 oipl.effective_end_date
514 and oipl.oipl_stat_cd in ('A', 'I')
515 and opt.opt_id = oipl.opt_id
516 and p_effective_date between opt.effective_start_date and
517 opt.effective_end_date
518 and opt.group_opt_id= group_oipl.opt_id
519 and group_oipl.pl_id = pl.group_pl_id
520 and p_effective_date between group_oipl.effective_start_date and
521 group_oipl.effective_end_date
522 and pl.pl_id = oipl.pl_id
526 and p_effective_date between ws.effective_start_date(+) and
523 and p_effective_date between pl.effective_start_date and
524 pl.effective_end_date
525 and ws.oipl_id (+) = oipl.oipl_id
527 ws.effective_end_date (+)
528 and ws.acty_typ_cd (+) = 'CWBWS'
529 and ws.ACTY_BASE_RT_STAT_CD (+)= 'A'
530 and db.oipl_id (+) = oipl.oipl_id
531 and p_effective_date between db.effective_start_date(+) and
532 db.effective_end_date (+)
533 and db.acty_typ_cd (+) = 'CWBDB'
534 and db.ACTY_BASE_RT_STAT_CD (+)= 'A'
535 and wb.oipl_id (+) = oipl.oipl_id
536 and p_effective_date between wb.effective_start_date(+) and
537 wb.effective_end_date (+)
538 and wb.acty_typ_cd (+) = 'CWBWB'
539 and wb.ACTY_BASE_RT_STAT_CD (+)= 'A'
540 and rsrv.oipl_id (+) = oipl.oipl_id
541 and p_effective_date between rsrv.effective_start_date(+) and
542 rsrv.effective_end_date (+)
543 and rsrv.acty_typ_cd (+) = 'CWBR'
544 and rsrv.ACTY_BASE_RT_STAT_CD (+)= 'A'
545 and es.oipl_id (+) = oipl.oipl_id
546 and p_effective_date between es.effective_start_date(+) and
547 es.effective_end_date (+)
548 and es.acty_typ_cd (+) = 'CWBES'
549 and es.ACTY_BASE_RT_STAT_CD (+)= 'A'
550 and misc1.oipl_id (+) = oipl.oipl_id
551 and p_effective_date between misc1.effective_start_date(+) and
552 misc1.effective_end_date (+)
553 and misc1.acty_typ_cd (+) = 'CWBMR1'
554 and misc1.ACTY_BASE_RT_STAT_CD (+)= 'A'
555 and misc2.oipl_id (+) = oipl.oipl_id
556 and p_effective_date between misc2.effective_start_date(+) and
557 misc2.effective_end_date (+)
558 and misc2.acty_typ_cd (+) = 'CWBMR2'
559 and misc2.ACTY_BASE_RT_STAT_CD (+)= 'A'
560 and misc3.oipl_id (+) = oipl.oipl_id
561 and p_effective_date between misc3.effective_start_date(+) and
562 misc3.effective_end_date (+)
563 and misc3.acty_typ_cd (+) = 'CWBMR3'
564 and misc3.ACTY_BASE_RT_STAT_CD (+)= 'A'
565 and ss.oipl_id (+) = oipl.oipl_id
566 and p_effective_date between ss.effective_start_date(+) and
567 ss.effective_end_date (+)
568 and ss.acty_typ_cd (+) = 'CWBSS'
569 and ss.ACTY_BASE_RT_STAT_CD (+)= 'A'
570 and rec.oipl_id (+) = oipl.oipl_id
571 and p_effective_date between rec.effective_start_date(+) and
572 rec.effective_end_date (+)
573 and rec.acty_typ_cd (+) = 'CWBRA'
574 and rec.ACTY_BASE_RT_STAT_CD (+)= 'A'
575 and tc.oipl_id (+) = oipl.oipl_id
576 and p_effective_date between tc.effective_start_date(+) and
577 tc.effective_end_date (+)
578 and tc.acty_typ_cd (+) = 'CWBTC'
579 and tc.ACTY_BASE_RT_STAT_CD (+)= 'A'
580 and oc.oipl_id (+) = oipl.oipl_id
581 and p_effective_date between oc.effective_start_date(+) and
582 oc.effective_end_date (+)
583 and oc.acty_typ_cd (+) = 'CWBOS'
584 and oc.ACTY_BASE_RT_STAT_CD (+)= 'A';
585 --
586 -- cursor for fetching the ordr_num for oipls of group plan
587 cursor csr_grp_opt_ordr(p_group_pl_id number
588 ,p_effective_date date)is
589 select opt_id
590 from ben_oipl_f
591 where pl_id = p_group_pl_id
592 and p_effective_date between effective_start_date and effective_end_date
593 order by ordr_num;
594 --
595 -- local variable declarations
596 l_group_pl_row csr_group_pl%rowtype;
597 --
598 l_opt_count number;
599 l_oipl_ordr_num number;
600 --
601 l_proc varchar2(72) := g_package||'refresh_pl_dsgn';
602 --
603 begin
604 --
605 if g_debug then
606 hr_utility.set_location('Entering:'|| l_proc, 10);
607 end if;
608 --
609 if p_refresh_always = 'Y' then
610 --
611 if g_debug then
615 -- refresh. delete plans and oipls from the pl_dsgn
612 hr_utility.set_location('l_proc'|| l_proc, 17);
613 end if;
614 --
616 delete from ben_cwb_pl_dsgn
617 where group_pl_id = p_group_pl_id
618 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
619 --
620 end if;
621 --
622 --
623 if g_debug then
624 hr_utility.set_location(l_proc, 20);
625 end if;
626 --
627 -- get the group plan row
628 open csr_group_pl(p_group_pl_id
629 ,p_lf_evt_ocrd_dt
630 ,p_effective_date);
631 fetch csr_group_pl into l_group_pl_row;
632 --
633 if csr_group_pl%notfound then
634 close csr_group_pl;
635 return;
636 end if;
637 close csr_group_pl;
638 --
639 -- Get the within year dates.
640 --
641 if l_group_pl_row.wthn_strt_day is null or l_group_pl_row.wthn_strt_mo is null then
642 l_group_pl_row.wthn_yr_start_dt := l_group_pl_row.yr_perd_start_dt;
643 else
644 l_group_pl_row.wthn_yr_start_dt := get_valid_date(l_group_pl_row.wthn_strt_day,
645 l_group_pl_row.wthn_strt_mo,
646 l_group_pl_row.yr_perd_start_dt,
647 l_group_pl_row.yr_perd_end_dt,
648 l_group_pl_row.yr_perd_start_dt);
649 end if;
650
651 if l_group_pl_row.wthn_end_day is null or l_group_pl_row.wthn_end_mo is null then
652 l_group_pl_row.wthn_yr_end_dt := l_group_pl_row.yr_perd_end_dt;
653 else
654 l_group_pl_row.wthn_yr_end_dt := get_valid_date(l_group_pl_row.wthn_end_day,
655 l_group_pl_row.wthn_end_mo,
656 l_group_pl_row.yr_perd_start_dt,
657 l_group_pl_row.yr_perd_end_dt,
658 l_group_pl_row.yr_perd_end_dt);
659 end if;
660 --
661 -- check for the options in the group plan
662 open csr_grp_opt_ordr(p_group_pl_id
663 ,nvl(p_effective_date,nvl(l_group_pl_row.effective_date
664 ,p_lf_evt_ocrd_dt)));
665 fetch csr_grp_opt_ordr bulk collect into g_grp_opt;
666 close csr_grp_opt_ordr;
667 --
668 if g_debug then
669 hr_utility.set_location(l_proc, 30);
670 end if;
671 --
672 for pl in csr_pls(p_group_pl_id
673 ,nvl(p_effective_date,nvl(l_group_pl_row.effective_date
674 ,p_lf_evt_ocrd_dt))
675 ,l_group_pl_row.pl_uom
676 ,l_group_pl_row.business_group_id)
677 loop
678 --
679 if g_debug then
680 hr_utility.set_location(l_proc, 40);
681 end if;
682 --
683 -- first insert the oipl rows.
684 --
685 -- Intialize the count
686 l_opt_count := 0;
687 --
688 for oipl in csr_oipls(pl.pl_id
689 ,nvl(p_effective_date,nvl(l_group_pl_row.effective_date
690 ,p_lf_evt_ocrd_dt)))
691 loop
692 --
693 if g_debug then
694 hr_utility.set_location(l_proc, 50);
695 end if;
696 --
697 -- Increment the count
698 l_opt_count := l_opt_count + 1;
699 l_oipl_ordr_num := get_opt_ordr_in_grp(oipl.group_opt_id);
700 --
701 insert into ben_cwb_pl_dsgn
702 (pl_id
703 ,lf_evt_ocrd_dt
704 ,oipl_id
705 ,effective_date
706 ,name
707 ,group_pl_id
708 ,group_oipl_id
709 ,opt_hidden_flag
710 ,opt_id
711 ,pl_uom
712 ,pl_ordr_num
713 ,oipl_ordr_num
714 ,pl_xchg_rate
715 ,uses_bdgt_flag
716 ,prsrv_bdgt_cd
717 ,business_group_id
718 ,ws_abr_id
719 ,ws_nnmntry_uom
720 ,ws_rndg_cd
721 ,ws_sub_acty_typ_cd
722 ,dist_bdgt_abr_id
723 ,dist_bdgt_nnmntry_uom
724 ,dist_bdgt_rndg_cd
725 ,ws_bdgt_abr_id
726 ,ws_bdgt_nnmntry_uom
727 ,ws_bdgt_rndg_cd
728 ,rsrv_abr_id
729 ,rsrv_nnmntry_uom
730 ,rsrv_rndg_cd
731 ,elig_sal_abr_id
732 ,elig_sal_nnmntry_uom
733 ,elig_sal_rndg_cd
734 ,misc1_abr_id
735 ,misc1_nnmntry_uom
736 ,misc1_rndg_cd
737 ,misc2_abr_id
738 ,misc2_nnmntry_uom
739 ,misc2_rndg_cd
740 ,misc3_abr_id
741 ,misc3_nnmntry_uom
745 ,stat_sal_rndg_cd
742 ,misc3_rndg_cd
743 ,stat_sal_abr_id
744 ,stat_sal_nnmntry_uom
746 ,rec_abr_id
747 ,rec_nnmntry_uom
748 ,rec_rndg_cd
749 ,tot_comp_abr_id
750 ,tot_comp_nnmntry_uom
751 ,tot_comp_rndg_cd
752 ,oth_comp_abr_id
753 ,oth_comp_nnmntry_uom
754 ,oth_comp_rndg_cd
755 ,actual_flag
756 ,acty_ref_perd_cd
757 ,legislation_code
758 ,pl_annulization_factor
759 ,pl_stat_cd
760 ,uom_precision
761 ,ws_element_type_id
762 ,ws_input_value_id
763 ,data_freeze_date
764 ,object_version_number)
765 values (pl.pl_id
766 ,p_lf_evt_ocrd_dt
767 ,oipl.oipl_id
768 ,l_group_pl_row.effective_date
769 ,oipl.name
770 ,p_group_pl_id
771 ,oipl.group_oipl_id
772 ,oipl.opt_hidden_flag
773 ,oipl.opt_id
774 ,pl.pl_uom
775 ,pl.pl_ordr_num
776 ,l_oipl_ordr_num
777 ,pl.pl_xchg_rate
778 ,l_group_pl_row.uses_bdgt_flag
779 ,l_group_pl_row.prsrv_bdgt_cd
780 ,oipl.business_group_id
781 ,oipl.ws_abr_id
782 ,oipl.ws_nnmntry_uom
783 ,oipl.ws_rndg_cd
784 ,oipl.ws_sub_acty_typ_cd
785 ,oipl.dist_bdgt_abr_id
786 ,oipl.dist_bdgt_nnmntry_uom
787 ,oipl.dist_bdgt_rndg_cd
788 ,oipl.ws_bdgt_abr_id
789 ,oipl.ws_bdgt_nnmntry_uom
790 ,oipl.ws_bdgt_rndg_cd
791 ,oipl.rsrv_abr_id
792 ,oipl.rsrv_nnmntry_uom
793 ,oipl.rsrv_rndg_cd
794 ,oipl.elig_sal_abr_id
795 ,oipl.elig_sal_nnmntry_uom
796 ,oipl.elig_sal_rndg_cd
797 ,oipl.misc1_abr_id
798 ,oipl.misc1_nnmntry_uom
799 ,oipl.misc1_rndg_cd
800 ,oipl.misc2_abr_id
801 ,oipl.misc2_nnmntry_uom
802 ,oipl.misc2_rndg_cd
803 ,oipl.misc3_abr_id
804 ,oipl.misc3_nnmntry_uom
805 ,oipl.misc3_rndg_cd
806 ,oipl.stat_sal_abr_id
807 ,oipl.stat_sal_nnmntry_uom
808 ,oipl.stat_sal_rndg_cd
809 ,oipl.rec_abr_id
810 ,oipl.rec_nnmntry_uom
811 ,oipl.rec_rndg_cd
812 ,oipl.tot_comp_abr_id
813 ,oipl.tot_comp_nnmntry_uom
814 ,oipl.tot_comp_rndg_cd
815 ,oipl.oth_comp_abr_id
816 ,oipl.oth_comp_nnmntry_uom
817 ,oipl.oth_comp_rndg_cd
818 ,pl.actual_flag
819 ,pl.acty_ref_perd_cd
820 ,pl.legislation_code
821 ,pl.pl_annulization_factor
822 ,'A'
823 ,pl.uom_precision
824 ,oipl.ws_element_type_id
825 ,oipl.ws_input_value_id
826 ,l_group_pl_row.data_freeze_date
827 ,1); -- new row. so ovn is 1
828 end loop; -- of l_oipl_rows
829
830 --
831 if g_debug then
832 hr_utility.set_location(l_proc, 60);
833 end if;
834 --
835 --insert the plan row
836 insert into ben_cwb_pl_dsgn
837 (pl_id
838 ,lf_evt_ocrd_dt
839 ,oipl_id
840 ,effective_date
841 ,name
842 ,group_pl_id
843 ,group_oipl_id
844 ,pl_uom
845 ,pl_ordr_num
846 ,pl_xchg_rate
847 ,opt_count
848 ,uses_bdgt_flag
849 ,prsrv_bdgt_cd
850 ,upd_start_dt
851 ,upd_end_dt
852 ,approval_mode
853 ,enrt_perd_start_dt
854 ,enrt_perd_end_dt
855 ,yr_perd_start_dt
856 ,yr_perd_end_dt
857 ,wthn_yr_start_dt
858 ,wthn_yr_end_dt
859 ,enrt_perd_id
860 ,yr_perd_id
861 ,business_group_id
862 ,perf_revw_strt_dt
863 ,asg_updt_eff_date
864 ,emp_interview_typ_cd
865 ,salary_change_reason
866 ,ws_abr_id
867 ,ws_nnmntry_uom
868 ,ws_rndg_cd
869 ,ws_sub_acty_typ_cd
870 ,dist_bdgt_abr_id
871 ,dist_bdgt_nnmntry_uom
872 ,dist_bdgt_rndg_cd
873 ,ws_bdgt_abr_id
874 ,ws_bdgt_nnmntry_uom
875 ,ws_bdgt_rndg_cd
876 ,rsrv_abr_id
877 ,rsrv_nnmntry_uom
878 ,rsrv_rndg_cd
879 ,elig_sal_abr_id
880 ,elig_sal_nnmntry_uom
881 ,elig_sal_rndg_cd
882 ,misc1_abr_id
883 ,misc1_nnmntry_uom
884 ,misc1_rndg_cd
885 ,misc2_abr_id
886 ,misc2_nnmntry_uom
887 ,misc2_rndg_cd
888 ,misc3_abr_id
889 ,misc3_nnmntry_uom
890 ,misc3_rndg_cd
891 ,stat_sal_abr_id
892 ,stat_sal_nnmntry_uom
893 ,stat_sal_rndg_cd
894 ,rec_abr_id
895 ,rec_nnmntry_uom
896 ,rec_rndg_cd
897 ,tot_comp_abr_id
898 ,tot_comp_nnmntry_uom
899 ,tot_comp_rndg_cd
900 ,oth_comp_abr_id
901 ,oth_comp_nnmntry_uom
902 ,oth_comp_rndg_cd
903 ,actual_flag
904 ,acty_ref_perd_cd
905 ,legislation_code
906 ,pl_annulization_factor
907 ,pl_stat_cd
908 ,uom_precision
909 ,ws_element_type_id
910 ,ws_input_value_id
911 ,data_freeze_date
912 ,object_version_number)
913 values
914 (pl.pl_id
915 ,p_lf_evt_ocrd_dt
916 ,-1 -- for plans oipl_id is -1
917 ,l_group_pl_row.effective_date
918 ,pl.name
919 ,p_group_pl_id
920 ,-1 -- for plans group oipl id is -1
921 ,pl.pl_uom
922 ,pl.pl_ordr_num
923 ,pl.pl_xchg_rate
924 ,l_opt_count
925 ,l_group_pl_row.uses_bdgt_flag
926 ,l_group_pl_row.prsrv_bdgt_cd
927 ,l_group_pl_row.upd_start_dt
928 ,l_group_pl_row.upd_end_dt
929 ,l_group_pl_row.approval_mode
930 ,l_group_pl_row.enrt_perd_start_dt
931 ,l_group_pl_row.enrt_perd_end_dt
932 ,l_group_pl_row.yr_perd_start_dt
936 ,pl.enrt_perd_id
933 ,l_group_pl_row.yr_perd_end_dt
934 ,l_group_pl_row.wthn_yr_start_dt
935 ,l_group_pl_row.wthn_yr_end_dt
937 ,pl.yr_perd_id
938 ,pl.business_group_id
939 ,l_group_pl_row.perf_revw_strt_dt
940 ,l_group_pl_row.asg_updt_eff_date
941 ,l_group_pl_row.emp_interview_typ_cd
942 ,l_group_pl_row.salary_change_reason
943 ,pl.ws_abr_id
944 ,pl.ws_nnmntry_uom
945 ,pl.ws_rndg_cd
946 ,pl.ws_sub_acty_typ_cd
947 ,pl.dist_bdgt_abr_id
948 ,pl.dist_bdgt_nnmntry_uom
949 ,pl.dist_bdgt_rndg_cd
950 ,pl.ws_bdgt_abr_id
951 ,pl.ws_bdgt_nnmntry_uom
952 ,pl.ws_bdgt_rndg_cd
953 ,pl.rsrv_abr_id
954 ,pl.rsrv_nnmntry_uom
955 ,pl.rsrv_rndg_cd
956 ,pl.elig_sal_abr_id
957 ,pl.elig_sal_nnmntry_uom
958 ,pl.elig_sal_rndg_cd
959 ,pl.misc1_abr_id
960 ,pl.misc1_nnmntry_uom
961 ,pl.misc1_rndg_cd
962 ,pl.misc2_abr_id
963 ,pl.misc2_nnmntry_uom
964 ,pl.misc2_rndg_cd
965 ,pl.misc3_abr_id
966 ,pl.misc3_nnmntry_uom
967 ,pl.misc3_rndg_cd
968 ,pl.stat_sal_abr_id
969 ,pl.stat_sal_nnmntry_uom
970 ,pl.stat_sal_rndg_cd
971 ,pl.rec_abr_id
972 ,pl.rec_nnmntry_uom
973 ,pl.rec_rndg_cd
974 ,pl.tot_comp_abr_id
975 ,pl.tot_comp_nnmntry_uom
976 ,pl.tot_comp_rndg_cd
977 ,pl.oth_comp_abr_id
978 ,pl.oth_comp_nnmntry_uom
979 ,pl.oth_comp_rndg_cd
980 ,pl.actual_flag
981 ,pl.acty_ref_perd_cd
982 ,pl.legislation_code
983 ,pl.pl_annulization_factor
984 ,pl.pl_stat_cd
985 ,pl.uom_precision
986 ,pl.ws_element_type_id
987 ,pl.ws_input_value_id
988 ,l_group_pl_row.data_freeze_date
989 ,1); -- new row. so ovn is 1
990 end loop; -- l_pl_rows
991 --
992 if g_debug then
993 hr_utility.set_location(' Leaving:'|| l_proc, 99);
994 end if;
995 --
996 end; -- end of refresh_pl_dsgn
997
998 --
999 -- --------------------------------------------------------------------------
1000 -- |--------------------------< delete_pl_dsgn >----------------------------|
1001 -- --------------------------------------------------------------------------
1002 -- Description
1003 -- This procedure deletes the ben_cwb_pl_dsgn table when no cwb data exists.
1004 -- Input parameters
1005 -- p_group_pl_id : Group Plan Id
1006 -- p_lf_evt_ocrd_dt : Life Event Occured Date
1007 --
1008 procedure delete_pl_dsgn(p_group_pl_id in number
1009 ,p_lf_evt_ocrd_dt in date) is
1010
1011 cursor c_data_exists is
1012 select 'Y'
1013 from ben_cwb_person_info i
1014 where i.group_pl_id = p_group_pl_id
1015 and i.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
1016
1017 l_data_exists varchar2(1) := null;
1018
1019 begin
1020
1021 open c_data_exists;
1022 fetch c_data_exists into l_data_exists;
1023 close c_data_exists;
1024
1025 if l_data_exists is null then
1026 delete ben_cwb_pl_dsgn pl
1027 where pl.group_pl_id = p_group_pl_id
1028 and pl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
1029 end if;
1030
1031 end delete_pl_dsgn;
1032
1033 end ben_cwb_pl_dsgn_pkg;
1034