1 Package Body hr_hrc_bus as
2 /* $Header: hrhrcrhi.pkb 115.0 2004/01/09 01:12 vkarandi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_hrc_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_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_hrc_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_hrc_shd.api_updating
54 (p_hierarchy_id => p_rec.hierarchy_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 if nvl(p_rec.hierarchy_key, hr_api.g_varchar2) <>
64 nvl(hr_hrc_shd.g_old_rec.hierarchy_key,hr_api.g_varchar2
65 ) then
66 hr_api.argument_changed_error
67 (p_api_name => l_proc
68 ,p_argument => 'HIERARCHY_KEY'
69 ,p_base_table => hr_hrc_shd.g_tab_nam
70 );
71 end if;
72
73 --
74 End chk_non_updateable_args;
75 --
76 -- ----------------------------------------------------------------------------
77 -- --------------------------< CHK_HIERARCHY_KEY>------------------------------
78 -- ----------------------------------------------------------------------------
79 -- {Start Of Comments}
80 --
81 -- Description:
82 -- This procedure ensures a valid hierarchy key is entered
83 -- Pre Conditions:
84 -- g_rec has been populated with details of the values
85 -- from the ins or the upd procedures
86 --
87 -- In Arguments:
88 -- p_hierarchy_key
89 -- Post Success:
90 -- Processing continues if hierarchy key is not null and unique
91 --
92 -- Post Failure:
93 -- An application error is raised if hierarchy key is null or exists already
94 --
95 -- {End Of Comments}
96 -- ----------------------------------------------------------------------------
97
98 procedure chk_hierarchy_key
99 (p_hierarchy_key in varchar2
100 )
101 is
102 --
103 -- Declare cursors and local variables
104 --
105 -- Cursor to check if the hierarchy key provided in the insert is already
106 -- present
107 CURSOR csr_hrc_key is
108 select
109 distinct 'found'
110 From
111 hr_ki_hierarchies hrc
112 where
113 hrc.hierarchy_key = p_hierarchy_key;
114
115 -- Variables for API Boolean parameters
116 l_proc varchar2(72) := g_package ||'chk_hierarchy_key';
117 l_found varchar2(10);
118
119 Begin
120 hr_utility.set_location(' Entering:' || l_proc,10);
121 --
122 hr_api.mandatory_arg_error
123 (p_api_name => l_proc
124 ,p_argument => 'HIERARCHY_KEY'
125 ,p_argument_value => p_hierarchy_key
126 );
127
128 hr_utility.set_location(' Opening the cursor csr_hrc_key:' || l_proc,20);
129
130 OPEN csr_hrc_key;
131 FETCH csr_hrc_key into l_found;
132
133 IF csr_hrc_key%FOUND then
134 CLOSE csr_hrc_key;
135 fnd_message.set_name( 'PER','PER_449913_HRC_HRCHY_KEY_DUP');
136 fnd_message.raise_error;
137 END IF;
138
139 CLOSE csr_hrc_key;
140
141 hr_utility.set_location(' Closed the cursor csr_hrc_key:' || l_proc,30);
142
143 --
144 hr_utility.set_location(' Leaving:' || l_proc,40);
145 Exception
146 when app_exception.application_exception then
147 IF hr_multi_message.exception_add
148 (p_associated_column1 => 'HR_KI_HIERARCHIES.HIERARCHY_KEY'
149 )
150 THEN
151 hr_utility.set_location(' Leaving:'|| l_proc,50);
152 raise;
153 END IF;
154
155 hr_utility.set_location(' Leaving:'|| l_proc,60);
156 --
157 End chk_hierarchy_key ;
158
159 -- ----------------------------------------------------------------------------
160 -- ---------------------< CHK_PARENT_HIERARCHY_ID>-----------------------------
161 -- ----------------------------------------------------------------------------
162 -- {Start Of Comments}
163 --
164 -- Description:
165 -- This procedure ensures that a valid parent hierarchy id is entered
166 --
167 -- Pre Conditions:
168 -- g_rec has been populated with details of the values
169 -- from the ins or the upd procedures
170 --
171 -- In Arguments:
172 -- p_parent_hierarchy_id
173
174 -- Post Success:
175 -- Processing continues if the parent hierarchy id is valid.
176 --
177 -- Post Failure:
178 -- An application error is raised if there are any child rows from any of the
179 -- above mentioned tables.
180 --
181 -- {End Of Comments}
182 -- ----------------------------------------------------------------------------
183
184 procedure chk_parent_hierarchy_id
185 (p_parent_hierarchy_id in number
186 )
187 is
188 --
189 -- Declare cursors and local variables
190 cursor csr_hrc_parent_id is
191 select
192 'found'
193 From
194 hr_ki_hierarchies hrc
195 where
196 hrc.hierarchy_id = p_parent_hierarchy_id;
197
198 cursor csr_check_null is
199 select
200 'found'
201 From
202 hr_ki_hierarchies hrc
203 Where
204 hrc.parent_hierarchy_id is null;
205
206
207 -- Variables for API Boolean parameters
208 l_proc varchar2(72) := g_package ||'chk_parent_hierarchy_id';
209 l_found varchar2(10);
210
211 Begin
212
213 hr_utility.set_location(' Entering:'|| l_proc,10);
214
215
216 -- if the parent hierarchy id is null, then check if there is already a
217 -- global functional node
218
219 if p_parent_hierarchy_id is null then
220
221 hr_utility.set_location(' Parent hierarchy id is null:'|| l_proc,20);
222
223 open csr_check_null;
224 fetch csr_check_null into l_found;
225
226 If csr_check_null%FOUND then
227 hr_utility.set_location(' Global functional node already exists:'
228 || l_proc,30);
229 close csr_check_null;
230 fnd_message.set_name( 'PER','PER_449915_HRC_GLBL_FUNC_PRES');
231 fnd_message.raise_error;
232 End If;
233
234 close csr_check_null;
235
236 else
237
238 hr_utility.set_location(' Parent hierarchy is not null:'|| l_proc,40);
239
240 open csr_hrc_parent_id;
241 fetch csr_hrc_parent_id into l_found;
242
243 If csr_hrc_parent_id%NOTFOUND then
244 hr_utility.set_location(' Parent hierarchy does not exist:'
245 || l_proc,50);
246 close csr_hrc_parent_id;
247 fnd_message.set_name( 'PER','PER_449916_HRC_PARNT_ID_ABSNT');
248 fnd_message.raise_error;
249 End If;
250
251 close csr_hrc_parent_id;
252
253 End If;
254
255 hr_utility.set_location(' Leaving:'|| l_proc,60);
256
257 Exception
258 when app_exception.application_exception then
259 If hr_multi_message.exception_add
260 (p_associated_column1 =>
261 'HR_KI_HIERARCHIES.PARENT_HIERARCHY_ID'
262 ) THEN
263 hr_utility.set_location(' Leaving:'|| l_proc,70);
264 raise;
265 End If;
266
267 hr_utility.set_location(' Leaving:'|| l_proc,80);
268 --
269 End chk_parent_hierarchy_id ;
270 -- ----------------------------------------------------------------------------
271 -- ---------------------< CHK_PARENT_HIERARCHY_ID_UPDATE>----------------------
272 -- ----------------------------------------------------------------------------
273 -- {Start Of Comments}
274 --
275 -- Description:
276 -- This procedure ensures that parent hierarchy id of the global functional
277 -- node is not updated
278 --
279 -- Pre Conditions:
280 -- g_rec has been populated with details of the values
281 -- from the ins or the upd procedures
282 --
283 -- In Arguments:
284 -- p_parent_hierarchy_id
285
286 -- Post Success:
287 -- Processing continues if the parent hierarchy id is valid.
288 --
289 -- Post Failure:
290 -- An application error is raised if there are any child rows from any of the
291 -- above mentioned tables.
292 --
293 -- {End Of Comments}
294 -- ----------------------------------------------------------------------------
295 procedure chk_parent_hierarchy_id_update
296 (p_parent_hierarchy_id in number
297 )
298 is
299 -- Variables for API Boolean parameters
300 l_proc varchar2(72) := g_package ||'chk_parent_hierarchy_id_update';
301 l_found varchar2(10);
302
303 Begin
304
305 hr_utility.set_location(' Entering:'|| l_proc,10);
306
307 -- check if we are trying to update the parent id of global func node
308 if hr_hrc_shd.g_old_rec.parent_hierarchy_id is null and
309 (nvl(p_parent_hierarchy_id, hr_api.g_number) <>
310 nvl(hr_hrc_shd.g_old_rec.parent_hierarchy_id,hr_api.g_number))
311 then
312 hr_utility.set_location(' Parent hierarchy id of global node updated:'
313 || l_proc,20);
314 fnd_message.set_name( 'PER','PER_449914_HRC_GLBLND_NONUPD');
315 fnd_message.raise_error;
316 end if;
317
318 hr_utility.set_location(' Leaving:' || l_proc,30);
319
320 Exception
321 when app_exception.application_exception then
322 IF hr_multi_message.exception_add
323 (p_associated_column1 =>'HR_KI_HIERARCHIES.PARENT_HIERARCHY_ID'
324 )THEN
325 hr_utility.set_location(' Leaving:'|| l_proc,40);
326 raise;
327 END IF;
328 hr_utility.set_location(' Leaving:'|| l_proc,50);
329
330 End chk_parent_hierarchy_id_update;
331
332 -- ----------------------------------------------------------------------------
333 -- ---------------------< CHK_CYCLIC_HIEARCHY>---------------------------------
334 -- ----------------------------------------------------------------------------
335 -- {Start Of Comments}
336 --
337 -- Description:
338 -- This procedure ensures that a cyclic hierarchy does not occur as a result
339 -- of an update.
340 --
341 -- Pre Conditions:
342 -- g_rec has been populated with details of the values
343 -- from the ins or the upd procedures
344 --
345 -- In Arguments:
346 -- p_parent_hierarchy_id
347
348 -- Post Success:
349 -- Processing continues if no cycle occurs.
350 --
351 -- Post Failure:
352 -- An application error is raised if there are any child rows from any of the
353 -- above mentioned tables.
354 --
355 -- {End Of Comments}
356 -- ----------------------------------------------------------------------------
357 procedure chk_cyclic_hierarhcy
358 (p_hierarchy_id in number,
359 p_parent_hierarchy_id in number
360 )
361 is
362
363 cursor csr_cycle_chk
364 is
365 select
366 'found'
367 from
368 dual
369 where
370 p_hierarchy_id in
371 (
372 select
373 hrc.hierarchy_id
374 from hr_ki_hierarchies hrc
375 connect by prior hrc.parent_hierarchy_id = hrc.hierarchy_id
376 start with hrc.hierarchy_id = p_parent_hierarchy_id
377 );
378 -- Variables for API Boolean parameters
379 l_proc varchar2(72) := g_package ||'chk_parent_hierarchy_id_update';
380 l_found varchar2(10);
381
382 Begin
383
384 hr_utility.set_location(' Entering:'|| l_proc,10);
385
386 -- check if the parent_hierarchy_id that we are updating to will yield a cycle
387 open csr_cycle_chk;
388 fetch csr_cycle_chk into l_found;
389
390 If csr_cycle_chk%FOUND then
391 hr_utility.set_location(' Update will result in a cyclic hierarchy, aborting'
392 || l_proc,20);
393 close csr_cycle_chk;
394 fnd_message.set_name( 'PER','PER_449087_HRC_UPD_CYCLIC');
395 fnd_message.raise_error;
396 End If;
397
398 close csr_cycle_chk;
399
400 hr_utility.set_location(' Leaving:' || l_proc,30);
401
402 Exception
403 when app_exception.application_exception then
404 IF hr_multi_message.exception_add
405 (p_associated_column1 =>'HR_KI_HIERARCHIES.PARENT_HIERARCHY_ID'
406 )THEN
407 hr_utility.set_location(' Leaving:'|| l_proc,40);
408 raise;
409 END IF;
410 hr_utility.set_location(' Leaving:'|| l_proc,50);
411
412 End chk_cyclic_hierarhcy;
413 -- ----------------------------------------------------------------------------
414 -- -----------------------------< CHK_DELETE>----------------------------------
415 -- ----------------------------------------------------------------------------
416 -- {Start Of Comments}
417 --
418 -- Description:
419 -- This procedure ensures that a delete occurs only if there are no child
420 -- rows for a record in hr_ki_hierarchies. The tables that contain child rows
421 -- are hr_ki_hierarchies, hr_ki_hierarchy_node_maps,hr_ki_hierarchies_tl.
422
423 -- Pre Conditions:
424 -- g_rec has been populated with details of the values
425 -- from the ins or the upd procedures
426 --
427 -- In Arguments:
428 -- p_hierarchy_id
429
430 -- Post Success:
431 -- Processing continues if there are no child records.
432 --
433 -- Post Failure:
434 -- An application error is raised if there are any child rows from any of the
435 -- above mentioned tables.
436 --
437 -- {End Of Comments}
438 -- ----------------------------------------------------------------------------
439
440 procedure chk_delete(p_hierarchy_id in varchar2)
441 is
442
443 CURSOR csr_hrc_id is
444 select
445 distinct 'found'
446 From
447 hr_ki_hierarchies hrc
448 where
449 hrc.parent_hierarchy_id = p_hierarchy_id;
450
451 CURSOR csr_hnm_id is
452 select
453 distinct 'found'
454 From
455 hr_ki_hierarchy_node_maps hnm
456 where
457 hnm.hierarchy_id = p_hierarchy_id;
458
459 CURSOR csr_htl_id is
460 select
461 distinct 'found'
462 From
463 hr_ki_hierarchies_tl htl
464 where
465 htl.hierarchy_id = p_hierarchy_id;
466
467 l_found varchar2(30);
468 l_proc varchar2(72) := g_package ||'chk_delete';
469
470 Begin
471
472 hr_utility.set_location(' Entering:' || l_proc,10);
473 open csr_hrc_id;
474 fetch csr_hrc_id into l_found;
475
476 if csr_hrc_id%FOUND then
477 close csr_hrc_id;
478 fnd_message.set_name( 'PER','PER_449917_HRC_HRC_MAIN_EXIST');
479 fnd_message.raise_error;
480 end if;
481
482 close csr_hrc_id;
483
484 open csr_hnm_id;
485 fetch csr_hnm_id into l_found;
486
487 if csr_hnm_id%FOUND then
488 close csr_hnm_id;
489 fnd_message.set_name( 'PER','PER_449918_HRC_HNM_MAIN_EXIST');
490 fnd_message.raise_error;
491 end if;
492
493 close csr_hnm_id;
494
495 open csr_htl_id;
496 fetch csr_htl_id into l_found;
497
498 if csr_htl_id%FOUND then
499 close csr_htl_id;
500 fnd_message.set_name( 'PER','PER_449919_HRC_HTL_MAIN_EXIST');
501 fnd_message.raise_error;
502 end if;
503
504 close csr_htl_id;
505
506 hr_utility.set_location(' Leaving:' || l_proc,20);
507
508 Exception
509 when app_exception.application_exception then
510 IF hr_multi_message.exception_add
511 (p_associated_column1 => 'HR_KI_HIERARCHIES.HIERARCHY_ID'
512 )THEN
513 hr_utility.set_location(' Leaving:'|| l_proc,30);
514 raise;
515 END IF;
516 hr_utility.set_location(' Leaving:'|| l_proc,40);
517
518 End chk_delete;
519 --
520 -- ----------------------------------------------------------------------------
521 -- |---------------------------< insert_validate >----------------------------|
522 -- ----------------------------------------------------------------------------
523 Procedure insert_validate
524 (p_rec in hr_hrc_shd.g_rec_type
525 ) is
526 --
527 l_proc varchar2(72) := g_package||'insert_validate';
528 --
529 Begin
530 hr_utility.set_location('Entering:'||l_proc, 5);
531 --
532 -- Call all supporting business operations
533 --
534 --
535 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
536 chk_hierarchy_key(p_hierarchy_key => p_rec.hierarchy_key);
537 chk_parent_hierarchy_id(p_parent_hierarchy_id => p_rec.parent_hierarchy_id);
538 --
539 hr_utility.set_location(' Leaving:'||l_proc, 10);
540 End insert_validate;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |---------------------------< update_validate >----------------------------|
544 -- ----------------------------------------------------------------------------
545 Procedure update_validate
546 (p_rec in hr_hrc_shd.g_rec_type
547 ) is
548 --
549 l_proc varchar2(72) := g_package||'update_validate';
550 --
551 Begin
552 hr_utility.set_location('Entering:'||l_proc, 5);
553 --
554 -- Call all supporting business operations
555 --
556 --
557 --
558 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
559 --
560 -- Validate Dependent Attributes
561 --
562 chk_non_updateable_args
563 (p_rec => p_rec
564 );
565
566 chk_parent_hierarchy_id_update(p_parent_hierarchy_id => p_rec.parent_hierarchy_id);
567 chk_parent_hierarchy_id(p_parent_hierarchy_id => p_rec.parent_hierarchy_id);
568 chk_cyclic_hierarhcy(p_hierarchy_id => p_rec.hierarchy_id,
569 p_parent_hierarchy_id => p_rec.parent_hierarchy_id
570 );
571 --
572 --
573 hr_utility.set_location(' Leaving:'||l_proc, 10);
574 End update_validate;
575 --
576 -- ----------------------------------------------------------------------------
577 -- |---------------------------< delete_validate >----------------------------|
578 -- ----------------------------------------------------------------------------
579 Procedure delete_validate
580 (p_rec in hr_hrc_shd.g_rec_type
581 ) is
582 --
583 l_proc varchar2(72) := g_package||'delete_validate';
584 --
585 Begin
586 hr_utility.set_location('Entering:'||l_proc, 5);
587 --
588 -- Call all supporting business operations
589 --
590 chk_delete(p_hierarchy_id => p_rec.hierarchy_id);
591 --
592 hr_utility.set_location(' Leaving:'||l_proc, 10);
593 End delete_validate;
594 --
595 end hr_hrc_bus;
[Home] [Help]