[Home] [Help]
PACKAGE BODY: APPS.CSF_SKILLS_PKG
Source
1 PACKAGE BODY csf_skills_pkg AS
2 /* $Header: CSFPSKLB.pls 120.1 2006/03/01 02:00:51 ipananil noship $ */
3
4 -- ---------------------------------
5 -- private global package variables
6 -- ---------------------------------
7 g_user_id number;
8 g_login_id number;
9
10 -- ---------------------------------
11 -- public API's
12 -- ---------------------------------
13 PROCEDURE load_skill_type
14 ( p_skill_type_id in number
15 , p_rating_scale_id in number
16 , p_start_date_active in date
17 , p_end_date_active in date
18 , p_last_update_date in date
19 , p_seeded_flag in varchar2
20 , p_key_column in varchar2
21 , p_data_column in varchar2
22 , p_name_number_column in varchar2
23 , p_from_clause in varchar2
24 , p_where_clause in varchar2
25 , p_order_by_clause in varchar2
26 , p_object_version_number in number
27 , p_attribute1 in varchar2
28 , p_attribute2 in varchar2
29 , p_attribute3 in varchar2
30 , p_attribute4 in varchar2
31 , p_attribute5 in varchar2
32 , p_attribute6 in varchar2
33 , p_attribute7 in varchar2
34 , p_attribute8 in varchar2
35 , p_attribute9 in varchar2
36 , p_attribute10 in varchar2
37 , p_attribute11 in varchar2
38 , p_attribute12 in varchar2
39 , p_attribute13 in varchar2
40 , p_attribute14 in varchar2
41 , p_attribute15 in varchar2
42 , p_attribute_category in varchar2
43 , p_name in varchar2
44 , p_description in varchar2 )
45 IS
46 cursor skillType_cur (b_skill_type_id number) is
47 select skill_type_id, last_update_date
48 from csf_skill_types_b
49 where skill_type_id = b_skill_type_id;
50
51 l_skill_type_id number;
52 l_last_update_date date;
53 l_rowid varchar2(100);
54 l_obj_ver number;
55 BEGIN
56 open skillType_cur ( p_skill_type_id );
57 fetch skillType_cur into l_skill_type_id, l_last_update_date;
58 if skillType_cur%NOTFOUND then
59 l_skill_type_id := p_skill_type_id;
60 create_skill_type( l_rowid
61 , l_skill_type_id
62 , p_rating_scale_id
63 , p_start_date_active
64 , p_end_date_active
65 , p_seeded_flag
66 , p_key_column
67 , p_data_column
68 , p_name_number_column
69 , p_from_clause
70 , p_where_clause
71 , p_order_by_clause
72 , l_obj_ver
73 , p_attribute1
74 , p_attribute2
75 , p_attribute3
76 , p_attribute4
77 , p_attribute5
78 , p_attribute6
79 , p_attribute7
80 , p_attribute8
81 , p_attribute9
82 , p_attribute10
83 , p_attribute11
84 , p_attribute12
85 , p_attribute13
86 , p_attribute14
87 , p_attribute15
88 , p_attribute_category
89 , p_name
90 , p_description);
91 else
92 if p_last_update_date >= l_last_update_date then
93 update csf_skill_types_b
94 set rating_scale_id = p_rating_scale_id
95 , start_date_active = p_start_date_active
96 , end_date_active = p_end_date_active
97 , seeded_flag = p_seeded_flag
98 , object_version_number = object_version_number + 1
99 , key_column = p_key_column
100 , data_column = p_data_column
101 , name_number_column = p_name_number_column
102 , from_clause = p_from_clause
103 , where_clause = p_where_clause
104 , order_by_clause = p_order_by_clause
105 , attribute1 = p_attribute1
106 , attribute2 = p_attribute2
107 , attribute3 = p_attribute3
108 , attribute4 = p_attribute4
109 , attribute5 = p_attribute5
110 , attribute6 = p_attribute6
111 , attribute7 = p_attribute7
112 , attribute8 = p_attribute8
113 , attribute9 = p_attribute9
114 , attribute10 = p_attribute10
115 , attribute11 = p_attribute11
116 , attribute12 = p_attribute12
117 , attribute13 = p_attribute13
118 , attribute14 = p_attribute14
119 , attribute15 = p_attribute15
120 , attribute_category = p_attribute_category
121 , last_update_date = p_last_update_date
122 , last_updated_by = g_user_id
123 , last_update_login = g_login_id
124 where skill_type_id = l_skill_type_id;
125
126 update csf_skill_types_tl
127 set name = p_name
128 , description = p_description
129 , last_update_date = p_last_update_date
130 , last_updated_by = g_user_id
131 , last_update_login = g_login_id
132 , source_lang = userenv('LANG')
133 where skill_type_id = l_skill_type_id
134 and userenv('LANG') in (language, source_lang);
135 end if;
136 end if;
137 close skillType_cur;
138 END load_skill_type;
139
140
141 PROCEDURE create_skill_type
142 ( x_rowid in out nocopy varchar2
143 , x_skill_type_id in out nocopy number
144 , x_rating_scale_id in number
145 , x_start_date_active in date
146 , x_end_date_active in date
147 , x_seeded_flag in varchar2 default null
148 , x_key_column in varchar2 default null
149 , x_data_column in varchar2 default null
150 , x_name_number_column in varchar2 default null
151 , x_from_clause in varchar2 default null
152 , x_where_clause in varchar2 default null
153 , x_order_by_clause in varchar2 default null
154 , x_object_version_number in out nocopy number
155 , x_attribute1 in varchar2 default null
156 , x_attribute2 in varchar2 default null
157 , x_attribute3 in varchar2 default null
158 , x_attribute4 in varchar2 default null
159 , x_attribute5 in varchar2 default null
160 , x_attribute6 in varchar2 default null
161 , x_attribute7 in varchar2 default null
162 , x_attribute8 in varchar2 default null
163 , x_attribute9 in varchar2 default null
164 , x_attribute10 in varchar2 default null
165 , x_attribute11 in varchar2 default null
166 , x_attribute12 in varchar2 default null
167 , x_attribute13 in varchar2 default null
168 , x_attribute14 in varchar2 default null
169 , x_attribute15 in varchar2 default null
170 , x_attribute_category in varchar2 default null
171 , x_name in varchar2
172 , x_description in varchar2 )
173 IS
174 cursor c_rowid
175 is
176 select rowid
177 from csf_skill_types_b
178 where skill_type_id = x_skill_type_id;
179
180 -- cursor to check for duplicate skill type
181 cursor c_dup_task_type is
182 select 1
183 from csf_skill_types_tl a, csf_skill_types_b b
184 where a.skill_type_id = b.skill_type_id
185 and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
186 and language = userenv ('lang');
187
188 l_dummy_var number;
189
190
191 l_key_column varchar2(200);
192 l_data_column varchar2(2000);
193 l_name_number_column varchar2(200);
194 l_from_clause varchar2(2000);
195 l_where_clause varchar2(2000);
196 l_order_by_clause varchar2(200);
197
198 BEGIN
199 if x_skill_type_id is null
200 then
201 select csf_skill_types_b_s1.nextval
202 into x_skill_type_id
203 from dual;
204 else
205 -- Checks if record to be inserted already exists.
206 -- If it does, do nothing (RETURN), else, continue.
207 open c_rowid;
208 fetch c_rowid into x_rowid;
209 if c_rowid%found
210 then
211 close c_rowid;
212 return;
213 end if;
214 close c_rowid;
215 end if;
216
217 -- check for duplicate skill type
218 open c_dup_task_type;
219 fetch c_dup_task_type into l_dummy_var;
220
221 if l_dummy_var is not null then
222 close c_dup_task_type;
223 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_SKILLTYPE');
224 raise_application_error(-20110,fnd_message.get);
225 end if;
226
227 close c_dup_task_type;
228
229 if x_object_version_number is null
230 then
231 x_object_version_number := 1;
232 end if;
233
234 if x_key_column is null
235 then
236 l_key_column := 'skill_id';
237 else
238 l_key_column := x_key_column;
239 end if;
240
241 if x_data_column is null
242 then
243 l_data_column := 'skill_id id, description';
244 else
245 l_data_column := x_data_column;
246 end if;
247
248 if x_name_number_column is null
249 then
250 l_name_number_column := 'name';
251 else
252 l_name_number_column := x_name_number_column;
253 end if;
254
255 if x_from_clause is null
256 then
257 l_from_clause := 'csf_skills_vl';
258 else
259 l_from_clause := x_from_clause;
260 end if;
261
262 if x_where_clause is null
263 then
264 l_where_clause :=
265 'sysdate >= trunc(start_date_active) '||
266 'and (sysdate <= trunc(end_date_active)+1 or end_date_active is null) '||
267 'and skill_type_id = to_number('||to_char(x_skill_type_id)||')';
268 else
269 l_where_clause := x_where_clause;
270 end if;
271
272 if x_order_by_clause is null
273 then
274 l_order_by_clause := 'name';
275 else
276 l_order_by_clause := x_order_by_clause;
277 end if;
278
279 insert into csf_skill_types_b
280 ( skill_type_id
281 , rating_scale_id
282 , start_date_active
283 , end_date_active
284 , seeded_flag
285 , key_column
286 , data_column
287 , name_number_column
288 , from_clause
289 , where_clause
290 , order_by_clause
291 , object_version_number
292 , attribute1
293 , attribute2
294 , attribute3
295 , attribute4
296 , attribute5
297 , attribute6
298 , attribute7
299 , attribute8
300 , attribute9
301 , attribute10
302 , attribute11
303 , attribute12
304 , attribute13
305 , attribute14
306 , attribute15
307 , attribute_category
308 , creation_date
309 , created_by
310 , last_update_date
311 , last_updated_by
312 , last_update_login )
313 values
314 ( x_skill_type_id
315 , x_rating_scale_id
316 , x_start_date_active
317 , x_end_date_active
318 , nvl(x_seeded_flag, 'N')
319 , l_key_column
320 , l_data_column
321 , l_name_number_column
322 , l_from_clause
323 , l_where_clause
324 , l_order_by_clause
325 , x_object_version_number
326 , x_attribute1
327 , x_attribute2
328 , x_attribute3
329 , x_attribute4
330 , x_attribute5
331 , x_attribute6
332 , x_attribute7
333 , x_attribute8
334 , x_attribute9
335 , x_attribute10
336 , x_attribute11
337 , x_attribute12
338 , x_attribute13
339 , x_attribute14
340 , x_attribute15
341 , x_attribute_category
342 , sysdate
343 , fnd_global.user_id
344 , sysdate
345 , g_user_id
346 , g_login_id );
347
348 insert into csf_skill_types_tl
349 ( skill_type_id
350 , name
351 , description
352 , creation_date
353 , created_by
354 , last_update_date
355 , last_updated_by
356 , last_update_login
357 , language
358 , source_lang )
359 select x_skill_type_id
360 , x_name
361 , x_description
362 , sysdate
363 , g_user_id
364 , sysdate
365 , g_user_id
366 , g_login_id
367 , l.language_code
368 , userenv('LANG')
369 from fnd_languages l
370 where l.installed_flag in ('I', 'B')
371 and not exists
372 ( select null
373 from csf_skill_types_tl t
374 where t.skill_type_id = x_skill_type_id
375 and t.language = l.language_code );
376
377 open c_rowid;
378 fetch c_rowid into x_rowid;
379 if c_rowid%notfound
380 then
381 close c_rowid;
382 raise no_data_found;
383 end if;
384 close c_rowid;
385 END create_skill_type;
386
387 PROCEDURE lock_skill_type
388 ( x_skill_type_id in number
389 , x_object_version_number in number
390 , x_name in varchar2
391 , x_description in varchar2 )
392 IS
393 cursor c_ovn
394 is
395 select object_version_number
396 from csf_skill_types_b
397 where skill_type_id = x_skill_type_id
398 for update of skill_type_id nowait;
399
400 l_rec c_ovn%rowtype;
401
402 cursor c_tl
403 is
404 select name
405 , description
406 , decode(language, userenv('LANG'), 'Y', 'N') baselang
407 from csf_skill_types_tl
408 where skill_type_id = x_skill_type_id
409 and userenv('LANG') in (language, source_lang)
410 for update of skill_type_id nowait;
411
412 BEGIN
413 open c_ovn;
414 fetch c_ovn into l_rec;
415 if c_ovn%notfound
416 then
417 close c_ovn;
418 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
419 app_exception.raise_exception;
420 end if;
421 close c_ovn;
422
423 if l_rec.object_version_number = x_object_version_number
424 then
425 null;
426 else
427 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
428 app_exception.raise_exception;
429 end if;
430
431 for i in c_tl
432 loop
433 if i.baselang = 'Y'
434 then
435 if i.name = x_name
436 and ( i.description = x_description
437 or ( i.description is null and x_description is null ) )
438 then
439 null;
440 else
441 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442 app_exception.raise_exception;
443 end if;
444 end if;
445 end loop;
446 END lock_skill_type;
447
448 PROCEDURE update_skill_type
452 , x_start_date_active in date
449 ( x_skill_type_id in number
450 , x_object_version_number in out nocopy number
451 , x_rating_scale_id in number
453 , x_end_date_active in date
454 , x_seeded_flag in varchar2 default null
455 , x_attribute1 in varchar2 default null
456 , x_attribute2 in varchar2 default null
457 , x_attribute3 in varchar2 default null
458 , x_attribute4 in varchar2 default null
459 , x_attribute5 in varchar2 default null
460 , x_attribute6 in varchar2 default null
461 , x_attribute7 in varchar2 default null
462 , x_attribute8 in varchar2 default null
463 , x_attribute9 in varchar2 default null
464 , x_attribute10 in varchar2 default null
465 , x_attribute11 in varchar2 default null
466 , x_attribute12 in varchar2 default null
467 , x_attribute13 in varchar2 default null
468 , x_attribute14 in varchar2 default null
469 , x_attribute15 in varchar2 default null
470 , x_attribute_category in varchar2 default null
471 , x_name in varchar2
472 , x_description in varchar2 )
473 IS
474 -- cursor to check for duplicate skill type
475 cursor c_dup_task_type is
476 select 1
477 from csf_skill_types_tl a, csf_skill_types_b b
478 where a.skill_type_id = b.skill_type_id
479 and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
480 and language = userenv ('lang')
481 and a.skill_type_id <> x_skill_type_id;
482
483 l_dummy_var number;
484 l_ovn number;
485 BEGIN
486 -- check for duplicate skill type
487 open c_dup_task_type;
488 fetch c_dup_task_type into l_dummy_var;
489
490 if l_dummy_var is not null then
491 close c_dup_task_type;
492 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_SKILLTYPE');
493 raise_application_error(-20110, fnd_message.get);
494 end if;
495
496 close c_dup_task_type;
497
498 update csf_skill_types_b
499 set rating_scale_id = x_rating_scale_id
500 , start_date_active = x_start_date_active
501 , end_date_active = x_end_date_active
502 , seeded_flag = nvl(x_seeded_flag, 'N')
503 , object_version_number = object_version_number + 1
504 , attribute1 = x_attribute1
505 , attribute2 = x_attribute2
506 , attribute3 = x_attribute3
507 , attribute4 = x_attribute4
508 , attribute5 = x_attribute5
509 , attribute6 = x_attribute6
510 , attribute7 = x_attribute7
511 , attribute8 = x_attribute8
512 , attribute9 = x_attribute9
513 , attribute10 = x_attribute10
514 , attribute11 = x_attribute11
515 , attribute12 = x_attribute12
516 , attribute13 = x_attribute13
517 , attribute14 = x_attribute14
518 , attribute15 = x_attribute15
519 , attribute_category = x_attribute_category
520 , last_update_date = sysdate
521 , last_updated_by = g_user_id
522 , last_update_login = g_login_id
523 where skill_type_id = x_skill_type_id
524 returning object_version_number into l_ovn;
525
526 if sql%notfound
527 then
528 raise no_data_found;
529 end if;
530
531 update csf_skill_types_tl
532 set name = x_name
533 , description = x_description
534 , last_update_date = sysdate
535 , last_updated_by = g_user_id
536 , last_update_login = g_login_id
537 , source_lang = userenv('LANG')
538 where skill_type_id = x_skill_type_id
539 and userenv('LANG') in (language, source_lang);
540
541 if sql%notfound
542 then
543 raise no_data_found;
544 end if;
545 x_object_version_number := l_ovn;
546 END update_skill_type;
547
548 PROCEDURE delete_skill_type ( x_skill_type_id in number )
549 IS
550 BEGIN
551 delete from csf_skill_types_tl
552 where skill_type_id = x_skill_type_id;
553
554 if sql%notfound
555 then
556 raise no_data_found;
557 end if;
558
559 delete from csf_skill_types_b
560 where skill_type_id = x_skill_type_id;
561
562 if sql%notfound then
563 raise no_data_found;
564 end if;
565 END delete_skill_type;
566
567 PROCEDURE add_skill_type_language
568 IS
569 BEGIN
570 delete from csf_skill_types_tl t
571 where not exists
572 ( select null
573 from csf_skill_types_b b
574 where b.skill_type_id = t.skill_type_id );
575
576 update csf_skill_types_tl t
577 set ( name, description ) = ( select b.name
578 , b.description
579 from csf_skill_types_tl b
580 where b.skill_type_id = t.skill_type_id
581 and b.language = t.source_lang )
585 from csf_skill_types_tl subb
582 where ( t.skill_type_id, t.language ) in
583 ( select subt.skill_type_id
584 , subt.language
586 , csf_skill_types_tl subt
587 where subb.skill_type_id = subt.skill_type_id
588 and subb.language = subt.source_lang
589 and ( subb.name <> subt.name
590 or subb.description <> subt.description
591 or (subb.description is null and subt.description is not null)
592 or (subb.description is not null and subt.description is null)));
593
594 insert into csf_skill_types_tl
595 ( skill_type_id
596 , name
597 , description
598 , created_by
599 , creation_date
600 , last_updated_by
601 , last_update_date
602 , last_update_login
603 , language
604 , source_lang )
605 select b.skill_type_id
606 , b.name
607 , b.description
608 , b.created_by
609 , b.creation_date
610 , b.last_updated_by
611 , b.last_update_date
612 , b.last_update_login
613 , l.language_code
614 , b.source_lang
615 from csf_skill_types_tl b
616 , fnd_languages l
617 where l.installed_flag in ('I', 'B')
618 and b.language = userenv('LANG')
619 and not exists
620 ( select null
621 from csf_skill_types_tl t
622 where t.skill_type_id = b.skill_type_id
623 and t.language = l.language_code );
624 END add_skill_type_language;
625
626 PROCEDURE create_skill
627 ( x_rowid in out nocopy varchar2
628 , x_skill_id in out nocopy number
629 , x_skill_type_id in number
630 , x_skill_alias in varchar2
631 , x_start_date_active in date
632 , x_end_date_active in date
633 , x_seeded_flag in varchar2
634 , x_object_version_number in out nocopy number
635 , x_attribute1 in varchar2 default null
636 , x_attribute2 in varchar2 default null
637 , x_attribute3 in varchar2 default null
638 , x_attribute4 in varchar2 default null
639 , x_attribute5 in varchar2 default null
640 , x_attribute6 in varchar2 default null
641 , x_attribute7 in varchar2 default null
642 , x_attribute8 in varchar2 default null
643 , x_attribute9 in varchar2 default null
644 , x_attribute10 in varchar2 default null
645 , x_attribute11 in varchar2 default null
646 , x_attribute12 in varchar2 default null
647 , x_attribute13 in varchar2 default null
648 , x_attribute14 in varchar2 default null
649 , x_attribute15 in varchar2 default null
650 , x_attribute_category in varchar2 default null
651 , x_name in varchar2
652 , x_description in varchar2 )
653 IS
654 cursor c_rowid
655 is
656 select rowid
657 from csf_skills_b
658 where skill_id = x_skill_id;
659
660 -- cursor to check for duplicate skills
661 cursor c_dup_skill_type is
662 select 1
663 from csf_skills_b a, csf_skills_tl b
664 where a.skill_id = b.skill_id
665 and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
666 and skill_type_id = x_skill_type_id
667 and language = userenv ('lang');
668
669 l_dummy_var number;
670 BEGIN
671 if x_skill_id is null
672 then
673 select csf_skills_b_s1.nextval
674 into x_skill_id
675 from dual;
676 else
677 -- Checks if record to be inserted already exists.
678 -- If it does, do nothing (RETURN), else, continue.
679 open c_rowid;
680 fetch c_rowid into x_rowid;
681 if c_rowid%found
682 then
683 close c_rowid;
684 return;
685 end if;
686 close c_rowid;
687 end if;
688
689 -- check for duplicate skills
690 open c_dup_skill_type;
691 fetch c_dup_skill_type into l_dummy_var;
692
693 if l_dummy_var is not null then
694 close c_dup_skill_type;
695 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_SKILL');
696 raise_application_error(-20110, fnd_message.get);
697 end if;
698
699 close c_dup_skill_type;
700
701 if x_object_version_number is null
702 then
703 x_object_version_number := 1;
704 end if;
705
706 insert into csf_skills_b
707 ( skill_id
708 , skill_type_id
709 , skill_alias
710 , start_date_active
711 , end_date_active
712 , seeded_flag
713 , object_version_number
714 , attribute1
715 , attribute2
716 , attribute3
717 , attribute4
718 , attribute5
719 , attribute6
720 , attribute7
721 , attribute8
722 , attribute9
723 , attribute10
724 , attribute11
725 , attribute12
726 , attribute13
727 , attribute14
728 , attribute15
729 , attribute_category
730 , creation_date
731 , created_by
735 values
732 , last_update_date
733 , last_updated_by
734 , last_update_login )
736 ( x_skill_id
737 , x_skill_type_id
738 , x_skill_alias
739 , x_start_date_active
740 , x_end_date_active
741 , nvl(x_seeded_flag, 'N')
742 , x_object_version_number
743 , x_attribute1
744 , x_attribute2
745 , x_attribute3
746 , x_attribute4
747 , x_attribute5
748 , x_attribute6
749 , x_attribute7
750 , x_attribute8
751 , x_attribute9
752 , x_attribute10
753 , x_attribute11
754 , x_attribute12
755 , x_attribute13
756 , x_attribute14
757 , x_attribute15
758 , x_attribute_category
759 , sysdate
760 , fnd_global.user_id
761 , sysdate
762 , g_user_id
763 , g_login_id );
764
765 insert into csf_skills_tl
766 ( skill_id
767 , name
768 , description
769 , creation_date
770 , created_by
771 , last_update_date
772 , last_updated_by
773 , last_update_login
774 , language
775 , source_lang )
776 select x_skill_id
777 , x_name
778 , x_description
779 , sysdate
780 , g_user_id
781 , sysdate
782 , g_user_id
783 , g_login_id
784 , l.language_code
785 , userenv('LANG')
786 from fnd_languages l
787 where l.installed_flag in ('I', 'B')
788 and not exists
789 ( select null
790 from csf_skills_tl t
791 where t.skill_id = x_skill_id
792 and t.language = l.language_code );
793
794 open c_rowid;
795 fetch c_rowid into x_rowid;
796 if c_rowid%notfound
797 then
798 close c_rowid;
799 raise no_data_found;
800 end if;
801 close c_rowid;
802 END create_skill;
803
804 PROCEDURE lock_skill
805 ( x_skill_id in number
806 , x_object_version_number in number
807 , x_name in varchar2
808 , x_description in varchar2 )
809 IS
810 cursor c_ovn
811 is
812 select object_version_number
813 from csf_skills_b
814 where skill_id = x_skill_id
815 for update of skill_id nowait;
816
817 l_rec c_ovn%rowtype;
818
819 cursor c_tl
820 is
821 select name
822 , description
823 , decode(language, userenv('LANG'), 'Y', 'N') baselang
824 from csf_skills_tl
825 where skill_id = x_skill_id
826 and userenv('LANG') in (language, source_lang)
827 for update of skill_id nowait;
828
829 BEGIN
830 open c_ovn;
831 fetch c_ovn into l_rec;
832 if c_ovn%notfound
833 then
834 close c_ovn;
835 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
836 app_exception.raise_exception;
837 end if;
838 close c_ovn;
839
840 if l_rec.object_version_number = x_object_version_number
841 then
842 null;
843 else
844 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
845 app_exception.raise_exception;
846 end if;
847
848 for i in c_tl
849 loop
850 if i.baselang = 'Y'
851 then
852 if i.name = x_name
853 and ( i.description = x_description
854 or ( i.description is null and x_description is null ) )
855 then
856 null;
857 else
858 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
859 app_exception.raise_exception;
860 end if;
861 end if;
862 end loop;
863 END lock_skill;
864
865 PROCEDURE update_skill
866 ( x_skill_id in number
867 , x_object_version_number in out nocopy number
868 , x_skill_type_id in number
869 , x_skill_alias in varchar2
870 , x_start_date_active in date
871 , x_end_date_active in date
872 , x_attribute1 in varchar2 default null
873 , x_attribute2 in varchar2 default null
874 , x_attribute3 in varchar2 default null
875 , x_attribute4 in varchar2 default null
876 , x_attribute5 in varchar2 default null
877 , x_attribute6 in varchar2 default null
878 , x_attribute7 in varchar2 default null
879 , x_attribute8 in varchar2 default null
880 , x_attribute9 in varchar2 default null
881 , x_attribute10 in varchar2 default null
882 , x_attribute11 in varchar2 default null
883 , x_attribute12 in varchar2 default null
884 , x_attribute13 in varchar2 default null
885 , x_attribute14 in varchar2 default null
886 , x_attribute15 in varchar2 default null
887 , x_attribute_category in varchar2 default null
888 , x_name in varchar2
889 , x_description in varchar2 )
890 IS
891 -- cursor to check for duplicate skills
892 cursor c_dup_skill_type is
893 select 1
897 and skill_type_id = x_skill_type_id
894 from csf_skills_b a, csf_skills_tl b
895 where a.skill_id = b.skill_id
896 and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
898 and a.skill_id <> x_skill_id
899 and language = userenv ('lang');
900
901 l_dummy_var number;
902 l_ovn number;
903 BEGIN
904 -- check for duplicate skills
905 open c_dup_skill_type;
906 fetch c_dup_skill_type into l_dummy_var;
907
908 if l_dummy_var is not null then
909 close c_dup_skill_type;
910 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_SKILL');
911 raise_application_error(-20110, fnd_message.get);
912 end if;
913
914 close c_dup_skill_type;
915
916 update csf_skills_b
917 set skill_type_id = x_skill_type_id
918 , skill_alias = x_skill_alias
919 , start_date_active = x_start_date_active
920 , end_date_active = x_end_date_active
921 , object_version_number = object_version_number + 1
922 , attribute1 = x_attribute1
923 , attribute2 = x_attribute2
924 , attribute3 = x_attribute3
925 , attribute4 = x_attribute4
926 , attribute5 = x_attribute5
927 , attribute6 = x_attribute6
928 , attribute7 = x_attribute7
929 , attribute8 = x_attribute8
930 , attribute9 = x_attribute9
931 , attribute10 = x_attribute10
932 , attribute11 = x_attribute11
933 , attribute12 = x_attribute12
934 , attribute13 = x_attribute13
935 , attribute14 = x_attribute14
936 , attribute15 = x_attribute15
937 , attribute_category = x_attribute_category
938 , last_update_date = sysdate
939 , last_updated_by = g_user_id
940 , last_update_login = g_login_id
941 where skill_id = x_skill_id
942 returning object_version_number into l_ovn;
943
944 if sql%notfound
945 then
946 raise no_data_found;
947 end if;
948
949 update csf_skills_tl
950 set name = x_name
951 , description = x_description
952 , last_update_date = sysdate
953 , last_updated_by = g_user_id
954 , last_update_login = g_login_id
955 , source_lang = userenv('LANG')
956 where skill_id = x_skill_id
957 and userenv('LANG') in (language, source_lang);
958
959 if sql%notfound
960 then
961 raise no_data_found;
962 end if;
963 x_object_version_number := l_ovn;
964 END update_skill;
965
966 PROCEDURE delete_skill ( x_skill_id in number )
967 IS
968 BEGIN
969 delete from csf_skills_tl
970 where skill_id = x_skill_id;
971
972 if sql%notfound
973 then
974 raise no_data_found;
975 end if;
976
977 delete from csf_skills_b
978 where skill_id = x_skill_id;
979
980 if sql%notfound then
981 raise no_data_found;
982 end if;
983 END delete_skill;
984
985 PROCEDURE add_skill_language
986 IS
987 BEGIN
988 delete from csf_skills_tl t
989 where not exists
990 ( select null
991 from csf_skills_b b
992 where b.skill_id = t.skill_id );
993
994 update csf_skills_tl t
995 set ( name
996 , description ) = ( select b.name
997 , b.description
998 from csf_skills_tl b
999 where b.skill_id = t.skill_id
1000 and b.language = t.source_lang )
1001 where ( t.skill_id, t.language ) in
1002 ( select subt.skill_id
1003 , subt.language
1004 from csf_skills_tl subb
1005 , csf_skills_tl subt
1006 where subb.skill_id = subt.skill_id
1007 and subb.language = subt.source_lang
1008 and ( subb.name <> subt.name
1009 or subb.description <> subt.description
1010 or (subb.description is null and subt.description is not null)
1011 or (subb.description is not null and subt.description is null)));
1012
1013 insert into csf_skills_tl
1014 ( skill_id
1015 , name
1016 , description
1017 , created_by
1018 , creation_date
1019 , last_updated_by
1020 , last_update_date
1021 , last_update_login
1022 , language
1023 , source_lang )
1024 select b.skill_id
1025 , b.name
1026 , b.description
1027 , b.created_by
1028 , b.creation_date
1029 , b.last_updated_by
1030 , b.last_update_date
1031 , b.last_update_login
1032 , l.language_code
1033 , b.source_lang
1034 from csf_skills_tl b
1035 , fnd_languages l
1036 where l.installed_flag in ('I', 'B')
1037 and b.language = userenv('LANG')
1038 and not exists
1039 ( select null
1040 from csf_skills_tl t
1044
1041 where t.skill_id = b.skill_id
1042 and t.language = l.language_code );
1043 END add_skill_language;
1045 PROCEDURE create_rating_scale
1046 ( x_rowid in out nocopy varchar2
1047 , x_rating_scale_id in out nocopy number
1048 , x_start_date_active in date
1049 , x_end_date_active in date
1050 , x_seeded_flag in varchar2 default null
1051 , x_object_version_number in out nocopy number
1052 , x_attribute1 in varchar2 default null
1053 , x_attribute2 in varchar2 default null
1054 , x_attribute3 in varchar2 default null
1055 , x_attribute4 in varchar2 default null
1056 , x_attribute5 in varchar2 default null
1057 , x_attribute6 in varchar2 default null
1058 , x_attribute7 in varchar2 default null
1059 , x_attribute8 in varchar2 default null
1060 , x_attribute9 in varchar2 default null
1061 , x_attribute10 in varchar2 default null
1062 , x_attribute11 in varchar2 default null
1063 , x_attribute12 in varchar2 default null
1064 , x_attribute13 in varchar2 default null
1065 , x_attribute14 in varchar2 default null
1066 , x_attribute15 in varchar2 default null
1067 , x_attribute_category in varchar2 default null
1068 , x_name in varchar2
1069 , x_description in varchar2 )
1070 IS
1071 cursor c_rowid
1072 is
1073 select rowid
1074 from csf_rating_scales_b
1075 where rating_scale_id = x_rating_scale_id;
1076
1077 -- cursor to check for duplicate scale names
1078 cursor c_dup_scale_type is
1079 select 1
1080 from csf_rating_scales_tl a, csf_rating_scales_b b
1081 where a.rating_scale_id = b.rating_scale_id
1082 and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
1083 and language = userenv ('lang');
1084
1085 l_dummy_var number;
1086 BEGIN
1087 if x_rating_scale_id is null
1088 then
1089 select csf_rating_scales_b_s1.nextval
1090 into x_rating_scale_id
1091 from dual;
1092 else
1093 -- Checks if record to be inserted already exists.
1094 -- If it does, do nothing (RETURN), else, continue.
1095 open c_rowid;
1096 fetch c_rowid into x_rowid;
1097 if c_rowid%found
1098 then
1099 close c_rowid;
1100 return;
1101 end if;
1102 close c_rowid;
1103 end if;
1104
1105 -- check whether Scale name entered already exists
1106 open c_dup_scale_type;
1107 fetch c_dup_scale_type into l_dummy_var;
1108
1109 if l_dummy_var is not null then
1110 close c_dup_scale_type;
1111 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_SCALE');
1112 raise_application_error(-20110,fnd_message.get);
1113 end if;
1114
1115 close c_dup_scale_type;
1116
1117 if x_object_version_number is null
1118 then
1119 x_object_version_number := 1;
1120 end if;
1121
1122 insert into csf_rating_scales_b
1123 ( rating_scale_id
1124 , start_date_active
1125 , end_date_active
1126 , seeded_flag
1127 , object_version_number
1128 , attribute1
1129 , attribute2
1130 , attribute3
1131 , attribute4
1132 , attribute5
1133 , attribute6
1134 , attribute7
1135 , attribute8
1136 , attribute9
1137 , attribute10
1138 , attribute11
1139 , attribute12
1140 , attribute13
1141 , attribute14
1142 , attribute15
1143 , attribute_category
1144 , creation_date
1145 , created_by
1146 , last_update_date
1147 , last_updated_by
1148 , last_update_login )
1149 values
1150 ( x_rating_scale_id
1151 , x_start_date_active
1152 , x_end_date_active
1153 , nvl(x_seeded_flag, 'N')
1154 , x_object_version_number
1155 , x_attribute1
1156 , x_attribute2
1157 , x_attribute3
1158 , x_attribute4
1159 , x_attribute5
1160 , x_attribute6
1161 , x_attribute7
1162 , x_attribute8
1163 , x_attribute9
1164 , x_attribute10
1165 , x_attribute11
1166 , x_attribute12
1167 , x_attribute13
1168 , x_attribute14
1169 , x_attribute15
1170 , x_attribute_category
1171 , sysdate
1172 , fnd_global.user_id
1173 , sysdate
1174 , g_user_id
1175 , g_login_id );
1176
1177 insert into csf_rating_scales_tl
1178 ( rating_scale_id
1179 , name
1180 , description
1181 , creation_date
1182 , created_by
1183 , last_update_date
1184 , last_updated_by
1185 , last_update_login
1186 , language
1187 , source_lang )
1188 select x_rating_scale_id
1189 , x_name
1190 , x_description
1191 , sysdate
1192 , g_user_id
1193 , sysdate
1194 , g_user_id
1195 , g_login_id
1196 , l.language_code
1197 , userenv('LANG')
1198 from fnd_languages l
1199 where l.installed_flag in ('I', 'B')
1200 and not exists
1204 and t.language = l.language_code );
1201 ( select null
1202 from csf_rating_scales_tl t
1203 where t.rating_scale_id = x_rating_scale_id
1205
1206 open c_rowid;
1207 fetch c_rowid into x_rowid;
1208 if c_rowid%notfound
1209 then
1210 close c_rowid;
1211 raise no_data_found;
1212 end if;
1213 close c_rowid;
1214 END create_rating_scale;
1215
1216 PROCEDURE lock_rating_scale
1217 ( x_rating_scale_id in number
1218 , x_object_version_number in number
1219 , x_name in varchar2
1220 , x_description in varchar2 )
1221 IS
1222 cursor c_ovn
1223 is
1224 select object_version_number
1225 from csf_rating_scales_b
1226 where rating_scale_id = x_rating_scale_id
1227 for update of rating_scale_id nowait;
1228
1229 l_rec c_ovn%rowtype;
1230
1231 cursor c_tl
1232 is
1233 select name
1234 , description
1235 , decode(language, userenv('LANG'), 'Y', 'N') baselang
1236 from csf_rating_scales_tl
1237 where rating_scale_id = x_rating_scale_id
1238 and userenv('LANG') in (language, source_lang)
1239 for update of rating_scale_id nowait;
1240
1241 BEGIN
1242 open c_ovn;
1243 fetch c_ovn into l_rec;
1244 if c_ovn%notfound
1245 then
1246 close c_ovn;
1247 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1248 app_exception.raise_exception;
1249 end if;
1250 close c_ovn;
1251
1252 if l_rec.object_version_number = x_object_version_number
1253 then
1254 null;
1255 else
1256 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1257 app_exception.raise_exception;
1258 end if;
1259
1260 for i in c_tl
1261 loop
1262 if i.baselang = 'Y'
1263 then
1264 if i.name = x_name
1265 and ( i.description = x_description
1266 or ( i.description is null and x_description is null ) )
1267 then
1268 null;
1269 else
1270 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1271 app_exception.raise_exception;
1272 end if;
1273 end if;
1274 end loop;
1275 END lock_rating_scale;
1276
1277 PROCEDURE update_rating_scale
1278 ( x_rating_scale_id in number
1279 , x_object_version_number in out nocopy number
1280 , x_start_date_active in date
1281 , x_end_date_active in date
1282 , x_seeded_flag in varchar2 default null
1283 , x_attribute1 in varchar2 default null
1284 , x_attribute2 in varchar2 default null
1285 , x_attribute3 in varchar2 default null
1286 , x_attribute4 in varchar2 default null
1287 , x_attribute5 in varchar2 default null
1288 , x_attribute6 in varchar2 default null
1289 , x_attribute7 in varchar2 default null
1290 , x_attribute8 in varchar2 default null
1291 , x_attribute9 in varchar2 default null
1292 , x_attribute10 in varchar2 default null
1293 , x_attribute11 in varchar2 default null
1294 , x_attribute12 in varchar2 default null
1295 , x_attribute13 in varchar2 default null
1296 , x_attribute14 in varchar2 default null
1297 , x_attribute15 in varchar2 default null
1298 , x_attribute_category in varchar2 default null
1299 , x_name in varchar2
1300 , x_description in varchar2 )
1301 IS
1302 -- cursor to check for duplicate scale names
1303 cursor c_dup_scale_type is
1304 select 1
1305 from csf_rating_scales_tl a, csf_rating_scales_b b
1306 where a.rating_scale_id = b.rating_scale_id
1307 and upper (rtrim (ltrim (name))) = upper (rtrim (ltrim (x_name)))
1308 and language = userenv ('lang')
1309 and b.rating_scale_id <> x_rating_scale_id;
1310
1311 l_dummy_var number;
1312 l_ovn number;
1313 BEGIN
1314 -- check whether Scale name entered already exists
1315 open c_dup_scale_type;
1316 fetch c_dup_scale_type into l_dummy_var;
1317
1318 if l_dummy_var is not null then
1319 close c_dup_scale_type;
1320 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_SCALE');
1321 raise_application_error(-20110,fnd_message.get);
1322 end if;
1323
1324 close c_dup_scale_type;
1325
1326 update csf_rating_scales_b
1327 set start_date_active = x_start_date_active
1328 , end_date_active = x_end_date_active
1329 , seeded_flag = nvl(x_seeded_flag, 'N')
1330 , object_version_number = object_version_number + 1
1331 , attribute1 = x_attribute1
1332 , attribute2 = x_attribute2
1333 , attribute3 = x_attribute3
1334 , attribute4 = x_attribute4
1335 , attribute5 = x_attribute5
1336 , attribute6 = x_attribute6
1337 , attribute7 = x_attribute7
1338 , attribute8 = x_attribute8
1339 , attribute9 = x_attribute9
1340 , attribute10 = x_attribute10
1344 , attribute14 = x_attribute14
1341 , attribute11 = x_attribute11
1342 , attribute12 = x_attribute12
1343 , attribute13 = x_attribute13
1345 , attribute15 = x_attribute15
1346 , attribute_category = x_attribute_category
1347 , last_update_date = sysdate
1348 , last_updated_by = g_user_id
1349 , last_update_login = g_login_id
1350 where rating_scale_id = x_rating_scale_id
1351 returning object_version_number into l_ovn;
1352
1353 if sql%notfound
1354 then
1355 raise no_data_found;
1356 end if;
1357
1358 update csf_rating_scales_tl
1359 set name = x_name
1360 , description = x_description
1361 , last_update_date = sysdate
1362 , last_updated_by = g_user_id
1363 , last_update_login = g_login_id
1364 , source_lang = userenv('LANG')
1365 where rating_scale_id = x_rating_scale_id
1366 and userenv('LANG') in (language, source_lang);
1367
1368 if sql%notfound
1369 then
1370 raise no_data_found;
1371 end if;
1372 x_object_version_number := l_ovn;
1373 END update_rating_scale;
1374
1375 PROCEDURE delete_rating_scale ( x_rating_scale_id in number )
1376 IS
1377 BEGIN
1378 delete from csf_rating_scales_tl
1379 where rating_scale_id = x_rating_scale_id;
1380
1381 if sql%notfound
1382 then
1383 raise no_data_found;
1384 end if;
1385
1386 delete from csf_rating_scales_b
1387 where rating_scale_id = x_rating_scale_id;
1388
1389 if sql%notfound then
1390 raise no_data_found;
1391 end if;
1392 END delete_rating_scale;
1393
1394 PROCEDURE add_rating_scale_language
1395 IS
1396 BEGIN
1397 delete from csf_rating_scales_tl t
1398 where not exists
1399 ( select null
1400 from csf_rating_scales_b b
1401 where b.rating_scale_id = t.rating_scale_id );
1402
1403 update csf_rating_scales_tl t
1404 set ( name
1405 , description ) = ( select b.name
1406 , b.description
1407 from csf_rating_scales_tl b
1408 where b.rating_scale_id = t.rating_scale_id
1409 and b.language = t.source_lang )
1410 where ( t.rating_scale_id, t.language ) in
1411 ( select subt.rating_scale_id
1412 , subt.language
1413 from csf_rating_scales_tl subb
1414 , csf_rating_scales_tl subt
1415 where subb.rating_scale_id = subt.rating_scale_id
1416 and subb.language = subt.source_lang
1417 and ( subb.name <> subt.name
1418 or subb.description <> subt.description
1419 or (subb.description is null and subt.description is not null)
1420 or (subb.description is not null and subt.description is null)));
1421
1422 insert into csf_rating_scales_tl
1423 ( rating_scale_id
1424 , name
1425 , description
1426 , created_by
1427 , creation_date
1428 , last_updated_by
1429 , last_update_date
1430 , last_update_login
1431 , language
1432 , source_lang )
1433 select b.rating_scale_id
1434 , b.name
1435 , b.description
1436 , b.created_by
1437 , b.creation_date
1438 , b.last_updated_by
1439 , b.last_update_date
1440 , b.last_update_login
1441 , l.language_code
1442 , b.source_lang
1443 from csf_rating_scales_tl b
1444 , fnd_languages l
1445 where l.installed_flag in ('I', 'B')
1446 and b.language = userenv('LANG')
1447 and not exists
1448 ( select null
1449 from csf_rating_scales_tl t
1450 where t.rating_scale_id = b.rating_scale_id
1451 and t.language = l.language_code );
1452 END add_rating_scale_language;
1453
1454 PROCEDURE create_skill_level
1455 ( x_rowid in out nocopy varchar2
1456 , x_skill_level_id in out nocopy number
1457 , x_rating_scale_id in number
1458 , x_step_value in number
1459 , x_correction_factor in number
1460 , x_start_date_active in date
1461 , x_end_date_active in date
1462 , x_seeded_flag in varchar2 default null
1463 , x_object_version_number in out nocopy number
1464 , x_attribute1 in varchar2 default null
1465 , x_attribute2 in varchar2 default null
1466 , x_attribute3 in varchar2 default null
1467 , x_attribute4 in varchar2 default null
1468 , x_attribute5 in varchar2 default null
1469 , x_attribute6 in varchar2 default null
1470 , x_attribute7 in varchar2 default null
1471 , x_attribute8 in varchar2 default null
1472 , x_attribute9 in varchar2 default null
1473 , x_attribute10 in varchar2 default null
1474 , x_attribute11 in varchar2 default null
1475 , x_attribute12 in varchar2 default null
1476 , x_attribute13 in varchar2 default null
1480 , x_name in varchar2
1477 , x_attribute14 in varchar2 default null
1478 , x_attribute15 in varchar2 default null
1479 , x_attribute_category in varchar2 default null
1481 , x_description in varchar2 )
1482 IS
1483 cursor c_rowid
1484 is
1485 select rowid
1486 from csf_skill_levels_b
1487 where skill_level_id = x_skill_level_id;
1488
1489 -- cursor to check for duplicate level name
1490 cursor c_dup_name is
1491 select 1
1492 from csf_skill_levels_b a, csf_skill_levels_tl b
1493 where a.skill_level_id = b.skill_level_id
1494 and (upper (rtrim (ltrim (b.name))) = upper (rtrim (ltrim (x_name)))
1495 )
1496 and a.rating_scale_id = x_rating_scale_id
1497 and b.language = userenv ('LANG');
1498
1499 -- cursor to check for duplicate level order
1500 cursor c_dup_order is
1501 select 1
1502 from csf_skill_levels_b a, csf_skill_levels_tl b
1503 where a.skill_level_id = b.skill_level_id
1504 and a.step_value in (
1505 select c.step_value
1506 from csf_skill_levels_b c
1507 where c.skill_level_id = a.skill_level_id
1508 and rating_scale_id = x_rating_scale_id
1509 and c.step_value = x_step_value)
1510 and a.rating_scale_id = x_rating_scale_id
1511 and b.language = userenv ('LANG');
1512
1513 l_dummy_var number;
1514 BEGIN
1515 if x_skill_level_id is null
1516 then
1517 select csf_skill_levels_b_s1.nextval
1518 into x_skill_level_id
1519 from dual;
1520 else
1521 -- Checks if record to be inserted already exists.
1522 -- If it does, do nothing (RETURN), else, continue.
1523 open c_rowid;
1524 fetch c_rowid into x_rowid;
1525 if c_rowid%found
1526 then
1527 close c_rowid;
1528 return;
1529 end if;
1530 close c_rowid;
1531 end if;
1532
1533 -- for bug 3799295
1534 -- check for duplicate level order
1535 open c_dup_order;
1536 fetch c_dup_order into l_dummy_var;
1537
1538 if l_dummy_var is not null then
1539 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_ORDER');
1540 raise_application_error(-20120,fnd_message.get);
1541 end if;
1542
1543 close c_dup_order;
1544
1545 -- check for duplicate level name
1546 open c_dup_name;
1547 fetch c_dup_name into l_dummy_var;
1548
1549 if l_dummy_var is not null then
1550 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_LEVEL');
1551 raise_application_error(-20110,fnd_message.get);
1552 end if;
1553
1554 close c_dup_name;
1555
1556 if x_object_version_number is null
1557 then
1558 x_object_version_number := 1;
1559 end if;
1560
1561 insert into csf_skill_levels_b
1562 ( skill_level_id
1563 , rating_scale_id
1564 , step_value
1565 , correction_factor
1566 , start_date_active
1567 , end_date_active
1568 , seeded_flag
1569 , object_version_number
1570 , attribute1
1571 , attribute2
1572 , attribute3
1573 , attribute4
1574 , attribute5
1575 , attribute6
1576 , attribute7
1577 , attribute8
1578 , attribute9
1579 , attribute10
1580 , attribute11
1581 , attribute12
1582 , attribute13
1583 , attribute14
1584 , attribute15
1585 , attribute_category
1586 , creation_date
1587 , created_by
1588 , last_update_date
1589 , last_updated_by
1590 , last_update_login )
1591 values
1592 ( x_skill_level_id
1593 , x_rating_scale_id
1594 , x_step_value
1595 , x_correction_factor
1596 , x_start_date_active
1597 , x_end_date_active
1598 , nvl(x_seeded_flag, 'N')
1599 , x_object_version_number
1600 , x_attribute1
1601 , x_attribute2
1602 , x_attribute3
1603 , x_attribute4
1604 , x_attribute5
1605 , x_attribute6
1606 , x_attribute7
1607 , x_attribute8
1608 , x_attribute9
1609 , x_attribute10
1610 , x_attribute11
1611 , x_attribute12
1612 , x_attribute13
1613 , x_attribute14
1614 , x_attribute15
1615 , x_attribute_category
1616 , sysdate
1617 , fnd_global.user_id
1618 , sysdate
1619 , g_user_id
1620 , g_login_id );
1621
1622 insert into csf_skill_levels_tl
1623 ( skill_level_id
1624 , name
1625 , description
1626 , creation_date
1627 , created_by
1628 , last_update_date
1629 , last_updated_by
1630 , last_update_login
1631 , language
1632 , source_lang )
1633 select x_skill_level_id
1634 , x_name
1635 , x_description
1636 , sysdate
1637 , g_user_id
1638 , sysdate
1639 , g_user_id
1640 , g_login_id
1641 , l.language_code
1645 and not exists
1642 , userenv('LANG')
1643 from fnd_languages l
1644 where l.installed_flag in ('I', 'B')
1646 ( select null
1647 from csf_skill_levels_tl t
1648 where t.skill_level_id = x_skill_level_id
1649 and t.language = l.language_code );
1650
1651 open c_rowid;
1652 fetch c_rowid into x_rowid;
1653 if c_rowid%notfound
1654 then
1655 close c_rowid;
1656 raise no_data_found;
1657 end if;
1658 close c_rowid;
1659 END create_skill_level;
1660
1661 PROCEDURE lock_skill_level
1662 ( x_skill_level_id in number
1663 , x_object_version_number in number
1664 , x_name in varchar2
1665 , x_description in varchar2 )
1666 IS
1667 cursor c_ovn
1668 is
1669 select object_version_number
1670 from csf_skill_levels_b
1671 where skill_level_id = x_skill_level_id
1672 for update of skill_level_id nowait;
1673
1674 l_rec c_ovn%rowtype;
1675
1676 cursor c_tl
1677 is
1678 select name
1679 , description
1680 , decode(language, userenv('LANG'), 'Y', 'N') baselang
1681 from csf_skill_levels_tl
1682 where skill_level_id = x_skill_level_id
1683 and userenv('LANG') in (language, source_lang)
1684 for update of skill_level_id nowait;
1685
1686 BEGIN
1687 open c_ovn;
1688 fetch c_ovn into l_rec;
1689 if c_ovn%notfound
1690 then
1691 close c_ovn;
1692 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1693 app_exception.raise_exception;
1694 end if;
1695 close c_ovn;
1696
1697 if l_rec.object_version_number = x_object_version_number
1698 then
1699 null;
1700 else
1701 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1702 app_exception.raise_exception;
1703 end if;
1704
1705 for i in c_tl
1706 loop
1707 if i.baselang = 'Y'
1708 then
1709 if i.name = x_name
1710 and ( i.description = x_description
1711 or ( i.description is null and x_description is null ) )
1712 then
1713 null;
1714 else
1715 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1716 app_exception.raise_exception;
1717 end if;
1718 end if;
1719 end loop;
1720 END lock_skill_level;
1721
1722 PROCEDURE update_skill_level
1723 ( x_skill_level_id in number
1724 , x_object_version_number in out nocopy number
1725 , x_rating_scale_id in number
1726 , x_step_value in number
1727 , x_correction_factor in number
1728 , x_start_date_active in date
1729 , x_end_date_active in date
1730 , x_seeded_flag in varchar2 default null
1731 , x_attribute1 in varchar2 default null
1732 , x_attribute2 in varchar2 default null
1733 , x_attribute3 in varchar2 default null
1734 , x_attribute4 in varchar2 default null
1735 , x_attribute5 in varchar2 default null
1736 , x_attribute6 in varchar2 default null
1737 , x_attribute7 in varchar2 default null
1738 , x_attribute8 in varchar2 default null
1739 , x_attribute9 in varchar2 default null
1740 , x_attribute10 in varchar2 default null
1741 , x_attribute11 in varchar2 default null
1742 , x_attribute12 in varchar2 default null
1743 , x_attribute13 in varchar2 default null
1744 , x_attribute14 in varchar2 default null
1745 , x_attribute15 in varchar2 default null
1746 , x_attribute_category in varchar2 default null
1747 , x_name in varchar2
1748 , x_description in varchar2 )
1749 IS
1750 -- cursor to check for duplicate level name
1751 cursor c_dup_name is
1752 select 1
1753 from csf_skill_levels_b a, csf_skill_levels_tl b
1754 where a.skill_level_id = b.skill_level_id
1755 and (upper (rtrim (ltrim (b.name))) = upper (rtrim (ltrim (x_name)))
1756 )
1757 and a.rating_scale_id = x_rating_scale_id
1758 and b.language = userenv ('LANG')
1759 and a.skill_level_id <> x_skill_level_id;
1760
1761 -- cursor to check for duplicate level order
1762 cursor c_dup_order is
1763 select 1
1764 from csf_skill_levels_b a, csf_skill_levels_tl b
1765 where a.skill_level_id = b.skill_level_id
1766 and a.step_value in (
1767 select c.step_value
1768 from csf_skill_levels_b c
1769 where c.skill_level_id = a.skill_level_id
1770 and rating_scale_id = x_rating_scale_id
1771 and c.step_value = x_step_value)
1772 and a.rating_scale_id = x_rating_scale_id
1773 and b.language = userenv ('LANG')
1774 and a.skill_level_id <> x_skill_level_id;
1775
1776 l_dummy_var number;
1777 l_ovn number;
1778 BEGIN
1779 -- for bug 3799295
1780 -- duplicate checking for the level order
1781 open c_dup_order;
1782 fetch c_dup_order into l_dummy_var;
1783
1784 if l_dummy_var is not null then
1788
1785 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_ORDER');
1786 raise_application_error(-20120,fnd_message.get);
1787 end if;
1789 close c_dup_order;
1790
1791 -- duplicate checking for the level name
1792 open c_dup_name;
1793 fetch c_dup_name into l_dummy_var;
1794
1795 if l_dummy_var is not null then
1796 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_LEVEL');
1797 raise_application_error(-20110,fnd_message.get);
1798 end if;
1799
1800 close c_dup_name;
1801
1802 update csf_skill_levels_b
1803 set rating_scale_id = x_rating_scale_id
1804 , step_value = x_step_value
1805 , correction_factor = x_correction_factor
1806 , start_date_active = x_start_date_active
1807 , end_date_active = x_end_date_active
1808 , seeded_flag = nvl(x_seeded_flag, 'N')
1809 , object_version_number = object_version_number + 1
1810 , attribute1 = x_attribute1
1811 , attribute2 = x_attribute2
1812 , attribute3 = x_attribute3
1813 , attribute4 = x_attribute4
1814 , attribute5 = x_attribute5
1815 , attribute6 = x_attribute6
1816 , attribute7 = x_attribute7
1817 , attribute8 = x_attribute8
1818 , attribute9 = x_attribute9
1819 , attribute10 = x_attribute10
1820 , attribute11 = x_attribute11
1821 , attribute12 = x_attribute12
1822 , attribute13 = x_attribute13
1823 , attribute14 = x_attribute14
1824 , attribute15 = x_attribute15
1825 , attribute_category = x_attribute_category
1826 , last_update_date = sysdate
1827 , last_updated_by = g_user_id
1828 , last_update_login = g_login_id
1829 where skill_level_id = x_skill_level_id
1830 returning object_version_number into l_ovn;
1831
1832 if sql%notfound
1833 then
1834 raise no_data_found;
1835 end if;
1836
1837 update csf_skill_levels_tl
1838 set name = x_name
1839 , description = x_description
1840 , last_update_date = sysdate
1841 , last_updated_by = g_user_id
1842 , last_update_login = g_login_id
1843 , source_lang = userenv('LANG')
1844 where skill_level_id = x_skill_level_id
1845 and userenv('LANG') in (language, source_lang);
1846
1847 if sql%notfound
1848 then
1849 raise no_data_found;
1850 end if;
1851 x_object_version_number := l_ovn;
1852 END update_skill_level;
1853
1854 PROCEDURE delete_skill_level ( x_skill_level_id in number )
1855 IS
1856 BEGIN
1857 delete from csf_skill_levels_tl
1858 where skill_level_id = x_skill_level_id;
1859
1860 if sql%notfound
1861 then
1862 raise no_data_found;
1863 end if;
1864
1865 delete from csf_skill_levels_b
1866 where skill_level_id = x_skill_level_id;
1867
1868 if sql%notfound then
1869 raise no_data_found;
1870 end if;
1871 END delete_skill_level;
1872
1873 PROCEDURE add_skill_level_language
1874 IS
1875 BEGIN
1876 delete from csf_skill_levels_tl t
1877 where not exists
1878 ( select null
1879 from csf_skill_levels_b b
1880 where b.skill_level_id = t.skill_level_id );
1881
1882 update csf_skill_levels_tl t
1883 set ( name
1884 , description ) = ( select b.name
1885 , b.description
1886 from csf_skill_levels_tl b
1887 where b.skill_level_id = t.skill_level_id
1888 and b.language = t.source_lang )
1889 where ( t.skill_level_id, t.language ) in
1890 ( select subt.skill_level_id
1891 , subt.language
1892 from csf_skill_levels_tl subb
1893 , csf_skill_levels_tl subt
1894 where subb.skill_level_id = subt.skill_level_id
1895 and subb.language = subt.source_lang
1896 and ( subb.name <> subt.name
1897 or subb.description <> subt.description
1898 or (subb.description is null and subt.description is not null)
1899 or (subb.description is not null and subt.description is null)));
1900
1901 insert into csf_skill_levels_tl
1902 ( skill_level_id
1903 , name
1904 , description
1905 , created_by
1906 , creation_date
1907 , last_updated_by
1908 , last_update_date
1909 , last_update_login
1910 , language
1911 , source_lang )
1912 select b.skill_level_id
1913 , b.name
1914 , b.description
1915 , b.created_by
1916 , b.creation_date
1917 , b.last_updated_by
1918 , b.last_update_date
1919 , b.last_update_login
1920 , l.language_code
1921 , b.source_lang
1922 from csf_skill_levels_tl b
1923 , fnd_languages l
1924 where l.installed_flag in ('I', 'B')
1925 and b.language = userenv('LANG')
1926 and not exists
1930 and t.language = l.language_code );
1927 ( select null
1928 from csf_skill_levels_tl t
1929 where t.skill_level_id = b.skill_level_id
1931 END add_skill_level_language;
1932
1933 PROCEDURE create_resource_skill
1934 ( x_rowid in out nocopy varchar2
1935 , x_resource_skill_id in out nocopy number
1936 , x_skill_type_id in number
1937 , x_skill_id in number
1938 , x_resource_type in varchar2
1939 , x_resource_id in number
1940 , x_skill_level_id in number
1941 , x_start_date_active in date
1942 , x_end_date_active in date
1943 , x_object_version_number in out nocopy number
1944 , x_attribute1 in varchar2 default null
1945 , x_attribute2 in varchar2 default null
1946 , x_attribute3 in varchar2 default null
1947 , x_attribute4 in varchar2 default null
1948 , x_attribute5 in varchar2 default null
1949 , x_attribute6 in varchar2 default null
1950 , x_attribute7 in varchar2 default null
1951 , x_attribute8 in varchar2 default null
1952 , x_attribute9 in varchar2 default null
1953 , x_attribute10 in varchar2 default null
1954 , x_attribute11 in varchar2 default null
1955 , x_attribute12 in varchar2 default null
1956 , x_attribute13 in varchar2 default null
1957 , x_attribute14 in varchar2 default null
1958 , x_attribute15 in varchar2 default null
1959 , x_attribute_category in varchar2 default null )
1960 IS
1961 cursor c_rowid
1962 is
1963 select rowid
1964 from csf_resource_skills_b
1965 where resource_skill_id = x_resource_skill_id;
1966
1967 -- cursor to check for duplicate skills assigned to a resource
1968 cursor c_resSkill is
1969 select 1
1970 from csf_resource_skills_b
1971 where resource_id = x_resource_id
1972 and skill_id = x_skill_id
1973 and resource_type = x_resource_type
1974 and skill_type_id = x_skill_type_id;
1975
1976 l_dummy_var number;
1977 BEGIN
1978 if x_resource_skill_id is null
1979 then
1980 select csf_resource_skills_b_s1.nextval
1981 into x_resource_skill_id
1982 from dual;
1983 else
1984 -- Checks if record to be inserted already exists.
1985 -- If it does, do nothing (RETURN), else, continue.
1986 open c_rowid;
1987 fetch c_rowid into x_rowid;
1988 if c_rowid%found
1989 then
1990 close c_rowid;
1991 return;
1992 end if;
1993 close c_rowid;
1994 end if;
1995
1996 -- check for duplicte skills assigned to a resource
1997 open c_resSkill;
1998 fetch c_resSkill into l_dummy_var;
1999
2000 if l_dummy_var is not null then
2001 close c_resSkill;
2002 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_RES_SKILL');
2003 raise_application_error(-20110,fnd_message.get);
2004 end if;
2005
2006 close c_resSkill;
2007
2008 if x_object_version_number is null
2009 then
2010 x_object_version_number := 1;
2011 end if;
2012
2013 insert into csf_resource_skills_b
2014 ( resource_skill_id
2015 , skill_type_id
2016 , skill_id
2017 , resource_type
2018 , resource_id
2019 , skill_level_id
2020 , start_date_active
2021 , end_date_active
2022 , object_version_number
2023 , attribute1
2024 , attribute2
2025 , attribute3
2026 , attribute4
2027 , attribute5
2028 , attribute6
2029 , attribute7
2030 , attribute8
2031 , attribute9
2032 , attribute10
2033 , attribute11
2034 , attribute12
2035 , attribute13
2036 , attribute14
2037 , attribute15
2038 , attribute_category
2039 , creation_date
2040 , created_by
2041 , last_update_date
2042 , last_updated_by
2043 , last_update_login )
2044 values
2045 ( x_resource_skill_id
2046 , x_skill_type_id
2047 , x_skill_id
2048 , x_resource_type
2049 , x_resource_id
2050 , x_skill_level_id
2051 , x_start_date_active
2052 , x_end_date_active
2053 , x_object_version_number
2054 , x_attribute1
2055 , x_attribute2
2056 , x_attribute3
2057 , x_attribute4
2058 , x_attribute5
2059 , x_attribute6
2060 , x_attribute7
2061 , x_attribute8
2062 , x_attribute9
2063 , x_attribute10
2064 , x_attribute11
2065 , x_attribute12
2066 , x_attribute13
2067 , x_attribute14
2068 , x_attribute15
2069 , x_attribute_category
2070 , sysdate
2071 , fnd_global.user_id
2072 , sysdate
2073 , g_user_id
2074 , g_login_id );
2075
2076 open c_rowid;
2077 fetch c_rowid into x_rowid;
2078 if c_rowid%notfound
2079 then
2080 close c_rowid;
2081 raise no_data_found;
2082 end if;
2086 PROCEDURE lock_resource_skill
2083 close c_rowid;
2084 END create_resource_skill;
2085
2087 ( x_resource_skill_id in number
2088 , x_object_version_number in number )
2089 IS
2090 cursor c_ovn
2091 is
2092 select object_version_number
2093 from csf_resource_skills_b
2094 where resource_skill_id = x_resource_skill_id
2095 for update of resource_skill_id nowait;
2096
2097 l_rec c_ovn%rowtype;
2098
2099 BEGIN
2100 open c_ovn;
2101 fetch c_ovn into l_rec;
2102 if c_ovn%notfound
2103 then
2104 close c_ovn;
2105 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2106 app_exception.raise_exception;
2107 end if;
2108 close c_ovn;
2109
2110 if l_rec.object_version_number = x_object_version_number
2111 then
2112 null;
2113 else
2114 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2115 app_exception.raise_exception;
2116 end if;
2117 END lock_resource_skill;
2118
2119 PROCEDURE update_resource_skill
2120 ( x_resource_skill_id in number
2121 , x_object_version_number in out nocopy number
2122 , x_skill_type_id in number
2123 , x_skill_id in number
2124 , x_resource_type in varchar2
2125 , x_resource_id in number
2126 , x_skill_level_id in number
2127 , x_start_date_active in date
2128 , x_end_date_active in date
2129 , x_attribute1 in varchar2 default null
2130 , x_attribute2 in varchar2 default null
2131 , x_attribute3 in varchar2 default null
2132 , x_attribute4 in varchar2 default null
2133 , x_attribute5 in varchar2 default null
2134 , x_attribute6 in varchar2 default null
2135 , x_attribute7 in varchar2 default null
2136 , x_attribute8 in varchar2 default null
2137 , x_attribute9 in varchar2 default null
2138 , x_attribute10 in varchar2 default null
2139 , x_attribute11 in varchar2 default null
2140 , x_attribute12 in varchar2 default null
2141 , x_attribute13 in varchar2 default null
2142 , x_attribute14 in varchar2 default null
2143 , x_attribute15 in varchar2 default null
2144 , x_attribute_category in varchar2 default null )
2145 IS
2146 -- cursor to check for duplicate skills assigned to a resource
2147 cursor c_resSkill is
2148 select 1
2149 from csf_resource_skills_b
2150 where resource_id = x_resource_id
2151 and skill_id = x_skill_id
2152 and resource_type = x_resource_type
2153 and skill_type_id = x_skill_type_id
2154 and resource_skill_id <> x_resource_skill_id;
2155
2156 l_dummy_var number;
2157 l_ovn number;
2158 BEGIN
2159 -- check for duplicate skills assigned to a resource
2160 open c_resSkill;
2161 fetch c_resSkill into l_dummy_var;
2162
2163 if l_dummy_var is not null then
2164 close c_resSkill;
2165 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_RES_SKILL');
2166 raise_application_error(-20110,fnd_message.get);
2167 end if;
2168
2169 close c_resSkill;
2170
2171 update csf_resource_skills_b
2172 set skill_type_id = x_skill_type_id
2173 , skill_id = x_skill_id
2174 , resource_type = x_resource_type
2175 , resource_id = x_resource_id
2176 , skill_level_id = x_skill_level_id
2177 , start_date_active = x_start_date_active
2178 , end_date_active = x_end_date_active
2179 , object_version_number = object_version_number + 1
2180 , attribute1 = x_attribute1
2181 , attribute2 = x_attribute2
2182 , attribute3 = x_attribute3
2183 , attribute4 = x_attribute4
2184 , attribute5 = x_attribute5
2185 , attribute6 = x_attribute6
2186 , attribute7 = x_attribute7
2187 , attribute8 = x_attribute8
2188 , attribute9 = x_attribute9
2189 , attribute10 = x_attribute10
2190 , attribute11 = x_attribute11
2191 , attribute12 = x_attribute12
2192 , attribute13 = x_attribute13
2193 , attribute14 = x_attribute14
2194 , attribute15 = x_attribute15
2195 , attribute_category = x_attribute_category
2196 , last_update_date = sysdate
2197 , last_updated_by = g_user_id
2198 , last_update_login = g_login_id
2199 where resource_skill_id = x_resource_skill_id
2200 returning object_version_number into l_ovn;
2201
2202 if sql%notfound
2203 then
2204 raise no_data_found;
2205 end if;
2206 x_object_version_number := l_ovn;
2207 END update_resource_skill;
2208
2209 PROCEDURE delete_resource_skill ( x_resource_skill_id in number )
2210 IS
2211 BEGIN
2212 delete from csf_resource_skills_b
2213 where resource_skill_id = x_resource_skill_id;
2214
2215 if sql%notfound then
2216 raise no_data_found;
2217 end if;
2218 END delete_resource_skill;
2219
2220 PROCEDURE create_required_skill
2221 ( x_rowid in out nocopy varchar2
2222 , x_required_skill_id in out nocopy number
2223 , x_skill_type_id in number
2227 , x_skill_level_id in number
2224 , x_skill_id in number
2225 , x_has_skill_type in varchar2
2226 , x_has_skill_id in number
2228 , x_skill_required_flag in varchar2
2229 , x_level_required_flag in varchar2
2230 , x_disabled_flag in varchar2
2231 , x_start_date_active in date
2232 , x_end_date_active in date
2233 , x_object_version_number in out nocopy number
2234 , x_attribute1 in varchar2 default null
2235 , x_attribute2 in varchar2 default null
2236 , x_attribute3 in varchar2 default null
2237 , x_attribute4 in varchar2 default null
2238 , x_attribute5 in varchar2 default null
2239 , x_attribute6 in varchar2 default null
2240 , x_attribute7 in varchar2 default null
2241 , x_attribute8 in varchar2 default null
2242 , x_attribute9 in varchar2 default null
2243 , x_attribute10 in varchar2 default null
2244 , x_attribute11 in varchar2 default null
2245 , x_attribute12 in varchar2 default null
2246 , x_attribute13 in varchar2 default null
2247 , x_attribute14 in varchar2 default null
2248 , x_attribute15 in varchar2 default null
2249 , x_attribute_category in varchar2 default null )
2250 IS
2251 cursor c_rowid
2252 is
2253 select rowid
2254 from csf_required_skills_b
2255 where required_skill_id = x_required_skill_id;
2256
2257 -- cursor to check for duplicate skills to task
2258 cursor c_reqSkill is
2259 select 1
2260 from csf_required_skills_b
2261 where skill_id = x_skill_id
2262 and skill_type_id = x_skill_type_id
2263 and has_skill_type = x_has_skill_type
2264 and has_skill_id = x_has_skill_id;
2265
2266 l_dummy_var number;
2267 BEGIN
2268 if x_required_skill_id is null
2269 then
2270 select csf_required_skills_b_s1.nextval
2271 into x_required_skill_id
2272 from dual;
2273 else
2274 -- Checks if record to be inserted already exists.
2275 -- If it does, do nothing (RETURN), else, continue.
2276 open c_rowid;
2277 fetch c_rowid into x_rowid;
2278 if c_rowid%found
2279 then
2280 close c_rowid;
2281 return;
2282 end if;
2283 close c_rowid;
2284 end if;
2285
2286 -- check for duplicate skills to task
2287 open c_reqSkill;
2288 fetch c_reqSkill into l_dummy_var;
2289
2290 if l_dummy_var is not null then
2291 close c_reqSkill;
2292
2293 if x_has_skill_type='TASK' then
2294 fnd_message.set_name('CSF','CSF_DUPLICATE_RECORD');
2295 raise_application_error(-20110,fnd_message.get);
2296 elsIf x_has_skill_type='TASK TEMPLATE' then
2297 fnd_message.set_name('CSF','CSF_DC_DUPLICATE_TASK_TEMPLATE');
2298 raise_application_error(-20110,fnd_message.get);
2299 end if;
2300 end if;
2301
2302 close c_reqSkill;
2303
2304 if x_object_version_number is null
2305 then
2306 x_object_version_number := 1;
2307 end if;
2308
2309 insert into csf_required_skills_b
2310 ( required_skill_id
2311 , skill_type_id
2312 , skill_id
2313 , has_skill_type
2314 , has_skill_id
2315 , skill_level_id
2316 , skill_required_flag
2317 , level_required_flag
2318 , disabled_flag
2319 , start_date_active
2320 , end_date_active
2321 , object_version_number
2322 , attribute1
2323 , attribute2
2324 , attribute3
2325 , attribute4
2326 , attribute5
2327 , attribute6
2328 , attribute7
2329 , attribute8
2330 , attribute9
2331 , attribute10
2332 , attribute11
2333 , attribute12
2334 , attribute13
2335 , attribute14
2336 , attribute15
2337 , attribute_category
2338 , creation_date
2339 , created_by
2340 , last_update_date
2341 , last_updated_by
2342 , last_update_login )
2343 values
2344 ( x_required_skill_id
2345 , x_skill_type_id
2346 , x_skill_id
2347 , x_has_skill_type
2348 , x_has_skill_id
2349 , x_skill_level_id
2350 , x_skill_required_flag
2351 , x_level_required_flag
2352 , x_disabled_flag
2353 , x_start_date_active
2354 , x_end_date_active
2355 , x_object_version_number
2356 , x_attribute1
2357 , x_attribute2
2358 , x_attribute3
2359 , x_attribute4
2360 , x_attribute5
2361 , x_attribute6
2362 , x_attribute7
2363 , x_attribute8
2364 , x_attribute9
2365 , x_attribute10
2366 , x_attribute11
2367 , x_attribute12
2368 , x_attribute13
2369 , x_attribute14
2370 , x_attribute15
2371 , x_attribute_category
2372 , sysdate
2373 , fnd_global.user_id
2374 , sysdate
2375 , g_user_id
2376 , g_login_id );
2377
2378 open c_rowid;
2379 fetch c_rowid into x_rowid;
2380 if c_rowid%notfound
2381 then
2382 close c_rowid;
2383 raise no_data_found;
2384 end if;
2385 close c_rowid;
2386 END create_required_skill;
2387
2388 PROCEDURE lock_required_skill
2389 ( x_required_skill_id in number
2393 is
2390 , x_object_version_number in number )
2391 IS
2392 cursor c_ovn
2394 select object_version_number
2395 from csf_required_skills_b
2396 where required_skill_id = x_required_skill_id
2397 for update of required_skill_id nowait;
2398
2399 l_rec c_ovn%rowtype;
2400
2401 BEGIN
2402 open c_ovn;
2403 fetch c_ovn into l_rec;
2404 if c_ovn%notfound
2405 then
2406 close c_ovn;
2407 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2408 app_exception.raise_exception;
2409 end if;
2410 close c_ovn;
2411
2412 if l_rec.object_version_number = x_object_version_number
2413 then
2414 null;
2415 else
2416 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2417 app_exception.raise_exception;
2418 end if;
2419 END lock_required_skill;
2420
2421 PROCEDURE update_required_skill
2422 ( x_required_skill_id in number
2423 , x_object_version_number in out nocopy number
2424 , x_skill_type_id in number
2425 , x_skill_id in number
2426 , x_has_skill_type in varchar2
2427 , x_has_skill_id in number
2428 , x_skill_level_id in number
2429 , x_skill_required_flag in varchar2
2430 , x_level_required_flag in varchar2
2431 , x_disabled_flag in varchar2
2432 , x_start_date_active in date
2433 , x_end_date_active in date
2434 , x_attribute1 in varchar2 default null
2435 , x_attribute2 in varchar2 default null
2436 , x_attribute3 in varchar2 default null
2437 , x_attribute4 in varchar2 default null
2438 , x_attribute5 in varchar2 default null
2439 , x_attribute6 in varchar2 default null
2440 , x_attribute7 in varchar2 default null
2441 , x_attribute8 in varchar2 default null
2442 , x_attribute9 in varchar2 default null
2443 , x_attribute10 in varchar2 default null
2444 , x_attribute11 in varchar2 default null
2445 , x_attribute12 in varchar2 default null
2446 , x_attribute13 in varchar2 default null
2447 , x_attribute14 in varchar2 default null
2448 , x_attribute15 in varchar2 default null
2449 , x_attribute_category in varchar2 default null )
2450 IS
2451 l_ovn number;
2452 BEGIN
2453 update csf_required_skills_b
2454 set skill_type_id = x_skill_type_id
2455 , skill_id = x_skill_id
2456 , has_skill_type = x_has_skill_type
2457 , has_skill_id = x_has_skill_id
2458 , skill_level_id = x_skill_level_id
2459 , skill_required_flag = x_skill_required_flag
2460 , level_required_flag = x_level_required_flag
2461 , disabled_flag = x_disabled_flag
2462 , start_date_active = x_start_date_active
2463 , end_date_active = x_end_date_active
2464 , object_version_number = object_version_number + 1
2465 , attribute1 = x_attribute1
2466 , attribute2 = x_attribute2
2467 , attribute3 = x_attribute3
2468 , attribute4 = x_attribute4
2469 , attribute5 = x_attribute5
2470 , attribute6 = x_attribute6
2471 , attribute7 = x_attribute7
2472 , attribute8 = x_attribute8
2473 , attribute9 = x_attribute9
2474 , attribute10 = x_attribute10
2475 , attribute11 = x_attribute11
2476 , attribute12 = x_attribute12
2477 , attribute13 = x_attribute13
2478 , attribute14 = x_attribute14
2479 , attribute15 = x_attribute15
2480 , attribute_category = x_attribute_category
2481 , last_update_date = sysdate
2482 , last_updated_by = g_user_id
2483 , last_update_login = g_login_id
2484 where required_skill_id = x_required_skill_id
2485 returning object_version_number into l_ovn;
2486
2487 if sql%notfound
2488 then
2489 raise no_data_found;
2490 end if;
2491 x_object_version_number := l_ovn;
2492 END update_required_skill;
2493
2494 PROCEDURE delete_required_skill ( x_required_skill_id in number )
2495 IS
2496 BEGIN
2497 delete from csf_required_skills_b
2498 where required_skill_id = x_required_skill_id;
2499
2500 if sql%notfound then
2501 raise no_data_found;
2502 end if;
2503 END delete_required_skill;
2504
2505 --==============================================================
2506 -- PUBLIC Procedures for translation
2507 --==============================================================
2508 PROCEDURE translate_rating_scale
2509 ( p_rating_scale_id in varchar2
2510 , p_owner in varchar2
2511 , p_name in varchar2
2512 , p_description in varchar2)
2513 IS
2514 BEGIN
2515 update csf_rating_scales_tl
2516 set name = p_name,
2517 description = p_description,
2518 last_update_date = sysdate,
2519 last_updated_by = decode(p_owner, 'SEED', 1, 0),
2520 last_update_login = 0,
2521 source_lang = userenv('LANG')
2522 where rating_scale_id = to_number(p_rating_scale_id)
2523 and userenv('LANG') in (language, source_lang);
2524 END translate_rating_scale;
2525
2526 PROCEDURE translate_skill
2527 ( p_skill_id in varchar2
2528 , p_owner in varchar2
2529 , p_name in varchar2
2530 , p_description in varchar2)
2531 IS
2532 BEGIN
2533 update csf_skills_tl
2534 set name = p_name,
2535 description = p_description,
2536 last_update_date = sysdate,
2537 last_updated_by = decode(p_owner, 'SEED', 1, 0),
2538 last_update_login = 0,
2539 source_lang = userenv('LANG')
2540 where skill_id = to_number(p_skill_id)
2541 and userenv('LANG') in (language, source_lang);
2542 END translate_skill;
2543
2544
2545 PROCEDURE translate_skill_level
2546 ( p_skill_level_id in varchar2
2547 , p_owner in varchar2
2548 , p_name in varchar2
2549 , p_description in varchar2)
2550 IS
2551 BEGIN
2552 update csf_skill_levels_tl
2553 set name = p_name,
2554 description = p_description,
2555 last_update_date = sysdate,
2556 last_updated_by = decode(p_owner, 'SEED', 1, 0),
2557 last_update_login = 0,
2558 source_lang = userenv('LANG')
2559 where skill_level_id = to_number(p_skill_level_id)
2560 and userenv('LANG') in (language, source_lang);
2561 END translate_skill_level;
2562
2563 PROCEDURE translate_skill_type
2564 ( p_skill_type_id in varchar2
2565 , p_owner in varchar2
2566 , p_name in varchar2
2567 , p_description in varchar2)
2568 IS
2569 BEGIN
2570 update csf_skill_types_tl
2571 set name = p_name,
2572 description = p_description,
2573 last_update_date = sysdate,
2574 last_updated_by = decode(p_owner, 'SEED', 1, 0),
2575 last_update_login = 0,
2576 source_lang = userenv('LANG')
2577 where skill_type_id = to_number(p_skill_type_id)
2578 and userenv('LANG') in (language, source_lang);
2579 END translate_skill_type;
2580
2581 BEGIN
2582 -- set some session info
2583 g_user_id := fnd_global.user_id;
2584 g_login_id := fnd_global.login_id;
2585
2586 END csf_skills_pkg;