[Home] [Help]
PACKAGE BODY: APPS.CSF_REQUIRED_SKILLS_PKG
Source
1 PACKAGE BODY CSF_REQUIRED_SKILLS_PKG AS
2 /* $Header: CSFPRQSB.pls 120.1 2006/05/12 02:04:05 srengana noship $ */
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
151 IS
152 l_api_name constant varchar2(30) := 'create_row';
153 l_api_version constant number := 1.0;
154 l_req_skill_id number;
155 BEGIN
156 -- standard call to check for call compatibility
157 if not fnd_api.compatible_api_call (
158 l_api_version
159 , p_api_version
160 , l_api_name
161 , g_package_name
162 )
163 then
164 raise fnd_api.g_exc_unexpected_error;
165 end if;
166
167 -- initialize message list if p_init_msg_list is set to true
168 if fnd_api.to_boolean(p_init_msg_list)
169 then
170 fnd_msg_pub.initialize;
171 end if;
172
173 /* Check if a required skill to be inserted does exist for a
174 particular task (has_skill_id with has_skill_type = TASKS). If it
175 does not exist then proceed to insert the record. Otherwise, DO NOT
176 INSERT!!!!!!
177 */
178 if not required_skill_exists ( p_task_id
179 , p_skill_id
180 , p_skill_type_id)
181 then
182
183 /* Checks if task exist in JTF_TASK_B table. */
184 If not task_exists (p_task_id) then
185 X_return_status := fnd_api.g_ret_sts_error;
186 X_msg_count := 1;
187 X_msg_data := 'Task Id ('||to_char(p_task_id)||
188 ') does not exist in JTF_TASKS_B table.';
189 return;
190 end if;
191
192 /* Checks if skill type and id exist. */
193 If not skill_exists (p_skill_type_id, p_skill_id) then
194 X_return_status := fnd_api.g_ret_sts_error;
195 X_msg_count := 1;
196 X_msg_data := 'Combination of skill id ('||p_skill_id||') '||
197 'and skill type id ('||p_skill_type_id||') '||
198 'does not exist in CSF_SKILLS_B table.';
199 return;
200 end if;
201
202 /* Checks if skill level exists in CSF_SKILL_LEVELS_B. */
203 If not skill_level_exists (p_skill_level_id) then
204 X_return_status := fnd_api.g_ret_sts_error;
205 X_msg_count := 1;
206 X_msg_data := 'Skill level id ('||p_skill_level_id||') '||
207 'does not exist in CSF_SKILL_LEVELS_B table.';
208 return;
209 end if;
210
211 /* When all checks are ok, insert record
212 into CSF_REQUIRED_SKILLS table. */
213
214 select csf_required_skills_b_s1.nextval
215 into l_req_skill_id
216 from dual;
217
218 insert into csf_required_skills_b
219 ( required_skill_id
220 , skill_type_id
221 , skill_id
222 , has_skill_type
223 , has_skill_id
224 , skill_level_id
225 , skill_required_flag
226 , level_required_flag
227 , disabled_flag
228 , start_date_active
229 , end_date_active
230 , object_version_number
231 , created_by
232 , creation_date
233 , last_updated_by
234 , last_update_date
235 , last_update_login
236 , attribute1
237 , attribute2
238 , attribute3
239 , attribute4
240 , attribute5
241 , attribute6
242 , attribute7
243 , attribute8
244 , attribute9
245 , attribute10
246 , attribute11
247 , attribute12
248 , attribute13
249 , attribute14
250 , attribute15
251 , attribute_category)
252 values
253 ( l_req_skill_id
254 , p_skill_type_id
255 , p_skill_id
256 , 'TASK'
257 , p_task_id
258 , p_skill_level_id
259 , null
260 , null
261 , null
262 , sysdate
263 , null
264 , 1
265 , fnd_global.user_id
266 , sysdate
267 , fnd_global.user_id
268 , sysdate
269 , fnd_global.login_id
270 , null
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
287 if fnd_api.to_boolean(p_commit)
288 then
289 commit;
290 end if;
291
292 x_return_status := fnd_api.g_ret_sts_success;
293
294 --Bug Fixed 3884408
295 --When the required skill to be inserted into csf_required_skills_b table already exist for
296 --the given task, Procedure create_row() must return status 'E' to calling function.
297 else
298 if g_called_from_hook = 'T' then
299 X_return_status := fnd_api.g_ret_sts_success;
300 else
301 X_return_status := fnd_api.g_ret_sts_error;
302 X_msg_count := 1;
303 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';
304 end if;
305 end if;
306 EXCEPTION
307 when fnd_api.g_exc_error then
308 x_return_status := fnd_api.g_ret_sts_error;
309
310 fnd_msg_pub.count_and_get ( p_count => x_msg_count
311 , p_data => x_msg_data );
312
313 when fnd_api.g_exc_unexpected_error then
314 x_return_status := fnd_api.g_ret_sts_unexp_error;
315 fnd_msg_pub.count_and_get ( p_count => x_msg_count
316 , p_data => x_msg_data );
317
318 when others then
319 x_return_status := fnd_api.g_ret_sts_unexp_error;
320 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
321 then
322 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
323 end if;
324 fnd_msg_pub.count_and_get ( p_count => x_msg_count
325 , p_data => x_msg_data );
326 END create_row;
327
328 PROCEDURE create_row_based_on_product ( x_return_status out nocopy varchar2 )
329 IS
330 l_task_id number ;
331
332 Cursor c_ProdSkill is
333 select mtl.inventory_item_id
334 from jtf_tasks_b tb
335 , cs_incidents_all_b sr
336 , mtl_system_items_kfv mtl
337 where mtl.organization_id = fnd_profile.value('CSF_INVENTORY_ORG')
338 and sysdate between nvl(mtl.start_date_active,sysdate) and nvl(mtl.end_date_active,sysdate)
339 and mtl.inventory_item_id = sr.inventory_item_id
340 and sr.incident_id = tb.source_object_id
341 and tb.task_id = l_task_id;
342
343 l_api_name constant varchar2(30) := 'create_row_based_on_product';
344 l_return_status varchar2(50);
345 l_msg_count number;
346 l_msg_data varchar2 (1000);
347
348 l_skill_id csf_skills_b.skill_id%type;
349 l_skill_level_id csf_skill_levels_b.skill_level_id%type;
350
351 BEGIN
352 -- Check if required skill based on product/inventroy item
353 -- has to be created.
354 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
355 g_called_from_hook := 'T';
356 if nvl(fnd_profile.value('CSF_SR_ITEM_TO_TASK_REQ'), 'N') = 'Y'
357 then
358 -- Takes the Product Skills from the inv table
359 open c_ProdSkill;
360 fetch c_ProdSkill into l_skill_id;
361 close c_ProdSkill;
362 if l_skill_id is not null
363 then
364 -- retrieving default values for skill type and level.
365 l_skill_level_id := fnd_profile.value('CSF_DFLT_SKILL_LEVEL');
366
367 -- when either skill level is null do not proceed.
368 if nvl(l_skill_level_id, null) is not null
369 then
370
371 create_row
372 ( p_api_version => 1
373 , p_init_msg_list => FND_API.G_FALSE
374 , p_commit => FND_API.G_FALSE
375 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
376 , x_return_status => l_return_status
377 , x_msg_count => l_msg_count
378 , x_msg_data => l_msg_data
379 , p_task_id => l_task_id
380 , p_skill_type_id => 2 -- This is the id for Product Skills.
381 , p_skill_id => l_skill_id
382 , p_skill_level_id => l_skill_level_id);
383
384 end if;
385 end if;
386 end if;
387 g_called_from_hook := 'F';
388 x_return_status := nvl( l_return_status
389 ,fnd_api.g_ret_sts_success );
390 EXCEPTION
391 when others then
392 x_return_status := fnd_api.g_ret_sts_unexp_error;
393 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
394 then
395 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
396 end if;
397 END create_row_based_on_product;
398
399
400 PROCEDURE create_row_from_tpl ( x_return_status out nocopy varchar2) IS
401 l_task_id number;
402 l_tpl_id number;
403 l_tpl_grp_id number;
404
405 Cursor c_skills is
406 Select skill_type_id
407 , skill_id
408 , skill_level_id
409 from csf_required_skills_b
410 where has_skill_type = 'TASK TEMPLATE'
411 and has_skill_id = l_tpl_id;
412
413 l_api_name constant varchar2(30) := 'create_row_from_tpl';
414 l_return_status varchar2(50);
415 l_msg_count number;
416 l_msg_data varchar2 (1000);
417
418 BEGIN
419 /* Retrieve skills attached to the task template and
420 call create_task_req_skill procedure to insert the
421 record. */
422 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
423 l_tpl_id := jtf_tasks_pub.p_task_user_hooks.template_id;
424 l_tpl_grp_id := jtf_tasks_pub.p_task_user_hooks.template_group_id;
425 g_called_from_hook := 'T';
426
427
428 for r_skills in c_skills loop
429 create_row
430 ( p_api_version => 1
431 , p_init_msg_list => FND_API.G_FALSE
432 , p_commit => FND_API.G_FALSE
433 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
434 , x_return_status => l_return_status
435 , x_msg_count => l_msg_count
436 , x_msg_data => l_msg_data
437 , p_task_id => l_task_id
438 , p_skill_type_id => r_skills.skill_type_id
439 , p_skill_id => r_skills.skill_id
440 , p_skill_level_id => r_skills.skill_level_id);
441
442 If l_return_status = fnd_api.g_ret_sts_error -- there's an error.
443 Then
444 Exit;
445 End if;
446 end loop;
447 g_called_from_hook := 'F';
448 x_return_status := nvl( l_return_status
449 ,fnd_api.g_ret_sts_success );
450 EXCEPTION
451 when others then
452 x_return_status := fnd_api.g_ret_sts_unexp_error;
453 if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
454 then
455 fnd_msg_pub.add_exc_msg ( g_package_name, l_api_name );
456 end if;
457 END create_row_from_tpl;
458
459
460 END CSF_REQUIRED_SKILLS_PKG;