[Home] [Help]
PACKAGE BODY: APPS.BEN_EAT_BUS
Source
1 Package Body ben_eat_bus as
2 /* $Header: beeatrhi.pkb 115.11 2002/12/16 11:53:54 vsethi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_eat_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_actn_typ_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- actn_typ_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_actn_typ_id(p_actn_typ_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_actn_typ_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_eat_shd.api_updating
47 (p_actn_typ_id => p_actn_typ_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_actn_typ_id,hr_api.g_number)
52 <> ben_eat_shd.g_old_rec.actn_typ_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_eat_shd.constraint_error('BEN_ACTN_TYP_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_actn_typ_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_eat_shd.constraint_error('BEN_ACTN_TYP_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_actn_typ_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_type_cd >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure is used to check that the lookup value is valid.
82 --
83 -- Pre Conditions
84 -- None.
85 --
86 -- In Parameters
87 -- actn_typ_id PK of record being inserted or updated.
88 -- type_cd Value of lookup code.
89 -- effective_date effective date
90 -- object_version_number Object version number of record being
91 -- inserted or updated.
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error handled by procedure
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_type_cd(p_actn_typ_id in number,
103 p_type_cd in varchar2,
104 p_business_group_id in number,
105 p_effective_date in date,
106 p_object_version_number in number) is
107 --
108 cursor l_csr_eat is
109 SELECT 'x'
110 FROM ben_actn_typ
111 WHERE type_cd = nvl(p_type_cd, hr_api.g_varchar2)
112 AND business_group_id + 0 = p_business_group_id;
113 --
114 l_db_eat_row l_csr_eat%rowtype;
115 l_proc varchar2(72) := g_package||'chk_type_cd';
116 l_api_updating boolean;
117 l_table_name all_tables.table_name%TYPE;
118 l_dummy varchar2(1);
119 --
120
121 Begin
122 --
123 hr_utility.set_location('Entering:'||l_proc, 5);
124 --
125 l_api_updating := ben_eat_shd.api_updating
126 (p_actn_typ_id => p_actn_typ_id,
127 p_object_version_number => p_object_version_number);
128 --
129 if (l_api_updating and
130 ben_eat_shd.g_old_rec.type_cd in ('BNF', 'BNFADDNL', 'BNFADDR', 'BNFCTFN',
131 'BNFDOB', 'BNFSSN','BNFTTEE', 'DD', 'DDADDNL','DDADDR','DDCTFN',
132 'DDDOB', 'DDSSN', 'LEECTFN', 'WVPRTNCTFN', 'ENRTCTFN', 'PC', 'TA')
133 and nvl(p_type_cd,hr_api.g_varchar2) <> ben_eat_shd.g_old_rec.type_cd) then
134 -- The user is not allowed to change these System Enrollment Action TYPES
135 --
136 fnd_message.set_name('BEN','BEN_91449_ACTN_TYP_CHG');
137 fnd_message.raise_error;
138 end if;
139
140 if nvl(p_type_cd,hr_api.g_varchar2)
141 <> nvl(ben_eat_shd.g_old_rec.type_cd,hr_api.g_varchar2)
142 and
143 p_type_cd in ('BNF', 'BNFADDNL', 'BNFADDR', 'BNFCTFN',
144 'BNFDOB', 'BNFSSN', 'BNFTTEE', 'DD', 'DDADDNL','DDADDR','DDCTFN',
145 'DDDOB', 'DDSSN', 'LEECTFN', 'WVPRTNCTFN', 'ENRTCTFN', 'PC', 'TA') then
146 -- Check to see if a eat already exists of this type. If so, do not
147 -- allow creation of it. If not, allow creation.
148 open l_csr_eat;
149 fetch l_csr_eat into l_db_eat_row;
150 if l_csr_eat%found then
151 close l_csr_eat;
152 -- The user is not allowed to create Actions of System type.
153 fnd_message.set_name('BEN','BEN_91450_ACTN_TYP_INS');
154 fnd_message.raise_error;
155 else
156 close l_csr_eat;
157 end if;
158 end if;
159
160
161 if (l_api_updating
162 and p_type_cd
163 <> nvl(ben_eat_shd.g_old_rec.type_cd,hr_api.g_varchar2)
164 or not l_api_updating)
165 and p_type_cd is not null then
166 --
167 -- check if value of lookup falls within lookup type.
168 --
169 if hr_api.not_exists_in_hr_lookups
170 (p_lookup_type => 'BEN_ACTN_TYP',
171 p_lookup_code => p_type_cd,
172 p_effective_date => p_effective_date) then
173 --
174 -- raise error as does not exist as lookup
175 --
176 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
177 fnd_message.set_token('FIELD','p_type_cd');
178 fnd_message.set_token('TYPE','BEN_ACTN_TYP');
179 fnd_message.raise_error;
180 --
181 end if;
182 --
183 end if;
184 --
185 -- Only allow the Type Code to change if the record is not being used in any
186 -- foreign keys. CAN change the type from null to something though.
187 if (l_api_updating
188 and nvl(p_type_cd,hr_api.g_varchar2)
189 <> nvl(ben_eat_shd.g_old_rec.type_cd,hr_api.g_varchar2)
190 and ben_eat_shd.g_old_rec.type_cd is not null) then
191 null;
192 --
193 declare
194 cursor c1 is select null
195 from ben_prtt_enrt_actn_f
196 where actn_typ_id = p_actn_typ_id;
197 begin
198 open c1;
199 fetch c1 into l_dummy;
200 if c1%found then
201 fnd_message.set_name('BEN','BEN_91424_DERIV_TYPE_INS');
202 fnd_message.raise_error;
203 end if;
204 end;
205
206 end if;
207 hr_utility.set_location('Leaving:'||l_proc,10);
208 --
209 end chk_type_cd;
210 --
211 -- ----------------------------------------------------------------------------
212 -- |------< chk_name >------|
213 -- ----------------------------------------------------------------------------
214 --
215 -- Description
216 -- This procedure checks that a name is unique.
217 --
218 --
219 -- Pre Conditions
220 -- None.
221 --
222 -- In Parameters
223 -- p_actn_typ_id PK
224 -- p_organization_id ID of FK column
225 -- p_effective_date Session date of record
226 -- p_object_version_number Object version number
227 --
228 -- Post Success
229 -- Processing continues
230 --
231 -- Post Failure
232 -- Error raised
233 --
234 -- Access Status
235 -- Internal table handler use only.
236 --
237 Procedure chk_name(p_actn_typ_id in number,
238 p_effective_date in date,
239 p_name in varchar2,
240 p_business_group_id in number,
241 p_object_version_number in number) is
242 --
243 l_proc varchar2(72) := g_package||'chk_name';
244 l_api_updating boolean;
245 l_dummy varchar2(1);
246 --
247 cursor c1 is
248 select null
249 from ben_actn_typ
250 where name = p_name
251 and p_actn_typ_id <> nvl(p_actn_typ_id, hr_api.g_number)
252 and business_group_id + 0 = p_business_group_id;
253 --
254 Begin
255 --
256 hr_utility.set_location('Entering:'||l_proc, 5);
257 --
258 l_api_updating := ben_eat_shd.api_updating
259 (p_actn_typ_id => p_actn_typ_id,
260 p_object_version_number => p_object_version_number);
261 --
262 if (l_api_updating
263 and p_name <> ben_eat_shd.g_old_rec.name) or
264 not l_api_updating then
265 --
266 -- check if name already exists
267 --
268 open c1;
269 --
270 fetch c1 into l_dummy;
271 if c1%found then
272 --
273 close c1;
274 --
275 -- raise error as Name must be Unique
276 --
277 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
278 fnd_message.raise_error;
279 --
280 end if;
281 --
282 close c1;
283 --
284 end if;
285 --
286 hr_utility.set_location('Leaving:'||l_proc,10);
287 --
288 end chk_name;
289 -- --
290 --
291 -- Procedure Added chk_actn_typ_sys_del, Bug 2366282
292 -- ----------------------------------------------------------------------------
293 -- |-----------------------< chk_actn_typ_sys_del >---------------------------|
294 -- ----------------------------------------------------------------------------
295 --
296 -- Description
297 -- This procedure checks whether the record being deleted
298 -- has a system defined action type.
299 --
300 -- Pre Conditions
301 -- None.
302 --
303 -- In Parameters
304 -- p_actn_typ_id PK
305 -- p_object_version_number Object version number
306 --
307 -- Post Success
308 -- Processing continues
309 --
310 -- Post Failure
311 -- Error raised
312 --
313 -- Access Status
314 -- Internal table handler use only.
315 --
316 Procedure chk_actn_typ_sys_del(p_actn_typ_id in number,
317 p_object_version_number in number) is
318 --
319 l_proc varchar2(72) := g_package||'chk_actn_typ_sys_del';
320 l_api_updating boolean;
321 l_type_cd varchar2(30);
322 --
323 cursor c_type_cd is
324 select type_cd
325 from ben_actn_typ
326 where actn_typ_id = p_actn_typ_id;
327 --
328 Begin
329 --
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332 l_api_updating := ben_eat_shd.api_updating
333 (p_actn_typ_id => p_actn_typ_id,
334 p_object_version_number => p_object_version_number);
335 --
336 open c_type_cd;
337 fetch c_type_cd into l_type_cd;
338 --
339 if(l_api_updating and
340 l_type_cd in ('BNF', 'BNFADDNL', 'BNFADDR', 'BNFCTFN', 'BNFDOB','BNFSSN',
341 'BNFTTEE', 'DD', 'DDADDNL','DDADDR','DDCTFN','DDDOB', 'DDSSN',
342 'LEECTFN', 'WVPRTNCTFN', 'ENRTCTFN', 'PC', 'TA')) then
343 close c_type_cd;
344 --
345 -- raise error as System Defined Actions Types cannot be Deleted
346 --
347 fnd_message.set_name('PAY','HR_6044_STARTUP_CANNOT_DELETE');
348 fnd_message.raise_error;
349 --
350 end if;
351 --
352 close c_type_cd;
353 --
354 hr_utility.set_location('Leaving:'||l_proc,10);
355 --
356 end chk_actn_typ_sys_del;
357 --
358 -- End of fix, Bug 2366282
359 -- --
360 -- ----------------------------------------------------------------------------
361 -- |---------------------------< insert_validate >----------------------------|
362 -- ----------------------------------------------------------------------------
363 Procedure insert_validate(p_rec in ben_eat_shd.g_rec_type
364 ,p_effective_date in date) is
365 --
366 l_proc varchar2(72) := g_package||'insert_validate';
367 --
368 Begin
369 hr_utility.set_location('Entering:'||l_proc, 5);
370 --
371 -- Call all supporting business operations
372 --
373 --
374 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
375 --
376 chk_actn_typ_id
377 (p_actn_typ_id => p_rec.actn_typ_id,
378 p_object_version_number => p_rec.object_version_number);
379 --
380 chk_type_cd
381 (p_actn_typ_id => p_rec.actn_typ_id,
382 p_type_cd => p_rec.type_cd,
383 p_business_group_id => p_rec.business_group_id,
384 p_effective_date => p_effective_date,
385 p_object_version_number => p_rec.object_version_number);
386 --
387 chk_name
388 (p_actn_typ_id => p_rec.actn_typ_id,
389 p_effective_date => p_effective_date,
390 p_name => p_rec.name,
394 --
391 p_business_group_id => p_rec.business_group_id,
392 p_object_version_number => p_rec.object_version_number);
393
395 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
396 --
397 hr_utility.set_location(' Leaving:'||l_proc, 10);
398 End insert_validate;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |---------------------------< update_validate >----------------------------|
402 -- ----------------------------------------------------------------------------
403 Procedure update_validate(p_rec in ben_eat_shd.g_rec_type
404 ,p_effective_date in date) is
405 --
406 l_proc varchar2(72) := g_package||'update_validate';
407 --
408 Begin
409 hr_utility.set_location('Entering:'||l_proc, 5);
410 --
411 -- Call all supporting business operations
412 --
413 --
414 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
415 --
416 chk_actn_typ_id
417 (p_actn_typ_id => p_rec.actn_typ_id,
418 p_object_version_number => p_rec.object_version_number);
419 --
420 chk_type_cd
421 (p_actn_typ_id => p_rec.actn_typ_id,
422 p_type_cd => p_rec.type_cd,
423 p_business_group_id => p_rec.business_group_id,
424 p_effective_date => p_effective_date,
425 p_object_version_number => p_rec.object_version_number);
426 --
427 chk_name
428 (p_actn_typ_id => p_rec.actn_typ_id,
429 p_effective_date => p_effective_date,
430 p_name => p_rec.name,
431 p_business_group_id => p_rec.business_group_id,
432 p_object_version_number => p_rec.object_version_number);
433 --
434 hr_utility.set_location(' Leaving:'||l_proc, 10);
435 End update_validate;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |---------------------------< delete_validate >----------------------------|
439 -- ----------------------------------------------------------------------------
440 Procedure delete_validate(p_rec in ben_eat_shd.g_rec_type
441 ,p_effective_date in date) is
442 --
443 l_proc varchar2(72) := g_package||'delete_validate';
444 --
445 Begin
446 hr_utility.set_location('Entering:'||l_proc, 5);
447 --
448 -- Call all supporting business operations
449 --
450 chk_actn_typ_sys_del -- Bug 2366282
451 (p_actn_typ_id => p_rec.actn_typ_id,
452 p_object_version_number => p_rec.object_version_number); -- Bug 2366282
453 --
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 End delete_validate;
456 --
457 --
458 -- ---------------------------------------------------------------------------
459 -- |---------------------< return_legislation_code >-------------------------|
460 -- ---------------------------------------------------------------------------
461 --
462 function return_legislation_code
463 (p_actn_typ_id in number) return varchar2 is
464 --
465 -- Declare cursor
466 --
467 cursor csr_leg_code is
468 select a.legislation_code
469 from per_business_groups a,
470 ben_actn_typ b
471 where b.actn_typ_id = p_actn_typ_id
472 and a.business_group_id = b.business_group_id;
473 --
474 -- Declare local variables
475 --
476 l_legislation_code varchar2(150);
477 l_proc varchar2(72) := g_package||'return_legislation_code';
478 --
479 begin
480 --
481 hr_utility.set_location('Entering:'|| l_proc, 10);
482 --
483 -- Ensure that all the mandatory parameter are not null
484 --
485 hr_api.mandatory_arg_error(p_api_name => l_proc,
486 p_argument => 'actn_typ_id',
487 p_argument_value => p_actn_typ_id);
488 --
489 open csr_leg_code;
490 --
491 fetch csr_leg_code into l_legislation_code;
492 --
493 if csr_leg_code%notfound then
494 --
495 close csr_leg_code;
496 --
497 -- The primary key is invalid therefore we must error
498 --
499 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
500 hr_utility.raise_error;
501 --
502 end if;
503 --
504 close csr_leg_code;
505 --
506 hr_utility.set_location(' Leaving:'|| l_proc, 20);
507 --
508 return l_legislation_code;
509 --
510 end return_legislation_code;
511 --
512 end ben_eat_bus;