1 Package Body pay_rtu_bus as
2 /* $Header: pyrturhi.pkb 115.3 2002/12/09 15:08:35 divicker noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_rtu_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_run_type_usage_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_run_type_usage_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_run_type_usages_f rtu
30 where rtu.run_type_usage_id = p_run_type_usage_id
31 and pbg.business_group_id = rtu.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'run_type_usage_id'
47 ,p_argument_value => p_run_type_usage_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_run_type_usage_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pay_run_type_usages_f rtu
90 where rtu.run_type_usage_id = p_run_type_usage_id
91 and pbg.business_group_id (+) = rtu.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'run_type_usage_id'
107 ,p_argument_value => p_run_type_usage_id
108 );
109 --
110 if ( nvl(pay_rtu_bus.g_run_type_usage_id, hr_api.g_number)
111 = p_run_type_usage_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pay_rtu_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pay_rtu_bus.g_run_type_usage_id := p_run_type_usage_id;
142 pay_rtu_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in pay_rtu_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 --
184 Begin
185 --
186 -- Only proceed with the validation if a row exists for the current
187 -- record in the HR Schema.
188 --
189 IF NOT pay_rtu_shd.api_updating
190 (p_run_type_usage_id => p_rec.run_type_usage_id
191 ,p_effective_date => p_effective_date
192 ,p_object_version_number => p_rec.object_version_number
193 ) THEN
194 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
195 fnd_message.set_token('PROCEDURE ', l_proc);
196 fnd_message.set_token('STEP ', '5');
197 fnd_message.raise_error;
198 END IF;
199 --
200 hr_utility.set_location(l_proc, 10);
201 --
202 if nvl(p_rec.business_group_id, hr_api.g_number) <>
203 nvl(pay_rtu_shd.g_old_rec.business_group_id, hr_api.g_number) then
204 l_argument := 'business_group_id';
205 raise l_error;
206 end if;
207 hr_utility.set_location(l_proc, 15);
208 --
209 if p_rec.run_type_usage_id <> pay_rtu_shd.g_old_rec.run_type_usage_id then
210 l_argument := 'run_type_usage_id';
211 raise l_error;
212 end if;
213 hr_utility.set_location(l_proc, 20);
214 --
215 if p_rec.legislation_code <> pay_rtu_shd.g_old_rec.legislation_code then
216 l_argument := 'legislation_code';
217 raise l_error;
218 end if;
219 hr_utility.set_location(l_proc, 25);
220 --
221 if p_rec.parent_run_type_id <> pay_rtu_shd.g_old_rec.parent_run_type_id then
222 l_argument := 'parent_run_type_id';
223 raise l_error;
224 end if;
225 hr_utility.set_location(l_proc, 30);
226 --
227 if p_rec.child_run_type_id <> pay_rtu_shd.g_old_rec.child_run_type_id then
228 l_argument := 'child_run_type_id';
229 raise l_error;
230 end if;
231 hr_utility.set_location(l_proc, 35);
232 --
233 EXCEPTION
234 WHEN l_error THEN
235 hr_api.argument_changed_error
236 (p_api_name => l_proc
237 ,p_argument => l_argument);
238 WHEN OTHERS THEN
239 RAISE;
240 hr_utility.set_location(' Leaving:'|| l_proc, 40);
241 End chk_non_updateable_args;
242 --
243 -- ---------------------------------------------------------------------------
244 -- |-----------------------< chk_parent_run_type_id >------------------------|
245 -- ---------------------------------------------------------------------------
246 --
247 -- Description:
248 -- Checks the validity of the parent_run_type_id enterend by carrying out
249 -- the following:
250 -- - check that the parent_run_type_id exists
251 -- - check that the parent_run_type_id has a run_method = C
252 -- - check that the following rules apply:
253 --
254 -- Mode Run Type Available Components Resulting usage
255 -- ------ ----------- ------------------------------- ---------------
256 -- USER USER USER, STARTUP, GENERIC USER
257 -- USER STARTUP This mode cannot enter usages Error
258 -- for STARTUP run types
259 -- USER GENERIC This mode cannot enter usages Error
260 -- for GENERIC run types
261 -- STARTUP USER This mode cannot access USER Error
262 -- run types
263 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
264 -- STARTUP GENERIC This mode cannot enter usages Error
265 -- for GENERIC run types
266 -- GENERIC USER This mode cannot access USER Error
267 -- run types
268 -- GENERIC STARTUP This mode cannot access STARTUP Error
269 -- run types
270 -- GENERIC GENERIC GENERIC GENERIC
271 --
272 -- Pre-conditions:
273 -- None
274 --
275 -- In Arguments:
276 -- p_run_type_usage_id
277 -- p_parent_run_type_id
278 -- p_business_group_id
279 -- p_legislation_code
280 --
281 -- Post Success:
282 -- If the parent_run_type_id is valid then processing continues
283 --
284 -- Post Failure:
285 -- If any of the following cases are true then an application error will be
286 -- raised and processing is terminated:
287 --
288 -- a) parent_run_type_id does not exist
289 -- b) run_method of parent_run_type_id is not C
290 --
291 -- Access Status:
292 -- Internal Row Handler Use Only.
293 --
294 -- ---------------------------------------------------------------------------
295 PROCEDURE chk_parent_run_type_id
296 (p_run_type_usage_id in number
297 ,p_parent_run_type_id in number
298 ,p_effective_date in date
299 ,p_business_group_id in number
300 ,p_legislation_code in varchar2) is
301 --
302 l_exists varchar2(1);
303 l_proc varchar2(72) := g_package||'chk_parent_run_type_id';
304 --
305 CURSOR csr_chk_valid_parent
306 is
307 select 'Y'
308 from pay_run_types_f prt
309 where prt.run_type_id = p_parent_run_type_id
310 and prt.run_method = 'C'
311 and p_effective_date between prt.effective_start_date
312 and prt.effective_end_date
313 and ((p_business_group_id is not null
314 and prt.business_group_id = p_business_group_id)
315 or (p_business_group_id is null
316 and prt.business_group_id is null))
317 and ((p_legislation_code is not null
318 and prt.legislation_code = p_legislation_code)
319 or (p_legislation_code is null
320 and prt.legislation_code is null));
321 --
322 l_mode varchar2(10);
323 BEGIN
324 --
325 hr_utility.set_location('Entering: '|| l_proc, 5);
326 --
327 -- Only execute the cursor if absolutely necessary.
328 -- a) During update, the parent_run_type_id has actually changed to another not
329 -- null value, i,e, the value passed to this procedure is different to the
330 -- g_old_rec value.
331 -- b) During insert, the parent_run_type_id is null.
332 -- Can tell the difference between insert and update by looking at the
333 -- primary key value. For update it will be not null. For insert it will be
334 -- null, because pre_inset has not been called yet.
335 --
336 IF (((p_run_type_usage_id is not null) and
337 nvl(pay_rtu_shd.g_old_rec.parent_run_type_id, hr_api.g_number) <>
338 nvl(p_parent_run_type_id, hr_api.g_number))
339 or
340 (p_run_type_usage_id is null)) then
341 --
342 hr_utility.set_location(l_proc, 10);
343 --
344 -- Only need to open the cursor if parent_run_type_id is not null
345 --
346 if p_parent_run_type_id is not null then
347 --
348 IF p_business_group_id is NULL THEN
349 IF p_legislation_code is NULL THEN
350 --
351 -- If business_group and legislation code are not null then must
352 -- be in GENERIC mode, check by calling:
353 --
354 if hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
355 hr_utility.set_location(l_proc, 15);
356 --
357 OPEN csr_chk_valid_parent;
358 FETCH csr_chk_valid_parent INTO l_exists;
359 IF csr_chk_valid_parent%NOTFOUND THEN
360 --
361 CLOSE csr_chk_valid_parent;
362 -- parent run type does not exist
363 hr_utility.set_message(801, 'HR_33593_RTU_INV_P_RT');
364 hr_utility.raise_error;
365 --
366 END IF;
367 CLOSE csr_chk_valid_parent;
368 end if; -- if generic
369 --
370 ELSE
371 hr_utility.set_location(l_proc, 20);
372 --
373 -- If business_group_id is null, and legislation code is not null,
374 -- then must be in STRATUP mode, check by calling:
375 --
376 if hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
377 hr_utility.set_location(l_proc, 25);
378 --
379 OPEN csr_chk_valid_parent;
380 FETCH csr_chk_valid_parent INTO l_exists;
381 IF csr_chk_valid_parent%NOTFOUND THEN
382 --
383 CLOSE csr_chk_valid_parent;
384 -- parent run type_id does not exist
385 hr_utility.set_message(801, 'HR_33593_RTU_INV_P_RT');
386 hr_utility.raise_error;
387 END IF;
388 CLOSE csr_chk_valid_parent;
389 end if; -- if STARTUP
390 --
391 END IF; -- if p_legislation_code is null
392 ELSE -- if p_business_group_id is null
393 --
394 if p_legislation_code is null then
395 --
396 -- If business_group is not null, and legislation code is null then
397 -- must be in USER mode, check by calling:
398 --
399 if hr_startup_data_api_support.g_startup_mode = 'USER' then
400 hr_utility.set_location(l_proc, 30);
401 --
402 OPEN csr_chk_valid_parent;
403 FETCH csr_chk_valid_parent INTO l_exists;
404 IF csr_chk_valid_parent%NOTFOUND THEN
405 --
406 CLOSE csr_chk_valid_parent;
407 -- parent run type does not exist
408 hr_utility.set_message(801, 'HR_33593_RTU_INV_P_RT');
409 hr_utility.raise_error;
410 END IF;
411 CLOSE csr_chk_valid_parent;
412 --
413 end if; -- if USER
414 else -- p_legislation_code is not null
415 -- legislaiton_code cannot be NOT NULL in USER mode
416 hr_utility.set_message(801, 'HR_33594_RTU_INV_P_LEG');
417 hr_utility.raise_error;
418 end if;
419 END IF;
420 hr_utility.set_location(l_proc, 35);
421 end if;
422 hr_utility.set_location(l_proc, 40);
423 end if;
424 hr_utility.set_location('Leaving: '||l_proc, 45);
425 END chk_parent_run_type_id;
426 -- ---------------------------------------------------------------------------
427 -- |------------------------< chk_child_run_type_id >------------------------|
428 -- ---------------------------------------------------------------------------
429 --
430 -- Description:
431 -- Checks the validity of the child_run_type_id enterend by carrying out
432 -- the following:
433 -- - check that the child_run_type_id exists
434 -- - check that the following rules apply:
435 --
436 -- Mode Run Type Available Components Resulting usage
437 -- ------ ----------- ------------------------------- ---------------
438 -- USER USER USER, STARTUP, GENERIC USER
439 -- USER STARTUP This mode cannot enter usages Error
440 -- for STARTUP run types
441 -- USER GENERIC This mode cannot enter usages Error
442 -- for GENERIC run types
443 -- STARTUP USER This mode cannot access USER Error
444 -- run types
445 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
446 -- STARTUP GENERIC This mode cannot enter usages Error
447 -- for GENERIC run types
448 -- GENERIC USER This mode cannot access USER Error
449 -- run types
450 -- GENERIC STARTUP This mode cannot access STARTUP Error
451 -- run types
452 -- GENERIC GENERIC GENERIC GENERIC
453 --
454 -- Pre-conditions:
455 -- None
456 --
457 -- In Arguments:
458 -- p_child_run_type_id
459 -- p_business_group_id
460 -- p_legislation_code
461 --
462 -- Post Success:
463 -- If the child_run_type_id is valid then processing continues
464 --
465 -- Post Failure:
466 -- If any of the following cases are true then an application error will be
467 -- raised and processing is terminated:
468 --
469 -- a) child_run_type_id does not exist
470 --
471 -- Access Status:
472 -- Internal Row Handler Use Only.
473 --
474 -- ---------------------------------------------------------------------------
475 PROCEDURE chk_child_run_type_id
476 (p_run_type_usage_id in number
477 ,p_child_run_type_id in number
478 ,p_parent_run_type_id in number
479 ,p_effective_date in date
480 ,p_business_group_id in number
481 ,p_legislation_code in varchar2) is
482 --
483 l_exists varchar2(1);
484 l_proc varchar2(72) := g_package||'chk_child_run_type_id';
485 --
486 CURSOR csr_chk_valid_child
487 is
488 select prt.business_group_id
489 , prt.legislation_code
490 , prt.run_type_id
491 from pay_run_types_f prt
492 where prt.run_type_id = p_child_run_type_id
493 and p_effective_date between prt.effective_start_date
494 and prt.effective_end_date;
495 --
496 CURSOR csr_usage_exists
497 is
498 select 'Y'
499 from pay_run_type_usages_f rtu
500 where rtu.parent_run_type_id = p_parent_run_type_id
501 and rtu.child_run_type_id = p_child_run_type_id
502 and p_effective_date between rtu.effective_start_date
503 and rtu.effective_end_date;
504 --
505 l_bg_id number;
506 l_leg_code varchar2(20);
507 l_rt_id number;
508 l_derived_leg varchar2(20);
509 --
510 BEGIN
511 --
512 hr_utility.set_location('Entering: '|| l_proc, 5);
513 -- Only execute the cursor if absolutely necessary.
514 -- a) During update, the child_run_type_id has actually changed to another not
515 -- null value, i,e, the value passed to this procedure is different to the
516 -- g_old_rec value.
517 -- b) During insert, the child_run_type_id is null.
518 -- Can tell the difference between insert and update by looking at the
519 -- primary key value. For update it will be not null. For insert it will be
520 -- null, because pre_inset has not been called yet.
521 --
522 IF (((p_run_type_usage_id is not null) and
523 nvl(pay_rtu_shd.g_old_rec.child_run_type_id, hr_api.g_number) <>
524 nvl(p_child_run_type_id, hr_api.g_number))
525 or
526 (p_run_type_usage_id is null)) then
527 --
528 hr_utility.set_location(l_proc, 10);
529 --
530 -- Only need to open the cursor if child_run_type_id is not null
531 --
532 if p_child_run_type_id is not null then
533 --
534 --
535 IF p_business_group_id is NULL THEN
536 hr_utility.set_location(l_proc, 15);
537 --
538 IF p_legislation_code is NULL THEN
539 hr_utility.set_location(l_proc, 20);
540 --
541 -- If business group id and leg code are null, then we must be in
542 -- GENERIC mode, check this by calling:
543 --
544 if hr_startup_data_api_support.g_startup_mode = 'GENERIC' then
545 hr_utility.set_location(l_proc, 25);
546 --
547 OPEN csr_chk_valid_child;
548 FETCH csr_chk_valid_child INTO l_bg_id, l_leg_code, l_rt_id;
549 IF csr_chk_valid_child%NOTFOUND THEN
550 --
551 CLOSE csr_chk_valid_child;
552 -- invalid primary key error, child run type does not exist
553 hr_utility.set_message(801, 'HR_33595_RTU_INV_C_RT');
554 hr_utility.raise_error;
555 END IF;
556 CLOSE csr_chk_valid_child;
557 --
558 -- chk that this usage doesn't already exist
559 --
560 OPEN csr_usage_exists;
561 FETCH csr_usage_exists into l_exists;
562 IF csr_usage_exists%FOUND THEN
563 --
564 CLOSE csr_usage_exists;
565 -- usage already exists error
566 hr_utility.set_message(801, 'HR_33596_RTU_EXISTS');
567 hr_utility.raise_error;
568 END IF;
569 CLOSE csr_usage_exists;
570 --
571 -- if the child run type bg or leg code are not null then
572 -- the child run type is NOT a GENERIC row, raise error.
573 --
574 if (l_bg_id is not null
575 or l_leg_code is not null) then
576 --
577 -- child run type not of generic mode
578 hr_utility.set_message(801, 'HR_33597_RTU_INV_C_RT_G');
579 hr_utility.raise_error;
580 end if;
581 --
582 end if; -- if GENERIC
583 --
584 ELSE -- p_legislation_code is not null
585 hr_utility.set_location(l_proc, 30);
586 --
587 -- If business_group_id is null, and legislation_code is not null,
588 -- we must be in STARTUP mode, check this by calling:
589 --
590 if hr_startup_data_api_support.g_startup_mode = 'STARTUP' then
591 hr_utility.set_location(l_proc, 35);
592 --
593 OPEN csr_chk_valid_child;
594 FETCH csr_chk_valid_child INTO l_bg_id, l_leg_code, l_rt_id;
595 IF csr_chk_valid_child%NOTFOUND THEN
596 --
597 CLOSE csr_chk_valid_child;
598 -- child run type does not exist
599 hr_utility.set_message(801, 'HR_33595_RTU_INV_C_RT');
600 hr_utility.raise_error;
601 END IF;
602 CLOSE csr_chk_valid_child;
603 --
604 -- chk that this usage doesn't already exist
605 --
606 OPEN csr_usage_exists;
607 FETCH csr_usage_exists into l_exists;
608 IF csr_usage_exists%FOUND THEN
609 --
610 CLOSE csr_usage_exists;
611 -- usage already exists error
612 hr_utility.set_message(801, 'HR_33596_RTU_EXISTS');
613 hr_utility.raise_error;
614 END IF;
615 CLOSE csr_usage_exists;
616 --
617 -- If the bg of the child run type is not null, then the child
618 -- run type is not a STARTUP row, so error.
619 --
620 if l_bg_id is not null then
621 --
622 hr_utility.set_message(801, 'HR_33598_RTU_INV_C_RT_S');
623 hr_utility.raise_error;
624 --
625 elsif l_leg_code is not null then
626 --
627 if l_leg_code = p_legislation_code then
628 --
629 hr_utility.trace ('valid startup child run type id');
630 else
631 -- child run type leg code is not same as usage leg code
632 -- therefore is invalid
633 hr_utility.set_message(801, 'HR_33599_RTU_INV_LEG_S');
634 hr_utility.raise_error;
635 end if;
636 end if;
637 --
638 end if; -- if STARTUP
639 --
640 END IF; -- if p_legislation_code is null
641 --
642 ELSE -- p_business_group_id is not null
643 hr_utility.set_location(l_proc, 40);
644 --
645 if p_legislation_code is null then
646 --
647 -- If business_group_id is NOT NULL and legislation code is null then
648 -- we must be in USER mode, check this by calling:
649 --
650 if hr_startup_data_api_support.g_startup_mode = 'USER' then
651 hr_utility.set_location(l_proc, 45);
652 --
653 OPEN csr_chk_valid_child;
654 FETCH csr_chk_valid_child INTO l_bg_id, l_leg_code, l_rt_id;
655 IF csr_chk_valid_child%NOTFOUND THEN
656 --
657 CLOSE csr_chk_valid_child;
658 -- child run type does not exist
659 hr_utility.set_message(801, 'HR_33595_RTU_INV_C_RT');
660 hr_utility.raise_error;
661 END IF;
662 CLOSE csr_chk_valid_child;
663 --
664 -- chk that this usage doesn't already exist
665 --
666 OPEN csr_usage_exists;
667 FETCH csr_usage_exists into l_exists;
668 IF csr_usage_exists%FOUND THEN
669 --
670 CLOSE csr_usage_exists;
671 -- usage already exists error
672 hr_utility.set_message(801, 'HR_33596_RTU_EXISTS');
673 hr_utility.raise_error;
674 END IF;
675 CLOSE csr_usage_exists;
676 --
677 if l_bg_id is not null then
678 if l_bg_id = p_business_group_id then
679 --
680 hr_utility.trace('valid user child run type id');
681 --
682 else -- in USER mode so BG must not be null
683 -- child run type bg is not same as usage bg, so invalid
684 hr_utility.set_message(801, 'HR_33600_RTU_INV_BG');
685 hr_utility.raise_error;
686 end if;
687 else -- l_bg_id is null
688 if l_leg_code is NOT NULL then
689 --
690 -- As in USER mode p_legislation_code will be null, so need to get
691 -- the legislation code.
692 -- If p_run_type_usage_id is null i.e. this is an insert, need to
693 -- get the legislation code for the parent run type id, which has
694 -- already been validated in chk_parent_run_type.
695 -- Else this is an update or delete, so use the rtu return leg
696 -- code function
697 --
698 if p_run_type_usage_id is null then
699 l_derived_leg :=
700 pay_prt_bus.return_legislation_code(p_parent_run_type_id);
701 else -- p_run_type_usage_id is not null
702 l_derived_leg :=
703 pay_rtu_bus.return_legislation_code(p_run_type_usage_id);
704 end if;
705 --
706 if l_leg_code = l_derived_leg then
707 hr_utility.trace('valid user child run type id');
708 else
709 hr_utility.set_message(801, 'HR_33995_RTU_INV_LEG_U');
710 hr_utility.raise_error;
711 end if;
712 end if; -- l_leg_code is not null
713 end if; -- l_bg_is not null
714 end if; -- if USER
715 --
716 ELSE -- leg_code is not null
717 --
718 hr_utility.set_message(801, 'HR_33993_RTU_INV_BG_LEG');
719 hr_utility.raise_error;
720 --
721 END IF; -- leg code is null
722 --
723 END IF; -- p_business_group_id is null
724 hr_utility.set_location(l_proc, 50);
725 end if; -- p_child_run_type is null
726 hr_utility.set_location(l_proc, 55);
727 END IF;
728 hr_utility.set_location('Leaving: '||l_proc, 60);
729 END chk_child_run_type_id;
730 -- ---------------------------------------------------------------------------
731 -- |-----------------------------< chk_sequence >----------------------------|
732 -- ---------------------------------------------------------------------------
733 --
734 -- Description:
735 -- Checks the validity of the sequence enterend by carrying out the
736 -- following:
737 -- - check that the sequence is unique within a parent_run_type_id
738 --
739 -- Pre-conditions:
740 -- None
741 --
742 -- In Arguments:
743 -- p_run_type_usage_id
744 -- p_parent_run_type_id
745 -- p_sequence
746 -- p_effective_date
747 --
748 -- Post Success:
749 -- If the sequence is valid then processing continues
750 --
751 -- Post Failure:
752 -- If any of the following cases are true then an application error will be
753 -- raised and processing is terminated:
754 --
755 -- a) sequence is not unique within a run_type_usage_id
756 --
757 -- Access Status:
758 -- Internal Row Handler Use Only.
759 --
760 -- ---------------------------------------------------------------------------
761 PROCEDURE chk_sequence
762 (p_run_type_usage_id in number
763 ,p_parent_run_type_id in number
764 ,p_sequence in number
765 ,p_effective_date in date) is
766 --
767 l_exists varchar2(1);
768 l_proc varchar2(72) := g_package||'chk_sequence';
769 --
770 CURSOR csr_chk_valid_sequence
771 is
772 select null
773 from pay_run_type_usages_f rtu
774 where rtu.parent_run_type_id = p_parent_run_type_id
775 and rtu.sequence = p_sequence
776 and p_effective_date between rtu.effective_start_date
777 and rtu.effective_end_date;
778 --
779 BEGIN
780 --
781 hr_utility.set_location('Entering: '|| l_proc, 5);
782 --
783 -- Only execute the cursor if absolutely necessary.
784 -- a) During update, the sequence has actually changed to another not
785 -- null value, i,e, the value passed to this procedure is different to the
786 -- g_old_rec value.
787 -- b) During insert, the sequence is null.
788 -- Can tell the difference between insert and update by looking at the
789 -- primary key value. For update it will be not null. For insert it will be
790 -- null, because pre_inset has not been called yet.
791 --
792 IF (((p_run_type_usage_id is not null) and
793 nvl(pay_rtu_shd.g_old_rec.sequence, hr_api.g_number) <>
794 nvl(p_sequence, hr_api.g_number))
795 or
796 (p_run_type_usage_id is null)) then
797 --
798 hr_utility.set_location(l_proc, 10);
799 --
800 -- Only need to opent the cursor if is not null
801 --
802 if p_sequence is not null then
803 --
804 OPEN csr_chk_valid_sequence;
805 FETCH csr_chk_valid_sequence INTO l_exists;
806 IF csr_chk_valid_sequence%FOUND THEN
807 --
808 CLOSE csr_chk_valid_sequence;
809 hr_utility.set_message(801, 'HR_33994_RTU_DUP_SEQUENCE');
810 hr_utility.raise_error;
811 END IF;
812 CLOSE csr_chk_valid_sequence;
813 --
814 end if;
815 hr_utility.set_location(l_proc, 15);
816 --
817 END IF;
818 hr_utility.set_location('Leaving: '||l_proc, 20);
819 END chk_sequence;
820 -- ----------------------------------------------------------------------------
821 -- |--------------------------< dt_update_validate >--------------------------|
822 -- ----------------------------------------------------------------------------
823 -- {Start Of Comments}
824 --
825 -- Description:
826 -- This procedure is used for referential integrity of datetracked
827 -- parent entities when a datetrack update operation is taking place
828 -- and where there is no cascading of update defined for this entity.
829 --
830 -- Prerequisites:
831 -- This procedure is called from the update_validate.
832 --
833 -- In Parameters:
834 --
835 -- Post Success:
836 -- Processing continues.
837 --
838 -- Post Failure:
839 --
840 -- Developer Implementation Notes:
841 -- This procedure should not need maintenance unless the HR Schema model
842 -- changes.
843 --
844 -- Access Status:
845 -- Internal Row Handler Use Only.
846 --
847 -- {End Of Comments}
848 -- ----------------------------------------------------------------------------
849 Procedure dt_update_validate
850 (p_parent_run_type_id in number default hr_api.g_number
851 ,p_child_run_type_id in number default hr_api.g_number
852 ,p_datetrack_mode in varchar2
853 ,p_validation_start_date in date
854 ,p_validation_end_date in date
855 ) Is
856 --
857 l_proc varchar2(72) := g_package||'dt_update_validate';
858 l_integrity_error Exception;
859 l_table_name all_tables.table_name%TYPE;
860 --
861 Begin
862 --
863 -- Ensure that the p_datetrack_mode argument is not null
864 --
865 hr_api.mandatory_arg_error
866 (p_api_name => l_proc
867 ,p_argument => 'datetrack_mode'
868 ,p_argument_value => p_datetrack_mode
869 );
870 --
871 -- Mode will be valid, as this is checked at the start of the upd.
872 --
873 -- Ensure the arguments are not null
874 --
875 hr_api.mandatory_arg_error
876 (p_api_name => l_proc
877 ,p_argument => 'validation_start_date'
878 ,p_argument_value => p_validation_start_date
879 );
880 --
881 hr_api.mandatory_arg_error
882 (p_api_name => l_proc
883 ,p_argument => 'validation_end_date'
884 ,p_argument_value => p_validation_end_date
885 );
886 --
887 If ((nvl(p_parent_run_type_id, hr_api.g_number) <> hr_api.g_number) and
888 NOT (dt_api.check_min_max_dates
889 (p_base_table_name => 'pay_run_types_f'
890 ,p_base_key_column => 'RUN_TYPE_ID'
891 ,p_base_key_value => p_parent_run_type_id
892 ,p_from_date => p_validation_start_date
893 ,p_to_date => p_validation_end_date))) Then
894 l_table_name := 'run types';
895 raise l_integrity_error;
896 End If;
897 If ((nvl(p_child_run_type_id, hr_api.g_number) <> hr_api.g_number) and
898 NOT (dt_api.check_min_max_dates
899 (p_base_table_name => 'pay_run_types_f'
900 ,p_base_key_column => 'RUN_TYPE_ID'
901 ,p_base_key_value => p_child_run_type_id
902 ,p_from_date => p_validation_start_date
903 ,p_to_date => p_validation_end_date))) Then
904 l_table_name := 'run types';
905 raise l_integrity_error;
906 End If;
907 --
908 Exception
909 When l_integrity_error Then
910 --
911 -- A referential integrity check was violated therefore
912 -- we must error
913 --
914 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
915 fnd_message.set_token('TABLE_NAME', l_table_name);
916 fnd_message.raise_error;
917 When Others Then
918 --
919 -- An unhandled or unexpected error has occurred which
920 -- we must report
921 --
922 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
923 fnd_message.set_token('PROCEDURE', l_proc);
924 fnd_message.set_token('STEP','15');
925 fnd_message.raise_error;
926 End dt_update_validate;
927 --
928 -- ----------------------------------------------------------------------------
929 -- |--------------------------< dt_delete_validate >--------------------------|
930 -- ----------------------------------------------------------------------------
931 -- {Start Of Comments}
932 --
933 -- Description:
934 -- This procedure is used for referential integrity of datetracked
935 -- child entities when either a datetrack DELETE or ZAP is in operation
936 -- and where there is no cascading of delete defined for this entity.
937 -- For the datetrack mode of DELETE or ZAP we must ensure that no
938 -- datetracked child rows exist between the validation start and end
939 -- dates.
940 --
941 -- Prerequisites:
942 -- This procedure is called from the delete_validate.
943 --
944 -- In Parameters:
945 --
946 -- Post Success:
947 -- Processing continues.
948 --
949 -- Post Failure:
950 -- If a row exists by determining the returning Boolean value from the
951 -- generic dt_api.rows_exist function then we must supply an error via
952 -- the use of the local exception handler l_rows_exist.
953 --
954 -- Developer Implementation Notes:
955 -- This procedure should not need maintenance unless the HR Schema model
956 -- changes.
957 --
958 -- Access Status:
959 -- Internal Row Handler Use Only.
960 --
961 -- {End Of Comments}
962 -- ----------------------------------------------------------------------------
963 Procedure dt_delete_validate
964 (p_run_type_usage_id in number
965 ,p_datetrack_mode in varchar2
966 ,p_validation_start_date in date
967 ,p_validation_end_date in date
968 ) Is
969 --
970 l_proc varchar2(72) := g_package||'dt_delete_validate';
971 l_rows_exist Exception;
972 l_table_name all_tables.table_name%TYPE;
973 --
974 Begin
975 --
976 -- Ensure that the p_datetrack_mode argument is not null
977 --
978 hr_api.mandatory_arg_error
979 (p_api_name => l_proc
980 ,p_argument => 'datetrack_mode'
981 ,p_argument_value => p_datetrack_mode
982 );
983 --
984 -- Only perform the validation if the datetrack mode is either
985 -- DELETE or ZAP
986 --
987 If (p_datetrack_mode = hr_api.g_delete or
988 p_datetrack_mode = hr_api.g_zap) then
989 --
990 --
991 -- Ensure the arguments are not null
992 --
993 hr_api.mandatory_arg_error
994 (p_api_name => l_proc
995 ,p_argument => 'validation_start_date'
996 ,p_argument_value => p_validation_start_date
997 );
998 --
999 hr_api.mandatory_arg_error
1000 (p_api_name => l_proc
1001 ,p_argument => 'validation_end_date'
1002 ,p_argument_value => p_validation_end_date
1003 );
1004 --
1005 hr_api.mandatory_arg_error
1006 (p_api_name => l_proc
1007 ,p_argument => 'run_type_usage_id'
1008 ,p_argument_value => p_run_type_usage_id
1009 );
1010 --
1011 --
1012 --
1013 End If;
1014 --
1015 Exception
1016 When l_rows_exist Then
1017 --
1018 -- A referential integrity check was violated therefore
1019 -- we must error
1020 --
1021 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1022 fnd_message.set_token('TABLE_NAME', l_table_name);
1023 fnd_message.raise_error;
1024 When Others Then
1025 --
1026 -- An unhandled or unexpected error has occurred which
1027 -- we must report
1028 --
1029 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1030 fnd_message.set_token('PROCEDURE', l_proc);
1031 fnd_message.set_token('STEP','15');
1032 fnd_message.raise_error;
1033 --
1034 End dt_delete_validate;
1035 --
1036 -- ----------------------------------------------------------------------------
1037 -- |----------------------< chk_startup_action >------------------------------|
1038 -- ----------------------------------------------------------------------------
1039 --
1040 -- Description:
1041 -- This procedure will check that the current action is allowed according
1042 -- to the current startup mode.
1043 --
1044 -- ----------------------------------------------------------------------------
1045 PROCEDURE chk_startup_action
1046 (p_insert IN boolean
1047 ,p_business_group_id IN number
1048 ,p_legislation_code IN varchar2
1049 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1050 --
1051 BEGIN
1052 --
1053 -- Call the supporting procedure to check startup mode
1054 -- EDIT_HERE: The following call should be edited if certain types of rows
1055 -- are not permitted.
1056 IF (p_insert) THEN
1057 --
1058 -- Call procedure to check startup_action for inserts
1059 --
1060 hr_startup_data_api_support.chk_startup_action
1061 (p_generic_allowed => TRUE
1062 ,p_startup_allowed => TRUE
1063 ,p_user_allowed => TRUE
1064 ,p_business_group_id => p_business_group_id
1065 ,p_legislation_code => p_legislation_code
1066 ,p_legislation_subgroup => p_legislation_subgroup
1067 );
1068 ELSE
1069 --
1070 -- Call procedure to check startup action for upd and del
1071 --
1072 hr_startup_data_api_support.chk_upd_del_startup_action
1073 (p_generic_allowed => TRUE
1074 ,p_startup_allowed => TRUE
1075 ,p_user_allowed => TRUE
1076 ,p_business_group_id => p_business_group_id
1077 ,p_legislation_code => p_legislation_code
1078 ,p_legislation_subgroup => p_legislation_subgroup
1079 );
1080 END IF;
1081 --
1082 END chk_startup_action;
1083 -- ----------------------------------------------------------------------------
1084 -- |---------------------------< insert_validate >----------------------------|
1085 -- ----------------------------------------------------------------------------
1086 Procedure insert_validate
1087 (p_rec in pay_rtu_shd.g_rec_type
1088 ,p_effective_date in date
1089 ,p_datetrack_mode in varchar2
1090 ,p_validation_start_date in date
1091 ,p_validation_end_date in date
1092 ) is
1093 --
1094 l_proc varchar2(72) := g_package||'insert_validate';
1095 --
1096 Begin
1097 hr_utility.set_location('Entering:'||l_proc, 5);
1098 --
1099 -- Call all supporting business operations
1100 --
1101 /* RET removed this call infavour or new chk_startup_action
1102 IF p_rec.business_group_id is not null THEN
1103 --
1104 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1105 --
1106 END IF;
1107 */
1108 chk_startup_action(true
1109 ,p_rec.business_group_id
1110 ,p_rec.legislation_code
1111 );
1112 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP')
1113 THEN
1114 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1115 END IF;
1116 --
1117 hr_utility.set_location(l_proc, 10);
1118 --
1119 chk_parent_run_type_id
1120 (p_run_type_usage_id => p_rec.run_type_usage_id
1121 ,p_parent_run_type_id => p_rec.parent_run_type_id
1122 ,p_effective_date => p_effective_date
1123 ,p_business_group_id => p_rec.business_group_id
1124 ,p_legislation_code => p_rec.legislation_code);
1125 --
1126 hr_utility.set_location(l_proc, 15);
1127 --
1128 chk_child_run_type_id
1129 (p_run_type_usage_id => p_rec.run_type_usage_id
1130 ,p_child_run_type_id => p_rec.child_run_type_id
1131 ,p_parent_run_type_id => p_rec.parent_run_type_id
1132 ,p_effective_date => p_effective_date
1133 ,p_business_group_id => p_rec.business_group_id
1134 ,p_legislation_code => p_rec.legislation_code);
1135 --
1136 hr_utility.set_location(l_proc, 20);
1137 --
1138 chk_sequence (p_run_type_usage_id => p_rec.run_type_usage_id
1139 ,p_parent_run_type_id => p_rec.parent_run_type_id
1140 ,p_sequence => p_rec.sequence
1141 ,p_effective_date => p_effective_date);
1142 --
1143 hr_utility.set_location(' Leaving:'||l_proc, 25);
1144 End insert_validate;
1145 --
1146 -- ----------------------------------------------------------------------------
1147 -- |---------------------------< update_validate >----------------------------|
1148 -- ----------------------------------------------------------------------------
1149 Procedure update_validate
1150 (p_rec in pay_rtu_shd.g_rec_type
1151 ,p_effective_date in date
1152 ,p_datetrack_mode in varchar2
1153 ,p_validation_start_date in date
1154 ,p_validation_end_date in date
1155 ) is
1156 --
1157 l_proc varchar2(72) := g_package||'update_validate';
1158 --
1159 Begin
1160 hr_utility.set_location('Entering:'||l_proc, 5);
1161 --
1162 -- Call all supporting business operations
1163 --
1164 /* RET removed in favour of new chk_startup_data_api
1165 IF p_rec.business_group_id is not null THEN
1166 --
1167 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1168 --
1169 END IF;
1170 */
1171 chk_startup_action(false
1172 ,p_rec.business_group_id
1173 ,p_rec.legislation_code
1174 );
1175 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP')
1176 THEN
1177 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1178 END IF;
1179 --
1180 hr_utility.set_location(l_proc, 10);
1181 --
1182 chk_parent_run_type_id
1183 (p_run_type_usage_id => p_rec.run_type_usage_id
1184 ,p_parent_run_type_id => p_rec.parent_run_type_id
1185 ,p_effective_date => p_effective_date
1186 ,p_business_group_id => p_rec.business_group_id
1187 ,p_legislation_code => p_rec.legislation_code);
1188 --
1189 hr_utility.set_location(l_proc, 15);
1190 --
1191 chk_child_run_type_id
1192 (p_run_type_usage_id => p_rec.run_type_usage_id
1193 ,p_child_run_type_id => p_rec.child_run_type_id
1194 ,p_parent_run_type_id => p_rec.parent_run_type_id
1195 ,p_effective_date => p_effective_date
1196 ,p_business_group_id => p_rec.business_group_id
1197 ,p_legislation_code => p_rec.legislation_code);
1198 --
1199 hr_utility.set_location(l_proc, 20);
1200 --
1201 chk_sequence (p_run_type_usage_id => p_rec.run_type_usage_id
1202 ,p_parent_run_type_id => p_rec.parent_run_type_id
1203 ,p_sequence => p_rec.sequence
1204 ,p_effective_date => p_effective_date);
1205 --
1206 hr_utility.set_location(l_proc, 25);
1207 --
1208 -- Call the datetrack update integrity operation
1209 --
1210 dt_update_validate
1211 (p_parent_run_type_id => p_rec.parent_run_type_id
1212 ,p_child_run_type_id => p_rec.child_run_type_id
1213 ,p_datetrack_mode => p_datetrack_mode
1214 ,p_validation_start_date => p_validation_start_date
1215 ,p_validation_end_date => p_validation_end_date
1216 );
1217 --
1218 hr_utility.set_location(l_proc, 30);
1219 --
1220 chk_non_updateable_args
1221 (p_effective_date => p_effective_date
1222 ,p_rec => p_rec
1223 );
1224 --
1225 hr_utility.set_location(' Leaving:'||l_proc, 35);
1226 --
1227 End update_validate;
1228 --
1229 -- ----------------------------------------------------------------------------
1230 -- |---------------------------< delete_validate >----------------------------|
1231 -- ----------------------------------------------------------------------------
1232 Procedure delete_validate
1233 (p_rec in pay_rtu_shd.g_rec_type
1234 ,p_effective_date in date
1235 ,p_datetrack_mode in varchar2
1236 ,p_validation_start_date in date
1237 ,p_validation_end_date in date
1238 ) is
1239 --
1240 l_proc varchar2(72) := g_package||'delete_validate';
1241 --
1242 Begin
1243 hr_utility.set_location('Entering:'||l_proc, 5);
1244 --
1245 -- Call all supporting business operations
1246 --
1247 chk_startup_action(false
1248 ,pay_rtu_shd.g_old_rec.business_group_id
1249 ,pay_rtu_shd.g_old_rec.legislation_code
1250 );
1251 --
1252 dt_delete_validate
1253 (p_datetrack_mode => p_datetrack_mode
1254 ,p_validation_start_date => p_validation_start_date
1255 ,p_validation_end_date => p_validation_end_date
1256 ,p_run_type_usage_id => p_rec.run_type_usage_id
1257 );
1258 --
1259 hr_utility.set_location(' Leaving:'||l_proc, 10);
1260 End delete_validate;
1261 --
1262 end pay_rtu_bus;
[Home] [Help]