[Home] [Help]
PACKAGE BODY: APPS.OTA_LP_CATEGORY_API
Source
1 PACKAGE BODY OTA_LP_CATEGORY_API as
2 /* $Header: otlciapi.pkb 115.2 2003/12/30 18:49:37 dhmulia noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_LP_CATEGORY_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_lp_cat_inclusion >------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_lp_cat_inclusion
13 (p_validate in boolean default false,
14 p_effective_date in date,
15 p_learning_path_id in number,
16 p_category_usage_id in number ,
17 p_object_version_number out nocopy number,
18 p_start_date_active in date ,
19 p_end_date_active in date ,
20 p_primary_flag in varchar2 default 'N',
21
22 p_attribute_category in varchar2 ,
23 p_attribute1 in varchar2 ,
24 p_attribute2 in varchar2 ,
25 p_attribute3 in varchar2 ,
26 p_attribute4 in varchar2 ,
27 p_attribute5 in varchar2 ,
28 p_attribute6 in varchar2 ,
29 p_attribute7 in varchar2 ,
30 p_attribute8 in varchar2 ,
31 p_attribute9 in varchar2 ,
32 p_attribute10 in varchar2 ,
33 p_attribute11 in varchar2 ,
34 p_attribute12 in varchar2 ,
35 p_attribute13 in varchar2 ,
36 p_attribute14 in varchar2 ,
37 p_attribute15 in varchar2 ,
38 p_attribute16 in varchar2 ,
39 p_attribute17 in varchar2 ,
40 p_attribute18 in varchar2 ,
41 p_attribute19 in varchar2 ,
42 p_attribute20 in varchar2
43
44 )
45 is
46 --
47 -- Declare cursors and local variables
48 --
49 l_proc varchar2(72) := g_package||' create_lp_cat_inclusion ';
50 l_object_version_number number;
51 l_effective_date date;
52
53 begin
54 hr_utility.set_location('Entering:'|| l_proc, 10);
55 --
56 -- Issue a savepoint
57 --
58 savepoint create_lp_cat_inclusion;
59 --
60 -- Truncate the time portion from all IN date parameters
61 --
62 l_effective_date := trunc(p_effective_date);
63
64 --
65 -- Call Before Process User Hook
66 --
67 begin
68 ota_lp_category_bk1.create_lp_cat_inclusion_b
69 (p_effective_date => l_effective_date,
70 p_learning_path_id => p_learning_path_id ,
71 p_object_version_number => p_object_version_number,
72 p_attribute_category => p_attribute_category,
73 p_attribute1 => p_attribute1,
74 p_attribute2 => p_attribute2,
75 p_attribute3 => p_attribute3,
76 p_attribute4 => p_attribute4,
77 p_attribute5 => p_attribute5 ,
78 p_attribute6 => p_attribute6,
79 p_attribute7 => p_attribute7,
80 p_attribute8 => p_attribute8,
81 p_attribute9 => p_attribute9,
82 p_attribute10 => p_attribute10,
83 p_attribute11 => p_attribute11,
84 p_attribute12 => p_attribute12,
85 p_attribute13 => p_attribute13,
86 p_attribute14 => p_attribute14,
87 p_attribute15 => p_attribute15,
88 p_attribute16 => p_attribute16,
89 p_attribute17 => p_attribute17,
90 p_attribute18 => p_attribute18,
91 p_attribute19 => p_attribute19,
92 p_attribute20 => p_attribute20,
93 p_start_date_active => p_start_date_active,
94 p_end_date_active => p_end_date_active,
95 p_primary_flag => p_primary_flag,
96 p_category_usage_id => p_category_usage_id
97 );
98 exception
99 when hr_api.cannot_find_prog_unit then
100 hr_api.cannot_find_prog_unit_error
101 (p_module_name => 'create_lp_cat_inclusion_b'
102 ,p_hook_type => 'BP'
103 );
104 end;
105 --
106 -- Validation in addition to Row Handlers
107 --
108 --
109 -- Process Logic
110 --
111 ota_lci_ins.ins
112 (p_effective_date => l_effective_date,
113 p_learning_path_id => p_learning_path_id ,
114 p_object_version_number => p_object_version_number,
115 p_attribute_category => p_attribute_category,
116 p_attribute1 => p_attribute1,
117 p_attribute2 => p_attribute2,
118 p_attribute3 => p_attribute3,
119 p_attribute4 => p_attribute4,
120 p_attribute5 => p_attribute5 ,
121 p_attribute6 => p_attribute6,
122 p_attribute7 => p_attribute7,
123 p_attribute8 => p_attribute8,
124 p_attribute9 => p_attribute9,
125 p_attribute10 => p_attribute10,
126 p_attribute11 => p_attribute11,
127 p_attribute12 => p_attribute12,
128 p_attribute13 => p_attribute13,
129 p_attribute14 => p_attribute14,
130 p_attribute15 => p_attribute15,
131 p_attribute16 => p_attribute16,
132 p_attribute17 => p_attribute17,
133 p_attribute18 => p_attribute18,
134 p_attribute19 => p_attribute19,
135 p_attribute20 => p_attribute20,
136 p_start_date_active => p_start_date_active,
137 p_end_date_active => p_end_date_active,
138 p_primary_flag => p_primary_flag,
139 p_category_usage_id => p_category_usage_id
140 );
141
142 --
143 -- Call After Process User Hook
144 --
145 begin
146 ota_lp_category_bk1.create_lp_cat_inclusion_a
147 (p_effective_date => l_effective_date,
148 p_learning_path_id => p_learning_path_id ,
149 p_object_version_number => p_object_version_number,
150 p_attribute_category => p_attribute_category,
151 p_attribute1 => p_attribute1,
152 p_attribute2 => p_attribute2,
153 p_attribute3 => p_attribute3,
154 p_attribute4 => p_attribute4,
155 p_attribute5 => p_attribute5 ,
156 p_attribute6 => p_attribute6,
157 p_attribute7 => p_attribute7,
158 p_attribute8 => p_attribute8,
159 p_attribute9 => p_attribute9,
160 p_attribute10 => p_attribute10,
161 p_attribute11 => p_attribute11,
162 p_attribute12 => p_attribute12,
163 p_attribute13 => p_attribute13,
164 p_attribute14 => p_attribute14,
165 p_attribute15 => p_attribute15,
166 p_attribute16 => p_attribute16,
167 p_attribute17 => p_attribute17,
168 p_attribute18 => p_attribute18,
169 p_attribute19 => p_attribute19,
170 p_attribute20 => p_attribute20,
171 p_start_date_active => p_start_date_active,
172 p_end_date_active => p_end_date_active,
173 p_primary_flag => p_primary_flag,
174 p_category_usage_id => p_category_usage_id
175 );
176
177 exception
178 when hr_api.cannot_find_prog_unit then
179 hr_api.cannot_find_prog_unit_error
180 (p_module_name => 'create_lp_cat_inclusion_a'
181 ,p_hook_type => 'AP'
182 );
183 end;
184 --
185 -- When in validation only mode raise the Validate_Enabled exception
186 --
187 if p_validate then
188 raise hr_api.validate_enabled;
189 end if;
190 --
191 -- Set all output arguments
192 --
193 --
194 p_object_version_number := l_object_version_number;
195 --
196 hr_utility.set_location(' Leaving:'||l_proc, 70);
197 exception
198 when hr_api.validate_enabled then
199 --
200 -- As the Validate_Enabled exception has been raised
201 -- we must rollback to the savepoint
202 --
203 rollback to create_lp_cat_inclusion;
204 --
205 -- Only set output warning arguments
206 -- (Any key or derived arguments must be set to null
207 -- when validation only mode is being used.)
208 --
209 p_object_version_number := null;
210 hr_utility.set_location(' Leaving:'||l_proc, 80);
211 when others then
212 --
213 -- A validation or unexpected error has occured
214 --
215 rollback to create_lp_cat_inclusion;
216 p_object_version_number := null;
217 hr_utility.set_location(' Leaving:'||l_proc, 90);
218 raise;
219 end create_lp_cat_inclusion ;
220 -- ----------------------------------------------------------------------------
221 -- |-------------------------< update_lp_cat_inclusion >-------------------|
222 -- ----------------------------------------------------------------------------
223 --
224 procedure update_lp_cat_inclusion
225 (p_validate in boolean default false
226 ,p_effective_date in date
227 ,p_learning_path_id in number
228 ,p_object_version_number in out nocopy number
229 ,p_attribute_category in varchar2 default hr_api.g_varchar2
230 ,p_attribute1 in varchar2 default hr_api.g_varchar2
231 ,p_attribute2 in varchar2 default hr_api.g_varchar2
232 ,p_attribute3 in varchar2 default hr_api.g_varchar2
233 ,p_attribute4 in varchar2 default hr_api.g_varchar2
234 ,p_attribute5 in varchar2 default hr_api.g_varchar2
235 ,p_attribute6 in varchar2 default hr_api.g_varchar2
236 ,p_attribute7 in varchar2 default hr_api.g_varchar2
237 ,p_attribute8 in varchar2 default hr_api.g_varchar2
238 ,p_attribute9 in varchar2 default hr_api.g_varchar2
239 ,p_attribute10 in varchar2 default hr_api.g_varchar2
240 ,p_attribute11 in varchar2 default hr_api.g_varchar2
241 ,p_attribute12 in varchar2 default hr_api.g_varchar2
242 ,p_attribute13 in varchar2 default hr_api.g_varchar2
243 ,p_attribute14 in varchar2 default hr_api.g_varchar2
244 ,p_attribute15 in varchar2 default hr_api.g_varchar2
245 ,p_attribute16 in varchar2 default hr_api.g_varchar2
246 ,p_attribute17 in varchar2 default hr_api.g_varchar2
247 ,p_attribute18 in varchar2 default hr_api.g_varchar2
248 ,p_attribute19 in varchar2 default hr_api.g_varchar2
249 ,p_attribute20 in varchar2 default hr_api.g_varchar2
250 ,p_start_date_active in date default hr_api.g_date
251 ,p_end_date_active in date default hr_api.g_date
252 ,p_primary_flag in varchar2 default hr_api.g_varchar2
253 ,p_category_usage_id in number
254 ) is
255 --
256 -- Declare cursors and local variables
257 --
258 l_proc varchar2(72) := g_package||' update_lp_cat_inclusion ';
259 l_object_version_number number := p_object_version_number;
260 l_effective_date date;
261
262 begin
263 hr_utility.set_location('Entering:'|| l_proc, 10);
264 --
265 -- Issue a savepoint
266 --
267 savepoint update_lp_cat_inclusion ;
268
269 --
270 -- Truncate the time portion from all IN date parameters
271 --
272 l_effective_date := trunc(p_effective_date);
273 --
274
275 -- Call Before Process User Hook
276 --
277 begin
278 ota_lp_category_bk2.update_lp_cat_inclusion_b
279 (p_effective_date => l_effective_date,
280 p_learning_path_id => p_learning_path_id ,
281 p_object_version_number => p_object_version_number,
282 p_attribute_category => p_attribute_category,
283 p_attribute1 => p_attribute1,
284 p_attribute2 => p_attribute2,
285 p_attribute3 => p_attribute3,
286 p_attribute4 => p_attribute4,
287 p_attribute5 => p_attribute5 ,
288 p_attribute6 => p_attribute6,
289 p_attribute7 => p_attribute7,
290 p_attribute8 => p_attribute8,
291 p_attribute9 => p_attribute9,
292 p_attribute10 => p_attribute10,
293 p_attribute11 => p_attribute11,
294 p_attribute12 => p_attribute12,
295 p_attribute13 => p_attribute13,
296 p_attribute14 => p_attribute14,
297 p_attribute15 => p_attribute15,
298 p_attribute16 => p_attribute16,
299 p_attribute17 => p_attribute17,
300 p_attribute18 => p_attribute18,
301 p_attribute19 => p_attribute19,
302 p_attribute20 => p_attribute20,
303 p_start_date_active => p_start_date_active,
304 p_end_date_active => p_end_date_active,
305 p_primary_flag => p_primary_flag,
306 p_category_usage_id => p_category_usage_id
307 );
308
309 exception
310 when hr_api.cannot_find_prog_unit then
311 hr_api.cannot_find_prog_unit_error
312 (p_module_name => 'update_lp_cat_inclusion_b'
313 ,p_hook_type => 'BP'
314 );
315 end;
316 --
317 -- Validation in addition to Row Handlers
318 --
319 --
320 -- Process Logic
321 --
322 ota_lci_upd.upd
323 (p_effective_date => l_effective_date,
324 p_learning_path_id => p_learning_path_id ,
325 p_object_version_number => p_object_version_number,
326 p_attribute_category => p_attribute_category,
327 p_attribute1 => p_attribute1,
328 p_attribute2 => p_attribute2,
329 p_attribute3 => p_attribute3,
330 p_attribute4 => p_attribute4,
331 p_attribute5 => p_attribute5 ,
332 p_attribute6 => p_attribute6,
333 p_attribute7 => p_attribute7,
334 p_attribute8 => p_attribute8,
335 p_attribute9 => p_attribute9,
336 p_attribute10 => p_attribute10,
337 p_attribute11 => p_attribute11,
338 p_attribute12 => p_attribute12,
339 p_attribute13 => p_attribute13,
340 p_attribute14 => p_attribute14,
341 p_attribute15 => p_attribute15,
342 p_attribute16 => p_attribute16,
343 p_attribute17 => p_attribute17,
344 p_attribute18 => p_attribute18,
345 p_attribute19 => p_attribute19,
346 p_attribute20 => p_attribute20,
347 p_start_date_active => p_start_date_active,
348 p_end_date_active => p_end_date_active,
349 p_primary_flag => p_primary_flag,
350 p_category_usage_id => p_category_usage_id
351 );
352 --
353 -- Call After Process User Hook
354 --
355 begin
356 ota_lp_category_bk2.update_lp_cat_inclusion_a
357 (p_effective_date => l_effective_date,
358 p_learning_path_id => p_learning_path_id ,
359 p_object_version_number => p_object_version_number,
360 p_attribute_category => p_attribute_category,
361 p_attribute1 => p_attribute1,
362 p_attribute2 => p_attribute2,
363 p_attribute3 => p_attribute3,
364 p_attribute4 => p_attribute4,
365 p_attribute5 => p_attribute5 ,
366 p_attribute6 => p_attribute6,
367 p_attribute7 => p_attribute7,
368 p_attribute8 => p_attribute8,
369 p_attribute9 => p_attribute9,
370 p_attribute10 => p_attribute10,
371 p_attribute11 => p_attribute11,
372 p_attribute12 => p_attribute12,
373 p_attribute13 => p_attribute13,
374 p_attribute14 => p_attribute14,
375 p_attribute15 => p_attribute15,
376 p_attribute16 => p_attribute16,
377 p_attribute17 => p_attribute17,
378 p_attribute18 => p_attribute18,
379 p_attribute19 => p_attribute19,
380 p_attribute20 => p_attribute20,
381 p_start_date_active => p_start_date_active,
382 p_end_date_active => p_end_date_active,
383 p_primary_flag => p_primary_flag,
384 p_category_usage_id => p_category_usage_id
385 );
386 exception
387 when hr_api.cannot_find_prog_unit then
388 hr_api.cannot_find_prog_unit_error
389 (p_module_name => 'update_lp_cat_inclusion'
390 ,p_hook_type => 'AP'
391 );
392 end;
393 --
394 -- When in validation only mode raise the Validate_Enabled exception
395 --
396 if p_validate then
397 raise hr_api.validate_enabled;
398 end if;
399 --
400 -- Set all output arguments
401 --
402 p_object_version_number := l_object_version_number;
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 70);
405 exception
406 when hr_api.validate_enabled then
407 --
408 -- As the Validate_Enabled exception has been raised
409 -- we must rollback to the savepoint
410 --
411 rollback to update_lp_cat_inclusion ;
412 --
413 -- Only set output warning arguments
414 -- (Any key or derived arguments must be set to null
415 -- when validation only mode is being used.)
416 --
417 p_object_version_number := null;
418 hr_utility.set_location(' Leaving:'||l_proc, 80);
419 when others then
420 --
421 -- A validation or unexpected error has occured
422 --
423 rollback to update_lp_cat_inclusion ;
424 hr_utility.set_location(' Leaving:'||l_proc, 90);
425 p_object_version_number := l_object_version_number;
426 raise;
427 end update_lp_cat_inclusion ;
428
429 --
430 -- ----------------------------------------------------------------------------
431 -- |-------------------------< delete_lp_cat_inclusion >------------------|
432 -- ----------------------------------------------------------------------------
433 --
434 procedure delete_lp_cat_inclusion
435 ( p_learning_path_id in number,
436 p_category_usage_id in varchar2,
437 p_object_version_number in number,
438 p_validate in boolean default false
439
440 ) is
441 --
442 -- Declare cursors and local variables
443 --
444 l_proc varchar2(72) := g_package||' delete_lp_cat_inclusion ';
445 --
446 begin
447 hr_utility.set_location('Entering:'|| l_proc, 10);
448 --
449 -- Issue a savepoint
450 --
451 savepoint delete_lp_cat_inclusion ;
452 --
453 -- Call Before Process User Hook
454 --
455 begin
456 ota_lp_category_bk3.delete_lp_cat_inclusion_b
457 (p_learning_path_id => p_learning_path_id ,
458 p_category_usage_id => p_category_usage_id,
459 p_object_version_number => p_object_version_number);
460
461 exception
462 when hr_api.cannot_find_prog_unit then
463 hr_api.cannot_find_prog_unit_error
464 (p_module_name => 'delete_lp_cat_inclusion_b '
465 ,p_hook_type => 'BP'
466 );
467 end;
468 --
469 -- Validation in addition to Row Handlers
470 --
471 --
472 -- Process Logic
473 --
474 OTA_lci_del.del
475 (p_learning_path_id => p_learning_path_id ,
476 p_category_usage_id => p_category_usage_id,
477 p_object_version_number => p_object_version_number);
478 --
479 -- Call After Process User Hook
480 --
481 begin
482 ota_lp_category_bk3.delete_lp_cat_inclusion_a
483 (p_learning_path_id => p_learning_path_id ,
484 p_category_usage_id => p_category_usage_id,
485 p_object_version_number => p_object_version_number);
486 exception
487 when hr_api.cannot_find_prog_unit then
488 hr_api.cannot_find_prog_unit_error
489 (p_module_name => 'delete_lp_cat_inclusion_a '
490 ,p_hook_type => 'AP'
491 );
492 end;
493 --
494 -- When in validation only mode raise the Validate_Enabled exception
495 --
496 if p_validate then
497 raise hr_api.validate_enabled;
498 end if;
499 --
500 -- Set all output arguments
501 --
502 --
503 hr_utility.set_location(' Leaving:'||l_proc, 170);
504 exception
505 when hr_api.validate_enabled then
506 --
507 -- As the Validate_Enabled exception has been raised
508 -- we must rollback to the savepoint
509 --
510 rollback to delete_lp_cat_inclusion ;
511 --
512 -- Only set output warning arguments
513 -- (Any key or derived arguments must be set to null
514 -- when validation only mode is being used.)
515 --
516 hr_utility.set_location(' Leaving:'||l_proc, 180);
517 when others then
518 --
519 -- A validation or unexpected error has occured
520 --
521 rollback to delete_lp_cat_inclusion ;
522 hr_utility.set_location(' Leaving:'||l_proc, 190);
523 raise;
524 end delete_lp_cat_inclusion;
525 --
526 end ota_lp_category_api;