[Home] [Help]
PACKAGE BODY: APPS.OTA_LP_SECTION_API
Source
1 PACKAGE BODY OTA_LP_SECTION_API as
2 /* $Header: otlpcapi.pkb 120.0 2005/05/29 07:20:34 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_LP_SECTION_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_LP_SECTION >------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_lp_section
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_business_group_id in number
16 ,p_section_name in varchar2
17 ,p_description in varchar2 default null
18 ,p_learning_path_id in number
19 ,p_section_sequence in number
20 ,p_completion_type_code in varchar2
21 ,p_no_of_mandatory_courses in number default null
22 ,p_attribute_category in varchar2 default null
23 ,p_attribute1 in varchar2 default null
24 ,p_attribute2 in varchar2 default null
25 ,p_attribute3 in varchar2 default null
26 ,p_attribute4 in varchar2 default null
27 ,p_attribute5 in varchar2 default null
28 ,p_attribute6 in varchar2 default null
29 ,p_attribute7 in varchar2 default null
30 ,p_attribute8 in varchar2 default null
31 ,p_attribute9 in varchar2 default null
32 ,p_attribute10 in varchar2 default null
33 ,p_attribute11 in varchar2 default null
34 ,p_attribute12 in varchar2 default null
35 ,p_attribute13 in varchar2 default null
36 ,p_attribute14 in varchar2 default null
37 ,p_attribute15 in varchar2 default null
38 ,p_attribute16 in varchar2 default null
39 ,p_attribute17 in varchar2 default null
40 ,p_attribute18 in varchar2 default null
41 ,p_attribute19 in varchar2 default null
42 ,p_attribute20 in varchar2 default null
43 ,p_learning_path_section_id out nocopy number
44 ,p_object_version_number out nocopy number
45 ) is
46 --
47 -- Declare cursors and local variables
48 --
49 l_proc varchar2(72) := g_package||' Create Learning Path Section';
50 l_learning_path_section_id number;
51 l_object_version_number number;
52 l_effective_date date;
53
54 begin
55 hr_utility.set_location('Entering:'|| l_proc, 10);
56 --
57 -- Issue a savepoint
58 --
59 savepoint create_lp_section;
60 --
61 -- Truncate the time portion from all IN date parameters
62 --
63 l_effective_date := trunc(p_effective_date);
64
65 --
66 -- Call Before Process User Hook
67 --
68 begin
69 ota_lp_section_bk1.create_lp_section_b
70 (p_effective_date => l_effective_date
71 ,p_business_group_id => p_business_group_id
72 ,p_section_name => p_section_name
73 ,p_description => p_description
74 ,p_learning_path_id => p_learning_path_id
75 ,p_section_sequence => p_section_sequence
76 ,p_completion_type_code => p_completion_type_code
77 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
78 ,p_attribute_category => p_attribute_category
79 ,p_attribute1 => p_attribute1
80 ,p_attribute2 => p_attribute2
81 ,p_attribute3 => p_attribute3
82 ,p_attribute4 => p_attribute4
83 ,p_attribute5 => p_attribute5
84 ,p_attribute6 => p_attribute6
85 ,p_attribute7 => p_attribute7
86 ,p_attribute8 => p_attribute8
87 ,p_attribute9 => p_attribute9
88 ,p_attribute10 => p_attribute10
89 ,p_attribute11 => p_attribute11
90 ,p_attribute12 => p_attribute12
91 ,p_attribute13 => p_attribute13
92 ,p_attribute14 => p_attribute14
93 ,p_attribute15 => p_attribute15
94 ,p_attribute16 => p_attribute16
95 ,p_attribute17 => p_attribute17
96 ,p_attribute18 => p_attribute18
97 ,p_attribute19 => p_attribute19
98 ,p_attribute20 => p_attribute20
99 );
100 exception
101 when hr_api.cannot_find_prog_unit then
102 hr_api.cannot_find_prog_unit_error
103 (p_module_name => 'Create_Lp_Section'
104 ,p_hook_type => 'BP'
105 );
106 end;
107 --
108 -- Validation in addition to Row Handlers
109 --
110 --
111 -- Process Logic
112 --
113 ota_lpc_ins.ins
114 (p_effective_date => l_effective_date
115 ,p_business_group_id => p_business_group_id
116 ,p_learning_path_id => p_learning_path_id
117 ,p_section_sequence => p_section_sequence
118 ,p_completion_type_code => p_completion_type_code
119 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
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_object_version_number => l_object_version_number
142 ,p_learning_path_section_id => l_learning_path_section_id
143 );
144 --
145 --
146 -- Set all output arguments
147 --
148 p_learning_path_section_id := l_learning_path_section_id;
149 p_object_version_number := l_object_version_number;
150
151
152 ota_lst_ins.ins_tl
153 ( p_effective_date => p_effective_date
154 ,p_language_code => USERENV('LANG')
155 ,p_learning_path_section_id => p_learning_path_section_id
156 ,p_name => rtrim(p_section_name)
157 ,p_description => p_description
158 );
159
160 -- Call After Process User Hook
161 --
162 begin
163 ota_lp_section_bk1.create_lp_section_a
164 (p_effective_date => l_effective_date
165 ,p_business_group_id => p_business_group_id
166 ,p_section_name => p_section_name
167 ,p_description => p_description
168 ,p_learning_path_id => p_learning_path_id
169 ,p_section_sequence => p_section_sequence
170 ,p_completion_type_code => p_completion_type_code
171 ,p_no_of_mandatory_courses => p_no_of_mandatory_courses
172 ,p_attribute_category => p_attribute_category
173 ,p_attribute1 => p_attribute1
174 ,p_attribute2 => p_attribute2
175 ,p_attribute3 => p_attribute3
176 ,p_attribute4 => p_attribute4
177 ,p_attribute5 => p_attribute5
178 ,p_attribute6 => p_attribute6
179 ,p_attribute7 => p_attribute7
180 ,p_attribute8 => p_attribute8
181 ,p_attribute9 => p_attribute9
182 ,p_attribute10 => p_attribute10
183 ,p_attribute11 => p_attribute11
184 ,p_attribute12 => p_attribute12
185 ,p_attribute13 => p_attribute13
186 ,p_attribute14 => p_attribute14
187 ,p_attribute15 => p_attribute15
188 ,p_attribute16 => p_attribute16
189 ,p_attribute17 => p_attribute17
190 ,p_attribute18 => p_attribute18
191 ,p_attribute19 => p_attribute19
192 ,p_attribute20 => p_attribute20
193 ,p_learning_path_section_id => l_learning_path_section_id
194 ,p_object_version_number => l_object_version_number
195 );
196 exception
197 when hr_api.cannot_find_prog_unit then
198 hr_api.cannot_find_prog_unit_error
199 (p_module_name => 'Create_Lp_Section'
200 ,p_hook_type => 'AP'
201 );
202 end;
203 --
204 -- When in validation only mode raise the Validate_Enabled exception
205 --
206 if p_validate then
207 raise hr_api.validate_enabled;
208 end if;
209 --
210 -- Set all output arguments
211 --
212 p_learning_path_section_id := l_learning_path_section_id;
213 p_object_version_number := l_object_version_number;
214 --
215 hr_utility.set_location(' Leaving:'||l_proc, 70);
216 exception
217 when hr_api.validate_enabled then
218 --
219 -- As the Validate_Enabled exception has been raised
220 -- we must rollback to the savepoint
221 --
222 rollback to create_lp_section;
223 --
224 -- Only set output warning arguments
225 -- (Any key or derived arguments must be set to null
226 -- when validation only mode is being used.)
227 --
228 p_learning_path_section_id := null;
229 p_object_version_number := null;
230 hr_utility.set_location(' Leaving:'||l_proc, 80);
231 when others then
232 --
233 -- A validation or unexpected error has occured
234 --
235 rollback to create_lp_section;
236 p_learning_path_section_id := null;
237 p_object_version_number := null;
238 hr_utility.set_location(' Leaving:'||l_proc, 90);
239 raise;
240 end create_lp_section;
241 -- ----------------------------------------------------------------------------
242 -- |-------------------------< update_lp_section >-------------------|
243 -- ----------------------------------------------------------------------------
244 --
245 procedure update_lp_section
246 (p_validate in boolean default false
247 ,p_effective_date in date
248 ,p_learning_path_section_id in number
249 ,p_section_name in varchar2
250 ,p_description in varchar2 default hr_api.g_varchar2
251 ,p_object_version_number in out nocopy number
252 ,p_section_sequence in number default hr_api.g_number
253 ,p_completion_type_code in varchar2 default hr_api.g_varchar2
254 ,p_no_of_mandatory_courses in number default hr_api.g_number
255 ,p_attribute_category in varchar2 default hr_api.g_varchar2
256 ,p_attribute1 in varchar2 default hr_api.g_varchar2
257 ,p_attribute2 in varchar2 default hr_api.g_varchar2
258 ,p_attribute3 in varchar2 default hr_api.g_varchar2
259 ,p_attribute4 in varchar2 default hr_api.g_varchar2
260 ,p_attribute5 in varchar2 default hr_api.g_varchar2
261 ,p_attribute6 in varchar2 default hr_api.g_varchar2
262 ,p_attribute7 in varchar2 default hr_api.g_varchar2
263 ,p_attribute8 in varchar2 default hr_api.g_varchar2
264 ,p_attribute9 in varchar2 default hr_api.g_varchar2
265 ,p_attribute10 in varchar2 default hr_api.g_varchar2
266 ,p_attribute11 in varchar2 default hr_api.g_varchar2
267 ,p_attribute12 in varchar2 default hr_api.g_varchar2
268 ,p_attribute13 in varchar2 default hr_api.g_varchar2
269 ,p_attribute14 in varchar2 default hr_api.g_varchar2
270 ,p_attribute15 in varchar2 default hr_api.g_varchar2
271 ,p_attribute16 in varchar2 default hr_api.g_varchar2
272 ,p_attribute17 in varchar2 default hr_api.g_varchar2
273 ,p_attribute18 in varchar2 default hr_api.g_varchar2
274 ,p_attribute19 in varchar2 default hr_api.g_varchar2
275 ,p_attribute20 in varchar2 default hr_api.g_varchar2
276 ) is
277 --
278 -- Declare cursors and local variables
279 --
280 l_proc varchar2(72) := g_package||' Update Learning Path Section';
281 l_object_version_number number := p_object_version_number;
282 l_effective_date date;
283
284 l_lpm_ovn ota_learning_path_members.object_version_number%TYPE;
285 l_old_comp_type ota_lp_sections.completion_type_code%TYPE;
286 l_no_of_mandatory_courses ota_lp_sections.no_of_mandatory_courses%TYPE := p_no_of_mandatory_courses;
287
288 CURSOR comp_type IS
289 SELECT completion_type_code
290 FROM ota_lp_sections
291 WHERE learning_path_section_id = p_learning_path_section_id;
292
293 CURSOR get_lpms IS
294 SELECT learning_path_member_id,
295 object_version_number,
296 activity_version_id,
297 course_sequence
298 FROM ota_learning_path_members
299 WHERE learning_path_section_id = p_learning_path_section_id
300 AND (duration IS NOT NULL OR
301 duration_units is not null OR
302 notify_days_before_target IS NOT NULL);
303
304 begin
305 hr_utility.set_location('Entering:'|| l_proc, 10);
306 --
307 -- Issue a savepoint
308 --
309 savepoint update_lp_section;
310
311 --
312 -- Truncate the time portion from all IN date parameters
313 --
314 l_effective_date := trunc(p_effective_date);
315 --
316 OPEN comp_type;
317 FETCH comp_type INTO l_old_comp_type;
318 CLOSE comp_type;
319
320 IF l_old_comp_type = 'S' AND
321 p_completion_type_code <> 'S' AND
322 p_no_of_mandatory_courses IS NOT NULL
323 THEN l_no_of_mandatory_courses := null;
324 END IF;
325
326 -- Call Before Process User Hook
327 --
328 begin
329 ota_lp_section_bk2.update_lp_section_b
330 (p_effective_date => l_effective_date
331 ,p_learning_path_section_id => p_learning_path_section_id
332 ,p_section_name => p_section_name
333 ,p_description => p_description
334 ,p_object_version_number => l_object_version_number
335 ,p_section_sequence => p_section_sequence
336 ,p_completion_type_code => p_completion_type_code
337 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
338 ,p_attribute_category => p_attribute_category
339 ,p_attribute1 => p_attribute1
340 ,p_attribute2 => p_attribute2
341 ,p_attribute3 => p_attribute3
342 ,p_attribute4 => p_attribute4
343 ,p_attribute5 => p_attribute5
344 ,p_attribute6 => p_attribute6
345 ,p_attribute7 => p_attribute7
346 ,p_attribute8 => p_attribute8
347 ,p_attribute9 => p_attribute9
348 ,p_attribute10 => p_attribute10
349 ,p_attribute11 => p_attribute11
350 ,p_attribute12 => p_attribute12
351 ,p_attribute13 => p_attribute13
352 ,p_attribute14 => p_attribute14
353 ,p_attribute15 => p_attribute15
354 ,p_attribute16 => p_attribute16
355 ,p_attribute17 => p_attribute17
356 ,p_attribute18 => p_attribute18
357 ,p_attribute19 => p_attribute19
358 ,p_attribute20 => p_attribute20
359 );
360 exception
361 when hr_api.cannot_find_prog_unit then
362 hr_api.cannot_find_prog_unit_error
363 (p_module_name => 'Update_Lp_Section'
364 ,p_hook_type => 'BP'
365 );
366 end;
367 --
368 -- Validation in addition to Row Handlers
369 --
370 --
371 -- Process Logic
372 --
373 ota_lpc_upd.upd
374 (p_effective_date => l_effective_date
375 ,p_learning_path_section_id => p_learning_path_section_id
376 ,p_object_version_number => l_object_version_number
377 ,p_section_sequence => p_section_sequence
378 ,p_completion_type_code => p_completion_type_code
379 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
380 ,p_attribute_category => p_attribute_category
381 ,p_attribute1 => p_attribute1
382 ,p_attribute2 => p_attribute2
383 ,p_attribute3 => p_attribute3
384 ,p_attribute4 => p_attribute4
385 ,p_attribute5 => p_attribute5
386 ,p_attribute6 => p_attribute6
387 ,p_attribute7 => p_attribute7
388 ,p_attribute8 => p_attribute8
389 ,p_attribute9 => p_attribute9
390 ,p_attribute10 => p_attribute10
391 ,p_attribute11 => p_attribute11
392 ,p_attribute12 => p_attribute12
393 ,p_attribute13 => p_attribute13
394 ,p_attribute14 => p_attribute14
395 ,p_attribute15 => p_attribute15
396 ,p_attribute16 => p_attribute16
397 ,p_attribute17 => p_attribute17
398 ,p_attribute18 => p_attribute18
399 ,p_attribute19 => p_attribute19
400 ,p_attribute20 => p_attribute20
401 );
402 --
403
404 ota_lst_upd.upd_tl
405 ( p_effective_date => p_effective_date
406 ,p_language_code => USERENV('LANG')
407 ,p_learning_path_section_id => p_learning_path_section_id
408 ,p_name => rtrim(p_section_name)
409 ,p_description => p_description
410 );
411
412
413 -- Call After Process User Hook
414 --
415 begin
416 ota_lp_section_bk2.update_lp_section_a
417 (p_effective_date => l_effective_date
418 ,p_learning_path_section_id => p_learning_path_section_id
419 ,p_section_name => p_section_name
420 ,p_description => p_description
421 ,p_object_version_number => l_object_version_number
422 ,p_section_sequence => p_section_sequence
423 ,p_completion_type_code => p_completion_type_code
424 ,p_no_of_mandatory_courses => l_no_of_mandatory_courses
425 ,p_attribute_category => p_attribute_category
426 ,p_attribute1 => p_attribute1
427 ,p_attribute2 => p_attribute2
428 ,p_attribute3 => p_attribute3
429 ,p_attribute4 => p_attribute4
430 ,p_attribute5 => p_attribute5
431 ,p_attribute6 => p_attribute6
432 ,p_attribute7 => p_attribute7
433 ,p_attribute8 => p_attribute8
434 ,p_attribute9 => p_attribute9
435 ,p_attribute10 => p_attribute10
436 ,p_attribute11 => p_attribute11
437 ,p_attribute12 => p_attribute12
438 ,p_attribute13 => p_attribute13
439 ,p_attribute14 => p_attribute14
440 ,p_attribute15 => p_attribute15
441 ,p_attribute16 => p_attribute16
442 ,p_attribute17 => p_attribute17
443 ,p_attribute18 => p_attribute18
444 ,p_attribute19 => p_attribute19
445 ,p_attribute20 => p_attribute20
446 );
447 exception
448 when hr_api.cannot_find_prog_unit then
449 hr_api.cannot_find_prog_unit_error
450 (p_module_name => 'Update_Lp_Section'
451 ,p_hook_type => 'AP'
452 );
453 end;
454 --
455
456 IF l_old_comp_type = 'M' AND p_completion_type_code <> 'M' THEN
457 FOR csr_lpms IN get_lpms
458 LOOP
459 l_lpm_ovn := csr_lpms.object_version_number;
460
461 ota_lp_member_api.update_learning_path_member
462 (p_validate => p_validate
463 ,p_effective_date => l_effective_date
464 ,p_learning_path_member_id => csr_lpms.learning_path_member_id
465 ,p_object_version_number => l_lpm_ovn
466 ,p_activity_version_id => csr_lpms.activity_version_id
467 ,p_course_sequence => csr_lpms.course_sequence
468 ,p_duration => null
469 ,p_duration_units => null
470 ,p_notify_days_before_target => null);
471
472 END LOOP;
473 END IF;
474
475 -- When in validation only mode raise the Validate_Enabled exception
476 --
477 if p_validate then
478 raise hr_api.validate_enabled;
479 end if;
480 --
481 -- Set all output arguments
482 --
483 p_object_version_number := l_object_version_number;
484 --
485 hr_utility.set_location(' Leaving:'||l_proc, 70);
486 exception
487 when hr_api.validate_enabled then
488 --
489 -- As the Validate_Enabled exception has been raised
490 -- we must rollback to the savepoint
491 --
492 rollback to update_lp_section;
493 --
494 -- Only set output warning arguments
495 -- (Any key or derived arguments must be set to null
496 -- when validation only mode is being used.)
497 --
498 p_object_version_number := null;
499 hr_utility.set_location(' Leaving:'||l_proc, 80);
500 when others then
501 --
502 -- A validation or unexpected error has occured
503 --
504 rollback to update_lp_section;
505 hr_utility.set_location(' Leaving:'||l_proc, 90);
506 p_object_version_number := l_object_version_number;
507 raise;
508 end update_lp_section;
509
510 --
511 -- ----------------------------------------------------------------------------
512 -- |-------------------------< delete_lp_section >------------------|
513 -- ----------------------------------------------------------------------------
514 --
515 procedure delete_lp_section
516 (p_validate in boolean default false
517 ,p_learning_path_section_id in number
518 ,p_object_version_number in number
519 ) is
520 --
521 -- Declare cursors and local variables
522 --
523 l_proc varchar2(72) := g_package||' Delete Learning Path Section';
524 --
525 begin
526 hr_utility.set_location('Entering:'|| l_proc, 10);
527 --
528 -- Issue a savepoint
529 --
530 savepoint delete_lp_section;
531 --
532 -- Call Before Process User Hook
533 --
534 begin
535 ota_lp_section_bk3.delete_lp_section_b
536 (p_learning_path_section_id => p_learning_path_section_id
537 ,p_object_version_number => p_object_version_number
538 );
539 exception
540 when hr_api.cannot_find_prog_unit then
541 hr_api.cannot_find_prog_unit_error
542 (p_module_name => 'Delete_Learning_Path_Section'
543 ,p_hook_type => 'BP'
544 );
545 end;
546 --
547 -- Validation in addition to Row Handlers
548 --
549 --
550 -- Process Logic
551 --
552 OTA_lpc_del.del
553 (p_learning_path_section_id => p_learning_path_section_id
554 ,p_object_version_number => p_object_version_number
555 );
556 --
557 -- Call After Process User Hook
558 --
559 begin
560 ota_lp_section_bk3.delete_lp_section_a
561 (p_learning_path_section_id => p_learning_path_section_id
562 ,p_object_version_number => p_object_version_number
563 );
564 exception
565 when hr_api.cannot_find_prog_unit then
566 hr_api.cannot_find_prog_unit_error
567 (p_module_name => 'Delete_Lp_Section'
568 ,p_hook_type => 'AP'
569 );
570 end;
571 --
572 -- When in validation only mode raise the Validate_Enabled exception
573 --
574 if p_validate then
575 raise hr_api.validate_enabled;
576 end if;
577 --
578 -- Set all output arguments
579 --
580 --
581 hr_utility.set_location(' Leaving:'||l_proc, 170);
582 exception
583 when hr_api.validate_enabled then
584 --
585 -- As the Validate_Enabled exception has been raised
586 -- we must rollback to the savepoint
587 --
588 rollback to delete_lp_section;
589 --
590 -- Only set output warning arguments
591 -- (Any key or derived arguments must be set to null
592 -- when validation only mode is being used.)
593 --
594 hr_utility.set_location(' Leaving:'||l_proc, 180);
595 when others then
596 --
597 -- A validation or unexpected error has occured
598 --
599 rollback to delete_lp_section;
600 hr_utility.set_location(' Leaving:'||l_proc, 190);
601 raise;
602 end delete_lp_section;
603 --
604 end ota_lp_section_api;