1 Package Body ame_itc_bus as
2 /* $Header: amitcrhi.pkb 120.2 2005/11/22 03:17 santosin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_itc_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< chk_name >--------------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure checks whether the mandatory column name has been
17 -- populated or not.
18 --
19 -- Pre-Requisites:
20 -- None
21 --
22 -- In Parameters:
23 -- p_name
24 --
25 -- Post Success:
26 -- Processing continues if a valid name has been entered.
27 --
28 -- Post Failure:
29 -- An application error is raised if the name is undefined.
30 --
31 -- Access Status:
32 -- Internal Row Handler Use Only.
33 --
34 -- {End Of Comments}
35 -- ----------------------------------------------------------------------------
36 procedure chk_name(p_name in varchar2) IS
37 --
38 l_proc varchar2(72) := g_package || 'CHK_NAME';
39 --
40 Begin
41 hr_utility.set_location('Entering:'||l_proc,10);
42 hr_api.mandatory_arg_error(p_api_name => l_proc
43 ,p_argument => 'NAME'
44 ,p_argument_value => p_name
45 );
46 hr_utility.set_location(' Leaving:'||l_proc,30);
47 exception
48 when app_exception.application_exception then
49 if hr_multi_message.exception_add
50 (p_associated_column1 => 'AME_ITEM_CLASSES.NAME'
51 ) then
52 hr_utility.set_location(' Leaving:'||l_proc, 40);
53 raise;
54 end if;
55 hr_utility.set_location(' Leaving:'||l_proc,50);
56 End chk_name;
57 -- ----------------------------------------------------------------------------
58 -- |------------------------< chk_unique >------------------------------------|
59 -- ----------------------------------------------------------------------------
60 -- {Start Of Comments}
61 --
62 -- Description:
63 -- This procedure ensures that a duplicate item_class_id and name don't
64 -- exist
65 --
66 -- Pre-Requisites:
67 -- None
68 --
69 -- In Parameters:
70 -- p_name
71 -- p_effective_date
72 --
73 -- Post Success:
74 -- Processing continues if a valid record has been entered.
75 --
76 -- Post Failure:
77 -- An application error is raised if entered item_class_id and name are
78 -- not unique
79 --
80 -- Access Status:
81 -- Internal Row Handler Use Only.
82 --
83 -- {End Of Comments}
84 -- ----------------------------------------------------------------------------
85 procedure chk_unique(p_name in varchar2
86 ,p_effective_date in date
87 ) IS
88 --
89 cursor csr_name is
90 select null
91 from ame_item_classes
92 where name = p_name
93 and p_effective_date between start_date and
94 nvl(end_date - ame_util.oneSecond, p_effective_date);
95 --
96 l_proc varchar2(72) := g_package || 'CHK_UNIQUE';
97 l_dummy varchar2(1);
98 --
99 Begin
100 hr_utility.set_location('Entering:'||l_proc,10);
101 --
102 open csr_name;
103 fetch csr_name into l_dummy;
104 if csr_name%found then
105 close csr_name;
106 fnd_message.set_name('PER','AME_400764_ITC_DUP_NAME');
107 fnd_message.raise_error;
108 end if;
109 close csr_name;
110 --
111 hr_utility.set_location(' Leaving:'||l_proc,30);
112 exception
113 when app_exception.application_exception then
114 if hr_multi_message.exception_add
115 (p_associated_column1 => 'AME_ITEM_CLASSES.UNIQUE'
116 ) then
117 hr_utility.set_location(' Leaving:'||l_proc, 40);
118 raise;
119 end if;
120 hr_utility.set_location(' Leaving:'||l_proc,50);
121 End chk_unique;
122 --
123 -- ----------------------------------------------------------------------------
124 -- |-----------------------------< CHK_DELETE >-------------------------------|
125 -- ----------------------------------------------------------------------------
126 -- {Start Of Comments}
127 --
128 -- Description:
129 -- This procedure is used to check whether the given item_class is seeded.
130 --
131 -- Pre-Requisites:
132 -- None
133 --
134 -- In Parameters:
135 -- p_condition_id
136 --
137 -- Post Success:
138 -- Processing continues if no child records for the said condition are found.
139 --
140 -- Post Failure:
141 -- An application error is raised if valid child records exist for the given
142 -- condition_id.
143 --
144 -- Access Status:
145 -- Internal Row Handler Use Only.
146 --
147 -- {End Of Comments}
148 -- ----------------------------------------------------------------------------
149 procedure chk_delete(p_item_class_id in number
150 ,p_effective_date in date
151 ) IS
152 --
153 cursor csr_isSeeded is
154 select null
155 from ame_item_classes
156 where item_class_id = p_item_class_id
157 and p_effective_date between start_date and
158 nvl(end_date - ame_util.oneSecond, p_effective_date)
159 and ame_utility_pkg.is_seed_user(created_by) = ame_util.seededDataCreatedById;
160 l_proc varchar2(72) := g_package || 'CHK_DELETE';
161 l_key varchar2(1);
162 --
163 Begin
164 hr_utility.set_location('Entering:'||l_proc,10);
165 -- Check if the item_class is seeded.
166 open csr_isSeeded;
167 fetch csr_isSeeded into l_key;
168 if(csr_isSeeded%found) then
169 close csr_isSeeded;
170 fnd_message.set_name('PER','AME_400765_SEEDED_ITC_CNT_DEL');
171 fnd_message.raise_error;
172 end if;
173 close csr_isSeeded;
174 hr_utility.set_location(' Leaving:'||l_proc,30);
175 exception
176 when app_exception.application_exception then
177 if hr_multi_message.exception_add
178 (p_associated_column1 => 'AME_ITEM_CLASS.DELETE'
179 ) then
180 hr_utility.set_location(' Leaving:'||l_proc, 40);
181 raise;
182 end if;
183 hr_utility.set_location(' Leaving:'||l_proc,50);
184 End chk_delete;
185 --
186 -- ----------------------------------------------------------------------------
187 -- |-----------------------< chk_non_updateable_args >------------------------|
188 -- ----------------------------------------------------------------------------
189 -- {Start Of Comments}
190 --
191 -- Description:
192 -- This procedure is used to ensure that non updateable attributes have
193 -- not been updated. If an attribute has been updated an error is generated.
194 --
195 -- Pre Conditions:
196 -- g_old_rec has been populated with details of the values currently in
197 -- the database.
198 --
199 -- In Arguments:
200 -- p_rec has been populated with the updated values the user would like the
201 -- record set to.
202 --
203 -- Post Success:
204 -- Processing continues if all the non updateable attributes have not
205 -- changed.
206 --
207 -- Post Failure:
208 -- An application error is raised if any of the non updatable attributes
209 -- have been altered.
210 --
211 -- {End Of Comments}
212 -- ----------------------------------------------------------------------------
213 Procedure chk_non_updateable_args
214 (p_effective_date in date
215 ,p_rec in ame_itc_shd.g_rec_type
216 ) IS
217 --
218 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
219 --
220 Begin
221 --
222 -- Only proceed with the validation if a row exists for the current
223 -- record in the HR Schema.
224 --
225 IF NOT ame_itc_shd.api_updating
226 (p_item_class_id => p_rec.item_class_id
227 ,p_effective_date => p_effective_date
228 ,p_object_version_number => p_rec.object_version_number
229 ) THEN
230 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
231 fnd_message.set_token('PROCEDURE ', l_proc);
232 fnd_message.set_token('STEP ', '5');
233 fnd_message.raise_error;
234 END IF;
235 --
236 -- NAME is non-updateable
237 --
238 if nvl(p_rec.name, hr_api.g_varchar2) <>
239 nvl(ame_itc_shd.g_old_rec.name, hr_api.g_varchar2) then
240 hr_api.argument_changed_error
241 (p_api_name => l_proc
242 ,p_argument => 'NAME'
243 ,p_base_table => ame_itc_shd.g_tab_nam
244 );
245 end if;
246 End chk_non_updateable_args;
247 --
248 -- ----------------------------------------------------------------------------
249 -- |--------------------------< dt_update_validate >--------------------------|
250 -- ----------------------------------------------------------------------------
251 -- {Start Of Comments}
252 --
253 -- Description:
254 -- This procedure is used for referential integrity of datetracked
255 -- parent entities when a datetrack update operation is taking place
256 -- and where there is no cascading of update defined for this entity.
257 --
258 -- Prerequisites:
259 -- This procedure is called from the update_validate.
260 --
261 -- In Parameters:
262 --
263 -- Post Success:
264 -- Processing continues.
265 --
266 -- Post Failure:
267 --
268 -- Developer Implementation Notes:
269 -- This procedure should not need maintenance unless the HR Schema model
270 -- changes.
271 --
272 -- Access Status:
273 -- Internal Row Handler Use Only.
274 --
275 -- {End Of Comments}
276 -- ----------------------------------------------------------------------------
277 Procedure dt_update_validate
278 (p_datetrack_mode in varchar2
279 ,p_validation_start_date in date
280 ,p_validation_end_date in date
281 ) Is
282 --
283 l_proc varchar2(72) := g_package||'dt_update_validate';
284 --
285 Begin
286 --
287 -- Ensure that the p_datetrack_mode argument is not null
288 --
289 hr_api.mandatory_arg_error
290 (p_api_name => l_proc
291 ,p_argument => 'datetrack_mode'
292 ,p_argument_value => p_datetrack_mode
293 );
294 --
295 -- Mode will be valid, as this is checked at the start of the upd.
296 --
297 -- Ensure the arguments are not null
298 --
299 hr_api.mandatory_arg_error
300 (p_api_name => l_proc
301 ,p_argument => 'validation_start_date'
302 ,p_argument_value => p_validation_start_date
303 );
304 --
305 /*hr_api.mandatory_arg_error
306 (p_api_name => l_proc
307 ,p_argument => 'validation_end_date'
308 ,p_argument_value => p_validation_end_date
309 );*/
310 --
311 Exception
312 When Others Then
313 --
314 -- An unhandled or unexpected error has occurred which
315 -- we must report
316 --
317 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
318 fnd_message.set_token('PROCEDURE', l_proc);
319 fnd_message.set_token('STEP','15');
320 fnd_message.raise_error;
321 End dt_update_validate;
322 --
323 -- ----------------------------------------------------------------------------
324 -- |--------------------------< dt_delete_validate >--------------------------|
325 -- ----------------------------------------------------------------------------
326 -- {Start Of Comments}
327 --
328 -- Description:
329 -- This procedure is used for referential integrity of datetracked
330 -- child entities when either a datetrack DELETE or ZAP is in operation
331 -- and where there is no cascading of delete defined for this entity.
332 -- For the datetrack mode of DELETE or ZAP we must ensure that no
333 -- datetracked child rows exist between the validation start and end
334 -- dates.
335 --
336 -- Prerequisites:
337 -- This procedure is called from the delete_validate.
338 --
339 -- In Parameters:
340 --
341 -- Post Success:
342 -- Processing continues.
343 --
344 -- Post Failure:
345 -- If a row exists by determining the returning Boolean value from the
346 -- generic dt_api.rows_exist function then we must supply an error via
347 -- the use of the local exception handler l_rows_exist.
348 --
349 -- Developer Implementation Notes:
350 -- This procedure should not need maintenance unless the HR Schema model
351 -- changes.
352 --
353 -- Access Status:
354 -- Internal Row Handler Use Only.
355 --
356 -- {End Of Comments}
357 -- ----------------------------------------------------------------------------
358 Procedure dt_delete_validate
359 (p_item_class_id in number
360 ,p_datetrack_mode in varchar2
361 ,p_validation_start_date in date
362 ,p_validation_end_date in date
363 ) Is
364 --
365 l_proc varchar2(72) := g_package||'dt_delete_validate';
366 --
367 Begin
368 --
369 -- Ensure that the p_datetrack_mode argument is not null
370 --
371 hr_api.mandatory_arg_error
372 (p_api_name => l_proc
373 ,p_argument => 'datetrack_mode'
374 ,p_argument_value => p_datetrack_mode
375 );
376 --
377 -- Only perform the validation if the datetrack mode is either
378 -- DELETE or ZAP
379 --
380 If (p_datetrack_mode = hr_api.g_delete or
381 p_datetrack_mode = hr_api.g_zap) then
382 --
383 --
384 -- Ensure the arguments are not null
385 --
386 hr_api.mandatory_arg_error
387 (p_api_name => l_proc
388 ,p_argument => 'validation_start_date'
389 ,p_argument_value => p_validation_start_date
390 );
391 --
392 /*hr_api.mandatory_arg_error
393 (p_api_name => l_proc
394 ,p_argument => 'validation_end_date'
395 ,p_argument_value => p_validation_end_date
396 );*/
397 --
398 hr_api.mandatory_arg_error
399 (p_api_name => l_proc
400 ,p_argument => 'item_class_id'
401 ,p_argument_value => p_item_class_id
402 );
403 --
404 ame_itc_shd.child_rows_exist
405 (p_item_class_id => p_item_class_id
406 ,p_start_date => p_validation_start_date
407 ,p_end_date => p_validation_end_date);
408 --
409 --
410 End If;
411 --
412 Exception
413 When Others Then
414 --
415 -- An unhandled or unexpected error has occurred which
416 -- we must report
417 --
418 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
419 fnd_message.set_token('PROCEDURE', l_proc);
420 fnd_message.set_token('STEP','15');
421 fnd_message.raise_error;
422 --
423 End dt_delete_validate;
424 --
425 -- ----------------------------------------------------------------------------
426 -- |---------------------------< insert_validate >----------------------------|
427 -- ----------------------------------------------------------------------------
428 Procedure insert_validate
429 (p_rec in ame_itc_shd.g_rec_type
430 ,p_effective_date in date
431 ,p_datetrack_mode in varchar2
432 ,p_validation_start_date in date
433 ,p_validation_end_date in date
434 ) is
435 --
436 l_proc varchar2(72) := g_package||'insert_validate';
437 --
438 Begin
439 hr_utility.set_location('Entering:'||l_proc, 5);
440 --
441 -- Validate Dependent Attributes
442 --
443 chk_name(p_name => p_rec.name);
444
445 chk_unique(p_name => p_rec.name
446 ,p_effective_date => p_effective_date
447 );
448
449 hr_utility.set_location(' Leaving:'||l_proc, 10);
450 End insert_validate;
451 --
452 -- ----------------------------------------------------------------------------
453 -- |---------------------------< update_validate >----------------------------|
454 -- ----------------------------------------------------------------------------
455 Procedure update_validate
456 (p_rec in ame_itc_shd.g_rec_type
457 ,p_effective_date in date
458 ,p_datetrack_mode in varchar2
459 ,p_validation_start_date in date
460 ,p_validation_end_date in date
461 ) is
462 --
463 l_proc varchar2(72) := g_package||'update_validate';
464 --
465 Begin
466 hr_utility.set_location('Entering:'||l_proc, 5);
467 --
468 -- Validate Dependent Attributes
469 --
470 -- Call the datetrack update integrity operation
471 --
472 dt_update_validate
473 (p_datetrack_mode => p_datetrack_mode
474 ,p_validation_start_date => p_validation_start_date
475 ,p_validation_end_date => p_validation_end_date
476 );
477 --
478 chk_non_updateable_args
479 (p_effective_date => p_effective_date
480 ,p_rec => p_rec
481 );
482 --
483 --
484 hr_utility.set_location(' Leaving:'||l_proc, 10);
485 End update_validate;
486 --
487 -- ----------------------------------------------------------------------------
488 -- |---------------------------< delete_validate >----------------------------|
489 -- ----------------------------------------------------------------------------
490 Procedure delete_validate
491 (p_rec in ame_itc_shd.g_rec_type
492 ,p_effective_date in date
493 ,p_datetrack_mode in varchar2
494 ,p_validation_start_date in date
495 ,p_validation_end_date in date
496 ) is
497 --
498 l_proc varchar2(72) := g_package||'delete_validate';
499 --
500 Begin
501 hr_utility.set_location('Entering:'||l_proc, 5);
502 --
503 -- Call all supporting business operations
504 --
505 -- Check for seeded data
506 --
507 chk_delete(p_item_class_id => p_rec.item_class_id
508 ,p_effective_date => p_effective_date);
509 --
510 dt_delete_validate
511 (p_datetrack_mode => p_datetrack_mode
512 ,p_validation_start_date => p_validation_start_date
513 ,p_validation_end_date => p_validation_end_date
514 ,p_item_class_id => p_rec.item_class_id
515 );
516 --
517 hr_utility.set_location(' Leaving:'||l_proc, 10);
518 End delete_validate;
519 --
520 end ame_itc_bus;
[Home] [Help]