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