[Home] [Help]
PACKAGE BODY: APPS.OTA_CATEGORY_USAGE_API
Source
1 Package Body ota_category_usage_api as
2 /* $Header: otctuapi.pkb 120.0.12010000.2 2009/07/24 10:52:06 shwnayak ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_category_usage_api.';
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< get_act_def_name_with_cat_id >-----------------|
12 -- ----------------------------------------------------------------------------
13 function get_act_def_name_with_cat_id
14 (p_act_def_name in varchar2
15 ,p_cat_usg_id in number
16 ) return varchar2 is
17
18 MAX_ACT_DEF_NAME_LEN constant number := 240;
19
20 l_new_act_def_len number;
21 l_cat_id_len number;
22 l_act_def_name ota_activity_definitions_tl.name%type := p_act_def_name;
23 l_old_act_def_len number := length(l_act_def_name);
24
25 begin
26 --
27 l_new_act_def_len := length(l_act_def_name||'-'||p_cat_usg_id);
28 --
29 If l_new_act_def_len > MAX_ACT_DEF_NAME_LEN then
30 --
31 l_cat_id_len := l_new_act_def_len - MAX_ACT_DEF_NAME_LEN;
32 l_act_def_name := substr(l_act_def_name,1,l_old_act_def_len - l_cat_id_len)||'-'||to_char(p_cat_usg_id);
33 --
34 Else
35 --
36 l_act_def_name := l_act_def_name||'-'||p_cat_usg_id;
37 --
38 End if;
39 --
40 return l_act_def_name;
41 end get_act_def_name_with_cat_id;
42 --
43 -- ----------------------------------------------------------------------------
44 -- |---------------------------< CREATE_CATEGORY >----------------------------|
45 -- ----------------------------------------------------------------------------
46 --
47 procedure Create_category
48 (p_validate in boolean default false
49 ,p_effective_date in date
50 ,p_business_group_id in number
51 ,p_category in varchar2
52 ,p_type in varchar2
53 ,p_description in varchar2
54 ,p_parent_cat_usage_id in number
55 ,p_synchronous_flag in varchar2
56 ,p_online_flag in varchar2 default null
57 ,p_attribute_category in varchar2 default null
58 ,p_attribute1 in varchar2 default null
59 ,p_attribute2 in varchar2 default null
60 ,p_attribute3 in varchar2 default null
61 ,p_attribute4 in varchar2 default null
62 ,p_attribute5 in varchar2 default null
63 ,p_attribute6 in varchar2 default null
64 ,p_attribute7 in varchar2 default null
65 ,p_attribute8 in varchar2 default null
66 ,p_attribute9 in varchar2 default null
67 ,p_attribute10 in varchar2 default null
68 ,p_attribute11 in varchar2 default null
69 ,p_attribute12 in varchar2 default null
70 ,p_attribute13 in varchar2 default null
71 ,p_attribute14 in varchar2 default null
72 ,p_attribute15 in varchar2 default null
73 ,p_attribute16 in varchar2 default null
74 ,p_attribute17 in varchar2 default null
75 ,p_attribute18 in varchar2 default null
76 ,p_attribute19 in varchar2 default null
77 ,p_attribute20 in varchar2 default null
78 ,p_data_source in varchar2 default null
79 ,p_start_date_active in date
80 ,p_end_date_active in date default null
81 ,p_category_usage_id out nocopy number
82 ,p_object_version_number out nocopy number
83 ,p_comments in varchar2 default null
84 ,p_user_group_id in number default null
85 ) is
86 --
87 -- Declare cursors and local variables
88 --
89 l_proc varchar2(72) := g_package||'Create_Category';
90 l_category_usage_id number;
91 l_object_version_number number;
92 l_act_object_version_number ota_activity_definitions.object_version_number%TYPE;
93 l_activity_id ota_activity_definitions.activity_id%TYPE;
94 l_effective_date date;
95 l_category varchar2(240);
96
97 begin
98 hr_utility.set_location('Entering:'|| l_proc, 10);
99 --
100 -- Issue a savepoint
101 --
102 savepoint CREATE_CATEGORY;
103 --
104 -- Truncate the time portion from all IN date parameters
105 --
106 l_effective_date := trunc(p_effective_date);
107 l_category := rtrim(p_category);
108 --
109 -- Call Before Process User Hook
110 --
111 begin
112 ota_category_usage_bk1.create_category_b
113 (p_effective_date => l_effective_date
114 ,p_business_group_id => p_business_group_id
115 ,p_category => l_category
116 ,p_type => p_type
117 ,p_parent_cat_usage_id => p_parent_cat_usage_id
118 ,p_synchronous_flag => p_synchronous_flag
119 ,p_online_flag => p_online_flag
120 ,p_attribute_category => p_attribute_category
121 ,p_attribute1 => p_attribute1
122 ,p_attribute2 => p_attribute2
123 ,p_attribute3 => p_attribute3
124 ,p_attribute4 => p_attribute4
125 ,p_attribute5 => p_attribute5
126 ,p_attribute6 => p_attribute6
127 ,p_attribute7 => p_attribute7
128 ,p_attribute8 => p_attribute8
129 ,p_attribute9 => p_attribute9
130 ,p_attribute10 => p_attribute10
131 ,p_attribute11 => p_attribute11
132 ,p_attribute12 => p_attribute12
133 ,p_attribute13 => p_attribute13
134 ,p_attribute14 => p_attribute14
135 ,p_attribute15 => p_attribute15
136 ,p_attribute16 => p_attribute16
137 ,p_attribute17 => p_attribute17
138 ,p_attribute18 => p_attribute18
139 ,p_attribute19 => p_attribute19
140 ,p_attribute20 => p_attribute20
141 ,p_data_source => p_data_source
142 ,p_start_date_active => p_start_date_active
143 ,p_end_date_active => p_end_date_active
144 ,p_comments => p_comments
145 );
146 exception
147 when hr_api.cannot_find_prog_unit then
148 hr_api.cannot_find_prog_unit_error
149 (p_module_name => 'CREATE_CATEGORY'
150 ,p_hook_type => 'BP'
151 );
152 end;
153 --
154 -- Validation in addition to Row Handlers
155 --
156 --
157 -- Process Logic (Base table)
158 --
159 ota_ctu_ins.ins
160 (p_effective_date => l_effective_date
161 ,p_business_group_id => p_business_group_id
162 ,p_category => l_category
163 ,p_type => p_type
164 ,p_parent_cat_usage_id => p_parent_cat_usage_id
165 ,p_synchronous_flag => p_synchronous_flag
166 ,p_online_flag => p_online_flag
167 ,p_attribute_category => p_attribute_category
168 ,p_attribute1 => p_attribute1
169 ,p_attribute2 => p_attribute2
170 ,p_attribute3 => p_attribute3
171 ,p_attribute4 => p_attribute4
172 ,p_attribute5 => p_attribute5
173 ,p_attribute6 => p_attribute6
174 ,p_attribute7 => p_attribute7
175 ,p_attribute8 => p_attribute8
176 ,p_attribute9 => p_attribute9
177 ,p_attribute10 => p_attribute10
178 ,p_attribute11 => p_attribute11
179 ,p_attribute12 => p_attribute12
180 ,p_attribute13 => p_attribute13
181 ,p_attribute14 => p_attribute14
182 ,p_attribute15 => p_attribute15
183 ,p_attribute16 => p_attribute16
184 ,p_attribute17 => p_attribute17
185 ,p_attribute18 => p_attribute18
186 ,p_attribute19 => p_attribute19
187 ,p_attribute20 => p_attribute20
188 ,p_data_source => p_data_source
189 ,p_start_date_active => p_start_date_active
190 ,p_end_date_active => p_end_date_active
191 ,p_category_usage_id => l_category_usage_id
192 ,p_object_version_number => l_object_version_number
193 ,p_comments => p_comments
194 ,p_user_group_id => p_user_group_id
195
196 );
197 --
198 -- Process Logic (MLS table)
199 --
200 ota_ctt_ins.ins_tl
201 (p_effective_date => p_effective_date
202 ,p_language_code => USERENV('LANG')
203 ,p_category_usage_id => l_category_usage_id
204 ,p_category => l_category
205 ,p_description => p_description
206 );
207 --
208 --Call activity type API to create corresponding activity type
209 IF p_type = 'C' THEN
210 --
211 ota_tad_api.ins(p_activity_id => l_activity_id
212 ,p_business_group_id => p_business_group_id
213 ,p_name => get_act_def_name_with_cat_id( l_category,l_category_usage_id)
214 ,p_description => p_description
215 ,p_object_version_number => l_act_object_version_number
216 ,p_category_usage_id => l_category_usage_id
217 ,p_multiple_con_versions_flag => 'Y' );
218
219 ota_adt_ins.ins_tl(p_effective_date => p_effective_date
220 ,p_language_code => USERENV('LANG')
221 ,p_activity_id => l_activity_id
222 ,p_name => get_act_def_name_with_cat_id( l_category,l_category_usage_id)
223 ,p_description => p_description);
224 --end call activity type APIs to create activity type for the category
225 -- Call After Process User Hook
226 END IF;
227 --
228
229 begin
230 ota_category_usage_bk1.create_category_a
231 (p_effective_date => l_effective_date
232 ,p_business_group_id => p_business_group_id
233 ,p_category => l_category
234 ,p_type => p_type
235 ,p_parent_cat_usage_id => p_parent_cat_usage_id
236 ,p_synchronous_flag => p_synchronous_flag
237 ,p_online_flag => p_online_flag
238 ,p_attribute_category => p_attribute_category
239 ,p_attribute1 => p_attribute1
240 ,p_attribute2 => p_attribute2
241 ,p_attribute3 => p_attribute3
242 ,p_attribute4 => p_attribute4
243 ,p_attribute5 => p_attribute5
244 ,p_attribute6 => p_attribute6
245 ,p_attribute7 => p_attribute7
246 ,p_attribute8 => p_attribute8
247 ,p_attribute9 => p_attribute9
248 ,p_attribute10 => p_attribute10
249 ,p_attribute11 => p_attribute11
250 ,p_attribute12 => p_attribute12
251 ,p_attribute13 => p_attribute13
252 ,p_attribute14 => p_attribute14
253 ,p_attribute15 => p_attribute15
254 ,p_attribute16 => p_attribute16
255 ,p_attribute17 => p_attribute17
256 ,p_attribute18 => p_attribute18
257 ,p_attribute19 => p_attribute19
258 ,p_attribute20 => p_attribute20
259 ,p_data_source => p_data_source
260 ,p_start_date_active => p_start_date_active
261 ,p_end_date_active => p_end_date_active
262 ,p_category_usage_id => l_category_usage_id
263 ,p_object_version_number => l_object_version_number
264 ,p_comments => p_comments
265
266 );
267 exception
268 when hr_api.cannot_find_prog_unit then
269 hr_api.cannot_find_prog_unit_error
270 (p_module_name => 'CREATE_CATEGORY'
271 ,p_hook_type => 'AP'
272 );
273 end;
274 --
275 -- When in validation only mode raise the Validate_Enabled exception
276 --
277 if p_validate then
278 raise hr_api.validate_enabled;
279 end if;
280 --
281 -- Set all output arguments
282 --
283 p_category_usage_id := l_category_usage_id;
284 p_object_version_number := l_object_version_number;
285 --
286 hr_utility.set_location(' Leaving:'||l_proc, 70);
287 exception
288 when hr_api.validate_enabled then
289 --
290 -- As the Validate_Enabled exception has been raised
291 -- we must rollback to the savepoint
292 --
293 rollback to CREATE_CATEGORY;
294 --
295 -- Only set output warning arguments
296 -- (Any key or derived arguments must be set to null
297 -- when validation only mode is being used.)
298 --
299 p_category_usage_id := null;
300 p_object_version_number := null;
301 hr_utility.set_location(' Leaving:'||l_proc, 80);
302 when others then
303 --
304 -- A validation or unexpected error has occured
305 --
306 rollback to CREATE_CATEGORY;
307 p_category_usage_id := null;
308 p_object_version_number := null;
309 hr_utility.set_location(' Leaving:'||l_proc, 90);
310 raise;
311 end create_category;
312 -- ----------------------------------------------------------------------------
313 -- |--------------------------< UPDATE_CATEGORY >-----------------------------|
314 -- ----------------------------------------------------------------------------
315 --
316 procedure Update_category
317 (p_validate in boolean default false
318 ,p_effective_date in date
319 ,p_category_usage_id in number
320 ,p_object_version_number in out nocopy number
321 ,p_category in varchar2
322 ,p_type in varchar2
323 ,p_description in varchar2
324 ,p_parent_cat_usage_id in number
325 ,p_synchronous_flag in varchar2
326 ,p_online_flag in varchar2 default null
327 ,p_attribute_category in varchar2 default null
328 ,p_attribute1 in varchar2 default null
329 ,p_attribute2 in varchar2 default null
330 ,p_attribute3 in varchar2 default null
331 ,p_attribute4 in varchar2 default null
332 ,p_attribute5 in varchar2 default null
333 ,p_attribute6 in varchar2 default null
334 ,p_attribute7 in varchar2 default null
335 ,p_attribute8 in varchar2 default null
336 ,p_attribute9 in varchar2 default null
337 ,p_attribute10 in varchar2 default null
338 ,p_attribute11 in varchar2 default null
339 ,p_attribute12 in varchar2 default null
340 ,p_attribute13 in varchar2 default null
341 ,p_attribute14 in varchar2 default null
342 ,p_attribute15 in varchar2 default null
343 ,p_attribute16 in varchar2 default null
344 ,p_attribute17 in varchar2 default null
345 ,p_attribute18 in varchar2 default null
346 ,p_attribute19 in varchar2 default null
347 ,p_attribute20 in varchar2 default null
348 ,p_data_source in varchar2 default null
349 ,p_start_date_active in date
350 ,p_end_date_active in date default null
351 ,p_comments in varchar2 default null
352
353 ) is
354 --
355 -- Declare cursors and local variables
356 -- get the activity id for the category_usage_id
357 CURSOR get_activity_id
358 IS
359 SELECT activity_id,
360 object_version_number
361 FROM ota_activity_definitions
362 WHERE category_usage_id = p_category_usage_id;
363
364 --
365 l_proc varchar2(72) := g_package||'Update_category';
366 l_effective_date date;
367 l_object_version_number number := p_object_version_number;
368 l_act_object_version_number ota_activity_definitions.object_version_number%TYPE;
369 l_activity_id ota_activity_definitions.activity_id%TYPE;
370 l_category varchar2(240);
371
372 begin
373 hr_utility.set_location('Entering:'|| l_proc, 10);
374 --
375 -- Issue a savepoint
376 --
377 savepoint UPDATE_CATEGORY;
378 --
379 -- Truncate the time portion from all IN date parameters
380 --
381 l_effective_date := trunc(p_effective_date);
382 --
383 l_category := rtrim(p_category);
384 --
385 -- Call Before Process User Hook
386 --
387 begin
388 ota_category_usage_bk2.update_category_b
389 (p_effective_date => l_effective_date
390 ,p_category_usage_id => p_category_usage_id
391 ,p_object_version_number => p_object_version_number
392 ,p_category => l_category
393 ,p_type => p_type
394 ,p_parent_cat_usage_id => p_parent_cat_usage_id
395 ,p_synchronous_flag => p_synchronous_flag
396 ,p_online_flag => p_online_flag
397 ,p_attribute_category => p_attribute_category
398 ,p_attribute1 => p_attribute1
399 ,p_attribute2 => p_attribute2
400 ,p_attribute3 => p_attribute3
401 ,p_attribute4 => p_attribute4
402 ,p_attribute5 => p_attribute5
403 ,p_attribute6 => p_attribute6
404 ,p_attribute7 => p_attribute7
405 ,p_attribute8 => p_attribute8
406 ,p_attribute9 => p_attribute9
407 ,p_attribute10 => p_attribute10
408 ,p_attribute11 => p_attribute11
409 ,p_attribute12 => p_attribute12
410 ,p_attribute13 => p_attribute13
411 ,p_attribute14 => p_attribute14
412 ,p_attribute15 => p_attribute15
413 ,p_attribute16 => p_attribute16
414 ,p_attribute17 => p_attribute17
415 ,p_attribute18 => p_attribute18
416 ,p_attribute19 => p_attribute19
417 ,p_attribute20 => p_attribute20
418 ,p_data_source => p_data_source
419 ,p_start_date_active => p_start_date_active
420 ,p_end_date_active => p_end_date_active
421 ,p_comments => p_comments
422 );
423 exception
424 when hr_api.cannot_find_prog_unit then
425 hr_api.cannot_find_prog_unit_error
426 (p_module_name => 'UPDATE_CATEGORY'
427 ,p_hook_type => 'BP'
428 );
429 end;
430 --
431 -- Validation in addition to Row Handlers
432 --
433 /*
434 ota_ctu_bus.Chk_valid_parent_category
435 (p_parent_cat_usage_id => p_parent_cat_usage_id
436 ,p_category_usage_id => p_category_usage_id
437 );
438 */
439 --
440 -- Process Logic (Base table)
441 --
442 ota_ctu_upd.upd
443 (p_effective_date => l_effective_date
444 ,p_category_usage_id => p_category_usage_id
445 ,p_object_version_number => p_object_version_number
446 ,p_category => l_category
447 ,p_type => p_type
448 ,p_parent_cat_usage_id => p_parent_cat_usage_id
449 ,p_synchronous_flag => p_synchronous_flag
450 ,p_online_flag => p_online_flag
451 ,p_attribute_category => p_attribute_category
452 ,p_attribute1 => p_attribute1
453 ,p_attribute2 => p_attribute2
454 ,p_attribute3 => p_attribute3
455 ,p_attribute4 => p_attribute4
456 ,p_attribute5 => p_attribute5
457 ,p_attribute6 => p_attribute6
458 ,p_attribute7 => p_attribute7
459 ,p_attribute8 => p_attribute8
460 ,p_attribute9 => p_attribute9
461 ,p_attribute10 => p_attribute10
462 ,p_attribute11 => p_attribute11
463 ,p_attribute12 => p_attribute12
464 ,p_attribute13 => p_attribute13
465 ,p_attribute14 => p_attribute14
466 ,p_attribute15 => p_attribute15
467 ,p_attribute16 => p_attribute16
468 ,p_attribute17 => p_attribute17
469 ,p_attribute18 => p_attribute18
470 ,p_attribute19 => p_attribute19
471 ,p_attribute20 => p_attribute20
472 ,p_data_source => p_data_source
473 ,p_start_date_active => p_start_date_active
474 ,p_end_date_active => p_end_date_active
475 ,p_comments => p_comments
476
477 );
478 --
479 -- Process Logic (MLS table)
480 --
481 ota_ctt_upd.upd_tl
482 (p_effective_date => p_effective_date
483 ,p_language_code => USERENV('LANG')
484 ,p_category_usage_id => p_category_usage_id
485 ,p_category => l_category
486 ,p_description => p_description
487 );
488 --
489 IF p_type = 'C' THEN-- if category type is Classification
490 --Call activity type API to update corresponding activity type
491 OPEN get_activity_id;
492 FETCH get_activity_id INTO l_activity_id, l_act_object_version_number;
493 CLOSE get_activity_id;
494
495 IF l_activity_id IS NOT NULL THEN
496
497 ota_tad_api.upd(p_activity_id => l_activity_id
498 ,p_name => get_act_def_name_with_cat_id( l_category,p_category_usage_id)
499 ,p_description => p_description
500 ,p_object_version_number => l_act_object_version_number
501 ,p_category_usage_id => p_category_usage_id
502 ,p_multiple_con_versions_flag => 'Y');
503
504 ota_adt_upd.upd_tl(p_effective_date => p_effective_date
505 ,p_language_code => USERENV('LANG')
506 ,p_activity_id => l_activity_id
507 ,p_name => get_act_def_name_with_cat_id( l_category,p_category_usage_id)
508 ,p_description => p_description);
509 END IF;
510 --end call activity type APIs to update activity type for the category
511 END IF; -- if category type is Classification
512 -- Call After Process User Hook
513 --
514 begin
515 ota_category_usage_bk2.update_category_a
516 (p_effective_date => l_effective_date
517 ,p_category_usage_id => p_category_usage_id
518 ,p_object_version_number => p_object_version_number
519 ,p_category => l_category
520 ,p_type => p_type
521 ,p_parent_cat_usage_id => p_parent_cat_usage_id
522 ,p_synchronous_flag => p_synchronous_flag
523 ,p_online_flag => p_online_flag
524 ,p_attribute_category => p_attribute_category
525 ,p_attribute1 => p_attribute1
526 ,p_attribute2 => p_attribute2
527 ,p_attribute3 => p_attribute3
528 ,p_attribute4 => p_attribute4
529 ,p_attribute5 => p_attribute5
530 ,p_attribute6 => p_attribute6
531 ,p_attribute7 => p_attribute7
532 ,p_attribute8 => p_attribute8
533 ,p_attribute9 => p_attribute9
534 ,p_attribute10 => p_attribute10
535 ,p_attribute11 => p_attribute11
536 ,p_attribute12 => p_attribute12
537 ,p_attribute13 => p_attribute13
538 ,p_attribute14 => p_attribute14
539 ,p_attribute15 => p_attribute15
540 ,p_attribute16 => p_attribute16
541 ,p_attribute17 => p_attribute17
542 ,p_attribute18 => p_attribute18
543 ,p_attribute19 => p_attribute19
544 ,p_attribute20 => p_attribute20
545 ,p_data_source => p_data_source
546 ,p_start_date_active => p_start_date_active
547 ,p_end_date_active => p_end_date_active
548 ,p_comments => p_comments
549
550 );
551 exception
552 when hr_api.cannot_find_prog_unit then
553 hr_api.cannot_find_prog_unit_error
554 (p_module_name => 'UPDATE_CATEGORY'
555 ,p_hook_type => 'AP'
556 );
557 end;
558 --
559 -- When in validation only mode raise the Validate_Enabled exception
560 --
561 if p_validate then
562 raise hr_api.validate_enabled;
563 end if;
564 --
565 -- Set all output arguments
566 --
567 hr_utility.set_location(' Leaving:'||l_proc, 70);
568 exception
569 when hr_api.validate_enabled then
570 --
571 -- As the Validate_Enabled exception has been raised
572 -- we must rollback to the savepoint
573 --
574 rollback to UPDATE_CATEGORY;
575 --
576 -- Only set output warning arguments
577 -- (Any key or derived arguments must be set to null
578 -- when validation only mode is being used.)
579 --
580 p_object_version_number := null;
581 hr_utility.set_location(' Leaving:'||l_proc, 80);
582 when others then
583 --
584 -- A validation or unexpected error has occured
585 --
586 rollback to UPDATE_CATEGORY;
587 p_object_version_number := l_object_version_number;
588 hr_utility.set_location(' Leaving:'||l_proc, 90);
589 raise;
590 end update_category;
591 --
592 -- ----------------------------------------------------------------------------
593 -- |--------------------------< DELETE_CATEGORY >-----------------------------|
594 -- ----------------------------------------------------------------------------
595 --
596 procedure delete_category
597 (p_validate in boolean default false
598 ,p_category_usage_id in number
599 ,p_object_version_number in number
600 ) is
601 --
602 -- Declare cursors and local variables
603 --
604 -- get the activity id for the category_usage_id
605 CURSOR get_activity_id
606 IS
607 SELECT activity_id,
608 object_version_number
609 FROM ota_activity_definitions
610 WHERE category_usage_id = p_category_usage_id;
611
612 --
613 l_proc varchar2(72) := g_package||'Delete_category';
614 l_budget_version_id number;
615 l_act_object_version_number ota_activity_definitions.object_version_number%TYPE;
616 l_activity_id ota_activity_definitions.activity_id%TYPE;
617
618
619
620 --
621 --
622 begin
623 hr_utility.set_location('Entering:'|| l_proc, 10);
624 --
625 -- Issue a savepoint
626 --
627 savepoint DELETE_CATEGORY;
628 --
629 -- Truncate the time portion from all IN date parameters
630 --
631 --
632 -- Call Before Process User Hook
633 --
634 begin
635 ota_category_usage_bk3.delete_category_b
636 (p_category_usage_id => p_category_usage_id
637 ,p_object_version_number => p_object_version_number
638 );
639 exception
640 when hr_api.cannot_find_prog_unit then
641 hr_api.cannot_find_prog_unit_error
642 (p_module_name => 'DELETE_CATEGORY'
643 ,p_hook_type => 'BP'
644 );
645 end;
646 --
647 -- Validation in addition to Row Handlers
648 --
649 --
650 -- Process Logic (MLS table
651 --
652 ota_ctt_del.del_tl
653 (p_category_usage_id => p_category_usage_id
654 );
655 --
656 -- Process Logic (Base table)
657 --
658 ota_ctu_del.del
659 (p_category_usage_id => p_category_usage_id
660 ,p_object_version_number => p_object_version_number
661 );
662 --
663 --Call activity type API to delete corresponding activity type
664 OPEN get_activity_id;
665 FETCH get_activity_id INTO l_activity_id, l_act_object_version_number;
666 CLOSE get_activity_id;
667
668 IF l_activity_id IS NOT NULL THEN
669
670 ota_tad_api.del(p_activity_id => l_activity_id,
671 p_object_version_number => l_act_object_version_number);
672
673 ota_adt_del.del_tl(p_activity_id => l_activity_id );
674 END IF;
675 --end call activity type APIs to delete activity type for the category
676 --
677 -- Call After Process User Hook
678 --
679 begin
680 ota_category_usage_bk3.delete_category_a
681 (p_category_usage_id => p_category_usage_id
682 ,p_object_version_number => p_object_version_number
683 );
684 exception
685 when hr_api.cannot_find_prog_unit then
686 hr_api.cannot_find_prog_unit_error
687 (p_module_name => 'DELETE_CATEGORY'
688 ,p_hook_type => 'AP'
689 );
690 end;
691 --
692 -- When in validation only mode raise the Validate_Enabled exception
693 --
694 if p_validate then
695 raise hr_api.validate_enabled;
696 end if;
697 --
698 -- Set all output arguments
699 --
700 --
701 hr_utility.set_location(' Leaving:'||l_proc, 170);
702 exception
703 when hr_api.validate_enabled then
704 --
705 -- As the Validate_Enabled exception has been raised
706 -- we must rollback to the savepoint
707 --
708 rollback to DELETE_CATEGORY;
709 --
710 -- Only set output warning arguments
711 -- (Any key or derived arguments must be set to null
712 -- when validation only mode is being used.)
713 --
714 hr_utility.set_location(' Leaving:'||l_proc, 180);
715 when others then
716 --
717 -- A validation or unexpected error has occured
718 --
719 rollback to DELETE_CATEGORY;
720 hr_utility.set_location(' Leaving:'||l_proc, 190);
721 raise;
722 end delete_category;
723 --
724 end OTA_CATEGORY_USAGE_API;