[Home] [Help]
PACKAGE BODY: APPS.BEN_DERIVABLE_RATE
Source
1 package body ben_derivable_rate as
2 /* $Header: bendrvrt.pkb 120.0 2005/05/28 04:13:54 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Derivable Factor Rate
13 Purpose
14 This package is used to handle setting of Derivable Factor Applies Rate Flag based on
15 data changes that have occurred on child component tables.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 07-Mar-2000 KMahendr 115.0 Created.
20 23-Mar-2000 gperry 115.1 Added support for coverages and
21 premiums.
22 11-Apr-2000 mmogel 115.2 Added tokens to messages to make
23 them more meaningful to the user
24 04-Jan-2001 RCHASE 115.3 Bug 1568561. Fix NDS Create calls in
25 rate_prfl_handler, missing parens
26 around or exists conditions.
27 Also, fixed c1 cursor in
28 derivable_rate_handler to include
29 premium and coverage calcs when
30 a derivable factor is attached to
31 and existing variable rate profile.
32 10-Jun-2002 kmahendr 115.4 Bug#2410546 - syntax error in dynamic sql
33 corrected.
34 11-Jun-2002 kmahendr 115.5 Commit statement added.
35 05-May-2003 kmahendr 115.6 Bug#2939392 - dynamic sql is modified to take
36 binary variables.
37 04-Feb-2004 vvprabhu 115.7 Bug 3431740 Parameter p_oracle_schema added
38 to cursor c1 in rate_prfl_handler,
39 the value is got by the
40 call to fnd_installation.get_app_info
41 15-Feb-2004 vvprabhu 115.8 Initialized l_application_short_name to BEN
42 04-Jun-2004 abparekh 115.9 Bug : 3667163 - Corrected number of arguments
43 in call to execute immediate in RATE_PRFL_HANDLER
44
45
46 */
47 --------------------------------------------------------------------------------
48 g_package varchar2(80) := 'ben_derivable_rate.';
49 --
50 procedure rate_prfl_handler
51 (p_event in varchar2,
52 p_table_name in varchar2,
53 p_col_name in varchar2,
54 p_col_id in number)is
55 --
56 l_proc varchar2(80) := g_package||'rate prfl handler';
57 l_strg varchar2(32000) ;
58 l_dummy varchar2(1);
59 l_status varchar2(1);
60 l_industry varchar2(1);
61 l_application_short_name varchar2(30) := 'BEN';
62 l_oracle_schema varchar2(30);
63 l_return boolean;
64
65 --
66 cursor c1 (p_tab_name varchar2, p_col_name varchar2,p_oracle_schema varchar2) is
67 select null
68 from all_tab_columns
69 where table_name = upper(p_tab_name)
70 and column_name = upper(p_col_name)
71 and owner = upper(p_oracle_schema);
72
73 --
74 begin
75 --
76 hr_utility.set_location('Entering '||l_proc,10);
77 --
78 --
79 -- Parameter validation
80 --
81 hr_api.mandatory_arg_error(p_api_name => l_proc,
82 p_argument => 'event',
83 p_argument_value => p_event);
84 --
85 hr_api.mandatory_arg_error(p_api_name => l_proc,
86 p_argument => 'table_name',
87 p_argument_value => p_table_name);
88 --
89 hr_api.mandatory_arg_error(p_api_name => l_proc,
90 p_argument => 'column name',
91 p_argument_value => p_col_name);
92 --
93 hr_api.mandatory_arg_error(p_api_name => l_proc,
94 p_argument => 'column_id',
95 p_argument_value => p_col_id);
96 --
97 -- Bug 3431740 Parameter p_oracle_schema added to cursor c1, the value is got by the
98 -- following call
99 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
100 status => l_status,
101 industry => l_industry,
102 oracle_schema => l_oracle_schema);
103 open c1(p_table_name, p_col_name, l_oracle_schema );
104 fetch c1 into l_dummy;
105 if c1%notfound then
106 close c1;
107 fnd_message.set_name('BEN','BEN_93388_NO_TAB_COL');
108 fnd_message.raise_error;
109 end if;
110 close c1;
111
112 --
113 -- Check operation is valid
114 --
115 if p_event not in ('CREATE','DELETE') then
116 --
117 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
118 fnd_message.set_token('PROC',l_proc);
119 fnd_message.set_token('EVENT',p_event);
120 fnd_message.raise_error;
121 --
122 end if;
123 --
124 if p_event = 'CREATE' then
125 --
126 -- rchase bug 1568561 - paren pair addition around ors
127 --
128 l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
129 where '||p_col_name|| '= :2 and
130 drvbl_fctr_apls_rts_flag = :3
131 and (
132 exists (select null
133 from ben_age_rt_f art,
134 ben_acty_base_rt_f abr,
135 ben_acty_vrbl_rt_f avr
136 where abr.'||p_col_name||'= :4
137 and abr.acty_base_rt_id = avr.acty_base_rt_id
138 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) or
139 exists (select null
140 from ben_cmbn_age_los_rt_f cmr,
141 ben_acty_base_rt_f abr,
142 ben_acty_vrbl_rt_f avr
143 where abr.'||p_col_name||'= :5
144 and abr.acty_base_rt_id = avr.acty_base_rt_id
145 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) or
146 exists (select null
147 from ben_comp_lvl_rt_f clr,
148 ben_acty_base_rt_f abr,
149 ben_acty_vrbl_rt_f avr
150 where abr.'||p_col_name||'= :6
151 and abr.acty_base_rt_id = avr.acty_base_rt_id
152 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) or
153 exists (select null
154 from ben_hrs_wkd_in_perd_rt_f hwr,
155 ben_acty_base_rt_f abr,
156 ben_acty_vrbl_rt_f avr
157 where abr.'||p_col_name||'= :7
158 and abr.acty_base_rt_id = avr.acty_base_rt_id
159 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) or
160 exists (select null
161 from ben_los_rt_f lsr,
162 ben_acty_base_rt_f abr,
163 ben_acty_vrbl_rt_f avr
164 where abr.'||p_col_name||'= :8
165 and abr.acty_base_rt_id = avr.acty_base_rt_id
166 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) or
167 exists (select null
168 from ben_pct_fl_tm_rt_f pfr,
169 ben_acty_base_rt_f abr,
170 ben_acty_vrbl_rt_f avr
171 where abr.'||p_col_name||'= :9
172 and abr.acty_base_rt_id = avr.acty_base_rt_id
173 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id))';
174 --
175 execute immediate l_strg using 'Y',p_col_id,'N',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
176 --
177 if upper(p_col_name) in ('OIPL_ID','PL_ID') then
178 --
179 -- Could be a premium rate
180 --
181 -- rchase bug 1568561 - paren pair addition around ors
182 --
183 l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
184 where '||p_col_name|| '= :2 and
185 drvbl_fctr_apls_rts_flag = :3
186 and (
187 exists (select null
188 from ben_age_rt_f art,
189 ben_actl_prem_f apr,
190 ben_actl_prem_vrbl_rt_f apv
191 where apr.'||p_col_name||' = :4
192 and apr.actl_prem_id = apv.actl_prem_id
193 and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) or
194 exists (select null
195 from ben_cmbn_age_los_rt_f cmr,
196 ben_actl_prem_f apr,
197 ben_actl_prem_vrbl_rt_f apv
198 where apr.'||p_col_name||'= :5
199 and apr.actl_prem_id = apv.actl_prem_id
200 and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) or
201 exists (select null
202 from ben_comp_lvl_rt_f clr,
203 ben_actl_prem_f apr,
204 ben_actl_prem_vrbl_rt_f apv
205 where apr.'||p_col_name||'= :6
206 and apr.actl_prem_id = apv.actl_prem_id
207 and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) or
208 exists (select null
209 from ben_hrs_wkd_in_perd_rt_f hwr,
210 ben_actl_prem_f apr,
211 ben_actl_prem_vrbl_rt_f apv
212 where apr.'||p_col_name||'= :7
213 and apr.actl_prem_id = apv.actl_prem_id
214 and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) or
215 exists (select null
216 from ben_los_rt_f lsr,
217 ben_actl_prem_f apr,
218 ben_actl_prem_vrbl_rt_f apv
219 where apr.'||p_col_name||'= :8
220 and apr.actl_prem_id = apv.actl_prem_id
221 and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) or
222 exists (select null
223 from ben_pct_fl_tm_rt_f pfr,
224 ben_actl_prem_f apr,
225 ben_actl_prem_vrbl_rt_f apv
226 where apr.'||p_col_name||'= :9
227 and apr.actl_prem_id = apv.actl_prem_id
228 and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id))';
229 --
230 execute immediate l_strg using 'Y',p_col_id,'N',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
231 --
232 end if;
233 --
234 if upper(p_col_name) in ('OIPL_ID','PL_ID','PLIP_ID') then
235 --
236 -- Could be a coverage
237 --
238 l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
239 where '||p_col_name|| '= :2 and
240 drvbl_fctr_apls_rts_flag = :3
241 and (
242 exists (select null
243 from ben_age_rt_f art,
244 ben_cvg_amt_calc_mthd_f ccm,
245 ben_bnft_vrbl_rt_f bvr
246 where ccm.'||p_col_name||' = :4
247 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
248 and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) or
249 exists (select null
250 from ben_cmbn_age_los_rt_f cmr,
251 ben_cvg_amt_calc_mthd_f ccm,
252 ben_bnft_vrbl_rt_f bvr
253 where ccm.'||p_col_name||'= :5
254 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
255 and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) or
256 exists (select null
257 from ben_comp_lvl_rt_f clr,
258 ben_cvg_amt_calc_mthd_f ccm,
259 ben_bnft_vrbl_rt_f bvr
260 where ccm.'||p_col_name||'= :6
261 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
262 and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) or
263 exists (select null
264 from ben_hrs_wkd_in_perd_rt_f hwr,
265 ben_cvg_amt_calc_mthd_f ccm,
266 ben_bnft_vrbl_rt_f bvr
267 where ccm.'||p_col_name||'= :7
268 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
269 and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) or
270 exists (select null
271 from ben_los_rt_f lsr,
272 ben_cvg_amt_calc_mthd_f ccm,
273 ben_bnft_vrbl_rt_f bvr
274 where ccm.'||p_col_name||'= :8
275 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
276 and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) or
277 exists (select null
278 from ben_pct_fl_tm_rt_f pfr,
279 ben_cvg_amt_calc_mthd_f ccm,
280 ben_bnft_vrbl_rt_f bvr
281 where ccm.'||p_col_name||'= :9
282 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
283 and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id))';
284 --
285 execute immediate l_strg using 'Y',p_col_id,'N',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
286 --
287 end if;
288 --
289 elsif p_event = 'DELETE' then
290 --
291 l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
292 where '||p_col_name|| '= :2 and
293 drvbl_fctr_apls_rts_flag = :3 and
294 not exists (select null
295 from ben_age_rt_f art,
296 ben_acty_base_rt_f abr,
297 ben_acty_vrbl_rt_f avr
298 where abr.'||p_col_name||' = :4
299 and abr.acty_base_rt_id = avr.acty_base_rt_id
300 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) and
301 not exists (select null
302 from ben_cmbn_age_los_rt_f cmr,
303 ben_acty_base_rt_f abr,
304 ben_acty_vrbl_rt_f avr
305 where abr.'||p_col_name||'= :5
309 from ben_comp_lvl_rt_f clr,
306 and abr.acty_base_rt_id = avr.acty_base_rt_id
307 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) and
308 not exists (select null
310 ben_acty_base_rt_f abr,
311 ben_acty_vrbl_rt_f avr
312 where abr.'||p_col_name||'= :6
313 and abr.acty_base_rt_id = avr.acty_base_rt_id
314 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) and
315 not exists (select null
316 from ben_hrs_wkd_in_perd_rt_f hwr,
317 ben_acty_base_rt_f abr,
318 ben_acty_vrbl_rt_f avr
319 where abr.'||p_col_name||'= :7
320 and abr.acty_base_rt_id = avr.acty_base_rt_id
321 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) and
322 not exists (select null
323 from ben_los_rt_f lsr,
324 ben_acty_base_rt_f abr,
325 ben_acty_vrbl_rt_f avr
326 where abr.'||p_col_name||'= :8
327 and abr.acty_base_rt_id = avr.acty_base_rt_id
328 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) and
329 not exists (select null
330 from ben_pct_fl_tm_rt_f pfr,
331 ben_acty_base_rt_f abr,
332 ben_acty_vrbl_rt_f avr
333 where abr.'||p_col_name||'= :9
334 and abr.acty_base_rt_id = avr.acty_base_rt_id
335 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)';
336 --
337 --
338 if upper(p_col_name) in ('OIPL_ID','PL_ID') then
339 --
340 -- Could be a premium rate
341 --
342 l_strg := l_strg||' and
343 not exists (select null
344 from ben_age_rt_f art,
345 ben_actl_prem_f apr,
346 ben_actl_prem_vrbl_rt_f apv
347 where apr.'||p_col_name||' = :10
348 and apr.actl_prem_id = apv.actl_prem_id
349 and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) and
350 not exists (select null
351 from ben_cmbn_age_los_rt_f cmr,
352 ben_actl_prem_f apr,
353 ben_actl_prem_vrbl_rt_f apv
354 where apr.'||p_col_name||' = :11
355 and apr.actl_prem_id = apv.actl_prem_id
356 and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) and
357 not exists (select null
358 from ben_comp_lvl_rt_f clr,
359 ben_actl_prem_f apr,
360 ben_actl_prem_vrbl_rt_f apv
361 where apr.'||p_col_name||'= :12
362 and apr.actl_prem_id = apv.actl_prem_id
363 and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) and
364 not exists (select null
365 from ben_hrs_wkd_in_perd_rt_f hwr,
366 ben_actl_prem_f apr,
367 ben_actl_prem_vrbl_rt_f apv
368 where apr.'||p_col_name||'= :13
369 and apr.actl_prem_id = apv.actl_prem_id
370 and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) and
371 not exists (select null
372 from ben_los_rt_f lsr,
373 ben_actl_prem_f apr,
374 ben_actl_prem_vrbl_rt_f apv
375 where apr.'||p_col_name||'= :14
376 and apr.actl_prem_id = apv.actl_prem_id
377 and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) and
378 not exists (select null
379 from ben_pct_fl_tm_rt_f pfr,
380 ben_actl_prem_f apr,
381 ben_actl_prem_vrbl_rt_f apv
382 where apr.'||p_col_name||'= :15
383 and apr.actl_prem_id = apv.actl_prem_id
384 and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)';
385 --
386 end if;
387 --
388 if upper(p_col_name) in ('OIPL_ID','PL_ID','PLIP_ID') then
389 --
390 -- Could be a coverage
391 --
392 l_strg := l_strg||' and
393 not exists (select null
394 from ben_age_rt_f art,
395 ben_cvg_amt_calc_mthd_f ccm,
396 ben_bnft_vrbl_rt_f bvr
397 where ccm.'||p_col_name||' = :16
398 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
399 and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) and
400 not exists (select null
401 from ben_cmbn_age_los_rt_f cmr,
402 ben_cvg_amt_calc_mthd_f ccm,
403 ben_bnft_vrbl_rt_f bvr
407 not exists (select null
404 where ccm.'||p_col_name||'= :17
405 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
406 and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) and
408 from ben_comp_lvl_rt_f clr,
409 ben_cvg_amt_calc_mthd_f ccm,
410 ben_bnft_vrbl_rt_f bvr
411 where ccm.'||p_col_name||'= :18
412 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
413 and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) and
414 not exists (select null
415 from ben_hrs_wkd_in_perd_rt_f hwr,
416 ben_cvg_amt_calc_mthd_f ccm,
417 ben_bnft_vrbl_rt_f bvr
418 where ccm.'||p_col_name||'= :19
419 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
420 and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) and
421 not exists (select null
422 from ben_los_rt_f lsr,
423 ben_cvg_amt_calc_mthd_f ccm,
424 ben_bnft_vrbl_rt_f bvr
425 where ccm.'||p_col_name||'= :20
426 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
427 and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) and
428 not exists (select null
429 from ben_pct_fl_tm_rt_f pfr,
430 ben_cvg_amt_calc_mthd_f ccm,
431 ben_bnft_vrbl_rt_f bvr
432 where ccm.'||p_col_name||'= :21
433 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
434 and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)';
435 --
436 end if;
437 --
438 --Bug : 3667163
439 --Wrong number of arguments while calling execute immediate.
440 /*
441 if upper(p_col_name) = 'PLIP_ID' then -- no check for premium
442 execute immediate l_strg using 'N',p_col_id,'Y',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,
443 p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
444 else
445 hr_utility.set_location('L string'||l_strg,9);
446 execute immediate l_strg using 'N',p_col_id,'Y',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,
447 p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,
448 p_col_id,p_col_id,p_col_id;
449 end if;
450 */
451 -- Corrected number of arguments in call to execute immediate based on value of p_col_name
452 if upper(p_col_name) in ('OIPL_ID','PL_ID') then
453 execute immediate l_strg using 'N',p_col_id,'Y',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,
454 p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,
455 p_col_id,p_col_id,p_col_id;
456
457 elsif upper(p_col_name) in ('PLIP_ID') then
458 execute immediate l_strg using 'N',p_col_id,'Y',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,
459 p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
460 else
461 execute immediate l_strg using 'N',p_col_id,'Y',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
462 end if;
463 --Bug : 3667163
464 --
465 end if;
466 --
467 hr_utility.set_location('Leaving '||l_proc,10);
468 --
469 end rate_prfl_handler;
470 -----------------------------------------------------------------------
471 procedure derivable_rate_handler
472 (p_event in varchar2,
473 p_vrbl_rt_prfl_id in number)is
474 --
475 l_proc varchar2(80) := g_package||'derivable rate handler';
476 --
477 cursor c1 is
478 select pgm_id,
479 pl_id,
480 ptip_id,
481 plip_id,
482 oipl_id
483 from ben_acty_base_rt_f abr,
484 ben_acty_vrbl_rt_f avr
485 where avr.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
486 and avr.acty_base_rt_id = abr.acty_base_rt_id
487 -- RCHASE bug 1568561 - added union
488 union
489 select to_number(null) pgm_id,
490 pl_id,
491 to_number(null) ptip_id,
492 to_number(null) plip_id,
493 oipl_id
494 from ben_actl_prem_f apr,
495 ben_actl_prem_vrbl_rt_f apv
496 where apr.actl_prem_id = apv.actl_prem_id
497 and apv.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
498 union
499 select to_number(null) pgm_id,
500 pl_id,
501 to_number(null) ptip_id,
502 plip_id,
503 oipl_id
504 from ben_cvg_amt_calc_mthd_f ccm,
505 ben_bnft_vrbl_rt_f bvr
506 where ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
507 and bvr.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
508 ;
509 --
510 begin
511 --
512 hr_utility.set_location('Entering '||l_proc,10);
513 --
514 -- Parameter validation
515 --
516 hr_api.mandatory_arg_error(p_api_name => l_proc,
517 p_argument => 'event',
518 p_argument_value => p_event);
522 --
519 hr_api.mandatory_arg_error(p_api_name => l_proc,
520 p_argument => 'vrbl_rt prfl id',
521 p_argument_value => p_vrbl_rt_prfl_id);
523 if p_event not in ('CREATE','DELETE') then
524 --
525 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
526 fnd_message.set_token('PROC',l_proc);
527 fnd_message.set_token('EVENT',p_event);
528 fnd_message.raise_error;
529 --
530 end if;
531 --
532 if p_event = 'CREATE' then
533 --
534 For i in c1 loop
535 --
536 If i.pgm_id is not null then
537 --
538 update ben_pgm_f
539 set drvbl_fctr_apls_rts_flag = 'Y'
540 where pgm_id = i.pgm_id;
541 --
542 end if;
543 --
544 If i.pl_id is not null then
545 --
546 update ben_pl_f
547 set drvbl_fctr_apls_rts_flag = 'Y'
548 where pl_id = i.pl_id;
549 --
550 end if;
551 --
552 If i.plip_id is not null then
553 --
554 update ben_plip_f
555 set drvbl_fctr_apls_rts_flag = 'Y'
556 where plip_id = i.plip_id;
557 --
558 end if;
559 --
560 If i.ptip_id is not null then
561 --
562 update ben_ptip_f
563 set drvbl_fctr_apls_rts_flag = 'Y'
564 where ptip_id = i.ptip_id;
565 --
566 end if;
567 --
568 If i.oipl_id is not null then
569 --
570 update ben_oipl_f
571 set drvbl_fctr_apls_rts_flag = 'Y'
572 where oipl_id = i.oipl_id;
573 --
574 end if;
575 --
576 End Loop;
577 --
578 elsif p_event = 'DELETE' then
579 --
580 For i in c1 Loop
581 --
582 If i.pgm_id is not null then
583 --
584 update ben_pgm_f
585 set drvbl_fctr_apls_rts_flag = 'N'
586 where pgm_id = i.pgm_id
587 and drvbl_fctr_apls_rts_flag = 'Y'
588 and not exists (select null
589 from ben_age_rt_f art,
590 ben_acty_base_rt_f abr,
591 ben_acty_vrbl_rt_f avr
592 where abr.pgm_id = i.pgm_id
593 and abr.acty_base_rt_id = avr.acty_base_rt_id
594 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
595 and not exists (select null
596 from ben_cmbn_age_los_rt_f cmr,
597 ben_acty_base_rt_f abr,
598 ben_acty_vrbl_rt_f avr
599 where abr.pgm_id = i.pgm_id
600 and abr.acty_base_rt_id = avr.acty_base_rt_id
601 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
602 and not exists (select null
603 from ben_comp_lvl_rt_f clr,
604 ben_acty_base_rt_f abr,
605 ben_acty_vrbl_rt_f avr
606 where abr.pgm_id = i.pgm_id
607 and abr.acty_base_rt_id = avr.acty_base_rt_id
608 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
609 and not exists (select null
610 from ben_hrs_wkd_in_perd_rt_f hwr,
611 ben_acty_base_rt_f abr,
612 ben_acty_vrbl_rt_f avr
613 where abr.pgm_id = i.pgm_id
614 and abr.acty_base_rt_id = avr.acty_base_rt_id
615 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
616 and not exists (select null
617 from ben_los_rt_f lsr,
618 ben_acty_base_rt_f abr,
619 ben_acty_vrbl_rt_f avr
620 where abr.pgm_id = i.pgm_id
621 and abr.acty_base_rt_id = avr.acty_base_rt_id
622 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
623 and not exists (select null
624 from ben_pct_fl_tm_rt_f pfr,
625 ben_acty_base_rt_f abr,
626 ben_acty_vrbl_rt_f avr
627 where abr.pgm_id = i.pgm_id
628 and abr.acty_base_rt_id = avr.acty_base_rt_id
629 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
630 --
631 End If;
632 --
633 If i.pl_id is not null then
634 --
635 update ben_pl_f
636 set drvbl_fctr_apls_rts_flag = 'N'
637 where pl_id = i.pl_id
638 and drvbl_fctr_apls_rts_flag = 'Y'
639 and not exists (select null
640 from ben_age_rt_f art,
641 ben_acty_base_rt_f abr,
642 ben_acty_vrbl_rt_f avr
643 where abr.pl_id = i.pl_id
644 and abr.acty_base_rt_id = avr.acty_base_rt_id
648 ben_acty_base_rt_f abr,
645 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
646 and not exists (select null
647 from ben_cmbn_age_los_rt_f cmr,
649 ben_acty_vrbl_rt_f avr
650 where abr.pl_id = i.pl_id
651 and abr.acty_base_rt_id = avr.acty_base_rt_id
652 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
653 and not exists (select null
654 from ben_comp_lvl_rt_f clr,
655 ben_acty_base_rt_f abr,
656 ben_acty_vrbl_rt_f avr
657 where abr.pl_id = i.pl_id
658 and abr.acty_base_rt_id = avr.acty_base_rt_id
659 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
660 and not exists (select null
661 from ben_hrs_wkd_in_perd_rt_f hwr,
662 ben_acty_base_rt_f abr,
663 ben_acty_vrbl_rt_f avr
664 where abr.pl_id = i.pl_id
665 and abr.acty_base_rt_id = avr.acty_base_rt_id
666 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
667 and not exists (select null
668 from ben_los_rt_f lsr,
669 ben_acty_base_rt_f abr,
670 ben_acty_vrbl_rt_f avr
671 where abr.pl_id = i.pl_id
672 and abr.acty_base_rt_id = avr.acty_base_rt_id
673 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
674 and not exists (select null
675 from ben_pct_fl_tm_rt_f pfr,
676 ben_acty_base_rt_f abr,
677 ben_acty_vrbl_rt_f avr
678 where abr.pl_id = i.pl_id
679 and abr.acty_base_rt_id = avr.acty_base_rt_id
680 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
681 and not exists (select null
682 from ben_age_rt_f art,
683 ben_actl_prem_f apr,
684 ben_actl_prem_vrbl_rt_f apv
685 where apr.pl_id = i.pl_id
686 and apr.actl_prem_id = apv.actl_prem_id
687 and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
688 and not exists (select null
689 from ben_cmbn_age_los_rt_f cmr,
690 ben_actl_prem_f apr,
691 ben_actl_prem_vrbl_rt_f apv
692 where apr.pl_id = i.pl_id
693 and apr.actl_prem_id = apv.actl_prem_id
694 and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
695 and not exists (select null
696 from ben_comp_lvl_rt_f clr,
697 ben_actl_prem_f apr,
698 ben_actl_prem_vrbl_rt_f apv
699 where apr.pl_id = i.pl_id
700 and apr.actl_prem_id = apv.actl_prem_id
701 and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
702 and not exists (select null
703 from ben_hrs_wkd_in_perd_rt_f hwr,
704 ben_actl_prem_f apr,
705 ben_actl_prem_vrbl_rt_f apv
706 where apr.pl_id = i.pl_id
707 and apr.actl_prem_id = apv.actl_prem_id
708 and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
709 and not exists (select null
710 from ben_los_rt_f lsr,
711 ben_actl_prem_f apr,
712 ben_actl_prem_vrbl_rt_f apv
713 where apr.pl_id = i.pl_id
714 and apr.actl_prem_id = apv.actl_prem_id
715 and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
716 and not exists (select null
717 from ben_pct_fl_tm_rt_f pfr,
718 ben_actl_prem_f apr,
719 ben_actl_prem_vrbl_rt_f apv
720 where apr.pl_id = i.pl_id
721 and apr.actl_prem_id = apv.actl_prem_id
722 and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
723 and not exists (select null
724 from ben_age_rt_f art,
725 ben_cvg_amt_calc_mthd_f ccm,
726 ben_bnft_vrbl_rt_f bvr
727 where ccm.pl_id = i.pl_id
728 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
729 and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
730 and not exists (select null
731 from ben_cmbn_age_los_rt_f cmr,
732 ben_cvg_amt_calc_mthd_f ccm,
733 ben_bnft_vrbl_rt_f bvr
734 where ccm.pl_id = i.pl_id
735 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
736 and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
737 and not exists (select null
741 where ccm.pl_id = i.pl_id
738 from ben_comp_lvl_rt_f clr,
739 ben_cvg_amt_calc_mthd_f ccm,
740 ben_bnft_vrbl_rt_f bvr
742 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
743 and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
744 and not exists (select null
745 from ben_hrs_wkd_in_perd_rt_f hwr,
746 ben_cvg_amt_calc_mthd_f ccm,
747 ben_bnft_vrbl_rt_f bvr
748 where ccm.pl_id = i.pl_id
749 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
750 and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
751 and not exists (select null
752 from ben_los_rt_f lsr,
753 ben_cvg_amt_calc_mthd_f ccm,
754 ben_bnft_vrbl_rt_f bvr
755 where ccm.pl_id = i.pl_id
756 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
757 and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
758 and not exists (select null
759 from ben_pct_fl_tm_rt_f pfr,
760 ben_cvg_amt_calc_mthd_f ccm,
761 ben_bnft_vrbl_rt_f bvr
762 where ccm.pl_id = i.pl_id
763 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
764 and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
765 --
766 End If;
767 --
768 If i.plip_id is not null then
769 --
770 update ben_plip_f
771 set drvbl_fctr_apls_rts_flag = 'N'
772 where plip_id = i.plip_id
773 and drvbl_fctr_apls_rts_flag = 'Y'
774 and not exists (select null
775 from ben_age_rt_f art,
776 ben_acty_base_rt_f abr,
777 ben_acty_vrbl_rt_f avr
778 where abr.plip_id = i.plip_id
779 and abr.acty_base_rt_id = avr.acty_base_rt_id
780 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
781 and not exists (select null
782 from ben_cmbn_age_los_rt_f cmr,
783 ben_acty_base_rt_f abr,
784 ben_acty_vrbl_rt_f avr
785 where abr.plip_id = i.plip_id
786 and abr.acty_base_rt_id = avr.acty_base_rt_id
787 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
788 and not exists (select null
789 from ben_comp_lvl_rt_f clr,
790 ben_acty_base_rt_f abr,
791 ben_acty_vrbl_rt_f avr
792 where abr.plip_id = i.plip_id
793 and abr.acty_base_rt_id = avr.acty_base_rt_id
794 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
795 and not exists (select null
796 from ben_hrs_wkd_in_perd_rt_f hwr,
797 ben_acty_base_rt_f abr,
798 ben_acty_vrbl_rt_f avr
799 where abr.plip_id = i.plip_id
800 and abr.acty_base_rt_id = avr.acty_base_rt_id
801 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
802 and not exists (select null
803 from ben_los_rt_f lsr,
804 ben_acty_base_rt_f abr,
805 ben_acty_vrbl_rt_f avr
806 where abr.plip_id = i.plip_id
807 and abr.acty_base_rt_id = avr.acty_base_rt_id
808 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
809 and not exists (select null
810 from ben_pct_fl_tm_rt_f pfr,
811 ben_acty_base_rt_f abr,
812 ben_acty_vrbl_rt_f avr
813 where abr.plip_id = i.plip_id
814 and abr.acty_base_rt_id = avr.acty_base_rt_id
815 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
816 and not exists (select null
817 from ben_age_rt_f art,
818 ben_cvg_amt_calc_mthd_f ccm,
819 ben_bnft_vrbl_rt_f bvr
820 where ccm.plip_id = i.plip_id
821 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
822 and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
823 and not exists (select null
824 from ben_cmbn_age_los_rt_f cmr,
825 ben_cvg_amt_calc_mthd_f ccm,
826 ben_bnft_vrbl_rt_f bvr
827 where ccm.plip_id = i.plip_id
828 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
829 and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
830 and not exists (select null
831 from ben_comp_lvl_rt_f clr,
832 ben_cvg_amt_calc_mthd_f ccm,
833 ben_bnft_vrbl_rt_f bvr
837 and not exists (select null
834 where ccm.plip_id = i.plip_id
835 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
836 and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
838 from ben_hrs_wkd_in_perd_rt_f hwr,
839 ben_cvg_amt_calc_mthd_f ccm,
840 ben_bnft_vrbl_rt_f bvr
841 where ccm.plip_id = i.plip_id
842 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
843 and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
844 and not exists (select null
845 from ben_los_rt_f lsr,
846 ben_cvg_amt_calc_mthd_f ccm,
847 ben_bnft_vrbl_rt_f bvr
848 where ccm.plip_id = i.plip_id
849 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
850 and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
851 and not exists (select null
852 from ben_pct_fl_tm_rt_f pfr,
853 ben_cvg_amt_calc_mthd_f ccm,
854 ben_bnft_vrbl_rt_f bvr
855 where ccm.plip_id = i.plip_id
856 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
857 and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
858 --
859 End If;
860 --
861 If i.ptip_id is not null then
862 --
863 update ben_ptip_f
864 set drvbl_fctr_apls_rts_flag = 'N'
865 where ptip_id = i.ptip_id
866 and drvbl_fctr_apls_rts_flag = 'Y'
867 and not exists (select null
868 from ben_age_rt_f art,
869 ben_acty_base_rt_f abr,
870 ben_acty_vrbl_rt_f avr
871 where abr.ptip_id = i.ptip_id
872 and abr.acty_base_rt_id = avr.acty_base_rt_id
873 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
874 and not exists (select null
875 from ben_cmbn_age_los_rt_f cmr,
876 ben_acty_base_rt_f abr,
877 ben_acty_vrbl_rt_f avr
878 where abr.ptip_id = i.ptip_id
879 and abr.acty_base_rt_id = avr.acty_base_rt_id
880 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
881 and not exists (select null
882 from ben_comp_lvl_rt_f clr,
883 ben_acty_base_rt_f abr,
884 ben_acty_vrbl_rt_f avr
885 where abr.ptip_id = i.ptip_id
886 and abr.acty_base_rt_id = avr.acty_base_rt_id
887 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
888 and not exists (select null
889 from ben_hrs_wkd_in_perd_rt_f hwr,
890 ben_acty_base_rt_f abr,
891 ben_acty_vrbl_rt_f avr
892 where abr.ptip_id = i.ptip_id
893 and abr.acty_base_rt_id = avr.acty_base_rt_id
894 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
895 and not exists (select null
896 from ben_los_rt_f lsr,
897 ben_acty_base_rt_f abr,
898 ben_acty_vrbl_rt_f avr
899 where abr.ptip_id = i.ptip_id
900 and abr.acty_base_rt_id = avr.acty_base_rt_id
901 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
902 and not exists (select null
903 from ben_pct_fl_tm_rt_f pfr,
904 ben_acty_base_rt_f abr,
905 ben_acty_vrbl_rt_f avr
906 where abr.ptip_id = i.ptip_id
907 and abr.acty_base_rt_id = avr.acty_base_rt_id
908 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
909 --
910 End If;
911 --
912 If i.oipl_id is not null then
913 --
914 update ben_oipl_f
915 set drvbl_fctr_apls_rts_flag = 'N'
916 where oipl_id = i.oipl_id
917 and drvbl_fctr_apls_rts_flag = 'Y'
918 and not exists (select null
919 from ben_age_rt_f art,
920 ben_acty_base_rt_f abr,
921 ben_acty_vrbl_rt_f avr
922 where abr.oipl_id = i.oipl_id
923 and abr.acty_base_rt_id = avr.acty_base_rt_id
924 and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
925 and not exists (select null
926 from ben_cmbn_age_los_rt_f cmr,
927 ben_acty_base_rt_f abr,
928 ben_acty_vrbl_rt_f avr
929 where abr.oipl_id = i.oipl_id
930 and abr.acty_base_rt_id = avr.acty_base_rt_id
931 and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
932 and not exists (select null
933 from ben_comp_lvl_rt_f clr,
937 and abr.acty_base_rt_id = avr.acty_base_rt_id
934 ben_acty_base_rt_f abr,
935 ben_acty_vrbl_rt_f avr
936 where abr.oipl_id = i.oipl_id
938 and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
939 and not exists (select null
940 from ben_hrs_wkd_in_perd_rt_f hwr,
941 ben_acty_base_rt_f abr,
942 ben_acty_vrbl_rt_f avr
943 where abr.oipl_id = i.oipl_id
944 and abr.acty_base_rt_id = avr.acty_base_rt_id
945 and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
946 and not exists (select null
947 from ben_los_rt_f lsr,
948 ben_acty_base_rt_f abr,
949 ben_acty_vrbl_rt_f avr
950 where abr.oipl_id = i.oipl_id
951 and abr.acty_base_rt_id = avr.acty_base_rt_id
952 and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
953 and not exists (select null
954 from ben_pct_fl_tm_rt_f pfr,
955 ben_acty_base_rt_f abr,
956 ben_acty_vrbl_rt_f avr
957 where abr.oipl_id = i.oipl_id
958 and abr.acty_base_rt_id = avr.acty_base_rt_id
959 and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
960 and not exists (select null
961 from ben_age_rt_f art,
962 ben_actl_prem_f apr,
963 ben_actl_prem_vrbl_rt_f apv
964 where apr.oipl_id = i.oipl_id
965 and apr.actl_prem_id = apv.actl_prem_id
966 and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
967 and not exists (select null
968 from ben_cmbn_age_los_rt_f cmr,
969 ben_actl_prem_f apr,
970 ben_actl_prem_vrbl_rt_f apv
971 where apr.oipl_id = i.oipl_id
972 and apr.actl_prem_id = apv.actl_prem_id
973 and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
974 and not exists (select null
975 from ben_comp_lvl_rt_f clr,
976 ben_actl_prem_f apr,
977 ben_actl_prem_vrbl_rt_f apv
978 where apr.oipl_id = i.oipl_id
979 and apr.actl_prem_id = apv.actl_prem_id
980 and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
981 and not exists (select null
982 from ben_hrs_wkd_in_perd_rt_f hwr,
983 ben_actl_prem_f apr,
984 ben_actl_prem_vrbl_rt_f apv
985 where apr.oipl_id = i.oipl_id
986 and apr.actl_prem_id = apv.actl_prem_id
987 and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
988 and not exists (select null
989 from ben_los_rt_f lsr,
990 ben_actl_prem_f apr,
991 ben_actl_prem_vrbl_rt_f apv
992 where apr.oipl_id = i.oipl_id
993 and apr.actl_prem_id = apv.actl_prem_id
994 and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
995 and not exists (select null
996 from ben_pct_fl_tm_rt_f pfr,
997 ben_actl_prem_f apr,
998 ben_actl_prem_vrbl_rt_f apv
999 where apr.oipl_id = i.oipl_id
1000 and apr.actl_prem_id = apv.actl_prem_id
1001 and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
1002 and not exists (select null
1003 from ben_age_rt_f art,
1004 ben_cvg_amt_calc_mthd_f ccm,
1005 ben_bnft_vrbl_rt_f bvr
1006 where ccm.oipl_id = i.oipl_id
1007 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
1008 and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
1009 and not exists (select null
1010 from ben_cmbn_age_los_rt_f cmr,
1011 ben_cvg_amt_calc_mthd_f ccm,
1012 ben_bnft_vrbl_rt_f bvr
1013 where ccm.oipl_id = i.oipl_id
1014 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
1015 and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
1016 and not exists (select null
1017 from ben_comp_lvl_rt_f clr,
1018 ben_cvg_amt_calc_mthd_f ccm,
1019 ben_bnft_vrbl_rt_f bvr
1020 where ccm.oipl_id = i.oipl_id
1021 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
1022 and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
1023 and not exists (select null
1024 from ben_hrs_wkd_in_perd_rt_f hwr,
1028 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
1025 ben_cvg_amt_calc_mthd_f ccm,
1026 ben_bnft_vrbl_rt_f bvr
1027 where ccm.oipl_id = i.oipl_id
1029 and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
1030 and not exists (select null
1031 from ben_los_rt_f lsr,
1032 ben_cvg_amt_calc_mthd_f ccm,
1033 ben_bnft_vrbl_rt_f bvr
1034 where ccm.oipl_id = i.oipl_id
1035 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
1036 and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
1037 and not exists (select null
1038 from ben_pct_fl_tm_rt_f pfr,
1039 ben_cvg_amt_calc_mthd_f ccm,
1040 ben_bnft_vrbl_rt_f bvr
1041 where ccm.oipl_id = i.oipl_id
1042 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
1043 and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
1044 --
1045 End If;
1046 --
1047 End Loop;
1048 --
1049 end if;
1050 --
1051 end derivable_rate_handler;
1052 --
1053 end ben_derivable_rate;