[Home] [Help]
PACKAGE BODY: APPS.OTA_CERT_MBR_ENROLLMENT_API
Source
1 Package Body OTA_CERT_MBR_ENROLLMENT_API as
2 /* $Header: otcmeapi.pkb 120.1 2005/08/10 15:26 asud noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_CERT_MBR_ENROLLMENT_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_cert_mbr_enrollment >-----------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_cert_mbr_enrollment
13 (
14 p_effective_date in date,
15 p_validate in boolean default false ,
16 p_cert_prd_enrollment_id in number,
17 p_cert_member_id in number,
18 p_member_status_code in varchar2,
19 p_completion_date in date default null,
20 p_business_group_id in number,
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_cert_mbr_enrollment_id out nocopy number,
43 p_object_version_number out nocopy number
44 ) is
45 --
46 -- Declare cursors and local variables
47 --
48 l_proc varchar2(72) := g_package||' create_cert_mbr_enrollment';
49 l_cert_mbr_enrollment_id number;
50 l_object_version_number number;
51 l_effective_date date;
52
53 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE := p_member_status_code;
54 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE := p_completion_date;
55 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
56
57 CURSOR csr_get_course_id IS
58 SELECT object_id
59 FROM ota_certification_members
60 WHERE certification_member_id = p_cert_member_id;
61
62 l_mode varchar2(1);
63
64 begin
65 hr_utility.set_location('Entering:'|| l_proc, 10);
66 --
67 -- Issue a savepoint
68 --
69 savepoint CREATE_cert_mbr_enrollment;
70 l_effective_date := trunc(p_effective_date);
71
72 IF p_member_status_code = 'PLANNED' THEN
73 OPEN csr_get_course_id;
74 FETCH csr_get_course_id INTO l_activity_version_id;
75 CLOSE csr_get_course_id;
76 l_mode := 'C';
77 ota_cme_util.calculate_cme_status(p_activity_version_id => l_activity_version_id,
78 p_cert_prd_enrollment_id => p_cert_prd_enrollment_id,
79 p_mode => l_mode,
80 p_member_status_code => l_member_status_code,
81 p_completion_date => l_completion_date);
82 END IF;
83
84 begin
85 OTA_CERT_MBR_ENROLLMENT_bk1.create_cert_mbr_enrollment_b
86 ( p_effective_date => p_effective_date
87 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
88 ,p_cert_member_id => p_cert_member_id
89 ,p_member_status_code => l_member_status_code
90 ,p_completion_date => l_completion_date
91 ,p_business_group_id => p_business_group_id
92 ,p_attribute_category => p_attribute_category
93 ,p_attribute1 => p_attribute1
94 ,p_attribute2 => p_attribute2
95 ,p_attribute3 => p_attribute3
96 ,p_attribute4 => p_attribute4
97 ,p_attribute5 => p_attribute5
98 ,p_attribute6 => p_attribute6
99 ,p_attribute7 => p_attribute7
100 ,p_attribute8 => p_attribute8
101 ,p_attribute9 => p_attribute9
102 ,p_attribute10 => p_attribute10
103 ,p_attribute11 => p_attribute11
104 ,p_attribute12 => p_attribute12
105 ,p_attribute13 => p_attribute13
106 ,p_attribute14 => p_attribute14
107 ,p_attribute15 => p_attribute15
108 ,p_attribute16 => p_attribute16
109 ,p_attribute17 => p_attribute17
110 ,p_attribute18 => p_attribute18
111 ,p_attribute19 => p_attribute19
112 ,p_attribute20 => p_attribute20
113 );
114 exception
115 when hr_api.cannot_find_prog_unit then
116 hr_api.cannot_find_prog_unit_error
117 (p_module_name => 'CREATE_cert_mbr_enrollment'
118 ,p_hook_type => 'BP'
119 );
120 end;
121
122 --
123 -- Process Logic
124 --
125 ota_cme_ins.ins
126 (
127 p_effective_date => p_effective_date
128 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
129 ,p_cert_member_id => p_cert_member_id
130 ,p_member_status_code => l_member_status_code
131 ,p_completion_date => l_completion_date
132 ,p_business_group_id => p_business_group_id
133 ,p_attribute_category => p_attribute_category
134 ,p_attribute1 => p_attribute1
135 ,p_attribute2 => p_attribute2
136 ,p_attribute3 => p_attribute3
137 ,p_attribute4 => p_attribute4
138 ,p_attribute5 => p_attribute5
139 ,p_attribute6 => p_attribute6
140 ,p_attribute7 => p_attribute7
141 ,p_attribute8 => p_attribute8
142 ,p_attribute9 => p_attribute9
143 ,p_attribute10 => p_attribute10
144 ,p_attribute11 => p_attribute11
145 ,p_attribute12 => p_attribute12
146 ,p_attribute13 => p_attribute13
147 ,p_attribute14 => p_attribute14
148 ,p_attribute15 => p_attribute15
149 ,p_attribute16 => p_attribute16
150 ,p_attribute17 => p_attribute17
151 ,p_attribute18 => p_attribute18
152 ,p_attribute19 => p_attribute19
153 ,p_attribute20 => p_attribute20
154 ,p_cert_mbr_enrollment_id => l_cert_mbr_enrollment_id
155 ,p_object_version_number => l_object_version_number
156 );
157 --
158 -- Set all output arguments
159 --
160 p_cert_mbr_enrollment_id := l_cert_mbr_enrollment_id;
161 p_object_version_number := l_object_version_number;
162
163
164
165 begin
166 OTA_CERT_MBR_ENROLLMENT_bk1.create_cert_mbr_enrollment_a
167 ( p_effective_date => p_effective_date
168 ,p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
169 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
170 ,p_cert_member_id => p_cert_member_id
171 ,p_member_status_code => l_member_status_code
172 ,p_completion_date => l_completion_date
173 ,p_business_group_id => p_business_group_id
174 ,p_attribute_category => p_attribute_category
175 ,p_attribute1 => p_attribute1
176 ,p_attribute2 => p_attribute2
177 ,p_attribute3 => p_attribute3
178 ,p_attribute4 => p_attribute4
179 ,p_attribute5 => p_attribute5
180 ,p_attribute6 => p_attribute6
181 ,p_attribute7 => p_attribute7
182 ,p_attribute8 => p_attribute8
183 ,p_attribute9 => p_attribute9
184 ,p_attribute10 => p_attribute10
185 ,p_attribute11 => p_attribute11
186 ,p_attribute12 => p_attribute12
187 ,p_attribute13 => p_attribute13
188 ,p_attribute14 => p_attribute14
189 ,p_attribute15 => p_attribute15
190 ,p_attribute16 => p_attribute16
191 ,p_attribute17 => p_attribute17
192 ,p_attribute18 => p_attribute18
193 ,p_attribute19 => p_attribute19
194 ,p_attribute20 => p_attribute20
195 );
196
197 exception
198 when hr_api.cannot_find_prog_unit then
199 hr_api.cannot_find_prog_unit_error
200 (p_module_name => 'CREATE_cert_mbr_enrollment'
201 ,p_hook_type => 'AP'
202 );
203 end;
204
205 --
206 -- When in validation only mode raise the Validate_Enabled exception
207 --
208 if p_validate then
209 raise hr_api.validate_enabled;
210 end if;
211
212 hr_utility.set_location(' Leaving:'||l_proc, 70);
213 exception
214 when hr_api.validate_enabled then
215 --
216 -- As the Validate_Enabled exception has been raised
217 -- we must rollback to the savepoint
218 --
219 rollback to CREATE_cert_mbr_enrollment;
220 --
221 -- Only set output warning arguments
222 -- (Any key or derived arguments must be set to null
223 -- when validation only mode is being used.)
224 --
225 p_cert_mbr_enrollment_id := null;
226 p_object_version_number := null;
227 hr_utility.set_location(' Leaving:'||l_proc, 80);
228 when others then
229 --
233 p_cert_mbr_enrollment_id := null;
230 -- A validation or unexpected error has occured
231 --
232 rollback to CREATE_cert_mbr_enrollment;
234 p_object_version_number := null;
235 hr_utility.set_location(' Leaving:'||l_proc, 90);
236 raise;
237 end create_cert_mbr_enrollment;
238 -- ----------------------------------------------------------------------------
239 -- |-------------------------< UPDATE_cert_mbr_enrollment >-------------------------|
240 -- ----------------------------------------------------------------------------
241 --
242 procedure update_cert_mbr_enrollment
243 (p_effective_date in date
244 ,p_cert_mbr_enrollment_id in number
245 ,p_object_version_number in out nocopy number
246 ,p_cert_prd_enrollment_id in number
247 ,p_cert_member_id in number
248 ,p_member_status_code in varchar2
249 ,p_completion_date in date default hr_api.g_date
250 ,p_business_group_id in number default hr_api.g_number
251 ,p_attribute_category in varchar2 default hr_api.g_varchar2
252 ,p_attribute1 in varchar2 default hr_api.g_varchar2
253 ,p_attribute2 in varchar2 default hr_api.g_varchar2
254 ,p_attribute3 in varchar2 default hr_api.g_varchar2
255 ,p_attribute4 in varchar2 default hr_api.g_varchar2
256 ,p_attribute5 in varchar2 default hr_api.g_varchar2
257 ,p_attribute6 in varchar2 default hr_api.g_varchar2
258 ,p_attribute7 in varchar2 default hr_api.g_varchar2
259 ,p_attribute8 in varchar2 default hr_api.g_varchar2
260 ,p_attribute9 in varchar2 default hr_api.g_varchar2
261 ,p_attribute10 in varchar2 default hr_api.g_varchar2
262 ,p_attribute11 in varchar2 default hr_api.g_varchar2
263 ,p_attribute12 in varchar2 default hr_api.g_varchar2
264 ,p_attribute13 in varchar2 default hr_api.g_varchar2
265 ,p_attribute14 in varchar2 default hr_api.g_varchar2
266 ,p_attribute15 in varchar2 default hr_api.g_varchar2
267 ,p_attribute16 in varchar2 default hr_api.g_varchar2
268 ,p_attribute17 in varchar2 default hr_api.g_varchar2
269 ,p_attribute18 in varchar2 default hr_api.g_varchar2
270 ,p_attribute19 in varchar2 default hr_api.g_varchar2
271 ,p_attribute20 in varchar2 default hr_api.g_varchar2
272 ,p_validate in boolean default false
273 ) is
274 --
275 -- Declare cursors and local variables
276 --
277 l_proc varchar2(72) := g_package||' Update Cert Enrollment';
281 l_member_status_code ota_cert_mbr_enrollments.member_status_code%TYPE := p_member_status_code;
278 l_object_version_number number := p_object_version_number;
279 l_effective_date date;
280
282 l_completion_date ota_cert_mbr_enrollments.completion_date%TYPE := p_completion_date;
283 l_activity_version_id ota_activity_versions.activity_version_id%TYPE;
284 l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_enrollment_id%TYPE := p_cert_prd_enrollment_id;
285
286 CURSOR csr_get_course_id IS
287 SELECT cmb.object_id,
288 cpe.cert_prd_enrollment_id
289 FROM ota_certification_members cmb,
290 ota_cert_mbr_enrollments cme,
291 ota_cert_prd_enrollments cpe
292 WHERE cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
293 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
294 AND cme.cert_member_id = cmb.certification_member_id;
295
296 l_mode varchar2(1);
297
298 begin
299 hr_utility.set_location('Entering:'|| l_proc, 10);
300 --
301 -- Issue a savepoint
302 --
303 savepoint UPDATE_cert_mbr_enrollment;
304 l_effective_date := trunc(p_effective_date);
305
306 IF p_member_status_code <> 'CANCELLED' THEN
307 FOR rec_course IN csr_get_course_id
308 LOOP
309 l_activity_version_id := rec_course.object_id;
310 l_cert_prd_enrollment_id := rec_course.cert_prd_enrollment_id;
311 EXIT;
312 END LOOP;
313 l_mode := 'U';
314 ota_cme_util.calculate_cme_status(p_activity_version_id => l_activity_version_id,
315 p_cert_prd_enrollment_id => l_cert_prd_enrollment_id,
316 p_mode => l_mode,
317 p_member_status_code => l_member_status_code,
318 p_completion_date => l_completion_date);
319 END IF;
320
321 begin
322 OTA_CERT_MBR_ENROLLMENT_bk2.update_cert_mbr_enrollment_b
323 ( p_effective_date => p_effective_date
324 ,p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
325 ,p_object_version_number => p_object_version_number
326 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
327 ,p_cert_member_id => p_cert_member_id
328 ,p_member_status_code => l_member_status_code
329 ,p_completion_date => l_completion_date
330 ,p_business_group_id => p_business_group_id
331 ,p_attribute_category => p_attribute_category
332 ,p_attribute1 => p_attribute1
333 ,p_attribute2 => p_attribute2
334 ,p_attribute3 => p_attribute3
335 ,p_attribute4 => p_attribute4
336 ,p_attribute5 => p_attribute5
337 ,p_attribute6 => p_attribute6
338 ,p_attribute7 => p_attribute7
339 ,p_attribute8 => p_attribute8
340 ,p_attribute9 => p_attribute9
341 ,p_attribute10 => p_attribute10
342 ,p_attribute11 => p_attribute11
343 ,p_attribute12 => p_attribute12
344 ,p_attribute13 => p_attribute13
345 ,p_attribute14 => p_attribute14
346 ,p_attribute15 => p_attribute15
347 ,p_attribute16 => p_attribute16
348 ,p_attribute17 => p_attribute17
349 ,p_attribute18 => p_attribute18
350 ,p_attribute19 => p_attribute19
351 ,p_attribute20 => p_attribute20
352 );
353
354 exception
355 when hr_api.cannot_find_prog_unit then
356 hr_api.cannot_find_prog_unit_error
357 (p_module_name => 'UPDATE_cert_mbr_enrollment'
358 ,p_hook_type => 'BP'
359 );
360 end;
361
362
363 --
364 -- Process Logic
365 --
366
367 ota_cme_upd.upd
368 (
369 p_effective_date => p_effective_date
370 ,p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
371 ,p_object_version_number => l_object_version_number
372 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
373 ,p_cert_member_id => p_cert_member_id
374 ,p_member_status_code => l_member_status_code
375 ,p_completion_date => l_completion_date
376 ,p_business_group_id => p_business_group_id
377 ,p_attribute_category => p_attribute_category
378 ,p_attribute1 => p_attribute1
379 ,p_attribute2 => p_attribute2
380 ,p_attribute3 => p_attribute3
381 ,p_attribute4 => p_attribute4
382 ,p_attribute5 => p_attribute5
383 ,p_attribute6 => p_attribute6
384 ,p_attribute7 => p_attribute7
385 ,p_attribute8 => p_attribute8
386 ,p_attribute9 => p_attribute9
387 ,p_attribute10 => p_attribute10
388 ,p_attribute11 => p_attribute11
389 ,p_attribute12 => p_attribute12
390 ,p_attribute13 => p_attribute13
391 ,p_attribute14 => p_attribute14
392 ,p_attribute15 => p_attribute15
393 ,p_attribute16 => p_attribute16
394 ,p_attribute17 => p_attribute17
395 ,p_attribute18 => p_attribute18
396 ,p_attribute19 => p_attribute19
397 ,p_attribute20 => p_attribute20
398 );
399
400
401 begin
405 ,p_object_version_number => p_object_version_number
402 OTA_CERT_MBR_ENROLLMENT_bk2.update_cert_mbr_enrollment_a
403 ( p_effective_date => p_effective_date
404 ,p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
406 ,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
407 ,p_cert_member_id => p_cert_member_id
408 ,p_member_status_code => l_member_status_code
409 ,p_completion_date => l_completion_date
410 ,p_business_group_id => p_business_group_id
411 ,p_attribute_category => p_attribute_category
412 ,p_attribute1 => p_attribute1
413 ,p_attribute2 => p_attribute2
414 ,p_attribute3 => p_attribute3
415 ,p_attribute4 => p_attribute4
416 ,p_attribute5 => p_attribute5
417 ,p_attribute6 => p_attribute6
418 ,p_attribute7 => p_attribute7
419 ,p_attribute8 => p_attribute8
420 ,p_attribute9 => p_attribute9
421 ,p_attribute10 => p_attribute10
422 ,p_attribute11 => p_attribute11
423 ,p_attribute12 => p_attribute12
424 ,p_attribute13 => p_attribute13
425 ,p_attribute14 => p_attribute14
426 ,p_attribute15 => p_attribute15
427 ,p_attribute16 => p_attribute16
428 ,p_attribute17 => p_attribute17
429 ,p_attribute18 => p_attribute18
430 ,p_attribute19 => p_attribute19
431 ,p_attribute20 => p_attribute20
432 );
433
434 exception
435 when hr_api.cannot_find_prog_unit then
436 hr_api.cannot_find_prog_unit_error
437 (p_module_name => 'UPDATE_CERT_MBR_ENROLLMENT'
438 ,p_hook_type => 'AP'
439 );
440 end;
441
442
443 --
444 -- When in validation only mode raise the Validate_Enabled exception
448 end if;
445 --
446 if p_validate then
447 raise hr_api.validate_enabled;
449 --
450 -- Set all output arguments
451 --
452 p_object_version_number := l_object_version_number;
453
454 hr_utility.set_location(' Leaving:'||l_proc, 70);
455 exception
456 when hr_api.validate_enabled then
457 --
458 -- As the Validate_Enabled exception has been raised
459 -- we must rollback to the savepoint
460 --
461 rollback to UPDATE_cert_mbr_enrollment;
462 --
463 -- Only set output warning arguments
464 -- (Any key or derived arguments must be set to null
465 -- when validation only mode is being used.)
466 --
467 p_object_version_number := null;
468 hr_utility.set_location(' Leaving:'||l_proc, 80);
469 when others then
470 --
471 -- A validation or unexpected error has occured
472 --
473 rollback to UPDATE_cert_mbr_enrollment;
474 p_object_version_number := l_object_version_number;
475 hr_utility.set_location(' Leaving:'||l_proc, 90);
476 raise;
477 end update_cert_mbr_enrollment;
478 --
479 -- ----------------------------------------------------------------------------
480 -- |-------------------------< DELETE_cert_mbr_enrollment >-------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 procedure delete_cert_mbr_enrollment
484 (p_cert_mbr_enrollment_id in number
485 ,p_object_version_number in number
486 ,p_validate in boolean default false
487
488 ) is
489 --
490 -- Declare cursors and local variables
491 --
492 l_proc varchar2(72) := g_package||'DELETE_cert_mbr_enrollment';
493 --
494 --
495 begin
496 hr_utility.set_location('Entering:'|| l_proc, 10);
497 --
498 -- Issue a savepoint
499 --
500 savepoint DELETE_cert_mbr_enrollment;
501 --
502 -- Truncate the time portion from all IN date parameters
503 --
504 --
505
506 begin
507 OTA_CERT_MBR_ENROLLMENT_bk3.delete_cert_mbr_enrollment_b
508 (p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
509 ,p_object_version_number => p_object_version_number
510 );
511 exception
512 when hr_api.cannot_find_prog_unit then
513 hr_api.cannot_find_prog_unit_error
514 (p_module_name => 'DELETE_cert_mbr_enrollment'
515 ,p_hook_type => 'BP'
516 );
517 end;
518
519 --
520 -- Process Logic
521 --
522
523 ota_cme_del.del
524 (
525 p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id,
526 p_object_version_number => p_object_version_number
527 );
528
529
530 begin
534 );
531 OTA_CERT_MBR_ENROLLMENT_bk3.delete_cert_mbr_enrollment_a
532 (p_cert_mbr_enrollment_id => p_cert_mbr_enrollment_id
533 ,p_object_version_number => p_object_version_number
535 exception
536 when hr_api.cannot_find_prog_unit then
537 hr_api.cannot_find_prog_unit_error
538 (p_module_name => 'DELETE_cert_mbr_enrollment'
539 ,p_hook_type => 'AP'
540 );
541 end;
542
543 --
544 -- When in validation only mode raise the Validate_Enabled exception
545 --
546 if p_validate then
547 raise hr_api.validate_enabled;
548 end if;
549 --
550 -- Set all output arguments
551 --
552 --
553 hr_utility.set_location(' Leaving:'||l_proc, 170);
554 exception
555 when hr_api.validate_enabled then
556 --
557 -- As the Validate_Enabled exception has been raised
558 -- we must rollback to the savepoint
559 --
560 rollback to DELETE_cert_mbr_enrollment;
561 --
562 -- Only set output warning arguments
563 -- (Any key or derived arguments must be set to null
564 -- when validation only mode is being used.)
565 --
566 hr_utility.set_location(' Leaving:'||l_proc, 180);
567 when others then
568 --
569 -- A validation or unexpected error has occured
570 --
571 rollback to DELETE_cert_mbr_enrollment;
572 hr_utility.set_location(' Leaving:'||l_proc, 190);
573 raise;
574 end delete_cert_mbr_enrollment;
575 --
576 end OTA_CERT_MBR_ENROLLMENT_api;