1 Package Body pay_bad_bus as
2 /* $Header: pybadrhi.pkb 115.3 2003/05/28 18:43:28 rthirlby noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_bad_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_attribute_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_attribute_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pay_bal_attribute_definitions bad
31 where bad.attribute_id = p_attribute_id
32 and pbg.business_group_id = bad.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'attribute_id'
48 ,p_argument_value => p_attribute_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'ATTRIBUTE_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_attribute_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , pay_bal_attribute_definitions bad
95 where bad.attribute_id = p_attribute_id
96 and pbg.business_group_id (+) = bad.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'attribute_id'
112 ,p_argument_value => p_attribute_id
113 );
114 --
115 if ( nvl(pay_bad_bus.g_attribute_id, hr_api.g_number)
116 = p_attribute_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pay_bad_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 pay_bad_bus.g_attribute_id := p_attribute_id;
147 pay_bad_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-------------------------< chk_attribute_name >---------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This procedure is used to ensure that the attribute_name is unique across
160 -- all modes, i.e. a user row cannot have the same attribute name as a
161 -- startup (legislation) row.
162 -- A hierachy is used to prevent duplicate attribute names. GENERIC mode takes
163 -- priority, if a generic row exists then error with duplicate name, but if a
164 -- startup or user row exists with same name then error, saying the existig
165 -- row must be deleted and retry insert of GENERIC.
166 -- IF in STARTUP mode, if generic row or startup row in same legislation
167 -- exists then error - duplicate row, but if user row exists then error
168 -- saying the existing row must be deleted and retry insert of startup row.
169 -- If in user mode, if generic row, or startup row with same leg as current
170 -- bg, or a user row in same bg exists then - error duplicate name.
171 --
172 -- Pre Conditions:
173 -- g_old_rec has been populated with details of the values currently in
174 -- the database.
175 --
176 -- In Arguments:
177 -- p_rec has been populated with the updated values the user would like the
178 -- record set to.
179 --
180 -- Post Success:
181 -- Processing continues if a valid attribute_name has been entered.
182 --
183 -- Post Failure:
184 -- An application error is raised if a duplicate attribute_name has been
185 -- entered.
186 --
187 -- {End Of Comments}
188 -- ----------------------------------------------------------------------------
189 Procedure chk_attribute_name
190 (p_attribute_id in number
191 ,p_attribute_name in varchar2
192 ,p_business_group_id in number default null
193 ,p_legislation_code in varchar2 default null)
194 IS
195 --
196 l_proc varchar2(72) := g_package || 'chk_attribute_name';
197 l_error EXCEPTION;
198 l_argument varchar2(30);
199 l_attribute_name varchar2(80);
200 l_leg_code varchar2(80);
201 l_bg_id number;
202 l_mode varchar2(30);
203 l_bg_leg varchar2(80);
204 --
205 cursor csr_attribute_name is
206 select bad.attribute_name
207 , bad.legislation_code
208 , bad.business_group_id
209 from pay_bal_attribute_definitions bad
210 where bad.attribute_name = p_attribute_name;
211 --
212 cursor csr_bg_leg(p_bg_id number)
213 is
214 select legislation_code
215 from per_business_groups
216 where nvl(business_group_id,-1) = nvl(p_bg_id,-1);
217 --
218 Begin
219 --
220 hr_utility.set_location('Entering: '||l_proc,5);
221 --
222 -- Only execute the cursor if absolutely necessary.
223 -- a) During update, the attribute_name has actually changed to another not
224 -- null value, i,e, the value passed to this procedure is different to the
225 -- g_old_rec value.
226 -- b) During insert, the attribute_name is null.
227 -- Can tell the difference between insert and update by looking at the
228 -- primary key value. For update it will be not null. For insert it will be
229 -- null, because pre_inset has not been called yet.
230 --
231 IF (((p_attribute_id is not null) and
232 nvl(pay_bad_shd.g_old_rec.attribute_name, hr_api.g_varchar2) <>
233 nvl(p_attribute_name, hr_api.g_varchar2))
234 or
235 (p_attribute_id is null)) then
236 --
237 hr_utility.set_location(l_proc, 10);
238 --
239 -- Only need to open the cursor if attribute_name is not null
240 --
241 if p_attribute_name is not null then
242 --
243 l_mode := hr_startup_data_api_support.return_startup_mode;
244 --
245 OPEN csr_attribute_name;
246 FETCH csr_attribute_name INTO l_attribute_name
247 ,l_leg_code
248 ,l_bg_id;
249 IF csr_attribute_name%NOTFOUND THEN
250 --
251 hr_utility.trace('insert row');
252 close csr_attribute_name;
253 ELSE
254 if l_mode = 'GENERIC' then
255 hr_utility.set_location(l_proc, 15);
256 if (l_leg_code is null and l_bg_id is null) then
257 -- generic row with duplicate name already exists
258 hr_utility.set_message(801, 'PAY_34231_DUP_ATT_G');
259 hr_utility.raise_error;
260 elsif
261 l_leg_code is not null
262 or l_bg_id is not null then
263 -- name exists at lower level, existing row must be deleted
264 -- so new seeded row can be inserted.
265 hr_utility.set_message(801,'PAY_34232_S_U_ATT_LOW_LVL_DEL');
266 hr_utility.raise_error;
267 end if;
268 elsif l_mode = 'STARTUP' THEN
269 --
270 hr_utility.set_location(l_proc, 20);
271 if (l_leg_code = p_legislation_code) then
272 -- startup row with duplicate name already exists
273 hr_utility.set_message(801,'PAY_34233_DUP_ATT_S');
274 hr_utility.raise_error;
275 elsif
276 l_leg_code is null then
277 if l_bg_id is not null then
278 open csr_bg_leg(l_bg_id);
279 fetch csr_bg_leg into l_bg_leg;
280 close csr_bg_leg;
281 if p_legislation_code = l_bg_leg then
282 -- Row with duplicate name exists at lower hierarchy.
283 -- Row needs to be deleted so seeded row can be inserted.
284 hr_utility.set_message(801,'PAY_34234_U_ATT_LOW_LVL_DEL');
285 hr_utility.raise_error;
286 end if;
287 else -- l_bg_id is null then
288 -- Row with duplicate name exists at higher level,
289 -- so cannot insert this row.
290 hr_utility.set_message(801,'PAY_34235_G_ATT_HI_LVL');
291 hr_utility.raise_error;
292 end if;
293 end if;
294 else -- mode is 'USER'
295 open csr_bg_leg(p_business_group_id);
296 fetch csr_bg_leg into l_bg_leg;
297 close csr_bg_leg;
298 --
299 if l_bg_id = p_business_group_id then
300 -- user row with duplicate name already exists
301 hr_utility.set_message(801,'PAY_34236_DUP_ATT_U');
302 hr_utility.raise_error;
303 elsif
304 l_bg_id is null then
305 if ((l_leg_code is null)
306 or (l_leg_code is not null
307 and l_leg_code = l_bg_leg)) then
308 -- Row with duplicate name exists at higher level, so
309 -- cannot insert this row.
310 hr_utility.set_message(801,'PAY_34237_G_S_ATT_HI_LVL');
311 hr_utility.raise_error;
312 end if;
313 end if;
314 end if; -- what mode
315 close csr_attribute_name;
316 END IF;
317 --
318 end if;
319 end if;
320 hr_utility.set_location('Leaving: '||l_proc, 20);
321 --
322 END chk_attribute_name;
323 -- ----------------------------------------------------------------------------
324 -- |-----------------------< chk_user_attribute_name >------------------------|
325 -- ----------------------------------------------------------------------------
326 -- {Start Of Comments}
327 --
328 -- Description:
329 -- This procedure is used to ensure that the user_attribute_name is unique
330 -- across all modes, i.e. a user row cannot have the same user attribute name
331 -- as a startup (legislation) row.
332 -- A hierachy is used to prevent duplicate user attribute names. GENERIC mode
333 -- takes priority, if a generic row exists then error with duplicate name,
334 -- but if a startup or user row exists with same user name then error, saying
335 -- the existig row must be deleted and retry insert of GENERIC.
336 -- IF in STARTUP mode, if generic row or startup row in same legislation
337 -- exists then error - duplicate row, but if user row exists then error
338 -- saying the existing row must be deleted and retry insert of startup row.
339 -- If in user mode, if generic row, or startup row with same leg as current
340 -- bg, or a user row in same bg exists then - error duplicate name.
341 --
342 -- Pre Conditions:
343 -- g_old_rec has been populated with details of the values currently in
344 -- the database.
345 --
346 -- In Arguments:
347 -- p_rec has been populated with the updated values the user would like the
348 -- record set to.
349 --
350 -- Post Success:
351 -- Processing continues if a valid attribute_name has been entered.
352 --
353 -- Post Failure:
354 -- An application error is raised if a duplicate attribute_name has been
355 -- entered.
356 --
357 -- {End Of Comments}
358 -- ----------------------------------------------------------------------------
359 Procedure chk_user_attribute_name
360 (p_attribute_id in number
361 ,p_user_attribute_name in varchar2
362 ,p_business_group_id in number default null
363 ,p_legislation_code in varchar2 default null)
364 IS
365 --
366 l_proc varchar2(72) := g_package || 'chk_user_attribute_name';
367 l_error EXCEPTION;
368 l_argument varchar2(30);
369 l_user_attribute_name varchar2(80);
370 l_leg_code varchar2(80);
371 l_bg_id number;
372 l_mode varchar2(30);
373 l_bg_leg varchar2(80);
374 --
375 cursor csr_user_attribute_name is
376 select bad.user_attribute_name
377 , bad.legislation_code
378 , bad.business_group_id
379 from pay_bal_attribute_definitions bad
380 where bad.user_attribute_name = p_user_attribute_name;
381 --
382 cursor csr_bg_leg(p_bg_id number)
383 is
384 select legislation_code
385 from per_business_groups
386 where nvl(business_group_id,-1) = nvl(p_bg_id,-1);
387 --
388 Begin
389 --
390 hr_utility.set_location('Entering: '||l_proc,5);
391 --
392 -- Only execute the cursor if absolutely necessary.
393 -- a) During update, the user_attribute_name has actually changed to another not
397 -- Can tell the difference between insert and update by looking at the
394 -- null value, i,e, the value passed to this procedure is different to the
395 -- g_old_rec value.
396 -- b) During insert, the user_attribute_name is null.
398 -- primary key value. For update it will be not null. For insert it will be
399 -- null, because pre_inset has not been called yet.
400 --
401 IF (((p_attribute_id is not null) and
402 nvl(pay_bad_shd.g_old_rec.user_attribute_name, hr_api.g_varchar2) <>
403 nvl(p_user_attribute_name, hr_api.g_varchar2))
404 or
405 (p_attribute_id is null)) then
406 --
407 hr_utility.set_location(l_proc, 10);
408 --
409 -- Only need to open the cursor if user_attribute_name is not null
410 --
411 if p_user_attribute_name is not null then
412 --
413 l_mode := hr_startup_data_api_support.return_startup_mode;
414 --
415 OPEN csr_user_attribute_name;
416 FETCH csr_user_attribute_name INTO l_user_attribute_name
417 ,l_leg_code
418 ,l_bg_id;
419 IF csr_user_attribute_name%NOTFOUND THEN
420 --
421 hr_utility.trace('insert row');
422 close csr_user_attribute_name;
423 ELSE
424 if l_mode = 'GENERIC' then
425 hr_utility.set_location(l_proc, 15);
426 if (l_leg_code is null and l_bg_id is null) then
427 -- generic row with duplicate name already exists
428 hr_utility.set_message(801, 'PAY_34277_DUP_USRATT_G');
429 hr_utility.raise_error;
430 elsif
431 l_leg_code is not null
432 or l_bg_id is not null then
433 -- name exists at lower level, existing row must be deleted
434 -- so new seeded row can be inserted.
435 hr_utility.set_message(801,'PAY_34278_S_U_USRAT_LWLVL_DEL');
436 hr_utility.raise_error;
437 end if;
438 elsif l_mode = 'STARTUP' THEN
439 --
440 hr_utility.set_location(l_proc, 20);
441 if (l_leg_code = p_legislation_code) then
442 -- startup row with duplicate name already exists
443 hr_utility.set_message(801,'PAY_34279_DUP_USRATT_S');
444 hr_utility.raise_error;
445 elsif
446 l_leg_code is null then
447 if l_bg_id is not null then
448 open csr_bg_leg(l_bg_id);
449 fetch csr_bg_leg into l_bg_leg;
450 close csr_bg_leg;
451 if p_legislation_code = l_bg_leg then
452 -- Row with duplicate name exists at lower hierarchy.
453 -- Row needs to be deleted so seeded row can be inserted.
454 hr_utility.set_message(801,'PAY_34280_U_USRAT_LWLVL_DEL');
455 hr_utility.raise_error;
456 end if;
457 else -- l_bg_id is null then
458 -- Row with duplicate name exists at higher level,
459 -- so cannot insert this row.
460 hr_utility.set_message(801,'PAY_34281_G_USRATT_HI_LVL');
461 hr_utility.raise_error;
462 end if;
463 end if;
464 else -- mode is 'USER'
465 open csr_bg_leg(p_business_group_id);
466 fetch csr_bg_leg into l_bg_leg;
467 close csr_bg_leg;
468 --
469 if l_bg_id = p_business_group_id then
470 -- user row with duplicate name already exists
471 hr_utility.set_message(801,'PAY_34282_DUP_USRATT_U');
472 hr_utility.raise_error;
473 elsif
474 l_bg_id is null then
475 if ((l_leg_code is null)
476 or (l_leg_code is not null
477 and l_leg_code = l_bg_leg)) then
478 -- Row with duplicate name exists at higher level, so
479 -- cannot insert this row.
480 hr_utility.set_message(801,'PAY_34283_G_S_USRATT_HI_LVL');
481 hr_utility.raise_error;
482 end if;
483 end if;
484 end if; -- what mode
485 close csr_user_attribute_name;
486 END IF;
487 --
488 end if;
489 end if;
490 hr_utility.set_location('Leaving: '||l_proc, 20);
491 --
492 END chk_user_attribute_name;
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------< chk_alterable >------------------------------|
495 -- ----------------------------------------------------------------------------
496 -- {Start Of Comments}
497 --
498 -- Description:
499 -- This procedure is used to ensure that a valid value is entered in the
500 -- alterable column, either 'Y' or 'N'. If in user mode the alterable flag
501 -- must be 'Y'.
502 --
503 -- Pre Conditions:
504 -- g_old_rec has been populated with details of the values currently in
505 -- the database.
506 --
507 -- In Arguments:
508 -- p_rec has been populated with the updated values the user would like the
509 -- record set to.
513 --
510 --
511 -- Post Success:
512 -- Processing continues if a valid value is entered in alterable.
514 -- Post Failure:
515 -- An application error is raised if a value other than those returned from
516 -- HR_STANDARD_LOOKUPS with lookup_type = 'YES_NO'.
517 -- entered.
518 --
519 -- {End Of Comments}
520 -- ----------------------------------------------------------------------------
521 Procedure chk_alterable
522 (p_effective_date in date
523 ,p_alterable in varchar2) IS
524 --
525 l_proc varchar2(72) := g_package || 'chk_alterable';
526 l_error EXCEPTION;
527 l_argument varchar2(30);
528 --
529 Begin
530 --
531 hr_utility.set_location('Entering: '||l_proc,5);
532 --
533 -- YES_NO is a system level lookup, so only need to validate against
534 -- hr_standard_lookups, even though the table has a business_group_id and
535 -- would expect to need to validate against hr_lookups.
536 --
537 -- if user mode then alterable must be 'Y'.
538 --
539 if hr_startup_data_api_support.g_startup_mode = 'USER' then
540 --
541 if p_alterable <> 'Y' then
542 hr_utility.set_location(l_proc, 10);
543 hr_utility.set_message(801, 'PAY_34238_U_ALT_FLAG_MUSTB_Y');
544 hr_utility.raise_error;
545 end if;
546 --
547 else -- startup or generic mode
548 --
549 IF hr_api.not_exists_in_hrstanlookups
550 (p_effective_date => p_effective_date
551 ,p_lookup_type => 'YES_NO'
552 ,p_lookup_code => p_alterable
553 )
554 THEN
555 --
556 -- the value entered for this record is not recognised
557 --
558 fnd_message.set_name('PAY', 'PAY_34239_INV_ALT_FLAG');
559 fnd_message.raise_error;
560 --
561 hr_utility.set_location(l_proc, 15);
562 END IF;
563 end if; -- what mode
564 --
565 hr_utility.set_location('Leaving: '||l_proc, 20);
566 --
567 End chk_alterable;
568 -- ----------------------------------------------------------------------------
569 -- |-------------------------< chk_for_child_rows >---------------------------|
570 -- ----------------------------------------------------------------------------
571 -- {Start Of Comments}
572 --
573 -- Description:
574 -- This procedure is used to ensure that no child rows exist when attempting
575 -- to delete a row from this table. Child row could exist in tables
576 -- pay_balance_attributes and pay_bal_attribute_defaults.
577 --
578 -- Pre Conditions:
579 -- g_old_rec has been populated with details of the values currently in
580 -- the database.
581 --
582 -- In Arguments:
583 -- p_rec has been populated with the updated values the user would like the
584 -- record set to.
585 --
586 --
587 -- Post Success:
588 -- If not child rows are found then the row is deleted.
589 --
590 -- Post Failure:
591 -- An application error is raised if any child rows are found and the delete
592 -- does not take place.
593 --
594 -- {End Of Comments}
595 -- ----------------------------------------------------------------------------
596 Procedure chk_for_child_rows
597 (p_attribute_id in number
598 ,p_business_group_id in number default null
599 ,p_legislation_code in varchar2 default null) is
600 --
601 cursor csr_child_attrib(p_bal_att_id number
602 ,p_bg_id number)
603 is
604 select 1
605 from pay_balance_attributes pba
606 where pba.attribute_id = p_bal_att_id
607 and pba.business_group_id = nvl(p_bg_id, pba.business_group_id);
608 --
609 cursor csr_child_defaults(p_bal_att_id number
610 ,p_bg_id number)
611 is
612 select 1
613 from pay_bal_attribute_defaults pbd
614 where pbd.attribute_id = p_bal_att_id
615 and pbd.business_group_id = nvl(p_bg_id, pbd.business_group_id);
616 --
617 cursor get_bg_id
618 is
619 select business_group_id
620 from per_business_groups
621 where legislation_code = p_legislation_code;
622 --
623 l_proc varchar2(72) := g_package || 'chk_for_child_rows';
624 l_error EXCEPTION;
625 l_argument varchar2(30);
626 l_exists number(1);
627 --
628 BEGIN
629 hr_utility.set_location('Entering: '||l_proc,5);
630 --
631 if p_business_group_id is not null then -- user run type
632 open csr_child_attrib(p_attribute_id, p_business_group_id);
633 fetch csr_child_attrib into l_exists;
634 if csr_child_attrib%FOUND then
635 close csr_child_attrib;
636 hr_utility.set_message(801,'PAY_34240_CHILD_ATTRIBUTE');
637 hr_utility.raise_error;
638 hr_utility.set_location(l_proc, 10);
639 else
640 close csr_child_attrib;
641 end if;
642 --
643 open csr_child_defaults(p_attribute_id, p_business_group_id);
644 fetch csr_child_defaults into l_exists;
645 if csr_child_defaults%FOUND then
646 close csr_child_defaults;
647 hr_utility.set_message(801,'PAY_34241_CHILD_ATT_DEFAULT');
648 hr_utility.raise_error;
649 hr_utility.set_location(l_proc, 15);
650 else
654 elsif p_legislation_code is not null then -- startup category
651 close csr_child_defaults;
652 end if;
653 --
655 --
656 for each_bg in get_bg_id loop
657 open csr_child_attrib(p_attribute_id, each_bg.business_group_id);
658 fetch csr_child_attrib into l_exists;
659 if csr_child_attrib%FOUND then
660 close csr_child_attrib;
661 hr_utility.set_message(801,'PAY_34240_CHILD_ATTRIBUTE');
662 hr_utility.raise_error;
663 hr_utility.set_location(l_proc, 20);
664 else
665 close csr_child_attrib;
666 end if;
667 --
668 -- check for existing child pay_bal_attribute_defaults
669 --
670 open csr_child_defaults(p_attribute_id, each_bg.business_group_id);
671 fetch csr_child_defaults into l_exists;
672 if csr_child_defaults%FOUND then
673 close csr_child_defaults;
674 hr_utility.set_message(801,'PAY_34241_CHILD_ATT_DEFAULT');
675 hr_utility.raise_error;
676 hr_utility.set_location(l_proc, 25);
677 else
678 close csr_child_defaults;
679 end if;
680 end loop;
681 --
682 ELSE -- generic category
683 open csr_child_attrib(p_attribute_id, p_business_group_id);
684 fetch csr_child_attrib into l_exists;
685 if csr_child_attrib%FOUND then
686 close csr_child_attrib;
687 hr_utility.set_message(801,'PAY_34240_CHILD_ATTRIBUTE');
688 hr_utility.raise_error;
689 hr_utility.set_location(l_proc, 30);
690 else
691 close csr_child_attrib;
692 end if;
693 --
694 -- check for existing child pay_bal_attribute_definitions
695 --
696 open csr_child_defaults(p_attribute_id, p_business_group_id);
697 fetch csr_child_defaults into l_exists;
698 if csr_child_defaults%FOUND then
699 close csr_child_defaults;
700 hr_utility.set_message(801,'PAY_34241_CHILD_ATT_DEFAULT');
701 hr_utility.raise_error;
702 hr_utility.set_location(l_proc, 35);
703 else
704 close csr_child_defaults;
705 end if;
706 END IF;
707 hr_utility.set_location(' Leaving:'|| l_proc, 40);
708 --
709 End chk_for_child_rows;
710 -- ----------------------------------------------------------------------------
711 -- |-----------------------< chk_non_updateable_args >------------------------|
712 -- ----------------------------------------------------------------------------
713 -- {Start Of Comments}
714 --
715 -- Description:
716 -- This procedure is used to ensure that non updateable attributes have
717 -- not been updated. If an attribute has been updated an error is generated.
718 --
719 -- Pre Conditions:
720 -- g_old_rec has been populated with details of the values currently in
721 -- the database.
722 --
723 -- In Arguments:
724 -- p_rec has been populated with the updated values the user would like the
725 -- record set to.
726 --
727 -- Post Success:
728 -- Processing continues if all the non updateable attributes have not
729 -- changed.
730 --
731 -- Post Failure:
732 -- An application error is raised if any of the non updatable attributes
733 -- have been altered.
734 --
738 (p_effective_date in date
735 -- {End Of Comments}
736 -- ----------------------------------------------------------------------------
737 Procedure chk_non_updateable_args
739 ,p_rec in pay_bad_shd.g_rec_type
740 ) IS
741 --
742 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
743 l_error EXCEPTION;
744 l_argument varchar2(30);
745 --
746 Begin
747 --
748 -- Only proceed with the validation if a row exists for the current
749 -- record in the HR Schema.
750 --
751 IF NOT pay_bad_shd.api_updating
752 (p_attribute_id => p_rec.attribute_id
753 ) THEN
754 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
755 fnd_message.set_token('PROCEDURE ', l_proc);
756 fnd_message.set_token('STEP ', '5');
757 fnd_message.raise_error;
758 END IF;
759 --
760 hr_utility.set_location(l_proc, 10);
761 --
762 if nvl(p_rec.business_group_id, hr_api.g_number) <>
763 nvl(pay_bad_shd.g_old_rec.business_group_id, hr_api.g_number) then
764 l_argument := 'business_group_id';
765 raise l_error;
766 end if;
767 hr_utility.set_location(l_proc, 15);
768 --
769 if p_rec.attribute_id <> pay_bad_shd.g_old_rec.attribute_id then
770 l_argument := 'attribute_id';
771 raise l_error;
772 end if;
773 hr_utility.set_location(l_proc, 20);
774 --
775 if p_rec.legislation_code <> pay_bad_shd.g_old_rec.legislation_code then
776 l_argument := 'legislation_code';
777 raise l_error;
778 end if;
779 hr_utility.set_location(l_proc, 25);
780 --
781 if p_rec.attribute_name <> pay_bad_shd.g_old_rec.attribute_name then
782 l_argument := 'attribute_name';
783 raise l_error;
784 end if;
785 hr_utility.set_location(l_proc, 30);
786 --
787 if p_rec.alterable <> pay_bad_shd.g_old_rec.alterable then
788 l_argument := 'alterable';
789 raise l_error;
790 end if;
791 hr_utility.set_location(l_proc, 35);
792 --
793 EXCEPTION
794 WHEN l_error THEN
795 hr_api.argument_changed_error
796 (p_api_name => l_proc
797 ,p_argument => l_argument);
798 WHEN OTHERS THEN
799 RAISE;
800 hr_utility.set_location(' Leaving:'|| l_proc, 40);
801 --
802 End chk_non_updateable_args;
803 --
804 -- ----------------------------------------------------------------------------
805 -- |----------------------< chk_startup_action >------------------------------|
806 -- ----------------------------------------------------------------------------
807 --
808 -- Description:
809 -- This procedure will check that the current action is allowed according
810 -- to the current startup mode.
811 --
812 -- ----------------------------------------------------------------------------
813 PROCEDURE chk_startup_action
814 (p_insert IN boolean
815 ,p_business_group_id IN number
816 ,p_legislation_code IN varchar2
817 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
818 --
819 BEGIN
820 --
821 -- Call the supporting procedure to check startup mode
822 -- EDIT_HERE: The following call should be edited if certain types of rows
823 -- are not permitted.
824 IF (p_insert) THEN
825 hr_startup_data_api_support.chk_startup_action
826 (p_generic_allowed => TRUE
827 ,p_startup_allowed => TRUE
828 ,p_user_allowed => TRUE
829 ,p_business_group_id => p_business_group_id
830 ,p_legislation_code => p_legislation_code
831 ,p_legislation_subgroup => p_legislation_subgroup
832 );
833 ELSE
834 hr_startup_data_api_support.chk_upd_del_startup_action
835 (p_generic_allowed => TRUE
836 ,p_startup_allowed => TRUE
837 ,p_user_allowed => TRUE
838 ,p_business_group_id => p_business_group_id
839 ,p_legislation_code => p_legislation_code
840 ,p_legislation_subgroup => p_legislation_subgroup
841 );
842 END IF;
843 --
844 END chk_startup_action;
845 --
846 -- ----------------------------------------------------------------------------
847 -- |---------------------------< insert_validate >----------------------------|
848 -- ----------------------------------------------------------------------------
849 Procedure insert_validate
850 (p_effective_date in date
851 ,p_rec in pay_bad_shd.g_rec_type
852 ) is
853 --
854 l_proc varchar2(72) := g_package||'insert_validate';
855 --
856 Begin
857 hr_utility.set_location('Entering:'||l_proc, 5);
858 --
859 -- Call all supporting business operations
860 --
861 --
862 chk_startup_action(true
863 ,p_rec.business_group_id
864 ,p_rec.legislation_code
865 );
866 IF hr_startup_data_api_support.g_startup_mode
867 NOT IN ('GENERIC','STARTUP') THEN
868 --
869 -- Validate Important Attributes
870 --
871 hr_api.validate_bus_grp_id
872 (p_business_group_id => p_rec.business_group_id
873 ,p_associated_column1 => pay_bad_shd.g_tab_nam
874 || '.BUSINESS_GROUP_ID');
875 --
876 -- after validating the set of important attributes,
877 -- if Multiple Message Detection is enabled and at least
878 -- one error has been found then abort further validation.
879 --
880 hr_multi_message.end_validation_set;
881 END IF;
882 --
883 -- Validate Dependent Attributes
884 --
885 pay_bad_bus.chk_attribute_name
886 (p_attribute_id => p_rec.attribute_id
890 );
887 ,p_attribute_name => p_rec.attribute_name
888 ,p_business_group_id => p_rec.business_group_id
889 ,p_legislation_code => p_rec.legislation_code
891 --
892 pay_bad_bus.chk_user_attribute_name
893 (p_attribute_id => p_rec.attribute_id
894 ,p_user_attribute_name => p_rec.user_attribute_name
895 ,p_business_group_id => p_rec.business_group_id
896 ,p_legislation_code => p_rec.legislation_code
897 );
898
899 pay_bad_bus.chk_alterable
900 (p_effective_date => p_effective_date
901 ,p_alterable => p_rec.alterable
902 );
903 --
904 hr_utility.set_location(' Leaving:'||l_proc, 10);
905 End insert_validate;
906 -- ----------------------------------------------------------------------------
907 -- |---------------------------< delete_validate >----------------------------|
908 -- ----------------------------------------------------------------------------
909 Procedure delete_validate
910 (p_rec in pay_bad_shd.g_rec_type
911 ) is
912 --
913 l_proc varchar2(72) := g_package||'delete_validate';
914 --
915 Begin
916 hr_utility.set_location('Entering:'||l_proc, 5);
917 --
918 chk_startup_action(false
919 ,pay_bad_shd.g_old_rec.business_group_id
920 ,pay_bad_shd.g_old_rec.legislation_code
921 );
922 --
923 chk_for_child_rows
924 (p_attribute_id => p_rec.attribute_id
925 ,p_business_group_id => pay_bad_shd.g_old_rec.business_group_id
926 ,p_legislation_code => pay_bad_shd.g_old_rec.legislation_code
927 );
928 --
929 hr_utility.set_location(l_proc, 10);
930 --
931 -- NB. need to use g_old_rec, as p_rec is not pupulated with all the columns
932 -- for delete mode.
933 --
934 IF hr_startup_data_api_support.g_startup_mode
935 NOT IN ('GENERIC','STARTUP') THEN
936 hr_utility.set_location(l_proc, 15);
937 --
938 -- Validate Important Attributes
939 --
940 -- After validating the set of important attributes,
941 -- if Multiple Message Detection is enabled and at least
942 -- one error has been found then abort further validation.
943 --
944 hr_multi_message.end_validation_set;
945 hr_utility.set_location(l_proc, 20);
946 END IF;
947 --
948 -- Call all supporting business operations
949 --
950 hr_utility.set_location(' Leaving:'||l_proc, 30);
951 End delete_validate;
952 --
953 end pay_bad_bus;
[Home] [Help]