1 Package Body ame_man_bus as
2 /* $Header: ammanrhi.pkb 120.5 2005/11/22 03:18 santosin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_man_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_man_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_man_shd.api_updating
50 (p_attribute_id => p_rec.attribute_id
51 ,p_action_type_id => p_rec.action_type_id
52 ,p_effective_date => p_effective_date
53 ,p_object_version_number => p_rec.object_version_number
54 ) THEN
55 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
56 fnd_message.set_token('PROCEDURE ', l_proc);
57 fnd_message.set_token('STEP ', '5');
58 fnd_message.raise_error;
59 END IF;
60 --
61 -- EDIT_HERE: Add checks to ensure non-updateable args have
62 -- not been updated.
63 --
64 End chk_non_updateable_args;
65 --
66 -- ---------------------------------------------------------------------------
67 -- |----------------------<chk_action_type_id >--------------------------|
68 -- ---------------------------------------------------------------------------
69 --
70 -- {Start Of Comments}
71 --
72 -- Description:
73 -- Validates that the action_type_id is a foreign key to ame_action_types.action_type_id.
74 --
75 -- Prerequisites:
76 -- None.
77 --
78 -- In Parameters:
79 -- p_action_type_id
80 -- p_effective_date
81 --
82 -- Post Success:
83 -- Processing continues.
84 --
85 -- Post Failure:
86 -- Log the error message.
87 --
88 -- Developer Implementation Notes:
89 -- None.
90 --
91 -- Access Status:
92 -- Internal Row Handler Use Only.
93 --
94 -- {End Of Comments}
95 -- ----------------------------------------------------------------------------
96 procedure chk_action_type_id
97 (p_effective_date in date,
98 p_action_type_id in number) is
99 l_proc varchar2(72) := g_package||'chk_action_type_id';
100 tempCount integer;
101 cursor c_sel1 is
102 select null
103 from ame_action_types
104 where
105 action_type_id = p_action_type_id and
106 p_effective_date between start_date and
107 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
108 l_exists varchar2(1);
109 begin
110 open c_sel1;
111 fetch c_sel1 into l_exists;
112 if c_sel1%notfound then
113 close c_sel1;
114 fnd_message.set_name('PER','AME_400575_ACT_TYP_NOT_EXIST');
115 -- Need message here
116 fnd_message.raise_error;
117 end if;
118 close c_sel1;
119 exception
120 when app_exception.application_exception then
121 if hr_multi_message.exception_add
122 (p_associated_column1 => 'ACTION_TYPE_ID') then
123 hr_utility.set_location(' Leaving:'|| l_proc, 50);
124 raise;
125 end if;
126 hr_utility.set_location(' Leaving:'|| l_proc, 60);
127 end chk_action_type_id;
128 --
129 --
130 -- ---------------------------------------------------------------------------
131 -- |----------------------<chk_attribute_id>--------------------------|
132 -- ---------------------------------------------------------------------------
133 --
134 -- {Start Of Comments}
135 --
136 -- Description:
137 -- Validates that the attribute_id is a foreign key to ame_attributes.attribute_id.
138 --
139 -- Prerequisites:
140 -- None.
141 --
142 -- In Parameters:
143 -- p_attribute_id
144 -- p_effective_date
145 --
146 -- Post Success:
147 -- Processing continues.
148 --
149 -- Post Failure:
150 -- Log the error message.
151 --
152 -- Developer Implementation Notes:
153 -- None.
154 --
155 -- Access Status:
156 -- Internal Row Handler Use Only.
157 --
158 -- {End Of Comments}
159 -- ----------------------------------------------------------------------------
160 procedure chk_attribute_id
161 (p_effective_date in date,
162 p_attribute_id in number) is
163 l_proc varchar2(72) := g_package||'chk_attribute_id';
164 tempCount integer;
165 cursor c_sel1 is
166 select null
167 from ame_attributes
168 where
169 attribute_id = p_attribute_id and
170 p_effective_date between start_date and
171 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
172 l_exists varchar2(1);
173 begin
174 open c_sel1;
175 fetch c_sel1 into l_exists;
176 if c_sel1%notfound then
177 close c_sel1;
178 -- Invalid attribute id
179 fnd_message.set_name('PER','AME_400473_INV_ATTRIBUTE_ID');
180 -- Need message here
181 fnd_message.raise_error;
182 end if;
183 close c_sel1;
184 exception
185 when app_exception.application_exception then
186 if hr_multi_message.exception_add
187 (p_associated_column1 => 'ATTRIBUTE_ID') then
188 hr_utility.set_location(' Leaving:'|| l_proc, 50);
189 raise;
190 end if;
191 hr_utility.set_location(' Leaving:'|| l_proc, 60);
192 end chk_attribute_id;
193 --
194
195 --------------------------------------------------------------------------
196 --------------------------------------------------------------------------
197 ---------------------------< chk_man_attribute >------------
198 ------------------------------------------------------------------------
199 procedure chk_man_attribute
200 (p_effective_date in date,
201 p_attribute_id in number ) is
202 l_proc varchar2(72) := g_package||'chk_man_attribute';
203 cursor c_sell is
204 select null
205 from ame_mandatory_attributes
206 where attribute_id = p_attribute_id and
207 action_type_id = -1 and
208 p_effective_date between start_date and
209 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
210 l_exists varchar2(1);
211 begin
212 open c_sell;
213 fetch c_sell into l_exists;
214 if c_sell%found then
215 --It is a mandatory attribute.should not be added
216 fnd_message.set_name('PER','AME_400792_ATTR_IS_MAN');
217 fnd_message.raise_error;
218
219 end if;
220 close c_sell;
221 exception
222 when app_exception.application_exception then
223 if hr_multi_message.exception_add
224 (p_associated_column1 => 'ATTRIBUTE_ID') then
225 hr_utility.set_location(' Leaving:'|| l_proc, 50);
226 raise;
227 end if;
228 hr_utility.set_location(' Leaving:'|| l_proc, 60);
229
230 end chk_man_attribute;
231 --------------------------------------------------------------------------
232 --------------------------------------------------------------------------
233 ---------------------------< chk_seeded_action_type >------------
234 ------------------------------------------------------------------------
235 procedure chk_seeded_action_type
236 (p_effective_date in date,
237 p_action_type_id in number ) is
238 l_proc varchar2(72) := g_package||'chk_seeded_action_type';
239
240 cursor c_sell is
241 select created_by
242 from ame_action_types
243 where action_type_id = p_action_type_id and
244 p_effective_date between start_date and
245 nvl(end_date - ame_util.oneSecond, p_effective_date) ;
246 l_created_by number;
247 begin
248 open c_sell;
249 fetch c_sell into l_created_by;
250 if ame_utility_pkg.is_seed_user(l_created_by) = ame_util.seededDataCreatedById and
251 ame_utility_pkg.check_seeddb = 'N'
252 then
253 fnd_message.set_name('PER','AME_400793_SEED_ATY_NO_UPD');
254 fnd_message.raise_error;
255 end if;
256 close c_sell;
257 exception
258 when app_exception.application_exception then
259 if hr_multi_message.exception_add
260 (p_associated_column1 => 'ACTION_TYPE_ID') then
261 hr_utility.set_location(' Leaving:'|| l_proc, 50);
262 raise;
263 end if;
264 hr_utility.set_location(' Leaving:'|| l_proc, 60);
265
266 end chk_seeded_action_type;
267 -------------------------------------------------------------------------
268 -- ---------------------------------------------------------------------------
269 -- |----------------------< chk_delete >--------------------------|
270 -- ---------------------------------------------------------------------------
271 --
272 -- {Start Of Comments}
273 --
274 -- Description:
275 -- check that 1) A required attribute of a seeded action type cannot be deleted.
276 --
277 -- Prerequisites:
278 -- None.
279 --
280 -- In Parameters:
281 -- p_action_type_id
282 --
283 -- Post Success:
284 -- Processing continues.
285 --
286 -- Post Failure:
287 -- Log the error message.
288 --
289 -- Developer Implementation Notes:
290 -- None.
291 --
292 -- Access Status:
293 -- Internal Row Handler Use Only.
294 --
295 -- {End Of Comments}
296 -- ----------------------------------------------------------------------------
297 procedure chk_delete
298 (p_action_type_id in number) is
299 l_proc varchar2(72) := g_package||'chk_delete';
300 cursor c_sel1 Is
301 select null
302 from ame_action_types
303 where
304 action_type_id = p_action_type_id and
305 ame_utility_pkg.is_seed_user(created_by) = ame_util.seededDataCreatedById and
306 ame_utility_pkg.check_seeddb = 'N';
307 l_exists varchar2(1);
308 begin
309 open c_sel1;
310 fetch c_sel1 into l_exists;
311 if c_sel1%found then
312 close c_sel1;
313 -- AT MESSAGE
314 -- A required attribute of a seeded action type cannot be deleted.
315 fnd_message.set_name('PER','AME_400599_SD_REQ_ATT_CN_DEL');
316 fnd_message.raise_error;
317 end if;
318 close c_sel1;
319 exception
320 when app_exception.application_exception then
321 if hr_multi_message.exception_add
322 (p_associated_column1 => 'ACTION_TYPE_ID') then
323 hr_utility.set_location(' Leaving:'|| l_proc, 50);
324 raise;
325 end if;
326 hr_utility.set_location(' Leaving:'|| l_proc, 60);
327 end chk_delete;
328 -- ----------------------------------------------------------------------------
329 -- |--------------------------< dt_update_validate >--------------------------|
330 -- ----------------------------------------------------------------------------
331 -- {Start Of Comments}
332 --
333 -- Description:
334 -- This procedure is used for referential integrity of datetracked
335 -- parent entities when a datetrack update operation is taking place
336 -- and where there is no cascading of update defined for this entity.
337 --
338 -- Prerequisites:
339 -- This procedure is called from the update_validate.
340 --
341 -- In Parameters:
342 --
343 -- Post Success:
344 -- Processing continues.
345 --
346 -- Post Failure:
347 --
348 -- Developer Implementation Notes:
349 -- This procedure should not need maintenance unless the HR Schema model
350 -- changes.
351 --
352 -- Access Status:
353 -- Internal Row Handler Use Only.
354 --
355 -- {End Of Comments}
356 -- ----------------------------------------------------------------------------
357 Procedure dt_update_validate
358 (p_datetrack_mode in varchar2
359 ,p_validation_start_date in date
360 ,p_validation_end_date in date
361 ) Is
362 --
363 l_proc varchar2(72) := g_package||'dt_update_validate';
364 --
365 Begin
366 --
367 -- Ensure that the p_datetrack_mode argument is not null
368 --
369 hr_api.mandatory_arg_error
370 (p_api_name => l_proc
371 ,p_argument => 'datetrack_mode'
372 ,p_argument_value => p_datetrack_mode
373 );
374 --
375 -- Mode will be valid, as this is checked at the start of the upd.
376 --
377 -- Ensure the arguments are not null
378 --
379 hr_api.mandatory_arg_error
380 (p_api_name => l_proc
381 ,p_argument => 'validation_start_date'
382 ,p_argument_value => p_validation_start_date
383 );
384 --
385 /*hr_api.mandatory_arg_error
386 (p_api_name => l_proc
387 ,p_argument => 'validation_end_date'
388 ,p_argument_value => p_validation_end_date
389 );*/
390 --
391 Exception
392 When Others Then
393 --
394 -- An unhandled or unexpected error has occurred which
395 -- we must report
396 --
397 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
398 fnd_message.set_token('PROCEDURE', l_proc);
399 fnd_message.set_token('STEP','15');
400 fnd_message.raise_error;
401 End dt_update_validate;
402 --
403 -- ----------------------------------------------------------------------------
404 -- |--------------------------< dt_delete_validate >--------------------------|
405 -- ----------------------------------------------------------------------------
406 -- {Start Of Comments}
407 --
408 -- Description:
409 -- This procedure is used for referential integrity of datetracked
410 -- child entities when either a datetrack DELETE or ZAP is in operation
411 -- and where there is no cascading of delete defined for this entity.
412 -- For the datetrack mode of DELETE or ZAP we must ensure that no
413 -- datetracked child rows exist between the validation start and end
414 -- dates.
415 --
416 -- Prerequisites:
417 -- This procedure is called from the delete_validate.
418 --
419 -- In Parameters:
420 --
421 -- Post Success:
422 -- Processing continues.
423 --
424 -- Post Failure:
425 -- If a row exists by determining the returning Boolean value from the
426 -- generic dt_api.rows_exist function then we must supply an error via
427 -- the use of the local exception handler l_rows_exist.
428 --
429 -- Developer Implementation Notes:
430 -- This procedure should not need maintenance unless the HR Schema model
431 -- changes.
432 --
433 -- Access Status:
434 -- Internal Row Handler Use Only.
435 --
436 -- {End Of Comments}
437 -- ----------------------------------------------------------------------------
438 Procedure dt_delete_validate
439 (p_attribute_id in number
443 ,p_validation_end_date in date
440 ,p_action_type_id in number
441 ,p_datetrack_mode in varchar2
442 ,p_validation_start_date in date
444 ) Is
445 --
446 l_proc varchar2(72) := g_package||'dt_delete_validate';
447 --
448 Begin
449 --
450 -- Ensure that the p_datetrack_mode argument is not null
451 --
452 hr_api.mandatory_arg_error
453 (p_api_name => l_proc
454 ,p_argument => 'datetrack_mode'
455 ,p_argument_value => p_datetrack_mode
456 );
457 --
458 -- Only perform the validation if the datetrack mode is either
459 -- DELETE or ZAP
460 --
461 If (p_datetrack_mode = hr_api.g_delete or
462 p_datetrack_mode = hr_api.g_zap) then
463 --
464 --
465 -- Ensure the arguments are not null
466 --
467 hr_api.mandatory_arg_error
468 (p_api_name => l_proc
469 ,p_argument => 'validation_start_date'
470 ,p_argument_value => p_validation_start_date
471 );
472 --
473 /*hr_api.mandatory_arg_error
474 (p_api_name => l_proc
475 ,p_argument => 'validation_end_date'
476 ,p_argument_value => p_validation_end_date
477 );*/
478 --
479 hr_api.mandatory_arg_error
480 (p_api_name => l_proc
481 ,p_argument => 'attribute_id'
482 ,p_argument_value => p_attribute_id
483 );
484 --
485 --
486 --
487 End If;
488 --
489 Exception
490 When Others Then
491 --
492 -- An unhandled or unexpected error has occurred which
493 -- we must report
494 --
495 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
496 fnd_message.set_token('PROCEDURE', l_proc);
497 fnd_message.set_token('STEP','15');
498 fnd_message.raise_error;
499 --
500 End dt_delete_validate;
501 --
502 -- ----------------------------------------------------------------------------
503 -- |---------------------------< insert_validate >----------------------------|
504 -- ----------------------------------------------------------------------------
505 Procedure insert_validate
506 (p_rec in ame_man_shd.g_rec_type
507 ,p_effective_date in date
508 ,p_datetrack_mode in varchar2
509 ,p_validation_start_date in date
510 ,p_validation_end_date in date
511 ) is
512 --
513 l_proc varchar2(72) := g_package||'insert_validate';
514 --
515 Begin
516 hr_utility.set_location('Entering:'||l_proc, 5);
517 --
518 -- Validate Dependent Attributes
519 -- Action Type Id
520 chk_action_type_id(p_effective_date => p_effective_date,
521 p_action_type_id => p_rec.action_type_id);
522 --
523 -- Attribute Type Id
524 chk_attribute_id(p_effective_date => p_effective_date,
525 p_attribute_id => p_rec.attribute_id);
526
527 ---check whether the attribute is a mandatory attribute.
528 ----mandatory attributes can not be added as a required attribute using create_ame_req_attribute
529 chk_man_attribute(p_effective_date => p_effective_date,
530 p_attribute_id => p_rec.attribute_id);
531
532 ---check whether the action type is seeded or not
533 -- Seeded Action Types should not be updateble using procedure 'ame_action_api.create_ame_req_attribute'
534 --But we should allow for the Ame developer responsibility
535 chk_seeded_action_type(p_effective_date => p_effective_date,
536 p_action_type_id => p_rec.action_type_id);
537 --
538 hr_utility.set_location(' Leaving:'||l_proc, 10);
539 End insert_validate;
540 --
541 -- ----------------------------------------------------------------------------
542 -- |---------------------------< update_validate >----------------------------|
543 -- ----------------------------------------------------------------------------
544 Procedure update_validate
545 (p_rec in ame_man_shd.g_rec_type
546 ,p_effective_date in date
547 ,p_datetrack_mode in varchar2
548 ,p_validation_start_date in date
549 ,p_validation_end_date in date
550 ) is
551 --
552 l_proc varchar2(72) := g_package||'update_validate';
553 --
554 Begin
555 hr_utility.set_location('Entering:'||l_proc, 5);
556 --
557 -- Validate Dependent Attributes
558 --
559 -- Call the datetrack update integrity operation
560 --
561 dt_update_validate
562 (p_datetrack_mode => p_datetrack_mode
563 ,p_validation_start_date => p_validation_start_date
564 ,p_validation_end_date => p_validation_end_date
565 );
566 --
567 chk_non_updateable_args
568 (p_effective_date => p_effective_date
569 ,p_rec => p_rec
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 ame_man_shd.g_rec_type
581 ,p_effective_date in date
582 ,p_datetrack_mode in varchar2
583 ,p_validation_start_date in date
584 ,p_validation_end_date in date
585 ) is
586 --
587 l_proc varchar2(72) := g_package||'delete_validate';
588 --
589 Begin
590 hr_utility.set_location('Entering:'||l_proc, 5);
591 --
595 dt_delete_validate
592 -- Call all supporting business operations
593 chk_delete(p_action_type_id => p_rec.action_type_id);
594 --
596 (p_datetrack_mode => p_datetrack_mode
597 ,p_validation_start_date => p_validation_start_date
598 ,p_validation_end_date => p_validation_end_date
599 ,p_attribute_id => p_rec.attribute_id
600 ,p_action_type_id => p_rec.action_type_id
601 );
602 --
603 hr_utility.set_location(' Leaving:'||l_proc, 10);
604 End delete_validate;
605 --
606 end ame_man_bus;