[Home] [Help]
PACKAGE BODY: APPS.BEN_ICD_PLAN_DESIGN_SETUP
Source
1 PACKAGE BODY BEN_ICD_PLAN_DESIGN_SETUP as
2 /* $Header: benicdsetup.pkb 120.2 2008/06/12 10:07:09 vkodedal noship $ */
3
4 procedure create_setup(p_element_type_id in number
5 ,p_business_group_id in number
6 ,p_effective_date in date
7 ,p_pl_typ_id in number
8 ,p_pl_id in number
9 ,p_pl_name in varchar2 default null
10 ,p_pl_typ_name in varchar2 default null
11 ,p_elig_prfl_id in number default null
12 ,p_opt_name in varchar2 default null
13 ,p_option_level in varchar2 default 'N') is
14 l_pl_typ_id number;
15 l_pl_id number;
16 l_opt_id number;
17 l_oipl_id number;
18 l_pl_typ_opt_typ_id number;
19 l_prtn_elig_id number;
20 l_prtn_elig_prfl_id number;
21 l_acty_base_rt_id number;
22 l_max_ordr_num number;
23 l_use_pl_id number;
24
25 l_ovn number;
26 l_esd date;
27 l_eed date;
28
29 cursor c_element is
30 select typ.element_type_id
31 ,typ.effective_start_date
32 ,typ.effective_end_date
33 ,nvl(typtl.description,typtl.element_name) use_name
34 ,typ.input_currency_code
35 ,typ.processing_type
36 from pay_element_types_f typ
37 ,pay_element_types_f_tl typtl
38 where typ.element_type_id = p_element_type_id
39 and p_effective_date between
40 typ.effective_start_date and typ.effective_end_date
41 and typ.element_type_id = typtl.element_type_id
42 and typtl.language (+) = userenv('lang');
43 l_element c_element%rowtype;
44
45 cursor c_opt(v_name varchar2) is
46 select opt.opt_id
47 from ben_opt_f opt
48 where opt.business_group_id = p_business_group_id
49 and p_effective_date between
50 opt.effective_start_date and opt.effective_end_date
51 and opt.name = v_name;
52
53 cursor c_pon(v_opt_id number, v_pl_typ_id number) is
54 select pon.pl_typ_opt_typ_id
55 from ben_pl_typ_opt_typ_f pon
56 where pon.opt_id = v_opt_id
57 and pon.pl_typ_id = v_pl_typ_id
58 and p_effective_date between
59 pon.effective_start_date and pon.effective_end_date;
60
61 cursor c_max_oipl is
62 select max(oipl.ordr_num)
63 from ben_oipl_f oipl
64 where oipl.pl_id = l_pl_id
65 and p_effective_date between
66 oipl.effective_start_date and oipl.effective_end_date;
67
68 begin
69 open c_element;
70 fetch c_element into l_element;
71 close c_element;
72
73 if p_pl_typ_id is null then
74 ben_plan_type_api.create_plan_type
75 (p_validate => false,
76 p_pl_typ_id => l_pl_typ_id,
77 p_effective_start_date => l_esd,
78 p_effective_end_date => l_eed,
79 p_name => nvl(p_pl_typ_name,
80 l_element.use_name),
81 p_pl_typ_stat_cd => 'A',
82 p_opt_typ_cd => 'ICM', -- New ICD
83 p_no_mx_enrl_num_dfnd_flag => 'N',
84 p_no_mn_enrl_num_dfnd_flag => 'N',
85 p_business_group_id => p_business_group_id,
86 p_object_version_number => l_ovn,
87 p_effective_date => p_effective_date);
88 else
89 l_pl_typ_id := p_pl_typ_id;
90 end if;
91
92 if p_pl_id is null then
93 ben_plan_api.create_plan
94 (p_validate => false,
95 p_pl_id => l_pl_id,
96 p_effective_start_date => l_esd,
97 p_effective_end_date => l_eed,
98 p_name => nvl(p_pl_name,
99 l_element.use_name),
100 p_ordr_num => 10,
101 p_pl_cd => 'MYNTBPGM', -- May Not Be in a program
102 p_enrt_mthd_cd => 'E', -- Explicit
103 p_enrt_cvg_strt_dt_cd => 'OED', -- Effective Date
104 p_enrt_cvg_end_dt_cd => 'ODBEFFD', -- 1 Day Before Effective Date
105 p_nip_pl_uom => l_element.input_currency_code,
106 p_nip_acty_ref_perd_cd => null, -- MO Monthly
107 p_nip_enrt_info_rt_freq_cd => 'PP', -- Per Pay Period
108 p_prtn_elig_ovrid_alwd_flag => 'Y',
109 p_pl_stat_cd => 'A', -- Active
110 p_rt_end_dt_cd => 'WAENT', -- 1 Prior or Enterable
111 p_rt_strt_dt_cd => 'ENTRBL', --Enterable
112 p_pl_typ_id => l_pl_typ_id,
113 p_business_group_id => p_business_group_id,
114 p_alws_unrstrctd_enrt_flag => 'Y',
115 p_object_version_number => l_ovn,
116 p_effective_date => p_effective_date);
117 else
118 l_pl_id := p_pl_id;
119 end if;
120
121 if p_option_level = 'Y' then
122
123 l_use_pl_id := null;
124
125 open c_opt(nvl(p_opt_name,l_element.use_name));
126 fetch c_opt into l_opt_id;
127 close c_opt;
128
129 if l_opt_id is null then
130 ben_option_definition_api.create_option_definition
131 (p_validate => false,
132 p_opt_id => l_opt_id,
133 p_effective_start_date => l_esd,
134 p_effective_end_date => l_eed,
135 p_name => nvl(p_opt_name,
136 l_element.use_name),
137 p_business_group_id => p_business_group_id,
138 p_object_version_number => l_ovn,
139 p_effective_date => p_effective_date);
140 end if;
141
142 open c_pon(l_opt_id, l_pl_typ_id);
143 fetch c_pon into l_pl_typ_opt_typ_id;
144 close c_pon;
145
146 if l_pl_typ_opt_typ_id is null then
147 ben_plan_type_option_type_api.create_plan_type_option_type
148 (p_validate => false,
149 p_pl_typ_opt_typ_id => l_pl_typ_opt_typ_id,
150 p_effective_start_date => l_esd,
151 p_effective_end_date => l_eed,
152 p_pl_typ_opt_typ_cd => 'ICM',
153 p_opt_id => l_opt_id,
154 p_pl_typ_id => l_pl_typ_id,
155 p_business_group_id => p_business_group_id,
156 p_object_version_number => l_ovn,
157 p_effective_date => p_effective_date);
158 end if;
159
160 open c_max_oipl;
161 fetch c_max_oipl into l_max_ordr_num;
162 close c_max_oipl;
163
164 l_max_ordr_num := nvl(l_max_ordr_num, 0) + 10;
165
166 ben_option_in_plan_api.create_option_in_plan
167 (p_validate => false,
168 p_oipl_id => l_oipl_id,
169 p_effective_start_date => l_esd,
170 p_effective_end_date => l_eed,
171 p_opt_id => l_opt_id,
172 p_business_group_id => p_business_group_id,
173 p_pl_id => l_pl_id,
174 p_ordr_num => l_max_ordr_num,
175 p_oipl_stat_cd => 'A',
176 p_auto_enrt_flag => 'N',
177 p_prtn_elig_ovrid_alwd_flag => 'Y',
178 p_object_version_number => l_ovn,
179 p_effective_date => p_effective_date);
180 else
181 l_use_pl_id := l_pl_id;
182 end if;
183
184 if p_elig_prfl_id is not null then
185 ben_participation_elig_api.create_participation_elig
186 (p_validate => false,
187 p_prtn_elig_id => l_prtn_elig_id,
188 p_effective_start_date => l_esd,
189 p_effective_end_date => l_eed,
190 p_business_group_id => p_business_group_id,
191 p_pl_id => l_use_pl_id,
192 p_oipl_id => l_oipl_id,
193 p_object_version_number => l_ovn,
194 p_effective_date => p_effective_date);
195
196 ben_prtn_elig_prfl_api.create_prtn_elig_prfl
197 (p_validate => false,
198 p_prtn_elig_prfl_id => l_prtn_elig_prfl_id,
199 p_effective_start_date => l_esd,
200 p_effective_end_date => l_eed,
201 p_business_group_id => p_business_group_id,
202 p_mndtry_flag => 'Y',
203 p_prtn_elig_id => l_prtn_elig_id,
204 p_eligy_prfl_id => p_elig_prfl_id,
205 p_object_version_number => l_ovn,
206 p_effective_date => p_effective_date);
207 end if;
208
209 ben_acty_base_rate_api.create_acty_base_rate
210 (p_validate => false,
211 p_acty_base_rt_id => l_acty_base_rt_id,
212 p_effective_start_date => l_esd,
213 p_effective_end_date => l_eed,
214 p_acty_typ_cd => 'ERPYD',
215 p_name => nvl(p_opt_name,
216 nvl(p_pl_name,
217 l_element.use_name)),
218 p_tx_typ_cd => 'NONTAXABLE',
219 p_rt_mlt_cd => 'NSVU', -- No Standard Rate Used
220 p_asn_on_enrt_flag => 'Y',
221 p_acty_base_rt_stat_cd => 'A',
222 p_procg_src_cd => 'PYRL', -- Payroll
223 p_rt_usg_cd => 'STD', -- Standard Contribution/Distribution
224 p_ele_rqd_flag => 'Y',
225 p_element_type_id => p_element_type_id,
226 p_pl_id => l_use_pl_id,
227 p_oipl_id => l_oipl_id,
228 p_ele_entry_val_cd => 'DFND', -- Defined Amount
229 p_business_group_id => p_business_group_id,
230 p_context_pl_id => l_pl_id,
231 p_context_opt_id => l_opt_id,
232 p_object_version_number => l_ovn,
233 p_effective_date => p_effective_date);
234
235 ben_icd_flex_field_setup.create_icd_config
236 (p_element_type_id => p_element_type_id,
237 p_effective_date => p_effective_date);
238
239 exception
240 when others then
241 rollback;
242 raise;
243
244 end create_setup;
245
246
247 procedure refresh_setup(p_element_type_id in number
248 ,p_business_group_id in number
249 ,p_effective_date in date) is
250 begin
251 ben_icd_flex_field_setup.refresh_icd_config
252 (p_element_type_id => p_element_type_id,
253 p_effective_date => p_effective_date);
254 exception
255 when others then
256 rollback;
257 raise;
258 end refresh_setup;
259
260 procedure delete_elig(p_prtn_elig_id in number,
261 p_effective_date in date,
262 p_object_version_number in out nocopy number) is
263 cursor c_prfl is
264 select prfl.prtn_elig_prfl_id
265 ,prfl.effective_start_date
266 ,prfl.effective_end_date
267 ,prfl.object_version_number
268 from ben_prtn_elig_prfl_f prfl
269 where prfl.prtn_elig_id = p_prtn_elig_id
270 and p_effective_date between
271 prfl.effective_start_date and prfl.effective_end_date;
272 l_esd date;
273 l_eed date;
274 begin
275 for l_prfl in c_prfl loop
276 ben_prtn_elig_prfl_api.delete_prtn_elig_prfl
277 (p_validate => false,
278 p_prtn_elig_prfl_id => l_prfl.prtn_elig_prfl_id,
279 p_effective_start_date => l_esd,
280 p_effective_end_date => l_eed,
281 p_object_version_number => l_prfl.object_version_number,
282 p_effective_date => p_effective_date,
283 p_datetrack_mode => hr_api.g_zap);
284 end loop;
285
286 ben_participation_elig_api.delete_participation_elig
287 (p_validate => false,
288 p_prtn_elig_id => p_prtn_elig_id,
289 p_effective_start_date => l_esd,
290 p_effective_end_date => l_eed,
291 p_object_version_number => p_object_version_number,
292 p_effective_date => p_effective_date,
293 p_datetrack_mode => hr_api.g_zap);
294
295 end delete_elig;
296
297 procedure delete_setup(p_element_type_id in number
298 ,p_business_group_id in number
299 ,p_effective_date in date) is
300
301 l_esd date;
302 l_eed date;
303
304 cursor c_abr is
305 select abr.acty_base_rt_id
306 ,abr.object_version_number
307 ,abr.context_pl_id pl_id
308 ,abr.oipl_id
309 ,pln.pl_typ_id
310 from ben_acty_base_rt_f abr
311 ,ben_pl_f pln
312 ,ben_pl_typ_f typ
313 where abr.element_type_id = p_element_type_id
314 and abr.business_group_id = p_business_group_id
315 and p_effective_date between
316 abr.effective_start_date and abr.effective_end_date
317 and abr.context_pl_id = pln.pl_id
318 and p_effective_date between
319 pln.effective_start_date and pln.effective_end_date
320 and pln.pl_typ_id = typ.pl_typ_id
321 and p_effective_date between
322 typ.effective_start_date and typ.effective_end_date
323 and typ.opt_typ_cd = 'ICM';
324
325 cursor c_oipl_prtn(v_oipl_id number) is
326 select prtn.prtn_elig_id
327 ,prtn.object_version_number
328 from ben_prtn_elig_f prtn
329 where prtn.oipl_id = v_oipl_id
330 and p_effective_date between
331 prtn.effective_start_date and prtn.effective_end_date;
332
333 cursor c_pl_prtn(v_pl_id number) is
334 select prtn.prtn_elig_id
335 ,prtn.object_version_number
336 from ben_prtn_elig_f prtn
337 where prtn.pl_id = v_pl_id
338 and p_effective_date between
339 prtn.effective_start_date and prtn.effective_end_date;
340
341 cursor c_oipl(v_oipl_id number) is
342 select oipl.object_version_number
343 ,oipl.opt_id
344 from ben_oipl_f oipl
345 where oipl.oipl_id = v_oipl_id
346 and p_effective_date between
347 oipl.effective_start_date and oipl.effective_end_date;
348
349 cursor c_pon(v_opt_id number,
350 v_pl_typ_id number) is
351 select pon.pl_typ_opt_typ_id
352 ,pon.object_version_number
353 from ben_pl_typ_opt_typ_f pon
354 where pon.opt_id = v_opt_id
355 and pon.pl_typ_id = v_pl_typ_id
356 and p_effective_date between
357 pon.effective_start_date and pon.effective_end_date
358 and not exists
359 (select 'Y'
360 from ben_oipl_f oipl
361 ,ben_pl_f pln
362 where oipl.opt_id = v_opt_id
363 and oipl.pl_id = pln.pl_id
364 and pln.pl_typ_id = v_pl_typ_id);
365
366 cursor c_opt(v_opt_id number) is
367 select opt.object_version_number
368 from ben_opt_f opt
369 where opt.opt_id = v_opt_id
370 and p_effective_date between
371 opt.effective_start_date and opt.effective_end_date
372 and not exists
373 (select 'Y'
374 from ben_pl_typ_opt_typ_f pon
375 where pon.opt_id = v_opt_id);
376
377 cursor c_pln(v_pl_id number) is
378 select pln.object_version_number
379 from ben_pl_f pln
380 where pln.pl_id = v_pl_id
381 and p_effective_date between
382 pln.effective_start_date and pln.effective_end_date
383 and not exists
384 (select 'Y'
385 from ben_oipl_f oipl
386 where oipl.pl_id = v_pl_id);
387
388 cursor c_typ(v_pl_typ_id number) is
389 select typ.object_version_number
390 from ben_pl_typ_f typ
391 where typ.pl_typ_id = v_pl_typ_id
392 and p_effective_date between
393 typ.effective_start_date and typ.effective_end_date
394 and not exists
395 (select 'Y'
396 from ben_pl_f pln
397 where pln.pl_typ_id = v_pl_typ_id);
398
399 cursor c_abr_exst is
400 select abr.acty_base_rt_id
401 from ben_acty_base_rt_f abr
402 ,ben_pl_f pln
403 ,ben_pl_typ_f typ
404 where abr.element_type_id = p_element_type_id
405 and abr.context_pl_id = pln.pl_id
406 and pln.pl_typ_id = typ.pl_typ_id
407 and typ.opt_typ_cd = 'ICM';
408
409 l_abr_id number;
410
411 begin
412
413 for l_abr in c_abr loop
414 ben_acty_base_rate_api.delete_acty_base_rate
415 (p_validate => false,
416 p_acty_base_rt_id => l_abr.acty_base_rt_id,
417 p_effective_start_date => l_esd,
418 p_effective_end_date => l_eed,
419 p_object_version_number => l_abr.object_version_number,
420 p_effective_date => p_effective_date,
421 p_datetrack_mode => hr_api.g_zap);
422
423 if l_abr.oipl_id is not null then
424 for l_prtn_elig in c_oipl_prtn(l_abr.oipl_id) loop
425 delete_elig(p_prtn_elig_id => l_prtn_elig.prtn_elig_id,
426 p_effective_date => p_effective_date,
427 p_object_version_number => l_prtn_elig.object_version_number);
428 end loop; -- c_oipl_prtn
429
430 for l_oipl in c_oipl(l_abr.oipl_id) loop
431 ben_option_in_plan_api.delete_option_in_plan
432 (p_validate => false,
433 p_oipl_id => l_abr.oipl_id,
434 p_effective_start_date => l_esd,
435 p_effective_end_date => l_eed,
436 p_object_version_number => l_oipl.object_version_number,
437 p_effective_date => p_effective_date,
438 p_datetrack_mode => hr_api.g_zap);
439
440 for l_pon in c_pon(l_oipl.opt_id, l_abr.pl_typ_id) loop
441 ben_plan_type_option_type_api.delete_plan_type_option_type
442 (p_validate => false,
443 p_pl_typ_opt_typ_id => l_pon.pl_typ_opt_typ_id,
444 p_effective_start_date => l_esd,
445 p_effective_end_date => l_eed,
446 p_object_version_number => l_pon.object_version_number,
447 p_effective_date => p_effective_date,
448 p_datetrack_mode => hr_api.g_zap);
449 end loop; -- c_pon
450
451 for l_opt in c_opt(l_oipl.opt_id) loop
452 ben_option_definition_api.delete_option_definition
453 (p_validate => false,
454 p_opt_id => l_oipl.opt_id,
455 p_effective_start_date => l_esd,
456 p_effective_end_date => l_eed,
457 p_object_version_number => l_opt.object_version_number,
458 p_effective_date => p_effective_date,
459 p_datetrack_mode => hr_api.g_zap);
460 end loop; -- c_opt
461
462 end loop; -- c_oipl
463 else
464 for l_prtn_elig in c_pl_prtn(l_abr.pl_id) loop
465 delete_elig(p_prtn_elig_id => l_prtn_elig.prtn_elig_id,
466 p_effective_date => p_effective_date,
467 p_object_version_number => l_prtn_elig.object_version_number);
468 end loop; -- c_pl_rtn
469 end if; -- oipl_id is not null
470
471 for l_pln in c_pln(l_abr.pl_id) loop
472 ben_plan_api.delete_plan
473 (p_validate => false,
474 p_pl_id => l_abr.pl_id,
475 p_effective_start_date => l_esd,
476 p_effective_end_date => l_eed,
477 p_object_version_number => l_pln.object_version_number,
478 p_effective_date => p_effective_date,
479 p_datetrack_mode => hr_api.g_zap);
480 end loop; -- c_pln
481
482 for l_typ in c_typ(l_abr.pl_typ_id) loop
483 ben_plan_type_api.delete_plan_type
484 (p_validate => false,
485 p_pl_typ_id => l_abr.pl_typ_id,
486 p_effective_start_date => l_esd,
487 p_effective_end_date => l_eed,
488 p_object_version_number => l_typ.object_version_number,
489 p_effective_date => p_effective_date,
490 p_datetrack_mode => hr_api.g_zap);
491 end loop; -- c_pl_typ
492
493 end loop; -- c_abr;
494
495 open c_abr_exst;
496 fetch c_abr_exst into l_abr_id;
497 close c_abr_exst;
498
499 if l_abr_id is null then
500 -- No other comp object using the same element, so delete
501 -- the setup information
502 ben_icd_flex_field_setup.delete_icd_config
503 (p_element_type_id => p_element_type_id,
504 p_effective_date => p_effective_date);
505 end if;
506
507 exception
508 when others then
509 rollback;
510 raise;
511 end delete_setup;
512
513 end ben_icd_plan_design_setup;