[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_SKILLS_PVT
Source
1 PACKAGE BODY JTF_RS_RESOURCE_SKILLS_PVT AS
2 /* $Header: jtfrsekb.pls 120.0 2005/05/11 08:19:53 appldev ship $ */
3
4 /*****************************************************************************************
5 Its main procedures are as following:
6 Create resource skills
7 Update resource skills
8 Delete resource skills
9 Calls to these procedures will invoke procedures from JTF_RS_RESOURCE_SKILLS_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_RESOURCE_SKILLS_PVT';
15 G_NAME VARCHAR2(240);
16
17 /* Procedure to create the resource skills
18 based on input values passed by calling routines. */
19
20 PROCEDURE create_resource_skills
21 (P_API_VERSION IN NUMBER,
22 P_INIT_MSG_LIST IN VARCHAR2,
23 P_COMMIT IN VARCHAR2,
24 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
25 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
26 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
27 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
28 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
29 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
30 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
31 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
32 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
33 P_COMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
34 P_SUBCOMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
35 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
36 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
37 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
38 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
39 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
40 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
41 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
42 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
43 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
44 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
45 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
46 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
47 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
48 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
49 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
50 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
51 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
52 X_MSG_COUNT OUT NOCOPY NUMBER,
53 X_MSG_DATA OUT NOCOPY VARCHAR2,
54 X_RESOURCE_SKILL_ID OUT NOCOPY JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE
55 )IS
56 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_SKILLS';
57 l_api_version CONSTANT NUMBER := 1.0;
58
59 l_object_version_number number ;
60
61 l_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE;
62
63 l_return_code VARCHAR2(100);
64 l_count NUMBER;
65 l_data VARCHAR2(200);
66
67 l_return_status VARCHAR2(200);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(200);
70 l_rowid VARCHAR2(200);
71
72 -- Check resource_id is valid
73 CURSOR resource_id_cur(ll_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
74 IS
75 SELECT resource_id
76 FROM JTF_RS_RESOURCE_EXTNS
77 WHERE resource_id = ll_resource_id ;
78
79 resource_id_rec resource_id_cur%rowtype;
80
81 -- Check Skill_level_id is valid
82 CURSOR skill_level_id_cur(ll_skill_level_id JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE)
83 IS
84 SELECT skill_level_id
85 FROM JTF_RS_SKILL_LEVELS_B
86 WHERE skill_level_id = ll_skill_level_id;
87
88 skill_level_id_rec skill_level_id_cur%rowtype;
89
90 -- Check category_id is valid
91 CURSOR category_id_cur(ll_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE)
92 IS
93 SELECT category_id
94 FROM JTF_RS_ITEM_CATEGORIES_V
95 WHERE category_id = ll_category_id
96 AND nvl(enabled_flag, 'Y') <> 'N'
97 AND trunc(sysdate) < nvl(disable_date, sysdate);
98
99 category_id_rec category_id_cur%rowtype;
100
101 -- Check category_id is valid
102 CURSOR category_catset_id_cur(ll_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
103 ll_catset_id NUMBER )
104 IS
105 SELECT cat.category_id
106 FROM JTF_RS_ITEM_CATEGORIES_V cat,
107 mtl_category_set_valid_cats ic
108 WHERE cat.category_id = ll_category_id
109 AND nvl(cat.enabled_flag, 'Y') <> 'N'
110 AND trunc(sysdate) < nvl(cat.disable_date, sysdate)
111 AND cat.category_id = ic.category_id
112 AND ic.category_set_id = ll_catset_id ;
113
114 category_catset_id_rec category_catset_id_cur%rowtype;
115
116 -- Check subcategory is valid
117 CURSOR subcategory_cur(ll_subcategory JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE)
118 IS
119 SELECT lookup_code, meaning
120 FROM FND_LOOKUPS
121 WHERE lookup_type = 'JTF_RS_SKILL_CAT_TYPE'
122 AND enabled_flag = 'Y';
123
124 subcategory_rec subcategory_cur%rowtype;
125
126 -- Check product_id is valid
127 CURSOR product_id_cur(ll_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
128 ll_product_org_id JTF_RS_RESOURCE_SKILLS.product_org_id%TYPE)
129 IS
130 SELECT product_id
131 FROM JTF_RS_PRODUCTS_V
132 WHERE product_id = ll_product_id
133 AND product_org_id = ll_product_org_id
134 AND enabled_flag = 'Y';
135
136 product_id_rec product_id_cur%rowtype;
137
138 -- Check product_id and category_id combination is valid
139 -- only if category_id is passed otherwise do not validate against it
140 CURSOR product_cat_id_cur(lpco_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
141 lpco_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
142 lpco_product_org_id JTF_RS_RESOURCE_SKILLS.product_org_id%TYPE)
143 IS
144 SELECT p.product_id
145 FROM JTF_RS_PRODUCTS_V p
146 WHERE p.product_id = lpco_product_id
147 AND p.product_org_id = lpco_product_org_id
148 AND p.enabled_flag = 'Y'
149 AND EXISTS ( SELECT null FROM MTL_ITEM_CATEGORIES c
150 WHERE p.product_id = c.inventory_item_id
151 AND p.product_org_id = c.organization_id
152 AND c.category_id = lpco_category_id) ;
153
154 product_cat_id_rec product_cat_id_cur%rowtype;
155
156 -- Check component_id is valid
157 CURSOR component_id_cur(ll_component_id JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
158 ll_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
159 ll_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE)
160 IS
161 SELECT component_id, product_id
162 FROM JTF_RS_COMPONENTS_V
163 WHERE component_id = ll_component_id
164 AND product_id = ll_product_id
165 AND product_org_id = ll_product_org_id ;
166
167 component_id_rec component_id_cur%rowtype;
168
169 -- Check product, problem_code is valid
170 type prod_prob_code_cur_type is ref cursor;
171 prod_prob_code_cur prod_prob_code_cur_type;
172 l_problem_code JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE;
173 l_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE;
174
175 -- Check platform_id is valid
176 CURSOR platform_id_cur(ll_platform_id JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
177 ll_platform_org_id JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE)
178 IS
179 SELECT platform_id
180 FROM JTF_RS_PLATFORMS_V
181 WHERE platform_id = ll_platform_id
182 AND platform_org_id = ll_platform_org_id;
183
184 platform_id_rec platform_id_cur%rowtype;
185
186 -- Check platform_id and category_id combination is valid
187 CURSOR platform_cat_id_cur(ll_platform_id JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
188 ll_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
189 ll_platform_org_id JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE)
190 IS
191 SELECT platform_id
192 FROM JTF_RS_PLATFORMS_V
193 WHERE platform_id = ll_platform_id
194 AND platform_org_id = ll_platform_org_id
195 AND category_id = ll_category_id ;
196
197 platform_cat_id_rec platform_cat_id_cur%rowtype;
198
199 -- Check problem_code is valid
200 CURSOR problem_code_cur(ll_problem_code JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE)
201 IS
202 SELECT problem_code
203 FROM JTF_RS_PROBLEM_CODES_V
204 WHERE problem_code = ll_problem_code
205 AND enabled_flag = 'Y'
206 AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
207 nvl(end_date_active, sysdate);
208
209 problem_code_rec problem_code_cur%rowtype;
210
211 -- Duplicate cursor check for category
212 CURSOR category_dup_cur (lpcat_resource_id JTF_RS_RESOURCE_SKILLS.resource_id%TYPE,
213 lpcat_category_id JTF_RS_RESOURCE_SKILLS.category_id%TYPE)
214 IS
215 SELECT resource_skill_id
216 FROM JTF_RS_RESOURCE_SKILLS
217 WHERE resource_id = lpcat_resource_id
218 AND nvl(category_id,-99) = nvl(lpcat_category_id, -99)
219 AND subcategory is NULL;
220
221 category_dup_rec category_dup_cur%rowtype;
222
223 -- Duplicate cursor check for product
224 CURSOR product_dup_cur (lpd_resource_id JTF_RS_RESOURCE_SKILLS.resource_id%TYPE,
225 lpd_category_id JTF_RS_RESOURCE_SKILLS.category_id%TYPE,
226 lpd_subcategory JTF_RS_RESOURCE_SKILLS.subcategory%TYPE,
227 lpd_product_id JTF_RS_RESOURCE_SKILLS.product_id%TYPE,
228 lpd_component_id JTF_RS_RESOURCE_SKILLS.component_id%TYPE,
229 lpd_problem_code JTF_RS_RESOURCE_SKILLS.problem_code%TYPE,
230 lpd_product_org_id JTF_RS_RESOURCE_SKILLS.product_org_id%TYPE)
231 IS
232 SELECT resource_skill_id
233 FROM JTF_RS_RESOURCE_SKILLS
234 WHERE resource_id = lpd_resource_id
235 AND subcategory = lpd_subcategory
236 AND product_id = lpd_product_id
237 AND product_org_id = lpd_product_org_id
238 -- AND nvl(category_id,-99) = nvl(lpd_category_id, -99)
239 AND nvl(problem_code,-99) = nvl(lpd_problem_code, -99)
240 AND nvl(component_id, -99) = nvl(lpd_component_id, -99);
241
242 product_dup_rec product_dup_cur%rowtype;
243
244 -- Duplicate cursor check for platform
245 CURSOR platform_dup_cur (lpt_resource_id JTF_RS_RESOURCE_SKILLS.resource_id%TYPE,
246 lpt_category_id JTF_RS_RESOURCE_SKILLS.category_id%TYPE,
247 lpt_subcategory JTF_RS_RESOURCE_SKILLS.subcategory%TYPE,
248 lpt_platform_id JTF_RS_RESOURCE_SKILLS.platform_id%TYPE,
249 lpt_platform_org_id JTF_RS_RESOURCE_SKILLS.platform_org_id%TYPE)
250 IS
251 SELECT resource_skill_id
252 FROM JTF_RS_RESOURCE_SKILLS
253 WHERE resource_id = lpt_resource_id
254 AND subcategory = lpt_subcategory
255 AND platform_id = lpt_platform_id
256 AND platform_org_id = lpt_platform_org_id
257 AND nvl(category_id,-99) = nvl(lpt_category_id, -99) ;
258
259 platform_dup_rec platform_dup_cur%rowtype;
260
261 -- Duplicate cursor check for problem_code
262 CURSOR problem_code_dup_cur (lpc_resource_id JTF_RS_RESOURCE_SKILLS.resource_id%TYPE,
263 lpc_category_id JTF_RS_RESOURCE_SKILLS.category_id%TYPE,
264 lpc_subcategory JTF_RS_RESOURCE_SKILLS.subcategory%TYPE,
265 lpc_problem_code JTF_RS_RESOURCE_SKILLS.problem_code%TYPE)
266 IS
267 SELECT resource_skill_id
268 FROM JTF_RS_RESOURCE_SKILLS
269 WHERE resource_id = lpc_resource_id
270 AND subcategory = lpc_subcategory
271 AND problem_code = lpc_problem_code
272 AND nvl(category_id, -99) = nvl(lpc_category_id, -99) ;
273
274 problem_code_dup_rec problem_code_dup_cur%rowtype;
275
276 l_date Date;
277 l_user_id Number;
278 l_login_id Number;
279
280 l_go_ahead VARCHAR2(3) := 'YES' ;
281 l_product_org_id number ;
282 l_platform_org_id number ;
283 l_catset number ;
284
285 BEGIN
286 --Standard Start of API SAVEPOINT
287 SAVEPOINT RESOURCE_SKILLS_SP;
288
289 x_return_status := fnd_api.g_ret_sts_success;
290
291 --Standard Call to check API compatibility
292 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
293 THEN
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296
297 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
298 IF FND_API.To_boolean(P_INIT_MSG_LIST)
299 THEN
300 FND_MSG_PUB.Initialize;
301 END IF;
302
303 --GET USER ID AND SYSDATE
304 l_date := sysdate;
305 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
306 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
307
308 l_catset := to_number(fnd_profile.value('CS_SR_DEFAULT_CATEGORY_SET'));
309
310 -------------------------------------------------------------------
311
312 -- Check if subcategory passed is valid
313
314 IF (p_subcategory IS NOT NULL) THEN
315
316 OPEN subcategory_cur(p_subcategory);
317 FETCH subcategory_cur into subcategory_rec;
318 IF (subcategory_cur%NOTFOUND) THEN
319 x_return_status := fnd_api.g_ret_sts_error;
320 fnd_message.set_name ('JTF', 'JTF_RS_SUBCATEGORY_INVALID');
321 FND_MSG_PUB.add;
322 RAISE fnd_api.g_exc_error;
323 END IF;
324 CLOSE subcategory_cur;
325
326 END IF;
327
328 -----------------------------------------------------------------------
329
330 -- check for mutual exclusion and Null value of parameters for subcategory
331
332 IF (p_subcategory = 'PRODUCT')
333 THEN
334 IF ((p_product_id IS NULL and p_category_id is NULL)
335 OR (p_platform_id IS NOT NULL)
336 OR (p_platform_org_id IS NOT NULL)
337 /*OR (p_problem_code IS NOT NULL)*/)
338 THEN
339 x_return_status := fnd_api.g_ret_sts_error;
340 fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
341 FND_MSG_PUB.add;
342 RAISE fnd_api.g_exc_error;
343 END IF;
344
345 -- If user doesn't pass org_id determine it and set it
346 IF (p_product_org_id IS NULL) THEN
347 l_product_org_id := nvl(JTF_RESOURCE_UTL.GET_INVENTORY_ORG_ID, -1);
348 l_platform_org_id := null ;
349 ELSE
350 l_product_org_id := p_product_org_id ;
351 l_platform_org_id := null ;
352 END IF;
353
354 ELSIF (p_subcategory = 'PLATFORM')
355 THEN
356 IF ((p_platform_id IS NULL)
357 OR (p_product_id IS NOT NULL)
358 OR (p_product_org_id IS NOT NULL)
359 OR (p_component_id IS NOT NULL)
360 OR (p_problem_code IS NOT NULL))
361 THEN
362 x_return_status := fnd_api.g_ret_sts_error;
363 fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
364 FND_MSG_PUB.add;
368 -- If user doesn't pass org_id determine it and set it
365 RAISE fnd_api.g_exc_error;
366 END IF;
367
369 IF (p_platform_org_id IS NULL) THEN
370 l_product_org_id := null;
371 l_platform_org_id := nvl(JTF_RESOURCE_UTL.GET_INVENTORY_ORG_ID, -1);
372 ELSE
373 l_product_org_id := null;
374 l_platform_org_id := p_platform_org_id;
375 END IF;
376
377 ELSIF (p_subcategory = 'PROBLEM_CODE')
378 THEN
379 IF ((p_problem_code IS NULL)
380 OR (p_product_id IS NOT NULL)
381 OR (p_product_org_id IS NOT NULL)
382 OR (p_component_id IS NOT NULL)
383 OR (p_platform_id IS NOT NULL)
384 OR (p_platform_org_id IS NOT NULL))
385 THEN
386 x_return_status := fnd_api.g_ret_sts_error;
387 fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
388 FND_MSG_PUB.add;
389 RAISE fnd_api.g_exc_error;
390 END IF;
391 ELSE
392 IF (p_subcategory IS NOT NULL) THEN
393 x_return_status := fnd_api.g_ret_sts_error;
394 fnd_message.set_name ('JTF', 'JTF_RS_SUBCATEGORY_INVALID');
395 FND_MSG_PUB.add;
396 RAISE fnd_api.g_exc_error;
397 END IF;
398 END IF;
399
400 -----------------------------------------------------------------------
401
402 -- Check if Resource_id passed is valid
403
404 OPEN resource_id_cur(p_resource_id);
405 FETCH resource_id_cur into resource_id_rec;
406 IF (resource_id_cur%NOTFOUND) THEN
407 x_return_status := fnd_api.g_ret_sts_error;
408 fnd_message.set_name ('JTF', 'JTF_RS_RES_ID_INVALID');
409 FND_MSG_PUB.add;
410 RAISE fnd_api.g_exc_error;
411 END IF;
412 CLOSE resource_id_cur;
413
414 -----------------------------------------------------------------------
415
416 -- Check if skill_level_id passed is valid
417
418 OPEN skill_level_id_cur(p_skill_level_id);
419 FETCH skill_level_id_cur into skill_level_id_rec;
420 IF (skill_level_id_cur%NOTFOUND) THEN
421 x_return_status := fnd_api.g_ret_sts_error;
422 fnd_message.set_name ('JTF', 'JTF_RS_SKILL_LEVEL_ID_INVALID');
423 FND_MSG_PUB.add;
424 RAISE fnd_api.g_exc_error;
425 END IF;
426 CLOSE skill_level_id_cur;
427
428 -----------------------------------------------------------------------
429
430 -- Check if category_id passed is valid
431 IF (p_category_id IS NOT NULL) THEN
432 IF (l_catset IS NULL) THEN
433 OPEN category_id_cur(p_category_id);
434 FETCH category_id_cur into category_id_rec;
435 IF (category_id_cur%NOTFOUND) THEN
436 x_return_status := fnd_api.g_ret_sts_error;
437 fnd_message.set_name ('JTF', 'JTF_RS_CATEGORY_ID_INVALID');
438 FND_MSG_PUB.add;
439 RAISE fnd_api.g_exc_error;
440 END IF;
441 CLOSE category_id_cur;
442 ELSE
443 OPEN category_catset_id_cur(p_category_id, l_catset);
444 FETCH category_catset_id_cur into category_catset_id_rec;
445 IF (category_catset_id_cur%NOTFOUND) THEN
446 x_return_status := fnd_api.g_ret_sts_error;
447 fnd_message.set_name ('JTF', 'JTF_RS_CATEGORY_ID_INVALID');
448 FND_MSG_PUB.add;
449 RAISE fnd_api.g_exc_error;
450 END IF;
451 CLOSE category_catset_id_cur;
452 END IF;
453 END IF;
454
455 -----------------------------------------------------------------------
456
457 -- Check if product_id / component_id / platform_id / problem_code passed is valid
458
459 IF (p_subcategory = 'PRODUCT') THEN
460 IF (p_category_id IS NULL) THEN
461 OPEN product_id_cur(p_product_id, l_product_org_id);
462 FETCH product_id_cur into product_id_rec;
463 IF (product_id_cur%NOTFOUND) THEN
464 x_return_status := fnd_api.g_ret_sts_error;
465 fnd_message.set_name ('JTF', 'JTF_RS_PRODUCT_ID_INVALID');
466 FND_MSG_PUB.add;
467 RAISE fnd_api.g_exc_error;
468 END IF;
469 CLOSE product_id_cur;
470 ELSE
471 OPEN product_cat_id_cur(p_product_id, p_category_id, l_product_org_id);
472 FETCH product_cat_id_cur into product_cat_id_rec;
473 IF (product_cat_id_cur%NOTFOUND) THEN
474 x_return_status := fnd_api.g_ret_sts_error;
475 fnd_message.set_name ('JTF', 'JTF_RS_PROD_CAT_ID_INVALID');
476 FND_MSG_PUB.add;
477 RAISE fnd_api.g_exc_error;
478 END IF;
479 CLOSE product_cat_id_cur;
480 END IF;
481
482
483 IF (p_component_id IS NOT NULL AND p_problem_code IS NOT NULL) THEN
484 x_return_status := fnd_api.g_ret_sts_error;
485 fnd_message.set_name ('JTF', 'JTF_RS_COMP_PROB_CODE_MUTEX');
486 FND_MSG_PUB.add;
487 RAISE fnd_api.g_exc_error;
488 END IF;
489
490 IF (p_component_id IS NOT NULL) THEN
491 OPEN component_id_cur(p_component_id, p_product_id, l_product_org_id);
492 FETCH component_id_cur into component_id_rec;
493 IF (component_id_cur%NOTFOUND) THEN
497 RAISE fnd_api.g_exc_error;
494 x_return_status := fnd_api.g_ret_sts_error;
495 fnd_message.set_name ('JTF', 'JTF_RS_COMPONENT_ID_INVALID');
496 FND_MSG_PUB.add;
498 END IF;
499 CLOSE component_id_cur;
500 END IF;
501
502 if (p_problem_code IS NOT NULL) THEN
503 OPEN prod_prob_code_cur
504 FOR
505 ' SELECT problem_code, inventory_item_id
506 FROM CS_SR_PROB_CODE_MAPPING
507 WHERE problem_code = :1
508 AND inventory_item_id = :2
509 AND organization_id = :3
510 AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
511 nvl(end_date_active, sysdate) '
512 USING p_problem_code, p_product_id, l_product_org_id;
513
514 FETCH prod_prob_code_cur into l_problem_code, l_product_id;
515 IF (prod_prob_code_cur%NOTFOUND) THEN
516 x_return_status := fnd_api.g_ret_sts_error;
517 fnd_message.set_name ('JTF', 'JTF_RS_PROD_PROB_CODE_INVALID');
518 FND_MSG_PUB.add;
519 RAISE fnd_api.g_exc_error;
520 END IF;
521 END IF;
522
523 ELSIF (p_subcategory = 'PLATFORM') THEN
524 IF (p_category_id IS NULL) THEN
525 OPEN platform_id_cur(p_platform_id, l_platform_org_id);
526 FETCH platform_id_cur into platform_id_rec;
527 IF (platform_id_cur%NOTFOUND) THEN
528 x_return_status := fnd_api.g_ret_sts_error;
529 fnd_message.set_name ('JTF', 'JTF_RS_PLATFORM_ID_INVALID');
530 FND_MSG_PUB.add;
531 RAISE fnd_api.g_exc_error;
532 END IF;
533 CLOSE platform_id_cur;
534 ELSE
535 OPEN platform_cat_id_cur(p_platform_id, p_category_id, l_platform_org_id);
536 FETCH platform_cat_id_cur into platform_cat_id_rec;
537 IF (platform_cat_id_cur%NOTFOUND) THEN
538 x_return_status := fnd_api.g_ret_sts_error;
539 fnd_message.set_name ('JTF', 'JTF_RS_PLAT_CAT_ID_INVALID');
540 FND_MSG_PUB.add;
541 RAISE fnd_api.g_exc_error;
542 END IF;
543 CLOSE platform_cat_id_cur;
544 END IF;
545
546 ELSIF (p_subcategory = 'PROBLEM_CODE') THEN
547 OPEN problem_code_cur(p_problem_code);
548 FETCH problem_code_cur into problem_code_rec;
549 IF (problem_code_cur%NOTFOUND) THEN
550 x_return_status := fnd_api.g_ret_sts_error;
551 fnd_message.set_name ('JTF', 'JTF_RS_PROBLEM_CODE_INVALID');
552 FND_MSG_PUB.add;
553 RAISE fnd_api.g_exc_error;
554 END IF;
555 CLOSE problem_code_cur;
556 END IF;
557
558 -----------------------------------------------------------------------
559
560 -- Do Duplicate Record Check
561 IF (p_subcategory = 'PRODUCT') THEN
562
563 OPEN product_dup_cur(p_resource_id, p_category_id, p_subcategory, p_product_id, p_component_id, p_problem_code, l_product_org_id);
564 FETCH product_dup_cur into product_dup_rec;
565 IF (product_dup_cur%NOTFOUND) THEN
566 l_go_ahead := 'YES';
567 ELSE
568 l_go_ahead := 'NO';
569 x_return_status := fnd_api.g_ret_sts_error;
570 fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
571 FND_MSG_PUB.add;
572 RAISE fnd_api.g_exc_error;
573 END IF;
574 CLOSE product_dup_cur;
575 ELSIF (p_subcategory = 'PLATFORM') THEN
576 OPEN platform_dup_cur(p_resource_id, p_category_id, p_subcategory, p_platform_id, l_platform_org_id);
577 FETCH platform_dup_cur into platform_dup_rec;
578 IF (platform_dup_cur%NOTFOUND) THEN
579 l_go_ahead := 'YES';
580 ELSE
581 l_go_ahead := 'NO';
582 x_return_status := fnd_api.g_ret_sts_error;
583 fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
584 FND_MSG_PUB.add;
585 RAISE fnd_api.g_exc_error;
586 END IF;
587 CLOSE platform_dup_cur;
588 ELSIF (p_subcategory = 'PROBLEM_CODE') THEN
589 OPEN problem_code_dup_cur(p_resource_id, p_category_id, p_subcategory, p_problem_code);
590 FETCH problem_code_dup_cur into problem_code_dup_rec;
591 IF (problem_code_dup_cur%NOTFOUND) THEN
592 l_go_ahead := 'YES';
593 ELSE
594 l_go_ahead := 'NO';
595 x_return_status := fnd_api.g_ret_sts_error;
596 fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
597 FND_MSG_PUB.add;
598 RAISE fnd_api.g_exc_error;
599 END IF;
600 CLOSE problem_code_dup_cur;
601 ELSE
602 IF (p_subcategory IS NULL and p_category_id is NOT NULL) THEN
603
604 OPEN category_dup_cur(p_resource_id, p_category_id);
605 FETCH category_dup_cur into category_dup_rec;
606 IF (category_dup_cur%NOTFOUND) THEN
607 l_go_ahead := 'YES';
608 ELSE
609 l_go_ahead := 'NO';
610 x_return_status := fnd_api.g_ret_sts_error;
611 fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
612 FND_MSG_PUB.add;
613 RAISE fnd_api.g_exc_error;
614 END IF;
615 CLOSE category_dup_cur;
619 l_go_ahead := 'NO';
616 l_go_ahead := 'YES';
617 ELSE
618
620 END IF;
621 END IF;
622 -----------------------------------------------------------------------
623
624 IF (l_go_ahead = 'YES') THEN
625
626 SELECT jtf_rs_resource_skills_s.nextval
627 INTO l_resource_skill_id
628 FROM dual;
629
630 JTF_RS_RESOURCE_SKILLS_PKG.INSERT_ROW(
631 X_ROWID => l_rowid,
632 X_RESOURCE_SKILL_ID => l_resource_skill_id,
633 X_RESOURCE_ID => p_resource_id,
634 X_SKILL_LEVEL_ID => p_SKILL_LEVEL_ID,
635 X_CATEGORY_ID => p_category_id,
636 X_SUBCATEGORY => p_subcategory,
637 X_PRODUCT_ID => p_product_id,
638 X_PRODUCT_ORG_ID => l_product_org_id,
639 X_PLATFORM_ID => p_platform_id,
640 X_PLATFORM_ORG_ID => l_platform_org_id,
641 X_PROBLEM_CODE => p_problem_code,
642 X_COMPONENT_ID => p_component_id,
643 X_SUBCOMPONENT_ID => p_subcomponent_id,
644 X_OBJECT_VERSION_NUMBER => l_object_version_number,
645 X_ATTRIBUTE1 => p_attribute1,
646 X_ATTRIBUTE2 => p_attribute2,
647 X_ATTRIBUTE3 => p_attribute3,
648 X_ATTRIBUTE4 => p_attribute4,
649 X_ATTRIBUTE5 => p_attribute5,
650 X_ATTRIBUTE6 => p_attribute6,
651 X_ATTRIBUTE7 => p_attribute7,
652 X_ATTRIBUTE8 => p_attribute8,
653 X_ATTRIBUTE9 => p_attribute9,
654 X_ATTRIBUTE10 => p_attribute10,
655 X_ATTRIBUTE11 => p_attribute11,
656 X_ATTRIBUTE12 => p_attribute12,
657 X_ATTRIBUTE13 => p_attribute13,
658 X_ATTRIBUTE14 => p_attribute14,
659 X_ATTRIBUTE15 => p_attribute15,
660 X_ATTRIBUTE_CATEGORY => p_attribute_category,
661 X_CREATION_DATE => sysdate,
662 X_CREATED_BY => l_user_id,
663 X_LAST_UPDATE_DATE => sysdate,
664 X_LAST_UPDATED_BY => l_user_id,
665 X_LAST_UPDATE_LOGIN => 0);
666
667 -- return resource_skill_id
668 x_resource_skill_id := l_resource_skill_id;
669
670 ELSE
671 fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
672 FND_MSG_PUB.add;
673 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
674 raise fnd_api.g_exc_error;
675 END IF;
676
677
678 --standard commit
679 IF fnd_api.to_boolean (p_commit)
680 THEN
681 COMMIT WORK;
682 END IF;
683
684 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
685
686 EXCEPTION
687 WHEN fnd_api.g_exc_unexpected_error
688 THEN
689 ROLLBACK TO RESOURCE_SKILLS_SP;
690 x_return_status := fnd_api.g_ret_sts_unexp_error;
691 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
692 WHEN fnd_api.g_exc_error
693 THEN
694 ROLLBACK TO RESOURCE_SKILLS_SP;
695 x_return_status := fnd_api.g_ret_sts_error;
696 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
697 WHEN OTHERS
698 THEN
699 ROLLBACK TO RESOURCE_SKILLS_SP;
700 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
701 fnd_message.set_token('P_SQLCODE',SQLCODE);
702 fnd_message.set_token('P_SQLERRM',SQLERRM);
703 fnd_message.set_token('P_API_NAME', l_api_name);
704 FND_MSG_PUB.add;
705 x_return_status := fnd_api.g_ret_sts_unexp_error;
706 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
707
708 END create_resource_skills;
709
710
711 /* Procedure to update resource skills
712 based on input values passed by calling routines. */
713
714 PROCEDURE update_resource_skills
715 (P_API_VERSION IN NUMBER,
716 P_INIT_MSG_LIST IN VARCHAR2,
717 P_COMMIT IN VARCHAR2,
718 P_RESOURCE_SKILL_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
719 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
720 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
721 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
722 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
723 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
724 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
725 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
729 P_SUBCOMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
726 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
727 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
728 P_COMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
730 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
731 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
732 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
733 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
734 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
735 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
736 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
737 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
738 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
739 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
740 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
741 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
742 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
743 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
744 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
745 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
746 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
747 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
748 X_MSG_COUNT OUT NOCOPY NUMBER,
749 X_MSG_DATA OUT NOCOPY VARCHAR2
750 )IS
751 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_SKILLS';
752 l_api_version CONSTANT NUMBER := 1.0;
753
754 l_return_code VARCHAR2(100);
755 l_count NUMBER;
756 l_data VARCHAR2(200);
757
758
759 L_ATTRIBUTE1 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE;
760 L_ATTRIBUTE2 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE;
761 L_ATTRIBUTE3 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE;
762 L_ATTRIBUTE4 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE;
763 L_ATTRIBUTE5 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE;
764 L_ATTRIBUTE6 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE;
765 L_ATTRIBUTE7 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE;
766 L_ATTRIBUTE8 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE;
767 L_ATTRIBUTE9 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE;
768 L_ATTRIBUTE10 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE;
769 L_ATTRIBUTE11 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE;
770 L_ATTRIBUTE12 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE;
771 L_ATTRIBUTE13 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE;
772 L_ATTRIBUTE14 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE;
773 L_ATTRIBUTE15 JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE;
774 L_ATTRIBUTE_CATEGORY JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE;
775
776
777 CURSOR resource_skills_cur(ll_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE)
778 IS
779 SELECT
780 RESOURCE_SKILL_ID,
781 RESOURCE_ID,
782 SKILL_LEVEL_ID,
783 CATEGORY_ID,
784 SUBCATEGORY,
785 PRODUCT_ID,
786 PRODUCT_ORG_ID,
787 PLATFORM_ID,
788 PLATFORM_ORG_ID,
789 PROBLEM_CODE,
790 COMPONENT_ID,
791 SUBCOMPONENT_ID,
792 OBJECT_VERSION_NUMBER,
793 ATTRIBUTE1,
794 ATTRIBUTE2,
795 ATTRIBUTE3,
796 ATTRIBUTE4,
797 ATTRIBUTE5,
798 ATTRIBUTE6,
799 ATTRIBUTE7,
800 ATTRIBUTE8,
801 ATTRIBUTE9,
802 ATTRIBUTE10,
803 ATTRIBUTE11,
804 ATTRIBUTE12,
805 ATTRIBUTE13,
806 ATTRIBUTE14,
807 ATTRIBUTE15,
808 ATTRIBUTE_CATEGORY,
809 CREATED_BY,
810 CREATION_DATE,
811 LAST_UPDATED_BY,
812 LAST_UPDATE_DATE,
813 LAST_UPDATE_LOGIN
814 FROM jtf_rs_resource_skills
815 WHERE resource_skill_id = ll_resource_skill_id;
816
817 resource_skills_rec resource_skills_cur%rowtype;
818
819 l_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE := p_resource_skill_id;
820 l_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE := p_resource_id;
821 l_skill_level_id JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE := p_skill_level_id;
822 l_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE := p_category_id;
823 l_subcategory JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE := p_subcategory;
824 l_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE := p_product_id;
825 l_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE := p_product_org_id;
826 l_platform_id JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE := p_platform_id;
827 l_platform_org_id JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE := p_platform_org_id;
828 l_problem_code JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE := p_problem_code;
829 l_component_id JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE := p_component_id;
830 l_subcomponent_id JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE := p_subcomponent_id;
831
832 l_object_version_number JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE := p_object_version_num;
833
834 l_return_status VARCHAR2(200);
835 l_msg_count NUMBER;
836 l_msg_data VARCHAR2(200);
840 l_user_id Number;
837 l_rowid VARCHAR2(200);
838
839 l_date Date;
841 l_login_id Number;
842
843 BEGIN
844 --Standard Start of API SAVEPOINT
845 SAVEPOINT RESOURCE_SKILLS_SP;
846
847 x_return_status := fnd_api.g_ret_sts_success;
848
849 --Standard Call to check API compatibility
850 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
851 THEN
852 RAISE FND_API.G_EXC_ERROR;
853 END IF;
854
855 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
856 IF FND_API.To_boolean(P_INIT_MSG_LIST)
857 THEN
858 FND_MSG_PUB.Initialize;
859 END IF;
860
861
862 --GET USER ID AND SYSDATE
863 l_date := sysdate;
864 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
865 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
866
867 OPEN resource_skills_cur(l_resource_skill_id);
868 FETCH resource_skills_cur INTO resource_skills_rec;
869
870 IF (resource_skills_cur%found) THEN
871
872 IF (p_resource_id = FND_API.G_MISS_NUM)
873 THEN
874 l_resource_id := resource_skills_rec.resource_id;
875 ELSE
876 l_resource_id := p_resource_id;
877 END IF;
878
879 IF (p_SKILL_LEVEL_ID = FND_API.G_MISS_NUM)
880 THEN
881 l_SKILL_LEVEL_ID := resource_skills_rec.SKILL_LEVEL_ID;
882 ELSE
883 l_SKILL_LEVEL_ID := p_SKILL_LEVEL_ID;
884 END IF;
885
886 IF (p_category_id = FND_API.G_MISS_NUM)
887 THEN
888 l_category_id := resource_skills_rec.category_id;
889 ELSE
890 l_category_id := p_category_id;
891 END IF;
892
893 IF (p_subcategory = FND_API.G_MISS_CHAR)
894 THEN
895 l_subcategory := resource_skills_rec.subcategory;
896 ELSE
897 l_subcategory := p_subcategory;
898 END IF;
899
900 IF (p_product_id = FND_API.G_MISS_NUM)
901 THEN
902 l_product_id := resource_skills_rec.product_id;
903 ELSE
904 l_product_id := p_product_id;
905 END IF;
906
907 IF (p_product_org_id = FND_API.G_MISS_NUM)
908 THEN
909 l_product_org_id := resource_skills_rec.product_org_id;
910 ELSE
911 l_product_org_id := p_product_org_id;
912 END IF;
913
914 IF (p_platform_id = FND_API.G_MISS_NUM)
915 THEN
916 l_platform_id := resource_skills_rec.platform_id;
917 ELSE
918 l_platform_id := p_platform_id;
919 END IF;
920
921 IF (p_platform_org_id = FND_API.G_MISS_NUM)
922 THEN
923 l_platform_org_id := resource_skills_rec.platform_org_id;
924 ELSE
925 l_platform_org_id := p_platform_org_id;
926 END IF;
927
928 IF (p_problem_code = FND_API.G_MISS_CHAR)
929 THEN
930 l_problem_code := resource_skills_rec.problem_code;
931 ELSE
932 l_problem_code := p_problem_code;
933 END IF;
934
935 IF (p_component_id = FND_API.G_MISS_NUM)
936 THEN
937 l_component_id := resource_skills_rec.component_id;
938 ELSE
939 l_component_id := p_component_id;
940 END IF;
941
942 IF (p_subcomponent_id = FND_API.G_MISS_NUM)
943 THEN
944 l_subcomponent_id := resource_skills_rec.subcomponent_id;
945 ELSE
946 l_subcomponent_id := p_subcomponent_id;
947 END IF;
948
949 IF(p_attribute1 = FND_API.G_MISS_CHAR)
950 THEN
951 l_attribute1 := resource_skills_rec.attribute1;
952 ELSE
953 l_attribute1 := p_attribute1;
954 END IF;
955
956 IF(p_attribute2 = FND_API.G_MISS_CHAR)
957 THEN
958 l_attribute2 := resource_skills_rec.attribute2;
959 ELSE
960 l_attribute2 := p_attribute2;
961 END IF;
962
963 IF(p_attribute3 = FND_API.G_MISS_CHAR)
964 THEN
965 l_attribute3 := resource_skills_rec.attribute3;
966 ELSE
967 l_attribute3 := p_attribute3;
968 END IF;
969
970 IF(p_attribute4 = FND_API.G_MISS_CHAR)
971 THEN
972 l_attribute4 := resource_skills_rec.attribute4;
973 ELSE
974 l_attribute4 := p_attribute4;
975 END IF;
976
977 IF(p_attribute5 = FND_API.G_MISS_CHAR)
978 THEN
979 l_attribute5 := resource_skills_rec.attribute5;
980 ELSE
981 l_attribute5 := p_attribute5;
982 END IF;
983
984 IF(p_attribute6 = FND_API.G_MISS_CHAR)
985 THEN
986 l_attribute6 := resource_skills_rec.attribute6;
987 ELSE
988 l_attribute6 := p_attribute6;
989 END IF;
990
991 IF(p_attribute7 = FND_API.G_MISS_CHAR)
992 THEN
993 l_attribute7 := resource_skills_rec.attribute7;
994 ELSE
995 l_attribute7 := p_attribute7;
996 END IF;
997
998 IF(p_attribute8 = FND_API.G_MISS_CHAR)
999 THEN
1000 l_attribute8 := resource_skills_rec.attribute8;
1001 ELSE
1002 l_attribute8 := p_attribute8;
1003 END IF;
1004
1005 IF(p_attribute9 = FND_API.G_MISS_CHAR)
1006 THEN
1010 END IF;
1007 l_attribute9 := resource_skills_rec.attribute9;
1008 ELSE
1009 l_attribute9 := p_attribute9;
1011
1012 IF(p_attribute10 = FND_API.G_MISS_CHAR)
1013 THEN
1014 l_attribute10 := resource_skills_rec.attribute10;
1015 ELSE
1016 l_attribute10 := p_attribute10;
1017 END IF;
1018
1019 IF(p_attribute11 = FND_API.G_MISS_CHAR)
1020 THEN
1021 l_attribute11 := resource_skills_rec.attribute11;
1022 ELSE
1023 l_attribute11 := p_attribute11;
1024 END IF;
1025
1026 IF(p_attribute12 = FND_API.G_MISS_CHAR)
1027 THEN
1028 l_attribute12 := resource_skills_rec.attribute12;
1029 ELSE
1030 l_attribute12 := p_attribute12;
1031 END IF;
1032
1033 IF(p_attribute13 = FND_API.G_MISS_CHAR)
1034 THEN
1035 l_attribute13 := resource_skills_rec.attribute13;
1036 ELSE
1037 l_attribute13 := p_attribute13;
1038 END IF;
1039
1040 IF(p_attribute14 = FND_API.G_MISS_CHAR)
1041 THEN
1042 l_attribute14 := resource_skills_rec.attribute14;
1043 ELSE
1044 l_attribute14 := p_attribute14;
1045 END IF;
1046
1047 IF(p_attribute15 = FND_API.G_MISS_CHAR)
1048 THEN
1049 l_attribute15 := resource_skills_rec.attribute15;
1050 ELSE
1051 l_attribute15 := p_attribute15;
1052 END IF;
1053
1054 IF(p_attribute_category = FND_API.G_MISS_CHAR)
1055 THEN
1056 l_attribute_category := resource_skills_rec.attribute_category;
1057 ELSE
1058 l_attribute_category := p_attribute_category;
1059 END IF;
1060
1061 -------------------------------------------------------------------------------------------
1062 -------------------------------------------------------------------------------------------
1063
1064 BEGIN
1065
1066 jtf_rs_resource_skills_pkg.lock_row(
1067 X_RESOURCE_SKILL_ID => l_resource_skill_id,
1068 X_OBJECT_VERSION_NUMBER => p_object_version_num
1069 );
1070
1071 EXCEPTION
1072
1073 WHEN OTHERS THEN
1074 x_return_status := fnd_api.g_ret_sts_error;
1075 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1076 fnd_msg_pub.add;
1077 RAISE fnd_api.g_exc_error;
1078
1079 END;
1080
1081 l_object_version_number := l_object_version_number +1;
1082
1083 jtf_rs_resource_skills_pkg.update_row(
1084 X_RESOURCE_SKILL_ID => l_resource_skill_id,
1085 X_RESOURCE_ID => l_resource_id,
1086 X_SKILL_LEVEL_ID => l_SKILL_LEVEL_ID,
1087 X_CATEGORY_ID => l_category_id,
1088 X_SUBCATEGORY => l_subcategory,
1089 X_PRODUCT_ID => l_product_id,
1090 X_PRODUCT_ORG_ID => l_product_org_id,
1091 X_PLATFORM_ID => l_platform_id,
1092 X_PLATFORM_ORG_ID => l_platform_org_id,
1093 X_PROBLEM_CODE => l_problem_code,
1094 X_COMPONENT_ID => l_component_id,
1095 X_SUBCOMPONENT_ID => l_subcomponent_id,
1096 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1097 X_ATTRIBUTE1 => l_attribute1,
1098 X_ATTRIBUTE2 => l_attribute2,
1099 X_ATTRIBUTE3 => l_attribute3,
1100 X_ATTRIBUTE4 => l_attribute4,
1101 X_ATTRIBUTE5 => l_attribute5,
1102 X_ATTRIBUTE6 => l_attribute6,
1103 X_ATTRIBUTE7 => l_attribute7,
1104 X_ATTRIBUTE8 => l_attribute8,
1105 X_ATTRIBUTE9 => l_attribute9,
1106 X_ATTRIBUTE10 => l_attribute10,
1107 X_ATTRIBUTE11 => l_attribute11,
1108 X_ATTRIBUTE12 => l_attribute12,
1109 X_ATTRIBUTE13 => l_attribute13,
1110 X_ATTRIBUTE14 => l_attribute14,
1111 X_ATTRIBUTE15 => l_attribute15,
1112 X_ATTRIBUTE_CATEGORY => l_attribute_category,
1113 X_LAST_UPDATE_DATE => l_date,
1114 X_LAST_UPDATED_BY => l_user_id,
1115 X_LAST_UPDATE_LOGIN => l_login_id);
1116
1117
1118
1119 P_OBJECT_VERSION_NUM := l_object_version_number;
1120
1121 ELSIF (resource_skills_cur%notfound) THEN
1122 x_return_status := fnd_api.g_ret_sts_error;
1123 fnd_message.set_name ('JTF', 'JTF_RS_RES_SKILL_ID_INVALID');
1124 FND_MSG_PUB.add;
1125 RAISE fnd_api.g_exc_error;
1126
1127 END IF;
1128
1129 CLOSE resource_skills_cur;
1130
1131 IF fnd_api.to_boolean (p_commit)
1132 THEN
1133 COMMIT WORK;
1134 END IF;
1135
1139 WHEN fnd_api.g_exc_error
1136 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1137
1138 EXCEPTION
1140 THEN
1141 ROLLBACK TO RESOURCE_SKILLS_SP;
1142 x_return_status := fnd_api.g_ret_sts_error;
1143 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1144 WHEN fnd_api.g_exc_unexpected_error
1145 THEN
1146 ROLLBACK TO RESOURCE_SKILLS_SP;
1147 x_return_status := fnd_api.g_ret_sts_unexp_error;
1148 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1149 WHEN OTHERS
1150 THEN
1151 ROLLBACK TO RESOURCE_SKILLS_SP;
1152 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1153 fnd_message.set_token('P_SQLCODE',SQLCODE);
1154 fnd_message.set_token('P_SQLERRM',SQLERRM);
1155 fnd_message.set_token('P_API_NAME',l_api_name);
1156 FND_MSG_PUB.add;
1157 x_return_status := fnd_api.g_ret_sts_unexp_error;
1158 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1159 END update_resource_skills;
1160
1161
1162 /* Procedure to delete the resource skills */
1163
1164 PROCEDURE delete_resource_skills
1165 (P_API_VERSION IN NUMBER,
1166 P_INIT_MSG_LIST IN VARCHAR2,
1167 P_COMMIT IN VARCHAR2,
1168 P_RESOURCE_SKILL_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
1169 P_OBJECT_VERSION_NUM IN JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
1170 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1171 X_MSG_COUNT OUT NOCOPY NUMBER,
1172 X_MSG_DATA OUT NOCOPY VARCHAR2
1173 )IS
1174
1175
1176 CURSOR chk_res_exist_cur(ll_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE)
1177 IS
1178 SELECT resource_id
1179 FROM JTF_RS_RESOURCE_SKILLS
1180 WHERE resource_skill_id = ll_resource_skill_id;
1181
1182 chk_res_exist_rec chk_res_exist_cur%rowtype;
1183
1184 l_resource_skill_id JTF_RS_RESOURCE_SKILLS.resource_skill_id%TYPE := p_resource_skill_id;
1185
1186 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_SKILLS';
1187 l_api_version CONSTANT NUMBER := 1.0;
1188
1189 l_date Date;
1190 l_user_id Number;
1191 l_login_id Number;
1192
1193
1194 l_return_code VARCHAR2(100);
1195 l_count NUMBER;
1196 l_data VARCHAR2(200);
1197
1198 l_return_status VARCHAR2(200);
1199 l_msg_count NUMBER;
1200 l_msg_data VARCHAR2(200);
1201
1202 BEGIN
1203 --Standard Start of API SAVEPOINT
1204 SAVEPOINT RESOURCE_SKILLS_SP;
1205
1206 x_return_status := fnd_api.g_ret_sts_success;
1207
1208 --Standard Call to check API compatibility
1209 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1210 THEN
1211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212 END IF;
1213
1214 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1215 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1216 THEN
1217 FND_MSG_PUB.Initialize;
1218 END IF;
1219
1220 OPEN chk_res_exist_cur(l_resource_skill_id);
1221 FETCH chk_res_exist_cur INTO chk_res_exist_rec;
1222 IF (chk_res_exist_cur%FOUND)
1223 THEN
1224
1225 JTF_RS_RESOURCE_SKILLS_PKG.DELETE_ROW(
1226 X_RESOURCE_SKILL_ID => l_resource_skill_id);
1227
1228 ELSIF (chk_res_exist_cur%notfound) THEN
1229 x_return_status := fnd_api.g_ret_sts_error;
1230 fnd_message.set_name ('JTF', 'JTF_RS_RES_SKILL_ID_INVALID');
1231 FND_MSG_PUB.add;
1232 RAISE fnd_api.g_exc_error;
1233
1234 END IF;
1235
1236 CLOSE chk_res_exist_cur;
1237
1238 IF fnd_api.to_boolean (p_commit)
1239 THEN
1240 COMMIT WORK;
1241 END IF;
1242
1243 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1244
1245 EXCEPTION
1246 WHEN fnd_api.g_exc_unexpected_error
1247 THEN
1248 ROLLBACK TO RESOURCE_SKILLS_SP;
1249 x_return_status := fnd_api.g_ret_sts_unexp_error;
1250 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1251 WHEN fnd_api.g_exc_error
1252 THEN
1253 ROLLBACK TO RESOURCE_SKILLS_SP;
1254 x_return_status := fnd_api.g_ret_sts_error;
1255 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1256 WHEN OTHERS
1257 THEN
1258 ROLLBACK TO RESOURCE_SKILLS_SP;
1259 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1260 fnd_message.set_token('P_SQLCODE',SQLCODE);
1261 fnd_message.set_token('P_SQLERRM',SQLERRM);
1262 fnd_message.set_token('P_API_NAME',l_api_name);
1263 FND_MSG_PUB.add;
1264 x_return_status := fnd_api.g_ret_sts_unexp_error;
1265 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1266
1267 END delete_resource_skills;
1268
1269 END JTF_RS_RESOURCE_SKILLS_PVT;