[Home] [Help]
PACKAGE BODY: APPS.AME_CONDITION_API
Source
1 Package Body AME_CONDITION_API as
2 /* $Header: amconapi.pkb 120.0 2005/09/02 03:55 mbocutt noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'AME_CONDITION_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< IS_STRING_CONDITION >---------------------------|
10 -- ----------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- This procedure is used to determine the attribute data type of a given
15 -- attribute, and hence the condition_type of a condition based on it. It
16 -- helps to establish whether the given condition is based on String
17 -- attributes or not. If attribute_id is not provided, then the condition_id
18 -- is used to first determine the attribute and then its type.
19 --
20 -- Prerequisites:
21 -- None.
22 --
23 -- In Parameters:
24 -- p_effective_date
25 -- p_condition_id
26 --
27 -- Post Success:
28 -- The type of condition/attribute is determined.
29 --
30 -- Post Failure:
31 -- An application error is raised if the input parameters are invalid.
32 -- All Errors are propagated to the calling procedure.
33 --
34 -- Developer Implementation Notes:
35 -- None.
36 --
37 -- Access Status:
38 -- Internal API Use Only.
39 --
40 -- {End Of Comments}
41 -- ----------------------------------------------------------------------------
42 function is_string_condition(p_effective_date in date
43 ,p_condition_id in number default null
44 )
45 return boolean is
46 --
47 -- Declare cursors and local variables
48 --
49 cursor csr_atr_type is
50 select attribute_type
51 from ame_attributes
52 where p_effective_date between start_date
53 and nvl(end_date - ame_util.oneSecond, p_effective_date)
54 and attribute_id in (select attribute_id
55 from ame_conditions
56 where condition_id = p_condition_id
57 and p_effective_date between start_date and
58 nvl(end_date - ame_util.oneSecond,
59 p_effective_date)
60 );
61 l_func varchar2(72) := g_package||'is_string_condition';
62 l_atr_type varchar2(20);
63 l_return_value boolean;
64 --
65 begin
66 hr_utility.set_location('Entering:'|| l_func, 10);
67 l_return_value := false;
68 if(p_condition_id is null) then
69 hr_utility.set_location('Leaving:'|| l_func, 20);
70 fnd_message.set_name('PER','AME_400494_INVALID_CONDITION');
71 fnd_message.raise_error;
72 else
73 open csr_atr_type;
74 fetch csr_atr_type into l_atr_type;
75 if(csr_atr_type%found and l_atr_type = ame_util.stringAttributeType) then
76 l_return_value := true;
77 end if;
78 close csr_atr_type;
79 end if;
80 hr_utility.set_location('Leaving:'|| l_func, 30);
81 return l_return_value;
82 end is_string_condition;
83 --
84 -- ----------------------------------------------------------------------------
85 -- |-----------------------< CREATE_AME_CONDITION >---------------------------|
86 -- ----------------------------------------------------------------------------
87 --
88 procedure create_ame_condition
89 (p_validate in boolean default false
90 ,p_condition_key in varchar2
91 ,p_condition_type in varchar2
92 ,p_attribute_id in number default null
93 ,p_parameter_one in varchar2 default null
94 ,p_parameter_two in varchar2 default null
95 ,p_parameter_three in varchar2 default null
96 ,p_include_upper_limit in varchar2 default null
97 ,p_include_lower_limit in varchar2 default null
98 ,p_string_value in varchar2 default null
99 ,p_condition_id out nocopy number
100 ,p_con_start_date out nocopy date
101 ,p_con_end_date out nocopy date
102 ,p_con_object_version_number out nocopy number
103 ,p_stv_start_date out nocopy date
104 ,p_stv_end_date out nocopy date
105 ,p_stv_object_version_number out nocopy number
106 ) is
107 --
108 -- Declare cursors and local variables
109 --
110 l_proc varchar2(72) := g_package||'create_ame_condition';
111 l_swi_pkg_name varchar2(72) := 'AME_CONDITION_SWI';
112 l_condition_id number;
113 l_object_version_number number;
114 l_object_version_number_chd number;
115 l_string_value varchar2(4000);
116 l_start_date date;
117 l_start_date_chd date;
118 l_end_date date;
119 l_end_date_chd date;
120 l_isStringCondition boolean;
121 --
122 begin
123 hr_utility.set_location('Entering:'|| l_proc, 10);
124 --
125 -- Issue a savepoint
126 --
127 savepoint create_ame_condition;
128 --
129 -- Remember IN OUT parameter IN values. None here.
130 --
131 -- Call Before Process User Hook
132 --
133 begin
134 ame_condition_bk1.create_ame_condition_b
135 (p_condition_key => p_condition_key
136 ,p_condition_type => p_condition_type
137 ,p_attribute_id => p_attribute_id
138 ,p_parameter_one => p_parameter_one
139 ,p_parameter_two => p_parameter_two
140 ,p_parameter_three => p_parameter_three
141 ,p_include_upper_limit => p_include_upper_limit
142 ,p_include_lower_limit => p_include_lower_limit
143 ,p_string_value => p_string_value
144 );
145 exception
146 when hr_api.cannot_find_prog_unit then
147 hr_api.cannot_find_prog_unit_error
148 (p_module_name => 'create_ame_condition'
149 ,p_hook_type => 'BP'
150 );
151 end;
152 --
153 -- Process Logic
154 --
155 ame_con_ins.ins(p_effective_date => sysdate
156 ,p_condition_key => p_condition_key
157 ,p_condition_type => p_condition_type
158 ,p_condition_id => l_condition_id
159 ,p_attribute_id => p_attribute_id
160 ,p_parameter_one => p_parameter_one
161 ,p_parameter_two => p_parameter_two
162 ,p_parameter_three => p_parameter_three
163 ,p_include_lower_limit => p_include_lower_limit
164 ,p_include_upper_limit => p_include_upper_limit
165 ,p_security_group_id => null
166 ,p_object_version_number => l_object_version_number
167 ,p_start_date => l_start_date
168 ,p_end_date => l_end_date
169 );
170 --
171 -- If condition is based on string attributes and string_value is not empty,
172 -- given that the call is NOT made through SWI package,call
173 -- create_ame_string_value
174 --
175 if (instr(DBMS_UTILITY.FORMAT_CALL_STACK,
176 l_swi_pkg_name||fnd_global.local_chr(10)) = 0) then
177 l_isStringCondition := is_string_condition
178 (p_effective_date => sysdate
179 ,p_condition_id => l_condition_id
180 );
181 if(l_isStringCondition) then
182 if(p_string_value is not null ) then
183 --
184 -- Insert the record.
185 --
186 create_ame_string_value
187 (p_validate => p_validate
188 ,p_condition_id => l_condition_id
189 ,p_string_value => p_string_value
190 ,p_object_version_number => l_object_version_number_chd
191 ,p_start_date => l_start_date_chd
192 ,p_end_date => l_end_date_chd
193 );
194 else
195 --
196 -- Raise an exception.Empty String Conditions cannot be created.
197 --
198 fnd_message.set_name('PER','AME_400525_STR_COND_EMPTY');
199 fnd_message.raise_error;
200 end if;
201 else
202 -- The condition is not based on string Attributes.
203 -- Check for non empty String Value List and prompt the user
204 if(p_string_value is not null ) then
205 fnd_message.set_name('PER','AME_400495_STRVAL_NON_STR_CON');
206 fnd_message.raise_error;
207 end if;
208 end if;
209 end if;
210 --
211 -- Call After Process User Hook
212 --
213 begin
214 ame_condition_bk1.create_ame_condition_a
215 (p_condition_key => p_condition_key
216 ,p_condition_type => p_condition_type
217 ,p_attribute_id => p_attribute_id
218 ,p_parameter_one => p_parameter_one
219 ,p_parameter_two => p_parameter_two
220 ,p_parameter_three => p_parameter_three
221 ,p_include_lower_limit => p_include_lower_limit
222 ,p_include_upper_limit => p_include_upper_limit
223 ,p_string_value => p_string_value
224 ,p_condition_id => l_condition_id
225 ,p_con_object_version_number => l_object_version_number
226 ,p_con_start_date => l_start_date
227 ,p_con_end_date => l_end_date
228 ,p_stv_object_version_number => l_object_version_number_chd
229 ,p_stv_start_date => l_start_date_chd
230 ,p_stv_end_date => l_end_date_chd
231 );
232 exception
233 when hr_api.cannot_find_prog_unit then
234 hr_api.cannot_find_prog_unit_error
235 (p_module_name => 'create_ame_condition'
236 ,p_hook_type => 'AP'
237 );
238 end;
239 --
240 -- When in validation only mode raise the Validate_Enabled exception
241 --
242 if p_validate then
243 raise hr_api.validate_enabled;
244 end if;
245 --
246 -- Set all IN OUT and OUT parameters with out values
247 --
248 p_condition_id := l_condition_id;
249 p_con_object_version_number := l_object_version_number;
250 p_con_start_date := l_start_date;
251 p_con_end_date := l_end_date;
252 p_stv_object_version_number := l_object_version_number_chd;
253 p_stv_start_date := l_start_date_chd;
254 p_stv_end_date := l_end_date_chd;
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 70);
257 exception
258 when hr_api.validate_enabled then
259 --
260 -- As the Validate_Enabled exception has been raised
261 -- we must rollback to the savepoint
262 --
263 rollback to create_ame_condition;
264 --
265 -- Reset IN OUT parameters and set OUT parameters
266 -- (Any key or derived arguments must be set to null
267 -- when validation only mode is being used.)
268 p_condition_id := null;
269 p_con_object_version_number := null;
270 p_con_start_date := null;
271 p_con_end_date := null;
272 p_stv_object_version_number := null;
273 p_stv_start_date := null;
274 p_stv_end_date := null;
275
276 --
277 hr_utility.set_location(' Leaving:'||l_proc, 80);
278 when others then
279 --
280 -- A validation or unexpected error has occured
281 --
282 rollback to create_ame_condition;
283 --
284 -- Reset IN OUT parameters and set all
285 -- OUT parameters, including warnings, to null
286 --
287 p_condition_id := null;
288 p_con_object_version_number := null;
289 p_con_start_date := null;
290 p_con_end_date := null;
291 p_stv_object_version_number := null;
292 p_stv_start_date := null;
293 p_stv_end_date := null;
294 --
295 hr_utility.set_location(' Leaving:'||l_proc, 90);
296 raise;
297 end create_ame_condition;
298 --
299 --
300 -- ----------------------------------------------------------------------------
301 -- |------------------------< UPDATE_AME_CONDITION >--------------------------|
302 -- ----------------------------------------------------------------------------
303 --
304 procedure update_ame_condition
305 (p_validate in boolean default false
306 ,p_condition_id in number
307 ,p_parameter_one in varchar2 default hr_api.g_varchar2
308 ,p_parameter_two in varchar2 default hr_api.g_varchar2
309 ,p_parameter_three in varchar2 default hr_api.g_varchar2
310 ,p_include_upper_limit in varchar2 default hr_api.g_varchar2
311 ,p_include_lower_limit in varchar2 default hr_api.g_varchar2
312 ,p_object_version_number in out nocopy number
313 ,p_start_date out nocopy date
314 ,p_end_date out nocopy date
315 ) is
316 --
317 -- Declare cursors and local variables
318 --
319 l_proc varchar2(72) := g_package||'update_ame_condition';
320 l_object_version_number number;
321 l_object_version_number_chd number := 1;
322 l_condition_id number;
323 l_start_date date;
324 l_start_date_chd date;
325 l_end_date date;
326 l_end_date_chd date;
327 l_string_value varchar2(4000);
328 --
329 begin
330 hr_utility.set_location('Entering:'|| l_proc, 10);
331 --
332 -- Issue a savepoint
333 --
334 savepoint update_ame_condition;
335 --
336 -- Remember IN OUT parameter IN values
337 --
338 l_object_version_number := p_object_version_number;
339 --
340 -- Call Before Process User Hook
341 --
342 begin
343 ame_condition_bk2.update_ame_condition_b
344 (p_condition_id => p_condition_id
345 ,p_parameter_one => p_parameter_one
346 ,p_parameter_two => p_parameter_two
347 ,p_parameter_three => p_parameter_three
348 ,p_include_upper_limit => p_include_upper_limit
349 ,p_include_lower_limit => p_include_lower_limit
350 ,p_object_version_number => p_object_version_number
351 );
352 exception
353 when hr_api.cannot_find_prog_unit then
354 hr_api.cannot_find_prog_unit_error
355 (p_module_name => 'update_ame_condition'
356 ,p_hook_type => 'BP'
357 );
358 end;
359 --
360 -- Process Logic
361 --
362 ame_con_upd.upd(p_effective_date => sysdate
363 ,p_datetrack_mode => hr_api.g_update
364 ,p_condition_id => p_condition_id
365 ,p_object_version_number => p_object_version_number
366 ,p_parameter_one => p_parameter_one
367 ,p_parameter_two => p_parameter_two
368 ,p_parameter_three => p_parameter_three
369 ,p_include_upper_limit => p_include_upper_limit
370 ,p_include_lower_limit => p_include_lower_limit
371 ,p_security_group_id => hr_api.g_number
372 ,p_start_date => l_start_date
373 ,p_end_date => l_end_date
374 );
375 --
376 -- Call After Process User Hook
377 --
378 begin
379 ame_condition_bk2.update_ame_condition_a
380 (p_condition_id => p_condition_id
381 ,p_parameter_one => p_parameter_one
382 ,p_parameter_two => p_parameter_two
383 ,p_parameter_three => p_parameter_three
384 ,p_include_upper_limit => p_include_upper_limit
385 ,p_include_lower_limit => p_include_lower_limit
386 ,p_object_version_number => p_object_version_number
387 ,p_start_date => l_start_date
388 ,p_end_date => l_end_date
389 );
390 exception
391 when hr_api.cannot_find_prog_unit then
392 hr_api.cannot_find_prog_unit_error
393 (p_module_name => 'update_ame_condition'
394 ,p_hook_type => 'AP'
395 );
396 end;
397 --
398 -- When in validation only mode raise the Validate_Enabled exception
399 --
400 if p_validate then
401 raise hr_api.validate_enabled;
402 end if;
403 --
404 -- Set all IN OUT and OUT parameters with out values.
405 --
406 p_start_date := l_start_date;
407 p_end_date := l_end_date;
408 --
409 hr_utility.set_location(' Leaving:'||l_proc, 70);
410 exception
411 when hr_api.validate_enabled then
412 --
413 -- As the Validate_Enabled exception has been raised
414 -- we must rollback to the savepoint
415 --
416 rollback to update_ame_condition;
417 --
418 -- Reset IN OUT parameters and set OUT parameters
419 -- (Any key or derived arguments must be set to null
420 -- when validation only mode is being used.)
421 --
422 p_object_version_number := l_object_version_number;
423 p_start_date := null;
424 p_end_date := null;
425 --
426 hr_utility.set_location(' Leaving:'||l_proc, 80);
427 when others then
428 --
429 -- A validation or unexpected error has occured
430 --
431 rollback to update_ame_condition;
432 --
433 -- Reset IN OUT parameters and set all
434 -- OUT parameters, including warnings, to null
435 --
436 p_object_version_number := l_object_version_number;
437 p_start_date := null;
438 p_end_date := null;
439 --
440 hr_utility.set_location(' Leaving:'||l_proc, 90);
441 raise;
442 end update_ame_condition;
443 --
444 --
445 -- ----------------------------------------------------------------------------
446 -- |------------------------< DELETE_AME_CONDITION >--------------------------|
447 -- ----------------------------------------------------------------------------
448 --
449 procedure delete_ame_condition
450 (p_validate in boolean default false
451 ,p_condition_id in number
452 ,p_object_version_number in out nocopy number
453 ,p_start_date out nocopy date
454 ,p_end_date out nocopy date
455 ) is
456 --
457 -- Declare cursors and local variables
458 --
459 cursor ame_str_vals is
460 select string_value, object_version_number
461 from ame_string_values
462 where condition_id = p_condition_id
463 and sysdate between start_date and
464 nvl(end_date - ame_util.oneSecond,sysdate);
465 cursor csr_rules(p_chk_condition_id number) is
466 select null
467 from ame_rules,
468 ame_rule_usages,
469 ame_condition_usages
470 where ame_rules.rule_id = ame_condition_usages.rule_id
471 and ame_rules.rule_id = ame_rule_usages.rule_id
472 and ame_condition_usages.condition_id = p_chk_condition_id
473 and ((sysdate between ame_rules.start_date and
474 nvl(ame_rules.end_date - ame_util.oneSecond,sysdate))
475 or
476 (sysdate < ame_rules.start_date and
477 ame_rules.start_date < nvl(ame_rules.end_date,
478 ame_rules.start_date + ame_util.oneSecond)))
479 and ((sysdate between ame_rule_usages.start_date and
480 nvl(ame_rule_usages.end_date - ame_util.oneSecond,sysdate))
481 or
482 (sysdate < ame_rule_usages.start_date and
483 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
484 ame_rule_usages.start_date + ame_util.oneSecond)))
485 and ((sysdate between ame_condition_usages.start_date and
486 nvl(ame_condition_usages.end_date-ame_util.oneSecond,sysdate))
487 or
488 (sysdate < ame_condition_usages.start_date and
489 ame_condition_usages.start_date <
490 nvl(ame_condition_usages.end_date,
491 ame_condition_usages.start_date + ame_util.oneSecond)));
492 --
493 l_proc varchar2(72) := g_package||'delete_ame_condition';
494 l_object_version_number number;
495 l_start_date date;
496 l_end_date date;
497 l_isStringCondition boolean;
498 l_key varchar2(1);
499 l_string_value_tab ame_util.longestStringList;
500 l_object_version_number_tab ame_util.idList;
501 --
502 begin
503 hr_utility.set_location('Entering:'|| l_proc, 10);
504 --
505 -- Issue a savepoint
506 --
507 savepoint delete_ame_condition;
508 --
509 -- Remember IN OUT parameter IN values
510 --
511 l_object_version_number := p_object_version_number;
512 --
513 -- Call Before Process User Hook
514 --
515 begin
516 ame_condition_bk3.delete_ame_condition_b
517 (p_condition_id => p_condition_id
518 ,p_object_version_number => p_object_version_number
519 );
520 exception
521 when hr_api.cannot_find_prog_unit then
522 hr_api.cannot_find_prog_unit_error
523 (p_module_name => 'delete_ame_condition'
524 ,p_hook_type => 'BP'
525 );
526 end;
527 --
528 -- Process Logic
529 --
530 -- Determine if the condition is being used by any rule. If yes, then
531 -- throw an error mentioning the same.
532 open csr_rules(p_condition_id);
533 fetch csr_rules into l_key;
534 if(csr_rules%found) then
535 close csr_rules;
536 fnd_message.set_name('PER', 'AME_400193_CON_IN USE');
537 fnd_message.raise_error;
538 else
539 close csr_rules;
540 -- Determine if the condition is based on string attributes. If yes, call
541 -- the deleteAllStringValues api to delete associated string values
542 --
543 l_isStringCondition := is_string_condition(p_effective_date => sysdate
544 ,p_condition_id => p_condition_id
545 );
546 if(l_isStringCondition) then
547 open ame_str_vals;
548 fetch ame_str_vals bulk collect into l_string_value_tab
549 ,l_object_version_number_tab;
550 close ame_str_vals;
551 if(l_string_value_tab.count > 0) then
552 for indx in 1..l_string_value_tab.count loop
553 delete_ame_string_value
554 (p_validate => p_validate
555 ,p_condition_id => p_condition_id
556 ,p_string_value => l_string_value_tab(indx)
557 ,p_object_version_number => l_object_version_number_tab(indx)
558 ,p_start_date => l_start_date
559 ,p_end_date => l_end_date
560 );
561 end loop;
562 end if;
563 end if;
564 ame_con_del.del(p_effective_date => sysdate
565 ,p_datetrack_mode => hr_api.g_delete
566 ,p_condition_id => p_condition_id
567 ,p_object_version_number => p_object_version_number
568 ,p_start_date => l_start_date
569 ,p_end_date => l_end_date
570 );
571 --
572 -- Call After Process User Hook
573 --
574 begin
575 ame_condition_bk3.delete_ame_condition_a
576 (p_condition_id => p_condition_id
577 ,p_object_version_number => p_object_version_number
578 ,p_start_date => l_start_date
579 ,p_end_date => l_end_date
580 );
581 exception
582 when hr_api.cannot_find_prog_unit then
583 hr_api.cannot_find_prog_unit_error
584 (p_module_name => 'delete_ame_condition'
585 ,p_hook_type => 'AP'
586 );
587 end;
588 --
589 -- When in validation only mode raise the Validate_Enabled exception
590 --
591 if p_validate then
592 raise hr_api.validate_enabled;
593 end if;
594 --
595 -- Set all IN OUT and OUT parameters with out values.
596 --
597 p_start_date := l_start_date;
598 p_end_date := l_end_date;
599 --
600 end if;
601 hr_utility.set_location(' Leaving:'||l_proc, 70);
602 exception
603 when hr_api.validate_enabled then
604 --
605 -- As the Validate_Enabled exception has been raised
606 -- we must rollback to the savepoint
607 --
608 rollback to delete_ame_condition;
609 --
610 -- Reset IN OUT parameters and set OUT parameters
611 -- (Any key or derived arguments must be set to null
612 -- when validation only mode is being used.)
613 --
614 p_object_version_number := l_object_version_number;
615 p_start_date := null;
616 p_end_date := null;
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 80);
619 when others then
620 --
621 -- A validation or unexpected error has occured
622 --
623 rollback to delete_ame_condition;
624 --
625 -- Reset IN OUT parameters and set all
626 -- OUT parameters, including warnings, to null
627 --
628 p_object_version_number := l_object_version_number;
629 p_start_date := null;
630 p_end_date := null;
631 --
632 hr_utility.set_location(' Leaving:'||l_proc, 90);
633 raise;
634 end delete_ame_condition;
635 --
636 -- ----------------------------------------------------------------------------
637 -- |-----------------------< CREATE_AME_STRING_VALUE >------------------------|
638 -- ----------------------------------------------------------------------------
639 --
640 procedure create_ame_string_value
641 (p_validate in boolean default false
642 ,p_condition_id in number
643 ,p_string_value in varchar2
644 ,p_object_version_number out nocopy number
645 ,p_start_date out nocopy date
646 ,p_end_date out nocopy date
647 ) is
648 --
649 -- Declare cursors and local variables
650 --
651 l_proc varchar2(72) := g_package||'create_ame_string_value';
652 l_object_version_number number;
653 l_start_date date;
654 l_end_date date;
655 --
656 begin
657 hr_utility.set_location('Entering:'|| l_proc, 10);
658 --
659 -- Issue a savepoint
660 --
661 savepoint create_ame_string_value;
662 --
663 -- Remember IN OUT parameter IN values.(None to remember here)
664 --
665 -- Call Before Process User Hook
666 --
667 begin
668 ame_condition_bk4.create_ame_string_value_b
669 (p_condition_id => p_condition_id
670 ,p_string_value => p_string_value
671 );
672 exception
673 when hr_api.cannot_find_prog_unit then
674 hr_api.cannot_find_prog_unit_error
675 (p_module_name => 'create_ame_string_value'
676 ,p_hook_type => 'BP'
677 );
678 end;
679 --
680 -- Process Logic.
681 --
682 ame_stv_ins.ins(p_effective_date => sysdate
683 ,p_security_group_id => null
684 ,p_condition_id => p_condition_id
685 ,p_string_value => p_string_value
686 ,p_object_version_number => l_object_version_number
687 ,p_start_date => l_start_date
688 ,p_end_date => l_end_date
689 );
690 --
691 -- Call After Process User Hook
692 --
693 begin
694 ame_condition_bk4.create_ame_string_value_a
695 (p_condition_id => p_condition_id
696 ,p_string_value => p_string_value
697 ,p_object_version_number => l_object_version_number
698 ,p_start_date => l_start_date
699 ,p_end_date => l_end_date
700 );
701 exception
702 when hr_api.cannot_find_prog_unit then
703 hr_api.cannot_find_prog_unit_error
704 (p_module_name => 'create_ame_string_value'
705 ,p_hook_type => 'AP'
706 );
707 end;
708 --
709 -- When in validation only mode raise the Validate_Enabled exception
710 --
711 if p_validate then
712 raise hr_api.validate_enabled;
713 end if;
714 --
715 -- Set all IN OUT and OUT parameters with out values.
716 --
717 p_object_version_number := l_object_version_number;
718 p_start_date := l_start_date;
719 p_end_date := l_end_date;
720 --
721 hr_utility.set_location(' Leaving:'||l_proc, 70);
722 exception
723 when hr_api.validate_enabled then
724 --
725 -- As the Validate_Enabled exception has been raised
729 --
726 -- we must rollback to the savepoint
727 --
728 rollback to create_ame_string_value;
730 -- Reset IN OUT parameters and set OUT parameters
731 -- (Any key or derived arguments must be set to null
732 -- when validation only mode is being used.)
733 --
734 p_object_version_number := null;
735 p_start_date := null;
736 p_end_date := null;
737 --
738 hr_utility.set_location(' Leaving:'||l_proc, 80);
739 when others then
740 --
741 -- A validation or unexpected error has occured
742 --
743 rollback to create_ame_string_value;
744 --
745 -- Reset IN OUT parameters and set all
746 -- OUT parameters, including warnings, to null
747 --
748 p_object_version_number := null;
749 p_start_date := null;
750 p_end_date := null;
751 --
752 hr_utility.set_location(' Leaving:'||l_proc, 90);
753 raise;
754 end create_ame_string_value;
755 --
756 --
757 -- ----------------------------------------------------------------------------
758 -- |----------------------< DELETE_AME_STRING_VALUE >-------------------------|
759 -- ----------------------------------------------------------------------------
760 --
761 procedure delete_ame_string_value
762 (p_validate in boolean default false
763 ,p_condition_id in number
764 ,p_string_value in varchar2
765 ,p_object_version_number in out nocopy number
766 ,p_start_date out nocopy date
767 ,p_end_date out nocopy date
768 ) is
769 --
770 -- Declare cursors and local variables
771 --
772 l_proc varchar2(72) := g_package||'delete_ame_string_value';
773 l_object_version_number number;
774 l_start_date date;
775 l_end_date date;
776 --
777 begin
778 hr_utility.set_location('Entering:'|| l_proc, 10);
779 --
780 -- Issue a savepoint
781 --
782 savepoint delete_ame_string_value;
783 --
784 -- Remember IN OUT parameter IN values
785 --
786 l_object_version_number := p_object_version_number;
787 --
788 -- Call Before Process User Hook
789 --
790 begin
791 ame_condition_bk5.delete_ame_string_value_b
792 (p_condition_id => p_condition_id
793 ,p_string_value => p_string_value
794 ,p_object_version_number => p_object_version_number
795 );
796 exception
797 when hr_api.cannot_find_prog_unit then
798 hr_api.cannot_find_prog_unit_error
799 (p_module_name => 'delete_ame_string_value'
800 ,p_hook_type => 'BP'
801 );
802 end;
803 --
804 -- Process Logic
805 --
806 ame_stv_del.del(p_effective_date => sysdate
807 ,p_datetrack_mode => hr_api.g_delete
808 ,p_condition_id => p_condition_id
809 ,p_string_value => p_string_value
810 ,p_object_version_number => p_object_version_number
811 ,p_start_date => l_start_date
812 ,p_end_date => l_end_date
813 );
814 --
815 -- Call After Process User Hook
816 --
817 begin
818 ame_condition_bk5.delete_ame_string_value_a
819 (p_condition_id => p_condition_id
820 ,p_string_value => p_string_value
821 ,p_object_version_number => p_object_version_number
822 ,p_start_date => l_start_date
823 ,p_end_date => l_end_date
824 );
825 exception
826 when hr_api.cannot_find_prog_unit then
827 hr_api.cannot_find_prog_unit_error
828 (p_module_name => 'delete_ame_string_value'
829 ,p_hook_type => 'AP'
830 );
831 end;
832 --
833 -- When in validation only mode raise the Validate_Enabled exception
834 --
835 if p_validate then
836 raise hr_api.validate_enabled;
837 end if;
838 --
839 -- Set all IN OUT and OUT parameters with out values.
840 --
841 p_start_date := l_start_date;
842 p_end_date := l_end_date;
843 --
844 hr_utility.set_location(' Leaving:'||l_proc, 70);
845 exception
846 when hr_api.validate_enabled then
847 --
848 -- As the Validate_Enabled exception has been raised
849 -- we must rollback to the savepoint
850 --
851 rollback to delete_ame_string_value;
852 --
853 -- Reset IN OUT parameters and set OUT parameters
854 -- (Any key or derived arguments must be set to null
855 -- when validation only mode is being used.)
856 --
857 p_object_version_number := l_object_version_number;
858 p_start_date := null;
859 p_end_date := null;
860 --
861 hr_utility.set_location(' Leaving:'||l_proc, 80);
862 when others then
863 --
864 -- A validation or unexpected error has occured
865 --
866 rollback to delete_ame_string_value;
867 --
868 -- Reset IN OUT parameters and set all
869 -- OUT parameters, including warnings, to null
870 --
871 p_object_version_number := l_object_version_number;
872 p_start_date := null;
873 p_end_date := null;
874 --
875 hr_utility.set_location(' Leaving:'||l_proc, 90);
878 end AME_CONDITION_API;
876 raise;
877 end delete_ame_string_value;