[Home] [Help]
PACKAGE BODY: APPS.PER_CPN_SHD
Source
1 Package Body per_cpn_shd as
2 /* $Header: pecpnrhi.pkb 120.0 2005/05/31 07:14:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cpn_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 hr_utility.set_location(p_constraint_name,9999);
21 --
22 If (p_constraint_name = 'PER_COMPETENCES_FK1') Then
23 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
24 hr_utility.set_message_token('PROCEDURE', l_proc);
25 hr_utility.set_message_token('STEP','5');
26 hr_utility.raise_error;
27 ElsIf (p_constraint_name = 'PER_COMPETENCES_FK2') Then
28 hr_utility.set_message(801, 'HR_51452_COMP_RAT_SC_NOT_EXIST');
29 hr_utility.raise_error;
30 ElsIf (p_constraint_name = 'PER_COMPETENCES_PK') Then
31 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
32 hr_utility.set_message_token('PROCEDURE', l_proc);
33 hr_utility.set_message_token('STEP','15');
34 hr_utility.raise_error;
35 --
36 -- pmfletch - Replaced with procedure chk_definition_id
37 --ElsIf (p_constraint_name = 'PER_COMPETENCES_UK2') Then
38 -- hr_utility.set_message(801, 'HR_51436_COMP_NOT_UNIQUE');
39 -- hr_utility.raise_error;
40 --
41 ElsIf (p_constraint_name = 'PER_CPN_CERT_REQ_CHK') Then
42 hr_utility.set_message(801, 'HR_51432_COMP_CERTIFY_FLAG');
43 hr_utility.raise_error;
44 ElsIf (p_constraint_name = 'PER_CPN_RENEWAL_CHK') Then
45 hr_utility.set_message(801, 'HR_51435_COMP_DEPENDENT_FIELDS');
46 hr_utility.set_message_token('PROCEDURE', l_proc);
47 hr_utility.set_message_token('STEP','35');
48 hr_utility.raise_error;
49 --
50 -- pmfletch Replaced with procedure chk_alias in TL row handler
51 -- -- ngundura changes done for pa requirements
52 -- ElsIf (p_constraint_name = 'PER_COMPETENCES_UK3') Then
53 -- fnd_message.set_name('PER', 'HR_52700_ALIAS_NOT_UNIQUE');
54 -- --hr_utility.set_message_token('PROCEDURE', l_proc);
55 -- --hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
56 -- fnd_message.raise_error;
57 -- -- ngundura end of changes
58 Else
59 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
60 hr_utility.set_message_token('PROCEDURE', l_proc);
61 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
62 hr_utility.raise_error;
63 End If;
64 --
65 hr_utility.set_location(' Leaving:'||l_proc, 10);
66 End constraint_error;
67 --
68 -- ----------------------------------------------------------------------------
69 -- |-----------------------------< api_updating >-----------------------------|
70 -- ----------------------------------------------------------------------------
71 Function api_updating
72 (
73 p_competence_id in number,
74 p_object_version_number in number
75 ) Return Boolean Is
76 --
77 --
78 -- Cursor selects the 'current' row from the HR Schema
79 --
80 Cursor C_Sel1 is
81 select
82 competence_id,
83 name,
84 business_group_id,
85 object_version_number,
86 description,
87 date_from,
88 date_to,
89 behavioural_indicator,
90 certification_required,
91 evaluation_method,
92 renewal_period_frequency,
93 renewal_period_units,
94 min_level,
95 max_level,
96 rating_scale_id,
97 attribute_category,
98 attribute1,
99 attribute2,
100 attribute3,
101 attribute4,
102 attribute5,
103 attribute6,
104 attribute7,
105 attribute8,
106 attribute9,
107 attribute10,
108 attribute11,
109 attribute12,
110 attribute13,
111 attribute14,
112 attribute15,
113 attribute16,
114 attribute17,
115 attribute18,
116 attribute19,
117 attribute20,
118 competence_alias,
119 competence_definition_id
120 ,competence_cluster -- BUG3356369
121 ,unit_standard_id
122 ,credit_type
123 ,credits
124 ,level_type
125 ,level_number
126 ,field
127 ,sub_field
128 ,provider
129 ,qa_organization
130 ,information_category
131 ,information1
132 ,information2
133 ,information3
134 ,information4
135 ,information5
136 ,information6
137 ,information7
138 ,information8
139 ,information9
140 ,information10
141 ,information11
142 ,information12
143 ,information13
144 ,information14
145 ,information15
146 ,information16
147 ,information17
148 ,information18
149 ,information19
150 ,information20
151 from per_competences
152 where competence_id = p_competence_id;
153 --
154 l_proc varchar2(72) := g_package||'api_updating';
155 l_fct_ret boolean;
156 --
157 Begin
158 hr_utility.set_location('Entering:'||l_proc, 5);
159 --
160 If (
161 p_competence_id is null and
162 p_object_version_number is null
163 ) Then
164 --
165 -- One of the primary key arguments is null therefore we must
166 -- set the returning function value to false
167 --
168 l_fct_ret := false;
169 Else
170 If (
171 p_competence_id = g_old_rec.competence_id and
172 p_object_version_number = g_old_rec.object_version_number
173 ) Then
174 hr_utility.set_location(l_proc, 10);
175 --
176 -- The g_old_rec is current therefore we must
177 -- set the returning function to true
178 --
179 l_fct_ret := true;
180 Else
181 --
182 -- Select the current row into g_old_rec
183 --
184 Open C_Sel1;
185 Fetch C_Sel1 Into g_old_rec;
186 If C_Sel1%notfound Then
187 Close C_Sel1;
188 --
189 -- The primary key is invalid therefore we must error
190 --
191 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
192 hr_utility.raise_error;
193 End If;
194 Close C_Sel1;
195 If (p_object_version_number <> g_old_rec.object_version_number) Then
196 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
197 hr_utility.raise_error;
198 End If;
199 hr_utility.set_location(l_proc, 15);
200 l_fct_ret := true;
201 End If;
202 End If;
203 hr_utility.set_location(' Leaving:'||l_proc, 20);
204 Return (l_fct_ret);
205 --
206 End api_updating;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |---------------------------------< lck >----------------------------------|
210 -- ----------------------------------------------------------------------------
211 Procedure lck
212 (
213 p_competence_id in number,
214 p_object_version_number in number
215 ) is
216 --
217 -- Cursor selects the 'current' row from the HR Schema
218 --
219 Cursor C_Sel1 is
220 select competence_id,
221 name,
222 business_group_id,
223 object_version_number,
224 description,
225 date_from,
226 date_to,
227 behavioural_indicator,
228 certification_required,
229 evaluation_method,
230 renewal_period_frequency,
231 renewal_period_units,
232 min_level,
233 max_level,
234 rating_scale_id,
235 attribute_category,
236 attribute1,
237 attribute2,
238 attribute3,
239 attribute4,
240 attribute5,
241 attribute6,
242 attribute7,
243 attribute8,
244 attribute9,
245 attribute10,
246 attribute11,
247 attribute12,
248 attribute13,
249 attribute14,
250 attribute15,
251 attribute16,
252 attribute17,
253 attribute18,
254 attribute19,
255 attribute20,
256 competence_alias,
257 competence_definition_id
258 ,competence_cluster -- BUG3356369
259 ,unit_standard_id
260 ,credit_type
261 ,credits
262 ,level_type
263 ,level_number
264 ,field
265 ,sub_field
266 ,provider
267 ,qa_organization
268 ,information_category
269 ,information1
270 ,information2
271 ,information3
272 ,information4
273 ,information5
274 ,information6
275 ,information7
276 ,information8
277 ,information9
278 ,information10
279 ,information11
280 ,information12
281 ,information13
282 ,information14
283 ,information15
284 ,information16
285 ,information17
286 ,information18
287 ,information19
288 ,information20
289 from per_competences
290 where competence_id = p_competence_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_competences');
333 hr_utility.raise_error;
334 End lck;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |-----------------------------< convert_args >-----------------------------|
338 -- ----------------------------------------------------------------------------
339 Function convert_args
340 (
341 p_competence_id in number,
342 p_name in varchar2,
343 p_business_group_id in number,
344 p_object_version_number in number,
345 p_description in varchar2,
346 p_date_from in date,
347 p_date_to in date,
348 p_behavioural_indicator in varchar2,
349 p_certification_required in varchar2,
350 p_evaluation_method in varchar2,
351 p_renewal_period_frequency in number,
352 p_renewal_period_units in varchar2,
353 p_min_level in number,
354 p_max_level in number,
355 p_rating_scale_id in number,
356 p_attribute_category in varchar2,
357 p_attribute1 in varchar2,
358 p_attribute2 in varchar2,
359 p_attribute3 in varchar2,
360 p_attribute4 in varchar2,
361 p_attribute5 in varchar2,
362 p_attribute6 in varchar2,
363 p_attribute7 in varchar2,
364 p_attribute8 in varchar2,
365 p_attribute9 in varchar2,
366 p_attribute10 in varchar2,
367 p_attribute11 in varchar2,
368 p_attribute12 in varchar2,
369 p_attribute13 in varchar2,
370 p_attribute14 in varchar2,
371 p_attribute15 in varchar2,
372 p_attribute16 in varchar2,
373 p_attribute17 in varchar2,
374 p_attribute18 in varchar2,
375 p_attribute19 in varchar2,
376 p_attribute20 in varchar2,
377 p_competence_alias in varchar2,
378 p_competence_definition_id in number
379 ,p_competence_cluster in varchar2 -- BUG3356369
380 ,p_unit_standard_id in varchar2
381 ,p_credit_type in varchar2
382 ,p_credits in number
383 ,p_level_type in varchar2
384 ,p_level_number in number
385 ,p_field in varchar2
386 ,p_sub_field in varchar2
387 ,p_provider in varchar2
388 ,p_qa_organization in varchar2
389 ,p_information_category in varchar2
390 ,p_information1 in varchar2
391 ,p_information2 in varchar2
392 ,p_information3 in varchar2
393 ,p_information4 in varchar2
394 ,p_information5 in varchar2
395 ,p_information6 in varchar2
396 ,p_information7 in varchar2
397 ,p_information8 in varchar2
398 ,p_information9 in varchar2
399 ,p_information10 in varchar2
400 ,p_information11 in varchar2
401 ,p_information12 in varchar2
402 ,p_information13 in varchar2
403 ,p_information14 in varchar2
404 ,p_information15 in varchar2
405 ,p_information16 in varchar2
406 ,p_information17 in varchar2
407 ,p_information18 in varchar2
408 ,p_information19 in varchar2
409 ,p_information20 in varchar2
410 )
411 Return g_rec_type is
412 --
413 l_rec g_rec_type;
414 l_proc varchar2(72) := g_package||'convert_args';
415 --
416 Begin
417 --
418 hr_utility.set_location('Entering:'||l_proc, 5);
419 --
420 -- Convert arguments into local l_rec structure.
421 --
422 l_rec.competence_id := p_competence_id;
423 l_rec.name := p_name;
424 l_rec.business_group_id := p_business_group_id;
425 l_rec.object_version_number := p_object_version_number;
426 l_rec.description := p_description;
427 l_rec.date_from := p_date_from;
428 l_rec.date_to := p_date_to;
429 l_rec.behavioural_indicator := p_behavioural_indicator;
430 l_rec.certification_required := p_certification_required;
431 l_rec.evaluation_method := p_evaluation_method;
432 l_rec.renewal_period_frequency := p_renewal_period_frequency;
433 l_rec.renewal_period_units := p_renewal_period_units;
434 l_rec.min_level := p_min_level;
435 l_rec.max_level := p_max_level;
436 l_rec.rating_scale_id := p_rating_scale_id;
437 l_rec.attribute_category := p_attribute_category;
438 l_rec.attribute1 := p_attribute1;
439 l_rec.attribute2 := p_attribute2;
440 l_rec.attribute3 := p_attribute3;
441 l_rec.attribute4 := p_attribute4;
442 l_rec.attribute5 := p_attribute5;
443 l_rec.attribute6 := p_attribute6;
444 l_rec.attribute7 := p_attribute7;
445 l_rec.attribute8 := p_attribute8;
446 l_rec.attribute9 := p_attribute9;
447 l_rec.attribute10 := p_attribute10;
448 l_rec.attribute11 := p_attribute11;
449 l_rec.attribute12 := p_attribute12;
450 l_rec.attribute13 := p_attribute13;
451 l_rec.attribute14 := p_attribute14;
452 l_rec.attribute15 := p_attribute15;
453 l_rec.attribute16 := p_attribute16;
454 l_rec.attribute17 := p_attribute17;
455 l_rec.attribute18 := p_attribute18;
456 l_rec.attribute19 := p_attribute19;
457 l_rec.attribute20 := p_attribute20;
458 l_rec.competence_alias := p_competence_alias;
459 l_rec.competence_definition_id := p_competence_definition_id;
460 -- BUG3356369
461 l_rec.competence_cluster := p_competence_cluster;
462 l_rec.unit_standard_id := p_unit_standard_id;
463 l_rec.credit_type := p_credit_type;
464 l_rec.credits := p_credits;
465 l_rec.level_type := p_level_type;
466 l_rec.level_number := p_level_number;
467 l_rec.field := p_field ;
468 l_rec.sub_field := p_sub_field;
469 l_rec.provider := p_provider;
470 l_rec.qa_organization := p_qa_organization;
471 l_rec.information_category := p_information_category;
472 l_rec.information1 := p_information1;
473 l_rec.information2 := p_information2;
474 l_rec.information3 := p_information3;
475 l_rec.information4 := p_information4;
476 l_rec.information5 := p_information5;
477 l_rec.information6 := p_information6;
478 l_rec.information7 := p_information7;
479 l_rec.information8 := p_information8;
480 l_rec.information9 := p_information9;
481 l_rec.information10 := p_information10;
482 l_rec.information11 := p_information11;
483 l_rec.information12 := p_information12;
484 l_rec.information13 := p_information13;
485 l_rec.information14 := p_information14;
486 l_rec.information15 := p_information15;
487 l_rec.information16 := p_information16;
488 l_rec.information17 := p_information17;
489 l_rec.information18 := p_information18;
490 l_rec.information19 := p_information19;
491 l_rec.information20 := p_information20;
492 --
493 -- Return the plsql record structure.
494 --
495 hr_utility.set_location(' Leaving:'||l_proc, 10);
496 Return(l_rec);
497 --
498 End convert_args;
499 --
500 end per_cpn_shd;