[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SKILL_LEVELS_PVT
Source
1 PACKAGE BODY JTF_RS_SKILL_LEVELS_PVT AS
2 /* $Header: jtfrsesb.pls 120.0 2005/05/11 08:19:57 appldev ship $ */
3
4 /*****************************************************************************************
5 Its main procedures are as following:
6 Create skill levels
7 Update skill levels
8 Delete skill levels
9 Calls to these procedures will invoke procedures from JTF_RS_SKILL_LEVELS_PUB
10 to do business validations and to do actual inserts, updates and deletes into tables.
11 ******************************************************************************************/
12 /* Package variables. */
13
14 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_SKILL_LEVELS_PVT';
15 G_NAME VARCHAR2(240);
16
17 PROCEDURE chk_dup_skill_level(P_SKILL_LEVEL IN NUMBER,
18 X_SKILL_LEVEL_ID IN OUT NOCOPY NUMBER,
19 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
20 X_MSG_COUNT OUT NOCOPY NUMBER,
21 X_MSG_DATA OUT NOCOPY VARCHAR2) is
22 cursor chk_dup_skill(ll_skill_level jtf_rs_skill_levels_b.skill_level%type,
23 ll_skill_level_id jtf_rs_skill_levels_b.skill_level_id%type)
24 is
25 select * from
26 jtf_rs_skill_levels_b where
27 skill_level = ll_skill_level and
28 skill_level_id <> ll_skill_level_id;
29
30 skill_levels_rec chk_dup_skill%rowtype;
31 BEGIN
32 x_return_status := fnd_api.g_ret_sts_success;
33 open chk_dup_skill(p_skill_level, x_skill_level_id);
34 fetch chk_dup_skill into skill_levels_rec;
35 if (chk_dup_skill%NOTFOUND) then
36 close chk_dup_skill;
37 ELSIF chk_dup_skill%FOUND THEN
38 close chk_dup_skill;
39 x_return_status := fnd_api.g_ret_sts_error;
40 x_skill_level_id := skill_levels_rec.skill_level_id;
41 fnd_message.set_name ('JTF', 'JTF_RS_DUP_SKILL_LEVEL');
42 FND_MSG_PUB.add;
43 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
44 raise fnd_api.g_exc_error;
45 END IF;
46 END;
47
48 PROCEDURE chk_dup_level_name(P_LEVEL_NAME IN VARCHAR2,
49 X_SKILL_LEVEL_ID IN OUT NOCOPY NUMBER,
50 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
51 X_MSG_COUNT OUT NOCOPY NUMBER,
52 X_MSG_DATA OUT NOCOPY VARCHAR2)
53 is
54 cursor chk_dup_name
55 (ll_level_name jtf_rs_skill_levels_tl.level_name%type,
56 ll_skill_level_id jtf_rs_skill_levels_b.skill_level_id%type)
57 is
58 select * from
59 jtf_rs_skill_levels_tl where
60 upper(level_name) = upper(ll_level_name) and
61 skill_level_id <> ll_skill_level_id;
62
63 levels_vl_rec chk_dup_name%rowtype;
64 BEGIN
65 x_return_status := fnd_api.g_ret_sts_success;
66 open chk_dup_name(p_level_name, x_skill_level_id);
67 fetch chk_dup_name INTO levels_vl_rec;
68
69 if chk_dup_name%NOTFOUND then
70 CLOSE chk_dup_name;
71 ELSIF chk_dup_name%FOUND THEN
72 CLOSE chk_dup_name;
73 x_return_status := fnd_api.g_ret_sts_error;
74 x_skill_level_id := levels_vl_rec.skill_level_id;
75 fnd_message.set_name ('JTF', 'JTF_RS_DUP_SKILL_LEVEL_NAME');
76 FND_MSG_PUB.add;
77 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
78 raise fnd_api.g_exc_error;
79 END IF;
80
81 END chk_dup_level_name;
82
83 /* Procedure to create the skill levels
84 based on input values passed by calling routines. */
85
86 PROCEDURE create_skills
87 (P_API_VERSION IN NUMBER,
88 P_INIT_MSG_LIST IN VARCHAR2,
89 P_COMMIT IN VARCHAR2,
90 P_SKILL_LEVEL IN JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL%TYPE,
91 P_LEVEL_NAME IN JTF_RS_SKILL_LEVELS_TL.LEVEL_NAME%TYPE,
92 P_LEVEL_DESC IN JTF_RS_SKILL_LEVELS_TL.LEVEL_DESC%TYPE,
93 P_ATTRIBUTE1 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE1%TYPE,
94 P_ATTRIBUTE2 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE2%TYPE,
95 P_ATTRIBUTE3 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE3%TYPE,
96 P_ATTRIBUTE4 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE4%TYPE,
97 P_ATTRIBUTE5 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE5%TYPE,
98 P_ATTRIBUTE6 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE6%TYPE,
99 P_ATTRIBUTE7 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE7%TYPE,
100 P_ATTRIBUTE8 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE8%TYPE,
101 P_ATTRIBUTE9 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE9%TYPE,
102 P_ATTRIBUTE10 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE10%TYPE,
103 P_ATTRIBUTE11 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE11%TYPE,
104 P_ATTRIBUTE12 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE12%TYPE,
105 P_ATTRIBUTE13 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE13%TYPE,
106 P_ATTRIBUTE14 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE14%TYPE,
107 P_ATTRIBUTE15 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE15%TYPE,
108 P_ATTRIBUTE_CATEGORY IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE_CATEGORY%TYPE,
109 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
110 X_MSG_COUNT OUT NOCOPY NUMBER,
111 X_MSG_DATA OUT NOCOPY VARCHAR2,
112 X_SKILL_LEVEL_ID OUT NOCOPY JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE
113 )IS
114 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SKILLS';
115 l_api_version CONSTANT NUMBER :=1.0;
116 l_bind_data_id number;
117
118 l_object_version_number number := 1;
119
120 l_skill_level_id JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE;
121
122 l_return_code VARCHAR2(100);
123 l_count NUMBER;
124 l_data VARCHAR2(200);
125
126 l_return_status VARCHAR2(200);
127 l_msg_count NUMBER;
128 l_msg_data VARCHAR2(200);
129 l_rowid VARCHAR2(200);
130
131 l_date Date;
132 l_user_id Number;
133 l_login_id Number;
134
135 BEGIN
136 --Standard Start of API SAVEPOINT
137 SAVEPOINT SKILL_SP;
138
139 x_return_status := fnd_api.g_ret_sts_success;
140
141 --Standard Call to check API compatibility
142 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
143 THEN
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146
147 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
148 IF FND_API.To_boolean(P_INIT_MSG_LIST)
149 THEN
150 FND_MSG_PUB.Initialize;
151 END IF;
152
153 --GET USER ID AND SYSDATE
154 l_date := sysdate;
155 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
156 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
157 x_skill_level_id := -1;
158
159 chk_dup_skill_level(P_SKILL_LEVEL => p_skill_level,
160 X_SKILL_LEVEL_ID => x_skill_level_id,
161 X_RETURN_STATUS => x_return_status,
162 X_MSG_COUNT => x_msg_count,
163 X_MSG_DATA => x_msg_data);
164 if (p_level_name is not null) then
165 chk_dup_level_name(P_LEVEL_NAME => p_level_name,
166 X_SKILL_LEVEL_ID => X_SKILL_LEVEL_ID,
167 X_RETURN_STATUS => x_return_status,
168 X_MSG_COUNT => x_msg_count,
169 X_MSG_DATA => x_msg_data);
170 end if;
171
172 SELECT jtf_rs_skill_levels_s.nextval
173 INTO l_skill_level_id
174 FROM dual;
175
176 JTF_RS_SKILL_LEVELS_PKG.INSERT_ROW(
177 X_ROWID => l_rowid,
178 X_SKILL_LEVEL_ID => l_skill_level_id,
179 X_SKILL_LEVEL => p_skill_level,
180 X_LEVEL_NAME => p_level_name,
181 X_LEVEL_DESC => p_level_desc,
182 X_OBJECT_VERSION_NUMBER => l_object_version_number,
183 X_ATTRIBUTE1 => p_attribute1,
184 X_ATTRIBUTE2 => p_attribute2,
185 X_ATTRIBUTE3 => p_attribute3,
186 X_ATTRIBUTE4 => p_attribute4,
187 X_ATTRIBUTE5 => p_attribute5,
188 X_ATTRIBUTE6 => p_attribute6,
189 X_ATTRIBUTE7 => p_attribute7,
190 X_ATTRIBUTE8 => p_attribute8,
191 X_ATTRIBUTE9 => p_attribute9,
192 X_ATTRIBUTE10 => p_attribute10,
193 X_ATTRIBUTE11 => p_attribute11,
194 X_ATTRIBUTE12 => p_attribute12,
195 X_ATTRIBUTE13 => p_attribute13,
196 X_ATTRIBUTE14 => p_attribute14,
197 X_ATTRIBUTE15 => p_attribute15,
198 X_ATTRIBUTE_CATEGORY => p_attribute_category,
199 X_CREATION_DATE => sysdate,
200 X_CREATED_BY => l_user_id,
201 X_LAST_UPDATE_DATE => sysdate,
202 X_LAST_UPDATED_BY => l_user_id,
203 X_LAST_UPDATE_LOGIN => 0);
204
205 x_skill_level_id := l_skill_level_id;
206 --standard commit
207 IF fnd_api.to_boolean (p_commit)
208 THEN
209 COMMIT WORK;
210 END IF;
211
212 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
213
214 EXCEPTION
215 WHEN fnd_api.g_exc_unexpected_error
216 THEN
217 ROLLBACK TO SKILL_SP;
218 x_return_status := fnd_api.g_ret_sts_unexp_error;
219 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
220 WHEN fnd_api.g_exc_error
221 THEN
222 ROLLBACK TO SKILL_SP;
223 x_return_status := fnd_api.g_ret_sts_error;
224 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
225 WHEN OTHERS
226 THEN
227 ROLLBACK TO SKILL_SP;
228 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
229 fnd_message.set_token('P_SQLCODE',SQLCODE);
230 fnd_message.set_token('P_SQLERRM',SQLERRM);
231 fnd_message.set_token('P_API_NAME', l_api_name);
232 FND_MSG_PUB.add;
233 x_return_status := fnd_api.g_ret_sts_unexp_error;
234 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
235
236 END create_skills;
237
238
239 /* Procedure to update skill levels
240 based on input values passed by calling routines. */
241
242 PROCEDURE update_skills
243 (P_API_VERSION IN NUMBER,
244 P_INIT_MSG_LIST IN VARCHAR2,
245 P_COMMIT IN VARCHAR2,
246 P_SKILL_LEVEL_ID IN JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE,
247 P_SKILL_LEVEL IN JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL%TYPE,
248 P_LEVEL_NAME IN JTF_RS_SKILL_LEVELS_TL.LEVEL_NAME%TYPE,
249 P_LEVEL_DESC IN JTF_RS_SKILL_LEVELS_TL.LEVEL_DESC%TYPE,
250 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_SKILL_LEVELS_B.OBJECT_VERSION_NUMBER%TYPE,
251 P_ATTRIBUTE1 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE1%TYPE,
252 P_ATTRIBUTE2 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE2%TYPE,
253 P_ATTRIBUTE3 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE3%TYPE,
254 P_ATTRIBUTE4 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE4%TYPE,
255 P_ATTRIBUTE5 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE5%TYPE,
256 P_ATTRIBUTE6 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE6%TYPE,
257 P_ATTRIBUTE7 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE7%TYPE,
258 P_ATTRIBUTE8 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE8%TYPE,
259 P_ATTRIBUTE9 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE9%TYPE,
260 P_ATTRIBUTE10 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE10%TYPE,
261 P_ATTRIBUTE11 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE11%TYPE,
262 P_ATTRIBUTE12 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE12%TYPE,
263 P_ATTRIBUTE13 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE13%TYPE,
264 P_ATTRIBUTE14 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE14%TYPE,
265 P_ATTRIBUTE15 IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE15%TYPE,
266 P_ATTRIBUTE_CATEGORY IN JTF_RS_SKILL_LEVELS_B.ATTRIBUTE_CATEGORY%TYPE,
267 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
268 X_MSG_COUNT OUT NOCOPY NUMBER,
269 X_MSG_DATA OUT NOCOPY VARCHAR2
270 )IS
271 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SKILLS';
272 l_api_version CONSTANT NUMBER := 1.0;
273 l_bind_data_id number;
274
275 l_return_code VARCHAR2(100);
276 l_count NUMBER;
277 l_data VARCHAR2(200);
278
279
280 L_ATTRIBUTE1 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE1%TYPE;
281 L_ATTRIBUTE2 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE2%TYPE;
282 L_ATTRIBUTE3 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE3%TYPE;
283 L_ATTRIBUTE4 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE4%TYPE;
284 L_ATTRIBUTE5 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE5%TYPE;
285 L_ATTRIBUTE6 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE6%TYPE;
286 L_ATTRIBUTE7 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE7%TYPE;
287 L_ATTRIBUTE8 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE8%TYPE;
288 L_ATTRIBUTE9 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE9%TYPE;
289 L_ATTRIBUTE10 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE10%TYPE;
290 L_ATTRIBUTE11 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE11%TYPE;
291 L_ATTRIBUTE12 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE12%TYPE;
292 L_ATTRIBUTE13 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE13%TYPE;
293 L_ATTRIBUTE14 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE14%TYPE;
294 L_ATTRIBUTE15 JTF_RS_SKILL_LEVELS_B.ATTRIBUTE15%TYPE;
295 L_ATTRIBUTE_CATEGORY JTF_RS_SKILL_LEVELS_B.ATTRIBUTE_CATEGORY%TYPE;
296
297
298 CURSOR chk_skill_cur(ll_skill_level_id JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE)
299 IS
300 SELECT *
301 FROM jtf_rs_skill_levels_vl
302 WHERE skill_level_id = ll_skill_level_id;
303
304 skill_levels_rec chk_skill_cur%rowtype;
305
306 l_skill_level_id JTF_RS_SKILL_LEVELS_B.skill_level_id%TYPE := p_skill_level_id;
307 l_skill_level JTF_RS_SKILL_LEVELS_B.skill_level%TYPE := p_skill_level;
308 l_level_name JTF_RS_SKILL_LEVELS_TL.level_name%TYPE := p_level_name;
309 l_level_desc JTF_RS_SKILL_LEVELS_TL.level_desc%type := p_level_desc;
310 l_object_version_number JTF_RS_SKILL_LEVELS_B.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
311
312 l_return_status VARCHAR2(200);
313 l_msg_count NUMBER;
314 l_msg_data VARCHAR2(200);
315 l_rowid VARCHAR2(200);
316
317 l_date Date;
318 l_user_id Number;
319 l_login_id Number;
320 x_skill_level_id number;
321
322 BEGIN
323 --Standard Start of API SAVEPOINT
324 SAVEPOINT SKILL_LEVELS_SP;
325
326 x_return_status := fnd_api.g_ret_sts_success;
327
328 --Standard Call to check API compatibility
329 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
330 THEN
331 RAISE FND_API.G_EXC_ERROR;
332 END IF;
333
334 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
335 IF FND_API.To_boolean(P_INIT_MSG_LIST)
336 THEN
337 FND_MSG_PUB.Initialize;
338 END IF;
339
340
341 --GET USER ID AND SYSDATE
342 l_date := sysdate;
343 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
344 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
345
346 OPEN chk_skill_cur(l_skill_level_id);
347 FETCH chk_skill_cur INTO skill_levels_rec;
348
349 IF (chk_skill_cur%found) THEN
350 CLOSE chk_skill_cur;
351
352
353 IF (p_skill_level = FND_API.G_MISS_NUM)
354 THEN
355 l_skill_level := skill_levels_rec.skill_level;
356 ELSE
357 chk_dup_skill_level(P_SKILL_LEVEL => p_skill_level,
358 X_SKILL_LEVEL_ID => l_skill_level_id,
359 X_RETURN_STATUS => x_return_status,
360 X_MSG_COUNT => x_msg_count,
361 X_MSG_DATA => x_msg_data);
362 l_skill_level := p_skill_level;
363 END IF;
364
365 IF (p_level_name = FND_API.G_MISS_CHAR)
366 THEN
367 l_level_name := skill_levels_rec.level_name;
368 ELSE
369 chk_dup_level_name(P_LEVEL_NAME => p_level_name,
370 X_SKILL_LEVEL_ID => l_SKILL_LEVEL_ID,
371 X_RETURN_STATUS => x_return_status,
372 X_MSG_COUNT => x_msg_count,
373 X_MSG_DATA => x_msg_data);
374 l_level_name := p_level_name;
375 END IF;
376
377 IF (p_level_desc = FND_API.G_MISS_CHAR)
378 THEN
379 l_level_desc := skill_levels_rec.level_desc;
380 ELSE
381 l_level_desc := p_level_desc;
382 END IF;
383
384 IF(p_attribute1 = FND_API.G_MISS_CHAR)
385 THEN
386 l_attribute1 := skill_levels_rec.attribute1;
387 ELSE
388 l_attribute1 := p_attribute1;
389 END IF;
390
391 IF(p_attribute2 = FND_API.G_MISS_CHAR)
392 THEN
393 l_attribute2 := skill_levels_rec.attribute2;
394 ELSE
395 l_attribute2 := p_attribute2;
396 END IF;
397
398 IF(p_attribute3 = FND_API.G_MISS_CHAR)
399 THEN
400 l_attribute3 := skill_levels_rec.attribute3;
401 ELSE
402 l_attribute3 := p_attribute3;
403 END IF;
404
405 IF(p_attribute4 = FND_API.G_MISS_CHAR)
406 THEN
407 l_attribute4 := skill_levels_rec.attribute4;
408 ELSE
409 l_attribute4 := p_attribute4;
410 END IF;
411
412 IF(p_attribute5 = FND_API.G_MISS_CHAR)
413 THEN
414 l_attribute5 := skill_levels_rec.attribute5;
415 ELSE
416 l_attribute5 := p_attribute5;
417 END IF;
418
419 IF(p_attribute6 = FND_API.G_MISS_CHAR)
420 THEN
421 l_attribute6 := skill_levels_rec.attribute6;
422 ELSE
423 l_attribute6 := p_attribute6;
424 END IF;
425
426 IF(p_attribute7 = FND_API.G_MISS_CHAR)
427 THEN
428 l_attribute7 := skill_levels_rec.attribute7;
429 ELSE
430 l_attribute7 := p_attribute7;
431 END IF;
432
433 IF(p_attribute8 = FND_API.G_MISS_CHAR)
434 THEN
435 l_attribute8 := skill_levels_rec.attribute8;
436 ELSE
437 l_attribute8 := p_attribute8;
438 END IF;
439
440 IF(p_attribute9 = FND_API.G_MISS_CHAR)
441 THEN
442 l_attribute9 := skill_levels_rec.attribute9;
443 ELSE
444 l_attribute9 := p_attribute9;
445 END IF;
446
447 IF(p_attribute10 = FND_API.G_MISS_CHAR)
448 THEN
449 l_attribute10 := skill_levels_rec.attribute10;
450 ELSE
451 l_attribute10 := p_attribute10;
452 END IF;
453
454 IF(p_attribute11 = FND_API.G_MISS_CHAR)
455 THEN
456 l_attribute11 := skill_levels_rec.attribute11;
457 ELSE
458 l_attribute11 := p_attribute11;
459 END IF;
460
461 IF(p_attribute12 = FND_API.G_MISS_CHAR)
462 THEN
463 l_attribute12 := skill_levels_rec.attribute12;
464 ELSE
465 l_attribute12 := p_attribute12;
466 END IF;
467
468 IF(p_attribute13 = FND_API.G_MISS_CHAR)
469 THEN
470 l_attribute13 := skill_levels_rec.attribute13;
471 ELSE
472 l_attribute13 := p_attribute13;
473 END IF;
474
475 IF(p_attribute14 = FND_API.G_MISS_CHAR)
476 THEN
477 l_attribute14 := skill_levels_rec.attribute14;
478 ELSE
479 l_attribute14 := p_attribute14;
480 END IF;
481
482 IF(p_attribute15 = FND_API.G_MISS_CHAR)
483 THEN
484 l_attribute15 := skill_levels_rec.attribute15;
485 ELSE
486 l_attribute15 := p_attribute15;
487 END IF;
488
489 IF(p_attribute_category = FND_API.G_MISS_CHAR)
490 THEN
491 l_attribute_category := skill_levels_rec.attribute_category;
492 ELSE
493 l_attribute_category := p_attribute_category;
494 END IF;
495
496
497 BEGIN
498
499 jtf_rs_skill_levels_pkg.lock_row(
500 X_SKILL_LEVEL_ID => l_skill_level_id,
501 X_OBJECT_VERSION_NUMBER => l_object_version_number
502 );
503
504 EXCEPTION
505
506 WHEN OTHERS THEN
507 x_return_status := fnd_api.g_ret_sts_error;
508 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
509 fnd_msg_pub.add;
510 RAISE fnd_api.g_exc_error;
511
512 END;
513
514
515 l_object_version_number := l_object_version_number +1;
516
517 jtf_rs_skill_levels_pkg.update_row(
518 X_SKILL_LEVEL_ID => l_skill_level_id,
519 X_SKILL_LEVEL => l_skill_level,
520 X_LEVEL_NAME => l_level_name,
521 X_LEVEL_DESC => l_level_desc,
522 X_OBJECT_VERSION_NUMBER => l_object_version_number,
523 X_ATTRIBUTE1 => l_attribute1,
524 X_ATTRIBUTE2 => l_attribute2,
525 X_ATTRIBUTE3 => l_attribute3,
526 X_ATTRIBUTE4 => l_attribute4,
527 X_ATTRIBUTE5 => l_attribute5,
528 X_ATTRIBUTE6 => l_attribute6,
529 X_ATTRIBUTE7 => l_attribute7,
530 X_ATTRIBUTE8 => l_attribute8,
531 X_ATTRIBUTE9 => l_attribute9,
532 X_ATTRIBUTE10 => l_attribute10,
533 X_ATTRIBUTE11 => l_attribute11,
534 X_ATTRIBUTE12 => l_attribute12,
535 X_ATTRIBUTE13 => l_attribute13,
536 X_ATTRIBUTE14 => l_attribute14,
537 X_ATTRIBUTE15 => l_attribute15,
538 X_ATTRIBUTE_CATEGORY => l_attribute_category,
539 X_LAST_UPDATE_DATE => l_date,
540 X_LAST_UPDATED_BY => l_user_id,
541 X_LAST_UPDATE_LOGIN => l_login_id);
542
543 P_OBJECT_VERSION_NUM := l_object_version_number;
544
545 ELSIF (chk_skill_cur%notfound) THEN
546 CLOSE chk_skill_cur;
547 x_return_status := fnd_api.g_ret_sts_error;
548 fnd_message.set_name ('JTF', 'JTF_RS_SKILL_LEVEL_ID_INVALID');
549 FND_MSG_PUB.add;
550 RAISE fnd_api.g_exc_error;
551
552 END IF;
553
554
555 IF fnd_api.to_boolean (p_commit)
556 THEN
557 COMMIT WORK;
558 END IF;
559
560 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
561
562 EXCEPTION
563 WHEN fnd_api.g_exc_error
564 THEN
565 ROLLBACK TO SKILL_LEVELS_SP;
566 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
567 x_return_status := fnd_api.g_ret_sts_error;
568
569 WHEN fnd_api.g_exc_unexpected_error
570 THEN
571 ROLLBACK TO SKILL_LEVELS_SP;
572 x_return_status := fnd_api.g_ret_sts_unexp_error;
573 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
574 WHEN OTHERS
575 THEN
576 ROLLBACK TO SKILL_LEVELS_SP;
577 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
578 fnd_message.set_token('P_SQLCODE',SQLCODE);
579 fnd_message.set_token('P_SQLERRM',SQLERRM);
580 fnd_message.set_token('P_API_NAME',l_api_name);
581 FND_MSG_PUB.add;
582 x_return_status := fnd_api.g_ret_sts_unexp_error;
583 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
584 END update_skills;
585
586
587 /* Procedure to delete the skill levels */
588
589 PROCEDURE delete_skills
590 (P_API_VERSION IN NUMBER,
591 P_INIT_MSG_LIST IN VARCHAR2,
592 P_COMMIT IN VARCHAR2,
593 P_SKILL_LEVEL_ID IN JTF_RS_SKILL_LEVELS_B.skill_level_id%TYPE,
594 P_OBJECT_VERSION_NUM IN JTF_RS_SKILL_LEVELS_B.OBJECT_VERSION_NUMBER%TYPE,
595 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
596 X_MSG_COUNT OUT NOCOPY NUMBER,
597 X_MSG_DATA OUT NOCOPY VARCHAR2
598 )IS
599
600
601 CURSOR chk_skill_exist_cur(ll_skill_level_id JTF_RS_SKILL_LEVELS_B.skill_level_id%TYPE)
602 IS
603 SELECT skill_level_id
604 FROM JTF_RS_SKILL_LEVELS_B
605 WHERE skill_level_id = ll_skill_level_id;
606
607 chk_skill_exist_rec chk_skill_exist_cur%rowtype;
608
609 l_skill_level_id JTF_RS_SKILL_LEVELS_B.SKILL_LEVEL_ID%TYPE := p_skill_level_id;
610
611 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SKILLS';
612 l_api_version CONSTANT NUMBER := 1.0;
613 l_bind_data_id number;
614
615 l_date Date;
616 l_user_id Number;
617 l_login_id Number;
618
619
620 l_return_code VARCHAR2(100);
621 l_count NUMBER;
622 l_data VARCHAR2(200);
623
624 l_return_status VARCHAR2(200);
625 l_msg_count NUMBER;
626 l_msg_data VARCHAR2(200);
627
628 BEGIN
629 --Standard Start of API SAVEPOINT
630 SAVEPOINT SKILL_LEVELS_SP;
631
632 x_return_status := fnd_api.g_ret_sts_success;
633
634 --Standard Call to check API compatibility
635 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
636 THEN
637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
638 END IF;
639
640 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
641 IF FND_API.To_boolean(P_INIT_MSG_LIST)
642 THEN
643 FND_MSG_PUB.Initialize;
644 END IF;
645
646 OPEN chk_skill_exist_cur(l_skill_level_id);
647 FETCH chk_skill_exist_cur INTO chk_skill_exist_rec;
648
649 IF (chk_skill_exist_cur%FOUND)
650 THEN
651 CLOSE chk_skill_exist_cur;
652
653 BEGIN
654
655 jtf_rs_skill_levels_pkg.lock_row(
656 X_SKILL_LEVEL_ID => l_skill_level_id,
657 X_OBJECT_VERSION_NUMBER => p_object_version_num
658 );
659
660 EXCEPTION
661
662 WHEN OTHERS THEN
663 x_return_status := fnd_api.g_ret_sts_error;
664 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
665 fnd_msg_pub.add;
666 RAISE fnd_api.g_exc_error;
667
668 END;
669
670 JTF_RS_SKILL_LEVELS_PKG.DELETE_ROW(
671 X_SKILL_LEVEL_ID => l_skill_level_id);
672
673 ELSIF (chk_skill_exist_cur%notfound) THEN
674 CLOSE chk_skill_exist_cur;
675 x_return_status := fnd_api.g_ret_sts_error;
676 fnd_message.set_name ('JTF', 'JTF_RS_SKILL_LEVEL_ID_INVALID');
677 FND_MSG_PUB.add;
678 RAISE fnd_api.g_exc_error;
679
680 END IF;
681
682 IF fnd_api.to_boolean (p_commit)
683 THEN
684 COMMIT WORK;
685 END IF;
686
687 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
688
689 EXCEPTION
690 WHEN fnd_api.g_exc_unexpected_error
691 THEN
692 ROLLBACK TO SKILL_LEVELS_SP;
693 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
694 WHEN fnd_api.g_exc_error
695 THEN
696 ROLLBACK TO SKILL_LEVELS_SP;
697 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
698 WHEN OTHERS
699 THEN
700 ROLLBACK TO SKILL_LEVELS_SP;
701 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
702 fnd_message.set_token('P_SQLCODE',SQLCODE);
703 fnd_message.set_token('P_SQLERRM',SQLERRM);
704 fnd_message.set_token('P_API_NAME',l_api_name);
705 FND_MSG_PUB.add;
706 x_return_status := fnd_api.g_ret_sts_unexp_error;
707 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
708
709 END delete_skills;
710
711 END JTF_RS_SKILL_LEVELS_PVT;