1 Package Body ben_xel_bus as
2 /* $Header: bexelrhi.pkb 120.1 2005/06/08 13:15:34 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
9 --
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xel_bus.'; -- Global package name
10 -- ---------------------------------------------------------------------------
11 -- |----------------------< set_security_group_id >--------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 Procedure set_security_group_id
15 (p_ext_data_elmt_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 xel
24 where xel.ext_data_elmt_id = p_ext_data_elmt_id
25 and pbg.business_group_id = xel.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_id'
41 ,p_argument_value => p_ext_data_elmt_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_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_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_id(p_ext_data_elmt_id in number,
97 p_object_version_number in number) is
98 --
99 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_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_xel_shd.api_updating
107 (p_ext_data_elmt_id => p_ext_data_elmt_id,
108 p_object_version_number => p_object_version_number);
109 --
110 if (l_api_updating
111 and nvl(p_ext_data_elmt_id,hr_api.g_number)
112 <> ben_xel_shd.g_old_rec.ext_data_elmt_id) then
113 --
114 -- raise error as PK has changed
115 --
116 ben_xel_shd.constraint_error('BEN_EXT_DATA_ELMT_PK');
117 --
118 elsif not l_api_updating then
119 --
120 -- check if PK is null
121 --
122 if p_ext_data_elmt_id is not null then
123 --
124 -- raise error as PK is not null
125 --
126 ben_xel_shd.constraint_error('BEN_EXT_DATA_ELMT_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_id;
135 --
136 -- ----------------------------------------------------------------------------
137 -- |------< chk_frmt_mask_cd >------|
138 -- ----------------------------------------------------------------------------
139 --
140 -- Description
141 -- This procedure is used to check that the lookup value is valid.
142 --
143 -- Pre Conditions
144 -- None.
145 --
146 -- In Parameters
147 -- ext_data_elmt_id PK of record being inserted or updated.
148 -- frmt_mask_cd Value of lookup code.
149 -- effective_date effective date
150 -- object_version_number Object version number of record being
151 -- inserted or updated.
152 --
153 -- Post Success
154 -- Processing continues
155 --
156 -- Post Failure
157 -- Error handled by procedure
158 --
159 -- Access Status
160 -- Internal table handler use only.
161 --
162 Procedure chk_frmt_mask_cd(p_ext_data_elmt_id in number,
163 p_frmt_mask_cd in varchar2,
164 p_effective_date in date,
165 p_business_group_id in number,
166 p_object_version_number in number) is
167 --
168 l_proc varchar2(72) := g_package||'chk_frmt_mask_cd';
169 l_api_updating boolean;
170 frmt_val varchar2(240); -- UTF8
171 invalid_frmt1 exception;
172 invalid_frmt2 exception;
173 Pragma Exception_Init(invalid_frmt1,-01821);
174 Pragma Exception_Init(invalid_frmt2,-01481);
175 p_meaning hr_lookups.meaning%type;
176 p_part_meaning hr_lookups.meaning%type;
177 --
178
179 Begin
180 --
181 hr_utility.set_location('Entering:'||l_proc, 5);
182 --
183 l_api_updating := ben_xel_shd.api_updating
184 (p_ext_data_elmt_id => p_ext_data_elmt_id,
185 p_object_version_number => p_object_version_number);
186 --
187 if (l_api_updating
188 and p_frmt_mask_cd
189 <> nvl(ben_xel_shd.g_old_rec.frmt_mask_cd,hr_api.g_varchar2)
190 or not l_api_updating)
191 and p_frmt_mask_cd is not null then
192 --
193 -- check if value of lookup falls within lookup type.
194 --
195 if p_business_group_id is not null then
196 if hr_api.not_exists_in_hr_lookups
197 (p_lookup_type => 'BEN_EXT_FRMT_MASK',
198 p_lookup_code => p_frmt_mask_cd,
199 p_effective_date => p_effective_date) then
200 --
201 -- raise error as does not exist as lookup
202 --
203 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
204 fnd_message.set_token('FIELD','p_frmt_mask_cd');
205 fnd_message.set_token('TYPE','BEN_EXT_FRMT_MASK');
206 fnd_message.raise_error;
207 --
208 end if;
209 --
210 else
211 if hr_api.not_exists_in_hrstanlookups
212 (p_lookup_type => 'BEN_EXT_FRMT_MASK',
213 p_lookup_code => p_frmt_mask_cd,
214 p_effective_date => p_effective_date) then
215 --
216 -- raise error as does not exist as lookup
217 --
218 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
219 fnd_message.set_token('FIELD','p_frmt_mask_cd');
220 fnd_message.set_token('TYPE','BEN_EXT_FRMT_MASK');
221 fnd_message.raise_error;
222 --
223 end if;
224 --
225 end if;
226 --
227 select meaning into p_meaning from hr_lookups
228 where lookup_type='BEN_EXT_FRMT_MASK'
229 and lookup_code =p_frmt_mask_cd
230 and enabled_flag='Y';
231 if substr(p_frmt_mask_cd,1,1) = 'N' then
232 if substr(p_meaning,length(p_meaning),1) in ('{','}') then
233 p_part_meaning := substr(p_meaning,1,length(p_meaning)-1);
234 else
235 p_part_meaning := p_meaning;
236 end if;
237 select to_char(123456789,p_part_meaning) into frmt_val from dual;
238 elsif substr(p_frmt_mask_cd,1,1) = 'D' then
239 select to_char(sysdate,p_meaning) into frmt_val from dual;
240 elsif substr(p_frmt_mask_cd,1,1) = 'S' then
241 if p_frmt_mask_cd not in ('S1','S2') then
242 fnd_message.set_name('BEN','BEN_91960_FRMT_MSK_UNSPRTD');
243 fnd_message.raise_error;
244 end if;
245 elsif substr(p_frmt_mask_cd,1,1) = 'P' then
246 if p_frmt_mask_cd not in ('P1','P2','P3','P4','P5') then
247 fnd_message.set_name('BEN','BEN_91960_FRMT_MSK_UNSPRTD');
248 fnd_message.raise_error;
249 end if;
250 end if;
251 end if;
252 --
253 hr_utility.set_location('Leaving:'||l_proc,10);
254 --
255 Exception
256 When invalid_frmt1 or invalid_frmt2 then
257 fnd_message.set_name('BEN','BEN_91959_INVLD_FRMT_MSK');
258 fnd_message.raise_error;
259 end chk_frmt_mask_cd;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |------< chk_data_elmt_rl >------|
263 -- ----------------------------------------------------------------------------
264 --
265 -- Description
266 -- This procedure is used to check that the Formula Rule is valid.
267 --
268 -- Pre Conditions
269 -- None.
270 --
271 -- In Parameters
272 -- ext_data_elmt_id PK of record being inserted or updated.
273 -- data_elmt_rl Value of formula rule id.
274 -- effective_date effective date
275 -- object_version_number Object version number of record being
276 -- inserted or updated.
277 --
278 -- Post Success
279 -- Processing continues
280 --
281 -- Post Failure
282 -- Error handled by procedure
283 --
284 -- Access Status
285 -- Internal table handler use only.
286 --
287 Procedure chk_data_elmt_rl(p_ext_data_elmt_id in number,
288 p_data_elmt_rl in number,
289 p_business_group_id in number,
290 p_legislation_code in varchar2,
291 p_effective_date in date,
292 p_object_version_number in number) is
293 --
294 l_proc varchar2(72) := g_package||'chk_data_elmt_rl';
295 l_api_updating boolean;
296 l_dummy varchar2(1);
297 --
298 cursor c1 is
299 select null
300 from ff_formulas_f ff
301 -- ,per_business_groups pbg
302 where ff.formula_id = p_data_elmt_rl
303 and ff.formula_type_id in (-413,-531,-536,-537,-538,-539,-540,-541,-542,-543,-544,-545,-546)
304 -- and pbg.business_group_id = p_business_group_id
305 -- and nvl(ff.business_group_id, p_business_group_id) =
306 -- p_business_group_id
307 -- and nvl(ff.legislation_code, pbg.legislation_code) =
308 -- pbg.legislation_code
309 and ( -- exists globally
310 (business_group_id is null
311 and legislation_code is null
312 )
313 or -- exists within this legilsation
314 (legislation_code is not null
315 and legislation_code = p_legislation_code
316 )
317 or -- exists within this business group
318 (business_group_id is not null
319 and business_group_id = p_business_group_id
320 )
321 )
322 and p_effective_date
323 between ff.effective_start_date
324 and ff.effective_end_date
325 ;
326 --
327 Begin
328 --
329 hr_utility.set_location('Entering:'||l_proc, 5);
330 --
331 l_api_updating := ben_xel_shd.api_updating
332 (p_ext_data_elmt_id => p_ext_data_elmt_id,
333 p_object_version_number => p_object_version_number);
334 --
335 if (l_api_updating
336 and nvl(p_data_elmt_rl,hr_api.g_number)
337 <> ben_xel_shd.g_old_rec.data_elmt_rl
338 or not l_api_updating)
339 and p_data_elmt_rl is not null then
340 --
341 -- check if value of formula rule is valid.
342 --
343 open c1;
344 --
345 -- fetch value from cursor if it returns a record then the
346 -- formula is valid otherwise its invalid
347 --
348 fetch c1 into l_dummy;
349 if c1%notfound then
350 --
351 close c1;
352 --
353 -- raise error
354 --
355 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
356 fnd_message.set_token('ID',p_data_elmt_rl);
357 fnd_message.set_token('TYPE_ID',-413);
358 fnd_message.raise_error;
359 --
360 end if;
361 --
362 close c1;
363 --
364 end if;
365 --
366 hr_utility.set_location('Leaving:'||l_proc,10);
367 --
368 end chk_data_elmt_rl;
369 --
370 -- ----------------------------------------------------------------------------
371 -- |--------------------------< chk_delete_allowed >--------------------------|
372 -- ----------------------------------------------------------------------------
373 --
374 -- Description
375 -- This procedure is used to check that the user can delete the data element
376 -- only if its not in a record layout.
377 --
378 -- Pre Conditions
379 -- None.
380 --
381 -- In Parameters
382 -- ext_data_elmt_id PK of record being inserted or updated.
383 --
384 -- Post Success
385 -- Processing continues
386 --
387 -- Post Failure
388 -- Error handled by procedure
389 --
390 -- Access Status
391 -- Internal table handler use only.
392 --
393 Procedure chk_delete_allowed(p_ext_data_elmt_id in number) is
394 --
395 l_proc varchar2(72) := g_package||'chk_delete_allowed';
396 l_api_updating boolean;
397 l_dummy varchar2(1);
398 --
399 cursor c1 is
400 select null
401 from ben_ext_data_elmt_in_rcd bed
402 where bed.ext_data_elmt_id = p_ext_data_elmt_id;
403 --
404 Begin
405 --
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 open c1;
409 --
410 fetch c1 into l_dummy;
411 if c1%found then
412 --
413 close c1;
414 --
415 -- raise error
416 --
417 fnd_message.set_name('BEN','BEN_92616_XER_RECS_EXIST');
418 fnd_message.raise_error;
419 --
420 end if;
421 --
422 close c1;
423 --
424 hr_utility.set_location('Leaving:'||l_proc,10);
425 --
426 end chk_delete_allowed;
427 -- ----------------------------------------------------------------------------
428 -- |------< chk_max_length_num >------|
429 -- ----------------------------------------------------------------------------
430 --
431 -- Description
432 -- This procedure is used to check that the maximum length number is valid.
433 -- Pre Conditions
434 -- None.
435 --
436 -- In Parameters
437 -- max_length_num of record being inserted or updated.
438 --
439 -- Post Success
440 -- Processing continues
441 --
442 -- Post Failure
443 -- Error handled by procedure
444 --
445 -- Access Status
446 -- Internal table handler use only.
447 --
448 Procedure chk_max_length_num(p_max_length_num in number,
449 p_dflt_val in varchar2) is
450 --
451 l_proc varchar2(72) := g_package||'chk_max_length_num';
452 --
453 Begin
454 --
455 hr_utility.set_location('Entering:'||l_proc, 5);
456 --
457 --
458 if p_max_length_num =0 or p_max_length_num <0 then
459 fnd_message.set_name('BEN','BEN_91865_INVLD_MAX_NUM');
460 fnd_message.raise_error;
461 end if;
462 --
463 if p_max_length_num >0 then
464 if p_dflt_val is not null then
465 if length(p_dflt_val) > p_max_length_num then
466 fnd_message.set_name('BEN','BEN_91866_INVLD_DFLT_VAL');
467 fnd_message.raise_error;
468 end if;
469 end if;
470 end if;
471 hr_utility.set_location('Leaving:'||l_proc,10);
472 --
473 end chk_max_length_num;
474 --
475 -- ----------------------------------------------------------------------------
476 -- |------< chk_data_elmt_typ_cd >------|
477 -- ----------------------------------------------------------------------------
478 --
479 -- Description
480 -- This procedure is used to check that the lookup value is valid.
481 --
482 -- Pre Conditions
483 -- None.
484 --
485 -- In Parameters
486 -- ext_data_elmt_id PK of record being inserted or updated.
487 -- data_elmt_typ_cd Value of lookup code.
488 -- effective_date effective date
489 -- object_version_number Object version number of record being
490 -- inserted or updated.
491 --
492 -- Post Success
493 -- Processing continues
494 --
495 -- Post Failure
496 -- Error handled by procedure
497 --
498 -- Access Status
499 -- Internal table handler use only.
500 --
501 Procedure chk_data_elmt_typ_cd(p_ext_data_elmt_id in number,
502 p_data_elmt_typ_cd in varchar2,
503 p_string_val in varchar2,
504 p_max_length_num in number,
505 p_data_elmt_rl in number,
506 p_ttl_fnctn_cd in varchar2,
507 p_effective_date in date,
508 p_defined_balance_id in number,
509 p_business_group_id in number,
510 p_object_version_number in number) is
511 --
512 l_proc varchar2(72) := g_package||'chk_data_elmt_typ_cd';
513 l_api_updating boolean;
514 --
515 Begin
516 --
517 hr_utility.set_location('Entering:'||l_proc, 5);
518 --
519 l_api_updating := ben_xel_shd.api_updating
520 (p_ext_data_elmt_id => p_ext_data_elmt_id,
521 p_object_version_number => p_object_version_number);
522 --
523 if (l_api_updating
524 and ( ( p_data_elmt_typ_cd <> nvl(ben_xel_shd.g_old_rec.data_elmt_typ_cd,hr_api.g_varchar2))
525 or( nvl(p_string_val,hr_api.g_varchar2) <> nvl(ben_xel_shd.g_old_rec.string_val,hr_api.g_varchar2))
526 or( nvl(p_max_length_num,hr_api.g_number) <> nvl(ben_xel_shd.g_old_rec.max_length_num,hr_api.g_number))
527 or( nvl(p_data_elmt_rl,hr_api.g_number) <> nvl(ben_xel_shd.g_old_rec.data_elmt_rl,hr_api.g_number))
528 or( nvl(p_ttl_fnctn_cd,hr_api.g_varchar2) <> nvl(ben_xel_shd.g_old_rec.ttl_fnctn_cd,hr_api.g_varchar2))
529 or( nvl(p_defined_balance_id,hr_api.g_number) <> nvl(ben_xel_shd.g_old_rec.defined_balance_id,hr_api.g_number))
530 )
531 or not l_api_updating)
532 and p_data_elmt_typ_cd is not null then
533 --
534 -- check if value of lookup falls within lookup type.
535 --
536 hr_utility.set_location('update mode :' || p_string_val ||' '|| ben_xel_shd.g_old_rec.string_val, 5);
537 if p_business_group_id is not null then
538 if hr_api.not_exists_in_hr_lookups
539 (p_lookup_type => 'BEN_EXT_DATA_ELMT_TYP',
540 p_lookup_code => p_data_elmt_typ_cd,
541 p_effective_date => p_effective_date) then
542 --
543 -- raise error as does not exist as lookup
544 --
545 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
546 fnd_message.set_token('FIELD','p_data_elmt_typ_cd');
547 fnd_message.set_token('TYPE','BEN_EXT_DATA_ELMT_TYP');
548 fnd_message.raise_error;
549 --
550 end if;
551 --
552 else
553 if hr_api.not_exists_in_hrstanlookups
554 (p_lookup_type => 'BEN_EXT_DATA_ELMT_TYP',
555 p_lookup_code => p_data_elmt_typ_cd,
556 p_effective_date => p_effective_date) then
557 --
558 -- raise error as does not exist as lookup
559 --
560 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
561 fnd_message.set_token('FIELD','p_data_elmt_typ_cd');
562 fnd_message.set_token('TYPE','BEN_EXT_DATA_ELMT_TYP');
563 fnd_message.raise_error;
564 --
565 end if;
566 --
567 end if;
568 -- allow to enter null for string value
569 -- Need to report fix number of space if the data not avaialble
570 -- this may be need for ANSI
571 if p_data_elmt_typ_cd = 'S' then
572 if p_string_val is null and p_max_length_num is null then
573 fnd_message.set_name('BEN','BEN_91867_STR_VAL_NULL');
574 fnd_message.raise_error;
575 end if;
576 end if;
577
578 if p_data_elmt_typ_cd = 'R' then
579 if p_data_elmt_rl is null then
580 fnd_message.set_name('BEN','BEN_91868_RL_VAL_NULL');
581 fnd_message.raise_error;
582 end if;
583 end if;
584
585 if p_data_elmt_typ_cd = 'T' then
586 if p_ttl_fnctn_cd is null then
587 fnd_message.set_name('BEN','BEN_92138_FNCTN_CD_NULL');
588 fnd_message.raise_error;
589 end if;
590 end if;
591 if p_data_elmt_typ_cd = 'C' then
592 if p_ttl_fnctn_cd is null then
593 fnd_message.set_name('BEN','BEN_92138_FNCTN_CD_NULL');
594 fnd_message.raise_error;
595 end if;
596 end if;
597
598 if p_data_elmt_typ_cd = 'P' then
599 if p_defined_balance_id is null then
600 fnd_message.set_name('BEN','BEN_94248_DEFINE_BAL_NULL');
601 fnd_message.raise_error;
602 end if;
603 end if;
604
605
606 end if;
607 --
608 hr_utility.set_location('Leaving:'||l_proc,10);
609 --
610 end chk_data_elmt_typ_cd;
611 --
612 -- ----------------------------------------------------------------------------
613 -- |------< chk_just_cd >------|
614 -- ----------------------------------------------------------------------------
615 --
616 -- Description
617 -- This procedure is used to check that the lookup value is valid.
618 --
619 -- Pre Conditions
620 -- None.
621 --
622 -- In Parameters
623 -- ext_data_elmt_id PK of record being inserted or updated.
624 -- just_cd Value of lookup code.
625 -- effective_date effective date
626 -- object_version_number Object version number of record being
627 -- inserted or updated.
628 --
629 -- Post Success
630 -- Processing continues
631 --
632 -- Post Failure
633 -- Error handled by procedure
634 --
635 -- Access Status
636 -- Internal table handler use only.
637 --
638 Procedure chk_just_cd(p_ext_data_elmt_id in number,
639 p_just_cd in varchar2,
640 p_effective_date in date,
641 p_business_group_id in varchar2,
642 p_object_version_number in number) is
643 --
644 l_proc varchar2(72) := g_package||'chk_just_cd';
645 l_api_updating boolean;
646 --
647 Begin
648 --
649 hr_utility.set_location('Entering:'||l_proc, 5);
650 --
651 l_api_updating := ben_xel_shd.api_updating
652 (p_ext_data_elmt_id => p_ext_data_elmt_id,
653 p_object_version_number => p_object_version_number);
654 --
655 if (l_api_updating
656 and p_just_cd
657 <> nvl(ben_xel_shd.g_old_rec.just_cd,hr_api.g_varchar2)
658 or not l_api_updating)
659 and p_just_cd is not null then
660 --
661 -- check if value of lookup falls within lookup type.
662 --
663 if p_business_group_id is not null then
664 if hr_api.not_exists_in_hr_lookups
665 (p_lookup_type => 'BEN_EXT_JUST',
666 p_lookup_code => p_just_cd,
667 p_effective_date => p_effective_date) then
668 --
669 -- raise error as does not exist as lookup
670 --
671 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
672 fnd_message.set_token('FIELD','p_just_cd');
673 fnd_message.set_token('TYPE','BEN_EXT_JUST');
674 fnd_message.raise_error;
675 --
676 end if;
677 --
678 else
679 if hr_api.not_exists_in_hrstanlookups
680 (p_lookup_type => 'BEN_EXT_JUST',
681 p_lookup_code => p_just_cd,
682 p_effective_date => p_effective_date) then
683 --
684 -- raise error as does not exist as lookup
685 --
686 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
687 fnd_message.set_token('FIELD','p_just_cd');
688 fnd_message.set_token('TYPE','BEN_EXT_JUST');
689 fnd_message.raise_error;
690 --
691 end if;
692 --
693 end if;
694 --
695 end if;
696 --
697 hr_utility.set_location('Leaving:'||l_proc,10);
698 --
699 end chk_just_cd;
700
701
702
703 -- ----------------------------------------------------------------------------
704 -- |------< chk_just_cd >------|
705 -- ----------------------------------------------------------------------------
706 --
707 -- Description
708 -- This procedure is used to check that the lookup value is valid.
709 --
710 -- Pre Conditions
711 -- None.
712 --
713 -- In Parameters
714 -- ext_data_elmt_id PK of record being inserted or updated.
715 -- just_cd Value of lookup code.
716 -- effective_date effective date
717 -- object_version_number Object version number of record being
718 -- inserted or updated.
719 --
720 -- Post Success
721 -- Processing continues
722 --
723 -- Post Failure
724 -- Error handled by procedure
725 --
726 -- Access Status
727 -- Internal table handler use only.
728 --
729 Procedure chk_defined_balance(p_ext_data_elmt_id in number,
730 p_defined_balance_id in varchar2,
731 p_effective_date in date,
732 p_business_group_id in varchar2,
733 p_object_version_number in number) is
734 --
735 l_proc varchar2(72) := g_package||'chk_defined_balance';
736 l_api_updating boolean;
737 --
738 cursor c is
739 select 'x' from
740 pay_defined_balances
741 where defined_balance_id = p_defined_balance_id
742 ;
743 l_dummy varchar2(1) ;
744 Begin
745 --
746 hr_utility.set_location('Entering:'||l_proc, 5);
747 --
748 l_api_updating := ben_xel_shd.api_updating
749 (p_ext_data_elmt_id => p_ext_data_elmt_id,
750 p_object_version_number => p_object_version_number);
751 --
752 if (l_api_updating
753 and p_defined_balance_id
754 <> nvl(ben_xel_shd.g_old_rec.defined_balance_id,hr_api.g_number)
755 or not l_api_updating)
756 and p_defined_balance_id is not null then
757 --
758 -- check if value of lookup falls within lookup type.
759 --
760 open c ;
761 fetch c into l_dummy ;
762 if c%notfound then
763 close c ;
764 fnd_message.set_name('BEN','BEN_94249_DEFINE_BAL_NOTFOUND');
765 fnd_message.raise_error;
766
767 end if ;
768 close c ;
769 --
770 end if;
771 --
772 hr_utility.set_location('Leaving:'||l_proc,10);
773 --
774 end chk_defined_balance;
775
776 -- ----------------------------------------------------------------------------
777 -- |------------------------< chk_xml_name_format >---------------------------------|
778 -- ----------------------------------------------------------------------------
779 Procedure chk_xml_name_format
780 ( p_xml_tag_name in out nocopy varchar2
781 ) is
782 rgeflg varchar2(1);
783 l_proc varchar2(72) := g_package||'chk_xml_name_format';
784 --
785 Begin
786 hr_utility.set_location('Entering:'||l_proc, 5);
787 --
788
789 if p_xml_tag_name is not null then
790 begin
791 -- Check if name legal format eg no spaces, or special characters
792 hr_chkfmt.checkformat (p_xml_tag_name, 'DB_ITEM_NAME', p_xml_tag_name,
793 null,null,'Y',rgeflg,null);
794 exception
795 when hr_utility.hr_error then
796 hr_utility.set_message (802, 'FFHR_6016_ALL_RES_WORDS');
797 hr_utility.set_message_token(802,'VALUE_NAME','XML Tag');
798 hr_utility.raise_error;
799 end;
800 end if ;
801 --
802 hr_utility.set_location('Leaving:'||l_proc, 15);
803 End chk_xml_name_format;
804 --
805
806 -- ----------------------------------------------------------------------------
807 -- |------------------------< chk_name_unique >---------------------------------|
808 -- ----------------------------------------------------------------------------
809 --
810 -- Description
811 -- ensure that not two data elements have the same name
812 --
813 -- Pre Conditions
814 -- None.
815 --
816 -- In Parameters
817 -- p_name is data element name
818 -- p_ext_data_elmt_id is data elmt id
819 -- p_business_group_id
820 --
821 -- Post Success
822 -- Processing continues
823 --
824 -- Post Failure
825 -- Errors handled by the procedure
826 --
827 -- Access Status
828 -- Internal table handler use only.
829 --
830 -- ----------------------------------------------------------------------------
831 Procedure chk_name_unique
832 (p_ext_data_elmt_id in number
833 ,p_name in varchar2
834 ,p_business_group_id in number
835 ,p_legislation_code in varchar2)
836 is
837 l_proc varchar2(72) := g_package||'chk_name_unique';
838 l_dummy char(1);
839 cursor c1 is select null
840 from ben_ext_data_elmt
841 Where ext_data_elmt_id <> nvl(p_ext_data_elmt_id,-1)
842 and name = p_name
843 -- and business_group_id = p_business_group_id
844 and ( (business_group_id is null -- is unique globally
845 and legislation_code is null
846 )
847 or -- is unique within this legilsation
848 (legislation_code is not null
849 and business_group_id is null
850 and legislation_code = p_legislation_code)
851 or -- is unique within this business group
852 (business_group_id is not null
853 and business_group_id = p_business_group_id)
854 )
855 ;
856 rgeflg varchar2(1);
857
858 --
859 Begin
860 hr_utility.set_location('Entering:'||l_proc, 5);
861 --
862 open c1;
863 fetch c1 into l_dummy;
864 if c1%found then
865 close c1;
866 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
867 fnd_message.raise_error;
868 end if;
869
870 --
871 hr_utility.set_location('Leaving:'||l_proc, 15);
872 End chk_name_unique;
873 --
874 -- ----------------------------------------------------------------------------
875 -- |------------------------< chk_ttl_cond >---------------------------------|
876 -- ----------------------------------------------------------------------------
877 --
878 -- Description
879 -- Check that condition, function, operation have consistent values
880 -- for Totals
881 --
882 -- Pre Conditions
883 -- None.
884 --
885 -- In Parameters
886 -- p_ext_data_elmt_id is data elmt id
887 -- p_ttl_fnctn_cd is value of ttl_fnctn_cd
888 -- p_ttl_sum_ext_data_elmt_id is value of ttl_sum_ext_data_elmt_id
889 -- p_ttl_cond_ext_data_elmt_id is value of ttl_cond_ext_data_elmt_id
890 -- p_ttl_cond_operation_cd is value of ttl_cond_operation_cd
891 -- p_ttl_cond_val is value of ttl_cond_val
892 --
893 -- Post Success
894 -- Processing continues
895 --
896 -- Post Failure
897 -- Errors handled by the procedure
898 --
899 -- Access Status
900 -- Internal table handler use only.
901 --
902 -- ----------------------------------------------------------------------------
903 Procedure chk_ttl_cond
904 ( p_ext_data_elmt_id in number
905 ,p_data_elmt_typ_cd in varchar2
906 ,p_ttl_fnctn_cd in varchar2
907 ,p_ttl_sum_ext_data_elmt_id in number
911 ,p_object_version_number in number )
908 ,p_ttl_cond_ext_data_elmt_id in number
909 ,p_ttl_cond_operation_cd in varchar2
910 ,p_ttl_cond_val in varchar2
912 is
913 l_proc varchar2(72) := g_package||'chk_ttl_cond';
914 l_api_updating boolean;
915 --
916 Begin
917 hr_utility.set_location('Entering:'||l_proc, 5);
918 --
919 l_api_updating := ben_xel_shd.api_updating
920 (p_ext_data_elmt_id => p_ext_data_elmt_id,
921 p_object_version_number => p_object_version_number);
922 --
923 hr_utility.set_location('p_ttl_fnctn_cd:'||p_ttl_fnctn_cd, 15);
924 if (l_api_updating
925 and nvl(p_ttl_fnctn_cd,hr_api.g_varchar2)
926 <> nvl(ben_xel_shd.g_old_rec.ttl_fnctn_cd,hr_api.g_varchar2)
927 or not l_api_updating) then
928 --
929 if p_ttl_fnctn_cd = 'CNT' then
930 if p_ttl_sum_ext_data_elmt_id is not null then
931 fnd_message.set_name('BEN','BEN_92199_CHK_FNCTN_CD');
932 fnd_message.raise_error;
933 end if;
934 elsif p_ttl_fnctn_cd = 'SUM' then
935 if p_ttl_sum_ext_data_elmt_id is null then
936 fnd_message.set_name('BEN','BEN_92418_CHK_FNCTN_CD2');
937 fnd_message.raise_error;
938 end if;
939 if p_ttl_cond_ext_data_elmt_id is null then
940 fnd_message.set_name('BEN','BEN_92419_CHK_FNCTN_CD3');
941 -- if function is 'Sum' then a record must be specified.
942 fnd_message.raise_error;
943 end if;
944
945 elsif p_ttl_fnctn_cd in ( 'ADD','SUB','MLT','DIV' ) then
946 if p_ttl_cond_ext_data_elmt_id is nulL then
947 fnd_message.set_name('BEN','BEN_92419_CHK_FNCTN_CD3');
948 -- if function is 'Sum' then a record must be specified.
949 fnd_message.raise_error;
950 end if;
951
952 elsif p_ttl_fnctn_cd is null then
953 if p_ttl_sum_ext_data_elmt_id is not null then
954 fnd_message.set_name('BEN','BEN_92200_CHK_COND_DATA_ELMT');
955 fnd_message.raise_error;
956 elsif p_ttl_cond_ext_data_elmt_id is not null then
957 fnd_message.set_name('BEN','BEN_92201_CHK_COND_DATA_ELMT');
958 fnd_message.raise_error;
959 /* these fields no longer used
960 elsif p_ttl_cond_operation_cd is not null then
961 fnd_message.set_name('BEN','BEN_92202_CHK_COND_DATA_ELMT');
962 fnd_message.raise_error;
963 elsif p_ttl_cond_val is not null then
964 fnd_message.set_name('BEN','BEN_92203_CHK_COND_DATA_ELMT');
965 fnd_message.raise_error; */
966 elsif p_data_elmt_typ_cd = 'T' then
967 fnd_message.set_name('BEN','BEN_92138_FNCTN_CD_NULL');
968 fnd_message.raise_error;
969 end if;
970 end if;
971 end if;
972 --
973 if (l_api_updating
974 and nvl(p_ttl_sum_ext_data_elmt_id,hr_api.g_number)
975 <> nvl(ben_xel_shd.g_old_rec.ttl_sum_ext_data_elmt_id,hr_api.g_number)
976 or not l_api_updating) then
977 if p_ttl_sum_ext_data_elmt_id is not null and p_ttl_fnctn_cd <> 'SUM' then
978 fnd_message.set_name('BEN','BEN_92200_CHK_SUM_DATA_ELMT');
979 fnd_message.raise_error;
980 elsif p_ttl_sum_ext_data_elmt_id is null and p_ttl_fnctn_cd = 'SUM' then
981 fnd_message.set_name('BEN','BEN_92199_CHK_FNCTN_CD');
982 fnd_message.raise_error;
983 end if;
984
985 end if;
986 hr_utility.set_location('p_ttl_fnctn_cd:'||p_ttl_fnctn_cd, 15);
987 hr_utility.set_location('p_ttl_cond_ext_data_elmt_id:'||p_ttl_cond_ext_data_elmt_id, 15);
988 if (l_api_updating
989 and nvl(p_ttl_cond_ext_data_elmt_id,hr_api.g_number)
990 <> nvl(ben_xel_shd.g_old_rec.ttl_cond_ext_data_elmt_id,hr_api.g_number)
991 or not l_api_updating) then
992 --
993 if p_ttl_cond_ext_data_elmt_id is not null then
994 if p_ttl_fnctn_cd is null then
995 fnd_message.set_name('BEN','BEN_92201_CHK_COND_DATA_ELMT');
996 fnd_message.raise_error;
997 end if;
998 end if;
999 end if;
1000 --
1001 hr_utility.set_location('Leaving:'||l_proc, 15);
1002 End chk_ttl_cond;
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |----------------------< chk_startup_action >------------------------------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 -- Description:
1009 -- This procedure will check that the current action is allowed according
1010 -- to the current startup mode.
1011 --
1012 -- ----------------------------------------------------------------------------
1013 PROCEDURE chk_startup_action
1014 (p_insert IN boolean
1015 ,p_business_group_id IN number
1016 ,p_legislation_code IN varchar2
1017 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1018 --
1019 BEGIN
1020 --
1021 -- Call the supporting procedure to check startup mode
1022 --
1023 IF (p_insert) THEN
1024 --
1025 -- Call procedure to check startup_action for inserts.
1026 --
1027 hr_startup_data_api_support.chk_startup_action
1028 (p_generic_allowed => TRUE
1029 ,p_startup_allowed => TRUE
1030 ,p_user_allowed => TRUE
1031 ,p_business_group_id => p_business_group_id
1032 ,p_legislation_code => p_legislation_code
1033 ,p_legislation_subgroup => p_legislation_subgroup
1034 );
1035 ELSE
1036 --
1037 -- Call procedure to check startup_action for updates and deletes.
1038 --
1039 hr_startup_data_api_support.chk_upd_del_startup_action
1040 (p_generic_allowed => TRUE
1041 ,p_startup_allowed => TRUE
1042 ,p_user_allowed => TRUE
1043 ,p_business_group_id => p_business_group_id
1044 ,p_legislation_code => p_legislation_code
1045 ,p_legislation_subgroup => p_legislation_subgroup
1046 );
1047 END IF;
1048 --
1049 END chk_startup_action;
1050 --
1051 -- ----------------------------------------------------------------------------
1052 -- |---------------------------< insert_validate >----------------------------|
1053 -- ----------------------------------------------------------------------------
1054 Procedure insert_validate(p_rec in ben_xel_shd.g_rec_type
1055 ,p_effective_date in date) is
1056 --
1057 l_proc varchar2(72) := g_package||'insert_validate';
1058
1059 -- Added for Bug fix 2091110
1060 l_legislation_code per_business_groups.legislation_code%TYPE;
1061 --
1062 Begin
1063 hr_utility.set_location('Entering:'||l_proc, 5);
1064 --
1065 -- Call all supporting business operations
1066 --
1067 --
1068 --
1069 -- Bug fix 2091110
1070 -- legislation code should not be the one
1071 -- from p_rec always instead get it for the business group
1072 --
1073 -- Get the legislation code for this business group
1074
1075 IF p_rec.business_group_id IS NOT NULL THEN
1076 l_legislation_code := hr_api.return_legislation_code (
1077 p_business_group_id=> p_rec.business_group_id
1078 );
1079 ELSE
1080 l_legislation_code := p_rec.legislation_code;
1081 END IF; -- End if of bg not null check ...
1082
1083 chk_startup_action(True
1084 ,p_rec.business_group_id
1085 ,l_legislation_code);
1086 -- ,p_rec.legislation_code);
1087 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1088 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1089 END IF;
1090 --
1091 chk_ext_data_elmt_id
1092 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1093 p_object_version_number => p_rec.object_version_number);
1094 --
1095 chk_frmt_mask_cd
1096 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1097 p_frmt_mask_cd => p_rec.frmt_mask_cd,
1098 p_effective_date => p_effective_date,
1099 p_business_group_id => p_rec.business_group_id,
1100 p_object_version_number => p_rec.object_version_number);
1101 --
1102 chk_just_cd
1103 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1104 p_just_cd => p_rec.just_cd,
1105 p_effective_date => p_effective_date,
1106 p_business_group_id => p_rec.business_group_id,
1107 p_object_version_number => p_rec.object_version_number);
1108 --
1109 chk_data_elmt_rl
1110 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1111 p_data_elmt_rl => p_rec.data_elmt_rl,
1112 p_business_group_id => p_rec.business_group_id,
1113 p_legislation_code => l_legislation_code,
1114 -- p_legislation_code => p_rec.legislation_code,
1115 p_effective_date => p_effective_date,
1116 p_object_version_number => p_rec.object_version_number);
1117 --
1118 chk_data_elmt_typ_cd
1119 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1120 p_data_elmt_typ_cd => p_rec.data_elmt_typ_cd,
1121 p_string_val => p_rec.string_val,
1122 p_max_length_num => p_rec.max_length_num,
1123 p_data_elmt_rl => p_rec.data_elmt_rl,
1124 p_ttl_fnctn_cd => p_rec.ttl_fnctn_cd,
1125 p_effective_date => p_effective_date,
1126 p_defined_balance_id => p_rec.defined_balance_id,
1127 p_business_group_id => p_rec.business_group_id,
1128 p_object_version_number => p_rec.object_version_number);
1129 --
1130 chk_max_length_num
1131 (p_max_length_num => p_rec.max_length_num,
1132 p_dflt_val => p_rec.dflt_val);
1133
1134 chk_name_unique
1135 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id
1136 ,p_name => p_rec.name
1137 ,p_business_group_id => p_rec.business_group_id
1138 ,p_legislation_code => l_legislation_code);
1139 -- ,p_legislation_code => p_rec.legislation_code);
1140 --
1141 chk_ttl_cond
1142 ( p_ext_data_elmt_id => p_rec.ext_data_elmt_id
1143 ,p_data_elmt_typ_cd => p_rec.data_elmt_typ_cd
1144 ,p_ttl_fnctn_cd => p_rec.ttl_fnctn_cd
1145 ,p_ttl_sum_ext_data_elmt_id => p_rec.ttl_sum_ext_data_elmt_id
1146 ,p_ttl_cond_ext_data_elmt_id => p_rec.ttl_cond_ext_data_elmt_id
1147 ,p_ttl_cond_operation_cd => p_rec.ttl_cond_oper_cd
1148 ,p_ttl_cond_val => p_rec.ttl_cond_val
1149 ,p_object_version_number => p_rec.object_version_number);
1150 --
1151 chk_defined_balance(p_ext_data_elmt_id => p_rec.ext_data_elmt_id ,
1152 p_defined_balance_id => p_rec.defined_balance_id,
1153 p_effective_date => p_effective_date ,
1154 p_business_group_id => p_rec.business_group_id ,
1155 p_object_version_number => p_rec.object_version_number ) ;
1156
1157 hr_utility.set_location(' Leaving:'||l_proc, 10);
1158 End insert_validate;
1159 --
1160 -- ----------------------------------------------------------------------------
1161 -- |---------------------------< update_validate >----------------------------|
1162 -- ----------------------------------------------------------------------------
1163 Procedure update_validate(p_rec in ben_xel_shd.g_rec_type
1164 ,p_effective_date in date) is
1165 --
1166 l_proc varchar2(72) := g_package||'update_validate';
1167
1168 -- Added for bug fix 2091110
1169 l_legislation_code per_business_groups.legislation_code%TYPE;
1170 --
1171 Begin
1172 hr_utility.set_location('Entering:'||l_proc, 5);
1173 --
1174 -- Call all supporting business operations
1175 --
1176 --
1177 --
1178 -- Bug fix 2091110
1179 -- legislation code should not be the one
1180 -- from p_rec always instead get it for the business group
1181 --
1182 -- Get the legislation code for this business group
1183
1184 IF p_rec.business_group_id IS NOT NULL THEN
1185 l_legislation_code := hr_api.return_legislation_code (
1186 p_business_group_id=> p_rec.business_group_id
1187 );
1188 ELSE
1189 l_legislation_code := p_rec.legislation_code;
1190 END IF; -- End if of bg not null check ...
1191
1192 chk_startup_action(False
1193 ,p_rec.business_group_id
1194 ,l_legislation_code);
1195 -- ,p_rec.legislation_code);
1196 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1197 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1198 END IF;
1199 --
1200 chk_ext_data_elmt_id
1201 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1202 p_object_version_number => p_rec.object_version_number);
1203 --
1204 chk_frmt_mask_cd
1205 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1206 p_frmt_mask_cd => p_rec.frmt_mask_cd,
1207 p_effective_date => p_effective_date,
1208 p_business_group_id => p_rec.business_group_id,
1209 p_object_version_number => p_rec.object_version_number);
1210 --
1211 chk_data_elmt_rl
1212 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1213 p_data_elmt_rl => p_rec.data_elmt_rl,
1214 p_business_group_id => p_rec.business_group_id,
1215 p_legislation_code => l_legislation_code,
1216 -- p_legislation_code => p_rec.legislation_code,
1217 p_effective_date => p_effective_date,
1218 p_object_version_number => p_rec.object_version_number);
1219 --
1220 chk_data_elmt_typ_cd
1221 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1222 p_data_elmt_typ_cd => p_rec.data_elmt_typ_cd,
1223 p_string_val => p_rec.string_val,
1224 p_max_length_num => p_rec.max_length_num,
1225 p_data_elmt_rl => p_rec.data_elmt_rl,
1226 p_ttl_fnctn_cd => p_rec.ttl_fnctn_cd,
1227 p_effective_date => p_effective_date,
1228 p_defined_balance_id => p_rec.defined_balance_id,
1229 p_business_group_id => p_rec.business_group_id,
1230 p_object_version_number => p_rec.object_version_number);
1231 --
1232 chk_just_cd
1233 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1234 p_just_cd => p_rec.just_cd,
1235 p_effective_date => p_effective_date,
1236 p_business_group_id => p_rec.business_group_id,
1237 p_object_version_number => p_rec.object_version_number);
1238 --
1239 chk_max_length_num
1240 (p_max_length_num => p_rec.max_length_num,
1241 p_dflt_val => p_rec.dflt_val);
1242
1243 chk_name_unique
1244 (p_ext_data_elmt_id => p_rec.ext_data_elmt_id
1245 ,p_name => p_rec.name
1246 ,p_business_group_id => p_rec.business_group_id
1247 ,p_legislation_code => l_legislation_code);
1248 -- ,p_legislation_code => p_rec.legislation_code);
1249 --
1250 chk_ttl_cond
1251 ( p_ext_data_elmt_id => p_rec.ext_data_elmt_id
1252 ,p_data_elmt_typ_cd => p_rec.data_elmt_typ_cd
1253 ,p_ttl_fnctn_cd => p_rec.ttl_fnctn_cd
1254 ,p_ttl_sum_ext_data_elmt_id => p_rec.ttl_sum_ext_data_elmt_id
1255 ,p_ttl_cond_ext_data_elmt_id => p_rec.ttl_cond_ext_data_elmt_id
1256 ,p_ttl_cond_operation_cd => p_rec.ttl_cond_oper_cd
1257 ,p_ttl_cond_val => p_rec.ttl_cond_val
1258 ,p_object_version_number => p_rec.object_version_number);
1259
1260 chk_defined_balance(p_ext_data_elmt_id => p_rec.ext_data_elmt_id ,
1261 p_defined_balance_id => p_rec.defined_balance_id,
1262 p_effective_date => p_effective_date ,
1263 p_business_group_id => p_rec.business_group_id ,
1264 p_object_version_number => p_rec.object_version_number ) ;
1265
1266 --
1267 hr_utility.set_location(' Leaving:'||l_proc, 10);
1268 End update_validate;
1269 --
1270 -- ----------------------------------------------------------------------------
1271 -- |---------------------------< delete_validate >----------------------------|
1272 -- ----------------------------------------------------------------------------
1273 Procedure delete_validate(p_rec in ben_xel_shd.g_rec_type
1274 ,p_effective_date in date) is
1275 --
1276 l_proc varchar2(72) := g_package||'delete_validate';
1277
1278 -- Added for Bug fix 2091110
1279 l_legislation_code per_business_groups.legislation_code%TYPE;
1280 --
1281 Begin
1282 hr_utility.set_location('Entering:'||l_proc, 5);
1283 --
1284 -- Call all supporting business operations
1285 --
1286 --
1287 --
1288 -- Bug fix 2091110
1289 -- legislation code should not be the one
1290 -- from p_rec always instead get it for the business group
1291 --
1292 -- Get the legislation code for this business group
1293
1294 IF ben_xel_shd.g_old_rec.business_group_id IS NOT NULL THEN
1295 l_legislation_code := hr_api.return_legislation_code (
1296 p_business_group_id=> ben_xel_shd.g_old_rec.business_group_id
1297 );
1298 ELSE
1299 l_legislation_code := ben_xel_shd.g_old_rec.legislation_code;
1300 END IF; -- End if of bg not null check ...
1301
1302 chk_startup_action(False
1303 ,ben_xel_shd.g_old_rec.business_group_id
1304 ,l_legislation_code);
1305 -- ,ben_xel_shd.g_old_rec.legislation_code);
1306 --
1307 chk_delete_allowed(p_ext_data_elmt_id => p_rec.ext_data_elmt_id);
1308 --
1309 hr_utility.set_location(' Leaving:'||l_proc, 10);
1310 End delete_validate;
1311 --
1312 --
1313 -- ---------------------------------------------------------------------------
1314 -- |---------------------< return_legislation_code >-------------------------|
1315 -- ---------------------------------------------------------------------------
1316 --
1317 function return_legislation_code
1318 (p_ext_data_elmt_id in number) return varchar2 is
1319 --
1320 -- Declare cursor
1321 --
1322 cursor csr_leg_code is
1323 select a.legislation_code
1324 from per_business_groups a,
1325 ben_ext_data_elmt b
1326 where b.ext_data_elmt_id = p_ext_data_elmt_id
1327 and a.business_group_id(+) = b.business_group_id;
1328 --
1329 -- Declare local variables
1330 --
1331 l_legislation_code per_business_groups.legislation_code%type ;
1332 l_proc varchar2(72) := g_package||'return_legislation_code';
1333 --
1334 begin
1335 --
1336 hr_utility.set_location('Entering:'|| l_proc, 10);
1337 --
1338 -- Ensure that all the mandatory parameter are not null
1339 --
1340 hr_api.mandatory_arg_error(p_api_name => l_proc,
1341 p_argument => 'ext_data_elmt_id',
1342 p_argument_value => p_ext_data_elmt_id);
1343 --
1344 open csr_leg_code;
1345 --
1346 fetch csr_leg_code into l_legislation_code;
1347 --
1348 if csr_leg_code%notfound then
1349 --
1350 close csr_leg_code;
1351 --
1352 -- The primary key is invalid therefore we must error
1353 --
1354 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1355 fnd_message.raise_error;
1356 --
1357 end if;
1358 --
1359 close csr_leg_code;
1360 --
1361 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1362 --
1363 return l_legislation_code;
1364 --
1365 end return_legislation_code;
1366 --
1367 end ben_xel_bus;
[Home] [Help]