1 Package Body hxc_mpc_bus as
2 /* $Header: hxcmpcrhi.pkb 120.2 2005/09/23 08:47:45 nissharm noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_mpc_bus.'; -- Global package name
9
10 g_debug boolean := hr_utility.debug_enabled;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |-----------------------< chk_non_updateable_args >------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- This procedure is used to ensure that non updateable attributes have
19 -- not been updated. If an attribute has been updated an error is generated.
20 --
21 -- Pre Conditions:
22 -- g_old_rec has been populated with details of the values currently in
23 -- the database.
24 --
25 -- In Arguments:
26 -- p_rec has been populated with the updated values the user would like the
27 -- record set to.
28 --
29 -- Post Success:
30 -- Processing continues if all the non updateable attributes have not
31 -- changed.
32 --
33 -- Post Failure:
34 -- An application error is raised if any of the non updatable attributes
35 -- have been altered.
36 --
37 -- {End Of Comments}
38 -- ----------------------------------------------------------------------------
39 Procedure chk_non_updateable_args
40 (p_rec in hxc_mpc_shd.g_rec_type
41 ) IS
42 --
43 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
44 l_error EXCEPTION;
45 l_argument varchar2(30);
46 --
47 Begin
48 --
49 -- Only proceed with the validation if a row exists for the current
50 -- record in the HR Schema.
51 --
52 IF NOT hxc_mpc_shd.api_updating
53 (p_mapping_component_id => p_rec.mapping_component_id
54 ,p_object_version_number => p_rec.object_version_number
55 ) THEN
56 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
57 fnd_message.set_token('PROCEDURE ', l_proc);
58 fnd_message.set_token('STEP ', '5');
59 fnd_message.raise_error;
60 END IF;
61 --
62 -- EDIT_HERE: Add checks to ensure non-updateable args have
63 -- not been updated.
64 --
65 EXCEPTION
66 WHEN l_error THEN
67 hr_api.argument_changed_error
68 (p_api_name => l_proc
69 ,p_argument => l_argument);
70 WHEN OTHERS THEN
71 RAISE;
72 End chk_non_updateable_args;
73 --
74 -- ----------------------------------------------------------------------------
75 -- |-----------------------------< chk_name >---------------------------------|
76 -- ----------------------------------------------------------------------------
77 -- {Start Of Comments}
78 --
79 -- Description:
80 -- This procedure insures a valid mapping component name
81 --
82 -- Pre Conditions:
83 -- None
84 --
85 -- In Arguments:
86 -- name
87 -- mapping_component_id
88 --
89 -- Post Success:
90 -- Processing continues if the mapping component name business rules
91 -- have not been violated
92 --
93 -- Post Failure:
94 -- An application error is raised if the mapping component name is not valid
95 --
96 -- {End Of Comments}
97 -- ----------------------------------------------------------------------------
98 Procedure chk_name
99 (
100 p_name in hxc_mapping_components.name%TYPE,
101 p_mapping_component_id in hxc_mapping_components.mapping_component_id%TYPE
102 ) IS
103 --
104 l_proc varchar2(72);
105 --
106 -- cursor to check mapping name is not duplicated
107 --
108 CURSOR csr_chk_name IS
109 SELECT 'error'
110 FROM sys.dual
111 WHERE EXISTS (
112 SELECT 'x'
113 FROM hxc_mapping_components mpc
114 WHERE mpc.name = p_name
115 AND ( mpc.mapping_component_id <> p_mapping_component_id OR
116 p_mapping_component_id IS NULL ) );
117 --
118 l_error varchar2(5) := NULL;
119 --
120 BEGIN
121 g_debug := hr_utility.debug_enabled;
122
123 if g_debug then
124 l_proc := g_package||'chk_name';
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 end if;
127 --
128 -- check that the mapping component name has been entered
129 --
130 IF p_name IS NULL
131 THEN
132 --
133 hr_utility.set_message(809, 'HXC_0021_MPC_MAPPING_NAME_MAND');
134 hr_utility.raise_error;
135 --
136 END IF;
137 if g_debug then
138 hr_utility.set_location('Processing:'||l_proc, 10);
139 end if;
140 --
141 -- check that mapping component name is unique
142 --
143 OPEN csr_chk_name;
144 FETCH csr_chk_name INTO l_error;
145 CLOSE csr_chk_name;
146 --
147 IF l_error IS NOT NULL
148 THEN
149 --
150 hr_utility.set_message(809, 'HXC_0022_MPC_DUP_MAPPING_NAME');
151 hr_utility.raise_error;
152 --
153 END IF;
154 --
155 if g_debug then
156 hr_utility.set_location('Leaving:'||l_proc, 20);
157 end if;
158 --
159 END chk_name;
160 --
161 -- ----------------------------------------------------------------------------
162 -- |------------------------< chk_bld_blk_info_type_id >----------------------|
163 -- ----------------------------------------------------------------------------
164 -- {Start Of Comments}
165 --
166 -- Description:
167 -- This procedure insures a valid bld blk info type id
168 --
169 -- Pre Conditions:
170 -- None
171 --
172 -- In Arguments:
173 -- bld blk info type id
174 --
175 -- Post Success:
176 -- Processing continues if the bld blk info type id business rules
177 -- have not been violated
178 --
179 -- Post Failure:
180 -- An application error is raised if the bld blk info is not valid
181 --
182 -- {End Of Comments}
183 -- ----------------------------------------------------------------------------
184 Procedure chk_bld_blk_info_type_id
185 (
186 p_bld_blk_info_type_id in hxc_bld_blk_info_type_usages.bld_blk_info_type_id%TYPE
187 , p_segment in hxc_mapping_components.segment%TYPE
188 ) IS
189 --
190 l_proc varchar2(72);
191 --
192 -- cursor to check building block category is valid
193 --
194 CURSOR csr_chk_bbc IS
195 SELECT 'error'
196 FROM sys.dual
197 WHERE NOT EXISTS (
198 SELECT 'x'
199 FROM hxc_bld_blk_info_type_usages bb
200 WHERE bb.bld_blk_info_type_id = p_bld_blk_info_type_id );
201 --
202 l_error varchar2(5) := NULL;
203 --
204 BEGIN
205 g_debug := hr_utility.debug_enabled;
206
207 if g_debug then
208 l_proc := g_package||'chk_bld_blk_info_type_id';
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 end if;
211
212 -- if the bld_blk_category is null and the segment is ATTRIBUTE_CATEGORY
213 -- then no validation needed
214
215 IF ( p_segment = 'ATTRIBUTE_CATEGORY' AND p_bld_blk_info_type_id IS NULL )
216 THEN
217 null;
218 ELSE
219 --
220 -- check that the building block category has been entered
221 --
222 IF p_bld_blk_info_type_id IS NULL
223 THEN
224 --
225 hr_utility.set_message(809, 'HXC_0023_MPC_BLD_BLK_CAT_MAND');
226 hr_utility.raise_error;
227 --
228 END IF;
229 if g_debug then
230 hr_utility.set_location('Processing:'||l_proc, 10);
231 end if;
232 --
233 -- check that building block category is valid
234 --
235 OPEN csr_chk_bbc;
236 FETCH csr_chk_bbc INTO l_error;
237 CLOSE csr_chk_bbc;
238 --
239 IF l_error IS NOT NULL
240 THEN
241 --
242 hr_utility.set_message(809, 'HXC_0024_MPC_BLD_BLK_CAT_INVLD');
243 hr_utility.raise_error;
244 --
245 END IF;
246
247 END IF;
248 --
249 if g_debug then
250 hr_utility.set_location('Leaving:'||l_proc, 20);
251 end if;
252 --
253 END chk_bld_blk_info_type_id;
254 --
255 -- ----------------------------------------------------------------------------
256 -- |-----------------------------< chk_segment >------------------------------|
257 -- ----------------------------------------------------------------------------
258 -- {Start Of Comments}
259 --
260 -- Description:
261 -- This procedure insures a valid segment
262 --
263 -- Pre Conditions:
264 -- None
265 --
266 -- In Arguments:
267 -- bld_blk_info_type_id
268 -- segment
269 --
270 -- Post Success:
271 -- Processing continues if the segment business rules
272 -- have not been violated
273 --
274 -- Post Failure:
275 -- An application error is raised if the segment is not valid
276 --
277 -- {End Of Comments}
278 -- ----------------------------------------------------------------------------
279 Procedure chk_segment
280 (
281 p_bld_blk_info_type_id IN hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE
282 , p_segment in hxc_mapping_components.segment%TYPE
283 ) IS
284 --
285 l_proc varchar2(72);
286
287 -- cursor to check segment is valid
288
289 CURSOR csr_chk_segment IS
290 SELECT 'ok'
291 FROM fnd_descr_flex_column_usages df
292 , hxc_bld_blk_info_types bbit
293 WHERE bbit.bld_blk_info_type_id = p_bld_blk_info_type_id
294 AND df.descriptive_flex_context_code= bbit.bld_blk_info_type
295 AND df.application_id = 809
296 AND df.application_column_name = p_segment;
297
298 l_error varchar2(5) := NULL;
299 --
300 BEGIN
301 g_debug := hr_utility.debug_enabled;
302
303 if g_debug then
304 l_proc := g_package||'chk_segment';
305 hr_utility.set_location('Entering:'||l_proc, 5);
306 end if;
307 --
308 -- check that the segment has been entered
309 --
310 IF p_segment IS NULL
311 THEN
312 --
313 hr_utility.set_message(809, 'HXC_0025_MPC_SEGMENT_MAND');
314 hr_utility.raise_error;
315 --
316 END IF;
317 if g_debug then
318 hr_utility.set_location('Processing:'||l_proc, 10);
319 end if;
320
321 IF ( p_segment <> 'ATTRIBUTE_CATEGORY' )
322 THEN
323 --
324 -- check that segment is valid
325 --
326 OPEN csr_chk_segment;
327 FETCH csr_chk_segment INTO l_error;
328 CLOSE csr_chk_segment;
329 --
330 IF l_error IS NULL
331 THEN
332 --
333 hr_utility.set_message(809, 'HXC_0026_MPC_TYPE_INVALID');
334 hr_utility.raise_error;
335 --
336 END IF;
337 END IF;
338 --
339 if g_debug then
340 hr_utility.set_location('Leaving:'||l_proc, 20);
341 end if;
342 --
343 END chk_segment;
344 --
345 -- ----------------------------------------------------------------------------
346 -- |-----------------------------< chk_composite_key >------------------------|
347 -- ----------------------------------------------------------------------------
348 -- {Start Of Comments}
349 --
350 -- Description:
351 -- This procedure insures a valid composite key based on Field Name, bld blk
352 -- info type and segment
353 --
354 -- Pre Conditions:
355 -- None
356 --
357 -- In Arguments:
358 -- Field Name
359 -- bld blk info type id
360 -- Segment
361 --
362 -- Post Success:
363 -- Processing continues if the composite key is unique
364 --
365 -- Post Failure:
366 -- An application error is raised if the composite is not unique
367 --
368 -- {End Of Comments}
369 -- ----------------------------------------------------------------------------
370 Procedure chk_composite_key
371 (
372 p_object_version_number in hxc_mapping_components.object_version_number%TYPE
373 , p_field_name IN hxc_mapping_components.field_name%TYPE
374 , p_bld_blk_info_type_id IN hxc_mapping_components.bld_blk_info_type_id%TYPE
375 , p_segment IN hxc_mapping_components.segment%TYPE
376 ) IS
377 --
378 l_proc varchar2(72);
379
380 -- cursor to check key not duplicated
381
382 CURSOR csr_chk_key IS
383 SELECT 'error'
384 FROM sys.dual
385 WHERE EXISTS (
386 SELECT 'x'
387 FROM hxc_mapping_components mpc
388 WHERE mpc.field_name = p_field_name
389 AND mpc.bld_blk_info_type_id = p_bld_blk_info_type_id
390 AND mpc.segment = p_segment
391 AND mpc.object_version_number <> NVL( p_object_version_number, -1 ));
392
393 l_error varchar2(5) := NULL;
394 --
395 BEGIN
396 g_debug := hr_utility.debug_enabled;
397
398 if g_debug then
399 l_proc := g_package||'chk_composite_key';
400 hr_utility.set_location('Entering:'||l_proc, 5);
401 end if;
402 --
403
404 -- check that segment is valid
405
406 OPEN csr_chk_key;
407 FETCH csr_chk_key INTO l_error;
408 CLOSE csr_chk_key;
409 --
410 IF l_error IS NOT NULL
411 THEN
412 --
413 hr_utility.set_message(809, 'HXC_0020_MPC_DUP_COMP_KEY');
414 hr_utility.raise_error;
415 --
416 END IF;
417 --
418 if g_debug then
419 hr_utility.set_location('Leaving:'||l_proc, 20);
420 end if;
421 --
422 END chk_composite_key;
423 --
424 -- ----------------------------------------------------------------------------
425 -- |--------------------------< chk_field_name >------------------------------|
426 -- ----------------------------------------------------------------------------
427 -- {Start Of Comments}
428 --
429 -- Description:
430 -- This procedure insures a valid field name
431 --
432 -- Pre Conditions:
433 -- None
434 --
435 -- In Arguments:
436 -- field name
437 --
438 -- Post Success:
439 -- Processing continues if the field name business rules
440 -- have not been violated
441 --
442 -- Post Failure:
443 -- An application error is raised if the segment is not valid
444 --
445 -- {End Of Comments}
446 -- ----------------------------------------------------------------------------
447 Procedure chk_field_name
448 (
449 p_field_name in hxc_mapping_components.field_name%TYPE
450 ) IS
451 --
452 l_proc varchar2(72);
453
454 --
455 BEGIN
456 g_debug := hr_utility.debug_enabled;
457
458 if g_debug then
459 l_proc := g_package||'chk_field_name';
460 hr_utility.set_location('Entering:'||l_proc, 5);
461 end if;
462 --
463 -- check that the field name has been entered
464 --
465 IF p_field_name IS NULL
466 THEN
467 --
468 hr_utility.set_message(809, 'HXC_0027_MPC_FIELD_MAND');
469 hr_utility.raise_error;
470 --
471 END IF;
472 if g_debug then
473 hr_utility.set_location('Processing:'||l_proc, 10);
474 end if;
475 --
476 -- check that the field name is not reserved
477 --
478 IF ( UPPER(p_field_name) IN ( 'RESOURCE_TYPE', 'RESOURCE_ID', 'COMMENT_TEXT', 'COMMENT' ) )
479 THEN
480 --
481 hr_utility.set_message(809, 'HXC_0019_MPC_SYSTEM_FIELD_NAME');
482 hr_utility.raise_error;
483 --
484 END IF;
485 --
486 if g_debug then
487 hr_utility.set_location('Leaving:'||l_proc, 20);
488 end if;
489 --
490 END chk_field_name;
491 --
492 -- ----------------------------------------------------------------------------
493 -- |-----------------------------< chk_delete >-------------------------------|
494 -- ----------------------------------------------------------------------------
495 -- {Start Of Comments}
496 --
497 -- Description:
498 -- This procedure insures referential integrity when deleting a mapping
499 -- component
500 --
501 -- Pre Conditions:
502 -- None
503 --
504 -- In Arguments:
505 -- mapping_component_id
506 --
507 -- Post Success:
508 -- Processing continues if the mapping component is not referenced
509 --
510 -- Post Failure:
511 -- An application error is raised if the mapping component is being used
512 --
513 -- {End Of Comments}
514 -- ----------------------------------------------------------------------------
515 Procedure chk_delete
516 (
517 p_mapping_component_id in hxc_mapping_components.mapping_component_id%TYPE
518 ) IS
519 --
520 l_proc varchar2(72);
521 --
522 -- cursor to check mapping component is not referenced
523 --
524 CURSOR csr_chk_mcu IS
525 SELECT 'error'
526 FROM sys.dual
527 WHERE EXISTS (
528 SELECT 'x'
529 FROM hxc_mapping_comp_usages mcu
530 WHERE mcu.mapping_component_id = p_mapping_component_id );
531 --
532 l_error varchar2(5) := NULL;
533 --
534 BEGIN
535 g_debug := hr_utility.debug_enabled;
536
537 if g_debug then
538 l_proc := g_package||'chk_delete';
539 hr_utility.set_location('Entering:'||l_proc, 5);
540 end if;
541 --
542 -- check that mapping component is not being used
543 --
544 OPEN csr_chk_mcu;
545 FETCH csr_chk_mcu INTO l_error;
546 CLOSE csr_chk_mcu;
547 --
548 if g_debug then
549 hr_utility.set_location('Leaving:'||l_proc, 10);
550 end if;
551 --
552 IF l_error IS NOT NULL
553 THEN
554 --
555 hr_utility.set_message(809, 'HXC_0018_MPC_COMPONENT_USED');
556 hr_utility.raise_error;
557 --
558 END IF;
559 --
560 if g_debug then
561 hr_utility.set_location('Leaving:'||l_proc, 20);
562 end if;
563 --
564 END chk_delete;
565 --
566 -- ----------------------------------------------------------------------------
567 -- |---------------------------< insert_validate >----------------------------|
568 -- ----------------------------------------------------------------------------
569 Procedure insert_validate
570 (p_rec in hxc_mpc_shd.g_rec_type
571 ) is
572 --
573 l_proc varchar2(72);
574 --
575 Begin
576 g_debug := hr_utility.debug_enabled;
577
578 if g_debug then
579 l_proc := g_package||'insert_validate';
580 hr_utility.set_location('Entering:'||l_proc, 5);
581 end if;
582 --
583 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
584 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
585 --
586 -- Call all supporting business operations
587 --
588 chk_name ( p_name => p_rec.name,
589 p_mapping_component_id => p_rec.mapping_component_id );
590 --
591 if g_debug then
592 hr_utility.set_location('Processing:'||l_proc, 10);
593 end if;
594 --
595 chk_bld_blk_info_type_id ( p_bld_blk_info_type_id
596 => p_rec.bld_blk_info_type_id
597 , p_segment => p_rec.segment );
598 --
599 if g_debug then
600 hr_utility.set_location('Processing:'||l_proc, 20);
601 end if;
602 --
603 chk_segment ( p_bld_blk_info_type_id => p_rec.bld_blk_info_type_id
604 , p_segment => p_rec.segment );
605 --
606 if g_debug then
607 hr_utility.set_location('Processing:'||l_proc, 30);
608 end if;
609 --
610 chk_field_name ( p_field_name => p_rec.field_name );
611 --
612 chk_composite_key ( p_object_version_number => p_rec.object_version_number
613 , p_field_name => p_rec.field_name
614 , p_bld_blk_info_type_id => p_rec.bld_blk_info_type_id
615 , p_segment => p_rec.segment );
616 --
617 if g_debug then
618 hr_utility.set_location('Processing:'||l_proc, 40);
619 --
620 hr_utility.set_location('Leaving:'||l_proc, 50);
621 end if;
622 End insert_validate;
623 --
624 -- ----------------------------------------------------------------------------
625 -- |---------------------------< update_validate >----------------------------|
626 -- ----------------------------------------------------------------------------
627 Procedure update_validate
628 (p_rec in hxc_mpc_shd.g_rec_type
629 ) is
630 --
631 l_proc varchar2(72);
632 --
633 Begin
634 g_debug := hr_utility.debug_enabled;
635
636 if g_debug then
637 l_proc := g_package||'update_validate';
638 hr_utility.set_location('Entering:'||l_proc, 5);
639 end if;
640 --
641 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
642 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
643 --
644 -- Call all supporting business operations
645 --
646 -- GPM v115.8
647 chk_delete (p_mapping_component_id => p_rec.mapping_component_id );
648
649 chk_name ( p_name => p_rec.name,
650 p_mapping_component_id => p_rec.mapping_component_id );
651 --
652 if g_debug then
653 hr_utility.set_location('Processing:'||l_proc, 10);
654 end if;
655 --
656 chk_bld_blk_info_type_id ( p_bld_blk_info_type_id
657 => p_rec.bld_blk_info_type_id
658 , p_segment => p_rec.segment );
659 --
660 if g_debug then
661 hr_utility.set_location('Processing:'||l_proc, 20);
662 end if;
663 --
664 chk_segment ( p_bld_blk_info_type_id => p_rec.bld_blk_info_type_id
665 , p_segment => p_rec.segment );
666 --
667 if g_debug then
668 hr_utility.set_location('Processing:'||l_proc, 30);
669 end if;
670 --
671 chk_field_name ( p_field_name => p_rec.field_name );
672 --
673 chk_composite_key ( p_object_version_number => p_rec.object_version_number
674 , p_field_name => p_rec.field_name
675 , p_bld_blk_info_type_id => p_rec.bld_blk_info_type_id
676 , p_segment => p_rec.segment );
677 --
678 if g_debug then
679 hr_utility.set_location('Processing:'||l_proc, 40);
680 end if;
681 --
682 chk_non_updateable_args
683 (p_rec => p_rec
684 );
685 --
686 if g_debug then
687 hr_utility.set_location(' Leaving:'||l_proc, 50);
688 end if;
689 End update_validate;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |---------------------------< delete_validate >----------------------------|
693 -- ----------------------------------------------------------------------------
694 Procedure delete_validate
695 (p_rec in hxc_mpc_shd.g_rec_type
696 ) is
697 --
698 l_proc varchar2(72);
699 --
700 Begin
701 g_debug := hr_utility.debug_enabled;
702
703 if g_debug then
704 l_proc := g_package||'delete_validate';
705 hr_utility.set_location('Entering:'||l_proc, 5);
706 end if;
707 --
708 -- Call all supporting business operations
709 --
710 chk_delete (p_mapping_component_id => p_rec.mapping_component_id );
711 --
712 if g_debug then
713 hr_utility.set_location(' Leaving:'||l_proc, 10);
714 end if;
715 End delete_validate;
716 --
717 end hxc_mpc_bus;