[Home] [Help]
123: --
124: l_strg := 'update '||p_table_name||' t set drvbl_fctr_prtn_elig_flag = :1
125:
126: where '||p_col_name|| '= :2 and (
127: exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
128: ben_prtn_elig_prfl_f cep
129: where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
130: cep.eligy_prfl_id = eap.eligy_prfl_id) or
131: exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
127: exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
128: ben_prtn_elig_prfl_f cep
129: where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
130: cep.eligy_prfl_id = eap.eligy_prfl_id) or
131: exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
132: ben_prtn_elig_prfl_f cep
133: where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
134: cep.eligy_prfl_id = ecp.eligy_prfl_id) or
135: exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
131: exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
132: ben_prtn_elig_prfl_f cep
133: where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
134: cep.eligy_prfl_id = ecp.eligy_prfl_id) or
135: exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
136: ben_prtn_elig_prfl_f cep
137: where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
138: cep.eligy_prfl_id = ecl.eligy_prfl_id) or
139: exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
135: exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
136: ben_prtn_elig_prfl_f cep
137: where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
138: cep.eligy_prfl_id = ecl.eligy_prfl_id) or
139: exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
140: ben_prtn_elig_prfl_f cep
141: where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
142: cep.eligy_prfl_id = ehw.eligy_prfl_id) or
143: exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
139: exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
140: ben_prtn_elig_prfl_f cep
141: where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
142: cep.eligy_prfl_id = ehw.eligy_prfl_id) or
143: exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
144: ben_prtn_elig_prfl_f cep
145: where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
146: cep.eligy_prfl_id = els.eligy_prfl_id) or
147: exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
143: exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
144: ben_prtn_elig_prfl_f cep
145: where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
146: cep.eligy_prfl_id = els.eligy_prfl_id) or
147: exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
148: ben_prtn_elig_prfl_f cep
149: where epa.'||p_col_name||'= :8 and epa.prtn_elig_id = cep.prtn_elig_id and
150: cep.eligy_prfl_id = epf.eligy_prfl_id))';
151: execute immediate l_strg using 'Y',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
153: elsif p_event = 'DELETE' then
154: hr_utility.set_location('update event ',10);
155: l_strg := 'update '||p_table_name||' t set drvbl_fctr_prtn_elig_flag = :1
156: where '||p_col_name|| '= :2 and
157: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
158: ben_prtn_elig_prfl_f cep
159: where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
160: cep.eligy_prfl_id = eap.eligy_prfl_id) and
161: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
157: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
158: ben_prtn_elig_prfl_f cep
159: where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
160: cep.eligy_prfl_id = eap.eligy_prfl_id) and
161: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
162: ben_prtn_elig_prfl_f cep
163: where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
164: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
165: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
161: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
162: ben_prtn_elig_prfl_f cep
163: where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
164: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
165: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
166: ben_prtn_elig_prfl_f cep
167: where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
168: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
169: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
165: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
166: ben_prtn_elig_prfl_f cep
167: where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
168: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
169: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
170: ben_prtn_elig_prfl_f cep
171: where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
172: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
173: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
169: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
170: ben_prtn_elig_prfl_f cep
171: where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
172: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
173: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
174: ben_prtn_elig_prfl_f cep
175: where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
176: cep.eligy_prfl_id = els.eligy_prfl_id) and
177: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
173: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
174: ben_prtn_elig_prfl_f cep
175: where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
176: cep.eligy_prfl_id = els.eligy_prfl_id) and
177: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
178: ben_prtn_elig_prfl_f cep
179: where epa.'||p_col_name||'= :8 and epa.prtn_elig_id = cep.prtn_elig_id and
180: cep.eligy_prfl_id = epf.eligy_prfl_id)';
181: execute immediate l_strg using 'N',p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id,p_col_id;
192: --
193: l_proc varchar2(80) := g_package||'derivable factor handler';
194: --
195: cursor c1 is select pgm_id,pl_id,ptip_id,plip_id,oipl_id
196: from ben_prtn_elig_f epa, ben_prtn_elig_prfl_f cep
197: where cep.eligy_prfl_id = p_eligy_prfl_id and
198: cep.prtn_elig_id = epa.prtn_elig_id;
199:
200: --
254: If i.pgm_id is not null then
255:
256: update ben_pgm_f set drvbl_fctr_prtn_elig_flag = 'N'
257: where pgm_id = i.pgm_id and
258: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
259: ben_prtn_elig_prfl_f cep
260: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
261: cep.eligy_prfl_id = eap.eligy_prfl_id) and
262: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
258: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
259: ben_prtn_elig_prfl_f cep
260: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
261: cep.eligy_prfl_id = eap.eligy_prfl_id) and
262: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
263: ben_prtn_elig_prfl_f cep
264: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
265: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
266: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
262: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
263: ben_prtn_elig_prfl_f cep
264: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
265: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
266: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
267: ben_prtn_elig_prfl_f cep
268: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
269: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
270: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
266: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
267: ben_prtn_elig_prfl_f cep
268: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
269: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
270: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
271: ben_prtn_elig_prfl_f cep
272: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
273: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
274: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
270: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
271: ben_prtn_elig_prfl_f cep
272: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
273: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
274: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
275: ben_prtn_elig_prfl_f cep
276: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
277: cep.eligy_prfl_id = els.eligy_prfl_id) and
278: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
274: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
275: ben_prtn_elig_prfl_f cep
276: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
277: cep.eligy_prfl_id = els.eligy_prfl_id) and
278: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
279: ben_prtn_elig_prfl_f cep
280: where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
281: cep.eligy_prfl_id = epf.eligy_prfl_id);
282: End If;
284: If i.pl_id is not null then
285:
286: update ben_pl_f set drvbl_fctr_prtn_elig_flag = 'N'
287: where pl_id = i.pl_id and
288: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
289: ben_prtn_elig_prfl_f cep
290: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
291: cep.eligy_prfl_id = eap.eligy_prfl_id) and
292: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
288: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
289: ben_prtn_elig_prfl_f cep
290: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
291: cep.eligy_prfl_id = eap.eligy_prfl_id) and
292: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
293: ben_prtn_elig_prfl_f cep
294: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
295: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
296: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
292: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
293: ben_prtn_elig_prfl_f cep
294: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
295: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
296: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
297: ben_prtn_elig_prfl_f cep
298: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
299: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
300: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
296: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
297: ben_prtn_elig_prfl_f cep
298: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
299: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
300: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
301: ben_prtn_elig_prfl_f cep
302: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
303: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
304: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
300: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
301: ben_prtn_elig_prfl_f cep
302: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
303: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
304: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
305: ben_prtn_elig_prfl_f cep
306: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
307: cep.eligy_prfl_id = els.eligy_prfl_id) and
308: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
304: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
305: ben_prtn_elig_prfl_f cep
306: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
307: cep.eligy_prfl_id = els.eligy_prfl_id) and
308: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
309: ben_prtn_elig_prfl_f cep
310: where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
311: cep.eligy_prfl_id = epf.eligy_prfl_id);
312: End If;
314: If i.plip_id is not null then
315:
316: update ben_plip_f set drvbl_fctr_prtn_elig_flag = 'N'
317: where plip_id = i.plip_id and
318: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
319: ben_prtn_elig_prfl_f cep
320: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
321: cep.eligy_prfl_id = eap.eligy_prfl_id) and
322: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
318: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
319: ben_prtn_elig_prfl_f cep
320: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
321: cep.eligy_prfl_id = eap.eligy_prfl_id) and
322: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
323: ben_prtn_elig_prfl_f cep
324: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
325: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
326: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
322: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
323: ben_prtn_elig_prfl_f cep
324: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
325: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
326: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
327: ben_prtn_elig_prfl_f cep
328: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
329: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
330: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
326: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
327: ben_prtn_elig_prfl_f cep
328: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
329: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
330: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
331: ben_prtn_elig_prfl_f cep
332: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
333: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
334: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
330: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
331: ben_prtn_elig_prfl_f cep
332: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
333: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
334: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
335: ben_prtn_elig_prfl_f cep
336: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
337: cep.eligy_prfl_id = els.eligy_prfl_id) and
338: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
334: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
335: ben_prtn_elig_prfl_f cep
336: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
337: cep.eligy_prfl_id = els.eligy_prfl_id) and
338: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
339: ben_prtn_elig_prfl_f cep
340: where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
341: cep.eligy_prfl_id = epf.eligy_prfl_id);
342: End If;
344: If i.ptip_id is not null then
345:
346: update ben_ptip_f set drvbl_fctr_prtn_elig_flag = 'N'
347: where ptip_id = i.ptip_id and
348: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
349: ben_prtn_elig_prfl_f cep
350: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
351: cep.eligy_prfl_id = eap.eligy_prfl_id) and
352: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
348: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
349: ben_prtn_elig_prfl_f cep
350: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
351: cep.eligy_prfl_id = eap.eligy_prfl_id) and
352: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
353: ben_prtn_elig_prfl_f cep
354: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
355: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
356: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
352: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
353: ben_prtn_elig_prfl_f cep
354: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
355: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
356: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
357: ben_prtn_elig_prfl_f cep
358: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
359: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
360: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
356: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
357: ben_prtn_elig_prfl_f cep
358: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
359: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
360: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
361: ben_prtn_elig_prfl_f cep
362: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
363: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
364: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
360: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
361: ben_prtn_elig_prfl_f cep
362: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
363: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
364: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
365: ben_prtn_elig_prfl_f cep
366: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
367: cep.eligy_prfl_id = els.eligy_prfl_id) and
368: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
364: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
365: ben_prtn_elig_prfl_f cep
366: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
367: cep.eligy_prfl_id = els.eligy_prfl_id) and
368: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
369: ben_prtn_elig_prfl_f cep
370: where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
371: cep.eligy_prfl_id = epf.eligy_prfl_id);
372: End If;
374: If i.oipl_id is not null then
375:
376: update ben_oipl_f set drvbl_fctr_prtn_elig_flag = 'N'
377: where oipl_id = i.oipl_id and
378: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
379: ben_prtn_elig_prfl_f cep
380: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
381: cep.eligy_prfl_id = eap.eligy_prfl_id) and
382: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
378: not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
379: ben_prtn_elig_prfl_f cep
380: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
381: cep.eligy_prfl_id = eap.eligy_prfl_id) and
382: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
383: ben_prtn_elig_prfl_f cep
384: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
385: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
386: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
382: not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
383: ben_prtn_elig_prfl_f cep
384: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
385: cep.eligy_prfl_id = ecp.eligy_prfl_id) and
386: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
387: ben_prtn_elig_prfl_f cep
388: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
389: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
390: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
386: not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
387: ben_prtn_elig_prfl_f cep
388: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
389: cep.eligy_prfl_id = ecl.eligy_prfl_id) and
390: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
391: ben_prtn_elig_prfl_f cep
392: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
393: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
394: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
390: not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
391: ben_prtn_elig_prfl_f cep
392: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
393: cep.eligy_prfl_id = ehw.eligy_prfl_id) and
394: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
395: ben_prtn_elig_prfl_f cep
396: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
397: cep.eligy_prfl_id = els.eligy_prfl_id) and
398: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
394: not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
395: ben_prtn_elig_prfl_f cep
396: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
397: cep.eligy_prfl_id = els.eligy_prfl_id) and
398: not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
399: ben_prtn_elig_prfl_f cep
400: where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
401: cep.eligy_prfl_id = epf.eligy_prfl_id);
402: End If;