[Home] [Help]
PACKAGE BODY: APPS.PER_QUALIFICATIONS_API
Source
1 Package body PER_QUALIFICATIONS_API as
2 /* $Header: pequaapi.pkb 120.0.12010000.3 2009/03/12 11:38:16 dparthas ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'per_qualifications_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------<CREATE_QUALIFICATION>--------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure CREATE_QUALIFICATION
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_qualification_type_id in number
16 ,p_language_code in varchar2 default hr_api.userenv_lang
17 ,p_business_group_id in number default null
18 ,p_person_id in number default null
19 ,p_title in varchar2 default null
20 ,p_grade_attained in varchar2 default null
21 ,p_status in varchar2 default null
22 ,p_awarded_date in date default null
23 ,p_fee in number default null
24 ,p_fee_currency in varchar2 default null
25 ,p_training_completed_amount in number default null
26 ,p_reimbursement_arrangements in varchar2 default null
27 ,p_training_completed_units in varchar2 default null
28 ,p_total_training_amount in number default null
29 ,p_start_date in date default null
30 ,p_end_date in date default null
31 ,p_license_number in varchar2 default null
32 ,p_expiry_date in date default null
33 ,p_license_restrictions in varchar2 default null
34 ,p_projected_completion_date in date default null
35 ,p_awarding_body in varchar2 default null
36 ,p_tuition_method in varchar2 default null
37 ,p_group_ranking in varchar2 default null
38 ,p_comments in varchar2 default null
39 ,p_attendance_id in number default null
40 ,p_attribute_category in varchar2 default null
41 ,p_attribute1 in varchar2 default null
42 ,p_attribute2 in varchar2 default null
43 ,p_attribute3 in varchar2 default null
44 ,p_attribute4 in varchar2 default null
45 ,p_attribute5 in varchar2 default null
46 ,p_attribute6 in varchar2 default null
47 ,p_attribute7 in varchar2 default null
48 ,p_attribute8 in varchar2 default null
49 ,p_attribute9 in varchar2 default null
50 ,p_attribute10 in varchar2 default null
51 ,p_attribute11 in varchar2 default null
52 ,p_attribute12 in varchar2 default null
53 ,p_attribute13 in varchar2 default null
54 ,p_attribute14 in varchar2 default null
55 ,p_attribute15 in varchar2 default null
56 ,p_attribute16 in varchar2 default null
57 ,p_attribute17 in varchar2 default null
58 ,p_attribute18 in varchar2 default null
59 ,p_attribute19 in varchar2 default null
60 ,p_attribute20 in varchar2 default null
61 ,p_party_id in number default null
62 ,p_qua_information_category in varchar2 default null
63 ,p_qua_information1 in varchar2 default null
64 ,p_qua_information2 in varchar2 default null
65 ,p_qua_information3 in varchar2 default null
66 ,p_qua_information4 in varchar2 default null
67 ,p_qua_information5 in varchar2 default null
68 ,p_qua_information6 in varchar2 default null
69 ,p_qua_information7 in varchar2 default null
70 ,p_qua_information8 in varchar2 default null
71 ,p_qua_information9 in varchar2 default null
72 ,p_qua_information10 in varchar2 default null
73 ,p_qua_information11 in varchar2 default null
74 ,p_qua_information12 in varchar2 default null
75 ,p_qua_information13 in varchar2 default null
76 ,p_qua_information14 in varchar2 default null
77 ,p_qua_information15 in varchar2 default null
78 ,p_qua_information16 in varchar2 default null
79 ,p_qua_information17 in varchar2 default null
80 ,p_qua_information18 in varchar2 default null
81 ,p_qua_information19 in varchar2 default null
82 ,p_qua_information20 in varchar2 default null
83 ,p_professional_body_name in varchar2 default null
84 ,p_membership_number in varchar2 default null
85 ,p_membership_category in varchar2 default null
86 ,p_subscription_payment_method in varchar2 default null
87 ,p_qualification_id out nocopy number
88 ,p_object_version_number out nocopy number
89 ) is
90 --
91 -- Declare cursors and local variables
92 --
93 l_proc varchar2(72) := g_package||'create_qualification';
94 l_object_version_number number(9);
95 l_qualification_id number(9);
96 l_effective_date date;
97 l_awarded_date date;
98 l_start_date date;
99 l_end_date date;
100 l_expiry_date date;
101 l_projected_completion_date date;
102 l_language_code varchar2(30);
103
104 begin
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Issue a savepoint
108 --
109 savepoint CREATE_QUALIFICATION;
110 --
111 -- Truncate the time portion from all IN date parameters
112 --
113 l_effective_date := TRUNC(p_effective_date);
114 l_awarded_date := TRUNC(p_awarded_date);
115 l_start_date := TRUNC(p_start_date);
116 l_end_date := TRUNC(p_end_date);
117 l_expiry_date := TRUNC(p_expiry_date);
118 l_projected_completion_date := TRUNC(p_projected_completion_date);
119
120 -- Validate the language parameter. l_language_code should be passed
121 -- instead of p_language_code from now on, to allow an IN OUT parameter to
122 -- be passed through.
123 --
124 l_language_code := p_language_code;
125 hr_api.validate_language_code(p_language_code => l_language_code);
126
127 --
128 -- Call Before Process User Hook
129 --
130 begin
131 PER_QUALIFICATIONS_BK1.CREATE_QUALIFICATION_B
132 (p_effective_date => l_effective_date
133 ,p_business_group_id => p_business_group_id
134 ,p_qualification_type_id => p_qualification_type_id
135 ,p_person_id => p_person_id
136 ,p_party_id => p_party_id
137 ,p_title => p_title
138 ,p_grade_attained => p_grade_attained
139 ,p_status => p_status
140 ,p_awarded_date => l_awarded_date
141 ,p_fee => p_fee
142 ,p_fee_currency => p_fee_currency
143 ,p_training_completed_amount => p_training_completed_amount
144 ,p_reimbursement_arrangements => p_reimbursement_arrangements
145 ,p_training_completed_units => p_training_completed_units
146 ,p_total_training_amount => p_total_training_amount
147 ,p_start_date => l_start_date
148 ,p_end_date => l_end_date
149 ,p_license_number => p_license_number
150 ,p_expiry_date => l_expiry_date
151 ,p_license_restrictions => p_license_restrictions
152 ,p_projected_completion_date => l_projected_completion_date
153 ,p_awarding_body => p_awarding_body
154 ,p_tuition_method => p_tuition_method
155 ,p_group_ranking => p_group_ranking
156 ,p_comments => p_comments
157 ,p_attendance_id => p_attendance_id
158 ,p_attribute_category => p_attribute_category
159 ,p_attribute1 => p_attribute1
160 ,p_attribute2 => p_attribute2
161 ,p_attribute3 => p_attribute3
162 ,p_attribute4 => p_attribute4
163 ,p_attribute5 => p_attribute5
164 ,p_attribute6 => p_attribute6
165 ,p_attribute7 => p_attribute7
166 ,p_attribute8 => p_attribute8
167 ,p_attribute9 => p_attribute9
168 ,p_attribute10 => p_attribute10
169 ,p_attribute11 => p_attribute11
170 ,p_attribute12 => p_attribute12
171 ,p_attribute13 => p_attribute13
172 ,p_attribute14 => p_attribute14
173 ,p_attribute15 => p_attribute15
174 ,p_attribute16 => p_attribute16
175 ,p_attribute17 => p_attribute17
176 ,p_attribute18 => p_attribute18
177 ,p_attribute19 => p_attribute19
178 ,p_attribute20 => p_attribute20
179 ,p_qua_information_category => p_qua_information_category
180 ,p_qua_information1 => p_qua_information1
181 ,p_qua_information2 => p_qua_information2
182 ,p_qua_information3 => p_qua_information3
183 ,p_qua_information4 => p_qua_information4
184 ,p_qua_information5 => p_qua_information5
185 ,p_qua_information6 => p_qua_information6
186 ,p_qua_information7 => p_qua_information7
187 ,p_qua_information8 => p_qua_information8
188 ,p_qua_information9 => p_qua_information9
189 ,p_qua_information10 => p_qua_information10
190 ,p_qua_information11 => p_qua_information11
191 ,p_qua_information12 => p_qua_information12
192 ,p_qua_information13 => p_qua_information13
193 ,p_qua_information14 => p_qua_information14
194 ,p_qua_information15 => p_qua_information15
195 ,p_qua_information16 => p_qua_information16
196 ,p_qua_information17 => p_qua_information17
197 ,p_qua_information18 => p_qua_information18
198 ,p_qua_information19 => p_qua_information19
199 ,p_qua_information20 => p_qua_information20
200 ,p_professional_body_name => p_professional_body_name
201 ,p_membership_number => p_membership_number
202 ,p_membership_category => p_membership_category
203 ,p_subscription_payment_method => p_subscription_payment_method
204 ,p_language_code => l_language_code);
205 exception
206 when hr_Api.cannot_find_prog_unit then
207 hr_Api.cannot_find_prog_unit_error
208 (p_module_name => 'CREATE_QUALIFICATION'
209 ,p_hook_type => 'BP'
210 );
211 end;
212
213 --
214 -- Process Logic
215 --
216 per_qua_ins.ins
217 (p_validate => false
218 ,p_effective_date => l_effective_date
219 ,p_business_group_id => p_business_group_id
220 ,p_person_id => p_person_id
221 ,p_party_id => p_party_id
222 ,p_title => p_title
223 ,p_grade_attained => p_grade_attained
224 ,p_status => p_status
225 ,p_awarded_date => l_awarded_date
226 ,p_fee => p_fee
227 ,p_fee_currency => p_fee_currency
228 ,p_training_completed_amount => p_training_completed_amount
229 ,p_reimbursement_arrangements => p_reimbursement_arrangements
230 ,p_training_completed_units => p_training_completed_units
231 ,p_total_training_amount => p_total_training_amount
232 ,p_start_date => l_start_date
233 ,p_end_date => l_end_date
234 ,p_license_number => p_license_number
235 ,p_expiry_date => l_expiry_date
236 ,p_license_restrictions => p_license_restrictions
237 ,p_projected_completion_date => l_projected_completion_date
238 ,p_awarding_body => p_awarding_body
239 ,p_tuition_method => p_tuition_method
240 ,p_group_ranking => p_group_ranking
241 ,p_comments => p_comments
242 ,p_qualification_type_id => p_qualification_type_id
243 ,p_attendance_id => p_attendance_id
244 ,p_attribute_category => p_attribute_category
245 ,p_attribute1 => p_attribute1
246 ,p_attribute2 => p_attribute2
247 ,p_attribute3 => p_attribute3
248 ,p_attribute4 => p_attribute4
249 ,p_attribute5 => p_attribute5
250 ,p_attribute6 => p_attribute6
251 ,p_attribute7 => p_attribute7
252 ,p_attribute8 => p_attribute8
253 ,p_attribute9 => p_attribute9
254 ,p_attribute10 => p_attribute10
255 ,p_attribute11 => p_attribute11
256 ,p_attribute12 => p_attribute12
257 ,p_attribute13 => p_attribute13
258 ,p_attribute14 => p_attribute14
259 ,p_attribute15 => p_attribute15
260 ,p_attribute16 => p_attribute16
261 ,p_attribute17 => p_attribute17
262 ,p_attribute18 => p_attribute18
263 ,p_attribute19 => p_attribute19
264 ,p_attribute20 => p_attribute20
265 ,p_qua_information_category => p_qua_information_category
266 ,p_qua_information1 => p_qua_information1
267 ,p_qua_information2 => p_qua_information2
268 ,p_qua_information3 => p_qua_information3
269 ,p_qua_information4 => p_qua_information4
270 ,p_qua_information5 => p_qua_information5
271 ,p_qua_information6 => p_qua_information6
272 ,p_qua_information7 => p_qua_information7
273 ,p_qua_information8 => p_qua_information8
274 ,p_qua_information9 => p_qua_information9
275 ,p_qua_information10 => p_qua_information10
276 ,p_qua_information11 => p_qua_information11
277 ,p_qua_information12 => p_qua_information12
278 ,p_qua_information13 => p_qua_information13
279 ,p_qua_information14 => p_qua_information14
280 ,p_qua_information15 => p_qua_information15
281 ,p_qua_information16 => p_qua_information16
282 ,p_qua_information17 => p_qua_information17
283 ,p_qua_information18 => p_qua_information18
284 ,p_qua_information19 => p_qua_information19
285 ,p_qua_information20 => p_qua_information20
286 ,p_professional_body_name => p_professional_body_name
287 ,p_membership_number => p_membership_number
288 ,p_membership_category => p_membership_category
289 ,p_subscription_payment_method => p_subscription_payment_method
290 ,p_qualification_id => l_qualification_id
291 ,p_object_version_number => l_object_version_number
292 );
293
294 --
295 -- MLS Processing
296 --
297 per_qat_ins.ins_tl
298 (p_language_code => l_language_code
299 ,p_qualification_id => l_qualification_id
300 ,p_title => p_title
301 ,p_group_ranking => p_group_ranking
302 ,p_license_restrictions => p_license_restrictions
303 ,p_awarding_body => p_awarding_body
304 ,p_grade_attained => p_grade_attained
305 ,p_reimbursement_arrangements => p_reimbursement_arrangements
306 ,p_training_completed_units => p_training_completed_units
307 ,p_membership_category => p_membership_category
308 );
309
310 --
311 -- Call After Process User Hook
312 --
313 begin
314 PER_QUALIFICATIONS_BK1.CREATE_QUALIFICATION_A
315 (p_qualification_id => l_qualification_id
316 ,p_effective_date => l_effective_date
317 ,p_business_group_id => p_business_group_id
318 ,p_qualification_type_id => p_qualification_type_id
319 ,p_person_id => p_person_id
320 ,p_party_id => p_party_id
321 ,p_title => p_title
322 ,p_grade_attained => p_grade_attained
323 ,p_status => p_status
324 ,p_awarded_date => l_awarded_date
325 ,p_fee => p_fee
326 ,p_fee_currency => p_fee_currency
327 ,p_training_completed_amount => p_training_completed_amount
328 ,p_reimbursement_arrangements => p_reimbursement_arrangements
329 ,p_training_completed_units => p_training_completed_units
330 ,p_total_training_amount => p_total_training_amount
331 ,p_start_date => l_start_date
332 ,p_end_date => l_end_date
333 ,p_license_number => p_license_number
334 ,p_expiry_date => l_expiry_date
335 ,p_license_restrictions => p_license_restrictions
336 ,p_projected_completion_date => l_projected_completion_date
337 ,p_awarding_body => p_awarding_body
338 ,p_tuition_method => p_tuition_method
339 ,p_group_ranking => p_group_ranking
340 ,p_comments => p_comments
341 ,p_attendance_id => p_attendance_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
352 ,p_attribute10 => p_attribute10
349 ,p_attribute7 => p_attribute7
350 ,p_attribute8 => p_attribute8
351 ,p_attribute9 => p_attribute9
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 ,p_qua_information_category => p_qua_information_category
364 ,p_qua_information1 => p_qua_information1
365 ,p_qua_information2 => p_qua_information2
366 ,p_qua_information3 => p_qua_information3
367 ,p_qua_information4 => p_qua_information4
368 ,p_qua_information5 => p_qua_information5
369 ,p_qua_information6 => p_qua_information6
370 ,p_qua_information7 => p_qua_information7
371 ,p_qua_information8 => p_qua_information8
372 ,p_qua_information9 => p_qua_information9
373 ,p_qua_information10 => p_qua_information10
374 ,p_qua_information11 => p_qua_information11
375 ,p_qua_information12 => p_qua_information12
376 ,p_qua_information13 => p_qua_information13
377 ,p_qua_information14 => p_qua_information14
378 ,p_qua_information15 => p_qua_information15
379 ,p_qua_information16 => p_qua_information16
380 ,p_qua_information17 => p_qua_information17
381 ,p_qua_information18 => p_qua_information18
382 ,p_qua_information19 => p_qua_information19
383 ,p_qua_information20 => p_qua_information20
384 ,p_professional_body_name => p_professional_body_name
385 ,p_membership_number => p_membership_number
386 ,p_membership_category => p_membership_category
387 ,p_subscription_payment_method => p_subscription_payment_method
388 ,p_language_code => l_language_code);
389 exception
390 when hr_Api.cannot_find_prog_unit then
391 hr_Api.cannot_find_prog_unit_error
392 (p_module_name => 'CREATE_QUALIFICATION'
393 ,p_hook_type => 'AP'
394 );
395 end;
396 --
397 -- When in validation only mode raise the Validate_Enabled exception
398 --
399 if p_validate then
400 raise hr_Api.validate_enabled;
401 end if;
402 --
403 -- Set all output arguments
404 --
405 p_qualification_id := l_qualification_id;
406 p_object_version_number := l_object_version_number;
407 --
408 hr_utility.set_location(' Leaving:'||l_proc, 70);
409 exception
410 when hr_Api.validate_enabled then
411 --
412 -- As the Validate_Enabled exception has been raised
413 -- we must rollback to the savepoint
414 --
415 rollback to CREATE_QUALIFICATION;
416 --
417 -- Only set output warning arguments
418 -- (Any key or derived arguments must be set to null
419 -- when validation only mode is being used.)
420 --
421 p_qualification_id := null;
422 p_object_version_number := null;
423 hr_utility.set_location(' Leaving:'||l_proc, 80);
424 when others then
425 --
426 -- A validation or unexpected error has occured
427 --
428 rollback to CREATE_QUALIFICATION;
429 --
430 p_qualification_id := null;
431 p_object_version_number := null;
432 --
433 hr_utility.set_location(' Leaving:'||l_proc, 90);
434 raise;
435 end CREATE_QUALIFICATION;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |--------------------------<UPDATE_QUALIFICATION>--------------------------|
439 -- ----------------------------------------------------------------------------
440 --
441 procedure UPDATE_QUALIFICATION
442 (p_validate in boolean default false
443 ,p_effective_date in date
444 ,p_qualification_id in number
445 ,p_language_code in varchar2 default hr_api.userenv_lang
446 ,p_qualification_type_id in number default hr_api.g_number
447 ,p_title in varchar2 default hr_api.g_varchar2
448 ,p_grade_attained in varchar2 default hr_api.g_varchar2
449 ,p_status in varchar2 default hr_api.g_varchar2
450 ,p_awarded_date in date default hr_api.g_date
451 ,p_fee in number default hr_api.g_number
452 ,p_fee_currency in varchar2 default hr_api.g_varchar2
453 ,p_training_completed_amount in number default hr_api.g_number
454 ,p_reimbursement_arrangements in varchar2 default hr_api.g_varchar2
455 ,p_training_completed_units in varchar2 default hr_api.g_varchar2
456 ,p_total_training_amount in number default hr_api.g_number
457 ,p_start_date in date default hr_api.g_date
458 ,p_end_date in date default hr_api.g_date
459 ,p_license_number in varchar2 default hr_api.g_varchar2
460 ,p_expiry_date in date default hr_api.g_date
461 ,p_license_restrictions in varchar2 default hr_api.g_varchar2
462 ,p_projected_completion_date in date default hr_api.g_date
466 ,p_comments in varchar2 default hr_api.g_varchar2
463 ,p_awarding_body in varchar2 default hr_api.g_varchar2
464 ,p_tuition_method in varchar2 default hr_api.g_varchar2
465 ,p_group_ranking in varchar2 default hr_api.g_varchar2
467 ,p_attendance_id in number default hr_api.g_number
468 ,p_attribute_category in varchar2 default hr_api.g_varchar2
469 ,p_attribute1 in varchar2 default hr_api.g_varchar2
470 ,p_attribute2 in varchar2 default hr_api.g_varchar2
471 ,p_attribute3 in varchar2 default hr_api.g_varchar2
472 ,p_attribute4 in varchar2 default hr_api.g_varchar2
473 ,p_attribute5 in varchar2 default hr_api.g_varchar2
474 ,p_attribute6 in varchar2 default hr_api.g_varchar2
475 ,p_attribute7 in varchar2 default hr_api.g_varchar2
476 ,p_attribute8 in varchar2 default hr_api.g_varchar2
477 ,p_attribute9 in varchar2 default hr_api.g_varchar2
478 ,p_attribute10 in varchar2 default hr_api.g_varchar2
479 ,p_attribute11 in varchar2 default hr_api.g_varchar2
480 ,p_attribute12 in varchar2 default hr_api.g_varchar2
481 ,p_attribute13 in varchar2 default hr_api.g_varchar2
482 ,p_attribute14 in varchar2 default hr_api.g_varchar2
483 ,p_attribute15 in varchar2 default hr_api.g_varchar2
484 ,p_attribute16 in varchar2 default hr_api.g_varchar2
485 ,p_attribute17 in varchar2 default hr_api.g_varchar2
486 ,p_attribute18 in varchar2 default hr_api.g_varchar2
487 ,p_attribute19 in varchar2 default hr_api.g_varchar2
488 ,p_attribute20 in varchar2 default hr_api.g_varchar2
489 ,p_qua_information_category in varchar2 default hr_api.g_varchar2
490 ,p_qua_information1 in varchar2 default hr_api.g_varchar2
491 ,p_qua_information2 in varchar2 default hr_api.g_varchar2
492 ,p_qua_information3 in varchar2 default hr_api.g_varchar2
493 ,p_qua_information4 in varchar2 default hr_api.g_varchar2
494 ,p_qua_information5 in varchar2 default hr_api.g_varchar2
495 ,p_qua_information6 in varchar2 default hr_api.g_varchar2
496 ,p_qua_information7 in varchar2 default hr_api.g_varchar2
497 ,p_qua_information8 in varchar2 default hr_api.g_varchar2
498 ,p_qua_information9 in varchar2 default hr_api.g_varchar2
499 ,p_qua_information10 in varchar2 default hr_api.g_varchar2
500 ,p_qua_information11 in varchar2 default hr_api.g_varchar2
501 ,p_qua_information12 in varchar2 default hr_api.g_varchar2
502 ,p_qua_information13 in varchar2 default hr_api.g_varchar2
503 ,p_qua_information14 in varchar2 default hr_api.g_varchar2
504 ,p_qua_information15 in varchar2 default hr_api.g_varchar2
505 ,p_qua_information16 in varchar2 default hr_api.g_varchar2
506 ,p_qua_information17 in varchar2 default hr_api.g_varchar2
507 ,p_qua_information18 in varchar2 default hr_api.g_varchar2
508 ,p_qua_information19 in varchar2 default hr_api.g_varchar2
509 ,p_qua_information20 in varchar2 default hr_api.g_varchar2
510 ,p_professional_body_name in varchar2 default hr_api.g_varchar2
511 ,p_membership_number in varchar2 default hr_api.g_varchar2
512 ,p_membership_category in varchar2 default hr_api.g_varchar2
513 ,p_subscription_payment_method in varchar2 default hr_api.g_varchar2
514 ,p_object_version_number in out nocopy number
515 ) is
516 --
517 -- Declare cursors and local variables
518 --
519 l_proc varchar2(72) := g_package||'update_qualification';
520 l_effective_date date;
521 l_awarded_date date;
522 l_start_date date;
523 l_end_date date;
524 l_expiry_date date;
525 l_projected_completion_date date;
526 l_object_version_number number;
527 l_language_code varchar2(30);
528 l_temp_ovn number;
529
530 begin
531 hr_utility.set_location('Entering:'|| l_proc, 10);
532 --
533 -- Issue a savepoint
534 --
535 savepoint UPDATE_QUALIFICATION;
536 --
537 -- Truncate the time portion from all IN date parameters
538 --
539 l_effective_date := TRUNC(p_effective_date);
540 l_awarded_date := TRUNC(p_awarded_date);
541 l_start_date := TRUNC(p_start_date);
542 l_end_date := TRUNC(p_end_date);
543 l_expiry_date := TRUNC(p_expiry_date);
544 l_projected_completion_date := TRUNC(p_projected_completion_date);
545 l_object_version_number := p_object_version_number;
546 l_temp_ovn := p_object_version_number;
547
548 -- Validate the language parameter. l_language_code should be passed
549 -- instead of p_language_code from now on, to allow an IN OUT parameter to
550 -- be passed through.
551 --
552 l_language_code := p_language_code;
553 hr_api.validate_language_code(p_language_code => l_language_code);
554
555 --
556 -- Call Before Process User Hook
557 --
561 ,p_qualification_type_id => p_qualification_type_id
558 begin
559 PER_QUALIFICATIONS_BK2.UPDATE_QUALIFICATION_B
560 (p_effective_date => l_effective_date
562 ,p_qualification_id => p_qualification_id
563 ,p_title => p_title
564 ,p_grade_attained => p_grade_attained
565 ,p_status => p_status
566 ,p_awarded_date => l_awarded_date
567 ,p_fee => p_fee
568 ,p_fee_currency => p_fee_currency
569 ,p_training_completed_amount => p_training_completed_amount
570 ,p_reimbursement_arrangements => p_reimbursement_arrangements
571 ,p_training_completed_units => p_training_completed_units
572 ,p_total_training_amount => p_total_training_amount
573 ,p_start_date => l_start_date
574 ,p_end_date => l_end_date
575 ,p_license_number => p_license_number
576 ,p_expiry_date => l_expiry_date
577 ,p_license_restrictions => p_license_restrictions
578 ,p_projected_completion_date => l_projected_completion_date
579 ,p_awarding_body => p_awarding_body
580 ,p_tuition_method => p_tuition_method
581 ,p_group_ranking => p_group_ranking
582 ,p_comments => p_comments
583 ,p_attendance_id => p_attendance_id
584 ,p_attribute_category => p_attribute_category
585 ,p_attribute1 => p_attribute1
586 ,p_attribute2 => p_attribute2
587 ,p_attribute3 => p_attribute3
588 ,p_attribute4 => p_attribute4
589 ,p_attribute5 => p_attribute5
590 ,p_attribute6 => p_attribute6
591 ,p_attribute7 => p_attribute7
592 ,p_attribute8 => p_attribute8
593 ,p_attribute9 => p_attribute9
594 ,p_attribute10 => p_attribute10
595 ,p_attribute11 => p_attribute11
596 ,p_attribute12 => p_attribute12
597 ,p_attribute13 => p_attribute13
598 ,p_attribute14 => p_attribute14
599 ,p_attribute15 => p_attribute15
600 ,p_attribute16 => p_attribute16
601 ,p_attribute17 => p_attribute17
602 ,p_attribute18 => p_attribute18
603 ,p_attribute19 => p_attribute19
604 ,p_attribute20 => p_attribute20
605 ,p_qua_information_category => p_qua_information_category
606 ,p_qua_information1 => p_qua_information1
607 ,p_qua_information2 => p_qua_information2
608 ,p_qua_information3 => p_qua_information3
609 ,p_qua_information4 => p_qua_information4
610 ,p_qua_information5 => p_qua_information5
611 ,p_qua_information6 => p_qua_information6
612 ,p_qua_information7 => p_qua_information7
613 ,p_qua_information8 => p_qua_information8
614 ,p_qua_information9 => p_qua_information9
615 ,p_qua_information10 => p_qua_information10
616 ,p_qua_information11 => p_qua_information11
617 ,p_qua_information12 => p_qua_information12
618 ,p_qua_information13 => p_qua_information13
619 ,p_qua_information14 => p_qua_information14
620 ,p_qua_information15 => p_qua_information15
621 ,p_qua_information16 => p_qua_information16
622 ,p_qua_information17 => p_qua_information17
623 ,p_qua_information18 => p_qua_information18
624 ,p_qua_information19 => p_qua_information19
625 ,p_qua_information20 => p_qua_information20
626 ,p_professional_body_name => p_professional_body_name
627 ,p_membership_number => p_membership_number
628 ,p_membership_category => p_membership_category
629 ,p_subscription_payment_method => p_subscription_payment_method
630 ,p_object_version_number => l_object_version_number
631 ,p_language_code => l_language_code);
632 exception
633 when hr_Api.cannot_find_prog_unit then
634 hr_Api.cannot_find_prog_unit_error
635 (p_module_name => 'UPDATE_QUALIFICATION'
636 ,p_hook_type => 'BP'
637 );
638 end;
639
640 --
641 -- Process Logic
642 --
643
644
645 per_qua_upd.upd
646 (p_validate => false
647 ,p_effective_date => l_effective_date
648 ,p_qualification_id => p_qualification_id
649 ,p_object_version_number => l_object_version_number
650 ,p_title => p_title
651 ,p_grade_attained => p_grade_attained
652 ,p_status => p_status
653 ,p_awarded_date => l_awarded_date
654 ,p_fee => p_fee
655 ,p_fee_currency => p_fee_currency
656 ,p_training_completed_amount => p_training_completed_amount
657 ,p_reimbursement_arrangements => p_reimbursement_arrangements
658 ,p_training_completed_units => p_training_completed_units
659 ,p_total_training_amount => p_total_training_amount
660 ,p_start_date => l_start_date
661 ,p_end_date => l_end_date
662 ,p_license_number => p_license_number
663 ,p_expiry_date => l_expiry_date
664 ,p_license_restrictions => p_license_restrictions
665 ,p_projected_completion_date => l_projected_completion_date
669 ,p_comments => p_comments
666 ,p_awarding_body => p_awarding_body
667 ,p_tuition_method => p_tuition_method
668 ,p_group_ranking => p_group_ranking
670 ,p_qualification_type_id => p_qualification_type_id
671 ,p_attendance_id => p_attendance_id
672 ,p_attribute_category => p_attribute_category
673 ,p_attribute1 => p_attribute1
674 ,p_attribute2 => p_attribute2
675 ,p_attribute3 => p_attribute3
676 ,p_attribute4 => p_attribute4
677 ,p_attribute5 => p_attribute5
678 ,p_attribute6 => p_attribute6
679 ,p_attribute7 => p_attribute7
680 ,p_attribute8 => p_attribute8
681 ,p_attribute9 => p_attribute9
682 ,p_attribute10 => p_attribute10
683 ,p_attribute11 => p_attribute11
684 ,p_attribute12 => p_attribute12
685 ,p_attribute13 => p_attribute13
686 ,p_attribute14 => p_attribute14
687 ,p_attribute15 => p_attribute15
688 ,p_attribute16 => p_attribute16
689 ,p_attribute17 => p_attribute17
690 ,p_attribute18 => p_attribute18
691 ,p_attribute19 => p_attribute19
692 ,p_attribute20 => p_attribute20
693 ,p_qua_information_category => p_qua_information_category
694 ,p_qua_information1 => p_qua_information1
695 ,p_qua_information2 => p_qua_information2
696 ,p_qua_information3 => p_qua_information3
697 ,p_qua_information4 => p_qua_information4
698 ,p_qua_information5 => p_qua_information5
699 ,p_qua_information6 => p_qua_information6
700 ,p_qua_information7 => p_qua_information7
701 ,p_qua_information8 => p_qua_information8
702 ,p_qua_information9 => p_qua_information9
703 ,p_qua_information10 => p_qua_information10
704 ,p_qua_information11 => p_qua_information11
705 ,p_qua_information12 => p_qua_information12
706 ,p_qua_information13 => p_qua_information13
707 ,p_qua_information14 => p_qua_information14
708 ,p_qua_information15 => p_qua_information15
709 ,p_qua_information16 => p_qua_information16
710 ,p_qua_information17 => p_qua_information17
711 ,p_qua_information18 => p_qua_information18
712 ,p_qua_information19 => p_qua_information19
713 ,p_qua_information20 => p_qua_information20
714 ,p_professional_body_name => p_professional_body_name
715 ,p_membership_number => p_membership_number
716 ,p_membership_category => p_membership_category
717 ,p_subscription_payment_method => p_subscription_payment_method
718 );
719
720 --
721 -- MLS Processing
722 --
723 per_qat_upd.upd_tl
724 (p_language_code => l_language_code
725 ,p_qualification_id => p_qualification_id
726 ,p_title => p_title
727 ,p_group_ranking => p_group_ranking
728 ,p_license_restrictions => p_license_restrictions
729 ,p_awarding_body => p_awarding_body
730 ,p_grade_attained => p_grade_attained
731 ,p_reimbursement_arrangements => p_reimbursement_arrangements
732 ,p_training_completed_units => p_training_completed_units
733 ,p_membership_category => p_membership_category
734 );
735
736 --
737 -- Call After Process User Hook
738 --
739 begin
740 PER_QUALIFICATIONS_BK2.UPDATE_QUALIFICATION_A
741 (p_effective_date => l_effective_date
742 ,p_qualification_type_id => p_qualification_type_id
743 ,p_qualification_id => p_qualification_id
744 ,p_title => p_title
745 ,p_grade_attained => p_grade_attained
746 ,p_status => p_status
747 ,p_awarded_date => l_awarded_date
748 ,p_fee => p_fee
749 ,p_fee_currency => p_fee_currency
750 ,p_training_completed_amount => p_training_completed_amount
751 ,p_reimbursement_arrangements => p_reimbursement_arrangements
752 ,p_training_completed_units => p_training_completed_units
753 ,p_total_training_amount => p_total_training_amount
754 ,p_start_date => l_start_date
755 ,p_end_date => l_end_date
756 ,p_license_number => p_license_number
757 ,p_expiry_date => l_expiry_date
758 ,p_license_restrictions => p_license_restrictions
759 ,p_projected_completion_date => l_projected_completion_date
760 ,p_awarding_body => p_awarding_body
761 ,p_tuition_method => p_tuition_method
762 ,p_group_ranking => p_group_ranking
763 ,p_comments => p_comments
764 ,p_attendance_id => p_attendance_id
765 ,p_attribute_category => p_attribute_category
766 ,p_attribute1 => p_attribute1
767 ,p_attribute2 => p_attribute2
768 ,p_attribute3 => p_attribute3
769 ,p_attribute4 => p_attribute4
770 ,p_attribute5 => p_attribute5
774 ,p_attribute9 => p_attribute9
771 ,p_attribute6 => p_attribute6
772 ,p_attribute7 => p_attribute7
773 ,p_attribute8 => p_attribute8
775 ,p_attribute10 => p_attribute10
776 ,p_attribute11 => p_attribute11
777 ,p_attribute12 => p_attribute12
778 ,p_attribute13 => p_attribute13
779 ,p_attribute14 => p_attribute14
780 ,p_attribute15 => p_attribute15
781 ,p_attribute16 => p_attribute16
782 ,p_attribute17 => p_attribute17
783 ,p_attribute18 => p_attribute18
784 ,p_attribute19 => p_attribute19
785 ,p_attribute20 => p_attribute20
786 ,p_qua_information_category => p_qua_information_category
787 ,p_qua_information1 => p_qua_information1
788 ,p_qua_information2 => p_qua_information2
789 ,p_qua_information3 => p_qua_information3
790 ,p_qua_information4 => p_qua_information4
791 ,p_qua_information5 => p_qua_information5
792 ,p_qua_information6 => p_qua_information6
793 ,p_qua_information7 => p_qua_information7
794 ,p_qua_information8 => p_qua_information8
795 ,p_qua_information9 => p_qua_information9
796 ,p_qua_information10 => p_qua_information10
797 ,p_qua_information11 => p_qua_information11
798 ,p_qua_information12 => p_qua_information12
799 ,p_qua_information13 => p_qua_information13
800 ,p_qua_information14 => p_qua_information14
801 ,p_qua_information15 => p_qua_information15
802 ,p_qua_information16 => p_qua_information16
803 ,p_qua_information17 => p_qua_information17
804 ,p_qua_information18 => p_qua_information18
805 ,p_qua_information19 => p_qua_information19
806 ,p_qua_information20 => p_qua_information20
807 ,p_professional_body_name => p_professional_body_name
808 ,p_membership_number => p_membership_number
809 ,p_membership_category => p_membership_category
810 ,p_subscription_payment_method => p_subscription_payment_method
811 ,p_object_version_number => l_object_version_number
812 ,p_language_code => l_language_code);
813 exception
814 when hr_Api.cannot_find_prog_unit then
815 hr_Api.cannot_find_prog_unit_error
816 (p_module_name => 'UPDATE_QUALIFICATION'
817 ,p_hook_type => 'AP'
818 );
819 end;
820 --
821 -- When in validation only mode raise the Validate_Enabled exception
822 --
823 if p_validate then
824 raise hr_Api.validate_enabled;
825 end if;
826 --
827 -- Set all output arguements
828 --
829 p_object_version_number := l_object_version_number;
830 hr_utility.set_location(' Leaving:'||l_proc, 70);
831 exception
832 when hr_Api.validate_enabled then
833 --
834 -- As the Validate_Enabled exception has been raised
835 -- we must rollback to the savepoint
836 --
837 rollback to UPDATE_QUALIFICATION;
838 --
839 -- Only set output warning arguments
840 -- (Any key or derived arguments must be set to null
841 -- when validation only mode is being used.)
842 --
843 hr_utility.set_location(' Leaving:'||l_proc, 80);
844 when others then
845 --
846 -- A validation or unexpected error has occured
847 --
848 p_object_version_number := l_temp_ovn;
849 rollback to UPDATE_QUALIFICATION;
850 hr_utility.set_location(' Leaving:'||l_proc, 90);
851 raise;
852 end UPDATE_QUALIFICATION;
853
854 --
855 -- ----------------------------------------------------------------------------
856 -- |--------------------------<DELETE_QUALIFICATION>--------------------------|
857 -- ----------------------------------------------------------------------------
858 --
859 procedure DELETE_QUALIFICATION
860 (p_validate in boolean default false
861 ,p_qualification_id in number
862 ,p_object_version_number in number
863 ) is
864
865 CURSOR get_person_info IS
866 select person_id
867 from PER_QUALIFICATIONS
868 where QUALIFICATION_ID = p_qualification_id;
869 --
870 -- Declare cursors and local variables
871 --
872 l_proc varchar2(72) := g_package||'delete_qualification';
873 l_object_version_number number(9) := p_object_version_number;
874
875 l_person_id number := -1;
876
877 begin
878 hr_utility.set_location('Entering:'|| l_proc, 10);
879
880 OPEN get_person_info;
881 FETCH get_person_info INTO l_person_id;
882 CLOSE get_person_info;
883 --
884 -- Issue a savepoint
885 --
886 savepoint DELETE_QUALIFICATION;
887 --
888 -- Call Before Process User Hook
889 --
890 begin
891 PER_QUALIFICATIONS_BK3.DELETE_QUALIFICATION_B
892 (p_qualification_id => p_qualification_id
893 ,p_object_version_number => p_object_version_number
894 );
895 exception
896 when hr_Api.cannot_find_prog_unit then
900 );
897 hr_Api.cannot_find_prog_unit_error
898 (p_module_name => 'DELETE_QUALIFICATION'
899 ,p_hook_type => 'BP'
901 end;
902
903 --
904 -- MLS Processing
905 --
906 per_qat_del.del_tl
907 (p_qualification_id => p_qualification_id
908 );
909
910 --
911 -- Process Logic
912 --
913 per_qua_del.del
914 (p_validate => false
915 ,p_qualification_id => p_qualification_id
916 ,p_object_version_number => p_object_version_number
917 );
918
919 --
920 -- Call After Process User Hook
921 --
922 begin
923 PER_QUALIFICATIONS_BK3.DELETE_QUALIFICATION_A
927 );
924 (p_qualification_id => p_qualification_id
925 ,p_object_version_number => p_object_version_number
926 ,p_person_id => l_person_id
928 exception
929 when hr_Api.cannot_find_prog_unit then
930 hr_Api.cannot_find_prog_unit_error
931 (p_module_name => 'DELETE_QUALIFICATION'
932 ,p_hook_type => 'AP'
933 );
934 end;
935 --
936 -- When in validation only mode raise the Validate_Enabled exception
937 --
938 if p_validate then
939 raise hr_Api.validate_enabled;
940 end if;
941 --
942 hr_utility.set_location(' Leaving:'||l_proc, 70);
943 exception
944 when hr_Api.validate_enabled then
945 --
946 -- As the Validate_Enabled exception has been raised
947 -- we must rollback to the savepoint
948 --
949 rollback to DELETE_QUALIFICATION;
950 --
951 hr_utility.set_location(' Leaving:'||l_proc, 80);
952 when others then
953 --
954 -- A validation or unexpected error has occured
955 --
956 rollback to DELETE_QUALIFICATION;
957 hr_utility.set_location(' Leaving:'||l_proc, 90);
958 raise;
959 end DELETE_QUALIFICATION;
960
961 end PER_QUALIFICATIONS_API;