[Home] [Help]
PACKAGE BODY: APPS.OTA_TSP_SHD
Source
1 Package Body ota_tsp_shd as
2 /* $Header: ottsp01t.pkb 120.0 2005/05/29 07:54:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tsp_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 Begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 End return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
29 (p_constraint_name in varchar2) Is
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 If (p_constraint_name = 'OTA_SKILL_PROVISIONS_PK') Then
37 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
38 hr_utility.set_message_token('PROCEDURE', l_proc);
39 hr_utility.set_message_token('STEP','5');
40 hr_utility.raise_error;
41 ElsIf (p_constraint_name = 'OTA_TSP_TYPE_CHK') Then
42 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
43 hr_utility.set_message_token('PROCEDURE', l_proc);
44 hr_utility.set_message_token('STEP','10');
45 hr_utility.raise_error;
46 Else
47 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
48 hr_utility.set_message_token('PROCEDURE', l_proc);
49 hr_utility.set_message_token('STEP','15');
50 hr_utility.raise_error;
51 End If;
52 --
53 hr_utility.set_location(' Leaving:'||l_proc, 10);
54 End constraint_error;
55 --
56 -- ----------------------------------------------------------------------------
57 -- |-----------------------------< api_updating >-----------------------------|
58 -- ----------------------------------------------------------------------------
59 Function api_updating
60 (
61 p_skill_provision_id in number,
62 p_object_version_number in number
63 ) Return Boolean Is
64 --
65 --
66 -- Cursor selects the 'current' row from the HR Schema
67 --
68 Cursor C_Sel1 is
69 select
70 skill_provision_id,
71 activity_version_id,
72 object_version_number,
73 type,
74 comments,
75 tsp_information_category,
76 tsp_information1,
77 tsp_information2,
78 tsp_information3,
79 tsp_information4,
80 tsp_information5,
81 tsp_information6,
82 tsp_information7,
83 tsp_information8,
84 tsp_information9,
85 tsp_information10,
86 tsp_information11,
87 tsp_information12,
88 tsp_information13,
89 tsp_information14,
90 tsp_information15,
91 tsp_information16,
92 tsp_information17,
93 tsp_information18,
94 tsp_information19,
95 tsp_information20,
96 analysis_criteria_id
97 from ota_skill_provisions
98 where skill_provision_id = p_skill_provision_id;
99 --
100 l_proc varchar2(72) := g_package||'api_updating';
101 l_fct_ret boolean;
102 --
103 Begin
104 hr_utility.set_location('Entering:'||l_proc, 5);
105 --
106 If (
107 p_skill_provision_id is null and
108 p_object_version_number is null
109 ) Then
110 --
111 -- One of the primary key arguments is null therefore we must
112 -- set the returning function value to false
113 --
114 l_fct_ret := false;
115 Else
116 If (
117 p_skill_provision_id = g_old_rec.skill_provision_id and
118 p_object_version_number = g_old_rec.object_version_number
119 ) Then
120 hr_utility.set_location(l_proc, 10);
121 --
122 -- The g_old_rec is current therefore we must
123 -- set the returning function to true
124 --
125 l_fct_ret := true;
126 Else
127 --
128 -- Select the current row into g_old_rec
129 --
130 Open C_Sel1;
131 Fetch C_Sel1 Into g_old_rec;
132 If C_Sel1%notfound Then
133 Close C_Sel1;
134 --
135 -- The primary key is invalid therefore we must error
136 --
137 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
138 hr_utility.raise_error;
139 End If;
140 Close C_Sel1;
141 If (p_object_version_number <> g_old_rec.object_version_number) Then
142 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
143 hr_utility.raise_error;
144 End If;
145 hr_utility.set_location(l_proc, 15);
146 l_fct_ret := true;
147 End If;
148 End If;
149 hr_utility.set_location(' Leaving:'||l_proc, 20);
150 Return (l_fct_ret);
151 --
152 End api_updating;
153 --
154 -- ----------------------------------------------------------------------------
155 -- |---------------------------------< lck >----------------------------------|
156 -- ----------------------------------------------------------------------------
157 Procedure lck
158 (
159 p_skill_provision_id in number,
160 p_object_version_number in number
161 ) is
162 --
163 -- Cursor selects the 'current' row from the HR Schema
164 --
165 Cursor C_Sel1 is
166 select skill_provision_id,
167 activity_version_id,
168 object_version_number,
169 type,
170 comments,
171 tsp_information_category,
172 tsp_information1,
173 tsp_information2,
174 tsp_information3,
175 tsp_information4,
176 tsp_information5,
177 tsp_information6,
178 tsp_information7,
179 tsp_information8,
180 tsp_information9,
181 tsp_information10,
182 tsp_information11,
183 tsp_information12,
184 tsp_information13,
185 tsp_information14,
186 tsp_information15,
187 tsp_information16,
188 tsp_information17,
189 tsp_information18,
190 tsp_information19,
191 tsp_information20,
192 analysis_criteria_id
193 from ota_skill_provisions
194 where skill_provision_id = p_skill_provision_id
195 for update nowait;
196 --
197 l_proc varchar2(72) := g_package||'lck';
198 --
199 Begin
200 hr_utility.set_location('Entering:'||l_proc, 5);
201 --
202 -- Add any mandatory argument checking here:
203 -- Example:
204 -- hr_api.mandatory_arg_error
205 -- (p_api_name => l_proc,
206 -- p_argument => 'object_version_number',
207 -- p_argument_value => p_object_version_number);
208 --
209 Open C_Sel1;
210 Fetch C_Sel1 Into g_old_rec;
211 If C_Sel1%notfound then
212 Close C_Sel1;
213 --
214 -- The primary key is invalid therefore we must error
215 --
216 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
217 hr_utility.raise_error;
218 End If;
219 Close C_Sel1;
220 If (p_object_version_number <> g_old_rec.object_version_number) Then
221 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
222 hr_utility.raise_error;
223 End If;
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 10);
226 --
227 -- We need to trap the ORA LOCK exception
228 --
229 Exception
230 When HR_Api.Object_Locked then
231 --
232 -- The object is locked therefore we need to supply a meaningful
233 -- error message.
234 --
235 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
236 hr_utility.set_message_token('TABLE_NAME', 'ota_skill_provisions');
237 hr_utility.raise_error;
238 End lck;
239 --
240 -- ----------------------------------------------------------------------------
241 -- |-----------------------------< convert_args >-----------------------------|
242 -- ----------------------------------------------------------------------------
243 Function convert_args
244 (
245 p_skill_provision_id in number,
246 p_activity_version_id in number,
247 p_object_version_number in number,
248 p_type in varchar2,
249 p_comments in varchar2,
250 p_tsp_information_category in varchar2,
251 p_tsp_information1 in varchar2,
252 p_tsp_information2 in varchar2,
253 p_tsp_information3 in varchar2,
254 p_tsp_information4 in varchar2,
255 p_tsp_information5 in varchar2,
256 p_tsp_information6 in varchar2,
257 p_tsp_information7 in varchar2,
258 p_tsp_information8 in varchar2,
259 p_tsp_information9 in varchar2,
260 p_tsp_information10 in varchar2,
261 p_tsp_information11 in varchar2,
262 p_tsp_information12 in varchar2,
263 p_tsp_information13 in varchar2,
264 p_tsp_information14 in varchar2,
265 p_tsp_information15 in varchar2,
266 p_tsp_information16 in varchar2,
267 p_tsp_information17 in varchar2,
268 p_tsp_information18 in varchar2,
269 p_tsp_information19 in varchar2,
270 p_tsp_information20 in varchar2,
271 p_analysis_criteria_id in number
272 )
273 Return g_rec_type is
274 --
275 l_rec g_rec_type;
276 l_proc varchar2(72) := g_package||'convert_args';
277 --
278 Begin
279 --
280 hr_utility.set_location('Entering:'||l_proc, 5);
281 --
282 -- Convert arguments into local l_rec structure.
283 --
284 l_rec.skill_provision_id := p_skill_provision_id;
285 l_rec.activity_version_id := p_activity_version_id;
286 l_rec.object_version_number := p_object_version_number;
287 l_rec.type := p_type;
288 l_rec.comments := p_comments;
289 l_rec.tsp_information_category := p_tsp_information_category;
290 l_rec.tsp_information1 := p_tsp_information1;
291 l_rec.tsp_information2 := p_tsp_information2;
292 l_rec.tsp_information3 := p_tsp_information3;
293 l_rec.tsp_information4 := p_tsp_information4;
294 l_rec.tsp_information5 := p_tsp_information5;
295 l_rec.tsp_information6 := p_tsp_information6;
296 l_rec.tsp_information7 := p_tsp_information7;
297 l_rec.tsp_information8 := p_tsp_information8;
298 l_rec.tsp_information9 := p_tsp_information9;
299 l_rec.tsp_information10 := p_tsp_information10;
300 l_rec.tsp_information11 := p_tsp_information11;
301 l_rec.tsp_information12 := p_tsp_information12;
302 l_rec.tsp_information13 := p_tsp_information13;
303 l_rec.tsp_information14 := p_tsp_information14;
304 l_rec.tsp_information15 := p_tsp_information15;
305 l_rec.tsp_information16 := p_tsp_information16;
306 l_rec.tsp_information17 := p_tsp_information17;
307 l_rec.tsp_information18 := p_tsp_information18;
308 l_rec.tsp_information19 := p_tsp_information19;
309 l_rec.tsp_information20 := p_tsp_information20;
310 l_rec.analysis_criteria_id := p_analysis_criteria_id;
311 --
312 -- Return the plsql record structure.
313 --
314 hr_utility.set_location(' Leaving:'||l_proc, 10);
315 Return(l_rec);
316 --
317 End convert_args;
318 --
319 -- ----------------------------------------------------------------------------
320 -- |-----------------------------< copy_skill >-------------------------------|
321 -- ----------------------------------------------------------------------------
322 --
323 -- Copy all skills from one activity to another
324 --
325 Procedure copy_skill
326 (
327 p_activity_version_from in number,
328 p_activity_version_to in number
329 ) Is
330 --
331 l_rec g_rec_type;
332 v_proc varchar2(72):= g_package||'copy_skill';
333 --
334 --
335 cursor sel_skill is
336 select
337 skill_provision_id,
338 activity_version_id,
339 type,
340 comments,
341 tsp_information_category,
342 tsp_information1,
343 tsp_information2,
344 tsp_information3,
345 tsp_information4,
346 tsp_information5,
347 tsp_information6,
348 tsp_information7,
349 tsp_information8,
350 tsp_information9,
351 tsp_information10,
352 tsp_information11,
353 tsp_information12,
354 tsp_information13,
355 tsp_information14,
356 tsp_information15,
357 tsp_information16,
358 tsp_information17,
359 tsp_information18,
360 tsp_information19,
361 tsp_information20,
362 analysis_criteria_id
363 from ota_skill_provisions
364 where activity_version_id = p_activity_version_from;
365 --
366 Begin
367 --
368 hr_utility.set_location('Entering:'||v_proc, 5);
369 --
370 Open sel_skill;
371 Fetch sel_skill into
372 l_rec.skill_provision_id,
373 l_rec.activity_version_id,
374 l_rec.type,
375 l_rec.comments,
376 l_rec.tsp_information_category,
377 l_rec.tsp_information1,
378 l_rec.tsp_information2,
379 l_rec.tsp_information3,
380 l_rec.tsp_information4,
381 l_rec.tsp_information5,
382 l_rec.tsp_information6,
383 l_rec.tsp_information7,
384 l_rec.tsp_information8,
385 l_rec.tsp_information9,
386 l_rec.tsp_information10,
387 l_rec.tsp_information11,
388 l_rec.tsp_information12,
389 l_rec.tsp_information13,
390 l_rec.tsp_information14,
391 l_rec.tsp_information15,
392 l_rec.tsp_information16,
393 l_rec.tsp_information17,
394 l_rec.tsp_information18,
395 l_rec.tsp_information19,
396 l_rec.tsp_information20,
397 l_rec.analysis_criteria_id;
398 --
399 Loop
400 --
401 Exit When sel_skill%notfound;
402 --
403 ota_tsp_ins. ins(
404 l_rec.skill_provision_id,
405 p_activity_version_to,
406 l_rec.object_version_number,
407 l_rec.type,
408 l_rec.comments,
409 l_rec.tsp_information_category,
410 l_rec.tsp_information1,
411 l_rec.tsp_information2,
412 l_rec.tsp_information3,
413 l_rec.tsp_information4,
414 l_rec.tsp_information5,
415 l_rec.tsp_information6,
416 l_rec.tsp_information7,
417 l_rec.tsp_information8,
418 l_rec.tsp_information9,
419 l_rec.tsp_information10,
420 l_rec.tsp_information11,
421 l_rec.tsp_information12,
422 l_rec.tsp_information13,
423 l_rec.tsp_information14,
424 l_rec.tsp_information15,
425 l_rec.tsp_information16,
426 l_rec.tsp_information17,
427 l_rec.tsp_information18,
428 l_rec.tsp_information19,
429 l_rec.tsp_information20,
430 l_rec.analysis_criteria_id,
431 false);
432 --
433 Fetch sel_skill into
434 l_rec.skill_provision_id,
435 l_rec.activity_version_id,
436 l_rec.type,
437 l_rec.comments,
438 l_rec.tsp_information_category,
439 l_rec.tsp_information1,
440 l_rec.tsp_information2,
441 l_rec.tsp_information3,
442 l_rec.tsp_information4,
443 l_rec.tsp_information5,
444 l_rec.tsp_information6,
445 l_rec.tsp_information7,
446 l_rec.tsp_information8,
447 l_rec.tsp_information9,
448 l_rec.tsp_information10,
449 l_rec.tsp_information11,
450 l_rec.tsp_information12,
451 l_rec.tsp_information13,
452 l_rec.tsp_information14,
453 l_rec.tsp_information15,
454 l_rec.tsp_information16,
455 l_rec.tsp_information17,
456 l_rec.tsp_information18,
457 l_rec.tsp_information19,
458 l_rec.tsp_information20,
459 l_rec.analysis_criteria_id;
460 --
461 End Loop;
462 --
463 Close sel_skill;
464 --
465 hr_utility.set_location('Leaving:'||v_proc, 10);
466 --
467 End copy_skill;
468 --
469 end ota_tsp_shd;