1 Package Body ame_apt_bus as
2 /* $Header: amaptrhi.pkb 120.1 2006/04/21 08:44 avarri noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := 'ame_apt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< chk_non_updateable_args >------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure is used to ensure that non updateable attributes have
17 -- not been updated. If an attribute has been updated an error is generated.
18 --
19 -- Pre Conditions:
20 -- g_old_rec has been populated with details of the values currently in
21 -- the database.
22 --
23 -- In Arguments:
24 -- p_rec has been populated with the updated values the user would like the
25 -- record set to.
26 --
27 -- Post Success:
28 -- Processing continues if all the non updateable attributes have not
29 -- changed.
30 --
31 -- Post Failure:
32 -- An application error is raised if any of the non updatable attributes
33 -- have been altered.
34 --
35 -- {End Of Comments}
36 -- ----------------------------------------------------------------------------
37 Procedure chk_non_updateable_args
38 (p_effective_date in date
39 ,p_rec in ame_apt_shd.g_rec_type
40 ) IS
41 --
42 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
43 --
44 Begin
45 --
46 -- Only proceed with the validation if a row exists for the current
47 -- record in the HR Schema.
48 --
49 IF NOT ame_apt_shd.api_updating
50 (p_approver_type_id => p_rec.approver_type_id
51 ,p_effective_date => p_effective_date
52 ,p_object_version_number => p_rec.object_version_number
53 ) THEN
54 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
55 fnd_message.set_token('PROCEDURE ', l_proc);
56 fnd_message.set_token('STEP ', '5');
57 fnd_message.raise_error;
58 END IF;
59 --
60 -- NAME is non-updateable
61 --
62 if nvl(p_rec.orig_system, hr_api.g_varchar2) <>
63 nvl(ame_apt_shd.g_old_rec.orig_system, hr_api.g_varchar2) then
64 hr_api.argument_changed_error
65 (p_api_name => l_proc
66 ,p_argument => 'orig_system'
67 ,p_base_table => ame_apt_shd.g_tab_nam
68 );
69 end if;
70 End chk_non_updateable_args;
71 --
72 -- ----------------------------------------------------------------------------
73 -- |------------------------< chk_orig_system >-------------------------------|
74 -- ----------------------------------------------------------------------------
75 -- {Start Of Comments}
76 --
77 -- Description:
78 -- This procedure checks whether the mandatory column orig_system has been
79 -- populated or not.
80 --
81 -- Pre-Requisites:
82 -- None
83 --
84 -- In Parameters:
85 -- p_orig_system
86 --
87 -- Post Success:
88 -- Processing continues if a valid orig_system has been entered.
89 --
90 -- Post Failure:
91 -- An application error is raised if the orig_system is undefined.
92 --
93 -- Access Status:
94 -- Internal Row Handler Use Only.
95 --
96 -- {End Of Comments}
97 -- ------------------------------------------------------------------------
98 procedure chk_orig_system
99 (p_orig_system in varchar2) IS
100
101 l_proc varchar2(72) := g_package||'chk_orig_system';
102 origsystem varchar2(50);
103 Cursor chk_exist_orig_system(p_orig_system in varchar2) is
104 SELECT lookup_code
105 from FND_LOOKUPS
106 where lookup_type like 'FND_WF_ORIG_SYSTEMS'
107 and lookup_code = p_orig_system
108 and rownum <2 ;
109 begin
110 hr_api.mandatory_arg_error
111 (p_api_name => l_proc
112 ,p_argument => 'orig_system'
113 ,p_argument_value => p_orig_system
114 );
115 open chk_exist_orig_system(p_orig_system);
116 fetch chk_exist_orig_system into origsystem;
117 if chk_exist_orig_system%notfound then
118 close chk_exist_orig_system ;
119 fnd_message.set_name('PER','AME_400806_INV_ORIG_SYSTEM');
120 -- RAISE_APPLICATION_ERROR( -20201,'INVALID ORIG SYSTEM ');
121 fnd_message.raise_error;
122 end if;
123 close chk_exist_orig_system ;
124 exception
125 when app_exception.application_exception then
126 if hr_multi_message.exception_add
127 (p_associated_column1 => 'ame_approver_types.orig_system') then
128 raise;
129 end if;
130 hr_utility.set_location(' Leaving:'|| l_proc, 60);
131 end chk_orig_system ;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |------------------------< chk_unique >------------------------------------|
135 -- ----------------------------------------------------------------------------
136 -- {Start Of Comments}
137 --
138 -- Description:
139 -- This procedure ensures that a duplicate approver_type_id and orig_system don't
140 -- exist
141 --
142 -- Pre-Requisites:
143 -- None
144 --
145 -- In Parameters:
146 -- p_orig_system
147 -- p_effective_date
148 --
149 -- Post Success:
150 -- Processing continues if a valid record has been entered.
151 --
152 -- Post Failure:
153 -- An application error is raised if entered approver_type_id and orig_system
154 -- are not unique
155 --
156 -- Access Status:
157 -- Internal Row Handler Use Only.
158 --
159 -- {End Of Comments}
160 -- ----------------------------------------------------------------------------
161 procedure chk_unique(p_orig_system in varchar2
162 ,p_effective_date in date
163 ) IS
164 --
165 cursor csr_name is
166 select null
167 from ame_approver_types
168 where orig_system = p_orig_system
169 and p_effective_date between start_date and
170 nvl(end_date - ame_util.oneSecond, p_effective_date);
171 --
172 l_proc varchar2(72) := g_package || 'CHK_UNIQUE';
173 l_dummy varchar2(1);
174 --
175 Begin
176 hr_utility.set_location('Entering:'||l_proc,10);
177 --
178 open csr_name;
179 fetch csr_name into l_dummy;
180 if csr_name%found then
181 close csr_name;
182 fnd_message.set_name('PER','AME_400805_DUP_APPROVER_TYPE');
183 fnd_message.raise_error;
184 end if;
185 close csr_name;
186 --
187 hr_utility.set_location(' Leaving:'||l_proc,30);
188 exception
189 when app_exception.application_exception then
190 if hr_multi_message.exception_add
191 (p_associated_column1 => 'ame_approver_types.UNIQUE'
192 ) then
193 hr_utility.set_location(' Leaving:'||l_proc, 40);
194 raise;
195 end if;
196 hr_utility.set_location(' Leaving:'||l_proc,50);
197 End chk_unique;
198 --
199 -- ----------------------------------------------------------------------------
200 -- |-----------------------------< CHK_DELETE >-------------------------------|
201 -- ----------------------------------------------------------------------------
202 -- {Start Of Comments}
203 --
204 -- Description:
205 --
206 -- check that 1) Seeded approvertypes types cannot be deleted.
207 -- 2) Approver types cannot have attributes.
208 -- 3) Approver types cannot have approver group menbers.
209 -- 4) Approver types cannot have conditions.
210 -- 5) Approver types cannot have substitution actions
211 --
212 --
213 -- Pre-Requisites:
214 -- None
215 --
216 -- In Parameters:
217 -- p_approver_type_id
218 -- p_orig_system
219 --
220 -- Post Success:
221 -- Processing continues if no child records for the said condition are found.
222 --
223 -- Post Failure:
224 -- An application error is raised if valid child records exist for the given
225 -- condition_id.
226 --
227 -- Access Status:
228 -- Internal Row Handler Use Only.
229 --
230 -- {End Of Comments}
231 -- ----------------------------------------------------------------------------
232 procedure chk_delete(p_approver_type_id in number
233 ,p_orig_system in varchar2
234 ,p_effective_date in date
235 ) IS
236 l_proc varchar2(72) := g_package || 'CHK_DELETE';
237 l_key varchar2(1);
238 --
239
240 cursor csr_isSeeded is
241 select null
242 from ame_approver_types
243 where approver_type_id = p_approver_type_id
244 and p_effective_date between start_date
245 and nvl(end_date - ame_util.oneSecond, p_effective_date)
246 and ame_utility_pkg.is_seed_user(created_by) = ame_util.seededDataCreatedById;
247 --
248 cursor c_sel1 is
249 select null
250 from ame_attributes
251 where approver_type_id = p_approver_type_id
252 and p_effective_date between start_date
253 and nvl(end_date - ame_util.oneSecond, p_effective_date);
254 --
255 cursor c_sel2 is
256 select null
257 from ame_approver_type_usages
258 where approver_type_id = p_approver_type_id
259 and p_effective_date between start_date
260 and nvl(end_date - ame_util.oneSecond, p_effective_date);
261 --
262 cursor c_sel3 is
263 select null
264 from AME_APPROVAL_GROUP_MEMBERS
265 where orig_system = p_orig_system;
266 --
267 Cursor c_sel4 is
268 select null
269 from AME_CONDITIONS cond
270 ,WF_ROLES wfr
271 where cond.parameter_two = wfr.name
272 and wfr.orig_system = p_orig_system
273 and p_effective_date between cond.start_date
274 and nvl(cond.end_date - ame_util.oneSecond, p_effective_date);
275 --
276 Cursor c_sel5 is
277 select null
278 from AME_ACTIONS actions
279 ,WF_ROLES wfr
280 ,ame_action_types act
281 where actions.parameter = wfr.name
282 and wfr.orig_system = p_orig_system
283 and act.name ='substitution'
284 and p_effective_date between actions.start_date
285 and nvl(actions.end_date - ame_util.oneSecond, p_effective_date);
286 l_exists varchar2(1);
287 begin
288 open csr_isSeeded;
289 fetch csr_isSeeded into l_key;
290 if(csr_isSeeded%found) then
291 -- close csr_isSeeded;
292 fnd_message.set_name('PER','AME_400807_SEED_APT_DEL');
293 -- fnd_message.raise_error;
294 hr_multi_message.add;
295 end if;
296 close csr_isSeeded;
297 --
298 -- AME_ATTRIBUTES
299 --
300 open c_sel1;
301 fetch c_sel1 into l_exists;
302 if c_sel1%found then
303 -- close c_sel1;
304 -- AT MESSAGE
305 -- An attributee usage(s) still exists. You must first delete the attribute usage(s)
306 -- before deleting the approver type
307 fnd_message.set_name('PER','AME_400809_APT_ATTR_EXISTS');
308 -- fnd_message.raise_error;
309 hr_multi_message.add;
310 end if;
311 close c_sel1;
312 --
313 -- AME_APPROVER_TYPE_USAGE
314 --
315 /* open c_sel2;
316 fetch c_sel2 into l_exists;
317 if c_sel2%found then
318 -- close c_sel2;
319 -- AT MESSAGE
320 -- An approver type usage(s) still exists. You must first delete the approver type usage(s)
321 -- before deleting the approver type
322 fnd_message.set_name('PER','AME_400612_APPR_USG_EXISTS');
323 -- fnd_message.raise_error;
324 hr_multi_message.add;
325 end if;
326 close c_sel2; */
327 --
328 -- AME_APPROVAL_GROUP_MEMBERS
329 --
330 open c_sel3;
331 fetch c_sel3 into l_exists;
332 if c_sel3%found then
333 -- close c_sel3;
334 -- AT MESSAGE
335 -- An apporver group member usage(s) still exists. You must first delete the approver group member usage(s)
336 -- before deleting the approver type
337 fnd_message.set_name('PER','AME_400810_APT_APG_EXISTS');
338 -- fnd_message.raise_error;
339 hr_multi_message.add;
340 end if;
341 close c_sel3;
342 --
343 -- AME_CONDITIONS
344 --
345 open c_sel4;
346 fetch c_sel4 into l_exists;
347 if c_sel4%found then
348 -- close c_sel4;
349 -- AT MESSAGE
350 -- A condition usage(s) still exists. You must first delete the condition usage(s)
351 -- before deleting the approver type
352 fnd_message.set_name('PER','AME_400808_APT_COND_EXISTS');
353 -- fnd_message.raise_error;
354 hr_multi_message.add;
355 end if;
356 close c_sel4;
357 --
358 -- AME_ACTIONS
359 --
360 open c_sel5;
361 fetch c_sel5 into l_exists;
362 if c_sel5%found then
363 -- close c_sel5;
364 -- AT MESSAGE
365 -- A condition usage(s) still exists. You must first delete the condition usage(s)
366 -- before deleting the approver type
367 fnd_message.set_name('PER','AME_400811_APT_ACT_EXISTS');
368 hr_multi_message.add;
369 -- fnd_message.raise_error;
370 end if;
371 close c_sel5;
372 hr_utility.set_location(' Leaving:'||l_proc,30);
373 exception
374 when app_exception.application_exception then
375 if hr_multi_message.exception_add
376 (p_associated_column1 => 'AME_APPROVER_TYPE.DELETE'
377 ) then
378 hr_utility.set_location(' Leaving:'||l_proc, 40);
379 raise;
380 end if;
381 hr_utility.set_location(' Leaving:'||l_proc,50);
382 End chk_delete;
383 --
384 -- ----------------------------------------------------------------------------
385 -- |--------------------------< dt_update_validate >--------------------------|
386 -- ----------------------------------------------------------------------------
387 -- {Start Of Comments}
388 --
389 -- Description:
390 -- This procedure is used for referential integrity of datetracked
391 -- parent entities when a datetrack update operation is taking place
392 -- and where there is no cascading of update defined for this entity.
393 --
394 -- Prerequisites:
395 -- This procedure is called from the update_validate.
396 --
397 -- In Parameters:
398 --
399 -- Post Success:
400 -- Processing continues.
401 --
402 -- Post Failure:
403 --
404 -- Developer Implementation Notes:
405 -- This procedure should not need maintenance unless the HR Schema model
406 -- changes.
407 --
408 -- Access Status:
409 -- Internal Row Handler Use Only.
410 --
411 -- {End Of Comments}
412 -- ----------------------------------------------------------------------------
413 Procedure dt_update_validate
414 (p_datetrack_mode in varchar2
415 ,p_validation_start_date in date
416 ,p_validation_end_date in date
417 ) Is
418 --
419 l_proc varchar2(72) := g_package||'dt_update_validate';
420 --
421 Begin
422 --
423 -- Ensure that the p_datetrack_mode argument is not null
424 --
425 hr_api.mandatory_arg_error
426 (p_api_name => l_proc
427 ,p_argument => 'datetrack_mode'
428 ,p_argument_value => p_datetrack_mode
429 );
430 --
431 -- Mode will be valid, as this is checked at the start of the upd.
432 --
433 -- Ensure the arguments are not null
434 --
435 hr_api.mandatory_arg_error
436 (p_api_name => l_proc
437 ,p_argument => 'validation_start_date'
438 ,p_argument_value => p_validation_start_date
439 );
440 --
441 /*hr_api.mandatory_arg_error
442 (p_api_name => l_proc
443 ,p_argument => 'validation_end_date'
444 ,p_argument_value => p_validation_end_date
445 );*/
446 --
447 Exception
448 When Others Then
449 --
450 -- An unhandled or unexpected error has occurred which
451 -- we must report
452 --
453 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
454 fnd_message.set_token('PROCEDURE', l_proc);
455 fnd_message.set_token('STEP','15');
456 fnd_message.raise_error;
457 End dt_update_validate;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |--------------------------< dt_delete_validate >--------------------------|
461 -- ----------------------------------------------------------------------------
462 -- {Start Of Comments}
463 --
464 -- Description:
465 -- This procedure is used for referential integrity of datetracked
466 -- child entities when either a datetrack DELETE or ZAP is in operation
467 -- and where there is no cascading of delete defined for this entity.
468 -- For the datetrack mode of DELETE or ZAP we must ensure that no
469 -- datetracked child rows exist between the validation start and end
470 -- dates.
471 --
472 -- Prerequisites:
473 -- This procedure is called from the delete_validate.
474 --
475 -- In Parameters:
476 --
477 -- Post Success:
478 -- Processing continues.
479 --
480 -- Post Failure:
481 -- If a row exists by determining the returning Boolean value from the
482 -- generic dt_api.rows_exist function then we must supply an error via
483 -- the use of the local exception handler l_rows_exist.
484 --
485 -- Developer Implementation Notes:
486 -- This procedure should not need maintenance unless the HR Schema model
487 -- changes.
488 --
489 -- Access Status:
490 -- Internal Row Handler Use Only.
491 --
492 -- {End Of Comments}
493 -- ----------------------------------------------------------------------------
494 Procedure dt_delete_validate
495 (p_approver_type_id in number
496 ,p_datetrack_mode in varchar2
497 ,p_validation_start_date in date
498 ,p_validation_end_date in date
499 ) Is
500 --
501 l_proc varchar2(72) := g_package||'dt_delete_validate';
502 --
503 Begin
504 --
505 -- Ensure that the p_datetrack_mode argument is not null
506 --
507 hr_api.mandatory_arg_error
508 (p_api_name => l_proc
509 ,p_argument => 'datetrack_mode'
510 ,p_argument_value => p_datetrack_mode
511 );
512 --
513 -- Only perform the validation if the datetrack mode is either
514 -- DELETE or ZAP
515 --
516 If (p_datetrack_mode = hr_api.g_delete or
517 p_datetrack_mode = hr_api.g_zap) then
518 --
519 --
520 -- Ensure the arguments are not null
521 --
522 hr_api.mandatory_arg_error
523 (p_api_name => l_proc
524 ,p_argument => 'validation_start_date'
525 ,p_argument_value => p_validation_start_date
526 );
527 --
528 /*hr_api.mandatory_arg_error
529 (p_api_name => l_proc
530 ,p_argument => 'validation_end_date'
531 ,p_argument_value => p_validation_end_date
532 );*/
533 --
534 hr_api.mandatory_arg_error
535 (p_api_name => l_proc
536 ,p_argument => 'approver_type_id'
537 ,p_argument_value => p_approver_type_id
538 );
539 --
540 /*
541 ame_apt_shd.child_rows_exist
542 (p_approver_type_id => p_approver_type_id
543 ,p_orig_system => p_orig_system
544 ,p_start_date => p_validation_start_date
545 ,p_end_date => p_validation_end_date);
546 --
547 --
548 */
549 --
550 End If;
551 --
552 Exception
553 When Others Then
554 --
555 -- An unhandled or unexpected error has occurred which
556 -- we must report
557 --
558 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
559 fnd_message.set_token('PROCEDURE', l_proc);
560 fnd_message.set_token('STEP','15');
561 fnd_message.raise_error;
562 --
563 End dt_delete_validate;
564 --
565 -- ----------------------------------------------------------------------------
566 -- |---------------------------< insert_validate >----------------------------|
567 -- ----------------------------------------------------------------------------
568 Procedure insert_validate
569 (p_rec in ame_apt_shd.g_rec_type
570 ,p_effective_date in date
571 ,p_datetrack_mode in varchar2
572 ,p_validation_start_date in date
573 ,p_validation_end_date in date
574 ) is
575 --
576 l_proc varchar2(72) := g_package||'insert_validate';
577 --
578 Begin
579 hr_utility.set_location('Entering:'||l_proc, 5);
580 --
581 -- Validate Dependent Attributes
582 --
583 chk_orig_system(p_orig_system => p_rec.orig_system);
584
585 chk_unique(p_orig_system => p_rec.orig_system
586 ,p_effective_date => p_effective_date
587 );
588 hr_utility.set_location(' Leaving:'||l_proc, 10);
589 End insert_validate;
590 --
591 -- ----------------------------------------------------------------------------
592 -- |---------------------------< update_validate >----------------------------|
593 -- ----------------------------------------------------------------------------
594 Procedure update_validate
595 (p_rec in ame_apt_shd.g_rec_type
596 ,p_effective_date in date
597 ,p_datetrack_mode in varchar2
598 ,p_validation_start_date in date
599 ,p_validation_end_date in date
600 ) is
601 --
602 l_proc varchar2(72) := g_package||'update_validate';
603 --
604 Begin
605 hr_utility.set_location('Entering:'||l_proc, 5);
606 --
607 -- Validate Dependent Attributes
608 --
609 -- Call the datetrack update integrity operation
610 --
611 dt_update_validate
612 (p_datetrack_mode => p_datetrack_mode
613 ,p_validation_start_date => p_validation_start_date
614 ,p_validation_end_date => p_validation_end_date
615 );
616 --
617 chk_non_updateable_args
618 (p_effective_date => p_effective_date
619 ,p_rec => p_rec
620 );
621 --
622 --
623 hr_utility.set_location(' Leaving:'||l_proc, 10);
624 End update_validate;
625 --
626 -- ----------------------------------------------------------------------------
627 -- |---------------------------< delete_validate >----------------------------|
628 -- ----------------------------------------------------------------------------
629 Procedure delete_validate
630 (p_rec in ame_apt_shd.g_rec_type
631 ,p_effective_date in date
632 ,p_datetrack_mode in varchar2
633 ,p_validation_start_date in date
634 ,p_validation_end_date in date
635 ) is
636 --
637 l_proc varchar2(72) := g_package||'delete_validate';
638 l_orig_system varchar2(100);
639 --
640 cursor csr_origSystem is
641 select orig_system
642 from ame_approver_types
643 where approver_type_id = p_rec.approver_type_id
644 and p_effective_date between start_date
645 and nvl(end_date - ame_util.oneSecond, p_effective_date);
646 Begin
647 hr_utility.set_location('Entering:'||l_proc, 5);
648 --
649 -- Call all supporting business operations
650 --
651 -- Check for seeded data
652 --
653 open csr_origSystem;
654 fetch csr_origSystem into l_orig_system;
655 close csr_origSystem;
656
657 chk_delete(p_approver_type_id => p_rec.approver_type_id
658 ,p_orig_system => l_orig_system
659 ,p_effective_date => p_effective_date);
660 --
661 dt_delete_validate
662 (p_datetrack_mode => p_datetrack_mode
663 ,p_validation_start_date => p_validation_start_date
664 ,p_validation_end_date => p_validation_end_date
665 ,p_approver_type_id => p_rec.approver_type_id
666 );
667 --
668 hr_utility.set_location(' Leaving:'||l_proc, 10);
669 End delete_validate;
670 --
671 end ame_apt_bus;
[Home] [Help]