[Home] [Help]
PACKAGE BODY: APPS.OTA_CERT_MEMBER_API
Source
4 -- Package Variables
1 PACKAGE BODY ota_cert_member_api as
2 /* $Header: otcmbapi.pkb 120.0 2005/06/03 08:28 appldev noship $ */
3 --
5 --
6 g_package varchar2(33) := ' ota_cert_member_api.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< CREATE_CERTIFICATION_MEMBER >-----------------|
11 -- ----------------------------------------------------------------------------
12 -- {Start Of Comments}
13 --
14 -- Description:
15 --
16 --
17 -- Prerequisites:
18 --
19 --
20 -- In Parameters:
21 -- Name Reqd Type Description
22 --
23 -- Post Success:
24 --
25 --
26 --
27 -- Post Failure:
28 --
29 --
30 -- Access Status:
31 -- Public.
32 --
33 -- {End Of Comments}
34 --
35 procedure create_certification_member
36 (p_validate in boolean default false
37 ,p_effective_date in date
38 ,p_certification_id in number
39 ,p_object_id in number
40 ,p_object_type in varchar2
41 ,p_member_sequence in number
42 ,p_business_group_id in number
43 ,p_start_date_active in date default null
44 ,p_end_date_active in date default null
45 ,p_attribute_category in varchar2 default null
46 ,p_attribute1 in varchar2 default null
47 ,p_attribute2 in varchar2 default null
48 ,p_attribute3 in varchar2 default null
49 ,p_attribute4 in varchar2 default null
50 ,p_attribute5 in varchar2 default null
51 ,p_attribute6 in varchar2 default null
52 ,p_attribute7 in varchar2 default null
53 ,p_attribute8 in varchar2 default null
54 ,p_attribute9 in varchar2 default null
55 ,p_attribute10 in varchar2 default null
56 ,p_attribute11 in varchar2 default null
57 ,p_attribute12 in varchar2 default null
58 ,p_attribute13 in varchar2 default null
59 ,p_attribute14 in varchar2 default null
60 ,p_attribute15 in varchar2 default null
61 ,p_attribute16 in varchar2 default null
62 ,p_attribute17 in varchar2 default null
63 ,p_attribute18 in varchar2 default null
64 ,p_attribute19 in varchar2 default null
68 ) is
65 ,p_attribute20 in varchar2 default null
66 ,p_certification_member_id out nocopy number
67 ,p_object_version_number out nocopy number
69 --
70 -- Declare cursors and local variables
71 --
72 l_proc varchar2(72) := g_package||' Create Certification Member';
73 l_certification_member_id number;
74 l_object_version_number number;
75 l_effective_date date;
76
77 begin
78 hr_utility.set_location('Entering:'|| l_proc, 10);
79 --
80 -- Issue a savepoint
81 --
82 savepoint create_certification_member;
83 --
84 -- Truncate the time portion from all IN date parameters
85 --
86 l_effective_date := trunc(p_effective_date);
87
88 --
89 -- Call Before Process User Hook
90 --
91 begin
92 ota_cert_member_bk1.create_certification_member_b
93 (p_effective_date => l_effective_date
94 ,p_certification_id => p_certification_id
95 ,p_object_id => p_object_id
96 ,p_object_type => p_object_type
97 ,p_member_sequence => p_member_sequence
98 ,p_business_group_id => p_business_group_id
99 ,p_start_date_active => p_start_date_active
100 ,p_end_date_active => p_end_date_active
101 ,p_attribute_category => p_attribute_category
102 ,p_attribute1 => p_attribute1
103 ,p_attribute2 => p_attribute2
104 ,p_attribute3 => p_attribute3
105 ,p_attribute4 => p_attribute4
106 ,p_attribute5 => p_attribute5
107 ,p_attribute6 => p_attribute6
108 ,p_attribute7 => p_attribute7
109 ,p_attribute8 => p_attribute8
110 ,p_attribute9 => p_attribute9
111 ,p_attribute10 => p_attribute10
112 ,p_attribute11 => p_attribute11
113 ,p_attribute12 => p_attribute12
114 ,p_attribute13 => p_attribute13
115 ,p_attribute14 => p_attribute14
116 ,p_attribute15 => p_attribute15
117 ,p_attribute16 => p_attribute16
118 ,p_attribute17 => p_attribute17
119 ,p_attribute18 => p_attribute18
120 ,p_attribute19 => p_attribute19
121 ,p_attribute20 => p_attribute20
122 );
123 exception
124 when hr_api.cannot_find_prog_unit then
125 hr_api.cannot_find_prog_unit_error
126 (p_module_name => 'create_certification_member'
127 ,p_hook_type => 'BP'
128 );
129 end;
130 --
131 -- Validation in addition to Row Handlers
132 --
133 --
134 -- Process Logic
135 --
136 ota_cmb_ins.ins
137 (p_effective_date => l_effective_date
138 ,p_certification_id => p_certification_id
139 ,p_object_id => p_object_id
140 ,p_object_type => p_object_type
141 ,p_member_sequence => p_member_sequence
142 ,p_business_group_id => p_business_group_id
143 ,p_start_date_active => p_start_date_active
144 ,p_end_date_active => p_end_date_active
145 ,p_attribute_category => p_attribute_category
146 ,p_attribute1 => p_attribute1
147 ,p_attribute2 => p_attribute2
148 ,p_attribute3 => p_attribute3
149 ,p_attribute4 => p_attribute4
150 ,p_attribute5 => p_attribute5
151 ,p_attribute6 => p_attribute6
152 ,p_attribute7 => p_attribute7
153 ,p_attribute8 => p_attribute8
154 ,p_attribute9 => p_attribute9
155 ,p_attribute10 => p_attribute10
156 ,p_attribute11 => p_attribute11
157 ,p_attribute12 => p_attribute12
158 ,p_attribute13 => p_attribute13
159 ,p_attribute14 => p_attribute14
160 ,p_attribute15 => p_attribute15
161 ,p_attribute16 => p_attribute16
162 ,p_attribute17 => p_attribute17
163 ,p_attribute18 => p_attribute18
164 ,p_attribute19 => p_attribute19
165 ,p_attribute20 => p_attribute20
166 ,p_certification_member_id => l_certification_member_id
167 ,p_object_version_number => l_object_version_number
168 );
169 --
170 -- Call After Process User Hook
171 --
172 begin
173 ota_cert_member_bk1.create_certification_member_a
174 (p_effective_date => l_effective_date
175 ,p_certification_id => p_certification_id
176 ,p_object_id => p_object_id
177 ,p_object_type => p_object_type
178 ,p_member_sequence => p_member_sequence
179 ,p_business_group_id => p_business_group_id
180 ,p_start_date_active => p_start_date_active
181 ,p_end_date_active => p_end_date_active
182 ,p_attribute_category => p_attribute_category
183 ,p_attribute1 => p_attribute1
184 ,p_attribute2 => p_attribute2
185 ,p_attribute3 => p_attribute3
186 ,p_attribute4 => p_attribute4
190 ,p_attribute8 => p_attribute8
187 ,p_attribute5 => p_attribute5
188 ,p_attribute6 => p_attribute6
189 ,p_attribute7 => p_attribute7
191 ,p_attribute9 => p_attribute9
192 ,p_attribute10 => p_attribute10
193 ,p_attribute11 => p_attribute11
194 ,p_attribute12 => p_attribute12
195 ,p_attribute13 => p_attribute13
196 ,p_attribute14 => p_attribute14
197 ,p_attribute15 => p_attribute15
198 ,p_attribute16 => p_attribute16
199 ,p_attribute17 => p_attribute17
200 ,p_attribute18 => p_attribute18
201 ,p_attribute19 => p_attribute19
202 ,p_attribute20 => p_attribute20
203 );
204 exception
205 when hr_api.cannot_find_prog_unit then
206 hr_api.cannot_find_prog_unit_error
207 (p_module_name => 'create_certification_member'
208 ,p_hook_type => 'AP'
209 );
210 end;
211 --
212 -- When in validation only mode raise the Validate_Enabled exception
213 --
214 if p_validate then
215 raise hr_api.validate_enabled;
216 end if;
217 --
218 -- Set all output arguments
219 --
220 p_certification_member_id := l_certification_member_id;
221 p_object_version_number := l_object_version_number;
222 --
223 hr_utility.set_location(' Leaving:'||l_proc, 70);
224 exception
225 when hr_api.validate_enabled then
226 --
227 -- As the Validate_Enabled exception has been raised
228 -- we must rollback to the savepoint
229 --
230 rollback to create_certification_member;
231 --
232 -- Only set output warning arguments
233 -- (Any key or derived arguments must be set to null
234 -- when validation only mode is being used.)
235 --
236 p_certification_member_id := null;
237 p_object_version_number := null;
238 hr_utility.set_location(' Leaving:'||l_proc, 80);
239 raise;
240 end create_certification_member;
241
242 --
243 -- ----------------------------------------------------------------------------
244 -- |--------------------------< UPDATE_CERTIFICATION_MEMBER >-----------------|
245 -- ----------------------------------------------------------------------------
246 -- {Start Of Comments}
247 --
248 -- Description:
249 --
250 --
251 -- Prerequisites:
252 --
253 --
254 -- In Parameters:
255 -- Name Reqd Type Description
256 --
257 -- Post Success:
258 --
259 --
260 --
261 -- Post Failure:
262 --
263 --
264 -- Access Status:
265 -- Public.
266 --
267 -- {End Of Comments}
268 --
269 procedure update_certification_member
270 (p_validate in boolean default false
271 ,p_effective_date in date
272 ,p_certification_member_id in number
273 ,p_object_version_number in out nocopy number
274 ,p_object_id in number
275 ,p_object_type in varchar2
276 ,p_member_sequence in number
277 ,p_start_date_active in date default null
278 ,p_end_date_active in date default null
279 ,p_attribute_category in varchar2 default null
280 ,p_attribute1 in varchar2 default null
281 ,p_attribute2 in varchar2 default null
282 ,p_attribute3 in varchar2 default null
283 ,p_attribute4 in varchar2 default null
284 ,p_attribute5 in varchar2 default null
285 ,p_attribute6 in varchar2 default null
286 ,p_attribute7 in varchar2 default null
287 ,p_attribute8 in varchar2 default null
288 ,p_attribute9 in varchar2 default null
289 ,p_attribute10 in varchar2 default null
290 ,p_attribute11 in varchar2 default null
291 ,p_attribute12 in varchar2 default null
292 ,p_attribute13 in varchar2 default null
293 ,p_attribute14 in varchar2 default null
294 ,p_attribute15 in varchar2 default null
295 ,p_attribute16 in varchar2 default null
296 ,p_attribute17 in varchar2 default null
297 ,p_attribute18 in varchar2 default null
298 ,p_attribute19 in varchar2 default null
299 ,p_attribute20 in varchar2 default null
300 ) is
301 --
302 -- Declare cursors and local variables
303 --
304 l_proc varchar2(72) := g_package||' Update Certification member';
305 l_object_version_number number := p_object_version_number;
306 l_effective_date date;
307
308 begin
309 hr_utility.set_location('Entering:'|| l_proc, 10);
310 --
311 -- Issue a savepoint
312 --
313 savepoint update_certification_member;
314
315 --
316 -- Truncate the time portion from all IN date parameters
317 --
318 l_effective_date := trunc(p_effective_date);
319 --
320
321 -- Call Before Process User Hook
322 --
323 begin
324 ota_cert_member_bk2.update_certification_member_b
325 (p_effective_date => l_effective_date
329 ,p_object_type => p_object_type
326 ,p_certification_member_id => p_certification_member_id
327 ,p_object_version_number => l_object_version_number
328 ,p_object_id => p_object_id
330 ,p_member_sequence => p_member_sequence
331 ,p_start_date_active => p_start_date_active
332 ,p_end_date_active => p_end_date_active
333 ,p_attribute_category => p_attribute_category
334 ,p_attribute1 => p_attribute1
335 ,p_attribute2 => p_attribute2
336 ,p_attribute3 => p_attribute3
337 ,p_attribute4 => p_attribute4
338 ,p_attribute5 => p_attribute5
339 ,p_attribute6 => p_attribute6
340 ,p_attribute7 => p_attribute7
341 ,p_attribute8 => p_attribute8
342 ,p_attribute9 => p_attribute9
343 ,p_attribute10 => p_attribute10
344 ,p_attribute11 => p_attribute11
345 ,p_attribute12 => p_attribute12
346 ,p_attribute13 => p_attribute13
347 ,p_attribute14 => p_attribute14
348 ,p_attribute15 => p_attribute15
349 ,p_attribute16 => p_attribute16
350 ,p_attribute17 => p_attribute17
351 ,p_attribute18 => p_attribute18
352 ,p_attribute19 => p_attribute19
353 ,p_attribute20 => p_attribute20
354 );
355 exception
356 when hr_api.cannot_find_prog_unit then
357 hr_api.cannot_find_prog_unit_error
358 (p_module_name => 'UPDATE_certification_MEMBER'
359 ,p_hook_type => 'BP'
360 );
361 end;
362 --
363 -- Validation in addition to Row Handlers
364 --
365 --
366 -- Process Logic
367 --
368 ota_cmb_upd.upd
369 (p_effective_date => l_effective_date
370 ,p_certification_member_id => p_certification_member_id
371 ,p_object_version_number => l_object_version_number
372 ,p_object_id => p_object_id
373 ,p_object_type => p_object_type
374 ,p_member_sequence => p_member_sequence
375 ,p_start_date_active => p_start_date_active
376 ,p_end_date_active => p_end_date_active
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 -- Call After Process User Hook
401 --
402 begin
403 ota_cert_member_bk2.update_certification_member_a
404 (p_effective_date => l_effective_date
405 ,p_certification_member_id => p_certification_member_id
406 ,p_object_version_number => l_object_version_number
407 ,p_object_id => p_object_id
408 ,p_object_type => p_object_type
409 ,p_member_sequence => p_member_sequence
410 ,p_start_date_active => p_start_date_active
411 ,p_end_date_active => p_end_date_active
412 ,p_attribute_category => p_attribute_category
413 ,p_attribute1 => p_attribute1
414 ,p_attribute2 => p_attribute2
415 ,p_attribute3 => p_attribute3
416 ,p_attribute4 => p_attribute4
417 ,p_attribute5 => p_attribute5
418 ,p_attribute6 => p_attribute6
419 ,p_attribute7 => p_attribute7
420 ,p_attribute8 => p_attribute8
421 ,p_attribute9 => p_attribute9
422 ,p_attribute10 => p_attribute10
423 ,p_attribute11 => p_attribute11
424 ,p_attribute12 => p_attribute12
425 ,p_attribute13 => p_attribute13
426 ,p_attribute14 => p_attribute14
427 ,p_attribute15 => p_attribute15
428 ,p_attribute16 => p_attribute16
429 ,p_attribute17 => p_attribute17
430 ,p_attribute18 => p_attribute18
431 ,p_attribute19 => p_attribute19
432 ,p_attribute20 => p_attribute20
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_certification_MEMBER'
438 ,p_hook_type => 'AP'
439 );
440 end;
441 --
442 -- When in validation only mode raise the Validate_Enabled exception
443 --
444 if p_validate then
445 raise hr_api.validate_enabled;
446 end if;
447 --
448 -- Set all output arguments
449 --
450 p_object_version_number := l_object_version_number;
451 --
452 hr_utility.set_location(' Leaving:'||l_proc, 70);
453 exception
454 when hr_api.validate_enabled then
455 --
456 -- As the Validate_Enabled exception has been raised
457 -- we must rollback to the savepoint
458 --
459 rollback to update_certification_member;
460 --
461 -- Only set output warning arguments
462 -- (Any key or derived arguments must be set to null
463 -- when validation only mode is being used.)
464 --
465 p_object_version_number := null;
466 hr_utility.set_location(' Leaving:'||l_proc, 80);
467 when others then
468 --
469 -- A validation or unexpected error has occured
470 --
471 rollback to update_certification_member;
472 hr_utility.set_location(' Leaving:'||l_proc, 90);
473 p_object_version_number := l_object_version_number;
474 raise;
475 end update_certification_member;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |--------------------------< DELETE_CERTIFICATION_MEMBER >-----------------|
479 -- ----------------------------------------------------------------------------
480 -- {Start Of Comments}
481 --
482 -- Description:
483 --
484 -- This business process allows the user to delete a certification
485 --
486 -- Prerequisites:
487 --
488 --
489 -- In Parameters:
490 -- Name Reqd Type Description
491 --
492 -- Post Success:
493 --
494 -- Post Failure:
495 --
496 --
497 -- Access Status:
498 -- Public.
499 --
500 -- {End Of Comments}
501 --
502 procedure delete_certification_member
503 (p_validate in boolean default false
504 ,p_certification_member_id in number
505 ,p_object_version_number in number
506 ) is
507 --
508 -- Declare cursors and local variables
509 --
510 l_proc varchar2(72) := g_package||' Delete Certification Member';
511 --
512 begin
513 hr_utility.set_location('Entering:'|| l_proc, 10);
514 --
515 -- Issue a savepoint
516 --
517 savepoint delete_certification_member;
518 --
519 -- Call Before Process User Hook
520 --
521 begin
522 ota_cert_member_bk3.delete_certification_member_b
523 (p_certification_member_id => p_certification_member_id
524 ,p_object_version_number => p_object_version_number
525 );
526 exception
527 when hr_api.cannot_find_prog_unit then
528 hr_api.cannot_find_prog_unit_error
529 (p_module_name => 'DELETE_CERTIFICATION_MEMBER'
530 ,p_hook_type => 'BP'
531 );
532 end;
533 --
534 -- Process Logic
535 --
536 ota_cmb_del.del
537 (p_certification_member_id => p_certification_member_id
538 ,p_object_version_number => p_object_version_number
539 );
540 --
541 -- Call After Process User Hook
542 --
543 begin
544 ota_cert_member_bk3.delete_certification_member_a
545 (p_certification_member_id => p_certification_member_id
546 ,p_object_version_number => p_object_version_number
547 );
548 exception
549 when hr_api.cannot_find_prog_unit then
550 hr_api.cannot_find_prog_unit_error
551 (p_module_name => 'DELETE_CERTIFICATION_MEMBER'
552 ,p_hook_type => 'AP'
553 );
554 end;
555 --
556 -- When in validation only mode raise the Validate_Enabled exception
557 --
558 if p_validate then
559 raise hr_api.validate_enabled;
560 end if;
561 --
562 -- Set all output arguments
563 --
564 --
565 hr_utility.set_location(' Leaving:'||l_proc, 170);
566 exception
567 when hr_api.validate_enabled then
568 --
569 -- As the Validate_Enabled exception has been raised
570 -- we must rollback to the savepoint
571 --
572 rollback to delete_certification_member;
573 --
574 -- Only set output warning arguments
575 -- (Any key or derived arguments must be set to null
576 -- when validation only mode is being used.)
577 --
578 hr_utility.set_location(' Leaving:'||l_proc, 180);
579 when others then
580 --
581 -- A validation or unexpected error has occured
582 --
583 rollback to delete_certification_member;
584 hr_utility.set_location(' Leaving:'||l_proc, 190);
585 raise;
586 end delete_certification_member;
587 --
588 end ota_cert_member_api;