1 Package Body ame_apu_bus as
2 /* $Header: amapurhi.pkb 120.3 2005/11/22 03:13 santosin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_apu_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_apu_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_apu_shd.api_updating
50 (p_approver_type_id => p_rec.approver_type_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_action_type_id in number,
98 p_effective_date in date) 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 -- Message: Invalid action type id
115 fnd_message.set_name('PER','AME_400575_ACT_TYP_NOT_EXIST');
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_approver_type_id>--------------------------|
132 -- ---------------------------------------------------------------------------
133 --
134 -- {Start Of Comments}
135 --
136 -- Description:
137 -- Validates that the approver_type_id is a foreign key to
138 -- ame_approver_types.approver_type_id
139 --
140 -- Prerequisites:
141 -- None.
142 --
143 -- In Parameters:
144 -- p_approver_type_id
145 -- p_effective_date
146 --
147 -- Post Success:
148 -- Processing continues.
149 --
150 -- Post Failure:
151 -- Log the error message.
152 --
153 -- Developer Implementation Notes:
154 -- None.
155 --
156 -- Access Status:
157 -- Internal Row Handler Use Only.
158 --
159 -- {End Of Comments}
160 -- ----------------------------------------------------------------------------
161 procedure chk_approver_type_id
162 (p_approver_type_id in number,
163 p_action_type_id in number,
164 p_effective_date in date) is
165 cursor c_sel1 is
166 select null
167 from ame_approver_types
168 where
169 approver_type_id = p_approver_type_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 l_proc varchar2(72) := g_package||'chk_approver_type_id';
174 l_temp_count number;
175 begin
176 open c_sel1;
177 fetch c_sel1 into l_exists;
178 -- Verify if approver type id is valid (-1 = any approver type).
179 if(c_sel1%notfound) then
180 if(p_approver_type_id = ame_util.anyApproverType) then
181 select count(*) into l_temp_count
182 from ame_approver_type_usages
183 where
184 action_type_id = p_action_type_id and
185 p_effective_date between start_date and
186 nvl(end_date - ame_util.oneSecond, p_effective_date);
187 if(l_temp_count <> 0) then
188 close c_sel1;
189 -- AT Message
190 -- Message: An approver id already exists. Need to delete before.
191 fnd_message.set_name('PER','AME_400607_APPR_TYP_USG_EXISTS');
192 fnd_message.raise_error;
193 end if;
194 else
195 close c_sel1;
196 -- AT Message
197 -- Message: Invalid approver type id
198 fnd_message.set_name('PER','AME_400469_INV_APPROVER_TYPE');
199 fnd_message.raise_error;
200 end if;
201 end if;
202 close c_sel1;
203 exception
204 when app_exception.application_exception then
205 if hr_multi_message.exception_add
206 (p_associated_column1 => 'APPROVER_TYPE_ID') then
207 hr_utility.set_location(' Leaving:'|| l_proc, 50);
208 raise;
209 end if;
210 hr_utility.set_location(' Leaving:'|| l_proc, 60);
211 end chk_approver_type_id;
212 --
213 -- ---------------------------------------------------------------------------
214 -- |----------------------< chk_delete >--------------------------|
215 -- ---------------------------------------------------------------------------
216 --
217 -- {Start Of Comments}
218 --
219 -- Description:
220 -- check that 1) An approver type usage of a seeded action type cannot be deleted.
221 --
222 -- Prerequisites:
223 -- None.
224 --
225 -- In Parameters:
226 -- p_action_type_id
227 --
228 -- Post Success:
229 -- Processing continues.
230 --
231 -- Post Failure:
232 -- Log the error message.
233 --
234 -- Developer Implementation Notes:
235 -- None.
236 --
237 -- Access Status:
238 -- Internal Row Handler Use Only.
239 --
240 -- {End Of Comments}
241 -- ----------------------------------------------------------------------------
242 procedure chk_delete
243 (p_action_type_id in number) is
244 l_proc varchar2(72) := g_package||'chk_delete';
245 cursor c_sel1 Is
246 select null
247 from ame_action_types
248 where
249 ame_utility_pkg.check_seeddb = 'N' and
250 action_type_id = p_action_type_id and
251 ame_utility_pkg.is_seed_user(created_by) = ame_util.seededDataCreatedById;
252 l_exists varchar2(1);
253 begin
254 open c_sel1;
255 fetch c_sel1 into l_exists;
256 if c_sel1%found then
257 close c_sel1;
258 -- AT MESSAGE
259 -- An approver type usage of a seeded action type cannot be deleted.
260 fnd_message.set_name('PER','AME_400596_SD_APRTYPUSG_CN_DEL');
261 fnd_message.raise_error;
262 end if;
263 close c_sel1;
264 exception
265 when app_exception.application_exception then
266 if hr_multi_message.exception_add
267 (p_associated_column1 => 'ACTION_TYPE_ID') then
268 hr_utility.set_location(' Leaving:'|| l_proc, 50);
269 raise;
270 end if;
271 hr_utility.set_location(' Leaving:'|| l_proc, 60);
272 end chk_delete;
273 -- ----------------------------------------------------------------------------
274 -- |--------------------------< dt_update_validate >--------------------------|
275 -- ----------------------------------------------------------------------------
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- This procedure is used for referential integrity of datetracked
280 -- parent entities when a datetrack update operation is taking place
281 -- and where there is no cascading of update defined for this entity.
282 --
283 -- Prerequisites:
284 -- This procedure is called from the update_validate.
285 --
286 -- In Parameters:
287 --
288 -- Post Success:
289 -- Processing continues.
290 --
291 -- Post Failure:
292 --
293 -- Developer Implementation Notes:
294 -- This procedure should not need maintenance unless the HR Schema model
295 -- changes.
296 --
297 -- Access Status:
298 -- Internal Row Handler Use Only.
299 --
300 -- {End Of Comments}
301 -- ----------------------------------------------------------------------------
302 Procedure dt_update_validate
303 (p_datetrack_mode in varchar2
304 ,p_validation_start_date in date
305 ,p_validation_end_date in date
306 ) Is
307 --
308 l_proc varchar2(72) := g_package||'dt_update_validate';
309 --
310 Begin
311 --
312 -- Ensure that the p_datetrack_mode argument is not null
313 --
314 hr_api.mandatory_arg_error
315 (p_api_name => l_proc
316 ,p_argument => 'datetrack_mode'
317 ,p_argument_value => p_datetrack_mode
318 );
319 --
320 -- Mode will be valid, as this is checked at the start of the upd.
321 --
322 -- Ensure the arguments are not null
323 --
324 hr_api.mandatory_arg_error
325 (p_api_name => l_proc
326 ,p_argument => 'validation_start_date'
327 ,p_argument_value => p_validation_start_date
328 );
329 --
330 /*hr_api.mandatory_arg_error
331 (p_api_name => l_proc
332 ,p_argument => 'validation_end_date'
333 ,p_argument_value => p_validation_end_date
334 );*/
335 --
336 Exception
337 When Others Then
338 --
339 -- An unhandled or unexpected error has occurred which
340 -- we must report
341 --
342 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
343 fnd_message.set_token('PROCEDURE', l_proc);
344 fnd_message.set_token('STEP','15');
345 fnd_message.raise_error;
346 End dt_update_validate;
347 --
348 -- ----------------------------------------------------------------------------
349 -- |--------------------------< dt_delete_validate >--------------------------|
350 -- ----------------------------------------------------------------------------
351 -- {Start Of Comments}
352 --
353 -- Description:
354 -- This procedure is used for referential integrity of datetracked
355 -- child entities when either a datetrack DELETE or ZAP is in operation
356 -- and where there is no cascading of delete defined for this entity.
357 -- For the datetrack mode of DELETE or ZAP we must ensure that no
358 -- datetracked child rows exist between the validation start and end
359 -- dates.
360 --
361 -- Prerequisites:
362 -- This procedure is called from the delete_validate.
363 --
364 -- In Parameters:
365 --
366 -- Post Success:
367 -- Processing continues.
368 --
369 -- Post Failure:
370 -- If a row exists by determining the returning Boolean value from the
371 -- generic dt_api.rows_exist function then we must supply an error via
372 -- the use of the local exception handler l_rows_exist.
373 --
374 -- Developer Implementation Notes:
375 -- This procedure should not need maintenance unless the HR Schema model
376 -- changes.
377 --
378 -- Access Status:
379 -- Internal Row Handler Use Only.
380 --
381 -- {End Of Comments}
382 -- ----------------------------------------------------------------------------
383 Procedure dt_delete_validate
384 (p_approver_type_id in number
385 ,p_action_type_id in number
386 ,p_datetrack_mode in varchar2
387 ,p_validation_start_date in date
388 ,p_validation_end_date in date
389 ) Is
390 --
391 l_proc varchar2(72) := g_package||'dt_delete_validate';
392 --
393 Begin
394 --
395 -- Ensure that the p_datetrack_mode argument is not null
396 --
397 hr_api.mandatory_arg_error
398 (p_api_name => l_proc
399 ,p_argument => 'datetrack_mode'
400 ,p_argument_value => p_datetrack_mode
401 );
402 --
403 -- Only perform the validation if the datetrack mode is either
404 -- DELETE or ZAP
405 --
406 If (p_datetrack_mode = hr_api.g_delete or
407 p_datetrack_mode = hr_api.g_zap) then
408 --
409 --
410 -- Ensure the arguments are not null
411 --
412 hr_api.mandatory_arg_error
413 (p_api_name => l_proc
414 ,p_argument => 'validation_start_date'
415 ,p_argument_value => p_validation_start_date
416 );
417 --
418 /*hr_api.mandatory_arg_error
419 (p_api_name => l_proc
420 ,p_argument => 'validation_end_date'
421 ,p_argument_value => p_validation_end_date
422 );*/
423 --
424 hr_api.mandatory_arg_error
425 (p_api_name => l_proc
426 ,p_argument => 'approver_type_id'
427 ,p_argument_value => p_approver_type_id
428 );
429 --
430 --
431 --
432 End If;
433 --
434 Exception
435 When Others Then
436 --
437 -- An unhandled or unexpected error has occurred which
438 -- we must report
439 --
440 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
441 fnd_message.set_token('PROCEDURE', l_proc);
442 fnd_message.set_token('STEP','15');
443 fnd_message.raise_error;
444 --
445 End dt_delete_validate;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |---------------------------< insert_validate >----------------------------|
449 -- ----------------------------------------------------------------------------
450 Procedure insert_validate
451 (p_rec in ame_apu_shd.g_rec_type
452 ,p_effective_date in date
453 ,p_datetrack_mode in varchar2
454 ,p_validation_start_date in date
455 ,p_validation_end_date in date
456 ) is
457 --
458 l_proc varchar2(72) := g_package||'insert_validate';
459 --
460 Begin
461 hr_utility.set_location('Entering:'||l_proc, 5);
462 --
463 -- Validate Dependent Attributes
464 -- Action Type Id
465 chk_action_type_id(p_effective_date => p_effective_date,
466 p_action_type_id => p_rec.action_type_id);
467 --
468 -- Approver Type Id
469 chk_approver_type_id(p_effective_date => p_effective_date,
470 p_approver_type_id => p_rec.approver_type_id,
471 p_action_type_id => p_rec.action_type_id);
472 --
473 hr_utility.set_location(' Leaving:'||l_proc, 10);
474 End insert_validate;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |---------------------------< update_validate >----------------------------|
478 -- ----------------------------------------------------------------------------
479 Procedure update_validate
480 (p_rec in ame_apu_shd.g_rec_type
481 ,p_effective_date in date
482 ,p_datetrack_mode in varchar2
483 ,p_validation_start_date in date
484 ,p_validation_end_date in date
485 ) is
486 --
487 l_proc varchar2(72) := g_package||'update_validate';
488 --
489 Begin
490 hr_utility.set_location('Entering:'||l_proc, 5);
491 --
492 -- Validate Dependent Attributes
493 --
494 -- Call the datetrack update integrity operation
495 --
496 dt_update_validate
497 (p_datetrack_mode => p_datetrack_mode
498 ,p_validation_start_date => p_validation_start_date
499 ,p_validation_end_date => p_validation_end_date
500 );
501 --
502 chk_non_updateable_args
503 (p_effective_date => p_effective_date
504 ,p_rec => p_rec
505 );
506 --
507 --
508 hr_utility.set_location(' Leaving:'||l_proc, 10);
509 End update_validate;
510 --
511 -- ----------------------------------------------------------------------------
512 -- |---------------------------< delete_validate >----------------------------|
513 -- ----------------------------------------------------------------------------
514 Procedure delete_validate
515 (p_rec in ame_apu_shd.g_rec_type
516 ,p_effective_date in date
517 ,p_datetrack_mode in varchar2
518 ,p_validation_start_date in date
519 ,p_validation_end_date in date
520 ) is
521 --
522 l_proc varchar2(72) := g_package||'delete_validate';
523 --
524 Begin
525 hr_utility.set_location('Entering:'||l_proc, 5);
526 --
527 chk_delete(p_action_type_id => p_rec.action_type_id);
528 -- Call all supporting business operations
529 --
530 dt_delete_validate
531 (p_datetrack_mode => p_datetrack_mode
532 ,p_validation_start_date => p_validation_start_date
533 ,p_validation_end_date => p_validation_end_date
534 ,p_approver_type_id => p_rec.approver_type_id
535 ,p_action_type_id => p_rec.action_type_id
536 );
537 --
538 hr_utility.set_location(' Leaving:'||l_proc, 10);
539 End delete_validate;
540 --
541 end ame_apu_bus;
[Home] [Help]