[Home] [Help]
PACKAGE BODY: APPS.PER_QUA_SHD
Source
1 Package Body per_qua_shd as
2 /* $Header: pequarhi.pkb 120.0.12010000.2 2008/08/06 09:31:13 ubhat ship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' per_qua_shd.'; -- Global package name
8 --
9 -- ----------------------------------------------------------------------------
10 -- |------------------------< return_api_dml_status >-------------------------|
11 -- ----------------------------------------------------------------------------
12 Function return_api_dml_status Return Boolean Is
13 --
14 l_proc varchar2(72) := g_package||'return_api_dml_status';
15 --
16 Begin
17 hr_utility.set_location('Entering:'||l_proc, 5);
18 --
19 Return (nvl(g_api_dml, false));
20 --
21 hr_utility.set_location(' Leaving:'||l_proc, 10);
22 End return_api_dml_status;
23 --
24 -- ----------------------------------------------------------------------------
25 -- |---------------------------< constraint_error >---------------------------|
26 -- ----------------------------------------------------------------------------
27 Procedure constraint_error
28 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
29 --
30 l_proc varchar2(72) := g_package||'constraint_error';
31 --
32 Begin
33 hr_utility.set_location('Entering:'||l_proc, 5);
34 --
35 If (p_constraint_name = 'PER_QUALIFICATIONS_FK1') Then
36 hr_utility.set_message(801, 'HR_51850_QUA_ATT_ID_FK');
37 hr_utility.raise_error;
38 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_FK2') Then
39 hr_utility.set_message(801, 'HR_51851_QUA_QUAL_TYPE_ID_FK');
40 hr_utility.raise_error;
41 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_FK3') Then
42 hr_utility.set_message(801, 'HR_51852_QUA_BUS_GRP_ID_FK');
43 hr_utility.raise_error;
44 ElsIf (p_constraint_name = 'PER_QUA_CHK_DATES') Then
45 hr_utility.set_message(801, 'HR_51853_QUA_DATE_INV');
46 hr_utility.raise_error;
47 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_PK') Then
48 hr_utility.set_message(801, 'HR_51854_QUA_QUAL_ID_PK');
49 hr_utility.raise_error;
50 Else
51 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
52 hr_utility.set_message_token('PROCEDURE', l_proc);
53 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
54 hr_utility.raise_error;
55 End If;
56 --
57 hr_utility.set_location(' Leaving:'||l_proc, 10);
58 End constraint_error;
59 --
60 -- ----------------------------------------------------------------------------
61 -- |-----------------------------< api_updating >-----------------------------|
62 -- ----------------------------------------------------------------------------
63 Function api_updating
64 (
65 p_qualification_id in number,
66 p_object_version_number in number
67 ) Return Boolean Is
68 --
69 --
70 -- Cursor selects the 'current' row from the HR Schema
71 --
72 Cursor C_Sel1 is
73 select
74 qualification_id,
75 business_group_id,
76 object_version_number,
77 person_id,
78 title,
79 grade_attained,
80 status,
81 awarded_date,
82 fee,
83 fee_currency,
84 training_completed_amount,
85 reimbursement_arrangements,
86 training_completed_units,
87 total_training_amount,
88 start_date,
89 end_date,
90 license_number,
91 expiry_date,
92 license_restrictions,
93 projected_completion_date,
94 awarding_body,
95 tuition_method,
96 group_ranking,
97 comments,
98 qualification_type_id,
99 attendance_id,
100 attribute_category,
101 attribute1,
102 attribute2,
103 attribute3,
104 attribute4,
105 attribute5,
106 attribute6,
107 attribute7,
108 attribute8,
109 attribute9,
110 attribute10,
111 attribute11,
112 attribute12,
113 attribute13,
114 attribute14,
115 attribute15,
116 attribute16,
117 attribute17,
118 attribute18,
119 attribute19,
120 attribute20,
121 qua_information_category,
122 qua_information1,
123 qua_information2,
124 qua_information3,
125 qua_information4,
126 qua_information5,
127 qua_information6,
128 qua_information7,
129 qua_information8,
130 qua_information9,
131 qua_information10,
132 qua_information11,
133 qua_information12,
134 qua_information13,
135 qua_information14,
136 qua_information15,
137 qua_information16,
138 qua_information17,
139 qua_information18,
140 qua_information19,
141 qua_information20,
142 professional_body_name,
143 membership_number,
144 membership_category,
145 subscription_payment_method,
146 party_id
147 from per_qualifications
148 where qualification_id = p_qualification_id;
149 --
150 l_proc varchar2(72) := g_package||'api_updating';
151 l_fct_ret boolean;
152 --
153 Begin
154 hr_utility.set_location('Entering:'||l_proc, 5);
155 --
156 If (
157 p_qualification_id is null and
158 p_object_version_number is null
159 ) Then
160 --
161 -- One of the primary key arguments is null therefore we must
162 -- set the returning function value to false
163 --
164 l_fct_ret := false;
165 Else
166 If (
167 p_qualification_id = g_old_rec.qualification_id and
168 p_object_version_number = g_old_rec.object_version_number
169 ) Then
170 hr_utility.set_location(l_proc, 10);
171 --
172 -- The g_old_rec is current therefore we must
173 -- set the returning function to true
174 --
175 l_fct_ret := true;
176 Else
177 --
178 -- Select the current row into g_old_rec
179 --
180 Open C_Sel1;
181 Fetch C_Sel1 Into g_old_rec;
182 If C_Sel1%notfound Then
183 Close C_Sel1;
184 --
185 -- The primary key is invalid therefore we must error
186 --
187 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
188 hr_utility.raise_error;
189 End If;
190 Close C_Sel1;
191 If (p_object_version_number <> g_old_rec.object_version_number) Then
192 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
193 hr_utility.raise_error;
194 End If;
195 hr_utility.set_location(l_proc, 15);
196 l_fct_ret := true;
197 End If;
198 End If;
199 hr_utility.set_location(' Leaving:'||l_proc, 20);
200 Return (l_fct_ret);
201 --
202 End api_updating;
203 --
204 -- ----------------------------------------------------------------------------
205 -- |---------------------------------< lck >----------------------------------|
206 -- ----------------------------------------------------------------------------
207 Procedure lck
208 (
209 p_qualification_id in number,
210 p_object_version_number in number
211 ) is
212 --
213 -- Cursor selects the 'current' row from the HR Schema
214 --
215 Cursor C_Sel1 is
216 select qualification_id,
217 business_group_id,
218 object_version_number,
219 person_id,
220 title,
221 grade_attained,
222 status,
223 awarded_date,
224 fee,
225 fee_currency,
226 training_completed_amount,
227 reimbursement_arrangements,
228 training_completed_units,
229 total_training_amount,
230 start_date,
231 end_date,
232 license_number,
233 expiry_date,
234 license_restrictions,
235 projected_completion_date,
236 awarding_body,
237 tuition_method,
238 group_ranking,
239 comments,
240 qualification_type_id,
241 attendance_id,
242 attribute_category,
243 attribute1,
244 attribute2,
245 attribute3,
246 attribute4,
247 attribute5,
248 attribute6,
249 attribute7,
250 attribute8,
251 attribute9,
252 attribute10,
253 attribute11,
254 attribute12,
255 attribute13,
256 attribute14,
257 attribute15,
258 attribute16,
259 attribute17,
260 attribute18,
261 attribute19,
262 attribute20,
263 qua_information_category,
264 qua_information1,
265 qua_information2,
266 qua_information3,
267 qua_information4,
268 qua_information5,
269 qua_information6,
270 qua_information7,
271 qua_information8,
272 qua_information9,
273 qua_information10,
274 qua_information11,
275 qua_information12,
276 qua_information13,
277 qua_information14,
278 qua_information15,
279 qua_information16,
280 qua_information17,
281 qua_information18,
282 qua_information19,
283 qua_information20,
284 professional_body_name,
285 membership_number,
286 membership_category,
287 subscription_payment_method,
288 party_id
289 from per_qualifications
290 where qualification_id = p_qualification_id
291 for update nowait;
292 --
293 l_proc varchar2(72) := g_package||'lck';
294 --
295 Begin
296 hr_utility.set_location('Entering:'||l_proc, 5);
297 --
298 -- Add any mandatory argument checking here:
299 -- Example:
300 -- hr_api.mandatory_arg_error
301 -- (p_api_name => l_proc,
302 -- p_argument => 'object_version_number',
303 -- p_argument_value => p_object_version_number);
304 --
305 Open C_Sel1;
306 Fetch C_Sel1 Into g_old_rec;
307 If C_Sel1%notfound then
308 Close C_Sel1;
309 --
310 -- The primary key is invalid therefore we must error
311 --
312 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
313 hr_utility.raise_error;
314 End If;
315 Close C_Sel1;
316 If (p_object_version_number <> g_old_rec.object_version_number) Then
317 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
318 hr_utility.raise_error;
319 End If;
320 --
321 hr_utility.set_location(' Leaving:'||l_proc, 10);
322 --
323 -- We need to trap the ORA LOCK exception
324 --
325 Exception
326 When HR_Api.Object_Locked then
327 --
328 -- The object is locked therefore we need to supply a meaningful
329 -- error message.
330 --
331 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
332 hr_utility.set_message_token('TABLE_NAME', 'per_qualifications');
333 hr_utility.raise_error;
334 End lck;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |-----------------------------< convert_args >-----------------------------|
338 -- ----------------------------------------------------------------------------
339 Function convert_args
340 (
341 p_qualification_id in number,
342 p_business_group_id in number,
343 p_object_version_number in number,
344 p_person_id in number,
345 p_title in varchar2,
346 p_grade_attained in varchar2,
347 p_status in varchar2,
348 p_awarded_date in date,
349 p_fee in number,
350 p_fee_currency in varchar2,
351 p_training_completed_amount in number,
352 p_reimbursement_arrangements in varchar2,
353 p_training_completed_units in varchar2,
354 p_total_training_amount in number,
355 p_start_date in date,
356 p_end_date in date,
357 p_license_number in varchar2,
358 p_expiry_date in date,
359 p_license_restrictions in varchar2,
360 p_projected_completion_date in date,
361 p_awarding_body in varchar2,
362 p_tuition_method in varchar2,
363 p_group_ranking in varchar2,
364 p_comments in varchar2,
365 p_qualification_type_id in number,
366 p_attendance_id in number,
367 p_attribute_category in varchar2,
368 p_attribute1 in varchar2,
369 p_attribute2 in varchar2,
370 p_attribute3 in varchar2,
371 p_attribute4 in varchar2,
372 p_attribute5 in varchar2,
373 p_attribute6 in varchar2,
374 p_attribute7 in varchar2,
375 p_attribute8 in varchar2,
376 p_attribute9 in varchar2,
377 p_attribute10 in varchar2,
378 p_attribute11 in varchar2,
379 p_attribute12 in varchar2,
380 p_attribute13 in varchar2,
381 p_attribute14 in varchar2,
382 p_attribute15 in varchar2,
383 p_attribute16 in varchar2,
384 p_attribute17 in varchar2,
385 p_attribute18 in varchar2,
386 p_attribute19 in varchar2,
387 p_attribute20 in varchar2,
388 p_qua_information_category in varchar2,
389 p_qua_information1 in varchar2,
390 p_qua_information2 in varchar2,
391 p_qua_information3 in varchar2,
392 p_qua_information4 in varchar2,
393 p_qua_information5 in varchar2,
394 p_qua_information6 in varchar2,
395 p_qua_information7 in varchar2,
396 p_qua_information8 in varchar2,
397 p_qua_information9 in varchar2,
398 p_qua_information10 in varchar2,
399 p_qua_information11 in varchar2,
400 p_qua_information12 in varchar2,
401 p_qua_information13 in varchar2,
402 p_qua_information14 in varchar2,
403 p_qua_information15 in varchar2,
404 p_qua_information16 in varchar2,
405 p_qua_information17 in varchar2,
406 p_qua_information18 in varchar2,
407 p_qua_information19 in varchar2,
408 p_qua_information20 in varchar2,
409 p_professional_body_name in varchar2,
410 p_membership_number in varchar2,
411 p_membership_category in varchar2,
412 p_subscription_payment_method in varchar2,
413 p_party_id in number default null
414 )
415 Return g_rec_type is
416 --
417 l_rec g_rec_type;
418 l_proc varchar2(72) := g_package||'convert_args';
419 --
420 Begin
421 --
422 hr_utility.set_location('Entering:'||l_proc, 5);
423 --
424 -- Convert arguments into local l_rec structure.
425 --
426 l_rec.qualification_id := p_qualification_id;
427 l_rec.business_group_id := p_business_group_id;
428 l_rec.object_version_number := p_object_version_number;
429 l_rec.person_id := p_person_id;
430 l_rec.title := p_title;
431 l_rec.grade_attained := p_grade_attained;
432 l_rec.status := p_status;
433 l_rec.awarded_date := p_awarded_date;
434 l_rec.fee := p_fee;
435 l_rec.fee_currency := p_fee_currency;
436 l_rec.training_completed_amount := p_training_completed_amount;
437 l_rec.reimbursement_arrangements := p_reimbursement_arrangements;
438 l_rec.training_completed_units := p_training_completed_units;
439 l_rec.total_training_amount := p_total_training_amount;
440 l_rec.start_date := p_start_date;
441 l_rec.end_date := p_end_date;
442 l_rec.license_number := p_license_number;
443 l_rec.expiry_date := p_expiry_date;
444 l_rec.license_restrictions := p_license_restrictions;
445 l_rec.projected_completion_date := p_projected_completion_date;
446 l_rec.awarding_body := p_awarding_body;
447 l_rec.tuition_method := p_tuition_method;
448 l_rec.group_ranking := p_group_ranking;
449 l_rec.comments := p_comments;
450 l_rec.qualification_type_id := p_qualification_type_id;
451 l_rec.attendance_id := p_attendance_id;
452 l_rec.attribute_category := p_attribute_category;
453 l_rec.attribute1 := p_attribute1;
454 l_rec.attribute2 := p_attribute2;
455 l_rec.attribute3 := p_attribute3;
456 l_rec.attribute4 := p_attribute4;
457 l_rec.attribute5 := p_attribute5;
458 l_rec.attribute6 := p_attribute6;
459 l_rec.attribute7 := p_attribute7;
460 l_rec.attribute8 := p_attribute8;
461 l_rec.attribute9 := p_attribute9;
462 l_rec.attribute10 := p_attribute10;
463 l_rec.attribute11 := p_attribute11;
464 l_rec.attribute12 := p_attribute12;
465 l_rec.attribute13 := p_attribute13;
466 l_rec.attribute14 := p_attribute14;
467 l_rec.attribute15 := p_attribute15;
468 l_rec.attribute16 := p_attribute16;
469 l_rec.attribute17 := p_attribute17;
470 l_rec.attribute18 := p_attribute18;
471 l_rec.attribute19 := p_attribute19;
472 l_rec.attribute20 := p_attribute20;
473 l_rec.qua_information_category := p_qua_information_category;
474 l_rec.qua_information1 := p_qua_information1;
475 l_rec.qua_information2 := p_qua_information2;
476 l_rec.qua_information3 := p_qua_information3;
477 l_rec.qua_information4 := p_qua_information4;
478 l_rec.qua_information5 := p_qua_information5;
479 l_rec.qua_information6 := p_qua_information6;
480 l_rec.qua_information7 := p_qua_information7;
481 l_rec.qua_information8 := p_qua_information8;
482 l_rec.qua_information9 := p_qua_information9;
483 l_rec.qua_information10 := p_qua_information10;
484 l_rec.qua_information11 := p_qua_information11;
485 l_rec.qua_information12 := p_qua_information12;
486 l_rec.qua_information13 := p_qua_information13;
487 l_rec.qua_information14 := p_qua_information14;
488 l_rec.qua_information15 := p_qua_information15;
489 l_rec.qua_information16 := p_qua_information16;
490 l_rec.qua_information17 := p_qua_information17;
491 l_rec.qua_information18 := p_qua_information18;
492 l_rec.qua_information19 := p_qua_information19;
493 l_rec.qua_information20 := p_qua_information20;
494 l_rec.professional_body_name := p_professional_body_name;
495 l_rec.membership_number := p_membership_number;
496 l_rec.membership_category := p_membership_category;
497 l_rec.subscription_payment_method := p_subscription_payment_method;
498 l_rec.party_id := p_party_id;
499 --
500 -- Return the plsql record structure.
501 --
502 hr_utility.set_location(' Leaving:'||l_proc, 10);
503 Return(l_rec);
504 --
505 End convert_args;
506 --
507 end per_qua_shd;