1 Package Body pqp_exr_bus as
2 /* $Header: pqexrrhi.pkb 120.4 2006/10/20 18:38:32 sshetty noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_exr_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_exception_report_id number default null ;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_exception_report_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 , pqp_exception_reports exr
30 where exr.exception_report_id = p_exception_report_id
31 and pbg.business_group_id = exr.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 => 'exception_report_id'
47 ,p_argument_value => p_exception_report_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 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 =>'EXCEPTION_REPORT_ID' );
64
65 fnd_message.raise_error;
66 END IF;
67 --
68 end if;
69 close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 hr_api.set_security_group_id
74 (p_security_group_id => l_security_group_id
75 );
76 --
77 hr_utility.set_location(' Leaving:'|| l_proc, 20);
78 --
79 end set_security_group_id;
80 --
81 -- ---------------------------------------------------------------------------
82 -- |---------------------< return_legislation_code >-------------------------|
83 -- ---------------------------------------------------------------------------
84 --
85 Function return_legislation_code
86 (p_business_group_id in number
87 )
88 Return Varchar2 Is
89 --
90 -- Declare cursor
91 --
92 cursor csr_leg_code is
93 select pbg.legislation_code
94 from per_business_groups pbg
95 where pbg.business_group_id = p_business_group_id;
96 --
97 -- Declare local variables
98 --
99 l_legislation_code varchar2(150);
100 l_proc varchar2(72) := g_package||'return_legislation_code';
101 --
102 Begin
103 --
104 hr_utility.set_location('Entering:'|| l_proc, 10);
105 --
106 -- Ensure that all the mandatory parameter are not null
107 --
108 --
109 -- The legislation code has already been found with a previous
110 -- call to this function. Just return the value in the global
111 -- variable.
112 --
113 if pqp_exr_bus.g_legislation_code IS NOT NULL THEN
114 l_legislation_code := pqp_exr_bus.g_legislation_code;
115 end if;
116 hr_utility.set_location(l_proc, 20);
117 -- The ID is different to the last call to this function
118 -- or this is the first call to this function.
119 --
120 open csr_leg_code;
121 fetch csr_leg_code into l_legislation_code;
122 --
123 if csr_leg_code%notfound then
124 --
125 -- The primary key is invalid therefore we must error
126 --
127 close csr_leg_code;
128 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
129 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
130 fnd_message.raise_error;
131 END IF;
132 end if;
133 hr_utility.set_location(l_proc,30);
134 --
135 -- Set the global variables so the values are
136 -- available for the next call to this function.
137 --
138 close csr_leg_code;
139 -- pqp_exr_bus.g_exception_report_id := p_exception_report_id;
140 pqp_exr_bus.g_legislation_code := l_legislation_code;
141 -- end if;
142 hr_utility.set_location(' Leaving:'|| l_proc, 40);
143 return l_legislation_code;
144 end return_legislation_code;
145 --
146 -- ---------------------------------------------------------------------------
147 -- |---------------------< return_legislation_code >-------------------------|
148 -- ---------------------------------------------------------------------------
149 --
150 Function return_exr_legislation_code
151 (p_exception_report_id in number
152 )
153 Return Varchar2 Is
154 --
155 -- Declare cursor
156 --
157 cursor csr_leg_code is
158 select pbg.legislation_code
159 ,exr.legislation_code
160 from per_business_groups pbg
161 ,pqp_exception_reports exr
162 where pbg.business_group_id = exr.business_group_id
163 and exr.exception_report_id= p_exception_report_id;
164 --
165 -- Declare local variables
166 --
167 l_legislation_code varchar2(150);
168 l_exr_legislation_code varchar2(150);
169 l_proc varchar2(72) := g_package||'return_exr_legislation_code';
170 --
171 Begin
172 --
173 hr_utility.set_location('Entering:'|| l_proc, 10);
174 --
175 -- Ensure that all the mandatory parameter are not null
176 -- The legislation code has already been found with a previous
177 -- call to this function. Just return the value in the global
178 -- variable.
179 --
180 if pqp_exr_bus.g_legislation_code is not null and
181 p_exception_report_id = g_exception_report_id then
182 l_legislation_code := pqp_exr_bus.g_legislation_code;
183 hr_utility.set_location(l_proc, 19);
184 else
185 hr_utility.set_location(l_proc, 20);
186 -- The ID is different to the last call to this function
187 -- or this is the first call to this function.
188 --
189 open csr_leg_code;
190 fetch csr_leg_code into l_legislation_code,l_exr_legislation_code;
191 --
192 if csr_leg_code%notfound then
193 --
194 -- The primary key is invalid therefore we must error
195 --
196 close csr_leg_code;
197 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
198 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
199 fnd_message.raise_error;
200 END IF;
201 end if;
202 hr_utility.set_location(l_proc,30);
203 --
204 -- Set the global variables so the values are
205 -- available for the next call to this function.
206 --
207 close csr_leg_code;
208 If l_legislation_code is not null then
209 pqp_exr_bus.g_exception_report_id := p_exception_report_id;
210 pqp_exr_bus.g_legislation_code := l_legislation_code;
211 elsif l_exr_legislation_code is not null then
212 pqp_exr_bus.g_exception_report_id := p_exception_report_id;
213 pqp_exr_bus.g_legislation_code := l_exr_legislation_code;
214 end if;
215 end if;
216 hr_utility.set_location(' Leaving:'|| l_proc, 40);
217 return l_legislation_code;
218 end return_exr_legislation_code;
219 --
220
221 -- ----------------------------------------------------------------------------
222 -- |-----------------------< chk_non_updateable_args >------------------------|
223 -- ----------------------------------------------------------------------------
224 -- {Start Of Comments}
225 --
226 -- Description:
227 -- This procedure is used to ensure that non updateable attributes have
228 -- not been updated. If an attribute has been updated an error is generated.
229 --
230 -- Pre Conditions:
231 -- g_old_rec has been populated with details of the values currently in
232 -- the database.
233 --
234 -- In Arguments:
235 -- p_rec has been populated with the updated values the user would like the
236 -- record set to.
237 --
238 -- Post Success:
239 -- Processing continues if all the non updateable attributes have not
240 -- changed.
241 --
242 -- Post Failure:
243 -- An application error is raised if any of the non updatable attributes
244 -- have been altered.
245 --
246 -- {End Of Comments}
247 -- ----------------------------------------------------------------------------
248 Procedure chk_non_updateable_args
249 (p_rec in pqp_exr_shd.g_rec_type
250 ) IS
251 --
252 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
253 l_error EXCEPTION;
254 l_argument varchar2(30);
255 --
256 Begin
257 --
258 -- Only proceed with the validation if a row exists for the current
259 -- record in the HR Schema.
260 --
261 IF NOT pqp_exr_shd.api_updating
262 (p_exception_report_id => p_rec.exception_report_id
263 ,p_object_version_number => p_rec.object_version_number
264 ) THEN
265 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
266 fnd_message.set_token('PROCEDURE ', l_proc);
267 fnd_message.set_token('STEP ', '5');
268 fnd_message.raise_error;
269 END IF;
270
271 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
272 nvl (pqp_exr_shd.g_old_rec.business_group_id, hr_api.g_number) THEN
273 l_argument := 'business_group_id';
274 RAISE l_error;
275 END IF;
276
277 IF nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
278 nvl (pqp_exr_shd.g_old_rec.legislation_code, hr_api.g_varchar2) THEN
279 l_argument := 'legislation_code';
280 RAISE l_error;
281 END IF;
282
283 EXCEPTION
284 WHEN l_error THEN
285 hr_api.argument_changed_error
286 (p_api_name => l_proc
287 ,p_argument => l_argument);
288 WHEN OTHERS THEN
289 RAISE;
290 End chk_non_updateable_args;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |----------------------< chk_startup_action >------------------------------|
294 -- ----------------------------------------------------------------------------
295 --
296 -- Description:
297 -- This procedure will check that the current action is allowed according
298 -- to the current startup mode.
299 --
300 -- ----------------------------------------------------------------------------
301 PROCEDURE chk_startup_action
302 (p_insert IN boolean
303 ,p_business_group_id IN number
304 ,p_legislation_code IN varchar2
305 ,p_legislation_subgroup IN varchar2 ) IS
306 --
307 BEGIN
308 --
309 -- Call the supporting procedure to check startup mode
310 IF (p_insert) THEN
311 hr_startup_data_api_support.chk_startup_action
312 (p_generic_allowed => TRUE
313 ,p_startup_allowed => TRUE
314 ,p_user_allowed => TRUE
315 ,p_business_group_id => p_business_group_id
316 ,p_legislation_code => p_legislation_code
317 ,p_legislation_subgroup => p_legislation_subgroup
318 );
319 ELSE
320 hr_startup_data_api_support.chk_upd_del_startup_action
321 (p_generic_allowed => TRUE
322 ,p_startup_allowed => TRUE
323 ,p_user_allowed => TRUE
324 ,p_business_group_id => p_business_group_id
325 ,p_legislation_code => p_legislation_code
326 ,p_legislation_subgroup => p_legislation_subgroup
327 );
328 END IF;
329 --
330 END chk_startup_action;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |-------------------------< chk_currency_code >----------------------------|
334 -- ----------------------------------------------------------------------------
335 --
336 -- {Start Of Comments}
337 --
338 -- Description:
339 -- This procedure is used to ensure that the currency code matches with
340 -- the currency code of the Business Group.
341 --
342 -- Pre Conditions:
343 -- None
344 --
345 -- In Arguments:
346 -- exception_report_id
347 -- business_group_id
348 -- currency_code
349 -- object_version_number
350 --
351 -- Post Success:
352 -- Processing continues
353 --
354 -- Post Failure:
355 -- An application error is raised and processing is terminated
356 --
357 -- Access Status :
358 -- Internal Table Handler Use only.
359 --
360 -- {End Of Comments}
361 --
362 -- ----------------------------------------------------------------------------
363 PROCEDURE chk_currency_code
364 (p_exception_report_id IN number
365 ,p_business_group_id IN number
366 ,p_currency_code IN varchar2
367 ,p_object_version_number IN number
368 ) IS
369 --
370 l_proc varchar2(72) := g_package||'chk_currency_code';
371 l_api_updating boolean;
372 l_default_currency_code varchar2(15);
373 --
374 BEGIN
375 --
376 hr_utility.set_location('Entering:'|| l_proc, 10);
377 --
378 -- Only proceed with validation if :
379 -- a) The g_old_rec is current and
380 -- b) The currency code value has changed
381 --
382
383 l_api_updating := pqp_exr_shd.api_updating
384 (p_exception_report_id => p_exception_report_id
385 ,p_object_version_number => p_object_version_number);
386
387 --
388
389 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.currency_code, hr_api.g_varchar2) <>
390 nvl(p_currency_code, hr_api.g_varchar2))) or
391 (NOT l_api_updating)) THEN
392
393 --
394 -- Get currency code information
395 --
396
397 IF p_currency_code is not null THEN
398
399 hr_utility.set_location(l_proc, 20);
400
401 l_default_currency_code := hr_general.default_currency_code
402 (p_business_group_id => p_business_group_id);
403
404
405 IF p_currency_code <> l_default_currency_code THEN
406
407 -- Raise error as the input currency code is not equal
408 -- to the default currency for the BG
409
410 fnd_message.set_name('PQP','PQP_230520_CUR_CODE_MISMATCH' );
411 fnd_message.raise_error;
412 --hr_utility.set_message(8303,'PQP_230520_CUR_CODE_MISMATCH' );
413 --hr_utility.raise_error;
414
415 END IF; -- end if of currency code check ...
416 --
417
418 END IF; -- End if of currency code not null check ...
419 --
420
421 END IF; -- end if of api updating check ...
422 --
423
424 hr_utility.set_location(' Leaving:' || l_proc, 30);
425 --
426 END chk_currency_code;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |-------------------------< chk_balance_type_id >--------------------------|
430 -- ----------------------------------------------------------------------------
431 --
432 -- {Start Of Comments}
433 --
434 -- Description:
435 -- This procedure is used to ensure that the balance_type_id should be for
436 -- that BG and Leg code is null or Leg code and BG is null
437 --
438 -- Pre Conditions:
439 -- None
440 --
441 -- In Arguments:
442 -- exception_report_id
443 -- business_group_id
444 -- legislation_id
445 -- balance_type_id
446 -- object_version_number
447 --
448 -- Post Success:
449 -- Processing continues
450 --
451 -- Post Failure:
452 -- An application error is raised and processing is terminated
453 --
454 -- Access Status :
455 -- Internal Table Handler Use only.
456 --
457 -- {End Of Comments}
458 --
459 -- ----------------------------------------------------------------------------
460 PROCEDURE chk_balance_type_id
461 (p_exception_report_id IN number
462 ,p_business_group_id IN number
463 ,p_legislation_code IN varchar2
464 ,p_balance_type_id IN number
465 ,p_object_version_number IN number
466 ) IS
467 --
468 l_proc varchar2(72) := g_package||'chk_balance_type_id';
469 l_api_updating boolean;
470 l_exists varchar2(1);
471 l_legislation_code per_business_groups.legislation_code%TYPE;
472
473 cursor csr_bal_type
474 is
475 select 'X'
476 from pay_balance_types
477 where balance_type_id = p_balance_type_id
478 and ( (legislation_code is not null
479 and legislation_code = l_legislation_code)
480 or (business_group_id is not null
481 and business_group_id = p_business_group_id));
482 --
483 BEGIN
484 --
485 hr_utility.set_location('Entering:'|| l_proc, 10);
486 --
487 -- Only proceed with validation if :
488 -- a) The g_old_rec is current and
489 -- b) The balance type id value has changed
490 --
491
492 l_api_updating := pqp_exr_shd.api_updating
493 (p_exception_report_id => p_exception_report_id
494 ,p_object_version_number => p_object_version_number);
495
496 --
497
498 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.balance_type_id, hr_api.g_number) <>
499 nvl(p_balance_type_id, hr_api.g_number))) or
500 (NOT l_api_updating)) THEN
501
502 --
503 -- Check whether the balance type id exists within the BG or Leg Code
504 --
505 IF p_legislation_code is NULL THEN
506 l_legislation_code:=pqp_exr_bus.return_legislation_code(p_business_group_id);
507 ELSE
508 l_legislation_code:=p_legislation_code;
509 END IF;
510
511 IF p_balance_type_id is not null THEN
512
513 hr_utility.set_location(l_proc, 20);
514
515 open csr_bal_type;
516 fetch csr_bal_type into l_exists;
517
518 IF csr_bal_type%NOTFOUND THEN
519
520 -- Raise an error
521
522 fnd_message.set_name('PQP','PQP_230549_BAL_TYPE_NOT_FOUND' );
523 fnd_message.raise_error;
524 -- hr_utility.set_message(8303,'PQP_230549_BAL_TYPE_NOT_FOUND' );
525 --hr_utility.raise_error;
526
527 END IF; -- end if of balance type id check ...
528 close csr_bal_type;
529 --
530
531 END IF; -- End if of balance type id not null check ...
532 --
533
534 END IF; -- end if of api updating check ...
535 --
536
537 hr_utility.set_location(' Leaving:' || l_proc, 30);
538 --
539 END chk_balance_type_id;
540 --
541 -- ----------------------------------------------------------------------------
542 -- |-------------------------< chk_bal_dim_id >-------------------------------|
543 -- ----------------------------------------------------------------------------
544 --
545 -- {Start Of Comments}
546 --
547 -- Description:
548 -- This procedure is used to ensure that the balance dimension id should
549 -- be of the balance type id
550 --
551 -- Pre Conditions:
552 -- None
553 --
554 -- In Arguments:
555 -- exception_report_id
556 -- balance_type_id
557 -- balance_dimension_id
558 -- object_version_number
559 --
560 -- Post Success:
561 -- Processing continues
562 --
563 -- Post Failure:
564 -- An application error is raised and processing is terminated
565 --
566 -- Access Status :
567 -- Internal Table Handler Use only.
568 --
569 -- {End Of Comments}
570 --
571 -- ----------------------------------------------------------------------------
572 PROCEDURE chk_bal_dim_id
573 (p_exception_report_id IN number
574 ,p_balance_type_id IN number
575 ,p_balance_dimension_id IN number
576 ,p_object_version_number IN number
577 ) IS
578 --
579 l_proc varchar2(72) := g_package||'chk_bal_dim_id';
580 l_api_updating boolean;
581 l_exists varchar2(1);
582
583 cursor csr_bal_dim
584 is
585 select 'X'
586 from pay_defined_balances
587 where balance_type_id = p_balance_type_id
588 and balance_dimension_id = p_balance_dimension_id;
589 --
590 BEGIN
591 --
592 hr_utility.set_location('Entering:'|| l_proc, 10);
593 --
594 -- Only proceed with validation if :
595 -- a) The g_old_rec is current and
596 -- b) The balance dimension id value has changed
597 --
598
599 l_api_updating := pqp_exr_shd.api_updating
600 (p_exception_report_id => p_exception_report_id
601 ,p_object_version_number => p_object_version_number);
602
603 --
604
605 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.balance_dimension_id, hr_api.g_number) <>
606 nvl(p_balance_dimension_id, hr_api.g_number))) or
607 (NOT l_api_updating)) THEN
608
609 --
610 -- Check whether this balance dimension id exists for this balance type id
611 --
612
613 IF p_balance_dimension_id is not null THEN
614
615 hr_utility.set_location(l_proc, 20);
616
617 open csr_bal_dim;
618 fetch csr_bal_dim into l_exists;
619
620 IF csr_bal_dim%NOTFOUND THEN
621
622 -- Raise an error
623
624 fnd_message.set_name('PQP','PQP_230550_BAL_DIM_NOT_FOUND' );
625 fnd_message.raise_error;
626 --hr_utility.set_message(8303,'PQP_230550_BAL_DIM_NOT_FOUND' );
627 --hr_utility.raise_error;
628
629 END IF; -- end if of balance dimension check...
630 --
631
632 END IF; -- End if of balance dimension id check ...
633 --
634
635 END IF; -- end if of api updating check ...
636 --
637
638 hr_utility.set_location(' Leaving:' || l_proc, 30);
639 --
640 END chk_bal_dim_id;
641 --
642 -- ----------------------------------------------------------------------------
643 -- |-------------------------< chk_variance_type >----------------------------|
644 -- ----------------------------------------------------------------------------
645 --
646 -- {Start Of Comments}
647 --
648 -- Description:
649 -- This procedure is used to ensure that the variance_type can only be
650 -- 'A' or 'P'
651 --
652 -- Pre Conditions:
653 -- None
654 --
655 -- In Arguments:
656 -- exception_report_id
657 -- variance_type
658 -- object_version_number
659 --
660 -- Post Success:
661 -- Processing continues
662 --
663 -- Post Failure:
664 -- An application error is raised and processing is terminated
665 --
666 -- Access Status :
667 -- Internal Table Handler Use only.
668 --
669 -- {End Of Comments}
670 --
671 -- ----------------------------------------------------------------------------
672 PROCEDURE chk_variance_type
673 (p_exception_report_id IN number
674 ,p_variance_type IN varchar2
675 ,p_comparison_type IN varchar2
676 ,p_object_version_number IN number
677
678 ) IS
679 --
680 l_proc varchar2(72) := g_package||'chk_variance_type';
681 l_api_updating boolean;
682 --
683 BEGIN
684 --
685 hr_utility.set_location('Entering:'|| l_proc, 10);
686 --
687 -- Only proceed with validation if :
688 -- a) The g_old_rec is current and
689 -- b) The variance type value has changed
690 --
691
692 l_api_updating := pqp_exr_shd.api_updating
693 (p_exception_report_id => p_exception_report_id
694 ,p_object_version_number => p_object_version_number);
695
696 --
697
698 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.variance_type, hr_api.g_varchar2) <>
699 nvl(p_variance_type, hr_api.g_varchar2))) or
700 (NOT l_api_updating)) THEN
701
702 IF p_variance_type is not null THEN
703
704 --
705 -- Check whether the variance type value is 'A' or 'P'
706 --
707
708 IF p_variance_type NOT IN ('A', 'P') THEN
709
710 -- Raise error as the variance type is not 'A' or 'P'
711
712 fnd_message.set_name('PQP','PQP_230551_VARIANCE_MISMATCH' );
713 fnd_message.raise_error;
714 -- hr_utility.set_message(8303,'PQP_230551_VARIANCE_MISMATCH' );
715 -- hr_utility.raise_error;
716
717 END IF; -- end if of variance type check ...
718 --
719
720 END IF; -- End if of variance type not null check ...
721 --
722
723 END IF; -- end if of api updating check ...
724 --
725 --added by pjavaji
726 IF p_comparison_type ='PC' AND p_variance_type='P' THEN
727 fnd_message.set_name('PQP','PQP_230583_INVALID_VAR_TYPE' );
728 fnd_message.raise_error;
729 -- hr_utility.set_message(8303,'PQP_230583_INVALID_VAR_TYPE' );
730 -- hr_utility.raise_error;
731 END IF;
732 --
733
734 hr_utility.set_location(' Leaving:' || l_proc, 20);
735 --
736 END chk_variance_type;
737 --
738 -- ----------------------------------------------------------------------------
739 -- |-------------------------< chk_output_format >----------------------------|
740 -- ----------------------------------------------------------------------------
741 --
742 -- {Start Of Comments}
743 --
744 -- Description:
745 -- This procedure is used to ensure that the output_format should be from the
746 -- lookup type 'PQP_OUTPUT_FORAMT_TYPES'
747 --
748 -- Pre Conditions:
749 -- None
750 --
751 -- In Arguments:
752 -- exception_report_id
753 -- output_format_type
754 -- object_version_number
755 --
756 -- Post Success:
757 -- Processing continues
758 --
759 -- Post Failure:
760 -- An application error is raised and processing is terminated
761 --
762 -- Access Status :
763 -- Internal Table Handler Use only.
764 --
765 -- {End Of Comments}
766 --
767 -- ----------------------------------------------------------------------------
768 PROCEDURE chk_output_format
769 (p_exception_report_id IN number
770 ,p_business_group_id IN number
771 ,p_output_format_type IN varchar2
772 ,p_object_version_number IN number
773 ) IS
774 --
775 l_proc varchar2(72) := g_package||'chk_output_format';
776 l_api_updating boolean;
777 l_effective_date date;
778
779 cursor csr_eff_date
780 is
781 select effective_date
782 from fnd_sessions
783 where session_id = USERENV('sessionid');
784 --
785 BEGIN
786 -- Code is changed to validate the existence of
787 -- TXT format.
788 hr_utility.set_location('Entering:'|| l_proc, 10);
789 --
790 -- Only proceed with validation if :
791 -- a) The g_old_rec is current and
792 -- b) The output_format type value has changed
793 --
794
795 l_api_updating := pqp_exr_shd.api_updating
796 (p_exception_report_id => p_exception_report_id
797 ,p_object_version_number => p_object_version_number);
798
799 --
800 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.output_format_type, hr_api.g_varchar2) <>
801 nvl(p_output_format_type, hr_api.g_varchar2))) or
802 (NOT l_api_updating)) THEN
803
804 IF p_output_format_type is not null THEN
805
806 hr_utility.set_location(l_proc, 20);
807
808 /* open csr_eff_date;
809 fetch csr_eff_date into l_effective_date;
810 close csr_eff_date;*/
811 --
812 /*IF l_effective_date is NULL THEN
813 l_effective_date:=TRUNC(sysdate);
814 END IF;*/
815 --
816 hr_utility.set_location(l_proc, 30);
817
818 IF p_business_group_id is not null THEN
819 IF p_output_format_type='TXT' THEN
820 fnd_message.set_name('PQP','PQP_230444_BACKCOMP_SUPPORT');
821 fnd_message.raise_error;
822 END IF;
823 /* IF hr_api.not_exists_in_hr_lookups
824 (p_lookup_type => 'PQP_OUTPUT_FORMAT_TYPES'
825 ,p_lookup_code => p_output_format_type
826 ,p_effective_date => l_effective_date ) THEN
827
828 -- Raise error as the value does not exist as a lookup
829
830 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
831 fnd_message.set_token('COLUMN','output_format_TYPE' );
832 fnd_message.set_token('LOOKUP_TYPE', 'PQP_output_format_TYPES' );
833 fnd_message.raise_error;
834 END IF; */ -- End if of lookup check ...
835 hr_utility.set_location(l_proc, 40);
836 /* ELSE
837 IF hr_api.not_exists_in_hrstanlookups
838 (p_lookup_type => 'PQP_OUTPUT_FORMAT_TYPES'
839 ,p_lookup_code => p_output_format_type
840 ,p_effective_date => l_effective_date ) THEN
841
842 -- Raise error as the value does not exist as a lookup
843
844 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
845 fnd_message.set_token('COLUMN','output_format_TYPE' );
846 fnd_message.set_token('LOOKUP_TYPE', 'PQP_OUTPUT_FORMAT_TYPES' );
847 fnd_message.raise_error;
848
849 END IF; */ -- End if of lookup check ...
850 hr_utility.set_location(l_proc, 50);
851 END IF; -- End if of business group check...
852 --
853 hr_utility.set_location(l_proc, 60);
854 --
855 END IF; -- p_output_format_type is not null check...
856 --
857 END IF; -- end if of api updating check ...
858 --
859 hr_utility.set_location(' Leaving:' || l_proc, 70);
860 --
861 END chk_output_format;
862 -- ----------------------------------------------------------------------------
863 -- |-------------------------< chk_variance_value >---------------------------|
864 -- ----------------------------------------------------------------------------
865 --
866 -- {Start Of Comments}
867
868 --
869 -- Description:
870 -- This procedure is used to ensure that the variance_value cannot
871 -- exceed 100 if variance type is 'P'
872 --
873 -- Pre Conditions:
874 -- None
875 --
876 -- In Arguments:
877 -- exception_report_id
878 -- variance type
879 -- variance_value
880 -- object_version_number
881 --
882 -- Post Success:
883 -- Processing continues
884 --
885 -- Post Failure:
886 -- An application error is raised and processing is terminated
887 --
888 -- Access Status :
889 -- Internal Table Handler Use only.
890 --
891 -- {End Of Comments}
892 --
893 -- ----------------------------------------------------------------------------
894 PROCEDURE chk_variance_value
895 (p_exception_report_id IN number
896 ,p_variance_type IN varchar2
897 ,p_variance_value IN varchar2
898 ,p_object_version_number IN number
899 ) IS
900 --
901 --
902 l_proc varchar2(72) := g_package||'chk_variance_value';
903 l_api_updating boolean;
904 --
905 BEGIN
906 --
907 hr_utility.set_location('Entering:'|| l_proc, 10);
908 --
909 -- Only proceed with validation if :
910 -- a) The variance type is 'P'
911 -- b) The g_old_rec is current and
912 -- c) The variance value has changed
913 --
914
915 IF p_variance_type = 'P' and
916 p_variance_value is not null THEN
917 l_api_updating := pqp_exr_shd.api_updating
918 (p_exception_report_id => p_exception_report_id
919 ,p_object_version_number => p_object_version_number);
920
921 --
922
923 /* IF ((l_api_updating
924 and (nvl(pqp_exr_shd.g_old_rec.variance_value, hr_api.g_number) <> nvl(p_variance_value, hr_api.g_number)))
925 OR (NOT l_api_updating))
926 THEN */ -- commented by pjavaji
927
928 --
929 -- Check whether the variance value is greater than 100
930 --
931 IF p_variance_value > 100 THEN
932
933 --Raise error as the variance value exceeded 100
934
935 fnd_message.set_name('PQP','PQP_230552_VARIANCE_VALUE_MORE' );
936 fnd_message.raise_error;
937 --hr_utility.set_message(8303,'PQP_230552_VARIANCE_VALUE_MORE' );
938 --hr_utility.raise_error;
939
940 END IF; -- end if of variance value check ...
941 --
942 --END IF; -- end if of api updating check ...
943 --
944 END IF; -- end if of variance_type check ...
945 --
946
947 hr_utility.set_location(' Leaving:' || l_proc, 20);
948 --
949 END chk_variance_value;
950 --
951 -- ----------------------------------------------------------------------------
952 -- |-------------------------< chk_comparison_type >--------------------------|
953 -- ----------------------------------------------------------------------------
954 --
955 -- {Start Of Comments}
956 --
957 -- Description:
958 -- This procedure is used to ensure that the comparison type should be from
959 -- the look up type 'PQP_COMPARISON_TYPE'.
960 --
961 -- Pre Conditions:
962 -- None
963 --
964 -- In Arguments:
965 -- exception_report_id
966 -- comparison_type
967 -- object_version_number
968 -- balance_dimension_id
969 --
970 -- Post Success:
971 -- Processing continues
972 --
973 -- Post Failure:
974 -- An application error is raised and processing is terminated
975 --
976 -- Access Status :
977 -- Internal Table Handler Use only.
978 --
979 -- {End Of Comments}
980 --
981 -- ----------------------------------------------------------------------------
982 PROCEDURE chk_comparison_type
983 (p_exception_report_id IN number
984 ,p_business_group_id IN number
985 ,p_comparison_type IN varchar2
986 ,p_object_version_number IN number
987 ,p_balance_dimension_id IN number
988 ) IS
989 --
990 l_proc varchar2(72) := g_package||'chk_comparison_type';
991 l_api_updating boolean;
992 l_effective_date date;
993
994 cursor csr_eff_date
995 is
996 select effective_date
997 from fnd_sessions
998 where session_id = USERENV('sessionid');
999 --
1000 CURSOR c_dim_suffix IS
1001 SELECT decode(pers.exception_report_period
1002 ,'YEAR' ,'Y'
1003 ,'QUARTER','Q'
1004 ,'PERIOD' ,'P'
1005 ,'MONTH' ,'M','INCEPTION','I','X') db_suffix
1006 FROM pay_balance_dimensions pbd
1007 ,pqp_exception_report_suffix pers
1008 WHERE pbd.balance_dimension_id = p_balance_dimension_id
1009 and pers.legislation_code = pbd.legislation_code
1010 and pers.database_item_suffix = pbd.database_item_suffix;
1011 --
1012 BEGIN
1013 --
1014 hr_utility.set_location('Entering:'|| l_proc, 10);
1015 --
1016 -- Only proceed with validation if :
1017 -- a) The g_old_rec is current and
1018 -- b) The comparison type value has changed
1019 --
1020
1021 l_api_updating := pqp_exr_shd.api_updating
1022 (p_exception_report_id => p_exception_report_id
1023 ,p_object_version_number => p_object_version_number);
1024
1025 --
1026 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.comparison_type, hr_api.g_varchar2) <>
1027 nvl(p_comparison_type, hr_api.g_varchar2))) or
1028 (NOT l_api_updating)) THEN
1029
1030 IF p_comparison_type is not null THEN
1031
1032 hr_utility.set_location(l_proc, 20);
1033
1034 open csr_eff_date;
1035 fetch csr_eff_date into l_effective_date;
1036 close csr_eff_date;
1037 --
1038 IF l_effective_date is NULL THEN
1039 l_effective_date:=TRUNC(sysdate);
1040 END IF;
1041 --
1042 hr_utility.set_location(l_proc, 30);
1043
1044 IF p_business_group_id is not null THEN
1045 IF hr_api.not_exists_in_hr_lookups
1046 (p_lookup_type => 'PQP_COMPARISON_TYPE'
1047 ,p_lookup_code => p_comparison_type
1048 ,p_effective_date => l_effective_date ) THEN
1049
1050 -- Raise error as the value does not exist as a lookup
1051
1052 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
1053 fnd_message.set_token('COLUMN','COMPARISON_TYPE' );
1054 fnd_message.set_token('LOOKUP_TYPE', 'PQP_COMPARISON_TYPE' );
1055 fnd_message.raise_error;
1056 END IF; -- End if of lookup check ...
1057 hr_utility.set_location(l_proc, 40);
1058 ELSE
1059 IF hr_api.not_exists_in_hrstanlookups
1060 (p_lookup_type => 'PQP_COMPARISON_TYPE'
1061 ,p_lookup_code => p_comparison_type
1062 ,p_effective_date => l_effective_date ) THEN
1063
1064 -- Raise error as the value does not exist as a lookup
1065
1066 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
1067 fnd_message.set_token('COLUMN','COMPARISON_TYPE' );
1068 fnd_message.set_token('LOOKUP_TYPE', 'PQP_COMPARISON_TYPE' );
1069 fnd_message.raise_error;
1070
1071 END IF; -- End if of lookup check ...
1072 hr_utility.set_location(l_proc, 50);
1073 END IF; -- End if of business group check...
1074 --
1075 hr_utility.set_location(l_proc, 60);
1076 --
1077 END IF; -- End if of comparison type not null check...
1078 --
1079 END IF; -- end if of api updating check ...
1080 --
1081 -- added by skutteti to validate the combination of comparison type
1082 -- and balance dimension
1083 --
1084 FOR c_rec IN c_dim_suffix LOOP
1085 IF upper(c_rec.db_suffix) <> upper(substr(p_comparison_type,1,1)) THEN
1086 fnd_message.set_name('PQP','PQP_230567_DIM_COM_TYP_INVALID');
1087 fnd_message.raise_error;
1088 --hr_utility.set_message(8303,'PQP_230567_DIM_COM_TYP_INVALID');
1089 --hr_utility.raise_error;
1090 END IF;
1091 END LOOP;
1092 --
1093 hr_utility.set_location(' Leaving:' || l_proc, 70);
1094 --
1095 END chk_comparison_type;
1096 --
1097 --
1098 -- ----------------------------------------------------------------------------
1099 -- |-------------------------< chk_variance_operator >--------------------------|
1100 -- ----------------------------------------------------------------------------
1101 --
1102 -- {Start Of Comments}
1103 --
1104 -- Description:
1105 -- This procedure is used to ensure that the variance_operator type should be from
1106 -- the look up type 'PQP_variance_operator_TYPES'.
1107 --
1108 -- Pre Conditions:
1109 -- None
1110 --
1111 -- In Arguments:
1112 -- exception_report_id
1113 -- variance_operator
1114 -- object_version_number
1115 -- balance_dimension_id
1116 --
1117 -- Post Success:
1118 -- Processing continues
1119 --
1120 -- Post Failure:
1121 -- An application error is raised and processing is terminated
1122 --
1123 -- Access Status :
1124 -- Internal Table Handler Use only.
1125 --
1126 -- {End Of Comments}
1127 --
1128 -- ----------------------------------------------------------------------------
1129 PROCEDURE chk_variance_operator
1130 (p_exception_report_id IN number
1131 ,p_business_group_id IN number
1132 ,p_variance_operator IN varchar2
1133 ,p_object_version_number IN number
1134 ,p_balance_dimension_id IN number
1135 ) IS
1136 --
1137 l_proc varchar2(72) := g_package||'chk_variance_operator';
1138 l_api_updating boolean;
1139 l_effective_date date;
1140
1141 cursor csr_eff_date
1142 is
1143 select effective_date
1144 from fnd_sessions
1145 where session_id = USERENV('sessionid');
1146 --
1147 BEGIN
1148 --
1149 hr_utility.set_location('Entering:'|| l_proc, 10);
1150 --
1151 -- Only proceed with validation if :
1152 -- a) The g_old_rec is current and
1153 -- b) The variance_operator type value has changed
1154 --
1155
1156 l_api_updating := pqp_exr_shd.api_updating
1157 (p_exception_report_id => p_exception_report_id
1158 ,p_object_version_number => p_object_version_number);
1159
1160 --
1161 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.variance_operator, hr_api.g_varchar2) <>
1162 nvl(p_variance_operator, hr_api.g_varchar2))) or
1163 (NOT l_api_updating)) THEN
1164
1165 IF p_variance_operator is not null THEN
1166
1167 hr_utility.set_location(l_proc, 20);
1168
1169 open csr_eff_date;
1170 fetch csr_eff_date into l_effective_date;
1171 close csr_eff_date;
1172 --
1173 IF l_effective_date is NULL THEN
1174 l_effective_date:=TRUNC(sysdate);
1175 END IF;
1176 --
1177 hr_utility.set_location(l_proc, 30);
1178
1179 IF p_business_group_id is not null THEN
1180 IF hr_api.not_exists_in_hr_lookups
1181 (p_lookup_type => 'PQP_VARIANCE_OPERATOR_TYPES'
1182 ,p_lookup_code => p_variance_operator
1183 ,p_effective_date => l_effective_date ) THEN
1184
1185 -- Raise error as the value does not exist as a lookup
1186
1187 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
1188 fnd_message.set_token('COLUMN','variance_operator_TYPE' );
1189 fnd_message.set_token('LOOKUP_TYPE', 'PQP_VARIANCE_OPERATOR_TYPES' );
1190 fnd_message.raise_error;
1191 END IF; -- End if of lookup check ...
1192 hr_utility.set_location(l_proc, 40);
1193 ELSE
1194 IF hr_api.not_exists_in_hrstanlookups
1195 (p_lookup_type => 'PQP_VARIANCE_OPERATOR_TYPES'
1196 ,p_lookup_code => p_variance_operator
1197 ,p_effective_date => l_effective_date ) THEN
1198
1199 -- Raise error as the value does not exist as a lookup
1200
1201 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP' );
1202 fnd_message.set_token('COLUMN','variance_operator_TYPE' );
1203 fnd_message.set_token('LOOKUP_TYPE', 'PQP_VARIANCE_OPERATOR_TYPES' );
1204 fnd_message.raise_error;
1205
1206 END IF; -- End if of lookup check ...
1207 hr_utility.set_location(l_proc, 50);
1208 END IF; -- End if of business group check...
1209 --
1210 hr_utility.set_location(l_proc, 60);
1211 --
1212 END IF; -- End if of variance_operator not null check...
1213 --
1214 END IF; -- end if of api updating check ...
1215 --
1216 hr_utility.set_location(' Leaving:' || l_proc, 70);
1217 --
1218 END chk_variance_operator;
1219 -- ----------------------------------------------------------------------------
1220 -- |-------------------------< chk_comparison_value >-------------------------|
1221 -- ----------------------------------------------------------------------------
1222 --
1223 -- {Start Of Comments}
1224 --
1225 -- Description:
1226 -- This procedure is used to ensure that the comparison value has a value
1227 -- one if the second characterof comparion type is 'P'
1228 --
1229 -- Pre Conditions:
1230 -- None
1231 --
1232 -- In Arguments:
1233 -- exception_report_id
1234 -- comparison_type
1235 -- comparison_value
1236 -- object_version_number
1237 --
1238 -- Post Success:
1239 -- Processing continues
1240 --
1241 -- Post Failure:
1242 -- An application error is raised and processing is terminated
1243 --
1244 -- Access Status :
1245 -- Internal Table Handler Use only.
1246 --
1247 -- {End Of Comments}
1248 --
1249 -- ----------------------------------------------------------------------------
1250 PROCEDURE chk_comparison_value
1251 (p_exception_report_id IN number
1252 ,p_comparison_type IN varchar2
1253 ,p_comparison_value IN number
1254 ,p_object_version_number IN number
1255 ) IS
1256 --
1257 l_proc varchar2(72) := g_package||'chk_comparison_value';
1258 l_api_updating boolean;
1259 --
1260 BEGIN
1261 --
1262 hr_utility.set_location('Entering:'|| l_proc, 10);
1263 --
1264 -- Only proceed with validation if :
1265 -- a) The second character of comparison_type is 'P'
1266 -- b) The g_old_rec is current and
1267 -- c) The comparison value has changed
1268 --
1269
1270 IF substr(p_comparison_type,2,1) = 'P' THEN
1271
1272 l_api_updating := pqp_exr_shd.api_updating
1273 (p_exception_report_id => p_exception_report_id
1274 ,p_object_version_number => p_object_version_number);
1275
1276 --
1277 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.comparison_value, hr_api.g_number) <>
1278 nvl(p_comparison_value, hr_api.g_number))) or
1279 (NOT l_api_updating)) THEN
1280
1281
1282 IF nvl(p_comparison_value, hr_api.g_number) <> 1 THEN
1283
1284 -- Raise error as the input comparison value is not equal
1285 -- to one
1286
1287 fnd_message.set_name('PQP','PQP_230553_COMPARISON_VAL_QUAL' );
1288 fnd_message.raise_error;
1289 --hr_utility.set_message(8303,'PQP_230553_COMPARISON_VAL_QUAL' );
1290 --hr_utility.raise_error;
1291
1292 END IF; -- end if of comparison value check ...
1293 --
1294
1295 END IF; -- end if of api updating check ...
1296 --
1297
1298 END IF; -- End if of comparison type check ...
1299 --
1300 --Added by sshetty
1301 IF substr(p_comparison_type,2,1) = 'C' THEN
1302
1303 l_api_updating := pqp_exr_shd.api_updating
1304 (p_exception_report_id => p_exception_report_id
1305 ,p_object_version_number => p_object_version_number);
1306
1307 --
1308 IF ((l_api_updating and (nvl(pqp_exr_shd.g_old_rec.comparison_value, hr_api.g_number) <>
1309 nvl(p_comparison_value, hr_api.g_number))) or
1310 (NOT l_api_updating)) THEN
1311
1312
1313 IF nvl(p_comparison_value, hr_api.g_number) <> 0 THEN
1314
1315 -- Raise error as the input comparison value is not equal
1316 -- to one
1317
1318 fnd_message.set_name('PQP','PQP_230553_COMPARISON_VAL_QUAL' );
1319 fnd_message.raise_error;
1320 --hr_utility.set_message(8303,'PQP_230553_COMPARISON_VAL_QUAL' );
1321 --hr_utility.raise_error;
1322
1323 END IF; -- end if of comparison value check ...
1324 --
1325
1326 END IF; -- end if of api updating check ...
1327 --
1328
1329 END IF; -- End if of comparison type check ...
1330 hr_utility.set_location(' Leaving:' || l_proc, 30);
1331 --
1332 END chk_comparison_value;
1333 --
1334 -- ----------------------------------------------------------------------------
1335 -- |-------------------------< chk_report_name >-----------------------------|
1336 -- ----------------------------------------------------------------------------
1337 --
1338 -- {Start Of Comments}
1339 --
1340 -- Description:
1341 -- This procedure is used to ensure that the exception report name is unique
1342 --
1343 -- Pre Conditions:
1344 -- None
1345 --
1346 -- In Arguments:
1347 -- exception_report_id
1348 -- business_group_id
1349 -- legislation_code
1350 -- exception_report_name
1351 -- object_version_number
1352 --
1353 -- Post Success:
1354 -- Processing continues
1355 --
1356 -- Post Failure:
1357 -- An application error is raised and processing is terminated
1358 --
1359 -- Access Status :
1360 -- Internal Table Handler Use only.
1361 --
1362 -- {End Of Comments}
1363 --
1364 -- ----------------------------------------------------------------------------
1365 PROCEDURE chk_report_name
1366 (p_exception_report_id IN number
1367 ,p_business_group_id IN number
1368 ,p_legislation_code IN varchar2
1369 ,p_exception_report_name IN varchar2
1370 ,p_object_version_number IN number
1371 ) IS
1372 --
1373 l_proc varchar2(72) := g_package||'chk_report_name';
1374 l_exists varchar2(1);
1375 l_api_updating boolean;
1376 l_legislation_code per_business_groups.legislation_code%TYPE;
1377
1378 cursor csr_report_name
1379 is
1380 select 'X'
1381 from pqp_exception_reports
1382 where exception_report_name = p_exception_report_name
1383 and( (legislation_code is not null
1384 and legislation_code = l_legislation_code)
1385 or (business_group_id is not null
1386 and business_group_id = p_business_group_id)
1387 );
1388 --
1389 BEGIN
1390 IF p_legislation_code is NULL THEN
1391 l_legislation_code:=pqp_exr_bus.return_legislation_code(p_business_group_id);
1392 ELSE
1393 l_legislation_code:=p_legislation_code;
1394 END IF;
1395
1396 --
1397 hr_utility.set_location('Entering:'|| l_proc, 10);
1398 --
1399
1400 -- Check manadatory arg
1401
1402 hr_api.mandatory_arg_error
1403 (p_api_name => l_proc
1404 ,p_argument => 'exception_report_name'
1405 ,p_argument_value => p_exception_report_name
1406 );
1407
1408 -- Only proceed with validation if :
1409 -- a) The g_old_rec is current and
1410 -- b) The report name has changed
1411 --
1412
1413 l_api_updating := pqp_exr_shd.api_updating
1414 (p_exception_report_id => p_exception_report_id
1415 ,p_object_version_number => p_object_version_number);
1416
1417 --
1418 IF ((l_api_updating and (pqp_exr_shd.g_old_rec.exception_report_name) <>
1419 (p_exception_report_name)) or
1420 (NOT l_api_updating)) THEN
1421
1422 --
1423 -- Check whether a row exists already in pqp_exception_reports
1424 -- with this exception_report_name
1425 --
1426
1427 hr_utility.set_location(l_proc, 20);
1428
1429 open csr_report_name;
1430 fetch csr_report_name into l_exists;
1431
1432 IF csr_report_name%FOUND THEN
1433
1434 -- Raise an error
1435
1436 fnd_message.set_name('PQP','PQP_230554_REPORT_NAME_UNIQUE' );
1437 fnd_message.raise_error;
1438 --hr_utility.set_message(8303,'PQP_230554_REPORT_NAME_UNIQUE' );
1439 --hr_utility.raise_error;
1440
1441 END IF; -- end if of report name check ...
1442 close csr_report_name;
1443 --
1444
1445 END IF; -- end if of api updating check ...
1446 --
1447
1448 hr_utility.set_location(' Leaving:' || l_proc, 30);
1449 --
1450 END chk_report_name;
1451 --
1452 -- ----------------------------------------------------------------------------
1453 -- |-------------------------< chk_report_grp_exists >------------------------|
1454 -- ----------------------------------------------------------------------------
1455 --
1456 -- {Start Of Comments}
1457 --
1458 -- Description:
1459 -- This procedure is used to ensure that the a row doesn't exist in
1460 -- exception_report_groups table with the same exception_report_id before
1461 -- deleting exception_reports
1462 --
1463 -- Pre Conditions:
1464 -- None
1465 --
1466 -- In Arguments:
1467 -- exception_report_id
1468 --
1469 -- Post Success:
1470 -- Processing continues
1471 --
1472 -- Post Failure:
1473 -- An application error is raised and processing is terminated
1474 --
1475 -- Access Status :
1476 -- Internal Table Handler Use only.
1477 --
1478 -- {End Of Comments}
1479 --
1480 -- ----------------------------------------------------------------------------
1481 PROCEDURE chk_report_grp_exists
1482 (p_exception_report_id IN number
1483 ) IS
1484 --
1485 l_proc varchar2(72) := g_package||'chk_report_grp_exists';
1486 l_exists varchar2(1);
1487
1488 cursor csr_rep_grp
1489 is
1490 select 'X'
1491 from pqp_exception_report_groups
1492 where exception_report_id = p_exception_report_id;
1493 --
1494 BEGIN
1495 --
1496 hr_utility.set_location('Entering:'|| l_proc, 10);
1497 --
1498
1499 open csr_rep_grp;
1500 fetch csr_rep_grp into l_exists;
1501
1502 IF csr_rep_grp%FOUND THEN
1503
1504 -- Raise error
1505
1506 fnd_message.set_name('PQP','PQP_230555_CHILD_REPORT_GRP' );
1507 fnd_message.raise_error;
1508 --hr_utility.set_message(8303,'PQP_230555_CHILD_REPORT_GRP' );
1509 --hr_utility.raise_error;
1510
1511 END IF; -- end if of report group row check...
1512 --
1513
1514 hr_utility.set_location(' Leaving:' || l_proc, 20);
1515 --
1516 END chk_report_grp_exists;
1517 --
1518 --This procedure is introduced when a bug was detected
1519 --while deleteing the seeded report which was throwing
1520 --core message.
1521
1522 Procedure chk_del_seed_report ( p_rec in pqp_exr_shd.g_rec_type)
1523 IS
1524
1525 BEGIN
1526
1527 IF p_rec.business_group_id IS NULL THEN
1528
1529 fnd_message.set_name('PQP','PQP_230919_SEED_DEL_CHK' );
1530 fnd_message.raise_error;
1531
1532 END IF;
1533
1534 END;
1535
1536
1537 Procedure chk_upd_seed_report ( p_rec in pqp_exr_shd.g_rec_type)
1538 IS
1539
1540 BEGIN
1541 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1542 IF p_rec.business_group_id IS NULL THEN
1543
1544 fnd_message.set_name('PQP','PQP_230584_SEED_UPD_CHK' );
1545 fnd_message.raise_error;
1546
1547 END IF;
1548 END IF;
1549
1550 END;
1551 --
1552 -- ----------------------------------------------------------------------------
1553 -- |---------------------------< insert_validate >----------------------------|
1554 -- ----------------------------------------------------------------------------
1555 Procedure insert_validate
1556 (p_rec in pqp_exr_shd.g_rec_type
1557 ) is
1558 --
1559
1560 l_proc varchar2(72) := g_package||'insert_validate';
1561 --
1562 Begin
1563 hr_utility.set_location('Entering:'||l_proc, 5);
1564 --
1565 -- Call all supporting business operations
1566 --
1567 --
1568
1569 chk_startup_action(p_insert =>true
1570 ,p_business_group_id =>p_rec.business_group_id
1571 ,p_legislation_code =>p_rec.legislation_code
1572 ,p_legislation_subgroup =>NULL
1573 );
1574 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1575 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1576 END IF;
1577
1578 --
1579 -- Check currency code
1580 --
1581
1582 hr_utility.set_location('Entering:'||l_proc, 10);
1583 chk_output_format
1584 (p_exception_report_id => p_rec.exception_report_id
1585 ,p_business_group_id => p_rec.business_group_id
1586 ,p_output_format_type => p_rec.output_format_type
1587 ,p_object_version_number => p_rec.object_version_number
1588 );
1589
1590
1591
1592
1593
1594
1595 hr_utility.set_location(l_proc, 20);
1596
1597 chk_currency_code (p_exception_report_id => p_rec.exception_report_id
1598 ,p_business_group_id => p_rec.business_group_id
1599 ,p_currency_code => p_rec.currency_code
1600 ,p_object_version_number => p_rec.object_version_number
1601 );
1602 --
1603
1604 -- Check balance type id
1605 --
1606
1607 hr_utility.set_location(l_proc, 30);
1608
1609 chk_balance_type_id (p_exception_report_id => p_rec.exception_report_id
1610 ,p_business_group_id => p_rec.business_group_id
1611 ,p_legislation_code => p_rec.legislation_code
1612 ,p_balance_type_id => p_rec.balance_type_id
1613 ,p_object_version_number => p_rec.object_version_number
1614 );
1615
1616 --
1617 -- Check balance dimension id
1618 --
1619
1620 hr_utility.set_location(l_proc, 40);
1621
1622 chk_bal_dim_id (p_exception_report_id => p_rec.exception_report_id
1623 ,p_balance_type_id => p_rec.balance_type_id
1624 ,p_balance_dimension_id => p_rec.balance_dimension_id
1625 ,p_object_version_number => p_rec.object_version_number
1626 );
1627
1628 --
1629 -- Check variance type
1630 --
1631
1632 hr_utility.set_location(l_proc, 50);
1633
1634 chk_variance_type (p_exception_report_id => p_rec.exception_report_id
1635 ,p_variance_type => p_rec.variance_type
1636 ,p_comparison_type => p_rec.comparison_type
1637 ,p_object_version_number => p_rec.object_version_number
1638 );
1639
1640 --
1641 -- Check variance value
1642 --
1643
1644 hr_utility.set_location(l_proc, 60);
1645
1646 chk_variance_value (p_exception_report_id => p_rec.exception_report_id
1647 ,p_variance_type => p_rec.variance_type
1648 ,p_variance_value => p_rec.variance_value
1649 ,p_object_version_number => p_rec.object_version_number
1650 );
1651
1652 --
1653 -- Check comparison type
1654 --
1655
1656 hr_utility.set_location(l_proc, 70);
1657
1658 chk_comparison_type (p_exception_report_id => p_rec.exception_report_id
1659 ,p_business_group_id => p_rec.business_group_id
1660 ,p_comparison_type => p_rec.comparison_type
1661 ,p_object_version_number => p_rec.object_version_number
1662 ,p_balance_dimension_id => p_rec.balance_dimension_id
1663 );
1664
1665 --
1666 -- Check comparison value
1667 --
1668
1669 hr_utility.set_location(l_proc, 80);
1670
1671 chk_comparison_value (p_exception_report_id => p_rec.exception_report_id
1672 ,p_comparison_type => p_rec.comparison_type
1673 ,p_comparison_value => p_rec.comparison_value
1674 ,p_object_version_number => p_rec.object_version_number
1675 );
1676
1677 --
1678 -- Check report name
1679 --
1680
1681 hr_utility.set_location(l_proc, 90);
1682
1683 chk_report_name (p_exception_report_id => p_rec.exception_report_id
1684 ,p_business_group_id => p_rec.business_group_id
1685 ,p_legislation_code => p_rec.legislation_code
1686 ,p_exception_report_name => p_rec.exception_report_name
1687 ,p_object_version_number => p_rec.object_version_number
1688 );
1689
1690 --
1691 --
1692 EXCEPTION
1693 WHEN app_exception.application_exception THEN
1694 -- IF hr_multi_message.exception_add
1695 -- (p_same_associated_columns => 'Y') THEN
1696 RAISE;
1697 --END IF;
1698 -- After validating the set of important attributes
1699 -- if Multiple Message detection is enabled and at least
1700 -- one error has been found then abort further validation.
1701 hr_multi_message.end_validation_set;
1702 hr_utility.set_location(' Leaving:'||l_proc, 100);
1703 End insert_validate;
1704 --
1705 -- ----------------------------------------------------------------------------
1706 -- |---------------------------< update_validate >----------------------------|
1707 -- ----------------------------------------------------------------------------
1708 Procedure update_validate
1709 (p_rec in pqp_exr_shd.g_rec_type
1710 ) is
1711 --
1712 l_proc varchar2(72) := g_package||'update_validate';
1713 --
1714 Begin
1715 hr_utility.set_location('Entering:'||l_proc, 5);
1716 --
1717 -- Call all supporting business operations
1718 --
1719 --
1720 --
1721 IF p_rec.business_group_id IS NOT NULL THEN
1722 chk_startup_action(p_insert =>false
1723 ,p_business_group_id =>p_rec.business_group_id
1724 ,p_legislation_code =>p_rec.legislation_code
1725 ,p_legislation_subgroup=>NULL
1726 );
1727 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1728 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1729 END IF;
1730 END IF;
1731
1732
1733 chk_upd_seed_report ( p_rec=>p_rec );
1734 --
1735
1736 hr_multi_message.end_validation_set;
1737 chk_non_updateable_args (
1738 p_rec => p_rec
1739 );
1740 --
1741
1742 hr_multi_message.end_validation_set;
1743 --
1744 -- Check currency code
1745 --
1746
1747 hr_utility.set_location(l_proc, 10);
1748 chk_output_format
1749 (p_exception_report_id => p_rec.exception_report_id
1750 ,p_business_group_id => p_rec.business_group_id
1751 ,p_output_format_type => p_rec.output_format_type
1752 ,p_object_version_number => p_rec.object_version_number
1753 );
1754
1755
1756 hr_utility.set_location(l_proc, 20);
1757
1758
1759
1760 chk_currency_code (p_exception_report_id => p_rec.exception_report_id
1761 ,p_business_group_id => p_rec.business_group_id
1762 ,p_currency_code => p_rec.currency_code
1763 ,p_object_version_number => p_rec.object_version_number
1764 );
1765
1766 --
1767 -- Check balance type id
1768 --
1769
1770 hr_utility.set_location(l_proc, 30);
1771
1772 chk_balance_type_id (p_exception_report_id => p_rec.exception_report_id
1773 ,p_business_group_id => p_rec.business_group_id
1774 ,p_legislation_code => p_rec.legislation_code
1775 ,p_balance_type_id => p_rec.balance_type_id
1776 ,p_object_version_number => p_rec.object_version_number
1777 );
1778
1779 --
1780 -- Check balance dimension id
1781 --
1782
1783 hr_utility.set_location(l_proc, 40);
1784
1785 chk_bal_dim_id (p_exception_report_id => p_rec.exception_report_id
1786 ,p_balance_type_id => p_rec.balance_type_id
1787 ,p_balance_dimension_id => p_rec.balance_dimension_id
1788 ,p_object_version_number => p_rec.object_version_number
1789 );
1790
1791 --
1792 -- Check variance type
1793 --
1794
1795 hr_utility.set_location(l_proc, 50);
1796
1797 chk_variance_type (p_exception_report_id => p_rec.exception_report_id
1798 ,p_variance_type => p_rec.variance_type
1799 ,p_comparison_type => p_rec.comparison_type
1800 ,p_object_version_number => p_rec.object_version_number
1801 );
1802
1803 --
1804 -- Check variance value
1805 --
1806
1807 hr_utility.set_location(l_proc, 60);
1808
1809 chk_variance_value (p_exception_report_id => p_rec.exception_report_id
1810 ,p_variance_type => p_rec.variance_type
1811 ,p_variance_value => p_rec.variance_value
1812 ,p_object_version_number => p_rec.object_version_number
1813 );
1814
1815 --
1816 -- Check comparison type
1817 --
1818
1819 hr_utility.set_location(l_proc, 70);
1820
1821 chk_comparison_type (p_exception_report_id => p_rec.exception_report_id
1822 ,p_business_group_id => p_rec.business_group_id
1823 ,p_comparison_type => p_rec.comparison_type
1824 ,p_object_version_number => p_rec.object_version_number
1825 ,p_balance_dimension_id => p_rec.balance_dimension_id
1826 );
1827
1828 --
1829 -- Check comparison value
1830 --
1831
1832 hr_utility.set_location(l_proc, 80);
1833
1834 chk_comparison_value (p_exception_report_id => p_rec.exception_report_id
1835 ,p_comparison_type => p_rec.comparison_type
1836 ,p_comparison_value => p_rec.comparison_value
1837 ,p_object_version_number => p_rec.object_version_number
1838 );
1839
1840 --
1841 -- Check report name
1842 --
1843
1844 hr_utility.set_location(l_proc, 90);
1845
1846 chk_report_name (p_exception_report_id => p_rec.exception_report_id
1847 ,p_business_group_id => p_rec.business_group_id
1848 ,p_legislation_code => p_rec.legislation_code
1849 ,p_exception_report_name => p_rec.exception_report_name
1850 ,p_object_version_number => p_rec.object_version_number
1851 );
1852
1853
1854 --
1855 EXCEPTION
1856 WHEN app_exception.application_exception THEN
1857 --IF hr_multi_message.exception_add
1858 -- (
1859 -- p_same_associated_columns => 'Y'
1860 -- )
1861 --THEN
1862 RAISE;
1863 --END IF;
1864 --
1865 -- After validating the set of important attributes,
1866 -- if Multiple Message detection is enabled and at least
1867 -- one error has been found then abort further validation.
1868 --
1869 hr_multi_message.end_validation_set;
1870
1871 hr_utility.set_location(' Leaving:'||l_proc, 100);
1872 End update_validate;
1873 --
1874 -- ----------------------------------------------------------------------------
1875 -- |---------------------------< delete_validate >----------------------------|
1876 -- ----------------------------------------------------------------------------
1877 Procedure delete_validate
1878 (p_rec in pqp_exr_shd.g_rec_type
1879 ) is
1880 --
1881 l_proc varchar2(72) := g_package||'delete_validate';
1882 --
1883 Begin
1884 hr_utility.set_location('Entering:'||l_proc, 5);
1885 --
1886 --
1887 IF pqp_exr_shd.g_old_rec.business_group_id IS NOT NULL THEN
1888 chk_startup_action(p_insert =>false
1889 ,p_business_group_id =>pqp_exr_shd.g_old_rec.business_group_id
1890 ,p_legislation_code =>pqp_exr_shd.g_old_rec.legislation_code
1891 ,p_legislation_subgroup =>NULL
1892 );
1893 END IF;
1894
1895 --
1896 -- Call all supporting business operations
1897 --
1898
1899 --
1900 -- Check whether exception record group exists for this report id
1901 --
1902 chk_del_seed_report ( p_rec=>pqp_exr_shd.g_old_rec);
1903 hr_multi_message.end_validation_set;
1904 hr_utility.set_location(l_proc, 10);
1905 chk_report_grp_exists(p_exception_report_id => p_rec.exception_report_id);
1906 Exception
1907 when app_exception.application_exception then
1908 -- IF hr_multi_message.exception_add
1909 -- (p_same_associated_columns => 'Y')
1910 -- THEN
1911 RAISE;
1912 -- END IF;
1913 -- After validating the set of important attributes
1914 -- if Multiple Message detection is enabled and at least
1915 -- one error has been found then abort further validation.
1916 hr_multi_message.end_validation_set;
1917
1918 hr_utility.set_location(' Leaving:'||l_proc, 20);
1919 End delete_validate;
1920 --
1921 end pqp_exr_bus;
[Home] [Help]