[Home] [Help]
PACKAGE BODY: APPS.CSF_REQUIRED_SKILLS_PKG
Source
1 PACKAGE BODY CSF_REQUIRED_SKILLS_PKG AS
2 /* $Header: CSFPRQSB.pls 120.1.12010000.4 2009/09/04 11:32:15 ramchint ship $ */
3
4
5 FUNCTION task_tpl_exists (p_task_tpl_id in number
6 , p_task_tpl_grp_id number)
7 RETURN boolean
8 IS
9 Cursor c_tpl is
10 Select 1
11 From jtf_task_templates_b
12 Where task_template_id = p_task_tpl_id
13 and task_group_id = p_task_tpl_grp_id;
14 l_dummy_var number;
15 BEGIN
16 /* Check if the task exists. */
17 Open c_tpl;
18 Fetch c_tpl into l_dummy_var;
19 If c_tpl%notfound then
20 Close c_tpl;
21 Return false ;
22 end if;
23 close c_tpl;
24 return true;
25 END task_tpl_exists;
26
27 FUNCTION task_exists (p_task_id in number)
28 RETURN boolean
29 IS
30 Cursor c_task is
31 Select 1
32 From jtf_tasks_b
33 Where task_id = p_task_id;
34 l_dummy_var number;
35 BEGIN
36 /* Check if the task exists. */
37 Open c_task;
38 Fetch c_task into l_dummy_var;
39 If c_task%notfound then
40 Close c_task;
41 Return false ;
42 end if;
43 close c_task;
44 return true;
45 END task_exists;
46
47 --Bug fixed 3560830
48
49 FUNCTION required_skill_exists ( p_task_id in number
50 , p_skill_id in number
51 , p_skill_type_id in number)
52 RETURN boolean IS
53 cursor c_reqSkill is
54 select 1
55 from csf_required_skills_b
56 where skill_id = p_skill_id
57 and skill_type_id = p_skill_type_id
58 and has_skill_type = 'TASK'
59 and sysdate >= trunc(start_date_active)
60 and (sysdate <= trunc(end_date_active)+1 or end_date_active is null)
61 and has_skill_id = p_task_id;
62
63 l_dummy_var number := null;
64 BEGIN
65 open c_reqSkill;
66 fetch c_reqSkill into l_dummy_var;
67 if l_dummy_var is not null then
68 return true; -- means i exist.
69 end if;
70 return false; -- means it didn't exist.
71 END required_skill_exists;
72
73
74 FUNCTION skill_exists ( p_skill_type_id in number
75 , p_skill_id in number)
76 RETURN boolean
77 IS
78 cursor c_obj ( p_skill_type_id varchar2 )
79 is
80 select 'SELECT '||name_number_column||', '||data_column||
81 ' FROM '||from_clause||
82 ' WHERE '||where_clause||' AND '||key_column||' = :b_skill_id'
83 from csf_skill_types_b
84 where skill_type_id = p_skill_type_id;
85
86 l_qry varchar2(2000);
87 l_cur integer;
88 l_name varchar2(2000) := null;
89 l_desc varchar2(2000) := null;
90 l_cnt integer;
91
92 BEGIN
93 open c_obj ( p_skill_type_id );
94 fetch c_obj into l_qry;
95 if c_obj%notfound
96 then
97 close c_obj;
98 return false;
99 end if;
100 close c_obj;
101
102 -- declare a cursor for dynamic SQL
103 l_cur := dbms_sql.open_cursor;
104 -- parse query (constant 1 = dbms_sql.native)
105 dbms_sql.parse(l_cur,l_qry,1);
106 -- bind query input value
107 dbms_sql.bind_variable(l_cur,'b_skill_id', p_skill_id);
108 l_cnt := dbms_sql.execute_and_fetch(l_cur,false);
109 dbms_sql.close_cursor(l_cur);
110 if l_cnt <> 1
111 then
112 return false;
113 end if;
114 return true;
115 END skill_exists;
116
117 FUNCTION skill_level_exists ( p_skill_level_id in number)
118 RETURN boolean
119 IS
120 Cursor c_skill_lvl is
121 Select 1
122 From csf_skill_levels_b
123 Where skill_level_id = p_skill_level_id
124 and (sysdate <= trunc(end_date_active)+1
125 or end_date_active is null);
126 l_dummy_var number;
127 BEGIN
128 Open c_skill_lvl;
129 Fetch c_skill_lvl into l_dummy_var;
130 If c_skill_lvl %notfound then
131 Close c_skill_lvl;
132 Return false ;
133 end if;
134 close c_skill_lvl;
135 return true;
136 END skill_level_exists;
137
138 PROCEDURE create_row
139 ( p_api_version in number
140 , p_init_msg_list in varchar2
141 , p_commit in varchar2
142 , p_validation_level in number
143 , x_return_status out nocopy varchar2
144 , x_msg_count out nocopy number
145 , x_msg_data out nocopy varchar2
146 , p_task_id in number
147 , p_skill_type_id in number
148 , p_skill_id in number
149 , p_skill_level_id in number
150 , p_disabled_flag in varchar2 default null) --new parameter added to forward port for bug 6978751
151
152 IS
153 l_api_name constant varchar2(30) := 'create_row';
154 l_api_version constant number := 1.0;
155 l_req_skill_id number;
156 BEGIN
157 -- standard call to check for call compatibility
158 if not fnd_api.compatible_api_call (
159 l_api_version
160 , p_api_version
161 , l_api_name
162 , g_package_name
163 )
164 then
165 raise fnd_api.g_exc_unexpected_error;
166 end if;
167
168 -- initialize message list if p_init_msg_list is set to true
169 if fnd_api.to_boolean(p_init_msg_list)
170 then
171 fnd_msg_pub.initialize;
172 end if;
173
174 /* Check if a required skill to be inserted does exist for a
175 particular task (has_skill_id with has_skill_type = TASKS). If it
176 does not exist then proceed to insert the record. Otherwise, DO NOT
177 INSERT!!!!!!
178 */
179 if not required_skill_exists ( p_task_id
180 , p_skill_id
181 , p_skill_type_id)
182 then
183
184 /* Checks if task exist in JTF_TASK_B table. */
185 If not task_exists (p_task_id) then
186 X_return_status := fnd_api.g_ret_sts_error;
187 X_msg_count := 1;
188 X_msg_data := 'Task Id ('||to_char(p_task_id)||
189 ') does not exist in JTF_TASKS_B table.';
190 return;
191 end if;
192
193 /* Checks if skill type and id exist. */
194 If not skill_exists (p_skill_type_id, p_skill_id) then
195 X_return_status := fnd_api.g_ret_sts_error;
196 X_msg_count := 1;
197 X_msg_data := 'Combination of skill id ('||p_skill_id||') '||
198 'and skill type id ('||p_skill_type_id||') '||
199 'does not exist in CSF_SKILLS_B table.';
200 return;
201 end if;
202
203 /* Checks if skill level exists in CSF_SKILL_LEVELS_B. */
204 If not skill_level_exists (p_skill_level_id) then
205 X_return_status := fnd_api.g_ret_sts_error;
206 X_msg_count := 1;
207 X_msg_data := 'Skill level id ('||p_skill_level_id||') '||
208 'does not exist in CSF_SKILL_LEVELS_B table.';
209 return;
210 end if;
211
212 /* When all checks are ok, insert record
213 into CSF_REQUIRED_SKILLS table. */
214
215 select csf_required_skills_b_s1.nextval
216 into l_req_skill_id
217 from dual;
218
219 insert into csf_required_skills_b
220 ( required_skill_id
221 , skill_type_id
222 , skill_id
223 , has_skill_type
224 , has_skill_id
225 , skill_level_id
226 , skill_required_flag
227 , level_required_flag
228 , disabled_flag
229 , start_date_active
230 , end_date_active
231 , object_version_number
232 , created_by
233 , creation_date
234 , last_updated_by
235 , last_update_date
236 , last_update_login
237 , attribute1
238 , attribute2
239 , attribute3
240 , attribute4
241 , attribute5
242 , attribute6
243 , attribute7
244 , attribute8
245 , attribute9
246 , attribute10
247 , attribute11
248 , attribute12
249 , attribute13
250 , attribute14
251 , attribute15
252 , attribute_category)
253 values
254 ( l_req_skill_id
255 , p_skill_type_id
256 , p_skill_id
257 , 'TASK'
258 , p_task_id
259 , p_skill_level_id
260 , null
261 , null
262 , p_disabled_flag
263 , sysdate
264 , null
265 , 1
266 , fnd_global.user_id
267 , sysdate
268 , fnd_global.user_id
269 , sysdate
270 , fnd_global.login_id
271 , null
272 , null
273 , null
274 , null
275 , null
276 , null
277 , null
278 , null
279 , null
280 , null
281 , null
282 , null
283 , null
284 , null
285 , null
286 , null);
287
288 if fnd_api.to_boolean(p_commit)
289 then
290 commit;
291 end if;
292
293 x_return_status := fnd_api.g_ret_sts_success;
294
295 --Bug Fixed 3884408
296 --When the required skill to be inserted into csf_required_skills_b table already exist for
297 --the given task, Procedure create_row() must return status 'E' to calling function.
298 else
299 if g_called_from_hook = 'T' then
300 X_return_status := fnd_api.g_ret_sts_success;
301 else
302 X_return_status := fnd_api.g_ret_sts_error;
303 X_msg_count := 1;
304 X_msg_data := 'Combination of Skill id ('||p_skill_id||') and Skill type id ('||p_skill_type_id||') to be inserted does exist for Task ('||to_char(p_task_id)|| ') in CSF_REQUIRED_SKILLS_B table';
305 end if;
306 end if;
307 EXCEPTION
308 when fnd_api.g_exc_error then
309 x_return_status := fnd_api.g_ret_sts_error;
310
311 fnd_msg_pub.count_and_get ( p_count => x_msg_count
312 , p_data => x_msg_data );
313
314 when fnd_api.g_exc_unexpected_error then
315 x_return_status := fnd_api.g_ret_sts_unexp_error;
316 fnd_msg_pub.count_and_get ( p_count => x_msg_count
317 , p_data => x_msg_data );
318
319 when others then
320 x_return_status := fnd_api.g_ret_sts_unexp_error;
321 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
322 then
323 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
324 end if;
325 fnd_msg_pub.count_and_get ( p_count => x_msg_count
326 , p_data => x_msg_data );
327 END create_row;
328
329 PROCEDURE create_row_based_on_product ( x_return_status out nocopy varchar2 )
330 IS
331 l_task_id number ;
332
333 Cursor c_ProdSkill is
334 select mtl.inventory_item_id
335 from jtf_tasks_b tb
336 , cs_incidents_all_b sr
337 , mtl_system_items_kfv mtl
338 where mtl.organization_id = fnd_profile.value('CSF_INVENTORY_ORG')
339 and sysdate between nvl(mtl.start_date_active,sysdate) and nvl(mtl.end_date_active,sysdate)
340 and mtl.inventory_item_id = sr.inventory_item_id
341 and sr.incident_id = tb.source_object_id
342 and tb.task_id = l_task_id;
343
344 cursor c_prod_cat_skill
345 is
346 SELECT c.category_id
347 FROM mtl_category_set_valid_cats ic,
348 mtl_categories_kfv c,
349 mtl_category_sets_vl cs,
350 mtl_categories_tl t,
351 cs_incidents_all_b sr,
352 jtf_tasks_b tb
353 WHERE ic.category_id = c.category_id
354 AND t.category_id (+) = c.category_id
355 AND t.language (+) = userenv('LANG')
356 AND ic.category_set_id = cs.category_set_id
357 AND ic.category_set_id = fnd_profile.value('CS_SR_DEFAULT_CATEGORY_SET')
358 AND Sysdate <= NVL(c.disable_date, Sysdate)
359 AND c.category_id = sr.category_id
360 AND sr.incident_id = tb.source_object_id
361 and tb.task_id = l_task_id;
362
363 l_api_name constant varchar2(30) := 'create_row_based_on_product';
364 l_return_status varchar2(50);
365 l_msg_count number;
366 l_msg_data varchar2 (1000);
367
368 l_skill_id csf_skills_b.skill_id%type;
369 l_skill_id1 csf_skills_b.skill_id%type;
370 l_skill_level_id csf_skill_levels_b.skill_level_id%type;
371
372 BEGIN
373 -- Check if required skill based on product/inventroy item
374 -- has to be created.
375 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
376 g_called_from_hook := 'T';
377 if nvl(fnd_profile.value('CSF_SR_ITEM_TO_TASK_REQ'), 'N') = 'Y'
378 then
379 -- Takes the Product Skills from the inv table
380 open c_ProdSkill;
381 fetch c_ProdSkill into l_skill_id;
382 close c_ProdSkill;
383 if l_skill_id is not null
384 then
385 -- retrieving default values for skill type and level.
386 l_skill_level_id := fnd_profile.value('CSF_DFLT_SKILL_LEVEL');
387
388 -- when either skill level is null do not proceed.
389 if nvl(l_skill_level_id, null) is not null
390 then
391
392 create_row
393 ( p_api_version => 1
394 , p_init_msg_list => FND_API.G_FALSE
395 , p_commit => FND_API.G_FALSE
396 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
397 , x_return_status => l_return_status
398 , x_msg_count => l_msg_count
399 , x_msg_data => l_msg_data
400 , p_task_id => l_task_id
401 , p_skill_type_id => 2 -- This is the id for Product Skills.
402 , p_skill_id => l_skill_id
403 , p_skill_level_id => l_skill_level_id);
404
405 end if;
406 end if;
407 end if;
408 if nvl(fnd_profile.value('CSF_SR_ITEM_CAT_TO_TASK_REQ'), 'N') = 'Y'
409 then
410 open c_Prod_cat_skill;
411 fetch c_Prod_cat_Skill into l_skill_id1;
412 close c_Prod_cat_Skill;
413
414 if l_skill_id1 is not null
415 then
416 -- retrieving default values for skill type and level.
417 l_skill_level_id := fnd_profile.value('CSF_DFLT_SKILL_LEVEL');
418
419 -- when either skill level is null do not proceed.
420 if nvl(l_skill_level_id, null) is not null
421 then
422
423 create_row
424 ( p_api_version => 1
425 , p_init_msg_list => FND_API.G_FALSE
426 , p_commit => FND_API.G_FALSE
427 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
428 , x_return_status => l_return_status
429 , x_msg_count => l_msg_count
430 , x_msg_data => l_msg_data
431 , p_task_id => l_task_id
432 , p_skill_type_id => 3 -- This is the id for Product Skills.
433 , p_skill_id => l_skill_id1
434 , p_skill_level_id => l_skill_level_id);
435
436 end if;
437 end if;
438 end if;
439 g_called_from_hook := 'F';
440 x_return_status := nvl( l_return_status
441 ,fnd_api.g_ret_sts_success );
442 EXCEPTION
443 when others then
444 x_return_status := fnd_api.g_ret_sts_unexp_error;
445 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
446 then
447 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
448 end if;
449 END create_row_based_on_product;
450
451
452 PROCEDURE create_row_from_tpl ( x_return_status out nocopy varchar2) IS
453 l_task_id number;
454 l_tpl_id number;
455 l_tpl_grp_id number;
456
457 Cursor c_skills is
458 Select skill_type_id
459 , skill_id
460 , skill_level_id
461 from csf_required_skills_b
462 where has_skill_type = 'TASK TEMPLATE'
463 and has_skill_id = l_tpl_id;
464
465 l_api_name constant varchar2(30) := 'create_row_from_tpl';
466 l_return_status varchar2(50);
467 l_msg_count number;
468 l_msg_data varchar2 (1000);
469
470 BEGIN
471 /* Retrieve skills attached to the task template and
472 call create_task_req_skill procedure to insert the
473 record. */
474 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
475 l_tpl_id := jtf_tasks_pub.p_task_user_hooks.template_id;
476 l_tpl_grp_id := jtf_tasks_pub.p_task_user_hooks.template_group_id;
477 g_called_from_hook := 'T';
478
479
480 for r_skills in c_skills loop
481 create_row
482 ( p_api_version => 1
483 , p_init_msg_list => FND_API.G_FALSE
484 , p_commit => FND_API.G_FALSE
485 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
486 , x_return_status => l_return_status
487 , x_msg_count => l_msg_count
488 , x_msg_data => l_msg_data
489 , p_task_id => l_task_id
490 , p_skill_type_id => r_skills.skill_type_id
491 , p_skill_id => r_skills.skill_id
492 , p_skill_level_id => r_skills.skill_level_id);
493
494 If l_return_status = fnd_api.g_ret_sts_error -- there's an error.
495 Then
496 Exit;
497 End if;
498 end loop;
499 g_called_from_hook := 'F';
500 x_return_status := nvl( l_return_status
501 ,fnd_api.g_ret_sts_success );
502 EXCEPTION
503 when others then
504 x_return_status := fnd_api.g_ret_sts_unexp_error;
505 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
506 then
507 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
508 end if;
509 END create_row_from_tpl;
510
511
512 --
513 --new procedure added for bug 6978751
514 --
515 PROCEDURE create_row_for_child_tasks ( x_return_status out nocopy varchar2 )
516 IS
517
518 cursor c_parent_task_id(p_task_id number)
519 is
520 select a.parent_task_id
521 from jtf_tasks_b a, jtf_task_statuses_b b
522 where a.task_id = p_task_id
523 and a.task_status_id = b.task_status_id
524 and nvl(task_split_flag, 'N') = 'D'
525 and nvl(b.cancelled_flag, 'N') <> 'Y';
526
527 cursor skills_to_create(p_task_id number)
528 is
529 select skill_type_id, skill_id, skill_level_id, disabled_flag
530 from csf_required_skills_v
531 where has_skill_id = p_task_id
532 and skill_type_id <>2;
533
534 l_api_name constant varchar2(30) := 'create_row_for_child_tasks';
535 l_task_split_flag jtf_tasks_b.task_split_flag%TYPE;
536 l_task_id number ;
537 l_parent_task_id number;
538 l_return_status varchar2(50);
539 l_msg_count number;
540 l_msg_data varchar2 (1000);
541 l_skill_type_id csf_required_skills_b.skill_type_id%type;
542 l_skill_id csf_required_skills_b.skill_id%type;
543 l_skill_level_id csf_required_skills_b.skill_level_id%type;
544 l_disabled_flag csf_required_skills_b.disabled_flag%type;
545
546 BEGIN
547
548 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
549 g_called_from_hook := 'T';
550
551 open c_parent_task_id(l_task_id);
552 fetch c_parent_task_id into l_parent_task_id;
553 if c_parent_task_id%FOUND
554 then
555 open skills_to_create(l_parent_task_id);
556 loop
557 fetch skills_to_create into l_skill_type_id, l_skill_id, l_skill_level_id, l_disabled_flag;
558 EXIT WHEN skills_to_create%NOTFOUND;
559 -- retrieving default values for skill type and level.
560 -- l_skill_level_id := fnd_profile.value('CSF_DFLT_SKILL_LEVEL');
561 create_row
562 ( p_api_version => 1
563 , p_init_msg_list => FND_API.G_FALSE
564 , p_commit => FND_API.G_FALSE
565 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
566 , x_return_status => l_return_status
567 , x_msg_count => l_msg_count
568 , x_msg_data => l_msg_data
569 , p_task_id => l_task_id
570 , p_skill_type_id => l_skill_type_id
571 , p_skill_id => l_skill_id
572 , p_skill_level_id => l_skill_level_id
573 , p_disabled_flag => l_disabled_flag);
574 end loop;
575 end if;
576 close c_parent_task_id;
577
578 g_called_from_hook := 'F';
579 x_return_status := nvl( l_return_status ,fnd_api.g_ret_sts_success );
580 EXCEPTION
581 when others then
582 x_return_status := fnd_api.g_ret_sts_unexp_error;
583 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
584 then
585 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
586 end if;
587 END create_row_for_child_tasks;
588
589
590 END CSF_REQUIRED_SKILLS_PKG;