[Home] [Help]
PACKAGE BODY: APPS.PER_QUALIFICATIONS_SWI
Source
1 Package Body per_qualifications_swi As
2 /* $Header: pequaswi.pkb 115.2 2002/12/05 17:24:46 eumenyio ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'per_qualifications_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_qualification >-------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_qualification
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date
14 ,p_qualification_type_id in number
15 ,p_business_group_id in number default null
16 ,p_person_id in number default null
17 ,p_title in varchar2 default null
18 ,p_grade_attained in varchar2 default null
19 ,p_status in varchar2 default null
20 ,p_awarded_date in date default null
21 ,p_fee in number default null
22 ,p_fee_currency in varchar2 default null
23 ,p_training_completed_amount in number default null
24 ,p_reimbursement_arrangements in varchar2 default null
25 ,p_training_completed_units in varchar2 default null
26 ,p_total_training_amount in number default null
27 ,p_start_date in date default null
28 ,p_end_date in date default null
29 ,p_license_number in varchar2 default null
30 ,p_expiry_date in date default null
31 ,p_license_restrictions in varchar2 default null
32 ,p_projected_completion_date in date default null
33 ,p_awarding_body in varchar2 default null
34 ,p_tuition_method in varchar2 default null
35 ,p_group_ranking in varchar2 default null
36 ,p_comments in varchar2 default null
37 ,p_attendance_id in number default null
38 ,p_attribute_category in varchar2 default null
39 ,p_attribute1 in varchar2 default null
40 ,p_attribute2 in varchar2 default null
41 ,p_attribute3 in varchar2 default null
42 ,p_attribute4 in varchar2 default null
43 ,p_attribute5 in varchar2 default null
44 ,p_attribute6 in varchar2 default null
45 ,p_attribute7 in varchar2 default null
46 ,p_attribute8 in varchar2 default null
47 ,p_attribute9 in varchar2 default null
48 ,p_attribute10 in varchar2 default null
49 ,p_attribute11 in varchar2 default null
50 ,p_attribute12 in varchar2 default null
51 ,p_attribute13 in varchar2 default null
52 ,p_attribute14 in varchar2 default null
53 ,p_attribute15 in varchar2 default null
54 ,p_attribute16 in varchar2 default null
55 ,p_attribute17 in varchar2 default null
56 ,p_attribute18 in varchar2 default null
57 ,p_attribute19 in varchar2 default null
58 ,p_attribute20 in varchar2 default null
59 ,p_party_id in number default null
60 ,p_qua_information_category in varchar2 default null
61 ,p_qua_information1 in varchar2 default null
62 ,p_qua_information2 in varchar2 default null
63 ,p_qua_information3 in varchar2 default null
64 ,p_qua_information4 in varchar2 default null
65 ,p_qua_information5 in varchar2 default null
66 ,p_qua_information6 in varchar2 default null
67 ,p_qua_information7 in varchar2 default null
68 ,p_qua_information8 in varchar2 default null
69 ,p_qua_information9 in varchar2 default null
70 ,p_qua_information10 in varchar2 default null
71 ,p_qua_information11 in varchar2 default null
72 ,p_qua_information12 in varchar2 default null
73 ,p_qua_information13 in varchar2 default null
74 ,p_qua_information14 in varchar2 default null
75 ,p_qua_information15 in varchar2 default null
76 ,p_qua_information16 in varchar2 default null
77 ,p_qua_information17 in varchar2 default null
78 ,p_qua_information18 in varchar2 default null
79 ,p_qua_information19 in varchar2 default null
80 ,p_qua_information20 in varchar2 default null
81 ,p_professional_body_name in varchar2 default null
82 ,p_membership_number in varchar2 default null
83 ,p_membership_category in varchar2 default null
84 ,p_subscription_payment_method in varchar2 default null
85 ,p_qualification_id in number
86 ,p_object_version_number out nocopy number
87 ,p_return_status out nocopy varchar2
88 ) is
89 --
90 -- Variables for API Boolean parameters
91 l_validate boolean;
92 --
93 -- Variables for IN/OUT parameters
94 --
95 -- Other variables
96 l_qualification_id number;
97 l_proc varchar2(72) := g_package ||'create_qualification';
98 Begin
99 hr_utility.set_location(' Entering:' || l_proc,10);
100 --
101 -- Issue a savepoint
102 --
103 savepoint create_qualification_swi;
104 --
105 -- Initialise Multiple Message Detection
106 --
107 hr_multi_message.enable_message_list;
108 --
109 -- Remember IN OUT parameter IN values
110 --
111 --
112 -- Convert constant values to their corresponding boolean value
113 --
114 l_validate :=
115 hr_api.constant_to_boolean
116 (p_constant_value => p_validate);
117 --
118 -- Register Surrogate ID or user key values
119 --
120 per_qua_ins.set_base_key_value
121 (p_qualification_id => p_qualification_id
122 );
123 --
124 -- Call API
125 --
126 per_qualifications_api.create_qualification
127 (p_validate => l_validate
128 ,p_effective_date => p_effective_date
129 ,p_qualification_type_id => p_qualification_type_id
130 ,p_business_group_id => p_business_group_id
131 ,p_person_id => p_person_id
132 ,p_title => p_title
133 ,p_grade_attained => p_grade_attained
134 ,p_status => p_status
135 ,p_awarded_date => p_awarded_date
136 ,p_fee => p_fee
137 ,p_fee_currency => p_fee_currency
138 ,p_training_completed_amount => p_training_completed_amount
139 ,p_reimbursement_arrangements => p_reimbursement_arrangements
140 ,p_training_completed_units => p_training_completed_units
141 ,p_total_training_amount => p_total_training_amount
142 ,p_start_date => p_start_date
143 ,p_end_date => p_end_date
144 ,p_license_number => p_license_number
145 ,p_expiry_date => p_expiry_date
146 ,p_license_restrictions => p_license_restrictions
147 ,p_projected_completion_date => p_projected_completion_date
148 ,p_awarding_body => p_awarding_body
149 ,p_tuition_method => p_tuition_method
150 ,p_group_ranking => p_group_ranking
151 ,p_comments => p_comments
152 ,p_attendance_id => p_attendance_id
153 ,p_attribute_category => p_attribute_category
154 ,p_attribute1 => p_attribute1
155 ,p_attribute2 => p_attribute2
156 ,p_attribute3 => p_attribute3
157 ,p_attribute4 => p_attribute4
158 ,p_attribute5 => p_attribute5
159 ,p_attribute6 => p_attribute6
160 ,p_attribute7 => p_attribute7
161 ,p_attribute8 => p_attribute8
162 ,p_attribute9 => p_attribute9
163 ,p_attribute10 => p_attribute10
164 ,p_attribute11 => p_attribute11
165 ,p_attribute12 => p_attribute12
166 ,p_attribute13 => p_attribute13
167 ,p_attribute14 => p_attribute14
168 ,p_attribute15 => p_attribute15
169 ,p_attribute16 => p_attribute16
170 ,p_attribute17 => p_attribute17
171 ,p_attribute18 => p_attribute18
172 ,p_attribute19 => p_attribute19
173 ,p_attribute20 => p_attribute20
174 ,p_party_id => p_party_id
175 ,p_qua_information_category => p_qua_information_category
176 ,p_qua_information1 => p_qua_information1
177 ,p_qua_information2 => p_qua_information2
178 ,p_qua_information3 => p_qua_information3
179 ,p_qua_information4 => p_qua_information4
180 ,p_qua_information5 => p_qua_information5
181 ,p_qua_information6 => p_qua_information6
182 ,p_qua_information7 => p_qua_information7
183 ,p_qua_information8 => p_qua_information8
184 ,p_qua_information9 => p_qua_information9
185 ,p_qua_information10 => p_qua_information10
186 ,p_qua_information11 => p_qua_information11
187 ,p_qua_information12 => p_qua_information12
188 ,p_qua_information13 => p_qua_information13
189 ,p_qua_information14 => p_qua_information14
190 ,p_qua_information15 => p_qua_information15
191 ,p_qua_information16 => p_qua_information16
192 ,p_qua_information17 => p_qua_information17
193 ,p_qua_information18 => p_qua_information18
194 ,p_qua_information19 => p_qua_information19
195 ,p_qua_information20 => p_qua_information20
196 ,p_professional_body_name => p_professional_body_name
197 ,p_membership_number => p_membership_number
198 ,p_membership_category => p_membership_category
199 ,p_subscription_payment_method => p_subscription_payment_method
200 ,p_qualification_id => l_qualification_id
201 ,p_object_version_number => p_object_version_number
202 );
203 --
204 -- Convert API warning boolean parameter values to specific
205 -- messages and add them to Multiple Message List
206 --
207 --
208 -- Convert API non-warning boolean parameter values
209 --
210 --
211 -- Derive the API return status value based on whether
212 -- messages of any type exist in the Multiple Message List.
213 -- Also disable Multiple Message Detection.
214 --
215 p_return_status := hr_multi_message.get_return_status_disable;
216 hr_utility.set_location(' Leaving:' || l_proc,20);
217 --
218 exception
219 when hr_multi_message.error_message_exist then
220 --
221 -- Catch the Multiple Message List exception which
222 -- indicates API processing has been aborted because
223 -- at least one message exists in the list.
224 --
225 rollback to create_qualification_swi;
226 --
227 -- Reset IN OUT parameters and set OUT parameters
228 --
229 p_object_version_number := null;
230 p_return_status := hr_multi_message.get_return_status_disable;
231 hr_utility.set_location(' Leaving:' || l_proc, 30);
232 when others then
233 --
234 -- When Multiple Message Detection is enabled catch
235 -- any Application specific or other unexpected
236 -- exceptions. Adding appropriate details to the
237 -- Multiple Message List. Otherwise re-raise the
238 -- error.
239 --
240 rollback to create_qualification_swi;
241 if hr_multi_message.unexpected_error_add(l_proc) then
242 hr_utility.set_location(' Leaving:' || l_proc,40);
243 raise;
244 end if;
245 --
246 -- Reset IN OUT and set OUT parameters
247 --
248 p_object_version_number := null;
249 p_return_status := hr_multi_message.get_return_status_disable;
250 hr_utility.set_location(' Leaving:' || l_proc,50);
251 end create_qualification;
252 -- ----------------------------------------------------------------------------
253 -- |-------------------------< delete_qualification >-------------------------|
254 -- ----------------------------------------------------------------------------
255 PROCEDURE delete_qualification
256 (p_validate in number default hr_api.g_false_num
257 ,p_qualification_id in number
258 ,p_object_version_number in number
259 ,p_return_status out nocopy varchar2
260 ) is
261 --
262 -- Variables for API Boolean parameters
263 l_validate boolean;
264 --
265 -- Variables for IN/OUT parameters
266 --
267 -- Other variables
268 l_proc varchar2(72) := g_package ||'delete_qualification';
269 Begin
270 hr_utility.set_location(' Entering:' || l_proc,10);
271 --
272 -- Issue a savepoint
273 --
274 savepoint delete_qualification_swi;
275 --
276 -- Initialise Multiple Message Detection
277 --
278 hr_multi_message.enable_message_list;
279 --
280 -- Remember IN OUT parameter IN values
281 --
282 --
283 -- Convert constant values to their corresponding boolean value
284 --
285 l_validate :=
286 hr_api.constant_to_boolean
287 (p_constant_value => p_validate);
288 --
289 -- Register Surrogate ID or user key values
290 --
291 --
292 -- Call API
293 --
294 per_qualifications_api.delete_qualification
295 (p_validate => l_validate
296 ,p_qualification_id => p_qualification_id
297 ,p_object_version_number => p_object_version_number
298 );
299 --
300 -- Convert API warning boolean parameter values to specific
301 -- messages and add them to Multiple Message List
302 --
303 --
304 -- Convert API non-warning boolean parameter values
305 --
306 --
307 -- Derive the API return status value based on whether
308 -- messages of any type exist in the Multiple Message List.
309 -- Also disable Multiple Message Detection.
310 --
311 p_return_status := hr_multi_message.get_return_status_disable;
312 hr_utility.set_location(' Leaving:' || l_proc,20);
313 --
314 exception
315 when hr_multi_message.error_message_exist then
316 --
317 -- Catch the Multiple Message List exception which
318 -- indicates API processing has been aborted because
319 -- at least one message exists in the list.
320 --
321 rollback to delete_qualification_swi;
322 --
323 -- Reset IN OUT parameters and set OUT parameters
324 --
325 p_return_status := hr_multi_message.get_return_status_disable;
326 hr_utility.set_location(' Leaving:' || l_proc, 30);
327 when others then
328 --
329 -- When Multiple Message Detection is enabled catch
330 -- any Application specific or other unexpected
331 -- exceptions. Adding appropriate details to the
332 -- Multiple Message List. Otherwise re-raise the
333 -- error.
334 --
335 rollback to delete_qualification_swi;
336 if hr_multi_message.unexpected_error_add(l_proc) then
337 hr_utility.set_location(' Leaving:' || l_proc,40);
338 raise;
339 end if;
340 --
341 -- Reset IN OUT and set OUT parameters
342 --
343 p_return_status := hr_multi_message.get_return_status_disable;
344 hr_utility.set_location(' Leaving:' || l_proc,50);
345 end delete_qualification;
346 -- ----------------------------------------------------------------------------
347 -- |-------------------------< update_qualification >-------------------------|
348 -- ----------------------------------------------------------------------------
349 PROCEDURE update_qualification
350 (p_validate in number default hr_api.g_false_num
351 ,p_effective_date in date
352 ,p_qualification_id in number
353 ,p_qualification_type_id in number default hr_api.g_number
354 ,p_title in varchar2 default hr_api.g_varchar2
355 ,p_grade_attained in varchar2 default hr_api.g_varchar2
356 ,p_status in varchar2 default hr_api.g_varchar2
357 ,p_awarded_date in date default hr_api.g_date
358 ,p_fee in number default hr_api.g_number
359 ,p_fee_currency in varchar2 default hr_api.g_varchar2
360 ,p_training_completed_amount in number default hr_api.g_number
361 ,p_reimbursement_arrangements in varchar2 default hr_api.g_varchar2
362 ,p_training_completed_units in varchar2 default hr_api.g_varchar2
363 ,p_total_training_amount in number default hr_api.g_number
364 ,p_start_date in date default hr_api.g_date
365 ,p_end_date in date default hr_api.g_date
366 ,p_license_number in varchar2 default hr_api.g_varchar2
367 ,p_expiry_date in date default hr_api.g_date
368 ,p_license_restrictions in varchar2 default hr_api.g_varchar2
369 ,p_projected_completion_date in date default hr_api.g_date
370 ,p_awarding_body in varchar2 default hr_api.g_varchar2
371 ,p_tuition_method in varchar2 default hr_api.g_varchar2
372 ,p_group_ranking in varchar2 default hr_api.g_varchar2
373 ,p_comments in varchar2 default hr_api.g_varchar2
374 ,p_attendance_id in number default hr_api.g_number
375 ,p_attribute_category in varchar2 default hr_api.g_varchar2
376 ,p_attribute1 in varchar2 default hr_api.g_varchar2
377 ,p_attribute2 in varchar2 default hr_api.g_varchar2
378 ,p_attribute3 in varchar2 default hr_api.g_varchar2
379 ,p_attribute4 in varchar2 default hr_api.g_varchar2
380 ,p_attribute5 in varchar2 default hr_api.g_varchar2
381 ,p_attribute6 in varchar2 default hr_api.g_varchar2
382 ,p_attribute7 in varchar2 default hr_api.g_varchar2
383 ,p_attribute8 in varchar2 default hr_api.g_varchar2
384 ,p_attribute9 in varchar2 default hr_api.g_varchar2
385 ,p_attribute10 in varchar2 default hr_api.g_varchar2
386 ,p_attribute11 in varchar2 default hr_api.g_varchar2
387 ,p_attribute12 in varchar2 default hr_api.g_varchar2
388 ,p_attribute13 in varchar2 default hr_api.g_varchar2
389 ,p_attribute14 in varchar2 default hr_api.g_varchar2
390 ,p_attribute15 in varchar2 default hr_api.g_varchar2
391 ,p_attribute16 in varchar2 default hr_api.g_varchar2
392 ,p_attribute17 in varchar2 default hr_api.g_varchar2
393 ,p_attribute18 in varchar2 default hr_api.g_varchar2
394 ,p_attribute19 in varchar2 default hr_api.g_varchar2
395 ,p_attribute20 in varchar2 default hr_api.g_varchar2
396 ,p_qua_information_category in varchar2 default hr_api.g_varchar2
397 ,p_qua_information1 in varchar2 default hr_api.g_varchar2
398 ,p_qua_information2 in varchar2 default hr_api.g_varchar2
399 ,p_qua_information3 in varchar2 default hr_api.g_varchar2
400 ,p_qua_information4 in varchar2 default hr_api.g_varchar2
401 ,p_qua_information5 in varchar2 default hr_api.g_varchar2
402 ,p_qua_information6 in varchar2 default hr_api.g_varchar2
403 ,p_qua_information7 in varchar2 default hr_api.g_varchar2
404 ,p_qua_information8 in varchar2 default hr_api.g_varchar2
405 ,p_qua_information9 in varchar2 default hr_api.g_varchar2
406 ,p_qua_information10 in varchar2 default hr_api.g_varchar2
407 ,p_qua_information11 in varchar2 default hr_api.g_varchar2
408 ,p_qua_information12 in varchar2 default hr_api.g_varchar2
409 ,p_qua_information13 in varchar2 default hr_api.g_varchar2
410 ,p_qua_information14 in varchar2 default hr_api.g_varchar2
411 ,p_qua_information15 in varchar2 default hr_api.g_varchar2
412 ,p_qua_information16 in varchar2 default hr_api.g_varchar2
413 ,p_qua_information17 in varchar2 default hr_api.g_varchar2
414 ,p_qua_information18 in varchar2 default hr_api.g_varchar2
415 ,p_qua_information19 in varchar2 default hr_api.g_varchar2
416 ,p_qua_information20 in varchar2 default hr_api.g_varchar2
417 ,p_professional_body_name in varchar2 default hr_api.g_varchar2
418 ,p_membership_number in varchar2 default hr_api.g_varchar2
419 ,p_membership_category in varchar2 default hr_api.g_varchar2
420 ,p_subscription_payment_method in varchar2 default hr_api.g_varchar2
421 ,p_object_version_number in out nocopy number
422 ,p_return_status out nocopy varchar2
423 ) is
424 --
425 -- Variables for API Boolean parameters
426 l_validate boolean;
427 --
428 -- Variables for IN/OUT parameters
429 l_object_version_number number;
430 --
431 -- Other variables
432 l_proc varchar2(72) := g_package ||'update_qualification';
433 Begin
434 hr_utility.set_location(' Entering:' || l_proc,10);
435 --
436 -- Issue a savepoint
437 --
438 savepoint update_qualification_swi;
439 --
440 -- Initialise Multiple Message Detection
441 --
442 hr_multi_message.enable_message_list;
443 --
444 -- Remember IN OUT parameter IN values
445 --
446 l_object_version_number := p_object_version_number;
447 --
448 -- Convert constant values to their corresponding boolean value
449 --
450 l_validate :=
451 hr_api.constant_to_boolean
452 (p_constant_value => p_validate);
453 --
454 -- Register Surrogate ID or user key values
455 --
456 --
457 -- Call API
458 --
459 per_qualifications_api.update_qualification
460 (p_validate => l_validate
461 ,p_effective_date => p_effective_date
462 ,p_qualification_id => p_qualification_id
463 ,p_qualification_type_id => p_qualification_type_id
464 ,p_title => p_title
465 ,p_grade_attained => p_grade_attained
466 ,p_status => p_status
467 ,p_awarded_date => p_awarded_date
468 ,p_fee => p_fee
469 ,p_fee_currency => p_fee_currency
470 ,p_training_completed_amount => p_training_completed_amount
471 ,p_reimbursement_arrangements => p_reimbursement_arrangements
472 ,p_training_completed_units => p_training_completed_units
473 ,p_total_training_amount => p_total_training_amount
474 ,p_start_date => p_start_date
475 ,p_end_date => p_end_date
476 ,p_license_number => p_license_number
477 ,p_expiry_date => p_expiry_date
478 ,p_license_restrictions => p_license_restrictions
479 ,p_projected_completion_date => p_projected_completion_date
480 ,p_awarding_body => p_awarding_body
481 ,p_tuition_method => p_tuition_method
482 ,p_group_ranking => p_group_ranking
483 ,p_comments => p_comments
484 ,p_attendance_id => p_attendance_id
485 ,p_attribute_category => p_attribute_category
486 ,p_attribute1 => p_attribute1
487 ,p_attribute2 => p_attribute2
488 ,p_attribute3 => p_attribute3
489 ,p_attribute4 => p_attribute4
490 ,p_attribute5 => p_attribute5
491 ,p_attribute6 => p_attribute6
492 ,p_attribute7 => p_attribute7
493 ,p_attribute8 => p_attribute8
494 ,p_attribute9 => p_attribute9
495 ,p_attribute10 => p_attribute10
496 ,p_attribute11 => p_attribute11
497 ,p_attribute12 => p_attribute12
498 ,p_attribute13 => p_attribute13
499 ,p_attribute14 => p_attribute14
500 ,p_attribute15 => p_attribute15
501 ,p_attribute16 => p_attribute16
502 ,p_attribute17 => p_attribute17
503 ,p_attribute18 => p_attribute18
504 ,p_attribute19 => p_attribute19
505 ,p_attribute20 => p_attribute20
506 ,p_qua_information_category => p_qua_information_category
507 ,p_qua_information1 => p_qua_information1
508 ,p_qua_information2 => p_qua_information2
509 ,p_qua_information3 => p_qua_information3
510 ,p_qua_information4 => p_qua_information4
511 ,p_qua_information5 => p_qua_information5
512 ,p_qua_information6 => p_qua_information6
513 ,p_qua_information7 => p_qua_information7
514 ,p_qua_information8 => p_qua_information8
515 ,p_qua_information9 => p_qua_information9
516 ,p_qua_information10 => p_qua_information10
517 ,p_qua_information11 => p_qua_information11
518 ,p_qua_information12 => p_qua_information12
519 ,p_qua_information13 => p_qua_information13
520 ,p_qua_information14 => p_qua_information14
521 ,p_qua_information15 => p_qua_information15
522 ,p_qua_information16 => p_qua_information16
523 ,p_qua_information17 => p_qua_information17
524 ,p_qua_information18 => p_qua_information18
525 ,p_qua_information19 => p_qua_information19
526 ,p_qua_information20 => p_qua_information20
527 ,p_professional_body_name => p_professional_body_name
528 ,p_membership_number => p_membership_number
529 ,p_membership_category => p_membership_category
530 ,p_subscription_payment_method => p_subscription_payment_method
531 ,p_object_version_number => p_object_version_number
532 );
533 --
534 -- Convert API warning boolean parameter values to specific
535 -- messages and add them to Multiple Message List
536 --
537 --
538 -- Convert API non-warning boolean parameter values
539 --
540 --
541 -- Derive the API return status value based on whether
542 -- messages of any type exist in the Multiple Message List.
543 -- Also disable Multiple Message Detection.
544 --
545 p_return_status := hr_multi_message.get_return_status_disable;
546 hr_utility.set_location(' Leaving:' || l_proc,20);
547 --
548 exception
549 when hr_multi_message.error_message_exist then
550 --
551 -- Catch the Multiple Message List exception which
552 -- indicates API processing has been aborted because
553 -- at least one message exists in the list.
554 --
555 rollback to update_qualification_swi;
556 --
557 -- Reset IN OUT parameters and set OUT parameters
558 --
559 p_object_version_number := l_object_version_number;
560 p_return_status := hr_multi_message.get_return_status_disable;
561 hr_utility.set_location(' Leaving:' || l_proc, 30);
562 when others then
563 --
564 -- When Multiple Message Detection is enabled catch
565 -- any Application specific or other unexpected
566 -- exceptions. Adding appropriate details to the
567 -- Multiple Message List. Otherwise re-raise the
568 -- error.
569 --
570 rollback to update_qualification_swi;
571 if hr_multi_message.unexpected_error_add(l_proc) then
572 hr_utility.set_location(' Leaving:' || l_proc,40);
573 raise;
574 end if;
575 --
576 -- Reset IN OUT and set OUT parameters
577 --
578 p_object_version_number := l_object_version_number;
579 p_return_status := hr_multi_message.get_return_status_disable;
580 hr_utility.set_location(' Leaving:' || l_proc,50);
581 end update_qualification;
582 end per_qualifications_swi;