[Home] [Help]
PACKAGE BODY: APPS.BEN_XWC_BUS
Source
1 Package Body ben_xwc_bus as
2 /* $Header: bexwcrhi.pkb 120.3 2006/04/27 11:31:07 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xwc_bus.'; -- Global package name
9
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------< chk_startup_action >------------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- Description:
16 -- This procedure will check that the current action is allowed according
17 -- to the current startup mode.
18 --
19 -- ----------------------------------------------------------------------------
20 PROCEDURE chk_startup_action
21 (p_insert IN boolean
22 ,p_business_group_id IN number
23 ,p_legislation_code IN varchar2
24 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
25 --
26 BEGIN
27 --
28 -- Call the supporting procedure to check startup mode
29 --
30 IF (p_insert) THEN
31 --
32 -- Call procedure to check startup_action for inserts.
33 --
34 hr_startup_data_api_support.chk_startup_action
35 (p_generic_allowed => TRUE
36 ,p_startup_allowed => TRUE
37 ,p_user_allowed => TRUE
38 ,p_business_group_id => p_business_group_id
42 ELSE
39 ,p_legislation_code => p_legislation_code
40 ,p_legislation_subgroup => p_legislation_subgroup
41 );
43 --
44 -- Call procedure to check startup_action for updates and deletes.
45 --
46 hr_startup_data_api_support.chk_upd_del_startup_action
47 (p_generic_allowed => TRUE
48 ,p_startup_allowed => TRUE
49 ,p_user_allowed => TRUE
50 ,p_business_group_id => p_business_group_id
51 ,p_legislation_code => p_legislation_code
52 ,p_legislation_subgroup => p_legislation_subgroup
53 );
54 END IF;
55 --
56 END chk_startup_action;
57
58 --
59 -- ---------------------------------------------------------------------------
60 -- |----------------------< set_security_group_id >--------------------------|
61 -- ---------------------------------------------------------------------------
62 --
63 Procedure set_security_group_id
64 (p_ext_where_clause_id in number
65 ) is
66 --
67 -- Declare cursor
68 --
69 cursor csr_sec_grp is
70 select pbg.security_group_id
71 from per_business_groups pbg
72 , ben_ext_where_clause xwc
73 where xwc.ext_where_clause_id = p_ext_where_clause_id
74 and pbg.business_group_id = xwc.business_group_id;
75 --
76 -- Declare local variables
77 --
78 l_security_group_id number;
79 l_proc varchar2(72) := g_package||'set_security_group_id';
80 --
81 begin
82 --
83 hr_utility.set_location('Entering:'|| l_proc, 10);
84 --
85 -- Ensure that all the mandatory parameter are not null
86 --
87 hr_api.mandatory_arg_error
88 (p_api_name => l_proc
89 ,p_argument => 'ext_where_clause_id'
90 ,p_argument_value => p_ext_where_clause_id
91 );
92 --
93 open csr_sec_grp;
94 fetch csr_sec_grp into l_security_group_id;
95 --
96 if csr_sec_grp%notfound then
97 --
98 close csr_sec_grp;
99 --
100 -- The primary key is invalid therefore we must error
101 --
102 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
103 fnd_message.raise_error;
104 --
105 end if;
106 close csr_sec_grp;
107 --
108 -- Set the security_group_id in CLIENT_INFO
109 --
110 hr_api.set_security_group_id
111 (p_security_group_id => l_security_group_id
112 );
113 --
114 hr_utility.set_location(' Leaving:'|| l_proc, 20);
115 --
116 end set_security_group_id;
117
118 --
119 -- ----------------------------------------------------------------------------
120 -- |------< chk_ext_where_clause_id >------|
121 -- ----------------------------------------------------------------------------
122 --
123 -- Description
124 -- This procedure is used to check that the primary key for the table
125 -- is created properly. It should be null on insert and
126 -- should not be able to be updated.
127 --
128 -- Pre Conditions
129 -- None.
130 --
131 -- In Parameters
132 -- ext_where_clause_id PK of record being inserted or updated.
133 -- object_version_number Object version number of record being
134 -- inserted or updated.
135 --
136 -- Post Success
137 -- Processing continues
138 --
139 -- Post Failure
140 -- Errors handled by the procedure
141 --
142 -- Access Status
143 -- Internal table handler use only.
144 --
145 Procedure chk_ext_where_clause_id(p_ext_where_clause_id in number,
146 p_object_version_number in number) is
147 --
148 l_proc varchar2(72) := g_package||'chk_ext_where_clause_id';
149 l_api_updating boolean;
150 --
151 Begin
152 --
153 hr_utility.set_location('Entering:'||l_proc, 5);
154 --
155 l_api_updating := ben_xwc_shd.api_updating
156 (p_ext_where_clause_id => p_ext_where_clause_id,
157 p_object_version_number => p_object_version_number);
158 --
159 if (l_api_updating
160 and nvl(p_ext_where_clause_id,hr_api.g_number)
161 <> ben_xwc_shd.g_old_rec.ext_where_clause_id) then
162 --
163 -- raise error as PK has changed
164 --
165 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_PK');
166 --
167 elsif not l_api_updating then
168 --
169 -- check if PK is null
170 --
171 if p_ext_where_clause_id is not null then
172 --
173 -- raise error as PK is not null
174 --
175 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_PK');
176 --
177 end if;
178 --
179 end if;
180 --
181 hr_utility.set_location('Leaving:'||l_proc, 10);
182 --
183 End chk_ext_where_clause_id;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |------< chk_cond_ext_elmt_in_rcd_id >------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- Description
190 -- This procedure checks that a referenced foreign key actually exists
191 -- in the referenced table.
192 --
193 -- Pre-Conditions
194 -- None.
195 --
196 -- In Parameters
197 -- p_ext_where_clause_id PK
198 -- p_cond_ext_data_elmt_in_rcd_id ID of FK column
199 -- p_object_version_number object version number
200 --
204 -- Post Failure
201 -- Post Success
202 -- Processing continues
203 --
205 -- Error raised.
206 --
207 -- Access Status
208 -- Internal table handler use only.
209 --
210 Procedure chk_cond_ext_elmt_in_rcd_id (p_ext_where_clause_id in number,
211 p_cond_ext_data_elmt_in_rcd_id in number,
212 p_object_version_number in number) is
213 --
214 l_proc varchar2(72) := g_package||'chk_cond_ext_elmt_in_rcd_id';
215 l_api_updating boolean;
216 l_dummy varchar2(1);
217 --
218 cursor c1 is
219 select null
220 from ben_ext_data_elmt_in_rcd a
221 where a.ext_data_elmt_in_rcd_id = p_cond_ext_data_elmt_in_rcd_id;
222 --
223 Begin
224 --
225 hr_utility.set_location('Entering:'||l_proc,5);
226 --
227 l_api_updating := ben_xwc_shd.api_updating
228 (p_ext_where_clause_id => p_ext_where_clause_id,
229 p_object_version_number => p_object_version_number);
230 --
231 if (l_api_updating
232 and nvl(p_cond_ext_data_elmt_in_rcd_id,hr_api.g_number)
233 <> nvl(ben_xwc_shd.g_old_rec.cond_ext_data_elmt_in_rcd_id,hr_api.g_number)
234 or not l_api_updating) and
235 p_cond_ext_data_elmt_in_rcd_id is not null then
236 --
237 -- check if cond_ext_data_elmt_in_rcd_id value exists in ben_ext_data_elmt_in_rcd table
238 --
239 open c1;
240 --
241 fetch c1 into l_dummy;
242 if c1%notfound then
243 --
244 close c1;
245 --
246 -- raise error as FK does not relate to PK in ben_ext_data_elmt_in_rcd
247 -- table.
248 --
249 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_FK6');
250 --
251 end if;
252 --
253 close c1;
254 --
255 end if;
256 --
257 hr_utility.set_location('Leaving:'||l_proc,10);
258 --
259 End chk_cond_ext_elmt_in_rcd_id;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |------< chk_ext_rcd_in_file_id >------|
263 -- ----------------------------------------------------------------------------
264 --
265 -- Description
266 -- This procedure checks that a referenced foreign key actually exists
267 -- in the referenced table.
268 --
269 -- Pre-Conditions
270 -- None.
271 --
272 -- In Parameters
273 -- p_ext_where_clause_id PK
274 -- p_ext_rcd_in_file_id ID of FK column
275 -- p_object_version_number object version number
276 --
277 -- Post Success
278 -- Processing continues
279 --
280 -- Post Failure
281 -- Error raised.
282 --
283 -- Access Status
284 -- Internal table handler use only.
285 --
286 Procedure chk_ext_rcd_in_file_id (p_ext_where_clause_id in number,
287 p_ext_rcd_in_file_id in number,
288 p_object_version_number in number) is
289 --
290 l_proc varchar2(72) := g_package||'chk_ext_rcd_in_file_id';
291 l_api_updating boolean;
292 l_dummy varchar2(1);
293 --
294 cursor c1 is
295 select null
296 from ben_ext_rcd_in_file a
297 where a.ext_rcd_in_file_id = p_ext_rcd_in_file_id;
298 --
299 Begin
300 --
301 hr_utility.set_location('Entering:'||l_proc,5);
302 --
303 l_api_updating := ben_xwc_shd.api_updating
304 (p_ext_where_clause_id => p_ext_where_clause_id,
305 p_object_version_number => p_object_version_number);
306 --
307 if (l_api_updating
308 and nvl(p_ext_rcd_in_file_id,hr_api.g_number)
309 <> nvl(ben_xwc_shd.g_old_rec.ext_rcd_in_file_id,hr_api.g_number)
310 or not l_api_updating) and
311 p_ext_rcd_in_file_id is not null then
312 --
313 -- check if ext_rcd_in_file_id value exists in ben_ext_rcd_in_file table
314 --
315 open c1;
316 --
317 fetch c1 into l_dummy;
318 if c1%notfound then
319 --
320 close c1;
321 --
322 -- raise error as FK does not relate to PK in ben_ext_rcd_in_file
323 -- table.
324 --
325 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_FK4');
326 --
327 end if;
328 --
329 close c1;
330 --
331 end if;
332 --
333 hr_utility.set_location('Leaving:'||l_proc,10);
334 --
335 End chk_ext_rcd_in_file_id;
336 --
337 -- ----------------------------------------------------------------------------
338 -- |------< chk_ext_data_elmt_in_rcd_id >------|
339 -- ----------------------------------------------------------------------------
340 --
341 -- Description
342 -- This procedure checks that a referenced foreign key actually exists
343 -- in the referenced table.
344 --
345 -- Pre-Conditions
346 -- None.
347 --
348 -- In Parameters
349 -- p_ext_where_clause_id PK
350 -- p_ext_data_elmt_in_rcd_id ID of FK column
351 -- p_object_version_number object version number
352 --
353 -- Post Success
354 -- Processing continues
355 --
356 -- Post Failure
357 -- Error raised.
358 --
359 -- Access Status
360 -- Internal table handler use only.
361 --
362 Procedure chk_ext_data_elmt_in_rcd_id (p_ext_where_clause_id in number,
363 p_ext_data_elmt_in_rcd_id in number,
367 l_api_updating boolean;
364 p_object_version_number in number) is
365 --
366 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_in_rcd_id';
368 l_dummy varchar2(1);
369 --
370 cursor c1 is
371 select null
372 from ben_ext_data_elmt_in_rcd a
373 where a.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id;
374 --
375 Begin
376 --
377 hr_utility.set_location('Entering:'||l_proc,5);
378 --
379 l_api_updating := ben_xwc_shd.api_updating
380 (p_ext_where_clause_id => p_ext_where_clause_id,
381 p_object_version_number => p_object_version_number);
382 --
383 if (l_api_updating
384 and nvl(p_ext_data_elmt_in_rcd_id,hr_api.g_number)
385 <> nvl(ben_xwc_shd.g_old_rec.ext_data_elmt_in_rcd_id,hr_api.g_number)
386 or not l_api_updating) and
387 p_ext_data_elmt_in_rcd_id is not null then
388 --
389 -- check if ext_data_elmt_in_rcd_id value exists in ben_ext_data_elmt_in_rcd table
390 --
391 open c1;
392 --
393 fetch c1 into l_dummy;
394 if c1%notfound then
395 --
396 close c1;
397 --
398 -- raise error as FK does not relate to PK in ben_ext_data_elmt_in_rcd
399 -- table.
400 --
401 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_FK3');
402 --
403 end if;
404 --
405 close c1;
406 --
407 end if;
408 --
409 hr_utility.set_location('Leaving:'||l_proc,10);
410 --
411 End chk_ext_data_elmt_in_rcd_id;
412 --
413 -- ----------------------------------------------------------------------------
414 -- |------< chk_cond_ext_data_elmt_id >------|
415 -- ----------------------------------------------------------------------------
416 --
417 -- Description
418 -- This procedure checks that a referenced foreign key actually exists
419 -- in the referenced table.
420 --
421 -- Pre-Conditions
422 -- None.
423 --
424 -- In Parameters
425 -- p_ext_where_clause_id PK
426 -- p_cond_ext_data_elmt_id ID of FK column
427 -- p_object_version_number object version number
428 --
429 -- Post Success
430 -- Processing continues
431 --
432 -- Post Failure
433 -- Error raised.
434 --
435 -- Access Status
436 -- Internal table handler use only.
437 --
438 Procedure chk_cond_ext_data_elmt_id (p_ext_where_clause_id in number,
439 p_cond_ext_data_elmt_id in number,
440 p_object_version_number in number) is
441 --
442 l_proc varchar2(72) := g_package||'chk_cond_ext_data_elmt_id';
443 l_api_updating boolean;
444 l_dummy varchar2(1);
445 --
446 cursor c1 is
447 select null
448 from ben_ext_data_elmt a
449 where a.ext_data_elmt_id = p_cond_ext_data_elmt_id;
450 --
451 Begin
452 --
453 hr_utility.set_location('Entering:'||l_proc,5);
454 --
455 l_api_updating := ben_xwc_shd.api_updating
456 (p_ext_where_clause_id => p_ext_where_clause_id,
457 p_object_version_number => p_object_version_number);
458 --
459 if (l_api_updating
460 and nvl(p_cond_ext_data_elmt_id,hr_api.g_number)
461 <> nvl(ben_xwc_shd.g_old_rec.cond_ext_data_elmt_id,hr_api.g_number)
462 or not l_api_updating) and
463 p_cond_ext_data_elmt_id is not null then
464 --
465 -- check if cond_ext_data_elmt_id value exists in ben_ext_data_elmt table
466 --
467 open c1;
468 --
469 fetch c1 into l_dummy;
470 if c1%notfound then
471 --
472 close c1;
473 --
474 -- raise error as FK does not relate to PK in ben_ext_data_elmt
475 -- table.
476 --
477 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_FK2');
478 --
479 end if;
480 --
481 close c1;
482 --
483 end if;
484 --
485 hr_utility.set_location('Leaving:'||l_proc,10);
486 --
487 End chk_cond_ext_data_elmt_id;
488 --
489 -- ----------------------------------------------------------------------------
490 -- |------< chk_ext_data_elmt_id >------|
491 -- ----------------------------------------------------------------------------
492 --
493 -- Description
494 -- This procedure checks that a referenced foreign key actually exists
495 -- in the referenced table.
496 --
497 -- Pre-Conditions
498 -- None.
499 --
500 -- In Parameters
501 -- p_ext_where_clause_id PK
502 -- p_ext_data_elmt_id ID of FK column
503 -- p_object_version_number object version number
504 --
505 -- Post Success
506 -- Processing continues
507 --
508 -- Post Failure
509 -- Error raised.
510 --
511 -- Access Status
512 -- Internal table handler use only.
513 --
514 Procedure chk_ext_data_elmt_id (p_ext_where_clause_id in number,
515 p_ext_data_elmt_id in number,
516 p_object_version_number in number) is
517 --
518 l_proc varchar2(72) := g_package||'chk_ext_data_elmt_id';
519 l_api_updating boolean;
520 l_dummy varchar2(1);
521 --
522 cursor c1 is
523 select null
524 from ben_ext_data_elmt a
525 where a.ext_data_elmt_id = p_ext_data_elmt_id;
526 --
527 Begin
528 --
529 hr_utility.set_location('Entering:'||l_proc,5);
530 --
531 l_api_updating := ben_xwc_shd.api_updating
535 if (l_api_updating
532 (p_ext_where_clause_id => p_ext_where_clause_id,
533 p_object_version_number => p_object_version_number);
534 --
536 and nvl(p_ext_data_elmt_id,hr_api.g_number)
537 <> nvl(ben_xwc_shd.g_old_rec.ext_data_elmt_id,hr_api.g_number)
538 or not l_api_updating) and
539 p_ext_data_elmt_id is not null then
540 --
541 -- check if ext_data_elmt_id value exists in ben_ext_data_elmt table
542 --
543 open c1;
544 --
545 fetch c1 into l_dummy;
546 if c1%notfound then
547 --
548 close c1;
549 --
550 -- raise error as FK does not relate to PK in ben_ext_data_elmt
551 -- table.
552 --
553 ben_xwc_shd.constraint_error('BEN_EXT_WHERE_CLAUSE_FK1');
554 --
555 end if;
556 --
557 close c1;
558 --
559 end if;
560 --
561 hr_utility.set_location('Leaving:'||l_proc,10);
562 --
563 End chk_ext_data_elmt_id;
564 --
565 -- ----------------------------------------------------------------------------
566 -- |------< chk_oper_cd >------|
567 -- ----------------------------------------------------------------------------
568 --
569 -- Description
570 -- This procedure is used to check that the lookup value is valid.
571 --
572 -- Pre Conditions
573 -- None.
574 --
575 -- In Parameters
576 -- ext_where_clause_id PK of record being inserted or updated.
577 -- oper_cd Value of lookup code.
578 -- effective_date effective date
579 -- object_version_number Object version number of record being
580 -- inserted or updated.
581 --
582 -- Post Success
583 -- Processing continues
584 --
585 -- Post Failure
586 -- Error handled by procedure
587 --
588 -- Access Status
589 -- Internal table handler use only.
590 --
591 Procedure chk_oper_cd(p_ext_where_clause_id in number,
592 p_oper_cd in varchar2,
593 p_effective_date in date,
594 p_business_group_id in varchar2,
595 p_object_version_number in number) is
596 --
597 l_proc varchar2(72) := g_package||'chk_oper_cd';
598 l_api_updating boolean;
599 --
600 Begin
601 --
602 hr_utility.set_location('Entering:'||l_proc, 5);
603 --
604 l_api_updating := ben_xwc_shd.api_updating
605 (p_ext_where_clause_id => p_ext_where_clause_id,
606 p_object_version_number => p_object_version_number);
607 --
608 if (l_api_updating
609 and p_oper_cd
610 <> nvl(ben_xwc_shd.g_old_rec.oper_cd,hr_api.g_varchar2)
611 or not l_api_updating)
612 and p_oper_cd is not null then
613 --
614 -- check if value of lookup falls within lookup type.
615 --
616 if p_business_group_id is not null then
617 /* BG is set, so use the existing call, with no modifications*/
618 if hr_api.not_exists_in_hr_lookups
619 (p_lookup_type => 'BEN_EXT_OPER',
620 p_lookup_code => p_oper_cd,
621 p_effective_date => p_effective_date) then
622 --
623 -- raise error as does not exist as lookup
624 --
625 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
626 fnd_message.set_token('FIELD','p_oper_cd');
627 fnd_message.set_token('TYPE','BEN_EXT_OPER');
628 fnd_message.raise_error;
629 --
630 end if;
631 else
632 /* BG is null, so alternative call is required */
633 if hr_api.not_exists_in_hrstanlookups
634 (p_lookup_type => 'BEN_EXT_OPER',
635 p_lookup_code => p_oper_cd,
636 p_effective_date => p_effective_date) then
637 --
638 -- raise error as does not exist as lookup
639 --
640 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
641 fnd_message.set_token('FIELD','p_oper_cd');
642 fnd_message.set_token('TYPE','BEN_EXT_OPER');
643 fnd_message.raise_error;
644 --
645 end if;
646 end if;
647 --
648 end if; /* if (l_api_updating... */
649 --
650 hr_utility.set_location('Leaving:'||l_proc,10);
651 --
652 end chk_oper_cd;
653 --
654 -- ----------------------------------------------------------------------------
655 -- |------< chk_and_or_cd >------|
656 -- ----------------------------------------------------------------------------
657 --
658 -- Description
659 -- This procedure is used to check that the lookup value is valid.
660 --
661 -- Pre Conditions
662 -- None.
663 --
664 -- In Parameters
665 -- ext_where_clause_id PK of record being inserted or updated.
666 -- and_or_cd Value of lookup code.
667 -- effective_date effective date
668 -- object_version_number Object version number of record being
669 -- inserted or updated.
670 --
671 -- Post Success
672 -- Processing continues
673 --
674 -- Post Failure
675 -- Error handled by procedure
676 --
677 -- Access Status
678 -- Internal table handler use only.
679 --
680 Procedure chk_and_or_cd(p_ext_where_clause_id in number,
681 p_and_or_cd in varchar2,
682 p_effective_date in date,
686 l_proc varchar2(72) := g_package||'chk_and_or_cd';
683 p_business_group_id in varchar2,
684 p_object_version_number in number) is
685 --
687 l_api_updating boolean;
688 --
689 Begin
690 --
691 hr_utility.set_location('Entering:'||l_proc, 5);
692 --
693 l_api_updating := ben_xwc_shd.api_updating
694 (p_ext_where_clause_id => p_ext_where_clause_id,
695 p_object_version_number => p_object_version_number);
696 --
697 if (l_api_updating
698 and p_and_or_cd
699 <> nvl(ben_xwc_shd.g_old_rec.and_or_cd,hr_api.g_varchar2)
700 or not l_api_updating)
701 and p_and_or_cd is not null then
702 --
703 -- check if value of lookup falls within lookup type.
704 --
705 if p_business_group_id is not null then
706 /* BG is set, so use the existing call, with no modifications*/
707 if hr_api.not_exists_in_hr_lookups
708 (p_lookup_type => 'BEN_EXT_AND_OR',
709 p_lookup_code => p_and_or_cd,
710 p_effective_date => p_effective_date) then
711 --
712 -- raise error as does not exist as lookup
713 --
714 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
715 fnd_message.set_token('FIELD','p_and_or_cd');
716 fnd_message.set_token('TYPE','BEN_EXT_AND_OR');
717 fnd_message.raise_error;
718 --
719 end if;
720 else
721 /* BG is null, so alternative call is required */
722 if hr_api.not_exists_in_hrstanlookups
723 (p_lookup_type => 'BEN_EXT_AND_OR',
724 p_lookup_code => p_and_or_cd,
725 p_effective_date => p_effective_date) then
726 --
727 -- raise error as does not exist as lookup
728 --
729 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
730 fnd_message.set_token('FIELD','p_and_or_cd');
731 fnd_message.set_token('TYPE','BEN_EXT_AND_OR');
732 fnd_message.raise_error;
733 --
734 end if;
735 end if;
736 --
737 end if;
738 --
739 hr_utility.set_location('Leaving:'||l_proc,10);
740 --
741 end chk_and_or_cd;
742 --
743 -- ----------------------------------------------------------------------------
744 -- |------< chk_val >------|
745 -- ----------------------------------------------------------------------------
746 --
747 -- Description
748 -- This procedure is used to check that the val is valid by running it
749 -- through a quick dynamic sql test.
750 --
751 -- Pre Conditions
752 -- None.
753 --
754 -- In Parameters
755 -- ext_where_clause_id PK of record being inserted or updated.
756 -- and_or_cd Value of lookup code.
757 -- effective_date effective date
758 -- object_version_number Object version number of record being
759 -- inserted or updated.
760 --
761 -- Post Success
762 -- Processing continues
763 --
764 -- Post Failure
765 -- Error handled by procedure
766 --
767 -- Access Status
768 -- Internal table handler use only.
769 --
770 Procedure chk_val(p_ext_where_clause_id in number,
771 p_oper_cd in varchar2,
772 p_val in varchar2,
773 p_effective_date in date,
774 p_object_version_number in number) is
775 --
776 l_proc varchar2(72) := g_package||'chk_val';
777 l_api_updating boolean;
778 l_dynamic_condition varchar2(500);
779 l_str varchar2(2000) ;
780 --
781 Begin
782 --
783 hr_utility.set_location('Entering:'||l_proc, 5);
784 --
785 l_api_updating := ben_xwc_shd.api_updating
786 (p_ext_where_clause_id => p_ext_where_clause_id,
787 p_object_version_number => p_object_version_number);
788 --
789 if (
790 (l_api_updating and (p_val <> nvl(ben_xwc_shd.g_old_rec.val,hr_api.g_varchar2))) or
791 (l_api_updating and (p_oper_cd <> nvl(ben_xwc_shd.g_old_rec.oper_cd,hr_api.g_varchar2))) or
792 not l_api_updating
793 ) then
794
795
796 ben_ext_adv_conditions.g_ext_adv_ct_validation := 'N' ;
797 ben_ext_adv_ct_check.chk_val
798 (p_ext_where_clause_id => p_ext_where_clause_id,
799 p_oper_cd => p_oper_cd,
800 p_val => p_val,
801 p_effective_date => p_effective_date
802 );
803
804 if ben_ext_adv_conditions.g_ext_adv_ct_validation = 'N' then
805
806
807 --
808 -- check if value of lookup falls within lookup type.
809 --
810 -- make sure the p_val string starts with ' or (
811 -- make sure no function is callled from here
812
813 if not (substr( ltrim(rtrim(p_val)),1,1) = '''' or substr( ltrim(rtrim(p_val)),1,1) = '(' ) then
814 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
815 fnd_message.raise_error;
816 end if ;
817
818 --if the first string starts with ( then the second string should be '
819 -- to avoid ( tilak() ) kind
820 if substr( ltrim(rtrim(p_val)),1,1) = '(' then
821 if substr( rtrim(ltrim( substr(ltrim(rtrim(p_val)),2)) ), 1,1) <> '''' then
825 end if ;
822 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
823 fnd_message.raise_error;
824 end if ;
826
827 -- make sure the last string also the ' or ) , between amy call with sceond string as function
828 -- to avoid '0000' and tilak() kind
829 if not (substr( ltrim(rtrim(p_val)), -1) = '''' or substr( ltrim(rtrim(p_val)),-1) = ')' ) then
830 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
831 fnd_message.raise_error;
832 end if ;
833
834 -- to avoid '0000' and ( tilak () ) kind
835
836 if substr( ltrim(rtrim(p_val)),-1) = ')' then
837 l_str := substr(rtrim(p_val), 1, length(rtrim(p_val)) -1 ) ;
838 if substr( ltrim(rtrim(l_str)),-1) <> '''' then
839 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
840 fnd_message.raise_error;
841 end if ;
842 end if ;
843
844 -- to avoid '0000' and tilak('xxxx')
845 if p_oper_cd = 'BETWEEN' then
846 l_str := ltrim( substr( p_val, instr(upper(p_val),'AND')+3)) ;
847
848 if not (substr( ltrim(rtrim(l_str)),1,1) = '''' or substr( ltrim(rtrim(l_str)),1,1) = '(' ) then
849 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
850 fnd_message.raise_error;
851 end if ;
852
853 -- to avoid ( tilak() ) kind
854 if substr( ltrim(rtrim(l_str)),1,1) = '(' then
855 if substr( rtrim(ltrim( substr(ltrim(rtrim(l_str)),2)) ), 1,1) <> '''' then
856 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
857 fnd_message.raise_error;
858 end if ;
859 end if ;
860
861 end if ;
862
863 -- to avoid 'xxxx'||tilak()||'xxx'
864 if instr(p_val , '||') > 0 then
865 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
866 fnd_message.raise_error;
867 end if ;
868
869 l_dynamic_condition := 'Begin If ''TestValue '' ' || p_oper_cd || ' ' || p_val ||
870 ' then null; end if; end;';
871
872 begin
873 execute immediate l_dynamic_condition;
874 exception
875 when others then
876 fnd_message.set_name('BEN','BEN_92302_DYN_SQL_ERROR');
877 fnd_message.raise_error;
878 end;
879 end if;
880 end if ;
881 --
882 hr_utility.set_location('Leaving:'||l_proc,10);
883 --
884 end chk_val;
885 --
886 -- ----------------------------------------------------------------------------
887 -- |---------------------------< chk_dup_seq_no >----------------------------|
888 -- ----------------------------------------------------------------------------
889 -- The proc is added in fixing 4658335
890 procedure chk_dup_seq_no(p_business_group_id in number
891 ,p_legislation_code in varchar2
892 ,p_ext_where_clause_id in number
893 ,p_ext_rcd_in_file_id in number
894 ,p_ext_data_elmt_in_rcd_id in number
895 ,p_ext_data_elmt_id in number
896 ,p_seq_num in number
897 ,p_object_version_number in number) is
898 --
899 l_proc varchar2(72) := g_package||'insert_validate';
900 l_api_updating boolean;
901 --
902 cursor c_xwc is
903 SELECT null
904 FROM ben_ext_where_clause xwc
905 WHERE ( business_group_id is null
906 or business_group_id = p_business_group_id )
907 and (legislation_code is null
908 or legislation_code = p_legislation_code )
909 and (ext_rcd_in_file_id = p_ext_rcd_in_file_id
910 or p_ext_rcd_in_file_id is null )
911 and (ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
912 or p_ext_data_elmt_in_rcd_id is null)
913 and (ext_data_elmt_id = p_ext_data_elmt_id
914 or p_ext_data_elmt_id is null)
915 and seq_num = p_seq_num
916 and (ext_where_clause_id <> p_ext_where_clause_id
917 or p_ext_where_clause_id is null);
918 --
919 l_dummy number ;
920 Begin
921 --
922 -- bug 4658335, check only when seq num is changed. or inserting
923 l_api_updating := ben_xwc_shd.api_updating
924 (p_ext_where_clause_id => p_ext_where_clause_id,
925 p_object_version_number => p_object_version_number);
926 --
927 If (
928 (l_api_updating and (p_seq_num <> nvl(ben_xwc_shd.g_old_rec.seq_num,hr_api.g_number))) or
929 not l_api_updating
930 ) then
931 Open c_xwc;
932 --
933 Fetch c_xwc into l_Dummy;
934 If c_xwc%FOUND then
935 Close c_xwc ;
936 -- Raise Sequence Error
937 fnd_message.set_name('BEN','BEN_94223_DUP_ORDR_NUM');
938 fnd_message.raise_error;
939 End If;
940 Close c_xwc;
941 --
942 End if;
943 --
944 end chk_dup_seq_no;
945
946 -- ----------------------------------------------------------------------------
947 -- |---------------------------< insert_validate >----------------------------|
948 -- ----------------------------------------------------------------------------
949 Procedure insert_validate(p_rec in ben_xwc_shd.g_rec_type
950 ,p_effective_date in date) is
951 --
952 l_proc varchar2(72) := g_package||'insert_validate';
953 --
954 Begin
958 --
955 hr_utility.set_location('Entering:'||l_proc, 5);
956 --
957 -- Call all supporting business operations
959 --
960 chk_startup_action(True
961 ,p_rec.business_group_id
962 ,p_rec.legislation_code);
963 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
964 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
965 END IF;
966 --
967 chk_ext_where_clause_id
968 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
969 p_object_version_number => p_rec.object_version_number);
970 --
971 chk_cond_ext_elmt_in_rcd_id
972 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
973 p_cond_ext_data_elmt_in_rcd_id => p_rec.cond_ext_data_elmt_in_rcd_id,
974 p_object_version_number => p_rec.object_version_number);
975 --
976 chk_ext_rcd_in_file_id
977 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
978 p_ext_rcd_in_file_id => p_rec.ext_rcd_in_file_id,
979 p_object_version_number => p_rec.object_version_number);
980 --
981 chk_ext_data_elmt_in_rcd_id
982 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
983 p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
984 p_object_version_number => p_rec.object_version_number);
985 --
986 chk_cond_ext_data_elmt_id
987 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
988 p_cond_ext_data_elmt_id => p_rec.cond_ext_data_elmt_id,
989 p_object_version_number => p_rec.object_version_number);
990 --
991 chk_ext_data_elmt_id
992 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
993 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
994 p_object_version_number => p_rec.object_version_number);
995 --
996 chk_oper_cd
997 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
998 p_oper_cd => p_rec.oper_cd,
999 p_effective_date => p_effective_date,
1000 p_business_group_id => p_rec.business_group_id,
1001 p_object_version_number => p_rec.object_version_number);
1002 --
1003 chk_and_or_cd
1004 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1005 p_and_or_cd => p_rec.and_or_cd,
1006 p_effective_date => p_effective_date,
1007 p_business_group_id => p_rec.business_group_id,
1008 p_object_version_number => p_rec.object_version_number);
1009 --
1010 chk_val
1011 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1012 p_oper_cd => p_rec.oper_cd,
1013 p_val => p_rec.val,
1014 p_effective_date => p_effective_date,
1015 p_object_version_number => p_rec.object_version_number);
1016 --
1017 chk_dup_seq_no
1018 (p_business_group_id => p_rec.business_group_id,
1019 p_legislation_code => p_rec.legislation_code,
1020 p_ext_where_clause_id => p_rec.ext_where_clause_id,
1021 p_ext_rcd_in_file_id => p_rec.ext_rcd_in_file_id,
1022 p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id ,
1023 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1024 p_seq_num => p_rec.seq_num,
1025 p_object_version_number => p_rec.object_version_number);
1026 --
1027 hr_utility.set_location(' Leaving:'||l_proc, 10);
1028 End insert_validate;
1029 --
1030 -- ----------------------------------------------------------------------------
1031 -- |---------------------------< update_validate >----------------------------|
1032 -- ----------------------------------------------------------------------------
1033 Procedure update_validate(p_rec in ben_xwc_shd.g_rec_type
1034 ,p_effective_date in date) is
1035 --
1036 l_proc varchar2(72) := g_package||'update_validate';
1037 --
1038 Begin
1039 hr_utility.set_location('Entering:'||l_proc, 5);
1040 --
1041 -- Call all supporting business operations
1042 --
1043 --
1044 chk_startup_action(False
1045 ,p_rec.business_group_id
1046 ,p_rec.legislation_code);
1047 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1048 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1049 END IF;
1050 --
1051 chk_ext_where_clause_id
1052 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1053 p_object_version_number => p_rec.object_version_number);
1054 --
1055 chk_cond_ext_elmt_in_rcd_id
1056 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1057 p_cond_ext_data_elmt_in_rcd_id => p_rec.cond_ext_data_elmt_in_rcd_id,
1058 p_object_version_number => p_rec.object_version_number);
1059 --
1060 chk_ext_rcd_in_file_id
1061 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1062 p_ext_rcd_in_file_id => p_rec.ext_rcd_in_file_id,
1063 p_object_version_number => p_rec.object_version_number);
1064 --
1065 chk_ext_data_elmt_in_rcd_id
1066 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1067 p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id,
1068 p_object_version_number => p_rec.object_version_number);
1069 --
1070 chk_cond_ext_data_elmt_id
1071 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1072 p_cond_ext_data_elmt_id => p_rec.cond_ext_data_elmt_id,
1073 p_object_version_number => p_rec.object_version_number);
1074 --
1075 chk_ext_data_elmt_id
1076 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1077 p_ext_data_elmt_id => p_rec.ext_data_elmt_id,
1078 p_object_version_number => p_rec.object_version_number);
1079 --
1080 chk_oper_cd
1081 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1082 p_oper_cd => p_rec.oper_cd,
1083 p_effective_date => p_effective_date,
1084 p_business_group_id => p_rec.business_group_id,
1085 p_object_version_number => p_rec.object_version_number);
1086 --
1087 chk_and_or_cd
1088 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1089 p_and_or_cd => p_rec.and_or_cd,
1090 p_effective_date => p_effective_date,
1091 p_business_group_id => p_rec.business_group_id,
1092 p_object_version_number => p_rec.object_version_number);
1093 --
1094 chk_val
1095 (p_ext_where_clause_id => p_rec.ext_where_clause_id,
1096 p_oper_cd => p_rec.oper_cd,
1097 p_val => p_rec.val,
1098 p_effective_date => p_effective_date,
1099 p_object_version_number => p_rec.object_version_number);
1100 --
1101 chk_dup_seq_no
1102 (p_business_group_id => p_rec.business_group_id,
1103 p_legislation_code => p_rec.legislation_code,
1104 p_ext_where_clause_id => p_rec.ext_where_clause_id,
1105 p_ext_rcd_in_file_id => p_rec.ext_rcd_in_file_id,
1106 p_ext_data_elmt_in_rcd_id => p_rec.ext_data_elmt_in_rcd_id ,
1107 p_ext_data_elmt_id => p_rec.ext_data_elmt_id ,
1108 p_seq_num => p_rec.seq_num,
1109 p_object_version_number => p_rec.object_version_number);
1110 --
1111 hr_utility.set_location(' Leaving:'||l_proc, 10);
1112 End update_validate;
1113 --
1114 -- ----------------------------------------------------------------------------
1115 -- |---------------------------< delete_validate >----------------------------|
1116 -- ----------------------------------------------------------------------------
1117 Procedure delete_validate(p_rec in ben_xwc_shd.g_rec_type
1118 ,p_effective_date in date) is
1119 --
1120 l_proc varchar2(72) := g_package||'delete_validate';
1121 --
1122 Begin
1123 hr_utility.set_location('Entering:'||l_proc, 5);
1124 --
1125 -- Call all supporting business operations
1126 --
1127 chk_startup_action(False
1128 ,ben_xwc_shd.g_old_rec.business_group_id
1129 ,ben_xwc_shd.g_old_rec.legislation_code);
1130 --
1131 hr_utility.set_location(' Leaving:'||l_proc, 10);
1132 End delete_validate;
1133 --
1134 --
1135 -- ---------------------------------------------------------------------------
1136 -- |---------------------< return_legislation_code >-------------------------|
1137 -- ---------------------------------------------------------------------------
1138 --
1139 function return_legislation_code
1140 (p_ext_where_clause_id in number) return varchar2 is
1141 --
1142 -- Declare cursor
1143 --
1144 cursor csr_leg_code is
1145 select a.legislation_code
1146 from per_business_groups a,
1147 ben_ext_where_clause b
1148 where b.ext_where_clause_id = p_ext_where_clause_id
1149 and a.business_group_id(+) = b.business_group_id;
1150 --
1151 -- Declare local variables
1152 --
1153 l_legislation_code per_business_groups.legislation_code%type ;
1154 l_proc varchar2(72) := g_package||'return_legislation_code';
1155 --
1156 begin
1157 --
1158 hr_utility.set_location('Entering:'|| l_proc, 10);
1159 --
1160 -- Ensure that all the mandatory parameter are not null
1161 --
1162 hr_api.mandatory_arg_error(p_api_name => l_proc,
1163 p_argument => 'ext_where_clause_id',
1164 p_argument_value => p_ext_where_clause_id);
1165 --
1166 open csr_leg_code;
1167 --
1168 fetch csr_leg_code into l_legislation_code;
1169 --
1170 if csr_leg_code%notfound then
1171 --
1172 close csr_leg_code;
1173 --
1174 -- The primary key is invalid therefore we must error
1175 --
1176 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1177 fnd_message.raise_error;
1178 --
1179 end if;
1180 --
1181 close csr_leg_code;
1182 --
1183 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1184 --
1185 return l_legislation_code;
1186 --
1187 end return_legislation_code;
1188 --
1189 end ben_xwc_bus;