[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIGIBILITY_RULE_API
Source
1 Package Body ben_ELIGIBILITY_RULE_api as
2 /* $Header: becerapi.pkb 120.0 2005/05/28 01:00:28 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_ELIGIBILITY_RULE_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_ELIGIBILITY_RULE >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ELIGIBILITY_RULE
13 (p_validate in boolean default false
14 ,p_prtn_eligy_rl_id out nocopy number
15 ,p_effective_start_date out nocopy date
16 ,p_effective_end_date out nocopy date
17 ,p_business_group_id in number default null
18 ,p_prtn_elig_id in number default null
19 ,p_formula_id in number default null
20 ,p_drvbl_fctr_apls_flag in varchar2 default null
21 ,p_mndtry_flag in varchar2 default null
22 ,p_ordr_to_aply_num in number default null
23 ,p_cer_attribute_category in varchar2 default null
24 ,p_cer_attribute1 in varchar2 default null
25 ,p_cer_attribute2 in varchar2 default null
26 ,p_cer_attribute3 in varchar2 default null
27 ,p_cer_attribute4 in varchar2 default null
28 ,p_cer_attribute5 in varchar2 default null
29 ,p_cer_attribute6 in varchar2 default null
30 ,p_cer_attribute7 in varchar2 default null
31 ,p_cer_attribute8 in varchar2 default null
32 ,p_cer_attribute9 in varchar2 default null
33 ,p_cer_attribute10 in varchar2 default null
34 ,p_cer_attribute11 in varchar2 default null
35 ,p_cer_attribute12 in varchar2 default null
36 ,p_cer_attribute13 in varchar2 default null
37 ,p_cer_attribute14 in varchar2 default null
38 ,p_cer_attribute15 in varchar2 default null
39 ,p_cer_attribute16 in varchar2 default null
40 ,p_cer_attribute17 in varchar2 default null
41 ,p_cer_attribute18 in varchar2 default null
42 ,p_cer_attribute19 in varchar2 default null
43 ,p_cer_attribute20 in varchar2 default null
44 ,p_cer_attribute21 in varchar2 default null
45 ,p_cer_attribute22 in varchar2 default null
46 ,p_cer_attribute23 in varchar2 default null
47 ,p_cer_attribute24 in varchar2 default null
48 ,p_cer_attribute25 in varchar2 default null
49 ,p_cer_attribute26 in varchar2 default null
50 ,p_cer_attribute27 in varchar2 default null
51 ,p_cer_attribute28 in varchar2 default null
52 ,p_cer_attribute29 in varchar2 default null
53 ,p_cer_attribute30 in varchar2 default null
54 ,p_pgm_id in number default null
55 ,p_pl_id in number default null
56 ,p_ptip_id in number default null
57 ,p_plip_id in number default null
58 ,p_oipl_id in number default null
59 ,p_object_version_number out nocopy number
60 ,p_effective_date in date
61 ) is
62 --
63 -- Declare cursors and local variables
64 --
65 l_prtn_elig_id number;
66 l_prtn_eligy_rl_id ben_prtn_eligy_rl_f.prtn_eligy_rl_id%TYPE;
67 l_effective_start_date ben_prtn_eligy_rl_f.effective_start_date%TYPE;
68 l_effective_end_date ben_prtn_eligy_rl_f.effective_end_date%TYPE;
69 l_proc varchar2(72) := g_package||'create_ELIGIBILITY_RULE';
70 l_object_version_number ben_prtn_eligy_rl_f.object_version_number%TYPE;
71 --
72 begin
73 --
74 hr_utility.set_location('Entering:'|| l_proc, 10);
75 --
76 -- Issue a savepoint if operating in validation only mode
77 --
78 savepoint create_ELIGIBILITY_RULE;
79 --
80 hr_utility.set_location(l_proc, 20);
81 --
82 -- Process Logic
83 --
84 if p_prtn_elig_id is null then
85 ben_prtn_elig_prfl_api.insert_prtn_elig
86 (
87 p_pgm_id => p_pgm_id
88 ,p_pl_id => p_pl_id
89 ,p_plip_id => p_plip_id
90 ,p_ptip_id => p_ptip_id
91 ,p_oipl_id => p_oipl_id
92 ,p_effective_date => p_effective_date
93 ,p_business_group_id => p_business_group_id
94 ,p_prtn_elig_id => l_prtn_elig_id
95 );
96 else
97 l_prtn_elig_id := p_prtn_elig_id;
98 end if;
99
100 begin
101 --
102 -- Start of API User Hook for the before hook of create_ELIGIBILITY_RULE
103 --
104 ben_ELIGIBILITY_RULE_bk1.create_ELIGIBILITY_RULE_b
105 (
106 p_business_group_id => p_business_group_id
107 ,p_prtn_elig_id => l_prtn_elig_id
108 ,p_formula_id => p_formula_id
109 ,p_drvbl_fctr_apls_flag => p_drvbl_fctr_apls_flag
110 ,p_mndtry_flag => p_mndtry_flag
111 ,p_ordr_to_aply_num => p_ordr_to_aply_num
112 ,p_cer_attribute_category => p_cer_attribute_category
113 ,p_cer_attribute1 => p_cer_attribute1
114 ,p_cer_attribute2 => p_cer_attribute2
115 ,p_cer_attribute3 => p_cer_attribute3
116 ,p_cer_attribute4 => p_cer_attribute4
117 ,p_cer_attribute5 => p_cer_attribute5
118 ,p_cer_attribute6 => p_cer_attribute6
119 ,p_cer_attribute7 => p_cer_attribute7
120 ,p_cer_attribute8 => p_cer_attribute8
121 ,p_cer_attribute9 => p_cer_attribute9
122 ,p_cer_attribute10 => p_cer_attribute10
123 ,p_cer_attribute11 => p_cer_attribute11
124 ,p_cer_attribute12 => p_cer_attribute12
125 ,p_cer_attribute13 => p_cer_attribute13
126 ,p_cer_attribute14 => p_cer_attribute14
127 ,p_cer_attribute15 => p_cer_attribute15
128 ,p_cer_attribute16 => p_cer_attribute16
129 ,p_cer_attribute17 => p_cer_attribute17
130 ,p_cer_attribute18 => p_cer_attribute18
131 ,p_cer_attribute19 => p_cer_attribute19
132 ,p_cer_attribute20 => p_cer_attribute20
133 ,p_cer_attribute21 => p_cer_attribute21
134 ,p_cer_attribute22 => p_cer_attribute22
135 ,p_cer_attribute23 => p_cer_attribute23
136 ,p_cer_attribute24 => p_cer_attribute24
137 ,p_cer_attribute25 => p_cer_attribute25
138 ,p_cer_attribute26 => p_cer_attribute26
139 ,p_cer_attribute27 => p_cer_attribute27
140 ,p_cer_attribute28 => p_cer_attribute28
141 ,p_cer_attribute29 => p_cer_attribute29
142 ,p_cer_attribute30 => p_cer_attribute30
143 ,p_effective_date => trunc(p_effective_date)
144 );
145 exception
146 when hr_api.cannot_find_prog_unit then
147 hr_api.cannot_find_prog_unit_error
148 (
149 p_module_name => 'CREATE_ELIGIBILITY_RULE'
150 ,p_hook_type => 'BP'
151 );
152 --
153 -- End of API User Hook for the before hook of create_ELIGIBILITY_RULE
154 --
155 end;
156 --
157 ben_cer_ins.ins
158 (
159 p_prtn_eligy_rl_id => l_prtn_eligy_rl_id
160 ,p_effective_start_date => l_effective_start_date
161 ,p_effective_end_date => l_effective_end_date
162 ,p_business_group_id => p_business_group_id
163 ,p_prtn_elig_id => l_prtn_elig_id
164 ,p_formula_id => p_formula_id
165 ,p_drvbl_fctr_apls_flag => p_drvbl_fctr_apls_flag
166 ,p_mndtry_flag => p_mndtry_flag
167 ,p_ordr_to_aply_num => p_ordr_to_aply_num
168 ,p_cer_attribute_category => p_cer_attribute_category
169 ,p_cer_attribute1 => p_cer_attribute1
170 ,p_cer_attribute2 => p_cer_attribute2
171 ,p_cer_attribute3 => p_cer_attribute3
172 ,p_cer_attribute4 => p_cer_attribute4
173 ,p_cer_attribute5 => p_cer_attribute5
174 ,p_cer_attribute6 => p_cer_attribute6
175 ,p_cer_attribute7 => p_cer_attribute7
176 ,p_cer_attribute8 => p_cer_attribute8
177 ,p_cer_attribute9 => p_cer_attribute9
178 ,p_cer_attribute10 => p_cer_attribute10
179 ,p_cer_attribute11 => p_cer_attribute11
180 ,p_cer_attribute12 => p_cer_attribute12
181 ,p_cer_attribute13 => p_cer_attribute13
182 ,p_cer_attribute14 => p_cer_attribute14
183 ,p_cer_attribute15 => p_cer_attribute15
184 ,p_cer_attribute16 => p_cer_attribute16
185 ,p_cer_attribute17 => p_cer_attribute17
186 ,p_cer_attribute18 => p_cer_attribute18
187 ,p_cer_attribute19 => p_cer_attribute19
188 ,p_cer_attribute20 => p_cer_attribute20
189 ,p_cer_attribute21 => p_cer_attribute21
190 ,p_cer_attribute22 => p_cer_attribute22
191 ,p_cer_attribute23 => p_cer_attribute23
192 ,p_cer_attribute24 => p_cer_attribute24
193 ,p_cer_attribute25 => p_cer_attribute25
194 ,p_cer_attribute26 => p_cer_attribute26
195 ,p_cer_attribute27 => p_cer_attribute27
196 ,p_cer_attribute28 => p_cer_attribute28
197 ,p_cer_attribute29 => p_cer_attribute29
198 ,p_cer_attribute30 => p_cer_attribute30
199 ,p_object_version_number => l_object_version_number
200 ,p_effective_date => trunc(p_effective_date)
201 );
202 --
203 begin
204 --
205 -- Start of API User Hook for the after hook of create_ELIGIBILITY_RULE
206 --
207 ben_ELIGIBILITY_RULE_bk1.create_ELIGIBILITY_RULE_a
208 (
209 p_prtn_eligy_rl_id => l_prtn_eligy_rl_id
210 ,p_effective_start_date => l_effective_start_date
211 ,p_effective_end_date => l_effective_end_date
212 ,p_business_group_id => p_business_group_id
213 ,p_prtn_elig_id => l_prtn_elig_id
214 ,p_formula_id => p_formula_id
215 ,p_drvbl_fctr_apls_flag => p_drvbl_fctr_apls_flag
216 ,p_mndtry_flag => p_mndtry_flag
217 ,p_ordr_to_aply_num => p_ordr_to_aply_num
218 ,p_cer_attribute_category => p_cer_attribute_category
219 ,p_cer_attribute1 => p_cer_attribute1
220 ,p_cer_attribute2 => p_cer_attribute2
221 ,p_cer_attribute3 => p_cer_attribute3
222 ,p_cer_attribute4 => p_cer_attribute4
223 ,p_cer_attribute5 => p_cer_attribute5
224 ,p_cer_attribute6 => p_cer_attribute6
225 ,p_cer_attribute7 => p_cer_attribute7
226 ,p_cer_attribute8 => p_cer_attribute8
227 ,p_cer_attribute9 => p_cer_attribute9
228 ,p_cer_attribute10 => p_cer_attribute10
229 ,p_cer_attribute11 => p_cer_attribute11
230 ,p_cer_attribute12 => p_cer_attribute12
231 ,p_cer_attribute13 => p_cer_attribute13
232 ,p_cer_attribute14 => p_cer_attribute14
233 ,p_cer_attribute15 => p_cer_attribute15
234 ,p_cer_attribute16 => p_cer_attribute16
235 ,p_cer_attribute17 => p_cer_attribute17
236 ,p_cer_attribute18 => p_cer_attribute18
237 ,p_cer_attribute19 => p_cer_attribute19
238 ,p_cer_attribute20 => p_cer_attribute20
239 ,p_cer_attribute21 => p_cer_attribute21
240 ,p_cer_attribute22 => p_cer_attribute22
241 ,p_cer_attribute23 => p_cer_attribute23
242 ,p_cer_attribute24 => p_cer_attribute24
243 ,p_cer_attribute25 => p_cer_attribute25
244 ,p_cer_attribute26 => p_cer_attribute26
245 ,p_cer_attribute27 => p_cer_attribute27
246 ,p_cer_attribute28 => p_cer_attribute28
247 ,p_cer_attribute29 => p_cer_attribute29
248 ,p_cer_attribute30 => p_cer_attribute30
249 ,p_object_version_number => l_object_version_number
250 ,p_effective_date => trunc(p_effective_date)
251 );
252 exception
253 when hr_api.cannot_find_prog_unit then
254 hr_api.cannot_find_prog_unit_error
255 (p_module_name => 'CREATE_ELIGIBILITY_RULE'
256 ,p_hook_type => 'AP'
257 );
258 --
259 -- End of API User Hook for the after hook of create_ELIGIBILITY_RULE
260 --
261 end;
262 --
263 --update statements
264 --updates elig_apls_flag to 'Y' in ben_pgm_f table.
265 update ben_pgm_f c
266 set c.elig_apls_flag = 'Y'
267 where exists
268 (select a.pgm_id
269 from ben_prtn_elig_f a,
270 ben_prtn_eligy_rl_f b
271 where b.prtn_eligy_rl_id = l_prtn_eligy_rl_id
272 and a.prtn_elig_id = b.prtn_elig_id
273 and a.pgm_id = c.pgm_id);
274 --
275 --updates elig_apls_flag to 'Y' in ben_plip_f table.
276 update ben_plip_f c
277 set c.elig_apls_flag = 'Y'
278 where exists
279 (select a.plip_id
280 from ben_prtn_elig_f a,
281 ben_prtn_eligy_rl_f b
282 where b.prtn_eligy_rl_id = l_prtn_eligy_rl_id
283 and a.prtn_elig_id = b.prtn_elig_id
284 and a.plip_id = c.plip_id);
285 --
286 --updates elig_apls_flag to 'Y' in ben_ptip_f table.
287 update ben_ptip_f c
288 set c.elig_apls_flag = 'Y'
289 where exists
290 (select a.ptip_id
291 from ben_prtn_elig_f a,
292 ben_prtn_eligy_rl_f b
293 where b.prtn_eligy_rl_id = l_prtn_eligy_rl_id
294 and a.prtn_elig_id = b.prtn_elig_id
295 and a.ptip_id = c.ptip_id);
296 --
297 --updates elig_apls_flag to 'Y' in ben_pl_f table.
298 update ben_pl_f c
299 set c.elig_apls_flag = 'Y'
300 where exists
301 (select a.pl_id
302 from ben_prtn_elig_f a,
303 ben_prtn_eligy_rl_f b
304 where b.prtn_eligy_rl_id = l_prtn_eligy_rl_id
305 and a.prtn_elig_id = b.prtn_elig_id
306 and a.pl_id = c.pl_id);
307 --
308 --updates elig_apls_flag to 'Y' in ben_oipl_f table.
309 update ben_oipl_f c
310 set c.elig_apls_flag = 'Y'
311 where exists
312 (select a.oipl_id
313 from ben_prtn_elig_f a,
314 ben_prtn_eligy_rl_f b
315 where b.prtn_eligy_rl_id = l_prtn_eligy_rl_id
316 and a.prtn_elig_id = b.prtn_elig_id
317 and a.oipl_id = c.oipl_id);
318 --
319 hr_utility.set_location(l_proc, 60);
320 --
321 -- When in validation only mode raise the Validate_Enabled exception
322 --
323 if p_validate then
324 raise hr_api.validate_enabled;
325 end if;
326 --
327 -- Set all output arguments
328 --
329 p_prtn_eligy_rl_id := l_prtn_eligy_rl_id;
330 p_effective_start_date := l_effective_start_date;
331 p_effective_end_date := l_effective_end_date;
332 p_object_version_number := l_object_version_number;
333 --
334 hr_utility.set_location(' Leaving:'||l_proc, 70);
335 --
336 exception
337 --
338 when hr_api.validate_enabled then
339 --
340 -- As the Validate_Enabled exception has been raised
341 -- we must rollback to the savepoint
342 --
343 ROLLBACK TO create_ELIGIBILITY_RULE;
344 --
345 -- Only set output warning arguments
346 -- (Any key or derived arguments must be set to null
347 -- when validation only mode is being used.)
348 --
349 p_prtn_eligy_rl_id := null;
350 p_effective_start_date := null;
351 p_effective_end_date := null;
352 p_object_version_number := null;
353 hr_utility.set_location(' Leaving:'||l_proc, 80);
354 --
355 when others then
356 --
357 -- A validation or unexpected error has occured
358 --
359 ROLLBACK TO create_ELIGIBILITY_RULE;
360 raise;
361 --
362 end create_ELIGIBILITY_RULE;
363 -- ----------------------------------------------------------------------------
364 -- |------------------------< update_ELIGIBILITY_RULE >--- ------------------|
365 -- ----------------------------------------------------------------------------
366 --
367 procedure update_ELIGIBILITY_RULE
368 (p_validate in boolean default false
369 ,p_prtn_eligy_rl_id in number
370 ,p_effective_start_date out nocopy date
371 ,p_effective_end_date out nocopy date
372 ,p_business_group_id in number default hr_api.g_number
373 ,p_prtn_elig_id in number default hr_api.g_number
374 ,p_formula_id in number default hr_api.g_number
375 ,p_drvbl_fctr_apls_flag in varchar2 default hr_api.g_varchar2
376 ,p_mndtry_flag in varchar2 default hr_api.g_varchar2
377 ,p_ordr_to_aply_num in number default hr_api.g_number
378 ,p_cer_attribute_category in varchar2 default hr_api.g_varchar2
379 ,p_cer_attribute1 in varchar2 default hr_api.g_varchar2
380 ,p_cer_attribute2 in varchar2 default hr_api.g_varchar2
381 ,p_cer_attribute3 in varchar2 default hr_api.g_varchar2
382 ,p_cer_attribute4 in varchar2 default hr_api.g_varchar2
383 ,p_cer_attribute5 in varchar2 default hr_api.g_varchar2
384 ,p_cer_attribute6 in varchar2 default hr_api.g_varchar2
385 ,p_cer_attribute7 in varchar2 default hr_api.g_varchar2
386 ,p_cer_attribute8 in varchar2 default hr_api.g_varchar2
387 ,p_cer_attribute9 in varchar2 default hr_api.g_varchar2
388 ,p_cer_attribute10 in varchar2 default hr_api.g_varchar2
389 ,p_cer_attribute11 in varchar2 default hr_api.g_varchar2
390 ,p_cer_attribute12 in varchar2 default hr_api.g_varchar2
391 ,p_cer_attribute13 in varchar2 default hr_api.g_varchar2
392 ,p_cer_attribute14 in varchar2 default hr_api.g_varchar2
393 ,p_cer_attribute15 in varchar2 default hr_api.g_varchar2
394 ,p_cer_attribute16 in varchar2 default hr_api.g_varchar2
395 ,p_cer_attribute17 in varchar2 default hr_api.g_varchar2
396 ,p_cer_attribute18 in varchar2 default hr_api.g_varchar2
397 ,p_cer_attribute19 in varchar2 default hr_api.g_varchar2
398 ,p_cer_attribute20 in varchar2 default hr_api.g_varchar2
399 ,p_cer_attribute21 in varchar2 default hr_api.g_varchar2
400 ,p_cer_attribute22 in varchar2 default hr_api.g_varchar2
401 ,p_cer_attribute23 in varchar2 default hr_api.g_varchar2
402 ,p_cer_attribute24 in varchar2 default hr_api.g_varchar2
403 ,p_cer_attribute25 in varchar2 default hr_api.g_varchar2
404 ,p_cer_attribute26 in varchar2 default hr_api.g_varchar2
405 ,p_cer_attribute27 in varchar2 default hr_api.g_varchar2
406 ,p_cer_attribute28 in varchar2 default hr_api.g_varchar2
407 ,p_cer_attribute29 in varchar2 default hr_api.g_varchar2
408 ,p_cer_attribute30 in varchar2 default hr_api.g_varchar2
409 ,p_object_version_number in out nocopy number
410 ,p_effective_date in date
411 ,p_datetrack_mode in varchar2
412 ) is
413 --
414 -- Declare cursors and local variables
415 --
416 l_proc varchar2(72) := g_package||'update_ELIGIBILITY_RULE';
417 l_object_version_number ben_prtn_eligy_rl_f.object_version_number%TYPE;
418 l_effective_start_date ben_prtn_eligy_rl_f.effective_start_date%TYPE;
419 l_effective_end_date ben_prtn_eligy_rl_f.effective_end_date%TYPE;
420 --
421 begin
422 --
423 hr_utility.set_location('Entering:'|| l_proc, 10);
424 --
425 -- Issue a savepoint if operating in validation only mode
426 --
427 savepoint update_ELIGIBILITY_RULE;
428 --
429 hr_utility.set_location(l_proc, 20);
430 --
431 -- Process Logic
432 --
433 l_object_version_number := p_object_version_number;
434 --
435 begin
436 --
437 -- Start of API User Hook for the before hook of update_ELIGIBILITY_RULE
438 --
439 ben_ELIGIBILITY_RULE_bk2.update_ELIGIBILITY_RULE_b
440 (
441 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
442 ,p_business_group_id => p_business_group_id
443 ,p_prtn_elig_id => p_prtn_elig_id
444 ,p_formula_id => p_formula_id
445 ,p_drvbl_fctr_apls_flag => p_drvbl_fctr_apls_flag
446 ,p_mndtry_flag => p_mndtry_flag
447 ,p_ordr_to_aply_num => p_ordr_to_aply_num
448 ,p_cer_attribute_category => p_cer_attribute_category
449 ,p_cer_attribute1 => p_cer_attribute1
450 ,p_cer_attribute2 => p_cer_attribute2
451 ,p_cer_attribute3 => p_cer_attribute3
452 ,p_cer_attribute4 => p_cer_attribute4
453 ,p_cer_attribute5 => p_cer_attribute5
454 ,p_cer_attribute6 => p_cer_attribute6
455 ,p_cer_attribute7 => p_cer_attribute7
456 ,p_cer_attribute8 => p_cer_attribute8
457 ,p_cer_attribute9 => p_cer_attribute9
458 ,p_cer_attribute10 => p_cer_attribute10
459 ,p_cer_attribute11 => p_cer_attribute11
460 ,p_cer_attribute12 => p_cer_attribute12
461 ,p_cer_attribute13 => p_cer_attribute13
462 ,p_cer_attribute14 => p_cer_attribute14
463 ,p_cer_attribute15 => p_cer_attribute15
464 ,p_cer_attribute16 => p_cer_attribute16
465 ,p_cer_attribute17 => p_cer_attribute17
466 ,p_cer_attribute18 => p_cer_attribute18
467 ,p_cer_attribute19 => p_cer_attribute19
468 ,p_cer_attribute20 => p_cer_attribute20
469 ,p_cer_attribute21 => p_cer_attribute21
470 ,p_cer_attribute22 => p_cer_attribute22
471 ,p_cer_attribute23 => p_cer_attribute23
472 ,p_cer_attribute24 => p_cer_attribute24
473 ,p_cer_attribute25 => p_cer_attribute25
474 ,p_cer_attribute26 => p_cer_attribute26
475 ,p_cer_attribute27 => p_cer_attribute27
476 ,p_cer_attribute28 => p_cer_attribute28
477 ,p_cer_attribute29 => p_cer_attribute29
478 ,p_cer_attribute30 => p_cer_attribute30
479 ,p_object_version_number => p_object_version_number
480 ,p_effective_date => trunc(p_effective_date)
481 ,p_datetrack_mode => p_datetrack_mode
482 );
483 exception
484 when hr_api.cannot_find_prog_unit then
485 hr_api.cannot_find_prog_unit_error
486 (p_module_name => 'UPDATE_ELIGIBILITY_RULE'
487 ,p_hook_type => 'BP'
488 );
489 --
490 -- End of API User Hook for the before hook of update_ELIGIBILITY_RULE
491 --
492 end;
493 --
494 ben_cer_upd.upd
495 (
496 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
497 ,p_effective_start_date => l_effective_start_date
498 ,p_effective_end_date => l_effective_end_date
499 ,p_business_group_id => p_business_group_id
500 ,p_prtn_elig_id => p_prtn_elig_id
501 ,p_formula_id => p_formula_id
502 ,p_drvbl_fctr_apls_flag => p_drvbl_fctr_apls_flag
503 ,p_mndtry_flag => p_mndtry_flag
504 ,p_ordr_to_aply_num => p_ordr_to_aply_num
505 ,p_cer_attribute_category => p_cer_attribute_category
506 ,p_cer_attribute1 => p_cer_attribute1
507 ,p_cer_attribute2 => p_cer_attribute2
508 ,p_cer_attribute3 => p_cer_attribute3
509 ,p_cer_attribute4 => p_cer_attribute4
510 ,p_cer_attribute5 => p_cer_attribute5
511 ,p_cer_attribute6 => p_cer_attribute6
512 ,p_cer_attribute7 => p_cer_attribute7
513 ,p_cer_attribute8 => p_cer_attribute8
514 ,p_cer_attribute9 => p_cer_attribute9
515 ,p_cer_attribute10 => p_cer_attribute10
516 ,p_cer_attribute11 => p_cer_attribute11
517 ,p_cer_attribute12 => p_cer_attribute12
518 ,p_cer_attribute13 => p_cer_attribute13
519 ,p_cer_attribute14 => p_cer_attribute14
520 ,p_cer_attribute15 => p_cer_attribute15
521 ,p_cer_attribute16 => p_cer_attribute16
522 ,p_cer_attribute17 => p_cer_attribute17
523 ,p_cer_attribute18 => p_cer_attribute18
524 ,p_cer_attribute19 => p_cer_attribute19
525 ,p_cer_attribute20 => p_cer_attribute20
526 ,p_cer_attribute21 => p_cer_attribute21
527 ,p_cer_attribute22 => p_cer_attribute22
528 ,p_cer_attribute23 => p_cer_attribute23
529 ,p_cer_attribute24 => p_cer_attribute24
530 ,p_cer_attribute25 => p_cer_attribute25
531 ,p_cer_attribute26 => p_cer_attribute26
532 ,p_cer_attribute27 => p_cer_attribute27
533 ,p_cer_attribute28 => p_cer_attribute28
534 ,p_cer_attribute29 => p_cer_attribute29
535 ,p_cer_attribute30 => p_cer_attribute30
536 ,p_object_version_number => l_object_version_number
537 ,p_effective_date => trunc(p_effective_date)
538 ,p_datetrack_mode => p_datetrack_mode
539 );
540 --
541 begin
542 --
543 -- Start of API User Hook for the after hook of update_ELIGIBILITY_RULE
544 --
545 ben_ELIGIBILITY_RULE_bk2.update_ELIGIBILITY_RULE_a
546 (
547 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
548 ,p_effective_start_date => l_effective_start_date
549 ,p_effective_end_date => l_effective_end_date
550 ,p_business_group_id => p_business_group_id
551 ,p_prtn_elig_id => p_prtn_elig_id
552 ,p_formula_id => p_formula_id
553 ,p_drvbl_fctr_apls_flag => p_drvbl_fctr_apls_flag
554 ,p_mndtry_flag => p_mndtry_flag
555 ,p_ordr_to_aply_num => p_ordr_to_aply_num
556 ,p_cer_attribute_category => p_cer_attribute_category
557 ,p_cer_attribute1 => p_cer_attribute1
558 ,p_cer_attribute2 => p_cer_attribute2
559 ,p_cer_attribute3 => p_cer_attribute3
560 ,p_cer_attribute4 => p_cer_attribute4
561 ,p_cer_attribute5 => p_cer_attribute5
562 ,p_cer_attribute6 => p_cer_attribute6
563 ,p_cer_attribute7 => p_cer_attribute7
564 ,p_cer_attribute8 => p_cer_attribute8
565 ,p_cer_attribute9 => p_cer_attribute9
566 ,p_cer_attribute10 => p_cer_attribute10
567 ,p_cer_attribute11 => p_cer_attribute11
568 ,p_cer_attribute12 => p_cer_attribute12
569 ,p_cer_attribute13 => p_cer_attribute13
570 ,p_cer_attribute14 => p_cer_attribute14
571 ,p_cer_attribute15 => p_cer_attribute15
572 ,p_cer_attribute16 => p_cer_attribute16
573 ,p_cer_attribute17 => p_cer_attribute17
574 ,p_cer_attribute18 => p_cer_attribute18
575 ,p_cer_attribute19 => p_cer_attribute19
576 ,p_cer_attribute20 => p_cer_attribute20
577 ,p_cer_attribute21 => p_cer_attribute21
578 ,p_cer_attribute22 => p_cer_attribute22
579 ,p_cer_attribute23 => p_cer_attribute23
580 ,p_cer_attribute24 => p_cer_attribute24
581 ,p_cer_attribute25 => p_cer_attribute25
582 ,p_cer_attribute26 => p_cer_attribute26
583 ,p_cer_attribute27 => p_cer_attribute27
584 ,p_cer_attribute28 => p_cer_attribute28
585 ,p_cer_attribute29 => p_cer_attribute29
586 ,p_cer_attribute30 => p_cer_attribute30
587 ,p_object_version_number => l_object_version_number
588 ,p_effective_date => trunc(p_effective_date)
589 ,p_datetrack_mode => p_datetrack_mode
590 );
591 exception
592 when hr_api.cannot_find_prog_unit then
593 hr_api.cannot_find_prog_unit_error
594 (p_module_name => 'UPDATE_ELIGIBILITY_RULE'
595 ,p_hook_type => 'AP'
596 );
597 --
598 -- End of API User Hook for the after hook of update_ELIGIBILITY_RULE
599 --
600 end;
601 --
602 hr_utility.set_location(l_proc, 60);
603 --
604 -- When in validation only mode raise the Validate_Enabled exception
605 --
606 if p_validate then
607 raise hr_api.validate_enabled;
608 end if;
609 --
610 -- Set all output arguments
611 --
612 p_object_version_number := l_object_version_number;
613 p_effective_start_date := l_effective_start_date;
614 p_effective_end_date := l_effective_end_date;
615 --
616 hr_utility.set_location(' Leaving:'||l_proc, 70);
617 --
618 exception
619 --
620 when hr_api.validate_enabled then
621 --
622 -- As the Validate_Enabled exception has been raised
623 -- we must rollback to the savepoint
624 --
625 ROLLBACK TO update_ELIGIBILITY_RULE;
626 --
627 -- Only set output warning arguments
628 -- (Any key or derived arguments must be set to null
629 -- when validation only mode is being used.)
630 --
631 hr_utility.set_location(' Leaving:'||l_proc, 80);
632 --
633 when others then
634 --
635 -- A validation or unexpected error has occured
636 --
637 ROLLBACK TO update_ELIGIBILITY_RULE;
638 p_object_version_number := l_object_version_number;
639 p_effective_start_date := null;
640 p_effective_end_date := null;
641 raise;
642 --
643 end update_ELIGIBILITY_RULE;
644 -- ----------------------------------------------------------------------------
645 -- |------------------------< delete_ELIGIBILITY_RULE >----------------------|
646 -- ----------------------------------------------------------------------------
647 --
648 procedure delete_ELIGIBILITY_RULE
649 (p_validate in boolean default false
650 ,p_prtn_eligy_rl_id in number
651 ,p_effective_start_date out nocopy date
652 ,p_effective_end_date out nocopy date
653 ,p_object_version_number in out nocopy number
654 ,p_effective_date in date
655 ,p_datetrack_mode in varchar2
656 ) is
657 --
658 -- Declare cursors and local variables
659 --
660 l_proc varchar2(72) := g_package||'update_ELIGIBILITY_RULE';
661 l_object_version_number ben_prtn_eligy_rl_f.object_version_number%TYPE;
662 l_effective_start_date ben_prtn_eligy_rl_f.effective_start_date%TYPE;
663 l_effective_end_date ben_prtn_eligy_rl_f.effective_end_date%TYPE;
664 --
665 cursor c1 is
666 select pgm_id,
667 ptip_id,
668 plip_id,
669 pl_id,
670 oipl_id
671 from ben_prtn_elig_f a,
672 ben_prtn_eligy_rl_f b
673 where a.prtn_elig_id = b.prtn_elig_id
674 and b.prtn_eligy_rl_id = p_prtn_eligy_rl_id;
675 --
676 l_c1 c1%rowtype;
677 --
678 begin
679 --
680 open c1;
681 fetch c1 into l_c1;
682 close c1;
683 --
684 hr_utility.set_location('Entering:'|| l_proc, 10);
685 --
686 -- Issue a savepoint if operating in validation only mode
687 --
688 savepoint delete_ELIGIBILITY_RULE;
689 --
690 hr_utility.set_location(l_proc, 20);
691 --
692 -- Process Logic
693 --
694 l_object_version_number := p_object_version_number;
695 --
696 --
697 begin
698 --
699 -- Start of API User Hook for the before hook of delete_ELIGIBILITY_RULE
700 --
701 ben_ELIGIBILITY_RULE_bk3.delete_ELIGIBILITY_RULE_b
702 (
703 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
704 ,p_object_version_number => p_object_version_number
705 ,p_effective_date => trunc(p_effective_date)
706 ,p_datetrack_mode => p_datetrack_mode
707 );
708 exception
709 when hr_api.cannot_find_prog_unit then
710 hr_api.cannot_find_prog_unit_error
711 (p_module_name => 'DELETE_ELIGIBILITY_RULE'
712 ,p_hook_type => 'BP'
713 );
714 --
715 -- End of API User Hook for the before hook of delete_ELIGIBILITY_RULE
716 --
717 end;
718 --
719 ben_cer_del.del
720 (
721 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
722 ,p_effective_start_date => l_effective_start_date
723 ,p_effective_end_date => l_effective_end_date
724 ,p_object_version_number => l_object_version_number
725 ,p_effective_date => p_effective_date
726 ,p_datetrack_mode => p_datetrack_mode
727 );
728 --
729 begin
730 --
731 -- Start of API User Hook for the after hook of delete_ELIGIBILITY_RULE
732 --
733 ben_ELIGIBILITY_RULE_bk3.delete_ELIGIBILITY_RULE_a
734 (
735 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
736 ,p_effective_start_date => l_effective_start_date
737 ,p_effective_end_date => l_effective_end_date
738 ,p_object_version_number => l_object_version_number
739 ,p_effective_date => trunc(p_effective_date)
740 ,p_datetrack_mode => p_datetrack_mode
741 );
742 exception
743 when hr_api.cannot_find_prog_unit then
744 hr_api.cannot_find_prog_unit_error
745 (p_module_name => 'DELETE_ELIGIBILITY_RULE'
746 ,p_hook_type => 'AP'
747 );
748 --
749 -- End of API User Hook for the after hook of delete_ELIGIBILITY_RULE
750 --
751 end;
752 --
753 --
754 --updates elig_apls_flag to 'N' in ben_pgm_f table
755 --only when no record found in profile and rule tables.
756 if l_c1.pgm_id is not null then
757 --
758 update ben_pgm_f c
759 set c.elig_apls_flag = 'N'
760 where not exists
761 (select a.pgm_id
762 from ben_prtn_elig_f a,
763 ben_prtn_elig_prfl_f b
764 where a.prtn_elig_id = b.prtn_elig_id
765 and c.pgm_id = a.pgm_id)
766 and not exists
767 (select a.pgm_id
768 from ben_prtn_elig_f a,
769 ben_prtn_eligy_rl_f b
770 where a.prtn_elig_id = b.prtn_elig_id
771 and c.pgm_id = a.pgm_id)
772 and c.pgm_id = l_c1.pgm_id;
773 --
774 elsif l_c1.plip_id is not null then
775 --
776 --updates elig_apls_flag to 'N' in ben_plip_f table
777 --only when no record found in profile and rule tables.
778 update ben_plip_f c
779 set c.elig_apls_flag = 'N'
780 where not exists
781 (select a.plip_id
782 from ben_prtn_elig_f a,
783 ben_prtn_elig_prfl_f b
784 where a.prtn_elig_id = b.prtn_elig_id
785 and c.plip_id = a.plip_id)
786 and not exists
787 (select a.plip_id
788 from ben_prtn_elig_f a,
789 ben_prtn_eligy_rl_f b
790 where a.prtn_elig_id = b.prtn_elig_id
791 and c.plip_id = a.plip_id)
792 and c.plip_id = l_c1.plip_id;
793 --
794 elsif l_c1.ptip_id is not null then
795 --
796 --updates elig_apls_flag to 'N' in ben_ptip_f table
797 --only when no record found in profile and rule tables.
798 update ben_ptip_f c
799 set c.elig_apls_flag = 'N'
800 where not exists
801 (select a.ptip_id
802 from ben_prtn_elig_f a,
803 ben_prtn_elig_prfl_f b
804 where a.prtn_elig_id = b.prtn_elig_id
805 and c.ptip_id = a.ptip_id)
806 and not exists
807 (select a.ptip_id
808 from ben_prtn_elig_f a,
809 ben_prtn_eligy_rl_f b
810 where a.prtn_elig_id = b.prtn_elig_id
811 and c.ptip_id = a.ptip_id)
812 and c.ptip_id = l_c1.ptip_id;
813 --
814 elsif l_c1.pl_id is not null then
815 --
816 --updates elig_apls_flag to 'N' in ben_pl_f table
817 --only when no record found in profile and rule tables.
818 update ben_pl_f c
819 set c.elig_apls_flag = 'N'
820 where not exists
821 (select a.pl_id
822 from ben_prtn_elig_f a,
823 ben_prtn_elig_prfl_f b
824 where a.prtn_elig_id = b.prtn_elig_id
825 and c.pl_id = a.pl_id)
826 and not exists
827 (select a.pl_id
828 from ben_prtn_elig_f a,
829 ben_prtn_eligy_rl_f b
830 where a.prtn_elig_id = b.prtn_elig_id
831 and c.pl_id = a.pl_id)
832 and c.pl_id = l_c1.pl_id;
833 --
834 elsif l_c1.oipl_id is not null then
835 --
836 --updates elig_apls_flag to 'N' in ben_oipl_f table
837 --only when no record found in profile and rule tables.
838 update ben_oipl_f c
839 set c.elig_apls_flag = 'N'
840 where not exists
841 (select a.oipl_id
842 from ben_prtn_elig_f a,
843 ben_prtn_elig_prfl_f b
844 where a.prtn_elig_id = b.prtn_elig_id
845 and c.oipl_id = a.oipl_id)
846 and not exists
847 (select a.oipl_id
848 from ben_prtn_elig_f a,
849 ben_prtn_eligy_rl_f b
850 where a.prtn_elig_id = b.prtn_elig_id
851 and c.oipl_id = a.oipl_id)
852 and c.oipl_id = l_c1.oipl_id;
853 --
854 end if;
855 --
856 hr_utility.set_location(l_proc, 60);
857 --
858 -- When in validation only mode raise the Validate_Enabled exception
859 --
860 if p_validate then
861 raise hr_api.validate_enabled;
862 end if;
863 --
864 hr_utility.set_location(' Leaving:'||l_proc, 70);
865 --
866 exception
867 --
868 when hr_api.validate_enabled then
869 --
870 -- As the Validate_Enabled exception has been raised
871 -- we must rollback to the savepoint
872 --
873 ROLLBACK TO delete_ELIGIBILITY_RULE;
874 --
875 -- Only set output warning arguments
876 -- (Any key or derived arguments must be set to null
877 -- when validation only mode is being used.)
878 --
879 p_effective_start_date := null;
880 p_effective_end_date := null;
881 --
882 when others then
883 --
884 -- A validation or unexpected error has occured
885 --
886 ROLLBACK TO delete_ELIGIBILITY_RULE;
887 p_object_version_number := l_object_version_number;
888 p_effective_start_date := null;
889 p_effective_end_date := null;
890
891 raise;
892 --
893 end delete_ELIGIBILITY_RULE;
894 --
895 -- ----------------------------------------------------------------------------
896 -- |-------------------------------< lck >------------------------------------|
897 -- ----------------------------------------------------------------------------
898 --
899 procedure lck
900 (
901 p_prtn_eligy_rl_id in number
902 ,p_object_version_number in number
903 ,p_effective_date in date
904 ,p_datetrack_mode in varchar2
905 ,p_validation_start_date out nocopy date
906 ,p_validation_end_date out nocopy date
907 ) is
908 --
909 --
910 -- Declare cursors and local variables
911 --
912 l_proc varchar2(72) := g_package||'lck';
913 l_validation_start_date date;
914 l_validation_end_date date;
915 --
916 begin
917 --
918 hr_utility.set_location('Entering:'|| l_proc, 10);
919 --
920 ben_cer_shd.lck
921 (
922 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id
923 ,p_validation_start_date => l_validation_start_date
924 ,p_validation_end_date => l_validation_end_date
925 ,p_object_version_number => p_object_version_number
926 ,p_effective_date => p_effective_date
927 ,p_datetrack_mode => p_datetrack_mode
928 );
929 --
930 hr_utility.set_location(' Leaving:'||l_proc, 70);
931 --
932 end lck;
933 --
934 end ben_ELIGIBILITY_RULE_api;