[Home] [Help]
PACKAGE BODY: APPS.BEN_PRTN_ELIG_PRFL_API
Source
1 Package Body ben_PRTN_ELIG_PRFL_api as
2 /* $Header: becepapi.pkb 120.0 2005/05/28 00:59:43 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_PRTN_ELIG_PRFL_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_PRTN_ELIG_PRFL >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_PRTN_ELIG_PRFL
13 (p_validate in boolean default false
14 ,p_prtn_elig_prfl_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_mndtry_flag in varchar2 default null
19 ,p_prtn_elig_id in number default null
20 ,p_eligy_prfl_id in number default null
21 ,p_Elig_prfl_type_cd in varchar2 default null
22 ,p_cep_attribute_category in varchar2 default null
23 ,p_cep_attribute1 in varchar2 default null
24 ,p_cep_attribute2 in varchar2 default null
25 ,p_cep_attribute3 in varchar2 default null
26 ,p_cep_attribute4 in varchar2 default null
27 ,p_cep_attribute5 in varchar2 default null
28 ,p_cep_attribute6 in varchar2 default null
29 ,p_cep_attribute7 in varchar2 default null
30 ,p_cep_attribute8 in varchar2 default null
31 ,p_cep_attribute9 in varchar2 default null
32 ,p_cep_attribute10 in varchar2 default null
33 ,p_cep_attribute11 in varchar2 default null
34 ,p_cep_attribute12 in varchar2 default null
35 ,p_cep_attribute13 in varchar2 default null
36 ,p_cep_attribute14 in varchar2 default null
37 ,p_cep_attribute15 in varchar2 default null
38 ,p_cep_attribute16 in varchar2 default null
39 ,p_cep_attribute17 in varchar2 default null
40 ,p_cep_attribute18 in varchar2 default null
41 ,p_cep_attribute19 in varchar2 default null
42 ,p_cep_attribute20 in varchar2 default null
43 ,p_cep_attribute21 in varchar2 default null
44 ,p_cep_attribute22 in varchar2 default null
45 ,p_cep_attribute23 in varchar2 default null
46 ,p_cep_attribute24 in varchar2 default null
47 ,p_cep_attribute25 in varchar2 default null
48 ,p_cep_attribute26 in varchar2 default null
49 ,p_cep_attribute27 in varchar2 default null
50 ,p_cep_attribute28 in varchar2 default null
51 ,p_cep_attribute29 in varchar2 default null
52 ,p_cep_attribute30 in varchar2 default null
53 ,p_pgm_id in number default null
54 ,p_pl_id in number default null
55 ,p_ptip_id in number default null
56 ,p_plip_id in number default null
57 ,p_oipl_id in number default null
58 ,p_object_version_number out nocopy number
59 ,p_effective_date in date
60 ,p_compute_score_flag in varchar2 default null
61 ) is
62 --
63 -- Declare cursors and local variables
64 --
65 cursor c1(l_prtn_elig_id number) is select pgm_id,pl_id,ptip_id,plip_id,oipl_id
66 from ben_prtn_elig_f
67 where prtn_elig_id = l_prtn_elig_id;
68 l_pgm_id number;
69 l_pl_id number;
70 l_plip_id number;
71 l_oipl_id number;
72 l_ptip_id number;
73 l_prtn_elig_id number;
74 l_prtn_elig_prfl_id ben_prtn_elig_prfl_f.prtn_elig_prfl_id%TYPE;
75 l_effective_start_date ben_prtn_elig_prfl_f.effective_start_date%TYPE;
76 l_effective_end_date ben_prtn_elig_prfl_f.effective_end_date%TYPE;
77 l_proc varchar2(72) := g_package||'create_PRTN_ELIG_PRFL';
78 l_object_version_number ben_prtn_elig_prfl_f.object_version_number%TYPE;
79 --
80 begin
81 --
82 hr_utility.set_location('Entering:'|| l_proc, 10);
83 --
84 -- Issue a savepoint if operating in validation only mode
85 --
86 savepoint create_PRTN_ELIG_PRFL;
87 --
88 hr_utility.set_location(l_proc, 20);
89 --
90 -- Process Logic
91 --
92 begin
93 --
94 if p_prtn_elig_id is null then
95 ben_prtn_elig_prfl_api.insert_prtn_elig
96 (
97 p_pgm_id => p_pgm_id
98 ,p_pl_id => p_pl_id
99 ,p_plip_id => p_plip_id
100 ,p_ptip_id => p_ptip_id
101 ,p_oipl_id => p_oipl_id
102 ,p_effective_date => p_effective_date
103 ,p_business_group_id => p_business_group_id
104 ,p_prtn_elig_id => l_prtn_elig_id
105 );
106 else
107 l_prtn_elig_id := p_prtn_elig_id;
108 end if;
109 -- Start of API User Hook for the before hook of create_PRTN_ELIG_PRFL
110 --
111 ben_PRTN_ELIG_PRFL_bk1.create_PRTN_ELIG_PRFL_b
112 (
113 p_business_group_id => p_business_group_id
114 ,p_mndtry_flag => p_mndtry_flag
115 ,p_prtn_elig_id => l_prtn_elig_id
116 ,p_eligy_prfl_id => p_eligy_prfl_id
117 ,p_Elig_prfl_type_cd => p_Elig_prfl_type_cd
118 ,p_cep_attribute_category => p_cep_attribute_category
119 ,p_cep_attribute1 => p_cep_attribute1
120 ,p_cep_attribute2 => p_cep_attribute2
121 ,p_cep_attribute3 => p_cep_attribute3
122 ,p_cep_attribute4 => p_cep_attribute4
123 ,p_cep_attribute5 => p_cep_attribute5
124 ,p_cep_attribute6 => p_cep_attribute6
125 ,p_cep_attribute7 => p_cep_attribute7
126 ,p_cep_attribute8 => p_cep_attribute8
127 ,p_cep_attribute9 => p_cep_attribute9
128 ,p_cep_attribute10 => p_cep_attribute10
129 ,p_cep_attribute11 => p_cep_attribute11
130 ,p_cep_attribute12 => p_cep_attribute12
131 ,p_cep_attribute13 => p_cep_attribute13
132 ,p_cep_attribute14 => p_cep_attribute14
133 ,p_cep_attribute15 => p_cep_attribute15
134 ,p_cep_attribute16 => p_cep_attribute16
135 ,p_cep_attribute17 => p_cep_attribute17
136 ,p_cep_attribute18 => p_cep_attribute18
137 ,p_cep_attribute19 => p_cep_attribute19
138 ,p_cep_attribute20 => p_cep_attribute20
139 ,p_cep_attribute21 => p_cep_attribute21
140 ,p_cep_attribute22 => p_cep_attribute22
141 ,p_cep_attribute23 => p_cep_attribute23
142 ,p_cep_attribute24 => p_cep_attribute24
143 ,p_cep_attribute25 => p_cep_attribute25
144 ,p_cep_attribute26 => p_cep_attribute26
145 ,p_cep_attribute27 => p_cep_attribute27
146 ,p_cep_attribute28 => p_cep_attribute28
147 ,p_cep_attribute29 => p_cep_attribute29
148 ,p_cep_attribute30 => p_cep_attribute30
149 ,p_effective_date => trunc(p_effective_date)
150 ,p_compute_score_flag => p_compute_score_flag
151 );
152 exception
153 when hr_api.cannot_find_prog_unit then
154 hr_api.cannot_find_prog_unit_error
155 (
156 p_module_name => 'CREATE_PRTN_ELIG_PRFL'
157 ,p_hook_type => 'BP'
158 );
159 --
160 -- End of API User Hook for the before hook of create_PRTN_ELIG_PRFL
161 --
162 end;
163 --
164 ben_cep_ins.ins
165 (
166 p_prtn_elig_prfl_id => l_prtn_elig_prfl_id
167 ,p_effective_start_date => l_effective_start_date
168 ,p_effective_end_date => l_effective_end_date
169 ,p_business_group_id => p_business_group_id
170 ,p_mndtry_flag => p_mndtry_flag
171 ,p_prtn_elig_id => l_prtn_elig_id
172 ,p_eligy_prfl_id => p_eligy_prfl_id
173 ,p_Elig_prfl_type_cd => p_Elig_prfl_type_cd
174 ,p_cep_attribute_category => p_cep_attribute_category
175 ,p_cep_attribute1 => p_cep_attribute1
176 ,p_cep_attribute2 => p_cep_attribute2
177 ,p_cep_attribute3 => p_cep_attribute3
178 ,p_cep_attribute4 => p_cep_attribute4
179 ,p_cep_attribute5 => p_cep_attribute5
180 ,p_cep_attribute6 => p_cep_attribute6
181 ,p_cep_attribute7 => p_cep_attribute7
182 ,p_cep_attribute8 => p_cep_attribute8
183 ,p_cep_attribute9 => p_cep_attribute9
184 ,p_cep_attribute10 => p_cep_attribute10
185 ,p_cep_attribute11 => p_cep_attribute11
186 ,p_cep_attribute12 => p_cep_attribute12
187 ,p_cep_attribute13 => p_cep_attribute13
188 ,p_cep_attribute14 => p_cep_attribute14
189 ,p_cep_attribute15 => p_cep_attribute15
190 ,p_cep_attribute16 => p_cep_attribute16
191 ,p_cep_attribute17 => p_cep_attribute17
192 ,p_cep_attribute18 => p_cep_attribute18
193 ,p_cep_attribute19 => p_cep_attribute19
194 ,p_cep_attribute20 => p_cep_attribute20
195 ,p_cep_attribute21 => p_cep_attribute21
196 ,p_cep_attribute22 => p_cep_attribute22
197 ,p_cep_attribute23 => p_cep_attribute23
198 ,p_cep_attribute24 => p_cep_attribute24
199 ,p_cep_attribute25 => p_cep_attribute25
200 ,p_cep_attribute26 => p_cep_attribute26
201 ,p_cep_attribute27 => p_cep_attribute27
202 ,p_cep_attribute28 => p_cep_attribute28
203 ,p_cep_attribute29 => p_cep_attribute29
204 ,p_cep_attribute30 => p_cep_attribute30
205 ,p_object_version_number => l_object_version_number
206 ,p_effective_date => trunc(p_effective_date)
207 ,p_compute_score_flag => p_compute_score_flag
208 );
209 --
210 begin
211 --
212 -- Start of API User Hook for the after hook of create_PRTN_ELIG_PRFL
213 --
214 ben_PRTN_ELIG_PRFL_bk1.create_PRTN_ELIG_PRFL_a
215 (
216 p_prtn_elig_prfl_id => l_prtn_elig_prfl_id
217 ,p_effective_start_date => l_effective_start_date
218 ,p_effective_end_date => l_effective_end_date
219 ,p_business_group_id => p_business_group_id
220 ,p_mndtry_flag => p_mndtry_flag
221 ,p_prtn_elig_id => l_prtn_elig_id
222 ,p_eligy_prfl_id => p_eligy_prfl_id
223 ,p_Elig_prfl_type_cd => p_Elig_prfl_type_cd
224 ,p_cep_attribute_category => p_cep_attribute_category
225 ,p_cep_attribute1 => p_cep_attribute1
226 ,p_cep_attribute2 => p_cep_attribute2
227 ,p_cep_attribute3 => p_cep_attribute3
228 ,p_cep_attribute4 => p_cep_attribute4
229 ,p_cep_attribute5 => p_cep_attribute5
230 ,p_cep_attribute6 => p_cep_attribute6
231 ,p_cep_attribute7 => p_cep_attribute7
232 ,p_cep_attribute8 => p_cep_attribute8
233 ,p_cep_attribute9 => p_cep_attribute9
234 ,p_cep_attribute10 => p_cep_attribute10
235 ,p_cep_attribute11 => p_cep_attribute11
236 ,p_cep_attribute12 => p_cep_attribute12
237 ,p_cep_attribute13 => p_cep_attribute13
238 ,p_cep_attribute14 => p_cep_attribute14
239 ,p_cep_attribute15 => p_cep_attribute15
240 ,p_cep_attribute16 => p_cep_attribute16
241 ,p_cep_attribute17 => p_cep_attribute17
242 ,p_cep_attribute18 => p_cep_attribute18
243 ,p_cep_attribute19 => p_cep_attribute19
244 ,p_cep_attribute20 => p_cep_attribute20
245 ,p_cep_attribute21 => p_cep_attribute21
246 ,p_cep_attribute22 => p_cep_attribute22
247 ,p_cep_attribute23 => p_cep_attribute23
248 ,p_cep_attribute24 => p_cep_attribute24
249 ,p_cep_attribute25 => p_cep_attribute25
250 ,p_cep_attribute26 => p_cep_attribute26
251 ,p_cep_attribute27 => p_cep_attribute27
252 ,p_cep_attribute28 => p_cep_attribute28
253 ,p_cep_attribute29 => p_cep_attribute29
254 ,p_cep_attribute30 => p_cep_attribute30
255 ,p_object_version_number => l_object_version_number
256 ,p_effective_date => trunc(p_effective_date)
257 ,p_compute_score_flag => p_compute_score_flag
258 );
259 exception
260 when hr_api.cannot_find_prog_unit then
261 hr_api.cannot_find_prog_unit_error
262 (p_module_name => 'CREATE_PRTN_ELIG_PRFL'
263 ,p_hook_type => 'AP'
264 );
265 --
266 -- End of API User Hook for the after hook of create_PRTN_ELIG_PRFL
267 --
268 end;
269 --
270 Open c1(l_prtn_elig_id);
271 fetch c1 into l_pgm_id,l_pl_id,l_ptip_id,l_plip_id,l_oipl_id;
272 close c1;
273 --
274 --update statements
275 --updates elig_apls_flag to 'Y' in ben_pgm_f table.
276 --
277 if l_pgm_id is not null then
278 update ben_pgm_f c
279 set c.elig_apls_flag = 'Y'
280 where c.business_group_id = p_business_group_id
281 and c.elig_apls_flag <> 'Y'
282 and c.pgm_id = l_pgm_id ;
283 --
284 hr_utility.set_location(' update of pgm_f ' , 60);
285 --
286 elsif l_plip_id is not null then
287 --
288 --updates elig_apls_flag to 'Y' in ben_plip_f table.
289 update ben_plip_f c
290 set c.elig_apls_flag = 'Y'
291 where c.business_group_id = p_business_group_id
292 and c.elig_apls_flag <> 'Y'
293 and c.plip_id = l_plip_id ;
294 --
295 hr_utility.set_location(' update of plip_f ' , 60);
296 --
297 elsif l_ptip_id is not null then
298 --updates elig_apls_flag to 'Y' in ben_ptip_f table.
299 update ben_ptip_f c
300 set c.elig_apls_flag = 'Y'
301 where c.business_group_id = p_business_group_id
302 and c.elig_apls_flag <> 'Y'
303 and c.ptip_id = l_ptip_id ;
304 hr_utility.set_location(' update of ptip_f ' , 60);
305 --
306 elsif l_pl_id is not null then
307 --updates elig_apls_flag to 'Y' in ben_pl_f table.
308 update ben_pl_f c
309 set c.elig_apls_flag = 'Y'
310 where c.business_group_id = p_business_group_id
311 and c.elig_apls_flag <> 'Y'
312 and c.pl_id = l_pl_id ;
313 --
314 hr_utility.set_location(' update of pl_f ' , 60);
315 --
316 elsif l_oipl_id is not null then
317 --updates elig_apls_flag to 'Y' in ben_oipl_f table.
318 update ben_oipl_f c
319 set c.elig_apls_flag = 'Y'
320 where c.business_group_id = p_business_group_id
321 and c.elig_apls_flag <> 'Y'
322 and c.oipl_id = l_oipl_id ;
323 --
324 hr_utility.set_location(' update of oipl_f ' , 60);
325 end if;
326 --
327 hr_utility.set_location(l_proc, 60);
328 --
329 -- When in validation only mode raise the Validate_Enabled exception
330 --
331 if p_validate then
332 raise hr_api.validate_enabled;
333 end if;
334 --
335
336 if l_pgm_id is not null then
337 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_pgm_f','pgm_id',l_pgm_id);
338 end if;
339 --
340 if l_pl_id is not null then
341 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_pl_f','pl_id',l_pl_id);
342 end if;
343 --
344 if l_plip_id is not null then
345 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_plip_f','plip_id',l_plip_id);
346 end if;
347 --
348 if l_ptip_id is not null then
349 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_ptip_f','ptip_id',l_ptip_id);
350 end if;
351 --
352 if l_oipl_id is not null then
353 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_oipl_f','oipl_id',l_oipl_id);
354 end if;
355 --
356 -- Set all output arguments
357 p_prtn_elig_prfl_id := l_prtn_elig_prfl_id;
358 p_effective_start_date := l_effective_start_date;
359 p_effective_end_date := l_effective_end_date;
360 p_object_version_number := l_object_version_number;
361 --
362 hr_utility.set_location(' Leaving:'||l_proc, 70);
363 --
364 exception
365 --
366 when hr_api.validate_enabled then
367 --
368 -- As the Validate_Enabled exception has been raised
369 -- we must rollback to the savepoint
370 --
371 ROLLBACK TO create_PRTN_ELIG_PRFL;
372 --
373 -- Only set output warning arguments
374 -- (Any key or derived arguments must be set to null
375 -- when validation only mode is being used.)
376 --
377 p_prtn_elig_prfl_id := null;
378 p_effective_start_date := null;
379 p_effective_end_date := null;
380 p_object_version_number := null;
381 hr_utility.set_location(' Leaving:'||l_proc, 80);
382 --
383 when others then
384 --
385 -- A validation or unexpected error has occured
386 --
387 ROLLBACK TO create_PRTN_ELIG_PRFL;
388 raise;
389 --
390 end create_PRTN_ELIG_PRFL;
391 -- ----------------------------------------------------------------------------
392 -- |------------------------< update_PRTN_ELIG_PRFL >--- ------------------|
393 -- ----------------------------------------------------------------------------
394 --
395 procedure update_PRTN_ELIG_PRFL
396 (p_validate in boolean default false
397 ,p_prtn_elig_prfl_id in number
398 ,p_effective_start_date out nocopy date
399 ,p_effective_end_date out nocopy date
400 ,p_business_group_id in number default hr_api.g_number
401 ,p_mndtry_flag in varchar2 default hr_api.g_varchar2
402 ,p_prtn_elig_id in number default hr_api.g_number
403 ,p_eligy_prfl_id in number default hr_api.g_number
404 ,p_Elig_prfl_type_cd in varchar2 default hr_api.g_varchar2
405 ,p_cep_attribute_category in varchar2 default hr_api.g_varchar2
406 ,p_cep_attribute1 in varchar2 default hr_api.g_varchar2
407 ,p_cep_attribute2 in varchar2 default hr_api.g_varchar2
408 ,p_cep_attribute3 in varchar2 default hr_api.g_varchar2
409 ,p_cep_attribute4 in varchar2 default hr_api.g_varchar2
410 ,p_cep_attribute5 in varchar2 default hr_api.g_varchar2
411 ,p_cep_attribute6 in varchar2 default hr_api.g_varchar2
412 ,p_cep_attribute7 in varchar2 default hr_api.g_varchar2
413 ,p_cep_attribute8 in varchar2 default hr_api.g_varchar2
414 ,p_cep_attribute9 in varchar2 default hr_api.g_varchar2
415 ,p_cep_attribute10 in varchar2 default hr_api.g_varchar2
416 ,p_cep_attribute11 in varchar2 default hr_api.g_varchar2
417 ,p_cep_attribute12 in varchar2 default hr_api.g_varchar2
418 ,p_cep_attribute13 in varchar2 default hr_api.g_varchar2
419 ,p_cep_attribute14 in varchar2 default hr_api.g_varchar2
420 ,p_cep_attribute15 in varchar2 default hr_api.g_varchar2
421 ,p_cep_attribute16 in varchar2 default hr_api.g_varchar2
422 ,p_cep_attribute17 in varchar2 default hr_api.g_varchar2
423 ,p_cep_attribute18 in varchar2 default hr_api.g_varchar2
424 ,p_cep_attribute19 in varchar2 default hr_api.g_varchar2
425 ,p_cep_attribute20 in varchar2 default hr_api.g_varchar2
426 ,p_cep_attribute21 in varchar2 default hr_api.g_varchar2
427 ,p_cep_attribute22 in varchar2 default hr_api.g_varchar2
428 ,p_cep_attribute23 in varchar2 default hr_api.g_varchar2
429 ,p_cep_attribute24 in varchar2 default hr_api.g_varchar2
430 ,p_cep_attribute25 in varchar2 default hr_api.g_varchar2
431 ,p_cep_attribute26 in varchar2 default hr_api.g_varchar2
432 ,p_cep_attribute27 in varchar2 default hr_api.g_varchar2
433 ,p_cep_attribute28 in varchar2 default hr_api.g_varchar2
434 ,p_cep_attribute29 in varchar2 default hr_api.g_varchar2
435 ,p_cep_attribute30 in varchar2 default hr_api.g_varchar2
436 ,p_object_version_number in out nocopy number
437 ,p_effective_date in date
438 ,p_datetrack_mode in varchar2
439 ,p_compute_score_flag in varchar2 default hr_api.g_varchar2
440 ) is
441 --
442 -- Declare cursors and local variables
443 -- 3688111
444 cursor c_cep is
445 select eligy_prfl_id
446 from ben_prtn_elig_prfl_f
447 where prtn_elig_prfl_id = p_prtn_elig_prfl_id
448 and p_effective_date between effective_start_date and effective_end_date ;
449
450 cursor c1 is
451 select pgm_id,
452 ptip_id,
453 plip_id,
454 pl_id,
455 oipl_id
456 from ben_prtn_elig_f a,
457 ben_prtn_elig_prfl_f b
458 where a.prtn_elig_id = b.prtn_elig_id
459 and b.prtn_elig_prfl_id = p_prtn_elig_prfl_id;
460 l_c1 c1%rowtype;
461 --
462 cursor c_pgm_flag (c_id number) is
463 select drvbl_fctr_prtn_elig_flag
464 from ben_pgm_f
465 where pgm_id = c_id
466 and p_effective_date between effective_start_date and effective_end_date ;
467
468 cursor c_pl_flag (c_id number) is
469 select drvbl_fctr_prtn_elig_flag
470 from ben_pl_f
471 where pl_id = c_id
472 and p_effective_date between effective_start_date and effective_end_date ;
473
474 cursor c_plip_flag (c_id number) is
475 select drvbl_fctr_prtn_elig_flag
476 from ben_plip_f
477 where plip_id = c_id
478 and p_effective_date between effective_start_date and effective_end_date ;
479
480 cursor c_ptip_flag (c_id number) is
481 select drvbl_fctr_prtn_elig_flag
482 from ben_ptip_f
483 where ptip_id = c_id
484 and p_effective_date between effective_start_date and effective_end_date ;
485
486
487 cursor c_oipl_flag (c_id number) is
488 select drvbl_fctr_prtn_elig_flag
489 from ben_oipl_f
490 where oipl_id = c_id
491 and p_effective_date between effective_start_date and effective_end_date ;
492
493 --
494 l_proc varchar2(72) := g_package||'update_PRTN_ELIG_PRFL';
495 l_object_version_number ben_prtn_elig_prfl_f.object_version_number%TYPE;
496 l_effective_start_date ben_prtn_elig_prfl_f.effective_start_date%TYPE;
497 l_effective_end_date ben_prtn_elig_prfl_f.effective_end_date%TYPE;
498 -- 3688111
499 l_old_eligy_prfl_id number ;
500 l_drvbl_fctr_prtn_elig_flag varchar2(30) ;
501 --
502 begin
503 --
504 hr_utility.set_location('Entering:'|| l_proc, 10);
505 --
506 -- Issue a savepoint if operating in validation only mode
507 --
508 savepoint update_PRTN_ELIG_PRFL;
509 --
510 hr_utility.set_location(l_proc, 20);
511 --
512 -- Process Logic
513 --
514 l_object_version_number := p_object_version_number;
515 --
516 begin
517 --
518
519 -- bug 3688111 get the old id
520 open c_cep ;
521 fetch c_cep into l_old_eligy_prfl_id ;
522 close c_cep ;
523
524 -- Start of API User Hook for the before hook of update_PRTN_ELIG_PRFL
525 --
526 ben_PRTN_ELIG_PRFL_bk2.update_PRTN_ELIG_PRFL_b
527 (
528 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
529 ,p_business_group_id => p_business_group_id
530 ,p_mndtry_flag => p_mndtry_flag
531 ,p_prtn_elig_id => p_prtn_elig_id
532 ,p_eligy_prfl_id => p_eligy_prfl_id
533 ,p_Elig_prfl_type_cd => p_Elig_prfl_type_cd
534 ,p_cep_attribute_category => p_cep_attribute_category
535 ,p_cep_attribute1 => p_cep_attribute1
536 ,p_cep_attribute2 => p_cep_attribute2
537 ,p_cep_attribute3 => p_cep_attribute3
538 ,p_cep_attribute4 => p_cep_attribute4
539 ,p_cep_attribute5 => p_cep_attribute5
540 ,p_cep_attribute6 => p_cep_attribute6
541 ,p_cep_attribute7 => p_cep_attribute7
542 ,p_cep_attribute8 => p_cep_attribute8
543 ,p_cep_attribute9 => p_cep_attribute9
544 ,p_cep_attribute10 => p_cep_attribute10
545 ,p_cep_attribute11 => p_cep_attribute11
546 ,p_cep_attribute12 => p_cep_attribute12
547 ,p_cep_attribute13 => p_cep_attribute13
548 ,p_cep_attribute14 => p_cep_attribute14
549 ,p_cep_attribute15 => p_cep_attribute15
550 ,p_cep_attribute16 => p_cep_attribute16
551 ,p_cep_attribute17 => p_cep_attribute17
552 ,p_cep_attribute18 => p_cep_attribute18
553 ,p_cep_attribute19 => p_cep_attribute19
554 ,p_cep_attribute20 => p_cep_attribute20
555 ,p_cep_attribute21 => p_cep_attribute21
556 ,p_cep_attribute22 => p_cep_attribute22
557 ,p_cep_attribute23 => p_cep_attribute23
558 ,p_cep_attribute24 => p_cep_attribute24
559 ,p_cep_attribute25 => p_cep_attribute25
560 ,p_cep_attribute26 => p_cep_attribute26
561 ,p_cep_attribute27 => p_cep_attribute27
562 ,p_cep_attribute28 => p_cep_attribute28
563 ,p_cep_attribute29 => p_cep_attribute29
564 ,p_cep_attribute30 => p_cep_attribute30
565 ,p_object_version_number => p_object_version_number
566 ,p_effective_date => trunc(p_effective_date)
567 ,p_datetrack_mode => p_datetrack_mode
568 ,p_compute_score_flag => p_compute_score_flag
569 );
570 exception
571 when hr_api.cannot_find_prog_unit then
572 hr_api.cannot_find_prog_unit_error
573 (p_module_name => 'UPDATE_PRTN_ELIG_PRFL'
574 ,p_hook_type => 'BP'
575 );
576 --
577 -- End of API User Hook for the before hook of update_PRTN_ELIG_PRFL
578 --
579 end;
580 --
581 ben_cep_upd.upd
582 (
583 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
584 ,p_effective_start_date => l_effective_start_date
585 ,p_effective_end_date => l_effective_end_date
586 ,p_business_group_id => p_business_group_id
587 ,p_mndtry_flag => p_mndtry_flag
588 ,p_prtn_elig_id => p_prtn_elig_id
589 ,p_eligy_prfl_id => p_eligy_prfl_id
590 ,p_Elig_prfl_type_cd => p_Elig_prfl_type_cd
591 ,p_cep_attribute_category => p_cep_attribute_category
592 ,p_cep_attribute1 => p_cep_attribute1
593 ,p_cep_attribute2 => p_cep_attribute2
594 ,p_cep_attribute3 => p_cep_attribute3
595 ,p_cep_attribute4 => p_cep_attribute4
596 ,p_cep_attribute5 => p_cep_attribute5
597 ,p_cep_attribute6 => p_cep_attribute6
598 ,p_cep_attribute7 => p_cep_attribute7
599 ,p_cep_attribute8 => p_cep_attribute8
600 ,p_cep_attribute9 => p_cep_attribute9
601 ,p_cep_attribute10 => p_cep_attribute10
602 ,p_cep_attribute11 => p_cep_attribute11
603 ,p_cep_attribute12 => p_cep_attribute12
604 ,p_cep_attribute13 => p_cep_attribute13
605 ,p_cep_attribute14 => p_cep_attribute14
606 ,p_cep_attribute15 => p_cep_attribute15
607 ,p_cep_attribute16 => p_cep_attribute16
608 ,p_cep_attribute17 => p_cep_attribute17
609 ,p_cep_attribute18 => p_cep_attribute18
610 ,p_cep_attribute19 => p_cep_attribute19
611 ,p_cep_attribute20 => p_cep_attribute20
612 ,p_cep_attribute21 => p_cep_attribute21
613 ,p_cep_attribute22 => p_cep_attribute22
614 ,p_cep_attribute23 => p_cep_attribute23
615 ,p_cep_attribute24 => p_cep_attribute24
616 ,p_cep_attribute25 => p_cep_attribute25
617 ,p_cep_attribute26 => p_cep_attribute26
618 ,p_cep_attribute27 => p_cep_attribute27
619 ,p_cep_attribute28 => p_cep_attribute28
620 ,p_cep_attribute29 => p_cep_attribute29
621 ,p_cep_attribute30 => p_cep_attribute30
622 ,p_object_version_number => l_object_version_number
623 ,p_effective_date => trunc(p_effective_date)
624 ,p_datetrack_mode => p_datetrack_mode
625 ,p_compute_score_flag => p_compute_score_flag
626 );
627 --
628 begin
629 --
630 -- Start of API User Hook for the after hook of update_PRTN_ELIG_PRFL
631 --
632 ben_PRTN_ELIG_PRFL_bk2.update_PRTN_ELIG_PRFL_a
633 (
634 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
635 ,p_effective_start_date => l_effective_start_date
636 ,p_effective_end_date => l_effective_end_date
637 ,p_business_group_id => p_business_group_id
638 ,p_mndtry_flag => p_mndtry_flag
639 ,p_prtn_elig_id => p_prtn_elig_id
640 ,p_eligy_prfl_id => p_eligy_prfl_id
641 ,p_Elig_prfl_type_cd => p_Elig_prfl_type_cd
642 ,p_cep_attribute_category => p_cep_attribute_category
643 ,p_cep_attribute1 => p_cep_attribute1
644 ,p_cep_attribute2 => p_cep_attribute2
645 ,p_cep_attribute3 => p_cep_attribute3
646 ,p_cep_attribute4 => p_cep_attribute4
647 ,p_cep_attribute5 => p_cep_attribute5
648 ,p_cep_attribute6 => p_cep_attribute6
649 ,p_cep_attribute7 => p_cep_attribute7
650 ,p_cep_attribute8 => p_cep_attribute8
651 ,p_cep_attribute9 => p_cep_attribute9
652 ,p_cep_attribute10 => p_cep_attribute10
653 ,p_cep_attribute11 => p_cep_attribute11
654 ,p_cep_attribute12 => p_cep_attribute12
655 ,p_cep_attribute13 => p_cep_attribute13
656 ,p_cep_attribute14 => p_cep_attribute14
657 ,p_cep_attribute15 => p_cep_attribute15
658 ,p_cep_attribute16 => p_cep_attribute16
659 ,p_cep_attribute17 => p_cep_attribute17
660 ,p_cep_attribute18 => p_cep_attribute18
661 ,p_cep_attribute19 => p_cep_attribute19
662 ,p_cep_attribute20 => p_cep_attribute20
663 ,p_cep_attribute21 => p_cep_attribute21
664 ,p_cep_attribute22 => p_cep_attribute22
665 ,p_cep_attribute23 => p_cep_attribute23
666 ,p_cep_attribute24 => p_cep_attribute24
667 ,p_cep_attribute25 => p_cep_attribute25
668 ,p_cep_attribute26 => p_cep_attribute26
669 ,p_cep_attribute27 => p_cep_attribute27
670 ,p_cep_attribute28 => p_cep_attribute28
671 ,p_cep_attribute29 => p_cep_attribute29
672 ,p_cep_attribute30 => p_cep_attribute30
673 ,p_object_version_number => l_object_version_number
674 ,p_effective_date => trunc(p_effective_date)
675 ,p_datetrack_mode => p_datetrack_mode
676 ,p_compute_score_flag => p_compute_score_flag
677 );
678 exception
679 when hr_api.cannot_find_prog_unit then
680 hr_api.cannot_find_prog_unit_error
681 (p_module_name => 'UPDATE_PRTN_ELIG_PRFL'
682 ,p_hook_type => 'AP'
683 );
684 --
685 -- End of API User Hook for the after hook of update_PRTN_ELIG_PRFL
686 --
687 end;
688 --
689 hr_utility.set_location(l_proc, 60);
690 --
691 -- When in validation only mode raise the Validate_Enabled exception
692 --
693 if p_validate then
694 raise hr_api.validate_enabled;
695 end if;
696 --
697
698 -- eligy_prfl_handler called to update the DRVBL_FCTR_PRTN_ELIG_FLAG flag
699 -- so far this procedure called in create and delete only
700 -- there is a possibility the user may attach a prfile without derive factor
701 -- then update the profile with a profile which has derive factor or vise a verse
702 -- in this case we have to call the eligy_prfl_handler to update the column DRVBL_FCTR_PRTN_ELIG_FLAG
703 -- bug 3688111
704 -- 1 check whether the old profile id and new profile id is the same , if not
705 -- 2 if the flag is 'Y' and ther is a possibility current may not have the DF, so call in delete mode
706 -- 3 if the flag is 'N' and ther is a possibility current may have the DF, so call in Create mode
707
708 hr_utility.set_location( ' old ' || l_old_eligy_prfl_id || ' new ' || p_eligy_prfl_id , 11);
709 if l_old_eligy_prfl_id <> p_eligy_prfl_id then
710 open c1 ;
711 fetch c1 into l_c1;
712 close c1;
713
714 if l_c1.pgm_id is not null then
715 open c_pgm_flag(l_c1.pgm_id) ;
716 fetch c_pgm_flag into l_drvbl_fctr_prtn_elig_flag ;
717 close c_pgm_flag ;
718
719 hr_utility.set_location( ' old pgm flag ' || l_drvbl_fctr_prtn_elig_flag , 11);
720
721 if nvl( l_drvbl_fctr_prtn_elig_flag,'N') = 'N' then
722 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_pgm_f','pgm_id',l_c1.pgm_id);
723 else
724 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pgm_f','pgm_id',l_c1.pgm_id);
725 end if ;
726 end if;
727 --
728 if l_c1.pl_id is not null then
729
730 open c_pl_flag(l_c1.pl_id) ;
731 fetch c_pl_flag into l_drvbl_fctr_prtn_elig_flag ;
732 close c_pl_flag ;
733
734 hr_utility.set_location( ' old pl flag ' || l_drvbl_fctr_prtn_elig_flag , 11);
735
736
737 if nvl( l_drvbl_fctr_prtn_elig_flag,'N') = 'N' then
738 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_pl_f','pl_id',l_c1.pl_id);
739 else
740 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pl_f','pl_id',l_c1.pl_id);
741 end if ;
742 end if;
743 --
744 if l_c1.plip_id is not null then
745
746 open c_plip_flag( l_c1.plip_id ) ;
747 fetch c_plip_flag into l_drvbl_fctr_prtn_elig_flag ;
748 close c_plip_flag ;
749
750 hr_utility.set_location( ' old plip flag ' || l_drvbl_fctr_prtn_elig_flag , 11);
751
752 if nvl( l_drvbl_fctr_prtn_elig_flag,'N') = 'N' then
753 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_plip_f','plip_id',l_c1.plip_id);
754 else
755 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_plip_f','plip_id',l_c1.plip_id);
756 end if ;
757 end if;
758 --
759 if l_c1.ptip_id is not null then
760
761 open c_ptip_flag (l_c1.ptip_id ) ;
762 fetch c_ptip_flag into l_drvbl_fctr_prtn_elig_flag ;
763 close c_ptip_flag ;
764
765 hr_utility.set_location( ' old ptip flag ' || l_drvbl_fctr_prtn_elig_flag , 11);
766
767 if nvl( l_drvbl_fctr_prtn_elig_flag,'N') = 'N' then
768 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_ptip_f','ptip_id',l_c1.ptip_id);
769 else
770 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_ptip_f','ptip_id',l_c1.ptip_id);
771 end if ;
772 end if;
773 --
774 if l_c1.oipl_id is not null then
775
776 open c_oipl_flag ( l_c1.oipl_id ) ;
777 fetch c_oipl_flag into l_drvbl_fctr_prtn_elig_flag ;
778 close c_oipl_flag ;
779
780 hr_utility.set_location( ' old oipl flag ' || l_drvbl_fctr_prtn_elig_flag , 11);
781
782
783 if nvl( l_drvbl_fctr_prtn_elig_flag,'N') = 'N' then
784 ben_derivable_factor.eligy_prfl_handler('CREATE','ben_oipl_f','oipl_id',l_c1.oipl_id);
785 else
786 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_oipl_f','oipl_id',l_c1.oipl_id);
787 end if;
788
789 end if;
790 end if ;
791
792
793 -- Set all output arguments
794 --
795 p_object_version_number := l_object_version_number;
796 p_effective_start_date := l_effective_start_date;
797 p_effective_end_date := l_effective_end_date;
798 --
799 hr_utility.set_location(' Leaving:'||l_proc, 70);
800 --
801 exception
802 --
803 when hr_api.validate_enabled then
804 --
805 -- As the Validate_Enabled exception has been raised
806 -- we must rollback to the savepoint
807 --
808 ROLLBACK TO update_PRTN_ELIG_PRFL;
809 --
810 -- Only set output warning arguments
811 -- (Any key or derived arguments must be set to null
812 -- when validation only mode is being used.)
813 --
814 hr_utility.set_location(' Leaving:'||l_proc, 80);
815 --
816 when others then
817 --
818 -- A validation or unexpected error has occured
819 --
820 ROLLBACK TO update_PRTN_ELIG_PRFL;
821 p_object_version_number := l_object_version_number;
822 p_effective_start_date := null;
823 p_effective_end_date := null;
824 raise;
825 --
826 end update_PRTN_ELIG_PRFL;
827 -- ----------------------------------------------------------------------------
828 -- |------------------------< delete_PRTN_ELIG_PRFL >----------------------|
829 -- ----------------------------------------------------------------------------
830 --
831 procedure delete_PRTN_ELIG_PRFL
832 (p_validate in boolean default false
833 ,p_prtn_elig_prfl_id in number
834 ,p_effective_start_date out nocopy date
835 ,p_effective_end_date out nocopy date
836 ,p_object_version_number in out nocopy number
837 ,p_effective_date in date
838 ,p_datetrack_mode in varchar2
839 ) is
840 --
841 -- Declare cursors and local variables
842 --
843 l_proc varchar2(72) := g_package||'update_PRTN_ELIG_PRFL';
844 l_object_version_number ben_prtn_elig_prfl_f.object_version_number%TYPE;
845 l_effective_start_date ben_prtn_elig_prfl_f.effective_start_date%TYPE;
846 l_effective_end_date ben_prtn_elig_prfl_f.effective_end_date%TYPE;
847 --
848 cursor c1 is
849 select pgm_id,
850 ptip_id,
851 plip_id,
852 pl_id,
853 oipl_id
854 from ben_prtn_elig_f a,
855 ben_prtn_elig_prfl_f b
856 where a.prtn_elig_id = b.prtn_elig_id
857 and b.prtn_elig_prfl_id = p_prtn_elig_prfl_id;
858 --
859 l_c1 c1%rowtype;
860 --
861 begin
862 --
863 open c1;
864 fetch c1 into l_c1;
865 close c1;
866 --
867 hr_utility.set_location('Entering:'|| l_proc, 10);
868 --
869 -- Issue a savepoint if operating in validation only mode
870 --
871 savepoint delete_PRTN_ELIG_PRFL;
872 --
873 hr_utility.set_location(l_proc, 20);
874 --
875 -- Process Logic
876 --
877 l_object_version_number := p_object_version_number;
878 --
879 --
880 begin
881 --
882 -- Start of API User Hook for the before hook of delete_PRTN_ELIG_PRFL
883 --
884 ben_PRTN_ELIG_PRFL_bk3.delete_PRTN_ELIG_PRFL_b
885 (
886 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
887 ,p_object_version_number => p_object_version_number
888 ,p_effective_date => trunc(p_effective_date)
889 ,p_datetrack_mode => p_datetrack_mode
890 );
891 exception
892 when hr_api.cannot_find_prog_unit then
893 hr_api.cannot_find_prog_unit_error
894 (p_module_name => 'DELETE_PRTN_ELIG_PRFL'
895 ,p_hook_type => 'BP'
896 );
897 --
898 -- End of API User Hook for the before hook of delete_PRTN_ELIG_PRFL
899 --
900 end;
901 --
902 ben_cep_del.del
903 (
904 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
905 ,p_effective_start_date => l_effective_start_date
906 ,p_effective_end_date => l_effective_end_date
907 ,p_object_version_number => l_object_version_number
908 ,p_effective_date => p_effective_date
909 ,p_datetrack_mode => p_datetrack_mode
910 );
911 --
912 begin
913 --
914 -- Start of API User Hook for the after hook of delete_PRTN_ELIG_PRFL
915 --
916 ben_PRTN_ELIG_PRFL_bk3.delete_PRTN_ELIG_PRFL_a
917 (
918 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
919 ,p_effective_start_date => l_effective_start_date
920 ,p_effective_end_date => l_effective_end_date
921 ,p_object_version_number => l_object_version_number
922 ,p_effective_date => trunc(p_effective_date)
923 ,p_datetrack_mode => p_datetrack_mode
924 );
925 exception
926 when hr_api.cannot_find_prog_unit then
927 hr_api.cannot_find_prog_unit_error
928 (p_module_name => 'DELETE_PRTN_ELIG_PRFL'
929 ,p_hook_type => 'AP'
930 );
931 --
932 -- End of API User Hook for the after hook of delete_PRTN_ELIG_PRFL
933 --
934 end;
935 --
936 --updates elig_apls_flag to 'N' in ben_pgm_f table only when
937 --no record found in profile and rule tables.
938 if l_c1.pgm_id is not null then
939 --
940 update ben_pgm_f c
941 set c.elig_apls_flag = 'N'
942 where not exists
943 (select a.pgm_id
944 from ben_prtn_elig_f a,
945 ben_prtn_elig_prfl_f b
946 where a.prtn_elig_id = b.prtn_elig_id
947 and a.pgm_id = c.pgm_id)
948 and not exists
949 (select a.pgm_id
950 from ben_prtn_elig_f a,
951 ben_prtn_eligy_rl_f b
952 where a.prtn_elig_id = b.prtn_elig_id
953 and a.pgm_id = c.pgm_id)
954 and c.pgm_id = l_c1.pgm_id;
955 -- updates drvbl_fctr_prtn_elig_flag
956 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pgm_f','pgm_id',l_c1.pgm_id);
957 --
958 --updates elig_apls_flag to 'N' in ben_plip_f table only when
959 --no record found in profile and rule tables.
960 elsif l_c1.plip_id is not null then
961 --
962 update ben_plip_f c
963 set c.elig_apls_flag = 'N'
964 where not exists
965 (select a.plip_id
966 from ben_prtn_elig_f a,
967 ben_prtn_elig_prfl_f b
968 where a.prtn_elig_id = b.prtn_elig_id
969 and a.plip_id = c.plip_id)
970 and not exists
971 (select a.plip_id
972 from ben_prtn_elig_f a,
973 ben_prtn_eligy_rl_f b
974 where a.prtn_elig_id = b.prtn_elig_id
975 and a.plip_id = c.plip_id)
976 and c.plip_id = l_c1.plip_id;
977 --
978 -- updates drvbl_fctr_prtn_elig_flag
979 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_plip_f','plip_id',l_c1.plip_id);
980 --
981 --
982 --updates elig_apls_flag to 'N' in ben_ptip_f table only when
983 --no record found in profile and rule tables.
984 elsif l_c1.ptip_id is not null then
985 update ben_ptip_f c
986 set c.elig_apls_flag = 'N'
987 where not exists
988 (select a.ptip_id
989 from ben_prtn_elig_f a,
990 ben_prtn_elig_prfl_f b
991 where a.prtn_elig_id = b.prtn_elig_id
992 and a.ptip_id = c.ptip_id)
993 and not exists
994 (select a.ptip_id
995 from ben_prtn_elig_f a,
996 ben_prtn_eligy_rl_f b
997 where a.prtn_elig_id = b.prtn_elig_id
998 and a.ptip_id = c.ptip_id)
999 and c.ptip_id = l_c1.ptip_id;
1000 --
1001 -- updates drvbl_fctr_prtn_elig_flag
1002 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_ptip_f','ptip_id',l_c1.ptip_id);
1003 --
1004 --updates elig_apls_flag to 'N' in ben_pl_f table only when
1005 --no record found in profile and rule tables.
1006 elsif l_c1.pl_id is not null then
1007 update ben_pl_f c
1008 set c.elig_apls_flag = 'N'
1009 where not exists
1010 (select a.pl_id
1011 from ben_prtn_elig_f a,
1012 ben_prtn_elig_prfl_f b
1013 where a.prtn_elig_id = b.prtn_elig_id
1014 and a.pl_id = c.pl_id)
1015 and not exists
1016 (select a.pl_id
1017 from ben_prtn_elig_f a,
1018 ben_prtn_eligy_rl_f b
1019 where a.prtn_elig_id = b.prtn_elig_id
1020 and a.pl_id = c.pl_id)
1021 and c.pl_id = l_c1.pl_id;
1022 --
1023 -- updates drvbl_fctr_prtn_elig_flag
1024 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_pl_f','pl_id',l_c1.pl_id);
1025 --
1026 --
1027 --updates elig_apls_flag to 'N' in ben_oipl_f table only when
1028 --no record found in profile and rule tables.
1029 elsif l_c1.oipl_id is not null then
1030 update ben_oipl_f c
1031 set c.elig_apls_flag = 'N'
1032 where not exists
1033 (select a.oipl_id
1034 from ben_prtn_elig_f a,
1035 ben_prtn_elig_prfl_f b
1036 where a.prtn_elig_id = b.prtn_elig_id
1037 and a.oipl_id = c.oipl_id)
1038 and not exists
1039 (select a.oipl_id
1040 from ben_prtn_elig_f a,
1041 ben_prtn_eligy_rl_f b
1042 where a.prtn_elig_id = b.prtn_elig_id
1043 and a.oipl_id = c.oipl_id)
1044 and c.oipl_id = l_c1.oipl_id;
1045 --
1046 -- updates drvbl_fctr_prtn_elig_flag
1047 ben_derivable_factor.eligy_prfl_handler('DELETE','ben_oipl_f','oipl_id',l_c1.oipl_id);
1048 --
1049 end if;
1050 --
1051 hr_utility.set_location(l_proc, 60);
1052 --
1053 -- When in validation only mode raise the Validate_Enabled exception
1054 --
1055 if p_validate then
1056 raise hr_api.validate_enabled;
1057 end if;
1058 --
1059 hr_utility.set_location(' Leaving:'||l_proc, 70);
1060 --
1061 exception
1062 --
1063 when hr_api.validate_enabled then
1064 --
1065 -- As the Validate_Enabled exception has been raised
1066 -- we must rollback to the savepoint
1067 --
1068 ROLLBACK TO delete_PRTN_ELIG_PRFL;
1069 --
1070 -- Only set output warning arguments
1071 -- (Any key or derived arguments must be set to null
1072 -- when validation only mode is being used.)
1073 --
1074 p_effective_start_date := null;
1075 p_effective_end_date := null;
1076 --
1077 when others then
1078 --
1079 -- A validation or unexpected error has occured
1080 --
1081 ROLLBACK TO delete_PRTN_ELIG_PRFL;
1082 p_object_version_number := l_object_version_number;
1083 p_effective_start_date := null;
1084 p_effective_end_date := null;
1085 raise;
1086 --
1087 end delete_PRTN_ELIG_PRFL;
1088 --
1089 -- ----------------------------------------------------------------------------
1090 -- |-------------------------------< lck >------------------------------------|
1091 -- ----------------------------------------------------------------------------
1092 --
1093 procedure lck
1094 (
1095 p_prtn_elig_prfl_id in number
1096 ,p_object_version_number in number
1097 ,p_effective_date in date
1098 ,p_datetrack_mode in varchar2
1099 ,p_validation_start_date out nocopy date
1100 ,p_validation_end_date out nocopy date
1101 ) is
1102 --
1103 --
1104 -- Declare cursors and local variables
1105 --
1106 l_proc varchar2(72) := g_package||'lck';
1107 l_validation_start_date date;
1108 l_validation_end_date date;
1109 --
1110 begin
1111 --
1112 hr_utility.set_location('Entering:'|| l_proc, 10);
1113 --
1114 ben_cep_shd.lck
1115 (
1116 p_prtn_elig_prfl_id => p_prtn_elig_prfl_id
1117 ,p_validation_start_date => l_validation_start_date
1118 ,p_validation_end_date => l_validation_end_date
1119 ,p_object_version_number => p_object_version_number
1120 ,p_effective_date => p_effective_date
1121 ,p_datetrack_mode => p_datetrack_mode
1122 );
1123 --
1124 hr_utility.set_location(' Leaving:'||l_proc, 70);
1125 --
1126 end lck;
1127 --
1128 procedure insert_prtn_elig
1129 (
1130 p_pgm_id in number default null
1131 ,p_pl_id in number default null
1132 ,p_plip_id in number default null
1133 ,p_oipl_id in number default null
1134 ,p_ptip_id in number default null
1135 ,p_effective_date in date
1136 ,p_business_group_id in number default null
1137 ,p_prtn_elig_id out nocopy number
1138 ) is
1139 --
1140 l_effective_start_date date;
1141 l_effective_end_date date;
1142 l_prtn_elig_id number;
1143 l_object_version_number number;
1144 --
1145 cursor c1 is
1146 select prtn_elig_id
1147 from ben_prtn_elig_f
1148 where ( pgm_id = p_pgm_id or p_pgm_id is null ) and
1149 ( pl_id = p_pl_id or p_pl_id is null ) and
1150 ( plip_id = p_plip_id or p_plip_id is null) and
1151 ( ptip_id = p_ptip_id or p_ptip_id is null) and
1152 ( oipl_id = p_oipl_id or p_oipl_id is null) and
1153 effective_start_date <= p_effective_date and
1154 effective_end_date >= p_effective_date;
1155 begin
1156 --
1157 open c1;
1158 fetch c1 into l_prtn_elig_id;
1159 if c1%notfound then
1160 ben_Participation_Elig_api.create_Participation_Elig
1161 ( p_prtn_elig_id =>l_prtn_elig_id
1162 ,p_effective_start_date =>l_effective_start_date
1163 ,p_effective_end_date =>l_effective_end_date
1164 ,p_business_group_id =>p_business_group_id
1165 ,p_pgm_id =>p_pgm_id
1166 ,p_pl_id =>p_pl_id
1167 ,p_oipl_id =>p_oipl_id
1168 ,p_ptip_id =>p_ptip_id
1169 ,p_plip_id =>p_plip_id
1170 ,p_object_version_number =>l_object_version_number
1171 ,p_trk_scr_for_inelg_flag =>'N'
1172 ,p_effective_date =>p_effective_date
1173 ) ;
1174 end if;
1175 close c1;
1176 --
1177 p_prtn_elig_id := l_prtn_elig_id;
1178 --
1179 end;
1180 end ben_PRTN_ELIG_PRFL_api;