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