1 Package Body hr_hnm_bus as
2 /* $Header: hrhnmrhi.pkb 115.0 2004/01/09 01:21 vkarandi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_hnm_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_hierarchy_node_map_id number default null;
15 -- ----------------------------------------------------------------------------
16 -- |-----------------------< chk_non_updateable_args >------------------------|
17 -- ----------------------------------------------------------------------------
18 -- {Start Of Comments}
19 --
20 -- Description:
21 -- This procedure is used to ensure that non updateable attributes have
22 -- not been updated. If an attribute has been updated an error is generated.
23 --
24 -- Pre Conditions:
25 -- g_old_rec has been populated with details of the values currently in
26 -- the database.
27 --
28 -- In Arguments:
29 -- p_rec has been populated with the updated values the user would like the
30 -- record set to.
31 --
32 -- Post Success:
33 -- Processing continues if all the non updateable attributes have not
34 -- changed.
35 --
36 -- Post Failure:
37 -- An application error is raised if any of the non updatable attributes
38 -- have been altered.
39 --
40 -- {End Of Comments}
41 -- ----------------------------------------------------------------------------
42 Procedure chk_non_updateable_args
43 (p_rec in hr_hnm_shd.g_rec_type
44 ) IS
45 --
46 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
47 --
48 Begin
49 --
50 -- Only proceed with the validation if a row exists for the current
51 -- record in the HR Schema.
52 --
53 IF NOT hr_hnm_shd.api_updating
54 (p_hierarchy_node_map_id => p_rec.hierarchy_node_map_id
55 ,p_object_version_number => p_rec.object_version_number
56 ) THEN
57 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
58 fnd_message.set_token('PROCEDURE ', l_proc);
59 fnd_message.set_token('STEP ', '5');
60 fnd_message.raise_error;
61 END IF;
62 --
63 -- EDIT_HERE: Add checks to ensure non-updateable args have
64 -- not been updated.
65 --
66 End chk_non_updateable_args;
67 -- ----------------------------------------------------------------------------
68 -- --------------------------< CHK_HIERARCHY_ID>------------------------------
69 -- ----------------------------------------------------------------------------
70 -- {Start Of Comments}
71 --
72 -- Description:
73 -- This procedure ensures that the hierarchy id entered is present in the
74 -- master table hr_ki_hierarchies if not null.
75
76 -- Pre Conditions:
77 -- g_rec has been populated with details of the values
78 -- from the ins or the upd procedures
79 --
80 -- In Arguments:
81 -- p_hierarchy_id
82
83 -- Post Success:
84 -- Processing continues if hierarchy id is present in hr_ki_hierarchies
85 --
86 -- Post Failure:
87 -- An application error is raised if hierarchy id does not exist in
88 -- hr_ki_hierarchies.
89 --
90 -- {End Of Comments}
91 -- ----------------------------------------------------------------------------
92
93 Procedure chk_hierarchy_id(p_hierarchy_id in number)
94 is
95 l_proc varchar2(72) := g_package || 'chk_hierarchy_id';
96 l_found varchar2(10);
97
98
99 CURSOR csr_hrc_id is
100 select
101 'found'
102 From
103 hr_ki_hierarchies hrc
104 where
105 hrc.hierarchy_id = p_hierarchy_id;
106
107
108 begin
109
110 hr_utility.set_location(' Entering:' || l_proc,10);
111
112 -- check if the hierarchy id is not null
113 if(p_hierarchy_id is not null)
114 then
115 -- check if the id exists in the hr_ki_hierarchies
116 open csr_hrc_id;
117
118 fetch csr_hrc_id into l_found;
119
120 if(csr_hrc_id%NOTFOUND) then
121 close csr_hrc_id;
122 fnd_message.set_name('PER','PER_449922_HNM_HRCPRNT_ABSNT');
123 fnd_message.raise_error;
124 end if;
125 close csr_hrc_id;
126 end if;
127
128 hr_utility.set_location(' Leaving:' || l_proc,20);
129 Exception
130 when app_exception.application_exception then
131 IF hr_multi_message.exception_add
132 (p_associated_column1 =>
133 'HR_KI_HIERARCHY_NODE_MAPS.HIERARCHY_ID'
134 ) THEN
135 hr_utility.set_location(' Leaving:'|| l_proc,30);
136 raise;
137 END IF;
138
139 hr_utility.set_location(' Leaving:'|| l_proc,40);
140
141 end chk_hierarchy_id;
142
143 -- ----------------------------------------------------------------------------
144 -- ------------------------------< CHK_TOPIC_ID>-------------------------------
145 -- ----------------------------------------------------------------------------
146 -- {Start Of Comments}
147 --
148 -- Description:
149 -- This procedure ensures that the topic id entered is present in the
150 -- master table hr_ki_topics if not null.
151
152 -- Pre Conditions:
153 -- g_rec has been populated with details of the values
154 -- from the ins or the upd procedures
155 --
156 -- In Arguments:
157 -- p_topic_id
158
159 -- Post Success:
160 -- Processing continues if topic id is present in hr_ki_topics
161 --
162 -- Post Failure:
163 -- An application error is raised if topic id does not exist in
164 -- hr_ki_topics.
165 --
166 -- {End Of Comments}
167 -- ----------------------------------------------------------------------------
168
169 Procedure chk_topic_id(p_topic_id in number)
170 is
171 l_proc varchar2(72) := g_package || 'chk_topic_id';
172 l_found varchar2(10);
173
174
175 CURSOR csr_tpc_id is
176 select
177 'found'
178 From
179 hr_ki_topics tpc
180 where
181 tpc.topic_id = p_topic_id;
182
183
184 begin
185
186 hr_utility.set_location(' Entering:' || l_proc,10);
187
188 -- if the topic id is not null, check whether it exists in hr_ki_topics
189 if(p_topic_id is not null)
190 then
191 -- check if the id, key combination exists in the hr_ki_hierarchies
192 open csr_tpc_id;
193
194 fetch csr_tpc_id into l_found;
195
196 if(csr_tpc_id%NOTFOUND) then
197 close csr_tpc_id;
198 fnd_message.set_name('PER','PER_449923_HNM_TPCPRNT_ABSNT');
199 fnd_message.raise_error;
200 end if;
201 close csr_tpc_id;
202 end if;
203
204 hr_utility.set_location(' Leaving:' || l_proc,20);
205 Exception
206 when app_exception.application_exception then
207 IF hr_multi_message.exception_add
208 (p_associated_column1 =>
209 'HR_KI_HIERARCHY_NODE_MAPS.TOPIC_ID'
210 ) THEN
211 hr_utility.set_location(' Leaving:'|| l_proc,30);
212 raise;
213 END IF;
214
215 hr_utility.set_location(' Leaving:'|| l_proc,40);
216
217 end chk_topic_id;
218
219 -- ----------------------------------------------------------------------------
220 -- ---------------------------< CHK_USER_INTERFACE_ID>-------------------------
221 -- ----------------------------------------------------------------------------
222 -- {Start Of Comments}
223 --
224 -- Description:
225 -- This procedure ensures that the user interface id entered is present in
226 -- the master table hr_ki_user_interfaces if not null.
227
228 -- Pre Conditions:
229 -- g_rec has been populated with details of the values
230 -- from the ins or the upd procedures
231 --
232 -- In Arguments:
233 -- p_user_interface_id
234
235 -- Post Success:
236 -- Processing continues if user interface id is present in
237 -- hr_ki_user_interfaces.
238 --
239 -- Post Failure:
240 -- An application error is raised if user interface id does not exist in
241 -- hr_ki_user_interfaces.
242 --
243 -- {End Of Comments}
244 -- ----------------------------------------------------------------------------
245
246 Procedure chk_user_interface_id(p_user_interface_id in number)
247 is
248 l_proc varchar2(72) := g_package || 'chk_user_interface_id';
249 l_found varchar2(10);
250
251
252 CURSOR csr_itf_id is
253 select
254 'found'
255 From
256 hr_ki_user_interfaces itf
257 where
258 itf.user_interface_id = p_user_interface_id;
259
260
261 begin
262
263 hr_utility.set_location(' Entering:' || l_proc,10);
264
265 -- if the topic id is not null, check whether it exists in hr_ki_topics
266 if(p_user_interface_id is not null)
267 then
268 -- check if the id, key combination exists in the hr_ki_hierarchies
269 open csr_itf_id;
270
271 fetch csr_itf_id into l_found;
272
273 if(csr_itf_id%NOTFOUND) then
274 close csr_itf_id;
275 fnd_message.set_name('PER','PER_449924_HNM_INTPRNT_ABSNT');
276 fnd_message.raise_error;
277 end if;
278 close csr_itf_id;
279 end if;
280
281 hr_utility.set_location(' Leaving:' || l_proc,20);
282 Exception
283 when app_exception.application_exception then
284 IF hr_multi_message.exception_add
285 (p_associated_column1 =>
286 'HR_KI_HIERARCHY_NODE_MAPS.USER_INTERFACE_ID'
287 ) THEN
288 hr_utility.set_location(' Leaving:'|| l_proc,30);
289 raise;
290 END IF;
291
292 hr_utility.set_location(' Leaving:'|| l_proc,40);
293
294 end chk_user_interface_id;
295
296 -- ----------------------------------------------------------------------------
297 -- ---------------------------<CHK_VALID_COMBINATION>--------------------------
298 -- ----------------------------------------------------------------------------
299 -- {Start Of Comments}
300 --
301 -- Description:
302 -- This procedure ensures that the correct combination of values for
303 -- hierarchy_id,topic_id and user_interface_id are passed to the row handler
304 --
305 -- The following combinations are valid
306
307 -- hierarchy_id + topic_id
308 -- hierarchy_id + user_interface_id
309 -- topic_id + user_interface_id
310
311 -- In each of the above cases, the third parameter must be null.
312
313 -- Pre Conditions:
314 -- g_rec has been populated with details of the values
315 -- from the ins or the upd procedures
316 --
317 -- In Arguments:
318 -- p_hierarchy_id,p_topic_id,p_user_interface_id
319
320 -- Post Success:
321 -- Processing continues if a valid combination has been entered
322 --
323 -- Post Failure:
324 -- An application error is raised if an incorrect combination is entered
325 --
326 -- {End Of Comments}
327 -- ----------------------------------------------------------------------------
328
329 procedure chk_valid_combination(p_hierarchy_id in number,
330 p_topic_id in number,
331 p_user_interface_id in number)
332 is
333 l_proc varchar2(72) := g_package || 'chk_valid_combination';
334 l_found varchar2(30);
335
336 begin
337
338 hr_utility.set_location(' Entering:' || l_proc,10);
339
340 -- check if the combination of values entered for hierarchy_id, topic_id,
341 -- and user_interface_id are correct.
342
343 if(p_hierarchy_id is not null)
344 then
345 -- the hierarchy entries are not null, hence either the topic or the
346 -- ui entries should be populated,if both are populated then an
347 -- invalid combination is reported.
348
349 if( (p_topic_id is null and p_user_interface_id is null) or
350 (p_topic_id is not null and p_user_interface_id is not null)
351 )
352 then
353 fnd_message.set_name('PER','PER_449928_HNM_HRCINVLD_COMB');
354 fnd_message.raise_error;
355 end if;
356
357 else
358
359 if(p_topic_id is null or
360 p_user_interface_id is null)
361 then
362 fnd_message.set_name('PER','PER_449929_HNM_TPINTINVLD_COM');
363 fnd_message.raise_error;
364 end if;
365
366 end if;
367
368 hr_utility.set_location(' Leaving:' || l_proc,20);
369
370 Exception
371 when app_exception.application_exception then
372 IF hr_multi_message.exception_add
373 (p_associated_column1 =>
374 'HR_KI_HIERARCHY_NODE_MAPS.HIERARCHY_ID',
375 p_associated_column2 =>
376 'HR_KI_HIERARCHY_NODE_MAPS.TOPIC_ID',
377 p_associated_column3 =>
378 'HR_KI_HIERARCHY_NODE_MAPS.USER_INTERFACE_ID'
379 ) THEN
380 hr_utility.set_location(' Leaving:'|| l_proc,30);
381 raise;
382 END IF;
383
384 hr_utility.set_location(' Leaving:'|| l_proc,40);
385
386 end chk_valid_combination;
387
388 -- ----------------------------------------------------------------------------
389 -- ---------------------------<CHK_UNIQUE_COMBINATION>-------------------------
390 -- ----------------------------------------------------------------------------
391 -- {Start Of Comments}
392 --
393 -- Description:
394 -- This procedure ensures that a unique combination of the below three
395 -- cases are entered.
396 --
397 -- topic_id + hierarchy_id -> a row is entered with this combination only if
398 -- topic_id has not already been assigned to
399 -- hierarchy_id or any of it's ancestors in the
400 -- hierarchy tree.
401 --
402 -- user_interface_id + hierarchy_id -> a row is entered with this combination
403 -- only if user_interface_id has not
404 -- already been assigned to hierarchy_id
405 --
406 -- topic_id + user_interface_id -> a row is entered with this combinaton only
407 -- if topic_id has not already been assigned
408 -- to user_interface_id either directly or
409 -- indirectly through the hierarchy tree
410 -- accessible through that UI.
411 --
412 -- Pre Conditions:
413 -- g_rec has been populated with details of the values
414 -- from the ins or the upd procedures
415 --
416 -- In Arguments:
417 -- p_hierarchy_id,p_topic_id,p_user_interface_id
418 --
419 -- Post Success:
420 -- Processing continues if a valid unique combination has been entered
421 --
422 -- Post Failure:
423 -- An application error is raised if an non unique combination is entered
424 --
425 -- {End Of Comments}
426 -- ----------------------------------------------------------------------------
427
428 procedure chk_unique_combination(p_hierarchy_id in number,
429 p_topic_id in number,
430 p_user_interface_id in number)
431 is
432
433 cursor csr_hnm_hrctp
434 is
435 select
436 distinct 'found'
437 From
438 hr_ki_hierarchy_node_maps hnm
439 where
440 hnm.topic_id = p_topic_id
441 and hnm.hierarchy_id in
442 (
443 select hrc.hierarchy_id
444 from
445 hr_ki_hierarchies hrc
446 connect by prior hrc.parent_hierarchy_id = hrc.hierarchy_id
447 start with hrc.hierarchy_id = p_hierarchy_id
448 );
449
450 cursor csr_hnm_hrcui
451 is
452 select
453 distinct 'found'
454 From
455 hr_ki_hierarchy_node_maps hnm
456 where
457 hnm.hierarchy_id = p_hierarchy_id and
458 hnm.user_interface_id = p_user_interface_id;
459
460 cursor csr_hnm_tpui
461 is
462 select
463 distinct 'found'
464 from
465 hr_ki_hierarchy_node_maps hnm
466 where
467 (
468 hnm.topic_id = p_topic_id
469 and hnm.user_interface_id = p_user_interface_id
470 )
471 OR
472 (
473 hnm.topic_id = p_topic_id
474 and hnm.hierarchy_id in
475 (
476 select
477 hierarchy_id
478 from
479 hr_ki_hierarchies hrc
480 connect by prior hrc.parent_hierarchy_id = hrc.hierarchy_id
481 start with hrc.hierarchy_id in (
482 select
483 hierarchy_id
484 from
485 hr_ki_hierarchy_node_maps hnm1
486 where
487 hnm1.user_interface_id=p_user_interface_id
488 and hnm1.hierarchy_id is not null
489 )
490 )
491 );
492
493
494 l_proc varchar2(72) := g_package || 'chk_unique_combination';
495 l_found varchar2(10);
496
497 Begin
498
499 hr_utility.set_location(' Entering:' || l_proc,10);
500
501 -- the following validation needs to be done only if no errors have already
502 -- been detected previously, an inclusive error check needs to be done to
503 -- accomodate errors occuring from the first chk_valid_combination check.
504
505 if hr_multi_message.no_all_inclusive_error
506 (p_check_column1 =>
507 'HR_KI_HIERARCHY_NODE_MAPS.HIERARCHY_ID'
508 ,p_check_column2 =>
509 'HR_KI_HIERARCHY_NODE_MAPS.TOPIC_ID'
510 ,p_check_column3 =>
511 'HR_KI_HIERARCHY_NODE_MAPS.USER_INTERFACE'
512 )
513 then
514 if p_hierarchy_id is not null and p_topic_id is not null
515 then
516 -- check if there already exists a row in HNM with this combination
517 -- checks to be made:
518 -- 1. check if a row exists with the exact pair
519 -- 2. check if the topic has already been assigned to a hierarchy node
520 -- higher up the hierarchy from p_hierarchy_id
521
522 open csr_hnm_hrctp;
523 fetch csr_hnm_hrctp into l_found;
524
525 if(csr_hnm_hrctp%FOUND)
526 then
527 close csr_hnm_hrctp;
528 fnd_message.set_name('PER','PER_449925_HNM_HRCTPMAP_DUPLI');
529 fnd_message.raise_error;
530 end if;
531
532 close csr_hnm_hrctp;
533
534 elsif p_hierarchy_id is not null and p_user_interface_id is not null
535 then
536 -- check if there already exists a row in HNM with this combination
537 open csr_hnm_hrcui;
538 fetch csr_hnm_hrcui into l_found;
539
540 if(csr_hnm_hrcui%FOUND)
541 then
542 close csr_hnm_hrcui;
543 fnd_message.set_name('PER','PER_449926_HNM_HRCUIMAP_DUPLI');
544 fnd_message.raise_error;
545 end if;
546
547 close csr_hnm_hrcui;
548
549 elsif p_hierarchy_id is null
550 then
551 -- check if the topic,user interface combination exists in HNM.
552 -- checks to be made:
553 -- 1. check if a row exists with the exact pair
554 -- 2. check if the topic has already been assigned to that UI through
555 -- a hierarchy node.
556
557 open csr_hnm_tpui;
558 fetch csr_hnm_tpui into l_found;
559
560 if(csr_hnm_tpui%FOUND)
561 then
562 close csr_hnm_tpui;
563 fnd_message.set_name('PER','PER_449927_HNM_TPCUIMAP_DUPLI');
564 fnd_message.raise_error;
565 end if;
566
567 close csr_hnm_tpui;
568
569 end if;
570
571 end if;
572
573 hr_utility.set_location(' Leaving:' || l_proc,20);
574
575 Exception
576 when app_exception.application_exception then
577 IF hr_multi_message.exception_add
578 (p_associated_column1 =>
579 'HR_KI_HIERARCHY_NODE_MAPS.HIERARCHY_ID',
580 p_associated_column2 =>
581 'HR_KI_HIERARCHY_NODE_MAPS.TOPIC_ID',
582 p_associated_column3 =>
583 'HR_KI_HIERARCHY_NODE_MAPS.USER_INTERFACE_ID'
584 ) THEN
585 hr_utility.set_location(' Leaving:'|| l_proc,30);
586 raise;
587 END IF;
588
589 hr_utility.set_location(' Leaving:'|| l_proc,40);
590
591 End chk_unique_combination;
592
593 --
594 -- ----------------------------------------------------------------------------
595 -- |---------------------------< insert_validate >----------------------------|
596 -- ----------------------------------------------------------------------------
597 Procedure insert_validate
598 (p_rec in hr_hnm_shd.g_rec_type
599 ) is
600 --
601 l_proc varchar2(72) := g_package||'insert_validate';
602 --
603 Begin
604 hr_utility.set_location('Entering:'||l_proc, 5);
605 --
606 -- Call all supporting business operations
607 --
608 --
609 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
610 --
611 -- Validate Dependent Attributes
612 chk_valid_combination(p_hierarchy_id => p_rec.hierarchy_id,
613 p_topic_id => p_rec.topic_id,
614 p_user_interface_id => p_rec.user_interface_id);
615
616 chk_hierarchy_id(p_hierarchy_id => p_rec.hierarchy_id);
617
618 chk_topic_id(p_topic_id => p_rec.topic_id);
619
620 chk_user_interface_id(p_user_interface_id => p_rec.user_interface_id);
621
622 chk_unique_combination(p_hierarchy_id => p_rec.hierarchy_id,
623 p_topic_id => p_rec.topic_id,
624 p_user_interface_id => p_rec.user_interface_id);
625
626
627 --
628 --
629 hr_utility.set_location(' Leaving:'||l_proc, 10);
630 End insert_validate;
631 --
632 -- ----------------------------------------------------------------------------
633 -- |---------------------------< update_validate >----------------------------|
634 -- ----------------------------------------------------------------------------
635 Procedure update_validate
636 (p_rec in hr_hnm_shd.g_rec_type
637 ) is
638 --
639 l_proc varchar2(72) := g_package||'update_validate';
640 --
641 Begin
642 hr_utility.set_location('Entering:'||l_proc, 5);
643 --
644 -- Call all supporting business operations
645 --
646 --
647 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
648 -- Validate Dependent Attributes
649 --
650 chk_non_updateable_args
651 (p_rec => p_rec
652 );
653
654 chk_valid_combination(p_hierarchy_id => p_rec.hierarchy_id,
655 p_topic_id => p_rec.topic_id,
656 p_user_interface_id => p_rec.user_interface_id);
657
658 chk_hierarchy_id(p_hierarchy_id => p_rec.hierarchy_id);
659
660 chk_topic_id(p_topic_id => p_rec.topic_id);
661
662 chk_user_interface_id(p_user_interface_id => p_rec.user_interface_id);
663
664 chk_unique_combination(p_hierarchy_id => p_rec.hierarchy_id,
665 p_topic_id => p_rec.topic_id,
666 p_user_interface_id => p_rec.user_interface_id);
667 --
668 --
669 hr_utility.set_location(' Leaving:'||l_proc, 10);
670 End update_validate;
671 --
672 -- ----------------------------------------------------------------------------
673 -- |---------------------------< delete_validate >----------------------------|
674 -- ----------------------------------------------------------------------------
675 Procedure delete_validate
676 (p_rec in hr_hnm_shd.g_rec_type
677 ) is
678 --
679 l_proc varchar2(72) := g_package||'delete_validate';
680 --
681 Begin
682 hr_utility.set_location('Entering:'||l_proc, 5);
683 --
684 -- Call all supporting business operations
685 --
686 hr_utility.set_location(' Leaving:'||l_proc, 10);
687 End delete_validate;
688 --
689 end hr_hnm_bus;
[Home] [Help]