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