[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_SKILLS_PUB
Source
1 PACKAGE BODY jtf_rs_resource_skills_pub AS
2 /* $Header: jtfrsukb.pls 120.0 2005/05/11 08:22:47 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a public API that user API will invoke.
6 It provides procedures for managing seed data of jtf_rs_resource_skills tables
7 create, update and delete rows
8 Its main procedures are as following:
9 Create resource_skills
10 Update resource_skills
11 Delete resource_skills
12 Calls to these procedures will call procedures of jtf_rs_resource_skills_pvt
13 to do inserts, updates and deletes into tables.
14
15 Modification history
16
17 Date Name Description
18 02-DEC-02 asachan Added two overloaded procedures create_resource_skills and
19 update_resource_skills for providing product skill
20 cascading capability. Also added two package body level
21 procedure update_existing_comp_skills and
25 /* Package variables. */
22 create_unrated_comp_skills(bug#2002193)
23 ******************************************************************************************/
24
26
27 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RESOURCE_SKILLS_PUB';
28 G_NAME VARCHAR2(240);
29
30 /* constant introduced as part of bug#2002193 */
31
32 DONT_CASCADE CONSTANT NUMBER :=0;
33 DO_CASCADE CONSTANT NUMBER :=1;
34 CASCADE_ALL CONSTANT NUMBER :=2;
35
36 /* Package body level procedure to update existing component level skill ratings
37 for a given resource. introduced as part of bug#2002193 */
38
39 PROCEDURE UPDATE_EXISTING_COMP_SKILLS
40 ( P_API_VERSION IN NUMBER,
41 P_INIT_MSG_LIST IN VARCHAR2,
42 P_COMMIT IN VARCHAR2,
43 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
44 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
45 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
46 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
47 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
48 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
49 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
50 X_MSG_COUNT OUT NOCOPY NUMBER,
51 X_MSG_DATA OUT NOCOPY VARCHAR2
52 )
53 IS
54 -- Cursor for retrieving existing component level skills
55 CURSOR component_skills_curr( p_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
56 p_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
57 p_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
58 IS
59 SELECT
60 RESOURCE_SKILL_ID,
61 RESOURCE_ID,
62 SKILL_LEVEL_ID,
63 CATEGORY_ID,
64 SUBCATEGORY,
65 PRODUCT_ID,
66 PRODUCT_ORG_ID,
67 PLATFORM_ID,
68 PLATFORM_ORG_ID,
69 PROBLEM_CODE,
70 COMPONENT_ID,
71 SUBCOMPONENT_ID,
72 OBJECT_VERSION_NUMBER,
73 ATTRIBUTE1,
74 ATTRIBUTE2,
75 ATTRIBUTE3,
76 ATTRIBUTE4,
77 ATTRIBUTE5,
78 ATTRIBUTE6,
79 ATTRIBUTE7,
80 ATTRIBUTE8,
81 ATTRIBUTE9,
82 ATTRIBUTE10,
83 ATTRIBUTE11,
84 ATTRIBUTE12,
85 ATTRIBUTE13,
86 ATTRIBUTE14,
87 ATTRIBUTE15,
88 ATTRIBUTE_CATEGORY
89 FROM jtf_rs_resource_skills
90 WHERE resource_id = p_resource_id
91 AND product_id = p_product_id
92 AND product_org_id = p_product_org_id
93 AND component_id IS NOT null;
94
95 component_skills_curr_rec component_skills_curr%rowtype;
96 l_object_version_number JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE;
97
98 BEGIN
99
100 x_return_status := fnd_api.g_ret_sts_success ;
101
102 FOR component_skills_curr_rec in component_skills_curr( p_product_id, p_product_org_id, p_resource_id)
103 LOOP
104
105 l_object_version_number := component_skills_curr_rec.object_version_number;
106
107 -- Update skill with new skill level
108
109 UPDATE_RESOURCE_SKILLS(
110 P_API_VERSION => p_api_version,
111 P_INIT_MSG_LIST => p_init_msg_list,
112 P_COMMIT => p_commit,
113 P_RESOURCE_SKILL_ID => component_skills_curr_rec.resource_skill_id,
114 P_RESOURCE_ID => component_skills_curr_rec.resource_id,
115 P_SKILL_LEVEL_ID => p_skill_level_id,
116 P_CATEGORY_ID => component_skills_curr_rec.category_id,
117 P_SUBCATEGORY => component_skills_curr_rec.subcategory,
118 P_PRODUCT_ID => component_skills_curr_rec.product_id,
119 P_PRODUCT_ORG_ID => component_skills_curr_rec.product_org_id,
120 P_PLATFORM_ID => component_skills_curr_rec.platform_id,
121 P_PLATFORM_ORG_ID => component_skills_curr_rec.platform_org_id,
122 P_PROBLEM_CODE => component_skills_curr_rec.problem_code,
123 P_COMPONENT_ID => component_skills_curr_rec.component_id,
124 P_SUBCOMPONENT_ID => component_skills_curr_rec.subcomponent_id,
125 P_OBJECT_VERSION_NUM => l_object_version_number,
126 P_ATTRIBUTE1 => component_skills_curr_rec.attribute1,
127 P_ATTRIBUTE2 => component_skills_curr_rec.attribute2,
128 P_ATTRIBUTE3 => component_skills_curr_rec.attribute3,
129 P_ATTRIBUTE4 => component_skills_curr_rec.attribute4,
130 P_ATTRIBUTE5 => component_skills_curr_rec.attribute5,
131 P_ATTRIBUTE6 => component_skills_curr_rec.attribute6,
132 P_ATTRIBUTE7 => component_skills_curr_rec.attribute7,
133 P_ATTRIBUTE8 => component_skills_curr_rec.attribute8,
134 P_ATTRIBUTE9 => component_skills_curr_rec.attribute9,
135 P_ATTRIBUTE10 => component_skills_curr_rec.attribute10,
136 P_ATTRIBUTE11 => component_skills_curr_rec.attribute11,
137 P_ATTRIBUTE12 => component_skills_curr_rec.attribute12,
138 P_ATTRIBUTE13 => component_skills_curr_rec.attribute13,
139 P_ATTRIBUTE14 => component_skills_curr_rec.attribute14,
140 P_ATTRIBUTE15 => component_skills_curr_rec.attribute15,
141 P_ATTRIBUTE_CATEGORY => component_skills_curr_rec.attribute_category,
142 X_RETURN_STATUS => X_RETURN_STATUS,
143 X_MSG_COUNT => X_MSG_COUNT,
144 X_MSG_DATA => X_MSG_DATA
145 );
146
147 EXIT WHEN (x_return_status <> fnd_api.g_ret_sts_success );
148
149 END LOOP;
150
151 END UPDATE_EXISTING_COMP_SKILLS;
152
153 /* Package body level procedure to create component level skill ratings for
154 unrated component for a given resource. introduced as part of bug#2002193 */
158 P_COMMIT IN VARCHAR2,
155 PROCEDURE CREATE_UNRATED_COMP_SKILLS
156 ( P_API_VERSION IN NUMBER,
157 P_INIT_MSG_LIST IN VARCHAR2,
159 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
160 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
161 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
162 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
163 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
164 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
165 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
166 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
167 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
168 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
169 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
170 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
171 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
172 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
173 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
174 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
175 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
176 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
177 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
178 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
179 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
180 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
181 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
182 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
183 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
184 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
185 X_MSG_COUNT OUT NOCOPY NUMBER,
186 X_MSG_DATA OUT NOCOPY VARCHAR2
187 )
188 IS
189 -- Cursor for retrieving unrated components of the product
190 CURSOR unrated_component_curr( p_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
191 p_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
192 p_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
193 IS
194 SELECT component_id
195 FROM jtf_rs_components_v comp
196 WHERE comp.product_id = p_product_id
197 AND comp.product_org_id = p_product_org_id
198 AND NOT EXISTS ( SELECT null
199 FROM jtf_rs_resource_skills skills
200 WHERE skills.resource_id = p_resource_id
201 AND skills.product_id = p_product_id
202 AND skills.product_org_id = p_product_org_id
203 AND skills.component_id = comp.component_id);
204
205 unrated_component_curr_rec unrated_component_curr%rowtype;
206 l_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE;
207
208 BEGIN
209
210 x_return_status := fnd_api.g_ret_sts_success ;
211
212 FOR unrated_component_curr_rec IN unrated_component_curr( p_product_id, p_product_org_id, p_resource_id)
213 LOOP
214 -- Create skill rating with default subcomponent_id
215
216 CREATE_RESOURCE_SKILLS(
217 P_API_VERSION => p_api_version,
218 P_INIT_MSG_LIST => p_init_msg_list,
219 P_COMMIT => p_commit,
220 P_RESOURCE_ID => p_resource_id,
221 P_SKILL_LEVEL_ID => p_skill_level_id,
222 P_CATEGORY_ID => p_category_id,
223 P_SUBCATEGORY => p_subcategory,
224 P_PRODUCT_ID => p_product_id,
225 P_PRODUCT_ORG_ID => p_product_org_id,
226 P_PLATFORM_ID => p_platform_id,
227 P_PLATFORM_ORG_ID => p_platform_org_id,
228 P_PROBLEM_CODE => p_problem_code,
229 P_COMPONENT_ID => unrated_component_curr_rec.component_id,
230 P_ATTRIBUTE1 => p_attribute1,
231 P_ATTRIBUTE2 => p_attribute2,
232 P_ATTRIBUTE3 => p_attribute3,
233 P_ATTRIBUTE4 => p_attribute4,
234 P_ATTRIBUTE5 => p_attribute5,
235 P_ATTRIBUTE6 => p_attribute6,
236 P_ATTRIBUTE7 => p_attribute7,
237 P_ATTRIBUTE8 => p_attribute8,
238 P_ATTRIBUTE9 => p_attribute9,
239 P_ATTRIBUTE10 => p_attribute10,
240 P_ATTRIBUTE11 => p_attribute11,
241 P_ATTRIBUTE12 => p_attribute12,
242 P_ATTRIBUTE13 => p_attribute13,
243 P_ATTRIBUTE14 => p_attribute14,
244 P_ATTRIBUTE15 => p_attribute15,
245 P_ATTRIBUTE_CATEGORY => p_attribute_category,
246 X_RETURN_STATUS => X_RETURN_STATUS,
247 X_MSG_COUNT => X_MSG_COUNT,
248 X_MSG_DATA => X_MSG_DATA,
249 X_RESOURCE_SKILL_ID => l_resource_skill_id
250 );
251
252
253 EXIT WHEN (x_return_status <> fnd_api.g_ret_sts_success );
254 END LOOP;
255
256 END CREATE_UNRATED_COMP_SKILLS;
257
258 /* Package body level procedure to update existing product level skill ratings
259 for a given resource. Introduced as part of */
260
261 PROCEDURE UPDATE_EXISTING_PROD_SKILLS
262 ( P_API_VERSION IN NUMBER,
263 P_INIT_MSG_LIST IN VARCHAR2,
264 P_COMMIT IN VARCHAR2,
265 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
266 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
267 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
271 )
268 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
269 X_MSG_COUNT OUT NOCOPY NUMBER,
270 X_MSG_DATA OUT NOCOPY VARCHAR2
272 IS
273 -- Cursor for retrieving existing product level skills
274 CURSOR product_skills_curr( p_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
275 p_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
276 IS
277 SELECT
278 RESOURCE_SKILL_ID,
279 RESOURCE_ID,
280 SKILL_LEVEL_ID,
281 CATEGORY_ID,
282 SUBCATEGORY,
283 PRODUCT_ID,
284 PRODUCT_ORG_ID,
285 PLATFORM_ID,
286 PLATFORM_ORG_ID,
287 PROBLEM_CODE,
288 COMPONENT_ID,
289 SUBCOMPONENT_ID,
290 OBJECT_VERSION_NUMBER,
291 ATTRIBUTE1,
292 ATTRIBUTE2,
293 ATTRIBUTE3,
294 ATTRIBUTE4,
295 ATTRIBUTE5,
296 ATTRIBUTE6,
297 ATTRIBUTE7,
298 ATTRIBUTE8,
299 ATTRIBUTE9,
300 ATTRIBUTE10,
301 ATTRIBUTE11,
302 ATTRIBUTE12,
303 ATTRIBUTE13,
304 ATTRIBUTE14,
305 ATTRIBUTE15,
306 ATTRIBUTE_CATEGORY
307 FROM jtf_rs_resource_skills a
308 WHERE resource_id = p_resource_id
309 AND ((category_id is null and
310 exists (SELECT null
311 FROM mtl_item_categories c
312 WHERE a.product_id = c.inventory_item_id
313 AND c.organization_id = a.product_org_id
314 AND c.category_id = p_category_id)) OR
315 category_id = p_category_id)
316 AND product_id IS NOT null
317 AND component_id IS NULL
318 AND problem_code IS NULL;
319
320 product_skills_curr_rec product_skills_curr%rowtype;
321 l_object_version_number JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE;
322
323 BEGIN
324
325 x_return_status := fnd_api.g_ret_sts_success ;
326
327 FOR product_skills_curr_rec in product_skills_curr( p_category_id, p_resource_id)
328 LOOP
329
330 l_object_version_number := product_skills_curr_rec.object_version_number;
331
332 -- Update skill with new skill level
333
334 UPDATE_RESOURCE_SKILLS(
335 P_API_VERSION => p_api_version,
336 P_INIT_MSG_LIST => p_init_msg_list,
337 P_COMMIT => p_commit,
338 P_RESOURCE_SKILL_ID => product_skills_curr_rec.resource_skill_id,
339 P_RESOURCE_ID => product_skills_curr_rec.resource_id,
340 P_SKILL_LEVEL_ID => p_skill_level_id,
341 P_CATEGORY_ID => product_skills_curr_rec.category_id,
342 P_SUBCATEGORY => product_skills_curr_rec.subcategory,
343 P_PRODUCT_ID => product_skills_curr_rec.product_id,
344 P_PRODUCT_ORG_ID => product_skills_curr_rec.product_org_id,
345 P_PLATFORM_ID => product_skills_curr_rec.platform_id,
346 P_PLATFORM_ORG_ID => product_skills_curr_rec.platform_org_id,
347 P_PROBLEM_CODE => product_skills_curr_rec.problem_code,
348 P_COMPONENT_ID => product_skills_curr_rec.component_id,
349 P_SUBCOMPONENT_ID => product_skills_curr_rec.subcomponent_id,
350 P_OBJECT_VERSION_NUM => l_object_version_number,
351 P_ATTRIBUTE1 => product_skills_curr_rec.attribute1,
352 P_ATTRIBUTE2 => product_skills_curr_rec.attribute2,
353 P_ATTRIBUTE3 => product_skills_curr_rec.attribute3,
354 P_ATTRIBUTE4 => product_skills_curr_rec.attribute4,
355 P_ATTRIBUTE5 => product_skills_curr_rec.attribute5,
356 P_ATTRIBUTE6 => product_skills_curr_rec.attribute6,
357 P_ATTRIBUTE7 => product_skills_curr_rec.attribute7,
358 P_ATTRIBUTE8 => product_skills_curr_rec.attribute8,
359 P_ATTRIBUTE9 => product_skills_curr_rec.attribute9,
360 P_ATTRIBUTE10 => product_skills_curr_rec.attribute10,
361 P_ATTRIBUTE11 => product_skills_curr_rec.attribute11,
362 P_ATTRIBUTE12 => product_skills_curr_rec.attribute12,
363 P_ATTRIBUTE13 => product_skills_curr_rec.attribute13,
364 P_ATTRIBUTE14 => product_skills_curr_rec.attribute14,
365 P_ATTRIBUTE15 => product_skills_curr_rec.attribute15,
366 P_ATTRIBUTE_CATEGORY => product_skills_curr_rec.attribute_category,
367 X_RETURN_STATUS => X_RETURN_STATUS,
368 X_MSG_COUNT => X_MSG_COUNT,
369 X_MSG_DATA => X_MSG_DATA
370 );
371
372
373 EXIT WHEN (x_return_status <> fnd_api.g_ret_sts_success );
374
375 END LOOP;
376
377 END UPDATE_EXISTING_PROD_SKILLS;
378
379 /* Package body level procedure to create product level skill ratings for
380 unrated component for a given resource. Introduced as part of */
381 PROCEDURE CREATE_UNRATED_PROD_SKILLS
382 ( P_API_VERSION IN NUMBER,
383 P_INIT_MSG_LIST IN VARCHAR2,
384 P_COMMIT IN VARCHAR2,
385 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
386 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
387 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
388 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
389 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
390 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
391 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
392 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
393 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
394 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
395 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
396 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
397 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
398 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
399 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
400 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
401 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
402 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
403 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
404 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
405 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
406 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
407 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
408 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
409 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
410 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
411 X_MSG_COUNT OUT NOCOPY NUMBER,
412 X_MSG_DATA OUT NOCOPY VARCHAR2
413 )
414 IS
415 -- Cursor for retrieving unrated products of the category
416 CURSOR unrated_product_curr( p_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
417 p_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
418 IS
419 SELECT a.product_id
420 FROM jtf_rs_products_v a
421 WHERE a.product_org_id = p_product_org_id
422 AND nvl(a.enabled_flag, 'Y') <> 'N'
423 AND EXISTS(SELECT null
424 FROM mtl_item_categories c
425 WHERE a.product_id = c.inventory_item_id
426 AND c.organization_id = p_product_org_id
427 AND c.category_id = p_category_id)
428 AND NOT EXISTS (SELECT null
429 FROM jtf_rs_resource_skills skills
430 WHERE skills.resource_id = p_resource_id
431 -- AND skills.category_id = p_category_id
432 -- user cannot create same skill twice, once at product level and second time at category->product level. Bug # 2171572.
433 AND skills.product_id = a.product_id
434 AND skills.product_org_id = p_product_org_id);
435
436 unrated_product_curr_rec unrated_product_curr%rowtype;
437 l_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE;
438
439 BEGIN
440
441 x_return_status := fnd_api.g_ret_sts_success ;
442
443 FOR unrated_product_curr_rec IN unrated_product_curr( p_category_id, p_resource_id)
444 LOOP
445 -- Create skill rating with default product_id
446
447 CREATE_RESOURCE_SKILLS(
448 P_API_VERSION => p_api_version,
449 P_INIT_MSG_LIST => p_init_msg_list,
450 P_COMMIT => p_commit,
451 P_RESOURCE_ID => p_resource_id,
452 P_SKILL_LEVEL_ID => p_skill_level_id,
453 P_CATEGORY_ID => p_category_id,
454 P_SUBCATEGORY => p_subcategory,
455 P_PRODUCT_ID => unrated_product_curr_rec.product_id,
456 P_PRODUCT_ORG_ID => p_product_org_id,
457 P_PLATFORM_ID => null,
458 P_PLATFORM_ORG_ID => null,
459 P_PROBLEM_CODE => null,
460 P_COMPONENT_ID => null,
461 P_ATTRIBUTE1 => p_attribute1,
465 P_ATTRIBUTE5 => p_attribute5,
462 P_ATTRIBUTE2 => p_attribute2,
463 P_ATTRIBUTE3 => p_attribute3,
464 P_ATTRIBUTE4 => p_attribute4,
466 P_ATTRIBUTE6 => p_attribute6,
467 P_ATTRIBUTE7 => p_attribute7,
468 P_ATTRIBUTE8 => p_attribute8,
469 P_ATTRIBUTE9 => p_attribute9,
470 P_ATTRIBUTE10 => p_attribute10,
471 P_ATTRIBUTE11 => p_attribute11,
472 P_ATTRIBUTE12 => p_attribute12,
473 P_ATTRIBUTE13 => p_attribute13,
474 P_ATTRIBUTE14 => p_attribute14,
475 P_ATTRIBUTE15 => p_attribute15,
476 P_ATTRIBUTE_CATEGORY => p_attribute_category,
477 X_RETURN_STATUS => X_RETURN_STATUS,
478 X_MSG_COUNT => X_MSG_COUNT,
479 X_MSG_DATA => X_MSG_DATA,
480 X_RESOURCE_SKILL_ID => l_resource_skill_id
481 );
482
483
484 EXIT WHEN (x_return_status <> fnd_api.g_ret_sts_success );
485 END LOOP;
486
487 END CREATE_UNRATED_PROD_SKILLS;
488
489
490 /* Procedure to create table attributes
491 based on input values passed by calling routines. */
492 PROCEDURE create_resource_skills
493 (P_API_VERSION IN NUMBER,
494 P_INIT_MSG_LIST IN VARCHAR2,
495 P_COMMIT IN VARCHAR2,
496 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
497 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
498 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
499 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
500 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
501 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
502 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
503 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
504 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
505 P_COMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
506 P_SUBCOMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
507 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
508 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
509 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
510 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
511 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
512 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
513 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
514 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
515 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
516 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
517 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
518 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
519 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
520 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
521 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
522 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
523 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
524 X_MSG_COUNT OUT NOCOPY NUMBER,
525 X_MSG_DATA OUT NOCOPY VARCHAR2,
526 X_RESOURCE_SKILL_ID OUT NOCOPY JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE
527 )IS
528
529 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_SKILLS';
530 l_api_version CONSTANT NUMBER := 1.0;
531
532 l_object_version_number number;
533
534 l_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE;
535
536 l_return_code VARCHAR2(100);
537 l_count NUMBER;
538 l_data VARCHAR2(200);
539
540 l_return_status VARCHAR2(200);
541 l_msg_count NUMBER;
542 l_msg_data VARCHAR2(200);
543
544 BEGIN
545 --Standard Start of API SAVEPOINT
546 SAVEPOINT CREATE_RESOURCE_SKILLS_SP;
547
548 x_return_status := fnd_api.g_ret_sts_success;
549
550 --Standard Call to check API compatibility
551 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
552 THEN
553 RAISE FND_API.G_EXC_ERROR;
554 END IF;
555
556 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
557 IF FND_API.To_boolean(P_INIT_MSG_LIST)
558 THEN
559 FND_MSG_PUB.Initialize;
560 END IF;
561
562 JTF_RS_RESOURCE_SKILLS_PVT.CREATE_RESOURCE_SKILLS(
563 P_API_VERSION => l_api_version,
564 P_INIT_MSG_LIST => p_init_msg_list,
565 P_COMMIT => p_commit,
566 P_RESOURCE_ID => p_resource_id,
567 P_SKILL_LEVEL_ID => p_skill_level_id,
568 P_CATEGORY_ID => p_category_id,
569 P_SUBCATEGORY => p_subcategory,
570 P_PRODUCT_ID => p_product_id,
571 P_PRODUCT_ORG_ID => p_product_org_id,
572 P_PLATFORM_ID => p_platform_id,
573 P_PLATFORM_ORG_ID => p_platform_org_id,
574 P_PROBLEM_CODE => p_problem_code,
578 P_ATTRIBUTE2 => p_attribute2,
575 P_COMPONENT_ID => p_component_id,
576 P_SUBCOMPONENT_ID => p_subcomponent_id,
577 P_ATTRIBUTE1 => p_attribute1,
579 P_ATTRIBUTE3 => p_attribute3,
580 P_ATTRIBUTE4 => p_attribute4,
581 P_ATTRIBUTE5 => p_attribute5,
582 P_ATTRIBUTE6 => p_attribute6,
583 P_ATTRIBUTE7 => p_attribute7,
584 P_ATTRIBUTE8 => p_attribute8,
585 P_ATTRIBUTE9 => p_attribute9,
586 P_ATTRIBUTE10 => p_attribute10,
587 P_ATTRIBUTE11 => p_attribute11,
588 P_ATTRIBUTE12 => p_attribute12,
589 P_ATTRIBUTE13 => p_attribute13,
590 P_ATTRIBUTE14 => p_attribute14,
591 P_ATTRIBUTE15 => p_attribute15,
592 P_ATTRIBUTE_CATEGORY => p_attribute_category,
593 X_RETURN_STATUS => l_return_status,
594 X_MSG_COUNT => l_msg_count,
595 X_MSG_DATA => l_msg_data,
596 X_RESOURCE_SKILL_ID => l_resource_skill_id
597 );
598
599 X_RESOURCE_SKILL_ID := l_resource_skill_id;
600 X_RETURN_STATUS := l_return_status;
601 X_MSG_COUNT := l_msg_count;
602 X_MSG_DATA := l_msg_data;
603
604 IF(l_return_status <> fnd_api.g_ret_sts_success)
605 THEN
606 x_return_status := l_return_status ;
607 RAISE fnd_api.g_exc_error;
608 END IF;
609
610
611 --standard commit
612 IF fnd_api.to_boolean (p_commit)
613 THEN
614 COMMIT WORK;
615 END IF;
616
617 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
618
619 EXCEPTION
620 WHEN fnd_api.g_exc_unexpected_error
621 THEN
622
623 ROLLBACK TO CREATE_RESOURCE_SKILLS_SP;
624 x_return_status := fnd_api.g_ret_sts_unexp_error;
625 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
626 WHEN fnd_api.g_exc_error
627 THEN
628 ROLLBACK TO CREATE_RESOURCE_SKILLS_SP;
629 --x_return_status := fnd_api.g_ret_sts_error;
630 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
631 WHEN OTHERS
632 THEN
633 ROLLBACK TO CREATE_RESOURCE_SKILLS_SP;
634 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
635 fnd_message.set_token('P_SQLCODE',SQLCODE);
636 fnd_message.set_token('P_SQLERRM',SQLERRM);
637 fnd_message.set_token('P_API_NAME', l_api_name);
638 FND_MSG_PUB.add;
639 x_return_status := fnd_api.g_ret_sts_unexp_error;
640 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
641
642 END CREATE_RESOURCE_SKILLS;
643
644
645 /* Procedure to update resource skills
646 based on input values passed by calling routines. */
647
648 PROCEDURE update_resource_skills
649 (P_API_VERSION IN NUMBER,
650 P_INIT_MSG_LIST IN VARCHAR2,
651 P_COMMIT IN VARCHAR2,
652 P_RESOURCE_SKILL_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
653 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
654 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
655 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
656 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
657 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
658 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
659 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
660 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
661 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
662 P_COMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
663 P_SUBCOMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
664 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
665 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
666 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
667 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
668 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
669 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
670 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
671 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
672 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
673 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
674 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
675 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
676 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
677 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
678 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
679 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
680 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
681 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
682 X_MSG_COUNT OUT NOCOPY NUMBER,
683 X_MSG_DATA OUT NOCOPY VARCHAR2
684 )IS
688
685 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_SKILLS';
686 l_api_version CONSTANT NUMBER := 1.0;
687 l_bind_data_id number;
689 l_return_code VARCHAR2(100);
690 l_count NUMBER;
691 l_data VARCHAR2(200);
692
693 l_object_version_number JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
694
695 l_return_status VARCHAR2(200);
696 l_msg_count NUMBER;
697 l_msg_data VARCHAR2(200);
698
699 BEGIN
700 --Standard Start of API SAVEPOINT
701 SAVEPOINT UPDATE_RESOURCE_SKILLS_SP;
702
703 x_return_status := fnd_api.g_ret_sts_success;
704
705 --Standard Call to check API compatibility
706 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
707 THEN
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710
711 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
712 IF FND_API.To_boolean(P_INIT_MSG_LIST)
713 THEN
714 FND_MSG_PUB.Initialize;
715 END IF;
716
717 JTF_RS_RESOURCE_SKILLS_PVT.UPDATE_RESOURCE_SKILLS(
718 P_API_VERSION => l_api_version,
719 P_INIT_MSG_LIST => p_init_msg_list,
720 P_COMMIT => p_commit,
721 P_RESOURCE_SKILL_ID => p_resource_skill_id,
722 P_RESOURCE_ID => p_resource_id,
723 P_SKILL_LEVEL_ID => p_SKILL_LEVEL_ID,
724 P_CATEGORY_ID => p_category_id,
725 P_SUBCATEGORY => p_subcategory,
726 P_PRODUCT_ID => p_product_id,
727 P_PRODUCT_ORG_ID => p_product_org_id,
728 P_PLATFORM_ID => p_platform_id,
729 P_PLATFORM_ORG_ID => p_platform_org_id,
730 P_PROBLEM_CODE => p_problem_code,
731 P_COMPONENT_ID => p_component_id,
732 P_SUBCOMPONENT_ID => p_subcomponent_id,
733 P_OBJECT_VERSION_NUM => l_object_version_number,
734 P_ATTRIBUTE1 => p_attribute1,
735 P_ATTRIBUTE2 => P_attribute2,
736 P_ATTRIBUTE3 => p_attribute3,
737 P_ATTRIBUTE4 => p_attribute4,
738 P_ATTRIBUTE5 => p_attribute5,
739 P_ATTRIBUTE6 => p_attribute6,
740 P_ATTRIBUTE7 => p_attribute7,
741 P_ATTRIBUTE8 => p_attribute8,
742 P_ATTRIBUTE9 => p_attribute9,
743 P_ATTRIBUTE10 => p_attribute10,
744 P_ATTRIBUTE11 => p_attribute11,
745 P_ATTRIBUTE12 => p_attribute12,
746 P_ATTRIBUTE13 => p_attribute13,
747 P_ATTRIBUTE14 => p_attribute14,
748 P_ATTRIBUTE15 => p_attribute15,
749 P_ATTRIBUTE_CATEGORY => p_attribute_category,
750 X_RETURN_STATUS => l_return_status,
751 X_MSG_COUNT => l_msg_count,
752 X_MSG_DATA => l_msg_data
753 );
754
755 X_RETURN_STATUS := l_return_status;
756 X_MSG_COUNT := l_msg_count;
757 X_MSG_DATA := l_msg_data;
758 P_OBJECT_VERSION_NUM := l_object_version_number;
759
760 IF(l_return_status <> fnd_api.g_ret_sts_success)
761 THEN
762 x_return_status := l_return_status ;
763 RAISE fnd_api.g_exc_error;
764 END IF;
765
766
767 IF fnd_api.to_boolean (p_commit)
768 THEN
769 COMMIT WORK;
770 END IF;
771
772 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
773
774 EXCEPTION
775 WHEN fnd_api.g_exc_error
776 THEN
777 ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;
778 --x_return_status := fnd_api.g_ret_sts_error;
779 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
780
781 WHEN fnd_api.g_exc_unexpected_error
782 THEN
783 ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;
784 x_return_status := fnd_api.g_ret_sts_unexp_error;
785 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
786 WHEN OTHERS
787 THEN
788 ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;
789 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
790 fnd_message.set_token('P_SQLCODE',SQLCODE);
791 fnd_message.set_token('P_SQLERRM',SQLERRM);
792 fnd_message.set_token('P_API_NAME',l_api_name);
793 FND_MSG_PUB.add;
794 x_return_status := fnd_api.g_ret_sts_unexp_error;
795 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
796 END update_resource_skills;
797
798
799 /* Procedure to delete the resource skills */
800
801 PROCEDURE delete_resource_skills
802 (P_API_VERSION IN NUMBER,
803 P_INIT_MSG_LIST IN VARCHAR2,
804 P_COMMIT IN VARCHAR2,
805 P_RESOURCE_SKILL_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
806 P_OBJECT_VERSION_NUM IN JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
810 )IS
807 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
808 X_MSG_COUNT OUT NOCOPY NUMBER,
809 X_MSG_DATA OUT NOCOPY VARCHAR2
811
812
813 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_SKILLS';
814 l_api_version CONSTANT NUMBER :=1.0;
815 l_bind_data_id number;
816
817 l_return_code VARCHAR2(100);
818 l_count NUMBER;
819 l_data VARCHAR2(200);
820
821 l_return_status VARCHAR2(200);
822 l_msg_count NUMBER;
823 l_msg_data VARCHAR2(200);
824
825 BEGIN
826 --Standard Start of API SAVEPOINT
827 SAVEPOINT DELETE_RESOURCE_SKILLS_SP;
828
829 x_return_status := fnd_api.g_ret_sts_success;
830
831 --Standard Call to check API compatibility
832 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
833 THEN
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836
837 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
838 IF FND_API.To_boolean(P_INIT_MSG_LIST)
839 THEN
840 FND_MSG_PUB.Initialize;
841 END IF;
842
843 JTF_RS_RESOURCE_SKILLS_PVT.DELETE_RESOURCE_SKILLS
844 (P_API_VERSION => l_api_version,
845 P_INIT_MSG_LIST => p_init_msg_list,
846 P_COMMIT => p_commit,
847 P_RESOURCE_SKILL_ID => p_resource_skill_id,
848 P_OBJECT_VERSION_NUM => p_object_version_num,
849 X_RETURN_STATUS => l_return_status,
850 X_MSG_COUNT => l_msg_count,
851 X_MSG_DATA => l_msg_data
852 );
853
854 X_RETURN_STATUS := l_return_status;
855 X_MSG_COUNT := l_msg_count;
856 X_MSG_DATA := l_msg_data;
857
858 IF(l_return_status <> fnd_api.g_ret_sts_success)
859 THEN
860 x_return_status := l_return_status ;
861 RAISE fnd_api.g_exc_error;
862 END IF;
863
864 IF fnd_api.to_boolean (p_commit)
865 THEN
866 COMMIT WORK;
867 END IF;
868
869 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
870
871 EXCEPTION
872 WHEN fnd_api.g_exc_unexpected_error
873 THEN
874 ROLLBACK TO DELETE_RESOURCE_SKILLS_SP;
875 x_return_status := fnd_api.g_ret_sts_unexp_error;
876 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
877 WHEN fnd_api.g_exc_error
878 THEN
879 ROLLBACK TO DELETE_RESOURCE_SKILLS_SP;
880 --x_return_status := fnd_api.g_ret_sts_error;
881 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
882 WHEN OTHERS
883 THEN
884 ROLLBACK TO DELETE_RESOURCE_SKILLS_SP;
885 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
886 fnd_message.set_token('P_SQLCODE',SQLCODE);
887 fnd_message.set_token('P_SQLERRM',SQLERRM);
888 fnd_message.set_token('P_API_NAME',l_api_name);
889 FND_MSG_PUB.add;
890 x_return_status := fnd_api.g_ret_sts_unexp_error;
891 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
892
893 END delete_resource_skills;
894
895 /* Procedure to create skill rating with cascading.
896 introduced as part of bug#2002193 */
897 PROCEDURE create_resource_skills
898 (P_API_VERSION IN NUMBER,
899 P_INIT_MSG_LIST IN VARCHAR2,
900 P_COMMIT IN VARCHAR2,
901 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
902 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
903 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
904 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
905 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
906 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
907 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
908 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
909 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
910 P_COMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
911 P_SUBCOMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
912 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
913 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
914 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
915 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
916 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
917 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
918 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
919 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
920 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
921 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
922 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
923 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
924 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
925 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
926 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
927 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
928 P_CASCADE_OPTION IN NUMBER,
929 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
933 )IS
930 X_MSG_COUNT OUT NOCOPY NUMBER,
931 X_MSG_DATA OUT NOCOPY VARCHAR2,
932 X_RESOURCE_SKILL_ID OUT NOCOPY JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE
934
935
936
937 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_SKILLS';
938 l_api_version CONSTANT NUMBER :=1.0;
939 l_commit CONSTANT VARCHAR2(1):= FND_API.G_FALSE;
940 l_init_msg_list CONSTANT VARCHAR2(1):= FND_API.G_FALSE;
941
942 l_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE;
943
944 l_cascade_option NUMBER;
945 l_msg_count NUMBER;
946 l_msg_data VARCHAR2(200);
947
948 l_subcategory JTF_RS_RESOURCE_SKILLS.subcategory%TYPE := p_subcategory;
949
950 BEGIN
951
952 --Standard Start of API SAVEPOINT
953 SAVEPOINT CREATE_RESOURCE_SKILLS_SP;
954
955
956 l_cascade_option := p_cascade_option;
957 x_return_status := fnd_api.g_ret_sts_success;
958 l_product_org_id := p_product_org_id ;
959
960 --Standard Call to check API compatibility
961 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
962 THEN
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965
966 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
967 IF FND_API.To_boolean(P_INIT_MSG_LIST)
968 THEN
969 FND_MSG_PUB.Initialize;
970 END IF;
971
972 IF (l_cascade_option is NULL) THEN
973 l_cascade_option := DONT_CASCADE;
974 END IF;
975
976 IF (p_product_id is NULL and p_platform_id is null and p_problem_code is null) then
977 l_subcategory := NULL;
978 END IF;
979
980 -- Check if cascade option is set to proper value
981 IF (
982 NOT (
983 (l_cascade_option = DONT_CASCADE ) OR
984 (l_cascade_option = DO_CASCADE ) OR
985 (l_cascade_option = CASCADE_ALL )
986 )
987 )
988 THEN
989 RAISE fnd_api.g_exc_unexpected_error;
990 END IF;
991
992 IF ( l_product_org_id is NULL ) THEN
993 l_product_org_id := nvl(JTF_RESOURCE_UTL.GET_INVENTORY_ORG_ID, -1);
994 END IF;
995
996 -- Cannot cascade if product_id is not null and component or problem code is given.
997
998 IF (P_PRODUCT_ID IS NOT NULL AND
999 (P_COMPONENT_ID IS NOT NULL OR P_PROBLEM_CODE IS NOT NULL))
1000 THEN
1001 l_cascade_option := DONT_CASCADE;
1002 END IF;
1003
1004 IF (l_subcategory is null AND (P_CATEGORY_ID is NOT NULL) AND (l_cascade_option = DONT_CASCADE)) then
1005
1006 -- Create category only resource skill
1007
1008 CREATE_RESOURCE_SKILLS(
1009 P_API_VERSION => l_api_version,
1010 P_INIT_MSG_LIST => l_init_msg_list,
1011 P_COMMIT => l_commit,
1012 P_RESOURCE_ID => p_resource_id,
1013 P_SKILL_LEVEL_ID => p_skill_level_id,
1014 P_CATEGORY_ID => p_category_id,
1015 P_SUBCATEGORY => null,
1016 P_PRODUCT_ID => null,
1017 P_PRODUCT_ORG_ID => null,
1018 P_PLATFORM_ID => null,
1019 P_PLATFORM_ORG_ID => null,
1020 P_PROBLEM_CODE => null,
1021 P_COMPONENT_ID => null,
1022 P_SUBCOMPONENT_ID => null,
1023 P_ATTRIBUTE1 => p_attribute1,
1024 P_ATTRIBUTE2 => p_attribute2,
1025 P_ATTRIBUTE3 => p_attribute3,
1026 P_ATTRIBUTE4 => p_attribute4,
1027 P_ATTRIBUTE5 => p_attribute5,
1028 P_ATTRIBUTE6 => p_attribute6,
1029 P_ATTRIBUTE7 => p_attribute7,
1030 P_ATTRIBUTE8 => p_attribute8,
1031 P_ATTRIBUTE9 => p_attribute9,
1032 P_ATTRIBUTE10 => p_attribute10,
1033 P_ATTRIBUTE11 => p_attribute11,
1034 P_ATTRIBUTE12 => p_attribute12,
1035 P_ATTRIBUTE13 => p_attribute13,
1036 P_ATTRIBUTE14 => p_attribute14,
1037 P_ATTRIBUTE15 => p_attribute15,
1038 P_ATTRIBUTE_CATEGORY => p_attribute_category,
1039 X_RETURN_STATUS => X_RETURN_STATUS,
1040 X_MSG_COUNT => X_MSG_COUNT,
1041 X_MSG_DATA => X_MSG_DATA,
1042 X_RESOURCE_SKILL_ID => x_resource_skill_id
1043 );
1044
1045 IF (x_return_status <> fnd_api.g_ret_sts_success)
1046 THEN
1047 RAISE fnd_api.g_exc_error;
1048 END IF;
1049
1050 ELSE /* else of (l_subcategory is null) AND (P_CATEGORY_ID is NOT NULL) AND (l_cascade_option = DONT_CASCADE)*/
1051
1052 -- Update exisiting component level skill ratings with input skill level
1053
1054 IF ( l_cascade_option = CASCADE_ALL )
1055 THEN
1056
1057 IF ((l_subcategory is null) AND (p_category_id is NOT NULL)) THEN
1058 UPDATE_EXISTING_PROD_SKILLS
1059 (
1060 P_API_VERSION => l_api_version,
1061 P_INIT_MSG_LIST => l_init_msg_list,
1062 P_COMMIT => l_commit,
1063 P_RESOURCE_ID => p_resource_id,
1064 P_SKILL_LEVEL_ID => p_skill_level_id,
1065 P_CATEGORY_ID => p_category_id,
1066 X_RETURN_STATUS => X_RETURN_STATUS,
1067 X_MSG_COUNT => X_MSG_COUNT,
1068 X_MSG_DATA => X_MSG_DATA
1069 );
1070
1071 IF (x_return_status <> fnd_api.g_ret_sts_success)
1072 THEN
1073 RAISE fnd_api.g_exc_error;
1074 END IF;
1075 ELSE
1076 UPDATE_EXISTING_COMP_SKILLS
1077 (
1078 P_API_VERSION => l_api_version,
1079 P_INIT_MSG_LIST => l_init_msg_list,
1080 P_COMMIT => l_commit,
1081 P_RESOURCE_ID => p_resource_id,
1082 P_SKILL_LEVEL_ID => p_skill_level_id,
1083 P_CATEGORY_ID => p_category_id,
1084 P_SUBCATEGORY => p_subcategory,
1085 P_PRODUCT_ID => p_product_id,
1086 P_PRODUCT_ORG_ID => l_product_org_id,
1087 X_RETURN_STATUS => X_RETURN_STATUS,
1088 X_MSG_COUNT => X_MSG_COUNT,
1089 X_MSG_DATA => X_MSG_DATA
1090 );
1091
1092 IF (x_return_status <> fnd_api.g_ret_sts_success)
1093 THEN
1094 RAISE fnd_api.g_exc_error;
1095 END IF;
1096 END IF;
1097 END IF;
1098
1099 -- Create resource skill
1100
1101 CREATE_RESOURCE_SKILLS(
1102 P_API_VERSION => l_api_version,
1103 P_INIT_MSG_LIST => l_init_msg_list,
1104 P_COMMIT => l_commit,
1105 P_RESOURCE_ID => p_resource_id,
1106 P_SKILL_LEVEL_ID => p_skill_level_id,
1107 P_CATEGORY_ID => p_category_id,
1108 P_SUBCATEGORY => l_subcategory,
1109 P_PRODUCT_ID => p_product_id,
1110 P_PRODUCT_ORG_ID => p_product_org_id,
1111 P_PLATFORM_ID => p_platform_id,
1112 P_PLATFORM_ORG_ID => p_platform_org_id,
1113 P_PROBLEM_CODE => p_problem_code,
1114 P_COMPONENT_ID => p_component_id,
1115 P_SUBCOMPONENT_ID => p_subcomponent_id,
1116 P_ATTRIBUTE1 => p_attribute1,
1117 P_ATTRIBUTE2 => p_attribute2,
1118 P_ATTRIBUTE3 => p_attribute3,
1119 P_ATTRIBUTE4 => p_attribute4,
1120 P_ATTRIBUTE5 => p_attribute5,
1121 P_ATTRIBUTE6 => p_attribute6,
1122 P_ATTRIBUTE7 => p_attribute7,
1123 P_ATTRIBUTE8 => p_attribute8,
1124 P_ATTRIBUTE9 => p_attribute9,
1125 P_ATTRIBUTE10 => p_attribute10,
1126 P_ATTRIBUTE11 => p_attribute11,
1127 P_ATTRIBUTE12 => p_attribute12,
1128 P_ATTRIBUTE13 => p_attribute13,
1129 P_ATTRIBUTE14 => p_attribute14,
1130 P_ATTRIBUTE15 => p_attribute15,
1131 P_ATTRIBUTE_CATEGORY => p_attribute_category,
1132 X_RETURN_STATUS => X_RETURN_STATUS,
1133 X_MSG_COUNT => X_MSG_COUNT,
1134 X_MSG_DATA => X_MSG_DATA,
1135 X_RESOURCE_SKILL_ID => x_resource_skill_id
1136 );
1137
1138 IF (x_return_status <> fnd_api.g_ret_sts_success)
1139 THEN
1140 RAISE fnd_api.g_exc_error;
1141 END IF;
1142
1143 -- Create component level skill ratings for unrated components of the product
1144
1145 IF ( ( l_cascade_option = DO_CASCADE) OR ( l_cascade_option = CASCADE_ALL ) )
1146 THEN
1147
1148 IF ((l_subcategory is null) AND (p_category_id is NOT NULL)) THEN
1149
1150 CREATE_UNRATED_PROD_SKILLS(
1151 P_API_VERSION => l_api_version,
1152 P_INIT_MSG_LIST => l_init_msg_list,
1153 P_COMMIT => l_commit,
1154 P_RESOURCE_ID => p_resource_id,
1155 P_SKILL_LEVEL_ID => p_skill_level_id,
1156 P_CATEGORY_ID => p_category_id,
1157 P_SUBCATEGORY => p_subcategory,
1158 P_PRODUCT_ID => p_product_id,
1159 P_PRODUCT_ORG_ID => l_product_org_id,
1160 P_PLATFORM_ID => p_platform_id,
1161 P_PLATFORM_ORG_ID => p_platform_org_id,
1162 P_PROBLEM_CODE => p_problem_code,
1163 P_ATTRIBUTE1 => p_attribute1,
1164 P_ATTRIBUTE2 => p_attribute2,
1165 P_ATTRIBUTE3 => p_attribute3,
1166 P_ATTRIBUTE4 => p_attribute4,
1167 P_ATTRIBUTE5 => p_attribute5,
1168 P_ATTRIBUTE6 => p_attribute6,
1169 P_ATTRIBUTE7 => p_attribute7,
1170 P_ATTRIBUTE8 => p_attribute8,
1171 P_ATTRIBUTE9 => p_attribute9,
1172 P_ATTRIBUTE10 => p_attribute10,
1173 P_ATTRIBUTE11 => p_attribute11,
1174 P_ATTRIBUTE12 => p_attribute12,
1175 P_ATTRIBUTE13 => p_attribute13,
1176 P_ATTRIBUTE14 => p_attribute14,
1177 P_ATTRIBUTE15 => p_attribute15,
1178 P_ATTRIBUTE_CATEGORY => p_attribute_category,
1179 X_RETURN_STATUS => X_RETURN_STATUS,
1180 X_MSG_COUNT => X_MSG_COUNT,
1181 X_MSG_DATA => X_MSG_DATA
1182 );
1183
1184 IF (x_return_status <> fnd_api.g_ret_sts_success)
1185 THEN
1186 RAISE fnd_api.g_exc_error;
1187 END IF;
1188 ELSE
1189 CREATE_UNRATED_COMP_SKILLS(
1190 P_API_VERSION => l_api_version,
1191 P_INIT_MSG_LIST => l_init_msg_list,
1192 P_COMMIT => l_commit,
1193 P_RESOURCE_ID => p_resource_id,
1194 P_SKILL_LEVEL_ID => p_skill_level_id,
1195 P_CATEGORY_ID => p_category_id,
1196 P_SUBCATEGORY => p_subcategory,
1197 P_PRODUCT_ID => p_product_id,
1198 P_PRODUCT_ORG_ID => l_product_org_id,
1199 P_PLATFORM_ID => p_platform_id,
1200 P_PLATFORM_ORG_ID => p_platform_org_id,
1201 P_PROBLEM_CODE => p_problem_code,
1202 P_ATTRIBUTE1 => p_attribute1,
1203 P_ATTRIBUTE2 => p_attribute2,
1204 P_ATTRIBUTE3 => p_attribute3,
1205 P_ATTRIBUTE4 => p_attribute4,
1206 P_ATTRIBUTE5 => p_attribute5,
1207 P_ATTRIBUTE6 => p_attribute6,
1208 P_ATTRIBUTE7 => p_attribute7,
1209 P_ATTRIBUTE8 => p_attribute8,
1210 P_ATTRIBUTE9 => p_attribute9,
1211 P_ATTRIBUTE10 => p_attribute10,
1212 P_ATTRIBUTE11 => p_attribute11,
1213 P_ATTRIBUTE12 => p_attribute12,
1214 P_ATTRIBUTE13 => p_attribute13,
1215 P_ATTRIBUTE14 => p_attribute14,
1216 P_ATTRIBUTE15 => p_attribute15,
1217 P_ATTRIBUTE_CATEGORY => p_attribute_category,
1218 X_RETURN_STATUS => X_RETURN_STATUS,
1219 X_MSG_COUNT => X_MSG_COUNT,
1220 X_MSG_DATA => X_MSG_DATA
1221 );
1222
1223 IF (x_return_status <> fnd_api.g_ret_sts_success)
1224 THEN
1225 RAISE fnd_api.g_exc_error;
1226 END IF;
1227
1228 END IF;
1229 END IF;
1230
1231 END IF; /* End of if (l_subcategory is null) AND (P_CATEGORY_ID is NOT NULL) AND (l_cascade_option = DONT_CASCADE)*/
1232
1233 --standard commit
1234 IF fnd_api.to_boolean (p_commit)
1235 THEN
1236 COMMIT WORK;
1237 END IF;
1238
1239
1240 EXCEPTION
1241 WHEN fnd_api.g_exc_unexpected_error
1242 THEN
1243 ROLLBACK TO CREATE_RESOURCE_SKILLS_SP;
1244 x_return_status := fnd_api.g_ret_sts_unexp_error;
1245 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1246 WHEN fnd_api.g_exc_error
1247 THEN
1248 ROLLBACK TO CREATE_RESOURCE_SKILLS_SP;
1249 x_return_status := fnd_api.g_ret_sts_error;
1250 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1251 WHEN OTHERS
1252 THEN
1253 ROLLBACK TO CREATE_RESOURCE_SKILLS_SP;
1254 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1255 fnd_message.set_token('P_SQLCODE',SQLCODE);
1256 fnd_message.set_token('P_SQLERRM',SQLERRM);
1257 fnd_message.set_token('P_API_NAME', l_api_name);
1258 FND_MSG_PUB.add;
1259 x_return_status := fnd_api.g_ret_sts_unexp_error;
1260 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1261
1262 END CREATE_RESOURCE_SKILLS;
1263
1264 /* Procedure to update skill rating with cascading.
1265 introduced as part of bug#2002193 */
1266
1267 PROCEDURE update_resource_skills
1268 (P_API_VERSION IN NUMBER,
1269 P_INIT_MSG_LIST IN VARCHAR2,
1270 P_COMMIT IN VARCHAR2,
1271 P_RESOURCE_SKILL_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
1272 P_RESOURCE_ID IN JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
1273 P_SKILL_LEVEL_ID IN JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
1274 P_CATEGORY_ID IN JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
1275 P_SUBCATEGORY IN JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
1276 P_PRODUCT_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
1277 P_PRODUCT_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
1278 P_PLATFORM_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
1279 P_PLATFORM_ORG_ID IN JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
1280 P_PROBLEM_CODE IN JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
1281 P_COMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
1282 P_SUBCOMPONENT_ID IN JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
1283 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
1284 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
1285 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
1286 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
1287 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
1288 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
1289 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
1290 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
1291 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
1292 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
1293 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
1294 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
1295 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
1296 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
1297 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
1298 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
1299 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
1300 P_CASCADE_OPTION IN NUMBER,
1301 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1302 X_MSG_COUNT OUT NOCOPY NUMBER,
1303 X_MSG_DATA OUT NOCOPY VARCHAR2
1304 )IS
1305
1306
1307 CURSOR skill_curr( p_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE)
1308 IS
1309 SELECT
1310 RESOURCE_SKILL_ID,
1311 RESOURCE_ID,
1312 SKILL_LEVEL_ID,
1313 CATEGORY_ID,
1314 SUBCATEGORY,
1315 PRODUCT_ID,
1316 PRODUCT_ORG_ID,
1317 PLATFORM_ID,
1318 PLATFORM_ORG_ID,
1319 PROBLEM_CODE,
1320 COMPONENT_ID,
1321 SUBCOMPONENT_ID,
1322 OBJECT_VERSION_NUMBER,
1323 ATTRIBUTE1,
1324 ATTRIBUTE2,
1325 ATTRIBUTE3,
1326 ATTRIBUTE4,
1327 ATTRIBUTE5,
1328 ATTRIBUTE6,
1329 ATTRIBUTE7,
1330 ATTRIBUTE8,
1331 ATTRIBUTE9,
1332 ATTRIBUTE10,
1333 ATTRIBUTE11,
1334 ATTRIBUTE12,
1335 ATTRIBUTE13,
1336 ATTRIBUTE14,
1337 ATTRIBUTE15,
1338 ATTRIBUTE_CATEGORY
1339 FROM jtf_rs_resource_skills
1340 WHERE resource_skill_id = p_resource_skill_id;
1341
1342 skill_curr_rec skill_curr%rowtype;
1343
1344 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_SKILLS';
1345 l_api_version CONSTANT NUMBER :=1.0;
1346 l_commit CONSTANT VARCHAR2(1):= FND_API.G_FALSE;
1347 l_init_msg_list CONSTANT VARCHAR2(1):= FND_API.G_FALSE;
1348
1349 l_cascade_option NUMBER;
1350 l_return_status VARCHAR2(200);
1351 l_msg_count NUMBER;
1352 l_msg_data VARCHAR2(200);
1353 l_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE;
1354
1355 BEGIN
1356
1357 --Standard Start of API SAVEPOINT
1358 SAVEPOINT UPDATE_RESOURCE_SKILLS_SP;
1359
1360
1361 -- Initialize local variable
1362 l_cascade_option := p_cascade_option;
1363 x_return_status := fnd_api.g_ret_sts_success;
1364 l_return_status := fnd_api.g_ret_sts_success;
1365
1366
1367 --Standard Call to check API compatibility
1368 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1369 THEN
1370 RAISE FND_API.G_EXC_ERROR;
1371 END IF;
1372
1373 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1374 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1375 THEN
1376 FND_MSG_PUB.Initialize;
1377 END IF;
1378
1379 IF (l_cascade_option is NULL) THEN
1380 l_cascade_option := DONT_CASCADE;
1381 END IF;
1382
1383 -- Check if cascade option is set to proper value
1384 IF (
1385 NOT (
1386 (l_cascade_option = DONT_CASCADE ) OR
1387 (l_cascade_option = DO_CASCADE ) OR
1388 (l_cascade_option = CASCADE_ALL )
1389 )
1390 )
1391 THEN
1392 RAISE fnd_api.g_exc_unexpected_error;
1393 END IF;
1394
1395
1396 -- Update resource skill
1397 UPDATE_RESOURCE_SKILLS(
1398 P_API_VERSION => l_api_version,
1399 P_INIT_MSG_LIST => l_init_msg_list,
1400 P_COMMIT => l_commit,
1401 P_RESOURCE_SKILL_ID => p_resource_skill_id,
1402 P_RESOURCE_ID => p_resource_id,
1403 P_SKILL_LEVEL_ID => p_skill_level_id,
1404 P_CATEGORY_ID => p_category_id,
1405 P_SUBCATEGORY => p_subcategory,
1406 P_PRODUCT_ID => p_product_id,
1407 P_PRODUCT_ORG_ID => p_product_org_id,
1408 P_PLATFORM_ID => p_platform_id,
1409 P_PLATFORM_ORG_ID => p_platform_org_id,
1410 P_PROBLEM_CODE => p_problem_code,
1411 P_COMPONENT_ID => p_component_id,
1412 P_SUBCOMPONENT_ID => p_subcomponent_id,
1413 P_OBJECT_VERSION_NUM => p_object_version_num,
1414 P_ATTRIBUTE1 => p_attribute1,
1415 P_ATTRIBUTE2 => p_attribute2,
1416 P_ATTRIBUTE3 => p_attribute3,
1417 P_ATTRIBUTE4 => p_attribute4,
1418 P_ATTRIBUTE5 => p_attribute5,
1419 P_ATTRIBUTE6 => p_attribute6,
1420 P_ATTRIBUTE7 => p_attribute7,
1421 P_ATTRIBUTE8 => p_attribute8,
1422 P_ATTRIBUTE9 => p_attribute9,
1423 P_ATTRIBUTE10 => p_attribute10,
1424 P_ATTRIBUTE11 => p_attribute11,
1425 P_ATTRIBUTE12 => p_attribute12,
1426 P_ATTRIBUTE13 => p_attribute13,
1427 P_ATTRIBUTE14 => p_attribute14,
1428 P_ATTRIBUTE15 => p_attribute15,
1429 P_ATTRIBUTE_CATEGORY => p_attribute_category,
1430 X_RETURN_STATUS => X_RETURN_STATUS,
1431 X_MSG_COUNT => X_MSG_COUNT,
1432 X_MSG_DATA => X_MSG_DATA
1433 );
1434
1435 IF (x_return_status <> fnd_api.g_ret_sts_success)
1436 THEN
1437 RAISE fnd_api.g_exc_error;
1438 END IF;
1439
1440 IF ( ( l_cascade_option = DO_CASCADE) OR ( l_cascade_option = CASCADE_ALL ) )
1441 THEN
1442 OPEN skill_curr( p_resource_skill_id);
1443 FETCH skill_curr INTO skill_curr_rec;
1444 IF ( skill_curr%FOUND )
1445 THEN
1446 CLOSE skill_curr;
1447 IF (SKILL_CURR_REC.COMPONENT_ID IS NOT NULL OR SKILL_CURR_REC.PROBLEM_CODE IS NOT NULL)
1448 THEN
1449 l_cascade_option := DONT_CASCADE;
1450 ELSE
1451 -- Update existing component level skill ratings
1452 IF ( l_cascade_option = CASCADE_ALL )
1453 THEN
1454 IF (SKILL_CURR_REC.PRODUCT_ID IS NULL) THEN
1455 UPDATE_EXISTING_PROD_SKILLS
1456 (
1457 P_API_VERSION => l_api_version,
1458 P_INIT_MSG_LIST => l_init_msg_list,
1459 P_COMMIT => l_commit,
1460 P_RESOURCE_ID => skill_curr_rec.resource_id,
1461 P_SKILL_LEVEL_ID => skill_curr_rec.skill_level_id,
1462 P_CATEGORY_ID => skill_curr_rec.category_id,
1463 X_RETURN_STATUS => X_RETURN_STATUS,
1464 X_MSG_COUNT => X_MSG_COUNT,
1465 X_MSG_DATA => X_MSG_DATA
1466 );
1467
1468 ELSE
1469 UPDATE_EXISTING_COMP_SKILLS
1470 (
1471 P_API_VERSION => l_api_version,
1472 P_INIT_MSG_LIST => l_init_msg_list,
1473 P_COMMIT => l_commit,
1474 P_RESOURCE_ID => skill_curr_rec.resource_id,
1475 P_SKILL_LEVEL_ID => skill_curr_rec.skill_level_id,
1476 P_CATEGORY_ID => skill_curr_rec.category_id,
1477 P_SUBCATEGORY => skill_curr_rec.subcategory,
1478 P_PRODUCT_ID => skill_curr_rec.product_id,
1479 P_PRODUCT_ORG_ID => skill_curr_rec.product_org_id,
1480 X_RETURN_STATUS => X_RETURN_STATUS,
1481 X_MSG_COUNT => X_MSG_COUNT,
1482 X_MSG_DATA => X_MSG_DATA
1483 );
1484 END IF;
1485 END IF;
1486 IF (x_return_status <> fnd_api.g_ret_sts_success)
1487 THEN
1488 RAISE fnd_api.g_exc_error;
1489 END IF;
1490
1491 -- Create component level skill rating for unrated component
1492 IF (SKILL_CURR_REC.PRODUCT_ID IS NULL) THEN
1493
1494 l_product_org_id := nvl(JTF_RESOURCE_UTL.GET_INVENTORY_ORG_ID, -1);
1495 CREATE_UNRATED_PROD_SKILLS(
1496 P_API_VERSION => l_api_version,
1497 P_INIT_MSG_LIST => l_init_msg_list,
1498 P_COMMIT => l_commit,
1499 P_RESOURCE_ID => skill_curr_rec.resource_id,
1500 P_SKILL_LEVEL_ID => skill_curr_rec.skill_level_id,
1501 P_CATEGORY_ID => skill_curr_rec.category_id,
1502 P_SUBCATEGORY => 'PRODUCT',
1503 P_PRODUCT_ID => skill_curr_rec.product_id,
1504 P_PRODUCT_ORG_ID => l_product_org_id,
1505 P_PLATFORM_ID => skill_curr_rec.platform_id,
1506 P_PLATFORM_ORG_ID => skill_curr_rec.platform_org_id,
1507 P_PROBLEM_CODE => skill_curr_rec.problem_code,
1508 P_ATTRIBUTE1 => skill_curr_rec.attribute1,
1509 P_ATTRIBUTE2 => skill_curr_rec.attribute2,
1510 P_ATTRIBUTE3 => skill_curr_rec.attribute3,
1511 P_ATTRIBUTE4 => skill_curr_rec.attribute4,
1512 P_ATTRIBUTE5 => skill_curr_rec.attribute5,
1513 P_ATTRIBUTE6 => skill_curr_rec.attribute6,
1514 P_ATTRIBUTE7 => skill_curr_rec.attribute7,
1515 P_ATTRIBUTE8 => skill_curr_rec.attribute8,
1516 P_ATTRIBUTE9 => skill_curr_rec.attribute9,
1517 P_ATTRIBUTE10 => skill_curr_rec.attribute10,
1518 P_ATTRIBUTE11 => skill_curr_rec.attribute11,
1519 P_ATTRIBUTE12 => skill_curr_rec.attribute12,
1520 P_ATTRIBUTE13 => skill_curr_rec.attribute13,
1521 P_ATTRIBUTE14 => skill_curr_rec.attribute14,
1522 P_ATTRIBUTE15 => skill_curr_rec.attribute15,
1523 P_ATTRIBUTE_CATEGORY => skill_curr_rec.attribute_category,
1524 X_RETURN_STATUS => X_RETURN_STATUS,
1525 X_MSG_COUNT => X_MSG_COUNT,
1526 X_MSG_DATA => X_MSG_DATA
1527 );
1528 ELSE
1529 CREATE_UNRATED_COMP_SKILLS(
1530 P_API_VERSION => l_api_version,
1531 P_INIT_MSG_LIST => l_init_msg_list,
1532 P_COMMIT => l_commit,
1533 P_RESOURCE_ID => skill_curr_rec.resource_id,
1534 P_SKILL_LEVEL_ID => skill_curr_rec.skill_level_id,
1535 P_CATEGORY_ID => skill_curr_rec.category_id,
1536 P_SUBCATEGORY => skill_curr_rec.subcategory,
1537 P_PRODUCT_ID => skill_curr_rec.product_id,
1538 P_PRODUCT_ORG_ID => skill_curr_rec.product_org_id,
1539 P_PLATFORM_ID => skill_curr_rec.platform_id,
1540 P_PLATFORM_ORG_ID => skill_curr_rec.platform_org_id,
1541 P_PROBLEM_CODE => skill_curr_rec.problem_code,
1542 P_ATTRIBUTE1 => skill_curr_rec.attribute1,
1543 P_ATTRIBUTE2 => skill_curr_rec.attribute2,
1544 P_ATTRIBUTE3 => skill_curr_rec.attribute3,
1545 P_ATTRIBUTE4 => skill_curr_rec.attribute4,
1546 P_ATTRIBUTE5 => skill_curr_rec.attribute5,
1547 P_ATTRIBUTE6 => skill_curr_rec.attribute6,
1548 P_ATTRIBUTE7 => skill_curr_rec.attribute7,
1549 P_ATTRIBUTE8 => skill_curr_rec.attribute8,
1550 P_ATTRIBUTE9 => skill_curr_rec.attribute9,
1551 P_ATTRIBUTE10 => skill_curr_rec.attribute10,
1552 P_ATTRIBUTE11 => skill_curr_rec.attribute11,
1553 P_ATTRIBUTE12 => skill_curr_rec.attribute12,
1554 P_ATTRIBUTE13 => skill_curr_rec.attribute13,
1555 P_ATTRIBUTE14 => skill_curr_rec.attribute14,
1556 P_ATTRIBUTE15 => skill_curr_rec.attribute15,
1557 P_ATTRIBUTE_CATEGORY => skill_curr_rec.attribute_category,
1558 X_RETURN_STATUS => X_RETURN_STATUS,
1559 X_MSG_COUNT => X_MSG_COUNT,
1560 X_MSG_DATA => X_MSG_DATA
1561 );
1562 END IF;
1563 IF (x_return_status <> fnd_api.g_ret_sts_success)
1564 THEN
1565 RAISE fnd_api.g_exc_error;
1566 END IF;
1567 END IF;
1568 END IF;
1569 IF skill_curr%ISOPEN THEN
1570 CLOSE skill_curr;
1571 END IF;
1572
1573 END IF;
1574
1575 --standard commit
1576 IF fnd_api.to_boolean (p_commit)
1577 THEN
1578 COMMIT WORK;
1579 END IF;
1580
1581 EXCEPTION
1582 WHEN fnd_api.g_exc_unexpected_error
1583 THEN
1584 IF skill_curr%ISOPEN THEN
1585 CLOSE skill_curr;
1586 END IF;
1587 ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;
1588 x_return_status := fnd_api.g_ret_sts_unexp_error;
1589 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1590 WHEN fnd_api.g_exc_error
1591 THEN
1592 IF skill_curr%ISOPEN THEN
1593 CLOSE skill_curr;
1594 END IF;
1595 ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;
1596 x_return_status := fnd_api.g_ret_sts_error;
1597 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1598 WHEN OTHERS
1599 THEN
1600 IF skill_curr%ISOPEN THEN
1601 CLOSE skill_curr;
1602 END IF;
1603 ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;
1604 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1605 fnd_message.set_token('P_SQLCODE',SQLCODE);
1606 fnd_message.set_token('P_SQLERRM',SQLERRM);
1607 fnd_message.set_token('P_API_NAME', l_api_name);
1608 FND_MSG_PUB.add;
1609 x_return_status := fnd_api.g_ret_sts_unexp_error;
1610 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1611
1612 END UPDATE_RESOURCE_SKILLS;
1613 END jtf_rs_resource_skills_pub;