[Home] [Help]
PACKAGE BODY: APPS.PQH_CRF_BUS
Source
1 Package Body pqh_crf_bus as
2 /* $Header: pqcrfrhi.pkb 120.0 2005/10/06 14:52 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_crf_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_criteria_rate_factor_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_criteria_rate_factor_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , pqh_criteria_rate_factors crf
32 where crf.criteria_rate_factor_id = p_criteria_rate_factor_id
33 and pbg.business_group_id (+) = crf.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'criteria_rate_factor_id'
50 ,p_argument_value => p_criteria_rate_factor_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'CRITERIA_RATE_FACTOR_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_criteria_rate_factor_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , pqh_criteria_rate_factors crf
102 where crf.criteria_rate_factor_id = p_criteria_rate_factor_id
103 and pbg.business_group_id (+) = crf.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'criteria_rate_factor_id'
119 ,p_argument_value => p_criteria_rate_factor_id
120 );
121 --
122 if ( nvl(pqh_crf_bus.g_criteria_rate_factor_id, hr_api.g_number)
123 = p_criteria_rate_factor_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := pqh_crf_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 pqh_crf_bus.g_criteria_rate_factor_id := p_criteria_rate_factor_id;
154 pqh_crf_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------< chk_non_updateable_args >------------------------|
162 -- ----------------------------------------------------------------------------
163 -- {Start Of Comments}
164 --
165 -- Description:
166 -- This procedure is used to ensure that non updateable attributes have
167 -- not been updated. If an attribute has been updated an error is generated.
168 --
169 -- Pre Conditions:
170 -- g_old_rec has been populated with details of the values currently in
171 -- the database.
172 --
173 -- In Arguments:
174 -- p_rec has been populated with the updated values the user would like the
175 -- record set to.
176 --
177 -- Post Success:
178 -- Processing continues if all the non updateable attributes have not
179 -- changed.
180 --
181 -- Post Failure:
182 -- An application error is raised if any of the non updatable attributes
183 -- have been altered.
184 --
185 -- {End Of Comments}
186 -- ----------------------------------------------------------------------------
187 Procedure chk_non_updateable_args
188 (p_rec in pqh_crf_shd.g_rec_type
189 ) IS
190 --
191 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
192 --
193 Begin
194 --
195 -- Only proceed with the validation if a row exists for the current
196 -- record in the HR Schema.
197 --
198 IF NOT pqh_crf_shd.api_updating
199 (p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id
200 ,p_object_version_number => p_rec.object_version_number
201 ) THEN
202 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
203 fnd_message.set_token('PROCEDURE ', l_proc);
204 fnd_message.set_token('STEP ', '5');
205 fnd_message.raise_error;
206 END IF;
207 --
208 -- EDIT_HERE: Add checks to ensure non-updateable args have
209 -- not been updated.
210 --
211 --
212 if nvl(p_rec.business_group_id, hr_api.g_number) <>
213 nvl(pqh_crf_shd.g_old_rec.business_group_id
214 ,hr_api.g_number
215 ) then
216 hr_api.argument_changed_error
217 (p_api_name => l_proc
218 ,p_argument => 'BUSINESS_GROUP_ID'
219 ,p_base_table => pqh_crf_shd.g_tab_nam
220 );
221 end if;
222 --
223 End chk_non_updateable_args;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |----------------------< chk_startup_action >------------------------------|
227 -- ----------------------------------------------------------------------------
228 --
229 -- Description:
230 -- This procedure will check that the current action is allowed according
231 -- to the current startup mode.
232 --
233 -- ----------------------------------------------------------------------------
234 PROCEDURE chk_startup_action
235 (p_insert IN boolean
236 ,p_business_group_id IN number
237 ,p_legislation_code IN varchar2
238 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
239 --
240 BEGIN
241 --
242 -- Call the supporting procedure to check startup mode
243 -- EDIT_HERE: The following call should be edited if certain types of rows
244 -- are not permitted.
245 IF (p_insert) THEN
246 hr_startup_data_api_support.chk_startup_action
247 (p_generic_allowed => TRUE
248 ,p_startup_allowed => TRUE
249 ,p_user_allowed => TRUE
250 ,p_business_group_id => p_business_group_id
251 ,p_legislation_code => p_legislation_code
252 ,p_legislation_subgroup => p_legislation_subgroup
253 );
254 ELSE
255 hr_startup_data_api_support.chk_upd_del_startup_action
256 (p_generic_allowed => TRUE
257 ,p_startup_allowed => TRUE
258 ,p_user_allowed => TRUE
259 ,p_business_group_id => p_business_group_id
260 ,p_legislation_code => p_legislation_code
261 ,p_legislation_subgroup => p_legislation_subgroup
262 );
263 END IF;
264 --
265 END chk_startup_action;
266
267 -- ----------------------------------------------------------------------------
268 -- |------< chk_criteria_rate_defn_id>------|
269 -- ----------------------------------------------------------------------------
270
271 -- Description
272 -- This procedure checks that a referenced foreign key actually exists
273 -- in the referenced table.
274 --
275 -- Pre-Conditions
276 -- None.
277 --
278 -- In Parameters
279 -- p_criteria_rate_factor_id PK
280 -- p_criteria_rate_defn_id ID of FK column
281 -- p_object_version_number object version number
282 --
283 -- Post Success
284 -- Processing continues
285 --
286 -- Post Failure
287 -- Error raised.
288 --
289 -- Access Status
290 -- Internal table handler use only.
291 --
292 Procedure chk_criteria_rate_defn_id (p_criteria_rate_factor_id in number,
293 p_criteria_rate_defn_id in number,
294 p_object_version_number in number) is
295 --
296 l_proc varchar2(72) := g_package||'chk_criteria_rate_defn_id';
297 l_api_updating boolean;
298 l_dummy varchar2(1);
299 --
300
301 cursor c1 is
302 select null from PQH_CRITERIA_RATE_DEFN a
303 where a.criteria_rate_defn_id = p_criteria_rate_defn_id;
304
305 --
306 Begin
307 --
308 hr_utility.set_location('Entering:'||l_proc,10);
309 --
310
311 l_api_updating := pqh_crf_shd.api_updating
312 (p_criteria_rate_factor_id => p_criteria_rate_factor_id,
313 p_object_version_number => p_object_version_number);
314 --
315 if (l_api_updating
316 and nvl(p_criteria_rate_defn_id,hr_api.g_number)
317 <> nvl(pqh_crf_shd.g_old_rec.criteria_rate_defn_id,hr_api.g_number)
318 or not l_api_updating) then
319 --
320 -- check if criteria_rate_defn_id value exists in
321 -- PQH_CRITERIA_RATE_DEFN table
322 --
323 open c1;
324 --
325
326 fetch c1 into l_dummy;
327
328 if c1%notfound then
329 --
330 close c1;
331 --
332 -- raise error as FK does not relate to PK in PQH_CRITERIA_RATE_DEFN
333 -- table.
334 --
335 pqh_crf_shd.constraint_error('PQH_CRITERIA_RATE_FACTORS_FK1');
336 --
337 end if;
338 --
339 close c1;
340
341 --
342 --
343 end if;
344 --
345 hr_utility.set_location('Leaving:'||l_proc,90);
346 --
347 End chk_criteria_rate_defn_id;
348 --
349
350
351
352 -- ----------------------------------------------------------------------------
353 -- |------< chk_parent_rate_matrix_id >------|
354 -- ----------------------------------------------------------------------------
355
356 -- Description
357 -- This procedure checks that a referenced foreign key actually exists
358 -- in the referenced table.
359 --
360 -- Pre-Conditions
361 -- None.
362 --
363 -- In Parameters
364 -- p_criteria_rate_factor_id PK
365 -- p_parent_rate_matrix_id ID of FK column
366 -- p_object_version_number object version number
367 --
368 -- Post Success
369 -- Processing continues
370 --
371 -- Post Failure
372 -- Error raised.
373 --
374 -- Access Status
375 -- Internal table handler use only.
376 --
377 Procedure chk_parent_rate_matrix_id (p_criteria_rate_factor_id in number,
378 p_parent_rate_matrix_id in number,
379 p_object_version_number in number) is
380 --
381 l_proc varchar2(72) := g_package||'chk_parent_rate_matrix_id';
382 l_api_updating boolean;
383 l_dummy varchar2(1);
384 --
385 cursor c1 is
386 select null from BEN_PL_F a
387 where a.pl_id = p_parent_rate_matrix_id;
388
389 --
390 Begin
391 --
392 hr_utility.set_location('Entering:'||l_proc,10);
393 --
394
395 l_api_updating := pqh_crf_shd.api_updating
396 (p_criteria_rate_factor_id => p_criteria_rate_factor_id,
397 p_object_version_number => p_object_version_number);
398 --
399 if (l_api_updating
400 and nvl(p_parent_rate_matrix_id,hr_api.g_number)
401 <> nvl(pqh_crf_shd.g_old_rec.parent_rate_matrix_id,hr_api.g_number)
402 or not l_api_updating) then
403 --
404 -- check if PL_ID value exists in
405 -- BEN_PL_F table
406 --
407 open c1;
408 --
409 fetch c1 into l_dummy;
410
411 if c1%notfound then
412 --
413 close c1;
414 --
415 -- raise error as FK does not relate to PK in BEN_PL_F
416 -- table.
417 --
418 pqh_crf_shd.constraint_error('PQH_CRITERIA_RATE_FACTORS_FK4');
419 --
420 end if;
421 --
422 close c1;
423
424 --
425 --
426 end if;
427 --
428 hr_utility.set_location('Leaving:'||l_proc,90);
429 --
430 End chk_parent_rate_matrix_id;
431 --
432
433
434
435
436
437 -- ----------------------------------------------------------------------------
438 -- |------< chk_business_group_id>------|
439 -- ----------------------------------------------------------------------------
440
441 -- Description
442 -- This procedure checks that a referenced foreign key actually exists
443 -- in the referenced table.
444 --
445 -- Pre-Conditions
446 -- None.
447 --
448 -- In Parameters
449 -- p_criteria_rate_factor_id PK
450 -- p_business_group_id ID of FK column
451 -- p_object_version_number object version number
452 --
453 -- Post Success
454 -- Processing continues
455 --
456 -- Post Failure
457 -- Error raised.
458 --
459 -- Access Status
460 -- Internal table handler use only.
461 --
462 Procedure chk_business_group_id (p_criteria_rate_factor_id in number,
466 l_proc varchar2(72) := g_package||'chk_business_group_id';
463 p_business_group_id in number,
464 p_object_version_number in number) is
465 --
467 l_api_updating boolean;
468 l_dummy varchar2(1);
469 --
470 cursor c1 is
471 select null from HR_ALL_ORGANIZATION_UNITS a
472 where a.business_group_id = p_business_group_id;
473
474 --
475 Begin
476 --
477 hr_utility.set_location('Entering:'||l_proc,10);
478 --
479
480 l_api_updating := pqh_crf_shd.api_updating
481 (p_criteria_rate_factor_id => p_criteria_rate_factor_id,
482 p_object_version_number => p_object_version_number);
483 --
484 if (l_api_updating
485 and nvl(p_business_group_id,hr_api.g_number)
486 <> nvl(pqh_crf_shd.g_old_rec.business_group_id,hr_api.g_number)
487 or not l_api_updating) then
488 --
489 -- check if business_group_id value exists in
490 -- HR_ALL_ORGANIZATION_UNITS table
491 --
492 open c1;
493 --
494
495 fetch c1 into l_dummy;
496
497 if c1%notfound then
498 --
499 close c1;
500 --
501 -- raise error as FK does not relate to PK in HR_ALL_ORGANIZATION_UNITS
502 -- table.
503 --
504 pqh_crf_shd.constraint_error('PQH_CRITERIA_RATE_FACTORS_FK3');
505 --
506 end if;
507 --
508 close c1;
509
510 --
511 --
512 end if;
513 --
514 hr_utility.set_location('Leaving:'||l_proc,90);
515 --
516 End chk_business_group_id;
517 --
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537 --
538 -- ----------------------------------------------------------------------------
539 -- |---------------------------< insert_validate >----------------------------|
540 -- ----------------------------------------------------------------------------
541 Procedure insert_validate
542 (p_rec in pqh_crf_shd.g_rec_type
543 ) is
544 --
545 l_proc varchar2(72) := g_package||'insert_validate';
546 --
547 Begin
548 hr_utility.set_location('Entering:'||l_proc, 5);
549 --
550 -- Call all supporting business operations
551 --
552 --
553 chk_startup_action(true
554 ,p_rec.business_group_id
555 ,p_rec.legislation_code
556 );
557 IF hr_startup_data_api_support.g_startup_mode
558 NOT IN ('GENERIC','STARTUP') THEN
559 --
560 -- Validate Important Attributes
561 --
562 hr_api.validate_bus_grp_id
563 (p_business_group_id => p_rec.business_group_id
564 ,p_associated_column1 => pqh_crf_shd.g_tab_nam
565 || '.BUSINESS_GROUP_ID');
566 --
567 -- after validating the set of important attributes,
568 -- if Multiple Message Detection is enabled and at least
569 -- one error has been found then abort further validation.
570 --
571 hr_multi_message.end_validation_set;
572 END IF;
573 --
574 --
575 -- Validate Dependent Attributes
576 --
577 --
578
579 chk_criteria_rate_defn_id
580 (
581 p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
582 p_criteria_rate_defn_id => p_rec.criteria_rate_defn_id,
583 p_object_version_number => p_rec.object_version_number
584 );
585
586 IF p_rec.parent_rate_matrix_id IS NOT NULL THEN
587 chk_parent_rate_matrix_id
588 (p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
589 p_parent_rate_matrix_id => p_rec.parent_rate_matrix_id,
590 p_object_version_number => p_rec.object_version_number);
591 END IF;
592
593
594 -- check parent_criteria_rate_defn_id
595 chk_criteria_rate_defn_id
596 (
597 p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
598 p_criteria_rate_defn_id => p_rec.parent_criteria_rate_defn_id,
599 p_object_version_number => p_rec.object_version_number
600 );
601
602
603 IF p_rec.business_group_id IS NOT NULL THEN
604 chk_business_group_id
605 (p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
606 p_business_group_id => p_rec.business_group_id,
607 p_object_version_number => p_rec.object_version_number);
608 END IF;
609
610 hr_utility.set_location(' Leaving:'||l_proc, 10);
611 End insert_validate;
612 --
613 -- ----------------------------------------------------------------------------
614 -- |---------------------------< update_validate >----------------------------|
615 -- ----------------------------------------------------------------------------
616 Procedure update_validate
617 (p_rec in pqh_crf_shd.g_rec_type
618 ) is
619 --
620 l_proc varchar2(72) := g_package||'update_validate';
621 --
622 Begin
623 hr_utility.set_location('Entering:'||l_proc, 5);
624 --
625 -- Call all supporting business operations
626 --
627 --
628 chk_startup_action(false
629 ,p_rec.business_group_id
630 ,p_rec.legislation_code
631 );
632 IF hr_startup_data_api_support.g_startup_mode
636 --
633 NOT IN ('GENERIC','STARTUP') THEN
634 --
635 -- Validate Important Attributes
637 hr_api.validate_bus_grp_id
638 (p_business_group_id => p_rec.business_group_id
639 ,p_associated_column1 => pqh_crf_shd.g_tab_nam
640 || '.BUSINESS_GROUP_ID');
641 --
642 -- After validating the set of important attributes,
643 -- if Multiple Message Detection is enabled and at least
644 -- one error has been found then abort further validation.
645 --
646 hr_multi_message.end_validation_set;
647 END IF;
648 --
649 --
650 -- Validate Dependent Attributes
651 --
652 chk_criteria_rate_defn_id
653 (
654 p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
655 p_criteria_rate_defn_id => p_rec.criteria_rate_defn_id,
656 p_object_version_number => p_rec.object_version_number
657 );
658
659 IF p_rec.parent_rate_matrix_id IS NOT NULL THEN
660 chk_parent_rate_matrix_id
661 (p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
662 p_parent_rate_matrix_id => p_rec.parent_rate_matrix_id,
663 p_object_version_number => p_rec.object_version_number);
664 END IF;
665
666
667 --- check parent_criteria_rate_defn_id
668 chk_criteria_rate_defn_id
669 (
670 p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
671 p_criteria_rate_defn_id => p_rec.parent_criteria_rate_defn_id,
672 p_object_version_number => p_rec.object_version_number
673 );
674
675
676 IF p_rec.business_group_id IS NOT NULL THEN
677 chk_business_group_id
678 (p_criteria_rate_factor_id => p_rec.criteria_rate_factor_id,
679 p_business_group_id => p_rec.business_group_id,
680 p_object_version_number => p_rec.object_version_number);
681 END IF;
682
683 chk_non_updateable_args
684 (p_rec => p_rec
685 );
686 --
687 --
688 hr_utility.set_location(' Leaving:'||l_proc, 10);
689 End update_validate;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |---------------------------< delete_validate >----------------------------|
693 -- ----------------------------------------------------------------------------
694 Procedure delete_validate
695 (p_rec in pqh_crf_shd.g_rec_type
696 ) is
697 --
698 l_proc varchar2(72) := g_package||'delete_validate';
699 --
700 Begin
701 hr_utility.set_location('Entering:'||l_proc, 5);
702 --
703 --
704 chk_startup_action(false
705 ,pqh_crf_shd.g_old_rec.business_group_id
706 ,pqh_crf_shd.g_old_rec.legislation_code
707 );
708 IF hr_startup_data_api_support.g_startup_mode
709 NOT IN ('GENERIC','STARTUP') THEN
710 --
711 -- Validate Important Attributes
712 --
713 --
714 -- After validating the set of important attributes,
715 -- if Multiple Message Detection is enabled and at least
716 -- one error has been found then abort further validation.
717 --
718 hr_multi_message.end_validation_set;
719 END IF;
720 --
721 -- Call all supporting business operations
722 --
723 hr_utility.set_location(' Leaving:'||l_proc, 10);
724 End delete_validate;
725 --
726 end pqh_crf_bus;