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