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