1 Package Body pay_rom_bus as
2 /* $Header: pyromrhi.pkb 115.3 2002/12/09 15:04:01 divicker noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_rom_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_org_method_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_run_type_org_method_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_org_methods_f rom
30 where rom.run_type_org_method_id = p_run_type_org_method_id
31 and pbg.business_group_id = rom.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_org_method_id'
47 ,p_argument_value => p_run_type_org_method_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_org_method_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_org_methods_f rom
90 where rom.run_type_org_method_id = p_run_type_org_method_id
91 and pbg.business_group_id (+) = rom.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_org_method_id'
107 ,p_argument_value => p_run_type_org_method_id
108 );
109 --
110 if ( nvl(pay_rom_bus.g_run_type_org_method_id, hr_api.g_number)
111 = p_run_type_org_method_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_rom_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_rom_bus.g_run_type_org_method_id := p_run_type_org_method_id;
142 pay_rom_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_rom_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_rom_shd.api_updating
190 (p_run_type_org_method_id => p_rec.run_type_org_method_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 -- EDIT_HERE: Add checks to ensure non-updateable args have
201 -- not been updated.
202 --
203 hr_utility.set_location(l_proc, 10);
204 --
205 if nvl(p_rec.business_group_id, hr_api.g_number) <>
206 nvl(pay_rom_shd.g_old_rec.business_group_id, hr_api.g_number) then
207 l_argument := 'business_group_id';
208 raise l_error;
209 end if;
210 hr_utility.set_location(l_proc, 15);
211 --
212 if nvl(p_rec.run_type_org_method_id, hr_api.g_number) <>
213 nvl(pay_rom_shd.g_old_rec.run_type_org_method_id,hr_api.g_number) then
214 l_argument := 'run_type_org_method_id';
215 raise l_error;
216 end if;
217 hr_utility.set_location(l_proc, 20);
218 --
219 if nvl(p_rec.legislation_code,hr_api.g_varchar2) <>
220 nvl(pay_rom_shd.g_old_rec.legislation_code,hr_api.g_varchar2) then
221 l_argument := 'legislation_code';
222 raise l_error;
223 end if;
224 hr_utility.set_location(l_proc, 25);
225 --
226 if nvl(p_rec.run_type_id, hr_api.g_number) <>
227 nvl(pay_rom_shd.g_old_rec.run_type_id, hr_api.g_number) then
228 l_argument := 'run_type_id';
229 raise l_error;
230 end if;
231 hr_utility.set_location(l_proc, 30);
232 --
233 if nvl(p_rec.org_payment_method_id, hr_api.g_number) <>
234 nvl(pay_rom_shd.g_old_rec.org_payment_method_id,hr_api.g_number) then
235 l_argument := 'org_payment_method_id';
236 raise l_error;
237 end if;
238 hr_utility.set_location(l_proc, 35);
239 --
240 EXCEPTION
241 WHEN l_error THEN
242 hr_api.argument_changed_error
243 (p_api_name => l_proc
244 ,p_argument => l_argument);
245 WHEN OTHERS THEN
246 RAISE;
247 End chk_non_updateable_args;
248 --
249 -- ---------------------------------------------------------------------------
250 -- |--------------------------< chk_run_type_id >----------------------------|
251 -- ---------------------------------------------------------------------------
252 --
253 -- Description:
254 -- Checks the validity of the run_type_id enterend by carrying out
255 -- the following:
256 -- - check that the run_type_id exists
257 -- - check that the following rules apply:
258 --
259 -- Mode Run Type Available Components Resulting method
260 -- ------ ----------- ------------------------------- ---------------
261 -- USER USER USER, STARTUP, GENERIC USER
262 -- USER STARTUP USER, STARTUP, GENERIC USER
263 -- USER GENERIC USER, STARTUP, GENERIC USER
264 -- STARTUP USER This mode cannot access USER Error
265 -- run types
266 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
267 -- STARTUP GENERIC STARTUP, GENERIC STARTUP
268 -- GENERIC USER This mode cannot access USER Error
269 -- run types
270 -- GENERIC STARTUP This mode cannot access STARTUP Error
271 -- run types
272 -- GENERIC GENERIC GENERIC GENERIC
273 --
274 -- Pre-conditions:
275 -- None
276 --
277 -- In Arguments:
278 -- p_run_type_org_method_id
279 -- p_run_type_id
280 -- p_effective_date
281 -- p_business_group_id
282 -- p_legislation_code
283 --
284 -- Post Success:
285 -- If the run_type_id is valid then processing continues
286 --
287 -- Post Failure:
288 -- If any of the following cases are true then an application error will be
289 -- raised and processing is terminated:
290 --
291 -- a) run_type_id does not exist
292 --
293 -- Access Status:
294 -- Internal Row Handler Use Only.
295 --
296 -- ---------------------------------------------------------------------------
297 PROCEDURE chk_run_type_id
298 (p_run_type_org_method_id in number
299 ,p_run_type_id in number
300 ,p_effective_date in date
301 ,p_business_group_id in number
302 ,p_legislation_code in varchar2) is
303 --
304 l_exists varchar2(1);
305 l_proc varchar2(72) := g_package||'chk_run_type_id';
306 l_legislation_code pay_run_type_org_methods_f.legislation_code%TYPE := Null;
307 --
308 CURSOR csr_chk_user_run_type(p_leg_code varchar2) is
309 select 'Y'
310 from pay_run_types_f prt
311 where prt.run_type_id = p_run_type_id
312 and p_effective_date between effective_start_date
313 and effective_end_date
314 and ((prt.business_group_id is not null
315 and prt.business_group_id = p_business_group_id)
316 or (prt.legislation_code is not null
317 and prt.legislation_code = p_leg_code)
318 or (prt.business_group_id is null
319 and prt.legislation_code is null));
320 --
321 CURSOR csr_chk_startup_run_type is
322 select 'Y'
323 from pay_run_types_f prt
324 where prt.run_type_id = p_run_type_id
325 and p_effective_date between effective_start_date
326 and effective_end_date
327 and prt.business_group_id is null
328 and ((p_legislation_code is not null
329 and prt.legislation_code = p_legislation_code)
330 or (prt.legislation_code is null));
331 --
332 CURSOR csr_chk_generic_run_type is
333 select 'Y'
334 from pay_run_types_f prt
335 where prt.run_type_id = p_run_type_id
336 and p_effective_date between effective_start_date
337 and effective_end_date
338 and prt.business_group_id is null
339 and prt.legislation_code is null;
340 --
341 BEGIN
342 --
343 hr_utility.set_location('Entering: '|| l_proc, 5);
344 --
345 -- Only execute the cursor if absolutely necessary.
346 -- a) During update, the run_type_id has actually changed to another not
347 -- null value, i,e, the value passed to this procedure is different to the
348 -- g_old_rec value.
352 -- null because pre_insert has not been called yet.
349 -- b) During insert, the run_type_id is null.
350 -- Can tell the difference between insert and update by looking at the
351 -- primary key value. For update it will be not null. For insert it will be
353 --
354 IF (((p_run_type_org_method_id is not null) and
355 nvl(pay_rom_shd.g_old_rec.run_type_id, hr_api.g_number) <>
356 nvl(p_run_type_id, hr_api.g_number)) or
357 (p_run_type_org_method_id is null)) THEN
358 --
359 hr_utility.set_location(l_proc, 10);
360 --
361 -- Only need to open the cursor if run_type_id is not null
362 --
363 IF p_run_type_id is not null THEN
364 --
365 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
366 hr_utility.set_location(l_proc, 15);
367 --
368 OPEN csr_chk_generic_run_type;
369 FETCH csr_chk_generic_run_type INTO l_exists;
370 IF csr_chk_generic_run_type%NOTFOUND THEN
371 --
372 CLOSE csr_chk_generic_run_type;
373 hr_utility.set_message(801, 'HR_33587_INVALID_RT_FOR_MODE');
374 hr_utility.raise_error;
375 --
376 END IF;
377 CLOSE csr_chk_generic_run_type;
378 --
379 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
380 hr_utility.set_location(l_proc, 20);
381 --
382 OPEN csr_chk_startup_run_type;
383 FETCH csr_chk_startup_run_type INTO l_exists;
384 IF csr_chk_startup_run_type%NOTFOUND THEN
385 --
386 CLOSE csr_chk_startup_run_type;
387 hr_utility.set_message(801, 'HR_33587_INVALID_RT_FOR_MODE');
388 hr_utility.raise_error;
389 END IF;
390 CLOSE csr_chk_startup_run_type;
391 --
392 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
393 hr_utility.set_location(l_proc, 25);
394 --
395 IF p_run_type_org_method_id is not null THEN
396 l_legislation_code := pay_rom_bus.return_legislation_code(p_run_type_org_method_id);
397 ELSE
398 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
399 END IF;
400 --
401 OPEN csr_chk_user_run_type(l_legislation_code);
402 FETCH csr_chk_user_run_type INTO l_exists;
403 IF csr_chk_user_run_type%NOTFOUND THEN
404 --
405 CLOSE csr_chk_user_run_type;
406 hr_utility.set_message(801, 'HR_33589_INVALID_RUN_TYPE');
407 hr_utility.raise_error;
408 END IF;
409 CLOSE csr_chk_user_run_type;
410 --
411 END IF;
412 --
413 END IF;
414 --
415 END IF;
416 --
417 hr_utility.set_location('Leaving: '||l_proc, 30);
418 --
419 end chk_run_type_id;
420 --
421 -- ---------------------------------------------------------------------------
422 -- |---------------------< chk_org_payment_method_id >-----------------------|
423 -- ---------------------------------------------------------------------------
424 --
425 -- Description:
426 -- Checks the validity of the org_payment_method_id entered by carrying out
427 -- the following:
428 -- - check that the org_payment_method_id exists
429 -- - check that the following rules apply:
430 --
431 -- Mode Org Pay. Method Available Components Resulting usage
432 -- ------ --------------- ----------------------------- ---------------
433 -- USER USER USER, STARTUP, GENERIC USER
434 -- STARTUP USER This mode cannot access USER Error
435 -- GENERIC USER This mode cannot access USER Error
436 --
437 -- NB. Only USER defined organization payment methods exist.
438 --
439 -- Pre-conditions:
440 -- None
441 --
442 -- In Arguments:
443 -- p_run_type_org_method_id
444 -- p_org_payment_method_id
445 -- p_effective_date
446 -- p_business_group_id
447 --
448 -- Post Success:
449 -- If the org_payment_method_id is valid then processing continues
450 --
451 -- Post Failure:
452 -- If any of the following cases are true then an application error will be
453 -- raised and processing is terminated:
454 --
455 -- a) org_payment_method_id does not exist
456 -- b) Either STARTUP or GENERIC mode is used.
457 --
458 -- Access Status:
459 -- Internal Row Handler Use Only.
460 --
461 -- ---------------------------------------------------------------------------
462 PROCEDURE chk_org_payment_method_id
463 (p_run_type_org_method_id in number
464 ,p_org_payment_method_id in number
465 ,p_effective_date in date
466 ,p_business_group_id in number) is
467 --
468 l_exists varchar2(1);
469 l_proc varchar2(72) := g_package||'chk_org_payment_method_id';
470 --
471 CURSOR csr_chk_org_pay_method is
472 select 'Y'
473 from pay_org_payment_methods_f opm
474 where opm.org_payment_method_id = p_org_payment_method_id
475 and p_effective_date between opm.effective_start_date
476 and opm.effective_end_date
480 --
477 and opm.business_group_id = p_business_group_id;
478 --
479 BEGIN
481 hr_utility.set_location('Entering: '|| l_proc, 5);
482 --
483 -- Only execute the cursor if absolutely necessary.
484 -- a) During update, the org_payment_method_id has actually changed to another not
485 -- null value, i,e, the value passed to this procedure is different to the
486 -- g_old_rec value.
487 -- b) During insert, the org_payment_method_id is null.
488 -- Can tell the difference between insert and update by looking at the
489 -- primary key value. For update it will be not null. For insert it will be
490 -- null because pre_insert has not been called yet.
491 --
492 IF (((p_run_type_org_method_id is not null) and
493 nvl(pay_rom_shd.g_old_rec.org_payment_method_id, hr_api.g_number) <>
494 nvl(p_org_payment_method_id, hr_api.g_number)) or
495 (p_run_type_org_method_id is null)) THEN
496 --
497 hr_utility.set_location(l_proc, 10);
498 --
499 -- Only need to open the cursor if org_payment_method_id is not null
500 --
501 IF p_org_payment_method_id is not null THEN
502 --
503 IF hr_startup_data_api_support.g_startup_mode in ('GENERIC','STARTUP') THEN
504 hr_utility.set_location(l_proc, 15);
505 --
506 hr_utility.set_message(801, 'HR_33591_ROM_INV_PAYM_METHOD');
507 hr_utility.raise_error;
508 --
509 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
510 --
511 hr_utility.set_location(l_proc, 20);
512 --
513 OPEN csr_chk_org_pay_method;
514 FETCH csr_chk_org_pay_method INTO l_exists;
515 IF csr_chk_org_pay_method%NOTFOUND THEN
516 --
517 CLOSE csr_chk_org_pay_method;
518 hr_utility.set_message(801, 'HR_33591_ROM_INV_PAYM_METHOD');
519 hr_utility.raise_error;
520 END IF;
521 CLOSE csr_chk_org_pay_method;
522 --
523 END IF;
524 --
525 END IF;
526 --
527 END IF;
528 --
529 hr_utility.set_location('Leaving: '||l_proc, 25);
530 --
531 end chk_org_payment_method_id;
532 --
533 -- ---------------------------------------------------------------------------
534 -- |---------------------------< chk_priority >----------------------------|
535 -- ---------------------------------------------------------------------------
536 --
537 -- Description:
538 -- Checks the validity of the priority enterend by carrying out the
539 -- following:
540 -- - check that the priority is unique for a particular run_type/
541 -- org_payment_method combination
542 -- - check that the priority is between 1 and 99
543 --
544 -- Pre-conditions:
545 -- None
546 --
547 -- In Arguments:
548 -- p_run_type_org_method_id
549 -- p_org_payment_method_id
550 -- p_run_type_id
551 -- p_priority
552 -- p_business_group_id
553 -- p_legislation_code
554 -- p_validation_start_date
555 -- p_validation_end_date
556 --
557 -- Post Success:
558 -- If the priority is valid then processing continues
559 --
560 -- Post Failure:
561 -- If any of the following cases are true then an application error will be
562 -- raised and processing is terminated:
563 --
564 -- a) it is not unique
565 -- b) it is not between 1 and 99
566 --
567 -- Access Status:
568 -- Internal Row Handler Use Only.
569 --
570 -- ---------------------------------------------------------------------------
571 PROCEDURE chk_priority
572 (p_run_type_org_method_id in number
573 ,p_org_payment_method_id in number
574 ,p_run_type_id in number
575 ,p_priority in number
576 ,p_business_group_id in number
577 ,p_legislation_code in varchar2
578 ,p_validation_start_date in date
579 ,p_validation_end_date in date) is
580 --
581 l_exists varchar2(1);
582 l_proc varchar2(72) := g_package||'chk_priority';
583 --
584 CURSOR csr_chk_unique_priority_bg
585 is
586 select null
587 from pay_run_type_org_methods_f rom
588 where rom.run_type_id = p_run_type_id
589 and rom.org_payment_method_id = p_org_payment_method_id
590 and rom.priority = p_priority
591 and rom.effective_start_date <= p_validation_end_date
592 and rom.effective_end_date >= p_validation_start_date
593 and rom.business_group_id = p_business_group_id;
594 --
595 CURSOR csr_chk_unique_priority_leg
596 is
597 select null
598 from pay_run_type_org_methods_f rom
599 where rom.run_type_id = p_run_type_id
600 and rom.org_payment_method_id = p_org_payment_method_id
601 and rom.priority = p_priority
602 and rom.effective_start_date <= p_validation_end_date
603 and rom.effective_end_date >= p_validation_start_date
604 and rom.legislation_code = p_legislation_code;
605 --
606 BEGIN
607 --
608 hr_utility.set_location('Entering: '|| l_proc, 5);
609 --
610 IF ((p_priority is not null) AND (p_priority NOT BETWEEN 1 and 99)) THEN
611 --
612 hr_utility.set_message(801, 'HR_33582_ROM_PRIORITY_RANGE');
613 hr_utility.raise_error;
614 --
615 END IF;
616 --
617 -- Only execute the cursor if absolutely necessary.
618 -- a) During update, the priority has actually changed to another not
619 -- null value, i,e, the value passed to this procedure is different to the
623 -- primary key value. For update it will be not null. For insert it will be
620 -- g_old_rec value.
621 -- b) During insert, the priority is null.
622 -- Can tell the difference between insert and update by looking at the
624 -- null, because pre_inset has not been called yet.
625 --
626 IF (((p_run_type_org_method_id is not null) and
627 nvl(pay_rom_shd.g_old_rec.priority, hr_api.g_number) <>
628 nvl(p_priority, hr_api.g_number))
629 or
630 (p_run_type_org_method_id is null)) then
631 --
632 hr_utility.set_location(l_proc, 10);
633 --
634 -- Only need to open the cursors if priority is not null
635 --
636 if p_priority is not null then
637 --
638 IF p_business_group_id is null THEN
639 --
640 OPEN csr_chk_unique_priority_leg;
641 FETCH csr_chk_unique_priority_leg INTO l_exists;
642 IF csr_chk_unique_priority_leg%FOUND THEN
643 --
644 CLOSE csr_chk_unique_priority_leg;
645 hr_utility.set_message(801, 'HR_33584_ROM_PRIORITY_UNIQUE');
646 hr_utility.raise_error;
647 END IF;
648 CLOSE csr_chk_unique_priority_leg;
649 hr_utility.set_location(l_proc, 15);
650 --
651 ELSE
652 OPEN csr_chk_unique_priority_bg;
653 FETCH csr_chk_unique_priority_bg INTO l_exists;
654 IF csr_chk_unique_priority_bg%FOUND THEN
655 --
656 CLOSE csr_chk_unique_priority_bg;
657 hr_utility.set_message(801, 'HR_33584_ROM_PRIORITY_UNIQUE');
658 hr_utility.raise_error;
659 END IF;
660 CLOSE csr_chk_unique_priority_bg;
661 hr_utility.set_location(l_proc, 20);
662 --
663 END IF;
664 hr_utility.set_location(l_proc, 25);
665 --
666 else -- p_priority is null
667 hr_utility.set_message(801, 'HR_33583_ROM_PRIORITY_NULL');
668 hr_utility.raise_error;
669 end if;
670 hr_utility.set_location(l_proc, 30);
671 END IF;
672 hr_utility.set_location('Leaving: '||l_proc, 35);
673 END chk_priority;
674 --
675 -- ---------------------------------------------------------------------------
676 -- |-----------------------< chk_amount_percent >--------------------------|
677 -- ---------------------------------------------------------------------------
678 --
679 -- Description:
680 -- Checks the validity of the amount and percent enterend by carrying out
681 -- the following:
682 -- - check amount is >0
683 -- - check that only one of amount and percent are null
684 -- - if amount is not null check it has the correct money format
685 -- - if percent is not null check it has the correct format with 2 decimal
686 -- places
687 --
688 -- Pre-conditions:
689 -- None
690 --
691 -- In Arguments:
692 -- p_percent
693 -- p_amount
694 -- p_org_payment_method_id
695 --
696 -- Post Success:
697 -- If amount and percent are valid then processing continues
698 --
699 -- Post Failure:
700 -- If either percent or amount are invalid then an application error will be
701 -- raised and processing is terminated:
702 --
703 --
704 -- Access Status:
705 -- Internal Row Handler Use Only.
706 --
707 -- ---------------------------------------------------------------------------
708 PROCEDURE chk_percent_amount
709 (p_percent in number
710 ,p_amount in number
711 ,p_org_payment_method_id in number) is
712 --
713 l_exists varchar2(1);
714 l_proc varchar2(72) := g_package||'chk_percent_amount';
715 l_curcode varchar2(15);
716 l_amount number(38);
717 l_percentage number(10);
718 --
719 cursor get_curcode
720 is
721 select currency_code
722 from pay_org_payment_methods_f
723 where org_payment_method_id = p_org_payment_method_id;
724 --
725 BEGIN
726 hr_utility.set_location('Entering: '||l_proc, 5);
727 --
728 -- if amount is not null then percent is null, and vice versa
729 --
730 IF p_amount is null THEN
731 IF p_percent is null then
732 hr_utility.set_message(801, 'HR_6680_PPM_AMT_PERC');
733 hr_utility.raise_error;
734 END IF;
735 --
736 hr_utility.set_location('Entering: '||l_proc, 10);
737 --
738 ELSE
739 IF p_percent is not null THEN
740 hr_utility.set_message(801, 'HR_6221_PAYM_INVALID_PPM');
741 hr_utility.raise_error;
742 END IF;
743 END IF;
744 --
745 hr_utility.set_location(l_proc, 15);
746 --
747 -- Check formats
748 --
749 if p_amount < 0 then
750 hr_utility.set_message(801, 'HR_7355_PPM_AMOUNT_NEGATIVE');
751 hr_utility.raise_error;
752 end if;
753 --
754 if p_percent not between 0 and 100 then
755 hr_utility.set_message(801, 'HR_7040_PERCENT_RANGE');
756 hr_utility.raise_error;
757 end if;
758 --
759 hr_utility.set_location(l_proc, 20);
760 --
761 if p_amount is not null then
762 --
763 -- Check that Amount has a money format
764 --
765 l_amount := to_char(p_amount);
766 open get_curcode;
767 fetch get_curcode into l_curcode;
768 close get_curcode;
769 --
770 hr_dbchkfmt.is_db_format (p_value => l_amount,
771 p_arg_name => 'AMOUNT',
775 --
772 p_format => 'M',
773 p_curcode => l_curcode);
774 else
776 hr_utility.set_location(l_proc, 25);
777 --
778 -- p_percent is not null so check that format is decimal with
779 -- 2 decimal places
780 --
781 l_percentage := to_char(p_percent);
782 --
783 hr_dbchkfmt.is_db_format (p_value => l_percentage,
784 p_arg_name => 'PERCENTAGE',
785 p_format => 'H_DECIMAL2');
786 end if;
787 --
788 hr_utility.set_location('Leaving: '||l_proc, 30);
789 END chk_percent_amount;
790 --
791 -- ---------------------------------------------------------------------------
792 -- |--------------------------< chk_bg_leg_code >----------------------------|
793 -- ---------------------------------------------------------------------------
794 --
795 -- Description:
796 -- Checks the validity of the business_group_id and legislation code entered
797 -- by enforcing the following:
798 --
799 -- Mode Business Group ID Legislation Code
800 -- ------------- -------------------- ------------------------------
801 -- USER NOT NULL NULL
802 -- STARTUP NULL NOT NULL
803 -- GENERIC NULL NULL
804 --
805 -- Pre-conditions:
806 -- None
807 --
808 -- In Arguments:
809 -- p_business_group_id
810 -- p_legislation_code
811 --
812 -- Post Success:
813 -- If the combination is valid then processing continues
814 --
815 -- Post Failure:
816 -- If any of the following cases are true then an application error will be
817 -- raised and processing is terminated:
818 --
819 -- a) Combination of business_group_id and legislation_code is anything other
820 -- than detailed above.
821 --
822 -- Access Status:
823 -- Internal Row Handler Use Only.
824 --
825 -- ---------------------------------------------------------------------------
826 PROCEDURE chk_bg_leg_code
827 (p_business_group_id in number
828 ,p_legislation_code in varchar2) is
829 --
830 l_proc varchar2(72) := g_package||'chk_bg_leg_code';
831 --
832 BEGIN
833 --
834 hr_utility.set_location('Entering: '|| l_proc, 5);
835 --
836 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
837 --
838 hr_utility.set_location(l_proc, 15);
839 --
840 IF ((p_business_group_id is not null)
841 or (p_legislation_code is not null)) THEN
842 --
843 hr_utility.set_message(801, 'HR_33586_INVALID_BG_LEG_COMBI');
844 hr_utility.raise_error;
845 --
846 END IF;
847 --
848 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
849 --
850 hr_utility.set_location(l_proc, 20);
851 --
852 IF ((p_business_group_id is not null)
853 or (p_legislation_code is null)) THEN
854 --
855 hr_utility.set_message(801, 'HR_33586_INVALID_BG_LEG_COMBI');
856 hr_utility.raise_error;
857 --
858 END IF;
859 --
860 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
861 --
862 hr_utility.set_location(l_proc, 25);
863 --
864 IF ((p_business_group_id is null)
865 or (p_legislation_code is not null)) THEN
866 --
867 hr_utility.set_message(801, 'HR_33586_INVALID_BG_LEG_COMBI');
868 hr_utility.raise_error;
869 --
870 END IF;
871 --
872 END IF;
873 --
874 hr_utility.set_location('Leaving: '||l_proc, 30);
875 --
876 end chk_bg_leg_code;
877 --
878 -- ----------------------------------------------------------------------------
879 -- |--------------------------< dt_update_validate >--------------------------|
880 -- ----------------------------------------------------------------------------
881 -- {Start Of Comments}
882 --
883 -- Description:
884 -- This procedure is used for referential integrity of datetracked
885 -- parent entities when a datetrack update operation is taking place
886 -- and where there is no cascading of update defined for this entity.
887 --
888 -- Prerequisites:
889 -- This procedure is called from the update_validate.
890 --
891 -- In Parameters:
892 --
893 -- Post Success:
894 -- Processing continues.
895 --
896 -- Post Failure:
897 --
898 -- Developer Implementation Notes:
899 -- This procedure should not need maintenance unless the HR Schema model
900 -- changes.
901 --
902 -- Access Status:
903 -- Internal Row Handler Use Only.
904 --
905 -- {End Of Comments}
906 -- ----------------------------------------------------------------------------
907 Procedure dt_update_validate
908 (p_org_payment_method_id in number default hr_api.g_number
909 ,p_run_type_id in number default hr_api.g_number
910 ,p_datetrack_mode in varchar2
911 ,p_validation_start_date in date
912 ,p_validation_end_date in date
913 ) Is
914 --
915 l_proc varchar2(72) := g_package||'dt_update_validate';
916 l_integrity_error Exception;
917 l_table_name all_tables.table_name%TYPE;
918 --
919 Begin
920 --
921 -- Ensure that the p_datetrack_mode argument is not null
922 --
923 hr_api.mandatory_arg_error
924 (p_api_name => l_proc
928 --
925 ,p_argument => 'datetrack_mode'
926 ,p_argument_value => p_datetrack_mode
927 );
929 -- Mode will be valid, as this is checked at the start of the upd.
930 --
931 -- Ensure the arguments are not null
932 --
933 hr_api.mandatory_arg_error
934 (p_api_name => l_proc
935 ,p_argument => 'validation_start_date'
936 ,p_argument_value => p_validation_start_date
937 );
938 --
939 hr_api.mandatory_arg_error
940 (p_api_name => l_proc
941 ,p_argument => 'validation_end_date'
942 ,p_argument_value => p_validation_end_date
943 );
944 --
945 If ((nvl(p_org_payment_method_id, hr_api.g_number) <> hr_api.g_number) and
946 NOT (dt_api.check_min_max_dates
947 (p_base_table_name => 'pay_org_payment_methods_f'
948 ,p_base_key_column => 'ORG_PAYMENT_METHOD_ID'
949 ,p_base_key_value => p_org_payment_method_id
950 ,p_from_date => p_validation_start_date
951 ,p_to_date => p_validation_end_date))) Then
952 l_table_name := 'org payment methods';
953 raise l_integrity_error;
954 End If;
955 If ((nvl(p_run_type_id, hr_api.g_number) <> hr_api.g_number) and
956 NOT (dt_api.check_min_max_dates
957 (p_base_table_name => 'pay_run_types_f'
958 ,p_base_key_column => 'RUN_TYPE_ID'
959 ,p_base_key_value => p_run_type_id
960 ,p_from_date => p_validation_start_date
961 ,p_to_date => p_validation_end_date))) Then
962 l_table_name := 'run types';
963 raise l_integrity_error;
964 End If;
965 --
966 Exception
967 When l_integrity_error Then
968 --
969 -- A referential integrity check was violated therefore
970 -- we must error
971 --
972 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
973 fnd_message.set_token('TABLE_NAME', l_table_name);
974 fnd_message.raise_error;
975 When Others Then
976 --
977 -- An unhandled or unexpected error has occurred which
978 -- we must report
979 --
980 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
981 fnd_message.set_token('PROCEDURE', l_proc);
982 fnd_message.set_token('STEP','15');
983 fnd_message.raise_error;
984 End dt_update_validate;
985 --
986 -- ----------------------------------------------------------------------------
987 -- |--------------------------< dt_delete_validate >--------------------------|
988 -- ----------------------------------------------------------------------------
989 -- {Start Of Comments}
990 --
991 -- Description:
992 -- This procedure is used for referential integrity of datetracked
993 -- child entities when either a datetrack DELETE or ZAP is in operation
994 -- and where there is no cascading of delete defined for this entity.
995 -- For the datetrack mode of DELETE or ZAP we must ensure that no
996 -- datetracked child rows exist between the validation start and end
997 -- dates.
998 --
999 -- Prerequisites:
1000 -- This procedure is called from the delete_validate.
1001 --
1002 -- In Parameters:
1003 --
1004 -- Post Success:
1005 -- Processing continues.
1006 --
1007 -- Post Failure:
1008 -- If a row exists by determining the returning Boolean value from the
1009 -- generic dt_api.rows_exist function then we must supply an error via
1010 -- the use of the local exception handler l_rows_exist.
1011 --
1012 -- Developer Implementation Notes:
1013 -- This procedure should not need maintenance unless the HR Schema model
1014 -- changes.
1015 --
1016 -- Access Status:
1017 -- Internal Row Handler Use Only.
1018 --
1019 -- {End Of Comments}
1020 -- ----------------------------------------------------------------------------
1021 Procedure dt_delete_validate
1022 (p_run_type_org_method_id in number
1023 ,p_datetrack_mode in varchar2
1024 ,p_validation_start_date in date
1025 ,p_validation_end_date in date
1026 ) Is
1027 --
1028 l_proc varchar2(72) := g_package||'dt_delete_validate';
1029 l_rows_exist Exception;
1030 l_table_name all_tables.table_name%TYPE;
1031 --
1032 Begin
1033 --
1034 -- Ensure that the p_datetrack_mode argument is not null
1035 --
1036 hr_api.mandatory_arg_error
1037 (p_api_name => l_proc
1038 ,p_argument => 'datetrack_mode'
1039 ,p_argument_value => p_datetrack_mode
1040 );
1041 --
1042 -- Only perform the validation if the datetrack mode is either
1043 -- DELETE or ZAP
1044 --
1045 If (p_datetrack_mode = hr_api.g_delete or
1046 p_datetrack_mode = hr_api.g_zap) then
1047 --
1048 --
1049 -- Ensure the arguments are not null
1050 --
1051 hr_api.mandatory_arg_error
1052 (p_api_name => l_proc
1053 ,p_argument => 'validation_start_date'
1054 ,p_argument_value => p_validation_start_date
1055 );
1056 --
1057 hr_api.mandatory_arg_error
1058 (p_api_name => l_proc
1059 ,p_argument => 'validation_end_date'
1060 ,p_argument_value => p_validation_end_date
1061 );
1062 --
1063 hr_api.mandatory_arg_error
1064 (p_api_name => l_proc
1065 ,p_argument => 'run_type_org_method_id'
1066 ,p_argument_value => p_run_type_org_method_id
1067 );
1068 --
1072 --
1069 --
1070 --
1071 End If;
1073 Exception
1074 When l_rows_exist Then
1075 --
1076 -- A referential integrity check was violated therefore
1077 -- we must error
1078 --
1079 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1080 fnd_message.set_token('TABLE_NAME', l_table_name);
1081 fnd_message.raise_error;
1082 When Others Then
1083 --
1084 -- An unhandled or unexpected error has occurred which
1085 -- we must report
1086 --
1087 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1088 fnd_message.set_token('PROCEDURE', l_proc);
1089 fnd_message.set_token('STEP','15');
1090 fnd_message.raise_error;
1091 --
1092 End dt_delete_validate;
1093 --
1094 -- ----------------------------------------------------------------------------
1095 -- |----------------------< chk_startup_action >------------------------------|
1096 -- ----------------------------------------------------------------------------
1097 --
1098 -- Description:
1099 -- This procedure will check that the current action is allowed according
1100 -- to the current startup mode.
1101 --
1102 -- ----------------------------------------------------------------------------
1103 PROCEDURE chk_startup_action
1104 (p_insert IN boolean
1105 ,p_business_group_id IN number
1106 ,p_legislation_code IN varchar2
1107 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1108 --
1109 BEGIN
1110 --
1111 -- Call the supporting procedure to check startup mode
1112 --
1113 IF (p_insert) THEN
1114 --
1115 -- Call procedure to check startup_action for inserts.
1116 --
1117 hr_startup_data_api_support.chk_startup_action
1118 (p_generic_allowed => TRUE
1119 ,p_startup_allowed => TRUE
1120 ,p_user_allowed => TRUE
1121 ,p_business_group_id => p_business_group_id
1122 ,p_legislation_code => p_legislation_code
1123 ,p_legislation_subgroup => p_legislation_subgroup
1124 );
1125 ELSE
1126 --
1127 -- Call procedure to check startup_action for updates and deletes.
1128 --
1129 hr_startup_data_api_support.chk_upd_del_startup_action
1130 (p_generic_allowed => TRUE
1131 ,p_startup_allowed => TRUE
1132 ,p_user_allowed => TRUE
1133 ,p_business_group_id => p_business_group_id
1134 ,p_legislation_code => p_legislation_code
1135 ,p_legislation_subgroup => p_legislation_subgroup
1136 );
1137 END IF;
1138 --
1139 END chk_startup_action;
1140 --
1141 -- ----------------------------------------------------------------------------
1142 -- |---------------------------< insert_validate >----------------------------|
1143 -- ----------------------------------------------------------------------------
1144 Procedure insert_validate
1145 (p_rec in pay_rom_shd.g_rec_type
1146 ,p_effective_date in date
1147 ,p_datetrack_mode in varchar2
1148 ,p_validation_start_date in date
1149 ,p_validation_end_date in date
1150 ) is
1151 --
1152 l_proc varchar2(72) := g_package||'insert_validate';
1153 --
1154 Begin
1155 hr_utility.set_location('Entering:'||l_proc, 5);
1156 --
1157 -- Call all supporting business operations
1158 --
1159 chk_startup_action(True
1160 ,p_rec.business_group_id
1161 ,p_rec.legislation_code);
1162 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1163 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1164 END IF;
1165 --
1166 hr_utility.set_location(l_proc, 10);
1167 --
1168 chk_run_type_id(p_run_type_org_method_id => p_rec.run_type_org_method_id
1169 ,p_run_type_id => p_rec.run_type_id
1170 ,p_effective_date => p_effective_date
1171 ,p_business_group_id => p_rec.business_group_id
1172 ,p_legislation_code => p_rec.legislation_code);
1173 --
1174 hr_utility.set_location(l_proc, 15);
1175 --
1176 chk_org_payment_method_id
1177 (p_run_type_org_method_id => p_rec.run_type_org_method_id
1178 ,p_org_payment_method_id => p_rec.org_payment_method_id
1179 ,p_effective_date => p_effective_date
1180 ,p_business_group_id => p_rec.business_group_id);
1181 --
1182 hr_utility.set_location(l_proc, 20);
1183 --
1184 chk_priority(p_run_type_org_method_id => p_rec.run_type_org_method_id
1185 ,p_org_payment_method_id => p_rec.org_payment_method_id
1186 ,p_run_type_id => p_rec.run_type_id
1187 ,p_priority => p_rec.priority
1188 ,p_business_group_id => p_rec.business_group_id
1189 ,p_legislation_code => p_rec.legislation_code
1190 ,p_validation_start_date => p_validation_start_date
1191 ,p_validation_end_date => p_validation_end_date);
1192 --
1193 hr_utility.set_location(l_proc, 25);
1194 --
1195 chk_percent_amount(p_percent => p_rec.percentage
1196 ,p_amount => p_rec.amount
1197 ,p_org_payment_method_id => p_rec.org_payment_method_id);
1198 --
1202 ,p_legislation_code => p_rec.legislation_code);
1199 hr_utility.set_location(l_proc, 30);
1200 --
1201 chk_bg_leg_code(p_business_group_id => p_rec.business_group_id
1203 --
1204 hr_utility.set_location(' Leaving:'||l_proc, 35);
1205 End insert_validate;
1206 --
1207 -- ----------------------------------------------------------------------------
1208 -- |---------------------------< update_validate >----------------------------|
1209 -- ----------------------------------------------------------------------------
1210 Procedure update_validate
1211 (p_rec in pay_rom_shd.g_rec_type
1212 ,p_effective_date in date
1213 ,p_datetrack_mode in varchar2
1214 ,p_validation_start_date in date
1215 ,p_validation_end_date in date
1216 ) is
1217 --
1218 l_proc varchar2(72) := g_package||'update_validate';
1219 --
1220 Begin
1221 hr_utility.set_location('Entering:'||l_proc, 5);
1222 --
1223 -- Call all supporting business operations
1224 --
1225 chk_startup_action(False
1226 ,p_rec.business_group_id
1227 ,p_rec.legislation_code);
1228 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1229 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1230 END IF;
1231 --
1232 hr_utility.set_location(l_proc, 10);
1233 --
1234 chk_run_type_id(p_run_type_org_method_id => p_rec.run_type_org_method_id
1235 ,p_run_type_id => p_rec.run_type_id
1236 ,p_effective_date => p_effective_date
1237 ,p_business_group_id => p_rec.business_group_id
1238 ,p_legislation_code => p_rec.legislation_code);
1239 --
1240 hr_utility.set_location(l_proc, 15);
1241 --
1242 chk_org_payment_method_id
1243 (p_run_type_org_method_id => p_rec.run_type_org_method_id
1244 ,p_org_payment_method_id => p_rec.org_payment_method_id
1245 ,p_effective_date => p_effective_date
1246 ,p_business_group_id => p_rec.business_group_id);
1247 --
1248 hr_utility.set_location(l_proc, 20);
1249 --
1250 chk_priority(p_run_type_org_method_id => p_rec.run_type_org_method_id
1251 ,p_org_payment_method_id => p_rec.org_payment_method_id
1252 ,p_run_type_id => p_rec.run_type_id
1253 ,p_priority => p_rec.priority
1254 ,p_business_group_id => p_rec.business_group_id
1255 ,p_legislation_code => p_rec.legislation_code
1256 ,p_validation_start_date => p_validation_start_date
1257 ,p_validation_end_date => p_validation_end_date);
1258 --
1259 hr_utility.set_location(l_proc, 25);
1260 --
1261 chk_percent_amount(p_percent => p_rec.percentage
1262 ,p_amount => p_rec.amount
1263 ,p_org_payment_method_id => p_rec.org_payment_method_id);
1264 --
1265 hr_utility.set_location(l_proc, 30);
1266 --
1267 chk_bg_leg_code(p_business_group_id => p_rec.business_group_id
1268 ,p_legislation_code => p_rec.legislation_code);
1269 --
1270 hr_utility.set_location(l_proc, 35);
1271 --
1272 -- Call the datetrack update integrity operation
1273 --
1274 dt_update_validate
1275 (p_org_payment_method_id => p_rec.org_payment_method_id
1276 ,p_run_type_id => p_rec.run_type_id
1277 ,p_datetrack_mode => p_datetrack_mode
1278 ,p_validation_start_date => p_validation_start_date
1279 ,p_validation_end_date => p_validation_end_date
1280 );
1281 --
1282 hr_utility.set_location(l_proc, 40);
1283 --
1284 chk_non_updateable_args
1285 (p_effective_date => p_effective_date
1286 ,p_rec => p_rec
1287 );
1288 --
1289 hr_utility.set_location(' Leaving:'||l_proc, 45);
1290 End update_validate;
1291 --
1292 -- ----------------------------------------------------------------------------
1293 -- |---------------------------< delete_validate >----------------------------|
1294 -- ----------------------------------------------------------------------------
1295 Procedure delete_validate
1296 (p_rec in pay_rom_shd.g_rec_type
1297 ,p_effective_date in date
1298 ,p_datetrack_mode in varchar2
1299 ,p_validation_start_date in date
1300 ,p_validation_end_date in date
1301 ) is
1302 --
1303 l_proc varchar2(72) := g_package||'delete_validate';
1304 --
1305 Begin
1306 hr_utility.set_location('Entering:'||l_proc, 5);
1307 --
1308 -- Call all supporting business operations
1309 --
1310 chk_startup_action(False
1311 ,pay_rom_shd.g_old_rec.business_group_id
1312 ,pay_rom_shd.g_old_rec.legislation_code);
1313 --
1314 dt_delete_validate
1315 (p_datetrack_mode => p_datetrack_mode
1316 ,p_validation_start_date => p_validation_start_date
1317 ,p_validation_end_date => p_validation_end_date
1318 ,p_run_type_org_method_id => p_rec.run_type_org_method_id
1319 );
1320 --
1321 hr_utility.set_location(' Leaving:'||l_proc, 10);
1324 end pay_rom_bus;
1322 End delete_validate;
1323 --
[Home] [Help]