[Home] [Help]
PACKAGE BODY: APPS.AME_ATTRIBUTE_API
Source
1 PACKAGE BODY AME_ATTRIBUTE_API as
2 /* $Header: amatrapi.pkb 120.1 2006/02/07 09:48 prasashe noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ame_attribute_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_ame_attribute >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ame_attribute
13 (p_validate in boolean default false
14 ,p_language_code in varchar2 default hr_api.userenv_lang
15 ,p_name in varchar2
16 ,p_description in varchar2
17 ,p_attribute_type in varchar2
18 ,p_item_class_id in number
19 ,p_approver_type_id in number default null
20 ,p_application_id in number default null
21 ,p_is_static in varchar2 default ame_util.booleanTrue
22 ,p_query_string in varchar2 default null
23 ,p_user_editable in varchar2 default ame_util.booleanTrue
24 ,p_value_set_id in number default null
25 ,p_attribute_id out nocopy number
26 ,p_atr_object_version_number out nocopy number
27 ,p_atr_start_date out nocopy date
28 ,p_atr_end_date out nocopy date
29 ,p_atu_object_version_number out nocopy number
30 ,p_atu_start_date out nocopy date
31 ,p_atu_end_date out nocopy date
32 ) is
33 --
34 -- Declare cursors and local variables
35 --
36 l_proc varchar2(72) := g_package||'create_ame_attribute';
37 l_attribute_id number;
38 l_atr_object_version_number number;
39 l_atu_object_version_number number;
40 l_atr_start_date date;
41 l_atu_start_date date;
42 l_atr_end_date date;
43 l_atu_end_date date;
44 l_swi_call boolean;
45 l_swi_package_name varchar2(30) := 'AME_ATTRIBUTE_SWI';
46 l_effective_date date;
47 l_use_count number := 0;
48 l_name ame_attributes.name%type;
49 begin
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 --
52 l_name := upper(p_name);
53 --
54 -- Issue a savepoint
55 --
56 savepoint create_ame_attribute;
57 --
58 -- Call Before Process User Hook
59 --
60 begin
61 ame_attribute_bk1.create_ame_attribute_b
62 (p_name => l_name
63 ,p_description => p_description
64 ,p_attribute_type => p_attribute_type
65 ,p_item_class_id => p_item_class_id
66 ,p_approver_type_id => p_approver_type_id
67 );
68 exception
69 when hr_api.cannot_find_prog_unit then
70 hr_api.cannot_find_prog_unit_error
71 (p_module_name => 'create_ame_attribute'
72 ,p_hook_type => 'BP'
73 );
74 end;
75 --
76 -- Process Logic
77 --
78 -- Set the effective date to the sysdate
79 l_effective_date := sysdate;
80 l_swi_call := true;
81
82 ame_atr_ins.ins(p_effective_date => l_effective_date
83 ,p_name => l_name
84 ,p_attribute_type => p_attribute_type
85 ,p_description => p_description
86 ,p_item_class_id => p_item_class_id
87 ,p_approver_type_id => p_approver_type_id
88 ,p_attribute_id => l_attribute_id
89 ,p_object_version_number => l_atr_object_version_number
90 ,p_start_date => l_atr_start_date
91 ,p_end_date => l_atr_end_date
92 );
93 -- insert data into TL tables
94 ame_atl_ins.ins_tl(p_language_code => p_language_code
95 ,p_attribute_id => l_attribute_id
96 ,p_description => p_description
97 );
98 -- Call DBMS_UTILITY.FORMAT_CALL_STACK to check if the call has been made from the 'AME_ATTRIBUTE_SWI' package.
99 if (instrb(DBMS_UTILITY.FORMAT_CALL_STACK,l_swi_package_name||fnd_global.local_chr(10)) = 0) then
100 l_swi_call := false;
101 create_ame_attribute_usage
102 (p_validate => p_validate
103 ,p_attribute_id => l_attribute_id
104 ,p_application_id => p_application_id
105 ,p_is_static => p_is_static
106 ,p_query_string => p_query_string
107 ,p_user_editable => p_user_editable
108 ,p_value_set_id => p_value_set_id
109 ,p_object_version_number => l_atu_object_version_number
110 ,p_start_date => l_atu_start_date
111 ,p_end_date => l_atu_end_date
112 );
113 end if;
114 --
115 -- Call After Process User Hook
116 --
117 begin
118 ame_attribute_bk1.create_ame_attribute_a
119 (p_name => l_name
120 ,p_description => p_description
121 ,p_attribute_type => p_attribute_type
122 ,p_item_class_id => p_item_class_id
123 ,p_approver_type_id => p_approver_type_id
124 ,p_attribute_id => l_attribute_id
125 ,p_atr_object_version_number => l_atr_object_version_number
126 ,p_atr_start_date => l_atr_start_date
127 ,p_atr_end_date => l_atr_end_date
128 );
129 exception
130 when hr_api.cannot_find_prog_unit then
131 hr_api.cannot_find_prog_unit_error
132 (p_module_name => 'create_ame_attribute'
133 ,p_hook_type => 'AP'
134 );
135 end;
136 --
137 -- When in validation only mode raise the Validate_Enabled exception
138 --
139 if p_validate then
140 raise hr_api.validate_enabled;
141 end if;
142 --
143 -- Set all IN OUT and OUT parameters with out values
144 --
145 p_attribute_id := l_attribute_id;
146 p_atr_object_version_number := l_atr_object_version_number;
147 p_atr_start_date := l_atr_start_date;
148 p_atr_end_date := l_atr_end_date;
149 if not l_swi_call then
150 p_atu_object_version_number := l_atu_object_version_number;
151 p_atu_start_date := l_atu_start_date;
152 p_atu_end_date := l_atu_end_date;
153 end if;
154 --
155 hr_utility.set_location(' Leaving:'||l_proc, 70);
156 exception
157 when hr_api.validate_enabled then
158 --
159 -- As the Validate_Enabled exception has been raised
160 -- we must rollback to the savepoint
161 --
162 rollback to create_ame_attribute;
163 --
164 -- Reset IN OUT parameters and set OUT parameters
165 -- (Any key or derived arguments must be set to null
166 -- when validation only mode is being used.)
167 --
168 p_attribute_id := null;
169 p_atr_object_version_number := null;
170 p_atr_start_date := null;
171 p_atr_end_date := null;
172 if not l_swi_call then
173 p_atu_object_version_number := null;
174 p_atu_start_date := null;
175 p_atu_end_date := null;
176 end if;
177 hr_utility.set_location(' Leaving:'||l_proc, 80);
178 when others then
179 --
180 -- A validation or unexpected error has occured
181 --
182 rollback to create_ame_attribute;
183 --
184 -- Reset IN OUT parameters and set all
185 -- OUT parameters, including warnings, to null
186 --
187 p_attribute_id := null;
188 p_atr_object_version_number := null;
189 p_atr_start_date := null;
190 p_atr_end_date := null;
191 if not l_swi_call then
192 p_atu_object_version_number:= null;
193 p_atu_start_date := null;
194 p_atu_end_date := null;
195 end if;
196 hr_utility.set_location(' Leaving:'||l_proc, 90);
197 raise;
198 end create_ame_attribute;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |------------------< create_ame_attribute_usage >--------------------------|
202 -- ----------------------------------------------------------------------------
203 --
204 procedure create_ame_attribute_usage
205 (p_validate in boolean default false
206 ,p_attribute_id in number
207 ,p_application_id in number
208 ,p_is_static in varchar2 default ame_util.booleanTrue
209 ,p_query_string in varchar2 default null
210 ,p_user_editable in varchar2 default ame_util.booleanTrue
211 ,p_value_set_id in number default null
212 ,p_object_version_number out nocopy number
213 ,p_start_date out nocopy date
214 ,p_end_date out nocopy date
215 ) is
216
217 --
218 -- Declare cursors and local variables
219 --
220 l_proc varchar2(72) := g_package||'create_ame_attribute_usage';
221 l_atr_object_version_number number;
222 l_attribute_id number;
223 l_application_id number;
224 l_atu_object_version_number number;
225 l_atu_start_date date;
226 l_atu_end_date date;
227 l_effective_date date;
228 l_use_count number := 0;
229 l_validation_start_date date;
230 l_validation_end_date date;
231 begin
232 hr_utility.set_location('Entering:'|| l_proc, 10);
233 --
234 -- Issue a savepoint
235 --
236 savepoint create_ame_attribute_usage;
237 --
238 -- Call Before Process User Hook
239 --
240 begin
241 ame_attribute_bk2.create_ame_attribute_usage_b
242 (p_attribute_id => p_attribute_id
243 ,p_application_id => p_application_id
244 ,p_is_static => p_is_static
245 ,p_query_string => p_query_string
246 ,p_user_editable => p_user_editable
247 ,p_value_set_id => p_value_set_id
248 );
249 exception
250 when hr_api.cannot_find_prog_unit then
251 hr_api.cannot_find_prog_unit_error
252 (p_module_name => 'create_ame_attribute_usage'
253 ,p_hook_type => 'BP'
254 );
255 end;
256 --
257 -- Process Logic
258 --
259 -- Set the effective date to the sysdate
260 l_effective_date := sysdate;
261 -- insert the row in ame_attribute_usages
262 ame_atu_ins.ins(p_attribute_id => p_attribute_id
263 ,p_application_id => p_application_id
264 ,p_effective_date => l_effective_date
265 ,p_use_count => l_use_count
266 ,p_is_static => p_is_static
267 ,p_query_string => p_query_string
268 ,p_user_editable => p_user_editable
269 ,p_value_set_id => p_value_set_id
270 ,p_object_version_number => l_atu_object_version_number
271 ,p_start_date => l_atu_start_date
272 ,p_end_date => l_atu_end_date
273 );
274 --
275 -- Call After Process User Hook
276 --
277 begin
278 ame_attribute_bk2.create_ame_attribute_usage_a
279 (p_attribute_id => p_attribute_id
280 ,p_application_id => p_application_id
281 ,p_is_static => p_is_static
282 ,p_query_string => p_query_string
283 ,p_user_editable => p_user_editable
284 ,p_value_set_id => p_value_set_id
285 ,p_object_version_number => l_atu_object_version_number
286 ,p_start_date => l_atu_start_date
287 ,p_end_date => l_atu_end_date
288 );
289 exception
290 when hr_api.cannot_find_prog_unit then
291 hr_api.cannot_find_prog_unit_error
292 (p_module_name => 'create_ame_attribute_usage'
293 ,p_hook_type => 'AP'
294 );
295 end;
296 --
297 -- When in validation only mode raise the Validate_Enabled exception
298 --
299 if p_validate then
300 raise hr_api.validate_enabled;
301 end if;
302 --
303 -- Set all IN OUT and OUT parameters with out values
304 --
305 p_object_version_number := l_atu_object_version_number;
306 p_start_date := l_atu_start_date;
307 p_end_date := l_atu_end_date;
308 --
309 hr_utility.set_location(' Leaving:'||l_proc, 70);
310 exception
311 when hr_api.validate_enabled then
312 --
313 -- As the Validate_Enabled exception has been raised
314 -- we must rollback to the savepoint
315 --
316 rollback to create_ame_attribute_usage;
317 --
318 -- Reset IN OUT parameters and set OUT parameters
319 -- (Any key or derived arguments must be set to null
320 -- when validation only mode is being used.)
321 --
322 p_object_version_number := null;
323 p_start_date := null;
324 p_end_date := null;
325 hr_utility.set_location(' Leaving:'||l_proc, 80);
326 when others then
327 --
328 -- A validation or unexpected error has occured
329 --
330 rollback to create_ame_attribute_usage;
331 --
332 -- Reset IN OUT parameters and set all
333 -- OUT parameters, including warnings, to null
334 --
335 p_object_version_number := null;
336 p_start_date := null;
337 p_end_date := null;
338 hr_utility.set_location(' Leaving:'||l_proc, 90);
339 raise;
340 end create_ame_attribute_usage;
341 --
342 --
343 -- ----------------------------------------------------------------------------
344 -- |-----------------------< update_ame_attribute >-------------------------------|
345 -- ----------------------------------------------------------------------------
346 --
347 procedure update_ame_attribute
348 (p_validate in boolean default false
349 ,p_language_code in varchar2 default hr_api.userenv_lang
350 ,p_attribute_id in number
351 ,p_description in varchar2 default hr_api.g_varchar2
352 ,p_object_version_number in out nocopy number
353 ,p_start_date out nocopy date
354 ,p_end_date out nocopy date
355 ) is
356 --
357 -- Declare cursors and local variables
358 --
359 l_atr_object_version_number number;
360 l_atr_start_date date;
361 l_atr_end_date date;
362 l_effective_date date;
363 l_proc varchar2(72) := g_package||'update_ame_attribute';
364 begin
365 hr_utility.set_location('Entering:'|| l_proc, 10);
366 --
367 -- Issue a savepoint
368 --
369 savepoint update_ame_attribute;
370 --
371 -- Call Before Process User Hook
372 --
373 begin
374 ame_attribute_bk3.update_ame_attribute_b
375 (p_attribute_id => p_attribute_id
376 ,p_description => p_description
377 ,p_object_version_number => p_object_version_number
378 );
379 exception
380 when hr_api.cannot_find_prog_unit then
381 hr_api.cannot_find_prog_unit_error
382 (p_module_name => 'update_ame_attribute'
383 ,p_hook_type => 'BP'
384 );
385 end;
386 --
387 -- Process Logic
388 --
389 -- Set the effective date to the sysdate
390 l_effective_date := sysdate;
391 l_atr_object_version_number := p_object_version_number;
392 if p_attribute_id is null then
393 fnd_message.set_name('PER', 'AME_400473_INV_ATTRIBUTE_ID');
394 fnd_message.raise_error;
395 end if;
396 ame_atr_upd.upd(p_effective_date => l_effective_date
397 ,p_datetrack_mode => 'UPDATE'
398 ,p_attribute_id => p_attribute_id
399 ,p_object_version_number => l_atr_object_version_number
400 ,p_description => p_description
401 ,p_start_date => l_atr_start_date
402 ,p_end_date => l_atr_end_date
403 );
404 -- update data into TL tables
405 ame_atl_upd.upd_tl(p_language_code => p_language_code
406 ,p_attribute_id => p_attribute_id
407 ,p_description => p_description
408 );
409 --
410 -- Call After Process User Hook
411 --
412 begin
413 ame_attribute_bk3.update_ame_attribute_a
414 (p_attribute_id => p_attribute_id
415 ,p_description => p_description
416 ,p_object_version_number => l_atr_object_version_number
417 ,p_start_date => l_atr_start_date
418 ,p_end_date => l_atr_end_date
419 );
420 exception
421 when hr_api.cannot_find_prog_unit then
422 hr_api.cannot_find_prog_unit_error
423 (p_module_name => 'update_ame_attribute'
424 ,p_hook_type => 'AP'
425 );
426 end;
427 --
428 -- When in validation only mode raise the Validate_Enabled exception
429 --
430 if p_validate then
431 raise hr_api.validate_enabled;
432 end if;
433 --
434 -- Set all IN OUT and OUT parameters with out values
435 --
436 p_object_version_number := l_atr_object_version_number;
437 p_start_date := l_atr_start_date;
438 p_end_date := l_atr_end_date;
439 --
440 hr_utility.set_location(' Leaving:'||l_proc, 70);
441 exception
442 when hr_api.validate_enabled then
443 --
444 -- As the Validate_Enabled exception has been raised
445 -- we must rollback to the savepoint
446 --
447 rollback to update_ame_attribute;
448 --
449 -- Reset IN OUT parameters and set OUT parameters
450 -- (Any key or derived arguments must be set to null
451 -- when validation only mode is being used.)
452 --
453 p_start_date := null;
454 p_end_date := null;
455 hr_utility.set_location(' Leaving:'||l_proc, 80);
456 when others then
457 --
458 -- A validation or unexpected error has occured
459 --
460 rollback to update_ame_attribute;
461 --
462 -- Reset IN OUT parameters and set all
463 -- OUT parameters, including warnings, to null
464 --
465 p_start_date := null;
466 p_end_date := null;
467 hr_utility.set_location(' Leaving:'||l_proc, 90);
468 raise;
469 end update_ame_attribute;
470 --
471 --
472 -- ----------------------------------------------------------------------------
473 -- |---------------------< update_ame_attribute_usage >--------------------------|
474 -- ----------------------------------------------------------------------------
475 --
476 procedure update_ame_attribute_usage
477 (p_validate in boolean default false
478 ,p_attribute_id in number
479 ,p_application_id in number
480 ,p_is_static in varchar2 default ame_util.booleanTrue
481 ,p_query_string in varchar2 default null
482 ,p_value_set_id in number default null
483 ,p_object_version_number in out nocopy number
484 ,p_start_date out nocopy date
485 ,p_end_date out nocopy date
486 ) is
487 --
488 -- Declare cursors and local variables
489 --
490 l_atu_object_version_number number;
491 l_atu_start_date date;
492 l_atu_end_date date;
493 l_effective_date date;
494 l_proc varchar2(72) := g_package||'update_ame_attribute_usage';
495 begin
496 hr_utility.set_location('Entering:'|| l_proc, 10);
497 --
498 -- Issue a savepoint
499 --
500 savepoint update_ame_attribute_usage;
501 --
502 -- Call Before Process User Hook
503 --
504 begin
505 ame_attribute_bk4.update_ame_attribute_usage_b
506 (p_attribute_id => p_attribute_id
507 ,p_application_id => p_application_id
508 ,p_is_static => p_is_static
509 ,p_query_string => p_query_string
510 ,p_value_set_id => p_value_set_id
511 ,p_object_version_number => p_object_version_number
512 );
513 exception
514 when hr_api.cannot_find_prog_unit then
515 hr_api.cannot_find_prog_unit_error
516 (p_module_name => 'update_ame_attribute_usage'
517 ,p_hook_type => 'BP'
518 );
519 end;
520 --
521 -- Process Logic
522 --
523 -- Set the effective date to the sysdate
524 l_effective_date := sysdate;
525 l_atu_object_version_number := p_object_version_number;
526 -- update the row in ame_attribute_usages. Parent row locking not needed.
527 ame_atu_upd.upd(p_effective_date => l_effective_date
528 ,p_datetrack_mode => 'UPDATE'
529 ,p_attribute_id => p_attribute_id
530 ,p_application_id => p_application_id
531 ,p_object_version_number=> l_atu_object_version_number
532 ,p_query_string => p_query_string
533 ,p_is_static => p_is_static
534 ,p_value_set_id => p_value_set_id
535 ,p_start_date => l_atu_start_date
536 ,p_end_date => l_atu_end_date
537 );
538 --
539 -- Call After Process User Hook
540 --
541 begin
542 ame_attribute_bk4.update_ame_attribute_usage_a
543 (p_attribute_id => p_attribute_id
544 ,p_application_id => p_application_id
545 ,p_is_static => p_is_static
546 ,p_query_string => p_query_string
547 ,p_value_set_id => p_value_set_id
548 ,p_object_version_number => l_atu_object_version_number
549 ,p_start_date => l_atu_start_date
550 ,p_end_date => l_atu_end_date
551 );
552 exception
553 when hr_api.cannot_find_prog_unit then
554 hr_api.cannot_find_prog_unit_error
555 (p_module_name => 'update_ame_attribute_usage'
556 ,p_hook_type => 'AP'
557 );
558 end;
559 --
560 -- When in validation only mode raise the Validate_Enabled exception
561 --
562 if p_validate then
563 raise hr_api.validate_enabled;
564 end if;
565 --
566 -- Set all IN OUT and OUT parameters with out values
567 --
568 p_object_version_number := l_atu_object_version_number;
569 p_start_date := l_atu_start_date;
570 p_end_date := l_atu_end_date;
571 --
572 hr_utility.set_location(' Leaving:'||l_proc, 70);
573 exception
574 when hr_api.validate_enabled then
575 --
576 -- As the Validate_Enabled exception has been raised
577 -- we must rollback to the savepoint
578 --
579 rollback to update_ame_attribute_usage;
580 --
581 -- Reset IN OUT parameters and set OUT parameters
582 -- (Any key or derived arguments must be set to null
583 -- when validation only mode is being used.)
584 --
585 p_start_date := null;
586 p_end_date := null;
587 hr_utility.set_location(' Leaving:'||l_proc, 80);
588 when others then
589 --
590 -- A validation or unexpected error has occured
591 --
592 rollback to update_ame_attribute_usage;
593 --
594 -- Reset IN OUT parameters and set all
595 -- OUT parameters, including warnings, to null
596 --
597 p_start_date := null;
598 p_end_date := null;
599 hr_utility.set_location(' Leaving:'||l_proc, 90);
600 raise;
601 end update_ame_attribute_usage;
602 --
603 -- ----------------------------------------------------------------------------
604 -- |---------------------< delete_ame_attribute_usage >--------------------------|
605 -- ----------------------------------------------------------------------------
606 --
607 procedure delete_ame_attribute_usage
608 (p_validate in boolean default false
609 ,p_attribute_id in number
610 ,p_application_id in number
611 ,p_object_version_number in out nocopy number
612 ,p_start_date out nocopy date
613 ,p_end_date out nocopy date
614 ) is
615 --
616 -- Declare cursors and local variables
617 --
618 l_atr_object_version_number number;
619 l_atu_object_version_number number;
620 l_atr_start_date date;
621 l_atu_start_date date;
622 l_atr_end_date date;
623 l_atu_end_date date;
624 l_effective_date date;
625 l_proc varchar2(72) := g_package||'delete_ame_attribute_usage';
626 l_usage_count number;
627 l_validation_start_date date;
628 l_validation_end_date date;
629 l_exists varchar2(1);
630 l_con_start_date date;
631 l_con_end_date date;
632
633 cursor c_sel1 is
634 select condition_id, object_version_number
635 from ame_conditions
636 where attribute_id = p_attribute_id
637 and sysdate between start_date and
638 nvl(end_date - ame_util.oneSecond,sysdate);
639
640 cursor c_sel2 is
641 select null
642 from ame_mandatory_attributes
643 where attribute_id = p_attribute_id and
644 sysdate between start_date and
645 nvl(end_date - ame_util.oneSecond, sysdate) ;
646
647 begin
648
649 hr_utility.set_location('Entering:'|| l_proc, 10);
650 --
651 -- Issue a savepoint
652 --
653 savepoint delete_ame_attribute_usage;
654 --
655 -- Call Before Process User Hook
656 --
657 begin
658 ame_attribute_bk5.delete_ame_attribute_usage_b
659 (p_attribute_id => p_attribute_id
660 ,p_application_id => p_application_id
661 ,p_object_version_number => p_object_version_number
662 );
663 exception
664 when hr_api.cannot_find_prog_unit then
665 hr_api.cannot_find_prog_unit_error
666 (p_module_name => 'delete_ame_attribute_usage'
667 ,p_hook_type => 'BP'
668 );
669 end;
670 --
671 -- Process Logic
672 --
673 -- Set the effective date to the sysdate
674 l_effective_date := sysdate;
675 l_atu_object_version_number := p_object_version_number;
676 --
677 -- delete the row in ame_attribute_usages
678 ame_atu_del.del(p_effective_date => l_effective_date
679 ,p_datetrack_mode => 'DELETE'
680 ,p_attribute_id => p_attribute_id
681 ,p_application_id => p_application_id
682 ,p_object_version_number=> l_atu_object_version_number
683 ,p_start_date => l_atu_start_date
684 ,p_end_date => l_atu_end_date
685 );
686 --
687 -- Check number of usages which exist for this attribute. If the attribute is not a mandatory
688 --If usages at this point are = 0 delete the attribute row too.
689 --
690
691 select count(*)
692 into l_usage_count
693 from ame_attribute_usages
694 where attribute_id = p_attribute_id
695 and l_effective_date between start_date and
696 nvl(end_date - ame_util.oneSecond,sysdate);
697
698 if l_usage_count = 0 then
699 /* if this is a REQUIRED attribute, do not delete its conditions and the attribute itself
700 otherwise, delete all the conditions and the attribute itself, as no other usage exists
701 */
702 open c_Sel2;
703 fetch c_Sel2 into l_exists;
704 if c_Sel2%notfound then
705 close c_sel2;
706 /*Its not a required attribute, so select all the conditions to delete */
707 for con_rec in c_Sel1
708 loop
709 ame_condition_api.delete_ame_condition
710 (p_condition_id => con_rec.condition_id
711 ,p_object_version_number => con_rec.object_version_number
712 ,p_start_date => l_con_start_date
713 ,p_end_date => l_con_end_date
714 );
715 end loop;
716
717 select object_version_number
718 into l_atr_object_version_number
719 from ame_attributes
720 where attribute_id = p_attribute_id
721 and l_effective_date between start_date
722 and nvl(end_date - ame_util.oneSecond,sysdate);
723
724 ame_atr_del.del(p_effective_date => sysdate
725 ,p_datetrack_mode => 'DELETE'
726 ,p_attribute_id => p_attribute_id
727 ,p_object_version_number=> l_atr_object_version_number
728 ,p_start_date => l_atr_start_date
729 ,p_end_date => l_atr_end_date
730 );
731 else
732 close c_Sel2;
733 end if;
734 end if;
735
736 --
737 -- Call After Process User Hook
738 --
739 begin
740 ame_attribute_bk5.delete_ame_attribute_usage_a
741 (p_attribute_id => p_attribute_id
742 ,p_application_id => p_application_id
743 ,p_object_version_number => l_atu_object_version_number
744 ,p_start_date => l_atu_start_date
745 ,p_end_date => l_atu_end_date
746 );
747 exception
748 when hr_api.cannot_find_prog_unit then
749 hr_api.cannot_find_prog_unit_error
750 (p_module_name => 'delete_ame_attribute_usage'
751 ,p_hook_type => 'AP'
752 );
753 end;
754 --
755 -- When in validation only mode raise the Validate_Enabled exception
756 --
757 if p_validate then
758 raise hr_api.validate_enabled;
759 end if;
760 --
761 -- Set all IN OUT and OUT parameters with out values
762 --
763 p_object_version_number := l_atu_object_version_number;
764 p_start_date := l_atu_start_date;
765 p_end_date := l_atu_end_date;
766 --
767 hr_utility.set_location(' Leaving:'||l_proc, 70);
768 exception
769 when hr_api.validate_enabled then
770 --
771 -- As the Validate_Enabled exception has been raised
772 -- we must rollback to the savepoint
773 --
774 rollback to delete_ame_attribute_usage;
775 --
776 -- Reset IN OUT parameters and set OUT parameters
777 -- (Any key or derived arguments must be set to null
778 -- when validation only mode is being used.)
779 --
780 p_start_date := null;
781 p_end_date := null;
782 hr_utility.set_location(' Leaving:'||l_proc, 80);
783 when others then
784 --
785 -- A validation or unexpected error has occured
786 --
787 rollback to delete_ame_attribute_usage;
788 --
789 -- Reset IN OUT parameters and set all
790 -- OUT parameters, including warnings, to null
791 --
792 p_start_date := null;
793 p_end_date := null;
794 hr_utility.set_location(' Leaving:'||l_proc, 90);
795 raise;
796 end delete_ame_attribute_usage;
797 --+
798 --+
799 --+
800 function calculateUseCount(attributeIdIn in integer,
801 applicationIdIn in integer) return integer as
802 cursor ruleCursor(applicationIdIn in integer) is
803 select rule_id
804 from ame_rule_usages
805 where
806 ame_rule_usages.item_id = applicationIdIn and
807 ((sysdate between ame_rule_usages.start_date and
808 nvl(ame_rule_usages.end_date - ame_util.oneSecond, sysdate)) or
809 (sysdate < ame_rule_usages.start_date and
810 ame_rule_usages.start_date < nvl(ame_rule_usages.end_date,
811 ame_rule_usages.start_date + ame_util.oneSecond)));
812 ruleCount integer;
813 tempCount integer;
814 useCount integer;
815 begin
816 useCount := 0;
817 for tempRule in ruleCursor(applicationIdIn => applicationIdIn) loop
818 select count(*)
819 into tempCount
820 from
821 ame_conditions,
822 ame_condition_usages
823 where
824 ame_conditions.attribute_id = attributeIdIn and
825 ame_conditions.condition_id = ame_condition_usages.condition_id and
826 ame_condition_usages.rule_id = tempRule.rule_id and
827 sysdate between ame_conditions.start_date and
828 nvl(ame_conditions.end_date - ame_util.oneSecond, sysdate) and
829 ((sysdate between ame_condition_usages.start_date and
830 nvl(ame_condition_usages.end_date - ame_util.oneSecond, sysdate)) or
831 (sysdate < ame_condition_usages.start_date and
832 ame_condition_usages.start_date < nvl(ame_condition_usages.end_date,
833 ame_condition_usages.start_date + ame_util.oneSecond)));
834 if(tempCount > 0) then
835 useCount := useCount + 1;
836 else
837 select count(*)
838 into tempCount
839 from
840 ame_mandatory_attributes,
841 ame_actions,
842 ame_action_usages
843 where
844 ame_mandatory_attributes.attribute_id = attributeIdIn and
845 ame_mandatory_attributes.action_type_id = ame_actions.action_type_id and
846 ame_actions.action_id = ame_action_usages.action_id and
847 ame_action_usages.rule_id = tempRule.rule_id and
848 sysdate between ame_mandatory_attributes.start_date and
849 nvl(ame_mandatory_attributes.end_date - ame_util.oneSecond, sysdate) and
850 sysdate between ame_actions.start_date and
851 nvl(ame_actions.end_date - ame_util.oneSecond, sysdate) and
852 ((sysdate between ame_action_usages.start_date and
853 nvl(ame_action_usages.end_date - ame_util.oneSecond, sysdate)) or
854 (sysdate < ame_action_usages.start_date and
855 ame_action_usages.start_date < nvl(ame_action_usages.end_date,
856 ame_action_usages.start_date + ame_util.oneSecond)));
857 if(tempCount > 0) then
858 useCount := useCount + 1;
859 end if;
860 end if;
861 end loop;
862 return(useCount);
863 exception
864 when others then
865 fnd_message.set_name('PER','AME_9_INV_ATTRIBUTE_USAGE');
866 hr_multi_message.add (p_associated_column1 => 'ATTRIBUTE_ID');
867 raise;
868 return(null);
869 end calculateUseCount;
870 --+
871 --+
872 --+
873 procedure updateUseCount(p_attribute_id in integer
874 ,p_application_id in integer
875 ,p_atu_object_version_number in integer) as
876 useCount integer;
877 l_atu_start_date date;
878 l_atu_end_date date;
879 l_atu_object_version_number integer;
880 begin
881 l_atu_object_version_number := p_atu_object_version_number;
882 useCount := calculateUseCount(attributeIdIn => p_attribute_id
883 ,applicationIdIn => p_application_id);
884 ame_atu_upd.upd(p_attribute_id => p_attribute_id
885 ,p_datetrack_mode => hr_api.g_correction
886 ,p_application_id => p_application_id
887 ,p_use_count => useCount
888 ,p_effective_date => sysdate
889 ,p_object_version_number => l_atu_object_version_number
890 ,p_start_date => l_atu_start_date
891 ,p_end_date => l_atu_end_date
892 );
893 end updateUseCount;
894 --+
895 end ame_attribute_api;