1 package body ben_derivable_factor as
2 /* $Header: bendrvft.pkb 115.8 2004/02/10 07:16:58 vvprabhu 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
13 Purpose
14 This package is used to handle setting of Derivable Factor Participation Flag based on
15 data changes that have occurred on child component tables.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 06-Mar-2000 KMahendr 115.0 Created.
20 04-Jan-2001 RCHASE 115.1 Bug 1568561.Fix NDS Create calls in
21 eligy_prfl_handler, missing parens
22 around or exists conditions
23 21-dec-2001 tjesumic 115.2 dynomic sql corrected bug :2160610
24 21-dec-2001 tjesumic 115.5 dbdrv fixed
25 07-dec-2002 tjesumic 115.6 commit added
26 05-May-2003 kmahendr 115.7 Bug#2939392 - dynamic sql is modified to take
27 bind variables
28 04-Oct-2004 vvprabhu 115.8 Bug 3431740 Parameter p_oracle_schema added
29 to cursor c1 in elig_prfl_handler,
30 the value is got by the
31 call to fnd_installation.get_app_info
32
33
34 */
35 --------------------------------------------------------------------------------
36 g_package varchar2(80) := 'ben_derivable_factor.';
37 --
38 procedure eligy_prfl_handler
39 (p_event in varchar2,
40 p_table_name in varchar2,
41 p_col_name in varchar2,
42 p_col_id in number)is
43 --
44 l_proc varchar2(80) := g_package||'eligy prfl handler';
45 l_strg varchar2(32000) ;
46 l_dummy varchar2(1);
47 l_status varchar2(1);
48 l_industry varchar2(1);
49 l_application_short_name varchar2(30) := 'BEN';
50 l_oracle_schema varchar2(30);
51 l_return boolean;
52
53
54
55 --
56 cursor c1 (p_tab_name varchar2, p_col_name varchar2,p_oracle_schema varchar2) is
57 select null
58 from all_tab_columns
59 where table_name = upper(p_tab_name)
60 and column_name = upper(p_col_name)
61 and owner=upper(p_oracle_schema);
62 --
63 begin
64 --
65 hr_utility.set_location('Entering '||l_proc,10);
66 --
67 --
68 -- Parameter validation
69 --
70 hr_api.mandatory_arg_error(p_api_name => l_proc,
71 p_argument => 'event',
72 p_argument_value => p_event);
73 --
74 hr_api.mandatory_arg_error(p_api_name => l_proc,
75 p_argument => 'table_name',
76 p_argument_value => p_table_name);
77 --
78 hr_api.mandatory_arg_error(p_api_name => l_proc,
79 p_argument => 'column name',
80 p_argument_value => p_col_name);
81 --
82 hr_api.mandatory_arg_error(p_api_name => l_proc,
83 p_argument => 'column_id',
84 p_argument_value => p_col_id);
85
86 -- Bug 3431740 Parameter p_oracle_schema added to cursor c1, the value is got by the
87 -- following call
88 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
89 status => l_status,
90 industry => l_industry,
91 oracle_schema => l_oracle_schema);
92 --
93 --
94 -- Check operation is valid
95 --
96 if p_event not in ('CREATE','DELETE') then
97 --
98 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
99 fnd_message.raise_error;
100 --
101 end if;
102 --
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 --
114 if p_event = 'CREATE' then
115 --
116 -- rchase bug 1568561 - added paren pair around ors
117 hr_utility.set_location('Create event ',10);
118 --
119 l_strg := 'update '||p_table_name||' t set drvbl_fctr_prtn_elig_flag = :1
120
121 where '||p_col_name|| '= :2 and (
122 exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
123 ben_prtn_elig_prfl_f cep
124 where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
125 cep.eligy_prfl_id = eap.eligy_prfl_id) or
126 exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
127 ben_prtn_elig_prfl_f cep
128 where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
129 cep.eligy_prfl_id = ecp.eligy_prfl_id) or
130 exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
131 ben_prtn_elig_prfl_f cep
132 where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
133 cep.eligy_prfl_id = ecl.eligy_prfl_id) or
134 exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
135 ben_prtn_elig_prfl_f cep
136 where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
137 cep.eligy_prfl_id = ehw.eligy_prfl_id) or
138 exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
139 ben_prtn_elig_prfl_f cep
140 where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
141 cep.eligy_prfl_id = els.eligy_prfl_id) or
142 exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
143 ben_prtn_elig_prfl_f cep
144 where epa.'||p_col_name||'= :8 and epa.prtn_elig_id = cep.prtn_elig_id and
145 cep.eligy_prfl_id = epf.eligy_prfl_id))';
146 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;
147 --
148 elsif p_event = 'DELETE' then
149 hr_utility.set_location('update event ',10);
150 l_strg := 'update '||p_table_name||' t set drvbl_fctr_prtn_elig_flag = :1
151 where '||p_col_name|| '= :2 and
152 not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
153 ben_prtn_elig_prfl_f cep
154 where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
155 cep.eligy_prfl_id = eap.eligy_prfl_id) and
156 not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
157 ben_prtn_elig_prfl_f cep
158 where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
159 cep.eligy_prfl_id = ecp.eligy_prfl_id) and
160 not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
161 ben_prtn_elig_prfl_f cep
162 where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
163 cep.eligy_prfl_id = ecl.eligy_prfl_id) and
164 not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
165 ben_prtn_elig_prfl_f cep
166 where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
167 cep.eligy_prfl_id = ehw.eligy_prfl_id) and
168 not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
169 ben_prtn_elig_prfl_f cep
170 where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
171 cep.eligy_prfl_id = els.eligy_prfl_id) and
172 not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
173 ben_prtn_elig_prfl_f cep
174 where epa.'||p_col_name||'= :8 and epa.prtn_elig_id = cep.prtn_elig_id and
175 cep.eligy_prfl_id = epf.eligy_prfl_id)';
176 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;
177 --
178 end if;
179 --
180 hr_utility.set_location('Leaving '||l_proc,10);
181 --
182 end eligy_prfl_handler;
183 -----------------------------------------------------------------------
184 procedure derivable_factor_handler
185 (p_event in varchar2,
186 p_eligy_prfl_id in number)is
187 --
188 l_proc varchar2(80) := g_package||'derivable factor handler';
189 --
190 cursor c1 is select pgm_id,pl_id,ptip_id,plip_id,oipl_id
191 from ben_prtn_elig_f epa, ben_prtn_elig_prfl_f cep
192 where cep.eligy_prfl_id = p_eligy_prfl_id and
193 cep.prtn_elig_id = epa.prtn_elig_id;
194
195 --
196
197 begin
198 --
199 hr_utility.set_location('Entering '||l_proc,10);
200 --
201 --
202 -- Parameter validation
203 --
204 hr_api.mandatory_arg_error(p_api_name => l_proc,
205 p_argument => 'event',
206 p_argument_value => p_event);
207 hr_api.mandatory_arg_error(p_api_name => l_proc,
208 p_argument => 'eligy prfl id',
209 p_argument_value => p_eligy_prfl_id);
210 --
211 if p_event not in ('CREATE','DELETE') then
212 --
213 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
214 fnd_message.raise_error;
215 --
216 end if;
217
218
219 if p_event = 'CREATE' then
220
221 For i in c1 loop
222 If i.pgm_id is not null then
223 update ben_pgm_f set drvbl_fctr_prtn_elig_flag = 'Y'
224 where pgm_id = i.pgm_id;
225 end if;
226
227 If i.pl_id is not null then
228 update ben_pl_f set drvbl_fctr_prtn_elig_flag = 'Y'
229 where pl_id = i.pl_id;
230 end if;
231
232 If i.plip_id is not null then
233 update ben_plip_f set drvbl_fctr_prtn_elig_flag = 'Y'
234 where plip_id = i.plip_id;
235 end if;
236
237 If i.ptip_id is not null then
238 update ben_ptip_f set drvbl_fctr_prtn_elig_flag = 'Y'
239 where ptip_id = i.ptip_id;
240 end if;
241
242 If i.oipl_id is not null then
243 update ben_oipl_f set drvbl_fctr_prtn_elig_flag = 'Y'
244 where oipl_id = i.oipl_id;
245 end if;
246 End Loop;
247 elsif p_event = 'DELETE' then
248 For i in c1 Loop
249 If i.pgm_id is not null then
250
251 update ben_pgm_f set drvbl_fctr_prtn_elig_flag = 'N'
252 where pgm_id = i.pgm_id and
253 not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
254 ben_prtn_elig_prfl_f cep
255 where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
256 cep.eligy_prfl_id = eap.eligy_prfl_id) and
257 not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
258 ben_prtn_elig_prfl_f cep
259 where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
260 cep.eligy_prfl_id = ecp.eligy_prfl_id) and
261 not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
262 ben_prtn_elig_prfl_f cep
263 where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
264 cep.eligy_prfl_id = ecl.eligy_prfl_id) and
265 not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
266 ben_prtn_elig_prfl_f cep
267 where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
268 cep.eligy_prfl_id = ehw.eligy_prfl_id) and
269 not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
270 ben_prtn_elig_prfl_f cep
271 where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
272 cep.eligy_prfl_id = els.eligy_prfl_id) and
273 not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
274 ben_prtn_elig_prfl_f cep
275 where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
276 cep.eligy_prfl_id = epf.eligy_prfl_id);
277 End If;
278 --
279 If i.pl_id is not null then
280
281 update ben_pl_f set drvbl_fctr_prtn_elig_flag = 'N'
282 where pl_id = i.pl_id and
283 not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
284 ben_prtn_elig_prfl_f cep
285 where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
286 cep.eligy_prfl_id = eap.eligy_prfl_id) and
287 not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
288 ben_prtn_elig_prfl_f cep
289 where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
290 cep.eligy_prfl_id = ecp.eligy_prfl_id) and
291 not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
292 ben_prtn_elig_prfl_f cep
293 where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
294 cep.eligy_prfl_id = ecl.eligy_prfl_id) and
295 not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
296 ben_prtn_elig_prfl_f cep
297 where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
298 cep.eligy_prfl_id = ehw.eligy_prfl_id) and
299 not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
300 ben_prtn_elig_prfl_f cep
304 ben_prtn_elig_prfl_f cep
301 where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
302 cep.eligy_prfl_id = els.eligy_prfl_id) and
303 not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
305 where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
306 cep.eligy_prfl_id = epf.eligy_prfl_id);
307 End If;
308 --
309 If i.plip_id is not null then
310
311 update ben_plip_f set drvbl_fctr_prtn_elig_flag = 'N'
312 where plip_id = i.plip_id and
313 not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
314 ben_prtn_elig_prfl_f cep
315 where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
316 cep.eligy_prfl_id = eap.eligy_prfl_id) and
317 not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
318 ben_prtn_elig_prfl_f cep
319 where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
320 cep.eligy_prfl_id = ecp.eligy_prfl_id) and
321 not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
322 ben_prtn_elig_prfl_f cep
323 where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
324 cep.eligy_prfl_id = ecl.eligy_prfl_id) and
325 not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
326 ben_prtn_elig_prfl_f cep
327 where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
328 cep.eligy_prfl_id = ehw.eligy_prfl_id) and
329 not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
330 ben_prtn_elig_prfl_f cep
331 where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
332 cep.eligy_prfl_id = els.eligy_prfl_id) and
333 not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
334 ben_prtn_elig_prfl_f cep
335 where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
336 cep.eligy_prfl_id = epf.eligy_prfl_id);
337 End If;
338 --
339 If i.ptip_id is not null then
340
341 update ben_ptip_f set drvbl_fctr_prtn_elig_flag = 'N'
342 where ptip_id = i.ptip_id and
343 not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
344 ben_prtn_elig_prfl_f cep
345 where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
346 cep.eligy_prfl_id = eap.eligy_prfl_id) and
347 not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
348 ben_prtn_elig_prfl_f cep
349 where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
350 cep.eligy_prfl_id = ecp.eligy_prfl_id) and
351 not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
352 ben_prtn_elig_prfl_f cep
353 where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
354 cep.eligy_prfl_id = ecl.eligy_prfl_id) and
355 not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
356 ben_prtn_elig_prfl_f cep
357 where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
358 cep.eligy_prfl_id = ehw.eligy_prfl_id) and
359 not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
360 ben_prtn_elig_prfl_f cep
361 where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
362 cep.eligy_prfl_id = els.eligy_prfl_id) and
363 not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
364 ben_prtn_elig_prfl_f cep
365 where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
366 cep.eligy_prfl_id = epf.eligy_prfl_id);
367 End If;
368 --
369 If i.oipl_id is not null then
370
371 update ben_oipl_f set drvbl_fctr_prtn_elig_flag = 'N'
372 where oipl_id = i.oipl_id and
373 not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
374 ben_prtn_elig_prfl_f cep
375 where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
376 cep.eligy_prfl_id = eap.eligy_prfl_id) and
377 not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
378 ben_prtn_elig_prfl_f cep
379 where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
380 cep.eligy_prfl_id = ecp.eligy_prfl_id) and
381 not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
382 ben_prtn_elig_prfl_f cep
383 where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
384 cep.eligy_prfl_id = ecl.eligy_prfl_id) and
385 not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
386 ben_prtn_elig_prfl_f cep
387 where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
388 cep.eligy_prfl_id = ehw.eligy_prfl_id) and
389 not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
390 ben_prtn_elig_prfl_f cep
391 where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
392 cep.eligy_prfl_id = els.eligy_prfl_id) and
393 not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
394 ben_prtn_elig_prfl_f cep
395 where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
396 cep.eligy_prfl_id = epf.eligy_prfl_id);
397 End If;
398
399 End Loop;
400 end if;
401
402 end derivable_factor_handler;
403 --
404 end ben_derivable_factor;
[Home] [Help]