[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.12010000.2 2010/02/02 11:51:13 sgnanama ship $ */
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
315 ,es.rndg_cd elig_sal_rndg_cd
316 ,misc1.acty_base_rt_id misc1_abr_id
317 ,misc1.nnmntry_uom misc1_nnmntry_uom
318 ,misc1.rndg_cd misc1_rndg_cd
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'
426 and tc.pl_id (+) = pl.pl_id
427 and p_effective_date between tc.effective_start_date(+) and
428 tc.effective_end_date (+)
429 and tc.acty_typ_cd (+) = 'CWBTC'
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
523 and p_effective_date between pl.effective_start_date and
524 pl.effective_end_date
525 and ws.oipl_id (+) = oipl.oipl_id
526 and p_effective_date between ws.effective_start_date(+) and
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 -- ER:8369634
596 cursor csr_grp_plan_extra_info
597 is
598 select PLI_INFORMATION3 post_zero_salary_increase ,
599 PLI_INFORMATION4 show_appraisals_n_days
600 from ben_pl_extra_info
601 where INFORMATION_TYPE='CWB_CUSTOM_DOWNLOAD'
602 and pl_id = p_group_pl_id;
603
604
605 -- local variable declarations
606 l_group_pl_row csr_group_pl%rowtype;
607 --
608 l_opt_count number;
609 l_oipl_ordr_num number;
610 --
611 l_proc varchar2(72) := g_package||'refresh_pl_dsgn';
612 --
613 begin
614 --
615 if g_debug then
616 hr_utility.set_location('Entering:'|| l_proc, 10);
617 end if;
618 --
619 if p_refresh_always = 'Y' then
620 --
621 if g_debug then
622 hr_utility.set_location('l_proc'|| l_proc, 17);
623 end if;
624 --
625 -- refresh. delete plans and oipls from the pl_dsgn
626 delete from ben_cwb_pl_dsgn
627 where group_pl_id = p_group_pl_id
628 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
629 --
630 end if;
631 --
632 --
633 if g_debug then
634 hr_utility.set_location(l_proc, 20);
635 end if;
636 --
637 -- get the group plan row
638 open csr_group_pl(p_group_pl_id
639 ,p_lf_evt_ocrd_dt
640 ,p_effective_date);
641 fetch csr_group_pl into l_group_pl_row;
642 --
643 if csr_group_pl%notfound then
644 close csr_group_pl;
645 return;
646 end if;
647 close csr_group_pl;
648 --
649 -- Get the within year dates.
650 --
651 if l_group_pl_row.wthn_strt_day is null or l_group_pl_row.wthn_strt_mo is null then
652 l_group_pl_row.wthn_yr_start_dt := l_group_pl_row.yr_perd_start_dt;
653 else
654 l_group_pl_row.wthn_yr_start_dt := get_valid_date(l_group_pl_row.wthn_strt_day,
655 l_group_pl_row.wthn_strt_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_start_dt);
659 end if;
660
661 if l_group_pl_row.wthn_end_day is null or l_group_pl_row.wthn_end_mo is null then
662 l_group_pl_row.wthn_yr_end_dt := l_group_pl_row.yr_perd_end_dt;
663 else
664 l_group_pl_row.wthn_yr_end_dt := get_valid_date(l_group_pl_row.wthn_end_day,
665 l_group_pl_row.wthn_end_mo,
666 l_group_pl_row.yr_perd_start_dt,
667 l_group_pl_row.yr_perd_end_dt,
668 l_group_pl_row.yr_perd_end_dt);
669 end if;
670 --
671 -- check for the options in the group plan
672 open csr_grp_opt_ordr(p_group_pl_id
673 ,nvl(p_effective_date,nvl(l_group_pl_row.effective_date
674 ,p_lf_evt_ocrd_dt)));
675 fetch csr_grp_opt_ordr bulk collect into g_grp_opt;
676 close csr_grp_opt_ordr;
677 --
678 if g_debug then
679 hr_utility.set_location(l_proc, 30);
680 end if;
681 --
682 for pl in csr_pls(p_group_pl_id
683 ,nvl(p_effective_date,nvl(l_group_pl_row.effective_date
684 ,p_lf_evt_ocrd_dt))
685 ,l_group_pl_row.pl_uom
686 ,l_group_pl_row.business_group_id)
687 loop
688 --
689 if g_debug then
690 hr_utility.set_location(l_proc, 40);
691 end if;
692 --
693 -- first insert the oipl rows.
694 --
695 -- Intialize the count
696 l_opt_count := 0;
697 --
698 for oipl in csr_oipls(pl.pl_id
699 ,nvl(p_effective_date,nvl(l_group_pl_row.effective_date
700 ,p_lf_evt_ocrd_dt)))
701 loop
702 --
703 if g_debug then
704 hr_utility.set_location(l_proc, 50);
705 end if;
706 --
707 -- Increment the count
708 l_opt_count := l_opt_count + 1;
709 l_oipl_ordr_num := get_opt_ordr_in_grp(oipl.group_opt_id);
710 --
711 insert into ben_cwb_pl_dsgn
712 (pl_id
713 ,lf_evt_ocrd_dt
714 ,oipl_id
715 ,effective_date
716 ,name
717 ,group_pl_id
718 ,group_oipl_id
719 ,opt_hidden_flag
720 ,opt_id
721 ,pl_uom
722 ,pl_ordr_num
723 ,oipl_ordr_num
724 ,pl_xchg_rate
725 ,uses_bdgt_flag
726 ,prsrv_bdgt_cd
727 ,business_group_id
728 ,ws_abr_id
729 ,ws_nnmntry_uom
730 ,ws_rndg_cd
731 ,ws_sub_acty_typ_cd
732 ,dist_bdgt_abr_id
733 ,dist_bdgt_nnmntry_uom
734 ,dist_bdgt_rndg_cd
735 ,ws_bdgt_abr_id
736 ,ws_bdgt_nnmntry_uom
737 ,ws_bdgt_rndg_cd
738 ,rsrv_abr_id
739 ,rsrv_nnmntry_uom
740 ,rsrv_rndg_cd
741 ,elig_sal_abr_id
742 ,elig_sal_nnmntry_uom
743 ,elig_sal_rndg_cd
744 ,misc1_abr_id
745 ,misc1_nnmntry_uom
746 ,misc1_rndg_cd
747 ,misc2_abr_id
748 ,misc2_nnmntry_uom
749 ,misc2_rndg_cd
750 ,misc3_abr_id
751 ,misc3_nnmntry_uom
752 ,misc3_rndg_cd
753 ,stat_sal_abr_id
754 ,stat_sal_nnmntry_uom
755 ,stat_sal_rndg_cd
756 ,rec_abr_id
757 ,rec_nnmntry_uom
758 ,rec_rndg_cd
759 ,tot_comp_abr_id
760 ,tot_comp_nnmntry_uom
761 ,tot_comp_rndg_cd
762 ,oth_comp_abr_id
763 ,oth_comp_nnmntry_uom
764 ,oth_comp_rndg_cd
765 ,actual_flag
766 ,acty_ref_perd_cd
767 ,legislation_code
768 ,pl_annulization_factor
769 ,pl_stat_cd
770 ,uom_precision
771 ,ws_element_type_id
772 ,ws_input_value_id
773 ,data_freeze_date
774 ,object_version_number)
775 values (pl.pl_id
776 ,p_lf_evt_ocrd_dt
777 ,oipl.oipl_id
778 ,l_group_pl_row.effective_date
779 ,oipl.name
780 ,p_group_pl_id
781 ,oipl.group_oipl_id
782 ,oipl.opt_hidden_flag
783 ,oipl.opt_id
784 ,pl.pl_uom
785 ,pl.pl_ordr_num
786 ,l_oipl_ordr_num
787 ,pl.pl_xchg_rate
788 ,l_group_pl_row.uses_bdgt_flag
789 ,l_group_pl_row.prsrv_bdgt_cd
790 ,oipl.business_group_id
791 ,oipl.ws_abr_id
792 ,oipl.ws_nnmntry_uom
793 ,oipl.ws_rndg_cd
794 ,oipl.ws_sub_acty_typ_cd
795 ,oipl.dist_bdgt_abr_id
796 ,oipl.dist_bdgt_nnmntry_uom
797 ,oipl.dist_bdgt_rndg_cd
798 ,oipl.ws_bdgt_abr_id
799 ,oipl.ws_bdgt_nnmntry_uom
800 ,oipl.ws_bdgt_rndg_cd
801 ,oipl.rsrv_abr_id
802 ,oipl.rsrv_nnmntry_uom
803 ,oipl.rsrv_rndg_cd
804 ,oipl.elig_sal_abr_id
805 ,oipl.elig_sal_nnmntry_uom
806 ,oipl.elig_sal_rndg_cd
807 ,oipl.misc1_abr_id
808 ,oipl.misc1_nnmntry_uom
809 ,oipl.misc1_rndg_cd
810 ,oipl.misc2_abr_id
811 ,oipl.misc2_nnmntry_uom
812 ,oipl.misc2_rndg_cd
813 ,oipl.misc3_abr_id
814 ,oipl.misc3_nnmntry_uom
815 ,oipl.misc3_rndg_cd
816 ,oipl.stat_sal_abr_id
817 ,oipl.stat_sal_nnmntry_uom
818 ,oipl.stat_sal_rndg_cd
819 ,oipl.rec_abr_id
820 ,oipl.rec_nnmntry_uom
821 ,oipl.rec_rndg_cd
822 ,oipl.tot_comp_abr_id
823 ,oipl.tot_comp_nnmntry_uom
824 ,oipl.tot_comp_rndg_cd
825 ,oipl.oth_comp_abr_id
826 ,oipl.oth_comp_nnmntry_uom
827 ,oipl.oth_comp_rndg_cd
828 ,pl.actual_flag
829 ,pl.acty_ref_perd_cd
830 ,pl.legislation_code
831 ,pl.pl_annulization_factor
832 ,'A'
833 ,pl.uom_precision
834 ,oipl.ws_element_type_id
835 ,oipl.ws_input_value_id
836 ,l_group_pl_row.data_freeze_date
837 ,1); -- new row. so ovn is 1
838 end loop; -- of l_oipl_rows
839
840 --
841 if g_debug then
842 hr_utility.set_location(l_proc, 60);
843 end if;
844 --
845 --insert the plan row
846 insert into ben_cwb_pl_dsgn
847 (pl_id
848 ,lf_evt_ocrd_dt
849 ,oipl_id
850 ,effective_date
851 ,name
852 ,group_pl_id
853 ,group_oipl_id
854 ,pl_uom
855 ,pl_ordr_num
856 ,pl_xchg_rate
857 ,opt_count
858 ,uses_bdgt_flag
859 ,prsrv_bdgt_cd
860 ,upd_start_dt
861 ,upd_end_dt
862 ,approval_mode
863 ,enrt_perd_start_dt
864 ,enrt_perd_end_dt
865 ,yr_perd_start_dt
866 ,yr_perd_end_dt
867 ,wthn_yr_start_dt
868 ,wthn_yr_end_dt
869 ,enrt_perd_id
870 ,yr_perd_id
871 ,business_group_id
872 ,perf_revw_strt_dt
873 ,asg_updt_eff_date
874 ,emp_interview_typ_cd
875 ,salary_change_reason
876 ,ws_abr_id
877 ,ws_nnmntry_uom
878 ,ws_rndg_cd
879 ,ws_sub_acty_typ_cd
880 ,dist_bdgt_abr_id
881 ,dist_bdgt_nnmntry_uom
882 ,dist_bdgt_rndg_cd
883 ,ws_bdgt_abr_id
884 ,ws_bdgt_nnmntry_uom
885 ,ws_bdgt_rndg_cd
886 ,rsrv_abr_id
887 ,rsrv_nnmntry_uom
888 ,rsrv_rndg_cd
889 ,elig_sal_abr_id
890 ,elig_sal_nnmntry_uom
891 ,elig_sal_rndg_cd
892 ,misc1_abr_id
893 ,misc1_nnmntry_uom
894 ,misc1_rndg_cd
895 ,misc2_abr_id
896 ,misc2_nnmntry_uom
897 ,misc2_rndg_cd
898 ,misc3_abr_id
899 ,misc3_nnmntry_uom
900 ,misc3_rndg_cd
901 ,stat_sal_abr_id
902 ,stat_sal_nnmntry_uom
903 ,stat_sal_rndg_cd
904 ,rec_abr_id
905 ,rec_nnmntry_uom
906 ,rec_rndg_cd
907 ,tot_comp_abr_id
908 ,tot_comp_nnmntry_uom
909 ,tot_comp_rndg_cd
910 ,oth_comp_abr_id
911 ,oth_comp_nnmntry_uom
912 ,oth_comp_rndg_cd
913 ,actual_flag
914 ,acty_ref_perd_cd
915 ,legislation_code
916 ,pl_annulization_factor
917 ,pl_stat_cd
918 ,uom_precision
919 ,ws_element_type_id
920 ,ws_input_value_id
921 ,data_freeze_date
922 ,object_version_number)
923 values
924 (pl.pl_id
925 ,p_lf_evt_ocrd_dt
926 ,-1 -- for plans oipl_id is -1
927 ,l_group_pl_row.effective_date
928 ,pl.name
929 ,p_group_pl_id
930 ,-1 -- for plans group oipl id is -1
931 ,pl.pl_uom
932 ,pl.pl_ordr_num
933 ,pl.pl_xchg_rate
934 ,l_opt_count
935 ,l_group_pl_row.uses_bdgt_flag
936 ,l_group_pl_row.prsrv_bdgt_cd
937 ,l_group_pl_row.upd_start_dt
938 ,l_group_pl_row.upd_end_dt
939 ,l_group_pl_row.approval_mode
940 ,l_group_pl_row.enrt_perd_start_dt
941 ,l_group_pl_row.enrt_perd_end_dt
942 ,l_group_pl_row.yr_perd_start_dt
943 ,l_group_pl_row.yr_perd_end_dt
944 ,l_group_pl_row.wthn_yr_start_dt
945 ,l_group_pl_row.wthn_yr_end_dt
946 ,pl.enrt_perd_id
947 ,pl.yr_perd_id
948 ,pl.business_group_id
949 ,l_group_pl_row.perf_revw_strt_dt
950 ,l_group_pl_row.asg_updt_eff_date
951 ,l_group_pl_row.emp_interview_typ_cd
952 ,l_group_pl_row.salary_change_reason
953 ,pl.ws_abr_id
954 ,pl.ws_nnmntry_uom
955 ,pl.ws_rndg_cd
956 ,pl.ws_sub_acty_typ_cd
957 ,pl.dist_bdgt_abr_id
958 ,pl.dist_bdgt_nnmntry_uom
959 ,pl.dist_bdgt_rndg_cd
960 ,pl.ws_bdgt_abr_id
961 ,pl.ws_bdgt_nnmntry_uom
962 ,pl.ws_bdgt_rndg_cd
963 ,pl.rsrv_abr_id
964 ,pl.rsrv_nnmntry_uom
965 ,pl.rsrv_rndg_cd
966 ,pl.elig_sal_abr_id
967 ,pl.elig_sal_nnmntry_uom
968 ,pl.elig_sal_rndg_cd
969 ,pl.misc1_abr_id
970 ,pl.misc1_nnmntry_uom
971 ,pl.misc1_rndg_cd
972 ,pl.misc2_abr_id
973 ,pl.misc2_nnmntry_uom
974 ,pl.misc2_rndg_cd
975 ,pl.misc3_abr_id
976 ,pl.misc3_nnmntry_uom
977 ,pl.misc3_rndg_cd
978 ,pl.stat_sal_abr_id
979 ,pl.stat_sal_nnmntry_uom
980 ,pl.stat_sal_rndg_cd
981 ,pl.rec_abr_id
982 ,pl.rec_nnmntry_uom
983 ,pl.rec_rndg_cd
984 ,pl.tot_comp_abr_id
985 ,pl.tot_comp_nnmntry_uom
986 ,pl.tot_comp_rndg_cd
987 ,pl.oth_comp_abr_id
988 ,pl.oth_comp_nnmntry_uom
989 ,pl.oth_comp_rndg_cd
990 ,pl.actual_flag
991 ,pl.acty_ref_perd_cd
992 ,pl.legislation_code
993 ,pl.pl_annulization_factor
994 ,pl.pl_stat_cd
995 ,pl.uom_precision
996 ,pl.ws_element_type_id
997 ,pl.ws_input_value_id
998 ,l_group_pl_row.data_freeze_date
999 ,1); -- new row. so ovn is 1
1000 end loop; -- l_pl_rows
1001 --
1002
1003 -- ER:8369634
1004 for l_grp_plan_extra_info in csr_grp_plan_extra_info loop
1005 update ben_cwb_pl_dsgn set
1006 post_zero_salary_increase = l_grp_plan_extra_info.post_zero_salary_increase,
1007 show_appraisals_n_days = l_grp_plan_extra_info.show_appraisals_n_days
1008 where pl_id = p_group_pl_id
1009 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1010 and group_oipl_id = -1;
1011 end loop;
1012
1013 if g_debug then
1014 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1015 end if;
1016 --
1017 end; -- end of refresh_pl_dsgn
1018
1019 --
1020 -- --------------------------------------------------------------------------
1021 -- |--------------------------< delete_pl_dsgn >----------------------------|
1022 -- --------------------------------------------------------------------------
1023 -- Description
1024 -- This procedure deletes the ben_cwb_pl_dsgn table when no cwb data exists.
1025 -- Input parameters
1026 -- p_group_pl_id : Group Plan Id
1027 -- p_lf_evt_ocrd_dt : Life Event Occured Date
1028 --
1029 procedure delete_pl_dsgn(p_group_pl_id in number
1030 ,p_lf_evt_ocrd_dt in date) is
1031
1032 cursor c_data_exists is
1033 select 'Y'
1034 from ben_cwb_person_info i
1035 where i.group_pl_id = p_group_pl_id
1036 and i.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
1037
1038 l_data_exists varchar2(1) := null;
1039
1040 begin
1041
1042 open c_data_exists;
1043 fetch c_data_exists into l_data_exists;
1044 close c_data_exists;
1045
1046 if l_data_exists is null then
1047 delete ben_cwb_pl_dsgn pl
1048 where pl.group_pl_id = p_group_pl_id
1049 and pl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
1050 end if;
1051
1052 end delete_pl_dsgn;
1053
1054 end ben_cwb_pl_dsgn_pkg;
1055