[Home] [Help]
PACKAGE BODY: APPS.BEN_XDD_BUS
Source
1 Package Body ben_xdd_bus as
2 /* $Header: bexddrhi.pkb 120.1 2005/06/08 13:09:46 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xdd_bus.'; -- Global package name
9 --
10 -- ---------------------------------------------------------------------------
11 -- |----------------------< set_security_group_id >--------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 Procedure set_security_group_id
15 (p_ext_data_elmt_decd_id in number
16 ) is
17 --
18 -- Declare cursor
19 --
20 cursor csr_sec_grp is
21 select pbg.security_group_id
22 from per_business_groups pbg
23 , ben_ext_data_elmt_decd xdd
24 where xdd.ext_data_elmt_decd_id = p_ext_data_elmt_decd_id
25 and pbg.business_group_id = xdd.business_group_id;
26 --
27 -- Declare local variables
28 --
29 l_security_group_id number;
30 l_proc varchar2(72) := g_package||'set_security_group_id';
31 --
32 begin
33 --
34 hr_utility.set_location('Entering:'|| l_proc, 10);
35 --
36 -- Ensure that all the mandatory parameter are not null
37 --
38 hr_api.mandatory_arg_error
39 (p_api_name => l_proc
40 ,p_argument => 'ext_data_elmt_decd_id'
41 ,p_argument_value => p_ext_data_elmt_decd_id
42 );
43 --
44 open csr_sec_grp;
45 fetch csr_sec_grp into l_security_group_id;
46 --
47 if csr_sec_grp%notfound then
48 --
49 close csr_sec_grp;
50 --
51 -- The primary key is invalid therefore we must error
52 --
53 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
54 fnd_message.raise_error;
55 --
56 end if;
57 close csr_sec_grp;
58 --
59 -- Set the security_group_id in CLIENT_INFO
60 --
61 hr_api.set_security_group_id
62 (p_security_group_id => l_security_group_id
63 );
64 --
65 hr_utility.set_location(' Leaving:'|| l_proc, 20);
66 --
67 end set_security_group_id;
68 --
69 --
70 -- ----------------------------------------------------------------------------
71 -- |------< chk_ext_data_elmt_decd_id >------|
72 -- ----------------------------------------------------------------------------
73 --
74 -- Description
75 -- This procedure is used to check that the primary key for the table
76 -- is created properly. It should be null on insert and
77 -- should not be able to be updated.
78 --
79 -- Pre Conditions
80 -- None.
81 --
82 -- In Parameters
83 -- ext_data_elmt_decd_id PK of record being inserted or updated.
84 -- object_version_number Object version number of record being
85 -- inserted or updated.
86 --
87 -- Post Success
88 -- Processing continues
89 --
90 -- Post Failure
91 -- Errors handled by the procedure
92 --
93 -- Access Status
94 -- Internal table handler use only.
95 --
96 Procedure chk_ext_data_elmt_decd_id(p_ext_data_elmt_decd_id in number,
97 p_object_version_number in number) is
98 --
99 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_decd_id';
100 l_api_updating boolean;
101 --
102 Begin
103 --
104 hr_utility.set_location('Entering:'||l_proc, 5);
105 --
106 l_api_updating := ben_xdd_shd.api_updating
107 (p_ext_data_elmt_decd_id => p_ext_data_elmt_decd_id,
108 p_object_version_number => p_object_version_number);
109 --
110 if (l_api_updating
111 and nvl(p_ext_data_elmt_decd_id,hr_api.g_number)
112 <> ben_xdd_shd.g_old_rec.ext_data_elmt_decd_id) then
113 --
114 -- raise error as PK has changed
115 --
116 ben_xdd_shd.constraint_error('BEN_EXT_DATA_ELMT_DECD_PK');
117 --
118 elsif not l_api_updating then
119 --
120 -- check if PK is null
121 --
122 if p_ext_data_elmt_decd_id is not null then
123 --
124 -- raise error as PK is not null
125 --
126 ben_xdd_shd.constraint_error('BEN_EXT_DATA_ELMT_DECD_PK');
127 --
128 end if;
129 --
130 end if;
131 --
132 hr_utility.set_location('Leaving:'||l_proc, 10);
133 --
134 End chk_ext_data_elmt_decd_id;
135 --
136 -- ----------------------------------------------------------------------------
137 -- |------< chk_ext_data_elmt_id >------|
138 -- ----------------------------------------------------------------------------
139 --
140 -- Description
141 -- This procedure checks that a referenced foreign key actually exists
142 -- in the referenced table.
143 --
144 -- Pre-Conditions
145 -- None.
146 --
147 -- In Parameters
148 -- p_ext_data_elmt_decd_id PK
149 -- p_ext_data_elmt_id ID of FK column
150 -- p_object_version_number object version number
151 --
152 -- Post Success
153 -- Processing continues
154 --
155 -- Post Failure
156 -- Error raised.
157 --
158 -- Access Status
159 -- Internal table handler use only.
160 --
161 Procedure chk_ext_data_elmt_id (p_ext_data_elmt_decd_id in number,
162 p_ext_data_elmt_id in number,
163 p_object_version_number in number) is
164 --
165 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_id';
166 l_api_updating boolean;
167 l_dummy varchar2(1);
168 --
169 cursor c1 is
170 select null
171 from ben_ext_data_elmt a
172 where a.ext_data_elmt_id = p_ext_data_elmt_id;
173 --
174 Begin
175 --
176 hr_utility.set_location('Entering:'||l_proc,5);
177 --
178 l_api_updating := ben_xdd_shd.api_updating
179 (p_ext_data_elmt_decd_id => p_ext_data_elmt_decd_id,
180 p_object_version_number => p_object_version_number);
181 --
182 if (l_api_updating
183 and nvl(p_ext_data_elmt_id,hr_api.g_number)
184 <> nvl(ben_xdd_shd.g_old_rec.ext_data_elmt_id,hr_api.g_number)
185 or not l_api_updating) then
186 --
187 -- check if ext_data_elmt_id value exists in ben_ext_data_elmt table
188 --
189 open c1;
190 --
191 fetch c1 into l_dummy;
192 if c1%notfound then
193 --
194 close c1;
195 --
196 -- raise error as FK does not relate to PK in ben_ext_data_elmt
197 -- table.
198 --
199 ben_xdd_shd.constraint_error('BEN_EXT_DATA_ELMT_DECD_FK1');
200 --
201 end if;
202 --
203 close c1;
204 --
205 end if;
206 --
207 hr_utility.set_location('Leaving:'||l_proc,10);
208 --
209 End chk_ext_data_elmt_id;
210 --
211 -- ----------------------------------------------------------------------------
212 -- |------------------------< chk_val_unique >---------------------------------|
213 -- ----------------------------------------------------------------------------
214 --
215 -- Description
216 -- ensure that not two decode values are same for a decodable field.
217 --
218 -- Pre Conditions
219 -- None.
220 --
221 -- In Parameters
222 -- p_val is decode value
223 -- p_ext_data_elmt_id is data elmt id
224 -- p_business_group_id
225 --
226 -- Post Success
227 -- Processing continues
228 --
229 -- Post Failure
230 -- Errors handled by the procedure
231 --
232 -- Access Status
233 -- Internal table handler use only.
234 --
235 -- ----------------------------------------------------------------------------
236 Procedure chk_val_unique
237 (p_ext_data_elmt_id in number
238 ,p_ext_data_elmt_decd_id in number
239 ,p_val in varchar2
243 is
240 ,p_business_group_id in number
241 ,p_legislation_code in varchar2
242 )
244 l_proc varchar2(72) := g_package||'chk_val_unique';
245 l_dummy char(1);
246 cursor c1 is select null
247 from ben_ext_data_elmt_decd
248 Where ext_data_elmt_id = p_ext_data_elmt_id
249 and ext_data_elmt_decd_id <> nvl(p_ext_data_elmt_decd_id,-1)
250 and val = p_val
251 -- and business_group_id = p_business_group_id
252 and ( (business_group_id is null -- is global
253 and legislation_code is null
254 )
255 or -- is legilsation specific
256 (legislation_code is not null
257 and legislation_code = p_legislation_code)
258 or -- is business group specific
259 (business_group_id is not null
260 and business_group_id = p_business_group_id)
261 )
262 ;
263 --
264 Begin
265 hr_utility.set_location('Entering:'||l_proc, 5);
266 --
267 open c1;
268 fetch c1 into l_dummy;
269 if c1%found then
270 close c1;
271 fnd_message.set_name('BEN','BEN_91931_VAL_NOT_UNQ');
272 fnd_message.raise_error;
273 end if;
274 --
275 close c1;
276 hr_utility.set_location('Leaving:'||l_proc, 15);
277 End chk_val_unique;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |------------------------< chk_ifvalue_replace_null >---------------------------------|
281 -- ----------------------------------------------------------------------------
282 --
283 -- Description
284 -- ensures that if val is present, then dcd_val has to be present.
285 --
286 -- Pre Conditions
287 -- None.
288 --
289 -- In Parameters
290 -- p_val is decode value
291 -- p_dcd_val is the replace value
292 --
293 -- Post Success
294 -- Processing continues
295 --
296 -- Post Failure
297 -- Errors handled by the procedure
298 --
299 -- Access Status
300 -- Internal table handler use only.
301 --
302 -- ----------------------------------------------------------------------------
303 Procedure chk_ifvalue_replace_null
304 ( p_val in varchar2
305 ,p_dcd_val in varchar2)
306 is
307 l_proc varchar2(72) := g_package||'chk_ifvalue_replace_null';
308 --
309 Begin
310 hr_utility.set_location('Entering:'||l_proc, 5);
311 --
312 if p_val is not null and p_dcd_val is null then
313 fnd_message.set_name('BEN','BEN_92119_IFVALUE_REPLACE_ERR');
314 fnd_message.raise_error;
315 end if;
316 --
317 hr_utility.set_location('Leaving:'||l_proc, 15);
318 End chk_ifvalue_replace_null;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |------------------------< chk_replace_ifvalue_null >---------------------------------|
322 -- ----------------------------------------------------------------------------
323 --
324 -- Description
325 -- ensures that if dcd_val is present, then val has to be present.
326 --
327 -- Pre Conditions
328 -- None.
329 --
330 -- In Parameters
331 -- p_val is decode value
332 -- p_dcd_val is the replace value
333 --
334 -- Post Success
335 -- Processing continues
336 --
337 -- Post Failure
338 -- Errors handled by the procedure
339 --
340 -- Access Status
341 -- Internal table handler use only.
342 --
343 -- ----------------------------------------------------------------------------
344 Procedure chk_replace_ifvalue_null
345 ( p_val in varchar2
346 ,p_dcd_val in varchar2)
347 is
348 l_proc varchar2(72) := g_package||'chk_replace_ifvalue_null';
349 --
350 Begin
351 hr_utility.set_location('Entering:'||l_proc, 5);
352 --
353 if p_dcd_val is not null and p_val is null then
354 fnd_message.set_name('BEN','BEN_92120_IFVALUE_REPLACE_ERR');
355 fnd_message.raise_error;
356 end if;
357 --
358 hr_utility.set_location('Leaving:'||l_proc, 15);
359 End chk_replace_ifvalue_null;
360 --
361 -- ----------------------------------------------------------------------------
362 -- |----------------------< chk_startup_action >------------------------------|
363 -- ----------------------------------------------------------------------------
364 --
365 -- Description:
366 -- This procedure will check that the current action is allowed according
367 -- to the current startup mode.
368 --
369 -- ----------------------------------------------------------------------------
370 PROCEDURE chk_startup_action
371 (p_insert IN boolean
372 ,p_business_group_id IN number
373 ,p_legislation_code IN varchar2
374 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
375 --
376 BEGIN
377 --
378 -- Call the supporting procedure to check startup mode
379 --
380 IF (p_insert) THEN
381 --
382 -- Call procedure to check startup_action for inserts.
383 --
384 hr_startup_data_api_support.chk_startup_action
385 (p_generic_allowed => TRUE
386 ,p_startup_allowed => TRUE
387 ,p_user_allowed => TRUE
391 );
388 ,p_business_group_id => p_business_group_id
389 ,p_legislation_code => p_legislation_code
390 ,p_legislation_subgroup => p_legislation_subgroup
392 ELSE
393 --
394 -- Call procedure to check startup_action for updates and deletes.
395 --
396 hr_startup_data_api_support.chk_upd_del_startup_action
397 (p_generic_allowed => TRUE
398 ,p_startup_allowed => TRUE
399 ,p_user_allowed => TRUE
400 ,p_business_group_id => p_business_group_id
401 ,p_legislation_code => p_legislation_code
402 ,p_legislation_subgroup => p_legislation_subgroup
403 );
404 END IF;
405 --
406 END chk_startup_action;
407 --
408 --
409 -- ----------------------------------------------------------------------------
410 -- |---------------------------< insert_validate >----------------------------|
411 -- ----------------------------------------------------------------------------
412 Procedure insert_validate(p_rec in ben_xdd_shd.g_rec_type) is
413 --
414 l_proc varchar2(72) := g_package||'insert_validate';
415 --
416 Begin
417 hr_utility.set_location('Entering:'||l_proc, 5);
418 --
419 -- Call all supporting business operations
420 --
421 --
422 chk_startup_action(True
423 ,p_rec.business_group_id
424 ,p_rec.legislation_code);
425 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
426 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
427 END IF;
428 --
429 chk_ext_data_elmt_decd_id
430 (p_ext_data_elmt_decd_id => p_rec.ext_data_elmt_decd_id,
431 p_object_version_number => p_rec.object_version_number);
432 --
433 chk_ext_data_elmt_id
434 (p_ext_data_elmt_decd_id => p_rec.ext_data_elmt_decd_id,
435 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
436 p_object_version_number => p_rec.object_version_number);
437 --
438 chk_val_unique
439 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
440 p_ext_data_elmt_decd_id => p_rec.ext_data_elmt_decd_id,
441 p_val => p_rec.val,
442 p_business_group_id => p_rec.business_group_id,
443 p_legislation_code => p_rec.legislation_code);
444 --
445 chk_ifvalue_replace_null
446 (p_val => p_rec.val,
447 p_dcd_val => p_rec.dcd_val);
448 --
449 chk_replace_ifvalue_null
450 (p_val => p_rec.val,
451 p_dcd_val => p_rec.dcd_val);
452 --
453 hr_utility.set_location(' Leaving:'||l_proc, 10);
454 End insert_validate;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |---------------------------< update_validate >----------------------------|
458 -- ----------------------------------------------------------------------------
459 Procedure update_validate(p_rec in ben_xdd_shd.g_rec_type) is
460 --
461 l_proc varchar2(72) := g_package||'update_validate';
462 --
463 Begin
464 hr_utility.set_location('Entering:'||l_proc, 5);
465 --
466 -- Call all supporting business operations
467 --
468 --
469 chk_startup_action(False
470 ,p_rec.business_group_id
471 ,p_rec.legislation_code);
472 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
473 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
474 END IF;
475 --
476 chk_ext_data_elmt_decd_id
477 (p_ext_data_elmt_decd_id => p_rec.ext_data_elmt_decd_id,
478 p_object_version_number => p_rec.object_version_number);
479 --
480 chk_ext_data_elmt_id
481 (p_ext_data_elmt_decd_id => p_rec.ext_data_elmt_decd_id,
482 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
483 p_object_version_number => p_rec.object_version_number);
484 --
485 chk_val_unique
486 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
487 p_ext_data_elmt_decd_id => p_rec.ext_data_elmt_decd_id,
488 p_val => p_rec.val,
489 p_business_group_id => p_rec.business_group_id,
490 p_legislation_code => p_rec.legislation_code);
491 --
492 chk_ifvalue_replace_null
493 (p_val => p_rec.val,
494 p_dcd_val => p_rec.dcd_val);
495 --
496 chk_replace_ifvalue_null
497 (p_val => p_rec.val,
498 p_dcd_val => p_rec.dcd_val);
499 --
500 hr_utility.set_location(' Leaving:'||l_proc, 10);
501 End update_validate;
502 --
503 -- ----------------------------------------------------------------------------
504 -- |---------------------------< delete_validate >----------------------------|
505 -- ----------------------------------------------------------------------------
506 Procedure delete_validate(p_rec in ben_xdd_shd.g_rec_type) is
507 --
508 l_proc varchar2(72) := g_package||'delete_validate';
509 --
510 Begin
511 hr_utility.set_location('Entering:'||l_proc, 5);
512 --
513 -- Call all supporting business operations
514 --
515 chk_startup_action(False
516 ,ben_xdd_shd.g_old_rec.business_group_id
517 ,ben_xdd_shd.g_old_rec.legislation_code);
518 --
519 hr_utility.set_location(' Leaving:'||l_proc, 10);
520 End delete_validate;
521 --
522 --
523 -- ---------------------------------------------------------------------------
524 -- |---------------------< return_legislation_code >-------------------------|
528 (p_ext_data_elmt_decd_id in number) return varchar2 is
525 -- ---------------------------------------------------------------------------
526 --
527 function return_legislation_code
529 --
530 -- Declare cursor
531 --
532 cursor csr_leg_code is
533 select a.legislation_code
534 from per_business_groups a,
535 ben_ext_data_elmt_decd b
536 where b.ext_data_elmt_decd_id = p_ext_data_elmt_decd_id
537 and a.business_group_id(+) = b.business_group_id;
538 --
539 -- Declare local variables
540 --
541 l_legislation_code varchar2(150);
542 l_proc varchar2(72) := g_package||'return_legislation_code';
543 --
544 begin
545 --
546 hr_utility.set_location('Entering:'|| l_proc, 10);
547 --
548 -- Ensure that all the mandatory parameter are not null
549 --
550 hr_api.mandatory_arg_error(p_api_name => l_proc,
551 p_argument => 'ext_data_elmt_decd_id',
552 p_argument_value => p_ext_data_elmt_decd_id);
553 --
554 open csr_leg_code;
555 --
556 fetch csr_leg_code into l_legislation_code;
557 --
558 if csr_leg_code%notfound then
559 --
560 close csr_leg_code;
561 --
562 -- The primary key is invalid therefore we must error
563 --
564 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
565 fnd_message.raise_error;
566 --
567 end if;
568 --
569 close csr_leg_code;
570 --
571 hr_utility.set_location(' Leaving:'|| l_proc, 20);
572 --
573 return l_legislation_code;
574 --
575 end return_legislation_code;
576 --
577 end ben_xdd_bus;