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