4 /*
1 package body ben_derivable_factor as
2 /* $Header: bendrvft.pkb 120.0.12020000.2 2012/11/15 11:33:09 amnaraya 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
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
30 the value is got by the
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,
31 call to fnd_installation.get_app_info
32 15-Nov-2012 amnaraya 120.0.12020000.2 Bug 15876152: Changed all_tab_columns
33 reference to dba_tab_columns using synonyms
34
35
36 */
37 --------------------------------------------------------------------------------
38 g_package varchar2(80) := 'ben_derivable_factor.';
39 --
40 procedure eligy_prfl_handler
41 (p_event in varchar2,
42 p_table_name in varchar2,
43 p_col_name in varchar2,
44 p_col_id in number)is
45 --
46 l_proc varchar2(80) := g_package||'eligy prfl handler';
47 l_strg varchar2(32000) ;
48 l_dummy varchar2(1);
49 l_status varchar2(1);
50 l_industry varchar2(1);
51 l_application_short_name varchar2(30) := 'BEN';
52 l_oracle_schema varchar2(30);
53 l_return boolean;
54
55
56
57 --
58 --Bug 15876152: Changed the cursor select statement
59 cursor c1 (p_tab_name varchar2, p_col_name varchar2,p_oracle_schema varchar2) is
60 select null
61 from dba_tab_columns tab, user_synonyms syn
62 where syn.synonym_name = upper(p_tab_name)
63 and tab.column_name = upper(p_col_name)
64 and tab.owner = syn.table_owner
65 and tab.table_name = syn.table_name
66 and tab.owner=upper(p_oracle_schema);
67 --
68 begin
69 --
70 hr_utility.set_location('Entering '||l_proc,10);
71 --
72 --
73 -- Parameter validation
74 --
75 hr_api.mandatory_arg_error(p_api_name => l_proc,
76 p_argument => 'event',
77 p_argument_value => p_event);
78 --
79 hr_api.mandatory_arg_error(p_api_name => l_proc,
80 p_argument => 'table_name',
81 p_argument_value => p_table_name);
82 --
83 hr_api.mandatory_arg_error(p_api_name => l_proc,
84 p_argument => 'column name',
85 p_argument_value => p_col_name);
86 --
87 hr_api.mandatory_arg_error(p_api_name => l_proc,
88 p_argument => 'column_id',
89 p_argument_value => p_col_id);
90
91 -- Bug 3431740 Parameter p_oracle_schema added to cursor c1, the value is got by the
92 -- following call
93 l_return := fnd_installation.get_app_info(application_short_name => l_application_short_name,
94 status => l_status,
95 industry => l_industry,
96 oracle_schema => l_oracle_schema);
97 --
98 --
99 -- Check operation is valid
100 --
101 if p_event not in ('CREATE','DELETE') then
102 --
103 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
104 fnd_message.raise_error;
105 --
106 end if;
107 --
108 open c1(p_table_name, p_col_name,l_oracle_schema );
109 fetch c1 into l_dummy;
110 if c1%notfound then
111 close c1;
112 fnd_message.set_name('BEN','BEN_93388_NO_TAB_COL');
113 fnd_message.raise_error;
114 end if;
115 close c1;
116 --
117 --
118 --
119 if p_event = 'CREATE' then
120 --
121 -- rchase bug 1568561 - added paren pair around ors
122 hr_utility.set_location('Create event ',10);
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,
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,
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,
148 ben_prtn_elig_prfl_f cep
149 where epa.'||p_col_name||'= :8 and epa.prtn_elig_id = cep.prtn_elig_id and
153 elsif p_event = 'DELETE' then
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;
152 --
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,
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,
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,
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;
182 --
183 end if;
184 --
185 hr_utility.set_location('Leaving '||l_proc,10);
186 --
187 end eligy_prfl_handler;
188 -----------------------------------------------------------------------
189 procedure derivable_factor_handler
190 (p_event in varchar2,
191 p_eligy_prfl_id in number)is
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 --
201
202 begin
203 --
204 hr_utility.set_location('Entering '||l_proc,10);
205 --
206 --
207 -- Parameter validation
208 --
209 hr_api.mandatory_arg_error(p_api_name => l_proc,
210 p_argument => 'event',
211 p_argument_value => p_event);
212 hr_api.mandatory_arg_error(p_api_name => l_proc,
213 p_argument => 'eligy prfl id',
214 p_argument_value => p_eligy_prfl_id);
215 --
216 if p_event not in ('CREATE','DELETE') then
217 --
218 fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
219 fnd_message.raise_error;
220 --
221 end if;
222
223
224 if p_event = 'CREATE' then
225
226 For i in c1 loop
227 If i.pgm_id is not null then
228 update ben_pgm_f set drvbl_fctr_prtn_elig_flag = 'Y'
229 where pgm_id = i.pgm_id;
230 end if;
231
232 If i.pl_id is not null then
233 update ben_pl_f set drvbl_fctr_prtn_elig_flag = 'Y'
234 where pl_id = i.pl_id;
235 end if;
236
237 If i.plip_id is not null then
238 update ben_plip_f set drvbl_fctr_prtn_elig_flag = 'Y'
239 where plip_id = i.plip_id;
240 end if;
241
242 If i.ptip_id is not null then
243 update ben_ptip_f set drvbl_fctr_prtn_elig_flag = 'Y'
244 where ptip_id = i.ptip_id;
245 end if;
246
247 If i.oipl_id is not null then
248 update ben_oipl_f set drvbl_fctr_prtn_elig_flag = 'Y'
249 where oipl_id = i.oipl_id;
250 end if;
251 End Loop;
252 elsif p_event = 'DELETE' then
253 For i in c1 Loop
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,
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
272 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
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,
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;
283 --
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,
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,
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,
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;
313 --
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,
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,
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,
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;
343 --
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,
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
359 cep.eligy_prfl_id = ecl.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
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,
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;
373 --
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,
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,
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,
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;
403
404 End Loop;
405 end if;
406
407 end derivable_factor_handler;
408 --
409 end ben_derivable_factor;
[Home] [Help]